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

    <select id="examList" resultType="java.util.HashMap">
        select
        t.id as examId
        from
        t_e_exam t
        <where>
            and t.start_time <![CDATA[ <= ]]> unix_timestamp(current_timestamp()) * 1000
            and t.end_time <![CDATA[ >= ]]> unix_timestamp(current_timestamp()) * 1000
            and t.enable = 1
            <if test="orgId != null and orgId != ''">
                and t.org_id = #{orgId}
            </if>
            <if test="userId != null and userId != ''">
                and EXISTS(
                select
                DISTINCT tbeiu.exam_id
                from
                t_b_exam_invigilate_user tbeiu
                left join t_e_exam_student tees on
                tees.room_code = tbeiu.room_code
                where
                tbeiu.user_id = #{userId}
                and t.id = tbeiu.exam_id)
            </if>
        </where>
    </select>

    <select id="examQuery" resultType="com.qmth.themis.business.dto.response.TEExamQueryDto">
        select
        t.id,
        t.code,
        t.name,
        t.tag,
        t.mode,
        t.enable,
        t.startTime,
        t.endTime,
        t.progress,
        if(t.updateName is not null, t.updateName, t.createName) as updateName,
        if(t.updateTime is not null, t.updateTime, t.createTime) as updateTime
        from
        (select t.id,
        t.code,
        t.name,
        t.tag,
        t.mode,
        t.enable,
        t.start_time as startTime,
        t.end_time as endTime,
        t.create_time as createTime,
        t.update_time as updateTime,
        TRUNCATE(t.progress,2) as progress,
        (
        select
        t1.name
        from
        t_b_user t1
        where
        t1.id = t.create_id) as createName, (
        select
        t1.name
        from
        t_b_user t1
        where
        t1.id = t.update_id) as updateName
        from t_e_exam t
        <where> t.monitor_status !='FINISHED'
            <if test="userId != null and userId != ''">
                and t.start_time <![CDATA[ <= ]]> unix_timestamp(current_timestamp()) * 1000
                and t.end_time <![CDATA[ >= ]]> unix_timestamp(current_timestamp()) * 1000
                and t.enable = 1
                and EXISTS(
                select
                DISTINCT tbeiu.exam_id
                from
                t_b_exam_invigilate_user tbeiu
                left join t_e_exam_student tees on
                tees.room_code = tbeiu.room_code
                where
                tbeiu.user_id = #{userId}
                and t.id = tbeiu.exam_id)
            </if>
            <if test="orgId != null and orgId != ''">
                and t.org_id = #{orgId}
            </if>
            <if test="id != null and id != ''">
                and t.id = #{id}
            </if>
            <if test="code != null and code != ''">
                and t.code like concat('%', #{code}, '%')
            </if>
            <if test="name != null and name != ''">
                and t.name like concat('%', #{name}, '%')
            </if>
            <if test="mode != null and mode != '' or mode == 0">
                and t.mode = #{mode}
            </if>
            <if test="enable != null and enable != '' or enable == 0">
                and t.enable = #{enable}
            </if>
        </where> ) t order by t.createTime desc
    </select>

    <select id="getWaitingExam" resultType="com.qmth.themis.business.dto.response.TEExamDto">
        select
        t.*
        from
        (
            select
            distinct tee.id, teea.id as examActivityId,tee.name, tee.mode, tee.pre_notice as preNotice, tee.pre_notice_stay_seconds as
            preNoticeStaySeconds, tee.post_notice as postNotice,
            <!--FLOOR((teea.finish_time - teea.start_time) / (24*60*60*1000)) as diffSum1,
            FLOOR(((teea.finish_time - teea.start_time) / (24*60*60*1000) - (unix_timestamp(current_timestamp()) * 1000 - teea.start_time) / (24*60*60*1000))) as reallyTime1,-->
            datediff(FROM_UNIXTIME(teea.finish_time / 1000,'%Y-%m-%d %H:%i:%s'),FROM_UNIXTIME(teea.start_time / 1000,'%Y-%m-%d %H:%i:%s')) as diffSum,
            datediff(FROM_UNIXTIME(teea.finish_time / 1000,'%Y-%m-%d %H:%i:%s'),FROM_UNIXTIME(teea.start_time / 1000,'%Y-%m-%d %H:%i:%s')) -
            datediff(now(),FROM_UNIXTIME(teea.start_time / 1000,'%Y-%m-%d %H:%i:%s')) as reallyTime
        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
        <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>
            <!--and IF(tee.mode = 'TOGETHER', teea.start_time >= date_sub(now(),
            interval IFNULL(teea.opening_seconds, tee.opening_seconds) second),1 = 1 )-->
        </where>
        ) t
        <where>
            t.reallyTime <![CDATA[ <= ]]> diffSum + 15
            and t.reallyTime <![CDATA[ >= ]]> 0
        </where>
    </select>

    <select id="examPrivilegeQuery" resultType="com.qmth.themis.business.entity.TEExam">
        select
        *
        from
        t_e_exam tee
        where
            EXISTS(
            select
                DISTINCT tbeiu.exam_id
            from
                t_b_exam_invigilate_user tbeiu
            left join t_e_exam_student tees on
                tees.room_code = tbeiu.room_code
            <where> 1 = 1
                <if test="userId != null and userId != ''">
                    and tbeiu.user_id = #{userId}
                </if>
                and tee.id = tbeiu.exam_id
            </where>
                )
            order by tee.create_time desc
        </select>
    <update id="updateInvigilateMonitorStatus">
    	update t_e_exam set monitor_status=#{monitorStatus} where id=#{examId}
    </update>
</mapper>
