maintenance/application/admin/controller/teacher/Exams.php

<?php

namespace app\admin\controller\teacher;
use PhpOffice\PhpWord\PhpWord;

use app\admin\model\department\Department;
use app\common\controller\Backend;
use think\Db;
use think\exception\PDOException;
use think\exception\ValidateException;
use app\admin\model\teacher\ExamsScore;
use app\admin\model\Fault;
use app\admin\model\Report;

use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Reader\Csv;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;




/**
 * sim-考试表/成绩总分
 *
 * @icon fa fa-circle-o
 */
class Exams extends Backend
{

    /**
     * Exams模型对象
     * @var \app\admin\model\teacher\Exams
     */
    protected $model = null;
    protected $whereExtend = null;
    protected $noNeedRight = ['export','examslog','report'];
    public function _initialize()
    {
        parent::_initialize();
        $this->model = new \app\admin\model\teacher\Exams;
        $this->whereExtend['exam_collection_type'] = 3;
        $this->whereExtend['starttime'] = ['>',0];
        $this->whereExtend['endtime'] = ['>',0];

        $this->relationtTable = 'collection';
    }



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

    public function index()
    {
        //查询最后一场考试id
        $exam_collection_id = $this->model->where($this->whereExtend)->order('exam_id desc')->value('exam_collection_id');
        $collection_name = $this->model->where(['exam_collection_id'=>$exam_collection_id])->value('exam_collection_name');

        //设置过滤方法
        $this->request->filter(['strip_tags', 'trim']);
        if (false === $this->request->isAjax()) {
            $this->assignConfig('collection_name', $collection_name);
            return $this->view->fetch();
        }
        //如果发送的来源是 Selectpage，则转发到 Selectpage
        if ($this->request->request('keyField')) {
            return $this->selectpage();
        }
        //查询考试
        $filter = $this->request->get("filter", '');
        $filter = (array)json_decode($filter, true);
        $op = $this->request->get("op", '');
        $op = (array)json_decode($op, true);

       if(isset($filter) &&!empty($filter['exam_collection_name'])){
           unset($this->whereExtend['exam_collection_id']);
           $this->request->get(['filter'=>json_encode($filter),'op'=>json_encode($op)]);
       }else{
           $this->whereExtend['exam_collection_id'] = $exam_collection_id;
       }
        [$where, $sort, $order, $offset, $limit] = $this->buildparams();
        $list = $this->model
            ->where($where)->where($this->whereExtend)
            ->order($sort, $order)
            ->paginate($limit);

        //最高分 最低分 平均分
        $select = $this->model->where($where)->where($this->whereExtend)->field('count(*) as count,sum(total_score) as totalscore,min(total_score) as min,max(total_score) as max')->select();
        $pingfen  = '';
        if(!empty($select[0]['totalscore']) && !empty($select[0]['count'])){
            $pingfen  = bcdiv($select[0]['totalscore'],$select[0]['count']);
        }
        $defen = ['max'=>$select[0]['max'],'min'=>$select[0]['min'],'pingfen'=>$pingfen];
        $one_static = [];
        $two_static = [];
        $three_static = [];
        $where_falut = ['fault_type' => 3,'fault_state'=>0];
        $exam_ids = $this->model->where($where)->where($this->whereExtend)->column('exam_id');
        foreach ($list as $k => $val){
            $one = Fault::where(['sim_type'=>'0001'])->where($where_falut)->field('fault_id,name')->select();
            if($val['sim_type']=='0001'){
                foreach ($one as $k1 => $val1){
                    //先查分配次数,在查错误率
                    $one_total = Db::name('real_exam_fault')->where(['flag'=>1,'exam_id'=>['in',$exam_ids],'fault_id'=>$val1['fault_id']])->count();
                    $error_total = Db::name('real_exam_fault')->where(['flag'=>1,'exam_id'=>['in',$exam_ids],'fault_id'=>$val1['fault_id'],'answer_right'=>2])->count();
                    $one[$k1]['epercent'] = '';
                    if($one_total>0){
                        $bili = bcmul(bcdiv($error_total,$one_total,2),100);
                        $one[$k1]['epercent'] = $bili>0 ? $bili.'%' : '';
                    }
                }
                $vaccineCount = array_column($one, 'epercent');
                array_multisort($vaccineCount,SORT_DESC,$one);
                $one_static = $one;
            }
            $two = Fault::where(['sim_type'=>'0002'])->where($where_falut)->field('fault_id,name')->select();
            if($val['sim_type']=='0002'){
                foreach ($two as $k2 => $val2){
                    $two_total = Db::name('real_exam_fault')->where(['flag'=>1,'exam_id'=>['in',$exam_ids],'fault_id'=>$val2['fault_id']])->count();
                    $error_total = Db::name('real_exam_fault')->where(['flag'=>1,'exam_id'=>['in',$exam_ids],'fault_id'=>$val2['fault_id'],'answer_right'=>2])->count();
                    $two[$k2]['epercent'] = '';
                    if($two_total>0){
                        $bili = bcmul(bcdiv($error_total,$two_total,2),100);
                        $two[$k2]['epercent'] = $bili>0 ? $bili.'%' : '';
                    }
                }
                $vaccineCount1 = array_column($two, 'epercent');
                array_multisort($vaccineCount1,SORT_DESC,$two);
            }
            $two_static = $two;
            $three = Fault::where(['sim_type'=>'0003'])->where($where_falut)->field('fault_id,name')->select();
            if($val['sim_type']=='0003'){
                foreach ($three as $k3 => $val3){
                    $three_total = Db::name('real_exam_fault')->where(['flag'=>1,'exam_id'=>['in',$exam_ids],'fault_id'=>$val3['fault_id']])->count();
                    $error_total = Db::name('real_exam_fault')->where(['flag'=>1,'exam_id'=>['in',$exam_ids],'fault_id'=>$val3['fault_id'],'answer_right'=>2])->count();
                    $three[$k3]['epercent'] = '';
                    if($three_total>0){
                        $bili = bcmul(bcdiv($error_total,$three_total,2),100);
                        $three[$k3]['epercent'] = $bili>0 ? $bili.'%' : '';
                    }
                }
                $vaccineCount2 = array_column($three, 'epercent');
                array_multisort($vaccineCount2,SORT_DESC,$three);
            }
            $three_static = $three;
        }
        //统计错误率
        $statistics = [
            'one_static' => $one_static,
            'two_static' => $two_static,
            'three_static' => $three_static,
        ];
        $result = ['total' => $list->total(), 'rows' => $list->items(),'defen'=>$defen,'tongji'=>$statistics];
        return json($result);
    }



