xiaoben-as2-php/protected/controllers/CheckstudentController.php

<?php

/**
 * 学生管理控制器类
 * @author li
 * @date 2018-11-19 9:50:00
 * @company 上海风车教育有限公司.
 */

class CheckstudentController extends Controller
{

	// 开始
	public function actionIndex()
	{
        $data['is_last']=0;
        $data['count']=0;
        $last_step=StudentCheck::model()->find('last_step=1');
        if($last_step){
            $data['is_last']=1;

            $data['count']=$this->countRepeatData();
        }

        $this->render('check', $data);
	}

	//读取学生数据
    public function actionAjaxLoadData(){
        $result['status']=0;

        //读取已关联数据
        $sql_relation="select relation_student_id from student_relation ";
        $all_relation_data = $this->sConn->createCommand($sql_relation)->queryAll();
        $reStudentIds=array();
        if($all_relation_data){
            foreach ($all_relation_data as $item){
                @$ids=explode(',',$item['relation_student_id']);
                if($ids){
                    foreach ($ids as $id){
                        if($id){
                            $reStudentIds[]=$id;
                        }
                    }
                }
            }
            $reStudentIds=array_unique($reStudentIds);
        }

        $del_sql="truncate  `student_check`;";
        $this->sConn->createCommand($del_sql)->execute();


        if($reStudentIds){
            $sql="insert into `student_check`(SELECT `student_id`,`realname`,`school_id`,`class_id`,`id_number`,'',0,`is_outer`,0,0 from `student_info` where (id_number is  null or id_number='') and   student_id not in(".implode(',',$reStudentIds)."));";
        }else{
            $sql="insert into `student_check`(SELECT `student_id`,`realname`,`school_id`,`class_id`,`id_number`,'',0,`is_outer`,0,0 from `student_info` where (id_number is  null or id_number='') );";
        }
       
        $rs=$this->sConn->createCommand($sql)->execute();

        if($rs){
            $result['status']=1;
            $result['data']=$rs;
        }

        exit(json_encode($result));
    }
    //判断账号异常
    public function actionAjaxCheckData(){
        ini_set('memory_limit','512M');
        set_time_limit(0);
        $result['status']=0;
        $result['data']=0;
        $student_all_data = $this->sConn->createCommand("SELECT student_id,realname FROM `student_check` `t` ")->queryAll();
        if($student_all_data){
            //去除非中文字符
            $updateArr=array();
            $preg="/[^\x{4E00}-\x{9FFF}^·]+/u";
            $count=0;
            foreach($student_all_data as $val){
                if(preg_match($preg,$val['realname'])){
                    $ModifyName = preg_replace($preg, "", $val['realname']);
                    if($ModifyName){
                        $updateArr[$val['student_id']]=$ModifyName;
                        $count++;
                    }
                }else{
                    $updateArr[$val['student_id']]=$val['realname'];
                }
            }
            //组织更新语句
            if($updateArr){

                $arrNumber=ceil(count($updateArr)/500);
                $Arr=array_chunk($updateArr,$arrNumber,true);



                $transcation = $this->sConn->beginTransaction();
                try {
                    foreach ($Arr as $item){
                        $sql='update student_check set `modify_name`= case student_id ';
                        foreach ($item as $key=> $val){
                            $sql.=" WHEN ".$key." THEN '".$val."' ";
                        }
                        $sql.=" End ";
                        $sql.=" where modify_name=''  or modify_name is null";
                        $this->sConn->createCommand($sql)->execute();
                    }
                    $transcation->commit();
                    $result['status']=1;
                }catch (Exception $e){
                    $transcation->rollback();
                }
            }

            $result['count']=$count;
        }

        exit(json_encode($result));
    }
    //自动关联
    public function actionAjaxRelation(){
        $result['status']=0;
        //处理正常账号

        //查询同一学期名字不重复的学生且其它学期也不重复，这类学生账号是正常账号，自动关联
        /*
        $sql="SELECT count(*) as count,sc.class_id,sc.student_id,sc.modify_name,c.class_name,s.semester_id,s.semester_name,s.school_year,s.end_time FROM `student_check` sc  ";
        $sql.="LEFT JOIN class c on sc.class_id=c.class_id ";
        $sql.="LEFT JOIN semester s on s.semester_id=c.semester_id ";
        $sql.="GROUP BY sc.modify_name,s.semester_id ";
        $sql.="having modify_name <> '' and class_name <> '' ";
        $sql.="order by s.end_time desc ;";
        */
        $sql="select count(*) as count,modify_name,semester_id,student_id from (
SELECT sc.class_id,sc.student_id,sc.modify_name,sc.is_relation,c.class_name,s.semester_id,s.semester_name,s.school_year,s.end_time,sc.id_number FROM `student_check` sc 
LEFT JOIN class c on sc.class_id=c.class_id 
LEFT JOIN semester s on s.semester_id=c.semester_id 
LEFT JOIN student_class_relation scr on scr.student_id=sc.student_id  
where modify_name <> '' and (class_name <>'' or class_name is not null ) and sc.is_relation=0  and s.semester_id <>'' and scr.status=0  
) t GROUP BY modify_name,semester_id   ";

        $student_all_data = $this->sConn->createCommand($sql)->queryAll();

