baoshi-was/app/Services/StorageService.php

<?php

namespace App\Services;

use App\CommodityMaterialBoxModel;
use App\Station;
use App\StationTask;
use App\StationTaskMaterialBox;
use App\StoreItem;
use App\TaskTransaction;
use App\Traits\ServiceAppAop;
use App\Storage;
use App\ValueStore;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Str;

class StorageService
{
    use ServiceAppAop;
    protected $modelClass=Storage::class;

    /**
     * 缓存架放置记录
     *
     * @param StationTaskMaterialBox|\stdClass $stationTaskMaterialBox
     */
    public function putCacheShelf($stationTaskMaterialBox)
    {
        DB::beginTransaction();
        try{
            $storage = Storage::query()->where("material_box_id",$stationTaskMaterialBox->material_box_id)->lockForUpdate()->first();
            Storage::query()->where("station_id",$stationTaskMaterialBox->station_id)->where("status",0)
                ->whereNotNull("material_box_id")->update(["station_id"=>null]);
            Storage::query()->where("station_id",$stationTaskMaterialBox->station_id)->where("status",0)
                ->whereNull("material_box_id")->delete();
            if ($storage)$storage->update(["station_id"=>$stationTaskMaterialBox->station_id,'status'=>0]);
            else Storage::query()->create([
                "station_id" => $stationTaskMaterialBox->station_id,
                "material_box_id" => $stationTaskMaterialBox->material_box_id,
            ]);
            $stationTaskMaterialBox->loadMissing("station");
            //清理原有任务
            if ($stationTaskMaterialBox->station){
                switch ($stationTaskMaterialBox->station->parent_id){
                    case 6:
                        $this->clearTask([$stationTaskMaterialBox->station->code]);
                        break;
                    case 7:
                        $task = TaskTransaction::query()->with("materialBox")->where("fm_station_id",$stationTaskMaterialBox->station_id)
                            ->where("status",0)->first();
                        if ($task)app("CacheShelfService")->lightUp($stationTaskMaterialBox->station->code,'1','0',[
                            "title"=>$task->materialBox->code ?? '',
                            "detail01"=>"{$task->doc_code}",
                            "detail02"=>"商品条码：{$task->bar_code}",
                            "detail03"=>"上架数量：{$task->amount}",
                        ]);
                        break;
                }
            }
            DB::commit();
        }catch (\Exception $e){
            DB::rollBack();
        }
    }

    /**
     * 释放库位占用
     *
     * @param StationTaskMaterialBox|\stdClass $stationTaskMaterialBox
     */
    public function releaseOccupation($stationTaskMaterialBox)
    {
        if ($stationTaskMaterialBox->station->station_type_id != 5)return;
        $storage = Storage::query()->where("material_box_id",$stationTaskMaterialBox->material_box_id)->first();
        if (!$storage)return;
        $update = [];
        if ($storage->status == 1)$update["status"] = 0;
        if ($storage->station_id)$update["station_id"] = null;
        if ($update)$storage->update($update);
    }

    /**
     * 检查临时事务标记处理一些特殊情况
     *
     * @param StationTaskMaterialBox|\stdClass $stationTaskMaterialBox
     */
    public function checkMark($stationTaskMaterialBox)
    {
        $task = TaskTransaction::query()->where("material_box_id",$stationTaskMaterialBox->material_box_id)
            ->where("status",0)->first();
        if (!$task)return;
        //蓝灯闪烁
        if ($task->type == '入库' && $task->mark == 1)app("CacheShelfService")->lightUp($stationTaskMaterialBox->station->code,'2','2',["title"=>'机器人取箱中，禁止操作',]);
    }

