SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

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



/**
 * Stored Procedure:	dbo.ntm_insert_node
 * Creation Date:	Jan 1, 2001
 *
 * Insert a node into a Nested Tree.
 *
 * Root nodes are inserted when the boundary parameter is not
 * specified.
 *
 * Mar 12, 2001 - David Medinets - Added @add_as_last_child parameter.
 * Mar  7, 2001 - David Medinets - Added @switch_to_internal_id so that applications where
 *					multiple parents aren't needed can ignore the 
 *					InternalNodeID value.
 * Mar  7, 2001 - David Medinets - @ErrorMessage passes errors to calling routine.
 * Mar  6, 2001 - Michael Imhoff - Fixed incorrect alphabetic insertion by finding ParentNLeft value.
 * Mar  5, 2001 - David Medinets - Modifed routine to support single quotes in the node name parameter.
 * Feb 27, 2001 - David Medinets - Modifed routine to support inserting nodes alphabetically.
 * Feb 27, 2001 - David Medinets - Modifed routine to support setting ntmActive field.
 * Feb 27, 2001 - David Medinets - Modifed routine to support ntmLevel field.
 * Feb  8, 2001 - David Medinets - Modifed routine to use dynamic table name
 * Feb  7, 2001 - David Medinets - Modifed routine to use InternalNodeID 
 * Feb  7, 2001 - David Medinets - Modifed routine to support ParentID field.
 * Feb  7, 2001 - David Medinets - Modifed routine to support multple trees in the ntm_nodes table.
 * Feb  6, 2001 - David Medinets - Modifed routine to use nleft as parameters instead of a node's uuid.
 * Feb  6, 2001 - David Medinets - Modifed routine to accept pre-created UUID for new node. This change
 *	lets nodes have more than one parent.
 **/

CREATE       PROCEDURE ntm_insert_node
	@ErrorMessage varchar(2500) = NULL OUTPUT	-- * Error message returned to caller object
	,@node_name varchar(255) = null
	,@TreeID uniqueidentifier = null
	,@BuddyID uniqueidentifier = null
	,@InsertID uniqueidentifier = null
	,@insert_type varchar(255) = "AS_CHILD"
	,@active bit = 1
	,@use_alphabetic_order bit = 0
	,@add_as_last_child bit = 1
	,@switch_to_internal_id bit = 0
	,@node_table_name varchar(255) = 'ntm_nodes'
	,@str_debug varchar(255) = "NODEBUG"
