baoshi-was/app/Services/OrderTrackingService.php

<?php

namespace App\Services;

use App\Exports\Export;
use App\OracleBasSKU;
use App\OracleDOCOrderHeader;
use App\Order;
use App\OrderPackageCommodities;
use App\OrderTracking;
use App\Services\common\BatchUpdateService;
use App\Services\common\DataHandlerService;
use App\Services\common\QueryService;
use Illuminate\Support\Carbon;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\Auth;

class OrderTrackingService
{
    public function insert($params){
        return OrderTracking::query()->insert($params);
    }

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

    public function getQuery($params)
    {
        $user = Auth::user();
        $owner_ids = $user ? app('UserService')->getPermittingOwnerIds($user) : [];
        if ($params['owner_id'] ?? false) {
            $ownerids = explode(",", $params['owner_id']);
            $owner_ids = array_intersect($owner_ids, $ownerids);
        }
        $query = OrderTracking::query()->with(['owner','uploadFile', 'commodities' => function ($query) {
            $query->with(['commodity'=>function($query){
                $query->with('barcodes');
            }, 'package.order.logistic']);
        },])->whereHas('owner', function ($query) use ($owner_ids) {
            $query->whereIn('id', $owner_ids); //货主
        });

        $queryParam = [
            'start_at' => ['alias' => 'pick_up_at', 'startDate' => ' 00:00:00'],
            'end_at' => ['alias' => 'pick_up_at', 'endDate' => ' 23:59:59'],
            'start_planning_sent_at' => ['alias' => 'planning_sent_at', 'startDate' => ' 00:00:00'],
            'end_planning_sent_at' => ['alias' => 'planning_sent_at', 'endDate' => ' 23:59:59'],
        ];

        $param = [
            'start_at' => $params['start_at'] ?? '',
            'end_at' => $params['end_at'] ?? '',
            'start_planning_sent_at' => $params['start_planning_sent_at'] ?? '',
            'end_planning_sent_at' => $params['end_planning_sent_at'] ?? '',
        ];

        $query = app(QueryService::class)->query($param, $query, $queryParam);
        return $query;
    }

    public function getConditionQuery($params)
    {
        $query = $this->getQuery($params);

        if (isset($params['client_code'])|| isset($params['logistic_id'])){     // client_code logistic
            $query->whereHas('commodities.package.order', function ($query) use ($params) {
                if($params['client_code'] ?? false){
                    $query->where('client_code','like', $params['client_code']);
                }
                if(isset($params['logistic_id']) ){
                    $query->where('logistic_id',$params['logistic_id']);
                }
            });
        }

        if ($params['client'] ?? false)  // 客户
            $query->where('client', 'like', $params['client']);

        if(!empty($params['web_order_number']))
            $query->where('web_order_number', 'like', $params['web_order_number'].'%');

        if ($params['sku'] ?? false) {
            $query->whereHas('commodities.commodity', function ($query) use ($params) {
                $query->where('sku', $params['sku']);
            });
        }  // sku

        if ($params['logistic_number'] ?? false) {
            $query->whereHas('commodities.package', function ($query) use ($params) {
                $query->where('logistic_number', $params['logistic_number']);
            });
        }// 运输单号（快递单号）

        if(isset($params['remark'])){
            $query->where('remark','like','%'.$params['remark'].'%');
            if(isset($params['remark_addtime'])){
                $query->where('created_at','>',Carbon::now()->subDays($params['remark_addtime']));
            }else{
                $query->where('created_at','>=',Carbon::now()->subDays(15));
            }
        }

        if ($params['ids'] ?? false) {
            $ids = explode(',', $params['ids']);
            $query->whereIn('order_trackings.id', $ids);
        }
        return $query;
    }

    public function paginate($params)
    {
        return $this->getConditionQuery($params)
            ->orderByDesc('order_trackings.pick_up_at')
            ->orderBy('order_trackings.order_client_code')
            ->paginate($params['paginate'] ?? 50);
    }

