baoshi-was/app/Services/InventoryAccountService.php

<?php


namespace App\Services;

use App\Commodity;
use App\Http\Controllers\Controller;
use App\InventoryAccount;
use App\InventoryAccountMission;
use App\OracleBasSKU;
use App\OracleInvLotAtt;
use App\OracleInvLotLocId;
use App\Owner;
use App\Services\common\BatchUpdateService;
use App\Services\common\DataHandlerService;
use App\Services\common\QueryService;
use Carbon\Carbon;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use App\Traits\ServiceAppAop;


class InventoryAccountService
{
    use ServiceAppAop;
    protected $modelClass=InventoryAccount::class;
    private function conditionQueryInventoryAccountMission(array $params){
        $inventoryAccountMissions=InventoryAccountMission::with(['commodity.barcodes','stockInventoryPersons'])->orderBy('difference_amount','desc');
        $columnQueryRules=[];
        $inventoryAccountMissions = app(QueryService::class)->query($params,$inventoryAccountMissions,$columnQueryRules,'inventory_account_missions');
        return $inventoryAccountMissions;
    }
    public  function getSql(array $params){
        return $this->conditionQueryInventoryAccountMission($params)->selectRaw("inventory_account_missions.*")
            ->leftJoin('signs','inventory_account_missions.id','signs.signable_id')
            ->selectRaw('signs.mark stockInventoryPerson')
            ->leftJoin('commodities','inventory_account_missions.commodity_id','commodities.id')
            ->selectRaw('commodities.name commodity_name,commodities.sku commodity_sku')
            ->leftJoin('commodity_barcodes','commodity_barcodes.commodity_id','commodities.id')
            ->selectRaw('commodity_barcodes.code commodity_barcode_code')
            ->sql();
    }
    private function conditionQuery($queryParam){
        $ownerIds=app('OwnerService')->getSelection();
        $inventories=InventoryAccount::query()->with(['owner','creator'])->orderBy('id','desc')->whereIn('owner_id',$ownerIds);
        $columnQueryRules=[
            'owner' => ['alias'=>'owner_id','multi' => ','],
            'type' => ['multi' => ','],
            'date_start' => ['alias' => 'created_at' , 'startDate' => ' 00:00:00'],
            'date_end' => ['alias' => 'created_at' , 'endDate' => ' 23:59:59'],
        ];
        $inventories = app(QueryService::class)->query($queryParam,$inventories,$columnQueryRules);
        return $inventories;
    }
    public function paginate($queryParam){
        $inventories = $this->conditionQuery($queryParam);
        return $inventories->paginate($queryParam['paginate'] ?? 50);
    }
    public function get($queryParam){
        $inventories = $this->conditionQuery($queryParam);
        return $inventories->get();
    }