    public function edit($ids = null)
    {
        $row = Db::name('real_exam_score')->where('exam_id', $ids)->find();
        if (!$row) {
            $this->error(__('No Results were found'));
        }
        $rows = $this->model->get($ids);
        $row['seat_id'] = $rows->seat_id;
        $row['user_nickname'] = $rows->user_nickname;
        $row['user_username'] = $rows->user_username;
        $row['is_sure'] = $rows->is_sure;


        $fault_list = Db::name('real_exam_fault')->where(['exam_id'=>$ids,'flag'=>1])->select();
        $row['fault_name_one'] = Fault::where('fault_id',$fault_list[0]['fault_id'])->value('name');
        $row['fault_name_two'] = Fault::where('fault_id',$fault_list[1]['fault_id'])->value('name');
        $row['fault_name_three'] = Fault::where('fault_id',$fault_list[2]['fault_id'])->value('name');

        $other_jielun = !empty($row['other_jielun']) ? json_decode($row['other_jielun'],true):[];

        $diffInSeconds = $rows['endtime'] - $rows['starttime']; // 两个时间戳之间的差异（秒）
        $minutes = floor($diffInSeconds / 60); // 计算分钟数
        $seconds = $diffInSeconds % 60; // 计算剩余的秒数
        $row['shijian'] = $minutes.'分'.$seconds.'秒';

        $koufen = 0;
        if(!empty($other_jielun)){
            foreach ($other_jielun as $key => $value) {
                if(!empty($value['cx_score'])){
                    $koufen = $koufen+abs($value['cx_score']);
                }
            }
        }else{
            $koufen = 15;
        }
        if (false === $this->request->isPost()) {
            $this->assignConfig('koufen', $koufen);
            $this->view->assign('other_jielun', $other_jielun);
            $this->view->assign('row', $row);
            return $this->view->fetch();
        }
        $params = $this->request->post('row/a');
        if (empty($params)) {
            $this->error(__('Parameter %s can not be empty', ''));
        }
        $params = $this->preExcludeFields($params);
        $result = false;
        Db::startTrans();
        try {
            //是否采用模型验证
            $koufen = 0;
            if(!empty($other_jielun)){
                foreach ($other_jielun as $key => $value) {
                    if(!empty($value['cx_score'])){
                        $koufen = $koufen+abs($value['cx_score']);
                    }
                }
            }else{
                $koufen = 15; //超时未自动交卷的
            }
            $params['total'] = 100-$params['fault_one_score']-$params['fault_two_score']-$params['fault_three_score']-$params['overtime_score']-$koufen;
            $result = Db::name('real_exam_score')->where('id', $row['id'])->update($params);

            $rows->total_score = $params['total'];
            $rows->is_sure = 1;
            $rows->save();
            Db::commit();
        } catch (ValidateException|PDOException|Exception $e) {
            Db::rollback();
            $this->error($e->getMessage());
        }
        if (false === $result) {
            $this->error(__('No rows were updated'));
        }
        $this->success();
    }

