/****** Object:  StoredProcedure [dbo].[getAnnotationAuditsChronologyByItemID]    Script Date: 10/28/2011 17:32:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/****** Object:  StoredProcedure [getAnnotationAuditsChronologyByItemID] ******/
/*
getAnnotationAuditsChronologyByItemID 3,1230
getAnnotationAuditsChronologyByItemID 30,8570
getAnnotationAuditsChronologyByItemID 30,8513
getAnnotationAuditsChronologyByItemID 30,8505
*/
ALTER procedure [dbo].[getAnnotationAuditsChronologyByItemID]
(
	@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 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 annotationid,auditid--actionwhen
end
go

/****** Object:  UserDefinedFunction [dbo].[vefn_ChronologyReport]    Script Date: 10/28/2011 18:22:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
select * from vefn_chronologyreport(41)
*/
/****** Object:  UserDefinedFunction [dbo].[vefn_ChronologyReport]    Script Date: 05/18/2011 11:20:48 ******/
ALTER function [dbo].[vefn_ChronologyReport](@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 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 cadts,ActionDTS
	return
end
go