SourceCode/PROMS/SQL/6_Approval_Support_Procs_Funcs.sql.bak

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

(
	@RevisionID int,
	@StageID int,
	@ConsistencyChecks xml=null,
	@DTS datetime,
	@UserID nvarchar(100),
	@newCheckID int output,
	@newLastChanged timestamp output
)
WITH EXECUTE AS OWNER
AS
BEGIN TRY -- Try Block
	BEGIN TRANSACTION
	INSERT INTO [Checks]
	(
		[RevisionID],
		[StageID],
		[ConsistencyChecks],
		[DTS],
		[UserID]
	)
	VALUES
	(
		@RevisionID,
		@StageID,
		@ConsistencyChecks,
		@DTS,
		@UserID
	)
	SELECT @newCheckID= SCOPE_IDENTITY()
	SELECT 		@newLastChanged=[LastChanged]
		FROM [Checks] WHERE [CheckID]=@newCheckID
	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: addCheck Succeeded'
ELSE PRINT 'Procedure Creation: addCheck Error on Creation'
GO

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

(
	@ItemID int,
	@TypeID int,
	@RevisionNumber nvarchar(50)=null,
	@RevisionDate datetime=null,
	@Notes nvarchar(MAX)=null,
	@Config xml=null,
	@DTS datetime,
	@UserID nvarchar(100),
	@newRevisionID int output,
	@newLastChanged timestamp output
)
WITH EXECUTE AS OWNER
AS
BEGIN TRY -- Try Block
	BEGIN TRANSACTION
	INSERT INTO [Revisions]
	(
		[ItemID],
		[TypeID],
		[RevisionNumber],
		[RevisionDate],
		[Notes],
		[Config],
		[DTS],
		[UserID]
	)
	VALUES
	(
		@ItemID,
		@TypeID,
		@RevisionNumber,
		@RevisionDate,
		@Notes,
		@Config,
		@DTS,
		@UserID
	)
	SELECT @newRevisionID= SCOPE_IDENTITY()
	SELECT 		@newLastChanged=[LastChanged]
		FROM [Revisions] WHERE [RevisionID]=@newRevisionID
	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: addRevision Succeeded'
ELSE PRINT 'Procedure Creation: addRevision Error on Creation'
GO

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

(
	@Name nvarchar(50),
	@Description nvarchar(200)=null,
	@IsApproved int,
	@DTS datetime,
	@UserID nvarchar(100),
	@newStageID int output,
	@newLastChanged timestamp output
)
WITH EXECUTE AS OWNER
AS
BEGIN TRY -- Try Block
	BEGIN TRANSACTION
	INSERT INTO [Stages]
	(
		[Name],
		[Description],
		[IsApproved],
		[DTS],
		[UserID]
	)
	VALUES
	(
		@Name,
		@Description,
		@IsApproved,
		@DTS,
		@UserID
	)
	SELECT @newStageID= SCOPE_IDENTITY()
	SELECT 		@newLastChanged=[LastChanged]
		FROM [Stages] WHERE [StageID]=@newStageID
	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: addStage Succeeded'
ELSE PRINT 'Procedure Creation: addStage Error on Creation'
GO

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

(
	@RevisionID int,
	@StageID int,
	@PDF varbinary(MAX)=null,
	@SummaryPDF varbinary(MAX)=null,
	@DTS datetime,
	@UserID nvarchar(200),
	@newVersionID int output,
	@newLastChanged timestamp output
)
WITH EXECUTE AS OWNER
AS
BEGIN TRY -- Try Block
	BEGIN TRANSACTION
	INSERT INTO [Versions]
	(
		[RevisionID],
		[StageID],
		[PDF],
		[SummaryPDF],
		[DTS],
		[UserID]
	)
	VALUES
	(
		@RevisionID,
		@StageID,
		@PDF,
		@SummaryPDF,
		@DTS,
		@UserID
	)
	SELECT @newVersionID= SCOPE_IDENTITY()
	SELECT 		@newLastChanged=[LastChanged]
		FROM [Versions] WHERE [VersionID]=@newVersionID
	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: addVersion Succeeded'
ELSE PRINT 'Procedure Creation: addVersion Error on Creation'
GO

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

(
	@CheckID int
)
WITH EXECUTE AS OWNER
AS
BEGIN TRY -- Try Block
	BEGIN TRANSACTION
	DELETE [Checks]
		WHERE [CheckID] = @CheckID
	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: deleteCheck Succeeded'
ELSE PRINT 'Procedure Creation: deleteCheck Error on Creation'
GO

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

(
	@RevisionID int
)
WITH EXECUTE AS OWNER
AS
BEGIN TRY -- Try Block
	BEGIN TRANSACTION
	DELETE [Checks]
		WHERE [RevisionID]=@RevisionID
	DELETE [Versions]
		WHERE [RevisionID]=@RevisionID
	DELETE [Revisions]
		WHERE [RevisionID] = @RevisionID
	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: deleteRevision Succeeded'
ELSE PRINT 'Procedure Creation: deleteRevision Error on Creation'
GO

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

(
	@StageID int
)
WITH EXECUTE AS OWNER
AS
BEGIN TRY -- Try Block
	BEGIN TRANSACTION
	DELETE [Checks]
		WHERE [StageID]=@StageID
	DELETE [Versions]
		WHERE [StageID]=@StageID
	DELETE [Stages]
		WHERE [StageID] = @StageID
	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: deleteStage Succeeded'
ELSE PRINT 'Procedure Creation: deleteStage Error on Creation'
GO

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

(
	@VersionID int
)
WITH EXECUTE AS OWNER
AS
BEGIN TRY -- Try Block
	BEGIN TRANSACTION
	DELETE [Versions]
		WHERE [VersionID] = @VersionID
	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: deleteVersion Succeeded'
ELSE PRINT 'Procedure Creation: deleteVersion Error on Creation'
GO

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

(
	@CheckID int
)
WITH EXECUTE AS OWNER
AS
	SELECT COUNT(*)
	FROM [Checks] WHERE [CheckID]=@CheckID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsCheck Succeeded'
ELSE PRINT 'Procedure Creation: existsCheck Error on Creation'
GO

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

(
	@RevisionID int
)
WITH EXECUTE AS OWNER
AS
	SELECT COUNT(*)
	FROM [Revisions] WHERE [RevisionID]=@RevisionID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsRevision Succeeded'
ELSE PRINT 'Procedure Creation: existsRevision Error on Creation'
GO

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

(
	@StageID int
)
WITH EXECUTE AS OWNER
AS
	SELECT COUNT(*)
	FROM [Stages] WHERE [StageID]=@StageID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsStage Succeeded'
ELSE PRINT 'Procedure Creation: existsStage Error on Creation'
GO

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

(
	@VersionID int
)
WITH EXECUTE AS OWNER
AS
	SELECT COUNT(*)
	FROM [Versions] WHERE [VersionID]=@VersionID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsVersion Succeeded'
ELSE PRINT 'Procedure Creation: existsVersion Error on Creation'
GO

