<?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.ExamPaperStructureMapper">
    <select id="listByPropositionTeacherId"
            resultType="com.qmth.distributed.print.business.bean.dto.ExamPaperStructurePageDto">
        SELECT
            distinct eps.id,
            ep.school_id schoolId,
            ep.exam_id examId,
            ep.third_relate_id thirdRelateId,
            ep.third_relate_name thirdRelateName,
            eps.objective_structure objectiveStructure,
            eps.subjective_structure subjectiveStructure,
            et.id examTaskId,
            ep.paper_number paperNumber,
            ep.course_code courseCode,
            ep.course_name courseName,
            et.sequence,
            eps.paper_info_json paperInfoJson,
            es.paper_type paperType,
            IFNULL(eps.status, 'INIT') status,
            eps.paper_answer paperAnswer,
            et.user_id propositionTeacherId,
            eps.mark_leader markLeader,
            et.transfer_id transferId
        FROM
            (SELECT
                 ed.school_id,
                 ed.exam_id,
                 edc.paper_number,
                 edc.course_code,
                 edc.course_name,
                 ets.third_relate_id,
                 tsse.exam_name third_relate_name
             FROM
                 exam_detail ed
                     LEFT JOIN exam_detail_course edc ON ed.id = edc.exam_detail_id
                     LEFT JOIN exam_task_sync ets ON edc.school_id = ets.school_id
                     AND ed.exam_id = ets.exam_id
                     AND edc.paper_number = ets.paper_number
                     LEFT JOIN t_sync_stmms_exam tsse ON edc.school_id = tsse.school_id
                     AND ets.third_relate_id = tsse.exam_id
                     LEFT JOIN basic_exam be ON ed.exam_id = be.id
                <where>
                    AND ets.sync_status = #{syncStatus}
                    AND be.enable = TRUE
                    <if test="semesterId != null">
                        and be.semester_id = #{semesterId}
                    </if>
                    <if test="examId != null">
                        and be.id = #{examId}
                    </if>
                </where>
               ) ep
                LEFT JOIN
            (SELECT DISTINCT
                 es.school_id, es.exam_id, es.paper_number, es.paper_type
             FROM
                 exam_student es
                     JOIN basic_exam be ON es.exam_id = be.id
             <where>
                <if test="semesterId != null">
                    and be.semester_id = #{semesterId}
                </if>
                <if test="examId != null">
                    and be.id = #{examId}
                </if>
             </where>
             ) es ON es.school_id = ep.school_id
                AND es.exam_id = ep.exam_id
                AND es.paper_number = ep.paper_number
                LEFT JOIN
            exam_task et ON ep.school_id = et.school_id
                AND et.exam_id = ep.exam_id
                AND ep.paper_number = et.paper_number
                LEFT JOIN
            exam_paper_structure eps ON ep.school_id = eps.school_id
                AND et.exam_id = eps.exam_id
                AND ep.paper_number = eps.paper_number
                AND es.paper_type = eps.paper_type
        WHERE
            ep.school_id = #{schoolId}
          AND IFNULL(et.transfer_id, et.user_id) = #{propositionTeacherId}
        <if test="structureStatusEnums != null and structureStatusEnums != ''">
            <choose>
                <when test="ready != null and ready != '' and ready == true">
                    AND eps.status IN
                    <foreach collection="structureStatusEnums" item="item" index="index" open="(" separator="," close=")">
                        #{item}
                    </foreach>
                </when>
                <otherwise>
                    AND ifnull(eps.status, 'INIT') NOT IN
                    <foreach collection="structureStatusEnums" item="item" index="index" open="(" separator="," close=")">
                        #{item}
                    </foreach>
                </otherwise>
            </choose>
        </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 et.org_id IN
                <foreach collection="dpr.orgIdSet" item="item" index="index" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
        </if>
    </select>
</mapper>