<?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.entity.ExamPaperStructure">
        SELECT
            eps.id,
            epp.school_id schoolId,
            be.id examId,
            bs.id semesterId,
            bs.name semesterName,
            epp.third_relate_id thirdRelateId,
            epp.third_relate_name thirdRelateName,
            eps.objective_structure objectiveStructure,
            eps.subjective_structure subjectiveStructure,
            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
        FROM
            exam_print_plan epp
                LEFT JOIN
            basic_exam be ON epp.exam_id = be.id
                LEFT JOIN
            basic_semester bs ON be.semester_id = bs.id
                LEFT JOIN
            (SELECT
                 ed.school_id,
                 ed.print_plan_id,
                 edc.paper_number,
                 MAX(edc.course_code) AS course_code,
                 MAX(edc.course_name) AS course_name
             FROM
                 exam_detail ed
                     LEFT JOIN exam_detail_course edc ON ed.id = edc.exam_detail_id
             GROUP BY ed.school_id , ed.print_plan_id , edc.paper_number) ep ON epp.id = ep.print_plan_id
                LEFT JOIN
            (SELECT
                 school_id, paper_number, paper_type
             FROM
                 exam_student
             GROUP BY school_id , paper_number , paper_type) es ON es.school_id = ep.school_id
                AND es.paper_number = ep.paper_number
                LEFT JOIN
            exam_task et ON ep.school_id = et.school_id
                AND et.exam_id = epp.exam_id
                AND ep.paper_number = et.paper_number
                LEFT JOIN
            exam_paper_structure eps ON ep.school_id = eps.school_id
                AND ep.paper_number = eps.paper_number
                AND es.paper_type = eps.paper_type
        WHERE
            epp.school_id = #{schoolId}
            and et.user_id = #{propositionTeacherId}
            and epp.sync_status = #{syncStatus}
        <if test="semesterId != null and semesterId != ''">
            and bs.id = #{semesterId}
        </if>
        <if test="examId != null and examId != ''">
            and be.id = #{examId}
        </if>
        <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>
    </select>
</mapper>