<?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.score.ScoreMapper">
    <insert id="insertOrUpdateScore" parameterType="rd.data.db.table.Score">
        insert into score ("id", "student", "usual_score", "exam_score", "exam_ratio", "course", "term")
        values (#{score.id}, #{score.student}, #{score.usualScore}, #{score.examScore}, #{score.examRatio}, #{score.course}, #{score.term})
        on conflict (student, course, term)
            do update set (usual_score, exam_score, exam_ratio) = (excluded.usual_score, excluded.exam_score, excluded.exam_ratio)
    </insert>

<!--    <insert id="insertOrUpdateScoreBatch">-->
<!--        insert into score ("id", "student", "usual_score", "exam_score", "course_schedule")-->
<!--        values-->
<!--        <foreach collection="score" item="scores" separator=",">-->
<!--            (#{score.id}, #{score.student}, #{score.usualScore}, #{score.examScore}, #{score.schedule})-->
<!--        </foreach>-->
<!--        on conflict (id, student, course_schedule) do update set (usual_score,exam_score) = (excluded.usual_score, excluded.exam_score)-->
<!--    </insert>-->

    <select id="getStudentAllScore" resultType="rd.data.dto.score.StudentScore">
        SELECT s.id as id,
               crs.name as course_name,
               s.term as term,
               s.usual_score,
               s.exam_score,
               s.exam_ratio
        from score s
        inner join course crs on crs.id = s.course
        where s.student = #{student}
            <if test="term != null">
                and s.term = #{term}
            </if>
    </select>

    <select id="getTermCourseAllSelectedStudentScore" resultType="rd.data.dto.score.CourseScoreInfoDto">
        SELECT s.id,
               si.name           AS student_name,
               si.student_number AS student_number,
               s.exam_ratio,
               s.usual_score,
               s.exam_score
        FROM (SELECT DISTINCT csel.student
              FROM course_selection csel
                       INNER JOIN course_schedule csch ON csel.schedule = csch.id
              WHERE csch.course = #{course}
                and csel.term = #{term}) sel_stu
                 INNER JOIN student_info si ON si.id = sel_stu.student
                 LEFT JOIN score s ON s.student = sel_stu.student and s.term = #{term}
    </select>
</mapper>
