-- =============================================================================
-- /smartor/serviceSubtask/getSfStatistics 的 SQL 版（病区维度 · 全部病区 · 出院随访 serviceType=2）
-- =============================================================================
-- 对应代码：
--   ServiceSubtaskController#getSfStatistics
--     → ServiceSubtaskServiceImpl#getSfStatistics            （分组 + 逐条累加）
--     → ServiceSubtaskMapper.getSfStatistics / getSfStatisticsGroupKey
--     → ServiceSubtaskMapper.selectTimelyRateBatch           （及时率 rate）
-- 数据源：service_subtask（本接口只查这一张表）
--
-- 入参：只有 hospitalStartTime（出院时间下限）。
--   分组：statisticaltype=1（病区），且不传 leavehospitaldistrictcodes → 全部病区。
--   服务类型：serviceType = 2（出院随访）。
--   说明：接口 Mapper 里出院时间条件是「hospitalStartTime 与 hospitalEndTime 都传才生效」，
--        只传 hospitalStartTime 时接口其实【完全不过滤出院时间】。下面按你要的语义实现：
--        出院时间 >= hospitalStartTime（上限可选）。
--
-- 已在 MySQL 5.7.34（smartor_sltd）实跑验证：
--   hospitalStartTime = '2026-10-01' → 45 个病区 / 3088 条出院记录，
--   抽查病区 40003052：287 条、无需随访 4、应随访 283、首次随访成功 250、随访率 88.34%，与明细逐条核对一致。
-- =============================================================================


-- -----------------------------------------------------------------------------
-- PART 0. 参数（只有第一个是必填）
-- -----------------------------------------------------------------------------
SET @hospitalStartTime = '2026-10-01';   -- 必填：出院时间下限（当天 0 点起）
SET @hospitalEndTime   = NULL;           -- 可选：出院时间上限，NULL = 不限
SET @followUpCountStyle = '1';           -- 系统配置 followUpCountStyle；本库未配置该参数，接口按 '1' 处理
SET @orgid    = NULL;                    -- 可选：机构；NULL = 不限（接口里由登录信息注入）
SET @campusid = NULL;                    -- 可选：院区；NULL = 不限


-- -----------------------------------------------------------------------------
-- PART 1. 主查询：一行 = 一个出院病区，覆盖全部病区
--   · 内层 r   ：一行 = 一条随访子任务，把「统计口径」翻译成 0/1 标记
--   · 中间 t   ：按病区把标记累加
--   · 最外层   ：把累加值算成接口返回的比率字符串
--   想改统计维度：把 GROUP BY r.ward_code 换成 r.dept_code（科室）或 r.dr_code（医生）即可。
--   想要总计行：把「GROUP BY r.ward_code」改成「GROUP BY r.ward_code WITH ROLLUP」，
--               最后一行 ward_code 为 NULL 的就是全部病区合计。
-- -----------------------------------------------------------------------------
SELECT
  t.leavehospitaldistrictcode,
  t.leavehospitaldistrictname,
  t.deptcode,
  t.deptname,
  t.drcode,
  t.drname,
  t.dischargeCount,            -- 出院人次
  t.nonFollowUp,               -- 无需随访人次
  t.filterCount,               -- 过滤患者数量
  t.followUpNeeded,            -- 应随访人次
  t.needFollowUp,              -- 首次应随访
  t.nonFollowUpFirst,          -- 首次无需随访
  t.pendingFollowUp,           -- 首次待随访（本接口此路径恒为 0，见 PART 5）
  t.followUpSuccess,           -- 首次随访成功
  t.followUpFail,              -- 首次随访失败（含人工超时）
  t.manual,                    -- 首次人工随访
  t.voice,                     -- 首次语音随访
  t.sms,                       -- 首次短信随访
  t.weChat,                    -- 首次微信随访
  t.manualSuccess,             -- 首次人工随访成功
  t.voiceSuccess,              -- 首次语音随访成功
  t.smsSuccess,                -- 首次短信随访成功
  t.weChatSuccess,             -- 首次微信随访成功
  -- 首次随访率 =（首次随访成功 + 首次随访失败）/ 首次应随访
  IF(t.needFollowUp = 0, '0.00%',
     CONCAT(ROUND(100 * (t.followUpSuccess + t.followUpFail) / t.needFollowUp, 2), '%')) AS followUpRate,
  t.abnormal,                  -- 结果异常（首次）
  t.needFollowUpAgain,         -- 二次应随访
  t.nonFollowUpAgain,          -- 二次无需随访
  t.pendingFollowUpAgain,      -- 二次待随访
  t.followUpSuccessAgain,      -- 二次随访成功
  t.followUpFailAgain,         -- 二次随访失败
  t.manualAgain,
  t.voiceAgain,
  t.smsAgain,
  t.weChatAgain,
  t.manualAgainSuccess,
  t.voiceAgainSuccess,
  t.smsAgainSuccess,
  t.weChatAgainSuccess,
  -- 二次随访率
  IF(t.needFollowUpAgain = 0, '0.00%',
     CONCAT(ROUND(100 * (t.followUpSuccessAgain + t.followUpFailAgain) / t.needFollowUpAgain, 2), '%')) AS followUpRateAgain,
  t.abnormalAgain,             -- 结果异常（再次）
  t.taskSituation1,            -- 随访情况：正常语音
  t.taskSituation2,            -- 随访情况：患者拒接/拒访
  t.taskSituation3,            -- 随访情况：面访/接诊
  t.taskSituation4,            -- 随访情况：微信随访
  t.taskSituation5,            -- 随访情况：随访电话不正确
  t.taskSituation6            -- 随访情况：其他（task_situation = 7）
