CREATE PROCEDURE dbo.[Forum_Moderate_GetModeratedForums]
    (
      @UserID INT,
      @ModuleID INT,
      @PortalID INT
    )
AS -- If a record exists for this user as a global moderator, show all forums that have posts to moderate
    IF EXISTS ( SELECT  NULL
                FROM    dbo.Users U
                        INNER JOIN dbo.Forum_Users FU ON U.UserID = FU.UserID
                        INNER JOIN dbo.UserRoles UR ON FU.UserID = UR.UserID
                        INNER JOIN dbo.ModulePermission MP ON UR.RoleID = MP.RoleID
                        INNER JOIN dbo.Permission P ON MP.PermissionID = P.PermissionID
                WHERE   MP.ModuleID = @ModuleID
                        AND ( P.PermissionCode = 'FORUMGLBMOD'
                              OR P.PermissionCode = 'FORUMADMIN'
                            )
                        AND FU.PortalID = @PortalID )
        BEGIN
-- return all forums
            SELECT  G.GroupID,
                    G.ModuleID,
                    F.ForumID,
                    F.IsActive,
                    F.ParentID,
                    F.Name,
                    F.[Description],
                    F.CreatedDate,
                    F.CreatedByUser,
                    F.UpdatedByUser,
                    F.UpdatedDate,
                    F.IsModerated,
                    F.SortOrder,
                    F.PostsToModerate,
                    F.ForumType,
                    F.PublicView,
                    F.PublicPosting,
                    F.EnableForumsThreadStatus,
                    F.EnableForumsRating,
                    F.ForumBehavior,
                    F.AllowPolls,
                    F.EnableRSS,
                    F.EmailAddress,
                    F.EmailFriendlyFrom,
                    F.NotifyByDefault,
                    F.EmailStatusChange,
                    F.EmailServer,
                    F.EmailUser,
                    F.EmailPass,
                    F.EmailEnableSSL,
                    F.EmailAuth,
                    F.EmailPort,
                    F.EnableSitemap,
                    F.SitemapPriority,
                    1 AS TotalRecords,
                    ( SELECT    COUNT(ForumID)
                      FROM      dbo.Forum_Forums
                      WHERE     ParentID = F.ForumID
                    ) AS SubForums,
                    CASE WHEN ( SELECT  COUNT(ForumID)
                                FROM    dbo.Forum_Forums
                                WHERE   ParentID = F.ForumID
                              ) > 0 THEN ( SELECT   SUM(TotalPosts)
                                           FROM     dbo.Forum_Forums
                                           WHERE    ParentID = F.ForumID
                                         )
                         ELSE F.TotalPosts
                    END AS TotalPosts,
                    CASE WHEN ( SELECT  COUNT(ForumID)
                                FROM    dbo.Forum_Forums
                                WHERE   ParentID = F.ForumID
                              ) > 0 THEN ( SELECT   SUM(TotalThreads)
                                           FROM     dbo.Forum_Forums
                                           WHERE    ParentID = F.ForumID
                                         )
                         ELSE F.TotalThreads
                    END AS TotalThreads,
                    CASE WHEN ( SELECT  COUNT(ForumID)
                                FROM    dbo.Forum_Forums
                                WHERE   ParentID = F.ForumID
                              ) > 0
                         THEN ( SELECT TOP 1
                                        P.CreatedDate
                                FROM    dbo.Forum_Forums FF
                                        INNER JOIN dbo.Forum_Threads T ON FF.ForumId = T.ForumID
                                        INNER JOIN dbo.Forum_Posts P ON T.LastPostedPostID = P.PostID
                                WHERE   FF.ParentID = F.ForumID
                                ORDER BY P.CreatedDate DESC
                              )
                         ELSE ( SELECT TOP 1
                                        P.CreatedDate
                                FROM    dbo.Forum_Forums FF
                                        INNER JOIN dbo.Forum_Threads T ON FF.ForumId = T.ForumID
                                        INNER JOIN dbo.Forum_Posts P ON T.LastPostedPostID = P.PostID
                                WHERE   FF.ForumID = F.ForumID
                                ORDER BY P.CreatedDate DESC
                              )
                    END AS MostRecentPostDate,
                    ( SELECT    MAX(PostID)
                      FROM      dbo.Forum_Posts P
                                INNER JOIN dbo.Forum_Threads T ON P.ThreadID = T.ThreadID
                      WHERE     T.ForumID = F.ForumID
                                AND P.IsApproved = 1
                    ) AS MostRecentPostID,
                    CASE WHEN LEFT(LOWER(ForumLink), 6) = 'fileid'
                         THEN ( SELECT  Folder + FileName
                                FROM    dbo.Files
                                WHERE   'fileid=' + CONVERT(VARCHAR, dbo.Files.FileID) = ForumLink
                              )
                         ELSE ForumLink
                    END AS ForumLink
            FROM    dbo.Forum_Forums F
                    INNER JOIN dbo.Forum_Groups G ON F.GroupID = G.GroupID
            WHERE   F.[IsActive] = 1
            ORDER BY G.[SortOrder],
                    F.[SortOrder]
        END
    ELSE
        BEGIN
