/****** Object:  StoredProcedure [dbo].[jcbAuditConfig]    Script Date: 03/29/2011 17:32:35 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jcbAuditConfig]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[jcbAuditConfig]
GO

/****** Object:  StoredProcedure [dbo].[jcbAuditConfig]    Script Date: 03/29/2011 17:33:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
create procedure [dbo].[jcbAuditConfig]
@tablename varchar(max)
as begin
--print 'table name: ' + @tablename
	declare @cmd varchar(max)
	set @cmd = ''
	
	--new table name variable
	declare @newtablename varchar(max)
	set @newtablename = 'tbl' + @tablename
--print 'new table name: ' + @newtablename

	--rename table
	set @cmd = 'sp_rename ' + '''' + @tablename + '''' + ',' + '''' + @newtablename + ''''
--print 'rename table: ' + @cmd
	exec (@cmd)
	set @cmd = ''

	--add column DeleteStatus
	set @cmd = 'alter table ' + @newtablename + ' add DeleteStatus int not null constraint DF_' + @newtablename + '_DeleteStatus default ((0))'
--print 'add column deletestatus: ' + @cmd
	exec (@cmd)
	set @cmd = ''
	
	--create index on deletestatus
	set @cmd = 'CREATE NONCLUSTERED INDEX IX_' + @newtablename + 'DeleteStatus ON ' + @newtablename + '
	(
		[DeleteStatus] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	'
	exec (@cmd)
	set @cmd = ''
	
	--primary key variable
	declare @primarykey varchar(max)
	select @primarykey = coalesce(@primarykey + ' and ','') + 
	'dd.' + c.name + ' = ' + 'ii.' + c.name
	from sysindexes i
	inner join sysobjects o ON i.id = o.id
	inner join sysobjects pk ON i.name = pk.name and pk.parent_obj = i.id	and pk.xtype = 'PK'
	inner join sysindexkeys ik on i.id = ik.id and i.indid = ik.indid
	inner join syscolumns c ON ik.id = c.id and ik.colid = c.colid
	where o.name = @newtablename
	order by ik.keyno
--print 'primary key: ' + @primarykey
	
	--shadow table name variable
	declare @shadowtablename varchar(max)
	if substring(@tablename,len(@tablename)-2,3) = 'ies'
		set @shadowtablename = replace(@tablename,'ies','y') + 'Audits'
	else
		set @shadowtablename = substring(@tablename,1,len(@tablename)-1) + 'Audits'
--print 'shadow table name: ' + @shadowtablename
	
	--create base shadow table
	set @cmd = 'create table ' + @shadowtablename + '(AuditID bigint identity (1,1) not null CONSTRAINT [PK_' + @shadowtablename + '] PRIMARY KEY CLUSTERED ([AuditID] ASC))'
--print 'create shadow table: ' + @cmd
	exec (@cmd)
	set @cmd = ''
	
	--temp variables for cursor
	declare @columnname varchar(max)
	declare @columntype varchar(max)
	declare @columnlength smallint
	declare @columnnullable int
	declare @columncollation sysname
	declare @columnprecision tinyint
	declare @columnscale tinyint
	
	--separator variable
	declare @sep varchar(max)
	set @sep = ''

	--table columns variable
	declare @tablecolumns varchar(max)
	set @tablecolumns = ''

	--ii table columns variable
	declare @iitablecolumns varchar(max)
	set @iitablecolumns = ''

	--dd table columns variable
	declare @ddtablecolumns varchar(max)
	set @ddtablecolumns = ''

	--view columns variable
	declare @viewcolumns varchar(max)
	set @viewcolumns = ''
	
	--trigger columns variable
	declare @trigcolumns varchar(max)
	set @trigcolumns = ''
	
	--cursor for getting columns
	declare tc cursor read_only for
	select c.name,t.name typename,c.length,c.isnullable,c.collation,c.xprec,c.xscale
	from sysobjects o
	inner join syscolumns c on o.id = c.id
	inner join systypes t on c.xtype = t.xtype
	where 1=1
	and o.name = @newtablename
	and t.name != 'sysname'
	order by c.colorder
	
	--alter shadow table command
	set @cmd = 'alter table ' + @shadowtablename + ' add '
--print 'alter shadow table start: ' + @cmd
	
	open tc
	fetch next from tc
	into @columnname,@columntype,@columnlength,@columnnullable,@columncollation,@columnprecision,@columnscale
	while @@fetch_status = 0
	begin
		if @columntype not in ('text','ntext','image')
		begin
			if @columntype not in ('timestamp')
			begin
				set @cmd = @cmd + @sep + @columnname + ' ' + @columntype
				if @columntype in ('binary','char','nchar','nvarchar','varbinary','varchar')
				begin
					if @columnlength = -1
						set @cmd = @cmd + '(max)'
					else
						set @cmd = @cmd + '(' + cast(@columnlength as varchar(10)) + ')'
				end
				if @columntype in ('decimal','numeric')
					set @cmd = @cmd + '(' + cast(@columnprecision as varchar(10)) + ',' + cast(@columnscale as varchar(10)) + ')'
				if @columntype in ('char','nchar','nvarchar','varchar')
					set @cmd = @cmd + ' collate ' + @columncollation + ' '
				if @columnnullable = 0
					set @cmd = @cmd + ' not'
				set @cmd = @cmd + ' null'
				set @tablecolumns = @tablecolumns + @sep + @columnname
				set @iitablecolumns = @iitablecolumns + @sep + 'ii.' + @columnname
				set @ddtablecolumns = @ddtablecolumns + @sep + 'dd.' + @columnname
			end
			if @columnname <> 'DeleteStatus'
			begin
				set @viewcolumns = @viewcolumns + @sep + @columnname
				if @columntype not in ('timestamp')
				begin
					set @trigcolumns = @trigcolumns + @sep + @columnname
				end
			end
			set @sep = ','	
		end
		fetch next from tc
		into @columnname,@columntype,@columnlength,@columnnullable,@columncollation,@columnprecision,@columnscale
	end
	close tc
	deallocate tc
	
	--modify shadow table
--print 'modify shadow table: ' + @cmd
	exec (@cmd)
	set @cmd = ''

	--add extended property for shadow table dts
	set @cmd = 'sys.sp_addextendedproperty @name=N''MS_Description'', @value=N''{datetime}'' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''TABLE'',@level1name=N''' + @shadowtablename + ''', @level2type=N''COLUMN'',@level2name=N''DTS'''
	exec (@cmd)	
	set @cmd = ''
	
	--add original indexes to shadowtable
	--variables for cursor
	declare @indexcolumnname varchar(max)
	declare ic cursor for
	select 
	c.name columnname
	from sysindexes i
	inner join sysobjects o ON i.id = o.id
	inner join sysindexkeys ik on i.id = ik.id and i.indid = ik.indid
	inner join syscolumns c ON ik.id = c.id and ik.colid = c.colid
	where o.name = @newtablename

	open ic
	fetch next from ic into @indexcolumnname
	while @@fetch_status = 0
	begin
		set @cmd = 'CREATE NONCLUSTERED INDEX IX_' + @shadowtablename + @indexcolumnname + ' on ' + @shadowtablename + '
		(' + @indexcolumnname + ')
		WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]'
		exec (@cmd)	
		set @cmd = ''
		fetch next from ic into @indexcolumnname
	end
	close ic
	deallocate ic
	
	--create view
	set @cmd = 'create view ' + @tablename + ' as select ' + @viewcolumns + ' from ' + @newtablename + ' where DeleteStatus = 0'
--print 'create view: ' + @cmd
	exec (@cmd)
	set @cmd = ''
	
	--create update trigger on table
	set @cmd = 'create trigger tr_' + @newtablename + '_Update on ' + @newtablename + 
	' for update as 
	begin
		if exists (select * from inserted)
		begin
			insert into ' + @shadowtablename + '(' + @tablecolumns + ')
			select ' + @ddtablecolumns + ' from deleted dd
			inner join inserted ii on ' + @primarykey + '
		end
	end'
--print 'table trigger update: ' + @cmd
	exec (@cmd)
	set @cmd = ''
	
	--create delete trigger on view
	set @cmd = 'create trigger tr_' + @tablename + '_Delete on ' + @tablename + 
	' instead of delete as 
	begin
		update ii set DeleteStatus = (select max(DeleteID) from DeleteLog where SPID = @@spid), DTS = getdate(),
		UserID = (select top 1 UserID from DeleteLog where SPID = @@spid order by deleteid desc)
		from ' + @newtablename + ' ii inner join deleted dd on ' + @primarykey + '
		insert into ' + @shadowtablename + '(' + @tablecolumns + ')
		select ' + @iitablecolumns + '
		from ' + @newtablename + ' ii inner join deleted dd on ' + @primarykey + '
		where ii.DeleteStatus > 0
	end'
--print 'view trigger delete: ' + @cmd
	exec (@cmd)
	set @cmd = ''
end
GO
jcbAuditConfig 'Annotations'
go
jcbAuditConfig 'Contents'
go
jcbAuditConfig 'Documents'
go
jcbAuditConfig 'Entries'
go
jcbAuditConfig 'Grids'
go
jcbAuditConfig 'Images'
go
jcbAuditConfig 'Items'
go
jcbAuditConfig 'Parts'
go
--new stuff
jcbAuditConfig 'ROUsages'
go
jcbAuditConfig 'Transitions'
go

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

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

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

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

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

/****** Object:  UserDefinedFunction [dbo].[ve_GetPartType]    Script Date: 05/18/2011 11:20:48 ******/
/*
select ItemID, dbo.[ve_GetPartType](ItemID)
from Items II 
where ItemID in(10181,10182,10183)
*/
-- drop function ve_GetPath
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
create FUNCTION [dbo].[ve_GetPartType] (@ItemID int) RETURNS varchar(max)
WITH EXECUTE AS OWNER
AS
BEGIN
-- declare @STructID int
--set @StructID=11
declare @PartType varchar(max);
with Itemz(ItemID,PreviousID,PartType) as (
  Select ItemID,PreviousID,cast(null as varchar(max))
    from Items I
    where ItemID=@ItemID
  Union All
-- siblings
  Select Z.PreviousID,II.PreviousID,cast(null as varchar(max))
    from Items II
    Join Itemz Z on II.ItemID = Z.PreviousID
	where Z.PreviousID != 0
	and Z.PartType is null
  Union All
-- children
  select Z.ItemID,Z.PreviousID,
	cast(case P.FromType
	when 1 then 'Procedure'
	when 2 then 'Section'
	when 3 then 'Caution'
	when 4 then 'Note'
	when 5 then 'RNO'
	when 6 then 'Step'
	when 7 then 'Table'
	else 'Unknown'
	end as varchar(max)) PartType
	from Parts P
	join Itemz Z on P.ItemID=Z.ItemID
	where Z.PartType is null
)
select @PartType = PartType From Itemz where PartType is not null
OPTION (MAXRECURSION 10000)
return @PartType
END;
GO
-- Display the status of Trigger alter
IF (@@Error = 0) PRINT 'Function: ve_GetPartType Succeeded'
ELSE PRINT 'Function: ve_GetPartType Error on Creation'
go

