baoshi-was/app/Imports/UpdatePickZone.php

<?php

namespace App\Imports;

use App\OracleDOCOrderDetail;
use App\Services\LogService;
use Carbon\Carbon;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Concerns\ToCollection;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Maatwebsite\Excel\Imports\HeadingRowFormatter;

HeadingRowFormatter::default('none');
class UpdatePickZone implements ToCollection,WithHeadingRow
{
    /**
    * @param Collection $collection
    * @return bool
    * @throws
    */
    public function collection(Collection $collection)
    {
        $row = $collection->first();
        $headers = ["订单编号","商品条码","数量","生产日期","失效日期"];
        foreach ($headers as $header){
            if (!isset($row[$header])){
                Cache::put("commodityAssign",["success"=>false, "data"=>"表头不存在“".$header."”"],86400);
                return false;
            }
        }

        $errors = [];
        $ids = [];
        foreach ($collection as $index=>$item){
            if (!$item["订单编号"] && !$item["生产日期"] && !$item["失效日期"] && !$item["商品条码"] && !$item["数量"])continue;
            if (!$item["订单编号"]){
                $errors[] = "第“" . ($index + 2) . "”行订单编号为空";
                continue;
            }
            if (!$item["商品条码"]){
                $errors[] = "“{$item['订单编号']}”商品条码为空";
                continue;
            }

            if ($item["生产日期"]){
                if (is_numeric($item["生产日期"]))$item["生产日期"] = formatExcelDate($item["生产日期"]);
                else{
                    try{
                        $item["生产日期"]=Carbon::parse($item["生产日期"])->format('Y-m-d');
                    }catch (\Exception $e){
                        $errors[] = "“{$item["订单编号"]}”生产日期无法识别";
                        continue;
                    }
                }
            }
            if ($item["失效日期"]){
                if (is_numeric($item["失效日期"]))$item["失效日期"] = formatExcelDate($item["失效日期"]);
                else{
                    try{
                        $item["失效日期"]=Carbon::parse($item["失效日期"])->format('Y-m-d');
                    }catch (\Exception $e){
                        $errors[] = "“{$item['订单编号']}”失效日期无法识别";
                        continue;
                    }
                }
            }

            if (!$item["生产日期"] && !$item["失效日期"]){
                $errors[] = "“{$item['订单编号']}”不存在日期";
                continue;
            }
            $detail = OracleDOCOrderDetail::query()->select("DOC_ORDER_DETAILS.customerid","DOC_ORDER_DETAILS.sku","DOC_ORDER_DETAILS.orderno","DOC_ORDER_DETAILS.orderlineno","sostatus")
                ->where("DOC_ORDER_DETAILS.orderno",$item["订单编号"])
                ->whereHas("sku",function ($query)use($item){
                    /** @var Builder $query */
                    $query->where("alternate_sku1",$item["商品条码"]);
                })->leftJoin("DOC_ORDER_HEADER","DOC_ORDER_DETAILS.ORDERNO","=","DOC_ORDER_HEADER.ORDERNO")->first();
            if (!$detail){
                $errors[] = "“{$item['订单编号']}”未知订单商品";
                continue;
            }
            if ($detail->sostatus != '00'){
                $errors[] = "“{$item['订单编号']}”订单状态不为创建订单";
                continue;
            }
            $sql = "select BAS_ZONE.DESCR,INV_LOT_LOC_ID.LOCATIONID,INV_LOT_LOC_ID.LOTNUM,BAS_LOCATION.PICKZONE,(INV_LOT_LOC_ID.QTY-INV_LOT_LOC_ID.QTYALLOCATED-QTYONHOLD-QTYRPOUT-QTYMVOUT) AS qty from INV_LOT_ATT LEFT JOIN
                        INV_LOT_LOC_ID ON INV_LOT_ATT.LOTNUM = INV_LOT_LOC_ID.LOTNUM LEFT JOIN BAS_LOCATION ON INV_LOT_LOC_ID.LOCATIONID = BAS_LOCATION.LOCATIONID
                    LEFT JOIN BAS_ZONE ON BAS_LOCATION.PICKZONE = BAS_ZONE.ZONE 
                    where INV_LOT_ATT.LOTNUM in (select LOTNUM from INV_LOT_LOC_ID where CUSTOMERID = ? AND SKU = ? GROUP BY LOTNUM)";
            $bindings = [$detail->customerid,$detail->sku];
            if ($item["生产日期"]){
                $sql .= " AND LOTATT01 = ?";
                $bindings[] = $item["生产日期"];
            }else $sql .= " AND LOTATT01 IS NULL";
            if ($item["失效日期"]){
                $sql .= " AND LOTATT02 = ?";
                $bindings[] = $item["失效日期"];
            }else $sql .= " AND LOTATT02 IS NULL";

            $lots = DB::connection("oracle")->select(DB::raw($sql),$bindings);
            if (!$lots){
                $errors[] = "“{$item['订单编号']}”未找到库位";
                continue;
            }
            $result = null;
            if (count($lots) == 1)$result = $lots[0];
            else {
                $zone = ["存储","拣货","虚拟"];
                $zones = [[],[],[]];
                foreach ($lots as $lot){
                    foreach ($zone as $ind=>$str){
                        if (mb_strpos($lot->descr,$str) !== false){
                            $zones[$ind][] = $lot;
                            break;
                        }
                    }
                }
                foreach ($zones as $zl){
                    $result = $this->matchingMax($zl,$item["数量"]);
                    if ($result)break;
                }
                if (!$result){
                    foreach ($zones as $zl){
                        $result = $this->matchingMin($zl,$item["数量"]);
                        if ($result)break;
                    }
                }
            }
            if ($result){
                try{
                    $sql = "UPDATE DOC_ORDER_DETAILS SET LOTNUM = ?,PICKZONE = ?,KITREFERENCENO = ?,D_EDI_09 = ?,D_EDI_10 = ? WHERE ORDERNO = ? AND ORDERLINENO = ?";
                    DB::connection("oracle")->update(DB::raw($sql),[$result->lotnum,$result->pickzone,'0','0','0',$detail->orderno,$detail->orderlineno]);
                    LogService::log(__METHOD__,"SUCCESS-指定效期分配修改库位",json_encode($detail)." | ".json_encode($result));
                    $order = app("OrderService")->first(["code"=>$item["订单编号"]]);
                    if (!$order){
                        $errors[] = "“{$item['订单编号']}”已成功修改FLUX库位但在本地未找到订单";
                        continue;
                    }
                    $barcode = app("CommodityBarcodeService")->first(["code"=>$item["商品条码"]]);
                    if (!$barcode){
                        $errors[] = "“{$item['订单编号']}”已成功修改FLUX库位但在本地未找到条码";
                        continue;
                    }
                    $model = app("OrderCommodityAssignService")->create([
                        "order_id"      =>  $order->id,
                        "commodity_id"  =>  $barcode->commodity_id,
                        "amount"        =>  $item["数量"],
                        "produced_at"   =>  $item["生产日期"],
                        "valid_at"      =>  $item["失效日期"],
                        "batch_number"  =>  $result->lotnum,
                        "location"      =>  $result->locationid,
                        "region"        =>  $result->pickzone,
                        "user_id"       =>  Auth::id(),
                    ]);
                    LogService::log(__METHOD__,"SUCCESS-生成配置记录",json_encode($model));
                    $ids[] = $model->id;
                }catch (\Exception $e){
                    LogService::log(__METHOD__,"ERROR-指定效期分配修改库位",json_encode($detail)." | ".json_encode($result));
                }
            }else $errors[] = "“{$item['订单编号']}”未找到可分配库位";
        }
        $models = [];
        if ($ids){
            $assigns = app("OrderCommodityAssignService")->get(["id"=>$ids]);
            foreach ($assigns as $assign){
                $models[] = [
                    "orderNumber"   => $assign->order ? $assign->order->code : '',
                    "barcode"       => $assign->commodity ? $assign->commodity->barcode : '',
                    "amount"        => $assign->amount,
                    "producedAt"    => $assign->produced_at,
                    "validAt"       => $assign->valid_at,
                    "batchNumber"   => $assign->batch_number,
                    "location"      => $assign->location,
                    "region"        => $assign->region,
                    "createdAt"     => $assign->created_at,
                    "userName"      => $assign->user ? $assign->user->name : 'system',
                ];
            }
        }
        Cache::put("commodityAssign",["success"=>true,"data"=>$models,"errors"=>array_unique($errors)]);
    }

    private function matchingMax($lots, $amount)
    {
        if (!$lots) return null;
        $max = null;
        $map = [];
        foreach ($lots as $i => $l){
            $qty = (int)$l->qty;
            $map[$qty] = $i;
            if (($qty >= $amount && $qty<=$max) || $max===null)$max = $qty;
        }
        if ($max !== null && $max>=$amount)return $lots[$map[$max]];
        return null;
    }
    private function matchingMin($lots, $amount)
    {
        if (!$lots) return null;
        $min = null;
        $map = [];
        foreach ($lots as $i => $l){
            $qty = (int)$l->qty;
            $map[$qty] = $i;
            if (($qty < $amount && $qty>=$min) || $min===null)$min = $qty;
        }
        if ($min !== null)return $lots[$map[$min]];
        return null;
    }
}