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

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