new_sdk_wu/www/new_sdk/application/admin/controller/ComplexMember.php

<?php
/**
 * 聚合玩家用户管理控制器
 */

namespace app\admin\controller;

use app\common\library\MakeReportGo;
use app\common\model\ComplexMembers as MembersModel;
use app\common\model\ComplexChannelModel;
use app\common\model\ComplexPay;
use app\common\model\CompanyGameBind;
use GuzzleHttp\Exception\RequestException;
use think\Db;
use app\common\library\MakeReport;
use GuzzleHttp\Client;

class ComplexMember extends Admin
{
    protected $membersModel;
    protected $where;
    protected $start_time;
    protected $end_time;
    protected $login_start_time;
    protected $login_end_time;
    protected $pay_start_time;
    protected $pay_end_time;

    /**
     * 不进行父类的登录验证，所以增加构造方法重写了父类的初始化方法
     */
    protected function _initialize()
    {
        parent::_initialize();

        $this->membersModel = new MembersModel;
        $this->where        = [];
    }

    public function getWhere()
    {
        $where          = [];
        $start_time     = input('request.start_time');
        $end_time       = input('request.end_time');
        $channel_id     = input('request.channel_id');
        $gameid         = input('request.gameid');
        $username       = input('request.username');
		$complex_username = input('request.complex_username');


        //开始时间和结束时间不为空时
        if ($start_time != '' && $end_time != '') {
            $where['reg_time'] = [
                ['>=', strtotime($start_time)],
                ['<=', strtotime($end_time . ' 23:59:59')],
            ];
        } //开始时间不为空时
        elseif ($start_time != '') {
            $where['reg_time'] = ['>=', strtotime($start_time)];
        } //结束时间不为空时
        elseif ($end_time != '') {
            $where['reg_time'] = ['<=', strtotime($end_time . ' 23:59:59')];
        } else {
			/*
            $start_time = $end_time = date('Y-m-d', time());

            $where['reg_time'] = [
                ['>=', strtotime($start_time)],
                ['<=', strtotime($end_time . ' 23:59:59')],
            ];
			*/
        }

        //渠道
        if ($channel_id != '') {
            $where['channel_id'] = $channel_id;
        }

        //游戏
        if ($gameid != '') {
            $where['gameid'] = $gameid;
        }

        //用户名
        if ($username != '') {
            $where['m.mg_username'] = $username;
        }

        //聚合渠道ID/用户名
        if ($complex_username != '') {
            $where['m.complex_username'] = $complex_username;
        }

        // 游戏上线状态
        $cooperation_status = $this->request->param('cooperation_status', 0, 'intval'); // 游戏上线状态
        if(!empty($cooperation_status)){
            $game_cooperation_status = $cooperation_status==1?1:2;
            $gameIds = Model('Game')->getNormalByIds($game_cooperation_status);
            $where['m.gameid'] = ['in', $gameIds];
        }

        $this->start_time = $start_time;
        $this->end_time   = $end_time;
        $this->where      = $where;
    }

    /**
     * 注册用户列表
     */
    public function index()
    {
        $this->getWhere();
        $param = input('get.');

        $list  = $this->membersModel->table('nw_complex_members')->alias('m')
                    ->join('nw_complex_channel c', 'm.channel_id=c.id', 'left')
                    ->field('m.id,m.mg_username,m.complex_username,m.gameid,m.channel_id,m.total_pay_amount,m.reg_time,m.login_time,c.name channel_name')
                    ->where($this->where)->order('m.reg_time desc')
                    ->paginate(25, false, array('query' => $param));
        $data = $list->toArray()['data'];

        if ( ! empty($data) ) {
            foreach ($data as $v) {
                 $gameIdArr[] = $v['gameid'];
             }

             $gameNameArr = model('Common/Game')->whereIn('id', $gameIdArr)->column('name', 'id');
             foreach ($data as $key => $item) {
                 $data[ $key ]['game_name'] = isset($gameNameArr[ $item['gameid'] ]) ? $gameNameArr[ $item['gameid'] ] : '--';
             }
        }

        $channel_list = (new ComplexChannelModel())->getAllByCondition('id,name',['flag'=>4],'name asc');
        $gameList = model('Common/Game')->getAllByCondition('id,name',[],'','self');
		$selfGameList = array();
		foreach ($gameList as $game) {
              $selfGameList[ $game['id']] = $game;
        }

        //$gameList = array_column($gameList, 'name', 'id');

        $this->assign('list', $data);
        $this->assign('start_time', $this->start_time);
        $this->assign('end_time', $this->end_time);
        $this->assign('total', $list->total());
        $this->assign('page', $list->render());
        $this->assign('channel_list', $channel_list);
        $this->assign('game_list', $selfGameList);

        return $this->fetch();
    }

    /**
     * 报表下载
     *
     */
    public function download()
    {
        if (request()->isAjax()) {
            $auth       = new \app\common\logic\Auth();
            $is_show = input('is_show');
            if($is_show>(int)$auth->check(session('ADMIN_ID'), 'admin/complex_member/memberDowmExcel'))
            {
                $this->error('您没有访问权限！');
            }

            $this->getWhere();
            $sql  = $this->membersModel->table('nw_complex_members m,nw_complex_channel c')
                        ->field('m.id,m.mg_username,m.complex_username,m.gameid,m.total_pay_amount,m.channel_id,m.reg_time,m.login_time,c.name channel_name')
                        ->where('m.channel_id=c.id')
                        ->where($this->where)->order('m.reg_time desc')
                        ->fetchSql(true)->select();

            if ((new MakeReportGo())->addTask('admin.complexMemberList',$sql,session_id(),['is_show'=>boolval($is_show)])){
                //高级报表下载
                if($is_show==1){
                    $this->insertLog($this->current_node,'注册用户高级报表下载',58);
                }
                $this->success('报表生成的任务已经提交, 报表生成完成后，会及时通知您，请耐心稍等');
            }
			else{
				$this->error('报表生成任务不可重复提交，如遇到无法导出情况，建议修改查询条件解除当前状态，提交重新生成报表任务！');
			}
        }
        else{
            $this->error('非法请求');
        }

    }

