-- =============================================================================
-- 病区出院随访统计 · 可传参视图
-- =============================================================================
-- 为什么是「参数表 + 视图」而不是 @变量：
--   MySQL 视图定义里禁止引用 @用户变量 / 系统变量 / 存储过程参数，
--   否则创建时报 ERROR 1351 (HY000): View's SELECT contains a variable or parameter。
--   官方原文：「The SELECT statement cannot refer to system variables or
--   user-defined variables.」
--   https://dev.mysql.com/doc/refman/8.0/en/create-view.html
--
-- 所以用单行参数表 report_param 承载参数：
--   1) REPLACE INTO report_param ... 改第 1 行（起止时间 / 口径 / 及时天数）
--   2) SELECT * FROM v_ward_followup_stats;
--
-- 并发提醒：参数表是共享状态，两个会话同时改会互相串。多用户并发请改用
--          同目录的 p_ward_followup_stats.sql（存储过程，真正按调用传参）。
-- =============================================================================

-- ---------------------------------------------------------------------------
-- 1. 参数表（永远只有 id = 1 这一行）
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS report_param (
  id          TINYINT    NOT NULL                COMMENT '固定为 1',
  start_time  DATETIME   NOT NULL                COMMENT '出院时间起（含），传 yyyy-MM-dd 或 yyyy-MM-dd HH:mm:ss 均可',
  end_time    DATETIME   NOT NULL                COMMENT '出院时间止（含当天 23:59:59），传 yyyy-MM-dd 即可',
  count_style VARCHAR(2) NOT NULL DEFAULT '1'    COMMENT '随访方式命中口径：1=应随访 3=仅成功 其它=全部',
  timely_days INT        NOT NULL DEFAULT 7      COMMENT '及时率的天数基数（补偿天数另加）',
  PRIMARY KEY (id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '报表参数（单行，id 恒为 1）';

-- ---------------------------------------------------------------------------
-- 2. 视图
-- ---------------------------------------------------------------------------
CREATE OR REPLACE VIEW v_ward_followup_stats AS
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(p.count_style = '1',
         IF(ss.sendstate IS NOT NULL AND ss.sendstate <> 4, 1, 0),
         IF(p.count_style = '3',
            IF(ss.sendstate = 6, 1, 0),
            IF(ss.sendstate IS NOT NULL, 1, 0)))                AS ch_hit
    FROM service_subtask ss
    JOIN report_param p ON p.id = 1
    WHERE ss.del_flag = 0
      AND ss.service_type = '2'
      AND ss.visit_count = 1                                  -- 只要首次随访
      AND ss.leavehospitaldistrictcode IS NOT NULL
      -- >>> 可传参的时间范围：左闭右开，end_time 传日期即可含当天全天 <<<
      AND ss.endtime >= DATE(p.start_time)
      AND ss.endtime <  DATE_ADD(DATE(p.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) <= p.timely_days + IFNULL(cp.compensate_days, 0),
           1, 0)) AS timely_cnt
  FROM service_subtask ss
  JOIN report_param p ON p.id = 1
  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(p.start_time)
    AND ss.endtime <  DATE_ADD(DATE(p.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;

-- ---------------------------------------------------------------------------
-- 3. 用法
-- ---------------------------------------------------------------------------
-- 改参数（只改这一行，两个日期就是原来的 endtime 起止）
REPLACE INTO report_param (id, start_time, end_time, count_style, timely_days)
VALUES (1, '2026-10-04', '2026-11-03', '1', 7);

-- 查数
SELECT *
FROM v_ward_followup_stats
-- WHERE leavehospitaldistrictname LIKE '%骨科%'   -- 视图外照样能再加条件
;