<?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 semesterId,
            bs.name semesterName,
            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,
            tsess.subject_code as courseCode,
            et.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 exam_task_sync ets on tsess.school_id = ets.school_id and tsess.exam_id = ets.exam_id and tsess.paper_number = ets.paper_number
        join exam_task et on
            ets.school_id = et.school_id and ets.exam_id = et.exam_id and ets.paper_number = et.paper_number
        join sys_user su on
            et.user_id = su.id
        join basic_exam be on tsess.exam_id = be.id
        join basic_semester bs on be.semester_id = bs.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 et.course_code = #{courseCode}
            </if>
            <if test="(courseCode == null or courseCode == '') and basicCourses.size > 0">
                and et.course_code in
                <foreach collection="basicCourses" item="item" index="index" open="(" separator="," close=")">
                    #{item.code}
                </foreach>
            </if>
            <if test="dpr != null">
                <if test="dpr.requestUserId != null">
                    AND et.user_id = #{dpr.requestUserId}
                </if>
                <if test="dpr.orgIdSet != null and dpr.orgIdSet != '' and dpr.orgIdSet.size > 0">
                    AND su.org_id IN
                    <foreach collection="dpr.orgIdSet" item="item" index="index" open="(" separator="," close=")">
                        #{item}
                    </foreach>
                </if>
            </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>
