KPI / fw-morax

ddl.sql 31.9 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 
DROP TABLE IF EXISTS indicators;
CREATE TABLE indicators
(
    id             BIGINT(20)   NOT NULL AUTO_INCREMENT COMMENT 'ID',
    indicator_code VARCHAR(32)  NOT NULL COMMENT '指标编号',
    indicator_name VARCHAR(255) NOT NULL COMMENT '指标名称',
    sys_id         BIGINT(20)   NOT NULL COMMENT '归属系统id',
    sys_prefix      varchar(64) DEFAULT NULL COMMENT '归属系统前缀',
    sys_name       VARCHAR(32) COMMENT '归属系统名称',
    role_type      INT(3)       NOT NULL COMMENT '适用角色类型;1: 全部角色 2:全部管理角色 3:自定义',
    role_ids       VARCHAR(1024) DEFAULT -1 COMMENT '适用角色ids',
    role_codes     VARCHAR(1024) COMMENT '适用角色码',
    role_names     VARCHAR(2048) COMMENT '适用角色名称',
    enable          TINYINT(1)   NOT NULL COMMENT '启用/禁用;0:禁用 1:启用',
    target_type    INT(3)  NOT NULL DEFAULT '1' COMMENT '绩效目标值类型; 1:无 2:百分比 3:金额',
    create_time    DATETIME COMMENT '创建时间',
    update_time    DATETIME COMMENT '更新时间',
    yn             TINYINT(1) COMMENT '逻辑删除',
    PRIMARY KEY (id)
) COMMENT = '指标库';


CREATE UNIQUE INDEX idx_indicator_code ON indicators (indicator_code);

DROP TABLE IF EXISTS kpi_group;
CREATE TABLE kpi_group
(
    id                   BIGINT(20)   NOT NULL AUTO_INCREMENT COMMENT 'ID',
    post_id              BIGINT(20)   NOT NULL COMMENT '岗位id',
    post_name            VARCHAR(255) COMMENT '岗位名称',
    shop_ids             VARCHAR(255) NOT NULL COMMENT '适用门店ids',
    shop_names           VARCHAR(255) NOT NULL COMMENT '适用门店名称',
    pre_id               BIGINT(20)   NOT NULL COMMENT '前一次配置id',
    begin_time           DATETIME     NOT NULL COMMENT '生效时间',
    over_time            DATETIME COMMENT '失效时间',
    reason               VARCHAR(512) COMMENT '调整原因',
    attachment           VARCHAR(512) COMMENT '附件;fid逗号隔开',
    status               TINYINT(3)   NOT NULL COMMENT '状态;1:审批中 2:待生效 3:生效中 4:已失效',
    indicator_num        INT(5) COMMENT '包含指标数量',
    star_evaluation_type TINYINT(3)   NOT NULL COMMENT '星级评定方式;1:绩效得分率 2:人员百分比',
    group_id             BIGINT(20)   NOT NULL COMMENT '集团id',
    yn                   TINYINT(1)   NOT NULL COMMENT '逻辑删除',
    create_by            BIGINT(20) COMMENT '创建人',
    create_time          DATETIME COMMENT '创建时间',
    update_by            BIGINT(20) COMMENT '更新人',
    update_time          DATETIME COMMENT '更新时间',
    PRIMARY KEY (id)
) COMMENT = '绩效组配置';

DROP TABLE IF EXISTS kpi_star_ladders;
CREATE TABLE kpi_star_ladders
(
    id           BIGINT(20)     NOT NULL AUTO_INCREMENT COMMENT 'ID',
    kpi_group_id BIGINT(20) COMMENT '绩效组配置id',
    `level`      TINYINT(3)     NOT NULL COMMENT '等级;1:A 2:B 3:C 4:D',
    lower        DECIMAL(10, 4) NOT NULL COMMENT '下限',
    upper        DECIMAL(10, 4) COMMENT '上限',
    group_id     BIGINT(20)     NOT NULL COMMENT '集团id',
    yn           TINYINT(1)     NOT NULL COMMENT '逻辑删除',
    create_time  DATETIME COMMENT '创建时间',
    update_time  DATETIME COMMENT '更新时间',
    PRIMARY KEY (id)
) COMMENT = '星级评定阶梯';

