cs_system_updatesite.prc

community server 源码

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO






CREATE PROCEDURE [dbo].[cs_system_UpdateSite]
(
	@UpdateUserPostRank bit = 1,
	@UpdateMostActiveUserList bit = 1,
	@SettingsID int,
	@UpdateWindow int = 3
)
AS

-- Do we need to update the statistics?
DECLARE @LastUpdate datetime
DECLARE @DateWindow datetime
DECLARE @ApplicationId uniqueidentifier
DECLARE @ModeratorRoleID AS uniqueidentifier

SET @ApplicationId = (SELECT ApplicationId FROM aspnet_Applications A, cs_SiteSettings S WHERE A.ApplicationName = S.ApplicationName AND SettingsID = @SettingsID)
SET @ModeratorRoleID = (SELECT RoleId from aspnet_Roles WHERE LoweredRoleName = 'moderator' AND ApplicationId = @ApplicationId)
SET @LastUpdate = ISNULL((SELECT MAX(DateCreated) FROM cs_statistics_Site where SettingsID = @SettingsID), '1/1/1797')
SET @DateWindow = DATEADD(hh, -@UpdateWindow, GetDate())

if (@LastUpdate <  @DateWindow)
BEGIN


	-- Get summary information - Total Users, Total Posts, TotalTopics, DaysPosts, and DaysTopics
	DECLARE @LastDateTimeUpdate datetime
	DECLARE @TotalUsers int
	DECLARE @TotalPosts int
	DECLARE @TotalTopics int
	DECLARE @TotalModerators int
	DECLARE @TotalModeratedPosts int
	DECLARE @NewThreadsInPast24Hours int
	DECLARE @NewPostsInPast24Hours int
	DECLARE @NewUsersInPast24Hours int
	DECLARE @MostViewsPostID int
        DECLARE @ViewCount int
	DECLARE @MostActivePostID int
	DECLARE @ReplyCount int
	DECLARE @MostReadPostID int
	DECLARE @TotalAnonymousUsers int
	DECLARE @NewestUserID int
	DECLARE @MostActiveUserID int
	DECLARE @ApplicationName  NVARCHAR(256)

	SET @ApplicationName  = (Select Lower(ApplicationName) FROM cs_SiteSettings where SettingsID = @SettingsID)



	SET NOCOUNT ON

	SET @LastDateTimeUpdate = ISNULL( 
					(
						SELECT TOP 1
							DateCreated 
						FROM 
							cs_statistics_Site where SettingsID = @SettingsID
					), '1/1/1979 12:00:00')

	-- Reset top posters
	IF @UpdateUserPostRank = 1
		exec cs_system_UpdateUserPostRank @SettingsID

	IF @UpdateMostActiveUserList = 1
		exec cs_system_UpdateMostActiveUsers @SettingsID

	-- Total Anonymous Users
	-- ***********************************************
	SET @TotalAnonymousUsers = 0-- ISNULL((SELECT COUNT(UserID) FROM cs_AnonymousUsers where SettingsID = @SettingsID), 0 )

	-- Total Moderators, for this site only
	-- ***********************************************
	SET @TotalModerators = ISNULL(
					(
						SELECT 
							COUNT(*) 
						FROM 
							aspnet_UsersInRoles jUR
							JOIN aspnet_Roles jR ON jR.RoleID = jUR.RoleID
							JOIN aspnet_applications jA on jR.ApplicationId = jA.ApplicationId			
						WHERE 
							jR.LoweredRoleName = 'moderator' and LOWER(@ApplicationName) = jA.LoweredApplicationName
					), 0)

	-- Total Moderated Posts
	-- ***********************************************
	SET @TotalModeratedPosts  = 	ISNULL( 
					(
						SELECT TOP 1 
							TotalModeratedPosts 
						FROM 
							cs_statistics_Site where SettingsID = @SettingsID 
					), 0) + 
					ISNULL( 
					(
						SELECT 
							COUNT(ModerationAction) 
						FROM 
							cs_ModerationAudit 
						WHERE 
							ModeratedOn >= @LastDateTimeUpdate AND
							SettingsID = @SettingsID AND
							ModerationAction = 1
					), 0)
	IF @TotalModeratedPosts = 0
	BEGIN
		-- there was no previous count.  this is mainly for clean installs
		SET @TotalModeratedPosts = (SELECT COUNT(ModerationAction) FROM cs_ModerationAudit WHERE SettingsID = @SettingsID AND ModerationAction = 1)
	END

	-- Most "Viewed" thread, by grabbing the first post
	-- ***********************************************
	SET @MostViewsPostID = ISNULL(
					(
						SELECT TOP 1 
							jP1.PostID
						FROM 
							cs_Threads jT
							JOIN cs_Posts jP1 ON jP1.ThreadID = jT.ThreadID
							JOIN cs_vw_EveryOne_Role jE ON jE.LoweredApplicationName = @ApplicationName
							JOIN cs_ProductPermissions jP ON (jP.RoleID = jE.RoleId and jP.ApplicationType = 0)
							LEFT JOIN cs_SectionPermissions jP2 ON (jP2.RoleID = jE.RoleId and jP2.SectionID = jT.SectionID)
							JOIN cs_Sections jF on jT.SectionID = jF.SectionID
						WHERE 
							isnull(jP2.AllowMask,jP.AllowMask) & convert(bigint,0x0000000000000001) = convert(bigint,0x0000000000000001) AND
							jT.ThreadDate > DateAdd(d, -3, GetDate()) AND
							jP1.IsApproved = 1 AND
							jF.ForumType = 0 and jF.ApplicationType = 0 and jP1.SettingsID = @SettingsID
						ORDER BY 
							jT.TotalViews DESC
					), 0)

        -- "ViewCount" thread, by grabbing the first post
	-- ***********************************************
	SET @ViewCount = ISNULL(
					(
						SELECT TOP 1 
							jP1.TotalViews
						FROM 
							cs_Threads jT
							JOIN cs_Posts jP1 ON jP1.ThreadID = jT.ThreadID
							JOIN cs_vw_EveryOne_Role jE ON jE.LoweredApplicationName = @ApplicationName
							JOIN cs_ProductPermissions jP ON (jP.RoleID = jE.RoleId and jP.ApplicationType = 0)
							LEFT JOIN cs_SectionPermissions jP2 ON (jP2.RoleID = jE.RoleId and jP2.SectionID = jT.SectionID)
							JOIN cs_Sections jF on jT.SectionID = jF.SectionID
						WHERE 
							isnull(jP2.AllowMask,jP.AllowMask) & convert(bigint,0x0000000000000001) = convert(bigint,0x0000000000000001) AND
							jT.ThreadDate > DateAdd(d, -3, GetDate()) AND
							jP1.IsApproved = 1 AND
							jF.ForumType = 0 and jF.ApplicationType = 0 and jP1.SettingsID = @SettingsID
						ORDER BY
							jT.TotalViews DESC
					), 0)


	-- Most "Active" Thread, by grabbing the first post
	-- ***********************************************
	SET @MostActivePostID = ISNULL(
					(
						SELECT TOP 1 
							jP1.PostID
						FROM 
							cs_Threads jT
							JOIN cs_Posts jP1 ON jP1.ThreadID = jT.ThreadID
							JOIN cs_vw_EveryOne_Role jE ON jE.LoweredApplicationName = @ApplicationName
							JOIN cs_ProductPermissions jP ON (jP.RoleID = jE.RoleId and jP.ApplicationType = 0)
							LEFT JOIN cs_SectionPermissions jP2 ON (jP2.RoleID = jE.RoleId and jP2.SectionID = jT.SectionID)
							JOIN cs_Sections jF on jP1.SectionID = jF.SectionID
						WHERE 
							isnull(jP2.AllowMask,jP.AllowMask) & convert(bigint,0x0000000000000001) = convert(bigint,0x0000000000000001) AND
							jT.ThreadDate > DateAdd(d, -3, GetDate()) AND
							jP1.IsApproved = 1 AND
							jF.ForumType = 0 and jF.ApplicationType = 0 and jP1.SettingsID = @SettingsID		-- excluding PM and hidden forums
						ORDER BY 
							jT.TotalReplies DESC
					), 0)

        -- "ReplyCount" on a Thread, by grabbing the first post
	-- ***********************************************
	SET @ReplyCount = ISNULL(
					(
						SELECT TOP 1 
							jT.TotalReplies
						FROM 
							cs_Threads jT
							JOIN cs_Posts jP1 ON jP1.ThreadID = jT.ThreadID
							JOIN cs_vw_EveryOne_Role jE ON jE.LoweredApplicationName = @ApplicationName
							JOIN cs_ProductPermissions jP ON (jP.RoleID = jE.RoleId and jP.ApplicationType = 0)
							LEFT JOIN cs_SectionPermissions jP2 ON (jP2.RoleID = jE.RoleId and jP2.SectionID = jT.SectionID)
							JOIN cs_Sections jF on jP1.SectionID = jF.SectionID
						WHERE 
							isnull(jP2.AllowMask,jP.AllowMask) & convert(bigint,0x0000000000000001) = convert(bigint,0x0000000000000001) AND
							jT.ThreadDate > DateAdd(d, -3, GetDate()) AND
							jP1.IsApproved = 1 AND
							jF.ForumType = 0 and jF.ApplicationType = 0 and jP1.SettingsID = @SettingsID		-- excluding PM and hidden forums
						ORDER BY 
							jT.TotalReplies DESC
					), 0)

	-- Most "Read" thread, by grabbing the first post
	-- ***********************************************
	SET @MostReadPostID = ISNULL(
					(
						SELECT TOP 1 
							jP1.PostID
						FROM 
							cs_Threads jT
							JOIN cs_Posts jP1 ON jP1.ThreadID = jT.ThreadID
							JOIN cs_vw_EveryOne_Role jE ON jE.LoweredApplicationName = @ApplicationName
							JOIN cs_ProductPermissions jP ON (jP.RoleID = jE.RoleId and jP.ApplicationType = 0)
							LEFT JOIN cs_SectionPermissions jP2 ON (jP2.RoleID = jE.RoleId and jP2.SectionID = jT.SectionID)
							JOIN cs_Sections jF on jP1.SectionID = jF.SectionID
						WHERE 
							isnull(jP2.AllowMask,jP.AllowMask) & convert(bigint,0x0000000000000001) = convert(bigint,0x0000000000000001) AND
							jT.ThreadDate > DateAdd(d, -3, GetDate()) AND
							jP1.IsApproved = 1 AND
							jF.ForumType = 0 and jF.ApplicationType = 0 and jF.SettingsID = @SettingsID		-- excluding PM and hidden forums
						ORDER BY 
							( SELECT count(jTR.ThreadID) FROM cs_ThreadsRead jTR WHERE jP1.ThreadID = jTR.ThreadID ) DESC
					), 0)


	-- Most active user
	-- ***********************************************
	SET @MostActiveUserID = ISNULL(
					(
						SELECT TOP 1 
							--jU.cs_UserID
							jP.UserID
						FROM 
							--cs_vw_Users_FullUser jU JOIN 
							cs_UserProfile jP --ON jP.UserID = jU.cs_UserID
						WHERE
							jP.EnableDisplayInMemberList = 1 and jP.SettingsID = @SettingsID
						ORDER BY 
							jP.TotalPosts DESC
					), 0)
	-- Newest user
	-- ***********************************************
	SET @NewestUserID = ISNULL(
					(
						SELECT Max(ju.cs_UserID) 

						FROM 
							cs_vw_Users_FullUser jU
							JOIN cs_UserProfile jP ON jP.UserID = jU.cs_UserID
						WHERE
							jP.EnableDisplayInMemberList = 1 AND
							jU.cs_UserAccountStatus = 1 and jP.SettingsID = @SettingsID
						--ORDER BY jU.CreateDate DESC
					), 0)


	-- Total Users
	-- ***********************************************
	SET @TotalUsers = ISNULL( 
					(
						SELECT 
							COUNT(UserID) 
						FROM 
							cs_UserProfile 
						WHERE 
							--EnableDisplayInMemberList = 1 and SettingsID = @SettingsID
							SettingsID = @SettingsID
					) ,0) 


	-- Total Posts
	-- ***********************************************
	SET @TotalPosts = 	ISNULL( 
					(
						SELECT TOP 1 
							TotalPosts 
						FROM 
							cs_statistics_Site where SettingsID = @SettingsID 
					), 0) +
				 ISNULL( 
					(
						SELECT 
							COUNT(PostID) 
						FROM 
							cs_Posts, cs_Sections
						WHERE 
							ForumType = 0 and cs_Posts.SectionID = cs_Sections.SectionID and 
						PostDate >= @LastDateTimeUpdate and cs_Sections.SettingsID = @SettingsID and cs_Sections.ApplicationType = 0
					), 0)
	IF @TotalPosts = 0
	BEGIN
		-- there was no previous count.  this is mainly for clean installs
		SET @TotalPosts = (SELECT COUNT(PostID) FROM cs_Posts WHERE SettingsID = @SettingsID)
	END


	-- Total Topics
	-- ***********************************************
	SET @TotalTopics = 	ISNULL( 
					(
						SELECT TOP 1 
							TotalTopics 
						FROM 
							cs_statistics_Site where SettingsID = @SettingsID 
					), 0) + 
				ISNULL( 
					(
						SELECT 
							COUNT(ThreadID) 
						FROM 
							cs_Threads, cs_Sections 
						WHERE 
							ForumType = 0 AND cs_Threads.SectionID = cs_Sections.SectionID and 
							ThreadDate >= @LastDateTimeUpdate and cs_Sections.SettingsID = @SettingsID and cs_Sections.ApplicationType = 0
					), 0)
	IF @TotalTopics = 0
	BEGIN
		-- there was no previous count.  this is mainly for clean installs
		SET @TotalTopics = (SELECT COUNT(ThreadID) FROM cs_Threads, cs_Sections WHERE cs_Sections.SettingsID = @SettingsID and ForumType = 0 and cs_Sections.SectionID = cs_Threads.SectionID and cs_Sections.ApplicationType = 0)
	END

	-- Total Posts in past 24 hours
	-- ***********************************************
	SET @NewPostsInPast24Hours = ISNULL( 
					(SELECT COUNT(PostID) FROM cs_Posts, cs_Sections WHERE cs_Sections.ApplicationType = 0 and cs_Sections.SettingsID = @SettingsID And cs_Sections.SectionID = cs_Posts.SectionID and ForumType = 0 and PostDate > DATEADD(dd,-1,getdate())
					), 0)

	-- Total Users in past 24 hours
	-- ***********************************************
	SET @NewUsersInPast24Hours = ISNULL(
						(SELECT COUNT(IsApproved) FROM aspnet_Membership WHERE ApplicationId = @ApplicationId and CreateDate > DATEADD(dd,-1,getdate())
					), 0)

	-- Total Topics in past 24 hours
	-- ***********************************************
	SET @NewThreadsInPast24Hours = ISNULL(
						(SELECT COUNT(ThreadID) FROM cs_Threads, cs_Sections WHERE cs_Sections.SectionID = cs_Threads.SectionID AND cs_Sections.SettingsID = @SettingsID and ForumType = 0 and cs_Sections.ApplicationType = 0 AND PostDate > DATEADD(dd,-1,getdate())
					), 0)

	INSERT INTO cs_statistics_Site
	SELECT 
		DateCreated = GetDate(),
		TotalUsers = @TotalUsers,
		TotalPosts = @TotalPosts,
		TotalModerators = @TotalModerators,
		TotalModeratedPosts = @TotalModeratedPosts,
		TotalAnonymousUsers = @TotalAnonymousUsers,
		TotalTopics = @TotalTopics,
		DaysPosts = @NewPostsInPast24Hours, -- TODO remove
		DaysTopics = @NewThreadsInPast24Hours, -- TODO remove
		NewPostsInPast24Hours = @NewPostsInPast24Hours,
		NewThreadsInPast24Hours = @NewThreadsInPast24Hours,
		NewUsersInPast24Hours = @NewUsersInPast24Hours,
		MostViewsPostID = @MostViewsPostID,
		MostActivePostID = @MostActivePostID,
		MostActiveUserID = @MostActiveUserID,
		MostReadPostID = @MostReadPostID,
		NewestUserID = @NewestUserID,	
		SettingsID = @SettingsID,
        	ViewCount = @ViewCount,
        	ReplyCount = @ReplyCount
END

SET NOCOUNT OFF






GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_system_UpdateSite] to public
go