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\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)
    {
        $dateType = $params['dateType'] ?? 'day';
        $page = max(1, (int) ($params['page'] ?? 1));
        $limit = max(1, (int) ($params['limit'] ?? 10));

        // 时间戳
        $startTimestamp = !empty($params['start_time']) ? strtotime($params['start_time']) : strtotime(date('2000-01-01 00:00:00'));
        $endTimestamp = !empty($params['end_time']) ? strtotime($params['end_time']) : time();
        if ($endTimestamp < $startTimestamp) {
            $endTimestamp = $startTimestamp;
        }

        // 分组
        switch ($dateType) {
            case 'month':
                $groupRaw = "FROM_UNIXTIME(create_time, '%Y-%m')";
                break;
            case 'year':
                $groupRaw = "FROM_UNIXTIME(create_time, '%Y')";
                break;
            case 'week':
                $groupRaw = "CONCAT(YEAR(FROM_UNIXTIME(create_time)),'-',LPAD(WEEK(FROM_UNIXTIME(create_time),1),2,'0'))";
                break;
            case 'day':
            default:
                $groupRaw = "FROM_UNIXTIME(create_time, '%Y-%m-%d')";
                break;
        }

        // 基础查询
        $query = DB::table('member_answer_ranks')
            ->selectRaw("
                {$groupRaw} as stat_date,
                SUM(answer_count) as total_count,
                SUM(answer_time) as total_time
            ")
            ->whereBetween('create_time', [$startTimestamp, $endTimestamp])
            ->where('status', 1)
            ->where('mark', 1)
            ->groupBy('stat_date')
            ->orderBy('stat_date', 'desc');

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

        // 分页
        $list = $query->forPage($page, $limit)->get();

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