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

<?php
/**
 * 充值记录管理控制器
 *
 */

namespace app\admin\controller;

use app\common\library\MakeReportGo;
use think\Db;
use app\common\library\MakeReport;
use GuzzleHttp\Client;
use GuzzleHttp\Exception\GuzzleException;
use think\Model;

class Retaine extends Admin
{
    public function retaineGame()
    {

        $tmpGameList = model('Common/Game')->getAllByCondition('name,id', ['cooperation_status' => 1], "id desc", "all");
        $gameList = array();
        foreach ($tmpGameList as $game) {
            $gameList[$game['id']] = $game;
        }
        $this->assign('gameArr', $tmpGameList);
        $this->assign('game_list', $gameList);

        list($list, $total) = $this->retaineGameList(request()->param(), 1);
        
        $this->assign('total', $total);
        $this->assign('list', $list);
        $this->assign('page', $list->render());

        return $this->fetch();
    }

    public function retaineGameChannel()
    {
        $tmpGameList = model('Common/Game')->getAllByCondition('name,id', ['cooperation_status' => 1]);
        $gameList = array();
        foreach ($tmpGameList as $game) {
            $gameList[$game['id']] = $game;
        }
        $level = input('level', 0);

        $business = model('common/Business')->getChannelIds(session('ADMIN_ID'), $level);
        $channelWhere = [];
        if ($business != -1) {
            if ($business) {
                $channelWhere['id'] = ['in', $business];
            } else {
                $channelWhere['id'] = -1;
            }
        }

        $channel = model('common/Channel')->where(['level' => $level])->where($channelWhere)->field('id,name')->order('id desc')->select();
        $channelC = model('common/Channel')->field('id,name')->order('id desc')->select();
        foreach ($channelC as $v) {
            $channelList[$v['id']] = $v;
        }

        $this->assign('level', $level);
        $this->assign('channel', $channel);
        $this->assign('channelList', $channelList);
        $this->assign('gameArr', $tmpGameList);
        $this->assign('game_list', $gameList);

        list($list, $total) = $this->retaineGameList(request()->param(), 2);
        $this->assign('total', $total);
        $this->assign('list', $list);
        $this->assign('page', $list->render());

        return $this->fetch();
    }


    public function retaineGameChannelSummary()
    {
        $tmpGameList = model('Common/Game')->getAllByCondition('name,id', ['cooperation_status' => 1]);
        $gameList = array();
        foreach ($tmpGameList as $game) {
            $gameList[$game['id']] = $game;
        }
        $level = input('level', 0);

        $business = model('common/Business')->getChannelIds(session('ADMIN_ID'), $level);
        $channelWhere = [];
        if ($business != -1) {
            if ($business) {
                $channelWhere['id'] = ['in', $business];
            } else {
                $channelWhere['id'] = -1;
            }
        }

        $channel = model('common/Channel')->where(['level' => $level])->where($channelWhere)->field('id,name')->order('id desc')->select();
        $channelC = model('common/Channel')->field('id,name')->order('id desc')->select();
        foreach ($channelC as $v) {
            $channelList[$v['id']] = $v;
        }

        $this->assign('level', $level);
        $this->assign('channel', $channel);
        $this->assign('channelList', $channelList);
        $this->assign('gameArr', $tmpGameList);
        $this->assign('game_list', $gameList);

        list($list, $total) = $this->retaineGameList(request()->param(), 2, 'summary');
        $this->assign('total', $total);
        $this->assign('list', $list);
        $this->assign('page', $list->render());

        return $this->fetch();
    }


