drb/app/Console/Commands/U8Settle.php

<?php

namespace App\Console\Commands;

use App\Jobs\ProcessWMSDataJob;
use App\Model\SyncTempRecord;
use App\Service\U8ThirtyPartyDatabaseServerService;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Redis;

class U8Settle extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'command:u8_settle';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Command description';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    public function handle()
    {
        try {
            $this->settle();
        }catch (\Exception $exception){
            Log::channel('u8_daily')->info('单据同步异常', ['msg' => $exception->getMessage() . '|' . $exception->getLine()]);
        }
    }

    public function settle(){
        $service = new U8ThirtyPartyDatabaseServerService();
        $minPeriods = $this->getYjData();
        if(empty($minPeriods['pu_date']) || empty($minPeriods['st_date']) || empty($minPeriods['sa_date'])) {
            Log::channel('u8_daily')->info('月结数据为空', ['msg' => $minPeriods]);
            return;
        }

        //单据-----------------
        $this->orderInsert($service, $minPeriods);
    }

    public function orderInsert($service, $minPeriods){
        $pu = $minPeriods['pu_date'] . ' 00:00:00.000';
        $st = $minPeriods['st_date'] . ' 00:00:00.000';
        $sa = $minPeriods['sa_date'] . ' 00:00:00.000';

        $dateMap = [
            SyncTempRecord::type_one   => $pu,
            SyncTempRecord::type_two   => $st,
            SyncTempRecord::type_three => $st,
            SyncTempRecord::type_four  => $sa,
            SyncTempRecord::type_five  => $st,
            SyncTempRecord::type_six  => $sa,
            SyncTempRecord::type_seven  => $st,
            SyncTempRecord::type_eight  => $st,
        ];

        // 配置不同单据的表名和字段
        $tasks = [
            SyncTempRecord::type_one => [// 采购到货 0 |退货 1
                'main' => 'PU_ArrivalVouch',
                'detail' => 'PU_ArrivalVouchs',
                'main_key' => 'ID',
                'key' => 'ID',
                'whereRaw' => "(dDate >= '{$pu}')",
                'main_field' => ['ID as id','cCode as no','dDate as order_date', 'cMakeTime as crt_time', 'cModifyTime as upd_time', 'iBillType as type', 'cverifier as reviewer'],
                'son_field' => [
                    'detail.ID as id',
//                    'detail.ivouchrowno as lineNum',
//                    'detail.cWhCode as warehouseCode',
                    'detail.cInvCode as materialCode',
                    'detail.iQuantity as planQty',
                    'detail.cDefine23',
                    'detail.cBatch',
                ],
                'limit' => 30,
            ],
            SyncTempRecord::type_two => [// 领料申请单 材料出库----
                'main' => 'MaterialAppVouch',
                'detail' => 'MaterialAppVouchs',
                'main_key' => 'ID',
                'key' => 'ID',
                'whereRaw' => "(dDate >= '{$st}')",
                'main_field' => ['ID as id','cCode as no', 'dDate as order_date', 'dnmaketime as crt_time', 'dnmodifytime as upd_time','iverifystate as state', 'cHandler as reviewer'],
                'son_field' => [
                    'detail.ID as id',
//                    'detail.ivouchrowno as lineNum',
//                    'detail.cWhCode as warehouseCode',
                    'detail.cInvCode as materialCode',
                    'detail.iQuantity as planQty',
                ],
                'limit' => 30,
            ],
            SyncTempRecord::type_three => [// 产品报检单 产成品入库流程----- 没有仓库
                'main' => 'QMINSPECTVOUCHER',
                'detail' => 'QMINSPECTVOUCHERS',
                'main_key' => 'ID',
                'key' => 'ID',
                'whereRaw' => "(DDATE >= '{$st}' and CVOUCHTYPE = 'QM02')",
                'main_field' => ['ID as id','CINSPECTCODE as no','DDATE as order_date','DMAKETIME as crt_time', 'DMODIFYTIME as upd_time','CVERIFIER as reviewer'],
                'son_field' => [
                    'detail.ID as id',
                    'detail.AUTOID  as auto_id',
                    'detail.CINVCODE as materialCode',
                    'detail.FQUANTITY as planQty',
                    'detail.cDefine23',
                ],
                'limit' => 30,
            ],
            SyncTempRecord::type_four => [// 销售订单 销售出库流程 ------- 没有仓库
                'main' => 'SO_SOMain',
                'detail' => 'SO_SODetails',
                'main_key' => 'ID',
                'key' => 'ID',
                'whereRaw' => "(dDate >= '{$sa}')",
                'main_field' => ['ID as id','cSOCode as no','dDate as order_date','dcreatesystime as crt_time', 'dmodifysystime as upd_time','cVerifier as reviewer'],
                'son_field' => [
                    'detail.ID as id',
                    'detail.cInvCode as materialCode',
                    'detail.iQuantity as planQty',
                ],
                'limit' => 30,
            ],
            SyncTempRecord::type_five => [// 其他入  其他入库流程
                'main' => 'RdRecord08',
                'detail' => 'RdRecords08',
                'main_key' => 'ID',
                'key' => 'ID',
                'whereRaw' => "(dDate >= '{$st}' and COALESCE(cMemo, '') <> '接口生成')",
                'main_field' => ['ID as id','cCode as no','dDate as order_date','dnmaketime as crt_time', 'dnmodifytime as upd_time','cWhCode as warehouseCode','cHandler as reviewer'],
                'son_field' => [
                    'detail.ID as id',
                    'detail.cInvCode as materialCode',
                    'detail.iQuantity as planQty',
                ],
                'limit' => 30,
            ],
            SyncTempRecord::type_six => [// 销售退货单  其他入库流程
                'main' => 'DispatchList',
                'detail' => 'DispatchLists',
                'main_key' => 'DLID',
                'key' => 'DLID',
                'whereRaw' => "(dDate >= '{$st}' and bReturnFlag = 1)",
                'main_field' => ['DLID as id','cDLCode as no', 'dDate as order_date','dcreatesystime as crt_time', 'dmodifysystime as upd_time','cVerifier as reviewer'],
                'son_field' => [
                    'detail.DLID as id',
                    'detail.cInvCode as materialCode',
                    'detail.cWhCode as warehouseCode',
                    'detail.iQuantity as planQty',
                    'detail.cDefine23',
                    'detail.cBatch',
                    'detail.dMDate',
                    'detail.iMassDate',
                ],
                'limit' => 30,
            ],
            SyncTempRecord::type_seven => [// 其他出  其他入库流程
                'main' => 'RdRecord09',
                'detail' => 'RdRecords09',
                'main_key' => 'ID',
                'key' => 'ID',
                'whereRaw' => "(dDate >= '{$st}' and COALESCE(cMemo, '') <> '接口生成')",
                'main_field' => ['ID as id','cCode as no','dDate as order_date','dnmaketime as crt_time', 'dnmodifytime as upd_time','cWhCode as warehouseCode','cHandler as reviewer'],
                'son_field' => [
                    'detail.ID as id',
                    'detail.cInvCode as materialCode',
                    'detail.iQuantity as planQty',
                ],
                'limit' => 30,
            ],
            SyncTempRecord::type_eight => [
                //检验单
                //cvouchtype=QM03=>采购到货单 1  来料报检单
                //cvouchtype=QM04=>产品检验单 2
                //cvouchtype=QM14=>退货检验单 3
                'main' => 'QMCHECKVOUCHER',
                'detail' => '',
                'main_key' => 'ID',
                'key' => '',
                'whereRaw' => "(DDATE >= '{$st}' and (CVOUCHTYPE = 'QM03' OR CVOUCHTYPE = 'QM04'))",
                'main_field' => ['ID as id','CCHECKCODE as no','DDATE as order_date','DMAKETIME as crt_time', 'DMODIFYTIME as upd_time','CVERIFIER as reviewer', 'CVOUCHTYPE as type', 'CINVCODE as materialCode', 'CBATCH as lot', 'FREGQUANTITY as hg_quantity', 'FDISQUANTITY as hg_not_quantity', 'FCONQUANTIY as rb_quantity','SOURCECODE as from_order', 'CINSPECTCODE as bj_order'],
                'son_field' => [],
                'limit' => 30,
            ],
        ];

        $time = time();

        foreach ($tasks as $name => $config) {
            // 用于记录本次在 U8 查到的所有单号，用来比对删除
            $currentU8Nos = [];
            $lastId = 0;
            $orderDateLimit = $dateMap[$name]; // 当前类型的起始日期限制

            while (true) {
                list($status, $items) = $service->getPendingBills($config, $lastId);
                if (!$status || empty($items)) break;

                $nos = collect($items)->pluck('no')->toArray();
                $currentU8Nos = array_merge($currentU8Nos, $nos); // 记录当前存在的单号

                //获取这 30 条的快照
                $snapshots = DB::table('sync_snapshot')
                    ->where('type', $name)
                    ->whereIn('u8_no', $nos)
                    ->get()
                    ->keyBy('u8_no');

                foreach ($items as $item) {
                    if ($name == SyncTempRecord::type_eight){
                        $type_v = 0;
                        if($item['type'] == 'QM03'){
                            $type_v = 1;
                        }elseif ($item['type'] == 'QM04'){
                            $type_v = 2;
                        }elseif ($item['type'] == 'QM14'){
                            $type_v = 3;
                        }
                        $item['type'] = $type_v;
                    }

                    $no = $item['no'];
                    $u8Id = $item['id']; // 抓取主表 ID
                    $u8Upd = $item['upd_time'] ?: $item['crt_time'];
                    $snapshot = $snapshots->get($no);
                    $type_2 = isset($item['type']) ? $item['type'] : 0;
                    $bool = $item['reviewer'] ? true : false;
                    if(! $bool) continue;// 没审核跳过

                    $opType = null;
                    if (!$snapshot) {
                        $opType = SyncTempRecord::opt_zero;
                    } elseif ($u8Upd > $snapshot->last_upd_time) {
                        $opType = SyncTempRecord::opt_one;
                    }

                    if ($opType !== null) {
                        // 写入任务流水
                        $this->createSyncTask($name, $no, $u8Id, $item, $opType, $u8Upd, $time, $type_2);
                    }
                }

                //分页最大id
                $lastId = collect($items)->max('id');
            }

            $currentU8NosLookup = array_flip($currentU8Nos);

            // 使用 chunk 配合日期过滤，只查快照中大于等于月结日期的单据
            DB::table('sync_snapshot')
                ->where('type', $name)
                ->where('order_date', '>=', $orderDateLimit) // 关键：缩小快照查询范围
                ->orderBy('u8_id')
                ->chunk(100, function ($snapshots) use ($currentU8NosLookup, $name, $time) {
                    foreach ($snapshots as $oldSnapshot) {
                        // 如果快照里的单号不在本次 U8 扫描结果里
                        if (!isset($currentU8NosLookup[$oldSnapshot->u8_no])) {
                            $this->createSyncTask($name, $oldSnapshot->u8_no, $oldSnapshot->u8_id, $oldSnapshot->payload, SyncTempRecord::opt_two, "", $time);
                        }
                    }
                });

            unset($currentU8Nos); // 释放内存
        }
    }

    private function createSyncTask($type, $no, $u8Id, $payload, $opType, $u8Upd = "", $time, $type_2 = 0) {
        $record = SyncTempRecord::create([
            'type'    => $type,
            'type_2'  => $type_2,
            'u8_no'   => $no,
            'u8_id'   => $u8Id,
            'payload' => json_encode($payload),
            'u8_upd'  => $u8Upd,
            'op_type' => $opType,
            'crt_time'=> $time,
        ]);

        // 只分发 ID 给队列，Job 内部再根据此 ID 取 u8_id 和 payload
        ProcessWMSDataJob::dispatch(['id' => $record->id])->onQueue("sync_wms_order");
    }

    private function getYjData(){
        //结账
        //采购到货单 | 退货单  bflag_PU  采购
        //领料申请单|产成品入库单| bflag_ST 出和入
        //销售发货单 销售退货 bflag_SA 销售
        $result = DB::connection('u8_third_sqlserver')
            ->table('gl_mend')
            ->lock('WITH(NOLOCK)')
            ->selectRaw("
                MIN(CASE WHEN bflag_PU = 0 AND iyear >= 2023 THEN iYPeriod END) as min_pu,
                MIN(CASE WHEN bflag_ST = 0 THEN iYPeriod END) as min_st,
                MIN(CASE WHEN bflag_SA = 0 THEN iYPeriod END) as min_sa
            ")
            ->whereRaw("RIGHT(CAST(iYPeriod AS VARCHAR), 2) <> '00'")
            ->first();
        // 转换成数组方便取值
        $minPeriods = (array)$result;
        $formatDate = function($period) {
            if (!$period) return null; // 如果没有未结账月份，返回null

            $period = (string)$period; // 确保是字符串
            $year = substr($period, 0, 4);
            $month = substr($period, 4, 2);

            return "{$year}-{$month}-01";
        };
        // 2. 执行转换
        $formattedPeriods = [
            'pu_date' => $formatDate($minPeriods['min_pu'] ?? null),
            'st_date' => $formatDate($minPeriods['min_st'] ?? null),
            'sa_date' => $formatDate($minPeriods['min_sa'] ?? null),
        ];

        return $formattedPeriods;
    }
}