/****** Object:  StoredProcedure [getAnnotationAuditsChronologyByItemID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getAnnotationAuditsChronologyByItemID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getAnnotationAuditsChronologyByItemID];
GO
	
/****** Object:  StoredProcedure [getAnnotationAuditsChronologyByItemID] ******/
/*
getAnnotationAuditsChronologyByItemID 3,1230
getAnnotationAuditsChronologyByItemID 30,8570
getAnnotationAuditsChronologyByItemID 30,8513
getAnnotationAuditsChronologyByItemID 30,8505
*/
CREATE procedure [dbo].[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 ActionDTS
	when lastauditid = deletedauditid then ActionDTS
	else dts
	end ActionWhen
	,*
	from
	(
	select
	cast(ident_current('annotationaudits') + 1 as bigint) auditid
--	(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.ActionDTS
	,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.ActionDTS
	,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))
and dts > (select dts from versions where versionid = (select max(versionid) from revisions rr inner join versions vv on rr.revisionid = vv.revisionid
inner join stages ss on vv.stageid = ss.stageid where itemid = @procitemid and ss.isapproved = 1))
	order by annotationid,auditid--actionwhen
end
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getAnnotationAuditsChronologyByItemID Succeeded'
ELSE PRINT 'Procedure Creation: getAnnotationAuditsChronologyByItemID Error on Creation'
GO

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

(
	@CheckID int
)
WITH EXECUTE AS OWNER
AS
	SELECT
		[CheckID],
		[RevisionID],
		[StageID],
		[ConsistencyChecks],
		[DTS],
		[UserID],
		[LastChanged]
	FROM [Checks]
	WHERE [CheckID]=@CheckID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getCheck Succeeded'
ELSE PRINT 'Procedure Creation: getCheck Error on Creation'
GO

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

WITH EXECUTE AS OWNER
AS
	SELECT
		[CheckID],
		[RevisionID],
		[StageID],
		[ConsistencyChecks],
		[DTS],
		[UserID],
		[LastChanged]
	FROM [Checks]
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getChecks Succeeded'
ELSE PRINT 'Procedure Creation: getChecks Error on Creation'
GO

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

(
	@RevisionID int
)
WITH EXECUTE AS OWNER
AS

	SELECT
		[Checks].[CheckID],
		[Checks].[RevisionID],
		[Checks].[StageID],
		[Checks].[ConsistencyChecks],
		[Checks].[DTS],
		[Checks].[UserID],
		[Checks].[LastChanged],
		[Stages].[Name] [Stage_Name],
		[Stages].[Description] [Stage_Description],
		[Stages].[IsApproved] [Stage_IsApproved],
		[Stages].[DTS] [Stage_DTS],
		[Stages].[UserID] [Stage_UserID]
	FROM [Checks]
		JOIN [Stages]  ON
			[Stages].[StageID]=[Checks].[StageID]
	WHERE
		[Checks].[RevisionID]=@RevisionID

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

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

(
	@StageID int
)
WITH EXECUTE AS OWNER
AS

	SELECT
		[Checks].[CheckID],
		[Checks].[RevisionID],
		[Checks].[StageID],
		[Checks].[ConsistencyChecks],
		[Checks].[DTS],
		[Checks].[UserID],
		[Checks].[LastChanged],
		[Revisions].[ItemID] [Revision_ItemID],
		[Revisions].[TypeID] [Revision_TypeID],
		[Revisions].[RevisionNumber] [Revision_RevisionNumber],
		[Revisions].[RevisionDate] [Revision_RevisionDate],
		[Revisions].[Notes] [Revision_Notes],
		[Revisions].[Config] [Revision_Config],
		[Revisions].[DTS] [Revision_DTS],
		[Revisions].[UserID] [Revision_UserID]
	FROM [Checks]
		JOIN [Revisions]  ON
			[Revisions].[RevisionID]=[Checks].[RevisionID]
	WHERE
		[Checks].[StageID]=@StageID

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

/****** Object:  StoredProcedure [getContentAuditsByContentIDandDTS] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getContentAuditsByContentIDandDTS]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getContentAuditsByContentIDandDTS];
GO
	
/*
declare @dts datetime
set @dts = (select dts from items where itemid = 41)
print @dts
exec .dbo.getContentAuditsByContentIDandDTS 9701,@dts
*/

CREATE PROCEDURE [dbo].[getContentAuditsByContentIDandDTS]
(
	@ContentID int,
	@DTS datetime
)

WITH EXECUTE AS OWNER
AS
declare @audits 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],
	[ActionWhat] [nvarchar](max)
)
insert into @audits
	SELECT
		[AuditID],
		[ContentID],
		[Number],
		[Text],
		[Type],
		[FormatID],
		[Config],
		[DTS],
		[UserID],
		[DeleteStatus],
		case when auditid = (select min(auditid) from contentaudits where contentid = @ContentID) then 'Original'
		when deletestatus > 0 then 'Deleted'
		else 'Changed' end ActionWhat
	FROM [ContentAudits]
	WHERE ContentID = @ContentID
and DTS > @DTS
insert into @audits
	SELECT
		[AuditID],
		[ContentID],
		[Number],
		[Text],
		[Type],
		[FormatID],
		[Config],
		[DTS],
		[UserID],
		[DeleteStatus],
'Original' ActionWhat
--		case when auditid = (select min(auditid) from contentaudits where contentid = @ContentID) then 'Original'
--		when deletestatus > 0 then 'Deleted'
--		else 'Changed' end ActionWhat
	FROM [ContentAudits]
	WHERE ContentID = @ContentID
and auditid = (select max(auditid) from contentaudits where contentid = @contentid and auditid not in (select auditid from @audits))
and number is not null
select * from @audits
	ORDER BY AuditID DESC
	RETURN


/*
select * from contentaudits where auditid = (

 from contentaudits where contentid = 9701
select *
,(select max(auditid) from contentaudits where contentid = contentid and auditid > auditid and number is not null) LastAuditID
from contentaudits where contentid = 9806
select * from contentaudits where contentid = 10340

select * from items where itemid = 41
*/
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getContentAuditsByContentIDandDTS Succeeded'
ELSE PRINT 'Procedure Creation: getContentAuditsByContentIDandDTS Error on Creation'
GO

/****** Object:  StoredProcedure [getContentAuditsChronologyByItemID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getContentAuditsChronologyByItemID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getContentAuditsChronologyByItemID];
GO
	
/****** Object:  StoredProcedure [getContentAuditsChronologyByItemID] ******/
/*
getContentAuditsChronologyByItemID 146,146,0
getContentAuditsChronologyByItemID 42,42,0
getContentAuditsChronologyByItemID 9,9,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],[ActionDTS],[ActionWhat],[ActionWhen],[Path],ItemID,TypeName
	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
,actiondts actionwhen
--	,case
--	when lastauditid is null then dts
--	when r.deletestatus > 0 then ActionDTS
--	when lastauditid = -1 then dts
--	when DeletedAuditID is not null then ActionDTS
--	else dts
--	end actionwhen
,(select dbo.vefn_GetStepTypeNameByItemIDandTypeID(t.itemid,r.type)) TypeName
	,*
	from vefn_tblchilditems (@ProcedureItemID,@SelectedItemID,@IncludeDeletedChildren) t
	inner join vefn_chronologyreport(@ProcedureItemID) r
	on t.icontentid = r.contentid
--	where ActionDTS > procdts or dts > procdts
	) ah
	order by OrdinalPath, contentid,auditid--actionwhen
	RETURN
end
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getContentAuditsChronologyByItemID Succeeded'
ELSE PRINT 'Procedure Creation: getContentAuditsChronologyByItemID Error on Creation'
GO

/****** Object:  StoredProcedure [getContentAuditsSummaryByItemID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getContentAuditsSummaryByItemID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getContentAuditsSummaryByItemID];
GO
	
/****** Object:  StoredProcedure [getContentAuditsSummaryByItemID] ******/
/*
getContentAuditsSummaryByItemID 146,146,0
getContentAuditsSummaryByItemID 42,42,0
getContentAuditsSummaryByItemID 9,9,0
getContentAuditsSummaryByItemID 146,146,1
*/	
CREATE PROCEDURE [dbo].[getContentAuditsSummaryByItemID]
(
	@ProcedureItemID int,
	@SelectedItemID int,
	@IncludeDeletedChildren int
)

WITH EXECUTE AS OWNER
AS
begin