    /**
     * 登录用户列表
     */
    public function loginList()
    {
        $list = Db::table('nw_complex_loginlog l,nw_complex_members m,nw_complex_channel c,cy_game g')
                    ->field("l.ip,l.channel_id as channel_id,l.userid,l.login_time,m.mg_username,c.name as channel_name,g.name game_name,g.origin_name")
                    ->where('l.userid = m.id and l.channel_id=c.id and l.gameid=g.id')
                    ->where($this->_getLoginListCondition())
                    ->order('l.id desc')
                    ->paginate(20, false, ['query' => input('get.')]);

        $data = $list->toArray()['data'];

        $gameList = model('Common/Game')->getAllByCondition('id,name',[],'','self');
		$selfGameList = array();
		foreach ($gameList as $game) {
              $selfGameList[ $game['id']] = $game;
        }
        //$gameList = array_column($gameList, 'name', 'id');

        $this->assign('list', $data);
        $this->assign('total', $list->total());
        $this->assign('page', $list->render());
        $this->assign('game_list', $selfGameList);
        $this->assign('start_time', $this->login_start_time);
        $this->assign('end_time', $this->login_end_time);

        return $this->fetch('login_list');
    }

    /**
     * 登录用户 报表下载
     */
    public function loginListDownload()
    {
        if (request()->isAjax()) {

            $sql = Db::table('nw_complex_loginlog l,nw_complex_members m,nw_complex_channel c,cy_game g')
                        ->field("l.ip,l.channel_id as channel_id,l.userid,l.login_time,m.mg_username,c.name as channel_name,g.name game_name,g.origin_name")
                        ->where('l.userid = m.id and l.channel_id=c.id and l.gameid=g.id')
                        ->where($this->_getLoginListCondition())
                        ->order('l.id desc')
                        ->fetchSql(true)
                        ->select();

            if((new MakeReportGo())->addTask('admin.complexMemberLoginList', $sql, session_id())){
                $this->success('报表生成的任务已经提交, 报表生成完成后，会及时通知您，请耐心稍等');
            }
            $this->error('报表生成任务不可重复提交，如遇到无法导出情况，建议修改查询条件解除当前状态，提交重新生成报表任务！');

        } else {

            $this->error('非法请求');
        }
    }

    /**
     * 登录用户  条件查询
     * @return array
     */
    protected function _getLoginListCondition()
    {
        $where = [];

        $start_time       = input('request.start_time', '', 'trim');
        $end_time         = input('request.end_time', '', 'trim');
        $channel_name     = input('request.channel_name', '', 'trim');
        $username         = input('request.mg_username', '', 'trim');
        $gameid           = input('request.gameid', '', 'intval');

        //开始时间和结束时间不为空时
        if ($start_time != '' && $end_time != '') {
            $where['l.login_time'] = [
                ['>=', strtotime($start_time)],
                ['<=', strtotime($end_time . ' 23:59:59')],
            ];
        } //开始时间不为空时
        elseif ($start_time != '') {
            $where['l.login_time'] = ['>=', strtotime($start_time)];
        } //结束时间不为空时
        elseif ($end_time != '') {
            $where['l.login_time'] = ['<=', strtotime($end_time . ' 23:59:59')];
        } else {
            $start_time = date('Y-m-d', time());
            $end_time = $start_time;
            $where['l.login_time'] = [
                ['>=', strtotime($start_time)],
                ['<=', strtotime($end_time . ' 23:59:59')],
            ];
        }

        //渠道名称
        if ($channel_name != '') {
            $where['c.name'] = ['like', '%' . $channel_name . '%'];
        }

        //用户名
        if ($username != '') {
            $where['m.mg_username'] = $username;
        }

        //游戏名称
        if (!empty($gameid)) {
            $where['l.gameid'] = $gameid;
        }

        $this->login_start_time = $start_time;
        $this->login_end_time = $end_time;


        // 游戏上线状态
        $cooperation_status = $this->request->param('cooperation_status', 0, 'intval'); // 游戏上线状态
        if(!empty($cooperation_status)){
            $game_cooperation_status = $cooperation_status==1?1:2;
            $gameIds = Model('Game')->getNormalByIds($game_cooperation_status);
            $where['l.gameid'] = ['in', $gameIds];
        }

        return $where;
    }