DROP TABLE IF EXISTS kpi_group_indicator;
CREATE TABLE kpi_group_indicator
(
    id             BIGINT(20)   NOT NULL AUTO_INCREMENT COMMENT 'ID',
    kpi_group_id   BIGINT(20)   NOT NULL COMMENT '绩效组配置id',
    indicator_id   BIGINT(20)   NOT NULL COMMENT '指标库id',
    indicator_code VARCHAR(255) NOT NULL COMMENT '指标编码',
    indicator_name VARCHAR(255) COMMENT '指标库名称',
    base_score     INT(11)      NOT NULL COMMENT '基础绩效分',
    score_way      TINYINT(3) COMMENT '得分方式;1: 阶梯得分 2:普通得分',
    group_id       BIGINT(20) COMMENT '集团id',
    target_type  int(3) NOT NULL DEFAULT '1' COMMENT '绩效目标值类型; 1:无 2:百分比 3:金额',
    target_value decimal(10,4) DEFAULT NULL COMMENT '基础绩效分',
    yn             TINYINT(1)   NOT NULL COMMENT '逻辑删除',
    create_time    DATETIME COMMENT '创建时间',
    update_time    DATETIME COMMENT '更新时间',
    PRIMARY KEY (id)
) COMMENT = '绩效组指标项';

DROP TABLE IF EXISTS kpi_group_indicator_ladders;
CREATE TABLE kpi_group_indicator_ladders
(
    id                     BIGINT(20)     NOT NULL AUTO_INCREMENT COMMENT 'ID',
    kpi_group_indicator_id BIGINT(20)     NOT NULL COMMENT '绩效组指标项id',
    lower                  DECIMAL(10, 4) NOT NULL COMMENT '阶梯下限;大于等于',
    upper                  DECIMAL(10, 4) NOT NULL DEFAULT 65536 COMMENT '阶梯上限;小于; 无上限的情况值为 2<<15',
    standard_score         INT(3)         NOT NULL COMMENT '标准分;本阶梯对应标准分',
    group_id               BIGINT(20) COMMENT '集团id',
    yn                     TINYINT(1)     NOT NULL COMMENT '逻辑删除',
    create_time            DATETIME COMMENT '创建时间',
    update_time            DATETIME COMMENT '更新时间',
    PRIMARY KEY (id)
) COMMENT = '指标项得分阶梯';

DROP TABLE IF EXISTS kpi_group_user;
CREATE TABLE kpi_group_user
(
    id                BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
    kpi_group_id      BIGINT(20) NOT NULL COMMENT '绩效组id',
    user_id           BIGINT(20) NOT NULL COMMENT '人员id',
    user_name         VARCHAR(255) COMMENT '人员名称',
    post_id           bigint     NOT NULL COMMENT '岗位id',
    post_name         VARCHAR(255) COMMENT '岗位名称',
    shop_id           BIGINT(20) NOT NULL COMMENT '门店id',
    shop_name         VARCHAR(255) COMMENT '在职门店',
    ignored           TINYINT(1) NOT NULL COMMENT '是否不计入绩效考核',
    ignore_cause      TINYINT(3) COMMENT '不计入原因;枚举待定',
    ignore_cause_desc VARCHAR(255) COMMENT '不计入原因描述',
    anticipated_date  DATE COMMENT '开始考核时间',
    remark            VARCHAR(255) COMMENT '备注',
    data_date         DATE COMMENT '数据日期',
    group_id          BIGINT(20) NOT NULL COMMENT '集团id',
    yn                TINYINT(1) NOT NULL COMMENT '逻辑删除',
    create_time       DATETIME COMMENT '创建时间',
    update_time       DATETIME COMMENT '更新时间',
    PRIMARY KEY (id)
) COMMENT = '绩效组人员';

DROP TABLE IF EXISTS kpi_pool;
CREATE TABLE kpi_pool
(
    id              BIGINT(20)     NOT NULL AUTO_INCREMENT COMMENT 'ID',
    kpi_group_id    BIGINT(20)     NOT NULL COMMENT '绩效组id',
    user_id         BIGINT(20)     NOT NULL COMMENT '用户id',
    user_name       VARCHAR(255) COMMENT '用户名称',
    post_id         bigint         NOT NULL COMMENT '岗位id',
    post_name       VARCHAR(255) COMMENT '岗位名称',
    shop_id         BIGINT(20)     NOT NULL COMMENT '门店id',
    shop_name       VARCHAR(255) COMMENT '门店名称',
    kpi_score       DECIMAL(10, 4) NOT NULL COMMENT '绩效得分',
    kpi_score_ratio DECIMAL(10, 4) NOT NULL COMMENT '绩效得分率',
    star_level      TINYINT(3)     NOT NULL COMMENT '星级;1: A 2:B 3:C 4:D',
    actual_star     TINYINT(3)     NULL COMMENT '实际星级;默认等于系统评定的星级，有调整的情况修改次字段',
    revoked         TINYINT(1) COMMENT 'D级撤销状态;是否撤销本次D级',
    inclusion       TINYINT(1) COMMENT '是否纳入绩效计算',
    monthly         VARCHAR(8)     NOT NULL COMMENT '月度',
    regular         TINYINT(1)     NOT NULL DEFAULT 0 COMMENT '固定的',
    rank            int(10)         NULL COMMENT '排名' ,
    group_id        BIGINT(20)     NOT NULL COMMENT '集团id',
    yn              TINYINT(1)     NOT NULL COMMENT '逻辑删除',
    create_time     DATETIME COMMENT '创建时间',
    update_time     DATETIME COMMENT '更新时间',
    PRIMARY KEY (id)
) COMMENT = '绩效池';

