<?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.PaperStudentMapper">
    <insert id="saveBatchPaperStudent" parameterType="java.util.List">
        INSERT INTO t_paper_student (paperid,classid,studentid,papernum,
        paperscore,createid,createtime,schoolid,questionids)
        VALUES
        <foreach collection ="list" item="ps" index= "index" separator =",">
            (#{ps.paperid},#{ps.classid},#{ps.studentid},#{ps.papernum},
            #{ps.paperscore},#{ps.createid},#{ps.createtime},#{ps.schoolid},#{ps.questionids})
        </foreach>
    </insert>
    <!--作业列表-->
    <select id="findAll" resultType="com.xhkjedu.sstudy.vo.paperstudent.PaperStudentVo">
        select ps.psid,p.paperid,p.papername,p.paperclass,ps.papernum,ps.psstate,ps.pschecked
        ,pg.starttime,pg.stoptime,pg.showtype,ps.showanswer,u.username createname,p.papertype,d.directorname
        from t_paper_student ps join t_paper p on p.paperid = ps.paperid
        left join t_director d on p.directorid=d.directorid
        left join t_paper_group pg on ps.paperid = pg.paperid and ps.classid=pg.classid
        left join t_user u on p.createid = u.userid
        where ps.studentid = #{ps.studentid} and pg.starttime&lt;=UNIX_TIMESTAMP()
        <if test="ps.subjectid!=null and ps.subjectid!='0'.toString() and ps.subjectid!=''">
            and p.subjectid=#{ps.subjectid}
        </if>
        <if test="ps.lsbid!=null and ps.lsbid!='0'.toString() and ps.lsbid!=''">
            and p.lsbid=#{ps.lsbid}
        </if>
        <if test="ps.directorid!=null and ps.directorid!='0'.toString() and ps.directorid!=''">
            and p.directorid=#{ps.directorid}
        </if>
        <if test="ps.psstate==1">
            and ps.psstate=0
        </if>
        <if test="ps.psstate==2">
            and ps.psstate!=0 and ps.pschecked=0
        </if>
        <if test="ps.psstate==3">
            and ps.pschecked!=0
        </if>
        GROUP BY ps.psid ORDER BY pg.starttime DESC,p.createtime DESC LIMIT #{ps.page},#{ps.pageSize}
    </select>
    <!--批阅列表-->
    <select id="correctList" resultType="com.xhkjedu.sstudy.vo.paperstudent.PaperStudentVo">
        select ps.psid,p.paperid,p.papername,p.paperclass,ps.psstate,ps.pschecked
        ,pg.starttime,pg.stoptime,u.username createname,s.subjectname,p.papertype,pg.checked
        from t_paper_student ps join t_paper p on p.paperid = ps.paperid
        left join t_paper_group pg on ps.paperid = pg.paperid and ps.classid=pg.classid
        left join t_user u on p.createid = u.userid
        left join t_subject s on p.subjectid = s.subjectid
        where ps.checkid=#{ps.checkid} and ps.psstate!=0 and ps.pschecked!=1
        ORDER BY ps.stoptime desc LIMIT #{ps.page},#{ps.pageSize}
    </select>
    <!--作业说明-->
    <select id="getPaperComm" resultType="com.xhkjedu.sstudy.vo.paperstudent.PaperCommVo">
        select ps.psid,p.papername,ps.papernum,ps.paperscore,p.paperclass,p.comm,pg.starttime,pg.stoptime,pg.showtype
        from t_paper_student ps left join t_paper_group pg on ps.paperid=pg.paperid and ps.classid=pg.classid
        left join t_paper p on pg.paperid = p.paperid
        where ps.psid=#{psid}
    </select>
    <!--更新学生作业提交、批改状态-->
    <update id="updateState">
        update t_paper_student s set
        <choose>
            <when test="operate==1">s.starttime=UNIX_TIMESTAMP(NOW())</when>
            <when test="operate==2">s.pschecked=#{pschecked},s.psstate=#{psstate}</when>
            <when test="operate==3">s.pschecked=#{pschecked}</when>
        </choose>
        where s.psid=#{psid}
    </update>
    <!--批改完修改学生总得分和批改状态 (结构性试题)-->
    <update id="updateStuScore">
        UPDATE t_paper_student s
        SET s.userscore = (SELECT SUM(IFNULL(q.stuscore,0)) FROM t_paper_student_question q WHERE q.psid=s.psid)
        ,s.pschecked=1,s.checkid=#{checkid}
        WHERE s.psid = #{psid}
    </update>
    <!--批改完修改学生总得分和批改状态 (答题卡试题)-->
    <update id="updateScantronStuScore">
        UPDATE t_paper_student s
        SET s.userscore = (SELECT SUM(IFNULL(q.stuscore,0)) FROM t_paper_student_scantron q WHERE q.psid=s.psid)
          ,s.pschecked=1,s.checkid=#{checkid}
        WHERE s.psid = #{psid}
    </update>

    <!--详情-->
    <select id="findById" resultType="com.xhkjedu.sstudy.model.paperstudent.TPaperStudent">
        select s.psid,s.paperid,s.classid,s.studentid,s.psstate,s.pschecked from t_paper_student s
        where s.psid=#{psid} limit 1
    </select>
    <!--详情退回学生作业使用-->
    <select id="findById2" resultType="com.xhkjedu.sstudy.model.paperstudent.TPaperStudent">
        select s.psid,s.paperid,s.classid,s.studentid,s.psstate,s.pschecked,pg.checked
        from t_paper_student s left join t_paper_group pg on s.paperid=pg.paperid and s.classid=pg.classid
        where s.psid=#{psid} limit 1
    </select>

    <!--批量更新试题批改信息-->
    <update id="updatePsQuestionBatch" parameterType="java.util.List">
        UPDATE t_paper_student_question
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="stuscore = CASE " suffix="END,">
                <foreach collection="list" index="index" item="item" >
                    when psqid = #{item.psqid} then #{item.stuscore}
                </foreach>
            </trim>
            <trim prefix="checked = CASE " suffix="END,">
                <foreach collection="list" index="index" item="item" >
                    when psqid = #{item.psqid} then #{item.checked}
                </foreach>
            </trim>
            <trim prefix="checkid = CASE " suffix="END,">
                <foreach collection="list" index="index" item="item" >
                    when psqid = #{item.psqid} then #{item.checkid}
                </foreach>
            </trim>
            <trim prefix="checktime = CASE " suffix="END,">
                <foreach collection="list" index="index" item="item" >
                    when psqid = #{item.psqid} then #{item.checktime}
                </foreach>
            </trim>
            <trim prefix="checkcomm = CASE " suffix="END,">
                <foreach collection="list" index="index" item="item" >
                    when psqid = #{item.psqid} then #{item.checkcomm}
                </foreach>
            </trim>
        </trim>
        where
        <foreach collection="list" item="item" index="index" separator="or">
            psqid =#{item.psqid}
        </foreach>
    </update>
    <!--判断该作业该班级是否存在未批改学生-->
    <select id="getNotCorrect" resultType="java.lang.Integer">
        select count(*) from t_paper_student
        where paperid=#{paperid} and classid=#{classid} and psstate>0 and pschecked!=1
    </select>
    <!--设置批改人-->
    <update id="batchUpdateCheckId">
        UPDATE t_paper_student
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="checkid = CASE " suffix="END,">
                <foreach collection="list" index="index" item="item">
                    when studentid=#{item.studentid} and classid=#{item.classid} then #{item.checkid}
                </foreach>
            </trim>
        </trim>
        where
        <foreach collection="list" item="item" index="index" separator="or">
            (paperid=#{item.paperid} and classid=#{item.classid} and studentid=#{item.studentid})
        </foreach>
    </update>
    <!--设置批改人-->
    <update id="updateCheckId">
        update t_paper_student set checkid=#{checkid} where paperid=#{paperid}
    </update>
    <update id="updateCheckId2">
        update t_paper_group pg left join t_paper_student ps on pg.paperid=ps.paperid and pg.classid=ps.classid
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="pg.checkid = CASE " suffix="END,">
                <foreach collection="params" index="index" item="item">
                    when pg.classid=#{item.classid} then #{item.checkid}
                </foreach>
            </trim>
            <trim prefix="ps.checkid = CASE " suffix="END,">
                <foreach collection="params" index="index" item="item">
                    when ps.classid=#{item.classid} then #{item.checkid}
                </foreach>
            </trim>
        </trim>
        where pg.paperid=#{paperid}
    </update>
    <!--获取作业学生id集合-->
    <select id="listStudentIdByPaperIdClassId" resultType="com.xhkjedu.sstudy.vo.paper.PaperCheckParam">
        select studentid checkid,classid from t_paper_student where paperid=#{paperid} and classid in
        <foreach collection="params" item="param" index="index" open="(" separator="," close=")">
            ${param.classid}
        </foreach>
    </select>

    <update id="updateShowanswer">
        update t_paper_student ps left join t_paper_group pg on ps.paperid=pg.paperid
        set ps.showanswer=1 where pg.pgstate=22 AND pg.showtype=1 and ps.showanswer=0 and pg.stoptime <![CDATA[ <= ]]> UNIX_TIMESTAMP(NOW())
    </update>



    <!--//////////////////////////////////////////////学生WEB端//////////////////////////////////////////////-->
    <!--作业列表-->
    <select id="listWeb" resultType="com.xhkjedu.sstudy.vo.paperstudent.PaperStudentVo">
        select ps.psid,p.paperid,p.papername,p.paperclass,ps.papernum,ps.psstate,ps.pschecked
        ,pg.starttime,pg.stoptime,ps.showanswer,u.username createname,p.papertype,s.subjectname
        from t_paper_student ps join t_paper p on p.paperid = ps.paperid
        left join t_subject s on p.subjectid=s.subjectid
        left join t_paper_group pg on ps.paperid = pg.paperid and ps.classid=pg.classid
        left join t_user u on p.createid = u.userid
        where ps.studentid = #{ps.studentid} and pg.starttime&lt;=UNIX_TIMESTAMP()
        <if test="ps.subjectid!=null and ps.subjectid!='0'.toString() and ps.subjectid!=''">
            and p.subjectid=#{ps.subjectid}
        </if>
        <if test="ps.lsbid!=null and ps.lsbid!='0'.toString() and ps.lsbid!=''">
            and p.lsbid=#{ps.lsbid}
        </if>
        <if test="ps.directorid!=null and ps.directorid!='0'.toString() and ps.directorid!=''">
            and p.directorid=#{ps.directorid}
        </if>
        <if test="ps.psstate==1">
            and ps.psstate=0
        </if>
        <if test="ps.psstate==2">
            and ps.psstate!=0 and ps.pschecked=0
        </if>
        <if test="ps.psstate==3">
            and ps.pschecked!=0
        </if>
        <if test="ps.starttime!=null and ps.starttime!=0">
            and UNIX_TIMESTAMP(FROM_UNIXTIME(pg.starttime,'%Y%m%d'))>=#{ps.starttime}
        </if>
        <if test="ps.stoptime!=null and ps.stoptime!=0">
            and UNIX_TIMESTAMP(FROM_UNIXTIME(pg.starttime,'%Y%m%d'))&lt;=#{ps.stoptime}
        </if>
        GROUP BY ps.psid ORDER BY pg.starttime DESC,p.createtime DESC,ps.psid desc
    </select>
    <!--提交作业-->
    <update id="submitPaper">
        update t_paper_student set stoptime=#{ps.stoptime},costtime=ifnull(costtime,0)+#{ps.costtime}
        ,psstate=#{ps.psstate},userscore=#{ps.userscore},pschecked=#{ps.pschecked},showanswer=#{ps.showanswer}
        where psid=#{ps.psid}
    </update>
    <!--批量修改题库作业学生作答信息-->
    <update id="batchUpdateQuestion">
        UPDATE t_paper_student_question
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="answered = CASE " suffix="END,">
                <foreach collection="list" index="index" item="item">
                    when psqid=#{item.psqid} then #{item.answered}
                </foreach>
            </trim>
            <trim prefix="useranswer = CASE " suffix="END,">
                <foreach collection="list" index="index" item="item">
                    when psqid=#{item.psqid} then #{item.useranswer}
                </foreach>
            </trim>
            <trim prefix="converted = CASE " suffix="END,">
                <foreach collection="list" index="index" item="item">
                    when psqid=#{item.psqid} then #{item.converted}
                </foreach>
            </trim>
            <trim prefix="useranswertext = CASE " suffix="END,">
                <foreach collection="list" index="index" item="item">
                    when psqid=#{item.psqid} then #{item.useranswertext}
                </foreach>
            </trim>
            <trim prefix="useranswertxt = CASE " suffix="END,">
                <foreach collection="list" index="index" item="item">
                    when psqid=#{item.psqid} then #{item.useranswertxt}
                </foreach>
            </trim>
            <trim prefix="device = CASE " suffix="END,">
                <foreach collection="list" index="index" item="item">
                    when psqid=#{item.psqid} then #{item.device}
                </foreach>
            </trim>
            <trim prefix="answertime = CASE " suffix="END,">
                <foreach collection="list" index="index" item="item">
                    when psqid=#{item.psqid} then #{item.answertime}
                </foreach>
            </trim>
            <trim prefix="stuscore = CASE " suffix="END,">
                <foreach collection="list" index="index" item="item">
                    when psqid=#{item.psqid} then ifnull(#{item.stuscore},stuscore)
                </foreach>
            </trim>
            <trim prefix="checked = CASE " suffix="END,">
                <foreach collection="list" index="index" item="item">
                    when psqid=#{item.psqid} then ifnull(#{item.checked},checked)
                </foreach>
            </trim>
            <trim prefix="checkid = CASE " suffix="END,">
                <foreach collection="list" index="index" item="item">
                    when psqid=#{item.psqid} then ifnull(#{item.checkid},checkid)
                </foreach>
            </trim>
            <trim prefix="checktime = CASE " suffix="END,">
                <foreach collection="list" index="index" item="item">
                    when psqid=#{item.psqid} then ifnull(#{item.checktime},checktime)
                </foreach>
            </trim>
            <trim prefix="checkcomm = CASE " suffix="END,">
                <foreach collection="list" index="index" item="item" >
                    when psqid = #{item.psqid} then #{item.checkcomm}
                </foreach>
            </trim>
        </trim>
        where
        <foreach collection="list" item="item" index="index" separator="or">
            psqid=#{item.psqid}
        </foreach>
    </update>
    <!--附件作业提交更新操作-->
    <update id="updateSPaper">
        update t_paper_student set stoptime=#{ps.stoptime},costtime=#{ps.costtime}
        ,psstate=#{ps.psstate},pschecked=#{ps.pschecked},showanswer=#{ps.showanswer}
        ,userscore=(SELECT SUM(IFNULL(q.stuscore,0)) FROM t_paper_student_scantron q WHERE q.psid=#{ps.psid})
        where psid=#{ps.psid}
    </update>
    <!--题库作业提交更新操作-->
    <update id="updatePaper">
        update t_paper_student set stoptime=#{ps.stoptime},costtime=#{ps.costtime}
        ,psstate=#{ps.psstate},pschecked=#{ps.pschecked},showanswer=#{ps.showanswer}
        ,userscore=(SELECT SUM(IFNULL(q.stuscore,0)) FROM t_paper_student_question q WHERE q.psid=#{ps.psid})
        where psid=#{ps.psid}
    </update>
    <!--作业学生列表-->
    <select id="listStudent" resultType="java.util.Map">
        select ps.classid,ps.studentid,u.username,ps.questionids
        from t_paper_student ps left join t_user u on ps.studentid = u.userid
        where paperid=#{paperid}
    </select>
</mapper>
