<?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.xinelu.manage.mapper.appointmentorder.AppointmentOrderMapper">

    <resultMap type="AppointmentOrder" id="AppointmentOrderResult">
        <result property="id" column="id"/>
        <result property="patientId" column="patient_id"/>
        <result property="nurseStationPersonId" column="nurse_station_person_id"/>
        <result property="orderNo" column="order_no"/>
        <result property="totalPrice" column="total_price"/>
        <result property="orderStatus" column="order_status"/>
        <result property="orderChannel" column="order_channel"/>
        <result property="remark" column="remark"/>
        <result property="delFlag" column="del_flag"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
        <result property="cancelAppointmentReason" column="cancel_appointment_reason"/>
        <result property="confirmRefundStatus" column="confirm_refund_status"/>
        <result property="orderCommissionAmount" column="order_commission_amount"/>
        <result property="orderCheckStatus" column="order_check_status"/>
        <result property="orderType" column="order_type"/>
        <result property="companionStartDate" column="companion_start_date"/>
        <result property="companionEndDate" column="companion_end_date"/>
        <result property="companionDays" column="companion_days"/>
        <result property="orderWriteName" column="order_write_name"/>
    </resultMap>

    <resultMap type="com.xinelu.manage.vo.appointmentorder.PersonAndDepartmentVO" id="PersonAndDepartmentVOResult">
        <result property="nurseStationPersonId" column="nurseStationPersonId"/>
        <result property="nursePersonName" column="nurse_person_name"/>
        <result property="nursePersonType" column="nurse_person_type"/>
        <result property="sex" column="sex"/>
        <result property="phone" column="phone"/>
        <result property="geniusItem" column="genius_item"/>
        <result property="workStatus" column="work_status"/>
        <result property="positionalTitleLevel" column="positional_title_level"/>
        <collection property="nurseStationPersonCertificateList" javaType="java.util.List"
                    resultMap="nurseStationPersonCertificateResult"/>
    </resultMap>
    <resultMap type="NurseStationPersonCertificate" id="nurseStationPersonCertificateResult">
        <result property="id" column="id"/>
        <result property="nurseStationPersonId" column="nurse_station_person_id"/>
        <result property="certificateName" column="certificate_name"/>
        <result property="certificateCode" column="certificate_code"/>
        <result property="certificateUrl" column="certificate_url"/>
        <result property="sort" column="sort"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
    </resultMap>

    <sql id="selectAppointmentOrderVo">
        select id,
               patient_id,
               nurse_station_person_id,
               order_no,
               total_price,
               order_status,
               order_channel,
               remark,
               del_flag,
               create_by,
               create_time,
               update_by,
               update_time,
               cancel_appointment_reason,
               confirm_refund_status,
               task_return_reason,
               order_commission_amount,
               order_check_status,
               order_type,
               companion_start_date,
               companion_end_date,
               companion_days,
               order_write_name
        from appointment_order
    </sql>

    <select id="getAppointmentOrderList" parameterType="AppointmentOrder" resultMap="AppointmentOrderResult">
        <include refid="selectAppointmentOrderVo"/>
        <where>
            <if test="patientId != null ">
                and patient_id = #{patientId}
            </if>
            <if test="orderNo != null  and orderNo != ''">
                and order_no = #{orderNo}
            </if>
            <if test="orderStatus != null  and orderStatus != ''">
                and order_status = #{orderStatus}
            </if>
            <if test="orderType != null  and orderType != ''">
                and order_type = #{orderType}
            </if>
            <if test="companionStartDate != null ">
                and companion_start_date = #{companionStartDate}
            </if>
            <if test="companionEndDate != null ">
                and companion_end_date = #{companionEndDate}
            </if>
            <if test="companionDays != null ">
                and companion_days = #{companionDays}
            </if>
            <if test="orderWriteName != null  and orderWriteName != ''">
                and order_write_name like concat('%', #{orderWriteName}, '%')
            </if>
        </where>
    </select>

    <select id="selectAppointmentOrderList"
            resultType="com.xinelu.manage.vo.appointmentorder.AppointmentOrderVO">
        SELECT
        ao.id appointmentOrderId,
        ao.order_no,
        ao.order_status,
        ao.total_price,
        ao.create_time,
        ao.order_commission_amount,
        ao.order_check_status,
        pi.patient_name,
        nsp.nurse_person_name
        FROM
        appointment_order ao
        LEFT JOIN patient_info pi ON pi.id = ao.patient_id
        LEFT JOIN nurse_station_person nsp ON nsp.id = ao.nurse_station_person_id
        <where>
            <if test="orderNo != null  and orderNo != ''">
                and ao.order_no = #{orderNo}
            </if>
            <if test="orderStatus != null  and orderStatus != ''">
                and ao.order_status = #{orderStatus}
            </if>
            <if test="patientName != null  and patientName != ''">
                and pi.patient_name like concat('%', #{patientName}, '%')
            </if>
            and ao.del_flag=0
        </where>
        ORDER BY ao.create_time DESC
    </select>

    <select id="selectNameByOrderNoList"
            resultType="com.xinelu.manage.vo.appointmentorder.AppointmentOrderVO">
        SELECT
        ao.id appointmentOrderId,
        ao.order_no,
        ao.order_status,
        ao.total_price,
        ao.create_time,
        ao.order_commission_amount,
        ao.order_check_status,
        ao.order_type,
        pi.patient_name,
        pi.home_longitude,
        pi.home_latitude,
        nsp.nurse_person_name,
        aod.id detailsId,
        aod.order_no,
        aod.nurse_item_name,
        ns.nurse_station_name
        FROM appointment_order_details aod
        LEFT JOIN nurse_station_item nsi ON nsi.id = aod.nurse_station_item_id
        LEFT JOIN nurse_station ns ON ns.id = nsi.nurse_station_id
        LEFT JOIN appointment_order ao ON ao.order_no = aod.order_no
        LEFT JOIN patient_info pi ON pi.id = ao.patient_id
        LEFT JOIN nurse_station_person nsp ON nsp.id = ao.nurse_station_person_id
        <where>
                    ao.del_flag = 0
                and aod.del_flag = 0
            <if test="orderNo != null  and orderNo != ''">
                and ao.order_no = #{orderNo}
            </if>
            <if test="orderStatus != null  and orderStatus != ''">
                and ao.order_status = #{orderStatus}
            </if>
            <if test="patientName != null  and patientName != ''">
                and pi.patient_name like concat('%', #{patientName}, '%')
            </if>
            <if test="nurseStationName != null  and nurseStationName != ''">
                and ns.nurse_station_name like concat('%', #{nurseStationName}, '%')
            </if>
            <if test="nurseStationId != null ">
                and ns.id = #{nurseStationId}
            </if>
            <if test="orderType != null  and orderType != ''">
                and ao.order_type = #{orderType}
            </if>
            <if test="patientName != null  and patientName != ''">
                or aod.caregiver_name like concat('%', #{patientName}, '%')
            </if>
        </where>
        ORDER BY aod.create_time DESC
    </select>

    <select id="selectAppointmentOrderById" parameterType="Long"
            resultMap="AppointmentOrderResult">
        <include refid="selectAppointmentOrderVo"/>
        <where>
            del_flag = 0
            <if test="id != null">
                and id = #{id}
            </if>
            <if test="orderNo != null  and orderNo != ''">
                and order_no = #{orderNo}
            </if>
        </where>
    </select>

    <insert id="insertAppointmentOrder" parameterType="AppointmentOrder" useGeneratedKeys="true"
            keyProperty="id">
        insert into appointment_order
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="patientId != null">patient_id,
            </if>
            <if test="nurseStationPersonId != null">nurse_station_person_id,
            </if>
            <if test="orderNo != null">order_no,
            </if>
            <if test="totalPrice != null">total_price,
            </if>
            <if test="orderStatus != null">order_status,
            </if>
            <if test="orderChannel != null">order_channel,
            </if>
            <if test="remark != null">remark,
            </if>
            <if test="delFlag != null">del_flag,
            </if>
            <if test="createBy != null">create_by,
            </if>
            <if test="createTime != null">create_time,
            </if>
            <if test="updateBy != null">update_by,
            </if>
            <if test="updateTime != null">update_time,
            </if>
            <if test="cancelAppointmentReason != null">cancel_appointment_reason,
            </if>
            <if test="confirmRefundStatus != null">confirm_refund_status,
            </if>
            <if test="orderCommissionAmount != null">order_commission_amount,
            </if>
            <if test="orderCheckStatus != null">order_check_status,
            </if>
            <if test="orderType != null">order_type,
            </if>
            <if test="companionStartDate != null">companion_start_date,
            </if>
            <if test="companionEndDate != null">companion_end_date,
            </if>
            <if test="companionDays != null">companion_days,
            </if>
            <if test="orderWriteName != null">order_write_name,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="patientId != null">#{patientId},
            </if>
            <if test="nurseStationPersonId != null">#{nurseStationPersonId},
            </if>
            <if test="orderNo != null">#{orderNo},
            </if>
            <if test="totalPrice != null">#{totalPrice},
            </if>
            <if test="orderStatus != null">#{orderStatus},
            </if>
            <if test="orderChannel != null">#{orderChannel},
            </if>
            <if test="remark != null">#{remark},
            </if>
            <if test="delFlag != null">#{delFlag},
            </if>
            <if test="createBy != null">#{createBy},
            </if>
            <if test="createTime != null">#{createTime},
            </if>
            <if test="updateBy != null">#{updateBy},
            </if>
            <if test="updateTime != null">#{updateTime},
            </if>
            <if test="cancelAppointmentReason != null">#{cancelAppointmentReason},
            </if>
            <if test="confirmRefundStatus != null">#{confirmRefundStatus},
            </if>
            <if test="orderCommissionAmount != null">#{orderCommissionAmount},
            </if>
            <if test="orderCheckStatus != null">#{orderCheckStatus},
            </if>
            <if test="orderType != null">#{orderType},
            </if>
            <if test="companionStartDate != null">#{companionStartDate},
            </if>
            <if test="companionEndDate != null">#{companionEndDate},
            </if>
            <if test="companionDays != null">#{companionDays},
            </if>
            <if test="orderWriteName != null">#{orderWriteName},
            </if>
        </trim>
    </insert>

    <update id="updateAppointmentOrder" parameterType="AppointmentOrder">
        update appointment_order
        <trim prefix="SET" suffixOverrides=",">
            <if test="nurseStationPersonId != null">nurse_station_person_id =
                #{nurseStationPersonId},
            </if>
            <if test="patientId != null">patient_id =
                #{patientId},
            </if>
            <if test="orderNo != null">order_no =
                #{orderNo},
            </if>
            <if test="totalPrice != null">total_price =
                #{totalPrice},
            </if>
            <if test="orderStatus != null">order_status =
                #{orderStatus},
            </if>
            <if test="orderChannel != null">order_channel =
                #{orderChannel},
            </if>
            <if test="remark != null">remark =
                #{remark},
            </if>
            <if test="delFlag != null">del_flag =
                #{delFlag},
            </if>
            <if test="createBy != null">create_by =
                #{createBy},
            </if>
            <if test="createTime != null">create_time =
                #{createTime},
            </if>
            <if test="updateBy != null">update_by =
                #{updateBy},
            </if>
            <if test="updateTime != null">update_time =
                #{updateTime},
            </if>
            <if test="cancelAppointmentReason != null">cancel_appointment_reason =
                #{cancelAppointmentReason},
            </if>
            <if test="confirmRefundStatus != null">confirm_refund_status =
                #{confirmRefundStatus},
            </if>
            <if test="orderCommissionAmount != null">order_commission_amount =
                #{orderCommissionAmount},
            </if>
            <if test="orderCheckStatus != null">order_check_status =
                #{orderCheckStatus},
            </if>
            <if test="orderType != null">order_type =
                #{orderType},
            </if>
            <if test="companionStartDate != null">companion_start_date =
                #{companionStartDate},
            </if>
            <if test="companionEndDate != null">companion_end_date =
                #{companionEndDate},
            </if>
            <if test="companionDays != null">companion_days =
                #{companionDays},
            </if>
            <if test="orderWriteName != null">order_write_name =
                #{orderWriteName},
            </if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteAppointmentOrderById" parameterType="Long">
        delete
        from appointment_order
        where id = #{id}
    </delete>

    <update id="updateAppointmentOrderByIds" parameterType="String">
        update appointment_order set del_flag = 1 ,update_time = now() where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </update>

    <select id="getAppointmentOrderByIds" parameterType="String" resultMap="AppointmentOrderResult">
        SELECT
        id,
        patient_id,
        nurse_station_person_id,
        order_no,
        order_status,
        remark,
        del_flag,
        create_by,
        create_time,
        update_by,
        update_time,
        cancel_appointment_reason,
        confirm_refund_status,
        order_commission_amount,
        order_check_status
        FROM
        appointment_order
        where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>

    <select id="selectNurseStationPersonNameList"
            resultType="com.xinelu.manage.vo.appointmentorder.PersonAndDepartmentVO">
        SELECT
        nsp.id nurseStationPersonId,
        nsp.nurse_person_name,
        nsp.nurse_person_type,
        nsp.positional_title_level,
        nsp.sex,
        nsp.phone,
        nsp.work_status,
        nsp.evaluate_star_count,
        ns.nurse_station_name
        from
        appointment_order_details aod
        LEFT JOIN nurse_station_item nsi ON nsi.id = aod.nurse_station_item_id
        LEFT JOIN nurse_station_person nsp ON nsp.nurse_station_id = nsi.nurse_station_id
        LEFT JOIN nurse_station ns ON ns.id = nsp.nurse_station_id
        <where>
            <if test="orderNo != null and orderNo != ''">
                aod.order_no = #{orderNo}
            </if>
            <if test="nursePersonName != null and nursePersonName != ''">
                AND nsp.nurse_person_name like concat('%', #{nursePersonName}, '%')
            </if>
            <if test="nursePersonType != null and nursePersonType != ''">
                AND nsp.nurse_person_type like concat('%', #{nursePersonType}, '%')
            </if>
            <if test="departmentName != null and departmentName != ''">
                AND nsd.department_name like concat('%', #{departmentName}, '%')
            </if>
        </where>
    </select>

    <update id="updateAppointmentOrderDispatchStatus" parameterType="AppointmentOrder">
        UPDATE appointment_order a,
            appointment_order_details b
        SET a.nurse_station_person_id = #{nurseStationPersonId},
            a.order_status = #{orderStatus},
            b.order_status = #{orderStatus},
            a.update_time = now(),
            a.update_by = #{updateBy},
            b.update_time = now(),
            b.update_by = #{updateBy}
        WHERE
            a.order_no = b.order_no
          AND a.del_flag = 0
          and b.del_flag = 0
          AND a.order_status &lt;&gt; #{orderStatus}
          AND b.order_status &lt;&gt; #{orderStatus}
          AND a.order_no = #{orderNo}
    </update>

    <select id="getAppointmentOrderByOrderNo" parameterType="appointmentOrder" resultType="appointmentOrder">
        <include refid="selectAppointmentOrderVo"/>
        <where>
            del_flag = 0
            <if test="orderNo != null  and orderNo != ''">
                and order_no = #{orderNo}
            </if>
        </where>
    </select>

    <update id="updateAppointmentOrderStatus" parameterType="string">
        UPDATE appointment_order a,
            appointment_order_details b
        SET a.order_status = #{orderStatus},
            b.order_status = #{orderStatus},
            a.update_time = now(), b.update_time = now()
        WHERE
            a.order_no = b.order_no
          AND a.del_flag = 0
          AND a.order_no = #{orderNo}
          AND b.order_no = #{orderNo}
          AND a.order_status &lt;&gt; #{orderStatus}
          AND b.order_status &lt;&gt; #{orderStatus}
    </update>

    <select id="getWaitPayAppointmentOrderList" resultType="com.xinelu.manage.vo.AppointOrderAndDetailsInfo">
        SELECT DISTINCT ao.order_no,
                        ao.order_type,
                        aod.nurse_station_item_id,
                        aod.service_date,
                        aod.service_start_time,
                        (SELECT appointment_limit_count
                         FROM nurse_station_item
                         WHERE id = aod.nurse_station_item_id) appointment_limit_count
        FROM appointment_order ao
                 INNER JOIN appointment_order_details aod ON ao.order_no = aod.order_no
        WHERE ao.create_time &lt;= #{time}
          AND ao.order_status = 'WAIT_PAY'
          AND ao.del_flag = 0
    </select>

    <update id="updateBatchAppointmentOrderStatus">
        UPDATE appointment_order a,
        appointment_order_details b
        SET a.order_status = #{orderStatus},
        b.order_status = #{orderStatus},
        a.update_time = now(),
        b.update_time = now()
        WHERE
        a.order_no = b.order_no
        AND a.del_flag = 0
        AND a.order_status &lt;&gt; #{orderStatus}
        AND b.order_status &lt;&gt; #{orderStatus}
        AND a.order_no IN
        <foreach collection="orderNoList" item="orderNo" open="(" separator="," close=")">
            #{orderNo}
        </foreach>
    </update>

    <update id="updateBatchAppointmentOrderStatusAndPerson">
        UPDATE appointment_order a,
        appointment_order_details b
        SET a.order_status = #{orderStatus},
        b.order_status = #{orderStatus},
        a.nurse_station_person_id = null,
        a.update_time = now(),
        b.update_time = now()
        WHERE
        a.order_no = b.order_no
        AND a.del_flag = 0
        AND a.order_status &lt;&gt; #{orderStatus}
        AND b.order_status &lt;&gt; #{orderStatus}
        AND a.order_no IN
        <foreach collection="orderNoList" item="orderNo" open="(" separator="," close=")">
            #{orderNo}
        </foreach>
    </update>

    <select id="getWaitPayStatusAppointmentOrderList" resultType="appointmentOrder">
        SELECT DISTINCT
        patient_id,
        order_no,
        total_price,
        order_status,
        order_channel
        FROM
        appointment_order
        <where>
            del_flag = 0
            AND order_status = #{orderStatus}
        </where>
    </select>

    <select id="getRefundAppointmentOrderInfo" parameterType="string"
            resultType="com.xinelu.manage.vo.RefundOrderInfoVO">
        SELECT ao.order_no,
               ao.order_type appointmentOrderType,
               rf.out_refund_no,
               aod.nurse_station_item_id,
               aod.service_date,
               aod.service_start_time,
               (SELECT appointment_limit_count
                FROM nurse_station_item
                WHERE id = aod.nurse_station_item_id) appointment_limit_count
        FROM appointment_order ao
                 INNER JOIN appointment_order_details aod ON ao.order_no = aod.order_no
                 INNER JOIN refund_info rf ON ao.order_no = rf.order_no
        WHERE ao.order_status = #{orderStatus}
          AND ao.confirm_refund_status = #{confirmRefundStatus}
          AND rf.wechat_refund_status = #{refundStatus}
          AND ao.del_flag = 0
    </select>

    <update id="updateAppointmentOrderByOrderId">
        UPDATE appointment_order a,
            appointment_order_details b
        SET a.order_status = #{orderStatus},
            b.order_status = #{orderStatus},
            a.update_time = #{updateTime},
            a.update_by = #{updateBy},
            b.update_time = now(),
            b.update_by = #{updateBy}
        WHERE
            a.order_no = b.order_no
          AND a.del_flag = 0
          and b.del_flag = 0
          AND a.order_status &lt;&gt; #{orderStatus}
          AND b.order_status &lt;&gt; #{orderStatus}
          AND a.order_no = #{orderNo}
    </update>

    <select id="getAppointOrderAndDetailsInfo" parameterType="string"
            resultType="com.xinelu.manage.vo.AppointOrderAndDetailsInfo">
        SELECT ao.id,
               ao.patient_id,
               ao.order_no,
               ao.total_price,
               ao.order_status,
               ao.order_channel,
               ao.cancel_appointment_reason,
               ao.confirm_refund_status,
               ao.order_type,
               aod.nurse_station_item_id,
               aod.service_date,
               aod.service_start_time,
               (SELECT appointment_limit_count
                FROM nurse_station_item
                WHERE id = aod.nurse_station_item_id) appointment_limit_count
        FROM appointment_order ao
                 INNER JOIN appointment_order_details aod ON ao.order_no = aod.order_no
        WHERE ao.del_flag = 0
          AND ao.order_no = #{orderNo}
    </select>

    <update id="updateAppointConfirmRefundStatus" parameterType="string">
        update appointment_order
        set confirm_refund_status = #{confirmRefundStatus},
            update_time           = now()
        where del_flag = 0
          and order_no = #{orderNo}
          and confirm_refund_status &lt;&gt; #{confirmRefundStatus}
    </update>

    <select id="getWaitPayAppointList" parameterType="list"
            resultType="com.xinelu.manage.vo.AppointOrderAndDetailsInfo">
        SELECT DISTINCT
        ao.order_no,
        ao.order_type,
        aod.nurse_station_item_id,
        aod.service_date,
        aod.service_start_time,
        (SELECT appointment_limit_count FROM nurse_station_item WHERE id = aod.nurse_station_item_id)
        appointment_limit_count
        FROM
        appointment_order ao
        INNER JOIN appointment_order_details aod ON ao.order_no = aod.order_no
        where ao.del_flag = 0
        and ao.order_status = 'WAIT_PAY'
        and ao.order_no in
        <foreach collection="orderNoList" item="orderNo" open="(" separator="," close=")">
            #{orderNo}
        </foreach>
    </select>

    <select id="selectAppointmentOrderByTime"
            resultType="com.xinelu.manage.vo.orderDetails.AppointmentOrderAndDetails">
        select ao.patient_id,
               ao.order_no,
               ao.total_price,
               aod.nurse_item_name,
               ns.nurse_station_name
        from appointment_order ao
                 LEFT JOIN appointment_order_details aod ON aod.order_no = ao.order_no
                 LEFT JOIN nurse_station_item nsi ON aod.nurse_station_item_id = nsi.id
                 LEFT JOIN nurse_station ns ON ns.id = nsi.nurse_station_id
        where ao.create_time &lt;= #{time}
          and ao.create_time &gt; #{beforeTime}
          and aod.order_status = #{orderStatus}
          and ao.order_status = #{orderStatus}
          and ao.del_flag = 0
    </select>

    <select id="selectOrderDetailsAndPatientBy"
            resultType="com.xinelu.manage.vo.stationmessagepush.WaitReceiveMessagePushVO">
        select aod.service_address,
               aod.service_date,
               aod.service_start_time,
               aod.nurse_item_name,
               pi.patient_name,
               pi.phone
        from appointment_order ao
                 left join patient_info pi ON pi.id = ao.patient_id
                 left join appointment_order_details aod ON aod.order_no = ao.order_no
        where ao.order_no = #{orderNo}
    </select>

    <select id="getAppointmentOrderInfoByOrderNo" parameterType="appointmentOrder"
            resultType="com.xinelu.manage.vo.appointmentorder.AppointmentOrderInfoVO">
        SELECT
        ao.id,
        ao.patient_id,
        ao.order_no,
        ao.total_price,
        ao.order_status,
        ao.order_channel,
        ao.remark,
        ao.cancel_appointment_reason,
        ao.confirm_refund_status,
        ao.order_commission_amount,
        ao.order_check_status,
        ao.order_type,
        aod.nurse_station_item_id,
        aod.nurse_item_name,
        aod.service_address,
        aod.item_serve_duration_unit,
        aod.service_date,
        aod.service_start_time,
        ( SELECT nurse_station_id FROM nurse_station_item WHERE id = aod.nurse_station_item_id ) nurse_station_id
        FROM
        appointment_order ao
        LEFT JOIN appointment_order_details aod ON ao.order_no = aod.order_no
        <where>
            <if test="orderNo != null  and orderNo != ''">
                AND ao.order_no = #{orderNo}
            </if>
            AND ao.del_flag = 0
            AND aod.del_flag = 0
        </where>
    </select>

    <select id="selectWaitReceiveOrderByTime"
            resultType="com.xinelu.manage.vo.stationmessagepush.WaitReceiveMessagePushVO">
        select (select nurse_station_id from nurse_station_item where id = aod.nurse_station_item_id) nurseStationId,
               ao.nurse_station_person_id                                                             nurseStationPersonId,
               ao.order_channel,
               ao.order_no,
               aod.nurse_station_item_id,
               aod.service_address,
               aod.nurse_item_name,
               aod.service_date,
               aod.service_start_time,
               pi.patient_name,
               pi.phone
        from appointment_order ao
                 LEFT JOIN appointment_order_details aod ON aod.order_no = ao.order_no
                 LEFT JOIN patient_info pi ON pi.id = ao.patient_id
        where ao.update_time &gt; #{beforeTime}
          and ao.update_time &lt;= #{time}
          and aod.order_status = #{orderStatus}
          and ao.order_status = #{orderStatus}
          and ao.del_flag = 0
    </select>

    <select id="selectWaitReceiveOrderByUpdateTime"
            resultType="com.xinelu.manage.vo.stationmessagepush.WaitReceiveMessagePushVO">
        select (select nurse_station_id from nurse_station_item where id = aod.nurse_station_item_id) nurseStationId,
               ao.nurse_station_person_id                                                             nurseStationPersonId,
               ao.id                                                                                  appointmentOrderId,
               ao.order_channel,
               ao.order_no,
               aod.nurse_station_item_id,
               aod.service_address,
               aod.nurse_item_name,
               aod.service_date,
               aod.service_start_time,
               pi.patient_name,
               pi.phone
        from appointment_order ao
                 LEFT JOIN appointment_order_details aod ON aod.order_no = ao.order_no
                 LEFT JOIN patient_info pi ON pi.id = ao.patient_id
        where #{time} &gt; DATE_ADD(ao.update_time, INTERVAL 15 MINUTE)
          and aod.order_status = #{orderStatus}
          and ao.order_status = #{orderStatus}
          and ao.del_flag = 0
    </select>

    <select id="selectNurseStationPersonCertificateList"
            resultType="com.xinelu.manage.domain.nursestationpersoncertificate.NurseStationPersonCertificate">
        select certificate_name,
               certificate_url
        from nurse_station_person_certificate
        where nurse_station_person_id = #{nurseStationPersonId}
    </select>
</mapper>
