baoshi-was/app/Services/OrderService.php

<?php

namespace App\Services;

use App\Logistic;
use App\OracleActAllocationDetails;
use App\OracleBasCustomer;
use App\OracleDOCASNHeader;
use App\OracleDOCOrderHeader;
use App\Order;
use App\OrderPackage;
use App\OrderPackageCommodities;
use App\Owner;
use App\RejectedBill;
use App\Services\common\BatchUpdateService;
use App\Services\common\DataHandlerService;
use App\Shop;
use Carbon\Carbon;
use Illuminate\Database\Eloquent\Collection;
use Illuminate\Support\Facades\DB;

class OrderService
{
    public function batchUpdate($params){
        return app(BatchUpdateService::class)->batchUpdate('orders',$params);
    }

    public function insert($params){
        return Order::query()->insert($params);
    }

    private function preciseQuery(array $params,$sql){
        $orderdate_start=$params["orderdate_start"] ?? null;
        $orderdate_end=$params["orderdate_end"] ?? null;
        $customerid=$params["customerid"] ?? null;
        $orderno=$params["orderno"] ?? null;
        $soreference5=$params["soreference5"] ?? null;
        $codename_c=$params["codename_c"] ?? null;
        $codename_c_end=$params["codename_c_end"] ?? null;
        $carriername=$params["carriername"] ?? null;
        $soreference1=$params["soreference1"] ?? null;
        $issuepartyname=$params["issuepartyname"] ?? null;
        $notes=$params["notes"] ?? null;
        $addtime=$params["addtime"] ?? null;
        $waveno=$params["waveno"] ?? null;
        $alternate_sku1=$params["alternate_sku1"] ?? null;
        $c_contact = $params["c_contact"] ?? null;
        $c_tel2 = $params["c_tel2"] ?? null;
        $c_province = $params["c_province"] ?? null;
        $c_city = $params["c_city"] ?? null;
        $c_district = $params["c_district"] ?? null;
        $releasestatus = $params["releasestatus"] ?? null;
        $ordernos = $params["ordernos"] ?? null;
        $edisendflag2=$params["edisendflag2"] ?? null;
        $edisendflag=$params["edisendflag"] ?? null;
        $checkAllSign = $params['checkAllSign'] ?? null;
        if ($ordernos){
            $sql .= " AND orderno IN ".$ordernos;
        }
        if ($c_contact){
            $sql .= " AND c_contact like '".$c_contact."'";
        }
        if ($c_tel2){
            $sql .= " AND c_tel2 like '".$c_tel2."'";
        }
        if ($c_province){
            $sql .= " AND c_province like '".$c_province."'";
        }
        if ($c_city){
            $sql .= " AND c_city like '".$c_city."'";
        }
        if ($c_district){
            $sql .= " AND c_district like '".$c_district."'";
        }
        if ($releasestatus){
            $sql .= " AND releasestatus = '".$releasestatus."'";
        }
        if ($alternate_sku1){
            if ($checkAllSign) $detailsOrderno = $this->getOrdersNo($alternate_sku1,false);
            else $detailsOrderno = $this->getOrdersNo($alternate_sku1,true, $request->page ?? 1, $request->paginate ?? 50);
            if (count($detailsOrderno)>0){
                $sql.=' AND orderno IN (';
                foreach ($detailsOrderno as $index => $no){
                    if ($index==0){
                        $sql.="'".$no."'";
                        continue;
                    }
                    $sql.=",'".$no."'";
                }
                $sql.=')';
            }else{
                $sql .= 'AND orderno IS NULL ';
            }
        }
        if ($orderdate_start && $orderdate_end && $addtime){
            $orderdate_start=null;
            $orderdate_end=null;
        }
        if ($orderdate_start){
            if (strlen($orderdate_start)<11)$orderdate_start .= ' 00:00';
            $sql.=" and addtime>=to_date('".$orderdate_start.":00','yyyy-mm-dd hh24:mi:ss')";
        }
        if ($orderdate_end){
            if (strlen($orderdate_end)<11)$orderdate_end .= ' 23:59 ';
            $sql.=" and addtime<=to_date('".$orderdate_end.":59','yyyy-mm-dd hh24:mi:ss')";
        }
        if ($customerid){
            if (!is_array($customerid))$arr = explode(',',$customerid);
            else $arr = $customerid;
            if (count($arr)>0) {
                $sql.=" and customerid in (";
                foreach ($arr as $index=>$str){
                    if ($index==0){
                        $sql.="'".$str."'";
                        continue;
                    }
                    $sql.=",'".$str."'";
                }
                $sql.=")";
            }
        }
        if ($orderno)$sql.=" and orderno like '".$orderno."%'";
        if ($codename_c&&$codename_c_end)$sql.=" and sostatus between '".$codename_c."' and '".$codename_c_end."'";
        if ($codename_c&&!$codename_c_end)$sql.=" and sostatus = '".$codename_c."'";
        if (!$codename_c&&$codename_c_end)$sql.=" and sostatus = '".$codename_c_end."'";
        if ($carriername)$sql.=" and carriername like '".$carriername."%'";
        if ($soreference1){
            $arr=array_filter(preg_split('/[,， ]+/is', $soreference1));
            if (count($arr)==1){
                $sql.=" and soreference1 like '".$soreference1."%'";
            }
            if (count($arr)>1){
                $sql.=" and soreference1 in (";
                foreach ($arr as $index=>$str){
                    if ($index==0){
                        $sql.="'".$str."'";
                        continue;
                    }
                    $sql.=",'".$str."'";
                }
                $sql.=")";
            }
        }
        if ($waveno)$sql.=" and WaveNo like '".$waveno."%'";
        if ($issuepartyname)$sql.=" and issuepartyname like '".$issuepartyname."%'";
        if ($notes){
            if ($addtime&&$addtime!=0)$sql.=" and addtime >= to_date('".date('Y-m-d H:i:s',strtotime("-".$addtime." day"))."','yyyy-mm-dd hh24:mi:ss')";
            $sql.=" and notes like '%".$notes."%'";
        }
        if ($soreference5){
            $arr=array_filter(preg_split('/[,， ]+/is', $soreference5));
            if (count($arr)>0){
                $sql.=" and (soreference5 in (";
                foreach ($arr as $index=>$str){
                    if ($index==0){
                        $sql.="'".$str."'";
                        continue;
                    }
                    $sql.=",'".$str."'";
                }
                $sql.=") ";
                /*分箱号*/
                $oracleActAllocationDetails=DB::connection('oracle')->table('ACT_ALLOCATION_DETAILS')->whereIn('picktotraceid',$arr)->get();
                $picktotraceids=array_unique(array_column($oracleActAllocationDetails->toArray(),'orderno'));
                if (count($picktotraceids)>0){
                    $sql.='OR orderno IN (';
                    foreach ($picktotraceids as $index=>$str){
                        if ($index==0){
                            $sql.="'".$str."'";
                            continue;
                        }
                        $sql.=",'".$str."'";
                    }
                    $sql.=") ";
                }
                $sql.=")";
            }
        }
        if ($edisendflag2){
            $sql.=" and edisendflag2 like '".$edisendflag2."%'";
        }
        if ($edisendflag){
            $sql.=" and edisendflag like '".$edisendflag."%'";
        }
        return $sql;
    }

    public function getSql($params, $page = null, $paginate = null){
        $checktime_start = $params['checktime_start'] ?? null;
        $checktime_end = $params['checktime_end'] ?? null;
        $checkData = $params['data'] ?? null;
        if ($checktime_start || $checktime_end){
            /** @var  OracleActAllocationDetailService */
            $ordernos = app('oracleActAllocationDetailService')
                ->getOrderno(['checktime_start'=>$checktime_start,'checktime_end'=>$checktime_end,
                    'paginate'=>$paginate,'page'=>$page]);
            $params['ordernos'] = $ordernos;
        }
        $sql="select ACT_ALLOCATION_DETAILS.picktotraceid,ACT_ALLOCATION_DETAILS.CHECKTIME,DOC_ORDER_HEADER.addtime,DOC_ORDER_HEADER.C_PROVINCE,DOC_ORDER_HEADER.C_CITY,DOC_ORDER_HEADER.C_DISTRICT,DOC_ORDER_HEADER.C_CONTACT,DOC_ORDER_HEADER.OrderNo,DOC_ORDER_HEADER.SOStatus,DOC_ORDER_HEADER.WAREHOUSEID,DOC_ORDER_HEADER.CustomerID
        ,DOC_ORDER_HEADER.C_Tel2,DOC_ORDER_HEADER.CarrierName,DOC_ORDER_HEADER.IssuePartyName,DOC_ORDER_HEADER.EDIREMARKS2,
       DOC_ORDER_HEADER.WaveNo,DOC_ORDER_HEADER.SOReference1
        ,DOC_ORDER_HEADER.soreference5,DOC_ORDER_HEADER.EDISENDFLAG2,DOC_ORDER_HEADER.EDISendTime2,DOC_ORDER_HEADER.Notes,DOC_ORDER_HEADER.ERPCANCELFLAG,
       DOC_ORDER_HEADER.Picking_Print_Flag,DOC_ORDER_HEADER.EDISENDFLAG
        ,DOC_ORDER_HEADER.ReleaseStatus,DOC_ORDER_HEADER.C_Address1,DOC_ORDER_HEADER.OrderTime,DOC_Order_Details.CustomerID,
       DOC_Order_Details.SKU,DOC_Order_Details.QtyOrdered,DOC_Order_Details.LineStatus,DOC_Order_Details.OrderLineNo,order_code.codename_c as orderCodeName,
       order_detail_code.codename_c as orderDetailCodeName,BAS_Customer.descr_c as customer_descr_c,
       BAS_SKU.Alternate_SKU1,BAS_SKU.Descr_C,(select count(*) from DOC_ORDER_HEADER WHERE 1=1";
        $sql=$this->preciseQuery($params,$sql);
        ;$sql.=" ) as counted from (";
        if ($paginate && $page)$sql.=" select * from (";
        $sql.=" select ROWNUM as rn,DOC_ORDER_HEADER.addtime,DOC_ORDER_HEADER.C_PROVINCE,DOC_ORDER_HEADER.C_CITY,DOC_ORDER_HEADER.C_DISTRICT,DOC_ORDER_HEADER.C_CONTACT,DOC_ORDER_HEADER.OrderNo,DOC_ORDER_HEADER.SOStatus,DOC_ORDER_HEADER.WAREHOUSEID,DOC_ORDER_HEADER.CustomerID
                          ,DOC_ORDER_HEADER.C_Tel2,DOC_ORDER_HEADER.CarrierName,DOC_ORDER_HEADER.IssuePartyName,
                         DOC_ORDER_HEADER.WaveNo,DOC_ORDER_HEADER.SOReference1
                          ,DOC_ORDER_HEADER.soreference5,DOC_ORDER_HEADER.EDISENDFLAG2,DOC_ORDER_HEADER.EDISendTime2,DOC_ORDER_HEADER.Notes,DOC_ORDER_HEADER.ERPCANCELFLAG,
                         DOC_ORDER_HEADER.Picking_Print_Flag,DOC_ORDER_HEADER.EDISENDFLAG
                          ,DOC_ORDER_HEADER.ReleaseStatus,DOC_ORDER_HEADER.C_Address1,DOC_ORDER_HEADER.OrderTime,DOC_ORDER_HEADER.EDIREMARKS2 from (select * from DOC_ORDER_HEADER  order by ADDTIME desc )DOC_ORDER_HEADER where 1=1 ";
        $sql=$this->preciseQuery($params,$sql);
        if ($checkData){
            if (!is_array($checkData)) $checkData=explode(',',$checkData);
            if (count($checkData)>0){
                $sql.=" and orderno in (";
                foreach ($checkData as $index=>$str){
                    if ($index==0){
                        $sql.="'".$str."'";
                        continue;
                    }
                    $sql.=",'".$str."'";
                }
                $sql.=")";
            }
        }
        if ($paginate && $page)$sql.=" and ROWNUM<='".$page*$paginate."')";
        if ($paginate && $page)$sql.=" header where header.rn>'".($page-1)*$paginate."' ";
        $sql.=")DOC_ORDER_HEADER left join DOC_ORDER_DETAILS on DOC_ORDER_DETAILS.ORDERNO=DOC_ORDER_HEADER.ORDERNO
                      left join BAS_CODES  order_code on order_code.CODE=DOC_ORDER_HEADER.sostatus and order_code.codeid='SO_STS'
                      left join BAS_CODES  order_detail_code on order_detail_code.CODE=DOC_ORDER_DETAILS.linestatus and order_detail_code.codeid='SO_STS'
                      left join BAS_Customer on BAS_Customer.customerid=DOC_ORDER_HEADER.customerid
                      left join  BAS_SKU on DOC_Order_Details.CustomerID=BAS_SKU.CustomerID and DOC_Order_Details.SKU=BAS_SKU.SKU
                      left join ACT_ALLOCATION_DETAILS on DOC_Order_Details.orderno=ACT_ALLOCATION_DETAILS.orderno and
                      DOC_Order_Details.orderlineno=ACT_ALLOCATION_DETAILS.orderlineno GROUP BY ACT_ALLOCATION_DETAILS.picktotraceid,
                      ACT_ALLOCATION_DETAILS.CHECKTIME,DOC_ORDER_HEADER.addtime,DOC_ORDER_HEADER.C_PROVINCE,DOC_ORDER_HEADER.C_CITY,
                      DOC_ORDER_HEADER.C_DISTRICT,DOC_ORDER_HEADER.C_CONTACT,DOC_ORDER_HEADER.OrderNo,DOC_ORDER_HEADER.SOStatus,DOC_ORDER_HEADER.WAREHOUSEID,
                      DOC_ORDER_HEADER.CustomerID,DOC_ORDER_HEADER.C_Tel2,DOC_ORDER_HEADER.CarrierName,DOC_ORDER_HEADER.IssuePartyName,
                      DOC_ORDER_HEADER.EDIREMARKS2,DOC_ORDER_HEADER.WaveNo,DOC_ORDER_HEADER.SOReference1, DOC_ORDER_HEADER.soreference5,
                      DOC_ORDER_HEADER.EDISENDFLAG2,DOC_ORDER_HEADER.EDISendTime2,DOC_ORDER_HEADER.Notes,DOC_ORDER_HEADER.ERPCANCELFLAG,
                      DOC_ORDER_HEADER.Picking_Print_Flag,DOC_ORDER_HEADER.EDISENDFLAG,DOC_ORDER_HEADER.ReleaseStatus,DOC_ORDER_HEADER.C_Address1,
                      DOC_ORDER_HEADER.OrderTime,DOC_Order_Details.CustomerID,DOC_Order_Details.SKU,DOC_Order_Details.QtyOrdered,
                      DOC_Order_Details.LineStatus,DOC_Order_Details.OrderLineNo,order_code.codename_c,order_detail_code.codename_c,BAS_Customer.descr_c,
                      BAS_SKU.Alternate_SKU1,BAS_SKU.Descr_C ORDER BY DOC_ORDER_HEADER.ADDTIME desc";
        return $sql;
    }

    //通过商品条码获取订单编号
    public function getOrdersNo($alternate_sku1, $isPaging = true, $page = 1, $paginate = 50){
        if ($isPaging){
            $sql = "SELECT RESULT.ORDERNO FROM
                        (SELECT ROWNUM rn,DETAIL.ORDERNO as ORDERNO FROM
                            (SELECT ORDERNO FROM DOC_ORDER_DETAILS WHERE SKU LIKE '".$alternate_sku1."' GROUP BY ORDERNO ORDER BY ORDERNO DESC)DETAIL
                        WHERE ROWNUM<='".$page*$paginate."')RESULT
                    WHERE RESULT.rn>'".($page-1)*$paginate."'";
        }else {
            $sql = "SELECT ORDERNO FROM DOC_ORDER_DETAILS WHERE SKU LIKE '".$alternate_sku1."' GROUP BY ORDERNO ORDER BY ORDERNO DESC";
        }
        $orderDetails = DB::connection('oracle')->select(DB::raw($sql));
        return array_column($orderDetails,'orderno');
    }

    public function paginate(array $params){
        if (($params['is_display_all'] ?? false) && !($params['codename_c'] ?? false) && !($params['codename_c_end'] ?? false)){
            $params['codename_c'] = "00";
            $params['codename_c_end'] = "62";
        }

        $sql = $this->getSql($params,$params['page'] ?? 1, $params['paginate'] ?? 50);
        $orders=DB::connection('oracle')->select(DB::raw($sql));
        return $this->orderFormat($orders);
    }

    public function get(array $params){
        $sql = $this->getSql($params);
        $orders=DB::connection('oracle')->select(DB::raw($sql));
        return $this->orderFormat($orders);
    }

    private function orderFormat($orders){
        $commodities=[];
        $picktotraceids=[];
        /*
         * 第二种扁平化方式(节省空间)： 使用快慢指针，因拿到数据正常为顺序数据,自下标1开始与上一条对比orderno唯一标识，不同则更新慢指针下标，
         *  相同则为指定下标map类型数组追加一个键值对属性来记录同类数量,如N条，每次追加属性时先看属性是否存在，存在+1
         * */
        foreach ($orders as $index=>$order){
            if ($order->picktotraceid){
                if (array_key_exists($order->orderno,$picktotraceids)){
                    if (!in_array($order->picktotraceid,$picktotraceids[$order->orderno],true)){
                        array_push($picktotraceids[$order->orderno],$order->picktotraceid);
                    };
                }else $picktotraceids[$order->orderno] = [$order->picktotraceid];
            }
            if (isset($commodities[$order->orderno])){
                array_push($commodities[$order->orderno],
                    ["sku"=>$order->sku,"alternate_sku1"=>$order->alternate_sku1,"descr_c"=>$order->descr_c,"qtyordered"=>$order->qtyordered
                        ,"checktime"=>$order->checktime,"orderdetailcodename"=>$order->orderdetailcodename]);
                unset($orders[$index]);
                continue;
            }
            $commodities[$order->orderno]=[["sku"=>$order->sku,"alternate_sku1"=>$order->alternate_sku1,
                "descr_c"=>$order->descr_c,"qtyordered"=>$order->qtyordered,"checktime"=>$order->checktime,"orderdetailcodename"=>$order->orderdetailcodename]];
        }
        $picktotraceids=json_encode($picktotraceids);
        $orders=array_values($orders);
        $orders = new Collection($orders);
        $commodities=new Collection($commodities);
        return ['picktotraceids'=>$picktotraceids,'orders'=>$orders,'commodities'=>$commodities];
    }

    public function findOrCreateByClientCode($clientCode)
    {
        /** @var OrderPackageService $orderPackageService */
        $orderPackageService = app('orderPackageService');
        $order = Order::query()->where('client_code', $clientCode)->first();
        $orderHeader = OracleDOCOrderHeader::query()->where('SOReference1', $clientCode)->first();

        if ($orderHeader == null) {
            return null;
        }
        $orderHeadAttr = $orderHeader->toArray();
        if ($order == null) {
            $customer_name = $orderHeadAttr['oracleBASCustomer_descr_c'];
            $owner = Owner::query()->where('code', $orderHeadAttr['customerid'])->first();
            if (!$owner) {
                try {
                    $owner = Owner::query()->create(['code' => $orderHeadAttr['customerid'], 'name' => $customer_name]);
                    LogService::log(__METHOD__, __FUNCTION__, '创建货主' . json_encode($owner));
                } catch (\Exception $e) {
                    LogService::log(__METHOD__, __FUNCTION__, '创建货主失败' . $e->getMessage() . $e->getTraceAsString());
                }
            }
            $owner_id = $owner['id'];
            $shop_name = $orderHeadAttr['issuepartyname'];
            $shop_Id = null;
            if ($shop_name) {
                $shop = Shop::query()->firstOrCreate(['name' => $shop_name, 'owner_id' => $owner_id]);
                $shop_Id = $shop['id'];
            }
            $logistics = Logistic::query()->where(['code' => $orderHeadAttr['userdefine1']])->first();
            $logistics_id = null;
            if ($logistics) {
                $logistics_id = $logistics['id'];
            } else {
                try {

                    $logistics = Logistic::query()->create(['name' => $orderHeadAttr['carriername'], 'code' => $orderHeadAttr['userdefine1']]);
                    $logistics_id = $logistics['id'];
                    LogService::log(__METHOD__,__FUNCTION__,'创建承运商'.json_encode($logistics));
                } catch (\Exception $e) {
                    LogService::log(__METHOD__,__FUNCTION__,'创建承运商失败'.$e->getMessage().$e->getTraceAsString());
                }
            }
            $arr = [
                'code' => $orderHeadAttr['orderno'],
                'owner_id' => $owner_id,
                'wms_status' => $orderHeadAttr['oracleBASCode_codename_c'],
                'created_at' => $orderHeadAttr['addtime'],
                'logistic_id' => $logistics_id,
                'shop_id' => $shop_Id,
                'consignee_name' => $orderHeadAttr['c_contact'],
                'consignee_phone' => $orderHeadAttr['c_tel2'],
                'province' => $orderHeadAttr['c_province'],
                'city' => $orderHeadAttr['c_city'],
                'district' => $orderHeadAttr['c_district'],
                'address' => $orderHeadAttr['c_address1'],
                'client_code' => $orderHeadAttr['soreference1'],
            ];
            $order = Order::query()->create($arr);
            unset($arr,$orderHeadAttr,$owner);
        }
        $orderPackageService->createdByOrder($order);unset($order);
        return Order::with('packages.commodities.commodity')->where('client_code', $clientCode)->first();
    }

    public function getRejectedBillOfClientCode($clientCode)
    {
        $orderHeader = OracleDOCOrderHeader::query()->where('SOReference1', $clientCode)->first();
        if (!$orderHeader) {
            return null;
        }
        $ASNHeader = OracleDOCASNHeader::query()->where('ASNReference2', $orderHeader->soreference2)->first();
        if (!$ASNHeader) {
            return null;
        }
        return RejectedBill::query()->with(['owner', 'logistic', 'items'])->where('logistic_number_return', $ASNHeader->asnreference2)->first();
    }

    public function findOrCreteByLogisticNumberReturn($logisticNumberReturn)
    {
        $ASNHeader = OracleDOCASNHeader::query()->where('ASNReference3', $logisticNumberReturn)->first();
        if (!$ASNHeader) {
            return null;
        }
        $orderHeader = OracleDOCOrderHeader::query()->where('SOReference1', $ASNHeader->ASNReference2);
        if (!$orderHeader) {
            return null;
        }
        return $this->findOrCreateByClientCode($orderHeader['soreference1']);
    }

    public function 根据设置从WMS获取跟踪的订单($startDate)
    {

        /** @var OracleDOCOrderHeaderService $oracleDOCOrderHeaderService */
        $oracleDOCOrderHeaderService = app('oracleDOCOrderHeaderService');
        $orderHeadersResult = $oracleDOCOrderHeaderService->根据时间获取在这段时间内创建的WMS订单($startDate);

//        LogService::log('111','111',json_encode($orderHeadersResult));
        if ($orderHeadersResult->count() == 0) {
            return null;
        }

        $orderHeaders_map = [];
        $oracleDOCOrderDetails = [];
        $actAllocationDetails = [];

        foreach ($orderHeadersResult as $key=>$orderHeader) {
            $orderHeaders_map[$orderHeader['orderno']] = $orderHeader;
            $oracleDOCOrderDetails[$orderHeader['orderno']] = $orderHeader['oracleDOCOrderDetails'];
            $details = $orderHeader['actAllocationDetails'];
            $orderno = strval($orderHeader['orderno']);
            foreach ($details as $detail) {
                $picktotraceid = strval($detail['picktotraceid']);
                if ($picktotraceid == '*') {
                    if (!($actAllocationDetails[$orderno] ?? false)) {
                        $actAllocationDetails[$orderno] = [];
                    }
                    array_push($actAllocationDetails[$orderno], $detail);
                    continue;
                }
                if (!($actAllocationDetails[$picktotraceid] ?? false)) {
                    $actAllocationDetails[$picktotraceid] = [];
                }
                $actAllocationDetails[$picktotraceid][] =$detail;
            }
        }
        unset($orderHeadersResult);

        return $this->根据WMS订单生成WAS订单($orderHeaders_map, $oracleDOCOrderDetails, $actAllocationDetails);
    }

    public function 根据设置从WMS同步该时间段内更新的订单($startDate)
    {
        /** @var OracleDOCOrderHeaderService $oracleDOCOrderHeaderService */
        $oracleDOCOrderHeaderService = app('oracleDOCOrderHeaderService');
        $editedOrderHeaders = $oracleDOCOrderHeaderService->根据时间获取在这段时间内更新的WMS订单($startDate);

        $orderHeaders_map = [];
        foreach ($editedOrderHeaders as $editedOrderHeader) {
            $orderHeaders_map[$editedOrderHeader['orderno']] = $editedOrderHeader;
        }

        $orderNos = array_keys($orderHeaders_map);

        $orders = Order::query()
            ->with('packages.commodities')
            ->whereIn('code', $orderNos)
            ->get();

        if ($orders->count() == 0) {
            return ['orderPackageCommodities'=> null, 'orderHeaders'=>null];
        }

        $ordersList =[];
        foreach ($orders as $order) {
            $ordersList[$order['code']] = $order;
        }
        $orderHeaders_map = $this->filterNoCorrespondingOrder($orderHeaders_map,$ordersList);

        $this->将WMS订单对应的WAS订单信息更新($orderHeaders_map,$ordersList);

        $orderPackageCommodities = OrderPackageCommodities::query()
            ->with('package.order')
            ->whereHas('package.order', function ($query) use ($orderNos) {
                $query->whereIn('code',$orderNos);
            })->get();
        $orderHeaders = $orderHeaders_map;
        return compact('orderPackageCommodities', 'orderHeaders');
    }


    public function 根据WMS订单生成WAS订单($orderHeaders, $oracleDOCOrderDetails, $actAllocationDetails)
    {

        /** @var OrderPackageService $orderPackageService */
        $orderPackageService = app('orderPackageService');
        /** @var OrderPackageCommoditiesService $orderCommoditiesService */
        $orderCommoditiesService = app('orderPackageCommoditiesService');

        if (count($orderHeaders) === 0) {
            return [];
        }
        $params = $this->根据WMS订单信息返回创建WAS订单需要信息($orderHeaders);
        $owners = $params['owners'];
        $shops = $params['shops'];
        $logistics = $params['logistics'];
        $orderHeaders = $this->过滤已有的WAS订单($orderHeaders);
        $this->根据WMS订单创建WAS订单($orderHeaders, $owners, $shops, $logistics);
        $orderNos = array_keys($orderHeaders);
        $orders = Order::query()->whereIn('code',$orderNos)->get();
        $orders = $orderPackageService->根据WMS订单创建WAS包裹($orderHeaders, $orders);
        $orders = $orderCommoditiesService->根据WMS订单信息创建WAS订单包裹商品($orderHeaders, $oracleDOCOrderDetails, $actAllocationDetails, $orders);

        $orderPackages = [];
        foreach ($orders as $order) {
            $orderPackages[] = $order['packages'];
           $packages =  $order['packages'] ?? null;
            $orderPackages[] =  $packages;
//           array_push($orderPackages,$packages);
        }
        $orderPackageService->更新WAS订单的包裹的重量和体积($orderHeaders,$orderPackages);
        return compact('orders', 'orderHeaders', 'actAllocationDetails', 'oracleDOCOrderDetails');
    }

    public function 过滤已有的WAS订单($orderHeaders,$orders_map = [])
    {

        if(count($orders_map) == 0){
            $orderNos = array_keys($orderHeaders);
            $orders = Order::query()->whereIn('code', $orderNos)->get();
            foreach ($orders as $order) {
                $orders_map[$order['code']] = $order;
            }
        }
        if(count($orders_map) !== 0){
            foreach ($orders_map as $key=>$orderHeader) {
                unset($orderHeaders[$key]);
            }
        }
        return $orderHeaders;
    }

    public function filterNoCorrespondingOrder($orderHeaders_map,$orders_map = []){
        if(count($orders_map) == 0){
            $orderNos = array_keys($orderHeaders_map);
            $orders = Order::query()->whereIn('code', $orderNos)->get();
            foreach ($orders as $order) {
                $orders_map[$order['code']] = $order;
            }
        }
        if(count($orders_map) !== 0){
            foreach ($orderHeaders_map as $key=>$orderHeader) {
                if(!($orders_map[$key] ?? false)){
                    unset($orderHeaders_map[$key]);
                }
            }
        }
        return $orderHeaders_map;

    }

    private function 根据WMS订单信息返回创建WAS订单需要信息($orderHeaders)
    {
        /** @var ShopService $shopService */
        $shopService = app('shopService');
        /** @var LogisticService $logisticService */
        $logisticService = app('logisticService');
        /** @var OwnerService $ownerService */
        $ownerService = app('ownerService');
        $owners = $ownerService->获取订单跟踪的货主();    // 监听的货主
        $shops = $shopService->根据追踪的WMS订单返回商铺($orderHeaders);     // 商铺
        $logistics = $logisticService->getWASLogisticsByWMSOrderHeaders($orderHeaders); // 承运商
        return compact('owners', 'shops', 'logistics');
    }

    public function 根据WMS订单创建WAS订单($orderHeaders, $owners, $shops, $logistics)
    {
        if(!$orderHeaders){return [];}
        $fillables = [];
        $owners_map = [];
        $shop_map = [];
        $logistics_map = [];
        foreach ($owners as $owner) {
            $key = 'code='.$owner->code;
            $owners_map[$key] = $owner;
        }

        foreach ($shops as $shop) {
            $key = 'name='.$shop->name.' owner_id='.$shop->owner_id;
            $shop_map[$key] =  $shop;
        }

        foreach ($logistics as $logistic) {
            $key = 'code='.$logistic->code;
            $logistics_map[$key] =  $logistic;
        }

        $logistic_change = Logistic::query()->where('name','自提')->first();
        $owner_change = Owner::query()->where('name','安桥主品')->first();
        $logistic_an_jie = Logistic::query()->where('name','新杰物流')->first();

        // XXX 循环 300 个元素 3s-5s
        foreach ($orderHeaders as $orderHeader) {
            $owner_key = 'code='.$orderHeader->customerid;
            $owner = $owners_map[$owner_key] ?? '';

            $shop_key = 'name='.$orderHeader->issuepartyname.' owner_id='.$owner->owner_id ?? '';
            $shop = $shop_map[$shop_key] ?? '';

            $logistic_key = 'code='. $orderHeader->userdefine1;
            $logistic = $logistics_map[$logistic_key] ?? '';

            // change logistic to 新杰物流
            // $logistic 自提 and $owner 安桥主品
            if($logistic_change->id == $logistic->id ??'' && $owner_change->id == $owner->id ?? ''){
                $logistic = $logistic_an_jie;
            }

            $fillables[] = [
                'code' => $orderHeader->orderno ,
                'owner_id' => $owner->id ?? '',
                'wms_status' => $orderHeader->oracleBASCode_codename_c,
                'created_at' => $orderHeader->addtime,
                'logistic_id' => $logistic->id ?? '',
                'shop_id' => $shop->id ?? '',
                'consignee_name' => $orderHeader->c_contact,
                'consignee_phone' => $orderHeader->c_tel2,
                'province' => $orderHeader->c_province,
                'city' => $orderHeader->c_city,
                'district' => $orderHeader->c_district,
                'address' => $orderHeader->c_address1,
                'client_code' => $orderHeader->soreference1,
                'wms_edittime' => $orderHeader->edittime,
            ];

        }
        try {
            if (count($fillables) > 0) {
                Order::query()->insert($fillables);
                LogService::log(__METHOD__, __FUNCTION__, '批量创建Order' . count($fillables) . json_encode($fillables));
            }
        } catch (\Exception $e) {
            LogService::log(__METHOD__, __FUNCTION__, $e->getMessage(), $e->getTraceAsString() . json_encode($fillables));
        } finally {
            unset($owners_map,$shop_map,$logistics_map,$fillables);
        }
    }

    // XXX 业务逻辑待修改完善
    public function 将WMS订单对应的WAS订单信息更新($orderHeaders,$ordersList)
    {
        /** @var CommodityService $commodityService */
        $commodityService = app('commodityService');
        /** @var OrderPackageService $orderPackageService */
        $orderPackageService = app('orderPackageService');
        /** @var OrderPackageCommoditiesService $orderPackageCommoditiesService */
        $orderPackageCommoditiesService = app('orderPackageCommoditiesService');
        if (count($orderHeaders) == 0) {
            return [];
        }
        $commodities = $commodityService->getWASCommoditiesByWMSOrderHeaders($orderHeaders);     // 商品信息

        $this->更新WMS订单对应的的WAS订单($orderHeaders, $ordersList);

        $orderPackageService->更新WAS订单快递单号信息($orderHeaders, $ordersList);

        $orderPackageCommoditiesService->更新WAS订单快递打包商品信息($orderHeaders, $ordersList, $commodities);

        $orderNos = data_get($ordersList,'*.code');
/*        $orderPackages = OrderPackage::query()
            ->with('commodities','order')
            ->whereHas('order', function ($query) use ($orderNos) {
                $query->whereIn('code', $orderNos);
            })
            ->get();*/
        $orders = Order::query()
            ->with('packages.commodities')
            ->whereIn('code',$orderNos)
            ->get();
        $orderPackages = [];
        foreach ($orders as $order) {
            $packages =  $order['packages'] ?? null;
            $orderPackages[] =  $packages;
        }
        unset($orderNos);
        $orderPackageService->更新WAS订单的包裹的重量和体积($orderHeaders,$orderPackages);    // 同步包裹重量和体积
    }

    public function  更新WMS订单对应的的WAS订单($orderHeaders, $orders)
    {
        $multipleData = [[
            'id', 'wms_status', 'consignee_name', 'consignee_phone', 'province', 'city', 'district', 'wms_edittime', 'address', 'client_code'
        ]];
        // XXX 循环 300个元素 3s - 5s
            /** @var Order $order */
        foreach ($orders as $key=>$order) {
            $orderHeader = $orderHeaders[$key] ?? false;
            if ($orderHeader) {

                if($order->wms_status != $orderHeader->oracleBASCode_codename_c ||
                    $order->consignee_name != $orderHeader->c_contact ||
                    $order->consignee_phone != $orderHeader->c_tel2 ||
                    $order->province != $orderHeader->c_province ||
                    $order->city != $orderHeader->c_city ||
                    $order->district != $orderHeader->c_district ||
                    $order->wms_edittime != $orderHeader->edittime ||
                    $order->address != $orderHeader->c_address1 ||
                    $order->client_code != $orderHeader->soreference1 ){

                    $multipleData[] =[
                        'id' =>$order->id,
                        'wms_status' => $orderHeader->oracleBASCode_codename_c,
                        'consignee_name' => $orderHeader->c_contact,
                        'consignee_phone' => $orderHeader->c_tel2,
                        'province' => $orderHeader->c_province,
                        'city' => $orderHeader->c_city,
                        'district' => $orderHeader->c_district,
                        'wms_edittime' => $orderHeader->edittime,
                        'address' => $orderHeader->c_address1,
                        'client_code' => $orderHeader->soreference1,
                    ];
                }
            }
        }

        if(count($multipleData)  > 1){
            try {
                /** @var BatchUpdateService $batchUpdateService */
                $batchUpdateService = app('batchUpdateService');
                $batchUpdateService->batchUpdate('orders', $multipleData);
                LogService::log(__METHOD__,__FUNCTION__,'批量更新'.count($multipleData).json_encode($multipleData));
            } catch (\Exception $e) {
                LogService::log(__METHOD__,__FUNCTION__,'批量更新失败'.json_encode($multipleData).$e->getMessage(),$e->getTraceAsString());
            } finally {
                unset($multipleData);
            }
        }
    }

    public function first(array $params)
    {
        $order = Order::query();
        foreach ($params as $column => $value) {
            $order->where($column, $value);
        }
        return $order->first();
    }

    public function logisticNumberCreateOrder($logistic_number)
    {
        /**
         * @var OracleActAllocationDetailService
         * @var OracleActAllocationDetails $actAllocationDetail
         */
        $actAllocationDetail = app('oracleActAllocationDetailService')->first(['picktotraceid' => $logistic_number]);
        if ($actAllocationDetail) {
            $order = $this->first(['code' => $actAllocationDetail->orderno]);
            if ($order) return $order;

            $actAllocationDetail->load(['oracleDocOrderHeader' => function ($query) {
                $query->with('oracleBASCode');
            }]);
            $orderHeader = $actAllocationDetail->oracleDocOrderHeader ?? null;
        } else {
            $orderHeader = app('oracleDocOrderHeaderService')->first(['soreference5' => $logistic_number]);
        }

        if ($orderHeader == null) return null;

        /** @var OwnerService */
        $owner = app('ownerService')->first(['code' => $orderHeader->customerid]);
        if (!$owner) {
            $orderHeader->load('oracleBASCustomer');
            $customer_name = $orderHeader->oracleBASCustomer ? $orderHeader->oracleBASCustomer->descr_c : null;
            /** @var OwnerService */
            if ($customer_name) $owner = app('ownerService')->create(['code' => $orderHeader->customerid, 'name' => $customer_name]);
        }

        $shop = null;
        $shop_name = $orderHeader->issuepartyname;
        if ($shop_name && $owner) {
            /** @var ShopService */
            $shop = app('shopService')->firstOrCreate(['name' => $shop_name, 'owner_id' => $owner->id]);
        }
        $logistics = app('logisticService')->firstOrCreate(['code' => $orderHeader->userdefine1],
            ['code' => $orderHeader->userdefine1, 'name' => $orderHeader->carriername]);
        return Order::query()->create([
            'code' => $orderHeader->orderno,
            'owner_id' => $owner ? $owner->id : null,
            'wms_status' => $orderHeader->oracleBASCode ? $orderHeader->oracleBASCode->codename_c : null,
            'created_at' => $orderHeader->addtime,
            'logistic_id' => $logistics ? $logistics->id : null,
            'shop_id' => $shop ? $shop->id : null,
            'consignee_name' => $orderHeader->c_contact,
            'consignee_phone' => $orderHeader->c_tel2,
            'province' => $orderHeader->c_province,
            'city' => $orderHeader->c_city,
            'district' => $orderHeader->c_district,
            'address' => $orderHeader->c_address1,
            'client_code' => $orderHeader->soreference1,
        ]);
    }


    public function createByWmsOrder($orderHeaders)
    {
        if($orderHeaders->isEmpty())return null;
        /** @var OwnerService $ownerService */
        /** @var LogisticService $logisticService */
        /** @var ShopService $shopService */
        /** @var OrderPackageService $orderPackageService */
        $ownerService = app(OwnerService::class);
        $logisticService = app(LogisticService::class);
        $shopService = app(ShopService::class);
        $orderPackageService = app(OrderPackageService::class);

        $orderHeaders_map = [];$owners_code_map = [];$logistics_code_map = [];$shop_name_map=[]; $owners_id_map = [];

        $owners = $ownerService->getByWmsOrders($orderHeaders);
        $logistics = $logisticService->getByWmsOrders($orderHeaders);
        $shops = $shopService->getByWmsOrders($orderHeaders);

        foreach ($orderHeaders as $orderHeader) {
            $orderHeaders_map[$orderHeader->orderno] = $orderHeader;
        }
        foreach ($owners as $owner) {
            $owners_code_map[$owner->code] = $owner;
            $owners_id_map[$owner->id] = $owner;
        }
        foreach ($logistics as $logistic) {
            $logistics_code_map[$logistic->code] = $logistic;
        }

        foreach ($shops as $shop) {
            $owner_code = $owners_id_map[$shop->owner_id] ? $owners_id_map[$shop->owner_id]['code'] : '';
            $key = ' owner_code='.$owner_code.' name='.$shop->name;
            $shop_name_map[$key] = $shop;
        }

//        $this->filterExitingOrder($orderHeaders_map);
        $insert_params = $this->getParamsByOrderHeader($orderHeaders_map,$owners_code_map,$logistics_code_map,$shop_name_map);
        $this->create($insert_params);

        $orderPackageService->createByWmsOrder($orderHeaders_map);
        $order_nos = array_keys($orderHeaders_map);
        unset($orderHeaders_map,$orders_code_map,$shop_name_map,$logistics_code_map,$owners_code_map);
        return Order::query()->whereIn('code',$order_nos)->get();
    }

    public function filterExitingOrder(&$orderHeaders_map){
        $order_map = [];
        $order_nos = array_keys($orderHeaders_map);
        $orders = Order::query()->whereIn('code',$order_nos)->get();

        foreach ($orders as $order) {
            $key = $order->code;
            $order_map[$key] = $order;
        }
        $orderHeaders_map = array_diff_key($orderHeaders_map,$order_map);
    }

    public function getParamsByOrderHeader($orderHeaders_map,$owners_code_map,$logistics_code_map,$shop_name_map){
        $params = [];
        // 自提 转 新杰物流
        $orders = Order::query()->whereIn('code',data_get($orderHeaders_map,'*.orderno'))->get();
        $order_code_map = [];
        foreach ($orders as $order) {
            $order_code_map[$order->code]= $order;
        }
        $logistic_XJWL = Logistic::query()->where('code' ,'XJWL')->first();
        foreach ($orderHeaders_map as $orderHeader) {
            if($order_code_map[$orderHeader->orderno] ?? false)continue;
            $owner = $owners_code_map[$orderHeader->customerid] ?? null;
            $logistic =  $logistics_code_map[$orderHeader->userdefine1] ?? null;
            $shop = $shop_name_map[' _owner_code'.$orderHeader->customerid.' name='.$orderHeader->issuepartyname] ?? '';
            // 自提 转 新杰物流
            if($logistic!= null && $logistic->name == '自提'){
                $logistic = $logistic_XJWL;
            }

            $params[] = [
                'code' => $orderHeader->orderno,
                'owner_id' => $owner->id ?? null,
                'wms_status' => $orderHeader->oracleBASCode ? $orderHeader->oracleBASCode->codename_c : null,
                'created_at' => $orderHeader->addtime,
                'logistic_id' => $logistic->id ?? null,
                'shop_id' => $shop->id ?? null,
                'consignee_name' => $orderHeader->c_contact,
                'consignee_phone' => $orderHeader->c_tel2,
                'province' => $orderHeader->c_province,
                'city' => $orderHeader->c_city,
                'district' => $orderHeader->c_district,
                'address' => $orderHeader->c_address1,
                'client_code' => $orderHeader->soreference1,
                'wms_edittime' =>$orderHeader->edittime
            ];
        }
        return $params;
    }

    public function create(array $params){
        if(count($params) == 0) return [];
        try {
            $this->insert($params);
            LogService::log(__METHOD__,__FUNCTION__,'批量创建 order '.count($params).json_encode($params));
        } catch (\Exception $e) {
            LogService::log(__METHOD__,__FUNCTION__,'批量创建 order '.json_encode($params).'||'.$e->getMessage().'||'.$e->getTraceAsString());
        } finally{
            return Order::query()->whereIn('code',data_get($params,'*.code'))->get();
        }
    }

    public function updateByWmsOrders($orderHeaders)
    {
        /** @var OrderPackageService $orderPackageService */
        $orderPackageService = app(OrderPackageService::class);
        if(!$orderHeaders || $orderHeaders->count() == 0){return null;}
        $order_nos = data_get($orderHeaders,'*.orderno');
        $orders = Order::query()->whereIn('code',$order_nos)->get();
        $order_code_map = [];
        foreach ($orders as $order) {
            $order_code_map[$order->code] = $order;
        }

        $owners = app(OwnerService::class)->getByWmsOrders($orderHeaders);
        $owner_code_map = [];
        foreach ($owners as $owner) {
            $owner_code_map[$owner->code] = $owner;
        }
        $logistics = app(LogisticService::class)->getByWmsOrders($orderHeaders);
        $logistics_code_map = [];
        foreach ($logistics as $logistic) {
            $logistics_code_map[$logistic->code] = $logistic;
        }

        $shops = app(ShopService::class)->getByWmsOrders($orderHeaders);
        $shops_owner_id_name_map = [];
        foreach ($shops as  $shop){
            $key = ' owner_id='.$shop->owner_id.' name='.$shop->name;
            $shops_owner_id_name_map[$key] = $shop;
        }
        $updateParams = [[
            'id','owner_id','wms_status','logistic_id','shop_id','consignee_name','consignee_phone','province','city','district','address','client_code','updated_at','wms_edittime'
        ]];
        $updated_at = Carbon::now()->toDateTimeString();
        // 新杰物流
        $logistic_XJWL = Logistic::query()->where('code','XJWL')->first();
        foreach ($orderHeaders as $orderHeader) {
            $order_no = $orderHeader->orderno;
            $order = $order_code_map[$order_no] ?? false;
            if(!$order){continue;}
            $logistic = $logistics_code_map[$orderHeader->userdefine1] ?? '';
            // 自提 转 新杰物流
            if($logistic != '' && $logistic->name == '自提'){
                $logistic = $logistic_XJWL;
            }
            $owner = $owner_code_map[$orderHeader->customerid] ?? '';
            $shop = $shops_owner_id_name_map[' owner_id='.$owner->id.' name='.$orderHeader->issuepartyname] ?? '';

            $shop_id = $shop['id'] ?? '';
            $logistic_id = $logistic->id ?? '';
            $owner_id = $owner['id'] ?? '';

            if($order->wms_status != $orderHeader->oracleBASCode_codename_c ||
                $order->logistic_id != $logistic_id ||
                $order->shop_id != $shop_id ||
                $order->owner_id != $owner_id ||
                $order->consignee_name != $orderHeader->c_contact ||
                $order->consignee_phone != $orderHeader->c_tel2 ||
                $order->province != $orderHeader->c_province ||
                $order->city != $orderHeader->c_city ||
                $order->district != $orderHeader->c_district ||
                $order->address != $orderHeader->c_address1 ||
                $order->client_code != $orderHeader->soreference1 ||
                $order->wms_edittime != $orderHeader->edittime){
                $updateParams[] = [
                    'id'=>$order->id,
                    'owner_id' => $owner->id ?? null,
                    'wms_status' => $orderHeader->oracleBASCode_codename_c ?? '',
                    'logistic_id' => $logistic_id,
                    'shop_id' => $shop_id,
                    'consignee_name' => $orderHeader->c_contact,
                    'consignee_phone' => $orderHeader->c_tel2,
                    'province' => $orderHeader->c_province,
                    'city' => $orderHeader->c_city,
                    'district' => $orderHeader->c_district,
                    'address' => $orderHeader->c_address1,
                    'client_code' => $orderHeader->soreference1,
                    'updated_at' =>$updated_at,
                    'wms_edittime' =>$orderHeader->edittime];
            }
        }
        if(count($updateParams) > 0){
            $this->batchUpdate($updateParams);
        }
        $this->createByWmsOrder($orderHeaders);
        $orderPackageService->updateOrderPackageByWmsOrder($orderHeaders);
        unset($updateParams,$orderHeaders);
    }

    public function getByWmsOrders($orderHeaders){
        if(!$orderHeaders){return null;}
        $order_nos = data_get($orderHeaders,'*.orderno');
        return Order::query()->whereIn('code',$order_nos)->get();
    }

}