with params as(
select
cast($1 as bigint) as p_lpu_id,
cast($2 as bigint) as p_lpubuilding_id,
cast($3 as bigint) as p_lpuunit_id, 
cast($4 as bigint) as p_lpusection_id, 
cast(to_timestamp($5,'dd.mm.yyyy') as timestamp) as p_begdate, 
cast(to_timestamp($6,'dd.mm.yyyy') as timestamp) as p_enddate
),
Row_usl as(
select Rowusl,RepDiag_FromDiag,RepDiag_ToDiag,UslugaComplex_code
from (values
/*(15,'O85',	'O85.9','A16.30.006'),
(15,'O85',	'O85.9','A16.30.006.001'),
(15,'O85',	'O85.9','A16.30.007'),
(15,'O85',	'O85.9','A16.30.007.001'),
(15,'O85',	'O85.9','A16.30.007.002'),
(15,'O85',	'O85.9','A16.30.007.003'),
(15,'O85',	'O85.9','A16.30.007.004'),
(15,'O85',	'O85.9','A16.30.043'),
(15,'O85',	'O85.9','A16.30.043.001'),
(15,'O85',	'O85.9','A16.30.043.003'),*/

(35,'Z22.8','Z22.8','A26.05.066.002'),
(35,'Z22.8','Z22.8','A26.08.008'),
(35,'Z22.8','Z22.8','A26.08.008.001'),
(35,'Z22.8','Z22.8','A26.08.045'),
(35,'Z22.8','Z22.8','A26.08.045.001'),
(35,'Z22.8','Z22.8','A26.08.046.002'),
(35,'Z22.8','Z22.8','A26.09.020.001'),
(35,'Z22.8','Z22.8','A26.09.043'),
(35,'Z22.8','Z22.8','A26.09.043.001'),
(35,'Z22.8','Z22.8','A26.09.044.002'),
(35,'Z22.8','Z22.8','A26.09.060.002'),
	
(38,'Z22.8','Z22.8','A26.05.066.002'),
(38,'Z22.8','Z22.8','A26.08.008'),
(38,'Z22.8','Z22.8','A26.08.008.001'),
(38,'Z22.8','Z22.8','A26.08.045'),
(38,'Z22.8','Z22.8','A26.08.045.001'),
(38,'Z22.8','Z22.8','A26.08.046.002'),
(38,'Z22.8','Z22.8','A26.09.020.001'),
(38,'Z22.8','Z22.8','A26.09.043'),
(38,'Z22.8','Z22.8','A26.09.043.001'),
(38,'Z22.8','Z22.8','A26.09.044.002'),
(38,'Z22.8','Z22.8','A26.09.060.002')
) as t_usl(Rowusl,RepDiag_FromDiag,RepDiag_ToDiag,UslugaComplex_code)
), 
RD_3000 as(
	select RepDiag_Row,RepDiag_Text1,RepDiag_Text2,RepDiag_Text3,RepDiag_FromDiag,RepDiag_ToDiag,child
	from(values
(1,'Гнойно-септические инфекции новорожденных','01',	'P36, P38, P39, G00, L00, L01, L02, L03, L08.0,8,9, M86.0,1,2,8,9, J12-J18','P36','P36.9',28),
(1,'Гнойно-септические инфекции новорожденных','01',	'P36, P38, P39, G00, L00, L01, L02, L03, L08.0,8,9, M86.0,1,2,8,9, J12-J18','P38','P38.9',28),
(1,'Гнойно-септические инфекции новорожденных','01',	'P36, P38, P39, G00, L00, L01, L02, L03, L08.0,8,9, M86.0,1,2,8,9, J12-J18','P39','P39.9',28),
(1,'Гнойно-септические инфекции новорожденных','01',	'P36, P38, P39, G00, L00, L01, L02, L03, L08.0,8,9, M86.0,1,2,8,9, J12-J18','G00','G00.9',28),
(1,'Гнойно-септические инфекции новорожденных','01',	'P36, P38, P39, G00, L00, L01, L02, L03, L08.0,8,9, M86.0,1,2,8,9, J12-J18','L00','L00.9',28),
(1,'Гнойно-септические инфекции новорожденных','01',	'P36, P38, P39, G00, L00, L01, L02, L03, L08.0,8,9, M86.0,1,2,8,9, J12-J18','L01','L01.9',28),
(1,'Гнойно-септические инфекции новорожденных','01',	'P36, P38, P39, G00, L00, L01, L02, L03, L08.0,8,9, M86.0,1,2,8,9, J12-J18','L02','L02.9',28),
(1,'Гнойно-септические инфекции новорожденных','01',	'P36, P38, P39, G00, L00, L01, L02, L03, L08.0,8,9, M86.0,1,2,8,9, J12-J18','L03','L03.9',28),
(1,'Гнойно-септические инфекции новорожденных','01',	'P36, P38, P39, G00, L00, L01, L02, L03, L08.0,8,9, M86.0,1,2,8,9, J12-J18','L08.0','L08.0',28),
(1,'Гнойно-септические инфекции новорожденных','01',	'P36, P38, P39, G00, L00, L01, L02, L03, L08.0,8,9, M86.0,1,2,8,9, J12-J18','L08.8','L08.9',28),
(1,'Гнойно-септические инфекции новорожденных','01',	'P36, P38, P39, G00, L00, L01, L02, L03, L08.0,8,9, M86.0,1,2,8,9, J12-J18','M86.0','M86.2',28),
(1,'Гнойно-септические инфекции новорожденных','01',	'P36, P38, P39, G00, L00, L01, L02, L03, L08.0,8,9, M86.0,1,2,8,9, J12-J18','M86.8','M86.9',28),
(1,'Гнойно-септические инфекции новорожденных','01',	'P36, P38, P39, G00, L00, L01, L02, L03, L08.0,8,9, M86.0,1,2,8,9, J12-J18','J12','J18.9',28),
									
(2,'из них: бактериальный менингит','02',	'P39.8, G00','P39.8','P39.8',28),
(2,'из них: бактериальный менингит','02',	'P39.8, G00','G00','G00.9',28),									
(3,'сепсис','03',	'P36','P36','P36.9',28),									
(4,'остеомиелит','04',	'P39.8,M86.0, 1, 2, 8, 9','P39.8','P39.8',28),	
(4,'остеомиелит','04',	'P39.8,M86.0, 1, 2, 8, 9','M86.0','M86.2',28),
(4,'остеомиелит','04',	'P39.8,M86.0, 1, 2, 8, 9','M86.8','M86.9',28),
							
(5,'омфалит новорожденного','05',		'P38',	'P38','P38.9',28),						
(6,'Инфекции, связанные с инфузией, трансфузией и лечебной инъекцией, иммунизацией, флебит пупочной вены','06',	'T80.2, T88.0, I80.8',	'T80.2','T80.2',28),
(6,'Инфекции, связанные с инфузией, трансфузией и лечебной инъекцией, иммунизацией, флебит пупочной вены','06',	'T80.2, T88.0, I80.8','T88.0','T88.0',28),	
(6,'Инфекции, связанные с инфузией, трансфузией и лечебной инъекцией, иммунизацией, флебит пупочной вены','06',	'T80.2, T88.0, I80.8',	'I80.8','I80.8',28),								
(7,'пиодермия, импетиго, панариций, паронихий, мастит','07',	'P39.4, L00, L01, L03, L08.0,8,9, P39.0','P39.4','P39.4',28),
(7,'пиодермия, импетиго, панариций, паронихий, мастит','07',	'P39.4, L00, L01, L03, L08.0,8,9, P39.0','L00','L00.9',28),
(7,'пиодермия, импетиго, панариций, паронихий, мастит','07',	'P39.4, L00, L01, L03, L08.0,8,9, P39.0','L01','L01.9',28),
(7,'пиодермия, импетиго, панариций, паронихий, мастит','07',	'P39.4, L00, L01, L03, L08.0,8,9, P39.0','L03','L03.9',28),
(7,'пиодермия, импетиго, панариций, паронихий, мастит','07',	'P39.4, L00, L01, L03, L08.0,8,9, P39.0','L08.0','L08.0',28),
(7,'пиодермия, импетиго, панариций, паронихий, мастит','07',	'P39.4, L00, L01, L03, L08.0,8,9, P39.0','L08.8','L08.9',28),
(7,'пиодермия, импетиго, панариций, паронихий, мастит','07',	'P39.4, L00, L01, L03, L08.0,8,9, P39.0', 'P39.0', 'P39.0',28),									
(8,'неонатальная инфекция мочевых путей','08',	  'P39.3, T83.5, T88.8','P39.3','P39.3',28),
(8,'неонатальная инфекция мочевых путей','08',	  'P39.3, T83.5, T88.8','T83.5','T83.5',28),
(8,'неонатальная инфекция мочевых путей','08',	  'P39.3, T83.5, T88.8','T88.8',	'T88.8',28),								
(9,'конъюнктивит и дакриоцистит у новорожденного','09',	'P39.1','P39.1','P39.1',28),									
(10,'пневмонии','10',	'J12-J15','J12','J15.9',28),									
(11,'Внутриутробные инфекции','11',	'A54.3, P23, P35 - P37, P39.2, P39.9','A54.3','A54.3',28),	
(11,'Внутриутробные инфекции','11',	'A54.3, P23, P35 - P37, P39.2, P39.9','P23',	'P23.9',28),
(11,'Внутриутробные инфекции','11',	'A54.3, P23, P35 - P37, P39.2, P39.9',	'P35','P37.9',28),
(11,'Внутриутробные инфекции','11',	'A54.3, P23, P35 - P37, P39.2, P39.9',	'P39.2','P39.2',28),
(11,'Внутриутробные инфекции','11',	'A54.3, P23, P35 - P37, P39.2, P39.9',	'P39.9','P39.9',28),				
(12,'Гнойно-септические инфекции родильниц','12','O75.3, O85, O86, O90.0, 1, O91.0, 1, O99.5, J12 - J18, J20 - 21','O75.3',	'O75.3',null),
(12,'Гнойно-септические инфекции родильниц','12','O75.3, O85, O86, O90.0, 1, O91.0, 1, O99.5, J12 - J18, J20 - 21',	'O85','O85.9',null),
(12,'Гнойно-септические инфекции родильниц','12','O75.3, O85, O86, O90.0, 1, O91.0, 1, O99.5, J12 - J18, J20 - 21',	'O86','O86.9',null),
(12,'Гнойно-септические инфекции родильниц','12','O75.3, O85, O86, O90.0, 1, O91.0, 1, O99.5, J12 - J18, J20 - 21',	'O90.0','O90.0',null),
(12,'Гнойно-септические инфекции родильниц','12','O75.3, O85, O86, O90.0, 1, O91.0, 1, O99.5, J12 - J18, J20 - 21',	'O90.1','O90.1',null),
(12,'Гнойно-септические инфекции родильниц','12','O75.3, O85, O86, O90.0, 1, O91.0, 1, O99.5, J12 - J18, J20 - 21',	'O99.5','O99.5',null),
(12,'Гнойно-септические инфекции родильниц','12','O75.3, O85, O86, O90.0, 1, O91.0, 1, O99.5, J12 - J18, J20 - 21',	'J12','J18.9',null),
(12,'Гнойно-септические инфекции родильниц','12','O75.3, O85, O86, O90.0, 1, O91.0, 1, O99.5, J12 - J18, J20 - 21',	'J20','J21.9',null),				
(13,'из них: сепсис','13',	'O85',	'O85',	'O85.9',null),							
(14,'инфекции соска и молочной железы, связанные с деторождением','14',	'O91.0, O91.1',	'O91.0','O91.0',null),
(14,'инфекции соска и молочной железы, связанные с деторождением','14',	'O91.0, O91.1',	'O91.1',	'O91.1',null),							
(15,'острый перитонит','15',	'O85',	'O85',	'O85.9',null),							
(16,'инфекция хирургической акушерской раны, расхождение швов после кесарева сечения','16',	'O86.0, O90.0','O86.0',	'O86.0',null),
(16,'инфекция хирургической акушерской раны, расхождение швов после кесарева сечения','16',	'O86.0, O90.0',	 'O90.0', 'O90.0',null),										
(17,'расхождение швов промежности','17',	'O90.1','O90.1','O90.1',null),									
(18,'инфекции мочевых путей после родов','18',	'O86.2','O86.2','O86.2',null),									
(19,'инфекции органов дыхания, осложняющие роды и послеродовой период','19',	'J12 - 18, J20 - 21, O99.5','J12','J18.9',null),	
(19,'инфекции органов дыхания, осложняющие роды и послеродовой период','19',	'J12 - 18, J20 - 21, O99.5','J20','J21.9',null),
(19,'инфекции органов дыхания, осложняющие роды и послеродовой период','19',	'J12 - 18, J20 - 21, O99.5','O99.5',	'O99.5',null),							
(20,'Инфекции в области хирургического вмешательства','20',	'T82.6, 7, T83.5, T84.5, 6, 7, T85.7, T81.3, 4, T87.4, A40, A41, A48.0, G00, G04.2, 8, 9, J85, J86, O07.0, K65.0, M86, I80, N 98.0','T82.6','T82.7',null),
(20,'Инфекции в области хирургического вмешательства','20',	'T82.6, 7, T83.5, T84.5, 6, 7, T85.7, T81.3, 4, T87.4, A40, A41, A48.0, G00, G04.2, 8, 9, J85, J86, O07.0, K65.0, M86, I80, N 98.0','T83.5',	'T83.5',null),
(20,'Инфекции в области хирургического вмешательства','20',	'T82.6, 7, T83.5, T84.5, 6, 7, T85.7, T81.3, 4, T87.4, A40, A41, A48.0, G00, G04.2, 8, 9, J85, J86, O07.0, K65.0, M86, I80, N 98.0','T84.5','T84.7',null),	
(20,'Инфекции в области хирургического вмешательства','20',	'T82.6, 7, T83.5, T84.5, 6, 7, T85.7, T81.3, 4, T87.4, A40, A41, A48.0, G00, G04.2, 8, 9, J85, J86, O07.0, K65.0, M86, I80, N 98.0','T85.7',	'T85.7',null),
(20,'Инфекции в области хирургического вмешательства','20',	'T82.6, 7, T83.5, T84.5, 6, 7, T85.7, T81.3, 4, T87.4, A40, A41, A48.0, G00, G04.2, 8, 9, J85, J86, O07.0, K65.0, M86, I80, N 98.0','T81.3',	'T81.4',null),
(20,'Инфекции в области хирургического вмешательства','20',	'T82.6, 7, T83.5, T84.5, 6, 7, T85.7, T81.3, 4, T87.4, A40, A41, A48.0, G00, G04.2, 8, 9, J85, J86, O07.0, K65.0, M86, I80, N 98.0','T87.4',	'T87.4',null),
(20,'Инфекции в области хирургического вмешательства','20',	'T82.6, 7, T83.5, T84.5, 6, 7, T85.7, T81.3, 4, T87.4, A40, A41, A48.0, G00, G04.2, 8, 9, J85, J86, O07.0, K65.0, M86, I80, N 98.0','A40','A41.9',null),	
(20,'Инфекции в области хирургического вмешательства','20',	'T82.6, 7, T83.5, T84.5, 6, 7, T85.7, T81.3, 4, T87.4, A40, A41, A48.0, G00, G04.2, 8, 9, J85, J86, O07.0, K65.0, M86, I80, N 98.0','A48.0',	'A48.0',null),
(20,'Инфекции в области хирургического вмешательства','20',	'T82.6, 7, T83.5, T84.5, 6, 7, T85.7, T81.3, 4, T87.4, A40, A41, A48.0, G00, G04.2, 8, 9, J85, J86, O07.0, K65.0, M86, I80, N 98.0','G00','G00.9',null),	
(20,'Инфекции в области хирургического вмешательства','20',	'T82.6, 7, T83.5, T84.5, 6, 7, T85.7, T81.3, 4, T87.4, A40, A41, A48.0, G00, G04.2, 8, 9, J85, J86, O07.0, K65.0, M86, I80, N 98.0','G04.2','G04.2',null),	
(20,'Инфекции в области хирургического вмешательства','20',	'T82.6, 7, T83.5, T84.5, 6, 7, T85.7, T81.3, 4, T87.4, A40, A41, A48.0, G00, G04.2, 8, 9, J85, J86, O07.0, K65.0, M86, I80, N 98.0','G04.8','G04.9',null),	

(20,'Инфекции в области хирургического вмешательства','20',	'T82.6, 7, T83.5, T84.5, 6, 7, T85.7, T81.3, 4, T87.4, A40, A41, A48.0, G00, G04.2, 8, 9, J85, J86, O07.0, K65.0, M86, I80, N 98.0','J85','J85.9',null),	
(20,'Инфекции в области хирургического вмешательства','20',	'T82.6, 7, T83.5, T84.5, 6, 7, T85.7, T81.3, 4, T87.4, A40, A41, A48.0, G00, G04.2, 8, 9, J85, J86, O07.0, K65.0, M86, I80, N 98.0','J86','J86.9',null),	
(20,'Инфекции в области хирургического вмешательства','20',	'T82.6, 7, T83.5, T84.5, 6, 7, T85.7, T81.3, 4, T87.4, A40, A41, A48.0, G00, G04.2, 8, 9, J85, J86, O07.0, K65.0, M86, I80, N 98.0','O07.0','O07.0',null),	
(20,'Инфекции в области хирургического вмешательства','20',	'T82.6, 7, T83.5, T84.5, 6, 7, T85.7, T81.3, 4, T87.4, A40, A41, A48.0, G00, G04.2, 8, 9, J85, J86, O07.0, K65.0, M86, I80, N 98.0','K65.0','K65.0',null),	
(20,'Инфекции в области хирургического вмешательства','20',	'T82.6, 7, T83.5, T84.5, 6, 7, T85.7, T81.3, 4, T87.4, A40, A41, A48.0, G00, G04.2, 8, 9, J85, J86, O07.0, K65.0, M86, I80, N 98.0','M86','M86.9',null),
(20,'Инфекции в области хирургического вмешательства','20',	'T82.6, 7, T83.5, T84.5, 6, 7, T85.7, T81.3, 4, T87.4, A40, A41, A48.0, G00, G04.2, 8, 9, J85, J86, O07.0, K65.0, M86, I80, N 98.0','I80','I80.9',null),	
(20,'Инфекции в области хирургического вмешательства','20',	'T82.6, 7, T83.5, T84.5, 6, 7, T85.7, T81.3, 4, T87.4, A40, A41, A48.0, G00, G04.2, 8, 9, J85, J86, O07.0, K65.0, M86, I80, N 98.0','N98.0','N98.0',null),													
(21,'Инфекции, связанные с инфузией, трансфузией и лечебной инъекцией, иммунизацией','21',	'T80.2, T88.0','T80.2','T80.2',29),	
(21,'Инфекции, связанные с инфузией, трансфузией и лечебной инъекцией, иммунизацией','21',	'T80.2, T88.0','T88.0','T88.0',29),									
(22,'в т.ч. катетер-ассоциированные инфекции кровотока','22',	'T80.2','T80.2','T80.2',29),									
(23,'Инфекции мочевыводящих путей','23',	'N 30.0, N 39.0, N 34.0, T81.4, T83.5 T88.8','N30.0','N30.0',29),
(23,'Инфекции мочевыводящих путей','23',	'N 30.0, N 39.0, N 34.0, T81.4, T83.5 T88.8','N39.0','N39.0',29),
(23,'Инфекции мочевыводящих путей','23',	'N 30.0, N 39.0, N 34.0, T81.4, T83.5 T88.8','T81.4','T81.4',29),
(23,'Инфекции мочевыводящих путей','23',	'N 30.0, N 39.0, N 34.0, T81.4, T83.5 T88.8','T83.5','T83.5',29),
(23,'Инфекции мочевыводящих путей','23',	'N 30.0, N 39.0, N 34.0, T81.4, T83.5 T88.8','N34.0','N34.0',29),
(23,'Инфекции мочевыводящих путей','23',	'N 30.0, N 39.0, N 34.0, T81.4, T83.5 T88.8','T88.8','T88.8',29),									
(24,'в т.ч. катетер-ассоциированные инфекции мочевыводящих путей','24',	'T83.5, T88.8',	'T83.5','T83.5',29),
(24,'в т.ч. катетер-ассоциированные инфекции мочевыводящих путей','24',	'T83.5, T88.8',	'T88.8',	'T88.8',29),						
(25,'Инфекции нижних дыхательных путей (ИНДП) и пневмонии','25',	  'J12 - J18, J20 - J21, T88.8','J12', 'J18.9',29),
(25,'Инфекции нижних дыхательных путей (ИНДП) и пневмонии','25',	  'J12 - J18, J20 - J21, T88.8','J20','J21.9',29),
(25,'Инфекции нижних дыхательных путей (ИНДП) и пневмонии','25',	  'J12 - J18, J20 - J21, T88.8','T88.8','T88.8',29),									
(26,'в.т.ч. пневмонии','26',	'J12 - J18, T88.8','J18', 'J18.9',29),	
(26,'в.т.ч. пневмонии','26',	'J12 - J18, T88.8',	'T88.8','T88.8',29),							
(27,'Острые кишечные инфекции, острые вирусные гепатиты A, E','27',	'A01, A03, A04, A05, A08, A09, B15, B17.2',	'A01','A01.9',null),
(27,'Острые кишечные инфекции, острые вирусные гепатиты A, E','27',	'A01, A03, A04, A05, A08, A09, B15, B17.2',	'A03','A03.9',null),
(27,'Острые кишечные инфекции, острые вирусные гепатиты A, E','27',	'A01, A03, A04, A05, A08, A09, B15, B17.2',	'A04','A04.9',null),
(27,'Острые кишечные инфекции, острые вирусные гепатиты A, E','27',	'A01, A03, A04, A05, A08, A09, B15, B17.2',	'A05','A05.9',null),
(27,'Острые кишечные инфекции, острые вирусные гепатиты A, E','27',	'A01, A03, A04, A05, A08, A09, B15, B17.2',	'A08','A08.9',null),
(27,'Острые кишечные инфекции, острые вирусные гепатиты A, E','27',	'A01, A03, A04, A05, A08, A09, B15, B17.2',	'A09','A09.9',null),
(27,'Острые кишечные инфекции, острые вирусные гепатиты A, E','27',	'A01, A03, A04, A05, A08, A09, B15, B17.2',	'B15','B15.9',null),
(27,'Острые кишечные инфекции, острые вирусные гепатиты A, E','27',	'A01, A03, A04, A05, A08, A09, B15, B17.2',	'B17.2',	'B17.2',null),				
(28,'Другие сальмонеллезные инфекции','28',	'A02','A02','A02.9',null),									
(29,'Воздушно-капельные инфекции','29',	'A36, A37, A39, B01, B05, B06, B26, J06, J09 - 11, J22','A36','A36.9',null),
(29,'Воздушно-капельные инфекции','29',	'A36, A37, A39, B01, B05, B06, B26, J06, J09 - 11, J22','A37','A37.9',null),
(29,'Воздушно-капельные инфекции','29',	'A36, A37, A39, B01, B05, B06, B26, J06, J09 - 11, J22','A39','A39.9',null),
(29,'Воздушно-капельные инфекции','29',	'A36, A37, A39, B01, B05, B06, B26, J06, J09 - 11, J22','B01','B01.9',null),
(29,'Воздушно-капельные инфекции','29',	'A36, A37, A39, B01, B05, B06, B26, J06, J09 - 11, J22','B05','B05.9',null),
(29,'Воздушно-капельные инфекции','29',	'A36, A37, A39, B01, B05, B06, B26, J06, J09 - 11, J22','B06','B06.9',null),
(29,'Воздушно-капельные инфекции','29',	'A36, A37, A39, B01, B05, B06, B26, J06, J09 - 11, J22','B26','B26.9',null),
(29,'Воздушно-капельные инфекции','29',	'A36, A37, A39, B01, B05, B06, B26, J06, J09 - 11, J22','J06','J06.9',null),
(29,'Воздушно-капельные инфекции','29',	'A36, A37, A39, B01, B05, B06, B26, J06, J09 - 11, J22','J09','J11.9',null),
(29,'Воздушно-капельные инфекции','29',	'A36, A37, A39, B01, B05, B06, B26, J06, J09 - 11, J22','J22',	'J22.9',null),								
(30,'т.ч. корь','30',	'B05',	'B05',	'B05.9',null),							
(31,'Туберкулез впервые выявленный, активные формы','31',	'A15 - A19','A15','A19.9',null),									
(32,'ВИЧ-инфекция','32',	'B20 - B24, Z21','B20','B24.9',null),
(32,'ВИЧ-инфекция','32',	'B20 - B24, Z21',	 'Z21',' Z21.9',null),								
(33,'Вирусный гепатит В','33',	'B16','B16','B16.9',null),									
(34,'Вирусный гепатит C','34',	'B17.1','B17.1','B17.1',null),									
(35,'COVID-19, всего','35',	'U07.1, U07.2, J12 - J18, Z22.8','U07.1','U07.1',null),
(35,'COVID-19, всего','35',	'U07.1, U07.2, J12 - J18, Z22.8', 'U07.2', 'U07.2',null),
(35,'COVID-19, всего','35',	'U07.1, U07.2, J12 - J18, Z22.8','J12','J18.9',null),
(35,'COVID-19, всего','35',	'U07.1, U07.2, J12 - J18, Z22.8',	'Z22.8','Z22.8',null),							
(36,'из него пневмонии','36',	'U07.1, U07.2, J12 - J18','U07.1',	'U07.1',null),
(36,'из него пневмонии','36',	'U07.1, U07.2, J12 - J18', 'U07.2', 'U07.2',null),
(36,'из него пневмонии','36',	'U07.1, U07.2, J12 - J18',	 'J12','J18.9',null),							
(37,'Из них пневмонии, вызванные вирусом COVID-19, вирус идентифицирован','37',	'U07.1, J12 - J18','U07.1','U07.1',null),	
(37,'Из них пневмонии, вызванные вирусом COVID-19, вирус идентифицирован','37',	'U07.1, J12 - J18','J12','J18.9',null),									
(38,'носительство возбудителя COVID-19','38',	'Z22.8','Z22.8',	'Z22.8',null),								
(39,'Другие инфекционные заболевания, носительство возбудителей инфекционных заболеваний','39',	'обозначаются кодами по МКБ Х, соответственно нозологической форме','','',null)				
) as t_table(RepDiag_Row,RepDiag_Text1,RepDiag_Text2,RepDiag_Text3,RepDiag_FromDiag,RepDiag_ToDiag,child)
),
tap_osn as
(
	select null::timestamp gosp_d,evn.evnpl_id evn_id,evpl.EvnVizitPL_id vizit_id,null::integer EvnSection_Index,
	lpu.LpuPmuType_id,lpu.LpuType_id,LpuUnit.LpuUnit_id,LpuUnit.lpuunittype_id,LpuSection.LpuSectionProfile_id,FRMPSubdivision_id,LpuSectionAge_id,
	RepDiag_Row,evn.Lpu_id,EVPL.Diag_id,dg.Diag_Code osn,
	diagsop,evn.person_id,evn.evnpl_setdate setdate,ch28_29
	from params
	inner join v_EvnVizitPL EVPL on true  
	inner join v_diag dg on dg.Diag_id=EVPL.diag_id
	inner join v_EvnPL evn   on evn.EvnPL_id=EVPL.EvnVizitPL_pid and evpl.EvnVizitPL_Index = evpl.EvnVizitPL_Count - 1
	left join lateral
	(
		select Diag.Diag_Code diagsop
		from  dbo.v_EvnDiagPLSop DiagSop  
		inner join dbo.v_Diag Diag   on Diag.Diag_id = DiagSop.Diag_id
		where 
		DiagSop.EvnDiagPLSop_pid = evpl.EvnVizitPL_id --and DiagSop.DiagSetClass_id = 3
	) sop on true
	left join lateral
	(
		select 
		case when dbo.AgeYMD(ps.Person_BirthDay,evpl.EvnVizitPL_setDate,3) between 0 and 28 then 28 
		when dbo.AgeYMD(ps.Person_BirthDay,evpl.EvnVizitPL_setDate,3)>=29 then 29 end ch28_29
		from v_personstate ps 
		where ps.person_id=evpl.person_id limit 1
	) child on true
	left join v_lpu lpu   on lpu.Lpu_id=evn.Lpu_id
	left join v_DeseaseType dt   on dt.DeseaseType_id=evpl.DeseaseType_id
	inner join RD_3000 rd on dg.Diag_Code between RepDiag_FromDiag and RepDiag_ToDiag and (rd.child=ch28_29 or rd.child is null)
	left join v_LpuSection as LpuSection  on LpuSection.LpuSection_id = EVPL.lpuSection_id
	left join v_LpuUnit as LpuUnit  on LpuUnit.LpuUnit_id = LpuSection.LpuUnit_id
	where evn.EvnPL_IsFinish=2 and evn.EvnPL_disDate between p_begdate and p_enddate
		and 2= case when (lower(dt.DeseaseType_SysNick) in ('sharp','new') or dt.DeseaseType_SysNick is null) and left (dg.Diag_Code,3) between 'Z21' and  'Z22' then 2
					when lower(dt.DeseaseType_SysNick) in ('sharp','new') and left (dg.Diag_Code,3) not between 'Z21' and  'Z22' then 2 end
		and (lpu.lpu_id=p_lpu_id or p_lpu_id is null)
		and (LpuSection.LpuUnit_id=p_LpuUnit_id or p_LpuUnit_id is null)
		and (LpuSection.LpuSection_id=p_LpuSection_id or p_LpuSection_id is null)
		and (LpuUnit.LpuBuilding_id =p_LpuBuilding_id or p_LpuBuilding_id is null) 
),	
kvs_osn as
(
	select eps.evnps_setdate +eps.evnps_settime gosp_d, eps.evnps_id evn_id,es.EvnSection_id vizit_id,ES.EvnSection_Index,
	lpu.LpuPmuType_id,lpu.LpuType_id,LpuUnit.LpuUnit_id,LpuUnit.lpuunittype_id,LpuSection.LpuSectionProfile_id,FRMPSubdivision_id,LpuSectionAge_id,
	RepDiag_Row,eps.Lpu_id,es.diag_id,dg.diag_code osn,
	diagsop,eps.person_id,--es.EvnSection_setDate setdate,
	eps.evnps_setdate setdate,
	ch28_29
	from params
	inner join v_EvnSection ES on true
	inner join v_diag dg on dg.Diag_id=es.diag_id and EvnSection_Index=EvnSection_Count-1
	left join lateral
	(
		select Diag.Diag_Code diagsop
		from  v_EvnDiagPS EDPS  
		inner join dbo.v_Diag Diag   on Diag.Diag_id = EDPS.Diag_id
		where 
		EDPS.EvnDiagPS_pid = es.EvnSection_id --and EDPS.DiagSetClass_id=3
	) sop on true
	left join lateral
	(
		select 
		case when dbo.AgeYMD(ps.Person_BirthDay,es.EvnSection_setDate,3) between 0 and 28 then 28 
		when dbo.AgeYMD(ps.Person_BirthDay,es.EvnSection_setDate,3)>=29 then 29 end ch28_29
		from v_personstate ps 
		where ps.person_id=es.person_id limit 1
	) child on true
	left join v_EvnPS EPS  on ES.EvnSection_pid=EPS.EvnPS_id
	left join v_lpu lpu   on lpu.Lpu_id=eps.Lpu_id
	left join v_DeseaseType dt  on dt.DeseaseType_id=es.DeseaseType_id
	inner join RD_3000 rd on dg.Diag_Code between RepDiag_FromDiag and RepDiag_ToDiag and (rd.child=ch28_29 or rd.child is null)
	left join v_LpuSection as LpuSection on LpuSection.LpuSection_id = es.lpuSection_id
	left join v_LpuUnit as LpuUnit  on LpuUnit.LpuUnit_id = LpuSection.LpuUnit_id
	where  (lpu.lpu_id=p_lpu_id or p_lpu_id is null)
	and eps.evnps_disdate between  p_begdate and p_enddate
	and 2= case when (lower(dt.DeseaseType_SysNick) in ('sharp','new') or dt.DeseaseType_SysNick is null) and left (dg.Diag_Code,3) between 'Z21' and  'Z22' then 2
					when lower(dt.DeseaseType_SysNick) in ('sharp','new') and left (dg.Diag_Code,3) not between 'Z21' and  'Z22' then 2 end
	and (LpuSection.LpuUnit_id=p_LpuUnit_id or p_LpuUnit_id is null)
		and (LpuSection.LpuSection_id=p_LpuSection_id or p_LpuSection_id is null)
		and (LpuUnit.LpuBuilding_id =p_LpuBuilding_id or p_LpuBuilding_id is null)
),
tap_sop as
(
	select null::timestamp gosp_d,evn.evnpl_id evn_id,evpl.EvnVizitPL_id vizit_id,null::integer EvnSection_Index,
	lpu.LpuPmuType_id,lpu.LpuType_id,LpuUnit.LpuUnit_id,LpuUnit.lpuunittype_id,LpuSection.LpuSectionProfile_id,FRMPSubdivision_id,LpuSectionAge_id,
	RepDiag_Row,evn.Lpu_id,EVPL.Diag_id,--dg.Diag_Code osn,
	diagsop osn,
	diagsop,evn.person_id,--evpl.EvnVizitPL_setDate setdate,
	evn.evnpl_setdate setdate,
	ch28_29
	from params
	inner join v_EvnVizitPL EVPL on true
	inner join v_diag dg on dg.Diag_id=EVPL.diag_id
	inner join v_EvnPL evn  on evn.EvnPL_id=EVPL.EvnVizitPL_pid and evpl.EvnVizitPL_Index = evpl.EvnVizitPL_Count - 1
	left join lateral
	(
		select Diag.Diag_Code diagsop
		from  dbo.v_EvnDiagPLSop DiagSop  
		inner join dbo.v_Diag Diag   on Diag.Diag_id = DiagSop.Diag_id
		where 
		DiagSop.EvnDiagPLSop_pid = evpl.EvnVizitPL_id --and DiagSop.DiagSetClass_id = 3
	) sop on true
	left join lateral
	(
		select 
		case when dbo.AgeYMD(ps.Person_BirthDay,evpl.EvnVizitPL_setDate,3) between 0 and 28 then 28 
		when dbo.AgeYMD(ps.Person_BirthDay,evpl.EvnVizitPL_setDate,3)>=29 then 29 end ch28_29
		from v_personstate ps 
		where ps.person_id=evpl.person_id limit 1
	) child on true
	left join v_lpu lpu  on lpu.Lpu_id=evn.Lpu_id
	left join v_DeseaseType dt  on dt.DeseaseType_id=evpl.DeseaseType_id
	inner join RD_3000 rd on diagsop between RepDiag_FromDiag and RepDiag_ToDiag and (rd.child=ch28_29 or rd.child is null)
	left join v_LpuSection as LpuSection  on LpuSection.LpuSection_id = EVPL.lpuSection_id
	left join v_LpuUnit as LpuUnit  on LpuUnit.LpuUnit_id = LpuSection.LpuUnit_id
	where evn.EvnPL_IsFinish=2 and evn.EvnPL_disDate between p_begdate and p_enddate
		--and dt.DeseaseType_SysNick in ('sharp','new')
		and (lpu.lpu_id=p_lpu_id or p_lpu_id is null)
		and (LpuSection.LpuUnit_id=p_LpuUnit_id or p_LpuUnit_id is null)
		and (LpuSection.LpuSection_id=p_LpuSection_id or p_LpuSection_id is null)
		and (LpuUnit.LpuBuilding_id =p_LpuBuilding_id or p_LpuBuilding_id is null) 
),	
tap_sop_sop as
(
	select gosp_d,evn_id,vizit_id,null::integer EvnSection_Index,
	LpuPmuType_id,LpuType_id,LpuUnit_id,lpuunittype_id,LpuSectionProfile_id,FRMPSubdivision_id,LpuSectionAge_id,
	RepDiag_Row,Lpu_id,Diag_id,	osn,diagsop1 diagsop,person_id,setdate,
	ch28_29
	from tap_sop ts
	left join lateral
	(
		select Diag.Diag_Code diagsop1
		from  dbo.v_EvnDiagPLSop DiagSop   
		inner join dbo.v_Diag Diag   on Diag.Diag_id = DiagSop.Diag_id
		where 
		DiagSop.EvnDiagPLSop_pid = ts.vizit_id --and DiagSop.DiagSetClass_id = 3
	) sop on true
	),
