baoshi-was/app/Imports/UpdatePickZone.php

<?php

namespace App\Imports;

use App\OracleDOCOrderDetail;
use App\Services\LogService;
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["生产日期"] = formatExcelDate($item["生产日期"]);
            if ($item["失效日期"]) $item["失效日期"] = formatExcelDate($item["失效日期"]);

            if (!$item["订单编号"]){
                $errors[] = "第“" . ($index + 2) . "”行订单编号为空";
                continue;
            }
            if (!$item["商品条码"]){
                $errors[] = "第“" . ($index + 2) . "”行商品条码为空";
                continue;
            }
            if (!$item["生产日期"] && !$item["失效日期"]){
                $errors[] = "第“" . ($index + 2) . "”行不存在日期";
                continue;
            }
            $detail = OracleDOCOrderDetail::query()->select("customerid","sku")
                ->where("orderno",$item["订单编号"])
                ->whereHas("sku",function ($query)use($item){
                    /** @var Builder $query */
                    $query->where("alternate_sku1",$item["商品条码"]);
                })->first();
            if (!$detail){
                $errors[] = "第“" . ($index + 2) . "”行未知订单商品";
                continue;
            }
            $sql = "select INV_LOT_LOC_ID.LOCATIONID,INV_LOT_LOC_ID.LOTNUM,BAS_LOCATION.PICKZONE,INV_LOT_LOC_ID.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
                    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";

            $lot = DB::connection("oracle")->select(DB::raw($sql),$bindings);
            if (!$lot){
                $errors[] = "第“" . ($index + 2) . "”行未找到库位";
                continue;
            }
            $result = null;
            if (count($lot)>1){
                $min = null;
                $max = null;
                $map = [];
                foreach ($lot as $i => $l){
                    $qty = (int)$l->quty;
                    $map[$qty] = $i;
                    if ($qty >= (int)$item["数量"] && $qty<=$max)$max = $qty;
                    if ($qty < (int)$item["数量"] && $qty>=$min)$min = $qty;
                }
                if ($max !== null)$result = $lot[$map[$max]];
                else $result = $lot[$map[$min]];
            }
            if (count($lot) == 1)$result = $lot[0];
            if ($result){dd($result);
                try{
                    $detail->update([
                        "lotnum" => $result->lotnum,
                        "pickzone" => $result->pickzone,
                        "kitreferenceno" => '0',
                        "d_edi_09" => '0',
                        "d_edi_10" => '0',
                    ]);
                    DB::connection("oracle")->commit();
                    LogService::log(__METHOD__,"SUCCESS-指定效期分配修改库位",json_encode($detail)." | ".json_encode($result));
                    $order = app("OrderService")->first(["code"=>$item["订单编号"]]);
                    if (!$order){
                        $errors[] = "第“" . ($index + 2) . "”行已成功修改FLUX库位但在本地未找到订单";
                        continue;
                    }
                    $barcode = app("CommodityBarcodeService")->first(["code"=>$item["商品条码"]]);
                    if (!$barcode){
                        $errors[] = "第“" . ($index + 2) . "”行已成功修改FLUX库位但在本地未找到条码";
                        continue;
                    }
                    $model = app("OrderCommodityAssignService")->create([
                        "order_id"      =>  1,//$order->id,
                        "commodity_id"  =>  1,//$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[] = "第“" . ($index + 2) . "”行未找到可分配库位";
        }
        $assigns = app("OrderCommodityAssignService")->get(["id"=>$ids]);
        $models = [];
        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"=>$errors]);
    }
}