bot-28/app/Services/RegisterStatService.php

<?php

namespace App\Services;

use App\Models\User;
use App\Models\UserLogin;
use Illuminate\Support\Facades\DB;

/**
 * 注册走势 / 域名登录人数统计
 */
class RegisterStatService
{
    /**
     * @param string $type today|all
     * @return array{type:string,times:array,member:array,virtual:array,member_total:int,virtual_total:int,...}
     */
    public static function trend(string $type, ?string $startDate, ?string $endDate): array
    {
        $today = date('Y-m-d');
        $startDate = $startDate ?: $today;
        $endDate = $endDate ?: $today;

        if ($type === 'today') {
            $day = $startDate;
            $bucketExpr = "DATE_FORMAT(created_at, '%H:00')";
            $times = [];
            for ($h = 0; $h < 24; $h++) {
                $times[] = sprintf('%02d:00', $h);
            }
            $rangeStart = $day . ' 00:00:00';
            $rangeEnd = $day . ' 23:59:59';
            $meta = ['type' => 'today', 'date' => $day];
        } else {
            $bucketExpr = "DATE_FORMAT(created_at, '%Y-%m-%d')";
            $times = [];
            $cursor = strtotime($startDate);
            $endTs = strtotime($endDate);
            while ($cursor <= $endTs) {
                $times[] = date('Y-m-d', $cursor);
                $cursor = strtotime('+1 day', $cursor);
            }
            $rangeStart = $startDate . ' 00:00:00';
            $rangeEnd = $endDate . ' 23:59:59';
            $meta = [
                'type' => 'all',
                'start_time' => $startDate,
                'end_time' => $endDate,
            ];
        }

        // 测试代理名下会员不统计
        $rows = \App\Support\StatsScope::excludeTestUsers(User::query(), 'users')
            ->selectRaw("{$bucketExpr} as time_key, `from` as user_from, COUNT(*) as cnt")
            ->whereBetween('created_at', [$rangeStart, $rangeEnd])
            ->groupByRaw("{$bucketExpr}, `from`")
            ->get();

        $memberMap = array_fill_keys($times, 0);
        $virtualMap = array_fill_keys($times, 0);
        foreach ($rows as $row) {
            $key = (string)$row->time_key;
            if (!array_key_exists($key, $memberMap)) {
                continue;
            }
            if ((string)$row->user_from === '2') {
                $virtualMap[$key] += (int)$row->cnt;
            } else {
                $memberMap[$key] += (int)$row->cnt;
            }
        }

        return array_merge($meta, [
            'times' => $times,
            'member' => array_values($memberMap),
            'virtual' => array_values($virtualMap),
            'member_total' => array_sum($memberMap),
            'virtual_total' => array_sum($virtualMap),
        ]);
    }

    /**
     * 域名成功登录人数统计（同域名按用户去重，SQL 分页）
     *
     * @return array{total:int,data:list<array{login_url:string,login_count:int}>}
     */
    public static function domain(?string $startDate, ?string $endDate, int $page, int $limit): array
    {
        $domainExpr = "CASE WHEN login_domain = '' OR login_domain IS NULL THEN '未知' ELSE login_domain END";

        $base = DB::table('user_login')
            ->where('status', UserLogin::STATUS_SUCCESS)
            ->whereNotNull('user_id')
            ->where('user_id', '<>', '');
        // user_login.user_id 是会员登录 ID（users.user_id）；测试代理名下会员不统计
        \App\Support\StatsScope::excludeTestMembers($base, 'user_id', 'user_id');
        if ($startDate) {
            $base->where('created_at', '>=', $startDate . ' 00:00:00');
        }
        if ($endDate) {
            $base->where('created_at', '<=', $endDate . ' 23:59:59');
        }

        $grouped = (clone $base)
            ->selectRaw("{$domainExpr} as login_url, COUNT(DISTINCT user_id) as login_count")
            ->groupByRaw($domainExpr);

        $total = (int)DB::query()->fromSub(
            (clone $grouped),
            'domain_groups'
        )->count();

        $offset = max(0, ($page - 1) * $limit);
        $rows = (clone $grouped)
            ->orderByDesc('login_count')
            ->orderBy('login_url')
            ->offset($offset)
            ->limit($limit)
            ->get();

        $data = $rows->map(fn ($row) => [
            'login_url' => (string)$row->login_url,
            'login_count' => (int)$row->login_count,
        ])->values()->all();

        return ['total' => $total, 'data' => $data];
    }
}