/****** Object:  Alter Table: contentaudits ******/
alter table contentaudits add DeleteDTS datetime
go
-- Display the status of alter table
IF (@@Error = 0) PRINT 'Alter Table: contentaudits Succeeded'
ELSE PRINT 'Alter Table: contentaudits Error on Creation'
GO

/****** Object:  Alter Table: tblcontents ******/
alter table tblcontents add DeleteDTS datetime
go
-- Display the status of alter table
IF (@@Error = 0) PRINT 'Alter Table: tblcontents Succeeded'
ELSE PRINT 'Alter Table: tblcontents Error on Creation'
GO

/****** Object:  Alter Table: annotationaudits ******/
alter table annotationaudits add DeleteDTS datetime
go
-- Display the status of alter table
IF (@@Error = 0) PRINT 'Alter Table: annotationaudits Succeeded'
ELSE PRINT 'Alter Table: annotationaudits Error on Creation'
GO

/****** Object:  Alter Table: tblAnnotations ******/
alter table tblAnnotations add DeleteDTS datetime
go
-- Display the status of alter table
IF (@@Error = 0) PRINT 'Alter Table: tblAnnotations Succeeded'
ELSE PRINT 'Alter Table: tblAnnotations Error on Creation'
GO

/****** Object:  Alter Table: gridaudits ******/
alter table gridaudits add ContentAuditID bigint NOT NULL CONSTRAINT [DF_GridAudits_ContentAuditID]  DEFAULT ((0))
go
-- Display the status of alter table
IF (@@Error = 0) PRINT 'Alter Table: gridaudits Succeeded'
ELSE PRINT 'Alter Table: gridaudits Error on Creation'
GO


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

WITH EXECUTE AS OWNER
AS
	SELECT
		[AuditID],
		[AnnotationID],
		[ItemID],
		[TypeID],
		[RtfText],
		[SearchText],
		[Config],
		[DTS],
		[UserID],
		[DeleteStatus]
	FROM [AnnotationAudits]
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getAnnotationAudits Succeeded'
ELSE PRINT 'Procedure Creation: getAnnotationAudits Error on Creation'
GO

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

(
	@AuditID bigint
)
WITH EXECUTE AS OWNER
AS
	SELECT
		[AuditID],
		[AnnotationID],
		[ItemID],
		[TypeID],
		[RtfText],
		[SearchText],
		[Config],
		[DTS],
		[UserID],
		[DeleteStatus]
	FROM [AnnotationAudits]
	WHERE [AuditID]=@AuditID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getAnnotationAudit Succeeded'
ELSE PRINT 'Procedure Creation: getAnnotationAudit Error on Creation'
GO

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

/*
exec getannotationauditsbyitemid 894
*/	
	
CREATE PROCEDURE [dbo].[getAnnotationAuditsByItemID]
(
	@ItemID int
)

WITH EXECUTE AS OWNER
AS
	SELECT
		[AuditID],
		[AnnotationID],
		[ItemID],
		[TypeID],
		[RtfText],
		[SearchText],
		[Config],
		[DTS],
		[UserID],
		[DeleteStatus]
	FROM [AnnotationAudits]
	WHERE ItemID = @ItemID
	AND AnnotationID NOT IN (SELECT AnnotationID FROM Annotations)
	ORDER BY AuditID DESC
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getAnnotationAuditsByItemID Succeeded'
ELSE PRINT 'Procedure Creation: getAnnotationAuditsByItemID Error on Creation'
GO

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

/*
exec getannotationauditsbyannotationid 53
*/	
	
CREATE PROCEDURE [dbo].[getAnnotationAuditsByAnnotationID]
(
	@AnnotationID int
)

WITH EXECUTE AS OWNER
AS
	SELECT
		[AuditID],
		[AnnotationID],
		[ItemID],
		[TypeID],
		[RtfText],
		[SearchText],
		[Config],
		[DTS],
		[UserID],
		[DeleteStatus]
	FROM [AnnotationAudits]
	WHERE AnnotationID = @AnnotationID
	AND DeleteStatus = 0
	--AND AnnotationID IN (SELECT AnnotationID FROM Annotations)
	ORDER BY AuditID DESC
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getAnnotationAuditsByAnnotationID Succeeded'
ELSE PRINT 'Procedure Creation: getAnnotationAuditsByAnnotationID Error on Creation'
GO

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

WITH EXECUTE AS OWNER
AS
	SELECT
		[AuditID],
		[ContentID],
		[Number],
		[Text],
		[Type],
		[FormatID],
		[Config],
		[DTS],
		[UserID],
		[DeleteStatus]
	FROM [ContentAudits]
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getContentAudits Succeeded'
ELSE PRINT 'Procedure Creation: getContentAudits Error on Creation'
GO

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

(
	@AuditID bigint
)
WITH EXECUTE AS OWNER
AS
	SELECT
		[AuditID],
		[ContentID],
		[Number],
		[Text],
		[Type],
		[FormatID],
		[Config],
		[DTS],
		[UserID],
		[DeleteStatus]
	FROM [ContentAudits]
	WHERE [AuditID]=@AuditID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getContentAudit Succeeded'
ELSE PRINT 'Procedure Creation: getContentAudit Error on Creation'
GO

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

WITH EXECUTE AS OWNER
AS
	SELECT
		[AuditID],
		[ContentID],
		[Number],
		[Text],
		[Type],
		[FormatID],
		[Config],
		[DTS],
		[UserID],
		[DeleteStatus]
	FROM [ContentAudits]
	WHERE ContentID = @ContentID
	ORDER BY AuditID DESC
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getContentAuditsByContentID Succeeded'
ELSE PRINT 'Procedure Creation: getContentAuditsByContentID Error on Creation'
GO

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

WITH EXECUTE AS OWNER
AS
	SELECT
		[AuditID],
		[ContentID],
		[Number],
		[Text],
		[Type],
		[FormatID],
		[Config],
		[DTS],
		[UserID],
		[DeleteStatus]
	FROM [ContentAudits]
	WHERE DeleteStatus = @DeleteStatus
	ORDER BY AuditID DESC
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getContentAuditsByDeleteStatus Succeeded'
ELSE PRINT 'Procedure Creation: getContentAuditsByDeleteStatus Error on Creation'
GO

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

WITH EXECUTE AS OWNER
AS
	SELECT
		[AuditID],
		[DocID],
		[LibTitle],
		[DocContent],
		[DocAscii],
		[Config],
		[DTS],
		[UserID],
		[FileExtension],
		[DeleteStatus]
	FROM [DocumentAudits]
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getDocumentAudits Succeeded'
ELSE PRINT 'Procedure Creation: getDocumentAudits Error on Creation'
GO

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

(
	@AuditID bigint
)
WITH EXECUTE AS OWNER
AS
	SELECT
		[AuditID],
		[DocID],
		[LibTitle],
		[DocContent],
		[DocAscii],
		[Config],
		[DTS],
		[UserID],
		[FileExtension],
		[DeleteStatus]
	FROM [DocumentAudits]
	WHERE [AuditID]=@AuditID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getDocumentAudit Succeeded'
ELSE PRINT 'Procedure Creation: getDocumentAudit Error on Creation'
GO

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

WITH EXECUTE AS OWNER
AS
	SELECT
		[AuditID],
		[DocID],
		[LibTitle],
		[DocContent],
		[DocAscii],
		[Config],
		[DTS],
		[UserID],
		[FileExtension],
		[DeleteStatus]
	FROM [DocumentAudits]
	WHERE DocID = @DocID
	ORDER BY AuditID DESC
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getDocumentAuditsByDocID Succeeded'
ELSE PRINT 'Procedure Creation: getDocumentAuditsByDocID Error on Creation'
GO

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

WITH EXECUTE AS OWNER
AS
	SELECT
		[AuditID],
		[ContentID],
		[DocID],
		[DTS],
		[UserID],
		[DeleteStatus]
	FROM [EntryAudits]
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getEntryAudits Succeeded'
ELSE PRINT 'Procedure Creation: getEntryAudits Error on Creation'
GO

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

(
	@AuditID bigint
)
WITH EXECUTE AS OWNER
AS
	SELECT
		[AuditID],
		[ContentID],
		[DocID],
		[DTS],
		[UserID],
		[DeleteStatus]
	FROM [EntryAudits]
	WHERE [AuditID]=@AuditID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getEntryAudit Succeeded'
ELSE PRINT 'Procedure Creation: getEntryAudit Error on Creation'
GO

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

WITH EXECUTE AS OWNER
AS
	SELECT
		[AuditID],
		[ContentID],
		[DocID],
		[DTS],
		[UserID],
		[DeleteStatus]
	FROM [EntryAudits]
	WHERE ContentID = @ContentID
	ORDER BY AuditID DESC
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getEntryAuditsByContentID Succeeded'
ELSE PRINT 'Procedure Creation: getEntryAuditsByContentID Error on Creation'
GO

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

WITH EXECUTE AS OWNER
AS
	SELECT
		[AuditID],
		[ContentID],
		[Data],
		[Config],
		[DTS],
		[UserID],
		[DeleteStatus],
		[ContentAuditID]
	FROM [GridAudits]
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getGridAudits Succeeded'
ELSE PRINT 'Procedure Creation: getGridAudits Error on Creation'
GO

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

(
	@AuditID bigint
)
WITH EXECUTE AS OWNER
AS
	SELECT
		[AuditID],
		[ContentID],
		[Data],
		[Config],
		[DTS],
		[UserID],
		[DeleteStatus],
		[ContentAuditID]
	FROM [GridAudits]
	WHERE [AuditID]=@AuditID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getGridAudit Succeeded'
ELSE PRINT 'Procedure Creation: getGridAudit Error on Creation'
GO

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

WITH EXECUTE AS OWNER
AS
	SELECT
		[AuditID],
		[ContentID],
		[Data],
		[Config],
		[DTS],
		[UserID],
		[DeleteStatus],
		[ContentAuditID]
	FROM [GridAudits]
	WHERE ContentID = @ContentID
	ORDER BY AuditID DESC
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getGridAuditsByContentID Succeeded'
ELSE PRINT 'Procedure Creation: getGridAuditsByContentID Error on Creation'
GO

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

WITH EXECUTE AS OWNER
AS
	SELECT
		[AuditID],
		[ContentID],
		[ImageType],
		[FileName],
		[Data],
		[Config],
		[DTS],
		[UserID],
		[DeleteStatus]
	FROM [ImageAudits]
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getImageAudits Succeeded'
ELSE PRINT 'Procedure Creation: getImageAudits Error on Creation'
GO

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

(
	@AuditID bigint
)
WITH EXECUTE AS OWNER
AS
	SELECT
		[AuditID],
		[ContentID],
		[ImageType],
		[FileName],
		[Data],
		[Config],
		[DTS],
		[UserID],
		[DeleteStatus]
	FROM [ImageAudits]
	WHERE [AuditID]=@AuditID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getImageAudit Succeeded'
ELSE PRINT 'Procedure Creation: getImageAudit Error on Creation'
GO

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

WITH EXECUTE AS OWNER
AS
	SELECT
		[AuditID],
		[ContentID],
		[ImageType],
		[FileName],
		[Data],
		[Config],
		[DTS],
		[UserID],
		[DeleteStatus]
	FROM [ImageAudits]
	WHERE ContentID = @ContentID
	ORDER BY AuditID DESC
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getImageAuditsByContentID Succeeded'
ELSE PRINT 'Procedure Creation: getImageAuditsByContentID Error on Creation'
GO

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

WITH EXECUTE AS OWNER
AS
	SELECT
		[AuditID],
		[ItemID],
		[PreviousID],
		[ContentID],
		[DTS],
		[UserID],
		[DeleteStatus]
	FROM [ItemAudits]
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getItemAudits Succeeded'
ELSE PRINT 'Procedure Creation: getItemAudits Error on Creation'
GO

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

(
	@AuditID bigint
)
WITH EXECUTE AS OWNER
AS
	SELECT
		[AuditID],
		[ItemID],
		[PreviousID],
		[ContentID],
		[DTS],
		[UserID],
		[DeleteStatus]
	FROM [ItemAudits]
	WHERE [AuditID]=@AuditID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getItemAudit Succeeded'
ELSE PRINT 'Procedure Creation: getItemAudit Error on Creation'
GO

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

WITH EXECUTE AS OWNER
AS
/*
	with Itemz([AuditType],[Level], [ParentID], [Ordinal], [ItemID], [PreviousID], [FromType], [ContentID], [DTS], [UserID], [LastChanged]) as (
	  Select 'root' [AuditType], 0 [Level], 0 [ParentID], 0 [Ordinal], [ItemID], [PreviousID],0 [FromType],[ContentID],[DTS],[UserID],[LastChanged]
		FROM [Items]
	    where [ItemID]= @ItemID
	Union All
	-- Children
	  select 'chld' [AuditType], [Level] + 2,Z.ItemID,0, I.[ItemID], I.[PreviousID], P.[FromType],I.[ContentID],I.[DTS],I.[UserID],I.[LastChanged]
		from Itemz Z
		join Parts P on P.ContentID = Z.ContentID
		join Items I on I.ItemID = P.ItemID
	-- Siblings
	Union All
	  select 'sibl' [AuditType], [Level] ,Z.[ParentID],Z.[Ordinal] +1, I.[ItemID], I.[PreviousID], [FromType],I.[ContentID],I.[DTS],I.[UserID],I.[LastChanged]
		from Itemz Z
		join Items I on I.PreviousID = Z.ItemID
		where Z.[Level] > 0
	)
*/
select * from
(
	--get deleted previous item
	select 0 Level,ia.* from 
	itemaudits ia
	inner join itemaudits iaa on ia.itemid = iaa.previousid
	where iaa.itemid = @ItemID
	and ia.itemid not in (select itemid from items where itemid = ia.itemid)
	and ia.deletestatus in (select max(deletestatus) from itemaudits where itemid = ia.itemid)
	union
	--get deleted next item
	select 1 Level,ia.* from itemaudits ia
	where ia.previousid = @ItemID
	and ia.itemid not in (select itemid from items where itemid = ia.itemid)
	and ia.deletestatus in (select max(deletestatus) from itemaudits where itemid = ia.itemid)
	union
	--get chillins
	select 2 Level,ia.* from itemaudits ia 
	inner join partaudits pa on ia.itemid = pa.itemid 
	inner join items ii on pa.contentid = ii.contentid
	where ii.itemid = @ItemID
	and ia.itemid not in (select itemid from items where itemid = ia.itemid)
	and ia.deletestatus in (select max(deletestatus) from itemaudits where itemid = ia.itemid)
) ia
order by deletestatus desc

--select * from itemz order by parentid,ordinal
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getItemAuditsByItemID Succeeded'
ELSE PRINT 'Procedure Creation: getItemAuditsByItemID Error on Creation'
GO

/****** Object:  StoredProcedure [restoreDeletedItem] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[restoreDeletedItem]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [restoreDeletedItem];
GO
	
CREATE 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
	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 = ContentID FROM PartAudits WHERE ItemID = @ItemID
	UPDATE tblParts SET DeleteStatus = 0 WHERE ItemID IN (SELECT ItemID FROM PartAudits WHERE DeleteStatus = @DeleteID)
	UPDATE tblContents SET DeleteStatus = 0, DeleteDTS = 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, DeleteDTS = getdate() WHERE ItemID in (SELECT ItemID FROM ItemAudits WHERE DeleteStatus = @DeleteID)
	UPDATE tblROUsages SET DeleteStatus = 0 WHERE ContentID IN (SELECT ContentID FROM ContentAudits WHERE DeleteStatus = @DeleteID)
	UPDATE tblTransitions SET DeleteStatus = 0 WHERE 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
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: restoreDeletedItem Succeeded'
ELSE PRINT 'Procedure Creation: restoreDeletedItem Error on Creation'
GO

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

WITH EXECUTE AS OWNER
AS
	UPDATE tblAnnotations SET DeleteStatus = 0, DeleteDTS = getdate() WHERE DeleteStatus = (SELECT DeleteStatus FROM tblAnnotations WHERE AnnotationID = @AnnotationID)
	RETURN
GO

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

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

WITH EXECUTE AS OWNER
AS
	SELECT
		[AuditID],
		[ContentID],
		[FromType],
		[ItemID],
		[DTS],
		[UserID],
		[DeleteStatus]
	FROM [PartAudits]
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getPartAudits Succeeded'
ELSE PRINT 'Procedure Creation: getPartAudits Error on Creation'
GO

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

(
	@AuditID bigint
)
WITH EXECUTE AS OWNER
AS
	SELECT
		[AuditID],
		[ContentID],
		[FromType],
		[ItemID],
		[DTS],
		[UserID],
		[DeleteStatus]
	FROM [PartAudits]
	WHERE [AuditID]=@AuditID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getPartAudit Succeeded'
ELSE PRINT 'Procedure Creation: getPartAudit Error on Creation'
GO

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

WITH EXECUTE AS OWNER
AS
	SELECT
		[AuditID],
		[ContentID],
		[FromType],
		[ItemID],
		[DTS],
		[UserID],
		[DeleteStatus]
	FROM [PartAudits]
	WHERE ContentID = @ContentID
	ORDER BY AuditID DESC
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getPartAuditsByContentID Succeeded'
ELSE PRINT 'Procedure Creation: getPartAuditsByContentID Error on Creation'
GO

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

WITH EXECUTE AS OWNER
AS
	SELECT
		[AuditID],
		[ContentID],
		[FromType],
		[ItemID],
		[DTS],
		[UserID],
		[DeleteStatus]
	FROM [PartAudits]
	WHERE ItemID = @ItemID
	ORDER BY AuditID DESC
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getPartAuditsByItemID Succeeded'
ELSE PRINT 'Procedure Creation: getPartAuditsByItemID Error on Creation'
GO

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

WITH EXECUTE AS OWNER
AS
	SELECT
		[AuditID],
		[ContentID],
		[FromType],
		[ItemID],
		[DTS],
		[UserID],
		[DeleteStatus]
	FROM [PartAudits]
	WHERE DeleteStatus = @DeleteStatus
	ORDER BY AuditID DESC
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getPartAuditsByDeleteStatus Succeeded'
ELSE PRINT 'Procedure Creation: getPartAuditsByDeleteStatus Error on Creation'
GO

/****** Object:  Trigger [tr_tblContents_Update] ******/
ALTER trigger [dbo].[tr_tblContents_Update] on [dbo].[tblContents] for update as 
	begin
		if exists (select * from inserted)
		begin
			if update(Number) or update(Text) or update(Type) or update(FormatID) or update(Config) or update(DeleteStatus)
			begin
				insert into ContentAudits(ContentID,Number,Text,Type,FormatID,Config,DTS,UserID,DeleteStatus,DeleteDTS)
				select dd.ContentID,dd.Number,dd.Text,dd.Type,dd.FormatID,dd.Config,dd.DTS,dd.UserID,dd.DeleteStatus,dd.DeleteDTS
				from deleted dd
				inner join inserted ii on dd.ContentID = ii.ContentID
				where dd.Text not like '%<CROUSGID=%' and dd.Text not like '%<CTID=%'
				--and (select count(*) from contentaudits where contentid = dd.contentid and dts = dd.dts and deletestatus = dd.deletestatus) = 0
				and (select count(*) from contentaudits where contentid = dd.contentid and dts = dd.dts and deletedts = dd.deletedts) = 0
			end
		end
	end
GO

-- Display the status of Trigger alter
IF (@@Error = 0) PRINT 'Trigger Ateration: tr_tblContents_Update Succeeded'
ELSE PRINT 'Trigger Ateration: tr_tblContents_Update Error on Creation'
GO

/****** Object:  Trigger [tr_Transitions_Delete] ******/
ALTER trigger [dbo].[tr_Transitions_Delete] on [dbo].[Transitions] instead of delete as 
	begin
		update ii set DeleteStatus = isnull((select max(DeleteID) from DeleteLog where SPID = @@spid),-1), DTS = getdate(),
		UserID = isnull((select top 1 UserID from DeleteLog where SPID = @@spid order by deleteid desc),ii.UserID)
		from tblTransitions ii inner join deleted dd on dd.TransitionID = ii.TransitionID
		insert into TransitionAudits(TransitionID,FromID,ToID,RangeID,IsRange,TranType,Config,DTS,UserID,DeleteStatus)
		select ii.TransitionID,ii.FromID,ii.ToID,ii.RangeID,ii.IsRange,ii.TranType,ii.Config,ii.DTS,ii.UserID,ii.DeleteStatus
		from tblTransitions ii inner join deleted dd on dd.TransitionID = ii.TransitionID
		where ii.DeleteStatus != 0
	end
GO

-- Display the status of Trigger alter
IF (@@Error = 0) PRINT 'Trigger Ateration: tr_Transitions_Delete Succeeded'
ELSE PRINT 'Trigger Ateration: tr_Transitions_Delete Error on Creation'
GO

/****** Object:  Trigger [tr_ROUsages_Delete] ******/
ALTER trigger [dbo].[tr_ROUsages_Delete] on [dbo].[ROUsages] instead of delete as 
	begin
		update ii set DeleteStatus = isnull((select max(DeleteID) from DeleteLog where SPID = @@spid),-1), DTS = getdate(),
		UserID = isnull((select top 1 UserID from DeleteLog where SPID = @@spid order by deleteid desc),ii.UserID)
		from tblROUsages ii inner join deleted dd on dd.ROUsageID = ii.ROUsageID
		insert into ROUsageAudits(ROUsageID,ContentID,ROID,Config,DTS,UserID,RODbID,DeleteStatus)
		select ii.ROUsageID,ii.ContentID,ii.ROID,ii.Config,ii.DTS,ii.UserID,ii.RODbID,ii.DeleteStatus
		from tblROUsages ii inner join deleted dd on dd.ROUsageID = ii.ROUsageID
		where ii.DeleteStatus > 0
	end
GO

-- Display the status of Trigger alter
IF (@@Error = 0) PRINT 'Trigger Ateration: tr_ROUsages_Delete Succeeded'
ELSE PRINT 'Trigger Ateration: tr_ROUsages_Delete Error on Creation'
GO

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

WITH EXECUTE AS OWNER
AS
	DECLARE @AnnotationID int
	DECLARE @RtfText nvarchar(max)
	DECLARE @SearchText nvarchar(max)
	DECLARE @Config nvarchar(max)
	DECLARE @DTS datetime
	SELECT @AnnotationID = AnnotationID,@RtfText = RtfText,@SearchText = SearchText,@Config = Config,@DTS = DTS
	FROM AnnotationAudits WHERE AuditID = @AuditID
	UPDATE tblAnnotations SET RtfText = @RtfText,SearchText = @SearchText,Config = @Config,DTS = @DTS WHERE AnnotationID = @AnnotationID
	RETURN
GO

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

/****** Object:  TableFunction [vefn_FindTransitionIDs] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[vefn_FindTransitionIDs]') AND OBJECTPROPERTY(id,N'IsTableFunction') = 1)
    DROP FUNCTION [vefn_FindTransitionIDs];
GO
	
CREATE  FUNCTION [dbo].[vefn_FindTransitionIDs](@ContentAuditID int)
RETURNS @IDs TABLE
(
  TransitionID int PRIMARY KEY,
  TransitionAction char(3)
)
WITH EXECUTE AS OWNER
AS
BEGIN
	
	DECLARE @ContentID int
	DECLARE @text varchar(max)
	SELECT @ContentID = ContentID,@text = Text FROM ContentAudits WHERE AuditID = @ContentAuditID
	DECLARE @index1 int
	DECLARE @index2 int
	DECLARE @tid varchar(max)
	DECLARE @trid int
	WHILE (LEN(@text) > 0)
		BEGIN
			SET @index1 = CHARINDEX('#Link:Transition' , @text)
			IF (@index1 > 0)
				BEGIN
					SET @text = RIGHT(@text, (LEN(@text) - @index1))
					SET @index1 = CHARINDEX(' ', @text)
					SET @text = RIGHT(@text, (LEN(@text) - @index1))
					SET @index2 = CHARINDEX(' ', @text)
					SET @tid = LTRIM(LEFT(@text, @index2))
					SET @text = RIGHT(@text, (len(@text) - @index2))
					SET @trid = CAST(@tid AS INT)
					IF EXISTS (SELECT TransitionID FROM tblTransitions WHERE TransitionID = @trid AND DeleteStatus < 0)
						INSERT INTO @IDs VALUES (@trid, 'ADD')
					ELSE 
						INSERT INTO @IDs VALUES (@trid, NULL)
				END
			ELSE
				SET @text = ''
		END
	INSERT INTO @IDs SELECT tt.TransitionID,'DEL' FROM tblTransitions tt LEFT JOIN @IDs ti ON tt.TransitionID = ti.TransitionID WHERE tt.FromID = @ContentID
	AND ti.TransitionID IS NULL
	DELETE FROM @IDs WHERE TransitionAction IS NULL
	RETURN
END
GO

-- Display the status of func creation
IF (@@Error = 0) PRINT 'TableFunction Creation: vefn_FindTransitionIDs Succeeded'
ELSE PRINT 'TableFunction Creation: vefn_FindTransitionIDs Error on Creation'
GO

/****** Object:  TableFunction [vefn_FindROUsageIDs] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[vefn_FindROUsageIDs]') AND OBJECTPROPERTY(id,N'IsTableFunction') = 1)
    DROP FUNCTION [vefn_FindROUsageIDs];
GO
	
CREATE  FUNCTION [dbo].[vefn_FindROUsageIDs](@ContentAuditID int)
RETURNS @IDs TABLE
(
  ROUsageID int PRIMARY KEY,
  ROUsageAction char(3)
)
WITH EXECUTE AS OWNER
AS
BEGIN
	
	DECLARE @ContentID int
	DECLARE @text varchar(max)
	SELECT @ContentID = ContentID,@text = Text FROM ContentAudits WHERE AuditID = @ContentAuditID
	DECLARE @index1 int
	DECLARE @index2 int
	DECLARE @tid varchar(max)
	DECLARE @trid int
	WHILE (LEN(@text) > 0)
		BEGIN
			SET @index1 = CHARINDEX('#Link:Transition' , @text)
			IF (@index1 > 0)
				BEGIN
					SET @text = RIGHT(@text, (LEN(@text) - @index1))
					SET @index1 = CHARINDEX(' ', @text)
					SET @text = RIGHT(@text, (LEN(@text) - @index1))
					SET @index2 = CHARINDEX(' ', @text)
					SET @tid = LTRIM(LEFT(@text, @index2))
					SET @text = RIGHT(@text, (len(@text) - @index2))
					SET @trid = CAST(@tid AS INT)
					IF EXISTS (SELECT ROUsageID FROM tblROUsages WHERE ROUsageID = @trid AND DeleteStatus < 0)
						INSERT INTO @IDs VALUES (@trid, 'ADD')
					ELSE 
						INSERT INTO @IDs VALUES (@trid, NULL)
				END
			ELSE
				SET @text = ''
		END
	INSERT INTO @IDs SELECT tt.ROUsageID,'DEL' FROM tblROUsages tt LEFT JOIN @IDs ti ON tt.ROUsageID = ti.ROUsageID WHERE tt.ContentID = @ContentID
	AND ti.ROUsageID IS NULL
	DELETE FROM @IDs WHERE ROUsageAction IS NULL
	RETURN
END
GO

-- Display the status of func creation
IF (@@Error = 0) PRINT 'TableFunction Creation: vefn_FindROUsageIDs Succeeded'
ELSE PRINT 'TableFunction Creation: vefn_FindROUsageIDs Error on Creation'
GO

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

WITH EXECUTE AS OWNER
AS
	DECLARE @ContentID int
	DECLARE @Number nvarchar(256)
	DECLARE @Text nvarchar(max)
	DECLARE @Type int
	DECLARE @FormatID int
	DECLARE @Config nvarchar(max)
	DECLARE @DTS datetime
	DECLARE @Data xml
	DECLARE @ConfigX xml
	SELECT @Contentid = Contentid,@Number = Number,@Text = Text,@Type = Type,@FormatID = FormatID,@Config = Config,@DTS = DTS
	FROM ContentAudits WHERE AuditID = @AuditID
--	SELECT @Data = Data,@ConfigX = Config FROM GridAudits WHERE ContentID = @ContentID and DTS = @DTS
	SELECT @Data = Data,@ConfigX = Config FROM GridAudits WHERE ContentID = @ContentID and ContentAuditID = @AuditID
	UPDATE tblContents SET Number = @Number,Text = @Text,Type = @Type,FormatID = @FormatID,Config = @Config,DTS = @DTS WHERE ContentID = @ContentID
	UPDATE tblGrids SET Data = @Data,Config = @ConfigX WHERE ContentID = @ContentID
--transitions
update tblTransitions set deletestatus = 0 where transitionid in (select transitionid from vefn_FindTransitionIDs(@AuditID) where TransitionAction = 'ADD')
delete from transitions where transitionid in (select transitionid from vefn_FindTransitionIDs(@AuditID) where TransitionAction = 'DEL')
--transitions end
--rousages
update tblROUsages set deletestatus = 0 where rousageid in (select rousageid from vefn_FindROUsageIDs(@AuditID) where rousageaction = 'ADD')
delete from rousages where rousageid in (select rousageid from vefn_FindROUsageIDs(@AuditID) where rousageaction = 'DEL')
--rousages end
	RETURN
GO

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

/****** Object:  Trigger [tr_tblAnnotations_Update] ******/
ALTER trigger [dbo].[tr_tblAnnotations_Update] on [dbo].[tblAnnotations] for update as 
	begin
		if exists (select * from inserted)
		begin
			insert into AnnotationAudits(AnnotationID,ItemID,TypeID,RtfText,SearchText,Config,DTS,UserID,DeleteStatus,DeleteDTS)
			select dd.AnnotationID,dd.ItemID,dd.TypeID,dd.RtfText,dd.SearchText,dd.Config,dd.DTS,dd.UserID,dd.DeleteStatus,dd.DeleteDTS 
			from deleted dd
			inner join inserted ii on dd.AnnotationID = ii.AnnotationID
			where (select count(*) from annotationaudits where annotationid = dd.annotationid and dts = dd.dts and deletedts = dd.deletedts) = 0
		end
	end
GO

-- Display the status of Trigger alter
IF (@@Error = 0) PRINT 'Trigger Ateration: tr_tblAnnotations_Update Succeeded'
ELSE PRINT 'Trigger Ateration: tr_tblAnnotations_Update Error on Creation'
GO

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

(
	@AnnotationID int,
	@UserID varchar(200)
)
WITH EXECUTE AS OWNER
AS
BEGIN TRY -- Try Block
	BEGIN TRANSACTION
--delete log add
	DECLARE @DeleteID int
	INSERT INTO DeleteLog (UserID) VALUES (@UserID)
	SELECT @DeleteID = SCOPE_IDENTITY()
--end delete log add
	DELETE [Annotations]
		WHERE [AnnotationID] = @AnnotationID
--delete log purge
	DELETE from DeleteLog where DeleteID = @DeleteID
--end delete log purge
	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: deleteAnnotationWithUserID Succeeded'
ELSE PRINT 'Procedure Creation: deleteAnnotationWithUserID Error on Creation'
GO

/****** Object:  Trigger [tr_Annotations_Delete] ******/
ALTER trigger [dbo].[tr_Annotations_Delete] on [dbo].[Annotations] instead of delete as 
	begin
		update ii set DeleteStatus = (select max(DeleteID) from DeleteLog where SPID = @@spid),DeleteDTS = getdate() 
--DTS = getdate(),
,DTS = dd.dts,
		UserID = (select top 1 UserID from DeleteLog where SPID = @@spid order by deleteid desc)
		from tblAnnotations ii inner join deleted dd on dd.AnnotationID = ii.AnnotationID
		insert into AnnotationAudits(AnnotationID,ItemID,TypeID,RtfText,SearchText,Config,DTS,UserID,DeleteStatus,DeleteDTS)
		select ii.AnnotationID,ii.ItemID,ii.TypeID,ii.RtfText,ii.SearchText,ii.Config,ii.DTS,ii.UserID,ii.DeleteStatus,ii.DeleteDTS
		from tblAnnotations ii inner join deleted dd on dd.AnnotationID = ii.AnnotationID
		where ii.DeleteStatus > 0
	end
GO

-- Display the status of Trigger alter
IF (@@Error = 0) PRINT 'Trigger Ateration: tr_Annotations_Delete Succeeded'
ELSE PRINT 'Trigger Ateration: tr_Annotations_Delete Error on Creation'
GO

/****** Object:  Trigger [tr_tblItems_Update] ******/
ALTER trigger [dbo].[tr_tblItems_Update] on [dbo].[tblItems] for update as 
	begin
		if exists (select * from inserted)
		begin
			insert into ItemAudits(ItemID,PreviousID,ContentID,DTS,UserID,DeleteStatus)
			select dd.ItemID,dd.PreviousID,dd.ContentID,dd.DTS,dd.UserID,dd.DeleteStatus from deleted dd
			inner join inserted ii on dd.ItemID = ii.ItemID
			where dd.deletestatus = 0 or ii.deletestatus != 0
		end
	end
GO

-- Display the status of Trigger alter
IF (@@Error = 0) PRINT 'Trigger Ateration: tr_tblItems_Update Succeeded'
ELSE PRINT 'Trigger Ateration: tr_tblItems_Update Error on Creation'
GO