/****** Object:  UserDefinedFunction [dbo].[ve_GetPartContentID]    Script Date: 05/18/2011 11:20:48 ******/
/*
select ItemID, dbo.[ve_GetPartContentID](ItemID)
from ItemAudits II 
where ii.ItemID in(10181,10182,10183)
*/
-- drop function ve_GetPath
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE FUNCTION [dbo].[ve_GetPartContentID] (@ItemID int) RETURNS int
WITH EXECUTE AS OWNER
AS
BEGIN
-- declare @STructID int
--set @StructID=11
declare @ContentID int;
with Itemz(ItemID,PreviousID,ContentID,path) as (
  Select ItemID,PreviousID,cast(null as int),'.' + cast(itemid as varchar(max)) + '.'
    from ItemAudits I
    where ItemID=@ItemID
and i.deletestatus > 0
  Union All
-- siblings
  Select Z.PreviousID,II.PreviousID,cast(null as int),z.path + '.' + cast(z.previousid as varchar(max)) + '.'
    from Items II
    Join Itemz Z on II.ItemID = Z.PreviousID
	where Z.PreviousID != 0
	and Z.ContentID is null
  Union All
-- siblings
  Select Z.PreviousID,II.PreviousID,cast(null as int),z.path + '.' + cast(z.previousid as varchar(max)) + '.'
    from ItemAudits II
    Join Itemz Z on II.ItemID = Z.PreviousID
	where Z.PreviousID != 0
	and Z.ContentID is null
and ii.deletestatus > 0
and z.path not like '%.' + cast(z.previousid as varchar(max)) + '.%'
  Union All
-- children
  select Z.ItemID,Z.PreviousID,
P.Contentid,z.path + '.' + cast(Z.ItemID as varchar(max)) + '.'
	from Parts P
	join Itemz Z on P.ItemID=Z.ItemID
	where Z.ContentID is null
  Union All
-- children
  select Z.ItemID,Z.PreviousID,
P.Contentid,z.path + '.' + cast(Z.ItemID as varchar(max)) + '.'
	from PartAudits P
	join Itemz Z on P.ItemID=Z.ItemID
	where Z.ContentID is null
)
select @ContentID = min(ContentID) From Itemz where ContentID is not null
OPTION (MAXRECURSION 10000)
return @ContentID
END;
GO
-- Display the status of Trigger alter
IF (@@Error = 0) PRINT 'Function: ve_GetPartContentID Succeeded'
ELSE PRINT 'Function: ve_GetPartContentID Error on Creation'
go