    public function getSql($params)
    {
        if($params['data'] ?? false){
            $params['ids'] = $params['data'];
        }
        $order_tracking_colnums = "case order_trackings.order_client_code when  order_trackings.web_order_number then '' when order_trackings.order_client_code then  order_trackings.order_client_code end order_client_code,";
        $order_tracking_colnums .= 'order_trackings.web_order_number,order_trackings.pick_up_at,order_trackings.sale,order_trackings.client,order_trackings.order_remark,order_trackings.gross_weight,order_trackings.bulk,';
        $order_tracking_colnums .= "case order_trackings.planning_sent_at when '0000-00-00 00:00:00' then null else order_trackings.planning_sent_at end planning_sent_at,";
        $order_tracking_colnums .= 'order_trackings.pallet_total,order_trackings.is_on_duty_shift,order_trackings.is_arrival,order_trackings.signed_at,order_trackings.receive_bill_status,order_trackings.remark';
        return  $this->getConditionQuery($params)
            ->orderBy('order_trackings.order_client_code')
            ->orderByDesc('order_trackings.pick_up_at')
            ->selectRaw($order_tracking_colnums)
            ->leftJoin('order_package_commodities','order_trackings.order_package_commodity_id','order_package_commodities.id')
                ->selectRaw('order_package_commodities.amount amount')
            ->leftJoin('order_packages','order_package_commodities.order_package_id','order_packages.id')
                ->selectRaw('order_packages.logistic_number logistic_number')
            ->leftJoin('orders','order_packages.order_id','orders.id')
                ->selectRaw('orders.city city')
            ->leftJoin('logistics','orders.logistic_id','logistics.id')
                ->selectRaw("case logistics.code when 'ZT' then '新杰物流' else  logistics.name end  logistic_name")
            ->leftJoin('commodities','order_package_commodities.commodity_id','commodities.id')
                ->selectRaw('commodities.sku sku,commodities.name sku_name')
            ->leftJoin('owners','orders.owner_id','owners.id')
               ->selectRaw('owners.name owners_name')
            ->sql();
    }

    public function getOrderTrackings($params)
    {
        if($params['data'] ?? false){
            $params['ids'] = $params['data'];
        }
        return  $this->getConditionQuery($params)
            ->orderByDesc('order_trackings.pick_up_at')->orderBy('order_trackings.order_client_code')->get();
    }

    /**
     * @param $startData
     */
    public function trackingWmsOrder($startData){
        $this->trackingWmsOrderOnCreate($startData);
        $this->trackingWmsOrderOnEdit($startData);
    }

    public function trackingWmsOrderOnCreate($startData){
        /** @var OrderService $orderSerivce */
        $orderService = app(OrderService::class);
        /** @var Collection $orderHeaders */
        $orderHeaders = app(OracleDOCOrderHeaderService::class)->getWmsOrderOnStartDateCreate($startData);
        $this->customizedOperation($orderHeaders);
        $orderService->createByWmsOrder($orderHeaders);
        $this->createByWmsOrderHeader($orderHeaders);
    }

    public function trackingWmsOrderOnEdit($startData){
        /** @var OrderService $orderService */
        $orderService = app(OrderService::class);
        /** @var Collection $orderHeaders */
        $orderHeaders = app(OracleDOCOrderHeaderService::class)->getWmsOrderOnStartDateEdit($startData);
        $this->customizedOperation($orderHeaders);
        $orderService->updateByWmsOrders($orderHeaders);
        $this->updateByWmsOrderHeaders($orderHeaders);
    }

    public function updateOrderTracingAndSyc($id,$param,$value){
        $order_tracking = OrderTracking::query()->with('commodities.package.order')->where('id', $id)->first();
        $order_id = $order_tracking->commodities->package->order->id ?? false;
        if(!isset($order_id)){ return ['success'=> false];}
        return $this->updateOrderTracingsByOrderIds($order_id,$param,$value);
    }

    /**
     * @param Collection $orderHeaders
     */
    public function customizedOperation(&$orderHeaders) // 对追踪件的特定操作
    {
        $orderHeaders->each(function ($orderHeader) {
            if($orderHeader->userdefine1 ==='ZT'){
                $orderHeader->userdefine1 = 'XJWL';
            }
        });
    }

