new_sdk_wu/www/new_sdk/application/api/controller/JhApi.php

<?php

namespace app\api\controller;

use Exception;
use think\Cache;
use think\Controller;
use think\Db;
use think\Env;
use think\Request;

/**
 * 聚合平台对接
 */
class JhApi extends Controller
{
    protected $api_sign_key;

    public function __construct(Request $request = null)
    {
        parent::__construct($request);
        $this->api_sign_key = Env::get("qmjh.api_sign_key");

        log_message('__construct.input:' . json_encode(input()), 'log', LOG_PATH . 'jh_log/');
    }

    /**
     * sign
     *
     * @param $data
     *
     * @return string
     */
    private function sign($data)
    {
        unset($data['sign']);

        $data['key'] = $this->api_sign_key;
        ksort($data);

        $string = '';
        foreach ($data as $k => $v) {
            if ($string) {
                $string .= '&' . $k . "=" . $v;
            } else {
                $string = $k . "=" . $v;
            }
        }

        return md5($string);
    }

    public function getRole()
    {
        $data = input();
        $type = input('type', 0); // 类型:0=总数;1=列表
        $params = input('params');

        if (!isset($type) || !isset($params)) {
            return json(["code" => -100, "msg" => '缺少必要参数！', "data" => []]);
        }
        $signRes = $this->sign($data);
        // dump($signRes);
        if ($signRes != $data['sign']) {
            return json(["code" => -110, "msg" => '签名有误！', "data" => []]);
        }
        if (!is_json($params)) {
            return json(["code" => -110, "msg" => '查询数据格式有误！', "data" => []]);
        }

        $listBy = json_decode($params, true);
        if(count($listBy) > 2000){
            return json(["code" => -120, "msg" => '查询数据条数限制超 2000条', "data" => []]);
        }

        try {

            // 数据标识处理
            $markByList = [];
            array_walk($listBy, function ($v) use (&$markByList) {
                // $markByList[$v['id']] = $v['aid'] . '_' . $v['gid'].'_'.$v['id'];
                $markByList[$v['id']] = $v['aid'] . '_' . $v['gid'];
            });
            $markByFlip = array_flip($markByList);

            $modelHandle = Db::table('nw_member_game_server');
            $modelHandle->where(function ($query) use ($listBy) {
                foreach ($listBy as $v) {
                    $query->whereOr(function ($query) use ($v) {
                        $query->where(['member_id' => $v['aid'], 'game_id' => $v['gid']]);
                    });
                }
            });

            $result = [];
            if ($type == 1) {
                $list = $modelHandle->field("mgs_id,roleid,rolename,serverid,servername,FROM_UNIXTIME(create_time, '%Y-%m-%d %H:%i:%s') as create_time,CONCAT(`member_id`,'_',`game_id`) as mark")
                    ->chunk(500, function($items) use ($markByFlip, &$result) {
                        foreach ($items as $item) {
                            if (empty($markByFlip[$item['mark']])) {
                                break;
                            }
                            $key = $markByFlip[$item['mark']];
                            $result[$key][] = ['role_id' => $item['roleid'], 'rolename' => $item['rolename'], 'server_id' => $item['serverid'], 'server_name' => $item['servername'], 'create_time' => $item['create_time']];
                        }
                    });
            } else {
                $list = $modelHandle->field("CONCAT(`member_id`,'_',`game_id`) as mark, count(*) as count")->group('mark')->select();
                foreach ($list as $v) {
                    if (empty($markByFlip[$v['mark']])) {
                        break;
                    }
                    $key = $markByFlip[$v['mark']];
                    $result[$key][] = $v['count'];
                }
            }

            if (!$list) {
                return json(["code" => 200, "msg" => '没有相关数据！', "data" => []]);
            }
        } catch (\Exception $e) {
            // dump($e->getMessage(), $e->getFile() . ' : ' . $e->getLine());
            return json(["code" => -120, "msg" => '程序异常：' . $e->getMessage() . ' - ' . $e->getFile() . ' : ' . $e->getLine(), "data" => []]);
        }
        return json(["code" => 200, "msg" => '成功', "data" => $result]);
    }

