<?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.signpatientmanageroute.SignPatientManageRouteMapper">

    <resultMap type="com.xinelu.manage.domain.signpatientmanageroute.SignPatientManageRoute" id="SignPatientManageRouteResult">
        <result property="id" column="id"/>
        <result property="signPatientRecordId" column="sign_patient_record_id"/>
        <result property="patientId" column="patient_id"/>
        <result property="patientName" column="patient_name"/>
        <result property="departmentId" column="department_id"/>
        <result property="departmentName" column="department_name"/>
        <result property="servicePackageId" column="service_package_id"/>
        <result property="packageName" column="package_name"/>
        <result property="diseaseTypeId" column="disease_type_id"/>
        <result property="diseaseTypeName" column="disease_type_name"/>
	    <result property="routeId" column="route_id"/>
	    <result property="parentRouteId" column="parent_route_id"/>
        <result property="routeName" column="route_name"/>
        <result property="taskCreateType" column="task_create_type"/>
        <result property="version" column="version"/>
        <result property="routeClassify" column="route_classify"/>
        <result property="suitRange" column="suit_range"/>
        <result property="routeSort" column="route_sort"/>
        <result property="routeRemark" column="route_remark"/>
        <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>

    <resultMap type="com.xinelu.manage.vo.signpatientmanageroute.SignPatientManageRouteVO"
               id="SignPatientManageRouteVOResult">
        <result property="signPatientManageRouteId" column="signPatientManageRouteId"/>
        <result property="signPatientRecordId" column="sign_patient_record_id"/>
        <result property="patientId" column="patient_id"/>
        <result property="patientName" column="patient_name"/>
        <result property="departmentId" column="department_id"/>
        <result property="departmentName" column="department_name"/>
        <result property="servicePackageId" column="service_package_id"/>
        <result property="packageName" column="package_name"/>
        <result property="diseaseTypeId" column="disease_type_id"/>
        <result property="diseaseTypeName" column="disease_type_name"/>
        <result property="routeId" column="route_id"/>
	    <result property="parentRouteId" column="parent_route_id"/>
        <result property="routeName" column="route_name"/>
        <result property="taskCreateType" column="task_create_type"/>
        <result property="version" column="version"/>
        <result property="routeClassify" column="route_classify"/>
        <result property="suitRange" column="suit_range"/>
        <result property="routeSort" column="route_sort"/>
        <result property="routeRemark" column="route_remark"/>
        <collection property="routeNodeList" javaType="java.util.List"
                    resultMap="SignPatientManageRouteNodeResult"/>
        <collection property="triggerConditionList" javaType="java.util.List"
                    resultMap="SignRouteTriggerConditionResult"/>
    </resultMap>

    <resultMap type="SignPatientManageRouteNode" id="SignPatientManageRouteNodeResult">
        <result property="id" column="id"/>
        <result property="manageRouteId" column="manage_route_id"/>
        <result property="manageRouteName" column="manage_route_name"/>
        <result property="routeNodeName" column="route_node_name"/>
        <result property="routeNodeDay" column="route_node_day"/>
        <result property="taskType" column="task_type"/>
        <result property="taskStatus" column="task_status"/>
        <result property="taskSubdivision" column="task_subdivision"/>
        <result property="secondClassifyDescribe" column="second_classify_describe"/>
        <result property="executeTime" column="execute_time"/>
        <result property="phonePushSign" column="phone_push_sign"/>
        <result property="scriptInfoId" column="script_info_id"/>
        <result property="phoneTemplateId" column="phone_template_id"/>
        <result property="phoneTemplateName" column="phone_template_name"/>
        <result property="phoneNodeContent" column="phone_node_content"/>
        <result property="phoneRedialTimes" column="phone_redial_times"/>
        <result property="phoneTimeInterval" column="phone_time_interval"/>
        <result property="phoneMessageRemind" column="phone_message_remind"/>
        <result property="phoneMessageTemplateId" column="phone_message_template_id"/>
        <result property="phoneMessageTemplateName" column="phone_message_template_name"/>
        <result property="questionInfoId" column="question_info_id"/>
        <result property="questionnaireName" column="questionnaire_name"/>
        <result property="questionnaireContent" column="questionnaire_content"/>
        <result property="questionExpirationDate" column="question_expiration_date"/>
        <result property="propagandaInfoId" column="propaganda_info_id"/>
        <result property="propagandaTitle" column="propaganda_title"/>
        <result property="propagandaContent" column="propaganda_content"/>
        <result property="messagePushSign" column="message_push_sign"/>
        <result property="messageTemplateId" column="message_template__id"/>
        <result property="messageTemplateName" column="message_template_name"/>
        <result property="messagePreview" column="message_preview"/>
        <result property="messageNodeContent" column="message_node_content"/>
        <result property="officialPushSign" column="official_push_sign"/>
        <result property="officialTemplateId" column="official_template_id"/>
        <result property="officialTemplateName" column="official_template_name"/>
        <result property="officialRemindContent" column="official_remind_content"/>
        <result property="officialNodeContent" column="official_node_content"/>
        <result property="appletPushSign" column="applet_push_sign"/>
        <result property="appletTemplateId" column="applet_template_id"/>
        <result property="appletTemplateName" column="applet_template_name"/>
        <result property="appletRemindContent" column="applet_remind_content"/>
        <result property="appletPromptDescription" column="applet_prompt_description"/>
        <result property="appletNodeContent" column="applet_node_content"/>
        <result property="routeCheckStatus" column="route_check_status"/>
        <result property="routeCheckPerson" column="route_check_person"/>
        <result property="routeCheckDate" column="route_check_date"/>
        <result property="routeCheckRemark" column="route_check_remark"/>
        <result property="routeNodeRemark" column="route_node_remark"/>
        <result property="nodeExecuteStatus" column="node_execute_status"/>
        <result property="routeHandleRemark" column="route_handle_remark"/>
        <result property="routeHandleId" column="route_handle_id"/>
        <result property="routeHandlePerson" column="route_handle_person"/>
        <result property="routeLink" column="route_link"/>
        <result property="textRemindContent" column="text_remind_content"/>
    </resultMap>

    <resultMap type="com.xinelu.manage.vo.signroutetriggercondition.SignRouteTriggerConditionVO"
               id="SignRouteTriggerConditionResult">
        <result property="signRouteTriggerConditionId" column="signRouteTriggerConditionId"/>
        <result property="patientManageRouteId" column="patient_manage_route_id"/>
        <result property="routeName" column="route_name"/>
        <result property="triggerConditionCode" column="trigger_condition_code"/>
        <result property="triggerConditionName" column="trigger_condition_name"/>
        <result property="triggerConditionOperator" column="trigger_condition_operator"/>
        <result property="triggerConditionValue" column="trigger_condition_value"/>
        <result property="triggerConditionSort" column="trigger_condition_sort"/>
        <result property="triggerConditionRemark" column="trigger_condition_remark"/>
    </resultMap>

    <sql id="selectSignPatientManageRouteVo">
        select id, sign_patient_record_id, patient_id, patient_name, department_id, department_name, service_package_id, package_name, disease_type_id, disease_type_name, route_id, parent_route_id, route_name, task_create_type, version, route_classify, suit_range, route_sort, route_remark, create_by, create_time, update_by, update_time from sign_patient_manage_route
    </sql>

    <select id="selectSignPatientManageRouteList" parameterType="com.xinelu.manage.domain.signpatientmanageroute.SignPatientManageRoute" resultMap="SignPatientManageRouteResult">
        <include refid="selectSignPatientManageRouteVo"/>
        <where>
	        <if test="signPatientRecordId != null ">
	            and sign_patient_record_id = #{signPatientRecordId}
	        </if>
	        <if test="patientId != null ">
	            and patient_id = #{patientId}
	        </if>
	        <if test="patientName != null  and patientName != ''">
	            and patient_name like concat('%', #{patientName}, '%')
	        </if>
	        <if test="departmentId != null ">
	            and department_id = #{departmentId}
	        </if>
	        <if test="departmentName != null  and departmentName != ''">
	            and department_name like concat('%', #{departmentName}, '%')
	        </if>
	        <if test="servicePackageId != null ">
	            and service_package_id = #{servicePackageId}
	        </if>
	        <if test="packageName != null  and packageName != ''">
	            and package_name like concat('%', #{packageName}, '%')
	        </if>
	        <if test="diseaseTypeId != null ">
	            and disease_type_id = #{diseaseTypeId}
	        </if>
	        <if test="diseaseTypeName != null  and diseaseTypeName != ''">
	            and disease_type_name like concat('%', #{diseaseTypeName}, '%')
	        </if>
	        <if test="parentRouteId == null">
		        and (parent_route_id = 0 or parent_route_id is null)
	        </if>
	        <if test="parentRouteId != null ">
		        and parent_route_id = #{parentRouteId}
	        </if>
	        <if test="routeName != null  and routeName != ''">
	            and route_name like concat('%', #{routeName}, '%')
	        </if>
	        <if test="taskCreateType != null  and taskCreateType != ''">
	            and task_create_type = #{taskCreateType}
	        </if>
	        <if test="version != null  and version != ''">
	            and version = #{version}
	        </if>
	        <if test="routeClassify != null  and routeClassify != ''">
	            and route_classify = #{routeClassify}
	        </if>
	        <if test="suitRange != null  and suitRange != ''">
	            and suit_range = #{suitRange}
	        </if>
	        <if test="routeSort != null ">
	            and route_sort = #{routeSort}
	        </if>
	        <if test="routeRemark != null  and routeRemark != ''">
	            and route_remark = #{routeRemark}
	        </if>
        </where>
    </select>

    <select id="selectSignPatientManageRouteById" parameterType="Long"
            resultMap="SignPatientManageRouteResult">
            <include refid="selectSignPatientManageRouteVo"/>
            where id = #{id}
    </select>

    <select id="selectSignPatientManageRoute"
            resultType="com.xinelu.manage.vo.signpatientmanageroute.SignPatientManageRouteVO"
            resultMap="SignPatientManageRouteVOResult">
        select spmr.id signPatientManageRouteId,
               spmr.sign_patient_record_id,
               spmr.patient_id,
               spmr.patient_name,
               spmr.department_id,
               spmr.department_name,
               spmr.service_package_id,
               spmr.package_name,
               spmr.disease_type_id,
               spmr.disease_type_name,
               spmr.route_id,
               spmr.route_name,
               spmr.task_create_type,
               spmr.version,
               spmr.route_classify,
               spmr.suit_range,
               spmr.route_sort,
               spmr.route_remark,
               spmrn.id,
               spmrn.manage_route_id,
               spmrn.manage_route_name,
               spmrn.route_node_name,
               spmrn.route_node_day,
               spmrn.task_type,
               spmrn.task_status,
               spmrn.task_subdivision,
               spmrn.second_classify_describe,
               spmrn.execute_time,
               spmrn.phone_push_sign,
               spmrn.script_info_id,
               spmrn.phone_template_id,
               spmrn.phone_template_name,
               spmrn.phone_node_content,
               spmrn.phone_redial_times,
               spmrn.phone_time_interval,
               spmrn.phone_message_remind,
               spmrn.phone_message_template_id,
               spmrn.phone_message_template_name,
               spmrn.question_info_id,
               spmrn.questionnaire_name,
               spmrn.questionnaire_content,
               spmrn.question_expiration_date,
               spmrn.propaganda_info_id,
               spmrn.propaganda_title,
               spmrn.propaganda_content,
               spmrn.message_push_sign,
               spmrn.message_template__id,
               spmrn.message_template_name,
               spmrn.message_preview,
               spmrn.message_node_content,
               spmrn.official_push_sign,
               spmrn.official_template_id,
               spmrn.official_template_name,
               spmrn.official_remind_content,
               spmrn.official_node_content,
               spmrn.applet_push_sign,
               spmrn.applet_template_id,
               spmrn.applet_template_name,
               spmrn.applet_remind_content,
               spmrn.applet_prompt_description,
               spmrn.applet_node_content,
               spmrn.follow_template_id,
               spmrn.follow_template_name,
               spmrn.follow_content,
               spmrn.route_check_status,
               spmrn.route_check_person,
               spmrn.route_check_date,
               spmrn.route_check_remark,
               spmrn.route_node_remark,
               spmrn.node_execute_status,
               spmrn.route_handle_remark,
               spmrn.route_handle_id,
               spmrn.route_handle_person,
               spmrn.route_link,
               spmrn.text_remind_content,
               srtc.id signRouteTriggerConditionId,
               srtc.patient_manage_route_id,
               srtc.route_name,
               srtc.trigger_condition_code,
               srtc.trigger_condition_name,
               srtc.trigger_condition_operator,
               srtc.trigger_condition_value,
               srtc.trigger_condition_sort,
               srtc.trigger_condition_remark
        from sign_patient_manage_route spmr
                 LEFT JOIN sign_patient_manage_route_node spmrn ON spmr.id = spmrn.manage_route_id
                 LEFT JOIN sign_route_trigger_condition srtc ON spmr.id = spmrn.patient_manage_route_id
        where spmr.id = #{id}
    </select>

    <select id="selectManualFollowUpList"
            resultType="com.xinelu.manage.vo.manualfollowup.ManualFollowUpVO">
        SELECT
        pi.id AS 'patientId',
        pi.patient_name,
        pi.patient_phone,
        pi.sex,
        pi.patient_type,
        pi.hospital_agency_id,
        pi.hospital_agency_name,
        pi.campus_agency_id,
        pi.campus_agency_name,
        pi.department_id,
        pi.department_name,
        pi.ward_id,
        pi.ward_name,
        pi.in_hospital_number,
        pi.visit_serial_number,
        pi.main_diagnosis,
        pvr.id AS 'visitRecordId',
        pvr.surgical_name,
        pi.attending_physician_id,
        pi.attending_physician_name,
        pi.visit_date AS 'visitTime',
        pi.admission_time,
        pi.discharge_time AS 'dischargeTime',
        pi.visit_method,
        pter.id AS 'taskExecuteRecordId',
        pter.execute_time AS 'executeTime',
        spmr.id AS 'manageRouteId',
        spmrn.id AS 'manageRouteNodeId',
        spmrn.manage_route_name AS 'manageRouteName',
        spmrn.route_node_name AS 'routeNodeName',
        spmrn.route_node_day,
        spmrn.task_node_type,
        CASE
        WHEN spmrn.task_node_type = 'PHONE_OUTBOUND' THEN spmrn.phone_template_name
        WHEN spmrn.task_node_type = 'QUESTIONNAIRE_SCALE' THEN spmrn.questionnaire_name
        END AS 'templateName',
        CASE
        WHEN spmrn.task_node_type = 'PHONE_OUTBOUND' THEN spmrn.script_info_id
        WHEN spmrn.task_node_type = 'QUESTIONNAIRE_SCALE' THEN spmrn.question_info_id
        END AS 'templateId',
        spmrn.script_info_id,
        spmrn.follow_template_id,
        spmrn.node_execute_status,
        spmrn.route_handle_remark
        FROM
        sign_patient_manage_route spmr
        LEFT JOIN sign_patient_manage_route_node spmrn ON spmr.id = spmrn.manage_route_id
        LEFT JOIN patient_task_execute_record pter on spmr.id = pter.manage_route_id and spmrn.id = pter.manage_route_node_id
        LEFT JOIN patient_info pi ON spmr.patient_id = pi.id
        LEFT JOIN patient_visit_record pvr ON pi.patient_visit_record_id = pvr.id
        <where>
            pi.del_flag = 0
            AND spmrn.route_check_status = 'AGREE'
            AND spmrn.task_node_type in ('PHONE_OUTBOUND','QUESTIONNAIRE_SCALE')
            AND spmrn.phone_dial_method = 'COMMON'
            <if test="patientName != null and patientName != ''">
                AND pi.patient_name LIKE concat('%', #{patientName}, '%')
            </if>
            <if test="patientPhone != null and patientPhone != ''">
                AND pi.patient_phone LIKE concat('%', #{patientPhone}, '%')
            </if>
            <if test="hospitalAgencyId != null">
                AND pi.hospital_agency_id = #{hospitalAgencyId}
            </if>
            <if test="campusAgencyId != null">
                AND pi.campus_agency_id = #{campusAgencyId}
            </if>
            <if test="departmentId != null">
                AND pi.department_id = #{departmentId}
            </if>
            <if test="wardId != null">
                AND pi.ward_id = #{wardId}
            </if>
            <if test="visitMethod != null and visitMethod != ''">
               AND pi.visit_method = #{visitMethod}
            </if>
            <if test="visitSerialNumber != null and visitSerialNumber != ''">
                AND pi.visit_serial_number LIKE concat('%', #{visitSerialNumber}, '%')
            </if>
            <if test="mainDiagnosis != null and mainDiagnosis != ''">
                AND pi.main_diagnosis LIKE concat('%', #{mainDiagnosis}, '%')
            </if>
            <if test="attendingPhysicianId != null">
                AND pi.attending_physician_id = #{attendingPhysicianId}
            </if>
            <if test="attendingPhysicianName != null and attendingPhysicianName != ''">
                AND pi.attending_physician_name LIKE concat('%',#{attendingPhysicianName}, '%')
            </if>
            <if test="nodeExecuteStatus != null ">
                AND spmrn.node_execute_status = #{nodeExecuteStatus}
            </if>
            <if test="followStartTime != null ">
                AND pter.execute_time >= #{followStartTime}
            </if>
            <if test="followEndTime != null ">
                AND pter.execute_time &lt;= #{followEndTime}
            </if>
            <if test="manageRouteName != null and manageRouteName != ''">
                AND spmrn.manage_route_name like concat('%', #{manageRouteName}, '%')
            </if>
            <if test="phoneNodeExecuteResultStatus = null">
                AND (spmrn.phone_node_execute_result_status is null or spmrn.phone_node_execute_result_status = ' ')
            </if>
            <!-- 数据范围过滤 -->
            ${params.dataScope}
        </where>
        order by spmr.create_time DESC
    </select>

    <select id="selectFollowPatientInfo"
            resultType="com.xinelu.manage.vo.manualfollowup.ManualFollowPatientVO">
        SELECT
            ri.id,
            ri.patient_name,
            ri.patient_phone,
            ri.family_member_phone,
            ri.birth_date,
            ri.card_no,
            ri.sex,
            ri.address,
            ri.avatar_picture_url,
            p.id AS 'patientId',
            spmr.service_package_id,
            spmr.package_name
        FROM
            sign_patient_manage_route spmr
                LEFT JOIN patient_info p ON spmr.patient_id = p.id
                LEFT JOIN resident_info ri ON p.resident_id = ri.id
        WHERE
            p.del_flag = 0
          AND ri.del_flag = 0 AND spmr.id = #{id}
    </select>

    <insert id="insertSignPatientManageRoute" parameterType="com.xinelu.manage.domain.signpatientmanageroute.SignPatientManageRoute" useGeneratedKeys="true"
            keyProperty="id">
        insert into sign_patient_manage_route
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="signPatientRecordId != null">sign_patient_record_id,
            </if>
            <if test="patientId != null">patient_id,
            </if>
            <if test="patientName != null">patient_name,
            </if>
            <if test="departmentId != null">department_id,
            </if>
            <if test="departmentName != null">department_name,
            </if>
            <if test="servicePackageId != null">service_package_id,
            </if>
            <if test="packageName != null">package_name,
            </if>
            <if test="diseaseTypeId != null">disease_type_id,
            </if>
            <if test="diseaseTypeName != null">disease_type_name,
            </if>
	        <if test="routeId != null">route_id,
	        </if>
	        <if test="parentRouteId != null">parent_route_id,
	        </if>
            <if test="routeName != null">route_name,
            </if>
            <if test="taskCreateType != null">task_create_type,
            </if>
            <if test="version != null">version,
            </if>
            <if test="routeClassify != null">route_classify,
            </if>
            <if test="suitRange != null">suit_range,
            </if>
            <if test="routeSort != null">route_sort,
            </if>
            <if test="routeRemark != null">route_remark,
            </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>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="signPatientRecordId != null">#{signPatientRecordId},
            </if>
            <if test="patientId != null">#{patientId},
            </if>
            <if test="patientName != null">#{patientName},
            </if>
            <if test="departmentId != null">#{departmentId},
            </if>
            <if test="departmentName != null">#{departmentName},
            </if>
            <if test="servicePackageId != null">#{servicePackageId},
            </if>
            <if test="packageName != null">#{packageName},
            </if>
            <if test="diseaseTypeId != null">#{diseaseTypeId},
            </if>
            <if test="diseaseTypeName != null">#{diseaseTypeName},
            </if>
	        <if test="routeId != null">#{routeId},
	        </if>
	        <if test="parentRouteId != null">#{parentRouteId},
	        </if>
            <if test="routeName != null">#{routeName},
            </if>
            <if test="taskCreateType != null">#{taskCreateType},
            </if>
            <if test="version != null">#{version},
            </if>
            <if test="routeClassify != null">#{routeClassify},
            </if>
            <if test="suitRange != null">#{suitRange},
            </if>
            <if test="routeSort != null">#{routeSort},
            </if>
            <if test="routeRemark != null">#{routeRemark},
            </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>
        </trim>
    </insert>

    <update id="updateSignPatientManageRoute" parameterType="com.xinelu.manage.domain.signpatientmanageroute.SignPatientManageRoute">
        update sign_patient_manage_route
        <trim prefix="SET" suffixOverrides=",">
                    <if test="signPatientRecordId != null">sign_patient_record_id =
                        #{signPatientRecordId},
                    </if>
                    <if test="patientId != null">patient_id =
                        #{patientId},
                    </if>
                    <if test="patientName != null">patient_name =
                        #{patientName},
                    </if>
                    <if test="departmentId != null">department_id =
                        #{departmentId},
                    </if>
                    <if test="departmentName != null">department_name =
                        #{departmentName},
                    </if>
                    <if test="servicePackageId != null">service_package_id =
                        #{servicePackageId},
                    </if>
                    <if test="packageName != null">package_name =
                        #{packageName},
                    </if>
                    <if test="diseaseTypeId != null">disease_type_id =
                        #{diseaseTypeId},
                    </if>
                    <if test="diseaseTypeName != null">disease_type_name =
                        #{diseaseTypeName},
                    </if>
			        <if test="routeId != null">route_id =
				        #{routeId},
			        </if>
			        <if test="parentRouteId != null">parent_route_id =
				        #{parentRouteId},
			        </if>
                    <if test="routeName != null">route_name =
                        #{routeName},
                    </if>
                    <if test="taskCreateType != null">task_create_type =
                        #{taskCreateType},
                    </if>
                    <if test="version != null">version =
                        #{version},
                    </if>
                    <if test="routeClassify != null">route_classify =
                        #{routeClassify},
                    </if>
                    <if test="suitRange != null">suit_range =
                        #{suitRange},
                    </if>
                    <if test="routeSort != null">route_sort =
                        #{routeSort},
                    </if>
                    <if test="routeRemark != null">route_remark =
                        #{routeRemark},
                    </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>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteSignPatientManageRouteById" parameterType="Long">
        delete from sign_patient_manage_route where id = #{id}
    </delete>

    <delete id="deleteSignPatientManageRouteByIds" parameterType="String">
        delete from sign_patient_manage_route where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <select id="selectSignPatientManageRouteNode"
            resultType="com.xinelu.manage.vo.signpatientmanageroute.PhonePush">
        select pi.id                                           patientId,
               pi.patient_name,
               pi.patient_phone,
               spmrn.route_node_name,
               spmrn.phone_push_sign,
               spmrn.route_node_day,
               spmrn.script_info_id,
               spmrn.phone_template_id,
               spmrn.phone_template_name,
               spmrn.phone_node_content,
               spmrn.phone_redial_times,
               spmrn.phone_dial_method,
               spmrn.phone_time_interval,
               spmrn.phone_message_remind,
               spmrn.phone_connect_status,
               spmrn.route_handle_remark,
               spmrn.phone_message_template_id,
               spmrn.phone_message_template_name,
               spmrn.phone_node_execute_result_status,
               spmrn.phone_message_node_execute_result_status,
               (SELECT text_message_id
                FROM text_message tm
                WHERE tm.id = spmrn.phone_message_template_id) phoneMessageTemplateCode
        from sign_patient_manage_route_node spmrn
                 LEFT JOIN sign_patient_manage_route spmr ON spmr.id = spmrn.manage_route_id
                 LEFT JOIN patient_info pi ON spmr.patient_id = pi.id
        where spmrn.id = #{manageRouteNodeId}
    </select>

    <select id="selectPatientPhone" resultType="java.lang.String">
        select patient_phone
        from patient_info
        where id = #{id}
    </select>
</mapper>