考勤管理系统

在用javaweb三层架构，（传递多个字段）新增一个数据库数据时，在service层实体类对dao层接口方法调用时，先创建一个对象，然后用set方法给对象实体类的值赋值，然后再把对象赋值给接口函数

可以传年月日时分秒

employeeHistory.setChangeDate(java.sql.Timestamp.valueOf("2022-12-12 00:00:00"));

只能传年月日

employeeHistory.setChangeDate(Date.valueOf("2022-12-12"))

注意数据库的序列，这个东西好像规定你过来数据库主键的自增，在前端调用的时候如果传递了这个主键的字段会报错500来着，但是测试类传这个主键不会报错，因为测试类是直接调用controller层的方法，绕过了springMVC的检测

我的大概流程

先搞定数据库，把表建好序列搞好触发器弄好（保留整个sql语句）

然后确定系统界面的前后端框架（我是javaweb+mybatis的三层架构框架）（把框架和sql语句给ai然后生成可以省力一点反正后面写测试类还得自己修改然后熟悉然后测试）

然后连接数据库

然后按照功能把三层架构一个一个方法到一个一个类建好

然后写测试类把所有方法测试一遍（在这个过程补充需要的方法和修改ai给的方法）

然后再根据测试类看方法需要用的数据写jsp文件（可以直接把controller层的代码和测试类的代码给ai然后直接生成jsp文件）

然后在jsp文件按照方法一个一个测试是否能用并且是否合理然后修改

最后再统一样式或者结束。

这是数据库部分

-- 1. 序列创建
-- 部门序列
CREATE SEQUENCE seq_department_id START WITH 100 INCREMENT BY 10;

-- 职位序列
CREATE SEQUENCE seq_position_id START WITH 10 INCREMENT BY 1;

-- 员工序列
CREATE SEQUENCE seq_employee_id START WITH 1000 INCREMENT BY 1;

-- 考勤序列
CREATE SEQUENCE seq_attendance_id START WITH 1 INCREMENT BY 1;

-- 工资序列
CREATE SEQUENCE seq_salary_id START WITH 1 INCREMENT BY 1;

-- 员工历史记录序列
CREATE SEQUENCE seq_employee_history START WITH 1 INCREMENT BY 1;

-- 2. 核心表结构设计
-- 部门表
CREATE TABLE department (
dept_id NUMBER PRIMARY KEY,
dept_name VARCHAR2(50) NOT NULL
);

-- 职位表
CREATE TABLE position (
pos_id NUMBER PRIMARY KEY,
pos_name VARCHAR2(20) NOT NULL,
base_salary NUMBER(10,2) NOT NULL,
pos_level NUMBER(1) NOT NULL CHECK (pos_level BETWEEN 1 AND 5) -- 1试用2正式3组长4经理5总经理
);

-- 员工表
CREATE TABLE employee (
emp_id NUMBER PRIMARY KEY,
emp_name VARCHAR2(20) NOT NULL,
dept_id NUMBER REFERENCES department(dept_id),
pos_id NUMBER REFERENCES position(pos_id),
hire_date DATE NOT NULL,
status NUMBER(1) DEFAULT 1 -- 0离职1在职
);

-- 考勤表
CREATE TABLE attendance (
att_id NUMBER PRIMARY KEY,
emp_id NUMBER REFERENCES employee(emp_id),
att_date DATE DEFAULT SYSDATE NOT NULL,
check_in TIMESTAMP,
check_out TIMESTAMP,
att_type VARCHAR2(10) DEFAULT '正常'
);

-- 工资表
CREATE TABLE salary (
sal_id NUMBER PRIMARY KEY,
emp_id NUMBER REFERENCES employee(emp_id),
year NUMBER(4) NOT NULL,
month NUMBER(2) NOT NULL,
base_salary NUMBER(10,2) NOT NULL,
bonus NUMBER(10,2) DEFAULT 0,
deduction NUMBER(10,2) DEFAULT 0,
total NUMBER(10,2) GENERATED ALWAYS AS (base_salary + bonus - deduction) VIRTUAL,
CONSTRAINT uk_salary UNIQUE (emp_id, year, month)
);

-- 员工历史表
CREATE TABLE employee_history (
history_id NUMBER PRIMARY KEY,
emp_id NUMBER REFERENCES employee(emp_id),
change_type VARCHAR2(10), -- 调动/离职
change_date DATE DEFAULT SYSDATE
);