    /**
     * 充值记录
     *
     */
    public function payList()
    {
        $condition = $this->_beforeGetPayParams();

        //充值完成总金额
        $totalAmount = 0;
        $totalData = [
            'total_amount' => 0,
            'total_pay_amount' => 0,
        ];

        $payModel = new ComplexPay;
        $cp_status         = $this->request->param('cp_status');
        $where = [];
        if (is_numeric($cp_status)) {
            $where['y.status'] = (int)$cp_status;
        }

        $list = $payModel->alias('p')
             ->join('nw_complex_channel c', 'p.channel_id=c.id')
             ->join('cy_paycpinfo y', 'p.orderid=y.orderid')
             ->field('p.orderid,p.sub_orderid,p.gameid,p.username,p.userid,p.amount,p.status,p.create_time,p.attach,c.name channel_name,y.status as cp_status,p.roleid,p.rolename,p.pay_amount')
             ->where($where)
             ->where($condition)
             ->order('p.id desc')
             ->paginate(15, false, ['query' => input('get.')]);

        if ($list->total()>0) {
             $data      = $list->toArray()['data'];
             $userIdArr = $gameIdArr = [];
             foreach ($data as $v) {
                 $userIdArr[]    = $v['userid'];
                 $gameIdArr[]    = $v['gameid'];
             }

             $gameNameArr = model('Common/Game')->whereIn('id', $gameIdArr)->column('name', 'id');
             $regTimeArr  = $this->membersModel->whereIn('id', array_unique($userIdArr))->column('reg_time', 'id');

             foreach ($list as $key => $item) {
                 $default                        = '--';
                 $list[ $key ]['game_name']      = isset($gameNameArr[ $item['gameid'] ]) ? $gameNameArr[ $item['gameid'] ] : $default;

                 $list[ $key ]['reg_time']       = isset($regTimeArr[ $item['userid'] ]) ? date("Y-m-d H:i:s", $regTimeArr[ $item['userid'] ]) : $default;
             }

             // 充值完成总金额 + 充值实付完成总金额
            $totalData = $payModel->alias('p')->field('sum(amount) as total_amount, sum(pay_amount) as total_pay_amount')->where($condition)->where('p.status=1')->find();
        }

        $channel_list = (new ComplexChannelModel())->getAllByCondition('id,name',['flag'=>4],'name asc');
        // 获取公司列表
        $companyList = (new CompanyGameBind())->field('id,company_name,company_alias')->order('id desc')->select();
        $companyArr = [];
        foreach ($companyList as $company) {
            $companyArr[] = [
                'id' => $company['id'],
                'name' => $company['company_name'] . ' (' . $company['company_alias'] . ')'
            ];
        }

        $this->assign('list', $list);
        $this->assign('total', $list->total());
        $this->assign('page', $list->render());

        $gameList = model('Common/Game')->getAllByCondition('id,name',[],'','self');
		$selfGameList = array();
		foreach ($gameList as $game) {
            $selfGameList[ $game['id']] = $game;
        }
        $this->assign('game_list', $selfGameList);

        $this->assign('channel_list', $channel_list);          //渠道列表
        $this->assign('company_list', $companyArr);            //公司列表
        $this->assign('totalData', $totalData);
        $this->assign('pay_start_time', $this->pay_start_time); //开始时间
        $this->assign('pay_end_time', $this->pay_end_time);     //结束时间

        return $this->fetch('pay_list');
    }

    /**
     * 补单通知CP
     *
     */
    public function reCallback()
    {
        if($this->request->isPost()){

            $orderid = input('post.orderid','','trim');

            if(empty($orderid)){
                $this->error('订单号不能为空');
            }

            $payCpInfoModel = model('Common/PayCpinfo');

            $where['orderid'] = $orderid;

            $paycpInfo = $payCpInfoModel->field('id,fcallbackurl,params,payflag')->where($where)->find();

            if(empty($paycpInfo)){
                $this->error('订单通知回调记录不存在');
            }
            elseif($paycpInfo['payflag']!=1){
                $this->error('订单未完成支付或支付失败');
            }

            $guzzle = new Client();

            //将字符串转换成数组
            parse_str($paycpInfo['params'], $param);

            try {
                $response = $guzzle->request('post', $paycpInfo['fcallbackurl'], ['form_params' => $param, 'timeout' => 30]);
                log_message($paycpInfo['fcallbackurl'] . '?' . http_build_query($param) . '  ' . $response->getBody(), 'log', LOG_PATH . 'reCallback/');
                //通知成功
                if (0 == strcasecmp($response->getBody(), 'success')) {

                    $payCpInfoModel->save(['status' => 1, 'update_time' => NOW_TIMESTAMP], ['id' => $paycpInfo['id']]);

                    $this->success('订单通知成功');
                }
                else{
                    $this->error('订单通知已发送给对方，但对方未返回[success]信息');
                }

            } catch (RequestException $e) {

                $this->error("订单通知失败： " . $e->getMessage());
            }

        }

        return $this->fetch('re_callback');
    }

