<?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.steerinfo.ems.formula.mapper.FormulaMapper">
  <resultMap id="BaseResultMap" type="com.steerinfo.ems.formula.model.Formula">
    <id column="ID" jdbcType="VARCHAR" property="id" />
    <result column="CLOCK" jdbcType="VARCHAR" property="clock" />
    <result column="CODE" jdbcType="VARCHAR" property="code" />
    <result column="DESCRIPTION" jdbcType="VARCHAR" property="description" />
    <result column="ISAVAILABLE" jdbcType="VARCHAR" property="isavailable" />
    <result column="SOURCECODE" jdbcType="VARCHAR" property="sourcecode" />
    <result column="REVISOR" jdbcType="VARCHAR" property="revisor" />
    <result column="REVISETIME" jdbcType="VARCHAR" property="revisetime" />
    <result column="TABLENAME" jdbcType="VARCHAR" property="tablename" />
    <result column="UNITID" jdbcType="VARCHAR" property="unitid" />
    <result column="BZ" jdbcType="VARCHAR" property="bz" />
  </resultMap>
  <sql id="columns">
    ID, CLOCK, CODE, DESCRIPTION, ISAVAILABLE, SOURCECODE, REVISOR, REVISETIME, TABLENAME, 
    UNITID, BZ
  </sql>
  <sql id="select">
    SELECT <include refid="columns"/> FROM FORMULA
  </sql>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.String">
    delete from FORMULA
    where ID = #{id,jdbcType=VARCHAR}
  </delete>
  <delete id="deleteBySelectiveElement" parameterType="java.util.HashMap">
    delete from FORMULA
    where 1!=1 
      <if test="clock != null and clock != ''">
        or CLOCK = #{clock}
      </if>
      <if test="code != null and code != ''">
        or CODE = #{code}
      </if>
      <if test="description != null and description != ''">
        or DESCRIPTION = #{description}
      </if>
      <if test="isavailable != null and isavailable != ''">
        or ISAVAILABLE = #{isavailable}
      </if>
      <if test="sourcecode != null and sourcecode != ''">
        or SOURCECODE = #{sourcecode}
      </if>
      <if test="unitid != null and unitid != ''">
        or UNITID = #{unitid}
      </if>
      <if test="revisor != null and revisor != ''">
        or REVISOR = #{revisor}
      </if>
      <if test="revisetime != null and revisetime != ''">
        or REVISETIME = #{revisetime}
      </if>
      <if test="tablename != null and tablename != ''">
        or TABLENAME = #{tablename}
      </if>
  </delete>
  <insert id="insert" parameterType="com.steerinfo.ems.formula.model.Formula">
    insert into FORMULA (ID, CLOCK, CODE, 
      DESCRIPTION, ISAVAILABLE, SOURCECODE, UNITID, 
      REVISOR, REVISETIME, TABLENAME, BZ
      )
    values (#{id,jdbcType=VARCHAR}, #{clock,jdbcType=VARCHAR}, #{code,jdbcType=VARCHAR}, 
      #{description,jdbcType=VARCHAR}, #{isavailable,jdbcType=VARCHAR}, #{sourcecode,jdbcType=VARCHAR}, #{unitid,jdbcType=VARCHAR}, 
      #{revisor,jdbcType=VARCHAR}, #{revisetime,jdbcType=VARCHAR}, #{tablename,jdbcType=VARCHAR}, #{bz,jdbcType=VARCHAR}
      )
  </insert>
  <insert id="insertSelective" parameterType="com.steerinfo.ems.formula.model.Formula">
    insert into FORMULA
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="id != null">
        ID,
      </if>
      <if test="clock != null">
        CLOCK,
      </if>
      <if test="code != null">
        CODE,
      </if>
      <if test="description != null">
        DESCRIPTION,
      </if>
      <if test="isavailable != null">
        ISAVAILABLE,
      </if>
      <if test="sourcecode != null">
        SOURCECODE,
      </if>
      <if test="unitid != null">
        UNITID,
      </if>
      <if test="revisor != null">
        REVISOR,
      </if>
      <if test="revisetime != null">
        REVISETIME,
      </if>
      <if test="tablename != null">
        TABLENAME,
      </if>
      <if test="bz != null">
        BZ,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="id != null">
        #{id,jdbcType=VARCHAR},
      </if>
      <if test="clock != null">
        #{clock,jdbcType=VARCHAR},
      </if>
      <if test="code != null">
        #{code,jdbcType=VARCHAR},
      </if>
      <if test="description != null">
        #{description,jdbcType=VARCHAR},
      </if>
      <if test="isavailable != null">
        #{isavailable,jdbcType=VARCHAR},
      </if>
      <if test="sourcecode != null">
        #{sourcecode,jdbcType=VARCHAR},
      </if>
      <if test="unitid != null">
        #{unitid,jdbcType=VARCHAR},
      </if>
      <if test="revisor != null">
        #{revisor,jdbcType=VARCHAR},
      </if>
      <if test="revisetime != null">
        #{revisetime,jdbcType=VARCHAR},
      </if>
      <if test="tablename != null">
        #{tablename,jdbcType=VARCHAR},
      </if>
      <if test="bz != null">
        #{bz,jdbcType=VARCHAR},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKey" parameterType="com.steerinfo.ems.formula.model.Formula">
    update FORMULA
    set CLOCK = #{clock,jdbcType=VARCHAR},
      CODE = #{code,jdbcType=VARCHAR},
      DESCRIPTION = #{description,jdbcType=VARCHAR},
      ISAVAILABLE = #{isavailable,jdbcType=VARCHAR},
      SOURCECODE = #{sourcecode,jdbcType=VARCHAR},
      UNITID = #{unitid,jdbcType=VARCHAR},
      REVISOR = #{revisor,jdbcType=VARCHAR},
      REVISETIME = #{revisetime,jdbcType=VARCHAR},
      TABLENAME = #{tablename,jdbcType=VARCHAR},
      BZ = #{bz,jdbcType=VARCHAR}
    where ID = #{id,jdbcType=VARCHAR}
  </update>
  <update id="updateByPrimaryKeySelective" parameterType="com.steerinfo.ems.formula.model.Formula">
    update FORMULA
    <set>
      <if test="isavailable != null">
        ISAVAILABLE = #{isavailable,jdbcType=VARCHAR},
      </if>
      <if test="sourcecode != null">
        SOURCECODE = #{sourcecode,jdbcType=VARCHAR},
      </if>
      <if test="unitid != null">
        UNITID = #{unitid,jdbcType=VARCHAR},
      </if>
      <if test="revisor != null">
        REVISOR = #{revisor,jdbcType=VARCHAR},
      </if>
      <if test="revisetime != null">
        REVISETIME = #{revisetime,jdbcType=VARCHAR},
      </if>
      <if test="description != null">
        DESCRIPTION = #{description,jdbcType=VARCHAR},
      </if>
      <if test="clock != null">
        CLOCK = #{clock,jdbcType=VARCHAR},
      </if>
      <if test="bz != null">
        BZ = #{bz,jdbcType=VARCHAR},
      </if>
    </set>
    where ID = #{id,jdbcType=VARCHAR}
  </update>
  <select id="selectByPrimaryKey" parameterType="java.lang.String" resultMap="BaseResultMap">
    <include refid="select"/>
    where ID = #{id,jdbcType=VARCHAR}
  </select>
  <select id="selectByParameters" parameterType="java.util.HashMap" resultMap="BaseResultMap">
    select t.ID, t.CLOCK, t.CODE, t.DESCRIPTION, t.ISAVAILABLE, t.SOURCECODE, t.UNITID, t.REVISOR, t.REVISETIME, t.TABLENAME, t.BZ
    from FORMULA t,
    (select CODE, MAX(CLOCK) as CLOCK from FORMULA
    where CLOCK &lt;= #{clock}
    <if test="code != null and code != ''">
        and instr(','||#{code}||',', ','||code||',') &gt;0
    </if>
    <if test="tablename != null and tablename != ''">
      and TABLENAME = #{tablename}
    </if>
    <if test="isavailable != null and isavailable != ''">
      and ISAVAILABLE = #{isavailable}
    </if>
    GROUP BY CODE)t2
    where t.CODE in (t2.CODE)
    and t.CLOCK = t2.CLOCK
    order by t.code
  </select>

  <select id="selectLikeByParameters" parameterType="java.util.HashMap" resultMap="BaseResultMap">
    select t.ID, t.CLOCK, t.CODE, t.DESCRIPTION, t.ISAVAILABLE, t.SOURCECODE, t.UNITID, t.REVISOR, t.REVISETIME, t.TABLENAME, t.BZ
    from FORMULA t,
    (select CODE, MAX(CLOCK) as CLOCK from FORMULA
    where CLOCK &lt;= #{clock}
    <if test="code != null and code != ''">
      and CODE like '%${code}%'
    </if>
    <if test='itemtype == "R" or itemtype == "B" or itemtype == "P" '>
      and CODE like '%${itemtype}'
    </if>
    <if test='itemtype == "J" '>
      and TABLENAME = 'T_RM_CALPOINT_VALUE'
    </if>
    <if test='itemtype == "Z" '>
      and TABLENAME = 'T_RM_INDEX_VALUE'
    </if>
    <if test='itemtype == "E" '>
      and TABLENAME = 'T_RM_ENERGY'
    </if>
    <if test="description != null and description != ''">
      and DESCRIPTION like '%${description}%'
    </if>
    GROUP BY CODE)t2
    where t.CODE = t2.CODE
    and t.CLOCK = t2.CLOCK
    <if test="isavailable != null and isavailable != ''">
      and ISAVAILABLE = #{isavailable}
    </if>
  </select>

  <insert id="batchInsert" parameterType="java.util.List">
    insert into FORMULA 
      (ID, 
      CLOCK, CODE, DESCRIPTION, 
      ISAVAILABLE, SOURCECODE, UNITID, 
      REVISOR, REVISETIME, TABLENAME, BZ)
    ( <foreach collection="list" item="item" separator="union all"> 
   select  
      #{item.id,jdbcType=VARCHAR}, 
      #{item.clock,jdbcType=VARCHAR}, #{item.code,jdbcType=VARCHAR}, #{item.description,jdbcType=VARCHAR}, 
      #{item.isavailable,jdbcType=VARCHAR}, #{item.sourcecode,jdbcType=VARCHAR}, #{item.unitid,jdbcType=VARCHAR},
      #{item.revisor,jdbcType=VARCHAR}, #{item.revisetime,jdbcType=VARCHAR}, #{item.tablename,jdbcType=VARCHAR, #{item.bz,jdbcType=VARCHAR}} from dual  
   </foreach> )
  </insert>
  <update id="batchUpdate" parameterType="java.util.List">
     update FORMULA
     set
       ID=
       <foreach collection="list" item="item" index="index" separator=" " open="case ID" close="end">
          when #{item.id,jdbcType=VARCHAR} then #{item.id,jdbcType=VARCHAR}
       </foreach>
       ,CLOCK=
       <foreach collection="list" item="item" index="index" separator=" " open="case ID" close="end">
          when #{item.id,jdbcType=VARCHAR} then #{item.clock,jdbcType=VARCHAR}
       </foreach>
       ,CODE=
       <foreach collection="list" item="item" index="index" separator=" " open="case ID" close="end">
          when #{item.id,jdbcType=VARCHAR} then #{item.code,jdbcType=VARCHAR}
       </foreach>
       ,DESCRIPTION=
       <foreach collection="list" item="item" index="index" separator=" " open="case ID" close="end">
          when #{item.id,jdbcType=VARCHAR} then #{item.description,jdbcType=VARCHAR}
       </foreach>
       ,ISAVAILABLE=
       <foreach collection="list" item="item" index="index" separator=" " open="case ID" close="end">
          when #{item.id,jdbcType=VARCHAR} then #{item.isavailable,jdbcType=VARCHAR}
       </foreach>
       ,SOURCECODE=
       <foreach collection="list" item="item" index="index" separator=" " open="case ID" close="end">
          when #{item.id,jdbcType=VARCHAR} then #{item.sourcecode,jdbcType=VARCHAR}
       </foreach>
       ,UNITID=
       <foreach collection="list" item="item" index="index" separator=" " open="case ID" close="end">
          when #{item.id,jdbcType=VARCHAR} then #{item.unitid,jdbcType=VARCHAR}
       </foreach>
       ,REVISOR=
       <foreach collection="list" item="item" index="index" separator=" " open="case ID" close="end">
          when #{item.id,jdbcType=VARCHAR} then #{item.revisor,jdbcType=VARCHAR}
       </foreach>
       ,REVISETIME=
       <foreach collection="list" item="item" index="index" separator=" " open="case ID" close="end">
          when #{item.id,jdbcType=VARCHAR} then #{item.revisetime,jdbcType=VARCHAR}
       </foreach>
       ,TABLENAME=
       <foreach collection="list" item="item" index="index" separator=" " open="case ID" close="end">
          when #{item.id,jdbcType=VARCHAR} then #{item.tablename,jdbcType=VARCHAR}
       </foreach>
       ,BZ=
       <foreach collection="list" item="item" index="index" separator=" " open="case ID" close="end">
          when #{item.id,jdbcType=VARCHAR} then #{item.bz,jdbcType=VARCHAR}
       </foreach>
     where ID in 
     <foreach collection="list" index="index" item="item" separator="," open="(" close=")">
    #{item.id,jdbcType=VARCHAR}
     </foreach> 
  </update>
  <delete id="batchDelete" parameterType="java.util.List">
    delete from FORMULA
    where ID in 
    <foreach collection="list" item="id" open="(" close=")" separator=",">
      #{id}
    </foreach>
  </delete>

    <!--根据公式code查找-->
    <select id="getByCode" parameterType="java.lang.String" resultType="com.steerinfo.ems.formula.model.Formula">
    select ID, CLOCK, CODE, DESCRIPTION, ISAVAILABLE, SOURCECODE, UNITID, REVISOR, REVISETIME, BZ, TABLENAME
    from FORMULA
    WHERE
    CODE =#{code,jdbcType=VARCHAR}
    </select>
    
  <!-- 获取公式与写入表名 -->
  <select id="getFormulaAndTbname" parameterType="java.util.HashMap" resultType = "Map">
    select sourcecode,tablename from
    (select sourcecode,row_number() over (partition by code order by clock desc) rn,tablename,isavailable from formula 
    where code=#{itemid} and clock &lt;=#{clock})
    where isavailable='1' and rn = 1
  </select>
  
  <!-- 获取公式内容 -->
  <select id="getFormula" parameterType="java.util.HashMap" resultType = "string">
    select sourcecode from
    (select sourcecode,isavailable from 
        (select sourcecode,isavailable from FORMULA where code=#{itemid} and clock &lt;= #{clock} order by clock desc)
     where rownum=1) where isavailable='1'
  </select>

    <!--根据code进行删除-->
    <delete id="deleteBycode" parameterType="java.lang.String" >
      DELETE FROM FORMULA WHERE CODE=#{code}
    </delete>

	<!-- 当公式code发生更改，替换对应的公式内容  -->
	<update id="replaceFormula" parameterType="java.util.HashMap">
	  update FORMULA set sourcecode = replace(sourcecode, #{oldformula,jdbcType=VARCHAR}, #{newformula,jdbcType=VARCHAR}) 
	  where sourcecode like '%${oldformula,jdbcType=VARCHAR}%'
	</update>
  
    <!--根据公式code查找公式包含code的公式-->
    <select id="getFormulaByCode" parameterType="java.lang.String" resultType="com.steerinfo.ems.formula.model.Formula">
    select ID, CLOCK, CODE, DESCRIPTION, ISAVAILABLE, SOURCECODE, UNITID, REVISOR, REVISETIME,TABLENAME, BZ
    from FORMULA
    WHERE sourcecode like '%[${code}]%'
    </select>
    
    <!-- 获取最新版本的公式-->
	<select id="getFormulaLatest" parameterType="string" resultMap = "BaseResultMap">
	   select ID, CLOCK, CODE, DESCRIPTION, ISAVAILABLE, SOURCECODE, TABLENAME, UNITID, BZ from
	   (select ID, CLOCK, CODE, DESCRIPTION, ISAVAILABLE, SOURCECODE, TABLENAME, UNITID, BZ
	   from FORMULA where code=#{itemid} and isavailable = '1' order by clock desc)
	   where rownum=1
	</select>
	
	<!-- 根据项目编号、时间获取公式，clock之前最近的公式 -->
    <select id="getFormulaCodeClock" parameterType="java.util.HashMap" resultMap = "BaseResultMap">
       select ID, CLOCK, CODE, DESCRIPTION, ISAVAILABLE, SOURCECODE, TABLENAME, UNITID, BZ from
       (select ID, CLOCK, CODE, DESCRIPTION, ISAVAILABLE, SOURCECODE, TABLENAME, UNITID, BZ
       from FORMULA where code=#{code} and clock &lt;= #{clock} order by clock desc)
       where rownum=1
    </select>
    
    <!-- 根据项目编号、版本获取公式，指定版本 -->
    <select id="getFormulaCodeClock2" parameterType="java.util.HashMap" resultMap = "BaseResultMap">
       select ID, CLOCK, CODE, DESCRIPTION, ISAVAILABLE, SOURCECODE, TABLENAME, UNITID, BZ from
       (select ID, CLOCK, CODE, DESCRIPTION, ISAVAILABLE, SOURCECODE, TABLENAME, UNITID, BZ
       from FORMULA where code=#{code} and clock= #{clock} order by clock desc)
       where rownum=1
    </select>

  <!-- 获取该公式名所关联的所有公式 -->
  <select id="getAssociatedFormula" parameterType="java.lang.String" resultType="com.steerinfo.ems.formula.model.Formula">
    select t.ID, t.CLOCK, t.CODE, t.DESCRIPTION, t.ISAVAILABLE, t.SOURCECODE, t.UNITID, t.REVISOR, t.REVISETIME, t.TABLENAME, t.BZ
    from FORMULA t,
    (select CODE, MAX(CLOCK) as CLOCK from FORMULA
    where CLOCK &lt;= to_char(sysdate,'YYYYMM')
    GROUP BY CODE)t2
    where t.CODE = t2.CODE
    and t.CLOCK = t2.CLOCK
    and ISAVAILABLE = '1'
    and SOURCECODE not like 'R%' and SOURCECODE LIKE '%${code}%'
  </select>
  
  <!--查找日期前code的所有公式-->
  <select id="getByCodeClock" parameterType="java.util.HashMap" resultType="com.steerinfo.ems.formula.model.Formula">
   select ID, CLOCK, CODE, DESCRIPTION, ISAVAILABLE, SOURCECODE, UNITID, REVISOR, REVISETIME, TABLENAME, BZ
   from FORMULA
   WHERE CODE =#{code} and clock &lt;= #{clock}
  </select>
  
  <!-- 根据code进行批量更新描述 -->
  <update id="updateDesBycode" parameterType="java.util.HashMap">
    update FORMULA
    <set>
      <if test="clock != null">
        CLOCK = #{clock,jdbcType=VARCHAR},
      </if>
      <if test="isavailable != null">
        ISAVAILABLE = #{isavailable,jdbcType=VARCHAR},
      </if>
      <if test="sourcecode != null">
        SOURCECODE = #{sourcecode,jdbcType=VARCHAR},
      </if>
      <if test="description != null">
        DESCRIPTION = #{description} ,
      </if>
      <if test="tablename != null">
        TABLENAME = #{tablename},
      </if>
    </set>
    where CODE=#{code}
  </update>
  
  <!--查找日期前code的所有公式-->
  <select id="getClockByCode" parameterType="java.util.HashMap" resultType = "string">
    select clock from FORMULA where code = #{code} and isavailable = '1' order by clock desc
  </select>
  <!--更新所有公式-->
  <select id="getDateForPage" parameterType="java.util.HashMap" resultType = "Map">
    select * from FORMULA
    <where>
      <if test="code != null and code != ''">
        and CODE = #{code}
      </if>
      <if test="description != null and description != ''">
        and DESCRIPTION LIKE '%${description}%'
      </if>
      <if test="tablename != null and tablename != ''">
        and TABLENAME in (${tablename})
      </if>
      <if test="sourcecode != null and sourcecode != ''">
        and SOURCECODE LIKE '%${sourcecode}%'
      </if>
      <if test="clock != null and clock != ''">
        and CLOCK LIKE '%${clock}%'
      </if>
    </where>
  </select>
</mapper>