select z.* from
(
select contentid,min(auditid) auditid from
(
	select [AuditID],[ContentID],[Number],[Text],[Type],[FormatID],[Config],[DTS],[UserID],[DeleteStatus],[ActionDTS],[ActionWhat],[ActionWhen],[Path],ItemID,TypeName
	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
,actiondts actionwhen
--	,case
--	when lastauditid is null then dts
--	when r.deletestatus > 0 then ActionDTS
--	when lastauditid = -1 then dts
--	when DeletedAuditID is not null then ActionDTS
--	else dts
--	end actionwhen
,(select dbo.vefn_GetStepTypeNameByItemIDandTypeID(t.itemid,r.type)) TypeName
	,*
	from vefn_tblchilditems (@ProcedureItemID,@SelectedItemID,@IncludeDeletedChildren) t
	inner join vefn_chronologyreport(@ProcedureItemID) r
	on t.icontentid = r.contentid
--	where ActionDTS > procdts or dts > procdts
	) ah
) x
group by contentid
) y
inner join
(
	select [AuditID],[ContentID],[Number],[Text],[Type],[FormatID],[Config],[DTS],[UserID],[DeleteStatus],[ActionDTS],[ActionWhat],[ActionWhen],[Path],ItemID,TypeName,ordinalpath
	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
,actiondts actionwhen
--	,case
--	when lastauditid is null then dts
--	when r.deletestatus > 0 then ActionDTS
--	when lastauditid = -1 then dts
--	when DeletedAuditID is not null then ActionDTS
--	else dts
--	end actionwhen
,(select dbo.vefn_GetStepTypeNameByItemIDandTypeID(t.itemid,r.type)) TypeName
	,*
	from vefn_tblchilditems (@ProcedureItemID,@SelectedItemID,@IncludeDeletedChildren) t
	inner join vefn_chronologyreport(@ProcedureItemID) r
	on t.icontentid = r.contentid
--	where ActionDTS > procdts or dts > procdts
	) ah
) z on y.contentid = z.contentid and y.auditid = z.auditid
union
select z.* from
(
select contentid,max(auditid) auditid from
(
	select [AuditID],[ContentID],[Number],[Text],[Type],[FormatID],[Config],[DTS],[UserID],[DeleteStatus],[ActionDTS],[ActionWhat],[ActionWhen],[Path],ItemID,TypeName
	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
,actiondts actionwhen
--	,case
--	when lastauditid is null then dts
--	when r.deletestatus > 0 then ActionDTS
--	when lastauditid = -1 then dts
--	when DeletedAuditID is not null then ActionDTS
--	else dts
--	end actionwhen
,(select dbo.vefn_GetStepTypeNameByItemIDandTypeID(t.itemid,r.type)) TypeName
	,*
	from vefn_tblchilditems (@ProcedureItemID,@SelectedItemID,@IncludeDeletedChildren) t
	inner join vefn_chronologyreport(@ProcedureItemID) r
	on t.icontentid = r.contentid
--	where ActionDTS > procdts or dts > procdts
	) ah
) x
group by contentid
) y
inner join
(
	select [AuditID],[ContentID],[Number],[Text],[Type],[FormatID],[Config],[DTS],[UserID],[DeleteStatus],[ActionDTS],[ActionWhat],[ActionWhen],[Path],ItemID,TypeName,ordinalpath
	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
,actiondts actionwhen
--	,case
--	when lastauditid is null then dts
--	when r.deletestatus > 0 then ActionDTS
--	when lastauditid = -1 then dts
--	when DeletedAuditID is not null then ActionDTS
--	else dts
--	end actionwhen
,(select dbo.vefn_GetStepTypeNameByItemIDandTypeID(t.itemid,r.type)) TypeName
	,*
	from vefn_tblchilditems (@ProcedureItemID,@SelectedItemID,@IncludeDeletedChildren) t
	inner join vefn_chronologyreport(@ProcedureItemID) r
	on t.icontentid = r.contentid
--	where ActionDTS > procdts or dts > procdts
	) ah
) z on y.contentid = z.contentid and y.auditid = z.auditid
	order by OrdinalPath, contentid,auditid--actionwhen
	RETURN
end
-- Display the status of Trigger alter
IF (@@Error = 0) PRINT 'StoredProcedure [getAnnotationAuditsSummaryByItemID] Succeeded'
ELSE PRINT 'StoredProcedure [getAnnotationAuditsSummaryByItemID] Error on Creation'
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getContentAuditsSummaryByItemID Succeeded'
ELSE PRINT 'Procedure Creation: getContentAuditsSummaryByItemID Error on Creation'
GO

/****** Object:  StoredProcedure [getCurrentRevisionByItemID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getCurrentRevisionByItemID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getCurrentRevisionByItemID];
GO
	
/*
getCurrentRevisionByItemID 41
*/	
create PROCEDURE [dbo].[getCurrentRevisionByItemID]
(
	@ItemID int
)
WITH EXECUTE AS OWNER
AS
	SELECT
		[Revisions].[RevisionID],
		[ItemID],
		[TypeID],
		[RevisionNumber],
		[RevisionDate],
		[Notes],
		[Config],
		[DTS],
		[UserID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Checks] WHERE [Checks].[RevisionID]=[Revisions].[RevisionID]) [CheckCount],
		(SELECT COUNT(*) FROM [Versions] WHERE [Versions].[RevisionID]=[Revisions].[RevisionID]) [VersionCount]
	FROM [Revisions]
inner join
(
select top 1 revisionid,mxvid from
(
select rr.revisionid,max(vv.versionid) mxvid
from items ii
inner join revisions rr on ii.itemid = rr.itemid
inner join versions vv on rr.revisionid = vv.revisionid
inner join stages ss on vv.stageid = ss.stageid
where ss.isapproved = 1
and ii.itemid = @ItemID
group by rr.revisionid
union
select null,null
) ds
order by mxvid desc
) rr on [Revisions].revisionid = rr.revisionid
--	WHERE [ItemID] = @ItemID
--	ORDER BY [RevisionID] DESC
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getCurrentRevisionByItemID Succeeded'
ELSE PRINT 'Procedure Creation: getCurrentRevisionByItemID Error on Creation'
GO

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

WITH EXECUTE AS OWNER
AS
SELECT dd.[DocID]
      ,[LibTitle]
      ,[DocContent]
      ,[DocAscii]
      ,[Config]
      ,dd.[DTS]
      ,dd.[UserID]
      ,dd.[LastChanged]
      ,[FileExtension],
		(SELECT COUNT(*) FROM [DROUsages] WHERE [DROUsages].[DocID]=[dd].[DocID]) [DROUsageCount],
		(SELECT COUNT(*) FROM [Entries] WHERE [Entries].[DocID]=[dd].[DocID]) [EntryCount],
		(SELECT COUNT(*) FROM [Pdfs] WHERE [Pdfs].[DocID]=[dd].[DocID]) [PdfCount]

	FROM [Documents] dd
	INNER JOIN [Entries] ee on dd.docid = ee.docid
	INNER JOIN vefn_ChildItems(@ItemID) cc on cc.contentid = ee.contentid
	RETURN

/****** Object:  StoredProcedure [dbo].[getRoUsagesForProc]    Script Date: 10/21/2011 15:31:51 ******/
SET ANSI_NULLS ON
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getLibDocsForProc Succeeded'
ELSE PRINT 'Procedure Creation: getLibDocsForProc Error on Creation'
GO

/****** Object:  StoredProcedure [getPreviousRevisionByItemID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getPreviousRevisionByItemID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getPreviousRevisionByItemID];
GO
	
/*
getPreviousRevisionByItemID(41)
*/	
create PROCEDURE [dbo].[getPreviousRevisionByItemID]
(
	@ItemID int
)
WITH EXECUTE AS OWNER
AS
	SELECT
		[Revisions].[RevisionID],
		[ItemID],
		[TypeID],
		[RevisionNumber],
		[RevisionDate],
		[Notes],
		[Config],
		[DTS],
		[UserID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Checks] WHERE [Checks].[RevisionID]=[Revisions].[RevisionID]) [CheckCount],
		(SELECT COUNT(*) FROM [Versions] WHERE [Versions].[RevisionID]=[Revisions].[RevisionID]) [VersionCount]
	FROM [Revisions]
inner join
(
select top 1 revisionid,mxvid from
(
select top 2 revisionid,mxvid from
(
select rr.revisionid,max(vv.versionid) mxvid
from items ii
inner join revisions rr on ii.itemid = rr.itemid
inner join versions vv on rr.revisionid = vv.revisionid
inner join stages ss on vv.stageid = ss.stageid
where ss.isapproved = 1
and ii.itemid = @ItemID
group by rr.revisionid
union
select null,null
) ds
order by mxvid desc
) ah
order by revisionid
) rr on [Revisions].revisionid = rr.revisionid
--	WHERE [ItemID] = @ItemID
--	ORDER BY [RevisionID] DESC
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getPreviousRevisionByItemID Succeeded'
ELSE PRINT 'Procedure Creation: getPreviousRevisionByItemID Error on Creation'
GO

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

(
	@RevisionID int
)
WITH EXECUTE AS OWNER
AS
	SELECT
		[RevisionID],
		[ItemID],
		[TypeID],
		[RevisionNumber],
		[RevisionDate],
		[Notes],
		[Config],
		[DTS],
		[UserID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Checks] WHERE [Checks].[RevisionID]=[Revisions].[RevisionID]) [CheckCount],
		(SELECT COUNT(*) FROM [Versions] WHERE [Versions].[RevisionID]=[Revisions].[RevisionID]) [VersionCount]
	FROM [Revisions]
	WHERE [RevisionID]=@RevisionID

	SELECT
		[Checks].[CheckID],
		[Checks].[RevisionID],
		[Checks].[StageID],
		[Checks].[ConsistencyChecks],
		[Checks].[DTS],
		[Checks].[UserID],
		[Checks].[LastChanged],
		[Stages].[Name] [Stage_Name],
		[Stages].[Description] [Stage_Description],
		[Stages].[IsApproved] [Stage_IsApproved],
		[Stages].[DTS] [Stage_DTS],
		[Stages].[UserID] [Stage_UserID]
	FROM [Checks]
		JOIN [Stages]  ON
			[Stages].[StageID]=[Checks].[StageID]
	WHERE
		[Checks].[RevisionID]=@RevisionID


	SELECT
		[Versions].[VersionID],
		[Versions].[RevisionID],
		[Versions].[StageID],
		[Versions].[PDF],
		[Versions].[SummaryPDF],
		[Versions].[DTS],
		[Versions].[UserID],
		[Versions].[LastChanged],
		[Stages].[Name] [Stage_Name],
		[Stages].[Description] [Stage_Description],
		[Stages].[IsApproved] [Stage_IsApproved],
		[Stages].[DTS] [Stage_DTS],
		[Stages].[UserID] [Stage_UserID]
	FROM [Versions]
		JOIN [Stages]  ON
			[Stages].[StageID]=[Versions].[StageID]
	WHERE
		[Versions].[RevisionID]=@RevisionID

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

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

(
	@ItemID int,
	@RevisionNumber nvarchar(50)
)
WITH EXECUTE AS OWNER
AS
declare @RevisionID int
set @RevisionID = (select revisionid from revisions where itemid = @itemid and revisionnumber = @RevisionNumber)
	SELECT
		[RevisionID],
		[ItemID],
		[TypeID],
		[RevisionNumber],
		[RevisionDate],
		[Notes],
		[Config],
		[DTS],
		[UserID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Checks] WHERE [Checks].[RevisionID]=[Revisions].[RevisionID]) [CheckCount],
		(SELECT COUNT(*) FROM [Versions] WHERE [Versions].[RevisionID]=[Revisions].[RevisionID]) [VersionCount]
	FROM [Revisions]
	WHERE [RevisionID]=@RevisionID

	SELECT
		[Checks].[CheckID],
		[Checks].[RevisionID],
		[Checks].[StageID],
		[Checks].[ConsistencyChecks],
		[Checks].[DTS],
		[Checks].[UserID],
		[Checks].[LastChanged],
		[Stages].[Name] [Stage_Name],
		[Stages].[Description] [Stage_Description],
		[Stages].[IsApproved] [Stage_IsApproved],
		[Stages].[DTS] [Stage_DTS],
		[Stages].[UserID] [Stage_UserID]
	FROM [Checks]
		JOIN [Stages]  ON
			[Stages].[StageID]=[Checks].[StageID]
	WHERE
		[Checks].[RevisionID]=@RevisionID


	SELECT
		[Versions].[VersionID],
		[Versions].[RevisionID],
		[Versions].[StageID],
		[Versions].[DTS],
		[Versions].[UserID],
		[Versions].[LastChanged],
		[Versions].[PDF],
		[Versions].[SummaryPDF],
		[Stages].[Name] [Stage_Name],
		[Stages].[Description] [Stage_Description],
		[Stages].[IsApproved] [Stage_IsApproved],
		[Stages].[DTS] [Stage_DTS],
		[Stages].[UserID] [Stage_UserID]
	FROM [Versions]
		JOIN [Stages]  ON
			[Stages].[StageID]=[Versions].[StageID]
	WHERE
		[Versions].[RevisionID]=@RevisionID

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

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

WITH EXECUTE AS OWNER
AS
	SELECT
		[RevisionID],
		[ItemID],
		[TypeID],
		[RevisionNumber],
		[RevisionDate],
		[Notes],
		[Config],
		[DTS],
		[UserID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Checks] WHERE [Checks].[RevisionID]=[Revisions].[RevisionID]) [CheckCount],
		(SELECT COUNT(*) FROM [Versions] WHERE [Versions].[RevisionID]=[Revisions].[RevisionID]) [VersionCount]
	FROM [Revisions]
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getRevisions Succeeded'
ELSE PRINT 'Procedure Creation: getRevisions Error on Creation'
GO

/****** Object:  StoredProcedure [getRevisionsByItemID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getRevisionsByItemID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getRevisionsByItemID];
GO
	
CREATE PROCEDURE [dbo].[getRevisionsByItemID]
(
	@ItemID int
)
WITH EXECUTE AS OWNER
AS
	SELECT
		[RevisionID],
		[ItemID],
		[TypeID],
		[RevisionNumber],
		[RevisionDate],
		[Notes],
		[Config],
		[DTS],
		[UserID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Checks] WHERE [Checks].[RevisionID]=[Revisions].[RevisionID]) [CheckCount],
		(SELECT COUNT(*) FROM [Versions] WHERE [Versions].[RevisionID]=[Revisions].[RevisionID]) [VersionCount]
	FROM [Revisions]
	WHERE [ItemID] = @ItemID
	ORDER BY [RevisionID] DESC
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getRevisionsByItemID Succeeded'
ELSE PRINT 'Procedure Creation: getRevisionsByItemID Error on Creation'
GO

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

WITH EXECUTE AS OWNER
AS
	SELECT
		[ROUsageID],
		rr.[ContentID],
		[ROID],
		[Config],
		[DTS],
		[UserID],
		[LastChanged],
		[RODbID]
	FROM [RoUsages] rr
	INNER JOIN vefn_ChildItems(@ItemID) cc on cc.contentid = rr.contentid
	RETURN

/****** Object:  UserDefinedFunction [dbo].[vefn_CheckXML]    Script Date: 10/25/2011 18:36:53 ******/
SET ANSI_NULLS ON
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getRoUsagesForProc Succeeded'
ELSE PRINT 'Procedure Creation: getRoUsagesForProc Error on Creation'
GO

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

(
	@StageID int
)
WITH EXECUTE AS OWNER
AS
	SELECT
		[StageID],
		[Name],
		[Description],
		[IsApproved],
		[DTS],
		[UserID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Checks] WHERE [Checks].[StageID]=[Stages].[StageID]) [CheckCount],
		(SELECT COUNT(*) FROM [Versions] WHERE [Versions].[StageID]=[Stages].[StageID]) [VersionCount]
	FROM [Stages]
	WHERE [StageID]=@StageID

	SELECT
		[Checks].[CheckID],
		[Checks].[RevisionID],
		[Checks].[StageID],
		[Checks].[ConsistencyChecks],
		[Checks].[DTS],
		[Checks].[UserID],
		[Checks].[LastChanged],
		[Revisions].[ItemID] [Revision_ItemID],
		[Revisions].[TypeID] [Revision_TypeID],
		[Revisions].[RevisionNumber] [Revision_RevisionNumber],
		[Revisions].[RevisionDate] [Revision_RevisionDate],
		[Revisions].[Notes] [Revision_Notes],
		[Revisions].[Config] [Revision_Config],
		[Revisions].[DTS] [Revision_DTS],
		[Revisions].[UserID] [Revision_UserID]
	FROM [Checks]
		JOIN [Revisions]  ON
			[Revisions].[RevisionID]=[Checks].[RevisionID]
	WHERE
		[Checks].[StageID]=@StageID


	SELECT
		[Versions].[VersionID],
		[Versions].[RevisionID],
		[Versions].[StageID],
		[Versions].[PDF],
		[Versions].[SummaryPDF],
		[Versions].[DTS],
		[Versions].[UserID],
		[Versions].[LastChanged],
		[Revisions].[ItemID] [Revision_ItemID],
		[Revisions].[TypeID] [Revision_TypeID],
		[Revisions].[RevisionNumber] [Revision_RevisionNumber],
		[Revisions].[RevisionDate] [Revision_RevisionDate],
		[Revisions].[Notes] [Revision_Notes],
		[Revisions].[Config] [Revision_Config],
		[Revisions].[DTS] [Revision_DTS],
		[Revisions].[UserID] [Revision_UserID]
	FROM [Versions]
		JOIN [Revisions]  ON
			[Revisions].[RevisionID]=[Versions].[RevisionID]
	WHERE
		[Versions].[StageID]=@StageID

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

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

WITH EXECUTE AS OWNER
AS
	SELECT
		[StageID],
		[Name],
		[Description],
		[IsApproved],
		[DTS],
		[UserID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Checks] WHERE [Checks].[StageID]=[Stages].[StageID]) [CheckCount],
		(SELECT COUNT(*) FROM [Versions] WHERE [Versions].[StageID]=[Stages].[StageID]) [VersionCount]
	FROM [Stages]
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getStages Succeeded'
ELSE PRINT 'Procedure Creation: getStages Error on Creation'
GO

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

WITH EXECUTE AS OWNER
AS
SELECT tt.[TransitionID]
      ,[FromID]
      ,[ToID]
      ,[RangeID]
      ,[IsRange]
      ,[TranType]
      ,tt.[Config]
      ,tt.[DTS]
      ,tt.[UserID]
      ,tt.[LastChanged]
	  ,(SELECT COUNT(*) FROM [ZTransitions] WHERE [ZTransitions].[TransitionID]=[tt].[TransitionID]) [ZTransitionCount]
,cc.text ContentText
	FROM [Transitions] tt
inner join contents cc on tt.fromid = cc.contentid
	WHERE transitionid in (select transitionid from vefn_FindExternalFromTransitions(@ItemID))
	RETURN

/****** Object:  StoredProcedure [dbo].[getTransitionsToProc]    Script Date: 10/21/2011 15:24:11 ******/
SET ANSI_NULLS ON
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getTransitionsFromProc Succeeded'
ELSE PRINT 'Procedure Creation: getTransitionsFromProc Error on Creation'
GO

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

WITH EXECUTE AS OWNER
AS
SELECT tt.[TransitionID]
      ,[FromID]
      ,[ToID]
      ,[RangeID]
      ,[IsRange]
      ,[TranType]
      ,tt.[Config]
      ,tt.[DTS]
      ,tt.[UserID]
      ,tt.[LastChanged]
	  ,(SELECT COUNT(*) FROM [ZTransitions] WHERE [ZTransitions].[TransitionID]=[tt].[TransitionID]) [ZTransitionCount]
,cc.text ContentText
	FROM [Transitions] tt
inner join contents cc on tt.fromid = cc.contentid
	WHERE transitionid in (select transitionid from vefn_FindExternalTransitions(@ItemID))
	RETURN

/****** Object:  UserDefinedFunction [dbo].[vefn_FindExternalFromTransitions]    Script Date: 10/21/2011 15:27:17 ******/
SET ANSI_NULLS ON
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getTransitionsToProc Succeeded'
ELSE PRINT 'Procedure Creation: getTransitionsToProc Error on Creation'
GO

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

(
	@VersionID int
)
WITH EXECUTE AS OWNER
AS
	SELECT
		[VersionID],
		[RevisionID],
		[StageID],
		[PDF],
		[SummaryPDF],
		[DTS],
		[UserID],
		[LastChanged]
	FROM [Versions]
	WHERE [VersionID]=@VersionID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getVersion Succeeded'
ELSE PRINT 'Procedure Creation: getVersion Error on Creation'
GO

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

WITH EXECUTE AS OWNER
AS
	SELECT
		[VersionID],
		[RevisionID],
		[StageID],
		[PDF],
		[SummaryPDF],
		[DTS],
		[UserID],
		[LastChanged]
	FROM [Versions]
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getVersions Succeeded'
ELSE PRINT 'Procedure Creation: getVersions Error on Creation'
GO

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

(
	@RevisionID int
)
WITH EXECUTE AS OWNER
AS

	SELECT
		[Versions].[VersionID],
		[Versions].[RevisionID],
		[Versions].[StageID],
		[Versions].[PDF],
		[Versions].[SummaryPDF],
		[Versions].[DTS],
		[Versions].[UserID],
		[Versions].[LastChanged],
		[Stages].[Name] [Stage_Name],
		[Stages].[Description] [Stage_Description],
		[Stages].[IsApproved] [Stage_IsApproved],
		[Stages].[DTS] [Stage_DTS],
		[Stages].[UserID] [Stage_UserID]
	FROM [Versions]
		JOIN [Stages]  ON
			[Stages].[StageID]=[Versions].[StageID]
	WHERE
		[Versions].[RevisionID]=@RevisionID

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

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

(
	@StageID int
)
WITH EXECUTE AS OWNER
AS

	SELECT
		[Versions].[VersionID],
		[Versions].[RevisionID],
		[Versions].[StageID],
		[Versions].[PDF],
		[Versions].[SummaryPDF],
		[Versions].[DTS],
		[Versions].[UserID],
		[Versions].[LastChanged],
		[Revisions].[ItemID] [Revision_ItemID],
		[Revisions].[TypeID] [Revision_TypeID],
		[Revisions].[RevisionNumber] [Revision_RevisionNumber],
		[Revisions].[RevisionDate] [Revision_RevisionDate],
		[Revisions].[Notes] [Revision_Notes],
		[Revisions].[Config] [Revision_Config],
		[Revisions].[DTS] [Revision_DTS],
		[Revisions].[UserID] [Revision_UserID]
	FROM [Versions]
		JOIN [Revisions]  ON
			[Revisions].[RevisionID]=[Versions].[RevisionID]
	WHERE
		[Versions].[StageID]=@StageID

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

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

(
	@CheckID int,
	@RevisionID int,
	@StageID int,
	@ConsistencyChecks xml=null,
	@DTS datetime,
	@UserID nvarchar(100),
	@LastChanged timestamp,
	@newLastChanged timestamp output
)
WITH EXECUTE AS OWNER
AS
BEGIN TRY -- Try Block
	BEGIN TRANSACTION
	UPDATE [Checks]
		SET
			[RevisionID]=@RevisionID,
			[StageID]=@StageID,
			[ConsistencyChecks]=@ConsistencyChecks,
			[DTS]=@DTS,
			[UserID]=@UserID
		WHERE [CheckID]=@CheckID AND [LastChanged]=@LastChanged
	IF @@ROWCOUNT = 0
	BEGIN
		IF NOT exists(select * from [Checks] WHERE [CheckID]=@CheckID)
			RAISERROR('Check record has been deleted by another user', 16, 1)
		ELSE           	
			RAISERROR('Check has been edited by another user', 16, 1)            	
	END

		SELECT 		@newLastChanged=[LastChanged]	
		FROM [Checks] WHERE [CheckID]=@CheckID		

	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: updateCheck Succeeded'
ELSE PRINT 'Procedure Creation: updateCheck Error on Creation'
GO

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

(
	@RevisionID int,
	@ItemID int,
	@TypeID int,
	@RevisionNumber nvarchar(50)=null,
	@RevisionDate datetime=null,
	@Notes nvarchar(MAX)=null,
	@Config xml=null,
	@DTS datetime,
	@UserID nvarchar(100),
	@LastChanged timestamp,
	@newLastChanged timestamp output
)
WITH EXECUTE AS OWNER
AS
BEGIN TRY -- Try Block
	BEGIN TRANSACTION
	UPDATE [Revisions]
		SET
			[ItemID]=@ItemID,
			[TypeID]=@TypeID,
			[RevisionNumber]=@RevisionNumber,
			[RevisionDate]=@RevisionDate,
			[Notes]=@Notes,
			[Config]=@Config,
			[DTS]=@DTS,
			[UserID]=@UserID
		WHERE [RevisionID]=@RevisionID AND [LastChanged]=@LastChanged
	IF @@ROWCOUNT = 0
	BEGIN
		IF NOT exists(select * from [Revisions] WHERE [RevisionID]=@RevisionID)
			RAISERROR('Revision record has been deleted by another user', 16, 1)
		ELSE           	
			RAISERROR('Revision has been edited by another user', 16, 1)            	
	END

		SELECT 		@newLastChanged=[LastChanged]	
		FROM [Revisions] WHERE [RevisionID]=@RevisionID		

	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: updateRevision Succeeded'
