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

AS
	delete from [Annotations]
	dbcc checkident([Annotations],reseed,0)
	delete from [AnnotationTypes]
	dbcc checkident([AnnotationTypes],reseed,0)
	delete from [Assignments]
	dbcc checkident([Assignments],reseed,0)
	delete from [Associations]
	dbcc checkident([Associations],reseed,0)
	delete from [Details]
	dbcc checkident([Details],reseed,0)
	delete from [DocVersions]
	dbcc checkident([DocVersions],reseed,0)
	delete from [Entries]
	delete from [Figures]
	dbcc checkident([Figures],reseed,0)
	delete from [Folders]
	dbcc checkident([Folders],reseed,0)
	delete from [Memberships]
	dbcc checkident([Memberships],reseed,0)
	delete from [Parts]
	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 [RoUsages]
	dbcc checkident([RoUsages],reseed,0)
	delete from [Users]
	dbcc checkident([Users],reseed,0)
	delete from [ZContents]
	delete from [ZTransitions]
	delete from [Connections]
	dbcc checkident([Connections],reseed,0)
	delete from [Documents]
	dbcc checkident([Documents],reseed,0)
	delete from [Groups]
	dbcc checkident([Groups],reseed,0)
	delete from [RODbs]
	dbcc checkident([RODbs],reseed,0)
	delete from [Transitions]
	dbcc checkident([Transitions],reseed,0)
	delete from [Items]
	dbcc checkident([Items],reseed,0)
	delete from [Contents]
	dbcc checkident([Contents],reseed,0)
	delete from [Formats]
	dbcc checkident([Formats],reseed,0)
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: purgeData Succeeded'
ELSE PRINT 'Procedure Creation: purgeData Error on Creation'
GO

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

AS
	SELECT
		[AnnotationID],
		[ItemID],
		[TypeID],
		[RtfText],
		[SearchText],
		[Config],
		[DTS],
		[UserID],
		[LastChanged]
	FROM [Annotations]
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getAnnotations Succeeded'
ELSE PRINT 'Procedure Creation: getAnnotations Error on Creation'
GO

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

(
	@AnnotationID int,
	@ItemID int,
	@TypeID int,
	@RtfText nvarchar(MAX)=null,
	@SearchText nvarchar(MAX)=null,
	@Config nvarchar(MAX)=null,
	@DTS datetime,
	@UserID nvarchar(100),
	@LastChanged timestamp,
	@newLastChanged timestamp output
)
AS
	UPDATE [Annotations]
		SET
			[ItemID]=@ItemID,
			[TypeID]=@TypeID,
			[RtfText]=@RtfText,
			[SearchText]=@SearchText,
			[Config]=@Config,
			[DTS]=@DTS,
			[UserID]=@UserID
		WHERE [AnnotationID]=@AnnotationID AND [LastChanged]=@LastChanged
	IF @@ROWCOUNT = 0
	BEGIN
		IF NOT exists(select * from [Annotations] WHERE [AnnotationID]=@AnnotationID)
			RAISERROR('Annotation record has been deleted by another user', 16, 1)
		ELSE           	
			RAISERROR('Annotation has been edited by another user', 16, 1)            	
	END

		SELECT 		@newLastChanged=[LastChanged]	
		FROM [Annotations] WHERE [AnnotationID]=@AnnotationID		

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

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

(
	@ItemID int,
	@TypeID int,
	@RtfText nvarchar(MAX)=null,
	@SearchText nvarchar(MAX)=null,
	@Config nvarchar(MAX)=null,
	@DTS datetime,
	@UserID nvarchar(100),
	@newAnnotationID int output,
	@newLastChanged timestamp output
)
AS
	INSERT INTO [Annotations]
	(
		[ItemID],
		[TypeID],
		[RtfText],
		[SearchText],
		[Config],
		[DTS],
		[UserID]
	)
	VALUES
	(
		@ItemID,
		@TypeID,
		@RtfText,
		@SearchText,
		@Config,
		@DTS,
		@UserID
	)
	SELECT @newAnnotationID= SCOPE_IDENTITY()
	SELECT 		@newLastChanged=[LastChanged]
		FROM [Annotations] WHERE [AnnotationID]=@newAnnotationID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addAnnotation Succeeded'
ELSE PRINT 'Procedure Creation: addAnnotation Error on Creation'
GO

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

(
	@AnnotationID int
)
AS
	DELETE [Annotations]
		WHERE [AnnotationID] = @AnnotationID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteAnnotation Succeeded'
ELSE PRINT 'Procedure Creation: deleteAnnotation Error on Creation'
GO

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

(
	@AnnotationID int
)
AS
	SELECT
		[AnnotationID],
		[ItemID],
		[TypeID],
		[RtfText],
		[SearchText],
		[Config],
		[DTS],
		[UserID],
		[LastChanged]
	FROM [Annotations]
	WHERE [AnnotationID]=@AnnotationID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getAnnotation Succeeded'
ELSE PRINT 'Procedure Creation: getAnnotation Error on Creation'
GO

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

(
	@AnnotationID int
)
	AS
	SELECT COUNT(*)
	FROM [Annotations] WHERE [AnnotationID]=@AnnotationID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsAnnotation Succeeded'
ELSE PRINT 'Procedure Creation: existsAnnotation Error on Creation'
GO

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

(
	@TypeID int
)
AS

	SELECT
		[Annotations].[AnnotationID],
		[Annotations].[ItemID],
		[Annotations].[TypeID],
		[Annotations].[RtfText],
		[Annotations].[SearchText],
		[Annotations].[Config],
		[Annotations].[DTS],
		[Annotations].[UserID],
		[Annotations].[LastChanged],
		[Items].[PreviousID] [Item_PreviousID],
		[Items].[ContentID] [Item_ContentID],
		[Items].[DTS] [Item_DTS],
		[Items].[UserID] [Item_UserID]
	FROM [Annotations]
		JOIN [Items]  ON
			[Items].[ItemID]=[Annotations].[ItemID]
	WHERE
		[Annotations].[TypeID]=@TypeID

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

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

(
	@ItemID int
)
AS

	SELECT
		[Annotations].[AnnotationID],
		[Annotations].[ItemID],
		[Annotations].[TypeID],
		[Annotations].[RtfText],
		[Annotations].[SearchText],
		[Annotations].[Config],
		[Annotations].[DTS],
		[Annotations].[UserID],
		[Annotations].[LastChanged],
		[AnnotationTypes].[Name] [AnnotationType_Name],
		[AnnotationTypes].[Config] [AnnotationType_Config],
		[AnnotationTypes].[DTS] [AnnotationType_DTS],
		[AnnotationTypes].[UserID] [AnnotationType_UserID]
	FROM [Annotations]
		JOIN [AnnotationTypes]  ON
			[AnnotationTypes].[TypeID]=[Annotations].[TypeID]
	WHERE
		[Annotations].[ItemID]=@ItemID

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

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

AS
	SELECT
		[TypeID],
		[Name],
		[Config],
		[DTS],
		[UserID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Annotations] WHERE [Annotations].[TypeID]=[AnnotationTypes].[TypeID]) [AnnotationCount]
	FROM [AnnotationTypes]
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getAnnotationTypes Succeeded'
ELSE PRINT 'Procedure Creation: getAnnotationTypes Error on Creation'
GO

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

(
	@TypeID int,
	@Name nvarchar(100),
	@Config nvarchar(MAX)=null,
	@DTS datetime,
	@UserID nvarchar(100),
	@LastChanged timestamp,
	@newLastChanged timestamp output
)
AS
	UPDATE [AnnotationTypes]
		SET
			[Name]=@Name,
			[Config]=@Config,
			[DTS]=@DTS,
			[UserID]=@UserID
		WHERE [TypeID]=@TypeID AND [LastChanged]=@LastChanged
	IF @@ROWCOUNT = 0
	BEGIN
		IF NOT exists(select * from [AnnotationTypes] WHERE [TypeID]=@TypeID)
			RAISERROR('AnnotationType record has been deleted by another user', 16, 1)
		ELSE           	
			RAISERROR('AnnotationType has been edited by another user', 16, 1)            	
	END

		SELECT 		@newLastChanged=[LastChanged]	
		FROM [AnnotationTypes] WHERE [TypeID]=@TypeID		

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

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

(
	@Name nvarchar(100),
	@Config nvarchar(MAX)=null,
	@DTS datetime,
	@UserID nvarchar(100),
	@newTypeID int output,
	@newLastChanged timestamp output
)
AS
	INSERT INTO [AnnotationTypes]
	(
		[Name],
		[Config],
		[DTS],
		[UserID]
	)
	VALUES
	(
		@Name,
		@Config,
		@DTS,
		@UserID
	)
	SELECT @newTypeID= SCOPE_IDENTITY()
	SELECT 		@newLastChanged=[LastChanged]
		FROM [AnnotationTypes] WHERE [TypeID]=@newTypeID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addAnnotationType Succeeded'
ELSE PRINT 'Procedure Creation: addAnnotationType Error on Creation'
GO

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

(
	@TypeID int
)
AS
	DELETE [Annotations]
		WHERE [TypeID]=@TypeID
	DELETE [AnnotationTypes]
		WHERE [TypeID] = @TypeID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteAnnotationType Succeeded'
ELSE PRINT 'Procedure Creation: deleteAnnotationType Error on Creation'
GO

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

(
	@TypeID int
)
AS
	SELECT
		[TypeID],
		[Name],
		[Config],
		[DTS],
		[UserID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Annotations] WHERE [Annotations].[TypeID]=[AnnotationTypes].[TypeID]) [AnnotationCount]
	FROM [AnnotationTypes]
	WHERE [TypeID]=@TypeID

	SELECT
		[Annotations].[AnnotationID],
		[Annotations].[ItemID],
		[Annotations].[TypeID],
		[Annotations].[RtfText],
		[Annotations].[SearchText],
		[Annotations].[Config],
		[Annotations].[DTS],
		[Annotations].[UserID],
		[Annotations].[LastChanged],
		[Items].[PreviousID] [Item_PreviousID],
		[Items].[ContentID] [Item_ContentID],
		[Items].[DTS] [Item_DTS],
		[Items].[UserID] [Item_UserID]
	FROM [Annotations]
		JOIN [Items]  ON
			[Items].[ItemID]=[Annotations].[ItemID]
	WHERE
		[Annotations].[TypeID]=@TypeID

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

