baoshi-was/app/Services/InventoryAccountService.php

<?php


namespace App\Services;

use App\Commodity;
use App\CommodityBarcode;
use App\Http\Controllers\Controller;
use App\InventoryAccount;
use App\InventoryAccountMission;
use App\OraccleBasCustomer;
use App\OracleInvLotAtt;
use App\OracleInvLotLocId;
use App\Owner;
use App\Services\common\QueryService;
use App\Sign;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;

class InventoryAccountService
{

    private function conditionQuery($queryParam){
        $inventories=InventoryAccount::query()->with(['owner','creator'])->orderBy('id','desc');
        $columnQueryRules=[
            'owner_id' => ['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){
        if (!$ownerId) return null;
        $descr_c=Owner::where('id',$ownerId)->value('name');
        $sql='select * from (select result.*,rownum rn from (';
        $sql.=' select customer.Descr_C as 货主,stockLog.客户 客户, 库位, sku.SKU 产品编码, sku.ALTERNATE_SKU1 产品条码, ';
        $sql.=' sku.Descr_C 商品名称, lot.LotAtt05 属性仓, lot.LotAtt08 质量状态, lot.LotAtt02 失效日期, ';
        $sql.=' lot.LotAtt04 批号, lot.LotAtt01 生产日期, lot.LotAtt03 入库日期';
        $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 ($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 ($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 ($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 ($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 ';
        $sql.=' ,sku.Descr_C,FMLotNum,lot.LotAtt05,lot.LotAtt01,lot.LotAtt03,lot.LotAtt08,lot.LotAtt02,lot.LotAtt04 ';
        $sql.=' , storeStatus.QTY, storeStatus.QtyAllocated,stockLog.客户  ';
        $sql.=' )result where 1=1 ';
        if ($descr_c){
            $sql .= ' and 货主 in (';
            $descr_cs = explode(',',$descr_c);
            foreach ($descr_cs as $index => $descr_c){
                if ($index != 0)$sql .= ',';
                $sql .= "'".$descr_c."'";
            }
            $sql .= ') ';
        }
        $sql.=' )  ';
        return DB::connection('oracle')->select($sql);
    }
    //全盘查询
    private function conditionTotalStock($ownerId){
        $descr_c=Owner::where('id',$ownerId)->value('name');
        $sql='select * from (select result.*,rownum rn from (';
        $sql.=' select customer.Descr_C as 货主,storeStatus.CUSTOMERID 客户,storeStatus.LocationID 库位, sku.SKU 产品编码, sku.ALTERNATE_SKU1 产品条码, ';
        $sql.=' sku.Descr_C 商品名称, lot.LotAtt05 属性仓, lot.LotAtt08 质量状态, lot.LotAtt02 失效日期, storeStatus.ADDTIME 创建时间, ';
        $sql.=' lot.LotAtt04 批号,lot.LotAtt01 生产日期,lot.LotAtt03 入库日期 ';
        $sql.=' , storeStatus.QTY 在库数量, storeStatus.QtyAllocated 占用数量,count(1) over () as sum 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 ';
        $sql.=' ,sku.Descr_C,lot.LotAtt05,lot.LotAtt08,lot.LotAtt02,lot.LotAtt04 ';
        $sql.=' , storeStatus.QTY, storeStatus.QtyAllocated,storeStatus.CUSTOMERID,storeStatus.ADDTIME,lot.LotAtt01,lot.LotAtt03  ';
        $sql.=' )result where 1=1 ';
        if ($descr_c)$sql.=" and 货主 = '".$descr_c."' ";
        $sql.=' )  ';
        return DB::connection('oracle')->select($sql);
    }
    //创建盘点任务
    public function createMission($date_start,$date_end,$ownerId){
        if (!$ownerId) return null;
        if ($date_start&&$date_end){
            $date_end_time=$date_end.' 23:59:59';
            $type='动盘';
            $wmsInventories=$this->conditionPortStock($date_start,$date_end,$ownerId);
        }elseif (!$date_start&&!$date_end){
            $name=Owner::where('id',$ownerId)->value('name');
            $ownerName=OraccleBasCustomer::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');
            $type='全盘';
            $wmsInventories=$this->conditionTotalStock($ownerId);
        }else{
            return null;
        }
        $inventory=new InventoryAccount([
            'owner_id'=>$ownerId,
            '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,
            'inventoryId'=>$inventory['id'],
        ];
        Controller::logS(__METHOD__,"创建盘点任务__".__FUNCTION__,json_encode($request),Auth::user()['id']);
        return $inventory;
    }

    //创建盘点记录任务
    public function createInventoryAccountMissionRecord($ownerId,$inventoryAccountId,$wmsInventories){
        $commodities=Commodity::query()
            ->select('id','owner_id','name','sku')
            ->where('owner_id',$ownerId)
            ->whereNotNull(['sku','name'])
            ->get();
        $commodityArr=[];
        $inventoryAccountMissions=[];
        foreach ($wmsInventories as $wmsInventory){
            $commodity=$commodities->where('name',$wmsInventory->商品名称)->where('sku',$wmsInventory->产品编码)->first();
            if (!$commodity){
                $commodity=new Commodity();
                $commodity->owner_id=$ownerId;
                $commodity->sku=$wmsInventory->产品编码;
                $commodity->name=$wmsInventory->商品名称;
                $commodity->save();
                $commodity->newBarcode($wmsInventory->产品条码);
                $arr=[
                    'owner_id'=>$ownerId,
                    'sku'=>$wmsInventory->产品编码,
                    'name'=>$wmsInventory->商品名称,
                    'commodity_id'=>$commodity->id,
                    'barcode'=>$wmsInventory->产品条码,
                ];
                array_push($commodityArr,$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->失效日期,
                'stored_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__,"批量插入盘点记录__".__FUNCTION__,json_encode($inventoryAccountMissions));


    }
    //盘点库存
    public function stockInventory($location,$barcode,$count,$inventoryAccountId){

        $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',$inventoryAccountId)->first();
        if (!$inventoryAccountMission) return null;
        $inventory=InventoryAccount::find($inventoryAccountId);
        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();
        $request=[
            'location'=>$location,
            'barcode'=>$barcode,
            'count'=>$count,
            'inventoryId'=>$inventoryAccountId,
        ];
        Controller::logS(__METHOD__,"盘点__".__FUNCTION__,json_encode($request));
        $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',$inventoryAccountId)->first();
        return $inventoryAccountMission;
    }
    //盘点修改盘点任务数据
    public function updateInventory($inventoryAccountId){
        $inventoryAccount=InventoryAccount::find($inventoryAccountId);
        $inventoryAccount->processed=$inventoryAccount->getProcessedAmount();//已盘点数
        $inventoryAccount->difference=$inventoryAccount->getDifferenceAmount();//盘点差异数
        $inventoryAccount->returned=$inventoryAccount->getReturnedAmount(); //复盘归位数
        if($inventoryAccount->status=='待盘点')
            $inventoryAccount->status='盘点中';
        $inventoryAccount->update();
        Controller::logS(__METHOD__,"盘点修改盘点任务中的已盘条数__".__FUNCTION__,json_encode($inventoryAccountId));
        return $inventoryAccount;
    }
    //根据该库存和产品条码查询该条盘点记录
    public function searchStockInventoryRecord($location,$barcode,$inventoryAccountId){
        $inventoryAccountMission=InventoryAccountMission::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)->first();
        if (!$inventoryAccountMission) return null;
        return  $inventoryAccountMission;
    }

    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){
        $commodity=Commodity::query()->whereHas('barcodes',function ($query)use($barcode){
            $query->where('code',$barcode);
        })->first();
        $inventoryAccountMission=new InventoryAccountMission();
        $inventoryAccountMission->location=$location;
        $inventoryAccountMission->inventory_account_id=$inventoryId;
        $inventoryAccountMission->commodity_id=$commodity->id;
        $inventoryAccountMission->verified_amount=$count;//盘点数量
        $inventoryAccountMission->difference_amount=$count;//盘点差异
        $inventoryAccountMission->checked='是';
        $inventoryAccountMission->save();
        Controller::logS(__METHOD__,"".__FUNCTION__,json_encode($inventoryId));
        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->update();
            Controller::logS(__METHOD__,"修改盘点任务记录".__FUNCTION__,json_encode($inventoryId));
            return $inventoryAccountMission;
        }else{
            return null;
        }

    }
}