PHP项目如何实现预算执行分析？

wen java案例 2026-07-23 196

本文目录导读：

PHP项目如何实现预算执行分析？

核心数据结构设计
数据接入层实现
趋势分析与预警逻辑
可视化数据输出 (JSON API + Chart.js)
性能优化建议
安全与权限控制

在PHP项目中实现预算执行分析，通常涉及数据模型设计、数据抓取、计算逻辑以及可视化展示几个核心步骤,下面是一个完整的技术实现方案。

核心数据结构设计

首先需要设计数据库表来存储预算和实际执行数据。

-- 预算表
CREATE TABLE budgets (
    id INT PRIMARY KEY AUTO_INCREMENT,
    department_id INT,           -- 部门ID
    category_id INT,             -- 预算科目ID (如: 差旅费, 办公费)
    fiscal_year YEAR,            -- 预算年度 (2024)
    fiscal_month TINYINT,        -- 预算月份 (1-12), NULL表示年度预算
    budget_amount DECIMAL(15,2), -- 预算金额
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 实际执行表 (通常来自财务系统或业务系统)
CREATE TABLE actual_expenses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    department_id INT,
    category_id INT,
    expense_date DATE,
    amount DECIMAL(15,2),
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 预算科目表
CREATE TABLE budget_categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),       -- 如: 差旅费
    parent_id INT,           -- 支持科目层级 (0为顶级)
    code VARCHAR(20)         -- 科目编码
);
-- 部门表
CREATE TABLE departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    parent_id INT            -- 支持部门层级
);

数据接入层实现

