xh-erp.7in6.com/application/common/library/ProcuremenDashboard.php

<?php

namespace app\common\library;

use think\Db;

/**
 * 控制台 — 协助采购（采购供应商）统计
 */
class ProcuremenDashboard
{
    /**
     * @return array{
     *   today: array{issued:int,confirm:int,approval:int,completed:int},
     *   week: array{issued:int,confirm:int,approval:int,completed:int},
     *   pending: array{confirm:int,approval:int},
     *   supplierRank: array{quote: array, selected: array, period_label: string},
     *   kpi: array<string, array{today:int,month:int,link:string,link_text:string}>,
     *   updated: string
     * }
     */
    public static function buildStats(): array
    {
        $todayStart = date('Y-m-d 00:00:00');
        $todayEnd = date('Y-m-d H:i:s');
        $weekStart = date('Y-m-d 00:00:00', strtotime('monday this week'));
        $weekEnd = date('Y-m-d H:i:s');

        return [
            'kpi'          => self::buildKpiCards(),
            'today'        => self::countStageMetrics($todayStart, $todayEnd),
            'week'         => self::countStageMetrics($weekStart, $weekEnd),
            'pending'      => self::countCurrentPending(),
            'supplierRank' => self::buildSupplierRankings(10, date('Y-m-01 00:00:00'), date('Y-m-t 23:59:59')),
            'last7'        => self::buildLast7DaysChart(),
            'updated'      => date('Y-m-d H:i:s'),
        ];
    }

    /**
     * 控制台首行 KPI：今日 / 本月
     *
     * @return array<string, mixed>
     */
    public static function buildKpiCards(): array
    {
        $todayStart = date('Y-m-d 00:00:00');
        $todayEnd = date('Y-m-d 23:59:59');
        $monthStart = date('Y-m-01 00:00:00');
        $monthEnd = date('Y-m-t 23:59:59');

        $inboundToday = self::countInboundScoreInRange($todayStart, $todayEnd);
        $inboundMonth = self::countInboundScoreInRange($monthStart, $monthEnd);

        return [
            'supplier' => [
                'total'     => self::countActiveSuppliers(),
                'link'      => '',
                'link_text' => '',
            ],
            // 兼容旧模板字段（图表面板仍可能引用）
            'confirm' => [
                'today'     => self::countQuotedPendingConfirmOrders($todayStart, $todayEnd),
                'month'     => self::countQuotedPendingConfirmOrders($monthStart, $monthEnd),
                'link'      => 'procuremen/audit',
                'link_text' => '供应商确认',
            ],
            'approval' => [
                'today'     => $inboundToday['total'],
                'month'     => $inboundMonth['total'],
                'link'      => 'procuremenarchive/index',
                'link_text' => '历史存证档案查询',
            ],
            'completed' => [
                'today'     => self::countNewRfqInRange($todayStart, $todayEnd),
                'month'     => self::countNewRfqInRange($monthStart, $monthEnd),
                'link'      => 'procuremen/rfqlist',
                'link_text' => '查询询价',
            ],
            // 新 KPI
            'import' => [
                'today'     => self::countImportDemandInRange($todayStart, $todayEnd),
                'month'     => self::countImportDemandInRange($monthStart, $monthEnd),
                'link'      => 'procuremen/pick',
                'link_text' => '采购供应商初选',
            ],
            'quote_pending' => [
                'today'     => self::countQuotedPendingConfirmOrders($todayStart, $todayEnd),
                'month'     => self::countQuotedPendingConfirmOrders($monthStart, $monthEnd),
                'link'      => 'procuremen/audit',
                'link_text' => '供应商确认',
            ],
            'inbound' => [
                'today'     => $inboundToday,
                'month'     => $inboundMonth,
                'link'      => 'procuremenarchive/index',
                'link_text' => '历史存证档案查询',
            ],
            'rfq_new' => [
                'today'     => self::countNewRfqInRange($todayStart, $todayEnd),
                'month'     => self::countNewRfqInRange($monthStart, $monthEnd),
                'link'      => 'procuremen/rfqlist',
                'link_text' => '查询询价',
            ],
            'rfq_quoted' => [
                'today'     => self::countQuotedRfqInRange($todayStart, $todayEnd),
                'month'     => self::countQuotedRfqInRange($monthStart, $monthEnd),
                'link'      => 'procuremen/rfqlist',
                'link_text' => '查询询价',
            ],
        ];
    }

    /**
     * 导入需求：初选池中按提交日期（dputrecord，空则 dStamp）落在区间内的工序条数
     */
    protected static function countImportDemandInRange(string $start, string $end): int
    {
        try {
            $timeExpr = 'COALESCE('
                . "NULLIF(NULLIF(TRIM(CAST(b.dputrecord AS CHAR(32))), ''), '0000-00-00'),"
                . "NULLIF(NULLIF(TRIM(CAST(a.dStamp AS CHAR(32))), ''), '0000-00-00')"
                . ')';
            $n = Db::table('scydgy')
                ->alias('a')
                ->join('mcyd b', 'b.ICYDID = a.ICYDID AND b.iStatus >= 10', 'inner')
                ->where([
                    'a.bwjg'    => 1,
                    'a.iEndBz'  => 0,
                    'a.iType'   => 0,
                    'a.iStatus' => 10,
                ])
                ->whereRaw("({$timeExpr}) >= ? AND ({$timeExpr}) <= ?", [$start, $end])
                ->count('a.ID');

            return (int)$n;
        } catch (\Throwable $e) {
            return 0;
        }
    }

