本文目录导读：
我将为您提供一个完整的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;
}
这个系统提供了完整的财务报表生成功能,包括：
- 资产负债表 - 资产、负债、权益的完整报告
- 利润表 - 收入、费用和净利润
- 现金流量表 - 经营活动、投资活动、筹资活动
- 预算对比报告 - 预算与实际执行对比分析
- 图表数据 - 趋势分析和费用占比图表
您可以根据需要扩展功能，如添加更多的财务报表类型、自定义报告格式、PDF导出等。
