-
/******
-
* 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, -- TreeStructure (Node) to be moved
-
@ParentTreeStructureId int, -- Target TreeStructure to be moved to
-
@INDEX int -- Place where TreeStructure should be moved to
-
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
Recent Comments