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

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

namespace app\guildapi\controller;

use app\common\library\MakeReportGo;
use think\Db;
use think\JsonException;


class Retaine extends Guild
{
    public function retaineGameChannel()
    {
//        if (input('level')) {
//            $level = input('level');
//        } else {
//            $level = $this->_channelLevel;
//        }
//        if ($this->_channelLevel != $level && !input('channel_id')) {
//            return json(['data' => [], 'code' => 10020, 'msg' => '推广账号必填']);
//        }
        if (input('download') == 1) {
            return $this->retaineGameList(input(), 2);
        } else {
            list($list, $total) = $this->retaineGameList(input(), 2);
            $game_list = model('Common/Game')->getAllByCondition('id,name', ['game_kind' => 1, 'cooperation_status' => ['neq', 0], 'id' => ['in', model('game')->getTakingGame($this->_channelId)]], '', 'self');

            if ($this->_channelLevel == 1) {
                $channelLevel = [
                    ['id' => 1, 'name' => '会长(B)'],
                    ['id' => 2, 'name' => '子会长(B-)'],
                    ['id' => 3, 'name' => '推广员(C)'],
                ];
            } else if ($this->_channelLevel == 2) {
                $channelLevel = [
                    ['id' => 2, 'name' => '子会长(B-)'],
                    ['id' => 3, 'name' => '推广员(C)'],
                ];
            } else if ($this->_channelLevel == 3) {
                $channelLevel = [
                    ['id' => 3, 'name' => '推广员(C)'],
                ];
            }
            return json(['data' => $list, 'code' => 20000, 'msg' => '获取数据成功', 'total' => $total, 'gameList' => $game_list, 'channelLevel' => $channelLevel, 'level' => $this->_channelLevel]);

        }
    }

