WITH s_smo AS (
SELECT zp.spolis || zp.npolis AS polis,
       zp.sump + NVL(zp.sump_pod, 0 ) AS sump
FROM mtr.s19_zap zp,
     mtr.s19_schet s
WHERE s.Year>=$SY$ and s.Month >=$SM$ and s.Year<=$EY$ and s.Month <= $EM$ AND s.code = zp.code AND
      zp.usl_ok = 4 AND zp.sump > 0
UNION ALL
SELECT zpp.spolis || zpp.npolis AS polis,
       zpp.sump AS sump
FROM mtr.l19_zap@cod.linc zpp,
     mtr.l19_schet@cod.linc sm
WHERE sm.Year>=$SY$ and sm.Month >=$SM$ and sm.Year<=$EY$ and sm.Month <= $EM$ AND sm.code = zpp.code AND
      zpp.usl_ok = 4 AND zpp.sump > 0
	  )



, ks AS (
/*Онкогемотология*/
SELECT s.code_mo,
       1 AS profil,
       COUNT(zp.id_zap) AS zp, 
       SUM(zp.kd_z) AS kd,
       SUM(zp.sump) / 1000 AS sump,
       SUM(CASE WHEN TRUNC(MONTHS_BETWEEN(zp.date_z_1, zp.dr) / 12) <= 17 THEN 1 ELSE 0 END) AS dzp,
       SUM(CASE WHEN TRUNC(MONTHS_BETWEEN(zp.date_z_1, zp.dr) / 12) <= 17 THEN zp.kd_z ELSE 0 END) AS dkd,
       SUM(CASE WHEN TRUNC(MONTHS_BETWEEN(zp.date_z_1, zp.dr) / 12) <= 17 THEN zp.sump ELSE 0 END) AS dsump
FROM mtr.s19_schet s,
     mtr.s19_zap zp,
     mtr.s19_sl sl,
     mtr.s19_onk_usl ou
WHERE s.Year>=$SY$ and s.Month >=$SM$ and s.Year<=$EY$ and s.Month <= $EM$ AND s.code_mo IN (530001, 530050) AND s.code = zp.code AND
      zp.sump > 0 AND zp.usl_ok = 1 AND zp.id_zap = sl.id_zap AND
      SUBSTR(sl.ds1, 1, 3) BETWEEN 'C81' AND 'C96' AND sl.profil = 12 AND sl.id_sl = ou.id_sl
GROUP BY s.code_mo
UNION ALL
SELECT s.code_mo,
       1 AS profil,
       COUNT(zp.id_zap) AS zp, 
       SUM(zp.kd_z) AS kd,
       SUM(zp.sump) / 1000 AS sump,
       SUM(CASE WHEN TRUNC(MONTHS_BETWEEN(zp.date_z_1, zp.dr) / 12) <= 17 THEN 1 ELSE 0 END) AS dzp,
       SUM(CASE WHEN TRUNC(MONTHS_BETWEEN(zp.date_z_1, zp.dr) / 12) <= 17 THEN zp.kd_z ELSE 0 END) AS dkd,
       SUM(CASE WHEN TRUNC(MONTHS_BETWEEN(zp.date_z_1, zp.dr) / 12) <= 17 THEN zp.sump ELSE 0 END) AS dsump
FROM mtr.l19_schet@cod.linc s,
     mtr.l19_zap@cod.linc zp,
     mtr.l19_sl@cod.linc sl,
     mtr.l19_onk_usl@cod.linc ou
WHERE s.Year>=$SY$ and s.Month >=$SM$ and s.Year<=$EY$ and s.Month <= $EM$ AND s.code_mo IN (530001, 530050) AND s.code = zp.code AND
      zp.sump > 0 AND zp.usl_ok = 1 AND zp.id_zap = sl.id_zap AND
      SUBSTR(sl.ds1, 1, 3) BETWEEN 'C81' AND 'C96' AND sl.profil = 12 AND sl.id_sl = ou.id_sl
GROUP BY s.code_mo
UNION ALL      
/*Химиотерапия*/
SELECT s.code_mo,
       2 AS profil,
       COUNT(zp.id_zap) AS zp, 
       SUM(zp.kd_z) AS kd,
       SUM(zp.sump) / 1000 AS sump,
       SUM(CASE WHEN TRUNC(MONTHS_BETWEEN(zp.date_z_1, zp.dr) / 12) <= 17 THEN 1 ELSE 0 END) AS dzp,
       SUM(CASE WHEN TRUNC(MONTHS_BETWEEN(zp.date_z_1, zp.dr) / 12) <= 17 THEN zp.kd_z ELSE 0 END) AS dkd,
       SUM(CASE WHEN TRUNC(MONTHS_BETWEEN(zp.date_z_1, zp.dr) / 12) <= 17 THEN zp.sump ELSE 0 END) AS dsump
FROM mtr.s19_schet s,
     mtr.s19_zap zp,
     mtr.s19_sl sl,
     mtr.s19_onk_usl ou
WHERE s.Year>=$SY$ and s.Month >=$SM$ and s.Year<=$EY$ and s.Month <= $EM$ AND s.code_mo IN (530001, 530050) AND s.code = zp.code AND
      zp.sump > 0 AND zp.usl_ok = 1 AND zp.id_zap = sl.id_zap AND
      sl.profil IN (18, 60) AND sl.id_sl = ou.id_sl AND ou.usl_tip IN (2, 4)
GROUP BY s.code_mo
UNION ALL
SELECT s.code_mo,
       2 AS profil,
       COUNT(zp.id_zap) AS zp, 
       SUM(zp.kd_z) AS kd,
       SUM(zp.sump) / 1000 AS sump,
       SUM(CASE WHEN TRUNC(MONTHS_BETWEEN(zp.date_z_1, zp.dr) / 12) <= 17 THEN 1 ELSE 0 END) AS dzp,
       SUM(CASE WHEN TRUNC(MONTHS_BETWEEN(zp.date_z_1, zp.dr) / 12) <= 17 THEN zp.kd_z ELSE 0 END) AS dkd,
       SUM(CASE WHEN TRUNC(MONTHS_BETWEEN(zp.date_z_1, zp.dr) / 12) <= 17 THEN zp.sump ELSE 0 END) AS dsump
FROM mtr.l19_schet@cod.linc s,
     mtr.l19_zap@cod.linc zp,
     mtr.l19_sl@cod.linc sl,
     mtr.l19_onk_usl@cod.linc ou
WHERE s.Year>=$SY$ and s.Month >=$SM$ and s.Year<=$EY$ and s.Month <= $EM$ AND s.code_mo IN (530001, 530050) AND s.code = zp.code AND
      zp.sump > 0 AND zp.usl_ok = 1 AND zp.id_zap = sl.id_zap AND
      sl.profil IN (18, 60) AND sl.id_sl = ou.id_sl AND ou.usl_tip IN (2, 4)
GROUP BY s.code_mo
      )
      