    /**
     * 检查存储 根据事务表做处理
     *
     * @param Station|\stdClass $station
     *
     * @return bool
     *
     * @throws
     */
    public function checkStorage(Station $station)
    {
        $task = TaskTransaction::query()->with("materialBox")->where("fm_station_id",$station->id)
            ->where("status",0)->first();
        if (!$task)return true;
        //建立入库任务,通知入库，完善库存
        if ($task->type == '入库' && $task->mark == 1){
            DB::beginTransaction();
            try{
                //get flux
                $tasks = $this->getFluxTask($task->doc_code,$task->bar_code,$task->amount);
                if (!$tasks)return false;
                $ide = $task->materialBox->code;
                DB::connection("oracle")->beginTransaction();
                try{
                    foreach ($tasks as $t)if (!$this->fluxPA($t,$ide)){
                        DB::connection("oracle")->rollBack();
                        return false;
                    };
                }catch(\Exception $e){
                    DB::connection("oracle")->rollBack();
                    return false;
                }
                $taskMaterialBox = $this->createWarehousingTask($station->id,$task->material_box_id);//建立入库任务
                if (!$this->enterWarehouse($station->id,$task->material_box_id,$task->commodity_id,$task->amount))throw new \Exception("库存异常"); //处理库存
                $task->update([
                    "task_id" => $taskMaterialBox->id,
                    "status" => 1,
                ]);//标记事务完成
                app("ForeignHaiRoboticsService")->putBinToStore_fromCacheShelf($taskMaterialBox,$station); //呼叫机器人入库
                DB::commit();
                DB::connection("oracle")->commit();
                return true;
            }catch(\Exception $e){
                DB::rollBack();
                DB::connection("oracle")->rollBack();
                return false;
            }
        }
        return true;
    }

    /**
     * 建立入库任务
     *
     * @param $stationId
     * @param $boxId
     *
     * @return StationTaskMaterialBox|\stdClass|Model
     */
    public function createWarehousingTask($stationId,$boxId)
    {
        /** @var StationTask|\stdClass $task */
        $task = StationTask::query()->create([
            'status' => "待处理",
            'station_id' => $stationId,
        ]);
        return StationTaskMaterialBox::query()->create([
            'station_id' => $stationId,
            'material_box_id'=>$boxId,
            'status'=>"待处理",
            'type' => '放',
            'station_task_id' => $task->id,
        ]);
    }

    /**
     * 库存入库
     *
     * @param integer $stationId
     * @param integer $boxId
     * @param integer $commodityId
     * @param integer $amount
     * @param integer $modelId
     *
     * @return bool
     */
    public function enterWarehouse($stationId, $boxId, $commodityId, $amount, $modelId = null)
    {
        DB::beginTransaction();
        try{
            $storage = Storage::query()->where("material_box_id",$boxId)->lockForUpdate()->first();
            $obj = [
                "station_id" => $stationId,
                "material_box_id" => $boxId,
                "commodity_id" => $commodityId,
                "amount" => $amount,
                "status" => 1,
            ];
            if ($storage){
                $amountTemp = (int)$storage->amount + (int)$amount;
                $obj["amount"] = DB::raw("amount+{$amount}");
                $storage->update($obj);
                $amount = $amountTemp;
            } else Storage::query()->create($obj);
            if ($commodityId && $modelId){
                //维护料箱最大上限 用于半箱补货
                $model = CommodityMaterialBoxModel::query()->select("maximum")->where("commodity_id",$commodityId)
                    ->where("material_box_model_id",$modelId)->first();
                if (!$model)CommodityMaterialBoxModel::query()->create(["commodity_id"=>$commodityId,"material_box_model_id"=>$modelId,"maximum"=>$amount]);
                if ($model && $model->maximum < $amount)CommodityMaterialBoxModel::query()->select("maximum")->where("commodity_id",$commodityId)
                    ->where("material_box_model_id",$modelId)->update(["maximum"=>$amount]);
            }
            DB::commit();
            LogService::log(__CLASS__,"库存增加",$storage->toJson()."  |  ".json_encode([$stationId, $boxId, $commodityId, $amount, $modelId]));
            return true;
        }catch(\Exception $e){
            DB::rollBack();
            return false;
        }
    }

