baoshi-was/app/Services/HandInStorageService.php

<?php

namespace App\Services;

use App\CommodityBarcode;
use App\OracleActTransactionLog;
use App\OracleBasCode;
use App\OracleBasCustomer;
use App\OracleBasForwardingLoc;
use App\OracleBasLocation;
use App\OracleBasLotId;
use App\OracleBasSKU;
use App\OracleDOCASNDetail;
use App\OracleDOCASNHeader;
use App\OracleInvLotAtt;
use App\OracleInvLotLocId;
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;
use Monolog\Handler\IFTTTHandler;
use phpDocumentor\Reflection\Types\Object_;

class HandInStorageService
{
    use ServiceAppAop;

    /**
     * @param $asnno
     * @return object
     * 根据asn单号获取 总预期数量 总已收数量
     */
    public function getAsnQty($asnno)
    {
        $asnQty=OracleDOCASNDetail::query()
            ->select('expectedqty','receivedqty')
            ->where('asnno',$asnno)
            ->get();
        $expectedqty=0;
        $receivedqty=0;
        foreach ($asnQty as $qty){
            $expectedqty+=$qty->expectedqty;
            $receivedqty+=$qty->receivedqty;
        }
        return (object)array('expectedqty'=>$expectedqty,'receivedqty'=>$receivedqty);
    }
    /**
     * @param array $info
     * @return bool|int
     * 校验货主拣货位
     */
    public function checkForwardingLoc(array $info)
    {
        $res=OracleBasCustomer::query()
            ->where('customerid',$info['customerid'])
            ->where('customer_type','OW')
            ->where('udf1','Y')->count(); //查询此货主是否必须有拣货位
        if ($res>0) {
            $amount=OracleBasForwardingLoc::query()
                ->where('customerid',$info['customerid'])
                ->where('sku',$info['sku'])
                ->count();
            if ($amount==0)return 1;//请维护拣货位！
        }
        return true;
    }
    /**
     * @param array $info
     * @return bool|int
     * 校验产品档案长宽高
     */
    public function checkWidthHeight(array $info)
    {
        $basSku=OracleBasSKU::query()->where('customerid',$info['customerid'])->where('sku',$info['sku'])->first();
        if (!$basSku)return 1;//需要维护产品档案
        if ($basSku->skulength<=0||$basSku->skuwidth<=0||$basSku->skuhigh<=0)return 2;//需要维护该产品档案中的长宽高
        return true;
    }

    /**
     * @param array $info
     * @return bool|int
     * 校验产品档案重量体积
     */
    public function checkCubicWeight(array $info)
    {
        $basSku=OracleBasSKU::query()->where('customerid',$info['customerid'])->where('sku',$info['sku'])->first();
        if (!$basSku)return 1;//需要维护产品档案
        if ($basSku->grossweight<=0||$basSku->cube<=0)return 2;//需要维护该产品档案中的重量体积
        return true;
    }
    /**
     * @param array $info
     * @param array $param
     * @return bool|int
     * 校验库位
     */
    public function checkLocation(array $info, array $param)
    {
        $location = OracleBasLocation::query()
            ->where('locationid', $info['location'])
            ->where('status', 'OK')
            ->first();

        if (!$location) return 1;//库位不存在

        if ($location['mix_flag'] == 'N' && $location['mix_lotflag'] == 'N') { // 库位：产品和批次都不可混放
            $inv = OracleInvLotLocId::query()->where('locationid', $info['location'])->first();

            if (!$inv) return true; //当前库位无库存余量 可直接入库

            if ($inv['customerid'] == $param['customerid'] && $inv['sku'] == $param['sku']
                && $inv['lotnum'] == $param['plantolotnum'])
                return true;
            else return 2; //库位：产品和批次不可混放
        }


        if ($location['mix_flag'] == 'Y' && $location['mix_lotflag'] == 'N') {//库位：产品可混放，批次不可
            $invs = OracleInvLotLocId::query()->where('locationid', $info['location'])->get();

            if ($invs->count() == 0) return true; //当前库位无库存余量 可直接入库

            $skuInvs = [];    // 库位没有该商品
            foreach ($invs as $inv) {
                if ($inv['customerid'] != $param['customerid'] && $inv['sku'] != $param['sku']) {  // 库位没有该商品
                    $skuInvs[] = $inv;
                    continue;
                }
                if ($inv['lotnum'] == $param['plantolotnum']) return true; // 批次相同
                return 3;   //库位：产品相同，不能混放批次
            }
            if (count($skuInvs) == count($invs)) return true;
        }
        if ($location['mix_flag'] == 'N' && $location['mix_lotflag'] == 'Y') {  //库位：产品不可混放，批次可混放
            $inv = OracleInvLotLocId::query()->where('locationid', $info['location'])->first();
            if (!$inv) return true; //当前库位无库存余量 可直接入库

            if ($inv['customerid'] == $param['customerid'] && $inv['sku'] == $param['sku']) return true;
            else return 4; //库位：产品不能混放
        }
        // 库位
        return true;
    }

