本文目录导读：
在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)
);
这个方案覆盖了请假统计的核心功能,包括个人统计、部门统计、月度报告、年度趋势图等，你可以根据实际需求调整数据库设计和统计逻辑。
版权声明：足球直播
足球直播
足球直播
足球直播
足球直播
中超直播
中超直播
中超直播
中超直播
中超直播
英超直播
英超直播
英超直播
英超直播
英超直播
德甲直播
德甲直播
德甲直播
德甲直播
德甲直播
西甲直播
西甲直播
西甲直播
西甲直播
西甲直播
意甲直播
意甲直播
意甲直播
意甲直播
意甲直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
篮球直播
篮球直播
篮球直播
篮球直播
篮球直播
足球直播
足球直播
足球直播
足球直播
足球直播
中超直播
中超直播
中超直播
中超直播
中超直播
英超直播
英超直播
英超直播
英超直播
英超直播
德甲直播
德甲直播
德甲直播
德甲直播
德甲直播
西甲直播
西甲直播
西甲直播
西甲直播
西甲直播
意甲直播
意甲直播
意甲直播
意甲直播
意甲直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
篮球直播
篮球直播
篮球直播
篮球直播
篮球直播
足球直播
足球直播
足球直播
足球直播
足球直播
中超直播
中超直播
中超直播
中超直播
中超直播
英超直播
英超直播
英超直播
英超直播
英超直播
德甲直播
德甲直播
德甲直播
德甲直播
德甲直播
西甲直播
西甲直播
西甲直播
西甲直播
西甲直播
意甲直播
意甲直播
意甲直播
意甲直播
意甲直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
篮球直播
篮球直播
篮球直播
篮球直播
篮球直播
