SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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



CREATE    PROCEDURE [dbo].cs_Moderate_Post_Move
(
    @PostID int,
    @MoveToSectionID int,
    @MovedBy int,
    @SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
DECLARE @ThreadID INT
DECLARE @SectionID INT
DECLARE @IsApproved BIT
DECLARE @PostLevel INT
DECLARE @Notes NVARCHAR(1024)

-- First, get information about the post that is about to be moved.
SELECT
	@ThreadID = ThreadID,
	@SectionID = SectionID,
	@PostLevel = PostLevel,
	@IsApproved = IsApproved
FROM
	cs_Posts
WHERE
	PostID = @PostID and SettingsID = @SettingsID

-- EAD: We only move the post if it is a top level post.
IF @PostLevel = 1
BEGIN
	DECLARE @MoveToIsModerated SMALLINT
	
	-- Get information about the destination forum.
	SELECT 
		@MoveToIsModerated = IsModerated
	FROM 
		cs_Sections
	WHERE 
		SectionID = @MoveToSectionID and SettingsID = @SettingsID
	
	-- If the post is not already approved, check the moderation status and permissions on the moderator for approved status.
	IF @IsApproved = 0
	BEGIN
		-- If the destination forum requires moderation, make sure the moderator has permission.
		IF @MoveToIsModerated = 1
		BEGIN
			IF EXISTS(
				SELECT
					A.UserId
				FROM
					aspnet_UsersInRoles A, cs_UserProfile U
				WHERE
					U.MembershipID = A.UserId and
					U.UserID = @MovedBy  and U.SettingsID = @SettingsID
					AND A.RoleId IN (
						SELECT 
							RoleID 
						FROM 
							cs_SectionPermissions
						WHERE 
							SectionID = @SectionID
							AND AllowMask & convert(bigint,0x0000100000000000) = convert(bigint,0x0000100000000000)  and SettingsID = @SettingsID
					)
			)
			BEGIN
				-- The moderator has permissions to move the post and approve it.		
				UPDATE
					cs_Posts
				SET 
					SectionID = @MoveToSectionID
				WHERE 
					ThreadID = @ThreadID and SettingsID = @SettingsID

				UPDATE
					cs_PostAttachments
				SET
					SectionID = @MoveToSectionID
				WHERE
					PostID IN (SELECT PostID FROM cs_Posts WHERE ThreadID = @ThreadID AND SettingsID = @SettingsID)

				UPDATE
					cs_Threads
				SET 
					SectionID = @MoveToSectionID
				WHERE 
					ThreadID = @ThreadID and SettingsID = @SettingsID

				-- approve the post
				EXEC cs_Moderate_ApprovePost @PostID, @MovedBy, @SettingsID
				
				SET @Notes = 'The post was moved and approved.'
				PRINT @Notes
				SELECT 2
			END
			ELSE BEGIN
				-- The moderator has permissions to move the post but not approve.			
				UPDATE
					cs_Posts
				SET 
					SectionID = @MoveToSectionID
				WHERE 
					ThreadID = @ThreadID and SettingsID = @SettingsID

				UPDATE
					cs_PostAttachments
				SET
					SectionID = @MoveToSectionID
				WHERE
					PostID IN (SELECT PostID FROM cs_Posts WHERE ThreadID = @ThreadID AND SettingsID = @SettingsID)

				UPDATE
					cs_Threads
				SET 
					SectionID = @MoveToSectionID
				WHERE 
					ThreadID = @ThreadID and SettingsID = @SettingsID
				
				SET @Notes = 'The post was moved but not approved.'
				PRINT @Notes
				SELECT 1
			END
		END
		ELSE BEGIN
			UPDATE
				cs_Posts
			SET 
				SectionID = @MoveToSectionID
			WHERE 
				ThreadID = @ThreadID and SettingsID = @SettingsID

			UPDATE
				cs_PostAttachments
			SET
				SectionID = @MoveToSectionID
			WHERE
				PostID IN (SELECT PostID FROM cs_Posts WHERE ThreadID = @ThreadID AND SettingsID = @SettingsID)

			UPDATE
				cs_Threads
			SET 
				SectionID = @MoveToSectionID
			WHERE 
				ThreadID = @ThreadID and SettingsID = @SettingsID

			-- The destination forum is not moderated, approve the post and move the post.
			EXEC cs_Moderate_ApprovePost @PostID, @MovedBy, @SettingsID
			
			SET @Notes = 'The post was moved and approved.'
			PRINT @Notes
			SELECT 2
		END
	END
	ELSE BEGIN
		-- The post is already approved, move the post.
		UPDATE
			cs_Posts
		SET 
			SectionID = @MoveToSectionID
		WHERE 
			ThreadID = @ThreadID and SettingsID = @SettingsID

		UPDATE
			cs_PostAttachments
		SET
			SectionID = @MoveToSectionID
		WHERE
			PostID IN (SELECT PostID FROM cs_Posts WHERE ThreadID = @ThreadID AND SettingsID = @SettingsID)

		UPDATE
			cs_Threads
		SET 
			SectionID = @MoveToSectionID
		WHERE 
			ThreadID = @ThreadID and SettingsID = @SettingsID
		
		print 'The approved post was moved.'
		SET @Notes = 'The approved post was moved.'
		SELECT 3
	
	END

	-- Reset the statistics on both forums.
	EXEC cs_system_ResetForumStatistics @SectionID
	EXEC cs_system_ResetForumStatistics @MoveToSectionID
	
	-- Reset the thread statistics on the moved thread.
	EXEC cs_system_ResetThreadStatistics @ThreadID
		
	-- Update Moderation Audit table
	EXEC cs_system_ModerationAction_AuditEntry 3, @MovedBy, @PostID, null, @SectionID, @SettingsID, @Notes

END
ELSE BEGIN
	print 'The post was not moved.'
	SELECT 0
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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