mes-dc-server-api/application/api/controller/Synchronization.php

<?php

namespace app\api\controller;

use app\common\controller\Api;
use think\Db;
use think\exception\HttpResponseException;

/**
 * MES-ERP 工单同步
 */
class Synchronization extends Api
{
    protected $noNeedLogin = ['*'];
    protected $noNeedRight = ['*'];

    /** @var int 同步状态：待同步 */
    const SYNC_STATUS_PENDING = 0;
    /** @var int 同步状态：成功 */
    const SYNC_STATUS_SUCCESS = 1;
    /** @var int 同步状态：失败 */
    const SYNC_STATUS_FAILED = 2;

    /** @var int 操作状态：成功 */
    const OPERATE_STATUS_SUCCESS = 1;
    /** @var int 操作状态：失败 */
    const OPERATE_STATUS_FAILED = 2;
    /** @var int 操作状态：处理中 */
    const OPERATE_STATUS_PROCESSING = 3;

    /**
     * ERP SQL Server 表映射（请按实际 ERP 表结构填写 table 与 fields）
     * fields：MES 源字段名 => ERP 目标字段名；固定值以 = 开头，如 '=M' => 'is_mfg_wrk'
     * 报工 sfb_id 规则：K+年月日(同步日期)+四位自增，如 K2606100201
     */
    protected $erpTableMap = [
        // 1-工单数据：来源 工单_基本资料
        'work_order' => [
            'table' => 'mps_mfg_ord',
            'fields' => [
                '订单编号' => 'ordno',
                '=M' => 'is_mfg_wrk',
                '生产款号' => 'itemno',
                '接单日期' => 'cr_date',
                '落货日期' => 'rel_date',
                '同步日期' => 'due_date',
                '订单数量' => 'qty_ord',
                'remark' => 'remark',
                '计划制造工分' => 'curprc',
                '=0001' => 'ocode',
            ],
        ],
        // 2-工艺数据：来源 工单_基础工艺资料
        'process' => [
            'table' => 'mps_mfg_routing',
            'fields' => [
                'work_order' => 'ordno',
                '=M' => 'is_mfg_wrk',
                '生产款号' => 'itemno',
                'process_code' => 'op',
                'process_name' => 'routiname',
                'standard_score' => 'preprc',
                '=0001' => 'ocode',
                '订单数量' => 'qty_ord',
            ],
        ],
        // 3-报工数据：来源工序产量核查汇总（sfb_id 按同步日期自动生成）
        'report' => [
            'table' => 'wfc_shift_feedback',
            'fields' => [
                '员工编号' => 'emp_no',
                '=1' => 'shift_id',
                '订单编号' => 'ordno',
                '生产款号' => 'itemno',
                '工序号' => 'op',
                '工序名称' => 'routiname',
                '数量' => 'qty_comp',
                '开工日期' => 'op_begdt',
                '完工日期' => 'op_enddt',
                '填报日期' => 'tran_date',
                '=0202' => 'centerno',
                '=0001.02.02' => 'detno',
                '=0001' => 'ocode',
                '工分' => 'preprc',
            ],
        ],
    ];

