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
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
INSERT INTO `system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`,
`creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`)
VALUES (8, '财务审核', '8', 'bpm_model_category', 0, 'default', '', NULL, '1', '2022-12-11 20:48:45', '1',
'2023-01-15 18:29:50', b'0', 'finance approva');
DROP TABLE IF EXISTS `ecw_receipt_approval`;
CREATE TABLE `ecw_receipt_approval`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`receipt_id` bigint NOT NULL COMMENT '收款单ID',
`receipt_no` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '收款单编号',
`receipt_item_id` bigint DEFAULT NULL COMMENT '收款单ID',
`bmp_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '流程表单实例ID',
`bmp_key` varchar(100) NOT NULL COMMENT '流程实例KEY类型参考:WorkFlowEmus',
`status` tinyint DEFAULT '1' COMMENT '状态：1 处理中 2 通过 3 不通过 4 取消',
`reason` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '申请原因',
`processing_results` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '处理结果',
`creator` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '创建者',
`create_time` datetime NOT NULL COMMENT '创建时间',
`updater` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '更新者',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
`deleted` bit(1) DEFAULT b'0' COMMENT '是否删除',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='收款单或者收款明细审核表';
alter table ecw_receipt add column `tax_point` decimal(10,2) DEFAULT NULL COMMENT '税点';
alter table ecw_receipt_item add column `tax_point` decimal(10,2) DEFAULT '0.00' COMMENT '税点';
alter table ecw_receipt_item add column `tax_amount` decimal(15,2) DEFAULT '0.00' COMMENT '含税金额';
alter table ecw_receivable add column `tax_point` decimal(10,2) DEFAULT '0.00' COMMENT '税点';
alter table ecw_receivable add column `tax_amount` decimal(15,2) DEFAULT '0.00' COMMENT '含税金额';
alter table ecw_receipt add column `bmp_status` tinyint DEFAULT NULL COMMENT '流程状态：1 处理中 2 通过 3 不通过 4 取消';
alter table ecw_receipt_item add column `bmp_status` tinyint DEFAULT NULL COMMENT '流程状态：1 处理中 2 通过 3 不通过 4 取消';
DROP TABLE IF EXISTS `ecw_payment_approval`;
CREATE TABLE `ecw_payment_approval`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`payment_id` bigint NOT NULL COMMENT '付款单ID',
`payment_no` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '付款单编号',
`bmp_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '流程表单实例ID',
`bmp_key` varchar(100) NOT NULL COMMENT '流程实例KEY类型参考:WorkFlowEmus',
`status` tinyint DEFAULT '1' COMMENT '状态：1 处理中 2 通过 3 不通过 4 取消',
`reason` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '申请原因',
`processing_results` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '处理结果',
`creator` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '创建者',
`create_time` datetime NOT NULL COMMENT '创建时间',
`updater` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '更新者',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
`deleted` bit(1) DEFAULT b'0' COMMENT '是否删除',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='付款单审核表';
alter table ecw_payment add column `bmp_status` tinyint DEFAULT NULL COMMENT '流程状态：1 处理中 2 通过 3 不通过 4 取消';
alter table ecw_receipt add column `bmp_id` varchar(100) DEFAULT NULL COMMENT '当前流程ID';
alter table ecw_receipt_item add column `bmp_id` varchar(100) DEFAULT NULL COMMENT '当前流程ID';
alter table ecw_payment add column `bmp_id` varchar(100) DEFAULT NULL COMMENT '当前流程ID';
INSERT INTO `system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`)
VALUES ( 7, '审核中', '7', 'receipt_state', 0, 'default', '', NULL, '1', '2022-08-31 10:24:39', '115', '2023-01-15 18:09:20', b'0', 'APPROVE_ING');
INSERT INTO `system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`)
VALUES ( 8, '审核失败', '8', 'receipt_state', 0, 'default', '', NULL, '1', '2022-08-31 10:24:39', '115', '2023-01-15 18:09:20', b'0', 'APPROVE_FAIL');
INSERT INTO `system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`)
VALUES ( 9, '反审核中', '9', 'receipt_state', 0, 'default', '', NULL, '1', '2022-08-31 10:24:39', '115', '2023-01-15 18:09:20', b'0', 'APPROVE_NEGATION');
INSERT INTO `system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`)
VALUES ( 10, '全部核销中', '10', 'receipt_state', 0, 'default', '', NULL, '1', '2022-08-31 10:24:39', '115', '2023-01-15 18:09:20', b'0', 'WRITE_OFF_ALL_ING');
INSERT INTO `system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`)
VALUES ( 11, '反核销审核中', '11', 'receipt_state', 0, 'default', '', NULL, '1', '2022-08-31 10:24:39', '115', '2023-01-15 18:09:20', b'0', 'WRITE_OFF_ALL_NEGATION');
delete from system_dict_data t where t.dict_type='payment_state';
INSERT INTO `system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`)
VALUES (1, '草稿', '0', 'payment_state', 0, 'default', '', NULL, '1', '2022-08-18 10:11:57', '1', '2023-01-15 18:09:17', b'0', 'DRAFT');
INSERT INTO `system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`)
VALUES (2, '待审核', '1', 'payment_state', 0, 'default', '', NULL, '1', '2022-08-18 10:11:57', '1', '2023-01-15 18:09:17', b'0', 'APPROVE_ING');
INSERT INTO `system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`)
VALUES (3, '审批驳回', '2', 'payment_state', 0, 'default', '', NULL, '1', '2022-08-18 10:11:57', '1', '2023-01-15 18:09:17', b'0', 'APPROVE_FAIL');
INSERT INTO `system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`)
VALUES (4, '反审核中', '3', 'payment_state', 0, 'default', '', NULL, '1', '2022-08-18 10:11:57', '1', '2023-01-15 18:09:17', b'0', 'APPROVE_NEGATION');
INSERT INTO `system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`)
VALUES (5, '待核销', '4', 'payment_state', 0, 'default', '', NULL, '1', '2022-08-18 10:11:57', '1', '2023-01-15 18:09:17', b'0', 'WRITE_OFF_WAITING');
INSERT INTO `system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`)
VALUES (6, '核销审核中', '5', 'payment_state', 0, 'default', '', NULL, '1', '2022-08-18 10:11:57', '1', '2023-01-15 18:09:17', b'0', 'WRITE_OFF_APPROVE_ING');
INSERT INTO `system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`)
VALUES (7, '已核销', '6', 'payment_state', 0, 'default', '', NULL, '1', '2022-08-18 10:11:57', '1', '2023-01-15 18:09:17', b'0', 'WRITE_OFF');
INSERT INTO `system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`)
VALUES (8, '反核销审核中', '7', 'payment_state', 0, 'default', '', NULL, '1', '2022-08-18 10:11:57', '1', '2023-01-15 18:09:17', b'0', 'WRITE_OFF_NO_APPROVE_ING');
INSERT INTO `system_dict_type`(`name`, `type`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
('收款明细状态', 'receipt_item_state', 0, '状态: 0待核销，1已核销，2核销审核中，3反核销审核中', '1', '2022-07-11 21:20:46', '1', '2022-07-11 21:20:46', b'0');
INSERT INTO `system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`)
VALUES (1, '待核销', '0', 'receipt_item_state', 0, 'default', '', NULL, '1', '2022-08-18 10:11:57', '1', '2023-01-15 18:09:17', b'0', 'WRITE_OFF_NO_APPROVE_ING');
INSERT INTO `system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`)
VALUES (2, '已核销', '1', 'receipt_item_state', 0, 'default', '', NULL, '1', '2022-08-18 10:11:57', '1', '2023-01-15 18:09:17', b'0', 'WRITE_OFF_NO_APPROVE_ING');
INSERT INTO `system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`)
VALUES (3, '核销审核中', '2', 'receipt_item_state', 0, 'default', '', NULL, '1', '2022-08-18 10:11:57', '1', '2023-01-15 18:09:17', b'0', 'WRITE_OFF_NO_APPROVE_ING');
INSERT INTO `system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`)
VALUES (4, '反核销审核中', '3', 'receipt_item_state', 0, 'default', '', NULL, '1', '2022-08-18 10:11:57', '1', '2023-01-15 18:09:17', b'0', 'WRITE_OFF_NO_APPROVE_ING');
-- 对比空运客户
CREATE DEFINER = `root` @`%` PROCEDURE `compare_air_customer` ()
BEGIN
DECLARE s INT DEFAULT 0;
DECLARE cname VARCHAR ( 100 );
DECLARE cphone VARCHAR ( 100 );
DECLARE ccustomer VARCHAR ( 100 );-- 定义游标，并将sql结果集赋值到游标中，report为游标名
DECLARE report CURSOR FOR SELECT t.NAME, replace( concat(t.area_code,t.area_phone),'+',''),t.customer_name FROM tmp_air_customer t;-- 声明当游标遍历完后将标志变量置为某个值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET s = 1;-- 打开游标
OPEN report;-- 将游标中的值赋值给变量，注意：变量名不要与sql返回的列名相同，变量顺序要和sql结果列的顺序一致
FETCH report INTO cname,cphone,ccustomer;-- 当s不等于1时，也就是未遍历完时，会一直循环
WHILE s <> 1 DO
IF cphone <> '' AND ccustomer <> '' THEN
SET @new_count = ( SELECT count( 1 ) FROM ecw_customer_contacts s WHERE s.deleted = 0 and concat_ws( '', s.area_code, s.phone_new ) = cphone );
IF @new_count = 0 THEN
INSERT INTO `compare_air_customer_result` ( `name`, `phone`, `customer_name`, `type` )VALUES( cname, cphone, ccustomer, '1' );
ELSE
SET @customer_id = ( SELECT customer_id FROM ecw_customer_contacts s WHERE s.deleted = 0 and concat_ws( '', s.area_code, s.phone_new ) = cphone LIMIT 1 );
SET @tphone=cphone;
SET @tcustomer=ccustomer;
IF ifnull(@customer_id,0) > 0 THEN
SET @customer_service_name = (SELECT s.username FROM system_user s WHERE s.id =(SELECT customer_service FROM ecw_customer WHERE id = @customer_id ));
IF LOCATE( @customer_service_name, ccustomer ) > 0 THEN
INSERT INTO `compare_air_customer_result` ( `number`, `name`, `phone`, `old_customer_name`, `type` )
SELECT number,NAME,@tphone,@tcustomer,'2' FROM ecw_customer WHERE id = @customer_id;
ELSE
SET @is_open_sea = ( SELECT is_in_open_sea FROM ecw_customer WHERE id = @customer_id );
IF @is_open_sea = 1 THEN
INSERT INTO compare_air_customer_result ( `number`, `name`, `phone`, `old_customer_name`, `customer_name`, `enter_open_sea_time`, `creator`, `create_time`, `type` )
SELECT number,NAME,@tphone,@tcustomer,@customer_service_name,enter_open_sea_time,creator,create_time,'4' FROM ecw_customer WHERE id = @customer_id;
ELSE
INSERT INTO compare_air_customer_result ( `number`, `name`, `phone`, `old_customer_name`, `customer_name`, `creator`, `create_time`, `type` )
SELECT number,NAME,@tphone,@tcustomer,@customer_service_name,creator,create_time,'3' FROM ecw_customer WHERE id = @customer_id;
END IF;
END IF;
END IF;
END IF;
END IF;
FETCH report INTO cname,cphone,ccustomer;
END WHILE;
CLOSE report;
END
drop table if exists `tmp_air_customer`;
CREATE TABLE `tmp_air_customer` (
`name` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'name',
`phone` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'phone',
`customer_name` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'customer_name',
`area_code` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'area_code',
`area_phone` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'area_phone',
`customer_id` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'customer_id'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='同步空运客户表';
drop table if exists `compare_air_customer_result`;
CREATE TABLE `compare_air_customer_result` (
`id` bigint NOT NULL AUTO_INCREMENT,
`number` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'name',
`name` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'name',
`phone` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'phone',
`old_customer_name` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'old_customerName',
`customer_name` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'customerName',
`enter_open_sea_time` datetime default null COMMENT '入公海时间',
`creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '创建者',
`create_time` datetime default null COMMENT '创建时间',
`type` varchar(1) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'type',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='客户比对表结果表';