<?php

namespace app\admin\controller\data;

use app\common\controller\Backend;
use think\Db;
use PHPExcel_IOFactory;
/**
 * 课程数据
 * @icon fa fa-circle-o
 */
class Course extends Backend
{
    protected $model = null;

    public function _initialize()
    {
        parent::_initialize();
        $this->model = new \app\admin\model\data\Study();
        $this->timeModel = new \app\admin\model\data\Time();
    }


    /**
     * 学习详情
     * @remark 指定课程所属的用户学习情况
     * @return mixed
     */
    public function studyDetail(){
        //当前是否为关联查询
        $this->relationSearch = false;
        //设置过滤方法
        $this->request->filter(['strip_tags', 'trim']);
        //如果发送的来源是Selectpage，则转发到Selectpage
        if ($this->request->request('keyField')) {
            return $this->selectpage();
        }

        $timeFilter = $this->request->post('time_filter/a',[]);
        $timeWhere = [];
        if($timeFilter && $timeFilter['time']){

            if(is_string($timeFilter['time'])){
                $timeFilter['time'] = [$timeFilter['time']];
            }

            if(count($timeFilter['time']) == 1){
                $time = $timeFilter['time'][0];
            }
            $time = $this->timeModel->getFilterStartEndTime($time,$timeFilter['unit']);

            $timeWhere = [
                'start_time'=>['between',[$time['start'],$time['end']]]
            ];
        }

        $groupType = input('group_type','user');
        $courseId = input('course_id');
        $userId = input('user_id');

        list($where, $sort, $order, $offset, $limit) = $this->buildparams();


        $whereExtend = [
            'study.uniacid'=>UNIACID,
        ];

        if($courseId){
            $whereExtend['course_id']=$courseId;
        }

        if($userId){
            $whereExtend['user_id']=$userId;
        }

        $list = $this->model
            ->setUniacid(false)
            ->with([$groupType == 'user' ? 'user' : 'course'])
            ->where($where)
            ->where($timeWhere)
            ->where($whereExtend)
            ->field('user_id, COUNT(*) as study_num, SUM(total_time) AS total_study_time, MIN(start_time) AS first_study_time, MAX(end_time) AS last_study_time')
            ->group($groupType == 'user' ? 'user_id' : 'course_id')
            ->order($sort, $order)
            ->paginate($limit);

        foreach ($list as &$row) {
            $row->total_study_time = secondToMinute($row->total_study_time);

            if($groupType == 'user'){
                $row->getRelation('user')->visible(\app\admin\model\User::$listShowField);
            }else{
                $row->getRelation('course')->visible(['cover','type','name']);
            }
        }

        $result = array("total" => $list->total(), "rows" => $list->items());

        return $this->success("获取成功",$result);
    }


