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




CREATE  PROCEDURE [dbo].cs_Post_CreateUpdate
(
	@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,
	@StickyDate datetime,
	@PostType int = 0,
	@PostMedia int = 0,
	@PostDate datetime = null,
	@UserHostAddress nvarchar(32),
	@PropertyNames ntext = null,
	@PropertyValues ntext = null,
	@SettingsID int, 
	@IsTracked bit = 0,
	@PostID int out,
	@ThreadID int=0 out,
	@PostConfiguration int = 0,
	@UpdateSectionStatistics bit = 1,
	@UserTime datetime = null,
	@ApplicationPostType int,
	@PostName nvarchar(256) =  null,
	@ThreadStatus int = 0,
	@Points int = 0,
	@PostDisplayName nvarchar(64) = null,
	@AutoApprove bit = 0
) 
AS
/*
	stored procedure cs_Post_CreateUpdate
	* Procedure now creates new threads here, from an identity column in cs_Threads.
	* To update a Post, ParentID must not equal 0.
	* To delete a post, use forums_Moderate_Posts_Move to move it to the Deleted Forum
  * Default value for ThreadStatus is Not Set (0)
*/

SET NOCOUNT ON
DECLARE @MaxSortOrder int
DECLARE @ParentLevel int
DECLARE @ParentSortOrder int
DECLARE @NextSortOrder int
DECLARE @ModeratedForum bit
DECLARE @EnablePostStatistics bit
DECLARE @TrackThread bit
DECLARE @ContextUserID int
DECLARE @ContextPostAuthor nvarchar(64)

-- Set 'not set' for Sticky and Anouncement posts
IF ((@IsSticky = 1) OR ((@IsLocked = 1) AND ( @StickyDate > DATEADD( Year, 2, getdate() ) )))
	SET @ThreadStatus = 0 -- not set value

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

IF @UserTime IS NULL
	SET @UserTime = GetDate()

-- Get the real username for poster
IF @PostAuthor IS NULL
	SELECT 
		@PostAuthor = UserName
	FROM 
		cs_vw_Users_FullUser  
	WHERE 
		cs_UserID = @UserID and SettingsID = @SettingsID

-- Check if this is an anonymous post (value = 1)
IF (@PostConfiguration & 1) = 1
BEGIN
	-- Anonymous ID
	SELECT @ContextUserID = cs_UserID FROM cs_vw_Users_FullUser WHERE SettingsID = @SettingsID and IsAnonymous = 1

	-- Set the username
	IF(@PostAuthor is not null)
	begin
		SET @ContextPostAuthor = @PostAuthor
	end
	else
	begin
		SET @ContextPostAuthor = ''
	end
END
ELSE
BEGIN
	-- Set the real user ID
	SET @ContextUserID = @UserID

	IF(@PostDisplayName is not null)
	Begin
		-- Set the real username
		SET @ContextPostAuthor = @PostDisplayName
	END
	ELSE
	BEGIN
		SET @ContextPostAuthor = @PostAuthor
	END
END

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

	IF @IsDuplicate = 1
	BEGIN
		SET @PostID = -1	
		RETURN	-- 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 and SettingsID = @SettingsID

-- Is this forum moderated?
SELECT 
	@ModeratedForum = IsModerated, @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 @AutoApprove = 1
BEGIN
	SET @IsApproved = 1
END
ELSE IF @IsApproved = 1 AND @ModeratedForum = 1 AND @SectionID <> 0
BEGIN
	-- ok, this is a moderated forum.  Is this user trusted?  If he is, then the post is approved ; else it is not
	SET @IsApproved = ( 
		SELECT
			ModerationLevel
		FROM
			cs_UserProfile (nolock)
		WHERE
			UserID = @UserID and SettingsID = @SettingsID )
END


