select distinct
	now() as "date_load",
	L.Lpu_Nick "url",
	l.Lpu_OGRN "ogrn",
	PS.Person_surname "surname",
	PS.Person_firname "namep",
	PS.Person_secname "secname",
	PS.Person_birthday "date_bd",
	EvnPS_NumCard "numkvs",
	S.Sex_Name as "sex",
	D.Diag_code  "diag",
	case when  extract(year from  age(EPS.EvnPS_setDate,PS.Person_BirthDay))=0 then 
	(extract(month from age(EPS.EvnPS_setDate,PS.Person_BirthDay)))::text||' мес'
	else 
	extract(year from  age(EPS.EvnPS_setDate,PS.Person_BirthDay))::text
	end "age",
	   case when ESLast.EvnSection_IsPriem = 1 then RO2.RepositoryObserv_SpO2 end as "satur",
	ROLast.RepositoryObserv_Temperature as "temp",
	ROLast.RepositoryObserv_BreathFrequency  as "chdd",
	case when RO.RepositoryObserv_isIVL = 2 then 'Да' end as "ivl",
	case when RO.RepositoryObserv_isOxygen = 2  and coalesce(RepositoryObserv_isIVL, 1) = 1 then 'Да' end as "air"
  from dbo.v_Lpu L  
  inner join dbo.v_EvnPS EPS  on EPS.Lpu_id = L.Lpu_id 
    and coalesce(L.Lpu_IsTest, 1) = 1
    --and L.Lpu_begDate < (CURRENT_DATE+1)::timestamp
    --and coalesce(L.Lpu_endDate, CURRENT_DATE::timestamp) >= CURRENT_DATE::timestamp 
    and EPS.EvnPS_setDate <= CURRENT_DATE::timestamp 
    and (EPS.EvnPS_disDate > CURRENT_DATE::timestamp or EPS.EvnPS_disDate is null) 
    and EPS.EvnPS_setDate >= '2020-01-01'::timestamp
  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 <= CURRENT_DATE::timestamp 
    and (ES.EvnSection_disDate > CURRENT_DATE::timestamp 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 <= CURRENT_DATE::timestamp
    and (ERP.EvnReanimatPeriod_disDate > CURRENT_DATE::timestamp or ERP.EvnReanimatPeriod_disDate is null) 
left join dbo.v_Sex S  on S.Sex_id = PS.Sex_id
--  
  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 < (CURRENT_DATE+1)::timestamp
    order by RO.RepositoryObserv_setDT desc 
    limit 1
  ) RO on true
--
  left join lateral
  (
    select 
	  DSop.Diag_Code
    from dbo.v_EvnDiagPS EDPS  
    inner join dbo.Diag DSop  on DSop.Diag_id = EDPS.Diag_id
      and EDPS.EvnDiagPS_pid = ES.EvnSection_id
      and EDPS.DiagSetClass_id <> 1 -- select * from DiagSetClass
	  and (left(DSop.Diag_Code, 3) in ('I21', 'I22', 'I23', 'I24','I26') or DSop.Diag_code = 'I20.0' or left(DSop.Diag_Code, 3) between 'I60' and 'I64')
	 order by EDPS.EvnDiagPS_id
	   limit 1
  ) DiagSop on true