    /**
     * 报价供应商待确认：当前待确认且至少一家已填单价；按下发时间（pick_time）落在区间内（今日数据）
     */
    protected static function countQuotedPendingConfirmOrders(string $start = '', string $end = ''): int
    {
        $rows = self::loadPendingStageRowsWithTime('confirm');
        if ($rows === []) {
            return 0;
        }
        $sidList = [];
        $orderBySid = [];
        foreach ($rows as $row) {
            if (!is_array($row)) {
                continue;
            }
            if ($start !== '' && $end !== '') {
                $normalized = self::normalizeRowForListMonth($row);
                if (!self::rowMatchesTimeRange($normalized, $start, $end, 'pick_time')) {
                    continue;
                }
                $row = $normalized;
            }
            $sid = (int)($row['scydgy_id'] ?? 0);
            if ($sid === 0) {
                continue;
            }
            $sidList[$sid] = true;
            $orderBySid[$sid] = $row;
        }
        if ($sidList === []) {
            return 0;
        }
        try {
            $details = Db::table('purchase_order_detail')
                ->where('scydgy_id', 'in', array_keys($sidList))
                ->field('scydgy_id,amount')
                ->select();
        } catch (\Throwable $e) {
            return 0;
        }
        if (!is_array($details)) {
            return 0;
        }
        $quotedSids = [];
        foreach ($details as $d) {
            if (!is_array($d)) {
                continue;
            }
            $amount = trim((string)($d['amount'] ?? ''));
            if ($amount === '' || $amount === '0' || $amount === '0.00') {
                continue;
            }
            $sid = (int)($d['scydgy_id'] ?? 0);
            if ($sid !== 0) {
                $quotedSids[$sid] = true;
            }
        }
        if ($quotedSids === []) {
            return 0;
        }
        $matched = [];
        foreach ($quotedSids as $sid => $_) {
            if (isset($orderBySid[$sid])) {
                $matched[] = $orderBySid[$sid];
            }
        }

        return self::countDistinctOrders($matched);
    }

    /**
     * @param array<string, mixed> $row
     */
    protected static function rowMatchesTimeRange(array $row, string $start, string $end, string $primary = 'pick_time'): bool
    {
        $listTime = self::resolveRowListTime($row, $primary);
        $ts = ProcuremenTime::parseToTimestamp($listTime);
        $startTs = ProcuremenTime::parseToTimestamp($start);
        $endTs = ProcuremenTime::parseToTimestamp($end);
        if ($ts <= 0 || $startTs <= 0 || $endTs <= 0) {
            return false;
        }

        return $ts >= $startTs && $ts <= $endTs;
    }

    /**
     * 已入库打分：合格 / 不合格订单数（按订单号去重）
     *
     * @return array{pass:int,fail:int,total:int}
     */
    protected static function countInboundScoreInRange(string $start, string $end): array
    {
        $empty = ['pass' => 0, 'fail' => 0, 'total' => 0];
        try {
            $rows = Db::table('purchase_order_inbound_score')
                ->whereRaw(
                    '(COALESCE(updatetime, createtime) >= ? AND COALESCE(updatetime, createtime) <= ?)',
                    [$start, $end]
                )
                ->field('ccydh,scydgy_id,result')
                ->select();
        } catch (\Throwable $e) {
            return $empty;
        }
        if (!is_array($rows) || $rows === []) {
            return $empty;
        }
        $passKeys = [];
        $failKeys = [];
        foreach ($rows as $row) {
            if (!is_array($row)) {
                continue;
            }
            $result = trim((string)($row['result'] ?? ''));
            $ccydh = trim((string)($row['ccydh'] ?? ''));
            $key = $ccydh !== '' ? $ccydh : ('_sid_' . (int)($row['scydgy_id'] ?? 0));
            if ($result === '合格') {
                $passKeys[$key] = true;
            } elseif ($result === '不合格') {
                $failKeys[$key] = true;
            }
        }
        $pass = count($passKeys);
        $fail = count($failKeys);

        return [
            'pass'  => $pass,
            'fail'  => $fail,
            'total' => $pass + $fail,
        ];
    }

    /**
     * 新增询价：手工单 scydgy_id&lt;0
     */
    protected static function countNewRfqInRange(string $start, string $end): int
    {
        try {
            $expr = self::pickTimeSqlExpr();
            $query = Db::table('purchase_order')->field('id,CCYDH');
            self::applyNotDeletedWhere($query);
            $query->where('scydgy_id', '<', 0);
            $query->whereRaw("({$expr}) >= ? AND ({$expr}) <= ?", [$start, $end]);
            $rows = $query->select();
        } catch (\Throwable $e) {
            return 0;
        }

        return self::countDistinctOrders(is_array($rows) ? $rows : []);
    }