FROM (
  SELECT
    r.ward_code                                                             AS leavehospitaldistrictcode,
    MAX(r.ward_name)                                                        AS leavehospitaldistrictname,
    MAX(r.dept_code)                                                        AS deptcode,
    MAX(r.dept_name)                                                        AS deptname,
    MAX(r.dr_code)                                                          AS drcode,
    MAX(r.dr_name)                                                          AS drname,
    COUNT(*)                                                                AS dischargeCount,
    SUM(r.f_no)                                                             AS nonFollowUp,
    SUM(r.f_filter)                                                         AS filterCount,
    SUM(r.f_need)                                                           AS followUpNeeded,
    SUM(IF(r.f_first = 1 AND r.f_need = 1, 1, 0))                           AS needFollowUp,
    -- followUpCountStyle='1' 时接口把「首次无需随访」加了两次（代码 L2195-2197 与 L2211-2213），这里 *2 复刻
    SUM(IF(r.f_first = 1 AND r.f_no = 1, 1, 0)) * IF(@followUpCountStyle = '1', 2, 1) AS nonFollowUpFirst,
    0                                                                       AS pendingFollowUp,
    SUM(IF(r.f_first = 1 AND r.f_succ_first = 1, 1, 0))                     AS followUpSuccess,
    SUM(IF(r.f_first = 1 AND r.f_fail_first = 1, 1, 0))                     AS followUpFail,
    SUM(IF(r.f_first = 1 AND r.ch_hit = 1 AND r.cp = '1', 1, 0))            AS manual,
    SUM(IF(r.f_first = 1 AND r.ch_hit = 1 AND r.cp = '3', 1, 0))            AS voice,
    SUM(IF(r.f_first = 1 AND r.ch_hit = 1 AND r.cp = '4', 1, 0))            AS sms,
    SUM(IF(r.f_first = 1 AND r.ch_hit = 1 AND r.cp = '5', 1, 0))            AS weChat,
    SUM(IF(r.f_first = 1 AND r.sendstate = 6 AND r.cp = '1', 1, 0))         AS manualSuccess,
    SUM(IF(r.f_first = 1 AND r.sendstate = 6 AND r.cp = '3', 1, 0))         AS voiceSuccess,
    SUM(IF(r.f_first = 1 AND r.sendstate = 6 AND r.cp = '4', 1, 0))         AS smsSuccess,
    SUM(IF(r.f_first = 1 AND r.sendstate = 6 AND r.cp = '5', 1, 0))         AS weChatSuccess,
    SUM(IF(r.f_first = 1 AND r.f_abnormal = 1, 1, 0))                       AS abnormal,
    SUM(IF(r.f_again = 1 AND r.f_need = 1, 1, 0))                           AS needFollowUpAgain,
    SUM(IF(r.f_again = 1 AND r.f_no = 1, 1, 0))                             AS nonFollowUpAgain,
    SUM(IF(r.f_again = 1 AND r.sendstate = 2, 1, 0))                        AS pendingFollowUpAgain,
    SUM(IF(r.f_again = 1 AND r.f_succ_again = 1, 1, 0))                     AS followUpSuccessAgain,
    SUM(IF(r.f_again = 1 AND r.f_fail_again = 1, 1, 0))                     AS followUpFailAgain,
    SUM(IF(r.f_again = 1 AND r.ch_hit = 1 AND r.cp = '1', 1, 0))            AS manualAgain,
    SUM(IF(r.f_again = 1 AND r.ch_hit = 1 AND r.cp = '3', 1, 0))            AS voiceAgain,
    SUM(IF(r.f_again = 1 AND r.ch_hit = 1 AND r.cp = '4', 1, 0))            AS smsAgain,
    SUM(IF(r.f_again = 1 AND r.ch_hit = 1 AND r.cp = '5', 1, 0))            AS weChatAgain,
    SUM(IF(r.f_again = 1 AND r.sendstate = 6 AND r.cp = '1', 1, 0))         AS manualAgainSuccess,
    SUM(IF(r.f_again = 1 AND r.sendstate = 6 AND r.cp = '3', 1, 0))         AS voiceAgainSuccess,
    SUM(IF(r.f_again = 1 AND r.sendstate = 6 AND r.cp = '4', 1, 0))         AS smsAgainSuccess,
    SUM(IF(r.f_again = 1 AND r.sendstate = 6 AND r.cp = '5', 1, 0))         AS weChatAgainSuccess,
    SUM(IF(r.f_again = 1 AND r.f_abnormal = 1, 1, 0))                       AS abnormalAgain,
    SUM(IF(r.task_situation = 1, 1, 0))                                     AS taskSituation1,
    SUM(IF(r.task_situation = 2, 1, 0))                                     AS taskSituation2,
    SUM(IF(r.task_situation = 3, 1, 0))                                     AS taskSituation3,
    SUM(IF(r.task_situation = 4, 1, 0))                                     AS taskSituation4,
    SUM(IF(r.task_situation = 5, 1, 0))                                     AS taskSituation5,
    SUM(IF(r.task_situation = 7, 1, 0))                                     AS taskSituation6
  FROM (
    SELECT
      ss.leavehospitaldistrictcode                            AS ward_code,
      ss.leavehospitaldistrictname                            AS ward_name,
      ss.leaveldeptcode                                       AS dept_code,
      ss.leaveldeptname                                       AS dept_name,
      ss.drcode                                               AS dr_code,
      ss.drname                                               AS dr_name,
      ss.sendstate                                            AS sendstate,
      ss.current_preachform                                   AS cp,
      ss.task_situation                                       AS task_situation,
      IF(ss.sendstate = 4, 1, 0)                              AS f_no,
      IF(ss.sendstate = 4 AND ss.task_situation = 6, 1, 0)    AS f_filter,
      IF(ss.sendstate IS NOT NULL AND ss.sendstate <> 4, 1, 0) AS f_need,
      IF(ss.visit_count = 1, 1, 0)                            AS f_first,
      IF(ss.visit_count > 1, 1, 0)                            AS f_again,
      -- 首次随访成功：统计方式 1/3 只算已完成(6)；其它方式算上不执行/已发送(3,4,6)
      IF(@followUpCountStyle IN ('1', '3'),
         IF(ss.sendstate = 6, 1, 0),
         IF(ss.sendstate IN (3, 4, 6), 1, 0))                 AS f_succ_first,
      -- 再次随访成功：统计方式 1/3 只算已完成(6)；其它方式算上待随访失败前的 1 等(1,3,4,6)
      IF(@followUpCountStyle IN ('1', '3'),
         IF(ss.sendstate = 6, 1, 0),
         IF(ss.sendstate IN (1, 3, 4, 6), 1, 0))              AS f_succ_again,
      IF(ss.sendstate IN (5, 7), 1, 0)                        AS f_fail_first,
      IF(ss.sendstate = 5, 1, 0)                              AS f_fail_again,
      IF(ss.excep = '1', 1, 0)                                AS f_abnormal,
      -- 随访方式命中条件：方式 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'                              -- serviceType = 2 出院随访
      -- 接口里 groupKeyList 会带全量病区码，等价于「出院病区为 NULL 的行被丢掉」；
      -- 若想把空病区也统计进来，注释掉下一行。
      AND ss.leavehospitaldistrictcode IS NOT NULL
      AND ss.endtime >= DATE(@hospitalStartTime)
      AND (@hospitalEndTime IS NULL OR ss.endtime < DATE_ADD(DATE(@hospitalEndTime), INTERVAL 1 DAY))
      AND (@orgid    IS NULL OR ss.orgid    = @orgid)
      AND (@campusid IS NULL OR ss.campusid = @campusid)
  ) r
  GROUP BY r.ward_code
) t
ORDER BY t.dischargeCount DESC;