    public function view($type = null,$ids = null)
    {
        $row = Db::name('real_exam_score')->where('exam_id', $ids)->find();
        if (!$row) {
            $this->error(__('No Results were found'));
        }
        $rows = $this->model->get($ids);
        $row['fault_total'] = $rows->fault_total;
        $row['seat_id'] = $rows->seat_id;
        $row['user_nickname'] = $rows->user_nickname;
        $row['user_username'] = $rows->user_username;
        $row['user_depart_id'] = $rows->user_depart_id;
        $row['user_depart_name'] = Department::where('id',$rows->user_depart_id)->value('name');
        $row['start_time'] = $rows->start_time;
        $row['end_time'] = $rows->end_time;
        $row['exam_collection_type'] = $rows->exam_collection_type;


        $fault_list = Db::name('real_exam_fault')->where(['exam_id'=>$ids,'flag'=>1])->select();
        $row['fault_name_one'] = Fault::where('fault_id',$fault_list[0]['fault_id'])->value('name');
        if($rows['fault_total']>0){
            if($rows['fault_total']==2){
                $row['fault_name_two'] = Fault::where('fault_id',$fault_list[1]['fault_id'])->value('name');
            }
            if($rows['fault_total']==3){
                $row['fault_name_two'] = Fault::where('fault_id',$fault_list[1]['fault_id'])->value('name');
                $row['fault_name_three'] = Fault::where('fault_id',$fault_list[2]['fault_id'])->value('name');
            }
        }else{
            $row['fault_name_two'] = Fault::where('fault_id',$fault_list[1]['fault_id'])->value('name');
            $row['fault_name_three'] = Fault::where('fault_id',$fault_list[2]['fault_id'])->value('name');
        }
        $row['fault_score'] = 75-$row['fault_one_score']-$row['fault_two_score']-$row['fault_three_score'];


        $row['weixiu_score'] = 10-$row['overtime_score']??0; 
        $other_jielun = !empty($row['other_jielun']) ? json_decode($row['other_jielun'],true):[];


        $koufen = 0;
        $dt_count = 0;
        if(!empty($other_jielun)){
            foreach ($other_jielun as $key => $value) {
                if(!empty($value['cx_score'])){
                    $koufen = $koufen+abs($value['cx_score']);
                }
                if($value['cx_type']=='未作答'){
                    $dt_count = $dt_count+1;
                }
            }
        }else{
            $koufen = 15;
        }
        //训练，未作答。展示使用
        $is_nowancheng = 0;
        if($row['exam_collection_type']== 1 && $row['fault_total'] == $dt_count){
            $is_nowancheng = 1;
        }
        $report_score = 15-$koufen;
        $row['report_score'] = $report_score>0?$report_score:0; 


        $total = $row['fault_score']+$row['report_score']+$row['weixiu_score'];
        Db::name('real_exam_score')->where('exam_id', $ids)->update(['total'=>$total]);
        Db::name('real_exam')->where('exam_id', $ids)->update(['total_score'=>$total]);

        $row['total'] = $total;

        $this->view->assign('other_jielun', $other_jielun);
        $this->view->assign('dt_count', $dt_count);
        $this->view->assign('is_nowancheng', $is_nowancheng);

        $diffInSeconds = $rows['endtime'] - $rows['starttime']; // 两个时间戳之间的差异（秒）
        $minutes = floor($diffInSeconds / 60); // 计算分钟数
        $seconds = $diffInSeconds % 60; // 计算剩余的秒数
        $row['shijian'] = $minutes.'分'.$seconds.'秒';

        $this->view->assign('row', $row);
        if(!empty($type)){
            return $this->view->fetch('view1');
        }else{
            return $this->view->fetch();
        }
    }

    //正在考试
    public function persent($ids = null)
    {
        //设置过滤方法
        $this->request->filter(['strip_tags', 'trim']);
        if (false === $this->request->isAjax()) {
            $this->assignConfig('ids', $ids);
            return $this->view->fetch();
        }
        //如果发送的来源是 Selectpage，则转发到 Selectpage
        if ($this->request->request('keyField')) {
            return $this->selectpage();
        }
        $exam_ids = $this->model->where('exam_collection_id',$ids)->column('exam_id');
        [$where, $sort, $order, $offset, $limit] = $this->buildparams();
        $list = Db::name('real_exam_comp_request')
//            ->where('exam_collection_type',3)
            ->where($where)->where('exam_id','in',$exam_ids)
            ->order($sort, $order)
            ->paginate($limit);
        unset($v);
        $result = ['total' => $list->total(), 'rows' => $list->items()];
        return json($result);
    }

    //考试座次历史记录
    public function examslog($ids = null)
    {
        $row = $this->model->get($ids);
        if(!$row){
            $this->error(__('No Results were found'));
        }
        $list = $this->model->where(['seat_id'=>$row->seat_id,'exam_collection_id'=>$row->exam_collection_id])->order('starttime asc')->select();
        $this->view->assign('list', $list);
        return $this->view->fetch();
    }

