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



CREATE   PROCEDURE [dbo].cs_weblog_Post_Create
(
	@SectionID int,
	@ParentID int,
	@AllowDuplicatePosts bit,
	@DuplicateIntervalInMinutes int = 0,
	@Subject nvarchar(256),
	@UserID int,
	@PostAuthor nvarchar(64) = null,
	@Body ntext,
	@FormattedBody ntext,
	@EmoticonID int = 0,
	@IsLocked bit,
	@IsSticky bit,
	@IsApproved bit = 0,
	@IsTracked bit = 0,
	@StickyDate datetime,
	@PostType int = 0,
	@PostMedia int = 0,
	@PostDate datetime,
	@UserTime datetime,
	@UserHostAddress nvarchar(32),
	@PostName nvarchar(256) = null,
	@TitleUrl nvarchar(256) = null,
	@PostConfig int = 0,
	@BlogPostType tinyint = 1,
	@Categories nvarchar(4000) = null,
	@PropertyNames ntext = null,
	@PropertyValues ntext = null,
	@SettingsID int,
	@SpamScore int = 0,
	@PostStatus int = 0,
	@PostID int out
) 
AS
SET NOCOUNT ON
DECLARE @MaxSortOrder int
DECLARE @ParentLevel int
DECLARE @ThreadID int
DECLARE @ParentSortOrder int
DECLARE @NextSortOrder int
DECLARE @EnablePostStatistics bit
DECLARE @TrackThread bit
DECLARE @IsComment bit

if(@PostName is not null)
Begin

	if exists(Select p.PostID FROM cs_Posts p where p.SectionID = @SectionID and p.PostName = @PostName and p.ApplicationPostType = @BlogPostType)
	Begin
		Return 2
	End
End

if(@BlogPostType = 1 OR @BlogPostType = 2)
	SET @IsComment = 0
Else
	SET @IsComment = 1

-- set the PostDate
IF @PostDate IS NULL
	SET @PostDate = GetDate()

-- set the username
IF @PostAuthor IS NULL
	SELECT 
		@PostAuthor = UserName
	FROM 
		cs_vw_Users_FullUser 
	WHERE 
		cs_UserID = @UserID

-- Do we care about duplicates?
IF @AllowDuplicatePosts = 0 and @IsComment = 1
BEGIN
	DECLARE @IsDuplicate bit
	exec cs_system_DuplicatePost @UserID, @Body, @DuplicateIntervalInMinutes, @SettingsID, @IsDuplicate output

	IF @IsDuplicate = 1
	BEGIN
		SET @PostID = -1	
		RETURN 1-- Exit with error code.
	END
END

-- we need to get the SectionID, if the ParentID is not null (there should be a SectionID)
IF @SectionID = 0 AND @ParentID <> 0
	SELECT 
		@SectionID = SectionID
	FROM 
		cs_Posts (nolock) 
	WHERE 
		PostID = @ParentID

--Set @ModeratedForum = (@PostConfig & 16)
SELECT 
	@EnablePostStatistics = EnablePostStatistics
FROM 
	cs_Sections (nolock)
WHERE 
	SectionID = @SectionID and SettingsID = @SettingsID

-- Determine if this post will be approved.
-- If the forum is NOT moderated, then the post will be approved by default.
SET NOCOUNT ON
BEGIN TRAN


IF @ParentID = 0	-- parameter indicating this is a top-level post (for a new thread)
BEGIN
	-- First we create a new ThreadID.

	-- check the StickyDate to ensure it's not null
	IF @StickyDate < @PostDate
		SET @StickyDate = @PostDate

	INSERT cs_Threads 	
		( SectionID,
		PostDate, 
		UserID, 
		PostAuthor, 
		ThreadDate, 
		MostRecentPostAuthor, 
		MostRecentPostAuthorID, 	
		MostRecentPostID, 
		IsLocked, 
		IsApproved,
		IsSticky, 
		StickyDate, 
		ThreadEmoticonID,
		SettingsID )
	VALUES
		( @SectionID, 
		@PostDate, 
		@UserID, 
		@PostAuthor,
		@PostDate,
		@PostAuthor,
		@UserID, 
		0,	-- MostRecentPostID, which we don't know until after post INSERT below.
		@IsLocked,
		@IsApproved,
		@IsSticky,
		@StickyDate,
		@EmoticonID,
		@SettingsID )

	-- Get the new ThreadID
	SELECT @ThreadID = @@IDENTITY
		
	-- Now we add the new post
	INSERT cs_Posts 
		( SectionID, 
		ThreadID, 
		ParentID, 
		PostLevel, 
		SortOrder, 
		Subject, 
		UserID, 
		PostAuthor, 
		IsApproved, 
		IsLocked, 
		Body, 
		FormattedBody, 
		PostType, 
		PostMedia, 
		PostDate, 
		IPAddress, 
		EmoticonID,
		PropertyNames,
		PropertyValues,
		SettingsID,
		PostName,
		PostConfiguration,
		UserTime,
		ApplicationPostType,
		PostStatus,
		SpamScore
		
	        )
	VALUES 
		( @SectionID, 
		@ThreadID, 
		0, 	-- ParentID, which we don't know until after INSERT
		1, 	-- PostLevel, 1 marks start/top/first post in thread.
		1, 	-- SortOrder (not in use at this time)
		@Subject, 
		@UserID, 
		@PostAuthor,
		@IsApproved, 
		@IsLocked, 
		@Body, 
		@FormattedBody, 
		@PostType, 
		@PostMedia, 
		@PostDate, 
		@UserHostAddress, 
		@EmoticonID,
		@PropertyNames,
		@PropertyValues,
		@SettingsID,
		@PostName,
		@PostConfig,
		@UserTime,
		@BlogPostType,
		@PostStatus,
		@SpamScore )
		

	-- Get the new PostID
	SELECT 
		@PostID = @@IDENTITY


	-- Update the new Thread with the new PostID
	UPDATE 
		cs_Threads
	SET 
		MostRecentPostID = @PostID
	WHERE 
		ThreadID = @ThreadID

	-- Update the new Post's ParentID with the new PostID
	UPDATE 
		cs_Posts
	SET 
		ParentID = @PostID
	WHERE 
		PostID = @PostID

