<?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.calc.dao.AdjustDataSyncDao">

  <insert id="generateAdjustPlan" parameterType="Date">
    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,
    created_on, purpose_id, dispensing_note, solid_injection)
    select a.jh wellname, to_char(add_months(#{value}, 1), 'yyyymm') month,
    (select unitcode from well_basic where wellname = a.jh) unitcode,
    (select max(wgrq) from ys_ddh01 where jh = a.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,
    sysdate created_on,
    '1',
    b.RPZSL dispensing_note,
    b.RZSL solid_injection
    from
    (select * from (select t.*, row_number() over(partition by jh order by csrq desc) rn from ys_dca09 t where csrq &lt; #{value}) t
    where rn = 1 and csrq &lt; trunc(add_months(#{value}, -1),'mm')) a,
    (select * from ys_dba02 where rq = trunc(#{value} - 1)) b,
    (select jh, sum(scsj) scsj from ys_dba02 where rq >= trunc(#{value}, 'mm') and rq &lt;= #{value} group by jh) c
    where a.jh = b.jh and a.jh = c.jh and c.scsj > 24
    and b.zsfs in (select dm from sys_inject_technology where txsg = '0')
  </insert>

  <update id="finishAdjustPlan">
    update con_adjust_plan t
    set finish_date = (select min(csrq) from ys_dca09 where jh = t.wellname and csrq > t.last_date)
    where exists (select 1 from ys_dca09 where jh = t.wellname and csrq > t.last_date)
    and finish_date is null
  </update>

</mapper>