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

WITH EXECUTE AS OWNER
AS
BEGIN TRY -- Try Block
	BEGIN TRANSACTION
	delete from [Checks]
	dbcc checkident([Checks],reseed,0)
	delete from [Versions]
	dbcc checkident([Versions],reseed,0)
	delete from [Revisions]
	dbcc checkident([Revisions],reseed,0)
	delete from [Stages]
	dbcc checkident([Stages],reseed,0)
	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: purgeData Succeeded'
ELSE PRINT 'Procedure Creation: purgeData 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 [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 [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 [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 [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 [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 [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 [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 [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 [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 [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 [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 [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 [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 [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 [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 [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 [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 [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 [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 [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 [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 [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 [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 [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 [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 [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 [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],
		[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].[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