SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

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


/****** Object:  Stored Procedure dbo.ntm_outdent_node    Script Date: 3/8/2001 2:38:42 PM ******/

/****** Object:  Stored Procedure dbo.ntm_outdent_node    Script Date: 3/8/2001 1:26:25 PM ******/

/****** Object:  Stored Procedure dbo.ntm_outdent_node    Script Date: 3/8/2001 11:17:26 AM ******/

/****** Object:  Stored Procedure dbo.ntm_outdent_node    Script Date: 3/7/2001 11:02:49 AM ******/

/****** Object:  Stored Procedure dbo.ntm_outdent_node    Script Date: 3/7/2001 10:14:59 AM ******/

/**
 * Stored Procedure:	dbo.ntm_outdent_node
 * Creation Date:	Jan 1, 2001
 *
 * Outdent a node of a Nested Tree. Nodes can only be outdented one level
 * at a time.
 *
 * Mar 8, 2001 - David Medinets - Updated routine to match outdent business rules of
					onProject.com (and therefore MS Project).
 * 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 9, 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.
 **/

CREATE      PROCEDURE ntm_outdent_node
	@ErrorMessage varchar(250) = NULL OUTPUT	-- * Error message returned to caller object
	,@node_table_name varchar(255) = 'ntm_nodes'
	,@TreeID uniqueidentifier 
	,@OutdentID uniqueidentifier 
	,@switch_to_internal_id bit = 0
	,@str_debug varchar(255) = "NODEBUG"
as begin
	declare @sql_str nvarchar(2000)
	declare @new_parent_id uniqueidentifier 
	declare @outdent_parent_id uniqueidentifier 
	declare @outdent_nleft int
	declare @outdent_nright int
	declare @parent_nleft int
	declare @parent_nright int

	declare @new_parent_id_str nvarchar(50)
	declare @outdent_id_str nvarchar(50)
	declare @outdent_nleft_str nvarchar(50)
	declare @outdent_nright_str nvarchar(50)
	declare @parent_nright_str nvarchar(50)
	declare @outdent_parent_id_str nvarchar(50)
	declare @tree_id_str nvarchar(50)

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

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

	set @tree_id_str = '''' + convert(nvarchar(50), @TreeID) + ''''

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

	begin transaction

	set @sql_str = N'
	select 
		@outdent_parent_id = ParentID
		,@outdent_nleft = nleft
		,@outdent_nright = nright
	from 
		' + @node_table_name + N' (TABLOCK HOLDLOCK) 
	where 
		TreeID = ' + @tree_id_str + N'
		and InternalNodeID = ''' + convert(nvarchar(50), @OutdentID) + ''''

	execute sp_executesql @sql_str, N'@outdent_parent_id uniqueidentifier out, @outdent_nleft int out, @outdent_nright int out', @outdent_parent_id out, @outdent_nleft out, @outdent_nright out

	set @sql_str = N'
	select 
		@new_parent_id = ParentID, @parent_nleft = nleft, @parent_nright = nright
	from 
		' + @node_table_name + N' (TABLOCK HOLDLOCK) 
	where 
		TreeID = ' + @tree_id_str + N'
		and InternalNodeID = ''' + convert(nvarchar(50), @outdent_parent_id) + ''''

	execute sp_executesql @sql_str, N'@new_parent_id uniqueidentifier out, @parent_nleft int out, @parent_nright int out', @new_parent_id out, @parent_nleft out, @parent_nright out

	-- If no records are found then the node is outdented as far as it can go.
	if @parent_nleft is not null begin

		set @outdent_id_str = '''' + convert(nvarchar(50), @outdentid) + ''''

		if @new_parent_id is null 
			set @new_parent_id_str = 'null'
		else
			set @new_parent_id_str = '''' + convert(nvarchar(50), @new_parent_id) + ''''

		if @outdent_parent_id is null 
			set @outdent_parent_id_str = 'null'
		else
			set @outdent_parent_id_str = '''' + convert(nvarchar(50), @outdent_parent_id) + ''''

		set @outdent_nleft_str = convert(nvarchar(50), @outdent_nleft)
		set @outdent_nright_str = convert(nvarchar(50), @outdent_nright)
		set @parent_nright_str = convert(nvarchar(50), @parent_nright)

		-- update the parent task.
		-- update the outdent node and its children.
		set @sql_str = N'
		update 
			' + @node_table_name + N'
		set 
			-- The level of the Parent Node is not changed.
			ntmLevel = case when InternalNodeID = ' + @outdent_parent_id_str + N' then ntmLevel else ntmLevel - 1 end
			,ParentID = case when InternalNodeID = ''' + convert(nvarchar(50), @OutdentID) + ''' then ' + @new_parent_id_str + ' else ParentID end
			,nleft = case
					-- adjust the parent node
					when InternalNodeID = ' + @outdent_parent_id_str + ' then nleft
					when nleft between ' + @outdent_nleft_str + ' and ' + @outdent_nright_str + ' then nleft + 1
					else nleft
				end
			,nright = case 
					-- adjust the parent node.
					when InternalNodeID = ' + @outdent_parent_id_str + N' then ' + @outdent_nleft_str + N'
					-- adjust the outdent node.
					when InternalNodeID = ' + @outdent_id_str + N' then ' + @parent_nright_str + N'
					when nleft between ' + @outdent_nleft_str + ' and ' + @outdent_nright_str + ' then nright + 1
					else nright
				   end
		where 
			TreeID = ' + @tree_id_str + N' and (
			-- this between operator includes the outdent_node and its children.
			(nleft between ' + @outdent_nleft_str + N' and ' + @outdent_nright_str + N')
			-- include the parent node
			OR InternalNodeID = ' + @outdent_parent_id_str + N')'

		execute sp_executesql @sql_str

		if @@Error <> 0 begin
			set @errormessage = 'Error updating children nodes.'
			goto TransactionError
		end
	
	end

	commit transaction
	return(1)

	TransactionError:
	rollback transaction

	ErrorHandler:
	return(-1)
end









GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO