jili2/application/admin/controller/Questionnaire.php

<?php
namespace app\admin\controller;

use think\App;
use think\Db;

class Questionnaire extends Auth
{

    public function __construct(App $app = null) {
        parent::__construct($app);
        $this->model= new \app\common\model\Questionnaire();
        $this->table= $this->model->table;
        $this->table1= 'questionnaire_record';

    }
    public function index(){
        if(request()->isAjax()){
            //分页参数
            $length = input('rows',10,'intval');   //每页条数
            $page = input('page',1,'intval');      //第几页
            $start = ($page - 1) * $length;     //分页开始位置
            //排序
            $sortRow = input('sidx','id','trim');      //排序列
            $sort = input('sord','desc','trim');        //排序方式
            $order = $sortRow.' '.$sort;

            $title = input('title','','trim');
            if($title){
                $map[] = ['title','like','%'.$title.'%'];
            }
            $enable = input('enable','','trim');
            if($enable != ''){
                $map[] = ['enable','=',$enable];
            }
            $map[] = ['del','=',0];
            $map[] = ['org_id','=',$this->orgId];
            $map= empty($map) ? true: $map;
            //数据查询
            $lists = db($this->table)->where($map)->limit($start,$length)->order($order)->select();
            foreach ($lists as $k=>$v){
                $lists[$k]['records'] = db('questionnaire_record')
                    ->where('questionnaire_id',$v['id'])
                    ->count();
            }
            //数据返回
            $totalCount = db($this->table)->where($map)->count();
            $totalPage = ceil($totalCount/$length);
            $result['page'] = $page;
            $result['total'] = $totalPage;
            $result['records'] = $totalCount;
            $result['rows'] = $lists;
            return json($result);
        }else{
            $this->assign('m_name','问卷调查列表');
            return $this->fetch();
        }
    }
    public function record($id){
        if(request()->isAjax()){
            //分页参数
            $length = input('rows',10,'intval');   //每页条数
            $page = input('page',1,'intval');      //第几页
            $start = ($page - 1) * $length;     //分页开始位置
            //排序
            $sortRow = input('sidx','id','trim');      //排序列
            $sort = input('sord','desc','trim');        //排序方式
            $order = $sortRow.' '.$sort;

            $title = input('title','','trim');
            if($title){
                $map[] = ['title','like','%'.$title.'%'];
            }
            $enable = input('enable','','trim');
            if($enable != ''){
                $map[] = ['enable','=',$enable];
            }
            $map[] = ['questionnaire_id','=',$id];
            $map= empty($map) ? true: $map;
            //数据查询
            $lists = db($this->table1)->where($map)->limit($start,$length)->order($order)->select();
            //数据返回
            $totalCount = db($this->table1)->where($map)->count();
            $totalPage = ceil($totalCount/$length);
            $result['page'] = $page;
            $result['total'] = $totalPage;
            $result['records'] = $totalCount;
            $result['rows'] = $lists;
            return json($result);
        }else{
            $info = db($this->table)
                ->where('id',$id)
                ->find();
            $this->assign('m_name','['.$info['title'].']'.'回收记录');
            $this->assign('id',$id);
            return $this->fetch();
        }
    }

    /**
     * 新增/编辑
     */
    public function add($id=0){
        if(request()->isPost()){
            $question = input('question');
            $title = input('title');
            $description = input('description');
            if(!$question){
                $this->error('未设置题目');
            }
            if(!$title){
                $this->error('未设置名称');
            }
            $data = [
                'title' => $title,
                'description' => $description,
                'questions' => $question,
                'enable' => 0,
                'user_id' => $this->userId,
                'create_time' => getTime(),
                'org_id' => $this->orgId
            ];
            $ret = db($this->table)->insertGetId($data);
            if($ret){
                $this->success('操作成功');
            }else{
                $this->error('操作失败');
            }
        }else{
            return $this->fetch();
        }
    }

    public function share($id){
        $info =db($this->table)
            ->where('id',$id)
            ->find();
        $strs = aes_encrypt('wj',config('app.encryption_key'));
        $code = getSite().'/h5/Wj/index?id='.$id.'&code='.$strs.'&orgId='.$info['org_id'];
        $this->assign('url',$code);
        return $this->fetch();
    }

    /**
     * 删除记录
     * @param int $id
     */
    public function del($id=0){
        if(!$id){
            $this->error('参数错误');
        }
        $res = db($this->table)->where('id',$id)->setField('del',1);
        if($res){
            $this->success('删除成功');
        }else{
            $this->error('删除失败');
        }
    }


    public function fb($id=0,$status=0){
        $enable = $status==1?0:1;
        $res = db($this->table)->where('id',$id)->setField('enable',$enable);
        if($res){
            $this->success('操作成功');
        }else{
            $this->error('操作失败');
        }
    }
    /**
     * 改变字段值
     * @param int $fv
     * @param string $fn
     * @param int $fv
     */
    public function changeField($id=0,$fn='',$fv=0){
        if(!$fn||!$id){
            $this->error('参数错误');
        }
        $res = db($this->table)->where('id',$id)->setField($fn,$fv);
        if($res){
            $this->success('操作成功');
        }else{
            $this->error('操作失败');
        }
    }

    //回收记录详情
    public function info($id){
        $info = $this->model->record_info($id);
        if (!$info) {
            exit('数据不存在');
        }
        $this->assign('info',$info);
        return $this->fetch();
    }

    // 统计
    public function tj($id = 0){
        $info = Db::name('questionnaire')
            ->where('id',$id)
            ->where('del',0)
            ->find();
        if(!$info){
            $this->error('问卷不存在');
        }
        $questions = json_decode($info['questions'],true);
        $total = Db::name('questionnaire_record')
            ->where('questionnaire_id',$id)
            ->count();
        foreach ($questions as $k=>$v){ // 量表题
            if($v['type'] == 'star'){
                $answer = [];
                for($i=$v['star_val_min'];$i<=$v['star_val_max'];$i++){
                    $count = Db::name('questionnaire_record_answer')
                        ->where('question_id',$v['id'])
                        ->where('questionnaire_id',$id)
                        ->where('answer',$i)
                        ->count();
                    $bl = $total>0?round($count/$total,2)*100:0;
                    $answer[] = [
                        'id' => $i,
                        'text' => $i,
                        'count' => $count,
                        'count1' => $bl.'%'
                    ];
                }
                $questions[$k]['answer'] = $answer;
            }else if($v['type'] == 'radio' || $v['type'] == 'checkbox'){ // 单选或多项
                $answer = [];

                foreach ($v['options'] as $kk=>$vv){
                    $map = [];
                    $map[] = ['question_id','=',$v['id']];
                    $map[] = ['questionnaire_id','=',$id];
                    if($v['type'] == 'radio'){
                        $map[] = ['answer','=',$vv['id']];
                    }else{
                        $map[] = ['','exp', Db::raw("FIND_IN_SET({$vv['id']},answer)")];
                    }
                    $count = Db::name('questionnaire_record_answer')
                        ->where($map)
                        ->count();
                    $bl = $total>0?round($count/$total,2)*100:0;

                    $answer[] = [
                        'id' => $vv['id'],
                        'text' => $vv['text'],
                        'count' => $count,
                        'count1' => $bl.'%'

                    ];
                }
                $questions[$k]['answer'] = $answer;
            }else{ // 单行文本题/多行文本题
                $answer = Db::name('questionnaire_record_answer')
                    ->where('question_id',$v['id'])
                    ->where('questionnaire_id',$id)
                    ->count();
                $questions[$k]['answer'] = $answer;
            }
        }

        $info['questions'] = $questions;

        $this->assign('info',$info);
        return $this->fetch();
    }

