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, array{today:int,month:int,link:string,link_text:string}>
     */
    public static function buildKpiCards(): array
    {
        $todayStart = date('Y-m-d 00:00:00');
        $todayEnd = date('Y-m-d H:i:s');
        $monthStart = date('Y-m-01 00:00:00');
        $monthEnd = date('Y-m-t 23:59:59');
        $pending = self::countCurrentPending();

        return [
            'supplier' => [
                'total'     => self::countActiveSuppliers(),
                'link'      => '',
                'link_text' => '',
            ],
            'confirm' => [
                'today'     => (int)$pending['confirm'],
                'month'     => self::countOperLogDistinctOrdersInRange(['issue_submit'], $monthStart, $monthEnd),
                'link'      => 'procuremen/audit',
                'link_text' => '供应商确认',
            ],
            'approval' => [
                'today'     => (int)$pending['approval'],
                'month'     => self::countOperLogDistinctOrdersInRange(['audit_select'], $monthStart, $monthEnd),
                'link'      => 'procuremen/confirm',
                'link_text' => '供应商审批',
            ],
            'completed' => [
                'today'     => self::countCompletedInRange($todayStart, $todayEnd),
                'month'     => self::countCompletedInRange($monthStart, $monthEnd),
                'link'      => 'procuremenarchive/index',
                'link_text' => '历史存证档案查询',
            ],
        ];
    }

    /**
     * @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::emptyDayBuckets($dayKeys);

        try {
            $expr = self::pickTimeSqlExpr();
            $query = Db::table('purchase_order')->field('CCYDH,pick_time,createtime,dputrecord,dStamp,wflow_status,status');
            self::applyNotDeletedWhere($query);
            $query->whereRaw("({$expr}) >= ? AND ({$expr}) <= ?", [$rangeStart, $rangeEnd]);
            $rows = $query->select();
        } catch (\Throwable $e) {
            $rows = [];
        }
        if (is_array($rows)) {
            foreach ($rows as $row) {
                if (!is_array($row)) {
                    continue;
                }
                $day = self::resolveRowDayKey($row, $dayKeys);
                if ($day === null) {
                    continue;
                }
                $orderKey = self::resolveOrderKey($row);
                if (self::isPendingConfirmRow($row)) {
                    $confirmBuckets[$day][$orderKey] = true;
                }
            }
        }

        $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('action', 'in', $actions)
                ->field('scydgy_id,createtime')
                ->select();
        } catch (\Throwable $e) {
            return [];
        }
        if (!is_array($logs)) {
            return [];
        }
        $out = [];
        foreach ($logs as $log) {
            if (!is_array($log)) {
                continue;
            }
            $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);
    }

    /**
     * @return array{issued:int,confirm:int,approval:int,completed:int}
     */
    protected static function countStageMetrics(string $start, string $end): array
    {
        $pending = self::countCurrentPending();

        return [
            // 已下发：本时段内通知下发，且当前已离开「待确认」队列（不与待确认重复）
            'issued'    => self::countIssuedInRange($start, $end),
            // 待确认 / 待审批：与列表一致，统计当前积压（不按 pick_time 截断）
            'confirm'   => $pending['confirm'],
            'approval'  => $pending['approval'],
            '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('action', 'in', ['purchase_confirm', 'mark_complete'])
                ->field('scydgy_id,createtime')
                ->select();
        } catch (\Throwable $e) {
            return 0;
        }
        if (!is_array($logs) || $logs === []) {
            return 0;
        }
        $sidSet = [];
        foreach ($logs as $log) {
            if (!is_array($log)) {
                continue;
            }
            $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) . ')';
    }
}