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
(3,'Z22.8','Z22.8','A26.05.009'),
(3,'Z22.8','Z22.8','A26.05.067'),
(3,'Z22.8','Z22.8','A26.05.071'),
(3,'Z22.8','Z22.8','A26.05.073'),
(3,'Z22.8','Z22.8','A26.06.115'),
(3,'Z22.8','Z22.8','A26.06.129'),
(3,'Z22.8','Z22.8','A26.05.067.001'),
(3,'Z22.8','Z22.8','A26.05.071.001')
) as t_usl(Rowusl,RepDiag_FromDiag,RepDiag_ToDiag,UslugaComplex_code)
),
RD_2000 as(
select RepDiag_Row,RepDiag_Text1,RepDiag_Text2,RepDiag_Text3,RepDiag_FromDiag,RepDiag_ToDiag
from(values
(1,'Малярия впервые выявленная','01',	'В50-В54','B50','B54.9'),	--			
(2,'из нее малярия, вызванная  Plasmodium falciparum','02',	'В50.0'	,'B50.0','B50.0'),--			
(3,'Паразитоносительство малярии','03',	'Z22.8'	,'Z22.8','Z22.8'),			
(4,'Лямблиоз','04',	'A07.1'	,'A07.1','A07.1'),			
(5,'Криптоспоридиоз','05',	'A07.2'	,'A07.2','A07.2'),			
(6,'Токсоплазмоз','06',	'B58','B58','B58.9'),				
(7,'Амебиаз','07',	'A06','A06','A06.9'),				
(8,'Лейшманиоз','08',	'В55','B55','B55.9'),	--			
(9,'из него: висцеральный лейшманиоз','09',	'В55.0'	,'B55.0','B55.0'),--			
(10,'кожный лейшманиоз','10',	'В55.1'	,'B55.1','B55.1'),--			
(11,'кожно-слизистый лейшманиоз','11',	'B55.2'	,'B55.2','B55.2'),--			
(12,'Другие протозойные болезни','12',	'A07.0,3,8, B56, B57, B59, B60.0,1','A07.0','A07.0'),
(12,'Другие протозойные болезни','12',	'A07.0,3,8, B56, B57, B59, B60.0,1'	,'A07.3','A07.3'),
(12,'Другие протозойные болезни','12',	'A07.0,3,8, B56, B57, B59, B60.0,1','A07.8','A07.8'),	
(12,'Другие протозойные болезни','12',	'A07.0,3,8, B56, B57, B59, B60.0,1'	,'B56','B56.9'),
(12,'Другие протозойные болезни','12',	'A07.0,3,8, B56, B57, B59, B60.0,1'	,'B57','B57.9'),
(12,'Другие протозойные болезни','12',	'A07.0,3,8, B56, B57, B59, B60.0,1'	,'B59','B59.9'),
(12,'Другие протозойные болезни','12',	'A07.0,3,8, B56, B57, B59, B60.0,1'	,'B60.0','B60.0'),	
(12,'Другие протозойные болезни','12',	'A07.0,3,8, B56, B57, B59, B60.0,1'	,'B60.1','B60.1'),			
(13,'из них протозойная кишечная болезнь, вызываемая Blastocystis hominis','13',	'A07.8'	,'A07.8','A07.8'),	--		
(14,'Аскаридоз','14',	'B77','B77','B77.9'),				
(15,'Трихоцефалез','15',	'B79','B79','B79.9'),				
(16,'Энтеробиоз','16',	'B80','B80','B80.9'),				
(17,'Трихинеллез','17',	'B75','B75','B75.9'),				
(18,'Токсокароз','18',	'B83.0'	,'B83.0','B83.0'),			
(19,'Инвазия, вызванная Taenia saginata','19',	'B68.1'	,'B68.1','B68.1'),			
(20,'Инвазия, вызванная Taenia solium','20',	'B68.0','B68.0','B68.0'),				
(21,'Гименолепидоз','21',	'B71.0','B71.0','B71.0'),				
(22,'Дифиллоботриоз','22',	'B70.0','B70.0','B70.0'),				
(23,'Дирофиляриоз','23',	'В74.8','B74.8','B74.8'),--				
(24,'Эхинококкоз, вызванный Echinococcus granulosus и неуточненный','24',	'B67.0, 1, 2, 3, 4, 8, 9','B67.0','B67.0'),	
(24,'Эхинококкоз, вызванный Echinococcus granulosus и неуточненный','24',	'B67.0, 1, 2, 3, 4, 8, 9','B67.1','B67.1'),	
(24,'Эхинококкоз, вызванный Echinococcus granulosus и неуточненный','24',	'B67.0, 1, 2, 3, 4, 8, 9','B67.2','B67.2'),	
(24,'Эхинококкоз, вызванный Echinococcus granulosus и неуточненный','24',	'B67.0, 1, 2, 3, 4, 8, 9','B67.3','B67.3'),	
(24,'Эхинококкоз, вызванный Echinococcus granulosus и неуточненный','24',	'B67.0, 1, 2, 3, 4, 8, 9','B67.4','B67.4'),	
(24,'Эхинококкоз, вызванный Echinococcus granulosus и неуточненный','24',	'B67.0, 1, 2, 3, 4, 8, 9','B67.8','B67.8'),	
(24,'Эхинококкоз, вызванный Echinococcus granulosus и неуточненный','24',	'B67.0, 1, 2, 3, 4, 8, 9','B67.9','B67.9'),	
(25,'Эхинококкоз, вызванный Echinococcus multiocularis','25',	'В67.5-В67.7','B67.5','B67.7'),		--		
(26,'Описторхоз','26',	'B66.0','B66.0','B66.0'),				
(27,'Клонорхоз','27',	'B66.1'	,'B66.1','B66.1'),			
(28,'Другие гельминтозы','28',	'B65, B66.2.3.4.5.8, B69, B70.1, B71.1, B72, B73, B74.0,3, B76.0,1, B78, B81.0,1,2,3, B83.2	','B65','B65.9'),	
(28,'Другие гельминтозы','28',	'B65, B66.2.3.4.5.8, B69, B70.1, B71.1, B72, B73, B74.0,3, B76.0,1, B78, B81.0,1,2,3, B83.2	','B66.2','B66.2'),
(28,'Другие гельминтозы','28',	'B65, B66.2.3.4.5.8, B69, B70.1, B71.1, B72, B73, B74.0,3, B76.0,1, B78, B81.0,1,2,3, B83.2	','B66.3','B66.3'),
(28,'Другие гельминтозы','28',	'B65, B66.2.3.4.5.8, B69, B70.1, B71.1, B72, B73, B74.0,3, B76.0,1, B78, B81.0,1,2,3, B83.2	','B66.4','B66.4'),
(28,'Другие гельминтозы','28',	'B65, B66.2.3.4.5.8, B69, B70.1, B71.1, B72, B73, B74.0,3, B76.0,1, B78, B81.0,1,2,3, B83.2	','B66.5','B66.5'),
(28,'Другие гельминтозы','28',	'B65, B66.2.3.4.5.8, B69, B70.1, B71.1, B72, B73, B74.0,3, B76.0,1, B78, B81.0,1,2,3, B83.2	','B66.8','B66.8'),


(28,'Другие гельминтозы','28',	'B65, B66.2.3.4.5.8, B69, B70.1, B71.1, B72, B73, B74.0,3, B76.0,1, B78, B81.0,1,2,3, B83.2	','B69','B69.9'),
(28,'Другие гельминтозы','28',	'B65, B66.2.3.4.5.8, B69, B70.1, B71.1, B72, B73, B74.0,3, B76.0,1, B78, B81.0,1,2,3, B83.2	','B70.1','B70.1'),
(28,'Другие гельминтозы','28',	'B65, B66.2.3.4.5.8, B69, B70.1, B71.1, B72, B73, B74.0,3, B76.0,1, B78, B81.0,1,2,3, B83.2	','B71.1','B71.1'),	

(28,'Другие гельминтозы','28',	'B65, B66.2.3.4.5.8, B69, B70.1, B71.1, B72, B73, B74.0,3, B76.0,1, B78, B81.0,1,2,3, B83.2	','B72','B72.9'),	
(28,'Другие гельминтозы','28',	'B65, B66.2.3.4.5.8, B69, B70.1, B71.1, B72, B73, B74.0,3, B76.0,1, B78, B81.0,1,2,3, B83.2	','B73','B73.9'),
(28,'Другие гельминтозы','28',	'B65, B66.2.3.4.5.8, B69, B70.1, B71.1, B72, B73, B74.0,3, B76.0,1, B78, B81.0,1,2,3, B83.2	','B74.0','B74.0'),
(28,'Другие гельминтозы','28',	'B65, B66.2.3.4.5.8, B69, B70.1, B71.1, B72, B73, B74.0,3, B76.0,1, B78, B81.0,1,2,3, B83.2	','B74.3','B74.3'),
(28,'Другие гельминтозы','28',	'B65, B66.2.3.4.5.8, B69, B70.1, B71.1, B72, B73, B74.0,3, B76.0,1, B78, B81.0,1,2,3, B83.2	','B76.0','B76.0'),
(28,'Другие гельминтозы','28',	'B65, B66.2.3.4.5.8, B69, B70.1, B71.1, B72, B73, B74.0,3, B76.0,1, B78, B81.0,1,2,3, B83.2	','B76.1','B76.1'),
(28,'Другие гельминтозы','28',	'B65, B66.2.3.4.5.8, B69, B70.1, B71.1, B72, B73, B74.0,3, B76.0,1, B78, B81.0,1,2,3, B83.2	','B78','B78.9'),	
(28,'Другие гельминтозы','28',	'B65, B66.2.3.4.5.8, B69, B70.1, B71.1, B72, B73, B74.0,3, B76.0,1, B78, B81.0,1,2,3, B83.2	','B81.0','B81.0'),
(28,'Другие гельминтозы','28',	'B65, B66.2.3.4.5.8, B69, B70.1, B71.1, B72, B73, B74.0,3, B76.0,1, B78, B81.0,1,2,3, B83.2	','B81.1','B81.1'),
(28,'Другие гельминтозы','28',	'B65, B66.2.3.4.5.8, B69, B70.1, B71.1, B72, B73, B74.0,3, B76.0,1, B78, B81.0,1,2,3, B83.2	','B81.2','B81.2'),
(28,'Другие гельминтозы','28',	'B65, B66.2.3.4.5.8, B69, B70.1, B71.1, B72, B73, B74.0,3, B76.0,1, B78, B81.0,1,2,3, B83.2	','B81.3','B81.3')
) as t_table(RepDiag_Row,RepDiag_Text1,RepDiag_Text2,RepDiag_Text3,RepDiag_FromDiag,RepDiag_ToDiag)
),
evns as
(
select evn.evnpl_id evn_id,evn.evnpl_numcard,EVPL.EvnVizitPL_id,
RepDiag_Row,evn.Lpu_id,EVPL.Diag_id,dg.Diag_Code osn,
diagsop,
evn.person_id
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 (Diag.Diag_Code in ('U07.1', 'U07.2') or left(diag.Diag_Code,3) between 'J12' and 'J18')
	and DiagSop.EvnDiagPLSop_pid = evpl.EvnVizitPL_id --and DiagSop.DiagSetClass_id = 3
) sop 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_2000 rd on dg.Diag_Code between RepDiag_FromDiag and RepDiag_ToDiag
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) 
	
