<?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.UserResidentDao">
    <sql id="field">
        id,building_unit_estate_id,name,type,tel,id_type.id_number,
        pwd,confuse,email,create_id,create_date,effective_time_start,
        effective_time_end
    </sql>

<!--    查询业主信息list-->
    <select id="list" parameterType="com.api.model.basicArchives.UserResident" resultType="com.api.vo.basicArchives.VoUserResident">
        select id,name,tel,id_type,id_number
        from user_resident
        <where>
            and type = 1
            <if test="name != null and name != ''">
                and name like concat('%',#{name},'%')
            </if>
            <if test="tel != null and tel != ''">
                and tel like concat('%',#{tel},'%')
            </if>
            <if test="idNumber != null and idNumber != ''">
                and id_number like concat('%',#{idNumber},'%')
            </if>
        </where>
    </select>


    <select id="listAll" parameterType="com.api.model.basicArchives.UserResident" resultType="com.api.vo.basicArchives.VoUserResident">
        select id,name,tel,id_type,id_number
        from user_resident
        <where>
            and type in (1,3)
            <if test="name != null and name != ''">
                and name like concat('%',#{name},'%')
            </if>
            <if test="tel != null and tel != ''">
                and tel like concat('%',#{tel},'%')
            </if>
            <if test="idNumber != null and idNumber != ''">
                and id_number like concat('%',#{idNumber},'%')
            </if>
        </where>
    </select>

    <!-- 添加住户信息-->
    <insert id="insert" parameterType="com.api.model.basicArchives.UserResident">
        insert into
        <trim prefix="user_resident(" suffix=")" suffixOverrides=",">
            name,
            type,
            tel,
            id_type,
            id_number,
            <if test="pwd != null and pwd != ''">
                pwd,
            </if>
            <if test="confuse != null and confuse != ''">
                confuse,
            </if>
            <if test="email != null and email != ''">
                email,
            </if>
            <if test="nickName != null and nickName != ''">
                nick_name,
            </if>
            create_id,
            create_date,
        </trim>
        values
        <trim prefix="(" suffix=")" suffixOverrides=",">
            #{name},
            #{type},
            #{tel},
            #{idType},
            #{idNumber},
            <if test="pwd != null and pwd != ''">
                #{pwd},
            </if>
            <if test="confuse != null and confuse != ''">
                #{confuse},
            </if>
            <if test="email != null and email != ''">
                #{email},
            </if>
            <if test="nickName != null and nickName != ''">
                #{nickName},
            </if>
            #{createId},
            #{createDate},
        </trim>
        <selectKey keyProperty="id" resultType="java.lang.Integer">
            select LAST_INSERT_ID() AS id
        </selectKey>
    </insert>
    <insert id="insert2" parameterType="com.api.model.basicArchives.UserResident">
        insert into user_resident(
            type,
            tel,
            nick_name,
            create_id,
            create_date
        ) values (
            #{type},
            #{tel},
            #{nickName},
            #{createId},
            #{createDate}
        )
        <selectKey keyProperty="id" resultType="java.lang.Integer">
            select LAST_INSERT_ID() AS id
        </selectKey>
    </insert>

    <!--    添加业主亲属关联-->
    <insert id="insertResidentRelatives" parameterType="com.api.model.basicArchives.UserResidentRelatives">
        insert into user_resident_relatives(resident_id,relatives_id,identity)
        values (#{residentId},#{relativesId},#{identity})
    </insert>
    <!--添加亲属信息-->
    <insert id="insertRelatives" parameterType="com.api.vo.basicArchives.VoRelatives">
        insert into
        <trim prefix="user_resident(" suffix=")" suffixOverrides=",">
            name,
            type,
            tel,
            id_type,
            id_number,
            <if test="pwd != null and pwd != ''">
                pwd,
            </if>
            <if test="confuse != null and confuse != ''">
                confuse,
            </if>
            <if test="email != null and email != ''">
                email,
            </if>
            create_id,
            create_date,
        </trim>
        values
        <trim prefix="(" suffix=")" suffixOverrides=",">
            #{name},
            #{type},
            #{tel},
            #{idType},
            #{idNumber},
            <if test="pwd != null and pwd != ''">
                #{pwd},
            </if>
            <if test="confuse != null and confuse != ''">
                #{confuse},
            </if>
            <if test="email != null and email != ''">
                #{email},
            </if>
            #{createId},
            #{createDate},
        </trim>
        <selectKey keyProperty="id" resultType="java.lang.Integer">
            select LAST_INSERT_ID() AS id
        </selectKey>
    </insert>
    <!--添加业主房产关联-->
    <insert id="insertResidentEstate" parameterType="com.api.model.basicArchives.CpmResidentEstate">
        insert into cpm_resident_estate(building_unit_estate_id,resident_id)
        values (#{buildingUnitEstateId},#{residentId})
    </insert>

<!--    根据房产id查询业主信息-->
    <select id="findByBuildingUnitEstateId" parameterType="java.lang.Integer" resultType="com.api.model.basicArchives.UserResident">
        select ur.* from user_resident as ur,cpm_resident_estate as cre
        where ur.id = cre.resident_id
        and cre.building_unit_estate_id = #{buildingUnitEstateId}
    </select>

<!--    修改住户信息-->
    <update id="update" parameterType="com.api.model.basicArchives.UserResident">
        update user_resident
        <set>
            <if test="name != null and name != ''">
                name = #{name},
            </if>
            <if test="type != null">
                type = #{type},
            </if>
            <if test="tel != null and tel != ''">
                tel =#{tel},
            </if>
            <if test="idType != null">
                id_type = #{idType},
            </if>
            <if test="idNumber != null and idNumber != ''">
                id_number = #{idNumber},
            </if>
            <if test="pwd != null and pwd != ''">
                pwd = #{pwd},
            </if>
            <if test="email != null and email != ''">
                email = #{email},
            </if>
        </set>
        where id = #{id}
    </update>

<!--    根据住户id查询住户信息-->
    <select id="findById" parameterType="java.lang.Integer" resultType="com.api.model.basicArchives.UserResident">
        select id,name,tel,id_type,id_number
        from user_resident where id = #{id}
    </select>
<!--根据住户ID查询亲属信息-->
    <select id="findRelativesById" parameterType="java.lang.Integer" resultType="com.api.vo.basicArchives.VoRelatives">
        select ur2.id,ur2.name,ur2.tel,urr.identity,ur2.id_type,ur2.id_number
        from user_resident as ur1,user_resident_relatives as urr,user_resident as ur2
		where ur1.id = urr.resident_id and ur2.id = urr.relatives_id and ur1.id =#{id}
    </select>
<!--根据业主id和亲属id修改业主亲属关联中的身份信息-->
    <update id="updateResidentRelatives" parameterType="com.api.model.basicArchives.UserResidentRelatives">
        update user_resident_relatives
        set identity = #{identity}
        where resident_id = #{residentId} and relatives_id = #{relativesId}
    </update>
    <!--根据id删除住户表信息-->
    <delete id="delete" parameterType="java.lang.Integer">
        delete from user_resident where id =#{id}
    </delete>

    <!--  根据业主（租户）id和房产id删除业主房产关联表信息  -->
    <delete id="deleteByResidentIdAndEstateId" parameterType="com.api.model.basicArchives.ResidentIdAndEstateId">
        delete from cpm_resident_estate
        where building_unit_estate_id = #{estateId} and resident_id = #{residentId}
    </delete>

    <!--  根据业主（租户）id和亲属id删除业主亲属关联表信息  -->
    <delete id="deleteByResidentIdAndRelativesId" parameterType="com.api.model.basicArchives.ResidentIdAndRelativesId">
        delete from user_resident_relatives
        where resident_id = #{ResidentId} and relatives_id = #{RelativesId}
    </delete>
    <delete id="deleteUserResident" parameterType="com.api.model.basicArchives.UserResident">
        delete from user_resident
        where name=#{name} and tel=#{tel} and id_number=#{idNumber}
    </delete>

    <!--  查询所有的业主信息  -->
    <select id="findResidentAll" resultType="com.api.vo.basicArchives.VoFindAll">
        select id as value,name as label from user_resident where type = 1
    </select>

    <!--  查询所有的住户信息  -->
    <select id="findAll" resultType="com.api.vo.basicArchives.VoFindAll">
        select id as value,name as label from user_resident where type in (1,3)
    </select>

    <select id="findNameBySearch" parameterType="com.api.model.basicArchives.SearchFindName" resultType="com.api.vo.basicArchives.VoFindAll">
        select T.* from
        (
            select distinct ur.id as value,ur.name as label
            from user_resident as ur,cpm_resident_estate as cre
            where ur.id = cre.resident_id
        ) as T
        <where>
            <if test="name != null and name != ''">
                and T.label like concat('%',#{name},'%')
            </if>
        </where>
    </select>

    <!--   根据住户手机号查询住户信息-->
    <select id="findByTel" parameterType="java.lang.String" resultType="com.api.model.basicArchives.UserResident">
        select id,name,tel,id_type,id_number
        from user_resident where tel = #{tel}
    </select>

    <!--   根据住户证件号码查询住户信息-->
    <select id="findByIdNumber" parameterType="java.lang.String" resultType="com.api.model.basicArchives.UserResident">
        select id,name,tel,id_type,id_number
        from user_resident where id_number = #{idNumber}
    </select>

    <!--   根据业主手机号和证件号码查询住户信息-->
    <select id="findByTelAndIdNumber" parameterType="com.api.model.basicArchives.UserResident" resultType="com.api.model.basicArchives.UserResident">
        select id,name,tel,id_type,id_number
        from user_resident
        where tel = #{tel} and id_number = #{idNumber}
    </select>

    <!--  根据住户id和房产id查询住户房产关联表  -->
    <select id="findByEstateIdAndResidentId" parameterType="com.api.model.basicArchives.ResidentIdAndEstateId" resultType="com.api.model.basicArchives.CpmResidentEstate">
        select * from cpm_resident_estate
        where building_unit_estate_id = #{estateId}
        and resident_id = #{residentId}
    </select>

    <select id="findResidentNameBySearch" parameterType="com.api.model.basicArchives.SearchFindName" resultType="com.api.vo.basicArchives.VoFindResidentByName">
        select T.value,T.label,concat(cb.no,'-',cbu.no,'-',cbue.room_number) as room_name,cb.id as building_id,cbu.id as unit_id,cbue.id as estate_id from
        (
            select ur.id as value,ur.name as label,ur.type,max(cre.building_unit_estate_id) as estate_id
            from user_resident as ur,cpm_resident_estate as cre
            where ur.id = cre.resident_id
            GROUP BY ur.id
        ) as T,cpm_building_unit_estate as cbue,cpm_building_unit as cbu,cpm_building as cb
        <where>
            cbue.id = T.estate_id
            and cbue.building_unit_id = cbu.id
            and cbu.building_id = cb.id
            and T.type = 1
            <if test="name != null and name != ''">
                and T.label like concat('%',#{name},'%')
            </if>
        </where>
    </select>
    <select id="findResidentInfoBySearch" parameterType="com.api.model.basicArchives.SearchFindName" resultType="com.api.vo.basicArchives.VoFindResidentInfo">
        select T.value,T.label,T.tel,concat(cb.no,'-',cbu.no,'-',cbue.room_number) as room_name,cb.id as building_id,cbu.id as unit_id,cbue.id as estate_id from
        (
        select ur.id as value,ur.name as label,ur.tel,ur.type,max(cre.building_unit_estate_id) as estate_id
        from user_resident as ur,cpm_resident_estate as cre
        where ur.id = cre.resident_id
        GROUP BY ur.id
        ) as T,cpm_building_unit_estate as cbue,cpm_building_unit as cbu,cpm_building as cb
        <where>
            cbue.id = T.estate_id
            and cbue.building_unit_id = cbu.id
            and cbu.building_id = cb.id
            and T.type in (1,3)
            <if test="name != null and name != ''">
                and T.label like concat('%',#{name},'%')
            </if>
        </where>
    </select>
</mapper>