SELECT
h.distcode,
MAX(a.ampurcodefull) AS ampurcodefull,
a.ampurname,
SUM(IF(h.hostype IN ('05','07'), t.target_telemed, NULL)) AS target_hosp,
SUM(IF(h.hostype NOT IN ('05','07') AND h.mcode = '21000', t.target_telemed, NULL)) AS target_st,
SUM(IF(h.hostype NOT IN ('05','07') AND h.mcode = '75000', t.target_telemed, NULL)) AS target_obj,
SUM(IF(h.hostype NOT IN ('05','07') AND h.mcode IN ('21000','75000'), t.target_telemed, NULL)) AS target_rpst,
SUM(IF(h.hostype IN ('05','07') OR h.mcode IN ('21000','75000'), t.target_telemed, NULL)) AS target_all,
COALESCE(MAX(his.his_hosp), 0) AS his_hosp,
COALESCE(MAX(his.his_st), 0) AS his_st,
COALESCE(MAX(his.his_obj), 0) AS his_obj,
COALESCE(MAX(his.his_all), 0) AS his_all,
COALESCE(MAX(station.mp_hosp), 0) AS mp_hosp,
COALESCE(MAX(station.mp_st), 0) AS mp_st,
COALESCE(MAX(station.mp_obj), 0) AS mp_obj,
COALESCE(MAX(station.mp_all), 0) AS mp_all,
COALESCE(MAX(station.son_hosp), 0) AS son_hosp,
COALESCE(MAX(station.son_st), 0) AS son_st,
COALESCE(MAX(station.son_obj), 0) AS son_obj,
COALESCE(MAX(station.son_all), 0) AS son_all
FROM
telemed_target AS t
INNER JOIN chospital AS h ON t.hoscode = h.hoscode
INNER JOIN c_ampur AS a ON h.provcode = a.changwatcode AND h.distcode = a.ampurcode
LEFT JOIN (
SELECT
h.distcode,
COUNT(IF(h.hostype IN ('05','07'), tr.hoscode, NULL)) AS his_hosp,
COUNT(IF(h.hostype NOT IN ('05','07') AND h.mcode = '21000', tr.hoscode, NULL)) AS his_st,
COUNT(IF(h.hostype NOT IN ('05','07') AND h.mcode = '75000', tr.hoscode, NULL)) AS his_obj,
COUNT(IF(h.hostype IN ('05','07') OR h.mcode IN ('21000','75000'), tr.hoscode, NULL)) AS his_all
FROM telemed_result AS tr
INNER JOIN chospital AS h ON tr.hoscode = h.hoscode
WHERE tr.date_serv BETWEEN '2025-10-01' AND '2026-09-30' AND h.provcode = '53'
GROUP BY h.distcode
) AS his ON h.distcode = his.distcode
LEFT JOIN (
SELECT
h.distcode,
SUM(IF(h.hostype IN ('05','07') AND ts.telemed_type = '01', ts.cnt_telemed_station, NULL)) AS mp_hosp,
SUM(IF(h.hostype NOT IN ('05','07') AND h.mcode = '21000' AND ts.telemed_type = '01', ts.cnt_telemed_station, NULL)) AS mp_st,
SUM(IF(h.hostype NOT IN ('05','07') AND h.mcode = '75000' AND ts.telemed_type = '01', ts.cnt_telemed_station, NULL)) AS mp_obj,
SUM(IF((h.hostype IN ('05','07') OR h.mcode IN ('21000','75000')) AND ts.telemed_type = '01', ts.cnt_telemed_station, NULL)) AS mp_all,
SUM(IF(h.hostype IN ('05','07') AND ts.telemed_type = '02', ts.cnt_telemed_station, NULL)) AS son_hosp,
SUM(IF(h.hostype NOT IN ('05','07') AND h.mcode = '21000' AND ts.telemed_type = '02', ts.cnt_telemed_station, NULL)) AS son_st,
SUM(IF(h.hostype NOT IN ('05','07') AND h.mcode = '75000' AND ts.telemed_type = '02', ts.cnt_telemed_station, NULL)) AS son_obj,
SUM(IF((h.hostype IN ('05','07') OR h.mcode IN ('21000','75000')) AND ts.telemed_type = '02', ts.cnt_telemed_station, NULL)) AS son_all
FROM telemed_station AS ts
INNER JOIN chospital AS h ON ts.hoscode = h.hoscode
WHERE ts.b_year = 2569 AND h.provcode = '53'
GROUP BY h.distcode
) AS station ON h.distcode = station.distcode
WHERE
t.b_year = 2569 AND h.provcode = '53'
GROUP BY
h.distcode, a.ampurname
ORDER BY
MAX(a.ampurcodefull) ASC