php 统计代码

缺乏、安全感 2022-05-16 02:38 219阅读 0赞

<?php
    /**
     * Created by PhpStorm.
     * User: Administrator
     * Date: 2018/8/13
     * Time: 11:15
     */
    
    namespace console\controllers;
    
    use Yii;
    use yii\db\Query;
    
    class GameStatController extends BaseController
    {
        public $db = '';
    
        public function init(){
            parent::init();
            $this->db = Yii::$app->btbox_db;
        }
    
        /**
         * 生成游戏统计数据
         * @param string $time
         * @author:mzc
         * @date:2018/8/13 20:58
         * @return:
         */
        public function actionGenStat($time=''){
            try{
                $this->_genStat($time);
            }catch (\Exception $ex){
                self::repError($ex->getMessage());
            }
        }
    
        /**
         * 生成半年内游戏统计的数据
         * @author:mzc
         * @date:2018/8/13 20:58
         * @return:
         */
        public function actionGenHalfYear(){
            $time = strtotime('-6 month',time());//半年前的时间戳
            $time = strtotime(date('Y-m-d 00:00:00',$time));
            do{
                try{
                    $this->_genStat($time);
                }catch (\Exception $ex){
                    Yii::error('halfyear:'.date('Y-m-d',$time).':'.$ex->getMessage(),LOG_CATE_CRON);
                }
                $time = strtotime('+1 day',$time);
            }while (date('Ymd', $time) != date('Ymd', time()));
        }
    
        /**
         * 统计独代游戏每天的留存率
         * @param string $time
         * @author:mzc
         * @date:2018/8/14 14:20
         * @return:
         */
        public function actionGenRemain($time=''){
            try{
                $this->_genRemain($time);
                self::repSuccess('留存率生成成功');
            }catch(\Exception $ex){
                self::repError($ex->getMessage());
            }
        }
    
        /**
         * 统计半年内游戏每一天的留存率
         * @author:mzc
         * @date:2018/8/14 14:20
         * @return:
         */
        public function actionGenHalfYearRemain(){
            $time = strtotime('-6 month',time());//半年前的时间戳
            do{
                try{
                    $this->_genRemain($time);
                }catch (\Exception $ex){
                    Yii::error('halfyear-remain:'.date('Y-m-d',$time).':'.$ex->getMessage(),LOG_CATE_CRON);
                }
                $time = strtotime('+1 day',$time);
            }while (date('Ymd', $time) != date('Ymd', time()));
    //        }while (date('Ymd', $time) != '20180216');
            self::repSuccess('留存率生成成功');
        }
        
        private function _genRemain($ctime=''){
            $db = $this->db;
    
            $time = strtotime(date('Y-m-d 00:00:00',time()));
            if(!empty($ctime)){
                $time = strtotime(date('Y-m-d 00:00:00',$ctime));
            }
    
            try{
                // 第一天注册的用户
                $preSevenDay = strtotime('-7 day', $time);
                list($stime, $etime) = $this->_getStartEndTime($preSevenDay);
                $regUserIds = $this->getNewAcounts($stime, $etime);
    
                // 前6天日期,次日登录的用户
                $preSixDay = strtotime('-6 day', $time);
                list($stime, $etime) = $this->_getStartEndTime($preSixDay);
                $secondLoginUids = $this->getActivity($stime, $etime);
    
                // 前3天日期,第三天登录的用户
                $preThreeDay = strtotime('-4 day', $time);
                list($stime, $etime) = $this->_getStartEndTime($preThreeDay);
                $threeLoginUids = $this->getActivity($stime, $etime);
    
                // 第7天登录的用户
                list($stime, $etime) = $this->_getStartEndTime($time);
                $sevendLoginUids = $this->getActivity($stime, $etime);
    
                $games = $this->getSoleGames();
    
                if(empty($games)) throw new \Exception("没有可统计的游戏");
    
                foreach($games as $one){
                    $gameid = $one['id'];
                    $nextdayremains = $threedaysremains = $sevendaysremains = 0;
    
                    $regUserId = !empty($regUserIds[$gameid]) ? $regUserIds[$gameid] : 0;
    
                    $secondLoginUid = !empty($secondLoginUids[$gameid]) ? $secondLoginUids[$gameid] : 0;
                    $threeLoginUid = !empty($threeLoginUids[$gameid]) ? $threeLoginUids[$gameid] : 0;
                    $sevendLoginUid = !empty($sevendLoginUids[$gameid]) ? $sevendLoginUids[$gameid] : 0;
    
                    if($regUserId){
                        $nextdayremains = round($secondLoginUid/$regUserId,3);
                        $threedaysremains = round($threeLoginUid/$regUserId,3);
                        $sevendaysremains = round($sevendLoginUid/$regUserId,3);
                    }
    
                    $db->createCommand()->update('cy_game_summary_stat', [
                        'nextdayremains' => $nextdayremains,
                        'threedaysremains' => $threedaysremains,
                        'sevendaysremains' => $sevendaysremains
                    ], [
                        'gameid'=>$gameid,
                        'date_at'=>$time
                    ])->execute();
                }
            }catch (\Exception $ex){
                throw new \Exception($ex->getMessage());
            }
        }
    
        /**
         * 获取开始与结束时间
         * @param $time
         * @author:mzc
         * @date:2018/8/14 14:26
         * @return:
         */
        private function _getStartEndTime($time){
            $startTime = strtotime(date('Y-m-d 00:00:00'),time());
            $endTime = time();
    
            if(!empty($time)){
                $startTime = strtotime(date('Y-m-d 00:00:00',$time));
                $endTime = strtotime(date('Y-m-d 23:59:59',$time));
            }
            return [$startTime,$endTime];
        }
    
        /**
         * 判断当天该游戏是否有记录，没有添加，有先删除再添加
         * @param $time 时间戳
         * @author:mzc
         * @date:2018/8/13 11:19
         * @return:
         */
        private function _genStat($time=''){
            $db = $this->db;
    
            list($startTime,$endTime) = $this->_getStartEndTime($time);
    
            try{
                //1.查询游戏
                $games = $this->getGames();
                if(empty($games)) throw new \Exception("没有可统计的游戏");
    
                $newUsers = $this->getNewAcounts($startTime,$endTime);//2.新增账号
                list($totalDevices,$andriodNews,$iosNews) = $this->getNewDevices($startTime,$endTime); //3.新增设备
                $actives = $this->getActivity($startTime,$endTime);//3.获取活跃账号
                list($payMans,$payNums,$payTotals) = $this->getPay($startTime,$endTime); //4.付费人数，付费次数,总付费金额
                $ptbs = $this->getPtb($startTime,$endTime);//6.平台币金额
    
                list($newPayMans,$newPayNums,$newPayTotals) = $this->getNewIncreated($startTime,$endTime);//新增付费人数$newPayMans，新增付费次数$newPayNums，新增付费总金额$newPayTotals
    
                foreach($games as $one){
                    $gameid = $one['id'];
                    $newCounts = !empty($newUsers[$gameid]) ? $newUsers[$gameid] : 0;
                    $newDevices = !empty($totalDevices[$gameid]) ? count($totalDevices[$gameid]) : 0;
                    $andriodNew = !empty($andriodNews[$gameid]) ? count($andriodNews[$gameid]) : 0;
                    $iosNew = !empty($iosNews[$gameid]) ? count($iosNews[$gameid]) : 0;
                    $activity =  !empty($actives[$gameid]) ? $actives[$gameid] : 0;;
                    $payMan = !empty($payMans[$gameid]) ? $payMans[$gameid] : 0;
                    $payNum = !empty($payNums[$gameid]) ? $payNums[$gameid] : 0;
                    $payAmount = !empty($payTotals[$gameid]) ? $payTotals[$gameid] : 0;
                    $ptb = !empty($ptbs[$gameid]) ? $ptbs[$gameid] : 0;
    
                    $newPayMan = !empty($newPayMans[$gameid]) ? $newPayMans[$gameid] : 0;
                    $newPayNum = !empty($newPayNums[$gameid]) ? $newPayNums[$gameid] : 0;
                    $newPayTotal = !empty($newPayTotals[$gameid]) ? $newPayTotals[$gameid] : 0;
    
                    //有记录先删除再添加
                    $exists = (new Query())->from('cy_game_summary_stat')
                        ->where(['between', 'date_at', $startTime, $endTime])
                        ->andWhere([ 'gameid'=>$gameid])->exists($db);
    
                    if($exists){
                        $sql="DELETE FROM cy_game_summary_stat WHERE gameid = '".$gameid."' AND date_at BETWEEN ".$startTime." AND ".$endTime;
                        $db->createCommand($sql)->execute();
                    }
    
                    $datas[] = [
                        'gameid'=>$gameid,
                        'gamename'=>$one['name'],
                        'sumpaycount'=>$payNum,
                        'newsdevice'=>$newDevices,
                        'newsuser'=>$newCounts,
                        'sumpay'=>$payMan,
                        'active'=>$activity,
                        'sumpayamount'=>$payAmount,
                        'ptbamount'=>$ptb,
                        'date_at'=>$startTime,
                        'newspay'=>$newPayMan,
                        'newspaycount'=>$newPayNum,
                        'newspayamount'=>$newPayTotal,
                        'userandroid'=>$andriodNew,
                        'userios'=>$iosNew,
                    ];
                }
    
                //3.判断当天该游戏是否有记录，没有添加，有修改对应数据
                if (!empty($datas)) {
                    $column = array_keys($datas[0]);
                    $db->createCommand()->batchInsert('cy_game_summary_stat', $column, $datas)->execute();
                }
    
                $msg = date('Y-m-d').':生成游戏统计数据成功';
                Yii::info($msg, LOG_CATE_CRON);
            }catch (\Exception $ex){
                throw new \Exception($ex->getMessage());
            }
        }
    
        /**
         * 获取游戏
         * @author:mzc
         * @date:2018/8/13 14:44
         * @return:
         */
        private  function getGames(){
            $db = $this->db;
            $result = (new Query())
                ->select('*')
                ->from('cy_game')
                ->where(['isdelete' => 0])
                ->all($db);
            return $result;
        }
    
        /**
         * 获取独代游戏
         * @author:mzc
         * @date:2018/8/14 14:11
         * @return:
         */
        private  function getSoleGames(){
            $db = $this->db;
            $result = (new Query())
                ->select('a.id,a.name')
                ->from('cy_game a')
                ->leftJoin('cy_business b','a.origin=b.id')
                ->where(['a.isdelete' => 0])
                ->where(['a.id' => 2])
                ->andWhere(['like','b.cpinfo','墨仙互娱'])
                ->all($db);
    
            return $result;
        }
    
    
        
        /**
         * 新增账号
         * @param $gameid
         * @param $startTime
         * @param $endTime
         * @author:mzc
         * @date:2018/8/13 15:09
         * @return:
         */
        private function getNewAcounts($startTime,$endTime){
            $db = $this->db;
            $result = (new Query())
                ->select('gameid,count(*) as num')
                ->from('cy_members')
                ->where(['between', 'reg_time', $startTime, $endTime])
                ->groupBy('gameid')
                ->all($db);
            $data = [];
            if(!empty($result)){
                $gameid = array_column($result,'gameid');
                $num = array_column($result,'num');
                $data = array_combine($gameid,$num);
            }
            return $data;
        }
    
        /**
         * 新增设备，andriod新增，ios新增
         * @param $startTime
         * @param $endTime
         * @author:mzc
         * @date:2018/8/13 15:09
         * @return:
         */
        private function getNewDevices($startTime,$endTime){
            $db = $this->db;
            $total = $andriodAdd = $iosAdd = [];
    
            $result = (new Query())
                ->select('id,gameid,imeil,device')
                ->from('cy_members')
                ->where(['between', 'reg_time', $startTime, $endTime])
                ->groupBy('gameid,imeil,device')
                ->all($db);
    
            if(!empty($result)){
                foreach($result as $v){
                    $gameid = $v['gameid'];
                    if ($v['device'] == 2) $andriodAdd[$gameid][] = $v['id'];
                    if ($v['device'] == 3) $iosAdd[$gameid][] = $v['id'];
                    $total[$gameid][] =  $v['id'];
                }
            }
    
            return [$total,$andriodAdd,$iosAdd];
        }
    
        /**
         * 获取活跃人数
         * @param $gameid
         * @param $startTime
         * @param $endTime
         * @author:mzc
         * @date:2018/8/13 15:45
         * @return:
         */
        private function getActivity($startTime,$endTime){
            $db = $this->db;
            $result = (new Query())
                ->select('gameid,count(distinct(userid)) as num')
                ->from('cy_logininfo')
                ->where(['between', 'login_time', $startTime, $endTime])
                ->groupBy('gameid')
                ->all($db);
    
            $data = [];
            if(!empty($result)){
                $gameid = array_column($result,'gameid');
                $num = array_column($result,'num');
                $data = array_combine($gameid,$num);
            }
            return $data;
        }
    
        /**
         * 付费人数，付费次数,付费总金额
         * @param $gameid
         * @param $startTime
         * @param $endTime
         * @param $usernames 用户
         * @author:mzc
         * @date:2018/8/13 15:54
         * @return:
         */
        private function getPay($startTime,$endTime,$usernames=[]){
            $db = $this->db;
    
            //付费人数,//付费次数
            $result = (new Query())
                ->select('gameid,count(distinct(username)) as payMan,count(*) as payNum,sum(amount) as payTotal')
                ->from('cy_pay_ok')
                ->where(['between', 'create_time', $startTime, $endTime])
                ->andFilterWhere(['in','username',$usernames])
                ->groupBy('gameid')
                ->all($db);
    
            $payMans = $payNums = $payTotals= [];
            if(!empty($result)){
                $gameid = array_column($result,'gameid');
                $payMan = array_column($result,'payMan');
                $payMans = array_combine($gameid,$payMan);
    
                $payNum = array_column($result,'payNum');
                $payNums = array_combine($gameid,$payNum);
    
                $payTotal = array_column($result,'payTotal');
                $payTotals = array_combine($gameid,$payTotal);
    
            }
    
            return [$payMans,$payNums,$payTotals];
        }
    
    
        /**
         * 获取平台币金额
         * @param $gameid
         * @param $startTime
         * @param $endTime
         * @author:mzc
         * @date:ct
         * @return:
         */
        private function getPtb($startTime,$endTime){
            $db = $this->db;
            $result = (new Query())
                ->select('gameid,sum(amount) as total')
                ->from('cy_pay')
                ->where(['between', 'create_time', $startTime, $endTime])
                ->andWhere(['status' => 1, 'paytype' => 'ptb'])
                ->groupBy('gameid')
                ->all($db);
    
            $data = [];
            if(!empty($result)){
                $gameid = array_column($result,'gameid');
                $num = array_column($result,'total');
                $data = array_combine($gameid,$num);
            }
            return $data;
        }
    
        /**
         * 获取新增付费人数，新增付费次数，新增付费总金额
         * @param $gameid
         * @param $startTime
         * @param $endTime
         * @author:mzc
         * @date:2018/8/13 16:58
         * @return:
         */
        private function getNewIncreated($startTime,$endTime){
            $db = $this->db;
    
            //获取今日新增且有付费的用户
            $usernames = (new Query())
                ->select('distinct(a.username) as username')
                ->from('cy_pay_ok a')
                ->leftJoin('cy_members b','b.username = a.username')
                ->where(['between', 'a.create_time', $startTime, $endTime])
                ->andWhere(['between', 'b.reg_time', $startTime, $endTime])
                ->column($db);
    
            list($newPayMan,$newPayNum,$newPayTotal) = $this->getPay($startTime,$endTime,$usernames);
    
            return [$newPayMan,$newPayNum,$newPayTotal];
        }
    }

