SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


-- sp_helptext cs_Section_CreateUpdateDelete


CREATE PROCEDURE [dbo].cs_Section_CreateUpdateDelete
(
	@SectionID	int out,
	@DeleteForum	bit = 0,
	@Name		nvarchar(256) = '',
	@Url		nvarchar(512) = '',
	@Description	nvarchar(3000) = '',
	@ParentID	int = 0,
	@SettingsID	int,
	@GroupID	int = 0,
	@IsModerated	bit = 1,
	@IsSearchable   bit = 1,
	@DisplayPostsOlderThan	int = 0,
	@IsActive 	bit = 0,
	@EnablePostStatistics bit = 1,
	@EnablePostPoints bit = 1,
	@EnableAutoDelete bit = 0,
	@EnableAnonymousPosting bit = 0,
	@AutoDeleteThreshold int = 90,
	@SortOrder int = 0,
	@PropertyNames ntext = null,
	@PropertyValues ntext = null,
	@ApplicationKey nvarchar(256) = null,
	@ApplicationType smallint = 0,
	@ForumType int = 0,
	@UserID int,
	@DefaultLanguage nvarchar(32) = null
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
-- Are we deleting the forum?
IF @DeleteForum = 1
BEGIN

	Select @ApplicationKey = 'Deleted Section ' + ApplicationKey FROM cs_Sections where SectionID = @SectionID and SettingsID = @SettingsID

	exec cs_system_ModerationAction_AuditEntry 19, @UserID, null, @UserID, @SectionID, @SettingsID,  @ApplicationKey


           DECLARE @SectionToDeleteParentID int
           SET @SectionToDeleteParentID = (SELECT ParentID FROM cs_Sections WHERE SectionID = @SectionID)

	-- delete the specified forum and all of its posts
	-- first we must remove all the thread tracking rows
	DELETE 
		cs_TrackedThreads
	WHERE 
		ThreadID IN (SELECT DISTINCT ThreadID FROM cs_Threads WHERE SectionID = @SectionID and SettingsID = @SettingsID)

	-- we must remove all of the moderators for this forum
	DELETE 
		cs_Moderators
	WHERE 
		SectionID = @SectionID and SettingsID = @SettingsID

	-- we must remove all roller blog related data
	DELETE
		cs_RollerBlogPost
	FROM
		cs_RollerBlogPost RBP INNER JOIN cs_RollerBlogFeeds RBF ON  RBP.SectionID = RBF.SectionID AND RBP.UrlID = RBF.UrlID
	WHERE
		RBF.SectionID = @SectionID AND RBF.SettingsID = @SettingsID

	DELETE
		cs_RollerBlogFeeds
	WHERE
		SectionID = @SectionID and SettingsID = @SettingsID

	-- now we must remove all of the posts
	DELETE 
		cs_Posts
	WHERE 
		SectionID = @SectionID and SettingsID = @SettingsID

	DELETE cs_weblog_Weblogs Where SectionID = @SectionID and SettingsID = @SettingsID

	-- remove all the explicit forum permissions
	DELETE
		cs_SectionPermissions
	WHERE
		SectionID = @SectionID and SettingsID = @SettingsID

	-- finally we can delete the actual forum
	DELETE 
		cs_Sections
	WHERE 
		SectionID = @SectionID and SettingsID = @SettingsID

	DELETE 
		cs_Posts_InCategories
	WHERE
		CategoryID in (Select CategoryID FROM cs_Post_Categories where SectionID = @SectionID and SettingsID = @SettingsID)

	DELETE 
		cs_Post_Categories
	WHERE
		SectionID = @SectionID and SettingsID = @SettingsID		

	-- Clean up an child sectoins
       UPDATE
		cs_Sections
	SET 
		ParentID = @SectionToDeleteParentID
        WHERE
                ParentID = @SectionID


	RETURN
END

-- Are we updating a forum
IF @SectionID > 0
BEGIN
	-- if we are making the forum non-moderated, remove all forum moderators for this forum
	IF @IsModerated = 0
		DELETE 
			cs_Moderators
		WHERE 
			SectionID = @SectionID and SettingsID = @SettingsID

	-- Update the forum information
	UPDATE 
		cs_Sections 
	SET
		Name = @Name,
		Url = @Url,
		Description = @Description,
		ParentID = @ParentID,
		SettingsID = @SettingsID,
		GroupID = @GroupID,
		IsModerated = @IsModerated,
		IsActive = @IsActive,
		IsSearchable = @IsSearchable,
		DaysToView = @DisplayPostsOlderThan,
		EnablePostStatistics = @EnablePostStatistics,
		EnablePostPoints = @EnablePostPoints,
		EnableAutoDelete = @EnableAutoDelete,
		EnableAnonymousPosting = @EnableAnonymousPosting,
		AutoDeleteThreshold = @AutoDeleteThreshold,
		SortOrder = @SortOrder,
		ApplicationKey = @ApplicationKey,
		ApplicationType = @ApplicationType,
		ForumType = @ForumType,
		PropertyNames = @PropertyNames,
		PropertyValues = @PropertyValues,
		DefaultLanguage = @DefaultLanguage
	WHERE 
		SectionID = @SectionID and SettingsID = @SettingsID
END
ELSE
BEGIN

	if( @SortOrder = 0 ) 
	begin
		select @SortOrder = coalesce(max(SortOrder) + 1, 1) from cs_Sections where GroupID = @GroupID

		if(@SortOrder is null)
		Begin
			Select @SortOrder = 1
		End
	end

	-- Create a new Forum
	INSERT INTO 
		cs_Sections (
			Name, 
			Url,
			Description, 
			ParentID, 
			SettingsID, 
			GroupID, 
			IsModerated, 
			DaysToView, 
			IsActive,
			IsSearchable,
			EnablePostStatistics,
			EnablePostPoints,
			EnableAutoDelete,
			AutoDeleteThreshold,
			SortOrder,
			ApplicationKey,
			ApplicationType,
			ForumType,
			PropertyNames,
			PropertyValues,
			DefaultLanguage
			)
		VALUES (
			@Name,
			@Url,
			@Description,
			@ParentID,
			@SettingsID,
			@GroupID,
			@IsModerated,
			@DisplayPostsOlderThan,
			@IsActive,
			@IsSearchable,
			@EnablePostStatistics,
			@EnablePostPoints,
			@EnableAutoDelete,
			@AutoDeleteThreshold,
			@SortOrder,
			@ApplicationKey, 
			@ApplicationType,
			@ForumType,
			@PropertyNames,
			@PropertyValues,
			@DefaultLanguage
			)
	
	SET @SectionID = @@Identity

	If @ApplicationType = 1
	Begin
		Insert cs_weblog_Weblogs (SectionID, SettingsID) Values (@SectionID, @SettingsID)
	End

END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