--
 left join lateral
  (
    select 
      SUM(case when AV.Attribute_id between 267 and 268 and (ASV.AttributeSign_id in (23,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
--  
    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 < (CURRENT_DATE+1)::timestamp or ASV.AttributeSignValue_begDate is null)
      and (ASV.AttributeSignValue_endDate >= CURRENT_DATE::timestamp 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
  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 <= CURRENT_DATE::timestamp
    order by  
      coalesce(ESLast.EvnSection_IsPriem, 1),
      ESLast.EvnSection_setDT desc
        limit 1
  ) ESLast on true
    left join lateral-- строки профилей коек
(
  select  
    LSBPL.LpuSectionBedProfile_id,lsbs.LpuSection_id,SUM(LSBS.LpuSectionBedState_Fact) as BedState_Fact
  from v_LpuSectionBedState LSBS 
  inner join fed.LpuSectionBedProfileLink LSBPL  on LSBPL.LpuSectionBedProfileLink_id = LSBS.LpuSectionBedProfileLink_fedid
    and LSBS.LpuSection_id = LS.LpuSection_id
	and (LS.LpuSection_setDate <= (CURRENT_DATE+1)::timestamp or LS.LpuSection_setDate is null)
    and (LS.LpuSection_disDate >= (CURRENT_DATE-1)::timestamp or LS.LpuSection_disDate is null)  
    and cast(LpuSectionBedState_begDate as date) <=  (CURRENT_DATE+1)::timestamp
    and (cast(LpuSectionBedState_endDate as date) >=(CURRENT_DATE-1)::timestamp or LpuSectionBedState_endDate is null)  
	and l.Lpu_begDate <= (CURRENT_DATE-1)::timestamp
--	and coalesce(l.Lpu_endDate, l.LS_endDate) >= l.LS_begDate
	--and  LSBPL.LpuSectionBedProfile_id in ('3069','3079','3174')
	group by  LSBPL.LpuSectionBedProfile_id,lsbs.LpuSection_id
) StrProf on true
  left join lateral
  (
    select  
      RO2.RepositoryObserv_id,
      RO2.RepositoryObserv_setDT,
	  RO2.DiagSetPhase_id,
	  RO2.RepositoryObserv_SpO2,
	  RO2.DiagConfirmType_id
    from dbo.v_RepositoryObserv RO2  
    where 
      (ESLast.EvnSection_IsPriem = 1 and RO2.Evn_id in (ESLast.EvnSection_id, ERP.EvnReanimatPeriod_id)
        or ESLast.EvnSection_IsPriem = 2 and RO2.Evn_id = EPS.EvnPS_id)
      and RO2.RepositoryObserv_setDT < (CURRENT_DATE+1)::timestamp
    order by RO2.RepositoryObserv_setDT desc 
      limit 1
  ) RO2 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 < (CURRENT_DATE+1)::timestamp
    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
left join fed.LpuSectionBedProfileLink LSBPL  on LSBPL.LpuSectionBedProfileLink_id = ESLast.LpuSectionBedProfileLink_fedid
left join dbo.v_LpuSectionBedProfile LSBP  on LSBP.LpuSectionBedProfile_id = LSBPL.LpuSectionBedProfile_id 
 where 
    (cntCOVID_KoikiAll >= 0 or cntPnevmo_KoikiAll >= 0)  and LSBPL.LpuSectionBedProfile_id  in (3174, 3079, 3069) --or cntBerem_KoikiAll > 0
   -- and EvnPS_NumCard ='2021/13275'
  -- and l.Lpu_OGRN='1025001278158'
 --   limit 10
/*group by 
  L.Lpu_Nick,
	l.Lpu_OGRN,
    coalesce(LB.Address_id, L.PAddress_id, L.UAddress_id),
	PS.Person_surname ,S.Sex_Name,case when RO.RepositoryObserv_isIVL = 2 then 'Да' end,
	case when RO.RepositoryObserv_isOxygen = 2  and coalesce(RepositoryObserv_isIVL, 1) = 1 then 'Да' end ,
	PS.Person_firname ,
	PS.Person_secname ,D.Diag_code,
    case when ESLast.EvnSection_IsPriem = 1 then RO2.RepositoryObserv_SpO2 end ,
	ROLast.RepositoryObserv_Temperature,
	ROLast.RepositoryObserv_BreathFrequency,
	EvnPS_NumCard,
	PS.Person_birthday ,
case when  extract(year from  age(EPS.EvnPS_setDate,PS.Person_BirthDay))=0 then 
	(extract(month from age(EPS.EvnPS_setDate,PS.Person_BirthDay)))::text||' мес'
	else 
	extract(year from  age(EPS.EvnPS_setDate,PS.Person_BirthDay))::text
	end
*/
