CREATE PROCEDURE dbo.[Forum_Forum_GetAllByParentID]
    (
      @ParentID INT,
      @GroupID INT,
      @EnabledOnly BIT
    )
AS
    IF @EnabledOnly = 1
        BEGIN
            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,
                    CASE WHEN ( SELECT  COUNT(ForumID)
                                FROM    dbo.Forum_Forums
                                WHERE   ParentID = F.ForumID
                              ) > 0
                         THEN ( SELECT TOP 1
                                        P.PostID
                                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 ( F.MostRecentPostID )
                    END AS MostRecentPostID,
                    ( 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 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 F
                                INNER JOIN dbo.Forum_Groups G ON F.GroupID = G.GroupID
                      WHERE     F.[ParentID] = @ParentID
                                AND F.[GroupID] = @GroupID
                                AND F.IsActive = 1
                    ) AS TotalRecords
            FROM    dbo.Forum_Forums F
                    INNER JOIN dbo.Forum_Groups G ON F.GroupID = G.GroupID
            WHERE   F.[ParentID] = @ParentID
                    AND F.[GroupID] = @GroupID
                    AND F.IsActive = 1
            ORDER BY F.[SortOrder]
        END
    ELSE
        BEGIN
            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,
                    CASE WHEN ( SELECT  COUNT(ForumID)
                                FROM    dbo.Forum_Forums
                                WHERE   ParentID = F.ForumID
                              ) > 0
                         THEN ( SELECT TOP 1
                                        P.PostID
                                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 ( F.MostRecentPostID )
                    END AS MostRecentPostID,
                    ( 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 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 F
                                INNER JOIN dbo.Forum_Groups G ON F.GroupID = G.GroupID
                      WHERE     F.[ParentID] = @ParentID
                                AND F.[GroupID] = @GroupID
                                AND F.IsActive = 1
                    ) AS TotalRecords
            FROM    dbo.Forum_Forums F
                    INNER JOIN dbo.Forum_Groups G ON F.GroupID = G.GroupID
            WHERE   F.[ParentID] = @ParentID
                    AND F.[GroupID] = @GroupID
            ORDER BY F.[SortOrder]
        END

GO