<?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.AdjustPlanDao">
  <sql id="Base_Column_List">
    cap.WELLNAME, MONTH, cap.UNITCODE, WORK_DATE, TUBE_TYPE_ID, WATER_DISPENSER, round(inject_time / 24, 2) inject_time,
    LAST_UNIT, LAST_DATE, PLAN_WATER1, PLAN_WATER2, PLAN_WATER3, PLAN_WATER4, PLAN_WATER5, 
    PLAN_WATER6, PLAN_WATER7, PURPOSE_ID, CREATED_ON, MODIFIED_ON, FINISH_DATE, UNFINISH_REASON, FAILURE_REASON,
    DISPENSING_NOTE, SOLID_INJECTION,
    (select flag_name from v_sys_flag where category_code = 'TUBE_TYPE' and flag_code = cap.TUBE_TYPE_ID) TUBE_TYPE_NAME,
    (select flag_name from sys_flag where category_code = 'ADJUST_PURPOSE' and flag_code = cap.PURPOSE_ID) PURPOSE_NAME
  </sql>
  <select id="selectList" parameterType="AdjustPlanParam" resultType="AdjustPlan">
    select
    <include refid="Base_Column_List" />
    , 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 CON_ADJUST_PLAN cap, water_well_basic wb, sys_unitinfo su, sys_inject_technology sit
    where cap.wellname = wb.wellname
    and wb.unitcode = su.unitcode
    and cap.TUBE_TYPE_ID = sit.DM
    and MONTH = #{month1}
    <if test="tubeType != null and tubeType != ''">
      and sit.zsgy = #{tubeType}
    </if>
    <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="lastDate != null ">
      and CAP.LAST_DATE = #{lastDate}
    </if>
    <if test="workStartDate != null ">
      and CAP.WORK_DATE >= #{workStartDate}
    </if>
    <if test="workEndDate != null ">
      and CAP.WORK_DATE &lt;= #{workEndDate}
    </if>
    <if test="wellname != null and wellname != ''">
      and wb.wellname = #{wellname}
    </if>
    <if test="tubeTypeId != null and tubeTypeId != ''">
      and cap.TUBE_TYPE_ID = #{tubeTypeId}
    </if>
    <if test="injectType != null and injectType != ''">
      and cap.TUBE_TYPE_ID in (select dm from sys_inject_technology where zsgy = #{injectType})
    </if>
    <if test="finished == 1">
      and to_char(cap.finish_date, 'yyyymm') = cap.month
    </if>
    <if test="finished == 0">
      and (to_char(cap.finish_date, 'yyyymm') != cap.month or cap.finish_date is null)
    </if>
    order by cap.wellname
  </select>

  <select id="selectGzlx" parameterType="GzlxParam" resultType="Gzlx">
    select distinct
      cap.TUBE_TYPE_ID, sit.DMMC
    from CON_ADJUST_PLAN cap
    join sys_inject_technology sit on cap.TUBE_TYPE_ID = sit.DM
    where cap.MONTH = #{month}
    and sit.zsgy = #{zsgy}
  </select>

  <select id="generateByWellname" resultType="AdjustPlan">
    select b.jh wellname, #{month} month,
    (select unitcode from well_basic where wellname = b.jh) unitcode,
    (select max(wgrq) from ys_ddh01 where jh = b.jh) work_date,
    b.zsfs tube_type_id,
    null water_dispenser,
    c.scsj inject_time,
    csdwmc last_unit,
    csrq last_date,
    b.cdrpzl1 plan_water1,
    b.cdrpzl2 plan_water2,
    b.cdrpzl3 plan_water3,
    b.cdrpzl4 plan_water4,
    b.cdrpzl5 plan_water5,
    b.cdrpzl6 plan_water6,
    b.cdrpzl7 plan_water7,
    b.rpzsl dispensing_note,
    sysdate created_on
    from
    (select * from ys_dba02 where jh = #{wellname} and rq = trunc(sysdate - 1)) b
    full join
    (select jh, sum(scsj) scsj from ys_dba02
      where jh = #{wellname}
      and rq >= trunc(add_months(sysdate, -1), 'month')
      and rq &lt;= trunc(last_day(add_months(sysdate, -1))) group by jh) c on b.jh = c.jh
    left join (select * from (select t.*, row_number() over(partition by jh order by csrq desc) rn from ys_dca09 t where jh = #{wellname}) where rn = 1) d on b.jh = d.jh
  </select>

  <select id="summary" resultType="AdjustPlanSummary">
    select t.unitcode, su.unitname, su.unitlevel, t.plan_num, t.finish_num,
           t.split_plan_num, t.split_finish_num, t.single_plan_num, t.single_finish_num,
    decode(t.plan_num, 0, null, round((t.finish_num / t.plan_num) * 100, 2)) finish_rate,
    decode(t.split_plan_num, 0, null, round((t.split_finish_num / t.split_plan_num) * 100, 2)) split_finish_rate,
    decode(t.single_plan_num, 0, null, round((t.single_finish_num / t.single_plan_num) * 100, 2)) single_finish_rate
    from
    (select wb.${unitColumn} unitcode,
    sum(1) plan_num,
    sum(case when to_char(cap.finish_date, 'yyyymm') = #{param.month} then 1 else 0 end) finish_num,
    sum(case when sit.zsgy = '分注' then 1 else 0 end) split_plan_num,
    sum(case when sit.zsgy = '分注' and to_char(cap.finish_date, 'yyyymm') = #{param.month} then 1 else 0 end) split_finish_num,
    sum(case when sit.zsgy = '单注' then 1 else 0 end) single_plan_num,
    sum(case when sit.zsgy = '单注' and to_char(cap.finish_date, 'yyyymm') = #{param.month} then 1 else 0 end) single_finish_num
    from con_adjust_plan cap, water_well_basic wb, sys_unitinfo su, sys_inject_technology sit
    where cap.wellname = wb.wellname
    and wb.unitcode = su.unitcode
    and cap.tube_type_id = sit.dm
    and month = #{param.month}
    and su.unitcode in (select unitcode from sys_unitinfo start with unitcode = #{param.unitcode} connect by prior unitcode = p_unitcode)
    group by wb.${unitColumn}) t, v_sys_unitinfo su
    where t.unitcode = su.unitcode
    order by su.unitorder
  </select>

  <select id="summaryType" resultType="AdjustPlanSummaryType">
    select #{param.month} month, t.unitcode, su.unitname, su.unitlevel, t.plan_num, t.finish_num,
    t.split_plan_num, t.split_finish_num, t.single_plan_num, t.single_finish_num
    from
    (select nvl(wb.${unitColumn}, #{param.unitcode}) unitcode,
    sum(1) plan_num,
    sum(case when to_char(cap.finish_date, 'yyyymm') = #{param.month} then 1 else 0 end) finish_num,
    sum(case when sit.zsgy = '分注' then 1 else 0 end) split_plan_num,
    sum(case when sit.zsgy = '分注' and to_char(cap.finish_date, 'yyyymm') = #{param.month} then 1 else 0 end) split_finish_num,
    sum(case when sit.zsgy = '单注' then 1 else 0 end) single_plan_num,
    sum(case when sit.zsgy = '单注' and to_char(cap.finish_date, 'yyyymm') = #{param.month} then 1 else 0 end) single_finish_num
    from con_adjust_plan cap, water_well_basic wb, sys_unitinfo su, sys_inject_technology sit
    where cap.wellname = wb.wellname
    and wb.unitcode = su.unitcode
    and cap.tube_type_id = sit.dm
    and month = #{param.month}
    and su.unitcode in (select unitcode from sys_unitinfo start with unitcode = #{param.unitcode} connect by prior unitcode = p_unitcode)
    group by rollup(wb.${unitColumn})) t, v_sys_unitinfo su
    where t.unitcode = su.unitcode
    order by su.unitorder
  </select>

  <select id="selectByPrimaryKey" parameterType="AdjustPlan" resultType="AdjustPlan">
    select
    <include refid="Base_Column_List" />
    from CON_ADJUST_PLAN cap
    where WELLNAME = #{wellname,jdbcType=VARCHAR}
      and MONTH = #{month,jdbcType=VARCHAR}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="AdjustPlan">
    delete from CON_ADJUST_PLAN
    where WELLNAME = #{wellname,jdbcType=VARCHAR}
      and MONTH = #{month,jdbcType=VARCHAR}
  </delete>
  <delete id="deleteByMonth" parameterType="String">
    delete from CON_ADJUST_PLAN
    where MONTH = #{value,jdbcType=VARCHAR}
  </delete>
  <insert id="insert" parameterType="AdjustPlan">
    insert into CON_ADJUST_PLAN (WELLNAME, MONTH, UNITCODE,
      WORK_DATE, TUBE_TYPE_ID, WATER_DISPENSER,
      INJECT_TIME, LAST_UNIT, LAST_DATE,
      PLAN_WATER1, PLAN_WATER2, PLAN_WATER3,
      PLAN_WATER4, PLAN_WATER5, PLAN_WATER6,
      PLAN_WATER7, PURPOSE_ID, CREATED_ON,
      MODIFIED_ON, FAILURE_REASON, UNFINISH_REASON,DISPENSING_NOTE, SOLID_INJECTION)
    values (#{wellname,jdbcType=VARCHAR}, #{month,jdbcType=VARCHAR}, #{unitcode,jdbcType=VARCHAR},
      #{workDate,jdbcType=TIMESTAMP}, #{tubeTypeId,jdbcType=VARCHAR}, #{waterDispenser,jdbcType=VARCHAR},
      #{injectTime,jdbcType=DECIMAL}, #{lastUnit,jdbcType=VARCHAR}, #{lastDate,jdbcType=TIMESTAMP},
      #{planWater1,jdbcType=DECIMAL}, #{planWater2,jdbcType=DECIMAL}, #{planWater3,jdbcType=DECIMAL},
      #{planWater4,jdbcType=DECIMAL}, #{planWater5,jdbcType=DECIMAL}, #{planWater6,jdbcType=DECIMAL},
      #{planWater7,jdbcType=DECIMAL}, #{purposeId,jdbcType=VARCHAR}, #{createdOn,jdbcType=TIMESTAMP},
      #{modifiedOn,jdbcType=TIMESTAMP}, #{failureReason,jdbcType=VARCHAR}, #{unfinishReason,jdbcType=VARCHAR},
      #{dispensingNote,jdbcType=VARCHAR}, #{solidInjection,jdbcType=VARCHAR})
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="AdjustPlan">
    update CON_ADJUST_PLAN
    <set>
      <if test="unitcode != null">
        UNITCODE = #{unitcode,jdbcType=VARCHAR},
      </if>
      <if test="workDate != null">
        WORK_DATE = #{workDate,jdbcType=TIMESTAMP},
      </if>
      <if test="tubeTypeId != null">
        TUBE_TYPE_ID = #{tubeTypeId,jdbcType=VARCHAR},
      </if>
      <if test="waterDispenser != null">
        WATER_DISPENSER = #{waterDispenser,jdbcType=VARCHAR},
      </if>
      <if test="injectTime != null">
        INJECT_TIME = #{injectTime,jdbcType=DECIMAL},
      </if>
      <if test="lastUnit != null">
        LAST_UNIT = #{lastUnit,jdbcType=VARCHAR},
      </if>
      <if test="lastDate != null">
        LAST_DATE = #{lastDate,jdbcType=TIMESTAMP},
      </if>
      <if test="planWater1 != null">
        PLAN_WATER1 = #{planWater1,jdbcType=DECIMAL},
      </if>
      <if test="planWater2 != null">
        PLAN_WATER2 = #{planWater2,jdbcType=DECIMAL},
      </if>
      <if test="planWater3 != null">
        PLAN_WATER3 = #{planWater3,jdbcType=DECIMAL},
      </if>
      <if test="planWater4 != null">
        PLAN_WATER4 = #{planWater4,jdbcType=DECIMAL},
      </if>
      <if test="planWater5 != null">
        PLAN_WATER5 = #{planWater5,jdbcType=DECIMAL},
      </if>
      <if test="planWater6 != null">
        PLAN_WATER6 = #{planWater6,jdbcType=DECIMAL},
      </if>
      <if test="planWater7 != null">
        PLAN_WATER7 = #{planWater7,jdbcType=DECIMAL},
      </if>
      <if test="purposeId != null">
        PURPOSE_ID = #{purposeId,jdbcType=VARCHAR},
      </if>
      <if test="failureReason != null">
        FAILURE_REASON = #{failureReason,jdbcType=VARCHAR},
      </if>
      <if test="unfinishReason != null">
        UNFINISH_REASON = #{unfinishReason,jdbcType=VARCHAR},
      </if>
      <if test="dispensingNote != null">
        DISPENSING_NOTE = #{dispensingNote,jdbcType=VARCHAR},
      </if>
      <if test="solidInjection != null">
        SOLID_INJECTION = #{solidInjection,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=VARCHAR}
  </update>
  <update id="updateByPrimaryKey" parameterType="AdjustPlan">
    update CON_ADJUST_PLAN
    set UNITCODE = #{unitcode,jdbcType=VARCHAR},
      WORK_DATE = #{workDate,jdbcType=TIMESTAMP},
      TUBE_TYPE_ID = #{tubeTypeId,jdbcType=VARCHAR},
      WATER_DISPENSER = #{waterDispenser,jdbcType=VARCHAR},
      INJECT_TIME = #{injectTime,jdbcType=DECIMAL},
      LAST_UNIT = #{lastUnit,jdbcType=VARCHAR},
      LAST_DATE = #{lastDate,jdbcType=TIMESTAMP},
      PLAN_WATER1 = #{planWater1,jdbcType=DECIMAL},
      PLAN_WATER2 = #{planWater2,jdbcType=DECIMAL},
      PLAN_WATER3 = #{planWater3,jdbcType=DECIMAL},
      PLAN_WATER4 = #{planWater4,jdbcType=DECIMAL},
      PLAN_WATER5 = #{planWater5,jdbcType=DECIMAL},
      PLAN_WATER6 = #{planWater6,jdbcType=DECIMAL},
      PLAN_WATER7 = #{planWater7,jdbcType=DECIMAL},
      PURPOSE_ID = #{purposeId,jdbcType=VARCHAR},
      FAILURE_REASON = #{failureReason,jdbcType=VARCHAR},
      UNFINISH_REASON = #{unfinishReason,jdbcType=VARCHAR},
      DISPENSING_NOTE = #{dispensingNote,jdbcType=VARCHAR},
      SOLID_INJECTION = #{solidInjection,jdbcType=VARCHAR},
      CREATED_ON = #{createdOn,jdbcType=TIMESTAMP},
      MODIFIED_ON = #{modifiedOn,jdbcType=TIMESTAMP}
    where WELLNAME = #{wellname,jdbcType=VARCHAR}
      and MONTH = #{month,jdbcType=VARCHAR}
  </update>
</mapper>