<?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.AbnormalWellReasonDao">
  <select id="selectList" parameterType="AbnormalWellReasonParam" resultType="AbnormalWellReason">
    select
    t.WELLNAME, t."MONTH", su.UNITCODE, REASON_ID, CREATED_ON, MODIFIED_ON,
    (select flag_name from sys_flag where category_code = 'ABNORMAL_REASON' and flag_code = r.REASON_ID) REASON_NAME,
    su.unitname, su.fullname,
    (select blockname from sys_blockinfo where blockcode = wb.blockcode) blockname,
    (select unitname from sys_unitinfo where unitcode = su.p_unitcode) areaname
    from
    (select * from
    (select wellname, month, max(decode(EVALUATE, 'UNCLEAR', 1, 0)) unclear,
    max(DAY_NUM) DAY_NUM, sum(MONTH_WATER) MONTH_WATER
    from indi_well_section_month
    where month = #{month1}
    group by wellname, month)
    where unclear = 1 or (DAY_NUM &lt; 1 and MONTH_WATER = 0)) t,
    CON_ABNORMAL_WELL_REASON r, water_well_basic wb, sys_unitinfo su
    where t.wellname = wb.wellname
    and t.wellname = r.wellname(+)
    and t.month = r.month(+)
    and wb.unitcode = su.unitcode
    <if test="unitcode != null and unitcode != ''">
      and su.unitcode in (select unitcode from sys_unitinfo start with unitcode = #{unitcode} connect by prior unitcode = p_unitcode)
    </if>
    <if test="blockcode != null and blockcode != ''">
      and wb.blockcode = #{blockcode}
    </if>
    <if test="wellname != null and wellname != ''">
      and wb.wellname = #{wellname}
    </if>
    <if test="reasonId != null and reasonId != ''">
      and r.REASON_ID = #{reasonId}
    </if>
    order by t.wellname
  </select>
  <select id="selectByPrimaryKey" parameterType="AbnormalWellReason" resultType="AbnormalWellReason">
    select 
    t.WELLNAME, t."MONTH", t.UNITCODE, REASON_ID, CREATED_ON, MODIFIED_ON
    from CON_ABNORMAL_WELL_REASON t
    where WELLNAME = #{wellname,jdbcType=VARCHAR}
      and "MONTH" = #{month,jdbcType=CHAR}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="AbnormalWellReason">
    delete from CON_ABNORMAL_WELL_REASON
    where WELLNAME = #{wellname,jdbcType=VARCHAR}
      and "MONTH" = #{month,jdbcType=CHAR}
  </delete>
  <insert id="insert" parameterType="AbnormalWellReason">
    insert into CON_ABNORMAL_WELL_REASON (WELLNAME, "MONTH", UNITCODE, 
      REASON_ID, CREATED_ON, MODIFIED_ON
      )
    values (#{wellname,jdbcType=VARCHAR}, #{month,jdbcType=CHAR}, #{unitcode,jdbcType=VARCHAR}, 
      #{reasonId,jdbcType=VARCHAR}, #{createdOn,jdbcType=TIMESTAMP}, #{modifiedOn,jdbcType=TIMESTAMP}
      )
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="AbnormalWellReason">
    update CON_ABNORMAL_WELL_REASON
    <set>
      <if test="unitcode != null">
        UNITCODE = #{unitcode,jdbcType=VARCHAR},
      </if>
      <if test="reasonId != null">
        REASON_ID = #{reasonId,jdbcType=VARCHAR},
      </if>
      <if test="createdOn != null">
        CREATED_ON = #{createdOn,jdbcType=TIMESTAMP},
      </if>
      <if test="modifiedOn != null">
        MODIFIED_ON = #{modifiedOn,jdbcType=TIMESTAMP},
      </if>
    </set>
    where WELLNAME = #{wellname,jdbcType=VARCHAR}
      and "MONTH" = #{month,jdbcType=CHAR}
  </update>
  <update id="updateByPrimaryKey" parameterType="AbnormalWellReason">
    update CON_ABNORMAL_WELL_REASON
    set UNITCODE = #{unitcode,jdbcType=VARCHAR},
      REASON_ID = #{reasonId,jdbcType=VARCHAR},
      CREATED_ON = #{createdOn,jdbcType=TIMESTAMP},
      MODIFIED_ON = #{modifiedOn,jdbcType=TIMESTAMP}
    where WELLNAME = #{wellname,jdbcType=VARCHAR}
      and "MONTH" = #{month,jdbcType=CHAR}
  </update>
</mapper>