<?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.app.dao.butler.AppVisitorInviteDao">
    <select id="list" parameterType="com.api.model.app.SearchAppVisitorInvite" resultType="com.api.model.app.AppUserVisitorsInvite">
        select uvn.*,concat(cb.no,'-',cbu.no,'-',cbue.room_number) as room_name
        from user_visitors_new as uvn,cpm_building_unit_estate as cbue,
        cpm_building_unit as cbu,cpm_building as cb
        <where>
            uvn.estate_id = cbue.id
            and cbue.building_unit_id = cbu.id
            and cbu.building_id = cb.id
            and uvn.create_id = #{id}
            <if test="visitorInviteStatus == 1">
                and uvn.status = 1
            </if>
            <if test="visitorInviteStatus == 2">
                and uvn.status = 2
                and now() <![CDATA[ <= ]]> uvn.visit_date_end
            </if>
            <if test="visitorInviteStatus == 3">
                and uvn.status = 2
                and now() <![CDATA[ > ]]> uvn.visit_date_end
            </if>
        </where>
    </select>

    <insert id="insertUserVisitorsNew" parameterType="com.api.model.app.AppUserVisitorsInvite">
        insert into
        <trim prefix="user_visitors_new(" suffix=")" suffixOverrides=",">
            estate_id,
            <if test="name != null and name != ''">
                name,
            </if>
            <if test="tel != null and tel != ''">
                tel,
            </if>
            <if test="sex != null">
                sex,
            </if>
            <if test="carNumber != null and carNumber != ''">
                car_number,
            </if>
            <if test="visitDateStart != null">
                visit_date_start,
            </if>
            <if test="visitDateEnd != null">
                visit_date_end,
            </if>
            status,
            create_id,
            create_date
        </trim>
        values
        <trim prefix="(" suffix=")" suffixOverrides=",">
            #{estateId},
            <if test="name != null and name != ''">
                #{name},
            </if>
            <if test="tel != null and tel != ''">
                #{tel},
            </if>
            <if test="sex != null">
                #{sex},
            </if>
            <if test="carNumber != null and carNumber != ''">
                #{carNumber},
            </if>
            <if test="visitDateStart != null">
                #{visitDateStart},
            </if>
            <if test="visitDateEnd != null">
                #{visitDateEnd},
            </if>
            #{status},
            #{createId},
            #{createDate}
        </trim>
        <selectKey keyProperty="id" resultType="java.lang.Integer">
            select LAST_INSERT_ID() AS id
        </selectKey>
    </insert>
    <insert id="insertUserVisitorsUrl" parameterType="com.api.model.app.AppUserVisitorsUrl">
        insert into user_visitors_new_url(
            code,
            effective_date,
            user_visitors_new_id,
            is_use
        ) values (
            #{code},
            #{effectiveDate},
            #{userVisitorsNewId},
            #{isUse}
        )
    </insert>
    <select id="findEffectiveDateByCode" parameterType="java.lang.String" resultType="java.util.Date">
        select effective_date from user_visitors_new_url where code = #{code}
    </select>
    <select id="findByUrlCode" parameterType="java.lang.String" resultType="com.api.model.app.AppUserVisitorsInvite">
        select uvn.*,concat(cb.no,'幢',cbu.no,'单元',cbue.room_number) as room_name
        from user_visitors_new as uvn,user_visitors_new_url as uvnu,cpm_building_unit_estate as cbue,cpm_building_unit as cbu,cpm_building as cb
        where uvnu.user_visitors_new_id = uvn.id and uvn.estate_id = cbue.id and cbue.building_unit_id = cbu.id and cbu.building_id = cb.id and uvnu.code = #{code}
    </select>
    <select id="findVisitIdByCode" parameterType="java.lang.String" resultType="java.lang.Integer">
        select user_visitors_new_id from user_visitors_new_url where code = #{code}
    </select>
    <select id="findIsUseByCode" parameterType="java.lang.String" resultType="java.lang.Integer">
        select is_use from user_visitors_new_url where code = #{code}
    </select>
    <select id="findById" resultType="com.api.model.app.AppUserVisitorsInvite">
        select * from user_visitors_new where id = #{visitorsInviteId}
    </select>
    <update id="updateUserVisitorsNew" parameterType="com.api.model.app.AppUserVisitorsInviteSubmit">
        update user_visitors_new
        set name = #{name},
        tel = #{tel},
        sex = #{sex},
        <if test="carNumber != null and carNumber != ''">
            car_number = #{carNumber},
        </if>
        <if test="peers != null">
            peers = #{peers},
        </if>
        <if test="idNumber != null and idNumber != ''">
            id_number = #{idNumber},
        </if>
        visit_date_start = #{visitDateStart},
        visit_date_end = #{visitDateEnd},
        status = #{status}
        where id = #{id}
    </update>

    <update id="updateIsUseByCode" parameterType="java.lang.String">
        update user_visitors_new_url set is_use = 1 where code = #{code}
    </update>

    <insert id="insertQRUserVisitorsNew" parameterType="com.api.model.app.AppUserQRVisitorsInviteSubmit">
        insert into user_visitors_new(
            estate_id,
            name,
            tel,
            sex,
            <if test="carNumber != null and carNumber != ''">
                car_number,
            </if>
            visit_date_start,
            visit_date_end,
            <if test="peers != null">
                peers,
            </if>
            status,
            <if test="idNumber != null and idNumber != ''">
                id_number,
            </if>
            create_id,
            create_date
        )values (
            #{estateId},
            #{name},
            #{tel},
            #{sex},
            <if test="carNumber != null and carNumber != ''">
                #{carNumber},
            </if>
            #{visitDateStart},
            #{visitDateEnd},
            <if test="peers != null">
                #{peers},
            </if>
            #{status},
            <if test="idNumber != null and idNumber != ''">
                #{idNumber},
            </if>
            #{createId},
            #{createDate}
        )
        <selectKey keyProperty="id" resultType="java.lang.Integer">
            select LAST_INSERT_ID() AS id
        </selectKey>
    </insert>

</mapper>