<?php
/**
 * 前台注册用户管理控制器
 */

namespace app\guildapi\controller;

use app\common\library\MakeReportGo;
use think\Db;
use think\Paginator;
use think\Exception;
use think\JsonException;
use app\common\logic\Member as MemberService;
use app\common\library\MakeReport;
use think\paginator\driver\Bootstrap;
use think\db\exception\DataNotFoundException;
use think\db\exception\ModelNotFoundException;
use think\exception\DbException;
use think\Exception\httpResponseException;
use app\common\model\Members as MembersModel;


class Member extends Guild
{
    protected function _initialize()
    {
        parent::_initialize();
        $this->membersModel = Db::name('cy_members');
        $this->membersGameModel = Db::name('nw_member_game_server');
        $this->nw_channel = Db::name('nw_channel');
    }

    /**
     * 注册用户列表
     */
    public function getMemberList()
    {
        $channelArr = [5813];
        $download = input('download');
        $list_rows = input('pageSize', 10);
        $page = input('page', 1);
        $type = input('type', 1);
        $where = $this->_getCondition('members', 1);
        //已接单的游戏才能展示2022-08-03
        $where['game.id'] = ['in', model('game')->getTakingGame($this->_channelId)];
        switch ($download) {
            case '1':
                if ($type == 2) {
                    if (in_array($this->_channelId, $channelArr)) {
                        $field = 'mcg.mcgr_id,game.name as game_name,members.username, mgs.servername,mgs.rolename,mgs.rolelevel, mgs.ip,mgs.imeil,FROM_UNIXTIME( members.reg_time ) AS create_time,IFNULL(FROM_UNIXTIME( mgs.update_time),FROM_UNIXTIME( mcg.update_time)) AS login_time,FROM_UNIXTIME( mgs.create_time) AS reg_time,channel.name as channel_name,members.mobile as mobile';
                    } else {
                        $field = 'mcg.mcgr_id,game.name as game_name,members.username, mgs.servername,mgs.rolename,mgs.rolelevel, mgs.ip,mgs.imeil,FROM_UNIXTIME( members.reg_time ) AS create_time,IFNULL(FROM_UNIXTIME( mgs.update_time),FROM_UNIXTIME( mcg.update_time)) AS login_time,FROM_UNIXTIME( mgs.create_time) AS reg_time,channel.name as channel_name,"" as mobile';
                    }
                    $sql = Db::name('cy_member_channel_game_rel')->alias('mcg')
                        ->join('cy_game game', 'mcg.game_id = game.id', 'left')
                        ->join('cy_members members', 'mcg.member_id = members.id', 'left')
                        ->join('nw_member_game_server mgs', 'mcg.member_id=mgs.member_id and mcg.game_id=mgs.game_id', 'left')
                        ->join('nw_channel channel', 'mcg.channel_id=channel.id AND (channel.id=' . $this->_channelId . ' or channel.id_path like "' . $this->_channelIdPath . '%")')
                        ->field($field)
                        ->where($where)
                        ->where("game.is_default = 0")
                        // ->group("members.username")
                        ->group("mgs.rolename")
                        ->order('mcg.mcgr_id desc,mgs.mgs_id desc')
                        ->fetchSql(true)
                        ->select();
                } else {
                    if (in_array($this->_channelId, $channelArr)) {
                        $field = 'mgs.mgs_id,game.name as game_name,members.username,mgs.servername,mgs.rolename,mgs.rolelevel,mgs.ip,mgs.imeil,FROM_UNIXTIME( members.reg_time ) AS create_time,FROM_UNIXTIME( mgs.update_time) AS login_time,FROM_UNIXTIME( mgs.create_time) AS reg_time,channel.NAME AS channel_name ,members.mobile  as mobile';
                    } else {
                        $field = 'mgs.mgs_id,game.name as game_name,members.username,mgs.servername,mgs.rolename,mgs.rolelevel,mgs.ip,mgs.imeil,FROM_UNIXTIME( members.reg_time ) AS create_time,FROM_UNIXTIME( mgs.update_time) AS login_time,FROM_UNIXTIME( mgs.create_time) AS reg_time,channel.NAME AS channel_name ,"" as mobile';
                    }
                    $sql = Db::name('nw_member_game_server')->alias('mgs')
                        ->join('cy_game game', 'mgs.game_id = game.id', 'left')
                        ->join('cy_members members', 'mgs.member_id = members.id', 'left')
                        ->join('nw_channel channel', 'mgs.channel_id=channel.id AND (channel.id=' . $this->_channelId . ' or channel.id_path like "' . $this->_channelIdPath . '%")')
                        ->field($field)
                        ->where($where)
                        ->where("game.is_default = 0")
//                        ->group("members.username")
                        ->group("mgs.rolename")
                        ->order('mgs.mgs_id desc')
                        ->fetchSql(true)
                        ->select();
                }
//                if ((new MakeReport())->addTask('guild.memberRegExport', $sql, 'cps'.$this->_adminId)){
                if ((new MakeReportGo())->addTask('guild.memberRegExport', $sql, 'cps' . $this->_adminId)) {
                    return json(['data' => '', 'code' => 20000, 'msg' => '报表生成的任务已经提交, 报表生成完成后，会及时通知您，请耐心稍等']);
                } else {
                    return json(['data' => '', 'code' => 20013, 'msg' => '报表生成任务不可重复提交，如遇到无法导出情况，建议修改查询条件解除当前状态，提交重新生成报表任务！']);
                }
                break;
            default:
                if ($type == 2) {
                    if (in_array($this->_channelId, $channelArr)) {
                        $field = 'mcg.mcgr_id,game.name as game_name,members.username, mgs.servername,mgs.rolename,mgs.rolelevel, mgs.ip,mgs.imeil,FROM_UNIXTIME( members.reg_time ) AS create_time,IFNULL(FROM_UNIXTIME( mgs.update_time),FROM_UNIXTIME( mcg.update_time)) AS login_time,FROM_UNIXTIME( mgs.create_time) AS reg_time,channel.name as channel_name,members.mobile  as mobile';
                    } else {
                        $field = 'mcg.mcgr_id,game.name as game_name,members.username, mgs.servername,mgs.rolename,mgs.rolelevel, mgs.ip,mgs.imeil,FROM_UNIXTIME( members.reg_time ) AS create_time,IFNULL(FROM_UNIXTIME( mgs.update_time),FROM_UNIXTIME( mcg.update_time)) AS login_time,FROM_UNIXTIME( mgs.create_time) AS reg_time,channel.name as channel_name,"" as mobile';
                    }
                    $list = Db::name('cy_member_channel_game_rel')->alias('mcg')
                        ->join('cy_game game', 'mcg.game_id = game.id', 'left')
                        ->join('cy_members members', 'mcg.member_id = members.id', 'left')
                        ->join('nw_member_game_server mgs', 'mcg.member_id=mgs.member_id and mcg.game_id=mgs.game_id', 'left')
                        ->join('nw_channel channel', 'mcg.channel_id=channel.id AND (channel.id=' . $this->_channelId . ' or channel.id_path like "' . $this->_channelIdPath . '%")')
                        ->field($field)
                        ->where($where)
                        ->where("game.is_default = 0")
                        ->group("mgs.rolename")
                        ->order('mcg.mcgr_id desc,mgs.mgs_id desc')
                        ->paginate(['list_rows' => $list_rows, 'page' => $page])->toArray();
                } else {
                    if (in_array($this->_channelId, $channelArr)) {
                        $field = 'mgs.mgs_id,mgs.game_id,mgs.member_id,game.name as game_name,members.username,mgs.servername,mgs.rolename,mgs.rolelevel,mgs.ip,mgs.imeil,FROM_UNIXTIME( members.reg_time ) AS create_time,FROM_UNIXTIME( mgs.update_time) AS login_time,FROM_UNIXTIME( mgs.create_time) AS reg_time,channel.NAME AS channel_name ,members.mobile as mobile';
                    } else {
                        $field = 'mgs.mgs_id,mgs.game_id,mgs.member_id,game.name as game_name,members.username,mgs.servername,mgs.rolename,mgs.rolelevel,mgs.ip,mgs.imeil,FROM_UNIXTIME( members.reg_time ) AS create_time,FROM_UNIXTIME( mgs.update_time) AS login_time,FROM_UNIXTIME( mgs.create_time) AS reg_time,channel.NAME AS channel_name ,"" as mobile';
                    }

                    $list = Db::name('nw_member_game_server')->alias('mgs')
                        ->join('cy_game game', 'mgs.game_id = game.id', 'left')
                        ->join('cy_members members', 'mgs.member_id = members.id', 'left')
                        ->join('nw_channel channel', 'mgs.channel_id=channel.id AND (channel.id=' . $this->_channelId . ' or channel.id_path like "' . $this->_channelIdPath . '%")')
                        ->field($field)
                        ->where($where)
                        ->where("game.is_default = 0")
                        ->group("mgs.rolename")
                        ->order('mgs.mgs_id desc')
                        ->paginate(['list_rows' => $list_rows, 'page' => $page])->toArray();

                    foreach ($list['data'] as $k=> $value) {
                        if ($value['game_name'] == "仙剑奇侠传3D回合（iOS）") {
                            $level = Db::name('nw_member_game_server')->alias('mgs')
                                ->join('cy_game game', 'mgs.game_id = game.id', 'left')
                                ->where('game.name','=','仙剑奇侠传3D回合（iOS）')
                                ->where('mgs.servername','=',$value['servername'])
                                ->where('mgs.member_id','=',$value['member_id'])
                                ->where('mgs.game_id','=',$value['game_id'])
                                ->max('mgs.rolelevel');
                            $list['data'][$k]['rolelevel'] = $level;
                        }

                        if ($value['game_name'] == "仙剑奇侠传3D回合（安卓）") {
                            $level = Db::name('nw_member_game_server')->alias('mgs')
                                ->join('cy_game game', 'mgs.game_id = game.id', 'left')
                                ->where('game.name','=','仙剑奇侠传3D回合（安卓）')
                                ->where('mgs.servername','=',$value['servername'])
                                ->where('mgs.member_id','=',$value['member_id'])
                                ->where('mgs.game_id','=',$value['game_id'])
                                ->max('mgs.rolelevel');
                            $list['data'][$k]['rolelevel'] = $level;
                        }
                    }
                }
//	    echo Db::getLastSql();die;
//            $gameList = model('Common/Game')->getAllByCondition('id,name',['game_kind'=>1,'cooperation_status'=>['neq',0]],'','self');
                $gameList = $this->getTakingGameList();
                $channelList = Db::table('nw_channel')->field('id,name')->where(['level' => 3, 'id_path' => ['LIKE', $this->_channelIdPath . '%']])->select();
                return json(['data' => $list, 'code' => 20000, 'msg' => '获取数据成功', 'gameList' => $gameList, 'channelList' => $channelList]);
                break;
        }

    }
    /**************************************************************首页数据*****************************************/