    /**
     * 获取FLUX上架任务列表
     *
     * @param string $asn
     * @param string $barCode
     * @param int $amount
     *
     * @return array|null
     */
    public function getFluxTask(string $asn,string $barCode,int $amount):array
    {
        $sql = <<<sql
SELECT TSK_TASKLISTS.* FROM DOC_ASN_DETAILS LEFT JOIN BAS_SKU ON DOC_ASN_DETAILS.CUSTOMERID = BAS_SKU.CUSTOMERID AND DOC_ASN_DETAILS.SKU = BAS_SKU.SKU
LEFT JOIN TSK_TASKLISTS ON DOC_ASN_DETAILS.ASNNO = TSK_TASKLISTS.DOCNO AND DOC_ASN_DETAILS.ASNLINENO = TSK_TASKLISTS.DOCLINENO
WHERE ASNNO = ? AND (ALTERNATE_SKU1 = ? OR ALTERNATE_SKU2 = ? OR ALTERNATE_SKU3 = ?) AND RECEIVEDQTY >= ?
  AND TASKPROCESS = '00' AND TASKTYPE = 'PA'
sql;
        $tasks = DB::connection("oracle")->select(DB::raw($sql),[$asn,$barCode,$barCode,$barCode,$amount]);
        if (!$tasks)return [];
        $nums = [];
        $sum = 0;
        $maxIndex = null;
        foreach ($tasks as $i => $task){
            if ((int)$task->fmqty == $amount)return [$task];
            $nums[] = (int)$task->fmqty;
            $sum += (int)$task->fmqty;
            if ((int)$task->fmqty>$amount)$maxIndex = $i;
        }
        if ($sum<$amount)return []; //上架数大于入库数
        $result = $this->getMatch($nums,$amount);
        if (!$result)return $this->splitTask($tasks,$maxIndex,$amount);
        $arr = [];
        foreach ($result as $index)$arr[] = $tasks[$index];
        return $arr;
    }

    /**
     * 拆分任务
     * @param array $tasks
     * @param int|null $maxIndex
     * @param int $amount
     *
     * @return array
     */
    private function splitTask($tasks,$maxIndex,$amount):array
    {
        $result = [];
        if ($maxIndex===null){
            foreach ($tasks as $task){
                if ($amount>(int)$task->fmqty){
                    $result[] = $task;
                    $amount-=(int)$task->fmqty;
                }else $splitTarget = $task;
            }
        }else $splitTarget = $tasks[$maxIndex];
        $result[] = $this->copyTask($splitTarget,$amount);
        return $result;
    }

    /**
     * 值转换
     *
     * @param ?string $val
     *
     * @return ?string
     */
    private function valFormat($val):?string
    {
        if ($val!==null){
            $ret = date("Y-m-d H:i:s",strtotime($val))==$val;
            if ($ret)$val = "to_date('".$val."','yyyy-mm-dd hh24:mi:ss')";
            else $val = "'".$val."'";
        }else $val = "null";
        return $val;
    }
    /**
     * @param \stdClass $task
     * @param int $amount
     *
     * @return \stdClass
     *
     * @throws
     */
    private function copyTask($task,$amount)
    {
        DB::connection("oracle")->beginTransaction();
        try {
            $columns = '';
            $values = '';
            foreach ($task as $key=>$val){
                if (Str::upper($key)=='TASKID_SEQUENCE') {
                    $taskMax = DB::connection("oracle")->selectOne(DB::raw("select MAX(TASKID_SEQUENCE) maxseq from TSK_TASKLISTS where taskid = ?"),[$task->taskid]);
                    $val = $taskMax->maxseq + 1;
                }
                if (Str::upper($key)=='FMQTY' || Str::upper($key)=='FMQTY_EACH'
                    || Str::upper($key)=='PLANTOQTY' || Str::upper($key)=='PLANTOQTY_EACH'){
                    $val -= $amount;
                    $task->$key = $amount;
                }
                $columns .= $key.",";
                $values .= $this->valFormat($val) .",";
            }
            $columns = mb_substr($columns,0,-1);
            $values = mb_substr($values,0,-1);
            $sql = <<<sql
    INSERT INTO TSK_TASKLISTS({$columns}) VALUES({$values})
sql;
            DB::connection("oracle")->insert(DB::raw($sql));
            DB::connection("oracle")->update(DB::raw("UPDATE TSK_TASKLISTS SET FMQTY = ?,FMQTY_EACH = ?,PLANTOQTY=?,PLANTOQTY_EACH=? WHERE TASKID = ? AND TASKID_SEQUENCE = ?"),[
                $amount,$amount,$amount,$amount,$task->taskid,$task->taskid_sequence
            ]);
            DB::connection("oracle")->commit();
        }catch(\Exception $e) {
            DB::connection("oracle")->rollBack();
            throw new \Exception("拆分任务失败：".$e->getMessage());
        }
        return $task;
    }

