admin_api/app/Game/LogGamecardClick.php

<?php

namespace App\Game;

use Illuminate\Database\Eloquent\Model;
use Carbon\Carbon;

class LogGamecardClick extends Model
{
    protected $connection = 'mysql'; // 如果使用非默认连接，指定连接名称
    protected $table = 'webgame.log_gamecard_click';
    public $timestamps = false; // 表中没有 Laravel 自动管理的时间戳字段

    protected $fillable = [
        'gameid',
        'UserID',
        'create_at'
    ];

    /**
     * 记录日志
     *
     * @param int $gameid
     * @param int $userId
     * @return LogGamecardClick
     */
    public static function recordClick($gameid, $userId)
    {
        return self::create([
            'gameid' => $gameid,
            'UserID' => $userId,
            'create_at' => Carbon::now()
        ]);
    }

    /**
     * 统计最近一周内根据 gameid 返回条数
     *
     * @return \Illuminate\Support\Collection
     */
    public static function getWeeklyStats()
    {
        $oneWeekAgo = Carbon::now()->subWeek();
        return self::select('gameid', \DB::raw('COUNT(*) as clicks'))
            ->where('create_at', '>=', $oneWeekAgo)
            ->groupBy('gameid')
            ->orderBy('clicks', 'desc')
            ->get();
    }
    public static function getWeeklyStatsByBrand()
    {
        $oneWeekAgo = Carbon::now()->subWeek();

        // 获取最近一周点击数量最多的游戏
        $gameClicks = self::select('gameid', \DB::raw('COUNT(*) as clicks'))
            ->where('create_at', '>=', $oneWeekAgo)
            ->groupBy('gameid')
            ->orderBy('clicks', 'desc')
            ->get();

        // 获取游戏详细信息
        $gameIdsAll = $gameClicks->pluck('gameid')->toArray();
        $games = GameCard::whereIn('id', $gameIdsAll)->where('state','<>',0)->get()->keyBy('id');

        // 按品牌分类，确保每个品牌最多返回16个游戏
        $result = [];

        foreach ($gameClicks as $click) {
            $game = $games->get($click->gameid);
            if ($game) {
                if (!isset($result[$game->brand])) {
                    $result[$game->brand] = [];
                }
                if (count($result[$game->brand]) < 15) {
                    $result[$game->brand][] = $game->id;
                }
            }
        }

        // 将结果格式化为 "brand: gameid, gameid, ..."
        foreach ($result as $brand => $gameIds) {
            $result[$brand] = implode(', ', $gameIds);
        }
        $result['ALL']=$gameIdsAll;//implode(', ', array_slice(,0,16));

        return $result;
    }
}