limeng
ed58e8ef43
- 复用已登记镜像 jiansun.dbo.jst_workOrderInfo，不新增上游链路 - 口径与聚工单「记账」视图一致：工单类型“记账”，状态待审核/已拒绝/待处理/处理中/已完成， 记账原因为厂家记账 12 项 + 仓库记账 5 项二级原因，发起时间 >= 2026-09-01；同一工单只取最新一版 - 新页面 供应商应付 → 厂家记账：筛选、记账金额合计、列设置、CSV 导出；首次同步未完成时明确提示 - 镜像索引迁移 (woTypeId, created, Id) 涉及：金额展示（只读）、镜像索引迁移；不涉及权限模型和上游写入。 Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_01N5KQeRJaLrbJY9iKNC5veV
431 lines
16 KiB
PHP
431 lines
16 KiB
PHP
<?php
|
||
|
||
namespace Modules\EcommerceFinance\Repositories;
|
||
|
||
use Illuminate\Database\ConnectionInterface;
|
||
use Illuminate\Database\Query\Builder;
|
||
use Illuminate\Support\Facades\Cache;
|
||
use Illuminate\Support\Facades\Schema;
|
||
use Modules\EcommerceFinance\Contracts\BusinessMirrorDataset;
|
||
use Modules\EcommerceFinance\Services\BusinessMirrorDatasetRegistry;
|
||
use RuntimeException;
|
||
use Throwable;
|
||
|
||
/**
|
||
* 供应商应付「厂家记账」：只读聚水潭聚工单“记账”工单镜像。
|
||
*
|
||
* 接入口径与聚工单「协同工单列表 → 记账」视图一致（2026-10-18 由财务确认）：
|
||
* 工单类型“记账”，状态为待审核/已拒绝/待处理/处理中/已完成（不含已取消），
|
||
* 记账原因为“厂家记账”“仓库记账”下全部 17 个二级原因，发起时间不早于 2026-09-01。
|
||
* HTTP 请求只读共享镜像，不回退直连上游。
|
||
*/
|
||
class SupplierBookkeepingWorkOrderRepository
|
||
{
|
||
public const DATASET = 'jiansun.dbo.jst_workOrderInfo';
|
||
|
||
public const WORK_TYPE_ID = 'D7AE3ACE98BC4B66A41C7C2556E52F5E';
|
||
|
||
public const START_AT = '2026-09-01 00:00:00';
|
||
|
||
public const STATUS_LABELS = [
|
||
'WAIT_AUDIT' => '待审核',
|
||
'REJECT_AUDIT' => '已拒绝',
|
||
'WAIT_CONFIRM' => '待处理',
|
||
'CONFIRMING' => '处理中',
|
||
'CONFIRMED' => '已完成',
|
||
];
|
||
|
||
/** 记账原因：一级 => 已勾选的二级原因（与聚工单视图逐项一致）。 */
|
||
public const REASONS = [
|
||
'厂家记账' => [
|
||
'厂家成品发错货或漏发（面单照片，实物照片，条形码照片）',
|
||
'缺货损失',
|
||
'厂家双面单',
|
||
'厂家缺货-公司替换款',
|
||
'板材弯曲',
|
||
'产品安装后摇晃',
|
||
'退货运费（贴图详见同一快递单号另一笔记账）',
|
||
'孔位错误',
|
||
'配件超时未发',
|
||
'厂家配件发错/漏发（提供证据图、协商无果图）',
|
||
'板材爆口/掉漆/开裂/发霉/色差',
|
||
'退货退款',
|
||
],
|
||
'仓库记账' => [
|
||
'仓库双面单',
|
||
'仓库发错货（面单照片，实物照片，条形码照片）',
|
||
'审单原因（操作日志截图，交易截图）',
|
||
'仓库未发出（仓库通知截图，交易截图）',
|
||
'退货运费',
|
||
],
|
||
];
|
||
|
||
/** 聚工单只返回 fieldId，以下对应关系取自聚工单记账视图的列配置。 */
|
||
public const FIELD_REASON = 'SELECT_CASCADEField1413856620250906100920';
|
||
|
||
private const HEADER_FIELDS = [
|
||
'bookkeeping_type' => 'SELECTField1413856620250906100658',
|
||
'source' => 'TEXTField1413856620250908100111',
|
||
'amount' => 'NUMBERField1413856620250906100530',
|
||
'remark' => 'TEXTField1413856620250906100940',
|
||
'attachment' => 'attachmentField',
|
||
'online_order_no' => 'orderField',
|
||
'internal_order_no' => 'o_id',
|
||
'actual_paid_amount' => 'actualPaidAmountField',
|
||
'carrier' => 'logisticsField',
|
||
'tracking_no' => 'lIdField',
|
||
'shop_name' => 'shopField',
|
||
'platform' => 'platform',
|
||
'shipping_warehouse' => 'wmsField',
|
||
'processing_status' => 'SELECTField1413856620251010103748',
|
||
'counterparty_agreed' => 'SELECTField1413856620251010103459',
|
||
'counterparty_note' => 'TEXTField1413856620251010103542',
|
||
'specialist_result' => 'SELECTField1413856620251010104200',
|
||
'specialist_note' => 'TEXTField1413856620251010104107',
|
||
'accepting_department' => 'SELECTField1413856620251122154718',
|
||
'order_shipped_at' => 'orderSendTime',
|
||
'header_supplier' => 'supplierField',
|
||
];
|
||
|
||
/** 商品明细子字段（同一工单多件商品时按行合并展示）。 */
|
||
private const ITEM_FIELDS = [
|
||
'product_code' => ['skuId'],
|
||
'style_code' => ['iid'],
|
||
'other_attribute_6' => ['other6', 'other_6'],
|
||
'product_name' => ['name', 'skuName'],
|
||
'platform_sku_id' => ['platformItemSkuId'],
|
||
'platform_product_id' => ['platformItemId'],
|
||
'product_quantity' => ['qty'],
|
||
'product_price' => ['price'],
|
||
'product_amount' => ['priceCount'],
|
||
'buyer_paid_amount' => ['buyerPaidAmount'],
|
||
'supplier' => ['supplierName'],
|
||
'product_image' => ['pic'],
|
||
];
|
||
|
||
private const ROWS_CACHE_SECONDS = 600;
|
||
|
||
private const QUERY_TIMEOUT_SECONDS = 60;
|
||
|
||
private readonly ConnectionInterface $connection;
|
||
|
||
public function __construct(
|
||
private readonly BusinessMirrorDatasetRegistry $datasets,
|
||
private readonly GenericBusinessMirrorRepository $mirror,
|
||
) {
|
||
$this->connection = $mirror->connection();
|
||
}
|
||
|
||
/** @return list<string> 17 个“一级/二级”完整原因路径 */
|
||
public static function reasonPaths(): array
|
||
{
|
||
$paths = [];
|
||
foreach (self::REASONS as $parent => $children) {
|
||
foreach ($children as $child) {
|
||
$paths[] = $parent . '/' . $child;
|
||
}
|
||
}
|
||
|
||
return $paths;
|
||
}
|
||
|
||
/** @return array{code:string,last_succeeded_at:?string} */
|
||
public function syncStatus(): array
|
||
{
|
||
try {
|
||
$schema = Schema::connection($this->connection->getName());
|
||
if (! $schema->hasTable('business_mirror_table_states') || ! $schema->hasTable($this->dataset()->targetTable())) {
|
||
return ['code' => 'unavailable', 'last_succeeded_at' => null];
|
||
}
|
||
$state = $this->mirror->state($this->dataset());
|
||
} catch (Throwable) {
|
||
return ['code' => 'unavailable', 'last_succeeded_at' => null];
|
||
}
|
||
|
||
if (! is_array($state)) {
|
||
return ['code' => 'unavailable', 'last_succeeded_at' => null];
|
||
}
|
||
|
||
$last = isset($state['last_succeeded_at']) ? (string) $state['last_succeeded_at'] : null;
|
||
if (($state['initial_completed_at'] ?? null) === null) {
|
||
return ['code' => ($state['status'] ?? '') === 'failed' ? 'initial_failed' : 'initializing', 'last_succeeded_at' => $last];
|
||
}
|
||
|
||
return ['code' => ($state['status'] ?? '') === 'failed' ? 'failed' : 'ready', 'last_succeeded_at' => $last];
|
||
}
|
||
|
||
/**
|
||
* 当前接入口径下的全部记账工单（每个工单号只取最新一版），按发起时间倒序。
|
||
* 结果随镜像同步版本缓存；结果集为百至千级，页面筛选与分页在内存中完成。
|
||
*
|
||
* @return list<array<string,mixed>>
|
||
*/
|
||
public function rows(): array
|
||
{
|
||
$status = $this->syncStatus();
|
||
if (! in_array($status['code'], ['ready', 'failed'], true)) {
|
||
throw new RuntimeException('business_mirror_dataset_not_ready');
|
||
}
|
||
|
||
$key = 'supplier-bookkeeping:rows:v1:' . sha1((string) $status['last_succeeded_at']);
|
||
$cached = Cache::get($key);
|
||
if (is_array($cached)) {
|
||
return $cached;
|
||
}
|
||
|
||
$rows = $this->load();
|
||
Cache::put($key, $rows, self::ROWS_CACHE_SECONDS);
|
||
|
||
return $rows;
|
||
}
|
||
|
||
/** @return list<array<string,mixed>> */
|
||
private function load(): array
|
||
{
|
||
$table = $this->dataset()->targetTable();
|
||
$query = $this->connection->table($table . ' AS w')
|
||
->leftJoin($table . ' AS newer', static function ($join): void {
|
||
$join->on('newer.woTypeId', '=', 'w.woTypeId')
|
||
->on('newer.woNo', '=', 'w.woNo')
|
||
->on('newer.Id', '>', 'w.Id');
|
||
})
|
||
->where('w.woTypeId', self::WORK_TYPE_ID)
|
||
->where('w.created', '>=', self::START_AT)
|
||
// 镜像状态通常为英文码，兼容个别以中文状态落库的记录。
|
||
->whereIn('w.status', [...array_keys(self::STATUS_LABELS), ...array_values(self::STATUS_LABELS)])
|
||
->whereNull('newer.Id')
|
||
// 粗筛只为减少传输：JSON 可能是原文或 \u 转义，两种写法都保留；精确判定在下方解析后完成。
|
||
->where(function (Builder $reason): void {
|
||
foreach (array_keys(self::REASONS) as $parent) {
|
||
foreach ([$parent, trim(json_encode($parent), '"')] as $needle) {
|
||
$reason->orWhereRaw("w.businessFieldList LIKE ? ESCAPE '!'", [$this->contains($needle)]);
|
||
}
|
||
}
|
||
})
|
||
->select([
|
||
'w.Id', 'w.woNo', 'w.status', 'w.createby', 'w.executors',
|
||
'w.created', 'w.updated', 'w.businessFieldList',
|
||
])
|
||
->orderByDesc('w.created')
|
||
->orderByDesc('w.woNo');
|
||
|
||
$allowed = array_flip(self::reasonPaths());
|
||
$rows = [];
|
||
foreach ($this->boundedGet($query) as $record) {
|
||
$fields = $this->fields((string) ($record->businessFieldList ?? ''));
|
||
$reason = $this->reasonPath($fields[self::FIELD_REASON] ?? null);
|
||
if (! isset($allowed[$reason])) {
|
||
continue;
|
||
}
|
||
$rows[] = $this->decorate((array) $record, $fields, $reason);
|
||
}
|
||
|
||
return $rows;
|
||
}
|
||
|
||
/**
|
||
* @param array<string,mixed> $record
|
||
* @param array<string,array{value:mixed,label:mixed}> $fields
|
||
* @return array<string,mixed>
|
||
*/
|
||
private function decorate(array $record, array $fields, string $reason): array
|
||
{
|
||
$levels = explode('/', $reason, 2);
|
||
$row = [
|
||
'source_id' => $record['Id'],
|
||
'started_at' => (string) $record['created'],
|
||
'work_order_no' => (string) $record['woNo'],
|
||
'initiator' => (string) ($record['createby'] ?? ''),
|
||
'bookkeeping_reason' => $reason,
|
||
'reason_level_1' => $levels[0],
|
||
'reason_level_2' => $levels[1] ?? '',
|
||
'executor' => (string) ($record['executors'] ?? ''),
|
||
'status' => self::STATUS_LABELS[(string) $record['status']] ?? (string) $record['status'],
|
||
'updated_at' => (string) ($record['updated'] ?? ''),
|
||
'work_order_type' => '记账',
|
||
];
|
||
|
||
foreach (self::HEADER_FIELDS as $key => $fieldId) {
|
||
$row[$key] = $this->display($fields[$fieldId] ?? null);
|
||
}
|
||
$row['attachment_count'] = count($this->listValue($fields['attachmentField']['value'] ?? null));
|
||
$row['attachment'] = implode("\n", $this->listValue($fields['attachmentField']['value'] ?? null));
|
||
$row['amount'] = $this->money($row['amount']);
|
||
$row['actual_paid_amount'] = $this->money($row['actual_paid_amount']);
|
||
|
||
$items = $this->items($fields['itemInfo']['value'] ?? null, $fields['itemInfo']['raw'] ?? null);
|
||
foreach (self::ITEM_FIELDS as $key => $candidates) {
|
||
$values = array_map(fn (array $item): string => $this->firstOf($item, $candidates), $items);
|
||
$row[$key] = implode("\n", array_filter($values, static fn (string $v): bool => $v !== ''));
|
||
}
|
||
if ($row['supplier'] === '') {
|
||
$row['supplier'] = $row['header_supplier'];
|
||
}
|
||
unset($row['header_supplier']);
|
||
|
||
return $row;
|
||
}
|
||
|
||
/** @return array<string,array{value:mixed,label:mixed,raw:array<mixed>}> fieldId => 原值 */
|
||
private function fields(string $json): array
|
||
{
|
||
if (trim($json) === '') {
|
||
return [];
|
||
}
|
||
try {
|
||
$decoded = json_decode($json, true, 64, JSON_THROW_ON_ERROR);
|
||
} catch (Throwable) {
|
||
return [];
|
||
}
|
||
if (! is_array($decoded)) {
|
||
return [];
|
||
}
|
||
// 兼容 {"businessField":[...]} 包裹形式。
|
||
if (isset($decoded['businessField']) && is_array($decoded['businessField'])) {
|
||
$decoded = $decoded['businessField'];
|
||
}
|
||
|
||
$fields = [];
|
||
foreach ($decoded as $item) {
|
||
if (! is_array($item) || ! isset($item['fieldId'])) {
|
||
continue;
|
||
}
|
||
$fields[(string) $item['fieldId']] = [
|
||
'value' => $item['value'] ?? null,
|
||
'label' => $item['valueLabel'] ?? null,
|
||
'raw' => $item,
|
||
];
|
||
}
|
||
|
||
return $fields;
|
||
}
|
||
|
||
private function reasonPath(?array $field): string
|
||
{
|
||
if ($field === null) {
|
||
return '';
|
||
}
|
||
$value = $field['label'] ?? null;
|
||
if ($value === null || $value === '' || $value === []) {
|
||
$value = $field['value'];
|
||
}
|
||
if (is_array($value)) {
|
||
$value = implode('/', array_map(static fn (mixed $part): string => trim((string) (is_array($part) ? implode('/', $part) : $part)), $value));
|
||
}
|
||
|
||
return (string) preg_replace('/\s*\/\s*/u', '/', trim((string) $value));
|
||
}
|
||
|
||
/** @param array{value:mixed,label:mixed}|null $field */
|
||
private function display(?array $field): string
|
||
{
|
||
if ($field === null) {
|
||
return '';
|
||
}
|
||
foreach ([$field['label'], $field['value']] as $value) {
|
||
if (is_array($value)) {
|
||
$value = implode('、', array_filter(array_map(static fn (mixed $v): string => is_scalar($v) ? trim((string) $v) : '', $value)));
|
||
}
|
||
if (is_scalar($value) && trim((string) $value) !== '') {
|
||
return trim((string) $value);
|
||
}
|
||
}
|
||
|
||
return '';
|
||
}
|
||
|
||
/** @return list<string> */
|
||
private function listValue(mixed $value): array
|
||
{
|
||
if (is_string($value) && str_starts_with(trim($value), '[')) {
|
||
$value = json_decode($value, true);
|
||
}
|
||
if (is_string($value) && trim($value) !== '') {
|
||
return [trim($value)];
|
||
}
|
||
|
||
return is_array($value)
|
||
? array_values(array_filter(array_map(static fn (mixed $v): string => is_scalar($v) ? trim((string) $v) : '', $value)))
|
||
: [];
|
||
}
|
||
|
||
/**
|
||
* 商品明细：聚工单 API 为 value=[{skuId,...}]，镜像中也可能是 childFieldList=[[{fieldId,value}...]]。
|
||
*
|
||
* @param array<mixed>|null $raw
|
||
* @return list<array<string,string>>
|
||
*/
|
||
private function items(mixed $value, ?array $raw): array
|
||
{
|
||
$items = [];
|
||
if (is_string($value) && str_starts_with(trim($value), '[')) {
|
||
$value = json_decode($value, true);
|
||
}
|
||
foreach (is_array($value) ? $value : [] as $item) {
|
||
if (is_array($item) && ! array_is_list($item)) {
|
||
$items[] = array_map(static fn (mixed $v): string => is_scalar($v) ? trim((string) $v) : '', $item);
|
||
}
|
||
}
|
||
foreach ((array) ($raw['childFieldList'] ?? []) as $childRow) {
|
||
$item = [];
|
||
foreach ((array) $childRow as $child) {
|
||
if (is_array($child) && isset($child['fieldId'])) {
|
||
$v = $child['valueLabel'] ?? $child['value'] ?? '';
|
||
$item[(string) $child['fieldId']] = is_scalar($v) ? trim((string) $v) : '';
|
||
}
|
||
}
|
||
if ($item !== []) {
|
||
$items[] = $item;
|
||
}
|
||
}
|
||
|
||
return $items;
|
||
}
|
||
|
||
/** @param array<string,string> $item @param list<string> $candidates */
|
||
private function firstOf(array $item, array $candidates): string
|
||
{
|
||
foreach ($candidates as $key) {
|
||
if (($item[$key] ?? '') !== '') {
|
||
return $item[$key];
|
||
}
|
||
}
|
||
|
||
return '';
|
||
}
|
||
|
||
private function money(string $value): ?string
|
||
{
|
||
$normalized = str_replace([',', '，', '￥', '¥', '元', ' '], '', trim($value));
|
||
if (! preg_match('/^-?\d+(\.\d+)?$/', $normalized)) {
|
||
return null;
|
||
}
|
||
|
||
return number_format(round((float) $normalized, 2), 2, '.', '');
|
||
}
|
||
|
||
/** @return iterable<int,object> */
|
||
private function boundedGet(Builder $query): iterable
|
||
{
|
||
$connection = $query->getConnection();
|
||
if ($connection->getDriverName() !== 'mysql') {
|
||
return $query->get();
|
||
}
|
||
|
||
return $connection->select(
|
||
'SET STATEMENT max_statement_time=' . self::QUERY_TIMEOUT_SECONDS . ' FOR ' . $query->toSql(),
|
||
$query->getBindings(),
|
||
);
|
||
}
|
||
|
||
private function contains(string $value): string
|
||
{
|
||
return '%' . str_replace(['!', '%', '_'], ['!!', '!%', '!_'], $value) . '%';
|
||
}
|
||
|
||
private function dataset(): BusinessMirrorDataset
|
||
{
|
||
return $this->datasets->get(self::DATASET);
|
||
}
|
||
}
|