kvs_sop as
(
	select eps.evnps_setdate +eps.evnps_settime gosp_d,eps.evnps_id evn_id,es.EvnSection_id vizit_id,ES.EvnSection_Index,
	lpu.LpuPmuType_id,lpu.LpuType_id,LpuUnit.LpuUnit_id,LpuUnit.lpuunittype_id,LpuSection.LpuSectionProfile_id,FRMPSubdivision_id,LpuSectionAge_id,
	RepDiag_Row,eps.Lpu_id,es.diag_id,diagsop osn,--dg.Diag_Code osn_1,
	diagsop,eps.person_id,--es.EvnSection_setDate setdate,
	eps.evnps_setdate setdate,
	ch28_29
	from params
	inne join v_EvnSection ES on true 
	inner join v_diag dg on dg.Diag_id=es.diag_id and EvnSection_Index=EvnSection_Count-1
	left join lateral
	(
		select Diag.Diag_Code diagsop
		from  v_EvnDiagPS EDPS 
		inner join dbo.v_Diag Diag   on Diag.Diag_id = EDPS.Diag_id
		where 
		EDPS.EvnDiagPS_pid = es.EvnSection_id --and EDPS.DiagSetClass_id=3
	) sop on true
	left join lateral
	(
		select 
		case when dbo.AgeYMD(ps.Person_BirthDay,es.EvnSection_setDate,3) between 0 and 28 then 28 
		when dbo.AgeYMD(ps.Person_BirthDay,es.EvnSection_setDate,3)>=29 then 29 end ch28_29
		from v_personstate ps 
		where ps.person_id=es.person_id limit 1
	) child on true
	left join v_EvnPS EPS  on ES.EvnSection_pid=EPS.EvnPS_id
	left join v_lpu lpu  on lpu.Lpu_id=eps.Lpu_id
	left join v_DeseaseType dt   on dt.DeseaseType_id=es.DeseaseType_id
	inner join RD_3000 rd on diagsop between RepDiag_FromDiag and RepDiag_ToDiag and (rd.child=ch28_29 or rd.child is null)
	left join v_LpuSection as LpuSection on LpuSection.LpuSection_id = es.lpuSection_id
	left join v_LpuUnit as LpuUnit on LpuUnit.LpuUnit_id = LpuSection.LpuUnit_id
	where  (lpu.lpu_id=p_lpu_id or p_lpu_id is null)
	and eps.evnps_disdate between  p_begdate and p_enddate
	
	and (LpuSection.LpuUnit_id=p_LpuUnit_id or p_LpuUnit_id is null)
		and (LpuSection.LpuSection_id=p_LpuSection_id or p_LpuSection_id is null)
		and (LpuUnit.LpuBuilding_id =p_LpuBuilding_id or p_LpuBuilding_id is null)
),
kvs_sop_sop as
(
select gosp_d,evn_id,vizit_id,EvnSection_Index,
LpuPmuType_id,LpuType_id,LpuUnit_id,lpuunittype_id,LpuSectionProfile_id,FRMPSubdivision_id,LpuSectionAge_id,
	RepDiag_Row,Lpu_id,diag_id,osn,	diagsop1 diagsop,person_id,setdate,
	ch28_29 
from kvs_sop ks
left join lateral
	(
		select Diag.Diag_Code diagsop1
		from  v_EvnDiagPS EDPS   
		inner join dbo.v_Diag Diag  on Diag.Diag_id = EDPS.Diag_id
		where 
		EDPS.EvnDiagPS_pid = ks.vizit_id --and EDPS.DiagSetClass_id=3
		and coalesce(Diag.Diag_Code,'')<>ks.diagsop
	) sop on true
),

