cs_forums_post.prc

community server 源码

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE dbo.cs_forums_Post
(
	@PostID int,
	@UserID int,
	@TrackViews bit,
	@SettingsID int,
	@MarkRead bit = 0,
	@IncludeCategories bit = 0
) 
AS
/*
	Procedure for getting basic information on a single post.
*/
SET Transaction Isolation Level Read UNCOMMITTED

DECLARE @NextThreadID int
DECLARE @PrevThreadID int
DECLARE @ThreadID int 
DECLARE @SectionID int
DECLARE @SortOrder int
DECLARE @IsApproved bit
DECLARE @IsAnonymousUser bit

SELECT 
	@ThreadID = ThreadID, 
	@SectionID = SectionID, 
	@SortOrder=SortOrder,
	@IsApproved = IsApproved
FROM 
	cs_Posts (nolock) 
WHERE 
	PostID = @PostID and SettingsID = @SettingsID

-- Do we have an anonymous user?
SET @IsAnonymousUser = (SELECT IsAnonymous FROM cs_vw_Users_FullUser WHERE cs_UserID = @UserID 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)
		RETURN
END

DECLARE @TrackingThread bit

IF @TrackViews = 1
BEGIN
	-- Update the counter for the number of times this post is viewed
	UPDATE cs_Posts SET TotalViews = (TotalViews + 1) WHERE PostID = @PostID and SettingsID = @SettingsID
	UPDATE cs_Threads SET TotalViews = (TotalViews + 1) WHERE ThreadID = @ThreadID and SettingsID = @SettingsID
END

-- Mark the post as read if this user is not anonymous
IF @MarkRead = 1 AND @IsAnonymousUser = 0 AND @IsApproved = 1
BEGIN
	IF NOT EXISTS (SELECT ThreadID FROM cs_ThreadsRead WHERE ThreadID = @ThreadID AND UserID = @UserID and SettingsID = @SettingsID and SectionID = @SectionID)
		INSERT INTO cs_ThreadsRead (UserID, ThreadID, SectionID, SettingsID) VALUES (@UserID, @ThreadID, @SectionID, @SettingsID)
END

/*
-- get the anonymous user id for this site
if( @UserID = 0 ) 
BEGIN
	exec cs_GetAnonymousUserID @SettingsID, @UserID output
END


IF EXISTS(SELECT ThreadID FROM cs_TrackedThreads (nolock) WHERE ThreadID = @ThreadID AND UserID=@UserID)
	SELECT @TrackingThread = 1
ELSE
	SELECT @TrackingThread = 0
*/

-- Get tracking thread information
IF (@IsAnonymousUser = 0) AND EXISTS(SELECT ThreadID FROM cs_TrackedThreads WHERE ThreadID = @ThreadID AND UserID=@UserID)
	SET @TrackingThread = 1
ELSE
	SET @TrackingThread = 0

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 as PostPropertyNames, P.PostPropertyValues 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,
	U.*, P.PostAuthor as [Username],
	T.ThreadDate,
	T.StickyDate,
	T.IsLocked,
	T.IsSticky,
	T.RatingSum,
	T.TotalRatings,
	HasRead = 0,
	EditNotes = (SELECT EditNotes FROM cs_PostEditNotes WHERE PostID = P.PostID),
	IndexInThread = (SELECT Count(PostID) FROM cs_Posts P1 WHERE IsApproved = 1 AND ThreadID = @ThreadID AND SortOrder <= (SELECT SortOrder FROM cs_Posts where PostID = @PostID)),
	AttachmentFilename,ContentType, IsRemote, FriendlyFileName, ContentSize, [FileName],p.Created, Height, Width,
--	AttachmentFilename = ISNULL ( (SELECT [FileName] FROM cs_PostAttachments WHERE PostID = P.PostID), ''),
	IsModerator = (SELECT Count(*) FROM cs_Moderators WHERE UserID = U.cs_UserID),
	Replies = (SELECT COUNT(*) FROM cs_Posts P2 (nolock) WHERE P2.ParentID = P.PostID AND P2.PostLevel != 1),
	PrevThreadID = 0,
	NextThreadID = 0,
	UserIsTrackingThread = @TrackingThread
FROM 
	cs_vw_PostsWithAttachmentDetails P,
	--cs_Posts P,
	cs_Threads T,
	cs_vw_Users_FullUser U
WHERE 
	P.PostID = @PostID AND
	P.ThreadID = T.ThreadID AND
	P.UserID = U.cs_UserID and P.SettingsID = @SettingsID and U.SettingsID = @SettingsID


IF @IncludeCategories = 1
BEGIN
	SELECT 
		Cats.[Name]
	FROM 
		cs_Posts_InCategories PIC
		JOIN cs_Post_Categories Cats ON PIC.CategoryID = Cats.CategoryID
	WHERE 
		PIC.PostID = @PostID
End
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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