<?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.EBaseMapper">
    <!--考试删除-->
    <update id="deleteById">
        update e_base set deleted=10 where examid=#{examid}
    </update>
    <!--删除关联的班级和科目-->
    <delete id="deleteClassAndSubjectById">
        delete s,c,cs from e_subject s left join e_class c on s.examid=c.examid
        left join e_class_student cs on cs.examid=c.examid
        where s.examid=#{examid}
    </delete>
    <!--考试列表-->
    <select id="listAll" resultType="java.util.Map">
        select b.examid,b.examname,b.examdate,b.gradeid,b.examtype,b.exammode,b.monitored
        ,b.examstate,group_concat(distinct es.subjectname order by es.esid separator '|')subjectname
        ,count(distinct case when es.begintime is null or es.begintime='' then es.esid else null end)esnum
        ,min(es.esstate)esstate,min(ifnull(ep.correcttype,0))correcttype
        ,(case when b.exammode=1 then (count(distinct es.esid)-count(distinct ei.esid))
        else (select sum(ec.classnum)-count(ps.epsid) from e_class ec
        left join e_paper_student ps on ec.examid=ps.examid and ps.sstate in(0,1)
        where ec.examid=b.examid) end)papernum
        from e_base b left join e_subject es on es.examid=b.examid
        left join e_paper ep on ep.esid = es.esid
        left join e_subject_invigilate ei on ei.esid = es.esid
        where b.schoolid=#{base.schoolid} and b.deleted=1
        <if test="base.gradeid!=null and base.gradeid!=0">
            and b.gradeid=#{base.gradeid}
        </if>
        <if test="base.examstate!=null and base.examstate!=-1">
            and b.examstate=#{base.examstate}
        </if>
        <if test="base.begindate!=null and base.begindate!=''">
            and b.examdate between #{base.begindate} and #{base.enddate}
        </if>
        group by b.examid order by b.examid desc
    </select>
    <!--修改考试状态-->
    <update id="updateExamState">
        update e_base set examstate=#{examstate} where examid=#{examid}
    </update>
    <!--发布考试-->
    <update id="postExam">
        update e_base set examcomm=#{examcomm},examstate=1 where examid=#{examid}
    </update>
    <!--考试基本信息-->
    <select id="findById" resultType="java.util.Map">
        select examid,examname,gradeid,examdate,examtype,exammode,monitored,examstate
        from e_base where examid=#{examid}
    </select>
    <!--考试基本信息(带考试人数)-->
    <select id="findById2" resultType="java.util.Map">
        select b.examid,b.examname,b.examdate,b.examtype,b.gradeid,b.exammode,b.monitored,b.examstate,b.examcomm
        ,(select sum(c.classnum) from e_class c where c.examid=b.examid)examnum
        from e_base b where b.examid=#{examid}
    </select>
    <!--根据考试id获取名称-->
    <select id="getNameByExamId" resultType="java.lang.String">
        select examname from e_base where examid=#{examid}
    </select>
    <!--根据考试id获取考试状态-->
    <select id="getExamStateById" resultType="com.xhkjedu.sexam.vo.exam.EBaseVo">
        select eb.examid,eb.examname,eb.gradeid,eb.examstate,eb.schoolid,eb.exammode
        ,count(case when es.esstate!=1 then es.esid else null end)wwcnum
        ,(case when eb.exammode=1 then min(ep.answered) else 1 end)answered
        ,min(ifnull(UNIX_TIMESTAMP(concat(es.sdate,' ',es.begintime)),0))begintime
        from e_base eb left join e_subject es on eb.examid = es.examid
        left join e_paper ep on es.esid = ep.esid
        where eb.examid=#{examid}
    </select>
    <!--考试设置状态-->
    <select id="getSetState" resultType="java.util.Map">
        select count(distinct case when es.begintime is null or es.begintime='' then es.esid else null end)esnum
        ,min(es.esstate)esstate,min(ifnull(ep.correcttype,0))correcttype
        ,(case when b.exammode=1 then (count(distinct es.esid)-count(distinct ei.esid))
        else (select sum(ec.classnum)-count(ps.epsid) from e_class ec
        left join e_paper_student ps on ec.examid=ps.examid and ps.sstate in(0,1)
        where ec.examid=b.examid) end)papernum
        from e_base b left join e_subject es on es.examid=b.examid
        left join e_paper ep on ep.esid = es.esid
        left join e_subject_invigilate ei on ei.esid = es.esid
        where b.examid=#{examid}
    </select>
</mapper>
