--https://jira.is-mis.ru/browse/PROMEDWEB-4262
-- Перечень пациентов (Стационар)
with mv as 
(
  select	
    dbo.tzGetDate() as dt
),
--
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,
	dt
  from dbo.v_Lpu L
  inner join mv on coalesce (L.Lpu_IsTest, 1) = 1 
	--не знаю надо ли накладывать на это условие
    --and L.Lpu_begDate <= DATEADD(YEAR, 1, dbo.tzGetDate()) select * from EvnClass where EvnClass_id=30
    --and (L.Lpu_endDate >= convert(datetime, '01.01.2020', 104) or L.Lpu_endDate is null)
),
--
EPS_raw as 
(
  select ROW_NUMBER()OVER(partition by EPS.Person_id order by EPS.EvnPS_insDT desc, EPS.EvnPS_id) as rnum,
    Lp.Lpu_Name as Lpu_Name,
    Lp.Lpu_OGRN,
    EPS.EvnPS_NumCard,
    EPS.EvnPS_setDT,
    EPS.EvnPS_setDate,
    EPS.Diag_pid,
    EPS.DiagSetPhase_pid,
    EPS.person_id,
    EPS.EvnPS_id,
    EPS.PrehospArrive_id,
    EPS.Diag_id,
    EPS.PrehospWaifRefuseCause_id,
    dt
--  from 
--  (
--    select *
--    from dbo.v_EvnPS EPS
--    inner join mv on EPS.EvnPS_setDate <= dt
--      and (EPS.EvnPS_disDate > dt or EPS.EvnPS_disDate is null)
--      and EPS.EvnPS_setDate >='01.01.2020'::timestamp 
--  ) 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) Lp on true
--    
  from Lpu_W as Lp  
  inner join dbo.v_EvnPS EPS on EPS.Lpu_id = Lp.Lpu_id
    and EPS.EvnPS_setDate <= dt
    and (EPS.EvnPS_disDate > dt or EPS.EvnPS_disDate is null)
    and EPS.EvnPS_setDate >='01.01.2020'::timestamp 
),
--
EPS as 
(
  select 
    EPS.Lpu_Name as Lpu_Name,
    EPS.Lpu_OGRN,
    LS.LpuSection_FullName as LpuSection_Name,
    rtrim(PS.Person_SurName) as Person_SurName,
    rtrim(PS.Person_FirName) as Person_FirName,
    rtrim(PS.Person_SecName) as Person_SecName,
    PS.Person_BirthDay::date::timestamp without time zone as Person_BirthDay,
    PS.Sex_id,
	PS.Person_Phone,
	PS.SocStatus_id,
	PS.klcountry_id,
	coalesce(PS.PAddress_id, PS.UAddress_id) as Address_id,
	EPS.EvnPS_NumCard,
    EPS.EvnPS_setDT,
    EPS.EvnPS_setDate,
    EPS.Diag_pid,
    EPS.DiagSetPhase_pid,
    ROPriem.RepositoryObserv_SpO2 as RepositoryObservPriem_SpO2,
    ESLast.EvnSection_IsPriem,
    ESLast.EvnSection_id,
    case when ESLast.EvnSection_IsPriem = 1 then ESLast.EvnSection_setDT end as EvnSectionHosp_setDT, 
	ESLast.EvnSection_PlanDisDT, 
    case when ESLast.EvnSection_IsPriem = 1 then LS.LpuSectionProfile_Name end as LpuSectionHospProfile_Name,
    case when ESLast.EvnSection_IsPriem = 1 then ESLast.LpuSectionBedProfileLink_fedid end as LpuSectionBedProfileLink_fedid,
    case when ESLast.EvnSection_IsPriem = 1 then coalesce(ESLast.Diag_id, EPS.Diag_id, EPS.Diag_pid) else EPS.Diag_pid end as DiagHosp_id,
    case when ESLast.EvnSection_IsPriem = 1 then coalesce(RO.DiagSetPhase_id, ESLast.DiagSetPhase_id, EPS.DiagSetPhase_pid) else EPS.DiagSetPhase_pid end as DiagSetPhaseHosp_id,
    case when ESLast.EvnSection_IsPriem = 1 then RO.RepositoryObserv_SpO2 end as RepositoryObservHosp_SpO2,
	ROLast.RepositoryObserv_Temperature,
	ROLast.RepositoryObserv_BreathFrequency,
	ROLast.RepositoryObserv_Leukocytes,
	ROLast.RepositoryObserv_SRB,
    case when ESLast.EvnSection_IsPriem = 1 and coalesce(RO.RepositoryObserv_isIVL, 1) = 1 then RO.RepositoryObserv_isOxygen end as RepositoryObservHosp_isOxygen,
    case when ESLast.EvnSection_IsPriem = 1 then RO.RepositoryObserv_isIVL end as RepositoryObservHosp_isIVL,
    --case when ESLast.EvnSection_IsPriem = 1 then RO.DiagConfirmType_id else ROPriem.DiagConfirmType_id end as DiagConfirmType_id,
    coalesce(RO.DiagConfirmType_id,ROPriem.DiagConfirmType_id) as DiagConfirmType_id,
	EPS.PrehospArrive_id,
    ESPriem.EvnSection_insDT as EvnSectionPriem_insDT,
    RO.RepositoryObserv_setDT,
--    
    case when ESLast.EvnSection_IsPriem = 1
    then case when Attr.cntCOVID_KoikiAll > 0 then 'COVID-19'
              when Attr.cntPnevmo_KoikiAll > 0 then 'Пневмония'
	          when Attr.cntBerem_KoikiAll > 0 then 'Беременные COVID-19'
	          when Attr.cntRecover_KoikiAll >= 0 then 'Выздоравливающие'
         else 'Прочие' end 
    else 'Приемное отделение' end as LpuSectionHospType,
--  
    EPS.dt,
    EPS.Person_id
  from (select * from  EPS_raw where rnum=1) EPS
  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 
       ESLast.EvnSection_id,
       ESLast.LpuSection_id,
       ESLast.Diag_id,
       coalesce(ESLast.EvnSection_IsPriem, 1) as EvnSection_IsPriem,
       ESLast.EvnSection_setDT,
       ESLast.EvnSection_setDate,
       ESLast.EvnSection_disDate,
--       ES.EvnSection_disDT, 
       ESLast.DiagSetPhase_id,
       ESLast.LpuSectionBedProfileLink_fedid,
	   ESLast.EvnSection_PlanDisDT
    from dbo.v_EvnSection ESLast  
    where ESLast.EvnSection_pid = EPS.EvnPS_id
      and ESLast.EvnSection_setDate <= dt
    order by  
      coalesce(ESLast.EvnSection_IsPriem, 1),
      ESLast.EvnSection_setDT desc
      limit 1
  ) ESLast on TRUE
