<?php
/**
 * @author: CeeFee
 * @description: 考试
 */

class SExam extends Model {
	function __construct(){
		parent::__construct();
	}
	
	public function getDetailByExamId($examId)
	{
		$result = array();
		
		if ($examId AND is_numeric($examId))
		{
			$result = $this->sConn->createCommand()
					->from('exam')
					->where("exam_id = '". $examId ."'")
					->query()
					->read();
		}
		
		return $result;
	}
	
	public function getDetailByExamId_SemesterId($examId, $semesterId)
	{
		$result = array();
		
		if ($examId AND is_numeric($examId) AND $semesterId AND is_numeric($semesterId))
		{
			$result = $this->sConn->createCommand()
					->from('exam')
					->where("exam_id = '". $examId ."' AND semester_id = '". $semesterId ."'")
					->query()
					->read();
		}
		
		return $result;
	}
	
	public function getDetailByExamId_SemesterId_TeacherId($examId, $semesterId, $teacherId)
	{
		$result = array();
		
		if ($examId AND is_numeric($examId) AND $semesterId AND is_numeric($semesterId) AND $teacherId AND is_numeric($teacherId))
		{
			$result = $this->sConn->createCommand()
					->from('exam')
					->where("exam_id = '". $examId ."' AND semester_id = '". $semesterId ."' AND teacher_id = '". $teacherId ."'")
					->query()
					->read();
		}
		
		return $result;
	}
	
	public function getMaxWeekNumByClassId_SemesterId($classId, $semesterId)
	{
		$result = 0;
		
		if ($classId AND is_numeric($classId) AND $semesterId AND is_numeric($semesterId))
		{
			$sql = "SELECT IFNULL(MAX(e.week_num), 0) AS MAX_VALUE "
				 . "FROM exam e "
				 . "WHERE e.semester_id = '". $semesterId ."' AND e.class_id = '". $classId ."'";
			$query  = $this->sConn->createCommand($sql)->query()->read();
			$result = (int)$query['MAX_VALUE'];
		}
		
		return $result;
	}

	/*
         * 根据学期、科目读取考试
         * $is_all_quan 0-所有考试 1-全学科考试 2-非全学科考试
         */
        
    public function getExamBySubject_Semester($SubjectId,$SemesterId,$Grade,$Arts_science=0,$is_all_quan=0){
        if(!$SubjectId || !$SemesterId){
            return null;
        }

        $sql=" select exam_id,e.exam_group_id,`name`,e.create_time  from exam e  ";
        $sql.=" join exam_group g on e.exam_group_id=g.exam_group_id ";
        $sql.=" join class c on c.class_id=e.class_id ";
        $sql.=" where e.semester_id={$SemesterId} ";
        if(is_array($SubjectId)){
            $sql.="and e.subject_id in(".implode(',',$SubjectId).")";
        }else{
            $sql.="and e.subject_id={$SubjectId}";
        }
        if($Grade){
            $sql.=" and c.grade in(".implode(',',$Grade).") ";
        }
        if($Arts_science){
            $sql.=" and c.arts_science ={$Arts_science} ";
        }
        if($is_all_quan == 1){
            $sql.=" and g.qxk_paper_id is not null ";
        }else if($is_all_quan == 2){
             $sql.=" and g.qxk_paper_id is null ";
        }
        //$sql.=" and g.is_hide=0 and e.status=1 ";
        $sql.="  and e.status=1 and e.is_display=0";
        $sql.=" group by e.exam_group_id ";
        $sql.=" order by e.create_time desc ";
        $query  = $this->sConn->createCommand($sql)->query()->readAll();
        return $query;
    }
    /*
     * 根据学期、科目读取考试
     * $is_all_quan 0-所有考试 1-全学科考试 2-非全学科考试
     */
    public function getExamBySubject_Semester_nogroup($SubjectId,$SemesterId,$Grade,$Arts_science=0,$is_all_quan=0){
        if(!$SubjectId || !$SemesterId){
            return null;
        }

        $sql=" select exam_id,e.exam_group_id,`name`,e.create_time  from exam e  ";
        $sql.=" join exam_group g on e.exam_group_id=g.exam_group_id ";
        $sql.=" join class c on c.class_id=e.class_id ";
        $sql.=" where e.semester_id={$SemesterId} ";
        if(is_array($SubjectId)){
            $sql.="and e.subject_id in(".implode(',',$SubjectId).")";
        }else{
            $sql.="and e.subject_id={$SubjectId}";
        }
        if($Grade){
            $sql.=" and c.grade in(".implode(',',$Grade).") ";
        }
        if($Arts_science){
            $sql.=" and c.arts_science ={$Arts_science} ";
        }
        if($is_all_quan == 1){
            $sql.=" and g.qxk_paper_id is not null ";
        }else if($is_all_quan == 2){
             $sql.=" and g.qxk_paper_id is null ";
        }
        $sql.="  and e.status=1 and e.is_display=0 ";
        $sql.=" order by e.create_time desc ";

        $query  = $this->sConn->createCommand($sql)->query()->readAll();
        return $query;
    }