-- 登录用的
CREATE TABLE users (
user_id NUMBER PRIMARY KEY,
username VARCHAR2(50) UNIQUE NOT NULL,
password VARCHAR2(50) NOT NULL
);
CREATE SEQUENCE seq_user_id START WITH 1 INCREMENT BY 1;
INSERT INTO USERS (USER_ID, USERNAME, PASSWORD) VALUES (1, 'admin', '123456');

-- 3. 关键触发器
-- 触发器1：自动处理迟到/缺勤

CREATE OR REPLACE TRIGGER trg_attendance_check
BEFORE INSERT OR UPDATE ON attendance
FOR EACH ROW
DECLARE
v_base_time TIMESTAMP;
BEGIN
v_base_time := TO_TIMESTAMP(TO_CHAR(:NEW.att_date, 'YYYY-MM-DD'), 'YYYY-MM-DD');
IF :NEW.check_in > v_base_time + INTERVAL '9' HOUR THEN
IF :NEW.check_in >= v_base_time + INTERVAL '10' HOUR THEN
:NEW.att_type := '缺勤';
ELSE
:NEW.att_type := '迟到';
END IF;
ELSE
:NEW.att_type := '正常'; -- 建议加上确保数据一致性
END IF;
END;
/

-- 触发器2：自动记录员工变动历史
CREATE OR REPLACE TRIGGER trg_employee_history
AFTER UPDATE OF dept_id, pos_id, status ON employee
FOR EACH ROW
BEGIN
IF :OLD.dept_id != :NEW.dept_id OR :OLD.pos_id != :NEW.pos_id OR :OLD.status != :NEW.status THEN
    INSERT INTO employee_history VALUES(
      seq_employee_history.NEXTVAL,
      :NEW.emp_id,
      CASE WHEN :NEW.status = 0 THEN '离职' ELSE '调动' END,
      SYSDATE
    );
END IF;
END;
/

-- 触发器3：自动计算工资扣款

CREATE OR REPLACE TRIGGER trg_salary_deduction
BEFORE INSERT OR UPDATE ON salary
FOR EACH ROW
DECLARE
v_absent_days NUMBER;
BEGIN
SELECT COUNT(*) INTO v_absent_days
FROM attendance
WHERE emp_id = :NEW.emp_id
AND att_date >= TO_DATE(:NEW.year || '-' || :NEW.month || '-01', 'YYYY-MM-DD')
AND att_date < ADD_MONTHS(TO_DATE(:NEW.year || '-' || :NEW.month || '-01', 'YYYY-MM-DD'), 1)
AND att_type = '缺勤';

:NEW.deduction := ROUND(v_absent_days * (:NEW.base_salary/22), 2);
END;
/

-- 4. 统计视图
-- 视图1：部门考勤统计
CREATE OR REPLACE VIEW v_dept_attendance AS
SELECT
d.dept_name,
TO_CHAR(a.att_date, 'YYYY-MM') AS month,
COUNT(DISTINCT a.emp_id) AS employee_count,
SUM(CASE WHEN a.att_type = '正常' THEN 1 ELSE 0 END) AS normal_days,
SUM(CASE WHEN a.att_type = '迟到' THEN 1 ELSE 0 END) AS late_days,
SUM(CASE WHEN a.att_type = '缺勤' THEN 1 ELSE 0 END) AS absent_days
FROM attendance a
JOIN employee e ON a.emp_id = e.emp_id
JOIN department d ON e.dept_id = d.dept_id
GROUP BY d.dept_name, TO_CHAR(a.att_date, 'YYYY-MM');

-- 视图2：员工工资明细
CREATE OR REPLACE VIEW v_employee_salary AS
SELECT
e.emp_name,
d.dept_name,
p.pos_name,
s.year,
s.month,
s.base_salary,
s.bonus,
s.deduction,
s.total
FROM salary s
JOIN employee e ON s.emp_id = e.emp_id
JOIN department d ON e.dept_id = d.dept_id
JOIN position p ON e.pos_id = p.pos_id;

-- 部门数据插入
-- 插入部门数据
INSERT INTO department (dept_id, dept_name) VALUES (seq_department_id.NEXTVAL, '人力资源部');
INSERT INTO department (dept_id, dept_name) VALUES (seq_department_id.NEXTVAL, '财务部');
INSERT INTO department (dept_id, dept_name) VALUES (seq_department_id.NEXTVAL, '技术开发部');
INSERT INTO department (dept_id, dept_name) VALUES (seq_department_id.NEXTVAL, '市场营销部');
INSERT INTO department (dept_id, dept_name) VALUES (seq_department_id.NEXTVAL, '客户服务部');