, ks1 AS (
/*Онкогемотология*/
SELECT s.code_mo,
       1 AS profil,
       COUNT(zp.id_zap) AS zp, 
       SUM(zp.kd_z) AS kd,
       SUM(zp.sump) / 1000 AS sump,
       SUM(CASE WHEN TRUNC(MONTHS_BETWEEN(zp.date_z_1, zp.dr) / 12) <= 17 THEN 1 ELSE 0 END) AS dzp,
       SUM(CASE WHEN TRUNC(MONTHS_BETWEEN(zp.date_z_1, zp.dr) / 12) <= 17 THEN zp.kd_z ELSE 0 END) AS dkd,
       SUM(CASE WHEN TRUNC(MONTHS_BETWEEN(zp.date_z_1, zp.dr) / 12) <= 17 THEN zp.sump ELSE 0 END) AS dsump
FROM mtr.s19_schet s,
     mtr.s19_zap zp,
     mtr.s19_sl sl,
     mtr.s19_onk_usl ou
WHERE s.Year>=$SY$ and s.Month >=$SM$ and s.Year<=$EY$ and s.Month <= $EM$ AND s.code_mo IN (530001, 530050) AND s.code = zp.code AND
      zp.sump > 0 AND zp.usl_ok = 2 AND zp.id_zap = sl.id_zap AND
      SUBSTR(sl.ds1, 1, 3) BETWEEN 'C81' AND 'C96' AND sl.profil = 12 AND sl.id_sl = ou.id_sl
GROUP BY s.code_mo
UNION ALL
SELECT s.code_mo,
       1 AS profil,
       COUNT(zp.id_zap) AS zp, 
       SUM(zp.kd_z) AS kd,
       SUM(zp.sump) / 1000 AS sump,
       SUM(CASE WHEN TRUNC(MONTHS_BETWEEN(zp.date_z_1, zp.dr) / 12) <= 17 THEN 1 ELSE 0 END) AS dzp,
       SUM(CASE WHEN TRUNC(MONTHS_BETWEEN(zp.date_z_1, zp.dr) / 12) <= 17 THEN zp.kd_z ELSE 0 END) AS dkd,
       SUM(CASE WHEN TRUNC(MONTHS_BETWEEN(zp.date_z_1, zp.dr) / 12) <= 17 THEN zp.sump ELSE 0 END) AS dsump
FROM mtr.l19_schet@cod.linc s,
     mtr.l19_zap@cod.linc zp,
     mtr.l19_sl@cod.linc sl,
     mtr.l19_onk_usl@cod.linc ou
WHERE s.Year>=$SY$ and s.Month >=$SM$ and s.Year<=$EY$ and s.Month <= $EM$ AND s.code_mo IN (530001, 530050) AND s.code = zp.code AND
      zp.sump > 0 AND zp.usl_ok = 2 AND zp.id_zap = sl.id_zap AND
      SUBSTR(sl.ds1, 1, 3) BETWEEN 'C81' AND 'C96' AND sl.profil = 12 AND sl.id_sl = ou.id_sl
GROUP BY s.code_mo
UNION ALL      
/*Химиотерапия*/
SELECT s.code_mo,
       2 AS profil,
       COUNT(zp.id_zap) AS zp, 
       SUM(zp.kd_z) AS kd,
       SUM(zp.sump) / 1000 AS sump,
       SUM(CASE WHEN TRUNC(MONTHS_BETWEEN(zp.date_z_1, zp.dr) / 12) <= 17 THEN 1 ELSE 0 END) AS dzp,
       SUM(CASE WHEN TRUNC(MONTHS_BETWEEN(zp.date_z_1, zp.dr) / 12) <= 17 THEN zp.kd_z ELSE 0 END) AS dkd,
       SUM(CASE WHEN TRUNC(MONTHS_BETWEEN(zp.date_z_1, zp.dr) / 12) <= 17 THEN zp.sump ELSE 0 END) AS dsump
FROM mtr.s19_schet s,
     mtr.s19_zap zp,
     mtr.s19_sl sl,
     mtr.s19_onk_usl ou
WHERE s.Year>=$SY$ and s.Month >=$SM$ and s.Year<=$EY$ and s.Month <= $EM$ AND s.code_mo IN (530001, 530050) AND s.code = zp.code AND
      zp.sump > 0 AND zp.usl_ok = 2 AND zp.id_zap = sl.id_zap AND
      sl.profil IN (18, 60) AND sl.id_sl = ou.id_sl AND ou.usl_tip IN (2, 4)
GROUP BY s.code_mo
UNION ALL
SELECT s.code_mo,
       2 AS profil,
       COUNT(zp.id_zap) AS zp, 
       SUM(zp.kd_z) AS kd,
       SUM(zp.sump) / 1000 AS sump,
       SUM(CASE WHEN TRUNC(MONTHS_BETWEEN(zp.date_z_1, zp.dr) / 12) <= 17 THEN 1 ELSE 0 END) AS dzp,
       SUM(CASE WHEN TRUNC(MONTHS_BETWEEN(zp.date_z_1, zp.dr) / 12) <= 17 THEN zp.kd_z ELSE 0 END) AS dkd,
       SUM(CASE WHEN TRUNC(MONTHS_BETWEEN(zp.date_z_1, zp.dr) / 12) <= 17 THEN zp.sump ELSE 0 END) AS dsump
FROM mtr.l19_schet@cod.linc s,
     mtr.l19_zap@cod.linc zp,
     mtr.l19_sl@cod.linc sl,
     mtr.l19_onk_usl@cod.linc ou
WHERE s.Year>=$SY$ and s.Month >=$SM$ and s.Year<=$EY$ and s.Month <= $EM$ AND s.code_mo IN (530001, 530050) AND s.code = zp.code AND
      zp.sump > 0 AND zp.usl_ok = 2 AND zp.id_zap = sl.id_zap AND
      sl.profil IN (18, 60) AND sl.id_sl = ou.id_sl AND ou.usl_tip IN (2, 4)
GROUP BY s.code_mo
      )
 
