<?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.butler.DecorationApplicationDao">
    <select id="list" parameterType="com.api.model.app.SearchAppDecoration" resultType="com.api.vo.app.AppDecorationVo">
        select ud.id,ud.application_date,ud.status,CONCAT(cb.no,"-",cbu.no,"-",cbue.room_number) as room_name from user_decoration as ud,cpm_building_unit_estate as cbue,cpm_building_unit as cbu,cpm_building as cb
        where ud.building_unit_estate_id = cbue.id and cbue.building_unit_id = cbu.id and cbu.building_id = cb.id and cbue.id = #{estateId}
    </select>
    <select id="findDecorationDeposit" resultType="com.api.vo.app.AppDecorationCostVo">
        select sctd.id,sctd.name,sctd.unit_price from sys_charges_template as sct,sys_charges_template_detail as sctd
        where sct.id = sctd.charges_template_id and sct.status = 1 and sctd.status = 1 and sctd.marker = 3
    </select>

    <select id="findDecorationAdditionalCost" parameterType="java.lang.Integer" resultType="com.api.vo.app.AppDecorationAdditionalCostVo">
        select id,name,cost from sys_charges_template_additional_cost where charges_template_detail_id = #{id}
    </select>

    <select id="findDecorationDocUrl" resultType="java.lang.String">
        select url from user_decoration_doc limit 1
    </select>

    <select id="applicationDecoration" parameterType="com.api.model.app.UserIdAndEstateId" resultType="java.lang.Integer">
        select count(*) from user_resident as ur,cpm_resident_estate as cre
        where ur.id = cre.resident_id and ur.id = #{id} and cre.building_unit_estate_id = #{estateId}
    </select>

    <select id="findUserTypeByUserId" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select type from user_resident where id = #{id}
    </select>

    <insert id="insertDecorationApplication" parameterType="com.api.model.app.AppUserDecoration">
        insert into user_decoration(
            building_unit_estate_id,
            code,
            resident_id,
            resident_type,
            application_date,
            status
        ) values (
            #{buildingUnitEstateId},
            #{code},
            #{residentId},
            #{residentType},
            #{applicationDate},
            #{status}
        )
        <selectKey keyProperty="id" resultType="java.lang.Integer">
            select LAST_INSERT_ID() AS id
        </selectKey>
    </insert>

    <update id="update" parameterType="com.api.model.app.AppUserDecoration">
        update user_decoration
        <set>
            <if test="constructionUnit != null and constructionUnit != ''">
                construction_unit = #{constructionUnit},
            </if>
            <if test="director != null and director != ''">
                director = #{director},
            </if>
            <if test="directorTel != null and directorTel != ''">
                director_tel = #{directorTel},
            </if>
            <if test="emergencyContact != null and emergencyContact != ''">
                emergency_contact = #{emergencyContact},
            </if>
            <if test="tel != null and tel != ''">
                tel = #{tel},
            </if>
            <if test="expectedBegin != null">
                expected_begin = #{expectedBegin},
            </if>
            <if test="expectedEnd != null">
                expected_end = #{expectedEnd},
            </if>
        </set>
        where id = #{id}
    </update>

    <select id="findApplicationDecoration" parameterType="java.lang.Integer" resultType="com.api.vo.app.AppDecorationApplicationVo">
        select * from user_decoration where id = #{id}
    </select>

    <insert id="insertDepositManagement" parameterType="com.api.model.app.AppDepositManagement">
        insert into sys_deposit_management(
            user_decoration_id,
            order_code,
            charges_template_detail_id,
            pay_date,
            deposit_price,
            status,
            froms,
            pay_type,
            pay_account,
            <if test="remake != null and remake != ''">
                remake,
            </if>
            create_id,
            create_date,
            is_delete
        )values (
            #{userDecorationId},
            #{orderCode},
            #{chargesTemplateDetailId},
            #{payDate},
            #{depositPrice},
            #{status},
            #{froms},
            #{payType},
            #{payAccount},
            <if test="remake != null and remake != ''">
                #{remake},
            </if>
            #{createId},
            #{createDate},
            #{isDelete}
        )
        <selectKey keyProperty="id" resultType="java.lang.Integer">
            select LAST_INSERT_ID() AS id
        </selectKey>
    </insert>

    <insert id="insertDepositAdditionalCost" parameterType="com.api.model.app.AppDepositAdditionalCost">
        insert into sys_deposit_management_additional_cost(
            sys_deposit_management_id,
            name,
            cost
        ) values (
            #{sysDepositManagementId},
            #{name},
            #{cost}
        )
    </insert>
    <select id="findDecorationCodeById" parameterType="java.lang.Integer" resultType="java.lang.String">
        select code from user_decoration where id = #{decorationId}
    </select>

    <select id="findDecorationIdByCode" parameterType="java.lang.String" resultType="java.lang.Integer">
        select id from user_decoration where code = #{code}
    </select>

    <insert id="insertDecorationPerson" parameterType="com.api.model.app.AppUserDecorationPerson">
        insert into user_decoration_personnel(
            decoration_id,
            name,
            identity,
            card_type,
            card_code,
            tel,
            address
        ) values (
            #{decorationId},
            #{name},
            #{identity},
            #{cardType},
            #{cardCode},
            #{tel},
            #{address}
        )
    </insert>

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

    <update id="updateStatus" parameterType="com.api.model.app.AppUserDecoration" >
        update user_decoration set status = #{status} where id= #{id}
    </update>

    <select id="findDecorationById" parameterType="java.lang.Integer" resultType="com.api.vo.app.AppDecorationFBIVo">
        select ud.*,concat(cb.no,'-',cbu.no,'-',cbue.room_number) as room_name
        from user_decoration as ud,cpm_building_unit_estate as cbue,cpm_building_unit as cbu,cpm_building as cb
        where ud.building_unit_estate_id = cbue.id and cbue.building_unit_id = cbu.id and cbu.building_id = cb.id and ud.id = #{decorationId}
    </select>

    <select id="findDepositById" parameterType="java.lang.Integer" resultType="com.api.vo.app.AppDepositVo">
        select * from sys_deposit_management where user_decoration_id = #{decorationId}
    </select>

    <select id="findDACostByDId" parameterType="java.lang.Integer" resultType="com.api.vo.app.AppDepositAdditionalCostVo">
        select * from sys_deposit_management_additional_cost where sys_deposit_management_id = #{id}
    </select>

    <select id="findPersonByDecorationId" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select count(*) from user_decoration_personnel where decoration_id = #{decorationId}
    </select>

    <select id="findAllDetail" parameterType="java.lang.Integer" resultType="com.api.vo.app.AppDecorationFindAllDetailVo">
        select ud.*,su.actual_name as tracker_name,su.tel as tracker_tel,concat(cb.no,'幢',cbu.no,'单元',cbue.room_number) as room_name
        from user_decoration as ud,sys_user as su,cpm_building_unit_estate as cbue,cpm_building_unit as cbu,cpm_building as cb
        where ud.resident_id = su.id and ud.building_unit_estate_id = cbue.id and cbue.building_unit_id = cbu.id and cbu.building_id = cb.id and ud.id = #{decorationId}
    </select>

    <select id="findTrackRecord" parameterType="java.lang.Integer" resultType="com.api.vo.app.AppTrackRecordVo">
        select * from user_decoration_track_record where decoration_id = #{decorationId}
    </select>

    <select id="findTrackRecordDetail" parameterType="java.lang.Integer" resultType="com.api.vo.app.AppTrackRecordDetailVo">
        select udtrd.id,udtrd.is_qualified,udtcc.name as checks_content
        from user_decoration_track_record_detail as udtrd,user_decoration_track_checks_content as udtcc
        where udtrd.decoration_track_checks_content_id = udtcc.id and udtrd.decoration_track_record_id = #{id}
    </select>

    <update id="extendDecorationTime" parameterType="com.api.model.app.AppExtendDecoration">
        update user_decoration
        set extend_time = #{extendTime},
        extend_reasons = #{extendReasons},
        extend_date = #{extendDate}
        where id = #{decorationId}
    </update>

    <update id="applicationRefund" parameterType="com.api.model.app.AppUserDecoration" >
        update user_decoration
        set status = #{status},
        application_refund_date = #{applicationRefundDate}
        where id= #{id}
    </update>

    <select id="findRefundByDecorationId" parameterType="java.lang.Integer" resultType="com.api.vo.app.AppDepositRefundOrderVo">
        select ud.id,ud.status,ud.application_refund_date,sdm.pay_account,sdm.deposit_price,sdmro.refund_date,sdmro.code
        from user_decoration as ud,sys_deposit_management as sdm
        LEFT JOIN sys_deposit_management_refund_order as sdmro
        on sdmro.id = sdm.user_decoration_id
        where ud.id = sdm.user_decoration_id and ud.id = #{decorationId}
    </select>

    <update id="startDecoration" parameterType="com.api.model.app.AppUserDecoration" >
        update user_decoration
        set status = #{status},
        actual_begin = #{actualBegin}
        where id= #{id}
    </update>

    <select id="findStatusById" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select status from user_decoration where id =#{id}
    </select>

    <update id="applicationReview" parameterType="com.api.model.app.AppUserDecoration">
        update user_decoration
        set status = #{status},
        approve_results = #{approveResults},
        approve_id = #{approveId},
        approve_date = #{approveDate}
        where id =#{id}
    </update>

</mapper>