    private function retaineGameList($data, $type_id)
    {

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

        $channelC = model('common/Channel')->cache("Retaine:retaineGameList", 60)->field('id,name')->order('id desc')->select();
        foreach ($channelC as $v) {
            $channelList[$v['id']] = $v;
        }

        $where['type_id'] = ['=', $type_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 ($data['level']) {
            $level = $data['level'];
        } else {
            $level = $this->_channelLevel;
        }

        if ($data['channel_id']) {
            $channelId = $data['channel_id'];
        } else {
            $channelId = $this->_channelId;
        }


        $business = model('common/channel')->getChannelIds($channelId, $level);

//       var_dump($business);
        $_channelLevel = $this->_channelLevel;
        $_channelId = $this->_channelId;
        if ($type_id == 2 && $level != 3) {

            if ($level == 0) {
                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 {
                    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 && $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 {
                    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 && $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 {
                    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 ($data['download'] == 1) {
                $sql = model('common/RetaineGame')->field('type_id,day,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)->where(function ($query) use ($_channelLevel, $_channelId) {
                    if ($_channelLevel == 1) {
                        $query->where('b_channel_id', $_channelId);
                    } else if ($_channelLevel == 2) {
                        $query->where('bz_channel_id', $_channelId);
                    } else if ($_channelLevel == 3) {
                        $query->where('channel_id', $_channelId);
                    }
                })->group($group . ',game_id,day')->order('day desc,game_id desc')->fetchSql(true)->select();
                if ((new MakeReportGo())->addTask('guild.retaineGame', $sql, 'cps' . $this->_adminId, ['Level' => $level, 'TypeId' => $type_id])) {
                    return json(['data' => '', 'code' => 20000, 'msg' => '报表生成的任务已经提交, 报表生成完成后，会及时通知您，请耐心稍等']);
                } else {
                    return json(['data' => '', 'code' => 20013, 'msg' => '报表生成任务不可重复提交，如遇到无法导出情况，建议修改查询条件解除当前状态，提交重新生成报表任务！']);
                }
            }

            $total = model('common/RetaineGame')->where($where)->where(function ($query) use ($_channelLevel, $_channelId) {
                if ($_channelLevel == 1) {
                    $query->where('b_channel_id', $_channelId);
                } else if ($_channelLevel == 2) {
                    $query->where('bz_channel_id', $_channelId);
                } else if ($_channelLevel == 3) {
                    $query->where('channel_id', $_channelId);
                }
            })->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/RetaineGame')->field('type_id,day,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)->where(function ($query) use ($_channelLevel, $_channelId) {

                if ($_channelLevel == 1) {
                    $query->where('b_channel_id', $_channelId);
                } else if ($_channelLevel == 2) {
                    $query->where('bz_channel_id', $_channelId);
                } else if ($_channelLevel == 3) {
                    $query->where('channel_id', $_channelId);
                }
            })->group($group . ',game_id,day')->where($where)->buildSql();

            $list = Db::table($sql . ' a')->order('day desc,game_id desc')->paginate(['list_rows' => $data['pageSize'], 'page' => $data['page']])->each(function ($item, $key) use ($gameList, $channelList) {
                $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'] = $item['one_stay'] . ($item['role_num'] > 0 ? '(' . priceFormat(100 * $item['one_stay'] / $item['role_num']) . '%)' : '(0.00%)'); //次留
                $item['three_stay'] = $item['three_stay'] . ($item['role_num'] > 0 ? '(' . priceFormat(100 * $item['three_stay'] / $item['role_num']) . '%)' : '(0.00%)'); //三留
                $item['four_stay'] = $item['four_stay'] . ($item['role_num'] > 0 ? '(' . priceFormat(100 * $item['four_stay'] / $item['role_num']) . '%)' : '(0.00%)'); //四留
                $item['five_stay'] = $item['five_stay'] . ($item['role_num'] > 0 ? '(' . priceFormat(100 * $item['five_stay'] / $item['role_num']) . '%)' : '(0.00%)'); //五留
                $item['six_stay'] = $item['six_stay'] . ($item['role_num'] > 0 ? '(' . priceFormat(100 * $item['six_stay'] / $item['role_num']) . '%)' : '(0.00%)'); //六留
                $item['seven_stay'] = $item['seven_stay'] . ($item['role_num'] > 0 ? '(' . priceFormat(100 * $item['seven_stay'] / $item['role_num']) . '%)' : '(0.00%)'); //七留
                $item['fifteen_stay'] = $item['fifteen_stay'] . ($item['role_num'] > 0 ? '(' . priceFormat(100 * $item['fifteen_stay'] / $item['role_num']) . '%)' : '(0.00%)'); //十五留
                $item['thirty_stay'] = $item['thirty_stay'] . ($item['role_num'] > 0 ? '(' . priceFormat(100 * $item['thirty_stay'] / $item['role_num']) . '%)' : '(0.00%)'); //三十留
                $item['game_name'] = isset($gameList[$item['game_id']]) ? $gameList[$item['game_id']]['name'] : '';
                $item['channel_name'] = isset($channelList[$item['channel_id']]) ? $channelList[$item['channel_id']]['name'] : '';
                return $item;
            })->toArray();


        } else {
            if ($level = 3) {
                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 {
                    if (isset($data['channel_id']) && $data['channel_id']) {
                        $where['channel_id'] = ['in', $data['channel_id']];
                    }
                }
            }
            if ($data['download'] == 1) {
                $sql = model('common/RetaineGame')->where($where)->order('day desc,game_id desc')->where(function ($query) use ($_channelLevel, $_channelId) {
                    if ($_channelLevel == 1) {
                        $query->where('b_channel_id', $_channelId);
                    } else if ($_channelLevel == 2) {
                        $query->where('bz_channel_id', $_channelId);
                    } else if ($_channelLevel == 3) {
                        $query->where('channel_id', $_channelId);
                    }
                })->fetchSql(true)->select();
                if ((new MakeReportGo())->addTask('guild.retaineGame', $sql, 'cps' . $this->_adminId, ['Level' => $level, 'TypeId' => $type_id])) {
                    return json(['data' => '', 'code' => 20000, 'msg' => '报表生成的任务已经提交, 报表生成完成后，会及时通知您，请耐心稍等']);
                } else {
                    return json(['data' => '', 'code' => 20013, 'msg' => '报表生成任务不可重复提交，如遇到无法导出情况，建议修改查询条件解除当前状态，提交重新生成报表任务！']);
                }
            }

            $total = model('common/RetaineGame')->where($where)->where(function ($query) use ($_channelLevel, $_channelId) {
                if ($_channelLevel == 1) {
                    $query->where('b_channel_id', $_channelId);
                } else if ($_channelLevel == 2) {
                    $query->where('bz_channel_id', $_channelId);
                } else if ($_channelLevel == 3) {
                    $query->where('channel_id', $_channelId);
                }
            })->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/RetaineGame')->where($where)->where(function ($query) use ($_channelLevel, $_channelId) {
                if ($_channelLevel == 1) {
                    $query->where('b_channel_id', $_channelId);
                } else if ($_channelLevel == 2) {
                    $query->where('bz_channel_id', $_channelId);
                } else if ($_channelLevel == 3) {
                    $query->where('channel_id', $_channelId);
                }
            })->order('day desc,game_id desc')->paginate(['list_rows' => $data['pageSize'], 'page' => $data['page']])->each(function ($item, $key) use ($gameList, $channelList) {
                $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'] = $item['one_stay'] . ($item['role_num'] > 0 ? '(' . priceFormat(100 * $item['one_stay'] / $item['role_num']) . '%)' : '(0.00%)'); //次留
                $item['three_stay'] = $item['three_stay'] . ($item['role_num'] > 0 ? '(' . priceFormat(100 * $item['three_stay'] / $item['role_num']) . '%)' : '(0.00%)'); //三留
                $item['four_stay'] = $item['four_stay'] . ($item['role_num'] > 0 ? '(' . priceFormat(100 * $item['four_stay'] / $item['role_num']) . '%)' : '(0.00%)'); //四留
                $item['five_stay'] = $item['five_stay'] . ($item['role_num'] > 0 ? '(' . priceFormat(100 * $item['five_stay'] / $item['role_num']) . '%)' : '(0.00%)'); //五留
                $item['six_stay'] = $item['six_stay'] . ($item['role_num'] > 0 ? '(' . priceFormat(100 * $item['six_stay'] / $item['role_num']) . '%)' : '(0.00%)'); //六留
                $item['seven_stay'] = $item['seven_stay'] . ($item['role_num'] > 0 ? '(' . priceFormat(100 * $item['seven_stay'] / $item['role_num']) . '%)' : '(0.00%)'); //七留
                $item['fifteen_stay'] = $item['fifteen_stay'] . ($item['role_num'] > 0 ? '(' . priceFormat(100 * $item['fifteen_stay'] / $item['role_num']) . '%)' : '(0.00%)'); //十五留
                $item['thirty_stay'] = $item['thirty_stay'] . ($item['role_num'] > 0 ? '(' . priceFormat(100 * $item['thirty_stay'] / $item['role_num']) . '%)' : '(0.00%)'); //三十留
                $item['game_name'] = isset($gameList[$item['game_id']]) ? $gameList[$item['game_id']]['name'] : '';
                $item['channel_name'] = isset($channelList[$item['channel_id']]) ? $channelList[$item['channel_id']]['name'] : '';
                return $item;
            })->toArray();
        }


        return [$list, $total];
    }

    public function getChannel()
    {

        $business = model('common/channel')->getChannelIds($this->_channelId, input('level', 3));
        $channelWhere = [];
        if ($business != -1) {
            if ($business) {
                $channelWhere['id'] = ['in', $business];
            } else {
                $channelWhere['id'] = -1;
            }
        }

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