    /**
     * 充值记录 报表下载
     */
    public function payListDownload()
    {
        if (request()->isAjax()) {

            $payModel = new ComplexPay;

//            $sql = $payModel->table('nw_complex_pay p,nw_complex_channel c,cy_game g,nw_complex_members m')
//                            ->field('orderid,p.sub_orderid,p.gameid,p.username,amount,p.status,p.create_time,attach,c.name channel_name,g.name game_name,g.origin_name,m.reg_time')
//                            ->where('p.channel_id=c.id and p.gameid=g.id and p.userid=m.id')
//                            ->where($this->_beforeGetPayParams())
//                            ->order('p.id desc')
//                            ->fetchSql(true)
//                            ->select();
            $condition = $this->_beforeGetPayParams();
            $cp_status         = $this->request->param('cp_status');
            $where = [];
            if (is_numeric($cp_status)) {
                $where['y.status'] = (int)$cp_status;
            }

            $sql = $payModel->alias('p')
                ->join('nw_complex_channel c', 'p.channel_id=c.id')
                ->join('cy_paycpinfo y', 'p.orderid=y.orderid')
                ->join('nw_complex_members ncm', 'p.userid=ncm.id')
                ->field('p.orderid,p.sub_orderid,p.gameid,p.username,p.userid,p.amount,p.status,p.create_time,p.attach,c.name channel_name,y.status as cp_status,p.roleid,p.rolename,ncm.reg_time,p.pay_amount')
                ->where($where)
                ->where($condition)
                ->order('p.id desc')
                ->fetchSql(true)
                ->select();

            if ( (new MakeReportGo())->addTask('admin.complexPayList', $sql, session_id())){
                $this->success('报表生成的任务已经提交, 报表生成完成后，会及时通知您，请耐心稍等');
            }
            $this->error('报表生成任务不可重复提交，如遇到无法导出情况，建议修改查询条件解除当前状态，提交重新生成报表任务！');

        } else {

            $this->error('非法请求');
        }
    }

    /**
     * 前置方法(参数获取)
     *
     * @return $condition array 查询条件
     */
    protected function _beforeGetPayParams()
    {
        $start          = $this->request->param('start');
        $end            = $this->request->param('end');
        $channel_id     = $this->request->param('channel_id', 0, 'intval');
        $gameid         = $this->request->param('gameid', 0, 'intval');
        $status         = $this->request->param('status');
        $orderid        = $this->request->param('orderid', '', 'trim');
        $sub_orderid    = $this->request->param('sub_orderid', '', 'trim');
        $username       = $this->request->param('username', '', 'trim');
        $rolename = $this->request->param('rolename', '', 'trim');
        $roleid = $this->request->param('roleid', '', 'trim');
        $company_id = $this->request->param('company_id', 0, 'intval');
        $condition = [];

        // 如果选择了公司，则使用公司绑定的游戏ID列表
        if (!empty($company_id)) {
            $companyGameBind = new CompanyGameBind();
            $companyInfo = $companyGameBind->where(['id' => $company_id])->find();
            if (!empty($companyInfo) && !empty($companyInfo['game_ids'])) {
                $gameIds = explode(',', $companyInfo['game_ids']);
                $gameIds = array_filter($gameIds);
                if (!empty($gameIds)) {
                    if (count($gameIds) > 1) {
                        $condition['p.gameid'] = ['in', $gameIds];
                    } else {
                        $condition['p.gameid'] = $gameIds[0];
                    }
                } else {
                    // 如果公司没有绑定游戏，设置一个不存在的ID，使查询结果为空
                    $condition['p.gameid'] = -1;
                }
            } else {
                // 如果公司不存在或没有绑定游戏，设置一个不存在的ID
                $condition['p.gameid'] = -1;
            }
        } elseif (!empty($gameid)) {
            // 如果没有选择公司，则使用原来的游戏ID搜索逻辑
            $condition['p.gameid'] = $gameid;
        }
        if (!empty($orderid)) {
            $condition['p.orderid'] = $orderid;
        }
        if (!empty($sub_orderid)) {
            $condition['p.sub_orderid'] = $sub_orderid;
        }
        if (is_numeric($status)) {
            $condition['p.status'] = (int)$status;
        }
        if (!empty($username)) {
            $condition['p.username'] = $username;
        }
        if (!empty($channel_id)) {
            $condition['p.channel_id'] = $channel_id;
        }

        //开始时间和结束时间不为空时
        if ($start != '' && $end != '') {
            $condition['p.create_time'] = [
                ['>=', strtotime($start)],
                ['<=', strtotime($end . ' 23:59:59')],
            ];
        } //开始时间不为空时
        elseif ($start != '') {
            $condition['p.create_time'] = ['>=', strtotime($start)];
        } //结束时间不为空时
        elseif ($end != '') {
            $condition['p.create_time'] = ['<=', strtotime($end . ' 23:59:59')];
        } else {
            $start = date('Y-m-d', strtotime('-30 days'));
            $end   = date('Y-m-d', time());

            $condition['p.create_time'] = [
                ['>=', strtotime($start)],
                ['<=', strtotime($end . ' 23:59:59')],
            ];
        }

        $this->pay_start_time = $start;
        $this->pay_end_time   = $end;
        if ($rolename != '') {
            $condition['p.rolename'] = ['like', "%".$rolename."%"];
        }
        if ($roleid != '') {
            $condition['p.roleid'] = $roleid;
        }

        // 游戏上线状态
        $cooperation_status = $this->request->param('cooperation_status', 0, 'intval'); // 游戏上线状态
        if(!empty($cooperation_status)){
            $game_cooperation_status = $cooperation_status==1?1:2;
            $gameIds = Model('Game')->getNormalByIds($game_cooperation_status);
            $condition['p.gameid'] = ['in', $gameIds];
        }

        return $condition;
    }

    /**
     * ajax 获取cp订单号
     */
    public function showAttach(){
        $order_id = input('post.orderid', '', 'filterAndTrimInput');     // 订单号
        $this->_showAttach($order_id);
    }

