ALTER 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, @PostDate datetime, @BloggerTime datetime, @UserHostAddress nvarchar(32), @Excerpt nvarchar(500) = null, @PostName nvarchar(256) = null, @TitleUrl nvarchar(256) = null, @PostConfig int = 0, @BlogPostType tinyint = 1, @CategoryType tinyint = 1, @Categories nvarchar(4000) = null, @PropertyNames ntext = null, @PropertyValues ntext = null, @SettingsID int, @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 b.PostID FROM cs_weblog_Posts b, cs_Posts p where b.PostID = p.PostID and p.SectionID = @SectionID and b.PostName = @PostName and b.BlogPostType = @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 ---------------------------------------- ---Filtering If (@PostAuthor ='Anonymous') begin declare @temp_text varchar(8000) set @temp_text = UPPER(CAST(@Body as varchar(7500))) + UPPER(isnull(@UserHostAddress,'')) + Upper(isnull(@TitleUrl,'')) + Upper(isnull(@PostAuthor,'')) + Upper(isnull(@Subject,'')) + UPPER(CAST(@PropertyValues as varchar(50))) declare @word_buffer varchar(100) declare filterCursor CURSOR LOCAL FAST_FORWARD for select UPPER(fw_WORD) from FILTER_WORD OPEN filterCursor FETCH NEXT FROM filterCursor INTO @word_buffer WHILE @@FETCH_STATUS = 0 BEGIN IF (CHARINDEX(@word_buffer,@temp_text) > 0) begin CLOSE filterCursor DEALLOCATE filterCursor RAISERROR('Disallowed words or phrases detected. I you feel this message is in error, please contact blog owner.',11,1) RETURN 1 end FETCH NEXT FROM filterCursor INTO @word_buffer END CLOSE filterCursor DEALLOCATE filterCursor end ---------------------------------------- -- 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) -- 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 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, PostDate, IPAddress, EmoticonID, PropertyNames, PropertyValues, SettingsID ) 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, @PostDate, @UserHostAddress, @EmoticonID, @PropertyNames, @PropertyValues, @SettingsID ) -- Get the new PostID SELECT @PostID = @@IDENTITY Insert cs_weblog_Posts (PostID, PostName, Excerpt, TitleUrl, PostConfig, BlogPostType, SettingsID, BloggerTime ) VALUES (@PostID, @PostName, @Excerpt, @TitleUrl, @PostConfig, @BlogPostType, @SettingsID, @BloggerTime ) -- 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, PostDate, IPAddress, EmoticonID, PropertyNames, PropertyValues, SettingsID ) VALUES ( @SectionID, @ThreadID, @ParentID, @ParentLevel + 1, @MaxSortOrder, @Subject, @UserID, @PostAuthor, @IsApproved, @IsLocked, @Body, @FormattedBody, @PostType, @PostDate, @UserHostAddress, @EmoticonID, @PropertyNames, @PropertyValues, @SettingsID ) -- Grab the new PostID and update the ThreadID's info SELECT @PostID = @@IDENTITY Insert cs_weblog_Posts (PostID, PostName, Excerpt, TitleUrl, PostConfig, BlogPostType, SettingsID, BloggerTime ) VALUES (@PostID, @PostName, @Excerpt, @TitleUrl, @PostConfig, @BlogPostType, @SettingsID, @BloggerTime ) -- 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 --AND (@PostType = 1 OR @PostType = 2) 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 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 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 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, @CategoryType, @SettingsID END --exec cs_weblog_UpdateContentHistory @SectionID, @BlogPostType COMMIT TRAN SET NOCOUNT OFF SELECT @PostID = @PostID