/****** Object:  UserDefinedFunction [dbo].[ve_GetPartFromType]    Script Date: 05/18/2011 11:20:48 ******/
/*
select ItemID, dbo.[ve_GetPartFromType](ItemID)
from ItemAudits II 
where ii.ItemID in(10181,10182,10183)
*/
-- drop function ve_GetPath
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
create FUNCTION [dbo].[ve_GetPartFromType] (@ItemID int) RETURNS int
WITH EXECUTE AS OWNER
AS
BEGIN
-- declare @STructID int
--set @StructID=11
declare @FromType int;
with Itemz(ItemID,PreviousID,FromType,path) as (
  Select ItemID,PreviousID,cast(null as int),'.' + cast(itemid as varchar(max)) + '.'
    from ItemAudits I
    where ItemID=@ItemID
and i.deletestatus > 0
  Union All
-- siblings
  Select Z.PreviousID,II.PreviousID,cast(null as int),z.path + '.' + cast(z.previousid as varchar(max)) + '.'
    from Items II
    Join Itemz Z on II.ItemID = Z.PreviousID
	where Z.PreviousID != 0
	and Z.FromType is null
  Union All
-- siblings
  Select Z.PreviousID,II.PreviousID,cast(null as int),z.path + '.' + cast(z.previousid as varchar(max)) + '.'
    from ItemAudits II
    Join Itemz Z on II.ItemID = Z.PreviousID
	where Z.PreviousID != 0
	and Z.FromType is null
and ii.deletestatus > 0
and z.path not like '%.' + cast(z.previousid as varchar(max)) + '.%'
  Union All
-- children
  select Z.ItemID,Z.PreviousID,
P.FromType,z.path + '.' + cast(Z.ItemID as varchar(max)) + '.'
	from Parts P
	join Itemz Z on P.ItemID=Z.ItemID
	where Z.FromType is null
  Union All
-- children
  select Z.ItemID,Z.PreviousID,
P.FromType,z.path + '.' + cast(Z.ItemID as varchar(max)) + '.'
	from PartAudits P
	join Itemz Z on P.ItemID=Z.ItemID
	where Z.FromType is null
)
select @FromType = min(FromType) From Itemz where FromType is not null
OPTION (MAXRECURSION 10000)
return @FromType
END;
GO
-- Display the status of Trigger alter
IF (@@Error = 0) PRINT 'Function: ve_GetPartFromType Succeeded'
ELSE PRINT 'Function: ve_GetPartFromType Error on Creation'
go


/****** Object:  StoredProcedure [getAnnotationAudits] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getAnnotationAudits]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getAnnotationAudits];
GO
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getAnnotationAudits]

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

/****** Object:  StoredProcedure [getAnnotationAudit] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getAnnotationAudit]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getAnnotationAudit];
GO
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getAnnotationAudit]

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

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

/*
exec getannotationauditsbyitemid 894
*/	
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getAnnotationAuditsByItemID]
(
	@ItemID int
)

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

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

/*
exec getannotationauditsbyannotationid 53
*/	
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getAnnotationAuditsByAnnotationID]
(
	@AnnotationID int
)

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

/****** Object:  StoredProcedure [getContentAudits] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getContentAudits]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getContentAudits];
GO
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getContentAudits]

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

/****** Object:  StoredProcedure [getContentAudit] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getContentAudit]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getContentAudit];
GO
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getContentAudit]

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

/****** Object:  StoredProcedure [getContentAuditsByContentID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getContentAuditsByContentID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getContentAuditsByContentID];
GO
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getContentAuditsByContentID]
(
	@ContentID int
)

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

/****** Object:  StoredProcedure [getContentAuditsByDeleteStatus] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getContentAuditsByDeleteStatus]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getContentAuditsByDeleteStatus];
GO
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getContentAuditsByDeleteStatus]
(
	@DeleteStatus int
)

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

/****** Object:  StoredProcedure [getDocumentAudits] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getDocumentAudits]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getDocumentAudits];
GO
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getDocumentAudits]

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

/****** Object:  StoredProcedure [getDocumentAudit] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getDocumentAudit]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getDocumentAudit];
GO
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getDocumentAudit]

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

/****** Object:  StoredProcedure [getDocumentAuditsByDocID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getDocumentAuditsByDocID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getDocumentAuditsByDocID];
GO
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getDocumentAuditsByDocID]
(
	@DocID int
)

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

/****** Object:  StoredProcedure [getEntryAudits] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getEntryAudits]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getEntryAudits];
GO
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getEntryAudits]

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

/****** Object:  StoredProcedure [getEntryAudit] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getEntryAudit]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getEntryAudit];
GO
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getEntryAudit]

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

/****** Object:  StoredProcedure [getEntryAuditsByContentID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getEntryAuditsByContentID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getEntryAuditsByContentID];
GO
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getEntryAuditsByContentID]
(
	@ContentID int
)

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

/****** Object:  StoredProcedure [getGridAudits] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getGridAudits]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getGridAudits];
GO
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getGridAudits]

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

/****** Object:  StoredProcedure [getGridAudit] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getGridAudit]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getGridAudit];
GO
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getGridAudit]

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

/****** Object:  StoredProcedure [getGridAuditsByContentID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getGridAuditsByContentID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getGridAuditsByContentID];
GO
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getGridAuditsByContentID]
(
	@ContentID int
)

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

/****** Object:  StoredProcedure [getImageAudits] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getImageAudits]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getImageAudits];
GO
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getImageAudits]

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

/****** Object:  StoredProcedure [getImageAudit] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getImageAudit]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getImageAudit];
GO
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getImageAudit]

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

/****** Object:  StoredProcedure [getImageAuditsByContentID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getImageAuditsByContentID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getImageAuditsByContentID];
GO
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getImageAuditsByContentID]
(
	@ContentID int
)

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

/****** Object:  StoredProcedure [getItemAudits] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getItemAudits]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getItemAudits];
GO
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getItemAudits]

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

/****** Object:  StoredProcedure [getItemAudit] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getItemAudit]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getItemAudit];
GO
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getItemAudit]

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

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

*/
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getItemAuditsByItemID]
(
	@ItemID int
)