    /**
     * 查看订单的attach信息
     * @param $orderid string 订单号
     */
    private function _showAttach($orderid) {
        $runnable = true;
        $msg      = '';
        $status   = false;
        if(empty($orderid)) {
            $runnable = false;
            $msg      = '参数错误';
        }
        if($runnable) {
            $result = model('ComplexPay')
                ->where(['orderid'=>$orderid])
                ->field('attach')
                ->find();
            if(empty($result)) {
                $runnable = false;
                $msg      = '订单不存在';
            }
        }
        // 查看attach
        if($runnable) {
            $status = 1;
            $result['attach'] = 'attach='.$result['attach'];
            parse_str($result['attach'],$arr);
            $msg = $arr['attach']; // 变量由上一个函数解析出来的
        }
        echo json_encode(['code'=>$status,'msg'=>$msg]);
    }

    /*
     * 聚合用户充值列表
     */
    public function rechargeList()
    {
        $condition = $this->_rechargePay();
        $payModel = new ComplexPay;
        $amountSort = strtolower($this->request->param('amount_sort', 'desc'));
        $amountSort = in_array($amountSort, ['asc', 'desc']) ? $amountSort : 'desc';

        $query = $payModel->alias('p')
            ->field('p.userid AS userid,SUM(p.pay_amount) AS amount,COUNT(1) AS frequency,MAX(p.create_time) AS last_pay_time')
            ->where($condition)
            ->where('p.status', 1)
            ->group('p.userid')
            ->order('amount ' . $amountSort);

        $list = $query->paginate(25, false, ['query' => input('get.')]);
        $listData = $list->getCollection()->toArray();

        if (!empty($listData)) {
            $userIdArr = array_column($listData, 'userid');
            $latestPays = $payModel->alias('latest')
                ->field('latest.userid,latest.pay_amount,latest.username')
                ->where($condition)
                ->where('latest.status', 1)
                ->whereIn('latest.userid', $userIdArr)
                ->order('latest.create_time desc')
                ->select();

            if ($latestPays instanceof \think\Collection) {
                $latestPays = $latestPays->toArray();
            } elseif (is_object($latestPays) && method_exists($latestPays, 'toArray')) {
                $latestPays = $latestPays->toArray();
            } elseif (empty($latestPays)) {
                $latestPays = [];
            }
            $latestMap = [];
            foreach ($latestPays as $record) {
                $uid = $record['userid'];
                if (!isset($latestMap[$uid])) {
                    $latestMap[$uid] = [
                        'amount'   => $record['pay_amount'],
                        'username' => $record['username'],
                    ];
                }
            }

            foreach ($listData as $key => $item) {
                $uid = $item['userid'];
                $listData[$key]['ramount'] = isset($latestMap[$uid]) ? $latestMap[$uid]['amount'] : 0;
                $listData[$key]['username'] = isset($latestMap[$uid]) ? $latestMap[$uid]['username'] : '';
            }
        }

        $this->assign('list', $listData);
        $this->assign('total', $list->total());
        $this->assign('page', $list->render());
        $this->assign('amount_sort', $amountSort);

        $gameList = model('Common/Game')->getAllByCondition('id,name',[],'','self');
		$selfGameList = array();
		foreach ($gameList as $game) {
              $selfGameList[ $game['id']] = $game;
        }
        $this->assign('game_list', $selfGameList);

        $rawSelectedGameIds = $this->request->param('gameids', $this->request->param('game_ids', ''));
        $selectedGameIdArr = $this->normalizeGameIdsParam($rawSelectedGameIds);
        $this->assign('selected_game_id_arr', $selectedGameIdArr);
        $this->assign('selected_game_ids', implode(',', $selectedGameIdArr));

        return $this->fetch('recharge_list');
    }
    /*
     * 聚合用户充值查询
     */
    protected function _rechargePay()
    {
        $start = $this->request->param('start');
        $end = $this->request->param('end');
        $username = $this->request->param('username','','trim');
        $gameid = $this->request->param('gameid',0,'trim');
        $gameidsParam = $this->request->param('gameids', $this->request->param('game_ids', ''));
        $condition = [];
        $gameIdList = $this->normalizeGameIdsParam($gameidsParam);
        if (!empty($gameIdList)) {
            $condition['gameid'] = ['in', $gameIdList];
        } elseif (!empty($gameid)){
            $condition['gameid'] = $gameid;
        }
        if (!empty($username)){
            $condition['username'] = $username;
        }
        //开始时间和结束时间不为空时
        if ($start != '' && $end != '') {
            $condition['create_time'] = [
                ['>=', strtotime($start)],
                ['<=', strtotime($end . ' 23:59:59')],
            ];
        } //开始时间不为空时
        elseif ($start != '') {
            $condition['create_time'] = ['>=', strtotime($start)];
        } //结束时间不为空时
        elseif ($end != '') {
            $condition['create_time'] = ['<=', strtotime($end . ' 23:59:59')];
        } else {
			/*
            $start = date('Y-m-d', time());
            $end   = date('Y-m-d', time());

            $condition['create_time'] = [
                ['>=', strtotime($start)],
                ['<=', strtotime($end . ' 23:59:59')],
            ];
			*/
        }
        return $condition;
    }

    /**
     * 解析多选游戏参数
     * @param mixed $param
     * @return array
     */
    protected function normalizeGameIdsParam($param)
    {
        if (is_array($param)) {
            $ids = $param;
        } else {
            $param = trim((string)$param);
            if ($param === '') {
                return [];
            }
            $ids = explode(',', $param);
        }
        $ids = array_map('intval', $ids);
        $ids = array_filter($ids, function ($id) {
            return $id > 0;
        });
        return array_values(array_unique($ids));
    }