    public function updateOrderTracingsByOrderIds($orderId,$param,$value)
    {
        $bool = null;
        try {
            $update = [$param => $value];
            if($param == 'planning_sent_at'){
                $update= [$param => $value, 'signed_at' => $value];
            }
            $bool = OrderTracking::query()->whereHas('commodities.package.order', function ($query) use ($orderId) {
                $query->where('id', $orderId);
            })->update($update);
            return['success'=> $bool > 0];
        } catch (\Exception $e) {
            app('LogService')->log(__METHOD__,__FUNCTION__,'修改追踪件'.json_encode($orderId,$param,$value).json_encode($e->getMessage()).json_encode($e->getTraceAsString()));
            return['success'=> false,'fail_info'=>$e->getMessage()];
        }
    }

    public function createByWmsOrderHeader($orderHeaders)
    {
        if(!$orderHeaders){return;}
        /** @var DataHandlerService $dataService */
        $dataService = app(DataHandlerService::class);

        $owners = app(OwnerService::class)->getByWmsOrders($orderHeaders);
        $owner_id_map = $basSKUs_data = $dataService->dataHeader(['id'],$owners);

        $BasSKUs = app(OracleBasSkuService::class)->获取BasSKu_通过WMSOrderHeaders($orderHeaders);
        $BasSKUs_code_sku_map = $dataService->dataHeader(['customerid','sku'],$BasSKUs);
        $order_nos = data_get($orderHeaders,'*.orderno');

        $order_package_commodities = OrderPackageCommodities::query()->with(['package.order','commodity'])
            ->whereIn('order_package_id',function($query)use($order_nos){
                $query->from('order_packages')->select('id')->whereIn('order_id',function($query)use($order_nos){
                    $query->from('orders')->select('id')->whereIn('code',$order_nos);
                });
            })
            ->get();

        $order_package_commodities_ids = data_get($order_package_commodities,'*.id');
        $order_trackings = OrderTracking::query()->whereIn('order_package_commodity_id',$order_package_commodities_ids)->get();
        $is_exist_ids = data_get($order_trackings,'*.order_package_commodity_id');

        $order_package_commodities = $order_package_commodities->filter(function($item) use ($is_exist_ids){
            $id = $item->id;
            return !in_array($id,$is_exist_ids);
        });

        if($order_package_commodities->count() == 0){return ;}

        $order_headers_map = [];
        foreach ($orderHeaders as $item) {
            $order_headers_map[$item->orderno] = $item;
        }

        $params = [];
        foreach ($order_package_commodities as $item) {
            $order = $item->package->order;
            if(!$order){continue ;}
            $order_header = $order_headers_map[$order->code];
            if(!$order_header){continue ;}
            if($order_header->sostatus == 90){continue;}

            $web_order_number = null;
            if(preg_match('/^O[\d]/',$order_header['soreference1'] ?? '') > 0){
                $web_order_number = $order_header['soreference1'];
            }
            $param = $this->getParamsByOrderHeaderAndOrder($order_header,$order);
            $owner = $dataService->getKeyValue(['id'=>$order->owner_id],$owner_id_map);
            $basSkU = $dataService->getKeyValue(['customerid'=>$owner->code,'sku'=>$item->commodity->sku],$BasSKUs_code_sku_map);
            $gross_weight = round($basSkU->grossweight * $item->amount, 2);
            $bulk = round($basSkU->cube * $item->amount, 2);

            $order_client_code = $order->client_code;
            if($param['client']=='天猫'){
                $order_client_code = $this->get_d_edit_03($order_header,$item) ;
            }

            $params[] =[
                'order_package_commodity_id' => $item->id,
                'owner_id' => $order->owner_id,
                'order_client_code' =>$order_client_code,
                'sale' => $param['sale'],
                'client' => $param['client'],
                'created_at' => $order->created_at,
                'order_remark' => $param['order_remark'],
                'pick_up_at' => $order_header->lastshipmenttime, // 提货时间,
                'web_order_number' => $web_order_number,
                'gross_weight'=> $gross_weight,
                'bulk'=> $bulk,
                'is_on_duty_shift'=> $param['is_on_duty_shift'],
                'planning_sent_at' => $param['planning_sent_at'] ?? ''
            ];
        }
        try {
            if (count($params) > 0) {
                $this->insert($params);
                app('LogService')->log(__METHOD__, __FUNCTION__, '批量添加 orderTracking' . json_encode($params),null);
            }
        } catch (\Exception $e) {
            app('LogService')->log(__METHOD__, __FUNCTION__, '批量添加 orderTracking error' . json_encode($params).$e->getMessage().$e->getTraceAsString(),null);
        }
    }

