baoshi-was/app/Services/HandInStorageService.php

<?php

namespace App\Services;

use App\OracleBasCode;
use App\OracleBasSKU;
use App\OracleDOCASNDetail;
use App\OracleDOCASNHeader;
use App\OracleInvLotAtt;
use App\Traits\ServiceAppAop;
use App\ValueStore;
use Carbon\Carbon;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Collection;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\DB;

class HandInStorageService
{
    use ServiceAppAop;

    /**
     * @param $asn
     * @return Builder[]|Collection
     * 获取富勒asn_header
     *
     */
    public function selectAsn($asn)
    {
        if ($asn) return OracleDOCASNHeader::query()
            ->with('asnStatus')
            ->select(['asnno','asnreference1','asnstatus','addtime','customerid'])
            ->where('asnno',$asn)
            ->get();

        return OracleDOCASNHeader::query()
            ->select(['asnno','asnreference1','asnstatus','addtime','customerid'])
            ->with('asnStatus')
            ->where('asnstatus','00')
            ->orderByDesc('addtime')
            ->limit(50)
            ->get();
    }

    /**
     * @param $asnno
     * @return Builder[]|Collection
     * 获取富勒asn_detail (集合)
     */
    public function selectAsnDetails($asnno)
    {
        return OracleDOCASNDetail::query()
            ->select(['sku','expectedqty','skudescrc','asnlineno','asnno','receivedqty'])
            ->where('asnno',$asnno)
            ->get();
    }

    /**
     * @param $asnno
     * @param $sku
     * @return Builder|Model|object|null
     *
     */
    public function getAsnDetail($asnno,$sku)
    {
        return OracleDOCASNDetail::query()
            ->select(['sku','expectedqty','skudescrc','asnlineno','asnno','receivedqty'])
            ->where('asnno',$asnno)
            ->where('sku',$sku)
            ->first();
    }

    /**
     * @return mixed
     * 获取质量状态
     */
    public function getQualityStatus()
    {
        return Cache::remember('BAS_CODE_QLT_STS', 600, function () {
            return OracleBasCode::query()->select(['codeid','code','codename_c'])
                ->where('codeid','QLT_STS')
                ->get();
        });
    }

    /**
     * @return mixed
     * 获取属性仓
     */
    public function getAttributeLocation()
    {
        return Cache::remember('BAS_CODE_CUS_UDFPC', 600, function () {
            return OracleBasCode::query()->select(['codeid','code','codename_c'])
                ->where('codeid','CUS_UDFPC')
                ->get();
        });
    }

    /**
     * @param $customerid
     * @param $sku
     * @return mixed
     * 根据customerid和sku 查询商品关联的批次属性规则
     */
    public function getBasSkuLotId($customerid,$sku)
    {
        return Cache::remember('bas_sku_lot_'.$customerid.'_'.$sku, 600, function ()use($customerid,$sku) {
            return OracleBasSKU::query()->select(['customerid','sku','lotid'])
                ->where('customerid',$customerid)
                ->where('sku',$sku)
                ->with('lotId')
                ->first();
        });
    }

    /**
     * @throws \Throwable
     */
    public function fluxHandIn(array $info)
    {
        $lotatt=array_filter($info,function ($key){
            return strpos($key,'lotatt')===0;
        },ARRAY_FILTER_USE_KEY);
        $invlotatt=[];
        for ($i=1;$i<=8;$i++){$invlotatt["lotatt0{$i}"]=null;}
        foreach ($invlotatt as $key=>&$item){
            foreach ($lotatt as $key1=>$item1){
                if ($key===$key1) $item=$item1;
            }
        }
        $who= 'WAS'.(Auth::user() ? '-'.Auth::user()["name"] : '');
        $time=Carbon::now()->toDateTimeString();
        return DB::connection("oracle")->transaction(function ()use ($info,$invlotatt,$who,$time){
            //flux 批次号
            $lotNum=$this->getOrCreateLotNum($info,$invlotatt,$who,$time);
            if (!$lotNum) return false;
            //flux 创建入库事务
            $actTransactionLog=$this->setFluxActTransactionLog($info,$lotNum,$who,$time);
            if (!$actTransactionLog)return false;
            //flux 创建上架任务
            $this->setFluxTskTaskListPA($info,$invlotatt, $actTransactionLog, $who, $time);
            //flux 完善库存余量
            $this->updateFluxInv($info,$lotNum,$who,$time,$actTransactionLog);
            //flux 更新asn_detail 和 asn_header 状态
            return $this->updateFluxAsn($info,$invlotatt,$time,$who);
        });

    }

