1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
| -- =====================================================
| -- 需求四 外贸订单管理 —— 初始化(幂等)
| -- 1. 建表 trade_foreign_order / trade_foreign_order_item / trade_foreign_receive
| -- 2. 字典 trade_foreign_order_status / trade_delivery_mode / trade_currency / trade_port
| -- 3. 菜单:销售管理(2617) -> 外贸管理 -> 外贸订单 + 按钮权限
| -- 4. BPM 分类 foreign_order_approve
| -- =====================================================
|
| -- 1. 外贸订单主表
| CREATE TABLE IF NOT EXISTS `trade_foreign_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 '订单日期',
| `country` varchar(64) DEFAULT NULL COMMENT '国别',
| `currency` varchar(16) DEFAULT NULL COMMENT '币种(字典 trade_currency)',
| `exchange_rate` decimal(18,6) DEFAULT NULL COMMENT '汇率',
| `delivery_mode` int DEFAULT NULL COMMENT '出货模式(1 厂区订单直发 / 2 港口前置备货)',
| `port` varchar(64) DEFAULT NULL COMMENT '港口(字典 trade_port)',
| `receive_term` int DEFAULT NULL COMMENT '收汇账期(天)',
| `quotation_id` bigint DEFAULT NULL COMMENT '引用报价单编号',
| `quotation_no` varchar(32) DEFAULT NULL COMMENT '引用报价单号',
| `port_batch_code` varchar(64) DEFAULT NULL COMMENT '港口前置备货:港口入库批次号',
| `total_count` decimal(24,6) DEFAULT 0 COMMENT '合计数量',
| `total_foreign_price` decimal(24,6) DEFAULT 0 COMMENT '外币合计金额',
| `total_price` decimal(24,6) DEFAULT 0 COMMENT '人民币合计金额',
| `status` int NOT NULL DEFAULT 0 COMMENT '状态(字典 trade_foreign_order_status)',
| `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_foreign_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 '计量单位编号',
| `foreign_price` decimal(24,6) DEFAULT NULL COMMENT '外币单价',
| `exchange_rate` decimal(18,6) DEFAULT NULL COMMENT '汇率',
| `item_price` decimal(24,6) DEFAULT NULL COMMENT '人民币单价',
| `count` decimal(24,6) NOT NULL DEFAULT 0 COMMENT '数量',
| `total_foreign_price` decimal(24,6) DEFAULT NULL COMMENT '外币小计',
| `total_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='外贸订单明细';
|
| -- 1.2 外贸订单收汇登记
| CREATE TABLE IF NOT EXISTS `trade_foreign_receive` (
| `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
| `no` varchar(32) DEFAULT NULL COMMENT '收汇编号',
| `order_id` bigint NOT NULL COMMENT '外贸订单编号',
| `foreign_amount` decimal(24,6) DEFAULT NULL COMMENT '外币收款金额',
| `exchange_rate` decimal(18,6) DEFAULT NULL COMMENT '结汇汇率',
| `settled_amount` decimal(24,6) DEFAULT NULL COMMENT '结汇金额(人民币)',
| `receive_date` datetime DEFAULT NULL 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. 字典类型
| INSERT INTO `system_dict_type` (`name`,`type`,`status`,`remark`,`creator`,`create_time`,`updater`,`update_time`,`deleted`,`deleted_time`)
| SELECT '外贸订单状态','trade_foreign_order_status',0,'外贸订单状态','1',NOW(),'1',NOW(),b'0',NULL FROM DUAL
| WHERE NOT EXISTS (SELECT 1 FROM `system_dict_type` WHERE `type`='trade_foreign_order_status');
| INSERT INTO `system_dict_type` (`name`,`type`,`status`,`remark`,`creator`,`create_time`,`updater`,`update_time`,`deleted`,`deleted_time`)
| SELECT '出货模式','trade_delivery_mode',0,'外贸出货模式','1',NOW(),'1',NOW(),b'0',NULL FROM DUAL
| WHERE NOT EXISTS (SELECT 1 FROM `system_dict_type` WHERE `type`='trade_delivery_mode');
| INSERT INTO `system_dict_type` (`name`,`type`,`status`,`remark`,`creator`,`create_time`,`updater`,`update_time`,`deleted`,`deleted_time`)
| SELECT '币种','trade_currency',0,'币种','1',NOW(),'1',NOW(),b'0',NULL FROM DUAL
| WHERE NOT EXISTS (SELECT 1 FROM `system_dict_type` WHERE `type`='trade_currency');
| INSERT INTO `system_dict_type` (`name`,`type`,`status`,`remark`,`creator`,`create_time`,`updater`,`update_time`,`deleted`,`deleted_time`)
| SELECT '港口','trade_port',0,'港口','1',NOW(),'1',NOW(),b'0',NULL FROM DUAL
| WHERE NOT EXISTS (SELECT 1 FROM `system_dict_type` WHERE `type`='trade_port');
|
| -- 2.1 字典数据(先删后插)
| DELETE FROM `system_dict_data` WHERE `dict_type` IN ('trade_foreign_order_status','trade_delivery_mode','trade_currency','trade_port');
| 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_foreign_order_status',0,'default','','','1',NOW(),'1',NOW(),b'0'),
| (2,'审批中','10','trade_foreign_order_status',0,'processing','','','1',NOW(),'1',NOW(),b'0'),
| (3,'审核通过','20','trade_foreign_order_status',0,'success','','','1',NOW(),'1',NOW(),b'0'),
| (4,'审核不通过','30','trade_foreign_order_status',0,'error','','','1',NOW(),'1',NOW(),b'0'),
| (5,'已作废','40','trade_foreign_order_status',0,'default','','','1',NOW(),'1',NOW(),b'0'),
| (6,'已完结','50','trade_foreign_order_status',0,'success','','','1',NOW(),'1',NOW(),b'0'),
| (1,'厂区订单直发','1','trade_delivery_mode',0,'primary','','','1',NOW(),'1',NOW(),b'0'),
| (2,'港口前置备货','2','trade_delivery_mode',0,'warning','','','1',NOW(),'1',NOW(),b'0'),
| (1,'美元','USD','trade_currency',0,'primary','','','1',NOW(),'1',NOW(),b'0'),
| (2,'欧元','EUR','trade_currency',0,'success','','','1',NOW(),'1',NOW(),b'0'),
| (3,'人民币','CNY','trade_currency',0,'default','','','1',NOW(),'1',NOW(),b'0'),
| (4,'日元','JPY','trade_currency',0,'warning','','','1',NOW(),'1',NOW(),b'0'),
| (5,'港币','HKD','trade_currency',0,'default','','','1',NOW(),'1',NOW(),b'0'),
| (1,'上海港','上海港','trade_port',0,'primary','','','1',NOW(),'1',NOW(),b'0'),
| (2,'宁波港','宁波港','trade_port',0,'success','','','1',NOW(),'1',NOW(),b'0'),
| (3,'深圳盐田港','深圳盐田港','trade_port',0,'processing','','','1',NOW(),'1',NOW(),b'0'),
| (4,'青岛港','青岛港','trade_port',0,'warning','','','1',NOW(),'1',NOW(),b'0'),
| (5,'天津港','天津港','trade_port',0,'default','','','1',NOW(),'1',NOW(),b'0');
|
| -- 3. 菜单:外贸管理(目录) + 外贸订单(页面) + 按钮
| 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 1067100,'外贸管理','',1,9,2617,'foreign','ep:ship','','',0,b'1',b'1',b'1','1',NOW(),'1',NOW(),b'0'
| WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE id=1067100);
| 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 1067101,'外贸订单','',2,1,1067100,'order','','trade/foreignOrder/index','',0,b'1',b'1',b'1','1',NOW(),'1',NOW(),b'0'
| WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE id=1067101);
| 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 1067102,'外贸订单查询','trade:foreign-order:query',3,1,1067101,'','','','',0,b'1',b'1',b'1','1',NOW(),'1',NOW(),b'0'
| WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE id=1067102);
| 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 1067103,'外贸订单创建','trade:foreign-order:create',3,2,1067101,'','','','',0,b'1',b'1',b'1','1',NOW(),'1',NOW(),b'0'
| WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE id=1067103);
| 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 1067104,'外贸订单更新','trade:foreign-order:update',3,3,1067101,'','','','',0,b'1',b'1',b'1','1',NOW(),'1',NOW(),b'0'
| WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE id=1067104);
| 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 1067105,'外贸订单删除','trade:foreign-order:delete',3,4,1067101,'','','','',0,b'1',b'1',b'1','1',NOW(),'1',NOW(),b'0'
| WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE id=1067105);
| 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 1067106,'外贸订单提交审批','trade:foreign-order:submit',3,5,1067101,'','','','',0,b'1',b'1',b'1','1',NOW(),'1',NOW(),b'0'
| WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE id=1067106);
| 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 1067107,'外贸订单完结','trade:foreign-order:complete',3,6,1067101,'','','','',0,b'1',b'1',b'1','1',NOW(),'1',NOW(),b'0'
| WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE id=1067107);
| 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 1067108,'外贸订单收汇','trade:foreign-order:receive',3,7,1067101,'','','','',0,b'1',b'1',b'1','1',NOW(),'1',NOW(),b'0'
| WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE id=1067108);
| 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 1067109,'外贸订单导出','trade:foreign-order:export',3,8,1067101,'','','','',0,b'1',b'1',b'1','1',NOW(),'1',NOW(),b'0'
| WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE id=1067109);
|
| -- 4. BPM 分类
| INSERT INTO `bpm_category` (`name`,`code`,`description`,`status`,`sort`,`creator`,`create_time`,`updater`,`update_time`,`deleted`)
| SELECT '外贸订单审批','foreign_order_approve','外贸订单审批流程分类',0,27,'1',NOW(),'1',NOW(),0
| WHERE NOT EXISTS (SELECT 1 FROM `bpm_category` WHERE `code`='foreign_order_approve' AND deleted=0);
|
|