<?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.emr.dao.V_CountMapper">
  <resultMap id="BaseResultMap" type="com.emr.entity.V_Count">
    <!--<id column="id" jdbcType="INTEGER" property="id" />-->
    <result column="deptCode" jdbcType="NVARCHAR" property="deptCode" />
    <result column="deptName" jdbcType="NVARCHAR" property="deptName" />
    <result column="inNum" jdbcType="INTEGER" property="inNum" />
    <result column="outNum" jdbcType="INTEGER" property="outNum" />
    <result column="fileNum" jdbcType="INTEGER" property="fileNum" />
    <result column="unfileNum" jdbcType="INTEGER" property="unfileNum" />
    <result column="deathNum" jdbcType="INTEGER" property="deathNum" />
    <result column="death2Num" jdbcType="INTEGER" property="death2Num" />
    <result column="death3Num" jdbcType="INTEGER" property="death3Num" />
    <result column="fileRate" jdbcType="NVARCHAR" property="fileRate" />
    <result column="day2Rate" jdbcType="NVARCHAR" property="day2Rate" />
    <result column="day3Rate" jdbcType="NVARCHAR" property="day3Rate" />
    <result column="day7Rate" jdbcType="NVARCHAR" property="day7Rate"/>
    <result column="startDate" jdbcType="NVARCHAR" property="startDate"/>
    <result column="endDate" jdbcType="NVARCHAR" property="endDate"/>
    <result column="overdue" jdbcType="NVARCHAR" property="overdue"/>
    <result column="timeOutNum" jdbcType="INTEGER" property="timeOutNum"/>
    <result column="inTime" jdbcType="INTEGER" property="inTime"/>
    <result column="isOutNum" jdbcType="INTEGER" property="isOutNum"/>
    <result column="timeOutRate" jdbcType="NVARCHAR" property="timeOutRate"/>
    <result column="inTimeRate" jdbcType="NVARCHAR" property="inTimeRate"/>
  </resultMap>
  <resultMap id="BaseResultMap2" type="com.emr.entity.V_Count2">
    <result column="deptCode" jdbcType="NVARCHAR" property="deptCode"/>
    <result column="deptName" jdbcType="NVARCHAR" property="deptName"/>
    <result column="name" jdbcType="NVARCHAR" property="name"/>
    <result column="doctorInCharge" jdbcType="NVARCHAR" property="doctorInCharge"/>
    <result column="dischargeDateTime" jdbcType="NVARCHAR" property="dischargeDateTime"/>
    <result column="admissionDateTime" jdbcType="NVARCHAR" property="admissionDateTime"/>
    <result column="total" jdbcType="NVARCHAR" property="total"/>
    <result column="dayNum" jdbcType="NVARCHAR" property="dayNum"/>
    <result column="expried" jdbcType="NVARCHAR" property="expried"/>
    <result column="expiredDay" jdbcType="NVARCHAR" property="expiredDay"/>
  </resultMap>
  <sql id="Base_Column_List">
    deptCode, deptName, inNum, outNum, fileNum, unfileNum, deathNum, death2Num, death3Num, fileRate,
    day2Rate, day3Rate, day7Rate
  </sql>

  <select id="selectByCol" parameterType="com.emr.entity.V_Count" resultMap="BaseResultMap">
      SELECT a.dept_name deptCode,ISNULL(e.name, a.dept_name) deptName,
      a.deathNum,a.death2Num,a.death3Num,a.fileNum,	a.inNum,	a.isOutNum,	a.unfileNum,
      ISNULL((a.day2-a.death2Num)/NULLIF((a.fileNum-a.deathNum), 0)  ,'0') as day2Rate,
      ISNULL((a.day3-a.death3Num)/NULLIF((a.fileNum-a.deathNum), 0)  ,'0') as day3Rate,
      ISNULL((a.day7)/NULLIF((a.fileNum), 0)  ,'0') as day7Rate,
      ISNULL(b.timeOutNum, '0') timeOutNum,
      ISNULL(
          case when right(CAST(fileNum AS FLOAT)/ NULLIF (isOutNum, 0) * 100,2)='00'
          then CONVERT(VARCHAR,fileNum / NULLIF (isOutNum, 0) * 100)
          else CONVERT(VARCHAR,ROUND(CAST(fileNum AS FLOAT)/ NULLIF (isOutNum, 0) * 100,2)) end
      ,'0')  fileRate,
      ISNULL(
          case when ISNULL(timeOutNum, '0')='0'
          then '0'
          when right(CAST(timeOutNum AS FLOAT)/ NULLIF (isOutNum, 0) * 100,2)='00'
          then CONVERT(VARCHAR,timeOutNum / NULLIF (isOutNum, 0) * 100)
          else CONVERT(VARCHAR,ROUND(CAST(timeOutNum AS FLOAT)/ NULLIF (isOutNum, 0) * 100,2)+'') end
      ,'0')  timeOutRate,
      ISNULL((isOutNum - timeOutNum), '0') AS inTime,
      ISNULL(
          case when ISNULL(timeOutNum, '0')='0'
          then '100'
          when right(CAST((isOutNum - timeOutNum) AS FLOAT)/ NULLIF (isOutNum, 0) * 100,2)='00'
          then CONVERT(VARCHAR,(isOutNum - timeOutNum) / NULLIF (isOutNum, 0) * 100)
          else CONVERT(VARCHAR,ROUND(CAST((isOutNum - timeOutNum) AS FLOAT)/ NULLIF (isOutNum, 0) * 100,2)) end
      ,'0')  inTimeRate
      FROM(
        select dept_name,SUM(CASE WHEN ArchiveState IN ('在院') AND Is_Valid != 1
    <choose>
      <when test="startDate != null and startDate != '' and endDate != null and endDate != ''">
        and CONVERT(VARCHAR(10),discharge_date_time,120) between CONVERT(VARCHAR(10),#{startDate,jdbcType=NCHAR},120) and #{endDate,jdbcType=NCHAR}
      </when>
      <when test="startDate != null and startDate != ''">
        and CONVERT(VARCHAR(10),discharge_date_time,120) >= CONVERT(VARCHAR(10),#{startDate,jdbcType=NCHAR},120)
      </when>
      <when test="endDate != null and endDate != ''">
        and CONVERT(VARCHAR(10),discharge_date_time,120) &lt;= #{endDate,jdbcType=NCHAR}
      </when>
    </choose>
        AND NOT ( datepart( weekday, discharge_date_time) = 1
        OR datepart( weekday, discharge_date_time) = 7 ) THEN 1 ELSE 0 END) as inNum,
        SUM(CASE WHEN ArchiveState NOT IN ('已封存','在院','作废','') AND Is_Valid != 1 and ArchiveState is not null
    <choose>
      <when test="startDate != null and startDate != '' and endDate != null and endDate != ''">
        and CONVERT(VARCHAR(10),discharge_date_time,120) between CONVERT(VARCHAR(10),#{startDate,jdbcType=NCHAR},120) and #{endDate,jdbcType=NCHAR}
      </when>
      <when test="startDate != null and startDate != ''">
        and CONVERT(VARCHAR(10),discharge_date_time,120) >= CONVERT(VARCHAR(10),#{startDate,jdbcType=NCHAR},120)
      </when>
      <when test="endDate != null and endDate != ''">
        and CONVERT(VARCHAR(10),discharge_date_time,120) &lt;= #{endDate,jdbcType=NCHAR}
      </when>
    </choose>
        AND NOT (datepart( weekday, discharge_date_time  ) = 1  OR datepart( weekday, discharge_date_time) = 7) THEN 1 ELSE 0 END) as isOutNum,
        SUM(CASE WHEN ArchiveState IN ('已归档', '已认证') AND Is_Valid != 1
    <choose>
      <when test="startDate != null and startDate != '' and endDate != null and endDate != ''">
        and CONVERT(VARCHAR(10),discharge_date_time,120) between CONVERT(VARCHAR(10),#{startDate,jdbcType=NCHAR},120) and #{endDate,jdbcType=NCHAR}
      </when>
      <when test="startDate != null and startDate != ''">
        and CONVERT(VARCHAR(10),discharge_date_time,120) >= CONVERT(VARCHAR(10),#{startDate,jdbcType=NCHAR},120)
      </when>
      <when test="endDate != null and endDate != ''">
        and CONVERT(VARCHAR(10),discharge_date_time,120) &lt;= #{endDate,jdbcType=NCHAR}
      </when>
    </choose>
        AND NOT (datepart( weekday, discharge_date_time) = 1 OR datepart( weekday, discharge_date_time) = 7) THEN 1 ELSE 0 END) as fileNum,
        SUM(CASE WHEN ArchiveState NOT IN ('已封存','已归档', '已认证','作废','') AND Is_Valid != 1 and ArchiveState is not null
    <choose>
      <when test="startDate != null and startDate != '' and endDate != null and endDate != ''">
        and CONVERT(VARCHAR(10),discharge_date_time,120) between CONVERT(VARCHAR(10),#{startDate,jdbcType=NCHAR},120) and #{endDate,jdbcType=NCHAR}
      </when>
      <when test="startDate != null and startDate != ''">
        and CONVERT(VARCHAR(10),discharge_date_time,120) >= CONVERT(VARCHAR(10),#{startDate,jdbcType=NCHAR},120)
      </when>
      <when test="endDate != null and endDate != ''">
        and CONVERT(VARCHAR(10),discharge_date_time,120) &lt;= #{endDate,jdbcType=NCHAR}
      </when>
    </choose>
        AND NOT ( datepart( weekday, discharge_date_time ) = 1 OR datepart( weekday, discharge_date_time ) = 7 ) THEN 1 ELSE 0 END) as unfileNum,
        SUM(CASE WHEN DISCHARGE_DISPOSITION = 5  AND NOT ( datepart( weekday, discharge_date_time ) = 1 OR datepart( weekday, discharge_date_time ) = 7 ) THEN 1 ELSE 0 END) as deathNum,
        SUM(CASE WHEN DISCHARGE_DISPOSITION = 5 AND datediff(	d,discharge_date_time,getdate()) &lt;= 2  AND NOT ( datepart( weekday, discharge_date_time ) = 1 OR datepart( weekday, discharge_date_time ) = 7 ) THEN 1 ELSE 0 END) as death2Num,
        SUM(CASE WHEN DISCHARGE_DISPOSITION = 5 AND datediff(	d,discharge_date_time,getdate()) &lt;= 3  AND NOT ( datepart( weekday, discharge_date_time ) = 1 OR datepart( weekday, discharge_date_time ) = 7 ) THEN 1 ELSE 0 END) as death3Num,
        SUM(CASE WHEN ArchiveState IN ('已归档', '已认证') AND Is_Valid != 1
    <choose>
      <when test="startDate != null and startDate != '' and endDate != null and endDate != ''">
        and CONVERT(VARCHAR(10),discharge_date_time,120) between CONVERT(VARCHAR(10),#{startDate,jdbcType=NCHAR},120) and #{endDate,jdbcType=NCHAR}
      </when>
      <when test="startDate != null and startDate != ''">
        and CONVERT(VARCHAR(10),discharge_date_time,120) >= CONVERT(VARCHAR(10),#{startDate,jdbcType=NCHAR},120)
      </when>
      <when test="endDate != null and endDate != ''">
        and CONVERT(VARCHAR(10),discharge_date_time,120) &lt;= #{endDate,jdbcType=NCHAR}
      </when>
    </choose>
        AND datediff( d, discharge_date_time,getdate() ) &lt;= 2
        AND NOT ( datepart( weekday, discharge_date_time ) = 1 OR datepart( weekday, discharge_date_time ) = 7 ) THEN 1 ELSE 0 END) as day2,
        SUM(CASE WHEN ArchiveState IN ('已归档', '已认证') AND Is_Valid != 1
    <choose>
      <when test="startDate != null and startDate != '' and endDate != null and endDate != ''">
        and CONVERT(VARCHAR(10),discharge_date_time,120) between CONVERT(VARCHAR(10),#{startDate,jdbcType=NCHAR},120) and #{endDate,jdbcType=NCHAR}
      </when>
      <when test="startDate != null and startDate != ''">
        and CONVERT(VARCHAR(10),discharge_date_time,120) >= CONVERT(VARCHAR(10),#{startDate,jdbcType=NCHAR},120)
      </when>
      <when test="endDate != null and endDate != ''">
        and CONVERT(VARCHAR(10),discharge_date_time,120) &lt;= #{endDate,jdbcType=NCHAR}
      </when>
    </choose>
        AND datediff(d,discharge_date_time, getdate()) &lt;= 3
        AND NOT ( datepart( weekday, discharge_date_time ) = 1 OR datepart( weekday, discharge_date_time) = 7 ) THEN 1 ELSE 0 END) as day3,
        SUM(CASE WHEN ArchiveState IN ('已归档', '已认证') AND Is_Valid != 1
    <choose>
      <when test="startDate != null and startDate != '' and endDate != null and endDate != ''">
        and CONVERT(VARCHAR(10),discharge_date_time,120) between CONVERT(VARCHAR(10),#{startDate,jdbcType=NCHAR},120) and #{endDate,jdbcType=NCHAR}
      </when>
      <when test="startDate != null and startDate != ''">
        and CONVERT(VARCHAR(10),discharge_date_time,120) >= CONVERT(VARCHAR(10),#{startDate,jdbcType=NCHAR},120)
      </when>
      <when test="endDate != null and endDate != ''">
        and CONVERT(VARCHAR(10),discharge_date_time,120) &lt;= #{endDate,jdbcType=NCHAR}
      </when>
    </choose>
        AND datediff( d,discharge_date_time, getdate() ) &lt;= 7
        AND NOT ( datepart( weekday, discharge_date_time ) = 1  OR datepart(  weekday, discharge_date_time ) = 7 ) THEN 1 ELSE 0 END) as day7
      from Archive_Master
      <where>
        1=1
         <choose>
          <when test="startDate != null and startDate != '' and endDate != null and endDate != ''">
            and CONVERT(VARCHAR(10),discharge_date_time,120) between CONVERT(VARCHAR(10),#{startDate,jdbcType=NCHAR},120) and #{endDate,jdbcType=NCHAR}
          </when>
          <when test="startDate != null and startDate != ''">
            and CONVERT(VARCHAR(10),discharge_date_time,120) >= CONVERT(VARCHAR(10),#{startDate,jdbcType=NCHAR},120)
          </when>
          <when test="endDate != null and endDate != ''">
            and CONVERT(VARCHAR(10),discharge_date_time,120) &lt;= #{endDate,jdbcType=NCHAR}
          </when>
        </choose>
        <choose>
            <when test="isLg == 1">
                and inp_no like 'LG%'
            </when>
            <otherwise>
                and inp_no not like 'LG%'
            </otherwise>
        </choose>
      </where>
      GROUP BY dept_name
    ) as a
    LEFT JOIN(
      select m.dept_name,
    <if test="overdueDays != null and overdueDays != ''">
      SUM(CASE WHEN ISNULL(dbo.f_WorkDay(DATEADD(DAY,1,substring(m.discharge_date_time,1,10)),f.handle_time) , 0)-#{overdueDays,jdbcType=NCHAR}>0 THEN 1 ELSE 0 END) timeOutNum
    </if>
    <if test="overdueDays == null or overdueDays == ''">
      SUM(CASE WHEN ISNULL(dbo.f_WorkDay(DATEADD(DAY,1,substring(m.discharge_date_time,1,10)),f.handle_time) , 0)-3>0 THEN 1 ELSE 0 END) timeOutNum
    </if>
       from (
        select id,master_id ,convert(varchar,handle_time,120) handle_time ,handle_id,handle_name,following_type  from (
          select ROW_NUMBER() over(partition by master_id order by handle_time asc) RowNum
          ,Archive_Master_Following.*
          from Archive_Master_Following where 1=1 and following_type='5'
        ) as t1  where 1=1 and RowNum = 1
      ) f
      right JOIN (
        select id master_id,patient_id,inp_no,visit_id,name,dept_name,convert(varchar,discharge_date_time,120) discharge_date_time,check_doctor,first_instance,DOCTOR_IN_CHARGE,
        death_flag,dept_admission_to,convert(varchar,admission_date_time,120) admission_date_time
        from archive_master
      <where>
          1=1 and ArchiveState NOT IN ('已封存', '作废','在院','') and Is_Valid!=1 and ArchiveState is not null
          <choose>
              <when test="isLg == 1">
                  and inp_no like 'LG%'
              </when>
              <otherwise>
                  and inp_no not like 'LG%'
              </otherwise>
          </choose>
          <choose>
              <when test="startDate != null and startDate != '' and endDate != null and endDate != ''">
                  and CONVERT(VARCHAR(10),discharge_date_time,120) between CONVERT(VARCHAR(10),#{startDate,jdbcType=NCHAR},120) and #{endDate,jdbcType=NCHAR}
              </when>
              <when test="startDate != null and startDate != ''">
                  and CONVERT(VARCHAR(10),discharge_date_time,120) >= CONVERT(VARCHAR(10),#{startDate,jdbcType=NCHAR},120)
              </when>
              <when test="endDate != null and endDate != ''">
                  and CONVERT(VARCHAR(10),discharge_date_time,120) &lt;= #{endDate,jdbcType=NCHAR}
              </when>
          </choose>
      </where>
      ) m
      on f.master_id=m.master_id
      GROUP BY m.dept_name
    ) b
    on b.dept_name=a.dept_name
    LEFT JOIN (select code,name from emr_dictionary where parent_id=(select id from emr_dictionary where typeCode='dept_code' )) e
    on a.dept_name=e.code
    <where>
      1=1
      <if test="deptCode != null and deptCode!=''">
        and a.dept_name in
        <foreach item="item" collection="deptCode.split(',')" open="(" separator="," close=")">
          #{item}
        </foreach>
      </if>
      <if test="timeOutNum != null and timeOutNum != '' and timeOutNum!='0'">
        and timeOutNum>= #{timeOutNum,jdbcType=NCHAR}
      </if>
    </where>
</select>
  <!--<select id="selectByCol" parameterType="com.emr.entity.V_Count" resultMap="BaseResultMap">
    <![CDATA[
    SELECT deptCode, deptName, ISNULL(inNum, 0) inNum, ISNULL(outNum, 0) outNum, ISNULL(fileNum, 0) fileNum, ISNULL(unfileNum, 0) unfileNum, ISNULL(deathNum, 0) deathNum, ISNULL(fileRate, '0') fileRate, ISNULL(day2Rate, '0') day2Rate, ISNULL(day3Rate, '0') day3Rate, ISNULL(day7Rate, '0') day7Rate FROM (select DISTINCT c.dept_name as deptCode,t8.Name as deptName, t.inNum ,t1.outNum ,t2.fileNum , t3.unfileNum ,t4.deathNum,
    (t2.fileNum/t1.outNum) as fileRate,(t5.Num-t4.deathNum)/(t2.fileNum-t4.deathNum) as day2Rate, (t6.Num-t4.deathNum)/(t2.fileNum-t4.deathNum) as day3Rate, (t7.Num)/(t2.fileNum) as day7Rate
    from archive_master as c
    ]]>
    left join (select dept_name,COUNT(*) AS inNum from archive_master as s where s.ArchiveState in('在院')
    and not (datepart(weekday,discharge_date_time)=1 or datepart(weekday,discharge_date_time)=7)
    <choose>
      <when test="startDate != null and startDate != '' and endDate != null and endDate != ''">
        and discharge_date_time between CONVERT(VARCHAR(10),#{startDate,jdbcType=NCHAR},120) and
        #{endDate,jdbcType=NCHAR}+ ' 23:59:59'
      </when>
      <when test="startDate != null and startDate != ''">
        and discharge_date_time >= CONVERT(VARCHAR(10),#{startDate,jdbcType=NCHAR},120)
      </when>
      <when test="endDate != null and endDate != ''">
        and discharge_date_time &lt;= #{endDate,jdbcType=NCHAR}+ ' 23:59:59'
      </when>
    </choose>
    group by dept_name) as t on c.dept_name= t.dept_name
    left join (select dept_name,COUNT(*) AS outNum from archive_master as s where s.ArchiveState!='在院'
    and not (datepart(weekday,discharge_date_time)=1 or datepart(weekday,discharge_date_time)=7)
    <choose>
      <when test="startDate != null and startDate != '' and endDate != null and endDate != ''">
        and discharge_date_time between CONVERT(VARCHAR(10),#{startDate,jdbcType=NCHAR},120) and
        #{endDate,jdbcType=NCHAR}+ ' 23:59:59'
      </when>
      <when test="startDate != null and startDate != ''">
        and discharge_date_time >= CONVERT(VARCHAR(10),#{startDate,jdbcType=NCHAR},120)
      </when>
      <when test="endDate != null and endDate != ''">
        and discharge_date_time &lt;= #{endDate,jdbcType=NCHAR}+ ' 23:59:59'
      </when>
    </choose>
    group by dept_name) as t1 on c.dept_name= t1.dept_name
    left join (select dept_name,COUNT(*) AS fileNum from archive_master as s where s.ArchiveState in('已归档','已认证')
    and not (datepart(weekday,discharge_date_time)=1 or datepart(weekday,discharge_date_time)=7)
    <choose>
      <when test="startDate != null and startDate != '' and endDate != null and endDate != ''">
        and discharge_date_time between CONVERT(VARCHAR(10),#{startDate,jdbcType=NCHAR},120) and
        #{endDate,jdbcType=NCHAR}+ ' 23:59:59'
      </when>
      <when test="startDate != null and startDate != ''">
        and discharge_date_time >= CONVERT(VARCHAR(10),#{startDate,jdbcType=NCHAR},120)
      </when>
      <when test="endDate != null and endDate != ''">
        and discharge_date_time &lt;= #{endDate,jdbcType=NCHAR}+ ' 23:59:59'
      </when>
    </choose>
    group by dept_name) as t2 on c.dept_name= t2.dept_name
    left join (select dept_name,COUNT(*) AS unfileNum from archive_master as s where s.ArchiveState not in('已归档','已认证')
    and not (datepart(weekday,discharge_date_time)=1 or datepart(weekday,discharge_date_time)=7)
    <choose>
      <when test="startDate != null and startDate != '' and endDate != null and endDate != ''">
        and discharge_date_time between CONVERT(VARCHAR(10),#{startDate,jdbcType=NCHAR},120) and
        #{endDate,jdbcType=NCHAR}+ ' 23:59:59'
      </when>
      <when test="startDate != null and startDate != ''">
        and discharge_date_time >= CONVERT(VARCHAR(10),#{startDate,jdbcType=NCHAR},120)
      </when>
      <when test="endDate != null and endDate != ''">
        and discharge_date_time &lt;= #{endDate,jdbcType=NCHAR}+ ' 23:59:59'
      </when>
    </choose>
    group by dept_name) as t3 on c.dept_name= t3.dept_name
    left join (select dept_name,COUNT(*) AS deathNum from archive_master as s where s.DISCHARGE_DISPOSITION=5
    and not (datepart(weekday,discharge_date_time)=1 or datepart(weekday,discharge_date_time)=7)
    <choose>
      <when test="startDate != null and startDate != '' and endDate != null and endDate != ''">
        and discharge_date_time between CONVERT(VARCHAR(10),#{startDate,jdbcType=NCHAR},120) and
        #{endDate,jdbcType=NCHAR}+ ' 23:59:59'
      </when>
      <when test="startDate != null and startDate != ''">
        and discharge_date_time >= CONVERT(VARCHAR(10),#{startDate,jdbcType=NCHAR},120)
      </when>
      <when test="endDate != null and endDate != ''">
        and discharge_date_time &lt;= #{endDate,jdbcType=NCHAR}+ ' 23:59:59'
      </when>
    </choose>
    group by dept_name) as t4 on c.dept_name= t4.dept_name
    left join (select dept_name,COUNT(*) AS Num from archive_master as s where   s.ArchiveState in('已归档','已认证')  and datediff(d,discharge_date_time,getdate())&lt;=2
    and not (datepart(weekday,discharge_date_time)=1 or datepart(weekday,discharge_date_time)=7)
    <choose>
      <when test="startDate != null and startDate != '' and endDate != null and endDate != ''">
        and discharge_date_time between CONVERT(VARCHAR(10),#{startDate,jdbcType=NCHAR},120) and
        #{endDate,jdbcType=NCHAR}+ ' 23:59:59'
      </when>
      <when test="startDate != null and startDate != ''">
        and discharge_date_time >= CONVERT(VARCHAR(10),#{startDate,jdbcType=NCHAR},120)
      </when>
      <when test="endDate != null and endDate != ''">
        and discharge_date_time &lt;= #{endDate,jdbcType=NCHAR}+ ' 23:59:59'
      </when>
    </choose>
    group by dept_name) as t5 on c.dept_name= t5.dept_name
    left join (select dept_name,COUNT(*) AS Num from archive_master as s where   s.ArchiveState in('已归档','已认证')  and datediff(d,discharge_date_time,getdate())&lt;=3
    and not (datepart(weekday,discharge_date_time)=1 or datepart(weekday,discharge_date_time)=7)
    <choose>
      <when test="startDate != null and startDate != '' and endDate != null and endDate != ''">
        and discharge_date_time between CONVERT(VARCHAR(10),#{startDate,jdbcType=NCHAR},120) and
        #{endDate,jdbcType=NCHAR}+ ' 23:59:59'
      </when>
      <when test="startDate != null and startDate != ''">
        and discharge_date_time >= CONVERT(VARCHAR(10),#{startDate,jdbcType=NCHAR},120)
      </when>
      <when test="endDate != null and endDate != ''">
        and discharge_date_time &lt;= #{endDate,jdbcType=NCHAR}+ ' 23:59:59'
      </when>
    </choose>
    group by dept_name) as t6 on c.dept_name= t6.dept_name
    left join (select dept_name,COUNT(*) AS Num from archive_master as s where   s.ArchiveState in('已归档','已认证')  and datediff(d,discharge_date_time,getdate())&lt;=7
    and not (datepart(weekday,discharge_date_time)=1 or datepart(weekday,discharge_date_time)=7)
    <choose>
      <when test="startDate != null and startDate != '' and endDate != null and endDate != ''">
        and discharge_date_time between CONVERT(VARCHAR(10),#{startDate,jdbcType=NCHAR},120) and
        #{endDate,jdbcType=NCHAR}+ ' 23:59:59'
      </when>
      <when test="startDate != null and startDate != ''">
        and discharge_date_time >= CONVERT(VARCHAR(10),#{startDate,jdbcType=NCHAR},120)
      </when>
      <when test="endDate != null and endDate != ''">
        and discharge_date_time &lt;= #{endDate,jdbcType=NCHAR}+ ' 23:59:59'
      </when>
    </choose>
    group by dept_name) as t7 on c.dept_name= t7.dept_name
    left join (SELECT code,Name FROM emr_dictionary ) as t8 on c.dept_name=t8.code
    ) v_count where 1=1
    &lt;!&ndash;<if test="deptCode != null and deptCode!=''">&ndash;&gt;
      &lt;!&ndash;and deptCode = #{deptCode,jdbcType=NVARCHAR}&ndash;&gt;
    &lt;!&ndash;</if>&ndash;&gt;
    <if test="deptCode != null and deptCode!=''">
      and deptCode in
      <foreach item="item" collection="deptCode.split(',')" open="(" separator="," close=")">
        #{item}
      </foreach>
    </if>

    <if test="deptName != null and deptName!=''">
      and deptName in
      <foreach item="item" collection="deptName.split(',')" open="(" separator="," close=")">
        #{item}
      </foreach>
      and deptName is not null
    </if>
    &lt;!&ndash;<if test="deptName != null and deptName!=''">&ndash;&gt;
      &lt;!&ndash;and deptName = #{deptName,jdbcType=NVARCHAR}&ndash;&gt;
    &lt;!&ndash;</if>&ndash;&gt;
  </select>-->
  <select id="selByDept" parameterType="com.emr.entity.V_Count2" resultMap="BaseResultMap2">
   select c.deptName,c.Name,count(*) total,sum(c.dayNum) dayNum,sum(c.Saleable)expired,sum(c.expiredDay) expiredDay
     from view_count c
     where 1=1 and c.deptName is not null
      <if test="deptName != null and deptName!=''">
        and c.deptName in
        <foreach item="item" collection="deptName.split(',')" open="(" separator="," close=")">
          #{item}
        </foreach>
      </if>
    <choose>
      <when test="startDateTo != null and startDateTo != '' and endDateTo != null and endDateTo != ''">
        and CONVERT(VARCHAR(10),c.discharge_date_time,120) between CONVERT(VARCHAR(10),#{startDateTo,jdbcType=NCHAR},120) and
        CONVERT(VARCHAR(10),#{endDateTo,jdbcType=NCHAR},120)
      </when>
      <when test="startDateTo != null and startDateTo != ''">
        and CONVERT(VARCHAR(10),c.discharge_date_time,120) >= CONVERT(VARCHAR(10),#{startDateTo,jdbcType=NCHAR},120)
      </when>
      <when test="endDateTo != null and endDateTo != ''">
        and CONVERT(VARCHAR(10),c.discharge_date_time,120) &lt;= CONVERT(VARCHAR(10),#{endDateTo,jdbcType=NCHAR},120)
      </when>
    </choose>
     group by c.deptName,c.Name
  </select>
  <select id="selByCharge" parameterType="com.emr.entity.V_Count2" resultMap="BaseResultMap2">
   select c2.deptName,c2.doctorInCharge,count(*) total,sum(c2.dayNum) dayNum,sum(c2.Saleable)expired,sum(c2.expiredDay) expiredDay
   from view_count c2
   where 1=1  and c2.deptName is not null and c2.doctorInCharge is not null
    <if test="deptName != null and deptName!=''">
      and c2.deptName in
      <foreach item="item" collection="deptName.split(',')" open="(" separator="," close=")">
        #{item}
      </foreach>
    </if>
    <if test="doctorInCharge != null and doctorInCharge!=''">
      and c2.doctorInCharge in
      <foreach item="item" collection="doctorInCharge.split(',')" open="(" separator="," close=")">
        #{item}
      </foreach>
    </if>
   group by c2.deptName,c2.doctorInCharge
  </select>
</mapper>