    /**
     * @param array $info
     * @return int|mixed
     * 校验asn单号是否能收货
     */
    public function checkAsnOperation(array $info)
    {
        if (!$info['customerid'] || !$info['asntype']) {
            $asn = OracleDOCASNHeader::query()
                ->select(['asnno', 'asnreference1', 'asnstatus', 'addtime', 'customerid', 'asntype'])
                ->where('asnno', $info['asnno'])
                ->whereIn('asnstatus', ['00', '30'])
                ->first();
            if (!$asn) return 1; //无效asn单号
            return $this->whetherDeliver($asn);
        }
        return $this->whetherDeliver($info);
    }

    private function whetherDeliver($asn)
    {
        if ($asn['asntype'] != 'XNRK' && $asn['asntype'] != 'THRK' && $asn['asntype'] != 'F31') {
            $res = app(DeliveryAppointmentService::class)->checkOperableAsn($asn['asnno'], $asn['customerid']);
            if ($res) return $asn;
            else return 2; //当前asn单号无预约记录
        }
        return $asn;
    }

    /**
     * @param $asn
     * @return Builder[]|Collection
     * 获取富勒asn_header  根据货主，asn，或者条码
     *
     */
    public function selectAsn($asn)
    {
        if (!$asn) return OracleDOCASNHeader::query()  //空扫
            ->select(['asnno', 'asnreference1', 'asnstatus', 'addtime', 'customerid', 'asntype','notes'])
            ->where('asnstatus', '00')
            ->orderByDesc('addtime')
            ->limit(50)
            ->get();

        if (strpos(strtoupper($asn), 'ASN') !== false) {  //asn 单号
            return OracleDOCASNHeader::query()
                ->select(['asnno', 'asnreference1', 'asnstatus', 'addtime', 'customerid', 'asntype','notes'])
                ->where('asnno', $asn)
                ->whereIn('asnstatus', ['00', '30'])
                ->get();
        } else {
            $asns = OracleDOCASNHeader::query()  //货主
                ->select(['asnno', 'asnreference1', 'asnstatus', 'addtime', 'customerid', 'asntype','notes'])
                ->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,DOC_ASN_HEADER.asntype,DOC_ASN_HEADER.notes 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.SKU in (select SKU from BAS_SKU where ALTERNATE_SKU1=? union
                      select SKU from BAS_SKU where ALTERNATE_SKU2=? union
                      select SKU from BAS_SKU where ALTERNATE_SKU3=? )
group by DOC_ASN_HEADER.ASNNO,DOC_ASN_HEADER.addtime,DOC_ASN_HEADER.asnreference1,DOC_ASN_HEADER.customerid,DOC_ASN_HEADER.asnstatus,DOC_ASN_HEADER.asntype,DOC_ASN_HEADER.notes
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
     *根据sku 或者条码获取asn_detail
     */
    public function getAsnDetail($asnno, $skuOrBarcode)
    {
        $sql = <<<sql
SELECT DOC_ASN_DETAILS.sku,DOC_ASN_DETAILS.expectedqty,DOC_ASN_DETAILS.skudescrc,
       DOC_ASN_DETAILS.lotatt01, DOC_ASN_DETAILS.lotatt02, DOC_ASN_DETAILS.lotatt03, DOC_ASN_DETAILS.lotatt04,
       DOC_ASN_DETAILS.lotatt05, DOC_ASN_DETAILS.lotatt06, DOC_ASN_DETAILS.lotatt07, DOC_ASN_DETAILS.lotatt08,
       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',
                'lotatt01','lotatt02','lotatt03','lotatt04','lotatt05','lotatt06','lotatt07','lotatt08'])
            ->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 $barcode
     * @return array
     * 根据条码获取 库存
     */
    public function getInvotlocid($barcode): array
    {
        $sql=<<<sql
select INV_LOT_LOC_ID.CUSTOMERID,BAS_SKU.ALTERNATE_SKU1,INV_LOT_LOC_ID.LOCATIONID,INV_LOT_ATT.LOTATT05,INV_LOT_ATT.LOTATT08,
       INV_LOT_ATT.LOTATT01,INV_LOT_ATT.LOTATT02,INV_LOT_ATT.LOTATT03,INV_LOT_ATT.LOTATT04,
       sum(INV_LOT_LOC_ID.QTY) AS QTY from INV_LOT_LOC_ID
    left join BAS_SKU on INV_LOT_LOC_ID.CUSTOMERID=BAS_SKU.CUSTOMERID and INV_LOT_LOC_ID.SKU =BAS_SKU.SKU
    left join INV_LOT_ATT on INV_LOT_ATT.LOTNUM=INV_LOT_LOC_ID.LOTNUM
    where BAS_SKU.SKU in (select SKU from BAS_SKU where ALTERNATE_SKU1=? union
                      select SKU from BAS_SKU where ALTERNATE_SKU2=? union
                      select SKU from BAS_SKU where ALTERNATE_SKU3=? )
group by INV_LOT_LOC_ID.CUSTOMERID,BAS_SKU.ALTERNATE_SKU1,INV_LOT_LOC_ID.LOCATIONID,INV_LOT_ATT.LOTATT05,INV_LOT_ATT.LOTATT08,INV_LOT_ATT.LOTATT01,INV_LOT_ATT.LOTATT02,INV_LOT_ATT.LOTATT03,INV_LOT_ATT.LOTATT04
sql;
        $invLots = DB::connection("oracle")->select(DB::raw($sql), [$barcode, $barcode, $barcode]);
        if (!$invLots)return [];
        else return $invLots;
    }

    /**
     * @param string $barCode
     * @return array|int
     * 根据商品条码  获取完全收货状态  部分收货状态的 PA任务
     */
    public function getTsk($trackNumber,$barCode): array
    {
        $sql = <<<sql
select t.*, DOC_ASN_DETAILS.RECEIVEDQTY
from (select TSK_TASKLISTS.CustomerID,
             TSK_TASKLISTS.DOCNO,
             TSK_TASKLISTS.Sku,
             TSK_TASKLISTS.PlanToLotNum,
             TSK_TASKLISTS.PlanToID,
             DOC_ASN_DETAILS.SKUDESCRC,
             TSK_TASKLISTS.DOCLINENO,
             sum(TSK_TASKLISTS.PlanToQty) as qty
      from TSK_TASKLISTS
               LEFT JOIN DOC_ASN_DETAILS ON DOC_ASN_DETAILS.ASNNO = TSK_TASKLISTS.DOCNO AND
                                            DOC_ASN_DETAILS.ASNLINENO = TSK_TASKLISTS.DOCLINENO
      where TSK_TASKLISTS.TASKTYPE = 'PA'
        AND TSK_TASKLISTS.TASKPROCESS = '00'
sql;
        if (!$trackNumber){ //没有输入条件  空扫
            $owner_codes = app('OwnerService')->getIntersectPermitting(['code']);
            if (count($owner_codes)>0){
                $sql.=' AND TSK_TASKLISTS.CustomerID IN (';
                foreach ($owner_codes as $index => $no){
                    if ($index==0){
                        $sql.="'".$no->code."'";
                        continue;
                    }
                    $sql.=",'".$no->code."'";
                }
                $sql.=')';
            }else{
                $sql .= ' AND TSK_TASKLISTS.CustomerID IS NULL ';
            }

        }else{
            if (strpos(strtoupper($trackNumber), 'ASN') !== false){
                $sql.='AND TSK_TASKLISTS.DOCNO= ?'; //输入条件为asn单号
            } else{ //不为asn号时 判断是否为货主
                if ($this->checkUserOwnerAuth($trackNumber)){ //输入条件为货主
                    $sql.=' AND TSK_TASKLISTS.CustomerID= ?';
                }else{
                    $sql.=' AND TSK_TASKLISTS.PlanToID= ?';  //不是货主 判断是否为跟踪号
                }
            }
        }

        if ($barCode)$sql.=" AND TSK_TASKLISTS.sku in (select SKU from BAS_SKU where ALTERNATE_SKU1='".$barCode."' union
                                  select SKU from BAS_SKU where ALTERNATE_SKU2='".$barCode."' union
                                  select SKU from BAS_SKU where ALTERNATE_SKU3='".$barCode."' )";

        $sql.=' group by TSK_TASKLISTS.CustomerID, TSK_TASKLISTS.DOCNO, TSK_TASKLISTS.Sku, TSK_TASKLISTS.PlanToLotNum,
               TSK_TASKLISTS.PlanToID, DOC_ASN_DETAILS.SKUDESCRC, TSK_TASKLISTS.DOCLINENO) t
         left join DOC_ASN_DETAILS on t.DOCLINENO = DOC_ASN_DETAILS.ASNLINENO and t.DOCNO = DOC_ASN_DETAILS.ASNNO';
        if ($trackNumber){
            if ($this->checkUserOwnerAuth($trackNumber))$tasks = DB::connection("oracle")->select(DB::raw($sql), [strtoupper($trackNumber)]);
            else $tasks = DB::connection("oracle")->select(DB::raw($sql), [$trackNumber]);
        }else {$tasks = DB::connection("oracle")->select(DB::raw($sql));}
        if (!$tasks) return [];
        else return $tasks;
    }

