--http://bi.mz.mosreg.ru/#edit/etl/582

with prm as (
select GETDATE()::date::timestamp as Date),
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 *
from (
       select Lpu.lpu_name "Юридическое лицо",
              Lpu.Lpu_Ogrn "ОГРН",
              EPS.EvnPS_Numcard as "Номер КВС",
              lpusection.LpuSection_Name,
              es.EvnSection_setDate,
              es.EvnSection_disDate,
              coalesce(PS.Person_surname, '') || ' ' || left (coalesce(PS.Person_firname, ''), 1) || '. ' || left (coalesce(PS.Person_secname, ''), 1) || '.' "ФИО",
              case
                when ESLast.EvnSection_IsPriem = 1 then ESLast.EvnSection_setDT
              end as EvnSectionHosp_setDT,
              PS.Person_surname,
              PS.Person_firname,
              PS.Person_secname,
              PS.Person_birthday "Дата рождения",
              adr.Address_Nick as "Адрес регистрации",
              adrf.Address_Nick as "Адрес фактический",
              COALESCE(fc.FamilyContact_FIO, ' ') as FamilyContact_FIO,
              COALESCE(fc.FamilyContact_Phone, ' ') as FamilyContact_Phone,
              --  D.Diag_code [Диагноз по МКБ-10",--
              EPS.EvnPS_setDT "Дата и время поступления",
              --  to_char(EPS.EvnPS_disDT, 'DD.MM.YYYY') + ' ' + convert(varchar(5), EPS.EvnPS_disDT, 114) [Дата выписки",
              case
                when fc.FamilyContact_IsInfoAgree = 1 then 'Нет'
                when fc.FamilyContact_IsInfoAgree is null then 'Не заполнено'
                when fc.FamilyContact_IsInfoAgree = 2 then 'Да'
                else null
              end "Согласен на информирование родственника",
              EPS.EvnPS_insDT,
              case
                when fc.FamilyContact_IsInfoAgree is null or ((fc.FamilyContact_IsInfoAgree != 1 and fc.FamilyContact_Phone is null) or (fc.FamilyContact_Phone = '' and fc.FamilyContact_IsInfoAgree != 1)) then 1
                else 0
              end "Признак для окрашивания",
              ROW_NUMBER() over(partition by EPS.EvnPS_id
       order by EPS.EvnPS_insDT desc, EPS.EvnPS_id) rnum
       from Lpu_W as Lpu
            join v_EvnPS as EPS on Lpu.Lpu_id = EPS.lpu_id
            join v_EvnSection as ES on ES.EvnSection_pid = EPS.EvnPS_id and EPS.EvnPS_setDate <= (select Date from prm)
 and (EPS.EvnPS_disDate > (select Date from prm)
 or EPS.EvnPS_disDate is null) and (es.EvnSection_disDate > (select Date from prm)
 or es.EvnSection_disDate is null) and EPS.EvnPS_setDate >= '01.01.2020'::date
            join v_lpusection as lpusection on lpusection.lpusection_id = ES.lpusection_id
            join v_PersonState as PS on EPS.Person_id = PS.Person_id
            join address as adr on ps.uaddress_id = adr.address_id --адрес регистрации
            join address as adrf on ps.PAddress_id = adrf.address_id --адрес фактич
            --join v_Diag as D  on D.Diag_id = COALESCE(ES.Diag_id,EPS.Diag_pid)
            left join FamilyContact as fc on fc.EvnPS_id = EPS.EvnPS_id
            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 <= (select Date from prm)
              order by COALESCE(ESLast.EvnSection_IsPriem, 1),
                       ESLast.EvnSection_setDT desc
              limit 1
            ) ESLast 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
       where EPS.LeaveType_id is null and
             (ESLast.EvnSection_IsPriem = 1 or
             ESLast.EvnSection_IsPriem = 2 and
             (ESHosp.EvnSection_id > 0 or
             EPS.PrehospWaifRefuseCause_id is null))
             -- and coalesce(PS.Person_surname,'')+(coalesce(PS.Person_firname,'')) +(coalesce(PS.Person_secname,''))='ГусевВладимирАлександрович'
             --and year(eps.EvnPS_setDT)=2021
     ) k
where rnum = 1

