IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Checks]') AND OBJECTPROPERTY(id,N'IsTable') = 1)
ALTER TABLE [dbo].[Checks] DROP CONSTRAINT [FK_Checks_Revisions]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Versions]') AND OBJECTPROPERTY(id,N'IsTable') = 1)
ALTER TABLE [dbo].[Versions]  DROP CONSTRAINT [FK_Versions_Stages]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Versions]') AND OBJECTPROPERTY(id,N'IsTable') = 1)
ALTER TABLE [dbo].[Versions]  DROP CONSTRAINT [FK_Versions_Revisions]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Checks]') AND OBJECTPROPERTY(id,N'IsTable') = 1)
ALTER TABLE [dbo].[Checks]  DROP CONSTRAINT [FK_Checks_Stages]
GO

/****** Object:  Table [dbo].[Stages]    Script Date: 10/21/2011 15:04:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Stages]') AND OBJECTPROPERTY(id,N'IsTable') = 1)
    DROP TABLE [Stages];
GO
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE TABLE [dbo].[Stages](
	[StageID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[Description] [nvarchar](200) NULL,
	[IsApproved] [int] NOT NULL CONSTRAINT [DF_Stages_IsApproved]  DEFAULT ((0)),
	[DTS] [datetime] NOT NULL CONSTRAINT [DF_Stages_DTS]  DEFAULT (getdate()),
	[UserID] [nvarchar](100) NOT NULL CONSTRAINT [DF_Stages_UserID]  DEFAULT (upper(suser_sname())),
	[LastChanged] [timestamp] NOT NULL,
 CONSTRAINT [PK_Stages] PRIMARY KEY CLUSTERED 
(
	[StageID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{datetime}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stages', @level2type=N'COLUMN',@level2name=N'DTS'

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

/****** Object:  Table [dbo].[Revisions]    Script Date: 10/21/2011 15:06:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Revisions]') AND OBJECTPROPERTY(id,N'IsTable') = 1)
    DROP TABLE [Revisions];
GO
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE TABLE [dbo].[Revisions](
	[RevisionID] [int] IDENTITY(1,1) NOT NULL,
	[ItemID] [int] NOT NULL,
	[TypeID] [int] NOT NULL CONSTRAINT [DF_Revisions_TypeID]  DEFAULT ((1)),
	[RevisionNumber] [nvarchar](50) NULL,
	[RevisionDate] [datetime] NULL,
	[Notes] [nvarchar](max) NULL,
	[Config] [xml] NULL,
	[DTS] [datetime] NOT NULL CONSTRAINT [DF_Revisions_DTS]  DEFAULT (getdate()),
	[UserID] [nvarchar](100) NOT NULL CONSTRAINT [DF_Revisions_UserID]  DEFAULT (upper(suser_sname())),
	[LastChanged] [timestamp] NOT NULL,
 CONSTRAINT [PK_Revisions] PRIMARY KEY CLUSTERED 
(
	[RevisionID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{datetime}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Revisions', @level2type=N'COLUMN',@level2name=N'RevisionDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{datetime}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Revisions', @level2type=N'COLUMN',@level2name=N'DTS'
GO

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

/****** Object:  Table [dbo].[Checks]    Script Date: 10/21/2011 15:07:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Checks]') AND OBJECTPROPERTY(id,N'IsTable') = 1)
    DROP TABLE [Checks];
GO
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE TABLE [dbo].[Checks](
	[CheckID] [int] IDENTITY(1,1) NOT NULL,
	[RevisionID] [int] NOT NULL,
	[StageID] [int] NOT NULL,
	[ConsistencyChecks] [xml] NULL,
	[DTS] [datetime] NOT NULL CONSTRAINT [DF_Checks_DTS]  DEFAULT (getdate()),
	[UserID] [nvarchar](100) NOT NULL CONSTRAINT [DF_Checks_UserID]  DEFAULT (upper(suser_sname())),
	[LastChanged] [timestamp] NOT NULL,
 CONSTRAINT [PK_Checks] PRIMARY KEY CLUSTERED 
(
	[CheckID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{datetime}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Checks', @level2type=N'COLUMN',@level2name=N'DTS'
GO

ALTER TABLE [dbo].[Checks]  WITH CHECK ADD  CONSTRAINT [FK_Checks_Revisions] FOREIGN KEY([RevisionID])
REFERENCES [dbo].[Revisions] ([RevisionID])
GO
ALTER TABLE [dbo].[Checks] CHECK CONSTRAINT [FK_Checks_Revisions]
GO
ALTER TABLE [dbo].[Checks]  WITH CHECK ADD  CONSTRAINT [FK_Checks_Stages] FOREIGN KEY([StageID])
REFERENCES [dbo].[Stages] ([StageID])
GO
ALTER TABLE [dbo].[Checks] CHECK CONSTRAINT [FK_Checks_Stages]

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

/****** Object:  Table [dbo].[Versions]    Script Date: 10/21/2011 15:09:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Versions]') AND OBJECTPROPERTY(id,N'IsTable') = 1)
    DROP TABLE [Versions];
GO
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE TABLE [dbo].[Versions](
	[VersionID] [int] IDENTITY(1,1) NOT NULL,
	[RevisionID] [int] NOT NULL,
	[StageID] [int] NOT NULL,
	[PDF] [varbinary](max) NULL,
	[SummaryPDF] [varbinary](max) NULL,
	[DTS] [datetime] NOT NULL,
	[UserID] [nvarchar](200) NOT NULL,
	[LastChanged] [timestamp] NOT NULL,
 CONSTRAINT [PK_Version] PRIMARY KEY CLUSTERED 
(
	[VersionID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{datetime}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Versions', @level2type=N'COLUMN',@level2name=N'DTS'
GO

ALTER TABLE [dbo].[Versions]  WITH CHECK ADD  CONSTRAINT [FK_Versions_Revisions] FOREIGN KEY([RevisionID])
REFERENCES [dbo].[Revisions] ([RevisionID])
GO
ALTER TABLE [dbo].[Versions] CHECK CONSTRAINT [FK_Versions_Revisions]
GO
ALTER TABLE [dbo].[Versions]  WITH CHECK ADD  CONSTRAINT [FK_Versions_Stages] FOREIGN KEY([StageID])
REFERENCES [dbo].[Stages] ([StageID])
GO
ALTER TABLE [dbo].[Versions] CHECK CONSTRAINT [FK_Versions_Stages]

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


--put in stage data
INSERT INTO [dbo].[Stages] ([Name],[Description],[IsApproved],[DTS],[UserID]) VALUES ('Input to review','',0,getdate(),'VOLIAN\BODINE')
GO
INSERT INTO [dbo].[Stages] ([Name],[Description],[IsApproved],[DTS],[UserID]) VALUES ('Initial review','',0,getdate(),'VOLIAN\BODINE')
GO
INSERT INTO [dbo].[Stages] ([Name],[Description],[IsApproved],[DTS],[UserID]) VALUES ('Final review','',0,getdate(),'VOLIAN\BODINE')
GO
INSERT INTO [dbo].[Stages] ([Name],[Description],[IsApproved],[DTS],[UserID]) VALUES ('Verification','',0,getdate(),'VOLIAN\BODINE')
GO
INSERT INTO [dbo].[Stages] ([Name],[Description],[IsApproved],[DTS],[UserID]) VALUES ('Validation','',0,getdate(),'VOLIAN\BODINE')
GO
INSERT INTO [dbo].[Stages] ([Name],[Description],[IsApproved],[DTS],[UserID]) VALUES ('Issued','',1,getdate(),'VOLIAN\BODINE')
GO
INSERT INTO [dbo].[Stages] ([Name],[Description],[IsApproved],[DTS],[UserID]) VALUES ('Published','',1,getdate(),'VOLIAN\BODINE')
GO
INSERT INTO [dbo].[Stages] ([Name],[Description],[IsApproved],[DTS],[UserID]) VALUES ('Approved','',1,getdate(),'VOLIAN\BODINE')
GO
--end put in stage data

/****** 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
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
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
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
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
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
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
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
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
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
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
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
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
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
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
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
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
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
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
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
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
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
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
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
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
*/
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE procedure [dbo].[getAnnotationAuditsChronologyByItemID]
(
	@ProcItemID int,
	@ItemID int,
	@DTS datetime
)
WITH EXECUTE AS OWNER
AS
begin
--	declare @dts datetime
--	set @dts = (select dts from items where itemid = @procitemid)
	declare @tci table
	(
	ItemID int,
	IContentID int,
	IDeleteStatus int,
	ProcDTS datetime,
	Path nvarchar(max),
	OrdinalPath nvarchar(max)
	)
	insert into @tci
	select * from vefn_tblchilditems(@ProcItemID,@ItemID,0)
	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
