cs_blogactivityreportaggregate_get.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_BlogActivityReportAggregate_Get]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_BlogActivityReportAggregate_Get]
GO

CREATE      PROCEDURE [dbo].cs_BlogActivityReportAggregate_Get
(
	 @nRecordNumberStart INT
    	,@nRecordNumberEnd INT
        ,@BegReportDate DateTime
	,@EndReportDate DateTime
	,@Paged BIT
)

AS
BEGIN

-- declare @Paged BIT
-- set @Paged = 1
-- declare @EndReportDate DateTime
-- set @EndReportDate = '1/1/2008'
-- declare @BegReportDate DateTime
-- set @BegReportDate = '1/1/1900'
-- declare @nRecordNumberStart INT
-- set @nRecordNumberStart = 1
-- declare @nRecordNumberEnd INT
-- set @nRecordNumberEnd = 100


IF @Paged = 1
	BEGIN
		DECLARE @totalRecords INT
		--------------------------------------------------------------------
		-- Define the table to do the filtering and paging
		--------------------------------------------------------------------
		DECLARE @tblTempData TABLE
		(
			nID INT IDENTITY
			,DayViews INT
			,DayComments INT
			,DayTrackbacks INT
			,DayPosts INT
			,DayArticles INT
			,ApplicationKey varchar(100)
		)
		INSERT INTO @tblTempData
		(
			DayViews
			,DayComments
			,DayTrackbacks
			,DayPosts
			,DayArticles
			,ApplicationKey
		)
		SELECT
			SUM(DayViews) AS DayViews, SUM(DayComments) AS DayComments, SUM(DayTrackBacks) AS DayTrackBacks
			,SUM(DayPosts) AS DayPosts, SUM(DayArticles) AS DayArticles
			,(SELECT  ApplicationKey FROM cs_Sections AS s WHERE  (SectionID = bar.SectionID)) AS ApplicationKey
		FROM
			cs_BlogActivityReport AS bar
			INNER JOIN cs_Sections sec ON sec.SectionID = bar.SectionID
		WHERE 
			ApplicationType = 1
		        AND bar.CoverageDate between @BegReportDate and @EndReportDate
			AND DayViews > 0
		GROUP BY
			bar.SectionID
		ORDER BY
			DayViews DESC
		SET @totalRecords = @@rowcount
		-------------------------------------------------------------------------------------
		
		SELECT
			DayViews
			,DayComments
			,DayTrackbacks
			,DayPosts
			,DayArticles
			,ApplicationKey
		FROM
			@tblTempData 
		WHERE
			nID BETWEEN @nRecordNumberStart AND @nRecordNumberEnd
		ORDER BY 
			nID ASC
		
		--Return Record Count
		SELECT @totalRecords

		--Return TotalBlogs
		SELECT COUNT(SectionID)
		FROM cs_Sections
		WHERE ApplicationType = 1

		--Return TotalEnabledBlogs
		SELECT COUNT(SectionID)
		FROM cs_Sections
		WHERE ApplicationType = 1 AND IsActive = 1

		--Return TotalDisabledBlogs
		SELECT COUNT(SectionID)
		FROM cs_Sections
		WHERE ApplicationType = 1 AND IsActive = 0
	END
ELSE
	BEGIN
		SELECT
			(SELECT  ApplicationKey FROM cs_Sections AS s WHERE  (SectionID = bar.SectionID)) AS ApplicationKey
			,SUM(DayViews) As TotalViews
			,SUM(DayComments) As TotalComments
			,SUM(DayTrackBacks) As TotalTrackBacks
			,SUM(DayPosts) As TotalPosts
			,SUM(DayArticles) As TotalArticles
		FROM
			cs_BlogActivityReport AS bar
			INNER JOIN cs_Sections sec ON sec.SectionID = bar.SectionID
		WHERE
			ApplicationType = 1
		        AND bar.CoverageDate between @BegReportDate and @EndReportDate
		GROUP BY
			bar.SectionID
		ORDER BY
			TotalViews DESC
	END
END                                                                                                                                                                                                                               


GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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