cs_forumpostactivityreportrecords_get.prc

community server 源码

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


/* Most active forums for a given month: Forum, Total Posts, Replies */
CREATE PROC dbo.cs_ForumPostActivityReportRecords_Get
(
	@BegReportDate datetime,
	@EndReportDate datetime,
	@nRecordNumberStart int,
	@nRecordNumberEnd int,
	@Paged bit
)
AS

BEGIN
IF @Paged = 1
	BEGIN
		DECLARE @totalRecords INT
	
		--------------------------------------------------------------------
	    	-- Define the table to do the filtering and paging
	    	--------------------------------------------------------------------
	    	DECLARE @tblTempData TABLE
	        (
	        	nID INT IDENTITY,
	        	SectionID int,
			ApplicationKey nvarchar(256),
			[Name] nvarchar(255),
			Threads int,
			Replies int,
			Users int
	        )
		INSERT INTO @tblTempData
		(
	        	SectionID, ApplicationKey, [Name], Threads, Replies, Users
	        )
		SELECT * FROM
		    	(SELECT DISTINCT 
			s.SectionID,
			s.ApplicationKey,
			s.[Name],
			(SELECT COUNT(*) FROM cs_Threads t WHERE t.SectionID = s.SectionID AND t.ThreadDate BETWEEN @BegReportDate AND @EndReportDate ) AS Threads,
			(SELECT COUNT(*) FROM cs_Posts p WHERE p.SectionID = s.SectionID  AND p.PostDate BETWEEN @BegReportDate AND @EndReportDate) AS Replies,
			(SELECT COUNT(*) FROM (SELECT DISTINCT UserID FROM cs_Posts p WHERE p.SectionID = s.SectionID  AND p.PostDate BETWEEN @BegReportDate AND @EndReportDate) AS u) AS Users
				FROM cs_Sections s
				WHERE s.ApplicationType = 0
			) AS tr
		WHERE tr.Threads > 0 or tr.Replies > 0
		ORDER BY tr.Replies DESC

		SET @totalRecords = @@rowcount
	        ---------------------------------------------------------------------------------------------------------------------------------------
		SELECT
	        	SectionID, ApplicationKey, [Name], Threads, Replies, Users
	    	FROM
			@tblTempData 
	    	WHERE
			nID BETWEEN @nRecordNumberStart AND @nRecordNumberEnd
	    	ORDER BY 
	        	nID ASC
	
		-- Return totalRecords
		SELECT @totalRecords
	END
ELSE
	BEGIN
		SELECT * FROM
		    	(SELECT DISTINCT 
			s.SectionID,
			s.ApplicationKey,
			s.[Name],
			(SELECT COUNT(*) FROM cs_Threads t WHERE t.SectionID = s.SectionID AND t.ThreadDate BETWEEN @BegReportDate AND @EndReportDate ) AS Threads,
			(SELECT COUNT(*) FROM cs_Posts p WHERE p.SectionID = s.SectionID  AND p.PostDate BETWEEN @BegReportDate AND @EndReportDate) AS Replies,
			(SELECT COUNT(*) FROM (SELECT DISTINCT UserID FROM cs_Posts p WHERE p.SectionID = s.SectionID  AND p.PostDate BETWEEN @BegReportDate AND @EndReportDate) AS u) AS Users
				FROM cs_Sections s
				WHERE s.ApplicationType = 0
			) AS tr
		WHERE tr.Threads > 0 or tr.Replies > 0
		ORDER BY tr.Replies DESC
	END
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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