    public function tjExport($id){

        $info = Db::name('questionnaire')
            ->where('id',$id)
            ->where('del',0)
            ->find();
        if(!$info){
            $this->error('问卷不存在');
        }
        $questions = json_decode($info['questions'],true);
        $total = Db::name('questionnaire_record')
            ->where('questionnaire_id',$id)
            ->count();
        foreach ($questions as $k=>$v){ // 量表题
            if($v['type'] == 'star'){
                $answer = [];
                for($i=$v['star_val_min'];$i<=$v['star_val_max'];$i++){
                    $count = Db::name('questionnaire_record_answer')
                        ->where('question_id',$v['id'])
                        ->where('questionnaire_id',$id)
                        ->where('answer',$i)
                        ->count();
                    $bl = $total>0?round($count/$total,2)*100:0;
                    $answer[] = [
                        'id' => $i,
                        'text' => $i,
                        'count' => $count,
                        'count1' => $bl.'%'
                    ];
                }
                $questions[$k]['answer'] = $answer;
            }else if($v['type'] == 'radio' || $v['type'] == 'checkbox'){ // 单选或多项
                $answer = [];

                foreach ($v['options'] as $kk=>$vv){
                    $map = [];
                    $map[] = ['question_id','=',$v['id']];
                    $map[] = ['questionnaire_id','=',$id];
                    if($v['type'] == 'radio'){
                        $map[] = ['answer','=',$vv['id']];
                    }else{
                        $map[] = ['','exp', Db::raw("FIND_IN_SET({$vv['id']},answer)")];
                    }
                    $count = Db::name('questionnaire_record_answer')
                        ->where($map)
                        ->count();
                    $bl = $total>0?round($count/$total,2)*100:0;

                    $answer[] = [
                        'id' => $vv['id'],
                        'text' => $vv['text'],
                        'count' => $count,
                        'count1' => $bl.'%'

                    ];
                }
                $questions[$k]['answer'] = $answer;
            }else{ // 单行文本题/多行文本题
                $answer = Db::name('questionnaire_record_answer')
                    ->where('question_id',$v['id'])
                    ->where('questionnaire_id',$id)
                    ->count();
                $questions[$k]['answer'] = $answer;
                $questions[$k]['answer_list'] = Db::name('questionnaire_record_answer')
                    ->where('question_id',$v['id'])
                    ->where('questionnaire_id',$id)
                    ->select();
            }
        }

        $data = [];
        foreach ($questions as $k=>$v){
            $data[] = [
                'title'=>$v['title'],
                'title1'=>'',
                'num'=>is_array($v['answer'])?'':$v['answer'],
                'bl'=>'',
                'answer_list'=>is_array($v['answer'])?[]:$v['answer_list'],
            ];
            if(is_array($v['answer'])){
                foreach ($v['answer'] as $k1=>$v1){
                    $data[] = [
                        'title'=>'',
                        'title1'=>$v1['text'],
                        'num'=>$v1['count'],
                        'bl'=>$v1['count1'],
                    ];
                }
            }else if (is_array($v['answer_list']) && !empty($v['answer_list'])){
                foreach ($v['answer_list'] as $k2=>$v2){
                    $data[] = [
                        'title'=>'',
                        'title1'=>$v2['answer'],
                        'num'=>'',
                        'bl'=>'',
                    ];
                }

            }

        }

        include_once env('root_path') . '/extend/phpexcel/Classes/PHPExcel.php';
        $objPHPExcel = new \PHPExcel();
        //激活当前的sheet表
        $objPHPExcel->setActiveSheetIndex(0);
        //设置表格头（即excel表格的第一行）
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', '标题')
            ->setCellValue('B1', '答案')
            ->setCellValue('C1', '数量')
            ->setCellValue('D1', '比例');




        //循环刚取出来的数组，将数据逐一添加到excel表格。
        for ($i = 0; $i < count($data); $i++) {
            $objPHPExcel->getActiveSheet()->setCellValue('A' . ($i + 2), $data[$i]['title']);
            $objPHPExcel->getActiveSheet()->setCellValue('B' . ($i + 2), $data[$i]['title1']);
            $objPHPExcel->getActiveSheet()->setCellValue('C' . ($i + 2), $data[$i]['num']);
            $objPHPExcel->getActiveSheet()->setCellValue('D' . ($i + 2), $data[$i]['bl']);

        }
        //设置保存的Excel表格名称
        $filename = $info['title'].'问卷统计' . date('YmdHis', time()) . '.xls';

        //设置当前激活的sheet表格名称
        $objPHPExcel->getActiveSheet()->setTitle('问卷统计');

        //设置浏览器窗口下载表格
        ob_end_clean();
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:inline;filename="' . $filename);

        //生成excel文件
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        //下载文件在浏览器窗口
        return  $objWriter->save('php://output');
    }

    public function answer($id=0,$qid=0){
        if(request()->isAjax()){
            //分页参数
            $length = input('rows',10,'intval');   //每页条数
            $page = input('page',1,'intval');      //第几页
            $start = ($page - 1) * $length;     //分页开始位置
            //排序
            $sortRow = input('sidx','id','trim');      //排序列
            $sort = input('sord','desc','trim');        //排序方式
            $order = 'id desc';

            $title = input('title','','trim');
            if($title){
                $map[] = ['b.real_name','like','%'.$title.'%'];
            }
            $map[] = ['a.questionnaire_id','=',$id];
            $map[] = ['a.question_id','=',$qid];
            $map= empty($map) ? true: $map;
            //数据查询
            $lists = Db::name('questionnaire_record_answer')
                ->alias('a')
                ->join('questionnaire_record c','c.id = a.questionnaire_record_id')
                ->join('user b','b.id = c.user_id','left')
                ->where($map)
                ->field('a.*,b.real_name as name,c.create_time,c.user_id')
                ->limit($start,$length)
                ->order($order)
                ->select();
            foreach ($lists as $k=>$v){
                $lists[$k]['name'] = $v['user_id']>0?$v['name']:'游客';
            }
            //数据返回
            $totalCount = Db::name('questionnaire_record_answer')
                ->alias('a')
                ->join('questionnaire_record c','c.id = a.questionnaire_record_id')
                ->join('user b','b.id = c.user_id','left')
                ->where($map)->count();
            $totalPage = ceil($totalCount/$length);
            $result['page'] = $page;
            $result['total'] = $totalPage;
            $result['records'] = $totalCount;
            $result['rows'] = $lists;
            return json($result);
        }else{
            $this->assign('id',$id);
            $this->assign('qid',$qid);
            return $this->fetch();
        }

    }

}