<?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.ruoyi.course.mapper.ScClaTimeMapper">

    <select id="selectListForCalendar" resultType="com.ruoyi.course.domain.res.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.class_theme,
                a.cla_name,
                a.cla_color,
                course.course_name,
                s.user_id as teacher_id,
                s.teacher_name as staff_name,
                r.room_id,
                r.room_name
        from sc_course_cla a
        left join sc_course course on a.course_id=course.course_id
        ,sc_cla_time b
        left join sys_teacher s on b.teacher_id = s.user_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="deptId != null">
            and a.depart_id=#{deptId}
        </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="selectListForCalendar_old" resultType="com.ruoyi.course.domain.res.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.user_id as teacher_id,s.teacher_name as 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_teacher s on b.teacher_id = s.user_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="getCourseList" resultType="com.ruoyi.course.domain.time.ClaTimeCalendarItem">
        SELECT
            c.course_time_id,
            a.course_name,
            c.room_name,
            d.teacher_name as staff_Name,
            b.cla_name,
            b.cla_color,
            b.cla_pic,
            c.cla_date,
            c.start_time,
            c.end_time,
            c.book_attend_cnt,
            c.at_class_cnt,
            c.status as cla_time_status,
            c.room_name,
            c.book_attend_cnt,
            c.at_class_cnt,
            c.less_cnt,
            e.store_name

        from
            sc_course a,
            sc_course_cla  b,
            sc_cla_time c,
            sys_teacher d,
            yj_store e

        WHERE  a.course_id=b.course_id
          and b.cla_id=c.cla_id
          and c.teacher_id=d.user_id
          and b.depart_id=e.dept_id
        <if test="date != null ">
            and c.cla_date =#{date}
        </if>
        <if test="courseTimeId != null  ">
            and c.course_time_id =#{courseTimeId}
        </if>
        <if test="deptId != null  ">
            and e.dept_id =#{deptId}
        </if>

    </select>

    <select id="getCourseBookStudent" resultType="com.ruoyi.course.domain.res.RespStuBook">
        SELECT
            b.student_id,
            b.student_name,
            b.sex,
            b.phone,
            b.sex,
            a.id as book_id,
            a.book_status,
            a.check_in,
            a.create_time,
            a.check_in_time
        from
            sc_book_course a,
            sc_student b
        WHERE   a.student_id=b.student_id
          and   a.course_time_id=#{courseTimeId}
            order by a.book_status,a.create_time

    </select>
    <select id="getCourseCountByStoreManager" resultType="com.ruoyi.course.domain.time.ClaTimeItem">
        SELECT
                d.nick_name as teacherName,
                b.cla_name,
                c.real_cla_date,
                c.real_start_time,
                c.real_end_time,
                c.room_name,
                c.room_usage_time,
                c.teacher_fee,
                c.real_attend_cnt,
                c.venue_fee
                from
                       sys_dept a,
                       sc_course_cla b,
                       sc_cla_time c,
                       sys_user d
        WHERE a.dept_id=b.depart_id and c.cla_id=b.cla_id and c.teacher_id=d.user_id
          and a.leader_id=#{staffId}
          and real_cla_date BETWEEN #{beginTime} and  #{endTime}
    </select>

    <select id="getStudentsBySaleStaff" resultType="com.ruoyi.course.domain.time.StudentOrderItem">
        SELECT
            c.student_id,
            c.student_name,
            c.birth_day,
            year(curdate())-year(c.birth_day) as age,
            c.sex,
            c.phone,
            b.card_no as course_name,
            b.charge_type,
            b.activation_date as begin_date,
            b.expiry_date as end_date,
            a.receipt_fee
--         from sc_order a,sc_order_detail b,sc_student c
        from sc_order a,sc_member_cards b,sc_student c
        WHERE a.student_id=c.student_id and a.card_no=b.card_no
          and a.sale_staff_id=#{staffId}
          order by a.create_time desc
    </select>

    <select id="myDealOrder" resultType="com.ruoyi.course.domain.res.RespOrderForApp">
        SELECT
            a.sale_staff_name,
            a.student_name,
            a.receipt_fee,
            a.commission_plans_id,
            a.card_no as course_name,
            a.order_type as detail_tag,
            if(b.charge_type='days','日','')as date_unit,
            1 as buy_count,
            a.create_time as create_time
        from sc_order a, sc_member_cards  b
        where  a.card_no=b.card_no
            and a.order_status='2'
            and a.sale_staff_id=#{staffId}
            and a.create_time BETWEEN #{beginTime} and  #{endTime}
            order by a.create_time desc
    </select>

    <select id="getOrdersForStore" resultType="com.ruoyi.course.domain.res.RespOrderForApp">
        SELECT
            a.sale_staff_name,
            a.student_name,
            a.receipt_fee,
            a.card_no as course_name,
            a.order_type as detail_tag,
            if(b.charge_type='days','日','')as date_unit,
            1 as buy_count,
            a.create_time

        from sc_order a,
             sc_member_cards  b
        where  a.card_no=b.card_no
            and a.order_status='2'
            and a.handle_dept_id=#{deptId}
            and a.create_time BETWEEN #{beginTime} and  #{endTime}
        ORDER BY a.create_time DESC
    </select>

    <select id="getBookListForTeacher" resultType="com.ruoyi.course.domain.res.RespBook">
        SELECT
            b.id as book_id,
            (select cour.course_name from sc_course_cla cla ,sc_course cour WHERE cla.course_id=cour.course_id and cla.cla_id=a.cla_id) as course_name,
            (select cla_name from sc_course_cla WHERE cla_id=a.cla_id) as cla_name,
            (select teacher_name from sys_teacher WHERE user_id=a.teacher_id)as teacher_name,
            a.room_name,
            a.cla_date,
            a.start_time,
            a.status,
            a.book_attend_cnt,
            a.at_class_cnt,
            a.less_cnt,
            b.book_status,
            b.create_time,
            c.student_id,
            c.student_name,
            c.sex,
            c.phone
        from sc_cla_time a,sc_book_course b,sc_student c
        WHERE a.course_time_id=b.course_time_id and b.student_id=c.student_id
--           and b.book_status=0
          and a.teacher_id=#{teacherId}
          and a.cla_date BETWEEN #{beginTime} and  #{endTime}
          ORDER BY FIELD(b.book_status, 0) desc, b.create_time
    </select>
    <select id="getBookListForManager" resultType="com.ruoyi.course.domain.res.RespBook">
        select
            b.id as book_id,
            (select cour.course_name from sc_course cour WHERE d.course_id=cour.course_id ) as course_name,
            d.cla_name,
            (select teacher_name from sys_teacher WHERE user_id=a.teacher_id)as teacher_name,
            a.room_name,
            a.cla_date,
            a.start_time,
            a.status,
            a.book_attend_cnt,
            a.at_class_cnt,
            a.less_cnt,
            b.book_status,
            b.create_time,
            c.student_id,
            c.student_name,
            c.sex,
            c.phone

        from sc_cla_time a,sc_book_course b,sc_student c,sc_course_cla d
        WHERE a.course_time_id=b.course_time_id and b.student_id=c.student_id and a.cla_id=d.cla_id
--           and b.book_status=1
          and d.depart_id=#{deptId}
          and a.cla_date BETWEEN #{beginTime} and  #{endTime}
          ORDER BY FIELD(b.book_status, 1) desc,b.create_time
    </select>
</mapper>