END
ELSE BEGIN	-- @ParentID <> 0 means there is a reply to an existing post

	-- Get the Post Information for what we are replying to
	SELECT 
		@ThreadID = ThreadID,
		@SectionID = SectionID,
		@ParentLevel = PostLevel,
		@ParentSortOrder = SortOrder
	FROM 
		cs_Posts
	WHERE 
		PostID = @ParentID

	-- Is there another post at the same level or higher?
	SET @NextSortOrder = (
		SELECT 	
			MIN(SortOrder) 
		FROM 
			cs_Posts 
		WHERE 
			PostLevel <= @ParentLevel 
			AND SortOrder > @ParentSortOrder 
			AND ThreadID = @ThreadID )

	IF @NextSortOrder > 0
	BEGIN
		-- Move the existing posts down
		UPDATE 
			cs_Posts
		SET 
			SortOrder = SortOrder + 1
		WHERE 
			ThreadID = @ThreadID
			AND SortOrder >= @NextSortOrder

		SET @MaxSortOrder = @NextSortOrder

	END
   	ELSE BEGIN 	-- There are no posts at this level or above
	
		-- Find the highest sort order for this parent
		SELECT 
			@MaxSortOrder = MAX(SortOrder) + 1
		FROM 
			cs_Posts
		WHERE 
			ThreadID = @ThreadID

	END 

	-- Insert the new post
	INSERT cs_Posts 
		( SectionID, 
		ThreadID, 
		ParentID, 
		PostLevel, 
		SortOrder, 
		Subject, 
		UserID, 
		PostAuthor, 
		IsApproved, 
		IsLocked, 
		Body, 
		FormattedBody, 
		PostType, 
		PostMedia, 
		PostDate, 
		IPAddress, 
		EmoticonID,
		PropertyNames,
		PropertyValues,
		SettingsID,
		PostName,
		PostConfiguration,
		UserTime,
		ApplicationPostType,
		PostStatus,
		SpamScore )
	VALUES 
		( @SectionID, 
		@ThreadID, 
		@ParentID, 
		@ParentLevel + 1, 
		@MaxSortOrder,
		@Subject, 
		@UserID, 
		@PostAuthor, 
		@IsApproved, 
		@IsLocked, 
		@Body, 
		@FormattedBody, 
		@PostType, 
		@PostMedia, 
		@PostDate, 
		@UserHostAddress, 
		@EmoticonID,
		@PropertyNames,
		@PropertyValues,
		@SettingsID,
		@PostName,
		@PostConfig,
		@UserTime,
		@BlogPostType,
		@PostStatus,
		@SpamScore  )


		-- Grab the new PostID and update the ThreadID's info
		SELECT 
			@PostID = @@IDENTITY 


	-- Now check to see if this post is Approved by default.
	-- If so, we go ahead and update the Threads table for the MostRecent items.
	IF @IsApproved = 1 
	BEGIN		
		-- To cut down on overhead, I've elected to update the thread's info
		-- directly from here, without running cs_system_UpdateThread since
		-- I already have all of the information that this sproc would normally have to lookup.
		IF @StickyDate < @PostDate
			SET @StickyDate = @PostDate

		UPDATE
			cs_Threads 	
		SET 
			--MostRecentPostAuthor = @PostAuthor, << We do not track comments
			--MostRecentPostAuthorID = @UserID, << We do not track comments
			--MostRecentPostID = @PostID, << We do not track Comments
			TotalReplies = (SELECT COUNT(*) FROM cs_Posts p WHERE  p.ApplicationPostType & 12 <> 0 and   p.ThreadID = @ThreadID AND p.IsApproved = 1 AND p.PostLevel > 1)
			--IsLocked = @IsLocked,
			--StickyDate = @StickyDate,	-- this makes the thread a sticky/announcement, even if it's a reply.
			--ThreadDate = @PostDate
		WHERE
			ThreadID = @ThreadID
	END
--	ELSE
/*
	BEGIN
		-- Moderated Posts: get the new PostID
		SELECT @PostID = @@IDENTITY 
	END
*/
/*
	-- Clean up ThreadsRead (this should work very well now)
	-- Not tracking in blogs
	DELETE
		cs_ThreadsRead
	WHERE
		ThreadID = @ThreadID 
		AND UserID <> @UserID
*/
END

--Increments posts totals ignoring settings id
IF @EnablePostStatistics = 1 AND @UserID > 0 AND @IsApproved = 1
BEGIN
	EXEC cs_system_UpdateUserPostCount @SectionID, @UserID, @SettingsID
END

IF(@BlogPostType = 1 OR @BlogPostType = 2)
BEGIN
	IF @IsTracked = 1
		BEGIN
		INSERT INTO cs_TrackedThreads ( ThreadID, UserID, SettingsID )
		VALUES	( @ThreadID, @UserID, @SettingsID )
	END
	
	exec cs_Posts_UpdatePostsInCategories @Categories, @SectionID, @PostID, @SettingsID, 1
END

--exec cs_weblog_UpdateContentHistory @SectionID, @BlogPostType

	
COMMIT TRAN

SET NOCOUNT OFF

SELECT @PostID = @PostID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO





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