fw-valhalla.sql
19.4 KB
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
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for accident_customer
-- ----------------------------
DROP TABLE IF EXISTS `accident_pool`;
CREATE TABLE `accident_pool`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(64) DEFAULT NULL COMMENT '客户姓名',
`mobile` varchar(128) NOT NULL COMMENT '联系电话',
`plate_no` varchar(32) NOT NULL COMMENT '车牌号',
`brand_id` bigint DEFAULT NULL COMMENT '品牌id',
`brand_name` varchar(32) DEFAULT NULL COMMENT '品牌名称',
`series_id` bigint DEFAULT NULL COMMENT '车系id',
`series_name` varchar(32) DEFAULT NULL COMMENT '车系名称',
`spec_id` bigint DEFAULT NULL COMMENT '车系id',
`spec_name` varchar(32) DEFAULT NULL COMMENT '车系名称',
`insurer_id` bigint DEFAULT NULL COMMENT '保险公司id',
`insurer_name` varchar(64) DEFAULT NULL comment '保险公司名称',
`report_num` varchar(64) DEFAULT NULL COMMENT '报案号',
`user_id` bigint(20) not null comment '添加人',
`shop_id` bigint DEFAULT NULL COMMENT '服务站id',
`group_id` bigint NOT NULL COMMENT '集团id',
`create_time` datetime NOT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
KEY `mobile_index` (`mobile`),
KEY `plate_no_index` (`plate_no`),
UNIQUE KEY `report_num_group_index` (`report_num`, `group_id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci COMMENT ='事故池 ';
-- ----------------------------
-- Table structure for approve_record
-- ----------------------------
DROP TABLE IF EXISTS `approve_record`;
create table `approve_record`
(
`id` bigint auto_increment,
`data_id` bigint not null comment '业务数据id',
`user_id` bigint not null comment '申请人',
`order_no` varchar(64) not null comment '审批单号',
`reason` varchar(256) DEFAULT NULL COMMENT '申请原因',
`state` int(3) not null comment '1: 等待回调 2:回调完成',
`passed` tinyint(1) not null comment '是否审批通过',
`create_time` datetime null comment '创建时间',
`update_time` datetime null comment '更新时间',
constraint approve_record_pk
primary key (id),
UNIQUE KEY `index_order_no` (`order_no`)
)
comment '审批记录表';
-- ----------------------------
-- Table structure for affiliation_record
-- ----------------------------
DROP TABLE IF EXISTS `affiliation_record`;
CREATE TABLE `affiliation_record`
(
`id` bigint NOT NULL AUTO_INCREMENT,
`customer_id` bigint NOT NULL,
`type` int NOT NULL COMMENT '类型 1：战败 2：划走',
`defeat_time` datetime NOT NULL COMMENT '战败时间',
`reason` varchar(256) DEFAULT NULL COMMENT '战败原因： 系统战败、用户更换等',
`origin_user_id` bigint NOT NULL COMMENT '所属服务顾问',
`origin_shop_id` bigint NOT NULL COMMENT '所属服务站',
`cur_user_id` bigint NULL COMMENT '新顾问id',
`cur_shop_id` bigint NULL COMMENT '新服务站',
`create_time` datetime NOT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
KEY `customer_id` (`customer_id`),
KEY `user_id` (`origin_user_id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci COMMENT ='档案归属变更记录';
-- ----------------------------
-- Table structure for customer
-- ----------------------------
DROP TABLE IF EXISTS `customer`;
CREATE TABLE `customer`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
`base_id` bigint DEFAULT NULL COMMENT '基础信息id',
`tags` varchar(1024) DEFAULT NULL COMMENT '客户标签',
`plate_no` varchar(32) NOT NULL COMMENT '车牌号',
`frame_no` varchar(32) NOT NULL COMMENT '车架号',
`engine_no` varchar(32) DEFAULT NULL COMMENT '发动机编号',
`reg_date` date DEFAULT NULL COMMENT '行驶证注册日期',
`spec_code` varchar(32) DEFAULT NULL COMMENT '车型代码',
`brand_id` bigint DEFAULT NULL COMMENT '品牌id',
`brand_name` varchar(32) DEFAULT NULL COMMENT '品牌名称',
`series_id` bigint DEFAULT NULL COMMENT '车系id',
`series_name` varchar(32) DEFAULT NULL COMMENT '车系名称',
`spec_id` bigint DEFAULT NULL COMMENT '车系id',
`spec_name` varchar(32) DEFAULT NULL COMMENT '车系名称',
`cus_level` int(3) DEFAULT '0' comment '档案星级',
`buy_date` date DEFAULT NULL COMMENT '购车时间',
`buy_price` bigint DEFAULT NULL COMMENT '购车价，单位：分',
`use_type` int DEFAULT NULL COMMENT '使用性质 1:非运营 2:运营',
`source` int DEFAULT NULL COMMENT '车辆来源 10 :销售订单 20:自然到店 30:网上预约 40:数据导入',
`group_id` bigint NOT NULL COMMENT '所属集团',
`car_image` varchar(255) DEFAULT NULL COMMENT '车辆图片',
`vehicle_license` varchar(32) DEFAULT NULL COMMENT '行驶证附件fid',
`current_mileage` int DEFAULT '0' COMMENT '当前行驶里程 单位：KM',
`shop_id` bigint DEFAULT NULL COMMENT '专属服务站id',
`adviser_id` bigint DEFAULT NULL COMMENT '专属服务顾问id',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
`yn` tinyint(1) NOT NULL COMMENT '是否有效',
PRIMARY KEY (`id`),
KEY `normol_index` (`plate_no`, `engine_no`, `base_id`, `spec_code`, `frame_no`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci COMMENT ='保有客档案 ';
-- ----------------------------
-- Table structure for customer_base_info
-- ----------------------------
DROP TABLE IF EXISTS `customer_base_info`;
CREATE TABLE `customer_base_info`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(50) DEFAULT NULL COMMENT '客户名称',
`member_id` bigint NOT NULL COMMENT '会员id',
`contact` varchar(50) DEFAULT NULL COMMENT '联系人姓名',
`mobile` varchar(128) NOT NULL COMMENT '联系电话',
`gender` int DEFAULT 3 COMMENT '客户性别 1:男 2:女 3:未知',
`birthday` date DEFAULT NULL COMMENT '客户出生日期',
`id_code` varchar(32) DEFAULT NULL COMMENT '证件号码 (个人就是身份证号 单位就是组织机构代码)',
`hobby` varchar(255) DEFAULT NULL COMMENT '爱好',
`cus_type` int DEFAULT NULL COMMENT '客户类型 1:个人；2:单位',
`company_name` varchar(32) DEFAULT NULL COMMENT '工作单位 个人',
`address` varchar(500) DEFAULT NULL COMMENT '联系地址',
`lat` decimal(12, 10) DEFAULT NULL COMMENT '纬度',
`lng` decimal(13, 10) DEFAULT NULL COMMENT '经度',
`city_code` varchar(32) DEFAULT NULL COMMENT '所在城市代码',
`city_name` varchar(32) DEFAULT NULL COMMENT '所在城市名称',
`group_id` bigint NOT NULL COMMENT '所属集团',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`yn` tinyint(1) NOT NULL COMMENT '是否有效',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
KEY `id_code_index` (`id_code`),
KEY `member_id_index` (`member_id`),
KEY `mobile` (`mobile`),
KEY `name` (`name`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci COMMENT ='保有客档案基本信息 ';
-- ----------------------------
-- Table structure for customer_contact
-- ----------------------------
DROP TABLE IF EXISTS `customer_contact`;
CREATE TABLE `customer_contact`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
`cus_id` bigint DEFAULT NULL COMMENT '车辆id',
`name` varchar(32) DEFAULT NULL COMMENT '姓名',
`phone` varchar(32) DEFAULT NULL COMMENT '手机号',
`member_id` bigint DEFAULT NULL COMMENT '会员id',
`type` int DEFAULT NULL COMMENT '类型 1：送修人2：同行人',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
`relation` int DEFAULT NULL COMMENT '关系 1:父母 2:子女 3:夫妻 4:亲戚 5:朋友 6:同事 7:本人 8:其他',
PRIMARY KEY (`id`),
KEY `cus_id_index` (`cus_id`),
KEY `phone_index` (`phone`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci COMMENT ='联系人 ';
-- ----------------------------
-- Table structure for follow_notice_record
-- ----------------------------
DROP TABLE IF EXISTS `follow_notice_record`;
CREATE TABLE `follow_notice_record`
(
`id` bigint NOT NULL AUTO_INCREMENT,
`task_id` bigint NOT NULL COMMENT '所属任务',
`follow_type` int(3) not null comment '跟进类型',
`customer_id` bigint NOT NULL COMMENT '客户id',
`status` int DEFAULT NULL COMMENT '结果状态 1: 未发送 2：发送成功 3:发送失败',
`status_desc` varchar(2000) DEFAULT NULL COMMENT '失败时的原因记录',
`send_time` datetime DEFAULT NULL COMMENT '预计发送时间',
`create_time` datetime NOT NULL COMMENT '数据产生时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
KEY `customer_id_index` (`customer_id`),
KEY `task_id_index` (`task_id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci COMMENT ='服务号提醒记录';
-- ----------------------------
-- Table structure for follow_record
-- ----------------------------
DROP TABLE IF EXISTS `follow_record`;
CREATE TABLE `follow_record`
(
`id` bigint NOT NULL AUTO_INCREMENT,
`task_id` bigint NOT NULL COMMENT '所属任务',
`customer_id` bigint NOT NULL COMMENT '客户id',
`type` int NOT NULL COMMENT '设置类型 1：首保 2：例保 3：事故车 4：续保 等',
`user_id` bigint NOT NULL COMMENT '跟进人',
`plan_time` datetime NOT NULL COMMENT '预计跟进时间',
`deadline` datetime NOT NULL COMMENT '本次跟进截止时间',
`follow_time` datetime DEFAULT NULL COMMENT '实际跟进时间',
`out_time` tinyint(1) NOT NULL COMMENT '是否逾期 1：是 2：否',
`remark` varchar(2000) DEFAULT NULL COMMENT '本次跟进描述',
`group_id` bigint NOT NULL,
`shop_id` bigint NOT NULL,
`dealer_id` bigint NOT NULL,
`add_todo` tinyint(1) default '0' null comment '是否添加到待办',
`create_time` datetime NOT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `customer_id_index` (`customer_id`),
KEY `task_id_index` (`task_id`),
KEY `user_id_index` (`user_id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci COMMENT ='跟进记录';
-- ----------------------------
-- Table structure for follow_record_attachments
-- ----------------------------
DROP TABLE IF EXISTS `follow_record_attachments`;
CREATE TABLE `follow_record_attachments`
(
`id` bigint NOT NULL AUTO_INCREMENT,
`record_id` bigint NOT NULL COMMENT '跟进记录id',
`att_type` int NOT NULL COMMENT '附件类型 1：电话 2：微聊 3：拜访 4：微信 5：短信 6：企业微信',
`attachments` varchar(2000) DEFAULT NULL COMMENT '跟进凭证 fid逗号隔开',
`describes` varchar(2000) DEFAULT NULL COMMENT '描述',
`upload_time` datetime not null comment '上传时间',
PRIMARY KEY (`id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci COMMENT ='跟进附件凭证';
-- ----------------------------
-- Table structure for follow_setting
-- ----------------------------
DROP TABLE IF EXISTS `follow_setting`;
CREATE TABLE `follow_setting`
(
`id` bigint NOT NULL AUTO_INCREMENT,
`category` int NOT NULL COMMENT '设置类型 1：首保 2：例保 3：事故车 4：续保',
`group_id` bigint NOT NULL,
`create_time` datetime NOT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `follow_setting_category_group_id_uindex` (`category`, `group_id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci COMMENT ='跟进设置';
-- ----------------------------
-- Table structure for follow_setting_detail
-- ----------------------------
DROP TABLE IF EXISTS `follow_setting_detail`;
CREATE TABLE `follow_setting_detail`
(
`id` bigint NOT NULL AUTO_INCREMENT,
`setting_id` bigint NOT NULL COMMENT '设置id',
`type` int NOT NULL COMMENT '设置类型 1：首次触发时间 2：跟进有效时间 3：跟进周期 4：划走时间 5：失败时间 6：首次触发服务号提醒时间 7：提醒间隔时间 8：提醒次数',
`detail_value` int NOT NULL COMMENT '具体值 0表示 立即生效',
`unit` int NOT NULL COMMENT '单位： 1：次 2：分钟 3：小时 4：天 5：月',
`group_id` bigint NOT NULL,
`yn` tinyint(1) DEFAULT NULL COMMENT '有效状态 1:有效 2：无效',
`create_time` datetime NOT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci COMMENT ='跟进设置';
-- ----------------------------
-- Table structure for follow_task
-- ----------------------------
DROP TABLE IF EXISTS `follow_task`;
CREATE TABLE `follow_task`
(
`id` bigint NOT NULL AUTO_INCREMENT,
`customer_id` bigint NOT NULL COMMENT '客户id 事故车的情况下是事故池的id ',
`type` int NOT NULL COMMENT '设置类型 1：首保 2：例保 3：事故车 4：续保 等',
`origin_time` datetime NOT NULL comment '原始数据产生时间',
`state` int NOT NULL COMMENT '任务状态 1：未开始 2：进行中 3：已结束',
`begin_time` datetime NOT NULL COMMENT '任务开始时间',
`change_user_time` datetime NOT NULL COMMENT '更换人员跟进日期',
`changed` tinyint(1) default 0 not null comment '是否完成人员更换',
`deadline` datetime NOT NULL COMMENT '截止跟进日期',
`finished` tinyint(1) NOT NULL COMMENT '是否完成 1：是 2：否',
`finish_time` datetime DEFAULT NULL COMMENT '完成时间',
`origin_user` bigint not NULL comment '初始跟进人',
`origin_shop` bigint not null comment '初始门店',
`follow_user` bigint not null comment '当前跟进人',
`follow_shop` bigint not null comment '当前跟服务站',
`finish_user` bigint NULL comment '完成人员',
`finish_shop` bigint null comment '完成门店',
`group_id` bigint NOT NULL,
`dealer_id` bigint NOT NULL,
`create_time` datetime NOT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `customer_id_index` (`customer_id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci COMMENT ='跟进任务记录';
-- ----------------------------
-- Table structure for original_data
-- ----------------------------
DROP TABLE IF EXISTS `original_data`;
CREATE TABLE `original_data`
(
`id` bigint NOT NULL AUTO_INCREMENT,
`type` tinyint NOT NULL COMMENT '类型 1：订单交车 2：保养交车 3：事故车进站 4：购买保险',
`customer_id` bigint NOT NULL COMMENT '客户id ',
`plate_no` varchar(12) NULL COMMENT '车牌号',
`detail_id` varchar(64) not null comment '业务id',
`generate_time` datetime NOT NULL COMMENT '数据产生时间',
`solved` tinyint(1) NOT NULL COMMENT '是否已处理',
`group_id` bigint NOT NULL,
`create_time` datetime NOT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `customer_id_index` (`customer_id`),
KEY `plate_no_index` (`plate_no`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci COMMENT ='原始数记录';
create unique index type_detail_id_uindex
on original_data (type, detail_id);
SET FOREIGN_KEY_CHECKS = 1;
-- auto-generated definition
DROP TABLE IF EXISTS `wechat_subscribe`;
create table wechat_subscribe
(
id int auto_increment
primary key,
member_id bigint not null comment '会员系统ID',
subscribed tinyint(1) not null comment '是否关注公众号',
subscribe_time datetime null,
un_subscribe_time datetime null,
KEY `member_id_index` (`member_id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci COMMENT ='微信公众号关注情况';
-- auto-generated definition
DROP TABLE IF EXISTS `public_pool`;
create table public_pool
(
id bigint auto_increment,
customer_id bigint not null comment '档案id',
customer_name varchar(10) null comment '客户名称',
plate_no varchar(10) not null comment '车牌号',
address varchar(255) null comment '住址',
company varchar(128) null comment '工作单位',
type int(3) not null comment '类型 1：首保 2：流失 3：主动放弃',
shop_id bigint not null comment '服务站',
shop_name varchar(128) not null comment '服务站名',
times int default 0 not null comment '进站次数',
reason varchar(255) null comment '原因',
group_id bigint not null comment '集团id',
create_time datetime null,
update_time datetime null,
constraint public_pool_pk
primary key (id)
)
comment '公共池';
create index customer_id_customer_name_plate_no_index
on public_pool (customer_id, customer_name, plate_no);
create unique index plate_no_group_id_uindex
on public_pool (plate_no, group_id);
-- auto-generated definition
DROP TABLE IF EXISTS `company_wechat_join`;
create table company_wechat_join
(
id bigint auto_increment
primary key,
dealer_id bigint not null comment '商家ID',
member_id int(20) not null comment '会员系统ID',
joined tinyint(1) default 0 not null comment '是否加入',
join_time datetime null,
remove_time datetime null,
KEY `member_id_index` (`member_id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci COMMENT ='企业微信加入情况';