php 统计代码

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

 

 

PHPStat是一款专业的网站流量统计,网站营销监控,网站用户行为,网站数据分析,互联网广告分析系统,为客户提供深入挖掘的网站流量交叉数据报告.在访客行为分析,网络营销分析和网站决策支持方面有独特的分析体系,为客户找到数据背后的真实有价值的东西,拿出可执行性建议. PHPStat网站流量统计系统 3.2 更新日志: [改变]软件界面,更稳重、直观 [增加]统计站点LOGO [增加]统计图表,分为柱状图、曲线图,自由切换 [改变]左边树性菜单显示方式 [改变]统计概况 [增加]热门标题 [增加]来路域名 [改变]关闭窗口,注销窗口的位置 [修正]省份,城市无法显示详情的错误 [修正]删掉用户后,无法删除其统计信息的错误 [修正]无法在Firefox浏览器登录的问题 [修正]menu.php无法传递参数问题 [修正]ALEAX排名问题 [修正]无法清空以及删除站点问题 [修正]IP数据表文件过大,无法自动安装的问题 [修正]当用户退出后,无法再次登录的问题 [修正]公开统计信息时,游客身份无法查看的问题 [改变]安装文件,把较大的数据文件分割成几个小文件,统计增加了检测安装环境的探针 [增加]限制游客身份不能修改系统信息的检查 [增加]版权信息的加密文件,测试版以及免费版本都无法修改 [增加]多日访问数据自动清理,转变为历史数据,以备查询 [增加]各个访问项目csv,xml,txt文件格式的数据导出 [增加]RSS信息订阅,简要描述访问情况 [增加]国家,省份,城市,地区/接入方式等历史数据明细"
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值