1. SQL / Говнокод #20211

    −41

    1. 01
    2. 02
    3. 03
    4. 04
    5. 05
    6. 06
    7. 07
    8. 08
    9. 09
    10. 10
    11. 11
    12. 12
    13. 13
    14. 14
    15. 15
    16. 16
    17. 17
    18. 18
    19. 19
    20. 20
    21. 21
    22. 22
    23. 23
    24. 24
    25. 25
    26. 26
    27. 27
    28. 28
    29. 29
    30. 30
    31. 31
    32. 32
    33. 33
    34. 34
    35. 35
    36. 36
    37. 37
    38. 38
    39. 39
    40. 40
    41. 41
    42. 42
    43. 43
    44. 44
    45. 45
    46. 46
    47. 47
    48. 48
    49. 49
    50. 50
    51. 51
    52. 52
    53. 53
    54. 54
    55. 55
    56. 56
    57. 57
    58. 58
    59. 59
    60. 60
    61. 61
    62. 62
    63. 63
    64. 64
    65. 65
    66. 66
    67. 67
    68. 68
    69. 69
    70. 70
    71. 71
    72. 72
    73. 73
    74. 74
    75. 75
    76. 76
    77. 77
    78. 78
    79. 79
    80. 80
    81. 81
    82. 82
    83. 83
    84. 84
    85. 85
    86. 86
    87. 87
    88. 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

    Запостил: laMer007, 15 Июня 2016

    Комментарии (6) RSS

    Добавить комментарий