NN2025081602/app/Services/Common/AnswerRanksService.php

<?php
// +----------------------------------------------------------------------
// | LARAVEL8.0 框架 [ LARAVEL ][ RXThinkCMF ]
// +----------------------------------------------------------------------
// | 版权所有 2017~2021 LARAVEL研发中心
// +----------------------------------------------------------------------
// | 官方网站: http://www.laravel.cn
// +----------------------------------------------------------------------
// | Author: laravel开发员 <laravel.qq.com>
// +----------------------------------------------------------------------

namespace App\Services\Common;

use App\Models\UserModel;
use App\Models\ActionLogModel;
use App\Services\BaseService;
use App\Services\ConfigService;
use Illuminate\Support\Facades\DB;

/**
 * 用户管理-服务类
 * @author laravel开发员
 * @since 2020/11/11
 * Class UserService
 * @package App\Services\Common
 */
class AnswerRanksService extends BaseService
{
    /**
     * 构造函数
     * @author laravel开发员
     * @since 2020/11/11
     * UserService constructor.
     */
    public function __construct()
    {
    }

    /**
     * 静态入口
     */
    public static function make()
    {
        if (!self::$instance) {
            self::$instance = new static();
        }
        return self::$instance;
    }


    public function getAnswerRanks(array $params)
    {
        $page = max(1, (int) ($params['page'] ?? 1));
        $limit = min(100, max(1, (int) ($params['limit'] ?? 20))); // 限制最大100条

        // 时间筛选（可选）
        $startTimestamp = !empty($params['start_time']) ? strtotime($params['start_time']) : null;
        $endTimestamp = !empty($params['end_time']) ? strtotime($params['end_time']) : null;

        // 按用户分组查询答题排行榜
        $query = DB::table('member_answer_ranks')
            ->leftJoin('member', 'member_answer_ranks.user_id', '=', 'member.id')
            ->selectRaw("
                lev_member_answer_ranks.user_id,
                lev_member.nickname,
                lev_member.mobile,
                lev_member.avatar,
                lev_member.realname,
                lev_member.create_time as register_time,
                SUM(lev_member_answer_ranks.answer_count) as total_count,
                SUM(lev_member_answer_ranks.answer_time) as total_time,
                COUNT(lev_member_answer_ranks.id) as answer_days,
                AVG(lev_member_answer_ranks.answer_count) as avg_daily_count,
                MAX(lev_member_answer_ranks.create_time) as last_answer_time
            ")
            ->where('member_answer_ranks.status', 1)
            ->where('member_answer_ranks.mark', 1);
            // ->whereNotNull('member_answer_ranks.user_id')
            // ->where('member.status', 1)
            // ->where('member.mark', 1);

        // 时间筛选
        if ($startTimestamp && $endTimestamp) {
            $query->whereBetween('member_answer_ranks.create_time', [$startTimestamp, $endTimestamp]);
        }

        $query->groupBy('member_answer_ranks.user_id', 'member.nickname', 'member.mobile', 'member.avatar', 'member.realname', 'member.create_time')
            ->orderBy('total_count', 'desc')
            ->orderBy('total_time', 'asc'); // 答题数量相同时，用时少的排前面

        // // 打印 SQL 查询语句用于调试
        // $sql = $query->toSql();
        // $bindings = $query->getBindings();
        // $fullSql = vsprintf(str_replace('?', '%s', $sql), $bindings);
        
        // dd([
        //     'sql' => $sql,
        //     'bindings' => $bindings,
        //     'full_sql' => $fullSql,
        //     'base_count' => DB::table('member_answer_ranks')->where('status', 1)->where('mark', 1)->whereNotNull('user_id')->count(),
        //     'member_count' => DB::table('member')->where('status', 1)->where('mark', 1)->count()
        // ]);

        // 总条数
        $total = DB::table(DB::raw("({$query->toSql()}) as t"))
            ->mergeBindings($query)
            ->count();

        // 分页查询，限制前100名
        $list = $query->forPage($page, $limit)->get();
        
        // 打印查询结果
        \Log::info('Query Results:', [
            'total' => $total,
            'list_count' => count($list),
            'first_item' => $list->first()
        ]);

        // 添加排名和格式化数据
        $rank = ($page - 1) * $limit + 1;
        foreach ($list as $item) {
            $item->rank = $rank++;
            
            // 处理头像URL
            if ($item->avatar) {
                $item->avatar = get_image_url($item->avatar);
            } else {
                $item->avatar = '/static/images/default-avatar.png';
            }
            
            // 格式化注册时间
            $item->register_time_text = date('Y-m-d', $item->register_time);
            
            // 格式化最后答题时间
            $item->last_answer_time_text = date('Y-m-d H:i', $item->last_answer_time);
            
            // 计算平均每日答题数（保留1位小数）
            $item->avg_daily_count = round($item->avg_daily_count, 1);
            
            // 计算答题效率（题/分钟）
            if ($item->total_time > 0) {
                $item->efficiency = round(($item->total_count * 60) / $item->total_time, 2);
            } else {
                $item->efficiency = 0;
            }
        }

        return [
            'list' => $list,
            'total' => $total,
            'page' => $page,
            'limit' => $limit,
        ];
    }

    /**
     * 删除七天之前标记软删除的数据
     */
    public function delete()
    {
        // 设置日志标题
        ActionLogModel::setRecord(session('userId'), ['type' => 1, 'title' => "删除答题排行信息", 'content' => json_encode(request()->post(), 256), 'module' => 'admin']);
        ActionLogModel::record();
        $this->model->where('mark', 0)->where('update_time', '<=', time() - 7 * 86400)->delete();
        return parent::delete();
    }
}