    /**
     * 首页注册用户统计
     */
    public function getMemberCount()
    {
        $forbid_channel_ids = Db::table("nw_channel_view_limit")->column("channel_id");
        if (in_array($this->_channelId, $forbid_channel_ids)) {
            return json(['data' => '', 'code' => 10032, 'msg' => '暂时没有权限查看首页注册用户统计']);
        }
        $type = input('type');
        $where = [];
        //已接单的游戏才能展示2022-08-03
        $where['game.id'] = ['in', model('game')->getTakingGame($this->_channelId)];
        $weekormonth = [];
        switch ($type) {
            case 'month':
                $formdata = '%Y-%m';
                $where['mgs.create_time'] = [['>=', time() - 86400 * 220], ['<=', strtotime(date('Y-m-d 23:59:59'))]];
//                $where['mgs.create_time'] = [['>=', strtotime(date("Y",time())."-1"."-1")],['<=', strtotime(date('Y-m-d 23:59:59'))]];
                for ($i = 1; $i <= 7; $i++) {
//                    $weekormonth[$i]['date'] = date('Y-m' ,strtotime( '+' . $i-7 .' month'));
                    $weekormonth[$i]['date'] = date("Y-m", mktime(0, 0, 0, date("m") + ($i - 7), 01, date("Y")));
                    $weekormonth[$i]['注册量'] = 0;
                }
                break;

            default:
                $formdata = '%Y-%m-%d';
                $where['mgs.create_time'] = [['>=', strtotime('-7 days 0:0:0')], ['<=', strtotime(date('Y-m-d 23:59:59'))]];
                for ($i = 1; $i <= 7; $i++) {
                    $weekormonth[$i]['date'] = date('Y-m-d', strtotime('+' . $i - 7 . ' days'));
                    $weekormonth[$i]['注册量'] = 0;
                }
                break;
        }
        $list = [];
        $list = Db::name('nw_member_game_server')->alias('mgs')
            ->join('cy_game game', 'mgs.game_id = game.id and game.game_kind=1')
            ->join('nw_channel channel', 'mgs.channel_id=channel.id AND (channel.id=' . $this->_channelId . ' or channel.id_path like "' . $this->_channelIdPath . '%")')
            ->field('from_unixtime( mgs.create_time, "' . $formdata . '" ) AS date,count(*) AS counts')
            ->where($where)
            ->where("game.is_default = 0")
            ->group('date')
            ->order('date ASC')
            ->select();

        $list = array_column($list, 'counts', 'date');

        foreach ($weekormonth as $key => $value) {
            if (isset($list[$value['date']])) {
                $weekormonth[$key]['注册量'] = $list[$value['date']];
            }
        }
        $result = [];
        $result['columns'] = ['date', '注册量'];
        $result['rows'] = array_values($weekormonth);
        return json(['data' => $result, 'code' => 20000, 'msg' => '获取数据成功']);
    }

    /**
     * 首页流水数据统计
     */
    public function getSumCount()
    {
        $forbid_channel_ids = Db::table("nw_channel_view_limit")->column("channel_id");
        if (in_array($this->_channelId, $forbid_channel_ids)) {
            return json(['data' => '', 'code' => 10032, 'msg' => '暂时没有权限查看首页流水数据统计']);
        }
        $type = input('type');
        $where = [];
        //已接单的游戏才能展示2022-08-03
        $where['game.id'] = ['in', model('game')->getTakingGame($this->_channelId)];
        $weekormonth = [];
        switch ($type) {
            case 'month':
                $formdata = '%Y-%m';
                $where['pay.create_time'] = [['>=', time() - 86400 * 220], ['<=', strtotime(date('Y-m-d 23:59:59'))]];
//                $where['pay.create_time'] = [['>=', strtotime(date("Y",time())."-1"."-1")],['<=', strtotime(date('Y-m-d 23:59:59'))]];
                for ($i = 1; $i <= 7; $i++) {
//                    $weekormonth[$i]['date'] = date('Y-m' ,strtotime( '+' . $i-7 .' month'));
                    $weekormonth[$i]['date'] = date("Y-m", mktime(0, 0, 0, date("m") + ($i - 7), 01, date("Y")));
                    $weekormonth[$i]['流水'] = 0;
                }
                break;

            default:
                $formdata = '%Y-%m-%d';
                $where['pay.create_time'] = [['>=', strtotime('-7 days 0:0:0')], ['<=', strtotime(date('Y-m-d 23:59:59'))]];
                for ($i = 1; $i <= 7; $i++) {
                    $weekormonth[$i]['date'] = date('Y-m-d', strtotime('+' . $i - 7 . ' days'));
                    $weekormonth[$i]['流水'] = 0;
                }
                break;
        }

        $list = Db::name('cy_pay')->alias('pay')
            ->join('nw_channel channel', 'pay.channel_id=channel.id AND (channel.id=' . $this->_channelId . ' or channel.id_path like "' . $this->_channelIdPath . '%")')
            ->join('cy_game game', 'pay.gameid=game.id AND game.game_kind=1')
            ->field('from_unixtime( pay.create_time, "' . $formdata . '" ) AS date,sum(pay.pay_amount) as counts')
            ->where($where)
            ->where("game.is_default = 0")
            ->where(['pay.status' => 1])
            ->group('date')
            ->order('date ASC')
            ->select();
        $list = array_column($list, 'counts', 'date');
        foreach ($weekormonth as $key => $value) {
            if (isset($list[$value['date']])) {
                $weekormonth[$key]['流水'] = $list[$value['date']];
            }
        }
        $result = [];
        $result['columns'] = ['date', '流水'];
        $result['rows'] = array_values($weekormonth);
        return json(['data' => $result, 'code' => 20000, 'msg' => '获取数据成功']);
    }

