ALTERPROCEDURE[dbo].cs_BlogActivityNightlyJob AS SETTransactionIsolationLevelReadUNCOMMITTED BEGIN declare@RunDatedatetime declare@JobRunDatedatetime set@JobRunDate=getdate() set@RunDate=dateadd(d, -1, convert(datetime, (convert(varchar(10), @JobRunDate, 101)))) /**//*101---美国---输入输出(mm/dd/yyyy)*/ /**//*先转换成varchar(10),然后再转换成datetime,并在天数上递增-1*/ insertinto[cs_BlogActivityReport] select t.SectionID, t.ThreadID, @RunDateas CoverageDate ,TotalViews = (select t1.TotalViews from dbo.cs_Threads t1 leftouterjoin cs_BlogActivityReport yesterdays on t.ThreadID = yesterdays.ThreadID and yesterdays.CoverageDate =dateadd(d, -1, @RunDate) where t1.ThreadID = t.ThreadID and t1.SectionID = t.SectionID) ,DayViews = (selectcoalesce((t1.TotalViews - yesterdays.TotalViews), t1.TotalViews) from dbo.cs_Threads t1 leftouterjoin cs_BlogActivityReport yesterdays on t.ThreadID = yesterdays.ThreadID and yesterdays.CoverageDate =dateadd(d, -1, @RunDate) where t1.ThreadID = t.ThreadID and t1.SectionID = t.SectionID) ,DayComments = (selectcount(*) from dbo.cs_Posts p1 where p1.ThreadID = t.ThreadID and p1.PostID <> p1.ParentID and p1.ApplicationPostType =4and p1.PostDate between@RunDateanddateadd(d, 1, @RunDate)) ,DayTrackBacks = (selectcount(*) from dbo.cs_Posts p1 where p1.ThreadID = t.ThreadID and p1.PostID <> p1.ParentID and p1.ApplicationPostType =8and p1.PostDate between@RunDateanddateadd(d, 1, @RunDate)) ,IsPost = (selectcasewhen p1.ApplicationPostType =1then1else0endfrom dbo.cs_Posts p1 where p1.ThreadID = t.ThreadID and p1.PostID = p1.ParentID) ,IsArticle = (selectcasewhen p1.ApplicationPostType =2then1else0endfrom dbo.cs_Posts p1 where p1.ThreadID = t.ThreadID and p1.PostID = p1.ParentID) ,JobExecutionTimeStamp =@JobRunDate ,DayPosts = (selectcount(*) from dbo.cs_Posts p1 where p1.ApplicationPostType =1and p1.ThreadID = t.ThreadID and p1.PostID = p1.ParentID and p1.PostDate between@RunDateanddateadd(d, 1, @RunDate)) ,DayArticles = (selectcount(*) from dbo.cs_Posts p1 where p1.ApplicationPostType =2and p1.ThreadID = t.ThreadID and p1.PostID = p1.ParentID and p1.PostDate between@RunDateanddateadd(d, 1, @RunDate)) from dbo.cs_Threads t -- Some existing CS databases have orphaned thread records that should have been deleted, so check to make sure at least one top-level post exists for the thread whereexists(select PostID from dbo.cs_Posts p1 where p1.ThreadID = t.ThreadID and p1.PostID = p1.ParentID) /**//*就是插入数据,博客这块放着以后再来.在主要的GROUP\SECTION\THREAD\POST表中同时保持博客,论坛,相册的数据.*/ END GO
cs_BlogActivityReportAggregate_Get ALTERPROCEDURE[dbo].cs_BlogActivityReportAggregate_Get ( ----总计 @nRecordNumberStartINT ,@nRecordNumberEndINT ,@BegReportDateDateTime ,@EndReportDateDateTime ,@PagedBIT ) 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@totalRecordsINT -------------------------------------------------------------------- -- Define the table to do the filtering and paging(过滤和页面调度) -------------------------------------------------------------------- DECLARE@tblTempDataTABLE ( nID INTIDENTITY ,DayViews INT ,DayComments INT ,DayTrackbacks INT ,DayPosts INT ,DayArticles INT ,ApplicationKey varchar(100) ) INSERTINTO@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 INNERJOIN cs_Sections sec ON sec.SectionID = bar.SectionID WHERE ApplicationType =1 AND bar.CoverageDate between@BegReportDateand@EndReportDate AND DayViews >0 GROUPBY bar.SectionID ORDERBY DayViews DESC /**//*从cs_BlogActivityReport表中统计数据插入临时表中*/ SET@totalRecords=@@rowcount ------------------------------------------------------------------------------------- SELECT DayViews ,DayComments ,DayTrackbacks ,DayPosts ,DayArticles ,ApplicationKey FROM @tblTempData WHERE nID BETWEEN@nRecordNumberStartAND@nRecordNumberEnd ORDERBY nID ASC /**//*从临时表查询数据*/ --Return Record Count --返回总记录 SELECT@totalRecords --Return TotalBlogs --返回总博客 SELECTCOUNT(SectionID) FROM cs_Sections WHERE ApplicationType =1 --Return TotalEnabledBlogs --返回总可用博客 SELECTCOUNT(SectionID) FROM cs_Sections WHERE ApplicationType =1AND IsActive =1 --Return TotalDisabledBlogs --返回总不可用博客(不活动) SELECTCOUNT(SectionID) FROM cs_Sections WHERE ApplicationType =1AND 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 INNERJOIN cs_Sections sec ON sec.SectionID = bar.SectionID WHERE ApplicationType =1 AND bar.CoverageDate between@BegReportDateand@EndReportDate GROUPBY bar.SectionID ORDERBY TotalViews DESC END END GO
cs_BlogActivityReportRecords_Get ALTERPROCEDURE[dbo].cs_BlogActivityReportRecords_Get ( @nRecordNumberStartINT ,@nRecordNumberEndINT ,@BegReportDateDateTime ,@EndReportDateDateTime ) AS BEGIN DECLARE@totalRecordsINT -------------------------------------------------------------------- -- Define the table to do the filtering and paging -------------------------------------------------------------------- /**//*创建临时表并添加数据*/ DECLARE@tblTempDataTABLE ( nID INTIDENTITY ,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) ) INSERTINTO@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@BegReportDateand@EndReportDate ORDERBY 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@nRecordNumberStartAND@nRecordNumberEnd ORDERBY nID ASC -- Return totalRecords SELECT@totalRecords END GO