<?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.paper.PaperTeaAppMapper">
    <resultMap id="paperResult" type="com.xhkjedu.sstudy.vo.paper.PaperTeacherAppVo">
        <result property="paperid" column="paperid"></result>
        <result property="papername" column="papername"></result>
        <result property="papernum" column="papernum"></result>
        <result property="paperscore" column="paperscore"></result>
        <result property="papertype" column="papertype"></result>
        <result property="paperclass" column="paperclass"></result>
        <result property="starttime" column="starttime"></result>
        <result property="stoptime" column="stoptime"></result>
        <collection property="students" ofType="com.xhkjedu.sstudy.vo.paperstudent.StudentVo"
                    select="listPaperStu" column="{paperid=paperid,createid=createid,checkid=checkid}">
        </collection>
    </resultMap>
    <select id="listPaperStu" resultType="com.xhkjedu.sstudy.vo.paperstudent.StudentVo">
        select (case when ps.psstate=0 then 0 when ps.pschecked=1 then 2 else 1 end)pschecked
        from t_paper_student ps where ps.paperid=#{paperid}
        <if test="checkid!=createid">
            and ps.checkid=#{checkid}
        </if>
    </select>
    <select id="listTeacherApp" resultMap="paperResult">
        select p.paperid,p.papername,p.papernum,p.papertype,p.paperclass,p.paperstate,p.checktype,p.createid,#{paper.teacherid} checkid
        ,min(pg.starttime)starttime,min(pg.stoptime)stoptime,pg.checked,p.subjectid,p.paperscore
        from t_paper p left join t_paper_group pg on p.paperid=pg.paperid
        where (p.createid=#{paper.teacherid} or pg.checkid=#{paper.teacherid})
        <if test="paper.lsbid!=null and paper.lsbid!='0'.toString() and paper.lsbid!=''">
            and p.lsbid=#{paper.lsbid}
        </if>
        <if test="paper.directorid!=null and paper.directorid!='0'.toString() and paper.directorid!=''">
            and p.directorid=#{paper.directorid}
        </if>
        group by p.paperid
        having min(pg.checked)=#{paper.psstate}
        order by min(pg.starttime) desc
    </select>
    <!--未布置作业列表-->
    <select id="listWbz" resultType="com.xhkjedu.sstudy.vo.paper.PaperTeacherAppVo">
        select p.paperid,p.papername,p.papernum,p.papertype,p.paperclass,p.paperstate,p.checktype,p.createid
        ,p.subjectid,p.paperscore
        from t_paper p where p.createid=#{paper.teacherid} and p.paperstate=#{paper.psstate}
        order by p.paperid desc
    </select>
    <!--作业情况-->
    <resultMap id="paperDetailResult" type="com.xhkjedu.sstudy.vo.paper.PClassVo">
        <result property="classid" column="classid"></result>
        <result property="classname" column="classname"></result>
        <result property="gradeid" column="gradeid"></result>
        <result property="checked" column="checked"></result>
        <collection property="students" ofType="com.xhkjedu.sstudy.vo.paperstudent.StudentVo"
                    select="listPaperClassStu" column="{paperid=paperid,classid=classid}">
        </collection>
    </resultMap>
    <select id="listPaperClassStu" resultType="com.xhkjedu.sstudy.vo.paperstudent.StudentVo">
        select (case when ps.psstate=0 then 0 when ps.pschecked=1 then 2 else 1 end)pschecked
        from t_paper_student ps where ps.paperid=#{paperid} and ps.classid=#{classid}
    </select>
    <select id="getPaperDetail" resultMap="paperDetailResult">
        select pg.classid,c.classname,c.gradeid,pg.checked,pg.paperid
        from t_paper_group pg left join t_class c on pg.classid=c.classid
        where pg.paperid=#{paperid}
        <if test="checkid!=null and checkid!=0">
            and pg.checkid=#{checkid}
        </if>
        group by pg.classid
    </select>

    <!--作业报告-->
    <resultMap id="reportResult" type="com.xhkjedu.sstudy.vo.paper.PaperTeacherReportVo">
        <result property="qorder" column="qorder"></result>
        <collection property="students" ofType="com.xhkjedu.sstudy.vo.paperstudent.StudentVo">
            <result property="studentid" column="studentid"></result>
            <result property="pschecked" column="pschecked"></result>
        </collection>
    </resultMap>
    <!--20220421修改，不再判断学生试题是否提交只要作业提交了，就参与统计-->
    <select id="getReport" resultMap="reportResult">
        select q.qorder,q.studentid
        ,(case when q.stuscore=q.qscore then 1 else 0 end)pschecked
        <if test="paper.papertype==1 or paper.papertype==3">
            from t_paper_student_question q
        </if>
        <if test="paper.papertype==2">
            from t_paper_student_scantron q
        </if>
        left join t_paper_student ps on q.paperid=ps.paperid and q.studentid=ps.studentid
        where q.paperid=#{paper.paperid} and q.classid=#{paper.classid} and ps.psstate>0 group by q.qorder,q.studentid
        order by q.qorder
    </select>

    <!--未提交学生列表-->
    <select id="listWtjStudent" resultType="com.xhkjedu.sstudy.vo.paper.PStudentVo">
        select u.username studentname,u.headpic
        from t_paper_student ps left join t_user u on ps.studentid=u.userid
        where ps.paperid=#{paperid} and ps.psstate=0 and ps.classid=#{classid}
        order by convert(u.username using gbk)
    </select>
    <!--已提交学生列表-->
    <select id="listYtjStudent" resultType="com.xhkjedu.sstudy.vo.paper.PStudentVo">
        select u.username studentname,ps.psid,ps.stoptime,ps.costtime,ps.pschecked,#{paper.papertype} papertype
        ,u.headpic,ps.paperscore,ps.userscore
        ,(select count(ps2.psid) from t_paper_student ps2 where ps2.paperid=#{paper.paperid} and ps2.classid=#{paper.classid})classnum
        from t_paper_student ps left join t_user u on ps.studentid=u.userid
        where ps.paperid=#{paper.paperid} and ps.psstate!=0 and ps.classid=#{paper.classid}
        order by ps.pschecked,ps.userscore ${paper.fieldorder},convert(u.username using gbk)
    </select>
    <!--获取学生答题卡-->
    <resultMap id="scantronResult" type="com.xhkjedu.sstudy.vo.paper.PStudentVo">
        <result property="papername" column="papername"></result>
        <result property="papernum" column="papernum"></result>
        <result property="scorerate" column="scorerate"></result>
        <result property="papertype" column="papertype"></result>
        <result property="psid" column="psid"></result>
        <result property="stoptime" column="stoptime"></result>
        <result property="costtime" column="costtime"></result>
        <result property="rank" column="rank"></result>
        <result property="paperscore" column="paperscore"></result>
        <result property="userscore" column="userscore"></result>
        <collection property="questions" ofType="com.xhkjedu.sstudy.vo.paper.PaperTeacherReportVo">
            <result property="qorder" column="qorder"></result>
            <result property="correctrate" column="correctrate"></result>
            <result property="ctype" column="ctype"></result>
        </collection>
    </resultMap>
    <select id="getStudentScantron" resultMap="scantronResult">
        select p.papername,ps.papernum,truncate(ps.userscore*100/ps.paperscore,2)scorerate,#{paper.papertype} papertype
        ,ps.psid,ps.stoptime,ps.costtime,q.qorder,truncate(q.stuscore*100/q.qscore,2)correctrate,tq.ctype
        ,(select count(*)+1 from t_paper_student ps1
        where ps1.paperid=ps.paperid and ps1.userscore>ps.userscore)rank,ps.paperscore,ps.userscore
        from t_paper_student ps left join t_paper p on ps.paperid = p.paperid
        <if test="paper.papertype==1 or paper.papertype==3">
            left join t_paper_student_question q on ps.psid=q.psid
            left join t_question tq on q.questionid=tq.questionid
        </if>
        <if test="paper.papertype==2">
            left join t_paper_student_scantron q on ps.psid=q.psid
            left join t_paper_scantron tq on q.pscaid=tq.pscaid
        </if>
        where ps.psid=#{paper.psid}
    </select>
    <!--学生答题卡单题详情-->
    <select id="getStudentQuestion" resultType="com.xhkjedu.sstudy.vo.paperstudent.PaperStudentQuestionVo">
        SELECT psq.qorder,psq.useranswer,psq.converted,psq.useranswertext,psq.useranswertxt,psq.device,psq.qscore,psq.stuscore
        ,q.questionid,q.qstem,q.qoption,q.qanswer,q.qanalyze,q.ctype
        ,(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
        WHERE psq.psid=#{paper.psid} and psq.qorder=#{paper.qorder} limit 1
    </select>
    <!--试题详情-->
    <resultMap id="questionDetailResult" type="com.xhkjedu.sstudy.vo.paper.PQuestionVo">
        <result property="commitrate" column="commitrate"></result>
        <result property="scorerate" column="scorerate"></result>
        <result property="qtypename" column="qtypename"></result>
        <result property="qscore" column="qscore"></result>
        <result property="avgtime" column="avgtime"></result>
        <result property="mintime" column="mintime"></result>
        <collection property="students" ofType="com.xhkjedu.sstudy.vo.paper.PStudentVo">
            <result property="studentname" column="studentname"></result>
            <result property="stoptime" column="answertime"></result>
            <result property="costtime" column="costtime"></result>
            <result property="scorerate" column="stuscore"></result>
            <result property="pschecked" column="answered"></result>
        </collection>
    </resultMap>
    <!--20220421修改获取提交作业的学生未基准-->
    <select id="getQuestionDetail" resultMap="questionDetailResult">
        select tq.qtypename,q.qscore,q.answertime,q.costtime,q.stuscore,q.answered,u.username studentname
        <if test="question.qctype==1 or question.qctype==3">
            from t_paper_student_question q left join t_question tq on q.questionid=tq.questionid
        </if>
        <if test="question.qctype==2">
            from t_paper_student_scantron q left join t_paper_scantron tq on q.pscaid=tq.pscaid
        </if>
        left join t_user u on q.studentid=u.userid
        left join t_paper_student ps on q.paperid=ps.paperid and q.studentid=ps.studentid
        where q.paperid=#{question.paperid} and q.qorder=#{question.qorder} and q.classid=#{question.classid} and ps.psstate>0
        order by q.stuscore desc,convert(u.username using gbk)
    </select>
    <!--作业发布的人数-->
    <select id="getPaperstudentNum" resultType="java.lang.Integer">
        select count(*) from t_paper_student where paperid=#{paperid} and classid=#{classid}
    </select>
    <!--作业学生列表-->
    <select id="listPaperStudent" resultType="com.xhkjedu.sstudy.vo.paper.PStudentVo">
        select u.username studentname,ps.psid,ps.stoptime,ps.costtime
        ,(case when ps.psstate=0 then 0 when ps.pschecked=1 then 2 else 1 end)pschecked
        ,u.headpic,ps.paperscore,ps.userscore,ps.papernum
        from t_paper_student ps left join t_user u on ps.studentid=u.userid
        where ps.paperid=#{paper.paperid} and ps.classid=#{paper.classid}
        order by ps.pschecked,ps.userscore ${paper.fieldorder},convert(u.username using gbk)
    </select>
</mapper>