    /**
     * 获取匹配数字
     *
     * @param Integer[] $nums
     * @param Integer $target
     * @return Integer[]|null
     */
    protected function getMatch(array $nums,int $target) :?array
    {
        $map=[];
        foreach ($nums as $index=>$val){
            $complement=$target-$val;
            if(array_key_exists($complement,$map))return [$map[$complement],$index];
            if ($val==$target)return [$index];
            $map[$val]=$index;
            if ($val<$target){
                $temp = $nums;
                unset($temp[$index]);
                $arr = $this->getMatch($temp,$target-$val);
                if ($arr) {
                    $arr[] = $index;
                    return $arr;
                }
            }
        }
        return null;
    }

    /**
     * 将任务在flux上架
     *
     * @param \stdClass $task
     * @param $ide
     * @return bool
     * @throws \Throwable
     */
    public function fluxPA($task,$ide):bool
    {
        if (!$task->taskid)return false;//ASN单无此入库信息，禁止上架
        $amount = (int)$task->fmqty;
        $db = DB::connection("oracle");
        $db->beginTransaction();
        try {
            $sql = <<<sql
SELECT * FROM inv_lot_loc_id  WHERE lotnum = ? AND traceid = ? AND locationid = ? AND customerid= ?  and sku = ? and qty >= {$amount} FOR UPDATE
sql;
            $inv = $db->selectOne(DB::raw($sql),[$task->fmlotnum,$task->fmid,$task->fmlocation,$task->customerid,$task->sku]);
            if (!$inv)return false;//余量与入库不符

            $inv1 = $db->update(DB::raw("UPDATE inv_lot_loc_id SET qty = qty - ? WHERE LOTNUM = ? AND LOCATIONID = ? AND TRACEID = ? AND traceid != '*'"),[
                $amount,$task->fmlotnum,$task->fmlocation,$task->fmid
            ]);
            $db->update(DB::raw("UPDATE inv_lot_loc_id SET qtypa = qtypa - ? WHERE LOTNUM = ? AND LOCATIONID = ? AND TRACEID = ? AND traceid != '*'"),[
                $amount,$task->plantolotnum,$task->plantolocation,$task->plantoid
            ]);
            if ($inv1!=1){
                $db->rollBack();
                return false;//库存余量错误
            }
            $invHistory = $db->selectOne(DB::raw("SELECT * FROM inv_lot_loc_id WHERE lotnum = ? AND locationid = ? AND customerid = ? AND sku = ? AND traceid = '*' FOR UPDATE"),[
                $inv->lotnum,$ide,$inv->customerid,$inv->sku
            ]);
            $who = 'WAS'.(Auth::user() ? '-'.Auth::user()["name"] : '');
            if ($invHistory)$db->update(DB::raw("UPDATE inv_lot_loc_id SET qty = qty+? WHERE lotnum = ? AND locationid = ? AND traceid = '*'"),[
                (int)$amount,$inv->lotnum,$ide
            ]);
            else $db->insert(DB::raw("INSERT INTO inv_lot_loc_id VALUES(?,?,'*',?,?,?,0,0,0,0,0,0,TO_DATE(?,'yyyy-mm-dd hh24:mi:ss'),?,TO_DATE(?,'yyyy-mm-dd hh24:mi:ss'),?,0,0,0,0,0,'*',0,null)"),[
                $inv->lotnum,$ide,$inv->customerid,$inv->sku,$amount,date("Y-m-d H:i:s"),$who,
                date("Y-m-d H:i:s"),$who
            ]);
            $sql = <<<sql
INSERT INTO ACT_TRANSACTION_LOG VALUES(?,'PA',?,?,?,?,'ASN',?,?,?,?,?,?,?,?,TO_DATE(?,'yyyy-mm-dd hh24:mi:ss'),?,
TO_DATE(?,'yyyy-mm-dd hh24:mi:ss'),?,0,0,0,0,TO_DATE(?,'yyyy-mm-dd hh24:mi:ss'),?,?,null,null,null,'*',?,?,?,?,?,?,?,
?,?,?,?,?,'N',null,?,?,?,?,null,null,?,null,null)
sql;
            list($trid,$max) = $this->getTrNumber();
            $db->insert(DB::raw($sql),[
                $trid,$task->customerid,$task->sku,
                $task->docno,$task->doclineno,$inv->lotnum,$task->fmlocation,$task->fmid,$task->fmpackid,$task->fmuom,$amount,$amount,'99',date("Y-m-d H:i:s"),$who,
                date("Y-m-d H:i:s"),$who,date("Y-m-d H:i:s"),$task->customerid,$task->sku,$ide,$who,$task->fmpackid,$task->fmuom,$amount,$amount,$inv->lotnum,
                '*','0','N','*',$task->taskid_sequence,$task->warehouseid,$task->userdefine1,$task->userdefine2,
                $task->userdefine3,'O'
            ]);
            $this->setTrNumber($max);
            $sql = <<<sql
update TSK_TASKLISTS set TASKPROCESS = '99',REASONCODE = 'OK',PLANTOLOCATION = ?,PLANLOGICALTOSEQUENCE = ?,
COMPLETED_TRANSACTIONID = ?,OPENWHO = ?,OPENTIME = TO_DATE(?,'yyyy-mm-dd hh24:mi:ss'),
 CLOSEWHO = ?,CLOSETIME = ?,EDITTIME = ?,EDITWHO = ?
 where taskid = ? AND TASKID_SEQUENCE = ?
sql;
            $db->update(DB::raw($sql),[
                $ide,'0',$trid,$who,date("Y-m-d H:i:s"),$who,date("Y-m-d H:i:s"),date("Y-m-d H:i:s"),$who,$task->taskid,$task->taskid_sequence
            ]);
            $task->who = $who;
            $this->checkAsn($task);
            $db->commit();
            return true;
        }catch (\Exception $e){
            $db->rollBack();
            return false;
        }
    }

