<?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.MoreSummaryDao">
  <sql id="Base_Column_List">
    ims.UNITCODE, "MONTH", REASON_ID, ORIGINAL_WELL, ORIGINAL_SECTION, ORIGINAL_WATER,
    INCREASE_WELL, INCREASE_SECTION, INCREASE_WATER, REDUCE_WELL, REDUCE_SECTION, REDUCE_WATER, 
    WELL, "SECTION", WATER, SUMMARY_DATE, su.unitname,
    (select flag_name from sys_flag where category_code = 'MORE_REASON' and flag_code = ims.reason_id) reason_name
  </sql>

  <select id="selectUnit" parameterType="UnitMonthParam" resultType="MoreSummary">
    select
    <include refid="Base_Column_List" />
    from INDI_MORE_SUMMARY ims, V_SYS_UNITINFO su
    where "MONTH" = #{month,jdbcType=CHAR}
    and ims.UNITCODE = su.UNITCODE
    and su.unitcode= #{unitcode,jdbcType=VARCHAR}
  </select>

  <select id="selectChildren" parameterType="UnitMonthParam" resultType="MoreSummary">
    select
    <include refid="Base_Column_List" />
    from INDI_MORE_SUMMARY ims, V_SYS_UNITINFO su
    where "MONTH" = #{month,jdbcType=CHAR}
    and ims.UNITCODE = su.UNITCODE
    and su.p_unitcode= #{unitcode,jdbcType=VARCHAR}
    order by su.unitorder
  </select>

  <select id="selectRange" parameterType="UnitMonthRangeParam" resultType="MoreSummary">
    select
    <include refid="Base_Column_List" />
    from INDI_MORE_SUMMARY ims, V_SYS_UNITINFO su
    where "MONTH" >= #{month1,jdbcType=CHAR}
    and "MONTH" &lt;= #{month2,jdbcType=CHAR}
    and ims.UNITCODE = su.UNITCODE
    and su.UNITCODE = #{unitcode,jdbcType=VARCHAR}
    order by ims."MONTH"
  </select>

  <select id="selectByPrimaryKey" parameterType="MoreSummary" resultType="MoreSummary">
    select 
    <include refid="Base_Column_List" />
    from INDI_MORE_SUMMARY ims, V_SYS_UNITINFO su
    where UNITCODE = #{unitcode,jdbcType=VARCHAR}
      and "MONTH" = #{month,jdbcType=CHAR}
      and REASON_ID = #{reasonId,jdbcType=VARCHAR}
      and ims.UNITCODE = su.UNITCODE
  </select>

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

  <delete id="deleteByPrimaryKey" parameterType="MoreSummary">
    delete from INDI_MORE_SUMMARY
    where UNITCODE = #{unitcode,jdbcType=VARCHAR}
      and "MONTH" = #{month,jdbcType=CHAR}
      and REASON_ID = #{reasonId,jdbcType=VARCHAR}
  </delete>

  <insert id="summaryStation">
    insert into INDI_MORE_SUMMARY (UNITCODE, "MONTH", REASON_ID,
      ORIGINAL_WELL, ORIGINAL_SECTION, ORIGINAL_WATER,
      WELL, "SECTION", WATER,
      SUMMARY_DATE)
    select t.unitcode,
           #{month} month,
           t.reason_id,
           b.well ORIGINAL_WELL,
           b.section ORIGINAL_SECTION,
           b.water ORIGINAL_WATER,
           a.well,
           a.section,
           a.water,
           sysdate
      from
      (select b.unitcode, a.reason_id from
        (select 'all' reason_id from dual
        union select flag_code from sys_flag t where category_code = 'MORE_REASON') a,
        (select unitcode from sys_unitinfo t where unitlevel = 4) b) t
      left join (select unitcode,
                   max(month) month,
                   nvl(parse_reason_id, 'all') reason_id,
                   count(distinct wellname) well,
                   count(1) section,
                   sum(diff_water) water
              from (select t.*, nvl(reason_id, 'null') parse_reason_id from indi_well_section_month t)
             where month = #{month}
               and EVALUATE = 'MORE'
               and reason_id is not null
             group by unitcode, rollup(parse_reason_id)) a
      on t.unitcode = a.unitcode
      and t.reason_id = a.reason_id
      left join (select * from indi_more_summary where month = #{prevMonth}) b
      on t.unitcode = b.unitcode
      and t.reason_id = b.reason_id
      where (a.section is not null or b.section is not null)
  </insert>

  <update id="summaryStationIncrease">
    merge into indi_more_summary t
    using (select unitcode,
                  max(month) month,
                  nvl(parse_reason_id, 'all') reason_id,
                  count(distinct wellname) well,
                  count(1) section,
                  sum(diff_water) water
             from (select t.*, nvl(reason_id, 'null') parse_reason_id from indi_well_section_month t) t
            where month = #{month}
              and EVALUATE = 'MORE'
              and (
                exists (select 1
                     from indi_well_section_month
                    where month = #{prevMonth}
                      and (EVALUATE is null or EVALUATE != 'MORE')
--                       and EVALUATE = 'NORMAL'
                      and wellname = t.wellname
                      and section = t.section)
                or not exists (select 1
                     from indi_well_section_month
                    where month = #{prevMonth}
                      and wellname = t.wellname
                      and section = t.section)
              )
            group by unitcode, rollup(parse_reason_id)) tmp
    on (t.unitcode = tmp.unitcode and t.month = tmp.month and t.reason_id = tmp.reason_id)
    when matched then
      update
         set t.INCREASE_WELL    = tmp.well,
             t.INCREASE_SECTION = tmp.section,
             t.INCREASE_WATER   = tmp.water
  </update>

  <update id="summaryStationReduce">
    merge into indi_more_summary t
    using (select unitcode,
                  max(month) month,
                  nvl(parse_reason_id, 'all') reason_id,
                  count(distinct wellname) well,
                  count(1) section,
                  sum(diff_water) water
             from (select t.*, nvl(reason_id, 'null') parse_reason_id from indi_well_section_month t) t
            where month = #{prevMonth}
              and EVALUATE = 'MORE'
              and (
                exists (select 1
                     from indi_well_section_month
                    where month = #{month}
                      and (EVALUATE is null or EVALUATE != 'MORE')
--                       and EVALUATE = 'NORMAL'
                      and wellname = t.wellname
                      and section = t.section)
                or not exists (select 1
                     from indi_well_section_month
                    where month = #{month}
                      and wellname = t.wellname
                      and section = t.section)
              )
            group by unitcode, rollup(parse_reason_id)) tmp
    on (t.unitcode = tmp.unitcode
        and t.month = #{month}
        and t.reason_id = tmp.reason_id)
    when matched then
      update
         set t.REDUCE_WELL    = tmp.well,
             t.REDUCE_SECTION = tmp.section,
             t.REDUCE_WATER   = tmp.water
  </update>

  <sql id="Insert_Sql">
    insert into INDI_MORE_SUMMARY (UNITCODE, "MONTH", REASON_ID,
      ORIGINAL_WELL, ORIGINAL_SECTION, ORIGINAL_WATER,
      INCREASE_WELL, INCREASE_SECTION, INCREASE_WATER,
      REDUCE_WELL, REDUCE_SECTION, REDUCE_WATER,
      WELL, "SECTION", WATER,
      SUMMARY_DATE)
  </sql>

  <sql id="Summary_Parent_Sql">
    <include refid="Insert_Sql" />
    select b.p_unitcode, max(a.month), a.reason_id,
    sum(ORIGINAL_WELL), sum(ORIGINAL_SECTION), sum(ORIGINAL_WATER),
    sum(INCREASE_WELL), sum(INCREASE_SECTION), sum(INCREASE_WATER),
    sum(REDUCE_WELL), sum(REDUCE_SECTION), sum(REDUCE_WATER),
    sum(WELL), sum(SECTION), sum(WATER), sysdate
    from indi_more_summary a, sys_unitinfo b
    where a.unitcode = b.unitcode
    and a.month = #{month}
  </sql>

  <insert id="summaryArea" parameterType="String">
    <include refid="Summary_Parent_Sql" />
    and b.unitlevel = 4
    group by b.p_unitcode, a.reason_id
  </insert>

  <insert id="summaryCompany" parameterType="String">
    <include refid="Summary_Parent_Sql" />
    and b.unitlevel = 3
    group by b.p_unitcode, a.reason_id
  </insert>

  <insert id="summaryGroup" parameterType="String">
    <include refid="Summary_Parent_Sql" />
    and b.unitlevel = 2
    group by b.p_unitcode, a.reason_id
  </insert>

  <insert id="insert" parameterType="MoreSummary">
    <include refid="Insert_Sql" />
    values (#{unitcode,jdbcType=VARCHAR}, #{month,jdbcType=CHAR}, #{reasonId,jdbcType=VARCHAR},
      #{originalWell,jdbcType=DECIMAL}, #{originalSection,jdbcType=DECIMAL}, #{originalWater,jdbcType=DECIMAL}, 
      #{increaseWell,jdbcType=DECIMAL}, #{increaseSection,jdbcType=DECIMAL}, #{increaseWater,jdbcType=DECIMAL}, 
      #{reduceWell,jdbcType=DECIMAL}, #{reduceSection,jdbcType=DECIMAL}, #{reduceWater,jdbcType=DECIMAL}, 
      #{well,jdbcType=DECIMAL}, #{section,jdbcType=DECIMAL}, #{water,jdbcType=DECIMAL},
      #{summaryDate,jdbcType=TIMESTAMP})
  </insert>

  <update id="updateByPrimaryKeySelective" parameterType="MoreSummary">
    update INDI_MORE_SUMMARY
    <set>
      <if test="originalWell != null">
        ORIGINAL_WELL = #{originalWell,jdbcType=DECIMAL},
      </if>
      <if test="originalSection != null">
        ORIGINAL_SECTION = #{originalSection,jdbcType=DECIMAL},
      </if>
      <if test="originalWater != null">
        ORIGINAL_WATER = #{originalWater,jdbcType=DECIMAL},
      </if>
      <if test="increaseWell != null">
        INCREASE_WELL = #{increaseWell,jdbcType=DECIMAL},
      </if>
      <if test="increaseSection != null">
        INCREASE_SECTION = #{increaseSection,jdbcType=DECIMAL},
      </if>
      <if test="increaseWater != null">
        INCREASE_WATER = #{increaseWater,jdbcType=DECIMAL},
      </if>
      <if test="reduceWell != null">
        REDUCE_WELL = #{reduceWell,jdbcType=DECIMAL},
      </if>
      <if test="reduceSection != null">
        REDUCE_SECTION = #{reduceSection,jdbcType=DECIMAL},
      </if>
      <if test="reduceWater != null">
        REDUCE_WATER = #{reduceWater,jdbcType=DECIMAL},
      </if>
      <if test="well != null">
        WELL = #{well,jdbcType=DECIMAL},
      </if>
      <if test="section != null">
        "SECTION" = #{section,jdbcType=DECIMAL},
      </if>
      <if test="water != null">
        WATER = #{water,jdbcType=DECIMAL},
      </if>
      <if test="summaryDate != null">
        SUMMARY_DATE = #{summaryDate,jdbcType=TIMESTAMP},
      </if>
    </set>
    where UNITCODE = #{unitcode,jdbcType=VARCHAR}
      and "MONTH" = #{month,jdbcType=CHAR}
      and REASON_ID = #{reasonId,jdbcType=VARCHAR}
  </update>

  <update id="updateByPrimaryKey" parameterType="MoreSummary">
    update INDI_MORE_SUMMARY
    set ORIGINAL_WELL = #{originalWell,jdbcType=DECIMAL},
      ORIGINAL_SECTION = #{originalSection,jdbcType=DECIMAL},
      ORIGINAL_WATER = #{originalWater,jdbcType=DECIMAL},
      INCREASE_WELL = #{increaseWell,jdbcType=DECIMAL},
      INCREASE_SECTION = #{increaseSection,jdbcType=DECIMAL},
      INCREASE_WATER = #{increaseWater,jdbcType=DECIMAL},
      REDUCE_WELL = #{reduceWell,jdbcType=DECIMAL},
      REDUCE_SECTION = #{reduceSection,jdbcType=DECIMAL},
      REDUCE_WATER = #{reduceWater,jdbcType=DECIMAL},
      WELL = #{well,jdbcType=DECIMAL},
      "SECTION" = #{section,jdbcType=DECIMAL},
      WATER = #{water,jdbcType=DECIMAL},
      SUMMARY_DATE = #{summaryDate,jdbcType=TIMESTAMP}
    where UNITCODE = #{unitcode,jdbcType=VARCHAR}
      and "MONTH" = #{month,jdbcType=CHAR}
      and REASON_ID = #{reasonId,jdbcType=VARCHAR}
  </update>
</mapper>