PHP项目财务对账如何编写统计脚本

wen PHP项目 2026-06-30 197

本文目录导读：

PHP项目财务对账如何编写统计脚本

基础对账统计脚本结构
详细统计功能实现
报表生成与导出
定时任务与自动化
监控与告警
使用示例

我来为你详细讲解PHP财务对账统计脚本的编写方法：

基础对账统计脚本结构

1 数据库表结构准备

-- 交易订单表
CREATE TABLE `orders` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `order_no` varchar(50) NOT NULL COMMENT '订单号',
    `amount` decimal(10,2) NOT NULL COMMENT '金额',
    `status` tinyint(1) NOT NULL COMMENT '状态：1-成功 2-失败 3-退款',
    `pay_time` datetime DEFAULT NULL COMMENT '支付时间',
    `create_time` datetime NOT NULL COMMENT '创建时间',
    PRIMARY KEY (`id`)
);
-- 财务流水表
CREATE TABLE `financial_records` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `order_no` varchar(50) NOT NULL COMMENT '关联订单号',
    `type` tinyint(1) NOT NULL COMMENT '类型：1-收入 2-支出 3-退款',
    `amount` decimal(10,2) NOT NULL COMMENT '金额',
    `balance` decimal(10,2) NOT NULL COMMENT '余额',
    `create_time` datetime NOT NULL,
    PRIMARY KEY (`id`)
);

2 基础对账统计类

<?php
namespace App\Service;
class FinanceReconciliationService
{
    private $db;
    public function __construct(\PDO $db)
    {
        $this->db = $db;
    }
    /**
     * 日对账统计
     */
    public function dailyReconciliation($date)
    {
        try {
            $this->db->beginTransaction();
            // 1. 统计当日订单
            $orderStats = $this->getOrderStatsByDate($date);
            // 2. 统计当日财务流水
            $financeStats = $this->getFinanceStatsByDate($date);
            // 3. 核对差异
            $differences = $this->checkDifferences($orderStats, $financeStats);
            // 4. 生成对账报告
            $report = $this->generateReconciliationReport(
                $date, 
                $orderStats, 
                $financeStats, 
                $differences
            );
            $this->db->commit();
            return [
                'success' => true,
                'data' => $report
            ];
        } catch (\Exception $e) {
            $this->db->rollBack();
            return [
                'success' => false,
                'message' => $e->getMessage()
            ];
        }
    }
    /**
     * 获取订单统计
     */
    private function getOrderStatsByDate($date)
    {
        $sql = "SELECT 
                    COUNT(*) as total_orders,
                    SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END) as success_orders,
                    SUM(CASE WHEN status = 1 THEN amount ELSE 0 END) as total_amount,
                    SUM(CASE WHEN status = 2 THEN 1 ELSE 0 END) as failed_orders,
                    SUM(CASE WHEN status = 3 THEN 1 ELSE 0 END) as refund_orders,
                    SUM(CASE WHEN status = 3 THEN amount ELSE 0 END) as refund_amount
                FROM orders 
                WHERE DATE(pay_time) = :date";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([':date' => $date]);
        return $stmt->fetch(\PDO::FETCH_ASSOC);
    }
    /**
     * 获取财务流水统计
     */
    private function getFinanceStatsByDate($date)
    {
        $sql = "SELECT 
                    SUM(CASE WHEN type = 1 THEN amount ELSE 0 END) as income_total,
                    SUM(CASE WHEN type = 2 THEN amount ELSE 0 END) as expense_total,
                    SUM(CASE WHEN type = 3 THEN amount ELSE 0 END) as refund_total
                FROM financial_records 
                WHERE DATE(create_time) = :date";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([':date' => $date]);
        return $stmt->fetch(\PDO::FETCH_ASSOC);
    }
}

详细统计功能实现

1 多维统计查询

