<?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.yonge.cooleshow.biz.dal.dao.CourseScheduleRecordDao">
    <select id="sumCourseTime" resultType="com.yonge.cooleshow.biz.dal.entity.CourseScheduleRecord">
        SELECT
            c.id_ AS courseId,
            c.teacher_id_ AS teacherId,
            COUNT(1) AS studentCount,
            c.end_time_ AS endTime,
            c.single_course_time_ AS singleCourseTime
        FROM course_schedule c
        LEFT JOIN course_schedule_student_payment p ON c.id_ = p.course_id_
        WHERE c.type_ = 'PIANO_ROOM_CLASS'
        AND c.lock_ = 0
        AND c.status_ IN ( 'ING', 'NOT_START' )
        AND NOW() >= c.end_time_
        GROUP BY c.id_
    </select>
    <select id="countTimeByTeacherId" resultType="java.lang.Long">
        SELECT SUM(consum_time_) FROM course_schedule_piano WHERE teacher_id_=#{teacherId} AND  date_format(end_time_,'%Y-%m') = #{month}
    </select>
    <insert id="insertBatch">
        INSERT INTO course_schedule_piano (course_id_, teacher_id_, student_count_, consum_time_, end_time_) VALUES
        <foreach collection ="recordList" item="item" index="index" separator =",">
            (#{item.courseId},#{item.teacherId},#{item.studentCount}, #{item.consumTime}, #{item.endTime})
        </foreach>
    </insert>
    <select id="selectConsumeTimeList" resultType="com.yonge.cooleshow.biz.dal.vo.CourseScheduleRecordVo">
        SELECT
            s.id_ AS courseId,
            g.name_ AS groupName,
            g.subject_id_ AS subjectId,
            sb.name_ AS subjectName,
            s.status_ AS `status`,
            s.start_time_ AS startTime,
            s.end_time_ AS endTime,
            r.student_count_ AS studentCount,
            r.consum_time_ AS consumTime,
            s.single_course_time_ AS singleCourseTime
        FROM course_schedule s
        LEFT JOIN course_schedule_piano r ON r.course_id_ =s.id_
        LEFT JOIN course_group g ON s.course_group_id_=g.id_
        LEFT JOIN `subject` sb ON g.subject_id_ = sb.id_
        <where>
            <if test="param.type != null and param.type !=''">
                AND s.type_ = #{param.type}
            </if>
            <if test="param.status != null and param.status !=''">
                AND s.status_ = #{param.status}
            </if>
            <if test="param.teacherId != null">
               AND r.teacher_id_ = #{param.teacherId}
            </if>
            <if test="param.subjectId != null">
                AND g.subject_id_ = #{param.subjectId}
            </if>
            <if test="param.month != null and param.month !=''">
                AND date_format(s.end_time_,'%Y-%m') = #{param.month}
            </if>
            <if test="param.status != null and param.status !=''">
                AND s.status_ = #{param.status}
            </if>
        </where>
    </select>
    <select id="selectCourseList" resultType="com.yonge.cooleshow.biz.dal.vo.CourseScheduleRecordVo">
        SELECT
        s.id_ AS courseId,
        CONCAT(g.name_,'-第',s.class_num_,'课') AS groupName,
        g.subject_id_ AS subjectId,
        sb.name_ AS subjectName,
        s.status_ AS `status`,
        s.start_time_ AS startTime,
        s.end_time_ AS endTime,
        s.single_course_time_ AS singleCourseTime,
        a.count_ AS studentCount,
        (CASE WHEN s.status_ = 'NOT_START' THEN 0 ELSE ( a.count_ + 1 )*(a.count_)* s.single_course_time_ END) AS consumTime,
        i.id_ AS imGroupId
        FROM course_schedule s
        LEFT JOIN course_group g ON s.course_group_id_ = g.id_
        LEFT JOIN `subject` sb ON g.subject_id_ = sb.id_
        LEFT JOIN (SELECT course_id_,COUNT(1) AS count_ FROM course_schedule_student_payment WHERE course_type_='PIANO_ROOM_CLASS' GROUP BY course_id_) a ON s.id_=a.course_id_
        LEFT JOIN im_group i ON g.id_=i.course_group_id_
        <where>
            <if test="param.type != null and param.type !=''">
                AND s.type_ = #{param.type}
            </if>
            <if test="param.status != null and param.status !=''">
                AND s.status_ = #{param.status}
            </if>
            <if test="param.teacherId != null">
                AND s.teacher_id_ = #{param.teacherId}
            </if>
            <if test="param.subjectId != null">
                AND g.subject_id_ = #{param.subjectId}
            </if>
            <if test="param.month != null and param.month !=''">
                AND date_format(s.end_time_,'%Y-%m') = #{param.month}
            </if>
            <if test="param.status != null and param.status !=''">
                AND s.status_ = #{param.status}
            </if>
        </where>
        ORDER BY s.start_time_
    </select>
</mapper>