ELSE PRINT 'Procedure Creation: updateRevision Error on Creation'
GO

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

(
	@StageID int,
	@Name nvarchar(50),
	@Description nvarchar(200)=null,
	@IsApproved int,
	@DTS datetime,
	@UserID nvarchar(100),
	@LastChanged timestamp,
	@newLastChanged timestamp output
)
WITH EXECUTE AS OWNER
AS
BEGIN TRY -- Try Block
	BEGIN TRANSACTION
	UPDATE [Stages]
		SET
			[Name]=@Name,
			[Description]=@Description,
			[IsApproved]=@IsApproved,
			[DTS]=@DTS,
			[UserID]=@UserID
		WHERE [StageID]=@StageID AND [LastChanged]=@LastChanged
	IF @@ROWCOUNT = 0
	BEGIN
		IF NOT exists(select * from [Stages] WHERE [StageID]=@StageID)
			RAISERROR('Stage record has been deleted by another user', 16, 1)
		ELSE           	
			RAISERROR('Stage has been edited by another user', 16, 1)            	
	END

		SELECT 		@newLastChanged=[LastChanged]	
		FROM [Stages] WHERE [StageID]=@StageID		

	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: updateStage Succeeded'
ELSE PRINT 'Procedure Creation: updateStage Error on Creation'
GO

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

(
	@VersionID int,
	@RevisionID int,
	@StageID int,
	@PDF varbinary(MAX)=null,
	@SummaryPDF varbinary(MAX)=null,
	@DTS datetime,
	@UserID nvarchar(200),
	@LastChanged timestamp,
	@newLastChanged timestamp output
)
WITH EXECUTE AS OWNER
AS
BEGIN TRY -- Try Block
	BEGIN TRANSACTION
	UPDATE [Versions]
		SET
			[RevisionID]=@RevisionID,
			[StageID]=@StageID,
			[PDF]=@PDF,
			[SummaryPDF]=@SummaryPDF,
			[DTS]=@DTS,
			[UserID]=@UserID
		WHERE [VersionID]=@VersionID AND [LastChanged]=@LastChanged
	IF @@ROWCOUNT = 0
	BEGIN
		IF NOT exists(select * from [Versions] WHERE [VersionID]=@VersionID)
			RAISERROR('Version record has been deleted by another user', 16, 1)
		ELSE           	
			RAISERROR('Version has been edited by another user', 16, 1)            	
	END

		SELECT 		@newLastChanged=[LastChanged]	
		FROM [Versions] WHERE [VersionID]=@VersionID		

	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: updateVersion Succeeded'
ELSE PRINT 'Procedure Creation: updateVersion Error on Creation'
GO

/****** Object:  StoredProcedure [vefn_CheckAllXML] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[vefn_CheckAllXML]') AND OBJECTPROPERTY(id,N'IsScalarFunction') = 1)
    DROP FUNCTION [vefn_CheckAllXML];
GO
	
/*
select [dbo].[vefn_CheckAllXML](1)
*/
CREATE function [dbo].[vefn_CheckAllXML](@DocVersionID int)
returns xml
begin
	declare @ChkXML xml
	set @ChkXML =
	(
		select
		(--ro inconsistencies accross set
			select ItemID,ROID,ROValue from
			(
				select rr.itemid,r1.value('@ROID','varchar(max)') roid,r1.value('@ROValue','varchar(max)') rovalue
				from checks cc
				inner join revisions rr on cc.revisionid = rr.revisionid
				cross apply cc.consistencychecks.nodes('//ROCheck') t1(r1)
				where cc.checkid in
				--latest checkid with approved stage for each itemid
				(
					select max(cc.checkid) checkid
					from checks cc
					inner join stages ss on cc.stageid = ss.stageid
					inner join revisions rr on cc.revisionid = rr.revisionid
					where ss.isapproved = 1
					group by rr.itemid
				)
				and rr.itemid in (select itemid from vefn_siblingitems((select itemid from docversions where versionid = @docversionid),0))
			) ROCheck
			where roid in
			(
				--get roids that has more than 1 rovalue from distinct roid and rovalue from checks for latest checkid with approved stage for each itemid
				select roid from
				(
					--distinct roid and rovalue from checks for latest checkid with approved stage for each itemid
					select distinct r1.value('@ROID','varchar(max)') roid,r1.value('@ROValue','varchar(max)') rovalue
					from checks cc
					cross apply cc.consistencychecks.nodes('//ROCheck') t1(r1)
					where cc.checkid in
					--latest checkid with approved stage for each itemid
					(
						select max(cc.checkid) checkid
						from checks cc
						inner join stages ss on cc.stageid = ss.stageid
						inner join revisions rr on cc.revisionid = rr.revisionid
						where ss.isapproved = 1
						group by rr.itemid
					)
				) t1
				group by roid having count(*) > 1
			)
			order by itemid
			for xml auto,root('ROChecks'),type
		),
--jcb
		(--transition inconsistencies accross set
			select ItemID,TransitionID,TransitionValue from
			(
				select rr.itemid,r1.value('@TransitionID','int') transitionid,r1.value('@TransitionValue','varchar(max)') transitionvalue
				from checks cc
				inner join revisions rr on cc.revisionid = rr.revisionid
				cross apply cc.consistencychecks.nodes('//TransitionCheck') t1(r1)
				where cc.checkid in
				--latest checkid with approved stage for each itemid
				(
					select max(cc.checkid) checkid
					from checks cc
					inner join stages ss on cc.stageid = ss.stageid
					inner join revisions rr on cc.revisionid = rr.revisionid
					where ss.isapproved = 1
					group by rr.itemid
				)
				and rr.itemid in (select itemid from vefn_siblingitems((select itemid from docversions where versionid = @docversionid),0))
			) TransitionCheck
			where transitionid in
			(
				--get transitionids that has more than 1 transitionvalue from distinct transitionid and transitionvalue from checks for latest checkid with approved stage for each itemid
				select transitionid from
				(
					--distinct docid and docdate from checks for latest checkid with approved stage for each itemid
					select distinct r1.value('@TransitionID','int') transitionid,r1.value('@TransitionValue','varchar(max)') transitionvalue
					from checks cc
					cross apply cc.consistencychecks.nodes('//TransitionCheck') t1(r1)
					where cc.checkid in
					--latest checkid with approved stage for each itemid
					(
						select max(cc.checkid) checkid
						from checks cc
						inner join stages ss on cc.stageid = ss.stageid
						inner join revisions rr on cc.revisionid = rr.revisionid
						where ss.isapproved = 1
						group by rr.itemid
					)
				) t1
				group by transitionid having count(*) > 1
			)
			order by itemid
			for xml auto,root('TransitionChecks'),type
		),
--end jcb
		(--libdoc inconsistencies accross set
			select ItemID,DocID,DocDate from
			(
				select rr.itemid,r1.value('@DocID','int') docid,r1.value('@DocDate','datetime') docdate
				from checks cc
				inner join revisions rr on cc.revisionid = rr.revisionid
				cross apply cc.consistencychecks.nodes('//LibDocCheck') t1(r1)
				where cc.checkid in
				--latest checkid with approved stage for each itemid
				(
					select max(cc.checkid) checkid
					from checks cc
					inner join stages ss on cc.stageid = ss.stageid
					inner join revisions rr on cc.revisionid = rr.revisionid
					where ss.isapproved = 1
					group by rr.itemid
				)
				and rr.itemid in (select itemid from vefn_siblingitems((select itemid from docversions where versionid = @docversionid),0))
			) LibDocCheck
			where docid in
			(
				--get docids that has more than 1 docdate from distinct docid and docdate from checks for latest checkid with approved stage for each itemid
				select docid from
				(
					--distinct docid and docdate from checks for latest checkid with approved stage for each itemid
					select distinct r1.value('@DocID','int') docid,r1.value('@DocDate','datetime') docdate
					from checks cc
					cross apply cc.consistencychecks.nodes('//LibDocCheck') t1(r1)
					where cc.checkid in
					--latest checkid with approved stage for each itemid
					(
						select max(cc.checkid) checkid
						from checks cc
						inner join stages ss on cc.stageid = ss.stageid
						inner join revisions rr on cc.revisionid = rr.revisionid
						where ss.isapproved = 1
						group by rr.itemid
					)
				) t1
				group by docid having count(*) > 1
			)
			order by itemid
			for xml auto,root('LibDocChecks'),type
		)
		for xml path(''),ROOT ('ConsistencyChecks'),type
	)
	return @ChkXML
