<?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.diagbot.mapper.MedDefectFeedbackMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.diagbot.entity.MedDefectFeedback">
        <id column="id" property="id"/>
        <result column="hospital_id" property="hospitalId"/>
        <result column="dept_id" property="deptId"/>
        <result column="dept_name" property="deptName"/>
        <result column="behospital_code" property="behospitalCode"/>
        <result column="name" property="name"/>
        <result column="cases_entry_id" property="casesEntryId"/>
        <result column="qcresult_detail_msg" property="qcresultDetailMsg"/>
        <result column="qcresult_detai_score" property="qcresultDetaiScore"/>
        <result column="mode_id" property="modeId"/>
        <result column="mode_name" property="modeName"/>
        <result column="explain_info" property="explainInfo"/>
        <result column="operation_type" property="operationType"/>
        <result column="sender_code" property="senderCode"/>
        <result column="sender_name" property="senderName"/>
        <result column="receiver_code" property="receiverCode"/>
        <result column="receiver_name" property="receiverName"/>
        <result column="cc_codes" property="ccCodes"/>
        <result column="cc_names" property="ccNames"/>
        <result column="status" property="status"/>
        <result column="is_deleted" property="isDeleted"/>
        <result column="gmt_create" property="gmtCreate"/>
        <result column="gmt_modified" property="gmtModified"/>
        <result column="creator" property="creator"/>
        <result column="modifier" property="modifier"/>
    </resultMap>

    <select id="getMedDefectFeedbackPage" resultType="com.diagbot.dto.GetMedDefectFeedbackPageDTO">
        SELECT
        t1.*, t2.age
        FROM
        (
        SELECT
        a.* ,b.is_placefile as isPlacefile FROM
        (
        SELECT
        id,
        dept_name as deptName,
        gmt_modified as gmtModified,
        mode_name as modeName,
        qcresult_detai_score as qcresultDetaiScore,
        modifier,
        receiver_code as receiverCode,
        sender_name as senderName,
        is_deleted as isDeleted,
        mode_id as modeId,
        hospital_id as hospitalId,
        creator,
        receiver_name as receiverName,
        behospital_code as behospitalCode,
        dept_id as deptId,
        gmt_create as gmtCreate,
        sender_code as senderCode,
        cases_entry_id as casesEntryId,
        explain_info as explainInfo,
        name,
        cc_names as ccNames,
        cc_codes as ccCodes,
        operation_type as operationType,
        CASE WHEN (qcresult_detail_msg is null OR qcresult_detail_msg = '') THEN '-' ELSE qcresult_detail_msg END AS qcresultDetailMsg,
        status
        FROM
        med_defect_feedback
        WHERE
        is_deleted = 'N'
        <if test="getMedDefectFeedbackPageVO.hospitalId != null and getMedDefectFeedbackPageVO.hospitalId != ''">
            and hospital_id = #{getMedDefectFeedbackPageVO.hospitalId}
        </if>
        <if test="getMedDefectFeedbackPageVO.operationType != null and getMedDefectFeedbackPageVO.operationType != ''">
            and operation_type = #{getMedDefectFeedbackPageVO.operationType}
        </if>
        <if test="getMedDefectFeedbackPageVO.status != null and getMedDefectFeedbackPageVO.status != ''">
            and status = #{getMedDefectFeedbackPageVO.status}
        </if>
        <if test="getMedDefectFeedbackPageVO.deptId != null and getMedDefectFeedbackPageVO.deptId != ''">
            and dept_id = #{getMedDefectFeedbackPageVO.deptId}
        </if>
        <if test="getMedDefectFeedbackPageVO.deptName != null and getMedDefectFeedbackPageVO.deptName != ''">
            and dept_name  LIKE CONCAT( '%', #{getMedDefectFeedbackPageVO.deptName}, '%' )
        </if>
        <if test="getMedDefectFeedbackPageVO.modeId != null and getMedDefectFeedbackPageVO.modeId != ''">
            and mode_id = #{getMedDefectFeedbackPageVO.modeId}
        </if>
        <if test="getMedDefectFeedbackPageVO.modeName != null and getMedDefectFeedbackPageVO.modeName != ''">
            and mode_name LIKE CONCAT( '%', #{getMedDefectFeedbackPageVO.modeName}, '%' )
        </if>
        <if test="getMedDefectFeedbackPageVO.startDate != null ">
            <![CDATA[ AND gmt_create >= #{getMedDefectFeedbackPageVO.startDate}]]>
        </if>
        <if test="getMedDefectFeedbackPageVO.endDate != null">
            <![CDATA[ AND gmt_create <= #{getMedDefectFeedbackPageVO.endDate}]]>
        </if>
        <if test="getMedDefectFeedbackPageVO.receiverName != null and getMedDefectFeedbackPageVO.receiverName != ''">
            and receiver_name LIKE CONCAT( '%', #{getMedDefectFeedbackPageVO.receiverName}, '%' )
        </if>
        <if test="getMedDefectFeedbackPageVO.ccNames != null and getMedDefectFeedbackPageVO.ccNames != ''">
            and cc_names LIKE CONCAT( '%', #{getMedDefectFeedbackPageVO.ccNames}, '%' )
        </if>
        <if test="getMedDefectFeedbackPageVO.name != null and getMedDefectFeedbackPageVO.name != ''">
            and name LIKE CONCAT( '%', #{getMedDefectFeedbackPageVO.name}, '%' )
        </if>
        <if test="getMedDefectFeedbackPageVO.senderName != null and getMedDefectFeedbackPageVO.senderName != ''">
            and sender_name LIKE CONCAT( '%', #{getMedDefectFeedbackPageVO.senderName}, '%' )
        </if>
        ) a ,
        med_behospital_info b
        where
        a.behospitalCode=b.behospital_code
        and b.is_deleted='N'
        <if test="getMedDefectFeedbackPageVO.isPlacefile != null and getMedDefectFeedbackPageVO.isPlacefile != ''">
            and b.is_placefile =#{getMedDefectFeedbackPageVO.isPlacefile}
        </if>
        ) t1
        LEFT JOIN med_home_page t2 ON t2.behospital_code = t1.behospitalCode
        AND t2.is_deleted = 'N'
    </select>

    <select id="getDefectDept" resultType="com.diagbot.dto.GetDefectDeptDTO">
    SELECT
        a.*, b.is_placefile
    FROM
        (
        SELECT
            dept_id as deptId,
            dept_name as deptName,
            behospital_code AS behospitalCode
        FROM
            med_defect_feedback
        WHERE
            is_deleted = 'N'
        AND hospital_id = #{getDefectDeptModeVO.hospitalId}
        GROUP BY
        dept_id,
        dept_name,
		behospital_code
        ) a,
        med_behospital_info b
    WHERE
        a.behospitalCode = b.behospital_code
    AND a.deptId IS NOT NULL
    <if test="getDefectDeptModeVO.isPlacefile != null and getDefectDeptModeVO.isPlacefile != ''">
        and b.is_placefile =#{getDefectDeptModeVO.isPlacefile}
    </if>
    GROUP BY
        a.deptId,
        a.deptName
    </select>

    <select id="getDefectMode" resultType="com.diagbot.dto.GetDefectModeDTO">
    SELECT
        a.*, b.is_placefile
    FROM
        (
        SELECT
            mode_id as modeId,
            mode_name as modeName,
            behospital_code AS behospitalCode
        FROM
            med_defect_feedback
        WHERE
            is_deleted = 'N'
        AND hospital_id = #{getDefectDeptModeVO.hospitalId}
    	GROUP BY
		mode_id,
        mode_name,
		behospital_code
        ) a,
        med_behospital_info b
    WHERE
        a.behospitalCode = b.behospital_code
    AND a.modeId IS NOT NULL
    <if test="getDefectDeptModeVO.isPlacefile != null and getDefectDeptModeVO.isPlacefile != ''">
        and b.is_placefile =#{getDefectDeptModeVO.isPlacefile}
    </if>
    GROUP BY
        a.modeId,
        a.modeName
    </select>

</mapper>
