baoshi-was/app/Services/StorageService.php

<?php

namespace App\Services;

use App\CommodityMaterialBoxModel;
use App\Components\ErrorPush;
use App\MaterialBoxCommodity;
use App\Order;
use App\Station;
use App\StationTask;
use App\StationTaskMaterialBox;
use App\StoreItem;
use App\TaskTransaction;
use App\Traits\ServiceAppAop;
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\Cache;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Str;

class StorageService
{
    use ServiceAppAop;
    use ErrorPush;

    /**
     * 缓存架放置记录
     *
     * @param StationTaskMaterialBox|\stdClass $stationTaskMaterialBox
     */
    public function putCacheShelf($stationTaskMaterialBox)
    {
        DB::beginTransaction();
        try{
            $stationTaskMaterialBox->loadMissing("station");
            if (!$stationTaskMaterialBox->station)return;
            if (app("StationService")->isHalfBoxLocation($stationTaskMaterialBox->station)){
                //清除海柔库位信息
                $this->clearTask([$stationTaskMaterialBox->station->code]);
                $stationId = $stationTaskMaterialBox->station_id;
                $tasks = TaskTransaction::query()->with("materialBox")->where(function ($query)use($stationId){
                    $query->where("fm_station_id",$stationId)->orWhere("to_station_id",$stationId);
                })->where("status",0)->get();
                if ($tasks->count()!=0){
                    $options = [];
                    switch ($tasks[0]->mark){
                        case 1:
                            $options["title"] = '上架任务';
                            break;
                        case 2:
                            $options["title"] = '出库任务';
                            break;
                        default:
                            $options["title"] = '未知类型';
                    }
                    switch ($tasks->count()){
                        case 1:
                            $task = $tasks[0];
                            $options["detail01"] = $task->materialBox->code ?? '';
                            $options["detail02"] = $task->doc_code ?: '跟踪:‘'.$task->track_num."’ 批次:‘".$task->lot_num."’";
                            $options["detail03"] = $task->bar_code;
                            $options["qty01"] = $task->amount;
                            $options["uomDesc01"] = '件';
                            $options["qty02"] = $task->bin_number;
                            $options["uomDesc02"] = '号';
                            break;
                        default:
                            $count = count(array_unique(array_column($tasks->toArray(),"commodity_id")));
                            if ($count==1){
                                $options["detail01"] = $tasks[0]->bar_code;
                                $options["detail02"] = "";
                                $options["detail03"] = "";
                                $amount = 0;
                                foreach ($tasks as $task){
                                    if (mb_strlen($options["detail02"])>20){
                                        $options["detail03"] .= $task->bin_number."号-".$task->amount."件,";
                                    }else $options["detail02"] .= $task->bin_number."号-".$task->amount."件,";
                                    $amount += $task->amount;
                                }
                                $options["detail02"] = rtrim($options["detail02"],",");
                                $options["detail03"] = rtrim($options["detail03"],",");
                                $options["qty02"] = $amount;
                                $options["uomDesc02"] = '件';
                            }else{
                                $task = $tasks[0];
                                $options["detail01"] = $task->materialBox->code ?? '';
                                $options["detail02"] = "货品过多请自行核对";
                                $options["detail03"] = "波次：".$task->doc_code ?
                                    (Order::query()->with("batch")->where("code",$task->doc_code)->first()->batch->code ?? '无') : '无';
                            }
                            break;
                    }
                    Cache::forget("CACHE_SHELF_OCCUPANCY_{$stationTaskMaterialBox->station->id}");//关闭无限亮灯
                    app("StationService")->locationOccupy($stationTaskMaterialBox->station->code,$stationTaskMaterialBox->material_box_id);//料箱绑定缓存架
                    app("CacheShelfService")->lightUp($stationTaskMaterialBox->station->code,'2','0',$options);
                }else{
                    app("StationService")->locationFreed($stationTaskMaterialBox->station->code); //释放库位占用
                    app("CacheShelfService")->_stationCacheLightOff($stationTaskMaterialBox->station->code);//灭灯
                }
            }
            DB::commit();
        }catch (\Exception $e){
            DB::rollBack();
            $this->push(__METHOD__."->".__LINE__,"清除任务亮灯失败","错误信息：".$e->getMessage()."  执行任务信息：".json_encode($stationTaskMaterialBox));
        }
    }

