/****** Object:  UserDefinedFunction [dbo].[vefn_ChronologyReportPast]    Script Date: 11/01/2011 18:14:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
select * from vefn_chronologyreportpast(41)
*/
/****** Object:  UserDefinedFunction [dbo].[vefn_ChronologyReportPast]    Script Date: 05/18/2011 11:20:48 ******/
create function [dbo].[vefn_ChronologyReportPast](@ProcItemID int)
returns @Report table
(
AuditID bigint,
ContentID int,
Number nvarchar(512),
Text nvarchar(max),
Type int,
FormatID int,
Config nvarchar(max),
DTS datetime,
UserID nvarchar(200),
DeleteStatus int,
ActionDTS datetime,
ItemDTS datetime,
LastAuditID int,
DeletedAuditID int
)
WITH EXECUTE AS OWNER
AS
BEGIN
	insert into @Report
select * from
(
	SELECT 
	[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)
--end added jcb 20111028_1827
	UNION
	SELECT 
	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)
) ah
where cadts <= (select dts from items where itemid = @ProcItemID) 
	order by cadts,ActionDTS
	return
end
go

/****** Object:  StoredProcedure [dbo].[getContentAuditsChronologyPastByItemID]    Script Date: 11/01/2011 18:13:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/****** Object:  StoredProcedure [getContentAuditsChronologyPastByItemID] ******/
/*
getContentAuditsChronologyPastByItemID 146,146,0
getContentAuditsChronologyPastByItemID 41,41,0
getContentAuditsChronologyPastByItemID 9,9,0
getContentAuditsChronologyPastByItemID 146,146,1
*/	
create PROCEDURE [dbo].[getContentAuditsChronologyPastByItemID]
(
	@ProcedureItemID int,
	@SelectedItemID int,
	@IncludeDeletedChildren int
)

WITH EXECUTE AS OWNER
AS
begin
	select [AuditID],[ContentID],[Number],[Text],[Type],[FormatID],[Config],[DTS],[UserID],[DeleteStatus],[ActionDTS],[ActionWhat],[ActionWhen],[Path],ItemID
	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
	,* 
	from vefn_tblchilditems (@ProcedureItemID,@SelectedItemID,@IncludeDeletedChildren) t
	inner join vefn_chronologyreportpast(@ProcedureItemID) r
	on t.icontentid = r.contentid
--	where ActionDTS > procdts or dts > procdts
	) ah
	order by OrdinalPath, contentid,auditid--actionwhen
	RETURN
end
go

/****** Object:  StoredProcedure [dbo].[getContentAuditsSummaryPastByItemID]    Script Date: 11/01/2011 18:17:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/****** Object:  StoredProcedure [getContentAuditsSummaryPastByItemID] ******/
/*
getContentAuditsSummaryPastByItemID 146,146,0
getContentAuditsSummaryPastByItemID 41,41,0
getContentAuditsSummaryPastByItemID 9,9,0
getContentAuditsSummaryPastByItemID 146,146,1
*/	
create PROCEDURE [dbo].[getContentAuditsSummaryPastByItemID]
(
	@ProcedureItemID int,
	@SelectedItemID int,
	@IncludeDeletedChildren int
)

WITH EXECUTE AS OWNER
AS
begin

select z.* from
(
select contentid,min(auditid) auditid from
(
	select [AuditID],[ContentID],[Number],[Text],[Type],[FormatID],[Config],[DTS],[UserID],[DeleteStatus],[ActionDTS],[ActionWhat],[ActionWhen],[Path],ItemID
	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
	,* 
	from vefn_tblchilditems (@ProcedureItemID,@SelectedItemID,@IncludeDeletedChildren) t
	inner join vefn_chronologyreportpast(@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,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
	,* 
	from vefn_tblchilditems (@ProcedureItemID,@SelectedItemID,@IncludeDeletedChildren) t
	inner join vefn_chronologyreportpast(@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
	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
	,* 
	from vefn_tblchilditems (@ProcedureItemID,@SelectedItemID,@IncludeDeletedChildren) t
	inner join vefn_chronologyreportpast(@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,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
	,* 
	from vefn_tblchilditems (@ProcedureItemID,@SelectedItemID,@IncludeDeletedChildren) t
	inner join vefn_chronologyreportpast(@ProcedureItemID) r
	on t.icontentid = r.contentid
--	where ActionDTS > procdts or dts > procdts
	) ah
) z on y.contentid = z.contentid and y.auditid = z.auditid
	order by OrdinalPath, contentid,auditid--actionwhen
	RETURN
end
go

/****** Object:  StoredProcedure [dbo].[getAnnotationAuditsChronologyByItemID]    Script Date: 11/01/2011 21:31:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/****** Object:  StoredProcedure [getAnnotationAuditsChronologyPastByItemID] ******/
/*
getAnnotationAuditsChronologyPastByItemID 3,1230
getAnnotationAuditsChronologyPastByItemID 30,8570
getAnnotationAuditsChronologyPastByItemID 30,8513
getAnnotationAuditsChronologyPastByItemID 30,8505
*/
create procedure [dbo].[getAnnotationAuditsChronologyPastByItemID]
(
	@ProcItemID int,
	@ItemID int
)
WITH EXECUTE AS OWNER
AS
begin
	select 
	case
	when lastauditid is null and dts > itemdts then 'Added'
	when deletestatus > 0 then 'Deleted'
	when lastauditid = deletedauditid then 'Restored'
	else 'Changed'
	end ActionWhat
	,case
	when lastauditid is null and dts > itemdts then dts
	when deletestatus > 0 then ActionDTS
	when lastauditid = deletedauditid then ActionDTS
	else dts
	end ActionWhen
	,* 
	from
	(
	select 
	cast(ident_current('annotationaudits') + 1 as bigint) auditid
--	(select max(auditid) + 1 from annotationaudits) auditid
--	0 auditid
	,aa.annotationid
	,aa.itemid
	,aa.typeid
	,aa.rtftext
	,aa.searchtext
	,aa.config
	,aa.dts
	,aa.userid
	,0 deletestatus
	,aa.ActionDTS
	,ii.contentid icontentid
	,(select min(dts) from tblitems where itemid = ii.itemid) ItemDTS
	,(select max(auditid) from annotationaudits where annotationid = aa.annotationid and itemid = ii.itemid) LastAuditID
	,(select max(auditid) from annotationaudits where annotationid = aa.annotationid and itemid = ii.itemid and deletestatus > 0 and aa.dts = dts) DeletedAuditID
	from tblannotations aa
	inner join items ii on aa.itemid = ii.itemid
	where aa.deletestatus = 0
	union
	select 
	aa.auditid
	,aa.annotationid
	,aa.itemid
	,aa.typeid
	,aa.rtftext
	,aa.searchtext
	,aa.config
	,aa.dts
	,aa.userid
	,aa.deletestatus
	,aa.ActionDTS
	,ii.contentid icontentid
	,(select min(dts) from tblitems where itemid = ii.itemid) ItemDTS
	,(select max(auditid) from annotationaudits where annotationid = aa.annotationid and itemid = ii.itemid and aa.auditid > auditid ) LastAuditID
	,(select max(auditid) from annotationaudits where annotationid = aa.annotationid and itemid = ii.itemid and aa.auditid > auditid and deletestatus > 0 and aa.dts = dts) DeletedAuditID
	from annotationaudits aa
	inner join items ii on aa.itemid = ii.itemid
	) ah
	where itemid in (select itemid from vefn_tblchilditems (@procitemid,@itemid,0))
and dts <= (select dts from items where itemid = @procitemid)
	order by annotationid,auditid--actionwhen
end
go