WITH EXECUTE AS OWNER
AS
/*
	with Itemz([AuditType],[Level], [ParentID], [Ordinal], [ItemID], [PreviousID], [FromType], [ContentID], [DTS], [UserID], [LastChanged]) as (
	  Select 'root' [AuditType], 0 [Level], 0 [ParentID], 0 [Ordinal], [ItemID], [PreviousID],0 [FromType],[ContentID],[DTS],[UserID],[LastChanged]
		FROM [Items]
	    where [ItemID]= @ItemID
	Union All
	-- Children
	  select 'chld' [AuditType], [Level] + 2,Z.ItemID,0, I.[ItemID], I.[PreviousID], P.[FromType],I.[ContentID],I.[DTS],I.[UserID],I.[LastChanged]
		from Itemz Z
		join Parts P on P.ContentID = Z.ContentID
		join Items I on I.ItemID = P.ItemID
	-- Siblings
	Union All
	  select 'sibl' [AuditType], [Level] ,Z.[ParentID],Z.[Ordinal] +1, I.[ItemID], I.[PreviousID], [FromType],I.[ContentID],I.[DTS],I.[UserID],I.[LastChanged]
		from Itemz Z
		join Items I on I.PreviousID = Z.ItemID
		where Z.[Level] > 0
	)
*/

select * from
(
	--get deleted previous item
	select 0 Level,ia.*,dbo.[ve_GetPartType](@ItemID) ItemType from 
	itemaudits ia
	inner join itemaudits iaa on ia.itemid = iaa.previousid
	where iaa.itemid = @ItemID
	and ia.itemid not in (select itemid from items where itemid = ia.itemid)
	and ia.deletestatus in (select max(deletestatus) from itemaudits where itemid = ia.itemid)
	union
	--get deleted next item
/*
	select 1 Level,ia.*,@itemtype ItemType from itemaudits ia
	where ia.previousid = @ItemID
	and ia.itemid not in (select itemid from items where itemid = ia.itemid)
	and ia.deletestatus in (select max(deletestatus) from itemaudits where itemid = ia.itemid)
*/
	select 1 Level,
		ia.[AuditID],
		ia.[ItemID],
		ia.[PreviousID],
		ia.[ContentID],
		ia.[DTS],
		ia.[UserID],
		ti.[DeleteStatus]
,dbo.[ve_GetPartType](@ItemID) ItemType
	from itemaudits ia
	inner join tblitems ti on ia.itemid = ti.itemid
	where ia.previousid = @ItemID
	and ti.deletestatus > 0
and ia.dts = ti.dts
	union
	--get chillins
	select 2 Level,ia.*,
case
when pa.fromtype = 1 then 'Procedure'
when pa.fromtype = 2 then 'Section'
when pa.fromtype = 3 then 'Caution'
when pa.fromtype = 4 then 'Note'
when pa.fromtype = 5 then 'RNO'
when pa.fromtype = 6 then 'Step'
when pa.fromtype = 7 then 'Table'
else 'Unknown'
end itemtype
 from itemaudits ia 
	inner join partaudits pa on ia.itemid = pa.itemid 
	inner join items ii on pa.contentid = ii.contentid
	where ii.itemid = @ItemID
	and ia.itemid not in (select itemid from items where itemid = ia.itemid)
	and ia.deletestatus in (select max(deletestatus) from itemaudits where itemid = ia.itemid)
) ia
order by deletestatus desc
OPTION (MAXRECURSION 10000)
--select * from itemz order by parentid,ordinal
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getItemAuditsByItemID Succeeded'
ELSE PRINT 'Procedure Creation: getItemAuditsByItemID Error on Creation'
GO

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

/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[restoreDeletedItem]
(
	@ItemID int,
	@DeleteID int,
	@CurrentID int,
	@Level int
)

WITH EXECUTE AS OWNER
AS
	DECLARE @PreviousID int
	DECLARE @NextID int
	DECLARE @ContentID int
declare @fromtype int
	IF @Level = 0
		BEGIN
			SET @NextID = @CurrentID
			SELECT @PreviousID = PreviousID FROM Items WHERE ItemID = @CurrentID
		END
	IF @Level = 1
		BEGIN
			SELECT @NextID = ItemID FROM Items WHERE PreviousID = @CurrentID
			SET @PreviousID = @CurrentID
		END
	SELECT @ContentID = dbo.[ve_GetPartContentID](@ItemID)
select @fromtype = dbo.[ve_GetPartFromType](@ItemID)
if @level = 2
begin
	select @NextID = itemid from parts where contentid = @contentid and fromtype = @fromtype
end
	UPDATE tblParts SET DeleteStatus = 0 WHERE ItemID IN (SELECT ItemID FROM PartAudits WHERE DeleteStatus = @DeleteID)