/*Деньги*/
SELECT 'тыс.руб.' AS " ",
       TO_CHAR( SUM(sump)/1000)  AS "В рамках базовой программы", null AS "Всего случаи лечения" , null AS "Всего койко-дни", null AS "Всего тыс.руб.", null AS "Дети случаи лечения", null AS "Дети койко-дни", null AS "Дети тыс.руб."
FROM s_smo
     union all
  
/*Вызовы*/
SELECT 'вызовы' AS " ",
       TO_CHAR(COUNT(polis)) AS "В рамках базовой программы", null, null, null, null, null, null
FROM s_smo
UNION ALL
      
/*Люди*/
SELECT 'чел.' AS " ",
      TO_CHAR( COUNT(DISTINCT polis)) AS "В рамках базовой программы", null, null, null, null, null, null
FROM s_smo



union all

SELECT code_mo AS "МО",
       'Онкогематология (KC)' AS "Профиль медицинской помощи",
       SUM(zp) AS "Всего случаи лечения", 
       SUM(kd) AS "Всего койко-дни", 
       SUM(sump) AS "Всего тыс.руб.", 
       SUM(dzp) AS "Дети случаи лечения", 
       SUM(dkd) AS "Дети койко-дни", 
       SUM(dsump) AS "Дети тыс.руб."