    protected $exportIndex = 0;
    /**
     * 导出用户
     * @return void
     */
    public function exportStudy(){
        $courseId = input('course_id');
        list($where, $sort, $order, $offset, $limit) = $this->buildparams();

        $whereExtend = [
            'study.uniacid'=>UNIACID,
        ];

        $timeFilter = $this->request->post('time_filter/a',[]);
        if($timeFilter && $timeFilter['time']){

            if(is_string($timeFilter['time'])){
                $timeFilter['time'] = [$timeFilter['time']];
            }

            if(count($timeFilter['time']) == 1){
                $time = $timeFilter['time'][0];
            }
            $time = $this->timeModel->getFilterStartEndTime($time,$timeFilter['unit']);

            $whereExtend['start_time'] = ['between',[$time['start'],$time['end']]];
        }


        $groupType = input('group_type','user');
        $userId = input('user_id');
        if($courseId){
            $whereExtend['course_id']=$courseId;
        }
        if($userId){
            $whereExtend['user_id']=$userId;
        }

        $excelTitle = '课程学习数据【'.date('Y-m-d h:i',time()).'】';

        $objPHPExcel = new \PHPExcel();
        //设置Excel属性
        $objPHPExcel->getProperties()
            ->setTitle($excelTitle);		//设置标题

        $objPHPExcel->getActiveSheet()->setCellValue('A1', '学员用户名');//可以指定位置
        if($groupType == 'user'){
            $objPHPExcel->getActiveSheet()->setCellValue('B1', '昵称');
            $objPHPExcel->getActiveSheet()->setCellValue('C1', '学员手机号');
        }else{
            $objPHPExcel->getActiveSheet()->setCellValue('B1', '课程名称');//可以指定位置
            $objPHPExcel->getActiveSheet()->setCellValue('C1', '课程类型');
        }
        $objPHPExcel->getActiveSheet()->setCellValue('D1', '累计学习时长(分)');
        $objPHPExcel->getActiveSheet()->setCellValue('E1', '首次学习时间');
        $objPHPExcel->getActiveSheet()->setCellValue('F1', '最后学习时间');
        $objPHPExcel->getActiveSheet()->setCellValue('G1', '打开次数');
        $objPHPExcel->getActiveSheet()->setCellValue('H1', '来源');

        // 给表格添加数据
            $this->model
                ->setUniacid(false)
                ->with(['course','user'])
                ->where($where)
                ->where($whereExtend)
                ->field('user_id, COUNT(*) as study_num, SUM(total_time) AS total_study_time, MIN(start_time) AS first_study_time, MAX(end_time) AS last_study_time')
                ->group($groupType == 'user' ? 'user_id' : 'course_id')
                ->chunk(30,function($list) use ($objPHPExcel,$groupType) {
                    if($list){
                        foreach ($list as $key => $item){

                            $objPHPExcel->getActiveSheet()->setCellValue('A'.(2 + $this->exportIndex), $item['user']['username']);
                            if($groupType == 'user'){
                                $objPHPExcel->getActiveSheet()->setCellValue('B'.(2 + $this->exportIndex), $item['user']['nickname']);
                                $objPHPExcel->getActiveSheet()->setCellValue('C'.(2 + $this->exportIndex), $item['user']['mobile']);
                            }else{
                                $objPHPExcel->getActiveSheet()->setCellValue('B'.(2 + $this->exportIndex), $item['course']['name']);
                                $objPHPExcel->getActiveSheet()->setCellValue('C'.(2 + $this->exportIndex), __($item['course']['type']));
                            }

                            $item['total_study_time'] = secondToMinute($item['total_study_time'],1);

                            $objPHPExcel->getActiveSheet()->setCellValue('D'.(2 + $this->exportIndex), $item['total_study_time']);
                            $objPHPExcel->getActiveSheet()->setCellValue('E'.(2 + $this->exportIndex), date('Y-m-d H:i:s',$item['start_time']));
                            $objPHPExcel->getActiveSheet()->setCellValue('F'.(2 + $this->exportIndex), date('Y-m-d H:i:s',$item['last_study_time']));
                            $objPHPExcel->getActiveSheet()->setCellValue('G'.(2 + $this->exportIndex), $item['study_num']);
                            $objPHPExcel->getActiveSheet()->setCellValue('H'.(2 + $this->exportIndex), $item['user']['platform'] ? __($item['user']['platform']) : '默认渠道');
                            $this->exportIndex++;
                        }
                    }

                },$this->model->getTable().'.id','desc');

        //设置字体
        $objPHPExcel->getActiveSheet()->getStyle('A1:H1')->getFont()->setBold(true)->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_BLACK);
        //设置水平居中
        $objPHPExcel->getActiveSheet()->getStyle('A1:H1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //设置背景颜色
        $objPHPExcel->getActiveSheet()->getStyle('A1:H1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('D9FFC125');
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(26);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(18);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(18);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(18);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(12);
        $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(12);
        //激活当前表
        $objPHPExcel->setActiveSheetIndex(0);
        ob_end_clean();//清除缓冲区,避免乱码

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

        $objWriter->save( 'php://output');
    }
}