<?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.question.QuestionClassMapper">
    <!--作业统计按题-->
    <select id="getQuestionAnalyze" resultType="com.xhkjedu.sstudy.model.question.TQuestionClass">
        select ptq.questionid,ptq.ptqorder qorder,ptq.ptqscore qscore,sum(ifnull(psq.stuscore,0))stuscore,ptq.schoolid,q.qtypeid
        ,(select count(*) from t_paper_student ps where ps.paperid=#{paperid} and ps.classid=#{classid}
        <if test="qctype==3">and locate(concat('"',ptq.questionid,'"'),ps.questionids)>0</if>)stunum
        from t_paper_qtype_question ptq left join t_question q on ptq.questionid=q.questionid
        left join t_paper_student_question psq on ptq.paperid = psq.paperid and ptq.questionid=psq.questionid and psq.classid=#{classid}
        where ptq.paperid=#{paperid}
        group by ptq.questionid having stunum!=0 order by ptq.ptqorder
    </select>
    <!--答题卡作业统计按题-->
    <select id="getSQuestionAnalyze" resultType="com.xhkjedu.sstudy.model.question.TQuestionClass">
        select ps.pscaid questionid,ps.psorder qorder,ps.psscore qscore,sum(ifnull(pss.stuscore,0))stuscore,ps.schoolid,ps.qtypeid
        ,(select count(*) from t_paper_student ps where ps.paperid=#{paperid} and ps.classid=#{classid})stunum
        from t_paper_scantron ps left join t_paper_student_scantron pss on ps.pscaid=pss.pscaid and pss.classid=#{classid}
        where ps.paperid=#{paperid}
        group by ps.pscaid order by ps.psorder
    </select>
    <!--考试统计按题-->
    <select id="getEQuestionAnalyze" resultType="com.xhkjedu.sstudy.model.question.TQuestionClass">
        select ps.pscaid questionid,ps.psorder qorder,ps.psscore qscore,sum(ifnull(pss.stuscore,0))stuscore,ps.schoolid,ps.ctype qtypeid
        ,(select count(*) from t_classroom_paper_student ps where ps.paperid=#{paperid} and ps.classid=#{classid})stunum
        from t_classroom_paper_scantron ps left join t_classroom_paper_student_scantron pss on ps.pscaid=pss.pscaid
        where ps.paperid=#{paperid} and pss.classid=#{classid}
        group by ps.pscaid order by ps.psorder
    </select>
    <!--详情-->
    <select id="findById" resultType="com.xhkjedu.sstudy.vo.question.QuestionClassVo">
        select q.questionid,q.score,q.complexity,q.ctype,q.qstem,q.qoption,q.qanswer,q.qanalyze,q.belong
        ,q.qtypeid,q.qtypename,q.createtime,u.username createname,qd.directorid,d.directorname,0 pointid
        from t_question q left join t_user u on u.userid = q.createid
        left join t_question_director qd on q.questionid=qd.questionid
        left join t_director d on qd.directorid = d.directorid
        where q.questionid=#{questionid} limit 1
    </select>
    <!--章节班级错题列表-->
    <select id="directorList" resultType="com.xhkjedu.sstudy.vo.question.QuestionClassVo">
        select qc.questionid,qc.qscore,qc.avgscore,qc.errorrate,qc.qctype,qc.qtypeid
        ,concat(p.papername,'-第',qc.qorder,'题')qstem
        from (select max(qcid)qcid,questionid from t_question_class where classid=#{question.classid} group by
        questionid)a
        left join t_question_class qc on a.qcid=qc.qcid
        left join t_paper p on qc.paperid=p.paperid
        where qc.classid=#{question.classid} and qc.qctype in(1,2)
        and UNIX_TIMESTAMP(FROM_UNIXTIME(qc.createtime,'%Y%m%d'))>=#{question.starttime}
        and UNIX_TIMESTAMP(FROM_UNIXTIME(qc.createtime,'%Y%m%d'))&lt;=#{question.stoptime}
        <if test="question.subjectid!=null and question.subjectid!='0'.toString() and question.subjectid!=''">
            and p.subjectid=#{question.subjectid}
        </if>
        <if test="question.lsbid!=null and question.lsbid!='0'.toString() and question.lsbid!=''">
            and p.lsbid=#{question.lsbid}
        </if>
        <if test="question.directorid!=null and question.directorid!='0'.toString() and question.directorid!=''">
            and p.directorid=#{question.directorid}
        </if>
        <if test="question.qtypeid!=null and question.qtypeid!='0'.toString() and question.qtypeid!=''">
            and qc.qtypeid=#{question.qtypeid}
        </if>
        group by qc.questionid order by
        <if test="question.errorrate!=null and question.errorrate!=0">
            qc.errorrate <if test="question.errorrate==1">desc</if>,
        </if>
        qc.qcid desc
    </select>
    <!--根据试题id获取试题信息-->
    <select id="getQuestionById" resultType="com.xhkjedu.sstudy.vo.question.QuestionClassVo">
        select q.questionid,q.ctype,q.qstem,q.qoption,q.qtypename,q.count from t_question q where
        <foreach collection="questionids" item="questionid" index="index" separator="or">
            q.questionid=#{questionid}
        </foreach>
    </select>
    <!--根据试题id获取答题卡信息-->
    <select id="getScantronById" resultType="com.xhkjedu.sstudy.vo.question.QuestionClassVo">
        select q.pscaid questionid,q.ctype,q.qtypename from t_paper_scantron q where
        <foreach collection="questionids" item="questionid" index="index" separator="or">
            q.pscaid=#{questionid}
        </foreach>
    </select>
    <!--知识点班级错题列表-->
    <select id="pointList" resultType="com.xhkjedu.sstudy.vo.question.QuestionClassVo">
        select qc.questionid,qc.qscore,qc.avgscore,qc.errorrate,qc.qctype
        ,q.ctype,q.qstem,q.qoption,q.qtypeid,q.qtypename,q.count
        from (select max(qcid)qcid,questionid from t_question_class where classid=#{question.classid} group by
        questionid)a
        left join t_question_class qc on a.qcid=qc.qcid
        left join t_paper p on qc.paperid=p.paperid
        left join t_question q on qc.questionid=q.questionid
        <if test="question.pointid!=null and question.pointid!='0'.toString() and question.pointid!=''">
            left join t_question_point qp on q.questionid = qp.questionid
        </if>
        where qc.qctype!=2 and qc.classid=#{question.classid}
        and UNIX_TIMESTAMP(FROM_UNIXTIME(qc.createtime,'%Y%m%d'))>=#{question.starttime}
        and UNIX_TIMESTAMP(FROM_UNIXTIME(qc.createtime,'%Y%m%d'))&lt;=#{question.stoptime}
        <if test="question.subjectid!=null and question.subjectid!='0'.toString() and question.subjectid!=''">
            and q.subjectid=#{question.subjectid}
        </if>
        <if test="question.pointid!=null and question.pointid!='0'.toString() and question.pointid!=''">
            and qp.pointid=#{question.pointid}
        </if>
        <if test="question.qtypeid!=null and question.qtypeid!='0'.toString() and question.qtypeid!=''">
            and qc.qtypeid=#{question.qtypeid}
        </if>
        order by
        <if test="question.errorrate!=null and question.errorrate!=0">
            qc.errorrate <if test="question.errorrate==1">desc</if>,
        </if>
        qc.qcid desc
    </select>
    <!--学校错误率%-->
    <select id="getErrorRateBySchoolId" resultType="java.lang.Integer">
        select errorrate from t_school where schoolid=#{schoolid}
    </select>
    <!--题型列表-->
    <select id="listQtype" resultType="com.xhkjedu.sstudy.vo.subjectbook.SubjectQuestiontypeVo">
        select st.qtypeid,st.qtypename,st.qtypeorder,st.ctype
        from t_question_class qc left join t_subject_questiontype st on qc.qtypeid=st.qtypeid
        where qc.classid=#{question.classid} and st.subjectid=#{question.subjectid}
        <if test="question.qctype!=null and (question.qctype==1 or question.qctype==3)">
            and qc.qctype!=2
        </if>
        group by st.qtypeid order by st.qtypeorder
    </select>
    <resultMap id="questionResult" type="com.xhkjedu.sstudy.vo.question.QuestionClassDetailVo">
        <result property="paperid" column="paperid"></result>
        <result property="questionid" column="questionid"></result>
        <result property="qscore" column="qscore"></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="complexity" column="complexity"></result>
        <result property="qctype" column="qctype"></result>
        <result property="avgscore" column="avgscore"></result>
        <result property="errorrate" column="errorrate"></result>
        <result property="ytjnum" column="ytjnum"></result>
        <result property="wtjnum" column="wtjnum"></result>
        <result property="pointname" column="pointname"></result>
        <collection property="stus" ofType="com.xhkjedu.sstudy.vo.question.AnswerStu">
            <result property="section" column="section"></result>
            <result property="stunum" column="stunum"></result>
            <result property="stuscore" column="stuscore"></result>
            <result property="stuname" column="stuname"></result>
        </collection>
        <collection property="rates" select="listRate" column="{questionid=questionid,classid=classid}">
        </collection>
    </resultMap>
    <select id="listRate" resultType="com.xhkjedu.sstudy.vo.question.AnswerErrorRate">
        select errorrate,FROM_UNIXTIME(createtime, '%m.%d')createtime
        from t_question_class where questionid=#{questionid} and classid=#{classid}
        order by createtime desc limit 5;
    </select>
    <!--获取班级下作业总人数-->
    <select id="getTotalnum" resultType="java.lang.Integer">
        select count(*) from t_paper_student
        where paperid=#{paperid} and classid=#{classid}
    </select>
    <!--客观题详情-->
    <select id="objectiveDetail" resultMap="questionResult">
        select qc.qscore,q.ctype,q.qstem,q.qoption,q.qanswer,q.qanalyze,q.complexity
        ,qc.qctype,qc.avgscore,qc.errorrate,qc.paperid,qc.classid,qc.questionid
        ,psq.useranswer section,count(psq.psqid)stunum,psq.stuscore,group_concat(u.username order by convert(u.username using gbk) separator '  ')stuname
        ,(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 (select max(qcid)qcid,questionid from t_question_class
        where questionid=#{question.questionid} and classid=#{question.classid} and qctype!=2)a
        left join t_question_class qc on a.qcid=qc.qcid
        left join t_question q on qc.questionid=q.questionid
        left join t_paper_student_question psq on qc.paperid=psq.paperid and qc.classid=psq.classid
        and qc.questionid=psq.questionid and (psq.answered=1 or psq.checked=1)
        left join t_user u on psq.studentid=u.userid
        group by psq.useranswer
    </select>
    <!--答题卡客观题详情-->
    <select id="objectiveSDetail" resultMap="questionResult">
        select ps.psanswer qanswer,qc.qscore,qc.qctype,qc.avgscore,qc.errorrate,qc.paperid,qc.classid,qc.questionid
        ,pss.useranswer section,count(pss.pssid)stunum,pss.stuscore,group_concat(u.username order by convert(u.username using gbk) separator '  ')stuname
        from (select max(qcid)qcid,questionid from t_question_class
        where questionid=#{question.questionid} and classid=#{question.classid} and qctype=2)a
        left join t_question_class qc on a.qcid=qc.qcid
        left join t_paper_scantron ps on qc.questionid=ps.pscaid
        left join t_paper_student_scantron pss on ps.pscaid=pss.pscaid and (pss.answered=1 or pss.checked=1)
        and pss.classid=#{question.classid}
        left join t_user u on pss.studentid=u.userid
        group by pss.useranswer
    </select>
    <!--考试客观题详情-->
    <select id="objectiveEDetail" resultMap="questionResult">
        select ps.psanswer qanswer,qc.qscore,qc.qctype,qc.avgscore,qc.errorrate,qc.paperid,qc.classid,qc.questionid
        ,pss.useranswer section,count(pss.pssid)stunum,pss.stuscore,group_concat(u.username order by convert(u.username using gbk) separator '  ')stuname
        from (select max(qcid)qcid,questionid from t_question_class
        where questionid=#{question.questionid} and classid=#{question.classid} and qctype=3)a
        left join t_question_class qc on a.qcid=qc.qcid
        left join t_classroom_paper_scantron ps on qc.questionid=ps.pscaid
        left join t_classroom_paper_student_scantron pss on ps.pscaid=pss.pscaid and (pss.answered=1 or pss.checked=1)
        and pss.classid=#{question.classid}
        left join t_user u on pss.studentid=u.userid
        group by pss.useranswer
    </select>
    <!--主观题详情-->
    <select id="subjectiveDetail" resultMap="questionResult">
        select qc.qscore,q.ctype,q.qstem,q.qoption,q.qanswer,q.qanalyze,q.complexity
        ,qc.qctype,qc.avgscore,qc.errorrate,qc.paperid,qc.classid,qc.questionid
        ,psq.stuscore,u.username stuname
        ,(select count(*) from t_paper_student ps where ps.paperid=qc.paperid and ps.classid=qc.classid)wtjnum
        ,(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 (select max(qcid)qcid,questionid from t_question_class
        where questionid=#{question.questionid} and classid=#{question.classid} and qctype!=2)a
        left join t_question_class qc on a.qcid=qc.qcid
        left join t_question q on qc.questionid=q.questionid
        left join t_paper_student_question psq on qc.paperid=psq.paperid and qc.classid=psq.classid
        and qc.questionid=psq.questionid and (psq.answered=1 or psq.checked=1)
        left join t_user u on psq.studentid=u.userid
        order by convert(u.username using gbk)
    </select>
    <!--答题卡主观题详情-->
    <select id="subjectiveSDetail" resultMap="questionResult">
        select ps.psanswer qanswer,qc.qscore,qc.qctype,qc.avgscore,qc.errorrate,qc.paperid,qc.classid,qc.questionid
        ,pss.stuscore,u.username stuname
        ,(select count(*) from t_paper_student ps where ps.paperid=qc.paperid and ps.classid=qc.classid)wtjnum
        from (select max(qcid)qcid,questionid from t_question_class
        where questionid=#{question.questionid} and classid=#{question.classid} and qctype=2)a
        left join t_question_class qc on a.qcid=qc.qcid
        left join t_paper_scantron ps on qc.questionid=ps.pscaid
        left join t_paper_student_scantron pss on ps.pscaid=pss.pscaid and (pss.answered=1 or pss.checked=1)
        and pss.classid=#{question.classid}
        left join t_user u on pss.studentid=u.userid
        order by convert(u.username using gbk)
    </select>
    <!--考试主观题详情-->
    <select id="subjectiveEDetail" resultMap="questionResult">
        select ps.psanswer qanswer,qc.qscore,qc.qctype,qc.avgscore,qc.errorrate,qc.paperid,qc.classid,qc.questionid
        ,pss.stuscore,u.username stuname
        ,(select count(*) from t_classroom_paper_student ps where ps.paperid=qc.paperid and ps.classid=qc.classid)wtjnum
        from (select max(qcid)qcid,questionid from t_question_class
        where questionid=#{question.questionid} and classid=#{question.classid} and qctype=3)a
        left join t_question_class qc on a.qcid=qc.qcid
        left join t_classroom_paper_scantron ps on qc.questionid=ps.pscaid
        left join t_classroom_paper_student_scantron pss on ps.pscaid=pss.pscaid and (pss.answered=1 or pss.checked=1)
        and pss.classid=#{question.classid}
        left join t_user u on pss.studentid=u.userid
        order by convert(u.username using gbk)
    </select>

    <!--获取试题知识点ids-->
    <select id="getPointIds" resultType="java.lang.String">
        select concat("'",group_concat(pointid separator "','"),"'") from t_question_point where questionid=#{questionid}
    </select>
    <!--举一反三试题列表(随机返回3条)-->
    <select id="listJYFSQuestion" resultType="java.util.Map">
        select q.questionid,q.qtypeid,q.qtypename,q.complexity,q.count,q.qstem,q.qoption,q.qanswer,q.qanalyze,q.ctype
        from t_question_point qp left join t_question q on qp.questionid=q.questionid
        where qp.pointid in(${pointIds}) and qp.questionid!=#{questionid} and q.qstate=1
        and (q.belong in(1,2) or (q.schoolid=#{schoolid} and q.belong=3) or (q.createid=#{userid} and q.belong=4))
        group by qp.questionid order by rand() limit 3
    </select>
    <!--获取试题是否被某人收藏-->
    <select id="getCollected" resultType="java.lang.Integer">
        select count(*) from t_question_collect qc where qc.questionid=#{questionid} and qc.userid=#{userid}
    </select>
    <!--获取试题关联知识点-->
    <select id="getPointName" resultType="java.lang.String">
        select group_concat(p.pointname)
        from t_question_point qp left join t_point p on qp.pointid=p.pointid
        where qp.questionid=#{questionid}
    </select>

    <!-- 根据作业id获取作业附件 -->
    <select id="listObjsByPaperid" resultType="java.util.Map">
        SELECT pfid,paperid,filepath,fileclass,fileorder,createtime
        FROM t_paper_file
        WHERE paperid=#{paperid} ORDER BY fileorder asc
    </select>
</mapper>
