<?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="cn.com.victorysoft.business.ConditionUnitDao">
  <sql id="Base_Column_List">
    t.UNITCODE, RECORD_DATE, NORMAL_WELL, LESS_WELL, MORE_WELL, UNCLEAR_WELL, ABNORMAL_WELL,
    SUMMARY_DATE,
    su.unitname,
    decode(TOTAL_WELL, 0, null, round(NORMAL_WELL / TOTAL_WELL * 100, 2)) NORMAL_RATE,
    decode(TOTAL_WELL, 0, null, round(LESS_WELL / TOTAL_WELL * 100, 2)) LESS_RATE,
    decode(TOTAL_WELL, 0, null, round(MORE_WELL / TOTAL_WELL * 100, 2)) MORE_RATE,
    decode(TOTAL_WELL, 0, null, round(UNCLEAR_WELL / TOTAL_WELL * 100, 2)) UNCLEAR_RATE,
    decode(TOTAL_WELL, 0, null, round(ABNORMAL_WELL / TOTAL_WELL * 100, 2)) ABNORMAL_RATE
  </sql>

  <select id="selectUnit" parameterType="UnitDatePageParam" resultType="ConditionUnit">
    select
    <include refid="Base_Column_List" />
    from
    (select t.*,
    (nvl(NORMAL_WELL, 0) + nvl(LESS_WELL, 0) + nvl(MORE_WELL, 0) + nvl(UNCLEAR_WELL, 0) + nvl(ABNORMAL_WELL, 0)) TOTAL_WELL
    from CON_CONDITION_UNIT t) t, V_SYS_UNITINFO su
    where t.RECORD_DATE = #{testdate}
    and t.UNITCODE = su.UNITCODE
    and su.unitcode= #{unitcode,jdbcType=VARCHAR}
  </select>

  <select id="selectChildren" parameterType="UnitDatePageParam" resultType="ConditionUnit">
    select
    <include refid="Base_Column_List" />
    from
    (select t.*,
    (nvl(NORMAL_WELL, 0) + nvl(LESS_WELL, 0) + nvl(MORE_WELL, 0) + nvl(UNCLEAR_WELL, 0) + nvl(ABNORMAL_WELL, 0)) TOTAL_WELL
    from CON_CONDITION_UNIT t) t, V_SYS_UNITINFO su
    where t.RECORD_DATE = #{testdate}
    and t.UNITCODE = su.UNITCODE
    and su.p_unitcode= #{unitcode,jdbcType=VARCHAR}
    order by su.unitorder
  </select>

  <delete id="deleteByPrimaryKey" parameterType="ConditionUnit">
    delete from CON_CONDITION_UNIT
    where UNITCODE = #{unitcode,jdbcType=VARCHAR}
      and RECORD_DATE = #{recordDate,jdbcType=TIMESTAMP}
  </delete>
  <delete id="deleteByDate" parameterType="Date">
    delete from CON_CONDITION_UNIT
    where RECORD_DATE = #{value,jdbcType=TIMESTAMP}
  </delete>
  <insert id="insert" parameterType="ConditionUnit">
    insert into CON_CONDITION_UNIT (UNITCODE, RECORD_DATE, NORMAL_WELL, 
      LESS_WELL, MORE_WELL, UNCLEAR_WELL, 
      ABNORMAL_WELL, SUMMARY_DATE)
    values (#{unitcode,jdbcType=VARCHAR}, #{recordDate,jdbcType=TIMESTAMP}, #{normalWell,jdbcType=DECIMAL}, 
      #{lessWell,jdbcType=DECIMAL}, #{moreWell,jdbcType=DECIMAL}, #{unclearWell,jdbcType=DECIMAL}, 
      #{abnormalWell,jdbcType=DECIMAL}, #{summaryDate,jdbcType=TIMESTAMP})
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="ConditionUnit">
    update CON_CONDITION_UNIT
    <set>
      <if test="normalWell != null">
        NORMAL_WELL = #{normalWell,jdbcType=DECIMAL},
      </if>
      <if test="lessWell != null">
        LESS_WELL = #{lessWell,jdbcType=DECIMAL},
      </if>
      <if test="moreWell != null">
        MORE_WELL = #{moreWell,jdbcType=DECIMAL},
      </if>
      <if test="unclearWell != null">
        UNCLEAR_WELL = #{unclearWell,jdbcType=DECIMAL},
      </if>
      <if test="abnormalWell != null">
        ABNORMAL_WELL = #{abnormalWell,jdbcType=DECIMAL},
      </if>
      <if test="summaryDate != null">
        SUMMARY_DATE = #{summaryDate,jdbcType=TIMESTAMP},
      </if>
    </set>
    where UNITCODE = #{unitcode,jdbcType=VARCHAR}
      and RECORD_DATE = #{recordDate,jdbcType=TIMESTAMP}
  </update>
  <update id="updateByPrimaryKey" parameterType="ConditionUnit">
    update CON_CONDITION_UNIT
    set NORMAL_WELL = #{normalWell,jdbcType=DECIMAL},
      LESS_WELL = #{lessWell,jdbcType=DECIMAL},
      MORE_WELL = #{moreWell,jdbcType=DECIMAL},
      UNCLEAR_WELL = #{unclearWell,jdbcType=DECIMAL},
      ABNORMAL_WELL = #{abnormalWell,jdbcType=DECIMAL},
      SUMMARY_DATE = #{summaryDate,jdbcType=TIMESTAMP}
    where UNITCODE = #{unitcode,jdbcType=VARCHAR}
      and RECORD_DATE = #{recordDate,jdbcType=TIMESTAMP}
  </update>
</mapper>