cs_points_calculateforuser.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]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_Points_CalculateForUser]
GO
--------------------------------------------------------------------------------
--	cs_Points_CalculateForUser
--	Calculates the points for a user
--------------------------------------------------------------------------------

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


	-- get user's post points
	DECLARE		@PostPoints		int
	SELECT		@PostPoints = ISNULL(SUM(P.Points), 0)
	FROM		cs_Posts P
	INNER JOIN	cs_Sections S				-- exclude private messages
		ON		P.SectionID = S.SectionID
	WHERE		P.UserID = @UserID
		AND		(P.SettingsID = @SettingsID or @UpdateAllSettingsID = 1)
		AND		S.EnablePostPoints = 1		-- ignore excluded sections
		AND		P.IsApproved = 1			-- ignore posts awaiting moderation


	-- get user's favorite count
	DECLARE		@FavoriteUserCount	int
	SELECT		@FavoriteUserCount = COUNT(FU.OwnerID)
	FROM		cs_FavoriteUsers FU
	WHERE		FU.UserID = @UserID
		AND		(FU.SettingsID = @SettingsID or @UpdateAllSettingsID = 1)
		AND		FU.UserID <> FU.OwnerID			-- ignore self-favorites


	-- get user's rating sum
	DECLARE		@RatingSum	int
	SELECT		@RatingSum = 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
	WHERE		PR.UserID = @UserID
		AND		(P.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


	-- update user points
	UPDATE		UP
	SET			Points =
					1 * @PostPoints
					+ @FavoriteUserCount * @FavoriteUserFactor
					+ @RatingSum * @RaterFactor,
				PointsUpdated = GETDATE()
	FROM		cs_UserProfile UP
	WHERE		UP.UserID = @UserID
		AND		(UP.SettingsID = @SettingsID or @UpdateAllSettingsID = 1)


	RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

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