SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

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

CREATE PROCEDURE dbo.files_Entries_GetEntries
(
	@SectionID int,
	@PageIndex int, 
	@PageSize int,
	@sqlPopulate nText,
	@UserID int,
	@IncludeCategories bit,
	@SettingsID int,
	@TotalRecords int output
)
AS


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
)


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

SET ROWCOUNT @RowsToReturn

SELECT 
	jT.*,
	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.Points as PostPoints,
	jP.AttachmentFilename,jP.ContentType, jP.IsRemote, jP.Height, jP.Width, jP.FriendlyFileName, jP.ContentSize, jP.[FileName],jP.Created,
	HasRead = 1,
	UserName = jT.PostAuthor,
	Replies = (SELECT COUNT(P2.PostID) FROM cs_Posts P2 (nolock) WHERE P2.ParentID = jP.PostID AND P2.PostLevel != 1),
	Downloads = (SELECT COUNT(fD.UserID) FROM files_Downloads fD (nolock) WHERE fD.PostID = jP.PostID)
FROM 
	#PageIndex jPI
	JOIN cs_vw_PostsWithAttachmentDetails jP ON jPI.PostID = jP.PostID
	JOIN cs_Threads jT ON jP.ThreadID = jT.ThreadID
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
ORDER BY
	jPI.IndexID	-- this is the ordering system we're using populated from the @sqlPopulate

Select @TotalRecords = Count(*) FROM #PageIndex

IF @IncludeCategories = 1
Begin

Select 
	Cats.[Name], jP.PostID
FROM 
	#PageIndex jPI
	JOIN cs_Posts jP ON jPI.PostID = jP.PostID
	JOIN cs_Threads jT ON jT.ThreadID = jP.ThreadID
	JOIN cs_Posts_InCategories PIC on jP.PostID = PIC.PostID
	JOIN cs_Post_Categories Cats on PIC.CategoryID = Cats.CategoryID
WHERE 
	jPI.IndexID > @PageLowerBound
	AND jPI.IndexID < @PageUpperBound
	AND jP.PostLevel = 1 and jT.SettingsID = @SettingsID and jP.SettingsID = @SettingsID


End

DROP TABLE #PageIndex
GO

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