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

CREATE PROCEDURE [dbo].[cs_Points_CalculateForPost_Set]
(
	@SettingsID			int,
	@FilterType			int,
	@FilterValue		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

	-- declare temp structures
	CREATE TABLE	#PostSet		(PostID int)
	CREATE TABLE	#ReplyData		(PostID int, ReplyCount int, ReplierCount int)
	CREATE TABLE	#RatingData		(PostID int, RatingSum int)
	CREATE TABLE	#DownloadData	(PostID int, DownloadCount int, DownloaderCount int)
	CREATE TABLE	#FavoriteData	(PostID int, Value int)


	-- get working post set
	INSERT INTO	#PostSet
				(PostID)
	EXECUTE		cs_Posts_GetPostIDByFilter @SettingsID, @FilterType, @FilterValue


	-- get post's reply statistics
	INSERT INTO	#ReplyData
				(PostID, ReplyCount, ReplierCount)
	SELECT		PP.PostID, COUNT(P.PostID), COUNT(DISTINCT(P.UserID))
	FROM		cs_Posts PP
	INNER JOIN	cs_Posts P
		ON		PP.PostID = P.ParentID
	INNER JOIN	#PostSet PS
		ON		PP.PostID = PS.PostID
	WHERE		PP.SettingsID = @SettingsID
		AND		PP.UserID <> P.UserID		-- ignore self-replies
		AND		PP.PostID <> P.PostID		-- ignore thread starters
	GROUP BY	PP.PostID


	-- get post's rating statistics
	INSERT INTO	#RatingData
				(PostID, RatingSum)
	SELECT		PR.PostID, ISNULL(SUM(PR.Rating), 0)
	FROM		cs_PostRating PR
	INNER JOIN	cs_Posts P
		ON		PR.PostID = P.PostID
	INNER JOIN	#PostSet PS
		ON		PR.PostID = PS.PostID
	WHERE		PR.SettingsID = @SettingsID
		AND		PR.UserID <> P.UserID		-- ignore self-ratings
		AND		@ForumRatingType = 1		-- only used if setup for post rating; ignore thread rating
	GROUP BY	PR.PostID


	-- get post's download statistics
	INSERT INTO	#DownloadData
				(PostID, DownloadCount, DownloaderCount)
	SELECT		D.PostID, COUNT(D.PostID), COUNT(DISTINCT(D.UserID))
	FROM		files_Downloads D
	INNER JOIN	cs_Posts P
		ON		D.PostID = P.PostID
	INNER JOIN	#PostSet PS
		ON		D.PostID = PS.PostID
	WHERE		P.SettingsID = @SettingsID
		AND		D.UserID <> P.UserID		-- ignore self-downloads
	GROUP BY	D.PostID


	-- get post's favorite count
	INSERT INTO	#FavoriteData
				(PostID, Value)
	SELECT		FP.PostID, COUNT(FP.PostID)
	FROM		cs_FavoritePosts FP
	INNER JOIN	cs_Posts P
		ON		FP.PostID = P.PostID
	INNER JOIN	#PostSet PS
		ON		FP.PostID = PS.PostID
	WHERE		FP.SettingsID = @SettingsID
		AND		FP.OwnerID <> P.UserID
	GROUP BY	FP.PostID


	-- disable triggers (nntp triggers make this update deathly SLOW!)
	ALTER TABLE cs_posts DISABLE TRIGGER ALL


	-- update post points
	UPDATE		P
	SET			Points =
				1 * @PostFactor
				+ ISNULL(RPD.ReplyCount, 0) * @ReplyFactor
				+ ISNULL(RPD.ReplierCount, 0) * @ReplierFactor
				+ ISNULL(RTD.RatingSum, 0) * @RatingFactor
				+ ISNULL(DD.DownloadCount, 0) * @DownloadFactor
				+ ISNULL(DD.DownloaderCount, 0) * @DownloaderFactor
				+ ISNULL(FD.Value, 0) * @FavoritePostFactor,
				PointsUpdated = GETDATE()
	FROM		cs_Posts P
	INNER JOIN	#PostSet PS
		ON		P.PostID = PS.PostID
	LEFT JOIN	#ReplyData RPD
		ON		P.PostID = RPD.PostID
	LEFT JOIN	#RatingData RTD
		ON		P.PostID = RTD.PostID
	LEFT JOIN	#DownloadData DD
		ON		P.PostID = DD.PostID
	LEFT JOIN	#FavoriteData FD
		ON		P.PostID = FD.PostID
	WHERE		P.SettingsID = @SettingsID


	-- enable triggers
	ALTER TABLE cs_posts ENABLE TRIGGER ALL


	-- clean up
	DROP TABLE	#FavoriteData
	DROP TABLE	#DownloadData
	DROP TABLE	#RatingData
	DROP TABLE	#ReplyData
	DROP TABLE	#PostSet


	RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

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