447 lines
17 KiB
PHP
447 lines
17 KiB
PHP
<?php
|
||
|
||
namespace Modules\EcommerceFinance\Repositories;
|
||
|
||
use Modules\EcommerceFinance\Services\UpstreamReadOnlyQuery;
|
||
use Modules\EcommerceFinance\Services\UpstreamReadOnlyQueryFactory;
|
||
|
||
/**
|
||
* 淘系（天猫+淘宝）支付宝账户流水 - 子功能页面数据仓库
|
||
*
|
||
* 数据来源：上游 JianSun.dbo.tmall_spread_bills
|
||
* 表1列：店铺名称 | 记账日期 | 交易日期 | 金额 | 余额 | 备注 | finType | 收支类型 | 交易类型
|
||
*
|
||
* 字段映射（上游表 -> 表1 列）：
|
||
* shopName -> 店铺名称
|
||
* uniTransTime -> 记账日期（datetime）
|
||
* transTime -> 交易日期（date）
|
||
* amount（分） -> 金额（元）—— 上游以「分」存储，返回时除以 100
|
||
* balance（分） -> 余额（元）—— 上游以「分」存储，返回时除以 100
|
||
* comment -> 备注
|
||
* finType -> finType（CREDIT/DEBIT 原值）
|
||
* finType 派生 -> 收支类型（CREDIT=支出, DEBIT=收入）
|
||
* bizType/tradeType -> 交易类型（CASH_DEDUCT=扣款, INTEL_CHARGE_POST=充值 等）
|
||
*
|
||
* 金额单位换算：上游 amount/balance 均以「分」存储，前端期望「元」，
|
||
* 因此 normalize() 中统一除以 100，summary() 也基于「元」单位汇总。
|
||
*/
|
||
class TmallTaobaoSpreadBillRepository
|
||
{
|
||
private const TABLE = '[dbo].[tmall_spread_bills]';
|
||
private const SOURCE = 'jiansun';
|
||
|
||
/** finType -> 中文收支类型（CREDIT=支出, DEBIT=收入，与天猫平台口径一致）*/
|
||
private const FIN_TYPE_MAP = [
|
||
'CREDIT' => '支出',
|
||
'DEBIT' => '收入',
|
||
];
|
||
|
||
/** bizType -> 中文交易类型（天猫平台 bizType 枚举）*/
|
||
private const BIZ_TYPE_MAP = [
|
||
'CASH_DEDUCT' => '扣款',
|
||
'INTEL_CHARGE_POST' => '充值',
|
||
'TRANSFER_IN' => '转入',
|
||
'TRANSFER_OUT' => '转出',
|
||
'REFUND' => '退款',
|
||
'WITHDRAW' => '提现',
|
||
];
|
||
|
||
/** tradeType 兜底映射 */
|
||
private const TRADE_TYPE_MAP = [
|
||
'DEDUCT' => '扣款',
|
||
'CHARGE' => '充值',
|
||
'TRANSFER_IN' => '转入',
|
||
'TRANSFER_OUT' => '转出',
|
||
'REFUND' => '退款',
|
||
'WITHDRAW' => '提现',
|
||
];
|
||
|
||
private UpstreamReadOnlyQuery $query;
|
||
|
||
public function __construct(
|
||
UpstreamReadOnlyQueryFactory $factory,
|
||
private readonly ?MarketingAccountBillMirrorRepository $mirror = null,
|
||
) {
|
||
$this->query = $factory->forSource(self::SOURCE);
|
||
}
|
||
|
||
public function usesBusinessMirror(): bool
|
||
{
|
||
return $this->mirror?->ready(MarketingAccountBillMirrorRepository::TAOBAO) ?? false;
|
||
}
|
||
|
||
/**
|
||
* 店铺下拉选项（全部去重）
|
||
*
|
||
* @return string[]
|
||
*/
|
||
public function storeOptions(): array
|
||
{
|
||
if ($this->usesBusinessMirror()) {
|
||
return $this->mirror->taobaoStoreOptions();
|
||
}
|
||
|
||
$rows = $this->query->select(
|
||
'SELECT DISTINCT shopName FROM ' . self::TABLE . ' WHERE shopName IS NOT NULL ORDER BY shopName'
|
||
);
|
||
|
||
return array_map(static fn (array $r) => (string) $r['shopName'], $rows);
|
||
}
|
||
|
||
/**
|
||
* 分页查询淘系支付宝账户流水，返回表1 9 列结构。
|
||
*
|
||
* SQL Server 2012 兼容：用 ROW_NUMBER() OVER 做分页，避免 OFFSET/FETCH。
|
||
*
|
||
* @param string|null $flowType 收支类型筛选：income=收入，expense=支出
|
||
* @return array{rows: array<int,array<string,mixed>>, total: int}
|
||
*/
|
||
public function paginate(
|
||
?string $storeName = null,
|
||
?string $fromDate = null, // Y-m-d（按交易日期 transTime 过滤）
|
||
?string $toDate = null, // Y-m-d
|
||
?string $flowType = null, // 'income' | 'expense' | null
|
||
int $page = 1,
|
||
int $pageSize = 25
|
||
): array {
|
||
$page = max(1, $page);
|
||
$pageSize = max(1, min(200, $pageSize));
|
||
|
||
if ($this->usesBusinessMirror()) {
|
||
$result = $this->mirror->paginateTaobao(
|
||
$storeName,
|
||
$fromDate,
|
||
$toDate,
|
||
$flowType,
|
||
$page,
|
||
$pageSize,
|
||
);
|
||
|
||
return [
|
||
'rows' => array_map(fn (array $row): array => $this->normalize($row), $result['rows']),
|
||
'total' => $result['total'],
|
||
];
|
||
}
|
||
|
||
[$whereSql, $params] = $this->buildWhere($storeName, $fromDate, $toDate, $flowType);
|
||
|
||
// 总数
|
||
$total = (int) $this->query->scalar(
|
||
'SELECT COUNT(*) FROM ' . self::TABLE . $whereSql,
|
||
$params
|
||
);
|
||
|
||
if ($total === 0) {
|
||
return ['rows' => [], 'total' => 0];
|
||
}
|
||
|
||
// 分页（按记账日期 uniTransTime DESC, id DESC 排序，与列表展示方向一致）
|
||
$offsetStart = ($page - 1) * $pageSize + 1;
|
||
$offsetEnd = $page * $pageSize;
|
||
|
||
$pagedSql = 'SELECT * FROM ('
|
||
. 'SELECT src.id, src.shopName, src.uniTransTime, src.transTime, '
|
||
. 'src.amount, src.balance, src.comment, src.finType, '
|
||
. 'src.bizType, src.tradeType, '
|
||
. 'ROW_NUMBER() OVER (ORDER BY src.uniTransTime DESC, src.id DESC) AS rn '
|
||
. 'FROM ' . self::TABLE . ' src' . $whereSql
|
||
. ') AS t WHERE t.rn BETWEEN ? AND ? ORDER BY t.rn';
|
||
|
||
$rawRows = $this->query->select(
|
||
$pagedSql,
|
||
array_merge($params, [$offsetStart, $offsetEnd])
|
||
);
|
||
|
||
return [
|
||
'rows' => array_map(fn (array $r) => $this->normalize($r), $rawRows),
|
||
'total' => $total,
|
||
];
|
||
}
|
||
|
||
/** @return \Generator<int,array<string,mixed>> */
|
||
public function exportRows(
|
||
?string $storeName = null,
|
||
?string $fromDate = null,
|
||
?string $toDate = null,
|
||
?string $flowType = null,
|
||
int $batchSize = 200,
|
||
): \Generator {
|
||
if (! $this->usesBusinessMirror()) {
|
||
throw new \RuntimeException('淘系账单共享业务镜像尚未完成同步。');
|
||
}
|
||
|
||
$batchSize = max(1, min(200, $batchSize));
|
||
$page = 1;
|
||
$exported = 0;
|
||
|
||
do {
|
||
$result = $this->mirror->paginateTaobao(
|
||
$storeName,
|
||
$fromDate,
|
||
$toDate,
|
||
$flowType,
|
||
$page,
|
||
$batchSize,
|
||
);
|
||
$rows = is_array($result['rows'] ?? null) ? $result['rows'] : [];
|
||
$total = max(0, (int) ($result['total'] ?? 0));
|
||
|
||
foreach ($rows as $row) {
|
||
yield $this->normalize($row);
|
||
}
|
||
|
||
$count = count($rows);
|
||
$exported += $count;
|
||
$page++;
|
||
} while ($count > 0 && $exported < $total);
|
||
}
|
||
|
||
/**
|
||
* 当前筛选条件下的汇总（金额单位：元）。
|
||
*
|
||
* @param string|null $flowType 'income'/'expense'/null
|
||
* @return array{income_total: float, expense_total: float, net: float, count: int}
|
||
*/
|
||
public function summary(
|
||
?string $storeName = null,
|
||
?string $fromDate = null,
|
||
?string $toDate = null,
|
||
?string $flowType = null
|
||
): array {
|
||
if ($this->usesBusinessMirror()) {
|
||
$row = $this->mirror->summarizeTaobao($storeName, $fromDate, $toDate, $flowType);
|
||
} else {
|
||
[$whereSql, $params] = $this->buildWhere($storeName, $fromDate, $toDate, $flowType);
|
||
|
||
// amount 单位为「分」，汇总时除以 100 转为「元」
|
||
$row = $this->query->first(
|
||
'SELECT '
|
||
. 'COALESCE(SUM(CASE WHEN finType = \'DEBIT\' THEN amount ELSE 0 END), 0) AS income_total_raw, '
|
||
. 'COALESCE(SUM(CASE WHEN finType = \'CREDIT\' THEN amount ELSE 0 END), 0) AS expense_total_raw, '
|
||
. 'COUNT(*) AS cnt '
|
||
. 'FROM ' . self::TABLE . $whereSql,
|
||
$params
|
||
);
|
||
}
|
||
|
||
// 分 -> 元
|
||
$income = ((float) ($row['income_total_raw'] ?? 0)) / 100;
|
||
$expense = ((float) ($row['expense_total_raw'] ?? 0)) / 100;
|
||
|
||
return [
|
||
'income_total' => $income,
|
||
'expense_total' => $expense,
|
||
'net' => $income - $expense,
|
||
'count' => (int) ($row['cnt'] ?? 0),
|
||
];
|
||
}
|
||
|
||
/** @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 {
|
||
$page = max(1, $page);
|
||
$pageSize = max(1, min(5000, $pageSize));
|
||
|
||
if ($this->usesBusinessMirror()) {
|
||
$result = $this->mirror->paginateTaobaoMonthlyBalances($storeName, $month, $page, $pageSize);
|
||
|
||
return [
|
||
'rows' => array_map(fn (array $row): array => $this->normalizeMonthlyBalance($row), $result['rows']),
|
||
'total' => $result['total'],
|
||
];
|
||
}
|
||
|
||
$monthExpression = 'DATEADD(MONTH, DATEDIFF(MONTH, 0, transTime), 0)';
|
||
[$where, $params] = $this->buildMonthlyWhere($storeName, $month);
|
||
|
||
$total = (int) $this->query->scalar(
|
||
'SELECT COUNT(*) FROM ('
|
||
. 'SELECT shopName, ' . $monthExpression . ' AS month_start '
|
||
. 'FROM ' . self::TABLE . ' ' . $where . ' '
|
||
. 'GROUP BY shopName, ' . $monthExpression
|
||
. ') AS monthly_count',
|
||
$params
|
||
);
|
||
|
||
if ($total === 0) {
|
||
return ['rows' => [], 'total' => 0];
|
||
}
|
||
|
||
$offsetStart = (($page - 1) * $pageSize) + 1;
|
||
$offsetEnd = $page * $pageSize;
|
||
$sql = 'WITH monthly AS ('
|
||
. 'SELECT shopName, ' . $monthExpression . ' AS month_start, '
|
||
. 'COALESCE(SUM(CASE WHEN finType = \'DEBIT\' THEN amount ELSE 0 END), 0) AS income_total_raw, '
|
||
. 'COALESCE(SUM(CASE WHEN finType = \'CREDIT\' THEN amount ELSE 0 END), 0) AS expense_total_raw '
|
||
. 'FROM ' . self::TABLE . ' ' . $where . ' '
|
||
. 'GROUP BY shopName, ' . $monthExpression
|
||
. '), numbered AS ('
|
||
. 'SELECT m.shopName, m.month_start, m.income_total_raw, m.expense_total_raw, '
|
||
. 'opening_entry.balance AS opening_balance_raw, closing_entry.balance AS system_closing_balance_raw, '
|
||
. 'ROW_NUMBER() OVER (ORDER BY m.month_start DESC, m.shopName ASC) AS rn '
|
||
. 'FROM monthly m '
|
||
. 'OUTER APPLY (SELECT TOP 1 t.balance FROM ' . self::TABLE . ' t '
|
||
. 'WHERE t.shopName = m.shopName AND t.transTime < m.month_start '
|
||
. 'ORDER BY t.transTime DESC, t.id DESC) opening_entry '
|
||
. 'OUTER APPLY (SELECT TOP 1 t.balance FROM ' . self::TABLE . ' t '
|
||
. 'WHERE t.shopName = m.shopName AND t.transTime < DATEADD(MONTH, 1, m.month_start) '
|
||
. 'ORDER BY t.transTime DESC, t.id DESC) closing_entry'
|
||
. ') SELECT shopName, CONVERT(varchar(10), EOMONTH(month_start), 120) AS balance_date, '
|
||
. 'opening_balance_raw, income_total_raw, expense_total_raw, system_closing_balance_raw, rn '
|
||
. 'FROM numbered WHERE rn BETWEEN ? AND ? ORDER BY rn';
|
||
$rows = $this->query->select($sql, array_merge($params, [$offsetStart, $offsetEnd]));
|
||
|
||
return [
|
||
'rows' => array_map(fn (array $row): array => $this->normalizeMonthlyBalance($row), $rows),
|
||
'total' => $total,
|
||
];
|
||
}
|
||
|
||
/** @return array{0:string,1:array<int,mixed>} */
|
||
private function buildMonthlyWhere(?string $storeName, ?string $month): array
|
||
{
|
||
$conditions = [
|
||
'shopName IS NOT NULL',
|
||
'transTime IS NOT NULL',
|
||
'transTime < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)',
|
||
];
|
||
$params = [];
|
||
|
||
if ($storeName !== null && $storeName !== '') {
|
||
$conditions[] = 'shopName = ?';
|
||
$params[] = $storeName;
|
||
}
|
||
|
||
if ($month !== null && preg_match('/^\d{4}-\d{2}$/', $month) === 1) {
|
||
$monthStart = $month . '-01';
|
||
$conditions[] = 'transTime >= ? AND transTime < DATEADD(MONTH, 1, CAST(? AS date))';
|
||
$params[] = $monthStart;
|
||
$params[] = $monthStart;
|
||
}
|
||
|
||
return ['WHERE ' . implode(' AND ', $conditions), $params];
|
||
}
|
||
|
||
/**
|
||
* 构造 WHERE 子句。
|
||
*
|
||
* @param string|null $flowType 'income'/'expense'/null
|
||
* @return array{0:string,1:array<int,mixed>}
|
||
*/
|
||
private function buildWhere(
|
||
?string $storeName,
|
||
?string $fromDate,
|
||
?string $toDate,
|
||
?string $flowType = null
|
||
): array {
|
||
$conditions = [];
|
||
$params = [];
|
||
|
||
if ($storeName !== null && $storeName !== '') {
|
||
$conditions[] = 'shopName = ?';
|
||
$params[] = $storeName;
|
||
}
|
||
|
||
// 日期按交易日期 transTime 过滤；Y-m-d 校验后字符串比较 date 即可
|
||
if ($fromDate !== null && preg_match('/^\d{4}-\d{2}-\d{2}$/', $fromDate) === 1) {
|
||
$conditions[] = 'transTime >= ?';
|
||
$params[] = $fromDate;
|
||
}
|
||
|
||
if ($toDate !== null && preg_match('/^\d{4}-\d{2}-\d{2}$/', $toDate) === 1) {
|
||
$conditions[] = 'transTime <= ?';
|
||
$params[] = $toDate;
|
||
}
|
||
|
||
// 收支类型筛选：income -> finType='DEBIT'（收入），expense -> finType='CREDIT'（支出）
|
||
if ($flowType === 'income') {
|
||
$conditions[] = 'finType = \'DEBIT\'';
|
||
} elseif ($flowType === 'expense') {
|
||
$conditions[] = 'finType = \'CREDIT\'';
|
||
}
|
||
|
||
$whereSql = $conditions === [] ? '' : ' WHERE ' . implode(' AND ', $conditions);
|
||
|
||
return [$whereSql, $params];
|
||
}
|
||
|
||
/**
|
||
* 把上游行归一化为表1 9 列结构（前端可直接消费）。
|
||
* 金额单位从「分」转为「元」。
|
||
*/
|
||
private function normalize(array $row): array
|
||
{
|
||
$finType = (string) ($row['finType'] ?? '');
|
||
$bizType = (string) ($row['bizType'] ?? '');
|
||
$tradeType = (string) ($row['tradeType'] ?? '');
|
||
|
||
// 交易类型：优先 bizType，其次 tradeType，最后返回原始 bizType
|
||
$transactionType = self::BIZ_TYPE_MAP[$bizType]
|
||
?? self::TRADE_TYPE_MAP[$tradeType]
|
||
?? $bizType;
|
||
|
||
return [
|
||
// 表1 9 列
|
||
'store_name' => (string) ($row['shopName'] ?? ''),
|
||
'bookkeeping_time' => (string) ($row['uniTransTime'] ?? ''),
|
||
'transaction_date' => (string) ($row['transTime'] ?? ''),
|
||
'amount' => ((float) ($row['amount'] ?? 0)) / 100,
|
||
'balance' => ((float) ($row['balance'] ?? 0)) / 100,
|
||
'comment' => (string) ($row['comment'] ?? ''),
|
||
'fin_type' => $finType,
|
||
'income_expense_type' => self::FIN_TYPE_MAP[$finType] ?? '',
|
||
'transaction_type' => $transactionType,
|
||
// 原始字段保留
|
||
'biz_type_raw' => $bizType,
|
||
'trade_type_raw' => $tradeType,
|
||
];
|
||
}
|
||
|
||
/** @return array<string,mixed> */
|
||
private function normalizeMonthlyBalance(array $row): array
|
||
{
|
||
$opening = isset($row['opening_balance_raw'])
|
||
? round(((float) $row['opening_balance_raw']) / 100, 2)
|
||
: null;
|
||
$income = round(((float) ($row['income_total_raw'] ?? 0)) / 100, 2);
|
||
$expense = round(((float) ($row['expense_total_raw'] ?? 0)) / 100, 2);
|
||
$systemClosing = isset($row['system_closing_balance_raw'])
|
||
? round(((float) $row['system_closing_balance_raw']) / 100, 2)
|
||
: null;
|
||
$calculatedClosing = $opening === null ? null : round($opening + $income - $expense, 2);
|
||
$difference = $systemClosing === null || $calculatedClosing === null
|
||
? null
|
||
: round($systemClosing - $calculatedClosing, 2);
|
||
|
||
if ($opening === null && $systemClosing === null) {
|
||
$reason = '缺少期初余额和系统期末余额';
|
||
} elseif ($opening === null) {
|
||
$reason = '缺少上月期末余额';
|
||
} elseif ($systemClosing === null) {
|
||
$reason = '缺少本月系统期末余额';
|
||
} elseif (abs($difference) < 0.005) {
|
||
$reason = '';
|
||
} else {
|
||
$reason = '系统期末余额与计算期末余额相差 ' . number_format($difference, 2) . ' 元';
|
||
}
|
||
|
||
return [
|
||
'date' => (string) ($row['balance_date'] ?? ''),
|
||
'store_name' => (string) ($row['shopName'] ?? ''),
|
||
'opening_balance' => $opening,
|
||
'income_total' => $income,
|
||
'expense_total' => $expense,
|
||
'closing_balance' => $calculatedClosing,
|
||
'system_closing_balance' => $systemClosing,
|
||
'balance_difference' => $difference,
|
||
'difference_reason' => $reason,
|
||
'verification_status' => $difference !== null && abs($difference) < 0.005 ? '已核对' : '待核对',
|
||
'notes' => '',
|
||
];
|
||
}
|
||
}
|