        $nameArr=array();
        $needRelation=array();
        if($student_all_data){
            foreach ($student_all_data as $item){
                $nameArr[$item['modify_name']][]=$item;
            }

            foreach ($nameArr as $val){
                $normal=true;
                foreach ($val as $v){
                    if($v['count']>1){
                        $normal=false;  //重复数量大于1，异常数据
                        break;
                    }
                }

                if($normal && count($val)>1){   //同学期不重复，且多个学期存在，需要关联
                    $needRelation[]=$val;
                }
            }

            $count=0;
            $needUpdateStudentId=array();
            if($needRelation){
                $insertSql="Replace into student_relation(`master_student_id`,`relation_student_id`) values ";
                $values=array();
                foreach ($needRelation as $val){
                    $count+=count($val);
                    $master_student_id=0;
                    $relation_student_id=array();
                    foreach ($val as $v){
                        if(!$master_student_id) $master_student_id=$v['student_id'];
                        $relation_student_id[]=$v['student_id'];
                        $needUpdateStudentId[]=$v['student_id'];
                    }
                    if($master_student_id && $relation_student_id){
                        $values[]="('".$master_student_id."','".implode(',',$relation_student_id)."')";
                    }

                }
                if($values){
                    $transcation = $this->sConn->beginTransaction();
                    try {
                        $insertSql.=implode(',',$values);
                        $this->sConn->createCommand($insertSql)->execute();

                        $updateSql="update student_check set is_relation=1 where student_id in(".implode(',',$needUpdateStudentId).")";
                        $this->sConn->createCommand($updateSql)->execute();
                        $transcation->commit();
                        $result['status']=1;
                    }catch (Exception $e){
                        $transcation->rollback();
                    }

                }
            }else{
                $result['status']=1;
            }
            $result['count']=$count;
        }


