baoshi-was/app/Services/HandInStorageService.php

<?php

namespace App\Services;

use App\CommodityBarcode;
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 Doctrine\DBAL\Schema\AbstractAsset;
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()
            ->select(['asnno','asnreference1','asnstatus','addtime','customerid'])
            ->where('asnstatus','00')
            ->orderByDesc('addtime')
            ->limit(50)
            ->get();

        if ($asn && strpos(strtoupper($asn),'ASN')!==false){
            return OracleDOCASNHeader::query()
                ->select(['asnno','asnreference1','asnstatus','addtime','customerid'])
                ->where('asnno',$asn)
                ->get();
        }else {
            $asns=OracleDOCASNHeader::query()
                ->select(['asnno','asnreference1','asnstatus','addtime','customerid'])
                ->where('customerid',strtoupper($asn))
                ->whereIn('asnstatus',['00','30'])
                ->get();
            if ($asns->count()>0){
                return $asns;
            }else{
                $sql = <<<SQL
 SELECT DOC_ASN_HEADER.ASNNO,DOC_ASN_HEADER.addtime,DOC_ASN_HEADER.asnreference1,DOC_ASN_HEADER.customerid,DOC_ASN_HEADER.asnstatus FROM DOC_ASN_HEADER
                  LEFT JOIN DOC_ASN_DETAILS ON DOC_ASN_HEADER.ASNNO = DOC_ASN_DETAILS.ASNNO
                  LEFT JOIN BAS_SKU ON DOC_ASN_DETAILS.CUSTOMERID = BAS_SKU.CUSTOMERID AND DOC_ASN_DETAILS.SKU = BAS_SKU.SKU
WHERE DOC_ASN_HEADER.ASNSTATUS in ('00','30') and (BAS_SKU.ALTERNATE_SKU1 = ? OR BAS_SKU.ALTERNATE_SKU2 = ?  OR BAS_SKU.ALTERNATE_SKU3 = ?)
group by DOC_ASN_HEADER.ASNNO,DOC_ASN_HEADER.addtime,DOC_ASN_HEADER.asnreference1,DOC_ASN_HEADER.customerid,DOC_ASN_HEADER.asnstatus
SQL;
                return DB::connection("oracle")->select(DB::raw($sql),[$asn,$asn,$asn]);
            }
        }

    }

    /**
     * @param $asnno
     * @return Builder[]|Collection
     * 获取富勒asn_detail (集合)
     */
    public function selectAsnDetails($asnno)
    {
        $sql = <<<sql
 SELECT DOC_ASN_DETAILS.sku,DOC_ASN_DETAILS.expectedqty,DOC_ASN_DETAILS.skudescrc,DOC_ASN_DETAILS.asnlineno,DOC_ASN_DETAILS.asnno,
       DOC_ASN_DETAILS.receivedqty,BAS_SKU.alternate_sku1
FROM DOC_ASN_DETAILS LEFT JOIN BAS_SKU ON DOC_ASN_DETAILS.CUSTOMERID = BAS_SKU.CUSTOMERID AND DOC_ASN_DETAILS.SKU = BAS_SKU.SKU
WHERE asnno = ? AND linestatus IN ('00','30')
sql;
        $asn_details = DB::connection("oracle")->select(DB::raw($sql),[$asnno]);
        if (count($asn_details)>0)return $asn_details;
        else return array();
    }

    /**
     * @param $asnno
     * @param $skuOrBarcode
     * @return Builder|Model|object|null
     *
     */
    public function getAsnDetail($asnno,$skuOrBarcode)
    {
        $sql = <<<sql
SELECT DOC_ASN_DETAILS.sku,DOC_ASN_DETAILS.expectedqty,DOC_ASN_DETAILS.skudescrc,
       DOC_ASN_DETAILS.asnlineno,DOC_ASN_DETAILS.asnno,DOC_ASN_DETAILS.receivedqty FROM DOC_ASN_DETAILS
         LEFT JOIN BAS_SKU ON DOC_ASN_DETAILS.CUSTOMERID = BAS_SKU.CUSTOMERID AND DOC_ASN_DETAILS.SKU = BAS_SKU.SKU
WHERE ASNNO = ? AND LINESTATUS IN ('00','30') AND (ALTERNATE_SKU1 = ? OR ALTERNATE_SKU2 = ?  OR ALTERNATE_SKU3 = ?)
sql;
        $asn_detail = DB::connection("oracle")->selectOne(DB::raw($sql),[$asnno,$skuOrBarcode,$skuOrBarcode,$skuOrBarcode]);
        if ($asn_detail)return $asn_detail;
        else return OracleDOCASNDetail::query()
            ->select(['sku','expectedqty','skudescrc','asnlineno','asnno','receivedqty'])
            ->where('asnno',$asnno)
            ->where('sku',$skuOrBarcode)
            ->whereIn('linestatus',['00','30'])
            ->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();
        });
    }

    /**
     * @param string $barCode
     * @return array
     * 根据商品条码  获取完全收货状态  部分收货状态的 PA任务
     */
    public function getTsk(string $trackNumber,string $barCode): array
    {
        $sql = <<<sql
SELECT TSK_TASKLISTS.CustomerID,TSK_TASKLISTS.Sku,TSK_TASKLISTS.PlanToLotNum,TSK_TASKLISTS.PlanToID,DOC_ASN_DETAILS.SKUDESCRC,sum(TSK_TASKLISTS.PlanToQty) AS QTY
FROM DOC_ASN_DETAILS
         LEFT JOIN TSK_TASKLISTS ON DOC_ASN_DETAILS.ASNNO = TSK_TASKLISTS.DOCNO AND
                                    DOC_ASN_DETAILS.ASNLINENO = TSK_TASKLISTS.DOCLINENO
WHERE
      ASNNO in (SELECT asnno FROM DOC_ASN_DETAILS
                                      LEFT JOIN BAS_SKU ON DOC_ASN_DETAILS.CUSTOMERID = BAS_SKU.CUSTOMERID AND DOC_ASN_DETAILS.SKU = BAS_SKU.SKU
                WHERE LINESTATUS in ('30','40') and (ALTERNATE_SKU1 = ? OR ALTERNATE_SKU2 = ?  OR ALTERNATE_SKU3 = ?))
  AND TSK_TASKLISTS.TASKPROCESS = '00'
  AND TSK_TASKLISTS.TASKTYPE = 'PA'
  AND TSK_TASKLISTS.PlanToID= ?
  group by TSK_TASKLISTS.CustomerID,TSK_TASKLISTS.Sku,TSK_TASKLISTS.PlanToLotNum,TSK_TASKLISTS.PlanToID,DOC_ASN_DETAILS.SKUDESCRC
sql;
        $tasks = DB::connection("oracle")->select(DB::raw($sql),[$barCode,$barCode,$barCode,$trackNumber]);
        if (!$tasks)return [];
        else return $tasks;
    }

    /**
     * @throws \Throwable
     * flux手持端 上架
     */
    public function fluxHandPa(array $info, array $taskParam): bool
    {
        $tasks=$this->selectFluxTask($taskParam,$info['amount']);
//        dd($tasks);
        if (!$tasks)return false; //获取任务失败
        return DB::connection("oracle")->transaction(function ()use($tasks,$info){ //单体嵌套事务 回滚FLUX失败任务
            foreach ($tasks as $task){
                if (!app("StorageService")->fluxPA($task,$info['location'])){
                    DB::connection("oracle")->rollBack();
                    return false; //上架失败
                }
            }
            return true; //上架成功
        });
    }

    /**
     * @param $taskParam
     * @param $amount
     * @return array
     * 根据跟踪号，货主，sku，批次 获取任务列表   再通过数量 进行任务的重组（拆分或选定）
     */
    public function selectFluxTask($taskParam,$amount): array
    {
        /** @var StorageService $storageService  */
        $storageService=app('StorageService');
        $sql = <<<sql
select * from TSK_TASKLISTS where customerid = ? AND sku = ? AND plantoid = ? AND plantolotnum = ? AND TASKPROCESS = '00' AND TASKTYPE = 'PA'
sql;
        $tasks = DB::connection("oracle")->select(DB::raw($sql),[$taskParam['customerid'],$taskParam['sku'],$taskParam['plantoid'],$taskParam['plantolotnum']]);
        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 = $storageService->getMatch($nums,$amount);
        if (!$result)return $storageService->splitTask($tasks,$maxIndex,$amount);
        $arr = [];
        foreach ($result as $index)$arr[] = $tasks[$index];
        return $arr;
    }


    /**
     * @throws \Throwable
     * fulx 手持收货
     */
    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){
                DB::connection("oracle")->rollBack();
                return false;
            }
            //flux 创建入库事务
            $actTransactionLog=$this->setFluxActTransactionLog($info,$lotNum,$who,$time);
            if (!$actTransactionLog){
                DB::connection("oracle")->rollBack();
                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;
        }
//        dd($receiveAsn);
        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 = ? and asnlineno = ?"),
                    [(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'],$info['asnlineno']]);
                //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 = ? and asnlineno = ?"),
                    [(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'],$info['asnlineno']]);
                //当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']]);
                }else {
                    //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']]);
                }
            }
            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(): array
    {
        $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(): array
    {
        $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")]);
    }
}