    public function get_d_edit_03($orderHeader,$orderPackageCommodity){
        $sku_line_no = null;
        foreach ($orderHeader->actAllocationDetails as $actAllocationDetail) {
            if($actAllocationDetail->picktotraceid == $orderPackageCommodity->package->logistic_number
                && $orderPackageCommodity->commodity->sku ==  $actAllocationDetail->sku){
                $sku_line_no = $actAllocationDetail->skulineno;
                break;
            }
        }
        if($sku_line_no==null)return null;
        foreach ($orderHeader->oracleDOCOrderDetails as $oracleDOCOrderDetail) {
            if($oracleDOCOrderDetail->orderlineno == $sku_line_no) {
                return $oracleDOCOrderDetail['d_edi_03'];
            }
        }
        return null;
    }


    public function updateByWmsOrderHeaders($orderHeaders)
    {
        if(!$orderHeaders){return ;}
        /** @var DataHandlerService $dataService */
        $dataService = app(DataHandlerService::class);
        $order_nos = data_get($orderHeaders,'*.orderno');

        $order_package_commodities = OrderPackageCommodities::query()
            ->with(['package.order','commodity'])
            ->whereIn('order_package_id',function($query)use($order_nos){
                $query->from('order_packages')->select('id')->whereIn('order_id',function($query)use($order_nos){
                    $query->from('orders')->select('id')->whereIn('code',$order_nos);
                });
            })->get();


        $orderTrackings = OrderTracking::query()->whereIn('order_package_commodity_id',data_get($order_package_commodities,'*.id'))->get();

        $order_tracking_id_map = [];
        foreach ($orderTrackings as $orderTracking) {
            $order_tracking_id_map[$orderTracking->order_package_commodity_id] = $orderTracking;
        }
        $order_package_commodity_order_code_map = [];
        $order_code_map = [];
        foreach ($order_package_commodities as $order_package_commodity) {
            $order =$order_package_commodity->package->order;
            if(!$order){continue;}
            $order_code_map[$order->code] = $order;
            $order_package_commodity_order_code_map[$order->code][] = $order_package_commodity;
        }
        $owners = app(OwnerService::class)->getByWmsOrders($orderHeaders);
        $owner_code_map = $basSKUs_data = $dataService->dataHeader(['code'],$owners);

        $BasSKUs = app(OracleBasSkuService::class)->获取BasSKu_通过WMSOrderHeaders($orderHeaders);
        $BasSKUs_code_sku_map = $dataService->dataHeader(['customerid','sku'],$BasSKUs);

        $update_params = [];
        $update_params[0] =['id','client','pick_up_at','order_remark','gross_weight','bulk','planning_sent_at'];
        foreach ($orderHeaders as $orderHeader) {
            if($orderHeader->sostatus == '90'){continue;}
            $order_package_commodity_list = $order_package_commodity_order_code_map[$orderHeader->orderno] ?? false;

            if(!$order_package_commodity_list){continue;}
            $order = $order_code_map[$orderHeader->orderno];

            foreach ($order_package_commodity_list as $order_package_commodity) {
                $order_tracking = $order_tracking_id_map[$order_package_commodity->id]??false;
                if(!$order_tracking){continue;}

                $params = $this->getParamsByOrderHeaderAndOrder($orderHeader,$order);
                $owner =  $dataService->getKeyValue(['code'=>$orderHeader->customerid],$owner_code_map);
                $basSku = $dataService->getKeyValue(['customerid'=>$owner->code,'sku'=>$order_package_commodity->commodity->sku],$BasSKUs_code_sku_map);
                $gross_weight = round($basSku->grossweight * $order_package_commodity->amount,2) ;
                $bulk = round($basSku->cube * $order_package_commodity->amount,2) ;
                if($order_tracking->owner_id != $params['owner_id'] ||
                    $order_tracking->client !=$params['client'] ||
                    $order_tracking->order_remark != $params['order_remark'] ||
                    $order_tracking->pick_up_at != $params['pick_up_at'] ||
                    $order_tracking->gross_weight != $gross_weight ||
                    $order_tracking->bulk != $bulk){
                    $update_params[] = [
                        'id' => $order_tracking->id,
                        'client' => $params['client'],
                        'pick_up_at' => $params['pick_up_at'],
                        'order_remark' => $params['order_remark'],
                        'web_order_number' => $params['web_order_number'],
                        'gross_weight'  => $gross_weight,
                        'bulk' => $bulk,
                        'planning_sent_at' => $params['planning_sent_at']
                    ];
                }
            }
        }
        if(count($update_params) > 1){
            $this->batchUpdate($update_params);
        }
        $insert_params = [];
        foreach ($orderHeaders as $orderHeader) {
            $order_package_commodity_list=  $order_package_commodity_order_code_map[$orderHeader->orderno] ?? false;
            if(!$order_package_commodity_list)continue;
            foreach ($order_package_commodity_list as $item) {
                $order_tracking =  $order_tracking_id_map[$item->id] ?? false;
                if(!$order_tracking){
                    $order = $order_code_map[$orderHeader->orderno] ?? false;
                    if(!$order) continue;
                    $basSkU = $dataService->getKeyValue(['customerid'=>$orderHeader->customerid,'sku'=>$item->commodity->sku],$BasSKUs_code_sku_map);
                    $gross_weight = round($basSkU->grossweight * $item->amount, 2);
                    $bulk = round($basSkU->cube * $item->amount, 2);
                    $param = $this->getParamsByOrderHeaderAndOrder($orderHeader,$order);
                    $param['order_package_commodity_id'] =$item->id;
                    $param['order_client_code'] = $order->client_code;
                    $param['gross_weight'] = $gross_weight;
                    $param['bulk'] = $bulk;
                    $insert_params[] =$param;
                }
            }
        }
        if(count($insert_params) > 0){
            try {
                $this->insert($insert_params);
                app('LogService')->log(__METHOD__,__FUNCTION__,'批量添加 OrderTracking '.count($insert_params).json_encode($insert_params));
            } catch (\Exception $e) {
                app('LogService')->log(__METHOD__,__FUNCTION__,'批量添加 OrderTracking error'.json_encode($insert_params) .'||'.$e->getMessage(),'||'.$e->getTraceAsString());
            }
        }
        $this->删除订单追踪_订单取消($orderHeaders);
    }