,tci.ordinalpath
	from tblannotations aa
	inner join items ii on aa.itemid = ii.itemid
join @tci tci on tci.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
,tci.ordinalpath
	from annotationaudits aa
	inner join items ii on aa.itemid = ii.itemid
join @tci tci on tci.itemid = ii.itemid
	) ah
	where itemid in (select itemid from @tci)
	and dts > @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 ordinalpath,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
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
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
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
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
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
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
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
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
*/

/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
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
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getContentAuditsByContentIDandDTS Succeeded'
ELSE PRINT 'Procedure Creation: getContentAuditsByContentIDandDTS Error on Creation'
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[vefn_GetMyChronology]') AND OBJECTPROPERTY(id,N'IsTableFunction') = 1)
    DROP FUNCTION [vefn_GetMyChronology];
GO
/****** Object:  UserDefinedFunction [dbo].[vefn_GetMyChronology]    Script Date: 11/07/2012 18:09:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE FUNCTION [dbo].[vefn_GetMyChronology](@ProcItemID int, @ItemID int, @IncludeDeletedChildren int, @DTS datetime)
RETURNS @Chrono TABLE
(
[AuditID] bigint,
[ContentID] int,
[Number] nvarchar(max),
[Text] nvarchar(max),
[Type] int,
[FormatID] int,
[Config] nvarchar(max),
[DTS] datetime,
[UserID] nvarchar(max),
[DeleteStatus] int,
[ActionDTS] datetime,
[ActionWhat] nvarchar(max),
[ActionWhen] datetime,
[Path] nvarchar(max),
ItemID int,
TypeName nvarchar(max),
ordinalpath nvarchar(max)
)
WITH EXECUTE AS OWNER
AS
BEGIN
insert into @Chrono
select distinct [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 (@ProcItemID,@ItemID,@IncludeDeletedChildren) t
	inner join vefn_chronologyreport(@ProcItemID,@DTS) r
	on t.icontentid = r.contentid
--	where ActionDTS > procdts or dts > procdts
	) ah
	order by OrdinalPath, contentid,auditid--actionwhen
	RETURN
END
go
IF (@@Error = 0) PRINT 'TableFunction [vefn_GetMyChronology] Succeeded'
ELSE PRINT 'TableFunction [vefn_GetMyChronology] 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
*/	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getContentAuditsChronologyByItemID]
(
	@ProcedureItemID int,
	@SelectedItemID int,
	@IncludeDeletedChildren int,
	@DTS datetime
)

