limeng
ea479733ab
- 种菜报销：复用已登记镜像 jiansun.dbo.jst_workOrderInfo，工单类型“种菜报销”、状态已完成， 按完成时间归属报销月份（M 月 = [M-02 00:00, M+1-02 00:00)），自 2026-09-02 起；同一工单只取最新一版 - 种菜损耗：当月种菜报销的线上订单号匹配上月+本月发货数据(jst_oms_shipping)交易单号； 任务备注/卖家备注含“拍A发B/拍A发A”的不扣款（单独导出发运营），只发信誉订单虚拟条码/透明挂钩的不扣款， 其余按发货条码在普通商品资料(ops_jst_sku)的成本价 × 数量 × 1.2 扣款，成本价缺失单独提示 - 两页均支持筛选、列设置、CSV 导出；按月缓存 10 分钟 - 测试：窗口口径、免扣字样、虚拟条码/挂钩、成本价取数与缺失、页面筛选和导出 涉及：金额计算展示（只读）；不涉及数据库迁移、权限模型和上游写入。 Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_01VANWYsNUbu2piMtCKymoyd
368 lines
14 KiB
PHP
368 lines
14 KiB
PHP
<?php
|
||
|
||
namespace Modules\EcommerceFinance\Repositories;
|
||
|
||
use Carbon\CarbonImmutable;
|
||
use Illuminate\Database\ConnectionInterface;
|
||
use Illuminate\Database\Query\Builder;
|
||
use Illuminate\Support\Facades\Cache;
|
||
use Illuminate\Support\Facades\Schema;
|
||
use InvalidArgumentException;
|
||
use Modules\EcommerceFinance\Contracts\BusinessMirrorDataset;
|
||
use Modules\EcommerceFinance\Services\BusinessMirrorDatasetRegistry;
|
||
use RuntimeException;
|
||
use Throwable;
|
||
|
||
/**
|
||
* 聚水潭聚工单「打款工单 → 种菜报销」：只读工单镜像 jiansun.dbo.jst_workOrderInfo。
|
||
*
|
||
* 口径（2026-10-19 财务确认）：工单类型“种菜报销”、工单状态“已完成”，按完成时间筛选，
|
||
* 自 2026-09-02 00:00 起接入。种菜报销都是当天报销前一天的明细，所以 M 月种菜报销明细
|
||
* = 完成时间 [M 月 2 日 00:00, M+1 月 2 日 00:00)。同一工单号只取最新一版。
|
||
*/
|
||
class PlantingReimbursementRepository
|
||
{
|
||
public const DATASET = 'jiansun.dbo.jst_workOrderInfo';
|
||
|
||
public const WORK_TYPE_ID = '6F03128403F2442CABCF283B59FAD88D';
|
||
|
||
public const STATUS_CONFIRMED = 'CONFIRMED';
|
||
|
||
public const START_AT = '2026-09-02 00:00:00';
|
||
|
||
public const FIRST_MONTH = '2026-09';
|
||
|
||
/** 聚工单只返回 fieldId；对应关系由历史种菜明细逐列对撞及打款工单列表核对得出。 */
|
||
public const FIELDS = [
|
||
'online_order_no' => 'orderField',
|
||
'internal_order_no' => 'o_id',
|
||
'platform' => 'platform',
|
||
'shop_name' => 'shopField',
|
||
'actual_paid_amount' => 'actualPaidAmountField',
|
||
'task_remark' => 'TEXTField1413856620251113150546',
|
||
'seller_remark' => 'businessRemarkField',
|
||
'task_payment' => 'NUMBERField1413856620250924131615',
|
||
'task_freight' => 'NUMBERField1413856620251113150537',
|
||
'task_commission' => 'NUMBERField1413856620251230154457',
|
||
'jingxi_price_diff' => 'NUMBERField1413856620250924131623',
|
||
'task_deduction' => 'NUMBERField1413856620251113150612',
|
||
'task_empty_package_fee' => 'NUMBERField1413856620251113145620',
|
||
'reimbursement_amount' => 'NUMBERField1413856620251117101250',
|
||
'payee_name' => 'TEXTField1413856620251117180931',
|
||
'payee_account' => 'TEXTField1413856620251117113451',
|
||
'payee_bank' => 'TEXTField1413856620251117112729',
|
||
'payee_mobile' => 'TEXTField1413856620260618171005',
|
||
'erp_order_status' => 'erpOrderStatus',
|
||
'platform_order_status' => 'platformOrderStatus',
|
||
'paid_at' => 'payDateField',
|
||
];
|
||
|
||
public const MONEY_FIELDS = [
|
||
'actual_paid_amount', 'task_payment', 'task_freight', 'task_commission', 'jingxi_price_diff',
|
||
'task_deduction', 'task_empty_package_fee', 'reimbursement_amount',
|
||
];
|
||
|
||
private const ERP_ORDER_STATUS = [
|
||
'Sent' => '已发货', 'Merged' => '被合并', 'Cancelled' => '已取消', 'Split' => '被拆分',
|
||
'WaitConfirm' => '待审核', 'WaitFConfirm' => '待财审', 'Delivering' => '发货中',
|
||
'Question' => '异常', 'WaitOuterSent' => '等供销商发货',
|
||
];
|
||
|
||
private const ROWS_CACHE_SECONDS = 600;
|
||
|
||
private const QUERY_TIMEOUT_SECONDS = 90;
|
||
|
||
private readonly ConnectionInterface $connection;
|
||
|
||
public function __construct(
|
||
private readonly BusinessMirrorDatasetRegistry $datasets,
|
||
private readonly GenericBusinessMirrorRepository $mirror,
|
||
) {
|
||
$this->connection = $mirror->connection();
|
||
}
|
||
|
||
/**
|
||
* 报销月份 → 完成时间窗口 [M-02 00:00, M+1-02 00:00)。
|
||
*
|
||
* @return array{from:string,to:string}
|
||
*/
|
||
public static function window(string $month): array
|
||
{
|
||
if (preg_match('/^\d{4}-(0[1-9]|1[0-2])$/', $month) !== 1 || $month < self::FIRST_MONTH) {
|
||
throw new InvalidArgumentException('报销月份无效。');
|
||
}
|
||
$start = CarbonImmutable::createFromFormat('!Y-m-d', $month . '-02');
|
||
|
||
return ['from' => $start->format('Y-m-d H:i:s'), 'to' => $start->addMonthNoOverflow()->format('Y-m-d H:i:s')];
|
||
}
|
||
|
||
/** 默认月份：完成时间窗口已开始的最近一个报销月（每月 1 日仍算上月）。 */
|
||
public static function defaultMonth(?CarbonImmutable $now = null): string
|
||
{
|
||
$now ??= CarbonImmutable::now();
|
||
$month = $now->subDay()->format('Y-m');
|
||
|
||
return max($month, self::FIRST_MONTH);
|
||
}
|
||
|
||
/** @return list<string> 可选报销月份（新到旧） */
|
||
public static function months(?CarbonImmutable $now = null): array
|
||
{
|
||
$months = [];
|
||
$cursor = CarbonImmutable::createFromFormat('!Y-m', self::defaultMonth($now));
|
||
while ($cursor->format('Y-m') >= self::FIRST_MONTH) {
|
||
$months[] = $cursor->format('Y-m');
|
||
$cursor = $cursor->subMonthNoOverflow();
|
||
}
|
||
|
||
return $months;
|
||
}
|
||
|
||
/** @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];
|
||
}
|
||
|
||
/**
|
||
* 某报销月份的全部种菜报销（已完成，每个工单号取最新一版），按完成时间倒序。
|
||
* 结果按月份缓存 10 分钟；单月约 1.5~2 万行。
|
||
*
|
||
* @return list<array<string,mixed>>
|
||
*/
|
||
public function rows(string $month): array
|
||
{
|
||
$window = self::window($month);
|
||
$status = $this->syncStatus();
|
||
if (! in_array($status['code'], ['ready', 'failed'], true)) {
|
||
throw new RuntimeException('business_mirror_dataset_not_ready');
|
||
}
|
||
|
||
// 镜像每几分钟增量一次，按版本做键会让缓存几乎每次失效；固定按月份缓存 10 分钟。
|
||
$key = 'planting-reimbursements:rows:v2:' . $month;
|
||
$cached = Cache::get($key);
|
||
if (is_array($cached)) {
|
||
return $cached;
|
||
}
|
||
|
||
$rows = $this->load($window['from'], $window['to']);
|
||
try {
|
||
Cache::put($key, $rows, self::ROWS_CACHE_SECONDS);
|
||
} catch (Throwable $exception) {
|
||
report($exception);
|
||
}
|
||
|
||
return $rows;
|
||
}
|
||
|
||
/** @return list<array<string,mixed>> */
|
||
private function load(string $from, string $to): array
|
||
{
|
||
$table = $this->dataset()->targetTable();
|
||
$query = $this->connection->table($table)
|
||
->where('woTypeId', self::WORK_TYPE_ID)
|
||
->where('completeTime', '>=', $from)
|
||
->where('completeTime', '<', $to)
|
||
->select([
|
||
'Id', 'woNo', 'status', 'createby', 'executors', 'completer',
|
||
'completeTime', 'created', 'businessFieldList',
|
||
])
|
||
->orderByDesc('completeTime')
|
||
->orderByDesc('woNo');
|
||
|
||
/** @var array<string,object> $latest 窗口内每个工单号的最新一版 */
|
||
$latest = [];
|
||
foreach ($this->boundedGet($query) as $record) {
|
||
$woNo = (string) $record->woNo;
|
||
if (! isset($latest[$woNo]) || (int) $record->Id > (int) $latest[$woNo]->Id) {
|
||
$latest[$woNo] = $record;
|
||
}
|
||
}
|
||
|
||
// 同一工单若有更新的一版（可能已改完成时间或被作废）落在窗口外，以最新一版为准，这里剔除。
|
||
$newestIds = [];
|
||
foreach (array_chunk(array_map('strval', array_keys($latest)), 1000) as $chunk) {
|
||
$versions = $this->connection->table($table)
|
||
->where('woTypeId', self::WORK_TYPE_ID)
|
||
->whereIn('woNo', $chunk)
|
||
->groupBy('woNo')
|
||
->selectRaw('woNo, MAX(Id) AS newest_id')
|
||
->get();
|
||
foreach ($versions as $version) {
|
||
$newestIds[(string) $version->woNo] = (int) $version->newest_id;
|
||
}
|
||
}
|
||
|
||
$rows = [];
|
||
foreach ($latest as $woNo => $record) {
|
||
if (($newestIds[(string) $woNo] ?? (int) $record->Id) > (int) $record->Id) {
|
||
continue;
|
||
}
|
||
// 最新一版已不是“已完成”（如完成后被作废）的工单不计入。
|
||
if ((string) $record->status !== self::STATUS_CONFIRMED) {
|
||
continue;
|
||
}
|
||
$rows[] = $this->decorate($record);
|
||
}
|
||
|
||
return $rows;
|
||
}
|
||
|
||
/** @return array<string,mixed> */
|
||
private function decorate(object $record): array
|
||
{
|
||
$fields = $this->fields((string) ($record->businessFieldList ?? ''));
|
||
$row = [
|
||
'work_order_no' => (string) $record->woNo,
|
||
'started_at' => $this->time($record->created ?? null),
|
||
'completed_at' => $this->time($record->completeTime ?? null),
|
||
'status' => '已完成',
|
||
'initiator' => (string) ($record->createby ?? ''),
|
||
'executor' => (string) ($record->executors ?? ''),
|
||
'completer' => (string) ($record->completer ?? ''),
|
||
];
|
||
foreach (self::FIELDS as $key => $fieldId) {
|
||
$row[$key] = $this->display($fields[$fieldId] ?? null);
|
||
}
|
||
foreach (self::MONEY_FIELDS as $key) {
|
||
$row[$key] = $this->money($row[$key]);
|
||
}
|
||
$row['erp_order_status'] = self::ERP_ORDER_STATUS[$row['erp_order_status']] ?? $row['erp_order_status'];
|
||
|
||
$items = $this->items($fields['itemInfo']['raw'] ?? null);
|
||
$row['product_code'] = implode("\n", array_filter(array_column($items, 'skuId')));
|
||
$row['product_name'] = implode("\n", array_filter(array_column($items, 'name')));
|
||
$row['product_quantity'] = implode("\n", array_filter(array_column($items, 'qty')));
|
||
$row['platform_product_id'] = implode("\n", array_filter(array_column($items, 'platformItemId')));
|
||
|
||
return $row;
|
||
}
|
||
|
||
/** @return array<string,array{value:mixed,label:mixed,raw:array<mixed>}> */
|
||
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 (isset($decoded['businessField']) && is_array($decoded['businessField'])) {
|
||
$decoded = $decoded['businessField'];
|
||
}
|
||
|
||
$fields = [];
|
||
foreach (is_array($decoded) ? $decoded : [] as $item) {
|
||
if (is_array($item) && isset($item['fieldId'])) {
|
||
$fields[(string) $item['fieldId']] = [
|
||
'value' => $item['value'] ?? ($item['valueList'] ?? null),
|
||
'label' => $item['valueLabel'] ?? null,
|
||
'raw' => $item,
|
||
];
|
||
}
|
||
}
|
||
|
||
return $fields;
|
||
}
|
||
|
||
/** @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 '';
|
||
}
|
||
|
||
/**
|
||
* @param array<mixed>|null $raw
|
||
* @return list<array<string,string>>
|
||
*/
|
||
private function items(?array $raw): array
|
||
{
|
||
$items = [];
|
||
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;
|
||
}
|
||
}
|
||
foreach ((array) ($raw['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);
|
||
}
|
||
}
|
||
|
||
return $items;
|
||
}
|
||
|
||
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, '.', '');
|
||
}
|
||
|
||
private function time(mixed $value): string
|
||
{
|
||
return substr(trim((string) $value), 0, 19);
|
||
}
|
||
|
||
/** @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 dataset(): BusinessMirrorDataset
|
||
{
|
||
return $this->datasets->get(self::DATASET);
|
||
}
|
||
}
|