    /**
     * 完工工单列表（含同步状态）
     * @ApiMethod (GET)
     * @param page 页码
     * @param limit 每页条数
     * @param search 搜索（订单编号/款号/款式/客户）
     * @param sync_status 同步状态筛选：unsynced|pending|synced|failed|cancelled
     */
    public function getCompletedWorkOrderList()
    {
        if (!$this->request->isGet()) {
            $this->error('请求方法错误');
        }
        $param = $this->request->param();
        $page = max(1, (int)(isset($param['page']) ? $param['page'] : 1));
        $limit = max(15, min(100, (int)(isset($param['limit']) ? $param['limit'] : 15)));
    
        $baseWhere = function ($query) use ($param) {
            $query->whereNull('Mod_rq')
                ->whereNotNull('工单完工日期')
                ->where('工单完工日期', '<>', '');
            if (!empty($param['search'])) {
                $query->where('订单编号|生产款号|客户编号|款式|工单完工日期', 'like', '%' . trim($param['search']) . '%');
            }
        };

        $countQuery = \db('工单_基本资料');
        $baseWhere($countQuery);
        $total = (int)$countQuery->count();

        $listQuery = \db('工单_基本资料');
        $baseWhere($listQuery);
        $list = $listQuery
            ->field('Uniqid,订单编号,生产款号,款式,客户编号,工单完工日期,remark,gd_statu,Sys_id,Sys_rq')
            ->order('工单完工日期 desc,订单编号 desc')
            ->page($page, $limit)
            ->select();
        if (!empty($list)) {
            $orderNos = array_column($list, '订单编号');
            $statusMap = $this->batchResolveDisplaySyncStatus($orderNos);
            foreach ($list as &$row) {
                $orderNo = $row['订单编号'];
                $statusInfo = isset($statusMap[$orderNo]) ? $statusMap[$orderNo] : $this->defaultDisplaySyncStatus();
                $row['sync_status'] = $statusInfo['code'];
                $row['sync_status_text'] = $statusInfo['text'];
                $row['同步状态'] = $statusInfo['text'];
                $row['last_sync_time'] = $statusInfo['sync_time'];
                $row['last_sync_msg'] = $statusInfo['sync_msg'];
                $row['last_sync_log_id'] = $statusInfo['sync_log_id'];
            }
            unset($row);

            if (!empty($param['sync_status'])) {
                $filter = $param['sync_status'];
                $list = array_values(array_filter($list, function ($row) use ($filter) {
                    return $row['sync_status'] === $filter;
                }));
            }
        }

        $this->success('成功', [
            'total' => $total,
            'page' => $page,
            'limit' => $limit,
            'list' => $list ?: [],
        ]);
    }

    /**
     * 同步到 ERP
     * @ApiMethod (POST)
     * @param work_order_no 工单编号
     * @param sync_module 1-工单 2-工艺 3-报工 4-全量
     * @param operator 操作人
     * @param operate_source backend|api|crontab，默认 backend
     * @param operate_type sync|manual|auto，默认 manual
     */
    public function syncWorkOrder()
    {
        $this->executeSync('sync');
    }

    /**
     * 重试同步（仅上次失败时可重试）
     * @ApiMethod (POST)
     */
    public function retrySyncWorkOrder()
    {
        $params = $this->request->post();
        $workOrderNo = isset($params['work_order_no']) ? trim($params['work_order_no']) : '';
        if ($workOrderNo === '') {
            $this->error('工单编号不能为空');
        }
        $statusInfo = $this->resolveDisplaySyncStatus($workOrderNo);
        if ($statusInfo['code'] !== 'failed') {
            $this->error('仅同步失败的工单可重试');
        }
        $this->executeSync('retry');
    }

    /**
     * 撤回同步
     * @ApiMethod (POST)
     * @param work_order_no 工单编号
     * @param operator 操作人
     * @param reason 撤回原因
     */
    public function cancelSyncWorkOrder()
    {
        if (!$this->request->isPost()) {
            $this->error('请求方法错误');
        }
        $params = $this->request->post();
        $workOrderNo = isset($params['work_order_no']) ? trim($params['work_order_no']) : '';
        $operator = isset($params['operator']) ? trim($params['operator']) : '';
        $reason = isset($params['reason']) ? trim($params['reason']) : '';

        if ($workOrderNo === '') {
            $this->error('工单编号不能为空');
        }
        if ($operator === '') {
            $this->error('操作人不能为空');
        }

        $workOrder = $this->fetchWorkOrderBasic($workOrderNo);
        if (empty($workOrder)) {
            $this->error('工单不存在');
        }

        $statusInfo = $this->resolveDisplaySyncStatus($workOrderNo);
        if ($statusInfo['code'] !== 'synced') {
            $this->error('仅已同步的工单可撤回');
        }

        $operTime = date('Y-m-d H:i:s');
        $operateParam = json_encode([
            'work_order_no' => $workOrderNo,
            'reason' => $reason,
        ], JSON_UNESCAPED_UNICODE);
        $operateContent = '撤回同步工单' . $workOrderNo . ($reason !== '' ? '，原因：' . $reason : '');

        $operationLogId = $this->insertOperationLog([
            'operate_type' => 'cancel',
            'operate_user' => $operator,
            'operate_user_name' => isset($params['operate_user_name']) ? $params['operate_user_name'] : $operator,
            'operate_time' => $operTime,
            'sync_log_id' => $statusInfo['sync_log_id'],
            'work_order_no' => $workOrderNo,
            'style_no' => $workOrder['生产款号'],
            'operate_content' => $operateContent,
            'operate_param' => $operateParam,
            'operate_status' => self::OPERATE_STATUS_PROCESSING,
            'operate_source' => isset($params['operate_source']) ? $params['operate_source'] : 'backend',
            'operate_ip' => $this->request->ip(),
            'server_ip' => isset($_SERVER['SERVER_ADDR']) ? $_SERVER['SERVER_ADDR'] : '',
        ]);

        Db::startTrans();
        try {
            $this->deleteErpDataByWorkOrder($workOrderNo);

            Db::name('mes_erp_operation_log')
                ->where('id', $operationLogId)
                ->update([
                    'operate_status' => self::OPERATE_STATUS_SUCCESS,
                ]);

            if (!empty($statusInfo['sync_log_id'])) {
                Db::name('mes_erp_work_order_sync_log')
                    ->where('id', $statusInfo['sync_log_id'])
                    ->update([
                        'sync_msg' => '已撤回' . ($reason !== '' ? '：' . $reason : ''),
                        'update_time' => $operTime,
                    ]);
            }

            Db::commit();
        } catch (HttpResponseException $e) {
            throw $e;
        } catch (\Exception $e) {
            Db::rollback();
            Db::name('mes_erp_operation_log')
                ->where('id', $operationLogId)
                ->update([
                    'operate_status' => self::OPERATE_STATUS_FAILED,
                    'error_msg' => $e->getMessage(),
                ]);
            $this->error('撤回失败：' . $e->getMessage());
        }

        $this->success('撤回成功');
    }

