本文目录导读：
我来详细介绍如何使用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>
部署建议
- 定时任务：设置cron job自动处理异常考勤
- 缓存优化：使用Redis缓存频繁查询的数据
- 安全措施：实现CSRF防护、XSS过滤
- 日志记录：记录所有考勤操作日志
这个系统可以根据实际需求进一步扩展，比如添加请假管理、加班统计、考勤规则灵活配置等功能。
版权声明：足球直播
足球直播
足球直播
足球直播
足球直播
中超直播
中超直播
中超直播
中超直播
中超直播
英超直播
英超直播
英超直播
英超直播
英超直播
德甲直播
德甲直播
德甲直播
德甲直播
德甲直播
西甲直播
西甲直播
西甲直播
西甲直播
西甲直播
意甲直播
意甲直播
意甲直播
意甲直播
意甲直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
篮球直播
篮球直播
篮球直播
篮球直播
篮球直播
足球直播
足球直播
足球直播
足球直播
足球直播
中超直播
中超直播
中超直播
中超直播
中超直播
英超直播
英超直播
英超直播
英超直播
英超直播
德甲直播
德甲直播
德甲直播
德甲直播
德甲直播
西甲直播
西甲直播
西甲直播
西甲直播
西甲直播
意甲直播
意甲直播
意甲直播
意甲直播
意甲直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
篮球直播
篮球直播
篮球直播
篮球直播
篮球直播
足球直播
足球直播
足球直播
足球直播
足球直播
中超直播
中超直播
中超直播
中超直播
中超直播
英超直播
英超直播
英超直播
英超直播
英超直播
德甲直播
德甲直播
德甲直播
德甲直播
德甲直播
西甲直播
西甲直播
西甲直播
西甲直播
西甲直播
意甲直播
意甲直播
意甲直播
意甲直播
意甲直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
篮球直播
篮球直播
篮球直播
篮球直播
篮球直播
