-- ============================================================
|
-- 销售订单审批接入协同办公(BPM) - 数据库变更脚本
|
-- 说明:
|
-- 1. 可重复执行(幂等),重复执行不会报错/重复插入
|
-- 2. 项目不使用多租户,脚本不包含 tenant_id
|
-- 3. BPM 流程定义(sale_order_approve)
|
-- 需要在「协同办公 -> 流程管理」中创建并发布,无法用 SQL 完成,见文末说明
|
-- ============================================================
|
|
-- ------------------------------------------------------------
|
-- 1. erp_sale_order 补 BPM 字段(幂等:缺列才加)
|
-- ------------------------------------------------------------
|
SET @s1 = IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='erp_sale_order' AND column_name='process_instance_id')=0,
|
'ALTER TABLE erp_sale_order ADD COLUMN process_instance_id VARCHAR(64) NULL COMMENT ''BPM 流程实例编号'' AFTER remark', 'SELECT 1');
|
PREPARE s1 FROM @s1; EXECUTE s1; DEALLOCATE PREPARE s1;
|
|
-- ------------------------------------------------------------
|
-- 2. 新增 BPM 流程分类:销售订单审批
|
-- ------------------------------------------------------------
|
INSERT INTO bpm_category (id, name, code, description, status, sort, creator, create_time, updater, update_time, deleted)
|
SELECT (SELECT COALESCE(MAX(id), 0) + 1 FROM bpm_category), '销售订单审批', 'sale_order_approve',
|
'销售订单审批流程分类', 0, 30, '1', NOW(), '1', NOW(), 0
|
FROM DUAL
|
WHERE NOT EXISTS (SELECT 1 FROM bpm_category WHERE code = 'sale_order_approve' AND deleted = 0);
|
|
-- ------------------------------------------------------------
|
-- 3. 修正/新增 erp_audit_status 字典(与 ErpAuditStatus 枚举保持一致)
|
-- ------------------------------------------------------------
|
-- 3.1 值 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 COALESCE(MAX(id), 0) + 1 FROM system_dict_data), 0, '未提交', '0', 'erp_audit_status', 0, 'info', '',
|
'销售订单等未提交审批', '1', NOW(), '1', NOW(), 0
|
FROM DUAL
|
WHERE NOT EXISTS (SELECT 1 FROM system_dict_data WHERE dict_type = 'erp_audit_status' AND value = '0' AND deleted = 0);
|
|
-- 3.2 若已存在但标签不是「未提交」,强制修正为「未提交」
|
UPDATE system_dict_data
|
SET label = '未提交', remark = '销售订单等未提交审批', updater = '1', update_time = NOW()
|
WHERE dict_type = 'erp_audit_status' AND value = '0' AND deleted = 0;
|
|
-- ------------------------------------------------------------
|
-- 4. bpm_business_attach 表:流程定义 -> 业务详情组件映射
|
-- 作用:协同办公审批详情页加载对应业务组件展示业务内容
|
-- 若销售订单详情组件路径不是 /erp/sale/order/detail/index.vue,请修改 component_path
|
-- ------------------------------------------------------------
|
CREATE TABLE IF NOT EXISTS `bpm_business_attach` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
|
`process_definition_key` varchar(64) NOT NULL DEFAULT '' COMMENT '流程定义的Key',
|
`business_type` varchar(64) NOT NULL DEFAULT '' COMMENT '业务类型',
|
`component_path` varchar(255) NOT NULL DEFAULT '' COMMENT '业务详情组件路径',
|
`status` tinyint NOT NULL DEFAULT '0' COMMENT '状态',
|
`remark` varchar(255) DEFAULT NULL COMMENT '备注',
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updater` varchar(64) DEFAULT '' COMMENT '更新者',
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='BPM业务附件配置';
|
|
-- 4.1 销售订单审批 -> 销售订单详情组件(协同办公审批页显示订单内容)
|
INSERT INTO bpm_business_attach (id, process_definition_key, business_type, component_path, status, remark, creator, create_time, updater, update_time, deleted)
|
SELECT (SELECT COALESCE(MAX(id), 0) + 1 FROM bpm_business_attach), 'sale_order_approve', 'erp_sale_order',
|
'/erp/sale/order/detail/index.vue', 0, '销售订单审批业务详情组件', '1', NOW(), '1', NOW(), 0
|
FROM DUAL
|
WHERE NOT EXISTS (SELECT 1 FROM bpm_business_attach WHERE process_definition_key = 'sale_order_approve' AND deleted = 0);
|
|
-- ------------------------------------------------------------
|
-- 5. 历史数据迁移:存量「未审核(10)」销售订单调整为「未提交(0)」
|
-- 新逻辑中创建后即为未提交,10 不再用于销售订单
|
-- ------------------------------------------------------------
|
UPDATE erp_sale_order SET status = 0, updater = '1', update_time = NOW()
|
WHERE status = 10 AND deleted = 0;
|
|
-- ============================================================
|
-- 注意:以下流程定义需要在「协同办公 -> 流程管理」中创建并发布,无法用 SQL 完成
|
-- 1. 流程 key: sale_order_approve
|
-- 名称: 销售订单审批
|
-- 分类: sale_order_approve(对应本脚本第 2 步)
|
-- 审批人: 按需配置
|
-- 表单: 普通表单(表单1);配合 bpm_business_attach 展示销售订单详情
|
-- ============================================================
|
