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

CREATE PROCEDURE [dbo].[cs_Points_CalculateForPost]
(
	@SettingsID			int,
	@PostID				int,
	@PostFactor			int,
	@ReplyFactor		int,
	@ReplierFactor		int,
	@RatingFactor		int,
	@DownloadFactor		int,
	@DownloaderFactor	int,
	@FavoritePostFactor	int,
	@ForumRatingType	int
)
AS
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


	-- get post's reply count
	DECLARE		@ReplyCount		int,
				@ReplierCount	int
	SELECT		@ReplyCount = COUNT(P.PostID),
				@ReplierCount = COUNT(DISTINCT(P.UserID))
	FROM		cs_Posts PP
	INNER JOIN	cs_Posts P
		ON		PP.PostID = P.ParentID
	INNER JOIN	cs_Sections S				-- exclude private messages
		ON		PP.SectionID = S.SectionID
	WHERE		PP.PostID = @PostID
		AND		PP.SettingsID = @SettingsID
		AND		S.EnablePostPoints = 1		-- ignore excluded sections
		AND		PP.UserID <> P.UserID		-- ignore self-replies
		AND		PP.PostID <> P.PostID		-- ignore thread starters
		AND		P.IsApproved = 1			-- ignore posts awaiting moderation


	-- get post'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.PostID = @PostID
		AND		PR.SettingsID = @SettingsID
		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


	-- get post's download count
	DECLARE		@DownloadCount		int,
				@DownloaderCount	int
	SELECT		@DownloadCount = COUNT(D.PostID),
				@DownloaderCount = COUNT(DISTINCT(D.UserID))
	FROM		files_Downloads D
	INNER JOIN	cs_Posts P
		ON		D.PostID = P.PostID
	INNER JOIN	cs_Sections S
		ON		P.SectionID = S.SectionID
	WHERE		D.PostID = @PostID
		AND		P.SettingsID = @SettingsID
		AND		S.EnablePostPoints = 1		-- ignore excluded sections
		AND		D.UserID <> P.UserID		-- ignore self-authored favorites
		AND		P.IsApproved = 1			-- ignore posts awaiting moderation


	-- get post's favorite count
	DECLARE		@FavoriteCount	int
	SELECT		@FavoriteCount = COUNT(FP.PostID)
	FROM		cs_FavoritePosts FP
	INNER JOIN	cs_Posts P
		ON		FP.PostID = P.PostID
	INNER JOIN	cs_Sections S				-- exclude private messages
		ON		P.SectionID = S.SectionID
	WHERE		FP.PostID = @PostID
		AND		P.SettingsID = @SettingsID
		AND		S.EnablePostPoints = 1		-- ignore excluded sections
		AND		FP.OwnerID <> P.UserID		-- ignore self-authored favorites
		AND		P.IsApproved = 1			-- ignore posts awaiting moderation


	-- update post points
	UPDATE		P
	SET			Points =
				1 * @PostFactor
				+ @ReplyCount * @ReplyFactor
				+ @ReplierCount * @ReplierFactor
				+ @RatingSum * @RatingFactor
				+ @DownloadCount * @DownloadFactor
				+ @DownloaderCount * @DownloaderFactor
				+ @FavoriteCount * @FavoritePostFactor,
				PointsUpdated = GETDATE()
	FROM		cs_Posts P
	INNER JOIN	cs_Sections S				-- exclude private messages
		ON		P.SectionID = S.SectionID
	WHERE		P.PostID = @PostID
		AND		P.SettingsID = @SettingsID
		AND		S.EnablePostPoints = 1		-- ignore excluded sections
		AND		P.IsApproved = 1			-- ignore posts awaiting moderation


	RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

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