-- 职位数据插入
-- 插入职位数据
INSERT INTO position (pos_id, pos_name, base_salary, pos_level) VALUES (seq_position_id.NEXTVAL, '试用员工', 4000, 1);
INSERT INTO position (pos_id, pos_name, base_salary, pos_level) VALUES (seq_position_id.NEXTVAL, '正式员工', 6000, 2);
INSERT INTO position (pos_id, pos_name, base_salary, pos_level) VALUES (seq_position_id.NEXTVAL, '开发组长', 10000, 3);
INSERT INTO position (pos_id, pos_name, base_salary, pos_level) VALUES (seq_position_id.NEXTVAL, '部门经理', 15000, 4);
INSERT INTO position (pos_id, pos_name, base_salary, pos_level) VALUES (seq_position_id.NEXTVAL, '总经理', 25000, 5);
INSERT INTO position (pos_id, pos_name, base_salary, pos_level) VALUES (seq_position_id.NEXTVAL, '会计', 8000, 2);
INSERT INTO position (pos_id, pos_name, base_salary, pos_level) VALUES (seq_position_id.NEXTVAL, '市场专员', 7000, 2);
INSERT INTO position (pos_id, pos_name, base_salary, pos_level) VALUES (seq_position_id.NEXTVAL, '客服主管', 9000, 3);

-- 员工数据插入
-- 插入员工数据
INSERT INTO employee (emp_id, emp_name, dept_id, pos_id, hire_date, status)
VALUES (seq_employee_id.NEXTVAL, '张三', 100, 10, TO_DATE('2020-01-15', 'YYYY-MM-DD'), 1);

INSERT INTO employee (emp_id, emp_name, dept_id, pos_id, hire_date, status)
VALUES (seq_employee_id.NEXTVAL, '李四', 100, 13, TO_DATE('2019-05-20', 'YYYY-MM-DD'), 1);

INSERT INTO employee (emp_id, emp_name, dept_id, pos_id, hire_date, status)
VALUES (seq_employee_id.NEXTVAL, '王五', 110, 11, TO_DATE('2021-03-10', 'YYYY-MM-DD'), 1);

INSERT INTO employee (emp_id, emp_name, dept_id, pos_id, hire_date, status)
VALUES (seq_employee_id.NEXTVAL, '赵六', 110, 15, TO_DATE('2020-11-01', 'YYYY-MM-DD'), 1);

INSERT INTO employee (emp_id, emp_name, dept_id, pos_id, hire_date, status)
VALUES (seq_employee_id.NEXTVAL, '钱七', 120, 12, TO_DATE('2022-02-18', 'YYYY-MM-DD'), 1);

INSERT INTO employee (emp_id, emp_name, dept_id, pos_id, hire_date, status)
VALUES (seq_employee_id.NEXTVAL, '孙八', 120, 14, TO_DATE('2018-07-22', 'YYYY-MM-DD'), 1);

INSERT INTO employee (emp_id, emp_name, dept_id, pos_id, hire_date, status)
VALUES (seq_employee_id.NEXTVAL, '周九', 130, 16, TO_DATE('2021-09-05', 'YYYY-MM-DD'), 1);

INSERT INTO employee (emp_id, emp_name, dept_id, pos_id, hire_date, status)
VALUES (seq_employee_id.NEXTVAL, '吴十', 140, 17, TO_DATE('2022-01-10', 'YYYY-MM-DD'), 1);

-- 插入一个已离职员工
INSERT INTO employee (emp_id, emp_name, dept_id, pos_id, hire_date, status)
VALUES (seq_employee_id.NEXTVAL, '郑十一', 130, 11, TO_DATE('2020-06-15', 'YYYY-MM-DD'), 0);

-- 考勤数据插入
-- 插入考勤数据 (当前月份)
INSERT INTO attendance (att_id, emp_id, att_date, check_in, check_out, att_type)
VALUES (seq_attendance_id.NEXTVAL, 1000, TRUNC(SYSDATE),
        TO_TIMESTAMP(TO_CHAR(TRUNC(SYSDATE), 'YYYY-MM-DD') || ' 08:45:00', 'YYYY-MM-DD HH24:MI:SS'),
        TO_TIMESTAMP(TO_CHAR(TRUNC(SYSDATE), 'YYYY-MM-DD') || ' 17:30:00', 'YYYY-MM-DD HH24:MI:SS'),
        '正常');

