CREATE TABLE IF NOT EXISTS `asr_bas_station_err_log` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
|
`wrk_no` int DEFAULT NULL COMMENT '工作号',
|
`start_time` datetime DEFAULT NULL COMMENT '发生时间',
|
`end_time` datetime DEFAULT NULL COMMENT '结束时间',
|
`wrk_sts` bigint DEFAULT NULL COMMENT '工作状态',
|
`io_type` int DEFAULT NULL COMMENT '入出库类型',
|
`station_id` int DEFAULT NULL COMMENT '站点号',
|
`loc_no` varchar(64) DEFAULT NULL COMMENT '目标库位',
|
`sta_no` int DEFAULT NULL COMMENT '目标站',
|
`source_sta_no` int DEFAULT NULL COMMENT '源站',
|
`source_loc_no` varchar(64) DEFAULT NULL COMMENT '源库位',
|
`barcode` varchar(128) DEFAULT NULL COMMENT '条码',
|
`err_code` int DEFAULT NULL COMMENT '异常码',
|
`error` text COMMENT '异常描述',
|
`status` int DEFAULT NULL COMMENT '异常情况 1未处理 2已修复',
|
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
|
`create_by` bigint DEFAULT NULL COMMENT '创建人',
|
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
|
`update_by` bigint DEFAULT NULL COMMENT '更新人',
|
`memo` varchar(255) DEFAULT NULL COMMENT '备注',
|
`system_status` longtext COMMENT '系统状态快照',
|
PRIMARY KEY (`id`),
|
KEY `idx_station_err_log_wrk_no` (`wrk_no`),
|
KEY `idx_station_err_log_station_id` (`station_id`),
|
KEY `idx_station_err_log_status` (`status`),
|
KEY `idx_station_err_log_start_time` (`start_time`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='输送站点异常日志';
|
|
SET @wrk_analysis_table_exists := (
|
SELECT COUNT(*)
|
FROM `information_schema`.`TABLES`
|
WHERE `TABLE_SCHEMA` = DATABASE()
|
AND `TABLE_NAME` = 'asr_wrk_analysis'
|
);
|
|
SET @station_fault_count_exists := (
|
SELECT COUNT(*)
|
FROM `information_schema`.`COLUMNS`
|
WHERE `TABLE_SCHEMA` = DATABASE()
|
AND `TABLE_NAME` = 'asr_wrk_analysis'
|
AND `COLUMN_NAME` = 'station_fault_count'
|
);
|
|
SET @station_fault_duration_exists := (
|
SELECT COUNT(*)
|
FROM `information_schema`.`COLUMNS`
|
WHERE `TABLE_SCHEMA` = DATABASE()
|
AND `TABLE_NAME` = 'asr_wrk_analysis'
|
AND `COLUMN_NAME` = 'station_fault_duration_ms'
|
);
|
|
SET @sql_station_fault_count := IF(
|
@wrk_analysis_table_exists = 1 AND @station_fault_count_exists = 0,
|
'ALTER TABLE `asr_wrk_analysis` ADD COLUMN `station_fault_count` int DEFAULT 0 COMMENT ''输送站点故障次数'' AFTER `rgv_fault_duration_ms`',
|
'SELECT 1'
|
);
|
PREPARE stmt_station_fault_count FROM @sql_station_fault_count;
|
EXECUTE stmt_station_fault_count;
|
DEALLOCATE PREPARE stmt_station_fault_count;
|
|
SET @sql_station_fault_duration := IF(
|
@wrk_analysis_table_exists = 1 AND @station_fault_duration_exists = 0,
|
'ALTER TABLE `asr_wrk_analysis` ADD COLUMN `station_fault_duration_ms` bigint DEFAULT 0 COMMENT ''输送站点故障耗时毫秒'' AFTER `station_fault_count`',
|
'SELECT 1'
|
);
|
PREPARE stmt_station_fault_duration FROM @sql_station_fault_duration;
|
EXECUTE stmt_station_fault_duration;
|
DEALLOCATE PREPARE stmt_station_fault_duration;
|
|
UPDATE `asr_wrk_analysis` a
|
SET a.`station_fault_count` = COALESCE((
|
SELECT COUNT(*)
|
FROM `asr_bas_station_err_log` e
|
WHERE e.`wrk_no` = a.`wrk_no`
|
AND (e.`start_time` IS NULL OR e.`start_time` <= COALESCE(a.`finish_time`, a.`update_time`, a.`appe_time`))
|
AND COALESCE(e.`end_time`, e.`start_time`, COALESCE(a.`finish_time`, a.`update_time`, a.`appe_time`)) >= COALESCE(a.`appe_time`, a.`finish_time`, a.`update_time`)
|
), 0),
|
a.`station_fault_duration_ms` = COALESCE((
|
SELECT SUM(
|
CASE
|
WHEN e.`start_time` IS NULL THEN 0
|
ELSE GREATEST(
|
TIMESTAMPDIFF(
|
MICROSECOND,
|
GREATEST(e.`start_time`, COALESCE(a.`appe_time`, a.`finish_time`, a.`update_time`)),
|
LEAST(COALESCE(e.`end_time`, COALESCE(a.`finish_time`, a.`update_time`, a.`appe_time`)), COALESCE(a.`finish_time`, a.`update_time`, a.`appe_time`))
|
),
|
0
|
) DIV 1000
|
END
|
)
|
FROM `asr_bas_station_err_log` e
|
WHERE e.`wrk_no` = a.`wrk_no`
|
AND (e.`start_time` IS NULL OR e.`start_time` <= COALESCE(a.`finish_time`, a.`update_time`, a.`appe_time`))
|
AND COALESCE(e.`end_time`, e.`start_time`, COALESCE(a.`finish_time`, a.`update_time`, a.`appe_time`)) >= COALESCE(a.`appe_time`, a.`finish_time`, a.`update_time`)
|
), 0),
|
a.`fault_count` = COALESCE(a.`crn_fault_count`, 0)
|
+ COALESCE(a.`dual_crn_fault_count`, 0)
|
+ COALESCE(a.`rgv_fault_count`, 0)
|
+ COALESCE((
|
SELECT COUNT(*)
|
FROM `asr_bas_station_err_log` e
|
WHERE e.`wrk_no` = a.`wrk_no`
|
AND (e.`start_time` IS NULL OR e.`start_time` <= COALESCE(a.`finish_time`, a.`update_time`, a.`appe_time`))
|
AND COALESCE(e.`end_time`, e.`start_time`, COALESCE(a.`finish_time`, a.`update_time`, a.`appe_time`)) >= COALESCE(a.`appe_time`, a.`finish_time`, a.`update_time`)
|
), 0),
|
a.`fault_duration_ms` = COALESCE(a.`crn_fault_duration_ms`, 0)
|
+ COALESCE(a.`dual_crn_fault_duration_ms`, 0)
|
+ COALESCE(a.`rgv_fault_duration_ms`, 0)
|
+ COALESCE((
|
SELECT SUM(
|
CASE
|
WHEN e.`start_time` IS NULL THEN 0
|
ELSE GREATEST(
|
TIMESTAMPDIFF(
|
MICROSECOND,
|
GREATEST(e.`start_time`, COALESCE(a.`appe_time`, a.`finish_time`, a.`update_time`)),
|
LEAST(COALESCE(e.`end_time`, COALESCE(a.`finish_time`, a.`update_time`, a.`appe_time`)), COALESCE(a.`finish_time`, a.`update_time`, a.`appe_time`))
|
),
|
0
|
) DIV 1000
|
END
|
)
|
FROM `asr_bas_station_err_log` e
|
WHERE e.`wrk_no` = a.`wrk_no`
|
AND (e.`start_time` IS NULL OR e.`start_time` <= COALESCE(a.`finish_time`, a.`update_time`, a.`appe_time`))
|
AND COALESCE(e.`end_time`, e.`start_time`, COALESCE(a.`finish_time`, a.`update_time`, a.`appe_time`)) >= COALESCE(a.`appe_time`, a.`finish_time`, a.`update_time`)
|
), 0),
|
a.`has_fault` = CASE
|
WHEN COALESCE(a.`crn_fault_count`, 0)
|
+ COALESCE(a.`dual_crn_fault_count`, 0)
|
+ COALESCE(a.`rgv_fault_count`, 0)
|
+ COALESCE((
|
SELECT COUNT(*)
|
FROM `asr_bas_station_err_log` e
|
WHERE e.`wrk_no` = a.`wrk_no`
|
AND (e.`start_time` IS NULL OR e.`start_time` <= COALESCE(a.`finish_time`, a.`update_time`, a.`appe_time`))
|
AND COALESCE(e.`end_time`, e.`start_time`, COALESCE(a.`finish_time`, a.`update_time`, a.`appe_time`)) >= COALESCE(a.`appe_time`, a.`finish_time`, a.`update_time`)
|
), 0) > 0 THEN 1
|
ELSE 0
|
END,
|
a.`update_time` = NOW()
|
WHERE @wrk_analysis_table_exists = 1;
|
|
SET @station_err_log_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 'basStationErrLog/basStationErrLog.html', '输送站点异常日志', @station_err_log_parent_id, 2, 995, 1
|
FROM dual
|
WHERE @station_err_log_parent_id IS NOT NULL
|
AND NOT EXISTS (
|
SELECT 1
|
FROM `sys_resource`
|
WHERE `code` = 'basStationErrLog/basStationErrLog.html' AND `level` = 2
|
);
|
|
UPDATE `sys_resource`
|
SET `name` = '输送站点异常日志',
|
`resource_id` = @station_err_log_parent_id,
|
`level` = 2,
|
`sort` = 995,
|
`status` = 1
|
WHERE `code` = 'basStationErrLog/basStationErrLog.html' AND `level` = 2;
|
|
SET @station_err_log_id := (
|
SELECT `id`
|
FROM `sys_resource`
|
WHERE `code` = 'basStationErrLog/basStationErrLog.html' AND `level` = 2
|
ORDER BY `id`
|
LIMIT 1
|
);
|
|
INSERT INTO `sys_resource`(`code`, `name`, `resource_id`, `level`, `sort`, `status`)
|
SELECT 'basStationErrLog/basStationErrLog.html#view', '查看', @station_err_log_id, 3, 1, 1
|
FROM dual
|
WHERE @station_err_log_id IS NOT NULL
|
AND NOT EXISTS (
|
SELECT 1
|
FROM `sys_resource`
|
WHERE `code` = 'basStationErrLog/basStationErrLog.html#view' AND `level` = 3
|
);
|
|
UPDATE `sys_resource`
|
SET `name` = '查看',
|
`resource_id` = @station_err_log_id,
|
`level` = 3,
|
`sort` = 1,
|
`status` = 1
|
WHERE `code` = 'basStationErrLog/basStationErrLog.html#view' AND `level` = 3;
|
