<?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.template.mapper.SmartFaceDiscernMapper">

    <select id="faceManagement" resultType="com.template.model.vo.FaceManagementVo">
        SELECT
        sfd.id,
        sfd.`name`,
        sc.`name` as className,
        sg.`name` as gradeName,
        sfd.type,
        sfd.location,
        sfd.image,
        sfd.date_time AS dateTime
        FROM
        `smart_face_discern` sfd
        LEFT JOIN smart_user su ON sfd.user_id = su.id
        LEFT JOIN smart_class sc ON su.school_class = sc.id
        LEFT JOIN smart_grade sg ON sc.grade_id = sg.id
        WHERE
        sfd.deleted =0
        <if test="keyWord != null and keyWord != ''">
            and (sfd.`name` like '%' #{keyWord} '%' or sfd.location like '%' #{keyWord} '%')
        </if>
        <if test="gradeId != null and gradeId != ''">
            and sc.grade_id= #{gradeId}
        </if>
        <if test="classId != null and classId != ''">
            and su.school_class= #{classId}
        </if>
        <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
            and sfd.date_time &gt;= #{startTime} and sfd.date_time &lt;= #{endTime}
        </if>
        ORDER BY sfd.date_time desc
    </select>

    <select id="faceManagementExport" resultType="com.template.model.vo.FaceManagementVo">
        SELECT
        sfd.id,
        sfd.`name`,
        sc.`name` as className,
        sg.`name` as gradeName,
        sfd.type,
        sfd.location,
        sfd.image,
        sfd.date_time AS dateTime
        FROM
        `smart_face_discern` sfd
        LEFT JOIN smart_user su ON sfd.user_id = su.id
        LEFT JOIN smart_class sc ON su.school_class = sc.id
        LEFT JOIN smart_grade sg ON sc.grade_id = sg.id
        WHERE
        sfd.deleted =0
        <if test="keyWord != null and keyWord != ''">
            and (sfd.`name` like '%' #{keyWord} '%' or sfd.location like '%' #{keyWord} '%')
        </if>
        <if test="gradeId != null and gradeId != ''">
            and sc.grade_id= #{gradeId}
        </if>
        <if test="classId != null and classId != ''">
            and su.school_class= #{classId}
        </if>
        <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
            and sfd.date_time &gt;= #{startTime} and sfd.date_time &lt;= #{endTime}
        </if>
        ORDER BY sfd.date_time desc
    </select>


    <resultMap type="com.template.model.vo.FaceDiscernVo" id="faceCountMap">
        <result property="dateStr" column="date_str"/>
        <result property="count" column="count"/>
    </resultMap>

    <select id="queryFaceCountByDate" resultType="com.template.model.vo.FaceDiscernVo" resultMap="faceCountMap">
        select DATE_FORMAT(date_time,'%Y-%m-%d') as date_str,Count(*) as count from smart_access
        WHERE deleted=0 AND date_time BETWEEN #{startTime} AND #{endTime}
        group by DATE_FORMAT(date_time,'%Y-%m-%d')
    </select>
    <select id="toDateList" resultType="com.template.model.pojo.SmartFaceDiscern">
        SELECT
            sfd.*
        FROM
            `smart_face_discern` sfd
                LEFT JOIN smart_user su ON sfd.user_id = su.id
        WHERE
            sfd.deleted = 0
          AND su.identity_id = 2
          AND sfd.date_time >= #{start}
          AND #{end} >= sfd.date_time
    </select>
    <select id="toDateAndPlacelist" resultType="com.template.model.pojo.SmartFaceDiscern">
        SELECT
            sfd.*
        FROM
            `smart_face_discern` sfd
                LEFT JOIN smart_user su ON sfd.user_id = su.id
        WHERE
            sfd.deleted = 0
          AND su.identity_id = 2
          AND sfd.date_time >= #{startTime}
          AND #{endTime} >= sfd.date_time
          AND sfd.location=#{location}
    </select>
</mapper>
