<?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.PaperGroupMapper">
    <!--详情-->
    <select id="selectByPaperAndClassId" resultType="com.xhkjedu.sstudy.model.paperstudent.TPaperGroup">
        select pg.paperid,pg.classid,pg.stoptime,pg.showtype
        from t_paper_group pg
        where pg.paperid=#{paperid} and pg.classid=#{classid} limit 1
    </select>
    <resultMap id="groupClass" type="com.xhkjedu.sstudy.vo.paperstudent.PaperGroupVo">
        <result property="classid" column="classid"></result>
        <result property="classname" column="classname"></result>
        <result property="gradeid" column="gradeid"></result>
        <result property="starttime" column="starttime"></result>
        <result property="stoptime" column="stoptime"></result>
        <collection property="students" ofType="com.xhkjedu.sstudy.vo.gradeclass.CStudentVo">
            <result property="studentid" column="studentid"></result>
            <result property="studentname" column="studentname"></result>
        </collection>
    </resultMap>
    <!--获取参与作业学生列表-->
    <select id="listPaperStudent" resultMap="groupClass">
        select ps.classid,c.classname,c.gradeid,ps.studentid,u.username studentname,pg.starttime,pg.stoptime
        from t_paper_group pg left join t_paper_student ps on ps.paperid = pg.paperid and pg.classid=ps.classid
        left join t_user u on ps.studentid=u.userid
        left join t_class c on ps.classid=c.classid
        where pg.paperid=#{paperid} order by c.gradeid,c.classtype,c.classorder,convert(u.username using gbk)
    </select>
    <!--批阅完成-->
    <update id="updateChecked">
        update t_paper_group set checked=1 where paperid=#{paperid} and classid=#{classid}
    </update>
    <!--根据学生作业id获取作业是否完成批阅-->
    <select id="getCheckedByPsId" resultType="java.lang.Integer">
        select pg.checked from t_paper_student ps
        left join t_paper_group pg on ps.paperid=pg.paperid and ps.classid=pg.classid
        where ps.psid=#{psid}
    </select>

    <!--作业班级下未批阅学生作业数量-->
    <select id="getWpyNum" resultType="java.lang.Integer">
        select count(*) from t_paper_student where paperid=#{pg.paperid} and classid=#{pg.classid} and pschecked!=1
    </select>
    <!--获取班级作业是否结束批阅-->
    <select id="getPaperClassChecked" resultType="java.lang.Integer">
        select pg.checked from t_paper_group pg where pg.paperid=#{paperid} and pg.classid=#{classid}
    </select>
    <!--教师权限分组-->
    <select id="listGroupByTeacherId" resultType="java.util.Map">
        select c.classid,c.classname,c.gradeid,g.groupid,g.groupname,g.groupnum,g.grouptype,gs.studentid
        from t_group g left join t_class_teacher ct on ct.classid = g.classid
        left join t_class c on ct.classid = c.classid
        left join t_school s on c.schoolid=s.schoolid
        left join t_group_student gs on gs.groupid=g.groupid
        where ct.teacherid = #{teacherid} and c.year = s.year and c.classstate = 1 and c.classnum!=0 and g.groupnum!=0
        and c.classtype<![CDATA[ < ]]>5 and (g.createid = #{teacherid} or g.grouptype = 1)
        order by c.classtype, c.classorder, g.grouptype asc, g.groupid desc
    </select>
    <!--作业对象集合-->
    <select id="listUserByIds" resultType="java.util.Map">
        select userid,username,headpic from t_user where userid in
        <foreach collection="userids" item="userid" index="index" open="(" separator="," close=")">
            ${userid}
        </foreach>
    </select>
    <!--作业试题列表-->
    <resultMap id="questionResult" type="com.xhkjedu.sstudy.vo.question.QuestionVo">
        <result property="questionid" column="questionid"></result>
        <result property="score" column="score"></result>
        <result property="ptqorder" column="ptqorder"></result>
        <result property="complexity" column="complexity"></result>
        <result property="ctype" column="ctype"></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="createtime" column="createtime"></result>
        <result property="count" column="count"></result>
        <result property="qtypeid" column="qtypeid"></result>
        <result property="qtypename" column="qtypename"></result>
        <collection property="points" ofType="com.xhkjedu.sstudy.vo.subjectbook.PointVo">
            <result property="pointid" column="pointid"></result>
            <result property="pointname" column="pointname"></result>
        </collection>
    </resultMap>
    <select id="listPaperQuestions" resultMap="questionResult">
        select q.qtypeid,q.qtypename,ptq.questionid,ptq.ptqscore score,ptq.ptqorder
        ,q.complexity,q.ctype,q.qstem,q.qoption,q.qanswer,q.qanalyze,qp.pointid,p.pointname,q.createtime,q.count
        from t_paper_qtype_question ptq left join t_question q on ptq.questionid = q.questionid
        left join t_question_point qp on q.questionid = qp.questionid
        left join t_point p on qp.pointid = p.pointid
        where ptq.paperid=#{paperid} order by ptq.ptqorder
    </select>
    <!--学生满分试题-->
    <select id="listStuQuestion" resultType="java.util.Map">
        select q.studentid,q.questionid from(select max(psqid)psqid from t_paper_student_question
        where qscore=stuscore and questionid in
        <foreach collection="questionids" item="questionid" index="index" open="(" separator="," close=")">
            #{questionid}
        </foreach>
        and studentid in
        <foreach collection="studentids" item="studentid" index="index" open="(" separator="," close=")">
            ${studentid}
        </foreach>
        group by studentid,questionid)a left join t_paper_student_question q on q.psqid=a.psqid where q.qscore=q.stuscore
    </select>
</mapper>
