本文目录导读：
在PHP项目中实现请假统计,通常需要结合数据库设计、业务逻辑处理和前端展示，以下是完整的实现方案，包含数据库设计、核心PHP代码和统计逻辑。
数据库表设计
请假申请表 (leave_requests)
CREATE TABLE `leave_requests` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL COMMENT '申请人ID', `leave_type` tinyint(1) NOT NULL COMMENT '请假类型 1:年假 2:事假 3:病假 4:调休', `start_date` date NOT NULL COMMENT '开始日期', `end_date` date NOT NULL COMMENT '结束日期', `total_days` decimal(5,1) NOT NULL COMMENT '请假天数', `reason` text COMMENT '请假原因', `status` tinyint(1) DEFAULT '0' COMMENT '状态 0:待审批 1:已批准 2:已驳回', `approver_id` int(11) DEFAULT NULL COMMENT '审批人ID', `create_time` datetime DEFAULT CURRENT_TIMESTAMP, `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_user_id` (`user_id`), KEY `idx_status` (`status`), KEY `idx_date` (`start_date`,`end_date`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
用户年度额度表 (leave_quotas)
CREATE TABLE `leave_quotas` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `year` year(4) NOT NULL, `annual_leave` decimal(5,1) DEFAULT '5.0' COMMENT '年假额度(天)', `personal_leave` decimal(5,1) DEFAULT '10.0' COMMENT '事假额度', `sick_leave` decimal(5,1) DEFAULT '15.0' COMMENT '病假额度', PRIMARY KEY (`id`), UNIQUE KEY `uk_user_year` (`user_id`,`year`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
核心统计功能实现
1 请假数据查询类 (LeaveStatistics.php)
<?php
namespace App\Services;
use App\Models\LeaveRequest;
use App\Models\LeaveQuota;
use Carbon\Carbon;
class LeaveStatistics
{
private $db;
public function __construct($db)
{
$this->db = $db;
}
/**
* 获取用户指定时间范围内的请假统计
* @param int $userId
* @param string $startDate
* @param string $endDate
* @return array
*/
public function getUserLeaveStats($userId, $startDate, $endDate)
{
$sql = "SELECT
leave_type,
COUNT(*) as total_requests,
SUM(total_days) as total_days,
SUM(CASE WHEN status = 1 THEN total_days ELSE 0 END) as approved_days,
SUM(CASE WHEN status = 2 THEN total_days ELSE 0 END) as rejected_days
FROM leave_requests
WHERE user_id = ?
AND start_date >= ?
AND end_date <= ?
AND status IN (1, 2)
GROUP BY leave_type";
$stmt = $this->db->prepare($sql);
$stmt->execute([$userId, $startDate, $endDate]);
$results = $stmt->fetchAll(\PDO::FETCH_ASSOC);
// 格式化数据
$leaveTypes = [1 => 'annual', 2 => 'personal', 3 => 'sick', 4 => 'compensatory'];
$stats = [];
foreach ($leaveTypes as $key => $type) {
$stats[$type] = [
'total_requests' => 0,
'total_days' => 0,
'approved_days' => 0,
'rejected_days' => 0
];
}
foreach ($results as $row) {
$type = $leaveTypes[$row['leave_type']];
$stats[$type]['total_requests'] = $row['total_requests'];
$stats[$type]['total_days'] = $row['total_days'];
$stats[$type]['approved_days'] = $row['approved_days'];
$stats[$type]['rejected_days'] = $row['rejected_days'];
}
return $stats;
}
/**
* 获取部门请假统计
* @param int $departmentId
* @param string $startDate
* @param string $endDate
* @return array
*/
public function getDepartmentStats($departmentId, $startDate, $endDate)
{
$sql = "SELECT
u.department_id,
d.name as department_name,
lr.leave_type,
COUNT(*) as total_requests,
SUM(lr.total_days) as total_days,
COUNT(DISTINCT lr.user_id) as unique_users
FROM leave_requests lr
JOIN users u ON lr.user_id = u.id
JOIN departments d ON u.department_id = d.id
WHERE u.department_id = ?
AND lr.start_date >= ?
AND lr.end_date <= ?
AND lr.status = 1
GROUP BY lr.leave_type
ORDER BY total_days DESC";
$stmt = $this->db->prepare($sql);
$stmt->execute([$departmentId, $startDate, $endDate]);
return $stmt->fetchAll(\PDO::FETCH_ASSOC);
}
/**
* 计算用户在指定年份的剩余假期
* @param int $userId
* @param int $year
* @return array
*/
public function getRemainingLeave($userId, $year)
{
// 获取年度额度
$quota = $this->getUserQuota($userId, $year);
// 获取已使用的假期
$used = $this->getUsedLeave($userId, $year);
return [
'annual' => [
'quota' => $quota['annual_leave'],
'used' => $used['annual'] ?? 0,
'remaining' => $quota['annual_leave'] - ($used['annual'] ?? 0)
],
'personal' => [
'quota' => $quota['personal_leave'],
'used' => $used['personal'] ?? 0,
'remaining' => $quota['personal_leave'] - ($used['personal'] ?? 0)
],
'sick' => [
'quota' => $quota['sick_leave'],
'used' => $used['sick'] ?? 0,
'remaining' => $quota['sick_leave'] - ($used['sick'] ?? 0)
]
];
}
/**
* 获取用户年度额度
*/
private function getUserQuota($userId, $year)
{
$sql = "SELECT * FROM leave_quotas WHERE user_id = ? AND year = ?";
$stmt = $this->db->prepare($sql);
$stmt->execute([$userId, $year]);
$quota = $stmt->fetch(\PDO::FETCH_ASSOC);
// 如果没有设置额度，返回默认值
return $quota ?: [
'annual_leave' => 5.0,
'personal_leave' => 10.0,
'sick_leave' => 15.0
];
}
/**
* 获取用户已使用的假期
*/
private function getUsedLeave($userId, $year)
{
$sql = "SELECT
leave_type,
SUM(total_days) as used_days
FROM leave_requests
WHERE user_id = ?
AND YEAR(start_date) = ?
AND status = 1
GROUP BY leave_type";
$stmt = $this->db->prepare($sql);
$stmt->execute([$userId, $year]);
$results = $stmt->fetchAll(\PDO::FETCH_ASSOC);
$used = [];
foreach ($results as $row) {
$typeMap = [1 => 'annual', 2 => 'personal', 3 => 'sick'];
$used[$typeMap[$row['leave_type']]] = $row['used_days'];
}
return $used;
}
/**
* 生成月度统计报告
* @param int $year
* @param int $month
* @return array
*/
public function getMonthlyReport($year, $month)
{
$startDate = Carbon::create($year, $month, 1)->startOfMonth();
$endDate = Carbon::create($year, $month, 1)->endOfMonth();
$sql = "SELECT
d.name as department_name,
COUNT(CASE WHEN lr.leave_type = 1 THEN 1 END) as annual_count,
COUNT(CASE WHEN lr.leave_type = 2 THEN 1 END) as personal_count,
COUNT(CASE WHEN lr.leave_type = 3 THEN 1 END) as sick_count,
COUNT(CASE WHEN lr.leave_type = 4 THEN 1 END) as compensatory_count,
COUNT(*) as total_requests,
SUM(lr.total_days) as total_days
FROM leave_requests lr
JOIN users u ON lr.user_id = u.id
JOIN departments d ON u.department_id = d.id
WHERE lr.start_date <= ?
AND lr.end_date >= ?
AND lr.status = 1
GROUP BY d.id, d.name
ORDER BY total_days DESC";
$stmt = $this->db->prepare($sql);
$stmt->execute([$endDate, $startDate]);
return $stmt->fetchAll(\PDO::FETCH_ASSOC);
}
}
2 统计控制器示例 (LeaveController.php)
<?php
namespace App\Controllers;
use App\Services\LeaveStatistics;
use Carbon\Carbon;
class LeaveController
{
private $statService;
public function __construct($db)
{
$this->statService = new LeaveStatistics($db);
}
/**
* 获取个人请假统计
*/
public function personalStats($userId, $year = null, $month = null)
{
$year = $year ?: date('Y');
$month = $month ?: date('m');
// 月度统计
$startDate = Carbon::create($year, $month, 1)->startOfMonth()->format('Y-m-d');
$endDate = Carbon::create($year, $month, 1)->endOfMonth()->format('Y-m-d');
$monthlyStats = $this->statService->getUserLeaveStats($userId, $startDate, $endDate);
// 年度剩余假期
$remaining = $this->statService->getRemainingLeave($userId, $year);
// 年度统计
$yearStart = Carbon::create($year, 1, 1)->format('Y-m-d');
$yearEnd = Carbon::create($year, 12, 31)->format('Y-m-d');
$yearlyStats = $this->statService->getUserLeaveStats($userId, $yearStart, $yearEnd);
return [
'monthly' => $monthlyStats,
'yearly' => $yearlyStats,
'remaining' => $remaining
];
}
/**
* 获取部门统计
*/
public function departmentStats($departmentId, $year = null)
{
$year = $year ?: date('Y');
$startDate = Carbon::create($year, 1, 1)->format('Y-m-d');
$endDate = Carbon::create($year, 12, 31)->format('Y-m-d');
$stats = $this->statService->getDepartmentStats($departmentId, $startDate, $endDate);
// 计算趋势数据（按月份）
$monthlyTrend = [];
for ($i = 1; $i <= 12; $i++) {
$monthStart = Carbon::create($year, $i, 1)->startOfMonth();
$monthEnd = Carbon::create($year, $i, 1)->endOfMonth();
$monthlyStats = $this->statService->getDepartmentStats(
$departmentId,
$monthStart,
$monthEnd
);
$monthlyTrend[] = [
'month' => $i,
'total_days' => array_sum(array_column($monthlyStats, 'total_days')),
'total_requests' => array_sum(array_column($monthlyStats, 'total_requests'))
];
}
return [
'yearly' => $stats,
'monthlyTrend' => $monthlyTrend
];
}
}
前端展示示例
Vue.js 统计面板组件
<template>
<div class="leave-statistics">
<!-- 月度统计卡片 -->
<div class="stats-cards">
<div v-for="(stats, type) in monthlyStats" :key="type" class="stat-card">
<div class="stat-title">{{ leaveTypeNames[type] }}</div>
<div class="stat-value">{{ stats.approved_days }}天</div>
<div class="stat-detail">
<span>申请: {{ stats.total_requests }}次</span>
<span>已批: {{ stats.approved_days }}天</span>
<span>驳回: {{ stats.rejected_days }}天</span>
</div>
<!-- 进度条 -->
<div class="progress-bar">
<div
class="progress-fill"
:style="{ width: getProgress(stats, remaining[type]) + '%' }"
></div>
</div>
</div>
</div>
<!-- 年度趋势图 -->
<div class="trend-chart">
<h3>月度请假趋势 ({{ currentYear }}年)</h3>
<canvas ref="trendChart"></canvas>
</div>
</div>
</template>
<script>
import Chart from 'chart.js/auto'
export default {
data() {
return {
monthlyStats: {},
remaining: {},
currentYear: new Date().getFullYear(),
leaveTypeNames: {
annual: '年假',
personal: '事假',
sick: '病假',
compensatory: '调休'
},
chart: null
}
},
methods: {
async fetchStats() {
try {
const response = await axios.get('/api/leave/stats', {
params: {
user_id: this.currentUser.id,
year: this.currentYear,
month: new Date().getMonth() + 1
}
})
this.monthlyStats = response.data.monthly
this.remaining = response.data.remaining
this.renderChart(response.data)
} catch (error) {
console.error('Failed to fetch leave stats:', error)
}
},
getProgress(stats, quota) {
if (!quota || quota.quota === 0) return 0
return Math.min((stats.approved_days / quota.quota) * 100, 100)
},
renderChart(data) {
if (this.chart) {
this.chart.destroy()
}
const ctx = this.$refs.trendChart.getContext('2d')
const months = ['1月','2月','3月','4月','5月','6月','7月','8月','9月','10月','11月','12月']
this.chart = new Chart(ctx, {
type: 'line',
data: {
labels: months,
datasets: [
{
label: '请假天数',
data: data.monthlyTrend.map(item => item.total_days),
borderColor: '#4CAF50',
backgroundColor: 'rgba(76, 175, 80, 0.1)',
tension: 0.4
},
{
label: '请假次数',
data: data.monthlyTrend.map(item => item.total_requests),
borderColor: '#2196F3',
backgroundColor: 'rgba(33, 150, 243, 0.1)',
tension: 0.4
}
]
},
options: {
responsive: true,
plugins: {
legend: {
position: 'top'
}
},
scales: {
y: {
beginAtZero: true,
title: {
display: true,
text: '数量'
}
}
}
}
})
}
},
mounted() {
this.fetchStats()
},
beforeUnmount() {
if (this.chart) {
this.chart.destroy()
}
}
}
</script>
<style scoped>
.leave-statistics {
padding: 20px;
}
.stats-cards {
display: grid;
grid-template-columns: repeat(auto-fit, minmax(250px, 1fr));
gap: 20px;
margin-bottom: 30px;
}
.stat-card {
background: #fff;
border-radius: 8px;
padding: 20px;
box-shadow: 0 2px 4px rgba(0,0,0,0.1);
}
.stat-title {
font-size: 14px;
color: #666;
margin-bottom: 10px;
}
.stat-value {
font-size: 28px;
font-weight: bold;
color: #333;
margin-bottom: 10px;
}
.stat-detail {
display: flex;
justify-content: space-between;
font-size: 12px;
color: #999;
margin-bottom: 10px;
}
.progress-bar {
height: 4px;
background: #e0e0e0;
border-radius: 2px;
overflow: hidden;
}
.progress-fill {
height: 100%;
background: linear-gradient(90deg, #4CAF50, #8BC34A);
border-radius: 2px;
transition: width 0.3s ease;
}
.trend-chart {
background: #fff;
border-radius: 8px;
padding: 20px;
box-shadow: 0 2px 4px rgba(0,0,0,0.1);
}
.trend-chart h3 {
margin-bottom: 20px;
color: #333;
}
</style>
关键实现要点
日期计算
// 计算两个日期之间的工作日天数（排除周末）
function getWorkingDays($startDate, $endDate) {
$workingDays = 0;
$current = new DateTime($startDate);
$end = new DateTime($endDate);
$end->modify('+1 day'); // 包含结束日期
while ($current < $end) {
$dayOfWeek = $current->format('N');
if ($dayOfWeek < 6) { // 1-5 为工作日
$workingDays++;
}
$current->modify('+1 day');
}
return $workingDays;
}
// 排除节假日（需要维护节假日表）
function getLeaveDaysExcludingHolidays($startDate, $endDate, $holidays) {
$days = 0;
$current = new DateTime($startDate);
$end = new DateTime($endDate);
$end->modify('+1 day');
while ($current < $end) {
$dayOfWeek = $current->format('N');
$dateStr = $current->format('Y-m-d');
// 非周末且非节假日算作请假天数
if ($dayOfWeek < 6 && !in_array($dateStr, $holidays)) {
$days++;
}
$current->modify('+1 day');
}
return $days;
}
性能优化
-- 创建索引以加速统计查询
CREATE INDEX idx_user_date_status ON leave_requests(user_id, start_date, end_date, status);
CREATE INDEX idx_department_date ON leave_requests(department_id, start_date, status);
-- 使用物化视图或汇总表（对于大数据量）
CREATE TABLE leave_daily_summary (
date DATE,
department_id INT,
leave_type TINYINT,
total_count INT,
total_days DECIMAL(10,1),
PRIMARY KEY (date, department_id, leave_type)
);
这个方案覆盖了请假统计的核心功能,包括个人统计、部门统计、月度报告、年度趋势图等，你可以根据实际需求调整数据库设计和统计逻辑。
