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

with prm as (
select GETDATE()::date::timestamp as Date,
       GETDATE()::timestamp as DT),
Koiki as
(
  select l.Lpu_Nick,
         l.Lpu_OGRN, --ls.LpuSection_Name,
         --
         SUM(cntOborudIVL_COVID) as cntKoikiCOVID_IVL,
         SUM(cntOborudOxy_All2) as cntKoikiCOVID_Oxy2
         --
  from dbo.v_LpuBuilding LB  
       inner join dbo.v_Lpu L on LB.Lpu_id = L.Lpu_id and (L.Lpu_IsTest=1 or L.Lpu_IsTest is null) -- 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)
        inner join dbo.v_LpuUnit LU on LU.LpuBuilding_id = LB.LpuBuilding_id and (LU.LpuUnit_begDate < dateadd('day', 1, (select Date from prm)) or LU.LpuUnit_begDate is null) and (LU.LpuUnit_endDate >= (select Date from prm)
 or LU.LpuUnit_endDate is null)
       inner join dbo.v_LpuSection LS on LS.LpuUnit_id = LU.LpuUnit_id and (LS.LpuSection_setDate < dateadd('day', 1, (select Date from prm)) or LS.LpuSection_setDate is null) and (LS.LpuSection_disDate >= (select Date from prm)
 or LS.LpuSection_disDate is null)
       --
       LEFT JOIN LATERAL
       (
         select SUM(case
                      when AV.Attribute_id between 267 and 268 and ASV.AttributeSign_id between 23 and 24 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 and ASV.AttributeSign_id in (27) 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 = LS.LpuSection_id and (ASV.AttributeSignValue_begDate < dateadd('day', 1, (select Date from prm)) or ASV.AttributeSignValue_begDate is null) and (ASV.AttributeSignValue_endDate >= (select Date from prm)
 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 SUM(case
                      when MedProductType_Code in (224, 1812, 874, 1918, 2101, 216, 290, 247, 156, 157, 211, 210, 212, 1395, 1149) then LSMPTL.LpuSectionMedProductTypeLink_IncludePatientKVI
                    end) as cntOborudOxy_COVID,
                SUM(case
                      when MedProductType_Code in (219, 220, 221, 222, 223) then LSMPTL.LpuSectionMedProductTypeLink_TotalAmount
                    end) as cntOborudIVL_COVID,
                SUM(case
                      when MedProductType_Code in (224, 1812, 874, 1918, 2101, 216, 290, 247, 156, 157, 211, 210, 212, 1395, 1149) then LSMPTL.LpuSectionMedProductTypeLink_TotalAmount
                    end) as cntOborudOxy_All,
                SUM(case
                      when MedProductType_Code in (224, 1812, 874, 1918, 2101, 216, 290, 247, 156, 157, 211, 210, 212, 1395, 1149, 219, 220, 221, 222, 223) then LSMPTL.LpuSectionMedProductTypeLink_TotalAmount
                    end) as cntOborudOxy_All2,
                SUM(case
                      when MedProductType_Code in (219, 220, 221, 222, 223) then LSMPTL.LpuSectionMedProductTypeLink_IncludeReanimation
                    end) as cntOborudIVL_All
         from dbo.v_LpuSectionMedProductTypeLink LSMPTL
              inner join passport.v_MedProductType MPT on MPT.MedProductType_id = LSMPTL.MedProductType_id and LSMPTL.LpuSection_id = LS.LpuSection_id and (LSMPTL.LpuSectionMedProductTypeLink_begDT < dateadd('day', 1, (select Date from prm)) or LSMPTL.LpuSectionMedProductTypeLink_begDT is null) and (LSMPTL.LpuSectionMedProductTypeLink_endDT >= (select Date from prm)
 or LSMPTL.LpuSectionMedProductTypeLink_endDT is null)
       ) MedProd on true
      where (LB.LpuBuilding_begDate < dateadd('day', 1, (select Date from prm)) or LB.LpuBuilding_begDate is null) 
      	and (LB.LpuBuilding_endDate >= (select Date from prm)  or LB.LpuBuilding_endDate is null)
       --
  group by l.Lpu_Nick,
           l.Lpu_OGRN --, ls.LpuSection_Name
),
--
Pers as
(
  select l.Lpu_Nick,
         l.Lpu_OGRN,
         --
         COUNT(distinct case
                          when RepositoryObserv_isIVL = 2 then EPS.Person_id
                        end) as cntPersCOVID_IVL,
         --
         COUNT(distinct case
                          when ((RepositoryObserv_isOxygen = 2 and COALESCE(RepositoryObserv_isIVL, 1) = 1) or RepositoryObserv_isIVL = 2) then EPS.Person_id
                        end) as cntPersCOVID_Oxy
  from dbo.v_EvnPS EPS 
       inner join dbo.v_Lpu L on EPS.Lpu_id = L.Lpu_id and (L.Lpu_IsTest=1 or L.Lpu_IsTest is null) 
       --and L.Lpu_begDate < dateadd('day', 1, null/*p_registry_evnps_exponkousl_2018.Date*/)
       --and COALESCE(L.Lpu_endDate, null/*p_registry_evnps_exponkousl_2018.Date*/) >= null/*p_registry_evnps_exponkousl_2018.Date*/
       inner join dbo.v_PersonState PS on PS.Person_id = EPS.Person_id
       inner join dbo.v_EvnSection ES on 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
       inner join  dbo.v_LpuSection LS on LS.LpuSection_id = ES.LpuSection_id	
       left join dbo.v_Diag D on D.Diag_id = coalesce(ES.Diag_id, EPS.Diag_id, EPS.Diag_pid)
       inner join dbo.v_LpuBuilding LB on LB.LpuBuilding_id = LS.LpuBuilding_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)
       --
       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 SUM(case
                      when AV.Attribute_id between 267 and 268 and ASV.AttributeSign_id between 23 and 24 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 and ASV.AttributeSign_id in (27) 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 = LS.LpuSection_id and (ASV.AttributeSignValue_begDate < dateadd('day', 1, (select Date from prm)) or ASV.AttributeSignValue_begDate is null) and (ASV.AttributeSignValue_endDate >= (select Date from prm)
 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
       --
  where   EPS.EvnPS_setDt <= (select Date from prm) and EPS.EvnPS_setDt >= '01.01.2020'::date
	 and (EPS.EvnPS_disDate > (select Date from prm) or EPS.EvnPS_disDate is null) 
  	and ((RepositoryObserv_isOxygen = 2 and
        COALESCE(RepositoryObserv_isIVL, 1) = 1) or
        RepositoryObserv_isIVL = 2)
  group by l.Lpu_Nick,
           l.Lpu_OGRN)
select coalesce(koiki.Lpu_OGRN, pers.Lpu_OGRN) as "OGRN",
       coalesce(koiki.Lpu_Nick, pers.Lpu_Nick) as Lpu_Nick,
       coalesce(koiki.cntKoikiCOVID_Oxy2, 0) "Точек подвода кислорода",
       coalesce(pers.cntPersCOVID_Oxy, 0) "Точек подвода кислорода занято",
       coalesce(koiki.cntKoikiCOVID_Oxy2, 0) - coalesce(pers.cntPersCOVID_Oxy, 0) "Точек подвода кислорода свободно",
       coalesce(koiki.cntKoikiCOVID_IVL, 0) "Точек подвода ИВЛ",
       coalesce(pers.cntPersCOVID_IVL, 0) "Точек подвода ИВЛ занято",
       coalesce(koiki.cntKoikiCOVID_IVL, 0) - coalesce(pers.cntPersCOVID_IVL, 0) "Точек подвода ИВЛ свободно"
from Koiki
     full join pers on koiki.Lpu_OGRN = pers.Lpu_OGRN
where (coalesce(koiki.cntKoikiCOVID_Oxy2, 0) + coalesce(pers.cntPersCOVID_Oxy, 0)) > 0

