IT技术社区_专业开发者平台_最新编程教程与解决方案 -沐辰社区

PHP 财务报表生成

wen PHP项目 135

本文目录导读：

PHP 财务报表生成

  1. 数据库表结构
  2. 财务报表类库
  3. 图表数据类库
  4. 使用示例
  5. HTML报表导出

我将为您提供一个完整的PHP财务报表生成系统,包含多种财务报表类型和图表可视化。

数据库表结构

-- 会计科目表
CREATE TABLE accounts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    account_code VARCHAR(20) NOT NULL UNIQUE,
    account_name VARCHAR(100) NOT NULL,
    account_type ENUM('asset', 'liability', 'equity', 'revenue', 'expense') NOT NULL,
    parent_id INT NULL,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_id) REFERENCES accounts(id)
);
-- 交易记录表
CREATE TABLE transactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    transaction_date DATE NOT NULL,
    description VARCHAR(255),
    reference_no VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 交易明细表
CREATE TABLE transaction_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    transaction_id INT NOT NULL,
    account_id INT NOT NULL,
    debit DECIMAL(15,2) DEFAULT 0,
    credit DECIMAL(15,2) DEFAULT 0,
    FOREIGN KEY (transaction_id) REFERENCES transactions(id),
    FOREIGN KEY (account_id) REFERENCES accounts(id)
);
-- 预算表
CREATE TABLE budgets (
    id INT PRIMARY KEY AUTO_INCREMENT,
    account_id INT NOT NULL,
    year INT NOT NULL,
    month INT NOT NULL,
    budget_amount DECIMAL(15,2),
    UNIQUE KEY (account_id, year, month),
    FOREIGN KEY (account_id) REFERENCES accounts(id)
);

财务报表类库

