- 01
- 02
- 03
- 04
- 05
- 06
- 07
- 08
- 09
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
create procedure [dbo].[GetXML] (
@ExternalId varchar(255)
)
as
begin
declare @Contained int = 2;
declare @Sent int = 3;
declare @ResultAdmissionSubmissionNO int = 76;
declare @dcStateSent int = 7;
declare @dcStateInProgress int = 1;
declare @dcStateFinishedWithError int = 2;
declare @dcStateFinished int = 3;
with ExternalIdMain as (
select
ExternalId as ExternalId,
dc.DocCircId as GroupId,
dc.DocCircId as DocCircId
from
[dbo].[gate_DocCirculations] as gdc
inner join [dbo].[DocCirculations] as dc on dc.DocCircId = gdc.DocCircId and not exists
(select TOP 1 1 from [dbo].[DocCircConnection] as dcci where dcci.Parent = dc.DocCircId or dcci.Child = dc.DocCircId )
where ExternalId = @ExternalId and
dc.StateId in (@dcStateSent, @dcStateInProgress, @dcStateFinishedWithError, @dcStateFinished)
union all
select
ExternalId as ExternalId,
dcc0.Parent as GroupId,
dcc0.Child as DocCircId
from
--dcc0.Child is leaf
[dbo].[DocCircConnection] as dcc0
inner join [dbo].[DocCircConnection] as dcc1 on dcc0.Parent = dcc1.Child and dcc0.ConnectionType = @Contained and dcc1.ConnectionType = @Sent
inner join [dbo].[gate_DocCirculations] as gdc on dcc1.Parent = gdc.DocCircId
where ExternalId = @ExternalId
),
ExternalIdStates as (
select
eim.ExternalId as ExternalId,
eim.GroupId as GroupId,
dcsg.StateId as GroupState,
dcsg.[Description] as GroupStateDescription,
eim.DocCircId as DocCircId,
dcs.StateId as DocCircState,
dcs.[Description] as DocCircStateDescription,
dc.timeUpdate as [Date]
from
ExternalIdMain as eim
inner join dbo.DocCirculations as dc on eim.DocCircId = dc.DocCircId
inner join dbo.DocCirculations as dcg on eim.GroupId = dcg.DocCircId
inner join dbo.DocCircStates as dcs on dcs.StateId = dc.StateId
inner join dbo.DocCircStates as dcsg on dcsg.StateId = dcg.StateId
),
ExternalIdFull as (
select
ExternalId as ExternalId,
GroupId as GroupId,
GroupState as GroupState,
GroupStateDescription as GroupStateDescription,
eis.DocCircId as DocCircId,
DocCircState as DocCircState,
DocCircStateDescription as DocCircStateDescription,
[Date] as [Date],
[Filename] as [Filename]
from
ExternalIdStates as eis
left join dbo.Transactions as t on eis.DocCircId = t.DocCircId and t.idTranstype = @ResultAdmissionSubmissionNO
left join dbo.Docs as d on t.idTransaction = d.idTransaction
left join dbo.Contents as c on d.idContent = c.idContent
)
select ExternalId as "@Value",
(
select GroupId as "@GroupId", Max(GroupState) as "@GroupState", Max(GroupStateDescription) as "@GroupStateDescription",
(
select DocCircId as "@DocCircId", Max(DocCircState) as "@DocCircState", Max(DocCircStateDescription) as "@DocCircStateDescription", Max([Date]) as "@Date",
(
--"case when" for null filenames
case when
(exists (select TOP 1 [Filename] from ExternalIdFull as eif3 where eif3.DocCircId = eif2.DocCircId and [Filename] is not null))
then (select [Filename] as "@Value" from ExternalIdFull as eif3 where eif3.DocCircId = eif2.DocCircId FOR XML PATH('Filename'), type)
else N'' end
)
from ExternalIdFull as eif2 where eif2.GroupId = eif1.GroupId group by DocCircId FOR XML PATH('DocCirc'), type
)
from ExternalIdFull as eif1 where eif1.ExternalId = eif0.ExternalId group by GroupId FOR XML PATH('Group'), type
)
from ExternalIdFull as eif0 group by ExternalId FOR XML PATH('ExternalId'), Root('Root')
end