如何用PHP项目实现考勤统计？

本文目录导读：

1. 数据库设计
2. 核心考勤类
3. 考勤统计类
4. 前端展示示例
5. 部署建议

我来详细介绍如何使用PHP实现考勤统计系统。

数据库设计

首先创建考勤相关的数据表：

-- 员工表
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    employee_no VARCHAR(20) UNIQUE NOT NULL,
    name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    position VARCHAR(50),
    status TINYINT DEFAULT 1
);
-- 考勤记录表
CREATE TABLE attendance_records (
    id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT NOT NULL,
    date DATE NOT NULL,
    sign_in_time DATETIME,
    sign_out_time DATETIME,
    status ENUM('normal', 'late', 'early_leave', 'absent', 'leave') DEFAULT 'normal',
    remark TEXT,
    FOREIGN KEY (employee_id) REFERENCES employees(id),
    UNIQUE KEY unique_attendance (employee_id, date)
);
-- 考勤配置表
CREATE TABLE attendance_settings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    work_start_time TIME NOT NULL DEFAULT '09:00:00',
    work_end_time TIME NOT NULL DEFAULT '18:00:00',
    late_threshold_minutes INT DEFAULT 30,
    effective_date DATE NOT NULL
);

核心考勤类

<?php
// Attendance.php
class Attendance {
    private $db;
    private $workStartTime;
    private $workEndTime;
    private $lateThreshold;
    public function __construct($db) {
        $this->db = $db;
        $this->loadSettings();
    }
    // 加载考勤配置
    private function loadSettings() {
        $query = "SELECT * FROM attendance_settings 
                  WHERE effective_date <= CURDATE() 
                  ORDER BY effective_date DESC LIMIT 1";
        $result = $this->db->query($query);
        if ($row = $result->fetch_assoc()) {
            $this->workStartTime = $row['work_start_time'];
            $this->workEndTime = $row['work_end_time'];
            $this->lateThreshold = $row['late_threshold_minutes'];
        }
    }
    // 签到
    public function signIn($employeeId) {
        $today = date('Y-m-d');
        $currentTime = date('Y-m-d H:i:s');
        // 检查是否已签到
        if ($this->hasSignedIn($employeeId, $today)) {
            return ['success' => false, 'message' => '今天已签到'];
        }
        // 判断考勤状态
        $currentHour = date('H:i');
        $status = $this->determineSignInStatus($currentHour);
        $query = "INSERT INTO attendance_records 
                  (employee_id, date, sign_in_time, status) 
                  VALUES (?, ?, ?, ?)";
        $stmt = $this->db->prepare($query);
        $stmt->bind_param("isss", $employeeId, $today, $currentTime, $status);
        if ($stmt->execute()) {
            return ['success' => true, 'message' => '签到成功', 'status' => $status];
        }
        return ['success' => false, 'message' => '签到失败'];
    }
    // 签到
    public function signOut($employeeId) {
        $today = date('Y-m-d');
        $currentTime = date('Y-m-d H:i:s');
        // 检查是否已签到
        $record = $this->getTodayRecord($employeeId);
        if (!$record) {
            return ['success' => false, 'message' => '今天还未签到'];
        }
        if ($record['sign_out_time']) {
            return ['success' => false, 'message' => '已签退'];
        }
        // 判断是否早退
        $currentHour = date('H:i');
        $status = $this->determineSignOutStatus($currentHour, $record['status']);
        $query = "UPDATE attendance_records 
                  SET sign_out_time = ?, status = ? 
                  WHERE id = ?";
        $stmt = $this->db->prepare($query);
        $stmt->bind_param("ssi", $currentTime, $status, $record['id']);
        if ($stmt->execute()) {
            return ['success' => true, 'message' => '签退成功', 'status' => $status];
        }
        return ['success' => false, 'message' => '签退失败'];
    }
    // 判断签到状态
    private function determineSignInStatus($currentTime) {
        $threshold = date('H:i', strtotime($this->workStartTime . " +{$this->lateThreshold} minutes"));
        if ($currentTime <= $this->workStartTime) {
            return 'normal';
        } elseif ($currentTime <= $threshold) {
            return 'late';
        } else {
            return 'absent';
        }
    }
    // 判断签退状态
    private function determineSignOutStatus($currentTime, $currentStatus) {
        if ($currentTime < $this->workEndTime) {
            return 'early_leave';
        }
        return $currentStatus;
    }
    // 检查是否已签到
    private function hasSignedIn($employeeId, $date) {
        $query = "SELECT id FROM attendance_records 
                  WHERE employee_id = ? AND date = ?";
        $stmt = $this->db->prepare($query);
        $stmt->bind_param("is", $employeeId, $date);
        $stmt->execute();
        return $stmt->get_result()->num_rows > 0;
    }
    // 获取今天的考勤记录
    private function getTodayRecord($employeeId) {
        $today = date('Y-m-d');
        $query = "SELECT * FROM attendance_records 
                  WHERE employee_id = ? AND date = ?";
        $stmt = $this->db->prepare($query);
        $stmt->bind_param("is", $employeeId, $today);
        $stmt->execute();
        return $stmt->get_result()->fetch_assoc();
    }
}

