CREATE PROCEDURE dbo.[Forum_Forum_GetAll] ( @GroupID INT )
AS
    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.MostRecentPostID,
            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,
            ( 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,
            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,
            ( SELECT    COUNT(ForumID)
              FROM      dbo.Forum_Forums
                        INNER JOIN dbo.Forum_Groups G ON F.GroupID = G.GroupID
              WHERE     F.GroupID = @GroupID
            ) AS TotalRecords
    FROM    dbo.Forum_Forums F
            INNER JOIN dbo.Forum_Groups G ON F.GroupID = G.GroupID
    WHERE   F.GroupID = @GroupID
    ORDER BY F.SortOrder

GO