<?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.basicArchives.UserTenantDao">
    <select id="list" parameterType="com.api.model.basicArchives.UserResident" resultType="com.api.vo.basicArchives.VoUserTenant">
        SELECT a.*, uc.code as car_code
        from (
        select ur.id,ur.type,ur.name,ur.tel,ur.id_type,ur.id_number,cre.effective_time_start,cre.effective_time_end,cbue.id as room_id,CONCAT(cb.no,'-',cbu.no,'-',cbue.room_number) as room_name
        from user_resident as ur,cpm_building_unit_estate as cbue,cpm_resident_estate as cre,cpm_building_unit as cbu,cpm_building as cb
        where ur.id = cre.resident_id and cbue.id = cre.building_unit_estate_id and cbue.building_unit_id =cbu.id and cbu.building_id = cb.id and  ur.type = 3
        ) as a LEFT JOIN user_car as uc
        on a.id = uc.owner
        <where>
            a.type = 3
            <if test="name != null and name != ''">
                and a.name = #{name}
            </if>
            <if test="tel != null and tel != ''">
                and a.tel = #{tel}
            </if>
        </where>
    </select>
    <select id="lists" resultType="com.api.vo.basicArchives.VoUserTenant"
            parameterType="com.api.model.basicArchives.UserResident">
        SELECT a.*, uc.code as car_code from
          (select sys_lease.id,sys_lease.name,sys_lease.tel,sys_lease.id_card,sys_lease.lease_date_start as effectiveTimeStart,
          sys_lease.lease_date_end as effectiveTimeEnd,cpm_building_unit_estate.room_number as roomName
          from sys_lease  left join cpm_building_unit_estate on sys_lease.estate_id =cpm_building_unit_estate.id
          where (sys_lease.status=5 or sys_lease.status=6))as  a LEFT JOIN user_car as uc
        on a.id = uc.owner
        <where>

            <if test="name != null and name != ''">
                and a.name = #{name}
            </if>
            <if test="tel != null and tel != ''">
                and a.tel = #{tel}
            </if>
        </where>
    </select>

    <!--添加租户房产关联-->
    <insert id="insertResidentEstate" parameterType="com.api.model.basicArchives.CpmResidentEstate">
        insert into cpm_resident_estate(building_unit_estate_id,resident_id,effective_time_start,effective_time_end)
        values (#{buildingUnitEstateId},#{residentId},#{effectiveTimeStart},#{effectiveTimeEnd})
    </insert>
</mapper>