-- -----------------------------------------------------------------------------
-- PART 1B. 只要「首次随访」的数据（等价于接口入参 visitCount = 1 → getStatisticFirst）
--   · 只统计 visit_count = 1 的记录：二次随访记录不进入任何一列
--   · 列：出院人次 / 应随访拆解 / 随访成功拆解 / 各成功率 / 及时率 / 出院时间范围
--   · 口径与 getStatisticFirst 对齐：pendingFollowUp 是 sendstate = 2 的真实数量
--
--   ★ 对账：接口这几列本身不等于「应随访」，差额是两个真实存在的口径缺项，不是数据错：
--      首次应随访 = 待发送(1) + 待随访(2) + 已发送未完成(3) + 其它状态 + 随访成功(6) + 随访失败(5,7)
--      首次随访成功 = 人工成功 + 语音成功 + 短信成功 + 微信成功 + 无方式成功(sendstate=6 但 current_preachform 为空)
--     接口只统计 2 / 6 / 5+7，所以「待随访 + 成功 + 失败」少于应随访；
--     渠道成功按 current_preachform 分，没有方式的成功记录不落任何渠道。
--     下面把 notSent / sentNotDone / otherStatus / noChannelSuccess 显式列出来，并给出 diffNeed / diffSucc
--     两个自检列（正常恒为 0）。
--
--   ★ 出院时间：主查询给 endTimeMin/endTimeMax，及时率口径给 timelyEndMin/timelyEndMax。
--     ⚠ 及时率子查询里的出院时间条件当前是注释掉的（按你的版本），所以它统计的是该病区「全部历史」，
--       分母 timelyBase 会远大于应随访（实测某病区 643 条应随访 vs 2342 条及时率分母），
--       timelyRate 与其它列不再同区间、不可直接比较。要恢复同区间就把那两行放开。
--
--   主查询已实跑校验（2026-09-04 ~ 2026-11-03，orgid=20001001，45 个病区）：7378 条 / 无需随访 428 /
--   应随访 6950 = 待发送 116 + 待随访 337 + 已发送未完成 941 + 成功 4188 + 失败 1368；
--   成功 4188 = 人工 2974 + 短信 1197 + 无方式 17（本库 current_preachform 只有 1 和 4，语音/微信为 0）。
-- -----------------------------------------------------------------------------
SELECT
  t.leavehospitaldistrictname,   -- 出院病区
  t.endTimeMin,                  -- 该病区命中的最早出院时间
  t.endTimeMax,                  -- 该病区命中的最晚出院时间
  t.deptname,                    -- 出院科室
  t.dischargeCount,              -- 出院人次（首次随访记录数）
  t.nonFollowUp,                 -- 无需随访人次
  t.followUpNeeded,              -- 应随访人次
  t.needFollowUp,                -- 首次应随访
  t.notSent,                     -- 待发送（sendstate = 1）
  t.pendingFollowUp,             -- 首次待随访（sendstate = 2）
  t.sentNotDone,                 -- 已发送未完成（sendstate = 3）
  t.otherStatus,                 -- 其它状态兜底（正常为 0）
  t.followUpSuccess,             -- 首次随访成功（sendstate = 6）
  t.followUpFail,                -- 首次随访失败（sendstate = 5,7，含人工超时）
  -- 各随访方式成功数（想同时看总数就放开下面 4 行，成功率分母就是这 4 个）
  -- t.manual,  t.voice,  t.sms,  t.weChat,
  t.manualSuccess,               -- 首次人工随访成功
  t.voiceSuccess,                -- 首次语音随访成功
  t.smsSuccess,                  -- 首次短信随访成功
  t.weChatSuccess,               -- 首次微信随访成功
  t.noChannelSuccess,            -- 成功但没有随访方式（current_preachform 为空或不属于 1/3/4/5）
  -- 随访率 =（成功 + 失败）/ 应随访（接口 followUpRate 的口径）
  IF(t.needFollowUp = 0, '0.00%',
     CONCAT(ROUND(100 * (t.followUpSuccess + t.followUpFail) / t.needFollowUp, 2), '%')) AS followUpRate,
  -- 随访成功率 = 成功 / 应随访（只算成功，不含失败）
  IF(t.needFollowUp = 0, '0.00%',
     CONCAT(ROUND(100 * t.followUpSuccess / t.needFollowUp, 2), '%')) AS followUpSuccessRate,
  -- 各方式成功率 = 该方式成功 / 该方式命中（sendstate<>4 且 current_preachform 为该方式）
  IF(t.manual = 0, '0.00%', CONCAT(ROUND(100 * t.manualSuccess / t.manual, 2), '%')) AS manualSuccessRate,
  IF(t.voice  = 0, '0.00%', CONCAT(ROUND(100 * t.voiceSuccess  / t.voice,  2), '%')) AS voiceSuccessRate,
  IF(t.sms    = 0, '0.00%', CONCAT(ROUND(100 * t.smsSuccess    / t.sms,    2), '%')) AS smsSuccessRate,
  IF(t.weChat = 0, '0.00%', CONCAT(ROUND(100 * t.weChatSuccess / t.weChat, 2), '%')) AS weChatSuccessRate,
  -- 渠道合计成功率 = 四种方式成功之和 / 四种方式命中之和
  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,
  -- 及时率：口径与接口 selectTimelyRateBatch 相同（独立的随访时间窗口 + 排除「无需随访」），
  -- 及时 = finishtime 不为空 且 DATEDIFF(finishtime, endtime) <= 及时天数 + 补偿天数。
  -- 及时天数阈值见下面 rt 子查询里的 7，按需修改（接口不传 rateDay 时返回 0）。
  IFNULL(rt.timely_base, 0) AS timelyBase,   -- 及时率分母（口径与「应随访」不同）
  IF(IFNULL(rt.timely_base, 0) = 0, '0.00%',
     CONCAT(ROUND(100 * rt.timely_cnt / rt.timely_base, 2), '%')) AS timelyRate,
  rt.timelyEndMin,               -- 及时率口径命中的最早出院时间
  rt.timelyEndMax,               -- 及时率口径命中的最晚出院时间
  -- 自检：两个差额，正常恒为 0
  t.needFollowUp - (t.notSent + t.pendingFollowUp + t.sentNotDone + t.otherStatus
                    + t.followUpSuccess + t.followUpFail) AS diffNeed,
  t.followUpSuccess - (t.manualSuccess + t.voiceSuccess + t.smsSuccess + t.weChatSuccess
                       + t.noChannelSuccess) AS diffSucc
  -- 另外几列需要时放开：
  -- , t.abnormal
  -- , t.taskSituation1, t.taskSituation2, t.taskSituation3, t.taskSituation4, t.taskSituation5, t.taskSituation6
