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($5 as bigint) as p_lpuregion_id,
		cast($6 as int) as p_AgeFrom,
		cast($7 as int) as p_AgeTo,
		cast($8 as bigint) as p_onlyPC,
		cast($9 as timestamp) as p_begdate,	
		cast($10 as timestamp) as p_enddate
),

Lpu_W as (
select l.Lpu_id from params
left join rpt.GetLpu_succession_pg(p_lpu_id,p_begdate,p_enddate) l on 1=1
),

EPL as (
select 
distinct
case when  epl.EvnClass_id =3 then 1
	 when  epl.EvnClass_id =6 then 2
	 end as priorit,
epl.Person_Id,
epl.Evn_id as Evn_ID, --идешник
left(dOsn.Diag_Code,3) as Diag_CodeOsn,
EPL.Diag_lid as Diag_idOsn, --Закл
dVne.Diag_Code as  Diag_CodeVne,
EPL.Diag_concid  as Diag_idVne, --внеш причина
epl.lpu_id

from EvnPL epl  
left join params on 1=1
inner join lateral
( select  PS.Person_id
    from v_PersonState PS  
	where epl.Person_id=PS.Person_id
	and rpt.WorkAge(ps.Person_BirthDay,ps.Sex_id,epl.Evn_setDT)=1
	and dbo.AgeYMD(PS.Person_BirthDay,epl.Evn_setDT,1) between coalesce(p_AgeFrom,55) and coalesce(p_AgeTo,200) limit 1
   ) PS1 on true
inner join DeseaseType DT  on EPL.DeseaseType_id=DT.DeseaseType_id
inner join Diag dOsn  on dOsn.Diag_id = EPL.Diag_lid
	 and dOsn.Diag_Code between 'S00' and 'T98.9' 
inner join Diag dVne  on EPL.Diag_concid=dVne.Diag_id
	and dVne.Diag_Code between 'V01' and 'Y98.9'
LEFT join v_lpusection LS  on EPL.LpuSection_id=LS.LpuSection_id
left join v_LpuUnit LU  on LS.LpuUnit_id=LU.LpuUnit_id
left join lateral
    (
      select  
        PersonCard_id,
        PersonCard.LpuRegion_id,
		PersonCard.Lpu_id 
      from PersonCard 
	  left join LpuRegion  on PersonCard.LpuRegion_id = LpuRegion.LpuRegion_id
	  left join LpuRegionType  on LpuRegion.LpuRegionType_id = LpuRegionType.LpuRegionType_id
      LEFT JOIN LpuRegionType  AS LpuRegionTypeCard  ON PersonCard.LpuRegionType_id=LpuRegionTypeCard.LpuRegionType_id
      where 
        PersonCard.Person_id = epl.Person_id 
		and (LpuAttachType_id = 1)  
		and coalesce(LpuRegionType.LpuRegionType_SysNick,LpuRegionTypeCard.LpuRegionType_SysNick) in ('ter','vop','ped')
        and ((PersonCard.personcard_begDate <= p_endDate) 
		  and (PersonCard.personcard_endDate >=p_endDate or PersonCard.personcard_endDate is null))
		and (PersonCard.LpuRegion_id = p_LpuRegion_id or p_LpuRegion_id is null) 
	  order by PersonCard.PersonCard_begDate  limit 1
	) PC on true
where 
1=1
and EPL.EvnPLBase_IsFinish=2
and epl.Evn_deleted = 1
and ((epl.Lpu_id in (select Lpu_id from Lpu_W) and ( p_onlyPC is null) or p_lpu_id is null)
or (p_onlyPC=1 and (PC.Lpu_id not in (select Lpu_id from Lpu_W) or PC.Lpu_id is null) 
and epl.Lpu_id in (select Lpu_id from Lpu_W) )
or (p_onlyPC=2 and PC.Lpu_id in (select Lpu_id from Lpu_W)))--#99114
and cast(epl.Evn_disDT as date) between p_begdate and p_enddate
and 1  =case when dbo.GetRegion()=2 and    DT.DeseaseType_Code in  (1,5,6)  then 1 
                              when  dbo.GetRegion()!=2 and DT.DeseaseType_Code =1 then 1  end 
and (LU.LpuBuilding_id=p_lpuBuilding_id or p_lpuBuilding_id is null)
and (LU.LpuUnit_id=p_lpuUnit_id or p_lpuUnit_id  is null)
and (LS.LpuSection_id=p_lpuSection_id or p_lpuSection_id is null)
),

