<?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.UnitMonthDao">
  <sql id="Base_Column_List">
    ium.UNITCODE,ium.MONTH,OPEN_WELL_RATE,SEPARATE_RATE,THIN_SEPARATE_RATE,NORMAL_RATE,OPEN_WELL_RATE1,SEPARATE_RATE1,
    NORMAL_RATE1,MORE_SECTION,LESS_SECTION,UNCLEAR_WELL,WATER_PUMP_EFF,DEVICE_POWER,WATER_QUALITY_RATE,ADJUST_RATE,
    FLUSH_RATE,OIL_QUALITY,SUSPENSION_QUALITY,PARTICLE_QUALITY,SINGLE_POWER,SUMMARY_DATE,
    PLAN_OPEN_WELL_RATE, PLAN_SEPARATE_RATE, PLAN_NORMAL_RATE,
    su.unitname
  </sql>

  <select id="selectListRange" parameterType="UnitMonthRangeParam"  resultType="UnitMonth">
    select
    <include refid="Base_Column_List" />
    from INDI_UNIT_MONTH ium, v_sys_unitinfo su
    where (su.unitcode = #{unitcode} or su.p_unitcode = #{unitcode})
    and ium.MONTH >= #{month1,jdbcType=CHAR}
    and ium.MONTH &lt;= #{month2,jdbcType=CHAR}
    and ium.unitcode = su.unitcode
    order by su.unitorder, ium.month
  </select>

  <select id="selectMonthRange" parameterType="UnitMonthRangeParam"  resultType="UnitMonth">
    select
    <include refid="Base_Column_List" />
    from INDI_UNIT_MONTH ium, v_sys_unitinfo su
    where (su.unitcode = #{unitcode} or su.p_unitcode = #{unitcode})
    and ium.MONTH >= #{month1,jdbcType=CHAR}
    and ium.MONTH &lt;= #{month2,jdbcType=CHAR}
    and ium.unitcode = su.unitcode
    order by ium.month, su.unitorder
  </select>

  <select id="selectListMonth" parameterType="UnitMonthParam"  resultType="UnitMonth">
    select
    ium.*, su.unitname
    from V_UNIT_MONTH ium, v_sys_unitinfo su
    where (su.unitcode = #{unitcode} or su.p_unitcode = #{unitcode})
    and ium.month = #{month,jdbcType=CHAR}
    and ium.unitcode = su.unitcode
    order by su.unitorder
  </select>

  <select id="selectUnitcode" parameterType="cn.com.victorysoft.business.sys.param.UnitcodeParam"
          resultType="String">
    select unitcode from
          (select * from sys_unitinfo where p_unitcode in
          (select unitcode from sys_unitinfo where unitname = #{cyc,jdbcType=VARCHAR}))
    where unitname = #{glq,jdbcType=VARCHAR}
  </select>

    <select id="selectCyc" parameterType="cn.com.victorysoft.business.sys.param.UnitcodeParam"
            resultType="String">
        select unitcode from sys_unitinfo where unitname = #{cyc,jdbcType=VARCHAR}
    </select>

  <select id="selectByPrimaryKey" parameterType="cn.com.victorysoft.business.indicator.entity.UnitMonth"
          resultType="UnitMonth">
    select *
    from INDI_UNIT_MONTH
    where UNITCODE = #{unitcode,jdbcType=VARCHAR}
    and MONTH = #{month,jdbcType=VARCHAR}
  </select>

  <insert id="update" parameterType="cn.com.victorysoft.business.indicator.entity.UnitMonth">
    UPDATE INDI_UNIT_MONTH SET
                               PLAN_OPEN_WELL_RATE = #{planOpenWellRate,jdbcType=DECIMAL},
                               PLAN_SEPARATE_RATE = #{planSeparateRate,jdbcType=DECIMAL},
                               PLAN_NORMAL_RATE = #{planNormalRate,jdbcType=DECIMAL}
    WHERE UNITCODE = #{unitcode,jdbcType=VARCHAR} and MONTH = #{month,jdbcType=TIMESTAMP}
  </insert>

  <insert id="insert" parameterType="cn.com.victorysoft.business.indicator.entity.UnitMonth">
    insert into INDI_UNIT_MONTH (UNITCODE, MONTH, PLAN_OPEN_WELL_RATE, PLAN_SEPARATE_RATE, PLAN_NORMAL_RATE)
    values (#{unitcode,jdbcType=VARCHAR}, #{month,jdbcType=TIMESTAMP},
            #{planOpenWellRate,jdbcType=DECIMAL},#{planSeparateRate,jdbcType=DECIMAL},#{planNormalRate,jdbcType=DECIMAL})
  </insert>

  <select id="selectList12" parameterType="UnitMonthRangeParam"  resultType="UnitMonth">
    select
    round(avg(OPEN_WELL_RATE), 2) OPEN_WELL_RATE,
    round(avg(SEPARATE_RATE), 2) SEPARATE_RATE,
    round(avg(THIN_SEPARATE_RATE), 2) THIN_SEPARATE_RATE,
    round(avg(NORMAL_RATE), 2) NORMAL_RATE,
    round(avg(OPEN_WELL_RATE1), 2) OPEN_WELL_RATE1,
    round(avg(SEPARATE_RATE1), 2) SEPARATE_RATE1,
    round(avg(NORMAL_RATE1), 2) NORMAL_RATE1,
    round(avg(MORE_SECTION), 0) MORE_SECTION,
    round(avg(LESS_SECTION), 0) LESS_SECTION,
    round(avg(UNCLEAR_WELL), 0) UNCLEAR_WELL,
    round(avg(WATER_PUMP_EFF), 2) WATER_PUMP_EFF,
    round(avg(DEVICE_POWER), 2) DEVICE_POWER,
    round(avg(WATER_QUALITY_RATE), 2) WATER_QUALITY_RATE,
    round(avg(ADJUST_RATE), 2) ADJUST_RATE,
    round(avg(FLUSH_RATE), 2) FLUSH_RATE,
    round(avg(OIL_QUALITY), 2) OIL_QUALITY,
    round(avg(SUSPENSION_QUALITY), 2) SUSPENSION_QUALITY,
    round(avg(PARTICLE_QUALITY), 2) PARTICLE_QUALITY,
    round(avg(SINGLE_POWER), 2) SINGLE_POWER
    from INDI_UNIT_MONTH ium, v_sys_unitinfo su
    where (su.unitcode = #{unitcode})
    and ium.MONTH >= #{month1,jdbcType=CHAR}
    and ium.MONTH &lt;= #{month2,jdbcType=CHAR}
    and ium.unitcode = su.unitcode
  </select>

  <select id="selectList1" parameterType="UnitMonthRangeParam"  resultType="UnitMonth">
    select
    <include refid="Base_Column_List" />
    from INDI_UNIT_MONTH ium, v_sys_unitinfo su
    where (su.unitcode = #{unitcode})
    and ium.MONTH >= #{month1,jdbcType=CHAR}
    and ium.MONTH &lt;= #{month2,jdbcType=CHAR}
    and ium.unitcode = su.unitcode
    order by su.unitorder
  </select>

  <select id="selectUnitMonth" parameterType="UnitMonthParam"  resultType="UnitMonth">
    select
    ium.*, su.unitname
    from V_UNIT_MONTH ium, v_sys_unitinfo su
    where (su.unitcode = #{unitcode})
    and ium.MONTH = #{month,jdbcType=CHAR}
    and ium.unitcode = su.unitcode
    order by su.unitorder
  </select>

  <update id="syncUnitMonth" parameterType="String">
    merge into indi_unit_month t
    using (select * from indi_unit_month_view where month = #{month}) v
    on (t.unitcode = v.unitcode and t.month = v.month)
      WHEN MATCHED THEN
        update
            set open_well_rate = v.open_well_rate,
                separate_rate = v.separate_rate,
                normal_rate = v.normal_rate,
                open_well_rate1 = v.open_well_rate1,
                separate_rate1 = v.separate_rate1,
                normal_rate1 = v.normal_rate1,
                more_section = v.more_section,
                less_section = v.less_section,
                unclear_well = v.unclear_well,
                water_pump_eff = v.water_pump_eff,
                device_power = v.device_power,
                water_quality_rate = v.water_quality_rate,
                adjust_rate = v.adjust_rate,
                flush_rate = v.flush_rate,
                summary_date = v.summary_date,
                work_total_well = v.work_total_well,
                separate_well = v.separate_well,
                oil_quality = v.oil_quality,
                suspension_quality = v.suspension_quality,
                particle_quality = v.particle_quality,
                single_power = v.single_power,
                thin_separate_rate = v.thin_separate_rate
      WHEN NOT MATCHED THEN
        insert
          (unitcode, month, open_well_rate, separate_rate, normal_rate, open_well_rate1, separate_rate1, normal_rate1, more_section, less_section, unclear_well, water_pump_eff, device_power, water_quality_rate, adjust_rate, flush_rate, summary_date, work_total_well, separate_well, oil_quality, suspension_quality, particle_quality, single_power, thin_separate_rate)
        values
          (v.unitcode, v.month, v.open_well_rate, v.separate_rate, v.normal_rate, v.open_well_rate1, v.separate_rate1, v.normal_rate1, v.more_section, v.less_section, v.unclear_well, v.water_pump_eff, v.device_power, v.water_quality_rate, v.adjust_rate, v.flush_rate, v.summary_date, v.work_total_well, v.separate_well, v.oil_quality, v.suspension_quality, v.particle_quality, v.single_power, v.thin_separate_rate)
  </update>

</mapper>
