SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

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



/**
 * Stored Procedure:	ntm_move_node
 * Creation Date:	Jan 1, 2001
 *
 * Move a node of a Nested Tree.
 *
 * Mar  9, 2001 - David Medinets - Added @move_to_first_child parameter. When 1, the moved node becomes the first child of the
					buddy node. Otherwise, it becomes the last.
 * Mar  8, 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.
 * Feb 27,2001 - David Medinets - Modifed routine to support ntmLevel field.
 * Feb 9, 2001 - David Medinets - Modifed routine to use dynamic table name
 * 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.
 **/

CREATE     PROCEDURE ntm_move_node
	@ErrorMessage varchar(250) = NULL OUTPUT	-- * Error message returned to caller object
	,@node_table_name varchar(255) = 'ntm_nodes'
	,@TreeID uniqueidentifier 
	,@MoveID uniqueidentifier
	,@BuddyID uniqueidentifier
	,@move_type varchar(10)		-- AS_SIBLING, AS_CHILD
	,@switch_to_internal_id bit = 0
	,@move_to_first_child bit = 1
	,@str_debug varchar(255) = "NODEBUG"

-- @MoveID uniqueidentifier -> GUID of the Node to move
-- @BuddyID uniqueidentifier -> GUID of the Node where to move
-- @move_type -> Realtionship with the node where the node will be node
-- Pass AS_CHILD ,if you want to move the node as the child
-- Pass AS_SIBLING ,if you want to move the node as sibling 

as begin

	declare @sql_str nvarchar(1000)
	declare @ParentID uniqueidentifier 
	declare @nleftNode int
	declare @nRightNode int
	declare @vacuumSize int
	declare @to_move_nleft int
	declare @to_move_nright int
	declare @adding_factor int
	declare @NodesNewLeft int
	declare @VacuumLocation int

	declare @min_level_in_moving_nodes int
	declare @new_level_of_move_node int

	if @TreeID is null begin
		set @errormessage = 'Error - TreeID can not be null.'
		goto ErrorHandler
	end

	if @MoveID is null begin
		set @errormessage = 'Error - MoveID can not be null.'
		goto ErrorHandler
	end

	if @BuddyID is null begin
		set @errormessage = 'Error - BuddyID can not be null.'
		goto ErrorHandler
	end


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


	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

	declare @tree_id_str nvarchar(50)
	declare @move_id_str nvarchar(50)
	declare @buddy_id_str nvarchar(50)
	declare @parent_id_str nvarchar(50)

	set @tree_id_str = '''' + convert(nvarchar(50), @TreeID) + ''''
	set @move_id_str = '''' + convert(nvarchar(50), @MoveID) + ''''
	set @buddy_id_str = '''' + convert(nvarchar(50), @BuddyID) + ''''

	/****
	**  Finding whether the node exists or not
	****/
	
	set @sql_str = N'
	select 
		@nleftNode = nleft, @nRightNode = nright
	from 
		' + @node_table_name + N'
	where 
		TreeID = ' + @tree_id_str + N'
		and InternalNodeID = ' + @move_id_str

	execute sp_executesql @sql_str, N'@nleftNode int out, @nRightNode int out', @nleftNode out, @nRightNode out
	
	if @nleftNode = null or @nRightNode = null
	begin
		set @errormessage = 'The node to move does not exist'
		GOTO  ErrorHandler
	end

	--Getting the Reference Node	
	
	set @sql_str = N'
	select 
		@new_level_of_move_node = case 
				when ''' + @move_type + N'''= ''AS_CHILD'' then ntmLevel + 1 
				when ''' + @move_type + N'''= ''AS_SIBLING'' then ntmLevel
				end
		,@to_move_nleft = nleft
		,@to_move_nright = nright
	from 
		' + @node_table_name + N'
	where 
		TreeID = ' + @tree_id_str + N'
		and InternalNodeID = ' + @buddy_id_str
	
	execute sp_executesql @sql_str, N'@new_level_of_move_node int out,@to_move_nleft int out, @to_move_nright int out', @new_level_of_move_node out, @to_move_nleft out, @to_move_nright out