FROM ks
WHERE profil = 1
GROUP BY code_mo
UNION ALL

SELECT code_mo AS "МО",
       'Химиотерапия (KC)' AS "Профиль медицинской помощи",
       SUM(zp) AS "Всего случаи лечения", 
       SUM(kd) AS "Всего койко-дни", 
       SUM(sump) AS "Всего тыс.руб.", 
       SUM(dzp) AS "Дети случаи лечения", 
       SUM(dkd) AS "Дети койко-дни", 
       SUM(dsump) AS "Дети тыс.руб."
FROM ks
WHERE profil = 2
GROUP BY code_mo

union all

      
SELECT code_mo AS "МО",
       'Онкогематология (ДС)' AS "Профиль медицинской помощи",
       SUM(zp) AS "Всего случаи лечения", 
       SUM(kd) AS "Всего койко-дни", 
       SUM(sump) AS "Всего тыс.руб.", 
       SUM(dzp) AS "Дети случаи лечения", 
       SUM(dkd) AS "Дети койко-дни", 
       SUM(dsump) AS "Дети тыс.руб."
FROM ks1
WHERE profil = 1
GROUP BY code_mo
UNION ALL

SELECT code_mo AS "МО",
       'Химиотерапия (ДС)' AS "Профиль медицинской помощи",
       SUM(zp) AS "Всего случаи лечения", 
       SUM(kd) AS "Всего койко-дни", 
       SUM(sump) AS "Всего тыс.руб.", 
       SUM(dzp) AS "Дети случаи лечения", 
       SUM(dkd) AS "Дети койко-дни", 
       SUM(dsump) AS "Дети тыс.руб."
FROM ks1
WHERE profil = 2
GROUP BY code_mo