    /**
     * 同步日志列表
     * @ApiMethod (GET)
     * @param work_order_no 工单编号
     */
    public function getSyncLogList()
    {
        if (!$this->request->isGet()) {
            $this->error('请求方法错误');
        }
        $workOrderNo = trim($this->request->param('work_order_no', ''));
        if ($workOrderNo === '') {
            $this->error('工单编号不能为空');
        }

        $list = Db::name('mes_erp_work_order_sync_log')
            ->where('work_order_no', $workOrderNo)
            ->order('id desc')
            ->select();

        foreach ($list as &$row) {
            $row['sync_status_text'] = $this->syncStatusText($row['sync_status']);
            $row['sync_module_text'] = $this->syncModuleText($row['sync_module']);
        }
        unset($row);

        $this->success('成功', $list ?: []);
    }

    /**
     * 操作过程记录列表
     * @ApiMethod (GET)
     * @param work_order_no 工单编号
     * @param sync_log_id 同步日志ID（可选）
     */
    public function getOperationLogList()
    {
        if (!$this->request->isGet()) {
            $this->error('请求方法错误');
        }
        $workOrderNo = trim($this->request->param('work_order_no', ''));
        $syncLogId = $this->request->param('sync_log_id');

        $query = Db::name('mes_erp_operation_log')->order('id desc');
        if ($workOrderNo !== '') {
            $query->where('work_order_no', $workOrderNo);
        }
        if ($syncLogId !== null && $syncLogId !== '') {
            $query->where('sync_log_id', intval($syncLogId));
        }
        if ($workOrderNo === '' && ($syncLogId === null || $syncLogId === '')) {
            $this->error('请传入 work_order_no 或 sync_log_id');
        }

        $list = $query->select();
        foreach ($list as &$row) {
            $row['operate_status_text'] = $this->operateStatusText($row['operate_status']);
        }
        unset($row);

        $this->success('成功', $list ?: []);
    }

