<?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.patientquestionsubmitresult.PatientQuestionSubmitResultMapper">

    <resultMap type="PatientQuestionSubmitResult" id="PatientQuestionSubmitResultResult">
        <result property="id" column="id"/>
        <result property="taskExecuteRecordId" column="task_execute_record_id"/>
        <result property="patientId" column="patient_id"/>
        <result property="manageRouteId" column="manage_route_id"/>
        <result property="manageRouteNodeId" column="manage_route_node_id"/>
        <result property="patientName" column="patient_name"/>
        <result property="manageRouteName" column="manage_route_name"/>
        <result property="manageRouteNodeName" column="manage_route_node_name"/>
        <result property="questionInfoId" column="question_info_id"/>
        <result property="departmentId" column="department_id"/>
        <result property="departmentName" column="department_name"/>
        <result property="diseaseTypeId" column="disease_type_id"/>
        <result property="diseaseTypeName" column="disease_type_name"/>
        <result property="questionnaireName" column="questionnaire_name"/>
        <result property="questionnaireDescription" column="questionnaire_description"/>
        <result property="answeringMethod" column="answering_method"/>
        <result property="questionnaireId" column="questionnaire_id"/>
        <result property="questionCount" column="question_count"/>
        <result property="questionnaireTotalScore" column="questionnaire_total_score"/>
        <result property="totalScore" column="total_score"/>
        <result property="remark" column="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"/>
        <result property="contentedFlag" column="contented_flag"/>
        <result property="remarkableFlag" column="remarkable_flag"/>
    </resultMap>

    <resultMap type="com.xinelu.manage.vo.patientquestionsubmitresult.PatientQuestionSubmitResultVO"
               id="PatientQuestionSubmitResultDTO">
        <result property="submitResulId" column="submitResulId"/>
        <result property="questionnaireName" column="questionnaire_name"/>
        <result property="questionnaireDescription" column="questionnaire_description"/>
        <result property="questionnaireTotalScore" column="questionnaire_total_score"/>
        <result property="totalScore" column="total_score"/>
        <result property="submitContentedFlag" column="submitContentedFlag"/>
        <result property="submitRemarkableFlag" column="submitRemarkableFlag"/>
        <collection property="subjectResultList" javaType="java.util.List"
                    resultMap="PatientQuestionSubjectResultResult"/>
    </resultMap>
    <resultMap type="com.xinelu.manage.vo.patientquestionsubjectresult.PatientQuestionSubjectResultVO"
               id="PatientQuestionSubjectResultResult">
        <result property="subjectResultId" column="subjectResultId"/>
        <result property="questionSubmitResultId" column="question_submit_result_id"/>
        <result property="questionInfoId" column="question_info_id"/>
        <result property="questionNumber" column="question_number"/>
        <result property="questionType" column="question_type"/>
        <result property="questionName" column="question_name"/>
        <result property="questionDescription" column="question_description"/>
        <result property="writeDescription" column="write_description"/>
        <result property="fillBlanksAnswer" column="fill_blanks_answer"/>
        <result property="optionCount" column="option_count"/>
        <result property="whetherScore" column="whether_score"/>
        <result property="scoringMethod" column="scoring_method"/>
        <result property="scoringDescription" column="scoring_description"/>
        <result property="questionScore" column="question_score"/>
        <result property="questionSort" column="question_sort"/>
        <result property="questionRemark" column="question_remark"/>
        <result property="questionSubjectId" column="question_subject_id"/>
        <collection property="optionResults" javaType="java.util.List"
                    resultMap="PatientQuestionOptionResultResult"/>
    </resultMap>

    <resultMap type="PatientQuestionOptionResult" id="PatientQuestionOptionResultResult">
        <result property="id" column="id"/>
        <result property="questionSubjectResultId" column="question_subject_result_id"/>
        <result property="questionnaireSubjectId" column="questionnaire_subject_id"/>
        <result property="questionName" column="question_name"/>
        <result property="optionName" column="option_name"/>
        <result property="optionAnswer" column="option_answer"/>
        <result property="optionScore" column="option_score"/>
        <result property="optionChooseSign" column="option_choose_sign"/>
        <result property="optionSubmitAnswer" column="option_submit_answer"/>
        <result property="optionSort" column="option_sort"/>
        <result property="optionRemark" column="option_remark"/>
        <result property="contentedFlag" column="contented_flag"/>
        <result property="remarkableFlag" column="remarkable_flag"/>
        <result property="questionSubjectOptionId" column="question_subject_option_id"/>
    </resultMap>

    <sql id="selectPatientQuestionSubmitResultVo">
        select id,
               task_execute_record_id,
               patient_id,
               manage_route_id,
               manage_route_node_id,
               patient_name,
               manage_route_name,
               manage_route_node_name,
               question_info_id,
               department_id,
               department_name,
               disease_type_id,
               disease_type_name,
               questionnaire_name,
               questionnaire_description,
               answering_method,
               questionnaire_id,
               question_count,
               questionnaire_total_score,
               total_score,
               remark,
               create_by,
               create_time,
               update_by,
               update_time,
               contented_flag,
               remarkable_flag
        from patient_question_submit_result
    </sql>

    <select id="selectPatientQuestionSubmitResultList" parameterType="PatientQuestionSubmitResult"
            resultMap="PatientQuestionSubmitResultResult">
        <include refid="selectPatientQuestionSubmitResultVo"/>
        <where>
            <if test="taskExecuteRecordId != null ">
                and task_execute_record_id = #{taskExecuteRecordId}
            </if>
            <if test="patientId != null ">
                and patient_id = #{patientId}
            </if>
            <if test="manageRouteId != null ">
                and manage_route_id = #{manageRouteId}
            </if>
            <if test="manageRouteNodeId != null ">
                and manage_route_node_id = #{manageRouteNodeId}
            </if>
            <if test="patientName != null  and patientName != ''">
                and patient_name like concat('%', #{patientName}, '%')
            </if>
            <if test="manageRouteName != null  and manageRouteName != ''">
                and manage_route_name like concat('%', #{manageRouteName}, '%')
            </if>
            <if test="manageRouteNodeName != null  and manageRouteNodeName != ''">
                and manage_route_node_name like concat('%', #{manageRouteNodeName}, '%')
            </if>
            <if test="questionInfoId != null ">
                and question_info_id = #{questionInfoId}
            </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="diseaseTypeId != null ">
                and disease_type_id = #{diseaseTypeId}
            </if>
            <if test="diseaseTypeName != null  and diseaseTypeName != ''">
                and disease_type_name like concat('%', #{diseaseTypeName}, '%')
            </if>
            <if test="questionnaireName != null  and questionnaireName != ''">
                and questionnaire_name like concat('%', #{questionnaireName}, '%')
            </if>
            <if test="questionnaireDescription != null  and questionnaireDescription != ''">
                and questionnaire_description = #{questionnaireDescription}
            </if>
            <if test="answeringMethod != null  and answeringMethod != ''">
                and answering_method = #{answeringMethod}
            </if>
            <if test="questionnaireId != null  and questionnaireId != ''">
                and questionnaire_id = #{questionnaireId}
            </if>
            <if test="questionCount != null ">
                and question_count = #{questionCount}
            </if>
            <if test="questionnaireTotalScore != null ">
                and questionnaire_total_score = #{questionnaireTotalScore}
            </if>
            <if test="totalScore != null ">
                and total_score = #{totalScore}
            </if>
            <if test="contentedFlag != null ">
                and contented_flag = #{contentedFlag}
            </if>
            <if test="remarkableFlag != null ">
                and remarkable_flag = #{remarkableFlag}
            </if>
        </where>
    </select>

    <select id="selectPatientQuestionSubmitResultById" parameterType="Long"
            resultMap="PatientQuestionSubmitResultResult">
        <include refid="selectPatientQuestionSubmitResultVo"/>
        where id = #{id}
    </select>

    <insert id="insertPatientQuestionSubmitResult" parameterType="PatientQuestionSubmitResult" useGeneratedKeys="true"
            keyProperty="id">
        insert into patient_question_submit_result
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="taskExecuteRecordId != null">task_execute_record_id,
            </if>
            <if test="patientId != null">patient_id,
            </if>
            <if test="manageRouteId != null">manage_route_id,
            </if>
            <if test="manageRouteNodeId != null">manage_route_node_id,
            </if>
            <if test="patientName != null">patient_name,
            </if>
            <if test="manageRouteName != null">manage_route_name,
            </if>
            <if test="manageRouteNodeName != null">manage_route_node_name,
            </if>
            <if test="questionInfoId != null">question_info_id,
            </if>
            <if test="departmentId != null">department_id,
            </if>
            <if test="departmentName != null">department_name,
            </if>
            <if test="diseaseTypeId != null">disease_type_id,
            </if>
            <if test="diseaseTypeName != null">disease_type_name,
            </if>
            <if test="questionnaireName != null">questionnaire_name,
            </if>
            <if test="questionnaireDescription != null">questionnaire_description,
            </if>
            <if test="answeringMethod != null">answering_method,
            </if>
            <if test="questionnaireId != null">questionnaire_id,
            </if>
            <if test="questionCount != null">question_count,
            </if>
            <if test="questionnaireTotalScore != null">questionnaire_total_score,
            </if>
            <if test="totalScore != null">total_score,
            </if>
            <if test="residentId != null">resident_id,
            </if>
            <if test="remark != null">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>
            <if test="contentedFlag != null">contented_flag,
            </if>
            <if test="remarkableFlag != null">remarkable_flag,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="taskExecuteRecordId != null">#{taskExecuteRecordId},
            </if>
            <if test="patientId != null">#{patientId},
            </if>
            <if test="manageRouteId != null">#{manageRouteId},
            </if>
            <if test="manageRouteNodeId != null">#{manageRouteNodeId},
            </if>
            <if test="patientName != null">#{patientName},
            </if>
            <if test="manageRouteName != null">#{manageRouteName},
            </if>
            <if test="manageRouteNodeName != null">#{manageRouteNodeName},
            </if>
            <if test="questionInfoId != null">#{questionInfoId},
            </if>
            <if test="departmentId != null">#{departmentId},
            </if>
            <if test="departmentName != null">#{departmentName},
            </if>
            <if test="diseaseTypeId != null">#{diseaseTypeId},
            </if>
            <if test="diseaseTypeName != null">#{diseaseTypeName},
            </if>
            <if test="questionnaireName != null">#{questionnaireName},
            </if>
            <if test="questionnaireDescription != null">#{questionnaireDescription},
            </if>
            <if test="answeringMethod != null">#{answeringMethod},
            </if>
            <if test="questionnaireId != null">#{questionnaireId},
            </if>
            <if test="questionCount != null">#{questionCount},
            </if>
            <if test="questionnaireTotalScore != null">#{questionnaireTotalScore},
            </if>
            <if test="totalScore != null">#{totalScore},
            </if>
            <if test="residentId != null">#{residentId},
            </if>
            <if test="remark != null">#{remark},
            </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="contentedFlag != null">#{contentedFlag},
            </if>
            <if test="remarkableFlag != null">#{remarkableFlag},
            </if>
        </trim>
    </insert>

    <update id="updatePatientQuestionSubmitResult" parameterType="PatientQuestionSubmitResult">
        update patient_question_submit_result
        <trim prefix="SET" suffixOverrides=",">
            <if test="taskExecuteRecordId != null">task_execute_record_id =
                #{taskExecuteRecordId},
            </if>
            <if test="patientId != null">patient_id =
                #{patientId},
            </if>
            <if test="manageRouteId != null">manage_route_id =
                #{manageRouteId},
            </if>
            <if test="manageRouteNodeId != null">manage_route_node_id =
                #{manageRouteNodeId},
            </if>
            <if test="patientName != null">patient_name =
                #{patientName},
            </if>
            <if test="manageRouteName != null">manage_route_name =
                #{manageRouteName},
            </if>
            <if test="manageRouteNodeName != null">manage_route_node_name =
                #{manageRouteNodeName},
            </if>
            <if test="questionInfoId != null">question_info_id =
                #{questionInfoId},
            </if>
            <if test="departmentId != null">department_id =
                #{departmentId},
            </if>
            <if test="departmentName != null">department_name =
                #{departmentName},
            </if>
            <if test="diseaseTypeId != null">disease_type_id =
                #{diseaseTypeId},
            </if>
            <if test="diseaseTypeName != null">disease_type_name =
                #{diseaseTypeName},
            </if>
            <if test="questionnaireName != null">questionnaire_name =
                #{questionnaireName},
            </if>
            <if test="questionnaireDescription != null">questionnaire_description =
                #{questionnaireDescription},
            </if>
            <if test="answeringMethod != null">answering_method =
                #{answeringMethod},
            </if>
            <if test="questionnaireId != null">questionnaire_id =
                #{questionnaireId},
            </if>
            <if test="questionCount != null">question_count =
                #{questionCount},
            </if>
            <if test="questionnaireTotalScore != null">questionnaire_total_score =
                #{questionnaireTotalScore},
            </if>
            <if test="totalScore != null">total_score =
                #{totalScore},
            </if>
            <if test="remark != null">remark =
                #{remark},
            </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="contentedFlag != null">contented_flag =
                #{contentedFlag},
            </if>
            <if test="remarkableFlag != null">remarkable_flag =
                #{remarkableFlag},
            </if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deletePatientQuestionSubmitResultById" parameterType="Long">
        delete
        from patient_question_submit_result
        where id = #{id}
    </delete>

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

    <select id="selectResultByTaskExecuteRecordId"
            resultType="com.xinelu.manage.vo.patientquestionsubmitresult.PatientQuestionSubmitResultVO"
            resultMap="PatientQuestionSubmitResultDTO">
        select
        pqsm.id submitResulId,
        pqsm.questionnaire_name,
        pqsm.questionnaire_description,
        pqsm.questionnaire_total_score,
        pqsm.total_score,
        pqsm.contented_flag submitContentedFlag,
        pqsm.remarkable_flag submitRemarkableFlag,
        pqsj.id subjectResult,
        pqsj.question_submit_result_id,
        pqsj.question_info_id,
        pqsj.question_number,
        pqsj.question_type,
        pqsj.question_name,
        pqsj.question_description,
        pqsj.write_description,
        pqsj.fill_blanks_answer,
        pqsj.option_count,
        pqsj.whether_score,
        pqsj.scoring_method,
        pqsj.scoring_description,
        pqsj.question_score,
        pqsj.question_sort,
        pqsj.question_remark,
        pqsj.question_subject_id,
        pqor.id,
        pqor.question_subject_result_id,
        pqor.questionnaire_subject_id,
        pqor.question_name,
        pqor.option_name,
        pqor.option_answer,
        pqor.option_score,
        pqor.option_choose_sign,
        pqor.option_submit_answer,
        pqor.option_sort,
        pqor.option_remark,
        pqor.contented_flag,
        pqor.remarkable_flag,
        pqor.question_subject_option_id
        FROM patient_question_submit_result pqsm
        LEFT JOIN patient_question_subject_result pqsj ON pqsm.id = pqsj.question_submit_result_id
        LEFT JOIN patient_question_option_result pqor ON pqor.question_subject_result_id = pqsj.id
        <where>
            <if test="taskExecuteRecordId != null">
                and pqsm.task_execute_record_id = #{taskExecuteRecordId}
            </if>
            <if test="patientQuestionSubmitResultId != null">
                and pqsm.id = #{patientQuestionSubmitResultId}
            </if>
        </where>
    </select>

    <select id="selectSatisfactionSurvey" resultType="com.xinelu.manage.vo.patientquestionsubmitresult.SatisfactionSurveyVO">
        select
        pqsr.id patientQuestionSubmitResultId,
        pqsr.patient_name,
        pi.patient_phone,
        pi.birth_date,
        pi.card_no,
        pi.sex,
        pi.patient_type,
        pi.sign_status,
        pi.visit_method,
        pi.patient_health_state,
        pi.hospital_agency_name,
        pi.department_name,
        pi.age
        from patient_question_submit_result pqsr
        LEFT JOIN question_info qi on pqsr.question_info_id = qi.id
        LEFT JOIN patient_info pi ON pi.id = pqsr.patient_id
        where
        pi.del_flag = 0
        <if test="questionType != null and questionType != ''">
            and qi.question_type = #{questionType}
        </if>
        <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="cardNo != null  and cardNo != ''">
            and pi.card_no = #{cardNo}
        </if>
        <if test="sex != null  and sex != ''">
            and pi.sex = #{sex}
        </if>
    </select>

	<select id="departmenQuestionSubmitCount" resultType="com.xinelu.manage.vo.department.DepartmentVO">
		select dt.id,
		dt.department_name,
		dt.department_code,
		( SELECT COUNT(1)
		FROM patient_question_submit_result a
		LEFT JOIN sign_patient_manage_route route on a.manage_route_id = route.id
		LEFT JOIN sign_patient_record record ON route.sign_patient_record_id = record.id
		left join patient_visit_record vr on record.patient_visit_record_id = vr.id
		WHERE vr.department_id = dt.id
		) AS countNum
		from department dt
	    left join patient_question_submit_result pqsr on dt.id = pqsr.department_id
		<where>
			<if test="hospitalAgencyId != null ">
				and dt.hospital_agency_id = #{hospitalAgencyId}
			</if>
			<if test="departmentId != null">
				and dt.id = #{departmentId}
			</if>
		</where>
		GROUP BY dt.id
	</select>

	<select id="patientQuestionSubmitList" resultType="com.xinelu.manage.vo.patientquestionsubmitresult.PatientQuestionSubmitListVo">
		select
		vr.patient_id,
		vr.patient_name,
		vr.card_no,
		vr.visit_method,
		vr.hospital_agency_id,
		vr.hospital_agency_name,
		vr.department_id,
		vr.department_name,
		vr.admission_time,
		vr.discharge_time,
		vr.in_hospital_number,
		pqsr.id as patientQuestionSubmitResultId,
		pqsr.question_info_id,
		pqsr.questionnaire_name,
		pqsr.total_score,
		pqsr.create_time
		from patient_question_submit_result pqsr
		LEFT JOIN sign_patient_manage_route route on pqsr.manage_route_id = route.id
		LEFT JOIN sign_patient_record record ON route.sign_patient_record_id = record.id
		left join patient_visit_record vr on record.patient_visit_record_id = vr.id
        <where>
			<if test="hospitalAgencyId != null">
				and vr.hospital_agency_id = #{hospitalAgencyId}
			</if>
			<if test="departmentId != null">
				and vr.department_id = #{departmentId}
			</if>
	        <if test="questionInfoId != null">
				and pqsr.question_info_id = #{questionInfoId}
			</if>
			<if test="patientName != null  and patientName != ''">
				and vr.patient_name like concat('%', #{patientName}, '%')
			</if>
			<if test="startDate != null">
				and pqsr.create_time &gt;=#{startDate}
			</if>
			<if test="endDate != null ">
				and pqsr.create_time &lt;= #{endDate}
			</if>
        </where>
	</select>
</mapper>