cs_forums_posts_postset.prc

community server 源码

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO







CREATE PROCEDURE dbo.cs_forums_Posts_PostSet
(
	@PostID	int,
	@PageIndex int,
	@PageSize int,
	@SortBy int,
	@SortOrder bit,
	@UserID int,
	@ReturnRecordCount bit,
	@AllowUnapproved bit = 0,
	@SettingsID int,
	@IncludeCategories bit = 0
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN

DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @ThreadID int
DECLARE @SectionID int

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

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

-- Get the ThreadID
SELECT
	@ThreadID = ThreadID,
	@SectionID = SectionID
FROM 
	cs_Posts 
WHERE 
	PostID = @PostID and SettingsID = @SettingsID

-- Is the Forum 0 (If so this is a private message and we need to verify the user can view it
IF @SectionID = 0
BEGIN
	IF NOT EXISTS (SELECT UserID FROM cs_PrivateMessages WHERE UserID = @UserID AND ThreadID = @ThreadID AND SettingsID = @SettingsID)
		RETURN
END

-- Create a temp table to store the select results
CREATE TABLE #PageIndex 
(
	IndexID int IDENTITY (1, 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 the individual posts
SELECT
	P.PostID, P.ThreadID, P.ParentID, P.PostAuthor, P.UserID, P.SectionID, P.PostLevel, P.SortOrder, P.Subject, P.PostDate, P.IsApproved,
	P.IsLocked, P.IsIndexed, P.TotalViews, P.Body, P.FormattedBody, P.IPAddress, P.PostType, P.PostMedia, P.EmoticonID, P.SettingsID, P.AggViews,
	P.PropertyNames as PostPropertyNames, P.PropertyValues as PostPropertyValues,
	P.PostConfiguration, P.UserTime, P.ApplicationPostType, P.PostName, P.PostStatus, P.SpamScore,
	P.Points as PostPoints, P.RatingSum as PostRatingSum, P.TotalRatings as PostTotalRatings,
	T.*, U.*, #PageIndex.*,
	T.IsLocked,
	T.IsSticky,
	Username = P.PostAuthor,
	ThreadStarterAuthor = T.PostAuthor,
	ThreadStartDate = T.PostDate,	
	EditNotes = (SELECT EditNotes FROM cs_PostEditNotes WHERE PostID = P.PostID),
	AttachmentFilename = ISNULL ( (SELECT [FileName] FROM cs_PostAttachments WHERE PostID = P.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
	HasRead = 0 -- not used
FROM 
	cs_Posts P (nolock),
	cs_Threads T,
	cs_vw_Users_FullUser U,
	#PageIndex
WHERE 
	P.PostID = #PageIndex.PostID AND
	P.UserID = U.cs_UserID AND
	T.ThreadID = P.ThreadID AND
	#PageIndex.IndexID > @PageLowerBound AND
	#PageIndex.IndexID < @PageUpperBound and U.SettingsID = @SettingsID
ORDER BY
	IndexID
END

IF @IncludeCategories = 1
BEGIN
	SELECT 
		Cats.[Name], jP.PostID
	FROM 
		#PageIndex jPI
		JOIN cs_Posts jP ON jPI.PostID = jP.PostID
		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.SettingsID = @SettingsID
End

IF @ReturnRecordCount = 1
  SELECT count(PostID) FROM cs_Posts (nolock) WHERE (IsApproved = 1 OR 1 = @AllowUnapproved) AND ThreadID = @ThreadID  and SettingsID = @SettingsID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[cs_forums_Posts_PostSet]  TO [public]
GO