    /**
     * 查看聚合渠道ID/账号
     */
    public function showUsername()
    {
        $userid = input('userid');

        if(empty($userid)){

            $this->error('用户ID不能为空');
        }

        $username= model('ComplexMembers')->where(['id'=>$userid])->value('complex_username');

        if(!empty($username)){

            $this->insertLog($this->current_node,'查看账号：'.$username,57);

            $this->result($username,1);
        }
        else{
            $this->error('用户信息不存在');
        }
    }

    /**
     * 渠道玩家活跃记录
     */
    public function playerActive()
    {
        $filters = $this->getPlayerActiveFilters();
        $where = $this->getPlayerActiveCondition($filters);

        $list = $this->getPlayerActiveQuery($where, $filters)
            ->order('a.active_hour desc,a.id desc')
            ->paginate(20, false, ['query' => input('get.')]);

        $gameList = model('Common/Game')->getAllByCondition('id,name', [], '', 'self');
        $complexList = (new ComplexChannelModel())
            ->getAllByCondition('id,name', [], 'name asc');
        $serverList = $this->getPlayerActiveServerOptions(
            $filters['game_id'],
            $filters['complex_id']
        );

        $this->hydratePlayerActiveList($list, $gameList, $complexList);

        $this->assign('list', $list);
        $this->assign('total', $list->total());
        $this->assign('page', $list->render());
        $this->assign('filters', $filters);
        $this->assign('gameList', $gameList);
        $this->assign('complexList', $complexList);
        $this->assign('serverList', $serverList);

        return $this->fetch('player_active');
    }

    /**
     * 根据渠道和游戏获取渠道玩家活跃页的区服选项。
     */
    public function getPlayerActiveServerList()
    {
        $gameId = input('get.game_id', 0, 'intval');
        $complexId = input('get.complex_id', 0, 'intval');
        $this->success('', '', $this->getPlayerActiveServerOptions($gameId, $complexId));
    }

    /**
     * 查询指定渠道、游戏的区服，任一条件未选择时不返回数据。
     */
    private function getPlayerActiveServerOptions($gameId, $complexId)
    {
        $gameId = intval($gameId);
        $complexId = intval($complexId);
        if ($gameId <= 0 || $complexId <= 0) {
            return [];
        }

        return Db::table('nw_complex_server')
            ->cache('complexPlayerActive:serverList:' . $complexId . ':' . $gameId, 60)
            ->where(['game_id' => $gameId, 'complex_id' => $complexId])
            ->field('id,game_id,server_id,complex_id,server_name as name')
            ->order('id desc')
            ->select();
    }

    /**
     * 渠道玩家活跃记录导出。
     */
    public function playerActiveDownload()
    {
        if (!request()->isAjax()) {
            $this->error('非法请求');
        }

        $filters = $this->getPlayerActiveFilters();
        $where = $this->getPlayerActiveCondition($filters);
        $sql = $this->getPlayerActiveQuery($where, $filters, true)
            ->order('a.active_hour desc,a.id desc')
            ->fetchSql(true)
            ->select();

        if ((new MakeReportGo())->addTask('complexPlayerActiveList', $sql, session_id())) {
            $this->success('报表生成的任务已经提交，报表生成完成后会及时通知您，请耐心等待');
        }

        $this->error('报表生成任务提交失败，请稍后重试');
    }

    /**
     * 获取渠道玩家活跃查询参数
     */
    private function getPlayerActiveFilters()
    {
        $activeStartTime = input('request.active_start_time', '', 'trim');
        $activeEndTime = input('request.active_end_time', '', 'trim');
        $mgUsername = input('request.mg_username', '', 'trim');
        if ($mgUsername === '') {
            // 兼容修复前生成的查询链接。
            $mgUsername = input('request.complex_username', '', 'trim');
        }

        // 活跃表数据量会持续增长，无时间条件时默认查询当天。
        if ($activeStartTime === '' && $activeEndTime === '') {
            $activeStartTime = $activeEndTime = date('Y-m-d');
        }

        $filters = [
            'mg_username' => $mgUsername,
            'game_id' => input('request.game_id', 0, 'intval'),
            'complex_id' => input('request.complex_id', 0, 'intval'),
            'server_id' => input('request.server_id', '', 'trim'),
            'role_name' => input('request.role_name', '', 'trim'),
            'role_id' => input('request.role_id', '', 'trim'),
            'create_start_time' => input('request.create_start_time', '', 'trim'),
            'create_end_time' => input('request.create_end_time', '', 'trim'),
            'active_start_time' => $activeStartTime,
            'active_end_time' => $activeEndTime,
            'register_start_time' => input('request.register_start_time', '', 'trim'),
            'register_end_time' => input('request.register_end_time', '', 'trim'),
        ];

        $this->validatePlayerActiveTimeRanges($filters);

        return $filters;
    }

    /**
     * 校验渠道玩家活跃查询中的时间范围。
     */
    private function validatePlayerActiveTimeRanges(array $filters)
    {
        $ranges = [
            '角色创建时间' => ['create_start_time', 'create_end_time'],
            '账号注册时间' => ['register_start_time', 'register_end_time'],
            '活跃时间' => ['active_start_time', 'active_end_time'],
        ];

        foreach ($ranges as $label => $fields) {
            $startTime = $filters[$fields[0]];
            $endTime = $filters[$fields[1]];
            if ($startTime !== '' && $endTime !== '' && strtotime($startTime) > strtotime($endTime)) {
                $this->error($label . '：开始时间 不能大于 结束时间');
            }
        }
    }