<?php
class FinanceStatisticsService
{
    /**
     * 多维度统计
     */
    public function getMultiDimensionStats($params)
    {
        $conditions = [];
        $bindings = [];
        // 时间范围
        if (!empty($params['start_date'])) {
            $conditions[] = "o.pay_time >= :start_date";
            $bindings[':start_date'] = $params['start_date'];
        }
        if (!empty($params['end_date'])) {
            $conditions[] = "o.pay_time <= :end_date";
            $bindings[':end_date'] = $params['end_date'] . ' 23:59:59';
        }
        // 日期维度统计
        $dailyStats = $this->getDailyStats($conditions, $bindings);
        // 支付方式维度统计
        $paymentMethodStats = $this->getPaymentMethodStats($conditions, $bindings);
        // 商品分类维度统计
        $categoryStats = $this->getCategoryStats($conditions, $bindings);
        return [
            'daily' => $dailyStats,
            'payment_method' => $paymentMethodStats,
            'category' => $categoryStats
        ];
    }
    /**
     * 每日统计
     */
    private function getDailyStats($conditions, $bindings)
    {
        $where = !empty($conditions) ? 'WHERE ' . implode(' AND ', $conditions) : '';
        $sql = "SELECT 
                    DATE(o.pay_time) as stat_date,
                    COUNT(*) as order_count,
                    SUM(o.amount) as total_amount,
                    AVG(o.amount) as avg_amount,
                    COUNT(DISTINCT o.user_id) as user_count
                FROM orders o
                {$where}
                GROUP BY DATE(o.pay_time)
                ORDER BY stat_date DESC";
        $stmt = $this->db->prepare($sql);
        $stmt->execute($bindings);
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }
    /**
     * 对比分析
     */
    public function getComparisonAnalysis($startDate, $endDate)
    {
        $currentPeriod = $this->getPeriodStats($startDate, $endDate);
        // 计算上一周期
        $periodDays = (strtotime($endDate) - strtotime($startDate)) / 86400;
        $previousStart = date('Y-m-d', strtotime($startDate . " -{$periodDays} days"));
        $previousEnd = date('Y-m-d', strtotime($startDate . " -1 days"));
        $previousPeriod = $this->getPeriodStats($previousStart, $previousEnd);
        return [
            'current' => $currentPeriod,
            'previous' => $previousPeriod,
            'growth_rate' => $this->calculateGrowthRate($currentPeriod, $previousPeriod)
        ];
    }
}

2 对账差异分析

<?php
class ReconciliationAnalyzer
{
    /**
     * 对账差异详情分析
     */
    public function analyzeDifferences($date)
    {
        // 1. 找出订单有但流水没有的记录
        $missingInFinance = $this->getMissingInFinance($date);
        // 2. 找出流水有但订单没有的记录
        $missingInOrder = $this->getMissingInOrder($date);
        // 3. 金额不一致的记录
        $amountMismatch = $this->getAmountMismatch($date);
        // 4. 状态不一致的记录
        $statusMismatch = $this->getStatusMismatch($date);
        return [
            'missing_in_finance' => $missingInFinance,
            'missing_in_order' => $missingInOrder,
            'amount_mismatch' => $amountMismatch,
            'status_mismatch' => $statusMismatch,
            'summary' => [
                'total_differences' => count($missingInFinance) + count($missingInOrder) + count($amountMismatch) + count($statusMismatch),
                'financial_impact' => $this->calculateFinancialImpact(
                    $missingInFinance, 
                    $missingInOrder, 
                    $amountMismatch
                )
            ]
        ];
    }
    /**
     * 计算财务影响
     */
    private function calculateFinancialImpact($missingInFinance, $missingInOrder, $amountMismatch)
    {
        $impact = [
            'order_not_in_finance' => 0,
            'finance_not_in_order' => 0,
            'amount_difference' => 0
        ];
        foreach ($missingInFinance as $item) {
            $impact['order_not_in_finance'] += $item['amount'];
        }
        foreach ($missingInOrder as $item) {
            $impact['finance_not_in_order'] += $item['amount'];
        }
        foreach ($amountMismatch as $item) {
            $impact['amount_difference'] += abs($item['order_amount'] - $item['finance_amount']);
        }
        return $impact;
    }
    /**
     * 自动修复对账差异
     */
    public function autoFixDifferences($differences)
    {
        $fixResults = [];
        // 自动修复缺失的财务记录
        if (!empty($differences['missing_in_finance'])) {
            foreach ($differences['missing_in_finance'] as $item) {
                $result = $this->createFinanceRecord($item);
                $fixResults[] = [
                    'type' => 'create_finance',
                    'order_no' => $item['order_no'],
                    'status' => $result ? 'fixed' : 'failed'
                ];
            }
        }
        // 自动修复金额不一致
        if (!empty($differences['amount_mismatch'])) {
            foreach ($differences['amount_mismatch'] as $item) {
                $result = $this->adjustAmount($item);
                $fixResults[] = [
                    'type' => 'adjust_amount',
                    'order_no' => $item['order_no'],
                    'status' => $result ? 'fixed' : 'failed'
                ];
            }
        }
        return $fixResults;
    }
}