    /**
     * 首页推广员数据
     */
    public function getAgentCount()
    {

        $forbid_channel_ids = Db::table("nw_channel_view_limit")->column("channel_id");
        if (in_array($this->_channelId, $forbid_channel_ids)) {
            return json(['data' => '', 'code' => 10032, 'msg' => '暂时没有权限查看首页推广员数据']);
        }
        $type = input('type');
        $where = [];
        $list_rows = input('pageSize', 5);
        $page = input('page', 1);
        $t_day = date('Y-m-d');//今日日期
        $yse_day = date("Y-m-d", strtotime("-1 day"));//昨日日期

        if ($type == 'agent') {
            $where['reg_time'] = [['>=', strtotime('-1 days 0:0:0')], ['<=', strtotime(date('Y-m-d 23:59:59'))]];
            $wherea['create_time'] = [['>=', strtotime('-1 days 0:0:0')], ['<=', strtotime(date('Y-m-d 23:59:59'))]];
            //$where['channel_id'] = [''.$this->_channelId.''];
            //$list  = $this->membersGameModel->alias('members')
            //->join('nw_channel channel', 'members.channel_id=channel.id AND (channel.id='.$this->_channelId.' or channel.id_path like "'.$this->_channelIdPath.'%")')
            //->join('cy_game game','members.game_id=game.id AND game.game_kind=1')
            //->field('count(*) AS counts,channel.name,from_unixtime(members.create_time, "%Y-%m-%d" ) AS date,channel.id')
            //->where($where)
            //->where("game.is_default = 0")
            //->where(['channel.level' => 3 ])
            //->group('channel.id,date')
            //->order('date')
            //->select();
            $field = ['id', 'name'];
            $zong = $this->nw_channel
                ->field($field)
                //->where($wherea)
                //->where('parent_id = '.$this->_channelId.'')
                ->where('id_path', 'like', '%' . $this->_channelId . '%')
                ->paginate(['list_rows' => $list_rows, 'page' => $page])
                ->each(function ($item) {
                    $zuotian['reg_time'] = [['>=', strtotime('-1 days 0:0:0')], ['<=', strtotime('-1 days 23:59:59')]];
                    $jint['reg_time'] = [['>=', strtotime('0:0:0')], ['<=', time()]];
                    //今天
                    $item['tday_counts'] = $this->membersModel
                        ->where('channel_id = ' . $item['id'] . '')
                        ->where($jint)
                        ->count();
                    //昨天
                    $item['ytay_counts'] = $this->membersModel
                        ->where('channel_id = ' . $item['id'] . '')
                        ->where($zuotian)
                        ->count();
                    return $item;
                })
                // ->select();
                ->toArray();
            //foreach ($zong as $k =>$v){
            //$list[]  = $this->membersModel->field('id,username')
            //     ->where('channel_id = '.$v['id'].'')
            //->where($where)
            //   ->select();
            // }


            return json(['data' => $zong, 'code' => 20000, 'msg' => '获取数据成功']);


        }
        if ($type == 'sum') {
            //已接单的游戏才能展示2022-08-03
            $where['game.id'] = ['in', model('game')->getTakingGame($this->_channelId)];
            $where['pay.create_time'] = [['>=', strtotime('-100 days 0:0:0')], ['<=', strtotime(date('Y-m-d 23:59:59'))]];
            $list = Db::name('cy_pay')->alias('pay')
                ->join('nw_channel channel', 'pay.channel_id=channel.id AND (channel.id=' . $this->_channelId . ' or channel.id_path like "' . $this->_channelIdPath . '%")')
                ->join('cy_game game', 'pay.gameid=game.id AND game.game_kind=1')
                ->field('sum(pay.pay_amount) as counts,channel.name,from_unixtime(pay.create_time, "%Y-%m-%d" ) AS date,channel.id')
                ->where($where)
                ->where("game.is_default = 0")
                ->where(['channel.level' => 3, 'pay.status' => 1])
                ->group('channel.id,date')
                ->order('date')
                ->select();

            $out = [];
            foreach ($list as $key => $value) {
                $out[$value['id']]['name'] = $value['name'];
                empty($out[$value['id']]['tday_counts']) && $out[$value['id']]['tday_counts'] = 0;
                empty($out[$value['id']]['ytay_counts']) && $out[$value['id']]['ytay_counts'] = 0;
                if ($value['date'] == $t_day) {
                    $out[$value['id']]['tday_counts'] = $value['counts'];
                } elseif ($value['date'] == $yse_day) {
                    $out[$value['id']]['ytay_counts'] = $value['counts'];
                }
            }

            $last_names = array_column($out, 'tday_counts');
            array_multisort($last_names, SORT_DESC, $out);

            $curpage = input('page') ? input('page') : 1;//当前第x页，有效值为：1,2,3,4,5...
            $listRow = 5;//每页5行记录
            $dataTo = array();
            $dataTo = array_chunk($out, $listRow);
            $showdata = array();
            if ($dataTo) {
                $showdata = $dataTo[$curpage - 1];
            } else {
                $showdata = null;
            }

            $p = Bootstrap::make($showdata, $listRow, $curpage, count($out), false, [
                'var_page' => 'page',
                'path' => url('url'),//这里根据需要修改url
                'fragment' => '',
            ]);

            return json(['data' => $p, 'code' => 20000, 'msg' => '获取数据成功']);
        }


    }


    /****************************************************************************************************************/

    /**
     * 订单用户列表
     */
    public function getOrderList()
    {
        $forbid_channel_ids = Db::table("nw_channel_view_limit")->column("channel_id");
        if (in_array($this->_channelId, $forbid_channel_ids)) {
            return json(['data' => '', 'code' => 10032, 'msg' => '暂时没有权限查看订单数据']);
        }
        $download = input('download');
        $list_rows = input('pageSize', 10);
        $page = input('page', 1);
        $where = $this->_getCondition('pay', 1);

        $status = input('status');
        $where['pay.status'] = 1;

        if($status == 2) {
            $where['pay.status'] = 2;
        }

        //已接单的游戏才能展示2022-08-03
        $where['game.id'] = ['in', model('game')->getTakingGame($this->_channelId)];
        switch ($download) {
            case '1':
                $sql = Db::table('cy_pay')->alias('pay')
                    ->join('cy_game game', 'pay.gameid = game.id')
                    ->join('nw_channel channel', 'pay.channel_id=channel.id AND (channel.id=' . $this->_channelId . ' or channel.id_path like "' . $this->_channelIdPath . '%")')
                    //  ->join('nw_game_server server', 'pay.gameid=server.game_id And pay.serverid = server.serverid','left')
                    ->field('pay.id,game.name as game_name,game.id as game_id,pay.username,pay.rolename,pay.pay_amount as amount,pay.paytype,pay.orderid,FROM_UNIXTIME(pay.create_time) as create_time,channel.name as channel_name,pay.servername,pay.status,pay.coupon_amount,pay.real_coin,pay.pay_amount')
                    ->where($where)
                    ->where("game.is_default = 0")
                    ->order('pay.id desc')
                    ->fetchSql(true)
                    ->select();
//                if ((new MakeReport())->addTask('guild.orderListExport', $sql, 'cps'.$this->_adminId)){
                if ((new MakeReportGo())->addTask('guild.orderListExport', $sql, 'cps' . $this->_adminId)) {
                    return json(['data' => '', 'code' => 20000, 'msg' => '报表生成的任务已经提交, 报表生成完成后，会及时通知您，请耐心稍等']);
                } else {
                    return json(['data' => '', 'code' => 20013, 'msg' => '报表生成任务不可重复提交，如遇到无法导出情况，建议修改查询条件解除当前状态，提交重新生成报表任务！']);
                }
                break;

            default:
                $list = Db::table('cy_pay')->alias('pay')
                    ->join('cy_game game', 'pay.gameid = game.id')
                    ->join('nw_channel channel', 'pay.channel_id=channel.id AND (channel.id=' . $this->_channelId . ' or channel.id_path like "' . $this->_channelIdPath . '%")')
                    //  ->join('nw_game_server server', 'pay.gameid=server.game_id And pay.serverid = server.serverid','left')
                    ->field('pay.id,game.name as game_name,game.id as game_id,pay.username,pay.rolename,pay.pay_amount as amount,pay.paytype,pay.orderid,FROM_UNIXTIME(pay.create_time) as create_time,channel.name as channel_name,pay.servername,pay.status,pay.coupon_amount,pay.real_coin,pay.pay_amount')
                    ->where($where)->order('pay.id desc')
                    ->where("game.is_default = 0")
                    ->paginate(['list_rows' => $list_rows, 'page' => $page])->toArray();
/*                foreach ($list['data'] as $key => $item) {
                    if (in_array($item['paytype'], ['zfb-wap', 'zfbsmzf', 'xzzfbzf', 'old-zfb-wap'])) {
                        $list['data'][$key]['paytype'] = "zfb";
                    } else if (in_array($item['paytype'], ['wx-wap', 'wxsmzf'])) {
                        $list['data'][$key]['paytype'] = "wxpay";
                    }
                    //$list['data'][$key]['orderid'] = $item['orderid'] . "--" . ($item['status'] == 1 ? "支付成功" : "待支付");
                }*/
                $total_amount = Db::table('cy_pay')->alias('pay')
                    ->join('cy_game game', 'pay.gameid = game.id')
                    ->join('nw_channel channel', 'pay.channel_id=channel.id AND (channel.id=' . $this->_channelId . ' or channel.id_path like "' . $this->_channelIdPath . '%")')
                    ->where($where)
                    ->where("game.is_default = 0")
                    ->value('sum(pay.pay_amount)');
//                $gameList = model('Common/Game')->getAllByCondition('id,name',['game_kind'=>1,'cooperation_status'=>['neq',0]],'','self');
                $gameList = $this->getTakingGameList();
                $channelList = Db::table('nw_channel')->field('id,name')->where(['level' => 3, 'id_path' => ['LIKE', '%,' . $this->_channelId . ',%']])->select();
                if(!empty($list['per_page'])){
                    $list['per_page'] = (int)$list['per_page'];
                }
                return json(['data' => $list, 'code' => 20000, 'msg' => '获取数据成功', 'gameList' => $gameList, 'channelList' => $channelList, 'total_amount' => $total_amount]);
                break;
        }
    }

