2023_update.sql
41.7 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
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
-- 2023年3月22日
ALTER TABLE `fw_morax`.`kpi_pool`
ADD COLUMN `assess_rank` int(11) NULL COMMENT '人员质量评测排名' AFTER `rank`;
CREATE TABLE `kpi_assess` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`type` tinyint(4) NOT NULL COMMENT '人员质量考核类型; 1:D级员工 2:转正后保护期员工',
`post_id` bigint(20) NOT NULL COMMENT '岗位id',
`post_name` varchar(255) NOT NULL COMMENT '岗位名称',
`shop_ids` varchar(255) NOT NULL COMMENT '适用门店ids',
`shop_names` varchar(1024) NOT NULL COMMENT '适用门店名称',
`rank` decimal(10,4) DEFAULT NULL COMMENT '排名',
`backup` tinyint(1) NOT NULL COMMENT '备份',
`group_id` bigint(20) NOT NULL COMMENT '集团id',
`yn` tinyint(1) DEFAULT '1' COMMENT '逻辑删除',
`create_by` bigint(20) DEFAULT NULL COMMENT '创建人',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_by` bigint(20) DEFAULT NULL COMMENT '更新人',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='人员质量考核配置';
CREATE TABLE `kpi_assess_detail` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`kpi_assess_id` bigint(20) NOT NULL COMMENT '人员质量考核id',
`role_codes` varchar(512) NOT NULL COMMENT '考核角色编码',
`role_names` varchar(1024) NOT NULL COMMENT '考核角色名称',
`penalty_score` decimal(10,4) DEFAULT NULL COMMENT '惩罚分',
`cap_person_penalty_score` decimal(10,4) DEFAULT NULL COMMENT '封顶单人惩罚分',
`cap_penalty_score` decimal(10,4) DEFAULT NULL COMMENT '封顶惩罚分',
`penalty_money` decimal(10,4) DEFAULT NULL COMMENT '惩罚奖励',
`cap_person_penalty_money` decimal(10,4) DEFAULT NULL COMMENT '封顶单人惩罚奖励',
`cap_penalty_money` decimal(10,4) DEFAULT NULL COMMENT '封顶惩罚奖励',
`award_score` decimal(10,4) DEFAULT NULL COMMENT '奖励分数',
`cap_person_award_score` decimal(10,4) DEFAULT NULL COMMENT '封顶单人奖励分数',
`cap_award_score` decimal(10,4) DEFAULT NULL COMMENT '封顶奖励分数',
`award_money` decimal(10,4) DEFAULT NULL COMMENT '奖励金额',
`cap_person_award_money` decimal(10,4) DEFAULT NULL COMMENT '封顶单人奖励金额',
`cap_award_money` decimal(10,4) DEFAULT NULL COMMENT '封顶奖励金额',
`yn` tinyint(1) DEFAULT '1' COMMENT '逻辑删除',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='人员质量考核配置';
CREATE TABLE `kpi_assess_penalty` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`kpi_assess_id` bigint(20) NOT NULL COMMENT '评测id',
`kpi_assess_detail_id` bigint(20) NOT NULL COMMENT '评测详情id',
`type` tinyint(4) DEFAULT NULL COMMENT '人员质量考核类型; 1:D级员工 2:转正后保护期员工',
`role_code` varchar(255) DEFAULT NULL COMMENT '角色编码',
`user_name` varchar(255) NOT NULL COMMENT '用户名',
`user_id` bigint(20) DEFAULT NULL COMMENT '用户id',
`rank` int(10) DEFAULT NULL COMMENT '目标排名',
`score` decimal(10,4) DEFAULT NULL COMMENT '奖惩绩效分',
`award` decimal(10,4) DEFAULT NULL COMMENT '奖惩金额',
`data_date` date DEFAULT NULL COMMENT '日期',
`expected_penalty` tinyint(1) DEFAULT NULL COMMENT '预期处罚',
`group_id` bigint(20) NOT NULL COMMENT '集团id',
`yn` tinyint(1) DEFAULT '1' COMMENT '逻辑删除',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='人员质量考核奖惩';
CREATE TABLE `kpi_assess_penalty_staff` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`kpi_assess_penalty_id` bigint(20) NOT NULL COMMENT '人员质量考核奖惩id',
`kpi_pool_id` bigint(20) NOT NULL COMMENT '绩效池id',
`user_name` varchar(255) NOT NULL COMMENT '用户名',
`user_id` bigint(20) NOT NULL COMMENT '用户id',
`shop_id` bigint(20) NOT NULL COMMENT '门店id',
`shop_name` varchar(255) NOT NULL COMMENT '门店名称',
`post_id` bigint(20) NOT NULL COMMENT '岗位id',
`post_name` varchar(255) NOT NULL COMMENT '岗位名称',
`rank` int(10) DEFAULT NULL COMMENT '排名',
`star_level` tinyint(4) DEFAULT NULL COMMENT '星级;1: A 2:B 3:C 4:D',
`penalty` tinyint(1) NOT NULL COMMENT '是否是惩罚',
`score` decimal(10,4) DEFAULT NULL COMMENT '奖惩绩效分',
`award` decimal(10,4) DEFAULT NULL COMMENT '奖惩金额',
`yn` tinyint(1) DEFAULT '1' COMMENT '逻辑删除',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='人员质量考核奖惩人员';
-- 2023年3月7日
ALTER TABLE `fw_morax`.`eval_group_reward_ladders`
ADD COLUMN `rank_order_type` tinyint(4) NULL DEFAULT 1 COMMENT '排名顺序类型 1:正 2:负' AFTER `upper`;
CREATE TABLE `eval_group_reward_rank_log` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(512) NOT NULL COMMENT '名称',
`pool_id` bigint(20) DEFAULT NULL COMMENT '人员id 、门店id',
`scope_type` tinyint(4) NOT NULL COMMENT '考评范围; 1:门店考评 2:人员考评',
`refer_id` bigint(20) DEFAULT NULL COMMENT '考评奖惩 前置条件 配置id',
`target_type` tinyint(4) DEFAULT NULL COMMENT '1:参数 2:前置条件',
`value` decimal(18,4) DEFAULT NULL COMMENT '原始值',
`reach_value` decimal(18,4) DEFAULT NULL COMMENT '达成目标',
`rank` int(10) DEFAULT NULL COMMENT '排名',
`hit` tinyint(1) DEFAULT NULL COMMENT '是否命中',
`data_date` date NOT NULL COMMENT '数据日期',
`group_id` bigint(20) NOT NULL COMMENT '集团',
`create_time` datetime DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
`yn` tinyint(1) DEFAULT '1',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='考评指标达成目标记录';
-- 报表
CREATE TABLE `morax_004_m` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`report_date` date DEFAULT NULL COMMENT '日期',
`report_dimension` varchar(2) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '维度',
`group_id` bigint(20) DEFAULT NULL COMMENT '集团id',
`s1` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '门店名称',
`s2` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '考评组名称',
`l1` bigint(20) DEFAULT NULL COMMENT '门店id',
`l2` bigint(20) DEFAULT NULL COMMENT '考评组id',
`l3` bigint(20) DEFAULT NULL COMMENT '考评池id',
`b1` decimal(10,4) DEFAULT NULL COMMENT '考评周期得分率',
`b2` decimal(10,4) DEFAULT NULL COMMENT '考评奖惩',
`yn` tinyint(1) DEFAULT NULL COMMENT '逻辑删除',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='门店考评';
CREATE TABLE `morax_003_m` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`report_date` date DEFAULT NULL COMMENT '日期',
`report_dimension` varchar(2) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '维度',
`group_id` bigint(20) DEFAULT NULL COMMENT '集团id',
`s1` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '人员名称',
`s2` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '门店名称',
`s3` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '岗位名称',
`s4` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '考评组名称',
`l1` bigint(20) DEFAULT NULL COMMENT '人员id',
`l2` bigint(20) DEFAULT NULL COMMENT '门店id',
`l3` bigint(20) DEFAULT NULL COMMENT '岗位id',
`l4` bigint(20) DEFAULT NULL COMMENT '考评组id',
`l5` bigint(20) DEFAULT NULL COMMENT '考评池id',
`b1` decimal(10,4) DEFAULT NULL COMMENT '考评周期得分率',
`b2` decimal(10,4) DEFAULT NULL COMMENT '考评奖惩',
`b3` tinyint(1) DEFAULT NULL COMMENT '是否纳入考评考核',
`yn` tinyint(1) DEFAULT NULL COMMENT '逻辑删除',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='人员考评';
-- 2023年3月29日
ALTER TABLE `fw_morax`.`kpi_group_indicator`
ADD COLUMN `code` varchar(255) NULL COMMENT '指标编码' AFTER `param_alias`,
ADD COLUMN `code_type` tinyint(4) NULL DEFAULT 1 COMMENT '指标类型(1.指标,2,考评指标 3,组合指标)' AFTER `code`;
ALTER TABLE `fw_morax`.`salary_group_project`
ADD COLUMN `code` varchar(255) NULL COMMENT '指标编码' AFTER `precondition_alias`,
ADD COLUMN `code_type` tinyint(4) NULL DEFAULT 1 COMMENT '指标类型(1.指标,2,考评指标 3,组合指标)' AFTER `code`;
ALTER TABLE `fw_morax`.`salary_group_project`
ADD COLUMN `ladder_code` varchar(255) NULL COMMENT '台阶指标编码' AFTER `precondition_alias`,
ADD COLUMN `ladder_code_type` tinyint(4) NULL DEFAULT 1 COMMENT '台阶指标类型(1.指标,2,考评指标 3,组合指标)' AFTER `ladder_code`,
ADD COLUMN `commission_code` varchar(255) NULL COMMENT '提成指标编码' AFTER `ladder_code_type`,
ADD COLUMN `commission_code_type` tinyint(4) NULL DEFAULT 1 COMMENT '提成指标类型(1.指标,2,考评指标 3,组合指标)' AFTER `commission_code`;
-- ALTER TABLE `fw_morax`.`eval_group_indicator`
-- ADD COLUMN `ladder_code` varchar(255) NULL COMMENT '台阶指标编码' AFTER `precondition_alias`,
-- ADD COLUMN `ladder_code_type` tinyint(4) NULL DEFAULT 1 COMMENT '台阶指标类型(1.指标,2,考评指标 3,组合指标)' AFTER `ladder_code`,
-- ADD COLUMN `commission_code` varchar(255) NULL COMMENT '提成指标编码' AFTER `ladder_code_type`,
-- ADD COLUMN `commission_code_type` tinyint(4) NULL DEFAULT 1 COMMENT '提成指标类型(1.指标,2,考评指标 3,组合指标)' AFTER `commission_code`;
ALTER TABLE `fw_morax`.`eval_group_indicator`
ADD COLUMN `code` varchar(255) NULL COMMENT '指标编码' AFTER `precondition_alias`,
ADD COLUMN `code_type` tinyint(4) NULL DEFAULT 1 COMMENT '指标类型(1.指标,2,考评指标 3,组合指标)' AFTER `code`;
ALTER TABLE `fw_morax`.`eval_group_reward`
ADD COLUMN `code` varchar(255) NULL COMMENT '指标编码' AFTER `precondition_alias`,
ADD COLUMN `code_type` tinyint(4) NULL DEFAULT 1 COMMENT '指标类型(1.指标,2,考评指标 3,组合指标)' AFTER `code`;
ALTER TABLE `fw_morax`.`eval_group_reward`
ADD COLUMN `ladder_code` varchar(255) NULL COMMENT '台阶指标编码' AFTER `precondition_alias`,
ADD COLUMN `ladder_code_type` tinyint(4) NULL DEFAULT 1 COMMENT '台阶指标类型(1.指标,2,考评指标 3,组合指标)' AFTER `ladder_code`,
ADD COLUMN `commission_code` varchar(255) NULL COMMENT '提成指标编码' AFTER `ladder_code_type`,
ADD COLUMN `commission_code_type` tinyint(4) NULL DEFAULT 1 COMMENT '提成指标类型(1.指标,2,考评指标 3,组合指标)' AFTER `commission_code`;
ALTER TABLE `fw_morax`.`eval_user_pool`
ADD COLUMN `rank` int(10) NULL COMMENT '排名' AFTER `score_ratio`;
ALTER TABLE `fw_morax`.`eval_shop_pool`
ADD COLUMN `rank` int(10) NULL COMMENT '排名' AFTER `score_ratio`;
CREATE TABLE `report_kpi_dim_value` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`report_kpi_rank_id` bigint(20) DEFAULT NULL COMMENT '绩效排名组id',
`dimension` tinyint(4) DEFAULT NULL COMMENT '维度(用户,门店)',
`refer_id` bigint(255) DEFAULT NULL COMMENT '维度id',
`kpi_pool_id` bigint(20) DEFAULT NULL COMMENT '绩效池id(用户维度跳转用)',
`user_id` bigint(20) DEFAULT NULL COMMENT '用户id',
`user_name` varchar(255) DEFAULT NULL COMMENT '用户名称',
`post_id` bigint(20) DEFAULT NULL COMMENT '岗位id',
`post_name` varchar(255) DEFAULT NULL COMMENT '岗位',
`shop_id` bigint(20) DEFAULT NULL COMMENT '门店id',
`shop_name` varchar(255) DEFAULT NULL COMMENT '门店名称',
`data_date` date DEFAULT NULL COMMENT '日期',
`inclusion` tinyint(255) DEFAULT NULL COMMENT '是否纳入绩效计算',
PRIMARY KEY (`id`),
KEY `idx_1` (`report_kpi_rank_id`,`dimension`,`data_date`)
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=utf8mb4 COMMENT='报表编码维度数据表';
CREATE TABLE `report_kpi_rank` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
`rank_id` bigint(20) DEFAULT NULL COMMENT '绩效排名组id',
`rank_name` varchar(255) DEFAULT NULL COMMENT '绩效排名组名称',
`kpi_group_ids` varchar(512) DEFAULT NULL COMMENT '绩效组id',
`kpi_group_info` text COMMENT '绩效组信息',
`shop_ids` varchar(512) DEFAULT NULL COMMENT '绩效所有门店id',
`user_ids` varchar(512) DEFAULT NULL COMMENT '绩效所有用户id',
`monthly` varchar(20) DEFAULT NULL COMMENT '数据月',
`data_date` date DEFAULT NULL,
`group_id` bigint(20) DEFAULT NULL COMMENT '集团id',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COMMENT='报表绩效排名组';
CREATE TABLE `report_kpi_rank_code` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`report_kpi_rank_id` bigint(20) DEFAULT NULL COMMENT '报表绩效排名组id',
`kpi_group_indicator_id` bigint(20) DEFAULT NULL COMMENT '对应指标id',
`code` varchar(255) DEFAULT NULL COMMENT '编码',
`code_type` tinyint(4) DEFAULT NULL COMMENT '编码类型 1:绩效指标 2:考评指标 3:组合指标',
`indicator_name` varchar(255) DEFAULT NULL COMMENT '指标名称',
`parent_code` varchar(255) DEFAULT NULL COMMENT '上一级编码,第一级为空',
`order_num` int(10) DEFAULT NULL COMMENT '顺序',
`data_date` date DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_report` (`report_kpi_rank_id`,`parent_code`,`data_date`)
) ENGINE=InnoDB AUTO_INCREMENT=50 DEFAULT CHARSET=utf8mb4 COMMENT='报表绩效排名组编码';
CREATE TABLE `report_kpi_value` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`report_kpi_dim_id` bigint(20) DEFAULT NULL COMMENT '报表编码维度数据表id',
`inclusion` tinyint(4) DEFAULT NULL COMMENT '是否纳入绩效计算',
`kpi_group_indicator_id` bigint(20) DEFAULT NULL COMMENT '对应指标id',
`parent_code` varchar(255) DEFAULT NULL COMMENT '上一级编码,第一级为空',
`code` varchar(255) DEFAULT NULL COMMENT '编码',
`code_type` tinyint(4) DEFAULT NULL COMMENT '编码类型',
`value` decimal(10,4) DEFAULT NULL COMMENT '值',
`data_date` date DEFAULT NULL COMMENT '日期',
PRIMARY KEY (`id`),
KEY `idx_report_kpi_dim_id` (`report_kpi_dim_id`)
) ENGINE=InnoDB AUTO_INCREMENT=361 DEFAULT CHARSET=utf8mb4 COMMENT='各个指标对应的各个维度(用户和门店)数据';
ALTER TABLE `fw_morax`.`kpi_pool`
ADD COLUMN `last_two_kpi_score_ratio` decimal(10, 4) NULL COMMENT '上两月绩效得分率(N-2)' AFTER `kpi_score`,
ADD COLUMN `last_one_kpi_score_ratio` decimal(10, 4) NULL COMMENT '上一月绩效得分率(N-1)' AFTER `last_two_kpi_score_ratio`,
ADD COLUMN `month_rank` int(11) NULL COMMENT '本月绩效排名' AFTER `assess_rank`;
-- ----------------------------
-- Table structure for eval_shop_rank_code_report
-- ----------------------------
DROP TABLE IF EXISTS `eval_shop_rank_code_report`;
CREATE TABLE `eval_shop_rank_code_report` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`eval_shop_rank_report_id` bigint(20) NOT NULL COMMENT '报表考评排名组id',
`eval_group_indicator_id` bigint(20) NOT NULL,
`code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '编码',
`code_type` tinyint(4) NULL DEFAULT NULL COMMENT '编码类型 1:绩效指标 2:考评指标 3:组合指标',
`indicator_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '指标名称',
`parent_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '上一级编码,第一级为空',
`order_num` int(10) NULL DEFAULT NULL COMMENT '顺序',
`data_date` date NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '报表绩效排名组编码' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for eval_shop_rank_report
-- ----------------------------
DROP TABLE IF EXISTS `eval_shop_rank_report`;
CREATE TABLE `eval_shop_rank_report` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
`rank_id` bigint(20) NULL DEFAULT NULL COMMENT '考评排名组id',
`rank_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '考评排名组名称',
`eval_group_ids` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '考评组id',
`shop_ids` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '考评所有门店id',
`group_id` bigint(20) NULL DEFAULT NULL COMMENT '集团id',
`data_date` date NULL DEFAULT NULL,
`monthly` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '月份',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '报表绩效排名组' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for eval_shop_report
-- ----------------------------
DROP TABLE IF EXISTS `eval_shop_report`;
CREATE TABLE `eval_shop_report` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`eval_shop_rank_report_id` bigint(20) NOT NULL,
`dimension` tinyint(4) NOT NULL COMMENT '维度',
`eval_shop_pool_id` bigint(20) NULL DEFAULT NULL,
`shop_id` bigint(20) NULL DEFAULT NULL COMMENT '门店id',
`shop_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '门店名称',
`data_date` date NULL DEFAULT NULL COMMENT '日期',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '报表编码数据' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for eval_shop_value_report
-- ----------------------------
DROP TABLE IF EXISTS `eval_shop_value_report`;
CREATE TABLE `eval_shop_value_report` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`eval_shop_report_id` bigint(20) NULL DEFAULT NULL,
`code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '编码',
`code_type` tinyint(4) NULL DEFAULT NULL COMMENT '编码类型',
`value` decimal(10, 4) NULL DEFAULT NULL COMMENT '值',
`data_date` date NULL DEFAULT NULL COMMENT '日期',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '报表编码数据' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for eval_user_rank_code_report
-- ----------------------------
DROP TABLE IF EXISTS `eval_user_rank_code_report`;
CREATE TABLE `eval_user_rank_code_report` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`eval_user_rank_report_id` bigint(20) NOT NULL COMMENT '报表考评排名组id',
`eval_group_indicator_id` bigint(20) NOT NULL COMMENT '考评项id',
`code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '编码',
`code_type` tinyint(4) NULL DEFAULT NULL COMMENT '编码类型 1:绩效指标 2:考评指标 3:组合指标',
`indicator_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '指标名称',
`parent_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '上一级编码,第一级为空',
`order_num` int(10) NULL DEFAULT NULL COMMENT '顺序',
`data_date` date NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '报表绩效排名组编码' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for eval_user_rank_report
-- ----------------------------
DROP TABLE IF EXISTS `eval_user_rank_report`;
CREATE TABLE `eval_user_rank_report` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
`rank_id` bigint(20) NULL DEFAULT NULL COMMENT '考评排名组id',
`rank_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '考评排名组名称',
`eval_group_ids` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '考评组id',
`shop_ids` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '考评所有门店id',
`group_id` bigint(20) NULL DEFAULT NULL COMMENT '集团id',
`monthly` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '月份',
`data_date` date NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '报表绩效排名组' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for eval_user_report
-- ----------------------------
DROP TABLE IF EXISTS `eval_user_report`;
CREATE TABLE `eval_user_report` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`eval_user_rank_report_id` bigint(20) NOT NULL,
`dimension` tinyint(4) NOT NULL COMMENT '维度',
`eval_user_pool_id` bigint(20) NULL DEFAULT NULL COMMENT '考评池id',
`user_id` bigint(20) NULL DEFAULT NULL COMMENT '用户id',
`user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '用户名称',
`post_id` bigint(20) NULL DEFAULT NULL COMMENT '岗位id',
`post_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '岗位',
`shop_id` bigint(20) NULL DEFAULT NULL COMMENT '门店id',
`shop_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '门店名称',
`inclusion` tinyint(4) NULL DEFAULT NULL COMMENT '是否纳入绩效计算',
`data_date` date NULL DEFAULT NULL COMMENT '日期',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '报表编码数据' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for eval_user_value_report
-- ----------------------------
DROP TABLE IF EXISTS `eval_user_value_report`;
CREATE TABLE `eval_user_value_report` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`eval_user_report_id` bigint(20) NULL DEFAULT NULL,
`code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '编码',
`code_type` tinyint(4) NULL DEFAULT NULL COMMENT '编码类型',
`value` decimal(10, 4) NULL DEFAULT NULL COMMENT '值',
`data_date` date NULL DEFAULT NULL COMMENT '日期',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '报表编码数据' ROW_FORMAT = Dynamic;
CREATE TABLE `eval_group_rank` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`scope_type` tinyint(4) NOT NULL COMMENT '考评范围; 1:人员考评 2:门店考评',
`name` varchar(255) NOT NULL COMMENT '组合名称',
`egcs` varchar(500) NOT NULL COMMENT '考评组编码',
`over_time` date DEFAULT NULL COMMENT '结束时间',
`backup` tinyint(1) DEFAULT NULL COMMENT '备份',
`status` tinyint(4) NOT NULL COMMENT '状态;1:审批中 2:审批拒绝 3:生效中 4:草稿',
`group_id` bigint(20) NOT NULL COMMENT '集团id',
`yn` tinyint(1) DEFAULT '1' COMMENT '逻辑删除',
`create_by` bigint(20) DEFAULT NULL COMMENT '创建者',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_by` bigint(20) DEFAULT NULL COMMENT '更新者',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='考评组排名配置';
-- 2023年4月21日
ALTER TABLE `fw_morax`.`kpi_reward_point`
ADD COLUMN `type_id` bigint(20) NULL COMMENT '类型id' AFTER `score_point`,
ADD COLUMN `type_name` varchar(1024) NULL COMMENT '类型名称' AFTER `type_id`;
ALTER TABLE `fw_morax`.`salary_extra_project`
ADD COLUMN `type_id` bigint(20) NULL COMMENT '类型id' AFTER `data_name`,
ADD COLUMN `type_name` varchar(1024) NULL COMMENT '类型名称' AFTER `type_id`;
-- 2023年4月17日
DROP TABLE IF EXISTS `report_kpi_dim_value_extra`;
CREATE TABLE `report_kpi_dim_value_extra` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`report_kpi_dim_value_id` bigint(20) NULL DEFAULT NULL COMMENT '报表编码维度数据表id',
`shop_ids` varchar(4048) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '管理门店',
`staff_ids` varchar(4048) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '管理人员id',
`role_codes` varchar(4048) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '角色编码',
`role_names` varchar(4048) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '角色名称',
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_1`(`report_kpi_dim_value_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '报表编码维度数据表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for report_salary
-- ----------------------------
DROP TABLE IF EXISTS `report_salary`;
CREATE TABLE `report_salary` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
`salary_name` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '薪酬名称',
`salary_group_id` bigint(20) NULL DEFAULT NULL COMMENT '薪酬组id',
`shop_ids` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '薪酬所有门店id',
`monthly` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '数据月',
`data_date` date NULL DEFAULT NULL,
`group_id` bigint(20) NULL DEFAULT NULL COMMENT '集团id',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '报表薪酬排名组' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for report_salary_code
-- ----------------------------
DROP TABLE IF EXISTS `report_salary_code`;
CREATE TABLE `report_salary_code` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`report_salary_id` bigint(20) NULL DEFAULT NULL COMMENT '薪酬报表id',
`salary_group_project_id` bigint(20) NULL DEFAULT NULL COMMENT '对应指标id',
`code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '编码',
`code_type` tinyint(4) NULL DEFAULT NULL COMMENT '编码类型 1:薪酬指标 2:考评指标 3:组合指标',
`indicator_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '指标名称',
`parent_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '上一级编码,第一级为空',
`order_num` int(10) NULL DEFAULT NULL COMMENT '顺序',
`data_date` date NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '报表薪酬排名组编码' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for report_salary_dim
-- ----------------------------
DROP TABLE IF EXISTS `report_salary_dim`;
CREATE TABLE `report_salary_dim` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`report_salary_id` bigint(20) DEFAULT NULL COMMENT '薪酬报表id',
`dimension` tinyint(4) DEFAULT NULL COMMENT '维度(用户,门店)',
`refer_id` bigint(255) DEFAULT NULL COMMENT '维度id',
`salary_pool_id` bigint(20) DEFAULT NULL COMMENT '薪酬池id(用户维度跳转用)',
`user_id` bigint(20) DEFAULT NULL COMMENT '用户id',
`user_name` varchar(255) DEFAULT NULL COMMENT '用户名称',
`post_id` bigint(20) DEFAULT NULL COMMENT '岗位id',
`post_name` varchar(255) DEFAULT NULL COMMENT '岗位',
`shop_id` bigint(20) DEFAULT NULL COMMENT '门店id',
`shop_name` varchar(255) DEFAULT NULL COMMENT '门店名称',
`data_date` date DEFAULT NULL COMMENT '日期',
`star_level` tinyint(4) DEFAULT NULL COMMENT '星级;1: A 2:B 3:C 4:D',
`total_reward` decimal(12,2) DEFAULT NULL COMMENT '总薪酬',
`kpi_score_ratio` decimal(10,2) DEFAULT NULL COMMENT '当月绩效得分率',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='报表编码维度数据表';
-- ----------------------------
-- Table structure for report_salary_dim_extra
-- ----------------------------
DROP TABLE IF EXISTS `report_salary_dim_extra`;
CREATE TABLE `report_salary_dim_extra` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`report_salary_dim_id` bigint(20) NULL DEFAULT NULL COMMENT '报表编码维度数据表id',
`shop_ids` varchar(4048) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '管理门店',
`staff_ids` varchar(4048) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '管理人员id',
`role_codes` varchar(4048) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '角色编码',
`role_names` varchar(4048) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '角色名称',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '报表编码维度额外数据表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for report_salary_dim_value
-- ----------------------------
DROP TABLE IF EXISTS `report_salary_dim_value`;
CREATE TABLE `report_salary_dim_value` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`report_salary_dim_id` bigint(20) NULL DEFAULT NULL COMMENT '报表编码维度数据表id',
`salary_group_project_id` bigint(20) NULL DEFAULT NULL COMMENT '对应指标id',
`parent_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '上一级编码,第一级为空',
`code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '编码',
`code_type` tinyint(4) NULL DEFAULT NULL COMMENT '编码类型',
`value` decimal(10, 4) NULL DEFAULT NULL COMMENT '值',
`data_date` date NULL DEFAULT NULL COMMENT '日期',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '各个指标对应的各个维度(用户和门店)数据' ROW_FORMAT = Dynamic;
ALTER TABLE `fw_morax`.`report_kpi_dim_value`
ADD COLUMN `kpi_score_ratio` decimal(10, 2) NULL COMMENT '当月绩效得分率' AFTER `inclusion`;
-- 2023年4月27日
ALTER TABLE `fw_morax`.`setting_draft`
ADD COLUMN `base_info` varchar(3000) NULL COMMENT '基本信息' AFTER `content`,
ADD COLUMN `post_ids` varchar(512) NULL COMMENT '岗位ids' AFTER `base_info`;
ALTER TABLE `fw_morax`.`kpi_group_rank`
ADD COLUMN `kpi_group_ids` varchar(255) NULL COMMENT '绩效组id' AFTER `kgcs`,
ADD COLUMN `begin_time` date NULL COMMENT '开始时间' AFTER `ineffective_date`,
ADD COLUMN `end_time` varchar(255) NULL COMMENT '失效时间' AFTER `begin_time`,
ADD COLUMN `status` tinyint(4) NULL COMMENT '状态;1:审批中 2:待生效 3:生效中 4:已失效' AFTER `end_time`,
ADD COLUMN `reason` varchar(512) DEFAULT NULL COMMENT '调整原因' AFTER `status`,
ADD COLUMN `attachment` varchar(512) DEFAULT NULL COMMENT '附件;fid逗号隔开' AFTER `reason`;
ALTER TABLE `fw_morax`.`salary_general_settin`
ADD COLUMN `appeal_days` int(10) NULL DEFAULT 3 COMMENT '工资申诉时间' AFTER `payoff_limit`;
ALTER TABLE `fw_morax`.`indicators`
ADD COLUMN `business_name` varchar(255) NULL COMMENT '业务名称' AFTER `indicator_name`;
ALTER TABLE `fw_morax`.`kpi_group_rank`
ADD COLUMN `post_ids` varchar(512) NULL COMMENT '岗位ids' AFTER `name`,
ADD COLUMN `shop_ids` varchar(512) NULL COMMENT '岗位ids' AFTER `post_ids`;
DROP TABLE IF EXISTS `eval_group`;
CREATE TABLE `eval_group` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`eval_group_rank_id` bigint(20) NULL DEFAULT NULL COMMENT '考评排名组id',
`eval_group_rank_stage_id` bigint(20) NULL DEFAULT NULL COMMENT '考评排名组阶段id',
`egc` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '考评组编码',
`post_ids` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '岗位id',
`post_names` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '岗位名称',
`shop_ids` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '适用门店ids',
`shop_names` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '适用门店名称',
`indicator_num` int(11) NULL DEFAULT NULL COMMENT '包含指标数量',
`group_id` bigint(20) NOT NULL COMMENT '集团id',
`yn` tinyint(1) NULL DEFAULT 1 COMMENT '逻辑删除',
`create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
`update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '考评组' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for eval_group_dim
-- ----------------------------
DROP TABLE IF EXISTS `eval_group_dim`;
CREATE TABLE `eval_group_dim` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`eval_group_id` bigint(20) NOT NULL COMMENT '考评组配置id',
`eval_stage_id` bigint(20) NOT NULL COMMENT '考评阶段id',
`type` tinyint(4) NOT NULL COMMENT '维度类型 1:人员 2:门店',
`shop_ids` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '门店id',
`shop_names` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '门店名称',
`post_ids` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '岗位id',
`post_names` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '岗位',
`dist_role_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '门店奖惩分配角色',
`dist_role_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '门店奖惩分配角色名称',
`yn` tinyint(1) NULL DEFAULT 1 COMMENT '逻辑删除',
`create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
`update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '考评奖惩' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for eval_group_rank
-- ----------------------------
DROP TABLE IF EXISTS `eval_group_rank`;
CREATE TABLE `eval_group_rank` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '考评,活动名称',
`status` tinyint(4) NOT NULL COMMENT '状态;1:审批中 2:审批拒绝 3:生效中 4:草稿 ',
`begin_time` date NULL DEFAULT NULL COMMENT '开始时间',
`over_time` date NULL DEFAULT NULL COMMENT '结束时间',
`multi_stage` tinyint(1) NULL DEFAULT NULL COMMENT '多阶段',
`reason` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '原因',
`attachment` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '附件;fid逗号隔开',
`group_id` bigint(20) NOT NULL COMMENT '集团id',
`yn` tinyint(1) NULL DEFAULT 1 COMMENT '逻辑删除',
`create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
`update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '考评' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for eval_group_rank_stage
-- ----------------------------
DROP TABLE IF EXISTS `eval_group_rank_stage`;
CREATE TABLE `eval_group_rank_stage` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`eval_group_rank_id` bigint(20) NULL DEFAULT NULL COMMENT '考评排名组id',
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '阶段名称',
`begin_time` date NULL DEFAULT NULL COMMENT '开始时间',
`over_time` date NULL DEFAULT NULL COMMENT '结束时间',
`yn` tinyint(1) NULL DEFAULT 1 COMMENT '逻辑删除',
`create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
`update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '考评' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for eval_group_user
-- ----------------------------
DROP TABLE IF EXISTS `eval_group_user`;
CREATE TABLE `eval_group_user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`eval_group_rank_id` bigint(20) NOT NULL COMMENT '考评排名组id',
`egc` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '考评组编码',
`eval_group_id` bigint(20) NULL DEFAULT NULL COMMENT '考评组id',
`user_id` bigint(20) NOT NULL COMMENT '人员id',
`user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '人员名称',
`post_id` bigint(20) NOT NULL COMMENT '岗位码',
`post_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '岗位名称',
`shop_id` bigint(20) NOT NULL COMMENT '门店id',
`shop_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '在职门店',
`ignored` tinyint(1) NOT NULL COMMENT '是否不计入考评考核',
`ignore_cause` tinyint(4) NULL DEFAULT NULL COMMENT '不计入原因;枚举待定',
`ignore_cause_desc` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '不计入原因描述',
`anticipated_date` date NULL DEFAULT NULL COMMENT '开始考核时间',
`remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
`data_date` date NULL DEFAULT NULL COMMENT '数据日期',
`group_id` bigint(20) NOT NULL COMMENT '集团id',
`yn` tinyint(1) NULL DEFAULT 1 COMMENT '逻辑删除',
`create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
`update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`) USING BTREE,
INDEX `eval_group_user_data_date_index`(`data_date`) USING BTREE,
INDEX `eval_group_user_user_id_index`(`user_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '考评组人员' ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
CREATE TABLE `salary_closure` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`shop_id` bigint(20) NOT NULL COMMENT '门店id',
`monthly` varchar(8) NOT NULL COMMENT '月度',
`closure_time` date NOT NULL COMMENT '关单时间',
`appeal_end_time` date NOT NULL COMMENT '申述结束时间',
`approve_end_time` date DEFAULT NULL COMMENT '审批结束时间',
`closure_kpi` tinyint(1) DEFAULT NULL COMMENT '门店绩效是否关单',
`create_confirm_todo` tinyint(1) DEFAULT NULL COMMENT '是否生成薪酬确认待办',
`create_sheet` tinyint(1) DEFAULT NULL COMMENT '是否生成工资单',
`group_id` bigint(20) NOT NULL COMMENT '集团id',
`yn` tinyint(1) DEFAULT '1' COMMENT '逻辑删除',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='薪酬月度通用设置';
CREATE TABLE `kpi_stage_mq_log` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`code` varchar(255) NOT NULL COMMENT '绩效指标编码',
`eval_group_rank_id` bigint(20) NOT NULL COMMENT '考评排名组id',
`eval_group_id` bigint(20) NOT NULL COMMENT '考评组id',
`begin_time` date NOT NULL COMMENT '开始时间',
`over_time` date NOT NULL COMMENT '结束时间',
`shop_ids` varchar(1024) DEFAULT NULL COMMENT '门店',
`user_ids` varchar(3000) DEFAULT NULL COMMENT '用户id',
`group_id` bigint(20) DEFAULT NULL COMMENT '集团id',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `indicator_user_stage_value` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`user_id` bigint(20) NOT NULL COMMENT '人员id',
`shop_id` bigint(20) DEFAULT '-1' COMMENT '门店id',
`dimension_type` tinyint(4) NOT NULL DEFAULT '1' COMMENT '维度类型 1:人员 2:门店',
`indicator_code` varchar(255) NOT NULL COMMENT '指标编码',
`indicator_name` varchar(255) DEFAULT NULL COMMENT '指标库名称',
`indicator_value` mediumtext NOT NULL COMMENT '指标业务值',
`value_type` tinyint(4) NOT NULL COMMENT '类型:1: 百分比 2:数量 3:条件值 4:金额 5:工龄',
`begin_date` date DEFAULT NULL COMMENT '数据开始日期',
`end_date` date DEFAULT NULL COMMENT '数据结束日期',
`group_id` bigint(20) NOT NULL COMMENT '集团id',
`yn` tinyint(1) DEFAULT '1' COMMENT '逻辑删除',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
KEY `kpi_indicator_value_data_date_index` (`begin_date`),
KEY `kpi_indicator_value_user_id_index` (`user_id`),
KEY `indicator_user_value_indicator_code_index` (`indicator_code`),
KEY `kpi_indicator_value_end_date_index` (`end_date`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='绩效人员指标阶段业务值';
-- eval_stage
-- salary_closure
-- eval_group_rank
-- eval_group
-- kpi_stage_mq_log
-- indicator_user_stage_value
-- 同步指标 考评得分率、绩效得分率