-- =============================================================================
|
-- 文件：bpm_audit_flow_01_repair_and_category.sql
|
-- 用途：BPM 审核流程「修复 + 分类补齐」段（第 1 个执行）
|
-- 目标库：ruoyi-vue-pro（后端 48080 实际读取的运行库）
|
-- 要求：MySQL 8.0+；建议先备份：mysqldump ruoyi-vue-pro act_re_procdef act_re_model
|
-- act_re_deployment act_ge_bytearray bpm_category bpm_process_definition_info > bpm_bak.sql
|
-- 特性：本文件内所有语句均幂等（可重复执行）；判断"已存在/数据不完整"后只做必要修复，
|
-- 不删除任何既有数据，只做状态修正。
|
-- 说明：
|
-- 1) 修复段只做安全修复，不自动删除"测试/垃圾流程"（涉及历史数据判断，需人工）。
|
-- 对明显垃圾流程统一"挂起"，避免出现在前端发起列表，且不影响运行中的流程实例。
|
-- 2) 分类补齐：只为代码中已引用、但库里缺失的分类 code 补插（已存在则跳过）。
|
-- =============================================================================
|
|
SET NAMES utf8mb4;
|
|
-- -----------------------------------------------------------------------------
|
-- ① 数据完整性修复
|
-- -----------------------------------------------------------------------------
|
|
-- 1.1 ACT_RE_PROCDEF.ENGINE_VERSION_ 空串会导致 Flowable 提交审批报错
|
-- （Invalid 'engine' for process definition ...），需修正为 NULL。
|
UPDATE ACT_RE_PROCDEF
|
SET ENGINE_VERSION_ = NULL
|
WHERE ENGINE_VERSION_ = '';
|
|
-- 1.2 ACT_RE_MODEL.META_INFO_ 为空会导致流程模型列表/页面反序列化异常。
|
-- 补齐一个最小的合法 JSON（type=10 BPMN 模式，含 sort），保证页面可打开。
|
-- 只补空值行，不影响已有完整配置。
|
UPDATE ACT_RE_MODEL
|
SET META_INFO_ = CONCAT('{"type":10,"sort":', UNIX_TIMESTAMP(NOW(3)) * 1000, ',"formType":10,"visible":true}')
|
WHERE META_INFO_ IS NULL OR META_INFO_ = '';
|
|
-- 1.3 bpm_process_definition_info.model_type 非 10 的 BPMN 流程，创建流程实例会 NPE。
|
-- 仅当该流程确为 BPMN（在 ACT_RE_PROCDEF 存在）且非 SIMPLE（simple_model 为空）时修正为 10。
|
UPDATE bpm_process_definition_info i
|
JOIN ACT_RE_PROCDEF p ON p.ID_ = i.process_definition_id
|
SET i.model_type = 10
|
WHERE i.simple_model IS NULL
|
AND i.model_type <> 10;
|
|
-- 1.4 业务规范流程的多版本治理：同 key 保留最新版本激活（SUSPENSION_STATE_=1），
|
-- 历史版本一律挂起（=2）。只作用于"在用"白名单 key，其余不动。
|
UPDATE ACT_RE_PROCDEF p
|
JOIN (SELECT KEY_, MAX(VERSION_) AS max_ver
|
FROM ACT_RE_PROCDEF
|
WHERE KEY_ IN ('erp-finance-payment-audit','crm-invoice-audit',
|
'crm-receivable-audit','mes-pd-project-audit',
|
'purchase_request_approve')
|
GROUP BY KEY_) t ON t.KEY_ = p.KEY_
|
SET p.SUSPENSION_STATE_ = IF(p.VERSION_ = t.max_ver, 1, 2);
|
|
-- 1.5 明显为"测试/已废弃"的流程统一挂起（=2），避免出现在发起/待办选择列表。
|
-- 仅改状态不删除，可随时人工在界面或 SQL 清理；对运行中实例无影响。
|
UPDATE ACT_RE_PROCDEF
|
SET SUSPENSION_STATE_ = 2
|
WHERE KEY_ IN ('Ada','QWEDD','SSQ','_..123abc','b000_abc.','yz001','yz002',
|
'caigoushenpi','pus','purchase_approve','contract_approve');
|
|
-- 1.6 （可选）查看仍处于激活状态的流程，人工确认是否残留垃圾流程：
|
-- SELECT KEY_, NAME_, VERSION_, SUSPENSION_STATE_ FROM ACT_RE_PROCDEF
|
-- WHERE SUSPENSION_STATE_ = 1 ORDER BY KEY_;
|
|
-- -----------------------------------------------------------------------------
|
-- ② 流程分类（bpm_category）补齐
|
-- 对应各业务单据发起审批时按 code 拉取可发起流程列表；代码中已引用但库中缺失则补。
|
-- 每条均做"已存在则跳过"判断（code + 未删除）。
|
-- -----------------------------------------------------------------------------
|
|
-- 2.1 ERP 采购订单审批
|
INSERT INTO bpm_category (name, code, description, status, sort, creator, create_time, updater, update_time, deleted)
|
SELECT '采购订单审批', 'purchase_order_approve', 'ERP 采购订单审批', 0, 10, '1', NOW(), '1', NOW(), 0
|
WHERE NOT EXISTS (SELECT 1 FROM bpm_category WHERE code = 'purchase_order_approve' AND deleted = 0);
|
|
-- 2.2 ERP 销售订单审批
|
INSERT INTO bpm_category (name, code, description, status, sort, creator, create_time, updater, update_time, deleted)
|
SELECT '销售订单审批', 'sale_order_approve', 'ERP 销售订单审批', 0, 20, '1', NOW(), '1', NOW(), 0
|
WHERE NOT EXISTS (SELECT 1 FROM bpm_category WHERE code = 'sale_order_approve' AND deleted = 0);
|
|
-- 2.3 HRM 请假审批
|
INSERT INTO bpm_category (name, code, description, status, sort, creator, create_time, updater, update_time, deleted)
|
SELECT '请假审批', 'hrm_leave_approve', 'HRM 请假申请审批', 0, 30, '1', NOW(), '1', NOW(), 0
|
WHERE NOT EXISTS (SELECT 1 FROM bpm_category WHERE code = 'hrm_leave_approve' AND deleted = 0);
|
|
-- 2.4 HRM 加班审批
|
INSERT INTO bpm_category (name, code, description, status, sort, creator, create_time, updater, update_time, deleted)
|
SELECT '加班审批', 'overtime_approve', 'HRM 加班申请审批', 0, 40, '1', NOW(), '1', NOW(), 0
|
WHERE NOT EXISTS (SELECT 1 FROM bpm_category WHERE code = 'overtime_approve' AND deleted = 0);
|
|
-- 2.5 HRM 离职审批
|
INSERT INTO bpm_category (name, code, description, status, sort, creator, create_time, updater, update_time, deleted)
|
SELECT '离职审批', 'hrm_resignation_approve', 'HRM 离职申请审批', 0, 50, '1', NOW(), '1', NOW(), 0
|
WHERE NOT EXISTS (SELECT 1 FROM bpm_category WHERE code = 'hrm_resignation_approve' AND deleted = 0);
|
|
-- 2.6 HRM 调动审批
|
INSERT INTO bpm_category (name, code, description, status, sort, creator, create_time, updater, update_time, deleted)
|
SELECT '调动审批', 'hrm_transfer_approve', 'HRM 调动申请审批', 0, 60, '1', NOW(), '1', NOW(), 0
|
WHERE NOT EXISTS (SELECT 1 FROM bpm_category WHERE code = 'hrm_transfer_approve' AND deleted = 0);
|
|
-- 2.7 MES 报工/生产工单审批（代码侧 MES 报工与工单共用分类 mes_pro_feedback_audit）
|
INSERT INTO bpm_category (name, code, description, status, sort, creator, create_time, updater, update_time, deleted)
|
SELECT '报工审批', 'mes_pro_feedback_audit', 'MES 生产报工/工单审批', 0, 70, '1', NOW(), '1', NOW(), 0
|
WHERE NOT EXISTS (SELECT 1 FROM bpm_category WHERE code = 'mes_pro_feedback_audit' AND deleted = 0);
|
|
-- -----------------------------------------------------------------------------
|
-- ③ 修复结果自查（应全部返回 0）
|
-- -----------------------------------------------------------------------------
|
-- SELECT '空串ENGINE_VERSION_残留' AS chk, COUNT(*) FROM ACT_RE_PROCDEF WHERE ENGINE_VERSION_ = ''
|
-- UNION ALL SELECT '空META_INFO_残留' , COUNT(*) FROM ACT_RE_MODEL WHERE META_INFO_ IS NULL OR META_INFO_ = ''
|
-- UNION ALL SELECT '分类待补' , 0;
|
