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

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.qmth.distributed.print.business.entity.GradeBatchPaper">
        <result column="id" property="id"/>
        <result column="school_id" property="schoolId"/>
        <result column="batch_id" property="batchId"/>
        <result column="grade_paper_id" property="gradePaperId"/>
        <result column="paper_number" property="paperNumber"/>
        <result column="paper_type" property="paperType"/>
        <result column="status" property="status"/>
        <result column="teach_college_id" property="teachCollegeId"/>
        <result column="teach_college_name" property="teachCollegeName"/>
        <result column="paper_name" property="paperName"/>
        <result column="grade_course_code" property="gradeCourseCode"/>
        <result column="create_id" property="createId"/>
        <result column="create_time" property="createTime"/>
        <result column="update_id" property="updateId"/>
        <result column="update_time" property="updateTime"/>
    </resultMap>

    <select id="findGradeBatchPaperPage"
            resultType="com.qmth.distributed.print.business.bean.result.analyze.GradeBatchPaperResult">
        SELECT
        gbp.id AS id,
        bs.name AS semesterName,
        be.name AS examName,
        su.real_name AS teacherName,
        et.course_code AS courseCode,
        et.course_name AS courseName,
        et.teaching_room_id AS teachingRoomId,
        gbp.paper_number AS paperNumber,
        gbp.paper_type AS paperType,
        gbp.paper_name AS paperName,
        gbp.status AS status,
        gbp.publish_status AS publishStatus
        FROM
        grade_batch_paper gbp
        LEFT JOIN
        exam_task et ON gbp.school_id = et.school_id
        AND gbp.paper_number = et.paper_number AND gbp.exam_id = et.exam_id
        LEFT JOIN
        basic_exam be ON et.school_id = be.school_id
        AND et.exam_id = be.id
        LEFT JOIN
        basic_semester bs ON be.school_id = bs.school_id
        AND be.semester_id = bs.id
        LEFT JOIN
        sys_user su ON et.user_id = su.id
        <where>
            AND gbp.enable = true
            <if test="schoolId != null and schoolId != ''">
                AND gbp.school_id = #{schoolId}
            </if>
            <if test="batchId != null and batchId != ''">
                AND gbp.batch_id = #{batchId}
            </if>
            <if test="examId != null and examId != ''">
                AND be.id = #{examId}
            </if>
            <if test="courseName != null and courseName != ''">
                AND et.course_name like concat('%',#{courseName} ,'%')
            </if>
            <if test="dpr != null">
                <if test="dpr.requestUserId != null">
                    AND gbp.create_id = #{dpr.requestUserId}
                </if>
                <if test="dpr.orgIdSet != null and dpr.orgIdSet != '' and dpr.orgIdSet.size > 0">
                    AND gbp.org_id IN
                    <foreach collection="dpr.orgIdSet" item="item" index="index" open="(" separator="," close=")">
                        #{item}
                    </foreach>
                </if>
            </if>
        </where>
        order by gbp.create_id
    </select>

    <select id="findAbleAnalyzePaper"
            resultType="com.qmth.distributed.print.business.bean.result.analyze.GradeBatchPaperResult">
        SELECT
            bs.name AS semesterName,
            be.name AS examName,
            et.course_code AS courseCode,
            et.course_name AS courseName,
            et.teaching_room_id AS teachingRoomId,
            su.real_name AS teacherName,
            et.paper_number AS paperNumber,
            etd.exposed_paper_type AS paperType
        FROM
            exam_task et
                INNER JOIN
            exam_task_detail etd ON et.id = etd.exam_task_id
                LEFT JOIN
            basic_exam be ON be.school_id = et.school_id
                AND be.id = et.exam_id
                LEFT JOIN
            basic_semester bs ON bs.school_id = et.school_id
                AND bs.id = be.semester_id
                LEFT JOIN
            sys_user su ON et.user_id = su.id
        <where>
            AND etd.exposed_paper_type != ''
            <if test="schoolId != null and schoolId != ''">
                AND et.school_id = #{schoolId}
            </if>
            <if test="semesterId != null and semesterId != ''">
                AND bs.id = #{semesterId}
            </if>
            <if test="examId != null and examId != ''">
                AND be.id = #{examId}
            </if>
            <if test="courseCode != null and courseCode != ''">
                AND et.course_code = #{courseCode}
            </if>
            <if test="teacherName != null and teacherName != ''">
                AND et.teacher_name LIKE CONCAT('%',#{teacherName} ,'%')
            </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>
        </where>
    </select>
    <select id="findByThirdExamIdAndGradeCourseCode" resultMap="BaseResultMap">
        select gbp.*
        from grade_batch gb
        join grade_batch_paper gbp on gb.school_id = gbp.school_id
        and gb.id = gbp.batch_id
        <where>
            gb.school_id = #{schoolId}
            <if test="thirdExamId != null and thirdExamId != ''">
                and gb.third_exam_id = #{thirdExamId}
            </if>
            <if test="gradeCourseCode != null and gradeCourseCode != ''">
                and gbp.grade_course_code = #{gradeCourseCode}
            </if>
        </where>
    </select>

</mapper>
