cs_weblog_postset.prc

community server 源码

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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





CREATE PROCEDURE dbo.cs_weblog_PostSet
(
	@SectionID int,
	@PostID	int,
	@PostName nvarchar(512) = null,
	@PageIndex int,
	@PageSize int,
	@UserID int,
	@ReturnFullThread bit,
	@IncludeCategories bit,
	@TotalRecords int output
)
AS

BEGIN

SET Transaction Isolation Level Read UNCOMMITTED

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


-- Get the ThreadID
if(@PostName is null)
Begin
	SELECT
		@ThreadID = ThreadID
	FROM 
		cs_Posts
	WHERE 
		PostID = @PostID and SectionID = @SectionID
End
Else
Begin
	SELECT
		@ThreadID = P.ThreadID, @PostID = P.PostID
	FROM 
		cs_Posts P
	WHERE 
		P.SectionID = @SectionID and P.PostName = @PostName

End

--Now we need the main post
	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.PostPropertyNames, P.PostPropertyValues, P.PostConfiguration,
		T.PostAuthor as ThreadPostAuthor,  T.UserID as ThreadUserID,
		T.PostDate as ThreadPostDate, T.ThreadDate, 
		T.LastViewedDate, T.StickyDate, 
		T.TotalViews, --??
		T.TotalReplies, T.TotalReplies as Replies, 
		T.MostRecentPostAuthorID, T.MostRecentPostAuthor, T.MostRecentPostID, 
		T.IsLocked as ThreadIsLocked, 
		T.IsSticky, T.IsApproved as ThreadIsApproved, 
		T.RatingSum, T.TotalRatings, T.ThreadEmoticonID, T.ThreadStatus, 
		P.PostName, P.UserTime,P.ApplicationPostType, P.Points as PostPoints,
		P.PostAuthor as Username,
		EditNotes = null,
		AttachmentFilename,ContentType, IsRemote, FriendlyFileName, ContentSize,[FileName], p.Created, p.Height, p.Width,
		IsModerator = 0,
		HasRead = 0 -- not used,
		, P.PostStatus, P.SpamScore
	FROM 
		cs_vw_PostsWithAttachmentDetails P (nolock),
		cs_Threads T
	WHERE 
		P.PostID = @PostID AND
		T.ThreadID = P.ThreadID



--Do we want/need categories
	IF @IncludeCategories = 1
	Begin
		Select [Name]
		FROM   cs_Post_Categories PC, cs_Posts_InCategories PIC
		Where  PC.CategoryID = PIC.CategoryID AND PIC.PostID = @PostID
	End


--Are we going to honor paging?
if @ReturnFullThread = 0
Begin
	-- First set the rowcount
	DECLARE @RowsToReturn int
	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)
	SELECT P.PostID 
	FROM cs_Posts P (nolock)
	WHERE P.IsApproved = 1 AND P.ThreadID = @ThreadID AND P.PostLevel > 1 and P.ApplicationPostType <> 16 -- Do not display Personal Comments (private)
	ORDER BY PostDate

	Set @TotalRecords = @@rowcount

	SET ROWCOUNT @RowsToReturn

	-- 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.PostName, P.UserTime,
		P.Points as PostPoints,
		T.PostAuthor as ThreadPostAuthor,  T.UserID as ThreadUserID,
		T.PostDate as ThreadPostDate, T.ThreadDate, 
		T.LastViewedDate, T.StickyDate, 
		T.TotalViews, --??
		T.TotalReplies, 
		T.MostRecentPostAuthorID, T.MostRecentPostAuthor, T.MostRecentPostID, 
		T.IsLocked as ThreadIsLocked, 
		T.IsSticky, T.IsApproved as ThreadIsApproved, 
		T.RatingSum, T.TotalRatings, T.ThreadEmoticonID, T.ThreadStatus, 
		#PageIndex.*,
		P.PostAuthor as Username,
		EditNotes = null, 
		AttachmentFilename = null,
		Replies = 0,
		IsModerator = 0, 
		HasRead = 0 -- not used
		, P.PostStatus, P.SpamScore
	FROM 
		cs_Posts P (nolock),
		cs_Threads T,
		#PageIndex
	WHERE 
		P.PostID = #PageIndex.PostID AND
		T.ThreadID = P.ThreadID AND
		#PageIndex.IndexID > @PageLowerBound AND
		#PageIndex.IndexID < @PageUpperBound
	ORDER BY
		IndexID

	--SELECT @TotalRecords = count(*) FROM #PageIndex  

	DROP TABLE #PageIndex
END
ELSE --No Paging, just get all of the comments/trackbacks
BEGIN
	-- 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.PostName, P.ApplicationPostType, P.UserTime,
		P.Points as PostPoints, P.PostStatus, P.SpamScore,
		T.PostAuthor as ThreadPostAuthor,  T.UserID as ThreadUserID,
		T.PostDate as ThreadPostDate, T.ThreadDate, 
		T.LastViewedDate, T.StickyDate, 
		T.TotalViews, --??
		T.TotalReplies, 
		T.MostRecentPostAuthorID, T.MostRecentPostAuthor, T.MostRecentPostID, 
		T.IsLocked as ThreadIsLocked, 
		T.IsSticky, T.IsApproved as ThreadIsApproved, 
		T.RatingSum, T.TotalRatings, T.ThreadEmoticonID, T.ThreadStatus, 
		P.PostAuthor as Username,
		EditNotes = null, 
		AttachmentFilename = null,
		Replies = 0, 
		IsModerator = 0, 
		HasRead = 0 -- not used
	FROM 
		cs_Posts P,
		cs_Threads t
	WHERE 
		P.ThreadID = t.ThreadID AND
		
		t.ThreadID = @ThreadID AND P.PostLevel > 1 AND P.IsApproved = 1 
		AND P.PostType <> 16 -- Do not display Personal Comments (private)
	ORDER BY
		P.PostID

	SELECT @TotalRecords = Count(P.PostID) FROM cs_Posts P (nolock) WHERE P.IsApproved = 1 AND P.ThreadID = @ThreadID AND P.PostLevel <> 1 and P.PostType <> 16

END

END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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