WITH EXECUTE AS OWNER
AS
begin
	select * from vefn_GetMyChronology(@ProcedureItemID,@SelectedItemID,@IncludeDeletedChildren,@DTS)
--	select distinct [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
--	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
*/	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getContentAuditsSummaryByItemID]
(
	@ProcedureItemID int,
	@SelectedItemID int,
	@IncludeDeletedChildren int,
	@DTS datetime
)

WITH EXECUTE AS OWNER
AS
begin
DECLARE @Chrono TABLE
(
[AuditID] bigint,
[ContentID] int,
[Number] nvarchar(max),
[Text] nvarchar(max),
[Type] int,
[FormatID] int,
[Config] nvarchar(max),
[DTS] datetime,
[UserID] nvarchar(max),
[DeleteStatus] int,
[ActionDTS] datetime,
[ActionWhat] nvarchar(max),
[ActionWhen] datetime,
[Path] nvarchar(max),
ItemID int,
TypeName nvarchar(max),
ordinalpath nvarchar(max)
)
insert into @Chrono
select * from vefn_GetMyChronology(@ProcedureItemID,@SelectedItemID,@IncludeDeletedChildren,@DTS)
select * from @Chrono
where AuditID is null OR AuditID in (select min(auditID) from @Chrono group by contentid UNION select max(auditID) from @Chrono group by contentid)
order by OrdinalPath, contentid,auditid
--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
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
*/	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
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
*/
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
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

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)
*/	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
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
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
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
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
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
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
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
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
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
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
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 [getRoUsagesForProc] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getDROUsagesForProc]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getDROUsagesForProc];
GO
	
--[dbo].[getDROUsagesForProc] 2
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getDROUsagesForProc]
(
	@ItemID int
)

WITH EXECUTE AS OWNER
AS
	SELECT
		[DROUsageID],
		rr.[DocID],
		[ROID],
		[Config],
		rr.[DTS],
		rr.[UserID],
		rr.[LastChanged],
		[RODbID]
	FROM [DROUsages] rr
	INNER JOIN Entries ee on rr.docid = ee.docid
	INNER JOIN vefn_ChildItems(@ItemID) cc on cc.contentid = ee.contentid
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getDROUsagesForProc Succeeded'
ELSE PRINT 'Procedure Creation: getDROUsagesForProc 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
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
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
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
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
*/
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
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

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
*/
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
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

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
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
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
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
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
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
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
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
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
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
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
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
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
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
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
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
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)
*/
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE function [dbo].[vefn_CheckAllXML](@DocVersionID int)
returns xml
begin
--temp table for max checkid
	declare @tChecks table (CheckID int)
	insert into @tChecks
	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