--   
  inner join lateral (select * from  dbo.v_LpuSection LS  where LS.LpuSection_id = ESLast.LpuSection_id
    and (ESLast.EvnSection_disDate > dt or ESLast.EvnSection_disDate is null) limit 1) LS on true
--   
  LEFT JOIN LATERAL
  (
    select 
      ESHosp.EvnSection_id
    from dbo.v_EvnSection ESHosp 
    where 
      ESHosp.EvnSection_pid = EPS.EvnPS_id
      and coalesce(ESHosp.EvnSection_IsPriem, 1) = 1
      limit 1
  ) ESHosp on TRUE
--  
  LEFT JOIN LATERAL
  (
    select  
      ESPriem.EvnSection_id,
      ESPriem.EvnSection_insDT
    from dbo.v_EvnSection ESPriem 
    where 
      ESPriem.EvnSection_pid = EPS.EvnPS_id
      and ESPriem.EvnSection_IsPriem = 2
      limit 1
  ) ESPriem on TRUE
--  
  LEFT JOIN LATERAL
  (
	select 
	  ROPriem.RepositoryObserv_SpO2,
	  ROPriem.DiagConfirmType_id
	from dbo.v_RepositoryObserv ROPriem 
	where
	  ROPriem.Evn_id = EPS.EvnPS_id
	ORDER BY ROPriem.RepositoryObserv_updDT desc
	limit 1
  ) ROPriem on TRUE
 -- 
  LEFT JOIN LATERAL 
  (
    select 
      SUM(case when AV.Attribute_id between 267 and 268
        then AV.AttributeValue_ValueInt end) as cntCOVID_KoikiAll,
--        
      SUM(case when AV.Attribute_id in (270,274)
        then AV.AttributeValue_ValueInt end) as cntPnevmo_KoikiAll,
--        
      SUM(case when AV.Attribute_id in (269,273)
        then AV.AttributeValue_ValueInt end) as cntBerem_KoikiAll,
--        
      SUM(case when AV.Attribute_id = 272
        then AV.AttributeValue_ValueInt end) as cntRecover_KoikiAll
 -- 
    from dbo.v_AttributeSignValue ASV  
    inner join dbo.v_AttributeValue AV on AV.AttributeSignValue_id = ASV.AttributeSignValue_id
      and ASV.AttributeSignValue_TablePKey = ESLast.LpuSection_id 
      and (ASV.AttributeSignValue_begDate < dateadd('day', 1, dt) or ASV.AttributeSignValue_begDate is null)
      and (ASV.AttributeSignValue_endDate >= dt or ASV.AttributeSignValue_endDate is null)
      and ASV.AttributeSign_id between 23 and 29 -- 23 Количество коек для пациентов с Covid-19, 24 Количество реанимационных коек для пациентов с Covid-19, 25 Количество коек для беременных пациенток с COVID-19, 26 Количество пульмонологических коек для пациентов с отрицательным COVID-19 (пневмония)
      and AV.Attribute_id between 267 and 274
  ) Attr on TRUE