报表生成与导出

1 Excel报表生成

<?php
class ReportGenerator
{
    /**
     * 生成对账报表
     */
    public function generateReconciliationReport($date, $data)
    {
        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        // 设置标题
        $sheet->setCellValue('A1', "财务对账报表 - {$date}");
        $sheet->mergeCells('A1:G1');
        // 设置表头
        $headers = ['统计项', '订单系统', '财务系统', '差异', '差异率', '处理状态', '备注'];
        foreach ($headers as $key => $header) {
            $sheet->setCellValueByColumnAndRow($key + 1, 2, $header);
        }
        // 填充数据
        $row = 3;
        foreach ($data['items'] as $item) {
            $sheet->setCellValueByColumnAndRow(1, $row, $item['name']);
            $sheet->setCellValueByColumnAndRow(2, $row, $item['order_value']);
            $sheet->setCellValueByColumnAndRow(3, $row, $item['finance_value']);
            $sheet->setCellValueByColumnAndRow(4, $row, $item['difference']);
            $sheet->setCellValueByColumnAndRow(5, $row, $item['difference_rate'] . '%');
            $sheet->setCellValueByColumnAndRow(6, $row, $item['status']);
            $sheet->setCellValueByColumnAndRow(7, $row, $item['remark']);
            $row++;
        }
        // 导出文件
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        $filename = "reconciliation_{$date}.xlsx";
        $writer->save($filename);
        return $filename;
    }
    /**
     * 生成统计图表数据
     */
    public function generateChartData($stats)
    {
        return [
            'daily_trend' => [
                'type' => 'line',
                'data' => [
                    'labels' => array_column($stats['daily'], 'stat_date'),
                    'datasets' => [
                        [
                            'label' => '交易金额',
                            'data' => array_column($stats['daily'], 'total_amount')
                        ],
                        [
                            'label' => '交易笔数',
                            'data' => array_column($stats['daily'], 'order_count')
                        ]
                    ]
                ]
            ],
            'payment_distribution' => [
                'type' => 'pie',
                'data' => [
                    'labels' => array_column($stats['payment_method'], 'method_name'),
                    'datasets' => [
                        [
                            'data' => array_column($stats['payment_method'], 'total_amount')
                        ]
                    ]
                ]
            ]
        ];
    }
}

定时任务与自动化

1 定时对账任务

