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

    <select id="list" parameterType="com.api.model.butlerService.SearchUserVisitors" resultType="com.api.vo.butlerService.VoUserVisitors">
        SELECT uv.id,uv.name,uv.tel,uv.car_number as car_num,uv.visit_date_start as expected_visit_date,cbue.room_number as room_number,cbu.no as unit_no,cb.no as estate_no
        from user_visitors_new as uv,cpm_building_unit_estate as cbue,cpm_building_unit as cbu,cpm_building as cb
        <where>
            uv.estate_id = cbue.id
            and cbue.building_unit_id = cbu.id
            and cbu.building_id = cb.id
            <if test="roomNumber != null and roomNumber != ''">
                and cbue.room_number like concat('%',#{roomNumber},'%')
            </if>
            <if test="unitNo != null">
                and cbu.no like concat('%',#{unitNo},'%')
            </if>
            <if test="estateNo != null">
                and cb.no like concat('%',#{estateNo},'%')
            </if>
            <if test="name != null and name != ''">
                and uv.name like concat('%',#{name},'%')
            </if>
            <if test="sex != null">
                and uv.sex = #{sex}
            </if>
            <if test="carNum != null and carNum != ''">
                and uv.car_number like concat('%',#{carNum},'%')
            </if>
            <if test="expectedVisitDateStart != null">
                and uv.visit_date_start <![CDATA[ >= ]]> #{expectedVisitDateStart}
            </if>
            <if test="expectedVisitDateEnd != null">
                and uv.visit_date_start <![CDATA[ <= ]]> #{expectedVisitDateEnd}
            </if>
            <if test="type == 1">
                and uv.type = -1
            </if>
            <if test="type == 2">
                and uv.type > 0
            </if>
        </where>
        order by uv.create_date DESC
    </select>
<!--    旧版访客管理list查询-->
<!--    <select id="list" parameterType="com.api.model.butlerService.SearchUserVisitors" resultType="com.api.vo.butlerService.VoUserVisitors">-->
<!--        SELECT uv.*,cbue.room_number as room_number,cbu.no as unit_no,cb.no as estate_no-->
<!--        from user_visitors as uv,cpm_building_unit_estate as cbue,cpm_building_unit as cbu,cpm_building as cb-->
<!--        <where>-->
<!--            uv.building_unit_estate_id = cbue.id-->
<!--            and cbue.building_unit_id = cbu.id-->
<!--            and cbu.building_id = cb.id-->
<!--            <if test="roomNumber != null and roomNumber != ''">-->
<!--                and cbue.room_number like concat('%',#{roomNumber},'%')-->
<!--            </if>-->
<!--            <if test="unitNo != null">-->
<!--                and cbu.no like concat('%',#{unitNo},'%')-->
<!--            </if>-->
<!--            <if test="estateNo != null">-->
<!--                and cb.no like concat('%',#{estateNo},'%')-->
<!--            </if>-->
<!--            <if test="name != null and name != ''">-->
<!--                and uv.name like concat('%',#{name},'%')-->
<!--            </if>-->
<!--            <if test="sex != null">-->
<!--                and uv.sex = #{sex}-->
<!--            </if>-->
<!--            <if test="carNum != null and carNum != ''">-->
<!--                and uv.car_num like concat('%',#{carNum},'%')-->
<!--            </if>-->
<!--            <if test="expectedVisitDateStart != null">-->
<!--                and uv.expected_visit_date <![CDATA[ >= ]]> #{expectedVisitDateStart}-->
<!--            </if>-->
<!--            <if test="expectedVisitDateEnd != null">-->
<!--                and uv.expected_visit_date <![CDATA[ <= ]]> #{expectedVisitDateEnd}-->
<!--            </if>-->
<!--            <if test="visitDateStart != null">-->
<!--                and uv.visit_date <![CDATA[ >= ]]> #{visitDateStart}-->
<!--            </if>-->
<!--            <if test="visitDateEnd != null">-->
<!--                and uv.visit_date <![CDATA[ <= ]]> #{visitDateEnd}-->
<!--            </if>-->
<!--            <if test="visitorStatus != null">-->
<!--                and uv.visitor_status = #{visitorStatus}-->
<!--            </if>-->
<!--            <if test="type != null">-->
<!--                and uv.type = #{type}-->
<!--            </if>-->
<!--        </where>-->
<!--        <if test="appointmentVisitDateSort == 1">-->
<!--            order by uv.expected_visit_date DESC-->
<!--        </if>-->
<!--        <if test="appointmentVisitDateSort == 2">-->
<!--            order by uv.expected_visit_date-->
<!--        </if>-->
<!--        <if test="actualVisitDateSort == 1">-->
<!--            order by uv.visit_date DESC-->
<!--        </if>-->
<!--        <if test="actualVisitDateSort == 2">-->
<!--            order by uv.visit_date-->
<!--        </if>-->
<!--    </select>-->

    <select id="findById" parameterType="java.lang.Integer" resultType="com.api.vo.butlerService.VoFindByIdVisitors">
        SELECT uv.*,cbue.room_number as room_number,cbu.no as unit_no,cb.no as estate_no
        from user_visitors as uv,cpm_building_unit_estate as cbue,cpm_building_unit as cbu,cpm_building as cb
        where uv.building_unit_estate_id = cbue.id
            and cbue.building_unit_id = cbu.id
            and cbu.building_id = cb.id
            and uv.id = #{id}
    </select>

    <update id="update" parameterType="com.api.model.butlerService.UserVisitors">
        update user_visitors
        <set>
            <if test="name != null and name != ''">
                name = #{name},
            </if>
            <if test="sex != null">
                sex = #{sex},
            </if>
            <if test="isDrive != null">
                is_drive = #{isDrive},
            </if>
            <if test="carNum != null and carNum != ''">
                car_num = #{carNum},
            </if>
            <if test="expectedVisitDate != null">
                expected_visit_date = #{expectedVisitDate},
            </if>
            <if test="visitDate != null">
                visit_date = #{visitDate},
            </if>
            <if test="departureTime != null">
                departure_time = #{departureTime},
            </if>
            <if test="effectiveTime != null">
                effective_time = #{effectiveTime},
            </if>
            <if test="visitorStatus != null">
                visitor_status = #{visitorStatus},
            </if>
            <if test="modifyId != null">
                modify_id = #{modifyId},
            </if>
            <if test="modifyDate != null">
                modify_date = #{modifyDate},
            </if>
            <if test="verifierId != null">
                verifier_id = #{verifierId},
            </if>
            <if test="verifierDate != null">
                verifier_date = #{verifierDate},
            </if>
        </set>
        where id = #{id}
    </update>

    <delete id="delete" parameterType="java.lang.Integer" >
        delete  from user_visitors where id = #{id}
    </delete>

    <select id="listDetail" parameterType="java.lang.Integer" resultType="com.api.vo.butlerService.VoUserVisitorsDetail">
        select * from user_visitors_detail
        where visitors_id = #{id}
        order by visit_time
    </select>

    <select id="countVisitorsNew" resultType="java.lang.Integer">
        select count(*) from user_visitors where to_days(create_date) = to_days(now())
    </select>
</mapper>