KVS as (
--КВС и отказы ( отказы поидее тоже что и обычные КВС поэтому в одном подзапросе, если нужно разделить то условие: PrehospWaifRefuseCause_id is not null)
select 
distinct
3 as priorit,
EPS.Person_Id,
EPS.EvnPS_id as Evn_ID, --идешник
left(dOsn.Diag_Code,3) as Diag_CodeOsn,
EPS.Diag_id as Diag_idOsn, --Закл
dVne.Diag_Code as  Diag_CodeVne,
coalesce(EPS.Diag_eid,ES.Diag_eid) as Diag_idVne, --внеш причина
EPS.lpu_id
from v_EvnPS EPS 
left join params on 1=1
inner join v_EvnSection ES  on EPS.EvnPS_id=EvnSection_pid
inner join lateral
( select  PS.Person_id
    from v_PersonState PS  
	where EPS.Person_id=PS.Person_id
	and rpt.WorkAge(ps.Person_BirthDay,ps.Sex_id,EPS.EvnPS_setDate)=1
	and dbo.AgeYMD(PS.Person_BirthDay,EPS.EvnPS_setDate,1) between coalesce(p_AgeFrom,55) and coalesce(p_AgeTo,200) limit 1
   ) PS1 on true
 
inner join Diag dOsn  on dOsn.Diag_id = case when EvnSection_IsPriem=2 then EPS.Diag_pid  else EPS.Diag_id end
	and dOsn.Diag_Code between 'S00' and 'T98.9'
inner join Diag dVne  on dVne.Diag_id = coalesce(EPS.Diag_eid,ES.Diag_eid)
	and dVne.Diag_Code between 'V01' and 'Y98.9'
LEFT join v_lpusection LS  on ES.LpuSection_id=LS.LpuSection_id
left join v_LpuUnit LU  on LS.LpuUnit_id=LU.LpuUnit_id
left join lateral
    (
      select 
        PersonCard_id,
        PersonCard.LpuRegion_id,
		PersonCard.Lpu_id 
      from PersonCard 
	  left join LpuRegion  on PersonCard.LpuRegion_id = LpuRegion.LpuRegion_id
	  left join LpuRegionType  on LpuRegion.LpuRegionType_id = LpuRegionType.LpuRegionType_id
      LEFT JOIN LpuRegionType AS LpuRegionTypeCard   ON PersonCard.LpuRegionType_id=LpuRegionTypeCard.LpuRegionType_id
      where 
        PersonCard.Person_id = EPS.Person_id 
		and (LpuAttachType_id = 1)  
		and coalesce(LpuRegionType.LpuRegionType_SysNick,LpuRegionTypeCard.LpuRegionType_SysNick) in ('ter','vop','ped')
        and ((PersonCard.personcard_begDate <= p_endDate) 
		  and (PersonCard.personcard_endDate >=p_endDate or PersonCard.personcard_endDate is null))
		and (PersonCard.LpuRegion_id = p_LpuRegion_id or p_LpuRegion_id is null) 
	  order by PersonCard.PersonCard_begDate limit 1 
	) PC on true
where EPS.EvnPS_disDate between p_begdate and p_enddate
and ((EPS.Lpu_id in (select Lpu_id from Lpu_W) and ( p_onlyPC is null) or p_lpu_id is null)
or (p_onlyPC=1 and (PC.Lpu_id not in (select Lpu_id from Lpu_W) or PC.Lpu_id is null) 
and EPS.Lpu_id in (select Lpu_id from Lpu_W) )
or (p_onlyPC=2 and PC.Lpu_id in (select Lpu_id from Lpu_W)))--#99114
and (LU.LpuBuilding_id=p_lpuBuilding_id or p_lpuBuilding_id is null)
and (LU.LpuUnit_id=p_lpuUnit_id or p_lpuUnit_id  is null)
and (LS.LpuSection_id=p_lpuSection_id or p_lpuSection_id is null)
and not exists ( 
select *
from EPL
where EPL.Person_id = EPS.Person_id and EPL.Diag_CodeOsn = left(dOsn.Diag_Code,3)
)
),

