公司绩效考评系统 -- 数据库设计, 接口方法

我不是女神ヾ 2022-03-15 04:18 167阅读 0赞

个人项目

# 数据库表 #

/*
     Navicat Premium Data Transfer
    
     Source Server         : mysql
     Source Server Type    : MySQL
     Source Server Version : 50540
     Source Host           : localhost:3306
     Source Schema         : company
    
     Target Server Type    : MySQL
     Target Server Version : 50540
     File Encoding         : 65001
    
     Date: 26/02/2019 18:27:01
    */
    
    SET NAMES utf8mb4;
    SET FOREIGN_KEY_CHECKS = 0;
    
    -- ----------------------------
    -- Table structure for controller
    -- ----------------------------
    DROP TABLE IF EXISTS `controller`;
    CREATE TABLE `controller`  (
      `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
      `controller_path` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '资源权限路径',
      `controller_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '资源权限名称',
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
    
    -- ----------------------------
    -- Records of controller
    -- ----------------------------
    INSERT INTO `controller` VALUES (1, 'sorce/findAll', NULL);
    
    -- ----------------------------
    -- Table structure for department
    -- ----------------------------
    DROP TABLE IF EXISTS `department`;
    CREATE TABLE `department`  (
      `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '部门id',
      `dept_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '部门名称',
      PRIMARY KEY (`id`) USING BTREE,
      INDEX `id`(`id`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
    
    -- ----------------------------
    -- Records of department
    -- ----------------------------
    INSERT INTO `department` VALUES (1, '技术部');
    INSERT INTO `department` VALUES (2, '设计部');
    INSERT INTO `department` VALUES (3, '人事部');
    
    -- ----------------------------
    -- Table structure for mark_history
    -- ----------------------------
    DROP TABLE IF EXISTS `mark_history`;
    CREATE TABLE `mark_history`  (
      `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
      `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
      `mark_score_date` date NULL DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
    
    -- ----------------------------
    -- Records of mark_history
    -- ----------------------------
    INSERT INTO `mark_history` VALUES (1, 'test', '2019-02-22');
    
    -- ----------------------------
    -- Table structure for performance_type
    -- ----------------------------
    DROP TABLE IF EXISTS `performance_type`;
    CREATE TABLE `performance_type`  (
      `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
      `score_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
    
    -- ----------------------------
    -- Records of performance_type
    -- ----------------------------
    INSERT INTO `performance_type` VALUES (1, '考勤');
    INSERT INTO `performance_type` VALUES (2, '经验分享');
    INSERT INTO `performance_type` VALUES (3, '工作量');
    INSERT INTO `performance_type` VALUES (4, '工作效率');
    INSERT INTO `performance_type` VALUES (5, '工作难度');
    INSERT INTO `performance_type` VALUES (6, '总分');
    
    -- ----------------------------
    -- Table structure for role
    -- ----------------------------
    DROP TABLE IF EXISTS `role`;
    CREATE TABLE `role`  (
      `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
      `role_name` varchar(54) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
      INDEX `id`(`id`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
    
    -- ----------------------------
    -- Records of role
    -- ----------------------------
    INSERT INTO `role` VALUES (1, '老板');
    INSERT INTO `role` VALUES (2, '项目经理');
    INSERT INTO `role` VALUES (3, '员工');
    INSERT INTO `role` VALUES (4, '测试');
    
    -- ----------------------------
    -- Table structure for role_controller
    -- ----------------------------
    DROP TABLE IF EXISTS `role_controller`;
    CREATE TABLE `role_controller`  (
      `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
      `controller_id` int(11) UNSIGNED NOT NULL,
      `role_id` int(11) UNSIGNED NULL DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE,
      INDEX `controller_id`(`controller_id`) USING BTREE,
      INDEX `role_id`(`role_id`) USING BTREE,
      CONSTRAINT `role_controller_ibfk_1` FOREIGN KEY (`controller_id`) REFERENCES `controller` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
      CONSTRAINT `role_controller_ibfk_2` FOREIGN KEY (`role_id`) REFERENCES `role` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
    ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
    
    -- ----------------------------
    -- Records of role_controller
    -- ----------------------------
    INSERT INTO `role_controller` VALUES (1, 1, 1);
    INSERT INTO `role_controller` VALUES (2, 1, 2);
    INSERT INTO `role_controller` VALUES (3, 1, 3);
    
    -- ----------------------------
    -- Table structure for score_performance
    -- ----------------------------
    DROP TABLE IF EXISTS `score_performance`;
    CREATE TABLE `score_performance`  (
      `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键 自增',
      `score_type_id` int(11) UNSIGNED NOT NULL COMMENT ' 绩效评项类别id',
      `score` int(11) NOT NULL COMMENT ' 绩效评项单项得分',
      `emp_id` int(11) UNSIGNED NOT NULL COMMENT ' 员工id',
      `status` int(1) UNSIGNED ZEROFILL NOT NULL COMMENT ' 默认0 表示自评, 1 表示他评',
      PRIMARY KEY (`id`) USING BTREE,
      INDEX `score_type_id`(`score_type_id`) USING BTREE,
      INDEX `emp_id`(`emp_id`) USING BTREE,
      CONSTRAINT `score_performance_ibfk_1` FOREIGN KEY (`emp_id`) REFERENCES `user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
      CONSTRAINT `score_type_id` FOREIGN KEY (`score_type_id`) REFERENCES `performance_type` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
    ) ENGINE = InnoDB AUTO_INCREMENT = 12 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
    
    -- ----------------------------
    -- Records of score_performance
    -- ----------------------------
    INSERT INTO `score_performance` VALUES (1, 1, 10, 1, 0);
    INSERT INTO `score_performance` VALUES (2, 2, 10, 1, 0);
    INSERT INTO `score_performance` VALUES (3, 3, 20, 1, 0);
    INSERT INTO `score_performance` VALUES (4, 4, 30, 1, 0);
    INSERT INTO `score_performance` VALUES (5, 5, 30, 1, 0);
    INSERT INTO `score_performance` VALUES (6, 6, 40, 1, 0);
    INSERT INTO `score_performance` VALUES (7, 6, 60, 1, 1);
    INSERT INTO `score_performance` VALUES (8, 2, 0, 2, 1);
    INSERT INTO `score_performance` VALUES (9, 3, 0, 2, 1);
    INSERT INTO `score_performance` VALUES (10, 6, 90, 2, 0);
    INSERT INTO `score_performance` VALUES (11, 6, 80, 3, 0);
    
    -- ----------------------------
    -- Table structure for user
    -- ----------------------------
    DROP TABLE IF EXISTS `user`;
    CREATE TABLE `user`  (
      `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '员工id',
      `parent_id` int(11) UNSIGNED NOT NULL COMMENT '员工上级领导id,为0表示没有上级',
      `username` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '员工姓名',
      `password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '员工密码',
      `dept_id` int(11) UNSIGNED NULL DEFAULT NULL COMMENT '部门id',
      `role_id` int(11) UNSIGNED NULL DEFAULT NULL COMMENT '角色id',
      `history_id` int(11) UNSIGNED NOT NULL COMMENT '历史记录id',
      PRIMARY KEY (`id`) USING BTREE,
      INDEX `history_id`(`history_id`) USING BTREE,
      INDEX `role_id`(`role_id`) USING BTREE,
      INDEX `dept_id`(`dept_id`) USING BTREE,
      CONSTRAINT `user_ibfk_1` FOREIGN KEY (`history_id`) REFERENCES `mark_history` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
      CONSTRAINT `user_ibfk_3` FOREIGN KEY (`dept_id`) REFERENCES `department` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
      CONSTRAINT `user_ibfk_4` FOREIGN KEY (`role_id`) REFERENCES `role` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
    ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
    
    -- ----------------------------
    -- Records of user
    -- ----------------------------
    INSERT INTO `user` VALUES (1, 0, 'leijun', '666', 1, 1, 1);
    INSERT INTO `user` VALUES (2, 1, 'linbin', '111', 2, 2, 1);
    INSERT INTO `user` VALUES (3, 2, 'dazhui', '222', 3, 3, 1);
    
    SET FOREIGN_KEY_CHECKS = 1;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2MjQxMDAz_size_16_color_FFFFFF_t_70]

每周员工评分查看，分数从高到低排序

每月员工评分查看，分数从高到低排序

# sql #

# 查出 某一个员工 总分 自评得分 他评得分 #

SELECT
    	ti.id,
    	ct.*,
    	ti.`score` + ct.`score` '总分' 
    FROM
    	( SELECT sc.* FROM score_performance sc WHERE sc.`status` = 0 AND sc.score_type_id = 6 ) ti,
    	( SELECT sc.* FROM score_performance sc WHERE sc.`status` = 1 AND sc.score_type_id = 6 ) ct 
    WHERE
    	ti.emp_id = ct.emp_id AND ti.emp_id = 2;

-- 查询所有员工总分 
    SELECT
    	ti.id,
    	ct.*,
    	ti.`score` + ct.`score` '总分' 
    FROM
    	( SELECT sc.* FROM score_performance sc WHERE sc.`status` = 0 AND sc.score_type_id = 6 ) ti,
    	( SELECT sc.* FROM score_performance sc WHERE sc.`status` = 1 AND sc.score_type_id = 6 ) ct 
    WHERE
    	ti.emp_id = ct.emp_id;

未完待续

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2MjQxMDAz_size_16_color_FFFFFF_t_70]: /images/20220315/0a6ab0e64c81488ba8cdfb58d6ad4102.png