2_4_2

WITH

    toStartOfMonth(toDate('2024-01-01')) AS dat1,
    toLastDayOfMonth(toDate('2024-11-01'))AS dat2,
    dateDiff('month', dat1, dat2)+1 AS date_diff,



lpu AS (
              SELECT lpu_id,
                     lpu_short_name
              FROM dm_ap2.dct_lpu
              WHERE lpu_close_date IS NULL
),
     dept AS (
              SELECT DISTINCT ON (dept_id) dept_id AS dept_id,
                     facility_id,
                     dept_profile_med,
                     dept_name,
                     CASE WHEN  dept_name ilike '%патологии беременности%' OR
                                 dept_name ilike '%новорожденн%' OR
                                 dept_name ilike '%недоношенн%' OR
                                 dept_name ilike '%Акушерское отделение%' OR
                                 dept_name ilike '%РД%' OR
                                 dept_name ilike '%Перинатальный центр%' OR
                                 dept_name ilike '%Детск%' OR
                                 dept_name ilike '%Педиатри%' OR
                                 dept_name ilike '%Дневной стационар%' OR
                                 dept_name ilike '%ДC%' OR
                                 dept_name ilike '%Женская консультация%' OR
                                 dept_name ilike '%ЖК%' OR
                                 dept_name ilike '%КЖЗ%' OR
                                 dept_name ilike '%Клиника женского здоровья%' OR
                                 dept_name ilike '%ЦАОП%' OR
                                 dept_name ilike '%Центр амбулаторной онкологической помощи%' OR
                                 dept_name ilike '%неотложной медицинской помощи%' OR
                                 dept_name ilike '%неотложной%помощ%' OR
                                 dept_name ilike '%приёмного отделения%' OR
                                 dept_name ilike '%скорой медицинской помощи%' OR
                                 dept_name ilike '%СКП%' OR
                                 dept_name ilike '%Стационар кратковременного пребывания%' OR
                                 dept_name ilike '%Платны %' OR
                                 dept_name ilike '%ПМУ%' OR
                                 dept_name ilike '%КДО%' OR
                                 dept_name ilike '%Консультативно-диагностическое отделение%' OR
                                 dept_name ilike '%Консультативно-диагностический центр %' OR
                                 dept_name ilike '%Клинико-диагностическое отделени%' OR
                                 dept_name ilike '%Эндоскоп.центр%' OR
                                 dept_name ilike '%Эндоскопический центр %' OR
                                 dept_name ilike '%СКП%'  OR
                                 dept_name ilike '%Стационар кратковременного пребывания%' OR
                                 dept_name ilike '%ФЦ%' OR
                                 dept_name ilike '%ССК%' OR
                                 dept_name ilike '%Паллиатив%' OR
                                 dept_name ilike '%Отд.сестр.ухода%' THEN 0
                                    ELSE 1
                                    END AS kruglosutochnoe_dept
              FROM kis.dept
              WHERE dept_end_dt IS NULL
     ),
    hosp_detail_cte AS (SELECT
                            DISTINCT
                                    mov.movement_id,
                                    toDate(in_dt) AS  in_dt,
                                    mov.facility_id AS facility_id,
                                    mov.bed_profile,
                                    mov.dept_id AS dept_id
                        FROM kis.movement_history_u AS mov
                        LEFT JOIN dm_ap2.dct_lpu lpu ON mov.facility_id = lpu.lpu_id
                        LEFT JOIN dept ON mov.facility_id = dept.facility_id AND mov.dept_id = dept.dept_id::text
                        WHERE dept.kruglosutochnoe_dept = 1
                        AND ((lpu.lpu_short_name IS NOT NULL AND (lpu.lpu_open_date  <= lpu.lpu_close_date  OR lpu.lpu_close_date  IS NULL OR lpu.lpu_open_date IS NULL))
                        OR (lpu.lpu_short_name IN ('(Оцифровка эпикризов) ГБУЗ "ГКБ им. С.П. Боткина"', 'ГБУЗ "ДГКБ св.Владимира ДЗМ"')))
  --AND patient_id IS NOT NULL
--                      and toDate(in_dt)>='2024-01-01'
--                      and toDate(in_dt)< '2024-12-01'
                        AND toDate(in_dt) BETWEEN dat1 AND dat2
  ),
    hosp_grouped AS (
                    SELECT
                          facility_id,
                          bed_profile,
                          COUNT(movement_id) AS hosp_count
                    FROM hosp_detail_cte
                      GROUP BY facility_id,
                      bed_profile)