    /**
     * 获取游戏账户列表
     *
     * @return \think\response\Json
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function getSubList()
    {
        $input = input();
        $signRes = $this->sign($input);
        if ($signRes != $input['sign']) {
            return json(["code" => -110, "msg" => '签名有误！', "data" => []]);
        }
        $date = $input['date'] ?? '';
        if ($date) {
            $where['cl.login_time'] = ['between', [strtotime($date . ' 00:00:00'), strtotime($date . ' 23:59:59')]];
        } else {
            // 三个月时间
            $where['cl.login_time'] = ['between', [strtotime('-3 months'), strtotime($date . ' 23:59:59')]];
        }
        if (!empty($input['game_id'])) {
            $where['cl.gameid'] = ['in', explode(',', $input['game_id'])];
        }

        $list = Db::table('cy_logininfo')->alias('cl')
            ->join('cy_members cm', 'cl.userid=cm.id', 'left')
            ->join('cy_game cg', 'cl.gameid=cg.id', 'left')
            ->join('nw_channel nc', 'cl.channel_id=nc.id', 'left')
            ->where(['nc.gh_status' => 1])->where($where)
            ->field('cm.username as user_name,cl.gameid as game_id,max(cl.login_time) as update_time')
            ->group('cl.gameid,cl.sub_id')
            ->page($input['page'] ?? 1, $input['limit'] ?? 100)
            ->select();
        $total = Db::table('cy_logininfo')->alias('cl')
            ->join('cy_members cm', 'cl.userid=cm.id', 'left')
            ->join('cy_game cg', 'cl.gameid=cg.id', 'left')
            ->join('nw_channel nc', 'cl.channel_id=nc.id', 'left')
            ->field('cm.username as user_name,cl.gameid as game_id,max(cl.login_time) as update_time')
            ->where(['nc.gh_status' => 1])->where($where)
            ->group('cl.gameid,cl.sub_id')
            ->count();

        return json(["code" => 200, "msg" => 'success', "data" => $list, 'count' => $total]);
    }

    /**
     * 获取渠道数据汇总
     *
     * @return \think\response\Json
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function getChannelDataSummary()
    {
        $input = input();
        $signRes = $this->sign($input);
        if ($signRes != $input['sign']) {
            return json(["code" => -110, "msg" => '签名有误！', "data" => []]);
        }
        $date = $input['date'] ?? '';
        if (!$date) {
            return json(["code" => -120, "msg" => '参数错误，缺少date参数！', "data" => []]);
        }

        $where = [
            'crg.day' => $date,
            'crg.type_id' => 2,
            'crg.channel_id' => ['>', 0],
        ];
        $list = Db::table('cy_retaine_game')->alias('crg')
            ->join('cy_game cg', 'cg.id=crg.game_id AND cg.cooperation_status=1', 'left')
            ->join('nw_channel nc', 'nc.id=crg.channel_id AND nc.gh_status=1', 'left')
            ->field("crg.type_id, crg.game_id, cg.name as game_name, crg.channel_id, nc.name as channel_name, crg.reg_num, crg.role_num, crg.act_num, crg.recharge_num, crg.pay_num")
            ->where($where)
            ->where(function ($query) {
                // 非空条件：至少有一个字段不为空
                $query->whereOr('crg.reg_num', '>', 0)
                    ->whereOr('crg.role_num', '>', 0)
                    ->whereOr('crg.act_num', '>', 0)
                    ->whereOr('crg.recharge_num', '>', 0)
                    ->whereOr('crg.pay_num', '>', 0);
            })
            ->order("crg.day desc, crg.reg_num desc, crg.game_id desc")
            ->select();

        return json(["code" => 200, "msg" => 'success', "data" => $list]);
    }
    public function getChannelDataSummaryV2()
    {
        $input = input();
        $signRes = $this->sign($input);
        if (empty($input['sign']) || $signRes != $input['sign']) {
            return json(["code" => -110, "msg" => '签名有误！', "data" => []]);
        }
        if (empty($input['date'])) {
            return json(["code" => -120, "msg" => '参数错误，缺少date参数！', "data" => []]);
        }
        $type = input('type', 1); // 类型: 1=汇总, 2=详情

        $where = [
            'crg.day' => $input['date'],
            'crg.type_id' => 2,
            'crg.game_id' => ['>', 0],
            'crg.channel_id' => ['>', 0],
        ];

        // , crg.old_act_num, crg.old_pay_num, crg.old_recharge_num
        $filed = "crg.id, crg.day, crg.game_id, cg.name as game_name, crg.channel_id, nc.name as channel_name, crg.reg_num, crg.role_num, crg.act_num, crg.recharge_num, crg.pay_num, crg.new_act_num, crg.new_pay_num, crg.new_recharge_num";
        if ($type == 2) {
            $filed = "crg.id, crg.day, crg.game_id, cg.name as game_name, crg.channel_id, nc.name as channel_name, crg.reg_total, crg.reg_num, crg.one_stay, crg.three_stay, crg.four_stay, crg.five_stay, crg.six_stay, crg.seven_stay, crg.fifteen_stay, crg.thirty_stay";
        }

        $listRows = intval(input('list_rows', 50));
        $pageNum  = intval(input('page', 1));

        $retaineList = Db::table('cy_retaine_game')->alias('crg')
            ->join('cy_game cg', 'cg.id=crg.game_id AND cg.cooperation_status=1', 'left')
            ->join('nw_channel nc', 'nc.id=crg.channel_id AND nc.gh_status=1')
            ->field($filed)
            ->where($where)
            ->where(function ($query) use ($type) {
                // 非空条件：至少有一个字段不为空
                if ($type == 1) {
                    $query->whereOr('crg.reg_num', '>', 0)
                        ->whereOr('crg.role_num', '>', 0)
                        ->whereOr('crg.act_num', '>', 0)
                        ->whereOr('crg.recharge_num', '>', 0)
                        ->whereOr('crg.pay_num', '>', 0)
                        ->whereOr('crg.new_act_num', '>', 0)
                        ->whereOr('crg.new_pay_num', '>', 0)
                        ->whereOr('crg.new_recharge_num', '>', 0);
                } else {
                    $query->whereOr('crg.reg_num', '>', 0)
                        ->whereOr('crg.one_stay', '>', 0)
                        ->whereOr('crg.three_stay', '>', 0)
                        ->whereOr('crg.four_stay', '>', 0)
                        ->whereOr('crg.five_stay', '>', 0)
                        ->whereOr('crg.six_stay', '>', 0)
                        ->whereOr('crg.seven_stay', '>', 0)
                        ->whereOr('crg.fifteen_stay', '>', 0)
                        ->whereOr('crg.thirty_stay', '>', 0);
                }
            })
            ->order("crg.id asc")
            ->paginate($listRows, false, ['page' => $pageNum]);
        $pageArr = $retaineList->toArray(); // 包含 data, total, per_page, current_page, last_page 等
        if ($type == 1) {
            $list = $pageArr['data'] ?? [];
            foreach ($list as &$v) {
                $old_recharge_num = $v['recharge_num'] - $v['new_recharge_num'];
                if ($old_recharge_num <= 0) {
                    $old_recharge_num = "0.00";
                }
                $v['old_act_num'] = $v['old_pay_num'] = $v['old_recharge_num'] = 0;
                if($v['act_num'] > 0 && ($v['act_num'] - $v['new_act_num']) > 0){
                    $v['old_act_num'] = $v['act_num'] - $v['new_act_num'];
                }
                if($v['pay_num'] > 0 && ($v['pay_num'] - $v['new_pay_num']) > 0){
                    $v['old_pay_num'] = $v['pay_num'] - $v['new_pay_num'];
                }
                $v['old_recharge_num'] = sprintf("%.2f", $old_recharge_num);
            }
            $pageArr['data'] = $list;
        }

        return json(["code" => 200, "msg" => 'success', "data" => $pageArr]);
    }

    // 获取注册归属数据列表
    public function getSubUserList()
    {
        $input = input();

        // 请求限流：一分钟三次（使用ThinkPHP5的Cache方式）
        $limitKey = 'jh_api_getSubUserList_limit:' . md5(json_encode($input));
        try {
            $redis = Cache::store('redis');
            $requestCount = $redis->get($limitKey);
            if ($requestCount === false || $requestCount === null) {
                // 首次请求，设置为1并设置过期时间
                $redis->set($limitKey, 1, 60);
                $requestCount = 1;
            } else {
                // 增加计数
                $requestCount = $redis->inc($limitKey);
            }

            if ($requestCount > 3) {
                return json(["code" => -130, "msg" => '请求过于频繁，请稍后再试（每分钟限制3次）！', "data" => []]);
            }
        } catch (\Exception $e) {
            // log_message('Redis连接失败: ' . $e->getMessage(), 'error', LOG_PATH . 'jh_log/');
            return json(["code" => -130, "msg" => '当前脚本程序执行异常，请联系开发！', "data" => []]);
        }

        $signRes = $this->sign($input);
        // dump($signRes);
        if (empty($input['sign']) || $signRes != $input['sign']) {
            return json(["code" => -110, "msg" => '签名有误！', "data" => []]);
        }
        if (empty($input['date'])) {
            return json(["code" => -120, "msg" => '参数错误，缺少date参数！', "data" => []]);
        }

        $where = [
            'nc.gh_status' => 1
        ];
        if (!empty($input['date']) && empty($input['uid'])) {
            $where['ns.create_time'] = ['between', [strtotime($input['date'] . ' 00:00:00'), strtotime($input['date'] . ' 23:59:59')]];
        }
        if (!empty($input['uid'])) {
            $where['ns.id'] = ['>', $input['uid']];
        }
        $list = Db::table('nw_subaccount')->alias('ns')
            ->join('cy_members cm', 'ns.member_id = cm.id', 'left')
            ->join('cy_game cg', 'ns.game_id = cg.id', 'left')
            ->join('nw_channel nc', 'ns.channel_id = nc.id', 'left')  // 推广员 (level 3)
            ->join('nw_channel p1', 'nc.parent_id = p1.id', 'left')  // 子会长
            ->join('nw_channel p2', 'p1.parent_id = p2.id', 'left')  // 会长
            ->where($where)
            ->order('ns.id asc')
            ->field([
                'ns.id', // 子账户ID
                'ns.member_id as mid', // 账户ID - 2
                'cm.username',                                      // 账号 - 3
                'cm.nickname',                                      // 昵称 - 4

                'cg.id as game_id',                                 // 游戏ID - 5
                'cg.name as game_name',                             // 游戏名 - 6

                'nc.id as channel_id',                              // 推广员ID - 10
                'nc.name as channel_name',                          // 推广员名称

                'p1.id as business_id',                             // 子会长ID - 11
                'p1.name as business_name',                         // 子会长名称

                'p2.id as sub_president_id',                        // 会长ID - 12
                'p2.name as sub_president_name',                    // 会长名称

                'FROM_UNIXTIME(ns.create_time, "%Y-%m-%d %H:%i:%s") as reg_time',                          // 注册时间 - 14
                'FROM_UNIXTIME(ns.update_time, "%Y-%m-%d %H:%i:%s") as login_time',                     // 登录时间 - 15
            ])
            ->paginate(null, false, ['page' => $input['page'] ?? 1, 'list_rows' => $input['limit'] ?? 100]);
        return json(["code" => 200, "msg" => 'success', "data" => $list ?? []]);
    }
    // 获取订单数据列表
    public function getPayList()
    {
        $input = input();

        $signRes = $this->sign($input);
        // dump($signRes);
        if (empty($input['sign']) || $signRes != $input['sign']) {
            return json(["code" => -110, "msg" => '签名有误！', "data" => []]);
        }
        if (empty($input['time_type'])) {
            return json(["code" => -111, "msg" => '参数错误，缺少 time_type 参数！', "data" => []]);
        }
        if (empty($input['start_time'])) {
            return json(["code" => -112, "msg" => '参数错误，缺少 start_time 参数！', "data" => []]);
        }
        if (empty($input['end_time'])) {
            return json(["code" => -113, "msg" => '参数错误，缺少 end_time 参数！', "data" => []]);
        }
        if(empty($input['limit']) && intval($input['limit']) > 1000){
            return json(["code" => -114, "msg" => '参数错误，limit参数不能大于1000！', "data" => []]);
        }

        // // 请求限流：一分钟三次（使用ThinkPHP5的Cache方式）
        // $limitKey = 'jh_api_getPayList_limit:' . md5(json_encode($input));
        // try {
        //     $redis = Cache::store('redis')->handler();
        //     $requestCount = $redis->incr($limitKey);  // 原子递增
        //     if ($requestCount === 1) {
        //         $redis->expire($limitKey, 60);
        //     }
        //     if ($requestCount > 10) {
        //         return json(["code" => -130, "msg" => '请求过于频繁！', "data" => []]);
        //     }
        // } catch (\Exception $e) {
        //     return json(["code" => -130, "msg" => '系统异常！', "data" => []]);
        // }

        // 处理日期：支持 '2026-02-13' 或 '2026-02-13 13:55:55' 两种格式
        $startTime = strtotime($input['start_time']);
        $endTime = strtotime($input['end_time']);

        $timeField = 'cp.create_time';
        if($input['time_type'] == 2){
            // 按照支付时间查询
            $timeField = 'cp.pay_time';
        }
        $where = [
            'cp.status' => 1,
            'nc.gh_status' => 1,
            $timeField => ['between', [$startTime, $endTime]],
        ];

        $payTypeList = config('paytype')??[];
        if(!$payTypeList){
            return json(["code" => -115, "msg" => '缺少支付配置，请联系开发处理！', "data" => []]);
        }

        // 查询测试账户ID列表（约10个，变化不频繁）
        $testMemberIds = Db::table('cy_members')
            ->where('test_status', 1)
            ->column('id');

        // 如果有测试账户，加入查询条件
        if (!empty($testMemberIds)) {
            $where['cp.userid'] = ['not in', $testMemberIds];
        }

        // 查询内容=账号/昵称/游戏ID/游戏名/推广员/子会长/公会/注册时间/登录时间/支付方式/支付金额/代金券金额/订单号/充值金额/发货状态/角色ID/角色名/区服名
        $list = Db::table('cy_pay')->alias('cp')
            ->join('nw_subaccount ns', 'cp.userid = ns.member_id AND cp.gameid=ns.game_id', 'left')
            ->join('cy_game cg', 'cp.gameid = cg.id', 'left')
            ->join('nw_channel nc', 'cp.channel_id = nc.id', 'left')  // 推广员 (level 3)
            // ->join('nw_channel p1', 'nc.parent_id = p1.id', 'left')  // 子会长
            // ->join('nw_channel p2', 'p1.parent_id = p2.id', 'left')  // 会长
            ->where($where)
            ->field([
                'cp.id',
                'cp.orderid as order_id',                          // 订单号 - 0
                'cp.username as account',                          // 账号 - 2
                // 'cm.nickname',                                     // 昵称

                'cp.gameid as game_id',                            // 游戏ID - 1
                'cg.name as game_name',                            // 游戏名

                'nc.id as channel_id',                             // 推广员ID - 13
                'nc.name as channel_name',                         // 推广员名称
                // 'p1.id as business_id',                            // 子会长ID
                // 'p1.name as business_name',                        // 子会长名称
                // 'p2.id as sub_president_id',                      // 会长ID
                // 'p2.name as sub_president_name',                  // 会长名称

                // 'ns.create_time reg_time',                         // 注册时间
                // 'ns.update_time as login_time',                    // 登录时间

                'cp.paytype as pay_method',                        // 支付方式 - 10
                // 'cp.real_amount as pay_amount',                    // 支付金额（人民币部分） - 9 - 删掉
                'cp.real_amount',                                   // 第三方支付金额（可能为空，因为有平台币支付）
                'cp.pay_amount',                                    // 实付金额(不会有空，订单支付价格，处理过折扣、代金卷)

                // ## 代金卷 ##
                'cp.coupon_member_id',                                // 代金券ID
                'cp.coupon_amount',                                // 代金券金额
                // ## 折扣 ##
                'cp.discount',                                // 游戏折扣

                'cp.amount as recharge_amount',                    // 充值金额（订单总金额） - 5
                'cp.real_coin as real_ptb',                          // 平台币 - 6
                // 'cp.status as delivery_status',                    // 发货状态：0=未支付、1=已支付发货
                'cp.roleid as role_id',                            // 角色ID - 21
                'cp.rolename as role_name',                        // 角色名 - 17
                'cp.servername as server_name',                    // 区服名 - 19
                'FROM_UNIXTIME(cp.create_time, "%Y-%m-%d %H:%i:%s") as create_time', // 下单时间
                'FROM_UNIXTIME(cp.pay_time, "%Y-%m-%d %H:%i:%s") as pay_time', // 支付时间 - 26
                'paytype', // 支付方式
                'client_type', // 客户端类型（1=祈盟sdk, 2=巨量, 3=快手磁力包）
            ])
            ->order('cp.id desc')
            ->paginate(null, false, ['page' => intval($input['page'] ?? 1), 'list_rows' => intval($input['limit'] ?? 100)])
            ->each(function($item, $key) use ($payTypeList){
                $item['pay_method'] = $payTypeList[$item['pay_method']] ?? $item['pay_method'];

                // ## 订单类型: 1=普通单、2=平台币、3=代金卷、4=折扣
                $pay_type = 1;
                if($item['real_ptb'] > 0){
                    $pay_type = 2;
                } elseif ($item['coupon_member_id'] > 0 && $item['coupon_amount'] > 0){
                    $pay_type = 3;
                } elseif ($item['discount'] > 0){
                    $pay_type = 4;
                }

                $item['pay_form'] = $pay_type;
                $item['real_amount'] = $item['real_amount'];
                return $item;
            });

        // 统计当天的订单总数和订单总金额
        $total_amount = Db::table('cy_pay')->alias('cp')
            ->join('nw_channel nc', 'cp.channel_id = nc.id AND nc.gh_status=1')
            ->where($where)
            ->sum('cp.pay_amount');

        $total_amount = sprintf("%.2f", $total_amount ?? 0.00);
        $resultList = $list->toArray();

        $resultList['total_amount'] = (float)$total_amount; // 订单总金额

        return json(["code" => 200, "msg" => 'success', "data" => $resultList ?? []]);
    }

    /**
     * 获取玩家活跃数据列表。
     *
     * active_start_hour、active_end_hour 均按小时传入，结束小时包含在查询范围内。
     * 单次最多查询 24 个小时，并过滤测试账号和非聚合渠道。
     */
    public function getPlayerActive()
    {
        $input = input();

        $signRes = $this->sign($input);
        if (empty($input['sign']) || $signRes != $input['sign']) {
            return json(["code" => -110, "msg" => '签名有误！', "data" => []]);
        }

        $activeStartHour = trim($input['active_start_hour'] ?? '');
        $activeEndHour = trim($input['active_end_hour'] ?? '');
        if ($activeStartHour === '') {
            return json(["code" => -120, "msg" => '参数错误，缺少 active_start_hour 参数！', "data" => []]);
        }
        if ($activeEndHour === '') {
            return json(["code" => -120, "msg" => '参数错误，缺少 active_end_hour 参数！', "data" => []]);
        }

        // 支持 YYYY-MM-DD HH 和 YYYY-MM-DD HH:00:00 两种整点格式。
        if (preg_match('/^\d{4}-\d{2}-\d{2} \d{2}$/', $activeStartHour)) {
            $activeStartHour .= ':00:00';
        }
        if (preg_match('/^\d{4}-\d{2}-\d{2} \d{2}$/', $activeEndHour)) {
            $activeEndHour .= ':00:00';
        }

        $hourPattern = '/^\d{4}-\d{2}-\d{2} \d{2}:00:00$/';
        $startTimestamp = preg_match($hourPattern, $activeStartHour) ? strtotime($activeStartHour) : false;
        $endTimestamp = preg_match($hourPattern, $activeEndHour) ? strtotime($activeEndHour) : false;
        if (
            $startTimestamp === false
            || date('Y-m-d H:00:00', $startTimestamp) !== $activeStartHour
            || $endTimestamp === false
            || date('Y-m-d H:00:00', $endTimestamp) !== $activeEndHour
        ) {
            return json(["code" => -120, "msg" => '参数错误，活跃时间格式应为 YYYY-MM-DD HH 或 YYYY-MM-DD HH:00:00！', "data" => []]);
        }
        if ($startTimestamp > $endTimestamp) {
            return json(["code" => -120, "msg" => '参数错误，active_start_hour 不能晚于 active_end_hour！', "data" => []]);
        }

        // 结束小时包含在查询范围内，所以 SQL 上界取结束小时的下一整点（不包含）。
        $endExclusiveTimestamp = $endTimestamp + 3600;
        if (($endExclusiveTimestamp - $startTimestamp) > 24 * 3600) {
            return json(["code" => -120, "msg" => '参数错误，单次活跃时间范围不能超过 24 小时！', "data" => []]);
        }

        $page = intval($input['page'] ?? 1);
        $limit = intval($input['limit'] ?? 100);
        if ($page < 1) {
            return json(["code" => -120, "msg" => '参数错误，page 必须大于等于 1！', "data" => []]);
        }
        if ($limit < 1 || $limit > 1000) {
            return json(["code" => -120, "msg" => '参数错误，limit 必须在 1 到 1000 之间！', "data" => []]);
        }

        $gameId = intval($input['game_id'] ?? 0);
        $channelId = intval($input['channel_id'] ?? 0);
        if ($gameId < 0 || $channelId < 0) {
            return json(["code" => -120, "msg" => '参数错误，game_id 和 channel_id 不能为负数！', "data" => []]);
        }

        $where = [
            'a.active_hour' => [
                ['>=', date('Y-m-d H:00:00', $startTimestamp)],
                ['<', date('Y-m-d H:00:00', $endExclusiveTimestamp)],
            ],
        ];
        if ($gameId > 0) {
            $where['a.game_id'] = $gameId;
        }
        if ($channelId > 0) {
            $where['a.channel_id'] = $channelId;
        }

        try {
            $list = Db::table('cy_player_active_log')->alias('a')
                ->join('cy_members m', 'm.id=a.member_id AND m.test_status=0')
                ->join('nw_channel channel', 'channel.id=a.channel_id AND channel.gh_status=1')
                // ->join('nw_channel channel', 'channel.id=a.channel_id')
                ->join('nw_game_server game_server', 'game_server.id=a.server_id AND game_server.game_id=a.game_id', 'left')
                ->field([
                    'a.id',
                    'a.member_id',
                    'a.subaccount_id',
                    'm.username',
                    'a.game_id',
                    'a.channel_id',
                    'channel.name as channel_name',
                    'a.server_id as server_pk',
                    'COALESCE(game_server.serverid, a.server_id) as server_id',
                    'COALESCE(game_server.servername, "") as server_name',
                    'a.role_id',
                    'a.role_name',
                    'a.role_level',
                    'a.active_hour',
                    'a.created_at',
                ])
                ->where($where)
                ->order('a.active_hour asc,a.id asc')
                ->paginate($limit, false, ['page' => $page]);

            return json(["code" => 200, "msg" => 'success', "data" => $list->toArray()]);
        } catch (\Exception $e) {
            log_message('getPlayerActive exception: ' . $e->getMessage(), 'error', LOG_PATH . 'jh_log/');
            return json(["code" => -500, "msg" => '系统异常，请联系开发处理！', "data" => []]);
        }
    }
}