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

	<select id="getUnFinishExam"
		resultType="com.qmth.themis.business.dto.response.TEExamUnFinishDto">
		select
		tee.id,
		teea.id as examActivityId,
		toer.id as recordId,
		tees.id as
		examStudentId,
		tee.name,
		tee.mode,
		tee.pre_notice as preNotice,
		tee.pre_notice_stay_seconds as preNoticeStaySeconds,
		tee.post_notice as
		postNotice,
		tee.code as examCode,
		teea.code as examActivityCode,
		tee.start_time as examStartTime,
		tee.end_time as examEndTime,
		teea.start_time as examActivityStartTime,
		teea.finish_time as
		examActivityFinishTime,
		tees.course_code as courseCode,
		teec.course_name as courseName,
		tee.prepare_seconds as prepareSeconds,
		tee.min_duration_seconds as minDurationSeconds,
		tee.max_duration_seconds as maxDurationSeconds,
		tee.exam_count as
		examCount,
		tee.force_finish as fouceFinish,
		tee.entry_authentication_policy as entryAuthenticationPolicy,
		tee.in_process_face_verify as inProcessFaceVerify,
		tee.in_process_face_stranger_ignore as inProcessFaceStrangerIgnore,
		tee.in_process_liveness_verify as inProcessLivenessVerify,
		tee.in_process_liveness_fixed_range as inProcessLivenessFixedRange,
		tee.in_process_liveness_judge_policy as inProcessLivenessJudgePolicy,
		tee.camera_photo_upload as cameraPhotoUpload,
		tee.mobile_photo_upload
		as mobilePhotoUpload,
		tee.break_expire_seconds as breakExpireSeconds,
		tee.break_resume_count as breakResumeCount,
		toer.duration_seconds as
		durationSeconds,
		toer.client_last_sync_time as clientLastSyncTime
		from
		t_oe_exam_record toer
		left join t_e_exam_student tees on
		tees.id =
		toer.exam_student_id
		left join t_e_exam_course teec on
		teec.course_code
		= tees.course_code
		left join t_e_exam tee on
		tee.id = tees.exam_id
		left
		join t_e_exam_activity teea on
		teea.id = tees.exam_activity_id
		<where>
			<if test="studentId != null and studentId != ''">
				and tees.student_id = #{studentId}
			</if>
			and tee.enable = 1
			and teea.enable = 1
			and tees.enable = 1
			<if test="orgId != null and orgId != ''">
				and tee.org_id = #{orgId}
			</if>
			<if test="examId != null and examId != ''">
				and tee.id = #{examId}
			</if>
		</where>
	</select>

	<update id="dataUpdate">
		update t_oe_exam_record t set
		t.${colName}=#{colValue} where t.id=#{recordId}
	</update>

	<sql id="invigilatePageHead">
		select
		distinct t.exam_id examId,
		tee.name as examName,
		t.exam_activity_id examActivityId,
		teea.code as examActivityCode,
		t.exam_student_id examStudentId,
		t.id examRecordId,
		s.identity identity,
		s.room_code roomCode,
		s.room_name roomName,
		s.name name,
		s.course_name courseName,
		s.course_code courseCode,
		IFNULL(t.paper_download,1) paperDownload,
		t.status statusCode,
	    TRUNCATE(t.answer_progress,2) progress,
		IFNULL(t.client_current_ip,'无') clientCurrentIp,
		IFNULL(t.warning_count,0) as warningCount,
		IFNULL(t.breach_status,1) as breachStatus,
		IFNULL(t.client_websocket_status,'OFF_LINE') as clientWebsocketStatus,
		IFNULL(t.monitor_status_source,'STOP') as monitorStatusSource,
		t.client_last_sync_time as updateTime
	</sql>

	<sql id="invigilatePageMiddle">
		from t_oe_exam_record t
		left join t_e_exam_student s on t.exam_student_id = s.id
		left join t_e_exam tee on tee.id = t.exam_id
		left join t_e_exam_activity teea on teea.id = t.exam_activity_id
		inner join (select toer.id from t_oe_exam_record toer where EXISTS(select
		tees.id from t_e_exam_student tees where EXISTS (select distinct
		tbeiu.room_code from t_b_exam_invigilate_user tbeiu
		where
		<if test="userId != null and userId != ''">
			tbeiu.user_id = #{userId} and
		</if>
		tbeiu.room_code = tees.room_code and toer.exam_student_id = tees.id)))
		t1 on t1.id = t.id
	</sql>

	<sql id="invigilatePageFoot">
		<where>
			<if test="examId != null and examId != ''">
				and t.exam_id = #{examId}
			</if>
			<if test="examActivityId != null and examActivityId != ''">
				and t.exam_activity_id = #{examActivityId}
			</if>
			<if test="roomCode != null and roomCode != ''">
				and s.room_code = #{roomCode}
			</if>
			<if test="status != null and status != ''">
				<choose>
					<when test="status == 'EXAMING'">
						and (t.status = 'ANSWERING' or t.status = 'RESUME_PREPARE')
					</when>
					<otherwise>
						and t.status = #{status}
					</otherwise>
				</choose>
			</if>
			<if test="name != null and name !=''">
				and s.name like CONCAT('%', #{name},'%')
			</if>
			<if test="identity != null and identity !=''">
				and s.identity like CONCAT('%', #{identity},'%')
			</if>
			<if test="maxWarningCount != null and maxWarningCount != '' or maxWarningCount == 0">
				and t.warning_count &lt;= #{maxWarningCount}
			</if>
			<if test="minWarningCount != null and minWarningCount != '' or minWarningCount == 0">
				and t.warning_count &gt;= #{minWarningCount}
			</if>
			<if test="clientWebsocketStatus != null and clientWebsocketStatus != ''">
				and t.client_websocket_status = #{clientWebsocketStatus}
			</if>
			<if test="monitorStatusSource != null and monitorStatusSource != ''">
				and t.monitor_status_source = #{monitorStatusSource}
			</if>
			and s.enable = 1
			and tee.enable = 1
			and teea.enable = 1
			and teea.finish_time > unix_timestamp(current_timestamp()) * 1000
		</where>
	</sql>

	<select id="invigilatePageList" resultType="com.qmth.themis.business.bean.backend.InvigilateListBean">
        select
        (@i := @i + 1) as seq,
        t.*
        from
        (
        <include refid="invigilatePageHead"/>
        ,(select count(1) from t_ie_invigilate_warn_info tiiwi where tiiwi.exam_record_id = t.id and
        tiiwi.approve_status = 0) as warningNew
		,date_format(date_sub(from_unixtime(IFNULL(teea.max_duration_seconds, tee.max_duration_seconds) - t.duration_seconds),INTERVAL 8 HOUR), '%H:%i:%s') as remainTime
        <include refid="invigilatePageMiddle"/>
		<include refid="invigilatePageFoot"/>
        <if test="paperDownload != null and paperDownload != '' or paperDownload == 0">
            and t.paper_download = #{paperDownload}
        </if>
		<if test="status == null or status == ''">
			and (t.status = 'FIRST_PREPARE' or t.status = 'ANSWERING' or t.status = 'BREAK_OFF' or t.status = 'RESUME_PREPARE')
		</if>
        ) t,
        (SELECT @i := 0) as i
		order by t.warningNew desc
	</select>

	<select id="invigilatePageListVideo" resultType="com.qmth.themis.business.bean.backend.InvigilateListVideoBean">
		select
		(@i := @i + 1) as seq,
		t.*
		from
		(
		<include refid="invigilatePageHead" />
		,t.monitor_live_url as monitorLiveUrl
		,(select count(1) from t_ie_invigilate_warn_info tiiwi where tiiwi.exam_record_id = t.id and tiiwi.approve_status = 0) as warningNew
		,date_format(date_sub(from_unixtime(IFNULL(teea.max_duration_seconds, tee.max_duration_seconds) - t.duration_seconds),INTERVAL 8 HOUR), '%H:%i:%s') as remainTime
		<include refid="invigilatePageMiddle" />
		<include refid="invigilatePageFoot" />
		<if test="paperDownload != null and paperDownload != '' or paperDownload == 0">
			and t.paper_download = #{paperDownload}
		</if>
		<if test="status == null or status == ''">
			and (t.status = 'FIRST_PREPARE' or t.status = 'ANSWERING' or t.status = 'BREAK_OFF' or t.status = 'RESUME_PREPARE')
		</if>
		) t,
		(SELECT @i := 0) as i
		order by t.warningNew desc
	</select>

	<select id="invigilatePageListVideoRandom" resultType="com.qmth.themis.business.bean.backend.InvigilateListVideoBean">
		<include refid="invigilatePageHead" />
		,t.monitor_live_url as monitorLiveUrl
		,(select count(1) from t_ie_invigilate_warn_info tiiwi where tiiwi.exam_record_id = t.id and tiiwi.approve_status = 0) as warningNew
		,date_format(date_sub(from_unixtime(IFNULL(teea.max_duration_seconds, tee.max_duration_seconds) - t.duration_seconds),INTERVAL 8 HOUR), '%H:%i:%s') as remainTime
		<include refid="invigilatePageMiddle" />
		<where> 1 = 1
		<if test="examId != null and examId != ''">
			and t.exam_id = #{examId}
		</if>
			and (t.status = 'FIRST_PREPARE' or t.status = 'ANSWERING' or t.status = 'BREAK_OFF' or t.status = 'RESUME_PREPARE')
		</where>
		ORDER BY RAND() LIMIT #{randomNum}
	</select>

	<select id="invigilatePagePatrolList" resultType="com.qmth.themis.business.bean.backend.InvigilateListPatrolBean">
		select (@i := @i + 1) as seq,t.* from(<include refid="invigilatePageHead" />
		,(select count(1) from t_ie_invigilate_exception_info tiiei where tiiei.exam_record_id = t.id) as exceptionCount
		,(select count(1) from t_ie_invigilate_warn_info tiiwi where tiiwi.exam_record_id = t.id and tiiwi.`type` =
		'FACE_COUNT_ERROR' and tiiwi.`level` = 'D8') as multipleFaceCount
		,date_format(date_sub(from_unixtime(IFNULL(teea.max_duration_seconds, tee.max_duration_seconds) - t.duration_seconds),INTERVAL 8 HOUR), '%H:%i:%s') as remainTime
		<include refid="invigilatePageMiddle" />
		<include refid="invigilatePageFoot" />
			<if test="status == null or status == ''">
				and (t.status = 'FIRST_PREPARE' or t.status = 'ANSWERING' or t.status = 'BREAK_OFF' or t.status = 'RESUME_PREPARE')
			</if>
		) t
		<where>
			<if test="minMultipleFaceCount != null and minMultipleFaceCount != '' or minMultipleFaceCount == 0">
				and t.multipleFaceCount &gt;= #{minMultipleFaceCount}
			</if>
			<if test="maxMultipleFaceCount != null and maxMultipleFaceCount != '' or maxMultipleFaceCount == 0">
				and t.multipleFaceCount &lt;= #{maxMultipleFaceCount}
			</if>
			<if test="minExceptionCount != null and minExceptionCount != '' or minExceptionCount == 0">
				and t.exceptionCount &gt;= #{minExceptionCount}
			</if>
			<if test="maxExceptionCount != null and maxExceptionCount != '' or maxExceptionCount == 0">
				and t.exceptionCount &lt;= #{maxExceptionCount}
			</if>
		</where>
		,(SELECT @i := 0) as i
		order by t.roomCode
	</select>

	<select id="invigilatePageWarningList" resultType="com.qmth.themis.business.bean.backend.InvigilateListWarningBean">
		select (@i := @i + 1) as seq,t.* from(select
		tiiwi.id as warningId,
		tee.id as examId,
		tee.name as examName,
		teea.id as examActivityId,
		tees.id as examStudentId,
		teea.code as examActivityCode,
		tees.room_code as roomCode,
		tees.room_name as roomName,
		toer.id as examRecordId,
		tees.`identity`,
		tees.name,
		tees.course_code as courseCode,
		tees.course_name as courseName,
		toer.status as statusCode,
		IFNULL(toer.warning_count,0) as warningCount,
		IFNULL(toer.breach_status,1) as breachStatus,
		toer.client_last_sync_time as updateTime,
		tiiwi.approve_status as approveStatus
		,(select count(1) from t_ie_invigilate_warn_info tiiwi where
		tiiwi.exam_record_id = toer.id and tiiwi.`type` =
		'FACE_COUNT_ERROR' and tiiwi.`level` = 'D8') as multipleFaceCount
		,(select count(1) from t_ie_invigilate_exception_info tiiei where
		tiiei.exam_record_id = toer.id) as exceptionCount
		from t_ie_invigilate_warn_info tiiwi
		left join t_e_exam tee on tee.id = tiiwi.exam_id
		left join t_e_exam_activity teea on teea.id = tiiwi.exam_activity_id
		inner join (select toer.id from t_oe_exam_record toer where EXISTS(select
		tees.id from t_e_exam_student tees where EXISTS (select distinct
		tbeiu.room_code from t_b_exam_invigilate_user tbeiu
		where
		<if test="userId != null and userId != ''">
			tbeiu.user_id = #{userId} and
		</if>
		tbeiu.room_code = tees.room_code and toer.exam_student_id = tees.id)))
		t on t.id = tiiwi.exam_record_id
		left join t_oe_exam_record toer on toer.id = t.id
		left join t_e_exam_student tees on tees.id = tiiwi.exam_student_id
		<where>
			<if test="examId != null and examId != ''">
				and tiiwi.exam_id = #{examId}
			</if>
			<if test="examActivityId != null and examActivityId != ''">
				and tiiwi.exam_activity_id = #{examActivityId}
			</if>
			<if test="roomCode != null and roomCode != ''">
				and tees.room_code = #{roomCode}
			</if>
			<if test="approveStatus != null and approveStatus != '' or approveStatus == 0">
				and tiiwi.approve_status = #{approveStatus}
			</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="maxWarningCount != null and maxWarningCount != '' or maxWarningCount == 0">
				and toer.warning_count &lt;= #{maxWarningCount}
			</if>
			<if test="minWarningCount != null and minWarningCount != '' or minWarningCount == 0">
				and toer.warning_count &gt;= #{minWarningCount}
			</if>
			<if
				test="clientWebsocketStatus != null and clientWebsocketStatus != ''">
				and toer.client_websocket_status = #{clientWebsocketStatus}
			</if>
			<if
				test="monitorStatusSource != null and monitorStatusSource != ''">
				and toer.monitor_status_source = #{monitorStatusSource}
			</if>
			and tee.enable = 1
			and teea.enable = 1
			and tees.enable = 1
		</where>
		) t,(SELECT @i := 0) as i
		<where>
			<if test="minMultipleFaceCount != null and minMultipleFaceCount != '' or minMultipleFaceCount == 0">
				and t.multipleFaceCount &gt;= #{minMultipleFaceCount}
			</if>
			<if test="maxMultipleFaceCount != null and maxMultipleFaceCount != '' or maxMultipleFaceCount == 0">
				and t.multipleFaceCount &lt;= #{maxMultipleFaceCount}
			</if>
			<if test="minExceptionCount != null and minExceptionCount != '' or minExceptionCount == 0">
				and t.exceptionCount &gt;= #{minExceptionCount}
			</if>
			<if test="maxExceptionCount != null and maxExceptionCount != '' or maxExceptionCount == 0">
				and t.exceptionCount &lt;= #{maxExceptionCount}
			</if>
		</where>
		order by t.roomCode
	</select>

	<select id="invigilatePageProgressList" resultType="com.qmth.themis.business.bean.backend.InvigilateListProgressBean">
		select
		distinct tee.id as examId,
		tee.name as examName,
		teea.id as examActivityId,
		teea.code as examActivityCode,
		tees.room_code as roomCode,
		tees.room_name as roomName,
		tees.`identity`,
		tees.name,
		tees.course_code as courseCode,
		tees.course_name as courseName,
		(tee.exam_count - tees.already_exam_count) as leftExamCount,
		if((select count(1) from t_oe_exam_record toer where toer.exam_student_id =
		tees.id and (toer.status = 'FINISHED' or toer.status = 'PERSISTED') >
		0),'已完成','未完成') as status,
		tes.mobile_number as mobileNumber
		from
		t_e_exam_student tees
		left join t_e_exam tee on
		tee.id = tees.exam_id
		left join t_e_exam_activity teea on
		teea.id = tees.exam_activity_id
		left join t_e_student tes on tees.student_id = tes.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 tee.enable = 1
			and teea.enable = 1
			and tees.enable = 1
		</where>
		order by tees.room_code
	</select>

	<select id="invigilatePageProgressListExport" resultType="com.qmth.themis.business.bean.backend.InvigilateListProgressExcelBean">
		select
		distinct tee.id as examId,
		tee.name as examName,
		teea.id as examActivityId,
		teea.code as examActivityCode,
		tees.room_code as roomCode,
		group_concat(tees.room_name,CONCAT(CONCAT('(',tees.room_code),')')) as roomName,
		tees.`identity`,
		tees.name,
		group_concat(tees.course_name,CONCAT(CONCAT('(',tees.course_code),')')) as courseName,
		(tee.exam_count - tees.already_exam_count) as leftExamCount,
		if((select count(1) from t_oe_exam_record toer where toer.exam_student_id =
		tees.id and (toer.status = 'FINISHED' or toer.status = 'PERSISTED') >
		0),'已完成','未完成') as status,
		tes.mobile_number as mobileNumber
		from
		t_e_exam_student tees
		left join t_e_exam tee on
		tee.id = tees.exam_id
		left join t_e_exam_activity teea on
		teea.id = tees.exam_activity_id
		left join t_e_student tes on tees.student_id = tes.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 tee.enable = 1
			and teea.enable = 1
			and tees.enable = 1
		</where>
		group by tee.id,tees.room_code,tee.name,teea.id,teea.code,tees.`identity`,tees.name,leftExamCount,status,tes.mobile_number
		order by tees.room_code
	</select>

	<select id="invigilatePageListHistory" resultType="com.qmth.themis.business.bean.backend.InvigilateListHistoryBean">
		select (@i := @i + 1) as seq,t.* from(
		<include refid="invigilatePageHead" />
		,tes.mobile_number as mobileNumber
		,t.finish_type as finishType
		,(select count(1) from t_ie_invigilate_warn_info tiiwi where tiiwi.exam_record_id = t.id and tiiwi.`type` =
		'FACE_COUNT_ERROR' and tiiwi.`level` = 'D8') as multipleFaceCount
		,(select count(1) from t_ie_invigilate_exception_info tiiei where tiiei.exam_record_id = t.id) as exceptionCount
		<include refid="invigilatePageMiddle" />
		left join t_e_student tes on s.student_id = tes.id
		<include refid="invigilatePageFoot" />
		<if test="breachStatus != null and breachStatus != '' or breachStatus == 0">
			and t.breach_status = #{breachStatus}
		</if>
		<if test="finishType != null and finishType != ''">
			and t.finish_type = #{finishType}
		</if>
		<if test="courseCode != null and courseCode != ''">
			and s.course_code like CONCAT('%', #{courseCode},'%')
		</if>
		) t,
		(SELECT @i := 0) as i
		order by t.roomCode
	</select>

	<select id="getDoneCount" resultType="java.util.Map">
		select f.exam_activity_id
		activityId,count(distinct(f.exam_student_id)) cc from t_oe_exam_record
		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>
		group by f.exam_activity_id
	</select>

	<select id="getDoneCountByDay" resultType="java.util.Map">
		select
		FROM_UNIXTIME(f.first_prepare_time / 1000,'%Y-%m-%d') AS day,
		count(DISTINCT(f.exam_student_id)) count from t_oe_exam_record 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>
		group by day
		ORDER BY day
	</select>

	<select id="getCountByExamId" resultType="java.lang.Long">
		select count(1) from t_oe_exam_record f
		where f.exam_id = #{examId}
	</select>

	<select id="findOneByExamId" resultType="com.qmth.themis.business.entity.TOeExamRecord">
		select f.* from t_oe_exam_record f
		<where>
			<if test="examId != null and examId != ''">
				and f.exam_id = #{examId}
			</if>
			<if test="status != null and status != ''">
				and f.status = #{status}
			</if>
		</where>
		limit 1
	</select>

	<select id="getListByExamIdAndStartId" resultType="com.qmth.themis.business.entity.TOeExamRecord">
		select f.* from t_oe_exam_record f
		where f.exam_id = #{examId} and f.id>#{startId}
		order by f.id
		limit 500
	</select>

	<update id="updateObjectiveScore">
		update t_oe_exam_record t set t.objective_score=#{score} where t.id=#{recordId}
	</update>
	
	<select id="findOneByPaperId" resultType="com.qmth.themis.business.entity.TOeExamRecord">
		select f.* from t_oe_exam_record f
		where f.paper_id = #{paperId}
		limit 1
	</select>

	<select id="patrolReport" resultType="com.qmth.themis.business.bean.backend.InvigilateListPatrolReportBean">
		select
		t.roomCode,
		t.roomName,
		sum(t.warningCount) as warningCount,
		count(t.client_websocket_status) as clientWebsocketStatusCount
		from
		(
		select
		s.room_code roomCode, s.room_name roomName, IFNULL(t.warning_count,
		0) as warningCount, t.client_websocket_status
		from
		t_oe_exam_record t
		left join t_e_exam_student s on
		t.exam_student_id = s.id
		left join t_e_exam tee on
		tee.id = t.exam_id
		left join t_e_exam_activity teea on
		teea.id = t.exam_activity_id
		inner join (
		select
		toer.id
		from
		t_oe_exam_record toer
		where
		EXISTS(
		select
		tees.id
		from
		t_e_exam_student tees
		where
		EXISTS (
		select
		distinct tbeiu.room_code
		from
		t_b_exam_invigilate_user tbeiu
		where
		<if test="userId != null and userId != ''">
			tbeiu.user_id = #{userId} and
		</if>
		tbeiu.room_code = tees.room_code
		and toer.exam_student_id = tees.id))) t1 on
		t1.id = t.id
		<where> 1 = 1
			<if test="examId != null and examId != ''">
				and t.exam_id = #{examId}
			</if>
			and s.enable = 1
			and tee.enable = 1
			and teea.enable = 1
			and teea.finish_time > unix_timestamp(current_timestamp()) * 1000
			and (t.status = 'FIRST_PREPARE' or t.status = 'ANSWERING' or t.status = 'BREAK_OFF' or t.status = 'RESUME_PREPARE')
		</where> ) t
		group by
		t.roomCode,
		t.roomName
		order by t.roomCode
	</select>

	<select id="getExamingCount" resultType="java.lang.Long">
		select count(1) from t_oe_exam_record f
		where f.status != 'FINISHED' and f.status != 'PERSISTED'
	</select>
	<select id="getExceptionCount" resultType="java.lang.Long">
			SELECT
			count(DISTINCT(f.id))
		FROM
			t_oe_exam_record f
		LEFT JOIN t_ie_invigilate_exception_info t ON f.id = t.exam_record_id
		WHERE
			f. STATUS != 'FINISHED'
		AND f. STATUS != 'PERSISTED'
		AND t.id IS NOT NULL
	</select>
	<select id="getWarnCount" resultType="java.lang.Long">
		SELECT
			count(DISTINCT(f.id))
		FROM
			t_oe_exam_record f
		LEFT JOIN t_ie_invigilate_warn_info t ON f.id = t.exam_record_id
		WHERE
			f. STATUS != 'FINISHED'
		AND f. STATUS != 'PERSISTED'
		AND t.type!='NONE'
		AND t.id IS NOT NULL
	</select>
	<select id="getOrgExamingCount" resultType="java.util.Map">
		SELECT
		o. NAME orgName,
		tem.cc count
	FROM
		(
			SELECT
				h.org_id orgId,
				count(DISTINCT(h.id)) cc
			FROM
				t_oe_exam_record f
			LEFT JOIN t_e_exam_student t ON f.exam_student_id = t.id
			LEFT JOIN t_e_student h ON t.student_id = h.id
			WHERE
				f. STATUS != 'FINISHED'
			AND f. STATUS != 'PERSISTED'
			GROUP BY
				h.org_id
		) tem
	LEFT JOIN t_b_org o ON o.id = tem.orgId
	</select>
	<select id="getOrgDistribution" resultType="java.util.Map">
		SELECT
		o. NAME orgName,
		tem.cc count
		FROM
			(
				SELECT
					h.org_id orgId,
					count(DISTINCT(w.id)) cc
				FROM
					t_oe_exam_record f
				LEFT JOIN t_ie_invigilate_warn_info w ON f.id = w.exam_record_id
				LEFT JOIN t_e_exam_student t ON f.exam_student_id = t.id
				LEFT JOIN t_e_student h ON t.student_id = h.id
				WHERE
					f. STATUS != 'FINISHED'
				AND f. STATUS != 'PERSISTED'
				AND w.id IS NOT NULL
				AND w.type!='NONE'
				GROUP BY
					h.org_id
			) tem
		LEFT JOIN t_b_org o ON o.id = tem.orgId
	</select>
	<select id="getTypeDistribution" resultType="java.util.Map">
		SELECT
			w.type type,
			count(DISTINCT(w.id)) count
		FROM
			t_oe_exam_record f
		LEFT JOIN t_ie_invigilate_warn_info w ON f.id = w.exam_record_id
		WHERE
			f. STATUS != 'FINISHED'
		AND f. STATUS != 'PERSISTED'
		AND w.id IS NOT NULL
		AND w.type!='NONE'
		GROUP BY
			w.type
	</select>
	<select id="getWarnTrend" resultType="com.qmth.themis.business.bean.backend.ExaminationMonitorHourWarnCountBean">
			SELECT
			FROM_UNIXTIME(w.create_time / 1000,'%H') hour,
			count(1) count
		FROM  t_ie_invigilate_warn_info w 
		WHERE w.type!='NONE' and w.create_time&gt;=#{startTime}
		GROUP BY
			FROM_UNIXTIME(w.create_time / 1000,'%H')
	</select>
</mapper>
