<?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.UserDecorationDao">
    <select id="list" parameterType="com.api.model.butlerService.SearchUserDecoration" resultType="com.api.vo.butlerService.VoUserDecoration">
        select ud.id,concat(cb.no,'-',cbu.no,'-',cbue.room_number) as room_name,ud.code,ur1.name as application_name,ud.resident_type as application_type,ur1.tel as application_tel
        ,a.resident_name,a.resident_tel,ud.emergency_contact,ud.tel,ud.construction_unit,ud.director,ud.director_tel,ud.application_date
        ,ud.expected_begin,ud.expected_end,ud.actual_begin,ud.actual_end,ud.status,ud.extend_time,ud.extend_reasons,ud.is_qualified
        from user_decoration as ud,user_resident as ur1,cpm_building_unit_estate as cbue,cpm_building_unit as cbu,cpm_building as cb,
        (
        select cbue.id,ur2.name as resident_name,ur2.tel as resident_tel
        from cpm_building_unit_estate as cbue,cpm_resident_estate as cre,user_resident as ur2
        where cbue.id = cre.building_unit_estate_id and cre.resident_id = ur2.id and ur2.type = 1 and cbue.id = 1 limit 1
        ) as a
        <where>
            ud.resident_id = ur1.id
            and cbue.id = ud.building_unit_estate_id
            and cbue.building_unit_id = cbu.id
            and cbu.building_id = cb.id
            and a.id = cbue.id
            <if test="residentName != null and residentName != ''">
                and a.resident_name like concat('%',#{residentName},'%')
            </if>
            <if test="residentTel != null and residentTel != ''">
                and a.resident_tel like concat('%',#{residentTel},'%')
            </if>
            <if test="expectedBegin != null">
                and ud.expected_begin = #{expectedBegin}
            </if>
            <if test="expectedEnd != null">
                and ud.expected_end = #{expectedEnd}
            </if>
            <if test="emergencyContact != null and emergencyContact != ''">
                and ud.emergency_contact like concat('%',#{emergencyContact},'%')
            </if>
            <if test="tel != null and tel != ''">
                and ud.tel like concat('%',#{tel},'%')
            </if>
            <if test="actualBegin != null">
                and ud.actual_begin = #{actualBegin}
            </if>
            <if test="actualEnd != null">
                and ud.actual_end = #{actualEnd}
            </if>
            <if test="constructionUnit != null and constructionUnit != ''">
                and ud.construction_unit like concat('%',#{constructionUnit},'%')
            </if>
            <if test="constructionName != null and constructionName != ''">
                and udp.name like concat('%',#{constructionName},'%')
            </if>
            <if test="constructionTel != null and constructionTel != ''">
                and udp.tel like concat('%',#{constructionTel},'%')
            </if>
            <if test="isQualified != null">
                and ud.is_qualified = #{isQualified}
            </if>
            <if test="buildingNo != null">
                and cb.no like concat('%',#{buildingNo},'%')
            </if>
            <if test="unitNo != null">
                and cbu.no like concat('%',#{unitNo},'%')
            </if>
            <if test="roomNumber != null and roomNumber != ''">
                and cbue.room_number like concat('%',#{roomNumber},'%')
            </if>
            <if test="status != null">
                and ud.status = #{status}
            </if>
        </where>
        <if test="expectedBeginSort == 1">
            order by ud.expected_begin DESC
        </if>
        <if test="expectedBeginSort == 2">
            order by ud.expected_begin
        </if>
        <if test="expectedEndSort == 1">
            order by ud.expected_end DESC
        </if>
        <if test="expectedEndSort == 2">
            order by ud.expected_end
        </if>
        <if test="actualBeginSort == 1">
            order by ud.actual_begin DESC
        </if>
        <if test="actualBeginSort == 2">
            order by ud.actual_begin
        </if>
        <if test="actualEndSort == 1">
            order by ud.actual_end DESC
        </if>
        <if test="actualEndSort == 2">
            order by ud.actual_end
        </if>
    </select>

    <select id="decorationPersonnelList" parameterType="java.lang.Integer" resultType="com.api.vo.butlerService.VoUserDecorationPersonnel">
        select id,name,identity,card_type,card_code,tel,address from user_decoration_personnel
        where decoration_id = #{id}
    </select>

    <select id="decorationTrackRecordList" parameterType="java.lang.Integer" resultType="com.api.vo.butlerService.VoUserDecorationTrackRecord">
        select udtr.*,su.actual_name as track_name
        from user_decoration_track_record as udtr,sys_user as su
        where udtr.track_id = su.id and udtr.decoration_id = #{id} and udtr.type = 1
    </select>

    <select id="decorationFinishRecordList" parameterType="java.lang.Integer" resultType="com.api.vo.butlerService.VoUserDecorationTrackRecord">
        select udtr.*,su.actual_name as track_name
        from user_decoration_track_record as udtr,sys_user as su
        where udtr.track_id = su.id and udtr.decoration_id = #{id} and udtr.type = 2
    </select>

    <select id="decorationTrackRecordDetailList" parameterType="java.lang.Integer" resultType="com.api.vo.butlerService.VoUserDecorationTrackRecordDetail">
        select udtrd.id,udtcc.name as review,udtrd.is_qualified
        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>

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

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

    <select id="findTrackRecordByDecorationId" parameterType="java.lang.Integer" resultType="com.api.vo.butlerService.VoUserDecorationTrackRecord">
        select * from user_decoration_track_record where decoration_id = #{id}
    </select>

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

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

    <select id="findUDACByDecorationId" parameterType="java.lang.Integer" resultType="com.api.model.butlerService.UserDecorationAccessCard">
        select * from user_decoration_access_card where decoration_id = #{id}
    </select>

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

    <select id="findUDTRDByTrackRecordId" parameterType="java.lang.Integer" resultType="com.api.model.butlerService.UserDecorationTrackRecordDetail">
        select * from user_decoration_track_record_detail where decoration_track_record_id = #{id}
    </select>

    <update id="invalid" parameterType="java.lang.Integer">
        update user_decoration set status = 8 where id = #{id}
    </update>

    <select id="countDecorationNow" resultType="java.lang.Integer">
        select count(*) from user_decoration where to_days(expected_begin) = to_days(now())
    </select>

    <select id="countPerformed" resultType="java.lang.Integer">
        select count(*) from user_decoration where status <![CDATA[ <= ]]> 1
    </select>

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

    <select id="findAllChecksContent" resultType="com.api.vo.butlerService.VoUserDecorationChecksContent">
        select id,name,remake from user_decoration_checks_content
    </select>

    <insert id="insertCheckContent" parameterType="com.api.model.butlerService.UserDecorationChecksContent">
        insert into user_decoration_checks_content(
            name,
            remake,
            create_id,
            create_date
        )values (
            #{name},
            #{remake},
            #{createId},
            #{createDate}
        )
    </insert>

    <update id="updateCheckContent" parameterType="com.api.model.butlerService.UserDecorationChecksContent">
        update user_decoration_checks_content
        <set>
            <if test="name != null and name != ''">
                name = #{name},
            </if>
            modify_id = #{modifyId},
            modify_date = #{modifyDate}
        </set>
        where id = #{id}
    </update>

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

    <delete id="deleteAllDoc">
        delete from user_decoration_doc
    </delete>

    <insert id="insertDoc" parameterType="com.api.model.butlerService.UserDecorationDoc">
        insert into user_decoration_doc(
            name,
            url,
            create_id,
            create_date
        ) values (
            #{name},
            #{url},
            #{createId},
            #{createDate}
        )
    </insert>

    <select id="findDocUrl" resultType="java.lang.String">
        select url from user_decoration_doc
    </select>

    <select id="findDepositById" parameterType="java.lang.Integer" resultType="com.api.vo.butlerService.VoDepositPriceAndId">
        select id,deposit_price from sys_deposit_management where user_decoration_id = #{id}
    </select>

    <select id="findDepositAdditionalCost" parameterType="java.lang.Integer" resultType="com.api.vo.butlerService.VoDepositAdditionalCost">
        select id,name,cost from sys_deposit_management_additional_cost where sys_deposit_management_id = #{id}
    </select>

    <select id="findRefundDeposit" parameterType="java.lang.Integer" resultType="java.math.BigDecimal">
        select deposit_price from sys_deposit_management where user_decoration_id = #{id}
    </select>

    <select id="findNoReturnAccessCard" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select count(*) from user_decoration_access_card where decoration_id = #{id} and id_return = 0
    </select>

    <update id="update" parameterType="com.api.model.butlerService.UserDecoration">
        update user_decoration
        <set>
            <if test="emergencyContact != null and emergencyContact != ''">
                emergency_contact = #{emergencyContact},
            </if>
            <if test="tel != null and tel != ''">
                tel = #{tel},
            </if>
        </set>
        where id = #{id}
    </update>

    <insert id="insert" parameterType="com.api.model.butlerService.UserDecoration">
        insert into
        <trim prefix="user_decoration(" suffix=")" suffixOverrides=",">
            building_unit_estate_id,
            code,
            resident_id,
            resident_type,
            application_date,
            <if test="constructionUnit != null and constructionUnit != ''">
                construction_unit,
            </if>
            director,
            director_tel,
            status,
            <if test="emergencyContact != null and emergencyContact != ''">
                emergency_contact,
            </if>
            <if test="tel != null and tel != ''">
                tel,
            </if>
            <if test="expectedBegin != null">
                expected_begin,
            </if>
            <if test="expectedEnd != null">
                expected_end
            </if>
        </trim>
        values
        <trim prefix="(" suffix=")" suffixOverrides=",">
            #{buildingUnitEstateId},
            #{code},
            #{residentId},
            #{residentType},
            #{applicationDate},
            <if test="constructionUnit != null and constructionUnit != ''">
                #{constructionUnit},
            </if>
            #{director},
            #{directorTel},
            #{status},
            <if test="emergencyContact != null and emergencyContact != ''">
                #{emergencyContact},
            </if>
            <if test="tel != null and tel != ''">
                #{tel},
            </if>
            <if test="expectedBegin != null">
                #{expectedBegin},
            </if>
            <if test="expectedEnd != null">
                #{expectedEnd}
            </if>
        </trim>
        <selectKey keyProperty="id" resultType="java.lang.Integer">
            select LAST_INSERT_ID() AS id
        </selectKey>
    </insert>

    <update id="updateStatus" parameterType="com.api.model.butlerService.UserDecoration">
        update user_decoration
        set status = #{status},application_refund_date = #{applicationRefundDate}
        where id = #{id}
    </update>

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