    private function checkAsn($task)
    {
        $sql = <<<SQL
SELECT 1 FROM DOC_ASN_DETAILS WHERE ASNNO = ? AND LINESTATUS != '40'
SQL;
        $asn = DB::connection("oracle")->selectOne(DB::raw($sql),[$task->docno]);
        if ($asn)return;

        $sql = <<<SQL
SELECT 1 FROM TSK_TASKLISTS WHERE TASKPROCESS != '99' AND TASKTYPE = 'PA' AND DOCNO = ?
SQL;
        $task = DB::connection("oracle")->selectOne(DB::raw($sql),[$task->docno]);
        if ($task)return;

        DB::connection("oracle")->update(DB::raw("UPDATE DOC_ASN_HEADER SET asnstatus = '99',edittime = TO_DATE(?,'yyyy-mm-dd hh24:mi:ss'),editwho = ? WHERE asnno = ?"),
            [date("Y-m-d H:i:s"),$task->who,$task->docno]);
        DB::connection("oracle")->update(DB::raw("UPDATE DOC_ASN_DETAILS SET linestatus = '99',edittime = TO_DATE(?,'yyyy-mm-dd hh24:mi:ss'),editwho = ? WHERE asnno = ?"),
            [date("Y-m-d H:i:s"),$task->who,$task->docno]);
        $sql = <<<SQL
DELETE FROM INV_LOT_LOC_ID WHERE ((LOTNUM,LOCATIONID,TRACEID) IN
(SELECT PLANTOLOTNUM,PLANTOLOCATION,PLANTOID FROM TSK_TASKLISTS WHERE DOCNO = ? AND DOCTYPE = 'ASN' AND TASKTYPE = 'PA' AND TASKPROCESS = '99') OR
(LOTNUM,LOCATIONID,TRACEID) IN
(SELECT FMLOTNUM,FMLOCATION,FMID FROM TSK_TASKLISTS WHERE DOCNO = ? AND DOCTYPE = 'ASN' AND TASKTYPE = 'PA' AND TASKPROCESS = '99'))
AND QTY = 0 AND QTYPA = 0
SQL;
        DB::connection("oracle")->delete(DB::raw($sql),[$task->docno,$task->docno]);
    }
    /**
     * put cache rack box to warehousing(将缓存架料箱入库)
     *
     * @param string $fromLocation
     * @param integer $boxId
     *
     * @return int
     */
    public function putWareHousing(string $fromLocation, $boxId):?int
    {
        $station = Station::query()->select("id")
            ->where("station_type_id",5)->where("code",$fromLocation)->first();
        if (!$station)return null;
        if (StationTask::query()->select("id")->where("status","!=",'完成')->where("station_id",$station->id)->first())return null;
        /** @var StationTaskMaterialBox|\stdClass $stmb */
        $stmb = $this->createWarehousingTask($station->id,$boxId);
        return $stmb->id;
    }