FROM (
  SELECT
    r.ward_code                                    AS leavehospitaldistrictcode,
    MAX(r.ward_name)                               AS leavehospitaldistrictname,
    MAX(r.dept_name)                               AS deptname,
    MIN(r.end_time)                                AS endTimeMin,
    MAX(r.end_time)                                AS endTimeMax,
    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 = 1, 1, 0))                 AS notSent,
    SUM(IF(r.sendstate = 2, 1, 0))                 AS pendingFollowUp,
    SUM(IF(r.sendstate = 3, 1, 0))                 AS sentNotDone,
    SUM(IF(r.sendstate IS NOT NULL AND r.sendstate <> 4
           AND r.sendstate NOT IN (1, 2, 3, 5, 6, 7), 1, 0)) AS otherStatus,
    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,
    SUM(IF(r.sendstate = 6 AND (r.cp IS NULL OR r.cp NOT IN ('1','3','4','5')), 1, 0)) AS noChannelSuccess
  FROM (
    SELECT
      ss.leavehospitaldistrictcode                            AS ward_code,
      ss.leavehospitaldistrictname                            AS ward_name,
      ss.leaveldeptname                                       AS dept_name,
      ss.endtime                                              AS end_time,
      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('2026-09-04 00:00:00')
      AND ss.endtime < DATE_ADD(DATE('2026-11-03 23:59:59'), INTERVAL 1 DAY)
      AND ss.orgid = '20001001'
  ) r
  GROUP BY r.ward_code
) t
LEFT JOIN (
  -- 及时率（selectTimelyRateBatch 的等价写法，按病区预聚合一次再关联，避免一对多放大分母）
  SELECT
    ss.leavehospitaldistrictcode AS ward_code,
    COUNT(*) AS timely_base,
    SUM(IF(ss.finishtime IS NOT NULL
           AND DATEDIFF(ss.finishtime, ss.endtime) <= 7 + IFNULL(cp.compensate_days, 0), 1, 0)) AS timely_cnt,
    MIN(ss.endtime) AS timelyEndMin,     -- 这一口径实际覆盖到的出院时间范围
    MAX(ss.endtime) AS timelyEndMax
  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 时的固定条件
    -- 及时率要不要跟主查询同区间？放开下面两行即可（放开后 timelyBase 才会等于应随访）
    -- AND ss.endtime >= DATE('2026-09-04 00:00:00')
    -- AND ss.endtime < DATE_ADD(DATE('2026-11-03 23:59:59'), INTERVAL 1 DAY)
    AND ss.orgid = '20001001'
  GROUP BY ss.leavehospitaldistrictcode
) rt ON rt.ward_code = t.leavehospitaldistrictcode
ORDER BY t.dischargeCount DESC;