DS as (
--свидетельства о смерти
select 
distinct
4 as priorit,
DS.Person_Id,
DS.DeathSvid_id as Evn_ID, 
left(dOsn.Diag_Code,3) as Diag_CodeOsn,
dOsn.Diag_id as Diag_idOsn,
dVne.Diag_Code as  Diag_CodeVne,
dVne.Diag_id as Diag_idVne,
DS.lpu_id
from v_DeathSvid DS 
left join params on 1=1
inner join lateral
( select  PS.Person_id
    from v_PersonState PS  
	where DS.Person_id=PS.Person_id
	and rpt.WorkAge(ps.Person_BirthDay,ps.Sex_id,DS.DeathSvid_DeathDate)=1
	and dbo.AgeYMD(PS.Person_BirthDay,DS.DeathSvid_DeathDate,1) between coalesce(p_AgeFrom,55) and coalesce(p_AgeTo,200) limit 1
   ) PS1 on true

inner join Diag dVne  on ds.Diag_eid=dVne.Diag_id
	and DS.DeathSvid_DeathDate between p_begdate and p_enddate
inner join (select MAX(DeathSvid_insDT) as DeathSvid_insDT,
person_id from v_DeathSvid DeathSvid 
left join params on 1=1
where 
coalesce(DeathSvid.DeathSvid_IsBad,1) <> 2
and DeathSvid.DeathSvid_DeathDate between p_begdate and p_enddate
group by person_id
) maxdate on DS.Person_id=maxdate.Person_id and DS.DeathSvid_insDT=maxdate.DeathSvid_insDT
left join Diag D_mid on D_mid.Diag_id=Diag_mid
left join Diag D_tid on D_tid.Diag_id=Diag_tid
left join Diag D_iid on D_iid.Diag_id=Diag_iid
inner join Diag dOsn  on 
dOsn.Diag_id= case when D_mid.Diag_Code between 'S00' and 'T98.9' 
				   then D_mid.Diag_id
			       else case when D_tid.Diag_Code between 'S00' and 'T98.9' 
							 then D_tid.Diag_id
							 else case when D_iid.Diag_Code between 'S00' and 'T98.9'
								       then D_iid.Diag_id
								  end
						end
			   end

LEFT join v_lpusection LS  on DS.LpuSection_id=LS.LpuSection_id
left join v_LpuUnit LU   on LS.LpuUnit_id=LU.LpuUnit_id
left join lateral
    (
      select 
        PersonCard_id,
        PersonCard.LpuRegion_id,
		PersonCard.Lpu_id 
      from PersonCard 
	  left join LpuRegion  on PersonCard.LpuRegion_id = LpuRegion.LpuRegion_id
	  left join LpuRegionType  on LpuRegion.LpuRegionType_id = LpuRegionType.LpuRegionType_id
      LEFT JOIN LpuRegionType AS LpuRegionTypeCard  ON PersonCard.LpuRegionType_id=LpuRegionTypeCard.LpuRegionType_id
      where 
        PersonCard.Person_id = DS.Person_id 
		and (LpuAttachType_id = 1)  
		and coalesce(LpuRegionType.LpuRegionType_SysNick,LpuRegionTypeCard.LpuRegionType_SysNick) in ('ter','vop','ped')
        and ((PersonCard.personcard_begDate <= p_endDate) 
		  and (PersonCard.personcard_endDate >=p_endDate or PersonCard.personcard_endDate is null))
		and (PersonCard.LpuRegion_id = p_LpuRegion_id or p_LpuRegion_id is null) 
	  order by PersonCard.PersonCard_begDate  limit 1
	) PC on true
where 1=1 --DeathSvid_id=3505
and coalesce(DS.DeathSvid_IsBad,1) <> 2
and dOsn.Diag_Code between 'S00' and 'T98.9'
and dVne.Diag_Code between 'V01' and 'Y98.9'
and ((DS.Lpu_id in (select Lpu_id from Lpu_W) and (p_onlyPC is null) or p_lpu_id is null)
or (p_onlyPC=1 and (PC.Lpu_id not in (select Lpu_id from Lpu_W) or PC.Lpu_id is null) 
and DS.Lpu_id in (select Lpu_id from Lpu_W) )
or (p_onlyPC=2 and PC.Lpu_id in (select Lpu_id from Lpu_W)))--#99114
and (LU.LpuBuilding_id=p_lpuBuilding_id or p_lpuBuilding_id is null)
and (LU.LpuUnit_id=p_lpuUnit_id or p_lpuUnit_id  is null)
and (LS.LpuSection_id=p_lpuSection_id or p_lpuSection_id is null)
),

