170 lines
5.0 KiB
MySQL
170 lines
5.0 KiB
MySQL
/****** 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 items where itemid = @procitemid)
|
|
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 items where itemid = @ProcItemID)
|
|
order by cadts,ActionDTS
|
|
return
|
|
end
|
|
go
|