baoshi-was/app/Services/InventoryCompareService.php

<?php


namespace App\Services;

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

class InventoryCompareService
{
    static private $missionCode;
//    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 getCreatingMissionCode($ownerName=''){
        if(self::$missionCode)return self::$missionCode;
        $sequence=Cache::get('InventoryCompareMissionSequence');
        if(!$sequence||$sequence>998)$sequence=0;
        $sequence++;
        Cache::put('InventoryCompareMissionSequence',$sequence,300);
        $pinyin=new Pinyin();
        $ownerFirstLetter=strtoupper($pinyin->abbr($ownerName));
        self::$missionCode= $ownerFirstLetter.date ("ymd").'KCBD'.str_pad($sequence,3,"0",STR_PAD_LEFT);
        return self::$missionCode;
    }


    public function createInventoryCompare_underImport($sku, $custom_location, $amount, $owner_id,$owner_name){
        $creatingMissionCode = $this->getCreatingMissionCode($owner_name);
        $commodityId=Commodity::where('sku',$sku)->where('owner_id',$owner_id)->value('id');
        $inventoryCompare=new InventoryCompare();
        $inventoryCompare->owner_id=$owner_id;
        $inventoryCompare->commodity_id=$commodityId;
        $inventoryCompare->mission_code=Uuid::uuid1();
        $inventoryCompare->custom_location=$custom_location;
        $inventoryCompare->created_at=Carbon::now()->format('Y-m-d H:i:s');
        $inventoryCompare->quality='正品';
        $inventoryCompare->amount_in_sys=0;
        $inventoryCompare->amount_in_compare=$amount;
        $inventoryCompare->differ=0-$amount;
        $inventoryCompare->mission_code= $creatingMissionCode;
        $inventoryCompare->save();
        Controller::logS(__METHOD__,"修改库存对比任务号__".__FUNCTION__,json_encode($creatingMissionCode));
        return $inventoryCompare;
    }

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

    public function createInventoryCompares($inventoryCompares){

        $custom_locations = array_column($inventoryCompares,'custom_location');
        $custom_locations = array_unique($custom_locations);

        $skus = array_column($inventoryCompares,'sku');
        $skus = array_unique($skus);

        $query = OracleInvLotLocId::query()
            ->leftJoin('INV_LOT_ATT','INV_LOT_LOC_ID.LOTNUM','=','INV_LOT_ATT.LOTNUM')
            ->whereIn('INV_LOT_LOC_ID.sku',$skus)
            ->whereIn('INV_LOT_ATT.LOTATT05',$custom_locations)
            ->selectRaw('INV_LOT_LOC_ID.customerid 货主编码,INV_LOT_ATT.LOTATT05 属性仓,INV_LOT_LOC_ID.sku 产品编码,
                INV_LOT_ATT.LOTATT08 质量状态,sum(INV_LOT_LOC_ID.QTY) 在库数量')
            ->groupBy(['INV_LOT_ATT.LOTATT05','INV_LOT_LOC_ID.customerid','INV_LOT_LOC_ID.sku','INV_LOT_ATT.LOTATT08'])
            ->get();

        $wasInventoryCompares=[];
        foreach ($inventoryCompares as $inventoryCompare){
            $owner_id=$inventoryCompare['owner_id'];
            $owner_name=$inventoryCompare['owner_name'];
            $owner_code=$inventoryCompare['owner_code'];
            $sku=$inventoryCompare['sku'];
            $custom_location=$inventoryCompare['custom_location'];
            $amount=$inventoryCompare['amount'];

            $wmsInventoryCompareZp=$query->where('属性仓',$custom_location)->where('产品编码',$sku)->where('质量状态','ZP')->first();
            $wmsInventoryCompareCc=$query->where('属性仓',$custom_location)->where('产品编码',$sku)->where('质量状态','CC')->first();
            $wmsInventoryCompareDj=$query->where('属性仓',$custom_location)->where('产品编码',$sku)->where('质量状态','DJ')->first();
            $unknownQualityStatus=$query->where('属性仓',$custom_location)->where('产品编码',$sku)->whereNotIn('质量状态',['DJ','CC','ZP']);
            if (!$wmsInventoryCompareZp&&!$wmsInventoryCompareCc&&!$wmsInventoryCompareDj&&$unknownQualityStatus->isEmpty()){
                $inventoryCompare=$this->createInventoryCompare_underImport($sku, $custom_location, $amount, $owner_id,$owner_name);
            }
            $creatingMissionCode = $this->getCreatingMissionCode($owner_name);
            $commodityId=Commodity::where('sku',$sku)->where('owner_id',$owner_id)->value('id');
            if ($wmsInventoryCompareZp||$wmsInventoryCompareDj) {
                $wasInventoryCompareZP = [
                    'owner_id' => $owner_id,
                    'commodity_id' => $commodityId,
                    'mission_code' => $creatingMissionCode,
                    'custom_location' => $custom_location,
                    'created_at' => Carbon::now()->format('Y-m-d H:i:s'),
                    'quality' => '正品',
                    'amount_in_sys' => ($wmsInventoryCompareZp['在库数量'] ?? 0) + ($wmsInventoryCompareDj['在库数量'] ?? 0),
                    'amount_in_compare' => $amount,
                    'differ' => $amount - ($wmsInventoryCompareZp['在库数量'] ?? 0) - ($wmsInventoryCompareDj['在库数量'] ?? 0),
                ];
                array_push($wasInventoryCompares,$wasInventoryCompareZP);
            }
            if ($wmsInventoryCompareCc){
                $wasInventoryCompareCC=[
                    'owner_id'=>$owner_id,
                    'commodity_id'=>$commodityId,
                    'mission_code'=>$creatingMissionCode,
                    'custom_location'=>$custom_location,
                    'created_at'=>Carbon::now()->format('Y-m-d H:i:s'),
                    'quality'=>'次品',
                    'amount_in_sys'=>$wmsInventoryCompareCc['在库数量'],
                    'amount_in_compare' =>'',
                    'differ' =>'',
                ];
                array_push($wasInventoryCompares,$wasInventoryCompareCC);
            }
            if($unknownQualityStatus->isNotEmpty())return null;
        }
        $inventoryCompares=DB::table('inventory_compares')->insert($wasInventoryCompares);
        if (!$inventoryCompares)return null;
        return $inventoryCompares;
    }
    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 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;
//        $creatingMissionCode = $this->getCreatingMissionCode($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');
//        $inventoryCompare->mission_code=$creatingMissionCode;
//        if ($zpAmount!=0){
//            $inventoryCompare->quality='正品';
//            $inventoryCompare->amount_in_sys=$zpAmount;
//            $inventoryCompare->amount_in_compare=$amount;
//            $inventoryCompare->differ=$zpAmount-$amount;
//        }
//        if ($ccAmount!=0){
//            $inventoryCompare->quality='次品';
//            $inventoryCompare['amount_in_sys']=$ccAmount;
//        }
//        if ($unknownStatusAmount!=0){
//            $inventoryCompare->quality='未知';
//            $inventoryCompare->amount_in_sys=$unknownStatusAmount;
//        }
//        $inventoryCompare->save();
//        Controller::logS(__METHOD__,"创建库存对比__".__FUNCTION__,json_encode($request));
//        Controller::logS(__METHOD__,"修改库存对比任务号__".__FUNCTION__,json_encode($creatingMissionCode));
//        return $inventoryCompare;
//    }
//
}