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

    <select id="list" parameterType="com.api.model.butlerService.SearchFacilitiesAppointment" resultType="com.api.vo.butlerService.VoFacilitiesAppointment">
        select * from (
            select sfa.id,sfa.code,sfc.name as facilities_category_name,sfm.name as facilities_name,
            ur.name as appointment_name,ur.tel as appointment_tel,
            sfa.appointment_start_date,sfa.appointment_end_date,sfa.status,sfa.create_date
            from sys_facilities_appointment as sfa,sys_facilities_manage as sfm,sys_facilities_category as sfc,user_resident as ur
            <where>
                sfa.facilities_manage_id = sfm.id
                and sfm.facilities_category_id = sfc.id
                and sfa.appointment_id = ur.id
                <if test="code != null and code != ''">
                    and sfa.code like concat('%',#{code},'%')
                </if>
                <if test="facilitiesCategoryId != null">
                    and sfc.id = #{facilitiesCategoryId}
                </if>
                <if test="appointmentName != null and appointmentName != ''">
                    and ur.name like concat('%',#{appointmentName},'%')
                </if>
                <if test="status != null">
                    and sfa.status = #{status}
                </if>
                <if test="createStartDate != null">
                    and sfa.create_date <![CDATA[ >= ]]> #{createStartDate}
                </if>
                <if test="createEndDate != null">
                    and sfa.create_date <![CDATA[ <= ]]> #{createEndDate}
                </if>
            </where>
        ) as T
        <where>
            <if test="appointmentStartDate != null and appointmentEndDate == null">
                T.appointment_end_date <![CDATA[ >= ]]> #{appointmentStartDate}
            </if>
            <if test="appointmentStartDate == null and appointmentEndDate != null">
                T.appointment_start_date <![CDATA[ <= ]]> #{appointmentEndDate}
            </if>
            <if test="appointmentStartDate != null and appointmentEndDate != null">
                T.appointment_start_date BETWEEN #{appointmentStartDate} and #{appointmentEndDate}
                or T.appointment_end_date BETWEEN #{appointmentStartDate} and #{appointmentEndDate}
            </if>
        </where>
    </select>

    <insert id="insert" parameterType="com.api.model.butlerService.FacilitiesAppointment">
        insert into sys_facilities_appointment(
            code,
            estate_id,
            facilities_manage_id,
            status,
            appointment_id,
            appointment_start_date,
            appointment_end_date,
            create_id,
            create_date
        )values (
            #{code},
            #{estateId},
            #{facilitiesManageId},
            #{status},
            #{appointmentId},
            #{appointmentStartDate},
            #{appointmentEndDate},
            #{createId},
            #{createDate}
        )
    </insert>

    <update id="nullify" parameterType="com.api.model.butlerService.FacilitiesAppointment">
        update sys_facilities_appointment
        set status = 3,
        nullify_reason = #{nullifyReason}
        where id = #{id}
    </update>
    <update id="update" parameterType="com.api.model.butlerService.FacilitiesAppointment">
        update sys_facilities_appointment
        <set>
            <if test="facilitiesManageId != null">
                facilities_manage_id = #{facilitiesManageId},
            </if>
            <if test="appointmentStartDate != null">
                appointment_start_date = #{appointmentStartDate},
            </if>
            <if test="appointmentEndDate != null">
                appointment_end_date = #{appointmentEndDate},
            </if>
        </set>
        where id = #{id}
    </update>

    <select id="countAppointmentNow" parameterType="java.util.Date" resultType="java.lang.Integer">
        select count(*) from sys_facilities_appointment where DATE(create_date) = DATE(#{date})
    </select>
    <select id="findIsBeInAppointmentDate" parameterType="com.api.model.butlerService.FacilitiesAppointment" resultType="java.lang.Integer">
        select count(*) from (
            select * from sys_facilities_appointment
            where facilities_manage_id = #{facilitiesManageId}
        ) as T
        where #{appointmentStartDate} between T.appointment_start_date and T.appointment_end_date
        and #{appointmentEndDate} between T.appointment_start_date and T.appointment_end_date and (T.status=1 or T.status=2)
    </select>
    <select id="judgeTime" resultType="java.lang.Integer"
            parameterType="com.api.model.butlerService.FacilitiesAppointment">
         select count(*) from
        (select sys_facilities_manage.id ,sys_facilities_category.open_start_date,sys_facilities_category.open_end_date from sys_facilities_category left join sys_facilities_manage on sys_facilities_manage.facilities_category_id=sys_facilities_category.id)
        as T
        where T.id=#{facilitiesManageId}
        and SUBSTR(#{appointmentStartDate},12) between T.open_start_date and T.open_end_date and SUBSTR(#{appointmentEndDate},12) between T.open_start_date and T.open_end_date
    </select>


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


</mapper>