    /**
     * @param OracleDOCOrderHeader $orderHeader
     * @param Order $order
     * @return array
     */
    public function getParamsByOrderHeaderAndOrder($orderHeader,$order)
    {
        $client = $orderHeader['issuepartyname'] ?? '';
        $order_remark = $orderHeader['notes'] ?? '';
        $web_order_number = null;
        if(preg_match('/^O[\d]/',$orderHeader['soreference1'] ?? '') > 0){
            $web_order_number = $orderHeader['soreference1'];
        }
        $pick_up_at = $orderHeader->lastshipmenttime;
        $planning_sent_at = null;
        if(isset($pick_up_at)){
             $logisticTiming = app(LogisticTimingService::class)->findByParams($orderHeader['c_city']??'',$orderHeader['c_province']??'',$order->logistic_id);
             if($logisticTiming){
                 $planning_sent_at = Carbon::parse((string)$pick_up_at)->addDays($logisticTiming->days_at_working);
             }
        }
        if(stristr($order_remark,'[')){
            $items = [];
            preg_match_all('/^(.*?)(\\[)(.*?)(\\])(.*?)$/u',$order_remark,$items);
            $client = $items[1][0];
            $order_remark = $items[3][0];
        }
        if(stristr($client,'天猫'))$client='天猫';
        if(stristr($client,'京东'))$client='京东';
        return [
            'owner_id' => $order->owner_id,
            'client' => $client,
            'sale' => $orderHeader['issuepartyname'] ?? '',
            'created_at' => $order->created_at,
            'order_remark' => $order_remark,
            'pick_up_at' => $orderHeader['lastshipmenttime'] ?? '', // 提货时间
            'web_order_number' =>$web_order_number,
            'is_on_duty_shift'=>$planning_sent_at?'是':null,
            'planning_sent_at' => (string)$planning_sent_at
        ];
    }

