cs_users_get.prc

community server 源码

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE [dbo].cs_users_Get
(
	@PageIndex int,
	@PageSize int,
	@sqlPopulate ntext,
	@SearchText nvarchar(256),
	@ReturnModerationCounters bit = 0,
	@SettingsID int
)
AS

SET Transaction Isolation Level Read UNCOMMITTED

BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int
DECLARE @TotalUsers int

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

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

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

DECLARE @ParmDefinition NVARCHAR(500)
SET @ParmDefinition = N'@SearchText nvarchar(256)'

INSERT INTO #PageIndexForUsers (UserID)
Exec sp_executesql @sqlPopulate, @ParmDefinition, @SearchText



SET @TotalUsers = @@rowcount

-- Get the user details
IF @ReturnModerationCounters = 1
	SELECT
		U.*,
		ApprovePost = (SELECT COUNT(*) FROM cs_ModerationAudit WHERE ModerationAction = 1 AND UserID = U.cs_UserID AND SettingsID = @SettingsID),
		EditPost = (SELECT COUNT(*) FROM cs_ModerationAudit WHERE ModerationAction = 2 AND UserID = U.cs_UserID AND SettingsID = @SettingsID),
		MovePost = (SELECT COUNT(*) FROM cs_ModerationAudit WHERE ModerationAction = 3 AND UserID = U.cs_UserID AND SettingsID = @SettingsID),
		DeletePost = (SELECT COUNT(*) FROM cs_ModerationAudit WHERE ModerationAction = 4 AND UserID = U.cs_UserID AND SettingsID = @SettingsID),
		LockPost = (SELECT COUNT(*) FROM cs_ModerationAudit WHERE ModerationAction = 5 AND UserID = U.cs_UserID AND SettingsID = @SettingsID),
		UnlockPost = (SELECT COUNT(*) FROM cs_ModerationAudit WHERE ModerationAction = 6 AND UserID = U.cs_UserID AND SettingsID = @SettingsID),
		MergePost = (SELECT COUNT(*) FROM cs_ModerationAudit WHERE ModerationAction = 7 AND UserID = U.cs_UserID AND SettingsID = @SettingsID),
		SplitPost = (SELECT COUNT(*) FROM cs_ModerationAudit WHERE ModerationAction = 8 AND UserID = U.cs_UserID AND SettingsID = @SettingsID),
		EditUser = (SELECT COUNT(*) FROM cs_ModerationAudit WHERE ModerationAction = 9 AND UserID = U.cs_UserID AND SettingsID = @SettingsID),
		UnmoderateUser = (SELECT COUNT(*) FROM cs_ModerationAudit WHERE ModerationAction = 10 AND UserID = U.cs_UserID AND SettingsID = @SettingsID),
		ModerateUser = (SELECT COUNT(*) FROM cs_ModerationAudit WHERE ModerationAction = 11 AND UserID = U.cs_UserID AND SettingsID = @SettingsID),
		BanUser = (SELECT COUNT(*) FROM cs_ModerationAudit WHERE ModerationAction = 12 AND UserID = U.cs_UserID AND SettingsID = @SettingsID),
		UnbanUser = (SELECT COUNT(*) FROM cs_ModerationAudit WHERE ModerationAction = 13  AND UserID = U.cs_UserID AND SettingsID = @SettingsID),
		ResetPassword = (SELECT COUNT(*) FROM cs_ModerationAudit WHERE ModerationAction = 14  AND UserID = U.cs_UserID AND SettingsID = @SettingsID),
		ChangePassword = (SELECT COUNT(*) FROM cs_ModerationAudit WHERE ModerationAction = 15  AND UserID = U.cs_UserID AND SettingsID = @SettingsID),
		PostIsAnnouncement = (SELECT COUNT(*) FROM cs_ModerationAudit WHERE ModerationAction = 16  AND UserID = U.cs_UserID AND SettingsID = @SettingsID),
		PostIsNotAnnoucement = (SELECT COUNT(*) FROM cs_ModerationAudit WHERE ModerationAction = 17  AND UserID = U.cs_UserID AND SettingsID = @SettingsID),
		UnApprovePost = (SELECT COUNT(*) FROM cs_ModerationAudit WHERE ModerationAction = 18  AND UserID = U.cs_UserID AND SettingsID = @SettingsID)	
		-- (we do not see to be using this value),IsModerator = (SELECT Count(*) FROM cs_Moderators WHERE UserID = U.cs_UserID)
	FROM 
		cs_vw_Users_FullUser U (nolock),
		#PageIndexForUsers
	WHERE 
		U.cs_UserID = #PageIndexForUsers.UserID AND
		#PageIndexForUsers.IndexID > @PageLowerBound AND
		#PageIndexForUsers.IndexID < @PageUpperBound and U.SettingsID = @SettingsID
	ORDER BY
		#PageIndexForUsers.IndexID
ELSE
	SELECT
		U.*
		-- (we do not see to be using this value),IsModerator = (SELECT Count(*) FROM cs_Moderators WHERE UserID = U.cs_UserID)
	FROM 
		cs_vw_Users_FullUser U (nolock),
		#PageIndexForUsers
	WHERE 
		U.cs_UserID = #PageIndexForUsers.UserID AND
		#PageIndexForUsers.IndexID > @PageLowerBound AND
		#PageIndexForUsers.IndexID < @PageUpperBound and U.SettingsID = @SettingsID
	ORDER BY
		#PageIndexForUsers.IndexID

END

-- Return the record count
SELECT @TotalUsers

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[cs_users_Get]  TO [public]
GO