From f12b3e4c7c8d55217cc1d21f814a9a3d5fc57251 Mon Sep 17 00:00:00 2001
From: liusheng <337615773@qq.com>
Date: 星期二, 03 十一月 2026 16:02:21 +0800
Subject: [PATCH] 提交Merge branch 'subtask' of http://116.62.18.175:6699/r/~yxh/smartor-web into subtask
---
sql/v_ward_followup_stats.sql | 152 ++++++++++++++++
src/api/smartor/pendingTask.js | 2
sql/ward_followup_stats_query.sql | 114 ++++++++++++
src/views/followvisit/discharge/outpatientService.vue | 48 +++-
sql/p_ward_followup_stats.sql | 146 ++++++++++++++++
src/views/followvisit/discharge/index.vue | 48 +++-
6 files changed, 481 insertions(+), 29 deletions(-)
diff --git a/sql/p_ward_followup_stats.sql b/sql/p_ward_followup_stats.sql
new file mode 100644
index 0000000..33cc5fc
--- /dev/null
+++ b/sql/p_ward_followup_stats.sql
@@ -0,0 +1,146 @@
+-- =============================================================================
+-- 鐥呭尯鍑洪櫌闅忚缁熻 路 瀛樺偍杩囩▼锛堟帹鑽愶紝鐪熸鎸夎皟鐢ㄤ紶鍙傦級
+-- =============================================================================
+-- 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); -- 鍙ｅ緞/鍙婃椂澶╂暟鐢ㄩ粯璁ゅ��
+-- 锛圡ySQL 瀛樺偍杩囩▼娌℃湁銆屽彲閫夊弬鏁般�嶏紝鍥涗釜鍙傛暟閮藉繀椤诲啓锛屼笉闇�瑕佺殑浼� 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 (
+ -- 琛ュ伩鏃堕棿锛歷archar 涓斿彲鑳芥槸绌轰覆锛屽厛鎸夊瓙浠诲姟鑱氬悎鎴愪竴琛�
+ 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 ;
diff --git a/sql/v_ward_followup_stats.sql b/sql/v_ward_followup_stats.sql
new file mode 100644
index 0000000..9b4bd1d
--- /dev/null
+++ b/sql/v_ward_followup_stats.sql
@@ -0,0 +1,152 @@
+-- =============================================================================
+-- 鐥呭尯鍑洪櫌闅忚缁熻 路 鍙紶鍙傝鍥�
+-- =============================================================================
+-- 涓轰粈涔堟槸銆屽弬鏁拌〃 + 瑙嗗浘銆嶈�屼笉鏄� @鍙橀噺锛�
+-- MySQL 瑙嗗浘瀹氫箟閲岀姝㈠紩鐢� @鐢ㄦ埛鍙橀噺 / 绯荤粺鍙橀噺 / 瀛樺偍杩囩▼鍙傛暟锛�
+-- 鍚﹀垯鍒涘缓鏃舵姤 ERROR 1351 (HY000): View's SELECT contains a variable or parameter銆�
+-- 瀹樻柟鍘熸枃锛氥�孴he 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
+ -- >>> 鍙紶鍙傜殑鏃堕棿鑼冨洿锛氬乏闂彸寮�锛宔nd_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 (
+ -- 琛ュ伩鏃堕棿锛歷archar 涓斿彲鑳芥槸绌轰覆锛屽厛鎸夊瓙浠诲姟鑱氬悎鎴愪竴琛�
+ 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 '%楠ㄧ%' -- 瑙嗗浘澶栫収鏍疯兘鍐嶅姞鏉′欢
+;
diff --git a/sql/ward_followup_stats_query.sql b/sql/ward_followup_stats_query.sql
new file mode 100644
index 0000000..50d4ff0
--- /dev/null
+++ b/sql/ward_followup_stats_query.sql
@@ -0,0 +1,114 @@
+-- =============================================================================
+-- 鐥呭尯鍑洪櫌闅忚缁熻 路 绾煡璇㈢増锛堜細璇濆彉閲忎紶鍙傦紝閫傚悎 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 (
+ -- 琛ュ伩鏃堕棿锛歷archar 涓斿彲鑳芥槸绌轰覆锛屽厛鎸夊瓙浠诲姟鑱氬悎鎴愪竴琛�
+ 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;
diff --git a/src/api/smartor/pendingTask.js b/src/api/smartor/pendingTask.js
index d54c40c..1bf84bb 100644
--- a/src/api/smartor/pendingTask.js
+++ b/src/api/smartor/pendingTask.js
@@ -7,7 +7,7 @@
* 鍚庣鍙ｅ緞锛歴ervice_subtask 涓�
* service_type = '2' 鍑洪櫌闅忚 / '3' 闂ㄨ瘖闅忚锛堝垎寮�璁℃暟锛�
* current_preachform = '1'锛堜汉宸ワ級
- * 寰呴殢璁跨姸鎬侊紙sendstate in 1,2,3,5,7锛�
+ * 寰呴殢璁跨姸鎬侊紙sendstate in 1,2,3,7锛涘彂閫佸け璐� 5 涓嶈鍏ワ級
* 鍑洪櫌鏃堕棿 endtime 鏃╀簬绛変簬浠婂ぉ - 7 澶�
* 鍙粺璁℃渶杩� 4 涓嚭闄㈡湀浠斤紙鍚綋鏈堬級
*
diff --git a/src/views/followvisit/discharge/index.vue b/src/views/followvisit/discharge/index.vue
index 3fb51d4..27bc525 100644
--- a/src/views/followvisit/discharge/index.vue
+++ b/src/views/followvisit/discharge/index.vue
@@ -1934,7 +1934,7 @@
},
/**
* 鍙充笅瑙掋�屽緟澶勭悊闅忚浠诲姟銆嶅崱鐗囩偣鍑绘暟瀛楄烦杞繃鏉ワ細
- * 鎸夊嚭闄㈠勾鏈� + 寰呴殢璁匡紙sendstate 1/2/3/5/7锛�+ 鍑洪櫌婊� 7 澶� 杩囨护锛屽彛寰勪笌鍗＄墖涓�鑷�
+ * 鎸夊嚭闄㈠勾鏈� + 寰呴殢璁匡紙sendstate 1/2/3/7锛�+ 鍑洪櫌婊� 7 澶� 杩囨护锛屽彛寰勪笌鍗＄墖涓�鑷�
* @returns {boolean} 鏄惁宸叉寜鍗＄墖鏉′欢鍙戣捣鏌ヨ
*/
applyPendingTaskQuery() {
@@ -1952,11 +1952,8 @@
const serviceType = Number(query.serviceType) === 3 ? 3 : 2;
this.errtype = undefined;
const start = month + "-01";
- let end = this.pendingTaskMonthEnd(month);
- // 鏈堝垵 7 澶╁唴锛�"浠婂ぉ-7澶�" 浼氭棭浜庢湀鍒濓紝鍏滃簳鎴愭湀鍒濓紝閬垮厤鏃ユ湡鍖洪棿鍊掓寕
- if (end < start) {
- end = start;
- }
+ // 鍗＄墖缁熻鐨勬槸銆屽嚭闄㈡弧 7 澶┿�嶇殑浠诲姟锛屾埅姝㈡棩 = min(璇ユ湀鏈�鍚庝竴澶�, 浠婂ぉ-7 澶�)
+ const end = this.pendingTaskMonthEnd(month);
this.dateRange = [start, end];
this.topqueryParams.serviceType = serviceType;
this.topqueryParams.sendstateView = 1;
@@ -1964,24 +1961,47 @@
// 鍗＄墖缁熻鐨勬槸鍏ㄩ儴寰呴殢璁夸换鍔★紙涓嶅尯鍒嗛杞�/澶嶈瘖锛夛紝鍒楄〃杩欓噷鍚屾鏀惧紑
this.topqueryParams.visitCount = null;
this.topqueryParams.pageNum = 1;
+ // 褰撴湀杩樻病鍒扮 8 澶╂椂锛岃鏈堜笉鍙兘鏈夈�屽嚭闄㈡弧 7 澶┿�嶇殑浠诲姟锛堝崱鐗囨樉绀� 0锛夛紝鍒楄〃鐩存帴缁欑┖銆�
+ // 鏃ユ湡妗嗕粛鏄剧ず璇ユ湀鐨勮嚜鐒跺尯闂达紝閬垮厤鍑虹幇銆岃捣姝㈠�掓寕銆嶇殑绛涢�夋潯浠躲��
+ if (end < start) {
+ this.dateRange = [start, this.pendingTaskMonthLastDay(month)];
+ this.userList = [];
+ this.total = 0;
+ this.loading = false;
+ this.$message.info("璇ユ湀鏆傛棤鍑洪櫌婊� 7 澶╃殑寰呴殢璁夸换鍔�");
+ return true;
+ }
this.handleQuery(1);
return true;
},
/**
- * 鍗＄墖鍙ｅ緞鐨勬湀浠界粨鏉熸棩锛�
- * 褰撴湀鍙粺璁″嚭闄㈡弧 7 澶╃殑锛屾墍浠ュ彇銆屼粖澶� - 7 澶┿�嶏紱鍘嗗彶鏈堜唤鍙栧綋鏈堟渶鍚庝竴澶�
+ * 璇ユ湀鏈�鍚庝竴澶╋紙yyyy-MM-dd锛夛紝鍙敤浜庛�屽綋鏈堣繕娌℃湁婊� 7 澶╀换鍔°�嶆椂灞曠ず姝ｅ父鏈堜唤鍖洪棿
+ */
+ pendingTaskMonthLastDay(month) {
+ const parts = String(month).split("-");
+ const date = new Date(Number(parts[0]), Number(parts[1]), 0);
+ const mm = String(date.getMonth() + 1).padStart(2, "0");
+ const dd = String(date.getDate()).padStart(2, "0");
+ return date.getFullYear() + "-" + mm + "-" + dd;
+ },
+ /**
+ * 鍗＄墖鍙ｅ緞鐨勬湀浠界粨鏉熸棩锛氬崱鐗囧彧缁熻銆屽嚭闄㈡弧 7 澶╀粛鏈殢璁裤�嶇殑浠诲姟锛�
+ * 鎵�浠ユ埅姝㈡棩姘歌繙鏄� min(璇ユ湀鏈�鍚庝竴澶�, 浠婂ぉ-7 澶�)锛�
+ * 鍘嗗彶鏈堜唤鍚屾牱瑕佹寜銆屼粖澶�-7 澶┿�嶆埅鏂紝鍚﹀垯鏈堟湯鍑犲ぉ鍑洪櫌鐨勬偅鑰呬細琚畻杩涙潵锛堝崱鐗� 49銆佸垪琛� 77锛夈��
*/
pendingTaskMonthEnd(month) {
const parts = String(month).split("-");
const year = Number(parts[0]);
const mon = Number(parts[1]);
const now = new Date();
- let date;
- if (year === now.getFullYear() && mon === now.getMonth() + 1) {
- date = new Date(year, mon - 1, now.getDate() - 7);
- } else {
- date = new Date(year, mon, 0);
- }
+ // 鍑洪櫌婊� 7 澶╋細鍑洪櫌鏃ユ湡 <= 浠婂ぉ-7锛堜笌鍚庣 endtime < 浠婂ぉ-6 00:00 绛変环锛�
+ const cutoff = new Date(
+ now.getFullYear(),
+ now.getMonth(),
+ now.getDate() - 7
+ );
+ const monthEnd = new Date(year, mon, 0);
+ const date = cutoff < monthEnd ? cutoff : monthEnd;
const mm = String(date.getMonth() + 1).padStart(2, "0");
const dd = String(date.getDate()).padStart(2, "0");
return date.getFullYear() + "-" + mm + "-" + dd;
diff --git a/src/views/followvisit/discharge/outpatientService.vue b/src/views/followvisit/discharge/outpatientService.vue
index 243496d..e6fc7d9 100644
--- a/src/views/followvisit/discharge/outpatientService.vue
+++ b/src/views/followvisit/discharge/outpatientService.vue
@@ -1373,7 +1373,7 @@
},
/**
* 鍙充笅瑙掋�屽緟澶勭悊闅忚浠诲姟銆嶅崱鐗囩偣鍑绘暟瀛楄烦杞繃鏉ワ細
- * 鎸夊嚭闄㈠勾鏈� + 寰呴殢璁匡紙sendstate 1/2/3/5/7锛�+ 鍑洪櫌婊� 7 澶� 杩囨护锛屽彛寰勪笌鍗＄墖涓�鑷�
+ * 鎸夊嚭闄㈠勾鏈� + 寰呴殢璁匡紙sendstate 1/2/3/7锛�+ 鍑洪櫌婊� 7 澶� 杩囨护锛屽彛寰勪笌鍗＄墖涓�鑷�
* @returns {boolean} 鏄惁宸叉寜鍗＄墖鏉′欢鍙戣捣鏌ヨ
*/
applyPendingTaskQuery() {
@@ -1391,11 +1391,8 @@
const serviceType = Number(query.serviceType) === 3 ? 3 : 2;
this.errtype = undefined;
const start = month + "-01";
- let end = this.pendingTaskMonthEnd(month);
- // 鏈堝垵 7 澶╁唴锛�"浠婂ぉ-7澶�" 浼氭棭浜庢湀鍒濓紝鍏滃簳鎴愭湀鍒濓紝閬垮厤鏃ユ湡鍖洪棿鍊掓寕
- if (end < start) {
- end = start;
- }
+ // 鍗＄墖缁熻鐨勬槸銆屽嚭闄㈡弧 7 澶┿�嶇殑浠诲姟锛屾埅姝㈡棩 = min(璇ユ湀鏈�鍚庝竴澶�, 浠婂ぉ-7 澶�)
+ const end = this.pendingTaskMonthEnd(month);
this.dateRange = [start, end];
this.topqueryParams.serviceType = serviceType;
this.topqueryParams.sendstateView = 1;
@@ -1403,24 +1400,47 @@
// 鍗＄墖缁熻鐨勬槸鍏ㄩ儴寰呴殢璁夸换鍔★紙涓嶅尯鍒嗛杞�/澶嶈瘖锛夛紝鍒楄〃杩欓噷鍚屾鏀惧紑
this.topqueryParams.visitCount = null;
this.topqueryParams.pageNum = 1;
+ // 褰撴湀杩樻病鍒扮 8 澶╂椂锛岃鏈堜笉鍙兘鏈夈�屽嚭闄㈡弧 7 澶┿�嶇殑浠诲姟锛堝崱鐗囨樉绀� 0锛夛紝鍒楄〃鐩存帴缁欑┖銆�
+ // 鏃ユ湡妗嗕粛鏄剧ず璇ユ湀鐨勮嚜鐒跺尯闂达紝閬垮厤鍑虹幇銆岃捣姝㈠�掓寕銆嶇殑绛涢�夋潯浠躲��
+ if (end < start) {
+ this.dateRange = [start, this.pendingTaskMonthLastDay(month)];
+ this.userList = [];
+ this.total = 0;
+ this.loading = false;
+ this.$message.info("璇ユ湀鏆傛棤鍑洪櫌婊� 7 澶╃殑寰呴殢璁夸换鍔�");
+ return true;
+ }
this.handleQuery(1);
return true;
},
/**
- * 鍗＄墖鍙ｅ緞鐨勬湀浠界粨鏉熸棩锛�
- * 褰撴湀鍙粺璁″嚭闄㈡弧 7 澶╃殑锛屾墍浠ュ彇銆屼粖澶� - 7 澶┿�嶏紱鍘嗗彶鏈堜唤鍙栧綋鏈堟渶鍚庝竴澶�
+ * 璇ユ湀鏈�鍚庝竴澶╋紙yyyy-MM-dd锛夛紝鍙敤浜庛�屽綋鏈堣繕娌℃湁婊� 7 澶╀换鍔°�嶆椂灞曠ず姝ｅ父鏈堜唤鍖洪棿
+ */
+ pendingTaskMonthLastDay(month) {
+ const parts = String(month).split("-");
+ const date = new Date(Number(parts[0]), Number(parts[1]), 0);
+ const mm = String(date.getMonth() + 1).padStart(2, "0");
+ const dd = String(date.getDate()).padStart(2, "0");
+ return date.getFullYear() + "-" + mm + "-" + dd;
+ },
+ /**
+ * 鍗＄墖鍙ｅ緞鐨勬湀浠界粨鏉熸棩锛氬崱鐗囧彧缁熻銆屽嚭闄㈡弧 7 澶╀粛鏈殢璁裤�嶇殑浠诲姟锛�
+ * 鎵�浠ユ埅姝㈡棩姘歌繙鏄� min(璇ユ湀鏈�鍚庝竴澶�, 浠婂ぉ-7 澶�)锛�
+ * 鍘嗗彶鏈堜唤鍚屾牱瑕佹寜銆屼粖澶�-7 澶┿�嶆埅鏂紝鍚﹀垯鏈堟湯鍑犲ぉ鍑洪櫌鐨勬偅鑰呬細琚畻杩涙潵锛堝崱鐗� 49銆佸垪琛� 77锛夈��
*/
pendingTaskMonthEnd(month) {
const parts = String(month).split("-");
const year = Number(parts[0]);
const mon = Number(parts[1]);
const now = new Date();
- let date;
- if (year === now.getFullYear() && mon === now.getMonth() + 1) {
- date = new Date(year, mon - 1, now.getDate() - 7);
- } else {
- date = new Date(year, mon, 0);
- }
+ // 鍑洪櫌婊� 7 澶╋細鍑洪櫌鏃ユ湡 <= 浠婂ぉ-7锛堜笌鍚庣 endtime < 浠婂ぉ-6 00:00 绛変环锛�
+ const cutoff = new Date(
+ now.getFullYear(),
+ now.getMonth(),
+ now.getDate() - 7
+ );
+ const monthEnd = new Date(year, mon, 0);
+ const date = cutoff < monthEnd ? cutoff : monthEnd;
const mm = String(date.getMonth() + 1).padStart(2, "0");
const dd = String(date.getDate()).padStart(2, "0");
return date.getFullYear() + "-" + mm + "-" + dd;
--
Gitblit v1.9.3