neiyou_user_web/app/Console/Commands/AutoStatisticsUserDailyTraffic.php

<?php

namespace App\Console\Commands;

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

class AutoStatisticsUserDailyTraffic extends Command
{
    protected $signature = 'autoStatisticsUserDailyTraffic';
    protected $description = '自动统计用户每日流量';

    public function handle(): void
    {
        $jobStartTime = microtime(true);
        $today = date('Y-m-d');
        $startTime = strtotime($today);
        $endTime = time();

        try {
            // 缓存用户和节点存在性检查
            $checkedUsers = [];
            $checkedNodes = [];
            
            // 直接遍历 UserDataFlowLog 表
            UserDataFlowLog::whereBetween('log_time', [$startTime, $endTime])
                ->select('user_id', 'node_id')
                ->selectRaw('SUM(u) as total_u, SUM(d) as total_d')
                ->groupBy('user_id', 'node_id')
                ->chunk(100, function ($logs) use ($checkedUsers, $checkedNodes, $today) {
                    foreach ($logs as $log) {
                        try {
                            // 检查用户是否存在
                            if (!isset($checkedUsers[$log->user_id])) {
                                $checkedUsers[$log->user_id] = User::where('id', $log->user_id)->exists();
                            }
                            if (!$checkedUsers[$log->user_id]) {
                                Log::warning("跳过记录，用户不存在: user_id={$log->user_id}");
                                continue;
                            }

                            // 检查节点是否存在（如果node_id不为null）
                            if ($log->node_id !== null) {
                                if (!isset($checkedNodes[$log->node_id])) {
                                    $checkedNodes[$log->node_id] = Node::where('id', $log->node_id)->exists();
                                }
                                if (!$checkedNodes[$log->node_id]) {
                                    Log::warning("跳过记录，节点不存在: node_id={$log->node_id}");
                                    continue;
                                }
                            }

                            $total = $log->total_u + $log->total_d;
                            if ($total > 0) {
                                UserDailyDataFlow::updateOrCreate(
                                    [
                                        'user_id' => $log->user_id,
                                        'node_id' => $log->node_id,
                                        'created_at' => $today
                                    ],
                                    [
                                        'u' => $log->total_u,
                                        'd' => $log->total_d,
                                        'total' => $total,
                                        'traffic' => flowAutoShow($total)
                                    ]
                                );
                            }
                        } catch (\Exception $e) {
                            Log::error('处理流量记录失败', [
                                'user_id' => $log->user_id,
                                'node_id' => $log->node_id,
                                'error' => $e->getMessage()
                            ]);
                        }
                    }
                });

            $jobEndTime = microtime(true);
            $jobUsedTime = round(($jobEndTime - $jobStartTime), 4);

            Log::info('---【'.$this->description.'】完成---，耗时'.$jobUsedTime.'秒');
        } catch (\Exception $e) {
            Log::error('统计用户流量任务失败', [
                'error' => $e->getMessage(),
                'trace' => $e->getTraceAsString()
            ]);
        }
    }
}