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

CREATE  Proc [dbo].cs_Posts_UpdatePostsInCategories
(
	@CategoryList nvarchar(4000) = null,
	@SectionID int,
	@PostID int,
	@SettingsID int = null,
	@UpdateStats bit = 1
)
as

DELETE FROM cs_Posts_InCategories where PostID = @PostID

-- If the post is not in any categories, we have to rebuild the index incase we are deleting a post or removing categories
IF @CategoryList Is Not Null AND LEN(LTRIM(RTRIM(@CategoryList))) > 0
BEGIN
	DECLARE @idoc int
	-- declare @CategoryList nvarchar(4000)
	-- select @CategoryList = "<?xml version=""1.0"" ?><Categories><Category>Test</Category></Categories>"

	EXEC sp_xml_preparedocument @idoc OUTPUT, @CategoryList

	DECLARE @CategoryIDList TABLE
	(
		CategoryID Int
	)

	--Insert Missing Categories
	INSERT INTO cs_Post_Categories (SectionID, [Name], IsEnabled, ParentID, [Description], SettingsID)
		Select 	DISTINCT
			@SectionID, CONVERT(nvarchar(255), X.[text]), 1, 0, null, @SettingsID
		FROM 
			OPENXML(@idoc, '/Categories/Category/', 2) X
		where 
			X.[text] is not null  
			and CONVERT(nvarchar(255), X.[text]) not in (
				Select [Name] FROM cs_Post_Categories where SectionID = @SectionID
			) 
	IF @@ROWCOUNT > 0
		exec [cs_PostCategories_Parents_RebuildIndex] @SectionID

	Insert Into @CategoryIDList (CategoryID) 
	SELECT DISTINCT C.CategoryID 
		FROM OPENXML(@idoc, '/Categories/Category/', 2) X
		inner join cs_Post_Categories C on C.[Name] = Convert(nvarchar(256),X.[text]) collate database_default
				and C.SectionID = @SectionID

		--Insert Entry Categories
		INSERT INTO cs_Posts_InCategories 
			( PostID, CategoryID, SettingsID)
		Select 
			@PostID, C.CategoryID, @SettingsID
		FROM 
			@CategoryIDList C
	
	EXEC sp_xml_removedocument @idoc
END

if(@UpdateStats = 1) --for changes we need to rebuild the entire section stats
Begin
	UPDATE cs_Post_Categories SET
		TotalSubThreads = IsNull(QSUB.posts, 0),
		MostRecentSubPostDate = QSUB.postdate,
		TotalThreads = IsNull(QCURR.posts, 0),
		MostRecentPostDate = QCURR.postdate
	FROM cs_Post_Categories 
	LEFT  JOIN (
	SELECT P.UplevelID CategoryID, COUNT(PIC.PostID) posts, MAX(jP.PostDate) postdate 
	FROM 
		cs_Posts_InCategories PIC 
		INNER JOIN cs_Posts jP ON (jP.PostID = PIC.PostID AND jP.PostID = jP.ParentID) 
		INNER JOIN cs_Post_Categories_Parents P ON PIC.CategoryID = P.CategoryID and P.SectionID = @SectionID
		INNER JOIN cs_Post_Categories C ON C.CategoryID = P.CategoryID
	WHERE jP.IsApproved = 1
	GROUP BY P.UpLevelID
	) QSUB ON cs_Post_Categories.CategoryID = QSUB.CategoryID

	LEFT  JOIN (
	SELECT C.CategoryID CategoryID, COUNT(PIC.PostID) posts, MAX(jP.PostDate) postdate 
	FROM 
		cs_Posts_InCategories PIC 
		INNER JOIN cs_Posts jP ON (jP.PostID = PIC.PostID AND jP.PostID = jP.ParentID) 
		INNER JOIN cs_Post_Categories C ON C.CategoryID = PIC.CategoryID
	WHERE jP.IsApproved = 1
	GROUP BY C.CategoryID
	) QCURR ON cs_Post_Categories.CategoryID = QCURR.CategoryID
	WHERE cs_Post_Categories.SectionID = @SectionID
End

