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

<?php

namespace app\admin\controller\teacher;

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'];
    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()
    {
        //设置过滤方法
        $this->request->filter(['strip_tags', 'trim']);
        if (false === $this->request->isAjax()) {
            return $this->view->fetch();
        }
        //如果发送的来源是 Selectpage，则转发到 Selectpage
        if ($this->request->request('keyField')) {
            return $this->selectpage();
        }
        [$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.'秒';
        
        if (false === $this->request->isPost()) {
            $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;
            foreach ($other_jielun as $key => $value) {
                if(!empty($value['cx_score'])){
                    $koufen = $koufen+abs($value['cx_score']);
                }
            }
            $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($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;


        $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;
        foreach ($other_jielun as $key => $value) {
            if(!empty($value['cx_score'])){
                $koufen = $koufen+abs($value['cx_score']);
            }
        }
        $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);
        
        $diffInSeconds = $rows['endtime'] - $rows['starttime']; // 两个时间戳之间的差异（秒）
        $minutes = floor($diffInSeconds / 60); // 计算分钟数
        $seconds = $diffInSeconds % 60; // 计算剩余的秒数
        $row['shijian'] = $minutes.'分'.$seconds.'秒';

        $this->view->assign('row', $row);
        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 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('exam_id 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)->where('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();
        }

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

        [$where, $sort, $order, $offset, $limit] = $this->buildparams();
        $list = $this->model
            ->where($where)->where($this->whereExtend)
            ->order($sort, $order)
            ->paginate($limit);

        $xlsName = '学员成绩信息';
        $this->exportExcel($xlsName,'Excel2007', $list);
    }

    /**
     * 输出到浏览器(需要设置header头)
     * @param string $fileName 文件名
     * @param string $fileType 文件类型
     */
    function exportExcel($fileName, $fileType,$data)
    {
        //文件名称校验
        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(15);
        $worksheet->getColumnDimension('C')->setWidth(15);
        $worksheet->getColumnDimension('D')->setWidth(15);

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

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

        $worksheet->setCellValue('A1',"序号");
        $worksheet->setCellValue('B1',"学号");
        $worksheet->setCellValue('C1',"姓名");
        $worksheet->setCellValue('D1',"总分");


        foreach ($data as $ky => $value)
        {
            $lie = $ky+2;
            $worksheet->setCellValue('A'.$lie,++$ky);
            $worksheet->setCellValue('B'.$lie,$value['user_username']);
            $worksheet->setCellValue('C'.$lie,$value['user_nickname']);
            $worksheet->setCellValue('D'.$lie,$value['total_score']);
        }


        $titlestyleArray = [
            'font' => [
                'name' => '黑体',
                'size' => 14
            ],
        ];
        $headerStyleArray = [
            'font' => [
                'name' => '方正小标宋简体',
                'size' => 18
            ],
        ];

        $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:D1')->applyFromArray($titlestyleArray);
        $worksheet->getStyle('A1:D1')->applyFromArray($commonStyleArray);
        foreach ($data as $k=>$v){
            $kk  = $k+2;
            $worksheet->getStyle('A'.$kk.':D'.$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();
    }


}