cs_site_statistics.prc

来自「community server 源码」· PRC 代码 · 共 98 行

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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




CREATE PROCEDURE [dbo].cs_Site_Statistics
(
	@SettingsID int
)
AS

SET Transaction Isolation Level Read UNCOMMITTED

-- Do we need to update the statistics?
DECLARE @LastUpdate 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')



-- SELECT current statistics
SELECT 
	S.*,
	CurrentAnonymousUsers = (SELECT Count(*) FROM cs_AnonymousUsers where SettingsID = @SettingsID),
	MostReadSubject = isnull((SELECT Subject FROM cs_Posts, cs_Sections WHERE PostID = S.MostReadPostID and cs_Posts.SettingsID = @SettingsID and cs_Posts.SectionID = cs_Sections.SectionID and cs_Sections.ApplicationType = 0),''),
	MostViewsSubject = isnull((SELECT Subject FROM cs_Posts, cs_Sections WHERE PostID = S.MostViewsPostID and cs_Posts.SettingsID = @SettingsID and cs_Posts.SectionID = cs_Sections.SectionID and cs_Sections.ApplicationType = 0),''),
	MostActiveSubject = isnull((SELECT Subject FROM cs_Posts, cs_Sections WHERE PostID = S.MostActivePostID and cs_Posts.SettingsID = @SettingsID and cs_Posts.SectionID = cs_Sections.SectionID and cs_Sections.ApplicationType = 0),''),
	MostActiveUser = isnull((SELECT UserName FROM cs_vw_Users_FullUser WHERE cs_UserID = S.MostActiveUserID and SettingsID = @SettingsID),''),
	NewestUser = isnull((SELECT UserName FROM cs_vw_Users_FullUser WHERE cs_UserID = S.NewestUserID and SettingsID = @SettingsID),'')
FROM
	cs_statistics_Site S
WHERE
	DateCreated = @LastUpdate  and SettingsID = @SettingsID

-- SELECT TOP 100 Users
SELECT TOP 100
	S.TotalPosts,
	U.*
FROM
	cs_statistics_User S,
	cs_vw_Users_FullUser U
WHERE
	S.UserID = U.cs_UserID AND
	U.EnableDisplayInMemberList = 1 AND
	U.cs_UserID > 0 and U.SettingsID = @SettingsID and S.SettingsID = @SettingsID
ORDER BY
	S.TotalPosts DESC

-- SELECT top 10 Moderators
SELECT TOP 10
	U.*,
	M.PostsModerated
FROM
	cs_vw_Users_FullUser U,
	aspnet_UsersInRoles R,
	cs_Moderators M
WHERE
	U.UserId = R.UserId AND
	R.RoleId = @ModeratorRoleID AND
	U.cs_UserID = M.UserID AND
	M.PostsModerated > 0 AND 
	U.SettingsID = @SettingsID AND 
	M.SettingsID = @SettingsID
ORDER BY
	PostsModerated DESC

-- SELECT Moderator actions
SELECT
	Description,
	TotalActions
FROM
	cs_ModerationAction
WHERE
	TotalActions > 0
ORDER BY
	TotalActions DESC 



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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