water/server/plugin/saiadmin/app/service/WaterFinanceDashboard.php

<?php

declare(strict_types=1);

namespace plugin\saiadmin\app\service;

use OpenSpout\Common\Entity\Row;
use OpenSpout\Writer\XLSX\Writer;
use plugin\saiadmin\app\model\water\WaterModel;
use plugin\saiadmin\app\model\water\WaterOrder;
use plugin\saiadmin\app\model\water\WaterCommission;
use plugin\saiadmin\app\service\finance\FinanceFilters;
use plugin\saiadmin\app\service\finance\ReceiptSources;
use plugin\saiadmin\app\service\finance\WithdrawalDemo;
use plugin\saiadmin\exception\ApiException;

/** Finance read facade; only recorded water receipts are available in the current domain. */
final class WaterFinanceDashboard
{
    private readonly \DateTimeImmutable $now;

    public function __construct(?\DateTimeImmutable $now = null)
    {
        $this->now = ($now ?? new \DateTimeImmutable())->setTimezone(new \DateTimeZone('Asia/Shanghai'));
    }

    private function period(FinanceFilters $filters)
    {
        return WaterOrder::query()->financePeriod($filters->start->format('Y-m-d H:i:s'), $filters->end->format('Y-m-d H:i:s'))
            ->financeSource($filters->source);
    }

    private function rows(FinanceFilters $filters)
    {
        return $this->period($filters)->financeDetails($filters->keyword)->orderByDesc('o.created_at')->orderByDesc('o.id');
    }

    private function totals($query): array
    {
        $row = $query->selectRaw('COUNT(*) AS count, COALESCE(SUM(o.amount_cents),0) AS cents')->first();

        return ['count' => (int) $row->count, 'cents' => (int) $row->cents];
    }

    private function formatRow(array $row, array $sources = []): array
    {
        foreach (['id', 'enterprise_id', 'buckets', 'amount_cents'] as $key) {
            $row[$key] = (int) $row[$key];
        }
        $row['is_demo'] = (bool) $row['is_demo'];
        $row['number'] = 'WS' . str_pad((string) $row['id'], 10, '0', STR_PAD_LEFT);
        $row['enterprise_name'] = $row['enterprise_name'] ?? '企业档案不存在';
        $row['type_label'] = $sources[$row['id']] ?? '线下购水登记';
        $row['status_label'] = $row['is_demo'] ? '模拟登记' : '已登记';

        return $row;
    }

    public function dashboard(array $input): array
    {
        $filters = new FinanceFilters($input, $this->now);

        return WaterModel::transaction(function () use ($filters) {
            $monthly = $this->totals($this->period($filters));
            $today = $this->totals(WaterOrder::query()->financePeriod($this->now->format('Y-m-d 00:00:00'), $this->now->format('Y-m-d H:i:s'))->financeSource($filters->source));
            $demo = $this->totals($this->period($filters)->financeSource('demo'));
            $pendingCommission = (int) WaterCommission::query()->where('status', 'pending')->sum('amount_cents');
            $days = $this->period($filters)->selectRaw('DATE(o.created_at) AS day,SUM(o.amount_cents) AS cents,COUNT(*) AS count')
                ->groupByRaw('DATE(o.created_at)')->get()->keyBy('day');
            $trend = [];
            for ($day = $filters->start;$day->format('Y-m-d') <= $filters->end->format('Y-m-d');$day = $day->modify('+1 day')) {
                $key = $day->format('Y-m-d');
                $row = $days->get($key);
                $trend[] = ['day' => $key, 'receipt_cents' => (int) ($row->cents ?? 0), 'count' => (int) ($row->count ?? 0)];
            }
            $list = $this->rows($filters)->pageResult($filters->page, $filters->limit);
            $sources = ReceiptSources::label($list['data']);
            $list['data'] = array_map(fn ($row) => $this->formatRow($row, $sources), $list['data']);

            return ['list' => $list, 'summary' => ['today_cents' => $today['cents'], 'today_count' => $today['count'],
                'month_cents' => $monthly['cents'], 'month_count' => $monthly['count'], 'demo_cents' => $demo['cents'], 'demo_count' => $demo['count'],
                'today_refund_cents' => null, 'month_refund_cents' => null, 'pending_withdrawal_cents' => null,
                'pending_commission_cents' => $pendingCommission, 'net_rate' => null],
                'trend' => $trend, 'withdrawals' => [],
                'withdrawal_demo' => (new WithdrawalDemo())->listing($filters),
            'capabilities' => ['refunds' => false, 'withdrawals' => false, 'commissions' => true,
                'rewards' => \plugin\saiadmin\app\service\promotion\PromotionPolicy::current()['enabled']],
                'period' => ['month' => $filters->month, 'start' => $filters->start->format('Y-m-d'), 'end' => $filters->end->format('Y-m-d')],
                'updated_at' => $this->now->format('Y-m-d H:i:s')];
        });
    }

    public function export(array $input): string
    {
        $filters = new FinanceFilters($input, $this->now);
        $rows = $this->rows($filters)->limit(50001)->get()->toArray();
        if (count($rows) > 50000) {
            throw new ApiException('导出超过50000条，请缩小查询范围');
        }
        $sources = ReceiptSources::label($rows);
        $path = tempnam(sys_get_temp_dir(), 'water-finance-');
        if ($path === false) {
            throw new ApiException('无法创建对账文件');
        }
        $writer = new Writer();
        $opened = false;
        try {
            $writer->openToFile($path);
            $opened = true;
            $writer->addRow(Row::fromValues(['流水号', '类型', '企业客户', '收款凭据', '套餐', '权益桶数', '金额（元）', '状态', '登记时间', '数据标记']));
            foreach ($rows as $raw) {
                $r = $this->formatRow($raw, $sources);
                $writer->addRow(Row::fromValues([$r['number'], $r['type_label'], self::excelText($r['enterprise_name']),
                    self::excelText($r['reference']), self::excelText($r['package_name']), $r['buckets'], $r['amount_cents'] / 100,
                    $r['status_label'], $r['created_at'], $r['is_demo'] ? '测试数据，无实际收款' : '未标记测试']));
            }
            $writer->close();
            $opened = false;
            $content = file_get_contents($path);
            if ($content === false) {
                throw new ApiException('读取对账文件失败');
            }

            return $content;
        } finally {
            try {
                if ($opened) {
                    $writer->close();
                }
            } finally {
                if (is_file($path)) {
                    unlink($path);
                }
            }
        }
    }

    private static function excelText(?string $value): string
    {
        $text = $value ?? '';

        return preg_match('/^[=+@\-\t\r\n]/u', $text) ? "'" . $text : $text;
    }
}