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

    <insert id="addQrCode" parameterType="com.api.model.butlerService.SysDoorQRCode">
        insert into sys_door_qrcode(
            id,
            estate_id,
            name,
            tel,
            id_card,
            start_time,
            end_time,
            create_id,
            create_date
        )values (
            #{id},
            #{estateId},
            #{name},
            #{tel},
            #{idCard},
            #{startTime},
            #{endTime},
            #{createId},
            #{createDate}
        )
    </insert>
    <delete id="removeQrCode" parameterType="com.api.model.butlerService.SysDoorQRCode">
        delete from sys_door_qrcode where id = #{id}
    </delete>
    <select id="countQRCodeByEstateIdAndTel" parameterType="com.api.model.butlerService.SysDoorQRCode" resultType="java.lang.Integer">
        select count(*) from sys_door_qrcode where estate_id = #{estateId} and tel = #{tel}
    </select>
    <select id="list" parameterType="com.api.model.butlerService.SearchDoorQRCode" resultType="com.api.vo.butlerService.VoDoorQRCode">
        select sdq.id,sdq.estate_id,concat(cb.no,'-',cbu.no,'-',cbue.room_number) as room_name,sdq.name,sdq.tel,sdq.id_card,sdq.start_time,sdq.end_time,su.actual_name as create_name,sdq.create_date
        from sys_door_qrcode as sdq,cpm_building_unit_estate as cbue,cpm_building_unit as cbu,cpm_building as cb,sys_user as su
        <where>
            sdq.estate_id = cbue.id
            and cbue.building_unit_id = cbu.id
            and cbu.building_id = cb.id
            and sdq.create_id = su.id
            <if test="tel != null and tel != ''">
                and sdq.tel = #{tel}
            </if>
        </where>
        order by sdq.create_date desc
    </select>
    <select id="checkAppointment" resultType="java.lang.Integer">
        select count(*) from user_visitors_new
        where tel = #{tel} and visit_date_start = #{startTime} and visit_date_end = #{endTime}
    </select>
    <select id="findEstateId" resultType="java.lang.Integer"
            parameterType="com.api.model.butlerService.GetHtmlCode">
        select estate_id from user_visitors_new where tel=#{tel} ORDER BY create_date desc limit 1
    </select>
    <select id="findRoomNumber" resultType="java.lang.String" parameterType="java.lang.Integer">
        select room_number from cpm_building_unit_estate where id=#{estateId}
    </select>
</mapper>