From af8cab142a6b15c06e131a8474574dd5b00df982 Mon Sep 17 00:00:00 2001
From: wlzboy <66905212@qq.com>
Date: 星期四, 04 十二月 2025 22:09:58 +0800
Subject: [PATCH] feat: 改造微信accesstoken存放在系统配置表中
---
sql/fix_segment_mileage_task_association.sql | 134 ++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 134 insertions(+), 0 deletions(-)
diff --git a/sql/fix_segment_mileage_task_association.sql b/sql/fix_segment_mileage_task_association.sql
new file mode 100644
index 0000000..d3b5a21
--- /dev/null
+++ b/sql/fix_segment_mileage_task_association.sql
@@ -0,0 +1,134 @@
+-- ========================================
+-- 淇GPS鍒嗘閲岀▼鐨勪换鍔″叧鑱�
+-- 鐢ㄩ�旓細涓哄凡鏈夌殑鍒嗘閲岀▼鏁版嵁琛ュ厖 task_id 鍜� task_code
+-- 浼樺寲璇存槑锛氳ˉ鍏卼ask_id鍚庯紝缁熻璁＄畻灏嗕娇鐢ㄤ紭鍖栨柟妗堬紙鐩存帴SQL鑱氬悎锛屾洿蹇級
+-- ========================================
+
+-- 1. 鏌ョ湅褰撳墠鏈叧鑱斾换鍔＄殑鍒嗘鏁伴噺
+SELECT COUNT(*) as '鏈叧鑱斾换鍔＄殑鍒嗘鏁�'
+FROM tb_vehicle_gps_segment_mileage
+WHERE task_id IS NULL;
+
+-- 2. 鏌ョ湅鏈夊灏戜换鍔″彲浠ヨ鍏宠仈
+SELECT COUNT(DISTINCT t.task_id) as '鍙叧鑱旂殑浠诲姟鏁�'
+FROM sys_task t
+INNER JOIN sys_task_vehicle tv ON t.task_id = tv.task_id
+WHERE t.del_flag = '0'
+ AND t.task_status NOT IN ('PENDING', 'CANCELLED');
+
+-- 3. 鏇存柊閫昏緫锛氭牴鎹溅杈咺D鍜屾椂闂撮噸鍙犲叧鑱斾换鍔�
+-- 娉ㄦ剰锛氳繖涓煡璇細姣旇緝鎱紝寤鸿鍒嗘壒鎵ц鎴栧湪闈為珮宄版湡鎵ц
+
+UPDATE tb_vehicle_gps_segment_mileage seg
+INNER JOIN (
+ SELECT
+ seg2.segment_id,
+ t.task_id,
+ t.task_code
+ FROM tb_vehicle_gps_segment_mileage seg2
+ INNER JOIN sys_task_vehicle tv ON seg2.vehicle_id = tv.vehicle_id
+ INNER JOIN sys_task t ON tv.task_id = t.task_id
+ WHERE seg2.task_id IS NULL
+ AND t.del_flag = '0'
+ -- AND t.task_status NOT IN ('PENDING', 'CANCELLED')
+ -- 鏃堕棿閲嶅彔鏉′欢锛氬垎娈靛紑濮嬫椂闂� < 浠诲姟缁撴潫鏃堕棿 AND 鍒嗘缁撴潫鏃堕棿 > 浠诲姟寮�濮嬫椂闂�
+ AND seg2.segment_start_time < COALESCE(t.actual_end_time, t.planned_end_time, DATE_ADD(t.create_time, INTERVAL 24 HOUR))
+ AND seg2.segment_end_time > COALESCE(t.actual_start_time, t.planned_start_time, t.create_time)
+ GROUP BY seg2.segment_id, t.task_id, t.task_code
+) task_match ON seg.segment_id = task_match.segment_id
+SET
+ seg.task_id = task_match.task_id,
+ seg.task_code = task_match.task_code;
+
+-- 4. 鏌ョ湅淇缁撴灉
+SELECT
+ '宸插叧鑱斾换鍔�' as '绫诲瀷',
+ COUNT(*) as '鏁伴噺',
+ CONCAT(ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM tb_vehicle_gps_segment_mileage), 2), '%') as '鍗犳瘮'
+FROM tb_vehicle_gps_segment_mileage
+WHERE task_id IS NOT NULL
+UNION ALL
+SELECT
+ '鏈叧鑱斾换鍔�' as '绫诲瀷',
+ COUNT(*) as '鏁伴噺',
+ CONCAT(ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM tb_vehicle_gps_segment_mileage), 2), '%') as '鍗犳瘮'
+FROM tb_vehicle_gps_segment_mileage
+WHERE task_id IS NULL;
+
+-- 5. 鏌ョ湅姣忎釜杞﹁締鐨勫叧鑱旀儏鍐�
+SELECT
+ seg.vehicle_id,
+ seg.vehicle_no,
+ COUNT(*) as '鎬诲垎娈垫暟',
+ SUM(CASE WHEN seg.task_id IS NOT NULL THEN 1 ELSE 0 END) as '宸插叧鑱斿垎娈垫暟',
+ CONCAT(ROUND(SUM(CASE WHEN seg.task_id IS NOT NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2), '%') as '鍏宠仈鐜�'
+FROM tb_vehicle_gps_segment_mileage seg
+GROUP BY seg.vehicle_id, seg.vehicle_no
+ORDER BY COUNT(*) DESC
+LIMIT 20;
+
+-- 6. 鍒嗘瀽鏈兘鍏宠仈鐨勫師鍥�
+-- 鏌ョ湅鏌愪釜鏈叧鑱斿垎娈电殑璇︾粏淇℃伅
+SELECT
+ seg.segment_id,
+ seg.vehicle_id,
+ seg.vehicle_no,
+ seg.segment_start_time,
+ seg.segment_end_time,
+ '璇ユ椂娈佃杞﹁締鐨勪换鍔�' as '璇存槑',
+ t.task_id,
+ t.task_code,
+ t.task_status,
+ t.create_time,
+ COALESCE(t.actual_start_time, t.planned_start_time) as '浠诲姟寮�濮嬫椂闂�',
+ COALESCE(t.actual_end_time, t.planned_end_time) as '浠诲姟缁撴潫鏃堕棿'
+FROM tb_vehicle_gps_segment_mileage seg
+LEFT JOIN sys_task_vehicle tv ON seg.vehicle_id = tv.vehicle_id
+LEFT JOIN sys_task t ON tv.task_id = t.task_id
+ AND t.del_flag = '0'
+ AND seg.segment_start_time < COALESCE(t.actual_end_time, t.planned_end_time, DATE_ADD(t.create_time, INTERVAL 24 HOUR))
+ AND seg.segment_end_time > COALESCE(t.actual_start_time, t.planned_start_time, t.create_time)
+WHERE seg.task_id IS NULL
+ORDER BY seg.segment_start_time DESC
+LIMIT 10;
+
+-- ========================================
+-- 浣跨敤璇存槑锛�
+-- 1. 鍏堟墽琛屾煡璇㈣鍙ワ紙1銆�2锛夋煡鐪嬫暟鎹儏鍐�
+-- 2. 鍦ㄩ潪楂樺嘲鏈熸墽琛屾洿鏂拌鍙ワ紙3锛�
+-- 3. 鎵ц缁撴灉鏌ヨ锛�4銆�5銆�6锛夐獙璇佷慨澶嶆晥鏋�
+-- 4. 濡傛灉鏁版嵁閲忓ぇ锛屽缓璁坊鍔� LIMIT 鍒嗘壒鎵ц
+-- ========================================
+
+-- ========================================
+-- 棰勯槻鎺柦锛氱‘淇濆畾鏃朵换鍔℃甯歌繍琛�
+-- ========================================
+
+-- 7. 妫�鏌PS鍒嗘閲岀▼璁＄畻瀹氭椂浠诲姟鐘舵��
+SELECT
+ job_id,
+ job_name,
+ job_group,
+ invoke_target,
+ cron_expression,
+ status as '鐘舵��(0=姝ｅ父,1=鏆傚仠)',
+ create_time,
+ update_time
+FROM sys_job
+WHERE job_name LIKE '%GPS%' OR job_name LIKE '%閲岀▼%'
+ORDER BY create_time DESC;
+
+-- 8. 濡傛灉瀹氭椂浠诲姟鏄殏鍋滅姸鎬侊紝鍚姩瀹�
+-- UPDATE sys_job SET status = '0' WHERE job_name = 'GPS鍒嗘閲岀▼瀹炴椂璁＄畻';
+
+-- 9. 妫�鏌ユ渶杩戠殑GPS璁＄畻璁板綍
+SELECT
+ vehicle_id,
+ vehicle_no,
+ MAX(segment_end_time) as '鏈�鍚庤绠楁椂闂�',
+ COUNT(*) as '鍒嗘鏁�',
+ SUM(CASE WHEN task_id IS NOT NULL THEN 1 ELSE 0 END) as '鏈変换鍔″叧鑱旂殑鍒嗘鏁�'
+FROM tb_vehicle_gps_segment_mileage
+WHERE segment_end_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)
+GROUP BY vehicle_id, vehicle_no
+ORDER BY MAX(segment_end_time) DESC;
--
Gitblit v1.9.1