Stored Procedure Properties

Name	Value
Owner	dbo
Created	2004-05-31
Startup	False
Encrypted	False

Creation Options

Name	Value
QUOTED_IDENTIFIER	OFF
ANSI_NULLS	ON

Parameters

Name	DataType	Length	Type

Total: 0 parameter(s)

SQL

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO




--exec forums_GetStatistics


CREATE          PROCEDURE forums_GetStatistics AS
	-- Get summary information - Total Users, Total Posts, TotalTopics, DaysPosts, and DaysTopics
	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 @MostViewsSubject nvarchar(256)
	DECLARE @MostActivePostID int
	DECLARE @MostActiveSubject nvarchar(256)
	DECLARE @MostReadPostID int
	DECLARE @MostReadSubject nvarchar(256)
	DECLARE @MostActiveUser nvarchar(50)
	DECLARE @NewestUser nvarchar(50)

	SET NOCOUNT ON

	-- Reset top posters
	exec Statistics_ResetTopPosters

	-- Total Moderators
	-- ***********************************************
	SELECT 
		@TotalModerators = count(*) 
	FROM 
		UsersInRoles 
	WHERE 
		RoleName = 'Forum-Moderators'


	-- Total Moderated Posts
	-- ***********************************************
	SELECT 
		@TotalModeratedPosts = count(*) 
	FROM 
		ModerationAudit 

	-- Most views
	-- ***********************************************
	SELECT TOP 1 
		@MostViewsPostID = PostId, 
		@MostViewsSubject = Subject 
	FROM 
		Posts 
	WHERE 
		ThreadDate > DateAdd(d, -2, GetDate()) AND
		ForumID NOT IN (SELECT ForumID from PrivateForums) AND
		Approved = 1
	ORDER BY 
		TotalViews desc

	-- Most active post
	-- ***********************************************
	SELECT TOP 1 
		@MostActivePostID = PostID, 
		@MostActiveSubject = Subject
	FROM 
		Posts P 
	WHERE 
		P.PostLevel = 1 AND 
		ThreadDate > DateAdd(d, -2, GetDate()) AND
		ForumID NOT IN (SELECT ForumID from PrivateForums) AND
		Approved = 1
	ORDER BY 
		(SELECT Count(*) FROM Posts P2 WHERE P2.ThreadID = P.ThreadID) DESC

	-- Most active user
	-- ***********************************************
	SELECT TOP 1 
		@MostActiveUser = Username 
	FROM 
		Users 
	ORDER BY 
		TotalPosts DESC

	-- Newest user
	-- ***********************************************
	SELECT TOP 1 
		@NewestUser = Username 
	FROM 
		Users 
	WHERE
		DisplayInMemberList = 1
	ORDER BY 
		DateCreated DESC

	-- Most read posts
	-- ***********************************************
	SELECT TOP 1 
		@MostReadPostID = PostId, 
		@MostReadSubject = Subject
	FROM 
		Posts P
	WHERE
		ForumID NOT IN (SELECT ForumID from PrivateForums) AND
		Approved = 1
	ORDER BY 
		(SELECT count(HasRead) FROM PostsRead WHERE P.PostID = PostsRead.PostID) DESC

	-- Other stats
	SELECT
		-- Total Users
		-- ***********************************************
		@TotalUsers = (SELECT COUNT(*) FROM Users (nolock)),

		-- Total Posts
		-- ***********************************************
		@TotalPosts = (SELECT COUNT(*) FROM Posts) + (SELECT COUNT(*) FROM Post_Archive (nolock)),

		-- Total Topics
		-- ***********************************************
		@TotalTopics = (SELECT COUNT(*) FROM Posts (nolock) WHERE ParentID = PostID) + (SELECT COUNT(*) FROM Post_Archive WHERE ParentID = PostID),

		-- Total Posts in past 24 hours
		-- ***********************************************
		@NewPostsInPast24Hours = (SELECT COUNT(*) FROM Posts (nolock)
				WHERE PostDate > DATEADD(dd,-1,getdate())),

		-- Total Users in past 24 hours
		-- ***********************************************
		@NewUsersInPast24Hours = (SELECT COUNT(*) FROM Users (nolock)
				WHERE DateCreated > DATEADD(dd,-1,getdate())),


		-- Total Topics in past 24 hours
		-- ***********************************************
		@NewThreadsInPast24Hours = (SELECT COUNT(*) FROM Posts (nolock)
				WHERE ParentID = PostID AND PostDate > DATEADD(dd,-1,getdate()))
	SET NOCOUNT OFF

	-- Test for null values
	IF @MostViewsPostID IS NULL
		SET @MostViewsPostID = 0

	IF @MostViewsSubject IS NULL
		SET @MostViewsSubject = 'No Posts Available'

	IF @MostActivePostID IS NULL
		SET @MostActivePostID = 0

	IF @MostActiveSubject IS NULL
		SET @MostActiveSubject = 'No Posts Available'

	IF @MostActiveUser IS NULL
		SET @MostActiveUser = 'No Posts Available'

	IF @MostReadPostID IS NULL
		SET @MostReadPostID = 0

	IF @MostReadSubject IS NULL
		SET @MostReadSubject = 'No Posts Available'

	SELECT 
		TotalUsers = @TotalUsers,
		TotalPosts = @TotalPosts,
		TotalModerators = @TotalModerators,
		TotalModeratedPosts = @TotalModeratedPosts,
		TotalTopics = @TotalTopics,
		DaysPosts = @NewPostsInPast24Hours, -- TODO remove
		DaysTopics = @NewThreadsInPast24Hours, -- TODO remove
		NewPostsInPast24Hours = @NewPostsInPast24Hours,
		NewThreadsInPast24Hours = @NewThreadsInPast24Hours,
		NewUsersInPast24Hours = @NewUsersInPast24Hours,
		MostViewsPostID = @MostViewsPostID,
		MostViewsSubject = @MostViewsSubject,
		MostActivePostID = @MostActivePostID,
		MostActiveSubject = @MostActiveSubject,
		MostActiveUser = @MostActiveUser,
		MostReadPostID = @MostReadPostID,
		MostReadSubject = @MostReadSubject,
		NewestUser = @NewestUser












GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO