<?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.sstudy.mapper.question.QuestionStudentMapper">
    <!--获取学生错题信息-->
    <select id="getQuestionStudent" resultType="com.xhkjedu.sstudy.model.question.TQuestionStudent">
        SELECT * FROM t_question_student
        WHERE questionid = #{qs.questionid} AND studentid = #{qs.studentid} and qctype=#{qs.qctype}
        ORDER BY qsid DESC LIMIT 1
    </select>
    <!--列表-->
    <select id="findAll" resultType="com.xhkjedu.sstudy.vo.question.QuestionStudentVo">
        select qs.qsid,qs.questionid,qs.qscore,qs.stuscore,qs.scorerate,qs.qctype,qs.qorder,qs.qtypeid,qs.paperid
        from t_question_student qs left join t_paper p on qs.paperid=p.paperid
        where qs.studentid = #{qs.studentid} and delstate=0 and qs.subjectid=#{qs.subjectid}
        <if test="qs.starttime!=null and qs.starttime!=0">
            and UNIX_TIMESTAMP(FROM_UNIXTIME(qs.handletime,'%Y%m%d'))>=#{qs.starttime}
        </if>
        <if test="qs.stoptime!=null and qs.stoptime!=0">
            and UNIX_TIMESTAMP(FROM_UNIXTIME(qs.handletime,'%Y%m%d'))&lt;=#{qs.stoptime}
        </if>
        <if test="qs.lsbid!=null and qs.lsbid!='0'.toString() and qs.lsbid!=''">
            and p.lsbid=#{qs.lsbid}
        </if>
        <if test="qs.directorid!=null and qs.directorid!='0'.toString() and qs.directorid!=''">
            and p.directorid=#{qs.directorid}
        </if>
        <if test="qs.qtypeid!=null and qs.qtypeid!='0'.toString() and qs.qtypeid!=''">
            and qs.qtypeid=#{qs.qtypeid}
        </if>
        order by qs.handletime desc,qs.qorder desc limit #{qs.page},#{qs.pageSize}
    </select>
    <!--删除-->
    <update id="deleteById">
        update t_question_student set errornum=0,delstate=1 where qsid=#{qsid}
    </update>
    <!--更新错误次数-->
    <update id="updateErrorNum">
        update t_question_student set errornum=errornum+1,delstate=0,handletime=UNIX_TIMESTAMP(NOW()) where qsid=#{qsid}
    </update>

    <!--//////////////////////////////////////////////学生WEB端//////////////////////////////////////////////-->
    <select id="listForWeb" resultType="com.xhkjedu.sstudy.vo.question.QuestionStudentVo">
        select qs.qsid,qs.questionid,qs.qscore,qs.stuscore,qs.scorerate,qs.qctype,qs.qorder
        ,qs.qtypeid,concat(p.papername,'-第',qs.qorder,'题')qstem
        from t_question_student qs left join t_paper p on qs.paperid=p.paperid
        where qs.studentid=#{question.studentid} and qs.delstate=0
        <if test="question.starttime!=null and question.starttime!=0">
            and UNIX_TIMESTAMP(FROM_UNIXTIME(qs.handletime,'%Y%m%d'))>=#{question.starttime}
        </if>
        <if test="question.stoptime!=null and question.stoptime!=0">
            and UNIX_TIMESTAMP(FROM_UNIXTIME(qs.handletime,'%Y%m%d'))&lt;=#{question.stoptime}
        </if>
        <if test="question.subjectid!=null and question.subjectid!='0'.toString() and question.subjectid!=''">
            and qs.subjectid=#{question.subjectid}
        </if>
        <if test="question.lsbid!=null and question.lsbid!='0'.toString() and question.lsbid!=''">
            and p.lsbid=#{question.lsbid}
        </if>
        <if test="question.directorid!=null and question.directorid!='0'.toString() and question.directorid!=''">
            and p.directorid=#{question.directorid}
        </if>
        <if test="question.qtypeid!=null and question.qtypeid!='0'.toString() and question.qtypeid!=''">
            and qs.qtypeid=#{question.qtypeid}
        </if>
        group by qs.questionid,qs.qctype order by qs.handletime desc,qs.qorder desc
    </select>
    <!--更新错题信息-->
    <update id="updateQuestion">
        update t_question_student set qtypeid=#{qs.qtypeid},qorder=#{qs.qorder},useranswer=#{qs.useranswer}
        ,qscore=#{qs.qscore},stuscore=#{qs.stuscore},scorerate=#{qs.scorerate},paperid=#{qs.paperid}
        ,errornum=errornum+1,delstate=0,handletime=#{qs.handletime},useranswertext=#{qs.useranswertext},useranswertxt=#{qs.useranswertxt},device=#{qs.device}
        where qsid=#{qs.qsid}
    </update>
    <!--结构试题详情-->
    <resultMap id="questionResult" type="com.xhkjedu.sstudy.vo.question.QuestionStudentVo">
        <result property="qsid" column="qsid"></result>
        <result property="qstem" column="qstem"></result>
        <result property="qoption" column="qoption"></result>
        <result property="qanswer" column="qanswer"></result>
        <result property="qanalyze" column="qanalyze"></result>
        <result property="ctype" column="ctype"></result>
        <result property="complexity" column="complexity"></result>
        <result property="qscore" column="qscore"></result>
        <result property="stuscore" column="stuscore"></result>
        <result property="useranswer" column="useranswer"></result>
        <result property="converted" column="converted"></result>
        <result property="useranswertext" column="useranswertext"></result>
        <result property="useranswertxt" column="useranswertxt"></result>
        <result property="device" column="device"></result>
        <collection property="answers" ofType="com.xhkjedu.sstudy.vo.question.StudentAnswerVo" javaType="java.util.List"
                    select="listAnswer" column="{questionid=questionid,paperid=paperid,studentid=studentid}">
        </collection>
    </resultMap>
    <select id="listAnswer" resultType="com.xhkjedu.sstudy.vo.question.StudentAnswerVo">
        select useranswer,useranswertext,useranswertxt,stuscore,answertime from t_paper_student_question
        where paperid!=#{paperid} and questionid=#{questionid} and studentid=#{studentid} and checked=1 and qscore!=stuscore
        order by answertime desc
    </select>
    <select id="detail" resultMap="questionResult">
        select q.qstem,q.qoption,q.qanswer,q.qanalyze,q.ctype,q.complexity,q.questionid
        ,qs.qsid,qs.qscore,qs.stuscore,qs.useranswer,qs.converted,qs.useranswertext,qs.useranswertxt,qs.device,qs.paperid,qs.studentid
        ,(select group_concat(p.pointname separator '  ') from t_question_point qp
        left join t_point p on qp.pointid = p.pointid where qp.questionid=qs.questionid)pointname
        from t_question_student qs left join t_question q on qs.questionid=q.questionid
        where qs.questionid=#{questionid} and qs.studentid=#{studentid} and qs.qctype=#{qctype} limit 1
    </select>
    <!--附件试题详情-->
    <select id="sdetail" resultType="com.xhkjedu.sstudy.vo.question.QuestionStudentVo">
        select (select concat(p.papername,'-第',qs.qorder,'题') from t_paper p where p.paperid=qs.paperid)qstem
        ,ps.psanswer qanswer,ps.ctype,qs.qsid,qs.qscore,qs.stuscore,qs.useranswer,qs.converted,qs.useranswertext,qs.useranswertxt,qs.device,qs.paperid
        from t_question_student qs left join t_paper_scantron ps on qs.questionid=ps.pscaid
        where qs.questionid=#{questionid} and qs.studentid=#{studentid} and qs.qctype=2 limit 1
    </select>

    <!--已批阅未得满分的试题-->
    <select id="listQuestions" resultType="com.xhkjedu.sstudy.model.question.TQuestionStudent">
        select #{qctype} qctype,q.studentid,q.qscore,q.stuscore,q.paperid,q.useranswer,q.converted,q.useranswertext,q.useranswertxt,q.device
        ,(select subjectid from t_paper p where p.paperid=#{paperid})subjectid
        <if test="qctype==1 or qctype==3">
            ,q.questionid,tq.qtypeid from t_paper_student_question q
            left join t_question tq on q.questionid=tq.questionid
        </if>
        <if test="qctype==2">
            ,q.pscaid questionid,ps.qtypeid,ps.psorder qorder from t_paper_student_scantron q
            left join t_paper_scantron ps on q.pscaid=ps.pscaid
        </if>
        where q.paperid=#{paperid} and q.classid=#{classid} and q.checked=1 and q.stuscore!=q.qscore
    </select>
    <!--题型列表-->
    <select id="listQtype" resultType="com.xhkjedu.sstudy.vo.subjectbook.SubjectQuestiontypeVo">
        select st.qtypeid,st.qtypename,st.qtypeorder,st.ctype
        from t_question_student qs left join t_subject_questiontype st on qs.qtypeid=st.qtypeid
        where qs.studentid=#{qs.studentid} and st.subjectid=#{qs.subjectid}
        group by st.qtypeid order by st.qtypeorder
    </select>
    <!--根据作业id获取作业名称-->
    <select id="getPaperNameById" resultType="java.util.Map">
        select paperid,papername from t_paper where
        <foreach collection="paperids" item="paperid" index="index" separator="or">
            paperid=#{paperid}
        </foreach>
    </select>
</mapper>
