<?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.paperstudent.PaperStudentQuestionMapper">
    <!--获取学生作业详情-->
    <select id="getPaperQuestion" resultType="com.xhkjedu.sstudy.vo.paperstudent.PaperStudentQuestionVo">
        SELECT psq.psqid,psq.paperid,psq.studentid,psq.answered,psq.answertime,psq.costtime,psq.device,psq.checkcomm
        ,psq.useranswer,psq.converted,psq.useranswertext,psq.useranswertxt,psq.checked,psq.qscore,psq.stuscore,psq.qorder,u.username studentname,psq.psid
        ,q.questionid,q.qstem,q.qoption,q.qanswer,q.qanalyze,q.ctype,q.complexity,q.qtypename
        ,(CASE WHEN psq.checked=1 AND psq.stuscore=psq.qscore THEN 1 ELSE 2 END)corrected
        ,(CASE WHEN q.ctype IN(7,8) THEN JSON_LENGTH(q.qoption) ELSE 0 END)qoptioncount
        ,(select group_concat(p.pointname separator '  ') from t_question_point qp
        left join t_point p on qp.pointid=p.pointid where qp.questionid=q.questionid)pointname
        FROM t_paper_student_question psq LEFT JOIN t_question q ON psq.questionid=q.questionid
        LEFT JOIN t_user u ON psq.studentid=u.userid
        WHERE psq.psid=#{psid} ORDER BY psq.qorder
    </select>
    <!--获取学生待批改试题列表-->
    <select id="listCorrectQuestion" resultType="com.xhkjedu.sstudy.vo.paperstudent.PaperStudentQuestionVo">
        SELECT psq.psqid,psq.paperid,psq.studentid,psq.answered,psq.answertime,psq.costtime,psq.device,psq.checkcomm
        ,psq.useranswer,psq.converted,psq.useranswertext,psq.useranswertxt,psq.checked,psq.qscore,psq.stuscore,psq.qorder,u.username studentname,psq.psid
        ,q.questionid,q.qstem,q.qoption,q.qanswer,q.qanalyze,q.ctype,q.complexity,q.qtypeid,q.qtypename
        ,(CASE WHEN psq.checked=1 AND psq.stuscore=psq.qscore THEN 1 ELSE 2 END)corrected
        ,(CASE WHEN q.ctype IN(7,8) THEN JSON_LENGTH(q.qoption) ELSE 0 END)qoptioncount
        FROM t_paper_student_question psq LEFT JOIN t_question q ON psq.questionid=q.questionid
        LEFT JOIN t_user u ON psq.studentid=u.userid
        WHERE psq.psid=#{psid} and q.ctype=3
        ORDER BY psq.qorder
    </select>
    <!--作业关联试题列表-->
    <select id="listPaperQuestion" resultType="com.xhkjedu.sstudy.model.paperstudent.TPaperStudentQuestion">
        SELECT ps.paperid,ps.classid,ps.studentid,ptq.questionid,ptq.ptqscore AS qscore,ptq.ptqorder qorder,ps.schoolid,ps.psid
        FROM t_paper_student ps LEFT JOIN t_paper_qtype_question ptq ON ps.paperid=ptq.paperid WHERE ps.psid=#{psid}
    </select>
    <!--批量保存-->
    <insert id="batchSave" parameterType="java.util.List">
        INSERT INTO t_paper_student_question (paperid,classid,studentid,questionid,qscore,qorder,schoolid,psid)
        VALUES
        <foreach collection ="list" item="r" index= "index" separator =",">
            (#{r.paperid},#{r.classid},#{r.studentid},#{r.questionid},#{r.qscore},#{r.qorder},#{r.schoolid},#{r.psid})
        </foreach>
    </insert>
    <!--获取学生未提交试题集合-->
    <select id="listPSQByNotDo" resultType="com.xhkjedu.sstudy.model.paperstudent.TPaperStudentQuestion">
        SELECT psq.questionid,psq.qscore,q.qtypeid
        FROM t_paper_student_question psq left join t_question q on psq.questionid = q.questionid
        WHERE psq.psid=#{psid} AND psq.checked=0 AND psq.answered=0
    </select>
    <!--自动批改学生未做试题-->
    <update id="updatePSQByNotDo">
        UPDATE t_paper_student_question SET costtime=0,checked=1,stuscore=0,checkid=0,checktime=UNIX_TIMESTAMP(NOW())
        WHERE psid=#{psid} AND answered=0
    </update>
    <!--获取该作业该学生未批改作业数量-->
    <select id="getNotCorrect" resultType="java.lang.Integer">
        SELECT count(*) FROM t_paper_student_question
        WHERE psid=#{psid} AND checked!=1
    </select>

    <!--计算所有试题用时-->
    <select id="getSumQuesitonsCosttime" resultType="java.lang.Integer">
         SELECT SUM(costtime) FROM t_paper_student_question WHERE psid=#{psid}
    </select>

    <!--修改学生答案图片，添加教师批阅-->
    <update id="updateUseranswer">
        UPDATE t_paper_student_question SET useranswer=#{useranswer} WHERE psqid=#{psqid}
    </update>

    <!--题库作业 按题批阅列表-->
    <resultMap id="questionResult" type="java.util.Map">
        <result property="qscore" column="qscore"></result>
        <result property="qorder" column="qorder"></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>
        <collection property="students" ofType="java.util.Map" javaType="java.util.List">
            <result property="stuscore" column="stuscore"></result>
            <result property="answered" column="answered"></result>
            <result property="checked" column="checked"></result>
            <result property="checkcomm" column="checkcomm"></result>
            <result property="psid" column="psid"></result>
            <result property="psqid" column="psqid"></result>
            <result property="device" column="device"></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="studentname" column="username"></result>
            <result property="psstate" column="psstate"></result>
        </collection>
    </resultMap>
    <select id="qcorrectList" resultMap="questionResult">
        select psq.qscore,psq.qorder,q.qstem,q.qoption,q.qanswer,q.qanalyze
        ,psq.stuscore,psq.answered,psq.checked,psq.checkcomm,psq.psid,psq.psqid
        ,psq.device,psq.useranswer,psq.converted,psq.useranswertext,psq.useranswertxt,u.username,ps.psstate
        from t_paper_student_question psq left join t_question q on psq.questionid = q.questionid
        left join t_user u on psq.studentid = u.userid
        left join t_paper_student ps on psq.psid=ps.psid
        where psq.paperid=#{paperid} and psq.classid=#{classid} and q.ctype=3
        order by psq.checked,psq.psqid
    </select>
    <!--获取学生作业未批改试题数量-->
    <select id="getNoCheckNumByPsId" resultType="integer">
        select count(*) from t_paper_student_question where psid=#{psid} and answered=1 and checked!=1
    </select>
    <!--获取未合并的学生答案-->
    <select id="listNoMergeUserAnswer" resultType="com.xhkjedu.sstudy.model.paperstudent.TPaperStudentQuestion">
        select psqid,useranswer from t_paper_student_question where converted=0
        <if test="psid!=0">and psid=#{psid}</if>
    </select>
</mapper>