--  
  LEFT JOIN LATERAL
  (
    select 
      ERP.EvnReanimatPeriod_id
    from dbo.v_EvnReanimatPeriod ERP  
    where 
      ERP.EvnReanimatPeriod_rid = EPS.EvnPS_id
      and ERP.EvnReanimatPeriod_setDate <= dt
      and (ERP.EvnReanimatPeriod_disDate > dt or ERP.EvnReanimatPeriod_disDate is null) 
      limit 1
  ) ERP on TRUE
--  
  LEFT JOIN LATERAL 
  (
    select  
      RO.RepositoryObserv_id,
      RO.RepositoryObserv_setDT,
      RepositoryObserv_isIVL,
      RepositoryObserv_isOxygen,
	  RO.DiagSetPhase_id,
	  RO.RepositoryObserv_SpO2,
	  RO.DiagConfirmType_id
    from dbo.v_RepositoryObserv RO  
    where 
      (ESLast.EvnSection_IsPriem = 1 and RO.Evn_id in (ESLast.EvnSection_id, ERP.EvnReanimatPeriod_id)
        or ESLast.EvnSection_IsPriem = 2 and RO.Evn_id = EPS.EvnPS_id)
      and RO.RepositoryObserv_setDT < dateadd('day', 1, dt)
    order by RO.RepositoryObserv_setDT desc  
    limit 1
  ) RO on TRUE
--
  LEFT JOIN LATERAL 
  (
    select 
      ROLast.RepositoryObserv_id,
      ROLast.RepositoryObserv_setDT, 
	  ROLast.RepositoryObserv_BreathFrequency,
	  coalesce(ROLast.RepositoryObserv_TemperatureTo, ROLast.RepositoryObserv_TemperatureFrom) as RepositoryObserv_Temperature,
	  ROLast.RepositoryObserv_Leukocytes,
	  ROLast.RepositoryObserv_SRB
    from dbo.v_RepositoryObserv ROLast  
	where 
	  ROLast.Evn_pid = EPS.EvnPS_id 
	  and ROLast.RepositoryObserv_setDT < dateadd('day', 1, dt)
    order by 
	  case when ROLast.Evn_id = EPS.EvnPS_id then 2 else 1 end,
	  case when ROLast.Evn_id = EPS.EvnPS_id then ROLast.RepositoryObserv_updDT else ROLast.RepositoryObserv_setDT end desc
	  limit 1
  ) ROLast on TRUE