    /**
     * 组装渠道玩家活跃查询条件
     */
    private function getPlayerActiveCondition(array $filters)
    {
        $where = [];

        if ($filters['game_id'] > 0) {
            $where['a.game_id'] = $filters['game_id'];
        }
        if ($filters['complex_id'] > 0) {
            $where['a.complex_id'] = $filters['complex_id'];
        }
        if ($filters['server_id'] !== '') {
            $where['a.server_id'] = $filters['server_id'];
        }
        if ($filters['role_name'] !== '') {
            $where['a.role_name'] = ['like', '%' . $filters['role_name'] . '%'];
        }
        if ($filters['role_id'] !== '') {
            $where['a.role_id'] = $filters['role_id'];
        }

        $this->appendPlayerActiveTimeCondition(
            $where,
            'a.active_hour',
            $filters['active_start_time'],
            $filters['active_end_time'],
            false
        );
        $this->appendPlayerAccountCondition($where, $filters);

        return $where;
    }

    /**
     * 玩家账号先查询成员表，再使用 member_id 查询活跃主表。
     */
    private function appendPlayerAccountCondition(array &$where, array $filters)
    {
        if ($filters['mg_username'] === '') {
            return;
        }

        $memberWhere = ['mg_username' => $filters['mg_username']];
        if ($filters['game_id'] > 0) {
            $memberWhere['gameid'] = $filters['game_id'];
        }
        if ($filters['complex_id'] > 0) {
            $memberWhere['channel_id'] = $filters['complex_id'];
        }
        $this->appendPlayerActiveTimeCondition(
            $memberWhere,
            'reg_time',
            $filters['register_start_time'],
            $filters['register_end_time']
        );

        $memberIds = Db::table('nw_complex_members')
            ->where($memberWhere)
            ->column('id');
        $memberIds = array_values(array_unique(array_map('intval', $memberIds)));

        if (empty($memberIds)) {
            $where['a.member_id'] = -1;
        } elseif (count($memberIds) === 1) {
            $where['a.member_id'] = $memberIds[0];
        } else {
            $where['a.member_id'] = ['in', $memberIds];
        }
    }

    /**
     * 添加时间范围条件。角色和账号时间为时间戳，活跃时间为 DATETIME。
     */
    private function appendPlayerActiveTimeCondition(
        array &$where,
        $field,
        $startTime,
        $endTime,
        $unixTime = true
    ) {
        if ($startTime !== '' && $endTime !== '') {
            $start = $unixTime ? strtotime($startTime) : $startTime . ' 00:00:00';
            $end = $unixTime ? strtotime($endTime . ' 23:59:59') : $endTime . ' 23:59:59';
            $where[$field] = [['>=', $start], ['<=', $end]];
        } elseif ($startTime !== '') {
            $where[$field] = ['>=', $unixTime ? strtotime($startTime) : $startTime . ' 00:00:00'];
        } elseif ($endTime !== '') {
            $where[$field] = ['<=', $unixTime ? strtotime($endTime . ' 23:59:59') : $endTime . ' 23:59:59'];
        }
    }

    /**
     * 渠道玩家活跃查询。角色创建时间取同一角色的首次创建时间。
     */
    private function getPlayerActiveQuery(array $where, array $filters, $forExport = false)
    {
        $query = Db::table('nw_complex_player_active_log')->alias('a')
            ->where($where);

        if ($forExport) {
            // a.server_id 存的是 nw_complex_server.id（主键）。
            // 角色创建时间与列表 hydrate 一致：按 member+complex+game+role_id 取首次创建时间，
            // 不强制同区服（避免角色只在其他区服有记录时导出为空）；用分组子查询避免多区服打成多行。
            $roleLatestSql = Db::table('nw_complex_role')
                ->field('member_id,complex_id,game_id,role_id,MIN(create_time) as create_time')
                ->group('member_id,complex_id,game_id,role_id')
                ->buildSql();

            $query
                ->join('nw_complex_members member', 'member.id=a.member_id', 'left')
                ->join('cy_game game', 'game.id=a.game_id', 'left')
                ->join('nw_complex_channel complex', 'complex.id=a.complex_id', 'left')
                ->join(
                    'nw_complex_server server',
                    'server.id=a.server_id',
                    'left'
                )
                ->join(
                    [$roleLatestSql => 'role'],
                    'role.member_id=a.member_id AND role.complex_id=a.complex_id AND role.game_id=a.game_id AND role.role_id=a.role_id',
                    'left'
                )
                ->field('member.complex_username,game.name as game_name,complex.name as complex_name,COALESCE(server.server_name,a.server_id) as server_name,a.role_name,a.active_hour,role.create_time as role_create_time,member.reg_time');
        } else {
            $query->field('a.*');
        }

        // 玩家账号已在分页前查询成员ID，此时注册时间也已经一并筛选。
        if ($filters['mg_username'] === '') {
            $query = $this->appendMemberRegisterTimeCondition(
                $query,
                $filters['register_start_time'],
                $filters['register_end_time']
            );
        }

        return $this->appendRoleCreateTimeCondition(
            $query,
            $filters['create_start_time'],
            $filters['create_end_time']
        );
    }

    /**
     * 只有使用注册时间筛选时，才按主键关联成员表进行过滤。
     */
    private function appendMemberRegisterTimeCondition($query, $startTime, $endTime)
    {
        if ($startTime === '' && $endTime === '') {
            return $query;
        }

        return $query->whereExists(function ($memberQuery) use ($startTime, $endTime) {
            $memberQuery->table('nw_complex_members')
                ->alias('member_filter')
                ->field('1')
                ->whereExp('member_filter.id', '= a.member_id');

            if ($startTime !== '') {
                $memberQuery->where(
                    'member_filter.reg_time',
                    '>=',
                    intval(strtotime($startTime))
                );
            }
            if ($endTime !== '') {
                $memberQuery->where(
                    'member_filter.reg_time',
                    '<=',
                    intval(strtotime($endTime . ' 23:59:59'))
                );
            }
        });
    }

