<?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.themis.business.dao.TEExamReexamMapper">

    <select id="reexamPageRequestList" resultType="com.qmth.themis.business.bean.backend.ReexamListRequestBean">
        select * from(select
             tees.room_code as roomCode,
            tees.room_name as roomName,
            tees.`identity`,
            tees.name,
            tees.exam_activity_id as examActivityId,
            tees.id as examStudentId,
            tees.course_code as courseCode,
            tees.course_name as courseName,
            toer.id as examRecordId,
            tees.exam_id as examId,
            tee.exam_count - tees.already_exam_count as examCount,
            toer.status
        from
            t_e_exam_student tees
            left join t_oe_exam_record toer on
            toer.exam_student_id = toer.exam_student_id
            left join t_e_exam tee on
            tees.exam_id = tee.id
            inner join (select distinct tbeiu.room_code from t_b_exam_invigilate_user tbeiu
            <if test="userId != null and userId != ''">
                where tbeiu.user_id = #{userId}
            </if>
            ) t on t.room_code = tees.room_code
            <where>
                <if test="examId != null and examId != ''">
                    and tees.exam_id = #{examId}
                </if>
                <if test="examActivityId != null and examActivityId != ''">
                    and tees.exam_activity_id = #{examActivityId}
                </if>
                <if test="roomCode != null and roomCode != ''">
                    and tees.room_code = #{roomCode}
                </if>
                <if test="courseCode != null and courseCode != ''">
                    and tees.course_code like CONCAT('%', #{courseCode},'%')
                </if>
                <if test="name != null and name !=''">
                    and tees.name like CONCAT('%', #{name},'%')
                </if>
                <if test="identity != null and identity !=''">
                    and tees.identity like CONCAT('%', #{identity},'%')
                </if>
                and tees.enable = 1
            </where>
        ) t where t.examCount = 0 and status = 'finished'
            order by t.roomCode
    </select>

    <select id="reexamPageNotDoneList" resultType="com.qmth.themis.business.bean.backend.ReexamListNotDoneBean">
        select
            teer.id as reexamId,
            teer.exam_id as examId,
            teer.exam_activity_id as examActivityId,
            teea.code as examActivityCode,
            tees.room_code as roomCode,
            tees.room_name as roomName,
            tees.course_code as courseCode,
            tees.course_name as courseName,
            tees.`identity`,
            tee.name as examName,
            tees.name,
            teer.reason,
            teer.create_time as applyTime,
            tbu.name as applyName
        from
            t_e_exam_reexam teer
        left join t_e_exam_student tees on
            tees.id = teer.exam_student_id
        left join t_e_exam tee on
            tee.id = teer.exam_id
        left join t_e_exam_activity teea on
            teea.id = teer.exam_activity_id
        left join t_b_user tbu on
            tbu.id = teer.create_id
        left join t_e_exam_reexam_auditing teera on
            teera.reexam_id = teer.id
         <where>
             <if test="userId != null and userId !=''">
                 and teera.auditing_id = #{userId}
             </if>
             <if test="examId != null and examId != ''">
                 and teer.exam_id = #{examId}
             </if>
             <if test="examActivityId != null and examActivityId != ''">
                 and teer.exam_activity_id = #{examActivityId}
             </if>
             <if test="roomCode != null and roomCode != ''">
                 and tees.room_code = #{roomCode}
             </if>
             <if test="courseCode != null and courseCode != ''">
                 and tees.course_code like CONCAT('%', #{courseCode},'%')
             </if>
             <if test="name != null and name !=''">
                 and tees.name like CONCAT('%', #{name},'%')
             </if>
             <if test="identity != null and identity !=''">
                 and tees.identity like CONCAT('%', #{identity},'%')
             </if>
             <if test="reason != null and reason !=''">
                 and teer.reason = #{reason}
             </if>
             <if test="reasonStartTime != null and reasonStartTime != ''">
                 and teer.create_time &gt;= #{reasonStartTime}
             </if>
             <if test="reasonEndTime != null and reasonEndTime != ''">
                 and teer.create_time &lt;= #{reasonEndTime}
             </if>
             <if test="applyName != null and applyName !=''">
                 and tbu.name like CONCAT('%', #{applyName},'%')
             </if>
             and tee.enable = 1
             and teea.enable = 1
             and tees.enable = 1
             and teer.status = 1
         </where>
         order by tees.room_code
    </select>

    <select id="reexamPageNotDoneCount" resultType="java.lang.Integer">
        select count(1)
        from
        t_e_exam_reexam teer
        left join t_e_exam_student tees on
        tees.id = teer.exam_student_id
        left join t_e_exam tee on
        tee.id = teer.exam_id
        left join t_e_exam_activity teea on
        teea.id = teer.exam_activity_id
        left join t_b_user tbu on
        tbu.id = teer.create_id
        left join t_e_exam_reexam_auditing teera on
        teera.reexam_id = teer.id
        <where>
            <if test="userId != null and userId !=''">
                and teera.auditing_id = #{userId}
            </if>
            <if test="examId != null and examId != ''">
                and teer.exam_id = #{examId}
            </if>
            and tee.enable = 1
            and teea.enable = 1
            and tees.enable = 1
            and teer.status = 1
        </where>
    </select>

    <select id="reexamPageDoneList" resultType="com.qmth.themis.business.bean.backend.ReexamListDoneBean">
        select
            teer.id as reexamId,
            teer.exam_id as examId,
            teer.exam_activity_id as examActivityId,
            teea.code as examActivityCode,
            tees.room_code as roomCode,
            tees.room_name as roomName,
            tees.course_code as courseCode,
            tees.course_name as courseName,
            tees.`identity`,
            tee.name as examName,
            tees.name,
            teer.reason,
            teer.create_time as applyTime,
            teer.auditing_status as auditingStatus,
            teer.update_time as auditingTime,
            tbu.name as applyName,
            tbu1.name as auditingName,
            if(teer.auditing_status = 0,'通过','不通过') as auditingStatusStr
        from
            t_e_exam_reexam teer
        left join t_e_exam_student tees on
            tees.id = teer.exam_student_id
        left join t_e_exam tee on
            tee.id = teer.exam_id
        left join t_e_exam_activity teea on
            teea.id = teer.exam_activity_id
        left join t_b_user tbu on
            tbu.id = teer.create_id
        left join t_b_user tbu1 on
            tbu1.id = teer.auditing_id
        <where>
            <if test="userId != null and userId !=''">
                and teer.auditing_id = #{userId}
            </if>
            <if test="examId != null and examId != ''">
                and teer.exam_id = #{examId}
            </if>
            <if test="examActivityId != null and examActivityId != ''">
                and teer.exam_activity_id = #{examActivityId}
            </if>
            <if test="roomCode != null and roomCode != ''">
                and tees.room_code = #{roomCode}
            </if>
            <if test="courseCode != null and courseCode != ''">
                and tees.course_code like CONCAT('%', #{courseCode},'%')
            </if>
            <if test="name != null and name !=''">
                and tees.name like CONCAT('%', #{name},'%')
            </if>
            <if test="identity != null and identity !=''">
                and tees.identity like CONCAT('%', #{identity},'%')
            </if>
            <if test="auditingStatus != null and auditingStatus !='' or auditingStatus == 0">
                and teer.auditing_status = #{auditingStatus}
            </if>
            <if test="reasonStartTime != null and reasonStartTime != ''">
                and teer.create_time &gt;= #{reasonStartTime}
            </if>
            <if test="reasonEndTime != null and reasonEndTime != ''">
                and teer.create_time &lt;= #{reasonEndTime}
            </if>
            <if test="applyName != null and applyName !=''">
                and tbu.name like CONCAT('%', #{applyName},'%')
            </if>
            and tee.enable = 1
            and teea.enable = 1
            and tees.enable = 1
            and teer.status = 2
        </where>
        order by tees.room_code
    </select>
    
    <select id="getExamReexamPage"
		resultType="com.qmth.themis.business.bean.backend.ExamReexamListBean">
		SELECT
		f.exam_student_id examStudentId,
		t.exam_id examId,
		t.exam_activity_id examActivityId,
		t.room_code roomCode,
		t.course_code courseCode,
		t.course_name courseName,
		t.NAME,
		t.identity,
		t.room_name roomName
		FROM
		t_e_exam_reexam f
		LEFT JOIN t_e_exam_student t ON f.exam_student_id = t.id
		where f.exam_id =#{examId}
		<if test="activityId != null and activityId != ''">
			and f.exam_activity_id = #{activityId}
		</if>
		<if test="roomCode != null and roomCode != ''">
			and t.room_code =#{roomCode}
		</if>
		<if test="courseCode != null and courseCode != ''">
			and t.course_code = #{courseCode}
		</if>
		<if test="identity != null and identity != ''">
			and t.identity like concat(#{identity},'%')
		</if>
		<if test="name != null and name != ''">
			and t.name like concat(#{name},'%')
		</if>
		GROUP BY f.exam_student_id
	</select>
</mapper>