    /*读取阅卷完成考试数量*/
    public function CountByExamGroupId($ExamGroupIds){
        $sql="select exam_group_id from exam_group where exam_group_id in(".implode(',',$ExamGroupIds).") ";
        $sql.=" and upload_status=2 and status=2";
        return $this->sConn->createCommand($sql)->query()->count();
    }

    /*读取阅卷完成考试数量*/
    public function CountByExamClass($ExamGroupIds,$ClassIds){
        $sql="select exam_id from exam where exam_group_id in(".implode(',',$ExamGroupIds).") and class_id in(".implode(',',$ClassIds).") ";
        $sql.="  and status=1 ";
        return $this->sConn->createCommand($sql)->query()->count();
    }
    /*读取阅卷完成考试数量*/
    public function getByExamClass($ExamGroupId,$ClassId,$checkStatus=1){
        $sql="select exam_id from exam where exam_group_id ='".$ExamGroupId."' and class_id ='".$ClassId."' ";
        if($checkStatus){
            $sql.="  and status=1 ";
        }
        return $this->sConn->createCommand($sql)->query()->read();
    }

    public function getForExamGroupId_ClassId($ExamGroupId,$ClassId){
        $sql="select e.exam_id,c.class_name,e.status from exam e ";
        $sql.=" join class c on c.class_id=e.class_id ";
        $sql.=" where e.exam_group_id='".$ExamGroupId."' and e.class_id='".$ClassId."' ";
        return $this->sConn->createCommand($sql)->query()->read();
    }

    /*根据exam_group读取所有班级*/
    public function getClassByExamGroupId($ExamGroupId,$arts_science=0){
        if(!$ExamGroupId){
            return null;
        }

        if($arts_science){
            $sql="select e.exam_id,c.class_id from exam e ";
            $sql.=" join class c on c.class_id=e.class_id ";
            $sql.=" where e.exam_group_id='".$ExamGroupId."' and c.arts_science='".$arts_science."' ";
            $result= $this->sConn->createCommand($sql)->query()->readAll();
        }else{
            $result = $this->sConn->createCommand()
                ->from('exam')
                ->where("exam_group_id = '". $ExamGroupId ."' ")
                ->query()
                ->readAll();
        }

        return $result;
    }
    /*根据paper_id获取所有考试*/
    public function getExamByPaperIds($paperIds){
        if(!$paperIds || !is_array($paperIds)){
            return false;
        }
        $sql=" select e.exam_id from exam e ";
        $sql.=" join exam_group g on e.exam_group_id=g.exam_group_id ";
        $sql.=" join paper p on p.exam_id=e.exam_id ";
        $sql.=" where paper_id in(". implode(',',$paperIds) .") and g.status=1 ";
        $query  = $this->sConn->createCommand($sql)->query()->readAll();
        return $query;

    }

