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

	<select id="examStudentQuery"
		resultType="com.qmth.themis.business.dto.response.TEExamStudentDto">
		select
		tees.id,
		tee.id as examId,
		tee.name as examName,
		tees.name,
		tees.`identity`,
		teea.code,
		tees.course_code as courseCode,
		tees.course_name as courseName,
		tees.enable,
		tees.room_code as roomCode,
		tees.room_name as roomName,
		tees.grade,
		tees.class_no as classNo,
		teea.id as examActivityId,
		teea.code as activityCode,
		stu.base_photo_path basePhotoUrl
		from
		t_e_exam_student tees
		left join t_e_student stu on tees.student_id=stu.id
		left join t_e_exam tee on
		tees.exam_id = tee.id
		left join t_e_exam_activity teea on
		tees.exam_activity_id = teea.id
		<where>
			<if test="examId != null and examId != ''">
				and tees.exam_id = #{examId}
			</if>
			<if test="activityId != null and activityId != ''">
				and tees.exam_activity_id = #{activityId}
			</if>
			<if test="identity != null and identity != ''">
				and tees.identity like concat('%', #{identity}, '%')
			</if>
			<if test="name != null and name != ''">
				and tees.name like concat('%', #{name}, '%')
			</if>
			<if test="roomCode != null and roomCode != ''">
				and tees.room_code like concat('%', #{roomCode}, '%')
			</if>
			<if test="courseCode != null and courseCode != ''">
				and tees.course_code = #{courseCode}
			</if>
			<if test="enable != null and enable != '' or enable == 0">
				and tees.enable = #{enable}
			</if>
			<if test="grade != null and grade != ''">
				and tees.grade like concat('%', #{grade}, '%')
			</if>
			<if test="classNo != null and classNo != ''">
				and tees.class_no like concat('%', #{classNo}, '%')
			</if>
			<if test="hasPhoto != null">
				<if test="hasPhoto == 0">
					and stu.base_photo_path is null
				</if>
				<if test="hasPhoto == 1">
					and stu.base_photo_path is not null
				</if>
			</if>
		</where>
	</select>
	<select id="examStudentList"
		resultType="com.qmth.themis.business.dto.response.TEExamStudentDto">
		select
		tees.id,
		tee.id as examId,
		tee.name as examName,
		tees.name,
		tees.`identity`,
		teea.code,
		tees.course_code as courseCode,
		tees.course_name as courseName,
		tees.enable,
		tees.room_code as roomCode,
		tees.room_name as roomName,
		tees.grade,
		tees.class_no as classNo,
		teea.id as examActivityId,
		teea.code as activityCode,
		stu.base_photo_path basePhotoUrl
		from
		t_e_exam_student tees
		left join t_e_student stu on tees.student_id=stu.id
		left join t_e_exam tee on
		tees.exam_id = tee.id
		left join t_e_exam_activity teea on
		tees.exam_activity_id = teea.id
		<where>
			<if test="examId != null and examId != ''">
				and tees.exam_id = #{examId}
			</if>
			<if test="activityId != null and activityId != ''">
				and tees.exam_activity_id = #{activityId}
			</if>
			<if test="identity != null and identity != ''">
				and tees.identity like concat('%', #{identity}, '%')
			</if>
			<if test="name != null and name != ''">
				and tees.name like concat('%', #{name}, '%')
			</if>
			<if test="roomCode != null and roomCode != ''">
				and tees.room_code like concat('%', #{roomCode}, '%')
			</if>
			<if test="courseCode != null and courseCode != ''">
				and tees.course_code = #{courseCode}
			</if>
			<if test="enable != null and enable != '' or enable == 0">
				and tees.enable = #{enable}
			</if>
			<if test="grade != null and grade != ''">
				and tees.grade like concat('%', #{grade}, '%')
			</if>
			<if test="classNo != null and classNo != ''">
				and tees.class_no like concat('%', #{classNo}, '%')
			</if>
			<if test="hasPhoto != null and hasPhoto != ''">
				<if test="hasPhoto == 0">
					and stu.base_photo_path is null
				</if>
				<if test="hasPhoto == 1">
					and stu.base_photo_path is not null
				</if>
			</if>
		</where>
	</select>
	
	<select id="getTotalCount" resultType="java.util.Map">
		select t.exam_activity_id activityId,count(1) cc from t_e_exam_student
		t
		where t.exam_id = #{examId}
		<if test="activityId != null and activityId != ''">
			and t.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>
		group by t.exam_activity_id
	</select>

	<select id="getTotalCountInfo"
		resultType="com.qmth.themis.business.bean.backend.ExamViewCountListBean">
		select t.exam_id examId,t.exam_activity_id examActivityId,t.room_code
		roomCode,t.room_name roomName, count(1) examTotal from
		t_e_exam_student t
		where t.exam_id = #{examId}
		<if test="activityId != null and activityId != ''">
			and t.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>
		group by t.exam_id,t.exam_activity_id, t.room_code,t.room_name
	</select>

	<select id="getDoneCountByActivityIds"
		resultType="java.util.Map">
		select t.exam_activity_id activityId,t.room_code roomCode,count(1) cc
		from t_e_exam_student t
		where t.exam_id = #{examId} and t.current_record_id is not null
		<if test="activityIds != null">
			and t.exam_activity_id in
			<foreach collection="activityIds" item="acid" index="index"
				open="(" close=")" separator=",">
				#{acid}
			</foreach>
		</if>
		group by t.exam_activity_id, t.room_code
	</select>

	<select id="getAbsentCountByActivityIds"
		resultType="java.util.Map">
		select t.exam_activity_id activityId,t.room_code roomCode,count(1) cc
		from t_e_exam_student t
		where t.exam_id = #{examId} and t.current_record_id is null
		<if test="activityIds != null">
			and t.exam_activity_id in
			<foreach collection="activityIds" item="acid" index="index"
				open="(" close=")" separator=",">
				#{acid}
			</foreach>
		</if>
		group by t.exam_activity_id, t.room_code
	</select>

	<select id="examRoomQuery"
		resultType="com.qmth.themis.business.dto.response.RoomCodeQueryDto">
		select
		DISTINCT
		tees.room_code as roomCode,
		tees.room_name as roomName
		from
		t_e_exam_student tees
		<where>
			<if test="roomName != null and roomName != ''">
				and tees.room_name like concat('%', #{roomName}, '%')
			</if>
		</where>
	</select>

	<select id="getExamDeficiencyPage"
		resultType="com.qmth.themis.business.bean.backend.ExamDeficiencyListBean">
		select 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_student t
		left join t_e_exam_activity f on
		t.exam_activity_id=f.id
		where t.exam_id = #{examId} and
		t.current_record_id is null
		and t.exam_activity_id in
		<foreach collection="activityIds" item="acid" index="index"
			open="(" close=")" separator=",">
			#{acid}
		</foreach>
		<if test="activityId != null and activityId != ''">
			and t.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>
	</select>
	
	<select id="getPageForStudentLog"
		resultType="com.qmth.themis.business.bean.backend.ExamStudentLogListBean">
		select t.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_student t
		where t.exam_id = #{examId} 
		<if test="activityId != null and activityId != ''">
			and t.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>
	</select>
	
	<update id="updateAlreadyExamCount">
	update t_e_exam_student set already_exam_count=#{alreadyExamCount} where id=#{examStudentId} and (already_exam_count is null or already_exam_count &lt; #{alreadyExamCount})
	</update>
	
	<update id="updateCurrentRecordId">
	update t_e_exam_student set current_record_id=#{currentRecordId} where id=#{examStudentId} and (current_record_id is null or current_record_id &lt; #{currentRecordId})
	</update>
	
</mapper>