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\BatchUpdateService;
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;
use App\Traits\ServiceAppAop;


class InventoryCompareService
{
    use ServiceAppAop;
    static private $missionCode;
    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=$amount-0;
        $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();
            $wmsInventoryCompareYjz=$query->where('属性仓',$custom_location)->where('产品编码',$sku)->where('质量状态','YJZ')->first();
            $unknownQualityStatus=$query->where('属性仓',$custom_location)->where('产品编码',$sku)->whereNotIn('质量状态',['DJ','CC','ZP','YJZ']);
            if (!$wmsInventoryCompareZp&&!$wmsInventoryCompareCc&&!$wmsInventoryCompareDj&&!$wmsInventoryCompareYjz&&$unknownQualityStatus->isEmpty()){
                $this->createInventoryCompare_underImport($sku, $custom_location, $amount, $owner_id,$owner_name);
                continue;
            }
            $creatingMissionCode = $this->getCreatingMissionCode($owner_name);
            $commodityId=Commodity::where('sku',$sku)->where('owner_id',$owner_id)->value('id');
            $params = [
                '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'),
                'amount_in_compare'=>$amount,
            ];
            if ($wmsInventoryCompareZp||$wmsInventoryCompareDj) {
                $params['quality'] = '正品';
                $params['amount_in_sys'] = ($wmsInventoryCompareZp['在库数量'] ?? 0)+($wmsInventoryCompareDj['在库数量'] ?? 0);
                $params['differ'] = $amount-(($wmsInventoryCompareZp['在库数量'] ?? 0) + ($wmsInventoryCompareDj['在库数量'] ?? 0));
                array_push($wasInventoryCompares,$params);
            }
            if ($wmsInventoryCompareCc){
                $params['quality'] = '次品';
                $params['amount_in_sys'] = $wmsInventoryCompareCc['在库数量'];
                $params['differ'] = $amount-($wmsInventoryCompareCc['在库数量']??0);
                array_push($wasInventoryCompares,$params);
            }
            if ($wmsInventoryCompareYjz){
                $params['quality'] = '有价值';
                $params['amount_in_sys'] = $wmsInventoryCompareYjz['在库数量'];
                $params['differ'] = $amount-($wmsInventoryCompareYjz['在库数量']??0);
                array_push($wasInventoryCompares,$params);
            }
            if($unknownQualityStatus->isNotEmpty())continue;
        }
        $inventoryCompares=DB::table('inventory_compares')->insert($wasInventoryCompares);
        if (!$inventoryCompares)return null;
        return $inventoryCompares;
    }
    private function conditionQueryInventoryCompare(array $param){
        $ownerIds=app('OwnerService')->getSelection();
        $differ=$param['differ']??'';
        if ($differ=='有'){
            $inventoryCompares = InventoryCompare::query()->with(['owner','commodity'=>function($query){
                $query->with('barcodes');
            }])->where('differ','>',0)->orderByDesc('id')->whereIn('inventory_compares.owner_id',$ownerIds);
        }elseif ($differ=='无'){
            $inventoryCompares = InventoryCompare::query()->with(['owner','commodity'=>function($query){
                $query->with('barcodes');
            }])->where('differ','<',0)->orderByDesc('id')->whereIn('inventory_compares.owner_id',$ownerIds);
        }else{
            $inventoryCompares = InventoryCompare::query()->with(['owner','commodity'=>function($query){
                $query->with('barcodes');
            }])->orderByDesc('id')->whereIn('inventory_compares.owner_id',$ownerIds);
        }
        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' => 15],
            'custom_location' => ['like' => '','timeLimit' => 15],
            'id' => ['multi' => ','],
        ];
        $inventoryCompares = app(QueryService::class)->query($param,$inventoryCompares,$columnQueryRules,'inventory_compares');
        return $inventoryCompares;
    }

    /**
     * @param array $params
     * @return string $sql
     */
    public function getSql(array $params){
      return  $this->conditionQueryInventoryCompare($params)
            ->selectRaw("inventory_compares.*")
            ->leftJoin('owners','inventory_compares.owner_id','owners.id')
                ->selectRaw('owners.name owner_name')
            ->leftJoin('commodities','inventory_compares.commodity_id','commodities.id')
                ->selectRaw('commodities.name commodity_name,commodities.sku commodity_sku')
            ->leftJoin('commodity_barcodes','commodity_barcodes.commodity_id','commodities.id')
                ->selectRaw('commodity_barcodes.code commodity_barcode_code')
                ->groupBy('owner_name','commodity_sku','inventory_compares.custom_location','inventory_compares.quality')
            ->sql();
    }

    public function batchUpdateItself($column, array $params)
    {
        return app(BatchUpdateService::class)->batchUpdateItself('inventory_compares', $column, $params);
    }
}