    /**
     * @param Collection $orderHeaders
     */
    public function 删除订单追踪_订单取消($orderHeaders)
    {
        $cancelledOrderHeaders = $orderHeaders->where('sostatus','90');
        $orderTracking = $this->getOrderTracingByWmsOrder($cancelledOrderHeaders);
        $this->deleteOrderTracings($orderTracking);
    }

    /**
     * @param Collection $orderHeaders
     * @return \Illuminate\Database\Eloquent\Builder[]|\Illuminate\Database\Eloquent\Collection|Collection|\Tightenco\Collect\Support\Collection
     */
    public function getOrderTracingByWmsOrder($orderHeaders)
    {
        if($orderHeaders->count() == 0){return  collect();}
        $orderNos = data_get($orderHeaders,'*.orderno');
        return OrderPackageCommodities::query()
            ->with('commodities.package.order')
            ->whereIn('order_package_commodity_id',function($query)use($orderNos){
                $query->from('order_package_commodities')->select('id')->whereIn('order_package_id',function($query)use($orderNos) {
                    $query->from('order_packages')->select('id')->whereIn('order_id', function ($query) use ($orderNos) {
                        $query->from('orders')->select('id')->whereIn('code', $orderNos);
                    });
                });
            })->get();
    }

    /**
     * @param Collection $orderTracking
     */
    public function deleteOrderTracings($orderTracking)
    {
        if($orderTracking->count() == 0){return;}
        try {
            OrderTracking::query()->whereIn('id', data_get($orderTracking,'*.id'))->delete();
            app('LogService')->log(__METHOD__,__FUNCTION__,'删除订单取消的OrderTracking '.$orderTracking->count().json_encode($orderTracking),null);
        } catch (\Exception $e) {
            app('LogService')->log(__METHOD__,__FUNCTION__,'删除订单取消的OrderTracking error'.$orderTracking->count().json_encode($orderTracking).$e->getMessage().$e->getTraceAsString(),null);
        }
    }

    /**
     * @param OracleBasSKU $basSkU
     * @param OrderPackageCommodities $orderPackageCommodity
     * @return array
     */
    public function getGrossWeightAndBulk($basSkU,$orderPackageCommodity)
    {
        return [
            'gross_weight' =>round($basSkU->grossweight * $orderPackageCommodity->amount, 2),
            'bulk' =>round($basSkU->cube * $orderPackageCommodity->amount, 2),
        ];
    }

    public function fillInOrderTracking($orderTracking = null)
    {
        $orderTrackingIds = [];
        try {
            $date = new Carbon();
            if($orderTracking==null){
                $orderTracking = OrderTracking::query()
                    ->where('created_at', '!=', '0000-00-00 00:00:00')
                    ->where('planning_sent_at', '!=', '0000-00-00 00:00:00')
                    ->whereNull('signed_at')
                    ->where('planning_sent_at','<',$date)
                    ->get();
            }
            if(count($orderTracking)==0)return;
            $update_params = [['id','signed_at']];
            $orderTracking = $orderTracking->where('created_at', '!=', '0000-00-00 00:00:00')
                ->where('planning_sent_at', '!=', '0000-00-00 00:00:00')
                ->whereNull('signed_at')
                ->where('planning_sent_at','<',$date);
            $orderTracking->each(function($item)use(&$update_params){
                $update_params[] = [
                    'id' => $item->id,
                    'signed_at' => $item->planning_sent_at
                ];
            });
            if(count($update_params) > 0) $this->batchUpdate($update_params);
            $orderTrackingIds = data_get($orderTracking, '*.id');
            OrderTracking::query()->whereIn('id', $orderTrackingIds)
                ->whereNull('is_on_duty_shift')
                ->whereNull('is_arrival')
                ->update(['is_on_duty_shift' => '是', 'is_arrival' => '是']);
            app('LogService')->log(__METHOD__,__FUNCTION__,'修改签收日期 是否赶上卡班 到货情况'.json_encode($orderTrackingIds));
        } catch (\Exception $e) {
            app('LogService')->log(__METHOD__,'ERROR '.__FUNCTION__,'修改签收日期 是否赶上卡班 到货情况 ERROR'.json_encode($orderTrackingIds).' || '.json_encode($e->getMessage()).' || '.json_encode($e->getTraceAsString()));
        }
    }


}