-- =============================================
|
-- 计划排程（MPS）审核流 + 辅料采购 升级脚本
|
-- 1) mes_pro_mps 增加：计划开始日期、审核信息、采购申请单关联、审批流程实例编号
|
-- 2) 字典 mes_pro_mps_status 增加：4-审核中、5-已审核
|
-- 3) 菜单/按钮：提交审核、生成采购申请单（页面内「审核」按钮已废弃，审批统一在协同办公完成）
|
-- 4) 审批流程：需部署流程定义 mes-pro-mps-audit（见 docs/sql/mes_pro_mps_audit_20261102.bpmn）
|
-- 可重复执行，不包含 tenant_id（项目不使用多租户）
|
-- =============================================
|
START TRANSACTION;
|
|
-- =============================================
|
-- 1. 表结构
|
-- =============================================
|
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_pro_mps' AND column_name = 'plan_start_date');
|
SET @sql = IF(@exists = 0,
|
'ALTER TABLE mes_pro_mps ADD COLUMN plan_start_date datetime DEFAULT NULL COMMENT ''计划开始日期'' AFTER request_date',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_pro_mps' AND column_name = 'audit_user_id');
|
SET @sql = IF(@exists = 0,
|
'ALTER TABLE mes_pro_mps ADD COLUMN audit_user_id bigint DEFAULT NULL COMMENT ''审核人编号'' AFTER remark',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_pro_mps' AND column_name = 'audit_time');
|
SET @sql = IF(@exists = 0,
|
'ALTER TABLE mes_pro_mps ADD COLUMN audit_time datetime DEFAULT NULL COMMENT ''审核时间'' AFTER audit_user_id',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_pro_mps' AND column_name = 'audit_remark');
|
SET @sql = IF(@exists = 0,
|
'ALTER TABLE mes_pro_mps ADD COLUMN audit_remark varchar(500) DEFAULT NULL COMMENT ''审核意见（驳回原因）'' AFTER audit_time',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_pro_mps' AND column_name = 'purchase_request_id');
|
SET @sql = IF(@exists = 0,
|
'ALTER TABLE mes_pro_mps ADD COLUMN purchase_request_id bigint DEFAULT NULL COMMENT ''生成的采购申请单编号'' AFTER audit_remark',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- 审批流程实例编号：提交审核后进入协同办公审批，审批结果回写本表
|
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_pro_mps' AND column_name = 'process_instance_id');
|
SET @sql = IF(@exists = 0,
|
'ALTER TABLE mes_pro_mps ADD COLUMN process_instance_id varchar(64) DEFAULT NULL COMMENT ''BPM 流程实例编号'' AFTER purchase_request_id',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- =============================================
|
-- 2. 字典数据：MPS 状态增加 审核中(4)、已审核(5)
|
-- =============================================
|
INSERT INTO system_dict_data(id, sort, label, value, dict_type, status, color_type, css_class, remark,
|
creator, create_time, updater, update_time, deleted)
|
SELECT (SELECT IFNULL(MAX(id), 0) + 1 FROM system_dict_data), 4, '审核中', '4', 'mes_pro_mps_status', 0, 'warning', '', '',
|
'1', NOW(), '1', NOW(), b'0'
|
FROM DUAL
|
WHERE NOT EXISTS (SELECT 1 FROM system_dict_data WHERE dict_type = 'mes_pro_mps_status' AND value = '4' AND deleted = b'0');
|
|
INSERT INTO system_dict_data(id, sort, label, value, dict_type, status, color_type, css_class, remark,
|
creator, create_time, updater, update_time, deleted)
|
SELECT (SELECT IFNULL(MAX(id), 0) + 1 FROM system_dict_data), 5, '已审核', '5', 'mes_pro_mps_status', 0, 'success', '', '',
|
'1', NOW(), '1', NOW(), b'0'
|
FROM DUAL
|
WHERE NOT EXISTS (SELECT 1 FROM system_dict_data WHERE dict_type = 'mes_pro_mps_status' AND value = '5' AND deleted = b'0');
|
|
-- =============================================
|
-- 3. 按钮权限：提交审核 / 生成采购申请单
|
-- =============================================
|
SET @mps_page = (SELECT id FROM system_menu WHERE deleted = b'0' AND permission = 'mes:pro-mps:query' ORDER BY id LIMIT 1);
|
|
-- 3.0 清理旧版页面内「审核」按钮（审批已改为在协同办公完成）
|
DELETE FROM system_role_menu
|
WHERE menu_id IN (SELECT id FROM (SELECT id FROM system_menu WHERE permission = 'mes:pro-mps:audit' AND deleted = b'0') t);
|
DELETE FROM system_menu WHERE permission = 'mes:pro-mps:audit' AND deleted = b'0';
|
|
-- 3.1 提交审核
|
SET @btn = (SELECT id FROM system_menu WHERE deleted = b'0' AND permission = 'mes:pro-mps:submit' ORDER BY id LIMIT 1);
|
SET @next = (SELECT IFNULL(MAX(id), 0) + 1 FROM system_menu);
|
INSERT INTO system_menu(id, name, permission, type, sort, parent_id, path, icon, component, component_name, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
|
SELECT @next, '生产计划-提交审核', 'mes:pro-mps:submit', 3, 20, @mps_page, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'
|
FROM DUAL WHERE @btn IS NULL AND @mps_page IS NOT NULL;
|
|
-- 3.2 生成采购申请单
|
SET @btn = (SELECT id FROM system_menu WHERE deleted = b'0' AND permission = 'mes:pro-mps:generate-purchase' ORDER BY id LIMIT 1);
|
SET @next = (SELECT IFNULL(MAX(id), 0) + 1 FROM system_menu);
|
INSERT INTO system_menu(id, name, permission, type, sort, parent_id, path, icon, component, component_name, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
|
SELECT @next, '生产计划-生成采购申请单', 'mes:pro-mps:generate-purchase', 3, 22, @mps_page, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'
|
FROM DUAL WHERE @btn IS NULL AND @mps_page IS NOT NULL;
|
|
-- =============================================
|
-- 4. 协同办公流程分类：生产计划审批（流程定义 mes-pro-mps-audit 部署后归属该分类）
|
-- =============================================
|
INSERT INTO bpm_category(name, code, description, status, sort, creator, create_time, updater, update_time, deleted)
|
SELECT '生产计划审批', 'mes_pro_mps_approve', 'MES 生产计划审批', 0, 72, '1', NOW(), '1', NOW(), b'0'
|
FROM DUAL
|
WHERE NOT EXISTS (SELECT 1 FROM bpm_category WHERE code = 'mes_pro_mps_approve' AND deleted = b'0');
|
|
-- =============================================
|
-- 5. 协同办公审批详情：绑定生产计划业务详情组件，审批人可查看计划数据
|
-- =============================================
|
INSERT INTO bpm_business_attach(process_definition_key, business_type, component_path, status, remark,
|
creator, create_time, updater, update_time, deleted)
|
SELECT 'mes-pro-mps-audit', 'mes_pro_mps', '/mes/pro/mps/detail/approve/index.vue', 0,
|
'生产计划审批展示生产计划详情', '1', NOW(), '1', NOW(), b'0'
|
FROM DUAL
|
WHERE NOT EXISTS (SELECT 1 FROM bpm_business_attach
|
WHERE process_definition_key = 'mes-pro-mps-audit' AND deleted = b'0');
|
|
-- =============================================
|
-- 6. 授权：超级管理员直接拥有全部菜单，此处仅补普通角色按需授权（示例：生产计划已有查询权限的角色）
|
-- =============================================
|
INSERT INTO system_role_menu(role_id, menu_id, creator, create_time, updater, update_time, deleted)
|
SELECT DISTINCT rm.role_id, m.id, '1', NOW(), '1', NOW(), b'0'
|
FROM system_role_menu rm
|
JOIN system_menu m ON m.permission IN ('mes:pro-mps:submit', 'mes:pro-mps:generate-purchase')
|
AND m.deleted = b'0'
|
JOIN system_menu parent ON parent.id = rm.menu_id AND parent.permission = 'mes:pro-mps:query' AND parent.deleted = b'0'
|
WHERE rm.deleted = b'0'
|
AND NOT EXISTS (SELECT 1 FROM system_role_menu rm2
|
WHERE rm2.role_id = rm.role_id AND rm2.menu_id = m.id AND rm2.deleted = b'0');
|
|
COMMIT;
|