        exit(json_encode($result));
    }

    //重名数据
    public function actionAjaxRepeat(){
        $result['status']=0;
        //查询同一学期名字有重复的学生，这类学生账号是异常账号，需要处理

        $sql="select count(*) as count,modify_name,semester_id,student_id from (
SELECT sc.class_id,sc.student_id,sc.modify_name,sc.is_relation,c.class_name,s.semester_id,s.semester_name,s.school_year,s.end_time,sc.id_number,sc.is_ignore FROM `student_check` sc 
LEFT JOIN class c on sc.class_id=c.class_id 
LEFT JOIN semester s on s.semester_id=c.semester_id 
LEFT JOIN student_class_relation scr on scr.student_id=sc.student_id  
where modify_name <> '' and (class_name <>'' or class_name is not null ) and sc.is_relation=0  and s.semester_id <>'' and scr.status=0  and sc.is_ignore=0
) t GROUP BY modify_name,semester_id having  count>1 ";
        $student_all_data = $this->sConn->createCommand($sql)->queryAll();


        if(!$student_all_data){
            $result['status']=1;
            $result['count']=0;
        }else{
            $nameArr=array();
            foreach ($student_all_data as $val){
                $nameArr[$val['modify_name']][]=$val;
            }

            $result['status']=1;
            $result['count']=count($nameArr);
        }
        //已检测到最后一步，做标记
        $sql_u=StudentCheck::model()->find();
        $sql_u->last_step=1;
        $sql_u->save();

        exit(json_encode($result));
    }
    private function countRepeatData(){
        //查询同一学期名字有重复的学生，这类学生账号是异常账号，需要处理

        $sql="select count(*) as count,modify_name,semester_id from (";
        $sql.="SELECT sc.class_id,sc.student_id,sc.modify_name,sc.is_relation,c.class_name,s.semester_id,s.semester_name,s.school_year,s.end_time,sc.id_number,sc.is_ignore FROM `student_check` sc ";
        $sql.="LEFT JOIN class c on sc.class_id=c.class_id ";
        $sql.="LEFT JOIN semester s on s.semester_id=c.semester_id ";
        $sql.="LEFT JOIN student_class_relation scr on scr.student_id=sc.student_id ";
        $sql.=" where modify_name <> '' and (class_name <>'' or class_name is not null ) and sc.is_relation=0  and s.semester_id <>'' and scr.status=0 and sc.is_ignore=0 ";
        $sql.=" ) t ";
        $sql.="GROUP BY modify_name,semester_id ";
        $sql.="having  count>1  ";


        $student_all_data = $this->sConn->createCommand($sql)->queryAll();


        if(!$student_all_data){
            return 0;
        }else{
            $nameArr=array();
            foreach ($student_all_data as $val){
                $nameArr[$val['modify_name']][]=$val;
            }

           return count($nameArr);
        }
    }
    //完成保存处理结果
    public function actionAjaxUpdateRealName(){
        $result['status']=0;
        $sql=" update student_info si,student_check sc set si.realname=sc.modify_name,si.id_number=sc.id_number where  si.student_id=sc.student_id and modify_name is not null ";
        $this->sConn->createCommand($sql)->execute();
        $del_sql="truncate  `student_check`;";
        $this->sConn->createCommand($del_sql)->execute();
        $result['status']=1;
        if(Yii::app()->params['handle_log_on_off'])
        {
            writeFileLog(jsonEncode(array(
                "exam_group_id" => 0,
                "operate_project" => 'zsyas2',
                "school_id" => $this->schoolId,
                "title" => '检测学生',
                "operate_account" => Yii::app()->session['coachInfo']['coach_name'],
                "operate_method" => $this->action,
                "operate_url" => $this->getRoute(),
                "operate_sql" =>'',
                "operate_param" =>'',
                "date"=>date('Y-m-d H:i:j')
            )));

        }
        exit(json_encode($result));
    }

    //处理重名
    public function actionHandleData(){
	    $page=Req::post('page');
	    $name_like=Req::post('name');
	    if(!$page) $page=1;


        //查询同一学期名字有重复的学生，这类学生账号是异常账号，需要处理
        $sql="select count(*) as count,modify_name from (";
        $sql.="SELECT sc.class_id,sc.student_id,sc.modify_name,sc.is_relation,c.class_name,s.semester_id,s.semester_name,s.school_year,s.end_time,sc.id_number,sc.is_ignore FROM `student_check` sc ";
        $sql.="LEFT JOIN class c on sc.class_id=c.class_id ";
        $sql.="LEFT JOIN semester s on s.semester_id=c.semester_id ";
        $sql.="LEFT JOIN student_class_relation scr on scr.student_id=sc.student_id ";
        $sql.=" where modify_name <> '' and (class_name <>'' or class_name is not null ) and sc.is_relation=0  and s.semester_id <>'' and scr.status=0 and sc.is_ignore=0 ";
        if($name_like){
            $sql.=" and modify_name like '%".$name_like."%' ";
        }
        $sql.=" ) t ";
        $sql.="GROUP BY modify_name ";
        $sql.="having  count>1  ";

        $sql.="order by count desc ;";

        $nameArr=array();
        $student_all_data = $this->sConn->createCommand($sql)->queryAll();


        if($student_all_data){
            foreach ($student_all_data as $val){
                $nameArr[$val['modify_name']]=$val;
            }
        }

        $nameArr=array_values($nameArr);

        $pageSize=10;

        $offset=($page-1)*$pageSize;
        $total=count($nameArr);
        $data['total_page']=ceil($total/$pageSize);
        $data['name_group']=array_slice($nameArr,$offset,$pageSize);
        $data['page']=$page;


        //debug($nameArr);
        if(Yii::app()->request->isAjaxRequest){
            $result['status']=1;
            $result['data']=$data['name_group'];
            $result['total_page']=$data['total_page'];
            exit(json_encode($result));
        }else{
            $this->render('handle', $data);
        }

    }

    //查询要处理的一组学生
    public function actionShowRepeatStudent(){
	    $name=Req::post('name');

        $sql="SELECT sc.realname,sc.class_id,sc.student_id,sc.modify_name,sc.is_relation,sc.is_outer,sc.id_number,c.class_name,s.semester_id,s.semester_name,s.school_year,s.end_time,sc.is_ignore FROM `student_check` sc ";
        $sql.="LEFT JOIN class c on sc.class_id=c.class_id ";
        $sql.="LEFT JOIN semester s on s.semester_id=c.semester_id ";
        $sql.=" where modify_name='".$name."' and (class_name <>'' or class_name is not null ) and sc.is_relation=0  and s.semester_id <>'' and sc.is_ignore=0 ";


        $sql.="order by end_time desc ;";

       
        $nameArr=array();
        $_allStudent = $this->sConn->createCommand($sql)->queryAll();

        $studentData=array();
        $businessData=array();
        $semester=array();
        $studentId=array();
        $html='';
        if($_allStudent){
            foreach ($_allStudent as $val){
                $val['serial_number']=0;
                $val['userno']=0;
                //班级信息
                $sql_class_relation="select * from student_class_relation where student_id='".$val['student_id']."' and status=0 ";
                $student_class_realtion=$this->sConn->createCommand($sql_class_relation)->queryRow();
                if($student_class_realtion){
                    $val['serial_number']=$student_class_realtion['serial_number'];
                    $val['userno']=$student_class_realtion['userno'];
                }else{
                    continue;
                }

                //查询副号
                $sql_fuhao="select * from student_relation where find_in_set('".$val['student_id']."',relation_student_id)";
                $student_fuhao=$this->sConn->createCommand($sql_fuhao)->queryRow();
                $val['fuhao']=0;
                if($student_fuhao){
                    if($val['student_id']!=$student_fuhao['master_student_id']){
                        continue;
                    }else{
                        $fuhao_arr=explode(',',$student_fuhao['relation_student_id']);
                        $val['fuhao']=count($fuhao_arr)-1;
                    }
                }
                $studentData[$val['semester_id']][]=$val;
                $semester[$val['semester_id']]=$val['semester_name'];
                $studentId[]=$val['student_id'];

            }


            $i=1;

            $currSemester = $this->schoolManager->getCurrSemester();

            $smid=$currSemester['semester_id'];
            foreach ($semester as $key=> $val){
                $semesterStatus='非当前学期';
                if($smid==$key){
                    $semesterStatus='当前学期';
                }
       
                $html.='<div class="handel-term-list">';
                if($smid==$key){
                    $html.='<div class="term-name fw">'.$val.'</div>';
                }else{
                    $html.='<div class="term-name fw">'.$val.'<span class="green-font pointer" semester="'.$key.'">忽略</span></div>';
                }

                    $html.='<div class="relative flex">';
                        $html.='<img src="/images/prev-arrow.png" alt="" class="prev-arrow arrow-img pointer">';
                        $html.='<div class="term-card-layout flex-one" >';
                            $html.='<ul class="term-card-detail">';
                            if(isset($studentData[$key])){
                               // debug($studentData[$key]);
                                foreach ($studentData[$key] as $item){
                                    $isZj='是';
                                    if($item['is_outer']==1){
                                        $isZj='否';
                                    }

                                    //查询business

                                    $b_student_data= $this->conn->createCommand("select username,student_card,school_student_card,zhixue_student_card from `student` where student_id='".$item['student_id']."'")->queryRow();

                                    if($b_student_data){
                                            $businessData[$item['student_id']]=$b_student_data;
                                    }

                                    $html.='<li class="term-card-list" id="'.$item['student_id'].'"  draggable="true" >';
                                        $html.='<div class="padding-layout">';
                                            $html.='<div class="card-lis-data flex">';
                                                $html.='<span class="card-list-menu gray-font">姓名</span>';
                                                $html.='<span class="card-list-msg flex-one">'.$item['modify_name'].'（原'.$item['realname'].'）</span>';
                                            $html.='</div>';
                                            $html.='<div class="card-lis-data flex">';
                                                $html.='<span class="card-list-menu gray-font">班级</span>';
                                                $html.='<span class="card-list-msg flex-one">'.$item['class_name'].'</span>';
                                            $html.='</div>';
                                            $html.='<div class="hide-list">';
                                                $html.='<div class="card-lis-data flex">';
                                                    $html.='<span class="card-list-menu gray-font">学期</span>';
                                                    $html.='<span class="card-list-msg flex-one">'.$val.'</span>';
                                                $html.='</div>';
                                                $html.='<div class="card-lis-data flex">';
                                                    $html.='<span class="card-list-menu gray-font">学期状态</span>';
                                                    $html.='<span class="card-list-msg flex-one">'.$semesterStatus.'</span>';
                                                $html.='</div>';
                                                $html.='<div class="card-lis-data flex">';
                                                    $html.='<span class="card-list-menu gray-font">班级序号</span>';
                                                    $html.='<span class="card-list-msg flex-one">'.$item['serial_number'].'</span>';
                                                $html.='</div>';
                                                $html.='<div class="card-lis-data flex">';
                                                    $html.='<span class="card-list-menu gray-font">学号</span>';
                                                    $html.='<span class="card-list-msg flex-one">'.$item['userno'].'</span>';
                                                $html.='</div>';
                                                $html.='<div class="card-lis-data flex">';
                                                    $html.='<span class="card-list-menu gray-font">登录账号</span>';
                                                    if(isset($businessData[$item['student_id']])){
                                                        $html.='<span class="card-list-msg flex-one">'.$businessData[$item['student_id']]['username'].'</span>';
                                                    }else{
                                                        $html.='<span class="card-list-msg flex-one"></span>';
                                                    }

                                                $html.='</div>';
                                                $html.='<div class="card-lis-data flex">';
                                                    $html.='<span class="card-list-menu gray-font">系统准考证号</span>';
                                                    if(isset($businessData[$item['student_id']])){
                                                        $html.='<span class="card-list-msg flex-one">'.$businessData[$item['student_id']]['student_card'].'</span>';
                                                    }else{
                                                        $html.='<span class="card-list-msg flex-one"></span>';
                                                    }

                                                $html.='</div>';
                                                $html.='<div class="card-lis-data flex">';
                                                    $html.='<span class="card-list-menu gray-font">学校准考证号</span>';
                                                    if(isset($businessData[$item['student_id']])){
                                                        $html.='<span class="card-list-msg flex-one">'.$businessData[$item['student_id']]['school_student_card'].'</span>';
                                                    }else{
                                                        $html.='<span class="card-list-msg flex-one"></span>';
                                                    }

                                                $html.='</div>';
                                                $html.='<div class="card-lis-data flex">';
                                                    $html.='<span class="card-list-menu gray-font">智学网考号</span>';
                                                    if(isset($businessData[$item['student_id']])){
                                                        $html.='<span class="card-list-msg flex-one">'.$businessData[$item['student_id']]['zhixue_student_card'].'</span>';
                                                    }else{
                                                        $html.='<span class="card-list-msg flex-one"></span>';
                                                    }

                                                $html.='</div>';
                                                $html.='<div class="card-lis-data flex">';
                                                    $html.='<span class="card-list-menu gray-font">身份证号</span>';
                                                    $html.='<span class="card-list-msg flex-one"><img src="/images/edit.png" alt=""  draggable="false" class="edit-icon"><input data="'.$item['student_id'].'" type="text" value="'.$item['id_number'].'" class="edit-self-no"></span>';
                                                $html.='</div>';
                                                $html.='<div class="card-lis-data flex">';
                                                    $html.='<span class="card-list-menu gray-font">是否为在籍生</span>';
                                                    $html.='<span class="card-list-msg flex-one">'.$isZj.'</span>';
                                                $html.='</div>';

                                                $html.='<div class="card-lis-data flex">';
                                                    $html.='<span class="card-list-menu gray-font">历史考试成绩</span>';
                                                    $html.='<span rel="'.$item['student_id'].'" class="card-list-msg flex-one browse-history green-font">查看</span>';
                                                $html.='</div>';

                                                $html.='<div class="card-lis-data flex">';
                                                    $html.='<span class="card-list-menu gray-font">副号</span>';
                                                    $html.='<span rel="'.$item['student_id'].'" class="card-list-msg flex-one green-font pointer fuhao-lock">'.$item['fuhao'].'</span>';
                                                $html.='</div>';

                                            $html.='</div>';
                                            $html.='<span class="toggle-opt-btn green-font pointer">展开</span>';
                                        $html.='</div>';
                                    $html.='</li>';
                                    $i++;
                                }
                            }
                            $html.='</ul>';
                        $html.='</div>';
                        $html.='<img src="/images/next-arrow.png" alt="" class="next-arrow arrow-img pointer">';
                    $html.='</div>';
                $html.='</div>';
            }
        }

        $result['data']=$html;
        $result['status']=1;
        exit(json_encode($result));
    }

    //保存身份证
    public function actionSaveIdNumber(){
	    $student_id=Req::post('student_id');
	    $id_number=Req::post('id_number');

	    $result['status']=0;
	    if(!$student_id || !$id_number){
            exit(json_encode($result));
        }
        //判断系统中是否存在
        if($this->schoolManager->checkStudentIdNumber($id_number)){
            $result['status']=0;
            $result['msg']='系统中已存在相同身份证，请检查后重新确认';
            exit(json_encode($result));
        }

        //判断表格中是否存在
        $sql="select student_id from student_check where id_number='".$id_number."' ";
        $data=$this->sConn->createCommand($sql)->queryRow();
        if($data){
            $result['msg']='已存在相同身份证，请检查后重新确认';
            exit(json_encode($result));
        }

        $info	=	StudentCheck::model()->find('student_id=:stid',array(':stid'=>$student_id));
        if(!$info){
            $result['msg']='学生不存在';
            exit(json_encode($result));
        }
        $info->id_number=$id_number;
        if($info->save()){
            $result['status']=1;
            exit(json_encode($result));
        }
    }

    //查询副号
    public function actionGetSlaves(){
	    $student_id=Req::post('student_id');

        $result['status']=0;
        if(!$student_id ){
            exit(json_encode($result));
        }

        $sql="select * from student_relation where find_in_set('".$student_id."',relation_student_id) limit 1 ";
        $_allStudent = $this->sConn->createCommand($sql)->queryRow();
        if($_allStudent && isset($_allStudent['relation_student_id'])){
            $studentIds=explode(',',$_allStudent['relation_student_id']);
            unset($studentIds[array_search($student_id,$studentIds)]);
            //查询
            $sql="SELECT sc.realname,sc.class_id,sc.student_id,sc.modify_name,sc.is_relation,sc.is_outer,sc.id_number,c.class_name,s.semester_id,s.semester_name,s.school_year,s.end_time,scr.serial_number,scr.userno,scr.status FROM `student_check` sc ";
            $sql.="LEFT JOIN class c on sc.class_id=c.class_id ";
            $sql.="LEFT JOIN semester s on s.semester_id=c.semester_id ";
            $sql.="LEFT JOIN student_class_relation scr on scr.student_id=sc.student_id ";
            $sql.=" where sc.student_id in(".implode(',',$studentIds).") and scr.status=0 ";
            $sql.="order by end_time desc ;";
            $nameArr=array();
            $_allStudent = $this->sConn->createCommand($sql)->queryAll();
            $studentData=array();
            $businessData=array();
            $semester=array();
            $studentId=array();
            if($_allStudent){
                foreach ($_allStudent as $val){
                    $studentId[]=$val['student_id'];
                    //查询副号
                }

                //查询business

                $criteria	=	new CDbCriteria();
                $b_student 	=	array();
                $criteria->addInCondition('student_id',$studentId);
                $b_student_data	=	BusinessStudent::model()->findAll($criteria);
                if($b_student_data){
                    foreach($b_student_data as $v)
                    {
                        $businessData[$v->student_id]=array(
                            'username'=>$v->username,
                            'student_card'=>$v->student_card,
                            'school_student_card'=>$v->school_student_card,
                            'zhixue_student_card'=>$v->zhixue_student_card,
                        );
                    }
                }
                $html='';
                $i=1;
                $smid	=	 safe_replace(Yii::app()->request->getParam('semesterId'));


                foreach ($_allStudent as $item){
                    $isZj='是';
                    if($item['is_outer']==1){
                        $isZj='否';
                    }
                    $semesterStatus='非当前学期';
                    if($smid==$item['semester_id']){
                        $semesterStatus='当前学期';
                    }
                    $html.='<li class="countermark-list">';
                    $html.='<div class="card-lis-data flex">';
                    $html.='<span class="card-list-menu gray-font">姓名</span>';
                    $html.='<span class="card-list-msg flex-one">'.$item['modify_name'].'（原'.$item['realname'].'）</span>';
                    $html.='</div>';
                    $html.='<div class="card-lis-data flex">';
                    $html.='<span class="card-list-menu gray-font">班级</span>';
                    $html.='<span class="card-list-msg flex-one">'.$item['class_name'].'</span>';
                    $html.='</div>';

                    $html.='<div class="card-lis-data flex">';
                    $html.='<span class="card-list-menu gray-font">学期</span>';
                    $html.='<span class="card-list-msg flex-one">'.$item['semester_name'].'</span>';
                    $html.='</div>';
                    $html.='<div class="card-lis-data flex">';
                    $html.='<span class="card-list-menu gray-font">学期状态</span>';
                    $html.='<span class="card-list-msg flex-one">'.$semesterStatus.'</span>';
                    $html.='</div>';
                    $html.='<div class="card-lis-data flex">';
                    $html.='<span class="card-list-menu gray-font">班级序号</span>';
                    $html.='<span class="card-list-msg flex-one">'.$item['serial_number'].'</span>';
                    $html.='</div>';
                    $html.='<div class="card-lis-data flex">';
                    $html.='<span class="card-list-menu gray-font">学号</span>';
                    $html.='<span class="card-list-msg flex-one">'.$item['userno'].'</span>';
                    $html.='</div>';
                    $html.='<div class="card-lis-data flex">';
                    $html.='<span class="card-list-menu gray-font">登录账号</span>';
                    if(isset($businessData[$item['student_id']])){
                        $html.='<span class="card-list-msg flex-one">'.$businessData[$item['student_id']]['username'].'</span>';
                    }else{
                        $html.='<span class="card-list-msg flex-one"></span>';
                    }

                    $html.='</div>';
                    $html.='<div class="card-lis-data flex">';
                    $html.='<span class="card-list-menu gray-font">系统准考证号</span>';
                    if(isset($businessData[$item['student_id']])){
                        $html.='<span class="card-list-msg flex-one">'.$businessData[$item['student_id']]['student_card'].'</span>';
                    }else{
                        $html.='<span class="card-list-msg flex-one"></span>';
                    }

                    $html.='</div>';
                    $html.='<div class="card-lis-data flex">';
                    $html.='<span class="card-list-menu gray-font">学校准考证号</span>';
                    if(isset($businessData[$item['student_id']])){
                        $html.='<span class="card-list-msg flex-one">'.$businessData[$item['student_id']]['school_student_card'].'</span>';
                    }else{
                        $html.='<span class="card-list-msg flex-one"></span>';
                    }

                    $html.='</div>';
                    $html.='<div class="card-lis-data flex">';
                    $html.='<span class="card-list-menu gray-font">智学网考号</span>';
                    if(isset($businessData[$item['student_id']])){
                        $html.='<span class="card-list-msg flex-one">'.$businessData[$item['student_id']]['zhixue_student_card'].'</span>';
                    }else{
                        $html.='<span class="card-list-msg flex-one"></span>';
                    }

                    $html.='</div>';
                    $html.='<div class="card-lis-data flex">';
                    $html.='<span class="card-list-menu gray-font">身份证号</span>';
                    $html.='<span class="card-list-msg flex-one">'.$item['id_number'].'</span>';
                    $html.='</div>';
                    $html.='<div class="card-lis-data flex">';
                    $html.='<span class="card-list-menu gray-font">是否为在籍生</span>';
                    $html.='<span class="card-list-msg flex-one">'.$isZj.'</span>';
                    $html.='</div>';
                    $html.='<span rel="'.$item['student_id'].'" data="'.$item['modify_name'].'" class="relieve-relate green-font pointer">解除关联</span>';
                    $html.='</li>';
                    $i++;
                }



            }
        }

        $result['data']=$html;
        $result['status']=1;
        exit(json_encode($result));
    }

    //学生历史成绩
    public function actionGetHistoryScore(){
        $student_id=Req::post('student_id');
        $result['status']=0;

        if(!$student_id ){
            exit(json_encode($result));
        }

        $Sql="SELECT scoring,spr.class_id,spr.exam_id,e.tpl_data,e.exam_group_id,e.name FROM `student_paper_relation` spr ";
        $Sql.="join exam e on spr.exam_id=e.exam_id ";
        $Sql.=" where student_id='".$student_id."' and e.status=1 order by e.create_time desc limit 10";
        $All_Exam=$this->sConn->createCommand($Sql)->queryAll();

        $list=array();
        if($All_Exam){
            $result['status']=1;
            foreach ($All_Exam as $val){

                $class_id=$val['class_id'];
                $exam_id=$val['exam_id'];


                $exam_date='';
                if(isset($val['tpl_data']) && $tpl_data=json_decode($val['tpl_data'],true)){
                    $exam_date=$tpl_data['examDate'];
                }
                //班级平均分
                $sql_c="SELECT AVG(scoring) as avg FROM `student_paper_relation` where exam_id='.$exam_id.' ";
                $class_avg = $this->sConn->createCommand($sql_c)->queryRow();

                //年级平均分
                $sql_g="SELECT AVG(scoring) as avg from student_paper_relation where exam_id in( SELECT exam_id from exam where exam_group_id =".$val['exam_group_id'].")";
                $grade_avg = $this->sConn->createCommand($sql_g)->queryRow();

                //班级排名
                $sql_cr="SELECT student_id FROM `student_paper_relation` where exam_id='".$exam_id."' order by scoring desc";
                $class_all_score = $this->sConn->createCommand($sql_cr)->queryAll();

                $class_rank=0;
                if($class_all_score){
                    foreach ($class_all_score as $key=> $item){
                        if($item['student_id']==$student_id){
                            $class_rank=($key+1);
                            break;
                        }
                    }
                }
                //年级排名
                $sql_gr="SELECT student_id from student_paper_relation where exam_id in( SELECT exam_id from exam where exam_group_id =".$val['exam_group_id'].") order by scoring desc";
                $grade_all_data = $this->sConn->createCommand($sql_gr)->queryAll();
                $grade_rank=0;

                if($grade_all_data){
                    foreach ($grade_all_data as $key=>$item){
                        if($item['student_id']==$student_id){
                            $grade_rank=($key+1);
                            break;
                        }
                    }

                }

                $list[]=array(
                    'score'=>$val['scoring'],
                    'exam_name'=>$val['name'],
                    'exam_date'=>$exam_date,
                    'class_avg'=>number_format($class_avg['avg'],2),
                    'grade_avg'=>number_format($grade_avg['avg'],2),
                    'class_rank'=>$class_rank,
                    'grade_rank'=>$grade_rank
                );

            }
            $result['data']=$list;
        }
        exit(json_encode($result));

    }

    //手动关联账号
    public function actionRelationHand(){
        $startStudentId=Req::post('startCardId');   //辅号
        $endStudentId=Req::post('endCardId');       //主账号

        $result['status']=0;
        $result['number']=0;
        $result['msg']='合并失败';
        if(!$startStudentId || !$endStudentId){
            $result['msg']='参数不正确';
            exit(json_encode($result));
        }
        //查询身份证号
        $masterStudent=$this->sConn->createCommand("select id_number from student_check where student_id='".$endStudentId."'")->queryRow();
        $slavesStudent=$this->sConn->createCommand("select id_number from student_check where student_id='".$startStudentId."'")->queryRow();
        if(!$masterStudent || !$slavesStudent){
            $result['msg']='参数不正确';
            exit(json_encode($result));
        }

        $updateStudentCheck='';
        if(!$masterStudent['id_number'] && !$slavesStudent['id_number']){
            $result['msg']='关联学生缺少身份证号';
            exit(json_encode($result));
        }
        if(!$masterStudent['id_number'] && $slavesStudent['id_number']){
            $updateStudentCheck="update student_check set id_number='".$slavesStudent['id_number']."' where student_id='".$endStudentId."'";
        }elseif($masterStudent['id_number'] && !$slavesStudent['id_number']){
            $updateStudentCheck="update student_check set id_number='".$masterStudent['id_number']."' where student_id='".$startStudentId."'";
        }elseif($masterStudent['id_number']!=$slavesStudent['id_number']){
            $result['msg']='身份证号不匹配!';
            exit(json_encode($result));
        }

        //查询被拖动账号有没有关联记录
        $sql_s="select * from student_relation where find_in_set('".$startStudentId."',relation_student_id);";
        $slaves_relation = $this->sConn->createCommand($sql_s)->queryRow();

        //主账号有没有关联记录
        $sql_m="select * from student_relation where find_in_set('".$endStudentId."',relation_student_id);";
        $master_Relation = $this->sConn->createCommand($sql_m)->queryRow();
        if($slaves_relation){
            if($master_Relation){//主账号有关联
                if($master_Relation['master_student_id']!=$slaves_relation['master_student_id']){
                    $m_relation_student_id=explode(',',$master_Relation['relation_student_id']);
                    $s_relation_student_id=explode(',',$slaves_relation['relation_student_id']);
                    $relation_student_id=array_unique(array_merge($m_relation_student_id,$s_relation_student_id));
                    $sql_u="update student_relation set relation_student_id='".implode(',',$relation_student_id)."' where master_student_id='".$master_Relation['master_student_id']."';";
                    $result['number']=count($relation_student_id)-1;
                }
            }else{  //主账号无关联
                $relation_student_id=explode(',',$slaves_relation['relation_student_id']);
                if(!in_array((string)$endStudentId,$relation_student_id,true)){
                    $relation_student_id[]=$endStudentId;
                    $sql_u="update student_relation set master_student_id='".$endStudentId."',relation_student_id='".implode(',',$relation_student_id)."' where master_student_id='".$slaves_relation['master_student_id']."';";
                    $result['number']=count($relation_student_id)-1;
                }

            }
        }else{     //辅号无关联记录
            if($master_Relation){ //主号有关联记录
                $m_relation_student_id=explode(',',$master_Relation['relation_student_id']);
                if(!in_array((string)$startStudentId,$m_relation_student_id,true)){
                    $m_relation_student_id[]=$startStudentId;
                    $sql_u="update student_relation set relation_student_id='".implode(',',$m_relation_student_id)."' where master_student_id='".$master_Relation['master_student_id']."';";
                    $result['number']=count($m_relation_student_id)-1;
                }
            }else{//主号无关联记录
                $relation_student_id=array($startStudentId,$endStudentId);
                $sql_u="insert into student_relation(`master_student_id`,`relation_student_id`) values ('".$endStudentId."','".implode(',',$relation_student_id)."');";
                $result['number']=1;
            }
        }

        if(isset($sql_u)){
            $this->sConn->createCommand($sql_u)->execute();
            if($updateStudentCheck){
                $this->sConn->createCommand($updateStudentCheck)->execute();
            }
            $result['status']=1;

        }

        exit(json_encode($result));
    }

    //解除关联
    public function actionCancelRelation(){
	    $student_id=Req::post('student_id');
        $result['status']=0;
        if(!$student_id){
            exit(json_encode($result));
        }

        //查询被拖动账号有没有关联记录
        $sql_s="select * from student_relation where find_in_set('".$student_id."',relation_student_id);";
        $slaves_relation = $this->sConn->createCommand($sql_s)->queryRow();
        if(!$slaves_relation){
            $result['msg']='无关联记录';
            exit(json_encode($result));
        }
        $relation_student_id=explode(',',$slaves_relation['relation_student_id']);
        if(in_array((string)$student_id,$relation_student_id,true)){
            unset($relation_student_id[array_search($student_id,$relation_student_id)]);
            if(count($relation_student_id)<2){
                $sql_u="delete from  student_relation  where master_student_id='".$slaves_relation['master_student_id']."'";
                if($this->sConn->createCommand($sql_u)->execute()){
                    $result['status']=1;
                    $sql="update student_check set is_relation=0 where student_id='".$student_id."' or student_id='".$slaves_relation['master_student_id']."' ";
                    $this->sConn->createCommand($sql)->execute();

                }
            }else{
                $sql_u="update student_relation set relation_student_id='".implode(',',$relation_student_id)."' where master_student_id='".$slaves_relation['master_student_id']."'";
                if($this->sConn->createCommand($sql_u)->execute()){
                    $result['status']=1;

                }
            }


        }
        exit(json_encode($result));
    }

    //检查
    public function actionCheckHandle(){
	    $name=Req::post('name');
	    $result['status']=0;
        $sql="SELECT sc.realname,sc.class_id,sc.student_id,sc.modify_name,sc.is_relation,sc.is_outer,sc.id_number,c.class_name,s.semester_id,s.semester_name,s.school_year,s.end_time,sc.is_ignore FROM `student_check` sc ";
        $sql.="LEFT JOIN class c on sc.class_id=c.class_id ";
        $sql.="LEFT JOIN semester s on s.semester_id=c.semester_id ";
        $sql.="LEFT JOIN student_class_relation scr on scr.student_id=sc.student_id  ";
        $sql.=" where modify_name='".$name."'  and (class_name <>'' or class_name is not null ) and sc.is_relation=0  and s.semester_id <>'' and scr.status=0 and sc.is_ignore=0 ";
        $sql.="order by end_time desc ;";



        $nameArr=array();
        $_allStudent = $this->sConn->createCommand($sql)->queryAll();

        if(!$_allStudent){
            //没有未处理学生，表示这一组已处理完毕
            $result['status']=1;
        }else{
            $studentIds=array();
            //检查身份证号
            foreach ($_allStudent as $key=> $val){
                if($val['id_number']){
                    $studentIds[]=$val['student_id'];
                    unset($_allStudent[$key]);
                }else{
                    //查检关联
                    $sql_r="select * from student_relation where find_in_set('".$val['student_id']."',relation_student_id)";
                    $Relation = $this->sConn->createCommand($sql_r)->queryRow();
                    if($Relation){
                        $studentIds[]=$val['student_id'];
                        unset($_allStudent[$key]);
                    }
                }
            }

            if(!empty($_allStudent)){
                if(count($_allStudent)==1){
                    $result['status']=1;    //处理完毕
  
                }else{
                    $result['status']=2; //还需要继续处理
                }

            }else{
                $result['status']=1;    //处理完毕
            }
            if($studentIds){
                //处理完毕，标记
                $sql_u="update `student_check` set is_relation=1 where student_id in(".implode(',',$studentIds).")";
                $this->sConn->createCommand($sql_u)->execute();
            }
        }
        exit(json_encode($result));
    }

    //放弃检测
    public function actionGiveUp(){
        $result['status']=0;
        $del_sql="truncate  `student_check`;";
        $this->sConn->createCommand($del_sql)->execute();
        $result['status']=1;
        exit(json_encode($result));
    }

    public function actionTest(){
        $data=array();
        $this->render('test', $data);
    }
    //忽略功能
    public function actionIgnore(){
        $result['status']=0;
        $name=Req::post('name');
        $semester=Req::post('semester');

        $sql="SELECT sc.student_id FROM `student_check` sc ";
        $sql.="LEFT JOIN class c on sc.class_id=c.class_id ";
        $sql.="LEFT JOIN semester s on s.semester_id=c.semester_id ";
        $sql.=" where modify_name='".$name."' and (class_name <>'' or class_name is not null ) and sc.is_relation=0  and s.semester_id ='".$semester."' ";
        $data = $this->sConn->createCommand($sql)->queryAll();
        $studentIds=array();
        if($data){
            foreach ($data as $val){
                $studentIds[]=$val['student_id'];
            }
        }
        if($studentIds){
           $rs= $this->sConn->createCommand("update `student_check` set is_ignore=1 where student_id in(".implode(',',$studentIds).")")->execute();
           if($rs){
               $result['status']=1;
           }
        }
        exit(json_encode($result));
    }
}