<?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="cn.xluobo.business.sc.course.repo.mapper.ScClaTimeMapper">

    <select id="selectListForCalendar" resultType="cn.xluobo.business.sc.course.domain.resp.time.RespClaTimeCalendar">
        select b.course_time_id,b.cla_id,b.cla_date,WEEKDAY(b.cla_date) + 1 as week_day,
               hour(b.start_time) as start_hour,b.start_time,
               b.end_time,b.source,b.status,b.memo,b.status,b.class_theme,
               a.cla_name,a.cla_color,course.course_name,
               s.staff_id as teacher_id,s.staff_name,
               r.room_id,r.room_name,
               (select count(distinct student_id) from sc_student_course sc where sc.cla_id=a.cla_id and sc.status='1') as student_count,
               b.pay_hour
        from sc_course_cla a
            left join sc_course course on a.course_id=course.course_id
           ,sc_cla_time b
               left join sys_staff s on b.teacher_id = s.staff_id
               left join sc_room r on b.room_id = r.room_id
        where a.cla_id=b.cla_id
        <if test="courseTimeId != null">
            and b.course_time_id=#{courseTimeId}
        </if>
        <if test="beginDate != null and beginDate != '' and endDate != null and endDate != ''">
            and b.cla_date between #{beginDate} and #{endDate}
        </if>
        <if test="claId != null">
            and a.cla_id=#{claId}
        </if>
        <if test="studentId != null">
            and exists(select 1 from sc_student_course sc where a.cla_id=sc.cla_id and sc.student_id=#{studentId})
        </if>
        <if test="deptId != null">
            and a.depart_id=#{deptId}
        </if>
        <if test="deptId == null and userId != null and userId != ''">
            and exists(select 1 from sys_user_dept ud
            where ud.user_id=#{userId}
            and (ud.dept_id=a.depart_id or ud.dept_id=-1))
        </if>
        <if test="teacherId != null">
            and b.teacher_id=#{teacherId}
        </if>
        <if test="courseId != null">
            and a.course_id = #{courseId}
        </if>
        <if test="attended != null and attended == false">
            and b.status = '1'
        </if>
        <if test="attended != null and attended == true">
            and b.status = '2'
        </if>
        order by b.cla_date,b.status,b.start_time
    </select>
    <select id="selectListForAttend" resultType="cn.xluobo.business.sc.course.domain.resp.time.RespClaTime">
        select ct.course_time_id,ct.cla_id,cc.cla_name,
               course.course_id,course.course_name,
               ct.teacher_id,s.staff_name,
               ct.status,
               ct.cla_date,ct.start_time,ct.end_time,
               ct.real_cla_date,ct.real_start_time,ct.real_end_time,
               ct.book_attend_cnt,ct.real_attend_cnt,ct.out_cnt,
               ct.pay_hour,ct.pay_total_hour,
               ct.pay_total_fee,
               ct.room_name,ct.class_theme,ct.memo,
               (select u.user_name from sys_user u where u.user_id=ct.last_update_user) as last_update_user_name,
               ct.create_time,ct.last_update_time
        from sc_course_cla cc
                 left join sc_course course on cc.course_id=course.course_id,
             sc_cla_time ct
                 left join sys_staff s on ct.teacher_id=s.staff_id
        where cc.cla_id = ct.cla_id
        <if test="searchClaTime.beginDate != null and searchClaTime.beginDate != '' and searchClaTime.endDate != null and searchClaTime.endDate != ''">
            and ct.cla_date between #{searchClaTime.beginDate} and #{searchClaTime.endDate}
        </if>
        <if test="searchClaTime.claId != null">
            and ct.cla_id=#{searchClaTime.claId}
        </if>
        <if test="searchClaTime.deptId != null">
            and cc.depart_id=#{searchClaTime.deptId}
        </if>
        <if test="searchClaTime.deptId == null and searchClaTime.userId != null and searchClaTime.userId != ''">
            and exists(select 1 from sys_user_dept ud
            where ud.user_id=#{searchClaTime.userId}
            and (ud.dept_id=cc.depart_id or ud.dept_id=-1))
        </if>
        <if test="searchClaTime.teacherId != null">
            and ct.teacher_id=#{searchClaTime.teacherId}
        </if>
        <if test="searchClaTime.courseId != null">
            and cc.course_id = #{searchClaTime.courseId}
        </if>
        <if test="searchClaTime.attended != null and searchClaTime.attended == false">
            and ct.status = '1'
        </if>
        <if test="searchClaTime.attended != null and searchClaTime.attended == true">
            and ct.status = '2'
        </if>
        <choose>
            <when test='searchClaTime.orderByType != null and searchClaTime.orderByType == "claTimeAttend"'>
                order by ct.status,ct.real_cla_date desc,ct.cla_date desc,ct.start_time
            </when>
            <otherwise>
                order by ct.real_cla_date desc,ct.last_update_time desc
            </otherwise>
        </choose>
    </select>
    <select id="selectClaTimeCount" resultType="java.lang.Integer">
        select count(1) from sc_course_cla b, sc_cla_time a
        where a.cla_id=b.cla_id
        and a.cla_date between #{beginDate} and #{endDate}
        <if test="hadBegin != null and hadBegin == true">
            and a.status='2'
        </if>
        <if test="hadBegin != null and hadBegin == false">
            and a.status='1'
        </if>
        <if test="teacherId != null">
            and a.teacher_id=#{teacherId}
        </if>
        <if test="deptId != null">
            and b.depart_id=#{deptId}
        </if>
        <if test="deptId == null and userId != null and userId != ''">
            and exists(select 1 from sys_user_dept ud
                where ud.user_id=#{userId}
                  and (ud.dept_id=b.depart_id or ud.dept_id=-1))
        </if>
    </select>
</mapper>
