-- =============================================
|
-- 生产订单、排产、报工优化 - 数据库变更脚本
|
-- 执行时间：2024-01-15
|
-- 说明：新增任务状态扩展字段、工单工艺路线关联字段
|
-- =============================================
|
|
-- =============================================
|
-- 1. 生产任务表新增字段
|
-- =============================================
|
ALTER TABLE mes_pro_task
|
ADD COLUMN IF NOT EXISTS first_feedback_time datetime COMMENT '首次报工时间' AFTER cancel_date,
|
ADD COLUMN IF NOT EXISTS last_feedback_time datetime COMMENT '最近报工时间' AFTER first_feedback_time;
|
|
-- =============================================
|
-- 2. 生产工单表新增字段
|
-- =============================================
|
ALTER TABLE mes_pro_work_order
|
ADD COLUMN IF NOT EXISTS route_id bigint COMMENT '工艺路线编号' AFTER batch_code,
|
ADD COLUMN IF NOT EXISTS auto_finish_flag tinyint(1) DEFAULT 1 COMMENT '是否自动完成（报工达标后自动完成工单）' AFTER route_id;
|
|
-- =============================================
|
-- 3. 补充索引
|
-- =============================================
|
ALTER TABLE mes_pro_task ADD INDEX IF NOT EXISTS idx_work_order_process (work_order_id, process_id);
|
|
-- =============================================
|
-- 4. 字典数据变更 - 任务状态枚举扩展
|
-- =============================================
|
-- 新增「生产中」状态
|
INSERT INTO system_dict_data (sort, label, value, dict_type, status, color_type, css_class, remark, creator, create_time, updater, update_time, deleted)
|
SELECT 1, '生产中', '1', 'mes_pro_task_status', 0, 'processing', '', '任务正在生产中', 'admin', NOW(), 'admin', NOW(), 0
|
FROM DUAL
|
WHERE NOT EXISTS (SELECT 1 FROM system_dict_data WHERE dict_type = 'mes_pro_task_status' AND value = '1');
|
|
-- 新增「部分完成」状态
|
INSERT INTO system_dict_data (sort, label, value, dict_type, status, color_type, css_class, remark, creator, create_time, updater, update_time, deleted)
|
SELECT 2, '部分完成', '2', 'mes_pro_task_status', 0, 'warning', '', '任务部分完成', 'admin', NOW(), 'admin', NOW(), 0
|
FROM DUAL
|
WHERE NOT EXISTS (SELECT 1 FROM system_dict_data WHERE dict_type = 'mes_pro_task_status' AND value = '2');
|
|
-- 更新原有枚举的 sort
|
UPDATE system_dict_data SET sort = 0 WHERE dict_type = 'mes_pro_task_status' AND value = '0' AND deleted = 0;
|
UPDATE system_dict_data SET sort = 3 WHERE dict_type = 'mes_pro_task_status' AND value = '3' AND deleted = 0;
|
UPDATE system_dict_data SET sort = 4 WHERE dict_type = 'mes_pro_task_status' AND value = '4' AND deleted = 0;
|
