baoshi-was/app/Services/StorageService.php

<?php 

namespace App\Services;

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

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


    /**
     * 填充缓存架
     *
     * @param \Illuminate\Database\Eloquent\Collection $stations
     */
    public function paddingCacheShelf($stations)
    {
        $collection = new Collection();
        $stationCollection = new Collection();
        $blacklist = [];
        foreach ($stations as $station){
            $box = app("MaterialBoxService")->getAnEmptyBox($blacklist);
            if (!$box)continue;
            $task = StationTask::query()->create([
                'status' => "待处理",
                'station_id' => $station->id,
            ]);
            $collection->add(StationTaskMaterialBox::query()->create([
                'station_id' => $station->id,
                'material_box_id'=>$box->id,
                'status'=>"待处理",
                'type' => '取',
                'station_task_id' => $task->id,
            ]));
            $stationCollection->add($station->code);
            $blacklist[] = $box->id;
        }
        app("ForeignHaiRoboticsService")->fetchGroup_multiLocation($stationCollection,$collection,'','立架出至缓存架');
    }

    /**
     * 缓存架放置记录
     *
     * @param StationTaskMaterialBox|\stdClass $stationTaskMaterialBox
     */
    public function putCacheShelf($stationTaskMaterialBox)
    {
        DB::beginTransaction();
        try{
            $storage = Storage::query()->where("material_box_id",$stationTaskMaterialBox->material_box_id)->lockForUpdate()->first();
            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,
            ]);
            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")->stationLightUp($stationTaskMaterialBox->station->code,null,'2','2');
    }

    /**
     * 检查存储 根据事务表做处理
     *
     * @param Station|\stdClass $station
     *
     * @return bool
     *
     * @throws
     */
    public function checkStorage($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
                $asns = app("StorageService")->getFluxTask($task->doc_code,$task->bar_code,$task->amount);
                if (!$asns)return false;
                $ide = $task->materialBox->code;
                DB::connection("oracle")->beginTransaction();
                try{
                    foreach ($asns as $asn)if (!$this->fluxPA($asn,$ide,(int)$asn->fmqty)){
                        DB::connection("oracle")->rollBack();
                        return false;
                    };
                    DB::connection("oracle")->commit();
                }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,
                    "user_id" => Auth::id(),
                ]);//标记事务完成
                $collection = new Collection([$taskMaterialBox]);
                app("ForeignHaiRoboticsService")->fetchGroup($station->code,$collection,'','缓存架入立架'); //呼叫机器人入库
                DB::commit();
                return true;
            }catch(\Exception $e){
                DB::rollBack();
                return false;
            }
        }
        return true;
    }

    /**
     * 建立入库任务
     *
     * @param $stationId
     * @param $boxId
     *
     * @return Model|\stdClass
     */
    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){
                $obj["amount"] = DB::raw("amount+{$amount}");
                $storage->update($obj);
                $amount = (int)$storage->amount + (int)$amount;
            } 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)$model->update(["maximum"=>$amount]);
            }
            DB::commit();
            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($asn,$barCode,$amount):?array
    {
        $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)return null;
        $nums = [];
        foreach ($asns as $index=>$asn){
            if ((int)$asn->fmqty == $amount)return [$asn];
            $nums[] = (int)$asn->fmqty;
        }
        $result = $this->twoSum($nums,$amount);
        if ($result)return [$asns[$result[0]],$asns[$result[1]]];
        return null;
    }

    /**
     * 获取匹配数字
     *
     * @param Integer[] $nums
     * @param Integer $target
     * @return Integer[]|null
     */
    protected 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;
    }

    /**
     * flux上架
     *
     * @param $asn
     * @param $ide
     * @param $amount
     * @return bool
     * @throws \Throwable
     */
    public function fluxPA($asn,$ide,$amount)
    {
        if (!$asn->taskid)return false;//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)return false;//余量与入库不符
        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;
            list($trid,$max) = $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($max);
            $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]);
                }
            }
        }
        return true;
    }

    /**
     * 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;
        }
    }
}