end
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'ScalarFunction Creation: vefn_CheckAllXML Succeeded'
ELSE PRINT 'ScalarFunction Creation: vefn_CheckAllXML Error on Creation'
GO

/****** Object:  StoredProcedure [vefn_CheckAllXMLByItemID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[vefn_CheckAllXMLByItemID]') AND OBJECTPROPERTY(id,N'IsScalarFunction') = 1)
    DROP FUNCTION [vefn_CheckAllXMLByItemID];
GO
	
/*
declare @myxml xml
set @myxml = (select dbo.vefn_checkallxml(1))
select dbo.vefn_checkallxmlbyitemid(1,@myxml)
*/
CREATE function [dbo].[vefn_CheckAllXMLByItemID](@ItemID int,@MyXml xml)
returns xml
begin
	declare @ChkXML xml
	declare @ROCheck table
	(
		ItemID int,
		ROID varchar(max),
		ROValue varchar(max)
	)
	insert into @ROCheck
	select r2.value('@ItemID','int'),r2.value('@ROID','varchar(max)'),r2.value('@ROValue','varchar(max)')
	from @MyXml.nodes('//ROCheck') t2(r2)
--jcb
	declare @TransitionCheck table
	(
		ItemID int,
		TransitionID int,
		TransitionValue varchar(max)
	)
	insert into @TransitionCheck
	select r2.value('@ItemID','int'),r2.value('@TransitionID','int'),r2.value('@TransitionValue','varchar(max)')
	from @MyXml.nodes('//TransitionCheck') t2(r2)
--end jcb
	declare @LibDocCheck table
	(
		ItemID int,
		DocID int,
		DocDate datetime
	)
	insert into @LibDocCheck
	select r2.value('@ItemID','int'),r2.value('@DocID','int'),r2.value('@DocDate','datetime')
	from @MyXml.nodes('//LibDocCheck') t2(r2)
	set @ChkXML =
	(
	select
	(select * from @ROCheck ROCheck where itemid = @ItemID for xml auto, root('ROChecks'),type),
	(select * from @TransitionCheck TransitionCheck where itemid = @ItemID for xml auto, root('TransitionChecks'),type),
	(select * from @LibDocCheck LibDocCheck where itemid = @ItemID for xml auto, root('LibDocChecks'),type)
	for xml path(''),ROOT ('ConsistencyChecks'),type
	)
	return @ChkXML
end
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'ScalarFunction Creation: vefn_CheckAllXMLByItemID Succeeded'
ELSE PRINT 'ScalarFunction Creation: vefn_CheckAllXMLByItemID Error on Creation'
GO

/****** Object:  StoredProcedure [vefn_CheckXML] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[vefn_CheckXML]') AND OBJECTPROPERTY(id,N'IsScalarFunction') = 1)
    DROP FUNCTION [vefn_CheckXML];
GO
	
/*
declare @NewXML xml
select @NewXML = consistencychecks from checks where checkid = 48
set @NewXML = cast(replace(cast(@NewXML as varchar(max)),'2011-08-24T11:20:57.027','2011-12-25T00:00:00.000') as xml)
set @NewXML = cast(replace(cast(@NewXML as varchar(max)),'88%','88.12345%') as xml)
set @NewXML = cast(replace(cast(@NewXML as varchar(max)),'="6%','="6.12345%') as xml)
set @NewXML = cast(replace(cast(@NewXML as varchar(max)),'23%','23.12345%') as xml)
set @NewXML = cast(replace(cast(@NewXML as varchar(max)),'90%','90.12345%') as xml)
set @NewXML = cast(replace(cast(@NewXML as varchar(max)),', Step 13',', Step 14') as xml)r
declare @OldXML xml
select @OldXML = consistencychecks from checks where checkid = 1
--select .dbo.vefn_CheckXML(@NewXML, @OldXML)
select ii.*,ss.*,.dbo.vefn_CheckXML(@NewXML, consistencychecks) chkxml from checks cc
inner join revisions rr on cc.revisionid = rr.revisionid
inner join items ii on rr.itemid = ii.itemid
inner join stages ss on cc.stageid = ss.stageid
*/
CREATE function [dbo].[vefn_CheckXML](@NewXML xml, @OldXML xml)
returns xml
begin
declare @ChkXML xml
set @ChkXML =
(
select
(
select * from
(
	select ah.ROID,OldROValue,ROValue from
	(
		select r1.value('@ROID','varchar(20)') roid,r1.value('@ROValue','varchar(max)') oldrovalue
		from @OldXML.nodes('//ROCheck') as t1(r1)
	) ah
	inner join
	(
		select distinct r2.value('@ROID','varchar(20)') roid,r2.value('@ROValue','varchar(max)') rovalue
		from @NewXML.nodes('//ROCheck') as t2(r2)
	) ds
	on ah.roid = ds.roid
	and oldrovalue != rovalue
) ROCheck
for xml auto,root('ROChecks'),type
)
	,
	(
select * from
(
		select ah.TransitionID,OldTransitionValue,TransitionValue from
		(
			select r1.value('@TransitionID','int') TransitionID,r1.value('@TransitionValue','varchar(max)') OldTransitionValue
			from @OldXML.nodes('//TransitionCheck') as t1(r1)
		) ah
		inner join
		(
			select distinct r2.value('@TransitionID','int') TransitionID,r2.value('@TransitionValue','varchar(max)') TransitionValue
			from @NewXML.nodes('//TransitionCheck') as t2(r2)
		) ds
		on ah.TransitionID = ds.TransitionID
		and OldTransitionValue != TransitionValue
) TransitionCheck
	  for xml auto,root('TransitionChecks'),type
)
	,
	(
select * from
(
		select ah.DocID,OldDocDate,DocDate from
		(
			select r1.value('@DocID','int') docid,r1.value('@DocDate','datetime') olddocdate
			from @OldXML.nodes('//LibDocCheck') as t1(r1)
		) ah
		inner join
		(
			select distinct r2.value('@DocID','int') docid,r2.value('@DocDate','datetime') docdate
			from @NewXML.nodes('//LibDocCheck') as t2(r2)
		) ds
		on ah.docid = ds.docid
		and olddocdate != docdate
) LibDocCheck
	  for xml auto,root('LibDocChecks'),type
)
for xml path(''),ROOT ('ConsistencyChecks'),type
)
return @ChkXML
end
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'ScalarFunction Creation: vefn_CheckXML Succeeded'
ELSE PRINT 'ScalarFunction Creation: vefn_CheckXML Error on Creation'
GO

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