    public function examing($ids = null)
    {
        //设置过滤方法
        $this->request->filter(['strip_tags', 'trim']);
        if (false === $this->request->isAjax()) {
            $this->assignConfig('ids', $ids);
            return $this->view->fetch();
        }
        //如果发送的来源是 Selectpage，则转发到 Selectpage
        if ($this->request->request('keyField')) {
            return $this->selectpage();
        }
        [$where, $sort, $order, $offset, $limit] = $this->buildparams();

        $list = Db::name('seat')->select();
        foreach ($list as $key => $value) {
            $exam = $this->model->where('exam_collection_id',$ids)->order('starttime desc')->where('seat_id',$value['seat_num'])->find();
            $user_username = '';
            $user_nickname = '';
            $exam_status = '未登录';
            $total_score = 0;
            $fault_names = '';
            $exam_id = 0;
            $simtype = '';
            $sim_state = '';
            if(!empty($exam)){
                if(!empty($value['current_sim_id'])){
                    $sim = Db::name('sim')->where('sim_id',$value['current_sim_id'])->find();
                    if(!empty($sim['sim_state'])){
                        switch ($sim['sim_state']) {
                            case '1':
                                $sim_state = '在线';
                                break;
                            case '2':
                                $sim_state = '离线';
                                break;
                        }
                    }
                    $simtype = $sim['sim_type'];
                }
                $user_username = $exam['user_username']??'';
                $user_nickname = $exam['user_nickname']??'';
                if($exam['exam_status']==1){
                    $exam_status= '已登录未开始考试';
                }else if($exam['exam_status']==4){
                    $exam_status= '已开始考试';
                }else if($exam['exam_status']==5){
                    $exam_status= '已交卷';
                }
                $total_score = $exam['total_score']??'';
                $fault_names = $exam['fault_names']??'';
                $exam_id = $exam['exam_id']??0;
                $simtype = $simtype;
            }
            $list[$key]['user_username'] = $user_username;
            $list[$key]['user_nickname'] = $user_nickname;
            $list[$key]['exam_status'] = $exam_status;
            $list[$key]['total_score'] = $total_score;
            $list[$key]['fault_names'] = $fault_names;
            $list[$key]['exam_id'] = $exam_id;
            $list[$key]['sim_state'] = $sim_state;
            $list[$key]['sim_type'] = $simtype;
        }
        $result = ['total' => count($list), 'rows' => $list];
        return json($result);
    }

    public function score($ids = null)
    {
        //设置过滤方法
        $this->request->filter(['strip_tags', 'trim']);
        if (false === $this->request->isAjax()) {
            $this->assignConfig('ids', $ids);
            return $this->view->fetch();
        }
        //如果发送的来源是 Selectpage，则转发到 Selectpage
        if ($this->request->request('keyField')) {
            return $this->selectpage();
        }
        $exam_ids = $this->model->where('exam_collection_id',$ids)->column('exam_id');
        [$where, $sort, $order, $offset, $limit] = $this->buildparams();
        $list = ExamsScore::where($where)->alias('s')->join('mx_real_exam e','s.exam_id=e.exam_id')->where('s.exam_id','in',$exam_ids)
            ->order($sort, $order)
            ->paginate($limit);
        foreach ($list as $k => $v){
            $exam =  $this->model->where(['exam_id'=>$v['exam_id']])->find();
            if($exam){
                $v->seat_id = $exam['seat_id'];
                $v->user_username = $exam['user_username'];
                $v->user_nickname = $exam['user_nickname'];
                $v->sim_type = $exam['sim_type'];
            }
        }
        unset($v);
        $result = ['total' => $list->total(), 'rows' => $list->items()];
        return json($result);
    }


    public function handle($type = null,$ids = null)
    {
        $row = Db::name('real_exam_comp_request')->where('rel_id',$ids)->find($ids);
        if(!$row){
            $this->error('未找到记录');
        }
        if($type ==1){
            $request_status = 2;
        }
        if($type==2)
        {
            $request_status= 3;
        }
        Db::name('real_exam_comp_request')->where('rel_id',$ids)->update(['request_status'=>$request_status]);

        //同步更新考试表json串
        $exam_info  = $this->model->get($row['exam_id']);
        if(!empty($exam_info)){
            $other_replace = json_decode($exam_info['other_replace'],true)??[];
            foreach ($other_replace as $k=>$v){
                if($v['fault_id'] == $row['fault_id']){
                    $other_replace[$k]['request_status'] = $request_status;
                }
            }
            $exam_info->other_replace = json_encode($other_replace);
            $exam_info->save();
            unset($v);
        }
        $this->success();
    }


