-- Oracle
with object_tree(pcmid, cmid, chemin, niveau, name, version, created, modified, disabled, classid, owner, obj_type_name)
as
(
-- membre racine
select a.pcmid, a.cmid, '>'||b.name as chemin, 1 as niveau, b.name, a.version, a.created, a.modified, a.disabled, a.classid,
a.owner, c.name as obj_type_name
from CMOBJECTS a,
(select cmid, name from CMOBJNAMES where isdefault=1) b, CMCLASSES c
WHERE a.cmid=b.cmid
AND a.classid=c.classid
AND a.cmid=0
UNION ALL
-- recursive query
select a1.pcmid, a1.cmid, chemin||'>'||b.name as chemin, niveau++1 as niveau, b.name, a1.version, a1.created, a1.modified,
a1.disabled, a1.classid, a1.owner, c.name as obj_type_name
from CMOBJECTS a1,
(select cmid, name from CMOBJNAMES where isdefault=1) b, CMCLASSES c, object_tree d
WHERE a1.cmid=b.cmid
AND a1.classid=c.classid
AND a1.pcmid=d.cmid
AND a1.cmid<>0
),
lastExecution(PCMID, LastExecDate)
as
(
select oh.PCMID, max(ACTEXECTIME) LastExecDate from CMOBJPROPS5 h, CMOBJECTS oh
where h.CMID=oh.CMID
and oh.CLASSID=111
group by oh.PCMID
),
objOutput(reportID, lastReportVersionID, lastReportVersionModified, ouputID, outputFormat, outputLocale, lastReportVersionType)
as
(
select lastReportVersion.PCMID reportID, output.PCMID reportVersionID, lastReportVersion.lastModified reportVersionModified, outputFmt.CMID outputID,
CASE
WHEN outputFmt.FORMAT=0 THEN 'CSV'
WHEN outputFmt.FORMAT=1 THEN 'HTML'
WHEN outputFmt.FORMAT=2 THEN 'PDF'
WHEN outputFmt.FORMAT=4 THEN 'XML'
WHEN outputFmt.FORMAT=5 THEN 'XHTML'
WHEN outputFmt.FORMAT=10 THEN 'HTML bis'
WHEN outputFmt.FORMAT=11 THEN 'XLSX'
ELSE to_char(outputFmt.FORMAT)
end outputFormat,
l.LOCALE outputLocale, lastReportVersion.CLASSID
from CMOBJPROPS54 outputFmt
INNER JOIN CMOBJECTS output
ON outputFmt.CMID=output.CMID and output.CLASSID=27
LEFT OUTER JOIN CMLOCALES l
ON outputFmt.OLOCALEID=l.LOCALEID
INNER JOIN (select o.PCMID, max(o.CMID) CMID, max(o.modified) lastModified, max(o.CLASSID) CLASSID from CMOBJECTS o
where o.CLASSID=17 group by o.PCMID) lastReportVersion
ON lastReportVersion.CMID=output.PCMID
),
scheduling(reportID, schedID, Periodicite)
as
(
select scheduleObj.PCMID reportID, scheduleObj.CMID schedID,
CASE
WHEN s.TYPE in (0, 9) THEN 'J'
WHEN s.TYPE in (4, 12) THEN 'H'
WHEN s.TYPE in (1, 2, 10, 11) THEN 'M'
WHEN s.TYPE in (5, 6, 13, 14) THEN 'A'
WHEN s.TYPE in (7) THEN 'Trigger'
ELSE to_char(s.TYPE)
END Periodicite
from CMOBJECTS scheduleObj
INNER JOIN CMOBJPROPS2 s ON scheduleObj.CMID=s.CMID
),
sourcePack(reportID, packName)
as
(
select r.CMID reportID, n.NAME packName from
CMREFNOORD1 r
INNER JOIN CMOBJECTS o ON r.CMID=o.CMID
INNER JOIN CMOBJNAMES n ON r.REFCMID=n.CMID
where r.propid=31
and n.isdefault=1
and o.CLASSID in (10, 234)
),
jobReportList(jobID, jobStepID, reportID)
as
(
select jobStep.PCMID jobID, jobStep.CMID jobStepID, r.REFCMID reportID from
CMREFNOORD1 r
INNER JOIN CMOBJECTS jobStep ON r.CMID=jobStep.CMID
INNER JOIN CMOBJECTS o ON r.REFCMID=o.CMID
INNER JOIN CMOBJNAMES n ON r.REFCMID=n.CMID
where r.propid=10
and n.isdefault=1
and o.CLASSID in (10, 19, 234)
),
destschedule(delivoptID, toEmail, ccEmail)
as
(
select x.CMID, xtomail.toEmail, xccmail.ccEmail
from
(select CMID,
xmltype(
case
when INSTR(delivoptions,'toAddress',1)=0 then to_clob('no data')
else
replace(replace(SUBSTR(delivoptions,INSTR(delivoptions,'',INSTR(delivoptions,'toAddress',1))+8-INSTR(delivoptions,'no data')
else
replace(replace(SUBSTR(delivoptions,INSTR(delivoptions,'',INSTR(delivoptions,'ccAddress',1))+8-INSTR(delivoptions,'no data')
else
replace(replace(SUBSTR(delivoptions,INSTR(delivoptions,'',INSTR(delivoptions,'toAddress',1))+8-INSTR(delivoptions,'no data')
else
replace(replace(SUBSTR(delivoptions,INSTR(delivoptions,'',INSTR(delivoptions,'ccAddress',1))+8-INSTR(delivoptions,'/>Team Content>%'
-- SQL Server
WITH
objTree(pcmid, cmid, chemin, niveau, name, version, created, modified, disabled, classid, owner)
as
(
-- membre racine
select a.pcmid, a.cmid, cast('>'+b.name as nvarchar(1000)) as chemin, 1 as niveau, b.name, a.version, a.created, a.modified, a.disabled, a.classid,
a.owner
from dbo.CMOBJECTS a WITH (NOLOCK),
(select cmid, name from dbo.CMOBJNAMES WITH (NOLOCK) where isdefault=1) b
WHERE a.cmid=b.cmid
AND a.cmid=0
UNION ALL
-- recursive query
select a1.pcmid, a1.cmid, cast(chemin+'>'+b.name as nvarchar(1000)) as chemin, niveau++1 as niveau, b.name, a1.version, a1.created, a1.modified,
a1.disabled, a1.classid, a1.owner
from dbo.CMOBJECTS a1 WITH (NOLOCK),
(select cmid, name from dbo.CMOBJNAMES WITH (NOLOCK) where isdefault=1) as b, objTree d
WHERE a1.cmid=b.cmid
AND a1.pcmid=d.cmid
AND (a1.DISABLED<>1 or a1.DISABLED is null)
AND a1.cmid<>0
),
objList(cmid, pcmid, objet, objRefName, code_type_obj, type_objet, display_sequence, objet_pere, delivoptions, toAddress, toAddressXML, toGroup, toGroupXML, ccAddress, ccAddressXML, ccGroup, ccGroupXML, bccAddress, bccAddressXML, bccGroup, bccGroupXML, paramName, ORD, paramValue, paramValueXML, objet_pere_active_schedule, LastExecDate) as
(select o.cmid, o.pcmid, objname.name objet, reference_objet.objRefName, o.classid code_Type_obj, type_obj.name type_objet, o.DISPSEQ, objpere.name objet_pere, delivopt.delivoptions,
case when CHARINDEX('toAddress',cast(delivopt.delivoptions as varchar(max)),1)=0 then null else '"'+SUBSTRING(cast(delivopt.delivoptions as varchar(max)), CHARINDEX('toAddress',cast(delivopt.delivoptions as varchar(max)),1),CHARINDEX('',cast(delivopt.delivoptions as varchar(max)),CHARINDEX('toAddress',cast(delivopt.delivoptions as varchar(max)),1))-CHARINDEX('toAddress',cast(delivopt.delivoptions as varchar(max)),1))+'"' end toAddress,
case when CHARINDEX('toAddress',cast(delivopt.delivoptions as varchar(max)),1)=0 then null else cast(replace(replace(SUBSTRING(cast(delivopt.delivoptions as varchar(max)), CHARINDEX('',cast(delivopt.delivoptions as varchar(max)),CHARINDEX('toAddress',cast(delivopt.delivoptions as varchar(max)),1))+8-CHARINDEX('',cast(delivopt.delivoptions as varchar(max)),CHARINDEX('to;',cast(delivopt.delivoptions as varchar(max)),1))+8-CHARINDEX('',cast(delivopt.delivoptions as varchar(max)),CHARINDEX('to;',cast(delivopt.delivoptions as varchar(max)),1))+8-CHARINDEX('',cast(delivopt.delivoptions as varchar(max)),CHARINDEX('ccAddress',cast(delivopt.delivoptions as varchar(max)),1))-CHARINDEX('ccAddress',cast(delivopt.delivoptions as varchar(max)),1))+'"' end ccAddress,
case when CHARINDEX('ccAddress',cast(delivopt.delivoptions as varchar(max)),1)=0 then null else cast(replace(replace(SUBSTRING(cast(delivopt.delivoptions as varchar(max)), CHARINDEX('',cast(delivopt.delivoptions as varchar(max)),CHARINDEX('ccAddress',cast(delivopt.delivoptions as varchar(max)),1))+8-CHARINDEX('',cast(delivopt.delivoptions as varchar(max)),CHARINDEX('cc;',cast(delivopt.delivoptions as varchar(max)),1))-CHARINDEX('cc;',cast(delivopt.delivoptions as varchar(max)),1))+'"' end ccGroup,
case when CHARINDEX('cc;',cast(delivopt.delivoptions as varchar(max)),1)=0 then null else cast(replace(replace(SUBSTRING(cast(delivopt.delivoptions as varchar(max)), CHARINDEX('',cast(delivopt.delivoptions as varchar(max)),CHARINDEX('cc;',cast(delivopt.delivoptions as varchar(max)),1))+8-CHARINDEX('',cast(delivopt.delivoptions as varchar(max)),CHARINDEX('bccAddress',cast(delivopt.delivoptions as varchar(max)),1))-CHARINDEX('bccAddress',cast(delivopt.delivoptions as varchar(max)),1))+'"' end bccAddress,
case when CHARINDEX('bccAddress',cast(delivopt.delivoptions as varchar(max)),1)=0 then null else cast(replace(replace(SUBSTRING(cast(delivopt.delivoptions as varchar(max)), CHARINDEX('',cast(delivopt.delivoptions as varchar(max)),CHARINDEX('bccAddress',cast(delivopt.delivoptions as varchar(max)),1))+8-CHARINDEX('',cast(delivopt.delivoptions as varchar(max)),CHARINDEX('bcc;',cast(delivopt.delivoptions as varchar(max)),1))-CHARINDEX('bcc;',cast(delivopt.delivoptions as varchar(max)),1))+'"' end bccGroup,
case when CHARINDEX('bcc;',cast(delivopt.delivoptions as varchar(max)),1)=0 then null else cast(replace(replace(SUBSTRING(cast(delivopt.delivoptions as varchar(max)), CHARINDEX('',cast(delivopt.delivoptions as varchar(max)),CHARINDEX('bcc;',cast(delivopt.delivoptions as varchar(max)),1))+8-CHARINDEX('true%'
),
DistribListes(idListe, memberOrd, nomListe, dateCreationListe, dateModificationListe, nomMembre, emailContact) as
(select o.CMID idListe, l.ORD memberOrd, n.NAME nomListe, o.CREATED dateCreationListe, o.MODIFIED dateModificationListe,
coalesce(nUser.NAME, nContact.NAME) nomMembre, c.EMAIL emailContact
from CMREFORD1 l WITH (NOLOCK) LEFT OUTER JOIN CMOBJPROPS33 nUser WITH (NOLOCK) on l.REFCMID=nUser.CMID,
CMOBJECTS o WITH (NOLOCK), CMOBJNAMES n WITH (NOLOCK), CMOBJNAMES nContact WITH (NOLOCK), CMOBJPROPS1 c WITH (NOLOCK)
where l.CMID=o.CMID
and o.CMID=n.CMID
and l.REFCMID=nContact.CMID
and l.REFCMID=c.CMID
and nContact.ISDEFAULT=1
and n.ISDEFAULT=1
and o.CLASSID=15
and l.PROPID=27
),
DistribListesAgg(idListe, nomListe, dateCreationListe, dateModificationListe, members, emails) as
(select idListe, nomListe, dateCreationListe, dateModificationListe, stuff(
(select ',' + nomMembre
from DistribListes l2
where l2.idListe=l1.idListe
order by memberOrd
for xml path(''), type).value('.', 'varchar(8000)')
,1,1,'') as members,
stuff(
(select ',' + emailContact
from DistribListes l2
where l2.idListe=l1.idListe
order by memberOrd
for xml path(''), type).value('.', 'varchar(8000)')
,1,1,'') as emails
from DistribListes l1
group by idListe, nomListe, dateCreationListe, dateModificationListe
),
endListAgg (IdObjet, IdObjet_pere, objet, objRefName, code_type_obj, type_objet, display_sequence, objet_pere, chemin, delivoptions,
toAddressXML, toAddress, toGroupXML, toGroup, toGroupClean, ccAddressXML, ccAddress, ccGroupXML, ccGroup, ccGroupClean,
bccAddressXML, bccAddress, bccGroupXML, bccGroup, bccGroupClean, paramName, ORD, paramValue, useValue,
objet_pere_active_schedule, LastExecDate) as
(select l.cmid, l.pcmid, objet, objRefName, code_type_obj, type_objet, display_sequence, objet_pere, t.chemin, delivoptions,
toAddressXML,
stuff(
(select ',' + useRef.value('(.)[1]','varchar(8000)')
from objList l2
cross apply l2.toAddressXML.nodes('/value/item') as r(useRef)
where l2.cmid=l.cmid
and l2.paramName=l.paramName
order by ord
for xml path(''), type).value('.', 'varchar(8000)')
,1,1,'') toAddress,
toGroupXML,
stuff(
(select ',' + useRef.value('(.)[1]','varchar(8000)')
from objList l2
cross apply l2.toGroupXML.nodes('/value/item') as r(useRef)
where l2.cmid=l.cmid
and l2.paramName=l.paramName
order by ord
for xml path(''), type).value('.', 'varchar(8000)')
,1,1,'') toGroup,
stuff(
(select ',' + replace(right(useRef.value('(.)[1]','varchar(8000)'), charindex(':',reverse(useRef.value('(.)[1]','varchar(8000)')))-1),'")','')
from objList l2
cross apply l2.toGroupXML.nodes('/value/item') as r(useRef)
where l2.cmid=l.cmid
and l2.paramName=l.paramName
order by ord
for xml path(''), type).value('.', 'varchar(8000)')
,1,1,'') toGroupClean,
ccAddressXML,
stuff(
(select ',' + useRef.value('(.)[1]','varchar(8000)')
from objList l2
cross apply l2.ccAddressXML.nodes('/value/item') as r(useRef)
where l2.cmid=l.cmid
and l2.paramName=l.paramName
order by ord
for xml path(''), type).value('.', 'varchar(8000)')
,1,1,'') ccAddress,
ccGroupXML,
stuff(
(select ',' + useRef.value('(.)[1]','varchar(8000)')
from objList l2
cross apply l2.ccGroupXML.nodes('/value/item') as r(useRef)
where l2.cmid=l.cmid
and l2.paramName=l.paramName
order by ord
for xml path(''), type).value('.', 'varchar(8000)')
,1,1,'') ccGroup,
stuff(
(select ',' + replace(right(useRef.value('(.)[1]','varchar(8000)'), charindex(':',reverse(useRef.value('(.)[1]','varchar(8000)')))-1),'")','')
from objList l2
cross apply l2.ccGroupXML.nodes('/value/item') as r(useRef)
where l2.cmid=l.cmid
and l2.paramName=l.paramName
order by ord
for xml path(''), type).value('.', 'varchar(8000)')
,1,1,'') ccGroupClean,
bccAddressXML,
stuff(
(select ',' + useRef.value('(.)[1]','varchar(8000)')
from objList l2
cross apply l2.bccAddressXML.nodes('/value/item') as r(useRef)
where l2.cmid=l.cmid
and l2.paramName=l.paramName
order by ord
for xml path(''), type).value('.', 'varchar(8000)')
,1,1,'') bccAddress,
bccGroupXML,
stuff(
(select ',' + useRef.value('(.)[1]','varchar(8000)')
from objList l2
cross apply l2.bccGroupXML.nodes('/value/item') as r(useRef)
where l2.cmid=l.cmid
and l2.paramName=l.paramName
order by ord
for xml path(''), type).value('.', 'varchar(8000)')
,1,1,'') bccGroup,
stuff(
(select ',' + replace(right(useRef.value('(.)[1]','varchar(8000)'), charindex(':',reverse(useRef.value('(.)[1]','varchar(8000)')))-1),'")','')
from objList l2
cross apply l2.bccGroupXML.nodes('/value/item') as r(useRef)
where l2.cmid=l.cmid
and l2.paramName=l.paramName
order by ord
for xml path(''), type).value('.', 'varchar(8000)')
,1,1,'') bccGroupClean,
paramName, ORD,
paramValue,
stuff(
(select ',' + useRef.value('use[1]','varchar(8000)')
from objList l2
cross apply l2.paramValueXML.nodes('/value/item') as r(useRef)
where l2.cmid=l.cmid
and l2.paramName=l.paramName
order by ord
for xml path(''), type).value('.', 'varchar(8000)')
,1,1,'') useValue,
objet_pere_active_schedule,
LastExecDate
from objList l, objTree t
where l.cmid = t.cmid
)
select IdObjet, IdObjet_pere, objet, objRefName, code_type_obj, type_objet, display_sequence, objet_pere, chemin, delivoptions,
toAddressXML, toAddress, toGroupXML,
toGroup, toGroupClean, distribTo.dateCreationListe toGroupDateCreationListe, distribTo.dateModificationListe toGroupDateModificationListe,
distribTo.members toGroupMembers, distribTo.emails toGroupMembersEmails,
ccAddressXML, ccAddress, ccGroupXML,
ccGroup, ccGroupClean, distribCc.dateCreationListe ccGroupDateCreationListe, distribCc.dateModificationListe ccGroupDateModificationListe,
distribCc.members ccGroupMembers, distribCc.emails ccGroupMembersEmails,
bccAddressXML, bccAddress, bccGroupXML,
bccGroup, bccGroupClean, distribBcc.dateCreationListe bccGroupDateCreationListe, distribBcc.dateModificationListe bccGroupDateModificationListe,
distribBcc.members bccGroupMembers, distribBcc.emails bccGroupMembersEmails,
paramName, ORD, paramValue, useValue,
objet_pere_active_schedule, LastExecDate
from endListAgg a LEFT OUTER JOIN DistribListesAgg distribTo ON a.toGroupClean=distribTo.nomListe
LEFT OUTER JOIN DistribListesAgg distribCc ON a.ccGroupClean=distribCc.nomListe
LEFT OUTER JOIN DistribListesAgg distribBcc ON a.bccGroupClean=distribBcc.nomListe
where
chemin like '>/>Team Content>%'
order by IdObjet_pere, display_sequence, IdObjet, ORD