dms-api/app/v1/logic/gameLog/SdkOrderRankLogic.php

<?php
// +----------------------------------------------------------------------
// | saiadmin [ saiadmin快速开发框架 ]
// +----------------------------------------------------------------------
// | Author: your name
// +----------------------------------------------------------------------
namespace app\v1\logic\gameLog;

use plugin\saiadmin\basic\BaseLogic;
use plugin\saiadmin\exception\ApiException;
use plugin\saiadmin\utils\Helper;
use app\v1\model\gameLog\SdkOrderRank;
use support\think\Db;

/**
 * 充值排行逻辑层
 */
class SdkOrderRankLogic extends BaseLogic
{
    /**
     * 构造函数
     */
    public function __construct()
    {
        $this->model = new SdkOrderRank();
    }

    public function getList($query): mixed
    {
        $saiType = request()->input('saiType', 'list');
        $page = request()->input('page', 1);
        $limit = request()->input('limit', 10);
        $orderBy = request()->input('orderBy', '');
        $orderType = request()->input('orderType', $this->orderType);
        
        if(empty($orderBy)) {
            $orderBy = 'searchTotalMoney';
        }

        // 先分组，再分页
        $query = $query->field('user_name,sum(money) as searchTotalMoney,
            orderid,
            game_id,
            media_id,
            agent_id,
            site_id,
            uid,
            pay_channel_id,
            auth_id,
            server_id,
            server_name,
            pay_date,
            reg_date,
            role_name,
            role_id');


        if ($saiType === 'all') {
            return $query->select()->toArray();
        }
        
     

        
        $query->group('uid, game_id,site_id,server_id,media_id,auth_id,agent_id,server_id,auth_id,role_id');

        $query->order($orderBy, $orderType);
        $data = $query->paginate($limit, false, ['page' => $page])->toArray();


        $data['data'] = $this->trandformListColumn($data['data'], ['game','auth', 'agent', 'pay_channel']);


        // // 告警提示，查询最近登录时间，查询最近充值时间，和现在时间对比，如果超过3天，则告警，并提示

        foreach($data['data'] as &$item){
            // 最后登录时间
           $item['login_time'] = Db::connect('db_origin')->table('user_'.$item['uid']%10)->where('uid', $item['uid'])->value('login_time');
           // 最近注册时间
           $item['pay_time'] = Db::connect('db_origin')->table('user_'.$item['uid']%10)->where('uid', $item['uid'])->value('pay_time');
           // 现在时间
           $item['now_time'] = time();

           // 最近登录时间与现在时间对比，如果超过3天，则告警，并提示
           if($item['login_time'] < $item['now_time'] - 3 * 24 * 60 * 60){
            $item['login_alert'] = 1;
           }else{
            $item['login_alert'] = 0;
           }
           // 最近充值时间与现在时间对比，如果超过3天，则告警，并提示
           if($item['pay_time'] < $item['now_time'] - 3 * 24 * 60 * 60){
            $item['pay_alert'] = 1;
           }else{
            $item['pay_alert'] = 0;
           }
        }



        $where = $query->getOptions();
        $totalWhere = [];
       if(!empty($where['game_id'])){
            $totalWhere[] = ['game_id', 'in', $where['game_id']];
       }
       if(!empty($where['auth_id'])){
            $totalWhere[] = ['auth_id', 'in', $where['auth_id']];
       }
       if(!empty($where['agent_id'])){
            $totalWhere['agent_id'] = $where['agent_id'];
       }
       if(!empty($where['site_id'])){
            $totalWhere['site_id'] = $where['site_id'];
       }
       if(!empty($where['server_id'])){
            $totalWhere['server_id'] = $where['server_id'];
       }
       if(!empty($where['user_name'])){
            $totalWhere['user_name'] = $where['user_name'];
       }
       if(!empty($where['server_name'])){
            $totalWhere['server_name'] = $where['server_name'];
       }
       if(!empty($where['reg_date'])){
            $totalWhere['reg_date'] = $where['reg_date'];
       }

        $totalList = Db::connect('db_game_log')->table('sdk_order_success')
                    ->where($totalWhere)
                    ->field('game_id,uid,sum(money) as totalMoney')
                    ->group('uid,game_id,site_id,server_id,media_id,auth_id,agent_id,server_id,auth_id,role_id')
                    ->select()->toArray();

        
        // 查询累计充值金额（对每一行数据单独查询历史充值金额）
        $totalMoneyMap = [];
        if (!empty($data['data']) && !empty($totalList)) {
            foreach ($totalList as $totalItem) {
                $key = $totalItem['uid'] . '_' . $totalItem['game_id'];
                $totalMoneyMap[$key] = $totalItem['totalMoney'] ?? 0;
            }
        }
        
        // 为搜索结果添加累计充值金额
        foreach($data['data'] as &$item){
            $key = $item['uid'] . '_' . $item['game_id'];
            $item['totalMoney'] = $totalMoneyMap[$key] ?? 0;
        }

        return $data;
    }
}