bot-28/app/Services/NewUserSummaryService.php

<?php

namespace App\Services;

use Illuminate\Support\Facades\DB;

/**
 * 新用户汇总：区间集合查询，按天输出
 *
 * 口径：
 * - H5/APP：register_domain 是否为包名风格（com.xxx.yyy）
 * - 活跃会员：当日新注册且当日下注 >= 50
 * - 有效会员：当日新注册且当日存款 >= 50
 * - 公司盈利：当日新用户当日 投注额 - 派奖
 *
 * 使用显式表前缀 raw SQL，避免 joinSub 别名前缀 / whereIn 多列 等问题。
 */
class NewUserSummaryService
{
    public const ACTIVE_BET_THRESHOLD = 50;
    public const VALID_DEPOSIT_THRESHOLD = 50;

    /**
     * @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';
        $p = DB::getTablePrefix();
        $users = $p . 'users';
        $logs = $p . 'balance_logs';

        // 包名风格 = APP
        $isApp = "(u.register_domain REGEXP '^(com|org|net|io|cn)\\\\.[a-zA-Z0-9_]+(\\\\.[a-zA-Z0-9_]+)+$')";

        // 1) 注册按天
        $registerRows = DB::select(
            "SELECT
                DATE(u.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
             FROM {$users} u
             WHERE u.created_at BETWEEN ? AND ?
             GROUP BY DATE(u.created_at)",
            [$rangeStart, $rangeEnd]
        );
        $registerByDay = [];
        foreach ($registerRows as $r) {
            $registerByDay[(string)$r->d] = $r;
        }

        // 2) 首存：新用户历史上第一次正充值时间 = 注册日
        $firstDepositRows = DB::select(
            "SELECT
                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
             FROM (
                SELECT
                    DATE(u.created_at) AS reg_date,
                    COALESCE(NULLIF(u.member_id, ''), u.user_id) AS member_key,
                    CASE WHEN {$isApp} THEN 1 ELSE 0 END AS is_app
                FROM {$users} u
                WHERE u.created_at BETWEEN ? AND ?
             ) nu
             INNER JOIN (
                SELECT bl.member_id, MIN(bl.created_at) AS first_at
                FROM {$logs} bl
                WHERE 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 '%退%'
                    )
                  )
                GROUP BY bl.member_id
             ) fd ON fd.member_id COLLATE utf8mb4_general_ci = nu.member_key COLLATE utf8mb4_general_ci
             WHERE DATE(fd.first_at) = nu.reg_date
             GROUP BY nu.reg_date",
            [$rangeStart, $rangeEnd]
        );
        $firstDepositByDay = [];
        foreach ($firstDepositRows as $r) {
            $firstDepositByDay[(string)$r->d] = $r;
        }

        // 3) 流水：新用户在其注册日的投注/存款/派奖（每人每天一行）
        $memberFlow = DB::select(
            "SELECT
                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
             FROM (
                SELECT
                    DATE(u.created_at) AS reg_date,
                    COALESCE(NULLIF(u.member_id, ''), u.user_id) AS member_key
                FROM {$users} u
                WHERE u.created_at BETWEEN ? AND ?
             ) nu
             INNER JOIN {$logs} bl
                ON bl.member_id COLLATE utf8mb4_general_ci = nu.member_key COLLATE utf8mb4_general_ci
             WHERE DATE(bl.created_at) = nu.reg_date
               AND bl.created_at BETWEEN ? AND ?
             GROUP BY nu.reg_date, bl.member_id",
            [$rangeStart, $rangeEnd, $rangeStart, $rangeEnd]
        );

        $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,
        ];
    }
}