DROP TABLE IF EXISTS kpi_pool_indicator_value;
CREATE TABLE kpi_pool_indicator_value
(
    id              BIGINT(20)     NOT NULL AUTO_INCREMENT COMMENT 'ID',
    kpi_pool_id     BIGINT(20)     NOT NULL COMMENT '绩效池id',
    user_id         BIGINT(20)     NOT NULL COMMENT '人员id',
    indicator_code  VARCHAR(255)   NOT NULL COMMENT '指标编码',
    indicator_value DECIMAL(10, 4) NOT NULL COMMENT '指标业务值',
    indicator_score DECIMAL(10, 4) NOT NULL COMMENT '指标得分',
    data_date       DATE COMMENT '数据日期',
    mq_log_id       BIGINT(20)     NOT NULL COMMENT 'mq记录id',
    group_id        BIGINT(20)     NOT NULL COMMENT '集团id',
    yn              TINYINT(1)     NOT NULL COMMENT '逻辑删除',
    create_time     DATETIME COMMENT '创建时间',
    update_time     DATETIME COMMENT '更新时间',
    PRIMARY KEY (id)
) COMMENT = '绩效人员指标业务值';


DROP TABLE IF EXISTS kpi_reward_point;
create table kpi_reward_point
(
    id            bigint auto_increment,
    user_id       bigint         not null comment '用户id',
    post_id       bigint         not null comment '岗位id',
    shop_id       bigint         not null comment '在职门店',
    occurred_time datetime       not null comment '产生时间',
    score_point   decimal(18, 4) not null comment '奖惩分数值; 正数奖励，负数惩罚',
    monthly       VARCHAR(8)     NOT NULL COMMENT '月度',
    group_id      bigint         not null comment '集团id',
    create_time   datetime       null,
    update_time   datetime       null,
    constraint kpi_deduct_record_pk
        primary key (id)
)
    comment '绩效分奖惩纪录';


DROP TABLE IF EXISTS kpi_star_special_rule;
CREATE TABLE kpi_star_special_rule
(
    id                        BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
    user_id                   BIGINT(20) NOT NULL COMMENT '人员id',
    user_name                 VARCHAR(255) COMMENT '人员名称',
    earliest_starting_monthly DATE COMMENT '开始考核日期',
    revised_monthly           DATE COMMENT '修正月度',
    revised_level             TINYINT(1) COMMENT '修正星级;1: A 2:B 3:C 4:D',
    exclusion_monthly         DATE COMMENT '排除月度',
    revoked_times             INT COMMENT '免除次数;免除D级次数',
    status                    TINYINT(3) COMMENT '状态;1:审批中 2:待生效 3:生效中 4:已失效',
    reason                    VARCHAR(255) COMMENT '原因',
    attachment                VARCHAR(255) COMMENT '附件;fid逗号隔开',
    group_id                  BIGINT(20) NOT NULL COMMENT '集团id',
    yn                        TINYINT(1) NOT NULL COMMENT '逻辑删除',
    create_time               DATETIME COMMENT '创建时间',
    update_time               DATETIME COMMENT '更新时间',
    PRIMARY KEY (id)
) COMMENT = '绩效星级特殊规则';

DROP TABLE IF EXISTS kpi_star_rule;
CREATE TABLE kpi_star_rule
(
    id                    BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
    roll_month            INT(5) COMMENT '滚动月数',
    forced_turnover_cycle INT(5) COMMENT '强制离职考核周期',
    max_unqualified_times INT(5) COMMENT '强制离职周期内最大累计D级数',
    continuous_monthly    INT(5) COMMENT '连续月份;满足次条件减少一次D级',
    min_score_ratio       DECIMAL(10, 4) COMMENT '最小得分率',
    group_id              BIGINT(20) NOT NULL COMMENT '集团id',
    yn                    TINYINT(1) NOT NULL COMMENT '逻辑删除',
    create_time           DATETIME COMMENT '创建时间',
    update_time           DATETIME COMMENT '更新时间',
    PRIMARY KEY (id)
) COMMENT = '绩效星级规则';

