<?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.MedClickInfoMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.diagbot.entity.MedClickInfo">
        <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="doctor_id" property="doctorId"/>
        <result column="doctor_name" property="doctorName"/>
        <result column="qcresult_info_id" property="qcresultInfoId"/>
        <result column="behospital_code" property="behospitalCode"/>
        <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>

    <resultMap id="ExportExcelMap" type="com.diagbot.dto.ExportExcelDTO">
        <result column="deptName" property="behDeptName"/>
        <result column="avgScore" property="avgScore"/>
        <collection property="excelBehospitalDTOS"
                    ofType="com.diagbot.dto.ExportExcelBehospitalDTO">
            <result column="doctorName" property="doctorName"/>
            <result column="NAME" property="patName"/>
            <result column="behospitalCode" property="behospitalCode"/>
            <result column="behospitalDate" property="behospitalDate"/>
            <result column="leaveHospitalDate" property="leaveHospitalDate"/>
            <result column="scoreRes" property="score"/>
            <result column="scoreBn" property="scoreBn"/>
            <collection property="exportExcelCaseDTOS" ofType="com.diagbot.dto.ExportExcelCaseDTO">
                <result column="casesName" property="caseName"/>
                <collection property="exportExcelMsgDTOS"
                            ofType="com.diagbot.dto.ExportExcelMsgDTO">
                    <result column="entryName" property="msg"/>
                </collection>
            </collection>
        </collection>
    </resultMap>

    <select id="getQcClick" resultType="com.diagbot.dto.GetQcClickDTO">
        select
        t.deptId,
        t.deptName,
        t.clickNum
        from(
        SELECT
        a.dept_id as deptId,
        a.dept_name as deptName,
        count(*) as clickNum
        FROM
        med_click_info a
        where
        a.is_deleted = 'N'
        <if test="hospitalId != null">
            and a.hospital_id = #{hospitalId}
        </if>
        <if test="deptName != null and deptName != ''">
            and a.dept_name like CONCAT('%',#{deptName},'%')
        </if>
        <if test="deptId != null and deptId != ''">
            and a.dept_id= #{deptId}
        </if>
        <if test="startDate != null">
            <![CDATA[ and a.gmt_create >= #{startDate}]]>
        </if>
        <if test="endDate != null">
            <![CDATA[ and a.gmt_create < #{endDate}]]>
        </if>
        group by
        a.dept_id,
        a.dept_name
        )t
        <if test="asc == 'clickNum'">
            order by t.clickNum
        </if>
        <if test="desc =='clickNum'">
            order by t.clickNum desc
        </if>
    </select>

    <select id="getQcClickInnerPage" resultType="com.diagbot.dto.GetQcClickInnerPageDTO">
        select
        t.deptId,
        t.deptName,
        t.doctorId,
        t.doctorName,
        t.clickNum
        from(
        SELECT
        a.dept_id as deptId,
        a.dept_name as deptName,
        a.doctor_id AS doctorId,
        a.doctor_name AS doctorName,
        count(*) as clickNum
        FROM
        med_click_info a
        where
        a.is_deleted = 'N'
        <if test="hospitalId != null">
            and a.hospital_id = #{hospitalId}
        </if>
        <if test="deptName != null and deptName != ''">
            and a.dept_name =#{deptName}
        </if>
        <if test="deptId != null and deptId != ''">
            and a.dept_id= #{deptId}
        </if>
        <if test="doctorId != null and doctorId != ''">
            and a.doctor_id like CONCAT('%',#{doctorId},'%')
        </if>
        <if test="doctorName != null and doctorName != ''">
            and a.doctor_name like CONCAT('%',#{doctorName},'%')
        </if>
        <if test="startDate != null">
            <![CDATA[ and a.gmt_create >= #{startDate}]]>
        </if>
        <if test="endDate != null">
            <![CDATA[ and a.gmt_create < #{endDate}]]>
        </if>
        group by
        a.dept_id,
        a.dept_name,
        a.doctor_id,
        a.doctor_name
       )t
    </select>

    <select id="getEntryDefectImprove" resultType="com.diagbot.dto.GetEntryDefectImproveDTO">
        SELECT
        s.hospitalId,
        s.deptId,
        s.deptName,
        s.casesId,
        s.casesName,
        s.entryName,
        s.entryId,
        sum(s.totalNum) AS totalNum,
        sum(s.improveleNum) AS improveleNum,
        sum(s.totalNum) - sum(s.improveleNum) AS handleNum,
        concat(ROUND((sum(s.totalNum) - sum(s.improveleNum)) / sum(s.totalNum) * 100,2),'%') AS handleStr
        FROM
        (
        SELECT
        f.hospitalId,
        f.deptId,
        f.deptName,
        f.behospitalCode,
        f.casesId,
        f.casesName,
        f.casesEntryName as entryName,
        f.beforeCasesEntryId as entryId,
        count(f.beforeCasesEntryId) AS totalNum,
        CASE
        WHEN count(f.lastCasesEntryId) &gt; 0 AND count(f.beforeCasesEntryId) - count(f.lastCasesEntryId) &lt;= 0 THEN
        count(f.beforeCasesEntryId)
        WHEN count(f.lastCasesEntryId) &gt; 0 AND count(f.beforeCasesEntryId) - count(f.lastCasesEntryId) &gt; 0 THEN
        count(f.beforeCasesEntryId) - count(f.lastCasesEntryId)
        WHEN count(f.lastCasesEntryId) = 0 THEN 0
        END AS improveleNum
        FROM
        (
        SELECT
        k.*, i. NAME AS casesName,
        o. NAME AS casesEntryName,
        y.cases_entry_id AS lastCasesEntryId
        FROM
        (
        SELECT
        e.hospital_id AS hospitalId,
        e.dept_id AS deptId,
        e.dept_name AS deptName,
        e.behospital_code AS behospitalCode,
        e.lastQcresultInfoId,
        t.beforeQcresultInfoId,
        u.cases_id AS casesId,
        u.cases_entry_id AS beforeCasesEntryId
        FROM
        (
        SELECT
        h.hospital_id,
        h.dept_id,
        h.dept_name,
        h.behospital_code,
        h.lastQcresultInfoId
        FROM
        (
        SELECT DISTINCT
        w.hospital_id,
        w.behospital_code
        FROM
        med_click_info w
        WHERE
        w.is_deleted = 'N'
        <if test="hospitalId != null and hospitalId != ''">
            AND w.hospital_id = #{hospitalId}
        </if>
        <if test="startDate != null and startDate != ''">
            <![CDATA[ AND w.gmt_create >= #{startDate}]]>
        </if>
        <if test="endDate != null and endDate != ''">
            <![CDATA[ AND w.gmt_create <= #{endDate}]]>
        </if>
        ) g
        LEFT JOIN (
        SELECT
        q.hospital_id,
        q.beh_dept_id AS dept_id,
        q.beh_dept_name AS dept_name,
        w.behospital_code,
        max(w.qcresult_info_id) AS lastQcresultInfoId
        FROM
        med_behospital_info q,
        med_click_info w
        WHERE
        q.is_deleted = 'N'
        AND w.is_deleted = 'N'
        AND q.hospital_id = w.hospital_id
        AND q.behospital_code = w.behospital_code
        AND q.qc_type_id != 0
        <if test="hospitalId != null and hospitalId != ''">
            AND q.hospital_id = #{hospitalId}
        </if>
        <if test="deptId != null and deptId != ''">
            and q.beh_dept_id = #{deptId}
        </if>
        <if test="deptName != null and deptName != ''">
            and q.beh_dept_name like CONCAT('%',#{deptName},'%')
        </if>
        GROUP BY
        q.beh_dept_id,
        q.beh_dept_name,
        q.behospital_code
        ) h ON g.hospital_id = h.hospital_id
        AND g.behospital_code = h.behospital_code
        ) e
        LEFT JOIN (
        SELECT
        q.hospital_id,
        q.beh_dept_id AS dept_id,
        q.beh_dept_name AS dept_name,
        w.behospital_code,
        min(w.qcresult_info_id) AS beforeQcresultInfoId
        FROM
        med_behospital_info q,
        med_click_info w,
        med_qcresult_detail e
        WHERE
        q.is_deleted = 'N'
        AND w.is_deleted = 'N'
        AND e.is_deleted = 'Y'
        AND q.hospital_id = w.hospital_id
        AND q.hospital_id = e.hospital_id
        AND q.behospital_code = w.behospital_code
        AND w.qcresult_info_id =e.qcresult_info_id
        AND q.qc_type_id != 0
        <if test="hospitalId != null and hospitalId != ''">
            AND q.hospital_id = #{hospitalId}
        </if>
        <if test="deptId != null and deptId != ''">
            and q.beh_dept_id = #{deptId}
        </if>
        <if test="deptName != null and deptName != ''">
            and q.beh_dept_name like CONCAT('%',#{deptName},'%')
        </if>
        GROUP BY
        q.beh_dept_id,
        q.beh_dept_name,
        q.behospital_code
        UNION
        SELECT
        q.hospital_id,
        q.beh_dept_id AS dept_id,
        q.beh_dept_name AS dept_name,
        w.behospital_code,
        min(w.qcresult_info_id) AS beforeQcresultInfoId
        FROM
        med_behospital_info q,
        med_click_info w
        WHERE
        q.is_deleted = 'N'
        AND w.is_deleted = 'N'
        AND q.hospital_id = w.hospital_id
        AND q.behospital_code = w.behospital_code
        AND q.qc_type_id != 0
        <if test="hospitalId != null and hospitalId != ''">
            AND q.hospital_id = #{hospitalId}
        </if>
        <if test="deptId != null and deptId != ''">
            and q.beh_dept_id = #{deptId}
        </if>
        <if test="deptName != null and deptName != ''">
            and q.beh_dept_name like CONCAT('%',#{deptName},'%')
        </if>
        GROUP BY
        q.beh_dept_id,
        q.beh_dept_name,
        q.behospital_code
        HAVING
        count(q.behospital_code) = 1
        ) t ON e.hospital_id = t.hospital_id
        AND e.behospital_code = t.behospital_code
        LEFT JOIN med_qcresult_detail u ON t.hospital_id = u.hospital_id
        AND t.beforeQcresultInfoId = u.qcresult_info_id
        WHERE
        u.grade_type = 1
        ORDER BY
        t.dept_name
        ) k
        LEFT JOIN med_qcresult_detail y ON k.hospitalId = y.hospital_id
        AND k.lastQcresultInfoId = y.qcresult_info_id
        AND k.beforeCasesEntryId = y.cases_entry_id
        LEFT JOIN qc_cases i ON i.id = k.casesId
        AND i.is_deleted = 'N'
        LEFT JOIN qc_cases_entry o ON o.id = k.beforeCasesEntryId
        AND o.is_deleted = 'N'
        WHERE
        (y.grade_type = 1 OR y.cases_entry_id IS NULL)
        <if test="casesName != null and casesName != ''">
            and i.name like CONCAT('%',#{casesName},'%')
        </if>
        <if test="entryName != null and entryName != ''">
            and o.name like CONCAT('%',#{entryName},'%')
        </if>
        ) f
        GROUP BY
        f.deptId,
        f.deptName,
        f.behospitalCode,
        f.casesId,
        f.beforeCasesEntryId
        ) s
        GROUP BY
        s.deptId,
        s.deptName,
        s.casesId,
        s.entryId
    </select>

    <select id="getEntryDefectImproveInner" resultType="com.diagbot.dto.BehospitalInfoDTO">
        SELECT distinct
        y.hospitalId,
        y.deptId,
        y.deptName,
        y.fileCode,
        y.behospitalCode,
        y. NAME,
        y.sex,
        y.bedCode,
        y.behospitalDate,
        y.leaveHospitalDate,
        y.placefileDate,
        y.diagnose,
        y.doctorId,
        y.doctorName,
        y.behDoctorId,
        y.behDoctorName,
        y.directorDoctorId,
        y.directorDoctorName,
        y.wardCode,
        y.wardName,
        y.`level`,
        y.gradeType,
        y.scoreRes,
        y.gradeTime,
        y.age,
        y.scoreBn,
        y.checkStatus,
        y.chType,
        y.chName,
        y.chTime
        from(
        SELECT
        s.hospitalId,
        s.deptId,
        s.deptName,
        s.fileCode,
        s.behospitalCode,
        s. NAME,
        s.sex,
        s.bedCode,
        s.behospitalDate,
        s.leaveHospitalDate,
        s.placefileDate,
        s.diagnose,
        s.doctorId,
        s.doctorName,
        s.behDoctorId,
        s.behDoctorName,
        s.directorDoctorId,
        s.directorDoctorName,
        s.wardCode,
        s.wardName,
        s.lastQcresultInfoId,
        s.`level`,
        s.gradeType,
        s.scoreRes,
        s.gradeTime,
        s.age,
        s.scoreBn,
        s.checkStatus,
        s.chType,
        s.chName,
        s.chTime,
        s.casesId,
        s.casesName,
        s.entryName,
        s.beforeCasesEntryId AS entryId,
        sum(s.totalNum) AS totalNum,
        sum(s.improveleNum) AS improveleNum,
        sum(s.totalNum) - sum(s.improveleNum) AS handleNum
        FROM
        (
        SELECT
        f.*,
        count(f.beforeCasesEntryId) AS totalNum,
        CASE
        WHEN count(f.lastCasesEntryId) &gt; 0 AND count(f.beforeCasesEntryId) - count(f.lastCasesEntryId) &lt;= 0 THEN
        count(f.beforeCasesEntryId)
        WHEN count(f.lastCasesEntryId) &gt; 0 AND count(f.beforeCasesEntryId) - count(f.lastCasesEntryId) &gt; 0 THEN
        count(f.beforeCasesEntryId) - count(f.lastCasesEntryId)
        WHEN count(f.lastCasesEntryId) = 0 THEN 0
        END AS improveleNum
        FROM
        (
        SELECT
        k.*, i. NAME AS casesName,
        o. NAME AS entryName,
        y.cases_entry_id AS lastCasesEntryId
        FROM
        (
        SELECT
        e.*, t.beforeQcresultInfoId,
        u.cases_id AS casesId,
        u.cases_entry_id AS beforeCasesEntryId
        FROM
        (
        SELECT
        h.*
        FROM
        (
        SELECT DISTINCT
        w.hospital_id,
        w.behospital_code
        FROM
        med_click_info w
        WHERE
        w.is_deleted = 'N'
        <if test="hospitalId != null and hospitalId != ''">
            AND w.hospital_id = #{hospitalId}
        </if>
        <if test="startDate != null and startDate != ''">
            <![CDATA[ AND w.gmt_create >= #{startDate}]]>
        </if>
        <if test="endDate != null and endDate != ''">
            <![CDATA[ AND w.gmt_create <= #{endDate}]]>
        </if>
        ) g
        LEFT JOIN (
        SELECT
        v.hospital_id AS hospitalId,
        v.beh_dept_id AS deptId,
        v.beh_dept_name AS deptName,
        v.file_code AS fileCode,
        v.behospital_code AS behospitalCode,
        v. NAME,
        v.sex,
        v.bed_code AS bedCode,
        v.behospital_date AS behospitalDate,
        v.leave_hospital_date AS leaveHospitalDate,
        v.placefile_date AS placefileDate,
        v.diagnose AS diagnose,
        v.doctor_id AS doctorId,
        v.doctor_name AS doctorName,
        v.beh_doctor_id AS behDoctorId,
        v.beh_doctor_name AS behDoctorName,
        v.director_doctor_id AS directorDoctorId,
        v.director_doctor_name AS directorDoctorName,
        v.ward_code AS wardCode,
        v.ward_name AS wardName,
        v.lastQcresultInfoId,
        ifnull(p. LEVEL, '未评分') AS `level`,
        p.grade_type AS gradeType,
        p.score_res AS scoreRes,
        p.gmt_create AS gradeTime,
        IF ( n.age IS NULL,NULL,CONCAT(ifnull(n.age, ''),ifnull(n.age_unit, ''))) AS age,
        r.score_res AS scoreBn,
        CASE WHEN ISNULL(j. STATUS) THEN 0 ELSE j.STATUS END checkStatus,
        j.check_type AS chType,
        j.check_name AS chName,
        j.check_time AS chTime
        FROM
        (
        SELECT
        q.*,
         max(w.qcresult_info_id) AS lastQcresultInfoId
        FROM
        med_behospital_info q,
        med_click_info w
        WHERE
        q.is_deleted = 'N'
        AND w.is_deleted = 'N'
        AND q.hospital_id = w.hospital_id
        AND q.behospital_code = w.behospital_code
        AND q.qc_type_id != 0
        <if test="hospitalId != null and hospitalId != ''">
            AND q.hospital_id = #{hospitalId}
        </if>
        <if test="deptId != null and deptId != ''">
            and q.beh_dept_id = #{deptId}
        </if>
        <if test="deptName != null and deptName != ''">
            and q.beh_dept_name like CONCAT('%',#{deptName},'%')
        </if>
        <if test="diagnose != null and diagnose != ''">
            AND q.diagnose LIKE CONCAT( '%', #{diagnose}, '%' )
        </if>
        <if test="doctorId != null and doctorId != ''">
            and q.doctor_id = #{doctor_id}
        </if>
        <if test="doctorName != null and doctorName != ''">
            and q.doctor_name like CONCAT('%',#{doctorName},'%')
        </if>
        <if test="patName != null and patName != ''">
            and q.name like CONCAT('%',#{patName},'%')
        </if>
        <if test="behospitalCode != null and behospitalCode != ''">
            and q.behospital_code like CONCAT('%',#{behospitalCode},'%')
        </if>
        GROUP BY
        q.beh_dept_id,
        q.beh_dept_name,
        q.behospital_code
        ) v
        LEFT JOIN med_qcresult_info p ON v.hospital_id = p.hospital_id
        AND v.behospital_code = p.behospital_code
        AND p.is_deleted = 'N'
        LEFT JOIN med_home_page n ON v.hospital_id = n.hospital_id
        AND v.behospital_code = n.behospital_code
        AND n.is_deleted = 'N'
        LEFT JOIN med_qcresult_cases r ON v.hospital_id = r.hospital_id
        AND v.behospital_code = r.behospital_code
        AND r.is_deleted = 'N'
        AND r.cases_id = 243
        LEFT JOIN (
        SELECT
        *
        FROM
        med_check_info
        WHERE
        is_deleted = 'N'
        AND check_type IN (0, 2)
        ) j ON v.behospital_code = j.behospital_code
        AND v.hospital_id = j.hospital_id
        where 1=1
        <if test="level != null and level != ''">
            and p.level = #{level}
        </if>
        <if test="checkStatus != null ">
            and j.check_status = #{checkStatus}
        </if>

        <if test="chName != null and chName !=''">
            AND j.ch_name like CONCAT('%',#{chName},'%')
        </if>
        <if test="chTimeStart != null">
            <![CDATA[ and j.ch_time >= #{chTimeStart}]]>
        </if>
        <if test="chTimeEnd != null">
            <![CDATA[ and j.ch_time < #{chTimeEnd}]]>
        </if>
        ) h ON g.hospital_id = h.hospitalId
        AND g.behospital_code = h.behospitalCode
        ) e
        LEFT JOIN (
        SELECT
        q.hospital_id,
        q.beh_dept_id AS dept_id,
        q.beh_dept_name AS dept_name,
        w.behospital_code,
        min(w.qcresult_info_id) AS beforeQcresultInfoId
        FROM
        med_behospital_info q,
        med_click_info w,
        med_qcresult_detail e
        WHERE
        q.is_deleted = 'N'
        AND w.is_deleted = 'N'
        AND e.is_deleted = 'Y'
        AND q.hospital_id = w.hospital_id
        AND q.hospital_id = e.hospital_id
        AND q.behospital_code = w.behospital_code
        AND w.qcresult_info_id = e.qcresult_info_id
        AND q.qc_type_id != 0
        <if test="hospitalId != null and hospitalId != ''">
            AND q.hospital_id = #{hospitalId}
        </if>
        <if test="deptId != null and deptId != ''">
            and q.beh_dept_id = #{deptId}
        </if>
        <if test="deptName != null and deptName != ''">
            and q.beh_dept_name like CONCAT('%',#{deptName},'%')
        </if>
        <if test="diagnose != null and diagnose != ''">
            AND q.diagnose LIKE CONCAT( '%', #{diagnose}, '%' )
        </if>
        <if test="doctorId != null and doctorId != ''">
            and q.doctor_id = #{doctor_id}
        </if>
        <if test="doctorName != null and doctorName != ''">
            and q.doctor_name like CONCAT('%',#{doctorName},'%')
        </if>
        <if test="patName != null and patName != ''">
            and q.name like CONCAT('%',#{patName},'%')
        </if>
        <if test="behospitalCode != null and behospitalCode != ''">
            and q.behospital_code like CONCAT('%',#{behospitalCode},'%')
        </if>
        GROUP BY
        q.beh_dept_id,
        q.beh_dept_name,
        q.behospital_code
        UNION
        SELECT
        q.hospital_id,
        q.beh_dept_id AS dept_id,
        q.beh_dept_name AS dept_name,
        w.behospital_code,
        min(w.qcresult_info_id) AS beforeQcresultInfoId
        FROM
        med_behospital_info q,
        med_click_info w
        WHERE
        q.is_deleted = 'N'
        AND w.is_deleted = 'N'
        AND q.hospital_id = w.hospital_id
        AND q.behospital_code = w.behospital_code
        AND q.qc_type_id != 0
        <if test="hospitalId != null and hospitalId != ''">
            AND q.hospital_id = #{hospitalId}
        </if>
        <if test="deptId != null and deptId != ''">
            and q.beh_dept_id = #{deptId}
        </if>
        <if test="deptName != null and deptName != ''">
            and q.beh_dept_name like CONCAT('%',#{deptName},'%')
        </if>
        <if test="diagnose != null and diagnose != ''">
            AND q.diagnose LIKE CONCAT( '%', #{diagnose}, '%' )
        </if>
        <if test="doctorId != null and doctorId != ''">
            and q.doctor_id = #{doctor_id}
        </if>
        <if test="doctorName != null and doctorName != ''">
            and q.doctor_name like CONCAT('%',#{doctorName},'%')
        </if>
        <if test="patName != null and patName != ''">
            and q.name like CONCAT('%',#{patName},'%')
        </if>
        <if test="behospitalCode != null and behospitalCode != ''">
            and q.behospital_code like CONCAT('%',#{behospitalCode},'%')
        </if>
        GROUP BY
        q.beh_dept_id,
        q.beh_dept_name,
        q.behospital_code
        HAVING
        count(q.behospital_code) = 1
        ) t ON e.hospitalId = t.hospital_id
        AND e.behospitalCode = t.behospital_code
        LEFT JOIN med_qcresult_detail u ON t.hospital_id = u.hospital_id
        AND t.beforeQcresultInfoId = u.qcresult_info_id
        WHERE
        u.grade_type = 1
        ORDER BY
        t.dept_name
        ) k
        LEFT JOIN med_qcresult_detail y ON k.hospitalId = y.hospital_id
        AND k.lastQcresultInfoId = y.qcresult_info_id
        AND k.beforeCasesEntryId = y.cases_entry_id
        LEFT JOIN qc_cases i ON i.id = k.casesId
        AND i.is_deleted = 'N'
        LEFT JOIN qc_cases_entry o ON o.id = k.beforeCasesEntryId
        AND o.is_deleted = 'N'
        WHERE
        (y.grade_type = 1 OR y.cases_entry_id IS NULL)
        <if test="casesName != null and casesName != ''">
            and i.name like CONCAT('%',#{casesName},'%')
        </if>
        <if test="entryName != null and entryName != ''">
            and o.name like CONCAT('%',#{entryName},'%')
        </if>
        ) f
        GROUP BY
        f.deptId,
        f.deptName,
        f.behospitalCode,
        f.casesId,
        f.beforeCasesEntryId
        ) s
        GROUP BY
        s.deptId,
        s.deptName,
        s.casesId,
        s.behospitalCode,
        s.beforeCasesEntryId)y
        where
        1=1
        <if test="typeMark != null and typeMark !='' and typeMark == 0">
            AND y.totalNum = 1
        </if>
        <if test="typeMark != null and typeMark !='' and typeMark == 1">
            AND y.handleNum = 1
        </if>
        <if test="typeMark != null and typeMark !='' and typeMark == 2">
            AND y.improveleNum = 1
        </if>
    </select>
    <select id="getDefectImproveInnerByExport" resultMap="ExportExcelMap">
        SELECT y.* from(
        SELECT
        s.hospitalId,
        s.deptId,
        s.deptName,
        s.fileCode,
        s.behospitalCode,
        s. NAME,
        s.sex,
        s.bedCode,
        s.behospitalDate,
        s.leaveHospitalDate,
        s.placefileDate,
        s.diagnose,
        s.doctorId,
        s.doctorName,
        s.behDoctorId,
        s.behDoctorName,
        s.directorDoctorId,
        s.directorDoctorName,
        s.wardCode,
        s.wardName,
        s.lastQcresultInfoId,
        s.`level`,
        s.gradeType,
        s.scoreRes,
        s.gradeTime,
        s.age,
        s.scoreBn,
        s.checkStatus,
        s.chType,
        s.chName,
        s.chTime,
        s.casesId,
        s.casesName,
        s.entryName,
        s.beforeCasesEntryId AS entryId,
        sum(s.totalNum) AS totalNum,
        sum(s.improveleNum) AS improveleNum,
        sum(s.totalNum) - sum(s.improveleNum) AS handleNum
        FROM
        (
        SELECT
        f.*,
        count(f.beforeCasesEntryId) AS totalNum,
        CASE
        WHEN count(f.lastCasesEntryId) &gt; 0 AND count(f.beforeCasesEntryId) - count(f.lastCasesEntryId) &lt;= 0 THEN
        count(f.beforeCasesEntryId)
        WHEN count(f.lastCasesEntryId) &gt; 0 AND count(f.beforeCasesEntryId) - count(f.lastCasesEntryId) &gt; 0 THEN
        count(f.beforeCasesEntryId) - count(f.lastCasesEntryId)
        WHEN count(f.lastCasesEntryId) = 0 THEN 0
        END AS improveleNum
        FROM
        (
        SELECT
        k.*, i. NAME AS casesName,
        o. NAME AS entryName,
        y.cases_entry_id AS lastCasesEntryId
        FROM
        (
        SELECT
        e.*, t.beforeQcresultInfoId,
        u.cases_id AS casesId,
        u.cases_entry_id AS beforeCasesEntryId
        FROM
        (
        SELECT
        h.*
        FROM
        (
        SELECT DISTINCT
        w.hospital_id,
        w.behospital_code
        FROM
        med_click_info w
        WHERE
        w.is_deleted = 'N'
        <if test="hospitalId != null and hospitalId != ''">
            AND w.hospital_id = #{hospitalId}
        </if>
        <if test="startDate != null and startDate != ''">
            <![CDATA[ AND w.gmt_create >= #{startDate}]]>
        </if>
        <if test="endDate != null and endDate != ''">
            <![CDATA[ AND w.gmt_create <= #{endDate}]]>
        </if>
        ) g
        LEFT JOIN (
        SELECT
        v.hospital_id AS hospitalId,
        v.beh_dept_id AS deptId,
        v.beh_dept_name AS deptName,
        v.file_code AS fileCode,
        v.behospital_code AS behospitalCode,
        v. NAME,
        v.sex,
        v.bed_code AS bedCode,
        v.behospital_date AS behospitalDate,
        v.leave_hospital_date AS leaveHospitalDate,
        v.placefile_date AS placefileDate,
        v.diagnose AS diagnose,
        v.doctor_id AS doctorId,
        v.doctor_name AS doctorName,
        v.beh_doctor_id AS behDoctorId,
        v.beh_doctor_name AS behDoctorName,
        v.director_doctor_id AS directorDoctorId,
        v.director_doctor_name AS directorDoctorName,
        v.ward_code AS wardCode,
        v.ward_name AS wardName,
        v.lastQcresultInfoId,
        ifnull(p. LEVEL, '未评分') AS `level`,
        p.grade_type AS gradeType,
        p.score_res AS scoreRes,
        p.gmt_create AS gradeTime,
        IF ( n.age IS NULL,NULL,CONCAT(ifnull(n.age, ''),ifnull(n.age_unit, ''))) AS age,
        r.score_res AS scoreBn,
        CASE WHEN ISNULL(j. STATUS) THEN 0 ELSE j.STATUS END checkStatus,
        j.check_type AS chType,
        j.check_name AS chName,
        j.check_time AS chTime
        FROM
        (
        SELECT
        q.*,
        max(w.qcresult_info_id) AS lastQcresultInfoId
        FROM
        med_behospital_info q,
        med_click_info w
        WHERE
        q.is_deleted = 'N'
        AND w.is_deleted = 'N'
        AND q.hospital_id = w.hospital_id
        AND q.behospital_code = w.behospital_code
        AND q.qc_type_id != 0
        <if test="hospitalId != null and hospitalId != ''">
            AND q.hospital_id = #{hospitalId}
        </if>
        <if test="deptId != null and deptId != ''">
            and q.beh_dept_id = #{deptId}
        </if>
        <if test="deptName != null and deptName != ''">
            and q.beh_dept_name like CONCAT('%',#{deptName},'%')
        </if>
        <if test="diagnose != null and diagnose != ''">
            AND q.diagnose LIKE CONCAT( '%', #{diagnose}, '%' )
        </if>
        <if test="doctorId != null and doctorId != ''">
            and q.doctor_id = #{doctor_id}
        </if>
        <if test="doctorName != null and doctorName != ''">
            and q.doctor_name like CONCAT('%',#{doctorName},'%')
        </if>
        <if test="patName != null and patName != ''">
            and q.name like CONCAT('%',#{patName},'%')
        </if>
        <if test="behospitalCode != null and behospitalCode != ''">
            and q.behospital_code like CONCAT('%',#{behospitalCode},'%')
        </if>
        GROUP BY
        q.beh_dept_id,
        q.beh_dept_name,
        q.behospital_code
        ) v
        LEFT JOIN med_qcresult_info p ON v.hospital_id = p.hospital_id
        AND v.behospital_code = p.behospital_code
        AND p.is_deleted = 'N'
        LEFT JOIN med_home_page n ON v.hospital_id = n.hospital_id
        AND v.behospital_code = n.behospital_code
        AND n.is_deleted = 'N'
        LEFT JOIN med_qcresult_cases r ON v.hospital_id = r.hospital_id
        AND v.behospital_code = r.behospital_code
        AND r.is_deleted = 'N'
        AND r.cases_id = 243
        LEFT JOIN (
        SELECT
        *
        FROM
        med_check_info
        WHERE
        is_deleted = 'N'
        AND check_type IN (0, 2)
        ) j ON v.behospital_code = j.behospital_code
        AND v.hospital_id = j.hospital_id
        where 1=1
        <if test="level != null and level != ''">
            and p.level = #{level}
        </if>
        <if test="checkStatus != null ">
            and j.check_status = #{checkStatus}
        </if>

        <if test="chName != null and chName !=''">
            AND j.ch_name like CONCAT('%',#{chName},'%')
        </if>
        <if test="chTimeStart != null">
            <![CDATA[ and j.ch_time >= #{chTimeStart}]]>
        </if>
        <if test="chTimeEnd != null">
            <![CDATA[ and j.ch_time < #{chTimeEnd}]]>
        </if>
        ) h ON g.hospital_id = h.hospitalId
        AND g.behospital_code = h.behospitalCode
        ) e
        LEFT JOIN (
        SELECT
        q.hospital_id,
        q.beh_dept_id AS dept_id,
        q.beh_dept_name AS dept_name,
        w.behospital_code,
        min(w.qcresult_info_id) AS beforeQcresultInfoId
        FROM
        med_behospital_info q,
        med_click_info w,
        med_qcresult_detail e
        WHERE
        q.is_deleted = 'N'
        AND w.is_deleted = 'N'
        AND e.is_deleted = 'Y'
        AND q.hospital_id = w.hospital_id
        AND q.hospital_id = e.hospital_id
        AND q.behospital_code = w.behospital_code
        AND w.qcresult_info_id = e.qcresult_info_id
        AND q.qc_type_id != 0
        <if test="hospitalId != null and hospitalId != ''">
            AND q.hospital_id = #{hospitalId}
        </if>
        <if test="deptId != null and deptId != ''">
            and q.beh_dept_id = #{deptId}
        </if>
        <if test="deptName != null and deptName != ''">
            and q.beh_dept_name like CONCAT('%',#{deptName},'%')
        </if>
        <if test="diagnose != null and diagnose != ''">
            AND q.diagnose LIKE CONCAT( '%', #{diagnose}, '%' )
        </if>
        <if test="doctorId != null and doctorId != ''">
            and q.doctor_id = #{doctor_id}
        </if>
        <if test="doctorName != null and doctorName != ''">
            and q.doctor_name like CONCAT('%',#{doctorName},'%')
        </if>
        <if test="patName != null and patName != ''">
            and q.name like CONCAT('%',#{patName},'%')
        </if>
        <if test="behospitalCode != null and behospitalCode != ''">
            and q.behospital_code like CONCAT('%',#{behospitalCode},'%')
        </if>
        GROUP BY
        q.beh_dept_id,
        q.beh_dept_name,
        q.behospital_code
        UNION
        SELECT
        q.hospital_id,
        q.beh_dept_id AS dept_id,
        q.beh_dept_name AS dept_name,
        w.behospital_code,
        min(w.qcresult_info_id) AS beforeQcresultInfoId
        FROM
        med_behospital_info q,
        med_click_info w
        WHERE
        q.is_deleted = 'N'
        AND w.is_deleted = 'N'
        AND q.hospital_id = w.hospital_id
        AND q.behospital_code = w.behospital_code
        AND q.qc_type_id != 0
        <if test="hospitalId != null and hospitalId != ''">
            AND q.hospital_id = #{hospitalId}
        </if>
        <if test="deptId != null and deptId != ''">
            and q.beh_dept_id = #{deptId}
        </if>
        <if test="deptName != null and deptName != ''">
            and q.beh_dept_name like CONCAT('%',#{deptName},'%')
        </if>
        <if test="diagnose != null and diagnose != ''">
            AND q.diagnose LIKE CONCAT( '%', #{diagnose}, '%' )
        </if>
        <if test="doctorId != null and doctorId != ''">
            and q.doctor_id = #{doctor_id}
        </if>
        <if test="doctorName != null and doctorName != ''">
            and q.doctor_name like CONCAT('%',#{doctorName},'%')
        </if>
        <if test="patName != null and patName != ''">
            and q.name like CONCAT('%',#{patName},'%')
        </if>
        <if test="behospitalCode != null and behospitalCode != ''">
            and q.behospital_code like CONCAT('%',#{behospitalCode},'%')
        </if>
        GROUP BY
        q.beh_dept_id,
        q.beh_dept_name,
        q.behospital_code
        HAVING
        count(q.behospital_code) = 1
        ) t ON e.hospitalId = t.hospital_id
        AND e.behospitalCode = t.behospital_code
        LEFT JOIN med_qcresult_detail u ON t.hospital_id = u.hospital_id
        AND t.beforeQcresultInfoId = u.qcresult_info_id
        WHERE
        u.grade_type = 1
        ORDER BY
        t.dept_name
        ) k
        LEFT JOIN med_qcresult_detail y ON k.hospitalId = y.hospital_id
        AND k.lastQcresultInfoId = y.qcresult_info_id
        AND k.beforeCasesEntryId = y.cases_entry_id
        LEFT JOIN qc_cases i ON i.id = k.casesId
        AND i.is_deleted = 'N'
        LEFT JOIN qc_cases_entry o ON o.id = k.beforeCasesEntryId
        AND o.is_deleted = 'N'
        WHERE
        (y.grade_type = 1 OR y.cases_entry_id IS NULL)
        <if test="casesName != null and casesName != ''">
            and i.name like CONCAT('%',#{casesName},'%')
        </if>
        <if test="entryName != null and entryName != ''">
            and o.name like CONCAT('%',#{entryName},'%')
        </if>
        ) f
        GROUP BY
        f.deptId,
        f.deptName,
        f.behospitalCode,
        f.casesId,
        f.beforeCasesEntryId
        ) s
        GROUP BY
        s.deptId,
        s.deptName,
        s.casesId,
        s.behospitalCode,
        s.beforeCasesEntryId)y
        where
        1=1
        <if test="typeMark != null and typeMark !='' and typeMark == 0">
            AND y.totalNum = 1
        </if>
        <if test="typeMark != null and typeMark !='' and typeMark == 1">
            AND y.handleNum = 1
        </if>
        <if test="typeMark != null and typeMark !='' and typeMark == 2">
            AND y.improveleNum = 1
        </if>
    </select>

    <select id="getEntryDefectImproveDept" resultType="com.diagbot.dto.GetEntryDefectImproveDeptDTO">
        SELECT
        s.hospitalId,
        s.deptId,
        s.deptName,
        s.doctorId,
        s.doctorName,
        s.casesId,
        s.casesName,
        s.entryName,
        s.entryId,
        sum(s.totalNum) AS totalNum,
        sum(s.improveleNum) AS improveleNum,
        sum(s.totalNum) - sum(s.improveleNum) AS handleNum,
        concat(ROUND((sum(s.totalNum) - sum(s.improveleNum)) / sum(s.totalNum) * 100,2),'%') AS handleStr
        FROM
        (
        SELECT
        f.hospitalId,
        f.deptId,
        f.deptName,
        f.doctorId,
        f.doctorName,
        f.behospitalCode,
        f.casesId,
        f.casesName,
        f.casesEntryName as entryName,
        f.beforeCasesEntryId as entryId,
        count(f.beforeCasesEntryId) AS totalNum,
        CASE
        WHEN count(f.lastCasesEntryId) &gt; 0 AND count(f.beforeCasesEntryId) - count(f.lastCasesEntryId) &lt;= 0 THEN
        count(f.beforeCasesEntryId)
        WHEN count(f.lastCasesEntryId) &gt; 0 AND count(f.beforeCasesEntryId) - count(f.lastCasesEntryId) &gt; 0 THEN
        count(f.beforeCasesEntryId) - count(f.lastCasesEntryId)
        WHEN count(f.lastCasesEntryId) = 0 THEN 0
        END AS improveleNum
        FROM
        (
        SELECT
        k.*, i. NAME AS casesName,
        o. NAME AS casesEntryName,
        y.cases_entry_id AS lastCasesEntryId
        FROM
        (
        SELECT
        e.hospital_id AS hospitalId,
        e.dept_id AS deptId,
        e.dept_name AS deptName,
        e.doctor_id AS doctorId,
        e.doctor_name AS doctorName,
        e.behospital_code AS behospitalCode,
        e.lastQcresultInfoId,
        t.beforeQcresultInfoId,
        u.cases_id AS casesId,
        u.cases_entry_id AS beforeCasesEntryId
        FROM
        (
        SELECT
        h.hospital_id,
        h.dept_id,
        h.dept_name,
        h.doctor_id,
        h.doctor_name,
        h.behospital_code,
        h.lastQcresultInfoId
        FROM
        (
        SELECT DISTINCT
        w.hospital_id,
        w.behospital_code
        FROM
        med_click_info w
        WHERE
        w.is_deleted = 'N'
        <if test="hospitalId != null and hospitalId != ''">
            AND w.hospital_id = #{hospitalId}
        </if>
        <if test="startDate != null and startDate != ''">
            <![CDATA[ AND w.gmt_create >= #{startDate}]]>
        </if>
        <if test="endDate != null and endDate != ''">
            <![CDATA[ AND w.gmt_create <= #{endDate}]]>
        </if>
        ) g
        LEFT JOIN (
        SELECT
        q.hospital_id,
        q.beh_dept_id AS dept_id,
        q.beh_dept_name AS dept_name,
        q.doctor_id,
        q.doctor_name,
        w.behospital_code,
        max(w.qcresult_info_id) AS lastQcresultInfoId
        FROM
        med_behospital_info q,
        med_click_info w
        WHERE
        q.is_deleted = 'N'
        AND w.is_deleted = 'N'
        AND q.hospital_id = w.hospital_id
        AND q.behospital_code = w.behospital_code
        AND q.qc_type_id != 0
        <if test="hospitalId != null and hospitalId != ''">
            AND q.hospital_id = #{hospitalId}
        </if>
        <if test="deptId != null and deptId != ''">
            and q.beh_dept_id = #{deptId}
        </if>
        <if test="deptName != null and deptName != ''">
            and q.beh_dept_name like CONCAT('%',#{deptName},'%')
        </if>
        <if test="doctorId != null and doctorId != ''">
            and q.doctor_id =,#{doctorId}
        </if>
        <if test="doctorName != null and doctorName != ''">
            and q.doctor_name like CONCAT('%',#{doctorName},'%')
        </if>
        GROUP BY
        q.beh_dept_id,
        q.beh_dept_name,
        q.behospital_code
        ) h ON g.hospital_id = h.hospital_id
        AND g.behospital_code = h.behospital_code
        ) e
        LEFT JOIN (
        SELECT
        q.hospital_id,
        q.beh_dept_id AS dept_id,
        q.beh_dept_name AS dept_name,
        w.behospital_code,
        min(w.qcresult_info_id) AS beforeQcresultInfoId
        FROM
        med_behospital_info q,
        med_click_info w,
        med_qcresult_detail e
        WHERE
        q.is_deleted = 'N'
        AND w.is_deleted = 'N'
        AND e.is_deleted = 'Y'
        AND q.hospital_id = w.hospital_id
        AND q.hospital_id = e.hospital_id
        AND q.behospital_code = w.behospital_code
        AND w.qcresult_info_id =e.qcresult_info_id
        AND q.qc_type_id != 0
        <if test="hospitalId != null and hospitalId != ''">
            AND q.hospital_id = #{hospitalId}
        </if>
        <if test="deptId != null and deptId != ''">
            and q.beh_dept_id = #{deptId}
        </if>
        <if test="deptName != null and deptName != ''">
            and q.beh_dept_name like CONCAT('%',#{deptName},'%')
        </if>
        <if test="doctorId != null and doctorId != ''">
            and q.doctor_id =,#{doctorId}
        </if>
        <if test="doctorName != null and doctorName != ''">
            and q.doctor_name like CONCAT('%',#{doctorName},'%')
        </if>
        GROUP BY
        q.beh_dept_id,
        q.beh_dept_name,
        q.behospital_code
        UNION
        SELECT
        q.hospital_id,
        q.beh_dept_id AS dept_id,
        q.beh_dept_name AS dept_name,
        w.behospital_code,
        min(w.qcresult_info_id) AS beforeQcresultInfoId
        FROM
        med_behospital_info q,
        med_click_info w
        WHERE
        q.is_deleted = 'N'
        AND w.is_deleted = 'N'
        AND q.hospital_id = w.hospital_id
        AND q.behospital_code = w.behospital_code
        AND q.qc_type_id != 0
        <if test="hospitalId != null and hospitalId != ''">
            AND q.hospital_id = #{hospitalId}
        </if>
        <if test="deptId != null and deptId != ''">
            and q.beh_dept_id = #{deptId}
        </if>
        <if test="deptName != null and deptName != ''">
            and q.beh_dept_name like CONCAT('%',#{deptName},'%')
        </if>
        <if test="doctorId != null and doctorId != ''">
            and q.doctor_id =,#{doctorId}
        </if>
        <if test="doctorName != null and doctorName != ''">
            and q.doctor_name like CONCAT('%',#{doctorName},'%')
        </if>
        GROUP BY
        q.beh_dept_id,
        q.beh_dept_name,
        q.behospital_code
        HAVING
        count(q.behospital_code) = 1
        ) t ON e.hospital_id = t.hospital_id
        AND e.behospital_code = t.behospital_code
        LEFT JOIN med_qcresult_detail u ON t.hospital_id = u.hospital_id
        AND t.beforeQcresultInfoId = u.qcresult_info_id
        WHERE
        u.grade_type = 1
        ORDER BY
        t.dept_name
        ) k
        LEFT JOIN med_qcresult_detail y ON k.hospitalId = y.hospital_id
        AND k.lastQcresultInfoId = y.qcresult_info_id
        AND k.beforeCasesEntryId = y.cases_entry_id
        LEFT JOIN qc_cases i ON i.id = k.casesId
        AND i.is_deleted = 'N'
        LEFT JOIN qc_cases_entry o ON o.id = k.beforeCasesEntryId
        AND o.is_deleted = 'N'
        WHERE
        (y.grade_type = 1 OR y.cases_entry_id IS NULL)
        <if test="casesName != null and casesName != ''">
            and i.name like CONCAT('%',#{casesName},'%')
        </if>
        <if test="entryName != null and entryName != ''">
            and o.name like CONCAT('%',#{entryName},'%')
        </if>
        ) f
        GROUP BY
        f.deptId,
        f.deptName,
        f.behospitalCode,
        f.casesId,
        f.beforeCasesEntryId
        ) s
        GROUP BY
        s.deptId,
        s.deptName,
        s.doctorId,
        s.doctorName,
        s.casesId,
        s.entryId
    </select>
</mapper>