<?php
class BudgetAnalyzer {
    private $db;
    public function __construct(PDO $db) {
        $this->db = $db;
    }
    /**
     * 获取指定部门/月份的执行率
     */
    public function getExecutionRate(int $departmentId, int $year, int $month): array {
        // 1. 获取当月预算总额
        $budgetStmt = $this->db->prepare("
            SELECT SUM(budget_amount) as total_budget 
            FROM budgets 
            WHERE department_id = :dept_id 
              AND fiscal_year = :year 
              AND (fiscal_month = :month OR fiscal_month IS NULL)
        ");
        $budgetStmt->execute([
            ':dept_id' => $departmentId,
            ':year' => $year,
            ':month' => $month
        ]);
        $budget = $budgetStmt->fetchColumn() ?: 0;
        // 2. 获取当月实际执行金额
        $expenseStmt = $this->db->prepare("
            SELECT COALESCE(SUM(amount), 0) as total_expense 
            FROM actual_expenses 
            WHERE department_id = :dept_id 
              AND YEAR(expense_date) = :year 
              AND MONTH(expense_date) = :month
        ");
        $expenseStmt->execute([
            ':dept_id' => $departmentId,
            ':year' => $year,
            ':month' => $month
        ]);
        $expense = $expenseStmt->fetchColumn();
        // 3. 计算执行率
        $executionRate = $budget > 0 ? round(($expense / $budget) * 100, 2) : 0;
        return [
            'budget' => $budget,
            'expense' => $expense,
            'execution_rate' => $executionRate,
            'remaining' => $budget - $expense
        ];
    }
    /**
     * 按科目维度分析
     */
    public function analyzeByCategory(int $departmentId, int $year, int $month): array {
        $sql = "
            SELECT 
                bc.id,
                bc.name as category_name,
                COALESCE(b.budget_amount, 0) as budget,
                COALESCE(e.expense, 0) as expense,
                CASE 
                    WHEN b.budget_amount > 0 
                    THEN ROUND((COALESCE(e.expense, 0) / b.budget_amount) * 100, 2) 
                    ELSE 0 
                END as execution_rate
            FROM budget_categories bc
            LEFT JOIN (
                SELECT category_id, SUM(budget_amount) as budget_amount
                FROM budgets
                WHERE department_id = :dept_id 
                  AND fiscal_year = :year 
                  AND (fiscal_month = :month OR fiscal_month IS NULL)
                GROUP BY category_id
            ) b ON bc.id = b.category_id
            LEFT JOIN (
                SELECT category_id, SUM(amount) as expense
                FROM actual_expenses
                WHERE department_id = :dept_id 
                  AND YEAR(expense_date) = :year 
                  AND MONTH(expense_date) = :month
                GROUP BY category_id
            ) e ON bc.id = e.category_id
            ORDER BY bc.name
        ";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([
            ':dept_id' => $departmentId,
            ':year' => $year,
            ':month' => $month
        ]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
}

趋势分析与预警逻辑

<?php
class BudgetTrendAnalyzer {
    /**
     * 计算近12个月的趋势数据
     */
    public function getYearlyTrend(int $departmentId, string $endDate): array {
        $trendData = [];
        $end = new DateTime($endDate);
        for ($i = 11; $i >= 0; $i--) {
            $target = clone $end;
            $target->modify("-{$i} months");
            // 获取该月份数据
            $monthData = $this->getMonthlyData(
                $departmentId, 
                (int)$target->format('Y'), 
                (int)$target->format('m')
            );
            $trendData[] = [
                'period' => $target->format('Y-m'),
                'budget' => $monthData['budget'],
                'expense' => $monthData['expense'],
                'execution_rate' => $monthData['execution_rate']
            ];
        }
        return $trendData;
    }
    /**
     * 预算预警检测
     */
    public function checkBudgetAlerts(int $departmentId, int $year): array {
        // 检测执行率超过80%的科目（可配置阈值）
        $sql = "
            SELECT 
                bc.name as category_name,
                b.budget_amount,
                e.total_expense,
                ROUND((e.total_expense / b.budget_amount) * 100, 2) as execution_rate
            FROM budgets b
            JOIN budget_categories bc ON b.category_id = bc.id
            JOIN (
                SELECT category_id, SUM(amount) as total_expense
                FROM actual_expenses
                WHERE department_id = :dept_id 
                  AND YEAR(expense_date) = :year
                GROUP BY category_id
            ) e ON b.category_id = e.category_id
            WHERE b.department_id = :dept_id 
              AND b.fiscal_year = :year
              AND b.budget_amount > 0
              AND (e.total_expense / b.budget_amount) >= 0.8
            ORDER BY execution_rate DESC
            LIMIT 10
        ";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([':dept_id' => $departmentId, ':year' => $year]);
        $alerts = $stmt->fetchAll(PDO::FETCH_ASSOC);
        // 生成预警消息
        foreach ($alerts as &$alert) {
            $alert['level'] = $alert['execution_rate'] >= 90 ? '危险' : '警告';
            $alert['message'] = "{$alert['category_name']} 预算执行率已达 {$alert['execution_rate']}%";
        }
        return $alerts;
    }
}

可视化数据输出 (JSON API + Chart.js)

<?php
// API 控制器示例
class BudgetApiController {
    public function getBoardData(Request $request): JsonResponse {
        $year = $request->input('year', date('Y'));
        $month = $request->input('month', date('m'));
        $departmentId = $request->input('department_id', 1);
        $analyzer = new BudgetAnalyzer($this->db);
        // 1. 总览数据
        $overview = $analyzer->getExecutionRate($departmentId, $year, $month);
        // 2. 科目明细
        $categoryData = $analyzer->analyzeByCategory($departmentId, $year, $month);
        // 3. 趋势数据
        $trendAnalyzer = new BudgetTrendAnalyzer($this->db);
        $trendData = $trendAnalyzer->getYearlyTrend($departmentId, "$year-$month-01");
        // 4. 预警信息
        $alerts = $trendAnalyzer->checkBudgetAlerts($departmentId, $year);
        return response()->json([
            'overview' => $overview,
            'categories' => $categoryData,
            'trend' => $trendData,
            'alerts' => $alerts
        ]);
    }
}

// 前端使用 Chart.js 展示
async function renderBudgetBoard() {
    const response = await fetch('/api/budget/board?year=2024&month=12');
    const data = await response.json();
    // 1. 进度条显示总执行率
    new Chart(document.getElementById('overviewProgress'), {
        type: 'doughnut',
        data: {
            labels: ['已执行', '剩余预算'],
            datasets: [{
                data: [data.overview.expense, data.overview.remaining],
                backgroundColor: ['#e74c3c', '#2ecc71'],
                borderWidth: 0
            }]
        },
        options: {
            cutout: '70%',
            plugins: {
                centerText: {
                    display: true,
                    text: `${data.overview.execution_rate}%`
                }
            }
        }
    });
    // 2. 科目执行率柱状图
    new Chart(document.getElementById('categoryChart'), {
        type: 'bar',
        data: {
            labels: data.categories.map(c => c.category_name),
            datasets: [{
                label: '预算金额',
                data: data.categories.map(c => c.budget),
                backgroundColor: 'rgba(54, 162, 235, 0.5)',
                borderColor: 'rgba(54, 162, 235, 1)',
                borderWidth: 1
            }, {
                label: '实际执行',
                data: data.categories.map(c => c.expense),
                backgroundColor: 'rgba(255, 99, 132, 0.5)',
                borderColor: 'rgba(255, 99, 132, 1)',
                borderWidth: 1
            }]
        },
        options: {
            scales: {
                y: {
                    beginAtZero: true,
                    ticks: {
                        callback: value => '¥' + value.toLocaleString()
                    }
                }
            }
        }
    });
    // 3. 趋势折线图
    new Chart(document.getElementById('trendChart'), {
        type: 'line',
        data: {
            labels: data.trend.map(t => t.period),
            datasets: [{
                label: '执行率 (%)',
                data: data.trend.map(t => t.execution_rate),
                borderColor: '#3498db',
                fill: false,
                tension: 0.1
            }]
        },
        options: {
            scales: {
                y: {
                    beginAtZero: true,
                    max: 100,
                    ticks: {
                        callback: value => value + '%'
                    }
                }
            }
        }
    });
    // 4. 预警列表
    const alertList = document.getElementById('alertList');
    data.alerts.forEach(alert => {
        const div = document.createElement('div');
        div.className = `alert alert-${alert.level === '危险' ? 'danger' : 'warning'}`;
        div.textContent = alert.message;
        alertList.appendChild(div);
    });
}

性能优化建议

预计算与缓存：

// 使用 Redis 缓存月度汇总数据
public function getCachedMonthlyData($departmentId, $year, $month) {
 $cacheKey = "budget:{$departmentId}:{$year}:{$month}";
 if ($cached = Redis::get($cacheKey)) {
     return json_decode($cached, true);
 }
 $data = $this->calculateMonthlyData($departmentId, $year, $month);
 Redis::setex($cacheKey, 3600, json_encode($data)); // 缓存1小时
 return $data;
}

数据库索引：

-- 关键查询字段建立联合索引
CREATE INDEX idx_budget_dept_year_month ON budgets(department_id, fiscal_year, fiscal_month);
CREATE INDEX idx_expense_dept_date ON actual_expenses(department_id, expense_date);

安全与权限控制

<?php
class BudgetAuthorization {
    public function __construct(private $user) {}
    public function canViewDepartment(int $departmentId): bool {
        // 管理员可查看所有部门
        if ($this->user->role === 'admin') return true;
        // 部门经理只能看本部门
        return $this->user->department_id === $departmentId;
    }
    public function filterDataByPermission(array $data): array {
        // 根据用户角色过滤数据粒度
        if ($this->user->role === 'manager') {
            // 经理可查看科级别细
            return $data;
        }
        // 普通员工只看汇总
        return [
            'overview' => $data['overview'],
            'alerts' => $data['alerts']
        ];
    }
}

实现预算执行分析的关键点：