*/
CREATE PROCEDURE [dbo].[vesp_GetAllConsistencyIssues]
(
	@DocVersionID int
)
AS BEGIN
	declare @myxml xml
	set @myxml = (select dbo.vefn_checkallxml(@DocVersionID))
	DECLARE @Items TABLE
	(
		ItemID int PRIMARY KEY
	)
	INSERT INTO @Items
	select r1.value('@ItemID','int') itemid
	from @myxml.nodes('//ROCheck') t1(r1)
	union
	select r1.value('@ItemID','int') itemid
	from @myxml.nodes('//TransitionCheck') t1(r1)
	union
	select r1.value('@ItemID','int') itemid
	from @myxml.nodes('//LibDocCheck') t1(r1)
	SELECT
	ii.[ItemID]
	,ii.[PreviousID]
	,ii.[ContentID]
	,ii.[DTS]
	,ii.[UserID]
	,ii.[LastChanged]
	,CC.[Number]
	,CC.[Text]
	,CC.[Type]
	,CC.[FormatID]
	,CC.[Config]
	,CC.[DTS] [cDTS]
	,CC.[UserID] [cUserID]
	,CC.[LastChanged] [cLastChanged]
	,(SELECT COUNT(*) FROM [Annotations] WHERE [Annotations].[ItemID]=ii.[ItemID]) [AnnotationCount]
	,(SELECT COUNT(*) FROM [DocVersions] WHERE [DocVersions].[ItemID]=ii.[ItemID]) [DocVersionCount]
	,(SELECT COUNT(*) FROM [Items] [Next] WHERE [Next].[PreviousID]=ii.[ItemID]) [NextCount]
	,(SELECT COUNT(*) FROM [Parts] WHERE [Parts].[ItemID]=ii.[ItemID]) [PartCount]
	,(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[RangeID]=ii.[ItemID]) [Transition_RangeIDCount]
	,(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[ToID]=ii.[ItemID]) [Transition_ToIDCount]
	,(SELECT COUNT(*) FROM [Details] WHERE [Details].[ContentID]=CC.[ContentID]) [DetailCount]
	,(SELECT COUNT(*) FROM [Entries] WHERE [Entries].[ContentID]=CC.[ContentID]) [EntryCount]
	,(SELECT COUNT(*) FROM [Grids] WHERE [Grids].[ContentID]=CC.[ContentID]) [GridCount]
	,(SELECT COUNT(*) FROM [Images] WHERE [Images].[ContentID]=CC.[ContentID]) [ImageCount]
	,(SELECT COUNT(*) FROM [Items] WHERE [Items].[ContentID]=CC.[ContentID]) [ItemCount]
	,(SELECT COUNT(*) FROM [Parts] WHERE [Parts].[ContentID]=CC.[ContentID]) [cPartCount]
	,(SELECT COUNT(*) FROM [RoUsages] WHERE [RoUsages].[ContentID]=CC.[ContentID]) [RoUsageCount]
	,(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[FromID]=CC.[ContentID]) [TransitionCount]
	,(SELECT COUNT(*) FROM [ZContents] WHERE [ZContents].[ContentID]=CC.[ContentID]) [ZContentCount]
	,(select dbo.vefn_CheckAllXMLByItemID(ii.itemid,@myxml)) ChkXml
	FROM [Items] ii
	INNER JOIN [Contents] cc ON ii.[ContentID] = cc.[ContentID]
	where ii.ItemID in (select ItemID from @Items)
--	order by ii.itemid
END
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: vesp_GetAllConsistencyIssues Succeeded'
ELSE PRINT 'Procedure Creation: vesp_GetAllConsistencyIssues Error on Creation'
GO

/****** Object:  StoredProcedure [vesp_GetConsistencyCheckProcedures] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[vesp_GetConsistencyCheckProcedures]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [vesp_GetConsistencyCheckProcedures];
GO
	
/*
declare @myxml xml
select @myxml = consistencychecks from checks where checkid = 2
declare @docversionid int
set @docversionid = 1
exec vesp_GetConsistencyCheckProcedures @docversionid, @myxml
*/
CREATE PROCEDURE [dbo].[vesp_GetConsistencyCheckProcedures]
(
	@DocVersionID int,
	@MyXml xml
)
AS BEGIN
--to be removed
--	set @myxml = cast(replace(cast(@myxml as varchar(max)),'2011-08-24T11:20:57.027','2011-12-25T00:00:00.000') as xml)
--	set @myxml = cast(replace(cast(@myxml as varchar(max)),'88%','88.12345%') as xml)
--	set @myxml = cast(replace(cast(@myxml as varchar(max)),'="6%','="6.12345%') as xml)
--	set @myxml = cast(replace(cast(@myxml as varchar(max)),'23%','23.12345%') as xml)
--	set @myxml = cast(replace(cast(@myxml as varchar(max)),'90%','90.12345%') as xml)
--	set @myxml = cast(replace(cast(@myxml as varchar(max)),', Step 13',', Step 14') as xml)
--end to be removed
	DECLARE @Items TABLE
	(
		ID int identity(1,1),
		ItemID int
	)
	INSERT INTO @Items (itemid)
	select roc.value('@ItemID','int') itemid
	from @myxml.nodes('//ConsistencyChecks') t1(roc)
	declare @LatestApproved TABLE
	(
		ItemID int PRIMARY KEY,
		CheckID int
	)
	insert into @LatestApproved
	select rr.itemid,max(checkid) checkid
	from revisions rr
	inner join checks cc on rr.revisionid = cc.revisionid
	inner join stages ss on cc.stageid = ss.stageid
	where ss.isapproved = 1
	and rr.itemid in
	(select itemid from vefn_siblingitems((select itemid from docversions where versionid = @docversionid),0))
	group by rr.itemid

	SELECT
	ii.[ItemID]
	,ii.[PreviousID]
	,ii.[ContentID]
	,ii.[DTS]
	,ii.[UserID]
	,ii.[LastChanged]
	,CC.[Number]
	,CC.[Text]
	,CC.[Type]
	,CC.[FormatID]
	,CC.[Config]
	,CC.[DTS] [cDTS]
	,CC.[UserID] [cUserID]
	,CC.[LastChanged] [cLastChanged]
	,(SELECT COUNT(*) FROM [Annotations] WHERE [Annotations].[ItemID]=ii.[ItemID]) [AnnotationCount]
	,(SELECT COUNT(*) FROM [DocVersions] WHERE [DocVersions].[ItemID]=ii.[ItemID]) [DocVersionCount]
	,(SELECT COUNT(*) FROM [Items] [Next] WHERE [Next].[PreviousID]=ii.[ItemID]) [NextCount]
	,(SELECT COUNT(*) FROM [Parts] WHERE [Parts].[ItemID]=ii.[ItemID]) [PartCount]
	,(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[RangeID]=ii.[ItemID]) [Transition_RangeIDCount]
	,(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[ToID]=ii.[ItemID]) [Transition_ToIDCount]
	,(SELECT COUNT(*) FROM [Details] WHERE [Details].[ContentID]=CC.[ContentID]) [DetailCount]
	,(SELECT COUNT(*) FROM [Entries] WHERE [Entries].[ContentID]=CC.[ContentID]) [EntryCount]
	,(SELECT COUNT(*) FROM [Grids] WHERE [Grids].[ContentID]=CC.[ContentID]) [GridCount]
	,(SELECT COUNT(*) FROM [Images] WHERE [Images].[ContentID]=CC.[ContentID]) [ImageCount]
	,(SELECT COUNT(*) FROM [Items] WHERE [Items].[ContentID]=CC.[ContentID]) [ItemCount]
	,(SELECT COUNT(*) FROM [Parts] WHERE [Parts].[ContentID]=CC.[ContentID]) [cPartCount]
	,(SELECT COUNT(*) FROM [RoUsages] WHERE [RoUsages].[ContentID]=CC.[ContentID]) [RoUsageCount]
	,(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[FromID]=CC.[ContentID]) [TransitionCount]
	,(SELECT COUNT(*) FROM [ZContents] WHERE [ZContents].[ContentID]=CC.[ContentID]) [ZContentCount]
	,ribeye.ChkXml
	FROM [Items] ii
	INNER JOIN [Contents] cc ON ii.[ContentID] = cc.[ContentID]
	inner join
	(
	select la.itemid,.dbo.vefn_checkxml(@myxml,cc.consistencychecks) chkxml
	from checks cc
	inner join @LatestApproved la on cc.checkid = la.checkid
	) ribeye on ii.itemid = ribeye.itemid
left join @items iii on ii.itemid = iii.itemid
	where chkxml.exist('//ROChecks') | chkxml.exist('//TransitionChecks') | chkxml.exist('//LibDocChecks') > 0 or
	ii.ItemID in (select ItemID from @Items)
	order by isnull(iii.id,999999)
END
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: vesp_GetConsistencyCheckProcedures Succeeded'
ELSE PRINT 'Procedure Creation: vesp_GetConsistencyCheckProcedures Error on Creation'
GO