    /**
     * 订单用户列表(汇总)
     */
    public function getSumOrderList()
    {
        $forbid_channel_ids = Db::table("nw_channel_view_limit")->column("channel_id");
        if (in_array($this->_channelId, $forbid_channel_ids)) {
            return json(['data' => '', 'code' => 10032, 'msg' => '暂时没有权限查看订单汇总数据']);
        }
        $download = input('download');//执行类型
        $list_rows = input('pageSize', 10);
        $page = input('page', 1);
        $where = $this->_getCondition('pay', 1);//条件选择func
        //已接单的游戏才能展示2022-08-03
        $where['game.id'] = ['in', model('game')->getTakingGame($this->_channelId)];

        $status = input('status');
        $where['pay.status'] = 1;

        if($status == 2) {
            $where['pay.status'] = 2;
        }

        switch ($download) {
            case '1'://py执行异步导出
                $sql = Db::table('cy_pay')->alias('pay')
                    ->join('cy_game game', 'pay.gameid = game.id')
                    ->join('nw_channel channel', 'pay.channel_id=channel.id AND (channel.id=' . $this->_channelId . ' or channel.id_path like "' . $this->_channelIdPath . '%")')
                    ->field('sum(pay.pay_amount) as money,round(sum(pay.pay_amount)-sum(pay.coupon_amount),2) as actual_amount,sum(pay.coupon_amount) as coupon_amount,game.name as game_name,game.id as game_id,pay.rolename,pay.username,channel.name as channel_name')
                    ->where("game.is_default = 0")
                    ->where($where)
                    ->order('pay.create_time desc')
                    ->group('userid,gameid,roleid')
                    ->fetchSql(true)
                    ->select();
//                if ((new MakeReport())->addTask('guild.orderTotalExport', $sql, 'cps'.$this->_adminId)){
                if ((new MakeReportGo())->addTask('guild.orderTotalExport', $sql, 'cps' . $this->_adminId)) {
                    return json(['data' => '', 'code' => 20000, 'msg' => '报表生成的任务已经提交, 报表生成完成后，会及时通知您，请耐心稍等']);
                } else {
                    return json(['data' => '', 'code' => 20013, 'msg' => '报表生成任务不可重复提交，如遇到无法导出情况，建议修改查询条件解除当前状态，提交重新生成报表任务！']);
                }
                break;
            default://列表展示
                $list = Db::table('cy_pay')->alias('pay')
                    ->join('cy_game game', 'pay.gameid = game.id')
                    ->join('nw_channel channel', 'pay.channel_id=channel.id AND (channel.id=' . $this->_channelId . ' or channel.id_path like "' . $this->_channelIdPath . '%")')
                    ->field('sum(pay.pay_amount) as money,round(sum(pay.pay_amount)-sum(pay.coupon_amount),2) as actual_amount,sum(pay.coupon_amount) as coupon_amount,game.name as game_name,game.id as game_id,pay.rolename,pay.username,channel.name as channel_name')
                    ->where("game.is_default = 0")
                    ->where($where)
                    ->order('pay.create_time desc')
                    ->group('userid,gameid,roleid')
                    ->paginate(['list_rows' => $list_rows, 'page' => $page])->toArray();

                $total_info = Db::table('cy_pay')->alias('pay')
                    ->join('cy_game game', 'pay.gameid = game.id')
                    ->join('nw_channel channel', 'pay.channel_id=channel.id AND (channel.id=' . $this->_channelId . ' or channel.id_path like "' . $this->_channelIdPath . '%")')
                    ->field('sum(pay.pay_amount) as money,round(sum(pay.pay_amount)-sum(pay.coupon_amount),2) as actual_amount,sum(pay.coupon_amount) as coupon_amount')
                    ->where("game.is_default = 0")
                    ->where($where)
                    ->find();

                return json(['data' => $list, 'total_amount' => $total_info['money'], 'actual_amount' => $total_info['actual_amount'], 'coupon_amount' => $total_info['coupon_amount'], 'code' => 20000, 'msg' => '获取数据成功']);
                break;
        }
    }

    /**
     * 推广员数据列表(汇总)
     */
    public function getSumAgentList()
    {
        $download = input('download');
        $list_rows = input('pageSize', 10);
        $page = input('page', 1);
        $where = $this->_getCondition('pay', 1);
        $this->membersModel = new MembersModel;
        //if ($this->_channelLevel == 3 || $this->_channelLevel == 0) {
        //  return json(['data'=>'','code'=>10032,'msg'=>'暂时没有权限查看推广员数据']);
        // }
        $forbid_channel_ids = Db::table("nw_channel_view_limit")->column("channel_id");

        if (in_array($this->_channelId, $forbid_channel_ids)) {
            return json(['data' => '', 'code' => 10032, 'msg' => '暂时没有权限查看推广员数据']);
        }

        //已接单的游戏才能展示2022-08-03
        $where['game.id'] = ['in', model('game')->getTakingGame($this->_channelId)];
        switch ($download) {
            case '1':
                $sql = Db::table('cy_pay')->alias('pay')
                    ->group('pay.channel_id,pay.gameid')
                    ->field('sum(pay.pay_amount) as money,round(sum(pay.pay_amount)-sum(pay.coupon_amount),2) as actual_amount,sum(pay.coupon_amount) as coupon_amount,game.name as game_name,channel.name as channel_name,channel.id as channel_id,parent.name as xinzeng')
                    ->join('cy_game game', 'pay.gameid = game.id')
                    ->join('nw_channel channel', 'pay.channel_id=channel.id AND (channel.id=' . $this->_channelId . ' or channel.id_path like "' . $this->_channelIdPath . '%")')
                    ->join('nw_channel parent', 'channel.parent_id = parent.id and parent.level = 2', 'left')
                    ->where("game.is_default = 0")
                    ->where("pay.status =1")
                    ->where($where)->order('pay.create_time desc')
                    ->fetchSql(true)
                    ->select();

//                if ((new MakeReport())->addTask('guild.sumAgentExport', $sql, 'cps'.$this->_adminId)){
                if ((new MakeReportGo())->addTask('guild.sumAgentExport', $sql, 'cps' . $this->_adminId)) {
                    return json(['data' => '', 'code' => 20000, 'msg' => '报表生成的任务已经提交, 报表生成完成后，会及时通知您，请耐心稍等']);
                } else {
                    return json(['data' => '', 'code' => 20013, 'msg' => '报表生成任务不可重复提交，如遇到无法导出情况，建议修改查询条件解除当前状态，提交重新生成报表任务！']);
                }
                break;
            default:
                //$where = $this->_getCondition('pay',1);
                $list = Db::table('cy_pay')->alias('pay')
                    ->group('pay.channel_id,pay.gameid')
                    ->field('sum(pay.pay_amount) as money,round(sum(pay.pay_amount)-sum(pay.coupon_amount),2) as actual_amount,sum(pay.coupon_amount) as coupon_amount,game.name as game_name,userid as member_id,rolename as rolename,game.id as game_id,channel.name as channel_name,channel.id as channel_id,parent.name as p_name')
                    ->join('cy_game game', 'pay.gameid = game.id')
                    ->join('nw_channel channel', 'pay.channel_id=channel.id AND (channel.id=' . $this->_channelId . ' or channel.id_path like "' . $this->_channelIdPath . '%")')
                    ->join('nw_channel parent', 'channel.parent_id = parent.id and parent.level = 2', 'left')
                    ->where("game.is_default = 0")
                    ->where("pay.status =1")
                    ->where($where)->order('pay.create_time desc')
                    ->order('pay.gameid desc')
                    ->paginate(['list_rows' => $list_rows, 'page' => $page])
                    //->each(function($item){

                    //$tj = Db::name('nw_member_game_server')->alias('mgs')
                    //->join('cy_game game', 'mgs.game_id = game.id','left')
                    //->join('cy_members members', 'mgs.member_id = members.id','left')
                    //->join('nw_channel channel', 'mgs.channel_id=channel.id AND (channel.id='.$item['channel_id'].' or channel.id_path like "'.$this->_channelIdPath.'%")')
                    //->field('mgs.mgs_id,game.name as game_name,members.username,mgs.servername,mgs.rolename,mgs.rolelevel,mgs.ip,mgs.imeil,FROM_UNIXTIME( members.reg_time ) AS create_time,FROM_UNIXTIME( mgs.update_time) AS login_time,FROM_UNIXTIME( mgs.create_time) AS reg_time,channel.NAME AS channel_name ')
                    //->where('channel.id='.$item['channel_id'].'')
                    //->where('game_id='.$item['game_id'].'')
                    //->where($where1)
                    //->where("game.is_default = 0")
                    //->group("members.username")
                    //->order('mgs.mgs_id desc')
                    //->fetchSql(true)
                    //->select();

                    //$item['p_name'] = $tj;
                    //return $item;
                    //  })
                    ->toArray();
                $start_time = input('order_start_time');
                $end_time = input('order_end_time');
                $gameid = input('gameid');

                if (!empty($start_time)) {
                    $reg['reg_time'] = [
                        ['>=', strtotime($start_time)],
                        ['<=', strtotime($end_time . ' 23:59:59')],
                    ];

                } else {
                    $reg = [];
                }

                //$where['reg_time'] =$this->getTimeCondition($start_time,$end_time);
                //print_r($where['pay.gameid']);die;
                foreach ($list['data'] as $k => $v) {
                    $condition = [
                        'gameid' => $list['data'][$k]['game_id'],
                        'channel_id' => $list['data'][$k]['channel_id'],
                    ];
                    $zong = $this->membersModel->field('id')
                        ->where($condition)
                        ->where($reg)
                        ->select();
                    $list ['data'][$k]['p_name'] = count($zong);
                }

//                   $gameList = model('Common/Game')->getAllByCondition('id,name',['game_kind'=>1,'cooperation_status'=>['neq',0]],'','self');
                $gameList = $this->getTakingGameList();
                $channelList = Db::table('nw_channel')->field('id,name')->where(['level' => 3, 'id_path' => ['LIKE', '%,' . $this->_channelId . ',%']])->select();
                return json(['data' => $list, 'code' => 20000, 'msg' => '获取数据成功', 'gameList' => $gameList, 'channelList' => $channelList]);
                break;
        }
    }


