cs_system_deletepostandadoptchildren.prc

community server 源码

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


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


CREATE procedure [dbo].cs_system_DeletePostAndAdoptChildren
(
	@PostID int,
	@SettingsID int,
	@DeletedBy INT,
	@Reason NVARCHAR(1024) = '',
	@NewThreadID int = NULL OUT
)
AS
-- Deleted Post is really moved to "Deleted Posts" forum (ForumType = 50)

SET Transaction Isolation Level Read UNCOMMITTED
BEGIN

DECLARE @OldThreadID INT
DECLARE @UserID INT
DECLARE @PostAuthor NVARCHAR(64)
DECLARE @SectionID INT
DECLARE @ParentID INT
DECLARE @IsApproved BIT
DECLARE @PostDate DATETIME
DECLARE @EmoticonID INT
DECLARE @IsLocked INT
DECLARE @DeletedSectionID INT
DECLARE @HasReplies INT
DECLARE @OldPostLevel INT
DECLARE @PostLevel INT
DECLARE @SortOrder INT

-- First, get information about the post that is about to be deleted.
------------------------------------------------------------------------------------------
SELECT @DeletedSectionID = SectionID FROM cs_Sections WHERE SettingsID = @SettingsID and ForumType = 50

SET @HasReplies = (SELECT COUNT(*) FROM cs_Posts WHERE ParentID = @PostID AND SettingsID = @SettingsID)

SELECT
	@OldThreadID = ThreadID,
	@OldPostLevel = PostLevel,
	@UserID = UserID,
	@PostAuthor = PostAuthor,
	@ParentID = ParentID,
	@SectionID = SectionID,
	@IsLocked = IsLocked,
	@IsApproved = IsApproved,
	@PostDate = PostDate,
	@EmoticonID = EmoticonID
FROM
	cs_Posts
WHERE
	PostID = @PostID AND 
	SettingsID = @SettingsID
------------------------------------------------------------------------------------------
print @parentid

-- Stop here if the post is not approved
IF (@IsApproved = 0)
BEGIN
	RETURN 1
END

-- This proc should only be called from non-thread starting posts
IF (@OldPostLevel <> 1)
BEGIN
	-- We create a new thread here because we won't move the whole thread.
	-- The new thread will clone current thread.
	------------------------------------------------------------------------------------------
	IF (@NewThreadID IS NULL)
	BEGIN
		INSERT cs_Threads
		(
			SectionID,
			PostDate,
			UserID,
			PostAuthor,
			ThreadDate,
			MostRecentPostAuthor,
			MostRecentPostAuthorID,
			MostRecentPostID,
			IsLocked,
			IsApproved,
			IsSticky,
			StickyDate,
			ThreadEmoticonID,
			SettingsID
		)
		VALUES
		(
			@DeletedSectionID,	-- the Deleted Posts forum
			@PostDate,
			@UserID,
			@PostAuthor,
			@PostDate,
			@PostAuthor,
			@UserID,
			@PostID,	-- MostRecentPostID, which we don't know until after post INSERT below.
			@IsLocked,
			@IsApproved,
			0,	-- Downgrade the thread to a non-sticky.
			@PostDate,
			@EmoticonID,
			@SettingsID
		)
		
		-- Get the new ThreadID
		SET @NewThreadID = @@IDENTITY
	END

	-- First delete this post which is a parent post and should have its children adopted
	------------------------------------------------------------------------------------------
	-- Posts related updates
	UPDATE cs_PostAttachments SET SectionID = @DeletedSectionID WHERE PostID = @PostID

	UPDATE
		cs_Posts
	SET
		SectionID = @DeletedSectionID,
		PostLevel = 1,
		ThreadID = @NewThreadID,
		ParentID = @PostID,
		SortOrder = 1
	WHERE
		PostID = @PostID AND
		SettingsID = @SettingsID

	-- Update the search index
	UPDATE cs_SearchBarrel SET ThreadID = @NewThreadID WHERE PostID = @PostID AND ThreadID = @OldThreadID AND SettingsID = @SettingsID

	-- Update Moderation Audit table
	IF (@Reason IS NULL OR @Reason = '')
		SET @Reason = 'Automatic generated reason: the post has been deleted on request.'

	EXEC cs_system_ModerationAction_AuditEntry 4, @DeletedBy, @PostID, null, null, @SettingsID, @Reason
	------------------------------------------------------------------------------------------

	-- Are there children to be adopted?
	------------------------------------------------------------------------------------------
	IF (@HasReplies > 0)
	BEGIN

print 'adopt children'
		-- Adopt children (by grandparent)
		UPDATE	cs_Posts
		SET		ParentID = @ParentID,
				PostLevel = @OldPostLevel
		WHERE	ParentID = @PostID AND
				ThreadID = @OldThreadID AND
				SectionID = @SectionID AND
				SettingsID = @SettingsID

		-- Get max post level (used for looping)
		DECLARE	@MaxPostLevel	INT
		SELECT	@MaxPostLevel = MAX(PostLevel)
		FROM	cs_Posts
		WHERE	ThreadID = @OldThreadID AND
				SectionID = @SectionID AND
				SettingsID = @SettingsID

		DECLARE	@level	int
		SET		@level = @OldPostLevel

		-- loop thru posting levels and recalculate (should just be a decrement)
		WHILE (@level <= @MaxPostLevel)
		BEGIN
			print 'reset levels'
			UPDATE	P
			SET		P.PostLevel = PP.PostLevel + 1
			FROM	cs_Posts P INNER JOIN cs_Posts PP ON P.ParentID = PP.PostID
			WHERE	P.PostLevel = @level AND
					P.ThreadID = @OldThreadID AND
					P.SectionID = @SectionID AND
					P.SettingsID = @SettingsID

			SET	@level = @level + 1
		END
	END
	------------------------------------------------------------------------------------------

	RETURN 1
END	

END
		
/*
	-- Decrease the TotalPosts on the user's profile.
	IF (SELECT EnablePostStatistics FROM cs_Sections WHERE SectionID = @SectionID and SettingsID = @SettingsID) = 1
		UPDATE 
			cs_UserProfile
		SET 
			TotalPosts = ISNULL(TotalPosts - (SELECT COUNT(PostID) FROM cs_Posts WHERE ThreadID = @ThreadID and SettingsID = @SettingsID), 0)
		WHERE 
			UserID = @UserID and SettingsID = @SettingsID

*/

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


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