-- -----------------------------------------------------------------------------
-- PART 1C. 想看「出院时间」这一列：按 病区 + 出院日期(DATE(endtime)) 分组
--   为什么不能只在 PART 1B 里 select ss.endtime 再 GROUP BY ward_code：
--     · ONLY_FULL_GROUP_BY 下会报 1055；
--     · 把带秒的 endtime 加进 GROUP BY = 一条记录一组，7378 条被拆成 7133 行，汇总没有意义；
--     · 更坑的是及时率子查询仍按病区聚合，整病区的 timelyBase 会被重复挂到每一行
--       （实测 timelyBase 合计会变成 9,649,708）。这里 rt 也按「病区 + 出院日期」聚合，
--       所以每行的及时率和该行数据是同一个粒度。
--   实测：1637 行 / dischargeCount 合计 7378 / timelyBase 合计 6950 / diffNeed、diffSucc 全 0。
-- -----------------------------------------------------------------------------
SELECT
  t.end_date                     AS 出院日期,      -- 出院时间（按天）
  t.leavehospitaldistrictname    AS 出院病区,
  t.deptname                     AS 出院科室,
  t.dischargeCount, t.nonFollowUp, t.followUpNeeded, t.needFollowUp,
  t.notSent, t.pendingFollowUp, t.sentNotDone, t.otherStatus,
  t.followUpSuccess, t.followUpFail,
  t.manualSuccess, t.voiceSuccess, t.smsSuccess, t.weChatSuccess, t.noChannelSuccess,
  IF(t.needFollowUp = 0, '0.00%',
     CONCAT(ROUND(100 * (t.followUpSuccess + t.followUpFail) / t.needFollowUp, 2), '%')) AS followUpRate,
  IF(t.needFollowUp = 0, '0.00%',
     CONCAT(ROUND(100 * t.followUpSuccess / t.needFollowUp, 2), '%')) AS followUpSuccessRate,
  IF(t.manual = 0, '0.00%', CONCAT(ROUND(100 * t.manualSuccess / t.manual, 2), '%')) AS manualSuccessRate,
  IF(t.voice  = 0, '0.00%', CONCAT(ROUND(100 * t.voiceSuccess  / t.voice,  2), '%')) AS voiceSuccessRate,
  IF(t.sms    = 0, '0.00%', CONCAT(ROUND(100 * t.smsSuccess    / t.sms,    2), '%')) AS smsSuccessRate,
  IF(t.weChat = 0, '0.00%', CONCAT(ROUND(100 * t.weChatSuccess / t.weChat, 2), '%')) AS weChatSuccessRate,
  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,
  IFNULL(rt.timely_base, 0) AS timelyBase,
  IF(IFNULL(rt.timely_base, 0) = 0, '0.00%',
     CONCAT(ROUND(100 * rt.timely_cnt / rt.timely_base, 2), '%')) AS timelyRate,
  t.needFollowUp - (t.notSent + t.pendingFollowUp + t.sentNotDone + t.otherStatus
                    + t.followUpSuccess + t.followUpFail) AS diffNeed,
  t.followUpSuccess - (t.manualSuccess + t.voiceSuccess + t.smsSuccess + t.weChatSuccess
                       + t.noChannelSuccess) AS diffSucc
