new_sdk_wu/www/new_sdk/application/mcpsapi/controller/Member.php

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

namespace app\mcpsapi\controller;

use think\Db;
use think\Exception;
use app\common\logic\Member as MemberService;
use app\common\library\MakeReport;

class Member extends Guild
{
     protected function _initialize()
    {
        parent::_initialize();
        $this->membersModel = Db::name('cy_members');
        $this->membersGameModel = Db::name('nw_member_game_server');
    }
    /**
     * 注册用户列表
     */
    public function getMemberList()
    {
        $download  = input('download');
        $list_rows = input('pageSize',10);
        $page      = input('page',1);
        $type      = input('type',1);
        $where = $this->_getCondition('members',2);
        //create_time 账号注册时间
        //reg_time 创角时间
        //login_time 最近登录时间
        switch ($download) {
            case '1':
                if ($type == 2) {
                    $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('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')
                        ->where($where)
                        ->where("game.is_default = 0")
                        ->order('mcg.mcgr_id desc,mgs.mgs_id desc')
                        ->fetchSql(true)
                        ->select();
                }else{
                    $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('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($where)
                    ->where("game.is_default = 1")
                    ->order('mgs.mgs_id desc')
                    ->fetchSql(true)
                    ->select();
                }
                if ((new MakeReport())->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) {
                   $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('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')
                    ->where($where)
                    ->where("game.is_default = 0")
                    ->order('mcg.mcgr_id desc,mgs.mgs_id desc')
                    ->paginate(['list_rows'=>$list_rows,'page'=>$page])->toArray();
                }else{
                    $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('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($where)
                    ->where("game.is_default = 0")
                    ->order('mgs.mgs_id desc')
                    ->paginate(['list_rows'=>$list_rows,'page'=>$page])->toArray();
                }
            $gameList = model('Common/Game')->getAllByCondition('id,name',['game_kind'=>2,'cooperation_status'=>['neq',0]],'','self');
            $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()
    {
        $type  = input('type');
        $where = [];
        $weekormonth = [];
        switch ($type) {
            case 'month':
                $formdata = '%Y-%m';
                $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]['注册量'] = 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=2')
            ->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 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()
    {
        $type  = input('type');
        $where = [];
        $weekormonth = [];
        switch ($type) {
            case 'month':
                $formdata = '%Y-%m';
                $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]['流水'] = 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=2')
            ->field('from_unixtime( pay.create_time, "'.$formdata.'" ) AS date,sum(pay.amount) as counts')
            ->where($where)
            ->where("game.is_default = 0")
            ->where(['pay.status' => 1])
            ->group('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()
    {
        $type  = input('type');
        $where = [];
        $t_day= date('Y-m-d');//今日日期
        $yse_day = date("Y-m-d",strtotime("-1 day"));//昨日日期

        if ($type == 'agent') {
            $where['members.create_time'] = [['>=', strtotime('-1 days 0:0:0')],['<=', strtotime(date('Y-m-d 23:59:59'))]];
            $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=2')
            ->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('date,channel.id')
            ->order('date')
            ->select();
        }
        if ($type == 'sum') {
            $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=2')
            ->field('sum(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);
        return json(['data'=>$out,'code'=>20000,'msg'=>'获取数据成功']);
    }

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

    /**
     * 订单用户列表
     */
    public function getOrderList()
    {
        $download = input('download');
        $list_rows         = input('pageSize',10);
        $page              = input('page',1);
        $where = $this->_getCondition('pay',2);
        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,pay.username,pay.rolename,pay.amount,pay.paytype,pay.orderid,FROM_UNIXTIME(pay.create_time) as create_time,channel.name as channel_name,pay.servername')
                    ->where($where)
                    ->where("game.is_default = 0")
					->order('pay.id desc')
                    ->fetchSql(true)
                    ->select();
                if ((new MakeReport())->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,pay.username,pay.rolename,pay.amount,pay.paytype,pay.orderid,FROM_UNIXTIME(pay.create_time) as create_time,channel.name as channel_name,pay.servername')
                ->where("game.is_default = 0")
                ->where($where)->order('pay.id desc')
                ->paginate(['list_rows'=>$list_rows,'page'=>$page])->toArray();
                $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.amount)');
                $gameList = model('Common/Game')->getAllByCondition('id,name',['game_kind'=>2,'cooperation_status'=>['neq',0]],'','self');
                $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,'total_amount'=>$total_amount]);
                break;
        }
    }

    /**
     * 订单用户列表(汇总)
     */
    public function getSumOrderList()
    {
        $download  = input('download');//执行类型
        $list_rows = input('pageSize',10);
        $page      = input('page',1);
        $where = $this->_getCondition('pay',2);//条件选择func
        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.amount) as money,game.name as game_name,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)){
                    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.amount) as money,game.name as game_name,pay.rolename,pay.username,pay.rolename,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();
                return json(['data'=>$list,'code'=>20000,'msg'=>'获取数据成功']);
            break;
        }
    }

    /**
     * 推广员数据列表(汇总)
     */
    public function getSumAgentList()
    {
        $download = input('download');
        $list_rows         = input('pageSize',10);
        $page               = input('page',1);
        $where = $this->_getCondition('pay',2);
        if ($this->_channelLevel == 3 || $this->_channelLevel == 0) {
            return json(['data'=>'','code'=>10032,'msg'=>'暂时没有权限查看推广员数据']);
        }
        switch ($download) {
            case '1':
                $sql = Db::table('cy_pay')->alias('pay')
                    ->group('pay.channel_id,pay.gameid')
                    ->field('sum(pay.amount) as money,game.name as game_name,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($where)->order('pay.create_time desc')
                    ->fetchSql(true)
                    ->select();
                if ((new MakeReport())->addTask('guild.sumAgentExport', $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')
                    ->group('pay.channel_id,pay.gameid')
                    ->field('sum(pay.amount) as money,game.name as game_name,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($where)->order('pay.create_time desc')
                    ->order('pay.gameid desc')
                    ->paginate(['list_rows'=>$list_rows,'page'=>$page])->toArray();
                $gameList = model('Common/Game')->getAllByCondition('id,name',['game_kind'=>2,'cooperation_status'=>['neq',0]],'','self');
                $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 getSumChlidList()
    {
        $list_rows         = input('pageSize',10);
        $page               = input('page',1);
        $where = $this->_getCondition('chlid',2);
        $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'=>'开始/结束日期输入不合法']);
        }
        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.amount) as total_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)){
                    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.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'=>2,'cooperation_status'=>['neq',0]],'','self');
                $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',2);
        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)){
                    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'=>2,'cooperation_status'=>['neq',0]],'','self');
                $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',2);
        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("g.is_default = 0")
                ->where(['c.level' => 3,'c.id_path'=>['LIKE', $this->_channelIdPath.'%']])
                ->where($where)
                ->order('mgs.mgs_id desc')
                ->fetchSql(true)
                ->select();
            if ((new MakeReport())->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'=>2,'cooperation_status'=>['neq',0]],'','self');
            $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()
    {
        $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'] = 2;
        if (! empty($gameid)) {
            $where['sever.game_id'] = $gameid;
        }
        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("g.is_default = 0")
        ->where($where)
        ->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'=>2,'cooperation_status'=>['neq',0]],'','self');
        // $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',0,'intval');
        $where = [];
        if (empty($gameid)) {
            return json(['data'=>[],'code'=>10001,'msg'=>'请选择游戏']);
        } else {
            $where['game.id'] = $gameid;
        }
        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')
                ->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')->find();
            if ((new MakeReport())->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')
                ->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'=>2,'cooperation_status'=>['neq',0]],'','self');
        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['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();
        $out['pay_cnt'] = Db::name('cy_pay')->alias('pay')
        ->field('COALESCE(sum(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 = 2)
    {
        $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
        $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($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') {
			$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)) {
                switch ($pay_tpye) {
                    case 'zfb':
                        $condition['pay.paytype'] = ['IN',['zfb','zfb-h5-mihua','zfb-h5-kj']];
                        break;
                    case 'wxpay':
                        $condition['pay.paytype'] = ['IN',['wxpay-h5','wxpay-h5-mihua','wxpay-h5-kj','wxpay-h5-sumpay']];
                        break;
                    case 'ptb':
                        $condition['pay.paytype'] = $pay_tpye;
                        break;
                    case 'zfbpay-hh':
                        $condition['pay.paytype'] = ['IN',['mix-zfb','mix-zfb-h5-mihua','mix-zfb-h5-kj']];
                        break;
                    case 'wxpay-hh':
                        $condition['pay.paytype'] = ['IN',['mix-wxpay-h5','mix-wxpay-h5-mihua','mix-wxpay-h5-kj','mix-wxpay-h5-sumpay']];
                        break;
                    default:
                        $condition['pay.paytype'] = $pay_tpye;
                        break;
                }

            }

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

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

            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;
            }

            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 == '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;
    }
}