    /**
     * @throws \Throwable
     */
    public function updateFluxAsn(array $info, array $invlotatt, $time, $who): bool
    {
        $db=DB::connection("oracle");
        $asn=OracleDOCASNHeader::query()
            ->withCount('asnDetails')
            ->with('asnDetails')
            ->where('asnno',$info['asnno'])
            ->first();
        if (!$asn||!$asn->asnDetails||!$asn->asn_details_count) return false;
        $asnDetails=$asn->asnDetails;
        $receiveAsn=null;
        foreach ($asnDetails as $asnDetail){
            if ($asnDetail['asnno']==$info['asnno'] &&
                $asnDetail['asnlineno']==$info['asnlineno'] &&
                $asnDetail['customerid']==$info['customerid'] &&
                $asnDetail['sku']==$info['sku']) $receiveAsn=$asnDetail;
        }
        return $db->transaction(function ()use($db,$info,$receiveAsn,$invlotatt,$time,$who,$asn){
            if ($receiveAsn && (int)$receiveAsn['receivedqty']+(int)$info['amount']<(int)$receiveAsn['expectedqty']){
                //asn_detail 收货数量+已收数量<预期数量
                $db->update(DB::raw("UPDATE DOC_ASN_DETAILS SET receivedqty = receivedqty + ?,receivedqty_each = receivedqty_each + ?,linestatus = '30',holdrejectcode ='OK',
                reserve_flag ='Y',edittime = TO_DATE(?,'yyyy-mm-dd hh24:mi:ss'),receivedtime = TO_DATE(?,'yyyy-mm-dd hh24:mi:ss'),editwho = ?,
             lotatt01 =?,lotatt02 =?,lotatt03 =?,lotatt04 =?,lotatt05 =?,lotatt06 =?,lotatt07 =?,lotatt08=? WHERE asnno = ?"),
                    [(int)$info['amount'],(int)$info['amount'],$time,$time,$who,$invlotatt['lotatt01'],$invlotatt['lotatt02'],$invlotatt['lotatt03'],$invlotatt['lotatt04'],
                        $invlotatt['lotatt05'],$invlotatt['lotatt06'],$invlotatt['lotatt07'],$invlotatt['lotatt08'],$info['asnno']]);
                //asn_header 部分收货状态
                $db->update(DB::raw("UPDATE DOC_ASN_HEADER SET asnstatus = '30',edittime = TO_DATE(?,'yyyy-mm-dd hh24:mi:ss'),editwho = ? WHERE asnno = ?"),
                    [$time,$who,$info['asnno']]);
            }elseif ($receiveAsn && (int)$receiveAsn['receivedqty']+(int)$info['amount']==(int)$receiveAsn['expectedqty']){
                //asn_detail 收货数量+已收数量=预期数量
                $db->update(DB::raw("UPDATE DOC_ASN_DETAILS SET receivedqty=receivedqty+?,receivedqty_each=receivedqty_each+?,linestatus = '40',
                edittime = TO_DATE(?,'yyyy-mm-dd hh24:mi:ss'),receivedtime = TO_DATE(?,'yyyy-mm-dd hh24:mi:ss'),editwho = ?,holdrejectcode='OK',
                reserve_flag='Y',lotatt01=?,lotatt02=?,lotatt03=?,lotatt04=?,lotatt05=?,lotatt06=?,lotatt07=?,lotatt08=? WHERE asnno = ?"),
                    [(int)$info['amount'],(int)$info['amount'],$time,$time,$who,$invlotatt['lotatt01'],$invlotatt['lotatt02'],$invlotatt['lotatt03'],$invlotatt['lotatt04'],
                        $invlotatt['lotatt05'],$invlotatt['lotatt06'],$invlotatt['lotatt07'],$invlotatt['lotatt08'],$info['asnno']]);
                //当asn_detail 所有状态都为完全收货是  asn_header 状态修改为 完全收货（asnstatus=40）
                if (OracleDOCASNDetail::query()->where('asnno',$info['asnno'])->where('linestatus',40)->count()==$asn->asn_details_count){
                    $db->update(DB::raw("UPDATE DOC_ASN_HEADER SET asnstatus = '40',edittime = TO_DATE(?,'yyyy-mm-dd hh24:mi:ss'),editwho = ? WHERE asnno = ?"),
                        [$time,$who,$info['asnno']]);
                }
            }
            return true;
        });
    }
    /**
     * @throws \Throwable
     */
    public function updateFluxInv(array $info, $lotNum, $who, $time, array $actTransactionLog)
    {
        $db = DB::connection("oracle");
        $db->transaction(function ()use ($db,$info,$lotNum,$actTransactionLog,$who,$time){
            //更新 inv_lot 批次 库存表
            $invLot=$db->selectOne(DB::raw("SELECT * FROM INV_LOT WHERE lotnum = ? AND customerid = ? AND sku = ? "),[
                $lotNum,$info['customerid'],$info['sku']
            ]);
            if ($invLot)$db->update(DB::raw("UPDATE INV_LOT SET qty = qty+?,edittime=?,editwho=? WHERE lotnum = ? AND customerid = ? AND sku = ?"),[
                (int)$info['amount'],$time,$who,$lotNum,$info['customerid'],$info['sku'],
            ]);
            else $db->insert(DB::raw("INSERT INTO INV_LOT VALUES(?,?,?,?,0,0,0,0,0,0,0,TO_DATE(?,'yyyy-mm-dd hh24:mi:ss'),?,TO_DATE(?,'yyyy-mm-dd hh24:mi:ss'),?)"),[
                $lotNum,$info['customerid'],$info['sku'],$info['amount'],$time,$who, $time,$who
            ]);
            //更新 inv_lot_loc_id 批次/库位/跟踪号 库存表
            $invLotId = $db->selectOne(DB::raw("SELECT * FROM inv_lot_loc_id WHERE lotnum = ? AND locationid = ? AND customerid = ? AND sku = ? AND traceid = ? FOR UPDATE"),[
                $lotNum,$actTransactionLog['location'],$actTransactionLog['customerid'],$actTransactionLog['sku'],$actTransactionLog['trackid']
            ]);

            if ($info['location']){ //存在目标库位
                $invLotIdHasPreLocation = $db->selectOne(DB::raw("SELECT * FROM inv_lot_loc_id WHERE lotnum = ? AND locationid = ? AND customerid = ? AND sku = ? AND traceid = ? FOR UPDATE"),[
                    $lotNum,$info['location'],$actTransactionLog['customerid'],$actTransactionLog['sku'],$actTransactionLog['trackid']
                ]);

                if ($invLotIdHasPreLocation)$db->update(DB::raw("UPDATE inv_lot_loc_id SET qtypa = qtypa+?,edittime=?,editwho=? WHERE lotnum = ? AND locationid = ? AND traceid = ?"),[
                    (int)$info['amount'],$time,$who,$lotNum,$info['location'],$actTransactionLog['trackid']
                ]);
                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,'*',?,null)"),[
                    $lotNum,$info['location'],$actTransactionLog['trackid'],$actTransactionLog['customerid'],$actTransactionLog['sku'],0,$time,$who,$time,$who,(int)$info['amount']
                ]);

                if ($invLotId)$db->update(DB::raw("UPDATE inv_lot_loc_id SET qty = qty+?,qtymvout = qtymvout+?,edittime=?,editwho=? WHERE lotnum = ? AND locationid = ? AND traceid = ?"),[
                    (int)$info['amount'],(int)$info['amount'],$time,$who,$lotNum,$actTransactionLog['location'],$actTransactionLog['trackid']
                ]);
                else $db->insert(DB::raw("INSERT INTO inv_lot_loc_id VALUES(?,?,?,?,?,?,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)"),[
                    $lotNum,$actTransactionLog['location'],$actTransactionLog['trackid'],$actTransactionLog['customerid'],$actTransactionLog['sku'],(int)$info['amount'],(int)$info['amount'],
                    $time,$who,$time,$who,
                ]);

            }else{
                if ($invLotId)$db->update(DB::raw("UPDATE inv_lot_loc_id SET qty = qty+?,edittime=?,editwho=? WHERE lotnum = ? AND locationid = ? AND traceid = ?"),[
                    (int)$info['amount'],$time,$who,$lotNum,$actTransactionLog['location'],$actTransactionLog['trackid']
                ]);
                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)"),[
                    $lotNum,$actTransactionLog['location'],$actTransactionLog['trackid'],$actTransactionLog['customerid'],$actTransactionLog['sku'],(int)$info['amount'],$time,$who,$time,$who,
                ]);
            }
        });
    }

    /**
     * @throws \Throwable
     */
    public function setFluxTskTaskListPA(array $info, array $invlotatt, $actTransactionLog, $who, $time)
    {
        $db = DB::connection("oracle");
        $db->transaction(function ()use ($db,$info,$invlotatt,$actTransactionLog,$who,$time){
            $sql = <<<sql
INSERT INTO TSK_TASKLISTS VALUES(?,'1','PA',?,?,'ASN',?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,null,null,?,?,?,?,?,?,?,?,null,null,null,null,
    0,0,0,0,null,?,null,null,null,?,TO_DATE(?,'yyyy-mm-dd hh24:mi:ss'),null,null,?,TO_DATE(?,'yyyy-mm-dd hh24:mi:ss'),?,TO_DATE(?,'yyyy-mm-dd hh24:mi:ss'),'N',null,null,
    ?,?,?,'N',null,?,'*',null,null,null,'N',null,null)
sql;
            $db->insert(DB::raw($sql),[
                $actTransactionLog['tsid'],$actTransactionLog['customerid'],$actTransactionLog['sku'],$actTransactionLog['docno'],$actTransactionLog['doclineno'],
                $actTransactionLog['lotNum'],$actTransactionLog['packid'],'EA',$info['amount'],$info['amount'],$actTransactionLog['location'],$actTransactionLog['location'],
                $actTransactionLog['trackid'],$actTransactionLog['lotNum'],$actTransactionLog['packid'],'EA',$info['amount'],$info['amount'],
                $info['location'],$info['location'],$actTransactionLog['trackid'],'00','Putaway Task','3',$invlotatt['lotatt01'],$invlotatt['lotatt02'],$invlotatt['lotatt03'],$invlotatt['lotatt04'],
                $invlotatt['lotatt05'],$invlotatt['lotatt06'],$invlotatt['lotatt07'],$invlotatt['lotatt08'],$actTransactionLog['trid'],$who,$time,null,null,null,null,
                $actTransactionLog['userdefine1'],$actTransactionLog['userdefine2'],$actTransactionLog['userdefine3'],$actTransactionLog['warehouseid']
            ]);
        });
    }

    /**
     * @param array $info
     * @param $lotNum
     * @param $who
     * @param $time
     * @return mixed
     * @throws \Throwable
     * 创建入库事务
     */
    public function setFluxActTransactionLog(array $info,$lotNum,$who,$time)
    {
        $db = DB::connection("oracle");
        return $db->transaction(function ()use ($db,$info,$lotNum,$time,$who){
            if ($info['trackNumber'])$trackNumber=$info['trackNumber'];
            else $trackNumber=substr(md5($time),0,30);
            $asnHeader=OracleDOCASNHeader::query()->where('asnno',$info['asnno'])->first();
            $asnDetail=OracleDOCASNDetail::query()->where('asnno',$info['asnno'])->where('sku',$info['sku'])->first();
            $sql = <<<sql
INSERT INTO ACT_TRANSACTION_LOG VALUES(?,'IN',?,?,?,?,'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,?,?,?,?,?,?,?,?,
?,?,?,?,'1','Y',null,?,?,?,?,null,null,?,null,null)
sql;
            list($trid,$max) = app('StorageService')->getTrNumber();
            list($tsid,$max) = $this->getTsNum();
            $db->insert(DB::raw($sql),[
                $trid,$asnDetail->customerid,$asnDetail->sku,
                $asnDetail->asnno,$asnDetail->asnlineno,$lotNum,$asnDetail->receivinglocation,'*',$asnDetail->packid,'EA',$info['amount'],$info['amount'],'99',$time,$who,
                $time,$who,$time,$asnDetail->customerid,$asnDetail->sku,$trackNumber,$asnDetail->receivinglocation,$who,$asnDetail->packid,'EA',$info['amount'],$info['amount'],$lotNum,
                '*','0','N',$tsid,substr($asnDetail->receivinglocation,-4),$asnHeader->userdefine1,$asnHeader->userdefine2,
                $asnHeader->userdefine3,'O'
            ]);
            app('StorageService')->setTrNumber();
            $this->setTsNum();
            $actTransactionLog=[
                'trid'=>$trid,'docno'=>$asnDetail->asnno,'customerid'=>$asnDetail->customerid,'sku'=>$asnDetail->sku,'doclineno'=>$asnDetail->asnlineno,'lotNum'=>$lotNum,'location'=>$asnDetail->receivinglocation,
                'packid'=>$asnDetail->packid,'tsid'=>$tsid,'warehouseid'=>substr($asnDetail->receivinglocation,-4),'userdefine1'=>$asnHeader->userdefine1,'userdefine2'=>$asnHeader->userdefine2,
                'userdefine3'=>$asnHeader->userdefine3,'trackid'=>$trackNumber
            ];
            return $actTransactionLog;
        });
    }
    /**
     * @param array $info
     * @return mixed
     * @throws \Throwable
     * 或去flux 批次号
     */
    public function getOrCreateLotNum(array $info,array $invlotatt,$who,$time)
    {
        $invlotatt['customerid']=$info['customerid'];
        $invlotatt['sku']=$info['sku'];
        //根据批次规则查询或新建批次
        $lotnum=OracleInvLotAtt::query()->where($invlotatt)->value('lotnum');
        if ($lotnum) return $lotnum;

        $db = DB::connection("oracle");
        list($num,$max)=$this->getLtNum();
        return $db->transaction(function ()use($db,$info,$invlotatt,$num,$who,$time){
           $sql = <<<sql
INSERT INTO INV_LOT_ATT VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,TO_DATE(?,'yyyy-mm-dd hh24:mi:ss'),?,
TO_DATE(?,'yyyy-mm-dd hh24:mi:ss'),?,TO_DATE(?,'yyyy-mm-dd hh24:mi:ss'),?)
sql;
           $db->insert(DB::raw($sql),[
               $num,$info['customerid'],$info['sku'],$invlotatt['lotatt01'],$invlotatt['lotatt02'],$invlotatt['lotatt03'],$invlotatt['lotatt04'],
               $invlotatt['lotatt05'],$invlotatt['lotatt06'],$invlotatt['lotatt07'],$invlotatt['lotatt08'],null,null,null,null,$time,$who,$time,$who,$time,null
           ]);
           $this->setLtNum();
           return $num;
       });

    }

    /**
     * 获取批次号
     * @return array
     */
    private function getLtNum()
    {
        $val = ValueStore::query()->select("value")->where("name","flux_lt_number")->lockForUpdate()->first();
        if (!$val)$val = ValueStore::query()->create(["name"=>"flux_lt_number","value"=>'0']);
        $max = $val->value+1;
        $number = sprintf("%07d", $max);
        return array ('WLT'.$number,$max);
    }

    /**
     * 设置批次号
     */
    private function setLtNum()
    {
        ValueStore::query()
            ->select("value")
            ->where("name","flux_lt_number")
            ->update(["value"=>DB::raw("value+1")]);
    }
    /**
     * 获取批次号
     * @return array
     */
    private function getTsNum()
    {
        $val = ValueStore::query()->select("value")->where("name","flux_ts_number")->lockForUpdate()->first();
        if (!$val)$val = ValueStore::query()->create(["name"=>"flux_ts_number","value"=>'0']);
        $max = $val->value+1;
        $number = sprintf("%07d", $max);
        return array ('WTS'.$number,$max);
    }

    /**
     * 设置批次号
     */
    private function setTsNum()
    {
        ValueStore::query()
            ->select("value")
            ->where("name","flux_ts_number")
            ->update(["value"=>DB::raw("value+1")]);
    }
}