,     doc AS (
            SELECT
                    emp_id,
                    kis.movement_history_u.facility_id AS facility_id,
                    CASE
                    WHEN kis.movement_history_u.facility_id IN (14932313289,
                     14932313323,
                     14932313349,
                     14932313361,
                     14932313419,
                     14932313341,
                     14932313377,
                     14932313363,
                     14932313331,
                     14932313365,
                     14932313351,
                     14932313369,
                     10394853,
                     14932313311,
                     14932313333,
                     14932313379,
                     14932313343,
                     14932313317,
                     14932313409,
                     14932313417,
                     14932313407)
                    THEN TRUE ELSE FALSE
                    END AS "Взрослый_многопрофильный",
                    kis.movement_history_u.dept_id AS dept_id_mov,
                    dept.dept_name AS dept_name,
                    dept.dept_id AS dept_id_d,
                    CASE WHEN dept_name ilike '%Детск%' OR dept_name ilike '%Педиатри%' THEN 'Детские отделения'
                   WHEN dept_name ilike '%Дневной стационар%' OR dept_name ilike '%ДС%' THEN 'Дневной стационар'
                   WHEN dept_name ilike '%Женская консультация%' OR dept_name ilike '%ЖК%' OR dept_name ilike '%Клиника женского здоровья%' OR dept_name ilike '%КЖЗ%' THEN 'Центр женского здоровья'
                   WHEN dept_name ilike '%КДО%' OR dept_name ilike '%Консультативно-диагностический центр%' OR dept_name ilike '%Консультативно-диагностическое отделение%' OR dept_name ilike '%Клинико-диагностическое отделение%' THEN 'КДО'
                   WHEN dept_name ilike '%неотложной медицинской помощи%' OR dept_name ilike '%неотложной%помощи%' OR dept_name ilike '%приёмного отделения%' OR dept_name ilike '%скорой медицинской помощи%'  THEN 'Приемное отделение'
                   WHEN dept_name ilike '%Паллиатив%' OR dept_name ilike '%Отд.сестр.ухода%' THEN 'Паллиатив'
                   WHEN dept_name ilike '%Платны%' OR dept_name ilike '%ПМУ%' THEN 'ПМУ'
                   WHEN dept_name ilike '%РД %' OR dept_name ilike '%Перинатальный центр%' OR dept_name ilike '%Акушерское отделение%' OR dept_name ilike '%недоношенн%' OR dept_name ilike '%новорожденн%' OR dept_name ilike '%патологии беременности%'  THEN 'РД'
                   WHEN dept_name ilike '%ФЦ_%' OR dept_name ilike '%ССК%' THEN 'ФЦ'
                   WHEN dept_name ilike '%СКП%' OR dept_name ilike '%Стационар кратковременного пребывания%' THEN 'СКП'
                   WHEN dept_name ilike '%ЦАОП%' OR dept_name ilike '%Центр амбулаторной онкологической помощи%' THEN 'ЦАОП'
                   WHEN dept_name ilike '%Эндоскоп.центр%' OR dept_name ilike '%Эндоскопический центр%' THEN 'Эндоскопический центр'
                   ELSE dept_name END AS "Отделение",
                     bed_profile,
                     post
   FROM kis.movement_history_u
   LEFT any JOIN kis.emp ON kis.emp.id::UUID = kis.movement_history_u.emp_id
   LEFT JOIN dept ON dept.dept_id = kis.movement_history_u.dept_id::UUID
   WHERE 1 = 1
   AND post ILIKE '%врач%'
   AND post NOT ILIKE'%методист%' AND post NOT ILIKE '%статистик%' AND post NOT ILIKE '%главн%' AND post NOT ILIKE
'%помощник%' AND post NOT ILIKE '%помошник%' AND post NOT ILIKE '%президент%' AND post NOT ILIKE '%заведующ%' AND post NOT ILIKE '%заведущ%' AND post NOT ILIKE '%зав.%' AND post NOT ILIKE '%руководитель%' AND post NOT ILIKE '%директор%' AND post NOT ILIKE
'%начальник%' AND post NOT ILIKE '%тест%' AND post NOT ILIKE '%заместитель%'
    AND in_dt >= dat1
              AND (in_dt IS NULL
              OR in_dt <= dat2)
),
lpu_doc AS (
    SELECT
        lpu_id,
        lpu_short_name,
         main_lpu_short_name
     FROM dm_ap2.dct_lpu
  )
