<?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.api.manage.dao.butlerService.UserVisitorsNewDao">

    <select id="list" parameterType="com.api.model.butlerService.SearchVisitorsNew" resultType="com.api.vo.butlerService.VoVisitorsNew">
        select uvn.*,(case uvn.create_id = -1 when true then '扫码录入' else ur.name end)as create_name,concat(cb.no,'-',cbu.no,'-',cbue.room_number) as room_name
        from cpm_building_unit_estate as cbue,cpm_building_unit as cbu,cpm_building as cb,
        user_visitors_new as uvn
        LEFT JOIN user_resident as ur on uvn.create_id = ur.id
        <where>
            uvn.estate_id = cbue.id
            and cbue.building_unit_id = cbu.id
            and cbu.building_id = cb.id
            <if test="name != null and name != ''">
                and uvn.name like concat('%',#{name},'%')
            </if>
            <if test="tel != null and tel != ''">
                and uvn.tel like concat('%',#{tel},'%')
            </if>
            <if test="years != null">
                and YEAR(uvn.create_date) = #{years}
            </if>
            <if test="months != null">
                and MONTH(uvn.create_date) = #{months}
            </if>
            and uvn.status = 2
        </where>
        order by uvn.create_date desc
    </select>
</mapper>