795 lines
42 KiB
SQL
795 lines
42 KiB
SQL
-- =============================================
|
||
-- 设备点巡检优化 v1.0.74
|
||
-- 作者: 周启威
|
||
-- 日期: 2026-01-13
|
||
-- 描述:
|
||
-- 1. 新增设备点位表（关联设备）
|
||
-- 2. 计划明细表、执行记录明细表
|
||
-- 3. 维修单点位扩展
|
||
-- 4. 菜单结构调整（点检、巡检、保养、维修四大模块）
|
||
-- 5. 简化流程：去掉标准层，直接在计划中选择设备点位
|
||
-- 6. 维修单增加故障描述字段
|
||
-- =============================================
|
||
|
||
-- =============================================
|
||
-- 一、新增表结构
|
||
-- =============================================
|
||
|
||
-- 1.1 设备点位表（关联设备，作为基础数据）
|
||
CREATE TABLE IF NOT EXISTS `dm_inspection_item_point` (
|
||
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`item_id` BIGINT DEFAULT NULL COMMENT '关联点检项目ID（可选，兼容旧数据）',
|
||
`equipment_id` BIGINT DEFAULT NULL COMMENT '关联设备ID',
|
||
`equipment_name` VARCHAR(100) DEFAULT NULL COMMENT '设备名称',
|
||
`point_name` VARCHAR(100) NOT NULL COMMENT '点位名称',
|
||
`point_code` VARCHAR(50) DEFAULT NULL COMMENT '点位编码',
|
||
`standard_value` VARCHAR(100) DEFAULT NULL COMMENT '标准值',
|
||
`upper_limit` VARCHAR(50) DEFAULT NULL COMMENT '上限值',
|
||
`lower_limit` VARCHAR(50) DEFAULT NULL COMMENT '下限值',
|
||
`unit` VARCHAR(20) DEFAULT NULL COMMENT '单位',
|
||
`check_method` VARCHAR(200) DEFAULT NULL COMMENT '检查方法',
|
||
`sort_order` INT DEFAULT 0 COMMENT '排序号',
|
||
`remark` VARCHAR(500) DEFAULT NULL COMMENT '备注',
|
||
`create_by` VARCHAR(32) DEFAULT NULL COMMENT '创建人',
|
||
`create_time` DATETIME DEFAULT NULL COMMENT '创建时间',
|
||
`update_by` VARCHAR(32) DEFAULT NULL COMMENT '更新人',
|
||
`update_time` DATETIME DEFAULT NULL COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_item_id` (`item_id`),
|
||
KEY `idx_equipment_id` (`equipment_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='设备点位表';
|
||
|
||
-- 1.2 计划点位明细表（计划关联的点位）
|
||
-- 注意：如果表已存在，需要执行下面的ALTER语句修改字段
|
||
CREATE TABLE IF NOT EXISTS `dm_inspection_plan_item` (
|
||
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`plan_id` BIGINT NOT NULL COMMENT '关联计划ID',
|
||
`item_id` BIGINT DEFAULT NULL COMMENT '关联点检项目ID（可选，兼容旧数据）',
|
||
`item_number` VARCHAR(50) DEFAULT NULL COMMENT '项目编号',
|
||
`item_name` VARCHAR(100) DEFAULT NULL COMMENT '项目名称',
|
||
`item_content` VARCHAR(500) DEFAULT NULL COMMENT '项目内容',
|
||
`item_standard` VARCHAR(500) DEFAULT NULL COMMENT '项目标准',
|
||
`equipment_id` BIGINT DEFAULT NULL COMMENT '关联设备ID',
|
||
`equipment_name` VARCHAR(100) DEFAULT NULL COMMENT '设备名称',
|
||
`point_id` BIGINT DEFAULT NULL COMMENT '关联点位ID',
|
||
`point_name` VARCHAR(100) DEFAULT NULL COMMENT '点位名称',
|
||
`point_code` VARCHAR(50) DEFAULT NULL COMMENT '点位编码',
|
||
`standard_value` VARCHAR(100) DEFAULT NULL COMMENT '标准值',
|
||
`upper_limit` VARCHAR(50) DEFAULT NULL COMMENT '上限值',
|
||
`lower_limit` VARCHAR(50) DEFAULT NULL COMMENT '下限值',
|
||
`unit` VARCHAR(20) DEFAULT NULL COMMENT '单位',
|
||
`check_method` VARCHAR(200) DEFAULT NULL COMMENT '检查方法',
|
||
`sort_order` INT DEFAULT 0 COMMENT '排序号',
|
||
`create_by` VARCHAR(32) DEFAULT NULL COMMENT '创建人',
|
||
`create_time` DATETIME DEFAULT NULL COMMENT '创建时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_plan_id` (`plan_id`),
|
||
KEY `idx_equipment_id` (`equipment_id`),
|
||
KEY `idx_point_id` (`point_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='计划点位明细表';
|
||
|
||
-- 1.2.1 修复已存在的dm_inspection_plan_item表（如果表已存在，修改字段允许为空并添加新字段）
|
||
SET @dbname = DATABASE();
|
||
SET @tablename = 'dm_inspection_plan_item';
|
||
|
||
-- 修改 item_id 字段允许为空
|
||
SET @columnname = 'item_id';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
CONCAT('ALTER TABLE ', @tablename, ' MODIFY COLUMN ', @columnname, ' BIGINT DEFAULT NULL COMMENT ''关联点检项目ID（可选）'''),
|
||
'SELECT 1'
|
||
));
|
||
PREPARE alterStatement FROM @preparedStatement;
|
||
EXECUTE alterStatement;
|
||
DEALLOCATE PREPARE alterStatement;
|
||
|
||
-- 修改 item_number 字段允许为空
|
||
SET @columnname = 'item_number';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
CONCAT('ALTER TABLE ', @tablename, ' MODIFY COLUMN ', @columnname, ' VARCHAR(50) DEFAULT NULL COMMENT ''项目编号'''),
|
||
'SELECT 1'
|
||
));
|
||
PREPARE alterStatement FROM @preparedStatement;
|
||
EXECUTE alterStatement;
|
||
DEALLOCATE PREPARE alterStatement;
|
||
|
||
-- 修改 item_name 字段允许为空
|
||
SET @columnname = 'item_name';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
CONCAT('ALTER TABLE ', @tablename, ' MODIFY COLUMN ', @columnname, ' VARCHAR(100) DEFAULT NULL COMMENT ''项目名称'''),
|
||
'SELECT 1'
|
||
));
|
||
PREPARE alterStatement FROM @preparedStatement;
|
||
EXECUTE alterStatement;
|
||
DEALLOCATE PREPARE alterStatement;
|
||
|
||
-- 修改 item_content 字段允许为空
|
||
SET @columnname = 'item_content';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
CONCAT('ALTER TABLE ', @tablename, ' MODIFY COLUMN ', @columnname, ' VARCHAR(500) DEFAULT NULL COMMENT ''项目内容'''),
|
||
'SELECT 1'
|
||
));
|
||
PREPARE alterStatement FROM @preparedStatement;
|
||
EXECUTE alterStatement;
|
||
DEALLOCATE PREPARE alterStatement;
|
||
|
||
-- 修改 item_standard 字段允许为空
|
||
SET @columnname = 'item_standard';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
CONCAT('ALTER TABLE ', @tablename, ' MODIFY COLUMN ', @columnname, ' VARCHAR(500) DEFAULT NULL COMMENT ''项目标准'''),
|
||
'SELECT 1'
|
||
));
|
||
PREPARE alterStatement FROM @preparedStatement;
|
||
EXECUTE alterStatement;
|
||
DEALLOCATE PREPARE alterStatement;
|
||
|
||
-- 添加 equipment_id 字段
|
||
SET @columnname = 'equipment_id';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' BIGINT DEFAULT NULL COMMENT ''关联设备ID'' AFTER `item_standard`')
|
||
));
|
||
PREPARE alterStatement FROM @preparedStatement;
|
||
EXECUTE alterStatement;
|
||
DEALLOCATE PREPARE alterStatement;
|
||
|
||
-- 添加 equipment_name 字段
|
||
SET @columnname = 'equipment_name';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(100) DEFAULT NULL COMMENT ''设备名称'' AFTER `equipment_id`')
|
||
));
|
||
PREPARE alterStatement FROM @preparedStatement;
|
||
EXECUTE alterStatement;
|
||
DEALLOCATE PREPARE alterStatement;
|
||
|
||
-- 添加 point_id 字段
|
||
SET @columnname = 'point_id';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' BIGINT DEFAULT NULL COMMENT ''关联点位ID'' AFTER `equipment_name`')
|
||
));
|
||
PREPARE alterStatement FROM @preparedStatement;
|
||
EXECUTE alterStatement;
|
||
DEALLOCATE PREPARE alterStatement;
|
||
|
||
-- 添加 point_name 字段
|
||
SET @columnname = 'point_name';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(100) DEFAULT NULL COMMENT ''点位名称'' AFTER `point_id`')
|
||
));
|
||
PREPARE alterStatement FROM @preparedStatement;
|
||
EXECUTE alterStatement;
|
||
DEALLOCATE PREPARE alterStatement;
|
||
|
||
-- 添加 point_code 字段
|
||
SET @columnname = 'point_code';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(50) DEFAULT NULL COMMENT ''点位编码'' AFTER `point_name`')
|
||
));
|
||
PREPARE alterStatement FROM @preparedStatement;
|
||
EXECUTE alterStatement;
|
||
DEALLOCATE PREPARE alterStatement;
|
||
|
||
-- 添加 standard_value 字段
|
||
SET @columnname = 'standard_value';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(100) DEFAULT NULL COMMENT ''标准值'' AFTER `point_code`')
|
||
));
|
||
PREPARE alterStatement FROM @preparedStatement;
|
||
EXECUTE alterStatement;
|
||
DEALLOCATE PREPARE alterStatement;
|
||
|
||
-- 添加 upper_limit 字段
|
||
SET @columnname = 'upper_limit';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(50) DEFAULT NULL COMMENT ''上限值'' AFTER `standard_value`')
|
||
));
|
||
PREPARE alterStatement FROM @preparedStatement;
|
||
EXECUTE alterStatement;
|
||
DEALLOCATE PREPARE alterStatement;
|
||
|
||
-- 添加 lower_limit 字段
|
||
SET @columnname = 'lower_limit';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(50) DEFAULT NULL COMMENT ''下限值'' AFTER `upper_limit`')
|
||
));
|
||
PREPARE alterStatement FROM @preparedStatement;
|
||
EXECUTE alterStatement;
|
||
DEALLOCATE PREPARE alterStatement;
|
||
|
||
-- 添加 unit 字段
|
||
SET @columnname = 'unit';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(20) DEFAULT NULL COMMENT ''单位'' AFTER `lower_limit`')
|
||
));
|
||
PREPARE alterStatement FROM @preparedStatement;
|
||
EXECUTE alterStatement;
|
||
DEALLOCATE PREPARE alterStatement;
|
||
|
||
-- 添加 check_method 字段
|
||
SET @columnname = 'check_method';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(200) DEFAULT NULL COMMENT ''检查方法'' AFTER `unit`')
|
||
));
|
||
PREPARE alterStatement FROM @preparedStatement;
|
||
EXECUTE alterStatement;
|
||
DEALLOCATE PREPARE alterStatement;
|
||
|
||
-- 1.3 执行记录明细表（记录每个点位的执行结果）
|
||
CREATE TABLE IF NOT EXISTS `dm_inspection_record_item` (
|
||
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`record_id` BIGINT NOT NULL COMMENT '关联执行记录ID',
|
||
`point_id` BIGINT DEFAULT NULL COMMENT '关联点位ID',
|
||
`point_name` VARCHAR(100) DEFAULT NULL COMMENT '点位名称',
|
||
`point_code` VARCHAR(50) DEFAULT NULL COMMENT '点位编码',
|
||
`standard_value` VARCHAR(100) DEFAULT NULL COMMENT '标准值',
|
||
`upper_limit` VARCHAR(50) DEFAULT NULL COMMENT '上限值',
|
||
`lower_limit` VARCHAR(50) DEFAULT NULL COMMENT '下限值',
|
||
`unit` VARCHAR(20) DEFAULT NULL COMMENT '单位',
|
||
`result` VARCHAR(20) DEFAULT NULL COMMENT '执行结果（normal正常/abnormal异常）',
|
||
`result_value` VARCHAR(100) DEFAULT NULL COMMENT '检测值',
|
||
`problem_desc` TEXT DEFAULT NULL COMMENT '问题描述',
|
||
`create_by` VARCHAR(32) DEFAULT NULL COMMENT '创建人',
|
||
`create_time` DATETIME DEFAULT NULL COMMENT '创建时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_record_id` (`record_id`),
|
||
KEY `idx_point_id` (`point_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='执行记录明细表';
|
||
|
||
-- =============================================
|
||
-- 二、扩展现有表结构
|
||
-- =============================================
|
||
|
||
-- 2.1 扩展巡检计划表，增加保养类型字段
|
||
-- MySQL 5.x 兼容写法，先检查字段是否存在
|
||
SET @dbname = DATABASE();
|
||
SET @tablename = 'dm_inspection_plan';
|
||
|
||
-- 添加 maintenance_type 字段
|
||
SET @columnname = 'maintenance_type';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(20) DEFAULT NULL COMMENT ''保养类型（daily日常保养/periodic定期保养/overhaul大修）'' AFTER `type`')
|
||
));
|
||
PREPARE alterIfNotExists FROM @preparedStatement;
|
||
EXECUTE alterIfNotExists;
|
||
DEALLOCATE PREPARE alterIfNotExists;
|
||
|
||
-- 添加 equipment_id 字段（单设备关联）
|
||
SET @columnname = 'equipment_id';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' BIGINT DEFAULT NULL COMMENT ''关联设备ID'' AFTER `executor_name`')
|
||
));
|
||
PREPARE alterIfNotExists FROM @preparedStatement;
|
||
EXECUTE alterIfNotExists;
|
||
DEALLOCATE PREPARE alterIfNotExists;
|
||
|
||
-- 添加 equipment_name 字段（单设备名称）
|
||
SET @columnname = 'equipment_name';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(100) DEFAULT NULL COMMENT ''关联设备名称'' AFTER `equipment_id`')
|
||
));
|
||
PREPARE alterIfNotExists FROM @preparedStatement;
|
||
EXECUTE alterIfNotExists;
|
||
DEALLOCATE PREPARE alterIfNotExists;
|
||
|
||
-- 添加 equipment_ids 字段（多设备ID，逗号分隔）
|
||
SET @columnname = 'equipment_ids';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(500) DEFAULT NULL COMMENT ''关联设备ID（多个用逗号分隔）'' AFTER `equipment_name`')
|
||
));
|
||
PREPARE alterIfNotExists FROM @preparedStatement;
|
||
EXECUTE alterIfNotExists;
|
||
DEALLOCATE PREPARE alterIfNotExists;
|
||
|
||
-- 添加 equipment_names 字段（多设备名称，逗号分隔）
|
||
SET @columnname = 'equipment_names';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(1000) DEFAULT NULL COMMENT ''关联设备名称（多个用逗号分隔）'' AFTER `equipment_ids`')
|
||
));
|
||
PREPARE alterIfNotExists FROM @preparedStatement;
|
||
EXECUTE alterIfNotExists;
|
||
DEALLOCATE PREPARE alterIfNotExists;
|
||
|
||
-- 2.2 扩展设备项目表，增加备件字段和多设备绑定字段
|
||
SET @tablename = 'dm_inspection_item';
|
||
|
||
SET @columnname = 'spare_parts';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(500) DEFAULT NULL COMMENT ''所需备件'' AFTER `equipment_point`')
|
||
));
|
||
PREPARE alterIfNotExists FROM @preparedStatement;
|
||
EXECUTE alterIfNotExists;
|
||
DEALLOCATE PREPARE alterIfNotExists;
|
||
|
||
SET @columnname = 'equipment_ids';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(500) DEFAULT NULL COMMENT ''绑定设备ID（多个用逗号分隔）'' AFTER `equipment_id`')
|
||
));
|
||
PREPARE alterIfNotExists FROM @preparedStatement;
|
||
EXECUTE alterIfNotExists;
|
||
DEALLOCATE PREPARE alterIfNotExists;
|
||
|
||
-- 2.3 扩展维修单明细表，增加点位相关字段
|
||
SET @tablename = 'dm_repair_order_entry';
|
||
|
||
SET @columnname = 'point_id';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' BIGINT DEFAULT NULL COMMENT ''关联点位ID'' AFTER `item_id`')
|
||
));
|
||
PREPARE alterIfNotExists FROM @preparedStatement;
|
||
EXECUTE alterIfNotExists;
|
||
DEALLOCATE PREPARE alterIfNotExists;
|
||
|
||
SET @columnname = 'point_name';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(100) DEFAULT NULL COMMENT ''点位名称'' AFTER `point_id`')
|
||
));
|
||
PREPARE alterIfNotExists FROM @preparedStatement;
|
||
EXECUTE alterIfNotExists;
|
||
DEALLOCATE PREPARE alterIfNotExists;
|
||
|
||
SET @columnname = 'point_code';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(50) DEFAULT NULL COMMENT ''点位编码'' AFTER `point_name`')
|
||
));
|
||
PREPARE alterIfNotExists FROM @preparedStatement;
|
||
EXECUTE alterIfNotExists;
|
||
DEALLOCATE PREPARE alterIfNotExists;
|
||
|
||
SET @columnname = 'standard_value';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(100) DEFAULT NULL COMMENT ''标准值'' AFTER `point_code`')
|
||
));
|
||
PREPARE alterIfNotExists FROM @preparedStatement;
|
||
EXECUTE alterIfNotExists;
|
||
DEALLOCATE PREPARE alterIfNotExists;
|
||
|
||
SET @columnname = 'upper_limit';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(50) DEFAULT NULL COMMENT ''上限值'' AFTER `standard_value`')
|
||
));
|
||
PREPARE alterIfNotExists FROM @preparedStatement;
|
||
EXECUTE alterIfNotExists;
|
||
DEALLOCATE PREPARE alterIfNotExists;
|
||
|
||
SET @columnname = 'lower_limit';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(50) DEFAULT NULL COMMENT ''下限值'' AFTER `upper_limit`')
|
||
));
|
||
PREPARE alterIfNotExists FROM @preparedStatement;
|
||
EXECUTE alterIfNotExists;
|
||
DEALLOCATE PREPARE alterIfNotExists;
|
||
|
||
SET @columnname = 'unit';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(20) DEFAULT NULL COMMENT ''单位'' AFTER `lower_limit`')
|
||
));
|
||
PREPARE alterIfNotExists FROM @preparedStatement;
|
||
EXECUTE alterIfNotExists;
|
||
DEALLOCATE PREPARE alterIfNotExists;
|
||
|
||
SET @columnname = 'result_value';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(100) DEFAULT NULL COMMENT ''检测值'' AFTER `unit`')
|
||
));
|
||
PREPARE alterIfNotExists FROM @preparedStatement;
|
||
EXECUTE alterIfNotExists;
|
||
DEALLOCATE PREPARE alterIfNotExists;
|
||
|
||
SET @columnname = 'result';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(20) DEFAULT NULL COMMENT ''检测结果（normal正常/abnormal异常）'' AFTER `result_value`')
|
||
));
|
||
PREPARE alterIfNotExists FROM @preparedStatement;
|
||
EXECUTE alterIfNotExists;
|
||
DEALLOCATE PREPARE alterIfNotExists;
|
||
|
||
-- =============================================
|
||
-- 三、字典数据配置
|
||
-- =============================================
|
||
|
||
-- 3.1 巡检计划类型增加"保养"选项
|
||
INSERT IGNORE INTO `sys_dict_data` (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `remark`)
|
||
VALUES (2, '保养', 'maintenance', 'inspection_plan_type', NULL, 'warning', 'N', '0', 'admin', NOW(), '保养计划');
|
||
|
||
-- 3.2 新增保养类型字典类型
|
||
INSERT IGNORE INTO `sys_dict_type` (`dict_name`, `dict_type`, `status`, `create_by`, `create_time`, `remark`)
|
||
VALUES ('保养类型', 'maintenance_type', '0', 'admin', NOW(), '保养类型：日常保养/定期保养/大修');
|
||
|
||
-- 3.3 保养类型字典数据
|
||
INSERT IGNORE INTO `sys_dict_data` (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `remark`)
|
||
VALUES (0, '日常保养', 'daily', 'maintenance_type', NULL, 'success', 'Y', '0', 'admin', NOW(), '日常保养');
|
||
INSERT IGNORE INTO `sys_dict_data` (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `remark`)
|
||
VALUES (1, '定期保养', 'periodic', 'maintenance_type', NULL, 'primary', 'N', '0', 'admin', NOW(), '定期保养');
|
||
INSERT IGNORE INTO `sys_dict_data` (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `remark`)
|
||
VALUES (2, '大修', 'overhaul', 'maintenance_type', NULL, 'danger', 'N', '0', 'admin', NOW(), '大修');
|
||
|
||
-- 3.4 清理重复的频率字典数据（v1.0.71已插入，这里只保留一份）
|
||
-- 删除重复的quarterly和yearly（保留dict_sort较小的）
|
||
DELETE d1 FROM sys_dict_data d1
|
||
INNER JOIN sys_dict_data d2
|
||
ON d1.dict_type = d2.dict_type AND d1.dict_value = d2.dict_value AND d1.dict_code > d2.dict_code
|
||
WHERE d1.dict_type = 'inspection_plan_frequency' AND d1.dict_value IN ('quarterly', 'yearly');
|
||
|
||
-- 3.5 清理重复的保养类型字典数据
|
||
DELETE d1 FROM sys_dict_data d1
|
||
INNER JOIN sys_dict_data d2
|
||
ON d1.dict_type = d2.dict_type AND d1.dict_value = d2.dict_value AND d1.dict_code > d2.dict_code
|
||
WHERE d1.dict_type = 'maintenance_type';
|
||
|
||
-- 3.6 清理重复的执行结果字典数据
|
||
DELETE d1 FROM sys_dict_data d1
|
||
INNER JOIN sys_dict_data d2
|
||
ON d1.dict_type = d2.dict_type AND d1.dict_value = d2.dict_value AND d1.dict_code > d2.dict_code
|
||
WHERE d1.dict_type = 'inspection_result';
|
||
|
||
-- =============================================
|
||
-- 四、菜单结构调整
|
||
-- =============================================
|
||
|
||
-- 4.1 创建"点检管理"二级目录（设备管理菜单ID为2168）
|
||
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `remark`)
|
||
SELECT '点检管理', 2168, 1, 'spotcheck', NULL, NULL, 1, 0, 'M', '0', '0', '', 'checkbox', 'admin', NOW(), '点检管理目录'
|
||
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_name = '点检管理' AND parent_id = 2168);
|
||
SET @spotcheck_menu_id = (SELECT menu_id FROM sys_menu WHERE menu_name = '点检管理' AND parent_id = 2168 LIMIT 1);
|
||
|
||
-- 4.2 创建"巡检管理"二级目录
|
||
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `remark`)
|
||
SELECT '巡检管理', 2168, 2, 'inspection', NULL, NULL, 1, 0, 'M', '0', '0', '', 'eye-open', 'admin', NOW(), '巡检管理目录'
|
||
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_name = '巡检管理' AND parent_id = 2168);
|
||
SET @inspection_menu_id = (SELECT menu_id FROM sys_menu WHERE menu_name = '巡检管理' AND parent_id = 2168 LIMIT 1);
|
||
|
||
-- 4.3 创建"保养管理"二级目录
|
||
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `remark`)
|
||
SELECT '保养管理', 2168, 3, 'maintenance', NULL, NULL, 1, 0, 'M', '0', '0', '', 'tool', 'admin', NOW(), '保养管理目录'
|
||
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_name = '保养管理' AND parent_id = 2168);
|
||
SET @maintenance_menu_id = (SELECT menu_id FROM sys_menu WHERE menu_name = '保养管理' AND parent_id = 2168 LIMIT 1);
|
||
|
||
-- 4.4 创建"维修管理"二级目录
|
||
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `remark`)
|
||
SELECT '维修管理', 2168, 4, 'repair', NULL, NULL, 1, 0, 'M', '0', '0', '', 'build', 'admin', NOW(), '维修管理目录'
|
||
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_name = '维修管理' AND parent_id = 2168);
|
||
SET @repair_menu_id = (SELECT menu_id FROM sys_menu WHERE menu_name = '维修管理' AND parent_id = 2168 LIMIT 1);
|
||
|
||
-- 4.5 设备点位菜单（放在设备管理一级菜单下，作为通用基础数据）
|
||
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `remark`)
|
||
SELECT '设备点位', 2168, 10, 'inspectionPoint', 'mes/equipment/inspectionPoint/index', NULL, 1, 0, 'C', '0', '0', 'equipment:inspectionPoint:list', 'edit', 'admin', NOW(), '设备点位管理菜单'
|
||
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE path = 'inspectionPoint' AND component = 'mes/equipment/inspectionPoint/index');
|
||
|
||
-- 4.6 移动点检相关菜单到点检管理下（只有当菜单不在点检管理下时才移动）
|
||
-- 点检计划移动到点检管理下（inspectionPlan是单选设备的点检计划页面）
|
||
UPDATE `sys_menu` SET `parent_id` = @spotcheck_menu_id, `order_num` = 1, `menu_name` = '点检计划'
|
||
WHERE `path` = 'inspectionPlan' AND `component` LIKE 'mes/equipment/inspectionPlan%' AND @spotcheck_menu_id IS NOT NULL
|
||
AND `parent_id` != @spotcheck_menu_id;
|
||
|
||
-- 点检记录：新增菜单（使用inspectionPlanRecord组件，通过query参数区分）
|
||
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `remark`)
|
||
SELECT '点检记录', @spotcheck_menu_id, 2, 'spotcheckRecord', 'mes/equipment/inspectionPlanRecord/index', '{"type":"spotcheck"}', 1, 0, 'C', '0', '0', 'equipment:inspectionPlanRecord:list', '#', 'admin', NOW(), '点检记录菜单'
|
||
FROM DUAL WHERE @spotcheck_menu_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys_menu WHERE path = 'spotcheckRecord');
|
||
|
||
-- 删除旧的点检记录菜单（inspectionItemRecord）
|
||
DELETE FROM `sys_menu` WHERE `path` = 'inspectionItemRecord' AND `component` LIKE 'mes/equipment/inspectionItemRecord%';
|
||
|
||
-- 删除设备点检项目菜单（inspectionItem）
|
||
DELETE FROM `sys_menu` WHERE `path` = 'inspectionItem' AND `component` LIKE 'mes/equipment/inspectionItem%';
|
||
|
||
-- 4.7 巡检管理下的菜单
|
||
-- 巡检计划（inspectionStandard是多选设备的巡检计划页面）
|
||
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `remark`)
|
||
SELECT '巡检计划', @inspection_menu_id, 1, 'inspectionStandard', 'mes/equipment/inspectionStandard/index', NULL, 1, 0, 'C', '0', '0', 'equipment:inspectionStandard:list', '#', 'admin', NOW(), '巡检计划菜单'
|
||
FROM DUAL WHERE @inspection_menu_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys_menu WHERE path = 'inspectionStandard');
|
||
|
||
-- 巡检记录（只有当菜单不在巡检管理下时才移动，并设置query参数）
|
||
UPDATE `sys_menu` SET `parent_id` = @inspection_menu_id, `order_num` = 2, `menu_name` = '巡检记录', `query` = '{"type":"inspection"}'
|
||
WHERE `path` = 'inspectionPlanRecord' AND `component` LIKE 'mes/equipment/inspectionPlanRecord%' AND @inspection_menu_id IS NOT NULL;
|
||
|
||
-- 4.8 保养管理下的菜单
|
||
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `remark`)
|
||
SELECT '保养计划', @maintenance_menu_id, 1, 'maintenancePlan', 'mes/equipment/maintenancePlan/index', NULL, 1, 0, 'C', '0', '0', 'equipment:maintenancePlan:list', '#', 'admin', NOW(), '保养计划菜单'
|
||
FROM DUAL WHERE @maintenance_menu_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys_menu WHERE path = 'maintenancePlan');
|
||
|
||
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `remark`)
|
||
SELECT '保养记录', @maintenance_menu_id, 2, 'maintenanceRecord', 'mes/equipment/maintenanceRecord/index', NULL, 1, 0, 'C', '0', '0', 'equipment:maintenanceRecord:list', '#', 'admin', NOW(), '保养记录菜单'
|
||
FROM DUAL WHERE @maintenance_menu_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys_menu WHERE path = 'maintenanceRecord');
|
||
|
||
-- 4.9 维修管理下的菜单
|
||
-- 先检查设备维修单是否已经在维修管理目录下，避免重复移动
|
||
UPDATE `sys_menu` SET `parent_id` = @repair_menu_id, `order_num` = 1
|
||
WHERE `path` = 'faultTree' AND `component` LIKE 'mes/equipment/faultTree%' AND @repair_menu_id IS NOT NULL
|
||
AND `parent_id` != @repair_menu_id;
|
||
|
||
-- 只有当设备维修单不在维修管理目录下时才移动
|
||
UPDATE `sys_menu` SET `parent_id` = @repair_menu_id, `order_num` = 2
|
||
WHERE `path` = 'repairOrder' AND `component` LIKE 'mes/equipment/repairOrder%' AND @repair_menu_id IS NOT NULL
|
||
AND `parent_id` != @repair_menu_id;
|
||
|
||
-- =============================================
|
||
-- 五、删除冗余字段的SQL（如需清理旧数据）
|
||
-- =============================================
|
||
|
||
-- 以下字段在简化流程后不再需要，可选择性删除：
|
||
-- ALTER TABLE `dm_inspection_item` DROP COLUMN `equipment_point`; -- 旧的点位字段（已改用子表）
|
||
-- ALTER TABLE `dm_inspection_plan_item` DROP COLUMN `item_id`; -- 旧的项目关联（已改用点位关联）
|
||
-- ALTER TABLE `dm_inspection_plan_item` DROP COLUMN `item_number`;
|
||
-- ALTER TABLE `dm_inspection_plan_item` DROP COLUMN `item_name`;
|
||
-- ALTER TABLE `dm_inspection_plan_item` DROP COLUMN `item_content`;
|
||
-- ALTER TABLE `dm_inspection_plan_item` DROP COLUMN `item_standard`;
|
||
|
||
-- =============================================
|
||
-- 六、回退SQL（如需回退请执行以下语句）
|
||
-- =============================================
|
||
|
||
/*
|
||
-- 删除新增的菜单
|
||
DELETE FROM `sys_menu` WHERE `path` IN ('inspectionPoint', 'inspectionStandard', 'maintenanceItem', 'maintenancePlan', 'maintenanceRecord');
|
||
DELETE FROM `sys_menu` WHERE `path` IN ('spotcheck', 'inspection', 'maintenance', 'repair') AND `parent_id` = 2168 AND `menu_type` = 'M';
|
||
|
||
-- 删除字典数据
|
||
DELETE FROM `sys_dict_data` WHERE `dict_type` = 'maintenance_type';
|
||
DELETE FROM `sys_dict_data` WHERE `dict_type` = 'inspection_plan_type' AND `dict_value` = 'maintenance';
|
||
DELETE FROM `sys_dict_type` WHERE `dict_type` = 'maintenance_type';
|
||
|
||
-- 删除扩展字段
|
||
ALTER TABLE `dm_inspection_plan` DROP COLUMN `maintenance_type`;
|
||
ALTER TABLE `dm_inspection_item` DROP COLUMN `spare_parts`;
|
||
ALTER TABLE `dm_inspection_item` DROP COLUMN `equipment_ids`;
|
||
|
||
-- 删除维修单扩展字段
|
||
ALTER TABLE `dm_repair_order_entry` DROP COLUMN `point_id`;
|
||
ALTER TABLE `dm_repair_order_entry` DROP COLUMN `point_name`;
|
||
ALTER TABLE `dm_repair_order_entry` DROP COLUMN `point_code`;
|
||
ALTER TABLE `dm_repair_order_entry` DROP COLUMN `standard_value`;
|
||
ALTER TABLE `dm_repair_order_entry` DROP COLUMN `upper_limit`;
|
||
ALTER TABLE `dm_repair_order_entry` DROP COLUMN `lower_limit`;
|
||
ALTER TABLE `dm_repair_order_entry` DROP COLUMN `unit`;
|
||
ALTER TABLE `dm_repair_order_entry` DROP COLUMN `result_value`;
|
||
ALTER TABLE `dm_repair_order_entry` DROP COLUMN `result`;
|
||
|
||
-- 删除新增表
|
||
DROP TABLE IF EXISTS `dm_inspection_record_item`;
|
||
DROP TABLE IF EXISTS `dm_inspection_plan_item`;
|
||
DROP TABLE IF EXISTS `dm_inspection_item_point`;
|
||
*/
|
||
|
||
|
||
-- =============================================
|
||
-- 七、扩展执行记录表，增加点位字段
|
||
-- =============================================
|
||
|
||
-- 7.1 给dm_inspection_plan_record表添加point_id字段
|
||
SET @dbname = DATABASE();
|
||
SET @tablename = 'dm_inspection_plan_record';
|
||
|
||
SET @columnname = 'point_id';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' BIGINT DEFAULT NULL COMMENT ''关联点位ID'' AFTER `equipment_name`')
|
||
));
|
||
PREPARE alterIfNotExists FROM @preparedStatement;
|
||
EXECUTE alterIfNotExists;
|
||
DEALLOCATE PREPARE alterIfNotExists;
|
||
|
||
-- 7.2 给dm_inspection_plan_record表添加point_name字段
|
||
SET @columnname = 'point_name';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(100) DEFAULT NULL COMMENT ''点位名称'' AFTER `point_id`')
|
||
));
|
||
PREPARE alterIfNotExists FROM @preparedStatement;
|
||
EXECUTE alterIfNotExists;
|
||
DEALLOCATE PREPARE alterIfNotExists;
|
||
|
||
|
||
-- =============================================
|
||
-- 八、修复dm_inspection_record_item表item_id字段
|
||
-- =============================================
|
||
|
||
-- 8.1 添加item_id字段（如果不存在则添加，如果存在则修改为允许空）
|
||
SET @dbname = DATABASE();
|
||
SET @tablename = 'dm_inspection_record_item';
|
||
SET @columnname = 'item_id';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
CONCAT('ALTER TABLE ', @tablename, ' MODIFY COLUMN ', @columnname, ' BIGINT DEFAULT NULL COMMENT ''关联标准项目ID（可选）'''),
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' BIGINT DEFAULT NULL COMMENT ''关联标准项目ID（可选）'' AFTER `record_id`')
|
||
));
|
||
PREPARE alterStatement FROM @preparedStatement;
|
||
EXECUTE alterStatement;
|
||
DEALLOCATE PREPARE alterStatement;
|
||
|
||
-- =============================================
|
||
-- 九、维修单增加富文本字段
|
||
-- =============================================
|
||
|
||
-- 9.1 给dm_repair_order表添加故障详情富文本字段
|
||
SET @tablename = 'dm_repair_order';
|
||
SET @columnname = 'fault_detail';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' TEXT DEFAULT NULL COMMENT ''故障详情（富文本）''')
|
||
));
|
||
PREPARE alterIfNotExists FROM @preparedStatement;
|
||
EXECUTE alterIfNotExists;
|
||
DEALLOCATE PREPARE alterIfNotExists;
|
||
|
||
-- 9.2 给dm_repair_order表添加更换备品备件富文本字段
|
||
SET @columnname = 'spare_parts_detail';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' TEXT DEFAULT NULL COMMENT ''更换备品备件（富文本）''')
|
||
));
|
||
PREPARE alterIfNotExists FROM @preparedStatement;
|
||
EXECUTE alterIfNotExists;
|
||
DEALLOCATE PREPARE alterIfNotExists;
|
||
|
||
-- =============================================
|
||
-- 十、维修单明细权限配置
|
||
-- =============================================
|
||
|
||
-- 10.1 添加维修单明细查看权限按钮
|
||
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `remark`)
|
||
SELECT '维修单明细', (SELECT menu_id FROM sys_menu WHERE path = 'repairOrder' AND component LIKE 'mes/equipment/repairOrder%' LIMIT 1), 5, '', NULL, NULL, 1, 0, 'F', '0', '0', 'equipment:repairOrder:detail', '#', 'admin', NOW(), '维修单明细查看权限'
|
||
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms = 'equipment:repairOrder:detail');
|
||
|
||
|
||
-- =============================================
|
||
-- 十一、修复dm_inspection_record_item表缺失字段
|
||
-- =============================================
|
||
|
||
-- 11.1 添加item_content字段（检查方法）
|
||
SET @dbname = DATABASE();
|
||
SET @tablename = 'dm_inspection_record_item';
|
||
SET @columnname = 'item_content';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(500) DEFAULT NULL COMMENT ''检查方法/项目内容'' AFTER `unit`')
|
||
));
|
||
PREPARE alterIfNotExists FROM @preparedStatement;
|
||
EXECUTE alterIfNotExists;
|
||
DEALLOCATE PREPARE alterIfNotExists;
|
||
|
||
-- 11.2 添加item_number字段
|
||
SET @columnname = 'item_number';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(50) DEFAULT NULL COMMENT ''项目编号'' AFTER `point_name`')
|
||
));
|
||
PREPARE alterIfNotExists FROM @preparedStatement;
|
||
EXECUTE alterIfNotExists;
|
||
DEALLOCATE PREPARE alterIfNotExists;
|
||
|
||
-- 11.3 添加item_name字段
|
||
SET @columnname = 'item_name';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(100) DEFAULT NULL COMMENT ''项目名称'' AFTER `item_number`')
|
||
));
|
||
PREPARE alterIfNotExists FROM @preparedStatement;
|
||
EXECUTE alterIfNotExists;
|
||
DEALLOCATE PREPARE alterIfNotExists;
|
||
|
||
-- 11.4 添加item_standard字段
|
||
SET @columnname = 'item_standard';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(500) DEFAULT NULL COMMENT ''项目标准'' AFTER `item_content`')
|
||
));
|
||
PREPARE alterIfNotExists FROM @preparedStatement;
|
||
EXECUTE alterIfNotExists;
|
||
DEALLOCATE PREPARE alterIfNotExists;
|
||
|
||
-- 11.5 添加spare_parts_used字段（保养用）
|
||
SET @columnname = 'spare_parts_used';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(500) DEFAULT NULL COMMENT ''使用的备件'' AFTER `problem_desc`')
|
||
));
|
||
PREPARE alterIfNotExists FROM @preparedStatement;
|
||
EXECUTE alterIfNotExists;
|
||
DEALLOCATE PREPARE alterIfNotExists;
|
||
|
||
-- 11.6 添加point_code字段
|
||
SET @columnname = 'point_code';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(50) DEFAULT NULL COMMENT ''点位编码'' AFTER `point_name`')
|
||
));
|
||
PREPARE alterIfNotExists FROM @preparedStatement;
|
||
EXECUTE alterIfNotExists;
|
||
DEALLOCATE PREPARE alterIfNotExists;
|
||
|
||
|
||
-- =============================================
|
||
-- 十二、巡检记录菜单路由参数配置（点检记录已在4.6节处理）
|
||
-- =============================================
|
||
|
||
-- 12.1 获取巡检管理目录ID
|
||
SET @inspection_menu_id = (SELECT menu_id FROM sys_menu WHERE menu_name = '巡检管理' AND parent_id = 2168 LIMIT 1);
|
||
|
||
-- 12.2 更新巡检记录菜单（带type=inspection参数）- 备用，4.7节已处理
|
||
-- UPDATE `sys_menu` SET `query` = '{"type":"inspection"}'
|
||
-- WHERE `path` = 'inspectionPlanRecord' AND `component` LIKE 'mes/equipment/inspectionPlanRecord%';
|
||
|
||
|
||
-- =============================================
|
||
-- 十三、维修单增加故障描述字段
|
||
-- =============================================
|
||
|
||
-- 13.1 给dm_repair_order表添加故障描述字段
|
||
SET @dbname = DATABASE();
|
||
SET @tablename = 'dm_repair_order';
|
||
SET @columnname = 'fault_desc';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(500) DEFAULT NULL COMMENT ''故障描述'' AFTER `spare_parts_detail`')
|
||
));
|
||
PREPARE alterIfNotExists FROM @preparedStatement;
|
||
EXECUTE alterIfNotExists;
|
||
DEALLOCATE PREPARE alterIfNotExists;
|
||
|