--https://jira.is-mis.ru/browse/PROMEDWEB-5796 (PG)
--  Летальность в стационарах
-- Указывается период без указания часов и минут.
with params as (
select
   cast(CAST('2021-06-13 00:00'  AS date) as timestamp) as BegDate,
   cast(CAST('2022-03-05 02:10'  AS date) as timestamp) as EndDate),
EPS as
(
  select
    ES.LpuSection_id,
    LSBPL.LpuSectionBedProfile_id,
    COALESCE(ES.Diag_id, EPS.Diag_pid) as Diag_id,
    COALESCE(RepositoryObserv_isIVL, 1) as isIVL,
    COALESCE(RepositoryObserv_isOxygen, 1) as isOxygen,

    COUNT(distinct case when LT.LeaveType_SysNick iLIKE '%die%'
      then ES.EvnSection_id end) as cntDie,

    COUNT(distinct case when COALESCE(LT.LeaveType_SysNick, '') not iLIKE '%die%'
      then ES.EvnSection_id end) as cntLeave

  from 
  (
    select *
    from dbo.v_EvnPS EPS 
    where 
      EPS.EvnPS_disDate between (select BegDate from params) and (select EndDate from params)
  ) 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_begDate < dateadd('day', 1, (select EndDate from params))
      and COALESCE(L.Lpu_endDate, (select BegDate from params)) >= (select BegDate from params)
    limit 1
  ) L on true 
  
    --and EPS.PrehospWaifRefuseCause_id is null
  inner join lateral (select * from dbo.v_PersonState PS where PS.Person_id = EPS.Person_id limit 1) PS on true
  --inner join dbo.v_EvnSection ES on ES.EvnSection_pid = EPS.EvnPS_id
  --  and ES.EvnSection_Index = ES.EvnSection_Count - 1
  --  and COALESCE(ES.EvnSection_IsPriem, 1) = 1
  INNER JOIN LATERAL
  (
    select
    ES.EvnSection_id, ES.EvnSection_setDate, ES.EvnSection_disDate, ES.LpuSectionBedProfileLink_fedid, ES.LeaveType_id, ES.LpuSection_id, ES.Diag_id
  from dbo.v_EvnSection ES
  where
    ES.EvnSection_pid = EPS.EvnPS_id
    and COALESCE(ES.EvnSection_IsPriem, 1) = 1
    order by ES.EvnSection_disDT desc
    limit 1
  ) ES on true
  inner join dbo.v_LeaveType LT on LT.LeaveType_id = ES.LeaveType_id
  left join lateral (select * from fed.LpuSectionBedProfileLink LSBPL where LSBPL.LpuSectionBedProfileLink_id = ES.LpuSectionBedProfileLink_fedid limit 1) LSBPL on true

  LEFT JOIN LATERAL
  (
    select
      RO.RepositoryObserv_id,
      RepositoryObserv_isIVL,
      RepositoryObserv_isOxygen
    from dbo.v_RepositoryObserv RO
    where
      RO.Evn_id in (ES.EvnSection_id)
      and RO.RepositoryObserv_setDT < dateadd('day', 1, (select EndDate from params))
    order by RO.RepositoryObserv_setDT desc
    limit 1
  ) RO on true

  group by
    ES.LpuSection_id,
    LSBPL.LpuSectionBedProfile_id,
    COALESCE(ES.Diag_id, EPS.Diag_pid),
    COALESCE(RepositoryObserv_isIVL, 1),
    COALESCE(RepositoryObserv_isOxygen, 1)
),
localMO as (
select 'Хавен' lpu_nick, '1027700442494' OGRN union all
select 'МЕДСИ' lpu_nick, '5067746338732' OGRN union all
select 'Воскресенская районная больница №2' lpu_nick, '1075005002160' OGRN union all
select 'Дрезненская городская больница' lpu_nick, '1025007461434' OGRN union all
select 'Дубненская городская больница' lpu_nick, '1075010004673' OGRN union all
select 'Клинический центр восстановительной медицины и реабилитации' lpu_nick, '1037739256906' OGRN union all
select 'Клинская городская больница' lpu_nick, '1025002590458' OGRN union all
select 'Клинский кожно-венерологический диспансер' lpu_nick, '1035003950079' OGRN union all
select 'Королевский кожно-венерологический диспансер' lpu_nick, '1035003354121' OGRN union all
select 'Московский областной хоспис (для детей)' lpu_nick, '1185027014908' OGRN union all
select 'Одинцовский кожно-венерологический диспансер' lpu_nick, '1035006474580' OGRN union all
select 'Павлово-Посадский кожно-венерологический диспансер' lpu_nick, '1055007307728' OGRN union all
select 'Психиатрическая больница №22' lpu_nick, '1025006175370' OGRN union all
select 'Сергиево-Посадский кожно-венерологический диспансер' lpu_nick, '1035008354226' OGRN union all
select 'Химкинская областная больница' lpu_nick, '1025006174852' OGRN union all
select 'Центральная городская больница имени М.В. Гольца' lpu_nick, '1025007067854' OGRN union all
select 'Центральная городская клиническая больница г. Реутов' lpu_nick, '1035008256140' OGRN union all
select 'Щелковский кожно-венерологический диспансер' lpu_nick, '1035010211532' OGRN union all
select 'Авсюнинская участковая больница' lpu_nick, '1035011300631' OGRN union all
select 'Балашихинская областная больница' lpu_nick, '1035000701592' OGRN union all
select 'Балашихинский родильный дом' lpu_nick, '1165012052700' OGRN union all
select 'Бронницкая городская больница' lpu_nick, '1025005121305' OGRN union all
select 'Верейская участковая больница' lpu_nick, '1025007460752' OGRN union all
select 'Видновская районная клиническая больница' lpu_nick, '1025000657857' OGRN union all
select 'Видновский перинатальный центр' lpu_nick, '1025000661696' OGRN union all
select 'Волоколамская центральная районная больница' lpu_nick, '1025000845539' OGRN union all
select 'Воскресенская первая районная больница' lpu_nick, '1175022004816' OGRN union all
select 'Давыдовская районная больница' lpu_nick, '1025007461962' OGRN union all
select 'Дедовская городская больница' lpu_nick, '1035003056208' OGRN union all
select 'Демиховская участковая больница' lpu_nick, '1035011301038' OGRN union all
select 'Детский клинический многопрофильный центр Московской области' lpu_nick, '1037700069956' OGRN union all
select 'Дзержинская городская больница' lpu_nick, '1025007270750' OGRN union all
select 'Дмитровская городская больница' lpu_nick, '1025001096361' OGRN union all
select 'Долгопрудненская центральная городская больница' lpu_nick, '1025001201170' OGRN union all
select 'Домодедовская центральная городская больница' lpu_nick, '1025001278158' OGRN union all
select 'Егорьевская центральная районная больница' lpu_nick, '1025001466445' OGRN union all
select 'Егорьевский кожно-венерологический диспансер' lpu_nick, '1035002358478' OGRN union all
select 'Жуковская городская клиническая больница' lpu_nick, '1025001624152' OGRN union all
select 'Зарайская центральная районная больница' lpu_nick, '1025001718059' OGRN union all
select 'Ивантеевская центральная городская больница' lpu_nick, '1025001767548' OGRN union all
select 'Истринская районная клиническая больница' lpu_nick, '1035003055020' OGRN union all
select 'Каширская центральная районная больница' lpu_nick, '1025002510664' OGRN union all
select 'Климовская городская больница №2' lpu_nick, '1025002690129' OGRN union all
select 'Климовская центральная городская больница' lpu_nick, '1025002690602' OGRN union all
select 'Клинская детская городская больница' lpu_nick, '1025002591899' OGRN union all
select 'Коломенская центральная районная больница' lpu_nick, '1035004250038' OGRN union all
select 'Коломенский перинатальный центр' lpu_nick, '1165022052898' OGRN union all
select 'Королевская городская больница' lpu_nick, '1035003374262' OGRN union all
select 'Красногорская городская больница №1' lpu_nick, '1035004465990' OGRN union all
select 'Красногорская городская больница №2"(ДЕТИ)' lpu_nick, '1025002882948' OGRN union all
select 'Куровская городская больница' lpu_nick, '1025007461632' OGRN union all
select 'Ликинская городская больница' lpu_nick, '1025007458871' OGRN union all
select 'Лобненская центральная городская больница' lpu_nick, '1035004800258' OGRN union all
select 'Лотошинская центральная районная больница' lpu_nick, '1025007373863' OGRN union all
select 'Луховицкая центральная районная больница' lpu_nick, '1025007389770' OGRN union all
select 'Лыткаринская городская больница' lpu_nick, '1025003177550' OGRN union all
select 'Львовская районная больница' lpu_nick, '1035011456985' OGRN union all
select 'Люберецкая областная больница' lpu_nick, '1195027016480' OGRN union all
select 'Люберецкий кожно-венерологический диспансер' lpu_nick, '1025003212420' OGRN union all
select 'Люберецкий психоневрологический диспансер' lpu_nick, '1025003220340' OGRN union all
select 'Менделеевская городская больница' lpu_nick, '1025005688509' OGRN union all
select 'Михневская районная больница' lpu_nick, '1025005919828' OGRN union all
select 'Можайская центральная районная больница' lpu_nick, '1065028003578' OGRN union all
select 'Монинская больница' lpu_nick, '1025006527875' OGRN union all
select 'Московская областная больница им. проф. Розанова В.Н.' lpu_nick, '1025004914967' OGRN union all
select 'Московская областная детская больница' lpu_nick, '1025007515037' OGRN union all
select 'Московская областная детская клиническая травматолого-ортопедическая больница' lpu_nick, '1037700225375' OGRN union all
select 'Московский областной госпиталь для ветеранов войн' lpu_nick, '1025005683449' OGRN union all
select 'Московский областной клинический наркологический диспансер' lpu_nick, '1195081097331' OGRN union all
select 'Московский областной клинический противотуберкулезный диспансер' lpu_nick, '1027700411672' OGRN union all
select 'Московский областной научно-исследовательский институт акушерства и гинекологии' lpu_nick, '1027739745461' OGRN union all
select 'МОНИКИ' lpu_nick, '1037739023200' OGRN union all
select 'Московский областной онкологический диспансер' lpu_nick, '1025000511733' OGRN union all
select 'Московский областной перинатальный центр' lpu_nick, '1035000711635' OGRN union all
select 'Московский областной центр охраны материнства и детства' lpu_nick, '1105027014454' OGRN union all
select 'Мытищинская городская клиническая больница' lpu_nick, '1035005503356' OGRN union all
select 'Наро-Фоминская областная больница' lpu_nick, '1205000017375' OGRN union all
select 'Наро-Фоминский перинатальный центр' lpu_nick, '1165030052428' OGRN union all
select 'Ногинская центральная районная больница' lpu_nick, '1035006103538' OGRN union all
select 'Одинцовская областная больница' lpu_nick, '1195081091743' OGRN union all
select 'Озерская центральная районная больница' lpu_nick, '1025004543321' OGRN union all
select 'Орехово-Зуевская центральная городская больница' lpu_nick, '1115034003347' OGRN union all
select 'Орехово-Зуевский кожно-венерологический диспансер' lpu_nick, '1035007004735' OGRN union all
select 'Павлово-Посадская центральная районная больница' lpu_nick, '1025004640320' OGRN union all
select 'Подольская городская больница №2' lpu_nick, '1025004710147' OGRN union all
select 'Подольская городская клиническая больница №3' lpu_nick, '1035007209555' OGRN union all
select 'Подольская городская клиническая больница' lpu_nick, '1035007200733' OGRN union all
select 'Подольская детская городская больница' lpu_nick, '1035007201932' OGRN union all
select 'Подольский родильный дом' lpu_nick, '1085074000054' OGRN union all
select 'Протвинская городская больница' lpu_nick, '1025004859010' OGRN union all
select 'Психиатрическая больница №11' lpu_nick, '1025006469102' OGRN union all
select 'Психиатрическая больница №12' lpu_nick, '1025007372961' OGRN union all
select 'Психиатрическая больница №13' lpu_nick, '1025002592746' OGRN union all
select 'Психиатрическая больница №14' lpu_nick, '1025001096780' OGRN union all
select 'Психиатрическая больница №15' lpu_nick, '1055007303449' OGRN union all
select 'Психиатрическая больница №19' lpu_nick, '1055001511762' OGRN union all
select 'Психиатрическая больница №2 им В.И. Яковенко' lpu_nick, '1035009954814' OGRN union all
select 'Психиатрическая больница №23' lpu_nick, '1025003748131' OGRN union all
select 'Психиатрическая больница №24' lpu_nick, '1025000661817' OGRN union all
select 'Психиатрическая больница №28' lpu_nick, '1025002512281' OGRN union all
select 'Психиатрическая больница №3' lpu_nick, '1035002356960' OGRN union all
select 'Психиатрическая больница №4' lpu_nick, '1035011654534' OGRN union all
select 'Психиатрическая больница №5' lpu_nick, '1025005330888' OGRN union all
select 'Психиатрическая больница №6' lpu_nick, '1045004259442' OGRN union all
select 'Психиатрическая больница №7' lpu_nick, '1055008519510' OGRN union all
select 'Психиатрическая больница №8' lpu_nick, '1025004585363' OGRN union all
select 'Психиатрическая больница №9' lpu_nick, '1035001608267' OGRN union all
select 'Психоневрологическая больница для детей с поражением ЦНС с нарушением психики' lpu_nick, '1037700177162' OGRN union all
select 'Раменская центральная районная больница' lpu_nick, '1035007906053' OGRN union all
select 'Рошальская городская больница' lpu_nick, '1025006468112' OGRN union all
select 'Рузская областная больница' lpu_nick, '1205000012381' OGRN union all
select 'Сергиево-Посадская районная больница' lpu_nick, '1025005325102' OGRN union all
select 'Серебряно-Прудская центральная районная больница' lpu_nick, '1025007730791' OGRN union all
select 'Серпуховская городская больница имени Семашко Н.А.' lpu_nick, '1035008752789' OGRN union all
select 'Серпуховская центральная районная больница' lpu_nick, '1025005599464' OGRN union all
select 'Серпуховский родильный дом' lpu_nick, '1065043009437' OGRN union all
select 'Синьковская участковая больница' lpu_nick, '1025001099672' OGRN union all
select 'Солнечногорская центральная районная больница' lpu_nick, '1025005688498' OGRN union all
select 'Ступинская областная клиническая больница' lpu_nick, '1205000034799' OGRN union all
select 'Ступинская центральная районная клиническая больница' lpu_nick, '1025005922446' OGRN union all
select 'Ступинский психоневрологический диспансер' lpu_nick, '1045009162384' OGRN union all
select 'Талдомская центральная районная больница' lpu_nick, '1025007829791' OGRN union all
select 'Центральная клиническая психиатрическая больница' lpu_nick, '1027739497268' OGRN union all
select 'Чеховская областная больница' lpu_nick, '1195074004366' OGRN union all
select 'Шатурская центральная районная больница' lpu_nick, '1025006469036' OGRN union all
select 'Шаховская центральная районная больница' lpu_nick, '1025007864793' OGRN union all
select 'Щелковская областная больница' lpu_nick, '1195050016149' OGRN union all
select 'Щелковский перинатальный центр' lpu_nick, '1165050057204' OGRN union all
select 'Щелковский психоневрологический диспансер' lpu_nick, '1035010208353' OGRN union all
select 'Электрогорская городская больница' lpu_nick, '1025004646204' OGRN union all
select 'Электростальская центральная городская больница' lpu_nick, '1025007110480' OGRN union all
select 'Яхромская городская больница' lpu_nick, '1035001607046' OGRN union all
select 'Детский санаторий Отдых' lpu_nick, '1035002603415' OGRN union all
select 'Дом сестринского ухода' lpu_nick, '1055007329090' OGRN union all
select 'Дмитровская областная больница' lpu_nick, '1205000033358' OGRN
)
select
  COALESCE(lm.lpu_nick, l.Lpu_Nick) as "Юридическое лицо",
  L.Lpu_OGRN as "ОГРН",
  LS.LpuSection_FullName as "Отделение",
  COALESCE(LS.LpuSectionProfile_Name, 'Не указан') as "Профиль отделения",
  COALESCE(LSBP.LpuSectionBedProfile_Name, 'Не указан') as "Профиль койки",

  case when cntCOVID_KoikiAll > 0 then 'COVID-19'
       when cntPnevmo_KoikiAll > 0 then 'Пневмония'
     when cntBerem_KoikiAll > 0 then 'Беременные COVID-19'
     when cntRecover_KoikiAll >= 0 then 'Выздоравливающие'
  else 'Прочие' end as "Признак отделения",

  COALESCE(D.Diag_Code, 'Не указан') as "Диагноз - Код",
  COALESCE(D.Diag_Name, 'Не указан') as "Диагноз - Наименование",
  case when isIVL = 2 then 'Да' else 'Нет' end as "ИВЛ (да/нет)",
  case when isOxygen = 2 then 'Да' else 'Нет' end as "Кислород (да/нет)",
  COALESCE(cntDie, 0) as "количество умерших",
  COALESCE(cntLeave, 0) as "количество выписанных",
  round((1.00 * COALESCE(cntDie, 0)) / (COALESCE(cntDie, 0) + COALESCE(cntLeave, 0)) * 100, 2) as "летальность"
from EPS
inner join lateral (select * from dbo.v_LpuSection LS where LS.LpuSection_id = EPS.LpuSection_id limit 1) LS on true
inner join dbo.v_Lpu L on L.Lpu_id = LS.Lpu_id
  --and COALESCE(L.Lpu_IsTest, 1) = 1
  --and L.Lpu_begDate < dateadd('day', 1, (select EndDate from params))
  --and COALESCE(L.Lpu_endDate, (select BegDate from params)) >= (select BegDate from params)
left join localMO lm on lm.OGRN = L.Lpu_OGRN
left join dbo.v_LpuSectionBedProfile LSBP on LSBP.LpuSectionBedProfile_id = EPS.LpuSectionBedProfile_id
left join lateral(select * from dbo.v_Diag D where D.Diag_id = EPS.Diag_id limit 1) D 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 = LS.LpuSection_id
    and (ASV.AttributeSignValue_begDate < dateadd('day', 1, (select EndDate from params)) or ASV.AttributeSignValue_begDate is null)
    and (ASV.AttributeSignValue_endDate >= (select EndDate from params) 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