/****** Object:  Trigger [tr_Contents_Delete] ******/
ALTER trigger [dbo].[tr_Contents_Delete] on [dbo].[Contents] instead of delete as 
	begin
		update ii set DeleteStatus = (select max(DeleteID) from DeleteLog where SPID = @@spid), DeleteDTS = getdate(),
		UserID = (select top 1 UserID from DeleteLog where SPID = @@spid order by deleteid desc)
		from tblContents ii inner join deleted dd on dd.ContentID = ii.ContentID
		insert into ContentAudits(ContentID,Number,Text,Type,FormatID,Config,DTS,UserID,DeleteStatus,DeleteDTS)
		select ii.ContentID,ii.Number,ii.Text,ii.Type,ii.FormatID,ii.Config,ii.DTS,ii.UserID,ii.DeleteStatus,ii.DeleteDTS
		from tblContents ii inner join deleted dd on dd.ContentID = ii.ContentID
		where ii.DeleteStatus > 0
	end
go

-- Display the status of Trigger alter
IF (@@Error = 0) PRINT 'Trigger Ateration: tr_Contents_Delete Succeeded'
ELSE PRINT 'Trigger Ateration: tr_Contents_Delete Error on Creation'
GO

/****** Object:  UserDefinedFunction [dbo].[vefn_tblChildItems]    Script Date: 05/18/2011 11:20:48 ******/
/*
select * from vefn_SiblingAndChildrenItemsByProc(30) II
join
*/

CREATE  FUNCTION [dbo].[vefn_SiblingAndChildrenItemsByProc](@ProcID int)
RETURNS @SiblingAndChildren TABLE
(
		[ItemID] int PRIMARY KEY
	, [DVPath] nvarchar(max)
	, [Path] nvarchar(max)
	, [Level] int
	, [FromType] int
	, [Ordinal] int
	, [ParentID] int
	, [PreviousID] int
	, [ContentID] int
	, [DTS] datetime
	, [UserID] nvarchar(100)
	, [pContentID] int
	, [pDTS] datetime
	, [pUserID] nvarchar(100)
	, [IsRNO] int

--	, [PPath] nvarchar(max)
--	, [POrdinalPath] nvarchar(max)
	, [OrdinalPath] nvarchar(max)
)
WITH EXECUTE AS OWNER
AS
BEGIN
Declare @Delim char(1)
Set @Delim=char(7)
Declare @DelimNumber char(1)
Set @DelimNumber=char(17)
Declare @DelimStep char(1)
Set @DelimStep='.'
BEGIN
with Itemz([ItemID], [DVPath], [Level], [ParentID], [Ordinal], [PreviousID], [FromType], [ContentID], [DTS], [UserID],[pContentID],
  [pDTS],[pUserID],PPath, [Path], [IsRNO], [POrdinalPath], [OrdinalPath]) as (
  Select [I].[ItemID], '' DVPath, 0 [Level], 0 [ParentID], 0 [Ordinal], [PreviousID],0 [FromType],[I].[ContentID],[I].[DTS],[I].[UserID]
	,0 [pContentID],[I].[DTS] [pDTS], [I].[UserID] [pUserID],
		Cast('' as nvarchar(max)) [PPath],
		Cast(Case when C.Type < 20000 then @Delim + C.Number + @DelimNumber + C.Text else '1' end as nvarchar(max)) [Path],
		0 IsRNO, Cast('' as nvarchar(max)) [POrdinalPath], Cast('0001' as nvarchar(max)) [OrdinalPath]
	FROM [Items] I
  Join Contents C on C.ContentID=I.ContentID
    WHERE I.[ItemID] = @ProcID
Union All
-- Children
  select I.[ItemID], DVPath, [Level] + 1,Z.ItemID,0, I.[PreviousID], P.[FromType],I.[ContentID],I.[DTS],I.[UserID],
	P.[ContentID] [pContentID],P.[DTS] [pDTS],P.[UserID] [pUserID]
	,PATH + --''
	case C.Type/10000
	when 2 then
	case P.FromType
	when 3 then @DelimStep + 'Caution'
	when 4 then @DelimStep + 'Note'
  else '' end
	else '' end
	PPath,
	Path + case C.Type/10000
	when 0 then @Delim +C.Number + @DelimNumber + C.Text
	when 1 then @Delim +C.Number + @DelimNumber + C.Text
	else
	case P.FromType
	--when 1 then 'PRC' + @Delim + cast(1 as varchar(3))
	--when 2 then 'SEC' + @Delim + cast(1 as varchar(3))
	when 3 then @DelimStep +'Caution' + @DelimStep + cast(1 as varchar(3))
	when 4 then @DelimStep +'Note' + @DelimStep + cast(1 as varchar(3))
	when 5 then @DelimStep +'RNO' + @DelimStep
	when 7 then @DelimStep +'Table' + @DelimStep
	else case when Z.FromType < 3 then @Delim else @DelimStep end + cast(1 as varchar(3))
	end end Path,
  case when P.FromType = 5 then -1 else 0 end IsRNO,
	OrdinalPath + '.' + Right('000' + Cast(P.FromType as varchar(4)), 4) + '-' ,
	OrdinalPath + '.' + Right('000' + Cast(P.FromType as varchar(4)), 4) + '-00001'
	from Itemz Z
	join Parts P on P.ContentID = Z.ContentID
	join Items I on I.ItemID = P.ItemID
  join Contents C on C.ContentID = I.ContentID
Union All
-- Siblings
  select I.[ItemID], DVPath, [Level] ,Z.[ParentID],Z.[Ordinal] +1, I.[PreviousID], [FromType],I.[ContentID],I.[DTS],I.[UserID]
	,null,null,null,
	PPath,
	--'1' +
	PPath  + case C.Type/10000
	when 0 then @Delim + C.Number + @DelimNumber + C.Text
	when 1 then @Delim + C.Number + @DelimNumber + C.Text
	else case when Path like '%.%' then @DelimStep else @Delim end + cast(Ordinal + 2 as varchar(3))
	end Path, 0,
	POrdinalPath,
	POrdinalPath + right('00000' + cast(Z.[Ordinal] + 2 as varchar(5)), 5)
	from Itemz Z
	join Items I on I.PreviousID = Z.ItemID
  join Contents C on C.ContentID = I.ContentID
	--where Z.[Level] > 0

)
insert into @SiblingAndChildren
select I.[ItemID], [DvPath], [Path], I.[Level],I.[FromType],I.[Ordinal], I.[ParentID], I.[PreviousID],I.[ContentID],I.[DTS],I.[UserID],
		[pContentID],[pDTS],[pUserID],[IsRNO]/*,[PPath],[POrdinalPath]*/,[OrdinalPath]
 from ItemZ I
OPTION (MAXRECURSION 10000)
END
RETURN
END
GO
-- Display the status of Trigger alter
IF (@@Error = 0) PRINT 'Function: vefn_SiblingAndChildrenItemsByProc Succeeded'
ELSE PRINT 'Function: vefn_SiblingAndChildrenItemsByProc Error on Creation'

go

/****** Object:  UserDefinedFunction [dbo].[vefn_tblChildItems]    Script Date: 05/18/2011 11:20:48 ******/
	
