<?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.butlerService.LeaseDao">

    <select id="list" parameterType="com.api.model.butlerService.SearchLease" resultType="com.api.vo.butlerService.VoLease">
        select sl.*,su1.actual_name as reviewer_name,su2.actual_name as create_name,CONCAT(cb.no,'-',cbu.no,'-',cbue.room_number) as room_name
        from sys_user as su2,cpm_building_unit_estate as cbue,cpm_building_unit as cbu,cpm_building as cb,sys_lease as sl
		left JOIN sys_user as su1 on sl.reviewer = su1.id
		<where>
            sl.create_id = su2.id
            and sl.estate_id = cbue.id
            and cbue.building_unit_id = cbu.id
            and cbu.building_id = cb.id
            <if test="name != null and name != ''">
                and sl.name like concat('%',#{name},'%')
            </if>
            <if test="idCard != null and idCard != ''">
                and sl.id_card like concat('%',#{idCard},'%')
            </if>
            <if test="tel != null and tel != ''">
                and sl.tel like concat('%',#{tel},'%')
            </if>
            <if test="estateId != null">
                and sl.estate_id = #{estateId}
            </if>
            <if test="type != null">
                and sl.type = #{type}
            </if>
            <if test="status != null">
                and sl.status = #{status}
            </if>
        </where>
    </select>
    <select id="findById" parameterType="java.lang.Integer" resultType="com.api.vo.butlerService.VoFBILease">
        select sl.*,cb.id as building_id,cb.no as building_no,cbu.id as unit_id,cbu.no as unit_no,cbue.id as estate_id,cbue.room_number as room_number
        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.id = #{id}
    </select>

    <insert id="insert" parameterType="com.api.model.butlerService.SysLease">
        insert into sys_lease(
            lease_parent_id,
            code,
            name,
            sex,
            id_card,
            tel,
            estate_id,
            type,
            estate_type,
            estate_structure,
            construction_area,
            indoor_area,
            rent_standard,
            margin,
            lease_date_start,
            lease_date_end,
            status,
            create_id,
            create_date
        )values (
            #{leaseParentId},
            #{code},
            #{name},
            #{sex},
            #{idCard},
            #{tel},
            #{estateId},
            #{type},
            #{estateType},
            #{estateStructure},
            #{constructionArea},
            #{indoorArea},
            #{rentStandard},
            #{margin},
            #{leaseDateStart},
            #{leaseDateEnd},
            #{status},
            #{createId},
            #{createDate}
        )
    </insert>


    <update id="update" parameterType="com.api.model.butlerService.SysLease">
        update sys_lease
        <set>
            <if test="code != null and code != ''">
                code = #{code},
            </if>
            <if test="name != null and name != ''">
                name = #{name},
            </if>
            <if test="sex != null">
                sex = #{sex},
            </if>
            <if test="idCard != null and idCard != ''">
                id_card = #{idCard},
            </if>
            <if test="tel != null and tel != ''">
                tel = #{tel},
            </if>
            <if test="estateId != null">
                estate_id = #{estateId},
            </if>
            <if test="type != null">
                type = #{type},
            </if>
            <if test="estateType != null and estateType != ''">
                estate_type = #{estateType},
            </if>
            <if test="estateStructure != null and estateStructure != ''">
                estate_structure = #{estateStructure},
            </if>
            <if test="constructionArea != null">
                construction_area = #{constructionArea},
            </if>
            <if test="indoorArea != null">
                indoor_area = #{indoorArea},
            </if>
            <if test="rentStandard != null">
                rent_standard = #{rentStandard},
            </if>
            <if test="margin != null">
                margin = #{margin},
            </if>
            <if test="leaseDateStart != null">
                lease_date_start = #{leaseDateStart},
            </if>
            <if test="leaseDateEnd != null">
                lease_date_end = #{leaseDateEnd},
            </if>
        </set>
        where id = #{id}
    </update>
    <update id="reviewer">
        update sys_lease
        set status = #{status},
        reviewer = #{reviewer},
        audit_date = #{auditDate},
        audit_remake = #{auditRemake}
        where id = #{id}
    </update>

    <delete id="delete" parameterType="java.lang.Integer">
        delete from sys_lease where id = #{id}
    </delete>

    <insert id="insertOrder" parameterType="com.api.model.alipay.SysLeaseOrder">
        insert into sys_lease_order(
            sys_lease_id,
            code,
            name,
            tel,
            pay_type,
            pay_price,
            create_id,
            create_date,
            status
        )values (
            #{sysLeaseId},
            #{code},
            #{name},
            #{tel},
            #{payType},
            #{payPrice},
            #{createId},
            #{createDate},
            #{status}
        )
    </insert>

    <select id="findSysLeaseOrderByCode" parameterType="java.lang.String" resultType="com.api.model.alipay.SysLeaseOrder">
        select * from sys_lease_order where code = #{code}
    </select>

    <update id="updateLeaseOrderStatusByCode" parameterType="com.api.model.alipay.SysLeaseOrder">
        update sys_lease_order
        set status = #{status}
        where code = #{code}
    </update>
    <update id="updateStatusById" parameterType="com.api.model.butlerService.SysLease">
        update sys_lease
        set status = #{status}
        where id = #{id}
    </update>

    <insert id="insertRentOrder" parameterType="com.api.model.alipay.SysLeaseRentOrder">
        insert into sys_lease_rent_order(
            sys_lease_id,
            code,
            name,
            tel,
            pay_type,
            pay_price,
            create_id,
            create_date,
            status
        )values (
            #{sysLeaseId},
            #{code},
            #{name},
            #{tel},
            #{payType},
            #{payPrice},
            #{createId},
            #{createDate},
            #{status}
        )
    </insert>
    <insert id="insertRent" parameterType="com.api.model.app.AppLeaseRent">
        insert into sys_lease_rent(
            sys_lease_id,
            price,
            status,
            create_date
        )values (
            #{sysLeaseId},
            #{price},
            #{status},
            #{createDate}
        )
    </insert>

    <select id="findSysLeaseRentOrderByCode" parameterType="java.lang.String" resultType="com.api.model.alipay.SysLeaseRentOrder">
        select * from sys_lease_rent_order where code = #{outTradeNo}
    </select>

    <update id="updateLeaseRentOrderStatusByCode" parameterType="com.api.model.alipay.SysLeaseRentOrder">
        update sys_lease_rent_order
        set status = #{status}
        where code = #{code}
    </update>
    <update id="reviewTerminationApplication" parameterType="com.api.model.butlerService.SysLease">
        update sys_lease
        set status = #{status},
        not_meter_rent_date = #{notMeterRentDate},
        required_rent = #{requiredRent}
        where id = #{id}
    </update>
    <update id="reviewDepositRefundApplication" parameterType="com.api.model.butlerService.SysLease">
        update sys_lease
        set status = #{status},
        deposit_refund_review_remake = #{depositRefundReviewRemake}
        where id = #{id}
    </update>

    <select id="findPaySysLeaseOrderById" parameterType="java.lang.Integer" resultType="com.api.model.alipay.SysLeaseOrder">
        select * from sys_lease_order where sys_lease_id = #{id} and status = 2
    </select>
    <select id="findUnPaymentLeaseOrder" resultType="com.api.model.alipay.SysLeaseOrder">
        select * from sys_lease_order where status = 0 and NOW() <![CDATA[ >= ]]> DATE_ADD(create_date,INTERVAL 30 MINUTE)
    </select>
    <select id="findAllEffectLease" resultType="com.api.vo.butlerService.VoLease">
        select * from sys_lease where status in (6,11,12)
    </select>

    <update id="updateSLOStatusByCode" parameterType="com.api.model.alipay.SysLeaseOrder">
        update sys_lease_order
        set status = #{status}
        where code = #{code}
    </update>
    <update id="updateMarginPayDateById" parameterType="com.api.model.butlerService.SysLease">
        update sys_lease
        set margin_pay_date = #{marginPayDate}
        where id = #{id}
    </update>

    <select id="findLeaseRemainingRental" parameterType="java.lang.Integer" resultType="java.math.BigDecimal">
        select (case sum(price) is null when true then 0 else sum(price) end) as sum
        from sys_lease_rent
        where sys_lease_id = #{leaseId} and status = 0
    </select>
    <select id="findLeaseRentById" parameterType="java.lang.Integer" resultType="com.api.model.app.AppLeaseRent">
        select * from sys_lease_rent where id = #{sysLeaseRentId}
    </select>

    <insert id="insertRentBillOrder" parameterType="com.api.model.alipay.SysLeaseRentBillOrder">
        insert into sys_lease_rent_bill_order(
            sys_lease_rent_id,
            code,
            name,
            tel,
            pay_type,
            pay_price,
            create_id,
            create_date,
            status
        )values (
            #{sysLeaseRentId},
            #{code},
            #{name},
            #{tel},
            #{payType},
            #{payPrice},
            #{createId},
            #{createDate},
            #{status}
        )
    </insert>
    <insert id="renew" parameterType="com.api.model.butlerService.SysLeaseRenew">
        insert into sys_lease(
            lease_parent_id,
            code,
            name,
            sex,
            id_card,
            tel,
            estate_id,
            type,
            estate_type,
            estate_structure,
            construction_area,
            indoor_area,
            rent_standard,
            margin,
            lease_date_start,
            lease_date_end,
            status,
            create_id,
            create_date,
            emergency_contact,
            emergency_contact_number,
            correspondence_address,
            work_units,
            pay_bank,
            bank_account_name,
            bank_account
        )values (
            #{leaseParentId},
            #{code},
            #{name},
            #{sex},
            #{idCard},
            #{tel},
            #{estateId},
            #{type},
            #{estateType},
            #{estateStructure},
            #{constructionArea},
            #{indoorArea},
            #{rentStandard},
            #{margin},
            #{leaseDateStart},
            #{leaseDateEnd},
            #{status},
            #{createId},
            #{createDate},
            #{emergencyContact},
            #{emergencyContactNumber},
            #{correspondenceAddress},
            #{workUnits},
            #{payBank},
            #{bankAccountName},
            #{bankAccount}
        )
        <selectKey keyProperty="id" resultType="java.lang.Integer">
            select LAST_INSERT_ID() AS id
        </selectKey>
    </insert>

    <select id="findSysLeaseRentBillOrderByCode" parameterType="java.lang.String" resultType="com.api.model.alipay.SysLeaseRentBillOrder">
        select * from sys_lease_rent_bill_order where code = #{outTradeNo}
    </select>
    <select id="findUnPaymentLeaseRentOrder" resultType="com.api.model.alipay.SysLeaseRentOrder">
        select * from sys_lease_rent_order where status = 0 and NOW() <![CDATA[ >= ]]> DATE_ADD(create_date,INTERVAL 30 MINUTE)
    </select>
    <select id="findUnPaymentLeaseRentBillOrder" resultType="com.api.model.alipay.SysLeaseRentBillOrder">
        select * from sys_lease_rent_bill_order where status = 0 and NOW() <![CDATA[ >= ]]> DATE_ADD(create_date,INTERVAL 30 MINUTE)
    </select>

    <update id="updateLeaseRentBillOrderStatusByCode" parameterType="com.api.model.alipay.SysLeaseRentBillOrder">
        update sys_lease_rent_bill_order
        set status = #{status}
        where code = #{code}
    </update>
    <update id="updateLeaseRentStatusById" parameterType="com.api.model.app.AppLeaseRent">
        update sys_lease_rent
        set status = #{status}
        where id = #{id}
    </update>
    <update id="updateSLRStatusUnPayToSettledBySLId" parameterType="java.lang.Integer">
        update sys_lease_rent
        set status = 2
        where sys_lease_id = #{sysLeaseId} and status = 0
    </update>
    <update id="updateSLROStatusByCode" parameterType="com.api.model.alipay.SysLeaseRentOrder">
        update sys_lease_rent_order
        set status = #{status}
        where code = #{code}
    </update>
    <update id="updateSLRBOStatusByCode" parameterType="com.api.model.alipay.SysLeaseRentBillOrder">
        update sys_lease_rent_bill_order
        set status = #{status}
        where code = #{code}
    </update>

    <select id="findPaymentRecordsById" parameterType="java.lang.Integer" resultType="com.api.model.butlerService.SysLeasePaymentRecords">
        select id,code,1 as type,pay_price,(case status = 2 when true then 1 else 0 end) as status,create_date from sys_lease_order where sys_lease_id = #{id}
        union all
        select slr.id,slrbo.code,2 as type,slr.price as pay_price,slr.status,slrbo.create_date
        from sys_lease_rent as slr LEFT JOIN sys_lease_rent_bill_order as slrbo on slrbo.sys_lease_rent_id = slr.id
        where slr.sys_lease_id = #{id}
    </select>

</mapper>