    /**
     * 按用户、渠道、游戏、角色筛选角色创建时间。
     */
    private function appendRoleCreateTimeCondition($query, $startTime, $endTime)
    {
        if ($startTime === '' && $endTime === '') {
            return $query;
        }

        return $query->whereExists(function ($roleQuery) use ($startTime, $endTime) {
            $roleQuery->table('nw_complex_role')
                ->alias('role_filter')
                ->field('1')
                ->whereExp('role_filter.member_id', '= a.member_id')
                ->whereExp('role_filter.complex_id', '= a.complex_id')
                ->whereExp('role_filter.game_id', '= a.game_id')
                ->whereExp('role_filter.role_id', '= a.role_id');

            if ($startTime !== '') {
                $roleQuery->where(
                    'role_filter.create_time',
                    '>=',
                    intval(strtotime($startTime))
                );
            }
            if ($endTime !== '') {
                $roleQuery->where(
                    'role_filter.create_time',
                    '<=',
                    intval(strtotime($endTime . ' 23:59:59'))
                );
            }
        });
    }

    /**
     * 分页完成后，仅批量查询当前页所需的关联数据并合并到列表。
     */
    private function hydratePlayerActiveList($list, $gameList, $complexList)
    {
        $items = $list->getCollection();
        if ($items->isEmpty()) {
            return;
        }

        $memberIds = [];
        $complexIds = [];
        $gameIds = [];
        $serverIds = [];
        $roleIds = [];
        foreach ($items as $item) {
            $memberIds[] = intval($item['member_id']);
            $complexIds[] = intval($item['complex_id']);
            $gameIds[] = intval($item['game_id']);
            $serverIds[] = $item['server_id'];
            $roleIds[] = (string)$item['role_id'];
        }
        $memberIds = array_values(array_unique($memberIds));
        $complexIds = array_values(array_unique($complexIds));
        $gameIds = array_values(array_unique($gameIds));
        $serverIds = array_values(array_unique($serverIds));
        $roleIds = array_values(array_unique($roleIds));

        $memberList = Db::table('nw_complex_members')
            ->where('id', 'in', $memberIds)
            ->field('id,mg_username,complex_username,reg_time')
            ->select();
        // dump($memberIds, $memberList);

        $memberById = array_column($memberList, null, 'id');
        // $memberById = [];
        // foreach ($memberList as $member) {
        //     $memberById[$member['id']] = $member;
        // }

        $currentServerList = Db::table('nw_complex_server')
            ->where('id', 'in', $serverIds)
            ->field('id,server_id,server_name')
            ->select();
        $serverById = [];
        foreach ($currentServerList as $server) {
            $serverById[$server['id']] = $server;
        }

        $gameById = array_column($gameList, 'name', 'id');
        $complexById = array_column($complexList, 'name', 'id');
        $roleCreateByKey = $this->getPlayerActiveRoleCreateMap(
            $memberIds,
            $complexIds,
            $gameIds,
            $roleIds
        );

        foreach ($items as $key => $item) {
            $member = $memberById[$item['member_id']] ?? [];
            $item['mg_username'] = $member['mg_username'] ?? '';
            $item['reg_time'] = $member['reg_time'] ?? 0;
            $item['game_name'] = $gameById[$item['game_id']] ?? '';
            $item['complex_name'] = $complexById[$item['complex_id']] ?? '';
            $server = $serverById[$item['server_id']] ?? [];
            $item['complex_server_id'] = $server['server_id'] ?? '';
            $item['server_name'] = $server['server_name'] ?? '';
            $item['role_create_time'] = $roleCreateByKey[$this->buildPlayerActiveRoleKey($item)] ?? 0;
            $items->offsetSet($key, $item);
        }
    }

    /**
     * 根据当前页用户、渠道、游戏、角色批量查询角色创建时间。
     */
    private function getPlayerActiveRoleCreateMap(
        array $memberIds,
        array $complexIds,
        array $gameIds,
        array $roleIds
    ) {
        if (empty($memberIds) || empty($complexIds) || empty($gameIds) || empty($roleIds)) {
            return [];
        }

        // 与导出一致：同角色多区服时取首次创建时间。
        $roleList = Db::table('nw_complex_role')
            ->where('member_id', 'in', $memberIds)
            ->where('complex_id', 'in', $complexIds)
            ->where('game_id', 'in', $gameIds)
            ->where('role_id', 'in', $roleIds)
            ->field('member_id,complex_id,game_id,role_id,MIN(create_time) as create_time')
            ->group('member_id,complex_id,game_id,role_id')
            ->select();

        $roleCreateByKey = [];
        foreach ($roleList as $role) {
            $roleKey = $this->buildPlayerActiveRoleKey($role);
            $roleCreateByKey[$roleKey] = $role['create_time'];
        }

        return $roleCreateByKey;
    }

    private function buildPlayerActiveRoleKey(array $item)
    {
        return json_encode([
            intval($item['member_id']),
            intval($item['complex_id']),
            intval($item['game_id']),
            (string)$item['role_id'],
        ], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
    }
}