    /**
     * 执行同步核心逻辑
     * @param string $operateType sync|retry|manual|auto
     */
    private function executeSync($operateType)
    {
        if (!$this->request->isPost()) {
            $this->error('请求方法错误');
        }
        $params = $this->request->post();
        $workOrderNo = isset($params['work_order_no']) ? trim($params['work_order_no']) : '';
        $syncModule = isset($params['sync_module']) ? (string)$params['sync_module'] : '4';
        $operator = isset($params['operator']) ? trim($params['operator']) : '';

        if ($workOrderNo === '') {
            $this->error('工单编号不能为空');
        }
        if ($operator === '') {
            $this->error('操作人不能为空');
        }
        if (!in_array($syncModule, ['1', '2', '3', '4'], true)) {
            $this->error('同步模块参数错误');
        }

        $workOrder = $this->fetchWorkOrderBasic($workOrderNo);
        if (empty($workOrder)) {
            $this->error('工单不存在');
        }
        if (empty($workOrder['工单完工日期'])) {
            $this->error('该工单尚未完工，不能同步');
        }

        $operTime = date('Y-m-d H:i:s');
        $operateSource = isset($params['operate_source']) ? $params['operate_source'] : 'backend';
        $requestOperateType = isset($params['operate_type']) ? $params['operate_type'] : 'manual';
        if ($operateType === 'retry') {
            $requestOperateType = 'retry';
        }

        $operateParam = json_encode([
            'work_order_no' => $workOrderNo,
            'sync_module' => $syncModule,
            'operator' => $operator,
        ], JSON_UNESCAPED_UNICODE);

        $operationLogId = $this->insertOperationLog([
            'operate_type' => $requestOperateType,
            'operate_user' => $operator,
            'operate_user_name' => isset($params['operate_user_name']) ? $params['operate_user_name'] : $operator,
            'operate_time' => $operTime,
            'sync_log_id' => null,
            'work_order_no' => $workOrderNo,
            'style_no' => $workOrder['生产款号'],
            'operate_content' => $this->buildSyncOperateContent($workOrderNo, $syncModule, $requestOperateType),
            'operate_param' => $operateParam,
            'operate_status' => self::OPERATE_STATUS_PROCESSING,
            'operate_source' => $operateSource,
            'operate_ip' => $this->request->ip(),
            'server_ip' => isset($_SERVER['SERVER_ADDR']) ? $_SERVER['SERVER_ADDR'] : '',
        ]);

        $seqRange = $this->fetchReportSeqRange($workOrderNo);
        $syncLogId = $this->insertSyncLog([
            'work_order_no' => $workOrderNo,
            'style_no' => $workOrder['生产款号'],
            'complete_date' => $this->formatDateValue($workOrder['工单完工日期']),
            'operator' => $operator,
            'sync_time' => $operTime,
            'report_start_seq' => $seqRange['start'],
            'report_end_seq' => $seqRange['end'],
            'sync_status' => self::SYNC_STATUS_PENDING,
            'sync_module' => $syncModule,
            'sync_msg' => '同步处理中',
            'erp_sync_id' => '',
        ]);

        Db::name('mes_erp_operation_log')
            ->where('id', $operationLogId)
            ->update(['sync_log_id' => $syncLogId]);

        try {
            $erpSyncId = $this->pushDataToErp($workOrderNo, $syncModule, $workOrder);
            Db::name('mes_erp_work_order_sync_log')
                ->where('id', $syncLogId)
                ->update([
                    'sync_status' => self::SYNC_STATUS_SUCCESS,
                    'sync_msg' => '同步成功',
                    'erp_sync_id' => $erpSyncId,
                    'update_time' => date('Y-m-d H:i:s'),
                ]);
            Db::name('mes_erp_operation_log')
                ->where('id', $operationLogId)
                ->update(['operate_status' => self::OPERATE_STATUS_SUCCESS]);
        } catch (HttpResponseException $e) {
            throw $e;
        } catch (\Exception $e) {
            Db::name('mes_erp_work_order_sync_log')
                ->where('id', $syncLogId)
                ->update([
                    'sync_status' => self::SYNC_STATUS_FAILED,
                    'sync_msg' => $e->getMessage(),
                    'update_time' => date('Y-m-d H:i:s'),
                ]);
            Db::name('mes_erp_operation_log')
                ->where('id', $operationLogId)
                ->update([
                    'operate_status' => self::OPERATE_STATUS_FAILED,
                    'error_msg' => $e->getMessage(),
                ]);
            $this->error('同步失败：' . $e->getMessage());
        }

        $this->success('同步成功', [
            'sync_log_id' => $syncLogId,
            'operation_log_id' => $operationLogId,
        ]);
    }

    /**
     * 推送数据到 ERP（db2 / SQL Server）
     * @param string $workOrderNo
     * @param string $syncModule
     * @param array $workOrder
     * @return string ERP 同步标识
     * @throws \Exception
     */
    private function pushDataToErp($workOrderNo, $syncModule, $workOrder)
    {
        $erpDb = $this->getErpDb();
        $modules = $syncModule === '4' ? ['1', '2', '3'] : [$syncModule];
        $inserted = [];
        $syncDate = date('Y-m-d');
        $workOrder['同步日期'] = $syncDate;

        $erpDb->startTrans();
        try {
            foreach ($modules as $module) {
                if ($module === '1') {
                    $rows = [$workOrder];
                    $count = $this->insertErpRows($erpDb, 'work_order', $rows);
                    $inserted['work_order'] = $count;
                } elseif ($module === '2') {
                    $rows = $this->fetchWorkOrderProcessList($workOrderNo);
                    if (empty($rows)) {
                        throw new \Exception('工单工艺数据为空');
                    }
                    foreach ($rows as &$processRow) {
                        $processRow['生产款号'] = $workOrder['生产款号'];
                        $processRow['订单数量'] = $workOrder['订单数量'];
                    }
                    unset($processRow);
                    $count = $this->insertErpRows($erpDb, 'process', $rows);
                    $inserted['process'] = $count;
                } elseif ($module === '3') {
                    $rows = $this->fetchProductionCheckRows($workOrderNo, $syncDate);
                    if (empty($rows)) {
                        throw new \Exception('报工数据为空，请先完成报工');
                    }
                    $count = $this->insertErpRows($erpDb, 'report', $rows, $syncDate);
                    $inserted['report'] = $count;
                }
            }
            $erpDb->commit();
        } catch (\Exception $e) {
            $erpDb->rollback();
            throw $e;
        }

        return $workOrderNo . '_' . $syncModule . '_' . time();
    }

    /**
     * 写入 ERP 表
     */
    private function insertErpRows($erpDb, $tableKey, array $sourceRows, $syncDate = null)
    {
        $config = $this->erpTableMap[$tableKey];
        if (empty($config['table'])) {
            throw new \Exception('请先在 Synchronization.php 中配置 ERP [' . $tableKey . '] 表名');
        }

        $sfbSeq = 0;
        $sfbPrefix = '';
        if ($tableKey === 'report') {
            $syncDate = $syncDate ?: date('Y-m-d');
            $sfbPrefix = 'K' . date('ymd', strtotime($syncDate));
            $sfbSeq = $this->fetchMaxSfbSeq($erpDb, $sfbPrefix);
        }

        $erpRows = [];
        foreach ($sourceRows as $sourceRow) {
            $mapped = $this->mapSourceRowToErp($sourceRow, $config['fields']);
            if ($tableKey === 'report') {
                $sfbSeq++;
                $mapped['sfb_id'] = $sfbPrefix . str_pad((string)$sfbSeq, 4, '0', STR_PAD_LEFT);
            }
            if (!empty($mapped)) {
                $erpRows[] = $mapped;
            }
        }
        if (empty($erpRows)) {
            throw new \Exception('ERP [' . $tableKey . '] 字段映射未配置或映射结果为空');
        }

        $result = $erpDb->name($config['table'])->insertAll($erpRows);
        if ($result === false || $result === 0) {
            throw new \Exception('写入 ERP [' . $tableKey . '] 失败');
        }
        return (int)$result;
    }

    /**
     * 查询 ERP 报工表当日 sfb_id 最大序号（K+年月日+四位自增）
     * 须对末四位做数值 MAX，字符串 MAX 在序号≥10 时会算错导致主键冲突
     */
    private function fetchMaxSfbSeq($erpDb, $sfbPrefix)
    {
        $row = $erpDb->query(
            'SELECT MAX(CAST(RIGHT(sfb_id, 4) AS INT)) AS max_seq FROM wfc_shift_feedback WHERE sfb_id LIKE ?',
            [$sfbPrefix . '%']
        );
        if (empty($row) || !isset($row[0]['max_seq']) || $row[0]['max_seq'] === null) {
            return 0;
        }
        return (int)$row[0]['max_seq'];
    }

    /**
     * 撤回时删除 ERP 数据（需配置表名及工单编号字段）
     */
    private function deleteErpDataByWorkOrder($workOrderNo)
    {
        $erpDb = $this->getErpDb();
        foreach (['work_order', 'process', 'report'] as $tableKey) {
            $config = $this->erpTableMap[$tableKey];
            if (empty($config['table'])) {
                continue;
            }
            $workOrderField = $this->resolveErpWorkOrderField($tableKey, $config['fields']);
            if ($workOrderField === '') {
                continue;
            }
            $erpDb->name($config['table'])->where($workOrderField, $workOrderNo)->delete();
        }
    }

    /**
     * MES 行映射为 ERP 行
     */
    private function mapSourceRowToErp(array $sourceRow, array $fieldMap)
    {
        $erpRow = [];
        foreach ($fieldMap as $mesField => $erpField) {
            if ($erpField === '' || $erpField === null) {
                continue;
            }
            if ($mesField !== '' && $mesField[0] === '=') {
                $erpRow[$erpField] = substr($mesField, 1);
                continue;
            }
            if (array_key_exists($mesField, $sourceRow)) {
                $erpRow[$erpField] = $sourceRow[$mesField];
            }
        }
        return $erpRow;
    }

    private function resolveErpWorkOrderField($tableKey, array $fieldMap)
    {
        $candidates = ['订单编号', 'work_order', 'work_order_no'];
        if ($tableKey === 'work_order') {
            $candidates = ['订单编号', 'work_order_no'];
        }
        foreach ($candidates as $mesField) {
            if (isset($fieldMap[$mesField]) && $fieldMap[$mesField] !== '') {
                return $fieldMap[$mesField];
            }
        }
        return '';
    }

    /**
     * 工序产量核查数据（与 WorkOrderProcess::checkProcessProduction 一致，扁平化为报工行）
     */
    private function fetchProductionCheckRows($workOrderNo, $syncDate = null)
    {
        $syncDate = $syncDate ?: date('Y-m-d');
        $list = Db::name('设备_工分计酬')->alias('a')
            ->join('工单_基本资料 b', 'a.work_order = b.订单编号 AND b.Mod_rq IS NULL', 'LEFT')
            ->where('a.work_order', $workOrderNo)
            ->whereNull('a.del_rq')
            ->field('b.订单编号,b.生产款号,b.款式,a.process_code as 工序号,a.process_name as 工序名称,a.staff_no as 员工编号,a.staff_name as 员工姓名,
                SUM(a.number) as 数量,MIN(a.date) as 开工日期,MAX(a.date) as 完工日期,
                SUM(a.standard_score) as 工分,SUM(a.production_score) as 工时,SUM(a.salary) as 工资')
            ->group('b.订单编号,b.生产款号,b.款式,a.process_code,a.process_name,a.staff_no,a.staff_name')
            ->order('a.process_code asc,a.staff_no asc')
            ->select();

        if (empty($list)) {
            return [];
        }

        $rows = [];
        foreach ($list as $row) {
            $rows[] = [
                '订单编号' => $row['订单编号'],
                '生产款号' => $row['生产款号'],
                '款式' => $row['款式'],
                '工序号' => $row['工序号'],
                '工序名称' => $row['工序名称'],
                '员工编号' => $row['员工编号'],
                '员工姓名' => $row['员工姓名'],
                '数量' => floatval($row['数量']),
                '开工日期' => !empty($row['开工日期']) ? date('Y-m-d', strtotime($row['开工日期'])) : '',
                '完工日期' => !empty($row['完工日期']) ? date('Y-m-d', strtotime($row['完工日期'])) : '',
                '填报日期' => $syncDate,
                '工分' => round(floatval($row['工分']), 4),
                '工时' => round(floatval($row['工时']), 4),
                '工资' => round(floatval($row['工资']), 4),
            ];
        }
        return $rows;
    }

    private function fetchWorkOrderBasic($workOrderNo)
    {
        return Db::table('工单_基本资料')
            ->where('订单编号', $workOrderNo)
            ->whereNull('Mod_rq')
            ->find();
    }

    private function fetchWorkOrderProcessList($workOrderNo)
    {
        return Db::table('工单_基础工艺资料')
            ->where('work_order', $workOrderNo)
            ->whereNull('del_rq')
            ->field('work_order,part_code,part_name,process_code,process_name,big_process,
                standard_hour,standard_minutes,standard_score,money,coefficient,remark')
            ->order('process_code')
            ->select();
    }

    private function fetchReportSeqRange($workOrderNo)
    {
        $range = Db::table('设备_工分计酬')
            ->where('work_order', $workOrderNo)
            ->whereNull('del_rq')
            ->field('MIN(id) as min_id,MAX(id) as max_id')
            ->find();
        return [
            'start' => !empty($range['min_id']) ? (string)$range['min_id'] : '',
            'end' => !empty($range['max_id']) ? (string)$range['max_id'] : '',
        ];
    }

    private function insertSyncLog(array $data)
    {
        $data['create_time'] = date('Y-m-d H:i:s');
        $data['update_time'] = $data['create_time'];
        return (int)Db::name('mes_erp_work_order_sync_log')->insertGetId($data);
    }

    private function insertOperationLog(array $data)
    {
        $data['create_time'] = date('Y-m-d H:i:s');
        return (int)Db::name('mes_erp_operation_log')->insertGetId($data);
    }

    private function getErpDb()
    {
        return Db::connect(config('database.db2'));
    }

    private function batchResolveDisplaySyncStatus(array $orderNos)
    {
        $orderNos = array_values(array_unique(array_filter($orderNos)));
        if (empty($orderNos)) {
            return [];
        }

        $defaultStatus = $this->defaultDisplaySyncStatus();
        $result = [];
        foreach ($orderNos as $orderNo) {
            $result[$orderNo] = $defaultStatus;
        }

        $latestSyncRows = Db::name('mes_erp_work_order_sync_log')
            ->whereIn('work_order_no', $orderNos)
            ->order('id desc')
            ->select();
        $latestSyncMap = [];
        foreach ($latestSyncRows as $row) {
            if (!isset($latestSyncMap[$row['work_order_no']])) {
                $latestSyncMap[$row['work_order_no']] = $row;
            }
        }

        $successSyncRows = Db::name('mes_erp_work_order_sync_log')
            ->whereIn('work_order_no', $orderNos)
            ->where('sync_status', self::SYNC_STATUS_SUCCESS)
            ->order('id desc')
            ->select();
        $latestSuccessSyncMap = [];
        foreach ($successSyncRows as $row) {
            if (!isset($latestSuccessSyncMap[$row['work_order_no']])) {
                $latestSuccessSyncMap[$row['work_order_no']] = $row;
            }
        }

        $cancelRows = Db::name('mes_erp_operation_log')
            ->whereIn('work_order_no', $orderNos)
            ->where('operate_type', 'cancel')
            ->where('operate_status', self::OPERATE_STATUS_SUCCESS)
            ->order('id desc')
            ->select();
        $latestCancelMap = [];
        foreach ($cancelRows as $row) {
            if (!isset($latestCancelMap[$row['work_order_no']])) {
                $latestCancelMap[$row['work_order_no']] = $row;
            }
        }

        foreach ($orderNos as $orderNo) {
            $latestSync = isset($latestSyncMap[$orderNo]) ? $latestSyncMap[$orderNo] : null;
            if (empty($latestSync)) {
                continue;
            }

            $latestSuccessSync = isset($latestSuccessSyncMap[$orderNo]) ? $latestSuccessSyncMap[$orderNo] : null;
            if (!empty($latestSuccessSync)) {
                $cancelAfterSync = isset($latestCancelMap[$orderNo]) ? $latestCancelMap[$orderNo] : null;
                if (!empty($cancelAfterSync) && $cancelAfterSync['operate_time'] >= $latestSuccessSync['sync_time']) {
                    $result[$orderNo] = [
                        'code' => 'cancelled',
                        'text' => '已撤回',
                        'sync_time' => $latestSuccessSync['sync_time'],
                        'sync_msg' => $latestSync['sync_msg'],
                        'sync_log_id' => $latestSuccessSync['id'],
                    ];
                    continue;
                }
                $result[$orderNo] = [
                    'code' => 'synced',
                    'text' => '已同步',
                    'sync_time' => $latestSuccessSync['sync_time'],
                    'sync_msg' => $latestSuccessSync['sync_msg'],
                    'sync_log_id' => $latestSuccessSync['id'],
                ];
                continue;
            }

            if ((int)$latestSync['sync_status'] === self::SYNC_STATUS_FAILED) {
                $result[$orderNo] = [
                    'code' => 'failed',
                    'text' => '同步失败',
                    'sync_time' => $latestSync['sync_time'],
                    'sync_msg' => $latestSync['sync_msg'],
                    'sync_log_id' => $latestSync['id'],
                ];
                continue;
            }

            $result[$orderNo] = [
                'code' => 'pending',
                'text' => '待同步',
                'sync_time' => $latestSync['sync_time'],
                'sync_msg' => $latestSync['sync_msg'],
                'sync_log_id' => $latestSync['id'],
            ];
        }

        return $result;
    }

    private function resolveDisplaySyncStatus($workOrderNo)
    {
        $latestSync = Db::name('mes_erp_work_order_sync_log')
            ->where('work_order_no', $workOrderNo)
            ->order('id desc')
            ->find();

        if (empty($latestSync)) {
            return $this->defaultDisplaySyncStatus();
        }

        $latestSuccessSync = Db::name('mes_erp_work_order_sync_log')
            ->where('work_order_no', $workOrderNo)
            ->where('sync_status', self::SYNC_STATUS_SUCCESS)
            ->order('id desc')
            ->find();

        if (!empty($latestSuccessSync)) {
            $cancelAfterSync = Db::name('mes_erp_operation_log')
                ->where('work_order_no', $workOrderNo)
                ->where('operate_type', 'cancel')
                ->where('operate_status', self::OPERATE_STATUS_SUCCESS)
                ->where('operate_time', '>=', $latestSuccessSync['sync_time'])
                ->order('id desc')
                ->find();
            if (!empty($cancelAfterSync)) {
                return [
                    'code' => 'cancelled',
                    'text' => '已撤回',
                    'sync_time' => $latestSuccessSync['sync_time'],
                    'sync_msg' => $latestSync['sync_msg'],
                    'sync_log_id' => $latestSuccessSync['id'],
                ];
            }
            return [
                'code' => 'synced',
                'text' => '已同步',
                'sync_time' => $latestSuccessSync['sync_time'],
                'sync_msg' => $latestSuccessSync['sync_msg'],
                'sync_log_id' => $latestSuccessSync['id'],
            ];
        }

        if ((int)$latestSync['sync_status'] === self::SYNC_STATUS_FAILED) {
            return [
                'code' => 'failed',
                'text' => '同步失败',
                'sync_time' => $latestSync['sync_time'],
                'sync_msg' => $latestSync['sync_msg'],
                'sync_log_id' => $latestSync['id'],
            ];
        }

        return [
            'code' => 'pending',
            'text' => '待同步',
            'sync_time' => $latestSync['sync_time'],
            'sync_msg' => $latestSync['sync_msg'],
            'sync_log_id' => $latestSync['id'],
        ];
    }

    private function defaultDisplaySyncStatus()
    {
        return [
            'code' => 'unsynced',
            'text' => '未同步',
            'sync_time' => '',
            'sync_msg' => '',
            'sync_log_id' => null,
        ];
    }

    private function buildSyncOperateContent($workOrderNo, $syncModule, $operateType)
    {
        $moduleText = $this->syncModuleText($syncModule);
        $typeMap = [
            'sync' => '同步',
            'retry' => '重试同步',
            'manual' => '手动同步',
            'auto' => '自动同步',
        ];
        $typeText = isset($typeMap[$operateType]) ? $typeMap[$operateType] : '同步';
        return $typeText . '工单' . $workOrderNo . '，模块：' . $moduleText;
    }

    private function syncStatusText($status)
    {
        $map = [
            self::SYNC_STATUS_PENDING => '待同步',
            self::SYNC_STATUS_SUCCESS => '同步成功',
            self::SYNC_STATUS_FAILED => '同步失败',
        ];
        return isset($map[(int)$status]) ? $map[(int)$status] : '未知';
    }

    private function syncModuleText($module)
    {
        $map = [
            '1' => '工单数据',
            '2' => '工艺数据',
            '3' => '报工数据',
            '4' => '全量同步',
        ];
        return isset($map[(string)$module]) ? $map[(string)$module] : (string)$module;
    }

    private function operateStatusText($status)
    {
        $map = [
            self::OPERATE_STATUS_SUCCESS => '成功',
            self::OPERATE_STATUS_FAILED => '失败',
            self::OPERATE_STATUS_PROCESSING => '处理中',
        ];
        return isset($map[(int)$status]) ? $map[(int)$status] : '未知';
    }

    private function formatDateValue($value)
    {
        if (empty($value)) {
            return null;
        }
        $timestamp = strtotime($value);
        return $timestamp ? date('Y-m-d', $timestamp) : null;
    }
}