<?php
// cron/task/ReconciliationTask.php
class ReconciliationTask
{
    private $reconciliationService;
    private $reportGenerator;
    private $emailService;
    /**
     * 每日对账任务
     */
    public function dailyReconciliationTask()
    {
        $yesterday = date('Y-m-d', strtotime('-1 day'));
        try {
            // 1. 执行对账
            $result = $this->reconciliationService->dailyReconciliation($yesterday);
            // 2. 生成报表
            $reportFile = $this->reportGenerator->generateReconciliationReport(
                $yesterday, 
                $result['data']
            );
            // 3. 发送通知
            $this->sendReconciliationNotification($result, $reportFile);
            // 4. 记录日志
            $this->logReconciliationResult($yesterday, $result);
            return true;
        } catch (\Exception $e) {
            // 发送错误通知
            $this->sendErrorNotification($yesterday, $e->getMessage());
            return false;
        }
    }
    /**
     * 发送对账通知
     */
    private function sendReconciliationNotification($result, $reportFile)
    {
        $subject = "财务对账完成 - " . date('Y-m-d');
        $body = "
            <h3>对账完成报告</h3>
            <p>日期：{$result['data']['date']}</p>
            <p>状态：{$result['success']}</p>
            <p>订单统计：{$result['data']['order_stats']['total_orders']}笔</p>
            <p>财务统计：{$result['data']['finance_stats']['total_records']}笔</p>
            <p>差异数量：{$result['data']['differences_count']}</p>
        ";
        $this->emailService->send($subject, $body, $reportFile);
    }
}

2 配置定时任务

# crontab 配置
# 每天凌晨2点执行对账任务
0 2 * * * /usr/bin/php /path/to/project/cron/daily_reconciliation.php
# 每小时执行实时对账检查
0 * * * * /usr/bin/php /path/to/project/cron/hourly_reconciliation.php
# 每月1号执行月度对账总结
0 0 1 * * /usr/bin/php /path/to/project/cron/monthly_reconciliation.php

监控与告警

<?php
class ReconciliationMonitor
{
    /**
     * 监控对账异常
     */
    public function monitorAbnormalities()
    {
        $alerts = [];
        // 1. 检查大额差异
        $largeDifferences = $this->getLargeDifferences();
        if (!empty($largeDifferences)) {
            $alerts[] = [
                'type' => 'large_difference',
                'severity' => 'high',
                'message' => "发现大额对账差异：{$largeDifferences['amount']}元",
                'data' => $largeDifferences
            ];
        }
        // 2. 检查连续对账失败
        $consecutiveFailures = $this->getConsecutiveFailures();
        if ($consecutiveFailures > 3) {
            $alerts[] = [
                'type' => 'consecutive_failure',
                'severity' => 'critical',
                'message' => "连续{$consecutiveFailures}次对账失败"
            ];
        }
        // 3. 发送告警
        if (!empty($alerts)) {
            $this->sendAlerts($alerts);
        }
        return $alerts;
    }
    /**
     * 对账健康检查
     */
    public function healthCheck()
    {
        $status = [
            'reconciliation_enabled' => true,
            'last_reconciliation_time' => $this->getLastReconciliationTime(),
            'success_rate' => $this->calculateSuccessRate(),
            'pending_differences' => $this->getPendingDifferenceCount(),
            'average_process_time' => $this->getAverageProcessTime()
        ];
        $status['health_score'] = $this->calculateHealthScore($status);
        return $status;
    }
}

使用示例

<?php
// 使用示例
$reconciliationService = new FinanceReconciliationService($db);
// 1. 执行日对账
$result = $reconciliationService->dailyReconciliation('2024-01-15');
if ($result['success']) {
    echo "对账完成，差异数：" . $result['data']['differences_count'];
}
// 2. 获取多维统计
$statsService = new FinanceStatisticsService($db);
$stats = $statsService->getMultiDimensionStats([
    'start_date' => '2024-01-01',
    'end_date' => '2024-01-31'
]);
// 3. 生成报表
$reportGenerator = new ReportGenerator();
$reportFile = $reportGenerator->generateReconciliationReport(
    '2024-01-15',
    $result['data']
);

这套财务对账系统包含了完整的统计、对账、报表和监控功能，可以根据实际业务需求进行调整和扩展。

本文地址： https://www.bmjup.com/post/12919.html