    /**
     * 供应商已报价询价：区间内新增询价中，至少一家已填单价
     */
    protected static function countQuotedRfqInRange(string $start, string $end): int
    {
        try {
            $expr = self::pickTimeSqlExpr();
            $query = Db::table('purchase_order')->field('id,CCYDH,scydgy_id');
            self::applyNotDeletedWhere($query);
            $query->where('scydgy_id', '<', 0);
            $query->whereRaw("({$expr}) >= ? AND ({$expr}) <= ?", [$start, $end]);
            $rows = $query->select();
        } catch (\Throwable $e) {
            return 0;
        }
        if (!is_array($rows) || $rows === []) {
            return 0;
        }
        $sidList = [];
        $orderBySid = [];
        foreach ($rows as $row) {
            if (!is_array($row)) {
                continue;
            }
            $sid = (int)($row['scydgy_id'] ?? 0);
            if ($sid >= 0) {
                continue;
            }
            $sidList[$sid] = true;
            $orderBySid[$sid] = $row;
        }
        if ($sidList === []) {
            return 0;
        }
        try {
            $details = Db::table('purchase_order_detail')
                ->where('scydgy_id', 'in', array_keys($sidList))
                ->field('scydgy_id,amount')
                ->select();
        } catch (\Throwable $e) {
            return 0;
        }
        if (!is_array($details)) {
            return 0;
        }
        $quotedSids = [];
        foreach ($details as $d) {
            if (!is_array($d)) {
                continue;
            }
            $amount = trim((string)($d['amount'] ?? ''));
            if ($amount === '' || $amount === '0' || $amount === '0.00') {
                continue;
            }
            $sid = (int)($d['scydgy_id'] ?? 0);
            if ($sid < 0) {
                $quotedSids[$sid] = true;
            }
        }
        $matched = [];
        foreach ($quotedSids as $sid => $_) {
            if (isset($orderBySid[$sid])) {
                $matched[] = $orderBySid[$sid];
            }
        }

        return self::countDistinctOrders($matched);
    }

    /**
     * @param string[] $wflowValues
     */
    protected static function countWflowOrdersInPickRange(array $wflowValues, string $start, string $end): int
    {
        try {
            $expr = self::pickTimeSqlExpr();
            $query = Db::table('purchase_order')->field('id,CCYDH');
            self::applyNotDeletedWhere($query);
            $query->whereIn('wflow_status', $wflowValues);
            $query->whereRaw("({$expr}) >= ? AND ({$expr}) <= ?", [$start, $end]);
            $rows = $query->select();
        } catch (\Throwable $e) {
            return 0;
        }

        return self::countDistinctOrders(is_array($rows) ? $rows : []);
    }

    protected static function countQuoteSuppliersInPickRange(string $start, string $end): int
    {
        $scydgyIds = self::loadScydgyIdsInPickTimeRange($start, $end);
        if ($scydgyIds === []) {
            return 0;
        }
        try {
            $rows = Db::table('purchase_order_detail')
                ->where('scydgy_id', 'in', $scydgyIds)
                ->field('company_name,amount')
                ->select();
        } catch (\Throwable $e) {
            return 0;
        }
        if (!is_array($rows)) {
            return 0;
        }
        $names = [];
        foreach ($rows as $row) {
            if (!is_array($row)) {
                continue;
            }
            $name = trim((string)($row['company_name'] ?? ''));
            $amount = trim((string)($row['amount'] ?? ''));
            if ($name === '' || $amount === '' || $amount === '0' || $amount === '0.00') {
                continue;
            }
            $names[$name] = true;
        }

        return count($names);
    }

    protected static function countActiveSuppliers(): int
    {
        try {
            $query = Db::table('customer');
            $query->where(function ($q) {
                $q->where('status', 1)
                    ->whereOr('status', '1')
                    ->whereOr('status', '')
                    ->whereNull('status');
            });

            return (int)$query->count();
        } catch (\Throwable $e) {
            return self::countDistinctSuppliersFromDetails();
        }
    }

    protected static function countDistinctSuppliersFromDetails(): int
    {
        try {
            $rows = Db::table('purchase_order_detail')
                ->field('company_name')
                ->select();
        } catch (\Throwable $e) {
            return 0;
        }
        if (!is_array($rows)) {
            return 0;
        }
        $names = [];
        foreach ($rows as $row) {
            if (!is_array($row)) {
                continue;
            }
            $name = trim((string)($row['company_name'] ?? ''));
            if ($name !== '') {
                $names[$name] = true;
            }
        }

        return count($names);
    }

    protected static function countDistinctOrdersInPickTimeRange(string $start, string $end): int
    {
        try {
            $expr = self::pickTimeSqlExpr();
            $query = Db::table('purchase_order')->field('id,CCYDH');
            self::applyNotDeletedWhere($query);
            $query->whereRaw("({$expr}) >= ? AND ({$expr}) <= ?", [$start, $end]);
            $rows = $query->select();
        } catch (\Throwable $e) {
            return 0;
        }

        return self::countDistinctOrders(is_array($rows) ? $rows : []);
    }

    /**
     * 供应商排名（本月）：参与报价次数 / 被选中次数
     *
     * @return array{
     *   quote: array<int, array{rank:int,name:string,value:int,percent:int}>,
     *   selected: array<int, array{rank:int,name:string,value:int,percent:int}>,
     *   period_label: string,
     *   supplier_total: int
     * }
     */
    public static function buildSupplierRankings(int $limit = 10, ?string $start = null, ?string $end = null): array
    {
        $start = $start ?: date('Y-m-d 00:00:00', strtotime('first day of this month'));
        $end = $end ?: date('Y-m-t 23:59:59');

        return [
            'quote'          => self::rankSuppliersByQuoteInRange($start, $end, $limit),
            'selected'       => self::rankSuppliersBySelectedInRange($start, $end, $limit),
            'period_label'   => '本月',
            'supplier_total' => self::countActiveSuppliers(),
        ];
    }

    /**
     * @param array<string, int> $counts
     * @return array<int, array{rank:int,name:string,value:int,percent:int}>
     */
    protected static function formatSupplierRankListFromCounts(array $counts, int $limit): array
    {
        if ($counts === []) {
            return [];
        }
        arsort($counts);
        $top = array_slice($counts, 0, max(1, $limit), true);
        $max = $top ? max($top) : 0;
        $out = [];
        $rank = 1;
        foreach ($top as $name => $value) {
            $out[] = [
                'rank'    => $rank++,
                'name'    => (string)$name,
                'value'   => (int)$value,
                'percent' => $max > 0 ? (int)round($value / $max * 100) : 0,
            ];
        }

        return $out;
    }

    /**
     * @param array<string, array<string|int, bool>> $buckets
     * @return array<int, array{rank:int,name:string,value:int,percent:int}>
     */
    protected static function formatSupplierRankList(array $buckets, int $limit): array
    {
        if ($buckets === []) {
            return [];
        }
        $counts = [];
        foreach ($buckets as $name => $keys) {
            $counts[$name] = is_array($keys) ? count($keys) : 0;
        }
        arsort($counts);
        $top = array_slice($counts, 0, max(1, $limit), true);
        $max = $top ? max($top) : 0;
        $out = [];
        $rank = 1;
        foreach ($top as $name => $value) {
            $out[] = [
                'rank'    => $rank++,
                'name'    => (string)$name,
                'value'   => (int)$value,
                'percent' => $max > 0 ? (int)round($value / $max * 100) : 0,
            ];
        }

        return $out;
    }

    /**
     * 按已填写报价金额的供应商统计参与报价次数（明细行数）
     *
     * @return array<int, array{rank:int,name:string,value:int,percent:int}>
     */
    protected static function rankSuppliersByQuoteInRange(string $start, string $end, int $limit): array
    {
        $scydgyIds = self::loadScydgyIdsInPickTimeRange($start, $end);
        if ($scydgyIds === []) {
            return [];
        }
        try {
            $rows = Db::table('purchase_order_detail')
                ->where('scydgy_id', 'in', $scydgyIds)
                ->field('company_name,amount')
                ->select();
        } catch (\Throwable $e) {
            return [];
        }
        if (!is_array($rows)) {
            return [];
        }
        $counts = [];
        foreach ($rows as $row) {
            if (!is_array($row)) {
                continue;
            }
            $name = trim((string)($row['company_name'] ?? ''));
            $amount = trim((string)($row['amount'] ?? ''));
            if ($name === '' || $amount === '' || $amount === '0' || $amount === '0.00') {
                continue;
            }
            $counts[$name] = ($counts[$name] ?? 0) + 1;
        }

        return self::formatSupplierRankListFromCounts($counts, $limit);
    }

    /**
     * 按主表 pick_company_name 统计被选中订单数
     *
     * @return array<int, array{rank:int,name:string,value:int,percent:int}>
     */
    protected static function rankSuppliersBySelectedInRange(string $start, string $end, int $limit): array
    {
        try {
            $expr = self::pickTimeSqlExpr();
            $query = Db::table('purchase_order')
                ->field('CCYDH,pick_company_name');
            self::applyNotDeletedWhere($query);
            $query->whereRaw("({$expr}) >= ? AND ({$expr}) <= ?", [$start, $end]);
            $query->whereRaw("pick_company_name IS NOT NULL AND TRIM(CAST(pick_company_name AS CHAR(255))) <> ''");
            $rows = $query->select();
        } catch (\Throwable $e) {
            return [];
        }
        if (!is_array($rows)) {
            return [];
        }
        $buckets = [];
        foreach ($rows as $row) {
            if (!is_array($row)) {
                continue;
            }
            $name = trim((string)($row['pick_company_name'] ?? ''));
            if ($name === '') {
                continue;
            }
            $orderKey = self::resolveOrderKey($row);
            if (!isset($buckets[$name])) {
                $buckets[$name] = [];
            }
            $buckets[$name][$orderKey] = true;
        }

        return self::formatSupplierRankList($buckets, $limit);
    }

    /**
     * @return int[]
     */
    protected static function loadScydgyIdsInPickTimeRange(string $start, string $end): array
    {
        try {
            $expr = self::pickTimeSqlExpr();
            $query = Db::table('purchase_order')->field('scydgy_id');
            self::applyNotDeletedWhere($query);
            $query->whereRaw("({$expr}) >= ? AND ({$expr}) <= ?", [$start, $end]);
            $rows = $query->select();
        } catch (\Throwable $e) {
            return [];
        }
        if (!is_array($rows)) {
            return [];
        }
        $ids = [];
        foreach ($rows as $row) {
            if (!is_array($row)) {
                continue;
            }
            $sid = (int)($row['scydgy_id'] ?? 0);
            if ($sid > 0) {
                $ids[$sid] = true;
            }
        }

        return array_keys($ids);
    }

