gganli/application/index/controller/Datacount.php

<?php

namespace app\index\controller;

use app\common\controller\Frontend;
use PHPExcel_IOFactory;

require_once VENDOR_PATH . 'phpoffice/phpexcel/Classes/PHPExcel.php';

class Datacount extends Frontend
{

    protected $noNeedLogin = '*';
    protected $noNeedRight = '*';
    protected $layout = '';

    public function index()
    {
        return $this->view->fetch();
    }

    /**
     * 收益数据导出
     */
    public function explode() {
        // 先做统计
        $p_id = $this->request->request('p_id',0,"intval"); // 派对ID
        $startdate = $this->request->request('starttime'); // 起始时间
        $enddate = $this->request->request('endtime'); // 结束时间
        if($p_id <=0 || !$startdate || !$enddate) $this->error("请选择起始时间！");
        $starttime = strtotime($startdate);
        $endtime = strtotime($enddate);
        if(strlen($starttime) != 10 || strlen($endtime) != 10) $this->error("时间格式有误！");
        $partyInfo = \app\common\model\Party::where(["id"=>$p_id])->find();
        $p_id = $partyInfo->party_id;
        $party_id = $partyInfo->id;
        // 查询连麦数据
        global $where;
        global $whereOr;
        $where = [];
        $where["party_id"] = $party_id;
        $where["offsite_time"] = ["gt",0];
        $where["onsite_time"] = [["gt",$starttime],["lt",$endtime]];

        $whereOr["party_id"] = $party_id;
        $whereOr["offsite_time"] = ["gt",0];
        $whereOr["offsite_time"] = [["gt",$starttime],["lt",$endtime]];
        $userOnlieTime = \app\common\model\UserOnsiteTime::
            where(function ($query) {
                global $where;
                $query->where($where);
            })
            ->whereOr(function ($query) {
                global $whereOr;
                $query->where($whereOr);
            })->select();

        $sitetimeList = [];
        if($userOnlieTime) foreach($userOnlieTime as $k => $v) {
            $onsite_time = $v["onsite_time"]<$starttime?$starttime:$v["onsite_time"];
            $offsite_time = $v["offsite_time"]>$endtime?$endtime:$v["offsite_time"];
            $site_time = $offsite_time-$onsite_time;
            isset($sitetimeList[$v["user_id"]]) && $sitetimeList[$v["user_id"]] += $site_time;
            isset($sitetimeList[$v["user_id"]]) || $sitetimeList[$v["user_id"]] = $site_time;
        }
        // 获取收礼声币数
        $where = [];
        $where["a.party_id"] = $party_id;
        $where["a.createtime"] = [["gt",$starttime],["lt",$endtime]];;
        $profitloglist = \app\common\model\UserProfitLog::alias("a")
            ->field("a.user_id,u.u_id,u.nickname,sum(gift_value) as gift_value,sum(guilder_value) as guilder_value")
            ->join("hx_user u","u.id = a.user_id")
            ->where($where)->group("a.user_id")
            ->select();
        $profit = [];
        $party_all_value = 0;
        $guilder_all_value = 0;
        if($profitloglist) foreach($profitloglist as $k => $v) {
            $sitetime = isset($sitetimeList[$v["user_id"]])?$sitetimeList[$v["user_id"]]:0;
            $profit[] = [
                "user_id" => $v["user_id"],
                "gift_value" => $v["gift_value"],
                "guilder_value" => $v["guilder_value"],
                "u_id" => $v["u_id"],
                "nickname" => $v["nickname"],
                "sitetime" => intval($sitetime/60)
            ];
            $party_all_value += $v["gift_value"];
            $guilder_all_value += $v["guilder_value"];

        }

//        print_r($profit);exit;
        // 获取派对信息
        $party_id = $p_id;
        $party_name = \app\common\model\Party::where(["party_id"=>$p_id])->value("party_name");
        // 导出数据
        $title = $startdate."—".$enddate." 房间营收数据";

        //实例化phpexcel对象
        $objPHPExcel = new \PHPExcel();


        //设置Excel属性
        $objPHPExcel->getProperties()->setTitle($title);		//设置标题

        $objPHPExcel->getActiveSheet()->mergeCells('A1:F1');
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10);   //设置宽度
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);   //设置宽度
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(25);   //设置宽度
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);   //设置宽度
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);   //设置宽度
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);   //设置宽度
        $objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal("left");//水平方向上靠左
        $objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal("left");//水平方向上靠左
        $objPHPExcel->getActiveSheet()->getStyle('C')->getAlignment()->setHorizontal("left");//水平方向上靠左
        $objPHPExcel->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal("left");//水平方向上靠左
        $objPHPExcel->getActiveSheet()->getStyle('E')->getAlignment()->setHorizontal("left");//水平方向上靠左
        $objPHPExcel->getActiveSheet()->getStyle('F')->getAlignment()->setHorizontal("left");//水平方向上靠左

        // 给表格添加数据
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1',$title);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A2',"房间ID");
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B2',"房间名称");
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C2',"收礼总声币数");
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D2',"房主抽成声币数");
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A3',$party_id);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B3',$party_name);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C3',$party_all_value);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D3',$guilder_all_value);

        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A5',"序号");
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B5',"连麦用户");
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C5',"用户昵称");
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D5',"连麦时长（分钟）");
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E5',"收礼总声币数");
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('F5',"收礼抽成声币数");

        //处理数据
        $j = 6;//行数
        foreach($profit as $key => $val){
            $objPHPExcel->getActiveSheet()
                ->setCellValue("A".$j,$key+1)
                ->setCellValue("B".$j,$val['u_id'])
                ->setCellValue("C".$j,$val['nickname'])
                ->setCellValue("D".$j,$val['sitetime'])
                ->setCellValue("E".$j,$val['gift_value'])
                ->setCellValue("F".$j,$val['guilder_value']);
            $j++;
        }

        //激活当前表
        $objPHPExcel->setActiveSheetIndex(0);
        ob_end_clean();//清除缓冲区,避免乱码

        //弹出提示下载文件
        header('pragma:public');
        header("Content-Disposition:attachment;filename=".$title.".xlsx");
        header('Cache-Control: max-age=0');
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save( 'php://output');

    }

}