USE [VEPROMS]
GO
/****** Object:  StoredProcedure [dbo].[addItemSiblingBefore]    Script Date: 01/25/2010 09:38:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
	
ALTER PROCEDURE [dbo].[addItemSiblingBefore]
(
	@ItemID int=null,	@Number nvarchar(30)=null,	@Text nvarchar(MAX)=null,	@FormatID int=null,	@Config nvarchar(MAX)=null,
	@Type int=null, @DTS datetime,	@UserID nvarchar(100), @newItemID int output
)
WITH EXECUTE AS OWNER
AS
BEGIN TRY -- Try Block
	BEGIN TRANSACTION
	DECLARE @PreviousID int, @ContentID int,	@newLastChanged timestamp, @NewType int
  SELECT @PreviousID = [PreviousID], @NewType=ISNULL(@Type,[Type])
		FROM [ITEMS] II
		JOIN [CONTENTS] CC ON CC.[ContentID]=II.[CONTENTID]
		WHERE [ItemID]=@ItemID
	EXECUTE AddContent @Number, @Text, @NewType, @FormatID, @Config, @DTS, @UserID, @ContentID output, @newLastChanged output
	EXECUTE AddItem @PreviousID, @ContentID,	@DTS,	@UserID ,	@newItemID output,	@newLastChanged output
  UPDATE [ITEMS] SET [PreviousID]=@newItemID where [ItemID]=@ItemID -- Should be UpdateItem
  UPDATE [PARTS] SET [ItemID]=@newItemID where [ItemID]=@ItemID -- Should be UpdatePart
  IF @PreviousID is null -- The step is replacing the first step
  BEGIN
		-- Update content records for the transitions
		Update CC
			Set Text = DBO.vefn_FixTransitionText(Text,TT.TransitionID,TT.TranType,TT.ToID,TT.RangeID,@ItemID,@newItemID)
		From CONTENTS CC
			JOIN Transitions TT ON TT.FromID = CC.ContentID
			WHERE TT.ToID = @ItemID OR TT.RangeID = @ItemID
		-- Update transitions that pointed to @ItemID to point to @newItemID
		Update TRANSITIONS
			Set ToID = CASE ToID WHEN @ItemID THEN @newItemID ELSE ToID END,
				RangeID = CASE RangeID WHEN @ItemID THEN @newItemID ELSE RangeID END
				WHERE ToID = @ItemID OR RangeID = @ItemID
  END
  ELSE -- Check for Transitions that point to the Next Step
  BEGIN
		-- Get a list of Transitions which need to change children of @ItemID that point to @NextID
		DECLARE @NextStepTransitions TABLE
		(
			[TransitionID] int PRIMARY KEY,
			[FromID] [int],
			[ToID] [int],
			[RangeID] [int],
			[TranType] [int],
			[Config] [nvarchar](max)
		)
		Insert into @NextStepTransitions select * from vefn_NextStepTransitions(@ItemID,@PreviousID)
		IF (SELECT COUNT(*) from @NextStepTransitions) > 0 -- Next Step Transitions
		BEGIN
			-- Update all transitions in the list to point to @newItemID
			Update TT
				Set TT.ToID = CASE TT.ToID WHEN @ItemID THEN @newItemID ELSE TT.ToID END,
					TT.RangeID = CASE TT.RangeID WHEN @ItemID THEN @newItemID ELSE TT.RangeID END
			From TRANSITIONS TT
				JOIN @NextStepTransitions NS ON NS.TransitionID = TT.TransitionID
			-- Update content records for the transitions
			Update CC
				Set Text = DBO.vefn_FixTransitionText(Text,NS.TransitionID,NS.TranType,NS.ToID,NS.RangeID,@ItemID,@newItemID)
			From CONTENTS CC
				JOIN @NextStepTransitions NS ON NS.FromID = CC.ContentID
		END
	END
  -- Add 'Verification Required' AnnotationType
	DECLARE @typeID int
	SELECT @typeID = TypeID from AnnotationTypes where Name = 'Verification Required'
	IF(@typeID IS NULL)
	BEGIN
		INSERT INTO [AnnotationTypes]	([Name],[UserID])	VALUES	('Verification Required','Volian')
		SELECT @typeID = SCOPE_IDENTITY()
	END
  -- Add "Verification Required" Annotation for each Transition that points to @newItemID or @NextID
	INSERT INTO [Annotations] ([ItemID],[TypeID],[SearchText],[UserID])
		SELECT ItemID, @typeID,'Verify Transition Destination',@UserID
		FROM Items where CONTENTID in (SELECT FromID FROM TRANSITIONS
		where ToID IN(@ItemID,@newItemID) OR RangeID IN(@ItemID,@newItemID))
  -- Transition Text gets updated in ItemInsertExt.cs
	IF( @@TRANCOUNT > 0 ) COMMIT
  PRINT 'Sibling Added Before ' + ltrim(str(@newItemID))
	EXECUTE GetItem @newItemID
END TRY
BEGIN CATCH -- Catch Block
	IF( @@TRANCOUNT = 1 ) ROLLBACK -- Only rollback if top level
 	ELSE IF( @@TRANCOUNT > 1 ) COMMIT -- Otherwise commit.  Top level will rollback
	EXEC vlnErrorHandler
END CATCH