From c853024d279758e692d539c8aff2c9ed5af0abea Mon Sep 17 00:00:00 2001
From: 陈昶聿 <chychen@nbjetron.com>
Date: 星期六, 07 十一月 2026 13:48:25 +0800
Subject: [PATCH] fix: 调整随访及时率基准
---
smartor/src/main/java/com/smartor/mapper/ServiceSubtaskMapper.java | 8 +++-
smartor/src/main/resources/mapper/smartor/ServiceSubtaskMapper.xml | 71 ++++++++++++++++++++++++++---------
2 files changed, 58 insertions(+), 21 deletions(-)
diff --git a/smartor/src/main/java/com/smartor/mapper/ServiceSubtaskMapper.java b/smartor/src/main/java/com/smartor/mapper/ServiceSubtaskMapper.java
index 8cc7a03..cf80de1 100644
--- a/smartor/src/main/java/com/smartor/mapper/ServiceSubtaskMapper.java
+++ b/smartor/src/main/java/com/smartor/mapper/ServiceSubtaskMapper.java
@@ -111,10 +111,14 @@
/**
* 闅忚鍙婃椂鐜囷紙鎸� groupKey 鍒嗙粍鎵归噺缁熻锛�
* <p>
- * rate锛氬嚭闄㈡椂闂�(endtime)璧� rateDay + 琛ュ伩澶╂暟 鍐呭畬鎴愰殢璁跨殑鍗犳瘮銆�
+ * rate锛氬畬鎴愭椂闂�(finishtime) 涓嶆櫄浜庡強鏃剁巼鍩哄噯鐨勫崰姣旓紝鍒嗘瘝涓哄簲闅忚鏁般��
+ * 闈炰换鍔＄粍(appltype != 5)锛歟ndtime + send_day(缂虹渷1) + 危琛ュ伩澶╂暟 + 1 澶╋紱
+ * 浠诲姟缁�(appltype = 5)锛氭寜 schedule_benchmark 瑙ｆ瀽鍩哄噯鏃堕棿
+ * (1-鍏ラ櫌 2-鍑洪櫌 3-灏辫瘖 4-鍏ョ粍 0/鍏跺畠-鑷畾涔夋椂闂�) + day_offset
+ * + send_day(缂虹渷1) + 危琛ュ伩澶╂暟 + 1 澶┿��
* 琛ュ伩澶╂暟鍙栬嚜 service_subtask_preachform.compensate_time 鎸夊瓙浠诲姟姹傚拰锛�
* 鐢ㄥ瓙浠诲姟蹇収鑰岄潪 service_task.preachform_desc锛屼繚璇佸巻鍙叉暟鎹笉闅忎换鍔℃敼閰嶇疆鑰屽彉鍔ㄣ��
- * rateDay 涓虹┖鏃� rate 杩斿洖 NULL銆�
+ * rateDay 鍙傛暟涓嶅啀鍙備笌璇ュ彛寰勩��
* <p>
* finishRate锛氬簲闅忚鏃堕棿(visit_time) <= 瀹為檯瀹屾垚鏃堕棿(finishtime) 鐨勫崰姣旓紝褰撳墠鏈闅忚缁熻浣跨敤銆�
*
diff --git a/smartor/src/main/resources/mapper/smartor/ServiceSubtaskMapper.xml b/smartor/src/main/resources/mapper/smartor/ServiceSubtaskMapper.xml
index a8960d6..b54d507 100644
--- a/smartor/src/main/resources/mapper/smartor/ServiceSubtaskMapper.xml
+++ b/smartor/src/main/resources/mapper/smartor/ServiceSubtaskMapper.xml
@@ -854,26 +854,59 @@
</if>
SUM(CASE WHEN ss.finishtime IS NOT NULL AND DATE(ss.visit_time) <= DATE(ss.finishtime) THEN 1 ELSE 0 END) /
COUNT(*) AS finishRate,
- <if test="rateDay != null">
- SUM(CASE WHEN ss.finishtime IS NOT NULL
- AND DATEDIFF(ss.finishtime, ss.endtime) <= #{rateDay} + COALESCE(cp.compensate_days, 0)
- THEN 1 ELSE 0 END) / COUNT(*) AS rate
- </if>
- <if test="rateDay == null">
- NULL AS rate
- </if>
+ <!-- 鍙婃椂鐜囧熀鍑嗭細
+ 1) 闈炰换鍔＄粍(appltype != 5)锛歟ndtime + send_day(缂虹渷1) + 危琛ュ伩澶╂暟 + 1 澶╋紱
+ 2) 浠诲姟缁�(appltype = 5)锛氭寜 schedule_benchmark 瑙ｆ瀽鍩哄噯鏃堕棿
+ (1-鍏ラ櫌鏃堕棿 2-鍑洪櫌鏃堕棿 3-灏辫瘖鏃堕棿 4-鍏ョ粍鏃堕棿 0/鍏跺畠-鑷畾涔夋椂闂�)
+ + day_offset + send_day(缂虹渷1) + 危琛ュ伩澶╂暟 + 1 澶╋紱
+ 瀹屾垚鏃堕棿 finishtime <= 鍩哄噯鏃堕棿璁′负鍙婃椂锛屽垎姣嶄粛涓哄簲闅忚鏁般��
+ 琛ュ伩澶╂暟鍙彇 service_subtask_preachform.compensate_time 鐨勬眹鎬诲�间竴娆★紝
+ 閬垮厤涓庤ˉ鍋挎槑缁嗙浉鍔犻�犳垚閲嶅琛ュ伩鎴栦竴瀵瑰鏀惧ぇ鍒嗘瘝銆� -->
+ SUM(CASE
+ WHEN ss.finishtime IS NOT NULL
+ AND COALESCE(st.appltype, '') != '5'
+ AND ss.endtime IS NOT NULL
+ AND ss.finishtime <= DATE_ADD(
+ DATE_ADD(ss.endtime, INTERVAL COALESCE(st.send_day, 1) DAY),
+ INTERVAL (COALESCE(cp.compensate_days, 0) + 1) DAY)
+ THEN 1
+ WHEN ss.finishtime IS NOT NULL
+ AND st.appltype = '5'
+ AND std.day_offset IS NOT NULL
+ AND ss.finishtime <= DATE_ADD(
+ DATE_ADD(
+ DATE_ADD(
+ CASE sts.schedule_benchmark
+ WHEN 1 THEN ss.starttime
+ WHEN 2 THEN ss.endtime
+ WHEN 3 THEN ss.admindate
+ WHEN 4 THEN ss.create_time
+ ELSE sts.schedule_benchmark_custom_time
+ END, INTERVAL std.day_offset DAY),
+ INTERVAL COALESCE(st.send_day, 1) DAY),
+ INTERVAL (COALESCE(cp.compensate_days, 0) + 1) DAY)
+ THEN 1
+ ELSE 0
+ END) / COUNT(*) AS rate
FROM service_subtask ss
- <if test="rateDay != null">
- <!-- 琛ュ伩鏃堕棿锛氬厛鎸夊瓙浠诲姟鑱氬悎鎴愪竴琛屽啀鍏宠仈锛岄伩鍏嶄竴瀵瑰鏀惧ぇ COUNT(*) 鍒嗘瘝銆�
- compensate_time 鏄� varchar 涓斿彲鑳藉瓨鎴愮┖涓诧紝闇�鍏堢疆绌哄啀杞暟瀛� -->
- 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
- </if>
+ <!-- 杩欓噷鎸� taskid / 鍘嗗彶 schedule 蹇収鍏宠仈锛屼笉鎸� del_flag 杩囨护锛�
+ 浠诲姟鎴栬鍒掕缂栬緫鍚庢棫 schedule/detail 浼氱疆涓� del_flag=1锛�
+ 浣嗗巻鍙� subtask 浠嶅紩鐢ㄦ棫 ID锛岃繃婊や細涓㈠け鍘嗗彶鍙婃椂鐜囧熀鍑嗐�� -->
+ LEFT JOIN service_task st
+ ON st.taskid = ss.taskid
+ LEFT JOIN service_task_schedule sts
+ ON sts.id = ss.schedule_id
+ LEFT JOIN service_task_schedule_detail std
+ ON std.id = ss.schedule_detail_id
+ <!-- 琛ュ伩鏃堕棿锛氬厛鎸夊瓙浠诲姟鑱氬悎鎴愪竴琛屽啀鍏宠仈锛岄伩鍏嶄竴瀵瑰鏀惧ぇ COUNT(*) 鍒嗘瘝銆�
+ compensate_time 鏄� varchar 涓斿彲鑳藉瓨鎴愮┖涓诧紝闇�鍏堢疆绌哄啀杞暟瀛� -->
+ 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.visit_time IS NOT NULL
AND ss.sendstate != 4
--
Gitblit v1.9.3