<?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="rd.dao.mapper.schedule.CourseScheduleMapper">

    <select id="getScheduleDetailFromCourseId" resultType="Object">
        select <include refid="scheduleDetailSelectColumns"/>
        from course c
                 left join (course_teacher tc inner join teacher_info ti
                            on tc.teacher = ti.id and tc.course = #{course}) on tc.course = c.id
                 inner join course_schedule cs on c.id = cs.course and c.id = #{course}
        group by (
                  c.id, c."name", c.course_hours, c.spring_term, c.target_major, c.target_grade,
                  cs.id, cs.start_week, cs.end_week, cs.time, cs.academic_term, cs.place, cs.ratio
        )
    </select>

    <select id="getScheduleDetailFromScheduleId" resultType="Object">
        select <include refid="scheduleDetailSelectColumns"/>
        from course_schedule cs
                 inner join course c on c.id = cs.course and cs.id = #{courseSchedule}
                 left join (course_teacher tc inner join teacher_info ti on tc.teacher = ti.id) on tc.course = c.id
        group by (
                  c.id, c."name", c.course_hours, c.spring_term, c.target_major, c.target_grade,
                  cs.id, cs.start_week, cs.end_week, cs.time, cs.academic_term, cs.place, cs.ratio
        )
    </select>

    <sql id="scheduleDetailSelectColumns">
        c.id course_id, c."name" course_name, array_agg(ti."name") teachers, c.course_hours, c.spring_term, c.target_major, c.target_grade,
        cs.id schedule_id, cs.start_week, cs.end_week, cs.time, cs.academic_term, cs.place, cs.ratio
    </sql>

    <select id="getScheduleTotalStudent" resultType="Integer">
        select count(student)
        from course_selection
        where course_selection.schedule = #{courseSchedule}
    </select>

    <select id="getSelectedStudent" resultType="rd.data.db.table.StudentInfo">
        select si."name", si.student_number
        from course_selection csele
                 inner join student_info si on si.id = csele.student and csele.schedule = #{courseSchedule}
    </select>

    <select id="getCourseScheduleDetail" resultType="java.lang.Object">
        SELECT
            csch.id as schedule, csch.start_week, csch.end_week, csch.time, csch.academic_term, csch.place, csch.ratio, csch.distribution, csch.name, csch.weekday, c.id as course,
            c.name as courseName, c.course_hours, c.spring_term
        FROM
            "course_schedule" csch
                INNER JOIN course c ON c.id = csch.course
        WHERE
            csch.ic = #{schedule}
    </select>

</mapper>
