<?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.koobietech.eas.dao.mapper.ScheduleMapper">

    <resultMap id="scheduleResultMap" type="com.koobietech.eas.dao.pojo.SchedulePojo">
        <result property="id" column="id"/>
        <result property="week" column="week"/>
        <result property="startTime" column="start_time"/>
        <result property="endTime" column="end_time"/>
        <result property="roomId" column="room_id"/>
        <result property="classId" column="class_id"/>
        <result property="assistantId" column="assistant_id"/>
        <result property="teacherId" column="teacher_id"/>
        <result property="categoryId" column="category_id"/>
        <result property="subjectsId" column="subjects_id"/>
        <result property="createTime" column="create_time"/>
        <result property="modifyTime" column="modify_time"/>
        <result property="createUid" column="create_uid"/>
        <result property="disabled" column="disabled"/>
        <result property="assistantName" column="assistantName" />
        <result property="teacherName" column="teacherName" />
        <result property="category" column="category" />
        <result property="subjects" column="subjects" />
        <result property="className" column="className" />
        <result property="classroom" column="classroomName" />
    </resultMap>

    <select id="getSchedules" parameterType="com.koobietech.eas.dao.dto.ScheduleDto"
        resultMap="scheduleResultMap"  >
        SELECT
            s.id,
            s.`week`,
            s.start_time,
            s.end_time,
            s.room_id,
            s.class_id,
            s.assistant_id,
            s.teacher_id,
            s.category_id,
            s.subjects_id,
            s.create_time,
            s.modify_time,
            s.create_uid,
            s.disabled,
            uu.relname AS assistantName,
            u.relname AS teacherName,
            c.`name` AS category,
            su.`name` AS subjects ,
            cl.`name` AS classroomName,
            cc.`name` AS className
        FROM
            eas_edu_schedule AS s
            LEFT JOIN eas_sys_userinfo AS u ON u.id = s.teacher_id
            LEFT JOIN eas_sys_userinfo AS uu ON uu.id = s.assistant_id
            LEFT JOIN eas_edu_category AS c ON c.id = s.category_id
            LEFT JOIN eas_edu_subjects AS su ON su.id = s.subjects_id
            LEFT JOIN eas_edu_classroom AS cl ON cl.id = s.room_id
            LEFT JOIN eas_edu_class AS cc ON cc.id = s.class_id
        WHERE s.disabled = 'N'
        <if test="week != null and week != ''">
            AND s.week = #{week}
        </if>
        <if test="startTime != null and startTime != ''">
            AND s.start_time &gt;= #{startTime,jdbcType=TIMESTAMP}
        </if>
        <if test="endTime != null and endTime != ''">
            AND s.end_time &lt;= #{endTime,jdbcType=TIMESTAMP}
        </if>
        <if test="roomId != null and roomId != 0">
            AND s.room_id = #{roomId}
        </if>
        <if test="classId != null and classId != 0">
            AND s.class_id = #{classId}
        </if>
        <if test="teacherId != null and teacherId != 0">
            AND s.teacher_id = #{teacherId}
        </if>
        <if test="assistantId != null and assistantId != 0">
            AND s.assistant_id = #{assistantId}
        </if>
        <if test="categoryId != null and categoryId != 0">
            AND s.category_id = #{categoryId}
        </if>
        <if test="subjectId != null and subjectId != 0">
            AND s.subjects_id = #{subjectId}
        </if>
        <if test="studentId != null and studentId != 0">
            AND s.class_id IN ( SELECT class_id FROM eas_edu_clt_relation WHERE student_id = #{studentId} )
        </if>
    </select>
</mapper>