CREATE PROCEDURE dbo.[Forum_Search_GetResults]
    (
      @Filter NVARCHAR(500),
      @PageIndex INT,
      @PageSize INT,
      @UserID INT,
      @ModuleID INT,
      @FromDate DATETIME,
      @ToDate DATETIME,
      @ThreadStatusID INT
    )
AS
    CREATE TABLE #PageIndex
        (
          IndexID INT IDENTITY(1, 1)
                      NOT NULL,
          CreatedDate DATETIME NULL,
          ThreadID INT
        )
    -- Create dynamic SQL to populate temporary table
    DECLARE @sql NVARCHAR(2000)
    SELECT  @sql = 'INSERT INTO #PageIndex(ThreadID, CreatedDate) SELECT (ThreadID), '
            + '(SELECT FP.CreatedDate FROM dbo.Forum_Threads FT INNER JOIN dbo.Forum_Posts FP '
            + 'ON FT.LastPostedPostID = FP.PostID '
            + 'INNER JOIN dbo.Forum_Posts FFP ON FT.ThreadID = FFP.PostID '
            + 'WHERE FT.ThreadID = PS.ThreadID) As LastPostDate '
            + 'FROM dbo.Forum_vw_PostSearch PS ' + 'WHERE [CreatedDate] >  '''
            + CONVERT(VARCHAR, @FromDate) + ''' ' + 'AND [CreatedDate] < '''
            + CONVERT(VARCHAR, @ToDate) + ''' ' + 'AND IsApproved = 1 '
            + 'AND IsActive = 1 ' + 'AND ModuleID = '
            + CONVERT(VARCHAR, @ModuleID)
            + 'AND ( (EnableForumsThreadStatus = 0) '
            + 'OR (EnableForumsThreadStatus = 1 AND ThreadStatus = '
            + CONVERT(VARCHAR, @ThreadStatusID) + ') '
            + 'OR (EnableForumsThreadStatus = 1 AND -1 = '
            + CONVERT(VARCHAR, @ThreadStatusID) + ') ) '
            + 'AND ( PublicView = 1 OR ' + ' ForumID IN ( '
            + ' SELECT ForumID ' + ' FROM  dbo.Forum_ForumPermission '
            + ' WHERE AllowAccess=1 AND '
            + '(RoleID IN (SELECT RoleID FROM dbo.UserRoles WHERE UserID = '
            + CONVERT(VARCHAR, @UserID) + ' )' + ' OR EXISTS '
            + '(SELECT TOP 1 1 FROM dbo.Users WHERE UserID = '
            + CONVERT(VARCHAR, @UserID) + ' AND IsSuperUser=1)))) ' + @Filter
            + 'GROUP BY ThreadID ORDER BY LastPostDate DESC'
    EXEC sp_executesql @sql

    DECLARE @PageLowerBound INT
    DECLARE @PageUpperBound INT
    DECLARE @RecordCount INT
    -- Set the page bounds
    SET @PageLowerBound = @PageSize * @PageIndex
    SET @PageUpperBound = @PageLowerBound + @PageSize + 1
    
    SELECT  FFP.[Subject],
            FLP.Body,
            FLP.CreatedDate,
            FFP.UserID AS StartedByUserID,
            T.ThreadID,
            ForumID,
            [Views],
            LastPostedPostID AS LastApprovedPostID,
            ( SELECT    COUNT(ThreadID)
              FROM      dbo.Forum_Posts
              WHERE     ThreadID = T.ThreadID
                        AND PostID <> T.ThreadID
                    AND IsApproved = 1
            ) AS Replies,
            IsPinned,
            PinnedDate,
            FFP.IsClosed,
            ThreadStatus,
            AnswerPostID,
            AnswerUserID,
            PollID,
            AnswerDate,
            SitemapInclude,
            ISNULL(( SELECT MAX(ThreadID)
                     FROM   dbo.Forum_Threads
                     WHERE  ThreadID < T.ThreadID
                            AND ForumID = T.ForumID
                   ), 0) AS PreviousThreadID,
            ISNULL(( SELECT MIN(ThreadID)
                     FROM   dbo.Forum_Threads
                     WHERE  ThreadID > T.ThreadID
                            AND ForumID = T.ForumID
                   ), 0) AS NextThreadID,
            ISNULL(( SELECT COUNT(ThreadID)
                     FROM   dbo.Forum_ThreadRating TR
                     WHERE  TR.ThreadID = T.ThreadID
                   ), 0) AS RatingCount,
            ISNULL(( SELECT AVG(Rate)
                     FROM   dbo.Forum_ThreadRating TR
                     WHERE  TR.ThreadID = T.ThreadID
                   ), 0) AS Rating,
            ( SELECT    COUNT(ThreadID)
              FROM      #PageIndex
            ) AS TotalRecords,
            CI.CreatedByUserID,
            CI.CreatedOnDate,
            CI.ContentKey,
            CI.Indexed,
            CI.Content,
            CI.ContentItemID,
            CI.LastModifiedByUserID,
            CI.LastModifiedOnDate,
            CI.ModuleID,
            CI.TabID,
            CI.ContentTypeID
    FROM    dbo.Forum_Threads T
            INNER JOIN #PageIndex I ON T.ThreadID = I.ThreadID
            INNER JOIN dbo.Forum_Posts FFP ON I.ThreadID = FFP.PostID
            INNER JOIN dbo.Forum_Posts FLP ON T.LastPostedPostID = FLP.PostID
            LEFT JOIN dbo.ContentItems CI ON T.ContentItemID = CI.ContentItemID
    WHERE   I.IndexID > @PageLowerBound
            AND I.IndexID < @PageUpperBound
    ORDER BY FLP.CreatedDate DESC

GO