-- ============================================================ -- 销售订单审批接入协同办公(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', ' 1 ${ nrOfCompletedInstances >= nrOfInstances } ', 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),如需调整请在「协同办公 -> 流程管理」修改流程 -- ============================================================