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;

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=$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();
            $unknownQualityStatus=$query->where('属性仓',$custom_location)->where('产品编码',$sku)->whereNotIn('质量状态',['DJ','CC','ZP']);
            if (!$wmsInventoryCompareZp&&!$wmsInventoryCompareCc&&!$wmsInventoryCompareDj&&$unknownQualityStatus->isEmpty()){
                $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' =>$amount,
                    'differ' =>$amount-($wmsInventoryCompareCc['在库数量']??0),
                ];
                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){
        $ownerIds=app('OwnerService')->getSelectionId();
        $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' => 20],
            '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);
    }
}