<?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.PaperMapper">
    <resultMap id="typeResult" type="com.xhkjedu.sstudy.vo.paper.PaperQtypeVo">
        <result property="qtypeid" column="qtypeid"></result>
        <result property="qtypename" column="qtypename"></result>
        <result property="ptorder" column="ptorder"></result>
        <result property="ptscore" column="ptscore"></result>
        <result property="ptnum" column="ptnum"></result>
        <result property="ctype" column="ctype"></result>
        <collection property="questions" ofType="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="typeid"></result>
            <result property="qtypename" column="typename"></result>
            <collection property="points" ofType="com.xhkjedu.sstudy.vo.subjectbook.PointVo">
                <result property="pointid" column="pointid"></result>
                <result property="pointname" column="pointname"></result>
            </collection>
        </collection>
    </resultMap>
    <!--获取作业题型列表-->
    <select id="listPaperType" resultMap="typeResult">
        select pt.qtypeid,pt.qtypename,pt.ptorder,pt.ptscore,pt.ptnum,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
        ,q.qtypeid typeid,q.qtypename typename
        from t_paper_qtype pt left join t_paper_qtype_question ptq on pt.ptid = ptq.ptid
        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 pt.paperid=#{paperid} order by pt.ptorder
    </select>
    <!-- 修改作业状态 -->
    <update id="updatePaperState">
        UPDATE t_paper SET paperstate=#{paperstate}
        <if test="ctypecount==0">,checktype=4</if>
        WHERE paperid= #{paperid}
    </update>
    <!-- 修改作业信息 -->
    <update id="updatePaper" parameterType="com.xhkjedu.sstudy.model.paper.TPaper">
        UPDATE t_paper SET papername=#{paper.papername}, papernum=#{paper.papernum},
       paperscore=#{paper.paperscore},papertype=#{paper.papertype},paperclass=#{paper.paperclass}
       WHERE paperid=#{paper.paperid}
    </update>

    <!--详情-->
    <select id="findById" resultType="com.xhkjedu.sstudy.vo.paper.PaperVo">
        select p.paperid,p.papername,p.papernum,p.paperscore,p.createtime,u.username createname,
        p.papertype,p.comm,p.paperclass,p.directorid,p.paperstate
        ,(select showtype from t_paper_group pg where pg.paperid=p.paperid limit 1)showtype
        from t_paper p left join t_user u on u.userid=p.createid
        where p.paperid=#{paperid} limit 1
    </select>

    <!--作业列表-->
    <resultMap id="paperResult" type="com.xhkjedu.sstudy.vo.paper.PaperListVo">
        <result property="paperid" column="paperid"></result>
        <result property="papername" column="papername"></result>
        <result property="papertype" column="papertype"></result>
        <result property="paperclass" column="paperclass"></result>
        <result property="paperstate" column="paperstate"></result>
        <result property="checktype" column="checktype"></result>
        <result property="createid" column="createid"></result>
        <result property="createtime" column="createtime"></result>
        <result property="createname" column="createname"></result>
        <result property="starttime" column="starttime"></result>
        <result property="stoptime" column="stoptime"></result>
        <result property="papernum" column="papernum"></result>
        <result property="showtype" column="showtype"></result>
        <collection property="classes" ofType="com.xhkjedu.sstudy.vo.paper.PaperClassVo" javaType="java.util.List"
                    select="listClass" column="{paperid=paperid,createid=createid,checkid=checkid,checktype=checktype}">
        </collection>
    </resultMap>
    <resultMap id="classResult" type="com.xhkjedu.sstudy.vo.paper.PaperClassVo">
        <result property="classid" column="classid"></result>
        <result property="classname" column="classname"></result>
        <result property="gradeid" column="gradeid"></result>
        <result property="classnum" column="classnum"></result>
        <result property="ytjnum" column="ytjnum"></result>
        <result property="ypgnum" column="ypgnum"></result>
        <result property="studentname" column="studentname"></result>
        <result property="checkid" column="checkid"></result>
        <result property="teachername" column="teachername"></result>
        <result property="checked" column="checked"></result>
    </resultMap>
    <select id="listClass" resultMap="classResult">
        select count(*)classnum,ps.classid,c.classname,c.gradeid,pg.checked
        ,count(distinct case when ps.psstate!=0 then ps.psid else null end)ytjnum
        ,count(distinct case when ps.pschecked=1 then ps.psid else null end)ypgnum
        <if test="checktype==3">
        ,(select group_concat(distinct u.username) from t_paper_student ps1 left join t_user u on ps1.checkid=u.userid
        where ps1.paperid=ps.paperid and ps1.classid=ps.classid)studentname
        </if>
        <if test="checktype==1">
        ,pg.checkid,(select group_concat(distinct u.username) from t_user u where pg.checkid=u.userid)teachername
        </if>
        from t_paper_group pg left join t_class c on pg.classid=c.classid
        left join t_paper_student ps on ps.paperid=pg.paperid and ps.classid=pg.classid
        where pg.paperid=#{paperid}
        <if test="checkid!=null and checkid!=0 and checkid!=createid">
            and pg.checkid=#{checkid}
        </if>
        group by pg.classid order by c.gradeid,c.classtype,c.classorder
    </select>
    <!--教师作业列表-->
    <select id="listAll" resultMap="paperResult">
        select p.paperid,p.papername,p.papertype,p.paperclass,p.paperstate,p.checktype,p.createtime
        ,u.username createname,pg.starttime,pg.stoptime,pg.showtype,p.papernum,p.createid,#{paper.createid} checkid
        from t_paper p left join t_user u on p.createid=u.userid
        left join t_paper_group pg on p.paperid=pg.paperid
        where p.lsbid=#{paper.lsbid} and (p.createid=#{paper.createid} or pg.checkid=#{paper.createid})
        <if test="paper.directorid!=null and paper.directorid!='0'.toString() and paper.directorid!=''">
            and p.directorid=#{paper.directorid}
        </if>
        <if test="paper.papertype!=0 and paper.papertype!=null">
            and p.papertype=#{paper.papertype}
        </if>
        <if test="paper.papername!=null and paper.papername!=''">
            and p.papername like '%${paper.papername}%'
        </if>
        <if test="paper.starttime!=null and paper.starttime!=0">
            and UNIX_TIMESTAMP(FROM_UNIXTIME(p.createtime,'%Y%m%d'))>=#{paper.starttime}
        </if>
        <if test="paper.stoptime!=null and paper.stoptime!=0">
            and UNIX_TIMESTAMP(FROM_UNIXTIME(p.createtime,'%Y%m%d'))&lt;=#{paper.stoptime}
        </if>
        group by p.paperid
        <if test="paper.psstate==0 || paper.psstate==1">
            having min(pg.checked)=#{paper.psstate}
        </if>
        order by p.createtime desc
    </select>
    <!--教师作业列表(未发布)-->
    <select id="listWfbPapers" resultType="com.xhkjedu.sstudy.vo.paper.PaperListVo">
        select p.paperid,p.papername,p.papertype,p.paperclass,p.paperstate,p.checktype,p.createtime,p.createid
        from t_paper p where p.lsbid=#{paper.lsbid} and p.createid=#{paper.createid} and p.paperstate=21
        <if test="paper.directorid!=null and paper.directorid!='0'.toString() and paper.directorid!=''">
            and p.directorid=#{paper.directorid}
        </if>
        <if test="paper.papertype!=0 and paper.papertype!=null">
            and p.papertype=#{paper.papertype}
        </if>
        <if test="paper.papername!=null and paper.papername!=''">
            and p.papername like '%${paper.papername}%'
        </if>
        <if test="paper.starttime!=null and paper.starttime!=0">
            and UNIX_TIMESTAMP(FROM_UNIXTIME(p.createtime,'%Y%m%d'))>=#{paper.starttime}
        </if>
        <if test="paper.stoptime!=null and paper.stoptime!=0">
            and UNIX_TIMESTAMP(FROM_UNIXTIME(p.createtime,'%Y%m%d'))&lt;=#{paper.stoptime}
        </if>
        group by p.paperid order by p.createtime desc
    </select>
    <!--教师批阅列表-->
    <select id="correctList" resultMap="paperResult">
        select p.paperid,p.papername,p.papertype,p.paperclass,p.paperstate,p.checktype,p.createtime,p.createid,#{checkid} checkid
        ,u.username createname,pg.starttime,pg.stoptime,p.papernum
        from t_paper p left join t_user u on p.createid=u.userid
        left join t_paper_group pg on p.paperid=pg.paperid
        left join t_paper_student ps on p.paperid=ps.paperid and pg.classid=ps.classid
        where p.checktype=1 and ps.checkid=#{checkid} and ps.psstate!=0 and ps.pschecked!=1
        group by p.paperid order by p.createtime desc
    </select>
    <!--设置批改方式-->
    <update id="updateCheckType">
        update t_paper set checktype=#{checktype} where paperid=#{paperid}
    </update>
    <!--获取批改学生列表-->
    <resultMap id="checkResult" type="com.xhkjedu.sstudy.vo.paper.CheckClassVo">
        <result property="classid" column="classid"></result>
        <result property="classname" column="classname"></result>
        <result property="classnum" column="classnum"></result>
        <result property="ytjnum" column="ytjnum"></result>
        <result property="ypynum" column="ypynum"></result>
        <collection property="students" ofType="com.xhkjedu.sstudy.vo.paperstudent.StudentVo"
                    javaType="java.util.List" select="listStudent" column="{paperid=paperid,checkid=checkid,classid=classid}">
        </collection>
    </resultMap>
    <resultMap id="studentResult" type="com.xhkjedu.sstudy.vo.paperstudent.StudentVo">
        <result property="psid" column="psid"></result>
        <result property="studentid" column="studentid"></result>
        <result property="studentname" column="studentname"></result>
        <result property="pschecked" column="pschecked"></result>
    </resultMap>
    <select id="listStudent" resultMap="studentResult">
        select ps.psid,ps.studentid,u.username studentname,ps.pschecked
        from t_paper_student ps left join t_user u on ps.studentid=u.userid
        where ps.paperid=#{paperid} and ps.classid=#{classid} and ps.psstate!=0
        <if test="checkid!=null and checkid!=0">
            and ps.checkid=#{checkid}
        </if>
        order by convert(u.username using gbk)
    </select>
    <select id="listStudentsByPaperIdCheckId" resultMap="checkResult">
        select ps.classid,c.classname,c.gradeid,count(*)classnum,ps.paperid,#{checkid} checkid,ps.classid
        ,count(distinct case when ps.psstate!=0 then ps.psid else null end)ytjnum
        ,count(distinct case when ps.pschecked=1 then ps.psid else null end)ypynum
        from t_paper_student ps left join t_class c on ps.classid=c.classid
        where ps.paperid=#{paperid} and ps.classid=#{classid}
        <if test="checkid!=null and checkid!=0">
            and ps.checkid=#{checkid}
        </if>
    </select>
    <!--作业附件列表-->
    <select id="listPaperFile" resultType="com.xhkjedu.sstudy.model.paper.TPaperFile">
        SELECT * FROM t_paper_file WHERE paperid=#{paperid}
        <if test="fileclass!=null and fileclass!=0">
            and fileclass=#{fileclass}
        </if>
        ORDER BY fileorder
    </select>
    <!--获取年级下教师发布作业列表-->
    <select id="listPaper" resultMap="paperResult">
        select p.paperid,p.papername,p.papertype,p.paperclass,p.paperstate,p.checktype,p.createtime
        ,pg.starttime,pg.stoptime,p.papernum,p.createid,#{paper.teacherid} checkid
        from t_paper p left join t_paper_group pg on p.paperid=pg.paperid
        left join t_class c on pg.classid=c.classid
        where p.createid=#{paper.teacherid} and c.gradeid=#{paper.gradeid} and c.year=#{paper.year}
        and UNIX_TIMESTAMP(FROM_UNIXTIME(p.createtime,'%Y%m%d'))>=#{paper.starttime}
        and UNIX_TIMESTAMP(FROM_UNIXTIME(p.createtime,'%Y%m%d'))&lt;=#{paper.stoptime}
        <if test="paper.classid!=null and paper.classid!=0">
            and c.classid=#{paper.classid}
        </if>
        <if test="paper.classids!=null and paper.classids!=''">
            and c.classid in (${paper.classids})
        </if>
        <if test="paper.subjectid!='0'.toString() and paper.subjectid!=''">
            and p.subjectid=#{paper.subjectid}
        </if>
        group by p.paperid order by p.createtime desc
    </select>

    <!--作业删除-->
    <delete id="delPaper">
        DELETE qe.*,qc.*,qs.*,p.*
        FROM t_paper p LEFT JOIN t_question_class qc ON p.paperid=qc.paperid
        LEFT JOIN t_question_explain qe ON qc.questionid=qe.questionid AND qc.qctype=qe.qctype
        LEFT JOIN t_question_student qs ON p.paperid=qs.paperid
        WHERE p.paperid=#{paperid}
    </delete>
    <!--获取作业原文件保存地址列表-->
    <select id="listSourcePath" resultType="java.lang.String">
        select distinct sourcepath from t_paper_file where paperid=#{paperid} and fileclass=1
    </select>
    <!--获取今天布置作业数量-->
    <select id="getPaperNum" resultType="java.lang.Integer">
        select count(*)+1 from t_paper where subjectid=#{subjectid} and createid=#{createid}
        and UNIX_TIMESTAMP(FROM_UNIXTIME(createtime,'%Y%m%d'))>=#{createtime}
        and UNIX_TIMESTAMP(FROM_UNIXTIME(createtime,'%Y%m%d'))&lt;=#{createtime}
    </select>
    <!--获取作业状态-->
    <select id="getPaperState" resultType="java.lang.Integer">
        select paperstate from t_paper where paperid=#{paperid}
    </select>

    <!--获取批阅班级-->
    <select id="listPaperClass" resultType="java.util.Map">
        select pg.classid,c.classname,c.gradeid,pg.checked
        from t_paper_group pg left join t_class c on pg.classid = c.classid
        left join t_paper_student ps on pg.paperid=ps.paperid and pg.classid=ps.classid
        where pg.paperid=#{paperid} and ps.psstate!=0
        <if test="checkid!=null and checkid!=0">
            and pg.checkid=#{checkid}
        </if>
        group by pg.classid order by c.gradeid,c.classtype,c.classorder
    </select>
    <!--根据作业id获取作业分类-->
    <select id="getPaperTypeById" resultType="java.lang.Integer">
        select papertype from t_paper where paperid=#{paperid}
    </select>
</mapper>
