-- ============================================================ -- 电力数转增强 - 第二阶段(采购管理)数据库变更 -- 适用:MySQL 8.4+ -- 说明:采购计划模块新表(幂等:已存在则跳过) -- 注意:本脚本不包含 tenant_id(项目未启用多租户) -- ============================================================ -- ---------- 1. ERP 采购计划主表 ---------- CREATE TABLE IF NOT EXISTS `erp_purchase_plan` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号', `no` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '采购计划单号', `name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '计划名称', `status` tinyint NOT NULL DEFAULT '0' COMMENT '状态(0:未提交 10:审批中 20:审核通过 30:审核不通过 40:已取消)', `demand_source` tinyint DEFAULT NULL COMMENT '需求来源(1:库存预警 2:运维/生产计划)', `plan_date` datetime DEFAULT NULL COMMENT '计划日期', `plan_user_id` bigint DEFAULT NULL COMMENT '编制人编号', `plan_dept_id` bigint DEFAULT NULL COMMENT '编制部门编号', `supplier_id` bigint DEFAULT NULL COMMENT '供应商编号(可选)', `total_count` decimal(24,6) NOT NULL DEFAULT '0.000000' COMMENT '合计数量', `generated_flag` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否已生成采购申请', `remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '备注', `process_instance_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'BPM 流程实例编号', `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '创建者', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci 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`) USING BTREE, UNIQUE KEY `uk_no` (`no`) USING BTREE, KEY `idx_status` (`status`) USING BTREE, KEY `idx_demand_source` (`demand_source`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='ERP 采购计划表'; -- ---------- 2. ERP 采购计划明细表 ---------- CREATE TABLE IF NOT EXISTS `erp_purchase_plan_items` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号', `plan_id` bigint NOT NULL COMMENT '采购计划编号', `product_id` bigint NOT NULL COMMENT '产品编号', `product_unit_id` bigint DEFAULT NULL COMMENT '产品单位编号', `count` decimal(24,6) NOT NULL COMMENT '计划采购数量', `demand_time` datetime DEFAULT NULL COMMENT '需求日期', `remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '备注', `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '创建者', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci 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`) USING BTREE, KEY `idx_plan_id` (`plan_id`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='ERP 采购计划明细表'; -- ============================================================ -- 3. 数据字典:采购计划需求来源 -- ============================================================ INSERT INTO `system_dict_type` (`name`, `type`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `deleted_time`) VALUES ('ERP 采购计划需求来源', 'erp_purchase_plan_demand_source', 0, 'ERP 采购计划需求来源(1=库存预警,2=运维/生产计划)', '1', NOW(), '1', NOW(), b'0', NULL) ON DUPLICATE KEY UPDATE `name` = VALUES(`name`); INSERT INTO `system_dict_data` (`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES (1, '库存预警', '1', 'erp_purchase_plan_demand_source', 0, 'warning', '', '安全库存缺量自动生成', '1', NOW(), '1', NOW(), b'0'), (2, '运维/生产计划', '2', 'erp_purchase_plan_demand_source', 0, 'primary', '', '运维/生产计划人工编制', '1', NOW(), '1', NOW(), b'0') ON DUPLICATE KEY UPDATE `label` = VALUES(`label`), `sort` = VALUES(`sort`); -- ============================================================ -- 4. BPM 审批分类:采购计划审核 -- ============================================================ INSERT INTO `bpm_category` (`id`, `name`, `code`, `status`, `sort`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES (208, '采购计划审核', 'purchase_plan_approve', 0, 208, '1', NOW(), '1', NOW(), b'0') ON DUPLICATE KEY UPDATE `name` = VALUES(`name`); -- ============================================================ -- 5. 菜单:采购计划页面 + 按钮权限(父菜单 id=2602 采购管理) -- ============================================================ 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`) VALUES (1065212, '采购计划', '', 2, 1, 2602, 'purchase-plan', 'ep:list', 'erp/purchase/plan/index', 'ErpPurchasePlan', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0') ON DUPLICATE KEY UPDATE `name` = VALUES(`name`); 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`) VALUES (1065213, '采购计划查询', 'erp:purchase-plan:query', 3, 1, 1065212, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'), (1065214, '采购计划创建', 'erp:purchase-plan:create', 3, 2, 1065212, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'), (1065215, '采购计划更新', 'erp:purchase-plan:update', 3, 3, 1065212, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'), (1065216, '采购计划删除', 'erp:purchase-plan:delete', 3, 4, 1065212, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'), (1065217, '采购计划导出', 'erp:purchase-plan:export', 3, 5, 1065212, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'), (1065218, '采购计划提交审批', 'erp:purchase-plan:submit', 3, 6, 1065212, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'), (1065219, '采购计划生成申请', 'erp:purchase-plan:generate-request', 3, 7, 1065212, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0') ON DUPLICATE KEY UPDATE `name` = VALUES(`name`);