--temp table for sibling itemid
	declare @tItems table (ItemID int)
	insert into @tItems
	select itemid from vefn_siblingitems((select itemid from docversions where versionid = @docversionid),0)
--temp table for multi valued roids
	declare @mvROIDS table (ROID varchar(max))
	insert into @mvROIDS
	select roid from (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)) t1 group by roid having count(*) > 1
--temp table for multi valued transitionids
	declare @mvTransitions table (TransitionID int)
	insert into @mvTransitions
	select transitionid from (select distinct r1.value('@TransitionID','int') transitionid,r1.value('@TransitionValue','varchar(max)') transitionvalue
	from checks cc cross apply cc.consistencychecks.nodes('//TransitionCheck') t1(r1)) t1 group by transitionid having count(*) > 1
--temp table for multi valued docids
	declare @mvLibDocs table (DocID int)
	insert into @mvLibDocs
	select docid from (select distinct r1.value('@DocID','int') docid,r1.value('@DocDate','datetime') docdate
	from checks cc cross apply cc.consistencychecks.nodes('//LibDocCheck') t1(r1)) t1 group by docid having count(*) > 1
--temp table for roids
	declare @tROIDS table (ItemID int,ROID varchar(max),ROValue varchar(max),CheckID int)
	insert into @tROIDS
	select rr.itemid,r1.value('@ROID','varchar(max)') roid,r1.value('@ROValue','varchar(max)') rovalue, cc.checkid
	from checks cc 	inner join revisions rr on cc.revisionid = rr.revisionid
	cross apply cc.consistencychecks.nodes('//ROCheck') t1(r1)
--temp table for transitions
	declare @tTransitions table (ItemID int,TransitionID int,TransitionValue varchar(max),CheckID int)
	insert into @tTransitions
	select rr.itemid,r1.value('@TransitionID','int') transitionid,r1.value('@TransitionValue','varchar(max)') transitionvalue,cc.checkid
	from checks cc inner join revisions rr on cc.revisionid = rr.revisionid
	cross apply cc.consistencychecks.nodes('//TransitionCheck') t1(r1)
--temp table for libdocs
	declare @tLibDocs table (ItemID int, DocID int, DocDate datetime,CheckID int)
	insert into @tLibDocs
	select rr.itemid,r1.value('@DocID','int') docid,r1.value('@DocDate','datetime') docdate,cc.checkid
	from checks cc inner join revisions rr on cc.revisionid = rr.revisionid
	cross apply cc.consistencychecks.nodes('//LibDocCheck') t1(r1)

