<?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,
                DATE_FORMAT(b.start_time, '%H:%i') AS start_time,
                b.end_time,
                b.source,
                b.status,
                b.memo,
                b.class_theme,
                b.cla_color,
                b.font_size,
                b.font_color,
                course.course_name,
                course.star,
                s.user_id as teacher_id,
                s.teacher_name as staff_name,
                (select store.store_name from yj_store store where  b.dept_id=store.dept_id )as store_name,
                r.room_id,
                r.room_name
        from sc_cla_time b
            left join sc_course course on b.course_id=course.course_id
            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 1=1
        <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="deptId != null">
            and b.dept_id=#{deptId}
        </if>
        <if test="teacherId != null">
            and b.teacher_id=#{teacherId}
        </if>
        <if test="courseId != null">
            and b.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,
            a.star,
            c.room_name,
            d.teacher_name as staff_Name,
            d.avatar_img as pic,
            c.cla_color,
            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_cla_time c,
            sys_teacher d,
            yj_store e

        WHERE  a.course_id=c.course_id
          and c.teacher_id=d.user_id
          and c.dept_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>
        order by c.cla_date,c.start_time
    </select>

    <select id="getCourseDetail" resultType="com.ruoyi.course.domain.time.ClaTimeCalendarItem">

        SELECT
        c.course_time_id,
        a.course_name,
        (select  course_type from sc_course_type where course_type_id=a.course_type_id)as course_type_name,
        a.star,
        c.room_name,
        d.teacher_name as staff_Name,
        d.avatar_img as pic,
        c.cla_color,
        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,
        a.remark

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

        WHERE a.course_id=c.course_id
        and c.teacher_id=d.user_id
        and c.dept_id=e.dept_id
        <if test="courseTimeId != null  ">
            and c.course_time_id =#{courseTimeId}
        </if>
        order by c.cla_date,c.start_time
    </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,
                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,
                       sys_user b,
                       sc_cla_time c,
                       sys_user d
        WHERE a.dept_id=b.dept_id  and c.teacher_id=d.user_id
          and b.user_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 cour
                                     WHERE cour.course_id=a.course_id ) as course_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 a.course_id=cour.course_id ) as course_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=1
          and a.dept_id=#{deptId}
          and a.cla_date BETWEEN #{beginTime} and  #{endTime}
          ORDER BY FIELD(b.book_status, 1) desc,b.create_time
    </select>
</mapper>
