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
-- 添加字典
INSERT INTO `system_dict_type` (`name`, `type`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES ('客户角色', 'customer_role', 0, NULL, '1', now(), '1', now(), b'0');
INSERT INTO `system_dict_data` (`sort`, `value`, `label`, `label_en`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (0, '0', 'C0', 'CO', 'customer_role', 0, 'default', '', NULL, '1', now(), '115', now(), b'0');
INSERT INTO `system_dict_data` (`sort`, `value`, `label`, `label_en`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, '1', '工程商', 'Project contractor', 'customer_role', 0, 'default', '', NULL, '1', now(), '115', now(), b'0');
INSERT INTO `system_dict_data` (`sort`, `value`, `label`, `label_en`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (2, '2', '代理', 'Agent', 'customer_role', 0, 'default', '', NULL, '1', now(), '115', now(), b'0');
INSERT INTO `system_dict_data` (`sort`, `value`, `label`, `label_en`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (3, '3', '同行', 'Consolidated shipping', 'customer_role', 0, 'default', '', NULL, '1', now(), '115', now(), b'0');
INSERT INTO `system_dict_data` (`sort`, `value`, `label`, `label_en`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (6, '6', '集运', 'Competitors', 'customer_role', 0, 'default', '', NULL, '1', now(), '115', now(), b'0');
INSERT INTO `system_dict_type` (`name`, `type`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES ('客户创建入口', 'customer_from', 0, NULL, '1', now(), '1', now(), b'0');
INSERT INTO `system_dict_data` (`sort`, `value`, `label`, `label_en`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, '1', '导入', 'Import', 'customer_from', 0, 'default', '', NULL, '1', now(), '115', now(), b'0');
INSERT INTO `system_dict_data` (`sort`, `value`, `label`, `label_en`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (2, '2', '后台用户创建', 'Backend', 'customer_from', 0, 'default', '', NULL, '1', now(), '115', now(), b'0');
INSERT INTO `system_dict_data` (`sort`, `value`, `label`, `label_en`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (3, '3', 'APP注册', 'APP', 'customer_from', 0, 'default', '', NULL, '1', now(), '115', now(), b'0');
INSERT INTO `system_dict_data` (`sort`, `value`, `label`, `label_en`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (4, '4', '发货人创建', 'Shipper', 'customer_from', 0, 'default', '', NULL, '1', now(), '115', now(), b'0');
INSERT INTO `system_dict_type` (`name`, `type`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES ('客户获取方式', 'customer_get_method', 0, NULL, '1', now(), '1', now(), b'0');
INSERT INTO `system_dict_data` (`sort`, `value`, `label`, `label_en`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, '1', '自主创建', 'Independent creation', 'customer_get_method', 0, 'default', '', NULL, '1', now(), '115', now(), b'0');
INSERT INTO `system_dict_data` (`sort`, `value`, `label`, `label_en`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (2, '2', '分配/移交接收', 'Allot/Transfer', 'customer_get_method', 0, 'default', '', NULL, '1', now(), '115', now(), b'0');
INSERT INTO `system_dict_data` (`sort`, `value`, `label`, `label_en`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (3, '3', '公海捞取', 'Open Sea pick up', 'customer_get_method', 0, 'default', '', NULL, '1', now(), '115', now(), b'0');
INSERT INTO `system_dict_type` (`name`, `type`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES ('客户跟进状态', 'customer_followup_status', 0, NULL, '1', now(), '1', now(), b'0');
INSERT INTO `system_dict_data` (`sort`, `value`, `label`, `label_en`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (0, '0', '未提交', 'UnCommited', 'customer_followup_status', 0, 'default', '', NULL, '1', now(), '115', now(), b'0');
INSERT INTO `system_dict_data` (`sort`, `value`, `label`, `label_en`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, '1', '已提交', 'Commited', 'customer_followup_status', 0, 'default', '', NULL, '1', now(), '115', now(), b'0');
INSERT INTO `system_dict_type` (`name`, `type`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES ('客户跟进类型', 'customer_followup_type', 0, NULL, '1', now(), '1', now(), b'0');
INSERT INTO `system_dict_data` (`sort`, `value`, `label`, `label_en`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (4, '1', '维护拜访', 'Maintenance visits', 'customer_followup_type', 0, 'default', '', NULL, '1', now(), '115', now(), b'0');
INSERT INTO `system_dict_data` (`sort`, `value`, `label`, `label_en`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, '2', '商机跟进', 'Business opportunities', 'customer_followup_type', 0, 'default', '', NULL, '1', now(), '115', now(), b'0');
INSERT INTO `system_dict_data` (`sort`, `value`, `label`, `label_en`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (2, '3', '新业务开发', 'New business development', 'customer_followup_type', 0, 'default', '', NULL, '1', now(), '115', now(), b'0');
INSERT INTO `system_dict_data` (`sort`, `value`, `label`, `label_en`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (3, '4', '复购跟进', 'Follow up on repeat purchases', 'customer_followup_type', 0, 'default', '', NULL, '1', now(), '115', now(), b'0');
INSERT INTO `system_dict_type` (`name`, `type`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES ('跟进方式', 'customer_followup_method', 0, NULL, '1', now(), '1', now(), b'0');
INSERT INTO `system_dict_data` (`sort`, `value`, `label`, `label_en`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, '1', '电话', 'Phone', 'customer_followup_method', 0, 'default', '', NULL, '1', now(), '115', now(), b'0');
INSERT INTO `system_dict_data` (`sort`, `value`, `label`, `label_en`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (2, '2', '面谈', 'Face-to-face', 'customer_followup_method', 0, 'default', '', NULL, '1', now(), '115', now(), b'0');
INSERT INTO `system_dict_data` (`sort`, `value`, `label`, `label_en`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (3, '3', '网络聊天', 'Online Chat', 'customer_followup_method', 0, 'default', '', NULL, '1', now(), '115', now(), b'0');
INSERT INTO `system_dict_type` (`name`, `type`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES ('跟进结果', 'customer_followup_result_type', 0, NULL, '1', now(), '1', now(), b'0');
INSERT INTO `system_dict_data` (`sort`, `value`, `label`, `label_en`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, '1', '等待消息', 'Waiting for message', 'customer_followup_result_type', 0, 'default', '', NULL, '1', now(), '115', now(), b'0');
-- ----------------------------
-- Table structure for ecw_customer_followup
-- ----------------------------
DROP TABLE IF EXISTS `ecw_customer_followup`;
CREATE TABLE `ecw_customer_followup` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
`number` varchar(64) NULL DEFAULT NULL COMMENT '编号',
`parent_id` bigint NULL DEFAULT NULL COMMENT '上一级跟进单ID',
`parent_number` varchar(64) NULL DEFAULT NULL COMMENT '上一级跟进单号',
`status` int NULL DEFAULT NULL COMMENT '状态 字典customer_followup_status',
`customer_id` bigint NULL DEFAULT NULL COMMENT '客户编号',
`offer_id` bigint NULL DEFAULT NULL COMMENT '报价单',
`follow_type` tinyint NULL DEFAULT NULL COMMENT '跟进类型 字典customer_followup_type',
`follow_time` datetime NULL DEFAULT NULL COMMENT '跟进时间',
`contact_id` bigint NULL DEFAULT NULL COMMENT '联系人id',
`contact_name` varchar(63) NULL DEFAULT NULL COMMENT '联系人',
`follow_method` tinyint NULL DEFAULT NULL COMMENT '跟进方式 字典customer_followup_method',
`follow_user_id` bigint NULL DEFAULT NULL COMMENT '客户经理/跟进业务员id',
`purpose` varchar(512) NULL DEFAULT NULL COMMENT '目的',
`result_type` tinyint NULL DEFAULT NULL COMMENT '跟进结果 字典customer_followup_result_type',
`feedback` varchar(2048) NULL DEFAULT NULL COMMENT '客户反馈',
`attatchment` varchar(2048) NULL DEFAULT NULL COMMENT '附件 多个以逗号分隔',
`next_time` datetime NULL DEFAULT NULL COMMENT '下次跟进时间',
`next_plan` varchar(2048) NULL DEFAULT NULL COMMENT '下次跟进计划',
`creator` varchar(63) NOT NULL DEFAULT '' COMMENT '创建者',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updater` varchar(63) NULL 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 AUTO_INCREMENT = 1 COMMENT = '客户跟进表';
-- 下面为刷新数据的sql
-- 更新客户角色
update ecw_customer set roles = '2' where FIND_IN_SET(2, type) != 0;
update ecw_customer set roles = '3' where FIND_IN_SET(3, type) != 0;
update ecw_customer set roles = '2,3' where FIND_IN_SET(2, type) != 0 and FIND_IN_SET(3, type) != 0;
-- 根据客户日志中的创建/接收/捞取的最后行为时间 来更新归属时间
update ecw_customer a
inner join (
select customer_id, max(create_time) as maxtime from ecw_customer_operate_log where (operate_type = 5 or operate_type = 1 or operate_type = 7) group by customer_id
) b on a.id = b.customer_id
set a.customer_service_confirmed_time = b.maxtime
where a.deleted = 0;
-- 刷新客户跟进数据
insert into ecw_customer_followup(id, number, parent_number, `status`, customer_id, offer_id, follow_type, follow_time, contact_name,
follow_method, follow_user_id, purpose, result_type, feedback, attatchment,
next_time, next_plan, creator, create_time, updater, update_time, deleted)
select null, null, null, 1, customer_id, null, 1, follow_time, contact_name, follow_method, follow_user_id, null, 1,
CONCAT('【客户反馈：】\r\n', feedback, '\r\n\r\n【处理结果：】\r\n', result),
null, null, null, creator, create_time, updater, update_time, deleted
from ecw_customer_follow;
-- 更新客户跟进编号
update ecw_customer_followup set number = CONCAT('GJ', 2024000000 + id);