SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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



CREATE   PROCEDURE dbo.cs_Post_GetPageIndex
(
	@PostID int,
	@PageSize int,
	@SortBy int,
	@SortOrder bit,
	@AllowUnapproved bit = 0,
	@SettingsID int
)
AS

DECLARE @ThreadID int
SELECT @ThreadID = ThreadID FROM cs_Posts WHERE PostID = @PostID AND SettingsID = @SettingsID

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

-- Sort by Post Date
IF @SortBy = 0 AND @SortOrder = 0
    INSERT INTO #PageIndex (PostID)
    SELECT PostID FROM cs_Posts (nolock) WHERE (IsApproved = 1 OR 1 = @AllowUnapproved) AND ThreadID = @ThreadID and SettingsID = @SettingsID ORDER BY PostDate

ELSE IF @SortBy = 0 AND @SortOrder = 1
    INSERT INTO #PageIndex (PostID)
    SELECT PostID FROM cs_Posts (nolock) WHERE (IsApproved = 1 OR 1 = @AllowUnapproved) AND ThreadID = @ThreadID and SettingsID = @SettingsID  ORDER BY PostDate DESC

-- Sort by Author
IF @SortBy = 1 AND @SortOrder = 0
    INSERT INTO #PageIndex (PostID)
    SELECT PostID FROM cs_Posts (nolock) WHERE (IsApproved = 1 OR 1 = @AllowUnapproved) AND ThreadID = @ThreadID and SettingsID = @SettingsID  ORDER BY UserID

ELSE IF @SortBy = 1 AND @SortOrder = 1
    INSERT INTO #PageIndex (PostID)
    SELECT PostID FROM cs_Posts (nolock) WHERE (IsApproved = 1 OR 1 = @AllowUnapproved) AND ThreadID = @ThreadID and SettingsID = @SettingsID  ORDER BY UserID DESC

-- Sort by SortOrder
IF @SortBy = 2 AND @SortOrder = 0
    INSERT INTO #PageIndex (PostID)
    SELECT PostID FROM cs_Posts (nolock) WHERE (IsApproved = 1 OR 1 = @AllowUnapproved) AND ThreadID = @ThreadID and SettingsID = @SettingsID  ORDER BY SortOrder

ELSE IF @SortBy = 2 AND @SortOrder = 1
    INSERT INTO #PageIndex (PostID)
    SELECT PostID FROM cs_Posts (nolock) WHERE (IsApproved = 1 OR 1 = @AllowUnapproved) AND ThreadID = @ThreadID and SettingsID = @SettingsID  ORDER BY SortOrder DESC

SELECT (IndexID / @PageSize) + 1 AS PageIndex FROM #PageIndex WHERE PostID = @PostID




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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