From 8027c8e2e0b5c559da612b187031dd6fd82d9bc7 Mon Sep 17 00:00:00 2001
From: Junjie <fallin.jie@qq.com>
Date: 星期六, 04 四月 2026 12:03:48 +0800
Subject: [PATCH] #任务分析异常修复
---
src/main/resources/sql/20260322_add_wrk_analysis_page_and_inbound_status_migration.sql | 261 +++++++++++++++++++++++++++++++++++++--------------
1 files changed, 188 insertions(+), 73 deletions(-)
diff --git a/src/main/resources/sql/20260322_add_wrk_analysis_page_and_inbound_status_migration.sql b/src/main/resources/sql/20260322_add_wrk_analysis_page_and_inbound_status_migration.sql
index 8d8350f..91137c3 100644
--- a/src/main/resources/sql/20260322_add_wrk_analysis_page_and_inbound_status_migration.sql
+++ b/src/main/resources/sql/20260322_add_wrk_analysis_page_and_inbound_status_migration.sql
@@ -1,4 +1,6 @@
CREATE TABLE IF NOT EXISTS `asr_wrk_analysis` (
+ `id` bigint NOT NULL AUTO_INCREMENT COMMENT '涓婚敭ID',
+ `wrk_log_id` bigint DEFAULT NULL COMMENT '宸ヤ綔鍘嗗彶ID',
`wrk_no` int NOT NULL COMMENT '宸ヤ綔鍙�',
`wms_wrk_no` varchar(64) DEFAULT NULL COMMENT 'WMS浠诲姟鍙�',
`io_type` int DEFAULT NULL COMMENT '鍏ュ嚭搴撶被鍨�',
@@ -31,7 +33,9 @@
`metric_completeness` varchar(16) DEFAULT 'PARTIAL' COMMENT '鏁版嵁瀹屾暣鎬�',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '鍒涘缓鏃堕棿',
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '鏇存柊鏃堕棿',
- PRIMARY KEY (`wrk_no`),
+ PRIMARY KEY (`id`),
+ UNIQUE KEY `uk_wrk_analysis_wrk_log_id` (`wrk_log_id`),
+ KEY `idx_wrk_analysis_wrk_no` (`wrk_no`),
KEY `idx_wrk_analysis_finish_time` (`finish_time`),
KEY `idx_wrk_analysis_appe_time` (`appe_time`),
KEY `idx_wrk_analysis_io_type` (`io_type`),
@@ -117,6 +121,7 @@
WHERE `wrk_sts` = 2;
INSERT INTO `asr_wrk_analysis` (
+ `wrk_log_id`,
`wrk_no`,
`wms_wrk_no`,
`io_type`,
@@ -147,6 +152,7 @@
`update_time`
)
SELECT
+ l.`id`,
l.`wrk_no`,
l.`wms_wrk_no`,
l.`io_type`,
@@ -166,83 +172,192 @@
END AS `total_duration_ms`,
CASE WHEN l.`io_type` = 201 THEN 0 ELSE NULL END AS `station_duration_ms`,
NULL AS `crane_duration_ms`,
- CASE WHEN COALESCE(c.`fault_count`, 0) + COALESCE(d.`fault_count`, 0) + COALESCE(r.`fault_count`, 0) > 0 THEN 1 ELSE 0 END AS `has_fault`,
- COALESCE(c.`fault_count`, 0) + COALESCE(d.`fault_count`, 0) + COALESCE(r.`fault_count`, 0) AS `fault_count`,
- COALESCE(c.`fault_duration_ms`, 0) + COALESCE(d.`fault_duration_ms`, 0) + COALESCE(r.`fault_duration_ms`, 0) AS `fault_duration_ms`,
- COALESCE(c.`fault_count`, 0) AS `crn_fault_count`,
- COALESCE(c.`fault_duration_ms`, 0) AS `crn_fault_duration_ms`,
- COALESCE(d.`fault_count`, 0) AS `dual_crn_fault_count`,
- COALESCE(d.`fault_duration_ms`, 0) AS `dual_crn_fault_duration_ms`,
- COALESCE(r.`fault_count`, 0) AS `rgv_fault_count`,
- COALESCE(r.`fault_duration_ms`, 0) AS `rgv_fault_duration_ms`,
+ CASE WHEN (
+ COALESCE((
+ SELECT COUNT(*)
+ FROM `asr_bas_crnp_err_log` e
+ WHERE e.`wrk_no` = l.`wrk_no`
+ AND (e.`start_time` IS NULL OR e.`start_time` <= COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`))
+ AND COALESCE(e.`end_time`, e.`start_time`, COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`)) >= COALESCE(l.`appe_time`, l.`io_time`, l.`modi_time`)
+ ), 0)
+ + COALESCE((
+ SELECT COUNT(*)
+ FROM `asr_bas_dual_crnp_err_log` e
+ WHERE e.`wrk_no` = l.`wrk_no`
+ AND (e.`start_time` IS NULL OR e.`start_time` <= COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`))
+ AND COALESCE(e.`end_time`, e.`start_time`, COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`)) >= COALESCE(l.`appe_time`, l.`io_time`, l.`modi_time`)
+ ), 0)
+ + COALESCE((
+ SELECT COUNT(*)
+ FROM `asr_bas_rgv_err_log` e
+ WHERE e.`task_no` = l.`wrk_no`
+ AND (e.`start_time` IS NULL OR e.`start_time` <= COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`))
+ AND COALESCE(e.`end_time`, e.`start_time`, COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`)) >= COALESCE(l.`appe_time`, l.`io_time`, l.`modi_time`)
+ ), 0)
+ ) > 0 THEN 1 ELSE 0 END AS `has_fault`,
+ COALESCE((
+ SELECT COUNT(*)
+ FROM `asr_bas_crnp_err_log` e
+ WHERE e.`wrk_no` = l.`wrk_no`
+ AND (e.`start_time` IS NULL OR e.`start_time` <= COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`))
+ AND COALESCE(e.`end_time`, e.`start_time`, COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`)) >= COALESCE(l.`appe_time`, l.`io_time`, l.`modi_time`)
+ ), 0)
+ + COALESCE((
+ SELECT COUNT(*)
+ FROM `asr_bas_dual_crnp_err_log` e
+ WHERE e.`wrk_no` = l.`wrk_no`
+ AND (e.`start_time` IS NULL OR e.`start_time` <= COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`))
+ AND COALESCE(e.`end_time`, e.`start_time`, COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`)) >= COALESCE(l.`appe_time`, l.`io_time`, l.`modi_time`)
+ ), 0)
+ + COALESCE((
+ SELECT COUNT(*)
+ FROM `asr_bas_rgv_err_log` e
+ WHERE e.`task_no` = l.`wrk_no`
+ AND (e.`start_time` IS NULL OR e.`start_time` <= COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`))
+ AND COALESCE(e.`end_time`, e.`start_time`, COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`)) >= COALESCE(l.`appe_time`, l.`io_time`, l.`modi_time`)
+ ), 0) AS `fault_count`,
+ COALESCE((
+ SELECT SUM(
+ CASE
+ WHEN e.`start_time` IS NULL THEN 0
+ ELSE GREATEST(
+ TIMESTAMPDIFF(
+ MICROSECOND,
+ GREATEST(e.`start_time`, COALESCE(l.`appe_time`, l.`io_time`, l.`modi_time`)),
+ LEAST(COALESCE(e.`end_time`, COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`)), COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`))
+ ),
+ 0
+ ) DIV 1000
+ END
+ )
+ FROM `asr_bas_crnp_err_log` e
+ WHERE e.`wrk_no` = l.`wrk_no`
+ AND (e.`start_time` IS NULL OR e.`start_time` <= COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`))
+ AND COALESCE(e.`end_time`, e.`start_time`, COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`)) >= COALESCE(l.`appe_time`, l.`io_time`, l.`modi_time`)
+ ), 0)
+ + COALESCE((
+ SELECT SUM(
+ CASE
+ WHEN e.`start_time` IS NULL THEN 0
+ ELSE GREATEST(
+ TIMESTAMPDIFF(
+ MICROSECOND,
+ GREATEST(e.`start_time`, COALESCE(l.`appe_time`, l.`io_time`, l.`modi_time`)),
+ LEAST(COALESCE(e.`end_time`, COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`)), COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`))
+ ),
+ 0
+ ) DIV 1000
+ END
+ )
+ FROM `asr_bas_dual_crnp_err_log` e
+ WHERE e.`wrk_no` = l.`wrk_no`
+ AND (e.`start_time` IS NULL OR e.`start_time` <= COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`))
+ AND COALESCE(e.`end_time`, e.`start_time`, COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`)) >= COALESCE(l.`appe_time`, l.`io_time`, l.`modi_time`)
+ ), 0)
+ + COALESCE((
+ SELECT SUM(
+ CASE
+ WHEN e.`start_time` IS NULL THEN 0
+ ELSE GREATEST(
+ TIMESTAMPDIFF(
+ MICROSECOND,
+ GREATEST(e.`start_time`, COALESCE(l.`appe_time`, l.`io_time`, l.`modi_time`)),
+ LEAST(COALESCE(e.`end_time`, COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`)), COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`))
+ ),
+ 0
+ ) DIV 1000
+ END
+ )
+ FROM `asr_bas_rgv_err_log` e
+ WHERE e.`task_no` = l.`wrk_no`
+ AND (e.`start_time` IS NULL OR e.`start_time` <= COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`))
+ AND COALESCE(e.`end_time`, e.`start_time`, COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`)) >= COALESCE(l.`appe_time`, l.`io_time`, l.`modi_time`)
+ ), 0) AS `fault_duration_ms`,
+ COALESCE((
+ SELECT COUNT(*)
+ FROM `asr_bas_crnp_err_log` e
+ WHERE e.`wrk_no` = l.`wrk_no`
+ AND (e.`start_time` IS NULL OR e.`start_time` <= COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`))
+ AND COALESCE(e.`end_time`, e.`start_time`, COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`)) >= COALESCE(l.`appe_time`, l.`io_time`, l.`modi_time`)
+ ), 0) AS `crn_fault_count`,
+ COALESCE((
+ SELECT SUM(
+ CASE
+ WHEN e.`start_time` IS NULL THEN 0
+ ELSE GREATEST(
+ TIMESTAMPDIFF(
+ MICROSECOND,
+ GREATEST(e.`start_time`, COALESCE(l.`appe_time`, l.`io_time`, l.`modi_time`)),
+ LEAST(COALESCE(e.`end_time`, COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`)), COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`))
+ ),
+ 0
+ ) DIV 1000
+ END
+ )
+ FROM `asr_bas_crnp_err_log` e
+ WHERE e.`wrk_no` = l.`wrk_no`
+ AND (e.`start_time` IS NULL OR e.`start_time` <= COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`))
+ AND COALESCE(e.`end_time`, e.`start_time`, COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`)) >= COALESCE(l.`appe_time`, l.`io_time`, l.`modi_time`)
+ ), 0) AS `crn_fault_duration_ms`,
+ COALESCE((
+ SELECT COUNT(*)
+ FROM `asr_bas_dual_crnp_err_log` e
+ WHERE e.`wrk_no` = l.`wrk_no`
+ AND (e.`start_time` IS NULL OR e.`start_time` <= COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`))
+ AND COALESCE(e.`end_time`, e.`start_time`, COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`)) >= COALESCE(l.`appe_time`, l.`io_time`, l.`modi_time`)
+ ), 0) AS `dual_crn_fault_count`,
+ COALESCE((
+ SELECT SUM(
+ CASE
+ WHEN e.`start_time` IS NULL THEN 0
+ ELSE GREATEST(
+ TIMESTAMPDIFF(
+ MICROSECOND,
+ GREATEST(e.`start_time`, COALESCE(l.`appe_time`, l.`io_time`, l.`modi_time`)),
+ LEAST(COALESCE(e.`end_time`, COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`)), COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`))
+ ),
+ 0
+ ) DIV 1000
+ END
+ )
+ FROM `asr_bas_dual_crnp_err_log` e
+ WHERE e.`wrk_no` = l.`wrk_no`
+ AND (e.`start_time` IS NULL OR e.`start_time` <= COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`))
+ AND COALESCE(e.`end_time`, e.`start_time`, COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`)) >= COALESCE(l.`appe_time`, l.`io_time`, l.`modi_time`)
+ ), 0) AS `dual_crn_fault_duration_ms`,
+ COALESCE((
+ SELECT COUNT(*)
+ FROM `asr_bas_rgv_err_log` e
+ WHERE e.`task_no` = l.`wrk_no`
+ AND (e.`start_time` IS NULL OR e.`start_time` <= COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`))
+ AND COALESCE(e.`end_time`, e.`start_time`, COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`)) >= COALESCE(l.`appe_time`, l.`io_time`, l.`modi_time`)
+ ), 0) AS `rgv_fault_count`,
+ COALESCE((
+ SELECT SUM(
+ CASE
+ WHEN e.`start_time` IS NULL THEN 0
+ ELSE GREATEST(
+ TIMESTAMPDIFF(
+ MICROSECOND,
+ GREATEST(e.`start_time`, COALESCE(l.`appe_time`, l.`io_time`, l.`modi_time`)),
+ LEAST(COALESCE(e.`end_time`, COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`)), COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`))
+ ),
+ 0
+ ) DIV 1000
+ END
+ )
+ FROM `asr_bas_rgv_err_log` e
+ WHERE e.`task_no` = l.`wrk_no`
+ AND (e.`start_time` IS NULL OR e.`start_time` <= COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`))
+ AND COALESCE(e.`end_time`, e.`start_time`, COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`)) >= COALESCE(l.`appe_time`, l.`io_time`, l.`modi_time`)
+ ), 0) AS `rgv_fault_duration_ms`,
'PARTIAL' AS `metric_completeness`,
NOW(),
NOW()
FROM `asr_wrk_mast_log` l
-LEFT JOIN (
- SELECT
- e.`wrk_no`,
- COUNT(*) AS `fault_count`,
- SUM(
- CASE
- WHEN e.`start_time` IS NULL THEN 0
- ELSE GREATEST(
- TIMESTAMPDIFF(
- MICROSECOND,
- e.`start_time`,
- COALESCE(e.`end_time`, wl.`modi_time`, wl.`io_time`, wl.`appe_time`, e.`start_time`)
- ),
- 0
- ) DIV 1000
- END
- ) AS `fault_duration_ms`
- FROM `asr_bas_crnp_err_log` e
- LEFT JOIN `asr_wrk_mast_log` wl ON wl.`wrk_no` = e.`wrk_no`
- GROUP BY e.`wrk_no`
-) c ON c.`wrk_no` = l.`wrk_no`
-LEFT JOIN (
- SELECT
- e.`wrk_no`,
- COUNT(*) AS `fault_count`,
- SUM(
- CASE
- WHEN e.`start_time` IS NULL THEN 0
- ELSE GREATEST(
- TIMESTAMPDIFF(
- MICROSECOND,
- e.`start_time`,
- COALESCE(e.`end_time`, wl.`modi_time`, wl.`io_time`, wl.`appe_time`, e.`start_time`)
- ),
- 0
- ) DIV 1000
- END
- ) AS `fault_duration_ms`
- FROM `asr_bas_dual_crnp_err_log` e
- LEFT JOIN `asr_wrk_mast_log` wl ON wl.`wrk_no` = e.`wrk_no`
- GROUP BY e.`wrk_no`
-) d ON d.`wrk_no` = l.`wrk_no`
-LEFT JOIN (
- SELECT
- e.`task_no` AS `wrk_no`,
- COUNT(*) AS `fault_count`,
- SUM(
- CASE
- WHEN e.`start_time` IS NULL THEN 0
- ELSE GREATEST(
- TIMESTAMPDIFF(
- MICROSECOND,
- e.`start_time`,
- COALESCE(e.`end_time`, wl.`modi_time`, wl.`io_time`, wl.`appe_time`, e.`start_time`)
- ),
- 0
- ) DIV 1000
- END
- ) AS `fault_duration_ms`
- FROM `asr_bas_rgv_err_log` e
- LEFT JOIN `asr_wrk_mast_log` wl ON wl.`wrk_no` = e.`task_no`
- GROUP BY e.`task_no`
-) r ON r.`wrk_no` = l.`wrk_no`
ON DUPLICATE KEY UPDATE
+ `wrk_log_id` = VALUES(`wrk_log_id`),
+ `wrk_no` = VALUES(`wrk_no`),
`wms_wrk_no` = VALUES(`wms_wrk_no`),
`io_type` = VALUES(`io_type`),
`final_wrk_sts` = VALUES(`final_wrk_sts`),
--
Gitblit v1.9.1