--
  where 
   (ESLast.EvnSection_IsPriem = 1 or ESLast.EvnSection_IsPriem = 2 and (ESHosp.EvnSection_id > 0 or EPS.PrehospWaifRefuseCause_id is null))
)
--
select 
  to_char(getdate(), 'dd.MM.yyyy HH24:mi:ss') as "Время выгрузки",
  d.Lpu_Name as "Юрлицо",
  d.Lpu_Ogrn as "ОГРН",
  d.LpuSection_Name as "Отделение",
  d.Person_surname as "Фамилия",
  d.Person_firname as "Имя",
  d.Person_secname as "Отчество",
  to_char( d.Person_birthday, 'DD.MM.YYYY') as "Дата рождения",
  dbo.AgeYMD(d.Person_birthday, dt, 1)::integer as "Возраст",
  S.Sex_Name as "Пол",
  SocStatus.SocStatus_Name as "Социальный статус",
  kl.klcountry_nick "Гражданство",
  Person_Phone as "Телефон",
  AddressP.Address_Address "Адрес проживания",
  d.EvnPS_NumCard as "Номер КВС",
  to_char(d.EvnPS_setDT, 'DD.MM.YYYY') as "Дата госп. (поступл. в прием.)",
  d.EvnPS_setDT as "Дата госп. (поступл. в прием.) датой",
  DSPPriem.DiagSetPhase_Name as "Состояние при поступлении в прием",
  DPriem.Diag_Code as "Диагноз (основной) по МКБ-10 при пос",
  d.RepositoryObservPriem_SpO2 as "Сатурация при поступлении в прием",
  datediff('day', d.EvnPS_setDate, dt) as "Время пребывания в стационаре, в д",
  to_char( d.EvnSectionHosp_setDT, 'dd.mm.yyyy hh24:mi:ss')  as "Дата и время поступления в отделен",
  to_char( d.EvnSection_PlanDisDT, 'dd.mm.yyyy') as "Планируемая дата выписки",
  DSPHosp.DiagSetPhase_Name as "Состояние при поступлении в отдел",
  LpuSectionHospProfile_Name as "Профиль отделения",
  LSBP.LpuSectionBedProfile_Name as "Профиль койки",
  DHosp.Diag_Code as "Диагноз (основной) по МКБ-10",
  DiagSop_List as "Сопутствующие заболевания",
  DCT.DiagConfirmType_Name "Диагноз подтвержден рентгенологи",
  d.RepositoryObservHosp_SpO2 as "Сатурация при динамическом наблюд",
  d.RepositoryObserv_Temperature as "Температура при динамическом набл",
  d.RepositoryObserv_BreathFrequency as "ЧДД при динамическом наблюдении", 
  d.RepositoryObserv_Leukocytes as "Лейкоциты, х 109/л при динамическом", 
  d.RepositoryObserv_SRB as "СРБ при динамическом наблюдении", 
  case when RepositoryObservHosp_isIVL = 2 then 'Да' end::varchar(2) as "ИВЛ",
  case when RepositoryObservHosp_isOxygen = 2 then 'Да'::varchar(2) end as "Кислород",
--  
  pha.PrehospArrive_Name as "Самостоятельно поступил/По скорой",
--  
  RepositoryObserv_setDT::date::timestamp without time zone as "Дата последнего обновления КВС",
  datediff('day', RepositoryObserv_setDT, dt)::integer as "Количество дней от последнего обн",
--  
  datediff('minute', d.EvnPS_setDT, d.EvnSectionPriem_insdt)::integer "Задержка ввода сведений по приемн",
  LpuSectionHospType as "Тип отделения",
  d.person_id as "Person_id"
from EPS d
left join dbo.v_DiagSetPhase DSPPriem on DSPPriem.DiagSetPhase_id = d.DiagSetPhase_pid
left join dbo.v_DiagSetPhase DSPHosp on DSPHosp.DiagSetPhase_id = d.DiagSetPhaseHosp_id
left join dbo.v_Sex S on S.Sex_id = d.Sex_id
left join lateral( select * from dbo.v_Diag DPriem  where DPriem.Diag_id = d.Diag_pid limit 1) DPriem on true
left join lateral( select * from dbo.v_Diag DHosp  where DHosp.Diag_id = d.DiagHosp_id limit 1) DHosp on true
left join lateral( select * from  fed.LpuSectionBedProfileLink LSBPL  where LSBPL.LpuSectionBedProfileLink_id = d.LpuSectionBedProfileLink_fedid limit 1) LSBPL on true 
left join dbo.v_LpuSectionBedProfile LSBP on LSBP.LpuSectionBedProfile_id = LSBPL.LpuSectionBedProfile_id
left join dbo.v_DiagConfirmType DCT on DCT.DiagConfirmType_id = d.DiagConfirmType_id
left join v_SocStatus as SocStatus on SocStatus.SocStatus_id = d.SocStatus_id
left join v_klcountry as kl on kl.klcountry_id = d.klcountry_id
left join lateral( select * from v_Address as AddressP  where AddressP.Address_id = d.Address_id limit 1) AddressP on true
left join PrehospArrive as pha on d.PrehospArrive_id = pha.PrehospArrive_id 
 LEFT JOIN LATERAL  
(
  select string_agg(distinct  DSop.Diag_Code,'; ') as DiagSop_List
  from dbo.v_EvnDiagPS EDPS  
  inner join dbo.Diag DSop on DSop.Diag_id = EDPS.Diag_id
    and EDPS.EvnDiagPS_pid =d.EvnSection_id
    and EDPS.DiagSetClass_id <> 1 
) DiagSop on true
--
order by d.Lpu_Name, LpuSection_Name, d.Person_surname, d.Person_firname, d.Person_secname, d.Person_birthday
