CREATE TABLE IF NOT EXISTS `asr_wrk_analysis` (
|
`wrk_no` int NOT NULL COMMENT '工作号',
|
`wms_wrk_no` varchar(64) DEFAULT NULL COMMENT 'WMS任务号',
|
`io_type` int DEFAULT NULL COMMENT '入出库类型',
|
`final_wrk_sts` bigint DEFAULT NULL COMMENT '最终工作状态',
|
`source_sta_no` int DEFAULT NULL COMMENT '源站',
|
`sta_no` int DEFAULT NULL COMMENT '目标站',
|
`source_loc_no` varchar(64) DEFAULT NULL COMMENT '源库位',
|
`loc_no` varchar(64) DEFAULT NULL COMMENT '目标库位',
|
`crn_no` int DEFAULT NULL COMMENT '堆垛机号',
|
`dual_crn_no` int DEFAULT NULL COMMENT '双工位堆垛机号',
|
`rgv_no` int DEFAULT NULL COMMENT 'RGV号',
|
`appe_time` datetime DEFAULT NULL COMMENT '创建时间',
|
`finish_time` datetime DEFAULT NULL COMMENT '完成时间',
|
`total_duration_ms` bigint DEFAULT NULL COMMENT '总耗时毫秒',
|
`station_start_time` datetime DEFAULT NULL COMMENT '站点开始时间',
|
`station_end_time` datetime DEFAULT NULL COMMENT '站点结束时间',
|
`station_duration_ms` bigint DEFAULT NULL COMMENT '站点耗时毫秒',
|
`crane_start_time` datetime DEFAULT NULL COMMENT '堆垛机开始时间',
|
`crane_end_time` datetime DEFAULT NULL COMMENT '堆垛机结束时间',
|
`crane_duration_ms` bigint DEFAULT NULL COMMENT '堆垛机耗时毫秒',
|
`has_fault` int DEFAULT '0' COMMENT '是否故障',
|
`fault_count` int DEFAULT '0' COMMENT '故障次数',
|
`fault_duration_ms` bigint DEFAULT '0' COMMENT '故障耗时毫秒',
|
`crn_fault_count` int DEFAULT '0' COMMENT '单堆垛机故障次数',
|
`crn_fault_duration_ms` bigint DEFAULT '0' COMMENT '单堆垛机故障耗时毫秒',
|
`dual_crn_fault_count` int DEFAULT '0' COMMENT '双工位堆垛机故障次数',
|
`dual_crn_fault_duration_ms` bigint DEFAULT '0' COMMENT '双工位堆垛机故障耗时毫秒',
|
`rgv_fault_count` int DEFAULT '0' COMMENT 'RGV故障次数',
|
`rgv_fault_duration_ms` bigint DEFAULT '0' COMMENT 'RGV故障耗时毫秒',
|
`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`),
|
KEY `idx_wrk_analysis_finish_time` (`finish_time`),
|
KEY `idx_wrk_analysis_appe_time` (`appe_time`),
|
KEY `idx_wrk_analysis_io_type` (`io_type`),
|
KEY `idx_wrk_analysis_final_wrk_sts` (`final_wrk_sts`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='任务执行分析汇总表';
|
|
SET @wrk_analysis_inbound_status_migrated := CASE
|
WHEN EXISTS (
|
SELECT 1
|
FROM `asr_bas_wrk_status`
|
WHERE `wrk_sts` = 3
|
AND `wrk_desc` = '站点运行完成'
|
) THEN 1
|
ELSE 0
|
END;
|
|
UPDATE `asr_wrk_mast`
|
SET `wrk_sts` = CASE
|
WHEN `wrk_sts` = 3 THEN 4
|
WHEN `wrk_sts` = 4 THEN 5
|
ELSE `wrk_sts`
|
END
|
WHERE @wrk_analysis_inbound_status_migrated = 0
|
AND `wrk_sts` IN (3, 4);
|
|
UPDATE `asr_wrk_mast_log`
|
SET `wrk_sts` = CASE
|
WHEN `wrk_sts` = 3 THEN 4
|
WHEN `wrk_sts` = 4 THEN 5
|
ELSE `wrk_sts`
|
END
|
WHERE @wrk_analysis_inbound_status_migrated = 0
|
AND `wrk_sts` IN (3, 4);
|
|
UPDATE `asr_bas_crnp_err_log`
|
SET `wrk_sts` = CASE
|
WHEN `wrk_sts` = 3 THEN 4
|
WHEN `wrk_sts` = 4 THEN 5
|
ELSE `wrk_sts`
|
END
|
WHERE @wrk_analysis_inbound_status_migrated = 0
|
AND `wrk_sts` IN (3, 4);
|
|
UPDATE `asr_bas_dual_crnp_err_log`
|
SET `wrk_sts` = CASE
|
WHEN `wrk_sts` = 3 THEN 4
|
WHEN `wrk_sts` = 4 THEN 5
|
ELSE `wrk_sts`
|
END
|
WHERE @wrk_analysis_inbound_status_migrated = 0
|
AND `wrk_sts` IN (3, 4);
|
|
UPDATE `asr_bas_rgv_err_log`
|
SET `wrk_sts` = CASE
|
WHEN `wrk_sts` = 3 THEN 4
|
WHEN `wrk_sts` = 4 THEN 5
|
ELSE `wrk_sts`
|
END
|
WHERE @wrk_analysis_inbound_status_migrated = 0
|
AND `wrk_sts` IN (3, 4);
|
|
INSERT INTO `asr_bas_wrk_status` (`wrk_sts`, `wrk_desc`, `appe_time`, `modi_time`)
|
VALUES (3, '站点运行完成', NOW(), NOW())
|
ON DUPLICATE KEY UPDATE
|
`wrk_desc` = VALUES(`wrk_desc`),
|
`modi_time` = NOW();
|
|
INSERT INTO `asr_bas_wrk_status` (`wrk_sts`, `wrk_desc`, `appe_time`, `modi_time`)
|
VALUES (4, '设备搬运中', NOW(), NOW())
|
ON DUPLICATE KEY UPDATE
|
`wrk_desc` = VALUES(`wrk_desc`),
|
`modi_time` = NOW();
|
|
INSERT INTO `asr_bas_wrk_status` (`wrk_sts`, `wrk_desc`, `appe_time`, `modi_time`)
|
VALUES (5, '设备搬运完成', NOW(), NOW())
|
ON DUPLICATE KEY UPDATE
|
`wrk_desc` = VALUES(`wrk_desc`),
|
`modi_time` = NOW();
|
|
UPDATE `asr_bas_wrk_status`
|
SET `wrk_desc` = '站点运行中',
|
`modi_time` = NOW()
|
WHERE `wrk_sts` = 2;
|
|
INSERT INTO `asr_wrk_analysis` (
|
`wrk_no`,
|
`wms_wrk_no`,
|
`io_type`,
|
`final_wrk_sts`,
|
`source_sta_no`,
|
`sta_no`,
|
`source_loc_no`,
|
`loc_no`,
|
`crn_no`,
|
`dual_crn_no`,
|
`rgv_no`,
|
`appe_time`,
|
`finish_time`,
|
`total_duration_ms`,
|
`station_duration_ms`,
|
`crane_duration_ms`,
|
`has_fault`,
|
`fault_count`,
|
`fault_duration_ms`,
|
`crn_fault_count`,
|
`crn_fault_duration_ms`,
|
`dual_crn_fault_count`,
|
`dual_crn_fault_duration_ms`,
|
`rgv_fault_count`,
|
`rgv_fault_duration_ms`,
|
`metric_completeness`,
|
`create_time`,
|
`update_time`
|
)
|
SELECT
|
l.`wrk_no`,
|
l.`wms_wrk_no`,
|
l.`io_type`,
|
l.`wrk_sts`,
|
l.`source_sta_no`,
|
l.`sta_no`,
|
l.`source_loc_no`,
|
l.`loc_no`,
|
l.`crn_no`,
|
l.`dual_crn_no`,
|
l.`rgv_no`,
|
l.`appe_time`,
|
COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`) AS `finish_time`,
|
CASE
|
WHEN l.`appe_time` IS NULL OR COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`) IS NULL THEN NULL
|
ELSE GREATEST(TIMESTAMPDIFF(MICROSECOND, l.`appe_time`, COALESCE(l.`modi_time`, l.`io_time`, l.`appe_time`)), 0) DIV 1000
|
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`,
|
'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
|
`wms_wrk_no` = VALUES(`wms_wrk_no`),
|
`io_type` = VALUES(`io_type`),
|
`final_wrk_sts` = VALUES(`final_wrk_sts`),
|
`source_sta_no` = VALUES(`source_sta_no`),
|
`sta_no` = VALUES(`sta_no`),
|
`source_loc_no` = VALUES(`source_loc_no`),
|
`loc_no` = VALUES(`loc_no`),
|
`crn_no` = VALUES(`crn_no`),
|
`dual_crn_no` = VALUES(`dual_crn_no`),
|
`rgv_no` = VALUES(`rgv_no`),
|
`appe_time` = VALUES(`appe_time`),
|
`finish_time` = VALUES(`finish_time`),
|
`total_duration_ms` = VALUES(`total_duration_ms`),
|
`station_duration_ms` = VALUES(`station_duration_ms`),
|
`crane_duration_ms` = VALUES(`crane_duration_ms`),
|
`has_fault` = VALUES(`has_fault`),
|
`fault_count` = VALUES(`fault_count`),
|
`fault_duration_ms` = VALUES(`fault_duration_ms`),
|
`crn_fault_count` = VALUES(`crn_fault_count`),
|
`crn_fault_duration_ms` = VALUES(`crn_fault_duration_ms`),
|
`dual_crn_fault_count` = VALUES(`dual_crn_fault_count`),
|
`dual_crn_fault_duration_ms` = VALUES(`dual_crn_fault_duration_ms`),
|
`rgv_fault_count` = VALUES(`rgv_fault_count`),
|
`rgv_fault_duration_ms` = VALUES(`rgv_fault_duration_ms`),
|
`metric_completeness` = VALUES(`metric_completeness`),
|
`update_time` = NOW();
|
|
SET @wrk_analysis_parent_id := COALESCE(
|
(
|
SELECT `id`
|
FROM `sys_resource`
|
WHERE `code` = 'logReport' AND `level` = 1
|
ORDER BY `id`
|
LIMIT 1
|
),
|
(
|
SELECT `id`
|
FROM `sys_resource`
|
WHERE `code` = 'develop' AND `level` = 1
|
ORDER BY `id`
|
LIMIT 1
|
)
|
);
|
|
INSERT INTO `sys_resource`(`code`, `name`, `resource_id`, `level`, `sort`, `status`)
|
SELECT 'wrkAnalysis/wrkAnalysis.html', '任务执行分析', @wrk_analysis_parent_id, 2, 996, 1
|
FROM dual
|
WHERE @wrk_analysis_parent_id IS NOT NULL
|
AND NOT EXISTS (
|
SELECT 1
|
FROM `sys_resource`
|
WHERE `code` = 'wrkAnalysis/wrkAnalysis.html' AND `level` = 2
|
);
|
|
UPDATE `sys_resource`
|
SET `name` = '任务执行分析',
|
`resource_id` = @wrk_analysis_parent_id,
|
`level` = 2,
|
`sort` = 996,
|
`status` = 1
|
WHERE `code` = 'wrkAnalysis/wrkAnalysis.html' AND `level` = 2;
|
|
SET @wrk_analysis_id := (
|
SELECT `id`
|
FROM `sys_resource`
|
WHERE `code` = 'wrkAnalysis/wrkAnalysis.html' AND `level` = 2
|
ORDER BY `id`
|
LIMIT 1
|
);
|
|
INSERT INTO `sys_resource`(`code`, `name`, `resource_id`, `level`, `sort`, `status`)
|
SELECT 'wrkAnalysis/wrkAnalysis.html#view', '查看', @wrk_analysis_id, 3, 1, 1
|
FROM dual
|
WHERE @wrk_analysis_id IS NOT NULL
|
AND NOT EXISTS (
|
SELECT 1
|
FROM `sys_resource`
|
WHERE `code` = 'wrkAnalysis/wrkAnalysis.html#view' AND `level` = 3
|
);
|
|
UPDATE `sys_resource`
|
SET `name` = '查看',
|
`resource_id` = @wrk_analysis_id,
|
`level` = 3,
|
`sort` = 1,
|
`status` = 1
|
WHERE `code` = 'wrkAnalysis/wrkAnalysis.html#view' AND `level` = 3;
|
