-- ===================================================== -- 需求三 内贸订单管理 —— 初始化(幂等) -- 1. 建表 trade_domestic_order / trade_domestic_order_item -- 2. crm_receivable 增加 domestic_order_id(回款关联内贸订单) -- 3. 字典 trade_domestic_order_status -- 4. 菜单:销售管理(2617) -> 内贸管理(1067000) -> 内贸订单(1067001) + 按钮权限 -- 5. BPM 流程分类 domestic_order_approve -- ===================================================== -- 1. 内贸订单主表 CREATE TABLE IF NOT EXISTS `trade_domestic_order` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号', `no` varchar(32) NOT NULL COMMENT '内贸订单号', `customer_id` bigint NOT NULL COMMENT '客户编号', `sale_user_id` bigint DEFAULT NULL COMMENT '业务员编号', `order_time` datetime DEFAULT NULL COMMENT '订单日期', `delivery_address` varchar(255) DEFAULT NULL COMMENT '交货地址', `total_count` decimal(24,6) DEFAULT 0 COMMENT '合计数量', `total_price` decimal(24,6) DEFAULT 0 COMMENT '合计金额(元)', `status` int NOT NULL DEFAULT 0 COMMENT '状态(字典 trade_domestic_order_status:0草稿/10审批中/20审核通过/30审核不通过/40已作废/50已完结)', `process_instance_id` varchar(64) DEFAULT NULL COMMENT '工作流编号', `out_status` int NOT NULL DEFAULT 0 COMMENT '出库状态(0未出库/1部分出库/2全部出库)', `out_count` decimal(24,6) DEFAULT 0 COMMENT '已出库数量', `remark` varchar(1024) 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 '是否删除', `tenant_id` bigint NOT NULL DEFAULT 0 COMMENT '租户编号', PRIMARY KEY (`id`), UNIQUE KEY `uk_no` (`no`), KEY `idx_customer_id` (`customer_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='内贸订单'; -- 1.1 内贸订单明细表 CREATE TABLE IF NOT EXISTS `trade_domestic_order_item` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号', `order_id` bigint NOT NULL COMMENT '订单编号', `item_id` bigint NOT NULL COMMENT 'MDM 物料编号', `item_unit_id` bigint DEFAULT NULL COMMENT '计量单位编号', `item_price` decimal(24,6) DEFAULT NULL COMMENT '单价(元)', `count` decimal(24,6) NOT NULL DEFAULT 0 COMMENT '数量', `total_price` decimal(24,6) DEFAULT NULL COMMENT '小计金额(元)', `tax_percent` decimal(10,2) DEFAULT NULL COMMENT '税率(%)', `tax_price` decimal(24,6) DEFAULT NULL COMMENT '税额(元)', `batch_code` varchar(64) DEFAULT NULL COMMENT '上游原料车次批次(成品原料溯源,预留)', `out_count` decimal(24,6) DEFAULT 0 COMMENT '已出库数量', `remark` varchar(256) 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 '是否删除', `tenant_id` bigint NOT NULL DEFAULT 0 COMMENT '租户编号', PRIMARY KEY (`id`), KEY `idx_order_id` (`order_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='内贸订单明细'; -- 2. crm_receivable 增加内贸订单关联(幂等) SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='crm_receivable' AND COLUMN_NAME='domestic_order_id'); SET @d := IF(@c=0, 'ALTER TABLE `crm_receivable` ADD COLUMN `domestic_order_id` bigint NULL COMMENT ''关联内贸订单编号(需求三)'' AFTER `contract_id`', 'SELECT 1'); PREPARE s FROM @d; EXECUTE s; DEALLOCATE PREPARE s; -- 3. 字典:trade_domestic_order_status INSERT INTO `system_dict_type` (`name`,`type`,`status`,`remark`,`creator`,`create_time`,`updater`,`update_time`,`deleted`,`deleted_time`) SELECT '内贸订单状态','trade_domestic_order_status',0,'内贸订单审批/流转状态','1',NOW(),'1',NOW(),b'0',NULL FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_dict_type` WHERE `type`='trade_domestic_order_status'); DELETE FROM `system_dict_data` WHERE `dict_type`='trade_domestic_order_status'; 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,'草稿','0','trade_domestic_order_status',0,'default','','','1',NOW(),'1',NOW(),b'0'), (2,'审批中','10','trade_domestic_order_status',0,'processing','','','1',NOW(),'1',NOW(),b'0'), (3,'审核通过','20','trade_domestic_order_status',0,'success','','','1',NOW(),'1',NOW(),b'0'), (4,'审核不通过','30','trade_domestic_order_status',0,'error','','','1',NOW(),'1',NOW(),b'0'), (5,'已作废','40','trade_domestic_order_status',0,'default','','','1',NOW(),'1',NOW(),b'0'), (6,'已完结','50','trade_domestic_order_status',0,'success','','','1',NOW(),'1',NOW(),b'0'); -- 4. 菜单:内贸管理(目录) + 内贸订单(页面) + 8 个按钮(逐个幂等) 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`) SELECT 1067000,'内贸管理','',1,8,2617,'domestic','ep:box','','',0,b'1',b'1',b'1','1',NOW(),'1',NOW(),b'0' WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE id=1067000); 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`) SELECT 1067001,'内贸订单','',2,1,1067000,'order','','trade/domesticOrder/index','',0,b'1',b'1',b'1','1',NOW(),'1',NOW(),b'0' WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE id=1067001); 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`) SELECT 1067002,'内贸订单查询','trade:domestic-order:query',3,1,1067001,'','','','',0,b'1',b'1',b'1','1',NOW(),'1',NOW(),b'0' WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE id=1067002); 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`) SELECT 1067003,'内贸订单创建','trade:domestic-order:create',3,2,1067001,'','','','',0,b'1',b'1',b'1','1',NOW(),'1',NOW(),b'0' WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE id=1067003); 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`) SELECT 1067004,'内贸订单更新','trade:domestic-order:update',3,3,1067001,'','','','',0,b'1',b'1',b'1','1',NOW(),'1',NOW(),b'0' WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE id=1067004); 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`) SELECT 1067005,'内贸订单删除','trade:domestic-order:delete',3,4,1067001,'','','','',0,b'1',b'1',b'1','1',NOW(),'1',NOW(),b'0' WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE id=1067005); 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`) SELECT 1067006,'内贸订单提交审批','trade:domestic-order:submit',3,5,1067001,'','','','',0,b'1',b'1',b'1','1',NOW(),'1',NOW(),b'0' WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE id=1067006); 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`) SELECT 1067007,'内贸订单完结','trade:domestic-order:complete',3,6,1067001,'','','','',0,b'1',b'1',b'1','1',NOW(),'1',NOW(),b'0' WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE id=1067007); 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`) SELECT 1067008,'内贸订单作废','trade:domestic-order:cancel',3,7,1067001,'','','','',0,b'1',b'1',b'1','1',NOW(),'1',NOW(),b'0' WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE id=1067008); 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`) SELECT 1067009,'内贸订单导出','trade:domestic-order:export',3,8,1067001,'','','','',0,b'1',b'1',b'1','1',NOW(),'1',NOW(),b'0' WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE id=1067009); -- 5. BPM 流程分类:内贸订单审批(幂等) INSERT INTO `bpm_category` (`name`,`code`,`description`,`status`,`sort`,`creator`,`create_time`,`updater`,`update_time`,`deleted`) SELECT '内贸订单审批','domestic_order_approve','内贸订单审批流程分类',0,26,'1',NOW(),'1',NOW(),0 WHERE NOT EXISTS (SELECT 1 FROM `bpm_category` WHERE `code`='domestic_order_approve' AND deleted=0); -- 6. 出库按钮权限(幂等) 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`) SELECT 1067010,'内贸订单出库','trade:domestic-order:outbound',3,9,1067001,'','','','',0,b'1',b'1',b'1','1',NOW(),'1',NOW(),b'0' WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE id=1067010);