baoshi-was/app/Services/InventoryCompareService.php

<?php


namespace App\Services;

use App\Commodity;
use App\Http\Controllers\Controller;
use App\InventoryCompare;
use App\Owner;
use App\Services\common\QueryService;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;
use Overtrue\Pinyin\Pinyin;
use Ramsey\Uuid\Uuid;

class InventoryCompareService
{
    private function conditionQuery($SKU,$LotAtt05,$descr_c){
        $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 批号 ';
        $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  ';
        $sql.=' )result where 1=1 ';
        if ($SKU)$sql.=" and 产品编码 like '".$SKU."' ";
        if ($LotAtt05)$sql .=" and 属性仓 like '".$LotAtt05."' ";
        if ($descr_c)$sql.=" and 货主 = '".$descr_c."' ";
        $sql.=' )  ';
        return DB::connection('oracle')->select($sql);
    }

    public function createInventoryCompare($SKU,$LotAtt05,$amount,$ownerId){
        $ownerName=Owner::where('id',$ownerId)->value('name');
        $request=[
            '产品编码'=>$SKU,
            '属性仓'=>$LotAtt05,
            '数量'=>$amount,
            '货主'=>$ownerId,
        ];
        $wmsInventories=$this->conditionQuery($SKU,$LotAtt05,$ownerName);
        if (count($wmsInventories)==0) return null;
        $zpAmount=0;
        $ccAmount=0;
        $amountTotal=0;
        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));
            $amountTotal=$amountTotal+$wmsInventory->在库数量;
            if ($wmsInventory->质量状态=='DJ'||$wmsInventory->质量状态=='ZP'){
                $zpAmount=$zpAmount+$wmsInventory->在库数量;
            }
            if ($wmsInventory->质量状态=='CC'){
                $ccAmount=$ccAmount+$wmsInventory->在库数量;
            }
        }
        $unknownStatusAmount=$amountTotal-$ccAmount-$zpAmount;
        $pinyin=new Pinyin();
        $owner=strtoupper($pinyin->abbr($ownerName));
        $commodityId=Commodity::where('sku',$SKU)->where('owner_id',$ownerId)->value('id');
        $inventoryCompare=new InventoryCompare();
        $inventoryCompare->owner_id=$ownerId;
        $inventoryCompare->commodity_id=$commodityId;
        $inventoryCompare->mission_code=Uuid::uuid1();
        $inventoryCompare->custom_location=$LotAtt05;
        $inventoryCompare->created_at=Carbon::now()->format('Y-m-d H:i:s');
        if ($zpAmount!=0){
            $inventoryCompare->quality='正品';
            $inventoryCompare->amount_in_sys=$zpAmount;
            $inventoryCompare->amount_in_compare=$amount;
            $inventoryCompare->differ=$zpAmount-$amount;
            $inventoryCompare->save();
            Controller::logS(__METHOD__,"创建库存对比__".__FUNCTION__,json_encode($request));
            $mission_code =$owner.date ("ymd").'KCBD'.str_pad($inventoryCompare['id']>99999?$inventoryCompare['id']%99999:$inventoryCompare['id'],4,"0",STR_PAD_LEFT);
            $inventoryCompare->update(['mission_code' => $mission_code ]);
            Controller::logS(__METHOD__,"修改库存对比任务号__".__FUNCTION__,json_encode($mission_code));
        }
        if ($ccAmount!=0){
            $inventoryCompare->quality='次品';
            $inventoryCompare['amount_in_sys']=$ccAmount;
            $inventoryCompare->save();
            Controller::logS(__METHOD__,"创建库存对比__".__FUNCTION__,json_encode($request));
            $mission_code =$owner.date ("ymd").'KCBD'.str_pad($inventoryCompare['id']>99999?$inventoryCompare['id']%99999:$inventoryCompare['id'],4,"0",STR_PAD_LEFT);
            $inventoryCompare->update(['mission_code' => $mission_code ]);
            Controller::logS(__METHOD__,"修改库存对比任务号__".__FUNCTION__,json_encode($mission_code));
        }
        if ($unknownStatusAmount!=0){
            $inventoryCompare->quality='未知';
            $inventoryCompare->amount_in_sys=$unknownStatusAmount;
            $inventoryCompare->save();
            Controller::logS(__METHOD__,"创建库存对比__".__FUNCTION__,json_encode($request));
            $mission_code =$owner.date ("ymd").'KCBD'.str_pad($inventoryCompare['id']>99999?$inventoryCompare['id']%99999:$inventoryCompare['id'],4,"0",STR_PAD_LEFT);
            $inventoryCompare->update(['mission_code' => $mission_code ]);
            Controller::logS(__METHOD__,"修改库存对比任务号__".__FUNCTION__,json_encode($mission_code));
        }
        return $inventoryCompare;
    }

    private function conditionQueryInventoryCompare(array $param){
        $differ=$param['differ']??'';
        if ($differ=='有'){
            $inventoryCompares = InventoryCompare::query()->with(['owner','commodity'=>function($query){
                $query->with('barcodes');
            }])->where('differ','>',0)->orderByDesc('id');
        }elseif ($differ=='无'){
            $inventoryCompares = InventoryCompare::query()->with(['owner','commodity'=>function($query){
                $query->with('barcodes');
            }])->where('differ','<',0)->orderByDesc('id');
        }else{
            $inventoryCompares = InventoryCompare::query()->with(['owner','commodity'=>function($query){
                $query->with('barcodes');
            }])->orderByDesc('id');
        }
        unset($param['differ']);
        $columnQueryRules=[
            'owner_id' => ['multi' => ','],
            'date_start' => ['alias' => 'created_at' , 'startDate' => ' 00:00:00'],
            'date_end' => ['alias' => 'created_at' , 'endDate' => ' 23:59:59'],
            'mission_code' => ['timeLimit' => 20],
        ];
        $inventoryCompares = app(QueryService::class)->query($param,$inventoryCompares,$columnQueryRules);
        return $inventoryCompares;
    }

    public function getInventoryCompare(array $param){
        return $this->conditionQueryInventoryCompare($param)->paginate($param['paginate'] ?? 50);
    }
}