as begin

	DECLARE @next_ntm_left int
	declare @ntmLevel int
	DECLARE @ParentID uniqueidentifier
	declare @sql_str nvarchar(2500)
	declare @parent_id_comparison nvarchar(100)
	declare @parentnleft int

	-- reject null TreeID
	if @TreeID is null begin
		set @ErrorMessage = 'Error - The TreeID can not be null.'
		goto errorhandler
	end

	-- reject invalid insert type.
	if @insert_type <> 'AS_SIBLING' and @insert_type <> 'AS_CHILD' begin
		set @ErrorMessage = 'Error - unknown insert type.'
		goto errorhandler
	end

	set @node_name = replace(@node_name, '''', '''''')

	if @switch_to_internal_id = 1 begin
		set @sql_str = N'
		select
			@BuddyID = InternalNodeID
		from
			' + @node_table_name + N'
		where
			NodeID = ''' + convert(nvarchar(50), @BuddyID) + N'''
		'
		execute sp_executesql @sql_str, N'@BuddyID uniqueidentifier out', @BuddyID out
	end


	-- Let's make sure that the node we want to insert after exists.
	if @BuddyID is not null begin
		declare @count int
		-- select sum(1) from [table] where TreeID = [TreeID] and InteralNodeID = [BuddyID]
		set @sql_str = N'select @count = sum(1) from ' + @node_table_name + N' where TreeID = ''' + convert(nvarchar(50), @TreeID) + N''' and InternalNodeID = ''' + convert(nvarchar(50), @BuddyID) + N''''
		execute sp_executesql @sql_str, N'@count int out', @count out

		if @count = 0 begin
			set @ErrorMessage = 'Error - Unknown Insertion Point Of ' + convert(varchar(30), @BuddyID) + '.'
			goto errorhandler
		end

		if @count > 1 begin
			set @ErrorMessage = 'Error - Too Many Insertion Points Equal To ' + convert(varchar(30), @BuddyID) + '.'
			goto errorhandler
		end

	end

	if @BuddyID is null begin

		-- add the new node to the end of the tree.

		-- select @next_ntm_left = IsNull(max(nright)+1, 1) from ntm_nodes where TreeID = @TreeID
		set @sql_str = N'select @next_ntm_left = IsNull(max(nright)+1, 1) from ' + @node_table_name + N' where TreeID = ''' + convert(nvarchar(50), @TreeID) + N''''
		execute sp_executesql @sql_str, N'@next_ntm_left int out', @next_ntm_left out

		set @ParentID = null
		set @ntmLevel = 0
	end
	else begin
		if @insert_type = 'AS_CHILD' begin

			set @ParentID = @BuddyID

			set @sql_str = N'
			select 
				@ntmLevel = ntmLevel + 1 
				,@next_ntm_left = nleft + 1 
			from 
				' + @node_table_name + N'
			where 
				InternalNodeID = ''' + convert(nvarchar(50), @BuddyID) + N''''

			execute sp_executesql @sql_str, N'@ntmLevel int out, @next_ntm_left int out, @ParentID uniqueidentifier out', @ntmLevel out, @next_ntm_left out, @ParentID out

			if @add_as_last_child = 1 begin
				if @ParentID is null begin
					set @parent_id_comparison = 'ParentID is null'
				end
				else begin
					set @parent_id_comparison = 'ParentID = ''' + convert(nvarchar(50),@ParentID) + N''''
				end

				set @sql_str = N'
				select 
					@next_ntm_left = IsNull(max(nright) + 1, ' + convert(nvarchar(50), @next_ntm_left) + N')
				from 
					' + @node_table_name + N'
				where ' +
					@parent_id_comparison

--	if @node_name = '2' begin
--		set @ErrorMessage = @sql_str
--		goto errorhandler
--		goto TransactionError
--	end
				execute sp_executesql @sql_str, N'@next_ntm_left int out', @next_ntm_left out
			end


		end
		else begin

			set @sql_str = N'
			select 
				@ntmLevel = ntmLevel
				,@next_ntm_left = nright + 1
				,@ParentID = ParentID 
			from 
				' + @node_table_name + N'
			where 
				InternalNodeID = ''' + convert(nvarchar(50), @BuddyID) + N''''
	
			execute sp_executesql @sql_str, N'@ntmLevel int out, @next_ntm_left int out, @ParentID uniqueidentifier out', @ntmLevel out, @next_ntm_left out, @ParentID out
		end


		--set @ErrorMessage = @next_ntm_left
		--goto errorhandler

	end

	begin transaction

		-- 
		if @use_alphabetic_order = 1 begin

			if @ParentID is null begin
				set @parent_id_comparison = 'ParentID is null'
				set @parentnleft = 0
			end
			else begin
				set @parent_id_comparison = 'ParentID = ''' + convert(nvarchar(50),@ParentID) + N''''
				set @sql_str = N'select @parentnleft = nleft from ' + @node_table_name + N' where InternalNodeID = ''' + convert(varchar(50), @ParentID) + N''''
				execute sp_executesql @sql_str, N'@parentnleft int out', @parentnleft out
			end

				set @sql_str = N'
				select 
					@next_ntm_left = IsNull(max(nleft) + 2, @parentnleft + 1)
				from
					' + @node_table_name + N'
				where 
					' + @parent_id_comparison + N'
					and TreeID = ''' + convert(nvarchar(50), @TreeID) + N'''
					and NodeName < ''' + @node_name + N''''

				execute sp_executesql @sql_str, N'@next_ntm_left int out, @BuddyID uniqueidentifier, @TreeID uniqueidentifier, @node_name varchar(255), @parent_id_comparison nvarchar(100)', @next_ntm_left out, @BuddyID, @TreeID, @node_name, @parent_id_comparison


		end

	-- make room for the new node in the tree.
	if @insert_type = 'AS_SIBLING' begin
		set @sql_str = N'
		update 
			' + @node_table_name + N'
		set 
			nleft = nleft + 2
			,nright = nright + 2 
		where 
			TreeID = ''' + convert(nvarchar(50), @TreeID) + N'''
			and nleft >= ' + convert(nvarchar(50), @next_ntm_left)


		execute sp_executesql @sql_str

		if @@Error <> 0 begin
			set @ErrorMessage = 'Error updating boundaries.'
			goto TransactionError
		end
	end

	if @insert_type = 'AS_CHILD' begin
		set @sql_str = N'
		update 
			' + @node_table_name + N'
		set 
			nleft = (case when nleft >= ' + convert(nvarchar(50), @next_ntm_left) + N' then nleft + 2 else nleft end) 
			,nright = (case when nright >= ' + convert(nvarchar(50), @next_ntm_left) + N' then nright + 2 else nright end) 
		where 
			TreeID = ''' + convert(nvarchar(50), @TreeID) + N''' 
			and ((nleft >= ' + convert(nvarchar(50), @next_ntm_left) + N') OR (nright >= ' + convert(nvarchar(50), @next_ntm_left) + N'))'

		execute sp_executesql @sql_str

		if @@Error <> 0 begin
			set @ErrorMessage = 'Error updating boundaries.'
			goto TransactionError
		end
	end

	--if @node_name <> '0' begin
	--	set @ErrorMessage = @ParentID
	--	goto TransactionError
	--end

	-- If an InsertID value was specified, then that UUID is used. Otherwise, a new UUID is created.
	set @sql_str = N'
	insert into 
		' + @node_table_name + N'
	(
		TreeID, NodeID, InternalNodeID, ParentID, NodeName, nleft, nright, ntmActive, ntmLevel
	) values (
		''' + convert(nvarchar(50), @TreeID) + N''','

	-- determine the NodeID
	if @InsertID is null begin
		set @sql_str = @sql_str + N'NewID()' 
	end
	else begin
		set @sql_str = @sql_str + '''' + convert(nvarchar(50), @InsertID) + ''''
	end

	-- determine the InternalNodeID
	set @sql_str = @sql_str + N',NewID()'

	-- determine the ParentID
	if @ParentID is null begin
		set @sql_str = @sql_str + N',null'
	end
	else begin
		set @sql_str = @sql_str + N',''' + convert(nvarchar(50), @ParentID) + N'''' 
	end

	set @sql_str = @sql_str + N',''' + @node_name + N''''
	set @sql_str = @sql_str + N',' + convert(nvarchar(50), @next_ntm_left)
	set @sql_str = @sql_str + N',' + convert(nvarchar(50), @next_ntm_left+1)
	set @sql_str = @sql_str + N',' + convert(nvarchar(50), @active)
	set @sql_str = @sql_str + N',' + convert(nvarchar(50), @ntmLevel) + N')'

--	if @node_name = '2' begin
--		set @ErrorMessage = @sql_str
--		goto TransactionError
--	end

	execute sp_executesql @sql_str

	if @@Error <> 0 begin
		set @ErrorMessage = 'Error inserting new node'
		goto TransactionError
	end

	TransactionCommit:
	commit transaction

	TheEnd:
	return(1)

	TransactionError:
	rollback transaction

	ErrorHandler:
	return(-1)

end




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO