xh-erp.7in6.com/application/admin/controller/Procuremenexport.php

<?php

namespace app\admin\controller;

use app\common\controller\Backend;
use app\common\library\ProcuremenStatus;
use think\Db;

/**
 * 协助采购 — 月度报表导出列表
 *
 * @icon fa fa-file-excel-o
 */
class Procuremenexport extends Backend
{
    protected $searchFields = 'CCYDH,CYJMC,CGYMC';

    public function index()
    {
        $this->relationSearch = false;
        $this->request->filter(['strip_tags', 'trim']);
        if ($this->request->isAjax()) {
            $ym = trim((string)$this->request->request('ym', date('Y-m')));
            if (!preg_match('/^\d{4}-\d{2}$/', $ym)) {
                $ym = date('Y-m');
            }
            try {
                $rows = $this->buildExportableOrderRows($ym);

                return json(['total' => count($rows), 'rows' => $rows, 'ym' => $ym]);
            } catch (\Throwable $e) {
                return json(['total' => 0, 'rows' => [], 'ym' => $ym, 'msg' => $e->getMessage()]);
            }
        }

        return $this->view->fetch();
    }

    /**
     * 已完结、可按月份导出的订单（合并为一单一行）
     */
    public function exportable()
    {
        if (!$this->request->isAjax()) {
            $this->error(__('Invalid parameters'));
        }
        $ym = trim((string)$this->request->get('ym', date('Y-m')));
        if (!preg_match('/^\d{4}-\d{2}$/', $ym)) {
            $ym = date('Y-m');
        }
        try {
            $rows = $this->buildExportableOrderRows($ym);

            return json(['total' => count($rows), 'rows' => $rows, 'ym' => $ym]);
        } catch (\Throwable $e) {
            return json(['total' => 0, 'rows' => [], 'ym' => $ym, 'msg' => $e->getMessage()]);
        }
    }

    /**
     * 已完结订单（与历史存证一致），按完结月份筛选后合并为一单一行
     *
     * @return array<int, array<string, mixed>>
     */
    protected function buildExportableOrderRows(string $ym): array
    {
        try {
            $query = Db::table('purchase_order');
            $query->whereRaw(ProcuremenStatus::sqlPoCompleted('status'));
            $dbRows = $query
                ->field('id,scydgy_id,CCYDH,CYJMC,CGYMC,pick_time,createtime,dStamp,updatetime')
                ->order('id', 'desc')
                ->select();
        } catch (\Throwable $e) {
            $dbRows = [];
        }
        if (!is_array($dbRows)) {
            $dbRows = [];
        }

        $sidList = [];
        foreach ($dbRows as $r) {
            if (!is_array($r)) {
                continue;
            }
            $sid = (int)($r['scydgy_id'] ?? 0);
            if ($sid > 0) {
                $sidList[$sid] = true;
            }
        }
        $amountMap = $this->loadDetailAmountSumByScydgyIds(array_keys($sidList));
        $completeTsMap = $this->loadOperLogTimestampMap(array_keys($sidList), ['purchase_confirm', 'mark_complete']);

        $pool = [];
        foreach ($dbRows as $r) {
            if (!is_array($r)) {
                continue;
            }
            $rowYm = $this->resolvePurchaseOrderRowYm($r, $completeTsMap);
            if ($rowYm !== $ym) {
                continue;
            }
            $sid = (int)($r['scydgy_id'] ?? 0);
            $doneText = $this->formatPurchaseOrderDoneTime($r, $completeTsMap);
            $pool[] = [
                'id'              => (int)($r['id'] ?? 0),
                'scydgy_id'       => $sid,
                'CCYDH'           => trim((string)($r['CCYDH'] ?? '')),
                'CYJMC'           => trim((string)($r['CYJMC'] ?? '')),
                'CGYMC'           => trim((string)($r['CGYMC'] ?? '')),
                'ym'              => $rowYm,
                'createtime_text' => $doneText,
                'detail_amount'   => (float)($amountMap[$sid] ?? 0),
            ];
        }

        $merged = $this->collapseExportRowsByOrder($pool);
        foreach ($merged as &$row) {
            $amt = 0.0;
            $cnt = 0;
            if (!empty($row['_merge_rows']) && is_array($row['_merge_rows'])) {
                foreach ($row['_merge_rows'] as $mr) {
                    if (!is_array($mr)) {
                        continue;
                    }
                    $amt += (float)($mr['detail_amount'] ?? 0);
                    $cnt++;
                    if (trim((string)($row['ym'] ?? '')) === '') {
                        $ymv = trim((string)($mr['ym'] ?? ''));
                        if ($ymv !== '') {
                            $row['ym'] = $ymv;
                        }
                    }
                }
            } else {
                $amt = (float)($row['detail_amount'] ?? 0);
                $cnt = 1;
            }
            $row['row_count'] = $cnt;
            $row['total_amount'] = round($amt, 2);
            $row['total_amount_text'] = number_format($amt, 2, '.', ',');
            unset($row['_merge_rows'], $row['detail_amount']);
        }
        unset($row);

        return $merged;
    }

    /**
     * 工序行金额合计：优先已选定明细 status=1，否则汇总有效明细上的加工金额
     *
     * @param int[] $scydgyIds
     * @return array<int, float>
     */
    protected function loadDetailAmountSumByScydgyIds(array $scydgyIds): array
    {
        $picked = $this->loadPickedAmountSumByScydgyIds($scydgyIds);
        $scydgyIds = array_values(array_unique(array_filter(array_map('intval', $scydgyIds))));
        $missing = [];
        foreach ($scydgyIds as $sid) {
            if ($sid > 0 && !isset($picked[$sid])) {
                $missing[] = $sid;
            }
        }
        if ($missing === []) {
            return $picked;
        }
        try {
            $rows = Db::table('purchase_order_detail')
                ->where('scydgy_id', 'in', $missing)
                ->whereRaw(ProcuremenStatus::sqlPodNotVoid('status'))
                ->field('scydgy_id,amount')
                ->select();
        } catch (\Throwable $e) {
            $rows = [];
        }
        if (!is_array($rows)) {
            return $picked;
        }
        foreach ($rows as $r) {
            if (!is_array($r)) {
                continue;
            }
            $sid = (int)($r['scydgy_id'] ?? 0);
            $am = trim((string)($r['amount'] ?? ''));
            if ($sid <= 0 || $am === '' || $am === '0' || $am === '0.00') {
                continue;
            }
            if (!isset($picked[$sid])) {
                $picked[$sid] = 0.0;
            }
            $picked[$sid] += (float)$am;
        }

        return $picked;
    }

    /**
     * @param int[] $scydgyIds
     * @return array<int, float>
     */
    protected function loadPickedAmountSumByScydgyIds(array $scydgyIds): array
    {
        $out = [];
        $scydgyIds = array_values(array_unique(array_filter(array_map('intval', $scydgyIds))));
        if ($scydgyIds === []) {
            return $out;
        }
        try {
            $rows = Db::table('purchase_order_detail')
                ->where('scydgy_id', 'in', $scydgyIds)
                ->whereIn('status', ProcuremenStatus::podPickedValues())
                ->field('scydgy_id,amount')
                ->select();
        } catch (\Throwable $e) {
            $rows = [];
        }
        if (!is_array($rows)) {
            return $out;
        }
        foreach ($rows as $r) {
            if (!is_array($r)) {
                continue;
            }
            $sid = (int)($r['scydgy_id'] ?? 0);
            $am = trim((string)($r['amount'] ?? ''));
            if ($sid <= 0 || $am === '' || $am === '0' || $am === '0.00') {
                continue;
            }
            if (!isset($out[$sid])) {
                $out[$sid] = 0.0;
            }
            $out[$sid] += (float)$am;
        }

        return $out;
    }

    /**
     * 归属月份：优先采购确认/完结操作时间，其次 updatetime，再 createtime
     *
     * @param array<string, mixed> $row
     * @param array<int, int>      $completeTsMap scydgy_id => unix 完结时间
     */
    protected function resolvePurchaseOrderRowYm(array $row, array $completeTsMap = []): string
    {
        $ts = $this->resolvePurchaseOrderCompleteTimestamp($row, $completeTsMap);
        if ($ts > 0) {
            return date('Y-m', $ts);
        }

        return '';
    }