    //啦
    public function editscore($ids = "")
    {
        $row = Db::name('real_exam_score')->where('exam_id', $ids)->find();
        if (!$row) {
            $this->error(__('No Results were found'));
        }
        $rows = $this->model->get($ids);
        $params = $this->request->post('row/a');
        if (empty($params)) {
            $this->error(__('Parameter %s can not be empty', ''));
        }
        $params = $this->preExcludeFields($params);
        $result = false;
        Db::startTrans();
        try {
            //是否采用模型验证
            $result = Db::name('real_exam_score')->where('id', $row['id'])->update($params);

            $info = Db::name('real_exam_score')->where('exam_id', $ids)->find();
            $total =100-$info['fault_one_score']-$info['fault_two_score']-$info['fault_three_score']-$info['xianxian_score']-$info['yuanyin_socre']-$info['buwei_score']-$info['fangfa_score']-$info['overtime_score']-$info['jielun_score'];;


            $rows->total_score = $total;
            $rows->is_sure = 1;
            $rows->save();
            $result = Db::name('real_exam_score')->where('id', $row['id'])->update(['total'=>$total]);
            Db::commit();
        } catch (ValidateException|PDOException|Exception $e) {
            Db::rollback();
            $this->error($e->getMessage());
        }
        if (false === $result) {
            $this->error(__('No rows were updated'));
        }
        $this->success();
    }

    public function export()
    {
        set_time_limit(0);
        ini_set('memory_limit', '2560M');

        //如果发送的来源是 Selectpage，则转发到 Selectpage
        if ($this->request->request('keyField')) {
            return $this->selectpage();
        }
        $exam_collection_id = $this->model->where($this->whereExtend)->order('exam_id desc')->value('exam_collection_id');

        $filter = $this->request->get("filter", '');
        $filter = (array)json_decode($filter, true);
        $op = $this->request->get("op", '');
        $op = (array)json_decode($op, true);

        if(isset($filter) &&!empty($filter['exam_collection_name'])){
            unset($this->whereExtend['exam_collection_id']);
            $this->request->get(['filter'=>json_encode($filter),'op'=>json_encode($op)]);
        }else{
            $this->whereExtend['exam_collection_id'] = $exam_collection_id;
        }
        [$where, $sort, $order, $offset, $limit] = $this->buildparams();
        $list = $this->model
            ->where($where)->where($this->whereExtend)
//            ->order($sort, $order)
            ->order('user_username asc')
            ->paginate($limit);

        $depart_ids = [];
        foreach ($list as $k => $v){
            if(!in_array($v['user_depart_id'],$depart_ids)){
                $depart_ids[] = $v['user_depart_id'];
            }
        }
        $xlsName = '学员成绩信息';
        $this->exportExcel($xlsName,'Excel2007', $list,$depart_ids);
    }

    /**
     * 输出到浏览器(需要设置header头)
     * @param string $fileName 文件名
     * @param string $fileType 文件类型
     */
    function exportExcel($fileName, $fileType,$data,$depart_ids)
    {
        //文件名称校验
        if (!$fileName) {
            trigger_error('文件名不能为空', E_USER_ERROR);
        }
        //Excel文件类型校验
        $type = ['Excel2007', 'Xlsx', 'Excel5', 'xls'];
        if (!in_array($fileType, $type)) {
            trigger_error('未知文件类型', E_USER_ERROR);
        }

        $ext = '';

        if ($fileType == 'Excel2007' || $fileType == 'Xlsx') {
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment;filename="' . $fileName . '.xlsx"');
            header('Cache-Control: max-age=0');
            $ext = 'Xlsx';
        } else { //Excel5
            header('Content-Type: application/vnd.ms-excel');
            header('Content-Disposition: attachment;filename="' . $fileName  . '.xls"');
            header('Cache-Control: max-age=0');
            $ext = 'Xls';
        }
        $spreadsheet = new Spreadsheet();
        $worksheet = $spreadsheet->getActiveSheet();
        $worksheet->getColumnDimension('A')->setWidth(15);
        $worksheet->getColumnDimension('B')->setWidth(20);
        $worksheet->getColumnDimension('C')->setWidth(20);
        $worksheet->getColumnDimension('D')->setWidth(25);
        $worksheet->getColumnDimension('E')->setWidth(30);

        $worksheet->getRowDimension('1')->setRowHeight(40);

        //设置工作表标题名称
        $worksheet->setTitle('学员成绩信息');

        //1745459433
        $kaoshi = isset($data[0]['endtime']) ? date('Y-m-d',$data[0]['endtime']) : '';
        $a = '';
        foreach ($depart_ids as $k1=>$v1){
            $wu = Db::name('department')->where('id',$v1)->find();
            $si = Db::name('department')->where('id',$wu['parent_id'])->find();
            $san = Db::name('department')->where('id',$si['parent_id'])->find();
            $er = Db::name('department')->where('id',$san['parent_id'])->find();
            $yi = Db::name('department')->where('id',$er['parent_id'])->find();
            $a = $a.$yi['name']."/".$er['name']."/".$san['name']."/".$si['name']."/".$wu['name']."\n";
        }
//        $a = "预选军士\n预选军士一区队";
        $worksheet->setCellValue('A1',$a);
        $worksheet->mergeCells('A1:D1');
        $worksheet->setCellValue('E1',"考试日期 ".$kaoshi);

        $worksheet->setCellValue('A2',"序号");
        $worksheet->setCellValue('B2',"学员账号");
        $worksheet->setCellValue('C2',"学员姓名");
        $worksheet->setCellValue('D2',"模拟器类型");
        $worksheet->setCellValue('E2',"总分");


        foreach ($data as $ky => $value)
        {
            $lie = $ky+3;
            $sim_type= '';
            if($value['sim_type'] == '0001'){
                $sim_type = 'FZD04B型侦毒器';
            }else if($value['sim_type'] == '0002'){
                $sim_type = 'FZB006型毒剂报警器';
            }else if($value['sim_type'] == '0003'){
                $sim_type = '防化兵用毒剂报警器';
            }
            $worksheet->setCellValue('A'.$lie,++$ky);
            $worksheet->setCellValue('B'.$lie,$value['user_username']);
            $worksheet->setCellValue('C'.$lie,$value['user_nickname']);
            $worksheet->setCellValue('D'.$lie,$sim_type);
            $worksheet->setCellValue('E'.$lie,$value['total_score']);
        }


        $titlestyleArray = [
            'font' => [
                'name' => '黑体',
                'size' => 14
            ],
        ];
        $titlestyleArray2 = [
            'font' => [
                'size' => 12
            ],
        ];
        $headerStyleArray = [
            'font' => [
                'name' => '方正小标宋简体',
                'size' => 18
            ],
        ];
        $headStyleArray1 = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
                'wrapText' => true,
            ],
        ];

        $commonStyleArray = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
                'wrapText' => true,
            ]
        ];
        $borderStyleArray = [
            'borders' => [
                'allBorders' => [
//                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                ],
            ],
            'font' => [
                'name' => '黑体',
            ],
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
                'wrapText' => true,
            ]
        ];

        $worksheet->getStyle('A1:E1')->applyFromArray($headStyleArray1);//换行
        $worksheet->getStyle('A1:E1')->applyFromArray($titlestyleArray);
        $worksheet->getStyle('A2:E2')->applyFromArray($titlestyleArray2);
        $worksheet->getStyle('A2:E2')->applyFromArray($commonStyleArray);
