<?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.distributed.print.business.mapper.ExamStudentMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.qmth.distributed.print.business.entity.ExamStudent">
        <result column="id" property="id"/>
        <result column="school_id" property="schoolId"/>
        <result column="exam_detail_course_id" property="examDetailCourseId"/>
        <result column="student_name" property="studentName"/>
        <result column="student_code" property="studentCode"/>
        <result column="ticket_number" property="ticketNumber"/>
        <result column="site_number" property="siteNumber"/>
        <result column="print_paper" property="printPaper"/>
        <result column="print_card" property="printCard"/>
        <result column="extend_fields" property="extendFields"/>
        <result column="paper_type" property="paperType"/>
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id
        ,
        school_id, exam_detail_course_id, student_name, student_code, ticket_number, site_number, print_paper, print_card, extend_fields, paper_type
    </sql>
    <insert id="insertBatch">
        insert into exam_student (id, school_id, org_id, exam_id, exam_detail_course_id, student_name, student_code,paper_number,
        ticket_number, site_number, extend_fields, college_name, major_name, clazz_id, clazz_name, teach_clazz_id, teach_clazz_name, student_clazz_type, campus_name, create_id) values
        <foreach collection="examStudents" separator="," item="item">
            (#{item.id}, #{item.schoolId}, #{item.orgId}, #{item.examId}, #{item.examDetailCourseId}, #{item.studentName},
            #{item.studentCode},#{item.paperNumber}, #{item.ticketNumber}, #{item.siteNumber}, #{item.extendFields}, #{item.collegeName}, #{item.majorName}, #{item.clazzId},
            #{item.clazzName},#{item.teachClazzId},#{item.teachClazzName},#{item.studentClazzType},#{item.campusName}, #{item.createId})
        </foreach>
    </insert>
    <select id="getStudentDetail" resultType="java.util.Map">
        SELECT a.student_name    studentName,
               a.student_code    studentCode,
               a.ticket_number   ticketNumber,
               a.site_number     siteNumber,
               a.extend_fields   extendFields,
               b.course_code     courseCode,
               b.course_name     courseName,
               b.paper_number    paperNumber,
               c.exam_start_time examStartTime,
               c.exam_end_time   examEndTime,
               c.exam_place      examPlace,
               c.exam_room       examRoom,
               bs.id             semesterId,
               bs.name           semesterName,
               be.id             examId,
               be.name           examName
        FROM exam_student a
                 LEFT JOIN
             exam_detail_course b ON a.exam_detail_course_id = b.id
                 LEFT JOIN
             exam_detail c ON b.exam_detail_id = c.id
                 LEFT JOIN
             exam_print_plan epp on epp.id = c.print_plan_id
                 LEFT JOIN
             basic_exam be on be.id = epp.exam_id
                 LEFT JOIN
             basic_semester bs on be.semester_id = bs.id
        WHERE a.id = #{id}
    </select>

    <select id="queryBySchoolIdAndExamDetailCourseIds"
            resultType="com.qmth.distributed.print.business.bean.dto.ExamStudentCourseDto">
        select
        es.id,
        es.school_id as schoolId,
        es.exam_detail_course_id as examDetailCourseId,
        es.student_name as studentName,
        es.student_code as studentCode,
        es.ticket_number as ticketNumber,
        es.site_number as siteNumber,
        es.print_paper as printPaper,
        es.print_card as printCard,
        es.extend_fields as extendFields,
        es.paper_type as paperType,
        es.create_id as createId,
        es.create_time as createTime,
        es.update_id as updateId,
        es.update_time as updateTime,
        es.attachment_id as attachmentId,
        edc.course_code as courseCode,
        edc.course_name as courseName,
        edc.paper_pages_a3 as paperPageA3,
        edc.paper_number as paperNumber,
        es.clazz_id clazzId,
        es.clazz_name clazzName,
        es.teach_clazz_name teachClazzName,
        es.college_name collegeName,
        es.major_name majorName,
        es.student_clazz_type studentClazzType
        from
        exam_student es
        left join exam_detail_course edc on
        edc.id = es.exam_detail_course_id
        <where>
            <if test="schoolId != null and schoolId != ''">
                and es.school_id = #{schoolId}
            </if>
            <if test="examDetailCourseIds != null and examDetailCourseIds != '' and examDetailCourseIds.size > 0">
                AND es.exam_detail_course_id IN
                <foreach collection="examDetailCourseIds" item="examDetailCourseId" index="index" open="(" separator=","
                         close=")">
                    #{examDetailCourseId}
                </foreach>
            </if>
        </where>
        order by edc.course_code, 0+ cast(es.site_number as char), es.ticket_number
    </select>
    <select id="listStudentByExamDetailCourseId"
            resultType="com.qmth.distributed.print.business.bean.dto.SyncExamStudentDto">
        SELECT a.id,
               b.course_code   courseCode,
               b.course_name   courseName,
               b.paper_number  paperNumber,
               a.student_name  studentName,
               a.student_code  studentCode,
               a.ticket_number ticketNumber,
               a.clazz_name    clazzName,
               a.extend_fields extendFields,
               a.paper_type    paperType,
               c.package_code pachageCode,
               et.sequence
        FROM exam_detail_course b
                 LEFT JOIN
             exam_student a ON a.exam_detail_course_id = b.id
                 LEFT JOIN
             exam_detail c ON b.exam_detail_id = c.id
                 LEFT JOIN
             exam_task et ON b.school_id = et.school_id
                 and c.exam_id = et.exam_id
                 and b.course_code = et.course_code
                 and b.paper_number = et.paper_number
        WHERE b.id = #{examDetailCourseId}
    </select>
    <select id="listExamStudentBySchoolIdAndClazzId"
            resultType="com.qmth.distributed.print.business.entity.ExamStudent">
        SELECT
        *
        FROM
        (SELECT
        bs.school_id AS schoolId,
        bs.student_code AS studentCode,
        bs.student_name AS studentName,
        bs.clazz_id AS clazzId,
        bc.clazz_name AS clazzName,
        FROM
        basic_student bs
        INNER JOIN basic_clazz bc ON bs.clazz_id = bc.id
        <where>
            <if test="schoolId != null and schoolId != ''">
                AND bs.school_id = #{schoolId}
            </if>
            <if test="classId != null and classId != ''">
                AND bc.id = #{classId}
            </if>
        </where>
        UNION
        SELECT
        ts.school_id AS schoolId,
        bs.student_code AS studentCode,
        bs.student_name AS studentName,
        ts.teach_clazz_id AS clazzId,
        tc.teach_clazz_name AS clazzName,
        FROM
        teach_student ts
        join basic_student bs on ts.student_id = bs.id
        INNER JOIN teach_clazz tc ON ts.teach_clazz_id = tc.id
        <where>
            <if test="schoolId != null and schoolId != ''">
                AND ts.school_id = #{schoolId}
            </if>
            <if test="classId != null and classId != ''">
                AND tc.id = #{classId}
            </if>
        </where>
        ) t0;
    </select>
    <select id="getBasicCollegeByBelongOrgId" resultType="com.qmth.teachcloud.common.entity.BasicCollege">
        SELECT bc.id,
               bc.college_name
        FROM basic_student bs
                 LEFT JOIN
             basic_college bc ON bs.belong_org_id = bc.id
        WHERE bs.student_code = #{studentCode}
          and bs.school_id = #{schoolId}
    </select>
    <select id="getByStudentId"
            resultType="com.qmth.distributed.print.business.bean.dto.ExamStudentPdfInfoDto">
        SELECT a.student_code  studentCode,
               a.student_name  studentName,
               a.ticket_number ticketNumber,
               a.site_number   siteNumber,
               a.extend_fields extendFields,
               a.paper_type    paperType,
               a.college_name  collegeName,
               a.major_name    majorName,
               a.clazz_id      clazzId,
               a.clazz_name    clazzName,
               a.campus_name   campusName,
               a.attachment_id attachmentId,
               b.course_code   courseCode,
               b.course_name   courseName,
               b.paper_number  paperNumber,
               c.exam_place    examPlace,
               c.exam_room     examRoom
        FROM exam_student a
                 JOIN
             exam_detail_course b ON a.exam_detail_course_id = b.id
                 JOIN
             exam_detail c ON b.exam_detail_id = c.id
        WHERE a.id = #{id}
    </select>
    <select id="listStudentScoreSync" resultType="java.util.Map">
        SELECT
            CASE a.student_clazz_type
                WHEN 'TEACH_CLAZZ' THEN a.teach_clazz_id
                WHEN 'BASIC_CLAZZ' THEN a.clazz_id
                END clazzId,
            CASE a.student_clazz_type
                WHEN 'TEACH_CLAZZ' THEN a.teach_clazz_name
                WHEN 'BASIC_CLAZZ' THEN a.clazz_name
                END clazzName,
            a.major_name majorName,
            b.course_code courseCode,
            b.paper_number paperNumber,
            a.paper_type paperType,
            d.sequence
        FROM
            exam_student a
                JOIN
            exam_detail_course b ON a.exam_detail_course_id = b.id
                JOIN
            exam_detail ed ON b.exam_detail_id = ed.id
                JOIN
            exam_task d ON b.school_id = d.school_id
                AND ed.exam_id = d.exam_id
                AND b.course_code = d.course_code
                AND b.paper_number = d.paper_number
        WHERE
            a.school_id = #{schoolId}
          AND a.student_code = #{studentCode}
          AND a.ticket_number = #{examNumber}
          AND CONCAT(b.course_code, a.paper_type, d.sequence) = #{subjectCode}
    </select>
    <select id="listExamStudentByPaperNumberAndPaperType"
            resultType="com.qmth.distributed.print.business.bean.dto.ExamStudentCourseClassDto">
        SELECT
            a.course_code courseCode,
            a.course_name courseName,
            a.paper_number paperNumber,
            a.paper_type paperType,
            a.clazz_id clazzId,
            a.clazz_name clazzName,
            b.teacher_name teacherName,
            b.teacher_number teacherNumber
        FROM
            (SELECT DISTINCT
                 edc.course_code,
                 edc.course_name,
                 edc.paper_number,
                 es.paper_type,
                 CASE es.student_clazz_type
                     WHEN 'TEACH_CLAZZ' THEN es.teach_clazz_id
                     WHEN 'BASIC_CLAZZ' THEN es.clazz_id
                     END clazz_id,
                 CASE es.student_clazz_type
                     WHEN 'TEACH_CLAZZ' THEN es.teach_clazz_name
                     WHEN 'BASIC_CLAZZ' THEN es.clazz_name
                     END clazz_name
             FROM
                 exam_student es
                     JOIN exam_detail_course edc ON es.exam_detail_course_id = edc.id
             WHERE
                 edc.paper_number = #{paperNumber}
               AND es.paper_type = #{paperType}) a
                LEFT JOIN
            (SELECT
                 *
             FROM
                 grade_batch_student_clazz
             WHERE
                 batch_id = #{batchId}) b ON a.course_code = b.course_code
                AND a.paper_number = b.paper_number
                AND a.paper_type = b.paper_type
                AND a.clazz_id = b.clazz_id
    </select>
    <select id="listTicketNumberBySemesterId" resultType="java.lang.String">
        SELECT
            es.ticket_number
        FROM
            exam_student es
        WHERE
            EXISTS( SELECT
                        1
                    FROM
                        exam_detail_course edc
                            JOIN
                        exam_detail ed ON edc.exam_detail_id = ed.id
                            JOIN
                        exam_print_plan epp ON ed.print_plan_id = epp.id
                            JOIN
                        basic_exam be ON epp.exam_id = be.id
                    WHERE
                        es.exam_detail_course_id = edc.id
                      AND be.semester_id = #{semesterId}
                        AND be.id = #{examId})
    </select>
</mapper>