if(@UpdateStats = 3) --this code is only good for additions
Begin

	UPDATE cs_Post_Categories SET
		TotalSubThreads = QSUB.posts,
		MostRecentSubPostDate = QSUB.postdate,
		TotalThreads = QCURR.posts,
		MostRecentPostDate = QCURR.postdate
	FROM cs_Post_Categories JOIN
	(
	SELECT P.UplevelID CategoryID, COUNT(PIC.PostID) posts, MAX(jP.PostDate) postdate 
	FROM 
		@CategoryIDList cid
		inner join cs_Post_Categories C on C.CategoryID = cid.CategoryID
		Inner Join cs_Post_Categories_Parents P on C.CategoryID = P.CategoryID
		INNER JOIN cs_Posts_InCategories PIC on PIC.CategoryID = P.CategoryID
		INNER JOIN cs_Posts jP ON (jP.PostID = PIC.PostID AND jP.PostID = jP.ParentID) 
	WHERE jP.IsApproved = 1
		Group By P.UplevelID
	) QSUB on cs_Post_Categories.CategoryID = QSUB.CategoryID

	JOIN
	(
	SELECT C.CategoryID CategoryID, COUNT(PIC.PostID) posts, MAX(jP.PostDate) postdate 
	FROM 
		@CategoryIDList cid
		inner join cs_Post_Categories C on C.CategoryID = cid.CategoryID
		INNER JOIN cs_Posts_InCategories PIC on C.CategoryID  =  PIC.CategoryID
		INNER JOIN cs_Posts jP ON (jP.PostID = PIC.PostID AND jP.PostID = jP.ParentID)
	WHERE jP.IsApproved = 1
		Group By C.CategoryID
	) QCURR on cs_Post_Categories.CategoryID = QCURR.CategoryID

End


/* This code has been replaced by the update statements above DanB 7-19-2005 */
if(@UpdateStats = 2)
Begin

	-- Update the most recent post dates and total thread stuff for the categories
	declare @UpCategoryID int
	declare @UpPath nvarchar(256)
	DECLARE Categories_Cursor CURSOR FOR
		SELECT CategoryID FROM cs_Post_Categories WHERE SectionID = @SectionID
	
	OPEN Categories_Cursor
	FETCH NEXT FROM Categories_Cursor INTO @UpCategoryID
	
	WHILE @@FETCH_STATUS = 0
	BEGIN
	
		set @UpPath = (select Path from cs_Post_Categories where CategoryID = @UpCategoryID)
	
		UPDATE cs_Post_Categories SET
			TotalThreads = IsNull((SELECT COUNT(PIC.PostID) FROM cs_Posts_InCategories PIC INNER JOIN cs_Posts jP ON (jP.PostID = PIC.PostID AND jP.PostID = jP.ParentID) WHERE PIC.CategoryID = @UpCategoryID AND jP.IsApproved = 1), 0),
			TotalSubThreads = IsNull((SELECT COUNT(P.PostID) FROM cs_Posts P INNER JOIN cs_Post_Categories jC ON (jC.CategoryID = @UpCategoryID OR jC.Path LIKE @UpPath + convert(nvarchar, @UpCategoryID) + '/%') INNER JOIN cs_Posts_InCategories jPIC ON (jPIC.CategoryID = jC.CategoryID) WHERE P.PostID = P.ParentID AND P.PostID = jPIC.PostID AND P.IsApproved = 1), 0),
			MostRecentPostDate = (SELECT MAX(PostDate) FROM cs_Posts_InCategories PIC INNER JOIN cs_Posts jP ON (jP.PostID = PIC.PostID AND jP.PostID = jP.ParentID) WHERE PIC.CategoryID = @UpCategoryID and jP.IsApproved = 1),
			MostRecentSubPostDate = (SELECT MAX(PostDate) FROM cs_Posts P INNER JOIN cs_Post_Categories jC ON (jC.CategoryID = @UpCategoryID OR jC.Path LIKE @UpPath + convert(nvarchar, @UpCategoryID) + '/%') INNER JOIN cs_Posts_InCategories jPIC ON (jPIC.CategoryID = jC.CategoryID) WHERE P.PostID = P.ParentID AND P.PostID = jPIC.PostID AND P.IsApproved = 1)
		WHERE CategoryID = @UpCategoryID
	
		FETCH NEXT FROM Categories_Cursor INTO @UpCategoryID
	END
	
	CLOSE Categories_Cursor
	DEALLOCATE Categories_Cursor
	
END

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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