    /**
     *注册用户统计
     */
    public function getreglist()
    {

        $download = input('download');
        $list_rows = input('pageSize', 20);
        $page = input('page', 1);
        $type = input('type', 1);

        $where = $this->_getCondition('members', 1);
        $where['reg_time'] = [['>=', strtotime('-1 days 0:0:0')], ['<=', strtotime(date('Y-m-d 23:59:59'))]];

        $field = ['id', 'name'];
        $zong = $this->nw_channel
            ->field($field)
            //->where('parent_id = '.$this->_channelId.'')
            ->where('id_path', 'like', '%' . $this->_channelId . '%')
            ->paginate(['list_rows' => $list_rows, 'page' => $page])
            ->each(function ($item) {
                $zuotian['reg_time'] = [['>=', strtotime('-1 days 0:0:0')], ['<=', strtotime('-1 days 23:59:59')]];
                $jint['reg_time'] = [['>=', strtotime('0:0:0')], ['<=', time()]];
                $list_rows = input('pageSize', 20);
                $page = input('page', 1);
                $item['username'] = $this->membersModel
                    ->where('id = ' . $item['id'] . '')
                    ->value('username');
                $item['username'] = $this->membersModel
                    ->where('id = ' . $item['id'] . '')
                    ->value('username');
                //今天
                $item['tday_counts'] = $this->membersModel
                    ->where('channel_id = ' . $item['id'] . '')
                    ->where($jint)
                    ->count();
                //昨天
                $item['ytay_counts'] = $this->membersModel
                    ->where('channel_id = ' . $item['id'] . '')
                    ->where($zuotian)
                    ->count();
                //总注册量
                $item['total_counts'] = $this->membersModel
                    ->where('channel_id = ' . $item['id'] . '')
                    //->where($zuotian)
                    ->count();
                return $item;
            })
            ->toArray();
        return json(['data' => $zong, 'code' => 20000, 'msg' => '获取数据成功']);
    }

    /**
     *注册用户统计详情
     */
    public function getregdetails()
    {


        $download = input('download');
        $list_rows = input('pageSize', 20);
        $page = input('page', 1);
        $type = input('type', 1);
        $aid = input('aid');
        $where = $this->_getCondition('reglist', 1);

        $list = $this->membersModel
            ->field('id,username,channel_id,gameid,reg_time,login_time')
            ->where('channel_id = ' . $aid . '')
            ->where($where)
            ->order("reg_time desc")
            ->paginate(['list_rows' => $list_rows, 'page' => $page])
            ->toArray();
        foreach ($list['data'] as $k => $v) {
            $list['data'][$k]['reg_time'] = date('Y-m-d H:i:s', $v['reg_time']);
            $list['data'][$k]['login_time'] = date('Y-m-d H:i:s', $v['login_time']);
            $list['data'][$k]['gameid'] = Db::table('cy_game')->where('id', $v['gameid'])->value('name');
            $list['data'][$k]['channel_id'] = Db::table('nw_channel')->where('id', $v['channel_id'])->value('name');
        }
//            $gameList = model('Common/Game')->getAllByCondition('id,name',['game_kind'=>1,'cooperation_status'=>['neq',0]],'','self');
        $gameList = $this->getTakingGameList();
        $channelList = Db::table('nw_channel')->field('id,name')->where(['id_path' => ['LIKE', '%,' . $this->_channelId . ',%']])->select();
        return json(['data' => $list, 'code' => 20000, 'msg' => '获取数据成功', 'gameList' => $gameList, 'channelList' => $channelList]);
    }

    /**
     * 子会长流水数据列表(汇总)
     */
    public function getSumChlidList()
    {
        $list_rows = input('pageSize', 10);
        $page = input('page', 1);
        $where = $this->_getCondition('chlid', 1);
        $order_start_time = input('order_start_time');
        $order_end_time = input('order_end_time');
        $download = input('download');
        if (empty($order_start_time) || empty($order_end_time)) {
            return json(['data' => '', 'code' => 20040, 'msg' => '请选择开始日期和结束日期']);
        } else if (date('Y-m-d', strtotime($order_start_time)) <> $order_start_time || date('Y-m-d', strtotime($order_end_time)) <> $order_end_time) {
            return json(['data' => '', 'code' => 20040, 'msg' => '开始/结束日期输入不合法']);
        }
        //已接单的游戏才能展示2022-08-03
        $where['g.id'] = ['in', model('game')->getTakingGame($this->_channelId)];
        switch ($download) {
            case '1':
                $time = $order_start_time . ' - ' . $order_end_time;
                $sql = Db::table('nw_channel')->alias('c1')
                    ->group('p.gameid,c2.id')
                    ->field('p.gameid,c2.id,c2.name,count(*) as total_cnt,sum(p.pay_amount) as total_amount,sum(p.pay_amount-p.coupon_amount) as actual_amount,sum(p.coupon_amount) as coupon_amount,g.name as gname,"' . $time . '" as time')
                    ->join('nw_channel c2', 'c1.parent_id = c2.id AND c2.LEVEL = 2 AND c2.id_path LIKE "' . $this->_channelIdPath . '%"')
                    ->join('cy_pay p', 'c1.id = p.channel_id AND p.status = 1')
                    ->join('cy_game g', 'p.gameid = g.id')
                    ->where("g.is_default = 0")
                    ->where(['c1.level' => 3, 'c1.id_path' => ['LIKE', $this->_channelIdPath . '%']])
                    ->where($where)
                    ->fetchSql(true)
                    ->select();
//                if ((new MakeReport())->addTask('guild.sumChlidExport', $sql, 'cps'.$this->_adminId)){
                if ((new MakeReportGo())->addTask('guild.sumChlidExport', $sql, 'cps' . $this->_adminId)) {
                    return json(['data' => '', 'code' => 20000, 'msg' => '报表生成的任务已经提交, 报表生成完成后，会及时通知您，请耐心稍等']);
                } else {
                    return json(['data' => '', 'code' => 20013, 'msg' => '报表生成任务不可重复提交，如遇到无法导出情况，建议修改查询条件解除当前状态，提交重新生成报表任务！']);
                }
                break;

            default:
                $list = Db::table('nw_channel')->alias('c1')
                    ->group('p.gameid,c2.id')
                    ->field('p.gameid,c2.id,c2.name,count(*) as total_cnt,sum(p.pay_amount) as total_amount,sum(p.pay_amount-p.coupon_amount) as actual_amount,sum(p.coupon_amount) as coupon_amount,g.name as gname')
                    ->join('nw_channel c2', 'c1.parent_id = c2.id AND c2.LEVEL = 2 AND c2.id_path LIKE "' . $this->_channelIdPath . '%"')
                    ->join('cy_pay p', 'c1.id = p.channel_id AND p.status = 1')
                    ->join('cy_game g', 'p.gameid = g.id')
                    ->where("g.is_default = 0")
                    ->where(['c1.level' => 3, 'c1.id_path' => ['LIKE', $this->_channelIdPath . '%']])
                    ->where($where)
                    ->paginate(['list_rows' => $list_rows, 'page' => $page])->toArray();
//                $gameList = model('Common/Game')->getAllByCondition('id,name',['game_kind'=>1,'cooperation_status'=>['neq',0]],'','self');
                $gameList = $this->getTakingGameList();
                $channelList = Db::table('nw_channel')->field('id,name')->where(['level' => 2, 'id_path' => ['LIKE', '%,' . $this->_channelId . ',%']])->select();
                return json(['data' => $list, 'code' => 20000, 'msg' => '获取数据成功', 'gameList' => $gameList, 'channelList' => $channelList]);
                break;
        }

    }
    /************************************************联盟数据/报表**************************************/