-- Детализация
, detal AS (
    SELECT
        DISTINCT
        --toDate(in_dt) AS beg_dt,
        --toDate(out_dt) AS end_dt,
        emp_id,
        bed_profile,
        lpu_doc.lpu_short_name AS lpu_short_name,
        lpu_doc.main_lpu_short_name AS main_lpu_short_name,
        doc.dept_name,
        doc."Отделение",
        doc.facility_id AS facility_id,
        doc.dept_id_mov AS dept_id,
        doc.post
    FROM doc
    LEFT JOIN lpu_doc ON doc.facility_id = lpu_doc.lpu_id
    WHERE 1 = 1
            AND (
            (lpu_short_name IS NOT NULL --AND (beg_dt <= end_dt OR end_dt IS NULL))
            OR
            (lpu_short_name IN ('(Оцифровка эпикризов) ГБУЗ "ГКБ им. С.П. Боткина"', 'ГБУЗ "ДГКБ св.Владимира ДЗМ"'))
            )
            AND "Взрослый_многопрофильный" = TRUE) --and facility_id = '14932313331' --and post ilike '%нейро%'
    ORDER BY 2
)
-- Рейтинг по кол-ву работающих врачей
, rating AS (
    SELECT
        facility_id,
        dept_id,
        bed_profile,
        "Отделение",
        COUNT(DISTINCT emp_id) FILTER(WHERE "Отделение" NOT IN('Детские отделения', 'Дневной стационар','ЖК', 'КДО','КЖЗ', 'Приемное отделение', 'Паллиатив', 'ПМУ', 'РД', 'ФЦ', 'СКП', 'ЦАОП','Эндоскопический центр')) AS cnt_id
    FROM detal
    GROUP BY 1,2,3,4
),
stat AS (SELECT  CASE
                        WHEN lpu.lpu_short_name ilike '%ГКБ № 13 ДЗМ%' THEN 'ЭПИКРИЗ ГБУЗ "ГКБ имени В.П. Демихова ДЗМ"'
                ELSE lpu.lpu_short_name
                END AS lpu_short_name,
                hosp_grouped.bed_profile AS bed_profile,
                hosp_grouped.hosp_count,
                rating.cnt_id,
                ROUND((hosp_grouped.hosp_count / date_diff)/ rating.cnt_id,2) AS "out_doc"
FROM hosp_grouped
LEFT JOIN rating ON hosp_grouped.facility_id = rating.facility_id AND hosp_grouped.bed_profile = rating.bed_profile
LEFT JOIN dm_ap2.dct_lpu lpu ON lpu.lpu_id = hosp_grouped.facility_id
WHERE (hosp_grouped.hosp_count != 0 AND rating.cnt_id != 0) OR rating.cnt_id != 0
),
        msk_stat AS (
                 SELECT bed_profile,
                        ROUND(avg("out_doc"),2) AS avg_msk
                 FROM stat
                 GROUP BY 1
)
SELECT lpu_short_name,
       bed_profile,
       round(SUM(hosp_count)/date_diff,0) AS sum_hosp,
       SUM(cnt_id) AS sumid,
        (SUM(hosp_count)/date_diff)/ (SUM(cnt_id)) AS "hosp/doc",
       avg_msk
FROM stat
LEFT JOIN msk_stat ON stat.bed_profile = msk_stat.bed_profile
GROUP BY 1,2,6
ORDER BY 1,2