DROP TABLE IF EXISTS kpi_mq_log;
create table kpi_mq_log
(
    id             bigint       not null,
    indicator_code varchar(225) not null comment '指标编码',
    post_id        bigint       not null comment '岗位id',
    send_time      datetime     not null comment '发送时间',
    group_id       bigint       not null comment '集团id',
    create_time    datetime     null comment '创建时间',
    constraint kpi_mq_log_pk
        primary key (id)
)
    comment '绩效上报mq发送记录';

create unique index kpi_mq_log_id_uindex
    on kpi_mq_log (id);



DROP TABLE IF EXISTS salary_general_settin;
CREATE TABLE salary_general_settin
(
    id                BIGINT(20)  NOT NULL AUTO_INCREMENT COMMENT 'ID',
    leader_visibility TINYINT(1) COMMENT '管理层可见',
    dates_of_appeal   VARCHAR(10) NOT NULL DEFAULT '3,5' COMMENT '可申诉日期;eg: 1,20 逗号隔开只存开始和结束',
    payoff_date       INT(2) COMMENT '薪资发放日期;取值1-28（每月）',
    payoff_limit      INT(11) COMMENT '薪资发放公户限额;元 0元则不限制',
    group_id          BIGINT(20)  NOT NULL COMMENT '集团id',
    yn                TINYINT(1)  NOT NULL COMMENT '逻辑删除',
    create_time       DATETIME COMMENT '创建时间',
    update_time       DATETIME COMMENT '更新时间',
    PRIMARY KEY (id)
) COMMENT = '薪酬通用设置';

DROP TABLE IF EXISTS salary_project;
CREATE TABLE salary_project
(
    id              BIGINT(20)    NOT NULL AUTO_INCREMENT COMMENT 'ID',
    salary_code     VARCHAR(255) COMMENT '薪酬项编码',
    salary_name     VARCHAR(255) COMMENT '薪酬项名称',
    optional_method VARCHAR(32) COMMENT '计算方式; 1: 星级 2:固定金额 3:阶梯(单) 4:按量 5: 固定提成 6:阶梯(总)',
    `visible`       TINYINT(1)    NOT NULL COMMENT '可见的;对用户可见 只有对用户可见的才能配置薪酬项，否则为系统默认使用的项目',
    role_type       int(3)        not null comment '适用角色类型;1: 全部角色 2:全部管理角色 3:自定义',
    role_ids        varchar(1024) not null default '-1' comment '角色ids',
    role_names      varchar(2048) null comment '角色名称',
    `enabled`       TINYINT(1)    NOT NULL COMMENT '启用禁用',
    yn              TINYINT(1)    NOT NULL COMMENT '逻辑删除',
    create_time     DATETIME COMMENT '创建时间',
    update_time     DATETIME COMMENT '更新时间',
    PRIMARY KEY (id)
) COMMENT = '薪酬项';

DROP TABLE IF EXISTS salary_group;
CREATE TABLE salary_group
(
    id          BIGINT(20)   NOT NULL AUTO_INCREMENT COMMENT 'ID',
    post_id     BIGINT(20)   NOT NULL COMMENT '岗位id',
    post_name   VARCHAR(255) COMMENT '岗位名称',
    shop_ids    VARCHAR(255) NOT NULL COMMENT '适用门店ids',
    shop_names  VARCHAR(1024) COMMENT '适用门店名称',
    pre_id      BIGINT(20) COMMENT '前次配置id',
    begin_time  DATE COMMENT '生效时间',
    over_time   DATE COMMENT '失效时间',
    reason      VARCHAR(900) COMMENT '调整原因',
    attachment  VARCHAR(512) COMMENT '附件ids',
    status      INT(3)       NOT NULL COMMENT '状态;1:审批中 2:待生效 3:生效中 4:已失效',
    project_num INT(11) COMMENT '包薪酬项数量;只统计可配置的的项目数',
    group_id    BIGINT(20)   NOT NULL COMMENT '集团id',
    yn          TINYINT(1)   NOT NULL COMMENT '逻辑删除',
    create_time DATETIME COMMENT '创建时间',
    update_time DATETIME COMMENT '更新时间',
    PRIMARY KEY (id)
) COMMENT = '薪酬组配置';