-- See if user is assigned mod via role, or if they are mod via individual assignment
    -- return only forums this user can moderate
            SELECT  G.GroupID,
                    G.ModuleID,
                    F.ForumID,
                    F.IsActive,
                    F.ParentID,
                    F.Name,
                    F.[Description],
                    F.CreatedDate,
                    F.CreatedByUser,
                    F.UpdatedByUser,
                    F.UpdatedDate,
                    F.IsModerated,
                    F.SortOrder,
                    F.PostsToModerate,
                    F.ForumType,
                    F.PublicView,
                    F.PublicPosting,
                    F.EnableForumsThreadStatus,
                    F.EnableForumsRating,
                    F.ForumBehavior,
                    F.AllowPolls,
                    F.EnableRSS,
                    F.EmailAddress,
                    F.EmailFriendlyFrom,
                    F.NotifyByDefault,
                    F.EmailStatusChange,
                    F.EmailServer,
                    F.EmailUser,
                    F.EmailPass,
                    F.EmailEnableSSL,
                    F.EmailAuth,
                    F.EmailPort,
                    F.EnableSitemap,
                    F.SitemapPriority,
                    1 AS TotalRecords,
                    ( SELECT    COUNT(ForumID)
                      FROM      dbo.Forum_Forums
                      WHERE     ParentID = F.ForumID
                    ) AS SubForums,
                    CASE WHEN ( SELECT  COUNT(ForumID)
                                FROM    dbo.Forum_Forums
                                WHERE   ParentID = F.ForumID
                              ) > 0 THEN ( SELECT   SUM(TotalPosts)
                                           FROM     dbo.Forum_Forums
                                           WHERE    ParentID = F.ForumID
                                         )
                         ELSE F.TotalPosts
                    END AS TotalPosts,
                    CASE WHEN ( SELECT  COUNT(ForumID)
                                FROM    dbo.Forum_Forums
                                WHERE   ParentID = F.ForumID
                              ) > 0 THEN ( SELECT   SUM(TotalThreads)
                                           FROM     dbo.Forum_Forums
                                           WHERE    ParentID = F.ForumID
                                         )
                         ELSE F.TotalThreads
                    END AS TotalThreads,
                    CASE WHEN ( SELECT  COUNT(ForumID)
                                FROM    dbo.Forum_Forums
                                WHERE   ParentID = F.ForumID
                              ) > 0
                         THEN ( SELECT TOP 1
                                        P.CreatedDate
                                FROM    dbo.Forum_Forums FF
                                        INNER JOIN dbo.Forum_Threads T ON FF.ForumId = T.ForumID
                                        INNER JOIN dbo.Forum_Posts P ON T.LastPostedPostID = P.PostID
                                WHERE   FF.ParentID = F.ForumID
                                ORDER BY P.CreatedDate DESC
                              )
                         ELSE ( SELECT TOP 1
                                        P.CreatedDate
                                FROM    dbo.Forum_Forums FF
                                        INNER JOIN dbo.Forum_Threads T ON FF.ForumId = T.ForumID
                                        INNER JOIN dbo.Forum_Posts P ON T.LastPostedPostID = P.PostID
                                WHERE   FF.ForumID = F.ForumID
                                ORDER BY P.CreatedDate DESC
                              )
                    END AS MostRecentPostDate,
                    ( SELECT    MAX(PostID)
                      FROM      dbo.Forum_Posts P
                                INNER JOIN dbo.Forum_Threads T ON P.ThreadID = T.ThreadID
                      WHERE     T.ForumID = F.ForumID
                                AND P.IsApproved = 1
                    ) AS MostRecentPostID,
                    CASE WHEN LEFT(LOWER(ForumLink), 6) = 'fileid'
                         THEN ( SELECT  Folder + FileName
                                FROM    dbo.Files
                                WHERE   'fileid=' + CONVERT(VARCHAR, dbo.Files.FileID) = ForumLink
                              )
                         ELSE ForumLink
                    END AS ForumLink
            FROM    dbo.Forum_Forums F
                    INNER JOIN dbo.Forum_Groups G ON F.GroupID = G.GroupID
            WHERE   G.PortalID = @PortalID
                    AND ModuleID = @ModuleID
                    AND F.[IsActive] = 1
                    AND F.[PostsToModerate] > 0
                    AND ( G.[ModuleID] IN (
                          SELECT    ModuleID
                          FROM      dbo.Users U
                                    INNER JOIN dbo.Forum_Users FU ON U.UserID = FU.UserID
                                    INNER JOIN dbo.UserRoles UR ON FU.UserID = UR.UserID
                                    INNER JOIN dbo.Forum_ForumPermission FP ON UR.RoleID = FP.RoleID
                                    INNER JOIN dbo.Forum_Permission P ON FP.PermissionID = P.PermissionID
                                    INNER JOIN dbo.Forum_Forums F ON FP.ForumID = F.ForumID
                                    INNER JOIN dbo.Forum_Groups G ON F.GroupID = G.GroupID
                          WHERE     P.PermissionCode = 'MODERATE'
                                    AND FU.PortalID = @PortalID
                                    AND ModuleID = @ModuleID ) )
            ORDER BY G.[SortOrder],
                    F.[SortOrder]
        END

GO