INSERT INTO attendance (att_id, emp_id, att_date, check_in, check_out, att_type)
VALUES (seq_attendance_id.NEXTVAL, 1001, TRUNC(SYSDATE),
        TO_TIMESTAMP(TO_CHAR(TRUNC(SYSDATE), 'YYYY-MM-DD') || ' 09:15:00', 'YYYY-MM-DD HH24:MI:SS'),
        TO_TIMESTAMP(TO_CHAR(TRUNC(SYSDATE), 'YYYY-MM-DD') || ' 18:00:00', 'YYYY-MM-DD HH24:MI:SS'),
        '迟到');

INSERT INTO attendance (att_id, emp_id, att_date, check_in, check_out, att_type)
VALUES (seq_attendance_id.NEXTVAL, 1002, TRUNC(SYSDATE)-1,
        TO_TIMESTAMP(TO_CHAR(TRUNC(SYSDATE)-1, 'YYYY-MM-DD') || ' 10:30:00', 'YYYY-MM-DD HH24:MI:SS'),
        TO_TIMESTAMP(TO_CHAR(TRUNC(SYSDATE)-1, 'YYYY-MM-DD') || ' 17:45:00', 'YYYY-MM-DD HH24:MI:SS'),
        '缺勤');

-- 上个月的数据
INSERT INTO attendance (att_id, emp_id, att_date, check_in, check_out, att_type)
VALUES (seq_attendance_id.NEXTVAL, 1000, ADD_MONTHS(TRUNC(SYSDATE), -1),
        TO_TIMESTAMP(TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE), -1), 'YYYY-MM-DD') || ' 08:50:00', 'YYYY-MM-DD HH24:MI:SS'),
        TO_TIMESTAMP(TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE), -1), 'YYYY-MM-DD') || ' 17:20:00', 'YYYY-MM-DD HH24:MI:SS'),
        '正常');

INSERT INTO attendance (att_id, emp_id, att_date, check_in, check_out, att_type)
VALUES (seq_attendance_id.NEXTVAL, 1003, ADD_MONTHS(TRUNC(SYSDATE), -1)+1,
        TO_TIMESTAMP(TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE), -1)+1, 'YYYY-MM-DD') || ' 08:55:00', 'YYYY-MM-DD HH24:MI:SS'),
        TO_TIMESTAMP(TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE), -1)+1, 'YYYY-MM-DD') || ' 17:40:00', 'YYYY-MM-DD HH24:MI:SS'),
        '正常');

-- 工资数据插入
-- 插入工资数据 (当前月份和上个月)
-- 当前月份
INSERT INTO salary (sal_id, emp_id, year, month, base_salary, bonus, deduction)
VALUES (seq_salary_id.NEXTVAL, 1000, EXTRACT(YEAR FROM SYSDATE), EXTRACT(MONTH FROM SYSDATE), 6000, 500, 0);

INSERT INTO salary (sal_id, emp_id, year, month, base_salary, bonus, deduction)
VALUES (seq_salary_id.NEXTVAL, 1001, EXTRACT(YEAR FROM SYSDATE), EXTRACT(MONTH FROM SYSDATE), 15000, 1000, 0);

INSERT INTO salary (sal_id, emp_id, year, month, base_salary, bonus, deduction)
VALUES (seq_salary_id.NEXTVAL, 1002, EXTRACT(YEAR FROM SYSDATE), EXTRACT(MONTH FROM SYSDATE), 8000, 300, 363.64); -- 8000/22 ≈ 363.64

-- 上个月
INSERT INTO salary (sal_id, emp_id, year, month, base_salary, bonus, deduction)
VALUES (seq_salary_id.NEXTVAL, 1000, EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE, -1)), EXTRACT(MONTH FROM ADD_MONTHS(SYSDATE, -1)), 6000, 300, 0);

INSERT INTO salary (sal_id, emp_id, year, month, base_salary, bonus, deduction)
VALUES (seq_salary_id.NEXTVAL, 1003, EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE, -1)), EXTRACT(MONTH FROM ADD_MONTHS(SYSDATE, -1)), 10000, 500, 0);