evns_diag as
(
select  EIN_58.EvnInfectNotify_id in1,usl.UslugaComplex_code uslUslugaComplex_code,ch28_29,
 
case when RepDiag_Row=1 then
	case 
	when left(osn,3) in ('P36', 'P38', 'P39') and  coalesce(osn,'') not in ('P39.2', 'P39.9') then osn
	when left(diagsop,3) in ('P36', 'P38', 'P39') and  coalesce(diagsop,'') not in ('P39.2', 'P39.9') then osn
	when left(osn,3) in ('P36', 'P38', 'P39') and  coalesce(osn,'') in ('P39.2', 'P39.9') then 'NO'
	when left(diagsop,3) in ('P36', 'P38', 'P39') and  coalesce(diagsop,'') in ('P39.2', 'P39.9') then 'NO' end

when RepDiag_Row between 12 and 14 then
case 
when coalesce(lsp.LpuSectionProfile_code,'') in ('3','136') then osn
when coalesce(lsp.LpuSectionProfile_code,'') not in ('3','136') then 'NO' end
	
when RepDiag_Row=15 and coalesce(lsp.LpuSectionProfile_code,'') in ('3','136') then 
case when osn between 'O85' and 'O85.9' and usl_15 is not null  then osn else 'NO' end

when RepDiag_Row between 16 and 19 then
case 
when coalesce(lsp.LpuSectionProfile_code,'') in ('3','136') then osn
when coalesce(lsp.LpuSectionProfile_code,'') not in ('3','136') then 'NO' end
when RepDiag_Row=20 then  
	case	
	when (left(osn,3) in ('A40', 'A41','G00','J85', 'J86','M86', 'I80') or osn in ('A48.0',  'G04.2', 'G04.8', 'G04.9',   'K65.0','N98.0'))	
		and coalesce(diagsop,'') in ('T82.6', 'T82.7', 'T83.5', 'T84.5', 'T84.6', 'T84.7', 'T85.7', 'T81.3', 'T81.4', 'T87.4', 'O07.0') then osn 
		when (left(osn,3) in ('A40', 'A41','G00','J85', 'J86','M86', 'I80') or osn in ('A48.0',  'G04.2', 'G04.8', 'G04.9',   'K65.0','N98.0'))	
		and coalesce(diagsop,'') not in ('T82.6', 'T82.7', 'T83.5', 'T84.5', 'T84.6', 'T84.7', 'T85.7', 'T81.3', 'T81.4', 'T87.4', 'O07.0') then 'NO' end
	
when RepDiag_Row=23 then  
	case
	when coalesce(diagsop,'') in  ('T81.4', 'T83.5', 'T88.8') --and coalesce(usl.UslugaComplex_code,'') in ('A11.28.007', 'A16.28.023', 'A16.28.051') 
	and usl_15 is not null then osn 
	when coalesce(diagsop,'') in  ('T81.4', 'T83.5', 'T88.8') and usl_15 is null  then 'NO' 
	when osn  in ('T81.4', 'T83.5', 'T88.8') and coalesce(diagsop,'')<>osn then 'NO' end
	
when RepDiag_Row=24 then  
	case
	when coalesce(diagsop,'') in ('T83.5', 'T88.8') and usl_15 is not null  then osn
	when coalesce(diagsop,'')  in ('T83.5', 'T88.8') and usl_15 is null  then 'NO' 
	when osn  in ('T83.5', 'T88.8') and coalesce(diagsop,'')<>osn then 'NO' end
when RepDiag_Row=25 then  
	case
	when (left(osn,3) between 'J12' and 'J18' or left(osn,3) between 'J20' and 'J21') 	and coalesce(diagsop,'')='T88.8' and osn='T88.8' and coalesce(diagsop,'')=osn then osn else 'NO' end
	--when (left(osn,3) between 'J12' and 'J18' or left(osn,3) between 'J20' and 'J21')  and coalesce(diagsop,'')<>'T88.8' then 'NO' 
	--when osn='T88.8' and coalesce(diagsop,'')<>osn then 'NO' end
when RepDiag_Row=26 then  
	case
	when left(osn,3)= 'J18' and coalesce(diagsop,'')='T88.8' and osn='T88.8' and coalesce(diagsop,'')=osn then osn else 'NO' end
	--when left(osn,3)= 'J18' and coalesce(diagsop,'')<>'T88.8' then 'NO' 
	--when osn='T88.8' and coalesce(diagsop,'')<>osn then 'NO' end
when RepDiag_Row=27 then  
	case	
	when (left(osn,3) in ('A01', 'A03', 'A04', 'A05', 'A08', 'A09', 'B15') or osn='B17.2')	and EvnInfectNotify_id is not null	then osn else 'NO' end
	
when RepDiag_Row=28 then  
	case	
	when left(osn,3)='A02'	and EvnInfectNotify_id is not null	then osn else 'NO' end
when RepDiag_Row=29 then  
	case	
	when (left(osn,3) in ('A36', 'A37', 'A39', 'B01', 'B05', 'B06', 'B26', 'J06', 'J22') or left(osn,3) between 'J09' and 'J11') and EvnInfectNotify_id is not null	then osn else 'NO' end
	
when RepDiag_Row=30 then  
	case	
	when left(osn,3)='B05'	and EvnInfectNotify_id is not null	then osn else 'NO' end
	
when RepDiag_Row=31 then  
	case	
	when left(osn,3) between 'A15' and 'A19'	and (EvnInfectNotify_id is not null or EvnNotifyTub_id is not null)	then osn else 'NO' end

when RepDiag_Row=32 then  
	case	
	when (left(osn,3) between 'B20' and 'B24' or left(osn,3)='Z21')	and EvnInfectNotify_id is not null	then osn else 'NO'	end

when RepDiag_Row=33 then  
	case	
	when left(osn,3)='B16'	and EvnInfectNotify_id is not null	then osn else 'NO'	end

when RepDiag_Row=34 then  
	case	
	when osn='B17.1'	and EvnInfectNotify_id is not null	then osn else 'NO'	end

when RepDiag_Row=35 then  
	case
	when (coalesce(diagsop,'') in ('U07.1', 'U07.2')	or  coalesce(osn,'') in ('U07.1', 'U07.2')) 
	and (DATE_PART('day', EvnDirection_setDate-gosp_d) * 24 + DATE_PART('hour', EvnDirection_setDate-gosp_d)) >24 and EvnSection_Index>0 then osn
	when ((left(osn,3) between 'J12' and  'J18'	and coalesce(diagsop,'') in ('U07.1', 'U07.2') )
	or (left(coalesce(diagsop,''),3) between 'J12' and  'J18'	and osn in ('U07.1', 'U07.2')))
	and (DATE_PART('day', EvnDirection_setDate-gosp_d) * 24 + DATE_PART('hour', EvnDirection_setDate-gosp_d)) >24	 and EvnSection_Index>0 then osn
	when diagsop='Z22.8'  and rusl.UslugaComplex_code is not null and (DATE_PART('day', EvnDirection_setDate-gosp_d) * 24 + DATE_PART('hour', EvnDirection_setDate-gosp_d)) >24 	then osn
	else 'NO' end
	
when RepDiag_Row=36 then  
	case		
	when left(osn,3) between 'J12' and  'J18'	and coalesce(diagsop,'') in ('U07.1', 'U07.2') 
	and (DATE_PART('day', EvnDirection_setDate-gosp_d) * 24 + DATE_PART('hour', EvnDirection_setDate-gosp_d)) >24	 and EvnSection_Index>0 then osn 
	when left(coalesce(diagsop,''),3) between 'J12' and  'J18'	and osn in ('U07.1', 'U07.2') 
	and (DATE_PART('day', EvnDirection_setDate-gosp_d) * 24 + DATE_PART('hour', EvnDirection_setDate-gosp_d)) >24	 and EvnSection_Index>0 then osn else 'NO'
	end
when RepDiag_Row=37 then  
	case	
	when left(osn,3) between 'J12' and  'J18'	and coalesce(diagsop,'')='U07.1'  
	and (DATE_PART('day', EvnDirection_setDate-gosp_d) * 24 + DATE_PART('hour', EvnDirection_setDate-gosp_d)) >24	 and EvnSection_Index>0 then osn
	when left(coalesce(diagsop,''),3) between 'J12' and  'J18'	and osn='U07.1'  
	and (DATE_PART('day', EvnDirection_setDate-gosp_d) * 24 + DATE_PART('hour', EvnDirection_setDate-gosp_d)) >24	 and EvnSection_Index>0 then osn else 'NO' 
	
	end
when RepDiag_Row=38 then  
	case		
	when diagsop='Z22.8' and rusl.UslugaComplex_code is not null and (DATE_PART('day', EvnDirection_setDate-gosp_d) * 24 + DATE_PART('hour', EvnDirection_setDate-gosp_d)) >24 	then osn else 'NO'
	end	 	 
end Diag_Code,evns.*,rusl.UslugaComplex_code,EvnInfectNotify_id,
dbo.AgeYMD(ps.Person_BirthDay,p_enddate,1) age,dbo.AgeYMD(ps.Person_BirthDay,setdate,3) child,params.*
from params
inner join
(
	select *
	from tap_osn
	union all
	select *
	from tap_sop
	union all
	select *
	from tap_sop_sop
	union all
	select *
	from kvs_osn
	union all
	select *
	from kvs_sop
	union all
	select *
	from kvs_sop_sop
) evns on true
left join v_PersonState ps on ps.person_id=evns.Person_id
left join lateral
(
	select UC.UslugaComplex_id ,UC.UslugaComplex_code usl_15
	from v_EvnUsluga EU 	
	inner join v_UslugaComplex UC  on UC.UslugaComplex_id = EU.UslugaComplex_id
	where  evns.evn_id = EU.EvnUsluga_rid
	and UC.UslugaComplex_code in ('A16.30.006','A16.30.006.001','A16.30.007','A16.30.007.001','A16.30.007.002','A16.30.007.003','A16.30.007.004','A16.30.043',
	'A11.28.007', 'A16.28.023', 'A16.28.051','A16.30.043.001','A16.30.043.003')
) usl15 on true
left join lateral
(
	select  EIN.EvnInfectNotify_id,EIN.EvnInfectNotify_SetDiagDate,ndiag.Diag_Code
	from v_EvnInfectNotify EIN 
	left join v_EvnDiag ED  on EIN.EvnInfectNotify_pid = ED.EvnDiag_id
	left join v_diag ndiag on ndiag.Diag_id=ed.Diag_id 
	where  EIN.EvnInfectNotify_rid = evns.evn_id
	and EvnInfectNotify_SetDiagDate>setdate limit 1
) EIN_58 on true
left join lateral
(
	select  EvnNotifyTub_id,ENC.EvnNotifyTub_setDT,DiagENC.Diag_Code
	from v_EvnNotifyTub ENC  
	inner join v_Morbus MO  on MO.Morbus_id = ENC.Morbus_id					
	left join v_Diag DiagENC  on DiagENC.Diag_id = ENC.Diag_id
	where ENC.Person_id = evns.Person_id
	and cast(ENC.EvnNotifyTub_setDT as date)>setdate and  (DiagENC.Diag_Code=evns.osn or DiagENC.Diag_Code=evns.diagsop) limit 1
) EIN_089 on true
left join lateral
(
	select distinct UC.UslugaComplex_id,UC.UslugaComplex_code,ed.EvnDirection_id,ed.EvnDirection_setDate
	from v_EvnUslugaPar EU
	inner join v_EvnDirection_all ed on ed.EvnDirection_id = eu.EvnDirection_id
	inner join v_EvnLabRequest elr on elr.EvnDirection_id=ed.EvnDirection_id
	left join v_EvnLabRequestUslugaComplex elruc on elr.EvnLabRequest_id = elruc.EvnLabRequest_id
	left join v_UslugaTest UT  on ut.EvnLabSample_id = elruc.EvnLabSample_id
	left join v_UslugaComplex UC on UC.UslugaComplex_id = EU.UslugaComplex_id
	where ed.EvnDirection_rid = evn_id
		and UC.UslugaComplex_code like 'A26.%'
		and ((lower(ut.UslugaTest_ResultValue) like ('пол%') or lower(ut.UslugaTest_ResultValue) like ('обн%'))
		or  (ut.UslugaTest_ResultValue<>'' and ut.UslugaTest_ResultLower<>'' and ut.UslugaTest_ResultUpper<>'' 
		and (
		rpt.try_cast(ut.UslugaTest_ResultValue,null::numeric)< rpt.try_cast(ut.UslugaTest_ResultLower,null::numeric) 
		or rpt.try_cast(ut.UslugaTest_ResultValue,null::numeric)> rpt.try_cast(ut.UslugaTest_ResultUpper,null::numeric)
		)))
) usl on true
left join v_LpuSectionProfile lsp on lsp.LpuSectionProfile_id=evns.LpuSectionProfile_id
left join Row_usl rusl on evns.osn between RepDiag_FromDiag and RepDiag_ToDiag and usl.UslugaComplex_code=rusl.UslugaComplex_code and rusl.Rowusl= evns.repdiag_row

)

