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

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.qmth.distributed.print.business.entity.ExamDetailCourse">
    <result column="id" property="id" />
        <result column="school_id" property="schoolId" />
        <result column="exam_detail_id" property="examDetailId" />
        <result column="course_code" property="courseCode" />
        <result column="course_name" property="courseName" />
        <result column="paper_number" property="paperNumber" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id,
        school_id, exam_detail_id, course_code, course_name, paper_number
    </sql>
    <select id="listByExamDetailId" resultType="java.util.Map">
        SELECT
            ed.id examDetailId,
            ed.school_id schoolId,
            b.exam_id examId,
            a.id examDetailCourseId,
            a.course_code courseCode,
            a.course_name courseName,
            a.paper_number paperNumber,
            c.paper_attachment_ids paperAttachmentIds,
            a.attachment_id cardAttachmentIds,
            b.id examTaskId,
            c.exposed_paper_type exposedPaperType,
            c.unexposed_paper_type unexposedPaperType,
            a.paper_type paperType,
            ifnull(ed.print_count,0) printCount,
            a.common_attachment_id commonAttachmentId
        FROM
            exam_detail ed
                LEFT JOIN
            exam_detail_course a ON ed.id = a.exam_detail_id
                LEFT JOIN
            exam_task b ON a.school_id = b.school_id
                AND ed.exam_id = b.exam_id
                AND a.course_code = b.course_code
                AND a.paper_number = b.paper_number
                LEFT JOIN
            exam_task_detail c ON b.id = c.exam_task_id
                LEFT JOIN
            t_f_flow_approve d ON b.flow_id = d.flow_id
        WHERE
            a.exam_detail_id = #{examDetailId}
            AND (b.status = 'SUBMIT' and (d.status is null or d.status = #{status}))
    </select>
    <select id="listByExamDetailIdAndStatus" resultMap="BaseResultMap">
        SELECT
            a.*
        FROM
            exam_detail_course a
                LEFT JOIN
            exam_detail ed ON a.exam_detail_id = ed.id
                LEFT JOIN
            exam_task b ON a.school_id = b.school_id
                AND ed.exam_id = b.exam_id
                AND a.course_code = b.course_code
                AND a.paper_number = b.paper_number
                LEFT JOIN
            t_f_flow_approve c ON b.flow_id = c.flow_id
        WHERE
            a.exam_detail_id = #{examDetailId} AND b.status = #{status}
            AND (c.status is not null and c.status != 'FINISH')
    </select>
    <select id="listCoursesByPrintPlanId" resultType="com.qmth.teachcloud.common.entity.BasicCourse">
        SELECT
            distinct a.course_code code, a.course_name name
        FROM
            exam_detail_course a
                LEFT JOIN
            exam_detail b ON a.exam_detail_id = b.id
        <where>
            <if test="printPlanIdList != null and printPlanIdList != '' and printPlanIdList.size() > 0">
                and b.print_plan_id IN
                <foreach collection="printPlanIdList" item="item" index="index" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="param != null and param != ''">
                and (a.course_code like concat('%', #{param},'%')
                or a.course_name like concat('%', #{param},'%'))
            </if>
        </where>

    </select>
    <select id="listPaperNumberByPrintPlanId" resultType="java.lang.String">
        SELECT
            distinct a.paper_number paperNumber
        FROM
            exam_detail_course a
                LEFT JOIN
            exam_detail b ON a.exam_detail_id = b.id
        <where>
            <if test="printPlanIdList != null and printPlanIdList != '' and printPlanIdList.size() > 0">
                and b.print_plan_id IN
                <foreach collection="printPlanIdList" item="item" index="index" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="param != null and param != ''">
                and a.paper_number like concat('%', #{param},'%')
            </if>
            <if test="orgIds != null and orgIds != '' and orgIds.size > 0">
                AND a.org_id IN
                <foreach collection="orgIds" item="item" index="index" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
        </where>
    </select>
    <select id="listByPrintPlanIdAndCourseCodeAndPaperNumber" resultMap="BaseResultMap">
        SELECT
            a.*
        FROM
            exam_detail_course a
                LEFT JOIN
            exam_detail b ON a.exam_detail_id = b.id
                LEFT JOIN
            exam_print_plan c ON b.print_plan_id = c.id
        WHERE
            a.school_id = #{schoolId}
                AND b.print_plan_id = #{printPlanId}
                AND a.course_code = #{courseCode}
                AND a.paper_number = #{paperNumber}
                AND c.status != 'END'
    </select>
    <select id="listByAndCourseCodeAndPaperNumber" resultMap="BaseResultMap">
        SELECT
            a.*
        FROM
            exam_detail_course a
                LEFT JOIN
            exam_detail b ON a.exam_detail_id = b.id
                LEFT JOIN
            exam_print_plan c ON b.print_plan_id = c.id
        WHERE
            a.school_id = #{schoolId}
                AND c.exam_id = #{examId}
                AND a.course_code = #{courseCode}
                AND a.paper_number = #{paperNumber}
                AND c.status != 'END'
    </select>
    <select id="listSyncCourseByPrintPlanId"
            resultType="com.qmth.distributed.print.business.bean.dto.SyncExamTaskDto">
        SELECT
            et.id examTaskId,
            et.course_code courseCode,
            et.course_name courseName,
            et.paper_number paperNumber,
            t.paper_type paperType,
            et.sequence,
            et.start_time startTime,
            et.end_time endTime,
            t.total_subjects totalSubjects,
            t.print_plan_ids printPlanIds,
            ets.third_relate_id thirdRelateId,
            ets.sync_status syncStatus,
            su.real_name syncUserName,
            bc.teaching_room_id teachingRoomId
        FROM
            exam_task et
                 JOIN
                (SELECT
                 b.school_id,
                 c.exam_id,
                 b.paper_number,
                 b.paper_type,
                 SUM(b.total_subjects) total_subjects,
                 group_concat(c.print_plan_id) print_plan_ids
             FROM
                 exam_detail_course b
                     LEFT JOIN exam_detail c ON b.exam_detail_id = c.id
                     LEFT JOIN exam_print_plan epp ON c.print_plan_id = epp.id
                     LEFT JOIN basic_exam be ON epp.exam_id = be.id
            <where>
                and c.status = 'FINISH' and be.enable = true
                <if test="semesterId != null">
                    and be.semester_id = #{semesterId}
                </if>
                <if test="examId != null">
                    and be.id = #{examId}
                </if>
                <if test="courseCode != null and courseCode != ''">
                    and b.course_code = #{courseCode}
                </if>
                <if test="paperNumber != null and paperNumber != ''">
                    and b.paper_number = #{paperNumber}
                </if>
            </where>
             GROUP BY b.school_id , c.exam_id,  b.paper_number , b.paper_type) t
                ON t.school_id = et.school_id
                AND t.exam_id = et.exam_id
                AND t.paper_number = et.paper_number
                LEFT JOIN
            exam_task_sync ets ON et.school_id = ets.school_id
                AND et.exam_id = ets.exam_id
                AND et.paper_number = ets.paper_number
                LEFT JOIN
            sys_user su ON ets.sync_user_id = su.id
                LEFT JOIN
            basic_course bc ON et.school_id = bc.school_id
                AND et.course_code = bc.code
            <where>
                <if test="orgIds != null">
                    and et.org_id in
                    <foreach collection="orgIds" separator="," open="(" close=")" item="orgId">
                        #{orgId}
                    </foreach>
                </if>
                <if test="syncStatus != null">
                    and ets.sync_status = #{syncStatus}
                </if>
                <if test="startTime != null">
                    and et.start_time &gt;= #{startTime}
                </if>
                <if test="endTime != null">
                    and et.end_time &lt;= #{endTime}
                </if>
            </where>
        ORDER BY et.create_time desc
    </select>
    <select id="listByPrintPlanIdAndExamTaskId"
            resultType="com.qmth.distributed.print.business.bean.dto.ExamDetailCourseDto">
        SELECT b.id,
               et.id examTaskId,
               b.course_code   courseCode,
               b.course_name   courseName,
               b.paper_number  paperNumber,
               b.paper_type paperType,
               ets.third_relate_id thirdRelateId,
               ets.sync_status syncStatus
        FROM exam_detail_course b
                 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
                 LEFT JOIN
             exam_task_sync ets ON et.school_id = ets.school_id
                and et.exam_id = ets.exam_id
                 and et.paper_number = ets.paper_number
        <where>
            <if test="examTaskId != null">
                and et.id = #{examTaskId}
            </if>
            <if test="printPlanIds != null">
                and c.print_plan_id in
                <foreach collection="printPlanIds" open="(" close=")" item="printPlanId" separator=",">
                    #{printPlanId}
                </foreach>
            </if>
        </where>
    </select>

</mapper>