DROP TABLE IF EXISTS salary_group_project;
CREATE TABLE salary_group_project
(
    id                  BIGINT(20)   NOT NULL AUTO_INCREMENT COMMENT 'ID',
    salary_group_id     BIGINT(20)   NOT NULL COMMENT '薪酬组id',
    salary_project_id   BIGINT(20)   NOT NULL COMMENT '薪酬项目id',
    salary_project_code VARCHAR(255) NOT NULL COMMENT '薪酬项目编码',
    salary_project_name VARCHAR(255) COMMENT '薪酬项目名称',
    cal_method          INT(3) COMMENT '计算方式; 1: 星级 2:固定金额 3:阶梯(单) 4:按量 5: 固定提成 6:阶梯(总) 7:动态',
    single              TINYINT(1)   NOT NULL COMMENT '唯一值;是否允许有多个相同项目',
    group_id            BIGINT(20)   NOT NULL COMMENT '集团id',
    yn                  TINYINT(1)   NOT NULL COMMENT '逻辑删除',
    create_time         DATETIME COMMENT '创建时间',
    update_time         DATETIME COMMENT '更新时间',
    PRIMARY KEY (id)
) COMMENT = '薪酬组薪酬项目';

DROP TABLE IF EXISTS salary_group_project_settin;
CREATE TABLE salary_group_project_settin
(
    id                      BIGINT(20)     NOT NULL AUTO_INCREMENT COMMENT 'ID',
    salary_group_project_id BIGINT(20)     NOT NULL COMMENT '薪酬项目配置id',
    stair_key               BIGINT(20) COMMENT '阶梯key;新车销售按车系计算时对应车系id 星级则是对应星级枚举 阶梯等则是对应指标id',
    stair_key_desc          VARCHAR(255) COMMENT '阶梯key对应描述',
    stair_value             DECIMAL(18, 2) NOT NULL COMMENT '计算相关值',
    stair_min               INT(11) COMMENT '阶梯值下限',
    stair_max               INT(11) DEFAULT 65536 COMMENT '阶梯值上限;阶梯值上限',
    group_id                BIGINT(20)     NOT NULL COMMENT '集团id',
    yn                      TINYINT(1)     NOT NULL COMMENT '逻辑删除',
    create_time             DATETIME COMMENT '创建时间',
    update_time             DATETIME COMMENT '更新时间',
    PRIMARY KEY (id)
) COMMENT = '薪酬项目配置';

DROP TABLE IF EXISTS salary_group_user;
CREATE TABLE salary_group_user
(
    id              BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
    salary_group_id BIGINT(20) NOT NULL COMMENT '薪酬组id',
    user_id         BIGINT(20) NOT NULL COMMENT '人员id',
    user_name       VARCHAR(255) COMMENT '人员名称',
    post_id         bigint     NOT NULL COMMENT '岗位id',
    post_name       VARCHAR(255) COMMENT '岗位名称',
    shop_id         BIGINT(20) NOT NULL COMMENT '门店id',
    shop_name       VARCHAR(255) COMMENT '门店名称',
    probationer     TINYINT(1) NOT NULL COMMENT '试用人员',
    frozen          TINYINT(1) NULL COMMENT '冻结状态',
    data_date       DATE COMMENT '数据日期',
    group_id        BIGINT(20) NOT NULL COMMENT '集团id',
    yn              TINYINT(1) NOT NULL COMMENT '逻辑删除',
    create_time     DATETIME COMMENT '创建时间',
    update_time     DATETIME COMMENT '更新时间',
    PRIMARY KEY (id)
) COMMENT = '薪酬组人员';



DROP TABLE IF EXISTS salary_pool;
CREATE TABLE salary_pool
(
    id              BIGINT(20)     NOT NULL AUTO_INCREMENT COMMENT 'ID',
    salary_group_id BIGINT(20)     NOT NULL COMMENT '薪酬组id',
    user_id         BIGINT(20)     NOT NULL COMMENT '用户id',
    user_name       VARCHAR(255) COMMENT '用户名称',
    post_id         bigint         NOT NULL COMMENT '岗位id',
    post_name       VARCHAR(255) COMMENT '岗位名称',
    shop_id         BIGINT(20)     NOT NULL COMMENT '门店id',
    shop_name       VARCHAR(255) COMMENT '门店名称',
    star_level      TINYINT(3)     NOT NULL COMMENT '星级;1: A 2:B 3:C 4:D',
    reward          DECIMAL(18, 2) NOT NULL COMMENT '薪资报酬',
    monthly         VARCHAR(8)     NOT NULL COMMENT '月度',
    regular         TINYINT(1)     NOT NULL COMMENT '固定的',
    signature_file  VARCHAR(255) COMMENT '签名文件',
    paid            TINYINT(1) COMMENT '是否已发放',
    group_id        BIGINT(20)     NOT NULL COMMENT '集团id',
    yn              TINYINT(1)     NOT NULL COMMENT '逻辑删除',
    create_time     DATETIME COMMENT '创建时间',
    update_time     DATETIME COMMENT '更新时间',
    PRIMARY KEY (id)
) COMMENT = '薪酬池';