    /**
     * @param $owner_code
     * @return bool
     * 判断当前用户货主权限
     */
    public function checkUserOwnerAuth($owner_code): bool
    {
        $owner_codes = app('OwnerService')->getIntersectPermitting(['code']);
        $owner=$owner_codes->where('code','=',strtoupper($owner_code));
        if ($owner->count()>0)return true;
        else return false;
    }

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

    /**
     * @param $task
     * @param $location
     * @return bool|int
     * 上架校验效期
     */
    public function checkExpiryPa($task,$location)
    {
        if (!$task->taskid)return 0;//任务id不存在
        if (strpos($task->taskid,'MIX') !== false)return true;//合并拣货，不校验

        $sql = <<<sql
select instr(DESCR,'拣货') as var_IsPickingArea from BAS_Zone where ZONE=(select PutawayZone from BAS_Location where LocationID = ?)
sql;
        $basZone = DB::connection("oracle")->selectOne(DB::raw($sql), [$location]);
        if ($basZone&&$basZone->var_ispickingarea>0) return true; //不是存储区，不校验

        $sql1 = <<<sql
select SKU,LotAtt02,CustomerID from INV_LOT_ATT WHERE LOTNUM=?
sql;
        $invLotAtt = DB::connection("oracle")->selectOne(DB::raw($sql1), [$task->fmlotnum]);
        $sql2 = <<<sql
    select count(*) as var_amountOfDecaying from INV_LOT_LOC_ID store
                                                       left join BAS_Location location on store.LocationID=location.locationId
                                                       left join BAS_Zone zone on zone.zone=location.PickZone
                                                       left join INV_LOT_ATT attres on store.LOTNUM=attres.LOTNUM
    where store.SKU=?
      and store.CustomerID=?
      and store.LOTNUM!=?
      and attres.LotAtt02 > ?
      and zone.DESCR like '%拣货%'
sql;
        $invLotLocId=DB::connection("oracle")->selectOne(DB::raw($sql2), [$invLotAtt->sku,$invLotAtt->customerid,$task->fmlotnum,$invLotAtt->lotatt02]);
        if ($invLotLocId&&$invLotLocId->var_amountofdecaying>0)return 1;//拣货区找到效期更新的同样货品，不能上架至存储区
        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) return false;