    /**
     * 近 7 天每日趋势（按订单号去重）
     *
     * @return array{
     *   labels: string[],
     *   issued: int[],
     *   confirm: int[],
     *   approval: int[],
     *   completed: int[]
     * }
     */
    public static function buildLast7DaysChart(): array
    {
        $dayKeys = [];
        $labels = [];
        for ($i = 6; $i >= 0; $i--) {
            $day = date('Y-m-d', strtotime('-' . $i . ' days'));
            $dayKeys[] = $day;
            $labels[] = date('m-d', strtotime($day));
        }
        if ($dayKeys === []) {
            return ['labels' => [], 'issued' => [], 'confirm' => [], 'approval' => [], 'completed' => []];
        }

        $rangeStart = $dayKeys[0] . ' 00:00:00';
        $rangeEnd = $dayKeys[count($dayKeys) - 1] . ' 23:59:59';
        $startTs = ProcuremenTime::parseToTimestamp($rangeStart);
        $endTs = ProcuremenTime::parseToTimestamp($rangeEnd);

        $issuedBuckets = self::bucketOperLogOrdersByDay(
            self::loadOperLogsInRange(['issue_submit'], $startTs, $endTs),
            $dayKeys,
            $startTs,
            $endTs
        );
        $confirmBuckets = self::bucketOperLogOrdersByDay(
            self::loadOperLogsInRange(['issue_submit'], $startTs, $endTs),
            $dayKeys,
            $startTs,
            $endTs
        );
        $approvalBuckets = self::bucketOperLogOrdersByDay(
            self::loadOperLogsInRange(['audit_select'], $startTs, $endTs),
            $dayKeys,
            $startTs,
            $endTs
        );
        $completedBuckets = self::bucketOperLogOrdersByDay(
            self::loadOperLogsInRange(['purchase_confirm', 'mark_complete'], $startTs, $endTs),
            $dayKeys,
            $startTs,
            $endTs
        );

        return [
            'labels'    => $labels,
            'issued'    => self::bucketCounts($issuedBuckets, $dayKeys),
            'confirm'   => self::bucketCounts($confirmBuckets, $dayKeys),
            'approval'  => self::bucketCounts($approvalBuckets, $dayKeys),
            'completed' => self::bucketCounts($completedBuckets, $dayKeys),
        ];
    }

    /**
     * @param string[] $dayKeys
     * @return array<string, array<string, bool>>
     */
    protected static function emptyDayBuckets(array $dayKeys): array
    {
        $out = [];
        foreach ($dayKeys as $day) {
            $out[$day] = [];
        }

        return $out;
    }

    /**
     * @param array<string, array<string, bool>> $buckets
     * @param string[]                         $dayKeys
     * @return int[]
     */
    protected static function bucketCounts(array $buckets, array $dayKeys): array
    {
        $out = [];
        foreach ($dayKeys as $day) {
            $out[] = isset($buckets[$day]) ? count($buckets[$day]) : 0;
        }

        return $out;
    }

    /**
     * @param array<string, mixed> $row
     * @param string[]             $dayKeys
     */
    protected static function resolveRowDayKey(array $row, array $dayKeys): ?string
    {
        $ts = 0;
        foreach (['pick_time', 'createtime', 'dputrecord', 'dStamp'] as $key) {
            $t = ProcuremenTime::parseToTimestamp($row[$key] ?? null);
            if ($t > $ts) {
                $ts = $t;
            }
        }
        if ($ts <= 0) {
            return null;
        }
        $day = date('Y-m-d', $ts);

        return in_array($day, $dayKeys, true) ? $day : null;
    }

    /**
     * @param array<string, mixed> $row
     */
    protected static function resolveOrderKey(array $row): string
    {
        $dh = trim((string)($row['CCYDH'] ?? ''));

        return $dh !== '' ? $dh : ('_id_' . (int)($row['id'] ?? 0));
    }

    /**
     * @param array<string, mixed> $row
     */
    protected static function isPendingConfirmRow(array $row): bool
    {
        return ProcuremenStatus::isWflowPendingConfirm($row['wflow_status'] ?? '');
    }

    /**
     * @param array<int, string> $actions
     * @return array<int, array{scydgy_id:int, createtime:mixed}>
     */
    protected static function loadOperLogsInRange(array $actions, int $startTs, int $endTs): array
    {
        try {
            $logs = Db::table('purchase_order_oper_log')
                ->where(ProcuremenOperLog::COL_ACTION, 'in', ProcuremenOperLog::expandActionQueryValues($actions))
                ->field(ProcuremenOperLog::COL_SCYDGY_ID . ',' . ProcuremenOperLog::COL_TIME)
                ->select();
        } catch (\Throwable $e) {
            return [];
        }
        if (!is_array($logs)) {
            return [];
        }
        $out = [];
        foreach ($logs as $log) {
            if (!is_array($log)) {
                continue;
            }
            $log = ProcuremenOperLog::normalizeRow($log);
            $ts = ProcuremenTime::parseToTimestamp($log['createtime'] ?? null);
            if ($ts < $startTs || $ts > $endTs) {
                continue;
            }
            $sid = (int)($log['scydgy_id'] ?? 0);
            if ($sid > 0) {
                $out[] = ['scydgy_id' => $sid, 'createtime' => $log['createtime'] ?? null];
            }
        }

        return $out;
    }

    /**
     * @param array<int, array{scydgy_id:int, createtime:mixed}> $logs
     * @param string[]                                           $dayKeys
     * @return array<string, array<string, bool>>
     */
    protected static function bucketOperLogOrdersByDay(array $logs, array $dayKeys, int $startTs, int $endTs): array
    {
        $buckets = self::emptyDayBuckets($dayKeys);
        if ($logs === []) {
            return $buckets;
        }
        $sidSet = [];
        foreach ($logs as $log) {
            $sidSet[(int)$log['scydgy_id']] = true;
        }
        $orderMap = [];
        foreach (self::loadOrdersByScydgyIds(array_keys($sidSet)) as $row) {
            if (!is_array($row)) {
                continue;
            }
            $sid = (int)($row['scydgy_id'] ?? 0);
            if ($sid > 0) {
                $orderMap[$sid] = self::resolveOrderKey($row);
            }
        }
        foreach ($logs as $log) {
            $sid = (int)$log['scydgy_id'];
            if ($sid <= 0 || !isset($orderMap[$sid])) {
                continue;
            }
            $ts = ProcuremenTime::parseToTimestamp($log['createtime'] ?? null);
            if ($ts < $startTs || $ts > $endTs) {
                continue;
            }
            $day = date('Y-m-d', $ts);
            if (!isset($buckets[$day])) {
                continue;
            }
            $buckets[$day][$orderMap[$sid]] = true;
        }

        return $buckets;
    }

    /**
     * 按操作日志统计时段内去重订单数（与近7天图口径一致）
     *
     * @param string[] $actions
     */
    protected static function countOperLogDistinctOrdersInRange(array $actions, string $start, string $end): int
    {
        $startTs = ProcuremenTime::parseToTimestamp($start);
        $endTs = ProcuremenTime::parseToTimestamp($end);
        if ($startTs <= 0 || $endTs <= 0 || $actions === []) {
            return 0;
        }
        $logs = self::loadOperLogsInRange($actions, $startTs, $endTs);
        if ($logs === []) {
            return 0;
        }
        $sidSet = [];
        foreach ($logs as $log) {
            $sid = (int)($log['scydgy_id'] ?? 0);
            if ($sid > 0) {
                $sidSet[$sid] = true;
            }
        }
        if ($sidSet === []) {
            return 0;
        }
        $keys = [];
        foreach (self::loadOrdersByScydgyIds(array_keys($sidSet)) as $row) {
            if (!is_array($row)) {
                continue;
            }
            $keys[self::resolveOrderKey($row)] = true;
        }

        return count($keys);
    }

    /**
     * 本月待处理：与「确认供应商 / 采购确认」列表一致（当前队列 + 下发月份 + 订单号去重）
     */
    protected static function countPendingStageOrdersInMonth(string $stage, string $monthStart, string $monthEnd): int
    {
        $rows = self::loadPendingStageRowsWithTime($stage);
        if ($rows === []) {
            return 0;
        }
        $matched = [];
        foreach ($rows as $row) {
            if (!is_array($row)) {
                continue;
            }
            $normalized = self::normalizeRowForListMonth($row);
            if (!self::rowMatchesMonthRange($normalized, $monthStart, $monthEnd, 'pick_time')) {
                continue;
            }
            $matched[] = $normalized;
        }

        return self::countDistinctOrders($matched);
    }

    /**
     * @return array<int, array<string, mixed>>
     */
    protected static function loadPendingStageRowsWithTime(string $stage): array
    {
        try {
            $fields = 'id,CCYDH,scydgy_id,pick_time,createtime,dputrecord,dStamp,wflow_status,status';
            $query = Db::table('purchase_order')->field($fields);
            self::applyNotDeletedWhere($query);
            if ($stage === 'confirm') {
                $query->whereIn('wflow_status', ProcuremenStatus::wflowPendingConfirmValues());
            } else {
                $vals = ProcuremenStatus::wflowPendingApprovalValues();
                $quoted = [];
                foreach ($vals as $v) {
                    $quoted[] = "'" . str_replace("'", "''", (string)$v) . "'";
                }
                if ($quoted === []) {
                    return [];
                }
                $query->whereRaw('TRIM(CAST(wflow_status AS CHAR)) IN (' . implode(',', $quoted) . ')');
                $query->whereRaw('NOT (' . ProcuremenStatus::sqlPoCompleted('status') . ')');
            }
            $rows = $query->select();
        } catch (\Throwable $e) {
            return [];
        }

        return is_array($rows) ? $rows : [];
    }

