baoshi-was/app/Services/InventoryService.php

<?php


namespace App\Services;

use App\Commodity;
use App\Http\Controllers\Controller;
use App\Inventory;
use App\inventoryDailyLog;
use App\InventoryDailyLoggingOwner;
use App\InventoryMission;
use App\OraccleBasCustomer;
use App\OracleActTransactionLog;
use App\Owner;
use App\Services\common\QueryService;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Gate;

class InventoryService
{

    private function conditionQuery(Request $request){
        $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)->query($request->input(),$inventories,$columnQueryRules);
        return $inventories;
    }
    public function paginate(Request $request){
        $inventories = $this->conditionQuery($request);
        return $inventories->paginate($request->paginate ?? 50);
    }

    public function get(Request $request){
        $inventories = $this->conditionQuery($request);
        return $inventories->get();
    }

    public function some(Request $request){
        return Inventory::query()->with(['owner'])->orderBy('id','DESC')
            ->whereIn('id',explode(',',$request->data))->get();
    }

    public function conditionSearch(Request $request){
        if(!Gate::allows("库存管理-盘点")){ return redirect(url('/'));  }
        $date_start=$request->input('formData.date_start');
        $date_end=$request->input('formData.date_end');
        $ownerId=$request->input('formData.owner_id')[0];
        $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);
    }
    //创建盘点任务
    public function createMission(Request $request){
        if(!Gate::allows("库存管理-盘点")){ return redirect(url('/'));  }
        $date_start=$request->input('formData.date_start');
        $date_end=$request->input('formData.date_end');
        $ownerIds=$request->input('formData.owner_id');
        if (count($ownerIds)<=0) return null;
        if ($date_start&&$date_end){
            $type='动盘';
        }elseif (!$date_start&&!$date_end){
            $name=Owner::where('id',$ownerIds[0])->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=OracleActTransactionLog::where('fmcustomerid',$ownerName)->orderBy('addtime','desc')->value('addtime');
            $type='全盘';
        }else{
            return null;
        }
        $ownerId=$ownerIds[0];
        $inventory=new Inventory([
            'owner_id'=>$ownerId,
            'type'=>$type,
            'start_at'=>$date_start,
            'end_at'=>$date_end,
        ]);
        $inventory->save();
        $this->createInventoryMissionRecord($request,$inventory['id'],$ownerId);
        Controller::logS(__METHOD__,"创建盘点记录任务__".__FUNCTION__,json_encode($request,$inventory['id'],$ownerId),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($request,$inventoryId,$ownerId){
        $wmsInventories=$this->conditionSearch($request);
        foreach ($wmsInventories as $wmsInventory){
            $commodity=Commodity::query()->firstOrCreate([
                'owner_id'=>$ownerId,
                'sku'=>$wmsInventory->产品编码,
            ]);
            if (!$commodity->name){
                $commodity->name=$wmsInventory->商品名称;
                $commodity->update();
                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($request){
        $inventoryId=$request->inventoryId;
        $count=$request->count;
        $location=$request->location;
        $barcode=$request->barcode;
        $inventoryMission=InventoryMission::with(['commodity'=>function($query)use($barcode){
            return $query->with(['barcodes'=>function($sql)use($barcode){
                return $sql->where('code',$barcode);
            }]);
        }])->where('location',$location)->where('inventory_id',$inventoryId)->first();
        if (!$inventoryMission) return null;
        $inventoryMission->verified_amount=$count;
        $inventoryMission->difference_amount=abs($inventoryMission->stored_amount-$count);
        $inventoryMission->checked='是';
        $inventoryMission->update();
        Controller::logS(__METHOD__,"盘点__".__FUNCTION__,json_encode($request));
        return $inventoryMission;
    }
    //盘点修改盘点任务中的已盘条数
    public function updateInventoryMissionProcessed($request){
        $inventoryId=$request->inventoryId;
        $inventory=Inventory::find($inventoryId);
        $processed=InventoryMission::where('checked','是')->where('inventory_id',$inventoryId)->count();
        $inventory->processed=$processed;
        $inventory->update();
        Controller::logS(__METHOD__,"盘点修改盘点任务中的已盘条数__".__FUNCTION__,json_encode($request));
        return $inventory;
    }

    //库存体积条件
    function conditionQueryDailyLog(array $param){
        $inventoryDailyLogs = InventoryDailyLog::query()->with(['owner','commodity'=>function($query){
            $query->with('barcodes');
        }])->orderByDesc('id');
        $columnQueryRules=[
            'owner_id' => ['multi' => ','],
            'created_at_start' => ['alias' => 'created_at' , 'startDate' => ' 00:00:00'],
            'created_at_end' => ['alias' => 'created_at' , 'endDate' => ' 23:59:59'],
        ];
        $inventoryDailyLogs = app(QueryService::class)->query($param,$inventoryDailyLogs,$columnQueryRules);
        return $inventoryDailyLogs;

    }

    //库存体积
    public function getInventoryDailyLog(array $param){
        return $this->conditionQueryDailyLog($param)->paginate($param['paginate'] ?? 50);
    }

    //获取开启监听记录货主
    public function getInventoryDailyLoggingOwner($column = ['id','owner_id'], $status = "启用"){
        if (!is_array($column)) {
            $column = [$column];
        }
        return InventoryDailyLoggingOwner::query()->select($column)->where('status',$status)->get();
    }

    //录入监听记录货主
    public function firstOrCreate($param,$column = null){
        if ($column)return InventoryDailyLoggingOwner::query()->firstOrCreate($param,$column);
        return InventoryDailyLoggingOwner::query()->firstOrCreate($param);
    }
}