-- 文件名：stored_procedures.sql
-- 这是为员工考勤管理系统编写的存储过程创建脚本

DELIMITER //

-- 1. 存储过程：sp_add_attendance_record
-- 需求：添加员工考勤记录
CREATE PROCEDURE sp_add_attendance_record(
    IN p_employee_id VARCHAR(50),
    IN p_attendance_date DATE,
    IN p_check_in_time DATETIME,
    IN p_check_out_time DATETIME,
    IN p_working_hours INT,
    IN p_overtime_hours INT,
    IN p_attendance_status VARCHAR(20)
)
BEGIN
    -- 开始事务
    START TRANSACTION;
    BEGIN
        -- 插入考勤记录
        INSERT INTO Attendance (EmployeeID, AttendanceDate, CheckInTime, CheckOutTime, WorkingHours, OvertimeHours, AttendanceStatus)
        VALUES (p_employee_id, p_attendance_date, p_check_in_time, p_check_out_time, p_working_hours, p_overtime_hours, p_attendance_status);

        -- 提交事务
        COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            -- 回滚事务
            ROLLBACK;
            -- 可以记录日志或返回错误信息
    END;
END //

-- 2. 存储过程：sp_approve_leave_request
-- 需求：批准假期申请，修改状态为“已批准”
CREATE PROCEDURE sp_approve_leave_request(
    IN p_request_id VARCHAR(50),
    IN p_approver_id VARCHAR(50)
)
BEGIN
    -- 开始事务
    START TRANSACTION;
    BEGIN
        -- 更新假期申请状态为“已批准”
        UPDATE LeaveRequest
        SET RequestStatus = '已批准', ApproverID = p_approver_id
        WHERE RequestID = p_request_id;

        -- 提交事务
        COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            -- 回滚事务
            ROLLBACK;
            -- 可以记录日志或返回错误信息
    END;
END //

-- 3. 存储过程：sp_reject_leave_request
-- 需求：拒绝假期申请，修改状态为“已拒绝”
CREATE PROCEDURE sp_reject_leave_request(
    IN p_request_id VARCHAR(50),
    IN p_approver_id VARCHAR(50)
)
BEGIN
    -- 开始事务
    START TRANSACTION;
    BEGIN
        -- 更新假期申请状态为“已拒绝”
        UPDATE LeaveRequest
        SET RequestStatus = '已拒绝', ApproverID = p_approver_id
        WHERE RequestID = p_request_id;

        -- 提交事务
        COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            -- 回滚事务
            ROLLBACK;
            -- 可以记录日志或返回错误信息
    END;
END //

-- 4. 存储过程：sp_update_performance_score
-- 需求：更新员工的绩效考核评分和等级
CREATE PROCEDURE sp_update_performance_score(
    IN p_review_id VARCHAR(50),
    IN p_review_score INT,
    IN p_review_grade VARCHAR(10)
)
BEGIN
    -- 开始事务
    START TRANSACTION;
    BEGIN
        -- 更新绩效评分和等级
        UPDATE PerformanceAppraisal
        SET ReviewScore = p_review_score, ReviewGrade = p_review_grade
        WHERE ReviewID = p_review_id;

        -- 提交事务
        COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            -- 回滚事务
            ROLLBACK;
            -- 可以记录日志或返回错误信息
    END;
END //

-- 5. 存储过程：sp_generate_monthly_attendance_summary
-- 需求：生成每月考勤总结
CREATE PROCEDURE sp_generate_monthly_attendance_summary(
    IN p_month DATE
)
BEGIN
    -- 开始事务
    START TRANSACTION;
    BEGIN
        -- 将每月的考勤数据汇总插入考勤总结表
        INSERT INTO AttendanceSummary (EmployeeID, Month, PresentDays, LateCount, EarlyLeaveCount, AbsentDays, TotalOvertimeHours)
        SELECT 
            E.EmployeeID,
            p_month AS StatisticMonth,
            SUM(CASE WHEN A.AttendanceStatus = '正常' THEN 1 ELSE 0 END) AS PresentDays,
            SUM(CASE WHEN A.AttendanceStatus = '迟到' THEN 1 ELSE 0 END) AS LateCount,
            SUM(CASE WHEN A.AttendanceStatus = '早退' THEN 1 ELSE 0 END) AS EarlyLeaveCount,
            SUM(CASE WHEN A.AttendanceStatus = '缺勤' THEN 1 ELSE 0 END) AS AbsentDays,
            SUM(A.OvertimeHours) AS TotalOvertimeHours
        FROM 
            Employee E
        LEFT JOIN 
            Attendance A ON E.EmployeeID = A.EmployeeID AND A.AttendanceDate >= DATE_FORMAT(p_month, '%Y-%m-01') 
            AND A.AttendanceDate < DATE_ADD(DATE_FORMAT(p_month, '%Y-%m-01'), INTERVAL 1 MONTH)
        GROUP BY 
            E.EmployeeID;

        -- 提交事务
        COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            -- 回滚事务
            ROLLBACK;
            -- 可以记录日志或返回错误信息
    END;
END //

-- 6. 存储过程：sp_add_employee
-- 需求：新增员工信息
CREATE PROCEDURE sp_add_employee(
    IN p_employee_id VARCHAR(50),
    IN p_name VARCHAR(100),
    IN p_gender VARCHAR(10),
    IN p_date_of_birth DATE,
    IN p_position VARCHAR(50),
    IN p_department_id VARCHAR(50),
    IN p_contact_info VARCHAR(20),
    IN p_hire_date DATE,
    IN p_employment_status VARCHAR(20)
)
BEGIN
    -- 开始事务
    START TRANSACTION;
    BEGIN
        -- 向员工表插入新员工记录
        INSERT INTO Employee (EmployeeID, Name, Gender, DateOfBirth, Position, DepartmentID, ContactInfo, HireDate, EmploymentStatus)
        VALUES (p_employee_id, p_name, p_gender, p_date_of_birth, p_position, p_department_id, p_contact_info, p_hire_date, p_employment_status);

        -- 提交事务
        COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            -- 回滚事务
            ROLLBACK;
            -- 可以记录日志或返回错误信息
    END;
END //

DELIMITER ;