with 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 
),
params as (
select        
       --cast (null as bigint) as Lpu_id,
       cast ('01.01.2020' as timestamp) as begdate,
       cast ('01.03.2022' as timestamp) as enddate  
)
select
  Lpu.lpu_name Юридическое_лицо,
  Lpu.Lpu_Ogrn ОГРН,
  EPS.EvnPS_Numcard as Номер_КВС,
  lpusection.lpusection_name Отделение,
  lpusection.LpuSectionProfile_Name Профиль_отделения,
  coalesce( (select LpuSectionBedProfile_Name 
  from fed.LpuSectionBedProfileLink LSBPL
  inner join V_LpuSectionBedProfile lsbp on lsbp.LpuSectionBedProfile_id = LSBPL.LpuSectionBedProfile_id
  where LSBPL.LpuSectionBedProfileLink_id = es.LpuSectionBedProfileLink_fedid ), 'Не указан') Профиль_койки,
     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 'Койки для выздоравливающих' end
     as Признак_отделения,
  PS.Person_surname Фамилия,
  PS.Person_firname Имя,
  PS.Person_secname Отчество,
  to_char(PS.Person_birthday, 'yyyy-MM-dd') Дата_рождения,
  D.Diag_code Диагноз_по_МКБ10,
  EPS.EvnPS_setDT Дата_и_время_поступления
from Lpu_W as Lpu 
inner join v_EvnPS as EPS on Lpu.Lpu_id =EPS.lpu_id
inner join v_EvnSection as ES on ES.EvnSection_pid = EPS.EvnPS_id
  and ES.EvnSection_Count-1 = ES.EvnSection_index  
  and EPS.PrehospWaifRefuseCause_id is null
inner join v_lpusection as lpusection on lpusection.lpusection_id =ES.lpusection_id
inner join v_LpuUnit LU on LU.LpuUnit_id = lpusection.LpuUnit_id
and LU.LpuUnitType_SysNick in ('stac')
 -- and (lpusection.LpuUnitType_SysNick = 'stac')
inner join v_PersonState as PS on EPS.Person_id = PS.Person_id
inner join v_Diag as D on D.Diag_id = coalesce(ES.Diag_id,EPS.Diag_pid)
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 = ES.LpuSection_id 
      and ASV.AttributeSign_id in (23, 27)-- 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_setDate >= (select begdate from params) and (Attr.cntCOVID_KoikiAll > 0 or Attr.cntPnevmo_KoikiAll > 0 or Attr.cntBerem_KoikiAll > 0 or Attr.cntRecover_KoikiAll >= 0)
and EPS.evnps_disdate is null
