<?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.qmth.teachcloud.report.business.mapper.TAExamCourseCollegeTeacherMapper">

    <select id="listClassRank"
            resultType="com.qmth.teachcloud.report.business.bean.result.TeacherClassRankResult">
        select * from(SELECT
            a.teacher_name teacherName,
            a.inspect_college_name inspectCollegeName,
            a.course_code courseCode,
            a.course_name courseName,
            a.teach_college_name teachCollegeName,
            round(a.avg_score, 1) avgScore,
            round(a.avg_score_assign, 1) avgScoreAssign,
            a.teacher_rank `rank`
        FROM
            t_a_exam_course_college_teacher a
                LEFT JOIN
            t_b_exam b ON a.exam_id = b.id
        <where>
            and b.school_id = #{schoolId}
            <if test="semesterId != null and semesterId != ''">
                and b.semester_id = #{semesterId}
            </if>
            <if test="examId != null">
                and a.exam_id = #{examId}
            </if>
            <if test="courseCode != null and courseCode != ''">
                and a.course_code = #{courseCode}
            </if>
            <if test="inspectCollegeId != null">
                and a.inspect_college_id = #{inspectCollegeId}
            </if>
            <if test="collegeIds != null and collegeIds != '' and collegeIds.size > 0">
                and a.teach_college_id in
                <foreach collection="collegeIds" item="collegeId" index="index" open="(" separator="," close=")">
                    #{collegeId}
                </foreach>
            </if>
        </where> ) t
            <if test="column != null and column != '' and order != null and order != ''">
                <choose>
                    <when test="column != null and column == 'inspectCollegeName'">
                        order by t.${column} ${order},t.rank ${order}
                    </when>
                    <otherwise>
                        order by t.${column} ${order}
                    </otherwise>
                </choose>
            </if>
    </select>

    <select id="listMyClassRank"
            resultType="com.qmth.teachcloud.report.business.bean.result.TeacherMyClassRankResult">
        select * from(SELECT
            a.teacher_name teacherName,
            a.inspect_college_name inspectCollegeName,
            a.course_code courseCode,
            a.course_name courseName,
            a.teach_college_name teachCollegeName,
            round(a.avg_score_assign, 1) avgScore,
            a.teacher_count teacherCount,
            a.teacher_rank `rank`,
            a.inspect_college_id as inspectCollegeId
        FROM
            t_a_exam_course_college_teacher a
                LEFT JOIN
            t_b_exam b ON a.exam_id = b.id
                LEFT JOIN
            t_b_exam_course c ON a.exam_id = c.exam_id and a.course_code = c.course_code
        <where>
            and b.school_id = #{schoolId}
            and a.teacher_id = #{teacherId}
            and c.publish_status = 'PUBLISH'
            <if test="semesterId != null and semesterId != ''">
                and b.semester_id = #{semesterId}
            </if>
            <if test="examId != null">
                and a.exam_id = #{examId}
            </if>
            <if test="courseCode != null and courseCode != ''">
                and a.course_code = #{courseCode}
            </if>
            <if test="inspectCollegeId != null">
                and a.inspect_college_id = #{inspectCollegeId}
            </if>
        </where> ) t
        <if test="column != null and column != '' and order != null and order != ''">
            <choose>
                <when test="column != null and column == 'inspectCollegeName'">
                    order by t.${column} ${order},t.rank ${order}
                </when>
                <otherwise>
                    order by t.${column} ${order}
                </otherwise>
            </choose>
        </if>
    </select>

    <select id="listAllTeacherClassRank"
            resultType="com.qmth.teachcloud.report.business.bean.result.TeacherClassRankResult">
        select * from(SELECT
        a.teach_college_name teachCollegeName,
        a.inspect_college_name inspectCollegeName,
        a.course_code courseCode,
        a.course_name courseName,
        a.teacher_name teacherName,
        round(a.avg_score_assign, 1) avgScoreAssign,
        a.teacher_rank `rank`,
        round(a.avg_score, 1) avgScore
        FROM
        t_a_exam_course_college_teacher a
        LEFT JOIN
        t_b_exam b ON a.exam_id = b.id
        join t_b_exam_course tbec on
            tbec.exam_id = a.exam_id and
            tbec.course_code = a.course_code
        <where>
            and b.school_id = #{schoolId}
            <if test="semesterId != null and semesterId != ''">
                and b.semester_id = #{semesterId}
            </if>
            <if test="examId != null">
                and a.exam_id = #{examId}
            </if>
            <if test="inspectCollegeId != null">
                and a.inspect_college_id = #{inspectCollegeId}
            </if>
            <if test="courseCode != null and courseCode != ''">
                and a.course_code = #{courseCode}
            </if>
            <if test="teacherId != null">
                and a.teacher_id = #{teacherId}
            </if>
            <if test="collegeIds != null and collegeIds != '' and collegeIds.size > 0">
                and a.teach_college_id in
                <foreach collection="collegeIds" item="collegeId" index="index" open="(" separator="," close=")">
                    #{collegeId}
                </foreach>
            </if>
                and tbec.publish_status = 'PUBLISH'
        </where> ) t
            <if test="column != null and column != '' and order != null and order != ''">
                <choose>
                    <when test="column != null and column == 'inspectCollegeName'">
                        order by t.${column} ${order},t.rank ${order}
                    </when>
                    <otherwise>
                        order by t.${column} ${order}
                    </otherwise>
                </choose>
            </if>
    </select>

    <select id="findTeacherInfo" resultType="com.qmth.teachcloud.report.business.bean.result.TAExamCourseTeacherResult">
        SELECT
            teacher_name AS teacherName,
            reality_count AS colRealityCount,
            total_count AS colTotalCount,
            absent_count AS colAbsentCount,
            ROUND(CONVERT( min_score_assign , DECIMAL (10 , 4 )),2) AS colMinScore,
            ROUND(CONVERT( max_score_assign , DECIMAL (10 , 4 )),2) AS colMaxScore,
            ROUND(CONVERT( avg_score_assign , DECIMAL (10 , 4 )),2) AS colAvgScore,
            ROUND(CONVERT( upper_quartile , DECIMAL (10 , 4 )),2) AS colUpperQuartile,
            ROUND(CONVERT( median , DECIMAL (10 , 4 )), 2) AS colMedian,
            ROUND(CONVERT( lower_quartile , DECIMAL (10 , 4 )),2) AS colLowerQuartile,
            mode AS colMode,
            ROUND(CONVERT( standard_deviation , DECIMAL (10 , 4 )),2) AS colStandardDeviation,
            ROUND(CONVERT( relative_position , DECIMAL (10 , 4 )),2) AS colRelativePosition,
            other_college_reality_count AS schRealityCount,
            other_college_total_count AS schTotalCount,
            other_college_absent_count AS schAbsentCount,
            ROUND(CONVERT( other_college_min_score_assign , DECIMAL (10 , 4 )),2) AS schMinScore,
            ROUND(CONVERT( other_college_max_score_assign , DECIMAL (10 , 4 )),2) AS schMaxScore,
            ROUND(CONVERT( other_college_avg_score_assign , DECIMAL (10 , 4 )),2) AS schAvgScore,
            ROUND(CONVERT( other_college_upper_quartile , DECIMAL (10 , 4 )),2) AS schUpperQuartile,
            ROUND(CONVERT( other_college_median , DECIMAL (10 , 4 )),2) AS schMedian,
            ROUND(CONVERT( other_college_lower_quartile , DECIMAL (10 , 4 )),2) AS schLowerQuartile,
            other_college_mode AS schMode
        FROM
            t_a_exam_course_college_teacher
        <where>
            <if test="examId != null and examId != ''">
                and exam_id = #{examId}
            </if>
            <if test="courseCode != null and courseCode != ''">
                and course_code = #{courseCode}
            </if>
            <if test="collegeId != null and collegeId != ''">
                and inspect_college_id = #{collegeId}
            </if>
        </where>
    </select>

    <select id="listTeacherClassRank"
            resultType="com.qmth.teachcloud.report.business.bean.result.TeacherClassRankResult">
        select * from(SELECT
        a.inspect_college_name inspectCollegeName,
        a.teacher_name teacherName,
        round(a.avg_score_assign, 1) avgScoreAssign,
        a.teacher_rank `rank`
        FROM
        t_a_exam_course_college_teacher a
        LEFT JOIN
        t_b_exam b ON a.exam_id = b.id
        join t_b_exam_course tbec on
        tbec.exam_id = a.exam_id and
        tbec.course_code = a.course_code
        <where>
            and b.school_id = #{schoolId}
            <if test="semesterId != null and semesterId != ''">
                and b.semester_id = #{semesterId}
            </if>
            <if test="examId != null">
                and a.exam_id = #{examId}
            </if>
            <if test="courseCode != null and courseCode != ''">
                and a.course_code = #{courseCode}
            </if>
                and tbec.publish_status = 'PUBLISH'
        </where> ) t
        <if test="column != null and column != '' and order != null and order != ''">
            <choose>
                <when test="column != null and column == 'inspectCollegeName'">
                    order by t.${column} ${order},t.rank ${order}
                </when>
                <otherwise>
                    order by t.${column} ${order}
                </otherwise>
            </choose>
        </if>
    </select>

    <select id="teacherView" resultType="com.qmth.teachcloud.report.business.bean.result.SurveyTeacherViewResult">

    </select>
</mapper>
