screen/application/admin/controller/VideoPoint.php

<?php

namespace app\admin\controller;

use app\common\controller\Backend;
use app\common\model\VideoPointUser as VPU;
use app\common\model\Area;
use think\db\Query;

/**
 *
 *
 * @icon fa fa-circle-o
 */
class VideoPoint extends Backend
{

    /**
     * VideoPoint模型对象
     * @var \app\admin\model\VideoPoint
     */
    protected $model = null;

    public function _initialize()
    {
        parent::_initialize();
        $this->model = new \app\admin\model\VideoPoint;

    }

    public function import()
    {
        parent::import();
    }

    /**
     * 默认生成的控制器所继承的父类中有index/add/edit/del/multi五个基础方法、destroy/restore/recyclebin三个回收站方法
     * 因此在当前控制器中可不用编写增删改查的代码,除非需要自己控制这部分逻辑
     * 需要将application/admin/library/traits/Backend.php中对应的方法复制到当前控制器,然后进行修改
     */


    /**
     * 查看
     */
    public function index()
    {
        //当前是否为关联查询
        $this->relationSearch = true;
        //设置过滤方法
        $this->request->filter(['strip_tags', 'trim']);
        if ($this->request->isAjax()) {
            //如果发送的来源是Selectpage，则转发到Selectpage
            if ($this->request->request('keyField')) {
                return $this->selectpage();
            }
            list($where, $sort, $order, $offset, $limit) = $this->buildparams();
            $ids=input('ids');

            $list = $this->model
                    ->with(['video'])
                    ->where(function (Query $query)use ($ids){
                        $ids && $query->where('video_id',$ids);
                    })
                    ->where($where)
                    ->order($sort, $order)
                    ->paginate($limit);

            foreach ($list as $row) {
                $row->visible(['id','type','second','title','items','item_type','key','key_desc','total_time','ave_time','title_img']);
                $row->visible(['video']);
				$row->getRelation('video')->visible(['title','src']);
            }

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

            return json($result);
        }
        return $this->view->fetch();
    }

    public function curve($ids){
        $point=$this->model->findOrFail($ids);
        if($point['type']=='wenda'){
            return '';
        }
        $this->assign('point',$point);
        $this->assign('x',array_keys($point['items']));
        $inputDate=input('time');
        if(!$inputDate){
            $inputDate=date('Y-m-01 00:00:00').' - '.date('Y-m-01 00:00:00',strtotime('+1month',strtotime(date('Y-m-01 00:00:00'))));
        }
        $this->assign('date',$inputDate);
        list($start,$end)=explode(' - ',$inputDate);
        $start=strtotime($start);
        $end=strtotime($end);
        $values=[];
        $total=$point->user()->day([$start,$end])->first()->sex(input('sex'))->filterUserArea(input('province_id'),input('city_id'))->filterUserAge(input('age_start'),input('age_end'))->count();
        foreach ($point['items'] as $key=>$value){
            if($total==0){
                $values[]=0;
                continue;
            }
            $count=$point->user()->day([$start,$end])->first()->sex(input('sex'))->filterUserArea(input('province_id'),input('city_id'))->filterUserAge(input('age_start'),input('age_end'))->whereRaw("FIND_IN_SET('{$key}',`key`)")->count();
            $values[]=bcmul(bcdiv($count,$total,2),100,2);
        }
        $this->assign('value',$values);
        $this->assign('province',Area::pro()->select());
        return view();
    }
    public function rate($ids){
        $point=$this->model->findOrFail($ids);
        if($point['type']=='wenda'){
            return '';
        }
        $this->assign('point',$point);
        $this->assign('x',['回答次数','正确率']);
        $inputDate=input('time');
        if(!$inputDate){
            $inputDate=date('Y-m-01 00:00:00').' - '.date('Y-m-01 00:00:00',strtotime('+1month',strtotime(date('Y-m-01 00:00:00'))));
        }
        $this->assign('date',$inputDate);
        list($start,$end)=explode(' - ',$inputDate);
        $start=strtotime($start);
        $end=strtotime($end);
        $values=[];
        $_total=$point->user()->day([$start,$end])->where('is_first',1)->column('is_right','id');
        $total=count($_total);
        $rightTotal=$_total?(array_count_values(array_values($_total))[1]??0):0;
        foreach ($point['items'] as $key=>$value){
            if($total==0){
                $values[]=0;
                continue;
            }
            $count=$point->user()->whereRaw("FIND_IN_SET('{$key}',`key`)")->count();
            $values[]=bcmul(bcdiv($count,$total,2),100,2);
        }
        $this->assign('value',[$total,$total?bcmul(bcdiv($rightTotal,$total,2),100,2):0]);
        return view();
    }

