cs_points_calculateforuser_set.prc

community server 源码

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_Points_CalculateForUser_Set]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_Points_CalculateForUser_Set]
GO
--------------------------------------------------------------------------------
--	cs_Points_CalculateForUser_Set
--	Calculates the points for a user set
--------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[cs_Points_CalculateForUser_Set]
(
	@SettingsID			int,
	@FilterType			int,
	@FilterValue		varchar(255),
	@FavoriteUserFactor	int,
	@RaterFactor		int,
	@ForumRatingType	int,
	@UpdateAllSettingsID bit = 0
)
AS
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


	-- declare temp structures
	CREATE TABLE	#UserSet		(UserID int)
	CREATE TABLE	#PostData		(UserID int, Value int)
	CREATE TABLE	#FavoriteData	(UserID int, Value int)
	CREATE TABLE	#RatingData		(UserID int, Value int)


	-- get working user set
	INSERT INTO	#UserSet
				(UserID)
	EXECUTE		cs_Users_GetUserIDByFilter @SettingsID, @FilterType, @FilterValue


	-- get user's post points
	INSERT INTO	#PostData
				(UserID, Value)
	SELECT		P.UserID, SUM(P.Points)
	FROM		cs_Posts P
	INNER JOIN	cs_Sections S				-- exclude private messages
		ON		P.SectionID = S.SectionID
	INNER JOIN	#UserSet US
		ON		P.UserID = US.UserID
	WHERE		(P.SettingsID = @SettingsID or @UpdateAllSettingsID = 1)
		AND		S.EnablePostPoints = 1		-- ignore excluded sections
		AND		P.IsApproved = 1			-- ignore posts awaiting moderation
	GROUP BY	P.UserID


	-- get user's favorite count
	INSERT INTO	#FavoriteData
				(UserID, Value)
	SELECT		FU.UserID, COUNT(FU.OwnerID)
	FROM		cs_FavoriteUsers FU
	INNER JOIN	#UserSet US
		ON		FU.UserID = US.UserID
	WHERE		(FU.SettingsID = @SettingsID or @UpdateAllSettingsID = 1)
		AND		FU.UserID <> FU.OwnerID			-- ignore self-favorites
	GROUP BY	FU.UserID


	-- get user's rating sum
	INSERT INTO	#RatingData
				(UserID, Value)
	SELECT		PR.UserID, ISNULL(SUM(PR.Rating), 0)
	FROM		cs_PostRating PR
	INNER JOIN	cs_Posts P
		ON		PR.PostID = P.PostID
	INNER JOIN	cs_Sections S				-- exclude private messages
		ON		P.SectionID = S.SectionID
	INNER JOIN	#UserSet US
		ON		PR.UserID = US.UserID
	WHERE		(PR.SettingsID = @SettingsID or @UpdateAllSettingsID = 1)
		AND		S.EnablePostPoints = 1		-- ignore excluded sections
		AND		PR.UserID <> P.UserID		-- ignore self-ratings
		AND		P.IsApproved = 1			-- ignore posts awaiting moderation
		AND		@ForumRatingType = 1		-- only used if setup for post rating; ignore thread rating
	GROUP BY	PR.UserID


	-- update user points
	UPDATE		UP
	SET			Points =
					ISNULL(PD.Value, 0)	* 1
					+ ISNULL(FD.Value, 0) * @FavoriteUserFactor
					+ ISNULL(RD.Value, 0) * @RaterFactor,
				PointsUpdated = GETDATE()
	FROM		cs_UserProfile UP
	INNER JOIN	#UserSet US
		ON		UP.UserID = US.UserID
	LEFT JOIN	#PostData PD
		ON		UP.UserID = PD.UserID
	LEFT JOIN	#FavoriteData FD
		ON		UP.UserID = FD.UserID
	LEFT JOIN	#RatingData RD
		ON		UP.UserID = RD.UserID
	WHERE		(UP.SettingsID = @SettingsID or @UpdateAllSettingsID = 1)


	-- clean up
	DROP TABLE	#RatingData
	DROP TABLE	#FavoriteData
	DROP TABLE	#PostData
	DROP TABLE	#UserSet


	RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT EXECUTE on [dbo].[cs_Points_CalculateForUser_Set] to [public]