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

    <select id="getPage" resultType="com.template.model.vo.SmartAccessVo">
        SELECT
        sa.id,
        sa.name,
        sa.sn,
        sa.type,
        sd.`name` as departmentName,
        sc.`name` as className,
        su.card_no as cardNo,
        sa.image,
        sa.open_type as openType,
        sa.date_time as dateTime,
        sa.in_out as access,
        sa.result_status as resultStatus
        FROM
        `smart_access` sa
        LEFT JOIN smart_user su on sa.user_id=su.id
        LEFT JOIN smart_department sd on su.department_id=sd.id
        LEFT JOIN smart_class sc on su.school_class=sc.id
        LEFT JOIN smart_grade sg on sc.grade_id=sg.id
        WHERE sa.deleted = 0
        <if test="keyWord != null and keyWord != ''">
            and (sa.name like '%' #{keyWord} '%' or su.card_no 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="departmentId != null and departmentId != ''">
            and su.department_id= #{departmentId}
        </if>
        <if test="openType != null and openType != ''">
            and sa.open_type= #{openType}
        </if>
        <if test="resultStatus != null and  resultStatus != ''">
            and sa.result_status= #{resultStatus}
        </if>
        <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
            and sa.date_time &gt;= #{startTime} and sa.date_time &lt;= #{endTime}
        </if>

        ORDER BY sa.date_time DESC
    </select>

    <select id="getPageExport" resultType="com.template.model.vo.SmartAccessVo">
        SELECT
        sa.id,
        sa.name,
        sa.sn,
        sa.type,
        sd.`name` as departmentName,
        sc.`name` as className,
        su.card_no as cardNo,
        sa.image,
        sa.open_type as openType,
        sa.date_time as dateTime,
        sa.in_out as access,
        sa.result_status as resultStatus
        FROM
        `smart_access` sa
        LEFT JOIN smart_user su on sa.user_id=su.id
        LEFT JOIN smart_department sd on su.department_id=sd.id
        LEFT JOIN smart_class sc on su.school_class=sc.id
        LEFT JOIN smart_grade sg on sc.grade_id=sg.id
        WHERE sa.deleted = 0
        <if test="keyWord != null and keyWord != ''">
            and (sa.name like '%' #{keyWord} '%' or su.card_no 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="departmentId != null and departmentId != ''">
            and su.department_id= #{departmentId}
        </if>
        <if test="openType != null and openType != ''">
            and sa.open_type= #{openType}
        </if>
        <if test="resultStatus != null and  resultStatus != ''">
            and sa.result_status= #{resultStatus}
        </if>
        <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
            and sa.date_time &gt;= #{startTime} and sa.date_time &lt;= #{endTime}
        </if>
        ORDER BY sa.date_time DESC
    </select>

    <select id="getAccessPage" resultType="com.template.model.vo.SmartAccessVo">
        SELECT
        sa.id,
        sa.name,
        sa.sn,
        sa.type,
        sd.`name` as departmentName,
        sc.`name` as className,
        su.card_no as cardNo,
        sa.image,
        sa.open_type as openType,
        sa.date_time as dateTime,
        sa.in_out as access,
        sa.result_status as resultStatus
        FROM
        `smart_access` sa
        LEFT JOIN smart_user su on sa.user_id=su.id
        LEFT JOIN smart_department sd on su.department_id=sd.id
        LEFT JOIN smart_class sc on su.school_class=sc.id
        LEFT JOIN smart_grade sg on sc.grade_id=sg.id
        WHERE sa.deleted = 0
        <if test="keyWord != null and keyWord != ''">
            and (sa.name like '%' #{keyWord} '%' or su.card_no 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="resultStatus != null and  resultStatus != ''">
            and sa.result_status= #{resultStatus}
        </if>
        <if test="inOut != null and  inOut != ''">
            and sa.in_out= #{inOut}
        </if>
        <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
            and sa.date_time &gt;= #{startTime} and sa.date_time &lt;= #{endTime}
        </if>

        ORDER BY sa.date_time DESC

    </select>
    <select id="toDatelist" resultType="com.template.model.pojo.SmartAccess">
        SELECT sa.*
        FROM `smart_access` sa
                 LEFT JOIN smart_user su ON sa.user_id = su.id
        WHERE sa.deleted = 0
          AND su.identity_id = 2
          AND sa.date_time >= #{start}
          AND #{end} >= sa.date_time
    </select>
    <select id="toDateAndPlacelist" resultType="com.template.model.pojo.SmartAccess">
        SELECT sa.*
        FROM `smart_access` sa
                 LEFT JOIN smart_user su ON sa.user_id = su.id
        WHERE sa.deleted = 0
          AND su.identity_id = 2
          AND sa.date_time >= #{startTime}
          AND #{endTime} >= sa.date_time
          AND sa.type=#{type}
    </select>
</mapper>