    /**
     * 检查临时事务标记处理一些特殊情况
     *
     * @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,'3','0',["title"=>'机器人取箱中，禁止操作',]);
    }

    /**
     * 检查存储 根据事务表做处理
     *
     * @param Station|\stdClass $station
     *
     * @return bool
     *
     * @throws
     */
    public function checkStorage(Station $station):?bool
    {
        $stationId = $station->id;
        $task = TaskTransaction::query()->with("materialBox")->where(function ($query)use($stationId){
            $query->where("fm_station_id",$stationId)->orWhere("to_station_id",$stationId);
        })->where("status",0)->first();
        if (!$task)return null;
        switch ($task->type){
            case "入库":
                switch ($task->mark){
                    case 1:
                        return $this->handlePaTransaction($task, $station);
                }
                break;
            case "出库":
                switch ($task->mark){
                    case 2:
                        return $this->handleOutTransaction($station);
                }
        }
        return null;
    }

    /**
     * 处理出货交易
     *
     * @param Station|\stdClass $station
     *
     * @return bool
     */
    private function handleOutTransaction($station):bool
    {
        return TaskTransaction::query()->with("materialBox")->orWhere("to_station_id",$station->id)
            ->where("status",0)->update([
                "status" => 1,
            ]) > 0;
    }

    /**
     * 处理上架交易
     *
     * @param TaskTransaction|\stdClass $task
     * @param Station|\stdClass $station
     *
     * @return bool
     * @throws
     */
    private function handlePaTransaction($task, $station):bool
    {
        DB::beginTransaction();
        try{
            //get flux
            $tasks = $this->getFluxTask($task->track_num,$task->lot_num,$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);//建立入库任务
            //2021-07-27 取消WAS库存维护
            //if (!$this->enterWarehouse($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;
        }
    }

    /**
     * 建立入库任务
     *
     * @param $stationId
     * @param $boxId
     *
     * @return StationTaskMaterialBox|\stdClass|Model
     */
    public function createWarehousingTask($stationId,$boxId):StationTaskMaterialBox
    {
        /** @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 $boxId
     * @param integer $commodityId
     * @param integer $amount
     * @param integer|null $modelId
     *
     * @return bool
     */
    public function enterWarehouse(int $boxId,int $commodityId,int $amount,?int $modelId = null):bool
    {
        DB::beginTransaction();
        try{
            $storage = MaterialBoxCommodity::query()->where("material_box_id",$boxId)
                ->where("commodity_id",$commodityId)->lockForUpdate()->first();
            if ($storage){
                $amountTemp = (int)$storage->amount + (int)$amount;
                $storage->update(["amount"=>DB::raw("amount+{$amount}")]);
                $amount = $amountTemp;
            } else $storage = MaterialBoxCommodity::query()->create([
                "amount" => $amount,
                "material_box_id" => $boxId,
                "commodity_id" => $commodityId,
            ]);
            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([$boxId, $commodityId, $amount, $modelId]));
            return true;
        }catch(\Exception $e){
            DB::rollBack();
            return false;
        }
    }

    /**
     * 获取FLUX上架任务列表
     *
     * @param string $trace
     * @param string $lotNum
     * @param string $barCode
     * @param int $amount
     *
     * @return array|null
     */
    public function getFluxTask(string $trace, string $lotNum, string $barCode,int $amount):array
    {
        $sql = <<<SQL
SELECT TSK_TASKLISTS.* FROM TSK_TASKLISTS LEFT JOIN BAS_SKU ON TSK_TASKLISTS.CUSTOMERID = BAS_SKU.CUSTOMERID
AND TSK_TASKLISTS.SKU = BAS_SKU.SKU
WHERE FMID = ? AND FMLOTNUM = ? AND (ALTERNATE_SKU1 = ? OR ALTERNATE_SKU2 = ? OR ALTERNATE_SKU3 = ?)
AND TASKPROCESS = '00' AND TASKTYPE = 'PA'
SQL;
        $tasks = DB::connection("oracle")->select(DB::raw($sql),[$trace,$lotNum,$barCode,$barCode,$barCode]);

        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
     * @throws
     */
    public 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
     */
    public 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();
            $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;
        if (DB::connection("oracle")->selectOne(DB::raw($sql),[$task->docno]))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
     */
    public function getTrNumber()
    {
        $val = ValueStore::query()->select("value")->where("name","flux_tr_number")->lockForUpdate()->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);
    }

