<?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.QuestionMapper">
    <!--批量更新试题信息-->
    <update id="updateBatch" parameterType="java.util.List">
        UPDATE t_question
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="qstem = CASE " suffix="END,">
                <foreach collection="list" index="index" item="item" >
                    when questionid = #{item.questionid} then #{item.qstem}
                </foreach>
            </trim>
            <trim prefix="qstemtxt = CASE " suffix="END,">
                <foreach collection="list" index="index" item="item" >
                    when questionid = #{item.questionid} then #{item.qstemtxt}
                </foreach>
            </trim>
            <trim prefix="qoption = CASE " suffix="END,">
                <foreach collection="list" index="index" item="item" >
                    when questionid = #{item.questionid} then #{item.qoption}
                </foreach>
            </trim>
            <trim prefix="qanswer = CASE " suffix="END,">
                <foreach collection="list" index="index" item="item" >
                    when questionid = #{item.questionid} then #{item.qanswer}
                </foreach>
            </trim>
            <trim prefix="qanalyze = CASE " suffix="END,">
                <foreach collection="list" index="index" item="item" >
                    when questionid = #{item.questionid} then #{item.qanalyze}
                </foreach>
            </trim>
            <trim prefix="score = CASE " suffix="END,">
                <foreach collection="list" index="index" item="item" >
                    when questionid = #{item.questionid} then #{item.score}
                </foreach>
            </trim>
            <trim prefix="complexity = CASE " suffix="END,">
                <foreach collection="list" index="index" item="item" >
                    when questionid = #{item.questionid} then #{item.complexity}
                </foreach>
            </trim>
            <trim prefix="ctype = CASE " suffix="END,">
                <foreach collection="list" index="index" item="item" >
                    when questionid = #{item.questionid} then #{item.ctype}
                </foreach>
            </trim>
        </trim>
        where
        <foreach collection="list" item="item" index="index" separator="or">
            questionid =#{item.questionid}
        </foreach>
    </update>
    <!--试题知识点列表-->
    <resultMap id="questionResult" type="com.xhkjedu.sstudy.vo.question.QuestionVo">
        <result property="questionid" column="questionid"></result>
        <result property="score" column="score"></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="qtypeid" column="qtypeid"></result>
        <result property="qtypename" column="qtypename"></result>
        <result property="createtime" column="createtime"></result>
        <result property="createname" column="createname"></result>
        <result property="count" column="count"></result>
        <result property="directorid" column="directorid"></result>
        <result property="directorname" column="directorname"></result>
        <result property="belong" column="belong"></result>
        <result property="createid" column="createid"></result>
        <result property="source" column="source"></result>
        <result property="year" column="year"></result>
        <result property="region" column="region"></result>
        <result property="schoolname" column="schoolname"></result>
        <result property="schoolid" column="schoolid"></result>
        <result property="subjectid" column="subjectid"></result>
        <result property="areaall" column="areaall"></result>
        <collection property="points" ofType="com.xhkjedu.sstudy.vo.subjectbook.PointVo" javaType="java.util.List"
                    select="listPoint" column="{questionid=questionid}">
        </collection>
        <collection property="directors" ofType="com.xhkjedu.sstudy.vo.subjectbook.DirectorVo" javaType="java.util.List">
            <result property="lsbid" column="lsbid"></result>
            <result property="directorid" column="directorid"></result>
            <result property="directorname" column="directorname"></result>
            <result property="level" column="level"></result>
            <result property="subjectid" column="subjectid"></result>
            <result property="subjectname" column="subjectname"></result>
            <result property="versionname" column="versionname"></result>
            <result property="lsbname" column="lsbname"></result>
        </collection>
    </resultMap>
    <resultMap id="pointResult" type="com.xhkjedu.sstudy.vo.subjectbook.PointVo">
        <result property="pointid" column="pointid"></result>
        <result property="pointname" column="pointname"></result>
    </resultMap>
    <select id="listPoint" resultMap="pointResult">
        select qp.pointid,p.pointname from t_question_point qp left join t_point p on qp.pointid = p.pointid
        where qp.questionid=#{questionid}
    </select>
    <select id="listQuestions" resultMap="questionResult">
        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,q.count,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 in
        <foreach item="question" collection="questions" open="(" separator="," close=")">
            #{question.questionid}
        </foreach>
    </select>
    <!--详情-->
    <select id="findById" resultMap="questionResult">
        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,q.source,q.year,q.region,q.schoolname,q.subjectid,q.schoolid
        ,qd.directorid,d.directorall directorname,d.lsbid,
        sb.level,sb.subjectid,s.subjectname,v.versionname,sb.lsbname
        ,(select a.areaall from t_areazone a where a.areacode=q.region)areaall
        from t_question q left join t_question_director qd on q.questionid=qd.questionid
        left join t_director d on qd.directorid = d.directorid
        left join t_subject_book sb on d.lsbid = sb.lsbid
        left join t_subject s on sb.subjectid = s.subjectid
        left join t_version v on sb.versionid = v.versionid
        where q.questionid=#{questionid}
    </select>
    <!--管理端试题列表-->
    <select id="findAll" resultType="com.xhkjedu.sstudy.vo.question.QuestionVo">
        select q.questionid,q.score,q.complexity,q.ctype,q.qstem,q.qoption,q.qanswer,q.qanalyze,q.belong,q.createid
        ,q.qtypeid,q.qtypename,q.createtime,q.count,q.qstate,q.source,q.year,q.region,q.schoolname
        from t_question q left join t_question_director qd on q.questionid=qd.questionid
        <if test="question.belong!=null and question.belong==5">
            left join t_question_collect qc on q.questionid = qc.questionid
        </if>
        <where>
        <if test="question.belong==0">
            and (q.belong in(1,2)
            <if test="question.schoolid!=null">
                or (q.belong=3 and q.schoolid=#{question.schoolid})
            </if>
            <if test="question.createid!=null">
                or (q.belong=4 and q.createid=#{question.createid})
            </if>
            )
        </if>
        <if test="question.belong>=3 and question.belong&lt;=4">
            and q.belong=#{question.belong}
        </if>
        <if test="question.belong==2">
            and q.belong in(1,2)
        </if>
        <if test="question.belong==3">
            and q.schoolid=#{question.schoolid}
        </if>
        <if test="question.subjectid!=null and question.subjectid!='0'.toString() and question.subjectid!=''">
            and q.subjectid=#{question.subjectid}
        </if>
        <if test="question.qstate!=null and question.qstate!=0">
            and q.qstate=#{question.qstate}
        </if>
        <if test="question.qstate==null or question.qstate==0">
            and q.qstate!=10
        </if>
        <if test="question.lsbid!=null and question.lsbid!='0'.toString() and question.lsbid!=''">
            and qd.lsbid=#{question.lsbid}
        </if>
        <if test="question.directorid!=null and question.directorid!='0'.toString() and question.directorid!=''">
            and qd.directorid=#{question.directorid}
        </if>
        <if test="question.qtypeid!=null and question.qtypeid!='0'.toString() and question.qtypeid!=''">
            and q.qtypeid=#{question.qtypeid}
        </if>
        <if test="question.belong!=null and question.belong!=0">
            <if test="question.belong==4 or question.belong==6">
                and q.createid=#{question.createid}
            </if>
            <if test="question.belong==5">
                and qc.userid=#{question.createid}
            </if>
        </if>
        <if test="question.complexity!=null and question.complexity!=0">
            and q.complexity=#{question.complexity}
        </if>
        and q.qlevel &lt; 3
        </where>
        group by q.questionid order by q.qstate,q.createtime desc
    </select>
    <!--章节、知识点选题-->
    <select id="listForSelect" resultType="com.xhkjedu.sstudy.vo.question.SelectQuestionVo">
        select q.questionid,q.score,q.complexity,q.qstem,q.qoption,q.qanswer,q.qanalyze
        ,q.qtypeid,q.qtypename,q.count,GROUP_CONCAT(p.pointname)points,q.ctype,q.belong,q.createid
        ,q.source,q.year,q.region,q.schoolname,q.qlevel
        from t_question q left join t_question_point qp on q.questionid = qp.questionid
        left join t_point p on p.pointid=qp.pointid
        left join t_question_director qd on q.questionid=qd.questionid
        <if test="question.belong!=null and question.belong==5">
            left join t_question_collect qc on q.questionid = qc.questionid
        </if>
        where q.qstate=1
        <if test="question.lsbid!=null and question.lsbid!='0'.toString() and question.lsbid!=''">
            and qd.lsbid=#{question.lsbid}
        </if>
        <if test="question.directorid!=null and question.directorid!='0'.toString() and question.directorid!=''">
            and qd.directorid=#{question.directorid}
        </if>
        <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 q.qtypeid=#{question.qtypeid}
        </if>
        <if test="question.complexity!=null and question.complexity!=0">
            and q.complexity=#{question.complexity}
        </if>
        <if test="question.belong==0">
            and (q.belong in(1,2)
            <if test="question.schoolid!=null">
                or (q.belong=3 and q.schoolid=#{question.schoolid})
            </if>
            <if test="question.createid!=null">
                or (q.belong=4 and q.createid=#{question.createid})
            </if>
            )
        </if>
        <if test="question.belong>=3 and question.belong&lt;=4">
            and q.belong=#{question.belong}
        </if>
        <if test="question.belong==2">
            and q.belong in(1,2)
        </if>
        <if test="question.belong==3">
            and q.schoolid=#{question.schoolid}
        </if>
        <if test="question.belong!=null and question.belong!=0">
            <if test="question.belong==4 or question.belong==6">
                and q.createid=#{question.createid}
            </if>
            <if test="question.belong==5">
                and qc.userid=#{question.createid}
            </if>
        </if>
        and q.qlevel &lt; 3
        group by q.questionid order by q.createtime desc
    </select>
    <!--修改试题状态-->
    <update id="updateQuestionState">
        update t_question set qstate=#{qstate} where questionid in (${questionIds})
    </update>
    <!--更新单个试题状态-->
    <update id="updateState">
        update t_question set qstate=#{qstate} where questionid=#{questionid}
    </update>
    <!--更新组题次数-->
    <update id="updateCount">
        update t_question set count=count+1 where questionid=#{questionid}
    </update>

    <!--批量保存-->
    <insert id="saveBathQuestion" parameterType="com.xhkjedu.sstudy.model.question.TQuestion">
        INSERT INTO t_question (questionid,qstem,qstemtxt,qoption,qanswer,qanalyze,score,complexity,
                                ctype,hearfile,qtypeid,qtypename, subjectid,qlevel,snum,sorder,questionpid,
                                qstate,source,year,region,schoolname,createid,createtime,belong,schoolid)
        VALUES
        <foreach collection ="list" item="p" index= "index" separator =",">
            (#{p.questionid},#{p.qstem},#{p.qstemtxt},#{p.qoption},#{p.qanswer},#{p.qanalyze},#{p.score},#{p.complexity},
             #{p.ctype},#{p.hearfile},#{p.qtypeid},#{p.qtypename},#{p.subjectid},#{p.qlevel},#{p.snum},#{p.sorder},#{p.questionpid},
             #{p.qstate},#{p.source},#{p.year},#{p.region},#{p.schoolname},#{p.createid},#{p.createtime},#{p.belong},#{p.schoolid})
        </foreach>
    </insert>
</mapper>
