baoshi-was/app/Services/InventoryService.php

<?php


namespace App\Services;

use App\Commodity;
use App\Http\Controllers\Controller;
use App\Inventory;
use App\InventoryMission;
use App\OraccleBasCustomer;
use App\OracleActTransactionLog;
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 InventoryService
{

    private function conditionQuery($queryParam){
        $inventories=Inventory::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)->queryCondition($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 Inventory::query()->with(['owner'])->orderBy('id','DESC')
            ->whereIn('id',explode(',',$queryParam['data']))->get();
    }

    public function conditionSearch($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.=' )  ';
        //dd($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='动盘';
        }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=OracleActTransactionLog::where('fmcustomerid',$ownerName)->orderBy('addtime','asc')->value('addtime');
            $date_end_time=OracleActTransactionLog::where('fmcustomerid',$ownerName)->orderBy('addtime','desc')->value('addtime');
            $type='全盘';
        }else{
            return null;
        }
        $inventory=new Inventory([
            'owner_id'=>$ownerId,
            'type'=>$type,
            'start_at'=>$date_start,
            'end_at'=>$date_end_time,
        ]);
        $inventory->save();
        $this->createInventoryMissionRecord($date_start,$date_end,$ownerId,$inventory['id']);
        $request=[
            'date_start'=>$date_start,
            'date_end'=>$date_end,
            'ownerId'=>$ownerId,
            'inventoryId'=>$inventory['id'],
        ];
        Controller::logS(__METHOD__,"创建盘点记录任务__".__FUNCTION__,json_encode($request),Auth::user()['id']);
        $inventoryMissionCount=InventoryMission::where('inventory_id',$inventory['id'])->count();
        $inventory->total=$inventoryMissionCount;
        $inventory->update();
        Controller::logS(__METHOD__,"创建盘点任务__".__FUNCTION__,json_encode($request),Auth::user()['id']);
        return $inventory;
    }

    //创建盘点记录任务
    public function createInventoryMissionRecord($date_start,$date_end,$ownerId,$inventoryId){
        if (!$ownerId) return null;
        $wmsInventories=$this->conditionSearch($date_start,$date_end,$ownerId);
        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));
            $inventoryMission = new InventoryMission();
            $inventoryMission->commodity_id=$commodity->id;
            $inventoryMission->inventory_id=$inventoryId;
            $inventoryMission->location=$wmsInventory->库位;
            $inventoryMission->produced_at=$wmsInventory->生产日期;
            $inventoryMission->valid_at=$wmsInventory->失效日期;
            $inventoryMission->stored_at=$wmsInventory->入库日期;
            $inventoryMission->batch_number=$wmsInventory->批号;
            $inventoryMission->erp_type_position=$wmsInventory->属性仓;
            $inventoryMission->quality=$wmsInventory->质量状态;
            $inventoryMission->stored_amount=$wmsInventory->在库数量;
            $inventoryMission->occupied_amount=$wmsInventory->占用数量;
            $inventoryMission->save();
        }

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

        $inventoryMission=InventoryMission::whereHas('commodity',function($query)use($barcode){
             $query->whereHas('barcodes',function($sql)use($barcode){
                 $sql->where('code','=',$barcode);
            });
        })->where('location',$location)->where('inventory_id',$inventoryId)->first();
        if (!$inventoryMission) return null;
        $inventory=Inventory::find($inventoryId);
        if ($inventory->surplus!=0){
            $inventoryMission->verified_amount=$count;
            $inventoryMission->difference_amount=abs($inventoryMission->stored_amount-$count);
            $inventoryMission->checked='是';
        }else{
            $inventoryMission->re_checked_amount=$count;
            $inventoryMission->difference_amount=abs($inventoryMission->stored_amount-$count);
            if ($inventoryMission->difference_amount==0){
                $inventoryMission->returned='是';
            }else{
                $inventoryMission->returned='否';
            }
        }
        $inventoryMission->update();
        $request=[
            'location'=>$location,
            'barcode'=>$barcode,
            'count'=>$count,
            'inventoryId'=>$inventoryId,
        ];
        Controller::logS(__METHOD__,"盘点__".__FUNCTION__,json_encode($request));
        return $inventoryMission;
    }
    //盘点修改盘点任务数据
    public function updateInventory($inventoryId){
        $inventory=Inventory::find($inventoryId);
        $inventory->processed=$inventory->getProcessedAmount();
        $inventory->difference=$inventory->getDifferenceAmount();
        $inventory->returned=$inventory->getReturnedAmount();
        $inventory->update();
        Controller::logS(__METHOD__,"盘点修改盘点任务中的已盘条数__".__FUNCTION__,json_encode($inventoryId));
        return $inventory;
    }
    //根据该库存和产品条码查询该条盘点记录
    public function searchStockInventoryRecord($location,$barcode,$inventoryId){
        $inventoryMission=InventoryMission::whereHas('commodity',function($query)use($barcode){
            $query->whereHas('barcodes',function($sql)use($barcode){
                $sql->where('code',$barcode);
            });
        })->where('location',$location)->where('inventory_id',$inventoryId)->first();
        if (!$inventoryMission) return null;
        return  $inventoryMission;
    }


}