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

    <select id="list" parameterType="com.api.model.basicArchives.AuditManagementSearch" resultType="com.api.vo.basicArchives.VoAuditManagement">
        select cree.id,cree.name,cree.type,ur1.tel,cree.id_type,cree.id_number,concat(cb.no,'-',cbu.no,'-',cbue.room_number) as room_name,
        cree.status,cree.effective_time_start,cree.effective_time_end,T.resident_name,T.resident_tel,cree.create_date,cree.reviewer_date,
        (case cree.reviewer when -1 then 'app自动审核' else su.actual_name end) as reviewer_name

        from user_resident as ur1,cpm_building_unit_estate as cbue,cpm_building_unit as cbu,cpm_building as cb,
        cpm_resident_estate_examine as cree
        LEFT JOIN (
            select cre.building_unit_estate_id,GROUP_CONCAT(ur2.name) as resident_name,GROUP_CONCAT(ur2.tel) as resident_tel from cpm_resident_estate as cre,user_resident as ur2
            where cre.resident_id = ur2.id GROUP BY cre.building_unit_estate_id
        ) as T
        on cree.building_unit_estate_id = T.building_unit_estate_id

        LEFT JOIN sys_user as su
        on cree.reviewer = su.id
        <where>
            cree.resident_id = ur1.id
            and cree.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="buildingNo != null">
                and cb.no like concat('%',#{buildingNo},'%')
            </if>
            <if test="status != null">
                and cree.status = #{status}
            </if>
            <if test="type != null">
                and cree.type = #{type}
            </if>
        </where>
    </select>
    <select id="findById" parameterType="java.lang.Integer" resultType="com.api.vo.basicArchives.VoFBIAuditManagement">
        select cree.id,cree.resident_id as applicant_id,cree.name,cree.type,cree.status,ur1.tel,cree.id_type,cree.id_number,cree.building_unit_estate_id as estate_id,concat(cb.no,'-',cbu.no,'-',cbue.room_number) as room_name,
        cree.effective_time_start,cree.effective_time_end,T.resident_id,T.resident_name,T.resident_tel,cree.create_date

        from user_resident as ur1,cpm_building_unit_estate as cbue,cpm_building_unit as cbu,cpm_building as cb,
        cpm_resident_estate_examine as cree
        LEFT JOIN (
            select cre.building_unit_estate_id,GROUP_CONCAT(ur2.id) as resident_id,GROUP_CONCAT(ur2.name) as resident_name,GROUP_CONCAT(ur2.tel) as resident_tel from cpm_resident_estate as cre,user_resident as ur2
            where cre.resident_id = ur2.id GROUP BY cre.building_unit_estate_id
        ) as T
        on cree.building_unit_estate_id = T.building_unit_estate_id
        <where>
            cree.resident_id = ur1.id
            and cree.building_unit_estate_id = cbue.id
            and cbue.building_unit_id = cbu.id
            and cbu.building_id = cb.id
            and cree.id = #{estateExamineId}
        </where>
    </select>

    <update id="review" parameterType="com.api.model.basicArchives.Review">
        update cpm_resident_estate_examine
        set status = #{status},remakes = #{remakes},reviewer_date = #{reviewerDate}
        where id = #{id}
    </update>

    <insert id="insertResidentEstate" parameterType="com.api.model.basicArchives.CpmResidentEstate">
        insert into
        <trim prefix="cpm_resident_estate(" suffix=")" suffixOverrides=",">
            building_unit_estate_id,
            resident_id,
            <if test="effectiveTimeStart != null">
                effective_time_start,
            </if>
            <if test="effectiveTimeEnd != null">
                effective_time_end,
            </if>
            <if test="sysLeaseId != null">
                sys_lease_id,
            </if>
        </trim>
        values
        <trim prefix="(" suffix=")" suffixOverrides=",">
            #{buildingUnitEstateId},
            #{residentId},
            <if test="effectiveTimeStart != null">
                #{effectiveTimeStart},
            </if>
            <if test="effectiveTimeEnd != null">
                #{effectiveTimeEnd},
            </if>
            <if test="sysLeaseId != null">
                #{sysLeaseId},
            </if>
        </trim>
    </insert>


    <update id="updateEstateStatus" parameterType="com.api.model.basicArchives.CpmBuildingUnitEstate">
        update cpm_building_unit_estate set status = #{status} where id =#{id}
    </update>

    <insert id="insertResidentRelatives" parameterType="com.api.model.basicArchives.UserResidentRelatives">
        insert into user_resident_relatves(
            resident_id,
            relatives_id,
            identity
        )values (
            #{residentId},
            #{relativesId},
            #{identity}
        )
    </insert>

    <update id="updateResident" parameterType="com.api.model.basicArchives.UserResident">
        update user_resident
        set name = #{name},
        type = #{type},
        id_type = #{idType},
        id_number = #{idNumber}
        where id = #{id}
    </update>
    <update id="updateResidentTypeById" parameterType="com.api.model.basicArchives.UserResident">
        update user_resident
        set type = #{type}
        where id = #{id}
    </update>

    <delete id="deleteByRIDAndEID" parameterType="com.api.model.basicArchives.ResidentIdAndEstateId">
        delete from cpm_resident_estate
        where building_unit_estate_id = #{estateId} and resident_id = #{residentId}
    </delete>
    <delete id="deleteByRIDAndRID" parameterType="com.api.model.basicArchives.ResidentIdAndRelativesId">
        delete from user_resident_relatives
        where resident_id = #{ResidentId} and relatives_id = #{RelativesId}
    </delete>
    <delete id="delete" parameterType="java.lang.Integer">
        delete from cpm_resident_estate_examine where id = #{id}
    </delete>

    <select id="checkById" parameterType="java.lang.Integer" resultType="com.api.vo.basicArchives.VoCheckAuditById">
        select cree.id,cree.resident_id as applicant_id,cree.name,cree.type,cree.status,ur1.tel,cree.id_type,cree.id_number,cree.building_unit_estate_id as estate_id,concat(cb.no,'-',cbu.no,'-',cbue.room_number) as room_name,
        cree.effective_time_start,cree.effective_time_end,T.resident_id,T.resident_name,T.resident_tel,cree.create_date,cree.remakes

        from user_resident as ur1,cpm_building_unit_estate as cbue,cpm_building_unit as cbu,cpm_building as cb,
        cpm_resident_estate_examine as cree
        LEFT JOIN (
        select cre.building_unit_estate_id,GROUP_CONCAT(ur2.id) as resident_id,GROUP_CONCAT(ur2.name) as resident_name,GROUP_CONCAT(ur2.tel) as resident_tel from cpm_resident_estate as cre,user_resident as ur2
        where cre.resident_id = ur2.id GROUP BY cre.building_unit_estate_id
        ) as T
        on cree.building_unit_estate_id = T.building_unit_estate_id
        <where>
            cree.resident_id = ur1.id
            and cree.building_unit_estate_id = cbue.id
            and cbue.building_unit_id = cbu.id
            and cbu.building_id = cb.id
            and cree.id = #{estateExamineId}
        </where>
    </select>

</mapper>