    public function some($queryParam){
        return InventoryAccount::query()->with(['owner'])->orderBy('id','DESC')
            ->whereIn('id',explode(',',$queryParam['data']))->get();
    }
    //动盘查询
    public function conditionPortStock($date_start,$date_end,$ownerId,$location,$barcode){
        if (!$ownerId) return null;
        $code=Owner::where('id',$ownerId)->value('code');
        $sql='select * from (select result.*,rownum rn from (';
        $sql.=' select customer.Descr_C as 货主,stockLog.客户 客户, 库位, sku.SKU 产品编码, sku.ALTERNATE_SKU1 产品条码1, sku.ALTERNATE_SKU2 产品条码2, sku.ALTERNATE_SKU3 产品条码3, ';
        $sql.=' sku.Descr_C 商品名称, MAX(lot.LotAtt05) 属性仓, lot.LotAtt08 质量状态, lot.LotAtt02 失效日期, ';
        $sql.=' lot.LotAtt04 批号, lot.LotAtt01 生产日期';
        $sql.=' , sum(移出数量)移出数量, sum(移入数量)移入数量 ';
        $sql.=' , storeStatus.QTY 在库数量, storeStatus.QtyAllocated 占用数量,count(1) over () as sum from ';
        $sql.=' (select FMLotNum,FMSKU,TOCustomerID 客户,0 as 移出数量, sum(TOQty_Each) as 移入数量, TOLocation as 库位 ';
        $sql.=" from ACT_Transaction_Log where TransactionType='PA' ";
        if ($code)$sql.=" and TOCustomerID = '".$code."' ";
        if ($date_start) $sql.=" and addtime > to_date('".$date_start." 00:00:00','yyyy-mm-dd hh24:mi:ss') ";
        if ($date_end) $sql.=" and addtime < to_date('".$date_end." 23:59:59','yyyy-mm-dd hh24:mi:ss') ";
        $sql.=' group by TOCustomerID, TOLocation,FMSKU,FMLotNum union all ';
        $sql.=' select FMLotNum,FMSKU,FMCUSTOMERID 客户,sum(FMQty_Each) as 移出数量, 0 as 移入数量, FMLOCATION as 库位 ';
        $sql.=" from ACT_Transaction_Log where TransactionType='SO' ";
        if ($code)$sql.=" and FMCUSTOMERID = '".$code."' ";
        if ($date_start) $sql.=" and addtime > to_date('".$date_start." 00:00:00','yyyy-mm-dd hh24:mi:ss') ";
        if ($date_end) $sql.=" and addtime < to_date('".$date_end." 23:59:59','yyyy-mm-dd hh24:mi:ss') ";
        $sql.=' group by FMCustomerID, FMLocation,FMSKU,FMLotNum union all ';
        $sql.=' select FMLotNum,FMSKU,FMCUSTOMERID 客户,sum(FMQty_Each) as 移出数量,0 as 移入数量, FMLocation as 库位 ';
        $sql.=" from ACT_Transaction_Log  where TransactionType='MV' ";
        if ($code)$sql.=" and FMCUSTOMERID = '".$code."' ";
        if ($date_start) $sql.=" and addtime > to_date('".$date_start." 00:00:00','yyyy-mm-dd hh24:mi:ss') ";
        if ($date_end) $sql.=" and addtime < to_date('".$date_end." 23:59:59','yyyy-mm-dd hh24:mi:ss') ";
        $sql.=' group by FMLocation,FMCUSTOMERID,FMSKU,FMLotNum union all ';
        $sql.=' select FMLotNum,FMSKU,TOCustomerID 客户,0 as 移出数量,sum(TOQty_Each)as 移入数量, TOLocation as 库位 ';
        $sql.=" from ACT_Transaction_Log where TransactionType='MV' ";
        if ($code)$sql.=" and TOCustomerID = '".$code."' ";
        if ($date_start) $sql.=" and addtime > to_date('".$date_start." 00:00:00','yyyy-mm-dd hh24:mi:ss') ";
        if ($date_end) $sql.=" and addtime < to_date('".$date_end." 23:59:59','yyyy-mm-dd hh24:mi:ss') ";
        $sql.=' group by TOLocation,TOCustomerID,FMSKU,FMLotNum)stockLog ';
        $sql.=' left join BAS_Customer customer on customer.CustomerID=stockLog.客户 ';
        $sql.=' left join BAS_SKU sku on sku.SKU=stockLog.FMSKU and sku.CUSTOMERID=stockLog.客户 ';
        $sql.=' left join INV_LOT_ATT lot on lot.LOTNUM=stockLog.FMLOTNUM ';
        $sql.=' left join INV_LOT_LOC_ID storeStatus on storeStatus.LOTNUM=stockLog.FMLOTNUM ';;
        $sql.=' and storeStatus.LocationID=stockLog.库位 ';
        $sql.=' group by 库位,customer.Descr_C,sku.SKU,sku.ALTERNATE_SKU1,sku.ALTERNATE_SKU2,sku.ALTERNATE_SKU3 ';
        $sql.=' ,sku.Descr_C,FMLotNum,lot.LotAtt01,lot.LotAtt08,lot.LotAtt02,lot.LotAtt04 ';
        $sql.=' , storeStatus.QTY, storeStatus.QtyAllocated,stockLog.客户  ';
        $sql.=' )result where 1=1 ';
        if ($code)$sql.=" and 客户 = '".$code."' ";
//        if ($location)$sql.=" and 库位 like '".$location."%' ";
        if ($location){
            $arr=array_filter(preg_split('/[,， ]+/is', $location));
            if (count($arr)==1){
                $sql.=" and 库位 like '".$location."%'";
            }
            if (count($arr)>1){
                $sql.=" and (库位 like ";
                foreach ($arr as $index=>$str){
                    if ($index==0){
                        $sql.="'".$str."%'";
                        continue;
                    }
                    $sql.= "or 库位 like'".$str."%'";
                }
                $sql.=")";
            }
        }
        if ($barcode)$sql=$this->按条码查询($sql,$barcode);
        $sql.=' )  ';
        return DB::connection('oracle')->select($sql);
    }
    //全盘查询
    public function conditionTotalStock($ownerId,$location,$barcode){
        $code=Owner::where('id',$ownerId)->value('code');
        $sql='select * from (select result.* from (';
        $sql.=' select customer.Descr_C as 货主,storeStatus.CUSTOMERID 客户,storeStatus.LocationID 库位, sku.SKU 产品编码, sku.ALTERNATE_SKU1 产品条码1, sku.ALTERNATE_SKU2 产品条码2, sku.ALTERNATE_SKU3 产品条码3, ';
        $sql.=' sku.Descr_C 商品名称, MAX(lot.LotAtt05) 属性仓, lot.LotAtt08 质量状态, lot.LotAtt02 失效日期, ';
        $sql.=' lot.LotAtt04 批号,lot.LotAtt01 生产日期 ';
        $sql.=' , SUM(storeStatus.QTY) 在库数量, SUM(storeStatus.QtyAllocated) 占用数量 from ';
        $sql.=' INV_LOT_LOC_ID storeStatus';
        $sql.=' left join BAS_Customer customer on customer.CustomerID=storeStatus.CUSTOMERID ';
        $sql.=' left join BAS_SKU sku on sku.SKU=storeStatus.SKU and sku.CUSTOMERID=storeStatus.CUSTOMERID ';
        $sql.=' left join INV_LOT_ATT lot on lot.LOTNUM = storeStatus.LOTNUM AND lot.CUSTOMERID = storeStatus.CUSTOMERID ';
        $sql.=' group by storeStatus.LocationID,customer.Descr_C,sku.SKU,sku.ALTERNATE_SKU1,sku.ALTERNATE_SKU2,sku.ALTERNATE_SKU3 ';
        $sql.=' ,sku.Descr_C,lot.LotAtt08,lot.LotAtt02,lot.LotAtt04 ';
        $sql.=' ,storeStatus.CUSTOMERID,lot.LotAtt01 ';
        $sql.=' )result where 1=1 ';
        if ($code)$sql.=" and 客户 = '".$code."' ";
        if ($location){
            $arr=array_filter(preg_split('/[,， ]+/is', $location));
            if (count($arr)==1){
                $sql.=" and 库位 like '".$location."%'";
            }
            if (count($arr)>1){
                $sql.=" and (库位 like ";
                foreach ($arr as $index=>$str){
                    if ($index==0){
                        $sql.="'".$str."%'";
                        continue;
                    }
                    $sql.= "or 库位 like'".$str."%'";
                }
                $sql.=")";
            }
        }
        if ($barcode)$sql=$this->按条码查询($sql,$barcode);
        $sql.=' )  ';
        return DB::connection('oracle')->select($sql);

    }
    private function 按条码查询($sql,$barcode){
        $arr=array_filter(preg_split('/[,， ]+/is', $barcode));
        if (count($arr)==1){
            $sql.=" and (产品条码1 like '".$barcode."%' or 产品条码2 like '".$barcode."%' or 产品条码3 like '".$barcode."%')";
        }
        if (count($arr)>1){
            $sql.="  and (产品条码1 in (";
            foreach ($arr as $index=>$str){
                if ($index==0){
                    $sql.="'".$str."'";
                    continue;
                }
                $sql.=",'".$str."'";
            }
            $sql.=")";
            $sql.=" or 产品条码2 in (";
            foreach ($arr as $index=>$str){
                if ($index==0){
                    $sql.="'".$str."'";
                    continue;
                }
                $sql.=",'".$str."'";
            }
            $sql.=")";
            $sql.=" or 产品条码3 in (";
            foreach ($arr as $index=>$str){
                if ($index==0){
                    $sql.="'".$str."'";
                    continue;
                }
                $sql.=",'".$str."'";
            }
            $sql.=")";
            $sql.=")";
        }
        return $sql;
    }
    //创建盘点任务
    public function createMission($date_start,$date_end,$ownerId,$location,$barcode){
        if (!$ownerId) return null;
        if ($date_start&&$date_end){
            $date_end_time=$date_end.' 23:59:59';
            if ($location||$barcode)$type='局部盘点';
            if (!$location&&!$barcode)$type='动盘';
            $wmsInventories=$this->conditionPortStock($date_start,$date_end,$ownerId,$location,$barcode);
            //动盘  判断库存为0的ide料想是否放有其他商品
            foreach ($wmsInventories as $key=>$wmsInventory){
                if (!$wmsInventory->在库数量 && strpos($wmsInventory->库位,'IDE') !== false){
                    /** @var HandInStorageService $handInStorageService  */
                    $handInStorageService=app('HandInStorageService');
                    $invs=$handInStorageService->getInventoryInfos($wmsInventory->库位);
                    if (count($invs)>0) unset($wmsInventories[$key]);
                }
            }
        }

        if (!$date_start&&!$date_end){
            $ownerName=Owner::where('id',$ownerId)->value('code');
            //$ownerName=OracleBasCustomer::where('customer_type','OW')->where('active_flag','Y')->where('descr_c',$name)->value('customerid');
            $date_start=OracleInvLotLocId::where('customerid',$ownerName)->orderBy('addtime','ASC')->value('addtime');
            $date_end_time=OracleInvLotLocId::where('customerid',$ownerName)->orderBy('addtime','DESC')->value('addtime');
            if ($location||$barcode)$type='局部盘点';
            if (!$location&&!$barcode)$type='全盘';
            $wmsInventories=$this->conditionTotalStock($ownerId,$location,$barcode);
        }

        $inventory=new InventoryAccount([
            'owner_id'=>$ownerId,
            'remark'=>$location,
            'type'=>$type,
            'start_at'=>$date_start,
            'end_at'=>$date_end_time,
            'total'=>count($wmsInventories),
        ]);
        $inventory->save();
        $inventory->createSignCreator();
        $this->createInventoryAccountMissionRecord($ownerId,$inventory['id'],$wmsInventories);
        $request=[
            'date_start'=>$date_start,
            'date_end'=>$date_end,
            'ownerId'=>$ownerId,
            'location'=>$location,
            'barcode'=>$barcode,
            'inventoryId'=>$inventory['id'],
        ];
        Controller::logS(__METHOD__,"创建盘点任务__".__FUNCTION__,json_encode($request),Auth::user()['id']);
        return $inventory;
    }
    //更新盘点记录任务
    public function updateInventoryAccountMissionRecord($ownerId,$inventoryAccountId,array $locations,$wmsInventories){
        ini_set('memory_limit','1526M');
        $ownerCode=Owner::query()->where('id',$ownerId)->value('code');
        $commodities=Commodity::query()
            ->select('id','owner_id','name','sku')
            ->where('owner_id',$ownerId)
            ->get();
        $commoditiesArr=[];
        foreach ($commodities as $commodity){
            $commoditiesArr[$ownerCode.'|'.$commodity['sku']]=$commodity;
        }
        $inventoryAccountMissions = InventoryAccountMission::query()
            ->where('inventory_account_id', $inventoryAccountId)
            ->whereIn('location', array_unique($locations))
            ->get();
        /** @var DataHandlerService $dataHandlerService */
        $dataHandlerService = app(DataHandlerService::class);
        $inventoryAccountMissions_map = $dataHandlerService->dataHeader(['inventory_account_id','commodity_id','location'], $inventoryAccountMissions);
        $updateParams = [[
            'id', 'commodity_id', 'inventory_account_id', 'location', 'produced_at', 'valid_at', 'batch_number',
            'erp_type_position', 'quality', 'stored_amount', 'occupied_amount', 'valid_amount','created_at','updated_at'
        ]];
        foreach ($wmsInventories as $wmsInventory){
            $commodity=$commoditiesArr[$wmsInventory->客户.'|'.$wmsInventory->产品编码]??null;
            if (!$commodity)continue;
            $inventoryAccountMission = $dataHandlerService
                ->getKeyValue(['inventory_account_id'=>$inventoryAccountId,'commodity_id' => $commodity['id'],'location' => $wmsInventory->库位], $inventoryAccountMissions_map);
            if (!$inventoryAccountMission)continue;
            if($wmsInventory->质量状态=='ZP') $quality='正品';
            if ($wmsInventory->质量状态=='CC') $quality='残次';
            if ($wmsInventory->质量状态=='XS') $quality='箱损';
            if ($wmsInventory->质量状态=='JS') $quality='机损';
            if ($wmsInventory->质量状态=='DJ') $quality='冻结';
            if ($wmsInventory->质量状态=='FKT') $quality='封口贴';

            if (
                $inventoryAccountMission->produced_at != $wmsInventory->生产日期 ||
                $inventoryAccountMission->valid_at != $wmsInventory->失效日期 ||
                $inventoryAccountMission->batch_number != $wmsInventory->批号 ||
                $inventoryAccountMission->erp_type_position != $wmsInventory->属性仓 ||
                $inventoryAccountMission->quality != $quality ||
                $inventoryAccountMission->stored_amount != $wmsInventory->在库数量 ||
                $inventoryAccountMission->occupied_amount != $wmsInventory->占用数量 ||
                $inventoryAccountMission->valid_amount != ($wmsInventory->在库数量-$wmsInventory->占用数量)
            ){
                $updateParams[] = [
                    'id' => $inventoryAccountMission->id,
                    'commodity_id'=>$commodity->id,
                    'inventory_account_id'=>$inventoryAccountId,
                    'location'=>$wmsInventory->库位,
                    'produced_at'=>$wmsInventory->生产日期,
                    'valid_at'=>$wmsInventory->失效日期,
                    'batch_number'=>$wmsInventory->批号,
                    'erp_type_position'=>$wmsInventory->属性仓,
                    'quality'=>$quality,
                    'stored_amount'=>$wmsInventory->在库数量,
                    'occupied_amount'=>$wmsInventory->占用数量,
                    'valid_amount'=>$wmsInventory->在库数量-$wmsInventory->占用数量,
                    'created_at'=>Carbon::now()->format('Y-m-d H:i:s'),
                    'updated_at'=>Carbon::now()->format('Y-m-d H:i:s'),
                ];
            }
        }
        app(BatchUpdateService::class)->batchUpdate('inventory_account_missions', $updateParams);
        Controller::logS(__METHOD__,"批量更新盘点记录__".__FUNCTION__,json_encode($updateParams));

    }
    //创建盘点记录任务
    public function createInventoryAccountMissionRecord($ownerId,$inventoryAccountId,$wmsInventories){
        ini_set('memory_limit','1526M');
        $ownerCode=Owner::query()->where('id',$ownerId)->value('code');
        $commodities=Commodity::query()
            ->select('id','owner_id','name','sku')
            ->where('owner_id',$ownerId)
            ->get();
        $commoditiesArr=[];
        foreach ($commodities as $commodity){
            $commoditiesArr[$ownerCode.'|'.$commodity['sku']]=$commodity;
        }
        $commodityArrBarcode=[];
        $commodityArr=[];
        $inventoryAccountMissions=[];
        foreach ($wmsInventories as $wmsInventory){
            $commodity=$commoditiesArr[$wmsInventory->客户.'|'.$wmsInventory->产品编码]??null;
            if (!$commodity){
                $commodity=new Commodity();
                $commodity->owner_id=$ownerId;
                $commodity->sku=$wmsInventory->产品编码;
                $commodity->name=$wmsInventory->商品名称;
                $commodity->save();
                $commoditiesArr[$wmsInventory->客户.'|'.$wmsInventory->产品编码]=$commodity;
                array_push($commodityArr,$commodity);
            }
            if ($wmsInventory->产品条码1){
                $commodity->newBarcode($wmsInventory->产品条码1);
                $arr=[
                    'owner_id'=>$ownerId,
                    'sku'=>$wmsInventory->产品编码,
                    'name'=>$wmsInventory->商品名称,
                    'commodity_id'=>$commodity->id,
                    'barcode'=>$wmsInventory->产品条码1,
                ];
            }
            if($wmsInventory->产品条码2){
                $commodity->newBarcode($wmsInventory->产品条码2);
                $arr=[
                    'owner_id'=>$ownerId,
                    'sku'=>$wmsInventory->产品编码,
                    'name'=>$wmsInventory->商品名称,
                    'commodity_id'=>$commodity->id,
                    'barcode'=>$wmsInventory->产品条码2,
                ];
            }
            if($wmsInventory->产品条码3){
                $commodity->newBarcode($wmsInventory->产品条码3);
                $arr=[
                    'owner_id'=>$ownerId,
                    'sku'=>$wmsInventory->产品编码,
                    'name'=>$wmsInventory->商品名称,
                    'commodity_id'=>$commodity->id,
                    'barcode'=>$wmsInventory->产品条码3,
                ];
            }
            array_push($commodityArrBarcode,$arr);
            if($wmsInventory->质量状态=='ZP') $quality='正品';
            if ($wmsInventory->质量状态=='CC') $quality='残次';
            if ($wmsInventory->质量状态=='XS') $quality='箱损';
            if ($wmsInventory->质量状态=='JS') $quality='机损';
            if ($wmsInventory->质量状态=='DJ') $quality='冻结';
            if ($wmsInventory->质量状态=='FKT') $quality='封口贴';
            $inventoryAccountMission=[
                'commodity_id'=>$commodity->id,
                'inventory_account_id'=>$inventoryAccountId,
                'location'=>$wmsInventory->库位,
                'produced_at'=>$wmsInventory->生产日期,
                'valid_at'=>$wmsInventory->失效日期,
                'batch_number'=>$wmsInventory->批号,
                'erp_type_position'=>$wmsInventory->属性仓,
                'quality'=>$quality,
                'stored_amount'=>$wmsInventory->在库数量,
                'occupied_amount'=>$wmsInventory->占用数量,
                'valid_amount'=>$wmsInventory->在库数量-$wmsInventory->占用数量,
                'created_at'=>Carbon::now()->format('Y-m-d H:i:s'),
            ];
            array_push($inventoryAccountMissions,$inventoryAccountMission);
        }

        $inventoryAccountMissions=InventoryAccountMission::query()->insert($inventoryAccountMissions);
        if ($commodityArr){
            Controller::logS(__METHOD__,"插入was中商品信息__".__FUNCTION__,json_encode($commodityArr));
            Controller::logS(__METHOD__,"插入was中商品信息的条码信息__".__FUNCTION__,json_encode($commodityArrBarcode));
        }
        Controller::logS(__METHOD__,"批量插入盘点记录__".__FUNCTION__,json_encode($inventoryAccountMissions));

    }
    //盘点库存
    public function stockInventory($id,$location,$barcode,$count,$inventoryAccountId){
        $inventoryAccountMission=InventoryAccountMission::with(['commodity.barcodes','stockInventoryPersons'])->find($id);
        if (!$inventoryAccountMission)return null;
        $this->盘点($inventoryAccountId,$count,$inventoryAccountMission);
        $request=[
            'location'=>$location,
            'barcode'=>$barcode,
            'count'=>$count,
            'inventoryId'=>$inventoryAccountId,
        ];
        Controller::logS(__METHOD__,"盘点__".__FUNCTION__,json_encode($request));
        $inventoryAccountMission=InventoryAccountMission::with(['commodity.barcodes','stockInventoryPersons'])->find($id);
        return $inventoryAccountMission;
    }
    //盘点修改盘点任务数据
    public function updateInventory($inventoryAccountId){
        $inventoryAccount=InventoryAccount::find($inventoryAccountId);
        $inventoryAccount->processed=$inventoryAccount->getProcessedAmount();//已盘点数
        $inventoryAccount->difference=$inventoryAccount->getDifferenceAmount();//盘点差异数
        $inventoryAccount->returned=$inventoryAccount->getReturnedAmount(); //复盘归位数
        $inventoryAccount->ignored=$inventoryAccount->getIgnoredAmount(); //跳过数
        if($inventoryAccount->status=='待盘点')
            $inventoryAccount->status='盘点中';
        $inventoryAccount->update();
        Controller::logS(__METHOD__,"盘点修改盘点任务中的已盘条数__".__FUNCTION__,json_encode($inventoryAccountId));
        return $inventoryAccount;
    }
    //根据该库存和产品条码查询该条盘点记录
    public function searchStockInventoryRecord($location,$barcode,$inventoryAccountId){
        $inventoryAccountMissions=InventoryAccountMission::with(['commodity.barcodes','stockInventoryPersons'])->whereHas('commodity',function($query)use($barcode){
            $query->whereHas('barcodes',function($sql)use($barcode){
                $sql->where('code',$barcode);
            });
        })->where('location',$location)->where('inventory_account_id',$inventoryAccountId)->get();
        return  $inventoryAccountMissions;
    }