union all
select eps.evnps_id evn_id,eps.evnps_numcard,ES.EvnSection_id,
RepDiag_Row,eps.Lpu_id,es.diag_id,dg.diag_code osn,
diagsop,
eps.person_id
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 (Diag.Diag_Code in ('U07.1', 'U07.2') or left(diag.Diag_Code,3) between 'J12' and 'J18')
	and EDPS.EvnDiagPS_pid = es.EvnSection_id --and EDPS.DiagSetClass_id=3
) sop on true
inner 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_2000 rd on dg.Diag_Code between RepDiag_FromDiag and RepDiag_ToDiag
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)
),


evns_diag as
(
select  
case 
when RepDiag_Row=3 then
	case 	
	when osn='Z22.8' and  rusl.UslugaComplex_code is not null then osn 
	when osn='Z22.8' and  rusl.UslugaComplex_code is null then 'NO'
	end
when RepDiag_Row<>3 then osn
end 
Diag_Code,evns.*,rusl.UslugaComplex_code,params.*
from params
inner join evns on true
left join lateral
(
	select distinct UC.UslugaComplex_id,UC.UslugaComplex_code
	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_pid = EvnVizitPL_id
	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 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,RepDiag_Text1,RepDiag_Text2,RepDiag_Text3,
count(distinct q.person_id) v4,
count(distinct case when age between 0 and 17 then q.person_id end) v5,
count(distinct case when age between 0 and 14 then q.person_id end) v6,
count(distinct case when ageM between 0 and 12 then q.person_id end) v7,
count(distinct case when age between 1 and 2 then q.person_id end) v8,
count(distinct case when age between 3 and 6 then q.person_id end) v9,
count(distinct case when age between 3 and 6 and SocStatus_SysNick='child_yasli' then q.person_id end) v10,
count(distinct case when KLAreaType_id = 2 then q.person_id end) v11,
count(distinct case when KLAreaType_id = 2 and age between 0 and 17 then q.person_id end) v12,
count(distinct case when DeathSvid_id is not null then q.person_id end) v13,
count(distinct case when DeathSvid_id is not null and age between 0 and 17 then q.person_id end) v14
from 
(select distinct RepDiag_Row,RepDiag_Text1,RepDiag_Text2,RepDiag_Text3 from RD_2000) 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,dbo.AgeYMD(ps.Person_BirthDay,p_enddate,1) age,dbo.AgeYMD(ps.Person_BirthDay,p_enddate,2) ageM,
adr.KLAreaType_id,DS.DeathSvid_id,SocStatus_SysNick
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 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 osn is not null
group by rd.RepDiag_Row,RepDiag_Text1,RepDiag_Text2,RepDiag_Text3
order by RepDiag_Row
