<?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="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.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>
        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.deptName != null and filterPageVO.deptName != ''">
            AND a.beh_dept_name  =  #{filterPageVO.deptName}
        </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>

</mapper>
