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

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.diagbot.entity.QcresultInfo">
        <id column="id" property="id" />
        <result column="behospital_code" property="behospitalCode" />
        <result column="hospital_id" property="hospitalId" />
        <result column="level" property="level" />
        <result column="score_res" property="scoreRes" />
        <result column="grade_type" property="gradeType" />
        <result column="have_home_page" property="haveHomePage" />
        <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" />
        <result column="remark" property="remark" />
    </resultMap>

    <!-- 病历数统计 -->
    <select id="getQcresultSelectively"  parameterType="com.diagbot.vo.QcresultFilterVO" resultType="int">
        SELECT
        count(1)
        FROM
        med_behospital_info a,
        med_qcresult_info c
        WHERE
        a.hospital_id = c.hospital_id
        AND a.behospital_code = c.behospital_code
        AND a.is_deleted = 'N'
        AND c.is_deleted = 'N'
        <![CDATA[AND a.qc_type_id <>0 ]]>
        <if test="hospitalId != null and hospitalId != ''">
            AND a.hospital_id = #{hospitalId}
        </if>
        <if test="isPlacefile != null and isPlacefile == 0">
            <if test="startDate != null and startDate != ''">
                <![CDATA[ AND a.behospital_date >= #{startDate}]]>
            </if>
            <if test="endDate != null and endDate != ''">
                <![CDATA[ AND a.behospital_date <= #{endDate}]]>
            </if>
        </if>
        <if test="isPlacefile != null and isPlacefile == 1">
            <if test="startDate != null and startDate != ''">
                <![CDATA[ AND a.leave_hospital_date >= #{startDate}]]>
            </if>
            <if test="endDate != null and endDate != ''">
                <![CDATA[ AND a.leave_hospital_date <= #{endDate}]]>
            </if>
        </if>
        <if test="gradeType != null and gradeType != ''">
            AND c.grade_type = #{gradeType}
        </if>
        <if test="level != null and level != ''">
            AND c.level = #{level}
        </if>
    </select>

    <!-- 质控病历数统计 -->
    <select id="resultCount" parameterType="com.diagbot.vo.QcresultFilterVO" resultType="int">
        SELECT
        COUNT(*)
        FROM
        med_behospital_info a,
        med_qcresult_info c
        WHERE
        a.is_deleted = 'N'
        AND c.is_deleted = 'N'
        AND a.hospital_id = c.hospital_id
        AND a.behospital_code = c.behospital_code
        <if test="isPlacefile != null and isPlacefile != ''">
            and a.is_placefile = #{isPlacefile}
        </if>
        <![CDATA[AND a.qc_type_id <>0 ]]>
        <if test="hospitalId != null and hospitalId != ''">
            AND a.hospital_id = #{hospitalId}
        </if>
        <if test="isPlacefile != null and isPlacefile == 0">
            <if test="startDate != null and startDate != ''">
                <![CDATA[ AND a.behospital_date >= #{startDate}]]>
            </if>
            <if test="endDate != null and endDate != ''">
                <![CDATA[ AND a.behospital_date <= #{endDate}]]>
            </if>
        </if>
        <if test="isPlacefile != null and isPlacefile == 1">
            <if test="startDate != null and startDate != ''">
                <![CDATA[ AND a.leave_hospital_date >= #{startDate}]]>
            </if>
            <if test="endDate != null and endDate != ''">
                <![CDATA[ AND a.leave_hospital_date <= #{endDate}]]>
            </if>
        </if>
        <if test="gradeType != null and gradeType != ''">
            AND c.grade_type = #{gradeType}
        </if>
        <if test="level != null and level != ''">
            AND c.level = #{level}
        </if>
    </select>

    <!-- 按科室统计质控病历数 -->
    <select id="levelPercentGroupByDept" parameterType="com.diagbot.vo.FilterVO" resultType="com.diagbot.dto.QcResultPercentDTO">
        SELECT
        a.beh_dept_id AS deptId,
        a.beh_dept_name AS deptName,
        count(*) AS totleNum,
        sum( c.`level` = '甲' ) AS firstLevelNum,
        sum( c.`level` = '乙' ) AS secondLevelNum,
        sum( c.`level` = '丙' ) AS thirdLevelNum,
        concat( ROUND( sum( c.`level` = '甲' )/ count(*)* 100, 2 ), '%' ) AS firstPercent,
        concat( ROUND( sum( c.`level` = '乙' )/ count(*)* 100, 2 ), '%' ) AS sencondPercent,
        concat( ROUND( sum( c.`level` = '丙' )/ count(*)* 100, 2 ), '%' ) AS thirdPercent
        FROM
        med_behospital_info a,
        med_qcresult_info c
        WHERE
        a.is_deleted = 'N'
        AND c.is_deleted = 'N'
        AND a.hospital_id = c.hospital_id
        AND a.behospital_code = c.behospital_code
        <if test="isPlacefile != null and isPlacefile != ''">
            and a.is_placefile = #{isPlacefile}
        </if>
        <![CDATA[AND a.qc_type_id <>0 ]]>
        <if test="hospitalId != null and hospitalId != ''">
            AND a.hospital_id = #{hospitalId}
        </if>
        <if test="isPlacefile != null and isPlacefile == 0">
            <if test="startDate != null and startDate != ''">
                <![CDATA[ AND a.behospital_date >= #{startDate}]]>
            </if>
            <if test="endDate != null and endDate != ''">
                <![CDATA[ AND a.behospital_date <= #{endDate}]]>
            </if>
        </if>
        <if test="isPlacefile != null and isPlacefile == 1">
            <if test="startDate != null and startDate != ''">
                <![CDATA[ AND a.leave_hospital_date >= #{startDate}]]>
            </if>
            <if test="endDate != null and endDate != ''">
                <![CDATA[ AND a.leave_hospital_date <= #{endDate}]]>
            </if>
        </if>
        GROUP BY
        a.beh_dept_id,
        a.beh_dept_name
    </select>

    <!-- 各科室甲/乙/丙级病历占比 -->
    <select id="qcResultLevelPercent" parameterType="com.diagbot.vo.FilterVO" resultType="com.diagbot.dto.DeptNumDTO">
        SELECT
        a.beh_dept_id AS deptId,
        a.beh_dept_name AS deptName,
        <if test="level != null and level != ''">
            sum( c.`level` = #{level} ) AS num,
            ROUND( sum( c.`level` = #{level} )/ count(*), 4 ) AS percent,
            concat( ROUND( sum( c.`level` = #{level} )/ count(*)* 100, 2 ), '%' ) AS percentStr,
        </if>
        count(*) AS totleNum
        FROM
        med_behospital_info a,
        med_qcresult_info c
        WHERE
        a.is_deleted = 'N'
        AND c.is_deleted = 'N'
        AND a.hospital_id = c.hospital_id
        AND a.behospital_code = c.behospital_code
        <if test="isPlacefile != null and isPlacefile != ''">
            and a.is_placefile = #{isPlacefile}
        </if>
        <![CDATA[AND a.qc_type_id <>0 ]]>
        <if test="hospitalId != null and hospitalId != ''">
            AND a.hospital_id = #{hospitalId}
        </if>
        <if test="isPlacefile != null and isPlacefile == 0">
            <if test="startDate != null and startDate != ''">
                <![CDATA[ AND a.behospital_date >= #{startDate}]]>
            </if>
            <if test="endDate != null and endDate != ''">
                <![CDATA[ AND a.behospital_date <= #{endDate}]]>
            </if>
        </if>
        <if test="isPlacefile != null and isPlacefile == 1">
            <if test="startDate != null and startDate != ''">
                <![CDATA[ AND a.leave_hospital_date >= #{startDate}]]>
            </if>
            <if test="endDate != null and endDate != ''">
                <![CDATA[ AND a.leave_hospital_date <= #{endDate}]]>
            </if>
        </if>
        GROUP BY
        a.beh_dept_id,
        a.beh_dept_name
        ORDER  BY
        percent DESC,
        a.beh_dept_name ASC
    </select>

    <!-- 按模块统计质控缺陷数 -->
    <select id="entryCountGroupByCase" parameterType="com.diagbot.vo.FilterVO" resultType="com.diagbot.dto.NumDTO">
        SELECT
        t1.id AS id,
        t1.NAME AS NAME,
        t1.num AS num,
        t2.mrNum AS mrNum,
        t3.standardNum AS standardNum,
        t2.mrNum * t3.standardNum AS totleNum,
        ROUND( t1.num /( t2.mrNum * t3.standardNum ), 4 ) AS percent,
        CONCAT( ROUND( t1.num /( t2.mrNum * t3.standardNum )* 100, 2 ), '%' ) AS percentStr
        FROM
        (
        SELECT
        e.id,
        f.NAME AS NAME,
        e.num
        FROM
        (
        SELECT
        d.cases_id AS id,
        count(*) AS num
        FROM
        med_behospital_info a,
        med_qcresult_info c,
        med_qcresult_detail d
        WHERE
        a.is_deleted = 'N'
        AND c.is_deleted = 'N'
        AND d.is_deleted = 'N'
        AND a.hospital_id = c.hospital_id
        AND a.hospital_id = d.hospital_id
        AND a.behospital_code = c.behospital_code
        AND a.behospital_code = d.behospital_code
        <if test="isPlacefile != null and isPlacefile != ''">
            and a.is_placefile = #{isPlacefile}
        </if>
        AND a.qc_type_id != 0
        <if test="hospitalId != null and hospitalId != ''">
            AND a.hospital_id = #{hospitalId}
        </if>
        <if test="isPlacefile != null and isPlacefile == 0">
            <if test="startDate != null and startDate != ''">
                <![CDATA[ AND a.behospital_date >= #{startDate}]]>
            </if>
            <if test="endDate != null and endDate != ''">
                <![CDATA[ AND a.behospital_date <= #{endDate}]]>
            </if>
        </if>
        <if test="isPlacefile != null and isPlacefile == 1">
            <if test="startDate != null and startDate != ''">
                <![CDATA[ AND a.leave_hospital_date >= #{startDate}]]>
            </if>
            <if test="endDate != null and endDate != ''">
                <![CDATA[ AND a.leave_hospital_date <= #{endDate}]]>
            </if>
        </if>
        GROUP BY
        d.cases_id
        ) e,
        qc_cases f
        WHERE
        f.is_deleted = 'N'
        AND e.id = f.id
        ) t1,
        (
        SELECT
        COUNT(*) AS mrNum
        FROM
        med_behospital_info a,
        med_qcresult_info c
        WHERE
        a.is_deleted = 'N'
        AND c.is_deleted = 'N'
        AND a.hospital_id = c.hospital_id
        AND a.behospital_code = c.behospital_code
        <if test="isPlacefile != null and isPlacefile != ''">
            and a.is_placefile = #{isPlacefile}
        </if>
        AND a.qc_type_id != 0
        <if test="hospitalId != null and hospitalId != ''">
            AND a.hospital_id = #{hospitalId}
        </if>
        <if test="isPlacefile != null and isPlacefile == 0">
            <if test="startDate != null and startDate != ''">
                <![CDATA[ AND a.behospital_date >= #{startDate}]]>
            </if>
            <if test="endDate != null and endDate != ''">
                <![CDATA[ AND a.behospital_date <= #{endDate}]]>
            </if>
        </if>
        <if test="isPlacefile != null and isPlacefile == 1">
            <if test="startDate != null and startDate != ''">
                <![CDATA[ AND a.leave_hospital_date >= #{startDate}]]>
            </if>
            <if test="endDate != null and endDate != ''">
                <![CDATA[ AND a.leave_hospital_date <= #{endDate}]]>
            </if>
        </if>
        ) t2,
        (
        SELECT
        a.id,
        a.NAME,
        count(*) AS standardNum
        FROM
        qc_cases a,
        qc_cases_entry b
        WHERE
        a.is_deleted = 'N'
        AND b.is_deleted = 'N'
        AND a.id = b.cases_id
        GROUP BY
        a.id,
        a.`name`
        ) t3
        WHERE
        t1.id = t3.id
        AND t1.NAME = t3.NAME
        ORDER BY
        percent DESC
        <if test="limitCount != null">
            LIMIT 0,
            #{limitCount}
        </if>
    </select>

    <!-- 条目缺陷占比（首页） -->
    <select id="entryCountGroupByEntry" parameterType="com.diagbot.vo.FilterVO" resultType="com.diagbot.dto.NumDTO">
        SELECT
        t1.NAME AS NAME,
        t1.num AS num,
        t2.mrNum AS totleNum,
        Round( t1.num / t2.mrNum, 4 ) AS percent,
        CONCAT( Round( t1.num / t2.mrNum * 100, 2 ), '%' ) AS percentStr
        FROM
        (
        SELECT
        tt2.id,
        tt2.NAME,
        tt1.num
        FROM
        (
        SELECT
        d.cases_entry_id,
        count(*) AS num
        FROM
        med_behospital_info a,
        med_qcresult_info c,
        med_qcresult_detail d
        WHERE
        a.is_deleted = 'N'
        AND c.is_deleted = 'N'
        AND d.is_deleted = 'N'
        AND a.hospital_id = c.hospital_id
        AND a.hospital_id = d.hospital_id
        AND a.behospital_code = c.behospital_code
        AND a.behospital_code = d.behospital_code
        <if test="isPlacefile != null and isPlacefile != ''">
            and a.is_placefile = #{isPlacefile}
        </if>
        AND a.qc_type_id != 0
        <if test="hospitalId != null and hospitalId != ''">
            AND a.hospital_id = #{hospitalId}
        </if>
        <if test="isPlacefile != null and isPlacefile == 0">
            <if test="startDate != null and startDate != ''">
                <![CDATA[ AND a.behospital_date >= #{startDate}]]>
            </if>
            <if test="endDate != null and endDate != ''">
                <![CDATA[ AND a.behospital_date <= #{endDate}]]>
            </if>
        </if>
        <if test="isPlacefile != null and isPlacefile == 1">
            <if test="startDate != null and startDate != ''">
                <![CDATA[ AND a.leave_hospital_date >= #{startDate}]]>
            </if>
            <if test="endDate != null and endDate != ''">
                <![CDATA[ AND a.leave_hospital_date <= #{endDate}]]>
            </if>
        </if>
        GROUP BY
        d.cases_entry_id
        ) tt1,
        qc_cases_entry tt2
        WHERE
        tt2.is_deleted = 'N'
        AND tt1.cases_entry_id = tt2.id
        ) t1,
        (
        SELECT
        count(*) AS mrNum
        FROM
        med_behospital_info a,
        med_qcresult_info c
        WHERE
        a.is_deleted = 'N'
        AND c.is_deleted = 'N'
        AND a.hospital_id = c.hospital_id
        AND a.behospital_code = c.behospital_code
        <if test="isPlacefile != null and isPlacefile != ''">
            and a.is_placefile = #{isPlacefile}
        </if>
        AND a.qc_type_id != 0
        <if test="hospitalId != null and hospitalId != ''">
            AND a.hospital_id = #{hospitalId}
        </if>
        <if test="isPlacefile != null and isPlacefile == 0">
            <if test="startDate != null and startDate != ''">
                <![CDATA[ AND a.behospital_date >= #{startDate}]]>
            </if>
            <if test="endDate != null and endDate != ''">
                <![CDATA[ AND a.behospital_date <= #{endDate}]]>
            </if>
        </if>
        <if test="isPlacefile != null and isPlacefile == 1">
            <if test="startDate != null and startDate != ''">
                <![CDATA[ AND a.leave_hospital_date >= #{startDate}]]>
            </if>
            <if test="endDate != null and endDate != ''">
                <![CDATA[ AND a.leave_hospital_date <= #{endDate}]]>
            </if>
        </if>
        ) t2
        ORDER BY
        percent DESC
    </select>

    <!-- 条目缺陷占比（内页） -->
    <select id="entryGroupByEntryInnerPage" resultType="com.diagbot.dto.EntryNumDTO">
        SELECT
        tt2.id as id,
        tt2.name as name,
        tt2.cases_id as casesId,
        tt2.cases_name as casesName,
        tt1.num as num,
        tt1.is_reject as isReject
        FROM
        (
        SELECT
        a.hospital_id,
        d.cases_entry_id,
        d.cases_id,
        count(*) as num,
        d.is_reject
        FROM
        med_behospital_info a,
        med_qcresult_detail d
        WHERE
        a.is_deleted = 'N'
        AND d.is_deleted = 'N'
        AND a.hospital_id = d.hospital_id
        AND a.behospital_code = d.behospital_code
        <if test="filterPageVO.isPlacefile != null and filterPageVO.isPlacefile != ''">
            AND a.is_placefile = #{filterPageVO.isPlacefile}
        </if>
        AND a.qc_type_id != 0
        <if test="filterPageVO.hospitalId != null and filterPageVO.hospitalId != ''">
            AND a.hospital_id = #{filterPageVO.hospitalId}
        </if>
        <if test="filterPageVO.isPlacefile != null and filterPageVO.isPlacefile == 0">
            <if test="filterPageVO.startDate != null and filterPageVO.startDate != ''">
                <![CDATA[ AND a.behospital_date >= #{filterPageVO.startDate}]]>
            </if>
            <if test="filterPageVO.endDate != null and filterPageVO.endDate != ''">
                <![CDATA[ AND a.behospital_date <= #{filterPageVO.endDate}]]>
            </if>
        </if>
        <if test="filterPageVO.isPlacefile != null and filterPageVO.isPlacefile == 1">
            <if test="filterPageVO.startDate != null and filterPageVO.startDate != ''">
                <![CDATA[ AND a.leave_hospital_date >= #{filterPageVO.startDate}]]>
            </if>
            <if test="filterPageVO.endDate != null and filterPageVO.endDate != ''">
                <![CDATA[ AND a.leave_hospital_date <= #{filterPageVO.endDate}]]>
            </if>
        </if>
        <if test="filterPageVO.isReject != null">
            AND d.is_reject = #{filterPageVO.isReject}
        </if>
        GROUP BY
        d.cases_entry_id,
        d.is_reject,
        d.cases_id
        ) tt1,
        qc_cases_entry tt2
        WHERE
        tt2.is_deleted = 'N'
        AND tt1.cases_id = tt2.cases_id
        AND tt1.cases_entry_id = tt2.id
        <if test="filterPageVO.name != null and filterPageVO.name != ''">
            AND tt2.name like CONCAT('%', #{filterPageVO.name},'%')
        </if>
        <if test="filterPageVO.casesName != null and filterPageVO.casesName != ''">
            AND tt2.cases_name like CONCAT('%', #{filterPageVO.casesName},'%')
        </if>

    </select>
    <!-- 条目缺陷占比缺陷总量（内页） -->
    <select id="entryGroupByEntrySum" resultType="java.lang.Integer">
        SELECT
        count(*) AS mrNum
        FROM
        med_behospital_info a,
        med_qcresult_info c
        WHERE
        a.is_deleted = 'N'
        AND c.is_deleted = 'N'
        AND a.hospital_id = c.hospital_id
        AND a.behospital_code = c.behospital_code
        <if test="isPlacefile != null and isPlacefile != ''">
            and a.is_placefile = #{isPlacefile}
        </if>
        AND a.qc_type_id != 0
        <if test="hospitalId != null and hospitalId != ''">
            AND a.hospital_id = #{hospitalId}
        </if>
        <if test="isPlacefile != null and isPlacefile == 0">
            <if test="startDate != null and startDate != ''">
                <![CDATA[ AND a.behospital_date >= #{startDate}]]>
            </if>
            <if test="endDate != null and endDate != ''">
                <![CDATA[ AND a.behospital_date <= #{endDate}]]>
            </if>
        </if>
        <if test="isPlacefile != null and isPlacefile == 1">
            <if test="startDate != null and startDate != ''">
                <![CDATA[ AND a.leave_hospital_date >= #{startDate}]]>
            </if>
            <if test="endDate != null and endDate != ''">
                <![CDATA[ AND a.leave_hospital_date <= #{endDate}]]>
            </if>
        </if>
    </select>

    <!-- 条目缺陷占比分值统计 -->
    <select id="entryGroupByEntryScore"  resultType="com.diagbot.dto.EntryNumDTO">
        SELECT
        a.hospital_id as hospitalId,

        <if test="isPlacefile != null and isPlacefile == 0">
            a.score_run as score,
        </if>
        <if test="isPlacefile != null and isPlacefile == 1">
            a.score,
        </if>
        a.cases_entry_id as id
        FROM
        qc_cases_entry_hospital a
        WHERE
        a.is_deleted = 'N'
        AND a.hospital_id = #{hospitalId}
        AND a.cases_entry_id IN
        <foreach collection="ids" open="(" separator="," close=")" item="item">
            ${item}
        </foreach>
    </select>
    <!-- 条目缺陷占比原始数据统计（内页） -->
    <!--  <select id="entryGroupByEntryInnerPage" resultType="com.diagbot.dto.EntryNumDTO">
          SELECT
          t.*
          FROM
          (
          SELECT
          t1.id AS id,
          t1.NAME AS NAME,
          t1.casesId AS casesId,
          t1.casesName AS casesName,
          t1.num AS num,
          t2.mrNum AS totleNum,
          t1.score AS score,
          t1.isReject AS isReject,
          Round( t1.num / t2.mrNum, 4 ) AS percent,
          CONCAT( Round( t1.num / t2.mrNum * 100, 2 ), '%' ) AS percentStr
          FROM
          (
          SELECT
          tt2.id,
          tt2.NAME,
          tt2.cases_id AS casesId,
          tt2.cases_name AS casesName,
          <if test="filterPageVO.isPlacefile != null and filterPageVO.isPlacefile == 0">
              tt3.score_run as score,
          </if>
          <if test="filterPageVO.isPlacefile != null and filterPageVO.isPlacefile == 1">
              tt3.score,
          </if>
          tt1.num,
          tt1.is_reject AS isReject
          FROM
          (
          SELECT
          a.hospital_id,
          d.cases_entry_id,
          d.cases_id,
          count(*) AS num,
          d.is_reject
          FROM
          med_behospital_info a,
          med_qcresult_info c,
          med_qcresult_detail d
          WHERE
          a.is_deleted = 'N'
          AND c.is_deleted = 'N'
          AND d.is_deleted = 'N'
          AND a.hospital_id = c.hospital_id
          AND a.hospital_id = d.hospital_id
          AND a.behospital_code = c.behospital_code
          AND a.behospital_code = d.behospital_code
          <if test="filterPageVO.isPlacefile != null and filterPageVO.isPlacefile != ''">
              and a.is_placefile = #{filterPageVO.isPlacefile}
          </if>
          AND a.qc_type_id != 0
          <if test="filterPageVO.hospitalId != null and filterPageVO.hospitalId != ''">
              AND a.hospital_id = #{filterPageVO.hospitalId}
          </if>
          <if test="filterPageVO.isPlacefile != null and filterPageVO.isPlacefile == 0">
              <if test="filterPageVO.startDate != null and filterPageVO.startDate != ''">
                  <![CDATA[ AND a.behospital_date >= #{filterPageVO.startDate}]]>
              </if>
              <if test="filterPageVO.endDate != null and filterPageVO.endDate != ''">
                  <![CDATA[ AND a.behospital_date <= #{filterPageVO.endDate}]]>
              </if>
          </if>
          <if test="filterPageVO.isPlacefile != null and filterPageVO.isPlacefile == 1">
              <if test="filterPageVO.startDate != null and filterPageVO.startDate != ''">
                  <![CDATA[ AND a.leave_hospital_date >= #{filterPageVO.startDate}]]>
              </if>
              <if test="filterPageVO.endDate != null and filterPageVO.endDate != ''">
                  <![CDATA[ AND a.leave_hospital_date <= #{filterPageVO.endDate}]]>
              </if>
          </if>
          <if test="filterPageVO.isReject != null">
              AND d.is_reject = #{filterPageVO.isReject}
          </if>
          GROUP BY
          d.cases_entry_id,
          d.is_reject,
          d.cases_id
          ) tt1,
          qc_cases_entry tt2,
          qc_cases_entry_hospital tt3
          WHERE
          tt2.is_deleted = 'N'
          AND tt3.is_deleted = 'N'
          AND tt1.hospital_id = tt3.hospital_id
          AND tt1.cases_id = tt2.cases_id
          AND tt1.cases_entry_id = tt2.id
          AND tt1.cases_entry_id = tt3.cases_entry_id
          <if test="filterPageVO.name != null and filterPageVO.name != ''">
              AND tt2.name like CONCAT('%', #{filterPageVO.name},'%')
          </if>
          <if test="filterPageVO.casesName != null and filterPageVO.casesName != ''">
              AND tt2.cases_name = #{filterPageVO.casesName}
          </if>
          ) t1,
          (
          SELECT
          count(*) AS mrNum
          FROM
          med_behospital_info a,
          med_qcresult_info c
          WHERE
          a.is_deleted = 'N'
          AND c.is_deleted = 'N'
          AND a.hospital_id = c.hospital_id
          AND a.behospital_code = c.behospital_code
          <if test="filterPageVO.isPlacefile != null and filterPageVO.isPlacefile != ''">
              and a.is_placefile = #{filterPageVO.isPlacefile}
          </if>
          AND a.qc_type_id != 0
          <if test="filterPageVO.hospitalId != null and filterPageVO.hospitalId != ''">
              AND a.hospital_id = #{filterPageVO.hospitalId}
          </if>
          <if test="filterPageVO.isPlacefile != null and filterPageVO.isPlacefile == 0">
              <if test="filterPageVO.startDate != null and filterPageVO.startDate != ''">
                  <![CDATA[ AND a.behospital_date >= #{filterPageVO.startDate}]]>
              </if>
              <if test="filterPageVO.endDate != null and filterPageVO.endDate != ''">
                  <![CDATA[ AND a.behospital_date <= #{filterPageVO.endDate}]]>
              </if>
          </if>
          <if test="filterPageVO.isPlacefile != null and filterPageVO.isPlacefile == 1">
              <if test="filterPageVO.startDate != null and filterPageVO.startDate != ''">
                  <![CDATA[ AND a.leave_hospital_date >= #{filterPageVO.startDate}]]>
              </if>
              <if test="filterPageVO.endDate != null and filterPageVO.endDate != ''">
                  <![CDATA[ AND a.leave_hospital_date <= #{filterPageVO.endDate}]]>
              </if>
          </if>
          ) t2
          ) t
      </select>-->
    <!-- 单项否决缺陷占比（首页） -->
    <select id="entryRejectPercent" parameterType="com.diagbot.vo.FilterVO" resultType="com.diagbot.dto.EntryNumDTO">
        SELECT
        t1.id AS id,
        t1.NAME AS NAME,
        t1.casesId AS casesId,
        t1.casesName AS casesName,
        t1.num AS num,
        t2.mrNum AS totleNum,
        t1.score AS score,
        t1.isReject AS isReject,
        Round( t1.num / t2.mrNum, 4 ) AS percent,
        CONCAT( Round( t1.num / t2.mrNum * 100, 2 ), '%' ) AS percentStr
        FROM
        (
        SELECT
        tt2.id,
        tt2.NAME,
        tt2.cases_id AS casesId,
        tt2.cases_name AS casesName,
        tt1.num,
        <if test="isPlacefile != null and isPlacefile == 0">
            tt3.score_run as score,
        </if>
        <if test="isPlacefile != null and isPlacefile == 1">
            tt3.score,
        </if>
        tt1.is_reject AS isReject
        FROM
        (
        SELECT
        d.cases_entry_id,
        d.cases_id,
        a.hospital_id,
        count(*) AS num,
        d.is_reject
        FROM
        med_behospital_info a,
        med_qcresult_info c,
        med_qcresult_detail d
        WHERE
        a.is_deleted = 'N'
        AND c.is_deleted = 'N'
        AND d.is_deleted = 'N'
        AND a.hospital_id = c.hospital_id
        AND a.hospital_id = d.hospital_id
        AND a.behospital_code = c.behospital_code
        AND a.behospital_code = d.behospital_code
        <if test="isPlacefile != null and isPlacefile != ''">
            and a.is_placefile = #{isPlacefile}
        </if>
        AND d.is_reject = 1
        AND a.qc_type_id != 0
        <if test="hospitalId != null and hospitalId != ''">
            AND a.hospital_id = #{hospitalId}
        </if>
        <if test="isPlacefile != null and isPlacefile == 0">
            <if test="startDate != null and startDate != ''">
                <![CDATA[ AND a.behospital_date >= #{startDate}]]>
            </if>
            <if test="endDate != null and endDate != ''">
                <![CDATA[ AND a.behospital_date <= #{endDate}]]>
            </if>
        </if>
        <if test="isPlacefile != null and isPlacefile == 1">
            <if test="startDate != null and startDate != ''">
                <![CDATA[ AND a.leave_hospital_date >= #{startDate}]]>
            </if>
            <if test="endDate != null and endDate != ''">
                <![CDATA[ AND a.leave_hospital_date <= #{endDate}]]>
            </if>
        </if>
        GROUP BY
        d.cases_entry_id,
        d.cases_id
        ) tt1,
        qc_cases_entry tt2,
        qc_cases_entry_hospital tt3
        WHERE
        tt2.is_deleted = 'N'
        AND tt3.is_deleted = 'N'
        AND tt1.hospital_id = tt3.hospital_id
        AND tt1.cases_id = tt2.cases_id
        AND tt1.cases_entry_id = tt2.id
        AND tt2.id = tt3.cases_entry_id
        ) t1,
        (
        SELECT
        count(*) AS mrNum
        FROM
        med_behospital_info a,
        med_qcresult_info c
        WHERE
        a.is_deleted = 'N'
        AND c.is_deleted = 'N'
        AND a.hospital_id = c.hospital_id
        AND a.behospital_code = c.behospital_code
        <if test="isPlacefile != null and isPlacefile != ''">
            and a.is_placefile = #{isPlacefile}
        </if>
        AND a.qc_type_id != 0
        <if test="hospitalId != null and hospitalId != ''">
            AND a.hospital_id = #{hospitalId}
        </if>
        <if test="isPlacefile != null and isPlacefile == 0">
            <if test="startDate != null and startDate != ''">
                <![CDATA[ AND a.behospital_date >= #{startDate}]]>
            </if>
            <if test="endDate != null and endDate != ''">
                <![CDATA[ AND a.behospital_date <= #{endDate}]]>
            </if>
        </if>
        <if test="isPlacefile != null and isPlacefile == 1">
            <if test="startDate != null and startDate != ''">
                <![CDATA[ AND a.leave_hospital_date >= #{startDate}]]>
            </if>
            <if test="endDate != null and endDate != ''">
                <![CDATA[ AND a.leave_hospital_date <= #{endDate}]]>
            </if>
        </if>
        ) t2
        ORDER BY
        percent DESC
    </select>
    <!-- 各科室质控平均分（首页） -->
    <select id="getAverageScore" parameterType="com.diagbot.vo.FilterVO" resultType="com.diagbot.dto.AverageStatisticsDTO">
        select
        m1.id,
        m1.name,
        m1.totleValue,
        m2.sameTotleValue,
        m1.averageValue,
        m2.lastYearAverageValue,
        m1.num,
        m2.sameNum
        from(
        SELECT
        a.beh_dept_id AS id,
        a.beh_dept_name AS name,
        ROUND( sum( CAST( c.score_res AS DECIMAL ( 18, 2 ) )), 2 ) AS totleValue,
        ROUND( sum( CAST( c.score_res AS DECIMAL ( 18, 2 ) ))/ count(*), 2 ) AS averageValue,
        count(*) AS num
        FROM
        med_behospital_info a,
        med_qcresult_info c
        WHERE
        a.is_deleted = 'N'
        AND c.is_deleted = 'N'
        AND a.hospital_id = c.hospital_id
        AND a.behospital_code = c.behospital_code
        <if test="isPlacefile != null and isPlacefile != ''">
            and a.is_placefile = #{isPlacefile}
        </if>
        <![CDATA[AND a.qc_type_id <>0 ]]>
        <if test="hospitalId != null and hospitalId != ''">
            AND a.hospital_id = #{hospitalId}
        </if>
        <if test="isPlacefile != null and isPlacefile == 0">
            <if test="startDate != null and startDate != ''">
                <![CDATA[ AND a.behospital_date >= #{startDate}]]>
            </if>
            <if test="endDate != null and endDate != ''">
                <![CDATA[ AND a.behospital_date <= #{endDate}]]>
            </if>
        </if>
        <if test="isPlacefile != null and isPlacefile == 1">
            <if test="startDate != null and startDate != ''">
                <![CDATA[ AND a.leave_hospital_date >= #{startDate}]]>
            </if>
            <if test="endDate != null and endDate != ''">
                <![CDATA[ AND a.leave_hospital_date <= #{endDate}]]>
            </if>
        </if>
        GROUP BY
        a.beh_dept_id,
        a.beh_dept_name)m1 left join
        ( SELECT
        a.beh_dept_id AS id,
        a.beh_dept_name AS name,
        ROUND( sum( CAST( c.score_res AS DECIMAL ( 18, 2 ) )), 2 ) AS sameTotleValue,
        ROUND( sum( CAST( c.score_res AS DECIMAL ( 18, 2 ) ))/ count(*), 2 ) AS lastYearAverageValue,
        count(*) AS sameNum
        FROM
        med_behospital_info a,
        med_qcresult_info c
        WHERE
        a.is_deleted = 'N'
        AND c.is_deleted = 'N'
        AND a.hospital_id = c.hospital_id
        AND a.behospital_code = c.behospital_code
        <if test="isPlacefile != null and isPlacefile != ''">
            and a.is_placefile = #{isPlacefile}
        </if>
        <![CDATA[AND a.qc_type_id <>0 ]]>
        <if test="hospitalId != null and hospitalId != ''">
            AND a.hospital_id = #{hospitalId}
        </if>
        <if test="isPlacefile != null and isPlacefile == 0">
            <if test="lastStartDate != null and lastStartDate != ''">
                <![CDATA[ AND a.behospital_date >= #{lastStartDate}]]>
            </if>
            <if test="lastEndDate != null and lastEndDate != ''">
                <![CDATA[ AND a.behospital_date <= #{lastEndDate}]]>
            </if>
        </if>
        <if test="isPlacefile != null and isPlacefile == 1">
            <if test="lastStartDate != null and lastStartDate != ''">
                <![CDATA[ AND a.leave_hospital_date >= #{lastStartDate}]]>
            </if>
            <if test="lastEndDate != null and lastEndDate != ''">
                <![CDATA[ AND a.leave_hospital_date <= #{lastEndDate}]]>
            </if>
        </if>
        GROUP BY
        a.beh_dept_id,
        a.beh_dept_name)m2
        on
        m1.id = m2.id
        and m1.name = m2.name
    </select>

    <!-- 各科室质控平均分（首页）-根据内外科系统统计 -->
    <select id="getAverageScoreByDeptClass" parameterType="com.diagbot.vo.FilterVO" resultType="com.diagbot.dto.AverageStatisticsDTO">
        select
        m1.id,
        m1.name,
        m1.totleValue,
        m2.sameTotleValue,
        m1.averageValue,
        m2.lastYearAverageValue,
        m1.num,
        m2.sameNum,
        m1.deptClass
        from(
        SELECT
        a.beh_dept_id AS id,
        a.beh_dept_name AS NAME,
        ROUND( sum( CAST( c.score_res AS DECIMAL ( 18, 2 ) )), 2 ) AS totleValue,
        ROUND( sum( CAST( c.score_res AS DECIMAL ( 18, 2 ) ))/ count(*), 2 ) AS averageValue,
        count(*) AS num ,
        e.dept_name as deptClass
        FROM
        med_behospital_info a,
        med_qcresult_info c,
        bas_dept_info d,
        bas_dept_info e
        WHERE
        a.is_deleted = 'N'
        AND c.is_deleted = 'N'
        AND d.is_deleted = 'N'
        AND e.is_deleted = 'N'
        AND a.hospital_id = c.hospital_id
        AND a.hospital_id = d.hospital_id
        AND a.hospital_id = e.hospital_id
        AND a.behospital_code = c.behospital_code
        AND a.beh_dept_id = d.dept_id
        AND d.parent_dept_id = e.dept_id
        <if test="isPlacefile != null and isPlacefile != ''">
            and a.is_placefile = #{isPlacefile}
        </if>
        <![CDATA[AND a.qc_type_id <>0 ]]>
        <if test="hospitalId != null and hospitalId != ''">
            AND a.hospital_id = #{hospitalId}
        </if>
        <if test="isPlacefile != null and isPlacefile == 0">
            <if test="startDate != null and startDate != ''">
                <![CDATA[ AND a.behospital_date >= #{startDate}]]>
            </if>
            <if test="endDate != null and endDate != ''">
                <![CDATA[ AND a.behospital_date <= #{endDate}]]>
            </if>
        </if>
        <if test="isPlacefile != null and isPlacefile == 1">
            <if test="startDate != null and startDate != ''">
                <![CDATA[ AND a.leave_hospital_date >= #{startDate}]]>
            </if>
            <if test="endDate != null and endDate != ''">
                <![CDATA[ AND a.leave_hospital_date <= #{endDate}]]>
            </if>
        </if>
        <if test="deptClass != null and deptClass != ''">
            AND e.dept_name = #{deptClass}
        </if>
        GROUP BY
        a.beh_dept_id,
        a.beh_dept_name) m1 left join
        (SELECT
        a.beh_dept_id AS id,
        a.beh_dept_name AS NAME,
        ROUND( sum( CAST( c.score_res AS DECIMAL ( 18, 2 ) )), 2 ) AS sameTotleValue,
        ROUND( sum( CAST( c.score_res AS DECIMAL ( 18, 2 ) ))/ count(*), 2 ) AS lastYearAverageValue,
        count(*) AS sameNum ,
        e.dept_name as deptClass
        FROM
        med_behospital_info a,
        med_qcresult_info c,
        bas_dept_info d,
        bas_dept_info e
        WHERE
        a.is_deleted = 'N'
        AND c.is_deleted = 'N'
        AND d.is_deleted = 'N'
        AND e.is_deleted = 'N'
        AND a.hospital_id = c.hospital_id
        AND a.hospital_id = d.hospital_id
        AND a.hospital_id = e.hospital_id
        AND a.behospital_code = c.behospital_code
        AND a.beh_dept_id = d.dept_id
        AND d.parent_dept_id = e.dept_id
        <if test="isPlacefile != null and isPlacefile != ''">
            and a.is_placefile = #{isPlacefile}
        </if>
        <![CDATA[AND a.qc_type_id <>0 ]]>
        <if test="hospitalId != null and hospitalId != ''">
            AND a.hospital_id = #{hospitalId}
        </if>
        <if test="isPlacefile != null and isPlacefile == 0">
            <if test="lastStartDate != null and lastStartDate != ''">
                <![CDATA[ AND a.behospital_date >= #{lastStartDate}]]>
            </if>
            <if test="lastEndDate != null and lastEndDate != ''">
                <![CDATA[ AND a.behospital_date <= #{lastEndDate}]]>
            </if>
        </if>
        <if test="isPlacefile != null and isPlacefile == 1">
            <if test="lastStartDate != null and lastStartDate != ''">
                <![CDATA[ AND a.leave_hospital_date >= #{lastStartDate}]]>
            </if>
            <if test="lastEndDate != null and lastEndDate != ''">
                <![CDATA[ AND a.leave_hospital_date <= #{lastEndDate}]]>
            </if>
        </if>
        <if test="deptClass != null and deptClass != ''">
            AND e.dept_name = #{deptClass}
        </if>
        GROUP BY
        a.beh_dept_id,
        a.beh_dept_name)m2
        on
        m1.id = m2.id
        and m1.name = m2.name
    </select>

    <!-- 按科室统计质控病历数 -->
    <select id="resultCountByDept" parameterType="com.diagbot.vo.QcresultFilterVO" resultType="com.diagbot.dto.NumDTO">
        SELECT
        a.beh_dept_id AS id,
        a.beh_dept_name AS NAME,
        COUNT(*) AS num
        FROM
        med_behospital_info a,
        med_qcresult_info c,
        sys_user_dept d
        WHERE
        a.is_deleted = 'N'
        AND c.is_deleted = 'N'
        AND d.is_deleted = 'N'
        AND a.hospital_id = c.hospital_id
        AND a.hospital_id = d.hospital_id
        AND a.behospital_code = c.behospital_code
        AND a.beh_dept_id = d.dept_id
        <if test="isPlacefile != null and isPlacefile != ''">
            and a.is_placefile = #{isPlacefile}
        </if>
        <![CDATA[AND a.qc_type_id <>0 ]]>
        <if test="userId!=null">
            AND d.user_id = #{userId}
        </if>
        <if test="hospitalId != null and hospitalId != ''">
            AND a.hospital_id = #{hospitalId}
        </if>
        <if test="isPlacefile != null and isPlacefile == 0">
            <if test="startDate != null and startDate != ''">
                <![CDATA[ AND a.behospital_date >= #{startDate}]]>
            </if>
            <if test="endDate != null and endDate != ''">
                <![CDATA[ AND a.behospital_date <= #{endDate}]]>
            </if>
        </if>
        <if test="isPlacefile != null and isPlacefile == 1">
            <if test="startDate != null and startDate != ''">
                <![CDATA[ AND a.leave_hospital_date >= #{startDate}]]>
            </if>
            <if test="endDate != null and endDate != ''">
                <![CDATA[ AND a.leave_hospital_date <= #{endDate}]]>
            </if>
        </if>
        <if test="gradeType != null and gradeType != ''">
            AND c.grade_type = #{gradeType}
        </if>
        <if test="level != null and level != ''">
            AND c.level = #{level}
        </if>
        GROUP BY
        a.beh_dept_id,
        a.beh_dept_name
    </select>

    <!-- 按模块统计质控缺陷数 -->
    <select id="entryCountGroupByCaseAndDept" parameterType="com.diagbot.vo.FilterVO" resultType="com.diagbot.dto.DeptNumDTO">
        SELECT
        f.id,
        g.NAME AS NAME,
        f.deptId,
        f.deptName,
        f.num
        FROM
        (
        SELECT
        a.beh_dept_id AS deptId,
        a.beh_dept_name AS deptName,
        c.cases_id AS id,
        count(*) AS num
        FROM
        med_behospital_info a,
        med_qcresult_info b,
        med_qcresult_detail c,
        qc_cases_entry d,
        sys_user_dept e
        WHERE
        a.is_deleted = 'N'
        AND b.is_deleted = 'N'
        AND c.is_deleted = 'N'
        AND d.is_deleted = 'N'
        AND e.is_deleted = 'N'
        AND a.hospital_id = b.hospital_id
        AND a.hospital_id = c.hospital_id
        AND a.hospital_id = e.hospital_id
        AND a.behospital_code = b.behospital_code
        AND a.behospital_code = c.behospital_code
        AND c.cases_entry_id = d.id
        AND a.beh_dept_id = e.dept_id
        <if test="isPlacefile != null and isPlacefile != ''">
            and a.is_placefile = #{isPlacefile}
        </if>
        AND a.qc_type_id != 0
        <if test="userId!=null">
            AND e.user_id = #{userId}
        </if>
        <if test="hospitalId != null and hospitalId != ''">
            AND a.hospital_id = #{hospitalId}
        </if>
        <if test="isPlacefile != null and isPlacefile == 0">
            <if test="startDate != null and startDate != ''">
                <![CDATA[ AND a.behospital_date >= #{startDate}]]>
            </if>
            <if test="endDate != null and endDate != ''">
                <![CDATA[ AND a.behospital_date <= #{endDate}]]>
            </if>
        </if>
        <if test="isPlacefile != null and isPlacefile == 1">
            <if test="startDate != null and startDate != ''">
                <![CDATA[ AND a.leave_hospital_date >= #{startDate}]]>
            </if>
            <if test="endDate != null and endDate != ''">
                <![CDATA[ AND a.leave_hospital_date <= #{endDate}]]>
            </if>
        </if>
        GROUP BY
        d.cases_id,
        a.beh_dept_id,
        a.beh_dept_name
        ) f,
        qc_cases g
        WHERE
        g.is_deleted = 'N'
        AND f.id = g.id
    </select>

    <!-- 条目缺陷占比-科室（首页） -->
    <select id="entryCountGroupByEntryAndDept" parameterType="com.diagbot.vo.FilterVO" resultType="com.diagbot.dto.DeptNumDTO">
        SELECT
        t1.deptId AS deptId,
        t1.deptName AS deptName,
        t1.NAME AS NAME,
        t1.num AS num,
        t2.mrNum AS totleNum,
        Round( t1.num / t2.mrNum, 4 ) AS percent,
        CONCAT( Round( t1.num / t2.mrNum * 100, 2 ), '%' ) AS percentStr
        FROM
        (
        SELECT
        tt2.id,
        tt2.NAME,
        tt1.beh_dept_id AS deptId,
        tt1.beh_dept_name AS deptName,
        tt1.num
        FROM
        (
        SELECT
        a.beh_dept_id,
        a.beh_dept_name,
        count(*) AS num,
        d.cases_entry_id
        FROM
        med_behospital_info a,
        med_qcresult_info c,
        med_qcresult_detail d,
        sys_user_dept f
        WHERE
        a.is_deleted = 'N'
        AND c.is_deleted = 'N'
        AND d.is_deleted = 'N'
        AND f.is_deleted = 'N'
        AND a.hospital_id = c.hospital_id
        AND a.hospital_id = d.hospital_id
        AND a.hospital_id = f.hospital_id
        AND a.behospital_code = c.behospital_code
        AND a.behospital_code = d.behospital_code
        AND a.beh_dept_id = f.dept_id
        <if test="isPlacefile != null and isPlacefile != ''">
            and a.is_placefile = #{isPlacefile}
        </if>
        AND a.qc_type_id != 0
        <if test="userId!=null">
            AND f.user_id = #{userId}
        </if>
        <if test="hospitalId != null and hospitalId != ''">
            AND a.hospital_id = #{hospitalId}
        </if>
        <if test="isPlacefile != null and isPlacefile == 0">
            <if test="startDate != null and startDate != ''">
                <![CDATA[ AND a.behospital_date >= #{startDate}]]>
            </if>
            <if test="endDate != null and endDate != ''">
                <![CDATA[ AND a.behospital_date <= #{endDate}]]>
            </if>
        </if>
        <if test="isPlacefile != null and isPlacefile == 1">
            <if test="startDate != null and startDate != ''">
                <![CDATA[ AND a.leave_hospital_date >= #{startDate}]]>
            </if>
            <if test="endDate != null and endDate != ''">
                <![CDATA[ AND a.leave_hospital_date <= #{endDate}]]>
            </if>
        </if>
        GROUP BY
        d.cases_entry_id,
        a.beh_dept_id,
        a.beh_dept_name
        ) tt1,
        qc_cases_entry tt2
        WHERE
        tt2.is_deleted = 'N'
        AND tt1.cases_entry_id = tt2.id
        ) t1,
        (
        SELECT
        a.beh_dept_id AS deptId,
        a.beh_dept_name AS deptName,
        count(*) AS mrNum
        FROM
        med_behospital_info a,
        med_qcresult_info c,
        sys_user_dept f
        WHERE
        a.is_deleted = 'N'
        AND c.is_deleted = 'N'
        AND f.is_deleted = 'N'
        AND a.hospital_id = c.hospital_id
        AND a.hospital_id = f.hospital_id
        AND a.behospital_code = c.behospital_code
        AND a.beh_dept_id = f.dept_id
        <if test="isPlacefile != null and isPlacefile != ''">
            and a.is_placefile = #{isPlacefile}
        </if>
        AND a.qc_type_id != 0
        <if test="userId!=null">
            AND f.user_id = #{userId}
        </if>
        <if test="hospitalId != null and hospitalId != ''">
            AND a.hospital_id = #{hospitalId}
        </if>
        <if test="isPlacefile != null and isPlacefile == 0">
            <if test="startDate != null and startDate != ''">
                <![CDATA[ AND a.behospital_date >= #{startDate}]]>
            </if>
            <if test="endDate != null and endDate != ''">
                <![CDATA[ AND a.behospital_date <= #{endDate}]]>
            </if>
        </if>
        <if test="isPlacefile != null and isPlacefile == 1">
            <if test="startDate != null and startDate != ''">
                <![CDATA[ AND a.leave_hospital_date >= #{startDate}]]>
            </if>
            <if test="endDate != null and endDate != ''">
                <![CDATA[ AND a.leave_hospital_date <= #{endDate}]]>
            </if>
        </if>
        GROUP BY
        a.beh_dept_id,
        a.beh_dept_name
        ) t2
        WHERE
        t1.deptId = t2.deptId
        AND t1.deptName = t2.deptName
        ORDER BY
        percent DESC,
        num DESC
    </select>

    <!-- 条目缺陷占比-科室（内页） -->
    <select id="entryGroupByEntryAndDeptInnerPage" resultType="com.diagbot.dto.DeptEntryNumDTO">
        SELECT
        t.*
        FROM
        (
        SELECT
        t1.deptId AS deptId,
        t1.deptName AS deptName,
        t1.id AS id,
        t1.NAME AS NAME,
        t1.casesId AS casesId,
        t1.casesName AS casesName,
        t1.num AS num,
        t2.mrNum AS totleNum,
        t1.score AS score,
        t1.isReject AS isReject,
        Round( t1.num / t2.mrNum, 4 ) AS percent,
        CONCAT( Round( t1.num / t2.mrNum * 100, 2 ), '%' ) AS percentStr
        FROM
        (
        SELECT
        tt2.id,
        tt2.NAME,
        tt1.beh_dept_id AS deptId,
        tt1.beh_dept_name AS deptName,
        tt2.cases_id AS casesId,
        tt2.cases_name AS casesName,
        tt1.num,
        <if test="filterPageByDeptVO.isPlacefile != null and filterPageByDeptVO.isPlacefile == 0">
            tt3.score_run as score,
        </if>
        <if test="filterPageByDeptVO.isPlacefile != null and filterPageByDeptVO.isPlacefile == 1">
            tt3.score,
        </if>
        tt1.is_reject AS isReject
        FROM
        (
        SELECT
        a.hospital_id,
        a.beh_dept_id,
        a.beh_dept_name,
        count(*) AS num,
        d.cases_id,
        d.cases_entry_id,
        d.is_reject
        FROM
        med_behospital_info a,
        med_qcresult_info c,
        med_qcresult_detail d,
        sys_user_dept f
        WHERE
        a.is_deleted = 'N'
        AND c.is_deleted = 'N'
        AND d.is_deleted = 'N'
        AND f.is_deleted = 'N'
        AND a.hospital_id = c.hospital_id
        AND a.hospital_id = d.hospital_id
        AND a.hospital_id = f.hospital_id
        AND a.behospital_code = c.behospital_code
        AND a.behospital_code = d.behospital_code
        AND a.beh_dept_id = f.dept_id
        <if test="filterPageByDeptVO.isPlacefile != null and filterPageByDeptVO.isPlacefile != ''">
            and a.is_placefile = #{filterPageByDeptVO.isPlacefile}
        </if>
        AND a.qc_type_id != 0
        <if test="filterPageByDeptVO.userId!=null">
            AND f.user_id = #{filterPageByDeptVO.userId}
        </if>
        <if test="filterPageByDeptVO.hospitalId != null and filterPageByDeptVO.hospitalId != ''">
            AND a.hospital_id = #{filterPageByDeptVO.hospitalId}
        </if>
        <if test="filterPageByDeptVO.isPlacefile != null and filterPageByDeptVO.isPlacefile == 0">
            <if test="filterPageByDeptVO.startDate != null and filterPageByDeptVO.startDate != ''">
                <![CDATA[ AND a.behospital_date >= #{filterPageByDeptVO.startDate}]]>
            </if>
            <if test="filterPageByDeptVO.endDate != null and filterPageByDeptVO.endDate != ''">
                <![CDATA[ AND a.behospital_date <= #{filterPageByDeptVO.endDate}]]>
            </if>
        </if>
        <if test="filterPageByDeptVO.isPlacefile != null and filterPageByDeptVO.isPlacefile == 1">
            <if test="filterPageByDeptVO.startDate != null and filterPageByDeptVO.startDate != ''">
                <![CDATA[ AND a.leave_hospital_date >= #{filterPageByDeptVO.startDate}]]>
            </if>
            <if test="filterPageByDeptVO.endDate != null and filterPageByDeptVO.endDate != ''">
                <![CDATA[ AND a.leave_hospital_date <= #{filterPageByDeptVO.endDate}]]>
            </if>
        </if>
        <if test="filterPageByDeptVO.deptName != null and filterPageByDeptVO.deptName != ''">
            AND a.beh_dept_name = #{filterPageByDeptVO.deptName}
        </if>
        <if test="filterPageByDeptVO.doctorName != null and filterPageByDeptVO.doctorName != ''">
            AND a.doctor_name like CONCAT('%', #{filterPageByDeptVO.doctorName}, '%')
        </if>
        <if test="filterPageByDeptVO.isReject != null">
            AND d.is_reject = #{filterPageByDeptVO.isReject}
        </if>
        GROUP BY
        d.cases_entry_id,
        a.beh_dept_id,
        a.beh_dept_name,
        d.is_reject,
        d.cases_id
        ) tt1,
        qc_cases_entry tt2,
        qc_cases_entry_hospital tt3
        WHERE
        tt2.is_deleted = 'N'
        AND tt3.is_deleted = 'N'
        AND tt1.hospital_id = tt3.hospital_id
        AND tt1.cases_id = tt2.cases_id
        AND tt1.cases_entry_id = tt2.id
        AND tt2.id = tt3.cases_entry_id
        <if test="filterPageByDeptVO.name != null and filterPageByDeptVO.name != ''">
            AND tt2.name like CONCAT('%', #{filterPageByDeptVO.name},'%')
        </if>
        <if test="filterPageByDeptVO.casesName != null and filterPageByDeptVO.casesName != ''">
            AND tt2.cases_name like CONCAT('%', #{filterPageByDeptVO.casesName},'%')
        </if>
        ) t1,
        (
        SELECT
        a.beh_dept_id AS deptId,
        a.beh_dept_name AS deptName,
        count(*) AS mrNum
        FROM
        med_behospital_info a,
        med_qcresult_info c,
        sys_user_dept f
        WHERE
        a.is_deleted = 'N'
        AND c.is_deleted = 'N'
        AND f.is_deleted = 'N'
        AND a.hospital_id = c.hospital_id
        AND a.hospital_id = f.hospital_id
        AND a.behospital_code = c.behospital_code
        AND a.beh_dept_id = f.dept_id
        <if test="filterPageByDeptVO.isPlacefile != null and filterPageByDeptVO.isPlacefile != ''">
            and a.is_placefile = #{filterPageByDeptVO.isPlacefile}
        </if>
        AND a.qc_type_id != 0
        <if test="filterPageByDeptVO.userId!=null">
            AND f.user_id = #{filterPageByDeptVO.userId}
        </if>
        <if test="filterPageByDeptVO.hospitalId != null and filterPageByDeptVO.hospitalId != ''">
            AND a.hospital_id = #{filterPageByDeptVO.hospitalId}
        </if>
        <if test="filterPageByDeptVO.isPlacefile != null and filterPageByDeptVO.isPlacefile == 0">
            <if test="filterPageByDeptVO.startDate != null and filterPageByDeptVO.startDate != ''">
                <![CDATA[ AND a.behospital_date >= #{filterPageByDeptVO.startDate}]]>
            </if>
            <if test="filterPageByDeptVO.endDate != null and filterPageByDeptVO.endDate != ''">
                <![CDATA[ AND a.behospital_date <= #{filterPageByDeptVO.endDate}]]>
            </if>
        </if>
        <if test="filterPageByDeptVO.isPlacefile != null and filterPageByDeptVO.isPlacefile == 1">
            <if test="filterPageByDeptVO.startDate != null and filterPageByDeptVO.startDate != ''">
                <![CDATA[ AND a.leave_hospital_date >= #{filterPageByDeptVO.startDate}]]>
            </if>
            <if test="filterPageByDeptVO.endDate != null and filterPageByDeptVO.endDate != ''">
                <![CDATA[ AND a.leave_hospital_date <= #{filterPageByDeptVO.endDate}]]>
            </if>
        </if>
        <if test="filterPageByDeptVO.deptName != null and filterPageByDeptVO.deptName != ''">
            AND a.beh_dept_name = #{filterPageByDeptVO.deptName}
        </if>
        <if test="filterPageByDeptVO.doctorName != null and filterPageByDeptVO.doctorName != ''">
            AND a.doctor_name like CONCAT('%', #{filterPageByDeptVO.doctorName}, '%')
        </if>
        GROUP BY
        a.beh_dept_id,
        a.beh_dept_name
        ) t2
        WHERE
        t1.deptId = t2.deptId
        AND t1.deptName = t2.deptName
        )t
    </select>

    <!-- 按模块统计质控缺陷数（分页） -->
    <select id="entryCountGroupByCasePage"  resultType="com.diagbot.dto.NumDTO">
        SELECT
        t.id,
        t.`NAME`,
        t.num,
        t.totleNum,
        t.percent,
        t.percentStr
        FROM
        (
        SELECT
        t1.casesId AS id,
        t1.casesName AS NAME,
        t1.num AS num,
        t2.mrNum,
        t3.originalNum,
        t2.mrNum * t3.originalNum AS totleNum,
        ROUND( t1.num /( t2.mrNum * t3.originalNum ), 4 ) AS percent,
        CONCAT( ROUND( t1.num /( t2.mrNum * t3.originalNum )* 100, 2 ), '%' ) AS percentStr
        FROM
        (
        SELECT
        d.casesId,
        e.NAME AS casesName,
        d.num
        FROM
        (
        SELECT
        c.cases_id AS casesId,
        count(*) AS num
        FROM
        med_behospital_info a,
        med_qcresult_info b,
        med_qcresult_detail c
        WHERE
        a.is_deleted = 'N'
        AND b.is_deleted = 'N'
        AND c.is_deleted = 'N'
        AND a.hospital_id = b.hospital_id
        AND a.hospital_id = c.hospital_id
        AND a.behospital_code = b.behospital_code
        AND a.behospital_code = c.behospital_code
        <if test="filterPageVO.isPlacefile != null and filterPageVO.isPlacefile != ''">
            and a.is_placefile = #{filterPageVO.isPlacefile}
        </if>
        AND a.qc_type_id != 0
        <if test="filterPageVO.hospitalId != null and filterPageVO.hospitalId != ''">
            AND a.hospital_id = #{filterPageVO.hospitalId}
        </if>
        <if test="filterPageVO.isPlacefile != null and filterPageVO.isPlacefile == 0">
            <if test="filterPageVO.startDate != null and filterPageVO.startDate != ''">
                <![CDATA[ AND a.behospital_date >= #{filterPageVO.startDate}]]>
            </if>
            <if test="filterPageVO.endDate != null and filterPageVO.endDate != ''">
                <![CDATA[ AND a.behospital_date <= #{filterPageVO.endDate}]]>
            </if>
        </if>
        <if test="filterPageVO.isPlacefile != null and filterPageVO.isPlacefile == 1">
            <if test="filterPageVO.startDate != null and filterPageVO.startDate != ''">
                <![CDATA[ AND a.leave_hospital_date >= #{filterPageVO.startDate}]]>
            </if>
            <if test="filterPageVO.endDate != null and filterPageVO.endDate != ''">
                <![CDATA[ AND a.leave_hospital_date <= #{filterPageVO.endDate}]]>
            </if>
        </if>
        <if test="filterPageVO.deptName != null and filterPageVO.deptName != ''">
            AND a.beh_dept_name like CONCAT('%', #{filterPageVO.deptName},'%')
        </if>
        GROUP BY
        c.cases_id
        ) d,
        qc_cases e
        WHERE
        e.is_deleted = 'N'
        AND d.casesId = e.id
        <if test="filterPageVO.casesName != null and filterPageVO.casesName != ''">
            AND e.name like CONCAT('%', #{filterPageVO.casesName},'%')
        </if>
        ) t1,
        (
        SELECT
        COUNT(*) AS mrNum
        FROM
        med_behospital_info a,
        med_qcresult_info c
        WHERE
        a.is_deleted = 'N'
        AND c.is_deleted = 'N'
        AND a.hospital_id = c.hospital_id
        AND a.behospital_code = c.behospital_code
        <if test="filterPageVO.isPlacefile != null and filterPageVO.isPlacefile != ''">
            and a.is_placefile = #{filterPageVO.isPlacefile}
        </if>
        AND a.qc_type_id != 0
        <if test="filterPageVO.hospitalId != null and filterPageVO.hospitalId != ''">
            AND a.hospital_id = #{filterPageVO.hospitalId}
        </if>
        <if test="filterPageVO.isPlacefile != null and filterPageVO.isPlacefile == 0">
            <if test="filterPageVO.startDate != null and filterPageVO.startDate != ''">
                <![CDATA[ AND a.behospital_date >= #{filterPageVO.startDate}]]>
            </if>
            <if test="filterPageVO.endDate != null and filterPageVO.endDate != ''">
                <![CDATA[ AND a.behospital_date <= #{filterPageVO.endDate}]]>
            </if>
        </if>
        <if test="filterPageVO.isPlacefile != null and filterPageVO.isPlacefile == 1">
            <if test="filterPageVO.startDate != null and filterPageVO.startDate != ''">
                <![CDATA[ AND a.leave_hospital_date >= #{filterPageVO.startDate}]]>
            </if>
            <if test="filterPageVO.endDate != null and filterPageVO.endDate != ''">
                <![CDATA[ AND a.leave_hospital_date <= #{filterPageVO.endDate}]]>
            </if>
        </if>
        <if test="filterPageVO.deptName != null and filterPageVO.deptName != ''">
            AND a.beh_dept_name like CONCAT('%', #{filterPageVO.deptName},'%')
        </if>
        ) t2,
        (
        SELECT
        a.id AS casesId,
        a.NAME AS casesName,
        count(*) AS originalNum
        FROM
        qc_cases a,
        qc_cases_entry b
        WHERE
        a.is_deleted = 'N'
        AND b.is_deleted = 'N'
        AND a.id = b.cases_id
        <if test="filterPageVO.casesName != null and filterPageVO.casesName != ''">
            AND a.name like CONCAT('%', #{filterPageVO.casesName},'%')
        </if>
        GROUP BY
        a.id,
        a.`name`
        ) t3
        WHERE
        t1.casesId = t3.casesId
        AND t1.casesName = t3.casesName
        ) t
    </select>

    <!-- 缺陷详情（分页） -->
    <select id="entryCountGroupByEntryPage"  resultType="com.diagbot.dto.EntryNumGroupDTO">
        SELECT
        t.*
        FROM
        (
        SELECT
        t1.id,
        t1.NAME,
        t1.casesId,
        t1.casesName,
        t1.ruleType,
        t1.isReject,
        t1.num,
        t2.totleNum,
        ROUND( t1.num / t2.totleNum, 4 ) AS percent,
        CONCAT( ROUND( t1.num / t2.totleNum * 100, 2 ), '%' ) AS percentStr
        FROM
        (
        SELECT
        tt2.id AS id,
        tt2.NAME AS NAME,
        tt2.cases_id AS casesId,
        tt2.cases_name AS casesName,
        tt1.num,
        tt2.rule_type AS ruleType,
        tt1.is_reject AS isReject
        FROM
        (
        SELECT
        d.cases_id,
        d.cases_entry_id,
        d.is_reject,
        count(*) AS num
        FROM
        med_behospital_info a,
        med_qcresult_detail d
        WHERE
        a.is_deleted = 'N'
        AND d.is_deleted = 'N'
        AND a.hospital_id = d.hospital_id
        AND a.behospital_code = d.behospital_code
        <if test="filterPageVO.isPlacefile != null and filterPageVO.isPlacefile != ''">
            and a.is_placefile = #{filterPageVO.isPlacefile}
        </if>
        AND a.qc_type_id != 0
        <if test="filterPageVO.hospitalId != null and filterPageVO.hospitalId != ''">
            AND a.hospital_id = #{filterPageVO.hospitalId}
        </if>
        <if test="filterPageVO.isPlacefile != null and filterPageVO.isPlacefile == 0">
            <if test="filterPageVO.startDate != null and filterPageVO.startDate != ''">
                <![CDATA[ AND a.behospital_date >= #{filterPageVO.startDate}]]>
            </if>
            <if test="filterPageVO.endDate != null and filterPageVO.endDate != ''">
                <![CDATA[ AND a.behospital_date <= #{filterPageVO.endDate}]]>
            </if>
        </if>
        <if test="filterPageVO.isPlacefile != null and filterPageVO.isPlacefile == 1">
            <if test="filterPageVO.startDate != null and filterPageVO.startDate != ''">
                <![CDATA[ AND a.leave_hospital_date >= #{filterPageVO.startDate}]]>
            </if>
            <if test="filterPageVO.endDate != null and filterPageVO.endDate != ''">
                <![CDATA[ AND a.leave_hospital_date <= #{filterPageVO.endDate}]]>
            </if>
        </if>
        <if test="filterPageVO.deptName != null and filterPageVO.deptName != ''">
            AND a.beh_dept_name = #{filterPageVO.deptName}
        </if>
        <if test="filterPageVO.deptId != null and filterPageVO.deptId != ''">
            AND a.beh_dept_id = #{filterPageVO.deptId}
        </if>
        <if test="filterPageVO.doctorName != null and filterPageVO.doctorName != ''">
            AND a.doctor_name = #{filterPageVO.doctorName}
        </if>
        <if test="filterPageVO.casesId != null and filterPageVO.casesId != 0">
            AND d.cases_id = #{filterPageVO.casesId}
        </if>
        <if test="filterPageVO.isReject != null">
            AND d.is_reject = #{filterPageVO.isReject}
        </if>
        GROUP BY
        d.cases_entry_id,
        d.cases_id
        ) tt1,
        qc_cases_entry tt2
        WHERE
        tt2.is_deleted = 'N'
        AND tt1.cases_id = tt2.cases_id
        AND tt1.cases_entry_id = tt2.id
        <if test="filterPageVO.casesName != null and filterPageVO.casesName != ''">
            AND tt2.cases_name like CONCAT('%', #{filterPageVO.casesName},'%')
        </if>
        <if test="filterPageVO.name != null and filterPageVO.name != ''">
            AND tt2.name like CONCAT('%', #{filterPageVO.name},'%')
        </if>
        <if test="filterPageVO.ruleType != null">
            AND tt2.rule_type = #{filterPageVO.ruleType}
        </if>
        ) t1,(
        SELECT
        count(*) AS totleNum
        FROM
        med_behospital_info a,
        med_qcresult_detail d,
        qc_cases_entry e
        WHERE
        a.is_deleted = 'N'
        AND d.is_deleted = 'N'
        AND e.is_deleted = 'N'
        AND a.hospital_id = d.hospital_id
        AND a.behospital_code = d.behospital_code
        <if test="filterPageVO.isPlacefile != null and filterPageVO.isPlacefile != ''">
            and a.is_placefile = #{filterPageVO.isPlacefile}
        </if>
        <if test="filterPageVO.casesId != null and filterPageVO.casesId != 0">
            AND d.cases_id = #{filterPageVO.casesId}
        </if>
        AND e.cases_id = d.cases_id
        AND e.id = d.cases_entry_id
        AND a.qc_type_id != 0
        <if test="filterPageVO.hospitalId != null and filterPageVO.hospitalId != ''">
            AND a.hospital_id = #{filterPageVO.hospitalId}
        </if>
        <if test="filterPageVO.isPlacefile != null and filterPageVO.isPlacefile == 0">
            <if test="filterPageVO.startDate != null and filterPageVO.startDate != ''">
                <![CDATA[ AND a.behospital_date >= #{filterPageVO.startDate}]]>
            </if>
            <if test="filterPageVO.endDate != null and filterPageVO.endDate != ''">
                <![CDATA[ AND a.behospital_date <= #{filterPageVO.endDate}]]>
            </if>
        </if>
        <if test="filterPageVO.isPlacefile != null and filterPageVO.isPlacefile == 1">
            <if test="filterPageVO.startDate != null and filterPageVO.startDate != ''">
                <![CDATA[ AND a.leave_hospital_date >= #{filterPageVO.startDate}]]>
            </if>
            <if test="filterPageVO.endDate != null and filterPageVO.endDate != ''">
                <![CDATA[ AND a.leave_hospital_date <= #{filterPageVO.endDate}]]>
            </if>
        </if>
        <if test="filterPageVO.doctorName != null and filterPageVO.doctorName != ''">
            AND a.doctor_name = #{filterPageVO.doctorName}
        </if>
        <if test="filterPageVO.deptName != null and filterPageVO.deptName != ''">
            AND a.beh_dept_name  =  #{filterPageVO.deptName}
        </if>
        <if test="filterPageVO.deptId != null and filterPageVO.deptId != ''">
            AND a.beh_dept_id = #{filterPageVO.deptId}
        </if>
        <if test="filterPageVO.isReject != null">
            AND d.is_reject = #{filterPageVO.isReject}
        </if>
        <if test="filterPageVO.ruleType != null">
            AND e.rule_type = #{filterPageVO.ruleType}
        </if>
        ) t2
        )t
    </select>

    <!-- 缺陷详情（分页）湘雅 -->
    <select id="entryCountGroupXYByEntryPage"  resultType="com.diagbot.dto.EntryNumGroupDTO">
        SELECT
        t.*
        FROM
        (
        SELECT
        t1.id,
        t1.NAME,
        t1.casesId,
        t1.casesName,
        t1.ruleType,
        t1.isReject,
        t1.num,
        t2.totleNum,
        ROUND( t1.num / t2.totleNum, 4 ) AS percent,
        CONCAT( ROUND( t1.num / t2.totleNum * 100, 2 ), '%' ) AS percentStr
        FROM
        (
        SELECT
        tt2.id AS id,
        tt2.NAME AS NAME,
        tt2.cases_id AS casesId,
        tt2.cases_name AS casesName,
        tt1.num,
        tt2.rule_type AS ruleType,
        tt1.is_reject AS isReject
        FROM
        (
        SELECT
        d.cases_id,
        d.cases_entry_id,
        d.is_reject,
        count(*) AS num
        FROM
        med_behospital_info a,
        med_home_page b,
        med_qcresult_detail d
        WHERE
        a.is_deleted = 'N'
        AND d.is_deleted = 'N'
        AND b.is_deleted = 'N'
        AND a.hospital_id = b.hospital_id
        AND a.hospital_id = d.hospital_id
        AND a.behospital_code = b.behospital_code
        AND a.behospital_code = d.behospital_code
        AND d.cases_id = 243
        <if test="filterPageVO.isPlacefile != null and filterPageVO.isPlacefile != ''">
            and a.is_placefile = #{filterPageVO.isPlacefile}
        </if>
        AND a.qc_type_id != 0
        <if test="filterPageVO.hospitalId != null and filterPageVO.hospitalId != ''">
            AND a.hospital_id = #{filterPageVO.hospitalId}
        </if>
        <if test="filterPageVO.isPlacefile != null and filterPageVO.isPlacefile == 0">
            <if test="filterPageVO.startDate != null and filterPageVO.startDate != ''">
                <![CDATA[ AND a.behospital_date >= #{filterPageVO.startDate}]]>
            </if>
            <if test="filterPageVO.endDate != null and filterPageVO.endDate != ''">
                <![CDATA[ AND a.behospital_date <= #{filterPageVO.endDate}]]>
            </if>
        </if>
        <if test="filterPageVO.isPlacefile != null and filterPageVO.isPlacefile == 1">
            <if test="filterPageVO.startDate != null and filterPageVO.startDate != ''">
                <![CDATA[ AND a.leave_hospital_date >= #{filterPageVO.startDate}]]>
            </if>
            <if test="filterPageVO.endDate != null and filterPageVO.endDate != ''">
                <![CDATA[ AND a.leave_hospital_date <= #{filterPageVO.endDate}]]>
            </if>
        </if>
        <if test="filterPageVO.deptName != null and filterPageVO.deptName != ''">
            AND a.beh_dept_name = #{filterPageVO.deptName}
        </if>
        <if test="filterPageVO.deptId != null and filterPageVO.deptId != ''">
            AND a.beh_dept_id = #{filterPageVO.deptId}
        </if>
        <if test="filterPageVO.doctorName != null and filterPageVO.doctorName != ''">
            AND(case
            when b.attending_doctor != '' and b.attending_doctor is not null then b.attending_doctor
            when (b.attending_doctor = '' or b.attending_doctor is null) and
            b.director_doctor != '' and b.director_doctor is not null
            then b.director_doctor
            when (b.attending_doctor = '' or b.attending_doctor is null) and
            (b.director_doctor = '' or b.director_doctor is null) and
            b.behospital_doctor != '' and b.behospital_doctor is not null
            then b.behospital_doctor end ) = #{filterPageVO.doctorName}
        </if>
        <if test="filterPageVO.professor != null and filterPageVO.professor != ''">
            AND(case
            when b.attending_doctor != '' and b.attending_doctor is not null then '主治医师'
            when (b.attending_doctor = '' or b.attending_doctor is null) and
            b.director_doctor != '' and b.director_doctor is not null
            then '主任医师'
            when (b.attending_doctor = '' or b.attending_doctor is null) and
            (b.director_doctor = '' or b.director_doctor is null) and
            b.behospital_doctor != '' and b.behospital_doctor is not null
            then '住院医师' end) = #{filterPageVO.professor}
        </if>
        <if test="filterPageVO.casesId != null and filterPageVO.casesId != 0">
            AND d.cases_id = #{filterPageVO.casesId}
        </if>
        <if test="filterPageVO.isReject != null">
            AND d.is_reject = #{filterPageVO.isReject}
        </if>
        GROUP BY
        d.cases_entry_id,
        d.cases_id
        ) tt1,
        qc_cases_entry tt2
        WHERE
        tt2.is_deleted = 'N'
        AND tt1.cases_id = tt2.cases_id
        AND tt1.cases_entry_id = tt2.id
        <if test="filterPageVO.casesName != null and filterPageVO.casesName != ''">
            AND tt2.cases_name like CONCAT('%', #{filterPageVO.casesName},'%')
        </if>
        <if test="filterPageVO.name != null and filterPageVO.name != ''">
            AND tt2.name like CONCAT('%', #{filterPageVO.name},'%')
        </if>
        <if test="filterPageVO.ruleType != null">
            AND tt2.rule_type = #{filterPageVO.ruleType}
        </if>
        ) t1,(
        SELECT
        count(*) AS totleNum
        FROM
        med_behospital_info a,
        med_home_page b,
        med_qcresult_detail d,
        qc_cases_entry e
        WHERE
        a.is_deleted = 'N'
        AND b.is_deleted = 'N'
        AND d.is_deleted = 'N'
        AND e.is_deleted = 'N'
        AND a.hospital_id = b.hospital_id
        AND a.hospital_id = d.hospital_id
        AND a.behospital_code = b.behospital_code
        AND a.behospital_code = d.behospital_code
        <if test="filterPageVO.isPlacefile != null and filterPageVO.isPlacefile != ''">
            and a.is_placefile = #{filterPageVO.isPlacefile}
        </if>
        AND e.cases_id = d.cases_id
        AND e.id = d.cases_entry_id
        AND e.cases_id = 243
        AND a.qc_type_id != 0
        <if test="filterPageVO.hospitalId != null and filterPageVO.hospitalId != ''">
            AND a.hospital_id = #{filterPageVO.hospitalId}
        </if>
        <if test="filterPageVO.isPlacefile != null and filterPageVO.isPlacefile == 0">
            <if test="filterPageVO.startDate != null and filterPageVO.startDate != ''">
                <![CDATA[ AND a.behospital_date >= #{filterPageVO.startDate}]]>
            </if>
            <if test="filterPageVO.endDate != null and filterPageVO.endDate != ''">
                <![CDATA[ AND a.behospital_date <= #{filterPageVO.endDate}]]>
            </if>
        </if>
        <if test="filterPageVO.isPlacefile != null and filterPageVO.isPlacefile == 1">
            <if test="filterPageVO.startDate != null and filterPageVO.startDate != ''">
                <![CDATA[ AND a.leave_hospital_date >= #{filterPageVO.startDate}]]>
            </if>
            <if test="filterPageVO.endDate != null and filterPageVO.endDate != ''">
                <![CDATA[ AND a.leave_hospital_date <= #{filterPageVO.endDate}]]>
            </if>
        </if>
        <if test="filterPageVO.doctorName != null and filterPageVO.doctorName != ''">
            AND(case
            when b.attending_doctor != '' and b.attending_doctor is not null then b.attending_doctor
            when (b.attending_doctor = '' or b.attending_doctor is null) and
            b.director_doctor != '' and b.director_doctor is not null
            then b.director_doctor
            when (b.attending_doctor = '' or b.attending_doctor is null) and
            (b.director_doctor = '' or b.director_doctor is null) and
            b.behospital_doctor != '' and b.behospital_doctor is not null
            then b.behospital_doctor end ) = #{filterPageVO.doctorName}
        </if>
        <if test="filterPageVO.professor != null and filterPageVO.professor != ''">
            AND(case
            when b.attending_doctor != '' and b.attending_doctor is not null then '主治医师'
            when (b.attending_doctor = '' or b.attending_doctor is null) and
            b.director_doctor != '' and b.director_doctor is not null
            then '主任医师'
            when (b.attending_doctor = '' or b.attending_doctor is null) and
            (b.director_doctor = '' or b.director_doctor is null) and
            b.behospital_doctor != '' and b.behospital_doctor is not null
            then '住院医师' end) = #{filterPageVO.professor}
        </if>
        <if test="filterPageVO.deptName != null and filterPageVO.deptName != ''">
            AND a.beh_dept_name  =  #{filterPageVO.deptName}
        </if>
        <if test="filterPageVO.deptId != null and filterPageVO.deptId != ''">
            AND a.beh_dept_id = #{filterPageVO.deptId}
        </if>
        <if test="filterPageVO.isReject != null">
            AND d.is_reject = #{filterPageVO.isReject}
        </if>
        <if test="filterPageVO.ruleType != null">
            AND e.rule_type = #{filterPageVO.ruleType}
        </if>
        ) t2
        )t
    </select>

    <!-- 质控平均分按科室统计（分页） -->
    <select id="getAverageScoreByDeptPage"  resultType="com.diagbot.dto.AverageStatisticsDTO">
        SELECT t.*
        FROM
        (SELECT
        a.beh_dept_id AS id,
        a.beh_dept_name AS name,
        ROUND( sum( CAST( c.score_res AS DECIMAL ( 18, 2 ) )), 2 ) AS totleValue,
        ROUND( sum( CAST( c.score_res AS DECIMAL ( 18, 2 ) ))/ count(*), 2 ) AS averageValue,
        count(*) AS num
        FROM
        med_behospital_info a,
        med_qcresult_info c
        WHERE
        a.is_deleted = 'N'
        AND c.is_deleted = 'N'
        AND a.hospital_id = c.hospital_id
        AND a.behospital_code = c.behospital_code
        <if test="filterPageVO.isPlacefile != null and filterPageVO.isPlacefile != ''">
            and a.is_placefile = #{filterPageVO.isPlacefile}
        </if>
        <![CDATA[AND a.qc_type_id <>0 ]]>
        <if test="filterPageVO.hospitalId != null and filterPageVO.hospitalId != ''">
            AND a.hospital_id = #{filterPageVO.hospitalId}
        </if>
        <if test="filterPageVO.isPlacefile != null and filterPageVO.isPlacefile == 0">
            <if test="filterPageVO.startDate != null and filterPageVO.startDate != ''">
                <![CDATA[ AND a.behospital_date >= #{filterPageVO.startDate}]]>
            </if>
            <if test="filterPageVO.endDate != null and filterPageVO.endDate != ''">
                <![CDATA[ AND a.behospital_date <= #{filterPageVO.endDate}]]>
            </if>
        </if>
        <if test="filterPageVO.isPlacefile != null and filterPageVO.isPlacefile == 1">
            <if test="filterPageVO.startDate != null and filterPageVO.startDate != ''">
                <![CDATA[ AND a.leave_hospital_date >= #{filterPageVO.startDate}]]>
            </if>
            <if test="filterPageVO.endDate != null and filterPageVO.endDate != ''">
                <![CDATA[ AND a.leave_hospital_date <= #{filterPageVO.endDate}]]>
            </if>
        </if>
        <if test="filterPageVO.name != null and filterPageVO.name != ''">
            AND a.beh_dept_name like CONCAT('%', #{filterPageVO.name},'%')
        </if>
        GROUP BY
        a.beh_dept_id,
        a.beh_dept_name
        ORDER BY averageValue DESC ) t
    </select>

    <!-- 按科室统计质控病历数（分页） -->
    <select id="levelPercentGroupByDeptPage" resultType="com.diagbot.dto.QcResultPercentDTO">
        SELECT t.*
        FROM
        (SELECT
        a.beh_dept_id AS deptId,
        a.beh_dept_name AS deptName,
        count(*) AS totleNum,
        sum( c.`level` = '甲' ) AS firstLevelNum,
        sum( c.`level` = '乙' ) AS secondLevelNum,
        sum( c.`level` = '丙' ) AS thirdLevelNum,
        concat( ROUND( sum( c.`level` = '甲' )/ count(*)* 100, 2 ), '%' ) AS firstPercent,
        concat( ROUND( sum( c.`level` = '乙' )/ count(*)* 100, 2 ), '%' ) AS sencondPercent,
        concat( ROUND( sum( c.`level` = '丙' )/ count(*)* 100, 2 ), '%' ) AS thirdPercent
        FROM
        med_behospital_info a,
        med_qcresult_info c
        WHERE
        a.is_deleted = 'N'
        AND c.is_deleted = 'N'
        AND a.hospital_id = c.hospital_id
        AND a.behospital_code = c.behospital_code
        <if test="filterPageVO.isPlacefile != null and filterPageVO.isPlacefile != ''">
            and a.is_placefile = #{filterPageVO.isPlacefile}
        </if>
        <![CDATA[AND a.qc_type_id <>0 ]]>
        <if test="filterPageVO.hospitalId != null and filterPageVO.hospitalId != ''">
            AND a.hospital_id = #{filterPageVO.hospitalId}
        </if>
        <if test="filterPageVO.isPlacefile != null and filterPageVO.isPlacefile == 0">
            <if test="filterPageVO.startDate != null and filterPageVO.startDate != ''">
                <![CDATA[ AND a.behospital_date >= #{filterPageVO.startDate}]]>
            </if>
            <if test="filterPageVO.endDate != null and filterPageVO.endDate != ''">
                <![CDATA[ AND a.behospital_date <= #{filterPageVO.endDate}]]>
            </if>
        </if>
        <if test="filterPageVO.isPlacefile != null and filterPageVO.isPlacefile == 1">
            <if test="filterPageVO.startDate != null and filterPageVO.startDate != ''">
                <![CDATA[ AND a.leave_hospital_date >= #{filterPageVO.startDate}]]>
            </if>
            <if test="filterPageVO.endDate != null and filterPageVO.endDate != ''">
                <![CDATA[ AND a.leave_hospital_date <= #{filterPageVO.endDate}]]>
            </if>
        </if>
        <if test="filterPageVO.name != null and filterPageVO.name != ''">
            AND a.beh_dept_name like CONCAT('%', #{filterPageVO.name},'%')
        </if>
        GROUP BY
        a.beh_dept_id,
        a.beh_dept_name
        order by ROUND( sum( c.`level` = '甲' )/ count(*)* 100, 2 ) DESC,
        ROUND( sum( c.`level` = '乙' )/ count(*)* 100, 2 ) DESC,
        ROUND( sum( c.`level` = '丙' )/ count(*)* 100, 2 ) DESC) t
    </select>

    <!-- 按模块统计质控缺陷数-科室（分页） -->
    <select id="entryCountGroupByCaseAndDeptPage" resultType="com.diagbot.dto.NumDTO">
        SELECT
        t.id,
        t.`NAME`,
        t.deptId,
        t.deptName,
        t.num,
        t.totleNum,
        t.percent,
        t.percentStr
        FROM
        (
        SELECT
        t1.caseId AS id,
        t1.caseName AS NAME,
        t1.deptId,
        t1.deptName,
        t1.num AS num,
        t2.mrNum,
        t3.originalNum,
        t2.mrNum * t3.originalNum AS totleNum,
        ROUND( t1.num /( t2.mrNum * t3.originalNum ), 4 ) AS percent,
        CONCAT( ROUND( t1.num /( t2.mrNum * t3.originalNum )* 100, 2 ), '%' ) AS percentStr
        FROM
        (
        SELECT
        f.caseId,
        e.NAME AS caseName,
        f.deptId,
        f.deptName,
        f.num
        FROM
        (
        SELECT
        a.beh_dept_id AS deptId,
        a.beh_dept_name AS deptName,
        d.cases_id AS caseId,
        count(*) AS num
        FROM
        med_behospital_info a,
        med_qcresult_info c,
        med_qcresult_detail d,
        sys_user_dept g
        WHERE
        a.is_deleted = 'N'
        AND c.is_deleted = 'N'
        AND d.is_deleted = 'N'
        AND g.is_deleted = 'N'
        AND a.hospital_id = c.hospital_id
        AND a.hospital_id = d.hospital_id
        AND a.hospital_id = g.hospital_id
        AND a.behospital_code = c.behospital_code
        AND a.behospital_code = d.behospital_code
        AND a.beh_dept_id = g.dept_id
        <if test="filterPageByDeptVO.isPlacefile != null and filterPageByDeptVO.isPlacefile != ''">
            and a.is_placefile = #{filterPageByDeptVO.isPlacefile}
        </if>
        AND a.qc_type_id != 0
        <if test="filterPageByDeptVO.userId!=null">
            AND g.user_id = #{filterPageByDeptVO.userId}
        </if>
        <if test="filterPageByDeptVO.hospitalId != null and filterPageByDeptVO.hospitalId != ''">
            AND a.hospital_id = #{filterPageByDeptVO.hospitalId}
        </if>
        <if test="filterPageByDeptVO.isPlacefile != null and filterPageByDeptVO.isPlacefile == 0">
            <if test="filterPageByDeptVO.startDate != null and filterPageByDeptVO.startDate != ''">
                <![CDATA[ AND a.behospital_date >= #{filterPageByDeptVO.startDate}]]>
            </if>
            <if test="filterPageByDeptVO.endDate != null and filterPageByDeptVO.endDate != ''">
                <![CDATA[ AND a.behospital_date <= #{filterPageByDeptVO.endDate}]]>
            </if>
        </if>
        <if test="filterPageByDeptVO.isPlacefile != null and filterPageByDeptVO.isPlacefile == 1">
            <if test="filterPageByDeptVO.startDate != null and filterPageByDeptVO.startDate != ''">
                <![CDATA[ AND a.leave_hospital_date >= #{filterPageByDeptVO.startDate}]]>
            </if>
            <if test="filterPageByDeptVO.endDate != null and filterPageByDeptVO.endDate != ''">
                <![CDATA[ AND a.leave_hospital_date <= #{filterPageByDeptVO.endDate}]]>
            </if>
        </if>
        <if test="filterPageByDeptVO.deptName != null and filterPageByDeptVO.deptName != ''">
            AND a.beh_dept_name = #{filterPageByDeptVO.deptName}
        </if>
        <if test="filterPageByDeptVO.doctorName != null and filterPageByDeptVO.doctorName != ''">
            and a.doctor_name like CONCAT('%', #{filterPageByDeptVO.doctorName}, '%')
        </if>
        GROUP BY
        d.cases_id,
        a.beh_dept_id,
        a.beh_dept_name
        ) f,
        qc_cases e
        WHERE
        e.is_deleted = 'N'
        AND f.caseId = e.id
        <if test="filterPageByDeptVO.name != null and filterPageByDeptVO.name != ''">
            AND e.name like CONCAT('%', #{filterPageByDeptVO.name},'%')
        </if>
        ) t1,
        (
        SELECT
        a.beh_dept_id AS deptId,
        a.beh_dept_name AS deptName,
        COUNT(*) AS mrNum
        FROM
        med_behospital_info a,
        med_qcresult_info c,
        sys_user_dept d
        WHERE
        a.is_deleted = 'N'
        AND c.is_deleted = 'N'
        AND d.is_deleted = 'N'
        AND a.hospital_id = c.hospital_id
        AND a.hospital_id = d.hospital_id
        AND a.behospital_code = c.behospital_code
        AND a.beh_dept_id = d.dept_id
        <if test="filterPageByDeptVO.isPlacefile != null and filterPageByDeptVO.isPlacefile != ''">
            and a.is_placefile = #{filterPageByDeptVO.isPlacefile}
        </if>
        AND a.qc_type_id != 0
        <if test="filterPageByDeptVO.userId!=null">
            AND d.user_id = #{filterPageByDeptVO.userId}
        </if>
        <if test="filterPageByDeptVO.hospitalId != null and filterPageByDeptVO.hospitalId != ''">
            AND a.hospital_id = #{filterPageByDeptVO.hospitalId}
        </if>
        <if test="filterPageByDeptVO.isPlacefile != null and filterPageByDeptVO.isPlacefile == 0">
            <if test="filterPageByDeptVO.startDate != null and filterPageByDeptVO.startDate != ''">
                <![CDATA[ AND a.behospital_date >= #{filterPageByDeptVO.startDate}]]>
            </if>
            <if test="filterPageByDeptVO.endDate != null and filterPageByDeptVO.endDate != ''">
                <![CDATA[ AND a.behospital_date <= #{filterPageByDeptVO.endDate}]]>
            </if>
        </if>
        <if test="filterPageByDeptVO.isPlacefile != null and filterPageByDeptVO.isPlacefile == 1">
            <if test="filterPageByDeptVO.startDate != null and filterPageByDeptVO.startDate != ''">
                <![CDATA[ AND a.leave_hospital_date >= #{filterPageByDeptVO.startDate}]]>
            </if>
            <if test="filterPageByDeptVO.endDate != null and filterPageByDeptVO.endDate != ''">
                <![CDATA[ AND a.leave_hospital_date <= #{filterPageByDeptVO.endDate}]]>
            </if>
        </if>
        <if test="filterPageByDeptVO.deptName != null and filterPageByDeptVO.deptName != ''">
            AND a.beh_dept_name = #{filterPageByDeptVO.deptName}
        </if>
        <if test="filterPageByDeptVO.doctorName != null and filterPageByDeptVO.doctorName != ''">
            and a.doctor_name like CONCAT('%', #{filterPageByDeptVO.doctorName}, '%')
        </if>
        GROUP BY
        a.beh_dept_id,
        a.beh_dept_name
        ) t2,
        (
        SELECT
        a.id AS caseId,
        a.NAME AS caseName,
        count(*) AS originalNum
        FROM
        qc_cases a,
        qc_cases_entry b
        WHERE
        a.is_deleted = 'N'
        AND b.is_deleted = 'N'
        AND a.id = b.cases_id
        GROUP BY
        a.id,
        a.`name`
        ) t3
        WHERE
        t1.caseId = t3.caseId
        AND t1.caseName = t3.caseName
        AND t1.deptId = t2.deptId
        AND t1.deptName = t2.deptName
        ) t
    </select>

    <!-- 缺陷详情-科室（分页） -->
    <select id="entryCountGroupByEntryAndDeptPage"  resultType="com.diagbot.dto.EntryNumGroupDTO">
        SELECT
        t.*
        FROM
        (
        SELECT
        t1.deptId,
        t1.deptName,
        t1.id,
        t1.NAME,
        t1.casesId,
        t1.casesName,
        t1.ruleType,
        t1.isReject,
        t1.num,
        t2.totleNum,
        ROUND( t1.num / t2.totleNum, 4 ) AS percent,
        CONCAT( ROUND( t1.num / t2.totleNum * 100, 2 ), '%' ) AS percentStr
        FROM
        (
        SELECT
        tt1.beh_dept_id AS deptId,
        tt1.beh_dept_name AS deptName,
        tt2.id AS id,
        tt2.NAME AS NAME,
        tt2.cases_id AS casesId,
        tt2.cases_name AS casesName,
        tt1.num,
        tt2.rule_type AS ruleType,
        tt1.is_reject AS isReject
        FROM
        (
        SELECT
        a.beh_dept_id,
        a.beh_dept_name,
        d.cases_id,
        d.cases_entry_id,
        d.is_reject,
        count(*) AS num
        FROM
        med_behospital_info a,
        med_qcresult_detail d,
        sys_user_dept f
        WHERE
        a.is_deleted = 'N'
        AND d.is_deleted = 'N'
        AND f.is_deleted = 'N'
        AND a.hospital_id = d.hospital_id
        AND a.hospital_id = f.hospital_id
        AND a.behospital_code = d.behospital_code
        AND a.beh_dept_id = f.dept_id
        <if test="filterPageByDeptVO.userId!=null">
            AND f.user_id = #{filterPageByDeptVO.userId}
        </if>
        <if test="filterPageByDeptVO.isPlacefile != null and filterPageByDeptVO.isPlacefile != ''">
            and a.is_placefile = #{filterPageByDeptVO.isPlacefile}
        </if>
        AND a.qc_type_id != 0
        <if test="filterPageByDeptVO.hospitalId != null and filterPageByDeptVO.hospitalId != ''">
            AND a.hospital_id = #{filterPageByDeptVO.hospitalId}
        </if>
        <if test="filterPageByDeptVO.isPlacefile != null and filterPageByDeptVO.isPlacefile == 0">
            <if test="filterPageByDeptVO.startDate != null and filterPageByDeptVO.startDate != ''">
                <![CDATA[ AND a.behospital_date >= #{filterPageByDeptVO.startDate}]]>
            </if>
            <if test="filterPageByDeptVO.endDate != null and filterPageByDeptVO.endDate != ''">
                <![CDATA[ AND a.behospital_date <= #{filterPageByDeptVO.endDate}]]>
            </if>
        </if>
        <if test="filterPageByDeptVO.isPlacefile != null and filterPageByDeptVO.isPlacefile == 1">
            <if test="filterPageByDeptVO.startDate != null and filterPageByDeptVO.startDate != ''">
                <![CDATA[ AND a.leave_hospital_date >= #{filterPageByDeptVO.startDate}]]>
            </if>
            <if test="filterPageByDeptVO.endDate != null and filterPageByDeptVO.endDate != ''">
                <![CDATA[ AND a.leave_hospital_date <= #{filterPageByDeptVO.endDate}]]>
            </if>
        </if>
        <if test="filterPageByDeptVO.deptName != null and filterPageByDeptVO.deptName != ''">
            AND a.beh_dept_name = #{filterPageByDeptVO.deptName}
        </if>
        <if test="filterPageByDeptVO.deptId != null and filterPageByDeptVO.deptId != ''">
            AND a.beh_dept_id = #{filterPageByDeptVO.deptId}
        </if>
        <if test="filterPageByDeptVO.doctorId != null and filterPageByDeptVO.doctorId != ''">
            AND a.doctor_id = #{filterPageByDeptVO.doctorId}
        </if>
        <if test="filterPageByDeptVO.doctorName != null and filterPageByDeptVO.doctorName != ''">
            AND a.doctor_name =  #{filterPageByDeptVO.doctorName}
        </if>
        <if test="filterPageByDeptVO.casesId != null and filterPageByDeptVO.casesId != 0">
            AND d.cases_id = #{filterPageByDeptVO.casesId}
        </if>
        <if test="filterPageByDeptVO.isReject != null">
            AND d.is_reject = #{filterPageByDeptVO.isReject}
        </if>
        GROUP BY
        d.cases_entry_id,
        d.cases_id
        ) tt1,
        qc_cases_entry tt2
        WHERE
        tt2.is_deleted = 'N'
        AND tt1.cases_id = tt2.cases_id
        AND tt1.cases_entry_id = tt2.id
        <if test="filterPageByDeptVO.casesName != null and filterPageByDeptVO.casesName != ''">
            AND tt2.cases_name like CONCAT('%', #{filterPageByDeptVO.casesName},'%')
        </if>
        <if test="filterPageByDeptVO.name != null and filterPageByDeptVO.name != ''">
            AND tt2.name like CONCAT('%', #{filterPageByDeptVO.name},'%')
        </if>
        <if test="filterPageByDeptVO.ruleType != null">
            AND tt2.rule_type = #{filterPageByDeptVO.ruleType}
        </if>
        ) t1,(
        SELECT
        count(*) AS totleNum
        FROM
        med_behospital_info a,
        med_qcresult_detail d,
        qc_cases_entry e,
        sys_user_dept f
        WHERE
        a.is_deleted = 'N'
        AND d.is_deleted = 'N'
        AND e.is_deleted = 'N'
        AND f.is_deleted = 'N'
        AND a.hospital_id = d.hospital_id
        AND a.hospital_id = f.hospital_id
        AND a.behospital_code = d.behospital_code
        AND a.beh_dept_id = f.dept_id
        <if test="filterPageByDeptVO.userId!=null">
            AND f.user_id = #{filterPageByDeptVO.userId}
        </if>
        <if test="filterPageByDeptVO.isPlacefile != null and filterPageByDeptVO.isPlacefile != ''">
            and a.is_placefile = #{filterPageByDeptVO.isPlacefile}
        </if>
        AND e.cases_id = d.cases_id
        AND e.cases_id = 243
        AND e.id = d.cases_entry_id
        AND a.qc_type_id != 0
        <if test="filterPageByDeptVO.hospitalId != null and filterPageByDeptVO.hospitalId != ''">
            AND a.hospital_id = #{filterPageByDeptVO.hospitalId}
        </if>
        <if test="filterPageByDeptVO.isPlacefile != null and filterPageByDeptVO.isPlacefile == 0">
            <if test="filterPageByDeptVO.startDate != null and filterPageByDeptVO.startDate != ''">
                <![CDATA[ AND a.behospital_date >= #{filterPageByDeptVO.startDate}]]>
            </if>
            <if test="filterPageByDeptVO.endDate != null and filterPageByDeptVO.endDate != ''">
                <![CDATA[ AND a.behospital_date <= #{filterPageByDeptVO.endDate}]]>
            </if>
        </if>
        <if test="filterPageByDeptVO.isPlacefile != null and filterPageByDeptVO.isPlacefile == 1">
            <if test="filterPageByDeptVO.startDate != null and filterPageByDeptVO.startDate != ''">
                <![CDATA[ AND a.leave_hospital_date >= #{filterPageByDeptVO.startDate}]]>
            </if>
            <if test="filterPageByDeptVO.endDate != null and filterPageByDeptVO.endDate != ''">
                <![CDATA[ AND a.leave_hospital_date <= #{filterPageByDeptVO.endDate}]]>
            </if>
        </if>
        <if test="filterPageByDeptVO.deptName != null and filterPageByDeptVO.deptName != ''">
            AND a.beh_dept_name  =  #{filterPageByDeptVO.deptName}
        </if>
        <if test="filterPageByDeptVO.doctorName != null and filterPageByDeptVO.doctorName != ''">
            AND a.doctor_name = #{filterPageByDeptVO.doctorName}
        </if>
        <if test="filterPageByDeptVO.deptId != null and filterPageByDeptVO.deptId != ''">
            AND a.beh_dept_id = #{filterPageByDeptVO.deptId}
        </if>
        <if test="filterPageByDeptVO.doctorId != null and filterPageByDeptVO.doctorId != ''">
            AND a.doctor_id = #{filterPageByDeptVO.doctorId}
        </if>
        <if test="filterPageByDeptVO.isReject != null">
            AND d.is_reject = #{filterPageByDeptVO.isReject}
        </if>
        <if test="filterPageByDeptVO.ruleType != null">
            AND e.rule_type = #{filterPageByDeptVO.ruleType}
        </if>
        ) t2
        )t
    </select>
</mapper>
