cs_Moderate_DeletePost
ALTER PROCEDURE [dbo].cs_Moderate_DeletePost
(
@PostID INT,
@DeletedBy INT,
@Reason NVARCHAR(1024) = '',
@DeleteChildPosts BIT = 1,
@SettingsID int
)
AS
-- Deletes the post
-- 删除回帖
SET Transaction Isolation Level Read UNCOMMITTED
/**//*设置事务隔离级别,Read UNCOMMITTED 执行脏读或 0 级隔离锁定,这表示不发出共享锁,
也不接受排它锁。当设置该选项时,可以对数据执行未提交读或脏读;在事务结束前可以更
改数据内的数值,行也可以出现在数据集中或从数据集消失。该选项的作用与在事务内所有
语句中的所有表上设置 NOLOCK 相同。这是四个隔离级别中限制最小的级别。*/
DECLARE @IsApproved bit
-- First, get information about the post that is about to be deleted.
-- 首先,获取要删除的帖子的信息
SELECT
@IsApproved = IsApproved
FROM
cs_Posts
WHERE
PostID = @PostID and SettingsID = @SettingsID
-- If the post is not approved, permanently delete the post
-- 如果帖子是未批准的,则永久删除
IF (@IsApproved = 0)
BEGIN
-- Delete the post.
-- 删除帖子
DELETE FROM cs_Posts WHERE PostID = @PostID 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
END
ELSE
--如果帖子是被核准的
BEGIN
DECLARE @DeletedSectionID INT
DECLARE @SectionID INT
DECLARE @OldThreadID INT
DECLARE @NewThreadID INT
-- Init data
-- 初始数据
SELECT @DeletedSectionID = SectionID FROM cs_Sections WHERE SettingsID = @SettingsID and ForumType = 50
SELECT @OldThreadID = ThreadID, @SectionID = SectionID FROM cs_Posts WHERE SettingsID = @SettingsID AND PostID = @PostID
SET @NewThreadID = NULL
-- Is this a private message post (@SectionID = 0)?
-- 如果SectionID=0则为个人消息,
IF (@SectionID = 0)
BEGIN
-- Permanently delete the post and its replies
-- 永久删除帖子和他的回复
EXEC cs_Post_Delete @SectionID, @SettingsID, @PostID, 0, @DeletedBy, @Reason
-- Statistics update
-- 状态更新
EXEC cs_system_ResetThreadStatistics @OldThreadID
END
ELSE -- (@SectionID <> 0)
BEGIN
IF (@DeleteChildPosts = 1)
-- Move the post and its replies to 'Deleted Posts' forum
-- 移动帖子和评论到一张特定的表中
EXEC cs_system_DeletePostAndChildren @PostID, @DeleteChildPosts, @SettingsID, @DeletedBy, @Reason, @NewThreadID OUT, NULL
ELSE -- (@DeleteChildPosts <> 1)
-- Move the post to 'Deleted Posts' forum, reassign children to deleted post's parent
-- 移动帖子到一张特定表中,再指定删除和其有关系的(不知道什么东西)
EXEC cs_system_DeletePostAndAdoptChildren @PostID, @SettingsID, @DeletedBy, @Reason, @NewThreadID OUTPUT
-- Statistics update
-- 状态更新
EXEC cs_system_ResetThreadStatistics @OldThreadID
EXEC cs_system_ResetThreadStatistics @NewThreadID
EXEC cs_system_ResetForumStatistics @SectionID
EXEC cs_system_ResetForumStatistics @DeletedSectionID
END
END
/**//*业务逻辑没弄清楚就是这样*/
ALTER PROCEDURE [dbo].cs_Moderate_DeletePost
(
@PostID INT,
@DeletedBy INT,
@Reason NVARCHAR(1024) = '',
@DeleteChildPosts BIT = 1,
@SettingsID int
)
AS
-- Deletes the post
-- 删除回帖
SET Transaction Isolation Level Read UNCOMMITTED
/**//*设置事务隔离级别,Read UNCOMMITTED 执行脏读或 0 级隔离锁定,这表示不发出共享锁,
也不接受排它锁。当设置该选项时,可以对数据执行未提交读或脏读;在事务结束前可以更
改数据内的数值,行也可以出现在数据集中或从数据集消失。该选项的作用与在事务内所有
语句中的所有表上设置 NOLOCK 相同。这是四个隔离级别中限制最小的级别。*/
DECLARE @IsApproved bit
-- First, get information about the post that is about to be deleted.
-- 首先,获取要删除的帖子的信息
SELECT
@IsApproved = IsApproved
FROM
cs_Posts
WHERE
PostID = @PostID and SettingsID = @SettingsID
-- If the post is not approved, permanently delete the post
-- 如果帖子是未批准的,则永久删除
IF (@IsApproved = 0)
BEGIN
-- Delete the post.
-- 删除帖子
DELETE FROM cs_Posts WHERE PostID = @PostID 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
END
ELSE
--如果帖子是被核准的
BEGIN
DECLARE @DeletedSectionID INT
DECLARE @SectionID INT
DECLARE @OldThreadID INT
DECLARE @NewThreadID INT
-- Init data
-- 初始数据
SELECT @DeletedSectionID = SectionID FROM cs_Sections WHERE SettingsID = @SettingsID and ForumType = 50
SELECT @OldThreadID = ThreadID, @SectionID = SectionID FROM cs_Posts WHERE SettingsID = @SettingsID AND PostID = @PostID
SET @NewThreadID = NULL
-- Is this a private message post (@SectionID = 0)?
-- 如果SectionID=0则为个人消息,
IF (@SectionID = 0)
BEGIN
-- Permanently delete the post and its replies
-- 永久删除帖子和他的回复
EXEC cs_Post_Delete @SectionID, @SettingsID, @PostID, 0, @DeletedBy, @Reason
-- Statistics update
-- 状态更新
EXEC cs_system_ResetThreadStatistics @OldThreadID
END
ELSE -- (@SectionID <> 0)
BEGIN
IF (@DeleteChildPosts = 1)
-- Move the post and its replies to 'Deleted Posts' forum
-- 移动帖子和评论到一张特定的表中
EXEC cs_system_DeletePostAndChildren @PostID, @DeleteChildPosts, @SettingsID, @DeletedBy, @Reason, @NewThreadID OUT, NULL
ELSE -- (@DeleteChildPosts <> 1)
-- Move the post to 'Deleted Posts' forum, reassign children to deleted post's parent
-- 移动帖子到一张特定表中,再指定删除和其有关系的(不知道什么东西)
EXEC cs_system_DeletePostAndAdoptChildren @PostID, @SettingsID, @DeletedBy, @Reason, @NewThreadID OUTPUT
-- Statistics update
-- 状态更新
EXEC cs_system_ResetThreadStatistics @OldThreadID
EXEC cs_system_ResetThreadStatistics @NewThreadID
EXEC cs_system_ResetForumStatistics @SectionID
EXEC cs_system_ResetForumStatistics @DeletedSectionID
END
END
/**//*业务逻辑没弄清楚就是这样*/
cs_Moderate_CheckUser
ALTER procedure [dbo].cs_Moderate_CheckUser
(
@UserID int,
@SectionID int,
@SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
/**//*设置事务隔离级别,Read UNCOMMITTED 执行脏读或 0 级隔离锁定,这表示不发出共享锁,
也不接受排它锁。当设置该选项时,可以对数据执行未提交读或脏读;在事务结束前可以更
改数据内的数值,行也可以出现在数据集中或从数据集消失。该选项的作用与在事务内所有
语句中的所有表上设置 NOLOCK 相同。这是四个隔离级别中限制最小的级别。*/
IF EXISTS(SELECT SectionID FROM cs_Moderators WHERE UserID = @UserID AND SectionID = 0 and SettingsID = @SettingsID)
SELECT 1
ELSE
IF EXISTS (SELECT SectionID FROM cs_Moderators WHERE UserID = @UserID AND SectionID = @SectionID AND SettingsID = @SettingsID)
SELECT 1
ELSE
SELECT 0
ALTER procedure [dbo].cs_Moderate_CheckUser
(
@UserID int,
@SectionID int,
@SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
/**//*设置事务隔离级别,Read UNCOMMITTED 执行脏读或 0 级隔离锁定,这表示不发出共享锁,
也不接受排它锁。当设置该选项时,可以对数据执行未提交读或脏读;在事务结束前可以更
改数据内的数值,行也可以出现在数据集中或从数据集消失。该选项的作用与在事务内所有
语句中的所有表上设置 NOLOCK 相同。这是四个隔离级别中限制最小的级别。*/
IF EXISTS(SELECT SectionID FROM cs_Moderators WHERE UserID = @UserID AND SectionID = 0 and SettingsID = @SettingsID)
SELECT 1
ELSE
IF EXISTS (SELECT SectionID FROM cs_Moderators WHERE UserID = @UserID AND SectionID = @SectionID AND SettingsID = @SettingsID)
SELECT 1
ELSE
SELECT 0
cs_Moderate_ApprovePost
ALTER procedure [dbo].cs_Moderate_ApprovePost
(
@PostID int,
@ApprovedBy int,
@SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
/**//*设置事务隔离级别,Read UNCOMMITTED 执行脏读或 0 级隔离锁定,这表示不发出共享锁,
也不接受排它锁。当设置该选项时,可以对数据执行未提交读或脏读;在事务结束前可以更
改数据内的数值,行也可以出现在数据集中或从数据集消失。该选项的作用与在事务内所有
语句中的所有表上设置 NOLOCK 相同。这是四个隔离级别中限制最小的级别。*/
DECLARE @SectionID int
DECLARE @ThreadID int
DECLARE @PostLevel int
DECLARE @UserID int
DECLARE @IsLocked bit
-- first make sure that the post is ALREADY non-approved
-- 首先确定回帖是否被批准的
IF (SELECT IsApproved FROM cs_Posts (nolock) WHERE PostID = @PostID and SettingsID = @SettingsID) = 1
BEGIN
print 'Post is already approved'
SELECT 0
RETURN
END
ELSE
BEGIN
print 'Post is not approved'
-- Get details about the thread and forum this post belongs in
-- 如果未批准,获取主题资料和该帖子的所属主题
SELECT
@SectionID = SectionID,
@ThreadID = ThreadID,
@PostLevel = PostLevel,
@UserID = UserID,
@IsLocked = IsLocked
FROM
cs_Posts
WHERE
PostID = @PostID and SettingsID = @SettingsID
-- Approve the post
-- 批准回帖
UPDATE
cs_Posts
SET
IsApproved = 1
WHERE
PostID = @PostID and SettingsID = @SettingsID
-- Approved the thread if necessary
-- 如果需要则批准主题可回复(如果是第一楼的话即主题,则可回复)
IF @PostLevel = 1
UPDATE
cs_Threads
SET
IsApproved = 1
WHERE
ThreadID = @ThreadID and SettingsID = @SettingsID
-- Update the user's post count
-- 更新用户回帖数量
exec cs_system_UpdateUserPostCount @SectionID, @UserID, @SettingsID
-- Update the forum statistics
-- 更新评论状态
exec cs_system_UpdateForum @SectionID, @ThreadID, @PostID, @SettingsID
-- Clean up unnecessary columns in forumsread
-- 清空forumsread中不需要的列
exec cs_system_CleanForumsRead @SectionID, @SettingsID
-- update the thread stats
-- 更新主题信息
exec cs_system_UpdateThread @ThreadID, @PostID, @SettingsID
-- Update Moderation audit table
-- 更新操作审核表
-- Update the ModerationAudit table
exec cs_system_ModerationAction_AuditEntry 1, @ApprovedBy, @PostID, null, @SectionID, @SettingsID, null
-- Send back a success code
-- 发送成功信息
SELECT 1
END
ALTER procedure [dbo].cs_Moderate_ApprovePost
(
@PostID int,
@ApprovedBy int,
@SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
/**//*设置事务隔离级别,Read UNCOMMITTED 执行脏读或 0 级隔离锁定,这表示不发出共享锁,
也不接受排它锁。当设置该选项时,可以对数据执行未提交读或脏读;在事务结束前可以更
改数据内的数值,行也可以出现在数据集中或从数据集消失。该选项的作用与在事务内所有
语句中的所有表上设置 NOLOCK 相同。这是四个隔离级别中限制最小的级别。*/
DECLARE @SectionID int
DECLARE @ThreadID int
DECLARE @PostLevel int
DECLARE @UserID int
DECLARE @IsLocked bit
-- first make sure that the post is ALREADY non-approved
-- 首先确定回帖是否被批准的
IF (SELECT IsApproved FROM cs_Posts (nolock) WHERE PostID = @PostID and SettingsID = @SettingsID) = 1
BEGIN
print 'Post is already approved'
SELECT 0
RETURN
END
ELSE
BEGIN
print 'Post is not approved'
-- Get details about the thread and forum this post belongs in
-- 如果未批准,获取主题资料和该帖子的所属主题
SELECT
@SectionID = SectionID,
@ThreadID = ThreadID,
@PostLevel = PostLevel,
@UserID = UserID,
@IsLocked = IsLocked
FROM
cs_Posts
WHERE
PostID = @PostID and SettingsID = @SettingsID
-- Approve the post
-- 批准回帖
UPDATE
cs_Posts
SET
IsApproved = 1
WHERE
PostID = @PostID and SettingsID = @SettingsID
-- Approved the thread if necessary
-- 如果需要则批准主题可回复(如果是第一楼的话即主题,则可回复)
IF @PostLevel = 1
UPDATE
cs_Threads
SET
IsApproved = 1
WHERE
ThreadID = @ThreadID and SettingsID = @SettingsID
-- Update the user's post count
-- 更新用户回帖数量
exec cs_system_UpdateUserPostCount @SectionID, @UserID, @SettingsID
-- Update the forum statistics
-- 更新评论状态
exec cs_system_UpdateForum @SectionID, @ThreadID, @PostID, @SettingsID
-- Clean up unnecessary columns in forumsread
-- 清空forumsread中不需要的列
exec cs_system_CleanForumsRead @SectionID, @SettingsID
-- update the thread stats
-- 更新主题信息
exec cs_system_UpdateThread @ThreadID, @PostID, @SettingsID
-- Update Moderation audit table
-- 更新操作审核表
-- Update the ModerationAudit table
exec cs_system_ModerationAction_AuditEntry 1, @ApprovedBy, @PostID, null, @SectionID, @SettingsID, null
-- Send back a success code
-- 发送成功信息
SELECT 1
END