187 lines
8.1 KiB
SQL
187 lines
8.1 KiB
SQL
-- ============================================
|
||
-- 兴万达MES系统改进 - 数据库变更脚本
|
||
-- 创建时间：2025-10-16
|
||
-- 最后更新：2025-10-17
|
||
-- ============================================
|
||
--
|
||
-- 本脚本包含以下改进内容：
|
||
-- 1. 设备维修单审批流程优化
|
||
-- 2. 设备故障树功能
|
||
-- 3. 销售订单批次号功能
|
||
--
|
||
-- 执行前请备份数据库！
|
||
-- ============================================
|
||
|
||
-- ============================================
|
||
-- 模块1：设备维修单审批流程优化
|
||
-- ============================================
|
||
|
||
-- 1.1 在设备维修单主表添加审批流程相关字段
|
||
ALTER TABLE `dm_repair_order`
|
||
ADD COLUMN `approver_user_id` BIGINT DEFAULT NULL COMMENT '审核人ID' AFTER `confirm_time`,
|
||
ADD COLUMN `approver_user_name` VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '审核人名称' AFTER `approver_user_id`,
|
||
ADD COLUMN `approve_time` DATETIME DEFAULT NULL COMMENT '审核时间' AFTER `approver_user_name`,
|
||
ADD COLUMN `approve_status` VARCHAR(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '审核状态(1=通过,0=不通过,NULL=待审核)' AFTER `approve_time`,
|
||
ADD COLUMN `confirm_status` VARCHAR(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '验收状态(1=通过,0=不通过,NULL=待验收)' AFTER `approve_status`;
|
||
|
||
-- 1.2 更新设备维修单状态字典标签
|
||
-- 调整流程：开始报修 → 待维修（审核通过）→ 待验收（维修完成）→ 已完成（验收通过）
|
||
UPDATE sys_dict_data
|
||
SET dict_label = '开始报修',
|
||
dict_sort = 1,
|
||
remark = '新建维修单的初始状态，等待审核'
|
||
WHERE dict_type = 'repair_order_status' AND dict_value = 'A';
|
||
|
||
UPDATE sys_dict_data
|
||
SET dict_label = '待维修',
|
||
dict_sort = 2,
|
||
remark = '审核通过后的状态，等待维修人维修'
|
||
WHERE dict_type = 'repair_order_status' AND dict_value = 'B';
|
||
|
||
UPDATE sys_dict_data
|
||
SET dict_label = '待验收',
|
||
dict_sort = 3,
|
||
remark = '维修完成后的状态，等待验收人验收'
|
||
WHERE dict_type = 'repair_order_status' AND dict_value = 'C';
|
||
|
||
UPDATE sys_dict_data
|
||
SET dict_label = '已完成',
|
||
dict_sort = 4,
|
||
remark = '验收完成或审核驳回'
|
||
WHERE dict_type = 'repair_order_status' AND dict_value = 'D';
|
||
|
||
-- ============================================
|
||
-- 模块2：设备故障树功能
|
||
-- ============================================
|
||
|
||
-- 2.1 创建设备故障树表（三级树形结构）
|
||
CREATE TABLE IF NOT EXISTS `dm_equipment_fault_tree` (
|
||
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '故障ID',
|
||
`fault_code` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '故障编码',
|
||
`fault_name` VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '故障名称',
|
||
`parent_id` BIGINT NOT NULL DEFAULT 0 COMMENT '父故障ID（0表示根节点）',
|
||
`tree_level` INT NOT NULL DEFAULT 1 COMMENT '层级（1/2/3）',
|
||
`order_num` INT NOT NULL DEFAULT 0 COMMENT '显示顺序',
|
||
`status` CHAR(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0' COMMENT '状态（0正常 1停用）',
|
||
`remark` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '备注',
|
||
`create_by` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '创建人',
|
||
`create_time` DATETIME DEFAULT NULL COMMENT '创建时间',
|
||
`update_by` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '更新人',
|
||
`update_time` DATETIME DEFAULT NULL COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
INDEX `idx_parent_id` (`parent_id`) USING BTREE,
|
||
INDEX `idx_tree_level` (`tree_level`) USING BTREE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='设备故障树';
|
||
|
||
-- 2.2 在维修单明细表添加故障字段
|
||
ALTER TABLE `dm_repair_order_entry`
|
||
ADD COLUMN `fault_id` BIGINT DEFAULT NULL COMMENT '故障ID（关联dm_equipment_fault_tree.id）' AFTER `item_standard`,
|
||
ADD COLUMN `fault_name` VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '故障名称（冗余字段）' AFTER `fault_id`;
|
||
|
||
-- ============================================
|
||
-- 模块3：销售订单批次号功能
|
||
-- ============================================
|
||
|
||
-- 3.1 在销售订单主表添加批次号字段
|
||
-- 说明：批次号是订单级别的属性，只存储在主表中
|
||
ALTER TABLE `sal_order`
|
||
ADD COLUMN `batch_number` VARCHAR(50) DEFAULT NULL COMMENT '批次号（格式：PC+yyyyMMdd+3位序号）';
|
||
|
||
-- 3.2 为批次号字段创建索引（提高查询性能）
|
||
CREATE INDEX `idx_sal_order_batch_number` ON `sal_order`(`batch_number`);
|
||
|
||
-- 3.3 清理和修复字段扩展配置
|
||
-- 说明：使用动态字段扩展系统来配置前端列表显示
|
||
|
||
-- 3.3.1 停用旧的banxing字段（如果存在）
|
||
UPDATE `sys_field_extend`
|
||
SET `status` = 1
|
||
WHERE `source_bill` = 'saleOrderEntry' AND `field` = 'banxing';
|
||
|
||
-- 3.3.2 修复销售订单明细列表的批次号显示
|
||
-- 重要：field 设置为 'salOrder.batchNumber' 以从主表关联显示
|
||
-- 注意：is_system='Y' 表示从对象属性读取，而非 extendFieldJson
|
||
UPDATE `sys_field_extend`
|
||
SET `status` = 0,
|
||
`field` = 'salOrder.batchNumber',
|
||
`is_system` = 'Y'
|
||
WHERE `source_bill` = 'saleOrderEntry'
|
||
AND (`field` = 'batchNumber' OR `field` = 'salOrder.batchNumber');
|
||
|
||
-- 备用方案：如果上面的条件匹配不到，直接通过 id 更新
|
||
-- UPDATE sys_field_extend SET status = 0, field = 'salOrder.batchNumber', is_system = 'Y' WHERE id = 99;
|
||
|
||
-- 3.3.3 删除生产工单重复的批次号字段扩展配置
|
||
-- 问题：workOrder 可能有多条 batchNumber 记录，导致列表重复显示
|
||
DELETE FROM `sys_field_extend`
|
||
WHERE `source_bill` = 'workOrder'
|
||
AND `field` = 'batchNumber'
|
||
AND `id` != (
|
||
SELECT min_id FROM (
|
||
SELECT MIN(id) as min_id
|
||
FROM sys_field_extend
|
||
WHERE source_bill = 'workOrder' AND field = 'batchNumber'
|
||
) AS temp
|
||
);
|
||
|
||
-- 3.3.4 更新生产工单的批次号字段扩展配置
|
||
-- 确保 is_system='Y'，这样前端才能从 WorkOrder 对象的 batchNumber 属性读取数据
|
||
UPDATE `sys_field_extend`
|
||
SET `is_system` = 'Y',
|
||
`status` = 0
|
||
WHERE `source_bill` = 'workOrder'
|
||
AND `field` = 'batchNumber';
|
||
|
||
-- ============================================
|
||
-- 可选：数据清理
|
||
-- ============================================
|
||
|
||
-- 如果之前错误地在明细表添加了批次号字段，可以选择删除
|
||
-- 警告：执行前请确认明细表确实有此字段且不需要保留数据
|
||
-- ALTER TABLE sal_order_entry DROP COLUMN IF EXISTS batch_number;
|
||
|
||
-- ============================================
|
||
-- 验证查询（执行后运行这些查询验证结果）
|
||
-- ============================================
|
||
|
||
-- 验证1：检查销售订单主表批次号字段
|
||
-- SELECT COLUMN_NAME, COLUMN_TYPE, COLUMN_COMMENT
|
||
-- FROM INFORMATION_SCHEMA.COLUMNS
|
||
-- WHERE TABLE_SCHEMA = DATABASE()
|
||
-- AND TABLE_NAME = 'sal_order'
|
||
-- AND COLUMN_NAME = 'batch_number';
|
||
|
||
-- 验证2：检查字段扩展配置
|
||
-- SELECT id, source_bill, field, field_name, is_system, status
|
||
-- FROM sys_field_extend
|
||
-- WHERE source_bill IN ('saleOrderEntry', 'workOrder')
|
||
-- AND (field LIKE '%batchNumber%' OR field LIKE '%batch%')
|
||
-- ORDER BY source_bill, id;
|
||
|
||
-- 验证3：检查设备维修单新增字段
|
||
-- SELECT COLUMN_NAME, COLUMN_TYPE, COLUMN_COMMENT
|
||
-- FROM INFORMATION_SCHEMA.COLUMNS
|
||
-- WHERE TABLE_SCHEMA = DATABASE()
|
||
-- AND TABLE_NAME = 'dm_repair_order'
|
||
-- AND COLUMN_NAME IN ('approver_user_id', 'approve_time', 'approve_status', 'confirm_status');
|
||
|
||
-- 验证4：检查故障树表是否创建成功
|
||
-- SELECT COUNT(*) as table_exists
|
||
-- FROM INFORMATION_SCHEMA.TABLES
|
||
-- WHERE TABLE_SCHEMA = DATABASE()
|
||
-- AND TABLE_NAME = 'dm_equipment_fault_tree';
|
||
|
||
-- ============================================
|
||
-- 脚本执行完成
|
||
-- ============================================
|
||
--
|
||
-- 执行后请验证：
|
||
-- 1. 重启后端服务
|
||
-- 2. 清除浏览器缓存
|
||
-- 3. 测试销售订单批次号生成和显示
|
||
-- 4. 测试生产工单批次号关联和显示
|
||
-- 5. 测试设备维修单审批流程
|
||
-- 6. 测试故障树选择功能
|
||
--
|
||
-- ============================================
|