    /**
     * 设置事务现号
     *
     */
    public function setTrNumber()
    {
        ValueStore::query()->select("value")->where("name","flux_tr_number")->update(["value"=>DB::raw("value+1")]);
    }

    /**
     * 清除任务
     *
     * @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,
            ]);
    }

    /**
     * 获取指定型号的半箱库位
     *
     * @param CommodityMaterialBoxModel $model
     * @param string $lotNum
     * @param bool $multi
     * @param array $blacklist
     * @return mixed
     */
    public function getMaxAvailableHalfBoxLocation(CommodityMaterialBoxModel $model, string $lotNum, bool $multi = true, array $blacklist = [])
    {
        list($boxCodes,$map) = app("MaterialBoxService")->getModelAvailableBox($model->material_box_model_id,true,$blacklist);
        if (!$boxCodes)return null;
        $sql = <<<SQL
SELECT LOCATIONID,({$model->maximum}-QTY) AS QTY FROM INV_LOT_LOC_ID WHERE LOTNUM = '{$lotNum}'
AND LOCATIONID IN ({$boxCodes}) AND TRACEID = '*' AND {$model->maximum}-QTY > 0 AND QTY > 0 ORDER BY {$model->maximum}-QTY
SQL;
        if ($multi)return array(DB::connection("oracle")->select(DB::raw($sql)),$map);
        return DB::connection("oracle")->selectOne(DB::raw($sql));
    }

    /**
     * 获取半箱库位库存信息  废弃
     *
     * @param CommodityMaterialBoxModel|\stdClass $model
     * @param StoreItem|\stdClass $item
     * @param string|null $asn
     *
     * @return ?MaterialBoxCommodity
     */
    public function getHalfBoxLocation(CommodityMaterialBoxModel $model,StoreItem $item,?string $asn = null,array $blacklist = []):?MaterialBoxCommodity
    {
        if (!$asn){$item->loadMissing("store");$asn = $item->store->asn_code;}
        $boxCodes = '';//拼接料箱编码
        $map = [];//库位与库存映射
        //查询填充
        $query = MaterialBoxCommodity::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);
        if ($blacklist)$query->whereNotIn("material_box_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 (CASE QTY WHEN 0 THEN 1 ELSE 0 END),{$model->maximum}-QTY";
        $res = DB::connection("oracle")->selectOne(DB::raw($sql),[
            $detail["customerid"],$detail["sku"]
        ]);
        return $res ? $map[$res->locationid] : null;
    }

    /**
     * 检查可上架数量
     *
     * @param string $track
     * @param string $barCode
     * @param string $lotNum
     *
     * @return int
     */
    public function checkPutAmount(string $track, string $barCode, string $lotNum):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 FMID = ? AND FMLOTNUM = ? AND (ALTERNATE_SKU1 = ? OR ALTERNATE_SKU2 = ? OR ALTERNATE_SKU3 = ?) AND TASKTYPE = 'PA'
AND TASKPROCESS = '00'
SQL;
        $tsk = DB::connection("oracle")->selectOne(DB::raw($sql),[$track,$lotNum,$barCode,$barCode,$barCode]);
        if (!$tsk)return 0;
        $trk = TaskTransaction::query()->select(DB::raw("SUM(amount) amount"))->where("track_num",$track)
            ->where("lot_num",$lotNum)->where("bar_code",$barCode)
            ->where("type","入库")->where("status",0)->first();
        if (!$trk)return $tsk->qty;
        return $tsk->qty - $trk->amount;
    }

    /**
     * @param Collection $tasks
     */
    public function handleStorage(Collection $tasks)
    {
        $tasks = \Illuminate\Database\Eloquent\Collection::make($tasks);
        $tasks->load("stationTaskCommodities");
        if (!$tasks->count())return;
        foreach ($tasks as $task){
            if (!$task->stationTaskCommodities)continue;
            foreach ($task->stationTaskCommodities as $commodity){
                $update[] = [$task->material_box_id];
                $result = MaterialBoxCommodity::query()->where("material_box_id",$task->material_box_id)
                    ->where("commodity_id",$commodity->commodity_id)
                    ->update(["amount"=>DB::raw("amount-{$commodity->amount}")]);
                if ($result!==1)$this->push(__METHOD__."->".__LINE__,"库存处理异常","修改了：".$result."行; 表参数：".$commodity->toJson());
            }
        }
    }
}