CREATE PROCEDURE dbo.[Forum_Tracking_GetUsersForums]
    (
      @UserID INT,
      @ModuleID INT,
      @PageSize INT,
      @PageIndex INT
    )
AS
    DECLARE @RowStart INT
    DECLARE @RowEnd INT
                
    SET @RowStart = @PageSize * @PageIndex + 1 ;
    SET @RowEnd = @RowStart + @PageSize - 1 ;

    SELECT  *
    FROM    ( 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,
                        ROW_NUMBER() OVER ( ORDER BY G.SortOrder ASC, F.SortOrder ASC ) AS RowNumber,
                        ( 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_TrackedForums
                          WHERE     UserID = @UserID
                                    AND ModuleID = @ModuleID
                        ) AS TotalRecords
              FROM      dbo.Forum_Forums F
                        INNER JOIN dbo.Forum_TrackedForums TF ON F.ForumID = TF.ForumID
                        INNER JOIN dbo.Forum_Groups G ON F.GroupID = G.GroupID
              WHERE     TF.UserID = @UserID
                        AND TF.ModuleID = @ModuleID
                        AND IsActive = 1
            ) AS ForumInfo
    WHERE   RowNumber >= @RowStart
            AND RowNumber <= @RowEnd

GO