<?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.Archive_MasterMapper">
    <resultMap id="BaseResultMap" type="com.emr.entity.Archive_Master">
        <id column="id" jdbcType="NVARCHAR" property="id" />
        <result column="patient_id" jdbcType="NVARCHAR" property="patientId" />
        <result column="inp_no" jdbcType="NVARCHAR" property="inpNo" />
        <result column="visit_id" jdbcType="NVARCHAR" property="visitId" />
        <result column="name" jdbcType="NVARCHAR" property="name" />
        <result column="sex" jdbcType="NVARCHAR" property="sex" />
        <result column="dept_name" jdbcType="NVARCHAR" property="deptName" />
        <result column="discharge_date_time" jdbcType="NVARCHAR" property="dischargeDateTime" />
        <result column="ArchiveState" jdbcType="NVARCHAR" property="archivestate" />
        <result column="admission_date_time" jdbcType="NVARCHAR" property="admissionDateTime" />
        <result column="dept_admission_to" jdbcType="NVARCHAR" property="deptAdmissionTo" />
        <result column="check_doctor" jdbcType="NVARCHAR" property="checkDoctor" />
        <result column="check_datetime" jdbcType="NVARCHAR" property="checkDatetime" />
        <result column="checked_doctor" jdbcType="NVARCHAR" property="checkedDoctor" />
        <result column="checked_datetime" jdbcType="NVARCHAR" property="checkedDatetime" />
        <result column="LockInfo" jdbcType="NVARCHAR" property="lockinfo" />
        <result column="DOCTOR_IN_CHARGE" jdbcType="NVARCHAR" property="doctorInCharge" />
        <result column="ID_NO" jdbcType="NVARCHAR" property="idNo" />
        <result column="DISCHARGE_DISPOSITION" jdbcType="NVARCHAR" property="dischargeDisposition" />
        <result column="dept_code_lend" jdbcType="NVARCHAR" property="deptCodeLend" />
        <result column="isscaned" jdbcType="NVARCHAR" property="isscaned" />
        <result column="is_scanning" jdbcType="NVARCHAR" property="isScanning" />
        <result column="bed_number" jdbcType="NVARCHAR" property="bedNumber" />

        <result column="cmt_doctor" jdbcType="INTEGER" property="cmtDoctor"/>
        <result column="cmt_nurse" jdbcType="INTEGER" property="cmtNurse"/>
        <result column="doctor_name" jdbcType="NVARCHAR" property="doctorName"/>
        <result column="nurse_name" jdbcType="NVARCHAR" property="nurseName"/>
        <result column="cmt_doctor_date" jdbcType="NVARCHAR" property="cmtDoctorDate"/>
        <result column="cmt_nurse_date" jdbcType="NVARCHAR" property="cmtNurseDate"/>

        <result column="emr_doctor_cmt" jdbcType="NVARCHAR" property="emrDoctorCmt"/>
        <result column="emr_nure_cmt" jdbcType="NVARCHAR" property="emrNureCmt"/>
        <result column="first_instance" jdbcType="NVARCHAR" property="firstInstance"/>
        <result column="death_flag" jdbcType="NVARCHAR" property="deathFlag"/>
        <result column="transfer_dept" jdbcType="NVARCHAR" property="transferDept"/>
        <result column="score" jdbcType="NVARCHAR" property="score"/>

        <result column="is_sign" jdbcType="INTEGER" property="isSign" />
        <result column="signer" jdbcType="NVARCHAR" property="signer"/>
        <result column="sign_time" jdbcType="NVARCHAR" property="signTime"/>
        <result column="print_num" jdbcType="NVARCHAR" property="printNum"/>
    </resultMap>

    <resultMap id="BaseResultMap2" type="com.emr.entity.Archive_Master_Vo">
        <id column="id" jdbcType="NVARCHAR" property="id" />
        <result column="patient_id" jdbcType="NVARCHAR" property="patientId" />
        <result column="inp_no" jdbcType="NVARCHAR" property="inpNo" />
        <result column="visit_id" jdbcType="NVARCHAR" property="visitId" />
        <result column="name" jdbcType="NVARCHAR" property="name" />
        <result column="sex" jdbcType="NVARCHAR" property="sex" />
        <result column="dept_name" jdbcType="NVARCHAR" property="deptName" />
        <result column="discharge_date_time" jdbcType="NVARCHAR" property="dischargeDateTime" />
        <result column="ArchiveState" jdbcType="NVARCHAR" property="archivestate" />
        <result column="admission_date_time" jdbcType="NVARCHAR" property="admissionDateTime" />
        <result column="dept_admission_to" jdbcType="NVARCHAR" property="deptAdmissionTo" />
        <result column="check_doctor" jdbcType="NVARCHAR" property="checkDoctor" />
        <result column="check_datetime" jdbcType="NVARCHAR" property="checkDatetime" />
        <result column="checked_doctor" jdbcType="NVARCHAR" property="checkedDoctor" />
        <result column="checked_datetime" jdbcType="NVARCHAR" property="checkedDatetime" />
        <result column="LockInfo" jdbcType="NVARCHAR" property="lockinfo" />
        <result column="DOCTOR_IN_CHARGE" jdbcType="NVARCHAR" property="doctorInCharge" />
        <result column="ID_NO" jdbcType="NVARCHAR" property="idNo" />
        <result column="DISCHARGE_DISPOSITION" jdbcType="NVARCHAR" property="dischargeDisposition" />
        <result column="dept_code_lend" jdbcType="NVARCHAR" property="deptCodeLend" />
        <result column="isscaned" jdbcType="NVARCHAR" property="isscaned" />
        <result column="is_scanning" jdbcType="NVARCHAR" property="isScanning" />
        <result column="bed_number" jdbcType="NVARCHAR" property="bedNumber" />
        <result column="cmt_doctor" jdbcType="INTEGER" property="cmtDoctor"/>
        <result column="cmt_nurse" jdbcType="INTEGER" property="cmtNurse"/>
        <result column="doctor_name" jdbcType="NVARCHAR" property="doctorName"/>
        <result column="nurse_name" jdbcType="NVARCHAR" property="nurseName"/>
        <result column="cmt_doctor_date" jdbcType="NVARCHAR" property="cmtDoctorDate"/>
        <result column="cmt_nurse_date" jdbcType="NVARCHAR" property="cmtNurseDate"/>
        <result column="emr_doctor_cmt" jdbcType="NVARCHAR" property="emrDoctorCmt"/>
        <result column="emr_nure_cmt" jdbcType="NVARCHAR" property="emrNureCmt"/>
        <result column="first_instance" jdbcType="NVARCHAR" property="firstInstance"/>
        <result column="death_flag" jdbcType="NVARCHAR" property="deathFlag"/>
        <result column="transfer_dept" jdbcType="NVARCHAR" property="transferDept"/>
        <result column="fid" jdbcType="NVARCHAR" property="fid"/>
        <result column="handle_id" jdbcType="NVARCHAR" property="handleId"/>
        <result column="handle_name" jdbcType="NVARCHAR" property="handleName"/>
        <result column="handle_time" jdbcType="NVARCHAR" property="handleTime"/>
        <result column="following_type" jdbcType="NVARCHAR" property="followingType"/>
        <result column="following_content" jdbcType="NVARCHAR" property="followingContent"/>
        <result column="overdue" jdbcType="NVARCHAR" property="overdue"/>
        <result column="create_time" jdbcType="NVARCHAR" property="createTime"/>
        <result column="score" jdbcType="NVARCHAR" property="score"/>
        <result column="numb" jdbcType="INTEGER" property="numb"/>
        <result column="is_sign" jdbcType="INTEGER" property="isSign" />
        <result column="signer" jdbcType="NVARCHAR" property="signer"/>
        <result column="sign_time" jdbcType="NVARCHAR" property="signTime"/>
        <result column="check_flag" jdbcType="NVARCHAR" property="checkFlag"/>
        <result column="print_num" jdbcType="NVARCHAR" property="printNum"/>
        <result column="doctor_num" jdbcType="NVARCHAR" property="doctorNum"/>
        <result column="nurse_num" jdbcType="NVARCHAR" property="nurseNum"/>
        <result column="fault_doctor_num" jdbcType="NVARCHAR" property="faultDoctorNum"/>
        <result column="fault_nurse_num" jdbcType="NVARCHAR" property="faultNurseNum"/>
        <result column="file_num" jdbcType="INTEGER" property="fileNum" />
        <result column="delay_num" jdbcType="INTEGER" property="delayNum" />
        <result column="quality_num" jdbcType="INTEGER" property="qualityNum" />
        <result column="back_num" jdbcType="INTEGER" property="backNum" />
    </resultMap>
    <sql id="Base_Column_List">
    id, patient_id, inp_no, visit_id, name, sex, dept_name, discharge_date_time, ArchiveState,
    admission_date_time, dept_admission_to, check_doctor, check_datetime, checked_doctor, transfer_dept,
    checked_datetime, LockInfo, DOCTOR_IN_CHARGE, ID_NO, DISCHARGE_DISPOSITION, dept_code_lend,isscaned,is_scanning,
    cmt_doctor,cmt_nurse,doctor_name,nurse_name,cmt_doctor_date,cmt_nurse_date,emr_doctor_cmt,emr_nure_cmt,death_flag,
    bed_number,is_sign,signer,sign_time
  </sql>
    <select id="selectById" parameterType="java.lang.String" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List" />
        from archive_master
        where id = #{id,jdbcType=NVARCHAR}
    </select>
    <!--查询病案是否存在-->
    <select id="selByCol" resultMap="BaseResultMap">
        select * from archive_master
        where 1=1
        <if test="id != null and id != ''">
            and id = #{id,jdbcType=NVARCHAR}
        </if>
        <if test="inpNo != null and inpNo != ''">
            and inp_no = #{inpNo,jdbcType=NVARCHAR}
        </if>
        <if test="visitId != null and visitId != ''">
            and visit_id=#{visitId,jdbcType=NCHAR}
        </if>
        <if test="checkDoctor != null">
            and check_doctor = #{checkDoctor,jdbcType=NVARCHAR}
        </if>
        <if test="checkedDoctor != null">
            and checked_doctor = #{checkedDoctor,jdbcType=NVARCHAR}
        </if>
        <if test="checkDatetime != null">
            and check_datetime = #{checkDatetime,jdbcType=NVARCHAR}
        </if>
        <if test="checkedDatetime != null">
            and checked_datetime = #{checkedDatetime,jdbcType=NVARCHAR}
        </if>
        <if test="lockinfo != null">
            and LockInfo = #{lockinfo,jdbcType=NVARCHAR}
        </if>
    </select>
    <!--查询病案是否存在-->
    <select id="selectByObject" resultMap="BaseResultMap">
        select * from archive_master
        where 1=1
        <if test="patientId != null and patientId != ''">
            and patient_id = #{patientId,jdbcType=NVARCHAR}
        </if>
        <if test="visitId != null and visitId != ''">
            and visit_id = #{visitId,jdbcType=NVARCHAR}
        </if>
    </select>
    <select id="selectByCol" parameterType="com.emr.entity.Archive_Master_Vo" resultMap="BaseResultMap">
        select distinct m.id,m.patient_id,m.inp_no,m.cmt_doctor,m.cmt_nurse,m.visit_id,m.name,m.sex,ISNULL(f.name, m.dept_name) dept_name
        ,CONVERT(varchar(10),m.discharge_date_time, 120) discharge_date_time,m.ArchiveState,CONVERT(varchar(10),m.admission_date_time, 120) admission_date_time
        ,ISNULL(d.name,m.dept_admission_to) dept_admission_to,m.check_doctor,m.doctor_name,m.nurse_name,m.checked_datetime,m.checked_doctor
        ,m.LockInfo,m.DOCTOR_IN_CHARGE,m.ID_NO,m.DISCHARGE_DISPOSITION,m.dept_code_lend,m.isscaned,m.is_scanning,bed_number
        ,ISNULL(printTable.num, 0) printNum
        from archive_master m
        LEFT JOIN (select code,name from emr_dictionary where parent_id=(select id from emr_dictionary where
        typeCode='dept_code' )) d
        on m.dept_admission_to=d.code
        LEFT JOIN (select code,name from emr_dictionary where parent_id=(select id from emr_dictionary where
        typeCode='dept_code' )) f
        on m.dept_name=f.code
        LEFT JOIN (select MasterID,COUNT(*) num from archive_printInfo group by MasterID) printTable
        on m.id=printTable.MasterID
        where 1=1
        and patindex('%B%',m.inp_no) = 0 and inp_no not like 'LG%' and  m.Is_Valid!=1
        <if test="deptAdmissionTo!= null and deptAdmissionTo!= ''">
            and (m.dept_admission_to in
            <foreach item="item" collection="deptAdmissionTo.split(',')" open="(" separator="," close=")">
                #{item}
            </foreach>
            or m.transfer_dept in
            <foreach item="item" collection="deptAdmissionTo.split(',')" open="(" separator="," close=")">
                #{item}
            </foreach>
            )
        </if>

        <if test="id != null and id != ''">
            and m.id = #{id,jdbcType=NVARCHAR}
        </if>
        <if test="inpNo != null and inpNo != ''">
            and m.inp_no like '%'+#{inpNo,jdbcType=NCHAR}+'%'
        </if>
        <if test="patientId != null and patientId != ''">
            and m.patient_id like '%'+#{patientId,jdbcType=NCHAR}+'%'
        </if>

        <if test="bedNumber != null and bedNumber != ''">
            and m.bed_number like '%'+#{bedNumber,jdbcType=NVARCHAR}+'%'
        </if>

        <if test="visitId != null and visitId != ''">
            and m.visit_id=#{visitId,jdbcType=NCHAR}
        </if>
        <if test="name != null and name != ''">
            and m.name like '%'+#{name,jdbcType=NCHAR}+'%'
        </if>
        <if test="archivestate != null and archivestate != ''">
            and m.ArchiveState=#{archivestate,jdbcType=NVARCHAR}
        </if>
        <if test="idNo != null and idNo != ''">
            and m.ID_NO like '%'+#{idNo,jdbcType=NCHAR}+'%'
        </if>
        <if test="sex != null">
            and m.sex = #{sex,jdbcType=NVARCHAR}
        </if>
        <if test="deptName!= null and deptName!= ''">
            and m.dept_name in
            <foreach item="item" collection="deptName.split(',')" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="dischargeDateTime != null">
            and m.discharge_date_time = #{dischargeDateTime,jdbcType=NVARCHAR}
        </if>

        <choose>
            <when test="startDateTo != null and startDateTo != '' and endDateTo != null and endDateTo != ''">
                and m.admission_date_time between CONVERT(VARCHAR(10),#{startDateTo,jdbcType=NCHAR},120) and
                #{endDateTo,jdbcType=NCHAR}+ ' 23:59:59'
            </when>
            <when test="startDateTo != null and startDateTo != ''">
                and m.admission_date_time >= CONVERT(VARCHAR(10),#{startDateTo,jdbcType=NCHAR},120)
            </when>
            <when test="endDateTo != null and endDateTo != ''">
                and m.admission_date_time &lt;= #{endDateTo,jdbcType=NCHAR}+ ' 23:59:59'
            </when>
        </choose>


        <if test="checkDoctor != null">
            and m.check_doctor = #{checkDoctor,jdbcType=NVARCHAR}
        </if>
        <if test="checkedDoctor != null">
            and checked_doctor = #{checkedDoctor,jdbcType=NVARCHAR}
        </if>
        <if test="checkDatetime != null">
            and m.check_datetime = #{checkDatetime,jdbcType=NVARCHAR}
        </if>
        <if test="checkedDatetime != null">
            and m.checked_datetime = #{checkedDatetime,jdbcType=NVARCHAR}
        </if>
        <if test="lockinfo != null">
            and m.LockInfo = #{lockinfo,jdbcType=NVARCHAR}
        </if>
        <if test="doctorInCharge != null">
            and m.DOCTOR_IN_CHARGE = #{doctorInCharge,jdbcType=NVARCHAR}
        </if>
        <if test="dischargeDisposition != null">
            and m.DISCHARGE_DISPOSITION = #{dischargeDisposition,jdbcType=NVARCHAR}
        </if>
        <if test="deptCodeLend != null">
            and m.dept_code_lend = #{deptCodeLend,jdbcType=NVARCHAR}
        </if>
        <if test="isscaned != null and isscaned != ''">
            and m.isscaned = #{isscaned,jdbcType=NVARCHAR}
        </if>
        <if test="isScanning != null and isScanning != ''">
            and m.is_scanning = #{isScanning,jdbcType=NVARCHAR}
        </if>
        ORDER BY admission_date_time desc
    </select>
    <select id="selectByColumn" parameterType="com.emr.entity.Archive_Master_Vo" resultMap="BaseResultMap2">
        select m.id,m.patient_id,m.inp_no,m.visit_id,m.name,m.sex,ISNULL(f.name, m.dept_name) dept_name
        ,CONVERT(varchar(10),m.discharge_date_time, 120) discharge_date_time,m.ArchiveState,CONVERT(varchar(10),m.admission_date_time, 120) admission_date_time,ISNULL(d.name,m.dept_admission_to) dept_admission_to
        ,m.check_doctor,m.checked_datetime,m.checked_doctor,m.LockInfo,m.DOCTOR_IN_CHARGE,m.ID_NO,m.DISCHARGE_DISPOSITION,m.dept_code_lend,m.isscaned,m.is_scanning,
        m.cmt_doctor,m.cmt_nurse,m.doctor_name,m.nurse_name,m.cmt_doctor_date,m.cmt_nurse_date,m.emr_doctor_cmt,m.emr_nure_cmt,m.death_flag,m.bed_number,ISNULL(s.numb, 0) numb
        ,ISNULL(sDoctor.numb, 0) faultDoctorNum,ISNULL(sNurse.numb, 0) faultNurseNum,ISNULL(extDoctor.num, 0) doctorNum,ISNULL(extNurse.num, 0) nurseNum
        from archive_master m
        LEFT JOIN (select code,name from emr_dictionary where parent_id=(select id from emr_dictionary where
        typeCode='dept_code' )) d
        on m.dept_admission_to=d.code
        LEFT JOIN (select code,name from emr_dictionary where parent_id=(select id from emr_dictionary where
        typeCode='dept_code' )) f
        on m.dept_name=f.code
        LEFT JOIN(select archive_detail_id,count(*) numb from emr_fault_detail where 1=1 and back_flag is not null GROUP BY archive_detail_id) s
        on m.id=s.archive_detail_id
        LEFT JOIN(select archive_detail_id,count(*) numb from emr_fault_detail where 1=1 and back_flag in ('1','5') GROUP BY archive_detail_id) sDoctor
        on m.id=sDoctor.archive_detail_id
        LEFT JOIN(select archive_detail_id,count(*) numb from emr_fault_detail where 1=1 and back_flag in ('2','6') GROUP BY archive_detail_id) sNurse
        on m.id=sNurse.archive_detail_id
        LEFT JOIN (select zyh,jzh,COUNT(*) num from archive_other_ext where T1!='1801-02-03 00:00:00.000' group by zyh,jzh) extDoctor
        on m.inp_no=extDoctor.zyh
        and m.visit_id=extDoctor.jzh
        LEFT JOIN (select zyh,jzh,COUNT(*) num from archive_other_ext where T2!='1801-02-03 00:00:00.000' group by zyh,jzh) extNurse
        on m.inp_no=extNurse.zyh
        and m.visit_id=extNurse.jzh
        LEFT JOIN (select MasterID,COUNT(*) num from archive_printInfo group by MasterID) printTable
        on m.id=printTable.MasterID
        where 1=1 and ArchiveState in('归档中','提交','主任退回') and  m.Is_Valid!=1
        and patindex('%B%',m.inp_no) = 0 and inp_no not like 'LG%'
        <if test="emrCmit!=null and emrCmit!=''">
            <if test="emrCmit==1">
                and m.emr_doctor_cmt='提交'
            </if>
            <if test="emrCmit==2">
                and m.emr_nure_cmt='提交'
            </if>
        </if>
        <if test="printNum != null and printNum != ''">
            <choose>
                <when test="printNum == 1">
                    and printTable.MasterID is not null
                </when>
                <otherwise>and printTable.MasterID is  null</otherwise>
            </choose>
        </if>
        <if test="inpNo != null and inpNo != ''">
            and m.inp_no like '%'+#{inpNo,jdbcType=NCHAR}+'%'
        </if>
        <if test="bedNumber != null and bedNumber != ''">
            and m.bed_number like '%'+#{bedNumber,jdbcType=NVARCHAR}+'%'
        </if>
        <if test="visitId != null and visitId != ''">
            and m.visit_id=#{visitId,jdbcType=NCHAR}
        </if>
        <if test="name != null and name != ''">
            and m.name like '%'+#{name,jdbcType=NCHAR}+'%'
        </if>
        <if test="archivestate != null and archivestate != ''">
            and m.ArchiveState=#{archivestate,jdbcType=NVARCHAR}
        </if>
        <if test="idNo != null and idNo != ''">
            and m.ID_NO like '%'+#{idNo,jdbcType=NCHAR}+'%'
        </if>
        <if test="sex != null">
            and m.sex = #{sex,jdbcType=NVARCHAR}
        </if>
        <if test="deptName!= null and deptName!= ''">
            and m.dept_name in
            <foreach item="item" collection="deptName.split(',')" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="dischargeDateTime != null">
            and m.discharge_date_time = #{dischargeDateTime,jdbcType=NVARCHAR}
        </if>
        <choose>
            <when test="startDateTo != null and startDateTo != '' and endDateTo != null and endDateTo != ''">
                and m.discharge_date_time between CONVERT(VARCHAR(10),#{startDateTo,jdbcType=NCHAR},120) and
                #{endDateTo,jdbcType=NCHAR}+ ' 23:59:59'
            </when>
            <when test="startDateTo != null and startDateTo != ''">
                and m.discharge_date_time >= CONVERT(VARCHAR(10),#{startDateTo,jdbcType=NCHAR},120)
            </when>
            <when test="endDateTo != null and endDateTo != ''">
                and m.discharge_date_time &lt;= #{endDateTo,jdbcType=NCHAR}+ ' 23:59:59'
            </when>
        </choose>
        <if test="deptAdmissionTo != null and deptAdmissionTo!=''">
            and m.dept_admission_to = #{deptAdmissionTo,jdbcType=NVARCHAR}
        </if>
        <if test="checkDoctor != null">
            and m.check_doctor = #{checkDoctor,jdbcType=NVARCHAR}
        </if>
        <if test="checkedDoctor != null">
            and checked_doctor = #{checkedDoctor,jdbcType=NVARCHAR}
        </if>
        <if test="checkDatetime != null">
            and m.check_datetime = #{checkDatetime,jdbcType=NVARCHAR}
        </if>
        <if test="checkedDatetime != null">
            and m.checked_datetime = #{checkedDatetime,jdbcType=NVARCHAR}
        </if>
        <if test="lockinfo != null">
            and m.LockInfo = #{lockinfo,jdbcType=NVARCHAR}
        </if>
        <!--<if test="doctorInCharge != null">-->
        <!--and m.DOCTOR_IN_CHARGE = #{doctorInCharge,jdbcType=NVARCHAR}-->
        <!--</if>-->
        <if test="doctorInCharge!= null and doctorInCharge!= ''">
            and m.DOCTOR_IN_CHARGE in
            <foreach item="item" collection="doctorInCharge.split(',')" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="dischargeDisposition != null">
            and m.DISCHARGE_DISPOSITION = #{dischargeDisposition,jdbcType=NVARCHAR}
        </if>
        <if test="deptCodeLend != null">
            and m.dept_code_lend = #{deptCodeLend,jdbcType=NVARCHAR}
        </if>
        <if test="isscaned != null and isscaned != ''">
            and m.isscaned = #{isscaned,jdbcType=NVARCHAR}
        </if>
        <if test="isScanning != null and isScanning != ''">
            and m.is_scanning = #{isScanning,jdbcType=NVARCHAR}
        </if>
        ORDER BY discharge_date_time desc
    </select>

    <select id="selectByLast" parameterType="com.emr.entity.Archive_Master_Vo" resultMap="BaseResultMap2">
        select m.id,m.patient_id,m.inp_no,m.visit_id,m.name,m.sex,ISNULL(f.name,m.dept_name) dept_name,CONVERT(varchar(10),m.discharge_date_time, 120) discharge_date_time,m.ArchiveState,CONVERT(varchar(10),m.admission_date_time, 120) admission_date_time,
        ISNULL(d.name,m.dept_admission_to) dept_admission_to,m.check_doctor,m.checked_datetime,m.checked_doctor,m.LockInfo,m.DOCTOR_IN_CHARGE,m.ID_NO,m.DISCHARGE_DISPOSITION,m.dept_code_lend,m.isscaned,
        m.cmt_doctor,m.cmt_nurse,m.doctor_name,m.nurse_name,m.cmt_doctor_date,m.cmt_nurse_date,m.emr_doctor_cmt,m.emr_nure_cmt,m.death_flag,m.first_instance,
        m.is_scanning,m.bed_number,amf.following_type,ISNULL(s.numb, 0) numb,ao.C5 is_sign
        ,case when emr_fault_detail.archive_detail_id is not null then '是' else '否' end isFault,ISNULL(sDoctor.numb, 0) faultDoctorNum,ISNULL(sNurse.numb, 0) faultNurseNum,ISNULL(printTable.num, 0) printNum
        from archive_master m
        LEFT JOIN (select code,name from emr_dictionary where parent_id=(select id from emr_dictionary where
        typeCode='dept_code' )) d
        on m.dept_admission_to=d.code
        LEFT JOIN (select code,name from emr_dictionary where parent_id=(select id from emr_dictionary where
        typeCode='dept_code' )) f
        on m.dept_name=f.code
        LEFT JOIN(select archive_detail_id,count(*) numb from emr_fault_detail where 1=1 and back_flag in ('1','5') GROUP BY archive_detail_id) sDoctor
        on m.id=sDoctor.archive_detail_id
        left join (select distinct archive_detail_id from emr_fault_detail where back_flag in ('6','5')) emr_fault_detail
        on emr_fault_detail.archive_detail_id = m.ID
        LEFT JOIN(select archive_detail_id,count(*) numb from emr_fault_detail where 1=1 and back_flag in ('2','6') GROUP BY archive_detail_id) sNurse
        on m.id=sNurse.archive_detail_id
        left join Archive_Master_Following amf
        on m.id=amf.master_id and amf.following_type=9
        LEFT JOIN(select archive_detail_id,count(*) numb from emr_fault_detail where 1=1  and back_flag is not null GROUP BY archive_detail_id) s
        on m.id=s.archive_detail_id
        LEFT JOIN archive_other ao
        on m.id=ao.masterID
        and ao.sysFlag='1'
        LEFT JOIN (select MasterID,COUNT(*) num from archive_printInfo group by MasterID) printTable
        on m.id=printTable.MasterID
        where 1=1 and ArchiveState in('初审') and  m.Is_Valid!=1
        and patindex('%B%',m.inp_no) = 0 and inp_no not like 'LG%'
        <if test="isFault != null  and isFault != ''">
            <if test="isFault == 1">
                AND emr_fault_detail.archive_detail_id is not null
            </if>
            <if test="isFault == 0">
                AND emr_fault_detail.archive_detail_id is  null
            </if>
        </if>
        <if test="printNum != null and printNum != ''">
            <choose>
                <when test="printNum == 1">
                    and printTable.MasterID is not null
                </when>
                <otherwise>and printTable.MasterID is  null</otherwise>
            </choose>
        </if>
        <if test="inpNo != null and inpNo != ''">
            and m.inp_no like '%'+#{inpNo,jdbcType=NCHAR}+'%'
        </if>
        <if test="bedNumber != null and bedNumber != ''">
            and m.bed_number like '%'+#{bedNumber,jdbcType=NVARCHAR}+'%'
        </if>
        <if test="visitId != null and visitId != ''">
            and m.visit_id=#{visitId,jdbcType=NCHAR}
        </if>
        <if test="name != null and name != ''">
            and m.name like '%'+#{name,jdbcType=NCHAR}+'%'
        </if>
        <if test="archivestate != null and archivestate != ''">
            and m.ArchiveState in(#{archivestate,jdbcType=NVARCHAR})
        </if>
        <if test="idNo != null and idNo != ''">
            and m.ID_NO like '%'+#{idNo,jdbcType=NCHAR}+'%'
        </if>
        <if test="sex != null">
            and m.sex = #{sex,jdbcType=NVARCHAR}
        </if>
        <if test="deptName!= null and deptName!= ''">
            and m.dept_name in
            <foreach item="item" collection="deptName.split(',')" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="deptAdmissionTo != null and deptAdmissionTo!=''">
            and m.dept_admission_to = #{deptAdmissionTo,jdbcType=NVARCHAR}
        </if>
        <if test="dischargeDateTime != null">
            and m.discharge_date_time = #{dischargeDateTime,jdbcType=NVARCHAR}
        </if>
        <choose>
            <when test="startDateTo != null and startDateTo != '' and endDateTo != null and endDateTo != ''">
                and m.discharge_date_time between CONVERT(VARCHAR(10),#{startDateTo,jdbcType=NCHAR},120) and
                #{endDateTo,jdbcType=NCHAR}+ ' 23:59:59'
            </when>
            <when test="startDateTo != null and startDateTo != ''">
                and m.discharge_date_time >= CONVERT(VARCHAR(10),#{startDateTo,jdbcType=NCHAR},120)
            </when>
            <when test="endDateTo != null and endDateTo != ''">
                and m.discharge_date_time &lt;= #{endDateTo,jdbcType=NCHAR}+ ' 23:59:59'
            </when>
        </choose>
        <choose>
            <when test="startDate != null and startDate != '' and endDate != null and endDate != ''">
                and m.admission_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 m.admission_date_time >= CONVERT(VARCHAR(10),#{startDate,jdbcType=NCHAR},120)
            </when>
            <when test="endDate != null and endDate != ''">
                and m.admission_date_time &lt;= #{endDate,jdbcType=NCHAR}+ ' 23:59:59'
            </when>
        </choose>

        <if test="checkDoctor != null">
            and m.check_doctor = #{checkDoctor,jdbcType=NVARCHAR}
        </if>
        <if test="checkDatetime != null">
            and m.check_datetime = #{checkDatetime,jdbcType=NVARCHAR}
        </if>
        <if test="checkedDoctor != null">
            and m.checked_doctor = #{checkedDoctor,jdbcType=NVARCHAR}
        </if>
        <if test="checkedDatetime != null">
            and m.checked_datetime = #{checkedDatetime,jdbcType=NVARCHAR}
        </if>
        <if test="lockinfo != null">
            and m.LockInfo = #{lockinfo,jdbcType=NVARCHAR}
        </if>
        <if test="doctorInCharge != null">
            and m.DOCTOR_IN_CHARGE = #{doctorInCharge,jdbcType=NVARCHAR}
        </if>
        <if test="dischargeDisposition != null">
            and m.DISCHARGE_DISPOSITION = #{dischargeDisposition,jdbcType=NVARCHAR}
        </if>
        <if test="deptCodeLend != null">
            and m.dept_code_lend = #{deptCodeLend,jdbcType=NVARCHAR}
        </if>
        <if test="isscaned != null and isscaned != ''">
            and m.isscaned = #{isscaned,jdbcType=NVARCHAR}
        </if>
        <if test="isScanning != null and isScanning != ''">
            and m.is_scanning = #{isScanning,jdbcType=NVARCHAR}
        </if>
        <if test="isSign != null ">
            and ao.C5 = #{isSign,jdbcType=NVARCHAR}
        </if>
        <if test="signer != null and signer != ''">
            and ao.C1 like '%'+#{signer,jdbcType=NCHAR}+'%'
        </if>
        <choose>
            <when test="startSignDate != null and startSignDate != '' and endSignDate != null and endSignDate != ''">
                and m.sign_time between CONVERT(VARCHAR(10),#{startSignDate,jdbcType=NCHAR},120) and
                #{endSignDate,jdbcType=NCHAR}+ ' 23:59:59'
            </when>
            <when test="startSignDate != null and startSignDate != ''">
                and m.sign_time >= CONVERT(VARCHAR(10),#{startDate,jdbcType=NCHAR},120)
            </when>
            <when test="endSignDate != null and endSignDate != ''">
                and m.sign_time &lt;= #{endSignDate,jdbcType=NCHAR}+ ' 23:59:59'
            </when>
        </choose>

        ORDER BY discharge_date_time desc
    </select>

    <select id="selectByUnfile" parameterType="com.emr.entity.Archive_Master_Vo" resultMap="BaseResultMap2">
        select m.id,m.patient_id,m.inp_no,m.visit_id,m.name,m.sex,ISNULL(f.name,m.dept_name) dept_name,CONVERT(varchar(10),m.discharge_date_time, 120) discharge_date_time
        ,m.ArchiveState,CONVERT(varchar(10),m.admission_date_time, 120) admission_date_time,ISNULL(d.name,m.dept_admission_to) dept_admission_to
        ,m.check_doctor,m.checked_datetime,m.checked_doctor,m.LockInfo,m.DOCTOR_IN_CHARGE,m.ID_NO,m.DISCHARGE_DISPOSITION,m.dept_code_lend,m.isscaned,
        m.cmt_doctor,m.cmt_nurse,m.doctor_name,m.nurse_name,m.cmt_doctor_date,m.cmt_nurse_date,m.emr_doctor_cmt,m.emr_nure_cmt,m.death_flag,m.is_scanning,
        m.bed_number,ISNULL(s.numb, 0) numb
        from archive_master m
        LEFT JOIN (select code,name from emr_dictionary where parent_id=(select id from emr_dictionary where
        typeCode='dept_code' )) d
        on m.dept_admission_to=d.code
        LEFT JOIN (select code,name from emr_dictionary where parent_id=(select id from emr_dictionary where
        typeCode='dept_code' )) f
        on m.dept_name=f.code
        LEFT JOIN(select archive_detail_id,count(*) numb from emr_fault_detail where 1=1  and back_flag is not null GROUP BY archive_detail_id) s
        on m.id=s.archive_detail_id
        where 1=1
        and m.ArchiveState not in('作废','已封存') and  m.Is_Valid!=1 and inp_no not like 'LG%'
        <if test="id != null and id != ''">
            and m.id = #{id,jdbcType=NVARCHAR}
        </if>
        <if test="inpNo != null and inpNo != ''">
            and m.inp_no like '%'+#{inpNo,jdbcType=NCHAR}+'%'
        </if>
        <if test="bedNumber != null and bedNumber != ''">
            and m.bed_number like '%'+#{bedNumber,jdbcType=NVARCHAR}+'%'
        </if>
        <if test="visitId != null and visitId != ''">
            and m.visit_id=#{visitId,jdbcType=NCHAR}
        </if>
        <if test="name != null and name != ''">
            and m.name like '%'+#{name,jdbcType=NCHAR}+'%'
        </if>
        <if test="archivestate != null and archivestate != ''">
            and m.ArchiveState in
            <foreach item="item" collection="archivestate.split(',')" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="idNo != null and idNo != ''">
            and m.ID_NO like '%'+#{idNo,jdbcType=NCHAR}+'%'
        </if>
        <if test="sex != null">
            and m.sex = #{sex,jdbcType=NVARCHAR}
        </if>
        <if test="deptName!= null and deptName!= ''">
            and m.dept_name in
            <foreach item="item" collection="deptName.split(',')" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="deptAdmissionTo != null and deptAdmissionTo!=''">
            and m.dept_admission_to in
            <foreach item="item" collection="deptAdmissionTo.split(',')" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <!--<if test="deptAdmissionTo != null and deptAdmissionTo!=''">-->
        <!--and m.dept_admission_to = #{deptAdmissionTo,jdbcType=NVARCHAR}-->
        <!--</if>-->
        <if test="dischargeDateTime != null">
            and m.discharge_date_time = #{dischargeDateTime,jdbcType=NVARCHAR}
        </if>
        <choose>
            <when test="startDateTo != null and startDateTo != '' and endDateTo != null and endDateTo != ''">
                and m.discharge_date_time between CONVERT(VARCHAR(10),#{startDateTo,jdbcType=NCHAR},120) and
                #{endDateTo,jdbcType=NCHAR}+ ' 23:59:59'
            </when>
            <when test="startDateTo != null and startDateTo != ''">
                and m.discharge_date_time >= CONVERT(VARCHAR(10),#{startDateTo,jdbcType=NCHAR},120)
            </when>
            <when test="endDateTo != null and endDateTo != ''">
                and m.discharge_date_time &lt;= #{endDateTo,jdbcType=NCHAR}+ ' 23:59:59'
            </when>
        </choose>
        <choose>
            <when test="startDate != null and startDate != '' and endDate != null and endDate != ''">
                and m.admission_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 m.admission_date_time >= CONVERT(VARCHAR(10),#{startDate,jdbcType=NCHAR},120)
            </when>
            <when test="endDate != null and endDate != ''">
                and m.admission_date_time &lt;= #{endDate,jdbcType=NCHAR}+ ' 23:59:59'
            </when>
        </choose>

        <if test="checkDoctor != null">
            and m.check_doctor = #{checkDoctor,jdbcType=NVARCHAR}
        </if>
        <if test="checkedDoctor != null">
            and m.checked_doctor = #{checkedDoctor,jdbcType=NVARCHAR}
        </if>
        <if test="checkDatetime != null">
            and m.check_datetime = #{checkDatetime,jdbcType=NVARCHAR}
        </if>
        <if test="checkedDatetime != null">
            and m.checked_datetime = #{checkedDatetime,jdbcType=NVARCHAR}
        </if>
        <if test="lockinfo != null">
            and m.LockInfo = #{lockinfo,jdbcType=NVARCHAR}
        </if>
        <if test="doctorInCharge != null">
            and m.DOCTOR_IN_CHARGE = #{doctorInCharge,jdbcType=NVARCHAR}
        </if>
        <if test="dischargeDisposition != null">
            and m.DISCHARGE_DISPOSITION = #{dischargeDisposition,jdbcType=NVARCHAR}
        </if>
        <if test="deptCodeLend != null">
            and m.dept_code_lend = #{deptCodeLend,jdbcType=NVARCHAR}
        </if>
        <if test="isscaned != null and isscaned != ''">
            and m.isscaned = #{isscaned,jdbcType=NVARCHAR}
        </if>
        <if test="isScanning != null and isScanning != ''">
            and m.is_scanning = #{isScanning,jdbcType=NVARCHAR}
        </if>
        <if test="deathFlag != null and deathFlag != ''">
            and m.death_flag = #{deathFlag,jdbcType=NVARCHAR}
        </if>
        ORDER BY discharge_date_time desc
    </select>

    <select id="selectByUnfileNew" parameterType="com.emr.entity.Archive_Master_Vo" resultMap="BaseResultMap2">
        select m.id,m.patient_id,m.inp_no,m.visit_id,m.name,m.sex,ISNULL(f.name,m.dept_name) dept_name
        ,CONVERT(varchar(10),m.discharge_date_time, 120) discharge_date_time,m.ArchiveState,CONVERT(varchar(10),m.admission_date_time, 120) admission_date_time,ISNULL(d.name,m.dept_admission_to) dept_admission_to
        ,m.check_doctor,m.checked_datetime,m.checked_doctor,m.LockInfo,m.DOCTOR_IN_CHARGE,m.ID_NO,m.DISCHARGE_DISPOSITION,m.dept_code_lend,m.isscaned,
        m.cmt_doctor,m.cmt_nurse,m.doctor_name,m.nurse_name,m.cmt_doctor_date,m.cmt_nurse_date,m.emr_doctor_cmt,m.emr_nure_cmt,m.death_flag,m.is_scanning,
        m.bed_number,ISNULL(s.numb, 0) numb,ISNULL(amf.num, 0) checkFlag
        ,ISNULL(sDoctor.numb, 0) faultDoctorNum,ISNULL(sNurse.numb, 0) faultNurseNum,ISNULL(printTable.num, 0) printNum
        from archive_master m
        LEFT JOIN(select master_id,count(*) num from Archive_Master_Following where following_type=5 GROUP BY  master_id ) amf
        on m.id=amf.master_id
        LEFT JOIN(select archive_detail_id,count(*) numb from emr_fault_detail where 1=1 and back_flag in ('1','5') GROUP BY archive_detail_id) sDoctor
        on m.id=sDoctor.archive_detail_id
        LEFT JOIN(select archive_detail_id,count(*) numb from emr_fault_detail where 1=1 and back_flag in ('2','6') GROUP BY archive_detail_id) sNurse
        on m.id=sNurse.archive_detail_id
        LEFT JOIN (select code,name from emr_dictionary where parent_id=(select id from emr_dictionary where
        typeCode='dept_code' )) d
        on m.dept_admission_to=d.code
        LEFT JOIN (select code,name from emr_dictionary where parent_id=(select id from emr_dictionary where
        typeCode='dept_code' )) f
        on m.dept_name=f.code
        LEFT JOIN(select archive_detail_id,count(*) numb from emr_fault_detail where 1=1  and back_flag is not null GROUP BY archive_detail_id) s
        on m.id=s.archive_detail_id
        LEFT JOIN (select MasterID,COUNT(*) num from archive_printInfo group by MasterID) printTable
        on m.id=printTable.MasterID
        where 1=1
        and patindex('%B%',m.inp_no) in (0,1)
        and m.ArchiveState not in('作废','已封存') and  m.Is_Valid!=1 and inp_no not like 'LG%'
        <if test="printNum != null and printNum != ''">
            <choose>
                <when test="printNum == 1">
                    and printTable.MasterID is not null
                </when>
                <otherwise>and printTable.MasterID is  null</otherwise>
            </choose>
        </if>
        <if test="inpNo != null and inpNo != ''">
            and m.inp_no like '%'+#{inpNo,jdbcType=NCHAR}+'%'
        </if>
        <if test="bedNumber != null and bedNumber != ''">
            and m.bed_number like '%'+#{bedNumber,jdbcType=NVARCHAR}+'%'
        </if>
        <if test="visitId != null and visitId != ''">
            and m.visit_id=#{visitId,jdbcType=NCHAR}
        </if>
        <if test="name != null and name != ''">
            and m.name like '%'+#{name,jdbcType=NCHAR}+'%'
        </if>
        <if test="archivestate != null and archivestate != ''">
            and m.ArchiveState in
            <foreach item="item" collection="archivestate.split(',')" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="idNo != null and idNo != ''">
            and m.ID_NO like '%'+#{idNo,jdbcType=NCHAR}+'%'
        </if>
        <if test="sex != null">
            and m.sex = #{sex,jdbcType=NVARCHAR}
        </if>
        <if test="deptName!= null and deptName!= ''">
            and m.dept_name in
            <foreach item="item" collection="deptName.split(',')" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="deptAdmissionTo != null and deptAdmissionTo!=''">
            and m.dept_admission_to in
            <foreach item="item" collection="deptAdmissionTo.split(',')" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <!--<if test="deptAdmissionTo != null and deptAdmissionTo!=''">-->
        <!--and m.dept_admission_to = #{deptAdmissionTo,jdbcType=NVARCHAR}-->
        <!--</if>-->
        <if test="dischargeDateTime != null">
            and m.discharge_date_time = #{dischargeDateTime,jdbcType=NVARCHAR}
        </if>
        <choose>
            <when test="startDateTo != null and startDateTo != '' and endDateTo != null and endDateTo != ''">
                and m.discharge_date_time between CONVERT(VARCHAR(10),#{startDateTo,jdbcType=NCHAR},120) and
                #{endDateTo,jdbcType=NCHAR}+ ' 23:59:59'
            </when>
            <when test="startDateTo != null and startDateTo != ''">
                and m.discharge_date_time >= CONVERT(VARCHAR(10),#{startDateTo,jdbcType=NCHAR},120)
            </when>
            <when test="endDateTo != null and endDateTo != ''">
                and m.discharge_date_time &lt;= #{endDateTo,jdbcType=NCHAR}+ ' 23:59:59'
            </when>
        </choose>
        <choose>
            <when test="startDate != null and startDate != '' and endDate != null and endDate != ''">
                and m.admission_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 m.admission_date_time >= CONVERT(VARCHAR(10),#{startDate,jdbcType=NCHAR},120)
            </when>
            <when test="endDate != null and endDate != ''">
                and m.admission_date_time &lt;= #{endDate,jdbcType=NCHAR}+ ' 23:59:59'
            </when>
        </choose>

        <if test="checkDoctor != null">
            and m.check_doctor = #{checkDoctor,jdbcType=NVARCHAR}
        </if>
        <if test="checkedDoctor != null">
            and m.checked_doctor = #{checkedDoctor,jdbcType=NVARCHAR}
        </if>
        <if test="checkDatetime != null">
            and m.check_datetime = #{checkDatetime,jdbcType=NVARCHAR}
        </if>
        <if test="checkedDatetime != null">
            and m.checked_datetime = #{checkedDatetime,jdbcType=NVARCHAR}
        </if>
        <if test="lockinfo != null">
            and m.LockInfo = #{lockinfo,jdbcType=NVARCHAR}
        </if>
        <if test="doctorInCharge != null">
            and m.DOCTOR_IN_CHARGE = #{doctorInCharge,jdbcType=NVARCHAR}
        </if>
        <if test="dischargeDisposition != null">
            and m.DISCHARGE_DISPOSITION = #{dischargeDisposition,jdbcType=NVARCHAR}
        </if>
        <if test="deptCodeLend != null">
            and m.dept_code_lend = #{deptCodeLend,jdbcType=NVARCHAR}
        </if>
        <if test="isscaned != null and isscaned != ''">
            and m.isscaned = #{isscaned,jdbcType=NVARCHAR}
        </if>
        <if test="isScanning != null and isScanning != ''">
            and m.is_scanning = #{isScanning,jdbcType=NVARCHAR}
        </if>
        <if test="deathFlag != null and deathFlag != ''">
            and m.death_flag = #{deathFlag,jdbcType=NVARCHAR}
        </if>
        ORDER BY discharge_date_time desc
    </select>

    <select id="selectByCmtCol" parameterType="com.emr.entity.Archive_Master_Vo" resultMap="BaseResultMap2">
        select m.id,m.patient_id,m.inp_no,m.visit_id,m.name,m.sex,ISNULL(f.name,m.dept_name) dept_name
        ,CONVERT(varchar(10),m.discharge_date_time, 120) discharge_date_time,m.ArchiveState,CONVERT(varchar(10),m.admission_date_time, 120) admission_date_time,ISNULL(d.name,m.dept_admission_to) dept_admission_to
        ,m.check_doctor,m.checked_datetime,m.checked_doctor,m.LockInfo,m.DOCTOR_IN_CHARGE,m.ID_NO,m.DISCHARGE_DISPOSITION,m.dept_code_lend,m.isscaned,m.is_scanning,
        m.cmt_doctor,m.cmt_nurse,m.doctor_name,m.nurse_name,m.cmt_doctor_date,m.cmt_nurse_date,m.emr_doctor_cmt,m.emr_nure_cmt,m.death_flag,m.bed_number,ISNULL(s.numb, 0) numb
        ,ISNULL(sDoctor.numb, 0) faultDoctorNum,ISNULL(sNurse.numb, 0) faultNurseNum,ISNULL(printTable.num, 0) printNum
        from archive_master m
        LEFT JOIN (select code,name from emr_dictionary where parent_id=(select id from emr_dictionary where
        typeCode='dept_code' )) d
        on m.dept_admission_to=d.code
        LEFT JOIN(select archive_detail_id,count(*) numb from emr_fault_detail where 1=1 and back_flag in ('1','5') GROUP BY archive_detail_id) sDoctor
        on m.id=sDoctor.archive_detail_id
        LEFT JOIN(select archive_detail_id,count(*) numb from emr_fault_detail where 1=1 and back_flag in ('2','6') GROUP BY archive_detail_id) sNurse
        on m.id=sNurse.archive_detail_id
        LEFT JOIN (select code,name from emr_dictionary where parent_id=(select id from emr_dictionary where
        typeCode='dept_code' )) f
        on m.dept_name=f.code
        LEFT JOIN(select archive_detail_id,count(*) numb from emr_fault_detail where 1=1  and back_flag is not null GROUP BY archive_detail_id) s
        on m.id=s.archive_detail_id
        LEFT JOIN (select MasterID,COUNT(*) num from archive_printInfo group by MasterID) printTable
        on m.id=printTable.MasterID
        where 1=1 and ArchiveState in('提交','主任退回','归档中','复审退回')
        and patindex('%B%',m.inp_no) = 0 and inp_no not like 'LG%' and  m.Is_Valid!=1
        <if test="printNum != null and printNum != ''">
            <choose>
                <when test="printNum == 1">
                    and printTable.MasterID is not null
                </when>
                <otherwise>and printTable.MasterID is  null</otherwise>
            </choose>
        </if>
        <if test="inpNo != null and inpNo != ''">
            and m.inp_no like '%'+#{inpNo,jdbcType=NCHAR}+'%'
        </if>
        <if test="bedNumber != null and bedNumber != ''">
            and m.bed_number like '%'+#{bedNumber,jdbcType=NVARCHAR}+'%'
        </if>
        <if test="visitId != null and visitId != ''">
            and m.visit_id=#{visitId,jdbcType=NCHAR}
        </if>
        <if test="name != null and name != ''">
            and m.name like '%'+#{name,jdbcType=NCHAR}+'%'
        </if>
        <if test="archivestate != null and archivestate != ''">
            and m.ArchiveState=#{archivestate,jdbcType=NVARCHAR}
        </if>
        <if test="idNo != null and idNo != ''">
            and m.ID_NO like '%'+#{idNo,jdbcType=NCHAR}+'%'
        </if>
        <if test="sex != null">
            and m.sex = #{sex,jdbcType=NVARCHAR}
        </if>
        <if test="deptName!= null and deptName!= ''">
            and m.dept_name in
            <foreach item="item" collection="deptName.split(',')" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="dischargeDateTime != null">
            and m.discharge_date_time = #{dischargeDateTime,jdbcType=NVARCHAR}
        </if>
        <choose>
            <when test="startDateTo != null and startDateTo != '' and endDateTo != null and endDateTo != ''">
                and m.discharge_date_time between CONVERT(VARCHAR(10),#{startDateTo,jdbcType=NCHAR},120) and
                #{endDateTo,jdbcType=NCHAR}+ ' 23:59:59'
            </when>
            <when test="startDateTo != null and startDateTo != ''">
                and m.discharge_date_time >= CONVERT(VARCHAR(10),#{startDateTo,jdbcType=NCHAR},120)
            </when>
            <when test="endDateTo != null and endDateTo != ''">
                and m.discharge_date_time &lt;= #{endDateTo,jdbcType=NCHAR}+ ' 23:59:59'
            </when>
        </choose>
        <if test="deptAdmissionTo != null and deptAdmissionTo!=''">
            and m.dept_admission_to = #{deptAdmissionTo,jdbcType=NVARCHAR}
        </if>
        <if test="checkDoctor != null">
            and m.check_doctor = #{checkDoctor,jdbcType=NVARCHAR}
        </if>
        <if test="checkedDoctor != null">
            and checked_doctor = #{checkedDoctor,jdbcType=NVARCHAR}
        </if>
        <if test="checkDatetime != null">
            and m.check_datetime = #{checkDatetime,jdbcType=NVARCHAR}
        </if>
        <if test="checkedDatetime != null">
            and m.checked_datetime = #{checkedDatetime,jdbcType=NVARCHAR}
        </if>
        <if test="lockinfo != null">
            and m.LockInfo = #{lockinfo,jdbcType=NVARCHAR}
        </if>
        <if test="doctorInCharge != null">
            and m.DOCTOR_IN_CHARGE = #{doctorInCharge,jdbcType=NVARCHAR}
        </if>
        <if test="dischargeDisposition != null">
            and m.DISCHARGE_DISPOSITION = #{dischargeDisposition,jdbcType=NVARCHAR}
        </if>
        <if test="deptCodeLend != null">
            and m.dept_code_lend = #{deptCodeLend,jdbcType=NVARCHAR}
        </if>
        <if test="isscaned != null and isscaned != ''">
            and m.isscaned = #{isscaned,jdbcType=NVARCHAR}
        </if>
        <if test="isScanning != null and isScanning != ''">
            and m.is_scanning = #{isScanning,jdbcType=NVARCHAR}
        </if>
        ORDER BY discharge_date_time desc
    </select>
    <!-- m.ArchiveState in('归档中','初审','提交','主任退回')-->
    <select id="getDoctorInCharge" parameterType="com.emr.entity.Archive_Master" resultMap="BaseResultMap">
        select DISTINCT m.DOCTOR_IN_CHARGE from archive_master m
        where 1=1
        <if test="archivestate!= null and archivestate!= ''">
            and m.ArchiveState in
            <foreach item="item" collection="archivestate.split(',')" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="deptName!= null and deptName!= ''">
            and m.dept_name in
            <foreach item="item" collection="deptName.split(',')" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="doctorInCharge!= null and doctorInCharge!= ''">
            and m.DOCTOR_IN_CHARGE in
            <foreach item="item" collection="doctorInCharge.split(',')" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
    </select>
    <select id="bbSel" parameterType="com.emr.entity.Archive_Master_Vo" resultMap="BaseResultMap">
        select a.*,ISNULL(b.name, a.dept_name) name from(select dept_name,
        SUM(CASE WHEN ArchiveState is not null and ArchiveState not IN ('已封存','在院','取消入院','作废','')
        <choose>
            <when test="startDateTo != null and startDateTo != '' and endDateTo != null and endDateTo != ''">
                and CONVERT(VARCHAR(10),discharge_date_time,120) between CONVERT(VARCHAR(10),#{startDateTo,jdbcType=NCHAR},120) and #{endDateTo,jdbcType=NCHAR}
            </when>
            <when test="startDateTo != null and startDateTo != ''">
                and CONVERT(VARCHAR(10),discharge_date_time,120) >= CONVERT(VARCHAR(10),#{startDateTo,jdbcType=NCHAR},120)
            </when>
            <when test="endDateTo != null and endDateTo != ''">
                and CONVERT(VARCHAR(10),discharge_date_time,120) &lt;= #{endDateTo,jdbcType=NCHAR}
            </when>
        </choose>
        AND Is_Valid != 1  and patindex('%B%',inp_no) = 0 and inp_no not like 'LG%' THEN 1 ELSE 0 END) as checkDoctor,
        SUM(CASE WHEN ArchiveState is not null and ArchiveState!='' and ArchiveState IN ('归档中','提交','主任退回','复审退回')
        <choose>
            <when test="startDateTo != null and startDateTo != '' and endDateTo != null and endDateTo != ''">
                and CONVERT(VARCHAR(10),discharge_date_time,120) between CONVERT(VARCHAR(10),#{startDateTo,jdbcType=NCHAR},120) and #{endDateTo,jdbcType=NCHAR}
            </when>
            <when test="startDateTo != null and startDateTo != ''">
                and CONVERT(VARCHAR(10),discharge_date_time,120) >= CONVERT(VARCHAR(10),#{startDateTo,jdbcType=NCHAR},120)
            </when>
            <when test="endDateTo != null and endDateTo != ''">
                and CONVERT(VARCHAR(10),discharge_date_time,120) &lt;= #{endDateTo,jdbcType=NCHAR}
            </when>
        </choose>
        AND Is_Valid != 1 and patindex('%B%',inp_no) = 0 and inp_no not like 'LG%' THEN 1 ELSE 0 END) as lockinfo,
        SUM(CASE WHEN ArchiveState is not null and ArchiveState not IN ('已封存','归档中','提交','主任退回','复审退回','','作废','取消入院')
        <choose>
            <when test="startDateTo != null and startDateTo != '' and endDateTo != null and endDateTo != ''">
                and CONVERT(VARCHAR(10),discharge_date_time,120) between CONVERT(VARCHAR(10),#{startDateTo,jdbcType=NCHAR},120) and #{endDateTo,jdbcType=NCHAR}
            </when>
            <when test="startDateTo != null and startDateTo != ''">
                and CONVERT(VARCHAR(10),discharge_date_time,120) >= CONVERT(VARCHAR(10),#{startDateTo,jdbcType=NCHAR},120)
            </when>
            <when test="endDateTo != null and endDateTo != ''">
                and CONVERT(VARCHAR(10),discharge_date_time,120) &lt;= #{endDateTo,jdbcType=NCHAR}
            </when>
        </choose>
        AND Is_Valid != 1 and patindex('%B%',inp_no) = 0 and inp_no not like 'LG%' THEN 1 ELSE 0 END) as inpNo,
        SUM(CASE WHEN ArchiveState is not null and  ArchiveState not IN ('已封存','归档中','提交','主任退回','复审退回','','作废','取消入院')
        <choose>
            <when test="startDateTo != null and startDateTo != '' and endDateTo != null and endDateTo != ''">
                and CONVERT(VARCHAR(10),discharge_date_time,120) between CONVERT(VARCHAR(10),#{startDateTo,jdbcType=NCHAR},120) and #{endDateTo,jdbcType=NCHAR}
            </when>
            <when test="startDateTo != null and startDateTo != ''">
                and CONVERT(VARCHAR(10),discharge_date_time,120) >= CONVERT(VARCHAR(10),#{startDateTo,jdbcType=NCHAR},120)
            </when>
            <when test="endDateTo != null and endDateTo != ''">
                and CONVERT(VARCHAR(10),discharge_date_time,120) &lt;= #{endDateTo,jdbcType=NCHAR}
            </when>
        </choose>
        AND Is_Valid != 1 and patindex('%B%',inp_no) = 0 and inp_no not like 'LG%'
        <if test="remark!= null and remark!= ''">
            and CEILING(DATEDIFF(hour,discharge_date_time,GETDATE())/24.00)>#{remark,jdbcType=NCHAR}
        </if>
        <if test="remark== null and remark== ''">
            and CEILING(DATEDIFF(hour,discharge_date_time,GETDATE())/24.00)>3
        </if>
        THEN 1 ELSE 0 END) as visitId
        FROM archive_master GROUP BY dept_name
        ) a
        left join(
        select code,name from emr_dictionary where parent_id=(select id from emr_dictionary where typeCode='dept_code' )
        ) b
        on a.dept_name=b.code
        <where>
            1=1
        </where>
        <if test="deptName!= null and deptName!= ''">
            and a.dept_name in
            <foreach item="item" collection="deptName.split(',')" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
    </select>
    <select id="bbSel2" parameterType="com.emr.entity.Archive_Master_Vo" resultMap="BaseResultMap">
        select id,inp_no,visit_id,U.name,dept_name,E.name doctor_name,DOCTOR_IN_CHARGE,ArchiveState,ISNULL(e.name, u.dept_name) doctorName,
        CONVERT(varchar(100),admission_date_time, 120) admission_date_time,CONVERT(varchar(100),U.discharge_date_time, 120) discharge_date_time
        ,bed_number,GETDATE() nowt,
        <if test="remark!= null and remark!= ''">
            CEILING(DATEDIFF(hour,U.discharge_date_time,GETDATE())/24.00)-#{remark,jdbcType=NCHAR} nurse_name
        </if>
        <if test="remark== null or remark== ''">
            CEILING(DATEDIFF(hour,U.discharge_date_time,GETDATE())/24.00)-3 nurse_name
        </if>
        from archive_master U
        LEFT JOIN (
           select code,name from emr_dictionary where parent_id=(select id from emr_dictionary where typeCode='dept_code' )
        ) E
        ON U.dept_name=E.code
        <where> 1=1 and U.ArchiveState in('归档中','提交','主任退回','复审退回') AND U.Is_Valid != 1 and patindex('%B%',inp_no) = 0 and inp_no not like 'LG%'
            <if test="remark!= null and remark!= ''">
                and CEILING(DATEDIFF(hour,U.discharge_date_time,GETDATE())/24.00)>#{remark,jdbcType=NCHAR}
            </if>
            <if test="remark== null or remark== ''">
                and CEILING(DATEDIFF(hour,U.discharge_date_time,GETDATE())/24.00)>3
            </if>
            <if test="deptName!= null and deptName!= ''">
                and U.dept_name 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(100),U.discharge_date_time, 23) between #{startDateTo,jdbcType=NCHAR} and #{endDateTo,jdbcType=NCHAR}
                </when>
                <when test="startDateTo != null and startDateTo != ''">
                    and CONVERT(varchar(100),U.discharge_date_time, 23) >= #{startDateTo,jdbcType=NCHAR}
                </when>
                <when test="endDateTo != null and endDateTo != ''">
                    and CONVERT(varchar(100),U.discharge_date_time, 23) &lt;= #{endDateTo,jdbcType=NCHAR}
                </when>
            </choose>
        </where>
    </select>
    <delete id="deleteByPrimaryKey" parameterType="java.lang.String">
    delete from archive_master
    where id = #{id,jdbcType=NVARCHAR}
  </delete>
    <insert id="insert" parameterType="com.emr.entity.Archive_Master">
    insert into archive_master (id, patient_id, inp_no,
      visit_id, name, sex,
      dept_name, discharge_date_time, ArchiveState,
      admission_date_time, dept_admission_to, check_doctor,
      check_datetime, checked_doctor, checked_datetime,
      LockInfo, DOCTOR_IN_CHARGE, ID_NO,
      DISCHARGE_DISPOSITION, dept_code_lend,isscaned,first_instance)
    values (#{id,jdbcType=NVARCHAR}, #{patientId,jdbcType=NVARCHAR}, #{inpNo,jdbcType=NVARCHAR},
      #{visitId,jdbcType=NVARCHAR}, #{name,jdbcType=NVARCHAR}, #{sex,jdbcType=NVARCHAR},
      #{deptName,jdbcType=NVARCHAR}, #{dischargeDateTime,jdbcType=NVARCHAR}, #{archivestate,jdbcType=NVARCHAR},
      #{admissionDateTime,jdbcType=NVARCHAR}, #{deptAdmissionTo,jdbcType=NVARCHAR}, #{checkDoctor,jdbcType=NVARCHAR},
      #{checkDatetime,jdbcType=NVARCHAR}, #{checkedDoctor,jdbcType=NVARCHAR}, #{checkedDatetime,jdbcType=NVARCHAR},
      #{lockinfo,jdbcType=NVARCHAR}, #{doctorInCharge,jdbcType=NVARCHAR}, #{idNo,jdbcType=NVARCHAR},
      #{dischargeDisposition,jdbcType=NVARCHAR}, #{deptCodeLend,jdbcType=NVARCHAR}, #{isscaned,jdbcType=NVARCHAR}, #{firstInstance,jdbcType=NVARCHAR})
  </insert>
    <insert id="insertSelective" parameterType="com.emr.entity.Archive_Master">
        insert into archive_master
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">
                id,
            </if>
            <if test="patientId != null">
                patient_id,
            </if>
            <if test="inpNo != null">
                inp_no,
            </if>
            <if test="visitId != null">
                visit_id,
            </if>
            <if test="name != null">
                name,
            </if>
            <if test="sex != null">
                sex,
            </if>
            <if test="deptName != null">
                dept_name,
            </if>
            <if test="dischargeDateTime != null">
                discharge_date_time,
            </if>
            <if test="archivestate != null">
                ArchiveState,
            </if>
            <if test="admissionDateTime != null">
                admission_date_time,
            </if>
            <if test="deptAdmissionTo != null">
                dept_admission_to,
            </if>
            <if test="checkDoctor != null">
                check_doctor,
            </if>
            <if test="checkDatetime != null">
                check_datetime,
            </if>
            <if test="checkedDoctor != null">
                checked_doctor,
            </if>
            <if test="checkedDatetime != null">
                checked_datetime,
            </if>
            <if test="lockinfo != null">
                LockInfo,
            </if>
            <if test="doctorInCharge != null">
                DOCTOR_IN_CHARGE,
            </if>
            <if test="idNo != null">
                ID_NO,
            </if>
            <if test="dischargeDisposition != null">
                DISCHARGE_DISPOSITION,
            </if>
            <if test="deptCodeLend != null">
                dept_code_lend,
            </if>
            <if test="isscaned != null and isscaned != ''">
                isscaned,
            </if>
            <if test="firstInstance != null">
                first_instance,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">
                #{id,jdbcType=NVARCHAR},
            </if>
            <if test="patientId != null">
                #{patientId,jdbcType=NVARCHAR},
            </if>
            <if test="inpNo != null">
                #{inpNo,jdbcType=NVARCHAR},
            </if>
            <if test="visitId != null">
                #{visitId,jdbcType=NVARCHAR},
            </if>
            <if test="name != null">
                #{name,jdbcType=NVARCHAR},
            </if>
            <if test="sex != null">
                #{sex,jdbcType=NVARCHAR},
            </if>
            <if test="deptName != null">
                #{deptName,jdbcType=NVARCHAR},
            </if>
            <if test="dischargeDateTime != null">
                #{dischargeDateTime,jdbcType=NVARCHAR},
            </if>
            <if test="archivestate != null">
                #{archivestate,jdbcType=NVARCHAR},
            </if>
            <if test="admissionDateTime != null">
                #{admissionDateTime,jdbcType=NVARCHAR},
            </if>
            <if test="deptAdmissionTo != null">
                #{deptAdmissionTo,jdbcType=NVARCHAR},
            </if>
            <if test="checkDoctor != null">
                #{checkDoctor,jdbcType=NVARCHAR},
            </if>
            <if test="checkDatetime != null">
                #{checkDatetime,jdbcType=NVARCHAR},
            </if>
            <if test="checkedDoctor != null">
                #{checkedDoctor,jdbcType=NVARCHAR},
            </if>
            <if test="checkedDatetime != null">
                #{checkedDatetime,jdbcType=NVARCHAR},
            </if>
            <if test="lockinfo != null">
                #{lockinfo,jdbcType=NVARCHAR},
            </if>
            <if test="doctorInCharge != null">
                #{doctorInCharge,jdbcType=NVARCHAR},
            </if>
            <if test="idNo != null">
                #{idNo,jdbcType=NVARCHAR},
            </if>
            <if test="dischargeDisposition != null">
                #{dischargeDisposition,jdbcType=NVARCHAR},
            </if>
            <if test="deptCodeLend != null">
                #{deptCodeLend,jdbcType=NVARCHAR},
            </if>
            <if test="isscaned != null and isscaned != ''">
                #{isscaned,jdbcType=NVARCHAR},
            </if>
            <if test="firstInstance != null">
                #{firstInstance,jdbcType=NVARCHAR},
            </if>
        </trim>
    </insert>
    <update id="updateByClo" parameterType="com.emr.entity.Archive_Master">
        update archive_master
        <set>
            <if test="patientId != null">
                patient_id = #{patientId,jdbcType=NVARCHAR},
            </if>
            <if test="inpNo != null">
                inp_no = #{inpNo,jdbcType=NVARCHAR},
            </if>
            <if test="visitId != null">
                visit_id = #{visitId,jdbcType=NVARCHAR},
            </if>
            <if test="name != null">
                name = #{name,jdbcType=NVARCHAR},
            </if>
            <if test="sex != null">
                sex = #{sex,jdbcType=NVARCHAR},
            </if>
            <if test="deptName != null">
                dept_name = #{deptName,jdbcType=NVARCHAR},
            </if>
            <if test="dischargeDateTime != null">
                discharge_date_time = #{dischargeDateTime,jdbcType=NVARCHAR},
            </if>
            <if test="archivestate != null">
                ArchiveState = #{archivestate,jdbcType=NVARCHAR},
            </if>
            <if test="admissionDateTime != null">
                admission_date_time = #{admissionDateTime,jdbcType=NVARCHAR},
            </if>
            <if test="deptAdmissionTo != null">
                dept_admission_to = #{deptAdmissionTo,jdbcType=NVARCHAR},
            </if>
            <if test="checkDoctor != null">
                check_doctor = #{checkDoctor,jdbcType=NVARCHAR},
            </if>
            <if test="checkDatetime != null">
                check_datetime = #{checkDatetime,jdbcType=NVARCHAR},
            </if>
            <if test="checkedDoctor != null">
                checked_doctor = #{checkedDoctor,jdbcType=NVARCHAR},
            </if>
            <if test="checkedDatetime != null">
                checked_datetime = #{checkedDatetime,jdbcType=NVARCHAR},
            </if>
            <if test="lockinfo != null">
                LockInfo = #{lockinfo,jdbcType=NVARCHAR},
            </if>
            <if test="doctorInCharge != null">
                DOCTOR_IN_CHARGE = #{doctorInCharge,jdbcType=NVARCHAR},
            </if>
            <if test="idNo != null">
                ID_NO = #{idNo,jdbcType=NVARCHAR},
            </if>
            <if test="dischargeDisposition != null">
                DISCHARGE_DISPOSITION = #{dischargeDisposition,jdbcType=NVARCHAR},
            </if>
            <if test="deptCodeLend != null">
                dept_code_lend = #{deptCodeLend,jdbcType=NVARCHAR},
            </if>
            <if test="isscaned != null and isscaned != ''">
                isscaned = #{isscaned,jdbcType=NVARCHAR},
            </if>
            <if test="isScanning != null and isScanning != ''">
                is_scanning = #{isScanning,jdbcType=NVARCHAR},
            </if>
            <if test="cmtDoctor != null">
                cmt_doctor = #{cmtDoctor,jdbcType=INTEGER},
            </if>
            <if test="cmtNurse != null">
                cmt_nurse = #{cmtNurse,jdbcType=INTEGER},
            </if>
            <if test="doctorName != null " >
                doctor_name = #{doctorName,jdbcType=NVARCHAR},
            </if>
            <if test="nurseName != null ">
                nurse_name = #{nurseName,jdbcType=NVARCHAR},
            </if>
            <if test="cmtDoctorDate != null and cmtDoctorDate != ''">
                cmt_doctor_date = #{cmtDoctorDate,jdbcType=NVARCHAR},
            </if>
            <if test="cmtDoctorDate ==''">
                cmt_doctor_date=null,
            </if>
            <if test="cmtNurseDate != null  and cmtNurseDate != ''">
                cmt_nurse_date = #{cmtNurseDate,jdbcType=NVARCHAR},
            </if>
            <if test="cmtNurseDate ==''">
                cmt_nurse_date=null,
            </if>
            <if test="firstInstance != null and firstInstance != ''">
                first_instance=#{firstInstance,jdbcType=NVARCHAR},
            </if>
            <if test="score != null and score != ''">
                score=#{score,jdbcType=NVARCHAR},
            </if>
            <if test="isSign != null ">
                is_sign=#{isSign,jdbcType=INTEGER},
            </if>
            <if test="signer != null and signer != ''">
                signer=#{signer,jdbcType=NVARCHAR},
            </if>
            <if test="signTime != null and signTime != ''">
                sign_time=#{signTime,jdbcType=NVARCHAR}
            </if>
        </set>
        where id = #{id,jdbcType=NVARCHAR}
    </update>
    <update id="updateById" parameterType="com.emr.entity.Archive_Master">
    update archive_master
    set patient_id = #{patientId,jdbcType=NVARCHAR},
      inp_no = #{inpNo,jdbcType=NVARCHAR},
      visit_id = #{visitId,jdbcType=NVARCHAR},
      name = #{name,jdbcType=NVARCHAR},
      sex = #{sex,jdbcType=NVARCHAR},
      dept_name = #{deptName,jdbcType=NVARCHAR},
      discharge_date_time = #{dischargeDateTime,jdbcType=NVARCHAR},
      ArchiveState = #{archivestate,jdbcType=NVARCHAR},
      admission_date_time = #{admissionDateTime,jdbcType=NVARCHAR},
      dept_admission_to = #{deptAdmissionTo,jdbcType=NVARCHAR},
      check_doctor = #{checkDoctor,jdbcType=NVARCHAR},
      check_datetime = #{checkDatetime,jdbcType=NVARCHAR},
      checked_doctor = #{checkedDoctor,jdbcType=NVARCHAR},
      checked_datetime = #{checkedDatetime,jdbcType=NVARCHAR},
      LockInfo = #{lockinfo,jdbcType=NVARCHAR},
      DOCTOR_IN_CHARGE = #{doctorInCharge,jdbcType=NVARCHAR},
      ID_NO = #{idNo,jdbcType=NVARCHAR},
      DISCHARGE_DISPOSITION = #{dischargeDisposition,jdbcType=NVARCHAR},
      dept_code_lend = #{deptCodeLend,jdbcType=NVARCHAR},
      isscaned = #{isscaned,jdbcType=NVARCHAR},
      is_scanning = #{isScanning,jdbcType=NVARCHAR},
      first_instance=#{firstInstance,jdbcType=NVARCHAR}
      bed_number=#{bedNumber,jdbcType=NVARCHAR}
    where id = #{id,jdbcType=NVARCHAR}
  </update>
    <select id="selectHandleDetail" parameterType="com.emr.entity.Archive_Master_Vo" resultMap="BaseResultMap2">
        SELECT m.id
        ,m.patient_id
        ,m.inp_no
        ,m.visit_id
        ,m.name
        ,m.sex
        ,g.name dept_name
        ,CONVERT(varchar(10),m.discharge_date_time, 120)discharge_date_time
        ,m.ArchiveState
        ,CONVERT(varchar(10),m.admission_date_time, 120)admission_date_time
        ,d.name dept_admission_to
        ,m.check_doctor
        ,m.check_datetime
        ,m.checked_doctor
        ,m.checked_datetime
        ,m.LockInfo
        ,m.DOCTOR_IN_CHARGE
        ,m.ID_NO
        ,m.DISCHARGE_DISPOSITION
        ,m.dept_code_lend
        ,m.cmt_doctor
        ,m.cmt_nurse
        ,m.doctor_name
        ,m.nurse_name
        ,m.cmt_doctor_date
        ,m.cmt_nurse_date
        ,m.emr_doctor_cmt
        ,m.emr_nure_cmt
        ,m.death_flag
        ,m.first_instance
        ,m.transfer_dept
        ,m.IsScaned
        ,m.bed_number
        ,m.is_scanning
        ,f.id as fid
        ,f.following_type
        ,f.following_content
        ,CONVERT(varchar(10),f.handle_time, 120)handle_time
        ,f.handle_id
        ,f.handle_name
        ,f.remark
        FROM archive_master m
        LEFT JOIN Archive_Master_Following f
        on m.id=f.master_id
        LEFT JOIN (select code,name from emr_dictionary where parent_id=(select id from emr_dictionary where
        typeCode='dept_code' )) d
        on m.dept_admission_to=d.code
        LEFT JOIN (select code,name from emr_dictionary where parent_id=(select id from emr_dictionary where
        typeCode='dept_code' )) g
        on m.dept_name=g.code
        where 1=1 and f.following_type in(8,9)
        <if test="id != null and id != ''">
            and m.id = #{id,jdbcType=NVARCHAR}
        </if>
        <if test="inpNo != null and inpNo != ''">
            and m.inp_no like '%'+#{inpNo,jdbcType=NCHAR}+'%'
        </if>
        <if test="visitId != null and visitId != ''">
            and m.visit_id=#{visitId,jdbcType=NCHAR}
        </if>
        <if test="name != null and name != ''">
            and m.name like '%'+#{name,jdbcType=NCHAR}+'%'
        </if>
        <if test="deptName!= null and deptName!= ''">
            and m.dept_name in
            <foreach item="item" collection="deptName.split(',')" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="handleTime != null">
            and f.handle_time = #{handleTime,jdbcType=NVARCHAR}
        </if>
        <choose>
            <when test="startHandleTime != null and startHandleTime != '' and endHandleTime != null and endHandleTime != ''">
                and f.handle_time between CONVERT(VARCHAR(10),#{startHandleTime,jdbcType=NCHAR},120) and
                #{endHandleTime,jdbcType=NCHAR}+ ' 23:59:59'
            </when>
            <when test="startHandleTime != null and startHandleTime != ''">
                and f.handle_time >= CONVERT(VARCHAR(10),#{startHandleTime,jdbcType=NCHAR},120)
            </when>
            <when test="endHandleTime != null and endHandleTime != ''">
                and f.handle_time &lt;= #{endHandleTime,jdbcType=NCHAR}+ ' 23:59:59'
            </when>
        </choose>
        <if test="handleId != null and handleId != ''">
            and f.handle_id in
            <foreach item="item" collection="handleId.split(',')" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="followingType != null and followingType != ''">
            and f.following_type = #{followingType,jdbcType=NVARCHAR}
        </if>
        ORDER BY f.handle_time desc
    </select>
    <!--初次提交归档-->
    <select id="selectOverdue" parameterType="com.emr.entity.Archive_Master_Vo" resultMap="BaseResultMap2">
        select f.id,f.handle_name,f.handle_id,f.handle_time,ISNULL(DATEDIFF(dd,CONVERT(VARCHAR(20),m.discharge_date_time,23),CONVERT(VARCHAR(20),f.handle_time,23)), 0) diffDay,
        <if test="overdueDays != null and overdueDays != ''">
            case
            when ISNULL(datediff(day,convert(varchar(100),m.mid_date,23),convert(varchar(100),f.handle_time,23)),0)&lt;=0
            then '0'
            when ISNULL( datediff(day,convert(varchar(100),m.mid_date,23),convert(varchar(100),f.handle_time,23)),0)+ISNULL(m.dayOne,0)-1-cast(#{overdueDays,jdbcType=NCHAR} as int)&lt;0
            then '0'
            when ISNULL(m.dayOne,0)>=0 and ISNULL( datediff(day,convert(varchar(100),m.mid_date,23),convert(varchar(100),f.handle_time,23)),0)>=0
            then ISNULL( datediff(day,convert(varchar(100),m.mid_date,23),convert(varchar(100),f.handle_time,23)),0)+ISNULL(m.dayOne,0)-1-cast(#{overdueDays,jdbcType=NCHAR} as int)
            else '0'
            end overdue
        </if>
        <if test="overdueDays == null or overdueDays == ''">
            case
            when ISNULL( datediff(day,convert(varchar(100),m.mid_date,23),convert(varchar(100),f.handle_time,23)),0)+ISNULL(m.dayOne,0)-1-3&lt;0
            then '0'
            when ISNULL(m.dayOne,0)>=0 and ISNULL( datediff(day,convert(varchar(100),m.mid_date,23),convert(varchar(100),f.handle_time,23)),0)>=0
            then ISNULL( datediff(day,convert(varchar(100),m.mid_date,23),convert(varchar(100),f.handle_time,23)),0)+ISNULL(m.dayOne,0)-1-3
            else '0'
            end overdue
        </if>
        ,m.master_id,m.patient_id,m.inp_no,m.visit_id,m.name,ISNULL(c.name,m.dept_name) dept_name,m.discharge_date_time,m.DOCTOR_IN_CHARGE,m.first_instance,m.death_flag,ISNULL(e.name,m.dept_admission_to) dept_admission_to,admission_date_time
        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
        LEFT 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,
            <if test="overdueDays != null and overdueDays != ''">
                convert(varchar(10),(DATEADD(DAY,cast(#{overdueDays,jdbcType=NCHAR} as int)-1,convert(varchar(10),discharge_date_time,23))),23) mid_date
                ,ISNULL(dbo.getWorkday(convert(varchar(10),discharge_date_time,23),convert(varchar(10),(DATEADD(DAY,cast(#{overdueDays,jdbcType=NCHAR} as int),convert(varchar(10),discharge_date_time,23))),23)) , 0) dayOne
            </if>
            <if test="overdueDays == null or overdueDays == ''">
                convert(varchar(10),(DATEADD(DAY,2,convert(varchar(10),discharge_date_time,23))),23) mid_date
                ,ISNULL(dbo.getWorkday(convert(varchar(10),discharge_date_time,23),convert(varchar(10),(DATEADD(DAY,3,convert(varchar(10),discharge_date_time,23))),23)) , 0) dayOne
            </if>
            from archive_master  where 1=1 and ArchiveState not in('作废','已封存','取消入院') and Is_Valid!=1
        ) m
        on f.master_id=m.master_id
        LEFT JOIN (select code,name from emr_dictionary where parent_id=(select id from emr_dictionary where typeCode='dept_code' )) c
        on m.dept_name=c.code

        LEFT JOIN (select code,name from emr_dictionary where parent_id=(select id from emr_dictionary where typeCode='dept_code' )) e
        on m.dept_admission_to=e.code
        <where> 1=1 and m.master_id!='' and m.master_id is not null
            <if test="(overdueDaysTwo != null and overdueDaysTwo != '') and (overdueDays != null and overdueDays != '')">
                and ISNULL( datediff(day,convert(varchar(100),m.mid_date,23),convert(varchar(100),f.handle_time,23)),0)+ISNULL(m.dayOne,0)-1-cast(#{overdueDays,jdbcType=NCHAR} as int)>=#{overdueDaysTwo,jdbcType=NCHAR}
            </if>
            <!--<if test="(overdueDaysTwo == null or overdueDaysTwo == '') and (overdueDays != null and overdueDays != '')">
                and ABS(ISNULL( datediff(day,convert(varchar(100),m.mid_date,23),convert(varchar(100),f.handle_time,23)),0)+ISNULL(m.dayOne,0)-1-cast(#{overdueDays,jdbcType=NCHAR} as int))>=0
            </if>-->
            <if test="(overdueDaysTwo != null and overdueDaysTwo != '') and (overdueDays == null or overdueDays == '')">
                and ISNULL( datediff(day,convert(varchar(100),m.mid_date,23),convert(varchar(100),f.handle_time,23)),0)+ISNULL(m.dayOne,0)-1-3>=#{overdueDaysTwo,jdbcType=NCHAR}
            </if>
            <!--<if test="(overdueDaysTwo == null or overdueDaysTwo == '') and (overdueDays == null or overdueDays == '')">
                 and ISNULL( datediff(day,convert(varchar(100),m.mid_date,23),convert(varchar(100),f.handle_time,23)),0)+ISNULL(m.dayOne,0)-1-3>=0
            </if>-->
            <if test="id != null and id != ''">
                and m.id = #{id,jdbcType=NVARCHAR}
            </if>
            <if test="inpNo != null and inpNo != ''">
                and m.inp_no like '%'+#{inpNo,jdbcType=NCHAR}+'%'
            </if>
            <if test="visitId != null and visitId != ''">
                and m.visit_id=#{visitId,jdbcType=NCHAR}
            </if>
            <if test="name != null and name != ''">
                and m.name like '%'+#{name,jdbcType=NCHAR}+'%'
            </if>
            <if test="deptName!= null and deptName!= ''">
                and m.dept_name in
                <foreach item="item" collection="deptName.split(',')" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="deptAdmissionTo != null and deptAdmissionTo!=''">
                and m.dept_admission_to in
                <foreach item="item" collection="deptAdmissionTo.split(',')" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <choose>
                <when test="startDate != null and startDate != '' and endDate != null and endDate != ''">
                    and CONVERT(VARCHAR(20),m.discharge_date_time,23) between #{startDate,jdbcType=NCHAR} and  #{endDate,jdbcType=NCHAR}
                </when>
                <when test="startDate != null and startDate != ''">
                    and CONVERT(VARCHAR(20),m.discharge_date_time,23) >=#{startDate,jdbcType=NCHAR}
                </when>
                <when test="endDate != null and endDate != ''">
                    and CONVERT(VARCHAR(20),m.discharge_date_time,23) &lt;= #{endDate,jdbcType=NCHAR}
                </when>
            </choose>
            <choose>
                <when test="createTimeStart != null and createTimeStart != '' and createTimeEnd != null and createTimeEnd != ''">
                    and CONVERT(VARCHAR(20),f.handle_time,23) between #{createTimeStart,jdbcType=NCHAR} and  #{createTimeEnd,jdbcType=NCHAR}
                </when>
                <when test="createTimeStart != null and createTimeStart != ''">
                    and CONVERT(VARCHAR(20),f.handle_time,23) >= #{createTimeStart,jdbcType=NCHAR}
                </when>
                <when test="createTimeEnd != null and createTimeEnd != ''">
                    and CONVERT(VARCHAR(20),f.handle_time,23) &lt;= #{createTimeEnd,jdbcType=NCHAR}
                </when>
            </choose>
        </where>
        ORDER BY m.master_id,m.discharge_date_time,f.handle_time asc
    </select>
    <!-- 住院返修病历 -->
    <select id="selectOverdue2" parameterType="com.emr.entity.Archive_Master_Vo" resultMap="BaseResultMap2">
        select f1.id,f1.following_content,f1.handle_name,CONVERT(VARCHAR(20),f1.handle_time,120) handle_time,ISNULL(DATEDIFF(dd,CONVERT(VARCHAR(20),f1.handle_time,23),CONVERT(VARCHAR(20),f2.handle_time,23)), 0) diffDay,
        <if test="overdueDays != null and overdueDays != ''">
            case
            when ISNULL( datediff(day,convert(varchar(100),f2.mid_date,23),convert(varchar(100),f1.handle_time,23)),0)&lt;=0
            then '0'
            when ISNULL( datediff(day,convert(varchar(100),f2.mid_date,23),convert(varchar(100),f1.handle_time,23)),0)+ISNULL(f2.dayOne,0)-1-cast(#{overdueDays,jdbcType=NCHAR} as int)&lt;0
            then '0'
            when ISNULL(f2.dayOne,0)>=0 and ISNULL( datediff(day,convert(varchar(100),f2.mid_date,23),convert(varchar(100),f1.handle_time,23)),0)>=0
            then ISNULL( datediff(day,convert(varchar(100),f2.mid_date,23),convert(varchar(100),f1.handle_time,23)),0)+ISNULL(f2.dayOne,0)-1-cast(#{overdueDays,jdbcType=NCHAR} as int)
            else '0'
            end overdue
        </if>
        <if test="overdueDays == null or overdueDays == ''">
            case
            when ISNULL( datediff(day,convert(varchar(100),f2.mid_date,23),convert(varchar(100),f1.handle_time,23)),0)&lt;=0
            then '0'
            when ISNULL( datediff(day,convert(varchar(100),f2.mid_date,23),convert(varchar(100),f1.handle_time,23)),0)+ISNULL(f2.dayOne,0)-1-3&lt;0
            then '0'
            when ISNULL(f2.dayOne,0)>=0 and ISNULL( datediff(day,convert(varchar(100),f2.mid_date,23),convert(varchar(100),f1.handle_time,23)),0)>=0
            then ISNULL( datediff(day,convert(varchar(100),f2.mid_date,23),convert(varchar(100),f1.handle_time,23)),0)+ISNULL(f2.dayOne,0)-1-3
            else '0'
            end overdue
        </if>
        ,m.master_id,m.patient_id,m.inp_no,m.visit_id,m.name,ISNULL(c.name,m.dept_name) dept_name,m.discharge_date_time,m.DOCTOR_IN_CHARGE,m.first_instance,m.death_flag,ISNULL(e.name,m.dept_admission_to) dept_admission_to,admission_date_time
        from(
        select id,master_id,following_type,following_content,handle_name,handle_time,ROW_NUMBER() OVER (partition by master_id ORDER BY master_id,handle_time) as rowNumb from Archive_Master_Following where following_type='9'
        )f1
        LEFT JOIN (
            select  *,
            <if test="overdueDays != null and overdueDays != ''">
                convert(varchar(10),(DATEADD(DAY,cast(#{overdueDays,jdbcType=NCHAR} as int)-1,convert(varchar(10),handle_time,23))),23) mid_date
                ,ISNULL(dbo.getWorkday(convert(varchar(10),handle_time,23),convert(varchar(10),(DATEADD(DAY,cast(#{overdueDays,jdbcType=NCHAR} as int),convert(varchar(10),handle_time,23))),23)) , 0) dayOne
            </if>
            <if test="overdueDays == null or overdueDays == ''">
                convert(varchar(10),(DATEADD(DAY,cast('2' as int),convert(varchar(10),handle_time,23))),23) mid_date
                ,ISNULL(dbo.getWorkday(convert(varchar(10),handle_time,23),convert(varchar(10),(DATEADD(DAY,cast('3' as int),convert(varchar(10),handle_time,23))),23)) , 0) dayOne
            </if>
            from(
               select *,ROW_NUMBER() OVER (partition by master_id ORDER BY master_id,handle_time) as rowNumb from Archive_Master_Following where following_type='5'
            )a
        )f2
        on f1.master_id=f2.master_id and f1.rowNumb=f2.rowNumb-1
        LEFT 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
        ) m
        on f1.master_id=m.master_id
        LEFT JOIN (select code,name from emr_dictionary where parent_id=(select id from emr_dictionary where typeCode='dept_code' )) c
        on m.dept_name=c.code
        LEFT JOIN (select code,name from emr_dictionary where parent_id=(select id from emr_dictionary where typeCode='dept_code' )) e
        on m.dept_admission_to=e.code
        <where> 1=1 and m.master_id!='' and m.master_id is not null and f2.handle_time is not null
            <if test="(overdueDaysTwo != null and overdueDaysTwo != '') and (overdueDays != null and overdueDays != '')">
                and ISNULL( datediff(day,convert(varchar(100),f2.mid_date,23),convert(varchar(100),f1.handle_time,23)),0)+ISNULL(f2.dayOne,0)-1-cast(#{overdueDays,jdbcType=NCHAR} as int)>=#{overdueDaysTwo,jdbcType=NCHAR}
            </if>
            <if test="(overdueDaysTwo != null and overdueDaysTwo != '') and (overdueDays == null or overdueDays == '')">
                and ISNULL( datediff(day,convert(varchar(100),f2.mid_date,23),convert(varchar(100),f1.handle_time,23)),0)+ISNULL(f2.dayOne,0)-1-3>=#{overdueDaysTwo,jdbcType=NCHAR}
            </if>
            <if test="id != null and id != ''">
                and m.id = #{id,jdbcType=NVARCHAR}
            </if>
            <if test="inpNo != null and inpNo != ''">
                and m.inp_no like '%'+#{inpNo,jdbcType=NCHAR}+'%'
            </if>
            <if test="visitId != null and visitId != ''">
                and m.visit_id=#{visitId,jdbcType=NCHAR}
            </if>
            <if test="name != null and name != ''">
                and m.name like '%'+#{name,jdbcType=NCHAR}+'%'
            </if>
            <if test="deptName!= null and deptName!= ''">
                and m.dept_name in
                <foreach item="item" collection="deptName.split(',')" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="deptAdmissionTo != null and deptAdmissionTo!=''">
                and m.dept_admission_to in
                <foreach item="item" collection="deptAdmissionTo.split(',')" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <choose>
                <when test="startDate != null and startDate != '' and endDate != null and endDate != ''">
                    and CONVERT(VARCHAR(20),m.discharge_date_time,23) between #{startDate,jdbcType=NCHAR} and  #{endDate,jdbcType=NCHAR}
                </when>
                <when test="startDate != null and startDate != ''">
                    and CONVERT(VARCHAR(20),m.discharge_date_time,23) >=#{startDate,jdbcType=NCHAR}
                </when>
                <when test="endDate != null and endDate != ''">
                    and CONVERT(VARCHAR(20),m.discharge_date_time,23) &lt;= #{endDate,jdbcType=NCHAR}
                </when>
            </choose>
            <choose>
                <when test="createTimeStart != null and createTimeStart != '' and createTimeEnd != null and createTimeEnd != ''">
                    and CONVERT(VARCHAR(20),f1.handle_time,23) between #{createTimeStart,jdbcType=NCHAR} and  #{createTimeEnd,jdbcType=NCHAR}
                </when>
                <when test="createTimeStart != null and createTimeStart != ''">
                    and CONVERT(VARCHAR(20),f1.handle_time,23) >= #{createTimeStart,jdbcType=NCHAR}
                </when>
                <when test="createTimeEnd != null and createTimeEnd != ''">
                    and CONVERT(VARCHAR(20),f1.handle_time,23) &lt;= #{createTimeEnd,jdbcType=NCHAR}
                </when>
            </choose>
        </where>
        ORDER BY m.master_id,m.discharge_date_time,f1.handle_time asc
    </select>
    <!--封存日志-->
    <select id="selectByFollowinglog" parameterType="com.emr.entity.Archive_Master_Vo" resultMap="BaseResultMap2">
        select m.INP_NO,m.VISIT_ID,m.name,m.sex,f.handle_name,CONVERT(VARCHAR(20),f.handle_time,120) handle_time, m.archiveState ,f.following_content
        from (select * from Archive_Master where ArchiveState!='作废' and Is_Valid!=1) m
        INNER JOIN Archive_Master_Following f on m.ID=f.master_id
        where 1=1 AND f.following_type in('11','12')
        <if test="inpNo != null and inpNo != ''">
            and m.inp_no like '%'+#{inpNo,jdbcType=NCHAR}+'%'
        </if>
        <if test="visitId != null and visitId != ''">
            and m.visit_id=#{visitId,jdbcType=NCHAR}
        </if>
        <if test="name != null and name != ''">
            and m.name like '%'+#{name,jdbcType=NCHAR}+'%'
        </if>
        <if test="archivestate!= null and archivestate!= ''">
            and m.ArchiveState in
            <foreach item="item" collection="archivestate.split(',')" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="handleName != null and handleName != ''">
            and f.handle_name=#{handleName,jdbcType=NVARCHAR}
        </if>
        <if test="followingContent != null and followingContent != ''">
            and f.following_content=#{followingContent,jdbcType=NVARCHAR}
        </if>
        <choose>
            <when test="startDate != null and startDate != '' and endDate != null and endDate != ''">
                and f.handle_time between CONVERT(VARCHAR(10),#{startDate,jdbcType=NCHAR},120) and
                #{endDate,jdbcType=NCHAR}+ ' 23:59:59'
            </when>
            <when test="startDate != null and startDate != ''">
                and f.handle_time >= CONVERT(VARCHAR(10),#{startDate,jdbcType=NCHAR},120)
            </when>
            <when test="endDate != null and endDate != ''">
                and f.handle_time &lt;= #{endDate,jdbcType=NCHAR}+ ' 23:59:59'
            </when>
        </choose>
        ORDER BY f.handle_time desc
    </select>
    <!--留观病历查询-->
    <select id="getObservationRecordList" resultType="com.emr.entity.Archive_Master">
        SELECT
        id,
        patient_id,
        inp_no,
        visit_id,
        NAME,
        CONVERT ( nvarchar ( 19 ), admission_date_time, 23 ) admission_date_time,
        CONVERT ( nvarchar ( 19 ), discharge_date_time, 23 ) discharge_date_time,
        ArchiveState,
        CASE

        WHEN death_flag = '1' THEN
        '是' ELSE '否'
        END death_flag
        FROM
        Archive_Master
        WHERE
        inp_no LIKE 'LG%'
        <if test="patientId != null and patientId != ''">
            AND patient_id LIKE '%' + #{patientId} + '%'
        </if>
        <if test="inpNo != null and inpNo != ''">
            AND inp_no LIKE '%' + #{inpNo} + '%'
        </if>
        <if test="visitId != null and visitId != ''">
            AND visit_id = #{visitId}
        </if>
        <if test="name != null and name != ''">
            AND name LIKE '%' + #{name} + '%'
        </if>
        <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>
        <choose>
            <when test="startDateTo != null and startDateTo != '' and endDateTo != null and endDateTo != ''">
                and admission_date_time between CONVERT(VARCHAR(10),#{startDateTo,jdbcType=NCHAR},120) and
                #{endDateTo,jdbcType=NCHAR}+ ' 23:59:59'
            </when>
            <when test="startDateTo != null and startDateTo != ''">
                and admission_date_time >= CONVERT(VARCHAR(10),#{startDateTo,jdbcType=NCHAR},120)
            </when>
            <when test="endDateTo != null and endDateTo != ''">
                and admission_date_time &lt;= #{endDateTo,jdbcType=NCHAR}+ ' 23:59:59'
            </when>
        </choose>
        <if test="archivestate != null and archivestate != ''">
            AND ArchiveState = #{archivestate}
        </if>
        <if test="deathFlag != null and deathFlag != ''">
            AND death_flag = #{deathFlag}
        </if>
    </select>
    <!--急诊留观病历报表-->
    <select id="getObservationReportList" resultType="com.emr.entity.Archive_Master_Vo" resultMap="BaseResultMap2">
        select * from(
            select DOCTOR_IN_CHARGE,
            SUM(CASE WHEN ArchiveState IN ('已归档')
            <choose>
                <when test="startDate != null and startDate != '' and endDate != null and endDate != ''">
                    and CONVERT(VARCHAR(20),discharge_date_time,23) between #{startDate,jdbcType=NCHAR} and  #{endDate,jdbcType=NCHAR}
                </when>
                <when test="startDate != null and startDate != ''">
                    and CONVERT(VARCHAR(20),discharge_date_time,23) >=#{startDate,jdbcType=NCHAR}
                </when>
                <when test="endDate != null and endDate != ''">
                    and CONVERT(VARCHAR(20),discharge_date_time,23) &lt;= #{endDate,jdbcType=NCHAR}
                </when>
            </choose>
            <if test="doctorInCharge!= null and doctorInCharge!= ''">
                and doctor_in_charge in
                <foreach item="item" collection="doctorInCharge.split(',')" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            and inp_no LIKE 'LG%' and ArchiveState is not null AND Is_Valid != 1 AND Is_Valid != 1 THEN 1 ELSE 0 END) as file_num,
            SUM(CASE WHEN ArchiveState not IN ('作废','已封存','取消入院')
            <if test="(overdueDays != null and overdueDays != '')">
                and ISNULL( datediff(day,convert(varchar(100),discharge_date_time,23),convert(varchar(100),admission_date_time,23)),0)-cast(#{overdueDays,jdbcType=NCHAR} as int)>=0
            </if>
            <if test="(overdueDays == null or overdueDays == '')">
                and ISNULL( datediff(day,convert(varchar(100),discharge_date_time,23),convert(varchar(100),admission_date_time,23)),0)-3>=0
            </if>
            <choose>
                <when test="startDate != null and startDate != '' and endDate != null and endDate != ''">
                    and CONVERT(VARCHAR(20),discharge_date_time,23) between #{startDate,jdbcType=NCHAR} and  #{endDate,jdbcType=NCHAR}
                </when>
                <when test="startDate != null and startDate != ''">
                    and CONVERT(VARCHAR(20),discharge_date_time,23) >=#{startDate,jdbcType=NCHAR}
                </when>
                <when test="endDate != null and endDate != ''">
                    and CONVERT(VARCHAR(20),discharge_date_time,23) &lt;= #{endDate,jdbcType=NCHAR}
                </when>
            </choose>
            <if test="doctorInCharge!= null and doctorInCharge!= ''">
                and doctor_in_charge in
                <foreach item="item" collection="doctorInCharge.split(',')" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            and inp_no LIKE 'LG%' AND Is_Valid != 1 THEN 1 ELSE 0 END) as delay_num,
            SUM(CASE WHEN ArchiveState not IN ('作废','已封存','取消入院')
            <choose>
                <when test="startDate != null and startDate != '' and endDate != null and endDate != ''">
                    and CONVERT(VARCHAR(20),discharge_date_time,23) between #{startDate,jdbcType=NCHAR} and  #{endDate,jdbcType=NCHAR}
                </when>
                <when test="startDate != null and startDate != ''">
                    and CONVERT(VARCHAR(20),discharge_date_time,23) >=#{startDate,jdbcType=NCHAR}
                </when>
                <when test="endDate != null and endDate != ''">
                    and CONVERT(VARCHAR(20),discharge_date_time,23) &lt;= #{endDate,jdbcType=NCHAR}
                </when>
            </choose>
            <if test="doctorInCharge!= null and doctorInCharge!= ''">
                and doctor_in_charge in
                <foreach item="item" collection="doctorInCharge.split(',')" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            and inp_no LIKE 'LG%' AND Is_Valid != 1 THEN 1 ELSE 0 END) as quality_num,
            SUM(CASE WHEN ArchiveState IN ('主任退回','复审退回')
            <choose>
                <when test="startDate != null and startDate != '' and endDate != null and endDate != ''">
                    and CONVERT(VARCHAR(20),discharge_date_time,23) between #{startDate,jdbcType=NCHAR} and  #{endDate,jdbcType=NCHAR}
                </when>
                <when test="startDate != null and startDate != ''">
                    and CONVERT(VARCHAR(20),discharge_date_time,23) >=#{startDate,jdbcType=NCHAR}
                </when>
                <when test="endDate != null and endDate != ''">
                    and CONVERT(VARCHAR(20),discharge_date_time,23) &lt;= #{endDate,jdbcType=NCHAR}
                </when>
            </choose>
            <if test="doctorInCharge!= null and doctorInCharge!= ''">
                and doctor_in_charge in
                <foreach item="item" collection="doctorInCharge.split(',')" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            and inp_no LIKE 'LG%' and ArchiveState is not null AND Is_Valid != 1   THEN 1 ELSE 0 END) as back_num
            FROM archive_master GROUP BY DOCTOR_IN_CHARGE
        ) a
        <where>
            1=1
            <if test="(overdueDaysTwo != null and overdueDaysTwo != '')">
               and delay_num>=#{overdueDaysTwo,jdbcType=NCHAR}
            </if>
        </where>
    </select>
    <!--封存查询-->
    <select id="selectStorageByCol" parameterType="com.emr.entity.Archive_Master_Vo" resultMap="BaseResultMap2">
        select m.id ,m.inp_no,m.visit_id,m.name,ISNULL(f.name, m.dept_name) dept_name,CONVERT(VARCHAR(20),m.discharge_date_time,120) discharge_date_time,m.ArchiveState,m.sex,m.ID_NO,
        ISNULL(c.name, m.dept_admission_to) dept_admission_to
        ,CONVERT(VARCHAR(20),m.admission_date_time,120) admission_date_time,m.DISCHARGE_DISPOSITION,d.handle_time,d.handle_name,d.following_type
        from(select * from Archive_Master where ArchiveState!='作废' and Is_Valid!=1 )  m
        LEFT JOIN(
            select master_id ,handle_time ,handle_name,following_type  from (
            select ROW_NUMBER() over(partition by master_id order by handle_time desc) RowNum
            ,Archive_Master_Following.*
            from Archive_Master_Following where 1=1 ) as t1  where 1=1 and RowNum = 1 and following_type='11'
        ) d
        on m.id=d.master_id
        LEFT JOIN (select code,name from emr_dictionary where parent_id=(select id from emr_dictionary where
        typeCode='dept_code' )) c
        on m.dept_admission_to=c.code
        LEFT JOIN (select code,name from emr_dictionary where parent_id=(select id from emr_dictionary where
        typeCode='dept_code' )) f
        on m.dept_name=f.code
        where 1=1
        <if test="id != null and id != ''">
            and m.id = #{id,jdbcType=NVARCHAR}
        </if>
        <if test="inpNo != null and inpNo != ''">
            and m.inp_no like '%'+#{inpNo,jdbcType=NCHAR}+'%'
        </if>
        <if test="visitId != null and visitId != ''">
            and m.visit_id=#{visitId,jdbcType=NCHAR}
        </if>
        <if test="name != null and name != ''">
            and m.name like '%'+#{name,jdbcType=NCHAR}+'%'
        </if>
        <if test="handleName != null and handleName != ''">
            and d.handle_name like '%'+#{handleName,jdbcType=NCHAR}+'%'
        </if>
        <if test="deptName!= null and deptName!= ''">
            and m.dept_name in
            <foreach item="item" collection="deptName.split(',')" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="deptAdmissionTo != null and deptAdmissionTo!=''">
            and m.dept_admission_to in
            <foreach item="item" collection="deptAdmissionTo.split(',')" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="archivestate!= null and archivestate!= ''">
            and m.ArchiveState in
            <foreach item="item" collection="archivestate.split(',')" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <choose>
            <when test="startDate != null and startDate != '' and endDate != null and endDate != ''">
                and m.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 m.discharge_date_time >= CONVERT(VARCHAR(10),#{startDate,jdbcType=NCHAR},120)
            </when>
            <when test="endDate != null and endDate != ''">
                and m.discharge_date_time &lt;= #{endDate,jdbcType=NCHAR}+ ' 23:59:59'
            </when>
        </choose>
        <choose>
            <when test="startDateTo != null and startDateTo != '' and endDateTo != null and endDateTo != ''">
                and d.handle_time between CONVERT(VARCHAR(10),#{startDateTo,jdbcType=NCHAR},120) and
                #{endDateTo,jdbcType=NCHAR}+ ' 23:59:59'
            </when>
            <when test="startDateTo != null and startDateTo != ''">
                and d.handle_time >= CONVERT(VARCHAR(10),#{startDateTo,jdbcType=NCHAR},120)
            </when>
            <when test="endDateTo != null and endDateTo != ''">
                and d.handle_time &lt;= #{endDateTo,jdbcType=NCHAR}+ ' 23:59:59'
            </when>
        </choose>
    </select>
    <select id="copyDetailList" parameterType="com.emr.entity.Archive_Master_Vo" resultMap="BaseResultMap2">
        select
        patient_id ,
        admiss_id inp_no,
        admiss_times visit_id,
        hzName name,
        sex sex,
        CONVERT(VARCHAR(20),admission_date_time,120) admission_date_time,
        CONVERT(VARCHAR(20),discharge_date_time,120) discharge_date_time,
        Name deptName,
        doctor_in_charge,
        assort_name content,
        cpyTime create_time
        from t_uuprint
        <where>
            1=1
            <if test="patientId != null and patientId != ''">
                AND patient_id LIKE '%' + #{patientId} + '%'
            </if>
            <choose>
                <when test="startDate != null and startDate != '' and endDate != null and endDate != ''">
                    and cpyTime between #{startDate,jdbcType=NCHAR} and  #{endDate,jdbcType=NCHAR}
                </when>
                <when test="startDate != null and startDate != ''">
                    and cpyTime >=#{startDate,jdbcType=NCHAR}
                </when>
                <when test="endDate != null and endDate != ''">
                    and cpyTime &lt;= #{endDate,jdbcType=NCHAR}
                </when>
            </choose>
            <if test="inpNo != null and inpNo != ''">
                and admiss_id like '%'+#{inpNo,jdbcType=NCHAR}+'%'
            </if>
            <if test="visitId != null and visitId != ''">
                and admiss_times=#{visitId,jdbcType=NCHAR}
            </if>
            <if test="name != null and name != ''">
                and hzName like '%'+#{name,jdbcType=NCHAR}+'%'
            </if>
            <if test="visitId != null and visitId != ''">
                AND visit_id = #{visitId}
            </if>
            <if test="deptName!= null and deptName!= ''">
                and Name in
                <foreach item="item" collection="deptName.split(',')" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="doctorInCharge!= null and doctorInCharge!= ''">
                and doctor_in_charge in
                <foreach item="item" collection="doctorInCharge.split(',')" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
        </where>
        order by hzName, cpyTime
    </select>
    <select id="copyCountList" parameterType="com.emr.entity.Archive_Master_Vo" resultMap="BaseResultMap2">
        select
        patient_id,
        admiss_id inp_no,
        admiss_times visit_id,
        name,
        convert(date, print_createtime, 8) create_time,
        (select COUNT(*) from [dbo].[archive_printInfo]
        where admiss_id=a.admiss_id and admiss_times=a.admiss_times and PageCount>0) numb
        from archive_printInfo a
        <where>
            1=1
            <if test="patientId != null and patientId != ''">
                AND patient_id LIKE '%' + #{patientId} + '%'
            </if>
            <choose>
                <when test="startDate != null and startDate != '' and endDate != null and endDate != ''">
                    and convert(date, print_createtime, 8) between #{startDate,jdbcType=NCHAR} and  #{endDate,jdbcType=NCHAR}
                </when>
                <when test="startDate != null and startDate != ''">
                    and convert(date, print_createtime, 8) >=#{startDate,jdbcType=NCHAR}
                </when>
                <when test="endDate != null and endDate != ''">
                    and convert(date, print_createtime, 8) &lt;= #{endDate,jdbcType=NCHAR}
                </when>
            </choose>
            <if test="inpNo != null and inpNo != ''">
                and admiss_id like '%'+#{inpNo,jdbcType=NCHAR}+'%'
            </if>
            <if test="visitId != null and visitId != ''">
                and admiss_times=#{visitId,jdbcType=NCHAR}
            </if>
            <if test="name != null and name != ''">
                and name like '%'+#{name,jdbcType=NCHAR}+'%'
            </if>
            <if test="visitId != null and visitId != ''">
                AND visit_id = #{visitId}
            </if>
        </where>
        group by patient_id,admiss_id, admiss_times, name , convert(date, print_createtime, 8)

    </select>
</mapper>