<?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.SectionRateSummaryDao">
  <sql id="Base_Column_List">
    isrs.UNITCODE, "MONTH", WELL_TYPE, TOTAL_WELL, OPEN_WELL, TOTAL_SECTION, OPEN_SECTION, CHECK_WELL,
    CHECK_SECTION, NORMAL_SECTION, NORMAL_RATE, MORE_SECTION, MORE_WATER, LESS_NOZZLE_SECTION,
    LESS_NOZZLE_WATER, LESS_BIG_SECTION, LESS_BIG_WATER, UNCLEAR_WELL, UNCLEAR_SECTION,
    NO_CHECK_WELL, NO_CHECK_SECTION, SUMMARY_DATE, su.unitname
  </sql>

  <select id="selectUnitChildren" parameterType="WellSectionMonthParam" resultType="SectionRateSummary">
    select t.*, nvl(su.unitname, '合计') unitname,
    round(t.normal_section / decode(t.check_section, 0, null, t.check_section) * 100, 2) normal_rate
    from
    (select iwsm.month, wb.${unitColumn} unitcode,
    <include refid="Summary_Sql" />
    <include refid="Condition_Sql" />
    group by iwsm.month, rollup(wb.${unitColumn})) t,
    V_SYS_UNITINFO su
    where t.unitcode = su.unitcode(+)
    and (t.unitcode is null or t.unitcode != '-999')
    order by t.month, su.unitorder nulls first
  </select>

  <select id="selectRange" parameterType="RangeWellType" resultType="SectionRateSummary">
    select t.*, su.unitname,
    round(t.normal_section / decode(t.check_section, 0, null, t.check_section) * 100, 2) normal_rate
    from
    (select iwsm.month, wb.${searchUnitColumn} unitcode,
    <include refid="Summary_Sql" />
    <include refid="Condition_Sql" />
    group by iwsm.month, wb.${searchUnitColumn}) t,
    V_SYS_UNITINFO su
    where t.unitcode = su.unitcode(+)
    order by t.month
  </select>

  <select id="summaryByInjectType" parameterType="WellSectionMonthParam" resultType="SectionRateSummary">
    select t.*, nvl(sf.flag_name, '合计') inject_type_name,
    round(t.normal_section / decode(t.check_section, 0, null, t.check_section) * 100, 2) normal_rate
    from
    (select iwsm.month, iwsm.inject_type,
    <include refid="Summary_Sql" />
    <include refid="Condition_Sql" />
    and iwsm.inject_type is not null
    group by iwsm.month, rollup(iwsm.inject_type)) t,
    (select * from sys_flag where category_code = 'INJECT_TYPE') sf
    where t.inject_type = sf.flag_code(+)
    order by t.month, sf.flag_order nulls last
  </select>

  <select id="summarySingle" parameterType="WellSectionMonthParam" resultType="SectionRateSummary">
    select t.*, nvl(su.unitname, '合计') unitname,
    round(t.normal_section / decode(t.check_section, 0, null, t.check_section) * 100, 2) normal_rate
    from
    (select iwsm.month, wb.${unitColumn} unitcode,
    <include refid="Summary_Sql" />
    <include refid="Condition_Sql" />
    and iwsm.inject_type = 'DAN_ZHU'
    group by iwsm.month, rollup(wb.${unitColumn})) t,
    sys_unitinfo su
    where t.unitcode = su.unitcode(+)
    and (t.unitcode is null or t.unitcode != '-999')
    order by t.month, su.unitorder nulls first
  </select>

  <select id="summaryBlock" parameterType="WellSectionMonthParam" resultType="SectionRateSummary">
    select t.*, nvl(sb.blockname, '合计') blockname,
    round(t.normal_section / decode(t.check_section, 0, null, t.check_section) * 100, 2) normal_rate
    from
    (select iwsm.month, wb.blockcode,
    <include refid="Summary_Sql" />
    <include refid="Condition_Sql" />
    and wb.blockcode in (select blockcode from sys_blockinfo)
    group by iwsm.month, rollup(wb.blockcode)) t
    left join sys_blockinfo sb on t.blockcode = sb.blockcode
    order by t.month, sb.blockcode nulls first
  </select>

  <select id="summarySplit" parameterType="WellSectionMonthParam" resultType="SectionRateSummary">
    select t.*, nvl(su.unitname, '合计') unitname,
    round(t.normal_section / decode(t.check_section, 0, null, t.check_section) * 100, 2) normal_rate
    from
    (select iwsm.month, wb.${unitColumn} unitcode,
    <include refid="Summary_Sql" />
    <include refid="Condition_Sql" />
    and iwsm.inject_type != 'DAN_ZHU'
    group by iwsm.month, rollup(wb.${unitColumn})) t,
    sys_unitinfo su
    where t.unitcode = su.unitcode(+)
    and (t.unitcode is null or t.unitcode != '-999')
    order by t.month, su.unitorder nulls first
  </select>

  <sql id="Condition_Sql">
    <if test="blockcode != null and blockcode != ''">
      and wb.blockcode = #{blockcode}
    </if>
    <if test="unitcode != null and unitcode != ''">
      and wb.${searchUnitColumn} = #{unitcode}
    </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="blockcodes != null and blockcodes.size > 0">
      and wb.blockcode in
      <foreach collection="blockcodes" item="item" open="(" separator="," close=")">
        #{item}
      </foreach>
    </if>
  </sql>

  <sql id="Insert_Sql">
    insert into INDI_SECTION_RATE_SUMMARY (UNITCODE, "MONTH", WELL_TYPE, TOTAL_WELL,
      OPEN_WELL, TOTAL_SECTION, OPEN_SECTION,
      CHECK_WELL, CHECK_SECTION, NORMAL_SECTION,
      NORMAL_RATE, MORE_SECTION, MORE_WATER,
      LESS_NOZZLE_SECTION, LESS_NOZZLE_WATER, LESS_BIG_SECTION,
      LESS_BIG_WATER, UNCLEAR_WELL, UNCLEAR_SECTION,
      NO_CHECK_WELL, NO_CHECK_SECTION, SUMMARY_DATE
      )
  </sql>

  <sql id="Summary_Sql">
    count(distinct iwsm.wellname) total_well,
    count(distinct (case when iwsm.section_open = '1' then iwsm.wellname end)) open_well,
    count(distinct (case when nvl(iwsm.NOZZLE, '_') != '方停' then iwsm.well_section end)) total_section,
    count(distinct (case when iwsm.section_open = '1' and nvl(iwsm.NOZZLE, '_') != '方停' then iwsm.well_section end)) open_section,
    count(distinct (case when iwsm.evaluate is not null and iwsm.evaluate != 'NO_CHECK' then iwsm.wellname end)) check_well ,
    count(distinct (case when iwsm.evaluate is not null and iwsm.evaluate != 'NO_CHECK' and nvl(iwsm.NOZZLE, '_') != '方停' then iwsm.well_section end)) check_section,
    count(distinct (case when iwsm.evaluate = 'NORMAL' and nvl(iwsm.NOZZLE, '_') != '方停' then iwsm.well_section end)) normal_section,
    count(distinct (case when iwsm.evaluate = 'MORE' and nvl(iwsm.NOZZLE, '_') != '方停' then iwsm.well_section end)) more_section,
    sum(case when iwsm.evaluate = 'MORE' then iwsm.diff_water end) more_water,
    count(distinct (case when iwsm.evaluate = 'LESS' and iwsm.less_type = 'NOZZLE' and nvl(iwsm.NOZZLE, '_') != '方停' then iwsm.well_section end)) less_nozzle_section,
    abs(sum(case when iwsm.evaluate = 'LESS' and iwsm.less_type = 'NOZZLE' then iwsm.diff_water end)) less_nozzle_water,
    count(distinct (case when iwsm.evaluate = 'LESS' and iwsm.less_type = 'BIG' and nvl(iwsm.NOZZLE, '_') != '方停' then iwsm.well_section end)) less_big_section,
    abs(sum(case when iwsm.evaluate = 'LESS' and iwsm.less_type = 'BIG' then iwsm.diff_water end)) less_big_water,
    count(distinct (case when iwsm.evaluate = 'UNCLEAR' then iwsm.wellname end)) unclear_well,
    count(distinct (case when iwsm.evaluate = 'UNCLEAR' and nvl(iwsm.NOZZLE, '_') != '方停' then iwsm.well_section end)) unclear_section,
    count(distinct (case when iwsm.evaluate = 'NO_CHECK' then iwsm.wellname end)) no_check_well,
    count(distinct (case when iwsm.evaluate = 'NO_CHECK' and nvl(iwsm.NOZZLE, '_') != '方停' then iwsm.well_section end)) no_check_section
    from
    (select t.*, t.wellname || t.section well_section from INDI_WELL_SECTION_MONTH t) iwsm,
    v_water_well_basic wb
    where iwsm.wellname = wb.wellname
  </sql>

  <sql id="Summary_Insert_Sql">
    <include refid="Insert_Sql" />
    select t.unitcode, t.month, nvl(t.well_type, 'all') well_type, t.total_well, t.open_well,
    t.total_section, t.open_section, t.check_well, t.check_section, t.normal_section,
    round(t.normal_section / decode(t.check_section, 0, null, t.check_section) * 100, 1) normal_rate,
    t.more_section, t.more_water, t.less_nozzle_section, t.less_nozzle_water, t.less_big_section, t.less_big_water,
    t.unclear_well, t.unclear_section, t.no_check_well, t.no_check_section, sysdate
    from
    (select wb.${unitColumn} unitcode, max(iwsm.month) month, iwsm.well_type,
    <include refid="Summary_Sql" />
  </sql>

  <insert id="summary">
    <include refid="Summary_Insert_Sql" />
    and iwsm.month = #{month}
    and wb.${unitColumn} is not null
    group by wb.${unitColumn}, rollup(iwsm.well_type)) t
  </insert>

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

  <insert id="insert" parameterType="SectionRateSummary">
    <include refid="Insert_Sql" />
    values (#{unitcode,jdbcType=VARCHAR}, #{month,jdbcType=CHAR}, #{wellType,jdbcType=VARCHAR}, #{totalWell,jdbcType=DECIMAL},
      #{openWell,jdbcType=DECIMAL}, #{totalSection,jdbcType=DECIMAL}, #{openSection,jdbcType=DECIMAL},
      #{checkWell,jdbcType=DECIMAL}, #{checkSection,jdbcType=DECIMAL}, #{normalSection,jdbcType=DECIMAL},
      #{normalRate,jdbcType=DECIMAL}, #{moreSection,jdbcType=DECIMAL}, #{moreWater,jdbcType=DECIMAL},
      #{lessNozzleSection,jdbcType=DECIMAL}, #{lessNozzleWater,jdbcType=DECIMAL}, #{lessBigSection,jdbcType=DECIMAL},
      #{lessBigWater,jdbcType=DECIMAL}, #{unclearWell,jdbcType=DECIMAL}, #{unclearSection,jdbcType=DECIMAL},
      #{noCheckWell,jdbcType=DECIMAL}, #{noCheckSection,jdbcType=DECIMAL}, #{summaryDate,jdbcType=TIMESTAMP}
      )
  </insert>

</mapper>