<?php
// FinancialReportGenerator.php
class FinancialReportGenerator {
    private $pdo;
    private $companyInfo;
    public function __construct(PDO $pdo, array $companyInfo) {
        $this->pdo = $pdo;
        $this->companyInfo = $companyInfo;
    }
    /**
     * 生成资产负债表
     */
    public function getBalanceSheet($asOfDate) {
        $report = [
            'company' => $this->companyInfo,
            'date' => $asOfDate,
            'assets' => [],
            'liabilities' => [],
            'equity' => [],
            'total_assets' => 0,
            'total_liabilities' => 0,
            'total_equity' => 0
        ];
        // 获取资产科目余额
        $report['assets'] = $this->getAccountBalances($asOfDate, 'asset');
        $report['total_assets'] = array_sum(array_column($report['assets'], 'balance'));
        // 获取负债科目余额
        $report['liabilities'] = $this->getAccountBalances($asOfDate, 'liability');
        $report['total_liabilities'] = array_sum(array_column($report['liabilities'], 'balance'));
        // 获取权益科目余额
        $report['equity'] = $this->getAccountBalances($asOfDate, 'equity');
        $report['total_equity'] = array_sum(array_column($report['equity'], 'balance'));
        // 计算净利润
        $netIncome = $this->getNetIncome($asOfDate, $asOfDate);
        $report['net_income'] = $netIncome;
        $report['total_equity'] += $netIncome;
        return $report;
    }
    /**
     * 生成利润表
     */
    public function getIncomeStatement($startDate, $endDate) {
        $report = [
            'company' => $this->companyInfo,
            'period' => [
                'start' => $startDate,
                'end' => $endDate
            ],
            'revenue' => [],
            'expenses' => [],
            'total_revenue' => 0,
            'total_expenses' => 0,
            'net_income' => 0
        ];
        // 获取收入
        $report['revenue'] = $this->getIncomeStatementItems($startDate, $endDate, 'revenue');
        $report['total_revenue'] = array_sum(array_column($report['revenue'], 'amount'));
        // 获取费用
        $report['expenses'] = $this->getIncomeStatementItems($startDate, $endDate, 'expense');
        $report['total_expenses'] = array_sum(array_column($report['expenses'], 'amount'));
        // 计算净利润
        $report['net_income'] = $report['total_revenue'] - $report['total_expenses'];
        return $report;
    }
    /**
     * 生成现金流量表（间接法）
     */
    public function getCashFlowStatement($startDate, $endDate) {
        $report = [
            'company' => $this->companyInfo,
            'period' => [
                'start' => $startDate,
                'end' => $endDate
            ],
            'operating' => [],
            'investing' => [],
            'financing' => [],
            'net_cash_flow' => 0
        ];
        // 经营活动现金流
        $report['operating'] = $this->calculateOperatingCashFlow($startDate, $endDate);
        // 投资活动现金流
        $report['investing'] = $this->calculateInvestingCashFlow($startDate, $endDate);
        // 筹资活动现金流
        $report['financing'] = $this->calculateFinancingCashFlow($startDate, $endDate);
        $report['net_cash_flow'] = 
            $report['operating']['net'] + 
            $report['investing']['net'] + 
            $report['financing']['net'];
        return $report;
    }
    /**
     * 获取科目余额
     */
    private function getAccountBalances($asOfDate, $accountType) {
        $sql = "
            SELECT 
                a.id,
                a.account_code,
                a.account_name,
                a.account_type,
                COALESCE(SUM(ti.debit - ti.credit), 0) as balance
            FROM accounts a
            LEFT JOIN transaction_items ti ON a.id = ti.account_id
            LEFT JOIN transactions t ON ti.transaction_id = t.id
                AND t.transaction_date <= :asOfDate
            WHERE a.account_type = :accountType AND a.is_active = 1
            GROUP BY a.id, a.account_code, a.account_name, a.account_type
            HAVING balance != 0
            ORDER BY a.account_code
        ";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([
            ':asOfDate' => $asOfDate,
            ':accountType' => $accountType
        ]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    /**
     * 获取利润表项目
     */
    private function getIncomeStatementItems($startDate, $endDate, $accountType) {
        $sql = "
            SELECT 
                a.id,
                a.account_code,
                a.account_name,
                COALESCE(SUM(
                    CASE 
                        WHEN a.account_type = 'revenue' THEN ti.credit - ti.debit
                        ELSE ti.debit - ti.credit
                    END
                ), 0) as amount
            FROM accounts a
            LEFT JOIN transaction_items ti ON a.id = ti.account_id
            LEFT JOIN transactions t ON ti.transaction_id = t.id
                AND t.transaction_date BETWEEN :startDate AND :endDate
            WHERE a.account_type = :accountType AND a.is_active = 1
            GROUP BY a.id, a.account_code, a.account_name
            HAVING amount != 0
            ORDER BY a.account_code
        ";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([
            ':startDate' => $startDate,
            ':endDate' => $endDate,
            ':accountType' => $accountType
        ]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    /**
     * 计算净利润
     */
    private function getNetIncome($startDate, $endDate) {
        $sql = "
            SELECT 
                COALESCE(
                    SUM(CASE WHEN a.account_type = 'revenue' THEN ti.credit - ti.debit ELSE 0 END) -
                    SUM(CASE WHEN a.account_type = 'expense' THEN ti.debit - ti.credit ELSE 0 END),
                    0
                ) as net_income
            FROM accounts a
            LEFT JOIN transaction_items ti ON a.id = ti.account_id
            LEFT JOIN transactions t ON ti.transaction_id = t.id
                AND t.transaction_date BETWEEN :startDate AND :endDate
            WHERE a.account_type IN ('revenue', 'expense')
        ";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([
            ':startDate' => $startDate,
            ':endDate' => $endDate
        ]);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        return $result['net_income'] ?? 0;
    }
    /**
     * 计算经营活动现金流
     */
    private function calculateOperatingCashFlow($startDate, $endDate) {
        $netIncome = $this->getNetIncome($startDate, $endDate);
        $sql = "
            SELECT 
                a.account_name,
                COALESCE(SUM(CASE WHEN a.account_type IN ('asset') THEN ti.debit - ti.credit 
                                WHEN a.account_type IN ('liability') THEN ti.credit - ti.debit 
                                ELSE 0 END), 0) as adjustment
            FROM accounts a
            LEFT JOIN transaction_items ti ON a.id = ti.account_id
            LEFT JOIN transactions t ON ti.transaction_id = t.id
                AND t.transaction_date BETWEEN :startDate AND :endDate
            WHERE a.account_type IN ('asset', 'liability')
            GROUP BY a.id, a.account_name
            HAVING adjustment != 0
        ";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':startDate' => $startDate, ':endDate' => $endDate]);
        $adjustments = $stmt->fetchAll(PDO::FETCH_ASSOC);
        $totalAdjustment = array_sum(array_column($adjustments, 'adjustment'));
        return [
            'items' => [
                'net_income' => $netIncome,
                'adjustments' => $adjustments
            ],
            'net' => $netIncome + $totalAdjustment
        ];
    }
    /**
     * 计算投资活动现金流
     */
    private function calculateInvestingCashFlow($startDate, $endDate) {
        // 实现投资活动现金流计算
        return [
            'items' => [],
            'net' => 0
        ];
    }
    /**
     * 计算筹资活动现金流
     */
    private function calculateFinancingCashFlow($startDate, $endDate) {
        // 实现筹资活动现金流计算
        return [
            'items' => [],
            'net' => 0
        ];
    }
    /**
     * 生成预算对比报告
     */
    public function getBudgetReport($year, $month = null) {
        $sql = "
            SELECT 
                a.account_code,
                a.account_name,
                a.account_type,
                b.budget_amount,
                COALESCE(SUM(
                    CASE 
                        WHEN a.account_type = 'revenue' THEN ti.credit - ti.debit
                        ELSE ti.debit - ti.credit
                    END
                ), 0) as actual_amount,
                (b.budget_amount - COALESCE(SUM(
                    CASE 
                        WHEN a.account_type = 'revenue' THEN ti.credit - ti.debit
                        ELSE ti.debit - ti.credit
                    END
                ), 0)) as variance
            FROM accounts a
            LEFT JOIN budgets b ON a.id = b.account_id
                AND b.year = :year
                AND (:month IS NULL OR b.month = :month)
            LEFT JOIN transaction_items ti ON a.id = ti.account_id
            LEFT JOIN transactions t ON ti.transaction_id = t.id
                AND YEAR(t.transaction_date) = :year
                AND (:month IS NULL OR MONTH(t.transaction_date) = :month)
            WHERE a.is_active = 1 AND b.id IS NOT NULL
            GROUP BY a.id, a.account_code, a.account_name, a.account_type, b.budget_amount
            ORDER BY a.account_code
        ";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([
            ':year' => $year,
            ':month' => $month
        ]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
}

