baoshi-was/app/Http/Controllers/StorageController.php

<?php

namespace App\Http\Controllers;

use App\CommodityMaterialBoxModel;
use App\Components\AsyncResponse;
use App\MaterialBox;
use App\Station;
use App\StationTask;
use App\StationTaskMaterialBox;
use App\Storage;
use App\StoreItem;
use App\TaskTransaction;
use App\ValueStore;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;

class StorageController extends Controller
{
    use AsyncResponse;

    public function putShelf()
    {
        $asn = \request("asn");
        $ide = \request("ide");
        $barCode = \request("barCode");
        $amount = (int)\request("amount");
        //check info
        if (!$asn || !$ide || !$barCode || !$amount)$this->error("信息不完整");
        $fromLocation = app("MaterialBoxService")->getBoxLocation($ide);
        if (!$fromLocation)$this->error("海柔无此库位信息");
        $box = MaterialBox::query()->select("id")->where("code",$ide)->first();
        if (!$box)$this->error("WAS无此料箱");

        //get flux
        $sql = <<<sql
SELECT * 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;
        $asns = DB::connection("oracle")->select(DB::raw($sql),[$asn,$barCode,$barCode,$barCode,$amount]);
        if (!$asns)$this->error("ASN不存在或上架数量与入库数量不符");
        $nums = [];
        $result = null;
        foreach ($asns as $index=>$asn){
            if ((int)$asn->fmqty == $amount){$result=$index;break;}
            $nums[] = (int)$asn->fmqty;
        }
        //flux上架
        if ($result===null){
            $result = $this->twoSum($nums,$amount);
            if (!$result)$this->error("无法匹配入库记录，检查您的上架数量");
            DB::connection("oracle")->transaction(function ()use($result,$asns,$ide){
                foreach ($result as $index){
                    $this->fluxPA($asns[$index],$ide,(int)$asns[$index]->fmqty);
                }
            });
        } else $this->fluxPA($asns[$result],$ide,$amount);
        //亮灯
        app("CacheShelfService")->_stationCacheLightOn($fromLocation,$ide);
        //库存相关信息
        /** @var Station|\stdClass $station */
        $station = Station::query()->firstOrCreate(["code"=>$fromLocation],[
            'name' => $fromLocation, 'code' => $fromLocation, 'station_type_id' => 5
        ]);
        $item = StoreItem::query()->whereHas("store",function (Builder $query){
            $query->where("asn_code",request("asn"));
        })->whereHas("commodity",function (Builder $query)use($barCode){
            $query->whereHas("barcodes",function (Builder $query)use($barCode){
                $query->where("code",$barCode);
            });
        })->first();
        //库存记录
        app("StorageService")->enterWarehouse($station->id, $box->id, $item->commodity_id ?? null, $amount, $box->material_box_model_id);
        //建立入库任务
        if (!app("ForeignHaiRoboticsService")->putWareHousing($fromLocation,$box->id))$this->error("错误库位或库位已被下达任务");

        $maximum = app("CommodityMaterialBoxModelService")->getMaximum($box->material_box_model_id,$item->commodity_id ?? null);
        if ($maximum && $maximum<$amount)app("CommodityMaterialBoxModelService")->setMaximum($box->material_box_model_id,$item->commodity_id ?? null,$amount);
        $this->success(["model" => $box->material_box_model_id,"commodity" => $item->commodity_id ?? null, "maximum" => $maximum]);
    }

    public function setMaximum()
    {
        app("CommodityMaterialBoxModelService")->setMaximum(request("model"),request("commodity"),request("maximum"));
        $this->success();
    }