--	select @to_move_nleft to_move_nleft, @to_move_nright to_move_nright
	
	if @to_move_nleft = null or @to_move_nright = null
	begin
		set @errormessage = 'The reference node does not exist'
		GOTO  ErrorHandler
	end
		
	if @BuddyID = @MoveID
	begin
		set @errormessage = 'This is not a valid move'
		GOTO  ErrorHandler
		
	end
		

	declare @field_to_extract varchar(50)
	if @move_type = 'AS_CHILD' begin
		set @field_to_extract = 'InternalNodeID'
	end
	else begin
		set @field_to_extract = 'ParentID'
	end

	set @sql_str = N'
	select 
		@ParentID = ' + @field_to_extract + N'
	from 
		' + @node_table_name + N'
	where 
		TreeID = ' + @tree_id_str + N'
		and InternalNodeID = ' + @buddy_id_str

	execute sp_executesql @sql_str, N'@ParentID uniqueidentifier out', @ParentID out


	--Finding the the Vacuum size
	
	set @vacuumSize = @nRightNode - @nleftNode + 1	
	
	--Determining where to create Vacuum
	
	if @move_type = 'AS_CHILD' begin
		-- all the nodes that are after the parent node needs to be change
		-- including the right value of the parent

		if @move_to_first_child = 1
			set @VacuumLocation = @to_move_nleft + 1
		else
			set @VacuumLocation = @to_move_nright

		-- The current right value would be the new left for the moved node
		set @NodesNewLeft = @to_move_nleft + 1
	end	
	else if @move_type = 'AS_SIBLING' begin 
		-- all the nodes that are after the node needs to be change
		set @VacuumLocation = @to_move_nright +1 
		-- The right value + 1 would be the new left for the moved node
		set @NodesNewLeft = @to_move_nright + 1
	end
	else begin
		set @errormessage = 'Invalid node_type'
		GOTO  ErrorHandler
	end

	--Creating a vacuum 
	
	begin transaction
	
	set @sql_str = N'
	update 
		' + @node_table_name + N'
	set 
		nleft = case
			-- only change the required left nodes
			when nleft >= ' + convert(nvarchar(50), @VacuumLocation) + ' then nleft + ' + convert(nvarchar(50), @vacuumSize) + N'
			else nleft
			end
		,nright = case 
			-- only change the required right nodes
			when nright >= ' + convert(nvarchar(50), @VacuumLocation) + ' then nright + ' + convert(nvarchar(50), @vacuumSize) + N'
			else nright
			end
	where
		TreeID = ' + @tree_id_str

	print @sql_str
	execute sp_executesql @sql_str

	--Getting the left and right of the node , if it is changed due to the above operations
	
	set @sql_str = N'
	select 
		@nleftNode = nleft, @nRightNode = nright
	from 
		' + @node_table_name + N'
	where 
		InternalNodeID = ' + @move_id_str

	execute sp_executesql @sql_str, N'@nleftNode int out, @nRightNode int out', @nleftNode out, @nRightNode out


	set @sql_str = N'
	select 
		@min_level_in_moving_nodes = min(ntmLevel) 
	from
		' + @node_table_name + N'
	Where 
		TreeID = ' + @tree_id_str + N'
		and nleft >= ' + convert(nvarchar(50), @nleftNode) + N'
		and nright <= ' + convert(nvarchar(50), @nrightNode)

	execute sp_executesql @sql_str, N'@min_level_in_moving_nodes int out', @min_level_in_moving_nodes out
		
	--Calculating the adjustment factor to adjust the node 
	
	set @adding_factor = @NodesNewLeft - @nleftNode

	-- Adjusting the Node and its children.

	if @ParentID is null
		set @parent_id_str = 'null'
	else
		set @parent_id_str = '''' + convert(nvarchar(50), @ParentID) + ''''

	set @sql_str = N'
	Update 
		' + @node_table_name + N'
	set 
		ntmLevel = ntmLevel - @min_level_in_moving_nodes + @new_level_of_move_node
		,ParentID = case when InternalNodeID = ' + @move_id_str + ' then ' + @parent_id_str + ' else ParentID end
		,nleft = nleft + ' + convert(nvarchar(50), @adding_factor) + N'
		,nright = nright + ' + convert(nvarchar(50), @adding_factor) + N'
	Where 
		TreeID = ' + @tree_id_str + N'
		and nleft >= ' + convert(nvarchar(50), @nleftNode) + N'
		and nright <= ' + convert(nvarchar(50), @nrightNode)

	execute sp_executesql @sql_str, N'@min_level_in_moving_nodes int, @new_level_of_move_node int', @min_level_in_moving_nodes, @new_level_of_move_node
	
	--After the above operation the node will be placed to the desired location

	--Removing the vacuum 

	set @sql_str = N'
	Update 
		' + @node_table_name + N'
	set 
		nleft = case
			when nleft > ' + convert(nvarchar(50), @nrightNode) + N' then nleft - ' + convert(nvarchar(50), @vacuumSize) + N'
			else nleft
			end
		,nright = case
			when nright > ' + convert(nvarchar(50), @nrightNode) + N' then nright - ' + convert(nvarchar(50), @vacuumSize) + N'
			else nright
			end
	where
		TreeID = ' + @tree_id_str

	execute sp_executesql @sql_str

	TransactionCommit:
	commit transaction
	return(1)

	TransactionError:
	rollback transaction

	ErrorHandler:
	return(-1)
					
End




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO