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\OrderIssue;
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 App\Warehouse;
use Carbon\Carbon;
use Illuminate\Database\Eloquent\Collection;
use Illuminate\Support\Facades\App;
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."' OR c_tel1 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_values(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_values(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.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.C_Tel1,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.C_Tel1,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.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.C_Tel1,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=[];
        $orderNos = "(''";
        $picktotraceids=[];
        /*
         * 第二种扁平化方式(节省空间)： 使用快慢指针，因拿到数据正常为顺序数据,自下标1开始与上一条对比orderno唯一标识，不同则更新慢指针下标，
         *  相同则为指定下标map类型数组追加一个键值对属性来记录同类数量,如N条，每次追加属性时先看属性是否存在，存在+1
         * */
        foreach ($orders as $index=>$order){
            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]];
            $orderNos .= ",'".$order->orderno."'";
        }
        $orderNos .= ")";
        $allocations = DB::connection("oracle")->select(DB::raw("SELECT DROPID,ORDERNO FROM ACT_ALLOCATION_DETAILS WHERE orderno IN ".$orderNos." GROUP BY (DROPID,ORDERNO)"));
        foreach ($allocations as $allocation){
            if (isset($picktotraceids[$allocation->orderno])) $picktotraceids[$allocation->orderno][] = $allocation->dropid;
            else $picktotraceids[$allocation->orderno] = [$allocation->dropid];
        }
        $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' => empty($orderHeadAttr['c_tel2'])?$orderHeadAttr['c_tel1']:$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);
        }else{
            $this->updateOrderByOrderHeader($order,$orderHeader);
        }
        $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 first(array $params)
    {
        $order = Order::query();
        foreach ($params as $column => $value) {
            $order->where($column, $value);
        }
        return $order->first();
    }

    public function logisticNumberFirstOrCreateOrder($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;
        $order = $this->first(['code'=>$orderHeader->orderno]);
        if ($order) return $order;
        /** @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' => empty($orderHeader->c_tel2)?$orderHeader->c_tel1:$orderHeader->c_tel2,
            'province' => $orderHeader->c_province,
            'city' => $orderHeader->c_city,
            'district' => $orderHeader->c_district,
            'address' => $orderHeader->c_address1,
            'client_code' => $orderHeader->soreference1,
        ]);
    }

    /**
     * @param $orderHeaders
     * @return \Illuminate\Database\Eloquent\Builder[]|Collection|null
     */
    public function createByWmsOrder($orderHeaders)
    {
        if($orderHeaders->isEmpty())return null;
        /**
         * @var OwnerService $ownerService
         * @var WarehouseService $warehouseService
         * @var LogisticService $logisticService
         * @var ShopService $shopService
         * @var OrderPackageService $orderPackageService
         */
        $warehouseService =app(WarehouseService::class);
        $ownerService = app(OwnerService::class);
        $logisticService = app(LogisticService::class);
        $shopService = app(ShopService::class);
        $orderPackageService = app(OrderPackageService::class);

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

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


        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;
        }

        foreach ($warehouses as $warehouse) {
            $warehouses_code_map[$warehouse->code] = $warehouse;
        }

        $insert_params = $this->getParamsByOrderHeader($orderHeaders,$owners_code_map,$logistics_code_map,$shop_name_map,$warehouses_code_map);

        if(count($insert_params)> 0){
            $this->create($insert_params);
        }
        $orderPackageService->createByWmsOrder($orderHeaders);
        $order_nos = data_get($orderHeaders,'*.orderno');
        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 getParamsByOrderHeader($orderHeaders_map,$owners_code_map,$logistics_code_map,$shop_name_map,$warehouses_code_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;
        }
        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;
            $warehouse=$warehouses_code_map[$orderHeader->warehouseid] ?? null;
            $key = ' owner_code='.$orderHeader->customerid.' name='.$orderHeader->issuepartyname ?? '';
            $shop = $shop_name_map[$key] ?? '';
            $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' => empty($orderHeader->c_tel2)?$orderHeader->c_tel1:$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,
                'warehouse_id' =>$warehouse->id??''
            ];
        }
        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)
    {
        if(!$orderHeaders){return null;}
        /** @var DataHandlerService $dataHandlerService */
        $dataHandlerService =  app(DataHandlerService::class);
        /** @var OrderPackageService $orderPackageService */
        $orderPackageService = app(OrderPackageService::class);
        if(!$orderHeaders || $orderHeaders->count() == 0){return null;}
        $orders = $this->getByWmsOrders($orderHeaders);
        $orders_code_map =  $dataHandlerService->dataHeader(['code'],$orders);
        $owners = app(OwnerService::class)->getByWmsOrders($orderHeaders);
        $owner_code_map = $dataHandlerService->dataHeader(['code'],$owners);

        $logistics = app(LogisticService::class)->getByWmsOrders($orderHeaders);
        $logistics_code_map = $dataHandlerService->dataHeader(['code'],$logistics);

        $shops = app(ShopService::class)->getByWmsOrders($orderHeaders);
        $shops_map = $dataHandlerService->dataHeader(['owner_id','name'],$shops);

        $warehouses = app(WarehouseService::class)->getByWmsOrders($orderHeaders);
        $warehouses_map = $dataHandlerService->dataHeader(['code'],$warehouses);
        $updateParams = [[
            'id','owner_id','wms_status','logistic_id','shop_id','consignee_name','consignee_phone','province','city','district','address','client_code','updated_at','wms_edittime','warehouse_id'
        ]];
        $updated_at = Carbon::now()->toDateTimeString();
        foreach ($orderHeaders as $orderHeader) {
            $order =$dataHandlerService->getKeyValue(['code'=>$orderHeader->orderno],$orders_code_map);
            if(!$order){continue;}
            $logistic = $dataHandlerService->getKeyValue(['code'=>$orderHeader->userdefine1] ,$logistics_code_map);
            $owner = $dataHandlerService->getKeyValue(['code'=>$orderHeader->customerid] ,$owner_code_map);
            $shop = $dataHandlerService->getKeyValue(['owner_id'=>$owner->id ?? '','name'=> $orderHeader->issuepartyname] ,$shops_map);
            $warehouse=$dataHandlerService->getKeyValue(['code'=>$orderHeader->warehouseid],$warehouses_map);
            $shop_id = $shop->id ?? null;
            $logistic_id = $logistic->id ?? null;
            $owner_id = $owner->id ?? null;
            $warehouse_id=$warehouse->id??null;
            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 != empty($orderHeader->c_tel2)?$orderHeader->c_tel1:$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||
                $order->warehouse_id!=$warehouse_id){
                $updateParams[] = [
                    'id'=>$order->id,
                    'owner_id' => $owner_id,
                    'wms_status' => $orderHeader->oracleBASCode_codename_c ?? '',
                    'logistic_id' => $logistic_id,
                    'shop_id' => $shop_id,
                    'consignee_name' => $orderHeader->c_contact,
                    'consignee_phone' => empty($orderHeader->c_tel2)?$orderHeader->c_tel1:$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,
                    'warehouse_id'=>$warehouse_id];
            }
        }
        if(count($updateParams) > 1){
            $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();
    }

    public function 返回有问题件的订单号($orders)
    {
        if(!isset($orders)){return [];}
        $orderNo =  data_get($orders,'*.orderno');
        $orderIssues = OrderIssue::query()->with('order')
            ->whereHas('order',function($query)use($orderNo){
                $query->whereIn('code',$orderNo);
            })->get();

        return $orderIssues->map(function($orderIssue){
            return $orderIssue->order->code;
        });
    }

    public function getParamByOrderHeader($orderHeader)
    {
        $logistic = Logistic::query()->where('code',$orderHeader->userdefine1)->first();
        $owner = Owner::query()->where('code',$orderHeader->customerid ?? '')->first();
        $shop = Shop::query()->where('name',$orderHeader->issuepartyname)->where('owner_id',$owner->id)->first();
        $warehouse = app('WarehouseService')->firstOrCreate(["code"=>$orderHeader->warehouseid],["code"=>$orderHeader->warehouseid,"name"=>$orderHeader->warehouseid]);
        return [
            'code' =>$orderHeader->orderno,
            'owner_id' =>$owner->id,
            'shop_id' =>$shop->id,
            'logistic_id' => $logistic->id ??'',
            'consignee_name' =>  $orderHeader->c_contact,
            'consignee_phone' =>  empty($orderHeader->c_tel2)?$orderHeader->c_tel1:$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,
            'wms_status' => $orderHeader->oracleBASCode_codename_c ?? '',
            'updated_at' => (string)Carbon::now()->format('Y-m-d H:i:s'),
            'created_at' => $orderHeader->addtime,
            'warehouse_id' => $warehouse->id ?? ''
        ];
    }

    /**
     * @param App/Order $order
     * @param App/OracleDOCOrderHeader $orderHeader
     */
    public function updateOrderByOrderHeader($order,$orderHeader)
    {
        if(!$orderHeader){return ;}
        if($orderHeader->orderno == $order->code){
            return ;
        }else if($orderHeader->soreference1 != $order->client_code){
            return ;
        }
        $params = $this->getParamByOrderHeader($orderHeader);

        $data = [
                'code'=>$order->code ?? $params['code'],
                'owner_id'=>$order->code ?? $params['owner_id'],
                'shop_id'=>$order->code ?? $params['shop_id'],
                'logistic_id'=>$order->code ?? $params['logistic_id'],
                'consignee_name'=>$order->code ?? $params['consignee_name'],
                'province'=>$order->code ?? $params['province'],
                'city'=>$order->code ?? $params['city'],
                'district'=>$order->code ?? $params['district'],
                'address'=>$order->code ?? $params['address'],
                'wms_edittime'=>$order->code ?? $params['wms_edittime'],
                'wms_status'=>$order->code ?? $params['wms_status'],
        ];
        Order::query()->where('id',$order->id)->update($data);
    }

    public function getOrderByLogisticNumber($logisticNumber)
    {
        $order = Order::query()->with('packages.commodities.commodity')
            ->whereHas('packages',function($query)use($logisticNumber){
                $query->where('logistic_number',$logisticNumber);
            })->first();
        if($order){
            $orderHeader = OracleDOCOrderHeader::query()->where('soreference1',$order->client_no)->first();
            $this->updateOrderByOrderHeader($order,$orderHeader);
            return $order;
        }
        $orderHeaders = OracleDOCOrderHeader::query()->with(['oracleDOCOrderDetails', 'actAllocationDetails','oracleBASCode'])
            ->whereHas('actAllocationDetails',function($query)use($logisticNumber){
                $query->where('picktotraceid',$logisticNumber);
            })->get();
        app(CommodityService::class)->getByWmsOrders($orderHeaders);
        $orders =$this->createByWmsOrder($orderHeaders);
        return  $orders ? $orders->first() : null ;
    }
}