    public function getExamsByIds($Exam_Ids){
        if(!$Exam_Ids || !is_array($Exam_Ids)){
            return false;
        }
        $sql=" select e.exam_id,e.name,e.create_time from exam e ";
        $sql.=" join exam_group g on e.exam_group_id=g.exam_group_id ";
        $sql.=" where exam_id in(". implode(',',$Exam_Ids) .")  ";
        $query  = $this->sConn->createCommand($sql)->query()->readAll();
        return $query;
    }

    public function getExamBySubjectTime($Subject_id,$SemesterId,$ExcludeExamGroupId,$StartTime,$grade=0){

        $Sql=" select e.exam_group_id,e.exam_id,e.tpl_data,e.name from exam e ";
        $Sql.=" join class c on c.class_id=e.class_id ";
        $Sql.=" where e.semester_id = '". $SemesterId ."' AND e.exam_group_id != '". $ExcludeExamGroupId ."' and e.subject_id='".$Subject_id."' and e.create_time>'".$StartTime."' and e.is_display=0 and e.status=1 ";
        if($grade){
            $Sql.=" and c.grade={$grade} ";
        }
        $Sql.=" group by exam_group_id order by create_time desc";

        $query  = $this->sConn->createCommand($Sql)->query()->readAll();
        return $query;

    }

    public function getExamByPaperExcludeClass($Paper_id,$Class_Arr){
        if(!$Paper_id){
            return null;
        }
        $Sql=" Select e.exam_id,e.name,e.exam_group_id from exam e";
        $Sql.=" join paper p on p.exam_id=e.exam_id ";
        $Sql.=" where p.paper_id ='{$Paper_id}' and e.class_id not in (".implode(',',$Class_Arr).")";
        $query  = $this->sConn->createCommand($Sql)->query()->read();
        return $query;
    }

    public function getExamByGroupId($Exam_Group_Id){
        if(!$Exam_Group_Id){
            return false;
        }
        $result = $this->sConn->createCommand()
            ->from('exam')
            ->where(" exam_group_id = '". $Exam_Group_Id ."' and  is_display=0 ")
            ->query()
            ->readAll();
        return $result;
    }

    public function getExamSubjectId($exam_id){
        $result = 0;
        if(!$exam_id){
            return $result;
        }
        $rs = $this->sConn->createCommand()
            ->select('subject_id')
            ->from('exam')
            ->where(" exam_id = '". $exam_id ."'")
            ->query()
            ->read();

        if($rs){
            $result = (int)$rs['subject_id'];
            unset($rs);
        }
        return $result;
    }


    /*
     *根据考试组id,班级id统计考试学生
     */
    public function getStudentNumberByExamGroupId($EIds,$classIds){
        if(!$EIds || !$classIds){
            return null;
        }
        $sql = "select exam_id from exam where exam_group_id in(".implode(',',$EIds).") and class_id in(".implode(',',$classIds)."); ";
        $ExamIds	=	$this->sConn->createCommand($sql)->queryAll();

        $examIdArr=array();
        $paperIdArr=array();
        $result=0;
        if($ExamIds){
            foreach ($ExamIds as $val){
                $examIdArr[]=$val['exam_id'];
            }
            $sql_p = "select paper_id from paper where exam_id in(".implode(',',$examIdArr).");";
            $paperIds	=	$this->sConn->createCommand($sql_p)->queryAll();
            if($paperIds){
                foreach ($paperIds as $v){
                    $paperIdArr[]=$v['paper_id'];
                }

                $sql_count="select DISTINCT(scoring) from `student_paper_relation` where paper_id in(".implode(',',$paperIdArr).") and is_complete=1 and is_del=0 and is_feedback=1 order by scoring desc ;";
                
                $allCoring=$this->sConn->createCommand($sql_p)->queryAll();

                if($allCoring){
                    $result=count($allCoring);
                }

            }
        }

        return $result;
    }