    /**
     * 联盟流水数据列表(汇总)
     */
    public function getSumUnionList()
    {
        $download = input('download');
        if ($this->_channelLevel !== 0) {
            return json(['data' => '', 'code' => 20013, 'msg' => '只有联盟有查看权限!']);
        }
        $list_rows = input('pageSize', 10, 'intval');
        $page = input('page', 1, 'intval');
        $order_start_time = input('order_start_time', '', 'trim');
        $order_end_time = input('order_end_time', '', 'trim');
        $where = $this->_getCondition('chlid', 1);
        if (empty($order_start_time) || empty($order_end_time)) {
            return json(['data' => '', 'code' => 20040, 'msg' => '请选择开始日期和结束日期']);
        } else if (date('Y-m-d', strtotime($order_start_time)) <> $order_start_time || date('Y-m-d', strtotime($order_end_time)) <> $order_end_time) {
            return json(['data' => '', 'code' => 20040, 'msg' => '开始/结束日期输入不合法']);
        }
        switch ($download) {
            case '1':
                $time = $order_start_time . ' 00:00:00 - ' . $order_end_time . ' 23:59:59';
                $sql = Db::table('nw_channel')->alias('c1')
                    ->group('p.gameid,c2.id')
                    ->field('p.gameid,c2.id,c2.name,count(*) as total_cnt,sum(p.amount) as total_amount,g.name as gname,"' . $time . '" as time')
                    ->join('nw_channel c2', 'c1.id_path like CONCAT("' . $this->_channelIdPath . '",c2.id,",%") AND c2.LEVEL = 1 AND c2.id_path LIKE "' . $this->_channelIdPath . '%"')
                    ->join('cy_pay p', 'c1.id = p.channel_id AND p.status = 1')
                    ->join('cy_game g', 'p.gameid = g.id')
                    ->where("g.is_default = 0")
                    ->where(['c1.level' => 3, 'c1.id_path' => ['LIKE', $this->_channelIdPath . '%']])
                    ->where($where)
                    ->fetchSql(true)
                    ->select();
//                if ((new MakeReport())->addTask('guild.sumUnionExport', $sql, 'cps'.$this->_adminId)){
                if ((new MakeReportGo())->addTask('guild.sumUnionExport', $sql, 'cps' . $this->_adminId)) {
                    return json(['data' => '', 'code' => 20000, 'msg' => '报表生成的任务已经提交, 报表生成完成后，会及时通知您，请耐心稍等']);
                } else {
                    return json(['data' => '', 'code' => 20013, 'msg' => '报表生成任务不可重复提交，如遇到无法导出情况，建议修改查询条件解除当前状态，提交重新生成报表任务！']);
                }
                break;
            default:
                $list = Db::table('nw_channel')->alias('c1')
                    ->group('p.gameid,c2.id')
                    ->field('p.gameid,c2.id,c2.name,count(*) as total_cnt,sum(p.amount) as total_amount,g.name as gname')
                    ->join('nw_channel c2', 'c1.id_path like CONCAT("' . $this->_channelIdPath . '",c2.id,",%") AND c2.LEVEL = 1 AND c2.id_path LIKE "' . $this->_channelIdPath . '%"')
                    ->join('cy_pay p', 'c1.id = p.channel_id AND p.status = 1')
                    ->join('cy_game g', 'p.gameid = g.id')
                    ->where("g.is_default = 0")
                    ->where(['c1.level' => 3, 'c1.id_path' => ['LIKE', $this->_channelIdPath . '%']])
                    ->where($where)
                    ->paginate(['list_rows' => $list_rows, 'page' => $page])->toArray();

                $total_amount = Db::table('nw_channel')->alias('c1')
                    ->join('nw_channel c2', 'c1.id_path like CONCAT("' . $this->_channelIdPath . '",c2.id,",%") AND c2.LEVEL = 1 AND c2.id_path LIKE "' . $this->_channelIdPath . '%"')
                    ->join('cy_pay p', 'c1.id = p.channel_id AND p.status = 1')
                    ->join('cy_game g', 'p.gameid = g.id')
                    ->where("g.is_default = 0")
                    ->where(['c1.level' => 3, 'c1.id_path' => ['LIKE', $this->_channelIdPath . '%']])
                    ->where($where)
                    ->value('sum(p.amount)');

//                $gameList = model('Common/Game')->getAllByCondition('id,name',['game_kind'=>1,'cooperation_status'=>['neq',0]],'','self');
                $gameList = $this->getTakingGameList();
                $channelList = Db::table('nw_channel')->field('id,name')->where(['level' => 1, 'id_path' => ['LIKE', '%,' . $this->_channelId . ',%']])->select();
                return json(['data' => $list, 'code' => 20000, 'msg' => '获取数据成功', 'gameList' => $gameList, 'channelList' => $channelList, 'total_amount' => $total_amount]);
                break;
        }

    }