-- EAD: Modifying this sproc to insert directly into cs_Threads.  We are no longer keying
-- cs_Threads.ThreadID to be same number as the top PostID for the thread.  This is to allow
-- for the FKs to be put into place.

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,
		ThreadStatus )
	VALUES
		( @SectionID, 
		@PostDate, 
		@UserID, 
		@PostAuthor,
		@PostDate,
		@ContextPostAuthor,
		@ContextUserID, 
		0,	-- MostRecentPostID, which we don't know until after post INSERT below.
		@IsLocked,
		@IsApproved,
		@IsSticky,
		@StickyDate,
		@EmoticonID,
		@SettingsID,
		@ThreadStatus )

	-- Get the new ThreadID
	SELECT 
		@ThreadID = @@IDENTITY
	--FROM
		--cs_Threads
		
	-- 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,
		PostConfiguration,
		UserTime,
		PostName,
		ApplicationPostType,
		Points )
	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,
		@PostConfiguration,
		@UserTime,
		@PostName,
		@ApplicationPostType,
		@Points )
		

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

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

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

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 and SettingsID = @SettingsID

	-- 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  and SettingsID = @SettingsID )

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

		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 and SettingsID = @SettingsID

	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,
		PostConfiguration,
		UserTime,
		PostName,
		ApplicationPostType,
		Points )
	VALUES 
		( @SectionID, 
		@ThreadID, 
		@ParentID, 
		@ParentLevel + 1, 
		@MaxSortOrder,
		@Subject, 
		@UserID, 
		@PostAuthor, 
		@IsApproved, 
		@IsLocked, 
		@Body, 
		@FormattedBody, 
		@PostType, 
		@PostMedia, 
		@PostDate, 
		@UserHostAddress, 
		@EmoticonID,
		@PropertyNames,
		@PropertyValues,
		@SettingsID,
		@PostConfiguration,
		@UserTime,
		@PostName,
		@ApplicationPostType,
		@Points )


	-- 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		
		-- Grab the new PostID and update the ThreadID's info
		SELECT 
			@PostID = @@IDENTITY 
--		FROM 
--			cs_Posts

		-- 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.
		Select @StickyDate = StickyDate FROM cs_Threads where ThreadID = @ThreadID and SettingsID = @SettingsID

		IF @StickyDate < @PostDate
			SET @StickyDate = @PostDate

		UPDATE
			cs_Threads 	
		SET 
			MostRecentPostAuthor = @ContextPostAuthor,
			MostRecentPostAuthorID = @ContextUserID,
			MostRecentPostID = @PostID,
			TotalReplies = TotalReplies + 1, -- (SELECT COUNT(*) FROM cs_Posts WHERE ThreadID = @ThreadID AND IsApproved = 1 AND PostLevel > 1),
			IsLocked = @IsLocked,
			StickyDate = @StickyDate,	-- this makes the thread a sticky/announcement, even if it's a reply.
			ThreadDate = @PostDate
		WHERE
			ThreadID = @ThreadID and SettingsID = @SettingsID
	END
	ELSE
	BEGIN
		-- Moderated Posts: get the new PostID
		SELECT @PostID = @@IDENTITY 
	END

	-- Clean up ThreadsRead (this should work very well now)
	DELETE
		cs_ThreadsRead
	WHERE
		ThreadID = @ThreadID 
		AND UserID != @UserID and SettingsID = @SettingsID
END


-- Update the users tracking for the new post (if needed)
SELECT 
	@TrackThread = EnableThreadTracking 
FROM 
	cs_UserProfile (nolock) 
WHERE 
	UserID = @UserID and SettingsID = @SettingsID

IF @TrackThread = 1 and @IsTracked = 1
	-- If a row already exists to track this thread for this user, do nothing - otherwise add the row
	IF NOT EXISTS ( SELECT ThreadID FROM cs_TrackedThreads (nolock) WHERE ThreadID = @ThreadID AND UserID = @UserID  and SettingsID = @SettingsID)
		INSERT INTO cs_TrackedThreads 
			( ThreadID, UserID, SettingsID)
		VALUES
			( @ThreadID, @UserID, @SettingsID )

COMMIT TRAN
BEGIN TRAN

-- Is this a private post
IF @SectionID = 0
	EXEC cs_PrivateMessages_CreateDelete @UserID, @ThreadID, @SettingsID, 0

-- Update the forum statitics
IF @SectionID > 0 AND @UserID > 0
BEGIN
	IF @IsApproved = 1 AND @EnablePostStatistics = 1
	BEGIN
		EXEC cs_system_UpdateUserPostCount @SectionID, @UserID, @SettingsID
	END

	IF @UpdateSectionStatistics = 1
	BEGIN
		EXEC cs_system_UpdateForum @SectionID, @ThreadID, @PostID, @SettingsID, 1, @PostDisplayName
	END
END


COMMIT TRAN
SET NOCOUNT OFF

SELECT @PostID = @PostID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[cs_Post_CreateUpdate]  TO [public]
GO