FROM (
  SELECT
    r.ward_code                                    AS leavehospitaldistrictcode,
    r.end_date                                     AS end_date,
    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 = 1, 1, 0))                 AS notSent,
    SUM(IF(r.sendstate = 2, 1, 0))                 AS pendingFollowUp,
    SUM(IF(r.sendstate = 3, 1, 0))                 AS sentNotDone,
    SUM(IF(r.sendstate IS NOT NULL AND r.sendstate <> 4
           AND r.sendstate NOT IN (1, 2, 3, 5, 6, 7), 1, 0)) AS otherStatus,
    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,
    SUM(IF(r.sendstate = 6 AND (r.cp IS NULL OR r.cp NOT IN ('1','3','4','5')), 1, 0)) AS noChannelSuccess
  FROM (
    SELECT
      ss.leavehospitaldistrictcode              AS ward_code,
      DATE(ss.endtime)                          AS end_date,
      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,
      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('2026-09-04 00:00:00')
      AND ss.endtime < DATE_ADD(DATE('2026-11-03 23:59:59'), INTERVAL 1 DAY)
      AND ss.orgid = '20001001'
  ) r
  GROUP BY r.ward_code, r.end_date
) t
LEFT JOIN (
  -- 及时率：同样按「病区 + 出院日期」聚合，才能和左边每一行对上
  SELECT
    ss.leavehospitaldistrictcode AS ward_code,
    DATE(ss.endtime)             AS end_date,
    COUNT(*)                     AS timely_base,
    SUM(IF(ss.finishtime IS NOT NULL
           AND DATEDIFF(ss.finishtime, ss.endtime) <= 7 + IFNULL(cp.compensate_days, 0), 1, 0)) AS timely_cnt
  FROM service_subtask ss
  LEFT JOIN (
    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())
    AND ss.orgid = '20001001'
  GROUP BY ss.leavehospitaldistrictcode, DATE(ss.endtime)
) rt ON rt.ward_code = t.leavehospitaldistrictcode AND rt.end_date = t.end_date
ORDER BY t.leavehospitaldistrictname, t.end_date;