考勤统计类

<?php
// AttendanceStats.php
class AttendanceStats {
    private $db;
    public function __construct($db) {
        $this->db = $db;
    }
    // 获取个人考勤统计
    public function getEmployeeStats($employeeId, $startDate, $endDate) {
        $query = "SELECT 
                    COUNT(*) as total_days,
                    SUM(CASE WHEN status = 'normal' THEN 1 ELSE 0 END) as normal_days,
                    SUM(CASE WHEN status = 'late' THEN 1 ELSE 0 END) as late_days,
                    SUM(CASE WHEN status = 'early_leave' THEN 1 ELSE 0 END) as early_leave_days,
                    SUM(CASE WHEN status = 'absent' THEN 1 ELSE 0 END) as absent_days,
                    SUM(CASE WHEN status = 'leave' THEN 1 ELSE 0 END) as leave_days
                  FROM attendance_records 
                  WHERE employee_id = ? 
                    AND date BETWEEN ? AND ?";
        $stmt = $this->db->prepare($query);
        $stmt->bind_param("iss", $employeeId, $startDate, $endDate);
        $stmt->execute();
        return $stmt->get_result()->fetch_assoc();
    }
    // 获取部门考勤统计
    public function getDepartmentStats($department, $startDate, $endDate) {
        $query = "SELECT 
                    e.name,
                    e.employee_no,
                    COUNT(*) as total_days,
                    SUM(CASE WHEN a.status = 'late' THEN 1 ELSE 0 END) as late_count,
                    SUM(CASE WHEN a.status = 'absent' THEN 1 ELSE 0 END) as absent_count
                  FROM employees e
                  LEFT JOIN attendance_records a ON e.id = a.employee_id
                    AND a.date BETWEEN ? AND ?
                  WHERE e.department = ? AND e.status = 1
                  GROUP BY e.id
                  ORDER BY late_count DESC";
        $stmt = $this->db->prepare($query);
        $stmt->bind_param("sss", $startDate, $endDate, $department);
        $stmt->execute();
        return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
    }
    // 获取全公司统计
    public function getCompanyStats($startDate, $endDate) {
        $query = "SELECT 
                    d.department,
                    COUNT(DISTINCT e.id) as employee_count,
                    COUNT(a.id) as total_records,
                    SUM(CASE WHEN a.status = 'late' THEN 1 ELSE 0 END) as total_late,
                    SUM(CASE WHEN a.status = 'absent' THEN 1 ELSE 0 END) as total_absent,
                    ROUND(AVG(TIMESTAMPDIFF(HOUR, a.sign_in_time, a.sign_out_time)), 1) as avg_work_hours
                  FROM employees e
                  LEFT JOIN attendance_records a ON e.id = a.employee_id
                    AND a.date BETWEEN ? AND ?
                  GROUP BY e.department";
        $stmt = $this->db->prepare($query);
        $stmt->bind_param("ss", $startDate, $endDate);
        $stmt->execute();
        return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
    }
    // 生成考勤报表
    public function generateReport($startDate, $endDate, $department = null) {
        $where = "a.date BETWEEN ? AND ?";
        $params = [$startDate, $endDate];
        $types = "ss";
        if ($department) {
            $where .= " AND e.department = ?";
            $params[] = $department;
            $types .= "s";
        }
        $query = "SELECT 
                    e.employee_no,
                    e.name,
                    e.department,
                    a.date,
                    a.sign_in_time,
                    a.sign_out_time,
                    a.status,
                    TIMESTAMPDIFF(HOUR, a.sign_in_time, a.sign_out_time) as work_hours,
                    CASE 
                        WHEN a.status = 'late' THEN TIME_FORMAT(TIMEDIFF(a.sign_in_time, CONCAT(a.date, ' ', '09:00:00')), '%H:%i')
                        ELSE NULL 
                    END as late_duration
                  FROM attendance_records a
                  JOIN employees e ON a.employee_id = e.id
                  WHERE $where
                  ORDER BY a.date DESC, e.department, e.name";
        $stmt = $this->db->prepare($query);
        $stmt->bind_param($types, ...$params);
        $stmt->execute();
        return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
    }
    // 导出考勤数据为CSV
    public function exportToCSV($data, $filename = 'attendance_report.csv') {
        header('Content-Type: text/csv; charset=utf-8');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        $output = fopen('php://output', 'w');
        // 添加BOM用于Excel识别UTF-8
        fprintf($output, chr(0xEF).chr(0xBB).chr(0xBF));
        // 写入表头
        fputcsv($output, ['工号', '姓名', '部门', '日期', '签到时间', '签退时间', '状态', '工时']);
        // 写入数据
        foreach ($data as $row) {
            fputcsv($output, $row);
        }
        fclose($output);
        exit;
    }
}

前端展示示例

