<?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.indicator.dao.WellSectionMonthDao">
  <sql id="Base_Column_List">
    iwsm.WELLNAME, iwsm."MONTH", iwsm.UNITCODE, iwsm.WELL_TYPE, iwsm."SECTION", NOZZLE, PP, PC, PT, PLAN_DAILY_WATER,
    DAILY_WATER, MONTH_WATER, DAY_NUM, DAILY_ABILITY, EVALUATE, WATER_WELL_CLASSIFY,
    INJECT_TYPE, LESS_TYPE, SECTION_OPEN,
    DIFF_WATER, REASON_ID, MEASURE_ID, APPLY, LAYER,
    EVALUATE_BASIS, EVALUATE_CALC, EVALUATE_EDIT, REMARK, APPROVED, EDIT_REASON,
    decode(APPROVED, '0', '待审核', '1', '审核通过', '2', '审核拒绝') APPROVED_NAME,
    su.unitname, su.fullname,
    (select blockname from sys_blockinfo where blockcode = wb.blockcode) blockname,
    (select flag_name from sys_flag where category_code = 'WELL_TYPE' and flag_code = iwsm.WELL_TYPE) WELL_TYPE_NAME,
    (select flag_name from sys_flag where category_code = 'WATER_WELL_CLASSIFY' and flag_code = iwsm.WATER_WELL_CLASSIFY) WATER_WELL_CLASSIFY_NAME,
    (select flag_name from sys_flag where category_code = 'SECTION_EVALUATE' and flag_code = iwsm.EVALUATE) EVALUATE_NAME,
    (select flag_name from sys_flag where category_code = 'SECTION_EVALUATE' and flag_code = iwsm.EVALUATE_EDIT) EVALUATE_EDIT_NAME,
    (select flag_name from sys_flag where category_code = 'SECTION_EVALUATE' and flag_code = iwsm.EVALUATE_CALC) EVALUATE_CALC_NAME,
    (select flag_name from sys_flag where category_code = 'INJECT_TYPE' and flag_code = iwsm.INJECT_TYPE) INJECT_TYPE_NAME,
    (select flag_name from sys_flag where category_code = iwsm.EVALUATE || '_REASON' and flag_code = iwsm.REASON_ID) REASON_NAME,
    (select flag_name from sys_flag where category_code = iwsm.EVALUATE || '_MEASURE' and flag_code = iwsm.MEASURE_ID) MEASURE_NAME
  </sql>

  <select id="selectList" parameterType="WellSectionMonthParam" resultType="WellSectionMonth">
    select
    <include refid="Base_Column_List" />
    from INDI_WELL_SECTION_MONTH iwsm, water_well_basic wb, sys_unitinfo su
    where iwsm.wellname = wb.wellname
    and iwsm.unitcode = su.unitcode(+)
    <if test="blockcode != null and blockcode != ''">
      and wb.blockcode = #{blockcode}
    </if>
    <if test="unitcode != null and unitcode != ''">
      and wb.${searchUnitColumn} = #{unitcode}
    </if>
    <if test="month != null and month != ''">
      and iwsm.month = #{month}
    </if>
    <if test="month1 != null and month1 != ''">
      and iwsm.month >= #{month1}
    </if>
    <if test="month2 != null and month2 != ''">
      and iwsm.month &lt;= #{month2}
    </if>
    <if test="waterWellClassify != null and waterWellClassify != ''">
      and iwsm.water_well_classify = #{waterWellClassify}
    </if>
    <if test="evaluate != null and evaluate != ''">
      and iwsm.evaluate = #{evaluate}
    </if>
    <if test="wellTypes != null and wellTypes.size > 0">
      and iwsm.well_type in
      <foreach collection="wellTypes" item="item" open="(" separator="," close=")">
        #{item}
      </foreach>
    </if>
    <if test="injectTypes != null and injectTypes.size > 0">
      and iwsm.inject_type in
      <foreach collection="injectTypes" item="item" open="(" separator="," close=")">
        #{item}
      </foreach>
    </if>
    <if test="injectType != null and injectType != ''">
      and iwsm.inject_type = #{injectType}
    </if>
    <if test="sectionOpen != null and sectionOpen != ''">
      and iwsm.section_open = #{sectionOpen}
    </if>
    <if test='checkWell == "1"'>
      and iwsm.evaluate is not null and iwsm.evaluate != 'NO_CHECK'
    </if>
    <if test='checkWell == "0"'>
      and iwsm.evaluate = 'NO_CHECK'
    </if>
    <if test='splitWell == "1"'>
      and iwsm.inject_type != 'DAN_ZHU'
    </if>
    <if test='splitWell == "0"'>
      and iwsm.inject_type = 'DAN_ZHU'
    </if>
    <if test="wellname != null and wellname != ''">
      and iwsm.wellname = #{wellname}
    </if>
    <if test="reasonId != null and reasonId != ''">
      and iwsm.REASON_ID = #{reasonId}
    </if>
    <if test="measureId != null and measureId != ''">
      and iwsm.MEASURE_ID = #{measureId}
    </if>
    <if test="apply != null">
      and iwsm.apply = #{apply}
    </if>
    <if test="approved != null and approved != ''">
      <if test="shzt">
        and iwsm.approved = '0'
      </if>
      <if test="shzt == false ">
        and iwsm.approved != '0'
      </if>
    </if>
    <if test="compareMonth != null and compareMonth != ''">
      and (
        exists (select 1
          from indi_well_section_month
          where month = #{compareMonth}
          and (EVALUATE is null or EVALUATE != #{evaluate}) -- and EVALUATE = 'NORMAL'
          and wellname = iwsm.wellname
          and section = iwsm.section)
        or not exists (select 1
          from indi_well_section_month
          where month = #{compareMonth}
          and wellname = iwsm.wellname
          and section = iwsm.section)
      )
    </if>
    order by
    <if test="approved != null and approved != ''">
      iwsm.approved,
    </if>
    iwsm.month, iwsm.wellname, iwsm.section
  </select>

  <select id="selectListTmp" parameterType="WellSectionMonthParam" resultType="WellSectionMonth">
    select
    <include refid="Base_Column_List" />
    from INDI_WELL_SECTION_MONTH_TMP iwsm, water_well_basic wb, sys_unitinfo su
    where iwsm.wellname = wb.wellname
    and iwsm.unitcode = su.unitcode
    and iwsm.tmp_id = #{tmpId}
    <if test="blockcode != null and blockcode != ''">
      and wb.blockcode = #{blockcode}
    </if>
    <if test="waterWellClassify != null and waterWellClassify != ''">
      and iwsm.water_well_classify = #{waterWellClassify}
    </if>
    <if test="evaluate != null and evaluate != ''">
      and iwsm.evaluate = #{evaluate}
    </if>
    <if test="wellType != null and wellType != ''">
      and iwsm.well_type in (${wellType})
    </if>
    <if test="injectType != null and injectType != ''">
      and iwsm.inject_type = #{injectType}
    </if>
    <if test="wellname != null and wellname != ''">
      and iwsm.wellname = #{wellname}
    </if>
    <if test="apply != null">
      and iwsm.apply = #{apply}
    </if>
    order by iwsm.month, iwsm.wellname, iwsm.section
  </select>

  <select id="selectNextMonthList" resultType="WellSectionMonth">
    with tmp as
    <foreach collection="list" item="item" open="(" close=")" separator="UNION ALL">
      SELECT #{item.wellname} as wellname,
      to_char(add_months(to_date(#{item.month},'yyyymm'),1),'yyyymm') as month,
      #{item.section} as section
      FROM dual
    </foreach>
    select
    <include refid="Base_Column_List" />
    from tmp
    join INDI_WELL_SECTION_MONTH iwsm
    on tmp.wellname = iwsm.wellname and tmp.month = iwsm.month and tmp.section = iwsm.section
    join water_well_basic wb
    on iwsm.wellname = wb.wellname
    join sys_unitinfo su
    on iwsm.unitcode = su.unitcode
  </select>

  <select id="selectOpen" parameterType="String" resultType="WellSectionMonth">
    select
    <include refid="Base_Column_List" />
    from INDI_WELL_SECTION_MONTH iwsm, water_well_basic wb, sys_unitinfo su
    where iwsm.wellname = wb.wellname
    and iwsm.unitcode = su.unitcode
    and iwsm.SECTION_OPEN = '1'
    and iwsm.month = #{value}
  </select>

  <select id="selectByPrimaryKey" parameterType="WellSectionMonth" resultType="WellSectionMonth">
    select
    <include refid="Base_Column_List" />
    from INDI_WELL_SECTION_MONTH iwsm, water_well_basic wb, sys_unitinfo su
    where iwsm.wellname = wb.wellname
    and iwsm.unitcode = su.unitcode
    and iwsm.WELLNAME = #{wellname,jdbcType=VARCHAR}
    and "MONTH" = #{month,jdbcType=CHAR}
    and "SECTION" = #{section,jdbcType=INTEGER}
  </select>

  <delete id="deleteByPrimaryKey" parameterType="WellSectionMonth">
    delete from INDI_WELL_SECTION_MONTH
    where WELLNAME = #{wellname,jdbcType=VARCHAR}
      and "MONTH" = #{month,jdbcType=CHAR}
      and "SECTION" = #{section,jdbcType=INTEGER}
  </delete>

  <delete id="deleteByMonth" parameterType="String">
    delete from INDI_WELL_SECTION_MONTH
    where "MONTH" = #{value,jdbcType=CHAR}
  </delete>

  <delete id="deleteTmp" parameterType="String">
    delete from INDI_WELL_SECTION_MONTH_TMP
    where TMP_ID = #{value,jdbcType=CHAR}
  </delete>

  <insert id="insert" parameterType="WellSectionMonth">
    insert into INDI_WELL_SECTION_MONTH (WELLNAME, "MONTH", UNITCODE,
      WELL_TYPE, "SECTION", NOZZLE,
      PP, PC, PT, PLAN_DAILY_WATER,
      DAILY_WATER, MONTH_WATER, DAY_NUM,
      DAILY_ABILITY, EVALUATE, WATER_WELL_CLASSIFY,
      INJECT_TYPE, LESS_TYPE, SECTION_OPEN,
      DIFF_WATER, REASON_ID, MEASURE_ID, APPLY, REMARK_NO_CHECK,LAYER, EVALUATE_BASIS,
      EVALUATE_CALC, EVALUATE_EDIT, REMARK, APPROVED, EDIT_REASON)
    values (#{wellname,jdbcType=VARCHAR}, #{month,jdbcType=CHAR}, #{unitcode,jdbcType=VARCHAR},
      #{wellType,jdbcType=VARCHAR}, #{section,jdbcType=INTEGER}, #{nozzle,jdbcType=VARCHAR},
      #{pp,jdbcType=DECIMAL}, #{pc,jdbcType=DECIMAL}, #{pt,jdbcType=DECIMAL}, #{planDailyWater,jdbcType=DECIMAL},
      #{dailyWater,jdbcType=DECIMAL}, #{monthWater,jdbcType=DECIMAL}, #{dayNum,jdbcType=DECIMAL},
      #{dailyAbility,jdbcType=DECIMAL}, #{evaluate,jdbcType=VARCHAR}, #{waterWellClassify,jdbcType=VARCHAR},
      #{injectType,jdbcType=VARCHAR}, #{lessType,jdbcType=VARCHAR}, #{sectionOpen,jdbcType=CHAR},
      #{diffWater,jdbcType=DECIMAL}, #{reasonId,jdbcType=VARCHAR}, #{measureId,jdbcType=VARCHAR},
      #{apply,jdbcType=CHAR}, #{remarkNoCheck,jdbcType=CHAR}, #{layer,jdbcType=VARCHAR},
      #{evaluateBasis,jdbcType=VARCHAR}, #{evaluateCalc,jdbcType=VARCHAR},
      #{evaluateEdit,jdbcType=VARCHAR}, #{remark,jdbcType=VARCHAR}, #{approved,jdbcType=CHAR},
      #{editReason,jdbcType=VARCHAR})
  </insert>

  <insert id="insertBatch" parameterType="java.util.List">
    insert into INDI_WELL_SECTION_MONTH (WELLNAME, "MONTH", UNITCODE,
      WELL_TYPE, "SECTION", NOZZLE,
      PP, PC, PT, PLAN_DAILY_WATER,
      DAILY_WATER, MONTH_WATER, DAY_NUM,
      DAILY_ABILITY, EVALUATE, WATER_WELL_CLASSIFY,
      INJECT_TYPE, LESS_TYPE, SECTION_OPEN,
      DIFF_WATER, REASON_ID, MEASURE_ID, APPLY, REMARK_NO_CHECK,LAYER, EVALUATE_BASIS,
      EVALUATE_CALC, EVALUATE_EDIT, REMARK, APPROVED, EDIT_REASON)
    <foreach collection="list" item="item" separator="UNION ALL">
      SELECT
      #{item.wellname,jdbcType=VARCHAR}, #{item.month,jdbcType=CHAR}, #{item.unitcode,jdbcType=VARCHAR},
      #{item.wellType,jdbcType=VARCHAR}, #{item.section,jdbcType=INTEGER}, #{item.nozzle,jdbcType=VARCHAR},
      #{item.pp,jdbcType=DECIMAL}, #{item.pc,jdbcType=DECIMAL}, #{item.pt,jdbcType=DECIMAL}, #{item.planDailyWater,jdbcType=DECIMAL},
      #{item.dailyWater,jdbcType=DECIMAL}, #{item.monthWater,jdbcType=DECIMAL}, #{item.dayNum,jdbcType=DECIMAL},
      #{item.dailyAbility,jdbcType=DECIMAL}, #{item.evaluate,jdbcType=VARCHAR}, #{item.waterWellClassify,jdbcType=VARCHAR},
      #{item.injectType,jdbcType=VARCHAR}, #{item.lessType,jdbcType=VARCHAR}, #{item.sectionOpen,jdbcType=CHAR},
      #{item.diffWater,jdbcType=DECIMAL}, #{item.reasonId,jdbcType=VARCHAR}, #{item.measureId,jdbcType=VARCHAR},
      #{item.apply,jdbcType=CHAR}, #{item.remarkNoCheck,jdbcType=CHAR}, #{item.layer,jdbcType=VARCHAR},
      #{item.evaluateBasis,jdbcType=VARCHAR}, #{item.evaluateCalc,jdbcType=VARCHAR},
      #{item.evaluateEdit,jdbcType=VARCHAR}, #{item.remark,jdbcType=VARCHAR}, #{item.approved,jdbcType=CHAR},
      #{item.editReason,jdbcType=VARCHAR}
      FROM dual
    </foreach>
  </insert>

  <insert id="insertBatchTmp" parameterType="java.util.List">
    insert into INDI_WELL_SECTION_MONTH_TMP (WELLNAME, "MONTH", UNITCODE,
    WELL_TYPE, "SECTION", NOZZLE,
    PP, PC, PT, PLAN_DAILY_WATER,
    DAILY_WATER, MONTH_WATER, DAY_NUM,
    DAILY_ABILITY, EVALUATE, WATER_WELL_CLASSIFY,
    INJECT_TYPE, LESS_TYPE, SECTION_OPEN,
    DIFF_WATER, REASON_ID, MEASURE_ID, APPLY, REMARK_NO_CHECK,LAYER, EVALUATE_BASIS,
    EVALUATE_CALC, EVALUATE_EDIT, REMARK, APPROVED, EDIT_REASON, TMP_ID)
    <foreach collection="list" item="item" separator="UNION ALL">
      SELECT
      #{item.wellname,jdbcType=VARCHAR}, #{item.month,jdbcType=CHAR}, #{item.unitcode,jdbcType=VARCHAR},
      #{item.wellType,jdbcType=VARCHAR}, #{item.section,jdbcType=INTEGER}, #{item.nozzle,jdbcType=VARCHAR},
      #{item.pp,jdbcType=DECIMAL}, #{item.pc,jdbcType=DECIMAL}, #{item.pt,jdbcType=DECIMAL}, #{item.planDailyWater,jdbcType=DECIMAL},
      #{item.dailyWater,jdbcType=DECIMAL}, #{item.monthWater,jdbcType=DECIMAL}, #{item.dayNum,jdbcType=DECIMAL},
      #{item.dailyAbility,jdbcType=DECIMAL}, #{item.evaluate,jdbcType=VARCHAR}, #{item.waterWellClassify,jdbcType=VARCHAR},
      #{item.injectType,jdbcType=VARCHAR}, #{item.lessType,jdbcType=VARCHAR}, #{item.sectionOpen,jdbcType=CHAR},
      #{item.diffWater,jdbcType=DECIMAL}, #{item.reasonId,jdbcType=VARCHAR}, #{item.measureId,jdbcType=VARCHAR},
      #{item.apply,jdbcType=CHAR}, #{item.remarkNoCheck,jdbcType=CHAR}, #{item.layer,jdbcType=VARCHAR},
      #{item.evaluateBasis,jdbcType=VARCHAR}, #{item.evaluateCalc,jdbcType=VARCHAR},
      #{item.evaluateEdit,jdbcType=VARCHAR}, #{item.remark,jdbcType=VARCHAR}, #{item.approved,jdbcType=CHAR},
      #{item.editReason,jdbcType=VARCHAR}, #{item.tmpId,jdbcType=VARCHAR}
      FROM dual
    </foreach>
  </insert>

  <update id="updateByPrimaryKeySelective" parameterType="WellSectionMonth">
    update INDI_WELL_SECTION_MONTH
    <set>
      <if test="unitcode != null">
        UNITCODE = #{unitcode,jdbcType=VARCHAR},
      </if>
      <if test="wellType != null">
        WELL_TYPE = #{wellType,jdbcType=VARCHAR},
      </if>
      <if test="section != null">
        "SECTION" = #{section,jdbcType=INTEGER},
      </if>
      <if test="nozzle != null">
        NOZZLE = #{nozzle,jdbcType=VARCHAR},
      </if>
      <if test="pp != null">
        PP = #{pp,jdbcType=DECIMAL},
      </if>
      <if test="pc != null">
        PC = #{pc,jdbcType=DECIMAL},
      </if>
      <if test="pt != null">
        PT = #{pt,jdbcType=DECIMAL},
      </if>
      <if test="planDailyWater != null">
        PLAN_DAILY_WATER = #{planDailyWater,jdbcType=DECIMAL},
      </if>
      <if test="dailyWater != null">
        DAILY_WATER = #{dailyWater,jdbcType=DECIMAL},
      </if>
      <if test="monthWater != null">
        MONTH_WATER = #{monthWater,jdbcType=DECIMAL},
      </if>
      <if test="dayNum != null">
        DAY_NUM = #{dayNum,jdbcType=DECIMAL},
      </if>
      <if test="dailyAbility != null">
        DAILY_ABILITY = #{dailyAbility,jdbcType=DECIMAL},
      </if>
      <if test="evaluate != null">
        EVALUATE = #{evaluate,jdbcType=VARCHAR},
      </if>
      <if test="waterWellClassify != null">
        WATER_WELL_CLASSIFY = #{waterWellClassify,jdbcType=VARCHAR},
      </if>
      <if test="injectType != null">
        INJECT_TYPE = #{injectType,jdbcType=VARCHAR},
      </if>
      <if test="lessType != null">
        LESS_TYPE = #{lessType,jdbcType=VARCHAR},
      </if>
      <if test="sectionOpen != null">
        SECTION_OPEN = #{sectionOpen,jdbcType=CHAR},
      </if>
      <if test="diffWater != null">
        DIFF_WATER = #{diffWater,jdbcType=DECIMAL},
      </if>
      <if test="reasonId != null">
        REASON_ID = #{reasonId,jdbcType=VARCHAR},
      </if>
      <if test="measureId != null">
        MEASURE_ID = #{measureId,jdbcType=VARCHAR},
      </if>
      <if test="apply != null">
        APPLY = #{apply,jdbcType=CHAR},
      </if>
      <if test="layer != null">
        LAYER = #{layer,jdbcType=VARCHAR},
      </if>
      <if test="evaluateBasis != null">
        EVALUATE_BASIS = #{evaluateBasis,jdbcType=VARCHAR},
      </if>
      <if test="evaluateCalc != null">
        EVALUATE_CALC = #{evaluateCalc,jdbcType=VARCHAR},
      </if>
      <if test="evaluateEdit != null">
        EVALUATE_EDIT = #{evaluateEdit,jdbcType=VARCHAR},
      </if>
      <if test="remark != null">
        REMARK = #{remark,jdbcType=VARCHAR},
      </if>
      <if test="approved != null">
        APPROVED =  #{approved,jdbcType=CHAR},
      </if>
      <if test="editReason != null">
        EDIT_REASON = #{editReason,jdbcType=VARCHAR},
      </if>
    </set>
    where WELLNAME = #{wellname,jdbcType=VARCHAR}
      and "MONTH" = #{month,jdbcType=CHAR}
      and "SECTION" = #{section,jdbcType=INTEGER}
  </update>

  <select id="countApproved" resultType="int">
    select count(1)
    from INDI_WELL_SECTION_MONTH iwsm, water_well_basic wb
    where iwsm.wellname = wb.wellname
    and wb.unitcode_company = #{companyCode}
    and iwsm.month = #{month}
    and iwsm.approved = '1'
  </select>

</mapper>
