cs_system_resetforumstatistics.prc

community server 源码

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_system_ResetForumStatistics]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_system_ResetForumStatistics]
GO




CREATE      procedure [dbo].cs_system_ResetForumStatistics
(
	@SectionID int = 0
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
DECLARE @AutoDelete bit
DECLARE @AutoDeleteThreshold int
DECLARE @ThreadID int
DECLARE @PostID int

IF @SectionID = 0
BEGIN
	-- Reset the statistics on all of the forums.

	DECLARE ForumCount_Cursor CURSOR FOR
		SELECT SectionID FROM cs_Sections

	OPEN ForumCount_Cursor
	FETCH NEXT FROM ForumCount_Cursor INTO @SectionID

	WHILE @@FETCH_STATUS = 0
	BEGIN
		EXEC cs_system_ResetForumStatistics @SectionID
		FETCH NEXT FROM ForumCount_Cursor INTO @SectionID
	END

	CLOSE ForumCount_Cursor
	DEALLOCATE ForumCount_Cursor
	
END
ELSE BEGIN

	DECLARE @SettingsID int

	Select @SettingsID = SettingsID FROM cs_Sections where SectionID = @SectionID
    
	-- Finally, perform any auto-delete
	SELECT 
		@AutoDelete = EnableAutoDelete, 
		@AutoDeleteThreshold = AutoDeleteThreshold 
	FROM 
		cs_Sections
	WHERE
		SectionID = @SectionID
	
	-- Do we need to cleanup the forum?
	IF @AutoDelete = 1
	BEGIN

		DECLARE @ThreadIDToDelete int

		DECLARE ThreadDelete_Cursor CURSOR FOR
			SELECT ThreadID FROM cs_Threads WHERE ThreadDate < DateAdd(dd, -@AutoDeleteThreshold, GetDate()) AND LastViewedDate < DateAdd(dd, -@AutoDeleteThreshold, GetDate()) AND StickyDate < DateAdd(dd, -@AutoDeleteThreshold, GetDate()) AND SectionID = @SectionID	

		OPEN ThreadDelete_Cursor
		FETCH NEXT FROM ThreadDelete_Cursor INTO @ThreadIDToDelete
	
		WHILE @@FETCH_STATUS = 0
		BEGIN
			-- delete corresponding posts first
			DELETE cs_Posts WHERE ThreadID = @ThreadIDToDelete
			DELETE cs_Threads WHERE ThreadID = @ThreadIDToDelete
			FETCH NEXT FROM ThreadDelete_Cursor INTO @ThreadIDToDelete
		END
	
		CLOSE ThreadDelete_Cursor
		DEALLOCATE ThreadDelete_Cursor

		exec sp_recompile 'cs_Threads'
		exec sp_recompile 'cs_Posts'
		exec sp_recompile 'cs_SearchBarrel'
	END

	-- Select the most recent post from the forum.
	SELECT TOP 1
		@ThreadID = ThreadID,
		@PostID = PostID
	FROM 
		cs_Posts
	WHERE 
		SectionID = @SectionID AND
		IsApproved = 1
	ORDER BY
		PostID DESC
   
	-- If the thread is null, reset the forum statistics.
	IF @ThreadID IS NULL
		UPDATE
			cs_Sections
		SET
			TotalThreads = 0,
			TotalPosts = (SELECT COUNT(PostID) FROM cs_Posts WHERE SectionID = @SectionID),
			MostRecentPostID = 0,
			MostRecentThreadID = 0,
			MostRecentPostDate = '1/01/1797',
			MostRecentPostAuthorID = 0,
			MostRecentPostSubject = '',
			MostRecentPostAuthor = ''
		WHERE
			SectionID = @SectionID
	ELSE
		EXEC cs_system_UpdateForum @SectionID, @ThreadID, @PostID, @SettingsID

END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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