<?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.mobile.mapper.homepage.HomePageMapper">

    <select id="selectManageRouteNode" resultType="com.xinelu.mobile.vo.myfollowup.MyFollowUpVO">
        select
        pi.id patientId,
        pi.patient_name,
        spmr.id manageRouteId,
        spmr.route_name manageRouteName,
        spmrn.id manageRouteNodeId,
        spmrn.route_node_name manageRouteNodeName,
        spmrn.task_node_type,
        spmrn.route_node_name,
        IFNULL( spmrn.route_node_day,0) routeNodeDay,
        pi.discharge_time,
        pter.id patientTaskExecuteRecordId,
        IF(pter.id is NULL,0,1) sign,
        CASE
        WHEN spmrn.task_node_type = 'PHONE_OUTBOUND' THEN spmrn.phone_id
        WHEN spmrn.task_node_type = 'QUESTIONNAIRE_SCALE' THEN spmrn.question_info_id
        END AS templateId
        FROM sign_patient_manage_route_node spmrn
        LEFT JOIN sign_patient_manage_route spmr ON spmrn.manage_route_id = spmr.id
        LEFT JOIN patient_info pi ON spmr.patient_id = pi.id
        LEFT JOIN patient_task_execute_record pter ON pter.manage_route_node_id = spmrn.id
        <where>
            spmrn.task_node_type in('PHONE_OUTBOUND','QUESTIONNAIRE_SCALE')
            and spmrn.phone_dial_method = 'COMMON'
            <if test="residentId != null ">
                and pi.resident_id = #{residentId}
            </if>
            <if test="routeNodeName != null and routeNodeName != ''">
                and spmrn.route_node_name = #{routeNodeName}
            </if>
            <if test="nodeExecuteStatus != null and nodeExecuteStatus != ''">
                and spmrn.node_execute_status = #{nodeExecuteStatus}
            </if>
        </where>
    </select>

    <select id="selectTaskExecuteRecordByResidentId"
            resultType="com.xinelu.manage.domain.patienttaskexecuterecord.PatientTaskExecuteRecord">
        select
        CASE
        WHEN spmrn.task_node_type = 'PHONE_OUTBOUND' THEN spmrn.phone_template_name
        WHEN spmrn.task_node_type = 'QUESTIONNAIRE_SCALE' THEN spmrn.questionnaire_name
        WHEN spmrn.task_node_type = 'PROPAGANDA_ARTICLE' THEN spmrn.propaganda_title
        WHEN spmrn.task_node_type = 'TEXT_REMIND' THEN '文字提醒'
        END AS manageRouteNodeName,
        pter.execute_time
        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 pi.id = spmr.patient_id
        LEFT JOIN resident_info ri ON ri.id = pi.resident_id
        LEFT JOIN patient_task_execute_record pter ON spmrn.id = pter.manage_route_node_id
        <where>
            ri.del_flag = 0 AND pi.del_flag = 0
            <if test="residentId != null ">
                and pi.resident_id = #{residentId}
            </if>
            <if test="nodeExecuteStatus != null and nodeExecuteStatus != ''">
                and spmrn.node_execute_status = #{nodeExecuteStatus}
            </if>
        </where>
        ORDER BY pter.execute_time desc
    </select>

    <select id="satisfactionQuestionnaireByResidentId"
            resultType="com.xinelu.mobile.vo.satisfactionquestionnaire.SatisfactionQuestionnaire">
        SELECT spmrn.question_info_id                        templateId,
               spmrn.questionnaire_name,
               pqsr.create_time                              executeTime,
               spmrn.id                                      manageRouteNodeId,
               (SELECT id
                FROM patient_question_submit_result pqsr2
                WHERE pqsr2.resident_id = #{residentId}
                  AND pqsr2.question_info_id = spmrn.question_info_id
                  AND pqsr2.manage_route_node_id = spmrn.id) patientQuestionSubmitResultId
        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 pi.id = spmr.patient_id
                 LEFT JOIN question_info qi on qi.id = spmrn.question_info_id
                 LEFT JOIN patient_question_submit_result pqsr on spmrn.id = pqsr.manage_route_node_id
        WHERE qi.question_type = #{questionType}
          AND qi.questionnaire_status = #{questionnaireStatus}
          AND pi.resident_id = #{residentId}
          AND spmrn.node_execute_status = 'EXECUTED'
        ORDER BY pqsr.create_time DESC, spmrn.create_time DESC
    </select>

    <select id="selectResidentAndSubscribeMessageRecord"
            resultType="com.xinelu.mobile.vo.wechatofficialaccountcallback.PatientVO">
        select ri.id residentId,
               ri.open_id,
               pi.patient_name,
               pi.patient_phone,
               pi.card_no,
               pi.patient_type,
               pi.admission_time,
               pi.discharge_time,
               pi.visit_date,
               pi.id patientId
        from resident_info ri
                 LEFT JOIN subscribe_message_record smr ON ri.open_id = smr.openid
                 LEFT JOIN patient_info pi ON ri.id = pi.resident_id
        where ri.del_flag = 0
          and smr.subscribe_status = #{subscribeStatus}
          and smr.template_id = #{templateId}
    </select>

    <select id="selectSignPatientManageRouteNode"
            resultType="com.xinelu.mobile.vo.wechatofficialaccountcallback.PatientVO">
        select
        spmrn.manage_route_id,
        spmrn.id signPatientManageRouteNodeId,
        spmrn.task_node_type,
        spmrn.task_subdivision,
        spmrn.manage_route_name,
        spmrn.route_node_name,
        spmrn.route_node_day,
        spmrn.task_subdivision,
        spmrn.applet_push_sign,
        spmrn.node_content,
        spmrn.node_execute_status,
        spmrn.applet_node_content,
        spmr.patient_id,
        pi.patient_type,
        pi.hospital_agency_name
        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 pi.id = spmr.patient_id
        where
        spmrn.node_execute_status = 'UNEXECUTED'
        and (spmrn.applet_node_execute_result_status is null or spmrn.applet_node_execute_result_status = '')
        and spmrn.del_flag = 0
        and spmrn.applet_push_sign = 1
        and pi.del_flag = 0
        and (spmrn.task_node_type = 'PROPAGANDA_ARTICLE' or spmrn.task_node_type = 'TEXT_REMIND' or
        spmrn.task_node_type = 'QUESTIONNAIRE_SCALE' and phone_dial_method != 'COMMON')
        <if test="patientId != null and patientId.size() > 0">
            and spmr.patient_id in
            <foreach item="patientId" collection="patientId" open="(" separator="," close=")">
                #{patientId}
            </foreach>
        </if>
    </select>

    <select id="selectManageRouteByResidentId"
            resultType="com.xinelu.mobile.vo.homepage.MessageTabulationVO">
        select spmrn.id                  manageRouteNodeId,
               spmrn.manage_route_name,
               spmrn.route_node_name,
               spmrn.route_node_day,
               spmrn.update_time,
               spmrn.message_status,
               IF(pter.id is NULL, 0, 1) sign,
               pter.id                   patientTaskExecuteRecordId,
               spmrn.task_node_type,
               spmrn.node_finish_date,
               CASE
                   WHEN spmrn.task_node_type = 'QUESTIONNAIRE_SCALE' THEN spmrn.questionnaire_name
                   WHEN spmrn.task_node_type = 'PROPAGANDA_ARTICLE' THEN spmrn.propaganda_title
                   WHEN spmrn.task_node_type = 'TEXT_REMIND' THEN '文字提醒'
                   END AS                manageRouteNodeName
        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 pi.id = spmr.patient_id
                 LEFT JOIN patient_task_execute_record pter ON pter.manage_route_node_id = spmrn.id
        where pi.resident_id = #{residentId}
          and spmrn.node_execute_status = #{nodeExecuteStatus}
          and spmrn.applet_node_execute_result_status = 'SUCCESS'
          and spmrn.applet_push_sign = 1
          and spmrn.del_flag = 0
          and pi.del_flag = 0
          and (spmrn.task_node_type = 'PROPAGANDA_ARTICLE' or spmrn.task_node_type = 'TEXT_REMIND' or
               spmrn.task_node_type = 'QUESTIONNAIRE_SCALE' and phone_dial_method != 'COMMON')
        Order BY spmrn.update_time DESC
    </select>

    <select id="selectPatientManageRouteByManageRouteNodeId"
            resultType="com.xinelu.manage.domain.patienttaskexecuterecord.PatientTaskExecuteRecord">
        select spmr.patient_id,
               spmr.patient_name,
               spmr.id                    manageRouteId,
               spmrn.id                   manageRouteNodeId,
               spmrn.manage_route_name    manageRouteName,
               spmrn.route_node_name      manageRouteNodeName,
               spmrn.task_node_type       taskNodeType,
               pi.patient_visit_record_id visitRecordId
        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 pi.id = spmr.patient_id
        where spmrn.id = #{manageRouteNodeId}
    </select>

    <select id="selectPhoneNodeContent"
            resultType="com.xinelu.manage.vo.signpatientmanageroute.PhonePush">
        select spmrn.route_node_name,
               spmrn.phone_push_sign,
               spmrn.route_node_day,
               spmrn.phone_id,
               spmrn.phone_template_id,
               spmrn.phone_template_name,
               spmrn.phone_redial_times,
               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_content
        from patient_task_execute_record pter
                 LEFT JOIN sign_patient_manage_route_node spmrn ON pter.manage_route_node_id = spmrn.id
                 LEFT JOIN sign_patient_manage_route spmr ON spmr.id = pter.manage_route_id
        where pter.id = #{patientTaskExecuteRecordId}
    </select>

    <select id="nodeExecuteStatus" resultType="com.xinelu.mobile.vo.homepage.NodeExecuteStatus">
        select spmrn.id manageRouteNodeId,
               spmrn.task_node_type,
               pter.id  patientTaskExecuteRecordId,
               pqsr.id  patientQuestionSubmitResultId
        from sign_patient_manage_route_node spmrn
                 LEFT JOIN patient_task_execute_record pter on spmrn.id = pter.manage_route_node_id
                 LEFT JOIN patient_question_submit_result pqsr ON pqsr.manage_route_node_id = spmrn.id
        where spmrn.id = #{manageRouteNodeId}
    </select>
</mapper>