<?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.yx.face.dao.FaceLogDao">


    <select id="getFaceLogs" resultType="com.yx.face.model.vo.FaceLogVO">
        SELECT log.id AS logId,
        log.recordid AS recordid,
        log.action AS action,
        log.device_sn AS deviceSn,
        fd.name AS deviceName,
        log.user_id AS userId,
        replace(log.`name`,' ','') AS name,
        log.phone AS phone,
        log.photo AS photo,
        log.cardid AS cardid,
        log.cardidex AS cardidex,
        log.verify_type AS verifyType,
        log.rightno AS rightno,
        log.tw_status AS twStatus,
        log.jkm_status AS jkmStatus,
        log.hs_status AS hsStatus,
        log.ym_status AS ymStatus,
        log.xc_info AS xcInfo,
        log.extend3 AS extend3,
        fd.pass AS outType,
        log.face_time AS faceTime,
        log.create_time AS createTime
        FROM face_log AS log
        LEFT JOIN face_device fd on log.device_sn = fd.sn
        left join admin a on a.id =log.admin_id
        <where>
            <if test="adminVo.type == 1">

            </if>
            <if test="adminVo.type == 2">
                and a.province_id = #{adminVo.provinceId}
                and a.type =5
            </if>
            <if test="adminVo.type == 3">
                and a.province_id = #{adminVo.provinceId}
                and a.type =5
                and a.tag_id = #{adminVo.tagId}
            </if>
            <if test="adminVo.type == 4">
                and a.city_id = #{adminVo.cityId}
                and a.type = 5
                and a.tag_id = #{adminVo.tagId}
            </if>
            <if test="adminVo.type == 5">
                and a.id =#{adminVo.adminId}
            </if>

            <if test="query.username != null and query.username !=''">
                and a.username = #{query.username}
            </if>
            <if test="query.provinceId != null">
                and a.province_id =#{query.provinceId}
            </if>
            <if test="query.cityId != null">
                and a.city_id =#{query.cityId}
            </if>
            <if test="query.areaId != null">
                and a.area_id =#{query.areaId}
            </if>


            <!-- 体温 -->
            <if test="query.twStatus != null and query.twStatus !=''">
                <if test="query.twStatus == '1'.toString()">
                    and replace(log.tw_status,' ','') = '0'
                </if>
                <if test="query.twStatus == '2'.toString()">
                    and replace(log.tw_status,' ','') = '1'
                </if>
                <if test="query.twStatus == '3'.toString()">
                    and replace(log.tw_status,' ','') = ''
                </if>
            </if>
            <!-- 健康码状态 -->
            <if test="query.jkmStatus != null and query.jkmStatus !=''">
                <if test="query.jkmStatus == 1">
                    and log.jkm_status = '00'
                </if>
                <if test="query.jkmStatus == 2">
                    and log.jkm_status = '01'
                </if>
                <if test="query.jkmStatus == 3">
                    and log.jkm_status = '10'
                </if>
                <if test="query.jkmStatus == 4">
                    and log.jkm_status = ''
                </if>

            </if>
            <!-- 核酸 -->
            <if test="query.hsStatus != null and query.hsStatus !=''">
                <if test="query.hsStatus == 1">
                    and log.hs_status LIKE CONCAT('%','阴性','%')
                </if>
                <if test="query.hsStatus == 2">
                    and log.hs_status LIKE CONCAT('%','阳性','%')
                </if>
                <if test="query.hsStatus == 3">
                    and log.hs_status =''
                </if>
            </if>
            <!-- 疫苗状态 -->
            <if test="query.ymStatus != null and query.ymStatus !=''">
                and right(log.ym_status,1) = #{query.ymStatus}
            </if>


            <if test="query.name != null and query.name != ''">
                AND replace(log.`name`,' ','') LIKE CONCAT('%',#{query.name},'%')
            </if>
            <if test="query.phone != null and query.phone != ''">
                AND log.phone = #{query.phone}
            </if>
            <if test="query.idNumber != null and query.idNumber != ''">
                AND log.cardid = #{query.idNumber}
            </if>
            <if test="query.deviceSn != null and query.deviceSn.size() > 0">
                AND log.device_sn in
                <foreach collection="query.deviceSn" index="index" item="sn" open="(" close=")" separator=",">
                    #{sn}
                </foreach>
            </if>
            <if test="query.startTime != null and query.startTime != '' and query.endTime != null and query.endTime != '' ">
                AND  <![CDATA[ log.face_time >= #{query.startTime}]]>
                AND  <![CDATA[ log.face_time < #{query.endTime}]]>
            </if>
            <if test="query.startTime == '' and query.endTime != null and query.endTime != '' ">
                AND  <![CDATA[ log.face_time <= #{query.endTime}]]>
            </if>

            <if test="biggerNum != null ">
                AND log.id > #{biggerNum}
            </if>
        </where>
        ORDER BY log.id DESC
    </select>

    <select id="getFaceLogsShowData" resultType="com.yx.face.model.vo.FaceLogVO">
        SELECT log.id AS logId,
        log.tw_status AS twStatus,
        log.jkm_status AS jkmStatus,
        log.hs_status AS hsStatus,
        log.ym_status AS ymStatus,
        log.face_time AS faceTime,
        log.create_time AS createTime
        FROM face_log AS log
        LEFT JOIN admin a on a.id =log.admin_id
        <where>
            <if test="adminVo.type == 1">

            </if>
            <if test="adminVo.type == 2">
                and a.province_id = #{adminVo.provinceId}
                and a.type =5
            </if>
            <if test="adminVo.type == 3">
                and a.province_id = #{adminVo.provinceId}
                and a.type =5
                and a.tag_id = #{adminVo.tagId}
            </if>
            <if test="adminVo.type == 4">
                and a.city_id = #{adminVo.cityId}
                and a.type = 5
                and a.tag_id = #{adminVo.tagId}
            </if>
            <if test="adminVo.type == 5">
                and a.id =#{adminVo.adminId}
            </if>
            <if test="biggerNum != null ">
                AND log.id > #{biggerNum}
            </if>
            <if test="todayTime != null ">
                AND log.face_time >= #{todayTime}
            </if>
        </where>
        ORDER BY log.id asc
    </select>
    <select id="getCount" resultType="java.lang.Integer">
        select
        count(1)
        FROM face_log AS log
        LEFT JOIN admin a on a.id =log.admin_id
        <where>
            <if test="adminVo.type == 1">

            </if>
            <if test="adminVo.type == 2">
                and a.province_id = #{adminVo.provinceId}
                and a.type =5
            </if>
            <if test="adminVo.type == 3">
                and a.province_id = #{adminVo.provinceId}
                and a.type =5
                and a.tag_id = #{adminVo.tagId}
            </if>
            <if test="adminVo.type == 4">
                and a.city_id = #{adminVo.cityId}
                and a.type = 5
                and a.tag_id = #{adminVo.tagId}
            </if>
            <if test="adminVo.type == 5">
                and a.id =#{adminVo.adminId}
            </if>
            <if test="todayTime != null ">
                AND log.face_time &lt; #{todayTime}
            </if>
        </where>
    </select>

    <select id="getRecent" resultType="java.util.Date">
        SELECT create_time
        FROM face_log
        WHERE user_id = #{userId}
          AND device_sn = #{sn}
        ORDER BY id DESC LIMIT 0,1
    </select>
    <select id="getFaceLogsExcel" resultType="com.yx.face.model.excel.FaceLogVoExcel">
        SELECT
        log.id AS logId,
        log.recordid AS recordid,
        log.action AS action,
        log.device_sn AS deviceSn,
        fd.NAME AS deviceName,
        log.user_id AS userId,
        log.`name` AS NAME,
        ui.phone AS phone,
        log.photo AS photo,
        log.cardid AS cardid,
        log.cardidex AS cardidex,
        log.face_time AS faceTime,
        log.create_time AS createTime,
        ( CASE fd.pass WHEN 0 THEN '通用' WHEN 1 THEN '进门' WHEN 2 THEN '出门' ELSE '其他' END ) AS outType,
        (
        CASE
        verify_type
        WHEN 0 THEN
        '人脸'
        WHEN 1 THEN
        '卡'
        WHEN 2 THEN
        '身份证'
        WHEN 3 THEN
        '二维码'
        WHEN 4 THEN
        '远程'
        WHEN 6 THEN
        'IC卡+人脸' ELSE '其他'
        END
        ) AS verifyType,
        (
        CASE
        rightno
        WHEN 106 THEN
        '管理员'
        WHEN 105 THEN
        '操作员'
        WHEN 104 THEN
        '普通用户'
        WHEN 103 THEN
        '普通用户'
        WHEN 102 THEN
        '普通用户'
        WHEN 101 THEN
        '普通用户' ELSE '其他'
        END
        ) AS rightno
        FROM
        face_log AS log
        LEFT JOIN user_info ui ON log.user_id = ui.id
        LEFT JOIN face_device fd ON log.device_sn = fd.sn
        <where>
            <if test="query != null">
                <if test="query.username != null and query.username != ''">
                    AND ui.username = #{query.username}
                </if>
                <if test="query.phone != null and query.phone != ''">
                    AND ui.phone = #{query.phone}
                </if>
                <if test="query.deviceSn != null and query.deviceSn != ''">
                    AND log.device_sn = #{query.deviceSn}
                </if>
            </if>
        </where>
        ORDER BY log.id DESC
    </select>
    <select id="getYellow" resultType="com.yx.face.model.vo.FaceLogVO">
        select
            id as logId,
            device_sn AS deviceSn,
            face_time AS faceTime
        from face_log
        where
            face_time > #{faceTime}
          and jkm_status=#{jkm}
          and admin_id = #{adminId}
    </select>
    <select id="getDetails" resultType="com.yx.face.model.vo.FaceLogVO">
        SELECT log.id       AS logId,
               log.recordid AS recordid,
               log.action AS action,
        log.device_sn AS deviceSn,
        fd.name AS deviceName,
        log.user_id AS userId,
        replace(log.`name`,' ','') AS name,
        ui.phone AS phone,
        log.photo AS photo,
        log.cardid AS cardid,
        log.cardidex AS cardidex,
        log.verify_type AS verifyType,
        log.rightno AS rightno,
        log.tw_status AS twStatus,
        log.jkm_status AS jkmStatus,
        log.hs_status AS hsStatus,
        log.ym_status AS ymStatus,
        log.xc_info AS xcInfo,
        log.extend3 AS extend3,
        fd.pass AS outType,
        log.face_time AS faceTime,
        log.create_time AS createTime
        FROM face_log AS log
            LEFT JOIN user_info ui
        on log.user_id = ui.id
            LEFT JOIN face_device fd on log.device_sn = fd.sn
            left join admin a on a.id =log.admin_id
        where log.id = #{id}
    </select>
    <select id="getFaceLogsNew" resultType="com.yx.face.model.vo.FaceLogVO">
        SELECT log.id AS logId,
        log.recordid AS recordid,
        log.action AS action,
        log.device_sn AS deviceSn,
        log.user_id AS userId,
        replace(log.`name`,' ','') AS name,
        log.phone AS phone,
        log.photo AS photo,
        log.cardid AS cardid,
        log.cardidex AS cardidex,
        log.verify_type AS verifyType,
        log.rightno AS rightno,
        log.tw_status AS twStatus,
        log.jkm_status AS jkmStatus,
        log.hs_status AS hsStatus,
        log.ym_status AS ymStatus,
        log.xc_info AS xcInfo,
        log.extend3 AS extend3,
        log.face_time AS faceTime,
        log.create_time AS createTime,
        log.admin_id AS adminId
        FROM face_log AS log
        <where>
            <!-- 体温 -->
            <if test="query.twStatus != null and query.twStatus !=''">
                <if test="query.twStatus == '1'.toString()">
                    and replace(log.tw_status,' ','') = '0'
                </if>
                <if test="query.twStatus == '2'.toString()">
                    and replace(log.tw_status,' ','') = '1'
                </if>
                <if test="query.twStatus == '3'.toString()">
                    and replace(log.tw_status,' ','') = ''
                </if>
            </if>
            <!-- 健康码状态 -->
            <if test="query.jkmStatus != null and query.jkmStatus !=''">
                <if test="query.jkmStatus == 1">
                    and log.jkm_status = '00'
                </if>
                <if test="query.jkmStatus == 2">
                    and log.jkm_status = '01'
                </if>
                <if test="query.jkmStatus == 3">
                    and log.jkm_status = '10'
                </if>
                <if test="query.jkmStatus == 4">
                    and log.jkm_status = ''
                </if>

            </if>
            <!-- 核酸 -->
            <if test="query.hsStatus != null and query.hsStatus !=''">
                <if test="query.hsStatus == 1">
                    and log.hs_status LIKE CONCAT('%','阴性','%')
                </if>
                <if test="query.hsStatus == 2">
                    and log.hs_status LIKE CONCAT('%','阳性','%')
                </if>
                <if test="query.hsStatus == 3">
                    and log.hs_status =''
                </if>
            </if>
            <!-- 疫苗状态 -->
            <if test="query.ymStatus != null and query.ymStatus !=''">
                and right(log.ym_status,1) = #{query.ymStatus}
            </if>


            <if test="query.name != null and query.name != ''">
                AND replace(log.`name`,' ','') LIKE CONCAT('%',#{query.name},'%')
            </if>
            <if test="query.phone != null and query.phone != ''">
                AND log.phone = #{query.phone}
            </if>
            <if test="query.idNumber != null and query.idNumber != ''">
                AND log.cardid = #{query.idNumber}
            </if>
            <if test="query.deviceSn != null and query.deviceSn.size() >0">
                AND log.device_sn in
                <foreach collection="query.deviceSn" item="sn" open="(" close=")" separator=",">
                    #{sn}
                </foreach>
            </if>
            <if test="query.startTime != null and query.startTime != '' and query.endTime != null and query.endTime != '' ">
                AND  <![CDATA[ log.face_time >= #{query.startTime}]]>
                AND  <![CDATA[ log.face_time < #{query.endTime}]]>
            </if>
            <if test="query.startTime == '' and query.endTime != null and query.endTime != '' ">
                AND  <![CDATA[ log.face_time <= #{query.endTime}]]>
            </if>

            <if test="biggerNum != null ">
                AND log.id > #{biggerNum}
            </if>
            <if test="adminIds !=null and adminIds.size()> 0">
                and  log.admin_id in
                <foreach collection="adminIds" index="index" item="adminId" open="(" close=")" separator=",">
                    #{adminId}
                </foreach>
            </if>

        </where>
        ORDER BY log.id DESC
    </select>
</mapper>