-- -----------------------------------------------------------------------------
-- PART 1D. 明细版：一行 = 一条首次随访记录，直接带出院时间（要核对到人/到天就跑这个）
-- -----------------------------------------------------------------------------
SELECT
  ss.endtime                                              AS 出院时间,
  ss.leavehospitaldistrictname                            AS 出院病区,
  ss.leaveldeptname                                       AS 出院科室,
  ss.sendname                                             AS 患者,
  ss.sendstate                                            AS 状态码,
  CASE ss.sendstate
    WHEN 1 THEN '待发送' WHEN 2 THEN '待随访' WHEN 3 THEN '已发送未完成'
    WHEN 4 THEN '无需随访' WHEN 5 THEN '随访失败' WHEN 6 THEN '随访成功'
    WHEN 7 THEN '失败/人工超时' ELSE '其它'
  END                                                     AS 随访状态,
  ss.current_preachform                                   AS 随访方式,
  ss.visit_count                                          AS 随访次数,
  ss.visit_time                                           AS 随访时间,
  ss.finishtime                                           AS 完成时间,
  IF(ss.sendstate = 4 OR ss.visit_time IS NULL, '-',
     IF(ss.finishtime IS NOT NULL AND DATEDIFF(ss.finishtime, ss.endtime) <= 7, '及时', '不及时')) AS 是否及时
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('2026-09-04 00:00:00')
  AND ss.endtime < DATE_ADD(DATE('2026-11-03 23:59:59'), INTERVAL 1 DAY)
  AND ss.orgid = '20001001'
ORDER BY ss.leavehospitaldistrictname, ss.endtime;


-- -----------------------------------------------------------------------------
-- PART 2. 及时率 rate（接口只在请求里传了 rateDay 时才有值，不传返回 0.0）
--   口径与接口不同：这一块用的是「随访时间(visit_time)」窗口，分母是 visit_time 不为空且
--   不是「无需随访」的记录数；出院时间只作为附加条件。
--   rate = 及时数 / 分母，及时 = finishtime 不为空 且 DATEDIFF(finishtime, endtime) <= rateDay + 补偿天数
-- -----------------------------------------------------------------------------
SET @rateDay = 7;   -- 几天及时率；不想看就整段不跑

SELECT
  ss.leavehospitaldistrictcode                                                        AS leavehospitaldistrictcode,
  COUNT(*)                                                                            AS base,   -- 分母
  SUM(IF(ss.finishtime IS NOT NULL
         AND DATEDIFF(ss.finishtime, ss.endtime) <= @rateDay + IFNULL(cp.compensate_days, 0), 1, 0)) AS timely, -- 及时数
  ROUND(SUM(IF(ss.finishtime IS NOT NULL
               AND DATEDIFF(ss.finishtime, ss.endtime) <= @rateDay + IFNULL(cp.compensate_days, 0), 1, 0))
        / COUNT(*), 4)                                                                AS rate
