<?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>

</mapper>