update tblparts set deletestatus = 0 where contentid = @contentid and fromtype = @fromtype
	UPDATE tblContents SET DeleteStatus = 0, ActionDTS = getdate() WHERE ContentID IN (SELECT ContentID FROM ContentAudits WHERE DeleteStatus = @DeleteID)
	UPDATE tblItems SET DeleteStatus = 0,PreviousID = CASE WHEN ItemID = @ItemID THEN @PreviousID ELSE PreviousID END 
	WHERE ItemID IN (SELECT ItemID FROM ItemAudits WHERE DeleteStatus = @DeleteID)
	UPDATE tblGrids SET DeleteStatus = 0 WHERE ContentID IN (SELECT ContentID FROM ContentAudits WHERE DeleteStatus = @DeleteID)
	UPDATE tblEntries SET DeleteStatus = 0 WHERE DocID IN (SELECT DocID FROM EntryAudits WHERE DeleteStatus = @DeleteID)
	UPDATE tblAnnotations SET DeleteStatus = 0, ActionDTS = getdate() WHERE deletestatus = @deleteid	-- ItemID in (SELECT ItemID FROM ItemAudits WHERE DeleteStatus = @DeleteID)
	UPDATE tblROUsages SET DeleteStatus = 0 WHERE deletestatus = @deleteid	--ContentID IN (SELECT ContentID FROM ContentAudits WHERE DeleteStatus = @DeleteID)
	UPDATE tblTransitions SET DeleteStatus = 0 WHERE deletestatus = @deleteid	--FromID IN (SELECT ContentID FROM ContentAudits WHERE DeleteStatus = @DeleteID)
	IF @NextID IS NOT NULL
		BEGIN
			UPDATE Items SET PreviousID = @ItemID WHERE ItemID = @NextID
			IF @ContentID IS NOT NULL
				BEGIN
					UPDATE Parts SET ItemID = @ItemID WHERE ItemID = @NextID and ContentID = @ContentID
				END
		END
--else
--	begin
--update parts set itemid = @itemid where contentid = @contentid and fromtype = @fromtype
--	end
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: restoreDeletedItem Succeeded'
ELSE PRINT 'Procedure Creation: restoreDeletedItem Error on Creation'
GO

/****** Object:  StoredProcedure [restoreDeletedAnnotation] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[restoreDeletedAnnotation]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [restoreDeletedAnnotation];
GO
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[restoreDeletedAnnotation]
(
	@AnnotationID int
)

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

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

/****** Object:  StoredProcedure [getPartAudits] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getPartAudits]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getPartAudits];
GO
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getPartAudits]

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

/****** Object:  StoredProcedure [getPartAudit] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getPartAudit]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getPartAudit];
GO
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getPartAudit]

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

/****** Object:  StoredProcedure [getPartAuditsByContentID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getPartAuditsByContentID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getPartAuditsByContentID];
GO
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getPartAuditsByContentID]
(
	@ContentID int
)

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

/****** Object:  StoredProcedure [getPartAuditsByItemID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getPartAuditsByItemID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getPartAuditsByItemID];
GO
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getPartAuditsByItemID]
(
	@ItemID int
)

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

/****** Object:  StoredProcedure [getPartAuditsByDeleteStatus] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getPartAuditsByDeleteStatus]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [getPartAuditsByDeleteStatus];
GO
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getPartAuditsByDeleteStatus]
(
	@DeleteStatus int
)

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

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

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

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

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

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

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

/****** Object:  StoredProcedure [restoreChangedAnnotation] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[restoreChangedAnnotation]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [restoreChangedAnnotation];
GO
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[restoreChangedAnnotation]
(
	@AuditID int
)

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

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

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

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

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

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

/****** Object:  StoredProcedure [restoreChangedContent] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[restoreChangedContent]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [restoreChangedContent];
GO
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[restoreChangedContent]
(
	@AuditID int
)

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

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

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

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

/****** Object:  StoredProcedure [deleteAnnotationWithUserID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[deleteAnnotationWithUserID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [deleteAnnotationWithUserID];
GO
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[deleteAnnotationWithUserID]

(
	@AnnotationID int,
	@UserID varchar(200)
)
WITH EXECUTE AS OWNER
AS
BEGIN TRY -- Try Block
	BEGIN TRANSACTION
--delete log add
	DECLARE @DeleteID int
	INSERT INTO DeleteLog (UserID) VALUES (@UserID)
	SELECT @DeleteID = SCOPE_IDENTITY()
--end delete log add
	DELETE [Annotations]
		WHERE [AnnotationID] = @AnnotationID
--delete log purge
	DELETE from DeleteLog where DeleteID = @DeleteID
--end delete log purge
	IF( @@TRANCOUNT > 0 ) COMMIT
END TRY
BEGIN CATCH -- Catch Block
	IF( @@TRANCOUNT = 1 ) ROLLBACK -- Only rollback if top level
 	ELSE IF( @@TRANCOUNT > 1 ) COMMIT -- Otherwise commit.  Top level will rollback
	EXEC vlnErrorHandler
END CATCH
GO

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

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

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

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

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

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

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

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

/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE  FUNCTION [dbo].[vefn_SiblingAndChildrenItemsByProc](@ProcID int)
RETURNS @SiblingAndChildren TABLE
(
		[ItemID] int PRIMARY KEY
	, [DVPath] nvarchar(max)
	, [Path] nvarchar(max)
	, [Level] int
	, [FromType] int
	, [Ordinal] int
	, [ParentID] int
	, [PreviousID] int
	, [ContentID] int
	, [DTS] datetime
	, [UserID] nvarchar(100)
	, [pContentID] int
	, [pDTS] datetime
	, [pUserID] nvarchar(100)
	, [IsRNO] int

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

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

go

/****** Object:  UserDefinedFunction [dbo].[vefn_ProcedureByProcID]    Script Date: 05/18/2011 11:20:48 ******/

/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
create  FUNCTION [dbo].[vefn_ProcedureByProcID](@ProcID int)
RETURNS @SiblingAndChildren TABLE
(
		[ItemID] int PRIMARY KEY
	, [DVPath] nvarchar(max)
	, [Path] nvarchar(max)
	, [Level] int
	, [FromType] int
	, [Ordinal] int
	, [ParentID] int
	, [PreviousID] int
	, [ContentID] int
	, [DTS] datetime
	, [UserID] nvarchar(100)
	, [pContentID] int
	, [pDTS] datetime
	, [pUserID] nvarchar(100)
	, [IsRNO] int

--	, [PPath] nvarchar(max)
--	, [POrdinalPath] nvarchar(max)
	, [OrdinalPath] nvarchar(max)
)
WITH EXECUTE AS OWNER
AS
BEGIN
Declare @Delim char(1)
Set @Delim=char(7)
Declare @DelimNumber char(1)
Set @DelimNumber=char(17)
Declare @DelimStep char(1)
Set @DelimStep='.'
BEGIN
insert into @SiblingAndChildren
  Select 
[I].[ItemID]
, '' DVPath
, Cast(Case when C.Type < 20000 then @Delim + C.Number + @DelimNumber + C.Text else '1' end as nvarchar(max)) [Path]
,0 [Level]
,0 [FromType]
, 0 [Ordinal]
, 0 [ParentID]
, [PreviousID]
,[I].[ContentID]
,[I].[DTS]
,[I].[UserID]
	,0 [pContentID]