DROP TABLE IF EXISTS salary_pool_detail;
CREATE TABLE salary_pool_detail
(
    id                   BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
    salary_pool_id       BIGINT(20) NOT NULL COMMENT '薪酬池id',
    salary_project_id    BIGINT(20) COMMENT '薪酬项id',
    salary_project_name  VARCHAR(255) COMMENT '薪酬项名称',
    type                 int(3)     not null comment '薪酬类型 1:绩效提成 2:内部奖励 3:内部罚款 4:社保扣款 5:福利补贴 6:试用期工资',
    salary_amount        DECIMAL(18, 2) COMMENT '预计收入',
    processed_amount     decimal(18, 2) COMMENT '已处理金额',
    actual_salary_amount DECIMAL(18, 2) COMMENT '实际收入',
    group_id             BIGINT(20) NOT NULL COMMENT '集团id',
    salary_date          date       not null COMMENT '薪资日期',
    yn                   TINYINT(1) NOT NULL COMMENT '逻辑删除',
    create_time          DATETIME COMMENT '创建时间',
    update_time          DATETIME COMMENT '更新时间',
    PRIMARY KEY (id)
) COMMENT = '薪资明细';

DROP TABLE IF EXISTS salary_confirm;
CREATE TABLE salary_confirm
(
    id               BIGINT(20)  NOT NULL AUTO_INCREMENT COMMENT 'ID',
    salary_pool_id   BIGINT(20)  NOT NULL COMMENT '薪酬池id',
    plan_time        datetime    not null comment '计划推送待办时间',
    pushed_todo      TINYINT(1)  NOT NULL COMMENT '是否推送代办',
    todo_code        varchar(64) not null comment '待办编码',
    deadline         DATETIME COMMENT '截止日期',
    confirmed_status TINYINT(3) COMMENT '确认状态;1:待定 2:已确认 3:已取消',
    confirm_time     DATETIME COMMENT '确认时间',
    self_confirm     TINYINT(1) COMMENT '是否本人确认',
    group_id         BIGINT(20)  NOT NULL COMMENT '集团id',
    yn               TINYINT(1)  NOT NULL COMMENT '逻辑删除',
    create_time      DATETIME COMMENT '创建时间',
    update_time      DATETIME COMMENT '更新时间',
    PRIMARY KEY (id)
) COMMENT = '薪酬确认记录';

DROP TABLE IF EXISTS salary_raw_data;
CREATE TABLE salary_raw_data
(
    id                   BIGINT(20)    NOT NULL AUTO_INCREMENT COMMENT 'ID',
    salary_project_code  VARCHAR(255) COMMENT '薪酬项编码',
    salary_project_value VARCHAR(2000) NOT NULL COMMENT '薪酬项具体值;jons字符串 map类型',
    value_type           TINYINT(3) COMMENT '类型;1: 百分比 2:数量 3:条件值 4:金额 5:工龄',
    data_date            DATE          NOT NULL COMMENT '数据日期',
    user_id              BIGINT(20)    NOT NULL COMMENT '人员id',
    group_id             BIGINT(20)    NOT NULL COMMENT '集团id',
    yn                   TINYINT(1)    NOT NULL COMMENT '逻辑删除',
    create_time          DATETIME COMMENT '创建时间',
    update_time          DATETIME COMMENT '更新时间',
    PRIMARY KEY (id)
) COMMENT = '薪酬提成原始数据';


