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

  <!-- 通用查询映射结果 -->
  <resultMap id="BaseResultMap" type="com.emr.entity.Sign_List">
    <id column="ID" property="id"/>
    <result column="pid" property="pid"/>
    <result column="createdtime" property="createdtime"/>
    <result column="otherInfo" property="otherInfo"/>
    <result column="masterID" property="masterID"/>
    <result column="detailID" property="detailID"/>
    <result column="dTime" property="dTime"/>
    <result column="sysFlag" property="sysFlag"/>
    <result column="C1"  property="c1"/>
    <result column="C2" property="c2"/>
    <result column="C3"  property="c3"/>
    <result column="C4" property="c4"/>
    <result column="C5" property="c5"/>
    <result column="N1" property="n1"/>
    <result column="N2" property="n2"/>
    <result column="N3" property="n3"/>
    <result column="T1" property="t1"/>
    <result column="T2" property="t2"/>
    <result column="T3" property="t3"/>
    <result column="IsFullText" property="isFullText"/>
  </resultMap>

  <resultMap id="BaseResultMapNew" type="com.emr.entity.Sign_List_Vo">
    <id column="ID" property="id"/>
    <result column="pid" property="pid"/>
    <result column="createdtime" property="createdtime"/>
    <result column="otherInfo" property="otherInfo"/>
    <result column="masterID" property="masterID"/>
    <result column="detailID" property="detailID"/>
    <result column="dTime" property="dTime"/>
    <result column="sysFlag" property="sysFlag"/>
    <result column="C1"  property="c1"/>
    <result column="C2" property="c2"/>
    <result column="C3"  property="c3"/>
    <result column="C4" property="c4"/>
    <result column="C5" property="c5"/>
    <result column="N1" property="n1"/>
    <result column="N2" property="n2"/>
    <result column="N3" property="n3"/>
    <result column="T1" property="t1"/>
    <result column="T2" property="t2"/>
    <result column="T3" property="t3"/>
    <result column="IsFullText" property="isFullText"/>
    <result column="inp_no"  property="inpNo" />
    <result column="visit_id"  property="visitId" />
    <result column="name" property="name" />
    <result column="dept_name"  property="deptName" />
    <result column="discharge_date_time"  property="dischargeDateTime" />
    <result column="dept_admission_to"  property="deptAdmissionTo" />
    <result column="admission_date_time"  property="admissionDateTime" />
    <result column="isscaned" jdbcType="NVARCHAR" property="isscaned" />
    <result column="num"  property="num" />
  </resultMap>

  <sql id="Base_Column_List">
    select ID, pid, createdtime, otherInfo, masterID, detailID, dTime,sysFlag, C1, C2, C3, C4, C5, N1, N2, N3, T1,
    T2,T3,IsFullText from archive_other
  </sql>

  <select id="selectSignList" parameterType="Sign_List_Vo" resultMap="BaseResultMapNew">
    select DISTINCT s.id,f.name dept_name,s.discharge_date_time, s.pid,s.createdtime, s.otherInfo, s.masterID, s.detailID, s.dTime,s.sysFlag, s.C1, s.C2, s.C3, s.C4, s.C5, s.N1, s.N2, s.N3, s.T1,
    s.T2,s.T3,s.IsFullText,s.inp_no,s.visit_id,s.name,s.admissionDateTime,d.name as deptAdmissionTo,s.isscaned,s.num
    from (
          select
          DISTINCT m.id,CONVERT(varchar(10),m.discharge_date_time, 120) discharge_date_time, s.pid,s.createdtime, s.otherInfo, s.masterID, s.detailID, s.dTime,s.sysFlag, s.C1, s.C2, s.C3, s.C4, s.C5, s.N1, s.N2, s.N3, s.T1,
          s.T2,s.T3,s.IsFullText,m.inp_no,m.visit_id,m.name,CONVERT(varchar(10),m.admission_date_time, 120) admissionDateTime,m.dept_name, m.isscaned,m.dept_admission_to,(select COUNT(1) from archive_other where archive_other.masterID=s.masterID and archive_other.sysFlag = 1)as num
          from archive_other s
          left join archive_master m
          on s.masterID=m.id
          where  s.id in(
          (select top 1 id from archive_other a where a.pid=s.pid and a.masterID=s.masterID ORDER BY createdtime desc)
          )
          <if test="id != null  and id != ''"> and s.ID = #{id}</if>
          <if test="pid != null and pid != ''"> and s.pid = #{pid}</if>
          <choose>
            <when test="startSignDate != null and startSignDate != '' and endSignDate != null and endSignDate != ''">
              and s.createdtime between CONVERT(VARCHAR(10),#{startSignDate,jdbcType=NCHAR},120) and
              #{endSignDate,jdbcType=NCHAR}+ ' 23:59:59'
            </when>
            <when test="startSignDate != null and startSignDate != ''">
              and s.createdtime >= CONVERT(VARCHAR(10),#{startDate,jdbcType=NCHAR},120)
            </when>
            <when test="endSignDate != null and endSignDate != ''">
              and s.createdtime &lt;= #{endSignDate,jdbcType=NCHAR}+ ' 23:59:59'
            </when>
          </choose>
          <if test="otherInfo != null  and otherInfo != ''"> and s.otherInfo = #{otherInfo}</if>
          <if test="masterID != null  and masterID != ''"> and s.masterID = #{masterID}</if>
          <if test="detailID != null and detailID != ''"> and s.detailID = #{detailID}</if>
          <if test="dTime != null  and dTime != ''"> and s.dTime = #{dTime}</if>
          <if test="c1 != null  and c1 != ''">
              and s.C1 in
              <foreach item="item" collection="c1.split(',')" open="(" separator="," close=")">
                  #{item}
              </foreach>
          </if>
          <if test="c2 != null and c2 != ''"> and s.C2 = #{c2}</if>
          <if test="c3 != null  and c3 != ''"> and s.C3 = #{c3}</if>
          <if test="c4 != null  and c4 != ''"> and s.C4 = #{c4}</if>
          <if test="c5 != null and c5 != ''"> and s.C5 = #{c5}</if>
          <if test="n1 != null and n1 != '' "> and s.N1 = #{n1}</if>
          <if test="n2 != null and n2 != ''"> and s.N2 = #{n2}</if>
          <if test="n3 != null and n3 != ''"> and s.N3 = #{n3} </if>
          <if test="t1 != null and t1 != ''"> and s.T1 = #{t1}</if>
          <if test="t2 != null  and t2 != ''"> and s.T2 = #{t2}</if>
          <if test="t3 != null  and t3 != ''"> and s.T3 = #{t3}</if>
          <if test="isFullText != null  and isFullText != ''"> and s.IsFullText = #{isFullText}</if>
          <if test="inpNo != null and inpNo != ''"> and m.inp_no = #{inpNo}</if>
          <if test="visitId != null  and visitId != ''"> and m.visit_id = #{visitId}</if>
          <if test="name != null  and name != ''"> and m.name like '%'+#{name}+'%'</if>
          and s.sysFlag =1
    )s
    LEFT JOIN emr_dictionary d
    on s.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 s.dept_name=f.code
    ORDER BY s.dTime desc
  </select>

  <select id="selectSignListWith" parameterType="Sign_List" resultMap="BaseResultMap">
    <include refid="Base_Column_List"/>
    <where>
    <if test="id != null  and id != ''"> and ID = #{id}</if>
    <if test="pid != null and pid != ''"> and pid = #{pid}</if>
    <if test="otherInfo != null  and otherInfo != ''"> and otherInfo = #{otherInfo}</if>
    <if test="masterID != null  and masterID != ''"> and masterID = #{masterID}</if>
    <if test="detailID != null and detailID != ''"> and detailID = #{detailID}</if>
    <if test="dTime != null  and dTime != ''"> and dTime = #{dTime}</if>
    <if test="sysFlag != null  and sysFlag != ''"> and sysFlag = #{sysFlag}</if>
    <if test="c1 != null  and c1 != ''"> and C1 = #{c1}</if>
    <if test="c2 != null and c2 != ''"> and C2 = #{c2}</if>
    <if test="c3 != null  and c3 != ''"> and C3 = #{c3}</if>
    <if test="c4 != null  and c4 != ''"> and C4 = #{c4}</if>
    <if test="c5 != null and c5 != ''"> and C5 = #{c5}</if>
    <if test="n1 != null and n1 != '' "> and N1 = #{n1}</if>
    <if test="n2 != null and n2 != ''"> and N2 = #{n2}</if>
    <if test="n3 != null and n3 != ''"> and N3 = #{n3} </if>
    <if test="t1 != null and t1 != ''"> and T1 = #{t1}</if>
    <if test="t2 != null  and t2 != ''"> and T2 = #{t2}</if>
    <if test="t3 != null  and t3 != ''"> and T3 = #{t3}</if>
    <if test="isFullText != null  and isFullText != ''"> and IsFullText = #{isFullText}</if>
     <if test="createdtime != null  and createdtime != ''"> and createdtime = #{createdtime}</if>
    </where>
    ORDER BY dTime desc
  </select>

  <select id="selectSignListById" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    <include refid="Base_Column_List"/>
    where ID = #{id}
  </select>
  <select id="selSignByColm" resultMap="BaseResultMap" parameterType="com.emr.entity.Sign_List_Vo">
    select id,pid,CONVERT (VARCHAR (20),createdtime,120) createdtime,otherInfo,masterID,detailID,CONVERT (VARCHAR (20),dTime,120)  dTime,sysFlag,C1,C2,C3,C4,C5,N1,N2,N3,T1,T2,T3
    from archive_other
    <where>
      1=1 and sysFlag = '1'
      <if test="masterID != null and masterID != ''">
        and masterID=#{masterID,jdbcType=VARCHAR}
      </if>
      <if test="c1 != null and c1 != ''">
        and C1 like '%'+#{c1,jdbcType=VARCHAR}+'%'
      </if>
      <choose>
        <when test="startSignDate != null and startSignDate != '' and endSignDate != null and endSignDate != ''">
          and CONVERT(VARCHAR(10),createdtime,23) between #{startSignDate,jdbcType=NCHAR} and  #{endSignDate,jdbcType=NCHAR}
        </when>
        <when test="startSignDate != null and startSignDate != ''">
          and CONVERT(VARCHAR(10)createdtime,23) >=#{startSignDate,jdbcType=NCHAR}
        </when>
        <when test="endSignDate != null and endSignDate != ''">
          and CONVERT(VARCHAR(10),createdtime,23) &lt;= #{endSignDate,jdbcType=NCHAR}
        </when>
      </choose>
      order by createdtime desc
    </where>
  </select>

  <insert id="insertSignList" parameterType="Sign_List" useGeneratedKeys="true" keyProperty="id">
    insert into archive_other
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="id != null  and id != ''">ID,</if>
      <if test="pid != null  and pid != ''">pid,</if>
      <if test="createdtime != null  and createdtime != ''">createdtime,</if>
      <if test="otherInfo != null  and otherInfo != ''">otherInfo,</if>
      <if test="masterID != null  and masterID != ''">masterID,</if>
      <if test="detailID != null  and detailID != ''">detailID,</if>
      <if test="dTime != null  and dTime != ''">dTime,</if>
      <if test="sysFlag != null  and sysFlag != ''">sysFlag,</if>
      <if test="c1 != null and c1 != '' ">C1,</if>
      <if test="c2 != null  and c2 != ''">C2,</if>
      <if test="c3 != null  and c3 != ''">C3,</if>
      <if test="c4 != null and c4 != ''">C4,</if>
      <if test="c5 != null and c5 != ''">C5,</if>
      <if test="n1 != null  and n1 != ''">N1,</if>
      <if test="n2 != null  and n2 != ''">N2,</if>
      <if test="n3 != null  and n3 != ''">N3,</if>
      <if test="t1 != null  and t1 != ''">T1,</if>
      <if test="t2 != null  and t2 != ''">T2,</if>
      <if test="t3 != null  and t3 != ''">T3,</if>
      <if test="isFullText != null  and isFullText != ''">IsFullText,</if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="id != null  and id != ''">#{id},</if>
      <if test="pid != null  and pid != ''">#{pid},</if>
      <if test="createdtime != null  and createdtime != ''">#{createdtime},</if>
      <if test="otherInfo != null  and otherInfo != ''">#{otherInfo},</if>
      <if test="masterID != null  and masterID != ''">#{masterID},</if>
      <if test="detailID != null  and detailID != ''">#{detailID},</if>
      <if test="dTime != null  and dTime != ''">#{dTime},</if>
      <if test="sysFlag != null  and sysFlag != ''">#{sysFlag},</if>
      <if test="c1 != null and c1 != '' ">#{c1},</if>
      <if test="c2 != null  and c2 != ''">#{c2},</if>
      <if test="c3 != null  and c3 != ''">#{c3},</if>
      <if test="c4 != null and c4 != ''">#{c4},</if>
      <if test="c5 != null and c5 != ''">#{c5},</if>
      <if test="n1 != null  and n1 != ''">#{n1},</if>
      <if test="n2 != null  and n2 != ''">#{n2},</if>
      <if test="n3 != null  and n3 != ''">#{n3},</if>
      <if test="t1 != null  and t1 != ''">#{t1},</if>
      <if test="t2 != null  and t2 != ''"> #{t2},</if>
      <if test="t3 != null  and t3 != ''">#{t3},</if>
      <if test="isFullText != null and isFullText != ''">#{isFullText},</if>
      <if test="isSign != null  and isSign != ''"> #{isSign},</if>
    </trim>
  </insert>

  <update id="updateSignList" parameterType="Sign_List">
    update archive_other
    <trim prefix="SET" suffixOverrides=",">
      <if test="pid != null  and pid != ''">pid = #{pid},</if>
      <if test="createdtime != null  and createdtime != ''">createdtime = #{createdtime},</if>
      <if test="otherInfo != null  and otherInfo != ''">otherInfo = #{otherInfo},</if>
      <if test="masterID != null  and masterID != ''">masterID = #{masterID},</if>
      <if test="detailID != null  and detailID != ''">detailID = #{detailID},</if>
      <if test="dTime != null  and dTime != ''">dTime = #{dTime},</if>
      <if test="sysFlag != null  and sysFlag != ''">sysFlag = #{sysFlag},</if>
      <if test="c1 != null and c1 != '' ">C1 = #{c1},</if>
      <if test="c2 != null  and c2 != ''">C2 = #{c2},</if>
      <if test="c3 != null  and c3 != ''">C3 = #{c3},</if>
      <if test="c4 != null and c4 != ''">C4 = #{c4},</if>
      <if test="c5 != null and c5 != ''">C5 = #{c5},</if>
      <if test="n1 != null  and n1 != ''">N1 = #{n1},</if>
      <if test="n2 != null  and n2 != ''">N2 = #{n2},</if>
      <if test="n3 != null  and n3 != ''">N3 = #{n3},</if>
      <if test="t1 != null and t1 != ''">T1 = #{t1},</if>
      <if test="t2 != null and t2 != ''">T2 = #{t2},</if>
      <if test="t3 != null  and t3 != ''">T3 = #{t3},</if>
      <if test="isFullText != null  and isFullText != ''">IsFullText = #{isFullText},</if>
    </trim>
    where ID = #{id}
  </update>

  <delete id="deleteSignListById" parameterType="java.lang.Integer">
        delete from archive_other where ID = #{id}
    </delete>

  <delete id="deleteSignListByIds" parameterType="java.lang.String">
    delete from archive_other where ID in
    <foreach item="id" collection="array" open="(" separator="," close=")">
      #{id}
    </foreach>
  </delete>


</mapper>