,[I].[DTS] [pDTS]
, [I].[UserID] [pUserID]
,0 IsRNO
		, Cast('0001' as nvarchar(max)) [OrdinalPath]
	FROM [Items] I
  Join Contents C on C.ContentID=I.ContentID
    WHERE I.[ItemID] = @ProcID
END
RETURN
END
go
-- Display the status of Trigger alter
IF (@@Error = 0) PRINT 'Function: vefn_ProcedureByProcID Succeeded'
ELSE PRINT 'Function: vefn_ProcedureByProcID Error on Creation'

go


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

Select * from Items where ItemID = @ItemID

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

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

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

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

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[vefn_GetMyChronology]') AND OBJECTPROPERTY(id,N'IsTableFunction') = 1)
    DROP FUNCTION [vefn_GetMyChronology];
GO
/****** Object:  UserDefinedFunction [dbo].[vefn_GetMyChronology]    Script Date: 11/07/2012 18:09:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE FUNCTION [dbo].[vefn_GetMyChronology](@ProcItemID int, @ItemID int, @IncludeDeletedChildren int, @DTS datetime)
RETURNS @Chrono TABLE
(
[AuditID] bigint,
[ContentID] int,
[Number] nvarchar(max),
[Text] nvarchar(max),
[Type] int,
[FormatID] int,
[Config] nvarchar(max),
[DTS] datetime,
[UserID] nvarchar(max),
[DeleteStatus] int,
[ActionDTS] datetime,
[ActionWhat] nvarchar(max),
[ActionWhen] datetime,
[Path] nvarchar(max),
ItemID int,
TypeName nvarchar(max),
ordinalpath nvarchar(max)
)
WITH EXECUTE AS OWNER
AS
BEGIN
insert into @Chrono
select distinct [AuditID],[ContentID],[Number],[Text],[Type],[FormatID],[Config],[DTS],[UserID],[DeleteStatus],[ActionDTS],[ActionWhat],[ActionWhen],[Path],ItemID,TypeName,ordinalpath
	from
	(
	select
	case
	when lastauditid is null then 'Added'
	when r.deletestatus > 0 then 'Deleted'
	when lastauditid = -1 then 'Changed'
	when DeletedAuditID is not null then 'Restored'
--	when DeletedAuditID is not null and lastauditid = deletedauditid then 'Restored'
	else 'Changed'
	end actionwhat
,actiondts actionwhen
--	,case
--	when lastauditid is null then dts
--	when r.deletestatus > 0 then ActionDTS
--	when lastauditid = -1 then dts
--	when DeletedAuditID is not null then ActionDTS
--	else dts
--	end actionwhen
,(select dbo.vefn_GetStepTypeNameByItemIDandTypeID(t.itemid,r.type)) TypeName
	,*
	from vefn_tblchilditems (@ProcItemID,@ItemID,@IncludeDeletedChildren) t
	inner join vefn_chronologyreport(@ProcItemID,@DTS) r
	on t.icontentid = r.contentid
--	where ActionDTS > procdts or dts > procdts
	) ah
	order by OrdinalPath, contentid,auditid--actionwhen
	RETURN
END
go
IF (@@Error = 0) PRINT 'TableFunction [vefn_GetMyChronology] Succeeded'
ELSE PRINT 'TableFunction [vefn_GetMyChronology] Error on Creation'
go

/****** Object:  StoredProcedure [dbo].[getContentAuditsChronologyByItemID]    Script Date: 03/21/2012 15:58:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
	
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getContentAuditsChronologyByItemID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getContentAuditsChronologyByItemID];
GO
/****** Object:  StoredProcedure [getContentAuditsChronologyByItemID] ******/
/*
getContentAuditsChronologyByItemID 10154,10154,0
getContentAuditsChronologyByItemID 42,42,0
getContentAuditsChronologyByItemID 9,9,0
getContentAuditsChronologyByItemID 146,146,1
*/	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getContentAuditsChronologyByItemID]
(
	@ProcedureItemID int,
	@SelectedItemID int,
	@IncludeDeletedChildren int,
	@DTS datetime
)

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

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

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

/****** Object:  StoredProcedure [getAnnotationAuditsChronologyByItemID] ******/
/*
getAnnotationAuditsChronologyByItemID 30,30
getAnnotationAuditsChronologyByItemID 30,8570
getAnnotationAuditsChronologyByItemID 30,8513
getAnnotationAuditsChronologyByItemID 30,8505
*/
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
create procedure [dbo].[getAnnotationAuditsChronologyByItemID]
(
	@ProcItemID int,
	@ItemID int,
	@DTS datetime
)
WITH EXECUTE AS OWNER
AS
begin
--	declare @dts datetime
--	set @dts = (select dts from items where itemid = @procitemid)
	declare @tci table
	(
	ItemID int,
	IContentID int,
	IDeleteStatus int,
	ProcDTS datetime,
	Path nvarchar(max),
	OrdinalPath nvarchar(max)
	)
	insert into @tci
	select * from vefn_tblchilditems(@ProcItemID,@ItemID,0)
	select
	case
	when lastauditid is null and dts > itemdts then 'Added'
	when deletestatus > 0 then 'Deleted'
	when lastauditid = deletedauditid then 'Restored'
	else 'Changed'
	end ActionWhat
	,case
	when lastauditid is null and dts > itemdts then dts
	when deletestatus > 0 then ActionDTS
	when lastauditid = deletedauditid then ActionDTS
	else dts
	end ActionWhen
	,*
	from
	(
	select
	cast(ident_current('annotationaudits') + 1 as bigint) auditid
	--	(select max(auditid) + 1 from annotationaudits) auditid
	--	0 auditid
	,aa.annotationid
	,aa.itemid
	,aa.typeid
	,aa.rtftext
	,aa.searchtext
	,aa.config
	,aa.dts
	,aa.userid
	,0 deletestatus
	,aa.ActionDTS
	,ii.contentid icontentid
	,(select min(dts) from tblitems where itemid = ii.itemid) ItemDTS
	,(select max(auditid) from annotationaudits where annotationid = aa.annotationid and itemid = ii.itemid) LastAuditID
	,(select max(auditid) from annotationaudits where annotationid = aa.annotationid and itemid = ii.itemid and deletestatus > 0 and aa.dts = dts) DeletedAuditID
,tci.ordinalpath
	from tblannotations aa
	inner join items ii on aa.itemid = ii.itemid
join @tci tci on tci.itemid = ii.itemid
	where aa.deletestatus = 0
	union
	select
	aa.auditid
	,aa.annotationid
	,aa.itemid
	,aa.typeid
	,aa.rtftext
	,aa.searchtext
	,aa.config
	,aa.dts
	,aa.userid
	,aa.deletestatus
	,aa.ActionDTS
	,ii.contentid icontentid
	,(select min(dts) from tblitems where itemid = ii.itemid) ItemDTS
	,(select max(auditid) from annotationaudits where annotationid = aa.annotationid and itemid = ii.itemid and aa.auditid > auditid ) LastAuditID
	,(select max(auditid) from annotationaudits where annotationid = aa.annotationid and itemid = ii.itemid and aa.auditid > auditid and deletestatus > 0 and aa.dts = dts) DeletedAuditID
,tci.ordinalpath
	from annotationaudits aa
	inner join items ii on aa.itemid = ii.itemid
join @tci tci on tci.itemid = ii.itemid
	) ah
	where itemid in (select itemid from @tci)
	and dts > @dts
	--(select dts from versions where versionid = (select max(versionid) from revisions rr inner join versions vv on rr.revisionid = vv.revisionid
	--inner join stages ss on vv.stageid = ss.stageid where itemid = @procitemid and ss.isapproved = 1))
	order by ordinalpath,annotationid,auditid--actionwhen
