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`;