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 Decimal\Decimal;
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): object
    {
        $asnQty = OracleDOCASNDetail::query()
            ->select('expectedqty', 'receivedqty','receivedqty_each','expectedqty_each')
            ->where('asnno', $asnno)
            ->get();
        $expectedqty=0;
        $receivedqty=0;
        foreach ($asnQty as $qty){
            if ($qty->expectedqty) {
                $expectedqty+=$qty->expectedqty;
            }else{
                $expectedqty+=$qty->expectedqty_each??0;
            }

            if ($qty->receivedqty){
                $receivedqty+=$qty->receivedqty;
            }else{
                $receivedqty+=$qty->receivedqty_each??0;
            }

        }
        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()->with('oracleInvLotAtt:lotnum,lotatt01,lotatt02,lotatt03,lotatt04,lotatt05,lotatt08')
                ->where('locationid', $info['location'])->first();

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

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


        if ($location['mix_flag'] == 'Y' && $location['mix_lotflag'] == 'N') {//库位：产品可混放，批次不可
            $invs = OracleInvLotLocId::query()->with('oracleInvLotAtt:lotnum,lotatt01,lotatt02,lotatt03,lotatt04,lotatt05,lotatt08')
                ->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']) { //货主相同,sku不同的商品
                    return true; //商品不同
                }
                if ($inv['customerid'] != $param['customerid'] && $inv['sku'] != $param['sku']) {  // 库位没有该商品
                    $skuInvs[] = $inv;
                    continue;
                }
                if ($inv['oracleInvLotAtt']['lotatt01'] == $param['lotatt01']
                    && $inv['oracleInvLotAtt']['lotatt02'] == $param['lotatt02']
                    && $inv['oracleInvLotAtt']['lotatt04'] == $param['lotatt04']
                    && $inv['oracleInvLotAtt']['lotatt05'] == $param['lotatt05']
                    && $inv['oracleInvLotAtt']['lotatt08'] == $param['lotatt08']) 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'])
                ->where('qty','>',0) //占用库位忽略当条库存余量
                ->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,DOC_ASN_DETAILS.receivedqty_each,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_each,DOC_ASN_DETAILS.skudescrc,DOC_ASN_DETAILS.expectedqty,
       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,DOC_ASN_DETAILS.receivedqty_each 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_each','expectedqty', 'skudescrc', 'asnlineno', 'asnno', 'receivedqty','receivedqty_each',
                '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,DOC_ASN_DETAILS.ReceivedQty_Each
from (select TSK_TASKLISTS.CustomerID,
             TSK_TASKLISTS.DOCNO,
             TSK_TASKLISTS.Sku,
             TSK_TASKLISTS.PlanToLotNum,
             TSK_TASKLISTS.PlanToID,
             DOC_ASN_DETAILS.SKUDESCRC,
             TSK_TASKLISTS.DOCLINENO,
             TSK_TASKLISTS.LOTATT01,
             TSK_TASKLISTS.LOTATT02,
             TSK_TASKLISTS.LOTATT03,
             TSK_TASKLISTS.LOTATT04,
             TSK_TASKLISTS.LOTATT05,
             TSK_TASKLISTS.LOTATT08,
             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 . "' union
                                    select SKU from BAS_SKU where SKU='" . $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, TSK_TASKLISTS.LOTATT01, TSK_TASKLISTS.LOTATT02, TSK_TASKLISTS.LOTATT03, TSK_TASKLISTS.LOTATT04, TSK_TASKLISTS.LOTATT05, TSK_TASKLISTS.LOTATT08) 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)
    {
        $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; //上架成功
        });
    }

    /**
     * ASN关闭后  针对JIANSHANG货主，订单关闭后将数据写入临时表CUS_ADJ_H
     */
    public function verifyAsnStatusAndInsert($asn)
    {
        $who = 'WAS' . (Auth::user() ? '-' . Auth::user()["name"] : '');
        $host = config('database.connections.oracle.host');
        $serviceName = config('database.connections.oracle.service_name');
        $user = config('database.connections.oracle.username');
        $password = config('database.connections.oracle.password');
        $conn = oci_connect($user, $password, $host . '/' . $serviceName, 'utf8');
        $IN_WarehouseID = '';
        $IN_Parameter1 = 'ASNCLOSE_AFTER';
        $IN_Parameter2 = $asn->asnno;
        $IN_Parameter3 = $asn->asnstatus;
        $IN_Parameter4 = '';
        $IN_Language = '';
        $IN_UserID = $who;
        $OUT_Return_Code = '';
        $sql_sp = "begin SPUDF_ProcessA(:IN_WarehouseID,:IN_Parameter1,:IN_Parameter2,:IN_Parameter3,:IN_Parameter4,:IN_Language,:IN_UserID,:OUT_Return_Code); end;";
        $stmt = oci_parse($conn, $sql_sp);
        oci_bind_by_name($stmt, ':IN_WarehouseID', $IN_WarehouseID);
        oci_bind_by_name($stmt, ':IN_Parameter1', $IN_Parameter1);
        oci_bind_by_name($stmt, ':IN_Parameter2', $IN_Parameter2);
        oci_bind_by_name($stmt, ':IN_Parameter3', $IN_Parameter3);
        oci_bind_by_name($stmt, ':IN_Parameter4', $IN_Parameter4);
        oci_bind_by_name($stmt, ':IN_Language', $IN_Language);
        oci_bind_by_name($stmt, ':IN_UserID', $IN_UserID);
        oci_bind_by_name($stmt, ':OUT_Return_Code', $OUT_Return_Code, 300);
        oci_execute($stmt);

        if (substr($OUT_Return_Code, 0, 3) != '000') {
            app('LogService')->log(__METHOD__, '上架调用sp生成异动数据失败' . __FUNCTION__, "asn:" . $asn . "ERROR：" . $OUT_Return_Code);
            return false;
        }
        oci_close($conn);
        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;
    }

    /**
     * @param array $info
     * @return bool|int
     * 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();
        $db = DB::connection("oracle");
        $db->beginTransaction();
        try {
            $asnHeader = OracleDOCASNHeader::query()->where('asnno', $info['asnno'])->first();
            $asnDetail = OracleDOCASNDetail::query()->where('asnno', $info['asnno'])
                ->where('asnlineno', $info['asnlineno'])->where('sku', $info['sku'])->first();
            // 1：flux 获取批次号
            $lotNum = $this->getOrCreateLotNum($info, $invlotatt, $who, $time);
            if (!$lotNum){$db->rollBack();return false;}

            // 2：flux 判断当前入库是否超收
            $res=$this->judgeOverCharge($info,$asnDetail);
            if (!$res) return 1; //超收

            // 3：flux 创建状态为创建的入库事务
            $actTransactionLog = $this->setFluxActTransactionLog($info, $lotNum, $who, $time,$asnHeader,$asnDetail);

            if (count($actTransactionLog) == 0){$db->rollBack();return false;}

            // 4：flux 新增一条上架任务记录
            $this->setFluxTskTaskListPA($info, $invlotatt, $actTransactionLog, $who, $time);

            // 5：flux 完善库存余量 入库完成修改库存余量
            $this->updateFluxInv($info, $lotNum, $who, $time, $actTransactionLog);

            // 6: flux 更新 asn_detail 和 asn_header 状态
            $result=$this->updateFluxAsn($info, $invlotatt, $time, $who,$asnDetail);
            if ($result===1){$db->rollBack();return 1;}//超收
            if (!$result){$db->rollBack();return false;}
            $db->commit();
            return true;
        } catch (\Exception $e) {
            $db->rollBack();
            app('LogService')->log(__METHOD__,__FUNCTION__,'收货异常'.json_encode($info).'|catch:'.$e->getMessage());
            return false;
        }

    }
    /**
     * @param array $info
     * @param $asnDetail
     * @return bool
     * 判断是否超收
     */
    public function judgeOverCharge(array $info,$asnDetail): bool
    {
        //添加超收判断
        $actTransactionLogQty = OracleActTransactionLog::query()->where('docno', $info['asnno'])
            ->where('status','99')->where('transactiontype','IN')//事务状态,类型限定
            ->where('doclineno', $info['asnlineno'])->where('fmsku', $info['sku'])->count('fmqty');
        $expectedQty = (int)($asnDetail['expectedqty']??$asnDetail['expectedqty_each']);
        if ((int)($info['amount'] + $actTransactionLogQty) > $expectedQty) return false;
        return true;
    }

    /**
     * @param array $info
     * @param array $invlotatt
     * @param $time
     * @param $who
     * @param $asnDetail
     * @return bool|int
     * 更新asn状态
     */
    public function updateFluxAsn(array $info, array $invlotatt, $time, $who,$asnDetail)
    {
        $db = DB::connection("oracle");
        $asn = OracleDOCASNHeader::query()
            ->withCount('asnDetails')
            ->where('asnno', $info['asnno'])
            ->first();
        if (!$asn || !$asn->asn_details_count) return false;


        if (!$asnDetail) return false;

        $receivedQty = (int)($asnDetail['receivedqty']??$asnDetail['receivedqty_each']);  // 已收货数量
        $amount = (int) $info['amount'];                    // 当前收货数量
        $expectedQty = (int)($asnDetail['expectedqty']??$asnDetail['expectedqty_each']);     // 预期数量
        if ($receivedQty+$amount>$expectedQty)return 1; //超收
        if ($receivedQty + $amount < $expectedQty) {
            // 已收货数量+当前收货数量 < 预期数量
            $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 ($receivedQty + $amount == $expectedQty) {
            // 已收货数量+当前收货数量 = 预期数量
            $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;
    }

    /**
     * @param array $info
     * @param $lotNum
     * @param $who
     * @param $time
     * @param array $actTransactionLog
     * 更新flux 库存
     */
    public function updateFluxInv(array $info, $lotNum, $who, $time, array $actTransactionLog)
    {
        $db = DB::connection("oracle");
        //更新 inv_lot 批次 库存表
        $invLot = $db->selectOne(DB::raw("SELECT * FROM INV_LOT WHERE lotnum = ? AND customerid = ? AND sku = ? FOR UPDATE"), [
            $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,
            ]);
        }
    }

    /**
     * @param array $info
     * @param array $invlotatt
     * @param $actTransactionLog
     * @param $who
     * @param $time
     * 生成上架任务
     */
    public function setFluxTskTaskListPA(array $info, array $invlotatt, $actTransactionLog, $who, $time)
    {
        $db = DB::connection("oracle");
        $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,
            SUBSTR($actTransactionLog['userdefine1'],1,40), $actTransactionLog['userdefine2'], $actTransactionLog['userdefine3'], $actTransactionLog['warehouseid']
        ]);
    }

    /**
     * @param array $info
     * @param $lotNum
     * @param $who
     * @param $time
     * @param $asnHeader
     * @param $asnDetail
     * @return array
     * 创建入库事务
     */
    public function setFluxActTransactionLog(array $info, $lotNum, $who, $time,$asnHeader,$asnDetail): array
    {
        $db = DB::connection("oracle");
        if ($info['trackNumber']) $trackNumber = $info['trackNumber'];
        else $trackNumber = substr(md5($time), 0, 30);
        $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,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
     * @param array $invlotatt
     * @param $who
     * @param $time
     * @return mixed
     * 获取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();
        $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) ||strpos($param,'IDE') !== false) { //判断是否为库位
            $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) ||strpos($param,'IDE') !== false){
            $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;
    }
}