    public function 修改质量状态($id,$location,$sku,$quality,$ownerCode){
        $lotnum=OracleInvLotLocId::query()->where('locationid',$location)->where('customerid',$ownerCode)->where('sku',$sku)->value('lotnum');
        if (!$lotnum) return null;
        $oracleInvLotAtt=OracleInvLotAtt::query()->where('lotnum',$lotnum)->where('sku',$sku)->where('customerid',$ownerCode)->first();
        if(!isset($oracleInvLotAtt))return null;
        if ($quality=='正品')$status='ZP';
        if ($quality=='残次')$status='CC';
        $oracleInvLotAtt=OracleInvLotAtt::query()->where('lotnum',$lotnum)->where('sku',$sku)->where('customerid',$ownerCode)->update([
            'lotatt08'=>$status,
        ]);
        if ($oracleInvLotAtt>0){
            $inventoryAccountMission=InventoryAccountMission::query()->find($id);
            $inventoryAccountMission->quality=$quality;
            $inventoryAccountMission=$inventoryAccountMission->update();
            return $inventoryAccountMission;
        }else{
            return null;
        }
    }
    public function 完结盘点任务($id){
        $inventoryAccount=InventoryAccount::query()->find($id);
        if ($inventoryAccount->status=='复盘中'){
            $inventoryAccount->status='已完成';
            $inventoryAccount->update();
            Controller::logS(__METHOD__,"盘点修改盘点任务状态__".__FUNCTION__,json_encode($id));
            return $inventoryAccount;
        }else{
            return null;
        }
    }
    public function 增加系统之外的盘点记录($location,$barcode,$inventoryId,$count,$owner_code,$param){
        if($param=='商品新增'||$param=='库位和商品新增'){
            $oracleBasSku=$this->searchCommodityByBarcode($barcode,$owner_code);
            if ($oracleBasSku){
                $ownerId=Owner::query()->where('code',$owner_code)->value('id');
                $commodity=Commodity::query()->where('sku',$oracleBasSku->sku)->where('owner_id',$ownerId)->first();
                if (!$commodity){
                    $commodity=Commodity::query()->create([
                        'sku'=>$oracleBasSku->sku,
                        'owner_id'=>$ownerId,
                        'name'=>$oracleBasSku->descr_c
                    ]);
                    $commodity->newBarcode($barcode);
                }
            }
        }
        $commodity=Commodity::query()->whereHas('barcodes',function ($query)use($barcode){
            $query->where('code',$barcode);
        })->orderBy('id','DESC')->first();

        $inventoryAccountMission=new InventoryAccountMission();
        $inventoryAccountMission->location=$location;
        $inventoryAccountMission->inventory_account_id=$inventoryId;
        $inventoryAccountMission->commodity_id=$commodity->id;
        $inventoryAccountMission->quality='正品';
        $inventoryAccountMission->verified_amount=$count;//盘点数量
        $inventoryAccountMission->difference_amount=$count;//盘点差异
        $inventoryAccountMission->checked='是';
        $inventoryAccountMission->save();
        Controller::logS(__METHOD__,"增加系统之外的盘点记录".__FUNCTION__,json_encode($inventoryAccountMission));
        if ($inventoryAccountMission){
            $inventoryAccountMission->createSignStockInventoryPersons();
            Controller::logS(__METHOD__,"增加盘点人".__FUNCTION__,json_encode($inventoryId));
            $inventoryAccount=InventoryAccount::query()->find($inventoryId);
            $inventoryAccount->total=$inventoryAccount->total+1;
            $inventoryAccount->processed=$inventoryAccount->getProcessedAmount();//已盘点数
            $inventoryAccount->difference=$inventoryAccount->getDifferenceAmount();//盘点差异数
            $inventoryAccount->returned=$inventoryAccount->getReturnedAmount(); //复盘归位数
            $inventoryAccount->ignored=$inventoryAccount->getIgnoredAmount();
            $inventoryAccount->update();
            Controller::logS(__METHOD__,"修改盘点任务记录".__FUNCTION__,json_encode($inventoryId));
            $inventoryAccountMission=InventoryAccountMission::with(['commodity.barcodes','stockInventoryPersons'])->where('id',$inventoryAccountMission->id)->first();
            return $inventoryAccountMission;
        }else{
            return null;
        }
    }
    public function 盘点选中任务($id,$count,$inventoryId){
        $inventoryAccountMission=InventoryAccountMission::query()->with(['commodity.barcodes','stockInventoryPersons'])->find($id);
        if (!$inventoryAccountMission) return null;
        $this->盘点($inventoryId,$count,$inventoryAccountMission);
        $request=[
            'id'=>$id,
            'count'=>$count,
            'inventoryId'=>$inventoryId,
        ];
        Controller::logS(__METHOD__,"盘点__".__FUNCTION__,json_encode($request));
        $inventoryAccountMission=InventoryAccountMission::query()->with(['commodity.barcodes','stockInventoryPersons'])->find($id);
        return $inventoryAccountMission;
    }
    public function 盘点生产日期_失效日期_批号有改动任务($id,$count,$inventoryId,$produced_at,$valid_at,$batch_number){
        $inventoryAccountMission=InventoryAccountMission::query()->with(['commodity.barcodes','stockInventoryPersons'])->find($id);
        $inventoryMissionId=null;
        if (!$inventoryAccountMission) return null;
        $inventory=InventoryAccount::find($inventoryId);
        if ($produced_at!=$inventoryAccountMission->produced_at||$valid_at!=$inventoryAccountMission->valid_at ||$batch_number!=$inventoryAccountMission->batch_number){
            if($inventory->status=='复盘中'){//盘点原记录
                $inventoryAccountMission->re_checked_amount=$count;
                $inventoryAccountMission->difference_amount=0-$count;
                $inventoryAccountMission->checked='已复核';
                if ($inventoryAccountMission->difference_amount==0){
                    $inventoryAccountMission->returned='是';
                }else{
                    $inventoryAccountMission->returned='否';
                }
            }else{//初盘
                $inventoryAccountMission->verified_amount=$count;
                $inventoryAccountMission->difference_amount=0-$count;
                $inventoryAccountMission->checked='是';
            }
            $inventoryAccountMission->update();
            $inventoryAccountMission->createSignStockInventoryPersons();
            //新增新记录
            $inventoryMission=new InventoryAccountMission();
            $inventoryMission->location=$inventoryAccountMission->location;
            $inventoryMission->inventory_account_id=$inventoryId;
            $inventoryMission->commodity_id=$inventoryAccountMission->commodity_id;
            $inventoryMission->produced_at=$produced_at;
            $inventoryMission->valid_at=$valid_at;
            $inventoryMission->batch_number=$batch_number;
            $inventoryMission->erp_type_position=$inventoryAccountMission->erp_type_position;
            $inventoryMission->quality=$inventoryAccountMission->quality;
            $inventoryMission->stored_amount=0;
            $inventoryMission->verified_amount=$count;
            $inventoryMission->difference_amount=$count;
            $inventoryMission->checked='是';
            if($inventory->status=='复盘中'){
                $inventoryMission->re_checked_amount=$count;
                $inventoryMission->returned='否';
            }
            $inventoryMission->save();
            $inventoryMissionId=$inventoryMission->id??null;
            $inventoryMission->createSignStockInventoryPersons();

        }else{
            $this->盘点($inventoryId,$count,$inventoryAccountMission);
        }
        $request=[
            'id'=>$id,
            'count'=>$count,
            'inventoryId'=>$inventoryId,
            'produced_at'=>$produced_at,
            'valid_at'=>$valid_at,
            'batch_number'=>$batch_number,
        ];
        Controller::logS(__METHOD__,"盘点__".__FUNCTION__,json_encode($request));
        $inventoryAccountMission=InventoryAccountMission::query()->with(['commodity.barcodes','stockInventoryPersons'])->whereIn('id',[$id,$inventoryMissionId])->get();
        return $inventoryAccountMission;
    }
    public function 盘点($inventoryId,$count,$inventoryAccountMission){
        $inventory=InventoryAccount::find($inventoryId);
        if($inventory->status=='复盘中'){
            $inventoryAccountMission->re_checked_amount=$count;
            $inventoryAccountMission->difference_amount=$count-$inventoryAccountMission->stored_amount;
            $inventoryAccountMission->checked='已复核';
            if ($inventoryAccountMission->difference_amount==0){
                $inventoryAccountMission->returned='是';
            }else{
                $inventoryAccountMission->returned='否';
            }
        }else{//初盘
            $inventoryAccountMission->verified_amount=$count;
            $inventoryAccountMission->difference_amount=$count-$inventoryAccountMission->stored_amount;
            $inventoryAccountMission->checked='是';
        }
        $inventoryAccountMission->update();
        $inventoryAccountMission->createSignStockInventoryPersons();
        return $inventoryAccountMission;
    }
    public function 删除盘点记录($inventoryAccountMissionId,$inventoryAccountId){
        $inventoryAccountMission=InventoryAccountMission::query()->where('id',$inventoryAccountMissionId)->delete();
        Controller::logS(__METHOD__,__FUNCTION__,json_encode($inventoryAccountMissionId));
        if ($inventoryAccountMission>0){
            $inventoryAccount=InventoryAccount::query()->find($inventoryAccountId);
            $inventoryAccount->total=$inventoryAccount->total-1;
            $inventoryAccount->processed=$inventoryAccount->getProcessedAmount();//已盘点数
            $inventoryAccount->difference=$inventoryAccount->getDifferenceAmount();//盘点差异数
            $inventoryAccount->returned=$inventoryAccount->getReturnedAmount(); //复盘归位数
            $inventoryAccount->ignored=$inventoryAccount->getIgnoredAmount();
            $inventoryAccount->update();
            Controller::logS(__METHOD__,'删除盘点记录时修改盘点任务信息'.__FUNCTION__,json_encode($inventoryAccountId));
        }
        return $inventoryAccountMission;
    }
    public function 跳过盘点记录($inventoryAccountMissionId,$inventoryAccountId){
        $inventoryAccountMission=InventoryAccountMission::query()->find($inventoryAccountMissionId);
        $inventoryAccountMission->checked='跳过';
        $inventoryAccountMission->update();
        app('LogService')->log(__METHOD__,"跳过盘点记录修改checked状态",json_encode($inventoryAccountMissionId));
        if ($inventoryAccountMission->checked=='跳过'){
            $inventoryAccount=InventoryAccount::query()->find($inventoryAccountId);
            $inventoryAccount->processed=$inventoryAccount->getProcessedAmount();//已盘点数
            $inventoryAccount->difference=$inventoryAccount->getDifferenceAmount();//盘点差异数
            $inventoryAccount->returned=$inventoryAccount->getReturnedAmount(); //复盘归位数
            $inventoryAccount->ignored=$inventoryAccount->getIgnoredAmount();
            $inventoryAccount->update();
            Controller::logS(__METHOD__,'跳过盘点记录时修改盘点任务信息'.__FUNCTION__,json_encode($inventoryAccountId));
        }
        return $inventoryAccountMission;
    }
    public function 确认盘点差异($inventoryAccountMissionId,$inventoryAccountId){
        $inventoryAccountMission=InventoryAccountMission::query()->find($inventoryAccountMissionId);
        $inventoryAccountMission->checked='确认差异';
        $inventoryAccountMission->update();
        app('LogService')->log(__METHOD__,"跳过盘点记录修改checked状态",json_encode($inventoryAccountMissionId));
        return $inventoryAccountMission;
    }
    public function 批量跳过或确认差异($checkData){
        $inventoryAccountMissions=[];
        $inventoryAccountId=null;
        foreach ($checkData as $inventoryMission){
            $inventoryAccountMission=InventoryAccountMission::query()->find($inventoryMission['id']);
            $inventoryAccountId=$inventoryAccountMission->inventory_account_id;
            if ($inventoryMission['mark']==='未盘')$inventoryAccountMission['checked']='跳过';
            if ($inventoryMission['mark']==='未复盘有差异'||$inventoryMission['mark']==='已复盘无差异')$inventoryAccountMission['checked']='确认差异';
            $inventoryAccountMission->update();
            array_push($inventoryAccountMissions,$inventoryAccountMission);
        }
        app('LogService')->log(__METHOD__,"批量跳过或确认差异",json_encode($checkData));
        if ($inventoryAccountId){
            $inventoryAccount=InventoryAccount::query()->find($inventoryAccountId);
            $inventoryAccount->processed=$inventoryAccount->getProcessedAmount();//已盘点数
            $inventoryAccount->difference=$inventoryAccount->getDifferenceAmount();//盘点差异数
            $inventoryAccount->returned=$inventoryAccount->getReturnedAmount(); //复盘归位数
            $inventoryAccount->ignored=$inventoryAccount->getIgnoredAmount();
            $inventoryAccount->update();
            Controller::logS(__METHOD__,'批量跳过或确认差异修改盘点任务信息'.__FUNCTION__,json_encode($inventoryAccountId));
        }
        return $inventoryAccountMissions;
    }
    public function searchCommodityByBarcode($barcode,$owner_code){
        $oracleBasSku=OracleBasSKU::query()
            ->where('ALTERNATE_SKU1',$barcode)
            ->orWhere('ALTERNATE_SKU2',$barcode)
            ->orWhere('ALTERNATE_SKU3',$barcode)
            ->where('customerid',$owner_code)->first();
        if (!$oracleBasSku)return null;
        return $oracleBasSku;
    }
    public function baseOnBlindReceive($location,$owner_code,$goodses,$inventoryId){
        $request=[
            'location'=>$location,
            'owner_code'=>$owner_code,
            'goodses'=>$goodses,
            'inventoryId'=>$inventoryId,
        ];
        app('LogService')->log(__METHOD__,"盘点基于盲收",json_encode($request));
        $inventoryAccountMissions=[];
        foreach ($goodses as $good){
            $barcode=$good['barcode']??'';
            $amount=$good['amount']??'';
            $inventoryAccountMission=InventoryAccountMission::with(['commodity.barcodes','stockInventoryPersons'])->whereHas('commodity',function($query)use($barcode){
                $query->whereHas('barcodes',function($sql)use($barcode){
                    $sql->where('code','=',$barcode);
                });
            })->where('location',$location)->where('inventory_account_id',$inventoryId)->first();
            if (!$inventoryAccountMission){
                $oracleBasSku=$this->searchCommodityByBarcode($barcode,$owner_code);
                if ($oracleBasSku){
                    $param='商品新增';
                    $inventoryAccountMission=$this->增加系统之外的盘点记录($location,$barcode,$inventoryId,$amount,$owner_code,$param);
                }
            }else{
                $inventoryAccountMission=$this->盘点($inventoryId,$amount,$inventoryAccountMission);
            }
            array_push($inventoryAccountMissions,$inventoryAccountMission);
        }
        if (count($inventoryAccountMissions)<1) return null;
        return $inventoryAccountMissions;
    }

