-- =============================================================================
-- 病区出院随访统计 · 纯查询版（会话变量传参，适合 Navicat/DataGrip 临时取数）
-- =============================================================================
-- @start_time / @end_time 就是原来写死的 endtime 起止，改这两行即可。
-- 注意：这不是视图，变量属于当前连接，换连接要重新 SET。
-- =============================================================================

SET @start_time         = '2026-10-04';   -- 出院时间起（含）
SET @end_time           = '2026-11-03';   -- 出院时间止（含当天）
SET @followUpCountStyle = '1';            -- 1=应随访 3=仅成功 其它=全部
SET @timelyDays         = 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(@followUpCountStyle = '1',
         IF(ss.sendstate IS NOT NULL AND ss.sendstate <> 4, 1, 0),
         IF(@followUpCountStyle = '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(@start_time)
      AND ss.endtime <  DATE_ADD(DATE(@end_time), 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) <= @timelyDays + 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(@start_time)
    AND ss.endtime <  DATE_ADD(DATE(@end_time), INTERVAL 1 DAY)
    AND ss.orgid = '20001001'
  GROUP BY ss.leavehospitaldistrictcode
) rt ON rt.ward_code = t.ward_code
ORDER BY t.dischargeCount DESC;