pax_global_header 0000666 0000000 0000000 00000000064 14432555067 0014525 g ustar 00root root 0000000 0000000 52 comment=58b7591d15f3416375a1ec59a983373e6974ff2d
fw-morax.git/ 0000775 0000000 0000000 00000000000 14432555067 0013413 5 ustar 00root root 0000000 0000000 fw-morax.git/.gitignore 0000664 0000000 0000000 00000000421 14432555067 0015400 0 ustar 00root root 0000000 0000000 .idea
*.iml
/*/target/
*.local.*
**/*.settings/
**/.project
**/target/
**/.classpath
**/logs/
**/*.iml
**/.settings
**/*.class
**/*.pyc
**/target
**/.idea
**/*.log
.settings
.project
.classpath
*.class
*.pyc
target
**/.cache/
*.log
/bin/
/*-logs/
**/banner.txt
**/.DS_Store fw-morax.git/.mvn/ 0000775 0000000 0000000 00000000000 14432555067 0014271 5 ustar 00root root 0000000 0000000 fw-morax.git/.mvn/wrapper/ 0000775 0000000 0000000 00000000000 14432555067 0015751 5 ustar 00root root 0000000 0000000 fw-morax.git/.mvn/wrapper/MavenWrapperDownloader.java 0000664 0000000 0000000 00000011527 14432555067 0023250 0 ustar 00root root 0000000 0000000 /*
* Copyright 2007-present the original author or authors.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* https://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
import java.net.*;
import java.io.*;
import java.nio.channels.*;
import java.util.Properties;
public class MavenWrapperDownloader {
private static final String WRAPPER_VERSION = "0.5.6";
/**
* Default URL to download the maven-wrapper.jar from, if no 'downloadUrl' is provided.
*/
private static final String DEFAULT_DOWNLOAD_URL = "https://repo.maven.apache.org/maven2/io/takari/maven-wrapper/"
+ WRAPPER_VERSION + "/maven-wrapper-" + WRAPPER_VERSION + ".jar";
/**
* Path to the maven-wrapper.properties file, which might contain a downloadUrl property to
* use instead of the default one.
*/
private static final String MAVEN_WRAPPER_PROPERTIES_PATH =
".mvn/wrapper/maven-wrapper.properties";
/**
* Path where the maven-wrapper.jar will be saved to.
*/
private static final String MAVEN_WRAPPER_JAR_PATH =
".mvn/wrapper/maven-wrapper.jar";
/**
* Name of the property which should be used to override the default download url for the wrapper.
*/
private static final String PROPERTY_NAME_WRAPPER_URL = "wrapperUrl";
public static void main(String args[]) {
System.out.println("- Downloader started");
File baseDirectory = new File(args[0]);
System.out.println("- Using base directory: " + baseDirectory.getAbsolutePath());
// If the maven-wrapper.properties exists, read it and check if it contains a custom
// wrapperUrl parameter.
File mavenWrapperPropertyFile = new File(baseDirectory, MAVEN_WRAPPER_PROPERTIES_PATH);
String url = DEFAULT_DOWNLOAD_URL;
if (mavenWrapperPropertyFile.exists()) {
FileInputStream mavenWrapperPropertyFileInputStream = null;
try {
mavenWrapperPropertyFileInputStream = new FileInputStream(mavenWrapperPropertyFile);
Properties mavenWrapperProperties = new Properties();
mavenWrapperProperties.load(mavenWrapperPropertyFileInputStream);
url = mavenWrapperProperties.getProperty(PROPERTY_NAME_WRAPPER_URL, url);
} catch (IOException e) {
System.out.println("- ERROR loading '" + MAVEN_WRAPPER_PROPERTIES_PATH + "'");
} finally {
try {
if (mavenWrapperPropertyFileInputStream != null) {
mavenWrapperPropertyFileInputStream.close();
}
} catch (IOException e) {
// Ignore ...
}
}
}
System.out.println("- Downloading from: " + url);
File outputFile = new File(baseDirectory.getAbsolutePath(), MAVEN_WRAPPER_JAR_PATH);
if (!outputFile.getParentFile().exists()) {
if (!outputFile.getParentFile().mkdirs()) {
System.out.println(
"- ERROR creating output directory '" + outputFile.getParentFile().getAbsolutePath() + "'");
}
}
System.out.println("- Downloading to: " + outputFile.getAbsolutePath());
try {
downloadFileFromURL(url, outputFile);
System.out.println("Done");
System.exit(0);
} catch (Throwable e) {
System.out.println("- Error downloading");
e.printStackTrace();
System.exit(1);
}
}
private static void downloadFileFromURL(String urlString, File destination) throws Exception {
if (System.getenv("MVNW_USERNAME") != null && System.getenv("MVNW_PASSWORD") != null) {
String username = System.getenv("MVNW_USERNAME");
char[] password = System.getenv("MVNW_PASSWORD").toCharArray();
Authenticator.setDefault(new Authenticator() {
@Override
protected PasswordAuthentication getPasswordAuthentication() {
return new PasswordAuthentication(username, password);
}
});
}
URL website = new URL(urlString);
ReadableByteChannel rbc;
rbc = Channels.newChannel(website.openStream());
FileOutputStream fos = new FileOutputStream(destination);
fos.getChannel().transferFrom(rbc, 0, Long.MAX_VALUE);
fos.close();
rbc.close();
}
}
fw-morax.git/.mvn/wrapper/maven-wrapper.properties 0000664 0000000 0000000 00000000333 14432555067 0022652 0 ustar 00root root 0000000 0000000 distributionUrl=https://repo.maven.apache.org/maven2/org/apache/maven/apache-maven/3.6.3/apache-maven-3.6.3-bin.zip
wrapperUrl=https://repo.maven.apache.org/maven2/io/takari/maven-wrapper/0.5.6/maven-wrapper-0.5.6.jar
fw-morax.git/README.md 0000664 0000000 0000000 00000000347 14432555067 0014676 0 ustar 00root root 0000000 0000000 # 绩效薪酬系统(fw-morax)
### 简介
绩效薪酬
### 对接文档
### 1. SDK
```xml
cn.fw
fw-morax-sdk
1.0.0
```
fw-morax.git/doc/ 0000775 0000000 0000000 00000000000 14432555067 0014160 5 ustar 00root root 0000000 0000000 fw-morax.git/doc/2022-11-29_report.sql 0000664 0000000 0000000 00000006123 14432555067 0017332 0 ustar 00root root 0000000 0000000 CREATE TABLE `kpi_ratio` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`date` date DEFAULT NULL COMMENT '日期',
`group_id` bigint(20) DEFAULT NULL COMMENT '集团id',
`dimension` varchar(2) COLLATE utf8_bin DEFAULT NULL COMMENT '维度',
`s1` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '人员名称',
`s2` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '门店名称',
`s3` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '岗位名称',
`s4` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '绩效组名称',
`s5` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '绩效排名包名称',
`s6` varchar(512) COLLATE utf8_bin DEFAULT NULL COMMENT '角色编码',
`s7` varchar(512) COLLATE utf8_bin DEFAULT NULL COMMENT '角色名称',
`s9` varchar(512) COLLATE utf8_bin DEFAULT NULL COMMENT '绩效排名组门店',
`i1` int(11) DEFAULT NULL COMMENT '星级',
`i2` int(11) 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',
`l6` bigint(20) DEFAULT NULL COMMENT '绩效池id',
`b1` decimal(10,4) DEFAULT NULL COMMENT '绩效考核周期得分率',
`b2` decimal(10,4) DEFAULT NULL COMMENT '绩效本月得分率',
`b3` decimal(10,4) DEFAULT NULL COMMENT '绩效上月得分率',
`b4` decimal(10,4) DEFAULT NULL COMMENT '绩效前两月得分率',
`b5` tinyint(1) DEFAULT NULL COMMENT '是否纳入绩效考核',
`b6` tinyint(1) DEFAULT NULL COMMENT '是否管理层',
`yn` tinyint(1) DEFAULT NULL COMMENT '逻辑删除',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=DYNAMIC COMMENT='绩效得分率报表';
CREATE TABLE `salary_reward` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`date` date DEFAULT NULL COMMENT '日期',
`dimension` varchar(2) COLLATE utf8_bin DEFAULT NULL COMMENT '维度',
`group_id` bigint(20) DEFAULT NULL COMMENT '集团id',
`s1` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '人员名称',
`s2` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '门店名称',
`s3` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '岗位名称',
`s4` varchar(512) COLLATE utf8_bin DEFAULT NULL COMMENT '角色编码',
`s5` varchar(512) COLLATE utf8_bin DEFAULT NULL COMMENT '角色名称',
`i1` int(11) 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',
`b1` decimal(10,4) DEFAULT NULL COMMENT '总薪酬',
`b2` decimal(10,4) DEFAULT NULL COMMENT '平均薪酬',
`b3` decimal(10,4) DEFAULT NULL COMMENT '岗位收入',
`b4` decimal(10,4) DEFAULT NULL COMMENT '岗位最低收入',
`yn` tinyint(1) DEFAULT NULL COMMENT '逻辑删除',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=DYNAMIC COMMENT='薪酬奖励报表'; fw-morax.git/doc/2023-02-08.sql 0000664 0000000 0000000 00000000254 14432555067 0015734 0 ustar 00root root 0000000 0000000 ALTER TABLE `fw_morax`.`indicators`
ADD COLUMN `data_scope_types` varchar(255) DEFAULT '1' COMMENT '数据范围范围类型; 1. 人员, 2.门店' AFTER `apply_types`;
fw-morax.git/doc/2023-05-18_update.sql 0000664 0000000 0000000 00000001023 14432555067 0017275 0 ustar 00root root 0000000 0000000 ALTER TABLE `fw_morax`.`eval_shop_pool`
CHANGE COLUMN `eval_group_stage_id` `eval_group_rank_stage_id` bigint(20) NOT NULL COMMENT '考评排名阶段id' AFTER `eval_group_rank_id`;
ALTER TABLE `fw_morax`.`eval_user_pool`
CHANGE COLUMN `eval_group_stage_id` `eval_group_rank_stage_id` bigint(20) NOT NULL COMMENT '考评阶段id' AFTER `eval_group_rank_id`;
ALTER TABLE `fw_morax`.`eval_group_user`
ADD COLUMN `eval_group_rank_stage_id` bigint(20) NOT NULL COMMENT '考评排名组阶段id' AFTER `eval_group_rank_id`;
fw-morax.git/doc/2023_update.sql 0000664 0000000 0000000 00000123273 14432555067 0016641 0 ustar 00root root 0000000 0000000
-- 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
-- 同步指标 考评得分率、绩效得分率 fw-morax.git/doc/ddl.sql 0000664 0000000 0000000 00000077650 14432555067 0015463 0 ustar 00root root 0000000 0000000 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`;
fw-morax.git/doc/ddl_2022-10-31.sql 0000664 0000000 0000000 00000075723 14432555067 0016566 0 ustar 00root root 0000000 0000000 -- 岗位最低补偿表
CREATE TABLE `income_compensation` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`post_id` bigint(20) NOT NULL COMMENT '岗位id',
`post_name` varchar(255) DEFAULT NULL COMMENT '岗位名称',
`shop_ids` varchar(255) NOT NULL COMMENT '适用门店ids',
`shop_names` varchar(255) NOT NULL COMMENT '适用门店名称',
`months` varchar(255) NOT NULL COMMENT '多个生效月份',
`money` decimal(10,4) DEFAULT NULL COMMENT '单人补偿金额',
`max_money` decimal(10,4) DEFAULT NULL COMMENT '单人最高补偿金额',
`status` tinyint(3) NOT NULL COMMENT '状态;1:审批中 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='收入补偿';
-- 新增SalaryTypeEnum 岗位最低收入补偿,新增计算器计算
-- 业务系统需要监听所有启用中的绩效指标,通过系统id获取到指标,上报所有人的绩效指标数据。考评、薪酬、绩效需要指标数据就到这里搜
-- 数据类型 当选择综合指标(只能相同类型的指标能一起计算)
-- 适用范围类型 用于筛选指标,比如工龄只能在薪酬组中使用,考评组、绩效组中不会展示工龄指标
ALTER TABLE `fw_morax`.`indicators`
ADD COLUMN `data_type` tinyint(3) NOT NULL COMMENT '数据类型;1:台数 2:百分比 3:金额' AFTER `target_type`,
ADD COLUMN `apply_types` varchar(255) NOT NULL COMMENT '适用范围类型;1:绩效组 2:薪酬组 3:考评组' AFTER `data_type`;
-- 保存 当月离职员工、调岗员工 最后在绩效组的日期,用于查询详情
ALTER TABLE `fw_morax`.`kpi_pool`
ADD COLUMN `data_date` date NOT NULL COMMENT '数据日期' AFTER `inclusion`;
CREATE TABLE `kpi_indicator_value` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`user_id` bigint(20) NOT NULL COMMENT '人员id',
`indicator_code` varchar(255) NOT NULL COMMENT '指标编码',
`indicator_name` varchar(255) DEFAULT NULL COMMENT '指标库名称',
`indicator_value` varchar(2000) NOT NULL COMMENT '指标业务值;jons字符串 map类型',
`value_type` tinyint(3) NOT NULL COMMENT '类型;1:台数 2:百分比 3:金额 4:条件值',
`data_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` (`data_date`),
KEY `kpi_indicator_value_user_id_index` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='绩效人员指标业务值';
-- 绩效
-- 1. 绩效组指标:kpi_group_indicator 新增字段:名称 目标计算类型
ALTER TABLE `fw_morax`.`kpi_group_indicator`
DROP COLUMN `indicator_code`,
DROP COLUMN `indicator_name`,
DROP COLUMN `target_type`,
DROP COLUMN `target_value`,
ADD COLUMN `name` varchar(255) NOT NULL COMMENT '指标名称' AFTER `id`;
-- 修改后
CREATE TABLE `kpi_group_indicator` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`kpi_group_id` bigint(20) NOT NULL COMMENT '绩效组配置id',
`name` varchar(255) DEFAULT NULL COMMENT '指标名称',
`base_score` int(11) NOT NULL COMMENT '基础绩效分',
`score_way` tinyint(3) DEFAULT NULL COMMENT '得分方式;1: 阶梯得分 2:普通得分',
`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='绩效组指标项';
-- 2. 绩效组新增关联表 绩效组指标参数(一对多):
CREATE TABLE `kpi_group_indicator_param` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`kpi_group_id` bigint(20) NOT NULL COMMENT '绩效组配置id',
`kpi_group_indicator_id` bigint(20) NOT NULL COMMENT '绩效组指标id',
`cond_type` tinyint(3) NOT NULL COMMENT '条件类型(1.台阶条件,2,提成条件)',
`indicator_code` varchar(255) NOT NULL COMMENT '指标编码',
`indicator_name` varchar(255) DEFAULT NULL COMMENT '指标库名称',
`proportion` decimal(10,4) DEFAULT NULL COMMENT '占比',
`cap` tinyint(1) DEFAULT '1' COMMENT '是否封顶',
`target_calc_type` tinyint(3) DEFAULT NULL COMMENT '目标计算类型;1: 目标值计算 2:最低要求计算',
`target_type` tinyint(3) DEFAULT '1' COMMENT '绩效目标值类型; 1:无 2:百分比 3:金额 4:台数',
`target_value` 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_group_indicator_cond` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`kpi_group_id` bigint(20) NOT NULL COMMENT '绩效组配置id',
`kpi_group_indicator_id` bigint(20) NOT NULL COMMENT '绩效组指标id',
`indicator_code` varchar(255) NOT NULL COMMENT '指标编码',
`indicator_name` varchar(255) DEFAULT NULL COMMENT '指标库名称',
`target_calc_type` tinyint(3) DEFAULT NULL COMMENT '目标计算类型;1: 目标值计算 2:最低要求计算',
`target_type` tinyint(3) DEFAULT '1' COMMENT '绩效目标值类型; 1:无 2:百分比 3:金额 4:台数',
`target_value` decimal(10,4) DEFAULT NULL COMMENT '目标值',
`sort` int(11) NOT 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_group_indicator_cond_ladders` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`kpi_group_indicator_cond_id` bigint(20) NOT NULL COMMENT '绩效组指标条件id',
`lower` decimal(10,4) NOT NULL COMMENT '阶梯下限;大于等于',
`upper` decimal(10,4) NOT NULL DEFAULT '65536.0000' COMMENT '阶梯上限;小于; 无上限的情况值为 2<<15',
`score_percent` 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_group_indicator_target_log` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`kpi_group_indicator_cond_id` bigint(20) DEFAULT NULL COMMENT '绩效组指标条件id',
`kpi_group_indicator_param_id` bigint(20) COMMENT DEFAULT NULL '绩效组指标参数id',
`value` decimal(18,2) DEFAULT NULL COMMENT '原始值',
`reach_value` decimal(18,2) DEFAULT NULL COMMENT '达成目标',
`data_date` date NOT NULL COMMENT '数据日期',
`group_id` bigint(20) NOT NULL COMMENT '集团id',
`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 `kpi_group_indicator_cond_log` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`kpi_group_indicator_cond_id` bigint(20) DEFAULT NULL COMMENT '绩效组指标条件id',
`kpi_group_indicator_cond_ladders_id` bigint(20) DEFAULT NULL COMMENT '命中绩效组指标条件得分阶梯id',
`value` decimal(18,2) DEFAULT NULL COMMENT '值',
`data_date` date NOT NULL COMMENT '数据日期',
`group_id` bigint(20) NOT NULL COMMENT '集团id',
`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 `kpi_group_indicator_log` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`kpi_group_indicator_id` bigint(20) NOT NULL COMMENT '绩效组指标项id',
`score` int(11) NOT 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='绩效组指标项得分记录';
-- 薪酬 各业务系统将各系统所有人的薪酬指标每天凌晨上报,有几种计算方式就上报几种
-- 删除salary_raw_data表
DROP TABLE salary_raw_data;
-- 保存 当月离职员工、调岗员工 最后在绩效组的日期,用于查询详情
ALTER TABLE `fw_morax`.`kpi_pool`
ADD COLUMN `data_date` date NULL COMMENT '数据日期' AFTER `inclusion`;
ALTER TABLE `fw_morax`.`salary_project`
DROP COLUMN `salary_code`,
DROP COLUMN `salary_name`,
ADD COLUMN `name` varchar(255) NOT NULL COMMENT '名称' AFTER `id`;
CREATE TABLE `salary_group_project_param` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`salary_group_id` bigint(20) NOT NULL COMMENT '薪酬组id',
`salary_group_project_id` bigint(20) NOT NULL COMMENT '薪酬组项目id',
`param_type` tinyint(3) NOT NULL COMMENT '参数类型(1.台阶,2,提成)',
`code_type` tinyint(3) NOT NULL COMMENT '编码类型(1.指标,2.综合指标组合)',
`indicator_code` varchar(255) NOT NULL COMMENT '指标编码',
`indicator_name` varchar(255) DEFAULT NULL COMMENT '指标库名称',
`target_type` tinyint(3) DEFAULT '1' COMMENT '目标值类型; 1:无 2:百分比 3:金额 4:台数',
`target_value` decimal(10,4) DEFAULT NULL COMMENT '目标值',
`proportion` decimal(10,4) DEFAULT NULL COMMENT '占比',
`cap` tinyint(1) DEFAULT '1' COMMENT '是否封顶',
`sort` int(11) 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 `salary_group_project_cond` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`salary_group_id` bigint(20) NOT NULL COMMENT '薪酬组id',
`salary_group_project_id` bigint(20) NOT NULL COMMENT '薪酬组项目id',
`code_type` tinyint(3) NOT NULL COMMENT '编码类型(1. 指标,2.综合指标组合,3.薪酬指标)',
`indicator_code` varchar(255) NOT NULL COMMENT '指标编码',
`indicator_name` varchar(255) DEFAULT NULL COMMENT '指标库名称',
`target_type` tinyint(3) DEFAULT '1' COMMENT '条件值类型; 1:无 2:百分比 3:金额 4:台数',
`target_value` decimal(10,4) DEFAULT NULL COMMENT '目标值',
`cond_value` decimal(10,4) DEFAULT NULL COMMENT '条件值',
`sort` int(11) 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 `salary_group_project_target_log` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`salary_group_project_id` bigint(20) NOT NULL COMMENT '薪酬组项目id',
`salary_group_project_cond_id` bigint(20) DEFAULT NULL COMMENT '薪酬组薪酬项目前置条件id',
`salary_group_project_param_id` bigint(20) DEFAULT NULL COMMENT '薪酬组薪酬项目参数id',
`value` decimal(18,2) DEFAULT NULL COMMENT '原始值',
`reach_value` decimal(18,2) DEFAULT NULL COMMENT '达成目标',
`data_date` date NOT NULL COMMENT '数据日期',
`create_time` datetime DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
`yn` tinyint(1) DEFAULT '1',
PRIMARY KEY (`id`),
UNIQUE KEY `salary_project_cond_log_index` (`salary_group_project_id`,`salary_pool_id`,`data_date`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='薪酬指标达成目标记录';
CREATE TABLE `salary_group_project_cond_log` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`salary_group_project_id` bigint(20) NOT NULL COMMENT '薪酬组项目id',
`salary_group_project_cond_id` bigint(20) DEFAULT NULL COMMENT '薪酬组薪酬项目前置条件id',
`value` decimal(18,2) DEFAULT NULL COMMENT '值',
`data_date` date NOT NULL COMMENT '数据日期',
`create_time` datetime DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
`yn` tinyint(1) DEFAULT '1',
PRIMARY KEY (`id`),
UNIQUE KEY `salary_project_cond_log_index` (`salary_group_project_id`,`salary_pool_id`,`data_date`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='薪酬指标条件记录';
CREATE TABLE `salary_group_project_hit_log` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`salary_project_id` bigint(20) NOT NULL COMMENT '薪酬项id',
`salary_pool_id` bigint(20) NOT NULL COMMENT '薪酬池id',
`salary_group_project_settin_id` bigint(20) DEFAULT NULL COMMENT '命中具体配置项id',
`salary_value` decimal(18,2) DEFAULT NULL COMMENT '薪酬值',
`data_date` date NOT NULL COMMENT '数据日期',
`group_id` bigint(20) NOT NULL COMMENT '集团id',
`create_time` datetime DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
`yn` tinyint(1) DEFAULT '1',
PRIMARY KEY (`id`),
UNIQUE KEY `salary_project_hit_log_index` (`salary_project_id`,`salary_pool_id`,`data_date`,`salary_group_project_settin_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2866 DEFAULT CHARSET=utf8mb4 COMMENT='用户薪酬项命中记录';
-- 考评
CREATE TABLE `eval_indicator` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`code` varchar(255) NOT NULL COMMENT '考评指标编码',
`name` varchar(255) DEFAULT NULL COMMENT '考评指标名称',
`sys_id` bigint(20) DEFAULT NULL COMMENT '归属系统id',
`sys_prefix` varchar(64) DEFAULT NULL COMMENT '归属系统前缀',
`sys_name` varchar(32) DEFAULT NULL COMMENT '归属系统名称',
`role_code` varchar(64) DEFAULT NULL COMMENT '导入操作人员角色码',
`role_name` varchar(64) DEFAULT NULL COMMENT '导入操作人员角色名称',
`rule_type` tinyint(3) DEFAULT NULL COMMENT '频率类型; 1:按滚动天 2:按星期 3:按月指定日期 4:按滚动月',
`rule_values` varchar(256) DEFAULT NULL COMMENT '频率值 ,号分隔(按星期、按月指定日期使用)',
`roll_value` int(11) DEFAULT NULL COMMENT '滚动月数/滚动天数',
`start_time` date DEFAULT NULL COMMENT '开始日期',
`month_day_type` tinyint(3) DEFAULT NULL COMMENT '月天; 1:月第几天 2:月倒数第几天',
`month_day` tinyint(3) DEFAULT NULL COMMENT '天',
`target_type` tinyint(3) DEFAULT '1' COMMENT '目标值类型; 1:无 2:百分比 3:金额 4:台数',
`enable` tinyint(1) NOT NULL COMMENT '启用/禁用;0:禁用 1:启用',
`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 `eval_group` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`egc` varchar(60) NOT NULL COMMENT '考评组编码',
`name` varchar(255) DEFAULT NULL COMMENT '考评,活动名称',
`type` tinyint(3) DEFAULT '1' COMMENT '考评类型; 1:活动考评 2:其他考评',
`post_id` bigint(20) NOT NULL COMMENT '岗位id',
`post_name` varchar(255) DEFAULT NULL COMMENT '岗位名称',
`shop_ids` varchar(255) NOT NULL COMMENT '适用门店ids',
`shop_names` varchar(255) NOT NULL COMMENT '适用门店名称',
`reason` varchar(512) DEFAULT NULL COMMENT '调整原因',
`attachment` varchar(512) DEFAULT NULL COMMENT '附件;fid逗号隔开',
`status` tinyint(3) NOT NULL COMMENT '状态;1:审批中 2:审批拒绝 3:生效中 4:草稿 ',
`indicator_num` int(5) 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 `draft` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`type` tinyint(3) DEFAULT '1' COMMENT '类型; 1:考评组 ',
`union_id` bigint(20) NOT NULL COMMENT '关联id',
`content` text COMMENT '内容【json】',
`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='草稿';
-- 排名正负激励 阶梯排名(按固定值、按比例)
-- 阶梯奖励(累加) 阶梯翻倍
-- 总金额X达成率占比 固定金额占比
-- 固定值X达成率 固定金额
-- 固定值+超额部分追加奖励 超额奖励
-- 固定金额占比、固定金额、超额奖励 使用
CREATE TABLE `eval_group_reward` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`name` varchar(255) DEFAULT NULL COMMENT '考评,活动名称',
`cal_method` int(3) NOT NULL COMMENT '计算方式; 1: 排名正负激励 2:阶梯奖励(翻倍) 3:总金额X达成率占比 4:固定值X达成率 5: 固定值+超额部分追加奖励',
`rank_type` tinyint(3) NOT NULL COMMENT '排名方式;1:排名序号 2:排名人数百分比',
`money` decimal(10,4) DEFAULT NULL COMMENT '金额',
`extra_money` decimal(10,4) DEFAULT NULL COMMENT '追加奖励 XX 元/台,超额奖励用',
`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 `eval_group_reward_ladders` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`eval_group_reward_id` bigint(20) NOT NULL COMMENT '绩效组指标条件id',
`lower` decimal(10,4) NOT NULL COMMENT '阶梯下限;大于等于',
`upper` decimal(10,4) NOT NULL DEFAULT '65536.0000' COMMENT '阶梯上限;小于; 无上限的情况值为 2<<15',
`money` decimal(10,4) DEFAULT NULL COMMENT '金额',
`cap_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 `eval_group_reward_param` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`eval_group_id` bigint(20) NOT NULL COMMENT '考评组id',
`eval_group_reward_id` bigint(20) NOT NULL COMMENT '考评奖惩id',
`param_type` tinyint(3) NOT NULL COMMENT '参数类型(1.台阶,2,提成)',
`code_type` tinyint(3) NOT NULL COMMENT '编码类型(1.指标,2.考评指标)',
`code` varchar(255) NOT NULL COMMENT '项目编码',
`name` varchar(255) DEFAULT NULL COMMENT '项目名称',
`proportion` decimal(10,4) DEFAULT NULL COMMENT '占比',
`cap` tinyint(1) DEFAULT '1' COMMENT '是否封顶',
`target_type` tinyint(3) DEFAULT '1' COMMENT '目标值类型; 1:无 2:百分比 3:金额 4:台数',
`target_value` decimal(10,4) DEFAULT NULL COMMENT '目标值',
`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 `eval_group_reward_cond` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`eval_group_id` bigint(20) NOT NULL COMMENT '考评组id',
`eval_group_reward_id` bigint(20) NOT NULL COMMENT '考评奖惩id',
`cond_type` tinyint(3) DEFAULT '1' COMMENT '条件值类型; 1:满足目标 2:满足排名率 3:金额',
`code_type` tinyint(3) NOT NULL COMMENT '编码类型(1. 指标,2.考评指标)',
`code` varchar(255) NOT NULL COMMENT '项目编码',
`name` varchar(255) DEFAULT NULL COMMENT '项目名称',
`target_type` tinyint(3) DEFAULT '1' COMMENT '条件值类型; 1:无 2:百分比 3:金额',
`target_value` decimal(10,4) DEFAULT NULL COMMENT '目标值',
`cond_value` decimal(10,4) DEFAULT NULL COMMENT '条件值',
`sort` int(11) 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 `eval_group_indicator` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`eval_group_id` bigint(20) NOT NULL COMMENT '考评组配置id',
`name` varchar(255) DEFAULT NULL COMMENT '指标名称',
`base_score` int(11) NOT NULL COMMENT '基础绩效分',
`score_way` tinyint(3) DEFAULT NULL COMMENT '得分方式;1: 阶梯得分 2:普通得分',
`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='绩效组指标项';
-- 2. 绩效组新增关联表 绩效组指标参数(一对多):
CREATE TABLE `eval_group_indicator_param` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`eval_group_id` bigint(20) NOT NULL COMMENT '考评组配置id',
`eval_group_indicator_id` bigint(20) NOT NULL COMMENT '考评组指标id',
`cond_type` tinyint(3) NOT NULL COMMENT '条件类型(1.台阶条件,2,提成条件)',
`code_type` tinyint(3) NOT NULL COMMENT '指标类型(1.指标,2,考评指标)',
`code` varchar(255) NOT NULL COMMENT '指标编码',
`name` varchar(255) DEFAULT NULL COMMENT '指标库名称',
`proportion` decimal(10,4) DEFAULT NULL COMMENT '占比',
`cap` tinyint(1) DEFAULT '1' COMMENT '是否封顶',
`target_calc_type` tinyint(3) DEFAULT NULL COMMENT '目标计算类型;1: 目标值计算 2:最低要求计算',
`target_type` tinyint(3) DEFAULT '1' COMMENT '绩效目标值类型; 1:无 2:百分比 3:金额 4:台数',
`target_value` 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 `eval_group_indicator_cond` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`eval_group_id` bigint(20) NOT NULL COMMENT '考评组配置id',
`eval_group_indicator_id` bigint(20) NOT NULL COMMENT '考评组指标id',
`indicator_code` varchar(255) NOT NULL COMMENT '指标编码',
`indicator_name` varchar(255) DEFAULT NULL COMMENT '指标库名称',
`target_calc_type` tinyint(3) DEFAULT NULL COMMENT '目标计算类型;1: 目标值计算 2:最低要求计算',
`target_type` tinyint(3) DEFAULT '1' COMMENT '绩效目标值类型; 1:无 2:百分比 3:金额 4:台数',
`target_value` decimal(10,4) DEFAULT NULL COMMENT '目标值',
`sort` int(11) NOT 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 `eval_group_indicator_cond_ladders` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`eval_group_indicator_cond_id` bigint(20) NOT NULL COMMENT '考评组指标条件id',
`lower` decimal(10,4) NOT NULL COMMENT '阶梯下限;大于等于',
`upper` decimal(10,4) NOT NULL DEFAULT '65536.0000' COMMENT '阶梯上限;小于; 无上限的情况值为 2<<15',
`score_percent` 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 `eval_group_indicator_target_log` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`eval_group_id` bigint(20) NOT NULL COMMENT '考评组配置id',
`eval_group_indicator_param_id` bigint(20) NOT NULL COMMENT '考评组指标参数id',
`eval_group_indicator_cond_id` bigint(20) NOT NULL COMMENT '考评组指标条件id',
`value` decimal(18,2) DEFAULT NULL COMMENT '原始值',
`reach_value` decimal(18,2) DEFAULT NULL COMMENT '达成目标',
`data_date` date NOT NULL COMMENT '数据日期',
`group_id` bigint(20) NOT NULL COMMENT '集团id',
`create_time` datetime DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
`yn` tinyint(1) DEFAULT '1',
PRIMARY KEY (`id`),
UNIQUE KEY `salary_project_hit_log_index` (`kpi_group_indicator_id`,`kpi_pool_id`,`data_date`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='考评指标达成目标记录';
CREATE TABLE `eval_group_indicator_cond_log` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`eval_group_id` bigint(20) NOT NULL COMMENT '考评组配置id',
`eval_group_indicator_cond_id` bigint(20) NOT NULL COMMENT '考评组指标条件id',
`eval_group_indicator_cond_ladders_id` bigint(20) NOT NULL COMMENT '考评组指标条件阶梯id',
`value` decimal(18,2) DEFAULT NULL COMMENT '原始值',
`data_date` date NOT NULL COMMENT '数据日期',
`group_id` bigint(20) NOT NULL COMMENT '集团id',
`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 `eval_group_reward_cond_log` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`eval_group_id` bigint(20) NOT NULL COMMENT '考评组配置id',
`eval_group_reward_id` bigint(20) NOT NULL COMMENT '考评组奖惩id',
`eval_group_reward_cond_id` bigint(20) NOT NULL COMMENT '考评组奖惩条件id',
`value` decimal(18,2) DEFAULT NULL COMMENT '原始值',
`reach_value` decimal(18,2) DEFAULT NULL COMMENT '达成目标',
`data_date` date NOT NULL COMMENT '数据日期',
`group_id` bigint(20) NOT NULL COMMENT '集团id',
`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 `eval_pool` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`egc` varchar(60) NOT NULL COMMENT '考评组编码',
`eval_group_id` bigint(20) NOT NULL COMMENT '考评组id',
`user_id` bigint(20) NOT NULL COMMENT '用户id',
`user_name` varchar(255) DEFAULT NULL COMMENT '用户名称',
`post_id` bigint(20) NOT NULL COMMENT '岗位id',
`post_name` varchar(255) DEFAULT NULL COMMENT '岗位名称',
`shop_id` bigint(20) NOT NULL COMMENT '门店id',
`shop_name` varchar(255) DEFAULT NULL COMMENT '门店名称',
`fund_shop_id` bigint(20) DEFAULT NULL COMMENT '社保公积金门店id',
`fund_shop_name` varchar(255) DEFAULT NULL COMMENT '社保公积金门店名称',
`reward` decimal(18,2) NOT NULL COMMENT '考评奖惩',
`score` decimal(10,2) NOT NULL COMMENT '考评得分',
`score_ratio` decimal(10,2) NOT NULL COMMENT '考评得分率',
`monthly` varchar(8) NOT NULL COMMENT '月度',
`regular` tinyint(1) NOT 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 `eval_pool_monthly_index` (`monthly`),
KEY `eval_pool_salary_group_id_index` (`eval_group_id`),
KEY `eval_pool_user_id_index` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='考评池';
CREATE TABLE `eval_pool_indicator_detail` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`eval_pool_id` bigint(20) NOT NULL COMMENT '考评池id',
`eval_group_indicator_id` bigint(20) DEFAULT NULL COMMENT '考评项id',
`name` varchar(255) DEFAULT NULL COMMENT '指标名称',
`base_score` int(11) NOT NULL COMMENT '基础绩效分',
`score` decimal(10,2) NOT NULL COMMENT '考评得分',
`score_ratio` decimal(10,2) NOT NULL COMMENT '考评得分率',
`monthly` varchar(8) NOT 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=3804686 DEFAULT CHARSET=utf8mb4 COMMENT='考评指标明细';
CREATE TABLE `eval_pool_reward_detail` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`eval_pool_id` bigint(20) NOT NULL COMMENT '考评池id',
`eval_group_reward_id` bigint(20) DEFAULT NULL COMMENT '考评项id',
`name` varchar(255) DEFAULT NULL COMMENT '指标名称',
`money` decimal(10,4) DEFAULT NULL COMMENT '金额',
`monthly` varchar(8) NOT 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=3804686 DEFAULT CHARSET=utf8mb4 COMMENT='考评奖惩明细';
fw-morax.git/doc/fw_morax_v2.sql 0000664 0000000 0000000 00000235117 14432555067 0017143 0 ustar 00root root 0000000 0000000 SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for approval_record
-- 8.0 utf8mb4_0900_ai_ci
-- 5.7 utf8mb4_general_ci
-- ----------------------------
DROP TABLE IF EXISTS `approval_record`;
CREATE TABLE `approval_record`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`data_id` bigint NOT NULL COMMENT '被审批业务表主键',
`approval_type` int NOT NULL COMMENT '审批业务类型;1:绩效配置 2:薪酬配置 3:绩效星级申诉',
`approval_no` varchar(255) NOT NULL COMMENT '审批单号',
`approval_status` int NOT NULL COMMENT '审批状态;1:审批中 2:审批通过 3:审批拒绝',
`group_id` bigint 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 `approval_record_approval_no_index` (`approval_no`)
) ENGINE = InnoDB
AUTO_INCREMENT = 63
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='审批记录';
-- ----------------------------
-- Table structure for eval
-- ----------------------------
DROP TABLE IF EXISTS `eval`;
CREATE TABLE `eval`
(
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`name` varchar(255) DEFAULT NULL COMMENT '考评,活动名称',
`type` tinyint(4) NULL DEFAULT 1 COMMENT '考评类型; 1:活动考评 2:其他考评',
`status` tinyint(4) NOT NULL COMMENT '状态;1:审批中 2:审批拒绝 3:生效中 4:草稿 ',
`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
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='考评';
-- ----------------------------
-- Table structure for eval_group
-- ----------------------------
DROP TABLE IF EXISTS `eval_group`;
CREATE TABLE `eval_group`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`eval_id` bigint NOT NULL COMMENT '考评id',
`egc` varchar(60) NOT NULL COMMENT '考评组编码',
`name` varchar(255) DEFAULT NULL COMMENT '考评,活动组名称',
`type` tinyint DEFAULT '1' COMMENT '考评类型; 1:活动考评 2:其他考评',
`post_id` bigint NOT NULL COMMENT '岗位id',
`post_name` varchar(255) DEFAULT NULL COMMENT '岗位名称',
`shop_ids` varchar(255) NOT NULL COMMENT '适用门店ids',
`shop_names` varchar(255) NOT NULL COMMENT '适用门店名称',
`reason` varchar(512) DEFAULT NULL COMMENT '调整原因',
`attachment` varchar(512) DEFAULT NULL COMMENT '附件;fid逗号隔开',
`indicator_num` int DEFAULT NULL COMMENT '包含指标数量',
`begin_time` date DEFAULT NULL COMMENT '生效时间',
`over_time` date DEFAULT NULL COMMENT '结束时间',
`group_id` bigint 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
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='考评组';
-- ----------------------------
-- Table structure for eval_group_indicator
-- ----------------------------
DROP TABLE IF EXISTS `eval_group_indicator`;
CREATE TABLE `eval_group_indicator`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`eval_group_id` bigint NOT NULL COMMENT '考评组配置id',
`name` varchar(255) DEFAULT NULL COMMENT '指标名称',
`base_score` int NOT NULL COMMENT '基础绩效分',
`score_way` tinyint DEFAULT NULL COMMENT '得分方式;1: 阶梯得分 2:普通得分',
`param_alias` varchar(255) NOT NULL COMMENT '考评项别名',
`precondition_alias` varchar(255) 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
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='绩效组指标项';
-- ----------------------------
-- Table structure for eval_group_indicator_param
-- ----------------------------
DROP TABLE IF EXISTS `eval_group_indicator_param`;
CREATE TABLE `eval_group_indicator_param`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`eval_group_id` bigint NOT NULL COMMENT '考评组配置id',
`eval_group_indicator_id` bigint NOT NULL COMMENT '考评组指标id',
`param_type` tinyint NOT NULL COMMENT '条件类型(1.台阶条件,2,提成条件)',
`code_type` tinyint NOT NULL COMMENT '指标类型(1.指标,2,考评指标)',
`code` varchar(255) NOT NULL COMMENT '指标编码',
`name` varchar(255) DEFAULT NULL COMMENT '指标库名称',
`proportion` decimal(10, 4) DEFAULT NULL COMMENT '占比',
`cap` tinyint(1) DEFAULT '1' COMMENT '是否封顶',
`target_calc_type` tinyint(4) DEFAULT NULL COMMENT '目标计算类型;1: 目标值计算 2:最低要求计算',
`target_type` tinyint(4) DEFAULT '1' COMMENT '绩效目标值类型; 1:无 2:百分比 3:金额 4:台数',
`target_value` 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
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='考评组指标参数';
-- ----------------------------
-- Table structure for eval_group_indicator_precondition
-- ----------------------------
DROP TABLE IF EXISTS `eval_group_indicator_precondition`;
CREATE TABLE `eval_group_indicator_precondition`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`eval_group_id` bigint NOT NULL COMMENT '考评组配置id',
`eval_group_indicator_id` bigint NOT NULL COMMENT '考评组指标id',
`code_type` tinyint(4) NOT NULL COMMENT '编码类型(1. 指标,2.考评指标)',
`code` varchar(255) NOT NULL COMMENT '指标编码',
`name` varchar(255) DEFAULT NULL COMMENT '指标库名称',
`target_calc_type` tinyint(4) DEFAULT NULL COMMENT '目标计算类型;1: 目标值计算 2:最低要求计算',
`target_type` tinyint(4) DEFAULT '1' COMMENT '绩效目标值类型; 1:无 2:百分比 3:金额 4:台数',
`target_value` decimal(10, 4) DEFAULT NULL COMMENT '目标值',
`sort` int NOT 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
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='考评组指标条件';
-- ----------------------------
-- Table structure for eval_group_indicator_ladders
-- ----------------------------
DROP TABLE IF EXISTS `eval_group_indicator_ladders`;
CREATE TABLE `eval_group_indicator_ladders`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`eval_group_indicator_id` bigint NOT NULL COMMENT '考评组指标id',
`lower` decimal(10, 4) NOT NULL COMMENT '阶梯下限;大于等于',
`upper` decimal(10, 4) NOT NULL DEFAULT '65536.0000' COMMENT '阶梯上限;小于; 无上限的情况值为 2<<15',
`standard_score` tinyint NOT 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
COLLATE = utf8mb4_0900_ai_ci COMMENT ='考评组指标得分阶梯';
-- ----------------------------
-- Table structure for eval_group_indicator_precondition_hit_log
-- ----------------------------
DROP TABLE IF EXISTS `eval_group_indicator_precondition_hit_log`;
CREATE TABLE `eval_group_indicator_precondition_hit_log`
(
`id` bigint NOT NULL AUTO_INCREMENT,
`precondition_id` bigint NOT NULL COMMENT '考评组指标条件id',
`precondition_ladders_id` bigint NOT NULL COMMENT '考评组指标条件阶梯id',
`value` decimal(18, 2) DEFAULT NULL COMMENT '原始值',
`data_date` date NOT NULL COMMENT '数据日期',
`group_id` bigint NOT NULL COMMENT '集团id',
`create_time` datetime DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
`yn` tinyint(1) DEFAULT '1',
PRIMARY KEY (`id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='考评指标条件记录';
-- ----------------------------
-- Table structure for eval_group_indicator_precondition_ladders
-- ----------------------------
DROP TABLE IF EXISTS `eval_group_indicator_precondition_ladders`;
CREATE TABLE `eval_group_indicator_precondition_ladders`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`precondition_id` bigint NOT NULL COMMENT '考评组指标条件id',
`lower` decimal(10, 4) NOT NULL COMMENT '阶梯下限;大于等于',
`upper` decimal(10, 4) NOT NULL DEFAULT '65536.0000' COMMENT '阶梯上限;小于; 无上限的情况值为 2<<15',
`score_percent` 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
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='考评组指标条件得分阶梯';
-- ----------------------------
-- Table structure for eval_group_indicator_target_hit_log
-- ----------------------------
DROP TABLE IF EXISTS `eval_group_indicator_target_hit_log`;
CREATE TABLE `eval_group_indicator_target_hit_log`
(
`id` bigint NOT NULL AUTO_INCREMENT,
`refer_id` bigint NOT NULL COMMENT '考评组指标/前置条件 id',
`target_type` tinyint NOT NULL COMMENT '类型 1:考评组指标 2:前置条件指标',
`value` decimal(18, 2) DEFAULT NULL COMMENT '原始值',
`reach_value` decimal(18, 2) DEFAULT NULL COMMENT '达成目标',
`data_date` date NOT NULL COMMENT '数据日期',
`group_id` bigint NOT NULL COMMENT '集团id',
`create_time` datetime DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
`yn` tinyint(1) DEFAULT '1',
PRIMARY KEY (`id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='考评指标达成目标记录';
-- ----------------------------
-- Table structure for eval_group_reward
-- ----------------------------
DROP TABLE IF EXISTS `eval_group_reward`;
CREATE TABLE `eval_group_reward`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`eval_group_id` bigint NOT NULL COMMENT '考评组配置id',
`name` varchar(255) DEFAULT NULL COMMENT '考评,活动名称',
`cal_method` int NOT NULL COMMENT '计算方式; 1: 排名正负激励 2:阶梯奖励(翻倍) 3:总金额X达成率占比 4:固定值X达成率 5: 固定值+超额部分追加奖励',
`rank_type` tinyint NOT NULL COMMENT '排名方式;1:排名序号 2:排名人数百分比',
`money` decimal(10, 4) DEFAULT NULL COMMENT '金额',
`extra_money` decimal(10, 4) DEFAULT NULL COMMENT '追加奖励 XX 元/台,超额奖励用',
`yn` tinyint(1) DEFAULT '1' COMMENT '逻辑删除',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='考评奖惩';
-- ----------------------------
-- Table structure for eval_group_reward_ladders
-- ----------------------------
DROP TABLE IF EXISTS `eval_group_reward_ladders`;
CREATE TABLE `eval_group_reward_ladders`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`eval_group_id` bigint NOT NULL COMMENT '考评组配置id',
`eval_group_reward_id` bigint NOT NULL COMMENT '绩效组指标条件id',
`lower` decimal(10, 4) NOT NULL COMMENT '阶梯下限;大于等于',
`upper` decimal(10, 4) NOT NULL DEFAULT '65536.0000' COMMENT '阶梯上限;小于; 无上限的情况值为 2<<15',
`money` decimal(10, 4) DEFAULT NULL COMMENT '金额',
`cap_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
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='考评奖惩阶梯';
-- ----------------------------
-- Table structure for eval_group_reward_param
-- ----------------------------
DROP TABLE IF EXISTS `eval_group_reward_param`;
CREATE TABLE `eval_group_reward_param`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`eval_group_id` bigint NOT NULL COMMENT '考评组id',
`eval_group_reward_id` bigint NOT NULL COMMENT '考评奖惩id',
`param_type` tinyint NOT NULL COMMENT '参数类型(1.台阶,2,提成)',
`code_type` tinyint NOT NULL COMMENT '编码类型(1.指标,2.考评指标)',
`code` varchar(255) NOT NULL COMMENT '项目编码',
`name` varchar(255) DEFAULT NULL COMMENT '项目名称',
`proportion` decimal(10, 4) DEFAULT NULL COMMENT '占比',
`cap` tinyint(1) DEFAULT '1' COMMENT '是否封顶',
`target_calc_type` tinyint(4) DEFAULT NULL COMMENT '目标计算类型;1: 目标值计算 2:最低要求计算',
`target_type` tinyint DEFAULT '1' COMMENT '目标值类型; 1:无 2:百分比 3:金额 4:台数',
`target_value` decimal(10, 4) DEFAULT NULL COMMENT '目标值',
`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
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='考评奖惩参数';
-- ----------------------------
-- Table structure for eval_group_reward_precondition
-- ----------------------------
DROP TABLE IF EXISTS `eval_group_reward_precondition`;
CREATE TABLE `eval_group_reward_precondition`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`eval_group_id` bigint NOT NULL COMMENT '考评组id',
`eval_group_reward_id` bigint NOT NULL COMMENT '考评奖惩id',
`cond_type` tinyint DEFAULT '1' COMMENT '条件值类型; 1:满足目标 2:满足排名率 3:金额',
`code_type` tinyint NOT NULL COMMENT '编码类型(1. 指标,2.考评指标)',
`code` varchar(255) NOT NULL COMMENT '项目编码',
`name` varchar(255) DEFAULT NULL COMMENT '项目名称',
`target_type` tinyint DEFAULT '1' COMMENT '条件值类型; 1:无 2:百分比 3:金额',
`target_value` decimal(10, 4) DEFAULT NULL COMMENT '目标值',
`cond_value` decimal(10, 4) DEFAULT NULL COMMENT '条件值',
`sort` int 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
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='考评奖惩前置条件';
-- ----------------------------
-- Table structure for eval_group_reward_precondition_hit_log
-- ----------------------------
DROP TABLE IF EXISTS `eval_group_reward_precondition_hit_log`;
CREATE TABLE `eval_group_reward_precondition_hit_log`
(
`id` bigint NOT NULL AUTO_INCREMENT,
`eval_group_id` bigint NOT NULL COMMENT '考评组配置id',
`eval_group_reward_id` bigint NOT NULL COMMENT '考评组奖惩id',
`precondition_id` bigint NOT NULL COMMENT '考评组奖惩条件id',
`value` decimal(18, 2) DEFAULT NULL COMMENT '原始值',
`reach_value` decimal(18, 2) DEFAULT NULL COMMENT '达成目标',
`data_date` date NOT NULL COMMENT '数据日期',
`group_id` bigint NOT NULL COMMENT '集团id',
`create_time` datetime DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
`yn` tinyint(1) DEFAULT '1',
PRIMARY KEY (`id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='考评奖惩条件记录';
-- ----------------------------
-- Table structure for eval_indicator
-- ----------------------------
DROP TABLE IF EXISTS `eval_indicator`;
CREATE TABLE `eval_indicator`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`code` varchar(255) NOT NULL COMMENT '考评指标编码',
`name` varchar(255) DEFAULT NULL COMMENT '考评指标名称',
`sys_id` bigint DEFAULT NULL COMMENT '归属系统id',
`sys_prefix` varchar(64) DEFAULT NULL COMMENT '归属系统前缀',
`sys_name` varchar(32) DEFAULT NULL COMMENT '归属系统名称',
`role_code` varchar(64) DEFAULT NULL COMMENT '导入操作人员角色码',
`role_name` varchar(64) DEFAULT NULL COMMENT '导入操作人员角色名称',
`rule_type` tinyint DEFAULT NULL COMMENT '频率类型; 1:按滚动天 2:按星期 3:按月指定日期 4:按滚动月',
`rule_values` varchar(256) DEFAULT NULL COMMENT '频率值 ,号分隔(按星期、按月指定日期使用)',
`order_type` tinyint DEFAULT NULL COMMENT '按滚动月计算顺序; 1:正序 2:倒序',
`start_time` date DEFAULT NULL COMMENT '开始日期',
`target_type` tinyint DEFAULT '1' COMMENT '目标值类型; 1:无 2:百分比 3:金额 4:台数',
`enable` tinyint(1) NOT NULL COMMENT '启用/禁用;0:禁用 1:启用',
`yn` tinyint(1) DEFAULT '1' COMMENT '逻辑删除',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='考评指标';
-- ----------------------------
-- Table structure for eval_pool
-- ----------------------------
DROP TABLE IF EXISTS `eval_pool`;
CREATE TABLE `eval_pool`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`egc` varchar(60) NOT NULL COMMENT '考评组编码',
`eval_group_id` bigint NOT NULL COMMENT '考评组id',
`user_id` bigint NOT NULL COMMENT '用户id',
`user_name` varchar(255) DEFAULT NULL COMMENT '用户名称',
`post_id` bigint NOT NULL COMMENT '岗位id',
`post_name` varchar(255) DEFAULT NULL COMMENT '岗位名称',
`shop_id` bigint NOT NULL COMMENT '门店id',
`shop_name` varchar(255) DEFAULT NULL COMMENT '门店名称',
`fund_shop_id` bigint DEFAULT NULL COMMENT '社保公积金门店id',
`fund_shop_name` varchar(255) DEFAULT NULL COMMENT '社保公积金门店名称',
`reward` decimal(18, 2) NOT NULL COMMENT '考评奖惩',
`score` decimal(10, 2) NOT NULL COMMENT '考评得分',
`score_ratio` decimal(10, 2) NOT NULL COMMENT '考评得分率',
`monthly` varchar(8) NOT NULL COMMENT '月度',
`regular` tinyint(1) NOT NULL COMMENT '固定的',
`group_id` bigint 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 `eval_pool_monthly_index` (`monthly`),
KEY `eval_pool_salary_group_id_index` (`eval_group_id`),
KEY `eval_pool_user_id_index` (`user_id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='考评池';
-- ----------------------------
-- Table structure for eval_pool_indicator_detail
-- ----------------------------
DROP TABLE IF EXISTS `eval_pool_indicator_detail`;
CREATE TABLE `eval_pool_indicator_detail`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`eval_pool_id` bigint NOT NULL COMMENT '考评池id',
`eval_group_indicator_id` bigint DEFAULT NULL COMMENT '考评项id',
`name` varchar(255) DEFAULT NULL COMMENT '指标名称',
`base_score` int NOT NULL COMMENT '基础绩效分',
`score` decimal(10, 2) NOT NULL COMMENT '考评得分',
`score_ratio` decimal(10, 2) NOT NULL COMMENT '考评得分率',
`monthly` varchar(8) NOT NULL COMMENT '月度',
`group_id` bigint 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 = 3804686
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='考评指标明细';
-- ----------------------------
-- Table structure for income_compensation
-- ----------------------------
DROP TABLE IF EXISTS `income_compensation`;
CREATE TABLE `income_compensation`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`post_id` bigint NOT NULL COMMENT '岗位id',
`post_name` varchar(255) DEFAULT NULL COMMENT '岗位名称',
`shop_ids` varchar(255) NOT NULL COMMENT '适用门店ids',
`shop_names` varchar(255) NOT NULL COMMENT '适用门店名称',
`months` varchar(255) NOT NULL COMMENT '多个生效月份',
`money` decimal(10, 4) DEFAULT NULL COMMENT '单人补偿金额',
`max_money` decimal(10, 4) DEFAULT NULL COMMENT '单人最高补偿金额',
`status` tinyint NOT NULL COMMENT '状态;1:审批中 3:生效中 4:已失效 ',
`group_id` bigint NOT NULL COMMENT '集团id',
`yn` tinyint(1) DEFAULT '1' COMMENT '逻辑删除',
`create_by` bigint DEFAULT NULL COMMENT '创建人',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_by` bigint DEFAULT NULL COMMENT '更新人',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='收入补偿配置';
-- ----------------------------
-- Table structure for income_compensation_list
-- ----------------------------
DROP TABLE IF EXISTS `income_compensation_list`;
CREATE TABLE `income_compensation_list`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`income_compensation_id` bigint NOT NULL COMMENT '补偿id',
`post_id` bigint NOT NULL COMMENT '岗位id',
`shop_id` bigint NOT NULL COMMENT '门店id',
`data_month` varchar(255) NOT NULL COMMENT '生效月份',
`effective` tinyint(1) NOT NULL COMMENT '有效性;0:生效 1:失效 ',
`ineffective_date` date 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
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='收入补偿清单';
-- ----------------------------
-- Table structure for indicators
-- ----------------------------
DROP TABLE IF EXISTS `indicators`;
CREATE TABLE `indicators`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`indicator_code` varchar(32) NOT NULL COMMENT '指标编号',
`indicator_name` varchar(255) NOT NULL COMMENT '指标名称',
`sys_id` bigint NOT NULL COMMENT '归属系统id',
`sys_prefix` varchar(64) DEFAULT NULL COMMENT '归属系统前缀',
`sys_name` varchar(32) DEFAULT NULL COMMENT '归属系统名称',
`role_type` tinyint NOT NULL COMMENT '适用角色类型;1: 全部角色 2:全部管理角色 3:自定义',
`role_codes` varchar(1024) DEFAULT NULL COMMENT '适用角色码',
`role_names` varchar(2048) DEFAULT NULL COMMENT '适用角色名称',
`enable` tinyint(1) NOT NULL COMMENT '启用/禁用;0:禁用 1:启用',
`target_type` tinyint NOT NULL DEFAULT '1' COMMENT '绩效目标值类型; 1:无 2:百分比 3:金额',
`data_type` tinyint NOT NULL COMMENT '数据类型;1:台数 2:百分比 3:金额',
`apply_types` varchar(255) NOT NULL COMMENT '适用范围类型;1:绩效组 2:薪酬组 3:考评组',
`inner_indicator` tinyint(1) DEFAULT '0' COMMENT '内部指标',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
`yn` tinyint(1) DEFAULT '1' COMMENT '逻辑删除',
PRIMARY KEY (`id`),
UNIQUE KEY `idx_indicator_code` (`indicator_code`)
) ENGINE = InnoDB
AUTO_INCREMENT = 40
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='指标库';
-- ----------------------------
-- Table structure for kpi_group
-- ----------------------------
DROP TABLE IF EXISTS `kpi_group`;
CREATE TABLE `kpi_group`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`name` varchar(255) DEFAULT NULL COMMENT '绩效组名称',
`kgc` varchar(60) NOT NULL COMMENT '绩效组编码',
`post_id` bigint NOT NULL COMMENT '岗位id',
`post_name` varchar(255) DEFAULT NULL COMMENT '岗位名称',
`shop_ids` varchar(255) NOT NULL COMMENT '适用门店ids',
`shop_names` varchar(255) NOT NULL COMMENT '适用门店名称',
`pre_id` bigint DEFAULT NULL COMMENT '前一次配置id',
`begin_time` date NOT NULL COMMENT '生效时间',
`over_time` date DEFAULT NULL COMMENT '失效时间',
`reason` varchar(512) DEFAULT NULL COMMENT '调整原因',
`attachment` varchar(512) DEFAULT NULL COMMENT '附件;fid逗号隔开',
`status` tinyint NOT NULL COMMENT '状态;1:审批中 2:待生效 3:生效中 4:已失效',
`indicator_num` int DEFAULT NULL COMMENT '包含指标数量',
`star_evaluation_type` tinyint NOT NULL COMMENT '星级评定方式;1:绩效得分率 2:人员百分比',
`revoked_score_ratio` decimal(10, 4) DEFAULT NULL COMMENT '人员百分比,撤销D级得分率',
`group_id` bigint NOT NULL COMMENT '集团id',
`yn` tinyint(1) DEFAULT '1' COMMENT '逻辑删除',
`create_by` bigint DEFAULT NULL COMMENT '创建人',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_by` bigint DEFAULT NULL COMMENT '更新人',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE = InnoDB
AUTO_INCREMENT = 78
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='绩效组配置';
-- ----------------------------
-- Table structure for kpi_group_indicator
-- ----------------------------
DROP TABLE IF EXISTS `kpi_group_indicator`;
CREATE TABLE `kpi_group_indicator`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`kpi_group_id` bigint NOT NULL COMMENT '绩效组配置id',
`name` varchar(255) DEFAULT NULL COMMENT '指标名称',
`base_score` int NOT NULL COMMENT '基础绩效分',
`score_way` tinyint DEFAULT NULL COMMENT '得分方式;1: 阶梯得分 2:普通得分',
`param_alias` varchar(255) NOT NULL COMMENT '考核项别名',
`precondition_alias` varchar(255) 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
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='绩效组指标项';
-- ----------------------------
-- Table structure for kpi_group_indicator_hit_log
-- ----------------------------
DROP TABLE IF EXISTS `kpi_group_indicator_hit_log`;
CREATE TABLE `kpi_group_indicator_hit_log`
(
`id` bigint NOT NULL AUTO_INCREMENT,
`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) DEFAULT NULL COMMENT '绩效值',
`data_date` date NOT NULL COMMENT '数据日期',
`group_id` bigint NOT NULL COMMENT '集团id',
`create_time` datetime DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
`yn` tinyint(1) DEFAULT '1',
PRIMARY KEY (`id`),
UNIQUE KEY `salary_project_hit_log_index` (`kpi_group_indicator_id`, `kpi_pool_id`, `data_date`)
) ENGINE = InnoDB
AUTO_INCREMENT = 1129
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='用户绩效项命中记录';
-- ----------------------------
-- Table structure for kpi_group_indicator_ladders
-- ----------------------------
DROP TABLE IF EXISTS `kpi_group_indicator_ladders`;
CREATE TABLE `kpi_group_indicator_ladders`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`kpi_group_indicator_id` bigint NOT NULL COMMENT '绩效组指标项id',
`lower` decimal(10, 4) NOT NULL COMMENT '阶梯下限;大于等于',
`upper` decimal(10, 4) NOT NULL DEFAULT '65536.0000' COMMENT '阶梯上限;小于; 无上限的情况值为 2<<15',
`standard_score` tinyint NOT 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 = 271
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='指标项得分阶梯';
-- ----------------------------
-- Table structure for kpi_group_indicator_param
-- ----------------------------
DROP TABLE IF EXISTS `kpi_group_indicator_param`;
CREATE TABLE `kpi_group_indicator_param`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`kpi_group_id` bigint NOT NULL COMMENT '绩效组配置id',
`kpi_group_indicator_id` bigint NOT NULL COMMENT '绩效组指标id',
`param_type` tinyint NOT NULL COMMENT '参数类型(1.台阶条件,2,提成条件)',
`indicator_code` varchar(255) NOT NULL COMMENT '指标编码',
`indicator_name` varchar(255) DEFAULT NULL COMMENT '指标库名称',
`proportion` decimal(10, 4) DEFAULT NULL COMMENT '占比',
`cap` tinyint(1) DEFAULT '1' COMMENT '是否封顶',
`target_calc_type` tinyint(4) DEFAULT NULL COMMENT '目标计算类型;1: 目标值计算 2:最低要求计算',
`target_type` tinyint(4) DEFAULT '1' COMMENT '绩效目标值类型; 1:无 2:百分比 3:金额 4:台数',
`target_value` 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
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='绩效组指标参数';
-- ----------------------------
-- Table structure for kpi_group_indicator_precondition
-- ----------------------------
DROP TABLE IF EXISTS `kpi_group_indicator_precondition`;
CREATE TABLE `kpi_group_indicator_precondition`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`kpi_group_id` bigint NOT NULL COMMENT '绩效组配置id',
`kpi_group_indicator_id` bigint NOT NULL COMMENT '绩效组指标id',
`indicator_code` varchar(255) NOT NULL COMMENT '指标编码',
`indicator_name` varchar(255) DEFAULT NULL COMMENT '指标库名称',
`target_calc_type` tinyint(4) DEFAULT NULL COMMENT '目标计算类型;1: 目标值计算 2:最低要求计算',
`target_type` tinyint(4) DEFAULT '1' COMMENT '绩效目标值类型; 1:无 2:百分比 3:金额 4:台数',
`target_value` decimal(10, 4) DEFAULT NULL COMMENT '目标值',
`sort` int NOT 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
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='绩效组指标条件';
-- ----------------------------
-- Table structure for kpi_group_indicator_precondition_hit_log
-- ----------------------------
DROP TABLE IF EXISTS `kpi_group_indicator_precondition_hit_log`;
CREATE TABLE `kpi_group_indicator_precondition_hit_log`
(
`id` bigint NOT NULL AUTO_INCREMENT,
`precondition_id` bigint DEFAULT NULL COMMENT '绩效组指标条件id',
`precondition_ladders_id` bigint DEFAULT NULL COMMENT '命中绩效组指标条件得分阶梯id',
`value` decimal(18, 2) DEFAULT NULL COMMENT '值',
`data_date` date NOT NULL COMMENT '数据日期',
`group_id` bigint NOT NULL COMMENT '集团id',
`create_time` datetime DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
`yn` tinyint(1) DEFAULT '1',
PRIMARY KEY (`id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='绩效指标前置条件命中条件记录';
-- ----------------------------
-- Table structure for kpi_group_indicator_precondition_ladders
-- ----------------------------
DROP TABLE IF EXISTS `kpi_group_indicator_precondition_ladders`;
CREATE TABLE `kpi_group_indicator_precondition_ladders`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`precondition_id` bigint NOT NULL COMMENT '绩效组指标条件id',
`lower` decimal(10, 4) NOT NULL COMMENT '阶梯下限;大于等于',
`upper` decimal(10, 4) NOT NULL DEFAULT '65536.0000' COMMENT '阶梯上限;小于; 无上限的情况值为 2<<15',
`score_percent` 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
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='绩效组指标条件得分阶梯';
-- ----------------------------
-- Table structure for kpi_group_indicator_score_log
-- ----------------------------
DROP TABLE IF EXISTS `kpi_group_indicator_score_log`;
CREATE TABLE `kpi_group_indicator_score_log`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`kpi_group_indicator_id` bigint NOT NULL COMMENT '绩效组指标项id',
`score` decimal(18,3) NOT NULL COMMENT '得分',
`data_date` date NOT 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
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='绩效组指标项得分记录';
-- ----------------------------
-- Table structure for kpi_group_indicator_target_hit_log
-- ----------------------------
DROP TABLE IF EXISTS `kpi_group_indicator_target_hit_log`;
CREATE TABLE `kpi_group_indicator_target_hit_log`
(
`id` bigint NOT NULL AUTO_INCREMENT,
`refer_id` bigint NOT NULL COMMENT '绩效组指标/前置条件配置id',
`target_type` tinyint NOT NULL COMMENT '类型 1:绩效组指标 2:前置条件指标',
`value` decimal(18, 2) DEFAULT NULL COMMENT '原始值',
`reach_value` decimal(18, 2) DEFAULT NULL COMMENT '达成目标',
`data_date` date NOT NULL COMMENT '数据日期',
`group_id` bigint NOT NULL COMMENT '集团id',
`create_time` datetime DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
`yn` tinyint(1) DEFAULT '1',
PRIMARY KEY (`id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='绩效指标目标达成记录';
-- ----------------------------
-- Table structure for kpi_group_rank
-- ----------------------------
DROP TABLE IF EXISTS `kpi_group_rank`;
CREATE TABLE `kpi_group_rank`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`name` varchar(255) NOT NULL COMMENT '组合名称',
`star_evaluation_type` tinyint NOT NULL COMMENT '星级评定方式;1:绩效得分率 2:人员百分比',
`revoked_score_ratio` decimal(10, 4) DEFAULT NULL COMMENT '撤销D级得分率',
`kgcs` varchar(500) NOT NULL COMMENT '绩效组编码',
`ineffective_date` date DEFAULT NULL COMMENT '失效日期',
`group_id` bigint NOT NULL COMMENT '集团id',
`yn` tinyint(1) DEFAULT '1' COMMENT '逻辑删除',
`create_by` bigint DEFAULT NULL COMMENT '创建者',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_by` bigint DEFAULT NULL COMMENT '更新者',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE = InnoDB
AUTO_INCREMENT = 11
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='绩效组排名配置';
-- ----------------------------
-- Table structure for kpi_group_rank_star_ladders
-- ----------------------------
DROP TABLE IF EXISTS `kpi_group_rank_star_ladders`;
CREATE TABLE `kpi_group_rank_star_ladders`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`kpi_group_rank_id` bigint DEFAULT NULL COMMENT '绩效组排名配置id',
`level` tinyint 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 = 41
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='排名星级评定阶梯';
-- ----------------------------
-- Table structure for kpi_group_user
-- ----------------------------
DROP TABLE IF EXISTS `kpi_group_user`;
CREATE TABLE `kpi_group_user`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`kgc` varchar(60) NOT NULL COMMENT '绩效组编码',
`kpi_group_id` bigint DEFAULT NULL COMMENT '绩效组id',
`user_id` bigint NOT NULL COMMENT '人员id',
`user_name` varchar(255) DEFAULT NULL COMMENT '人员名称',
`post_id` bigint NOT NULL COMMENT '岗位码',
`post_name` varchar(255) DEFAULT NULL COMMENT '岗位名称',
`shop_id` bigint NOT NULL COMMENT '门店id',
`shop_name` varchar(255) DEFAULT NULL COMMENT '在职门店',
`ignored` tinyint(1) NOT NULL COMMENT '是否不计入绩效考核',
`ignore_cause` tinyint DEFAULT NULL COMMENT '不计入原因;枚举待定',
`ignore_cause_desc` varchar(255) DEFAULT NULL COMMENT '不计入原因描述',
`anticipated_date` date DEFAULT NULL COMMENT '开始考核时间',
`remark` varchar(255) DEFAULT NULL COMMENT '备注',
`data_date` date DEFAULT NULL COMMENT '数据日期',
`group_id` bigint 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_group_user_data_date_index` (`data_date`),
KEY `kpi_group_user_user_id_index` (`user_id`)
) ENGINE = InnoDB
AUTO_INCREMENT = 3553
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='绩效组人员';
-- ----------------------------
-- Table structure for kpi_indicator_user_value
-- ----------------------------
DROP TABLE IF EXISTS `kpi_indicator_user_value`;
CREATE TABLE `kpi_indicator_user_value`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`user_id` bigint NOT NULL COMMENT '人员id',
`indicator_code` varchar(255) NOT NULL COMMENT '指标编码',
`indicator_name` varchar(255) DEFAULT NULL COMMENT '指标库名称',
`indicator_value` varchar(2000) NOT NULL COMMENT '指标业务值;jons字符串 map类型',
`value_type` tinyint NOT NULL COMMENT '类型;1:台数 2:百分比 3:金额 4:条件值',
`data_date` date DEFAULT NULL COMMENT '数据日期',
`group_id` bigint 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` (`data_date`),
KEY `kpi_indicator_value_user_id_index` (`user_id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='绩效人员指标业务值';
-- ----------------------------
-- Table structure for kpi_mq_log
-- ----------------------------
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_date` date NOT NULL COMMENT '数据时间 可能为过去的时间',
`group_id` bigint NOT NULL COMMENT '集团id',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`id`),
UNIQUE KEY `kpi_mq_log_id_uindex` (`id`),
KEY `kpi_mq_log_indicator_code_index` (`indicator_code`),
KEY `kpi_mq_log_post_code_index` (`post_id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='绩效上报mq发送记录';
-- ----------------------------
-- Table structure for kpi_pool
-- ----------------------------
DROP TABLE IF EXISTS `kpi_pool`;
CREATE TABLE `kpi_pool`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`kgc` varchar(60) NOT NULL COMMENT '绩效组编码',
`kpi_group_id` bigint NOT NULL COMMENT '绩效组id',
`user_id` bigint NOT NULL COMMENT '用户id',
`user_name` varchar(255) DEFAULT NULL COMMENT '用户名称',
`post_id` bigint NOT NULL COMMENT '岗位id',
`post_name` varchar(255) DEFAULT NULL COMMENT '岗位名称',
`shop_id` bigint NOT NULL COMMENT '门店id',
`shop_name` varchar(255) DEFAULT NULL COMMENT '门店名称',
`kpi_score` decimal(10, 2) NOT NULL COMMENT '绩效得分',
`kpi_score_ratio` decimal(10, 4) NOT NULL COMMENT '绩效得分率',
`average_kpi_score_ratio` decimal(10, 4) NOT NULL COMMENT '平均绩效得分率',
`star_level` tinyint DEFAULT NULL COMMENT '星级;1: A 2:B 3:C 4:D',
`actual_star` tinyint DEFAULT NULL COMMENT '实际星级;默认等于系统评定的星级,有调整的情况修改次字段',
`revoked` tinyint(1) DEFAULT NULL COMMENT 'D级撤销状态;是否撤销本次D级',
`inclusion` tinyint(1) DEFAULT NULL COMMENT '是否纳入绩效计算',
`monthly` varchar(8) NOT NULL COMMENT '月度',
`regular` tinyint(1) NOT NULL DEFAULT '0' COMMENT '固定的',
`rank` int DEFAULT NULL COMMENT '排名',
`group_id` bigint 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`),
UNIQUE KEY `pool_kpi_id_user_id_monthly_uindex` (`kpi_group_id`, `user_id`, `monthly`),
KEY `kpi_pool_kpi_group_id_index` (`kpi_group_id`),
KEY `kpi_pool_monthly_index` (`monthly`),
KEY `kpi_pool_user_id_index` (`user_id`)
) ENGINE = InnoDB
AUTO_INCREMENT = 196
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='绩效池';
-- ----------------------------
-- Table structure for kpi_pool_indicator_value
-- ----------------------------
DROP TABLE IF EXISTS `kpi_pool_indicator_value`;
CREATE TABLE `kpi_pool_indicator_value`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`kpi_pool_id` bigint NOT NULL COMMENT '绩效池id',
`user_id` bigint 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 DEFAULT NULL COMMENT '数据日期',
`mq_log_id` bigint NOT NULL COMMENT 'mq记录id',
`group_id` bigint 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_pool_indicator_value_data_date_index` (`data_date`),
KEY `kpi_pool_indicator_value_user_id_index` (`user_id`),
KEY `kpi_pool_indicator_value_mq_log_id_index` (`mq_log_id`)
) ENGINE = InnoDB
AUTO_INCREMENT = 16801
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='绩效人员指标业务值';
-- ----------------------------
-- Table structure for kpi_reward_point
-- ----------------------------
DROP TABLE IF EXISTS `kpi_reward_point`;
CREATE TABLE `kpi_reward_point`
(
`id` bigint NOT NULL 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 '产生时间',
`monthly` varchar(12) NOT NULL COMMENT '月度',
`score_point` decimal(18, 4) NOT NULL COMMENT '出炉的分数值(为正数则是奖励,负数则是处罚)',
`group_id` bigint NOT NULL COMMENT '集团id',
`create_time` datetime DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `kpi_reward_point_monthly_index` (`monthly`),
KEY `kpi_reward_point_user_id_post_id_index` (`user_id`, `post_id`)
) ENGINE = InnoDB
AUTO_INCREMENT = 2031
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='绩效分奖惩纪录';
-- ----------------------------
-- Table structure for kpi_star_ladders
-- ----------------------------
DROP TABLE IF EXISTS `kpi_star_ladders`;
CREATE TABLE `kpi_star_ladders`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`kpi_group_id` bigint DEFAULT NULL COMMENT '绩效组配置id',
`level` tinyint 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 = 309
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='星级评定阶梯';
-- ----------------------------
-- Table structure for kpi_star_rule
-- ----------------------------
DROP TABLE IF EXISTS `kpi_star_rule`;
CREATE TABLE `kpi_star_rule`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`roll_month` int DEFAULT NULL COMMENT '滚动月数',
`forced_turnover_cycle` int DEFAULT NULL COMMENT '强制离职考核周期',
`max_unqualified_times` int DEFAULT NULL COMMENT '强制离职周期内最大累计D级数',
`continuous_monthly` int DEFAULT NULL COMMENT '连续月份;满足次条件减少一次D级',
`min_score_ratio` decimal(10, 4) DEFAULT NULL COMMENT '最小得分率',
`group_id` bigint 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 = 3
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='绩效星级规则';
-- ----------------------------
-- Table structure for kpi_star_special_rule
-- ----------------------------
DROP TABLE IF EXISTS `kpi_star_special_rule`;
CREATE TABLE `kpi_star_special_rule`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`user_id` bigint NOT NULL COMMENT '人员id',
`user_name` varchar(255) DEFAULT NULL COMMENT '人员名称',
`earliest_starting_monthly` date DEFAULT NULL COMMENT '开始考核日期',
`revised_monthly` date DEFAULT NULL COMMENT '修正月度',
`revised_level` tinyint DEFAULT NULL COMMENT '修正星级;1: A 2:B 3:C 4:D',
`exclusion_monthly` date DEFAULT NULL COMMENT '排除月度',
`revoked_times` int DEFAULT NULL COMMENT '免除次数;免除D级次数',
`status` tinyint DEFAULT NULL COMMENT '状态;1:审批中 2:待生效 3:生效中 4:已失效',
`reason` varchar(255) DEFAULT NULL COMMENT '原因',
`attachment` varchar(255) DEFAULT NULL COMMENT '附件;fid逗号隔开',
`group_id` bigint 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 = 6
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='绩效星级特殊规则';
-- ----------------------------
-- Table structure for payroll_record
-- ----------------------------
DROP TABLE IF EXISTS `payroll_record`;
CREATE TABLE `payroll_record`
(
`id` bigint NOT NULL AUTO_INCREMENT,
`user_id` bigint NOT NULL COMMENT '员工id',
`user_name` varchar(32) DEFAULT NULL COMMENT '用户名称',
`shop_id` bigint NOT NULL COMMENT '门店id',
`fund_shop_id` bigint DEFAULT NULL COMMENT '社保公积金门店id',
`dealer_id` bigint DEFAULT NULL COMMENT '商家id',
`biz_id` varchar(32) NOT NULL COMMENT '业务ID(对应薪酬池id)',
`payment` tinyint(1) NOT NULL COMMENT '会否是付款',
`amount` decimal(18, 4) NOT NULL COMMENT '金额',
`corporate` tinyint(1) NOT NULL COMMENT '公帐发放',
`salary` tinyint(1) NOT NULL COMMENT '是否是工资',
`payroll_status` int NOT NULL COMMENT '发放状态 1:待推送到财务系统 2:已推送到财务系统 3:已发送成功',
`payroll_token` varchar(64) DEFAULT NULL COMMENT '发送token',
`monthly` varchar(12) DEFAULT NULL COMMENT '月度',
`group_id` bigint NOT NULL COMMENT '集团id',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
KEY `Payroll_Record_biz_id_index` (`biz_id`),
KEY `Payroll_Record_monthly_index` (`monthly`),
KEY `Payroll_Record_user_id_index` (`user_id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='薪资发放记录';
-- ----------------------------
-- Table structure for salary_confirm
-- ----------------------------
DROP TABLE IF EXISTS `salary_confirm`;
CREATE TABLE `salary_confirm`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`salary_pool_id` bigint 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 DEFAULT NULL COMMENT '截止日期',
`confirmed_status` tinyint DEFAULT NULL COMMENT '确认状态;1:待定 2:已确认 3:已取消',
`confirm_time` datetime DEFAULT NULL COMMENT '确认时间',
`self_confirm` tinyint(1) DEFAULT NULL COMMENT '是否本人确认',
`group_id` bigint 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 `salary_confirm_salary_pool_id_index` (`salary_pool_id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='薪酬确认记录';
-- ----------------------------
-- Table structure for salary_extra_project
-- ----------------------------
DROP TABLE IF EXISTS `salary_extra_project`;
CREATE TABLE `salary_extra_project`
(
`id` bigint NOT NULL AUTO_INCREMENT,
`post_id` bigint NOT NULL COMMENT '岗位id',
`shop_id` bigint NOT NULL COMMENT '在职门店id',
`user_id` bigint NOT NULL COMMENT '用户id',
`money` decimal(18, 4) NOT NULL COMMENT '金额',
`timely` tinyint(1) NOT NULL COMMENT '及时处理 为''真''时会立即推送给财务,否则会并入工资里结算',
`salary_type` int NOT NULL COMMENT '额外项类型 1:奖励 2:处罚 3:社保扣除项 4:福利补贴',
`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 NOT NULL COMMENT '集团id',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
`yn` tinyint(1) DEFAULT '1' COMMENT '逻辑删除',
PRIMARY KEY (`id`)
) ENGINE = InnoDB
AUTO_INCREMENT = 2150
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='额外薪资项';
-- ----------------------------
-- Table structure for salary_general_settin
-- ----------------------------
DROP TABLE IF EXISTS `salary_general_settin`;
CREATE TABLE `salary_general_settin`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`leader_visibility` tinyint(1) DEFAULT NULL COMMENT '管理层可见',
`dates_of_appeal` varchar(10) NOT NULL DEFAULT '3,5' COMMENT '可申诉日期;eg: 1,20 逗号隔开只存开始和结束',
`payoff_date` int DEFAULT NULL COMMENT '薪资发放日期;取值1-28(每月)',
`payoff_limit` int DEFAULT NULL COMMENT '薪资发放公户限额;元 0元则不限制',
`group_id` bigint 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 = 10
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='薪酬通用设置';
-- ----------------------------
-- Table structure for salary_group
-- ----------------------------
DROP TABLE IF EXISTS `salary_group`;
CREATE TABLE `salary_group`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`name` varchar(255) DEFAULT NULL COMMENT '薪酬组名称',
`sgc` varchar(60) NOT NULL COMMENT '薪酬组编码',
`post_id` bigint NOT NULL COMMENT '岗位id',
`post_name` varchar(255) DEFAULT NULL COMMENT '岗位名称',
`shop_ids` varchar(255) NOT NULL COMMENT '适用门店ids',
`shop_names` varchar(1024) DEFAULT NULL COMMENT '适用门店名称',
`pre_id` bigint DEFAULT NULL COMMENT '前次配置id',
`begin_time` date DEFAULT NULL COMMENT '生效时间',
`over_time` date DEFAULT NULL COMMENT '失效时间',
`reason` varchar(900) DEFAULT NULL COMMENT '调整原因',
`attachment` varchar(512) DEFAULT NULL COMMENT '附件ids',
`status` int NOT NULL COMMENT '状态;1:审批中 2:待生效 3:生效中 4:已失效',
`project_num` int DEFAULT NULL COMMENT '包薪酬项数量;只统计可配置的的项目数',
`group_id` bigint 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 = 41
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='薪酬组配置';
-- ----------------------------
-- Table structure for salary_group_project
-- ----------------------------
DROP TABLE IF EXISTS `salary_group_project`;
CREATE TABLE `salary_group_project`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`salary_group_id` bigint NOT NULL COMMENT '薪酬组id',
`name` varchar(255) DEFAULT NULL COMMENT '薪酬项目名称',
`cal_method` int NOT NULL COMMENT '计算方式;1:员工星级 2:固定金额 3:台阶提成 4:车系提成',
`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 = 212
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='薪酬组薪酬项目';
-- ----------------------------
-- Table structure for salary_group_project_hit_log
-- ----------------------------
DROP TABLE IF EXISTS `salary_group_project_hit_log`;
CREATE TABLE `salary_group_project_hit_log`
(
`id` bigint NOT NULL AUTO_INCREMENT,
`salary_project_id` bigint NOT NULL COMMENT '薪酬项id',
`salary_pool_id` bigint NOT NULL COMMENT '薪酬池id',
`salary_group_project_settin_id` bigint DEFAULT NULL COMMENT '命中具体配置项id',
`salary_value` decimal(18, 2) DEFAULT NULL COMMENT '薪酬值',
`data_date` date NOT NULL COMMENT '数据日期',
`group_id` bigint NOT NULL COMMENT '集团id',
`create_time` datetime DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
`yn` tinyint(1) DEFAULT '1',
PRIMARY KEY (`id`),
UNIQUE KEY `salary_project_hit_log_index` (`salary_project_id`, `salary_pool_id`, `data_date`,
`salary_group_project_settin_id`) USING BTREE
) ENGINE = InnoDB
AUTO_INCREMENT = 3617
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='用户薪酬项命中记录';
-- ----------------------------
-- Table structure for salary_group_project_param
-- ----------------------------
DROP TABLE IF EXISTS `salary_group_project_param`;
CREATE TABLE `salary_group_project_param`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`salary_group_id` bigint NOT NULL COMMENT '薪酬组id',
`salary_group_project_id` bigint NOT NULL COMMENT '薪酬组项目id',
`param_type` tinyint DEFAULT NULL COMMENT '参数类型(1.台阶指标,2,计算指标)',
`indicator_code` varchar(255) NOT NULL COMMENT '指标编码',
`indicator_name` varchar(255) DEFAULT NULL COMMENT '指标库名称',
`target_calc_type` tinyint(4) DEFAULT NULL COMMENT '目标计算类型;1: 目标值计算 2:最低要求计算',
`target_type` tinyint(4) DEFAULT '1' COMMENT '目标值类型; 1:无 2:百分比 3:金额 4:台数',
`target_value` decimal(10, 4) DEFAULT NULL COMMENT '目标值',
`proportion` decimal(10, 4) DEFAULT NULL COMMENT '占比',
`cap` tinyint(1) DEFAULT '1' COMMENT '是否封顶',
`sort` int 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
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='薪酬组薪酬项目参数';
-- ----------------------------
-- Table structure for salary_group_project_precondition
-- ----------------------------
DROP TABLE IF EXISTS `salary_group_project_precondition`;
CREATE TABLE `salary_group_project_precondition`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`salary_group_id` bigint NOT NULL COMMENT '薪酬组id',
`salary_group_project_id` bigint NOT NULL COMMENT '薪酬组项目id',
`indicator_code` varchar(255) NOT NULL COMMENT '指标编码',
`indicator_name` varchar(255) DEFAULT NULL COMMENT '指标库名称',
`target_calc_type` tinyint(4) DEFAULT NULL COMMENT '目标计算类型;1: 目标值计算 2:最低要求计算',
`target_type` tinyint(4) DEFAULT '1' COMMENT '条件值类型; 1:无 2:百分比 3:金额 4:台数',
`target_value` decimal(10, 4) DEFAULT NULL COMMENT '目标值',
`cond_value` decimal(10, 4) DEFAULT NULL COMMENT '条件值',
`sort` int 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
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='薪酬组薪酬项目前置条件';
-- ----------------------------
-- Table structure for salary_group_project_precondition_log
-- ----------------------------
DROP TABLE IF EXISTS `salary_group_project_precondition_log`;
CREATE TABLE `salary_group_project_precondition_log`
(
`id` bigint NOT NULL AUTO_INCREMENT,
`salary_group_project_id` bigint NOT NULL COMMENT '薪酬组项目id',
`precondition_id` bigint DEFAULT NULL COMMENT '薪酬组薪酬项目前置条件id',
`value` decimal(18, 2) DEFAULT NULL COMMENT '值',
`data_date` date NOT NULL COMMENT '数据日期',
`create_time` datetime DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
`yn` tinyint(1) DEFAULT '1',
PRIMARY KEY (`id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='薪酬指标条件记录';
-- ----------------------------
-- Table structure for salary_group_project_settin
-- ----------------------------
DROP TABLE IF EXISTS `salary_group_project_settin`;
CREATE TABLE `salary_group_project_settin`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`salary_group_project_id` bigint NOT NULL COMMENT '薪酬项目配置id',
`stair_key` bigint DEFAULT NULL COMMENT '阶梯key;星级名称/新车销售按车系计算是 对应车系id',
`stair_key_desc` varchar(255) DEFAULT NULL COMMENT '阶梯key对应描述',
`stair_value` decimal(18, 2) NOT NULL COMMENT '计算相关值',
`stair_min` int DEFAULT NULL COMMENT '阶梯值下限',
`stair_max` int 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 = 444
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='薪酬项目配置';
-- ----------------------------
-- Table structure for salary_group_project_target_hit_log
-- ----------------------------
DROP TABLE IF EXISTS `salary_group_project_target_hit_log`;
CREATE TABLE `salary_group_project_target_hit_log`
(
`id` bigint NOT NULL AUTO_INCREMENT,
`refer_id` bigint DEFAULT NULL COMMENT '薪酬项目、前置条件 配置id',
`target_type` tinyint DEFAULT NULL COMMENT '1:薪酬项目 2:前置条件',
`value` decimal(18, 2) DEFAULT NULL COMMENT '原始值',
`reach_value` decimal(18, 2) DEFAULT NULL COMMENT '达成目标',
`data_date` date NOT NULL COMMENT '数据日期',
`group_id` bigint NOT NULL COMMENT '集团',
`create_time` datetime DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
`yn` tinyint(1) DEFAULT '1',
PRIMARY KEY (`id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='薪酬指标达成目标记录';
-- ----------------------------
-- Table structure for salary_group_user
-- ----------------------------
DROP TABLE IF EXISTS `salary_group_user`;
CREATE TABLE `salary_group_user`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`sgc` varchar(60) NOT NULL COMMENT '薪酬组编码',
`salary_group_id` bigint NOT NULL COMMENT '薪酬组id',
`user_id` bigint NOT NULL COMMENT '人员id',
`user_name` varchar(255) DEFAULT NULL COMMENT '人员名称',
`post_id` bigint NOT NULL COMMENT '岗位id',
`post_name` varchar(255) DEFAULT NULL COMMENT '岗位名称',
`shop_id` bigint NOT NULL COMMENT '门店id',
`shop_name` varchar(255) DEFAULT NULL COMMENT '门店名称',
`probationer` tinyint(1) NOT NULL COMMENT '试用人员',
`frozen` tinyint(1) NOT NULL COMMENT '冻结状态',
`data_date` date DEFAULT NULL COMMENT '数据日期',
`group_id` bigint 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 `salary_group_user_data_date_index` (`data_date`),
KEY `salary_group_user_salary_group_id_index` (`salary_group_id`),
KEY `salary_group_user_user_id_index` (`user_id`)
) ENGINE = InnoDB
AUTO_INCREMENT = 3320
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='薪酬组人员';
-- ----------------------------
-- Table structure for salary_pay_notice
-- ----------------------------
DROP TABLE IF EXISTS `salary_pay_notice`;
CREATE TABLE `salary_pay_notice`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`salary_pool_id` bigint 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 DEFAULT NULL COMMENT '截止日期',
`confirmed_status` tinyint DEFAULT NULL COMMENT '确认状态;1:待定 2:已确认 3:已取消',
`confirm_time` datetime DEFAULT NULL COMMENT '确认时间',
`self_confirm` tinyint(1) DEFAULT NULL COMMENT '是否本人确认',
`group_id` bigint 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 `salary_confirm_salary_pool_id_index` (`salary_pool_id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='薪酬确认记录';
-- ----------------------------
-- Table structure for salary_pool
-- ----------------------------
DROP TABLE IF EXISTS `salary_pool`;
CREATE TABLE `salary_pool`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`sgc` varchar(60) NOT NULL COMMENT '薪酬组编码',
`salary_group_id` bigint NOT NULL COMMENT '薪酬组id',
`user_id` bigint NOT NULL COMMENT '用户id',
`user_name` varchar(255) DEFAULT NULL COMMENT '用户名称',
`post_id` bigint NOT NULL COMMENT '岗位id',
`post_name` varchar(255) DEFAULT NULL COMMENT '岗位名称',
`shop_id` bigint NOT NULL COMMENT '门店id',
`shop_name` varchar(255) DEFAULT NULL COMMENT '门店名称',
`fund_shop_id` bigint DEFAULT NULL COMMENT '社保公积金门店id',
`fund_shop_name` varchar(255) DEFAULT NULL COMMENT '社保公积金门店名称',
`star_level` tinyint 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) DEFAULT NULL COMMENT '签名文件',
`paid` tinyint(1) DEFAULT NULL COMMENT '是否已发放',
`group_id` bigint 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 `salary_pool_monthly_index` (`monthly`),
KEY `salary_pool_salary_group_id_index` (`salary_group_id`),
KEY `salary_pool_user_id_index` (`user_id`)
) ENGINE = InnoDB
AUTO_INCREMENT = 116
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='薪酬池';
-- ----------------------------
-- Table structure for salary_pool_detail
-- ----------------------------
DROP TABLE IF EXISTS `salary_pool_detail`;
CREATE TABLE `salary_pool_detail`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`salary_pool_id` bigint NOT NULL COMMENT '薪酬池id',
`salary_project_id` bigint DEFAULT NULL COMMENT '薪酬项id',
`salary_project_name` varchar(255) DEFAULT NULL COMMENT '薪酬项名称',
`type` int NOT NULL COMMENT '薪酬类型 1:绩效提成 2:内部奖励 3:内部罚款 4:社保扣款 5:福利补贴 6:试用期工资',
`salary_amount` decimal(18, 2) DEFAULT NULL COMMENT '预计收入',
`processed_amount` decimal(18, 2) NOT NULL COMMENT '已处理金额',
`actual_salary_amount` decimal(18, 2) DEFAULT NULL COMMENT '实际收入',
`salary_date` date NOT NULL COMMENT '薪资日期',
`group_id` bigint 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 `salary_pool_detail_salary_pool_id_index` (`salary_pool_id`),
KEY `salary_pool_detail_salary_date_index` (`salary_date`)
) ENGINE = InnoDB
AUTO_INCREMENT = 4219870
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='薪资明细';
-- ----------------------------
-- Table structure for salary_raw_data
-- ----------------------------
DROP TABLE IF EXISTS `salary_raw_data`;
CREATE TABLE `salary_raw_data`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`salary_project_code` varchar(255) DEFAULT NULL COMMENT '薪酬项编码',
`salary_project_value` varchar(2000) NOT NULL COMMENT '薪酬项具体值;jons字符串 map类型',
`value_type` tinyint NOT NULL COMMENT '类型;1: 百分比 2:数量 3:条件值 4:金额 5:工龄',
`user_id` bigint NOT NULL COMMENT '人员id',
`data_date` date NOT NULL COMMENT '数据对应日期',
`group_id` bigint 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 `salary_raw_data_user_id_index` (`user_id`),
KEY `salary_raw_data_data_date_index` (`data_date`)
) ENGINE = InnoDB
AUTO_INCREMENT = 9967
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='薪酬提成原始数据';
-- ----------------------------
-- Table structure for setting_draft
-- ----------------------------
DROP TABLE IF EXISTS `setting_draft`;
CREATE TABLE `setting_draft`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`type` tinyint DEFAULT '1' COMMENT '类型; 1:考评组 ',
`union_id` bigint NOT NULL COMMENT '关联id',
`content` text COMMENT '内容【json】',
`yn` tinyint(1) DEFAULT '1' COMMENT '逻辑删除',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci COMMENT ='草稿';
SET FOREIGN_KEY_CHECKS = 1;
fw-morax.git/doc/绩效.md 0000664 0000000 0000000 00000001472 14432555067 0016703 0 ustar 00root root 0000000 0000000 定时任务
绩效:
| 功能名 | 执行时间 | 方法名 |
| ---- | ---- | ---- |
| 绩效组 | 每月1号凌晨3点 | processCurMonthEffectKpis |
| 绩效组人员 | 每天凌晨30分 | processKpiUser |
| 绩效指标上报 | 每天凌晨2点 | sendNotice |
薪酬:
| 功能名 | 执行时间 | 方法名 |
| ---- | ---- | ---- |
| 薪酬组 | 每月1号凌晨3点 | processCurMonthEffectSalaryGroup |
| 薪酬组人员 | 每天凌晨30分 | asyncSalaryGroupUser |
周期任务:
#### 绩效:
业务系统查询绩效组人员。不计算绩效的人员不会被查询出
上报绩效组人员查询:查询当天生成的绩效组人员
实时绩效组人员查询:实时查询绩效组人员,当天调岗、离职、入职都会被查到
薪酬:
fw-morax.git/doc/绩效需求分析-2022-11-02.md 0000664 0000000 0000000 00000005602 14432555067 0023770 0 ustar 00root root 0000000 0000000 ##### 绩效指标、薪酬指标统一为 指标库
##### 一、岗位最低收入
1. 新增岗位最低收入配置表 income_compensation
2. 月底计算:计算完工资后,计算员工的最低收入补偿,将补偿保存在 额外薪资项(salary_extra_project)中
##### 二、绩效改动
1. 业务系统每天上报所有人在该系统的指标
绩效人员指标业务值 kpi_indicator_value
2. 绩效组配置改动
绩效组 kpi_group
绩效指标 kpi_group_indicator (一对多)
指标项得分阶梯 kpi_group_indicator_ladders( 一对多)
参数指标 kpi_group_indicator_param (新增 一对多)(存放 台阶条件,提成条件)
条件指标 kpi_group_indicator_cond (新增 一对多)(处理绩效最终得分)
绩效组指标条件得分阶梯 kpi_group_indicator_cond_ladders (新增 一对多,关联条件指标)
绩效指标达成目标记录 kpi_group_indicator_target_log
绩效指标命中条件记录 kpi_group_indicator_cond_log
绩效组指标项得分记录 kpi_group_indicator_log
##### 三、绩效组改动
1. 薪酬配置变动
薪酬组 salary_group
薪酬组薪酬项目 salary_group_project (一对多)
薪酬项目配置 salary_group_project_settin( 一对多)
薪酬组薪酬项目参数 salary_group_project_param (新增 一对多)(存放 台阶条件,提成条件)
薪酬组薪酬项目条件 salary_group_project_cond (新增 一对多)(能否得到此薪酬项目的前置条件)
薪酬指标达成目标记录 salary_group_project_target_log
薪酬指标条件记录 salary_group_project_cond_log
用户薪酬项命中记录 salary_group_project_hit_log
##### 四、考评组
考评指标 eval_indicator
考评组 eval_group
考评奖惩 eval_group_reward
考评奖惩阶梯 eval_group_reward_ladders
考评奖惩参数 eval_group_reward_param
考评奖惩条件 eval_group_reward_cond
考评组指标 eval_group_indicator
考评组指标参数 eval_group_indicator_param
考评组指标条件 eval_group_indicator_cond
考评组指标条件得分阶梯 eval_group_indicator_cond_ladders
考评指标达成目标记录 eval_group_indicator_target_log
考评指标条件记录 eval_group_indicator_cond_log
考评奖惩达成目标记录 eval_group_reward_target_log
考评奖惩条件记录 eval_group_reward_cond_log
考评池 eval_pool
考评指标条件命中记录 eval_pool_indicator_detail
考评奖惩明细 eval_pool_reward_detail
fw-morax.git/doc/霏微-绩效薪酬.chnr.json 0000664 0000000 0000000 00000660015 14432555067 0024535 0 ustar 00root root 0000000 0000000 {
"name": "霏微-绩效薪酬",
"describe": "绩效薪酬系统",
"avatar": "",
"version": "3.5.7",
"createdTime": "2022-3-26 15:50:15",
"updatedTime": "2022-4-2 20:19:02",
"profile": {
"default": {
"db": "543A6CA4-367B-4FE9-9472-F6333D855593",
"dbConn": "",
"entityInitFields": [
{
"defKey": "id",
"defName": "ID",
"comment": "",
"type": "BIGINT",
"len": 20,
"scale": "",
"primaryKey": true,
"notNull": true,
"autoIncrement": true,
"defaultValue": "",
"hideInGraph": true,
"domain": "",
"refDict": "",
"uiHint": "",
"id": "1A932E6A-10A9-4FEC-87C1-97DA1405B5F3"
},
{
"defKey": "group_id",
"defName": "集团id",
"comment": "",
"type": "BIGINT",
"len": 20,
"scale": "",
"primaryKey": false,
"notNull": true,
"autoIncrement": false,
"defaultValue": "",
"hideInGraph": false,
"refDict": "",
"domain": "",
"id": "8A56A0FF-2A98-40CD-B794-387FF35B2387"
},
{
"defKey": "yn",
"defName": "逻辑删除",
"comment": "",
"domain": "",
"type": "TINYINT",
"len": 1,
"scale": "",
"primaryKey": false,
"notNull": true,
"autoIncrement": false,
"defaultValue": "1",
"hideInGraph": true,
"refDict": "",
"uiHint": "",
"id": "62519A44-401D-4252-812B-F49B35996967"
},
{
"defKey": "create_by",
"defName": "创建人",
"comment": "",
"domain": "",
"type": "BIGINT",
"len": 20,
"scale": "",
"primaryKey": false,
"notNull": false,
"autoIncrement": false,
"defaultValue": "",
"hideInGraph": true,
"refDict": "",
"uiHint": "",
"id": "D2B27FE4-77FC-46F3-A12E-35E885105B16"
},
{
"defKey": "create_time",
"defName": "创建时间",
"comment": "",
"domain": "CCCB321A-1B89-4B7B-AB97-50888FCBD8F2",
"type": "",
"len": "",
"scale": "",
"primaryKey": false,
"notNull": false,
"autoIncrement": false,
"defaultValue": "",
"hideInGraph": true,
"refDict": "",
"uiHint": "",
"id": "092E39D5-A3AE-48A7-A18F-E745B5B0101A"
},
{
"defKey": "update_by",
"defName": "更新人",
"comment": "",
"domain": "",
"type": "BIGINT",
"len": 20,
"scale": "",
"primaryKey": false,
"notNull": false,
"autoIncrement": false,
"defaultValue": "",
"hideInGraph": true,
"refDict": "",
"uiHint": "",
"id": "43F57595-B1B0-4656-B3F7-FCD56EF61576"
},
{
"defKey": "update_time",
"defName": "更新时间",
"comment": "",
"domain": "CCCB321A-1B89-4B7B-AB97-50888FCBD8F2",
"type": "",
"len": "",
"scale": "",
"primaryKey": false,
"notNull": false,
"autoIncrement": false,
"defaultValue": "",
"hideInGraph": true,
"refDict": "",
"uiHint": "",
"id": "B7ABE9D2-EBAF-4345-AC09-1C9DD87A5669"
}
],
"entityInitProperties": {}
},
"sql": {
"delimiter": ""
},
"dataTypeSupports": [
{
"defKey": "MYSQL",
"id": "543A6CA4-367B-4FE9-9472-F6333D855593"
},
{
"defKey": "ORACLE",
"id": "FED6C309-134E-4028-BBC4-53D69D1A7DBB"
},
{
"defKey": "SQLServer",
"id": "70EF54D7-E6C4-4A38-A217-38947ACECBED"
},
{
"defKey": "PostgreSQL",
"id": "D1B25C45-A76F-4BA7-8726-E26F7C430F43"
},
{
"defKey": "DB2",
"id": "F6371CE7-3A15-47B9-A8F3-A1686D8D862E"
},
{
"defKey": "DM",
"id": "2F3A20A8-799B-455D-A959-60194F973858"
},
{
"defKey": "GaussDB",
"id": "E777D134-0B67-42EB-8B05-C2B903457508"
},
{
"defKey": "Kingbase",
"id": "114AB923-9E58-4E25-9F65-133A1AD17D20"
},
{
"defKey": "MaxCompute",
"id": "268352C1-F13B-4BCA-AB61-2EA299F85D00"
},
{
"defKey": "JAVA",
"id": "432C3EA6-81F1-4F22-AE1D-998224EBD753"
},
{
"defKey": "C#",
"id": "7AF32A5B-ABCC-40B8-91D6-FEB89C91D114"
},
{
"defKey": "SQLite",
"id": "B4BD574E-2AC5-45E9-B626-4261FF540AC8"
}
],
"codeTemplates": [
{
"type": "dbDDL",
"applyFor": "543A6CA4-367B-4FE9-9472-F6333D855593",
"isDefault": true,
"createTable": "DROP TABLE IF EXISTS {{=it.entity.defKey}};\nCREATE TABLE {{=it.entity.defKey}}(\n{{ pkList = [] ; }}\n{{~it.entity.fields:field:index}}\n {{? field.primaryKey }}{{ pkList.push(field.defKey) }}{{?}}\n {{=field.defKey}} {{=field.type}}{{?field.len>0}}{{='('}}{{=field.len}}{{?field.scale>0}}{{=','}}{{=field.scale}}{{?}}{{=')'}}{{?}} {{= field.notNull ? 'NOT NULL' : '' }} {{= field.autoIncrement ? 'AUTO_INCREMENT' : '' }} {{= field.defaultValue ? it.func.join('DEFAULT',field.defaultValue,' ') : '' }} COMMENT '{{=it.func.join(field.defName,field.comment,';')}}' {{= index < it.entity.fields.length-1 ? ',' : ( pkList.length>0 ? ',' :'' ) }}\n{{~}}\n{{? pkList.length >0 }}\n PRIMARY KEY ({{~pkList:pkName:i}}{{= pkName }}{{= i0}}{{='('}}{{=field.len}}{{?field.scale>0}}{{=','}}{{=field.scale}}{{?}}{{=')'}}{{?}}{{= field.defaultValue ? it.func.join(' DEFAULT ',field.defaultValue,' ') : '' }}{{= field.notNull ? ' NOT NULL' : '' }}{{= field.autoIncrement ? '' : '' }}{{= index < it.entity.fields.length-1 ? ',' : ( pkList.length>0 ? ',' :'' ) }}\n{{~}}\n{{? pkList.length >0 }}\n PRIMARY KEY ({{~pkList:pkName:i}}{{= pkName }}{{= i0}}{{='('}}{{=field.len}}{{?field.scale>0}}{{=','}}{{=field.scale}}{{?}}{{=')'}}{{?}}{{= field.notNull ? ' NOT NULL' : '' }}{{= field.autoIncrement ? ' IDENTITY(1,1)' : '' }}{{= field.defaultValue ? it.func.join(' DEFAULT ',field.defaultValue,' ') : '' }}{{= index < it.entity.fields.length-1 ? ',' : ( pkList.length>0 ? ',' :'' ) }}\n{{~}}\n{{? pkList.length >0 }}\n PRIMARY KEY ({{~pkList:pkName:i}}{{= pkName }}{{= i0}}{{='('}}{{=field.len}}{{?field.scale>0}}{{=','}}{{=field.scale}}{{?}}{{=')'}}{{?}}{{= field.notNull ? ' NOT NULL' : '' }}{{= field.autoIncrement ? ' AUTO_INCREMENT' : '' }}{{= field.defaultValue ? it.func.join(' DEFAULT ',field.defaultValue,' ') : '' }}{{= index < it.entity.fields.length-1 ? ',' : ( pkList.length>0 ? ',' :'' ) }}\n{{~}}\n{{? pkList.length >0 }}\n PRIMARY KEY ({{~pkList:pkName:i}}{{= pkName }}{{= i0}}{{='('}}{{=field.len}}{{?field.scale>0}}{{=','}}{{=field.scale}}{{?}}{{=')'}}{{?}}{{= field.notNull ? ' NOT NULL' : '' }}{{= field.autoIncrement ? '' : '' }}{{= field.defaultValue ? it.func.join(' DEFAULT ',field.defaultValue,' ') : '' }}{{= index < it.entity.fields.length-1 ? ',' : ( pkList.length>0 ? ',' :'' ) }}\n{{~}}\n{{? pkList.length >0 }}\n PRIMARY KEY ({{~pkList:pkName:i}}{{= pkName }}{{= i0}}{{='('}}{{=field.len}}{{?field.scale>0}}{{=','}}{{=field.scale}}{{?}}{{=')'}}{{?}}{{= field.notNull ? ' NOT NULL' : '' }}{{= field.autoIncrement ? '' : '' }}{{= field.defaultValue ? it.func.join(' DEFAULT ','('+field.defaultValue+')',' ') : '' }}{{= index < it.entity.fields.length-1 ? ',' : ( pkList.length>0 ? ',' :'' ) }}\n{{~}}\n{{? pkList.length >0 }}\n PRIMARY KEY ({{~pkList:pkName:i}}{{= pkName }}{{= i\n /// {{=it.func.join(field.defName,field.comment,';')}}\n /// \n public {{=field.type}} {{=it.func.camel(field.defKey,true)}} { get; set; }\n $blankline\n {{~}}\n \n }\n}"
},
{
"type": "dbDDL",
"applyFor": "E777D134-0B67-42EB-8B05-C2B903457508",
"isDefault": true,
"createTable": "DROP TABLE IF EXISTS {{=it.entity.defKey}};\nCREATE TABLE {{=it.entity.defKey}}(\n{{ pkList = [] ; }}\n{{~it.entity.fields:field:index}}\n {{? field.primaryKey }}{{ pkList.push(field.defKey) }}{{?}}\n {{=field.defKey}} {{=field.type}}{{?field.len>0}}{{='('}}{{=field.len}}{{?field.scale>0}}{{=','}}{{=field.scale}}{{?}}{{=')'}}{{?}}{{= field.notNull ? ' NOT NULL' : '' }}{{= field.autoIncrement ? ' AUTO_INCREMENT' : '' }}{{= field.defaultValue ? it.func.join(' DEFAULT ',field.defaultValue,' ') : '' }}{{= index < it.entity.fields.length-1 ? ',' : ( pkList.length>0 ? ',' :'' ) }}\n{{~}}\n{{? pkList.length >0 }}\n PRIMARY KEY ({{~pkList:pkName:i}}{{= pkName }}{{= i0}}{{='('}}{{=field.len}}{{?field.scale>0}}{{=','}}{{=field.scale}}{{?}}{{=')'}}{{= field.defaultValue ? it.func.join(' DEFAULT ',field.defaultValue,' ') : '' }}{{?}}{{= field.notNull ? ' NOT NULL' : '' }}{{= index < it.entity.fields.length-1 ? ',' : ( pkList.length>0 ? ',' :'' ) }}\n{{~}}\n{{? pkList.length >0 }}\n PRIMARY KEY ({{~pkList:pkName:i}}{{= pkName }}{{= i0 ? ',' :'' ) }}\n{{~}}\n{{? pkList.length >0 }}\n PRIMARY KEY ({{~pkList:pkName:i}}{{= pkName }}{{= i0}}{{='('}}{{=field.len}}{{?field.scale>0}}{{=','}}{{=field.scale}}{{?}}{{=')'}}{{?}} {{= field.notNull ? 'NOT NULL' : '' }} {{= field.autoIncrement ? 'AUTOINCREMENT' : '' }} {{= field.defaultValue ? it.func.join('DEFAULT',field.defaultValue,' ') : '' }}{{= index < it.entity.fields.length-1 ? ',' : ( pkList.length>0 ? ',' :'' ) }} --{{=it.func.join(field.defName,field.comment,';')}}\n{{~}}\n{{? pkList.length >0 }}\n PRIMARY KEY ({{~pkList:pkName:i}}{{= pkName }}{{= i