<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xhkjedu.sexam.mapper.exam.ESubjectInvigilateMapper">
    <!--删除考试科目监考教师-->
    <delete id="delByEsId">
        delete from e_subject_invigilate where esid=#{esid}
    </delete>
    <!--监考设置列表-->
    <select id="listSubject" resultType="java.util.Map">
        select s.*,ifnull(ep.pnum,0)pnum,ifnull(ep.pscore,0)pscore,ep.correcttype
        from (select s.esid,s.subjectid,s.sdate,s.begintime,s.endtime,s.esstate,s.subjectname
        ,group_concat(u.username separator '、')majorname
        ,count(DISTINCT ei.classid) einum
        from e_subject s left join e_subject_invigilate ei on ei.esid=s.esid
        left join t_user u on u.userid=ei.teacherid and ei.majored=1
        where s.examid=#{examid} group by s.esid)s left join e_paper ep on s.esid = ep.esid
    </select>
    <!--考试监考人-->
    <select id="listByEsId" resultType="java.util.Map">
        select ei.classid,ei.majored,ei.teacherid,u.username
        from e_subject_invigilate ei left join t_user u on ei.teacherid = u.userid
        where ei.esid=#{esid}
    </select>
    <!--教师监考-监控列表-->
    <select id="listJkSubject" resultType="java.util.Map">
        select es.esid,es.sdate,es.begintime,es.endtime,es.esstate,es.subjectname
        ,eb.examname,eb.gradeid,eb.examtype,sum(ec.classnum)examnum
        ,count(case when eps.sstate=0 then eps.epsid else null end) wjcnum
        ,count(case when eps.sstate=1 then eps.epsid else null end)jcnum
        ,truncate(count(case when eps.sstate=1 then eps.epsid else null end)*100/sum(ec.classnum),2) jcrate
        ,count(case when eps.sstate=2 then eps.epsid else null end)tjnum
        ,count(case when eps.sstate=3 then eps.epsid else null end)qknum
        from e_subject es left join e_base eb on eb.examid = es.examid
        left join e_class ec on eb.examid = ec.examid
        left join e_paper_student eps on es.esid = eps.esid
        <where>
        <if test="teacherid!=null and teacherid!=0">
            and es.teacherid=#{teacherid}
        </if>
        <if test="examid!=null and examid!=0">
            and es.examid=#{examid}
        </if>
        <if test="esstate==1">
            and concat(es.sdate,' ',es.endtime)>now()
        </if>
        <if test="esstate==2">
            and concat(es.sdate,' ',es.endtime)&lt;=now()
        </if>
        </where>
        group by es.esid
        <if test="esstate==1">
            order by abs(UNIX_TIMESTAMP(concat(es.sdate,' ',es.begintime))-UNIX_TIMESTAMP())
        </if>
        <if test="esstate==2">
            order by es.sdate desc,es.endtime desc
        </if>
    </select>

</mapper>