    /**
     * 联盟新增数据列表(汇总)
     */
    public function getAddUnionList()
    {
        $download = input('download', 0, 'intval');
        if ($this->_channelLevel !== 0) {
            return json(['data' => '', 'code' => 20013, 'msg' => '只有联盟有查看权限!']);
        }
        $list_rows = input('pageSize', 10);
        $page = input('page', 1);
        $reg_start_time = input('reg_start_time');
        $reg_end_time = input('reg_end_time');
        $where = $this->_getCondition('uniadd', 1);
        if (empty($reg_start_time) || empty($reg_end_time)) {
            return json(['data' => '', 'code' => 20040, 'msg' => '请选择开始日期和结束日期']);
        } else if (date('Y-m-d', strtotime($reg_start_time)) <> $reg_start_time || date('Y-m-d', strtotime($reg_end_time)) <> $reg_end_time) {
            return json(['data' => '', 'code' => 20040, 'msg' => '开始/结束日期输入不合法']);
        }
        if ($download) {
            $time = $reg_start_time . ' - ' . $reg_end_time;
            $sql = Db::name('nw_member_game_server')->alias('mgs')
                ->group('mgs.game_id,b.id')
                ->join('nw_channel c', 'mgs.channel_id=c.id')
                ->join('cy_game g', 'mgs.game_id = g.id')
                ->join('nw_channel b', 'c.id_path like CONCAT("' . $this->_channelIdPath . '",b.id,",%") AND b.LEVEL = 1 AND b.id_path LIKE "' . $this->_channelIdPath . '%"')
                ->field('mgs_id,b.id,b.name,count(*) AS total_cnt,g.NAME AS gname,"' . $time . '" as time')
                ->where(['c.level' => 3, 'c.id_path' => ['LIKE', $this->_channelIdPath . '%']])
                ->where("g.is_default = 0")
                ->where($where)
                ->order('mgs.mgs_id desc')
                ->fetchSql(true)
                ->select();
//            if ((new MakeReport())->addTask('guild.addUnionExport', $sql, 'cps'.$this->_adminId)){
            if ((new MakeReportGo())->addTask('guild.addUnionExport', $sql, 'cps' . $this->_adminId)) {
                return json(['data' => '', 'code' => 20000, 'msg' => '报表生成的任务已经提交, 报表生成完成后，会及时通知您，请耐心稍等']);
            } else {
                return json(['data' => '', 'code' => 20013, 'msg' => '报表生成任务不可重复提交，如遇到无法导出情况，建议修改查询条件解除当前状态，提交重新生成报表任务！']);
            }
        } else {
            $list = Db::name('nw_member_game_server')->alias('mgs')
                ->group('mgs.game_id,b.id')
                ->join('nw_channel c', 'mgs.channel_id=c.id')
                ->join('cy_game g', 'mgs.game_id = g.id')
                ->join('nw_channel b', 'c.id_path like CONCAT("' . $this->_channelIdPath . '",b.id,",%") AND b.LEVEL = 1 AND b.id_path LIKE "' . $this->_channelIdPath . '%"')
                ->field('mgs_id,b.id,b.name,count(*) AS total_cnt,g.NAME AS gname')
                ->where(['c.level' => 3, 'c.id_path' => ['LIKE', $this->_channelIdPath . '%']])
                ->where("g.is_default = 0")
                ->where($where)
                ->order('mgs.mgs_id desc')
                ->paginate(['list_rows' => $list_rows, 'page' => $page])->toArray();
//            $gameList = model('Common/Game')->getAllByCondition('id,name',['game_kind'=>1,'cooperation_status'=>['neq',0]],'','self');
            $gameList = $this->getTakingGameList();
            $channelList = Db::table('nw_channel')->field('id,name')->where(['level' => 1, 'id_path' => ['LIKE', '%,' . $this->_channelId . ',%']])->select();
            return json(['data' => $list, 'code' => 20000, 'msg' => '获取数据成功', 'gameList' => $gameList, 'channelList' => $channelList]);
        }
    }
    /************************************************end**************************************/
    /**
     * 区服数据列表(汇总)
     */
    public function getSumServerList()
    {
        $forbid_channel_ids = Db::table("nw_channel_view_limit")->column("channel_id");
        if (in_array($this->_channelId, $forbid_channel_ids)) {
            return json(['data' => '', 'code' => 10032, 'msg' => '暂无权限查看区服数据']);
        }
        $list_rows = input('pageSize', 10);
        $page = input('page', 1);
        $gameid = input('gameid', 0, 'intval');//游戏id
        $serverid = input('serverid');//区服id
        $time_start = input('time_start', '', 'trim');//时间起始
        $time_end = input('time_end', '', 'trim');//时间结束
        $where = [];
        $where['g.game_kind'] = 1;
        if (!empty($gameid)) {
            if (in_array($gameid, model('game')->getTakingGame($this->_channelId))) {
                $where['sever.game_id'] = $gameid;
            } else {
                return json(['data' => '', 'code' => 20000, 'msg' => '获取数据成功']);
            }
        } else {
            $where['sever.game_id'] = ['in', model('game')->getTakingGame($this->_channelId)];
        }
        if (!empty($serverid)) {
            $where['sever.serverid'] = $serverid;
        }
        if (!empty($time_start) && !empty($time_end)) {
            $where['sever.create_time'] = ['BETWEEN', [strtotime($time_start), strtotime($time_end)]];
        }

        $list = Db::table('nw_game_server')->alias('sever')
            ->field('sever.game_id,sever.serverid,count( DISTINCT role.member_id ) AS reg_count,g.name as gamename,sever.servername,role.channel_id')
            ->group('sever.game_id,sever.serverid')
            ->join('nw_member_game_server role', 'sever.game_id = role.game_id AND sever.serverid = role.serverid')
            ->join('cy_game g', 'sever.game_id = g.id')
            ->join('nw_channel channel', 'channel.id = role.channel_id AND (channel.id=' . $this->_channelId . ' or channel.id_path like "' . $this->_channelIdPath . '%")')
            ->where($where)
            ->where("g.is_default = 0")
            ->paginate(['list_rows' => $list_rows, 'page' => $page])->toArray();

        foreach ($list['data'] as $key => $value) {
            $res = $this->getOtherItem($value['serverid'], $value['game_id'], $time_start, $time_end);
            $list['data'][$key]['ip_cnt'] = $res['ip_cnt']['ip'];
            $list['data'][$key]['im_cnt'] = $res['im_cnt']['im'];
            $list['data'][$key]['money'] = $res['pay_cnt']['money'];
        }
//        $gameList = model('Common/Game')->getAllByCondition('id,name',['game_kind'=>1,'cooperation_status'=>['neq',0]],'','self');
        $gameList = $this->getTakingGameList();
        // $channelList = Db::table('nw_channel')->field('id,name')->where(['level'=>3,'id_path'=>['LIKE',  $this->_channelId . ',%']])->select();
        return json(['data' => $list, 'code' => 20000, 'msg' => '获取数据成功', 'gameList' => $gameList]);
    }

    /**
     * 游戏角色列表
     * @return [type] [description]
     */
    public function getRoleList()
    {
        $list_rows = input('pageSize', 10);
        $page = input('page', 1);
        $gameid = input('gameid', 0, 'intval');//游戏id
//        $download = input('download', 1, 'intval');
        $download = 1;
        $where = [];
        if (empty($gameid)) {
            return json(['data' => [], 'code' => 10001, 'msg' => '请选择游戏']);
        } else {

            if (in_array($gameid, model('game')->getTakingGame($this->_channelId))) {
                $where['game.id'] = $gameid;
            } else {
                if ($download) {
                    return json(['data' => '', 'code' => 20013, 'msg' => '没有数据可提供下载']);
                } else {
                    return json(['data' => '', 'code' => 20000, 'msg' => '获取数据成功']);
                }
            }
        }

        if (input('username') <> '') {
            $where['user.username'] = ['LIKE', input('username') . '%'];
        }
        if (input('rolename') <> '') {
            $where['info.rolename'] = ['LIKE', input('rolename') . '%'];
        }
        if (!$download) {
            $sql = Db::table('cy_role_info')->alias('info')
                ->join('cy_members user', 'info.userid = user.id')
                ->join('cy_game game', 'game.id = info.gameid')
                ->join('cy_member_channel_game_rel mcgr', 'info.userid = mcgr.member_id and info.gameid=mcgr.game_id')
                ->join('nw_channel channel', 'mcgr.channel_id =  channel.id AND (channel.id=' . $this->_channelId . ' or channel.id_path like "' . $this->_channelIdPath . '%")')
                ->field(['game.name', 'user.username', 'info.serverid', 'info.servername', 'info.roleid', 'info.rolename', 'info.rolelevel', 'info.create_time'])
                ->where($where)
                ->fetchSql(1)
                ->order('info.id', 'desc')->select();
//            if ((new MakeReport())->addTask('guild.getRoleExport', $sql, 'cps'.$this->_adminId)){
            if ((new MakeReportGo())->addTask('guild.getRoleExport', $sql, 'cps' . $this->_adminId)) {
                return json(['data' => '', 'code' => 20000, 'msg' => '报表生成的任务已经提交, 报表生成完成后，会及时通知您，请耐心稍等']);
            } else {
                return json(['data' => '', 'code' => 20013, 'msg' => '报表生成任务不可重复提交，如遇到无法导出情况，建议修改查询条件解除当前状态，提交重新生成报表任务！']);
            }
        } else {
            $roleList = Db::table('cy_role_info')->alias('info')
                ->join('cy_members user', 'info.userid = user.id')
                ->join('cy_game game', 'game.id = info.gameid')
                ->join('cy_member_channel_game_rel mcgr', 'info.userid = mcgr.member_id and info.gameid=mcgr.game_id')
                ->join('nw_channel channel', 'mcgr.channel_id =  channel.id AND (channel.id=' . $this->_channelId . ' or channel.id_path like "' . $this->_channelIdPath . '%")')
                ->field(['game.name', 'user.username', 'info.serverid', 'info.servername', 'info.roleid', 'info.rolename', 'info.rolelevel', ' FROM_UNIXTIME(info.create_time) AS create_time'])
                ->where($where)
                ->order('info.id', 'desc')
                ->paginate(['list_rows' => $list_rows, 'page' => $page])->toArray();
            return json(['data' => $roleList, 'code' => 20000, 'msg' => '获取数据成功']);
        }
    }

    /**
     * Undocumented function
     *
     * @Author yxon 564556921@qq.com
     * @DateTime 2020-11-30
     * @return void
     */
    public function gameList()
    {
//        $gameList = model('Common/Game')->getAllByCondition('id,name',['game_kind'=>1,'cooperation_status'=>['neq',0]],'','self');
        $gameList = $this->getTakingGameList($this->_channelId);
        return json(['gameList' => $gameList, 'code' => 20000, 'msg' => '获取数据成功',]);
    }