Zapros0 as (
select * from EPL--TAP
union all
select * from KVS
union all 
select * from DS
),
Zapros as (

	select priorit,Person_Id,Evn_ID, Diag_CodeOsn,Diag_idOsn, Diag_CodeVne, Diag_idVne,lpu_id,numMen
	from (
		select 
		ROW_NUMBER() OVER(PARTITION BY Person_Id order by priorit) numMen,
		ROW_NUMBER() OVER(PARTITION BY Person_Id,diag_codeosn order by priorit) ff,
		*
		from Zapros0 
	) q where ff=1
)

select RepDiag.RepDiag_Row as "RepDiag_Row",
RepDiag.RepDiag_Text1 as "RepDiag_Text1",
RepDiag.RepDiag_Text2 as "RepDiag_Text2",
RepDiag.RepDiag_Text3 as "RepDiag_Text3",
COUNT(distinct case when Zapros.Diag_CodeVne between 'V01' and 'Y98.9' then Zapros.Evn_ID end ) as v4,
COUNT(distinct case when Zapros.Diag_CodeVne between 'V01' and 'V99.9' then Zapros.Evn_ID end ) as v5,
COUNT(distinct case when 
Zapros.Diag_CodeVne in ('V01.1','V02.1','V03.1','V04.1','V06.1','V09.2','V09.3')
or Zapros.Diag_CodeVne between 'V10.4' and 'V10.9'			
or Zapros.Diag_CodeVne between 'V11.4' and 'V11.9'
or Zapros.Diag_CodeVne between 'V12.4' and 'V12.9'
or Zapros.Diag_CodeVne between 'V13.4' and 'V13.9'
or Zapros.Diag_CodeVne between 'V14.4' and 'V14.9'
or Zapros.Diag_CodeVne between 'V15.4' and 'V15.9'
or Zapros.Diag_CodeVne between 'V16.4' and 'V16.9'			
or Zapros.Diag_CodeVne between 'V17.4' and 'V17.9'
or Zapros.Diag_CodeVne between 'V18.4' and 'V18.9'  
or Zapros.Diag_CodeVne between 'V19.4' and 'V19.9'
or Zapros.Diag_CodeVne between 'V20.4' and 'V20.9'
or Zapros.Diag_CodeVne between 'V21.4' and 'V21.9'
or Zapros.Diag_CodeVne between 'V22.4' and 'V22.9'			
or Zapros.Diag_CodeVne between 'V23.4' and 'V23.9'
or Zapros.Diag_CodeVne between 'V24.4' and 'V24.9'
or Zapros.Diag_CodeVne between 'V25.4' and 'V25.9'
or Zapros.Diag_CodeVne between 'V26.4' and 'V26.9'
or Zapros.Diag_CodeVne between 'V27.4' and 'V27.9'
or Zapros.Diag_CodeVne between 'V28.4' and 'V28.9'			
or Zapros.Diag_CodeVne between 'V29.4' and 'V29.9'
or Zapros.Diag_CodeVne between 'V30.5' and 'V30.9'
or Zapros.Diag_CodeVne between 'V31.5' and 'V31.9'
or Zapros.Diag_CodeVne between 'V32.5' and 'V32.9'
or Zapros.Diag_CodeVne between 'V33.5' and 'V33.9'
or Zapros.Diag_CodeVne between 'V34.5' and 'V34.9'			
or Zapros.Diag_CodeVne between 'V35.5' and 'V35.9'
or Zapros.Diag_CodeVne between 'V36.5' and 'V36.9'
or Zapros.Diag_CodeVne between 'V37.5' and 'V37.9'
or Zapros.Diag_CodeVne between 'V38.5' and 'V38.9'
or Zapros.Diag_CodeVne between 'V39.4' and 'V39.9'
or Zapros.Diag_CodeVne between 'V40.5' and 'V40.9'			
or Zapros.Diag_CodeVne between 'V41.5' and 'V41.9'
or Zapros.Diag_CodeVne between 'V42.5' and 'V42.9'
or Zapros.Diag_CodeVne between 'V43.5' and 'V43.9'
or Zapros.Diag_CodeVne between 'V44.5' and 'V44.9'
or Zapros.Diag_CodeVne between 'V45.5' and 'V45.9'
or Zapros.Diag_CodeVne between 'V46.5' and 'V46.9'			
or Zapros.Diag_CodeVne between 'V47.5' and 'V47.9'
or Zapros.Diag_CodeVne between 'V48.5' and 'V48.9'
or Zapros.Diag_CodeVne between 'V49.4' and 'V49.9'
or Zapros.Diag_CodeVne between 'V50.5' and 'V50.9'
or Zapros.Diag_CodeVne between 'V51.5' and 'V51.9'
or Zapros.Diag_CodeVne between 'V52.5' and 'V52.9'			
or Zapros.Diag_CodeVne between 'V53.5' and 'V53.9'
or Zapros.Diag_CodeVne between 'V54.5' and 'V54.9'
or Zapros.Diag_CodeVne between 'V55.5' and 'V55.9'
or Zapros.Diag_CodeVne between 'V56.5' and 'V56.9'
or Zapros.Diag_CodeVne between 'V57.5' and 'V57.9'
or Zapros.Diag_CodeVne between 'V58.5' and 'V58.9'			
or Zapros.Diag_CodeVne between 'V59.4' and 'V59.9'
or Zapros.Diag_CodeVne between 'V60.5' and 'V60.9'
or Zapros.Diag_CodeVne between 'V61.5' and 'V61.9'
or Zapros.Diag_CodeVne between 'V62.5' and 'V62.9'
or Zapros.Diag_CodeVne between 'V63.5' and 'V63.9'
or Zapros.Diag_CodeVne between 'V64.5' and 'V64.9'			
or Zapros.Diag_CodeVne between 'V65.5' and 'V65.9'
or Zapros.Diag_CodeVne between 'V66.5' and 'V66.9'
or Zapros.Diag_CodeVne between 'V67.5' and 'V67.9'
or Zapros.Diag_CodeVne between 'V68.5' and 'V68.9'
or Zapros.Diag_CodeVne between 'V69.4' and 'V69.9'
or Zapros.Diag_CodeVne between 'V70.5' and 'V70.9'			
or Zapros.Diag_CodeVne between 'V71.5' and 'V71.9'
or Zapros.Diag_CodeVne between 'V72.5' and 'V72.9'
or Zapros.Diag_CodeVne between 'V73.5' and 'V73.9'
or Zapros.Diag_CodeVne between 'V74.5' and 'V74.9'
or Zapros.Diag_CodeVne between 'V75.5' and 'V75.9'
or Zapros.Diag_CodeVne between 'V76.5' and 'V76.9'
or Zapros.Diag_CodeVne between 'V77.5' and 'V77.9'
or Zapros.Diag_CodeVne between 'V78.5' and 'V78.9'
or Zapros.Diag_CodeVne between 'V79.4' and 'V79.9'
or Zapros.Diag_CodeVne in ('V82.1','V82.9','V89.2','V89.3')
or Zapros.Diag_CodeVne between 'V83.0' and 'V83.3'
or Zapros.Diag_CodeVne between 'V84.0' and 'V84.3'
or Zapros.Diag_CodeVne between 'V85.0' and 'V85.3'
or Zapros.Diag_CodeVne between 'V86.0' and 'V86.3'
or Zapros.Diag_CodeVne between 'V87.0' and 'V87.9'
then Zapros.Evn_ID end ) as v6,
COUNT(distinct case when Zapros.Diag_CodeVne between 'W00' and 'X59.9' then Zapros.Evn_ID end ) as v7,
COUNT(distinct case when Zapros.Diag_CodeVne between 'W65' and 'W74.9' then Zapros.Evn_ID end ) as v8,
COUNT(distinct case when Zapros.Diag_CodeVne between 'X00' and 'X09.9' then Zapros.Evn_ID end ) as v9,
COUNT(distinct case when Zapros.Diag_CodeVne between 'X40' and 'X49.9' then Zapros.Evn_ID end ) as v10,
COUNT(distinct case when Zapros.Diag_CodeVne between 'X42' and 'X42.9' then Zapros.Evn_ID end ) as v11,
COUNT(distinct case when Zapros.Diag_CodeVne between 'X45' and 'X45.9' then Zapros.Evn_ID end ) as v12,
COUNT(distinct case when Zapros.Diag_CodeVne between 'X60' and 'X84.9' then Zapros.Evn_ID end ) as v13,
COUNT(distinct case when Zapros.Diag_CodeVne between 'X62' and 'X62.9' then Zapros.Evn_ID end ) as v14,
COUNT(distinct case when Zapros.Diag_CodeVne between 'X65' and 'X65.9' then Zapros.Evn_ID end ) as v15,
COUNT(distinct case when Zapros.Diag_CodeVne between 'X85' and 'Y09.9' then Zapros.Evn_ID end ) as v16,
COUNT(distinct case when Zapros.Diag_CodeVne between 'Y10' and 'Y34.9' then Zapros.Evn_ID end ) as v17,
COUNT(distinct case when Zapros.Diag_CodeVne between 'Y35' and 'Y38.9' then Zapros.Evn_ID end ) as v18,
COUNT(distinct case when Zapros.Diag_CodeVne between 'Y40' and 'Y84.9' then Zapros.Evn_ID end ) as v19,
COUNT(distinct case when Zapros.Diag_CodeVne between 'Y85' and 'Y89.9' then Zapros.Evn_ID end ) as v20

--V01-Y98	V01-V99		W00-X59	W65-W74	X00-X09	X40-X49	X42	X45	X60-X84	X62	X65	X85-Y09	Y10-Y34	Y35-Y38	Y40-Y84	Y85-Y89
 
from RepDiag 
left join Zapros on Zapros.Diag_CodeOsn between RepDiag_FromDiag and RepDiag_ToDiag

where lower(RepDiag_SysNick)='f57' and RepDiag_Part=1
group by RepDiag.RepDiag_Row,
RepDiag.RepDiag_Text1,
RepDiag.RepDiag_Text2,
RepDiag.RepDiag_Text3
order by 1