    //读取7天内考试
    public function getExamIdByTime(){
        $result=array();
        $examIds=array();
        $subjectIdArr=array();
        $examGroupIdArr=array();
        $examNameArr=array();
        $time=time()-86400*7;
        $sql="select exam_id,subject_id,exam_group_id,`name`,complete_time from exam where complete_time>'{$time}' and status=1";
        $data	=	$this->sConn->createCommand($sql)->queryAll();
        if($data){
            foreach ($data as $datum){
                $examIds[]=$datum['exam_id'];
                $subjectIdArr[(string)$datum['exam_id']]=(int)$datum['subject_id'];
                $examGroupIdArr[(string)$datum['exam_id']]=$datum['exam_group_id'];
                $examNameArr[(string)$datum['exam_id']]=$datum['name'];
                $completeTimeArr[(string)$datum['exam_id']]=$datum['complete_time'];
            }
            $sql_wb="select spr.class_id,c.class_name,c.grade,spr.exam_id from student_paper_relation spr
join class c on c.class_id=spr.class_id
where `is_wrongbook_download`=1 and spr.exam_id in(".implode(',',$examIds).") GROUP BY class_id,exam_id";
            $wrongBook=$this->sConn->createCommand($sql_wb)->queryAll();
            if($wrongBook){
                foreach ($wrongBook as $val){

                    $result[]=array(
                        'id'=>$examGroupIdArr[$val['exam_id']],
                        'subject'=>$subjectIdArr[$val['exam_id']],
                        'productType'=>7,
                        'classId'=>$val['class_id'],
                        'grade'=>$val['grade'],
                        'name'=>$examNameArr[$val['exam_id']],
                        'className'=>$val['class_name'],
                        'productName'=>'五级钻石纠错本 ',
                        'time'=>$completeTimeArr[$val['exam_id']]
                    );
                }
            }

            $sql_isp="select spr.class_id,c.class_name,c.grade,spr.exam_id from student_paper_relation spr
join class c on c.class_id=spr.class_id
where `is_two_isp_download`=1 and spr.exam_id in(".implode(',',$examIds).") GROUP BY class_id,exam_id";
            $twoIsp=$this->sConn->createCommand($sql_isp)->queryAll();
            if($twoIsp){
                foreach ($twoIsp as $val){
                    $result[]=array(
                        'id'=>$examGroupIdArr[$val['exam_id']],
                        'subject'=>$subjectIdArr[$val['exam_id']],
                        'productType'=>8,
                        'classId'=>$val['class_id'],
                        'grade'=>$val['grade'],
                        'name'=>$examNameArr[$val['exam_id']],
                        'className'=>$val['class_name'],
                        'productName'=>'二步皇冠个性化学习宝 ',
                        'time'=>$completeTimeArr[$val['exam_id']]
                    );
                }
            }
            $sql_thIsp="select spr.class_id,c.class_name,c.grade,spr.exam_id from student_paper_relation spr
join class c on c.class_id=spr.class_id
where `is_three_isp_download`=1 and spr.exam_id in(".implode(',',$examIds).") GROUP BY class_id,exam_id";
            $threeIsp=$this->sConn->createCommand($sql_thIsp)->queryAll();
            if($threeIsp){
                foreach ($threeIsp as $val){
                    $result[]=array(
                        'id'=>$examGroupIdArr[$val['exam_id']],
                        'subject'=>$subjectIdArr[$val['exam_id']],
                        'productType'=>9,
                        'classId'=>$val['class_id'],
                        'grade'=>$val['grade'],
                        'name'=>$examNameArr[$val['exam_id']],
                        'className'=>$val['class_name'],
                        'productName'=>'三步金冠个性化学习宝 ',
                        'time'=>$completeTimeArr[$val['exam_id']]
                    );
                }
            }

        }
        return $result;
    }
}