<?php
    /**
     * Created by mxhy.
     * 
     * @FileName : BaseController.php
     * @Author : yzm <970468448@qq.com>
     * @DateTime : 2018/1/20 0020-01-20 12:16 
     */
    
    
    namespace console\controllers;
    
    use Yii;
    use yii\console\Controller;
    
    class BaseController extends Controller
    {
    
        protected static $runTime = null;
        protected $pageSize = 1000; // 每次请求多少数据
    
        function actions()
        {
            self::$runTime = time();
    
            error_reporting(E_ALL);
            ini_set("max_execution_time", "3600");
            ini_set("memory_limit", '2048M');
            set_time_limit(0);
    
            return parent::actions(); // TODO: Change the autogenerated stub
        }
    
        /**
         * 错误响应.
         *
         * @param $data
         */
        public static function repError($data = 'FAIL')
        {
            $msg = is_array($data) ? var_export($data, true) : $data;
            Yii::warning($msg, LOG_CATE_CRON);
            echo $msg;
            exit(1);
    
        }
    
        /**
         * 成功响应.
         *
         * @param $data
         */
        public static function repSuccess($data = 'SUCCESS')
        {
            $msg = is_array($data) ? var_export($data, true) : $data;
            Yii::info($msg, LOG_CATE_CRON);
            echo $msg;
    
            exit(0);
        }
    }