tp3_activity/Application/Admin/Controller/StatisticController.class.php

<?php

namespace Admin\Controller;
use Think\Model;
/**
 * 样式管理
 * @author   brent
 * @version  0.0.1
 */
class StatisticController extends CommonController
{
    protected $table = ''; //表名
	/**
	 * [_initialize 前置操作-继承公共前置方法]
	 * @author   Devil
	 * @blog     http://gong.gg/
	 * @version  0.0.1
	 * @datetime 2016-12-03T12:39:08+0800
	 */
	public function _initialize()
	{
		// 调用父类前置方法
		parent::_initialize();

		// 登录校验
		$this->Is_Login();

		// 权限校验
		$this->Is_Power();
		//要执行的表
		$this->table = M('iptv_users');
	}

    public function orderData()
    {
        $start = I('start_time',date('Y-m-d',strtotime('-8 day', time())));
        $end = I('end_time',date('Y-m-d'));

//        $start = '2019-10-10';
//        $end   = '2019-10-17';

        $start_date = date('Ymd',strtotime($start));
        $end_date = date('Ymd',strtotime($end));

        $where['date(created_at)'] = ['between',[$start,$end]];
        $pvs = []; $uvs =[]; $times = [];
        for ($i=0;$i<=9;$i++) {
            # code...
            if($i==0){
                $table = 'iptv_user_login_log';
            }else{
                $table = 'iptv_user_login_log_'.$i;
            }

            $tmp_pvs   = M($table)->where($where)->group('day')->getField('date(created_at) as day,count(*) as total');
            $tmp_uvs   = M($table)->where($where)->group('day')->getField('date(created_at) as day,count(distinct(uid)) as total');

            foreach ($tmp_pvs as $date => $val) {
                if(isset($pvs[$date])){
                    $pvs[$date] = $pvs[$date] + $val;
                }else{
                    $pvs[$date] = $val;
                }
            }

            foreach ($tmp_uvs as $date => $val) {
                if(isset($uvs[$date])){
                    $uvs[$date] = $uvs[$date] + $val;
                }else{
                    $uvs[$date] = $val;
                }
            }
        }
        unset($where);
        $where['date'] = ['between',[$start_date ,$end_date]];
        $where['status'] = 0;
        $orders = M('iptv_order_own')->where($where)->group('date')->getField('date,count(distinct(uid)) as total');
//        dump($orders);
        $where['status'] = 2;
        $pre_orders = M('iptv_order_own')->where($where)->group('date')->getField('date,count(distinct(uid)) as total');
        $pre_orders_counts = M('iptv_order_own')->where($where)->group('date')->getField('date,count(*) as total');

        unset($where);
        $where['status'] =99;
        $cancel_orders = M('iptv_order_own')->where($where)->group('date')->getField('date,count(distinct(uid)) as total');

        //新增用户数
        unset($where);
        $where['date(created_at)'] = ['between',[$start,$end]];
        $addusers = M('iptv_users')->where($where)->group('day')->getField('date(created_at) as day,count(*) as total');
//        dump($pre_orders);
        // $play_time_sum = M('statistic_source')->where(['date'=>])
        //播放时长
        // var_dump($addusers);
        while ($start_date <= $end_date){
            $dates[] = date('Y-m-d',strtotime($start_date));
            $start_date = date('Ymd',(strtotime($start_date)+24*3600));
        }
        $dates = array_reverse($dates);
        foreach ($dates as $date){
        	$where['created_at'] = ['elt',date('Y-m-d 23:59:59',strtotime($date))]; 
            $data[] = [
                'date'=>$date,
                'pv'=>$pvs[$date] ?:0,
                'uv'=>$uvs[$date]?:0,
                'order'=>$orders[date('Ymd',strtotime($date))]?:0,
                'pre_order'=>$pre_orders[date('Ymd',strtotime($date))]?:0,
                'pre_order_count'=>$pre_orders_counts[date('Ymd',strtotime($date))] ? :0,
                'cancel_order'=>$cancel_orders[date('Ymd',strtotime($date))] ? : 0,
                'add_user'=>$addusers[$date] ? : 0,
                'user_sum'=>M('iptv_users')->where($where)->order('user_id desc')->limit(1)->getField('user_id')

            ];
            // echo M()->getlastSQL();die;
        }
        // dump($data);
        if(I('model')){
            $title = array(
                'date'                      =>  array('col' => 'A', 'name' => '日期'),
                'pv'                        =>  array('col' => 'B', 'name' => 'pv'),
                'uv'                        =>  array('col' => 'C', 'name' => 'uv'),
                'order'                     =>  array('col' => 'D', 'name' => '订购数'),
                'cancel_order'              =>  array('col' => 'E', 'name' => '退订数'),
                'pre_order'                 =>  array('col' => 'F', 'name' => '预订购(去重)'),
                'pre_order_count'           =>  array('col' => 'G', 'name' => '预订购'),
                'add_user'					=>  array('col'	=> 'I',	'name' => '新增用户'	),
                'user_sum'					=>  array('col'	=> 'J',	'name' => '累计用户'	)
            );
            // Excel驱动导出数据
            $excel = new \My\Excel(array('filename' => '金鹰电竞运营数据'.$start.'至'.$end, 'title' => $title, 'data' => $data, 'msg' => L('common_not_data_tips')));
            $excel->Export();
            die;
        }
        $this->assign('start_time',$start);
        $this->assign('end_time',$end);
        $this->assign('dates',$dates);
        $this->assign('data',$data);
        $this->display();
    }
	
}