    /**
     * 检查最大限值并返回
     *
     */
    public function checkMaximum()
    {
        $item = StoreItem::query()->whereHas("store",function (Builder $query){
            $query->where("asn_code",request("asn"));
        })->whereHas("commodity",function (Builder $query){
            $query->whereHas("barcodes",function (Builder $query){
                $query->where("code",request("barCode"));
            });
        })->first();
        if (!$item)$this->error("WAS无此单据记录");
        $models = CommodityMaterialBoxModel::query()->where("commodity_id",$item->commodity_id)->get();
        if ($models->count()==0)$this->error("商品首入，请使用缓存架空箱入库");
        foreach ($models as $model){
            $box = Storage::query()->select(DB::raw("MAX({$model->maximum}-amount) need"),"material_box_id")
                ->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)->first();
            $box->commodity_id = $item->commodity_id;
            if ($box)$this->success($box);
        }
        $this->success(["need"=>$models[0]->maximum,"material_box_model_id"=>$models[0]->material_box_model_id,"commodity_id"=>$item->commodity_id]);
    }

    public function fluxPA($asn,$ide,$amount)
    {
        if (!$asn->taskid)$this->error("ASN单无此入库信息，禁止上架");

        $sql = <<<sql
SELECT * FROM inv_lot_loc_id  WHERE lotnum = ? AND traceid = ? AND customerid= ?  and sku = ?
sql;
        $inv = DB::connection("oracle")->select(DB::raw($sql),[$asn->fmlotnum,$asn->plantoid,$asn->customerid,$asn->sku]);
        if (count($inv)==0)$this->error("余量与入库不符");
        DB::connection("oracle")->transaction(function ()use($inv,$amount,$ide,$asn,&$who){
            $db = DB::connection("oracle");
            $qty = $amount;
            foreach ($inv as $in){
                if ($qty==0)break;
                if ($in->qty > $qty){
                    $db->update(DB::raw("update inv_lot_loc_id set qty = qty-?,qtymvout = qty-? where lotnum = ? and locationid = ? and traceid = ?"),[
                        $qty,$qty,$in->lotnum,$in->locationid,$in->traceid
                    ]);//TODO 遗留问题：对应生成分配库位上架数量未被变更
                    $in->qty = $in->qty-$qty;
                    $qty = 0;
                }else{
                    $db->delete(DB::raw("DELETE FROM inv_lot_loc_id WHERE lotnum = ? and locationid = ? and traceid = ?"),[
                        $in->lotnum,$in->locationid,$in->traceid
                    ]);
                    $qty = $qty-$in->qty;
                }
            }
            $db->delete(DB::raw("DELETE FROM inv_lot_loc_id WHERE lotnum = ? AND traceid = ? AND traceid != '*'  AND qty = 0"),[
                $inv[0]->lotnum,$inv[0]->traceid
            ]);
            $invHistory = $db->selectOne(DB::raw("SELECT * FROM inv_lot_loc_id WHERE lotnum = ? AND locationid = ? AND customerid = ? AND sku = ? AND traceid = '*' FOR UPDATE"),[
                $inv[0]->lotnum,$ide,$inv[0]->customerid,$inv[0]->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[0]->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[0]->lotnum,$ide,$inv[0]->customerid,$inv[0]->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)
sql;
            $trid = $this->getTrNumber();
            $db->insert(DB::raw($sql),[
                $trid,$asn->customerid,$asn->sku,
                $asn->asnno,$asn->asnlineno,$inv[0]->lotnum,$asn->fmlocation,$asn->plantoid,$asn->packid,$asn->uom,$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"),$asn->customerid,$asn->sku,$ide,$who,$asn->packid,$asn->uom,$amount,$amount,$inv[0]->lotnum,
                '*','0','N','*',$asn->taskid_sequence,$asn->warehouseid,$asn->userdefine1,$asn->userdefine2,
                $asn->userdefine3,$asn->userdefine4,$asn->userdefine5,'O'
            ]);
            $this->setTrNumber();
            $sql = <<<sql
update TSK_TASKLISTS set TASKPROCESS = '99',REASONCODE = 'OK',PLANTOLOCATION = ?,PLANLOGICALTOSEQUENCE = ?,
CREATE_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,$ide,$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,$asn->taskid,$asn->taskid_sequence
            ]);
        });
        //成功后应去修改ASN状态及数量 暂时不知上架后ASN应为状态
        /*        $sql = <<<sql
        UPDATE doc_asn_details SET linestatus = ?,edittime = TO_DATE(?,'yyyy-mm-dd hh24:mi:ss'),editwho = ? WHERE asnno = ? AND asnlineno = ?
        sql;*/
        //if ($asn->expectedqty>$asn->receivedqty && $asn->linestatus!='30')DB::connection("oracle")->update(DB::raw($sql),['30',date("Y-m-d H:i:s"),$who,$asn->asnno,$asn->asnlineno]);
        if ($asn->expectedqty==$asn->receivedqty && $asn->linestatus=='40'){
            //DB::connection("oracle")->update(DB::raw($sql),['40',date("Y-m-d H:i:s"),$who,$asn->asnno,$asn->asnlineno]);
            $check = DB::connection("oracle")->selectOne(DB::raw("SELECT 1 FROM DOC_ASN_DETAILS WHERE ASNNO = ? AND LINESTATUS != '40'"),[$asn->asnno]);
            if (!$check){
                $logs = DB::connection("oracle")->select(DB::raw("SELECT SUM(FMQTY) qty,TRANSACTIONTYPE FROM ACT_TRANSACTION_LOG WHERE DOCNO = ? AND TRANSACTIONTYPE IN ('PA','IN') GROUP BY TRANSACTIONTYPE"),[$asn->asnno]);
                $paQty = 0;
                $inQty = 0;
                foreach ($logs as $log){
                    if ($log->transactiontype == 'IN')$inQty = $log->qty;
                    else $paQty = $log->qty;
                }
                if ($paQty == $inQty){
                    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"),$who,$asn->asnno]);
                    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"),$who,$asn->asnno]);
                }
            }
        }
    }

    /**
     * @param Integer[] $nums
     * @param Integer $target
     * @return Integer[]|null
     */
    function twoSum($nums, $target) {
        $map=[];
        for($i=0;$i<count($nums);$i++){
            $complement=$target-$nums[$i];
            if(array_key_exists($complement,$map)){
                return [$map[$complement],$i];
            }
            $map[$nums[$i]]=$i;
        }
        return null;
    }

    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 'W'.$number;
    }

    function setTrNumber()
    {
        ValueStore::query()->select("value")->where("name","flux_tr_number")->update(["value"=>DB::raw("flux_tr_number+1")]);
    }

    /**
     * 重置缓存架指定格口
     */
    public function resetCacheShelf()
    {
        $boxes = request("boxes");
        //清理任务
        $data = '';
        $station = Station::query()->select("id")->whereIn("code",$boxes);
        $occupy = Storage::query()->with("station:id,code")->whereIn("station_id",$station)->where("status",1)->get();
        foreach ($occupy as $item){
            unset($boxes[array_search($item->station->code,$boxes)]);
            $data .= '“'.$item->station->code.'”,';
        }
        if ($occupy->count()>0){
            $data .= "存在任务待处理,无法释放";
            $boxes = array_values($boxes);
            $station = Station::query()->select("id")->whereIn("code",$boxes);
        }
        $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" => "完成"
        ]);

        //重新调取料箱
        app("ForeignHaiRoboticsService")->paddingCacheShelf($station->get());
        $this->success(["data"=>$data,"boxes"=>$boxes]);
    }

    /**
     * 取得料箱
     */
    public function acquireBox()
    {
        $boxId = request("material_box_id");
        $modelId = request("material_box_model_id");
        //获取目标库位
        $station = app("StationService")->getMirrorMappingLocation(request("station"));
        if (!$station)$this->error("未知库位");
        //获取料箱
        if ($boxId && !app("MaterialBoxService")->checkUsableBox($boxId)){
            $boxId = null;
            //料箱存在且不可用
            $models = CommodityMaterialBoxModel::query()->where("commodity_id",request("commodity_id"))->get();
            foreach ($models as $model){
                Storage::query()->select("material_box_id")
                    ->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"),">=",request("amount"))->get()->each(function ($box)use(&$boxId){
                        if (app("MaterialBoxService")->checkUsableBox($box->id)){
                            $boxId = $box->id;
                            return true;
                        }
                        return false;
                    });
                if ($boxId)break;
            }
        }
        if (!$boxId){
            if (!$modelId){
                $box = null;
                $models = CommodityMaterialBoxModel::query()->where("commodity_id",request("commodity_id"))->get();
                foreach ($models as $model){
                    $box = app("MaterialBoxService")->getAnEmptyBox([],$model->material_box_model_id);
                    if($box)break;
                }
            }else $box = app("MaterialBoxService")->getAnEmptyBox([],$modelId);
            if (!$box)$this->error("无可用料箱");
            $boxId = $box->id;
        }
        //发起取箱任务
        $collection = new Collection();
        $task = StationTask::query()->create([
            'status' => "待处理",
            'station_id' => $station->id,
        ]);
        $collection->add(StationTaskMaterialBox::query()->create([
            'station_id' => $station->id,
            'material_box_id'=>$boxId,
            'status'=>"待处理",
            'type' => '取',
            'station_task_id' => $task->id,
        ]));
        if (!app("ForeignHaiRoboticsService")->fetchGroup($station->code,$collection,'','立架出至缓存架'))$this->error("呼叫机器人失败");
        //生成临时任务事务
        TaskTransaction::query()->create([
            "fm_station_id" => $station->id,
            "material_box_id" => $boxId,
            "commodity_id" => request("commodity_id"),
            "amount" => request("amount"),
            "type" => "入库",
            "user_id" => Auth::id(),
            "mark" => 1
        ]);
        //亮灯
        app("CacheShelfService")->stationLightUp($station->code,null,'2');
        $this->success();
    }
}