//        foreach ($data as $k=>$v){
//            $kk  = $k+3;
//            $worksheet->getStyle('A'.$kk.':E'.$kk)->applyFromArray($borderStyleArray);
//        }
        // $worksheet->getStyle('A3:R3')->applyFromArray($commonStyleArray);
        // $worksheet->getStyle('A'.$footer_total.':R'.$footer_total)->applyFromArray($commonStyleArray);
        // $worksheet->getStyle('A1:C1')->applyFromArray($borderStyleArray);
        // $worksheet->mergeCells('A1:R1');
        $writer = IOFactory::createWriter($spreadsheet,$ext);
        $writer->save('php://output');
        die();
    }

    /**
     *
     * @return void
     */
    public function report()
    {
        //如果发送的来源是 Selectpage，则转发到 Selectpage
        if ($this->request->request('keyField')) {
            return $this->selectpage();
        }
        $exam_collection_id = $this->model->where($this->whereExtend)->order('exam_id desc')->value('exam_collection_id');

        $filter = $this->request->get("filter", '');
        $filter = (array)json_decode($filter, true);
        $op = $this->request->get("op", '');
        $op = (array)json_decode($op, true);

        $doc_name = '';
        if(isset($filter) &&!empty($filter['exam_collection_name'])){
            $doc_name = $filter['exam_collection_name'];
            unset($this->whereExtend['exam_collection_id']);
            $this->request->get(['filter'=>json_encode($filter),'op'=>json_encode($op)]);
        }else{
            $doc_name = $this->model->where('exam_collection_id',$exam_collection_id)->value('exam_collection_name');
            $this->whereExtend['exam_collection_id'] = $exam_collection_id;
        }
        [$where, $sort, $order, $offset, $limit] = $this->buildparams();
        $list = $this->model
            ->where($where)->where($this->whereExtend)
            ->order('user_username asc')
            ->paginate($limit);

        $languageEnGb = new \PhpOffice\PhpWord\Style\Language(\PhpOffice\PhpWord\Style\Language::ZH_CN);
        $phpWord = new \PhpOffice\PhpWord\PhpWord();
        $phpWord->getSettings()->setThemeFontLang($languageEnGb);

        $fancyTableStyleName = 'Fancy Table';
        $fancyTableStyle = array('borderSize' => 1, 'borderColor' => '#cad9ea', 'cellMargin' => 0, 'alignment' => \PhpOffice\PhpWord\SimpleType\JcTable::CENTER, 'cellSpacing' => 0);
        $fancyTableFirstRowStyle = array('borderBottomSize' => 0, 'borderBottomColor' => '#ffffff', 'bgColor' => '000000');
        $fancyTableCellStyle = array('valign' => 'center');
        $fancyTableFontStyle = array('bold' => true,'alignment' => \PhpOffice\PhpWord\SimpleType\JcTable::CENTER);
        $phpWord-> addTableStyle($fancyTableStyleName, $fancyTableStyle, $fancyTableFirstRowStyle);

        //中间学员的成绩信息  start
        foreach ($list as $k=> $it) {
            $section_string = 'section'.$k;
            $section_string = $phpWord->addSection([
                'pageSizeH' => \PhpOffice\PhpWord\Shared\Converter::inchToTwip(12.7),
                'pageSizeW' => \PhpOffice\PhpWord\Shared\Converter::inchToTwip(8.3)
            ]);
            $section_string->addTextBreak(1);
            $table = $section_string->addTable($fancyTableStyleName);
            $section_string -> addText($it['user_nickname'].'考试详细',[
                'size'    => 16,
                'name'=>'黑体',
            ],['alignment' => \PhpOffice\PhpWord\SimpleType\Jc::CENTER,
                'spaceAfter'  => 500,                                                   //标题后部预留长度
                'spaceBefore' => 0]);

            $TableContentStyle1 = array('size'=>10,'name'=>'宋体','lineHeight'  => 1.5);

            $table = $section_string->addTable($fancyTableStyleName);
            $table->addRow();
            $table->addCell(2000, $fancyTableCellStyle)->addText("学员账号",$TableContentStyle1, $fancyTableFontStyle);
            $table->addCell(2000, $fancyTableCellStyle)->addText($it['user_username'],$TableContentStyle1, $fancyTableFontStyle);
            $table->addCell(2000, $fancyTableCellStyle)->addText("学员姓名",$TableContentStyle1, $fancyTableFontStyle);
            $table->addCell(2000, $fancyTableCellStyle)->addText($it['user_nickname'],$TableContentStyle1, $fancyTableFontStyle);
            $table->addCell(2000, $fancyTableCellStyle)->addText("区队",$TableContentStyle1, $fancyTableFontStyle);
            $table->addCell(2000, $fancyTableCellStyle)->addText($it['user_nickname'],$TableContentStyle1, $fancyTableFontStyle);

            $table->addRow();
            $table->addCell(2000, $fancyTableCellStyle)->addText("考试开始时间",['name'=>'宋体','size'=>10], $fancyTableFontStyle);
            $table->addCell(2000, $fancyTableCellStyle)->addText("详解",['name'=>'宋体','size'=>10], $fancyTableFontStyle);
            $table->addCell(2000, $fancyTableCellStyle)->addText("考试结束时间",['name'=>'宋体','size'=>10], $fancyTableFontStyle);
            $table->addCell(2000, $fancyTableCellStyle)->addText("详解",['name'=>'宋体','size'=>10], $fancyTableFontStyle);
            $table->addCell(2000, $fancyTableCellStyle)->addText("成绩",['name'=>'宋体','size'=>10], $fancyTableFontStyle);
            $table->addCell(2000, $fancyTableCellStyle)->addText("详解",['name'=>'宋体','size'=>10], $fancyTableFontStyle);

            $table2 = $section_string->addTable($fancyTableStyleName);
            $table2->addRow();
            $table2->addCell(2500, $fancyTableCellStyle)->addText("序号",['name'=>'宋体','size'=>10], $fancyTableFontStyle);
            $table2->addCell(2500, $fancyTableCellStyle)->addText("评分标准",['name'=>'宋体','size'=>10], $fancyTableFontStyle);
            $table2->addCell(2000, $fancyTableCellStyle)->addText("扣分",['name'=>'宋体','size'=>10], $fancyTableFontStyle);
            $table2->addCell(2500, $fancyTableCellStyle)->addText("维修情况",['name'=>'宋体','size'=>10], $fancyTableFontStyle);
            $table2->addCell(2500, $fancyTableCellStyle)->addText("题目",['name'=>'宋体','size'=>10], $fancyTableFontStyle);

            $table3 = $section_string->addTable($fancyTableStyleName);
            $table3->addRow();
            $table3->addCell(12000, $fancyTableCellStyle)->addText("一.故障排除(75分)(本题得分:0)",['name'=>'宋体','size'=>10], $fancyTableFontStyle);

            //4
            $table4 = $section_string->addTable($fancyTableStyleName);
            $table4->addRow();


            $table5 = $section_string->addTable($fancyTableStyleName);
            $table5->addRow();
            $table5->addCell(12000, $fancyTableCellStyle)->addText("二.修理报告表(15分)(本题得分:0)",['name'=>'宋体','size'=>10], $fancyTableFontStyle);
            $table5->addRow();
            $table5->addCell(12000, $fancyTableCellStyle)->addText("学员答作答情况",['name'=>'宋体','size'=>10], $fancyTableFontStyle);

            $table6 = $section_string->addTable($fancyTableStyleName);
            $table6->addRow();
            $table6->addCell(600, $fancyTableCellStyle)->addText("序号",['name'=>'宋体','size'=>10], $fancyTableFontStyle);
            $table6->addCell(2000, $fancyTableCellStyle)->addText("故障现象",['name'=>'宋体','size'=>10], $fancyTableFontStyle);
            $table6->addCell(2000, $fancyTableCellStyle)->addText("可能原因",['name'=>'宋体','size'=>10], $fancyTableFontStyle);
            $table6->addCell(2000, $fancyTableCellStyle)->addText("故障部位",['name'=>'宋体','size'=>10], $fancyTableFontStyle);
            $table6->addCell(2000, $fancyTableCellStyle)->addText("排除方法",['name'=>'宋体','size'=>10], $fancyTableFontStyle);
            $table6->addCell(2000, $fancyTableCellStyle)->addText("扣分原因",['name'=>'宋体','size'=>10], $fancyTableFontStyle);
            $table6->addCell(1400, $fancyTableCellStyle)->addText("扣分情况",['name'=>'宋体','size'=>10], $fancyTableFontStyle);

            //7
            $table8 = $section_string->addTable($fancyTableStyleName);
            $table8->addRow();
            $table8->addCell(12000, $fancyTableCellStyle)->addText("故障现象少写或错写1条扣5分；可能原因少写或错写1条扣1分；故障部位少写或错写1条扣1分；排除方法少写或错写1条扣1分。",['name'=>'宋体','size'=>10], $fancyTableFontStyle);
            $table8->addRow();
            $table8->addCell(12000, $fancyTableCellStyle)->addText("三.维修时间(10分)(本题得分:0)",['name'=>'宋体','size'=>10], $fancyTableFontStyle);


            $table9 = $section_string->addTable($fancyTableStyleName);
            $table9->addRow();
            $table9->addCell(4500, $fancyTableCellStyle)->addText("按没超过1分钟扣1分的比例扣分",['name'=>'宋体','size'=>10], $fancyTableFontStyle);
            $table9->addCell(3000, $fancyTableCellStyle)->addText("考试时长:291分55秒",['name'=>'宋体','size'=>10], $fancyTableFontStyle);
            $table9->addCell(1500, $fancyTableCellStyle)->addText("扣分:10",['name'=>'宋体','size'=>10], $fancyTableFontStyle);


            $table10 = $section_string->addTable($fancyTableStyleName);
            $table10->addRow();
            $table10->addCell(6000, $fancyTableCellStyle)->addText("五.成绩",['name'=>'宋体','size'=>10], $fancyTableFontStyle);
            $table10->addCell(6000, $fancyTableCellStyle)->addText("",['name'=>'宋体','size'=>10], $fancyTableFontStyle);
        }
        //中间学员的成绩信息  end

        $objWriter = \PhpOffice\PhpWord\IOFactory::createWriter($phpWord, 'Word2007');

        $file_name = $doc_name.'.docx';//''uploads/'.date('Ymd').'/'.$userInfo['grade'].'年级'.$userInfo['classed'].'班级'.$userInfo['nickname'].'.docx';

        $tmpPath ='uploads/'.date('Ymd').'/';
        if(!file_exists($tmpPath)){
            mkdir($tmpPath, 0777, true);
        }
        $objWriter->save($file_name);
        //以只读和二进制模式打开文件
        $file = fopen ( $file_name, "rb" );

        //告诉浏览器这是一个文件流格式的文件
        Header ( "Content-type: application/octet-stream" );
        //请求范围的度量单位
        Header ( "Accept-Ranges: bytes" );
        //Content-Length是指定包含于请求或响应中数据的字节长度
        Header ( "Accept-Length: " . filesize ( $file_name ) );
        //用来告诉浏览器，文件是可以当做附件被下载，下载后的文件名称为$file_name该变量的值。
        Header ( "Content-Disposition: attachment; filename=".$doc_name.'.docx' );

        //读取文件内容并直接输出到浏览器
        echo fread ( $file, filesize ( $file_name ) );
        fclose ( $file );
        exit ();
    }

    public function multi($ids = null)
    {
        if (false === $this->request->isPost()) {
            $this->error(__('Invalid parameters'));
        }
        $ids = $ids ?: $this->request->post('ids');
        if (empty($ids)) {
            $this->error(__('Parameter %s can not be empty', 'ids'));
        }

        if (false === $this->request->has('params')) {
            $this->error(__('No rows were updated'));
        }
        parse_str($this->request->post('params'), $values);
//        $values = $this->auth->isSuperAdmin() ? $values : array_intersect_key($values, array_flip(is_array($this->multiFields) ? $this->multiFields : explode(',', $this->multiFields)));
//        if (empty($values)) {
//            $this->error(__('You have no permission'));
//        }
        $adminIds = $this->getDataLimitAdminIds();
        if (is_array($adminIds)) {
            $this->model->where($this->dataLimitField, 'in', $adminIds);
        }
        $count = 0;
        Db::startTrans();
        try {
            $list = $this->model->where($this->model->getPk(), 'in', $ids)->select();
            foreach ($list as $item) {
                $count += $item->allowField(true)->isUpdate(true)->save($values);
            }
            Db::commit();
        } catch (PDOException|Exception $e) {
            Db::rollback();
            $this->error($e->getMessage());
        }
        if ($count) {
            $this->success();
        }
        $this->error(__('No rows were updated'));
    }



}