with params as 
(
  select
    cast (null as bigint) as Lpu_id,
    ('2021-01-01')::timestamp as Date,
    (GETDATE()::date)::timestamp as Date2
),
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 
	and (l.Lpu_endDate > (select Date from params) or l.Lpu_endDate is null)
)
select
Lpu.lpu_name "Юридическое лицо",
Lpu.Lpu_Ogrn,
D.Diag_code "Диагноз по МКБ-10",
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 'Выздоравливающие'
       else 'Прочие' end as "Признак отделения",
eps.Person_id, lt.LeaveType_id
,case when date_part('days', EPS.EvnPS_disDT - EPS.EvnPS_setDT) < 1 then 1 else null end "24_h",
case when RO.RepositoryObserv_isIVL = 2 then 1 else null end "ivl",
date_part('year', age(EPS.EvnPS_setDate, PS.Person_BirthDay)) "age"
,cast(eps.EvnPS_setDate as date) "EvnPS_setDate"
,cast(eps.EvnPS_disDT as date) "EvnPS_disDT"   
from (select * from v_EvnPS as EPS 
			where (cast(EPS.EvnPS_disDT as date) between (select Date from params) and (select Date2 from params)) or 
					    eps.EvnPS_disDT is null) EPS 
inner join lateral (select * from Lpu_W as Lpu where Lpu.Lpu_id = EPS.lpu_id
					and (EPS.Lpu_id = (select Lpu_id from params) or (select Lpu_id from params) is null)
					limit 1) Lpu on true
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 dbo.v_LpuUnit LU on LU.LpuUnit_id = lpusection.LpuUnit_id
--and LU.LpuUnitType_SysNick in ('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)
--and ('Все' in  or coalesce(d.Diag_Code,'Всего') in )
left join v_LeaveType LT on LT.LeaveType_id = EPS.LeaveType_id
left join lateral
(
  select
    ERP.EvnReanimatPeriod_id
  from dbo.v_EvnReanimatPeriod ERP
  where 
    ERP.EvnReanimatPeriod_rid = EPS.EvnPS_id
    and ERP.EvnReanimatPeriod_setDate <= (select Date2 from params)
    and (ERP.EvnReanimatPeriod_disDate > (select Date2 from params) or ERP.EvnReanimatPeriod_disDate is null) 
    limit 1
) ERP on true
left join lateral 
  (
    select
      RepositoryObserv_isIVL
    from dbo.v_RepositoryObserv RO 
    where 
      RO.Evn_id in (ES.EvnSection_id, ERP.EvnReanimatPeriod_id)
      and RO.RepositoryObserv_setDT < dateadd('day', 1, (select Date2 from params))
      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
        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.AttributeSignValue_begDate < cast(EPS.EvnPS_disDate as date) or ASV.AttributeSignValue_begDate is null)
      and (ASV.AttributeSignValue_endDate >= cast(EPS.EvnPS_disDate as date) 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
