baoshi-was/app/Services/OrderTrackingService.php

<?php

namespace App\Services;

use App\Exports\Export;
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;
use Maatwebsite\Excel\Facades\Excel;

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 ? $user->getPermittingOwnerIdsAttribute() : [];
        if ($params['owner_id'] ?? false) {
            $ownerids = explode(",", $params['owner_id']);
            $owner_ids = array_intersect($owner_ids, $ownerids);
        }
        $query = OrderTracking::query()->with(['owner', 'commodities' => function ($query) {
            $query->with(['commodity', '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'],
        ];
        $param = [
            'start_at' => $params['start_at'] ?? '',
            'end_at' => $params['end_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 ($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.created_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 .= 'order_trackings.pallet_total,order_trackings.planning_sent_at,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();
    }
    /**
     * @param $startData
     */
    public function trackingWmsOrder($startData){
        $this->trackingWmsOrderOnCreate($startData);
        $this->trackingWmsOrderOnEdit($startData);
    }

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

    public function trackingWmsOrderOnEdit($startData){
        /** @var OrderService $orderService */
        $orderService = app(OrderService::class);
        /** @var OracleDOCOrderHeader $orderHeaders */
        $orderHeaders = app(OracleDOCOrderHeaderService::class)->getWmsOrderOnStartDateEdit($startData);
        $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);
    }

    public function updateOrderTracingsByOrderIds($orderId,$param,$value)
    {
        $bool = null;
        try {
            $update = [$param => $value];
            if($param == 'planning_sent_at'){
                $update= [$param => $value, 'signed_at' => $value,'is_arrival'=>'是','is_on_duty_shift'=>'是'];
            }
            $bool = OrderTracking::query()->whereHas('commodities.package.order', function ($query) use ($orderId) {
                $query->where('id', $orderId);
            })->update($update);
            return['success'=> $bool > 0];
        } catch (\Exception $e) {
            LogService::log(__METHOD__,__FUNCTION__,'修改追踪件'.json_encode($orderId,$param,$value).$e->getMessage().$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')
            ->whereHas('package.order',function($query) use ($order_nos){
                $query->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;}
            $client = null;
            $order_remark = null;
            if ($order_header['notes'] ?? false) {
                $notes = $order_header->notes;
                if (strpos($notes, '[')) {
                    $arr= str_split($notes,strpos($notes,'['));
                    $client = $arr[0] ?? '';
                    $order_remark = str_replace(['[',']'],'', $arr[1] ?? '');
                }
            }
            $web_order_number = null;
            if(preg_match('/^O[\d]/',$order_header['soreference1'] ?? '') > 0){
                $web_order_number = $order_header['soreference1'];
            }

            $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);
            $params[] =[
                'order_package_commodity_id' => $item->id,
                'owner_id' => $order->owner_id,
                'order_client_code' =>$order->client_code,
                'client' => $client,
                'created_at' => $order->created_at,
                'order_remark' => $order_remark,
                'pick_up_at' => $order_header->lastshipmenttime, // 提货时间,
                'web_order_number' => $web_order_number,
                'gross_weight'=> $gross_weight,
                'bulk'=> $bulk
            ];
        }

        try {
            if (count($params) > 0) {
                $this->insert($params);
                LogService::log(__METHOD__, __FUNCTION__, '批量添加 orderTracking' . json_encode($params),null);
            }
        } catch (\Exception $e) {
            LogService::log(__METHOD__, __FUNCTION__, '批量添加 orderTracking error' . json_encode($params).$e->getMessage().$e->getTraceAsString(),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')
            ->whereHas('package.order',function($query) use ($order_nos){
                $query->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'];
        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);
                    $pararm = $this->getParamsByOrderHeaderAndOrder($orderHeader,$order);
                    $pararm['order_package_commodity_id'] =$item->id;
                    $pararm['order_client_code'] = $order->client_code;
                    $pararm['gross_weight'] = $gross_weight;
                    $pararm['bulk'] = $bulk;
                    $insert_params[] =$pararm;
                }
            }
        }
        if(count($insert_params) > 0){
            try {
                $this->insert($insert_params);
                LogService::log(__METHOD__,__FUNCTION__,'批量添加 OrderTracking '.count($insert_params).json_encode($insert_params));
            } catch (\Exception $e) {
                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 = null;
        $order_remark = null;
        if ($orderHeader['notes'] ?? false) {
            $notes = $orderHeader->notes;
            if (strpos($notes, '[')) {
                $index = strpos($notes, '[');
                $client = substr($notes,0,$index);
                $order_remark = substr($notes,$index);
                $order_remark = ltrim($order_remark,'[');
                $order_remark = rtrim($order_remark,']');
            }
        }
        $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::create($planning_sent_at)->addDays($logisticTiming->days_at_working);
             }
        }
        return [
            'owner_id' => $order->owner_id,
            'client' => $client,
            'created_at' => $order->created_at,
            'order_remark' => $order_remark,
            'pick_up_at' => $orderHeader->lastshipmenttime, // 提货时间
            'web_order_number' =>$web_order_number,
            'planning_sent_at' => $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 OrderTracking::query()->with('commodities.package.order')
            ->whereHas('commodities.package.order',function($query)use($orderNos){
                $query->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();
            LogService::log(__METHOD__,__FUNCTION__,'删除订单取消的OrderTracking '.$orderTracking->count().json_encode($orderTracking),null);
        } catch (\Exception $e) {
            LogService::log(__METHOD__,__FUNCTION__,'删除订单取消的OrderTracking error'.$orderTracking->count().json_encode($orderTracking).$e->getMessage().$e->getTraceAsString(),null);
        }
    }

}