88 lines
3.4 KiB
Plaintext
88 lines
3.4 KiB
Plaintext
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
|