if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[files_comments_getComments]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[files_comments_getComments]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE  PROCEDURE [dbo].[files_comments_getComments]
(
	@SectionID int,
	@PageIndex int, 
	@PageSize int,
	@sqlPopulate ntext,
	@UserID int,
	@IncludePageIndex bit,
	@SettingsID int,
	@TotalRecords int output
)
AS
SET Transaction Isolation Level Read UNCOMMITTED

DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int
DECLARE @TotalThreads int

-- First set the rowcount
SET @RowsToReturn = @PageSize * (@PageIndex + 1)


-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1



-- Create a temp table to store the select results
CREATE TABLE #PageIndex 
(
	IndexID int IDENTITY (1, 1) NOT NULL,
	PostID int
)

--CREATE INDEX page_index ON #PageIndex(IndexID)

INSERT INTO #PageIndex (PostID)
EXEC (@sqlPopulate)

SET @TotalRecords = @@rowcount

SET ROWCOUNT @RowsToReturn

SELECT 
	SortOrder = jPI.IndexID,
	jP.PostID, jP.ThreadID, jP.ParentID, jP.PostAuthor, jP.UserID, jP.SectionID, jP.PostLevel, jP.SortOrder, jP.Subject, jP.PostDate, jP.IsApproved,
	jP.IsLocked, jP.IsIndexed, jP.TotalViews, jP.Body, jP.FormattedBody, jP.IPAddress, jP.PostType, jP.PostMedia, jP.EmoticonID, jP.SettingsID, jP.AggViews,
	jP.PostPropertyNames, jP.PostPropertyValues,
	jP.PostConfiguration, jP.UserTime, jP.ApplicationPostType, jP.PostName, jP.PostStatus, jP.SpamScore,
	jP.Points as PostPoints, jP.RatingSum as PostRatingSum, jP.TotalRatings as PostTotalRatings,
	HasRead = 1,
	jT.*, jU.*,
	Username = jP.PostAuthor,
	ThreadStarterAuthor = jT.PostAuthor,
	ThreadStartDate = jT.PostDate,	
	EditNotes = (SELECT EditNotes FROM cs_PostEditNotes WHERE PostID = jP.PostID),
	AttachmentFilename = ISNULL ( (SELECT [FileName] FROM cs_PostAttachments WHERE PostID = jP.PostID), ''),
	Replies = 0, --not used(SELECT COUNT(P2.PostID) FROM cs_Posts P2 (nolock) WHERE P2.ParentID = P.PostID AND P2.PostLevel != 1),
	IsModerator = 0 -- not used
FROM 
	#PageIndex jPI
	JOIN cs_vw_PostsWithAttachmentDetails jP ON jPI.PostID = jP.PostID
	JOIN cs_Threads jT ON jP.ThreadID = jT.ThreadID
	JOIN cs_vw_Users_FullUser jU ON jU.cs_UserID = jP.UserID
WHERE 
	jPI.IndexID > @PageLowerBound
	AND jPI.IndexID < @PageUpperBound
	--AND jP.PostLevel = 1 	-- PostLevel=1 should mean it's a top-level thread starter,
	AND jp.SettingsID = @SettingsID AND jT.SettingsID = @SettingsID and jU.SettingsID = @SettingsID 
ORDER BY
	jPI.IndexID	-- this is the ordering system we're using populated from the @sqlPopulate

SET ROWCOUNT 0

If @IncludePageIndex = 1
BEGIN
	SELECT IndexID, PostID from #PageIndex ORDER BY IndexID
END

DROP TABLE #PageIndex
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[files_Comments_GetComments] to public
go