    /**
     * 列表月份筛选前补齐时间字段（与 buildListRowFromPurchaseOrderDbRow 回退一致）
     *
     * @param array<string, mixed> $row
     * @return array<string, mixed>
     */
    protected static function normalizeRowForListMonth(array $row): array
    {
        $r = $row;
        $r['createtime'] = ProcuremenTime::formatDisplayDateTime($row['createtime'] ?? null);

        $dpOut = trim((string)($r['dputrecord'] ?? ''));
        if ($dpOut === '' || stripos($dpOut, '0000-00-00') === 0) {
            $fallback = trim((string)($r['dStamp'] ?? ''));
            if ($fallback !== '' && stripos($fallback, '0000-00-00') !== 0) {
                $r['dputrecord'] = $fallback;
            } elseif ($r['createtime'] !== '') {
                $r['dputrecord'] = $r['createtime'];
            }
        }

        $pickOut = trim((string)($r['pick_time'] ?? ''));
        if ($pickOut === '' || stripos($pickOut, '0000-00-00') === 0) {
            foreach (['createtime', 'dputrecord', 'dStamp'] as $pickFallbackKey) {
                $pickFallback = trim((string)($r[$pickFallbackKey] ?? ''));
                if ($pickFallback !== '' && stripos($pickFallback, '0000-00-00') !== 0) {
                    $r['pick_time'] = $pickFallback;
                    break;
                }
            }
        }

        return $r;
    }

    /**
     * @param array<string, mixed> $row
     */
    protected static function resolveRowListTime(array $row, string $primary = 'pick_time'): string
    {
        $keys = array_values(array_unique([$primary, 'pick_time', 'createtime', 'dputrecord', 'dStamp']));
        foreach ($keys as $k) {
            $t = trim((string)($row[$k] ?? ''));
            if ($t !== '' && stripos($t, '0000-00-00') !== 0) {
                return $t;
            }
        }

        return '';
    }

    /**
     * @param array<string, mixed> $row
     */
    protected static function rowMatchesMonthRange(array $row, string $monthStart, string $monthEnd, string $primary = 'pick_time'): bool
    {
        $listTime = self::resolveRowListTime($row, $primary);
        if ($listTime === '' || stripos($listTime, '0000-00-00') === 0
            || !preg_match('/^([12]\d{3})-(\d{1,2})-(\d{1,2})/', $listTime, $m)) {
            return false;
        }
        $mo = (int)$m[2];
        if ($mo < 1 || $mo > 12) {
            return false;
        }
        $ymRow = $m[1] . '-' . str_pad((string)$mo, 2, '0', STR_PAD_LEFT);
        $rowMonthStart = $ymRow . '-01 00:00:00';
        $rowMonthEnd = date('Y-m-t 23:59:59', strtotime($rowMonthStart));

        return !(strcmp($rowMonthEnd, $monthStart) < 0 || strcmp($rowMonthStart, $monthEnd) > 0);
    }

    /**
     * @return array{issued:int,confirm:int,approval:int,completed:int}
     */
    protected static function countStageMetrics(string $start, string $end): array
    {
        return [
            'issued'    => self::countIssuedInRange($start, $end),
            'confirm'   => self::countOperLogDistinctOrdersInRange(['issue_submit'], $start, $end),
            'approval'  => self::countOperLogDistinctOrdersInRange(['audit_select'], $start, $end),
            'completed' => self::countCompletedInRange($start, $end),
        ];
    }

    /**
     * @return array{confirm:int,approval:int}
     */
    protected static function countCurrentPending(): array
    {
        return [
            'confirm'  => self::countDistinctOrders(self::loadPendingConfirmRows()),
            'approval' => self::countDistinctOrders(self::loadPendingApprovalRows()),
        ];
    }

    protected static function countIssuedInRange(string $start, string $end): int
    {
        $startTs = ProcuremenTime::parseToTimestamp($start);
        $endTs = ProcuremenTime::parseToTimestamp($end);
        if ($startTs <= 0 || $endTs <= 0) {
            return 0;
        }

        $confirmSidSet = self::loadPendingConfirmScydgySet();
        $keys = [];

        $logs = self::loadOperLogsInRange(['issue_submit'], $startTs, $endTs);
        foreach ($logs as $log) {
            $sid = (int)($log['scydgy_id'] ?? 0);
            if ($sid <= 0 || isset($confirmSidSet[$sid])) {
                continue;
            }
            foreach (self::loadOrdersByScydgyIds([$sid]) as $row) {
                if (!is_array($row)) {
                    continue;
                }
                $keys[self::resolveOrderKey($row)] = true;
            }
        }
        if ($keys !== []) {
            return count($keys);
        }

        return self::countIssuedByPickTimePastConfirm($start, $end);
    }

    /**
     * 无下发日志时：本时段 pick_time 内且流程已进入待审批/已审核（不含仍停在待确认）
     */
    protected static function countIssuedByPickTimePastConfirm(string $start, string $end): int
    {
        try {
            $expr = self::pickTimeSqlExpr();
            $query = Db::table('purchase_order')->field('id,CCYDH,wflow_status');
            self::applyNotDeletedWhere($query);
            $query->whereRaw("({$expr}) >= ? AND ({$expr}) <= ?", [$start, $end]);
            $query->whereIn('wflow_status', array_merge(
                ProcuremenStatus::wflowPendingApprovalValues(),
                ProcuremenStatus::wflowApprovedValues()
            ));
            $rows = $query->select();
        } catch (\Throwable $e) {
            return 0;
        }

        return self::countDistinctOrders(is_array($rows) ? $rows : []);
    }

