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\OraccleBasCustomer;
use App\OracleActTransactionLog;
use App\OracleInvLotAtt;
use App\OracleInvLotLocId;
use App\Owner;
use App\Services\common\QueryService;
use Illuminate\Http\Request;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Gate;

class InventoryAccountService
{

    private function conditionQuery($queryParam){
        $inventories=InventoryAccount::query()->with(['owner'])->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();
        $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){
        $inventoryAccountMissions=[];
        foreach ($wmsInventories as $wmsInventory){
            $commodity=Commodity::query()->firstOrCreate([
                'owner_id'=>$ownerId,
                'sku'=>$wmsInventory->产品编码,
                'name'=>$wmsInventory->商品名称,
            ]);
            Controller::logS(__METHOD__,"根据wms产品编码和货主查询或创建商品信息__".__FUNCTION__,json_encode($wmsInventory));
            $commodity->newBarcode($wmsInventory->产品条码);
            Controller::logS(__METHOD__,"根据wms产品条码和商品id查询或创建商品条码信息__".__FUNCTION__,json_encode($wmsInventory));
            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->占用数量,
            ];
            array_push($inventoryAccountMissions,$inventoryAccountMission);
        }
        DB::table('inventory_account_missions')->insert($inventoryAccountMissions);
    }
    //盘点库存
    public function stockInventory($location,$barcode,$count,$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;
        $inventory=InventoryAccount::find($inventoryAccountId);
        if ($inventory->surplus!=0){
            $inventoryAccountMission->verified_amount=$count;
            $inventoryAccountMission->difference_amount=$count-$inventoryAccountMission->stored_amount;
            $inventoryAccountMission->checked='是';
        }else{
            $inventoryAccountMission->re_checked_amount=$count;
            $inventoryAccountMission->difference_amount=$count-$inventoryAccountMission->stored_amount;
            if ($inventoryAccountMission->difference_amount==0){
                $inventoryAccountMission->returned='是';
            }else{
                $inventoryAccountMission->returned='否';
            }
        }
        $inventoryAccountMission->update();
        $request=[
            'location'=>$location,
            'barcode'=>$barcode,
            'count'=>$count,
            'inventoryId'=>$inventoryAccountId,
        ];
        Controller::logS(__METHOD__,"盘点__".__FUNCTION__,json_encode($request));
        return $inventoryAccountMission;
    }
    //盘点修改盘点任务数据
    public function updateInventory($inventoryAccountId){
        $inventoryAccount=InventoryAccount::find($inventoryAccountId);
        $inventoryAccount->processed=$inventoryAccount->getProcessedAmount();
        $inventoryAccount->difference=$inventoryAccount->getDifferenceAmount();
        $inventoryAccount->returned=$inventoryAccount->getReturnedAmount();
        $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,
        ]);
        $inventoryAccountMission=InventoryAccountMission::query()->find($id);
        $inventoryAccountMission->quality=$quality;
        $inventoryAccountMission=$inventoryAccountMission->update();
        if ($oracleInvLotAtt>0&&$inventoryAccountMission)return $inventoryAccountMission;
    }
}