269 lines
9.9 KiB
PHP
269 lines
9.9 KiB
PHP
<?php
|
|
|
|
namespace Modules\EcommerceFinance\Repositories;
|
|
|
|
use Carbon\CarbonImmutable;
|
|
use Illuminate\Database\ConnectionInterface;
|
|
use Illuminate\Database\DatabaseManager;
|
|
use Illuminate\Database\Query\Builder;
|
|
use Illuminate\Support\Facades\Schema;
|
|
|
|
class AlibabaAccountBalanceRepository
|
|
{
|
|
public const TABLE = 'alibaba_account_balance_snapshots';
|
|
|
|
private const BILL_TABLE = 'js_alibaba_bill';
|
|
|
|
private const CUTOFF_DATE = '2026-06-30';
|
|
|
|
private const FIRST_CALCULATED_MONTH = '2026-07';
|
|
|
|
private readonly ConnectionInterface $connection;
|
|
|
|
public function __construct(
|
|
DatabaseManager $database,
|
|
private readonly MarketingAccountBillMirrorRepository $billMirror,
|
|
) {
|
|
$this->connection = $database->connection(
|
|
(string) config('upstream_mirrors.connection', 'business_mirror')
|
|
);
|
|
}
|
|
|
|
public function usesBusinessMirror(): bool
|
|
{
|
|
return $this->hasTable(self::TABLE);
|
|
}
|
|
|
|
/** @return string[] */
|
|
public function storeOptions(): array
|
|
{
|
|
if (! $this->usesBusinessMirror()) {
|
|
return [];
|
|
}
|
|
|
|
$stores = $this->connection->table(self::TABLE)
|
|
->whereNotNull('store_name')
|
|
->where('store_name', '<>', '')
|
|
->distinct()
|
|
->pluck('store_name')
|
|
->map(static fn (mixed $value): string => (string) $value)
|
|
->all();
|
|
|
|
if ($this->billMirrorReady()) {
|
|
$billStores = $this->connection->table(self::BILL_TABLE)
|
|
->where('tradeDay', '>=', self::FIRST_CALCULATED_MONTH . '-01')
|
|
->whereNotNull('shopName')
|
|
->where('shopName', '<>', '')
|
|
->distinct()
|
|
->pluck('shopName')
|
|
->map(static fn (mixed $value): string => (string) $value)
|
|
->all();
|
|
$stores = array_merge($stores, $billStores);
|
|
}
|
|
|
|
$stores = array_values(array_unique($stores));
|
|
sort($stores, SORT_NATURAL);
|
|
|
|
return $stores;
|
|
}
|
|
|
|
/** @return array{rows:array<int,array<string,mixed>>,total:int} */
|
|
public function paginateMonthlyBalances(
|
|
?string $storeName = null,
|
|
?string $month = null,
|
|
int $page = 1,
|
|
int $pageSize = 25,
|
|
): array {
|
|
if (! $this->usesBusinessMirror()) {
|
|
return ['rows' => [], 'total' => 0];
|
|
}
|
|
|
|
$month = $this->normalizeMonth($month);
|
|
$rows = [];
|
|
|
|
if ($month === null || $month < self::FIRST_CALCULATED_MONTH) {
|
|
$rows = $this->historicalRows($storeName, $month);
|
|
}
|
|
|
|
if (($month === null || $month >= self::FIRST_CALCULATED_MONTH)
|
|
&& $this->billMirrorReady()) {
|
|
$rows = array_merge($rows, $this->calculatedRows($storeName, $month));
|
|
}
|
|
|
|
usort($rows, static function (array $left, array $right): int {
|
|
$dateOrder = strcmp((string) $right['date'], (string) $left['date']);
|
|
|
|
return $dateOrder !== 0
|
|
? $dateOrder
|
|
: strnatcmp((string) $left['store_name'], (string) $right['store_name']);
|
|
});
|
|
|
|
$page = max($page, 1);
|
|
$pageSize = min(max($pageSize, 1), 5000);
|
|
|
|
return [
|
|
'rows' => array_slice($rows, ($page - 1) * $pageSize, $pageSize),
|
|
'total' => count($rows),
|
|
];
|
|
}
|
|
|
|
/** @return array<int,array<string,mixed>> */
|
|
private function historicalRows(?string $storeName, ?string $month): array
|
|
{
|
|
$query = $this->connection->table(self::TABLE)
|
|
->where('balance_date', '<=', self::CUTOFF_DATE);
|
|
$this->applyHistoricalFilters($query, $storeName, $month);
|
|
|
|
return $query->get([
|
|
'balance_date', 'store_name', 'opening_balance', 'income_total',
|
|
'expense_total', 'closing_balance', 'system_closing_balance',
|
|
'balance_difference', 'difference_reason', 'verification_status', 'notes',
|
|
])->map(fn (object $row): array => $this->mapHistoricalRow($row))->all();
|
|
}
|
|
|
|
/** @return array<int,array<string,mixed>> */
|
|
private function calculatedRows(?string $storeName, ?string $month): array
|
|
{
|
|
$currentMonth = CarbonImmutable::now()->startOfMonth();
|
|
if ($month !== null && $month >= $currentMonth->format('Y-m')) {
|
|
return [];
|
|
}
|
|
|
|
$query = $this->connection->table(self::BILL_TABLE)
|
|
->where('tradeDay', '>=', self::FIRST_CALCULATED_MONTH . '-01')
|
|
->where('tradeDay', '<', $currentMonth->toDateString())
|
|
->whereNotNull('shopName')
|
|
->where('shopName', '<>', '');
|
|
|
|
if ($storeName !== null && $storeName !== '') {
|
|
$query->where('shopName', $storeName);
|
|
}
|
|
|
|
if ($month !== null) {
|
|
$query->where('tradeDay', '<', CarbonImmutable::createFromFormat('!Y-m', $month)
|
|
->addMonth()->toDateString());
|
|
}
|
|
|
|
$monthExpression = $this->monthExpression('tradeDay');
|
|
$aggregates = $query
|
|
->selectRaw($monthExpression . ' AS balance_month, shopName')
|
|
->selectRaw('COALESCE(SUM(tradeAMTIn), 0) AS income_total')
|
|
->selectRaw('COALESCE(SUM(tradeAMTOut), 0) AS expense_total')
|
|
->groupByRaw($monthExpression . ', shopName')
|
|
->orderBy('shopName')
|
|
->orderBy('balance_month')
|
|
->get();
|
|
|
|
if ($aggregates->isEmpty()) {
|
|
return [];
|
|
}
|
|
|
|
$stores = $aggregates->pluck('shopName')
|
|
->map(static fn (mixed $value): string => (string) $value)
|
|
->unique()
|
|
->values()
|
|
->all();
|
|
$balances = $this->connection->table(self::TABLE)
|
|
->where('balance_date', self::CUTOFF_DATE)
|
|
->whereIn('store_name', $stores)
|
|
->pluck('closing_balance', 'store_name')
|
|
->map(static fn (mixed $value): ?float => $value === null ? null : (float) $value)
|
|
->all();
|
|
|
|
$rows = [];
|
|
foreach ($aggregates as $aggregate) {
|
|
$store = (string) $aggregate->shopName;
|
|
$balanceMonth = (string) $aggregate->balance_month;
|
|
$openingBalance = array_key_exists($store, $balances) ? $balances[$store] : null;
|
|
$income = round((float) $aggregate->income_total, 2);
|
|
$expense = round((float) $aggregate->expense_total, 2);
|
|
$closingBalance = $openingBalance === null
|
|
? null
|
|
: round($openingBalance + $income - $expense, 2);
|
|
$balances[$store] = $closingBalance;
|
|
|
|
if ($month !== null && $balanceMonth !== $month) {
|
|
continue;
|
|
}
|
|
|
|
$difference = $closingBalance === null ? null : round(-$closingBalance, 2);
|
|
$isVerified = $difference !== null && abs($difference) < 0.005;
|
|
$reason = $openingBalance === null
|
|
? '缺少上月期末余额'
|
|
: ($isVerified ? '' : sprintf('系统期末余额与计算期末余额相差 %.2f 元', abs($difference)));
|
|
|
|
$rows[] = [
|
|
'date' => CarbonImmutable::createFromFormat('!Y-m', $balanceMonth)->endOfMonth()->toDateString(),
|
|
'store_name' => $store,
|
|
'opening_balance' => $openingBalance,
|
|
'income_total' => $income,
|
|
'expense_total' => $expense,
|
|
'closing_balance' => $closingBalance,
|
|
'system_closing_balance' => 0.0,
|
|
'balance_difference' => $difference,
|
|
'difference_reason' => $reason,
|
|
'verification_status' => $isVerified ? '已核对' : '待核对',
|
|
'notes' => '',
|
|
];
|
|
}
|
|
|
|
return $rows;
|
|
}
|
|
|
|
/** @return array<string,mixed> */
|
|
private function mapHistoricalRow(object $row): array
|
|
{
|
|
return [
|
|
'date' => (string) $row->balance_date,
|
|
'store_name' => (string) $row->store_name,
|
|
'opening_balance' => $row->opening_balance === null ? null : (float) $row->opening_balance,
|
|
'income_total' => $row->income_total === null ? null : (float) $row->income_total,
|
|
'expense_total' => $row->expense_total === null ? null : (float) $row->expense_total,
|
|
'closing_balance' => $row->closing_balance === null ? null : (float) $row->closing_balance,
|
|
'system_closing_balance' => $row->system_closing_balance === null ? null : (float) $row->system_closing_balance,
|
|
'balance_difference' => $row->balance_difference === null ? null : (float) $row->balance_difference,
|
|
'difference_reason' => (string) ($row->difference_reason ?? ''),
|
|
'verification_status' => (string) ($row->verification_status ?? ''),
|
|
'notes' => (string) ($row->notes ?? ''),
|
|
];
|
|
}
|
|
|
|
private function applyHistoricalFilters(Builder $query, ?string $storeName, ?string $month): void
|
|
{
|
|
if ($storeName !== null && $storeName !== '') {
|
|
$query->where('store_name', $storeName);
|
|
}
|
|
|
|
if ($month !== null) {
|
|
$nextMonth = CarbonImmutable::createFromFormat('!Y-m', $month)->addMonth()->toDateString();
|
|
$query->where('balance_date', '>=', $month . '-01')
|
|
->where('balance_date', '<', $nextMonth);
|
|
}
|
|
}
|
|
|
|
private function normalizeMonth(?string $month): ?string
|
|
{
|
|
return $month !== null && preg_match('/^\d{4}-\d{2}$/', $month) === 1
|
|
? $month
|
|
: null;
|
|
}
|
|
|
|
private function monthExpression(string $column): string
|
|
{
|
|
return $this->connection->getDriverName() === 'sqlite'
|
|
? "strftime('%Y-%m', {$column})"
|
|
: "DATE_FORMAT({$column}, '%Y-%m')";
|
|
}
|
|
|
|
private function hasTable(string $table): bool
|
|
{
|
|
return Schema::connection($this->connection->getName())->hasTable($table);
|
|
}
|
|
|
|
private function billMirrorReady(): bool
|
|
{
|
|
return $this->hasTable(self::BILL_TABLE)
|
|
&& $this->billMirror->ready(MarketingAccountBillMirrorRepository::ALIBABA);
|
|
}
|
|
}
|