<?php
// attendance_dashboard.php
require_once 'Attendance.php';
require_once 'AttendanceStats.php';
// 数据库连接
$db = new mysqli('localhost', 'username', 'password', 'attendance_db');
$attendance = new Attendance($db);
$stats = new AttendanceStats($db);
// 处理签到/签退请求
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $employeeId = $_POST['employee_id'];
    $action = $_POST['action'];
    if ($action === 'sign_in') {
        $result = $attendance->signIn($employeeId);
    } elseif ($action === 'sign_out') {
        $result = $attendance->signOut($employeeId);
    }
    echo json_encode($result);
    exit;
}
// 获取统计报表
$startDate = $_GET['start_date'] ?? date('Y-m-01');
$endDate = $_GET['end_date'] ?? date('Y-m-t');
$department = $_GET['department'] ?? null;
$report = $stats->generateReport($startDate, $endDate, $department);
$companyStats = $stats->getCompanyStats($startDate, $endDate);
// 导出CSV
if (isset($_GET['export'])) {
    $stats->exportToCSV($report);
}
?>
<!DOCTYPE html>
<html>
<head>考勤管理系统</title>
    <style>
        .container { max-width: 1200px; margin: 0 auto; padding: 20px; }
        .stats-grid { display: grid; grid-template-columns: repeat(auto-fit, minmax(200px, 1fr)); gap: 20px; margin: 20px 0; }
        .stat-card { background: #f5f5f5; padding: 20px; border-radius: 8px; text-align: center; }
        .stat-value { font-size: 24px; font-weight: bold; }
        .positive { color: #4CAF50; }
        .negative { color: #f44336; }
        table { width: 100%; border-collapse: collapse; margin-top: 20px; }
        th, td { padding: 10px; border: 1px solid #ddd; text-align: left; }
        th { background-color: #4CAF50; color: white; }
    </style>
</head>
<body>
    <div class="container">
        <h1>考勤管理系统</h1>
        <!-- 公司统计 -->
        <div class="stats-grid">
            <?php foreach ($companyStats as $stat): ?>
            <div class="stat-card">
                <h3><?php echo $stat['department']; ?></h3>
                <div class="stat-value"><?php echo $stat['employee_count']; ?> 人</div>
                <p>迟到: <span class="negative"><?php echo $stat['total_late']; ?></span></p>
                <p>缺勤: <span class="negative"><?php echo $stat['total_absent']; ?></span></p>
            </div>
            <?php endforeach; ?>
        </div>
        <!-- 考勤签到 -->
        <div class="attendance-form">
            <h2>考勤打卡</h2>
            <form id="attendanceForm">
                <input type="text" name="employee_id" placeholder="员工工号" required>
                <button type="button" onclick="submitAttendance('sign_in')">签到</button>
                <button type="button" onclick="submitAttendance('sign_out')">签退</button>
            </form>
            <div id="message"></div>
        </div>
        <!-- 考勤报表 -->
        <h2>考勤报表</h2>
        <div class="filters">
            <form method="GET">
                <label>开始日期: <input type="date" name="start_date" value="<?php echo $startDate; ?>"></label>
                <label>结束日期: <input type="date" name="end_date" value="<?php echo $endDate; ?>"></label>
                <label>部门: 
                    <select name="department">
                        <option value="">全部</option>
                        <option value="技术部">技术部</option>
                        <option value="市场部">市场部</option>
                    </select>
                </label>
                <button type="submit">查询</button>
                <a href="?export=1&start_date=<?php echo $startDate; ?>&end_date=<?php echo $endDate; ?>">导出CSV</a>
            </form>
        </div>
        <table>
            <thead>
                <tr>
                    <th>工号</th>
                    <th>姓名</th>
                    <th>部门</th>
                    <th>日期</th>
                    <th>签到时间</th>
                    <th>签退时间</th>
                    <th>状态</th>
                    <th>工时</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($report as $row): ?>
                <tr>
                    <td><?php echo $row['employee_no']; ?></td>
                    <td><?php echo $row['name']; ?></td>
                    <td><?php echo $row['department']; ?></td>
                    <td><?php echo $row['date']; ?></td>
                    <td><?php echo $row['sign_in_time'] ? date('H:i:s', strtotime($row['sign_in_time'])) : '-'; ?></td>
                    <td><?php echo $row['sign_out_time'] ? date('H:i:s', strtotime($row['sign_out_time'])) : '-'; ?></td>
                    <td><?php echo $row['status']; ?></td>
                    <td><?php echo $row['work_hours']; ?>h</td>
                </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    </div>
    <script>
    function submitAttendance(action) {
        const form = document.getElementById('attendanceForm');
        const formData = new FormData(form);
        formData.append('action', action);
        fetch(window.location.href, {
            method: 'POST',
            body: formData
        })
        .then(response => response.json())
        .then(data => {
            document.getElementById('message').textContent = data.message;
            if (data.success) {
                setTimeout(() => location.reload(), 1000);
            }
        })
        .catch(error => console.error('Error:', error));
    }
    </script>
</body>
</html>

部署建议

1. 定时任务：设置cron job自动处理异常考勤
2. 缓存优化：使用Redis缓存频繁查询的数据
3. 安全措施：实现CSRF防护、XSS过滤
4. 日志记录：记录所有考勤操作日志

这个系统可以根据实际需求进一步扩展，比如添加请假管理、加班统计、考勤规则灵活配置等功能。