ALTER TABLE [Documents] ADD [DocPdf] [varbinary](max) NULL
GO


/****** Object:  StoredProcedure [addDocument] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[addDocument]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [addDocument];
GO
	
CREATE PROCEDURE [dbo].[addDocument]

(
	@LibTitle nvarchar(1024)=null,
	@DocContent varbinary(MAX)=null,
	@DocAscii nvarchar(MAX)=null,
	@Config nvarchar(MAX)=null,
	@DTS datetime,
	@UserID nvarchar(100),
	@FileExtension nvarchar(10),
	@DocPdf varbinary(MAX)=null,
	@newDocID int output,
	@newLastChanged timestamp output
)
WITH EXECUTE AS OWNER
AS
BEGIN TRY -- Try Block
	BEGIN TRANSACTION
	INSERT INTO [Documents]
	(
		[LibTitle],
		[DocContent],
		[DocAscii],
		[Config],
		[DTS],
		[UserID],
		[FileExtension],
		[DocPdf]
	)
	VALUES
	(
		@LibTitle,
		@DocContent,
		@DocAscii,
		@Config,
		@DTS,
		@UserID,
		@FileExtension,
		@DocPdf
	)
	SELECT @newDocID= SCOPE_IDENTITY()
	SELECT 		@newLastChanged=[LastChanged]
		FROM [Documents] WHERE [DocID]=@newDocID
	IF( @@TRANCOUNT > 0 ) COMMIT
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
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addDocument Succeeded'
ELSE PRINT 'Procedure Creation: addDocument Error on Creation'
GO


/****** Object:  StoredProcedure [getContent] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getContent]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getContent];
GO
	
CREATE PROCEDURE [dbo].[getContent]

(
	@ContentID int
)
WITH EXECUTE AS OWNER
AS
	SELECT
		[ContentID],
		[Number],
		[Text],
		[Type],
		[FormatID],
		[Config],
		[DTS],
		[UserID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Details] WHERE [Details].[ContentID]=[Contents].[ContentID]) [DetailCount],
		(SELECT COUNT(*) FROM [Entries] WHERE [Entries].[ContentID]=[Contents].[ContentID]) [EntryCount],
		(SELECT COUNT(*) FROM [Items] WHERE [Items].[ContentID]=[Contents].[ContentID]) [ItemCount],
		(SELECT COUNT(*) FROM [Parts] WHERE [Parts].[ContentID]=[Contents].[ContentID]) [PartCount],
		(SELECT COUNT(*) FROM [RoUsages] WHERE [RoUsages].[ContentID]=[Contents].[ContentID]) [RoUsageCount],
		(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[FromID]=[Contents].[ContentID]) [TransitionCount],
		(SELECT COUNT(*) FROM [ZContents] WHERE [ZContents].[ContentID]=[Contents].[ContentID]) [ZContentCount]
	FROM [Contents]
	WHERE [ContentID]=@ContentID

	SELECT
		[Details].[DetailID],
		[Details].[ContentID],
		[Details].[ItemType],
		[Details].[Text],
		[Details].[Config],
		[Details].[DTS],
		[Details].[UserID],
		[Details].[LastChanged]
	FROM [Details]
	WHERE
		[Details].[ContentID]=@ContentID


	SELECT
		[Entries].[ContentID],
		[Entries].[DocID],
		[Entries].[DTS],
		[Entries].[UserID],
		[Entries].[LastChanged],
		[Documents].[LibTitle] [Document_LibTitle],
		[Documents].[DocContent] [Document_DocContent],
		[Documents].[DocAscii] [Document_DocAscii],
		[Documents].[Config] [Document_Config],
		[Documents].[DTS] [Document_DTS],
		[Documents].[UserID] [Document_UserID],
		[Documents].[FileExtension] [Document_FileExtension],
		[Documents].[DocPdf] [Document_DocPdf]
	FROM [Entries]
		JOIN [Documents]  ON
			[Documents].[DocID]=[Entries].[DocID]
	WHERE
		[Entries].[ContentID]=@ContentID


	SELECT
		[Items].[ItemID],
		[Items].[PreviousID],
		[Items].[ContentID],
		[Items].[DTS],
		[Items].[UserID],
		[Items].[LastChanged]
	FROM [Items]
	WHERE
		[Items].[ContentID]=@ContentID


	SELECT
		[Parts].[ContentID],
		[Parts].[FromType],
		[Parts].[ItemID],
		[Parts].[DTS],
		[Parts].[UserID],
		[Parts].[LastChanged],
		[Items].[PreviousID] [Item_PreviousID],
		[Items].[ContentID] [Item_ContentID],
		[Items].[DTS] [Item_DTS],
		[Items].[UserID] [Item_UserID]
	FROM [Parts]
		JOIN [Items]  ON
			[Items].[ItemID]=[Parts].[ItemID]
	WHERE
		[Parts].[ContentID]=@ContentID


	SELECT
		[RoUsages].[ROUsageID],
		[RoUsages].[ContentID],
		[RoUsages].[ROID],
		[RoUsages].[Config],
		[RoUsages].[DTS],
		[RoUsages].[UserID],
		[RoUsages].[LastChanged],
		[RoUsages].[RODbID],
		[RODbs].[ROName] [RODb_ROName],
		[RODbs].[FolderPath] [RODb_FolderPath],
		[RODbs].[DBConnectionString] [RODb_DBConnectionString],
		[RODbs].[Config] [RODb_Config],
		[RODbs].[DTS] [RODb_DTS],
		[RODbs].[UserID] [RODb_UserID]
	FROM [RoUsages]
		JOIN [RODbs]  ON
			[RODbs].[RODbID]=[RoUsages].[RODbID]
	WHERE
		[RoUsages].[ContentID]=@ContentID


	SELECT
		[Transitions].[TransitionID],
		[Transitions].[FromID],
		[Transitions].[ToID],
		[Transitions].[RangeID],
		[Transitions].[TranType],
		[Transitions].[Config],
		[Transitions].[DTS],
		[Transitions].[UserID],
		[Transitions].[LastChanged],
		[Items_RangeID].[PreviousID] [Item_RangeID_PreviousID],
		[Items_RangeID].[ContentID] [Item_RangeID_ContentID],
		[Items_RangeID].[DTS] [Item_RangeID_DTS],
		[Items_RangeID].[UserID] [Item_RangeID_UserID],
		[Items_ToID].[PreviousID] [Item_ToID_PreviousID],
		[Items_ToID].[ContentID] [Item_ToID_ContentID],
		[Items_ToID].[DTS] [Item_ToID_DTS],
		[Items_ToID].[UserID] [Item_ToID_UserID]
	FROM [Transitions]
		JOIN [Items] [Items_RangeID] ON
			[Items_RangeID].[ItemID]=[Transitions].[RangeID]
		JOIN [Items] [Items_ToID] ON
			[Items_ToID].[ItemID]=[Transitions].[ToID]
	WHERE
		[Transitions].[FromID]=@ContentID


	SELECT
		[ZContents].[ContentID],
		[ZContents].[OldStepSequence],
		[ZContents].[LastChanged]
	FROM [ZContents]
	WHERE
		[ZContents].[ContentID]=@ContentID

	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getContent Succeeded'
ELSE PRINT 'Procedure Creation: getContent Error on Creation'
GO


/****** Object:  StoredProcedure [getDocument] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getDocument]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getDocument];
GO
	
CREATE PROCEDURE [dbo].[getDocument]

(
	@DocID int
)
WITH EXECUTE AS OWNER
AS
	SELECT
		[DocID],
		[LibTitle],
		[DocContent],
		[DocAscii],
		[Config],
		[DTS],
		[UserID],
		[LastChanged],
		[FileExtension],
		[DocPdf],
		(SELECT COUNT(*) FROM [Entries] WHERE [Entries].[DocID]=[Documents].[DocID]) [EntryCount]
	FROM [Documents]
	WHERE [DocID]=@DocID

	SELECT
		[Entries].[ContentID],
		[Entries].[DocID],
		[Entries].[DTS],
		[Entries].[UserID],
		[Entries].[LastChanged],
		[Contents].[Number] [Content_Number],
		[Contents].[Text] [Content_Text],
		[Contents].[Type] [Content_Type],
		[Contents].[FormatID] [Content_FormatID],
		[Contents].[Config] [Content_Config],
		[Contents].[DTS] [Content_DTS],
		[Contents].[UserID] [Content_UserID]
	FROM [Entries]
		JOIN [Contents]  ON
			[Contents].[ContentID]=[Entries].[ContentID]
	WHERE
		[Entries].[DocID]=@DocID

	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getDocument Succeeded'
ELSE PRINT 'Procedure Creation: getDocument Error on Creation'
GO


/****** Object:  StoredProcedure [getDocuments] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getDocuments]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getDocuments];
GO
	
CREATE PROCEDURE [dbo].[getDocuments]

WITH EXECUTE AS OWNER
AS
	SELECT
		[DocID],
		[LibTitle],
		[DocContent],
		[DocAscii],
		[Config],
		[DTS],
		[UserID],
		[LastChanged],
		[FileExtension],
		[DocPdf],
		(SELECT COUNT(*) FROM [Entries] WHERE [Entries].[DocID]=[Documents].[DocID]) [EntryCount]
	FROM [Documents]
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getDocuments Succeeded'
ELSE PRINT 'Procedure Creation: getDocuments Error on Creation'
GO


/****** Object:  StoredProcedure [getEntriesByContentID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getEntriesByContentID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getEntriesByContentID];
GO
	
CREATE PROCEDURE [dbo].[getEntriesByContentID]

(
	@ContentID int
)
WITH EXECUTE AS OWNER
AS

	SELECT
		[Entries].[ContentID],
		[Entries].[DocID],
		[Entries].[DTS],
		[Entries].[UserID],
		[Entries].[LastChanged],
		[Documents].[LibTitle] [Document_LibTitle],
		[Documents].[DocContent] [Document_DocContent],
		[Documents].[DocAscii] [Document_DocAscii],
		[Documents].[Config] [Document_Config],
		[Documents].[DTS] [Document_DTS],
		[Documents].[UserID] [Document_UserID],
		[Documents].[FileExtension] [Document_FileExtension],
		[Documents].[DocPdf] [Document_DocPdf]
	FROM [Entries]
		JOIN [Documents]  ON
			[Documents].[DocID]=[Entries].[DocID]
	WHERE
		[Entries].[ContentID]=@ContentID

	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getEntriesByContentID Succeeded'
ELSE PRINT 'Procedure Creation: getEntriesByContentID Error on Creation'
GO


/****** Object:  StoredProcedure [updateDocument] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[updateDocument]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [updateDocument];
GO
	
CREATE PROCEDURE [dbo].[updateDocument]

(
	@DocID int,
	@LibTitle nvarchar(1024)=null,
	@DocContent varbinary(MAX)=null,
	@DocAscii nvarchar(MAX)=null,
	@Config nvarchar(MAX)=null,
	@DTS datetime,
	@UserID nvarchar(100),
	@LastChanged timestamp,
	@FileExtension nvarchar(10),
	@DocPdf varbinary(MAX)=null,
	@newLastChanged timestamp output
)
WITH EXECUTE AS OWNER
AS
BEGIN TRY -- Try Block
	BEGIN TRANSACTION
	UPDATE [Documents]
		SET
			[LibTitle]=@LibTitle,
			[DocContent]=@DocContent,
			[DocAscii]=@DocAscii,
			[Config]=@Config,
			[DTS]=@DTS,
			[UserID]=@UserID,
			[FileExtension]=@FileExtension,
			[DocPdf]=@DocPdf
		WHERE [DocID]=@DocID AND [LastChanged]=@LastChanged
	IF @@ROWCOUNT = 0
	BEGIN
		IF NOT exists(select * from [Documents] WHERE [DocID]=@DocID)
			RAISERROR('Document record has been deleted by another user', 16, 1)
		ELSE           	
			RAISERROR('Document has been edited by another user', 16, 1)            	
	END

		SELECT 		@newLastChanged=[LastChanged]	
		FROM [Documents] WHERE [DocID]=@DocID		

	IF( @@TRANCOUNT > 0 ) COMMIT
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
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: updateDocument Succeeded'
ELSE PRINT 'Procedure Creation: updateDocument Error on Creation'
GO

/****** Object:  StoredProcedure [getLibraryDocuments] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getLibraryDocuments]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getLibraryDocuments];
GO
	
--getLibraryDocuments
CREATE PROCEDURE [dbo].[getLibraryDocuments]

WITH EXECUTE AS OWNER
AS
SELECT
[DocID],
[LibTitle],
[DocContent],
[DocAscii],
[FileExtension],
[Config],
[DTS],
[UserID],
[LastChanged],
[DocPdf],
(SELECT COUNT(*) FROM [Entries] WHERE [Entries].[DocID]=[Documents].[DocID]) [EntryCount]
FROM [Documents] where [LibTitle] <> '' order by [LibTitle]
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getLibraryDocuments Succeeded'
ELSE PRINT 'Procedure Creation: getLibraryDocuments Error on Creation'
GO


/****** Object:  StoredProcedure [CopyItemAndChildren] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[CopyItemAndChildren]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [CopyItemAndChildren];
GO
	
CREATE PROCEDURE [dbo].[CopyItemAndChildren]
(
	@StartItemID INT,
	@DestFormatID INT,
	@UserID NVARCHAR(100),
	@NewStartItemID int output
)
WITH EXECUTE AS OWNER
AS
BEGIN TRY -- Try Block

--+-----------------------------------------------------------------+
--¦ BEGIN TRANSACTION to make these changes temporary               ¦
--+-----------------------------------------------------------------+
	BEGIN TRANSACTION

DECLARE @Children AS TABLE
(
	ItemID INT PRIMARY KEY,
	NewItemID INT,
	ContentID INT,
	NewContentID INT,
	FormatID INT,
	NewFormatID INT
)
DECLARE @NewDocuments AS TABLE
(
	DocID INT PRIMARY KEY,
	NewDocID INT
)
-- Locals
DECLARE @DTS DATETIME -- DTS of all New Items
SET @DTS = GETDATE() -- Get the current Date and Time
-- Get a list of all of the Items to be copied based upon StartItemID and EndItemID
-- If the StartItemID = EndItemID then it is a single item and it's children
INSERT INTO @Children SELECT ItemID,ItemID,ContentID,ContentID,FormatID,FormatID FROM vefn_ChildItemsRange(@StartItemID,@StartItemID,null)
-- <<< Copy Contents >>>
-- Create new content rows to match the existing rows.  Set the type to the Current ContentID temporarily
-- so that the new content rows can be associated with the existing content rows.
INSERT INTO Contents
  ([Number],[Text],[Type],[FormatID],[Config],[DTS],[UserID])
	select [Number],[Text],[ContentID],[FormatID],[Config],@DTS,@UserID
 from Contents where ContentID in(Select ContentID from @Children)
-- Update the @Children with the NewConentIDs
UPDATE NN set NN.NewContentID = CC.ContentID
From Contents CC
Join @Children NN on NN.ContentID = CC.Type AND CC.DTS = @DTS and CC.UserID = @UserID
-- Reset the Type column in the Contents table with the Type column from the original Records.
UPDATE CC set CC.Type = CC2.Type
From Contents CC
Join @Children NN on NN.NewContentID = CC.ContentID
Join Contents CC2 on NN.ContentID = CC2.ContentID
-- Contents are done
	-- SELECT * From Contents where DTS = @DTS and UserID = @UserID
-- <<< Copy Items >>>
-- Create new item rows based upon the current item rows and the @Children table, with the NewContentIDs
INSERT INTO [Items] ([PreviousID],[ContentID],[DTS],[UserID])
     SELECT II.[PreviousID], -- Leave the PreviousID as is for now
			NN.NewContentID, @DTS, @UserID
			from @Children NN
			join Items II on II.ContentID = NN.ContentID
-- Update the @Children with the NewItemIDs
UPDATE NN set NN.NewItemID = II.ItemID
From Items II
Join @Children NN on NN.NewContentID = II.ContentID AND II.DTS = @DTS and II.UserID = @UserID
DECLARE @NewItemID int
SELECT @NewItemID = NewItemID
	FROM @Children
	WHERE ItemID = @StartItemID
UPDATE NN SET NN.[NewFormatID] = CC.[FormatID]
	FROM @Children NN
	Join vefn_ChildItemsRange(@NewItemID,@NewItemID,@DestFormatID) CC
	ON NN.NewItemID = CC.ItemID
-- The @Children table is now complete
	--SELECT * From @Children
-- Update the PreviousID in the new Item rows, to the new ItemIDs based upon the old ItemIDs
Update II Set II.[PreviousID] = NN.NewItemID
from Items II
Join @Children NN on NN.ItemID = II.PreviousID AND II.DTS = @DTS and II.UserID = @UserID
-- Get the new ItemIDs based upon the old ItemIDs
SELECT @NewStartItemID = NewItemID from @Children where ItemID = @StartItemID
--SELECT @NewEndItemID = NewItemID from @Children where ItemID = @EndItemID
-- Set the PreviousID for the starting Item to null temporarily.
-- This will be adjusted based upon where the step is inserted.
Update Items Set PreviousID = null where ItemID = @NewStartItemID
-- Items are done
	--SELECT * From Items where DTS = @DTS and UserID = @UserID
-- <<< Copy Parts >>>
INSERT INTO [Parts] ([ContentID],[FromType],[ItemID],[DTS],[UserID])
Select NNF.NewContentID,[FromType],NNT.NewItemID, @DTS, @UserID from Parts PP
JOIN @Children NNF on PP.ContentID = NNF.ContentID
JOIN @Children NNT on PP.ItemID = NNT.ItemID
-- Parts are done
	-- SELECT * From Parts where DTS = @DTS and UserID = @UserID
-- <<< Copy Annotations >>>
INSERT INTO [Annotations] ([ItemID],[TypeID],[RtfText],[SearchText],[Config],[DTS],[UserID])
     Select NewItemID, TypeID, RtfText, SearchText, Config, @DTS, @UserID
			from Annotations AA Join @Children NN on AA.ItemID = NN.ItemID
-- Annotations are done
	-- SELECT * From Annotations where DTS = @DTS and UserID = @UserID
-- <<< Copy Documents and Entries>>>
-- logic to create Entries for Library Documents
INSERT INTO [Entries] ([ContentID],[DocID],[DTS],[UserID])
	SELECT NN.[NewContentID],EE.[DocID],@DTS,@UserID
  FROM [Entries] EE JOIN @Children NN on NN.ContentID = EE.ContentID
	JOIN [Documents] DD on EE.[DocID] = DD.[DocID] and Isnull(LibTitle,'') <> ''
-- Logic to create new documents for any documents used that do not have libtitles
INSERT INTO [Documents] ([LibTitle],[DocContent],[DocAscii],[Config],[DTS],[UserID],[FileExtension],[DocPdf])
  OUTPUT CAST(INSERTED.[LibTitle] as INT),INSERTED.[DocID] INTO @NewDocuments
	SELECT str(DD.[DocID]),[DocContent],[DocAscii],[Config],@DTS,@UserID,[FileExtension],[DocPdf]
  FROM [Entries] EE JOIN @Children NN on NN.ContentID = EE.ContentID
	JOIN [Documents] DD on EE.[DocID] = DD.[DocID] and Isnull(LibTitle,'') = ''
UPDATE DD SET LibTitle = ''
	FROM Documents DD JOIN @NewDocuments ND on DD.[DocID] = ND.[NewDocID]
	where DTS = @DTS and UserID = @UserID
-- Documents are Done
	-- SELECT * From Documents where DTS = @DTS and UserID = @UserID
-- Logic to create entries for these newly created documents
INSERT INTO [Entries] ([ContentID],[DocID],[DTS],[UserID])
	SELECT NN.[NewContentID],ND.[NewDocID],@DTS,@UserID
  FROM [Entries] EE JOIN @Children NN on NN.ContentID = EE.ContentID
	JOIN @NewDocuments ND on EE.[DocID] = ND.[DocID]
-- Entries are done
	-- SELECT * From Entries EE JOIN Documents DD on ee.DocID = DD.DocID where EE.DTS = @DTS and EE.UserID = @UserID
-- <<< Copy RoUsages >>>
INSERT INTO [VEPROMS].[dbo].[RoUsages] ([ContentID],[ROID],[Config],[DTS],[UserID],[RODbID])
	SELECT NN.[NewContentID],CAST([ROUsageID] as nvarchar(16)),[Config],@DTS,@UserID,[RODbID]
		FROM [RoUsages] RR Join @Children NN on RR.ContentID = NN.ContentID
-- Update content records for newly copied records to use correct RO usage ids in the RO tags
DECLARE @RowsAffected int
SET @RowsAffected=1
WHILE @RowsAffected > 0
BEGIN
UPDATE CC SET [TEXT] = C2.NewText
	FROM CONTENTS CC
	JOIN (SELECT C1.ContentID, .dbo.vefn_FixROText(C1.Text, CAST([ROID] as int), [ROUsageID]) NewText
	FROM CONTENTS C1
	JOIN @Children NN on C1.ContentID = NN.NewContentID
	JOIN RoUsages RO on NN.NewContentID = RO.ContentID) C2 ON CC.ContentID = C2.ContentID
	WHERE [TEXT] <> C2.NewText
	SET @RowsAffected = @@RowCount
END
UPDATE RON SET [ROID] = ROO.[ROID]
	FROM RoUsages RON
	JOIN @Children NN on RON.ContentID = NN.NewContentID
	JOIN RoUsages ROO on CAST(RON.ROID as int) = ROO.RoUsageID

-- RoUsages are done
	-- SELECT * From RoUsages where DTS = @DTS and UserID = @UserID

-- <<< Copy Transtions >>>
-- Note that the inserted record has the 'TranType' field set to old transitionid.  This is done
-- so that the next step can replace the old transitionid with the new transitionid in the
-- content record's transition tokens.  The TranType gets reset after the content records are
-- updated.
-- Also note that the 'toid/rangeid' may need converted to newly copied ids or may not.  If it's
-- not a range, then it always is converted to new, if there is a new.  If it's a range, both
-- the toid & the rangeid must be new in order for the conversion to be correct.  You cannot
-- have part of the range pointing to the new and part of the range pointing to the original
-- locations.

INSERT INTO .[dbo].[Transitions] ([FromID],[ToID],[RangeID],[TranType],[Config],[DTS],[UserID])
	SELECT NNF.[NewContentID],
		-- if both toid & range are null, use the original toid & rangeid
		CASE WHEN NNT.[NewItemID] is null or NNR.[NewItemID] is null THEN [ToID] ELSE NNT.[NewItemID] END,
		CASE WHEN NNT.[NewItemID] is null or NNR.[NewItemID] is null THEN [RangeID] ELSE NNR.[NewItemID] END,
		[TransitionID],[Config],@DTS,@UserID
	FROM .[dbo].[Transitions] TT
	JOIN @Children NNF on TT.[FromID] = NNF.[ContentID]
	LEFT JOIN @Children NNT on TT.[ToID] = NNT.[ItemID]
	LEFT JOIN @Children NNR on TT.[RangeID] = NNR.[ItemID]
-- -- Update content records for newly copied records to use correct TransitionIDs in the Transition tags
SET @RowsAffected=1
WHILE @RowsAffected > 0
BEGIN
UPDATE CC SET [TEXT] = C2.NewText
	FROM CONTENTS CC
	JOIN (SELECT C1.ContentID, .dbo.vefn_FixTransitionTextForCopy(C1.Text, TRO.TransitionID, TRO.TranType,  TRO.[ToID], TRO.[RangeID],TR.[TransitionID], TR.[ToID], TR.[RangeID], .dbo.vefn_GetNewTranType(NN.FormatID, NN.NewFormatID, TRO.TranType)) NewText
	FROM CONTENTS C1
	JOIN @Children NN on C1.ContentID = NN.NewContentID
	JOIN Transitions TR on NN.NewContentID = TR.FromID
	JOIN Transitions TRO on TR.TranType = TRO.TransitionID) C2 ON CC.ContentID = C2.ContentID
	WHERE [TEXT] <> C2.NewText
	SET @RowsAffected = @@RowCount
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 whose transition format changes
INSERT INTO [Annotations] ([ItemID],[TypeID],[SearchText],[UserID])
		SELECT NN.NewItemID, @typeID,'Verify Transition Format',@UserID
			FROM Transitions TR
			JOIN @Children NN on TR.FromID = NN.NewContentID
			JOIN Transitions TRO on TR.TranType = TRO.TransitionID
			WHERE .dbo.vefn_CompareTranFormat(NN.FormatID, NN.NewFormatID, TRO.TranType) <> 0
UPDATE TR SET TR.[TranType] = .dbo.vefn_GetNewTranType(NN.FormatID, NN.NewFormatID, TRO.TranType)
	FROM Transitions TR
	JOIN @Children NN on TR.FromID = NN.NewContentID
	JOIN Transitions TRO on TR.TranType = TRO.TransitionID
-- Transitions are done
	-- SELECT * From Transitions where DTS = @DTS and UserID = @UserID

	IF( @@TRANCOUNT > 0 ) COMMIT
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

--USE MASTER
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: CopyItemAndChildren Succeeded'
ELSE PRINT 'Procedure Creation: CopyItemAndChildren Error on Creation'
GO