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



Create Proc [dbo].cs_weblog_UpdateWeblogRecentContent_Job
(
	@SettingsID int
) 

as

SET Transaction Isolation Level Read UNCOMMITTED

Set NOCount On

Update cs_weblog_weblogs
Set 
	MostRecentPostID = P.PostID, 
	MostRecentPostDate = P.UserTime, 
	MostRecentPostName = P.PostName, 
	MostRecentPostAuthorID = P.UserID, 
	MostRecentPostAuthor = P.PostAuthor, 
	MostRecentPostSubject = P.Subject
FROM
	cs_Posts p, 
	(Select Max(p.PostID) as PostID, p.SectionID FROM cs_Posts p where p.ApplicationPostType = 1 and p.UserTime <= getdate() and p.SettingsID = @SettingsID Group by p.SectionID) sub
where
	p.PostID = sub.PostID 
	and cs_weblog_weblogs.SectionID = sub.SectionID 
	and cs_weblog_weblogs.SettingsID = @SettingsID



--We may be able to can the articles part
Update cs_weblog_weblogs
Set 
	MostRecentArticleID = P.PostID , 
	MostRecentArticleDate = P.UserTime, 
	MostRecentArticleName = P.PostName, 
	MostRecentArticleAuthorID = P.UserID, 
	MostRecentArticleAuthor = P.PostAuthor, 
	MostRecentArticleSubject = P.Subject
FROM 
	cs_Posts p, 
	(Select Max(p.PostID) as PostID, p.SectionID FROM cs_Posts p where  p.ApplicationPostType = 2 and p.UserTime <= getdate() and p.SettingsID = @SettingsID Group by p.SectionID) sub
where 
	p.PostID = sub.PostID 
	and cs_weblog_weblogs.SectionID = sub.SectionID 
	and cs_weblog_weblogs.SettingsID = @SettingsID

--Update Count Items
update cs_Weblog_Weblogs
Set
	PostCount = (Select Count(*) FROM cs_Posts p where  p.ApplicationPostType = 1 and p.IsApproved = 1 and p.SectionID =cs_Weblog_Weblogs.SectionID),
	ArticleCount = (Select Count(*) FROM cs_Posts p where  p.ApplicationPostType = 2 and p.IsApproved = 1 and p.SectionID =cs_Weblog_Weblogs.SectionID),
	CommentCount = (Select Count(*) FROM cs_Posts p where  p.ApplicationPostType = 4 and p.IsApproved = 1 and p.SectionID =cs_Weblog_Weblogs.SectionID),
	TrackBackCount = (Select Count(*) FROM cs_Posts p where  p.ApplicationPostType = 8 and p.IsApproved = 1 and p.SectionID =cs_Weblog_Weblogs.SectionID)
where 
	cs_Weblog_Weblogs.SettingsID = @SettingsID


-- Update Weblog Section records. 
-- NOTE: Other applications update the Section table stats via cs_system_UpdateForum, called from cs_Post_CreateUpdate
UPDATE cs_Sections
SET
	TotalPosts = isnull((Select Count(*) FROM cs_Posts p where p.SectionID = cs_Sections.SectionID and p.IsApproved = 1), 0),
	TotalThreads = isnull((Select Count(*) FROM cs_Posts p where p.SectionID = cs_Sections.SectionID and p.IsApproved = 1 and p.PostLevel = 1), 0),
	-- For weblogs, only count feedback that is not approved (ignore posts/articles)
	PostsToModerate = isnull((SELECT Count(*) FROM cs_Posts p WHERE p.SectionID = cs_Sections.SectionID AND p.IsApproved = 0 and p.PostLevel > 1), 0)
WHERE
	cs_Sections.SettingsID = @SettingsID
	and cs_Sections.ApplicationType = 1



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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