with params as (
select --cast (null as bigint) as Lpu_id,
       cast ('01.01.2021' as timestamp) as begdate,
       cast ('31.12.2021' as timestamp) as enddate   
),
Lpu_W as
(
  select 
    L.Lpu_id,
	L.Lpu_Name,
	L.Lpu_Ogrn
  from dbo.v_Lpu L
  where coalesce(L.Lpu_IsTest, 1) = 1 
),
g as
(select L.Lpu_Name,
        count(person_id) as cnt
from 
(
select distinct
Lpu.Lpu_Name,
EPS.EvnPS_setDate::date, 
EPS.person_id
from Lpu_W as Lpu 
inner join dbo.v_EvnPS as EPS on Lpu.Lpu_id =EPS.lpu_id
inner join dbo.v_EvnSection as ES on ES.EvnSection_pid = EPS.EvnPS_id
  and ES.EvnSection_Count-1 = ES.EvnSection_index  
  and EPS.PrehospWaifRefuseCause_id is null
inner join dbo.v_lpusection as lpusection on lpusection.lpusection_id =ES.lpusection_id
inner join dbo.v_LpuUnit LU on LU.LpuUnit_id = lpusection.LpuUnit_id
and LU.LpuUnitType_SysNick in ('stac') 
where EPS.EvnPS_setDate::date between (select begdate::date from params) and (select enddate::date from params)
order by EPS.EvnPS_setDate::date desc) L
group by L.Lpu_Name
),
p as (
select PT.nam,
	   count (PT.person_id) as cnt
from (
select distinct on (Lpu.Lpu_Name, EPS.EvnPS_setDate::date, EPS.person_id)
Lpu.Lpu_Name as nam,
EPS.EvnPS_setDate::date, 
case when EPS.evnps_disdate is not null then EPS.evnps_disdate::date else '01.01.2025'::date end as evnps_disdate,
EPS.person_id,
ROW_NUMBER() OVER (partition by EPS.person_id order by EPS.EvnPS_setDate::date) as rnum,
LT.leavetype_name
from  
(select * from dbo.v_EvnPS as EPS 
      where EPS.EvnPS_setDate::date between (select begdate::date - interval '2 days' from params) and (select enddate::date from params)
      and (EPS.EvnPS_disDate > (select begdate::date from params) or EPS.EvnPS_disDate is null)
      and EPS.EvnPS_setDate >= cast('01.01.2020' as date)
) EPS
inner join lateral 
(select * from Lpu_W as Lpu 
where Lpu.Lpu_id = EPS.lpu_id
limit 1) Lpu on true
inner join lateral 
(select * from dbo.v_EvnSection as ES 
  where ES.EvnSection_pid = EPS.EvnPS_id
  and ES.EvnSection_Count-1 = ES.EvnSection_index  
  and EPS.PrehospWaifRefuseCause_id is null
  limit 1) ES on true
inner join lateral 
(select * from dbo.v_lpusection as lpusection where lpusection.lpusection_id = ES.lpusection_id
limit 1) lpusection on true
inner join lateral 
(select * from dbo.v_LpuUnit LU where LU.LpuUnit_id = lpusection.LpuUnit_id
and LU.LpuUnitType_SysNick in ('stac') 
limit 1) LU on true
left join lateral
(select * from v_LeaveType LT where LT.LeaveType_id = EPS.LeaveType_id
limit 1) LT on true
order by EPS.person_id) as PT
inner join
(
select distinct on (Lpu.Lpu_Name, EPS.EvnPS_setDate::date, EPS.person_id)
Lpu.Lpu_Name,
EPS.EvnPS_setDate::date, 
case when EPS.evnps_disdate is not null then EPS.evnps_disdate::date else '01.01.2025'::date end as evnps_disdate,
EPS.person_id as id,
ROW_NUMBER() OVER (partition by EPS.person_id order by EPS.EvnPS_setDate::date) as rnum,
LT.leavetype_name
from  
(select * from dbo.v_EvnPS as EPS 
      where EPS.EvnPS_setDate::date between (select begdate::date - interval '2 days' from params) and (select enddate::date from params)
      and (EPS.EvnPS_disDate > (select begdate::date from params) or EPS.EvnPS_disDate is null)
      and EPS.EvnPS_setDate >= cast('01.01.2020' as date)
) EPS
inner join lateral 
(select * from Lpu_W as Lpu 
where Lpu.Lpu_id = EPS.lpu_id
limit 1) Lpu on true
inner join lateral 
(select * from dbo.v_EvnSection as ES 
  where ES.EvnSection_pid = EPS.EvnPS_id
  and ES.EvnSection_Count-1 = ES.EvnSection_index  
  and EPS.PrehospWaifRefuseCause_id is null
  limit 1) ES on true
inner join lateral 
(select * from dbo.v_lpusection as lpusection where lpusection.lpusection_id = ES.lpusection_id
limit 1) lpusection on true
inner join lateral 
(select * from dbo.v_LpuUnit LU where LU.LpuUnit_id = lpusection.LpuUnit_id
and LU.LpuUnitType_SysNick in ('stac') 
limit 1) LU on true
left join lateral
(select * from v_LeaveType LT where LT.LeaveType_id = EPS.LeaveType_id
limit 1) LT on true
order by EPS.person_id) as PP 
on PT.person_id = PP.id and PT.rnum - 1 = PP.rnum and PP.leavetype_name ilike '%ВЫПИСАН%' 
where date_part('days', PT.EvnPS_setDate::timestamp - PP.evnps_disdate::timestamp) < 3
group by PT.nam
),
A as (
select A.Lpu_Name,
	   round(sum(A.diff)/count(A.person_id)) + 1 || ' мин' as cnt
from (
select distinct
Lpu.Lpu_Name,
EPS.EvnPS_setdt, 
EPS.person_id,
ES.EvnSection_disdt,
date_part('min', ES.EvnSection_disdt::timestamp - EPS.EvnPs_setDT::timestamp) + date_part('hours', ES.EvnSection_disdt::timestamp - EPS.EvnPs_setDT::timestamp)*60 as diff
from Lpu_W as Lpu 
inner join dbo.v_EvnPS as EPS on Lpu.Lpu_id =EPS.lpu_id
inner join dbo.v_EvnSection as ES on ES.EvnSection_pid = EPS.EvnPS_id
where EPS.EvnPS_setDate::date between (select begdate::date from params) and (select enddate::date from params) 
and ES.EvnSection_IsPriem is not null 
and ES.EvnSection_disdt is not null
order by EPS.EvnPS_setdt desc) A
group by A.Lpu_Name
),
B as 
(
select B.Lpu_Name,
	   round(sum(B.diff)/count(B.person_id)) + 1 || ' мин' as cnt
from (
select distinct
Lpu.Lpu_Name,
EPS.EvnPS_setdt, 
EPS.person_id,
ES.EvnSection_disdt,
date_part('min', ES.EvnSection_disdt::timestamp - EPS.EvnPs_setDT::timestamp) + date_part('hours', ES.EvnSection_disdt::timestamp - EPS.EvnPs_setDT::timestamp)*60 as diff,
DSPPriem.DiagSetPhase_Name
from Lpu_W as Lpu 
inner join dbo.v_EvnPS as EPS on Lpu.Lpu_id =EPS.lpu_id
inner join dbo.v_EvnSection as ES on ES.EvnSection_pid = EPS.EvnPS_id
left join dbo.v_DiagSetPhase DSPPriem on DSPPriem.DiagSetPhase_id  = EPS.DiagSetPhase_pid
where EPS.EvnPS_setDate::date between (select begdate::date from params) and (select enddate::date from params) 
and ES.EvnSection_IsPriem is not null 
and ES.EvnSection_disdt is not null
and (DSPPriem.DiagSetPhase_Name ilike 'ТЯЖЕЛОЕ' or DSPPriem.DiagSetPhase_Name ilike 'СРЕДНЕЙ ТЯЖЕСТИ' or DSPPriem.DiagSetPhase_Name ilike '%КРАЙНЕ%')
order by EPS.EvnPS_setdt desc) B
group by B.Lpu_Name
),
C as (
select
C.Lpu_Name, 
count(case when C.Diag_id <> C.Diag_pid and C.EvnSection_IsPriem is null then C.person_id end) + count(case when C.EvnSection_IsPriem is not null then C.person_id end) as cnt
from
(
select distinct on (Lpu.Lpu_Name, EPS.EvnPS_setDate, EPS.person_id)
EPS.evnps_numcard,
Lpu.Lpu_Name,
EPS.EvnPS_setDate::date, 
EPS.person_id,
ES.Diag_id,
EPS.Diag_pid,
ES.EvnSection_IsPriem
from Lpu_W as Lpu 
inner join dbo.v_EvnPS as EPS on Lpu.Lpu_id =EPS.lpu_id
inner join dbo.v_EvnSection as ES on ES.EvnSection_pid = EPS.EvnPS_id 
left join v_Diag as D on D.Diag_id = ES.Diag_id
left join dbo.v_Diag as DPriem on DPriem.Diag_id = EPS.Diag_pid
where EPS.EvnPS_setDate::date between (select begdate::date from params) and (select enddate::date from params)
order by Lpu.Lpu_Name, EPS.EvnPS_setDate desc, EPS.person_id) C
group by C.Lpu_Name
),
D as 
(
select D.Lpu_Name,
	   count(D.person_id) as cnt
from 
(
select
Lpu.Lpu_Name,
EPS.EvnPS_setdt, 
EPS.EvnPS_disdt,
ES.evnsection_setdt,
ES.evnsection_disdt,
EPS.person_id,
lpusection.lpusection_name
from Lpu_W as Lpu 
inner join dbo.v_EvnPS as EPS on Lpu.Lpu_id =EPS.lpu_id
inner join dbo.v_EvnSection as ES on ES.EvnSection_pid = EPS.EvnPS_id  
  and EPS.PrehospWaifRefuseCause_id = 10
inner join dbo.v_lpusection as lpusection on lpusection.lpusection_id =ES.lpusection_id
where EPS.EvnPS_setDate::date between (select begdate::date from params) and (select enddate::date from params)
order by EPS.EvnPS_setdt desc) D
group by D.Lpu_Name
),
F as (
select F.Lpu_Name,
       count (F.person_id) as cnt 
from (
select distinct on (Lpu.Lpu_Name, EPS.EvnPS_setdt, EPS.person_id)
Lpu.Lpu_Name,
EPS.EvnPS_setdt, 
EPS.person_id,
lpusection.lpusection_name,
PS.person_deaddt 
from Lpu_W as Lpu 
inner join dbo.v_EvnPS as EPS on Lpu.Lpu_id =EPS.lpu_id
inner join dbo.v_EvnSection as ES on ES.EvnSection_pid = EPS.EvnPS_id 
inner join dbo.v_lpusection as lpusection on lpusection.lpusection_id =ES.lpusection_id
inner join dbo.v_PersonState PS on PS.Person_id = EPS.Person_id
where EPS.EvnPS_setDate::date between (select begdate::date from params) and (select enddate::date from params)
and PS.person_isdead = 2
and date_part('hour', PS.person_deaddt::timestamp - EPS.EvnPS_setdt::timestamp) < 24
order by EPS.EvnPS_setdt desc) F
group by F.Lpu_Name
),
Amb as (
select Amb.Lpu_Name,
       count(Amb.person_id) as cnt
from 
(
select distinct on (Lpu.Lpu_Name, EPS.EvnPS_setDate::date, EPS.person_id)
Lpu.Lpu_Name,
EPS.EvnPS_setDate::date, 
EPS.person_id,
LU.LpuUnitType_SysNick,
lpusection.lpusection_name
from Lpu_W as Lpu 
inner join dbo.v_EvnPS as EPS on Lpu.Lpu_id =EPS.lpu_id
inner join dbo.v_EvnSection as ES on ES.EvnSection_pid = EPS.EvnPS_id
  and ES.EvnSection_Count-1 = ES.EvnSection_index  
  and EPS.PrehospWaifRefuseCause_id is null
inner join dbo.v_lpusection as lpusection on lpusection.lpusection_id =ES.lpusection_id
inner join dbo.v_LpuUnit LU on LU.LpuUnit_id = lpusection.LpuUnit_id
and LU.LpuUnitType_SysNick in ('dstac', 'hstac', 'pstac') 
where EPS.EvnPS_setDate::date between (select begdate::date from params) and (select enddate::date from params)
order by EPS.EvnPS_setDate::date desc) Amb 
group by Amb.Lpu_Name
)
select g.Lpu_Name "Медицинская организация стационар",
	   g.cnt "Количество госпитализированных пациентов",
	   case when p.cnt is not null then p.cnt else 0 end "Количество случаев повторной госпитализации после выписки",
	   A.cnt "Среднее время пребывание в приемном отделении",
	   B.cnt "Среднее время оказания ЭМП в приемном отделении больным, находившимся в тяжелом состоянии (мин.)",
	   case when C.cnt is not null then C.cnt else 0 end "Количество расхождений в МКБ ",
	   case when D.cnt is not null then D.cnt else 0 end "Летальность",
	   case when F.cnt is not null then F.cnt else 0 end "Досуточная летальность",
	   case when Amb.cnt is not null then Amb.cnt else 0 end "Пациентs выписанных на амбулаторное лечение после пребывания в приемном отделении"
from g
full join p on p.nam = g.Lpu_Name
full join A on A.Lpu_Name = g.Lpu_Name
full join B on B.Lpu_Name = g.Lpu_Name
full join C on C.Lpu_Name = g.Lpu_Name
full join D on D.Lpu_Name = g.Lpu_Name
full join F on F.Lpu_Name = g.Lpu_Name
full join Amb on Amb.Lpu_Name = g.Lpu_Name