/*
declare @PreviousID as int
declare @ItemID as int
set @ItemID = 450
select @PreviousID = PreviousID from items where ItemID = @ItemID

Select * from Items where ItemID = @ItemID

select * from Transitions
where FromID in (Select ContentID from vefn_ChildItems(@PreviousID))
AND (ToID = @ItemID or RangeID = @ItemID)

select CC.Text from Transitions TT
join contents CC on TT.FromID = CC.ContentID
where FromID in (Select ContentID from vefn_ChildItems(@PreviousID))
AND (ToID = @ItemID or RangeID = @ItemID)

*/
CREATE  FUNCTION [dbo].[vefn_tblChildItems](@ProcItemID int, @ItemID int, @IncludeDeletedChildren int)
RETURNS @Children TABLE
(
  ItemID int PRIMARY KEY,
	IContentID int,
	IDeleteStatus int,
	ProcDTS datetime,
	Path nvarchar(max),
	OrdinalPath nvarchar(max)
)
WITH EXECUTE AS OWNER
AS
BEGIN
declare @path nvarchar(max)
declare @ppath nvarchar(max)
declare @ordinalpath nvarchar(max)
declare @pordinalpath nvarchar(max)
declare @ordinal int
declare @pitemid int
select @path = path,@ordinalpath = ordinalpath,@ordinal = ordinal,@pitemid = parentid from vefn_siblingandchildrenitemsByProc(@ProcItemID) where itemid = @ItemID
if @pitemid = 0 begin
	set @pordinalpath = ''
	set @ppath = ''
end	
else begin
	select @ppath = path,@pordinalpath = ordinalpath from vefn_siblingandchildrenitemsByProc(@ProcItemID) where itemid = @pitemID
end
declare @procdts datetime
select @procdts = dts from items where itemid = @ProcItemID
Declare @Delim char(1)
Set @Delim=char(7)
Declare @DelimNumber char(1)
Set @DelimNumber=char(17)
Declare @DelimStep char(1)
Set @DelimStep='.'
begin
with Itemz([Level], [ItemID], [ContentID], DeleteStatus, ProcDTS, PPath, [Path], [POrdinalPath], [OrdinalPath], [FromType], [Ordinal]) as (
  Select 0 [Level], [ItemID], I.[ContentID], I.DeleteStatus, @procdts procdts,
		@ppath [PPath],
		@path [Path],
		@pordinalpath [POrdinalPath], 
		@ordinalpath [OrdinalPath],
0 [FromType],@ordinal [Ordinal]
	FROM [tblItems] I
--inner join tblContents C on C.ContentID=I.ContentID
    where I.[ItemID]=@ItemID
Union All
-- Children
  select [Level] + 1, I.[ItemID], I.[ContentID], I.DeleteStatus, @procdts procdts,
	PATH + --''
	case C.Type/10000
	when 2 then
	case P.FromType
	when 3 then @DelimStep + 'Caution'
	when 4 then @DelimStep + 'Note'
  else '' end
	else '' end
	PPath,
	Path + case C.Type/10000
	when 0 then @Delim +C.Number + @DelimNumber + C.Text
	when 1 then @Delim +C.Number + @DelimNumber + C.Text
	else
	case P.FromType
	--when 1 then 'PRC' + @Delim + cast(1 as varchar(3))
	--when 2 then 'SEC' + @Delim + cast(1 as varchar(3))
	when 3 then @DelimStep +'Caution' + @DelimStep + cast(1 as varchar(3))
	when 4 then @DelimStep +'Note' + @DelimStep + cast(1 as varchar(3))
	when 5 then @DelimStep +'RNO' + @DelimStep
	when 7 then @DelimStep +'Table' + @DelimStep
	else case when Z.FromType < 3 then @Delim else @DelimStep end + 
case when I.deletestatus != 0 then 'Old ' else '' end + cast(1 as varchar(3))
	end end Path,
	OrdinalPath + '.' + Right('000' + Cast(P.FromType as varchar(4)), 4) + '-' ,
	OrdinalPath + '.' + Right('000' + Cast(P.FromType as varchar(4)), 4) + '-00001',
P.[FromType],0
	from Itemz Z
	join tblParts P on P.ContentID = Z.ContentID
	join tblItems I on I.ItemID = P.ItemID
inner join tblContents C on C.ContentID=I.ContentID
	where @IncludeDeletedChildren = 1 or z.DeleteStatus = 0
-- Siblings
Union All
  select [Level] , I.[ItemID], I.[ContentID], I.DeleteStatus, @procdts procdts,
	PPath,
	--'1' +
	PPath  + case C.Type/10000
	when 0 then @Delim + C.Number + @DelimNumber + C.Text
	when 1 then @Delim + C.Number + @DelimNumber + C.Text
	else case when Path like '%.%' then @DelimStep else @Delim end + 
case when I.deletestatus != 0 then 'Old ' else '' end + cast(Ordinal + 2 as varchar(3))
	end Path,
	POrdinalPath,
	POrdinalPath + right('00000' + cast(Z.[Ordinal] + 2 as varchar(5)), 5),
FromType,Z.[Ordinal] +1
	from Itemz Z
	join tblItems I on I.PreviousID = Z.ItemID
inner join tblContents C on C.ContentID=I.ContentID
	where Z.[Level] > 0
)
insert into @Children select ItemID, ContentID, DeleteStatus, ProcDTS, Path, OrdinalPath from Itemz
RETURN
END
end

go
-- Display the status of Trigger alter
IF (@@Error = 0) PRINT 'Function: vefn_tblChildItems Succeeded'
ELSE PRINT 'Function: vefn_tblChildItems Error on Creation'

go

/****** Object:  StoredProcedure [getContentAuditsChronologyByItemID] ******/
/*
getContentAuditsChronologyByItemID 146,146,0
getContentAuditsChronologyByItemID 30,30,0
getContentAuditsChronologyByItemID 146,146,1
*/	
CREATE PROCEDURE [dbo].[getContentAuditsChronologyByItemID]
(
	@ProcedureItemID int,
	@SelectedItemID int,
	@IncludeDeletedChildren int
)

WITH EXECUTE AS OWNER
AS
begin
	select [AuditID],[ContentID],[Number],[Text],[Type],[FormatID],[Config],[DTS],[UserID],[DeleteStatus],[DeleteDTS],[ActionWhat],[ActionWhen],[Path],ItemID
	from
	(
	select
	case
	when lastauditid is null then 'Added'
	when r.deletestatus > 0 then 'Deleted'
	when lastauditid = -1 then 'Changed'
	when DeletedAuditID is not null then 'Restored'
--	when DeletedAuditID is not null and lastauditid = deletedauditid then 'Restored'
	else 'Changed'
	end actionwhat
	,case
	when lastauditid is null then dts
	when r.deletestatus > 0 then deletedts
	when lastauditid = -1 then dts
	when DeletedAuditID is not null then deletedts
	else dts
	end actionwhen
	,* 
	from vefn_tblchilditems (@ProcedureItemID,@SelectedItemID,@IncludeDeletedChildren) t
	inner join vefn_chronologyreport() r
	on t.icontentid = r.contentid
--	where deletedts > procdts or dts > procdts
	) ah
	order by contentid,auditid--actionwhen
	RETURN