    /**
     * [getOtherItem description]
     * @param  [type] $severid [description]
     * @return [type]          [description]
     */
    protected function getOtherItem($severid, $gameid, $time_start = null, $time_end = null)
    {
        $where = [];

        if (!empty($time_start) && !empty($time_end)) {
            $where['server.create_time'] = ['BETWEEN', [strtotime($time_start), strtotime($time_end)]];
        }
        $out = [];
        $out['ip_cnt'] = Db::name('nw_game_server_ip')->alias('server')//ip数量统计
        ->field('count(DISTINCT ip) as ip')
            ->join('nw_channel channel', 'channel.id = server.channel_id AND (channel.id=' . $this->_channelId . ' or channel.id_path like "' . $this->_channelIdPath . '%")')
            ->where($where)->where(['serverid' => $severid, 'game_id' => $gameid])
            ->find();
        $out['im_cnt'] = Db::name('nw_game_server_imeil')->alias('server')//im数量统计
        ->field('count(DISTINCT imeil) as im')
            ->join('nw_channel channel', 'channel.id = server.channel_id AND (channel.id=' . $this->_channelId . ' or channel.id_path like "' . $this->_channelIdPath . '%")')
            ->where($where)->where(['serverid' => $severid, 'game_id' => $gameid])
            ->find();
        if (!empty($time_start) && !empty($time_end)) {
            unset($where['server.create_time']);
            $where['pay.create_time'] = ['BETWEEN', [strtotime($time_start), strtotime($time_end)]];
        }


        $out['pay_cnt'] = Db::name('cy_pay')->alias('pay')
            ->field('COALESCE(sum(pay.pay_amount),0) as money')
            ->join('nw_channel channel', 'channel.id = pay.channel_id AND (channel.id=' . $this->_channelId . ' or channel.id_path like "' . $this->_channelIdPath . '%")')
            ->where($where)->where(['serverid' => $severid, 'gameid' => $gameid, 'pay.status' => 1])
            ->find();
        return $out;
    }

    // 公共搜索条件
    protected function _getCondition($action = null, $game_kind = 1)
    {

        $gameid = input('gameid', 0, 'intval');//游戏id
        $channelid = input('channelid', 0, 'intval');//推广员id
        $username = input('username', '', 'trim');//账号
        $pay_tpye = input('pay_tpye');//支付类型
        $orderid = input('orderid');//订单id
        $serverid = input('serverid');//区服id
        $status = input('status');//订单id
        $rolename = input('rolename', '', 'trim');//账号
        $login_start_time = input('login_start_time', '', 'trim');//登录时间起始
        $login_end_time = input('login_end_time', '', 'trim');//登录时间结束
        $reg_start_time = input('reg_start_time', '', 'trim');//注册时间起始
        $reg_end_time = input('reg_end_time', '', 'trim');//注册时间结束
        $order_start_time = input('order_start_time', '', 'trim');//订单创建时间开始
        $order_end_time = input('order_end_time', '', 'trim');//订单创建时间结束
        $condition = [];
        if ($action == 'members') {
            if (input('username') <> '') {
                $condition['members.username'] = ['LIKE', '%' . $username . '%'];
            }
            $condition['game.game_kind'] = $game_kind;
            if (!empty($channelid)) {
                $condition['mgs.channel_id'] = $channelid;
            }

            if (!empty($serverid)) {
                $condition['mgs.serverid'] = $serverid;
            }

            if (input('rolename') <> '') {
                $condition['mgs.rolename'] = ['LIKE', '%' . $rolename . '%'];;
            }

            if (!empty($gameid)) {
                $condition['mgs.game_id'] = $gameid;
            }

            if (!empty($status)) {
                $condition['mgs.status'] = $status;
            }

            if (!empty($login_start_time) && !empty($login_end_time)) {
                $condition['mgs.update_time'] = ['BETWEEN', [strtotime($login_start_time), strtotime($login_end_time . ' 23:59:59')]];
            }
            if (!empty($reg_start_time) && !empty($reg_end_time)) {
                $condition['members.reg_time'] = ['BETWEEN', [strtotime($reg_start_time), strtotime($reg_end_time . ' 23:59:59')]];
            }
        } else if ($action == 'pay') {
            if (request()->action() == "getSumOrderList") {
                $condition['pay.status'] = 1;
            }
            if (!empty($gameid)) {
                $condition['pay.gameid'] = $gameid;
            }
            $condition['game.game_kind'] = $game_kind;
            if (!empty($username) || $username === '0') {
                $condition['pay.username'] = ['LIKE', '%' . $username . '%'];
            }

            if (!empty($serverid)) {
                $condition['pay.serverid'] = $serverid;
            }

            if (!empty($pay_tpye)) {
                if ($pay_tpye == 'wx') {
                    $condition['pay.paytype'] = ['IN', ['wxpay-h5', 'ybzf_wxmp_h5', 'coin-wxpay-h5', 'coin-ybzf_wxmp_h5', 'mix-wxpay-h5', 'mix-ybzf_wxmp_h5']];
                } else if ($pay_tpye == 'zfb') {
                    $condition['pay.paytype'] = ['IN', ['zfb', 'old-zfb-wap', 'coin-zfb', 'old-zfb-wap', 'mix-zfb', 'mix-old-zfb-wap']];
                } else if ($pay_tpye == 'coin'){
                    $condition['pay.paytype'] = ['IN', ['coinpay', 'coin-zfb', 'coin-old-zfb-wap', 'coin-wxpay-h5', 'coin-ybzf_wxmp_h5']];
                } else if ($pay_tpye == 'coupon'){
                    $condition['pay.paytype'] = ['IN', ['coupon', 'mix-zfb', 'mix-old-zfb-wap', 'mix-wxpay-h5', 'mix-ybzf_wxmp_h5']];
                }
            }

            if (!empty($channelid)) {
                $condition['pay.channel_id'] = $channelid;
            }

            if (!empty($orderid)) {
                $condition['pay.orderid'] = ['LIKE', '%' . $orderid . '%'];
            }

            if (!empty($status)) {

                if ($status == '2') {
                    //$condition['pay.paytype'] = ['IN',['wxpay-h5','wxpay-h5-mihua','wxpay-h5-kj','wxpay-h5-sumpay']];
                    $condition['pay.status'] = ['IN', ['0']];
                } else if ($status == '1') {
                    //$condition['pay.paytype'] = ['IN',['zfb','zfb-h5-mihua','zfb-h5-kj']];
                    $condition['pay.status'] = ['IN', ['1']];
                } else {
                    $condition['pay.status'] = ['IN', ['0', '1', '2']];
                }
            }

            if (!empty($order_start_time) && !empty($order_end_time)) {
                $condition['pay.create_time'] = ['BETWEEN', [strtotime($order_start_time), strtotime($order_end_time . ' 23:59:59')]];
            }
        } else if ($action == 'chlid') {
            if (!empty($gameid)) {
                $condition['g.id'] = $gameid;
            }
            $condition['g.game_kind'] = $game_kind;
            if (!empty($channelid)) {
                $condition['c2.id'] = $channelid;
            }

            if (!empty($order_start_time) && !empty($order_end_time)) {
                $condition['p.create_time'] = ['BETWEEN', [strtotime($order_start_time), strtotime($order_end_time . ' 23:59:59')]];
            }
        } else if ($action == 'add') {
            if (!empty($gameid)) {
                $condition['g.id'] = $gameid;
            }
            $condition['g.game_kind'] = $game_kind;
            if (!empty($channelid)) {
                $condition['b.id'] = $channelid;
            }

            if (!empty($reg_start_time) && !empty($reg_end_time)) {
                $condition['member.mcgr_createtime'] = ['BETWEEN', [strtotime($reg_start_time), strtotime($reg_end_time . ' 23:59:59')]];
            }
        } else if ($action == 'reglist') {
            if (!empty($gameid)) {
                $condition['gameid'] = $gameid;
            }
            if (!empty($reg_start_time) && !empty($reg_end_time)) {
                $condition['reg_time'] = ['BETWEEN', [strtotime($reg_start_time), strtotime($reg_end_time . ' 23:59:59')]];
            }
        } else if ($action == 'uniadd') {
            if (!empty($gameid)) {
                $condition['g.id'] = $gameid;
            }
            $condition['g.game_kind'] = $game_kind;
            if (!empty($channelid)) {
                $condition['b.id'] = $channelid;
            }

            if (!empty($reg_start_time) && !empty($reg_end_time)) {
                $condition['mgs.create_time'] = ['BETWEEN', [strtotime($reg_start_time), strtotime($reg_end_time . ' 23:59:59')]];
            }
        }
        return $condition;
    }


    public function getTakingGameList()
    {

        return model('Common/Game')->getAllByCondition('id,name', ['game_kind' => 1, 'cooperation_status' => ['neq', 0], 'id' => ['in', model('game')->getTakingGame($this->_channelId)]], '', 'self');

    }
}