<?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.TSyncExamStudentScoreMapper">

    <sql id="headCommonSql">
         select
            distinct tsess.id,
            bs.id as semesterId,
            bs.name as semesterName,
            be.name examName,
            tsess.exam_id as examId,
            tsess.exam_code as examCode,
            tsess.name,
            tsess.student_code as studentCode,
            tsess.exam_number as examNumber,
            tsess.major_name as majorName,
            tsess.college as orgName,
            tsess.clazz_name as clazzName,
            concat(edc.paper_number,edc.paper_type) as courseCode,
            edc.course_name as courseName,
            tsess.total_score as totalScore,
            tsess.objective_score as objectiveScore,
            tsess.subjective_score as subjectiveScore,
            tsess.local_sheet_urls as localSheetUrls,
            tsess.sheet_urls as sheetUrls,
            tsess.status,
            tsess.create_time as syncTime
    </sql>

    <sql id="middleCommonSql">
        join basic_semester bs on
            bs.id = tsess.semester_id
        join basic_student bs2 on
            bs2.school_id = tsess.school_id and bs2.student_code = tsess.student_code
        join exam_student es on
            es.school_id = tsess.school_id and es.ticket_number = tsess.exam_number and es.student_code = tsess.student_code
        join exam_detail_course edc on
            edc.id = es.exam_detail_course_id
        join exam_task et on
            bs.school_id = et.school_id and edc.course_code = et.course_code and edc.paper_number = et.paper_number
        join basic_exam be on
            et.exam_id = be.id
    </sql>

    <sql id="footCommonSql">
        <where>
            <if test="schoolId != null and schoolId != ''">
                and tsess.school_id = #{schoolId}
            </if>
            <if test="semesterId != null and semesterId != ''">
                and tsess.semester_id = #{semesterId}
            </if>
            <if test="examId != null and examId != ''">
                and et.exam_id = #{examId}
            </if>
            <if test="clazzId != null and clazzId != ''">
                and tsess.clazz_id = #{clazzId}
            </if>
            <if test="courseCode != null and courseCode != ''">
                and edc.course_code = #{courseCode}
            </if>
            order by tsess.exam_id
        </where>
    </sql>

    <select id="list" resultType="com.qmth.distributed.print.business.bean.result.TSyncExamStudentScoreResult">
        <include refid="headCommonSql"/>
        from
        t_sync_exam_student_score tsess
        <include refid="middleCommonSql"/>
        <include refid="footCommonSql"/>
    </select>

    <select id="export" resultType="com.qmth.distributed.print.business.bean.result.TSyncExamStudentScoreResult">
        <include refid="headCommonSql"/>
        ,tsess.trajectory_urls as trajectoryUrls,
        tsess.sync_data as trajectoryCoordinate,
        tsess.sync_data as syncData
        from
        t_sync_exam_student_score tsess
        <include refid="middleCommonSql"/>
        <include refid="footCommonSql"/>
    </select>

    <select id="exportCount" resultType="java.lang.Integer">
        select count(1) from t_sync_exam_student_score tsess
        <include refid="middleCommonSql"/>
        <include refid="footCommonSql"/>
    </select>
</mapper>
