创建存储过程计算企业各部门每日10%请假配额及PHP表单报错提示

Alright, let's tackle this problem step by step. We need two core components here: a database stored procedure to handle daily leave quota calculations and checks, and PHP code to process form submissions and give users clear feedback.

1. 数据库存储过程设计 #

First, let's set up the basic tables to store department info and leave requests (if you don't already have them), then build the stored procedure to check quota availability.

基础表结构（若未创建） #

-- 部门信息表
CREATE TABLE departments (
    dept_id INT PRIMARY KEY AUTO_INCREMENT,
    dept_name VARCHAR(50) NOT NULL,
    total_employees INT NOT NULL
);

-- 插入示例部门数据
INSERT INTO departments (dept_name, total_employees)
VALUES ('A', 300), ('B', 150), ('C', 400), ('D', 50);

-- 请假申请表
CREATE TABLE leave_requests (
    request_id INT PRIMARY KEY AUTO_INCREMENT,
    dept_id INT NOT NULL,
    employee_id INT NOT NULL,
    leave_date DATE NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

存储过程：检查部门当日请假配额 #

This stored procedure takes a department ID and leave date, then returns whether the request can be submitted (1 = allowed, 0 = quota exhausted).

DELIMITER //

CREATE PROCEDURE CheckLeaveQuota(
    IN p_dept_id INT,
    IN p_leave_date DATE,
    OUT p_can_request INT
)
BEGIN
    DECLARE v_total_employees INT;
    DECLARE v_quota INT;
    DECLARE v_current_leaves INT;

    -- 获取对应部门总员工数
    SELECT total_employees INTO v_total_employees
    FROM departments
    WHERE dept_id = p_dept_id;

    -- 计算10%配额（向下取整，比如50人部门对应5个配额）
    SET v_quota = FLOOR(v_total_employees * 0.1);

    -- 统计当日该部门已提交的请假申请数（含待审批，避免超额申请）
    SELECT COUNT(*) INTO v_current_leaves
    FROM leave_requests
    WHERE dept_id = p_dept_id AND leave_date = p_leave_date;

    -- 判断是否还有剩余配额
    IF v_current_leaves < v_quota THEN
        SET p_can_request = 1;
    ELSE
        SET p_can_request = 0;
    END IF;
END //

DELIMITER ;

2. PHP表单处理逻辑 #

Next, let's build the form and PHP script to handle submissions, check the quota via the stored procedure, and show the appropriate message to users.

请假申请表单（HTML） #

<form method="POST" action="submit_leave.php">
    <label for="dept">所属部门:</label>
    <select name="dept_id" id="dept" required>
        <option value="1">A部门</option>
        <option value="2">B部门</option>
        <option value="3">C部门</option>
        <option value="4">D部门</option>
    </select>

    <label for="leave_date">请假日期:</label>
    <input type="date" name="leave_date" id="leave_date" required>

    <label for="emp_id">员工ID:</label>
    <input type="text" name="employee_id" id="emp_id" required>

    <button type="submit">提交请假申请</button>
</form>

PHP处理脚本（submit_leave.php） #

<?php
// 数据库连接配置
$host = 'localhost';
$dbname = 'your_database_name';
$username = 'your_db_username';
$password = 'your_db_password';

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    if ($_SERVER['REQUEST_METHOD'] === 'POST') {
        $dept_id = $_POST['dept_id'];
        $leave_date = $_POST['leave_date'];
        $employee_id = $_POST['employee_id'];

        // 调用存储过程检查配额
        $stmt = $pdo->prepare("CALL CheckLeaveQuota(?, ?, @can_request)");
        $stmt->execute([$dept_id, $leave_date]);

        // 获取存储过程返回的结果
        $result = $pdo->query("SELECT @can_request AS can_request")->fetch(PDO::FETCH_ASSOC);
        $can_request = $result['can_request'];

        if ($can_request == 1) {
            // 配额充足，插入请假申请记录
            $insertStmt = $pdo->prepare("INSERT INTO leave_requests (dept_id, employee_id, leave_date) VALUES (?, ?, ?)");
            $insertStmt->execute([$dept_id, $employee_id, $leave_date]);
            echo "<p style='color: green;'>请假申请已提交，请等待审批！</p>";
        } else {
            // 配额用尽，弹出提示弹窗
            echo "<script>alert('当日该部门10%请假配额已用完，请选择其他日期申请');</script>";
            // 返回申请页面的链接
            echo "<p><a href='leave_form.php'>返回请假申请页面</a></p>";
        }
    }
} catch(PDOException $e) {
    echo "数据库错误: " . $e->getMessage();
}
?>

额外注意事项 #

• 配额计算规则：这里用FLOOR()向下取整，若需要向上取整（比如50人部门算6个配额），可以替换为CEIL()函数，根据业务需求调整。
• 请假状态统计：存储过程统计了所有状态的请假申请，因为待审批的申请也应该占用配额，避免同一日期申请过载。如果仅统计已批准的申请，可在leave_requests查询中添加AND status = 'approved'条件。
• 前端体验优化：可以用AJAX在表单提交前异步检查配额，无需刷新页面就能给出提示，提升用户体验。

内容的提问来源于stack exchange，提问作者Rajendra Jadhav