--get all inconsitencies accross set
	declare @ChkXML xml
	set @ChkXML =
	(
		select
		(--ro inconsistencies accross set
			select ROCheck.ItemID,ROCheck.ROID,ROCheck.ROValue from @tROIDS ROCheck
			inner join @tChecks tc on ROCheck.checkid = tc.checkid
			inner join @tItems ti on ROCheck.itemid = ti.itemid
			inner join @mvROIDS mv on ROCheck.roid = mv.roid
			order by ROCheck.itemid
			for xml auto,root('ROChecks'),type
		),
		(--transition inconsistencies accross set
			select TransitionCheck.ItemID,TransitionCheck.TransitionID,TransitionCheck.TransitionValue from @tTransitions TransitionCheck
			inner join @tChecks tc on TransitionCheck.checkid = tc.checkid
			inner join @tItems ti on TransitionCheck.itemid = ti.itemid
			inner join @mvTransitions mv on TransitionCheck.TransitionID = mv.TransitionID
			order by TransitionCheck.itemid
			for xml auto,root('TransitionChecks'),type
		),
		(--libdoc inconsistencies accross set
			select LibDocCheck.ItemID,LibDocCheck.DocID,LibDocCheck.DocDate from @tLibDocs LibDocCheck
			inner join @tChecks tc on LibDocCheck.checkid = tc.checkid
			inner join @tItems ti on LibDocCheck.itemid = ti.itemid
			inner join @mvLibDocs mv on LibDocCheck.docid = mv.docid
			order by LibDocCheck.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)
*/
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
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
*/
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
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

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[vefn_ChronologyReport]') AND OBJECTPROPERTY(id,N'IsTableFunction') = 1)
	DROP FUNCTION [vefn_ChronologyReport];
/****** Object:  UserDefinedFunction [dbo].[vefn_ChronologyReport]    Script Date: 03/20/2012 17:50:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
	
/*
select * from vefn_chronologyreport(2) where type > 0 order by contentid,auditid
*/
/****** Object:  UserDefinedFunction [dbo].[vefn_ChronologyReport]    Script Date: 05/18/2011 11:20:48 ******/
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE function [dbo].[vefn_ChronologyReport](@ProcItemID int, @DTS datetime)
returns @Report table
(
src int,
AuditID bigint,
ContentID int,
Number nvarchar(512),
Text nvarchar(max),
Type int,
FormatID int,
Config nvarchar(max),
DTS datetime,
UserID nvarchar(200),
DeleteStatus int,
ActionDTS datetime,
ItemDTS datetime,
LastAuditID int,
DeletedAuditID int
)
WITH EXECUTE AS OWNER
AS
BEGIN
--added rhm/jcb 20121218i
declare @tmpTable table
(
	icontentid int primary key
)
insert into @tmpTable select icontentid from vefn_tblchilditems(@ProcItemID,@ProcItemID,1)
--added jcb 20111122
--declare @dts datetime
--set @dts = (select dts from items where itemid = @ProcItemID )
--set @dts = (SELECT .[dbo].[vefn_GetPreviousRevisionDTSByItemID] (@ProcItemID))
--end added jcb 20111122
	insert into @Report
	SELECT
1 src,
	[AuditID]
	,ca.[ContentID]
	,[Number]
	,[Text]
	,[Type]
	,[FormatID]
	,[Config]
	,ca.[DTS] cadts
	,ca.[UserID]
	,ca.[DeleteStatus]
	,ActionDTS
	,(select min(dts) from itemaudits where contentid = ca.contentid) ItemDTS
,(select max(auditid) from contentaudits where ca.contentid = contentid and ca.auditid > auditid and number is not null) LastAuditID
,(select max(auditid) from contentaudits where ca.contentid = contentid and ca.auditid > auditid and number is not null and deletestatus > 0 and ca.dts = dts) DeletedAuditID
	FROM ContentAudits ca
--	inner join tblitems ti on ca.contentid = ti.contentid
	where Number is not null
--added jcb 20111028_1827
and ca.contentid != (select contentid from items where itemid = @procitemid)
and ca.contentid in (select icontentid from @tmpTable)
--end added jcb 20111028_1827
--added jcb 20111122
--and ca.ContentID in (SELECT [ContentID] FROM ContentAudits where dts > (select dts from items where itemid = @ProcItemID ))
and ca.ContentID in (SELECT [ContentID] FROM ContentAudits where dts > @dts)
--and ca.dts > (select dts from items where itemid = @ProcItemID )
and ca.dts > @dts
--end added jcb 20111122
--	UNION
--	SELECT
--2 src,
--	cast(ident_current('contentaudits') + 1 as bigint) auditid
----	(select max(auditid) + 1 from contentaudits) [AuditID]
----	0 [AuditID]
--	,ca.[ContentID]
--	,[Number]
--	,[Text]
--	,[Type]
--	,[FormatID]
--	,[Config]
--	,ca.[DTS] cadts
--	,ca.[UserID]
--	,ca.[DeleteStatus]
--	,ActionDTS
--	,(select min(dts) from itemaudits where contentid = ca.contentid) ItemDTS
--,(select max(auditid) from contentaudits where ca.contentid = contentid and number is not null) LastAuditID
--,(select max(auditid) from contentaudits where ca.contentid = contentid and number is not null and deletestatus > 0 and ca.dts = dts) DeletedAuditID
--	 FROM tblContents ca
----	inner join tblitems ti on ca.contentid = ti.contentid
--WHERE ca.DeleteStatus = 0 AND
----added jcb 20111028_1827
--ca.contentid != (select contentid from items where itemid = @procitemid) and
----end added jcb 20111028_1827
----ca.ContentID in (SELECT [ContentID] FROM ContentAudits where dts > (select dts from items where itemid = @ProcItemID ))
----added jcb 20111122
----ca.ContentID in (SELECT [ContentID] FROM Contents where dts > (select dts from items where itemid = @ProcItemID ))
--ca.ContentID in (SELECT [ContentID] FROM Contents where dts > @dts)
----end added jcb 20111122
----order by ca.DTS,ActionDTS
	UNION
	SELECT
3 src,
	cast(ident_current('contentaudits') + 1 as bigint) auditid
--	(select max(auditid) + 1 from contentaudits) [AuditID]
--	0 [AuditID]
	,ca.[ContentID]
	,[Number]
	,[Text]
	,[Type]
	,[FormatID]
	,[Config]
	,ca.[DTS] cadts
	,ca.[UserID]
	,ca.[DeleteStatus]
	,ActionDTS
	,(select min(dts) from itemaudits where contentid = ca.contentid) ItemDTS
,(select max(auditid) from contentaudits where ca.contentid = contentid and number is not null) LastAuditID
,(select max(auditid) from contentaudits where ca.contentid = contentid and number is not null and deletestatus > 0 and ca.dts = dts) DeletedAuditID
	 FROM tblContents ca
--	inner join tblitems ti on ca.contentid = ti.contentid
WHERE ca.DeleteStatus = 0 AND
--added jcb 20111028_1827
ca.contentid != (select contentid from items where itemid = @procitemid)
and ca.contentid in (select icontentid from @tmpTable)
--end added jcb 20111028_1827
--added jcb 20111122
--ca.dts > (select dts from items where itemid = @ProcItemID )
and (ca.dts > @dts or ca.actiondts > @dts)
--end added jcb 20111122
	UNION
	SELECT distinct
5 src,
--	cast(ident_current('contentaudits') + 1 as bigint) auditid
--	(select max(auditid) + 1 from contentaudits) [AuditID]
	ca.[AuditID]
	,ca.[ContentID]
	,[Number]
	,[Text]
	,[Type]
	,[FormatID]
	,[Config]
	,ca.[DTS] cadts
	,ca.[UserID]
	,ca.[DeleteStatus]
	,ActionDTS
	,(select min(dts) from itemaudits where contentid = ca.contentid) ItemDTS
,(select max(auditid) from contentaudits where ca.contentid = contentid and number is not null) LastAuditID
,(select max(auditid) from contentaudits where ca.contentid = contentid and number is not null and deletestatus > 0 and ca.dts = dts) DeletedAuditID
	FROM ContentAudits ca
--	inner join tblitems ti on ca.contentid = ti.contentid
WHERE ca.DeleteStatus > 0
--added jcb 20111028_1827
		--ca.contentid != (select contentid from items where itemid = @procitemid) and
--end added jcb 20111028_1827
--added jcb 20111122
--ca.dts > (select dts from items where itemid = @ProcItemID )
and ca.contentid in (select icontentid from @tmpTable)
and (ca.dts > @dts or ca.actiondts > @dts)
--end added jcb 20111122
order by ca.DTS,ActionDTS

	insert into @Report
	SELECT
4 src,
	ca.[AuditID]
	,ca.[ContentID]
	,ca.[Number]
	,ca.[Text]
	,ca.[Type]
	,ca.[FormatID]
	,ca.[Config]
	,ca.[DTS] cadts
	,ca.[UserID]
	,ca.[DeleteStatus]
	,ca.ActionDTS
	,(select min(dts) from itemaudits where contentid = ca.contentid) ItemDTS
,(select max(auditid) from contentaudits where ca.contentid = contentid and ca.auditid > auditid and number is not null) LastAuditID
,(select max(auditid) from contentaudits where ca.contentid = contentid and ca.auditid > auditid and number is not null and deletestatus > 0 and ca.dts = dts) DeletedAuditID
	FROM ContentAudits ca
--	inner join tblitems ti on ca.contentid = ti.contentid
	inner join @Report rpt on ca.contentid = rpt.contentid and ca.auditid = rpt.lastauditid
where ca.auditid not in (select auditid from @report)
and rpt.lastauditid is not null
and ca.contentid in (select icontentid from @tmpTable)
	return
end
GO
-- Display the status of TableFunction creation
IF (@@Error = 0) PRINT 'Function: vefn_ChronologyReport Succeeded'
ELSE PRINT 'Function: vefn_ChronologyReport Error on Creation'
GO

/****** Object:  StoredProcedure [vefn_FindExternalFromTransitions] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[vefn_FindExternalFromTransitions]') AND OBJECTPROPERTY(id,N'IsTableFunction') = 1)
    DROP FUNCTION [vefn_FindExternalFromTransitions];
GO
	
/*
Select * from vefn_FindExternalFromTransitions(185)
*/

/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
create  FUNCTION [dbo].[vefn_FindExternalFromTransitions](@ItemID int)
RETURNS @Children TABLE
(
  [FromItemID] int,
	[TransitionID] [int] NOT NULL,
	[FromID] [int] NOT NULL,
	[ToID] [int] NOT NULL,
	[RangeID] [int] NOT NULL,
	[Config] [nvarchar](max) NULL
)
WITH EXECUTE AS OWNER
AS
BEGIN
with Itemz([Level], [Ordinal], [ItemID], [PreviousID], [FromType], [ContentID], [DTS], [UserID], [LastChanged],[pContentID], [pDTS],[pUserID],[pLastChanged]) as (
  Select 0 [Level], 0 [Ordinal], [ItemID], [PreviousID],0 [FromType],[ContentID],[DTS],[UserID],[LastChanged]
	,0 [pContentID],[DTS] [pDTS], [UserID] [pUserID], [LastChanged] [pLastChanged]
	FROM [Items]
    where [ItemID]=@ItemID
Union All
-- Children
  select [Level] + 1,0, I.[ItemID], I.[PreviousID], P.[FromType],I.[ContentID],I.[DTS],I.[UserID],I.[LastChanged]
	,P.[ContentID] [pContentID],P.[DTS] [pDTS],P.[UserID] [pUserID],P.[LastChanged] [pLastChanged]
	from Itemz Z
	join Parts P on P.ContentID = Z.ContentID
	join Items I on I.ItemID = P.ItemID
-- Siblings
Union All
  select [Level] ,Z.[Ordinal] +1, I.[ItemID], I.[PreviousID], [FromType],I.[ContentID],I.[DTS],I.[UserID],I.[LastChanged]
	,null,null,null,null
	from Itemz Z
	join Items I on I.PreviousID = Z.ItemID
	where Z.[Level] > 0
)
insert into @Children
select ItemID [FromItemID], TT.[TransitionID],	TT.[FromID],	TT.[ToID],	TT.[RangeID],	TT.[Config]
 from Transitions TT
 join Items II on II.ContentID=TT.FromID
where (ToID not in(select ItemID from Itemz) OR RangeID not in(select ItemID from Itemz))
AND FromID in(Select ContentID from ItemZ)
OPTION (MAXRECURSION 10000)
RETURN
END
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'TableFunction Creation: vefn_FindExternalFromTransitions Succeeded'
ELSE PRINT 'TableFunction Creation: vefn_FindExternalFromTransitions Error on Creation'
GO

/****** Object:  StoredProcedure [vefn_GetFormatIDByItemID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[vefn_GetFormatIDByItemID]') AND OBJECTPROPERTY(id,N'IsScalarFunction') = 1)
    DROP FUNCTION [vefn_GetFormatIDByItemID];
GO
	
/*
select dbo.vefn_GetFormatIDByItemID(41)
*/
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE function [dbo].[vefn_GetFormatIDByItemID]
(@ItemID int)
returns int
as begin
	declare @FormatID int;
	with itemz
	(
	itemid,previousid,formatid,folderid,parentid
	) as
	(
	select itemid,previousid,formatid,null,null
	from items ii
	join contents cc on ii.contentid = cc.contentid
	where itemid = @ItemID
	--siblins
	union all
	select ii.itemid,ii.previousid,cc.formatid,null,null
	from items ii
	join contents cc on ii.contentid = cc.contentid
	join itemz zz on zz.previousid = ii.itemid
	where zz.formatid is null
	--chillins
	union all
	select ii.itemid,ii.previousid,cc.formatid,null,null
	from parts pp
	join items ii on ii.contentid = pp.contentid
	join contents cc on ii.contentid = cc.contentid
	join itemz zz on zz.itemid = pp.itemid
	where zz.formatid is null
	--docversions
	union all
	select 0,0,dv.formatid,0,dv.folderid
	from docversions dv
	join itemz zz on dv.itemid = zz.itemid
	where zz.formatid is null
	--folders
	union all
	select 0,0,ff.formatid,ff.folderid,ff.parentid
	from folders ff
	join itemz zz on ff.folderid = zz.parentid
	where zz.folderid != zz.parentid
	and zz.formatid is null
	)
	select @FormatID = formatid from itemz zz
	where formatid is not null
	OPTION (MAXRECURSION 10000)
	return @FormatID
end
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'ScalarFunction Creation: vefn_GetFormatIDByItemID Succeeded'
ELSE PRINT 'ScalarFunction Creation: vefn_GetFormatIDByItemID Error on Creation'
GO

/****** Object:  StoredProcedure [vefn_GetPreviousRevisionDTSByItemID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[vefn_GetPreviousRevisionDTSByItemID]') AND OBJECTPROPERTY(id,N'IsScalarFunction') = 1)
    DROP FUNCTION [vefn_GetPreviousRevisionDTSByItemID];
GO
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE function [dbo].[vefn_GetPreviousRevisionDTSByItemID](@ItemID int)
returns datetime
WITH EXECUTE AS OWNER
AS
begin
	declare @dts datetime
	SELECT
		@dts = [RevisionDate]
	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 @dts
end
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'ScalarFunction Creation: vefn_GetPreviousRevisionDTSByItemID Succeeded'
ELSE PRINT 'ScalarFunction Creation: vefn_GetPreviousRevisionDTSByItemID Error on Creation'
GO

/****** Object:  StoredProcedure [vefn_GetStepTypeNameByItemIDandTypeID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[vefn_GetStepTypeNameByItemIDandTypeID]') AND OBJECTPROPERTY(id,N'IsScalarFunction') = 1)
    DROP FUNCTION [vefn_GetStepTypeNameByItemIDandTypeID];
GO
	
/*
select dbo.vefn_GetStepTypeNameByItemIDandTypeID(9830,20005)
*/
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE function [dbo].[vefn_GetStepTypeNameByItemIDandTypeID](@ItemID int, @TypeID int)
returns varchar(max)
as begin
	declare @typename varchar(max)
	select @typename = r1.value('@Type','varchar(max)')
	from items ii
	inner join formats ff on 1=1
	cross apply ff.data.nodes('//Step') t1(r1)
	where ii.itemid = @ItemID
	and ff.formatid = (select dbo.vefn_GetFormatIDByItemID(@ItemID))
	and r1.value('@Index','int') = @TypeID - 20000
	return @typename
end
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'ScalarFunction Creation: vefn_GetStepTypeNameByItemIDandTypeID Succeeded'
ELSE PRINT 'ScalarFunction Creation: vefn_GetStepTypeNameByItemIDandTypeID 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

*/
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
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
*/
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
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

/****** 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
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[purgeData]

WITH EXECUTE AS OWNER
AS
BEGIN TRY -- Try Block
	BEGIN TRANSACTION
	delete from [AnnotationAudits]
	dbcc checkident([AnnotationAudits],reseed,0)
	delete from [Applicabilities]
	dbcc checkident([Applicabilities],reseed,0)
	delete from [ApplicableStructures]
	delete from [Assignments]
	dbcc checkident([Assignments],reseed,0)
	delete from [Associations]
	dbcc checkident([Associations],reseed,0)
	delete from [Checks]
	dbcc checkident([Checks],reseed,0)
	delete from [ContentAudits]
	dbcc checkident([ContentAudits],reseed,0)
	delete from [DeleteLog]
	dbcc checkident([DeleteLog],reseed,0)
	delete from [Details]
	dbcc checkident([Details],reseed,0)
	delete from [DocumentAudits]
	dbcc checkident([DocumentAudits],reseed,0)
	delete from [DocVersions]
	dbcc checkident([DocVersions],reseed,0)
	delete from [DROUsages]
	dbcc checkident([DROUsages],reseed,0)
	delete from [EntryAudits]
	dbcc checkident([EntryAudits],reseed,0)
	delete from [Figures]
	dbcc checkident([Figures],reseed,0)
	delete from [Folders]
	dbcc checkident([Folders],reseed,0)
	delete from [GridAudits]
	dbcc checkident([GridAudits],reseed,0)
	delete from [ImageAudits]
	dbcc checkident([ImageAudits],reseed,0)
	delete from [ItemAudits]
	dbcc checkident([ItemAudits],reseed,0)
	delete from [Memberships]
	dbcc checkident([Memberships],reseed,0)
	delete from [PartAudits]
	dbcc checkident([PartAudits],reseed,0)
	delete from [Pdfs]
	delete from [Permissions]
	dbcc checkident([Permissions],reseed,0)
	delete from [ROFsts]
	dbcc checkident([ROFsts],reseed,0)
	delete from [ROImages]
	dbcc checkident([ROImages],reseed,0)
	delete from [Roles]
	dbcc checkident([Roles],reseed,0)
	delete from [ROUsageAudits]
	dbcc checkident([ROUsageAudits],reseed,0)
	delete from [tblAnnotations]
	dbcc checkident([tblAnnotations],reseed,0)
	delete from [tblEntries]
	delete from [tblGrids]
	delete from [tblImages]
	delete from [tblParts]
	delete from [tblROUsages]
	dbcc checkident([tblROUsages],reseed,0)
	delete from [TransitionAudits]
	dbcc checkident([TransitionAudits],reseed,0)
	delete from [Users]
	dbcc checkident([Users],reseed,0)
	delete from [Versions]
	dbcc checkident([Versions],reseed,0)
	delete from [ZContents]
	delete from [ZTransitions]
	delete from [AnnotationTypes]
	dbcc checkident([AnnotationTypes],reseed,0)
	delete from [Connections]
	dbcc checkident([Connections],reseed,0)
	delete from [Groups]
	dbcc checkident([Groups],reseed,0)
	delete from [Revisions]
	dbcc checkident([Revisions],reseed,0)
	delete from [RODbs]
	dbcc checkident([RODbs],reseed,0)
	delete from [Stages]
	dbcc checkident([Stages],reseed,0)
	delete from [tblDocuments]
	dbcc checkident([tblDocuments],reseed,0)
	delete from [tblTransitions]
	dbcc checkident([tblTransitions],reseed,0)
	delete from [tblItems]
	dbcc checkident([tblItems],reseed,0)
	delete from [tblContents]
	dbcc checkident([tblContents],reseed,0)
	delete from [Formats]
	dbcc checkident([Formats],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