baoshi-was/app/Services/OrderTrackingService.php

<?php

namespace App\Services;

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(['logistic','owner','uploadFile', 'commodities' => function ($query) {
            $query->with(['commodity'=>function($query){
                $query->with('barcodes');
            }, 'package.order']);
        },])->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->where('order_client_code','like',$params['client_code']);
//            $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'].'%'); // web+订单好

        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){
        $orderHeaders = app(OracleDOCOrderHeaderService::class)->getWmsOrderOnStartDateCreate($startData);
        app(OrderService::class)->syncOrderByWMSOrderHeaders($orderHeaders);
        app("OrderCommodityService")->syncOrderCommodity($orderHeaders);
        app('OrderPackageService')->syncOrderPackage($orderHeaders);
        app("OrderPackageCommoditiesService")->syncOrderPackageCommodities($orderHeaders);
        $this->createByWmsOrderHeader($orderHeaders);
    }

    public function trackingWmsOrderOnEdit($startData)
    {
        $orderHeaders = app(OracleDOCOrderHeaderService::class)->getWmsOrderOnStartDateEdit($startData);
        app(OrderService::class)->syncOrderByWMSOrderHeaders($orderHeaders);
        app("OrderCommodityService")->syncOrderCommodity($orderHeaders);
        app('OrderPackageService')->syncOrderPackage($orderHeaders);
        app("OrderPackageCommoditiesService")->syncOrderPackageCommodities($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];
            }
            $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);

        $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;}
            $params[] = $this->getCreateParams($order_header,$order,$item,$BasSKUs_code_sku_map);
        }
        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;
        }

        $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','web_order_number','order_client_code'];
        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->getCreateParams($orderHeader,$order,$order_package_commodity,$BasSKUs_code_sku_map);
                $params['id'] = $order_tracking->id;
                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 != $params['gross_weight'] ||
                    $order_tracking->bulk != $params['bulk'] ||
                    $order_tracking->client != $params['client']
                    || $order_tracking->order_client_code != $params['order_client_code']
                ){
                    $params['updated_at'] = Carbon::now();
                    $update_params[] = $params;
                }
            }
        }
        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;
                    $insert_params[] = $this->getCreateParams($orderHeader,$order,$item,$BasSKUs_code_sku_map);
                }
            }
        }
        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 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')
            ->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);
        }
    }

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

    public function getCreateParams($orderHeader,$order,$orderPackageCommodity,$BasSKUs_code_sku_map)
    {
        $params = [];
        /**
         * @var LogisticService $logisticService
         * @var DataHandlerService $dataService
         */
        $logisticService = app('LogisticService');
        $dataService = app('DataHandlerService');
        $params['order_package_commodity_id'] = $orderPackageCommodity->id;
        if(preg_match('/^O[\d]/',$orderHeader['soreference1'] ?? '') > 0){  // web_order_number  web+订单号
            $params['web_order_number'] =$orderHeader['soreference1'];
        }else $params['web_order_number']= null;

        $params['owner_id'] = $order->owner_id ?? null; // owner_id

        if($orderHeader['userdefine1'] =='ZT'){  // logistic_id
            $logistic = $logisticService->getLogisticByCode('XJWL'); // 自提转新杰物流
            $params['logistic_id'] = $logistic->id ?? null;
        }else{
            $logistic = $logisticService->getLogisticByCode($orderHeader['userdefine1']);
            $params['logistic_id'] = $logistic->id ?? null;
        }

        $order_remark = $orderHeader['notes'] ?? '';        // 订单备注
        if(mb_stristr($order_remark,'天猫') && mb_stristr($order_remark,'赠品')){ // client and order_remark 商铺 和 订单备注
            $params['client'] = '天猫(赠品)';
            $params['order_remark'] = $order_remark;
        }else{
            if(mb_stristr($order_remark,'[')){
                $items = [];
                preg_match_all('/^(.*?)(\\[)(.*?)(\\])(.*?)$/u',$order_remark,$items);
                $params['client'] = $items[1][0];
                $params['order_remark'] = $items[3][0];
            }else{
                $params['client'] = $orderHeader['issuepartyname'];
                $params['order_remark'] = $order_remark;
            }
        }
        if(mb_stristr($orderHeader['issuepartyname'],'天猫') && $params['client'] !== '天猫(赠品)') $params['client'] = '天猫';

        if($params['client'] == '天猫') // order_client_code 订单号  店铺包含天猫
            $params['order_client_code'] = $this->get_d_edit_03($orderHeader,$orderPackageCommodity);
        else
            $params['order_client_code'] = $order->client_code;

        $params['sale'] = $orderHeader['issuepartyname'] ?? null;   // sale 销售

        $params['pick_up_at'] = $orderHeader->lastshipmenttime ?? null;  // pick_up_at 提货时间

        if($params['pick_up_at']){  //  planning_sent_at   预期到货时间
            $logisticTiming = app(LogisticTimingService::class)->findByParams($orderHeader['c_city']??'',$orderHeader['c_province']??'',$logistic['id']);
            if($logisticTiming) $params['planning_sent_at'] = (string)Carbon::parse((string)$params['pick_up_at'])->addDays($logisticTiming->days_at_working); // 预期到货时间
            else  $params['planning_sent_at'] = null;
        }else {
            $params['planning_sent_at'] = null;
        }

        $params['is_on_duty_shift'] = isset($params['planning_sent_at']) ? '是' : null;  //is_on_duty_shift 是否卡班

        $basSkU = $dataService->getKeyValue(['customerid'=>$orderHeader->customerid,'sku'=>$orderPackageCommodity->commodity->sku],$BasSKUs_code_sku_map);
        $params['gross_weight'] = round($basSkU->grossweight * $orderPackageCommodity->amount, 2) ?? null;  // 重量
        $params['bulk'] = round($basSkU->cube * $orderPackageCommodity->amount, 2) ?? null; // 体积
        $params['created_at'] = Carbon::now();  // 创建时间
        return $params;
    }
}