cs_system_updateforum.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_system_UpdateForum]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_system_UpdateForum]
GO


CREATE PROCEDURE [dbo].cs_system_UpdateForum
(
	@SectionID int,
	@ThreadID int,
	@PostID int,
	@SettingsID int,
	@UpdateThreadDate bit = 1, -- new param with a default value,
	@PostDisplayName nvarchar(64) = null
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN

DECLARE @UserID 		int
DECLARE @PostDate 		datetime
DECLARE @TotalThreads 		int
DECLARE @TotalPosts 		int
DECLARE @PostsToModerate	int
DECLARE @Subject		nvarchar(64)
DECLARE @User 		nvarchar(64)
DECLARE @PostConfiguration	int
DECLARE @IsApproved bit


-- Set default
SET @PostConfiguration = 0

-- Get values necessary to update the forum statistics
SELECT
	@IsApproved = P.IsApproved,
	@UserID = P.UserID,
	@PostDate = PostDate,
	@TotalPosts = (SELECT COUNT(PostID) FROM cs_Posts WHERE SectionID = @SectionID AND IsApproved = 1 and SettingsID = @SettingsID),
	@TotalThreads = (SELECT COUNT(P2.PostID) FROM cs_Posts P2 (nolock) WHERE P2.SectionID = @SectionID AND P2.IsApproved=1 AND P2.PostLevel=1 and P2.SettingsID = @SettingsID),
	@PostsToModerate = (SELECT Count(PostID) FROM cs_Posts WHERE SectionID = @SectionID AND IsApproved = 0 and SettingsID = @SettingsID),
	@Subject = P.Subject,
	@User = PostAuthor,
	@PostConfiguration = P.PostConfiguration
FROM
	cs_Posts P
WHERE
	PostID = @PostID and P.SettingsID = @SettingsID and P.SectionID = @SectionID

-- Is anonymous post?
IF (@PostConfiguration & 1) = 1
BEGIN
	-- Set anonymous info instead real user info
	SELECT @UserID = cs_UserID FROM cs_vw_Users_FullUser WHERE SettingsID = @SettingsID and IsAnonymous = 1	
	SET @User = null
	SET @PostDisplayName = null
	
END

IF (@PostConfiguration & 2) <> 2 -- Check to make sure that the post is not flagged as ignored. 
BEGIN 
	-- We need to separate the process of updating a thread info into 2 steps:
	-- 1. first update the strings and counters
	-- 2. update MostRecentPostDate separatelly because there are situations when 
	-- we need to update info but not the date. Eg: anonymous user posting - when
	-- post's owner choose to become visible on editing its post.

	-- Always update counters
	UPDATE 
		cs_Sections
	SET
		TotalPosts = isnull(@TotalPosts,0),
		TotalThreads = isnull(@TotalThreads,0),
		PostsToModerate = isnull(@PostsToModerate,0)
	WHERE
		SectionID = @SectionID and SettingsID = @SettingsID

	-- Only update Post-specific info if the current post is approved
	IF @IsApproved = 1
	BEGIN
		UPDATE 
			cs_Sections
		SET
			MostRecentPostID = isnull(@PostID,0),
			MostRecentThreadID = isnull(@ThreadID,0),
			MostRecentPostAuthorID = isnull(@UserID,0),
			MostRecentPostSubject = isnull(@Subject,''),
			MostRecentPostAuthor = isnull(@PostDisplayName,isnull(@User,'')),
			MostRecentThreadReplies = ISNULL((SELECT TotalReplies FROM cs_Threads WHERE ThreadID = @ThreadID), 0)
		WHERE
			SectionID = @SectionID and SettingsID = @SettingsID
	
		IF @UpdateThreadDate = 1
			UPDATE 
				cs_Sections
			SET
				MostRecentPostDate = isnull(@PostDate,'1/01/1797')
			WHERE
				SectionID = @SectionID and SettingsID = @SettingsID
	
		-- UPDATE Parent
		DECLARE @ParentID int
		SELECT @ParentID = ParentID FROM cs_Sections where SectionID = @SectionID
	
		WHILE @ParentID > 0
		BEGIN
			UPDATE 
				cs_Sections
			SET
				MostRecentPostID = isnull(@PostID,0),
				MostRecentThreadID = isnull(@ThreadID,0),
				MostRecentPostAuthorID = isnull(@UserID,0),
				MostRecentPostSubject = isnull(@Subject,''),
				MostRecentPostAuthor = isnull(@User,''),
				MostRecentThreadReplies = ISNULL((SELECT TotalReplies FROM cs_Threads WHERE ThreadID = @ThreadID), 0)
			WHERE
				SectionID = @ParentID and SettingsID = @SettingsID
	
			-- Get the next ParentID
			SELECT @ParentID = ParentID FROM cs_Sections where SectionID = @ParentID
		END
	
	END

END

END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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