    private function retaineGameList($data, $type_id, $mode = 'normal')
    {
        $tmpGameList = model('Common/Game')->getAllByCondition('name,id', ['cooperation_status' => 1], "id desc", "all");
        $gameIds = '';
        if($tmpGameList){
            $gameIds = implode(array_column($tmpGameList, 'id'), ',');
            $where['game_id'] = ['in', $gameIds];
        }

        $where['type_id'] = ['=', $type_id];
        if (isset($data['game_id']) && $data['game_id']) {
            $where['game_id'] = ['in', $data['game_id']];
        }


        $dataTime = "";
        // 开始时间和结束时间不为空时
        if (isset($data['start']) && isset($data['end']) && $data['start'] != '' && $data['end'] != '') {
            $where['day'] = [
                ['>=', $data['start']],
                ['<=', $data['end']],
            ];
        } //开始时间不为空时
        elseif (isset($data['start']) && $data['start'] != '') {
            $where['day'] = ['>=', $data['start']];
        } //结束时间不为空时
        elseif (isset($data['end']) && $data['end'] != '') {
            $where['day'] = ['<=', $data['end']];
        }
        // 默认昨天
        if(empty($where['day'])){
            $dataTime = date('Y-m-d', strtotime('-1 day'));
            $where['day'] = [
                ['>=', $dataTime],
                ['<=', $dataTime],
            ];
        }
        $this->assign('start', $dataTime);
        $this->assign('end', $dataTime);

        // 判断是否多日期
        $rangeTimeStatus = "";
        if(!empty($where['day']) && $mode == 'summary'){
            // 从原始数据中获取开始和结束日期
            $startDate = isset($data['start']) && $data['start'] != '' ? $data['start'] : '';
            $endDate = isset($data['end']) && $data['end'] != '' ? $data['end'] : '';

            // 如果前端没有传入日期（采用默认日期范围），则从$where条件中解析
            if(($startDate == '' || $endDate == '') && isset($where['day'])){
                $dayCondition = $where['day'];
                // between 条件：[['>=', start], ['<=', end]]
                if(isset($dayCondition[0]) && is_array($dayCondition[0])){
                    if($startDate == '' && isset($dayCondition[0][1])){
                        $startDate = $dayCondition[0][1];
                    }
                    if($endDate == '' && isset($dayCondition[1][1])){
                        $endDate = $dayCondition[1][1];
                    }
                } else {
                    // 单条件：['>=', start] 或 ['<=', end]
                    if($startDate == '' && isset($dayCondition[0]) && $dayCondition[0] == '>=' && isset($dayCondition[1])){
                        $startDate = $dayCondition[1];
                    }
                    if($endDate == '' && isset($dayCondition[0]) && $dayCondition[0] == '<=' && isset($dayCondition[1])){
                        $endDate = $dayCondition[1];
                    }
                }
            }
            
            if($startDate && $endDate){
                // 有开始和结束日期
                $dayDiff = (strtotime($endDate) - strtotime($startDate))/(24*3600);
                if($dayDiff >= 1){
                    // 多天：显示日期范围
                    $rangeTimeStatus = $startDate.' 至 '.$endDate;
                } else {
                    // 单天：显示该日期（开始日期和结束日期相同，或日期差小于1天）
                    $rangeTimeStatus = $startDate;
                }
            } elseif($startDate){
                // 只有开始日期，使用开始日期
                $rangeTimeStatus = $startDate;
            } elseif($endDate){
                // 只有结束日期，使用结束日期
                $rangeTimeStatus = $endDate;
            }
        }
        $level = input('level', 0);
        $business = model('common/Business')->getChannelIds(session('ADMIN_ID'), $level);

        // $type_id = 2, $business = -1, $level = 0
        if ($type_id == 2 && $level != 3) {
            if ($level == 0) {
                if ($business != -1) {
                    if ($business && $business != -2) {
                        if (isset($data['channel_id']) && $data['channel_id']) {
                            if (in_array($data['channel_id'], $business)) {
                                $where['s_channel_id'] = ['in', $data['channel_id']];
                            } else {
                                $where['s_channel_id'] = ['in',[-1]];
                            }
                        } else {
                            $where['s_channel_id'] = ['in', $business];
                        }
                    } else {
                        $where['s_channel_id'] = ['in',[-1]];
                    }
                } else {
                    if (isset($data['channel_id']) && $data['channel_id']) {
                        $where['s_channel_id'] = ['in', $data['channel_id']];
                    }
                }

                $field = 's_channel_id as channel_id';
                $group = 's_channel_id';
            } else if ($level == 1) {
               // if (isset($data['channel_id']) && $data['channel_id']) {
               //     $where['b_channel_id'] = ['in', $data['channel_id']];
               // }
                if ($business != -1) {
                    if ($business && $business != -2) {
                        if (isset($data['channel_id']) && $data['channel_id']) {
                            if (in_array($data['channel_id'], $business)) {
                                $where['b_channel_id'] = ['in', $data['channel_id']];
                            } else {
                                $where['b_channel_id'] = ['in',[-1]];
                            }
                        } else {
                            $where['b_channel_id'] = ['in', $business];
                        }
                    } else {
                        $where['b_channel_id'] = ['in',[-1]];
                    }
                } else {
                    if (isset($data['channel_id']) && $data['channel_id']) {
                        $where['b_channel_id'] = ['in', $data['channel_id']];
                    }
                }
                $field = 'b_channel_id as channel_id';
                $group = 'b_channel_id';
            } else if ($level == 2) {

                if ($business != -1) {
                    if ($business && $business != -2) {
                        if (isset($data['channel_id']) && $data['channel_id']) {
                            if (in_array($data['channel_id'], $business)) {
                                $where['bz_channel_id'] = ['in', $data['channel_id']];
                            } else {
                                $where['bz_channel_id'] = ['in',[-1]];
                            }
                        } else {
                            $where['bz_channel_id'] = ['in', $business];
                        }
                    } else {
                        $where['bz_channel_id'] = ['in',[-1]];
                    }
                } else {
                    if (isset($data['channel_id']) && $data['channel_id']) {
                        $where['bz_channel_id'] = ['in', $data['channel_id']];
                    }
                }
                if (!(isset($data['channel_id']) && $data['channel_id'])) {
                    $where['bz_channel_id'] = ['>', 0];
                }

                $field = 'bz_channel_id as channel_id';
                $group = 'bz_channel_id';
            }

            if (request()->isAjax()) {
                // 根据mode决定group和order
                $groupForExport = $group . ',game_id,day';
                $orderForExport = 'day desc, reg_num desc, game_id desc';
                $dayField = ',day';
                if($mode == 'summary' && $rangeTimeStatus){
                    $groupForExport = $group . ',game_id';
                    $orderForExport = 'reg_num desc, game_id desc';
                    // summary模式下，day字段使用空字符串，实际显示由Go代码根据RangeTimeStatus处理
                    $dayField = ",'".$rangeTimeStatus."' as day";
                }

                $field = "channel_id"; // TODO：没找到具体问题，因为上边的 $field 看不懂用来干啥的，导致渠道数据下载时候下载为空；这里先用固定方式使用；
                $field = 'type_id' . $dayField . ',game_id,bz_channel_id,b_channel_id,s_channel_id,sum(reg_num) as reg_num,sum(role_num) as role_num,sum(act_num) as act_num,sum(recharge_num) as recharge_num,sum(pay_num) as pay_num,sum(one_stay) as one_stay,sum(three_stay) as three_stay,sum(four_stay) as four_stay,sum(five_stay) as five_stay,sum(six_stay) as six_stay,sum(seven_stay) as seven_stay,sum(fifteen_stay) as fifteen_stay,sum(thirty_stay) as thirty_stay, ' . $field;
                if($mode == 'summary'){
                    $field .= " ,reg_total,new_act_num,new_pay_num,new_recharge_num";
                }else{
                    $field .= " ,three_stay_rate,four_stay_rate,five_stay_rate,six_stay_rate,seven_stay_rate,fifteen_stay_rate,thirty_stay_rate,reg_total";
                }
                $sql = model('common/RetaineGame')->field($field)->where($where)->group($groupForExport)->order($orderForExport)->fetchSql(true)->select();

                // $sql = model('common/RetaineGame')->field('type_id' . $dayField . ',game_id,bz_channel_id,b_channel_id,s_channel_id,sum(reg_num) as reg_num,sum(role_num) as role_num,sum(act_num) as act_num,sum(recharge_num) as recharge_num,sum(pay_num) as pay_num,sum(one_stay) as one_stay,sum(three_stay) as three_stay,sum(four_stay) as four_stay,sum(five_stay) as five_stay,sum(six_stay) as six_stay,sum(seven_stay) as seven_stay,sum(fifteen_stay) as fifteen_stay,sum(thirty_stay) as thirty_stay' . $field)->where($where)->group($groupForExport)->order($orderForExport)->fetchSql(true)->select();

                $extendParams = ['Level' => $level, 'TypeId' => $type_id, 'Mode' => $mode];
                if($mode == 'summary' && $rangeTimeStatus){
                    $extendParams['RangeTimeStatus'] = $rangeTimeStatus;
                }
                if ((new MakeReportGo())->addTask('retaineGame', $sql, session_id(), $extendParams)) {
                    $this->success('报表生成的任务已经提交, 报表生成完成后，会及时通知您，请耐心稍等');
                } else {
                    $this->error('报表生成任务不可重复提交，如遇到无法导出情况，建议修改查询条件解除当前状态，提交重新生成报表任务！');
                }
            }

            $total = model('common/RetaineGame')->where($where)->field('sum(reg_num) as reg_num,sum(role_num) as role_num,sum(recharge_num) as recharge_num,sum(act_num) as act_num,sum(reg_total) as reg_total')->find();

            $groupNew = $group . ',game_id,day';
            if($mode == 'summary'){
                $groupNew = $group . ',game_id';
            }

            // dump($groupNew);
            // ->field($filed)
            $sql = model('common/RetaineGame')->where($where)->group($groupNew)->where($where)->buildSql();

            // summary模式下，排序去掉day desc
            $orderBy = 'day desc, reg_num desc, game_id desc';
            if($mode == 'summary'){
                $orderBy = 'reg_num desc, game_id desc';
            }
            $list = Db::table($sql . ' a')->order($orderBy)->paginate(30, false, ['query' => input('get.')])->each(function ($item, $key) use ($mode, $rangeTimeStatus) {

                if($rangeTimeStatus && $mode == 'summary'){
                    $item['day'] = $rangeTimeStatus;
                }
                $item['rate'] = $item['act_num'] > 0 ? priceFormat(100 * $item['pay_num'] / $item['act_num']) : '0.00'; // 付费率 = 付费人数/活跃人数
                $item['arpu'] = $item['act_num'] > 0 ? priceFormat($item['recharge_num'] / $item['act_num']) : '0.00'; // arpu = 充值金额/活跃玩家
                $item['arppu'] = $item['pay_num'] > 0 ? priceFormat($item['recharge_num'] / $item['pay_num']) : '0.00'; // arppu = 充值金额/付费人数

                if($mode == 'normal'){
                    $item['one_stay_rate'] = $item['reg_num'] > 0 ? priceFormat(100 * $item['one_stay'] / $item['reg_num']) : '0.00'; //次留
                    $item['three_stay_rate'] = $item['reg_num'] > 0 ? priceFormat(100 * $item['three_stay'] / $item['reg_num']) : '0.00'; //三留
                    $item['four_stay_rate'] = $item['reg_num'] > 0 ? priceFormat(100 * $item['four_stay'] / $item['reg_num']) : '0.00'; //四留
                    $item['five_stay_rate'] = $item['reg_num'] > 0 ? priceFormat(100 * $item['five_stay'] / $item['reg_num']) : '0.00'; //五留
                    $item['six_stay_rate'] = $item['reg_num'] > 0 ? priceFormat(100 * $item['six_stay'] / $item['reg_num']) : '0.00'; //六留
                    $item['seven_stay_rate'] = $item['reg_num'] > 0 ? priceFormat(100 * $item['seven_stay'] / $item['reg_num']) : '0.00'; //七留
                    $item['fifteen_stay_rate'] = $item['reg_num'] > 0 ? priceFormat(100 * $item['fifteen_stay'] / $item['reg_num']) : '0.00'; //十五留
                    $item['thirty_stay_rate'] = $item['reg_num'] > 0 ? priceFormat(100 * $item['thirty_stay'] / $item['reg_num']) : '0.00'; //三十留
                }else{
                    $item['old_act_num'] = $item['old_pay_num'] = $item['old_recharge_num'] = 0;
                    if($item['act_num'] > 0 & ($item['act_num'] - $item['new_act_num']) > 0){
                        $item['old_act_num'] = $item['act_num'] - $item['new_act_num']; // 老活跃玩家
                    }
                    if($item['pay_num'] > 0 & ($item['pay_num'] - $item['new_pay_num']) > 0){
                        $item['old_pay_num'] = $item['pay_num'] - $item['new_pay_num']; // 老付费玩家
                    }
                    if($item['recharge_num'] > 0 & ($item['recharge_num'] - $item['new_recharge_num']) > 0){
                        $item['old_recharge_num'] = sprintf("%.2f", ($item['recharge_num'] - $item['new_recharge_num'])); // 老玩家付费金额
                    }
                }
                return $item;
            });

        } else {

            if ($type_id == 2 && $level = 3) {
                if ($business != -1) {
                    if ($business && $business != -2) {
                        if (isset($data['channel_id']) && $data['channel_id']) {
                            if (in_array($data['channel_id'], $business)) {
                                $where['channel_id'] = ['in', $data['channel_id']];
                            } else {
                                $where['channel_id'] = ['in',[-1]];
                            }
                        } else {
                            $where['channel_id'] = ['in', $business];
                        }
                    } else {
                        $where['channel_id'] = ['in',[-1]];
                    }
                } else {
                    if (isset($data['channel_id']) && $data['channel_id']) {
                        $where['channel_id'] = ['in', $data['channel_id']];
                    }
                }
            }
            if (request()->isAjax()) {
                // 根据mode决定group和order
                $field = '*';
                if($mode == 'summary' && $rangeTimeStatus){
                    // summary模式下，day字段使用空字符串，实际显示由Go代码根据RangeTimeStatus处理
                    $field = "*,'{$rangeTimeStatus}' as day";
                }

                $sql = model('common/RetaineGame')->where($where)->field($field)->order('day desc, reg_num desc, game_id desc')->fetchSql(true)->select();
                // dump($sql);

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

            $total = model('common/RetaineGame')->where($where)->field('sum(reg_num) as reg_num,sum(role_num) as role_num,sum(recharge_num) as recharge_num,sum(act_num) as act_num,sum(reg_total) as reg_total')->find();
            $list = model('common/RetaineGame')->where($where)
                ->order('day desc, reg_num desc, game_id desc')
                // ->order('reg_num desc')
                ->paginate(20, false, ['query' => input('get.')])
                ->each(function ($item, $key) use ($mode, $rangeTimeStatus) {
                    if($rangeTimeStatus && $mode == 'summary'){
                        $item['day'] = $rangeTimeStatus;
                    }

                    $item['arpu'] = $item['act_num'] > 0 ? priceFormat($item['recharge_num'] / $item['act_num']) : '0.00'; // 充值金额/活跃玩家
                    $item['arppu'] = $item['pay_num'] > 0 ? priceFormat($item['recharge_num'] / $item['pay_num']) : '0.00'; //	充值金额/付费人数
                    if($mode == 'normal'){
                        $item['rate'] = $item['act_num'] > 0 ? priceFormat(100 * $item['pay_num'] / $item['act_num']) : '0.00'; // 付费率 = 付费人数/活跃人数

                        $item['one_stay_rate'] = $item['reg_num'] > 0 ? priceFormat(100 * $item['one_stay'] / $item['reg_num']) : '0.00'; //次留
                        $item['three_stay_rate'] = $item['reg_num'] > 0 ? priceFormat(100 * $item['three_stay'] / $item['reg_num']) : '0.00'; //三留
                        $item['four_stay_rate'] = $item['reg_num'] > 0 ? priceFormat(100 * $item['four_stay'] / $item['reg_num']) : '0.00'; //四留
                        $item['five_stay_rate'] = $item['reg_num'] > 0 ? priceFormat(100 * $item['five_stay'] / $item['reg_num']) : '0.00'; //五留
                        $item['six_stay_rate'] = $item['reg_num'] > 0 ? priceFormat(100 * $item['six_stay'] / $item['reg_num']) : '0.00'; //六留
                        $item['seven_stay_rate'] = $item['reg_num'] > 0 ? priceFormat(100 * $item['seven_stay'] / $item['reg_num']) : '0.00'; //七留
                        $item['fifteen_stay_rate'] = $item['reg_num'] > 0 ? priceFormat(100 * $item['fifteen_stay'] / $item['reg_num']) : '0.00'; //十五留
                        $item['thirty_stay_rate'] = $item['reg_num'] > 0 ? priceFormat(100 * $item['thirty_stay'] / $item['reg_num']) : '0.00'; //三十留
                    }else{
                        $item['old_act_num'] = $item['old_pay_num'] = $item['old_recharge_num'] = 0;
                        if($item['act_num'] > 0 && ($item['act_num'] - $item['new_act_num']) > 0){
                            $item['old_act_num'] = $item['act_num'] - $item['new_act_num']; // 老活跃玩家
                        }
                        if($item['pay_num'] > 0 && ($item['pay_num'] - $item['new_pay_num']) > 0){
                            $item['old_pay_num'] = $item['pay_num'] - $item['new_pay_num']; // 老付费玩家
                        }
                        if($item['recharge_num'] > 0 && ($item['recharge_num'] - $item['new_recharge_num']) > 0){
                            $item['old_recharge_num'] = sprintf("%.2f", ($item['recharge_num'] - $item['new_recharge_num'])); // 老玩家付费金额
                        }
                    }
                    return $item;
                });
        }

        return [$list, $total];
    }

    public function retaineServer()
    {
        $tmpGameList = model('Common/Game')->getAllByCondition('name,id', ['cooperation_status' => 1]);
        $gameList = array();
        foreach ($tmpGameList as $game) {
            $gameList[$game['id']] = $game;
        }
        $servers = model('common/GameServer')->cache('retaineServer:server', 60)->field('serverid,game_id,servername')->select();
        $serversList = [];
        foreach ($servers as $k => $v) {
            $serversList[$v['game_id'] . '_' . $v['serverid']] = $v['servername'];
        }

        $this->assign('serversList', $serversList);
        $this->assign('gameArr', $tmpGameList);
        $this->assign('game_list', $gameList);

        list($list, $total) = $this->retaineGameServerList(request()->param(), 1);
        $this->assign('total', $total);
        $this->assign('list', $list);
        $this->assign('page', $list->render());

        return $this->fetch();

    }

    public function retaineServerChannel()
    {

        $tmpGameList = model('Common/Game')->getAllByCondition('name,id', ['cooperation_status' => 1]);
        $gameList = array();
        foreach ($tmpGameList as $game) {
            $gameList[$game['id']] = $game;
        }
        $level = input('level', 0);
        $business = model('common/Business')->getChannelIds(session('ADMIN_ID'), $level);
        $channelWhere = [];
        if ($business != -1) {
            if ($business) {
                $channelWhere['id'] = ['in', $business];
            } else {
                $channelWhere['id'] = ['in',[-1]];
            }
        }

        $channel = model('common/Channel')->where(['level' => $level])->where($channelWhere)->field('id,name')->order('id desc')->select();

        $channelC = model('common/Channel')->field('id,name')->order('id desc')->select();
        foreach ($channelC as $v) {
            $channelList[$v['id']] = $v;
        }
        $servers = model('common/GameServer')->cache('retaineServer:server', 60)->field('serverid,game_id,servername')->select();
        $serversList = [];
        foreach ($servers as $k => $v) {
            $serversList[$v['game_id'] . '_' . $v['serverid']] = $v['servername'];
        }

        $this->assign('serversList', $serversList);

        $this->assign('level', $level);
        $this->assign('channel', $channel);
        $this->assign('channelList', $channelList);
        $this->assign('gameArr', $tmpGameList);
        $this->assign('game_list', $gameList);

        list($list, $total) = $this->retaineGameServerList(request()->param(), 2);
        $this->assign('total', $total);
        $this->assign('list', $list);
        $this->assign('page', $list->render());

        return $this->fetch();
    }

    private function retaineGameServerList($data, $type_id)
    {
        $where['type_id'] = ['=', $type_id];
        if (isset($data['server_id']) && $data['server_id']) {
            $where['server_id'] = ['in', $data['server_id']];
        }
        if (isset($data['game_id']) && $data['game_id']) {
            $where['game_id'] = ['in', $data['game_id']];
        }
        //开始时间和结束时间不为空时
        if (isset($data['start']) && isset($data['end']) && $data['start'] != '' && $data['end'] != '') {
            $where['day'] = [
                ['>=', $data['start']],
                ['<=', $data['end']],
            ];
        } //开始时间不为空时
        elseif (isset($data['start']) && $data['start'] != '') {
            $where['day'] = ['>=', $data['start']];
        } //结束时间不为空时
        elseif (isset($data['end']) && $data['end'] != '') {
            $where['day'] = ['<=', $data['end']];
        }
        // 默认昨天
        if(empty($where['day'])){
            $dataTime = date('Y-m-d', strtotime('-1 day'));
            $where['day'] = [
                ['>=', $dataTime],
                ['<=', $dataTime],
            ];
            $this->assign('start', $dataTime);
            $this->assign('end', $dataTime);
        }

        $level = input('level', 0);
        $business = model('common/Business')->getChannelIds(session('ADMIN_ID'), $level);

        if ($type_id == 2 && $level != 3) {

            if ($level == 0) {
                if ($business != -1) {
                    if ($business && $business != -2) {
                        if (isset($data['channel_id']) && $data['channel_id']) {
                            if (in_array($data['channel_id'], $business)) {
                                $where['s_channel_id'] = ['in', $data['channel_id']];
                            } else {
                                $where['s_channel_id'] = ['in',[-1]];
                            }
                        } else {
                            $where['s_channel_id'] = ['in', $business];
                        }
                    } else {
                        $where['s_channel_id'] = ['in',[-1]];
                    }
                } else {
                    if (isset($data['channel_id']) && $data['channel_id']) {
                        $where['s_channel_id'] = ['in', $data['channel_id']];
                    }
                }

                $field = ',s_channel_id as channel_id';
                $group = 's_channel_id';
            } else if ($level == 1) {
//                if (isset($data['channel_id']) && $data['channel_id']) {
//                    $where['b_channel_id'] = ['in', $data['channel_id']];
//                }
                if ($business != -1) {
                    if ($business && $business != -2) {
                        if (isset($data['channel_id']) && $data['channel_id']) {
                            if (in_array($data['channel_id'], $business)) {
                                $where['b_channel_id'] = ['in', $data['channel_id']];
                            } else {
                                $where['b_channel_id'] = ['in',[-1]];
                            }
                        } else {
                            $where['b_channel_id'] = ['in', $business];
                        }
                    } else {
                        $where['b_channel_id'] = ['in',[-1]];
                    }
                } else {
                    if (isset($data['channel_id']) && $data['channel_id']) {
                        $where['b_channel_id'] = ['in', $data['channel_id']];
                    }
                }
                $field = ',b_channel_id as channel_id';
                $group = 'b_channel_id';
            } else if ($level == 2) {

                if ($business != -1) {
                    if ($business && $business != -2) {
                        if (isset($data['channel_id']) && $data['channel_id']) {
                            if (in_array($data['channel_id'], $business)) {
                                $where['bz_channel_id'] = ['in', $data['channel_id']];
                            } else {
                                $where['bz_channel_id'] = ['in',[-1]];
                            }
                        } else {
                            $where['bz_channel_id'] = ['in', $business];
                        }
                    } else {
                        $where['bz_channel_id'] = ['in',[-1]];
                    }
                } else {
                    if (isset($data['channel_id']) && $data['channel_id']) {
                        $where['bz_channel_id'] = ['in', $data['channel_id']];
                    }
                }
                if (!(isset($data['channel_id']) && $data['channel_id'])) {
                    $where['bz_channel_id'] = ['>', 0];
                }
                $field = ',bz_channel_id as channel_id';

                $group = 'bz_channel_id';
            }

            if (request()->isAjax()) {
                $sql = model('common/RetaineServer')->field('type_id,day,game_id,server_id,bz_channel_id,b_channel_id,s_channel_id,sum(reg_num) as reg_num,sum(role_num) as role_num,sum(act_num) as act_num,sum(recharge_num) as recharge_num,sum(pay_num) as pay_num,sum(one_stay) as one_stay,sum(three_stay) as three_stay,sum(four_stay) as four_stay,sum(five_stay) as five_stay,sum(six_stay) as six_stay,sum(seven_stay) as seven_stay,sum(fifteen_stay) as fifteen_stay,sum(thirty_stay) as thirty_stay' . $field)->where($where)->group($group . ',game_id,server_id,day')->orderRaw('day desc,reg_num desc')->fetchSql(true)->select();
                if ((new MakeReportGo())->addTask('retaineGameServer', $sql, session_id(), ['Level' => $level, 'TypeId' => $type_id])) {
                    $this->success('报表生成的任务已经提交, 报表生成完成后，会及时通知您，请耐心稍等');
                } else {
                    $this->error('报表生成任务不可重复提交，如遇到无法导出情况，建议修改查询条件解除当前状态，提交重新生成报表任务！');
                }
            }

            $total = model('common/RetaineServer')->where($where)->field('sum(reg_num) as reg_num,sum(role_num) as role_num,sum(recharge_num) as recharge_num,sum(act_num) as act_num')->find();
            $sql = model('common/RetaineServer')->field('type_id,day,game_id,server_id,bz_channel_id,b_channel_id,s_channel_id,sum(reg_num) as reg_num,sum(role_num) as role_num,sum(act_num) as act_num,sum(recharge_num) as recharge_num,sum(pay_num) as pay_num,sum(one_stay) as one_stay,sum(three_stay) as three_stay,sum(four_stay) as four_stay,sum(five_stay) as five_stay,sum(six_stay) as six_stay,sum(seven_stay) as seven_stay,sum(fifteen_stay) as fifteen_stay,sum(thirty_stay) as thirty_stay' . $field)->where($where)->group($group . ',game_id,server_id,day')->buildSql();
            $list = Db::table($sql . ' a')->orderRaw('day desc,reg_num desc')->paginate(20, false, ['query' => input('get.')])->each(function ($item, $key) {
                $item['rate'] = $item['act_num'] > 0 ? priceFormat(100 * $item['pay_num'] / $item['act_num']) : '0.00';//付费人数/活跃人数
                $item['arpu'] = $item['act_num'] > 0 ? priceFormat($item['recharge_num'] / $item['act_num']) : '0.00';//充值金额/活跃玩家
                $item['arppu'] = $item['pay_num'] > 0 ? priceFormat($item['recharge_num'] / $item['pay_num']) : '0.00';//	充值金额/付费人数

                $item['one_stay_rate'] = $item['role_num'] > 0 ? priceFormat($item['one_stay'] / $item['role_num']) : '0.00'; //次留
                $item['three_stay_rate'] = $item['role_num'] > 0 ? priceFormat($item['three_stay'] / $item['role_num']) : '0.00'; //三留
                $item['four_stay_rate'] = $item['role_num'] > 0 ? priceFormat($item['four_stay'] / $item['role_num']) : '0.00'; //四留
                $item['five_stay_rate'] = $item['role_num'] > 0 ? priceFormat($item['five_stay'] / $item['role_num']) : '0.00'; //五留
                $item['six_stay_rate'] = $item['role_num'] > 0 ? priceFormat($item['six_stay'] / $item['role_num']) : '0.00'; //六留
                $item['seven_stay_rate'] = $item['role_num'] > 0 ? priceFormat($item['seven_stay'] / $item['role_num']) : '0.00'; //七留
                $item['fifteen_stay_rate'] = $item['role_num'] > 0 ? priceFormat($item['fifteen_stay'] / $item['role_num']) : '0.00'; //十五留
                $item['thirty_stay_rate'] = $item['role_num'] > 0 ? priceFormat($item['thirty_stay'] / $item['role_num']) : '0.00'; //三十留
                return $item;
            });

        } else {

            if ($level = 3 && $type_id == 2) {
                if ($business != -1) {
                    if ($business && $business != -2) {
                        if (isset($data['channel_id']) && $data['channel_id']) {
                            if (in_array($data['channel_id'], $business)) {
                                $where['channel_id'] = ['in', $data['channel_id']];
                            } else {
                                $where['channel_id'] = ['in',[-1]];
                            }
                        } else {
                            $where['channel_id'] = ['in', $business];
                        }
                    } else {
                        $where['channel_id'] = ['in',[-1]];
                    }
                } else {
                    if (isset($data['channel_id']) && $data['channel_id']) {
                        $where['channel_id'] = ['in', $data['channel_id']];
                    }
                }
            }

            if (request()->isAjax()) {
                $sql = model('common/RetaineServer')->where($where)->orderRaw('day desc,reg_num desc')->fetchSql(true)->select();
                if ((new MakeReportGo())->addTask('retaineGameServer', $sql, session_id(), ['Level' => $level, 'TypeId' => $type_id])) {
                    $this->success('报表生成的任务已经提交, 报表生成完成后，会及时通知您，请耐心稍等');
                } else {
                    $this->error('报表生成任务不可重复提交，如遇到无法导出情况，建议修改查询条件解除当前状态，提交重新生成报表任务！');
                }
            }

            $total = model('common/RetaineServer')->where($where)->field('sum(reg_num) as reg_num,sum(role_num) as role_num,sum(recharge_num) as recharge_num,sum(act_num) as act_num')->find();
            $list = model('common/RetaineServer')->where($where)->orderRaw('day desc,reg_num desc')->paginate(20, false, ['query' => input('get.')])->each(function ($item, $key) {
                $item['rate'] = $item['act_num'] > 0 ? priceFormat(100 * $item['pay_num'] / $item['act_num']) : '0.00';//付费人数/活跃人数
                $item['arpu'] = $item['act_num'] > 0 ? priceFormat($item['recharge_num'] / $item['act_num']) : '0.00';//充值金额/活跃玩家
                $item['arppu'] = $item['pay_num'] > 0 ? priceFormat($item['recharge_num'] / $item['pay_num']) : '0.00';//	充值金额/付费人数

                $item['one_stay_rate'] = $item['role_num'] > 0 ? priceFormat($item['one_stay'] / $item['role_num']) : '0.00'; //次留
                $item['three_stay_rate'] = $item['role_num'] > 0 ? priceFormat($item['three_stay'] / $item['role_num']) : '0.00'; //三留
                $item['four_stay_rate'] = $item['role_num'] > 0 ? priceFormat($item['four_stay'] / $item['role_num']) : '0.00'; //四留
                $item['five_stay_rate'] = $item['role_num'] > 0 ? priceFormat($item['five_stay'] / $item['role_num']) : '0.00'; //五留
                $item['six_stay_rate'] = $item['role_num'] > 0 ? priceFormat($item['six_stay'] / $item['role_num']) : '0.00'; //六留
                $item['seven_stay_rate'] = $item['role_num'] > 0 ? priceFormat($item['seven_stay'] / $item['role_num']) : '0.00'; //七留
                $item['fifteen_stay_rate'] = $item['role_num'] > 0 ? priceFormat($item['fifteen_stay'] / $item['role_num']) : '0.00'; //十五留
                $item['thirty_stay_rate'] = $item['role_num'] > 0 ? priceFormat($item['thirty_stay'] / $item['role_num']) : '0.00'; //三十留
                return $item;
            });
        }


        return [$list, $total];
    }

    public function getServer()
    {
        $games = explode(',', input('game'));
        if(in_array(223,$games) || in_array(224,$games)){
            $list = model('common/GameServer')
                ->where('game_id', 'in', explode(',', input('game')))
                ->whereRaw('serverid!=-1')
                ->field('servername as name,serverid as id')->group('serverid')->orderRaw('convert(serverid,signed) desc')->select();
        }else{
            $list = model('common/GameServer')
                ->where('game_id', 'in', explode(',', input('game')))
                ->whereRaw('serverid!=servername')
                ->field('servername as name,serverid as id')->group('serverid')->orderRaw('convert(serverid,signed) desc')->select();
        }

        $this->jsonResult($list, 1, '成功');
    }

    public function getChannel()
    {

        $business = model('common/Business')->getChannelIds(session('ADMIN_ID'), input('level', 0));
        $channelWhere = [];
        if ($business != -1) {
            if ($business) {
                $channelWhere['id'] = ['in', $business];
            } else {
                $channelWhere['id'] = -1;
            }
        }

        $channel = model('common/Channel')->where(['level' => input('level', 0)])->where($channelWhere)->field('id,name')->order('id desc')->select();
        $this->jsonResult($channel, 1, '成功');
    }

    public function retaine()
    {
        list($list, $total) = $this->retainePlatformList(request()->param(), 1);
        $this->assign('total', $total);
        $this->assign('list', $list);
        $this->assign('page', $list->render());

        return $this->fetch();
    }
    private function retainePlatformList($data, $type_id)
    {

        $where['type_id'] = ['=', $type_id];


        //开始时间和结束时间不为空时
        if (isset($data['start']) && isset($data['end']) && $data['start'] != '' && $data['end'] != '') {
            $where['day'] = [
                ['>=', $data['start']],
                ['<=', $data['end']],
            ];
        } //开始时间不为空时
        elseif (isset($data['start']) && $data['start'] != '') {
            $where['day'] = ['>=', $data['start']];
        } //结束时间不为空时
        elseif (isset($data['end']) && $data['end'] != '') {
            $where['day'] = ['<=', $data['end']];
        }
        $level = input('level', 0);

        $business = model('common/Business')->getChannelIds(session('ADMIN_ID'), $level);
        if ($type_id == 2 && $level != 3) {

            if ($level == 0) {
                if ($business != -1) {
                    if ($business && $business != -2) {
                        if (isset($data['channel_id']) && $data['channel_id']) {
                            if (in_array($data['channel_id'], $business)) {
                                $where['s_channel_id'] = ['in', $data['channel_id']];
                            } else {
                                $where['s_channel_id'] = ['in',[-1]];
                            }
                        } else {
                            $where['s_channel_id'] = ['in', $business];
                        }
                    } else {
                        $where['s_channel_id'] = ['in',[-1]];
                    }
                } else {
                    if (isset($data['channel_id']) && $data['channel_id']) {
                        $where['s_channel_id'] = ['in', $data['channel_id']];
                    }
                }

                $field = ',s_channel_id as channel_id';
                $group = 's_channel_id';
            } else if ($level == 1) {
                if ($business != -1) {
                    if ($business && $business != -2) {
                        if (isset($data['channel_id']) && $data['channel_id']) {
                            if (in_array($data['channel_id'], $business)) {
                                $where['b_channel_id'] = ['in', $data['channel_id']];
                            } else {
                                $where['b_channel_id'] = ['in',[-1]];
                            }
                        } else {
                            $where['b_channel_id'] = ['in', $business];
                        }
                    } else {
                        $where['b_channel_id'] = ['in',[-1]];
                    }
                } else {
                    if (isset($data['channel_id']) && $data['channel_id']) {
                        $where['b_channel_id'] = ['in', $data['channel_id']];
                    }
                }
                $field = ',b_channel_id as channel_id';
                $group = 'b_channel_id';
            } else if ($level == 2) {

                if ($business != -1) {
                    if ($business && $business != -2) {
                        if (isset($data['channel_id']) && $data['channel_id']) {
                            if (in_array($data['channel_id'], $business)) {
                                $where['bz_channel_id'] = ['in', $data['channel_id']];
                            } else {
                                $where['bz_channel_id'] = ['in',[-1]];
                            }
                        } else {
                            $where['bz_channel_id'] = ['in', $business];
                        }
                    } else {
                        $where['bz_channel_id'] = ['in',[-1]];
                    }
                } else {
                    if (isset($data['channel_id']) && $data['channel_id']) {
                        $where['bz_channel_id'] = ['in', $data['channel_id']];
                    }
                }

                $field = ',bz_channel_id as channel_id';
                $group = 'bz_channel_id';
            }

            if (request()->isAjax()) {
                $sql = model('common/Retaine')->field('type_id,day,bz_channel_id,b_channel_id,s_channel_id,sum(reg_num) as reg_num,sum(role_num) as role_num,sum(act_num) as act_num,sum(recharge_num) as recharge_num,sum(pay_num) as pay_num' . $field)->where($where)->group($group)->order('day desc')->fetchSql(true)->select();
                if ((new MakeReportGo())->addTask('retaine', $sql, session_id(), ['Level' => $level, 'TypeId' => $type_id])) {
                    $this->success('报表生成的任务已经提交, 报表生成完成后，会及时通知您，请耐心稍等');
                } else {
                    $this->error('报表生成任务不可重复提交，如遇到无法导出情况，建议修改查询条件解除当前状态，提交重新生成报表任务！');
                }
            }

            $total = model('common/Retaine')->where($where)->field('sum(reg_num) as reg_num,sum(role_num) as role_num,sum(recharge_num) as recharge_num,sum(act_num) as act_num')->find();
            $sql = model('common/Retaine')->field('type_id,day,bz_channel_id,b_channel_id,s_channel_id,sum(reg_num) as reg_num,sum(role_num) as role_num,sum(act_num) as act_num,sum(recharge_num) as recharge_num,sum(pay_num) as pay_num' . $field)->where($where)->group($group . ',day')->where($where)->buildSql();
            $list = Db::table($sql . ' a')->order('day desc')->paginate(20, false, ['query' => input('get.')])->each(function ($item, $key) {
                $item['rate'] = $item['act_num'] > 0 ? priceFormat(100 * $item['pay_num'] / $item['act_num']) : '0.00';//付费人数/活跃人数
                $item['arpu'] = $item['act_num'] > 0 ? priceFormat($item['recharge_num'] / $item['act_num']) : '0.00';//充值金额/活跃玩家
                $item['arppu'] = $item['pay_num'] > 0 ? priceFormat($item['recharge_num'] / $item['pay_num']) : '0.00';//	充值金额/付费人数

                return $item;
            });


        } else {

            if (request()->isAjax()) {
                $sql = model('common/Retaine')->where($where)->order('day desc')->fetchSql(true)->select();
                if ((new MakeReportGo())->addTask('retaine', $sql, session_id(), ['Level' => $level, 'TypeId' => $type_id])) {
                    $this->success('报表生成的任务已经提交, 报表生成完成后，会及时通知您，请耐心稍等');
                } else {
                    $this->error('报表生成任务不可重复提交，如遇到无法导出情况，建议修改查询条件解除当前状态，提交重新生成报表任务！');
                }
            }

            $total = model('common/Retaine')->where($where)->field('sum(reg_num) as reg_num,sum(role_num) as role_num,sum(recharge_num) as recharge_num,sum(act_num) as act_num')->find();

            $list = model('common/Retaine')->where($where)->order('day desc')->paginate(20, false, ['query' => input('get.')])->each(function ($item, $key) {
                $item['rate'] = $item['act_num'] > 0 ? priceFormat(100 * $item['pay_num'] / $item['act_num']) : '0.00';// 付费人数/活跃人数
                $item['arpu'] = $item['act_num'] > 0 ? priceFormat($item['recharge_num'] / $item['act_num']) : '0.00';// 充值金额/活跃玩家
                $item['arppu'] = $item['pay_num'] > 0 ? priceFormat($item['recharge_num'] / $item['pay_num']) : '0.00';//	充值金额/付费人数
                return $item;
            });
        }


        return [$list, $total];
    }
}