DROP TABLE IF EXISTS salary_extra_project;
create table salary_extra_project
(
    id             bigint auto_increment,
    salary_pool_id BIGINT COMMENT '薪酬池ID',
    post_id        bigint         not null comment '岗位id',
    shop_id        bigint         not null comment '在职门店id',
    user_id        BIGINT(20)     NOT NULL COMMENT '用户id',
    money          decimal(18, 4) not null COMMENT '金额',
    timely         TINYINT(1)     NOT NULL COMMENT '及时处理',
    salary_type    int(3)         not null comment '额外项类型 1:奖励 2:处罚 3:社保扣除项',
    data_id        varchar(64)    not null comment '业务数据id',
    data_name      varchar(128)   not null comment '业务名称 e.g 考勤扣款、活动奖金... ',
    data_date      DATE           NOT NULL COMMENT '产生时间',
    group_id       BIGINT(20)     NOT NULL COMMENT '集团id',
    create_time    DATETIME COMMENT '创建时间',
    update_time    DATETIME COMMENT '更新时间',
    constraint salary_extra_project_pk
        primary key (id)
)
    comment '额外薪资项';


DROP TABLE IF EXISTS approval_record;
CREATE TABLE approval_record
(
    id              BIGINT(20)   NOT NULL AUTO_INCREMENT COMMENT 'ID',
    data_id         BIGINT(20)   NOT NULL COMMENT '被审批业务表主键',
    approval_type   INT(3)       NOT NULL COMMENT '审批业务类型;1:绩效配置 2:薪酬配置 3:绩效星级申诉',
    approval_no     VARCHAR(255) NOT NULL COMMENT '审批单号',
    approval_status INT(3)       NOT NULL COMMENT '审批状态;1:审批中 2:审批通过 3:审批拒绝',
    group_id        BIGINT(20)   NOT NULL COMMENT '集团id',
    yn              TINYINT(1)   NOT NULL COMMENT '逻辑删除',
    create_time     DATETIME COMMENT '创建时间',
    update_time     DATETIME COMMENT '更新时间',
    PRIMARY KEY (id)
) COMMENT = '审批记录';



DROP TABLE IF EXISTS payroll_record;
create table payroll_record
(
    id             bigint auto_increment,
    user_id        bigint         not null comment '员工id',
    user_name      varchar(32)    null comment '员工名称',
    shop_id        bigint         not null comment '门店id',
    dealer_id      bigint         null comment '商家id',
    biz_id         varchar(32)    not null comment '业务ID(对应薪酬池id)',
    payment        tinyint(1)     not null comment '会否是付款',
    salary         tinyint(1)     not null comment '是否是工资',
    amount         decimal(18, 4) not null comment '金额',
    corporate      tinyint(1)     not null comment '公帐发放',
    payroll_status int(3)         not null comment '发放状态 1:待推送到财务系统 2:已推送到财务系统 3:已发送成功',
    payroll_token  varchar(64)    null comment '发送token',
    monthly        varchar(12)    null comment '月度',
    group_id       bigint         not null comment '集团id',
    create_time    datetime       null comment '创建时间',
    update_time    datetime       null comment '更新时间',
    constraint Payroll_Record_pk
        primary key (id)
)
    comment '薪资发放记录';


DROP TABLE IF EXISTS salary_project_hit_log;
create table salary_project_hit_log
(
    id                             bigint auto_increment,
    salary_project_id              bigint   not null comment '薪酬项id',
    salary_pool_id                 bigint   not null comment '薪酬池id',
    salary_group_project_settin_id bigint   not null comment '命中具体配置项id',
    salary_value                   decimal(18, 2) comment '薪酬值',
    data_date                      date     not null comment '数据日期',
    group_id                       bigint   not null comment '集团id',
    create_time                    datetime null,
    update_time                    datetime null,
    constraint salary_project_hit_log_pk
        primary key (id)
)
    comment '用户薪酬项命中记录';

DROP TABLE IF EXISTS kpi_indicator_hit_log;
create table kpi_indicator_hit_log
(
    id                             bigint auto_increment
        primary key,
    kpi_pool_id                    bigint               not null comment '绩效池id',
    kpi_group_indicator_id         bigint               not null comment '绩效组指标id',
    kpi_group_indicator_ladders_id bigint               not null comment '绩效组指标阶梯id',
    kpi_value                      decimal(18, 2)       null comment '绩效值',
    data_date                      date                 not null comment '数据日期',
    group_id                       bigint               not null comment '集团id',
    create_time                    datetime             null,
    update_time                    datetime             null,
    yn                             tinyint(1) default 1 null,
    constraint salary_project_hit_log_index
        unique (kpi_group_indicator_id, kpi_pool_id, data_date)
)
    comment '用户绩效项命中记录';





create index approval_record_approval_no_index
    on approval_record (approval_no);

create index kpi_group_post_id_index
    on kpi_group (post_id);

create index kpi_group_user_data_date_index
    on kpi_group_user (data_date);

create index kpi_group_user_kpi_group_id_index
    on kpi_group_user (kpi_group_id);

