USE [VEPROMS_JCB1]
GO
/****** Object:  StoredProcedure [dbo].[restoreDeletedItem]    Script Date: 07/07/2011 18:15:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
/*
restoreDeletedItem 2274,1,1940,2
*/

ALTER PROCEDURE [dbo].[restoreDeletedItem]
(
	@ItemID int,
	@DeleteID int,
	@CurrentID int,
	@Level int
)

WITH EXECUTE AS OWNER
AS
	DECLARE @PreviousID int
	DECLARE @NextID int
	DECLARE @ContentID int
	DECLARE @FromType int
DECLARE @ToIDs TABLE
(
	ToID int,
	StepText varchar(max),
	UserID varchar(100)
)
INSERT INTO @ToIDs SELECT tt.ToID,cc.Text,tt.UserID 
FROM tblTransitions tt
INNER JOIN tblItems ii ON tt.FromID = ii.Contentid and tt.DeleteStatus = ii.DeleteStatus
INNER JOIN tblitems iii ON tt.ToID = iii.ItemID
INNER JOIN tblContents cc ON iii.ContentID = cc.ContentID
WHERE tt.DeleteStatus = @DeleteID AND tt.ToID NOT IN (SELECT ItemID FROM Items)
	IF @Level = 0
		BEGIN
			SET @NextID = @CurrentID
			SELECT @PreviousID = PreviousID FROM Items WHERE ItemID = @CurrentID
		END
	IF @Level = 1
		BEGIN
			SELECT @NextID = ItemID FROM Items WHERE PreviousID = @CurrentID
			SET @PreviousID = @CurrentID
		END
	SELECT @ContentID = dbo.[ve_GetPartContentID](@ItemID)
	SELECT @FromType = dbo.[ve_GetPartFromType](@ItemID)
	IF @Level = 2
	BEGIN
		SELECT @NextID = ItemID FROM Parts WHERE ContentID = @ContentID and FromType = @FromType
	END
	UPDATE tblParts SET DeleteStatus = 0 WHERE ItemID IN (SELECT ItemID FROM PartAudits WHERE DeleteStatus = @DeleteID)
	UPDATE tblparts set DeleteStatus = 0 where ContentID = @ContentID and FromType = @FromType
	UPDATE tblContents SET DeleteStatus = 0, ActionDTS = getdate() WHERE ContentID IN (SELECT ContentID FROM ContentAudits WHERE DeleteStatus = @DeleteID)
	UPDATE tblItems SET DeleteStatus = 0,PreviousID = CASE WHEN ItemID = @ItemID THEN @PreviousID ELSE PreviousID END 
	WHERE ItemID IN (SELECT ItemID FROM ItemAudits WHERE DeleteStatus = @DeleteID)
	UPDATE tblGrids SET DeleteStatus = 0 WHERE ContentID IN (SELECT ContentID FROM ContentAudits WHERE DeleteStatus = @DeleteID)
	UPDATE tblEntries SET DeleteStatus = 0 WHERE DocID IN (SELECT DocID FROM EntryAudits WHERE DeleteStatus = @DeleteID)
	UPDATE tblAnnotations SET DeleteStatus = 0, ActionDTS = getdate() WHERE DeleteStatus = @DeleteID	-- ItemID in (SELECT ItemID FROM ItemAudits WHERE DeleteStatus = @DeleteID)
	UPDATE tblROUsages SET DeleteStatus = 0 WHERE DeleteStatus = @DeleteID	-- ContentID IN (SELECT ContentID FROM ContentAudits WHERE DeleteStatus = @DeleteID)
	UPDATE tblTransitions SET DeleteStatus = 0 WHERE DeleteStatus = @DeleteID	-- FromID IN (SELECT ContentID FROM ContentAudits WHERE DeleteStatus = @DeleteID)
	IF @NextID IS NOT NULL
		BEGIN
			UPDATE Items SET PreviousID = @ItemID WHERE ItemID = @NextID
			IF @ContentID IS NOT NULL
				BEGIN
					UPDATE Parts SET ItemID = @ItemID WHERE ItemID = @NextID and ContentID = @ContentID
				END
		END
IF(SELECT COUNT(*) FROM @ToIDs) > 0
BEGIN
	DECLARE @typeID int -- AnnotationType
	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' annotions for transtions that point to deleted step
	INSERT INTO [Annotations] ([ItemID],[TypeID],[SearchText],[UserID])	
	SELECT @ItemID, @typeID,'Verify Transition Destination (deleted step with text of "' + StepText + '")',UserID
	FROM @ToIDs
END

	RETURN