    /**
     * @param array<string, mixed> $row
     * @param array<int, int>    $completeTsMap
     */
    protected function resolvePurchaseOrderCompleteTimestamp(array $row, array $completeTsMap = []): int
    {
        $sid = (int)($row['scydgy_id'] ?? 0);
        if ($sid > 0 && isset($completeTsMap[$sid]) && (int)$completeTsMap[$sid] > 946684800) {
            return (int)$completeTsMap[$sid];
        }
        foreach (['updatetime', 'createtime', 'dStamp'] as $key) {
            $raw = $row[$key] ?? null;
            if ($raw === null || $raw === '') {
                continue;
            }
            if (is_numeric($raw) && (int)$raw > 946684800) {
                return (int)$raw;
            }
            $t = trim((string)$raw);
            if ($t !== '' && stripos($t, '0000-00-00') !== 0) {
                $parsed = (int)strtotime($t);

                return $parsed > 946684800 ? $parsed : 0;
            }
        }

        return 0;
    }

    /**
     * @param array<int, string> $actions
     * @return array<int, int> scydgy_id => 最近操作 unix 时间
     */
    protected function loadOperLogTimestampMap(array $scydgyIds, array $actions): array
    {
        $out = [];
        $scydgyIds = array_values(array_unique(array_filter(array_map('intval', $scydgyIds))));
        $actions = array_values(array_filter(array_map('strval', $actions)));
        if ($scydgyIds === [] || $actions === []) {
            return $out;
        }
        try {
            $logs = Db::table('purchase_order_oper_log')
                ->where('scydgy_id', 'in', $scydgyIds)
                ->where('action', 'in', $actions)
                ->field('scydgy_id,createtime')
                ->order('id', 'asc')
                ->select();
        } catch (\Throwable $e) {
            $logs = [];
        }
        if (!is_array($logs)) {
            return $out;
        }
        foreach ($logs as $log) {
            if (!is_array($log)) {
                continue;
            }
            $sid = (int)($log['scydgy_id'] ?? 0);
            $ct = (int)($log['createtime'] ?? 0);
            if ($sid > 0 && $ct > 946684800) {
                $out[$sid] = isset($out[$sid]) ? max($out[$sid], $ct) : $ct;
            }
        }

        return $out;
    }

    /**
     * @param array<string, mixed> $row
     * @param array<int, int>    $completeTsMap
     */
    protected function formatPurchaseOrderDoneTime(array $row, array $completeTsMap = []): string
    {
        $ts = $this->resolvePurchaseOrderCompleteTimestamp($row, $completeTsMap);
        if ($ts > 0) {
            return date('Y-m-d H:i:s', $ts);
        }

        return '';
    }

    /**
     * @param array<int, array<string, mixed>> $rows
     * @return array<int, array<string, mixed>>
     */
    protected function collapseExportRowsByOrder(array $rows): array
    {
        if ($rows === []) {
            return [];
        }
        $groups = [];
        foreach ($rows as $row) {
            if (!is_array($row)) {
                continue;
            }
            $dh = trim((string)($row['CCYDH'] ?? ''));
            $key = $dh !== '' ? $dh : ('_id_' . (int)($row['id'] ?? 0));
            if (!isset($groups[$key])) {
                $groups[$key] = [];
            }
            $groups[$key][] = $row;
        }
        $out = [];
        foreach ($groups as $groupRows) {
            if ($groupRows === []) {
                continue;
            }
            usort($groupRows, function ($a, $b) {
                return ((int)($a['id'] ?? 0)) <=> ((int)($b['id'] ?? 0));
            });
            $head = $groupRows[0];
            $gymcList = [];
            foreach ($groupRows as $r) {
                $g = trim((string)($r['CGYMC'] ?? ''));
                if ($g !== '' && !in_array($g, $gymcList, true)) {
                    $gymcList[] = $g;
                }
            }
            $merged = $head;
            $merged['_merge_rows'] = $groupRows;
            if (count($groupRows) > 1) {
                $merged['CGYMC'] = implode('、', $gymcList);
            }
            $latestText = '';
            $latestTs = 0;
            foreach ($groupRows as $r) {
                $t = trim((string)($r['createtime_text'] ?? ''));
                $ts = $t !== '' ? (int)strtotime($t) : 0;
                if ($ts > $latestTs) {
                    $latestTs = $ts;
                    $latestText = $t;
                }
            }
            if ($latestText !== '') {
                $merged['createtime_text'] = $latestText;
            }
            $maxId = (int)($head['id'] ?? 0);
            foreach ($groupRows as $r) {
                $maxId = max($maxId, (int)($r['id'] ?? 0));
            }
            $merged['id'] = $maxId;
            $out[] = $merged;
        }
        usort($out, function ($a, $b) {
            return ((int)($b['id'] ?? 0)) <=> ((int)($a['id'] ?? 0));
        });

        return $out;
    }
}