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

<?php

namespace app\admin\controller;

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

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

    public function _initialize()
    {
        parent::_initialize();
        $canExport = $this->hasProcuremenSiblingPerm(['export_month_outward', 'exportMonthOutward']);
        $canExportQuote = $this->hasProcuremenSiblingPerm(['export_month_quote', 'exportMonthQuote', 'export_month_outward', 'exportMonthOutward']);
        $canDetails = $this->hasProcuremenSiblingPerm(['details']);
        $this->view->assign('canExportMonthOutward', $canExport);
        $this->view->assign('canExportMonthQuote', $canExportQuote);
        $this->assignconfig('procuremenAuth', [
            'details'              => $canDetails,
            'export_month_outward' => $canExport,
            'export_month_quote'   => $canExportQuote,
        ]);
    }

    /**
     * 月度导出页引用的采购子权限（规则名在 procuremen 控制器下）
     *
     * @param string[] $actions
     */
    protected function hasProcuremenSiblingPerm(array $actions): bool
    {
        foreach ($actions as $action) {
            $action = strtolower(trim((string)$action));
            if ($action === '') {
                continue;
            }
            if (strpos($action, 'procuremen/') !== 0) {
                $action = 'procuremen/' . $action;
            }
            if ($this->auth->check($action)) {
                return true;
            }
        }
        $suffixes = [];
        foreach ($actions as $action) {
            $action = strtolower(trim((string)$action));
            $action = preg_replace('#^procuremen/#', '', $action);
            if ($action !== '') {
                $suffixes[$action] = true;
            }
        }
        if ($suffixes === []) {
            return false;
        }
        try {
            foreach ($this->auth->getRuleList() as $rule) {
                $rule = strtolower((string)$rule);
                if (strpos($rule, 'procuremen/') !== 0) {
                    continue;
                }
                foreach (array_keys($suffixes) as $suffix) {
                    if ($rule === 'procuremen/' . $suffix || strpos($rule, 'procuremen/' . $suffix . '/') === 0) {
                        return true;
                    }
                }
            }
        } catch (\Throwable $e) {
        }

        return false;
    }

    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 {
                // 有搜索条件时跨月份查询（用户常不知完结月份）
                $hasSearch = $this->exportListHasActiveSearch();
                $rows = $this->buildExportableOrderRows($hasSearch ? '' : $ym);
                $rows = $this->filterExportableRowsBySearch($rows);

                $total = count($rows);
                $offset = max(0, (int)$this->request->get('offset', 0));
                $limit = (int)$this->request->get('limit', 0);
                if ($limit > 0) {
                    $rows = array_slice($rows, $offset, $limit);
                }

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

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

    /**
     * 列表是否带有有效搜索（快速搜索或通用搜索）
     */
    protected function exportListHasActiveSearch(): bool
    {
        $keyword = trim((string)$this->request->request('search', ''));
        if ($keyword === '') {
            $keyword = trim((string)$this->request->get('search', ''));
        }
        if ($keyword !== '') {
            return true;
        }

        $filter = $this->request->get('filter', '');
        if (is_string($filter) && $filter !== '') {
            $filterArr = (array)json_decode($filter, true);
        } else {
            $filterArr = is_array($filter) ? $filter : [];
        }
        foreach ($filterArr as $val) {
            if (trim((string)$val) !== '') {
                return true;
            }
        }

        return false;
    }

    /**
     * 已审批通过、可按月份导出的订单（合并为一单一行）
     */
    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()]);
        }
    }

    /**
     * 快速搜索 / 通用搜索过滤（本页数据已按月聚合，在内存中筛选）
     *
     * @param array<int, array<string, mixed>> $rows
     * @return array<int, array<string, mixed>>
     */
    protected function filterExportableRowsBySearch(array $rows): array
    {
        $keyword = trim((string)$this->request->request('search', ''));
        if ($keyword === '') {
            $keyword = trim((string)$this->request->get('search', ''));
        }

        $filter = $this->request->get('filter', '');
        $op = $this->request->get('op', '');
        if (is_string($filter) && $filter !== '') {
            $filterArr = (array)json_decode($filter, true);
        } else {
            $filterArr = is_array($filter) ? $filter : [];
        }
        if (is_string($op) && $op !== '') {
            $opArr = (array)json_decode($op, true);
        } else {
            $opArr = is_array($op) ? $op : [];
        }

        if ($keyword === '' && $filterArr === []) {
            return $rows;
        }

        $searchFields = ['CCYDH', 'order_batch_no', 'CYJMC', 'CGYMC', 'picked_supplier', 'createtime_text', 'ym'];
        $out = [];
        foreach ($rows as $row) {
            if (!is_array($row)) {
                continue;
            }
            if ($keyword !== '') {
                $hit = false;
                foreach ($searchFields as $f) {
                    if (mb_stripos((string)($row[$f] ?? ''), $keyword) !== false) {
                        $hit = true;
                        break;
                    }
                }
                if (!$hit) {
                    continue;
                }
            }
            $passFilter = true;
            foreach ($filterArr as $field => $val) {
                $field = (string)$field;
                $val = trim((string)$val);
                if ($field === '' || $val === '') {
                    continue;
                }
                $cell = (string)($row[$field] ?? '');
                $mode = strtoupper((string)($opArr[$field] ?? 'LIKE'));
                if ($mode === '=' || $mode === 'EQUAL') {
                    if (strcasecmp($cell, $val) !== 0) {
                        $passFilter = false;
                        break;
                    }
                } elseif (mb_stripos($cell, $val) === false) {
                    $passFilter = false;
                    break;
                }
            }
            if (!$passFilter) {
                continue;
            }
            $out[] = $row;
        }

        return $out;
    }

    /**
     * 审批通过订单，按审批月份筛选后合并为一单一行（不要求已完结）
     *
     * @param string $ym 审批月份 Y-m；空字符串表示不限月份（用于跨月搜索）
     * @return array<int, array<string, mixed>>
     */
    protected function buildExportableOrderRows(string $ym): array
    {
        $approvedVals = ProcuremenStatus::wflowApprovedValues();
        $softDel = '(mod_rq IS NULL OR TRIM(CAST(mod_rq AS CHAR(32))) = \'\' OR TRIM(CAST(mod_rq AS CHAR(32))) LIKE \'0000-00-00%\')';
        try {
            $dbRows = Db::table('purchase_order')
                ->where('wflow_status', 'in', $approvedVals)
                ->whereRaw($softDel)
                ->field('id,scydgy_id,CCYDH,CYJMC,CGYMC,pick_time,createtime,dStamp,pick_company_name,wflow_status,status,order_batch_no')
                ->order('id', 'desc')
                ->select();
        } catch (\Throwable $e) {
            try {
                $dbRows = Db::table('purchase_order')
                    ->where('wflow_status', 'in', $approvedVals)
                    ->whereRaw($softDel)
                    ->field('id,scydgy_id,CCYDH,CYJMC,CGYMC,pick_time,createtime,dStamp,pick_company_name,wflow_status,status')
                    ->order('id', 'desc')
                    ->select();
            } catch (\Throwable $e2) {
                \think\Log::write('月度导出列表查询异常：' . $e2->getMessage(), 'error');
                $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;
            }
        }
        // 审核确认 / 完结时间都可用于归属月份与展示
        $completeTsMap = ProcuremenTime::loadOperLogTimestampMap(
            array_keys($sidList),
            ['purchase_confirm', 'mark_complete']
        );

        $pool = [];
        foreach ($dbRows as $r) {
            if (!is_array($r)) {
                continue;
            }
            if (!ProcuremenStatus::isWflowApproved($r['wflow_status'] ?? '')) {
                continue;
            }
            $done = ProcuremenTime::resolveCompletedDone($r, $completeTsMap);
            if ($done['ts'] <= 0) {
                // 审批通过但缺时间戳：仍展示；有筛月时挂到查询月
                $rowYm = $ym !== '' ? $ym : '';
                $timeText = '';
            } else {
                $rowYm = date('Y-m', $done['ts']);
                $timeText = $done['text'];
            }
            if ($ym !== '' && $rowYm !== $ym) {
                continue;
            }
            $sid = (int)($r['scydgy_id'] ?? 0);
            // 已选供应商以采购单中标字段为准（一家），不拼明细里多条「已选中」
            $pickedName = trim((string)($r['pick_company_name'] ?? ''));
            $pool[] = [
                'id'                 => (int)($r['id'] ?? 0),
                'scydgy_id'          => $sid,
                'CCYDH'              => trim((string)($r['CCYDH'] ?? '')),
                'order_batch_no'     => trim((string)($r['order_batch_no'] ?? '')),
                'CYJMC'              => trim((string)($r['CYJMC'] ?? '')),
                'CGYMC'              => trim((string)($r['CGYMC'] ?? '')),
                'ym'                 => $rowYm,
                'createtime_text'    => $timeText,
                'picked_supplier'    => $pickedName,
            ];
        }

        $merged = $this->collapseExportRowsByOrder($pool);
        foreach ($merged as &$row) {
            $picked = '';
            $batchNo = trim((string)($row['order_batch_no'] ?? ''));
            if (!empty($row['_merge_rows']) && is_array($row['_merge_rows'])) {
                foreach ($row['_merge_rows'] as $mr) {
                    if (!is_array($mr)) {
                        continue;
                    }
                    if ($picked === '') {
                        $n = trim((string)($mr['picked_supplier'] ?? ''));
                        if ($n !== '') {
                            $picked = $n;
                        }
                    }
                    if ($batchNo === '') {
                        $bn = trim((string)($mr['order_batch_no'] ?? ''));
                        if ($bn !== '') {
                            $batchNo = $bn;
                        }
                    }
                    if (trim((string)($row['ym'] ?? '')) === '') {
                        $ymv = trim((string)($mr['ym'] ?? ''));
                        if ($ymv !== '') {
                            $row['ym'] = $ymv;
                        }
                    }
                }
            } else {
                $picked = trim((string)($row['picked_supplier'] ?? ''));
            }
            $row['picked_supplier'] = $picked;
            $row['order_batch_no'] = $batchNo;
            unset($row['_merge_rows']);
        }
        unset($row);

        return $merged;
    }

    /**
     * 中标/已选供应商名称（按工序 scydgy_id）
     *
     * @param int[] $scydgyIds
     * @return array<int, string>
     */
    protected function loadPickedSupplierNamesByScydgyIds(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,company_name')
                ->select();
        } catch (\Throwable $e) {
            $rows = [];
        }
        if (!is_array($rows)) {
            return $out;
        }
        $namesBySid = [];
        foreach ($rows as $r) {
            if (!is_array($r)) {
                continue;
            }
            $sid = (int)($r['scydgy_id'] ?? 0);
            $cn = trim((string)($r['company_name'] ?? ''));
            if ($sid <= 0 || $cn === '') {
                continue;
            }
            if (!isset($namesBySid[$sid])) {
                $namesBySid[$sid] = [];
            }
            if (!in_array($cn, $namesBySid[$sid], true)) {
                $namesBySid[$sid][] = $cn;
            }
        }
        foreach ($namesBySid as $sid => $names) {
            $out[$sid] = implode('、', $names);
        }

        return $out;
    }

    /**
     * 工序行金额合计：优先已选定明细 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;
    }

    /**
     * 归属月份：优先采购确认/完结操作时间，其次 pick_time、createtime、dStamp
     *
     * @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 (['pick_time', '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(ProcuremenOperLog::COL_SCYDGY_ID, 'in', $scydgyIds)
                ->where(ProcuremenOperLog::COL_ACTION, 'in', ProcuremenOperLog::expandActionQueryValues($actions))
                ->field(ProcuremenOperLog::COL_SCYDGY_ID . ',' . ProcuremenOperLog::COL_TIME)
                ->order('id', 'asc')
                ->select();
        } catch (\Throwable $e) {
            $logs = [];
        }
        if (!is_array($logs)) {
            return $out;
        }
        foreach ($logs as $log) {
            if (!is_array($log)) {
                continue;
            }
            $log = ProcuremenOperLog::normalizeRow($log);
            $sid = (int)($log['scydgy_id'] ?? 0);
            $ct = ProcuremenTime::parseToTimestamp($log['createtime'] ?? null);
            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 = ProcuremenTime::parseToTimestamp($t);
                if ($ts > $latestTs) {
                    $latestTs = $ts;
                    $latestText = ProcuremenTime::formatDisplayDateTime($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;
    }
}