mex_api/app/Http/logic/api/RegisterHourlyStatsLogic.php

<?php

namespace App\Http\logic\api;

use Illuminate\Support\Facades\DB;

class RegisterHourlyStatsLogic extends BaseApiLogic
{
    /**
     * 获取按小时分布的注册用户、首充付费人数统计
     * 新增付费人数：在指定日期注册的用户，在注册后24小时内有首充的用户
     * 
     * @param string $date 日期 格式: Y-m-d
     * @param int $channel 渠道ID，默认102
     * @return array|false
     */
    public function getHourlyStats($date, $channel = 102)
    {
        try {
            // 验证日期格式
            if (!$this->validateDate($date)) {
                $this->error = 'Invalid date format. Please use Y-m-d format.';
                return false;
            }

            // 构建日期时间范围（当天00:00:00 到 23:59:59）
            $startDateTime = $date . ' 00:00:00';
            $endDateTime = $date . ' 23:59:59';

            // 查询注册数据 - 按小时分组统计，添加渠道筛选
            $registerStats = DB::connection('account')
                ->table('QPAccountsDB.dbo.AccountsInfo')
                ->select(
                    DB::raw("DATEPART(HOUR, RegisterDate) as hour"),
                    DB::raw("COUNT(*) as register_count")
                )
                ->where('Channel', $channel)
                ->whereBetween('RegisterDate', [$startDateTime, $endDateTime])
                ->groupBy(DB::raw("DATEPART(HOUR, RegisterDate)"))
                ->orderBy('hour')
                ->get();

            // 获取所有注册用户的ID和注册时间
            $registeredUsers = DB::connection('account')
                ->table('QPAccountsDB.dbo.AccountsInfo')
                ->select('UserID', DB::raw("DATEPART(HOUR, RegisterDate) as register_hour"), 'RegisterDate')
                ->where('Channel', $channel)
                ->whereBetween('RegisterDate', [$startDateTime, $endDateTime])
                ->get()
                ->keyBy('UserID');

            // 初始化结果数组（24小时）
            $result = [];
            for ($hour = 0; $hour < 24; $hour++) {
                $result[$hour] = [
                    'hour' => $hour,
                    'hour_display' => sprintf('%02d点', $hour),
                    'register_count' => 0,
                    'first_charge_count' => 0,
                    'charge_rate' => 0,
                ];
            }

            // 填充注册数据
            foreach ($registerStats as $stat) {
                $hour = (int)$stat->hour;
                $result[$hour]['register_count'] = (int)$stat->register_count;
            }

            // 查询首充数据：在注册后24小时内有首充的用户
            if ($registeredUsers->count() > 0) {
                $userIds = $registeredUsers->pluck('UserID')->toArray();
                
                // 查询这些用户在注册后24小时内的首充记录
                $firstChargeUsers = DB::connection('write')
                    ->table('agent.dbo.order as o')
                    ->leftJoin('QPAccountsDB.dbo.AccountsInfo as ai', 'o.user_id', '=', 'ai.UserID')
                    ->select('o.user_id', 'o.finished_at', 'ai.RegisterDate')
                    ->where('o.GiftsID', 301)
                    ->where('o.pay_status', 1)
                    ->whereNotNull('o.finished_at')
                    ->whereIn('o.user_id', $userIds)
                    ->get();

                // 按注册小时统计首充人数（首充时间必须在注册后24小时内）
                $chargeCountByHour = [];
                foreach ($firstChargeUsers as $charge) {
                    $userId = $charge->user_id;
                    if (!isset($registeredUsers[$userId])) {
                        continue;
                    }
                    
                    $registerDate = strtotime($registeredUsers[$userId]->RegisterDate);
                    $chargeDate = strtotime($charge->finished_at);
                    
                    // 检查首充是否在注册后24小时内
                    $hoursDiff = ($chargeDate - $registerDate) / 3600;
                    if ($hoursDiff >= 0 && $hoursDiff <= 24) {
                        $registerHour = (int)$registeredUsers[$userId]->register_hour;
                        if (!isset($chargeCountByHour[$registerHour])) {
                            $chargeCountByHour[$registerHour] = [];
                        }
                        // 使用数组去重，确保每个用户只计算一次
                        if (!in_array($userId, $chargeCountByHour[$registerHour])) {
                            $chargeCountByHour[$registerHour][] = $userId;
                        }
                    }
                }

                // 填充首充数据
                foreach ($chargeCountByHour as $hour => $userIds) {
                    $result[$hour]['first_charge_count'] = count($userIds);
                }
            }

            // 计算付费率
            foreach ($result as &$row) {
                if ($row['register_count'] > 0) {
                    $row['charge_rate'] = round(($row['first_charge_count'] / $row['register_count']) * 100, 2);
                }
            }

            // 添加日期和渠道信息
            foreach ($result as &$row) {
                $row['date'] = $date;
                $row['channel'] = $channel;
            }

            return array_values($result);

        } catch (\Exception $e) {
            $this->error = 'Database query error: ' . $e->getMessage();
            return false;
        }
    }

    /**
     * 验证日期格式
     * 
     * @param string $date
     * @return bool
     */
    private function validateDate($date)
    {
        $d = \DateTime::createFromFormat('Y-m-d', $date);
        return $d && $d->format('Y-m-d') === $date;
    }
}