/****** 
 * Object:  StoredProcedure [dbo].[sp_MoveTreeStructure]    
 * Script Date: 08/16/2008 19:45:40 
 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_MoveTreeStructure]
	@TreeStructureId int,
	@ParentTreeStructureId int,
	@Index int
AS
BEGIN
	SET NOCOUNT ON;
	
	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

	DECLARE @TranName VARCHAR(20);
	SELECT @TranName = 'MoveTreeStructure';

	BEGIN TRANSACTION @TranName

	BEGIN TRY	
	
		DECLARE @OriginalVolume int
		DECLARE @DiffTarget int

		DECLARE @OriginalLeft int
		DECLARE @OriginalRight int
		DECLARE @OriginalTreeId int

		DECLARE @TargetLeft int
		DECLARE @TargetRight int
		DECLARE @TargetTreeId int
		DECLARE @TargetTreeStructureId int

		DECLARE @LastPlace bit
		SET		@LastPlace = 0

		/******************************************************/	

		/**
		 * If @ParentTreeStructureId is NULL, we asume that 
		 * movement takes place under the same Folder
		 */

			IF (@ParentTreeStructureId IS NULL)
				BEGIN
					SELECT @ParentTreeStructureId = dbo.fn_Edumatic3_GetParentTreeStructure(@TreeStructureId)
				END
		
		/******************************************************/	


		/**
		 * If Movement is under same ParentTreeStructure and forward, 
		 * then Index needs to be +1
		 * For example, if the first child wants to be moved to 
		 * second place, you would give an index of 1
		 * saying the node wants to be inserted between first 
		 * and second child. Because the moved node is the
		 * first child, it really needs to be inserted between child 2 
		 * and 3. Child 2 will become child 1, the inserted
		 * child (used to be child 1) will become child 2 and child 3 
		 * stays 3. Difficult to explain... Draw it and you'll see.
		 */
	    
			IF @ParentTreeStructureId = dbo.fn_Edumatic3_GetParentTreeStructure(@TreeStructureId) 
				AND	@Index >= [dbo].[fn_Edumatic3_GetIndexOf](@TreeStructureId)
				BEGIN
					SET @Index = @Index + 1
				END

		/******************************************************/	

		/**
		 * Retrieve the original left/right and tree values
		 */

			SELECT	@OriginalLeft = LeftValue, @OriginalRight = RightValue, @OriginalTreeId = TreeId
			FROM	TreeStructures 
			WHERE	TreeStructureId = @TreeStructureId
		
		/******************************************************/	

		/**
		 * Retrieve the target left/right and tree values. 
		 * Here there's an exception to be caught.
		 * If the node is to be moved to the last place, 
		 * the @TargetTreeStructureId will be -1. We look for the
		 * Node before which the moved node will be inserted. 
		 * In this case there's is no last Node.
		 * If this happens, we take the last Node and insert the 
		 * Node to be moved after this last node instead of before.
		 */
		
			SET @TargetTreeStructureId = dbo.[fn_Edumatic3_GetChildTreeStructure](@ParentTreeStructureId, @Index)
			
			IF @TargetTreeStructureId <> -1
				BEGIN
					-- Retrieve the target left/right and tree values
					SELECT	@TargetLeft = LeftValue, 
							@TargetRight = RightValue, 
							@TargetTreeId = TreeId
					FROM	TreeStructures 
					WHERE	TreeStructureId = @TargetTreeStructureId
				END
			ELSE
				BEGIN
					SET @TargetTreeStructureId = dbo.[fn_Edumatic3_GetChildTreeStructure](@ParentTreeStructureId, (@Index-1))

					SELECT	@TargetLeft = LeftValue, 
							@TargetRight = RightValue, 
							@TargetTreeId = TreeId
					FROM	TreeStructures 
					WHERE	TreeStructureId = @TargetTreeStructureId

					SET		@LastPlace = 1
				END
			
			SET		@OriginalVolume = @OriginalRight - @OriginalLeft + 1
		
		/******************************************************/	

		/**
		 * Setting the Left and Right Values of TreeStructure 
		 * that needs to be replaced to - Left/Right Value
		 * This needs to be done because these Left and Right 
		 * values may not be changed by queries further down
		 * the road. At the end they are being made positive again. 
		 * Draw some cases if you want this to be clearder to you.
		 */

			UPDATE	TreeStructures 
			SET		LeftValue = -LeftValue,
					RightValue = -RightValue
			WHERE	TreeId = @OriginalTreeId
					AND LeftValue >= @OriginalLeft
					AND RightValue <= @OriginalRight
		
		/******************************************************/	

		/**
		 * If the Node is not inserted to the last place, 
		 * then we insert it before the TargetTreeStructure
		 * If it is to be inserted to the last place, 
		 * we insert it after the TargetTreeStructure
		 */
		IF @LastPlace = 0
			BEGIN
				-- Update + volume left/right >= target
				UPDATE	TreeStructures 
				SET		LeftValue = LeftValue + @OriginalVolume
				WHERE	TreeId = @TargetTreeId 
						AND LeftValue >= @TargetLeft

				UPDATE	TreeStructures 
				SET		RightValue = RightValue + @OriginalVolume  
				WHERE	TreeId = @TargetTreeId 
						AND RightValue >= @TargetLeft

				SET		@DiffTarget = @TargetLeft - @OriginalLeft
			END
		ELSE
			BEGIN
				-- Update + volume left/right >= target
				UPDATE	TreeStructures 
				SET		LeftValue = LeftValue + @OriginalVolume
				WHERE	TreeId = @TargetTreeId 
						AND LeftValue > @TargetRight

				UPDATE	TreeStructures 
				SET		RightValue = RightValue + @OriginalVolume  
				WHERE	TreeId = @TargetTreeId 
						AND RightValue > @TargetRight

				SET		@DiffTarget = @TargetRight + 1 - @OriginalLeft
			END
		
		/******************************************************/	

		/**
		 * For debugging purposes
		 */

				--	SELECT	LTRIM(STR(@OriginalVolume)) AS OrignalVolume, 
				--			LTRIM(STR(@OriginalLeft)) AS OriginalLeft,
				--			LTRIM(STR(@OriginalRight)) AS OriginalRight,
				--			LTRIM(STR(@TargetLeft)) AS TargetLeft,
				--			LTRIM(STR(@TargetRight)) AS TargetRight,
				--			LTRIM(STR(@LastPlace)) AS LastPlace,
				--			LTRIM(STR(@Index)) AS [Index],
				--			LTRIM(STR(@DiffTarget)) AS DiffTarget,
				--			LTRIM(STR(@TargetTreeStructureId)) AS TargetTreeStructureId

		/******************************************************/
		
		/**
		 * Updating the left and right values of the volume that needs to be transported.
		 * They become non negative again.
		 */
		 
			UPDATE	TreeStructures 
			SET		LeftValue = -LeftValue + @DiffTarget, 
					RightValue = -RightValue + @DiffTarget,
					TreeId = @TargetTreeId
			WHERE	LeftValue <= -@OriginalLeft 
					AND RightValue >= -@OriginalRight 
					AND TreeId = @OriginalTreeId
		
		/******************************************************/

		/**
		 * Closing the gap in the original tree (can be the same as target tree)
		 */
		
			UPDATE	TreeStructures 
			SET		LeftValue = LeftValue - @OriginalVolume
			WHERE	TreeId = @OriginalTreeId
					AND LeftValue > @OriginalRight

			UPDATE	TreeStructures 
			SET		RightValue = RightValue - @OriginalVolume
			WHERE	TreeId = @OriginalTreeId
					AND RightValue > @OriginalRight

		/******************************************************/
		
		COMMIT TRANSACTION @TranName

		/******************************************************/

	END TRY

	BEGIN CATCH
		
		/* 	
		 *	If an error occured we Roll Back the transaction 
		 *	and raise an error that VS can catch 
		*/

		ROLLBACK TRANSACTION @TranName
		RAISERROR 50001 'Error while trying to move. Rollback has been executed.'

		/******************************************************/
	END CATCH
END