<?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.yd.csf.service.dao.AppointmentMapper">

    <select id="page" resultType="com.yd.csf.feign.response.appointment.ApiAppointmentPageResponse">
        select
        a.appointment_biz_id,
        a.appointment_no,
        concat(pp.product_name,'（',pp.payment_term,'年）') as productName,
        concat(pp.company_name, '\n', pp.region) as companyName,
        concat(p.name, '\n', p.name_en) as policyholder,
        concat(i.name, '\n', i.name_en) as insurant,
        concat(pp.payment_term, '年') as paymentTerm,
        a.status,
        pp.each_issue_premium,
        pp.currency,
        IF(a.status = 0 or a.status = 1,
        concat('意向预约时间：','\n',DATE_FORMAT(a.intention_appointment_time, '%Y-%m-%d %H:%i')),
        concat('确认预约时间：','\n',DATE_FORMAT(a.confirm_appointment_time, '%Y-%m-%d %H:%i'),
        '\n','业务代表：','\n',a.business_represent_name1,'\n',
        '(',a.business_represent_mobile1_code,'-',a.business_represent_mobile1,')','\n',
        '预约地址：',a.signing_address)
        ) as time,
        a.business_represent_account1,
        a.creator_id,
        a.create_time,
        a.creator_name,
        a.intention_appointment_time,
        a.confirm_appointment_time
        from appointment a
        left join product_plan pp on a.appointment_biz_id = pp.appointment_biz_id and pp.is_deleted = 0
        left join policyholder p on a.appointment_biz_id = p.appointment_biz_id and p.is_deleted = 0
        left join insurant i on a.appointment_biz_id = i.appointment_biz_id and i.is_deleted = 0

        <where>
            <if test="request.status != null">
                and a.status = #{request.status}
            </if>

            <if test="request.appointmentNo != null and request.appointmentNo != ''">
                and a.appointment_no like concat('%', #{request.appointmentNo}, '%')
            </if>

            <if test="request.customerNo != null and request.customerNo != ''">
                and a.customer_no like concat('%', #{request.customerNo}, '%')
            </if>

            <if test="request.productBizId != null and request.productBizId != ''">
                and pp.product_biz_id = #{request.productBizId}
            </if>

            <if test="request.startTime != null">
                and a.confirm_appointment_time &gt;= #{request.startTime}
            </if>

            <if test="request.endTime != null">
                and a.confirm_appointment_time &lt;= #{request.endTime}
            </if>

            and a.is_deleted = 0
        </where>
        order by a.create_time desc
    </select>

    <select id="getItineraryDto" resultType="com.yd.csf.feign.dto.appointmentfile.ItineraryDto">
        select
        a.arrival_time,a.meeting_point,a.confirm_appointment_time,
        a.signing_address,a.business_represent_name1,
        a.business_represent_mobile1_code,a.business_represent_mobile1,
        t.name,i.name as insurantName,pp.payment_term,pp.each_issue_premium,
        pp.currency,pp.product_name,(pp.payment_term*pp.each_issue_premium) as total
        from appointment a
        left join product_plan pp on a.appointment_biz_id = pp.appointment_biz_id and pp.is_deleted = 0
        left join insurant i on a.appointment_biz_id = i.appointment_biz_id and i.is_deleted = 0
        left join customer t on t.customer_biz_id = a.customer_biz_id and t.is_deleted = 0

        <where>
            and a.appointment_biz_id = #{appointmentBizId}
            and a.is_deleted = 0
        </where>
        limit 1
    </select>

    <!-- 更新预约状态 -->
    <update id="updateDelStatus">
        update appointment set is_deleted = 1 where appointment_biz_id = #{appointmentBizId};
        update appointment_file set is_deleted = 1 where appointment_biz_id = #{appointmentBizId};
        update beneficiary set is_deleted = 1 where appointment_biz_id = #{appointmentBizId};
        update insurant set is_deleted = 1 where appointment_biz_id = #{appointmentBizId};
        update policyholder set is_deleted = 1 where appointment_biz_id = #{appointmentBizId};
        update product_plan set is_deleted = 1 where appointment_biz_id = #{appointmentBizId};
        update second_holder set is_deleted = 1 where appointment_biz_id = #{appointmentBizId};
        update fna set appointment_no = '',appointment_biz_id = '' where appointment_biz_id = #{appointmentBizId};
    </update>

</mapper>
