neiyou_user_web/app/Console/Commands/AutoStatisticsUserHourlyTraffic.php

<?php

namespace App\Console\Commands;

use App\Models\Node;
use App\Models\User;
use App\Models\UserDataFlowLog;
use App\Models\UserHourlyDataFlow;
use Illuminate\Console\Command;
use Log;

class AutoStatisticsUserHourlyTraffic extends Command
{
    protected $signature = 'autoStatisticsUserHourlyTraffic';
    protected $description = '自动统计用户每小时流量';



    public function handle(): void
    {
        $jobStartTime = microtime(true);
        echo "🟢 开始执行 autoStatisticsUserHourlyTraffic...\n";
        Log::info('🟢 开始执行 autoStatisticsUserHourlyTraffic');

        $startTime = strtotime('-1 hour');
        $endTime = time();

        // 获取 [user_id, node_id] 的流量聚合
        $records = UserDataFlowLog::whereBetween('log_time', [$startTime, $endTime])
            ->selectRaw('user_id, node_id, SUM(u) as sum_u, SUM(d) as sum_d')
            ->groupBy('user_id', 'node_id')
            ->get();

        $userTotals = [];
        $checkedUsers = [];
        $checkedNodes = [];

        foreach ($records as $record) {
            $user_id = $record->user_id;
            $node_id = $record->node_id;

            // 缓存判断用户是否存在
            if (!isset($checkedUsers[$user_id])) {
                $checkedUsers[$user_id] = User::where('id', $user_id)->exists();
            }
            if (!$checkedUsers[$user_id]) {
                Log::warning("跳过记录，用户不存在: user_id=$user_id");
                continue;
            }

            // 缓存判断节点是否存在
            if (!isset($checkedNodes[$node_id])) {
                $checkedNodes[$node_id] = Node::where('id', $node_id)->exists();
            }
            if (!$checkedNodes[$node_id]) {
                Log::warning("跳过记录，节点不存在: node_id=$node_id");
                continue;
            }

            $u = $record->sum_u ?? 0;
            $d = $record->sum_d ?? 0;
            $total = $u + $d;

            //Log::info("📊 保存流量记录 - user: $user_id, node: $node_id, u: $u, d: $d, total: $total");

            UserHourlyDataFlow::create([
                'user_id' => $user_id,
                'node_id' => $node_id,
                'u'       => $u,
                'd'       => $d,
                'total'   => $total,
                'traffic' => flowAutoShow($total),
            ]);

            // 累计每个用户的总流量
            if (!isset($userTotals[$user_id])) {
                $userTotals[$user_id] = 0;
            }
            $userTotals[$user_id] += $total;
        }

        // 判断是否超出阈值
        $trafficBanValue = sysConfig('traffic_ban_value');

        foreach ($userTotals as $user_id => $total) {
            if ($total > $trafficBanValue * GB) {
                $user = User::find($user_id);
                $email = $user->email ?? '未知邮箱';
                Log::warning("⚠️ 用户 [ID: $user_id, 邮箱: $email] 最近1小时流量异常：已使用 ".flowAutoShow($total));
                echo "⚠️ 异常用户: $email, 使用 ".flowAutoShow($total)."\n";
            }
        }

        $jobUsedTime = round((microtime(true) - $jobStartTime), 4);
        Log::info("✅ autoStatisticsUserHourlyTraffic 执行完成，耗时 {$jobUsedTime} 秒");
        echo "✅ 执行完成，耗时 {$jobUsedTime} 秒\n";
    }


    private function statisticsByNode($user_id, $node_id = null): void
    {
        $query = UserDataFlowLog::whereUserId($user_id)
            ->whereBetween('log_time', [strtotime('-1 hour'), time()]);

        if ($node_id) {
            $query->whereNodeId($node_id);
        }

        $sums = $query->selectRaw('SUM(u) as sum_u, SUM(d) as sum_d')->first();

        $u = $sums->sum_u ?? 0;
        $d = $sums->sum_d ?? 0;
        $total = $u + $d;

        if ($total > 0) {
            Log::info("📊 保存流量记录 - user: $user_id, node: " . ($node_id ?? '总计') . ", u: $u, d: $d, total: $total");
            UserHourlyDataFlow::create([
                'user_id' => $user_id,
                'node_id' => $node_id,
                'u'       => $u,
                'd'       => $d,
                'total'   => $total,
                'traffic' => flowAutoShow($total),
            ]);
        }

        // 检查是否超过阈值（仅统计总流量）
        if (is_null($node_id)) {
            $trafficBanValue = sysConfig('traffic_ban_value');
            if ($total > $trafficBanValue * GB) {
                $user = User::find($user_id);
                $email = $user->email ?? '未知邮箱';
                Log::warning("⚠️ 用户 [ID: $user_id, 邮箱: $email] 最近1小时流量异常：已使用 ".flowAutoShow($total));
                echo "⚠️ 异常用户: $email, 使用 ".flowAutoShow($total)."\n";
            }
        }
    }
}