员工薪资管理系统,源代码链接在文档的最后
数据库系统设计
部门信息表(ims_department)
字段名称 | 字段类型 | 字段描述 |
---|---|---|
dpt_id | varchar(64) | 主键ID |
dpt_name | varchar(255) | 部门名称 |
is_deleted | int | 逻辑删除标识(0:未删除;1:已删除) |
create_time | datetime | 创建时间 |
update_time | datetime | 更新时间 |
员工信息表(ims_employee)
字段名称 | 字段类型 | 字段描述 |
---|---|---|
emp_id | varchar(64) | 主键ID |
dpt_id | varchar(64) | 部门ID |
emp_name | varchar(255) | 员工姓名 |
emp_code | varchar(255) | 员工编码(部门首字母小写+日期+序列号) |
emp_sex | varchar(64) | 性别(MALE:男性;FEMALE:女性) |
is_deleted | int | 逻辑删除标识(0:未删除;1:已删除) |
create_time | datetime | 创建时间 |
update_time | datetime | 更新时间 |
薪资信息表(ims_salary)
字段名称 | 字段类型 | 字段描述 |
---|---|---|
sa_id | varchar(64) | 主键ID |
emp_id | varchar(64) | 员工ID |
sa_date | varchar(255) | 工资日期(字符串类型:例:2021-03) |
sa_base | decimal(9, 2) | 基础工资 |
sa_performance | decimal(9, 2) | 绩效工资 |
sa_insurance | decimal(9, 2) | 保险扣除 |
sa_actual | decimal(9, 2) | 实发工资(基础工资+绩效工资-保险扣除) |
is_deleted | int | 逻辑删除标识(0:未删除;1:已删除) |
create_time | datetime | 创建时间 |
update_time | datetime | 更新时间 |
三个表的创建及数据导入
/*
Navicat Premium Data Transfer
Source Server : demo
Source Server Type : MySQL
Source Server Version : 80029
Source Host : localhost:3306
Source Schema : project_01
Target Server Type : MySQL
Target Server Version : 80029
File Encoding : 65001
Date: 27/12/2023 14:12:47
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for ims_department
-- ----------------------------
DROP TABLE IF EXISTS `ims_department`;
CREATE TABLE `ims_department` (
`dpt_id` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`dpt_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`is_deleted` int NULL DEFAULT 0,
`create_time` datetime NULL DEFAULT NULL,
`update_time` datetime NULL DEFAULT NULL,
PRIMARY KEY (`dpt_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;
-- ----------------------------
-- Records of ims_department
-- ----------------------------
INSERT INTO `ims_department` VALUES ('7de8542e-a716-4a12-b283-7307267931fa', '数学部', 0, '2023-12-27 14:08:45', '2023-12-27 14:08:45');
INSERT INTO `ims_department` VALUES ('9f56800d-ee72-4754-8b39-ae2f14ce39c9', '语文部', 0, '2023-12-27 14:08:40', '2023-12-27 14:08:40');
INSERT INTO `ims_department` VALUES ('f87b768a-d93a-4db7-bcc1-b397523a389a', '英语部', 0, '2023-12-27 14:08:57', '2023-12-27 14:08:57');
SET FOREIGN_KEY_CHECKS = 1;
/*
Navicat Premium Data Transfer
Source Server : demo
Source Server Type : MySQL
Source Server Version : 80029
Source Host : localhost:3306
Source Schema : project_01
Target Server Type : MySQL
Target Server Version : 80029
File Encoding : 65001
Date: 27/12/2023 14:12:52
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for ims_employee
-- ----------------------------
DROP TABLE IF EXISTS `ims_employee`;
CREATE TABLE `ims_employee` (
`emp_id` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`dpt_id` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`emp_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`emp_code` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`emp_sex` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`is_deleted` int NULL DEFAULT 0,
`create_time` datetime NULL DEFAULT NULL,
`update_time` datetime NULL DEFAULT NULL,
PRIMARY KEY (`emp_id`) USING BTREE,
INDEX `dpt_id`(`dpt_id`) USING BTREE,
CONSTRAINT `dpt_id` FOREIGN KEY (`dpt_id`) REFERENCES `ims_department` (`dpt_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;
-- ----------------------------
-- Records of ims_employee
-- ----------------------------
INSERT INTO `ims_employee` VALUES ('6601b4c9-4cfa-4c73-8a9d-e9dd77b2e04f', '7de8542e-a716-4a12-b283-7307267931fa', '王五', 'SXB23122703', '男', 0, '2023-12-27 14:09:38', '2023-12-27 14:09:38');
INSERT INTO `ims_employee` VALUES ('c61ad900-51fe-4b99-ad45-f9e93cdb8bf3', '9f56800d-ee72-4754-8b39-ae2f14ce39c9', '张三', 'YWB23122701', '男', 0, '2023-12-27 14:09:15', '2023-12-27 14:09:15');
INSERT INTO `ims_employee` VALUES ('caebf24d-a9b7-4498-bb76-69eb93703c1d', '9f56800d-ee72-4754-8b39-ae2f14ce39c9', '李四', 'YWB23122702', '女', 0, '2023-12-27 14:09:24', '2023-12-27 14:09:24');
SET FOREIGN_KEY_CHECKS = 1;
/*
Navicat Premium Data Transfer
Source Server : demo
Source Server Type : MySQL
Source Server Version : 80029
Source Host : localhost:3306
Source Schema : project_01
Target Server Type : MySQL
Target Server Version : 80029
File Encoding : 65001
Date: 27/12/2023 14:13:01
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for ims_salary
-- ----------------------------
DROP TABLE IF EXISTS `ims_salary`;
CREATE TABLE `ims_salary` (
`sa_id` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`emp_id` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`sa_date` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`sa_base` decimal(9, 2) NULL DEFAULT NULL,
`sa_performance` decimal(9, 2) NULL DEFAULT NULL,
`sa_insurance` decimal(9, 2) NULL DEFAULT NULL,
`sa_actual` decimal(9, 2) NULL DEFAULT NULL,
`is_deleted` int NULL DEFAULT 0,
`create_time` datetime NULL DEFAULT NULL,
`update_time` datetime NULL DEFAULT NULL,
PRIMARY KEY (`sa_id`) USING BTREE,
INDEX `emp_id`(`emp_id`) USING BTREE,
CONSTRAINT `emp_id` FOREIGN KEY (`emp_id`) REFERENCES `ims_employee` (`emp_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;
-- ----------------------------
-- Records of ims_salary
-- ----------------------------
INSERT INTO `ims_salary` VALUES ('3caf12c0-8039-4b70-94a0-5c11879d3222', 'c61ad900-51fe-4b99-ad45-f9e93cdb8bf3', '2023-12', 5000.00, 2000.00, 2000.00, 5000.00, 0, '2023-12-27 14:09:46', '2023-12-27 14:09:46');
INSERT INTO `ims_salary` VALUES ('549bf0a1-e3bf-4696-bccd-b487ba0e6081', 'caebf24d-a9b7-4498-bb76-69eb93703c1d', '2023-12', 1000.00, 200.00, 200.00, 1000.00, 0, '2023-12-27 14:10:22', '2023-12-27 14:10:22');
INSERT INTO `ims_salary` VALUES ('a9e8d9df-0486-4540-946e-5337f08463cf', 'c61ad900-51fe-4b99-ad45-f9e93cdb8bf3', '2023-11', 5000.00, 2000.00, 1000.00, 6000.00, 0, '2023-11-01 14:11:03', '2023-11-01 14:11:03');
SET FOREIGN_KEY_CHECKS = 1;
系统功能介绍
主要功能
部门模块
部门新增
部门名必须是中文,限制长度8个中文
部门修改
展示部门信息,引导用户修改
部门删除
展示部门信息,引导用户删除,如果部门内有员工,则不能删除这个部门
员工模块
员工新增
根据部门添加员工,员工名必须为中文
员工修改
展示员工信息,修改员工信息
员工删除
展示员工信息,删除员工,员工对应的薪资也要删除
薪资模块
新增薪资
展示员工信息,根据员工添加薪资,当月该员工有薪资信息,则不能添加
修改薪资
根据员工的薪资信息进行修改,如果该员工没有薪资记录则不允许修改,只允许修改当月的薪资,历史薪资不允许修改
删除薪资
根据员工信息,删除薪资,只允许删除当月的薪资,历史薪资不允许删除
查询薪资
根据员工查询薪资
根据时间查询,时间格式要正确
文末
源码链接:CI/jdbc-work
如果对你有帮助,记得回来点个赞哟~