    public function batchStockByLocation($missions,$inventoryId)
    {
        $inventory=InventoryAccount::query()->find($inventoryId);
        if($inventory->status=='复盘中'){
            $updateParams = [[
                'id','returned','difference_amount','re_checked_amount','checked','updated_at'
            ]];
            foreach ($missions as $mission){
                if (($mission['amount']-$mission['stored_amount'])==0){
                    $return='是';
                }else{
                    $return='否';
                }
                $updateParams[] = [
                    'id'=>$mission['id'],
                    're_checked_amount'=>$mission['amount'],
                    'difference_amount'=>$mission['amount']-$mission['stored_amount'],
                    'returned'=>$return,
                    'checked'=>'已复核',
                    'updated_at' =>Carbon::now()->format('Y-m-d H:i:s'),
                ];
            }
        }else{//初盘
            $updateParams = [[
                'id','difference_amount','checked','verified_amount','updated_at'
            ]];
            foreach ($missions as $mission){
                $updateParams[] = [
                    'id'=>$mission['id'],
                    'verified_amount'=>$mission['amount'],
                    'difference_amount'=>$mission['amount']-$mission['stored_amount'],
                    'checked'=>'是',
                    'updated_at' =>Carbon::now()->format('Y-m-d H:i:s'),
                ];
            }
        }
        if(count($updateParams) > 1) app(BatchUpdateService::class)->batchUpdate('inventory_account_missions',$updateParams);
        LogService::log(__METHOD__,__FUNCTION__,'根据库位批量盘点'.count($updateParams).json_encode($updateParams));
        $ids = array_unique(data_get($missions,'*.id'));
        $inventoryMissions=InventoryAccountMission::query()->whereIn('id',$ids)->get();
        foreach ($inventoryMissions as $inventoryMission){
            $inventoryMission->createSignStockInventoryPersons();
        }
        return InventoryAccountMission::query()->with(['commodity.barcodes','stockInventoryPersons'])->whereIn('id',$ids)->get();
    }

}