<?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.ConditionMapper">
    <select id="listCourse" resultType="com.qmth.teachcloud.common.entity.BasicCourse">
        SELECT
            bc.code code,
            bc.name name
        FROM
            basic_course bc
        <where>
            EXISTS( SELECT
                        1
                    FROM
                        exam_task et
                            LEFT JOIN
                        basic_exam be ON et.exam_id = be.id
                    <where>
                        bc.school_id = et.school_id
                      AND bc.code = et.course_code
                      <if test="semesterId != null">
                          AND be.semester_id = #{semesterId}
                      </if>
                        <if test="examId != null">
                            AND et.exam_id = #{examId}
                        </if>
                        <if test="dpr != null">
                            <if test="dpr.requestUserId != null">
                                AND et.create_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>)
            <if test="enable != null">
                and bc.enable = #{enable}
            </if>
        </where>
        order by bc.code
    </select>
    <select id="listPaperNumber" resultType="java.lang.String">
        SELECT
            et.paper_number
        FROM
            exam_task et
                LEFT JOIN
            basic_exam be ON et.exam_id = be.id
        <where>
            <if test="semesterId != null">
                AND be.semester_id = #{semesterId}
            </if>
                AND et.exam_id = #{examId}
            <if test="courseCode != null and courseCode != ''">
                AND et.course_code = #{courseCode}
            </if>
            <if test="dpr != null">
                <if test="dpr.requestUserId != null">
                    AND et.create_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 et.paper_number
    </select>
    <select id="listCourseForApply" resultType="com.qmth.teachcloud.common.entity.BasicCourse">
        SELECT
            bc.code code,
            bc.name name
        FROM
            basic_course bc
        <where>
            EXISTS ( SELECT
                1
            FROM
            exam_task et
                LEFT JOIN
            basic_exam be ON et.exam_id = be.id
            <where>
                bc.school_id = et.school_id
                AND bc.code = et.course_code
                <if test="semesterId != null">
                    AND be.semester_id = #{semesterId}
                </if>
                <if test="examId != null">
                    AND et.exam_id = #{examId}
                </if>
                <if test="userId != null">
                    AND et.user_id = #{userId}
                </if>
            </where>)
            <if test="enable != null">
                and bc.enable = #{enable}
            </if>
        </where>
        order by bc.code
    </select>
    <select id="listCourseForAudit" resultType="com.qmth.teachcloud.common.entity.BasicCourse">
        SELECT
            bc.code code,
            bc.name name
        FROM
            basic_course bc
        <where>
            EXISTS( SELECT
            1
            FROM
                exam_task et
                    LEFT JOIN
                basic_exam be ON et.exam_id = be.id
            <where>
                AND bc.school_id = et.school_id
                AND bc.code = et.course_code
                <if test="semesterId != null">
                    AND be.semester_id = #{semesterId}
                </if>
                <if test="examId != null">
                    AND et.exam_id = #{examId}
                </if>
            </where>)
            <if test="enable != null">
                and bc.enable = #{enable}
            </if>
        </where>
        order by bc.code
    </select>
    <select id="listPaperNumberForApply" resultType="java.lang.String">
        SELECT
            et.paper_number
        FROM
            exam_task et
                LEFT JOIN
            basic_exam be ON et.exam_id = be.id
        <where>
            <if test="semesterId != null">
                AND be.semester_id = #{semesterId}
            </if>
                AND et.exam_id = #{examId}
            <if test="courseCode != null and courseCode != ''">
                AND et.course_code = #{courseCode}
            </if>
            <if test="userId != null">
                AND et.user_id = #{userId}
            </if>
        </where>
        order by et.paper_number
    </select>
    <select id="listPaperNumberAudit" resultType="java.lang.String">
        SELECT
            et.paper_number
        FROM
            exam_task et
            LEFT JOIN
            basic_exam be ON et.exam_id = be.id
        <where>
            <if test="semesterId != null">
                AND be.semester_id = #{semesterId}
            </if>
                AND et.exam_id = #{examId}
            <if test="courseCode != null and courseCode != ''">
                AND et.course_code = #{courseCode}
            </if>
        </where>
        order by et.paper_number
    </select>
    <select id="listPrintPlan" resultType="com.qmth.distributed.print.business.bean.result.PrintPlanBrief">
        select a.id, a.name, a.status from exam_print_plan a
            left join basic_exam be on a.exam_id = be.id
            left join sys_user b on a.create_id = b.id
        <where>
            <if test="semesterId != null">
                and be.semester_id = #{semesterId}
            </if>
            <if test="examId != null">
                and a.exam_id = #{examId}
            </if>
            <if test="dpr != null">
                <if test="dpr.requestUserId != null">
                    AND b.id = #{dpr.requestUserId}
                </if>
                <if test="dpr.orgIdSet != null and dpr.orgIdSet != '' and dpr.orgIdSet.size > 0">
                    AND b.org_id IN
                    <foreach collection="dpr.orgIdSet" item="item" index="index" open="(" separator="," close=")">
                        #{item}
                    </foreach>
                </if>
            </if>
        </where>
    </select>
    <select id="listCourseForScore" resultType="com.qmth.teachcloud.common.entity.BasicCourse">
        SELECT DISTINCT
            et.course_code code, et.course_name name
        FROM
            (SELECT DISTINCT
                 school_id, semester_id, exam_id, paper_number
             FROM
                 t_sync_exam_student_score) tsess
                JOIN
            exam_task et ON tsess.school_id = et.school_id
                AND tsess.paper_number = et.paper_number
                JOIN
            sys_user su ON et.user_id = su.id
                JOIN
            basic_exam be ON et.exam_id = be.id
                JOIN
            basic_course bc ON et.school_id = bc.school_id
                AND et.course_code = bc.code
                LEFT JOIN
            teach_course tc ON bc.school_id = tc.school_id
                AND bc.id = tc.basic_course_id
        <where>
            <if test="semesterId != null">
                AND tsess.semester_id = #{semesterId}
            </if>
            <if test="examId != null">
                AND et.exam_id = #{examId}
            </if>
            <if test="basicCourses.size > 0">
                and bc.code in
                <foreach collection="basicCourses" item="item" index="index" open="(" separator="," close=")">
                    #{item.code}
                </foreach>
            </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 su.org_id IN
                    <foreach collection="dpr.orgIdSet" item="item" index="index" open="(" separator="," close=")">
                        #{item}
                    </foreach>
                </if>
            </if>
        </where>
        order by et.course_code
    </select>
    <select id="listClazz" resultType="com.qmth.teachcloud.common.entity.BasicClazz">
        SELECT DISTINCT
            tsess.clazz_id id, tsess.clazz_name clazzName
        FROM
            t_sync_exam_student_score tsess
                JOIN
            exam_task et ON tsess.school_id = et.school_id
                AND tsess.paper_number = et.paper_number
                JOIN
            basic_course bc ON et.school_id = bc.school_id
                AND et.course_code = bc.code
                LEFT JOIN
            teach_course tc ON bc.school_id = tc.school_id
                AND bc.id = tc.basic_course_id
        <where>
            <if test="semesterId != null">
                AND tsess.semester_id = #{semesterId}
            </if>
            <if test="examId != null">
                AND et.exam_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 tc.user_id = #{dpr.requestUserId}
                </if>
                <if test="dpr.orgIdSet != null and dpr.orgIdSet != '' and dpr.orgIdSet.size > 0">
                    AND bc.org_id IN
                    <foreach collection="dpr.orgIdSet" item="item" index="index" open="(" separator="," close=")">
                        #{item}
                    </foreach>
                </if>
            </if>
        </where>
    </select>
    <select id="listPrintPlanForClient"
            resultType="com.qmth.distributed.print.business.bean.result.PrintPlanBrief">
        SELECT
            distinct epp.id, epp.name, epp.status
        FROM
            exam_print_plan epp
                JOIN
            exam_detail ed ON epp.id = ed.print_plan_id
                JOIN
            basic_exam be ON epp.exam_id = be.id
        <where>
            and be.enable = true
            <if test="semesterId != null">
                and be.semester_id = #{semesterId}
            </if>
            <if test="examId != null">
                and be.id = #{examId}
            </if>
            AND ed.status not IN
            <foreach collection="examDetailStatus" item="item" index="index" open="(" separator="," close=")">
                #{item}
            </foreach>
        </where>
    </select>
</mapper>