<?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.course.CourseSelectionMapper">
    <select id="getStudentSelectedSchedulesDetail" resultType="rd.data.db.CourseScheduleWithCourseIdName">
        SELECT csel.student,
               csel.create_time,
               csel.edit_time,
               csel.deleted,
               csel.activity,
               csch.id as scheduleId,
               csch.start_week,
               csch.end_week,
               csch.time,
               csch.academic_term,
               csch.place,
               csch.weekday,
               c.id   as courseId,
               c.name as courseName,
               c.exam_ratio,
               c.course_hours,
               c.spring_term
        FROM "course_selection" csel
                 INNER JOIN "course_schedule" csch ON csch.id = csel.schedule and csel.student = #{student}
                 INNER JOIN course C ON C.id = csch.course
        WHERE csel.deleted = FALSE
          AND csch.academic_term = #{term};
    </select>

    <select id="getStudentSelectedSchedules" resultType="rd.data.db.table.CourseSchedule">
        SELECT csch.*
        FROM "course_selection" csel
                 INNER JOIN "course_schedule" csch ON csch.ID = csel.schedule and csel.student = #{student}
                 inner join course c on c.id = csch.course
        WHERE csel.deleted = FALSE
          AND csch.academic_term = #{term};
    </select>

    <select id="isStudentSelected" resultType="java.lang.Integer">
        SELECT COUNT(*)
        FROM course_selection csel
                 INNER JOIN course_schedule csch ON csch."id" = csel.schedule
            AND csch.course = #{course}
        WHERE csel.student = #{student}
          and csel.term = #{term}
    </select>

    <select id="getSelectedStudentList" resultType="rd.data.db.CourseScheduleSelectionInfo">
        SELECT si.id   as uid,
               si.student_number,
               si.name,
               si.major,
               si.grade,
               si.class as clazz,
               csch.id as schedule,
               csch.course,
               csch.start_week,
               csch.end_week,
               csch.time,
               csch.academic_term,
               csch.place,
               csch.weekday
        FROM course_selection csel
                 INNER JOIN course_schedule csch
                            ON csch.id = csel.schedule AND csch.academic_term = #{term} AND csch.course = #{course}
                 INNER JOIN student_info si ON si.id = csel.student;
    </select>

    <select id="getNotSelectStudentList" resultType="rd.data.db.table.StudentInfo">
        SELECT si.id   as uid,
               si.student_number,
               si.name,
               si.major,
               si.grade,
               si.class as clazz
        FROM student_info si
                 INNER JOIN (SELECT "at".major,
                                    "at".grade
                             FROM activity_target "at"
                             WHERE "at".activity IN (SELECT activity."id" FROM activity WHERE activity.term = #{term})
                               AND #{course} = ANY ( "at".course_list)
                ) target ON target.grade = si.grade
            AND target.major = si.major
        WHERE si."id" NOT IN (SELECT DISTINCT csel.student AS "id"
                              FROM course_selection csel
                                       INNER JOIN course_schedule csch ON csch."id" = csel.schedule
                                  AND csch.course = #{course}
                                  AND csch.academic_term = #{term}
        )
    </select>

</mapper>