end
go
-- Display the status of Trigger alter
IF (@@Error = 0) PRINT 'StoredProcedure [getContentAuditsChronologyByItemID] Succeeded'
ELSE PRINT 'StoredProcedure [getContentAuditsChronologyByItemID] Error on Creation'
go

/****** Object:  UserDefinedFunction [dbo].[vefn_ChronologyReport]    Script Date: 05/18/2011 11:20:48 ******/
CREATE function [dbo].[vefn_ChronologyReport]()
returns @Report table
(
AuditID bigint,
ContentID int,
Number nvarchar(512),
Text nvarchar(max),
Type int,
FormatID int,
Config nvarchar(max),
DTS datetime,
UserID nvarchar(200),
DeleteStatus int,
DeleteDTS datetime,
ItemDTS datetime,
LastAuditID int,
DeletedAuditID int
)
WITH EXECUTE AS OWNER
AS
BEGIN
	insert into @Report
	SELECT 
	[AuditID]
	,ca.[ContentID]
	,[Number]
	,[Text]
	,[Type]
	,[FormatID]
	,[Config]
	,ca.[DTS]
	,ca.[UserID]
	,ca.[DeleteStatus]
	,DeleteDTS
	,(select min(dts) from itemaudits where contentid = ca.contentid) ItemDTS
,(select max(auditid) from contentaudits where ca.contentid = contentid and ca.auditid > auditid and number is not null) LastAuditID
,(select max(auditid) from contentaudits where ca.contentid = contentid and ca.auditid > auditid and number is not null and deletestatus > 0 and ca.dts = dts) DeletedAuditID
	FROM [VEPROMS_JCB1].[dbo].[ContentAudits] ca
--	inner join tblitems ti on ca.contentid = ti.contentid
	where Number is not null
	UNION
	SELECT 
	(select max(auditid) + 1 from contentaudits) [AuditID]
--	0 [AuditID]
	,ca.[ContentID]
	,[Number]
	,[Text]
	,[Type]
	,[FormatID]
	,[Config]
	,ca.[DTS]
	,ca.[UserID]
	,ca.[DeleteStatus]
	,DeleteDTS
	,(select min(dts) from itemaudits where contentid = ca.contentid) ItemDTS
,(select max(auditid) from contentaudits where ca.contentid = contentid and number is not null) LastAuditID
,(select max(auditid) from contentaudits where ca.contentid = contentid and number is not null and deletestatus > 0 and ca.dts = dts) DeletedAuditID
	 FROM [VEPROMS_JCB1].[dbo].[tblContents] ca
--	inner join tblitems ti on ca.contentid = ti.contentid
	WHERE ca.DeleteStatus = 0 AND 
	ca.ContentID in (SELECT [ContentID] FROM [VEPROMS_JCB1].[dbo].[ContentAudits])
	order by ca.DTS,DeleteDTS
	return
end
GO

-- Display the status of Trigger alter
IF (@@Error = 0) PRINT 'Function: vefn_ChronologyReport Succeeded'
ELSE PRINT 'Function: vefn_ChronologyReport Error on Creation'

go

/****** Object:  StoredProcedure [getAnnotationAuditsChronologyByItemID] ******/
/*
getAnnotationAuditsChronologyByItemID 30,30
getAnnotationAuditsChronologyByItemID 30,8570
getAnnotationAuditsChronologyByItemID 30,8513
getAnnotationAuditsChronologyByItemID 30,8505
*/
create procedure getAnnotationAuditsChronologyByItemID
(
	@ProcItemID int,
	@ItemID int
)
WITH EXECUTE AS OWNER
AS
begin
	select 
	case
	when lastauditid is null and dts > itemdts then 'Added'
	when deletestatus > 0 then 'Deleted'
	when lastauditid = deletedauditid then 'Restored'
	else 'Changed'
	end ActionWhat
	,case
	when lastauditid is null and dts > itemdts then dts
	when deletestatus > 0 then deletedts
	when lastauditid = deletedauditid then deletedts
	else dts
	end ActionWhen
	,* 
	from
	(
	select 
	(select max(auditid) + 1 from annotationaudits) auditid
--	0 auditid
	,aa.annotationid
	,aa.itemid
	,aa.typeid
	,aa.rtftext
	,aa.searchtext
	,aa.config
	,aa.dts
	,aa.userid
	,0 deletestatus
	,aa.deletedts
	,ii.contentid icontentid
	,(select min(dts) from tblitems where itemid = ii.itemid) ItemDTS
	,(select max(auditid) from annotationaudits where annotationid = aa.annotationid and itemid = ii.itemid) LastAuditID
	,(select max(auditid) from annotationaudits where annotationid = aa.annotationid and itemid = ii.itemid and deletestatus > 0 and aa.dts = dts) DeletedAuditID
	from tblannotations aa
	inner join items ii on aa.itemid = ii.itemid
	where aa.deletestatus = 0
	union
	select 
	aa.auditid
	,aa.annotationid
	,aa.itemid
	,aa.typeid
	,aa.rtftext
	,aa.searchtext
	,aa.config
	,aa.dts
	,aa.userid
	,aa.deletestatus
	,aa.deletedts
	,ii.contentid icontentid
	,(select min(dts) from tblitems where itemid = ii.itemid) ItemDTS
	,(select max(auditid) from annotationaudits where annotationid = aa.annotationid and itemid = ii.itemid and aa.auditid > auditid ) LastAuditID
	,(select max(auditid) from annotationaudits where annotationid = aa.annotationid and itemid = ii.itemid and aa.auditid > auditid and deletestatus > 0 and aa.dts = dts) DeletedAuditID
	from annotationaudits aa
	inner join items ii on aa.itemid = ii.itemid
	) ah
	where itemid in (select itemid from vefn_tblchilditems (@procitemid,@itemid,0))
	order by annotationid,auditid--actionwhen
end
go
-- Display the status of Trigger alter
IF (@@Error = 0) PRINT 'StoredProcedure [getAnnotationAuditsChronologyByItemID] Succeeded'
ELSE PRINT 'StoredProcedure [getAnnotationAuditsChronologyByItemID] Error on Creation'
go


/****** Object:  Trigger [dbo].[tr_GridAudits_insert]    Script Date: 06/06/2011 16:58:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create trigger [dbo].[tr_GridAudits_insert]
on [dbo].[GridAudits]
after INSERT
AS
begin
	update ga set ga.contentauditid = (select max(auditid) from contentaudits where contentid = ii.contentid)
	from inserted ii
	inner join gridaudits ga on ii.auditid = ga.auditid
end
go
-- Display the status of Trigger alter
IF (@@Error = 0) PRINT 'Trigger Creation: tr_GridAudits_insert Succeeded'
ELSE PRINT 'Trigger Creation: tr_GridAudits_insert Error on Creation'
GO