--https://jira.is-mis.ru/browse/PROMEDWEB-5701
--Отчет по исходу госпитализации (перечень пациентов)
with params as (
select cast (null as bigint) as Lpu_id,
       '01.01.2020'::timestamp as BegDate,
       '31.12.2021'::timestamp as EndDate),
Lpu_W as
(
  select
    L.Lpu_id,
  L.Lpu_Nick,
  L.Lpu_Name,
  L.PAddress_id,
  L.UAddress_id,
  L.Org_id,
  L.Lpu_OKATO,
  L.Lpu_Ogrn
  from dbo.v_Lpu L
  where
    COALESCE(L.Lpu_IsTest, 1) = 1
)
select
distinct 
Lpu.Lpu_Ogrn ,
EPS.EvnPS_Numcard ,
PS.Person_surname ,
PS.Person_firname ,
PS.Person_secname ,
PS.Person_birthday,
D.Diag_code ,
extract (year from EPS.EvnPS_setDT ) "year"
from 
(
  select * 
  from dbo.v_EvnPS as EPS 
  where 
    EPS.EvnPS_setDate between (select begdate from params) and (select EndDate from params)
    and (EPS.Lpu_id = (select Lpu_id from params) or (select Lpu_id from params) is null)
) EPS 
--
inner join lateral 
(
  select * 
  from dbo.v_Lpu L
  where 
    L.Lpu_id = EPS.lpu_id
    and COALESCE(L.Lpu_IsTest, 1) = 1
  limit 1  
) Lpu on true
inner join 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 lateral (select * from v_lpusection as lpusection where lpusection.lpusection_id = ES.lpusection_id limit 1) lpusection on true
inner join dbo.v_LpuUnit LU on LU.LpuUnit_id = lpusection.LpuUnit_id
and LU.LpuUnitType_SysNick in ('stac') 
inner join lateral (select * from v_PersonState as PS where EPS.Person_id = PS.Person_id limit 1) PS on true
inner join lateral (select * from v_Diag as D where D.Diag_id = COALESCE(ES.Diag_id, EPS.Diag_id) limit 1) D on true
where
 (( D.Diag_code between 'A00' and 'B99.999') 
or D.Diag_code ilike 'j1[2-8]%' 
or D.Diag_code ilike '%B34.2%' or D.Diag_code ilike '%U07.1%'
or D.Diag_code ilike '%U07.2%' or ( D.Diag_code between 'T36' and 'T65.999'))
