-- =============================================================================
|
-- 病区出院随访统计 · 存储过程（推荐，真正按调用传参）
|
-- =============================================================================
|
-- MySQL 视图不能带参数（视图定义里连 @变量 都不许用），要「传参」就用存储过程。
|
--
|
-- 调用：
|
-- CALL p_ward_followup_stats('2026-10-04', '2026-11-03', '1', 7);
|
-- CALL p_ward_followup_stats('2026-10-04', '2026-11-03', NULL, NULL); -- 口径/及时天数用默认值
|
-- （MySQL 存储过程没有「可选参数」，四个参数都必须写，不需要的传 NULL）
|
--
|
-- 参数：
|
-- p_start_time 出院时间起（含），'yyyy-MM-dd' 或 'yyyy-MM-dd HH:mm:ss'
|
-- p_end_time 出院时间止（含当天 23:59:59），传 'yyyy-MM-dd' 即可
|
-- p_count_style 随访方式命中口径：1=应随访（默认） 3=仅成功 其它=全部
|
-- p_timely_days 及时天数基数，默认 7（补偿天数另加）
|
-- =============================================================================
|
|
DROP PROCEDURE IF EXISTS p_ward_followup_stats;
|
|
DELIMITER $$
|
CREATE PROCEDURE p_ward_followup_stats(
|
IN p_start_time DATETIME,
|
IN p_end_time DATETIME,
|
IN p_count_style VARCHAR(2),
|
IN p_timely_days INT
|
)
|
BEGIN
|
DECLARE v_start DATETIME;
|
DECLARE v_end DATETIME;
|
DECLARE v_style VARCHAR(2);
|
DECLARE v_timely_days INT;
|
|
-- 起止时间必传；止时间按「包含当天」处理
|
IF p_start_time IS NULL OR p_end_time IS NULL THEN
|
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'p_start_time / p_end_time 不能为空';
|
END IF;
|
|
SET v_start = DATE(p_start_time);
|
SET v_end = DATE_ADD(DATE(p_end_time), INTERVAL 1 DAY);
|
SET v_style = IFNULL(p_count_style, '1');
|
SET v_timely_days = IFNULL(p_timely_days, 7);
|
|
SELECT
|
t.leavehospitaldistrictname, -- 出院病区
|
t.deptname, -- 出院科室
|
t.dischargeCount, -- 出院人次（首次随访记录数）
|
t.nonFollowUp, -- 无需随访人次
|
t.followUpNeeded, -- 应随访人次
|
t.needFollowUp, -- 首次应随访（= 应随访人次）
|
t.pendingFollowUp, -- 首次待随访
|
t.followUpSuccess, -- 首次随访成功
|
t.followUpFail, -- 首次随访失败（含人工超时）
|
t.manualSuccess, -- 首次人工随访成功
|
t.voiceSuccess, -- 首次语音随访成功
|
t.smsSuccess, -- 首次短信随访成功
|
t.weChatSuccess, -- 首次微信随访成功
|
-- 随访成功率 = 成功 / 应随访（只算成功，不含失败）
|
IF(t.needFollowUp = 0, '0.00%',
|
CONCAT(ROUND(100 * t.followUpSuccess / t.needFollowUp, 2), '%')) AS followUpSuccessRate,
|
-- 渠道合计成功率 = 四种方式成功之和 / 四种方式命中之和
|
IF(t.manual + t.voice + t.sms + t.weChat = 0, '0.00%',
|
CONCAT(ROUND(100 * (t.manualSuccess + t.voiceSuccess + t.smsSuccess + t.weChatSuccess)
|
/ (t.manual + t.voice + t.sms + t.weChat), 2), '%')) AS channelSuccessRate,
|
-- 及时率：及时 = finishtime 不为空 且 DATEDIFF(finishtime, endtime) <= 及时天数 + 补偿天数
|
IF(IFNULL(rt.timely_base, 0) = 0, '0.00%',
|
CONCAT(ROUND(100 * rt.timely_cnt / rt.timely_base, 2), '%')) AS timelyRate
|
FROM (
|
SELECT
|
r.ward_code AS ward_code,
|
MAX(r.ward_name) AS leavehospitaldistrictname,
|
MAX(r.dept_name) AS deptname,
|
COUNT(*) AS dischargeCount,
|
SUM(r.f_no) AS nonFollowUp,
|
SUM(r.f_need) AS followUpNeeded,
|
SUM(r.f_need) AS needFollowUp,
|
SUM(IF(r.sendstate = 2, 1, 0)) AS pendingFollowUp,
|
SUM(r.f_succ) AS followUpSuccess,
|
SUM(r.f_fail) AS followUpFail,
|
SUM(IF(r.ch_hit = 1 AND r.cp = '1', 1, 0)) AS manual,
|
SUM(IF(r.ch_hit = 1 AND r.cp = '3', 1, 0)) AS voice,
|
SUM(IF(r.ch_hit = 1 AND r.cp = '4', 1, 0)) AS sms,
|
SUM(IF(r.ch_hit = 1 AND r.cp = '5', 1, 0)) AS weChat,
|
SUM(IF(r.sendstate = 6 AND r.cp = '1', 1, 0)) AS manualSuccess,
|
SUM(IF(r.sendstate = 6 AND r.cp = '3', 1, 0)) AS voiceSuccess,
|
SUM(IF(r.sendstate = 6 AND r.cp = '4', 1, 0)) AS smsSuccess,
|
SUM(IF(r.sendstate = 6 AND r.cp = '5', 1, 0)) AS weChatSuccess
|
FROM (
|
SELECT
|
ss.leavehospitaldistrictcode AS ward_code,
|
ss.leavehospitaldistrictname AS ward_name,
|
ss.leaveldeptname AS dept_name,
|
ss.sendstate AS sendstate,
|
ss.current_preachform AS cp,
|
IF(ss.sendstate = 4, 1, 0) AS f_no,
|
IF(ss.sendstate IS NOT NULL AND ss.sendstate <> 4, 1, 0) AS f_need,
|
IF(ss.sendstate = 6, 1, 0) AS f_succ,
|
IF(ss.sendstate IN (5, 7), 1, 0) AS f_fail,
|
-- 随访方式命中条件：方式 1 排除「无需随访」；方式 3 只算已完成；其它方式不设限
|
IF(v_style = '1',
|
IF(ss.sendstate IS NOT NULL AND ss.sendstate <> 4, 1, 0),
|
IF(v_style = '3',
|
IF(ss.sendstate = 6, 1, 0),
|
IF(ss.sendstate IS NOT NULL, 1, 0))) AS ch_hit
|
FROM service_subtask ss
|
WHERE ss.del_flag = 0
|
AND ss.service_type = '2'
|
AND ss.visit_count = 1 -- 只要首次随访
|
AND ss.leavehospitaldistrictcode IS NOT NULL
|
AND ss.endtime >= DATE(v_start)
|
AND ss.endtime < DATE_ADD(DATE(v_end), INTERVAL 1 DAY)
|
AND ss.orgid = '20001001'
|
) r
|
GROUP BY r.ward_code
|
) t
|
LEFT JOIN (
|
-- 及时率：按病区预聚合一次再关联，避免一对多放大分母
|
SELECT
|
ss.leavehospitaldistrictcode AS ward_code,
|
COUNT(*) AS timely_base,
|
SUM(IF(ss.finishtime IS NOT NULL
|
AND DATEDIFF(ss.finishtime, ss.endtime) <= v_timely_days + IFNULL(cp.compensate_days, 0),
|
1, 0)) AS timely_cnt
|
FROM service_subtask ss
|
LEFT JOIN (
|
-- 补偿时间：varchar 且可能是空串，先按子任务聚合成一行
|
SELECT subid,
|
SUM(CAST(COALESCE(NULLIF(TRIM(compensate_time), ''), '0') AS SIGNED)) AS compensate_days
|
FROM service_subtask_preachform
|
WHERE del_flag = 0
|
GROUP BY subid
|
) cp ON cp.subid = ss.id
|
WHERE ss.del_flag = 0
|
AND ss.service_type = '2'
|
AND ss.visit_count = 1
|
AND ss.leavehospitaldistrictcode IS NOT NULL
|
AND ss.visit_time IS NOT NULL
|
AND ss.sendstate <> 4
|
AND DATE(ss.visit_time) < DATE(NOW()) -- 接口未传 startTime/endTime 时的固定条件
|
AND ss.endtime >= DATE(v_start)
|
AND ss.endtime < DATE_ADD(DATE(v_end), INTERVAL 1 DAY)
|
AND ss.orgid = '20001001'
|
GROUP BY ss.leavehospitaldistrictcode
|
) rt ON rt.ward_code = t.ward_code
|
ORDER BY t.dischargeCount DESC;
|
END$$
|
DELIMITER ;
|