            //flux 创建入库事务
            $actTransactionLog = $this->setFluxActTransactionLog($info, $lotNum, $who, $time);
            if (count($actTransactionLog)==0) 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 = ? 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,
//                    ]);
//                }
//
//            }
                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,
                ]);
        });
    }

    /**
     * @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('asnlineno',$info['asnlineno'])->where('sku', $info['sku'])->first();
            //添加超收判断
            $actTransactionLogQty=OracleActTransactionLog::query()->where('docno',$info['asnno'])
                ->where('doclineno',$info['asnlineno'])->where('fmsku', $info['sku'])->count('fmqty');
            if ((int)($info['amount']+$actTransactionLogQty)>(int)$asnDetail['expectedqty'])return [];

            $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")]);
    }

    /**
     * @param $param
     * @return array
     * 获取库存信息
     */
    public function getInventoryInfos($param): array
    {
        $sql = <<<sql
select BAS_SKU.SKU,INV_LOT_LOC_ID.CUSTOMERID,BAS_SKU.ALTERNATE_SKU1,INV_LOT_LOC_ID.LOCATIONID,INV_LOT_ATT.LOTATT05,INV_LOT_ATT.LOTATT08,
       INV_LOT_ATT.LOTATT01,INV_LOT_ATT.LOTATT02,INV_LOT_ATT.LOTATT03,INV_LOT_ATT.LOTATT04,
       sum(INV_LOT_LOC_ID.QTY) AS QTY from INV_LOT_LOC_ID
    left join BAS_SKU on INV_LOT_LOC_ID.CUSTOMERID=BAS_SKU.CUSTOMERID and INV_LOT_LOC_ID.SKU =BAS_SKU.SKU
    left join INV_LOT_ATT on INV_LOT_ATT.LOTNUM=INV_LOT_LOC_ID.LOTNUM where
sql;
        if ($this->checkUserOwnerAuth($param)) { //输入条件为货主
            $sql .= ' INV_LOT_LOC_ID.CUSTOMERID= ?';
        } else if (preg_match('/^[A-Z]{1,3}[0-9]{2,3}[-][0-9]{2,3}[-][0-9]{2,3}$/', $param)) { //判断是否为库位
            $sql .= ' INV_LOT_LOC_ID.LOCATIONID= ?';
        } else {
            $sql .= " BAS_SKU.SKU in (  select SKU from BAS_SKU where ALTERNATE_SKU1=? union
                                        select SKU from BAS_SKU where ALTERNATE_SKU2=? union
                                        select SKU from BAS_SKU where ALTERNATE_SKU3=? union
                                        select SKU from BAS_SKU where SKU=? )";
        }

        $sql .= ' group by BAS_SKU.SKU,INV_LOT_LOC_ID.CUSTOMERID,BAS_SKU.ALTERNATE_SKU1,INV_LOT_LOC_ID.LOCATIONID,
        INV_LOT_ATT.LOTATT05,INV_LOT_ATT.LOTATT08,INV_LOT_ATT.LOTATT01,INV_LOT_ATT.LOTATT02,INV_LOT_ATT.LOTATT03,INV_LOT_ATT.LOTATT04';
        if ($this->checkUserOwnerAuth($param)){
            $invLots = DB::connection("oracle")->select(DB::raw($sql), [strtoupper($param)
            ]);
        }else if(preg_match('/^[A-Z]{1,3}[0-9]{2,3}[-][0-9]{2,3}[-][0-9]{2,3}$/', $param)){
            $invLots = DB::connection("oracle")->select(DB::raw($sql), [$param]);
        }else{
            $invLots = DB::connection("oracle")->select(DB::raw($sql), [$param,$param,$param,$param]);
        }
        if (!$invLots) return [];
        else return $invLots;
    }
}