create index kpi_group_user_user_id_index
    on kpi_group_user (user_id);

create unique index pool_kpi_id_user_id_monthly_uindex
    on kpi_pool (kpi_group_id, user_id, monthly);

create index kpi_pool_kpi_group_id_index
    on kpi_pool (kpi_group_id);

create index kpi_pool_monthly_index
    on kpi_pool (monthly);

create index kpi_pool_user_id_index
    on kpi_pool (user_id);

create index kpi_pool_indicator_value_data_date_index
    on kpi_pool_indicator_value (data_date);

create index kpi_pool_indicator_value_user_id_index
    on kpi_pool_indicator_value (user_id);

create index salary_confirm_salary_pool_id_index
    on salary_confirm (salary_pool_id);


create index salary_group_user_data_date_index
    on salary_group_user (data_date);

create index salary_group_user_salary_group_id_index
    on salary_group_user (salary_group_id);

create index salary_group_user_user_id_index
    on salary_group_user (user_id);

create index salary_pool_monthly_index
    on salary_pool (monthly);

create index salary_pool_salary_group_id_index
    on salary_pool (salary_group_id);

create index salary_pool_user_id_index
    on salary_pool (user_id);

create index salary_pool_detail_salary_pool_id_index
    on salary_pool_detail (salary_pool_id);

create index salary_pool_detail_salary_date_index
    on salary_pool_detail (salary_date);

create index salary_raw_data_user_id_index
    on salary_raw_data (user_id);

create index salary_raw_data_date_index
    on salary_raw_data (data_date);

create index kpi_mq_log_indicator_code_index
    on kpi_mq_log (indicator_code);

create index kpi_mq_log_post_id_index
    on kpi_mq_log (post_id);

create index kpi_pool_indicator_value_mq_log_id_index
    on kpi_pool_indicator_value (mq_log_id);

create index kpi_reward_point_monthly_index
    on kpi_reward_point (monthly);

create index kpi_reward_point_user_id_post_id_index
    on kpi_reward_point (user_id, post_id);

create index payroll_record_biz_id_index
    on payroll_record (biz_id);

create index payroll_record_monthly_index
    on payroll_record (monthly);

create index payroll_record_user_id_index
    on payroll_record (user_id);

create unique index salary_project_hit_log_index
    on salary_project_hit_log (salary_project_id, salary_pool_id, data_date);

create unique index kpi_indicator_hit_log_index
    on kpi_indicator_hit_log (kpi_group_indicator_id, kpi_pool_id, data_date);


-- 2022年10月9日添加字段
ALTER TABLE `fw_morax`.`kpi_pool`
    ADD COLUMN `average_kpi_score_ratio` decimal(10, 4) NOT NULL COMMENT '平均绩效得分率' AFTER `kpi_score_ratio`;

ALTER TABLE `fw_morax`.`kpi_group`
    ADD COLUMN `name` varchar(255) NULL COMMENT '绩效组名称' AFTER `id`,
ADD COLUMN `revoked_score_ratio` decimal(10, 4) NULL COMMENT '人员百分比，撤销D级得分率' AFTER `star_evaluation_type`;

ALTER TABLE `fw_morax`.`salary_group`
    ADD COLUMN `name` varchar(255) NULL COMMENT '薪酬组名称' AFTER `id`;

CREATE TABLE `kpi_group_rank` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(255) NOT NULL COMMENT '组合名称',
  `star_evaluation_type` tinyint(3) NOT NULL COMMENT '星级评定方式;1:绩效得分率 2:人员百分比',
  `revoked_score_ratio` decimal(10,4) DEFAULT NULL COMMENT '撤销D级得分率',
  `kgcs` varchar(500) 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_group_rank_star_ladders` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`kpi_group_rank_id` bigint(20) DEFAULT NULL COMMENT '绩效组排名配置id',
`level` tinyint(3) NOT NULL COMMENT '等级;1:A 2:B 3:C 4:D',
`lower` decimal(10,2) NOT NULL COMMENT '下限',
`upper` decimal(10,2) 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='排名星级评定阶梯';

ALTER TABLE `fw_morax`.`salary_group_project`
    MODIFY COLUMN `cal_method` int(3) NOT NULL COMMENT '计算方式;1: 星级 2:固定金额 3:阶梯(单) 4:按量 5: 固定提成 6:阶梯(总) 7:动态 8.绩效指标阶梯（单）' AFTER `salary_project_name`,
    ADD COLUMN `kpi_indicator` bigint(20) NULL COMMENT '绩效指标' AFTER `cal_method`;