<?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.paperstudent.EScanbatchMapper">

    <!--获取指定设备试卷批次数量-->
    <select id="getBatchNameLikeNum" resultType="java.lang.Integer" parameterType="java.lang.String">
        select count(*) from e_scanbatch where batchname like concat(#{batchname,jdbcType=VARCHAR},'%')
    </select>

    <!--更改扫描数量-->
    <update id="updateScannum">
        update e_scanbatch set scannum=(select count(*) from e_paper_student ps where ps.sstate=2 and ps.batchid=#{batchid})
        where batchid=#{batchid}
    </update>
    <!--更改缺考数量-->
    <update id="updateQknum">
        update e_scanbatch set qknum=(select count(*) from e_paper_student ps where ps.sstate=3 and ps.batchid=#{batchid})
        where batchid=#{batchid}
    </update>
    <!--更改违规数量-->
    <update id="updateWgnum">
        update e_scanbatch set wgnum=(select count(*) from e_paper_student ps where ps.hasbad=1 and ps.batchid=#{batchid})
        where batchid=#{batchid}
    </update>
    <!--更改出错数量-->
    <update id="updateErrornum">
        update e_scanbatch set errornum=(select count(*) from e_scanerror e where e.batchid=#{batchid}) where batchid=#{batchid}
    </update>

    <!--扫描答题卡列表-->
    <select id="listScanSubject" resultType="java.util.Map">
        select a.*,ifnull(truncate(a.smnum*100/a.examnum,2),0)smrate
             ,ifnull(truncate(count(case when epsq.checked=1 then epsq.epsqid else null end)*100/count(epsq.epsqid),2),0)yjrate
        from(select es.esid,es.esstate,es.subjectname
                  ,count(eps.epid)examnum,ep.epid
                  ,count(case when eps.sstate!=0 then eps.epsid else null end)smnum
                  ,count(case when eps.sstate=3 then eps.epsid else null end)qknum
             from e_subject es left join e_paper ep on es.esid = ep.esid
                               left join e_paper_student eps on ep.epid = eps.epid
             where es.examid=#{examid}
             group by es.esid)a left join e_paper_qtype_question epqq on a.epid = epqq.epid and epqq.ctype in(3,13,14,15)
                                left join e_paper_student_question epsq on epsq.eptqid = epqq.eptqid and epsq.answered=1
        group by a.epid
    </select>

    <!--试卷扫描批次信息-->
    <select id="listScanBatchPapers" resultType="java.util.Map">
        select * from e_scanbatch where epid=#{epid} order by createtime desc
    </select>

    <!--获取答题卡扫描结果-->
    <select id="getScanResult" resultType="com.xhkjedu.sexam.model.paperstudent.EScanbatch">
        select sum(batchnum)batchnum,sum(scannum)scannum,su(errornum)errornum,sum(es.qknum)qknum,examid
        from e_scanbatch where epid=#{epid}
    </select>

</mapper>
