-- ============================================================
|
-- 销售订单审批接入协同办公(BPM) - 数据库变更脚本
|
-- 说明:
|
-- 1. 可重复执行(幂等),重复执行不会报错/重复插入
|
-- 2. 项目不使用多租户,脚本不包含 tenant_id
|
-- 3. 本脚本包含 BPM 流程定义(sale_order_approve)的部署,执行后即可使用
|
-- ============================================================
|
|
-- 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;
|
|
-- ============================================================
|
-- 6. BPM 流程定义部署: sale_order_approve(幂等,如已存在则跳过)
|
-- ============================================================
|
INSERT INTO ACT_RE_DEPLOYMENT (ID_, NAME_, CATEGORY_, KEY_, TENANT_ID_, DEPLOY_TIME_, DERIVED_FROM_, DERIVED_FROM_ROOT_, PARENT_DEPLOYMENT_ID_, ENGINE_VERSION_)
|
SELECT '532b0fc9-a1cb-11f1-b320-00ff263cc5e4', '销售订单审批', 'sale_order_approve', 'sale_order_approve', '', NOW(), NULL, NULL, '532b0fc9-a1cb-11f1-b320-00ff263cc5e4', NULL FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM ACT_RE_PROCDEF WHERE KEY_='sale_order_approve');
|
INSERT INTO ACT_RE_PROCDEF (ID_, REV_, CATEGORY_, NAME_, KEY_, VERSION_, DEPLOYMENT_ID_, RESOURCE_NAME_, DGRM_RESOURCE_NAME_, DESCRIPTION_, HAS_START_FORM_KEY_, HAS_GRAPHICAL_NOTATION_, SUSPENSION_STATE_, TENANT_ID_, ENGINE_VERSION_)
|
SELECT 'sale_order_approve:1:537fe68c-a1cb-11f1-b320-00ff263cc5e4', 1, 'sale_order_approve', '销售订单审批', 'sale_order_approve', '1', '532b0fc9-a1cb-11f1-b320-00ff263cc5e4', 'sale_order_approve.bpmn', 'sale_order_approve.sale_order_approve.png', NULL, '0', '1', '1', '', NULL FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM ACT_RE_PROCDEF WHERE KEY_='sale_order_approve');
|
INSERT INTO ACT_GE_BYTEARRAY (ID_, REV_, NAME_, DEPLOYMENT_ID_, BYTES_, GENERATED_)
|
SELECT '532b0fca-a1cb-11f1-b320-00ff263cc5e4', 1, 'sale_order_approve.bpmn', '532b0fc9-a1cb-11f1-b320-00ff263cc5e4', '<?xml version="1.0" encoding="UTF-8"?>
|
<definitions xmlns="http://www.omg.org/spec/BPMN/20100524/MODEL" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:flowable="http://flowable.org/bpmn" xmlns:bpmndi="http://www.omg.org/spec/BPMN/20100524/DI" xmlns:omgdc="http://www.omg.org/spec/DD/20100524/DC" xmlns:omgdi="http://www.omg.org/spec/DD/20100524/DI" typeLanguage="http://www.w3.org/2001/XMLSchema" expressionLanguage="http://www.w3.org/1999/XPath" targetNamespace="http://www.omg.org/spec/BPMN/20100524/MODEL">
|
<process id="sale_order_approve" name="销售订单审批" isExecutable="true">
|
<startEvent id="StartEvent" name="开始"></startEvent>
|
<userTask id="StartUserNode" name="发起人">
|
<extensionElements>
|
<flowable:approveType xmlns:flowable="http://flowable.org/bpmn"><![CDATA[1]]></flowable:approveType>
|
<flowable:candidateStrategy xmlns:flowable="http://flowable.org/bpmn"><![CDATA[36]]></flowable:candidateStrategy>
|
<flowable:buttonsSetting xmlns:flowable="http://flowable.org/bpmn" displayName="提交" enable="true" id="1"></flowable:buttonsSetting>
|
<flowable:buttonsSetting xmlns:flowable="http://flowable.org/bpmn" displayName="拒绝" enable="false" id="2"></flowable:buttonsSetting>
|
<flowable:buttonsSetting xmlns:flowable="http://flowable.org/bpmn" displayName="转办" enable="false" id="3"></flowable:buttonsSetting>
|
<flowable:buttonsSetting xmlns:flowable="http://flowable.org/bpmn" displayName="委派" enable="false" id="4"></flowable:buttonsSetting>
|
<flowable:buttonsSetting xmlns:flowable="http://flowable.org/bpmn" displayName="加签" enable="false" id="5"></flowable:buttonsSetting>
|
<flowable:buttonsSetting xmlns:flowable="http://flowable.org/bpmn" displayName="退回" enable="false" id="6"></flowable:buttonsSetting>
|
<flowable:assignStartUserHandlerType xmlns:flowable="http://flowable.org/bpmn"><![CDATA[2]]></flowable:assignStartUserHandlerType>
|
</extensionElements>
|
</userTask>
|
<userTask id="Activity_sale_order_approve" name="审批人">
|
<extensionElements>
|
<flowable:approveType xmlns:flowable="http://flowable.org/bpmn"><![CDATA[1]]></flowable:approveType>
|
<flowable:candidateStrategy xmlns:flowable="http://flowable.org/bpmn"><![CDATA[30]]></flowable:candidateStrategy>
|
<flowable:candidateParam xmlns:flowable="http://flowable.org/bpmn"><![CDATA[1]]></flowable:candidateParam>
|
<flowable:buttonsSetting xmlns:flowable="http://flowable.org/bpmn" displayName="通过" enable="true" id="1"></flowable:buttonsSetting>
|
<flowable:buttonsSetting xmlns:flowable="http://flowable.org/bpmn" displayName="拒绝" enable="true" id="2"></flowable:buttonsSetting>
|
<flowable:buttonsSetting xmlns:flowable="http://flowable.org/bpmn" displayName="转办" enable="true" id="3"></flowable:buttonsSetting>
|
<flowable:buttonsSetting xmlns:flowable="http://flowable.org/bpmn" displayName="委派" enable="true" id="4"></flowable:buttonsSetting>
|
<flowable:buttonsSetting xmlns:flowable="http://flowable.org/bpmn" displayName="加签" enable="true" id="5"></flowable:buttonsSetting>
|
<flowable:buttonsSetting xmlns:flowable="http://flowable.org/bpmn" displayName="退回" enable="true" id="6"></flowable:buttonsSetting>
|
<flowable:approveMethod xmlns:flowable="http://flowable.org/bpmn"><![CDATA[4]]></flowable:approveMethod>
|
<flowable:rejectHandlerType xmlns:flowable="http://flowable.org/bpmn"><![CDATA[1]]></flowable:rejectHandlerType>
|
<flowable:assignStartUserHandlerType xmlns:flowable="http://flowable.org/bpmn"><![CDATA[1]]></flowable:assignStartUserHandlerType>
|
<flowable:assignEmptyHandlerType xmlns:flowable="http://flowable.org/bpmn"><![CDATA[1]]></flowable:assignEmptyHandlerType>
|
<flowable:signEnable xmlns:flowable="http://flowable.org/bpmn"><![CDATA[false]]></flowable:signEnable>
|
<flowable:reasonRequire xmlns:flowable="http://flowable.org/bpmn"><![CDATA[false]]></flowable:reasonRequire>
|
<flowable:nodeType xmlns:flowable="http://flowable.org/bpmn"><![CDATA[11]]></flowable:nodeType>
|
</extensionElements>
|
<multiInstanceLoopCharacteristics isSequential="true" flowable:collection="${coll_userList}">
|
<loopCardinality>1</loopCardinality>
|
<completionCondition>${ nrOfCompletedInstances >= nrOfInstances }</completionCondition>
|
</multiInstanceLoopCharacteristics>
|
</userTask>
|
<endEvent id="EndEvent" name="结束"></endEvent>
|
<sequenceFlow id="sequenceFlow-55e2f6d8-5af6-4ea5-924b-e6ad6eee3d5f" sourceRef="StartEvent" targetRef="StartUserNode"></sequenceFlow>
|
<sequenceFlow id="sequenceFlow-37666882-17da-46ee-840f-7de7a2211060" sourceRef="StartUserNode" targetRef="Activity_sale_order_approve"></sequenceFlow>
|
<sequenceFlow id="sequenceFlow-b0342e71-0163-43ce-83d4-955beba1eda4" sourceRef="Activity_sale_order_approve" targetRef="EndEvent"></sequenceFlow>
|
</process>
|
<bpmndi:BPMNDiagram id="BPMNDiagram_sale_order_approve">
|
<bpmndi:BPMNPlane bpmnElement="sale_order_approve" id="BPMNPlane_sale_order_approve">
|
<bpmndi:BPMNShape bpmnElement="StartUserNode" id="BPMNShape_StartUserNode">
|
<omgdc:Bounds height="60.0" width="100.0" x="80.0" y="0.0"></omgdc:Bounds>
|
</bpmndi:BPMNShape>
|
<bpmndi:BPMNShape bpmnElement="Activity_sale_order_approve" id="BPMNShape_Activity_sale_order_approve">
|
<omgdc:Bounds height="60.0" width="100.0" x="230.0" y="0.0"></omgdc:Bounds>
|
</bpmndi:BPMNShape>
|
<bpmndi:BPMNShape bpmnElement="StartEvent" id="BPMNShape_StartEvent">
|
<omgdc:Bounds height="30.0" width="30.0" x="0.0" y="15.0"></omgdc:Bounds>
|
</bpmndi:BPMNShape>
|
<bpmndi:BPMNShape bpmnElement="EndEvent" id="BPMNShape_EndEvent">
|
<omgdc:Bounds height="30.0" width="30.0" x="380.0" y="15.0"></omgdc:Bounds>
|
</bpmndi:BPMNShape>
|
<bpmndi:BPMNEdge bpmnElement="sequenceFlow-37666882-17da-46ee-840f-7de7a2211060" id="BPMNEdge_sequenceFlow-37666882-17da-46ee-840f-7de7a2211060">
|
<omgdi:waypoint x="180.0" y="30.0"></omgdi:waypoint>
|
<omgdi:waypoint x="230.0" y="30.0"></omgdi:waypoint>
|
</bpmndi:BPMNEdge>
|
<bpmndi:BPMNEdge bpmnElement="sequenceFlow-55e2f6d8-5af6-4ea5-924b-e6ad6eee3d5f" id="BPMNEdge_sequenceFlow-55e2f6d8-5af6-4ea5-924b-e6ad6eee3d5f">
|
<omgdi:waypoint x="30.0" y="30.0"></omgdi:waypoint>
|
<omgdi:waypoint x="80.0" y="30.0"></omgdi:waypoint>
|
</bpmndi:BPMNEdge>
|
<bpmndi:BPMNEdge bpmnElement="sequenceFlow-b0342e71-0163-43ce-83d4-955beba1eda4" id="BPMNEdge_sequenceFlow-b0342e71-0163-43ce-83d4-955beba1eda4">
|
<omgdi:waypoint x="330.0" y="30.0"></omgdi:waypoint>
|
<omgdi:waypoint x="380.0" y="30.0"></omgdi:waypoint>
|
</bpmndi:BPMNEdge>
|
</bpmndi:BPMNPlane>
|
</bpmndi:BPMNDiagram>
|
</definitions>', 0 FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM ACT_GE_BYTEARRAY WHERE NAME_='sale_order_approve.bpmn');
|
INSERT INTO ACT_RE_MODEL (ID_, REV_, NAME_, KEY_, CATEGORY_, CREATE_TIME_, LAST_UPDATE_TIME_, VERSION_, META_INFO_, DEPLOYMENT_ID_, TENANT_ID_)
|
SELECT '52b96236-a1cb-11f1-b320-00ff263cc5e4', 1, '销售订单审批', 'sale_order_approve', 'sale_order_approve', NOW(), NOW(), '1', '{"allowCancelRunningProcess":true,"allowWithdrawTask":true,"autoApprovalType":null,"description":"销售订单提交后进入协同办公审批","formCustomCreatePath":null,"formCustomViewPath":null,"formId":1,"formType":10,"icon":null,"managerUserIds":[1],"printTemplateSetting":null,"processAfterTriggerSetting":null,"processBeforeTriggerSetting":null,"processIdRule":null,"sort":1787584300000,"startDeptIds":null,"startUserIds":null,"summarySetting":null,"taskAfterTriggerSetting":null,"taskBeforeTriggerSetting":null,"titleSetting":null,"type":20,"visible":true}', '532b0fc9-a1cb-11f1-b320-00ff263cc5e4', '' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM ACT_RE_MODEL WHERE KEY_='sale_order_approve');
|
INSERT INTO bpm_process_definition_info (process_definition_id, model_id, model_type, category, icon, description, form_type, form_id, form_conf, form_fields, form_custom_create_path, form_custom_view_path, simple_model, visible, sort, start_user_ids, start_dept_ids, manager_user_ids, allow_cancel_running_process, allow_withdraw_task, auto_approval_type, creator, create_time, updater, update_time, deleted)
|
SELECT 'sale_order_approve:1:537fe68c-a1cb-11f1-b320-00ff263cc5e4', '52b96236-a1cb-11f1-b320-00ff263cc5e4', '20', 'sale_order_approve', NULL, '销售订单提交后进入协同办公审批', '10', '1', '{"form":{"layout":"horizontal","labelAlign":"right","size":"middle","colon":false,"labelCol":{"style":{"width":"125px"}},"wrapperCol":{"span":24},"labelWidth":"100px"},"resetBtn":{"show":false,"innerText":"重置"},"submitBtn":{"show":true,"innerText":"提交"}}', '["{\\"type\\":\\"input\\",\\"field\\":\\"Fyqymqqdoln2abc\\",\\"title\\":\\"姓名\\",\\"info\\":\\"\\",\\"$required\\":false,\\"_fc_id\\":\\"id_Fuplmqqdoln2acc\\",\\"name\\":\\"ref_F18omqqdoln2adc\\",\\"_fc_drag_tag\\":\\"input\\",\\"display\\":true,\\"hidden\\":false}", "{\\"type\\":\\"input\\",\\"field\\":\\"Fh6gmqqdooy5aec\\",\\"title\\":\\"年龄\\",\\"info\\":\\"\\",\\"$required\\":false,\\"_fc_id\\":\\"id_F5whmqqdooy5afc\\",\\"name\\":\\"ref_Fltemqqdooy5agc\\",\\"_fc_drag_tag\\":\\"input\\",\\"display\\":true,\\"hidden\\":false}", "{\\"type\\":\\"select\\",\\"field\\":\\"F5mrmqqdutnnabc\\",\\"title\\":\\"选择器\\",\\"info\\":\\"\\",\\"effect\\":{\\"fetch\\":\\"\\"},\\"$required\\":false,\\"options\\":[{\\"label\\":\\"选项01\\",\\"value\\":\\"1\\"},{\\"label\\":\\"选项02\\",\\"value\\":\\"2\\"},{\\"label\\":\\"选项03\\",\\"value\\":\\"3\\"}],\\"_fc_id\\":\\"id_Fhnumqqdutnoacc\\",\\"name\\":\\"ref_Fd1wmqqdutnoadc\\",\\"_fc_drag_tag\\":\\"select\\",\\"display\\":true,\\"hidden\\":false}", "{\\"type\\":\\"ApiSelect\\",\\"field\\":\\"dc4b501620bb4c8184aaaeb66398324a\\",\\"title\\":\\"接口选择器\\",\\"info\\":\\"\\",\\"$required\\":false,\\"props\\":{\\"method\\":\\"GET\\"},\\"_fc_id\\":\\"id_F6ewmrbn96xdc3c\\",\\"name\\":\\"ref_F1jomrbn96xdc4c\\",\\"display\\":true,\\"hidden\\":false,\\"_fc_drag_tag\\":\\"ApiSelect\\"}"]', NULL, NULL, '{"buttonsSetting":[{"displayName":"提交","enable":true,"id":1},{"displayName":"拒绝","enable":false,"id":2},{"displayName":"转办","enable":false,"id":3},{"displayName":"委派","enable":false,"id":4},{"displayName":"加签","enable":false,"id":5},{"displayName":"退回","enable":false,"id":6}],"childNode":{"approveMethod":4,"approveType":1,"assignEmptyHandler":{"type":1,"userIds":null},"assignStartUserHandlerType":1,"buttonsSetting":[{"displayName":"通过","enable":true,"id":1},{"displayName":"拒绝","enable":true,"id":2},{"displayName":"转办","enable":true,"id":3},{"displayName":"委派","enable":true,"id":4},{"displayName":"加签","enable":true,"id":5},{"displayName":"退回","enable":true,"id":6}],"candidateParam":"1","candidateStrategy":30,"childNode":{"id":"EndEvent","name":"结束","type":1},"fieldsPermission":[],"id":"Activity_sale_order_approve","name":"审批人","reasonRequire":false,"rejectHandler":{"returnNodeId":null,"type":1},"showText":"指定成员或指定角色审批","signEnable":false,"skipExpression":"","taskAssignListener":{"body":[],"enable":false,"header":[],"path":null},"taskCompleteListener":{"body":[],"enable":false,"header":[],"path":null},"taskCreateListener":{"body":[],"enable":false,"header":[],"path":null},"timeoutHandler":{"enable":false,"maxRemindCount":null,"timeDuration":null,"type":null},"type":11},"fieldsPermission":[],"id":"StartUserNode","name":"发起人","showText":"发起人","type":10}', 1, '1787584300000', '', NULL, '1', 1, 1, NULL, '1', NOW(), '1', NOW(), 0 FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM bpm_process_definition_info WHERE deleted=0 AND category='sale_order_approve');
|
|
-- ============================================================
|
-- 说明:以上为销售订单审批接入协同办公的全部数据库变更
|
-- 1. 步骤 1-5 为表结构/分类/字典/业务映射/存量数据迁移
|
-- 2. 步骤 6 为 BPM 流程定义(sale_order_approve)部署
|
-- 3. 审批人默认为用户 id=1(admin),如需调整请在「协同办公 -> 流程管理」修改
|
-- ============================================================
|
-- ============================================================
|
-- 全部数据库变更已完成
|
-- 1. 步骤 1-5: 表结构 / BPM分类 / 审批字典 / 业务详情映射 / 存量数据迁移
|
-- 2. 步骤 6: BPM 流程定义(sale_order_approve)部署(幂等)
|
-- 3. 审批人默认为用户 id=1(admin),如需调整请在「协同办公 -> 流程管理」修改流程
|
-- ============================================================
|
