baoshi-was/app/Console/Commands/InventoryDailyLoggingOwner.php

<?php

namespace App\Console\Commands;

use App\CommodityBarcode;
use App\Services\CommodityService;
use Illuminate\Console\Command;
use App\InventoryDailyLoggingOwner as LoggingOwner;
use Illuminate\Support\Facades\DB;

class InventoryDailyLoggingOwner extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'InventoryDailyLoggingOwner';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '监听指定货主记录';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     * @param CommodityService $commodityService
     * @return int
     */
    public function handle(CommodityService $commodityService)
    {
        //获取需要查询的货主，键值对：code => id
        $owners = $this->getLoggingOwners();
        //计算数量，为0直接return
        $let = count($owners);
        if ($let == 0)return;
        //拼接SQL,SELECT仅取指定字段
        $sql = "SELECT INV_LOT.customerid,INV_LOT.sku,INV_LOT.qty,BAS_SKU.skulength,
                BAS_SKU.skuwidth,BAS_SKU.skuhigh,BAS_SKU.cube,BAS_SKU.descr_c,BAS_SKU.alternate_sku1 
                FROM INV_LOT
                LEFT JOIN BAS_SKU ON INV_LOT.sku = BAS_SKU.sku AND INV_LOT.customerid = BAS_SKU.customerid
                WHERE INV_LOT.customerid IN (";
        $index = 1;
        foreach ($owners as $code => $id){
            $sql .= "'".$code."'";
            if ($index != $let)$sql .= ",";
            $index++;
        }
        $sql .= ")";
        //执行获取结果：stdClass类型
        $invLots = DB::connection('oracle')->select(DB::raw($sql));
        //声明一个数组，作为第一次去重的容器
        $inventoryDailyLogs = [];
        foreach ($invLots as $invLot){
            //以MAP形式记录进数组，货主code与商品sku作为联合主键唯一标识，如重复叠加其数量
            if ($inventoryDailyLogs[$owners[$invLot->customerid].'-'.$invLot->sku] ?? false){
                $inventoryDailyLogs[$owners[$invLot->customerid].'-'.$invLot->sku]['amount'] += $invLot->qty;
            }else{
                //符合的结果取此对象的关键信息存进第一个数组
                $inventoryDailyLogs[$owners[$invLot->customerid].'-'.$invLot->sku] = [
                    'commodity' => [
                        'owner_id'=>$owners[$invLot->customerid],
                        'sku'=>$invLot->sku,
                        'name'=>$invLot->descr_c,
                        'length'=>$invLot->skulength,
                        'width'=>$invLot->skuwidth,
                        'height'=>$invLot->skuhigh,
                        'volumn'=>$invLot->cube,
                        'code'=>$invLot->alternate_sku1,
                    ],
                    'amount' => $invLot->qty,
                    'volumn_occupied' => 0,
                ];
            }
        }
        //第二个数组作为批量插入使用
        $data = [];
        //遍历第一个数组，此时已经去重完成，直接取对应参数push进data中
        foreach ($inventoryDailyLogs as $inventoryDailyLog){
            //寻找己方库中是否存在对应商品，存在更新其长宽高体积，不存在录入
            $commodity = $inventoryDailyLog['commodity'];
            $param = ['owner_id'=>$commodity['owner_id'],'sku'=>$commodity["sku"]];
            //体积存在为0的情况，需再次计算一次，此处体积为m³
            if (!$commodity['volumn'] || $commodity['volumn'] == "0"){
                $commodity['volumn'] = $commodity['length']*$commodity['width']*$commodity['height'];
            }
            $column = [
                'owner_id'=>$commodity['owner_id'],
                'sku'=>$commodity['sku'],
                'name'=>$commodity['name'],
                'length'=>$commodity['length'],
                'width'=>$commodity['width'],
                'height'=>$commodity['height'],
                'volumn'=>$commodity['volumn'],
            ];
            $result = $commodityService->updateOrCreate($param,$column);
            $commodity_id = $result->id;
            //寻找对应barcode是否存在，不存在录入
            if ($commodity['code']) CommodityBarcode::query()->firstOrCreate(['commodity_id'=>$commodity_id,'code'=>$commodity['code']]);
            //计算总体积，商品体积×该单数量
            $volumn_occupied = $commodity['volumn']*$inventoryDailyLog["amount"];
            array_push($data,[
                "owner_id"=>$commodity['owner_id'],
                "created_at"=>date('Y-m-d H:i:s'),
                "commodity_id"=>$commodity_id,
                "amount"=>$inventoryDailyLog['amount'],
                "volumn_occupied"=>$volumn_occupied,
            ]);
        }
        DB::table('inventory_daily_logs')->insert($data);
    }

    public function getLoggingOwners(){
        $loggingOwners = LoggingOwner::with('owner')->select('id','owner_id')->where('status','启用')->get();
        $owners = [];
        foreach ($loggingOwners as $loggingOwner){
            if ($loggingOwner->owner){
                $owners[$loggingOwner->owner->code] = $loggingOwner->owner_id;
            }
        }
        return $owners;
    }
}