<?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.sapi.mapper.research.ResearchUserMapper">
    <!-- 新增 -->
    <insert id="saveBatchResearchUser" parameterType="java.util.List">
        INSERT INTO t_research_user (researchid,userid,rustate,schoolid,createtime)
        VALUES
        <foreach collection ="list" item="ru" index= "index" separator =",">
            (#{ru.researchid},#{ru.userid},#{ru.rustate},#{ru.schoolid},UNIX_TIMESTAMP(NOW()))
        </foreach>
    </insert>

    <!-- 删除教研教师 教研id -->
    <delete id="delByResearchid">
        DELETE FROM t_research_user WHERE researchid=#{researchid}
    </delete>

    <!-- 教研id获取所有参与教研的用户id -->
    <select id="listRuserIdByResearchid" resultType="com.xhkjedu.sapi.model.research.TResearchUser">
        select ru.*,u.username createname,u.headpic
        from t_research_user ru left join t_user u on ru.userid = u.userid
        where ru.researchid=#{researchid} order by ru.reviewtime desc
    </select>

    <!-- 教研id获取素有教研信息 -->
    <select id="listRusersByReaearchid" resultType="com.xhkjedu.sapi.model.research.TResearchUser">
        SELECT ru.ruid,ru.userid,ru.content,ru.reviewtime,ru.createtime,u.username AS createname,u.headpic
        FROM t_research_user ru LEFT JOIN t_user u ON ru.userid=u.userid
        WHERE ru.researchid=#{researchid} AND ru.rustate=1 ORDER BY ru.reviewtime desc
    </select>

    <!-- 教师参与教研列表 -->
    <select id="listUserResearchs" resultType="com.xhkjedu.sapi.vo.research.ResearchVo">
         SELECT r.researchid,r.researchname,r.researchcover,r.duration,r.filesize,r.starttime,r.stoptime,r.mp4code,
        r.createid,r.createtime,u.username AS createname,r.readnum,r.researchpath,ru.rustate
        FROM t_research_user ru LEFT JOIN t_research r ON ru.researchid=r.researchid
        LEFT JOIN t_user u ON r.createid=u.userid
        WHERE ru.userid=#{userid} and r.starttime&lt;=UNIX_TIMESTAMP()
        <if test="rustate!=null"> and ru.rustate=#{rustate}</if>
        GROUP BY r.researchid ORDER BY ru.rustate
        <if test="rorder=='stoptime'">,r.stoptime DESC</if>
        <if test="rorder=='readnum'">,r.readnum DESC</if>
        <if test="rorder=='createtime'">,r.createtime DESC</if>
    </select>
    <!--教师未评价教研数量-->
    <select id="noEvaluatedNum" resultType="java.lang.Integer">
        select count(*) from t_research_user ru left join t_research r on ru.researchid = r.researchid
        where ru.userid=#{userid} and ru.rustate=0 and r.starttime&lt;=UNIX_TIMESTAMP() and r.stoptime>=UNIX_TIMESTAMP()
    </select>

    <!-- 根据id获取教研详细信息 -->
    <select id="getResearchUserDetail" resultType="com.xhkjedu.sapi.vo.research.ResearchUserVo">
         SELECT r.researchid,r.researchname,r.researchcontent,r.researchpath,r.researchcover,r.duration,r.filesize,
        r.starttime,r.stoptime,r.readnum,r.mp4code,ru.rustate,ru.content,ru.reviewtime
	 FROM t_research_user ru LEFT JOIN t_research r ON ru.researchid=r.researchid
        WHERE r.researchid=#{researchid} AND ru.userid=#{userid} limit 1
    </select>

    <!-- 保存教师教研评价 -->
    <update id="updateUserResearch" parameterType="com.xhkjedu.sapi.model.research.TResearchUser">
        UPDATE t_research_user SET rustate=#{ru.rustate},content=#{ru.content},reviewtime=#{ru.reviewtime}
        WHERE researchid=#{ru.researchid} AND userid=#{ru.userid}
    </update>
    <!-- 其他教研活动列表 -->
    <select id="otherList" resultType="com.xhkjedu.sapi.vo.research.ResearchVo">
        SELECT r.researchid,r.researchname,r.researchcover,r.duration,r.readnum
        ,ru.rustate,r.starttime,r.stoptime,r.createtime
        ,(select u.username from t_user u where u.userid=r.createid)createname
        FROM t_research_user ru LEFT JOIN t_research r ON ru.researchid=r.researchid
        WHERE ru.userid=#{research.userid} AND r.researchid!=#{research.researchid} and r.starttime&lt;=UNIX_TIMESTAMP()
        ORDER BY r.createtime DESC
    </select>
</mapper>
