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

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

AS
BEGIN
	DECLARE @totalRecords INT

	--------------------------------------------------------------------
    	-- Define the table to do the filtering and paging
    	--------------------------------------------------------------------
    	DECLARE @tblTempData TABLE
        (
        	nID INT IDENTITY
        	,SectionID INT
        	,ThreadID INT
		,CoverageDate DateTime
        	,TotalViews INT
		,DayViews INT
		,DayComments INT
		,DayTrackbacks INT
		,IsPost bit
		,IsArticle bit
		,JobExecutionTimeStamp DateTime
		,PostID INT
		,PostSubject varchar(256)
		,ApplicationPostType INT
		,ApplicationKey varchar(100)
        )
	INSERT INTO @tblTempData
	(
        	SectionID
        	,ThreadID
		,CoverageDate
        	,TotalViews
		,DayViews
		,DayComments
		,DayTrackbacks
		,IsPost
		,IsArticle
		,JobExecutionTimeStamp
		,PostID
		,PostSubject
		,ApplicationPostType
		,ApplicationKey
        )
    	SELECT  SectionID, ThreadID, CoverageDate, TotalViews, DayViews, DayComments, DayTrackBacks, IsPost, IsArticle, JobExecutionTimeStamp,
    		(SELECT  PostID FROM     cs_Posts AS p WHERE  (PostLevel = 1) AND (ThreadID = bar.ThreadID)) AS PostID,
		(SELECT  Subject FROM     cs_Posts AS p WHERE  (PostLevel = 1) AND (ThreadID = bar.ThreadID)) AS PostSubject,
    		(SELECT  ApplicationPostType FROM     cs_Posts AS p WHERE  (PostLevel = 1) AND (ThreadID = bar.ThreadID)) AS ApplicationPostType,
    		(SELECT  ApplicationKey FROM     cs_Sections AS s WHERE  (SectionID = bar.SectionID)) AS ApplicationKey
	FROM     cs_BlogActivityReport AS bar
	WHERE 
		(((SELECT  ApplicationPostType FROM     cs_Posts AS p WHERE  (PostLevel = 1) AND (ThreadID = bar.ThreadID)) = 1)
                OR ((SELECT  ApplicationPostType FROM     cs_Posts AS p WHERE  (PostLevel = 1) AND (ThreadID = bar.ThreadID)) = 2))
                AND bar.CoverageDate between @BegReportDate and @EndReportDate
	ORDER BY
		bar.CoverageDate
	SET @totalRecords = @@rowcount
        ---------------------------------------------------------------------------------------------------------------------------------------
	SELECT
        	SectionID
        	,ThreadID
        	,TotalViews
		,DayViews
		,DayComments
		,DayTrackbacks
		,IsPost
		,IsArticle
		,JobExecutionTimeStamp
		,PostID
		,PostSubject
		,ApplicationPostType
		,ApplicationKey
		,CoverageDate
    	FROM
		@tblTempData 
    	WHERE
		nID BETWEEN @nRecordNumberStart AND @nRecordNumberEnd
    	ORDER BY 
        	nID ASC

-- Return totalRecords
SELECT @totalRecords

END
                                                                                                                                                                                                                                         


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].cs_BlogActivityReportRecords_Get to public
go