图表数据类库

<?php
// ChartDataGenerator.php
class ChartDataGenerator {
    private $pdo;
    public function __construct(PDO $pdo) {
        $this->pdo = $pdo;
    }
    /**
     * 获取月度收入支出趋势数据
     */
    public function getMonthlyRevenueExpense($year) {
        $sql = "
            SELECT 
                MONTH(t.transaction_date) as month,
                COALESCE(SUM(
                    CASE WHEN a.account_type = 'revenue' THEN ti.credit - ti.debit ELSE 0 END
                ), 0) as revenue,
                COALESCE(SUM(
                    CASE WHEN a.account_type = 'expense' THEN ti.debit - ti.credit ELSE 0 END
                ), 0) as expense
            FROM transactions t
            JOIN transaction_items ti ON t.id = ti.transaction_id
            JOIN accounts a ON ti.account_id = a.id
            WHERE YEAR(t.transaction_date) = :year
            GROUP BY MONTH(t.transaction_date)
            ORDER BY month
        ";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':year' => $year]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    /**
     * 获取费用占比数据
     */
    public function getExpenseBreakdown($startDate, $endDate) {
        $sql = "
            SELECT 
                a.account_name,
                COALESCE(SUM(ti.debit - ti.credit), 0) as amount
            FROM accounts a
            JOIN transaction_items ti ON a.id = ti.account_id
            JOIN transactions t ON ti.transaction_id = t.id
            WHERE a.account_type = 'expense'
                AND t.transaction_date BETWEEN :startDate AND :endDate
            GROUP BY a.id, a.account_name
            HAVING amount > 0
            ORDER BY amount DESC
            LIMIT 10
        ";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':startDate' => $startDate, ':endDate' => $endDate]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
}

使用示例

<?php
// report_controller.php
// 数据库连接
$dsn = 'mysql:host=localhost;dbname=accounting;charset=utf8';
$pdo = new PDO($dsn, 'username', 'password');
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
// 公司信息
$companyInfo = [
    'name' => '示例科技有限公司',
    'address' => '北京市朝阳区xx路xx号',
    'phone' => '010-88888888',
    'tax_id' => '91110000XXXXXXXXXX'
];
// 创建报表生成器
$reportGenerator = new FinancialReportGenerator($pdo, $companyInfo);
$chartData = new ChartDataGenerator($pdo);
// 生成资产负债表
$balanceSheet = $reportGenerator->getBalanceSheet('2024-06-30');
// 生成利润表
$incomeStatement = $reportGenerator->getIncomeStatement('2024-01-01', '2024-06-30');
// 生成现金流量表
$cashFlow = $reportGenerator->getCashFlowStatement('2024-01-01', '2024-06-30');
// 生成预算对比报告
$budgetReport = $reportGenerator->getBudgetReport(2024, 6);
// 图表数据
$monthlyTrend = $chartData->getMonthlyRevenueExpense(2024);
$expenseBreakdown = $chartData->getExpenseBreakdown('2024-01-01', '2024-06-30');

HTML报表导出

<?php
// report_export.php
// 资产负债表
function exportBalanceSheet($reportData) {
    $html = '<h1>' . $reportData['company']['name'] . '</h1>';
    $html .= '<h2>资产负债表</h2>';
    $html .= '<p>截至日期：' . $reportData['date'] . '</p>';
    $html .= '<h3>资产</h3>';
    $html .= '<table border="1">';
    $html .= '<tr><th>科目编码</th><th>科目名称</th><th>余额</th></tr>';
    foreach ($reportData['assets'] as $asset) {
        $html .= '<tr>';
        $html .= '<td>' . $asset['account_code'] . '</td>';
        $html .= '<td>' . $asset['account_name'] . '</td>';
        $html .= '<td>' . number_format($asset['balance'], 2) . '</td>';
        $html .= '</tr>';
    }
    $html .= '<tr><td colspan="2">资产合计</td><td>' . number_format($reportData['total_assets'], 2) . '</td></tr>';
    $html .= '</table>';
    return $html;
}
// 利润表
function exportIncomeStatement($reportData) {
    $html = '<h1>' . $reportData['company']['name'] . '</h1>';
    $html .= '<h2>利润表</h2>';
    $html .= '<p>期间：' . $reportData['period']['start'] . ' 至 ' . $reportData['period']['end'] . '</p>';
    $html .= '<h3>收入</h3>';
    $html .= '<table border="1">';
    $html .= '<tr><th>科目编码</th><th>科目名称</th><th>金额</th></tr>';
    foreach ($reportData['revenue'] as $item) {
        $html .= '<tr>';
        $html .= '<td>' . $item['account_code'] . '</td>';
        $html .= '<td>' . $item['account_name'] . '</td>';
        $html .= '<td>' . number_format($item['amount'], 2) . '</td>';
        $html .= '</tr>';
    }
    $html .= '<tr><td colspan="2">收入合计</td><td>' . number_format($reportData['total_revenue'], 2) . '</td></tr>';
    $html .= '</table>';
    return $html;
}

这个系统提供了完整的财务报表生成功能,包括：

  1. 资产负债表 - 资产、负债、权益的完整报告
  2. 利润表 - 收入、费用和净利润
  3. 现金流量表 - 经营活动、投资活动、筹资活动
  4. 预算对比报告 - 预算与实际执行对比分析
  5. 图表数据 - 趋势分析和费用占比图表

您可以根据需要扩展功能，如添加更多的财务报表类型、自定义报告格式、PDF导出等。

版权声明：NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 NBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 CBA直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 足球直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播 英超直播

上一篇PHP 发票管理模块

下一篇PHP 订单模块怎么设计

相关文章

抱歉，评论功能暂时关闭!

友情链接攻略窝 · 礼包窝 · 配置窝 · 换算窝 · 算法窝 · 查文窝 · 手册窝 · 问答窝 · 全文搜 · 手册库
相关站点礼包窝 · 查文窝