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

    <select id="findNeedAnalyzedPaper"
            resultType="com.qmth.distributed.print.business.bean.result.analyze.GradeInitResult">
        SELECT
            bs.name AS semesterName,
            be.id AS examId,
            be.name AS examName,
            et.course_code AS courseCode,
            et.course_name AS courseName,
            et.paper_number AS paperNumber,
            pnpt.paper_type AS paperType,
            pnpt.paper_name AS paperName,
            pnpt.status AS status,
            pnpt.publish_status AS publishStatus,
            eps.structure_change AS structureChange
        FROM
            exam_task et
                INNER JOIN
            exam_task_detail etd ON et.id = etd.exam_task_id
                INNER JOIN
            (SELECT
                 school_id,
                 exam_id,
                 paper_number,
                 paper_type,
                 MAX(paper_name) AS paper_name,
                 GROUP_CONCAT(status) AS status,
                 MAX(publish_status) AS publish_status
             FROM
                 grade_batch_paper
             WHERE
                 enable = TRUE
               AND status NOT IN ('SETTING_GRADE_PAPER' , 'PUSH_GRADE_BATCH')
             GROUP BY school_id ,exam_id, paper_number , paper_type) pnpt ON et.school_id = pnpt.school_id
                AND et.paper_number = pnpt.paper_number
                AND et.exam_id = pnpt.exam_id
                AND LOCATE(pnpt.paper_type, etd.relate_paper_type)
                INNER JOIN
            basic_exam be ON et.exam_id = be.id AND be.enable
                INNER JOIN
            basic_semester bs ON bs.id = be.semester_id AND bs.enable
                LEFT JOIN
            exam_paper_structure eps ON et.school_id = eps.school_id
                AND et.paper_number = eps.paper_number AND eps.paper_type = pnpt.paper_type AND et.exam_id = eps.exam_id
        <where>
            <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="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>
        ORDER BY bs.name , be.name , et.course_code ,et.exam_id, et.paper_number , pnpt.paper_type
    </select>

    <select id="listNeedAnalyzedPaperToWork"
            resultType="com.qmth.distributed.print.business.bean.result.analyze.GradeInitResult">
        SELECT
        bs.name AS semesterName,
        be.name AS examName,
        et.course_code AS courseCode,
        et.course_name AS courseName,
        et.exam_id AS examId,
        et.paper_number AS paperNumber,
        pnpt.paper_type AS paperType,
        pnpt.paper_name AS paperName
        FROM
        exam_task et
        INNER JOIN
        exam_task_detail etd ON et.id = etd.exam_task_id
        INNER JOIN
        (SELECT
        school_id,
        exam_id,
        paper_number,
        paper_type,
        MAX(paper_name) AS paper_name
        FROM
        grade_batch_paper
        where  1 = 1
        <if test="status != null and status != ''">
            <choose>
                <when test="ready != null and ready != '' and ready == true">
                    and status <![CDATA[ <> ]]> #{status}
                </when>
                <otherwise>
                    and status = #{status}
                </otherwise>
            </choose>
        </if>
        GROUP BY school_id ,exam_id, paper_number , paper_type) pnpt ON et.school_id = pnpt.school_id
        AND et.exam_id = pnpt.exam_id
        AND et.paper_number = pnpt.paper_number
        AND LOCATE(pnpt.paper_type, etd.relate_paper_type)
        LEFT JOIN
        basic_exam be ON et.exam_id = be.id
        LEFT JOIN
        basic_semester bs ON bs.id = be.semester_id
        <where>
            <if test="schoolId != null and schoolId != ''">
                AND et.school_id = #{schoolId}
            </if>
            <if test="userId != null and userId != ''">
                AND et.user_id = #{userId}
            </if>
        </where>
        ORDER BY bs.name , be.name , et.course_code ,et.exam_id, et.paper_number , pnpt.paper_type
    </select>
</mapper>