<?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.sexam.mapper.exam.EClassMapper">
    <!--获取班级基本信息列表-->
    <select id="listByClassIds" resultType="com.xhkjedu.sexam.model.exam.EClass">
        select classid,classname,classnum from t_class
        where classid in
        <foreach collection="classids" item="classid" index="index" open="(" separator="," close=")">
            ${classid}
        </foreach>
    </select>
    <!--考试班级-->
    <select id="listByExamId" resultType="java.util.Map">
        select classid,classname,classnum from e_class where examid=#{examid}
    </select>
    <!--更新考试班级人数-->
    <update id="updateClassNumByCsId">
        update e_class c set c.classnum=(select count(*) from e_class_student s where s.examid=c.examid and s.classid=c.classid)
        where c.examid=#{examid} and c.classid=#{classid}
    </update>

    <!--获取班级教师-->
    <select id="listTeacherByClassIds" resultType="com.xhkjedu.sexam.vo.exam.CTeacherVo">
        select ct.classid,ct.teacherid from t_class_teacher ct where ct.classid in(${classids})
    </select>
    <!--获取教师科目-->
    <select id="listSubjectByTeacherIds" resultType="com.xhkjedu.sexam.vo.exam.CTeacherVo">
        select tp.teacherid,sb.subjectid
        from t_teacher_powersb tp left join t_subject_book sb on tp.lsbid = sb.lsbid
        where tp.teacherid in(${teacherids})
        group by tp.teacherid
    </select>
    <!--获取科目列表-->
    <select id="listSubjectBySubjectIds" resultType="com.xhkjedu.sexam.vo.exam.CTeacherVo">
        select subjectid,subjectname from t_subject where subjectid in('${subjectids}')
    </select>
    <!--获取学校所有教师-->
    <select id="listTeacherBySchoolId" resultType="com.xhkjedu.sexam.vo.exam.CTeacherVo">
        select userid teacherid,username,loginname,usersex from t_user where schoolid=#{schoolid} and usertype=1 and userstate=1
    </select>
    <!--班级教师查找-->
    <select id="listSearchTeacher" resultType="java.util.Map">
        select ct.teacherid,u.username,u.loginname,u.usersex,sb.subjectid
        from t_class_teacher ct left join t_user u on ct.teacherid = u.userid
        left join t_teacher_powersb tp on tp.teacherid=ct.teacherid
        left join t_subject_book sb on tp.lsbid = sb.lsbid
        where ct.classid=#{classid} and u.usertype=1 and u.userstate=1 and u.username in
        <foreach collection="usernames" item="username" open="(" separator="," close=")">
            #{username}
        </foreach>
        group by ct.teacherid
    </select>

    <!--按班设置教师列表-->
    <select id="listClassTeacher" resultType="java.util.Map">
        select ct.classid,ct.teacherid,u.username,u.loginname,u.usersex
        from t_class_teacher ct left join t_user u on ct.teacherid = u.userid
        left join t_teacher_powersb tp on tp.teacherid=ct.teacherid
        left join t_subject_book sb on tp.lsbid = sb.lsbid
        where ct.classid in(${classids}) and sb.subjectid=#{subjectid} and u.userstate=1
        group by ct.classid,ct.teacherid
    </select>
</mapper>
