<?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.ruoyi.system.mapper.HouseInfoMapper">
    
    <resultMap type="HouseInfo" id="HouseInfoResult">
        <result property="houseId"    column="house_id"    />
        <result property="ownerName"    column="owner_name"    />
        <result property="location"    column="location"    />
        <result property="rightType"    column="right_type"    />
        <result property="area"    column="area"    />
        <result property="documentNumber"    column="document_number"    />
        <result property="propertyUnitNumber"    column="property_unit_number"    />
        <result property="coOwnership"    column="co_ownership"    />
        <result property="usageType"    column="usage_type"    />
        <result property="usagePeriod"    column="usage_period"    />
        <result property="registrationDate"    column="registration_date"    />
        <result property="coOwner"    column="co_owner"    />
        <result property="detailedAddress"    column="detailed_address"    />
        <result property="hasParkingSpace"    column="has_parking_space"    />
        <result property="parkingNumber"    column="parking_number"    />
        <result property="communityName"    column="community_name"    />
        <result property="houseStatus"    column="house_status"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="remark"    column="remark"    />
        <collection property="residentInfoList" javaType="java.util.List" resultMap="ResidentInfoResult"  />
    </resultMap>

    <resultMap type="HouseInfo" id="HouseInfoResultResident">
        <result property="houseId"    column="h_house_id"    />
        <result property="ownerName"    column="owner_name"    />
        <result property="location"    column="location"    />
        <result property="rightType"    column="right_type"    />
        <result property="area"    column="area"    />
        <result property="documentNumber"    column="document_number"    />
        <result property="propertyUnitNumber"    column="property_unit_number"    />
        <result property="coOwnership"    column="co_ownership"    />
        <result property="usageType"    column="usage_type"    />
        <result property="usagePeriod"    column="usage_period"    />
        <result property="registrationDate"    column="registration_date"    />
        <result property="coOwner"    column="co_owner"    />
        <result property="detailedAddress"    column="h_detailed_address"    />
        <result property="hasParkingSpace"    column="has_parking_space"    />
        <result property="parkingNumber"    column="parking_number"    />
        <result property="communityName"    column="community_name"    />
        <result property="houseStatus"    column="house_status"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="remark"    column="remark"    />
        <collection property="residentInfoList" javaType="java.util.List" resultMap="ResidentInfoResult"  />
    </resultMap>

    <resultMap type="ResidentInfo" id="ResidentInfoResult">
        <result property="residentId"    column="resident_id"    />
        <result property="houseId"    column="house_id"    />
        <result property="userId"    column="user_id"    />
        <result property="detailedAddress"    column="detailed_address"    />
        <result property="residentName"    column="resident_name"    />
        <result property="residentIdCard"    column="resident_id_card"    />
        <result property="residentBirthday"    column="resident_birthday"    />
        <result property="residentPhone"    column="resident_phone"    />
        <result property="residentGender"    column="resident_gender"    />
        <result property="residentRelationship"    column="resident_relationship"    />
        <result property="isHouseholder"    column="is_householder"    />
        <result property="isTenant"    column="is_tenant"    />
        <result property="residentEmployer"    column="resident_employer"    />
        <result property="specialType"    column="special_type"    />
        <result property="residentHobby"    column="resident_hobby"    />
        <result property="residentAppearance"    column="resident_appearance"    />
        <result property="idCardFront"    column="id_card_front"    />
        <result property="idCardBack"    column="id_card_back"    />
        <result property="idCardAddress"    column="id_card_address"    />
        <result property="facePhoto"    column="face_photo"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="remark"    column="remark"    />
    </resultMap>
    <sql id="selectHouseInfoVo">
        select house_id , owner_name, location, right_type, area, parking_number,document_number, property_unit_number, co_ownership, usage_type, usage_period, registration_date, co_owner, detailed_address, has_parking_space, community_name, house_status, create_by, create_time, update_by, update_time, remark from house_info
    </sql>

    <select id="selectHouseInfoList" parameterType="HouseInfo" resultMap="HouseInfoResult">
        <include refid="selectHouseInfoVo"/>
        <where>  
            <if test="ownerName != null  and ownerName != ''"> and owner_name like concat('%', #{ownerName}, '%')</if>
            <if test="location != null  and location != ''"> and location = #{location}</if>
            <if test="rightType != null  and rightType != ''"> and right_type = #{rightType}</if>
            <if test="area != null "> and area = #{area}</if>
            <if test="documentNumber != null  and documentNumber != ''"> and document_number = #{documentNumber}</if>
            <if test="propertyUnitNumber != null  and propertyUnitNumber != ''"> and property_unit_number = #{propertyUnitNumber}</if>
            <if test="coOwnership != null  and coOwnership != ''"> and co_ownership = #{coOwnership}</if>
            <if test="usageType != null  and usageType != ''"> and usage_type = #{usageType}</if>
            <if test="usagePeriod != null  and usagePeriod != ''"> and usage_period = #{usagePeriod}</if>
            <if test="registrationDate != null "> and registration_date = #{registrationDate}</if>
            <if test="coOwner != null  and coOwner != ''"> and co_owner = #{coOwner}</if>
            <if test="detailedAddress != null  and detailedAddress != ''"> and detailed_address = #{detailedAddress}</if>
            <if test="hasParkingSpace != null  and hasParkingSpace != ''"> and has_parking_space = #{hasParkingSpace}</if>
            <if test="parkingNumber != null  and parkingNumber != ''"> and parking_number = #{parkingNumber}</if>
            <if test="communityName != null  and communityName != ''"> and community_name like concat('%', #{communityName}, '%')</if>
            <if test="houseStatus != null "> and house_status = #{houseStatus}</if>
        </where>
    </select>
    
    <select id="selectHouseInfoByHouseId" parameterType="Long" resultMap="HouseInfoResult">
        <include refid="selectHouseInfoVo"/>
        where house_id = #{houseId}
    </select>
    <select id="selectHouseInfoListAndResidentInfo" parameterType="ResidentInfo" resultMap="HouseInfoResultResident">
        SELECT
            h.house_id AS h_house_id,
            h.detailed_address AS h_detailed_address,
            h.house_status,
            h.location,
            h.owner_name,
            h.area,
            h.has_parking_space,
            h.community_name,
            r.resident_id,
            r.resident_name,
            r.resident_id_card,
            r.resident_gender,
            r.resident_relationship,
            r.is_householder,
            r.is_tenant,
            r.resident_appearance
        FROM
            house_info h
                left JOIN
            resident_info r on h.house_id = r.house_id
        <where>
            <if test="residentName != null  and residentName != ''"> and r.resident_name like concat('%', #{residentName}, '%')</if>
            <if test="residentAppearance != null  and residentAppearance != ''"> and r.resident_appearance = #{residentAppearance}</if>
            <if test="userId != null  and userId != ''"> and r.user_id = #{userId}</if>
            <if test="houseId != null"> and h.house_id = #{houseId}</if>
            <if test="residentPhone != null"> and r.resident_phone = #{residentPhone}</if>
            <if test="houseStatus != null"> and h.house_status = #{houseStatus}</if>
            <if test="detailedAddress != null"> and h.detailed_address = #{detailedAddress}</if>
        </where>
        order by h.create_time DESC
    </select>

    <insert id="insertHouseInfo" parameterType="HouseInfo" useGeneratedKeys="true" keyProperty="houseId">
        insert into house_info
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="ownerName != null">owner_name,</if>
            <if test="location != null">location,</if>
            <if test="rightType != null">right_type,</if>
            <if test="area != null">area,</if>
            <if test="documentNumber != null">document_number,</if>
            <if test="propertyUnitNumber != null">property_unit_number,</if>
            <if test="coOwnership != null">co_ownership,</if>
            <if test="usageType != null">usage_type,</if>
            <if test="usagePeriod != null">usage_period,</if>
            <if test="registrationDate != null">registration_date,</if>
            <if test="coOwner != null">co_owner,</if>
            <if test="detailedAddress != null">detailed_address,</if>
            <if test="hasParkingSpace != null">has_parking_space,</if>
            <if test="parkingNumber != null">parking_number,</if>
            <if test="communityName != null">community_name,</if>
            <if test="houseStatus != null">house_status,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="remark != null">remark,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="ownerName != null">#{ownerName},</if>
            <if test="location != null">#{location},</if>
            <if test="rightType != null">#{rightType},</if>
            <if test="area != null">#{area},</if>
            <if test="documentNumber != null">#{documentNumber},</if>
            <if test="propertyUnitNumber != null">#{propertyUnitNumber},</if>
            <if test="coOwnership != null">#{coOwnership},</if>
            <if test="usageType != null">#{usageType},</if>
            <if test="usagePeriod != null">#{usagePeriod},</if>
            <if test="registrationDate != null">#{registrationDate},</if>
            <if test="coOwner != null">#{coOwner},</if>
            <if test="detailedAddress != null">#{detailedAddress},</if>
            <if test="hasParkingSpace != null">#{hasParkingSpace},</if>
            <if test="parkingNumber != null">#{parkingNumber},</if>
            <if test="communityName != null">#{communityName},</if>
            <if test="houseStatus != null">#{houseStatus},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="remark != null">#{remark},</if>
         </trim>
    </insert>

    <update id="updateHouseInfo" parameterType="HouseInfo">
        update house_info
        <trim prefix="SET" suffixOverrides=",">
            <if test="ownerName != null">owner_name = #{ownerName},</if>
            <if test="location != null">location = #{location},</if>
            <if test="rightType != null">right_type = #{rightType},</if>
            <if test="area != null">area = #{area},</if>
            <if test="documentNumber != null">document_number = #{documentNumber},</if>
            <if test="propertyUnitNumber != null">property_unit_number = #{propertyUnitNumber},</if>
            <if test="coOwnership != null">co_ownership = #{coOwnership},</if>
            <if test="usageType != null">usage_type = #{usageType},</if>
            <if test="usagePeriod != null">usage_period = #{usagePeriod},</if>
            <if test="registrationDate != null">registration_date = #{registrationDate},</if>
            <if test="coOwner != null">co_owner = #{coOwner},</if>
            <if test="detailedAddress != null">detailed_address = #{detailedAddress},</if>
            <if test="hasParkingSpace != null">has_parking_space = #{hasParkingSpace},</if>
            <if test="parkingNumber != null">parking_number = #{parkingNumber},</if>
            <if test="communityName != null">community_name = #{communityName},</if>
            <if test="houseStatus != null">house_status = #{houseStatus},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="remark != null">remark = #{remark},</if>
        </trim>
        where house_id = #{houseId}
    </update>

    <delete id="deleteHouseInfoByHouseId" parameterType="Long">
        delete from house_info where house_id = #{houseId}
    </delete>

    <delete id="deleteHouseInfoByHouseIds" parameterType="String">
        delete from house_info where house_id in 
        <foreach item="houseId" collection="array" open="(" separator="," close=")">
            #{houseId}
        </foreach>
    </delete>
</mapper>