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
    {
        $logPrefix = '[用户流量统计]';
        $jobStartTime = microtime(true);
        echo "🟢 开始执行 autoStatisticsUserHourlyTraffic...\n";
        Log::info("$logPrefix 🟢 开始执行 autoStatisticsUserHourlyTraffic");

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

        $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 = [];
        $userWeightedTotals = [];
        $checkedUsers = [];
        $checkedNodes = [];
        $maxUserId = null;
        $maxTraffic = 0;

        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("$logPrefix 跳过记录，用户不存在: user_id=$user_id");
                continue;
            }

            // 判断节点是否存在（带缓存）
            if (!isset($checkedNodes[$node_id])) {
                $node = Node::find($node_id);
                $checkedNodes[$node_id] = $node ?: false;
            }
            if (!$checkedNodes[$node_id]) {
                Log::warning("$logPrefix 跳过记录，节点不存在: node_id=$node_id");
                continue;
            }

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

            $rate = floatval($checkedNodes[$node_id]->traffic_rate ?? 1);
            $weighted = $rate > 0 ? $total / $rate : $total;

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

            $userTotals[$user_id] = ($userTotals[$user_id] ?? 0) + $total;
            $userWeightedTotals[$user_id] = ($userWeightedTotals[$user_id] ?? 0) + $weighted;

            if ($userWeightedTotals[$user_id] > $maxTraffic) {
                $maxTraffic = $userWeightedTotals[$user_id];
                $maxUserId = $user_id;
            }
        }

        $trafficBanValue = sysConfig('traffic_ban_value');

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

        if ($maxUserId !== null) {
            $maxUser = User::find($maxUserId);
            $email = $maxUser->email ?? '未知邮箱';

            $rawTraffic = 0;
            $rawUpload = 0;
            $rawDownload = 0;
            $convertedTraffic = $userWeightedTotals[$maxUserId] ?? 0;
            $details = [];

            foreach ($records as $record) {
                if ($record->user_id == $maxUserId) {
                    $u = $record->sum_u ?? 0;
                    $d = $record->sum_d ?? 0;
                    $total = $u + $d;

                    $node = $checkedNodes[$record->node_id];
                    $rate = floatval($node->traffic_rate ?? 1);
                    $nodeName = $node->name ?? "未知节点";

                    if ($rate == 1) {
                        $rawUpload += $u;
                        $rawDownload += $d;
                        $rawTraffic += $total;
                    } else {
                        $converted = $rate > 0 ? $total / $rate : $total;
                        $details[] = "节点[$nodeName] 倍率 $rate => 上传: " . flowAutoShow($u) .
                            ", 下载: " . flowAutoShow($d) .
                            ", 总计: " . flowAutoShow($total) .
                            " => 换算后（按倍率 {$rate}）: " . flowAutoShow($converted);
                    }
                }
            }

            $detailsText = count($details) > 0 ? " 🔁 明细: " . implode("，", $details) : "未使用大于1倍的线路";

            $message = "🚨 过去1小时使用流量最多的用户：$email (ID: $maxUserId)，🟦 上行: " . flowAutoShow($rawUpload) .
                "，🟥 下行: " . flowAutoShow($rawDownload) .
                "，原始流量: " . flowAutoShow($rawTraffic) .
                "（仅倍率为1的线路），换算为1倍率流量: " . flowAutoShow($convertedTraffic) .
                $detailsText;

            echo "$message\n";
            Log::info("$logPrefix $message");
        }

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

        $nextRunTimestamp = time() + 3600;
        $nextRunTimeFormatted = date('Y-m-d H:i:s', $nextRunTimestamp);
        echo "⏰ 下次预计运行时间：$nextRunTimeFormatted\n";
        Log::info("$logPrefix ⏰ 下次预计运行时间：$nextRunTimeFormatted");
    }


    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";
            }
        }
    }
}