cs_moderationaudit_user_get.prc

community server 源码

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

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

CREATE PROCEDURE [dbo].cs_ModerationAudit_User_Get
(
	@UserID int,
	@ModerationAction int,
	@PageIndex int,
	@PageSize int,
	@ReturnRecordCount bit,
	@SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED

--
-- IF @UserActionsFilter = 1 then we want to have the following moderation actions for provided UserID:
-- baan (12), unban (13), moderate (11), unmoderate (10), rst passwd (14), chg passwd (15), edit user (9);
-- OTHERWISE we want to these moderation actions:
-- post approved (1), post edited (2), post moved (3), post deleted (4), 5, 6, 7, 8
--
-- Sometimes the moderator is the user itself, so take both of them into account
--

DECLARE @PageLowerBound int
DECLARE @PageUpperBound int

-- First set the rowcount
DECLARE @RowsToReturn int
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowsToReturn

-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1

-- Create a temp table to store the select results
CREATE TABLE #TmpPageIndex 
(
	IndexID int IDENTITY (1, 1) NOT NULL,
	ID int
)

CREATE INDEX page_index ON #TmpPageIndex(IndexID)

-- Select records
INSERT INTO #TmpPageIndex (ID)
	SELECT ID FROM cs_ModerationAudit (nolock) 
	WHERE UserID = @UserID AND ModerationAction = @ModerationAction AND SettingsID = @SettingsID
	ORDER BY ModeratedOn ASC

SELECT
	MA.*,
	U.UserName AS ModeratorName
FROM 
	cs_ModerationAudit MA (nolock) LEFT JOIN cs_vw_Users_FullUser U ON U.cs_UserID = MA.ModeratorID,
	#TmpPageIndex
WHERE 
	MA.ID = #TmpPageIndex.ID AND
	#TmpPageIndex.IndexID > @PageLowerBound AND
	#TmpPageIndex.IndexID < @PageUpperBound AND 
	MA.SettingsID = @SettingsID
ORDER BY
	IndexID

IF @ReturnRecordCount = 1
	SELECT COUNT(UserID) 
	FROM cs_ModerationAudit (nolock) 
	WHERE UserID = @UserID AND ModerationAction = @ModerationAction AND SettingsID = @SettingsID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].cs_ModerationAudit_User_Get to public
go