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