    /**
     * 获取事务现号
     *
     * @return array
     */
    private function getTrNumber()
    {
        $val = ValueStore::query()->select("value")->where("name","flux_tr_number")->first();
        if (!$val)$val = ValueStore::query()->create(["name"=>"flux_tr_number","value"=>'0']);
        $max = $val->value+1;
        $number = sprintf("%09d", $max);
        return array('W'.$number,$max);
    }

    /**
     * 设置事务现号
     *
     * @param integer $max
     */
    private function setTrNumber($max)
    {
        ValueStore::query()->select("value")->where("name","flux_tr_number")->update(["value"=>(string)((int)$max+1)]);
    }

    /**
     * 入库
     *
     * @param integer $boxId
     * @param integer $amount
     * @param integer $commodityId
     *
     * @return bool
     */
    public function warehousing($boxId, $amount, $commodityId = null):bool
    {
        DB::beginTransaction();
        try{
            $storage = Storage::query()->where("material_box_id",$boxId)->lockForUpdate()->first();
            if (!$storage && !$commodityId)return false;
            if (!$storage){
                Storage::query()->create([
                    "station_id" => null,
                    "material_box_id" => $boxId,
                    "commodity_id" => $commodityId,
                    "amount" => $amount,
                ]);
                return true;
            }
            if ($commodityId && $storage->commodity_id && $storage->commodity_id!=$commodityId)return false;
            $obj = [
                "station_id" => null,
                "amount" => DB::raw("amount + {$amount}"),
            ];
            if (!$storage->commodity_id && $commodityId)$obj["commodity_id"] = $commodityId;
            $storage->update($obj);
            DB::commit();
            return true;
        }catch (\Exception $e){
            DB::rollBack();
            return false;
        }
    }

    /**
     * 出库
     *
     * @param integer $boxId
     * @param integer $amount
     * @param integer $commodityId
     *
     * @return bool
     */
    public function outWarehousing($boxId, $amount, $commodityId = null):bool
    {
        DB::beginTransaction();
        try{
            $storage = Storage::query()->where("material_box_id",$boxId)->lockForUpdate()->first();
            if (!$storage)return false;
            if ($commodityId && $storage->commodity_id && $storage->commodity_id!=$commodityId)return false;
            $obj = [
                "station_id" => null,
                "amount" => DB::raw("amount - {$amount}"),
            ];
            if (!$storage->commodity_id && $commodityId)$obj["commodity_id"] = $commodityId;
            $storage->update($obj);
            DB::commit();
            return true;
        }catch (\Exception $e){
            DB::rollBack();
            return false;
        }
    }

    /**
     * 清除任务
     *
     * @param array $stationCodes
     *
     */
    public function clearTask(array $stationCodes)
    {
        //清除海柔信息，标记料箱为出库
        DB::connection("mysql_haiRobotics")->table("ks_bin")->whereIn("ks_bin_space_code",$stationCodes)
            ->where("status",1)->update([
                "ks_bin_space_code" => null,"ks_bin_space_id"=>null,"orig_ks_bin_space_code"=>null,"orig_ks_bin_space_id"=>null,
                "status"=>4,
            ]);
        //WAS任务清除
        $station = Station::query()->select("id")->whereIn("code",$stationCodes);
        $task = StationTask::query()->select("id")->where("status","!=",'完成')->whereIn("station_id",$station);
        StationTaskMaterialBox::query()->where("status","!=",'完成')->whereIn("station_task_id",$task)->update([
            "status" => "完成"
        ]);
        StationTask::query()->where("status","!=",'完成')->whereIn("station_id",$station)->update([
            "status" => "完成"
        ]);
    }

