bot-28/app/Services/NewUserSummaryService.php

<?php

namespace App\Services;

use Illuminate\Support\Facades\DB;

/**
 * 新用户汇总：区间集合查询，按天输出（无按天 PHP 循环打库）
 *
 * 口径：
 * - H5/APP：register_domain 是否为包名风格（com.xxx.yyy）
 * - 活跃会员：当日新注册且当日下注 >= 50
 * - 有效会员：当日新注册且当日存款 >= 50
 * - 公司盈利：当日新用户当日 投注额 - 派奖
 */
class NewUserSummaryService
{
    public const ACTIVE_BET_THRESHOLD = 50;
    public const VALID_DEPOSIT_THRESHOLD = 50;

    /** MySQL：包名风格域名视为 APP */
    private const IS_APP_SQL = "(register_domain REGEXP '^(com|org|net|io|cn)\\\\.[a-zA-Z0-9_]+(\\\\.[a-zA-Z0-9_]+)+$')";

    /**
     * @return array{start_time:string,end_time:string,rules:array,data:list<array>}
     */
    public static function summarize(string $startDate, string $endDate): array
    {
        $rangeStart = $startDate . ' 00:00:00';
        $rangeEnd = $endDate . ' 23:59:59';
        $isApp = self::IS_APP_SQL;

        // 当日新注册用户（供后续 join）
        $newUsers = DB::table('users')
            ->selectRaw("
                DATE(created_at) as reg_date,
                COALESCE(NULLIF(member_id, ''), user_id) as member_key,
                CASE WHEN {$isApp} THEN 1 ELSE 0 END as is_app
            ")
            ->whereBetween('created_at', [$rangeStart, $rangeEnd]);

        // 1) 注册按天
        $registerByDay = DB::table('users')
            ->selectRaw("
                DATE(created_at) as d,
                SUM(CASE WHEN {$isApp} THEN 1 ELSE 0 END) as app_register,
                SUM(CASE WHEN {$isApp} THEN 0 ELSE 1 END) as h5_register
            ")
            ->whereBetween('created_at', [$rangeStart, $rangeEnd])
            ->groupByRaw('DATE(created_at)')
            ->get()
            ->keyBy(fn ($r) => (string)$r->d);

        // 2) 首存：新用户首次正充值落在注册日
        $firstDepositSub = DB::table('balance_logs')
            ->selectRaw('member_id, MIN(created_at) as first_at')
            ->where('amount', '>', 0)
            ->where(function ($q) {
                $q->whereIn('change_type', ['充值', '人工充值', '三方充值'])
                    ->orWhere(function ($q2) {
                        $q2->where('change_type', 'like', '%充值%')
                            ->where('change_type', 'not like', '%返现%')
                            ->where('change_type', 'not like', '%退%');
                    });
            })
            ->whereIn('member_id', (clone $newUsers)->selectRaw('COALESCE(NULLIF(member_id, \'\'), user_id)'))
            ->groupBy('member_id');

        $firstDepositByDay = DB::query()
            ->fromSub($firstDepositSub, 'fd')
            ->joinSub($newUsers, 'nu', 'nu.member_key', '=', 'fd.member_id')
            ->whereRaw('DATE(fd.first_at) = nu.reg_date')
            ->selectRaw("
                nu.reg_date as d,
                SUM(CASE WHEN nu.is_app = 1 THEN 1 ELSE 0 END) as app_first_deposit,
                SUM(CASE WHEN nu.is_app = 0 THEN 1 ELSE 0 END) as h5_first_deposit
            ")
            ->groupBy('nu.reg_date')
            ->get()
            ->keyBy(fn ($r) => (string)$r->d);

        // 3) 流水：新用户在其注册日的 投注/存款/派奖（每人一行）
        $memberFlow = DB::table('balance_logs as bl')
            ->joinSub($newUsers, 'nu', 'nu.member_key', '=', 'bl.member_id')
            ->whereRaw('DATE(bl.created_at) = nu.reg_date')
            ->whereBetween('bl.created_at', [$rangeStart, $rangeEnd])
            ->selectRaw("
                nu.reg_date as d,
                bl.member_id,
                SUM(CASE
                    WHEN bl.change_type LIKE '%投注%'
                         AND bl.change_type NOT LIKE '%退款%'
                         AND bl.change_type NOT LIKE '%中奖%'
                    THEN ABS(bl.amount) ELSE 0 END) as bet_amt,
                SUM(CASE
                    WHEN bl.amount > 0 AND (
                        bl.change_type IN ('充值','人工充值','三方充值')
                        OR (
                            bl.change_type LIKE '%充值%'
                            AND bl.change_type NOT LIKE '%返现%'
                            AND bl.change_type NOT LIKE '%退%'
                        )
                    ) THEN bl.amount ELSE 0 END) as deposit_amt,
                SUM(CASE
                    WHEN bl.change_type LIKE '%中奖%' THEN bl.amount ELSE 0 END) as win_amt
            ")
            ->groupBy('nu.reg_date', 'bl.member_id')
            ->get();

        $flowByDay = [];
        foreach ($memberFlow as $row) {
            $d = (string)$row->d;
            if (!isset($flowByDay[$d])) {
                $flowByDay[$d] = [
                    'bet_total' => 0.0,
                    'deposit_total' => 0.0,
                    'win_total' => 0.0,
                    'active_member' => 0,
                    'valid_member' => 0,
                ];
            }
            $bet = (float)$row->bet_amt;
            $deposit = (float)$row->deposit_amt;
            $win = (float)$row->win_amt;
            $flowByDay[$d]['bet_total'] += $bet;
            $flowByDay[$d]['deposit_total'] += $deposit;
            $flowByDay[$d]['win_total'] += $win;
            if ($bet >= self::ACTIVE_BET_THRESHOLD) {
                $flowByDay[$d]['active_member']++;
            }
            if ($deposit >= self::VALID_DEPOSIT_THRESHOLD) {
                $flowByDay[$d]['valid_member']++;
            }
        }

        $data = [];
        $cursor = strtotime($startDate);
        $endTs = strtotime($endDate);
        while ($cursor <= $endTs) {
            $d = date('Y-m-d', $cursor);
            $reg = $registerByDay[$d] ?? null;
            $fd = $firstDepositByDay[$d] ?? null;
            $flow = $flowByDay[$d] ?? [
                'bet_total' => 0.0,
                'deposit_total' => 0.0,
                'win_total' => 0.0,
                'active_member' => 0,
                'valid_member' => 0,
            ];

            $betTotal = round((float)$flow['bet_total'], 2);
            $depositTotal = round((float)$flow['deposit_total'], 2);
            $winTotal = round((float)$flow['win_total'], 2);

            $data[] = [
                'date' => $d,
                'h5_register' => (int)($reg->h5_register ?? 0),
                'app_register' => (int)($reg->app_register ?? 0),
                'h5_first_deposit' => (int)($fd->h5_first_deposit ?? 0),
                'app_first_deposit' => (int)($fd->app_first_deposit ?? 0),
                'active_member' => (int)$flow['active_member'],
                'deposit_total' => $depositTotal,
                'bet_total' => $betTotal,
                'company_profit' => round($betTotal - $winTotal, 2),
                'valid_member' => (int)$flow['valid_member'],
            ];
            $cursor = strtotime('+1 day', $cursor);
        }

        return [
            'start_time' => $startDate,
            'end_time' => $endDate,
            'rules' => [
                'active_member' => '新注册会员当日下注>=¥' . self::ACTIVE_BET_THRESHOLD,
                'valid_member' => '新注册会员当日存款>=¥' . self::VALID_DEPOSIT_THRESHOLD,
            ],
            'data' => $data,
        ];
    }
}