<?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.my.MyHouseDao">
    <select id="houseList" parameterType="java.lang.Integer" resultType="com.api.vo.my.MyHouseListVo">
        select cre.id,cre.building_unit_estate_id as estate_id,concat(cb.no,'-',cbu.no,'-',cbue.room_number) as room_name,
        ur.type,cre.effective_time_start,cre.effective_time_end,cre.sys_lease_id
        from cpm_resident_estate as cre,user_resident as ur,cpm_building_unit_estate as cbue,cpm_building_unit as cbu,cpm_building as cb
        where cre.resident_id = ur.id and cre.building_unit_estate_id = cbue.id and cbue.building_unit_id = cbu.id
        and cbu.building_id = cb.id and cre.resident_id = #{id}
    </select>

    <select id="examineList" parameterType="java.lang.Integer" resultType="com.api.vo.my.MyHouseExamineVo">
        select cree.id,cree.building_unit_estate_id as estate_id,concat(cb.no,'-',cbu.no,'-',cbue.room_number) as room_name,cree.type,cree.status,cree.effective_time_start,cree.effective_time_end
        from cpm_resident_estate_examine as cree,cpm_building_unit_estate as cbue,cpm_building_unit as cbu,cpm_building as cb
        where cree.building_unit_estate_id = cbue.id and cbue.building_unit_id = cbu.id and cbu.building_id = cb.id and cree.resident_id = #{id} and cree.is_delete = 1
        order by cree.create_date DESC
    </select>

    <select id="countNotReviewed" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select count(*) from cpm_resident_estate_examine where resident_id = #{residentId} and status = 1
    </select>

    <update id="falseDelete" parameterType="java.lang.Integer">
        update cpm_resident_estate_examine set is_delete = 0 where id = #{id}
    </update>

    <select id="findById" parameterType="java.lang.Integer" resultType="com.api.vo.my.MyHouseFBIVo">
        select cree.id,concat(cb.no,'-',cbu.no,'-',cbue.room_number) as room_name,cree.building_unit_estate_id as estate_id,cree.type,cree.name,cree.id_type,cree.id_number,cree.effective_time_start,cree.effective_time_end,cree.status
        from cpm_resident_estate_examine as cree,cpm_building_unit_estate as cbue,cpm_building_unit as cbu,cpm_building as cb
        where cree.building_unit_estate_id = cbue.id and cbue.building_unit_id = cbu.id and cbu.building_id = cb.id and cree.is_delete = 1 and cree.id = #{estateExamineId}
    </select>

    <select id="findStatusById" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select status from cpm_resident_estate_examine where id = #{id}
    </select>
    <select id="findResidentIdById" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select resident_id from cpm_resident_estate_examine where id = #{id}
    </select>
    <select id="findEstateIdById" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select building_unit_estate_id from cpm_resident_estate_examine where id = #{id}
    </select>
    <select id="findNowEstateIdByResidentId" resultType="java.lang.Integer">
        select now_estate_id from user_resident where id = #{residentId}
    </select>
    <select id="findDBEstaIdByResidentId" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select building_unit_estate_id from cpm_resident_estate where resident_id = #{residentId}
    </select>
    <select id="findEstateInfoByResidentId" parameterType="com.api.model.basicArchives.ResidentIdAndEstateId" resultType="com.api.vo.my.MyHouseEstateInfoVo">
        select id,effective_time_start,effective_time_end from cpm_resident_estate where building_unit_estate_id = #{estateId} and resident_id = #{residentId}
    </select>
    <select id="countSuccessReviewed" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select building_unit_estate_id from cpm_resident_estate_examine where resident_id = #{residentId} and is_delete = 1 and status = 4
    </select>
    <select id="findSBResidentInfoByResidentId" parameterType="java.lang.Integer" resultType="com.api.vo.my.MyHouseResidentInfoVo">
        select id,name,id_type,id_number from user_resident where id = #{residentId}
    </select>

    <update id="updateEstateExamineId" parameterType="com.api.model.app.AppUserIdAndExamineId">
        update user_resident set now_estate_examine_id = #{examineId} where id = #{userId}
    </update>
    <update id="updateUserResidentInfo" parameterType="com.api.model.basicArchives.UserResident">
        update user_resident
        set name = #{name},
        sex = #{sex},
        id_type = #{idType},
        id_number = #{idNumber}
        where tel = #{tel} and type = 4
    </update>
    <update id="submitPersonalLeaseInfo" parameterType="com.api.model.butlerService.SysLease">
        update sys_lease
        set emergency_contact = #{emergencyContact},
        emergency_contact_number = #{emergencyContactNumber},
        correspondence_address = #{correspondenceAddress},
        work_units = #{workUnits},
        pay_bank = #{payBank},
        bank_account_name = #{bankAccountName},
        bank_account = #{bankAccount}
        where id = #{id}
    </update>
    <update id="updateStatusById" parameterType="com.api.model.butlerService.SysLease">
        update sys_lease
        set status = #{status}
        where id = #{id}
    </update>

    <select id="findLeaseByTel" parameterType="java.lang.String" resultType="com.api.model.butlerService.SysLease">
        select * from sys_lease where tel = #{tel}
    </select>
    <select id="findLeaseInfoByTel" parameterType="java.lang.String" resultType="com.api.vo.app.AppLeaseInfoVo">
        select * from user_resident where tel = #{tel} and type = 4
    </select>
    <select id="leaseList" parameterType="com.api.model.my.SearchSysLease" resultType="com.api.vo.app.AppLeaseVo">
        select sl.*,CONCAT(cb.no,'-',cbu.no,'-',cbue.room_number) as room_name
        from sys_lease as sl,cpm_building_unit_estate as cbue,cpm_building_unit as cbu,cpm_building as cb
        <where>
            sl.estate_id = cbue.id
            and cbue.building_unit_id = cbu.id
            and cbu.building_id = cb.id
            and sl.tel = #{tel}
            <if test="leaseParentId != null">
                and lease_parent_id = #{leaseParentId}
            </if>
        </where>
    </select>
    <select id="leaseFindById" parameterType="java.lang.Integer" resultType="com.api.vo.butlerService.VoLease">
        select sl.*,CONCAT(cb.no,'-',cbu.no,'-',cbue.room_number) as room_name,su1.actual_name as create_name,su2.actual_name as reviewer_name
        from cpm_building_unit_estate as cbue,cpm_building_unit as cbu,cpm_building as cb,sys_user as su1,sys_lease as sl
		LEFT JOIN sys_user su2 on sl.reviewer = su2.id
        where sl.estate_id = cbue.id and cbue.building_unit_id = cbu.id and cbu.building_id = cb.id and sl.create_id = su1.id and sl.id = #{leaseId}
    </select>
    <select id="findEnableLeaseContract" resultType="com.api.model.butlerService.SysLeaseContract">
        select * from sys_lease_contract where status = 1
    </select>
    <select id="findLeaseById" resultType="com.api.model.butlerService.SysLease">
        select * from sys_lease where id = #{id}
    </select>
    <select id="findLeaseRentList" parameterType="com.api.model.app.SearchAppLeaseRent" resultType="com.api.vo.app.AppLeaseRentVo">
        select a.* from (
            select id,price,status as pay_status,create_date,2 as type from sys_lease_rent where sys_lease_id = #{sysLeaseId}
            union all
            select id,pay_price as price,1 as pay_status,create_date,1 as type from sys_lease_order where status = 2 and sys_lease_id = #{sysLeaseId}
            union all
            select id,pay_price as price,1 as pay_status,create_date,3 as type from sys_lease_rent_order where status = 2 and sys_lease_id = #{sysLeaseId}
        ) as a
        where YEAR(a.create_date) = #{years}
        order by a.create_date desc
    </select>

    <update id="submitTerminateApplication" parameterType="com.api.model.butlerService.SysLease">
        update sys_lease
        set status = #{status},
        take_date = #{takeDate}
        where id = #{id}
    </update>

</mapper>