    /**
     * 获取半箱库位库存信息
     *
     * @param CommodityMaterialBoxModel|\stdClass $model
     * @param StoreItem|\stdClass $item
     * @param string|null $asn
     *
     * @return ?Storage
     */
    public function getHalfBoxLocation(CommodityMaterialBoxModel $model,StoreItem $item,?string $asn = null,array $blacklist = []):?Storage
    {
        if (!$asn){$item->loadMissing("store");$asn = $item->store->asn_code;}
        $boxCodes = '';//拼接料箱编码
        $map = [];//库位与库存映射
        //查询填充
        $query = Storage::query()->with("materialBox")->whereHas("materialBox",function (Builder $query)use($model){
            $query->where("material_box_model_id",$model->material_box_model_id);
        })->where("commodity_id",$model->commodity_id)->where("amount","<",$model->maximum)
            ->where("status",0)->where(DB::raw("{$model->maximum}-amount"),">",0);
        if ($blacklist)$query->whereHas("materialBox",function (Builder $query)use($blacklist){
           $query->whereNotIn("id",$blacklist);
        });
        $query->get()->each(function ($storage)use(&$boxCodes,&$map){
            $boxCodes .= "'".$storage->materialBox->code."',";
            $map[$storage->materialBox->code] = $storage;
        });
        //不存在跳出
        if (!$boxCodes)return null;
        $boxCodes = mb_substr($boxCodes,0,-1);
        //查询对应asn detail
        $detail = DB::connection("oracle")->selectOne(DB::raw("SELECT * FROM DOC_ASN_DETAILS WHERE ASNNO = ? AND ASNLINENO = ?"),[
            $asn,$item->asn_line_code
        ]);
        if(!$detail)return null;
        $detail = get_object_vars($detail);
        //查询对应批次属性
        $lot = DB::connection("oracle")->selectOne(DB::raw("SELECT * FROM BAS_LOTID WHERE LOTID = (SELECT LOTID FROM BAS_SKU WHERE CUSTOMERID = ? AND SKU = ?)"),[
            $detail["customerid"],$detail["sku"]
        ]);
        if(!$lot)return null;
        //通过符合条件的批次号来查询 库存
        $lot = get_object_vars($lot);
        $sql = <<<sql
SELECT * FROM INV_LOT_LOC_ID WHERE LOTNUM IN
 (SELECT LOTNUM FROM INV_LOT_ATT WHERE INV_LOT_ATT.CUSTOMERID = ? AND SKU = ?
sql;
        //拼接可以合并的批次属性要求
        for ($i=1;$i<=8;$i++){
            if ($lot["lotkey0{$i}"]=='Y'){
                $val = $detail["lotatt0{$i}"] ? "'{$detail["lotatt0{$i}"]}'" : null;
                $sql .= " AND LOTATT0{$i} = $val";
            }
        }
        $sql .= ") AND LOCATIONID IN ({$boxCodes}) AND TRACEID = '*' AND {$model->maximum}-QTY > 0 ORDER BY {$model->maximum}-QTY";
        $res = DB::connection("oracle")->selectOne(DB::raw($sql),[
            $detail["customerid"],$detail["sku"]
        ]);
        return $res ? $map[$res->locationid] : null;
    }

    /**
     * 检查可上架数量
     *
     * @param string $asn
     * @param string $barCode
     *
     * @return int
     */
    public function checkPutAmount(string $asn,string $barCode):int
    {
        $sql = <<<SQL
SELECT SUM(FMQTY) qty FROM TSK_TASKLISTS
LEFT JOIN BAS_SKU ON TSK_TASKLISTS.CUSTOMERID = BAS_SKU.CUSTOMERID AND TSK_TASKLISTS.SKU = BAS_SKU.SKU
WHERE DOCNO = ? AND (ALTERNATE_SKU1 = ? OR ALTERNATE_SKU2 = ? OR ALTERNATE_SKU3 = ?) AND TASKTYPE = 'PA'
AND TASKPROCESS = '00'
SQL;
        $tsk = DB::connection("oracle")->selectOne(DB::raw($sql),[$asn,$barCode,$barCode,$barCode]);
        if (!$tsk)return  0;
        $trk = TaskTransaction::query()->select(DB::raw("SUM(amount) amount"))->where("doc_code",$asn)->where("bar_code",$barCode)
            ->where("type","入库")->where("status",0)->first();
        if (!$trk)return $tsk->qty;
        return $tsk->qty - $trk->amount;
    }
}