    public function time($ids){
        $point=$this->model->findOrFail($ids);
        $type=input('type','day');
        $this->assign('point',$point);

        $x=[];
        $values=[];
        $inputDate=input('time');
        if(!$inputDate){
            $inputDate=date('Y-m-01 00:00:00').' - '.date('Y-m-01 00:00:00',strtotime('+1month',strtotime(date('Y-m-01 00:00:00'))));
        }
        $this->assign('date',$inputDate);
        list($start,$end)=explode(' - ',$inputDate);
        $selectDay=strtotime($inputDate);
        $type='day';
        if($type=='day'){
            $temp=[];
            //$day=strtotime(date('Y-m-01',$start));
            //$month=strtotime('+1month',$day);
            $start=strtotime($start);
            $end=strtotime($end);
            for ($i=0;$i<($end-$start)/86400;$i++){
                $temp[]=$a=strtotime("+{$i}days",$start);
                $x[]=date('Y/m/d',$a);
            }
            $data=$point->user()->day([$start,$end])->first()->group('day')->column('avg(time)','day');
            foreach ($temp as $time){
                $values[]=bcadd($data[$time]??0,0);
            }
        }elseif ($type=='month'){
            $month=strtotime(date('Y-01-01',$selectDay));
            $temp=[];
            for ($i=0;$i<12;$i++){
                $temp[]=$a=strtotime("+{$i}month",$month);
                $x[]=date('Y/m',$a);
            }
            $data=$point->user()->where('month','in',$temp)->group('month')->column('avg(time)','month');
            foreach ($temp as $time){
                $values[]=$data[$time]??0;
            }
        }elseif ($type=='year'){
            $limit=7;
            $year=strtotime("-{$limit}year",strtotime(date('Y-01-01',$selectDay)));
            $temp=[];
            for ($i=0;$i<=$limit;$i++){
                $temp[]=$a=strtotime("+{$i}year",$year);
                $x[]=date('Y',$a);
            }
            $data=$point->user()->where('year','in',$temp)->group('year')->column('avg(time)','year');
            foreach ($temp as $time){
                $values[]=$data[$time]??0;
            }
        }

        $this->assign('x',$x);
        $this->assign('value',$values);
        return view();
    }

    public function map($ids){
        $point = $this->model->find($ids);
        if($this->request->isGet()) {
            $this->assign('point', $point);
            return $this->view->fetch();
        }else{
            $data=input('');
            $sex=$data['sex'];
            $ageFrom=$data['age_from'];
            $ageTo=$data['age_to'];
            $time=$data['time'];
            $query=$point
                ->user()
                ->first('a')
                ->alias('a')
                ->join('user b','a.user_id=b.id');
            if($time){
                list($timeStart,$timeEnd)=explode(' - ',$time);
                $query->where('a.created_at','>=',strtotime($timeStart))->where('a.created_at','<=',strtotime($timeEnd));
            }
            if($sex!==''){
                $query->where('b.gender',$sex);
            }
            if($ageFrom){
                $query->where('b.age','>=',$ageFrom);
            }
            if($ageTo){
                $query->where('b.age','<=',$ageTo);
            }
            if($data['level']==1){
                    $query
                        ->join('area c','b.province_id=c.id')
                        ->group('c.id')
                        ->field('c.shortname AS name,COUNT(a.id) AS value');
                $data['province']=json_decode($data['province'],true);
                $result=array_column($query->select()->toArray(),'value','name');
                foreach ($data['province'] as &$area){
                    $area['value']=$result[$area['name']]??0;
                }
                $this->success('','',$data['province']);
            }elseif ($data['level']==2){
                $letter=$data['province_pinyin'];
                if(!$letter){
                    $this->success();
                }
                $json_str=file_get_contents(ROOT_PATH."public/assets/js/map/json/{$letter}.json");
                $cities_temp=json_decode($json_str,true);
                $cities=[];
                foreach ($cities_temp['features'] as $feature){
                    $cities[]=$feature['properties']['name'];
                }
                /*$province_id= Area::where('shortname',$data['province'])->value('id');
                if(!$province_id){
                    $this->error('未找到省份');
                }*/
                //$cities=Area::where('pid',$province_id)->column('name','id');
                if(in_array($data['province'],['北京','上海','重庆','天津'])){
                    $query
                        ->group('c.id')
                        ->join('area c','b.county_id=c.id')
                        ->field('COUNT(b.county_id) as value,c.name')
                        ->whereIn('c.name',$cities);
                    $result=array_column($query->select()->toArray(),'value','name');
                    $change=[];
                    foreach ($cities as $city){
                        $change[]=[
                            'name'=>$city,
                            'value'=>$result[$city]??0
                        ];
                    }
                }else{
                    $query
                        ->group('c.id')
                        ->join('area c','b.city_id=c.id')
                        ->field('COUNT(b.city_id) as value,c.name')
                        ->whereIn('c.name',$cities);
                    $result=array_column($query->select()->toArray(),'value','name');
                    $change=[];
                    foreach ($cities as $city){
                        $change[]=[
                            'name'=>$city,
                            'value'=>$result[$city]??0
                        ];
                    }
                }
                $this->success('','',$change);
            }

            $this->success('','',[]);
        }
    }
}