如果表存在先删除再创建.sql
30.6 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
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for mc_basic_cmty
-- ----------------------------
DROP TABLE IF EXISTS `mc_basic_cmty`;
CREATE TABLE `mc_basic_cmty` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键id',
`fiscal_year` int NOT NULL COMMENT '财年',
`region_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '区域名称',
`group_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '集团名称',
`company_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '公司名称',
`company_code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '公司代码',
`province` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '省',
`city` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '市',
`district` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '区',
`street` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '街道',
`vlg_or_cmty` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '村(小区)',
`resp_person` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '负责人',
`urb_rur_cls` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '城乡分类',
`project_type_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '项目类型名称',
`comm_hh` int NULL DEFAULT NULL COMMENT '村/小区总户数',
`acct_fy_start_conf` int NULL DEFAULT NULL COMMENT '应收账款目标及完成-财年初财务确认应收账款户数',
`acct_prev_recov` int NULL DEFAULT NULL COMMENT '应收账款目标及完成-往年应收在今年已回款户数',
`acct_prev_charged` int NULL DEFAULT NULL COMMENT '应收账款目标及完成-往年已收费户数',
`curr_fy_new_recovered` int NULL DEFAULT NULL COMMENT '本财年新增已回款户',
`total_charged` int NULL DEFAULT NULL COMMENT '累计已收费总户数',
`qtr_target_company` int NULL DEFAULT NULL COMMENT '季度目标(到公司)',
`hh_gsg_charged` int NULL DEFAULT NULL COMMENT '纳入政府补贴户数-已收费户数',
`hh_gsg_uncharged` int NULL DEFAULT NULL COMMENT '纳入政府补贴户数-未收费户数',
`hh_gsg_total` int NULL DEFAULT NULL COMMENT '纳入政府补贴户数-合计',
`hh_new_full` int NULL DEFAULT NULL COMMENT '市场化收费户数-新增户数(财务字段)全款户数',
`hh_new_install` int NULL DEFAULT NULL COMMENT '市场化收费户数-新增户数(财务字段)分期/定金户数',
`hh_prev_full` int NULL DEFAULT NULL COMMENT '市场化收费户数-往年应收户数(财务字段)全款户数',
`hh_prev_inst_dp` int NULL DEFAULT NULL COMMENT '市场化收费户数-往年应收户数(财务字段)分期/定金户数',
`hh_unchg` int NULL DEFAULT NULL COMMENT '市场化收费户数-未收费户数',
`hh_unchg_conf` int NULL DEFAULT NULL COMMENT '暂不可收费户数-其中：提前确认',
`hh_unchg_scan` int NULL DEFAULT NULL COMMENT '暂不可收费户数-其中：纳入扫雷',
`hh_ev_cum_paid` int NULL DEFAULT NULL COMMENT '评价指标-市场化累计回款户数',
`hh_ev_inst` int NULL DEFAULT NULL COMMENT '评价指标-其中：分期/定金户数',
`hh_ev_full` int NULL DEFAULT NULL COMMENT '评价指标-其中：全款户数',
`hh_ev_ignite` int NULL DEFAULT NULL COMMENT '评价指标-点火户数',
`hh_ev_cum_setup` int NULL DEFAULT NULL COMMENT '评价指标-累计报装率',
`hh_ev_cum_gas` int NULL DEFAULT NULL COMMENT '评价指标-累计气化率',
`remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注',
`create_time` datetime(0) NOT NULL COMMENT '创建时间',
`create_by` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '创建人',
`update_time` datetime(0) NOT NULL COMMENT '更新时间',
`update_by` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '更新人',
`def_flag` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '0' COMMENT '删除标志（0：未删除，1：已删除）',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 91 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '基础数据-户数' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for mc_basic_money
-- ----------------------------
DROP TABLE IF EXISTS `mc_basic_money`;
CREATE TABLE `mc_basic_money` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键id',
`fiscal_year` int NOT NULL COMMENT '财年',
`region_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '区域名称',
`group_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '集团名称',
`company_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '公司名称',
`company_code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '公司代码',
`province` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '省',
`city` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '市',
`district` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '区',
`street` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '街道',
`vlg_or_cmty` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '村(小区)',
`resp_person` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '负责人',
`urb_rur_cls` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '城乡分类',
`budg_conf_prev` decimal(10, 2) NULL DEFAULT NULL COMMENT '财务确认预算数-财年初财务确认往年应收',
`budg_conf_new` decimal(10, 2) NULL DEFAULT NULL COMMENT '财务确认预算数-本年新增',
`budg_total` decimal(10, 2) NULL DEFAULT NULL COMMENT '财务确认预算数-总预算金额',
`quarterly_target` decimal(10, 2) NULL DEFAULT NULL COMMENT '季度目标(到公司四个季度)已收费户数',
`cny_gsg_charged` decimal(10, 2) NULL DEFAULT NULL COMMENT '纳入政府补贴金额-已收费户数',
`cny_gsg_uncharged` decimal(10, 2) NULL DEFAULT NULL COMMENT '纳入政府补贴金额-未收费户数',
`cny_gsg_total` decimal(10, 2) NULL DEFAULT NULL COMMENT '纳入政府补贴金额-合计',
`cny_yr_full` decimal(10, 2) NULL DEFAULT NULL COMMENT '市场化收费金额 - 本年新增金额 - 全款户数',
`cny_yr_instal` decimal(10, 2) NULL DEFAULT NULL COMMENT '市场化收费金额 - 本年新增金额 - 分期户数',
`cny_prev_full` decimal(10, 2) NULL DEFAULT NULL COMMENT '往年应收金额-已收费户数-全款户数',
`cny_prev_install` decimal(10, 2) NULL DEFAULT NULL COMMENT '往年应收金额-已收费户数-分期/定金户数',
`cny_market_cumr` decimal(10, 2) NULL DEFAULT NULL COMMENT '评价指标-市场化累计回款',
`cny_fy_cum_return` decimal(10, 2) NULL DEFAULT NULL COMMENT '评价指标-财年累计回款',
`cny_fp_cum_return` decimal(10, 2) NULL DEFAULT NULL COMMENT '评价指标-全款累计回款',
`cny_inst_cum_return` decimal(10, 2) NULL DEFAULT NULL COMMENT '评价指标-分期累计回款',
`cny_new_cum_return` decimal(10, 2) NULL DEFAULT NULL COMMENT '评价指标-本年新增累计回款',
`cny_rcvbls_cum_return` decimal(10, 2) NULL DEFAULT NULL COMMENT '评价指标-往年应收累计回款',
`cny_bdgt_cmpl_rate` decimal(10, 2) NULL DEFAULT NULL COMMENT '评价指标-预算完成率',
`cny_rcvbls_cmpl_rate` decimal(10, 2) NULL DEFAULT NULL COMMENT '评价指标-应收账款完成率',
`cny_cyn_cmpl_rate` decimal(10, 2) NULL DEFAULT NULL COMMENT '评价指标-本年新增完成率',
`cny_pt_cmpl_rate` decimal(10, 2) NULL DEFAULT NULL COMMENT '评价指标-阶段性目标完成率',
`remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注',
`create_time` datetime(0) NOT NULL COMMENT '创建时间',
`create_by` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '创建人',
`update_time` datetime(0) NOT NULL COMMENT '更新时间',
`update_by` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '更新人',
`def_flag` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '0' COMMENT '删除标志（0：未删除，1：已删除）',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 25 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '基础数据-金额' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for mc_construction
-- ----------------------------
DROP TABLE IF EXISTS `mc_construction`;
CREATE TABLE `mc_construction` (
`id` bigint NOT NULL COMMENT '主键id',
`cmty_id` bigint NOT NULL COMMENT '基础数据-户数-id',
`region_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '区域名称',
`group_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '集团名称',
`company_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '公司名称',
`company_code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '公司代码',
`province` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '省',
`city` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '市',
`district` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '区',
`street` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '街道',
`vlg_or_cmty` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '村(小区)',
`urb_rur_cls` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '城乡分类',
`project_type_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '项目类型名称',
`project_type_code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '项目类型代码',
`assignment_time` datetime(0) NULL DEFAULT NULL COMMENT '市场派单时间',
`is_completed` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '是否竣工',
`completion_time` datetime(0) NULL DEFAULT NULL COMMENT '竣工时间',
`non_ncmp_rsn` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '未竣工原因',
`project_person_id` bigint NULL DEFAULT NULL COMMENT '工程负责人ID',
`project_person_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '工程负责人名称',
`project_person_account` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '工程负责人账号',
`gas_conn_mtr_ris` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '是否通气到表前/立管',
`gct_mtrris_time` datetime(0) NULL DEFAULT NULL COMMENT '通气到表前/立管时间',
`gct_ncmp_rsn` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '未完成原因',
`operate_person_id` bigint NULL DEFAULT NULL COMMENT '运营负责人ID',
`operate_person_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '运营负责人名称',
`operate_person_account` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '运营负责人账号',
`remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注',
`create_time` datetime(0) NOT NULL COMMENT '创建时间',
`create_by` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '创建人',
`update_time` datetime(0) NOT NULL COMMENT '更新时间',
`update_by` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '更新人',
`def_flag` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '删除标志（0：未删除，1：已删除）',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '施工维护' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for mc_file
-- ----------------------------
DROP TABLE IF EXISTS `mc_file`;
CREATE TABLE `mc_file` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增id',
`uuid` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '唯一标识',
`file_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '文件名称',
`extension` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '文件拓展名，如.txt',
`path` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '文件路径',
`url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '下载链接',
`create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
`create_by` bigint NULL DEFAULT NULL COMMENT '创建人',
`update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
`update_by` bigint NULL DEFAULT NULL COMMENT '更新人',
`def_flag` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '0' COMMENT '删除标志（0：未删除，1：已删除）',
`cited` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '0' COMMENT '是否被引用过（0：未被引用过，1：已被引用过）',
`count` int NULL DEFAULT 0 COMMENT '被引用次数',
`file_size` int NULL DEFAULT NULL COMMENT '文件大小(kb)',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 279 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '市场化收费相关文件' ROW_FORMAT = DYNAMIC;
-- ----------------------------
-- Table structure for mc_ignition
-- ----------------------------
DROP TABLE IF EXISTS `mc_ignition`;
CREATE TABLE `mc_ignition` (
`id` bigint NOT NULL COMMENT '主键id',
`visit_id` bigint NOT NULL COMMENT '拜访id',
`region_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '区域名称',
`group_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '集团名称',
`company_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '公司名称',
`company_code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '公司代码',
`user_province` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '客户-省',
`user_city` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '客户-市',
`user_district` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '客户-区',
`user_street` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '客户-街道',
`vlg_or_cmty` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '村(小区)',
`urb_rur_cls` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '城乡分类',
`user_detailed_address` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '客户-详细地址',
`user_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '客户-用户编号',
`user_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '客户-户名',
`user_phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '客户-电话',
`ign_cnf_status_code` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '点火确认状态代码(0：未确认，1：是，2：否)',
`ign_time` datetime(0) NULL DEFAULT NULL COMMENT '点火时间',
`n_ign_rsn` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '未点火原因',
`cnf_person_id` bigint NULL DEFAULT NULL COMMENT '负责人ID',
`cnf_person_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '负责人名称',
`cnf_person_account` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '负责人账号',
`remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注',
`create_time` datetime(0) NOT NULL COMMENT '创建时间',
`create_by` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '创建人',
`update_time` datetime(0) NOT NULL COMMENT '更新时间',
`update_by` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '更新人',
`def_flag` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '删除标志（0：未删除，1：已删除）',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '点火维护' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for mc_operation_log
-- ----------------------------
DROP TABLE IF EXISTS `mc_operation_log`;
CREATE TABLE `mc_operation_log` (
`id` bigint NOT NULL COMMENT '主键id',
`data_form` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '数据表(拜访 / 点火维护 /施工维护)',
`data_id` bigint NOT NULL COMMENT '数据id / 被修改的数据唯一id',
`proc_role` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '处理角色 / 处理节点角色',
`proc_user` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '处理人',
`proc_time` datetime(0) NOT NULL COMMENT '处理时间',
`proc_account` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '处理人账号',
`op_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '操作名称 (同意 / 拒绝 / 发起 /异动)',
`op_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '操作类型 (1：填报 2：异动 3：修改)',
`op_remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '操作备注(拒绝说明 异动说明)',
`before_data_json` json NULL COMMENT '操作前数据json',
`after_data_json` json NULL COMMENT '操作后数据json',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '市场化收费相关记录' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for mc_visit
-- ----------------------------
DROP TABLE IF EXISTS `mc_visit`;
CREATE TABLE `mc_visit` (
`id` bigint NOT NULL COMMENT '主键id',
`ticket_number` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '工单单号',
`fiscal_year` int NOT NULL COMMENT '财年',
`region_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '区域名称',
`group_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '集团名称',
`company_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '公司名称',
`company_code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '公司代码',
`billing_person_id` bigint NOT NULL COMMENT '收费人ID',
`billing_person_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '收费人名称',
`billing_person_account` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '收费人账号',
`job_nature` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '工作性质(兼职/全职)',
`entry_time` date NOT NULL COMMENT '入户时间',
`vlg_or_cmty` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '村(小区)',
`urb_rur_cls` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '城乡分类',
`user_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '客户-户名',
`user_phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '客户-电话',
`user_province` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '客户-省',
`user_city` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '客户-市',
`user_district` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '客户-区',
`user_street` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '客户-街道',
`user_detailed_address` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '客户-详细地址',
`has_dm` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '是否获取决策人信息',
`dm_age` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '决策人年龄',
`dm_income_src` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '决策人收入来源',
`dm_phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '决策人联系方式',
`is_charged` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '是否收费',
`user_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '客户-用户编号',
`user_id_card` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '身份证号',
`invoice_no` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '票据单号',
`charge_amt` decimal(10, 2) NULL DEFAULT NULL COMMENT '收费金额(元)',
`payment_meth` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '缴费方式',
`charge_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '收费类型',
`is_visited` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '是否拜访',
`is_intent` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '是否有意向',
`house_condition` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '房屋情况',
`current_cooking_energy` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '现用做饭能源',
`current_heating_energy` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '现用取暖能源',
`family_members` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '家庭人口情况',
`fridge_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '电冰箱',
`ac_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '空调',
`tv_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '电视',
`washer_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '洗衣机',
`water_heater_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '热水器',
`pv_system_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '光伏',
`visit_province` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '拜访_省',
`visit_city` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '拜访_市',
`visit_district` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '拜访_区',
`visit_street` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '拜访_街道',
`visit_detailed_address` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '拜访_详细地址',
`visit_longitude` decimal(11, 8) NULL DEFAULT NULL COMMENT '拜访_经度',
`visit_latitude` decimal(11, 8) NULL DEFAULT NULL COMMENT '拜访_纬度',
`field_photo_file` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '拜访现场照片(文件id，逗号分隔)',
`attachment_file` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '附件(文件id，逗号分隔)',
`status_code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '状态代码',
`status_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '状态名称',
`remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注',
`create_time` datetime(0) NOT NULL COMMENT '创建时间',
`create_by` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '创建人',
`update_time` datetime(0) NOT NULL COMMENT '更新时间',
`update_by` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '更新人',
`def_flag` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '删除标志（0：未删除，1：已删除）',
`ticket_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '工单类型',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '拜访工单' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for mc_visit_user
-- ----------------------------
DROP TABLE IF EXISTS `mc_visit_user`;
CREATE TABLE `mc_visit_user` (
`id` bigint NOT NULL COMMENT '主键id',
`user_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户编号',
`user_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '客户-户名',
`user_phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '客户-电话',
`residence_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '居住属性',
`family_members` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '家庭人口情况',
`income_source` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '家庭收入来源',
`house_condition` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '房屋情况',
`original_appliances` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '家电配置(原)-冗余字段',
`decoration_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '装修情况',
`current_cooking_energy` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '现用做饭能源（英文逗号分开）',
`current_heating_energy` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '现用取暖能源',
`is_intent` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '用户报装意愿',
`region_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '区域名称',
`region_code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '区域代码',
`budget_owner` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '预算主体',
`company_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '项目公司名称',
`company_code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '项目公司代码',
`province` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '省',
`city` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '市',
`district` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '区',
`street` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '街道',
`detailed_address` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '详细地址',
`fridge_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '电冰箱',
`ac_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '空调',
`tv_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '电视',
`washer_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '洗衣机',
`water_heater_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '热水器',
`pv_system_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '光伏',
`township` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '街道办/乡镇',
`village` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '居委会/行政村',
`vlg_or_cmty` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '小区/自然村',
`user_age` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '用户年龄',
`create_time` datetime(0) NOT NULL COMMENT '创建时间',
`create_by` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '创建人',
`def_flag` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '删除标志（0：未删除，1：已删除）',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '百万用户信息（被拜访的用户）' ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;