/****** Object:  StoredProcedure [getAnnotationTypeByName] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getAnnotationTypeByName]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getAnnotationTypeByName];
GO
	
CREATE PROCEDURE [dbo].[getAnnotationTypeByName]
(
	@Name nvarchar(100)
)
AS
	SELECT
		[TypeID],
		[Name],
		[Config],
		[DTS],
		[UserID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Annotations] WHERE [Annotations].[TypeID]=[AnnotationTypes].[TypeID]) [AnnotationCount]
	FROM [AnnotationTypes]
	WHERE [Name]=@Name
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getAnnotationTypeByName Succeeded'
ELSE PRINT 'Procedure Creation: getAnnotationTypeByName Error on Creation'
GO

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

(
	@TypeID int
)
	AS
	SELECT COUNT(*)
	FROM [AnnotationTypes] WHERE [TypeID]=@TypeID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsAnnotationType Succeeded'
ELSE PRINT 'Procedure Creation: existsAnnotationType Error on Creation'
GO

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

AS
	SELECT
		[AID],
		[GID],
		[RID],
		[FolderID],
		[StartDate],
		[EndDate],
		[DTS],
		[UsrID],
		[LastChanged]
	FROM [Assignments]
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getAssignments Succeeded'
ELSE PRINT 'Procedure Creation: getAssignments Error on Creation'
GO

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

(
	@AID int,
	@GID int,
	@RID int,
	@FolderID int,
	@StartDate datetime,
	@EndDate datetime=null,
	@DTS datetime,
	@UsrID nvarchar(100),
	@LastChanged timestamp,
	@newLastChanged timestamp output
)
AS
	UPDATE [Assignments]
		SET
			[GID]=@GID,
			[RID]=@RID,
			[FolderID]=@FolderID,
			[StartDate]=@StartDate,
			[EndDate]=@EndDate,
			[DTS]=@DTS,
			[UsrID]=@UsrID
		WHERE [AID]=@AID AND [LastChanged]=@LastChanged
	IF @@ROWCOUNT = 0
	BEGIN
		IF NOT exists(select * from [Assignments] WHERE [AID]=@AID)
			RAISERROR('Assignment record has been deleted by another user', 16, 1)
		ELSE           	
			RAISERROR('Assignment has been edited by another user', 16, 1)            	
	END

		SELECT 		@newLastChanged=[LastChanged]	
		FROM [Assignments] WHERE [AID]=@AID		

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

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

(
	@GID int,
	@RID int,
	@FolderID int,
	@StartDate datetime,
	@EndDate datetime=null,
	@DTS datetime,
	@UsrID nvarchar(100),
	@newAID int output,
	@newLastChanged timestamp output
)
AS
	INSERT INTO [Assignments]
	(
		[GID],
		[RID],
		[FolderID],
		[StartDate],
		[EndDate],
		[DTS],
		[UsrID]
	)
	VALUES
	(
		@GID,
		@RID,
		@FolderID,
		@StartDate,
		@EndDate,
		@DTS,
		@UsrID
	)
	SELECT @newAID= SCOPE_IDENTITY()
	SELECT 		@newLastChanged=[LastChanged]
		FROM [Assignments] WHERE [AID]=@newAID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addAssignment Succeeded'
ELSE PRINT 'Procedure Creation: addAssignment Error on Creation'
GO

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

(
	@AID int
)
AS
	DELETE [Assignments]
		WHERE [AID] = @AID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteAssignment Succeeded'
ELSE PRINT 'Procedure Creation: deleteAssignment Error on Creation'
GO

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

(
	@AID int
)
AS
	SELECT
		[AID],
		[GID],
		[RID],
		[FolderID],
		[StartDate],
		[EndDate],
		[DTS],
		[UsrID],
		[LastChanged]
	FROM [Assignments]
	WHERE [AID]=@AID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getAssignment Succeeded'
ELSE PRINT 'Procedure Creation: getAssignment Error on Creation'
GO

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

(
	@AID int
)
	AS
	SELECT COUNT(*)
	FROM [Assignments] WHERE [AID]=@AID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsAssignment Succeeded'
ELSE PRINT 'Procedure Creation: existsAssignment Error on Creation'
GO

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

(
	@FolderID int
)
AS

	SELECT
		[Assignments].[AID],
		[Assignments].[GID],
		[Assignments].[RID],
		[Assignments].[FolderID],
		[Assignments].[StartDate],
		[Assignments].[EndDate],
		[Assignments].[DTS],
		[Assignments].[UsrID],
		[Assignments].[LastChanged],
		[Groups].[GroupName] [Group_GroupName],
		[Groups].[GroupType] [Group_GroupType],
		[Groups].[Config] [Group_Config],
		[Groups].[DTS] [Group_DTS],
		[Groups].[UsrID] [Group_UsrID],
		[Roles].[Name] [Role_Name],
		[Roles].[Title] [Role_Title],
		[Roles].[DTS] [Role_DTS],
		[Roles].[UsrID] [Role_UsrID]
	FROM [Assignments]
		JOIN [Groups]  ON
			[Groups].[GID]=[Assignments].[GID]
		JOIN [Roles]  ON
			[Roles].[RID]=[Assignments].[RID]
	WHERE
		[Assignments].[FolderID]=@FolderID

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

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

(
	@GID int
)
AS

	SELECT
		[Assignments].[AID],
		[Assignments].[GID],
		[Assignments].[RID],
		[Assignments].[FolderID],
		[Assignments].[StartDate],
		[Assignments].[EndDate],
		[Assignments].[DTS],
		[Assignments].[UsrID],
		[Assignments].[LastChanged],
		[Folders].[ParentID] [Folder_ParentID],
		[Folders].[DBID] [Folder_DBID],
		[Folders].[Name] [Folder_Name],
		[Folders].[Title] [Folder_Title],
		[Folders].[ShortName] [Folder_ShortName],
		[Folders].[FormatID] [Folder_FormatID],
		[Folders].[ManualOrder] [Folder_ManualOrder],
		[Folders].[Config] [Folder_Config],
		[Folders].[DTS] [Folder_DTS],
		[Folders].[UsrID] [Folder_UsrID],
		[Roles].[Name] [Role_Name],
		[Roles].[Title] [Role_Title],
		[Roles].[DTS] [Role_DTS],
		[Roles].[UsrID] [Role_UsrID]
	FROM [Assignments]
		JOIN [Folders]  ON
			[Folders].[FolderID]=[Assignments].[FolderID]
		JOIN [Roles]  ON
			[Roles].[RID]=[Assignments].[RID]
	WHERE
		[Assignments].[GID]=@GID

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

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

(
	@RID int
)
AS

	SELECT
		[Assignments].[AID],
		[Assignments].[GID],
		[Assignments].[RID],
		[Assignments].[FolderID],
		[Assignments].[StartDate],
		[Assignments].[EndDate],
		[Assignments].[DTS],
		[Assignments].[UsrID],
		[Assignments].[LastChanged],
		[Folders].[ParentID] [Folder_ParentID],
		[Folders].[DBID] [Folder_DBID],
		[Folders].[Name] [Folder_Name],
		[Folders].[Title] [Folder_Title],
		[Folders].[ShortName] [Folder_ShortName],
		[Folders].[FormatID] [Folder_FormatID],
		[Folders].[ManualOrder] [Folder_ManualOrder],
		[Folders].[Config] [Folder_Config],
		[Folders].[DTS] [Folder_DTS],
		[Folders].[UsrID] [Folder_UsrID],
		[Groups].[GroupName] [Group_GroupName],
		[Groups].[GroupType] [Group_GroupType],
		[Groups].[Config] [Group_Config],
		[Groups].[DTS] [Group_DTS],
		[Groups].[UsrID] [Group_UsrID]
	FROM [Assignments]
		JOIN [Folders]  ON
			[Folders].[FolderID]=[Assignments].[FolderID]
		JOIN [Groups]  ON
			[Groups].[GID]=[Assignments].[GID]
	WHERE
		[Assignments].[RID]=@RID

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

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

AS
	SELECT
		[AssociationID],
		[VersionID],
		[ROFstID],
		[Config],
		[DTS],
		[UserID],
		[LastChanged]
	FROM [Associations]
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getAssociations Succeeded'
ELSE PRINT 'Procedure Creation: getAssociations Error on Creation'
GO

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

(
	@AssociationID int,
	@VersionID int,
	@ROFstID int,
	@Config nvarchar(MAX)=null,
	@DTS datetime,
	@UserID nvarchar(100),
	@LastChanged timestamp,
	@newLastChanged timestamp output
)
AS
	UPDATE [Associations]
		SET
			[VersionID]=@VersionID,
			[ROFstID]=@ROFstID,
			[Config]=@Config,
			[DTS]=@DTS,
			[UserID]=@UserID
		WHERE [AssociationID]=@AssociationID AND [LastChanged]=@LastChanged
	IF @@ROWCOUNT = 0
	BEGIN
		IF NOT exists(select * from [Associations] WHERE [AssociationID]=@AssociationID)
			RAISERROR('Association record has been deleted by another user', 16, 1)
		ELSE           	
			RAISERROR('Association has been edited by another user', 16, 1)            	
	END

		SELECT 		@newLastChanged=[LastChanged]	
		FROM [Associations] WHERE [AssociationID]=@AssociationID		

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

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

(
	@VersionID int,
	@ROFstID int,
	@Config nvarchar(MAX)=null,
	@DTS datetime,
	@UserID nvarchar(100),
	@newAssociationID int output,
	@newLastChanged timestamp output
)
AS
	INSERT INTO [Associations]
	(
		[VersionID],
		[ROFstID],
		[Config],
		[DTS],
		[UserID]
	)
	VALUES
	(
		@VersionID,
		@ROFstID,
		@Config,
		@DTS,
		@UserID
	)
	SELECT @newAssociationID= SCOPE_IDENTITY()
	SELECT 		@newLastChanged=[LastChanged]
		FROM [Associations] WHERE [AssociationID]=@newAssociationID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addAssociation Succeeded'
ELSE PRINT 'Procedure Creation: addAssociation Error on Creation'
GO

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

(
	@AssociationID int
)
AS
	DELETE [Associations]
		WHERE [AssociationID] = @AssociationID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteAssociation Succeeded'
ELSE PRINT 'Procedure Creation: deleteAssociation Error on Creation'
GO

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

(
	@AssociationID int
)
AS
	SELECT
		[AssociationID],
		[VersionID],
		[ROFstID],
		[Config],
		[DTS],
		[UserID],
		[LastChanged]
	FROM [Associations]
	WHERE [AssociationID]=@AssociationID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getAssociation Succeeded'
ELSE PRINT 'Procedure Creation: getAssociation Error on Creation'
GO

/****** Object:  StoredProcedure [getAssociationByVersionID_ROFstID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getAssociationByVersionID_ROFstID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getAssociationByVersionID_ROFstID];
GO
	
CREATE PROCEDURE [dbo].[getAssociationByVersionID_ROFstID]
(
	@VersionID int,
	@ROFstID int
)
AS
	SELECT
		[AssociationID],
		[VersionID],
		[ROFstID],
		[Config],
		[DTS],
		[UserID],
		[LastChanged]
	FROM [Associations]
	WHERE [VersionID]=@VersionID AND [ROFstID]=@ROFstID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getAssociationByVersionID_ROFstID Succeeded'
ELSE PRINT 'Procedure Creation: getAssociationByVersionID_ROFstID Error on Creation'
GO

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

(
	@AssociationID int
)
	AS
	SELECT COUNT(*)
	FROM [Associations] WHERE [AssociationID]=@AssociationID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsAssociation Succeeded'
ELSE PRINT 'Procedure Creation: existsAssociation Error on Creation'
GO

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

(
	@VersionID int
)
AS

	SELECT
		[Associations].[AssociationID],
		[Associations].[VersionID],
		[Associations].[ROFstID],
		[Associations].[Config],
		[Associations].[DTS],
		[Associations].[UserID],
		[Associations].[LastChanged],
		[ROFsts].[RODbID] [ROFst_RODbID],
		[ROFsts].[ROFST] [ROFst_ROFST],
		[ROFsts].[Config] [ROFst_Config],
		[ROFsts].[DTS] [ROFst_DTS],
		[ROFsts].[UserID] [ROFst_UserID]
	FROM [Associations]
		JOIN [ROFsts]  ON
			[ROFsts].[ROFstID]=[Associations].[ROFstID]
	WHERE
		[Associations].[VersionID]=@VersionID

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

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

(
	@ROFstID int
)
AS

	SELECT
		[Associations].[AssociationID],
		[Associations].[VersionID],
		[Associations].[ROFstID],
		[Associations].[Config],
		[Associations].[DTS],
		[Associations].[UserID],
		[Associations].[LastChanged],
		[DocVersions].[FolderID] [DocVersion_FolderID],
		[DocVersions].[VersionType] [DocVersion_VersionType],
		[DocVersions].[Name] [DocVersion_Name],
		[DocVersions].[Title] [DocVersion_Title],
		[DocVersions].[ItemID] [DocVersion_ItemID],
		[DocVersions].[FormatID] [DocVersion_FormatID],
		[DocVersions].[Config] [DocVersion_Config],
		[DocVersions].[DTS] [DocVersion_DTS],
		[DocVersions].[UserID] [DocVersion_UserID]
	FROM [Associations]
		JOIN [DocVersions]  ON
			[DocVersions].[VersionID]=[Associations].[VersionID]
	WHERE
		[Associations].[ROFstID]=@ROFstID

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

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

AS
	SELECT
		[DBID],
		[Name],
		[Title],
		[ConnectionString],
		[ServerType],
		[Config],
		[DTS],
		[UsrID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Folders] WHERE [Folders].[DBID]=[Connections].[DBID]) [FolderCount]
	FROM [Connections]
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getConnections Succeeded'
ELSE PRINT 'Procedure Creation: getConnections Error on Creation'
GO

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

(
	@DBID int,
	@Name nvarchar(100)=null,
	@Title nvarchar(510)=null,
	@ConnectionString nvarchar(510)=null,
	@ServerType int,
	@Config nvarchar(MAX)=null,
	@DTS datetime,
	@UsrID nvarchar(100),
	@LastChanged timestamp,
	@newLastChanged timestamp output
)
AS
	UPDATE [Connections]
		SET
			[Name]=@Name,
			[Title]=@Title,
			[ConnectionString]=@ConnectionString,
			[ServerType]=@ServerType,
			[Config]=@Config,
			[DTS]=@DTS,
			[UsrID]=@UsrID
		WHERE [DBID]=@DBID AND [LastChanged]=@LastChanged
	IF @@ROWCOUNT = 0
	BEGIN
		IF NOT exists(select * from [Connections] WHERE [DBID]=@DBID)
			RAISERROR('Connection record has been deleted by another user', 16, 1)
		ELSE           	
			RAISERROR('Connection has been edited by another user', 16, 1)            	
	END

		SELECT 		@newLastChanged=[LastChanged]	
		FROM [Connections] WHERE [DBID]=@DBID		

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

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

(
	@Name nvarchar(100)=null,
	@Title nvarchar(510)=null,
	@ConnectionString nvarchar(510)=null,
	@ServerType int,
	@Config nvarchar(MAX)=null,
	@DTS datetime,
	@UsrID nvarchar(100),
	@newDBID int output,
	@newLastChanged timestamp output
)
AS
	INSERT INTO [Connections]
	(
		[Name],
		[Title],
		[ConnectionString],
		[ServerType],
		[Config],
		[DTS],
		[UsrID]
	)
	VALUES
	(
		@Name,
		@Title,
		@ConnectionString,
		@ServerType,
		@Config,
		@DTS,
		@UsrID
	)
	SELECT @newDBID= SCOPE_IDENTITY()
	SELECT 		@newLastChanged=[LastChanged]
		FROM [Connections] WHERE [DBID]=@newDBID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addConnection Succeeded'
ELSE PRINT 'Procedure Creation: addConnection Error on Creation'
GO

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

(
	@DBID int
)
AS
	DELETE [Folders]
		WHERE [DBID]=@DBID
	DELETE [Connections]
		WHERE [DBID] = @DBID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteConnection Succeeded'
ELSE PRINT 'Procedure Creation: deleteConnection Error on Creation'
GO

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

(
	@DBID int
)
AS
	SELECT
		[DBID],
		[Name],
		[Title],
		[ConnectionString],
		[ServerType],
		[Config],
		[DTS],
		[UsrID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Folders] WHERE [Folders].[DBID]=[Connections].[DBID]) [FolderCount]
	FROM [Connections]
	WHERE [DBID]=@DBID

	SELECT
		[Folders].[FolderID],
		[Folders].[ParentID],
		[Folders].[DBID],
		[Folders].[Name],
		[Folders].[Title],
		[Folders].[ShortName],
		[Folders].[FormatID],
		[Folders].[ManualOrder],
		[Folders].[Config],
		[Folders].[DTS],
		[Folders].[UsrID],
		[Folders].[LastChanged]
	FROM [Folders]
	WHERE
		[Folders].[DBID]=@DBID

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

/****** Object:  StoredProcedure [getConnectionByName] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getConnectionByName]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getConnectionByName];
GO
	
CREATE PROCEDURE [dbo].[getConnectionByName]
(
	@Name nvarchar(100)
)
AS
	SELECT
		[DBID],
		[Name],
		[Title],
		[ConnectionString],
		[ServerType],
		[Config],
		[DTS],
		[UsrID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Folders] WHERE [Folders].[DBID]=[Connections].[DBID]) [FolderCount]
	FROM [Connections]
	WHERE [Name]=@Name
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getConnectionByName Succeeded'
ELSE PRINT 'Procedure Creation: getConnectionByName Error on Creation'
GO

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

(
	@DBID int
)
	AS
	SELECT COUNT(*)
	FROM [Connections] WHERE [DBID]=@DBID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsConnection Succeeded'
ELSE PRINT 'Procedure Creation: existsConnection Error on Creation'
GO

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

AS
	SELECT
		[ContentID],
		[Number],
		[Text],
		[Type],
		[FormatID],
		[Config],
		[DTS],
		[UserID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Details] WHERE [Details].[ContentID]=[Contents].[ContentID]) [DetailCount],
		(SELECT COUNT(*) FROM [Entries] WHERE [Entries].[ContentID]=[Contents].[ContentID]) [EntryCount],
		(SELECT COUNT(*) FROM [Items] WHERE [Items].[ContentID]=[Contents].[ContentID]) [ItemCount],
		(SELECT COUNT(*) FROM [Parts] WHERE [Parts].[ContentID]=[Contents].[ContentID]) [PartCount],
		(SELECT COUNT(*) FROM [RoUsages] WHERE [RoUsages].[ContentID]=[Contents].[ContentID]) [RoUsageCount],
		(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[FromID]=[Contents].[ContentID]) [TransitionCount],
		(SELECT COUNT(*) FROM [ZContents] WHERE [ZContents].[ContentID]=[Contents].[ContentID]) [ZContentCount]
	FROM [Contents]
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getContents Succeeded'
ELSE PRINT 'Procedure Creation: getContents Error on Creation'
GO

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

(
	@ContentID int,
	@Number nvarchar(30)=null,
	@Text nvarchar(MAX)=null,
	@Type int=null,
	@FormatID int=null,
	@Config nvarchar(MAX)=null,
	@DTS datetime,
	@UserID nvarchar(100),
	@LastChanged timestamp,
	@newLastChanged timestamp output
)
AS
	UPDATE [Contents]
		SET
			[Number]=@Number,
			[Text]=@Text,
			[Type]=@Type,
			[FormatID]=@FormatID,
			[Config]=@Config,
			[DTS]=@DTS,
			[UserID]=@UserID
		WHERE [ContentID]=@ContentID AND [LastChanged]=@LastChanged
	IF @@ROWCOUNT = 0
	BEGIN
		IF NOT exists(select * from [Contents] WHERE [ContentID]=@ContentID)
			RAISERROR('Content record has been deleted by another user', 16, 1)
		ELSE           	
			RAISERROR('Content has been edited by another user', 16, 1)            	
	END

		SELECT 		@newLastChanged=[LastChanged]	
		FROM [Contents] WHERE [ContentID]=@ContentID		

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

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

(
	@Number nvarchar(30)=null,
	@Text nvarchar(MAX)=null,
	@Type int=null,
	@FormatID int=null,
	@Config nvarchar(MAX)=null,
	@DTS datetime,
	@UserID nvarchar(100),
	@newContentID int output,
	@newLastChanged timestamp output
)
AS
	INSERT INTO [Contents]
	(
		[Number],
		[Text],
		[Type],
		[FormatID],
		[Config],
		[DTS],
		[UserID]
	)
	VALUES
	(
		@Number,
		@Text,
		@Type,
		@FormatID,
		@Config,
		@DTS,
		@UserID
	)
	SELECT @newContentID= SCOPE_IDENTITY()
	SELECT 		@newLastChanged=[LastChanged]
		FROM [Contents] WHERE [ContentID]=@newContentID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addContent Succeeded'
ELSE PRINT 'Procedure Creation: addContent Error on Creation'
GO

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

(
	@ContentID int
)
AS
	DELETE [Details]
		WHERE [ContentID]=@ContentID
	DELETE [Entries]
		WHERE [ContentID]=@ContentID
	DELETE [Items]
		WHERE [ContentID]=@ContentID
	DELETE [Parts]
		WHERE [ContentID]=@ContentID
	DELETE [RoUsages]
		WHERE [ContentID]=@ContentID
	DELETE [Transitions]
		WHERE [FromID]=@ContentID
	DELETE [ZContents]
		WHERE [ContentID]=@ContentID
	DELETE [Contents]
		WHERE [ContentID] = @ContentID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteContent Succeeded'
ELSE PRINT 'Procedure Creation: deleteContent Error on Creation'
GO

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

(
	@ContentID int
)
AS
	SELECT
		[ContentID],
		[Number],
		[Text],
		[Type],
		[FormatID],
		[Config],
		[DTS],
		[UserID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Details] WHERE [Details].[ContentID]=[Contents].[ContentID]) [DetailCount],
		(SELECT COUNT(*) FROM [Entries] WHERE [Entries].[ContentID]=[Contents].[ContentID]) [EntryCount],
		(SELECT COUNT(*) FROM [Items] WHERE [Items].[ContentID]=[Contents].[ContentID]) [ItemCount],
		(SELECT COUNT(*) FROM [Parts] WHERE [Parts].[ContentID]=[Contents].[ContentID]) [PartCount],
		(SELECT COUNT(*) FROM [RoUsages] WHERE [RoUsages].[ContentID]=[Contents].[ContentID]) [RoUsageCount],
		(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[FromID]=[Contents].[ContentID]) [TransitionCount],
		(SELECT COUNT(*) FROM [ZContents] WHERE [ZContents].[ContentID]=[Contents].[ContentID]) [ZContentCount]
	FROM [Contents]
	WHERE [ContentID]=@ContentID

	SELECT
		[Details].[DetailID],
		[Details].[ContentID],
		[Details].[ItemType],
		[Details].[Text],
		[Details].[Config],
		[Details].[DTS],
		[Details].[UserID],
		[Details].[LastChanged]
	FROM [Details]
	WHERE
		[Details].[ContentID]=@ContentID


	SELECT
		[Entries].[ContentID],
		[Entries].[DocID],
		[Entries].[DTS],
		[Entries].[UserID],
		[Entries].[LastChanged],
		[Documents].[LibTitle] [Document_LibTitle],
		[Documents].[DocContent] [Document_DocContent],
		[Documents].[DocAscii] [Document_DocAscii],
		[Documents].[Config] [Document_Config],
		[Documents].[DTS] [Document_DTS],
		[Documents].[UserID] [Document_UserID]
	FROM [Entries]
		JOIN [Documents]  ON
			[Documents].[DocID]=[Entries].[DocID]
	WHERE
		[Entries].[ContentID]=@ContentID


	SELECT
		[Items].[ItemID],
		[Items].[PreviousID],
		[Items].[ContentID],
		[Items].[DTS],
		[Items].[UserID],
		[Items].[LastChanged]
	FROM [Items]
	WHERE
		[Items].[ContentID]=@ContentID


	SELECT
		[Parts].[ContentID],
		[Parts].[FromType],
		[Parts].[ItemID],
		[Parts].[DTS],
		[Parts].[UserID],
		[Parts].[LastChanged],
		[Items].[PreviousID] [Item_PreviousID],
		[Items].[ContentID] [Item_ContentID],
		[Items].[DTS] [Item_DTS],
		[Items].[UserID] [Item_UserID]
	FROM [Parts]
		JOIN [Items]  ON
			[Items].[ItemID]=[Parts].[ItemID]
	WHERE
		[Parts].[ContentID]=@ContentID


	SELECT
		[RoUsages].[ROUsageID],
		[RoUsages].[ContentID],
		[RoUsages].[ROID],
		[RoUsages].[Config],
		[RoUsages].[DTS],
		[RoUsages].[UserID],
		[RoUsages].[LastChanged],
		[RoUsages].[RODbID],
		[RODbs].[ROName] [RODb_ROName],
		[RODbs].[FolderPath] [RODb_FolderPath],
		[RODbs].[DBConnectionString] [RODb_DBConnectionString],
		[RODbs].[Config] [RODb_Config],
		[RODbs].[DTS] [RODb_DTS],
		[RODbs].[UserID] [RODb_UserID]
	FROM [RoUsages]
		JOIN [RODbs]  ON
			[RODbs].[RODbID]=[RoUsages].[RODbID]
	WHERE
		[RoUsages].[ContentID]=@ContentID


	SELECT
		[Transitions].[TransitionID],
		[Transitions].[FromID],
		[Transitions].[ToID],
		[Transitions].[RangeID],
		[Transitions].[TranType],
		[Transitions].[Config],
		[Transitions].[DTS],
		[Transitions].[UserID],
		[Transitions].[LastChanged],
		[Items_RangeID].[PreviousID] [Item_RangeID_PreviousID],
		[Items_RangeID].[ContentID] [Item_RangeID_ContentID],
		[Items_RangeID].[DTS] [Item_RangeID_DTS],
		[Items_RangeID].[UserID] [Item_RangeID_UserID],
		[Items_ToID].[PreviousID] [Item_ToID_PreviousID],
		[Items_ToID].[ContentID] [Item_ToID_ContentID],
		[Items_ToID].[DTS] [Item_ToID_DTS],
		[Items_ToID].[UserID] [Item_ToID_UserID]
	FROM [Transitions]
		JOIN [Items] [Items_RangeID] ON
			[Items_RangeID].[ItemID]=[Transitions].[RangeID]
		JOIN [Items] [Items_ToID] ON
			[Items_ToID].[ItemID]=[Transitions].[ToID]
	WHERE
		[Transitions].[FromID]=@ContentID


	SELECT
		[ZContents].[ContentID],
		[ZContents].[OldStepSequence],
		[ZContents].[LastChanged]
	FROM [ZContents]
	WHERE
		[ZContents].[ContentID]=@ContentID

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

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

(
	@ContentID int
)
	AS
	SELECT COUNT(*)
	FROM [Contents] WHERE [ContentID]=@ContentID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsContent Succeeded'
ELSE PRINT 'Procedure Creation: existsContent Error on Creation'
GO

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

(
	@FormatID int
)
AS

	SELECT
		[Contents].[ContentID],
		[Contents].[Number],
		[Contents].[Text],
		[Contents].[Type],
		[Contents].[FormatID],
		[Contents].[Config],
		[Contents].[DTS],
		[Contents].[UserID],
		[Contents].[LastChanged],
		(SELECT COUNT(*) FROM [Details] WHERE [Details].[ContentID]=[Contents].[ContentID]) [DetailCount],
		(SELECT COUNT(*) FROM [Entries] WHERE [Entries].[ContentID]=[Contents].[ContentID]) [EntryCount],
		(SELECT COUNT(*) FROM [Items] WHERE [Items].[ContentID]=[Contents].[ContentID]) [ItemCount],
		(SELECT COUNT(*) FROM [Parts] WHERE [Parts].[ContentID]=[Contents].[ContentID]) [PartCount],
		(SELECT COUNT(*) FROM [RoUsages] WHERE [RoUsages].[ContentID]=[Contents].[ContentID]) [RoUsageCount],
		(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[FromID]=[Contents].[ContentID]) [TransitionCount],
		(SELECT COUNT(*) FROM [ZContents] WHERE [ZContents].[ContentID]=[Contents].[ContentID]) [ZContentCount]
	FROM [Contents]
	WHERE
		[Contents].[FormatID]=@FormatID

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

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

AS
	SELECT
		[DetailID],
		[ContentID],
		[ItemType],
		[Text],
		[Config],
		[DTS],
		[UserID],
		[LastChanged]
	FROM [Details]
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getDetails Succeeded'
ELSE PRINT 'Procedure Creation: getDetails Error on Creation'
GO

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

(
	@DetailID int,
	@ContentID int,
	@ItemType int,
	@Text nvarchar(MAX),
	@Config nvarchar(MAX)=null,
	@DTS datetime,
	@UserID nvarchar(100),
	@LastChanged timestamp,
	@newLastChanged timestamp output
)
AS
	UPDATE [Details]
		SET
			[ContentID]=@ContentID,
			[ItemType]=@ItemType,
			[Text]=@Text,
			[Config]=@Config,
			[DTS]=@DTS,
			[UserID]=@UserID
		WHERE [DetailID]=@DetailID AND [LastChanged]=@LastChanged
	IF @@ROWCOUNT = 0
	BEGIN
		IF NOT exists(select * from [Details] WHERE [DetailID]=@DetailID)
			RAISERROR('Detail record has been deleted by another user', 16, 1)
		ELSE           	
			RAISERROR('Detail has been edited by another user', 16, 1)            	
	END

		SELECT 		@newLastChanged=[LastChanged]	
		FROM [Details] WHERE [DetailID]=@DetailID		

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

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

(
	@ContentID int,
	@ItemType int,
	@Text nvarchar(MAX),
	@Config nvarchar(MAX)=null,
	@DTS datetime,
	@UserID nvarchar(100),
	@newDetailID int output,
	@newLastChanged timestamp output
)
AS
	INSERT INTO [Details]
	(
		[ContentID],
		[ItemType],
		[Text],
		[Config],
		[DTS],
		[UserID]
	)
	VALUES
	(
		@ContentID,
		@ItemType,
		@Text,
		@Config,
		@DTS,
		@UserID
	)
	SELECT @newDetailID= SCOPE_IDENTITY()
	SELECT 		@newLastChanged=[LastChanged]
		FROM [Details] WHERE [DetailID]=@newDetailID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addDetail Succeeded'
ELSE PRINT 'Procedure Creation: addDetail Error on Creation'
GO

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

(
	@DetailID int
)
AS
	DELETE [Details]
		WHERE [DetailID] = @DetailID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteDetail Succeeded'
ELSE PRINT 'Procedure Creation: deleteDetail Error on Creation'
GO

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

(
	@DetailID int
)
AS
	SELECT
		[DetailID],
		[ContentID],
		[ItemType],
		[Text],
		[Config],
		[DTS],
		[UserID],
		[LastChanged]
	FROM [Details]
	WHERE [DetailID]=@DetailID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getDetail Succeeded'
ELSE PRINT 'Procedure Creation: getDetail Error on Creation'
GO

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

(
	@DetailID int
)
	AS
	SELECT COUNT(*)
	FROM [Details] WHERE [DetailID]=@DetailID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsDetail Succeeded'
ELSE PRINT 'Procedure Creation: existsDetail Error on Creation'
GO

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

(
	@ContentID int
)
AS

	SELECT
		[Details].[DetailID],
		[Details].[ContentID],
		[Details].[ItemType],
		[Details].[Text],
		[Details].[Config],
		[Details].[DTS],
		[Details].[UserID],
		[Details].[LastChanged]
	FROM [Details]
	WHERE
		[Details].[ContentID]=@ContentID

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

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

AS
	SELECT
		[DocID],
		[LibTitle],
		[DocContent],
		[DocAscii],
		[Config],
		[DTS],
		[UserID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Entries] WHERE [Entries].[DocID]=[Documents].[DocID]) [EntryCount]
	FROM [Documents]
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getDocuments Succeeded'
ELSE PRINT 'Procedure Creation: getDocuments Error on Creation'
GO

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

(
	@DocID int,
	@LibTitle nvarchar(1024)=null,
	@DocContent varbinary(MAX)=null,
	@DocAscii nvarchar(MAX)=null,
	@Config nvarchar(MAX)=null,
	@DTS datetime,
	@UserID nvarchar(100),
	@LastChanged timestamp,
	@newLastChanged timestamp output
)
AS
	UPDATE [Documents]
		SET
			[LibTitle]=@LibTitle,
			[DocContent]=@DocContent,
			[DocAscii]=@DocAscii,
			[Config]=@Config,
			[DTS]=@DTS,
			[UserID]=@UserID
		WHERE [DocID]=@DocID AND [LastChanged]=@LastChanged
	IF @@ROWCOUNT = 0
	BEGIN
		IF NOT exists(select * from [Documents] WHERE [DocID]=@DocID)
			RAISERROR('Document record has been deleted by another user', 16, 1)
		ELSE           	
			RAISERROR('Document has been edited by another user', 16, 1)            	
	END

		SELECT 		@newLastChanged=[LastChanged]	
		FROM [Documents] WHERE [DocID]=@DocID		

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

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

(
	@LibTitle nvarchar(1024)=null,
	@DocContent varbinary(MAX)=null,
	@DocAscii nvarchar(MAX)=null,
	@Config nvarchar(MAX)=null,
	@DTS datetime,
	@UserID nvarchar(100),
	@newDocID int output,
	@newLastChanged timestamp output
)
AS
	INSERT INTO [Documents]
	(
		[LibTitle],
		[DocContent],
		[DocAscii],
		[Config],
		[DTS],
		[UserID]
	)
	VALUES
	(
		@LibTitle,
		@DocContent,
		@DocAscii,
		@Config,
		@DTS,
		@UserID
	)
	SELECT @newDocID= SCOPE_IDENTITY()
	SELECT 		@newLastChanged=[LastChanged]
		FROM [Documents] WHERE [DocID]=@newDocID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addDocument Succeeded'
ELSE PRINT 'Procedure Creation: addDocument Error on Creation'
GO

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

(
	@DocID int
)
AS
	DELETE [Entries]
		WHERE [DocID]=@DocID
	DELETE [Documents]
		WHERE [DocID] = @DocID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteDocument Succeeded'
ELSE PRINT 'Procedure Creation: deleteDocument Error on Creation'
GO

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

(
	@DocID int
)
AS
	SELECT
		[DocID],
		[LibTitle],
		[DocContent],
		[DocAscii],
		[Config],
		[DTS],
		[UserID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Entries] WHERE [Entries].[DocID]=[Documents].[DocID]) [EntryCount]
	FROM [Documents]
	WHERE [DocID]=@DocID

	SELECT
		[Entries].[ContentID],
		[Entries].[DocID],
		[Entries].[DTS],
		[Entries].[UserID],
		[Entries].[LastChanged],
		[Contents].[Number] [Content_Number],
		[Contents].[Text] [Content_Text],
		[Contents].[Type] [Content_Type],
		[Contents].[FormatID] [Content_FormatID],
		[Contents].[Config] [Content_Config],
		[Contents].[DTS] [Content_DTS],
		[Contents].[UserID] [Content_UserID]
	FROM [Entries]
		JOIN [Contents]  ON
			[Contents].[ContentID]=[Entries].[ContentID]
	WHERE
		[Entries].[DocID]=@DocID

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

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

(
	@DocID int
)
	AS
	SELECT COUNT(*)
	FROM [Documents] WHERE [DocID]=@DocID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsDocument Succeeded'
ELSE PRINT 'Procedure Creation: existsDocument Error on Creation'
GO

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

AS
	SELECT
		[VersionID],
		[FolderID],
		[VersionType],
		[Name],
		[Title],
		[ItemID],
		[FormatID],
		[Config],
		[DTS],
		[UserID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Associations] WHERE [Associations].[VersionID]=[DocVersions].[VersionID]) [AssociationCount]
	FROM [DocVersions]
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getDocVersions Succeeded'
ELSE PRINT 'Procedure Creation: getDocVersions Error on Creation'
GO

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

(
	@VersionID int,
	@FolderID int,
	@VersionType int,
	@Name nvarchar(100),
	@Title nvarchar(510)=null,
	@ItemID int=null,
	@FormatID int=null,
	@Config nvarchar(MAX)=null,
	@DTS datetime,
	@UserID nvarchar(100),
	@LastChanged timestamp,
	@newLastChanged timestamp output
)
AS
	UPDATE [DocVersions]
		SET
			[FolderID]=@FolderID,
			[VersionType]=@VersionType,
			[Name]=@Name,
			[Title]=@Title,
			[ItemID]=@ItemID,
			[FormatID]=@FormatID,
			[Config]=@Config,
			[DTS]=@DTS,
			[UserID]=@UserID
		WHERE [VersionID]=@VersionID AND [LastChanged]=@LastChanged
	IF @@ROWCOUNT = 0
	BEGIN
		IF NOT exists(select * from [DocVersions] WHERE [VersionID]=@VersionID)
			RAISERROR('DocVersion record has been deleted by another user', 16, 1)
		ELSE           	
			RAISERROR('DocVersion has been edited by another user', 16, 1)            	
	END

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

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

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

(
	@FolderID int,
	@VersionType int,
	@Name nvarchar(100),
	@Title nvarchar(510)=null,
	@ItemID int=null,
	@FormatID int=null,
	@Config nvarchar(MAX)=null,
	@DTS datetime,
	@UserID nvarchar(100),
	@newVersionID int output,
	@newLastChanged timestamp output
)
AS
	INSERT INTO [DocVersions]
	(
		[FolderID],
		[VersionType],
		[Name],
		[Title],
		[ItemID],
		[FormatID],
		[Config],
		[DTS],
		[UserID]
	)
	VALUES
	(
		@FolderID,
		@VersionType,
		@Name,
		@Title,
		@ItemID,
		@FormatID,
		@Config,
		@DTS,
		@UserID
	)
	SELECT @newVersionID= SCOPE_IDENTITY()
	SELECT 		@newLastChanged=[LastChanged]
		FROM [DocVersions] WHERE [VersionID]=@newVersionID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addDocVersion Succeeded'
ELSE PRINT 'Procedure Creation: addDocVersion Error on Creation'
GO

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

(
	@VersionID int
)
AS
	DELETE [Associations]
		WHERE [VersionID]=@VersionID
	DELETE [DocVersions]
		WHERE [VersionID] = @VersionID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteDocVersion Succeeded'
ELSE PRINT 'Procedure Creation: deleteDocVersion Error on Creation'
GO

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

(
	@VersionID int
)
AS
	SELECT
		[VersionID],
		[FolderID],
		[VersionType],
		[Name],
		[Title],
		[ItemID],
		[FormatID],
		[Config],
		[DTS],
		[UserID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Associations] WHERE [Associations].[VersionID]=[DocVersions].[VersionID]) [AssociationCount]
	FROM [DocVersions]
	WHERE [VersionID]=@VersionID

	SELECT
		[Associations].[AssociationID],
		[Associations].[VersionID],
		[Associations].[ROFstID],
		[Associations].[Config],
		[Associations].[DTS],
		[Associations].[UserID],
		[Associations].[LastChanged],
		[ROFsts].[RODbID] [ROFst_RODbID],
		[ROFsts].[ROFST] [ROFst_ROFST],
		[ROFsts].[Config] [ROFst_Config],
		[ROFsts].[DTS] [ROFst_DTS],
		[ROFsts].[UserID] [ROFst_UserID]
	FROM [Associations]
		JOIN [ROFsts]  ON
			[ROFsts].[ROFstID]=[Associations].[ROFstID]
	WHERE
		[Associations].[VersionID]=@VersionID

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

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

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

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

(
	@FolderID int
)
AS

	SELECT
		[DocVersions].[VersionID],
		[DocVersions].[FolderID],
		[DocVersions].[VersionType],
		[DocVersions].[Name],
		[DocVersions].[Title],
		[DocVersions].[ItemID],
		[DocVersions].[FormatID],
		[DocVersions].[Config],
		[DocVersions].[DTS],
		[DocVersions].[UserID],
		[DocVersions].[LastChanged],
		(SELECT COUNT(*) FROM [Associations] WHERE [Associations].[VersionID]=[DocVersions].[VersionID]) [AssociationCount]
	FROM [DocVersions]
	WHERE
		[DocVersions].[FolderID]=@FolderID

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

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

(
	@FormatID int
)
AS

	SELECT
		[DocVersions].[VersionID],
		[DocVersions].[FolderID],
		[DocVersions].[VersionType],
		[DocVersions].[Name],
		[DocVersions].[Title],
		[DocVersions].[ItemID],
		[DocVersions].[FormatID],
		[DocVersions].[Config],
		[DocVersions].[DTS],
		[DocVersions].[UserID],
		[DocVersions].[LastChanged],
		[Folders].[ParentID] [Folder_ParentID],
		[Folders].[DBID] [Folder_DBID],
		[Folders].[Name] [Folder_Name],
		[Folders].[Title] [Folder_Title],
		[Folders].[ShortName] [Folder_ShortName],
		[Folders].[FormatID] [Folder_FormatID],
		[Folders].[ManualOrder] [Folder_ManualOrder],
		[Folders].[Config] [Folder_Config],
		[Folders].[DTS] [Folder_DTS],
		[Folders].[UsrID] [Folder_UsrID],
		(SELECT COUNT(*) FROM [Associations] WHERE [Associations].[VersionID]=[DocVersions].[VersionID]) [AssociationCount]
	FROM [DocVersions]
		JOIN [Folders]  ON
			[Folders].[FolderID]=[DocVersions].[FolderID]
	WHERE
		[DocVersions].[FormatID]=@FormatID

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

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

(
	@ItemID int
)
AS

	SELECT
		[DocVersions].[VersionID],
		[DocVersions].[FolderID],
		[DocVersions].[VersionType],
		[DocVersions].[Name],
		[DocVersions].[Title],
		[DocVersions].[ItemID],
		[DocVersions].[FormatID],
		[DocVersions].[Config],
		[DocVersions].[DTS],
		[DocVersions].[UserID],
		[DocVersions].[LastChanged],
		[Folders].[ParentID] [Folder_ParentID],
		[Folders].[DBID] [Folder_DBID],
		[Folders].[Name] [Folder_Name],
		[Folders].[Title] [Folder_Title],
		[Folders].[ShortName] [Folder_ShortName],
		[Folders].[FormatID] [Folder_FormatID],
		[Folders].[ManualOrder] [Folder_ManualOrder],
		[Folders].[Config] [Folder_Config],
		[Folders].[DTS] [Folder_DTS],
		[Folders].[UsrID] [Folder_UsrID],
		(SELECT COUNT(*) FROM [Associations] WHERE [Associations].[VersionID]=[DocVersions].[VersionID]) [AssociationCount]
	FROM [DocVersions]
		JOIN [Folders]  ON
			[Folders].[FolderID]=[DocVersions].[FolderID]
	WHERE
		[DocVersions].[ItemID]=@ItemID

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

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

AS
	SELECT
		[ContentID],
		[DocID],
		[DTS],
		[UserID],
		[LastChanged]
	FROM [Entries]
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getEntries Succeeded'
ELSE PRINT 'Procedure Creation: getEntries Error on Creation'
GO

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

(
	@ContentID int,
	@DocID int,
	@DTS datetime,
	@UserID nvarchar(100),
	@LastChanged timestamp,
	@newLastChanged timestamp output
)
AS
	UPDATE [Entries]
		SET
			[DocID]=@DocID,
			[DTS]=@DTS,
			[UserID]=@UserID
		WHERE [ContentID]=@ContentID AND [LastChanged]=@LastChanged
	IF @@ROWCOUNT = 0
	BEGIN
		IF NOT exists(select * from [Entries] WHERE [ContentID]=@ContentID)
			RAISERROR('Entry record has been deleted by another user', 16, 1)
		ELSE           	
			RAISERROR('Entry has been edited by another user', 16, 1)            	
	END

		SELECT 		@newLastChanged=[LastChanged]	
		FROM [Entries] WHERE [ContentID]=@ContentID		

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

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

(
	@ContentID int,
	@DocID int,
	@DTS datetime,
	@UserID nvarchar(100),
	@newLastChanged timestamp output
)
AS
	INSERT INTO [Entries]
	(
		[ContentID],
		[DocID],
		[DTS],
		[UserID]
	)
	VALUES
	(
		@ContentID,
		@DocID,
		@DTS,
		@UserID
	)

	SELECT 		@newLastChanged=[LastChanged]
		FROM [Entries] WHERE [ContentID]=@ContentID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addEntry Succeeded'
ELSE PRINT 'Procedure Creation: addEntry Error on Creation'
GO

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

(
	@ContentID int
)
AS
	DELETE [Entries]
		WHERE [ContentID] = @ContentID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteEntry Succeeded'
ELSE PRINT 'Procedure Creation: deleteEntry Error on Creation'
GO

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

(
	@ContentID int
)
AS
	SELECT
		[ContentID],
		[DocID],
		[DTS],
		[UserID],
		[LastChanged]
	FROM [Entries]
	WHERE [ContentID]=@ContentID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getEntry Succeeded'
ELSE PRINT 'Procedure Creation: getEntry Error on Creation'
GO

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

(
	@ContentID int
)
	AS
	SELECT COUNT(*)
	FROM [Entries] WHERE [ContentID]=@ContentID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsEntry Succeeded'
ELSE PRINT 'Procedure Creation: existsEntry Error on Creation'
GO

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

(
	@ContentID int
)
AS

	SELECT
		[Entries].[ContentID],
		[Entries].[DocID],
		[Entries].[DTS],
		[Entries].[UserID],
		[Entries].[LastChanged],
		[Documents].[LibTitle] [Document_LibTitle],
		[Documents].[DocContent] [Document_DocContent],
		[Documents].[DocAscii] [Document_DocAscii],
		[Documents].[Config] [Document_Config],
		[Documents].[DTS] [Document_DTS],
		[Documents].[UserID] [Document_UserID]
	FROM [Entries]
		JOIN [Documents]  ON
			[Documents].[DocID]=[Entries].[DocID]
	WHERE
		[Entries].[ContentID]=@ContentID

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

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

(
	@DocID int
)
AS

	SELECT
		[Entries].[ContentID],
		[Entries].[DocID],
		[Entries].[DTS],
		[Entries].[UserID],
		[Entries].[LastChanged],
		[Contents].[Number] [Content_Number],
		[Contents].[Text] [Content_Text],
		[Contents].[Type] [Content_Type],
		[Contents].[FormatID] [Content_FormatID],
		[Contents].[Config] [Content_Config],
		[Contents].[DTS] [Content_DTS],
		[Contents].[UserID] [Content_UserID]
	FROM [Entries]
		JOIN [Contents]  ON
			[Contents].[ContentID]=[Entries].[ContentID]
	WHERE
		[Entries].[DocID]=@DocID

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

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

AS
	SELECT
		[FigureID],
		[ROFstID],
		[ImageID],
		[Config],
		[DTS],
		[UserID],
		[LastChanged]
	FROM [Figures]
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getFigures Succeeded'
ELSE PRINT 'Procedure Creation: getFigures Error on Creation'
GO

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

(
	@FigureID int,
	@ROFstID int,
	@ImageID int,
	@Config nvarchar(MAX)=null,
	@DTS datetime,
	@UserID nvarchar(100),
	@LastChanged timestamp,
	@newLastChanged timestamp output
)
AS
	UPDATE [Figures]
		SET
			[ROFstID]=@ROFstID,
			[ImageID]=@ImageID,
			[Config]=@Config,
			[DTS]=@DTS,
			[UserID]=@UserID
		WHERE [FigureID]=@FigureID AND [LastChanged]=@LastChanged
	IF @@ROWCOUNT = 0
	BEGIN
		IF NOT exists(select * from [Figures] WHERE [FigureID]=@FigureID)
			RAISERROR('Figure record has been deleted by another user', 16, 1)
		ELSE           	
			RAISERROR('Figure has been edited by another user', 16, 1)            	
	END

		SELECT 		@newLastChanged=[LastChanged]	
		FROM [Figures] WHERE [FigureID]=@FigureID		

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

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

(
	@ROFstID int,
	@ImageID int,
	@Config nvarchar(MAX)=null,
	@DTS datetime,
	@UserID nvarchar(100),
	@newFigureID int output,
	@newLastChanged timestamp output
)
AS
	INSERT INTO [Figures]
	(
		[ROFstID],
		[ImageID],
		[Config],
		[DTS],
		[UserID]
	)
	VALUES
	(
		@ROFstID,
		@ImageID,
		@Config,
		@DTS,
		@UserID
	)
	SELECT @newFigureID= SCOPE_IDENTITY()
	SELECT 		@newLastChanged=[LastChanged]
		FROM [Figures] WHERE [FigureID]=@newFigureID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addFigure Succeeded'
ELSE PRINT 'Procedure Creation: addFigure Error on Creation'
GO

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

(
	@FigureID int
)
AS
	DELETE [Figures]
		WHERE [FigureID] = @FigureID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteFigure Succeeded'
ELSE PRINT 'Procedure Creation: deleteFigure Error on Creation'
GO

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

(
	@FigureID int
)
AS
	SELECT
		[FigureID],
		[ROFstID],
		[ImageID],
		[Config],
		[DTS],
		[UserID],
		[LastChanged]
	FROM [Figures]
	WHERE [FigureID]=@FigureID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getFigure Succeeded'
ELSE PRINT 'Procedure Creation: getFigure Error on Creation'
GO

/****** Object:  StoredProcedure [getFigureByROFstID_ImageID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getFigureByROFstID_ImageID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getFigureByROFstID_ImageID];
GO
	
CREATE PROCEDURE [dbo].[getFigureByROFstID_ImageID]
(
	@ROFstID int,
	@ImageID int
)
AS
	SELECT
		[FigureID],
		[ROFstID],
		[ImageID],
		[Config],
		[DTS],
		[UserID],
		[LastChanged]
	FROM [Figures]
	WHERE [ROFstID]=@ROFstID AND [ImageID]=@ImageID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getFigureByROFstID_ImageID Succeeded'
ELSE PRINT 'Procedure Creation: getFigureByROFstID_ImageID Error on Creation'
GO

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

(
	@FigureID int
)
	AS
	SELECT COUNT(*)
	FROM [Figures] WHERE [FigureID]=@FigureID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsFigure Succeeded'
ELSE PRINT 'Procedure Creation: existsFigure Error on Creation'
GO

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

(
	@ROFstID int
)
AS

	SELECT
		[Figures].[FigureID],
		[Figures].[ROFstID],
		[Figures].[ImageID],
		[Figures].[Config],
		[Figures].[DTS],
		[Figures].[UserID],
		[Figures].[LastChanged],
		[ROImages].[RODbID] [ROImage_RODbID],
		[ROImages].[FileName] [ROImage_FileName],
		[ROImages].[Content] [ROImage_Content],
		[ROImages].[Config] [ROImage_Config],
		[ROImages].[DTS] [ROImage_DTS],
		[ROImages].[UserID] [ROImage_UserID]
	FROM [Figures]
		JOIN [ROImages]  ON
			[ROImages].[ImageID]=[Figures].[ImageID]
	WHERE
		[Figures].[ROFstID]=@ROFstID

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

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

(
	@ImageID int
)
AS

	SELECT
		[Figures].[FigureID],
		[Figures].[ROFstID],
		[Figures].[ImageID],
		[Figures].[Config],
		[Figures].[DTS],
		[Figures].[UserID],
		[Figures].[LastChanged],
		[ROFsts].[RODbID] [ROFst_RODbID],
		[ROFsts].[ROFST] [ROFst_ROFST],
		[ROFsts].[Config] [ROFst_Config],
		[ROFsts].[DTS] [ROFst_DTS],
		[ROFsts].[UserID] [ROFst_UserID]
	FROM [Figures]
		JOIN [ROFsts]  ON
			[ROFsts].[ROFstID]=[Figures].[ROFstID]
	WHERE
		[Figures].[ImageID]=@ImageID

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

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

AS
	SELECT
		[FolderID],
		[ParentID],
		[DBID],
		[Name],
		[Title],
		[ShortName],
		[FormatID],
		[ManualOrder],
		[Config],
		[DTS],
		[UsrID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Assignments] WHERE [Assignments].[FolderID]=[Folders].[FolderID]) [AssignmentCount],
		(SELECT COUNT(*) FROM [DocVersions] WHERE [DocVersions].[FolderID]=[Folders].[FolderID]) [DocVersionCount],
		(SELECT COUNT(*) FROM [Folders] [Children] WHERE [Children].[ParentID]=[Folders].[FolderID]) [ChildCount]
	FROM [Folders]
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getFolders Succeeded'
ELSE PRINT 'Procedure Creation: getFolders Error on Creation'
GO

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

(
	@FolderID int,
	@ParentID int,
	@DBID int,
	@Name nvarchar(100),
	@Title nvarchar(510)=null,
	@ShortName nvarchar(20),
	@FormatID int=null,
	@ManualOrder float=null,
	@Config nvarchar(MAX)=null,
	@DTS datetime,
	@UsrID nvarchar(100),
	@LastChanged timestamp,
	@newLastChanged timestamp output
)
AS
	UPDATE [Folders]
		SET
			[ParentID]=@ParentID,
			[DBID]=@DBID,
			[Name]=@Name,
			[Title]=@Title,
			[ShortName]=@ShortName,
			[FormatID]=@FormatID,
			[ManualOrder]=@ManualOrder,
			[Config]=@Config,
			[DTS]=@DTS,
			[UsrID]=@UsrID
		WHERE [FolderID]=@FolderID AND [LastChanged]=@LastChanged
	IF @@ROWCOUNT = 0
	BEGIN
		IF NOT exists(select * from [Folders] WHERE [FolderID]=@FolderID)
			RAISERROR('Folder record has been deleted by another user', 16, 1)
		ELSE           	
			RAISERROR('Folder has been edited by another user', 16, 1)            	
	END

		SELECT 		@newLastChanged=[LastChanged]	
		FROM [Folders] WHERE [FolderID]=@FolderID		

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

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

(
	@ParentID int,
	@DBID int,
	@Name nvarchar(100),
	@Title nvarchar(510)=null,
	@ShortName nvarchar(20),
	@FormatID int=null,
	@ManualOrder float=null,
	@Config nvarchar(MAX)=null,
	@DTS datetime,
	@UsrID nvarchar(100),
	@newFolderID int output,
	@newLastChanged timestamp output
)
AS
	INSERT INTO [Folders]
	(
		[ParentID],
		[DBID],
		[Name],
		[Title],
		[ShortName],
		[FormatID],
		[ManualOrder],
		[Config],
		[DTS],
		[UsrID]
	)
	VALUES
	(
		@ParentID,
		@DBID,
		@Name,
		@Title,
		@ShortName,
		@FormatID,
		@ManualOrder,
		@Config,
		@DTS,
		@UsrID
	)
	SELECT @newFolderID= SCOPE_IDENTITY()
	SELECT 		@newLastChanged=[LastChanged]
		FROM [Folders] WHERE [FolderID]=@newFolderID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addFolder Succeeded'
ELSE PRINT 'Procedure Creation: addFolder Error on Creation'
GO

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

(
	@FolderID int
)
AS
	DELETE [Assignments]
		WHERE [FolderID]=@FolderID
	DELETE [DocVersions]
		WHERE [FolderID]=@FolderID
	DELETE [Folders]
		WHERE [FolderID] = @FolderID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteFolder Succeeded'
ELSE PRINT 'Procedure Creation: deleteFolder Error on Creation'
GO

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

(
	@FolderID int
)
AS
	SELECT
		[FolderID],
		[ParentID],
		[DBID],
		[Name],
		[Title],
		[ShortName],
		[FormatID],
		[ManualOrder],
		[Config],
		[DTS],
		[UsrID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Assignments] WHERE [Assignments].[FolderID]=[Folders].[FolderID]) [AssignmentCount],
		(SELECT COUNT(*) FROM [DocVersions] WHERE [DocVersions].[FolderID]=[Folders].[FolderID]) [DocVersionCount],
		(SELECT COUNT(*) FROM [Folders] [Children] WHERE [Children].[ParentID]=[Folders].[FolderID]) [ChildCount]
	FROM [Folders]
	WHERE [FolderID]=@FolderID

	SELECT
		[Assignments].[AID],
		[Assignments].[GID],
		[Assignments].[RID],
		[Assignments].[FolderID],
		[Assignments].[StartDate],
		[Assignments].[EndDate],
		[Assignments].[DTS],
		[Assignments].[UsrID],
		[Assignments].[LastChanged],
		[Groups].[GroupName] [Group_GroupName],
		[Groups].[GroupType] [Group_GroupType],
		[Groups].[Config] [Group_Config],
		[Groups].[DTS] [Group_DTS],
		[Groups].[UsrID] [Group_UsrID],
		[Roles].[Name] [Role_Name],
		[Roles].[Title] [Role_Title],
		[Roles].[DTS] [Role_DTS],
		[Roles].[UsrID] [Role_UsrID]
	FROM [Assignments]
		JOIN [Groups]  ON
			[Groups].[GID]=[Assignments].[GID]
		JOIN [Roles]  ON
			[Roles].[RID]=[Assignments].[RID]
	WHERE
		[Assignments].[FolderID]=@FolderID


	SELECT
		[DocVersions].[VersionID],
		[DocVersions].[FolderID],
		[DocVersions].[VersionType],
		[DocVersions].[Name],
		[DocVersions].[Title],
		[DocVersions].[ItemID],
		[DocVersions].[FormatID],
		[DocVersions].[Config],
		[DocVersions].[DTS],
		[DocVersions].[UserID],
		[DocVersions].[LastChanged]
	FROM [DocVersions]
	WHERE
		[DocVersions].[FolderID]=@FolderID

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

/****** Object:  StoredProcedure [getFolderByParentID_Name] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getFolderByParentID_Name]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getFolderByParentID_Name];
GO
	
CREATE PROCEDURE [dbo].[getFolderByParentID_Name]
(
	@ParentID int,
	@Name nvarchar(100)
)
AS
	SELECT
		[FolderID],
		[ParentID],
		[DBID],
		[Name],
		[Title],
		[ShortName],
		[FormatID],
		[ManualOrder],
		[Config],
		[DTS],
		[UsrID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Assignments] WHERE [Assignments].[FolderID]=[Folders].[FolderID]) [AssignmentCount],
		(SELECT COUNT(*) FROM [DocVersions] WHERE [DocVersions].[FolderID]=[Folders].[FolderID]) [DocVersionCount],
		(SELECT COUNT(*) FROM [Folders] [Children] WHERE [Children].[ParentID]=[Folders].[FolderID]) [ChildCount]
	FROM [Folders]
	WHERE [ParentID]=@ParentID AND [Name]=@Name
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getFolderByParentID_Name Succeeded'
ELSE PRINT 'Procedure Creation: getFolderByParentID_Name Error on Creation'
GO

/****** Object:  StoredProcedure [getParentFolder] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getParentFolder]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getParentFolder];
GO
	
CREATE PROCEDURE [dbo].[getParentFolder]
(
	@ParentID int
)
AS
	SELECT
		[FolderID],
		[ParentID],
		[DBID],
		[Name],
		[Title],
		[ShortName],
		[FormatID],
		[ManualOrder],
		[Config],
		[DTS],
		[UsrID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Assignments] WHERE [Assignments].[FolderID]=[Folders].[FolderID]) [AssignmentCount],
		(SELECT COUNT(*) FROM [DocVersions] WHERE [DocVersions].[FolderID]=[Folders].[FolderID]) [DocVersionCount],
		(SELECT COUNT(*) FROM [Folders] [Children] WHERE [Children].[ParentID]=[Folders].[FolderID]) [ChildCount]
	FROM [Folders]
	WHERE
		[FolderID]=@ParentID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getParentFolder Succeeded'
ELSE PRINT 'Procedure Creation: getParentFolder Error on Creation'
GO

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

(
	@FolderID int
)
	AS
	SELECT COUNT(*)
	FROM [Folders] WHERE [FolderID]=@FolderID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsFolder Succeeded'
ELSE PRINT 'Procedure Creation: existsFolder Error on Creation'
GO

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

(
	@ParentID int
)
AS
	SELECT
		[FolderID],
		[ParentID],
		[DBID],
		[Name],
		[Title],
		[ShortName],
		[FormatID],
		[ManualOrder],
		[Config],
		[DTS],
		[UsrID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Assignments] WHERE [Assignments].[FolderID]=[Folders].[FolderID]) [AssignmentCount],
		(SELECT COUNT(*) FROM [DocVersions] WHERE [DocVersions].[FolderID]=[Folders].[FolderID]) [DocVersionCount],
		(SELECT COUNT(*) FROM [Folders] [Children] WHERE [Children].[ParentID]=[Folders].[FolderID]) [ChildCount]
	FROM [Folders] WHERE [ParentID]=@ParentID AND [FolderID]<>@ParentID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getChildFolders Succeeded'
ELSE PRINT 'Procedure Creation: getChildFolders Error on Creation'
GO

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

(
	@DBID int
)
AS

	SELECT
		[Folders].[FolderID],
		[Folders].[ParentID],
		[Folders].[DBID],
		[Folders].[Name],
		[Folders].[Title],
		[Folders].[ShortName],
		[Folders].[FormatID],
		[Folders].[ManualOrder],
		[Folders].[Config],
		[Folders].[DTS],
		[Folders].[UsrID],
		[Folders].[LastChanged],
		(SELECT COUNT(*) FROM [Assignments] WHERE [Assignments].[FolderID]=[Folders].[FolderID]) [AssignmentCount],
		(SELECT COUNT(*) FROM [DocVersions] WHERE [DocVersions].[FolderID]=[Folders].[FolderID]) [DocVersionCount],
		(SELECT COUNT(*) FROM [Folders] [Children] WHERE [Children].[ParentID]=[Folders].[FolderID]) [ChildCount]
	FROM [Folders]
	WHERE
		[Folders].[DBID]=@DBID

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

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

(
	@FormatID int
)
AS

	SELECT
		[Folders].[FolderID],
		[Folders].[ParentID],
		[Folders].[DBID],
		[Folders].[Name],
		[Folders].[Title],
		[Folders].[ShortName],
		[Folders].[FormatID],
		[Folders].[ManualOrder],
		[Folders].[Config],
		[Folders].[DTS],
		[Folders].[UsrID],
		[Folders].[LastChanged],
		[Connections].[Name] [Connection_Name],
		[Connections].[Title] [Connection_Title],
		[Connections].[ConnectionString] [Connection_ConnectionString],
		[Connections].[ServerType] [Connection_ServerType],
		[Connections].[Config] [Connection_Config],
		[Connections].[DTS] [Connection_DTS],
		[Connections].[UsrID] [Connection_UsrID],
		(SELECT COUNT(*) FROM [Assignments] WHERE [Assignments].[FolderID]=[Folders].[FolderID]) [AssignmentCount],
		(SELECT COUNT(*) FROM [DocVersions] WHERE [DocVersions].[FolderID]=[Folders].[FolderID]) [DocVersionCount],
		(SELECT COUNT(*) FROM [Folders] [Children] WHERE [Children].[ParentID]=[Folders].[FolderID]) [ChildCount]
	FROM [Folders]
		JOIN [Connections]  ON
			[Connections].[DBID]=[Folders].[DBID]
	WHERE
		[Folders].[FormatID]=@FormatID

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

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

AS
	SELECT
		[FormatID],
		[ParentID],
		[Name],
		[Description],
		[Data],
		[GenMac],
		[DTS],
		[UserID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Contents] WHERE [Contents].[FormatID]=[Formats].[FormatID]) [ContentCount],
		(SELECT COUNT(*) FROM [DocVersions] WHERE [DocVersions].[FormatID]=[Formats].[FormatID]) [DocVersionCount],
		(SELECT COUNT(*) FROM [Folders] WHERE [Folders].[FormatID]=[Formats].[FormatID]) [FolderCount],
		(SELECT COUNT(*) FROM [Formats] [Children] WHERE [Children].[ParentID]=[Formats].[FormatID]) [ChildCount]
	FROM [Formats]
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getFormats Succeeded'
ELSE PRINT 'Procedure Creation: getFormats Error on Creation'
GO

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

(
	@FormatID int,
	@ParentID int,
	@Name nvarchar(20),
	@Description nvarchar(250)=null,
	@Data xml,
	@GenMac xml=null,
	@DTS datetime,
	@UserID nvarchar(100),
	@LastChanged timestamp,
	@newLastChanged timestamp output
)
AS
	UPDATE [Formats]
		SET
			[ParentID]=@ParentID,
			[Name]=@Name,
			[Description]=@Description,
			[Data]=@Data,
			[GenMac]=@GenMac,
			[DTS]=@DTS,
			[UserID]=@UserID
		WHERE [FormatID]=@FormatID AND [LastChanged]=@LastChanged
	IF @@ROWCOUNT = 0
	BEGIN
		IF NOT exists(select * from [Formats] WHERE [FormatID]=@FormatID)
			RAISERROR('Format record has been deleted by another user', 16, 1)
		ELSE           	
			RAISERROR('Format has been edited by another user', 16, 1)            	
	END

		SELECT 		@newLastChanged=[LastChanged]	
		FROM [Formats] WHERE [FormatID]=@FormatID		

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

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

(
	@ParentID int,
	@Name nvarchar(20),
	@Description nvarchar(250)=null,
	@Data xml,
	@GenMac xml=null,
	@DTS datetime,
	@UserID nvarchar(100),
	@newFormatID int output,
	@newLastChanged timestamp output
)
AS
	INSERT INTO [Formats]
	(
		[ParentID],
		[Name],
		[Description],
		[Data],
		[GenMac],
		[DTS],
		[UserID]
	)
	VALUES
	(
		@ParentID,
		@Name,
		@Description,
		@Data,
		@GenMac,
		@DTS,
		@UserID
	)
	SELECT @newFormatID= SCOPE_IDENTITY()
	SELECT 		@newLastChanged=[LastChanged]
		FROM [Formats] WHERE [FormatID]=@newFormatID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addFormat Succeeded'
ELSE PRINT 'Procedure Creation: addFormat Error on Creation'
GO

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

(
	@FormatID int
)
AS
	DELETE [Contents]
		WHERE [FormatID]=@FormatID
	DELETE [DocVersions]
		WHERE [FormatID]=@FormatID
	DELETE [Folders]
		WHERE [FormatID]=@FormatID
	DELETE [Formats]
		WHERE [FormatID] = @FormatID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteFormat Succeeded'
ELSE PRINT 'Procedure Creation: deleteFormat Error on Creation'
GO

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

(
	@FormatID int
)
AS
	SELECT
		[FormatID],
		[ParentID],
		[Name],
		[Description],
		[Data],
		[GenMac],
		[DTS],
		[UserID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Contents] WHERE [Contents].[FormatID]=[Formats].[FormatID]) [ContentCount],
		(SELECT COUNT(*) FROM [DocVersions] WHERE [DocVersions].[FormatID]=[Formats].[FormatID]) [DocVersionCount],
		(SELECT COUNT(*) FROM [Folders] WHERE [Folders].[FormatID]=[Formats].[FormatID]) [FolderCount],
		(SELECT COUNT(*) FROM [Formats] [Children] WHERE [Children].[ParentID]=[Formats].[FormatID]) [ChildCount]
	FROM [Formats]
	WHERE [FormatID]=@FormatID

	SELECT
		[Contents].[ContentID],
		[Contents].[Number],
		[Contents].[Text],
		[Contents].[Type],
		[Contents].[FormatID],
		[Contents].[Config],
		[Contents].[DTS],
		[Contents].[UserID],
		[Contents].[LastChanged]
	FROM [Contents]
	WHERE
		[Contents].[FormatID]=@FormatID


	SELECT
		[DocVersions].[VersionID],
		[DocVersions].[FolderID],
		[DocVersions].[VersionType],
		[DocVersions].[Name],
		[DocVersions].[Title],
		[DocVersions].[ItemID],
		[DocVersions].[FormatID],
		[DocVersions].[Config],
		[DocVersions].[DTS],
		[DocVersions].[UserID],
		[DocVersions].[LastChanged],
		[Folders].[ParentID] [Folder_ParentID],
		[Folders].[DBID] [Folder_DBID],
		[Folders].[Name] [Folder_Name],
		[Folders].[Title] [Folder_Title],
		[Folders].[ShortName] [Folder_ShortName],
		[Folders].[FormatID] [Folder_FormatID],
		[Folders].[ManualOrder] [Folder_ManualOrder],
		[Folders].[Config] [Folder_Config],
		[Folders].[DTS] [Folder_DTS],
		[Folders].[UsrID] [Folder_UsrID]
	FROM [DocVersions]
		JOIN [Folders]  ON
			[Folders].[FolderID]=[DocVersions].[FolderID]
	WHERE
		[DocVersions].[FormatID]=@FormatID


	SELECT
		[Folders].[FolderID],
		[Folders].[ParentID],
		[Folders].[DBID],
		[Folders].[Name],
		[Folders].[Title],
		[Folders].[ShortName],
		[Folders].[FormatID],
		[Folders].[ManualOrder],
		[Folders].[Config],
		[Folders].[DTS],
		[Folders].[UsrID],
		[Folders].[LastChanged],
		[Connections].[Name] [Connection_Name],
		[Connections].[Title] [Connection_Title],
		[Connections].[ConnectionString] [Connection_ConnectionString],
		[Connections].[ServerType] [Connection_ServerType],
		[Connections].[Config] [Connection_Config],
		[Connections].[DTS] [Connection_DTS],
		[Connections].[UsrID] [Connection_UsrID]
	FROM [Folders]
		JOIN [Connections]  ON
			[Connections].[DBID]=[Folders].[DBID]
	WHERE
		[Folders].[FormatID]=@FormatID

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

/****** Object:  StoredProcedure [getFormatByParentID_Name] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getFormatByParentID_Name]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getFormatByParentID_Name];
GO
	
CREATE PROCEDURE [dbo].[getFormatByParentID_Name]
(
	@ParentID int,
	@Name nvarchar(20)
)
AS
	SELECT
		[FormatID],
		[ParentID],
		[Name],
		[Description],
		[Data],
		[GenMac],
		[DTS],
		[UserID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Contents] WHERE [Contents].[FormatID]=[Formats].[FormatID]) [ContentCount],
		(SELECT COUNT(*) FROM [DocVersions] WHERE [DocVersions].[FormatID]=[Formats].[FormatID]) [DocVersionCount],
		(SELECT COUNT(*) FROM [Folders] WHERE [Folders].[FormatID]=[Formats].[FormatID]) [FolderCount],
		(SELECT COUNT(*) FROM [Formats] [Children] WHERE [Children].[ParentID]=[Formats].[FormatID]) [ChildCount]
	FROM [Formats]
	WHERE [ParentID]=@ParentID AND [Name]=@Name
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getFormatByParentID_Name Succeeded'
ELSE PRINT 'Procedure Creation: getFormatByParentID_Name Error on Creation'
GO

/****** Object:  StoredProcedure [getParentFormat] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getParentFormat]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getParentFormat];
GO
	
CREATE PROCEDURE [dbo].[getParentFormat]
(
	@ParentID int
)
AS
	SELECT
		[FormatID],
		[ParentID],
		[Name],
		[Description],
		[Data],
		[GenMac],
		[DTS],
		[UserID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Contents] WHERE [Contents].[FormatID]=[Formats].[FormatID]) [ContentCount],
		(SELECT COUNT(*) FROM [DocVersions] WHERE [DocVersions].[FormatID]=[Formats].[FormatID]) [DocVersionCount],
		(SELECT COUNT(*) FROM [Folders] WHERE [Folders].[FormatID]=[Formats].[FormatID]) [FolderCount],
		(SELECT COUNT(*) FROM [Formats] [Children] WHERE [Children].[ParentID]=[Formats].[FormatID]) [ChildCount]
	FROM [Formats]
	WHERE
		[FormatID]=@ParentID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getParentFormat Succeeded'
ELSE PRINT 'Procedure Creation: getParentFormat Error on Creation'
GO

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

(
	@FormatID int
)
	AS
	SELECT COUNT(*)
	FROM [Formats] WHERE [FormatID]=@FormatID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsFormat Succeeded'
ELSE PRINT 'Procedure Creation: existsFormat Error on Creation'
GO

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

(
	@ParentID int
)
AS
	SELECT
		[FormatID],
		[ParentID],
		[Name],
		[Description],
		[Data],
		[GenMac],
		[DTS],
		[UserID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Contents] WHERE [Contents].[FormatID]=[Formats].[FormatID]) [ContentCount],
		(SELECT COUNT(*) FROM [DocVersions] WHERE [DocVersions].[FormatID]=[Formats].[FormatID]) [DocVersionCount],
		(SELECT COUNT(*) FROM [Folders] WHERE [Folders].[FormatID]=[Formats].[FormatID]) [FolderCount],
		(SELECT COUNT(*) FROM [Formats] [Children] WHERE [Children].[ParentID]=[Formats].[FormatID]) [ChildCount]
	FROM [Formats] WHERE [ParentID]=@ParentID AND [FormatID]<>@ParentID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getChildFormats Succeeded'
ELSE PRINT 'Procedure Creation: getChildFormats Error on Creation'
GO

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

AS
	SELECT
		[GID],
		[GroupName],
		[GroupType],
		[Config],
		[DTS],
		[UsrID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Assignments] WHERE [Assignments].[GID]=[Groups].[GID]) [AssignmentCount],
		(SELECT COUNT(*) FROM [Memberships] WHERE [Memberships].[GID]=[Groups].[GID]) [MembershipCount]
	FROM [Groups]
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getGroups Succeeded'
ELSE PRINT 'Procedure Creation: getGroups Error on Creation'
GO

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

(
	@GID int,
	@GroupName nvarchar(50),
	@GroupType int=null,
	@Config nvarchar(MAX)=null,
	@DTS datetime,
	@UsrID nvarchar(100),
	@LastChanged timestamp,
	@newLastChanged timestamp output
)
AS
	UPDATE [Groups]
		SET
			[GroupName]=@GroupName,
			[GroupType]=@GroupType,
			[Config]=@Config,
			[DTS]=@DTS,
			[UsrID]=@UsrID
		WHERE [GID]=@GID AND [LastChanged]=@LastChanged
	IF @@ROWCOUNT = 0
	BEGIN
		IF NOT exists(select * from [Groups] WHERE [GID]=@GID)
			RAISERROR('Group record has been deleted by another user', 16, 1)
		ELSE           	
			RAISERROR('Group has been edited by another user', 16, 1)            	
	END

		SELECT 		@newLastChanged=[LastChanged]	
		FROM [Groups] WHERE [GID]=@GID		

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

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

(
	@GroupName nvarchar(50),
	@GroupType int=null,
	@Config nvarchar(MAX)=null,
	@DTS datetime,
	@UsrID nvarchar(100),
	@newGID int output,
	@newLastChanged timestamp output
)
AS
	INSERT INTO [Groups]
	(
		[GroupName],
		[GroupType],
		[Config],
		[DTS],
		[UsrID]
	)
	VALUES
	(
		@GroupName,
		@GroupType,
		@Config,
		@DTS,
		@UsrID
	)
	SELECT @newGID= SCOPE_IDENTITY()
	SELECT 		@newLastChanged=[LastChanged]
		FROM [Groups] WHERE [GID]=@newGID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addGroup Succeeded'
ELSE PRINT 'Procedure Creation: addGroup Error on Creation'
GO

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

(
	@GID int
)
AS
	DELETE [Assignments]
		WHERE [GID]=@GID
	DELETE [Memberships]
		WHERE [GID]=@GID
	DELETE [Groups]
		WHERE [GID] = @GID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteGroup Succeeded'
ELSE PRINT 'Procedure Creation: deleteGroup Error on Creation'
GO

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

(
	@GID int
)
AS
	SELECT
		[GID],
		[GroupName],
		[GroupType],
		[Config],
		[DTS],
		[UsrID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Assignments] WHERE [Assignments].[GID]=[Groups].[GID]) [AssignmentCount],
		(SELECT COUNT(*) FROM [Memberships] WHERE [Memberships].[GID]=[Groups].[GID]) [MembershipCount]
	FROM [Groups]
	WHERE [GID]=@GID

	SELECT
		[Assignments].[AID],
		[Assignments].[GID],
		[Assignments].[RID],
		[Assignments].[FolderID],
		[Assignments].[StartDate],
		[Assignments].[EndDate],
		[Assignments].[DTS],
		[Assignments].[UsrID],
		[Assignments].[LastChanged],
		[Folders].[ParentID] [Folder_ParentID],
		[Folders].[DBID] [Folder_DBID],
		[Folders].[Name] [Folder_Name],
		[Folders].[Title] [Folder_Title],
		[Folders].[ShortName] [Folder_ShortName],
		[Folders].[FormatID] [Folder_FormatID],
		[Folders].[ManualOrder] [Folder_ManualOrder],
		[Folders].[Config] [Folder_Config],
		[Folders].[DTS] [Folder_DTS],
		[Folders].[UsrID] [Folder_UsrID],
		[Roles].[Name] [Role_Name],
		[Roles].[Title] [Role_Title],
		[Roles].[DTS] [Role_DTS],
		[Roles].[UsrID] [Role_UsrID]
	FROM [Assignments]
		JOIN [Folders]  ON
			[Folders].[FolderID]=[Assignments].[FolderID]
		JOIN [Roles]  ON
			[Roles].[RID]=[Assignments].[RID]
	WHERE
		[Assignments].[GID]=@GID


	SELECT
		[Memberships].[UGID],
		[Memberships].[UID],
		[Memberships].[GID],
		[Memberships].[StartDate],
		[Memberships].[EndDate],
		[Memberships].[Config],
		[Memberships].[DTS],
		[Memberships].[UsrID],
		[Memberships].[LastChanged],
		[Users].[UserID] [User_UserID],
		[Users].[FirstName] [User_FirstName],
		[Users].[MiddleName] [User_MiddleName],
		[Users].[LastName] [User_LastName],
		[Users].[Suffix] [User_Suffix],
		[Users].[CourtesyTitle] [User_CourtesyTitle],
		[Users].[PhoneNumber] [User_PhoneNumber],
		[Users].[CFGName] [User_CFGName],
		[Users].[UserLogin] [User_UserLogin],
		[Users].[UserName] [User_UserName],
		[Users].[Config] [User_Config],
		[Users].[DTS] [User_DTS],
		[Users].[UsrID] [User_UsrID]
	FROM [Memberships]
		JOIN [Users]  ON
			[Users].[UID]=[Memberships].[UID]
	WHERE
		[Memberships].[GID]=@GID

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

/****** Object:  StoredProcedure [getGroupByGroupName] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getGroupByGroupName]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getGroupByGroupName];
GO
	
CREATE PROCEDURE [dbo].[getGroupByGroupName]
(
	@GroupName nvarchar(50)
)
AS
	SELECT
		[GID],
		[GroupName],
		[GroupType],
		[Config],
		[DTS],
		[UsrID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Assignments] WHERE [Assignments].[GID]=[Groups].[GID]) [AssignmentCount],
		(SELECT COUNT(*) FROM [Memberships] WHERE [Memberships].[GID]=[Groups].[GID]) [MembershipCount]
	FROM [Groups]
	WHERE [GroupName]=@GroupName
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getGroupByGroupName Succeeded'
ELSE PRINT 'Procedure Creation: getGroupByGroupName Error on Creation'
GO

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

(
	@GID int
)
	AS
	SELECT COUNT(*)
	FROM [Groups] WHERE [GID]=@GID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsGroup Succeeded'
ELSE PRINT 'Procedure Creation: existsGroup Error on Creation'
GO

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

AS
	SELECT
		[ItemID],
		[PreviousID],
		[ContentID],
		[DTS],
		[UserID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Annotations] WHERE [Annotations].[ItemID]=[Items].[ItemID]) [AnnotationCount],
		(SELECT COUNT(*) FROM [DocVersions] WHERE [DocVersions].[ItemID]=[Items].[ItemID]) [DocVersionCount],
		(SELECT COUNT(*) FROM [Items] [Next] WHERE [Next].[PreviousID]=[Items].[ItemID]) [NextCount],
		(SELECT COUNT(*) FROM [Parts] WHERE [Parts].[ItemID]=[Items].[ItemID]) [PartCount],
		(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[RangeID]=[Items].[ItemID]) [Transition_RangeIDCount],
		(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[ToID]=[Items].[ItemID]) [Transition_ToIDCount]
	FROM [Items]
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getItems Succeeded'
ELSE PRINT 'Procedure Creation: getItems Error on Creation'
GO

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

(
	@ItemID int,
	@PreviousID int=null,
	@ContentID int,
	@DTS datetime,
	@UserID nvarchar(100),
	@LastChanged timestamp,
	@newLastChanged timestamp output
)
AS
	UPDATE [Items]
		SET
			[PreviousID]=@PreviousID,
			[ContentID]=@ContentID,
			[DTS]=@DTS,
			[UserID]=@UserID
		WHERE [ItemID]=@ItemID AND [LastChanged]=@LastChanged
	IF @@ROWCOUNT = 0
	BEGIN
		IF NOT exists(select * from [Items] WHERE [ItemID]=@ItemID)
			RAISERROR('Item record has been deleted by another user', 16, 1)
		ELSE           	
			RAISERROR('Item has been edited by another user', 16, 1)            	
	END

		SELECT 		@newLastChanged=[LastChanged]	
		FROM [Items] WHERE [ItemID]=@ItemID		

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

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

(
	@PreviousID int=null,
	@ContentID int,
	@DTS datetime,
	@UserID nvarchar(100),
	@newItemID int output,
	@newLastChanged timestamp output
)
AS
	INSERT INTO [Items]
	(
		[PreviousID],
		[ContentID],
		[DTS],
		[UserID]
	)
	VALUES
	(
		@PreviousID,
		@ContentID,
		@DTS,
		@UserID
	)
	SELECT @newItemID= SCOPE_IDENTITY()
	SELECT 		@newLastChanged=[LastChanged]
		FROM [Items] WHERE [ItemID]=@newItemID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addItem Succeeded'
ELSE PRINT 'Procedure Creation: addItem Error on Creation'
GO

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

(
	@ItemID int
)
AS
	DELETE [Annotations]
		WHERE [ItemID]=@ItemID
	DELETE [DocVersions]
		WHERE [ItemID]=@ItemID
	DELETE [Parts]
		WHERE [ItemID]=@ItemID
	DELETE [Transitions]
		WHERE [RangeID]=@ItemID
	DELETE [Transitions]
		WHERE [ToID]=@ItemID
	DELETE [Items]
		WHERE [ItemID] = @ItemID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteItem Succeeded'
ELSE PRINT 'Procedure Creation: deleteItem Error on Creation'
GO

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

(
	@ItemID int
)
AS
	SELECT
		[ItemID],
		[PreviousID],
		[ContentID],
		[DTS],
		[UserID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Annotations] WHERE [Annotations].[ItemID]=[Items].[ItemID]) [AnnotationCount],
		(SELECT COUNT(*) FROM [DocVersions] WHERE [DocVersions].[ItemID]=[Items].[ItemID]) [DocVersionCount],
		(SELECT COUNT(*) FROM [Items] [Next] WHERE [Next].[PreviousID]=[Items].[ItemID]) [NextCount],
		(SELECT COUNT(*) FROM [Parts] WHERE [Parts].[ItemID]=[Items].[ItemID]) [PartCount],
		(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[RangeID]=[Items].[ItemID]) [Transition_RangeIDCount],
		(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[ToID]=[Items].[ItemID]) [Transition_ToIDCount]
	FROM [Items]
	WHERE [ItemID]=@ItemID

	SELECT
		[Annotations].[AnnotationID],
		[Annotations].[ItemID],
		[Annotations].[TypeID],
		[Annotations].[RtfText],
		[Annotations].[SearchText],
		[Annotations].[Config],
		[Annotations].[DTS],
		[Annotations].[UserID],
		[Annotations].[LastChanged],
		[AnnotationTypes].[Name] [AnnotationType_Name],
		[AnnotationTypes].[Config] [AnnotationType_Config],
		[AnnotationTypes].[DTS] [AnnotationType_DTS],
		[AnnotationTypes].[UserID] [AnnotationType_UserID]
	FROM [Annotations]
		JOIN [AnnotationTypes]  ON
			[AnnotationTypes].[TypeID]=[Annotations].[TypeID]
	WHERE
		[Annotations].[ItemID]=@ItemID


	SELECT
		[DocVersions].[VersionID],
		[DocVersions].[FolderID],
		[DocVersions].[VersionType],
		[DocVersions].[Name],
		[DocVersions].[Title],
		[DocVersions].[ItemID],
		[DocVersions].[FormatID],
		[DocVersions].[Config],
		[DocVersions].[DTS],
		[DocVersions].[UserID],
		[DocVersions].[LastChanged],
		[Folders].[ParentID] [Folder_ParentID],
		[Folders].[DBID] [Folder_DBID],
		[Folders].[Name] [Folder_Name],
		[Folders].[Title] [Folder_Title],
		[Folders].[ShortName] [Folder_ShortName],
		[Folders].[FormatID] [Folder_FormatID],
		[Folders].[ManualOrder] [Folder_ManualOrder],
		[Folders].[Config] [Folder_Config],
		[Folders].[DTS] [Folder_DTS],
		[Folders].[UsrID] [Folder_UsrID]
	FROM [DocVersions]
		JOIN [Folders]  ON
			[Folders].[FolderID]=[DocVersions].[FolderID]
	WHERE
		[DocVersions].[ItemID]=@ItemID


	SELECT
		[Parts].[ContentID],
		[Parts].[FromType],
		[Parts].[ItemID],
		[Parts].[DTS],
		[Parts].[UserID],
		[Parts].[LastChanged],
		[Contents].[Number] [Content_Number],
		[Contents].[Text] [Content_Text],
		[Contents].[Type] [Content_Type],
		[Contents].[FormatID] [Content_FormatID],
		[Contents].[Config] [Content_Config],
		[Contents].[DTS] [Content_DTS],
		[Contents].[UserID] [Content_UserID]
	FROM [Parts]
		JOIN [Contents]  ON
			[Contents].[ContentID]=[Parts].[ContentID]
	WHERE
		[Parts].[ItemID]=@ItemID


	SELECT
		[Transitions].[TransitionID],
		[Transitions].[FromID],
		[Transitions].[ToID],
		[Transitions].[RangeID],
		[Transitions].[TranType],
		[Transitions].[Config],
		[Transitions].[DTS],
		[Transitions].[UserID],
		[Transitions].[LastChanged],
		[Contents].[Number] [Content_Number],
		[Contents].[Text] [Content_Text],
		[Contents].[Type] [Content_Type],
		[Contents].[FormatID] [Content_FormatID],
		[Contents].[Config] [Content_Config],
		[Contents].[DTS] [Content_DTS],
		[Contents].[UserID] [Content_UserID]
	FROM [Transitions]
		JOIN [Contents]  ON
			[Contents].[ContentID]=[Transitions].[FromID]
	WHERE
		[Transitions].[RangeID]=@ItemID


	SELECT
		[Transitions].[TransitionID],
		[Transitions].[FromID],
		[Transitions].[ToID],
		[Transitions].[RangeID],
		[Transitions].[TranType],
		[Transitions].[Config],
		[Transitions].[DTS],
		[Transitions].[UserID],
		[Transitions].[LastChanged],
		[Contents].[Number] [Content_Number],
		[Contents].[Text] [Content_Text],
		[Contents].[Type] [Content_Type],
		[Contents].[FormatID] [Content_FormatID],
		[Contents].[Config] [Content_Config],
		[Contents].[DTS] [Content_DTS],
		[Contents].[UserID] [Content_UserID]
	FROM [Transitions]
		JOIN [Contents]  ON
			[Contents].[ContentID]=[Transitions].[FromID]
	WHERE
		[Transitions].[ToID]=@ItemID

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

/****** Object:  StoredProcedure [getPreviousItem] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getPreviousItem]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getPreviousItem];
GO
	
CREATE PROCEDURE [dbo].[getPreviousItem]
(
	@PreviousID int
)
AS
	SELECT
		[ItemID],
		[PreviousID],
		[ContentID],
		[DTS],
		[UserID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Annotations] WHERE [Annotations].[ItemID]=[Items].[ItemID]) [AnnotationCount],
		(SELECT COUNT(*) FROM [DocVersions] WHERE [DocVersions].[ItemID]=[Items].[ItemID]) [DocVersionCount],
		(SELECT COUNT(*) FROM [Items] [Next] WHERE [Next].[PreviousID]=[Items].[ItemID]) [NextCount],
		(SELECT COUNT(*) FROM [Parts] WHERE [Parts].[ItemID]=[Items].[ItemID]) [PartCount],
		(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[RangeID]=[Items].[ItemID]) [Transition_RangeIDCount],
		(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[ToID]=[Items].[ItemID]) [Transition_ToIDCount]
	FROM [Items]
	WHERE
		[ItemID]=@PreviousID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getPreviousItem Succeeded'
ELSE PRINT 'Procedure Creation: getPreviousItem Error on Creation'
GO

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

(
	@ItemID int
)
	AS
	SELECT COUNT(*)
	FROM [Items] WHERE [ItemID]=@ItemID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsItem Succeeded'
ELSE PRINT 'Procedure Creation: existsItem Error on Creation'
GO

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

(
	@PreviousID int
)
AS
	SELECT
		[ItemID],
		[PreviousID],
		[ContentID],
		[DTS],
		[UserID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Annotations] WHERE [Annotations].[ItemID]=[Items].[ItemID]) [AnnotationCount],
		(SELECT COUNT(*) FROM [DocVersions] WHERE [DocVersions].[ItemID]=[Items].[ItemID]) [DocVersionCount],
		(SELECT COUNT(*) FROM [Items] [Next] WHERE [Next].[PreviousID]=[Items].[ItemID]) [NextCount],
		(SELECT COUNT(*) FROM [Parts] WHERE [Parts].[ItemID]=[Items].[ItemID]) [PartCount],
		(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[RangeID]=[Items].[ItemID]) [Transition_RangeIDCount],
		(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[ToID]=[Items].[ItemID]) [Transition_ToIDCount]
	FROM [Items] WHERE [PreviousID]=@PreviousID AND [ItemID]<>@PreviousID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getNextItems Succeeded'
ELSE PRINT 'Procedure Creation: getNextItems Error on Creation'
GO

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

(
	@ContentID int
)
AS

	SELECT
		[Items].[ItemID],
		[Items].[PreviousID],
		[Items].[ContentID],
		[Items].[DTS],
		[Items].[UserID],
		[Items].[LastChanged],
		(SELECT COUNT(*) FROM [Annotations] WHERE [Annotations].[ItemID]=[Items].[ItemID]) [AnnotationCount],
		(SELECT COUNT(*) FROM [DocVersions] WHERE [DocVersions].[ItemID]=[Items].[ItemID]) [DocVersionCount],
		(SELECT COUNT(*) FROM [Items] [Next] WHERE [Next].[PreviousID]=[Items].[ItemID]) [NextCount],
		(SELECT COUNT(*) FROM [Parts] WHERE [Parts].[ItemID]=[Items].[ItemID]) [PartCount],
		(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[RangeID]=[Items].[ItemID]) [Transition_RangeIDCount],
		(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[ToID]=[Items].[ItemID]) [Transition_ToIDCount]
	FROM [Items]
	WHERE
		[Items].[ContentID]=@ContentID

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

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

AS
	SELECT
		[UGID],
		[UID],
		[GID],
		[StartDate],
		[EndDate],
		[Config],
		[DTS],
		[UsrID],
		[LastChanged]
	FROM [Memberships]
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getMemberships Succeeded'
ELSE PRINT 'Procedure Creation: getMemberships Error on Creation'
GO

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

(
	@UGID int,
	@UID int,
	@GID int,
	@StartDate datetime,
	@EndDate datetime=null,
	@Config nvarchar(MAX)=null,
	@DTS datetime,
	@UsrID nvarchar(100),
	@LastChanged timestamp,
	@newLastChanged timestamp output
)
AS
	UPDATE [Memberships]
		SET
			[UID]=@UID,
			[GID]=@GID,
			[StartDate]=@StartDate,
			[EndDate]=@EndDate,
			[Config]=@Config,
			[DTS]=@DTS,
			[UsrID]=@UsrID
		WHERE [UGID]=@UGID AND [LastChanged]=@LastChanged
	IF @@ROWCOUNT = 0
	BEGIN
		IF NOT exists(select * from [Memberships] WHERE [UGID]=@UGID)
			RAISERROR('Membership record has been deleted by another user', 16, 1)
		ELSE           	
			RAISERROR('Membership has been edited by another user', 16, 1)            	
	END

		SELECT 		@newLastChanged=[LastChanged]	
		FROM [Memberships] WHERE [UGID]=@UGID		

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

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

(
	@UID int,
	@GID int,
	@StartDate datetime,
	@EndDate datetime=null,
	@Config nvarchar(MAX)=null,
	@DTS datetime,
	@UsrID nvarchar(100),
	@newUGID int output,
	@newLastChanged timestamp output
)
AS
	INSERT INTO [Memberships]
	(
		[UID],
		[GID],
		[StartDate],
		[EndDate],
		[Config],
		[DTS],
		[UsrID]
	)
	VALUES
	(
		@UID,
		@GID,
		@StartDate,
		@EndDate,
		@Config,
		@DTS,
		@UsrID
	)
	SELECT @newUGID= SCOPE_IDENTITY()
	SELECT 		@newLastChanged=[LastChanged]
		FROM [Memberships] WHERE [UGID]=@newUGID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addMembership Succeeded'
ELSE PRINT 'Procedure Creation: addMembership Error on Creation'
GO

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

(
	@UGID int
)
AS
	DELETE [Memberships]
		WHERE [UGID] = @UGID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteMembership Succeeded'
ELSE PRINT 'Procedure Creation: deleteMembership Error on Creation'
GO

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

(
	@UGID int
)
AS
	SELECT
		[UGID],
		[UID],
		[GID],
		[StartDate],
		[EndDate],
		[Config],
		[DTS],
		[UsrID],
		[LastChanged]
	FROM [Memberships]
	WHERE [UGID]=@UGID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getMembership Succeeded'
ELSE PRINT 'Procedure Creation: getMembership Error on Creation'
GO

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

(
	@UGID int
)
	AS
	SELECT COUNT(*)
	FROM [Memberships] WHERE [UGID]=@UGID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsMembership Succeeded'
ELSE PRINT 'Procedure Creation: existsMembership Error on Creation'
GO

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

(
	@GID int
)
AS

	SELECT
		[Memberships].[UGID],
		[Memberships].[UID],
		[Memberships].[GID],
		[Memberships].[StartDate],
		[Memberships].[EndDate],
		[Memberships].[Config],
		[Memberships].[DTS],
		[Memberships].[UsrID],
		[Memberships].[LastChanged],
		[Users].[UserID] [User_UserID],
		[Users].[FirstName] [User_FirstName],
		[Users].[MiddleName] [User_MiddleName],
		[Users].[LastName] [User_LastName],
		[Users].[Suffix] [User_Suffix],
		[Users].[CourtesyTitle] [User_CourtesyTitle],
		[Users].[PhoneNumber] [User_PhoneNumber],
		[Users].[CFGName] [User_CFGName],
		[Users].[UserLogin] [User_UserLogin],
		[Users].[UserName] [User_UserName],
		[Users].[Config] [User_Config],
		[Users].[DTS] [User_DTS],
		[Users].[UsrID] [User_UsrID]
	FROM [Memberships]
		JOIN [Users]  ON
			[Users].[UID]=[Memberships].[UID]
	WHERE
		[Memberships].[GID]=@GID

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

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

(
	@UID int
)
AS

	SELECT
		[Memberships].[UGID],
		[Memberships].[UID],
		[Memberships].[GID],
		[Memberships].[StartDate],
		[Memberships].[EndDate],
		[Memberships].[Config],
		[Memberships].[DTS],
		[Memberships].[UsrID],
		[Memberships].[LastChanged],
		[Groups].[GroupName] [Group_GroupName],
		[Groups].[GroupType] [Group_GroupType],
		[Groups].[Config] [Group_Config],
		[Groups].[DTS] [Group_DTS],
		[Groups].[UsrID] [Group_UsrID]
	FROM [Memberships]
		JOIN [Groups]  ON
			[Groups].[GID]=[Memberships].[GID]
	WHERE
		[Memberships].[UID]=@UID

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

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

AS
	SELECT
		[ContentID],
		[FromType],
		[ItemID],
		[DTS],
		[UserID],
		[LastChanged]
	FROM [Parts]
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getParts Succeeded'
ELSE PRINT 'Procedure Creation: getParts Error on Creation'
GO

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

(
	@ContentID int,
	@FromType int,
	@ItemID int,
	@DTS datetime,
	@UserID nvarchar(100),
	@LastChanged timestamp,
	@newLastChanged timestamp output
)
AS
	UPDATE [Parts]
		SET
			[ItemID]=@ItemID,
			[DTS]=@DTS,
			[UserID]=@UserID
		WHERE [ContentID]=@ContentID AND [FromType]=@FromType AND [LastChanged]=@LastChanged
	IF @@ROWCOUNT = 0
	BEGIN
		IF NOT exists(select * from [Parts] WHERE [ContentID]=@ContentID AND [FromType]=@FromType)
			RAISERROR('Part record has been deleted by another user', 16, 1)
		ELSE           	
			RAISERROR('Part has been edited by another user', 16, 1)            	
	END

		SELECT 		@newLastChanged=[LastChanged]	
		FROM [Parts] WHERE [ContentID]=@ContentID AND [FromType]=@FromType		

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

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

(
	@ContentID int,
	@FromType int,
	@ItemID int,
	@DTS datetime,
	@UserID nvarchar(100),
	@newLastChanged timestamp output
)
AS
	INSERT INTO [Parts]
	(
		[ContentID],
		[FromType],
		[ItemID],
		[DTS],
		[UserID]
	)
	VALUES
	(
		@ContentID,
		@FromType,
		@ItemID,
		@DTS,
		@UserID
	)

	SELECT 		@newLastChanged=[LastChanged]
		FROM [Parts] WHERE [ContentID]=@ContentID AND [FromType]=@FromType
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addPart Succeeded'
ELSE PRINT 'Procedure Creation: addPart Error on Creation'
GO

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

(
	@ContentID int,
	@FromType int
)
AS
	DELETE [Parts]
		WHERE [ContentID] = @ContentID AND [FromType] = @FromType
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deletePart Succeeded'
ELSE PRINT 'Procedure Creation: deletePart Error on Creation'
GO

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

(
	@ContentID int,
	@FromType int
)
AS
	SELECT
		[ContentID],
		[FromType],
		[ItemID],
		[DTS],
		[UserID],
		[LastChanged]
	FROM [Parts]
	WHERE [ContentID]=@ContentID AND [FromType]=@FromType
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getPart Succeeded'
ELSE PRINT 'Procedure Creation: getPart Error on Creation'
GO

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

(
	@ContentID int,
	@FromType int
)
	AS
	SELECT COUNT(*)
	FROM [Parts] WHERE [ContentID]=@ContentID AND [FromType]=@FromType
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsPart Succeeded'
ELSE PRINT 'Procedure Creation: existsPart Error on Creation'
GO

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

(
	@ContentID int
)
AS

	SELECT
		[Parts].[ContentID],
		[Parts].[FromType],
		[Parts].[ItemID],
		[Parts].[DTS],
		[Parts].[UserID],
		[Parts].[LastChanged],
		[Items].[PreviousID] [Item_PreviousID],
		[Items].[ContentID] [Item_ContentID],
		[Items].[DTS] [Item_DTS],
		[Items].[UserID] [Item_UserID]
	FROM [Parts]
		JOIN [Items]  ON
			[Items].[ItemID]=[Parts].[ItemID]
	WHERE
		[Parts].[ContentID]=@ContentID

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

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

(
	@ItemID int
)
AS

	SELECT
		[Parts].[ContentID],
		[Parts].[FromType],
		[Parts].[ItemID],
		[Parts].[DTS],
		[Parts].[UserID],
		[Parts].[LastChanged],
		[Contents].[Number] [Content_Number],
		[Contents].[Text] [Content_Text],
		[Contents].[Type] [Content_Type],
		[Contents].[FormatID] [Content_FormatID],
		[Contents].[Config] [Content_Config],
		[Contents].[DTS] [Content_DTS],
		[Contents].[UserID] [Content_UserID]
	FROM [Parts]
		JOIN [Contents]  ON
			[Contents].[ContentID]=[Parts].[ContentID]
	WHERE
		[Parts].[ItemID]=@ItemID

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

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

AS
	SELECT
		[PID],
		[RID],
		[PermLevel],
		[VersionType],
		[PermValue],
		[PermAD],
		[StartDate],
		[EndDate],
		[Config],
		[DTS],
		[UsrID],
		[LastChanged]
	FROM [Permissions]
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getPermissions Succeeded'
ELSE PRINT 'Procedure Creation: getPermissions Error on Creation'
GO

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

(
	@PID int,
	@RID int,
	@PermLevel int,
	@VersionType int,
	@PermValue int,
	@PermAD int,
	@StartDate datetime,
	@EndDate datetime=null,
	@Config nvarchar(MAX)=null,
	@DTS datetime,
	@UsrID nvarchar(100),
	@LastChanged timestamp,
	@newLastChanged timestamp output
)
AS
	UPDATE [Permissions]
		SET
			[RID]=@RID,
			[PermLevel]=@PermLevel,
			[VersionType]=@VersionType,
			[PermValue]=@PermValue,
			[PermAD]=@PermAD,
			[StartDate]=@StartDate,
			[EndDate]=@EndDate,
			[Config]=@Config,
			[DTS]=@DTS,
			[UsrID]=@UsrID
		WHERE [PID]=@PID AND [LastChanged]=@LastChanged
	IF @@ROWCOUNT = 0
	BEGIN
		IF NOT exists(select * from [Permissions] WHERE [PID]=@PID)
			RAISERROR('Permission record has been deleted by another user', 16, 1)
		ELSE           	
			RAISERROR('Permission has been edited by another user', 16, 1)            	
	END

		SELECT 		@newLastChanged=[LastChanged]	
		FROM [Permissions] WHERE [PID]=@PID		

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

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

(
	@RID int,
	@PermLevel int,
	@VersionType int,
	@PermValue int,
	@PermAD int,
	@StartDate datetime,
	@EndDate datetime=null,
	@Config nvarchar(MAX)=null,
	@DTS datetime,
	@UsrID nvarchar(100),
	@newPID int output,
	@newLastChanged timestamp output
)
AS
	INSERT INTO [Permissions]
	(
		[RID],
		[PermLevel],
		[VersionType],
		[PermValue],
		[PermAD],
		[StartDate],
		[EndDate],
		[Config],
		[DTS],
		[UsrID]
	)
	VALUES
	(
		@RID,
		@PermLevel,
		@VersionType,
		@PermValue,
		@PermAD,
		@StartDate,
		@EndDate,
		@Config,
		@DTS,
		@UsrID
	)
	SELECT @newPID= SCOPE_IDENTITY()
	SELECT 		@newLastChanged=[LastChanged]
		FROM [Permissions] WHERE [PID]=@newPID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addPermission Succeeded'
ELSE PRINT 'Procedure Creation: addPermission Error on Creation'
GO

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

(
	@PID int
)
AS
	DELETE [Permissions]
		WHERE [PID] = @PID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deletePermission Succeeded'
ELSE PRINT 'Procedure Creation: deletePermission Error on Creation'
GO

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

(
	@PID int
)
AS
	SELECT
		[PID],
		[RID],
		[PermLevel],
		[VersionType],
		[PermValue],
		[PermAD],
		[StartDate],
		[EndDate],
		[Config],
		[DTS],
		[UsrID],
		[LastChanged]
	FROM [Permissions]
	WHERE [PID]=@PID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getPermission Succeeded'
ELSE PRINT 'Procedure Creation: getPermission Error on Creation'
GO

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

(
	@PID int
)
	AS
	SELECT COUNT(*)
	FROM [Permissions] WHERE [PID]=@PID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsPermission Succeeded'
ELSE PRINT 'Procedure Creation: existsPermission Error on Creation'
GO

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

(
	@RID int
)
AS

	SELECT
		[Permissions].[PID],
		[Permissions].[RID],
		[Permissions].[PermLevel],
		[Permissions].[VersionType],
		[Permissions].[PermValue],
		[Permissions].[PermAD],
		[Permissions].[StartDate],
		[Permissions].[EndDate],
		[Permissions].[Config],
		[Permissions].[DTS],
		[Permissions].[UsrID],
		[Permissions].[LastChanged]
	FROM [Permissions]
	WHERE
		[Permissions].[RID]=@RID

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

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

AS
	SELECT
		[RODbID],
		[ROName],
		[FolderPath],
		[DBConnectionString],
		[Config],
		[DTS],
		[UserID],
		[LastChanged],
		(SELECT COUNT(*) FROM [ROFsts] WHERE [ROFsts].[RODbID]=[RODbs].[RODbID]) [ROFstCount],
		(SELECT COUNT(*) FROM [ROImages] WHERE [ROImages].[RODbID]=[RODbs].[RODbID]) [ROImageCount],
		(SELECT COUNT(*) FROM [RoUsages] WHERE [RoUsages].[RODbID]=[RODbs].[RODbID]) [RoUsageCount]
	FROM [RODbs]
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getRODbs Succeeded'
ELSE PRINT 'Procedure Creation: getRODbs Error on Creation'
GO

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

(
	@RODbID int,
	@ROName nvarchar(255),
	@FolderPath nvarchar(255),
	@DBConnectionString nvarchar(MAX),
	@Config nvarchar(MAX)=null,
	@DTS datetime,
	@UserID nvarchar(100),
	@LastChanged timestamp,
	@newLastChanged timestamp output
)
AS
	UPDATE [RODbs]
		SET
			[ROName]=@ROName,
			[FolderPath]=@FolderPath,
			[DBConnectionString]=@DBConnectionString,
			[Config]=@Config,
			[DTS]=@DTS,
			[UserID]=@UserID
		WHERE [RODbID]=@RODbID AND [LastChanged]=@LastChanged
	IF @@ROWCOUNT = 0
	BEGIN
		IF NOT exists(select * from [RODbs] WHERE [RODbID]=@RODbID)
			RAISERROR('RODb record has been deleted by another user', 16, 1)
		ELSE           	
			RAISERROR('RODb has been edited by another user', 16, 1)            	
	END

		SELECT 		@newLastChanged=[LastChanged]	
		FROM [RODbs] WHERE [RODbID]=@RODbID		

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

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

(
	@ROName nvarchar(255),
	@FolderPath nvarchar(255),
	@DBConnectionString nvarchar(MAX),
	@Config nvarchar(MAX)=null,
	@DTS datetime,
	@UserID nvarchar(100),
	@newRODbID int output,
	@newLastChanged timestamp output
)
AS
	INSERT INTO [RODbs]
	(
		[ROName],
		[FolderPath],
		[DBConnectionString],
		[Config],
		[DTS],
		[UserID]
	)
	VALUES
	(
		@ROName,
		@FolderPath,
		@DBConnectionString,
		@Config,
		@DTS,
		@UserID
	)
	SELECT @newRODbID= SCOPE_IDENTITY()
	SELECT 		@newLastChanged=[LastChanged]
		FROM [RODbs] WHERE [RODbID]=@newRODbID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addRODb Succeeded'
ELSE PRINT 'Procedure Creation: addRODb Error on Creation'
GO

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

(
	@RODbID int
)
AS
	DELETE [ROFsts]
		WHERE [RODbID]=@RODbID
	DELETE [ROImages]
		WHERE [RODbID]=@RODbID
	DELETE [RoUsages]
		WHERE [RODbID]=@RODbID
	DELETE [RODbs]
		WHERE [RODbID] = @RODbID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteRODb Succeeded'
ELSE PRINT 'Procedure Creation: deleteRODb Error on Creation'
GO

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

(
	@RODbID int
)
AS
	SELECT
		[RODbID],
		[ROName],
		[FolderPath],
		[DBConnectionString],
		[Config],
		[DTS],
		[UserID],
		[LastChanged],
		(SELECT COUNT(*) FROM [ROFsts] WHERE [ROFsts].[RODbID]=[RODbs].[RODbID]) [ROFstCount],
		(SELECT COUNT(*) FROM [ROImages] WHERE [ROImages].[RODbID]=[RODbs].[RODbID]) [ROImageCount],
		(SELECT COUNT(*) FROM [RoUsages] WHERE [RoUsages].[RODbID]=[RODbs].[RODbID]) [RoUsageCount]
	FROM [RODbs]
	WHERE [RODbID]=@RODbID

	SELECT
		[ROFsts].[ROFstID],
		[ROFsts].[RODbID],
		[ROFsts].[ROFST],
		[ROFsts].[Config],
		[ROFsts].[DTS],
		[ROFsts].[UserID],
		[ROFsts].[LastChanged]
	FROM [ROFsts]
	WHERE
		[ROFsts].[RODbID]=@RODbID


	SELECT
		[ROImages].[ImageID],
		[ROImages].[RODbID],
		[ROImages].[FileName],
		[ROImages].[Content],
		[ROImages].[Config],
		[ROImages].[DTS],
		[ROImages].[UserID],
		[ROImages].[LastChanged]
	FROM [ROImages]
	WHERE
		[ROImages].[RODbID]=@RODbID


	SELECT
		[RoUsages].[ROUsageID],
		[RoUsages].[ContentID],
		[RoUsages].[ROID],
		[RoUsages].[Config],
		[RoUsages].[DTS],
		[RoUsages].[UserID],
		[RoUsages].[LastChanged],
		[RoUsages].[RODbID],
		[Contents].[Number] [Content_Number],
		[Contents].[Text] [Content_Text],
		[Contents].[Type] [Content_Type],
		[Contents].[FormatID] [Content_FormatID],
		[Contents].[Config] [Content_Config],
		[Contents].[DTS] [Content_DTS],
		[Contents].[UserID] [Content_UserID]
	FROM [RoUsages]
		JOIN [Contents]  ON
			[Contents].[ContentID]=[RoUsages].[ContentID]
	WHERE
		[RoUsages].[RODbID]=@RODbID

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

/****** Object:  StoredProcedure [getRODbByFolderPath] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getRODbByFolderPath]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getRODbByFolderPath];
GO
	
CREATE PROCEDURE [dbo].[getRODbByFolderPath]
(
	@FolderPath nvarchar(255)
)
AS
	SELECT
		[RODbID],
		[ROName],
		[FolderPath],
		[DBConnectionString],
		[Config],
		[DTS],
		[UserID],
		[LastChanged],
		(SELECT COUNT(*) FROM [ROFsts] WHERE [ROFsts].[RODbID]=[RODbs].[RODbID]) [ROFstCount],
		(SELECT COUNT(*) FROM [ROImages] WHERE [ROImages].[RODbID]=[RODbs].[RODbID]) [ROImageCount],
		(SELECT COUNT(*) FROM [RoUsages] WHERE [RoUsages].[RODbID]=[RODbs].[RODbID]) [RoUsageCount]
	FROM [RODbs]
	WHERE [FolderPath]=@FolderPath
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getRODbByFolderPath Succeeded'
ELSE PRINT 'Procedure Creation: getRODbByFolderPath Error on Creation'
GO

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

(
	@RODbID int
)
	AS
	SELECT COUNT(*)
	FROM [RODbs] WHERE [RODbID]=@RODbID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsRODb Succeeded'
ELSE PRINT 'Procedure Creation: existsRODb Error on Creation'
GO

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

AS
	SELECT
		[ROFstID],
		[RODbID],
		[ROFST],
		[Config],
		[DTS],
		[UserID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Associations] WHERE [Associations].[ROFstID]=[ROFsts].[ROFstID]) [AssociationCount],
		(SELECT COUNT(*) FROM [Figures] WHERE [Figures].[ROFstID]=[ROFsts].[ROFstID]) [FigureCount]
	FROM [ROFsts]
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getROFsts Succeeded'
ELSE PRINT 'Procedure Creation: getROFsts Error on Creation'
GO

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

(
	@ROFstID int,
	@RODbID int,
	@ROFST varbinary(MAX),
	@Config nvarchar(MAX)=null,
	@DTS datetime,
	@UserID nvarchar(100),
	@LastChanged timestamp,
	@newLastChanged timestamp output
)
AS
	UPDATE [ROFsts]
		SET
			[RODbID]=@RODbID,
			[ROFST]=@ROFST,
			[Config]=@Config,
			[DTS]=@DTS,
			[UserID]=@UserID
		WHERE [ROFstID]=@ROFstID AND [LastChanged]=@LastChanged
	IF @@ROWCOUNT = 0
	BEGIN
		IF NOT exists(select * from [ROFsts] WHERE [ROFstID]=@ROFstID)
			RAISERROR('ROFst record has been deleted by another user', 16, 1)
		ELSE           	
			RAISERROR('ROFst has been edited by another user', 16, 1)            	
	END

		SELECT 		@newLastChanged=[LastChanged]	
		FROM [ROFsts] WHERE [ROFstID]=@ROFstID		

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

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

(
	@RODbID int,
	@ROFST varbinary(MAX),
	@Config nvarchar(MAX)=null,
	@DTS datetime,
	@UserID nvarchar(100),
	@newROFstID int output,
	@newLastChanged timestamp output
)
AS
	INSERT INTO [ROFsts]
	(
		[RODbID],
		[ROFST],
		[Config],
		[DTS],
		[UserID]
	)
	VALUES
	(
		@RODbID,
		@ROFST,
		@Config,
		@DTS,
		@UserID
	)
	SELECT @newROFstID= SCOPE_IDENTITY()
	SELECT 		@newLastChanged=[LastChanged]
		FROM [ROFsts] WHERE [ROFstID]=@newROFstID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addROFst Succeeded'
ELSE PRINT 'Procedure Creation: addROFst Error on Creation'
GO

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

(
	@ROFstID int
)
AS
	DELETE [Associations]
		WHERE [ROFstID]=@ROFstID
	DELETE [Figures]
		WHERE [ROFstID]=@ROFstID
	DELETE [ROFsts]
		WHERE [ROFstID] = @ROFstID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteROFst Succeeded'
ELSE PRINT 'Procedure Creation: deleteROFst Error on Creation'
GO

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

(
	@ROFstID int
)
AS
	SELECT
		[ROFstID],
		[RODbID],
		[ROFST],
		[Config],
		[DTS],
		[UserID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Associations] WHERE [Associations].[ROFstID]=[ROFsts].[ROFstID]) [AssociationCount],
		(SELECT COUNT(*) FROM [Figures] WHERE [Figures].[ROFstID]=[ROFsts].[ROFstID]) [FigureCount]
	FROM [ROFsts]
	WHERE [ROFstID]=@ROFstID

	SELECT
		[Associations].[AssociationID],
		[Associations].[VersionID],
		[Associations].[ROFstID],
		[Associations].[Config],
		[Associations].[DTS],
		[Associations].[UserID],
		[Associations].[LastChanged],
		[DocVersions].[FolderID] [DocVersion_FolderID],
		[DocVersions].[VersionType] [DocVersion_VersionType],
		[DocVersions].[Name] [DocVersion_Name],
		[DocVersions].[Title] [DocVersion_Title],
		[DocVersions].[ItemID] [DocVersion_ItemID],
		[DocVersions].[FormatID] [DocVersion_FormatID],
		[DocVersions].[Config] [DocVersion_Config],
		[DocVersions].[DTS] [DocVersion_DTS],
		[DocVersions].[UserID] [DocVersion_UserID]
	FROM [Associations]
		JOIN [DocVersions]  ON
			[DocVersions].[VersionID]=[Associations].[VersionID]
	WHERE
		[Associations].[ROFstID]=@ROFstID


	SELECT
		[Figures].[FigureID],
		[Figures].[ROFstID],
		[Figures].[ImageID],
		[Figures].[Config],
		[Figures].[DTS],
		[Figures].[UserID],
		[Figures].[LastChanged],
		[ROImages].[RODbID] [ROImage_RODbID],
		[ROImages].[FileName] [ROImage_FileName],
		[ROImages].[Content] [ROImage_Content],
		[ROImages].[Config] [ROImage_Config],
		[ROImages].[DTS] [ROImage_DTS],
		[ROImages].[UserID] [ROImage_UserID]
	FROM [Figures]
		JOIN [ROImages]  ON
			[ROImages].[ImageID]=[Figures].[ImageID]
	WHERE
		[Figures].[ROFstID]=@ROFstID

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

/****** Object:  StoredProcedure [getROFstByRODbID_DTS] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getROFstByRODbID_DTS]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getROFstByRODbID_DTS];
GO
	
CREATE PROCEDURE [dbo].[getROFstByRODbID_DTS]
(
	@RODbID int,
	@DTS datetime
)
AS
	SELECT
		[ROFstID],
		[RODbID],
		[ROFST],
		[Config],
		[DTS],
		[UserID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Associations] WHERE [Associations].[ROFstID]=[ROFsts].[ROFstID]) [AssociationCount],
		(SELECT COUNT(*) FROM [Figures] WHERE [Figures].[ROFstID]=[ROFsts].[ROFstID]) [FigureCount]
	FROM [ROFsts]
	WHERE [RODbID]=@RODbID AND [DTS]=@DTS
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getROFstByRODbID_DTS Succeeded'
ELSE PRINT 'Procedure Creation: getROFstByRODbID_DTS Error on Creation'
GO

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

(
	@ROFstID int
)
	AS
	SELECT COUNT(*)
	FROM [ROFsts] WHERE [ROFstID]=@ROFstID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsROFst Succeeded'
ELSE PRINT 'Procedure Creation: existsROFst Error on Creation'
GO

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

(
	@RODbID int
)
AS

	SELECT
		[ROFsts].[ROFstID],
		[ROFsts].[RODbID],
		[ROFsts].[ROFST],
		[ROFsts].[Config],
		[ROFsts].[DTS],
		[ROFsts].[UserID],
		[ROFsts].[LastChanged],
		(SELECT COUNT(*) FROM [Associations] WHERE [Associations].[ROFstID]=[ROFsts].[ROFstID]) [AssociationCount],
		(SELECT COUNT(*) FROM [Figures] WHERE [Figures].[ROFstID]=[ROFsts].[ROFstID]) [FigureCount]
	FROM [ROFsts]
	WHERE
		[ROFsts].[RODbID]=@RODbID

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

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

AS
	SELECT
		[ImageID],
		[RODbID],
		[FileName],
		[Content],
		[Config],
		[DTS],
		[UserID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Figures] WHERE [Figures].[ImageID]=[ROImages].[ImageID]) [FigureCount]
	FROM [ROImages]
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getROImages Succeeded'
ELSE PRINT 'Procedure Creation: getROImages Error on Creation'
GO

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

(
	@ImageID int,
	@RODbID int,
	@FileName nvarchar(255),
	@Content varbinary(MAX),
	@Config nvarchar(MAX)=null,
	@DTS datetime,
	@UserID nvarchar(100),
	@LastChanged timestamp,
	@newLastChanged timestamp output
)
AS
	UPDATE [ROImages]
		SET
			[RODbID]=@RODbID,
			[FileName]=@FileName,
			[Content]=@Content,
			[Config]=@Config,
			[DTS]=@DTS,
			[UserID]=@UserID
		WHERE [ImageID]=@ImageID AND [LastChanged]=@LastChanged
	IF @@ROWCOUNT = 0
	BEGIN
		IF NOT exists(select * from [ROImages] WHERE [ImageID]=@ImageID)
			RAISERROR('ROImage record has been deleted by another user', 16, 1)
		ELSE           	
			RAISERROR('ROImage has been edited by another user', 16, 1)            	
	END

		SELECT 		@newLastChanged=[LastChanged]	
		FROM [ROImages] WHERE [ImageID]=@ImageID		

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

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

(
	@RODbID int,
	@FileName nvarchar(255),
	@Content varbinary(MAX),
	@Config nvarchar(MAX)=null,
	@DTS datetime,
	@UserID nvarchar(100),
	@newImageID int output,
	@newLastChanged timestamp output
)
AS
	INSERT INTO [ROImages]
	(
		[RODbID],
		[FileName],
		[Content],
		[Config],
		[DTS],
		[UserID]
	)
	VALUES
	(
		@RODbID,
		@FileName,
		@Content,
		@Config,
		@DTS,
		@UserID
	)
	SELECT @newImageID= SCOPE_IDENTITY()
	SELECT 		@newLastChanged=[LastChanged]
		FROM [ROImages] WHERE [ImageID]=@newImageID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addROImage Succeeded'
ELSE PRINT 'Procedure Creation: addROImage Error on Creation'
GO

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

(
	@ImageID int
)
AS
	DELETE [Figures]
		WHERE [ImageID]=@ImageID
	DELETE [ROImages]
		WHERE [ImageID] = @ImageID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteROImage Succeeded'
ELSE PRINT 'Procedure Creation: deleteROImage Error on Creation'
GO

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

(
	@ImageID int
)
AS
	SELECT
		[ImageID],
		[RODbID],
		[FileName],
		[Content],
		[Config],
		[DTS],
		[UserID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Figures] WHERE [Figures].[ImageID]=[ROImages].[ImageID]) [FigureCount]
	FROM [ROImages]
	WHERE [ImageID]=@ImageID

	SELECT
		[Figures].[FigureID],
		[Figures].[ROFstID],
		[Figures].[ImageID],
		[Figures].[Config],
		[Figures].[DTS],
		[Figures].[UserID],
		[Figures].[LastChanged],
		[ROFsts].[RODbID] [ROFst_RODbID],
		[ROFsts].[ROFST] [ROFst_ROFST],
		[ROFsts].[Config] [ROFst_Config],
		[ROFsts].[DTS] [ROFst_DTS],
		[ROFsts].[UserID] [ROFst_UserID]
	FROM [Figures]
		JOIN [ROFsts]  ON
			[ROFsts].[ROFstID]=[Figures].[ROFstID]
	WHERE
		[Figures].[ImageID]=@ImageID

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

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

(
	@ImageID int
)
	AS
	SELECT COUNT(*)
	FROM [ROImages] WHERE [ImageID]=@ImageID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsROImage Succeeded'
ELSE PRINT 'Procedure Creation: existsROImage Error on Creation'
GO

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

(
	@RODbID int
)
AS

	SELECT
		[ROImages].[ImageID],
		[ROImages].[RODbID],
		[ROImages].[FileName],
		[ROImages].[Content],
		[ROImages].[Config],
		[ROImages].[DTS],
		[ROImages].[UserID],
		[ROImages].[LastChanged],
		(SELECT COUNT(*) FROM [Figures] WHERE [Figures].[ImageID]=[ROImages].[ImageID]) [FigureCount]
	FROM [ROImages]
	WHERE
		[ROImages].[RODbID]=@RODbID

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

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

AS
	SELECT
		[RID],
		[Name],
		[Title],
		[DTS],
		[UsrID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Assignments] WHERE [Assignments].[RID]=[Roles].[RID]) [AssignmentCount],
		(SELECT COUNT(*) FROM [Permissions] WHERE [Permissions].[RID]=[Roles].[RID]) [PermissionCount]
	FROM [Roles]
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getRoles Succeeded'
ELSE PRINT 'Procedure Creation: getRoles Error on Creation'
GO

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

(
	@RID int,
	@Name nvarchar(50),
	@Title nvarchar(250),
	@DTS datetime,
	@UsrID nvarchar(100),
	@LastChanged timestamp,
	@newLastChanged timestamp output
)
AS
	UPDATE [Roles]
		SET
			[Name]=@Name,
			[Title]=@Title,
			[DTS]=@DTS,
			[UsrID]=@UsrID
		WHERE [RID]=@RID AND [LastChanged]=@LastChanged
	IF @@ROWCOUNT = 0
	BEGIN
		IF NOT exists(select * from [Roles] WHERE [RID]=@RID)
			RAISERROR('Role record has been deleted by another user', 16, 1)
		ELSE           	
			RAISERROR('Role has been edited by another user', 16, 1)            	
	END

		SELECT 		@newLastChanged=[LastChanged]	
		FROM [Roles] WHERE [RID]=@RID		

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

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

(
	@Name nvarchar(50),
	@Title nvarchar(250),
	@DTS datetime,
	@UsrID nvarchar(100),
	@newRID int output,
	@newLastChanged timestamp output
)
AS
	INSERT INTO [Roles]
	(
		[Name],
		[Title],
		[DTS],
		[UsrID]
	)
	VALUES
	(
		@Name,
		@Title,
		@DTS,
		@UsrID
	)
	SELECT @newRID= SCOPE_IDENTITY()
	SELECT 		@newLastChanged=[LastChanged]
		FROM [Roles] WHERE [RID]=@newRID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addRole Succeeded'
ELSE PRINT 'Procedure Creation: addRole Error on Creation'
GO

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

(
	@RID int
)
AS
	DELETE [Assignments]
		WHERE [RID]=@RID
	DELETE [Permissions]
		WHERE [RID]=@RID
	DELETE [Roles]
		WHERE [RID] = @RID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteRole Succeeded'
ELSE PRINT 'Procedure Creation: deleteRole Error on Creation'
GO

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

(
	@RID int
)
AS
	SELECT
		[RID],
		[Name],
		[Title],
		[DTS],
		[UsrID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Assignments] WHERE [Assignments].[RID]=[Roles].[RID]) [AssignmentCount],
		(SELECT COUNT(*) FROM [Permissions] WHERE [Permissions].[RID]=[Roles].[RID]) [PermissionCount]
	FROM [Roles]
	WHERE [RID]=@RID

	SELECT
		[Assignments].[AID],
		[Assignments].[GID],
		[Assignments].[RID],
		[Assignments].[FolderID],
		[Assignments].[StartDate],
		[Assignments].[EndDate],
		[Assignments].[DTS],
		[Assignments].[UsrID],
		[Assignments].[LastChanged],
		[Folders].[ParentID] [Folder_ParentID],
		[Folders].[DBID] [Folder_DBID],
		[Folders].[Name] [Folder_Name],
		[Folders].[Title] [Folder_Title],
		[Folders].[ShortName] [Folder_ShortName],
		[Folders].[FormatID] [Folder_FormatID],
		[Folders].[ManualOrder] [Folder_ManualOrder],
		[Folders].[Config] [Folder_Config],
		[Folders].[DTS] [Folder_DTS],
		[Folders].[UsrID] [Folder_UsrID],
		[Groups].[GroupName] [Group_GroupName],
		[Groups].[GroupType] [Group_GroupType],
		[Groups].[Config] [Group_Config],
		[Groups].[DTS] [Group_DTS],
		[Groups].[UsrID] [Group_UsrID]
	FROM [Assignments]
		JOIN [Folders]  ON
			[Folders].[FolderID]=[Assignments].[FolderID]
		JOIN [Groups]  ON
			[Groups].[GID]=[Assignments].[GID]
	WHERE
		[Assignments].[RID]=@RID


	SELECT
		[Permissions].[PID],
		[Permissions].[RID],
		[Permissions].[PermLevel],
		[Permissions].[VersionType],
		[Permissions].[PermValue],
		[Permissions].[PermAD],
		[Permissions].[StartDate],
		[Permissions].[EndDate],
		[Permissions].[Config],
		[Permissions].[DTS],
		[Permissions].[UsrID],
		[Permissions].[LastChanged]
	FROM [Permissions]
	WHERE
		[Permissions].[RID]=@RID

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

/****** Object:  StoredProcedure [getRoleByName] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getRoleByName]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getRoleByName];
GO
	
CREATE PROCEDURE [dbo].[getRoleByName]
(
	@Name nvarchar(50)
)
AS
	SELECT
		[RID],
		[Name],
		[Title],
		[DTS],
		[UsrID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Assignments] WHERE [Assignments].[RID]=[Roles].[RID]) [AssignmentCount],
		(SELECT COUNT(*) FROM [Permissions] WHERE [Permissions].[RID]=[Roles].[RID]) [PermissionCount]
	FROM [Roles]
	WHERE [Name]=@Name
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getRoleByName Succeeded'
ELSE PRINT 'Procedure Creation: getRoleByName Error on Creation'
GO

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

(
	@RID int
)
	AS
	SELECT COUNT(*)
	FROM [Roles] WHERE [RID]=@RID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsRole Succeeded'
ELSE PRINT 'Procedure Creation: existsRole Error on Creation'
GO

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

AS
	SELECT
		[ROUsageID],
		[ContentID],
		[ROID],
		[Config],
		[DTS],
		[UserID],
		[LastChanged],
		[RODbID]
	FROM [RoUsages]
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getRoUsages Succeeded'
ELSE PRINT 'Procedure Creation: getRoUsages Error on Creation'
GO

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

(
	@ROUsageID int,
	@ContentID int,
	@ROID nvarchar(16),
	@Config nvarchar(MAX)=null,
	@DTS datetime,
	@UserID nvarchar(100),
	@LastChanged timestamp,
	@RODbID int,
	@newLastChanged timestamp output
)
AS
	UPDATE [RoUsages]
		SET
			[ContentID]=@ContentID,
			[ROID]=@ROID,
			[Config]=@Config,
			[DTS]=@DTS,
			[UserID]=@UserID,
			[RODbID]=@RODbID
		WHERE [ROUsageID]=@ROUsageID AND [LastChanged]=@LastChanged
	IF @@ROWCOUNT = 0
	BEGIN
		IF NOT exists(select * from [RoUsages] WHERE [ROUsageID]=@ROUsageID)
			RAISERROR('RoUsage record has been deleted by another user', 16, 1)
		ELSE           	
			RAISERROR('RoUsage has been edited by another user', 16, 1)            	
	END

		SELECT 		@newLastChanged=[LastChanged]	
		FROM [RoUsages] WHERE [ROUsageID]=@ROUsageID		

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

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

(
	@ContentID int,
	@ROID nvarchar(16),
	@Config nvarchar(MAX)=null,
	@DTS datetime,
	@UserID nvarchar(100),
	@RODbID int,
	@newROUsageID int output,
	@newLastChanged timestamp output
)
AS
	INSERT INTO [RoUsages]
	(
		[ContentID],
		[ROID],
		[Config],
		[DTS],
		[UserID],
		[RODbID]
	)
	VALUES
	(
		@ContentID,
		@ROID,
		@Config,
		@DTS,
		@UserID,
		@RODbID
	)
	SELECT @newROUsageID= SCOPE_IDENTITY()
	SELECT 		@newLastChanged=[LastChanged]
		FROM [RoUsages] WHERE [ROUsageID]=@newROUsageID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addRoUsage Succeeded'
ELSE PRINT 'Procedure Creation: addRoUsage Error on Creation'
GO

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

(
	@ROUsageID int
)
AS
	DELETE [RoUsages]
		WHERE [ROUsageID] = @ROUsageID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteRoUsage Succeeded'
ELSE PRINT 'Procedure Creation: deleteRoUsage Error on Creation'
GO

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

(
	@ROUsageID int
)
AS
	SELECT
		[ROUsageID],
		[ContentID],
		[ROID],
		[Config],
		[DTS],
		[UserID],
		[LastChanged],
		[RODbID]
	FROM [RoUsages]
	WHERE [ROUsageID]=@ROUsageID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getRoUsage Succeeded'
ELSE PRINT 'Procedure Creation: getRoUsage Error on Creation'
GO

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

(
	@ROUsageID int
)
	AS
	SELECT COUNT(*)
	FROM [RoUsages] WHERE [ROUsageID]=@ROUsageID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsRoUsage Succeeded'
ELSE PRINT 'Procedure Creation: existsRoUsage Error on Creation'
GO

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

(
	@ContentID int
)
AS

	SELECT
		[RoUsages].[ROUsageID],
		[RoUsages].[ContentID],
		[RoUsages].[ROID],
		[RoUsages].[Config],
		[RoUsages].[DTS],
		[RoUsages].[UserID],
		[RoUsages].[LastChanged],
		[RoUsages].[RODbID],
		[RODbs].[ROName] [RODb_ROName],
		[RODbs].[FolderPath] [RODb_FolderPath],
		[RODbs].[DBConnectionString] [RODb_DBConnectionString],
		[RODbs].[Config] [RODb_Config],
		[RODbs].[DTS] [RODb_DTS],
		[RODbs].[UserID] [RODb_UserID]
	FROM [RoUsages]
		JOIN [RODbs]  ON
			[RODbs].[RODbID]=[RoUsages].[RODbID]
	WHERE
		[RoUsages].[ContentID]=@ContentID

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

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

(
	@RODbID int
)
AS

	SELECT
		[RoUsages].[ROUsageID],
		[RoUsages].[ContentID],
		[RoUsages].[ROID],
		[RoUsages].[Config],
		[RoUsages].[DTS],
		[RoUsages].[UserID],
		[RoUsages].[LastChanged],
		[RoUsages].[RODbID],
		[Contents].[Number] [Content_Number],
		[Contents].[Text] [Content_Text],
		[Contents].[Type] [Content_Type],
		[Contents].[FormatID] [Content_FormatID],
		[Contents].[Config] [Content_Config],
		[Contents].[DTS] [Content_DTS],
		[Contents].[UserID] [Content_UserID]
	FROM [RoUsages]
		JOIN [Contents]  ON
			[Contents].[ContentID]=[RoUsages].[ContentID]
	WHERE
		[RoUsages].[RODbID]=@RODbID

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

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

AS
	SELECT
		[TransitionID],
		[FromID],
		[ToID],
		[RangeID],
		[TranType],
		[Config],
		[DTS],
		[UserID],
		[LastChanged],
		(SELECT COUNT(*) FROM [ZTransitions] WHERE [ZTransitions].[TransitionID]=[Transitions].[TransitionID]) [ZTransitionCount]
	FROM [Transitions]
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getTransitions Succeeded'
ELSE PRINT 'Procedure Creation: getTransitions Error on Creation'
GO

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

(
	@TransitionID int,
	@FromID int,
	@ToID int,
	@RangeID int,
	@TranType int,
	@Config nvarchar(MAX)=null,
	@DTS datetime,
	@UserID nvarchar(100),
	@LastChanged timestamp,
	@newLastChanged timestamp output
)
AS
	UPDATE [Transitions]
		SET
			[FromID]=@FromID,
			[ToID]=@ToID,
			[RangeID]=@RangeID,
			[TranType]=@TranType,
			[Config]=@Config,
			[DTS]=@DTS,
			[UserID]=@UserID
		WHERE [TransitionID]=@TransitionID AND [LastChanged]=@LastChanged
	IF @@ROWCOUNT = 0
	BEGIN
		IF NOT exists(select * from [Transitions] WHERE [TransitionID]=@TransitionID)
			RAISERROR('Transition record has been deleted by another user', 16, 1)
		ELSE           	
			RAISERROR('Transition has been edited by another user', 16, 1)            	
	END

		SELECT 		@newLastChanged=[LastChanged]	
		FROM [Transitions] WHERE [TransitionID]=@TransitionID		

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

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

(
	@FromID int,
	@ToID int,
	@RangeID int,
	@TranType int,
	@Config nvarchar(MAX)=null,
	@DTS datetime,
	@UserID nvarchar(100),
	@newTransitionID int output,
	@newLastChanged timestamp output
)
AS
	INSERT INTO [Transitions]
	(
		[FromID],
		[ToID],
		[RangeID],
		[TranType],
		[Config],
		[DTS],
		[UserID]
	)
	VALUES
	(
		@FromID,
		@ToID,
		@RangeID,
		@TranType,
		@Config,
		@DTS,
		@UserID
	)
	SELECT @newTransitionID= SCOPE_IDENTITY()
	SELECT 		@newLastChanged=[LastChanged]
		FROM [Transitions] WHERE [TransitionID]=@newTransitionID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addTransition Succeeded'
ELSE PRINT 'Procedure Creation: addTransition Error on Creation'
GO

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

(
	@TransitionID int
)
AS
	DELETE [ZTransitions]
		WHERE [TransitionID]=@TransitionID
	DELETE [Transitions]
		WHERE [TransitionID] = @TransitionID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteTransition Succeeded'
ELSE PRINT 'Procedure Creation: deleteTransition Error on Creation'
GO

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

(
	@TransitionID int
)
AS
	SELECT
		[TransitionID],
		[FromID],
		[ToID],
		[RangeID],
		[TranType],
		[Config],
		[DTS],
		[UserID],
		[LastChanged],
		(SELECT COUNT(*) FROM [ZTransitions] WHERE [ZTransitions].[TransitionID]=[Transitions].[TransitionID]) [ZTransitionCount]
	FROM [Transitions]
	WHERE [TransitionID]=@TransitionID

	SELECT
		[ZTransitions].[TransitionID],
		[ZTransitions].[oldto],
		[ZTransitions].[LastChanged]
	FROM [ZTransitions]
	WHERE
		[ZTransitions].[TransitionID]=@TransitionID

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

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

(
	@TransitionID int
)
	AS
	SELECT COUNT(*)
	FROM [Transitions] WHERE [TransitionID]=@TransitionID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsTransition Succeeded'
ELSE PRINT 'Procedure Creation: existsTransition Error on Creation'
GO

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

(
	@FromID int
)
AS

	SELECT
		[Transitions].[TransitionID],
		[Transitions].[FromID],
		[Transitions].[ToID],
		[Transitions].[RangeID],
		[Transitions].[TranType],
		[Transitions].[Config],
		[Transitions].[DTS],
		[Transitions].[UserID],
		[Transitions].[LastChanged],
		[Items_RangeID].[PreviousID] [Item_RangeID_PreviousID],
		[Items_RangeID].[ContentID] [Item_RangeID_ContentID],
		[Items_RangeID].[DTS] [Item_RangeID_DTS],
		[Items_RangeID].[UserID] [Item_RangeID_UserID],
		[Items_ToID].[PreviousID] [Item_ToID_PreviousID],
		[Items_ToID].[ContentID] [Item_ToID_ContentID],
		[Items_ToID].[DTS] [Item_ToID_DTS],
		[Items_ToID].[UserID] [Item_ToID_UserID],
		(SELECT COUNT(*) FROM [ZTransitions] WHERE [ZTransitions].[TransitionID]=[Transitions].[TransitionID]) [ZTransitionCount]
	FROM [Transitions]
		JOIN [Items] [Items_RangeID] ON
			[Items_RangeID].[ItemID]=[Transitions].[RangeID]
		JOIN [Items] [Items_ToID] ON
			[Items_ToID].[ItemID]=[Transitions].[ToID]
	WHERE
		[Transitions].[FromID]=@FromID

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

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

(
	@RangeID int
)
AS

	SELECT
		[Transitions].[TransitionID],
		[Transitions].[FromID],
		[Transitions].[ToID],
		[Transitions].[RangeID],
		[Transitions].[TranType],
		[Transitions].[Config],
		[Transitions].[DTS],
		[Transitions].[UserID],
		[Transitions].[LastChanged],
		[Contents].[Number] [Content_Number],
		[Contents].[Text] [Content_Text],
		[Contents].[Type] [Content_Type],
		[Contents].[FormatID] [Content_FormatID],
		[Contents].[Config] [Content_Config],
		[Contents].[DTS] [Content_DTS],
		[Contents].[UserID] [Content_UserID],
		(SELECT COUNT(*) FROM [ZTransitions] WHERE [ZTransitions].[TransitionID]=[Transitions].[TransitionID]) [ZTransitionCount]
	FROM [Transitions]
		JOIN [Contents]  ON
			[Contents].[ContentID]=[Transitions].[FromID]
	WHERE
		[Transitions].[RangeID]=@RangeID

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

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

(
	@ToID int
)
AS

	SELECT
		[Transitions].[TransitionID],
		[Transitions].[FromID],
		[Transitions].[ToID],
		[Transitions].[RangeID],
		[Transitions].[TranType],
		[Transitions].[Config],
		[Transitions].[DTS],
		[Transitions].[UserID],
		[Transitions].[LastChanged],
		[Contents].[Number] [Content_Number],
		[Contents].[Text] [Content_Text],
		[Contents].[Type] [Content_Type],
		[Contents].[FormatID] [Content_FormatID],
		[Contents].[Config] [Content_Config],
		[Contents].[DTS] [Content_DTS],
		[Contents].[UserID] [Content_UserID],
		(SELECT COUNT(*) FROM [ZTransitions] WHERE [ZTransitions].[TransitionID]=[Transitions].[TransitionID]) [ZTransitionCount]
	FROM [Transitions]
		JOIN [Contents]  ON
			[Contents].[ContentID]=[Transitions].[FromID]
	WHERE
		[Transitions].[ToID]=@ToID

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

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

AS
	SELECT
		[UID],
		[UserID],
		[FirstName],
		[MiddleName],
		[LastName],
		[Suffix],
		[CourtesyTitle],
		[PhoneNumber],
		[CFGName],
		[UserLogin],
		[UserName],
		[Config],
		[DTS],
		[UsrID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Memberships] WHERE [Memberships].[UID]=[Users].[UID]) [MembershipCount]
	FROM [Users]
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getUsers Succeeded'
ELSE PRINT 'Procedure Creation: getUsers Error on Creation'
GO

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

(
	@UID int,
	@UserID nvarchar(100),
	@FirstName nvarchar(50)=null,
	@MiddleName nvarchar(50)=null,
	@LastName nvarchar(50)=null,
	@Suffix nvarchar(10)=null,
	@CourtesyTitle nvarchar(10)=null,
	@PhoneNumber nvarchar(30)=null,
	@CFGName nvarchar(8)=null,
	@UserLogin nvarchar(10)=null,
	@UserName nvarchar(32)=null,
	@Config nvarchar(MAX)=null,
	@DTS datetime,
	@UsrID nvarchar(100),
	@LastChanged timestamp,
	@newLastChanged timestamp output
)
AS
	UPDATE [Users]
		SET
			[UserID]=@UserID,
			[FirstName]=@FirstName,
			[MiddleName]=@MiddleName,
			[LastName]=@LastName,
			[Suffix]=@Suffix,
			[CourtesyTitle]=@CourtesyTitle,
			[PhoneNumber]=@PhoneNumber,
			[CFGName]=@CFGName,
			[UserLogin]=@UserLogin,
			[UserName]=@UserName,
			[Config]=@Config,
			[DTS]=@DTS,
			[UsrID]=@UsrID
		WHERE [UID]=@UID AND [LastChanged]=@LastChanged
	IF @@ROWCOUNT = 0
	BEGIN
		IF NOT exists(select * from [Users] WHERE [UID]=@UID)
			RAISERROR('User record has been deleted by another user', 16, 1)
		ELSE           	
			RAISERROR('User has been edited by another user', 16, 1)            	
	END

		SELECT 		@newLastChanged=[LastChanged]	
		FROM [Users] WHERE [UID]=@UID		

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

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

(
	@UserID nvarchar(100),
	@FirstName nvarchar(50)=null,
	@MiddleName nvarchar(50)=null,
	@LastName nvarchar(50)=null,
	@Suffix nvarchar(10)=null,
	@CourtesyTitle nvarchar(10)=null,
	@PhoneNumber nvarchar(30)=null,
	@CFGName nvarchar(8)=null,
	@UserLogin nvarchar(10)=null,
	@UserName nvarchar(32)=null,
	@Config nvarchar(MAX)=null,
	@DTS datetime,
	@UsrID nvarchar(100),
	@newUID int output,
	@newLastChanged timestamp output
)
AS
	INSERT INTO [Users]
	(
		[UserID],
		[FirstName],
		[MiddleName],
		[LastName],
		[Suffix],
		[CourtesyTitle],
		[PhoneNumber],
		[CFGName],
		[UserLogin],
		[UserName],
		[Config],
		[DTS],
		[UsrID]
	)
	VALUES
	(
		@UserID,
		@FirstName,
		@MiddleName,
		@LastName,
		@Suffix,
		@CourtesyTitle,
		@PhoneNumber,
		@CFGName,
		@UserLogin,
		@UserName,
		@Config,
		@DTS,
		@UsrID
	)
	SELECT @newUID= SCOPE_IDENTITY()
	SELECT 		@newLastChanged=[LastChanged]
		FROM [Users] WHERE [UID]=@newUID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addUser Succeeded'
ELSE PRINT 'Procedure Creation: addUser Error on Creation'
GO

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

(
	@UID int
)
AS
	DELETE [Memberships]
		WHERE [UID]=@UID
	DELETE [Users]
		WHERE [UID] = @UID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteUser Succeeded'
ELSE PRINT 'Procedure Creation: deleteUser Error on Creation'
GO

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

(
	@UID int
)
AS
	SELECT
		[UID],
		[UserID],
		[FirstName],
		[MiddleName],
		[LastName],
		[Suffix],
		[CourtesyTitle],
		[PhoneNumber],
		[CFGName],
		[UserLogin],
		[UserName],
		[Config],
		[DTS],
		[UsrID],
		[LastChanged],
		(SELECT COUNT(*) FROM [Memberships] WHERE [Memberships].[UID]=[Users].[UID]) [MembershipCount]
	FROM [Users]
	WHERE [UID]=@UID

	SELECT
		[Memberships].[UGID],
		[Memberships].[UID],
		[Memberships].[GID],
		[Memberships].[StartDate],
		[Memberships].[EndDate],
		[Memberships].[Config],
		[Memberships].[DTS],
		[Memberships].[UsrID],
		[Memberships].[LastChanged],
		[Groups].[GroupName] [Group_GroupName],
		[Groups].[GroupType] [Group_GroupType],
		[Groups].[Config] [Group_Config],
		[Groups].[DTS] [Group_DTS],
		[Groups].[UsrID] [Group_UsrID]
	FROM [Memberships]
		JOIN [Groups]  ON
			[Groups].[GID]=[Memberships].[GID]
	WHERE
		[Memberships].[UID]=@UID

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

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

(
	@UID int
)
	AS
	SELECT COUNT(*)
	FROM [Users] WHERE [UID]=@UID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsUser Succeeded'
ELSE PRINT 'Procedure Creation: existsUser Error on Creation'
GO

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

AS
	SELECT
		[ContentID],
		[OldStepSequence],
		[LastChanged]
	FROM [ZContents]
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getZContents Succeeded'
ELSE PRINT 'Procedure Creation: getZContents Error on Creation'
GO

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

(
	@ContentID int,
	@OldStepSequence nvarchar(32),
	@LastChanged timestamp,
	@newLastChanged timestamp output
)
AS
	UPDATE [ZContents]
		SET
			[OldStepSequence]=@OldStepSequence
		WHERE [ContentID]=@ContentID AND [LastChanged]=@LastChanged
	IF @@ROWCOUNT = 0
	BEGIN
		IF NOT exists(select * from [ZContents] WHERE [ContentID]=@ContentID)
			RAISERROR('ZContent record has been deleted by another user', 16, 1)
		ELSE           	
			RAISERROR('ZContent has been edited by another user', 16, 1)            	
	END

		SELECT 		@newLastChanged=[LastChanged]	
		FROM [ZContents] WHERE [ContentID]=@ContentID		

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

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

(
	@ContentID int,
	@OldStepSequence nvarchar(32),
	@newLastChanged timestamp output
)
AS
	INSERT INTO [ZContents]
	(
		[ContentID],
		[OldStepSequence]
	)
	VALUES
	(
		@ContentID,
		@OldStepSequence
	)

	SELECT 		@newLastChanged=[LastChanged]
		FROM [ZContents] WHERE [ContentID]=@ContentID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addZContent Succeeded'
ELSE PRINT 'Procedure Creation: addZContent Error on Creation'
GO

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

(
	@ContentID int
)
AS
	DELETE [ZContents]
		WHERE [ContentID] = @ContentID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteZContent Succeeded'
ELSE PRINT 'Procedure Creation: deleteZContent Error on Creation'
GO

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

(
	@ContentID int
)
AS
	SELECT
		[ContentID],
		[OldStepSequence],
		[LastChanged]
	FROM [ZContents]
	WHERE [ContentID]=@ContentID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getZContent Succeeded'
ELSE PRINT 'Procedure Creation: getZContent Error on Creation'
GO

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

(
	@ContentID int
)
	AS
	SELECT COUNT(*)
	FROM [ZContents] WHERE [ContentID]=@ContentID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsZContent Succeeded'
ELSE PRINT 'Procedure Creation: existsZContent Error on Creation'
GO

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

(
	@ContentID int
)
AS

	SELECT
		[ZContents].[ContentID],
		[ZContents].[OldStepSequence],
		[ZContents].[LastChanged]
	FROM [ZContents]
	WHERE
		[ZContents].[ContentID]=@ContentID

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

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

AS
	SELECT
		[TransitionID],
		[oldto],
		[LastChanged]
	FROM [ZTransitions]
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getZTransitions Succeeded'
ELSE PRINT 'Procedure Creation: getZTransitions Error on Creation'
GO

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

(
	@TransitionID int,
	@Oldto nvarchar(32),
	@LastChanged timestamp,
	@newLastChanged timestamp output
)
AS
	UPDATE [ZTransitions]
		SET
			[oldto]=@Oldto
		WHERE [TransitionID]=@TransitionID AND [LastChanged]=@LastChanged
	IF @@ROWCOUNT = 0
	BEGIN
		IF NOT exists(select * from [ZTransitions] WHERE [TransitionID]=@TransitionID)
			RAISERROR('ZTransition record has been deleted by another user', 16, 1)
		ELSE           	
			RAISERROR('ZTransition has been edited by another user', 16, 1)            	
	END

		SELECT 		@newLastChanged=[LastChanged]	
		FROM [ZTransitions] WHERE [TransitionID]=@TransitionID		

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

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

(
	@TransitionID int,
	@Oldto nvarchar(32),
	@newLastChanged timestamp output
)
AS
	INSERT INTO [ZTransitions]
	(
		[TransitionID],
		[oldto]
	)
	VALUES
	(
		@TransitionID,
		@Oldto
	)

	SELECT 		@newLastChanged=[LastChanged]
		FROM [ZTransitions] WHERE [TransitionID]=@TransitionID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addZTransition Succeeded'
ELSE PRINT 'Procedure Creation: addZTransition Error on Creation'
GO

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

(
	@TransitionID int
)
AS
	DELETE [ZTransitions]
		WHERE [TransitionID] = @TransitionID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteZTransition Succeeded'
ELSE PRINT 'Procedure Creation: deleteZTransition Error on Creation'
GO

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

(
	@TransitionID int
)
AS
	SELECT
		[TransitionID],
		[oldto],
		[LastChanged]
	FROM [ZTransitions]
	WHERE [TransitionID]=@TransitionID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getZTransition Succeeded'
ELSE PRINT 'Procedure Creation: getZTransition Error on Creation'
GO

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

(
	@TransitionID int
)
	AS
	SELECT COUNT(*)
	FROM [ZTransitions] WHERE [TransitionID]=@TransitionID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsZTransition Succeeded'
ELSE PRINT 'Procedure Creation: existsZTransition Error on Creation'
GO

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

(
	@TransitionID int
)
AS

	SELECT
		[ZTransitions].[TransitionID],
		[ZTransitions].[oldto],
		[ZTransitions].[LastChanged]
	FROM [ZTransitions]
	WHERE
		[ZTransitions].[TransitionID]=@TransitionID

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