--https://jira.is-mis.ru/browse/PROMEDWEB-29667
-- Пациенты с острой интоксикацией, вызванной употреблением алкоголя, наркотических средств и психотропных веществ (PG)
with params as (
select
to_timestamp('01.04.2020','DD.MM.YYYY') ::timestamp as BegDate
       ,
       cast(cast(GetDate() as date) as timestamp) as EndDate)
select
  EPS.EvnPS_id as "ИД КВС", -- не для вывода (для возможно сравнения со случаями в хранилище)
  EPS.EvnPS_NumCard as "Номер КВС", -- не для вывода
  rtrim(COALESCE(PS.Person_SurName, '')) || rtrim(' ' || COALESCE(PS.Person_FirName, '')) || rtrim(' ' || COALESCE(PS.Person_SecName, '')) as "ФИО",
  to_char(PS.Person_BirthDay, 'DD.MM.YYYY') as "Дата рождения",
  COALESCE(AddrU.Address_Address, '') as "Адрес регистрации",
  COALESCE(AddrP.Address_Address, '') as "Адрес проживания",
  to_char(EPS.EvnPS_setDT, 'MM.DD.YYYY') as "Дата госпитализации",
  ES.Diag_Code as "Диагноз основной",
  COALESCE(ltrim(DiagSop.DiagSop_List), '') as "Диагноз сопутствующий",
  COALESCE(PhA.PrehospArrive_Name, '') as "Кем доставлен",
  L.Lpu_Name as "Место госпитализации",
  case when ESToxic.cntESec > 1 then 'Да' else 'Нет' end as "Кол-во движ. с инток. больше одного" -- не для вывода
--from dbo.v_Lpu L
--inner join dbo.v_EvnPS EPS on EPS.Lpu_id = L.Lpu_id
--  and EPS.EvnPS_setDate between (select BegDate from params) and (select EndDate from params)
--  and COALESCE(L.Lpu_IsTest, 1) = 1
from 
(
  select * 
  from dbo.v_EvnPS EPS
  where 
    EPS.EvnPS_setDate between (select BegDate from params) and (select EndDate from params)
) EPS 

inner join lateral (select * from dbo.v_Lpu L where EPS.Lpu_id = L.Lpu_id and COALESCE(L.Lpu_IsTest, 1) = 1 limit 1) L on true
inner join lateral (select * from dbo.v_PersonState PS where PS.Person_id = EPS.Person_id limit 1) PS on true

INNER JOIN LATERAL
(
  select
    ES.EvnSection_id,
  ES.LpuSection_id,
  ES.Diag_id,
  D.Diag_Code,
  ES.EvnSection_setDate,
  ES.EvnSection_disDate
  from dbo.v_EvnSection ES
  inner join dbo.Diag D on D.Diag_id = ES.Diag_id -- select * from dbo.Diag where DiagLevel_id > 3 and Diag_Code between 'F10' and 'F19.9' and right(Diag_Code, 1) <  '7'
  and D.Diag_Code between 'F10.0' and 'F19.6'
  and right(D.Diag_Code, 1) <= '6'
  and COALESCE(ES.EvnSection_IsPriem, 1) = 1
  and ES.EvnSection_setDate <= (select EndDate from params)
  where
    ES.EvnSection_pid = EPS.EvnPS_id
  order by ES.EvnSection_setDT
  limit 1
) ES on true

INNER JOIN LATERAL
(
  select
    COUNT(ES.EvnSection_id) as cntESec
  from dbo.v_EvnSection ES
  inner join dbo.Diag D on D.Diag_id = ES.Diag_id -- select * from dbo.Diag where DiagLevel_id > 3 and Diag_Code between 'F10' and 'F19.9' and right(Diag_Code, 1) <  '7'
  and D.Diag_Code between 'F10.0' and 'F19.6'
  and right(D.Diag_Code, 1) <= '6'
  and COALESCE(ES.EvnSection_IsPriem, 1) = 1
  and ES.EvnSection_setDate <= (select EndDate from params)
  where
    ES.EvnSection_pid = EPS.EvnPS_id
) ESToxic on true

inner join lateral (select * from dbo.v_LpuSection LS where LS.LpuSection_id = ES.LpuSection_id limit 1) LS on true
inner join lateral (select * from dbo.v_LpuUnit LU where LU.LpuUnit_id = LS.LpuUnit_id and LU.LpuUnitType_SysNick = 'stac' limit 1) LU on true  
left join dbo.v_PrehospArrive PhA on PhA.PrehospArrive_id = EPS.PrehospArrive_id
left join dbo.Address AddrP on AddrP.Address_id = PS.PAddress_id
left join dbo.Address AddrU on AddrU.Address_id = PS.UAddress_id
LEFT JOIN LATERAL
(
  select string_agg(Diag_SCode, ', ') as DiagSop_List
  from (select D.Diag_SCode
  from dbo.v_EvnDiagPS EDPS
  inner join dbo.Diag D on D.Diag_id = EDPS.Diag_id
    and COALESCE(EDPS.DiagSetClass_id, 1) <> 1 -- select * from v_DiagSetClass
  where
    EDPS.EvnDiagPS_pid = ES.EvnSection_id
  order by D.Diag_SCode
  ) t
) DiagSop on true
--where ESToxic.cntESec > 1