end
go
-- Display the status of Trigger alter
IF (@@Error = 0) PRINT 'StoredProcedure [getAnnotationAuditsChronologyByItemID] Succeeded'
ELSE PRINT 'StoredProcedure [getAnnotationAuditsChronologyByItemID] Error on Creation'
go


/****** Object:  Trigger [dbo].[tr_GridAudits_insert]    Script Date: 06/06/2011 16:58:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
create trigger [dbo].[tr_GridAudits_insert]
on [dbo].[GridAudits]
after INSERT
AS
begin
	update ga set ga.contentauditid = (select isnull(max(auditid),0) from contentaudits where contentid = ii.contentid)
	from inserted ii
	inner join gridaudits ga on ii.auditid = ga.auditid
end
go
-- Display the status of Trigger alter
IF (@@Error = 0) PRINT 'Trigger Creation: tr_GridAudits_insert Succeeded'
ELSE PRINT 'Trigger Creation: tr_GridAudits_insert Error on Creation'
GO


/****** Object:  Index [IX_GridAuditsContentAuditID]    Script Date: 06/08/2011 11:13:09 ******/
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE NONCLUSTERED INDEX [IX_GridAuditsContentAuditID] ON [dbo].[GridAudits] 
(
	[ContentAuditID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
-- Display the status of index creation
IF (@@Error = 0) PRINT 'Index Creation: IX_GridAuditsContentAuditID Succeeded'
ELSE PRINT 'Index Creation: IX_GridAuditsContentAuditID Error on Creation'
GO

/****** Object:  StoredProcedure [getContentAuditsSummaryByItemID] ******/
/*
getContentAuditsSummaryByItemID 146,146,0
getContentAuditsSummaryByItemID 30,30,0
getContentAuditsSummaryByItemID 9,9,0
getContentAuditsSummaryByItemID 146,146,1
*/	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2012 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
create PROCEDURE [dbo].[getContentAuditsSummaryByItemID]
(
	@ProcedureItemID int,
	@SelectedItemID int,
	@IncludeDeletedChildren int,
	@DTS datetime
)

WITH EXECUTE AS OWNER
AS
begin
DECLARE @Chrono TABLE
(
[AuditID] bigint,
[ContentID] int,
[Number] nvarchar(max),
[Text] nvarchar(max),
[Type] int,
[FormatID] int,
[Config] nvarchar(max),
[DTS] datetime,
[UserID] nvarchar(max),
[DeleteStatus] int,
[ActionDTS] datetime,
[ActionWhat] nvarchar(max),
[ActionWhen] datetime,
[Path] nvarchar(max),
ItemID int,
TypeName nvarchar(max),
ordinalpath nvarchar(max)
)
insert into @Chrono
select * from vefn_GetMyChronology(@ProcedureItemID,@SelectedItemID,@IncludeDeletedChildren,@DTS)
select * from @Chrono
where AuditID is null OR AuditID in (select min(auditID) from @Chrono group by contentid UNION select max(auditID) from @Chrono group by contentid)
order by OrdinalPath, contentid,auditid
--select z.* from
--(
--select contentid,min(auditid) auditid from
--(
--	select [AuditID],[ContentID],[Number],[Text],[Type],[FormatID],[Config],[DTS],[UserID],[DeleteStatus],[ActionDTS],[ActionWhat],[ActionWhen],[Path],ItemID,TypeName
--	from
--	(
--	select
--	case
--	when lastauditid is null then 'Added'
--	when r.deletestatus > 0 then 'Deleted'
--	when lastauditid = -1 then 'Changed'
--	when DeletedAuditID is not null then 'Restored'
----	when DeletedAuditID is not null and lastauditid = deletedauditid then 'Restored'
--	else 'Changed'
--	end actionwhat
--,actiondts actionwhen
----	,case
----	when lastauditid is null then dts
----	when r.deletestatus > 0 then ActionDTS
----	when lastauditid = -1 then dts
----	when DeletedAuditID is not null then ActionDTS
----	else dts
----	end actionwhen
--,(select dbo.vefn_GetStepTypeNameByItemIDandTypeID(t.itemid,r.type)) TypeName
--	,*
--	from vefn_tblchilditems (@ProcedureItemID,@SelectedItemID,@IncludeDeletedChildren) t
--	inner join vefn_chronologyreport(@ProcedureItemID) r
--	on t.icontentid = r.contentid
----	where ActionDTS > procdts or dts > procdts
--	) ah
--) x
--group by contentid
--) y
--inner join
--(
--	select [AuditID],[ContentID],[Number],[Text],[Type],[FormatID],[Config],[DTS],[UserID],[DeleteStatus],[ActionDTS],[ActionWhat],[ActionWhen],[Path],ItemID,TypeName,ordinalpath
--	from
--	(
--	select
--	case
--	when lastauditid is null then 'Added'
--	when r.deletestatus > 0 then 'Deleted'
--	when lastauditid = -1 then 'Changed'
--	when DeletedAuditID is not null then 'Restored'
----	when DeletedAuditID is not null and lastauditid = deletedauditid then 'Restored'
--	else 'Changed'
--	end actionwhat
--,actiondts actionwhen
----	,case
----	when lastauditid is null then dts
----	when r.deletestatus > 0 then ActionDTS
----	when lastauditid = -1 then dts
----	when DeletedAuditID is not null then ActionDTS
----	else dts
----	end actionwhen
--,(select dbo.vefn_GetStepTypeNameByItemIDandTypeID(t.itemid,r.type)) TypeName
--	,*
--	from vefn_tblchilditems (@ProcedureItemID,@SelectedItemID,@IncludeDeletedChildren) t
--	inner join vefn_chronologyreport(@ProcedureItemID) r
--	on t.icontentid = r.contentid
----	where ActionDTS > procdts or dts > procdts
--	) ah
--) z on y.contentid = z.contentid and y.auditid = z.auditid
--union
--select z.* from
--(
--select contentid,max(auditid) auditid from
--(
--	select [AuditID],[ContentID],[Number],[Text],[Type],[FormatID],[Config],[DTS],[UserID],[DeleteStatus],[ActionDTS],[ActionWhat],[ActionWhen],[Path],ItemID,TypeName
--	from
--	(
--	select
--	case
--	when lastauditid is null then 'Added'
--	when r.deletestatus > 0 then 'Deleted'
--	when lastauditid = -1 then 'Changed'
--	when DeletedAuditID is not null then 'Restored'
----	when DeletedAuditID is not null and lastauditid = deletedauditid then 'Restored'
--	else 'Changed'
--	end actionwhat
--,actiondts actionwhen
----	,case
----	when lastauditid is null then dts
----	when r.deletestatus > 0 then ActionDTS
----	when lastauditid = -1 then dts
----	when DeletedAuditID is not null then ActionDTS
----	else dts
----	end actionwhen
--,(select dbo.vefn_GetStepTypeNameByItemIDandTypeID(t.itemid,r.type)) TypeName
--	,*
--	from vefn_tblchilditems (@ProcedureItemID,@SelectedItemID,@IncludeDeletedChildren) t
--	inner join vefn_chronologyreport(@ProcedureItemID) r
--	on t.icontentid = r.contentid
----	where ActionDTS > procdts or dts > procdts
--	) ah
--) x
--group by contentid
--) y
--inner join
--(
--	select [AuditID],[ContentID],[Number],[Text],[Type],[FormatID],[Config],[DTS],[UserID],[DeleteStatus],[ActionDTS],[ActionWhat],[ActionWhen],[Path],ItemID,TypeName,ordinalpath
--	from
--	(
--	select
--	case
--	when lastauditid is null then 'Added'
--	when r.deletestatus > 0 then 'Deleted'
--	when lastauditid = -1 then 'Changed'
--	when DeletedAuditID is not null then 'Restored'
----	when DeletedAuditID is not null and lastauditid = deletedauditid then 'Restored'
--	else 'Changed'
--	end actionwhat
--,actiondts actionwhen
----	,case
----	when lastauditid is null then dts
----	when r.deletestatus > 0 then ActionDTS
----	when lastauditid = -1 then dts
----	when DeletedAuditID is not null then ActionDTS
----	else dts
----	end actionwhen
--,(select dbo.vefn_GetStepTypeNameByItemIDandTypeID(t.itemid,r.type)) TypeName
--	,*
--	from vefn_tblchilditems (@ProcedureItemID,@SelectedItemID,@IncludeDeletedChildren) t
--	inner join vefn_chronologyreport(@ProcedureItemID) r
--	on t.icontentid = r.contentid
----	where ActionDTS > procdts or dts > procdts
--	) ah
--) z on y.contentid = z.contentid and y.auditid = z.auditid
--	order by OrdinalPath, contentid,auditid--actionwhen
	RETURN
end
-- Display the status of Trigger alter
IF (@@Error = 0) PRINT 'StoredProcedure [getAnnotationAuditsSummaryByItemID] Succeeded'
ELSE PRINT 'StoredProcedure [getAnnotationAuditsSummaryByItemID] Error on Creation'
go


ALTER PROCEDURE [dbo].[getContentAuditsByContentID]
(
	@ContentID int
)

WITH EXECUTE AS OWNER
AS
	SELECT
		[AuditID],
		[ContentID],
		[Number],
		[Text],
		[Type],
		[FormatID],
		[Config],
		[DTS],
		[UserID],
		[DeleteStatus],
		case when auditid = (select min(auditid) from contentaudits where contentid = @ContentID) then 'Original'
		when deletestatus > 0 then 'Deleted'
		else 'Changed' end ActionWhat
	FROM [ContentAudits]
	WHERE ContentID = @ContentID
	ORDER BY AuditID DESC
	RETURN
go

--NEW STUFF 1
GO
/****** Object:  StoredProcedure [dbo].[getContent]    Script Date: 07/07/2011 15:46:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
	
ALTER PROCEDURE [dbo].[getContent]

(
	@ContentID int
)
WITH EXECUTE AS OWNER
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 [Grids] WHERE [Grids].[ContentID]=[Contents].[ContentID]) [GridCount],
		(SELECT COUNT(*) FROM [Images] WHERE [Images].[ContentID]=[Contents].[ContentID]) [ImageCount],
		(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],
		[Documents].[FileExtension] [Document_FileExtension]
	FROM [Entries]
		JOIN [Documents]  ON
			[Documents].[DocID]=[Entries].[DocID]
	WHERE
		[Entries].[ContentID]=@ContentID


	SELECT
		[Grids].[ContentID],
		[Grids].[Data],
		[Grids].[Config],
		[Grids].[DTS],
		[Grids].[UserID],
		[Grids].[LastChanged]
	FROM [Grids]
	WHERE
		[Grids].[ContentID]=@ContentID


	SELECT
		[Images].[ContentID],
		[Images].[ImageType],
		[Images].[FileName],
		[Images].[Data],
		[Images].[Config],
		[Images].[DTS],
		[Images].[UserID],
		[Images].[LastChanged]
	FROM [Images]
	WHERE
		[Images].[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].[IsRange],
		[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]
		LEFT JOIN [Items] [Items_RangeID] ON
			[Items_RangeID].[ItemID]=[Transitions].[RangeID]
		LEFT 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