FROM service_subtask ss
LEFT JOIN (   -- 补偿时间：先按子任务聚成一行再关联，避免一对多把分母放大
  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_time IS NOT NULL
  AND ss.sendstate <> 4
  AND DATE(ss.visit_time) < DATE(NOW())        -- 接口未传 startTime/endTime 时的固定条件
  AND ss.endtime >= DATE(@hospitalStartTime)   -- 等价于接口的 date_format(endtime,'%y%m%d') >= date_format(...)
  AND (@orgid    IS NULL OR ss.orgid    = @orgid)
  AND (@campusid IS NULL OR ss.campusid = @campusid)
GROUP BY ss.leavehospitaldistrictcode
ORDER BY base DESC;


-- -----------------------------------------------------------------------------
-- PART 3. 想固定成「视图」的话用这个（MySQL 的视图不支持传入参数，
--         所以视图按「一条子任务一行」落明细 + 出院日期，参数过滤放在查询外层）
--   注意：视图里把 followUpCountStyle 固定成 '1'（本库未配置该参数，接口实际就走 '1'）。
--         若某机构配的是 '3' 或其它值，改视图里的 f_succ_first / f_succ_again / ch_hit 三处。
-- -----------------------------------------------------------------------------
CREATE OR REPLACE VIEW v_sf_statistic_flag AS
SELECT
  ss.id,
  DATE(ss.endtime)                                        AS stat_date,     -- 出院日期（hospitalStartTime 过滤用）
  ss.leavehospitaldistrictcode                            AS ward_code,
  ss.leavehospitaldistrictname                            AS ward_name,
  ss.leaveldeptcode                                       AS dept_code,
  ss.leaveldeptname                                       AS dept_name,
  ss.drcode                                               AS dr_code,
  ss.drname                                               AS dr_name,
  ss.endtime                                              AS end_time,
  ss.visit_time,
  ss.finishtime,
  ss.sendstate,
  ss.visit_count,
  ss.current_preachform                                   AS cp,
  ss.task_situation,
  IF(ss.sendstate = 4, 1, 0)                              AS f_no,
  IF(ss.sendstate = 4 AND ss.task_situation = 6, 1, 0)    AS f_filter,
  IF(ss.sendstate IS NOT NULL AND ss.sendstate <> 4, 1, 0) AS f_need,
  IF(ss.visit_count = 1, 1, 0)                            AS f_first,
  IF(ss.visit_count > 1, 1, 0)                            AS f_again,
  IF(ss.sendstate = 6, 1, 0)                              AS f_succ_first,   -- style '1'/'3'
  IF(ss.sendstate = 6, 1, 0)                              AS f_succ_again,   -- style '1'/'3'
  IF(ss.sendstate IN (5, 7), 1, 0)                        AS f_fail_first,
  IF(ss.sendstate = 5, 1, 0)                              AS f_fail_again,
  IF(ss.excep = '1', 1, 0)                                AS f_abnormal,
  IF(ss.sendstate IS NOT NULL AND ss.sendstate <> 4, 1, 0) AS ch_hit         -- style '1'
FROM service_subtask ss
WHERE ss.del_flag = 0
  AND ss.service_type = '2';

-- 建好视图后，把 PART 1 里内层 "FROM ( ... ) r" 整段换成下面这一行即可（其余不动）：
--   FROM v_sf_statistic_flag r
--   WHERE r.stat_date >= DATE(@hospitalStartTime)
--     AND (@hospitalEndTime IS NULL OR r.end_time < DATE_ADD(DATE(@hospitalEndTime), INTERVAL 1 DAY))
--     AND (@orgid IS NULL OR 1 = 1)   -- 视图中未带 orgid/campusid，需要的话把它们也加进视图
-- 同时把中间层 nonFollowUpFirst 的 "* IF(@followUpCountStyle = '1', 2, 1)" 改成 "* 2"（视图里已是 style '1'）。


-- -----------------------------------------------------------------------------
-- PART 4. 口径说明 / 与接口的差异
-- -----------------------------------------------------------------------------
-- 1) 接口返回里恒为 0、本次未输出的字段（代码这条路径没赋值）：
--    serviceCount、bzxCount、ysfCount、yfsCount、dfsCount、fssbCount、ywcCount、
--    dxfscs、dxsfwccs、dhfscs、dhsfwccs、yczs、hfycs、finishRate、joyCount、joyAllCount、joyTotal。
--    pendingFollowUp（首次待随访）同理：getStatistic 的“不分次数”分支里没有累加，恒为 0。
-- 2) nonFollowUpFirst 在 followUpCountStyle='1' 时接口会重复累加一次（两个 if 都命中 sendstate=4），
--    SQL 用 *2 复刻。若按“一条记录算一次”应为 1 倍，把 IF(@followUpCountStyle='1',2,1) 改成 1。
-- 3) 比率格式：接口用 DecimalFormat("##.##%")（四舍六入、末尾 0 省略，如 85.5%），
--    SQL 用 ROUND(...,2)（四舍五入、固定两位，如 85.50%）；分母为 0 时接口给 "0.00%"。
-- 4) deptcode/deptname/drcode/drname：接口取「分组内第一条记录」的值（HashMap 顺序，无业务含义），
--    SQL 用 MAX() 代替；按病区统计时这四列只作参考。
-- 5) 随访情况 taskSituation1~6：task_situation = 1~5 分别对应 1~5，其他(=7) 对应 6。
--    本库 service_subtask.task_situation 全为 NULL，所以这几列目前必然都是 0。
-- 6) 只看首次/再次：接口传 visitCount=1 只看首次、>1 只看再次；本 SQL 两类同时给，均为 0 说明该窗口没有对应数据。
-- 7) 换维度：GROUP BY r.ward_code（病区，本 SQL）→ r.dept_code（科室）/ r.dr_code（医生）。
--    统计方式、及时率天数都通过 PART 0 的变量控制，不需要改结构。
-- =============================================================================