--http://bi.mz.mosreg.ru/#edit/etl/236
with prm as (
select GETDATE()::timestamp as Date,
       GETDATE()::timestamp as DT)
select L.Lpu_id/*,
         -- COUNT(distinct  EPS.Person_id ) as cnt_PersC,
         COUNT(distinct (case
                           when ERP.EvnReanimatPeriod_rid is null then EPS.Person_id
                         end)) as cnt_PersC,
         COUNT(distinct (case
                           when ERP.EvnReanimatPeriod_rid is not null then EPS.Person_id
                         end)) as cnt_PersCIVL,
         0 as cnt_PersNIVL,
         0 as cnt_PersN,
         --Бронь
         dir.cntReservCar,
         dir.cntReservCarIVL,
         0 as cntReservNevr,
         0 as cntReservNevrIVL*/
  from (select * from dbo.v_EvnPS EPS 
  where EPS.EvnPS_setdate::date >= '01.01.2020' and EPS.EvnPS_setDate <= (select Date from prm)
  and (EPS.EvnPS_disDate > (select Date from prm) or EPS.EvnPS_disDate is null)) EPS
  inner join lateral (select * from dbo.v_Lpu L where EPS.Lpu_id = L.Lpu_id and COALESCE(L.Lpu_IsTest, 1) = 1 -- and L.Lpu_id = 13100875
  and L.Lpu_begDate < dateadd('day', 1, (select Date from prm)) and COALESCE(L.Lpu_endDate, (select Date from prm)) >= (select Date from prm) limit 1) L on true
  inner join lateral (select * from dbo.v_EvnSection ES where ES.EvnSection_pid = EPS.EvnPS_id 
                      and ES.EvnSection_setDate <= (select Date from prm)
                      and (ES.EvnSection_disDate > (select Date from prm)
                      or ES.EvnSection_disDate is null) and COALESCE(ES.EvnSection_IsPriem, 1) = 1) ES on true
       left join v_Diag as Diag on Diag.Diag_id = coalesce(ES.Diag_id, EPS.Diag_id, EPS.Diag_pid) -- ES.Diag_id
       left join fed.v_LpuSectionBedProfileLink LSBPL on LSBPL.LpuSectionBedProfileLink_id = ES.LpuSectionBedProfileLink_fedid
       left join dbo.v_LpuSectionBedProfile LSBP on LSBP.LpuSectionBedProfile_id = LSBPL.LpuSectionBedProfile_id
       left join dbo.v_EvnReanimatPeriod ERP on ERP.EvnReanimatPeriod_rid = EPS.EvnPS_id -- ERP.EvnReanimatPeriod_pid = ES.EvnSection_pid
 and ERP.EvnReanimatPeriod_setDate <= (select Date from prm)
 and (ERP.EvnReanimatPeriod_disDate > (select Date from prm)
 or ERP.EvnReanimatPeriod_disDate is null)
       ---тут
       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
         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
       inner join dbo.v_LpuSection LS on LS.LpuSection_id = ESLast.LpuSection_id and (ESLast.EvnSection_disDate > (select Date from prm)
 or ESLast.EvnSection_disDate is null) and ls.lpusectionprofile_id in (20000317, --кардиол
       20000297, --анестезиологии и реаниматологии
       20000430 -- реаниматологии
       )
       LEFT JOIN LATERAL
       (
         select RO.RepositoryObserv_id,
                RepositoryObserv_isIVL,
                RepositoryObserv_isOxygen,
                case
                  when RO.Evn_id = ERP.EvnReanimatPeriod_id then ERP.LpuSection_id
                  else ES.LpuSection_id
                end as LpuSection_id
         from dbo.v_RepositoryObserv RO
         where RO.Evn_id in (ES.EvnSection_id, ERP.EvnReanimatPeriod_id) and
               RO.RepositoryObserv_setDT < dateadd('day', 1, (select Date from prm))
         order by RO.RepositoryObserv_setDT desc
         limit 1
       ) RO on true
       LEFT JOIN LATERAL
       (
         select era.EvnReanimatAction_id
         from v_EvnReanimatAction ERA
         where ERA.EvnReanimatAction_pid = ERP.EvnReanimatPeriod_id and
               era.EvnReanimatAction_setDt < dateadd('day', 1, (select Date from prm)) and
               era.ReanimatActionType_id = 1
         order by era.EvnReanimatAction_setDT desc
         limit 1
       ) era on true
       left join
       (
		select L.Lpu_id,
				null as Address_id,
                count(distinct
                			   case when 
                               left (ED.EvnDirection_Num, 3) = 'CN_' or left (ED.EvnDirection_Num, 3) = 'CI_' 
                               then ED.EvnDirection_id
                               end) 
                               as cntReservCar,
                COUNT(distinct case
                               when left (ED.EvnDirection_Num, 3) = 'CI_' then ED.EvnDirection_id
                               end) as cntReservCarIVL
         from (select * from dbo.v_EvnDirection_all ED where 
         ED.EvnDirection_insDT between dateadd('hour', - 4, (select DT from prm)) and (select DT from prm)
         and ED.DirType_id = 5 -- 5 На госпитализацию экстренную
         and (ED.EvnDirection_failDT > (select DT from prm)
         or ED.EvnDirection_failDT is null) and
         left (ED.EvnDirection_Num, 3) in ('CI_', 'CN_')) ED 
         inner join lateral (select * from dbo.v_Lpu L where L.Lpu_id = COALESCE(ED.Lpu_did, ED.Lpu_id) --select * from DirType
 		 and COALESCE(L.Lpu_IsTest, 1) = 1 and L.Lpu_begDate < dateadd('day', 1, (select Date from prm)) 
         and COALESCE(L.Lpu_endDate, dateadd('day', - 1, (select Date from prm))) >= dateadd('day', - 1, (select Date from prm))) L on true
         left join lateral (select * from dbo.v_EvnPS EPS where EPS.EvnDirection_id = ED.EvnDirection_id 
         and EPS.EvnPS_setDT between dateadd('hour', - 4, (select DT from prm)) and (select DT from prm)) EPS on true
         LEFT JOIN LATERAL
              (
                select ES.EvnSection_id,
                       COALESCE(ES.EvnSection_IsPriem, 1) as EvnSection_IsPriem
                from dbo.v_EvnSection ES
                where ES.EvnSection_pid = EPS.EvnPS_id and
                      ES.EvnSection_setDT between dateadd('hour', - 4, (select DT from prm)) and
                      (select DT from prm)
                order by COALESCE(ES.EvnSection_IsPriem, 1),
                         ES.EvnSection_setDT desc
                limit 1
              ) ES on true
         WHERE EPS.EvnPS_id is null or
               ES.EvnSection_IsPriem = 2 and
               EPS.PrehospWaifRefuseCause_id is null
         group by L.Lpu_id
       ) dir on dir.Lpu_id = L.Lpu_id
  		where (left (Diag.Diag_code, 3) in ('I21', 'I22', 'I23', 'I24', 'I26', 'I44', 'I47', 'I48', 'I50', 'I45') or
        Diag.Diag_code = 'I20.0') and
        ES.lpusection_id in (
                              select lpusection_id
                              from v_lpusection ls
                                   join
                                   (
                                     select lpu_id,
                                            lpu_nick
                                     from v_lpu
                                     where lpu_ogrn in ('1035007200733', '1195081091743', '1035004250038', '1195027016480', '1025001624152', '1035005503356', '1025001466445', '1035004465990',
                                           '1035005503356',
                                           '1205000033358',                                           '1025001278158',                                           '1035008256140',                                           '1175022004816',                                           '1027700442494',                                           '1025005599464',                                           '1025007389770',
                                           --'1025004914967', --убрала Розанова 04.06.2021 по кардиологии
                                           --'1025000845539', --убрала волоколамскую 04.06.2021 по кардиологии
                                           '1025002590458',                                           '1205000065764',
                                           --'1065028003578', --убрала можайскую 04.06.2021 по кардиологии
                                           --'1205000012381', --убрала рузскую 04.06.2021 по кардиологии
                                           --'1035003374262', --убрала королевскую 04.06.2021 по кардиологии
                                           '1035000701592', --убрала балашихинскую 04.06.2021 по кардиологии
                                           --'1035007906053', --убрала раменскую 04.06.2021 по кардиологии
                                           --'1035006103538', --убрала ногинскую 04.06.2021 по кардиологии
                                           '1205000034799'                                           --добавили ступинскую
                                           )
                                   ) l on ls.lpu_id = l.lpu_id
                                   join v_lpuunit lu on ls.lpuunit_id = lu.lpuunit_id
                              where LpuUnitType_SysNick = 'stac'
        ) and es.lpusectionprofile_id
 in (
20000317, --кардиол
 20000297, --анестезиологии и реаниматологии
20000430    -- реаниматологии
)
        --and (es.lpusectionbedprofile_id in (
        --3083, --кардиологические для больных с острым инфарктом миокарда
        --3107, --койки интенсивной терапии
        --3082, --кардиологические интенсивной терапии
        --3179 --интенсивной терапии
        --) or es.lpusectionbedprofile_id is null
        --)
        and
        lsbp.LpuSectionBedProfile_id in (3083, --кардиологические для больных с острым инфарктом миокарда
        3107, --койки интенсивной терапии
        3082, --кардиологические интенсивной терапии
        3179 --интенсивной терапии)
        )
  group by L.Lpu_id,
           dir.cntReservCar,
           dir.cntReservCarIVL