    /**
     * @return array<int, bool>
     */
    protected static function loadPendingConfirmScydgySet(): array
    {
        $set = [];
        foreach (self::loadPendingConfirmRows() as $row) {
            if (!is_array($row)) {
                continue;
            }
            $sid = (int)($row['scydgy_id'] ?? 0);
            if ($sid > 0) {
                $set[$sid] = true;
            }
        }

        return $set;
    }

    protected static function countCompletedInRange(string $start, string $end): int
    {
        $startTs = ProcuremenTime::parseToTimestamp($start);
        $endTs = ProcuremenTime::parseToTimestamp($end);
        if ($startTs <= 0 || $endTs <= 0) {
            return 0;
        }
        try {
            $logs = Db::table('purchase_order_oper_log')
                ->where(ProcuremenOperLog::COL_ACTION, 'in', ProcuremenOperLog::expandActionQueryValues(['purchase_confirm', 'mark_complete']))
                ->field(ProcuremenOperLog::COL_SCYDGY_ID . ',' . ProcuremenOperLog::COL_TIME)
                ->select();
        } catch (\Throwable $e) {
            return 0;
        }
        if (!is_array($logs) || $logs === []) {
            return 0;
        }
        $sidSet = [];
        foreach ($logs as $log) {
            if (!is_array($log)) {
                continue;
            }
            $log = ProcuremenOperLog::normalizeRow($log);
            $ts = ProcuremenTime::parseToTimestamp($log['createtime'] ?? null);
            if ($ts < $startTs || $ts > $endTs) {
                continue;
            }
            $sid = (int)($log['scydgy_id'] ?? 0);
            if ($sid > 0) {
                $sidSet[$sid] = true;
            }
        }
        if ($sidSet === []) {
            return 0;
        }

        return self::countDistinctOrders(self::loadOrdersByScydgyIds(array_keys($sidSet)));
    }

    /**
     * @param int[] $scydgyIds
     * @return array<int, array<string, mixed>>
     */
    protected static function loadOrdersByScydgyIds(array $scydgyIds): array
    {
        $scydgyIds = array_values(array_unique(array_filter(array_map('intval', $scydgyIds))));
        if ($scydgyIds === []) {
            return [];
        }
        try {
            $query = Db::table('purchase_order')
                ->where('scydgy_id', 'in', $scydgyIds)
                ->field('id,CCYDH,scydgy_id,wflow_status');
            self::applyNotDeletedWhere($query);
            $rows = $query->select();
        } catch (\Throwable $e) {
            return [];
        }

        return is_array($rows) ? $rows : [];
    }

    /**
     * @return array<int, array<string, mixed>>
     */
    protected static function loadPendingConfirmRows(): array
    {
        try {
            $query = Db::table('purchase_order')->field('id,CCYDH,scydgy_id');
            self::applyNotDeletedWhere($query);
            $query->whereIn('wflow_status', ProcuremenStatus::wflowPendingConfirmValues());
            $rows = $query->select();
        } catch (\Throwable $e) {
            return [];
        }

        return is_array($rows) ? $rows : [];
    }

    /**
     * @return array<int, array<string, mixed>>
     */
    protected static function loadPendingApprovalRows(): array
    {
        try {
            $query = Db::table('purchase_order')->field('id,CCYDH');
            self::applyNotDeletedWhere($query);
            $query->whereIn('wflow_status', ProcuremenStatus::wflowPendingApprovalValues());
            $query->whereRaw('NOT (' . ProcuremenStatus::sqlPoCompleted('status') . ')');
            $rows = $query->select();
        } catch (\Throwable $e) {
            return [];
        }

        return is_array($rows) ? $rows : [];
    }

    /**
     * @param array<int, array<string, mixed>> $rows
     */
    protected static function countDistinctOrders(array $rows): int
    {
        if ($rows === []) {
            return 0;
        }
        $keys = [];
        foreach ($rows as $row) {
            if (!is_array($row)) {
                continue;
            }
            $dh = trim((string)($row['CCYDH'] ?? ''));
            $keys[$dh !== '' ? $dh : ('_id_' . (int)($row['id'] ?? 0))] = true;
        }

        return count($keys);
    }

    protected static function applyNotDeletedWhere($query): void
    {
        $query->whereRaw(
            '(mod_rq IS NULL OR TRIM(CAST(mod_rq AS CHAR(32))) = \'\' OR TRIM(CAST(mod_rq AS CHAR(32))) LIKE \'0000-00-00%\')'
        );
    }

    protected static function pickTimeSqlExpr(): string
    {
        $norm = function (string $col): string {
            return "NULLIF(NULLIF(TRIM(CAST({$col} AS CHAR(32))), ''), '0000-00-00')";
        };
        $createtime = 'IF(createtime > 946684800, FROM_UNIXTIME(createtime), ' . $norm('createtime') . ')';
        $parts = [
            $norm('pick_time'),
            $createtime,
            $norm('dputrecord'),
            $norm('dStamp'),
        ];

        return 'COALESCE(' . implode(', ', $parts) . ')';
    }
}