select rd.RepDiag_Row,rd.RepDiag_Text1,rd.RepDiag_Text2,rd.RepDiag_Text3,
count(distinct person_id) v4,
count(distinct case when prof=5 and LpuUnitType_SysNick in ('stac','dstac','pstac') then person_id end) v5,
count(distinct case when prof=6 and LpuUnitType_SysNick in ('stac','dstac','pstac') then person_id end) v6,
count(distinct case when prof=7 and LpuUnitType_SysNick in ('stac','dstac','pstac') and LpuSectionAge_id=2 then person_id end) v7,
count(distinct case when prof=8 and LpuUnitType_SysNick in ('stac','dstac','pstac') then person_id end) v8,
count(distinct case when prof is null and LpuUnitType_SysNick in ('stac','dstac','pstac') then person_id end) v9,
count(distinct case when FRMPSubdivision_id in (10137,10141) or lpupmutype_id in (43,22) or lputype_id in (87,16,17) then person_id end) v10,
count(distinct case when LpuUnitType_SysNick in ('polka','fap') then person_id end) v11,
count(distinct case when DeathSvid_id is not null then person_id end) v12,
0 v13
from 
(
select distinct RepDiag_Row,RepDiag_Text1,RepDiag_Text2,RepDiag_Text3 from RD_3000) rd
left join 
(
select RepDiag_Row,
case when evns.diag_code='NO' then null
when evns.diag_code<>'NO' or evns.diag_code is null then osn end as osn,evns.person_id,
lp.LpuSectionProfile_Code,lt.LpuUnitType_SysNick,DeathSvid_id,
LpuPmuType_id,LpuType_id,LpuUnit_id,LpuSectionAge_id,FRMPSubdivision_id,
case 
when LpuSectionProfile_Code in ('3','2','136','184','137') then 5
when LpuSectionProfile_Code in ('54','164','99','112','1','114','113','116','20','81') then 6
when LpuSectionProfile_Code not in ('28','54','164','99','112','1','114','113','116','20','81') then 7
when LpuSectionProfile_Code='28' then 8 end prof
from evns_diag evns
left join v_personstate ps  on evns.Person_id=ps.person_id
left join v_SocStatus st  on st.SocStatus_id=ps.SocStatus_id
left join dbo.Address as adr  on adr.Address_id = coalesce(ps.pAddress_id,ps.uaddress_id)
left join v_LpuUnitType lt  on evns.lpuunittype_id=lt.lpuunittype_id
left join v_LpuSectionProfile lp on lp.LpuSectionProfile_id=evns.LpuSectionProfile_id
left join lateral
 (
	select
		DS.DeathSvid_id,
		DS.Person_id as PersonDS,
		DS.DeathPlace_id,
		left (DiagDS.Diag_Code, 3) as Diag_Code
	from v_DeathSvid DS  
	left join Diag DiagDS  on DiagDS.diag_id = coalesce(DS.Diag_mid,DS.Diag_tid,DS.Diag_iid)
	where (cast(DS.DeathSvid_DeathDate as date) between p_BegDate and p_EndDate)
	and ds.person_id=evns.person_id
	and DiagDS.diag_id=evns.Diag_id
) ds on true

) q  on q.RepDiag_Row=rd.RepDiag_Row and q.osn is not null
group by rd.RepDiag_Row,RepDiag_Text1,RepDiag_Text2,RepDiag_Text3
