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


CREATE procedure [dbo].cs_system_DeletePostAndChildren
(
	@PostID int,
	@DeleteChildren bit = 1,
	@SettingsID int,
	@DeletedBy INT,
	@Reason NVARCHAR(1024) = '',
	@NewThreadID int = NULL OUT,
	@NewPostLevel int = NULL
)
AS
-- Posts are not "deleted", but they are moved to SectionID=4.
-- Start deleting with @NewPostLevel being NULL

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
------------------------------------------------------------------------------------------

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

IF (@OldPostLevel = 1)
BEGIN		
	-- We may "move" the whole thread. So update and delete some records.
	------------------------------------------------------------------------------------------
	-- Posts related updates								
	UPDATE cs_PostAttachments SET SectionID = @DeletedSectionID WHERE PostID IN (SELECT PostID FROM cs_Posts WHERE ThreadID = @OldThreadID AND SettingsID = @SettingsID)

	UPDATE cs_Posts SET SectionID = @DeletedSectionID WHERE ThreadID = @OldThreadID AND SettingsID = @SettingsID

	-- Threads related updates
	UPDATE cs_ThreadsRead SET SectionID = @DeletedSectionID WHERE ThreadID = @OldThreadID AND SettingsID = @SettingsID

	DELETE FROM cs_TrackedThreads WHERE ThreadID = @OldThreadID AND SettingsID = @SettingsID

	UPDATE cs_Threads SET SectionID = @DeletedSectionID WHERE ThreadID = @OldThreadID AND SettingsID = @SettingsID

	-- Delete from the search index
	DELETE cs_SearchBarrel WHERE ThreadID = @OldThreadID AND SettingsID = @SettingsID
	
	EXEC cs_system_ModerationAction_AuditEntry 4, @DeletedBy, @PostID, null, null, @SettingsID, @Reason
		
	RETURN 1
	------------------------------------------------------------------------------------------
END
ELSE
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
	------------------------------------------------------------------------------------------
		
	-- Calculate Post Level, Parent ID & Sort Order values
	------------------------------------------------------------------------------------------
	IF (@NewPostLevel IS NULL)
	BEGIN
		-- We have an unknown post level, so we have to figure out its values
		IF (NOT EXISTS(SELECT PostID FROM cs_Posts WHERE ThreadID = @NewThreadID AND SettingsID = @SettingsID AND SectionID = @DeletedSectionID))
		BEGIN
			-- Set this post as a tread starter post
			SET @NewPostLevel = 1					
			SET @ParentID = @PostID
			SET @SortOrder = 1
		END
		ELSE
		BEGIN				
			-- Bind this post to the thread starter post
			SET @NewPostLevel = 2
			SET @SortOrder = (SELECT MAX(SortOrder) + 1 FROM cs_Posts WHERE ThreadID = @NewThreadID AND SettingsID = @SettingsID AND SectionID = @DeletedSectionID)
		END
		
		-- Set children's post level
		SET @PostLevel = (@NewPostLevel + 1)
	END
	ELSE
	BEGIN
		--SET @PostLevel = 2
		SET @PostLevel = (@NewPostLevel + 1)
		SET @SortOrder = (SELECT MAX(SortOrder) + 1 FROM cs_Posts WHERE ThreadID = @NewThreadID AND SettingsID = @SettingsID AND SectionID = @DeletedSectionID)
	END
	------------------------------------------------------------------------------------------

	-- First delete this post which is a parent post and should have its children removed
	------------------------------------------------------------------------------------------
	-- Posts related updates								
	UPDATE cs_PostAttachments SET SectionID = @DeletedSectionID WHERE PostID = @PostID
		
	UPDATE 
		cs_Posts 
	SET 
		SectionID = @DeletedSectionID, 
		PostLevel = @NewPostLevel, 
		ThreadID = @NewThreadID,
		ParentID = @ParentID,
		SortOrder = @SortOrder
	WHERE 
		PostID = @PostID AND 
		SettingsID = @SettingsID
		
	-- TODO: Threads related updates?!
		
	-- 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 we allowed to delete children and there are some?
	------------------------------------------------------------------------------------------
	IF (@DeleteChildren = 1 AND @HasReplies > 0)
	BEGIN
		DECLARE @CurrentPostID INT

		-- Delete replying posts through a cursor loop
		DECLARE Posts_Cursor CURSOR LOCAL FORWARD_ONLY FOR
		SELECT 
			PostID 
		FROM
			cs_Posts
		WHERE
			ParentID = @PostID AND
			ThreadID = @OldThreadID AND
			SectionID = @SectionID AND
			SettingsID = @SettingsID
	
		OPEN Posts_Cursor
			
		FETCH NEXT FROM Posts_Cursor INTO @CurrentPostID
	
		WHILE (@@FETCH_STATUS = 0)
		BEGIN
			-- Recursively call
			EXEC cs_system_DeletePostAndChildren @CurrentPostID, @DeleteChildren, @SettingsID, @DeletedBy, @Reason, @NewThreadID OUT, @PostLevel
	
			FETCH NEXT FROM Posts_Cursor INTO @CurrentPostID
		END
	
		CLOSE Posts_Cursor
	
		DEALLOCATE Posts_Cursor		
	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_DeletePostAndChildren] to public
go