<?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.AbnormalDataSyncDao">

  <insert id="syncAbnormalWell" parameterType="Date">
    insert into con_abnormal_well
    (wellname, record_date, unitcode, open_time, last_open_time, trunk_pressure, oil_pressure, casing_pressure, plan_daily_water, daily_water, oil_pressure_diff, water_diff, water_diff_rate, close_reason, type_id, created_on)
    select t.*,
    case
    when (OIL_PRESSURE_DIFF > 2 or OIL_PRESSURE_DIFF &lt; -2) and (WATER_DIFF_RATE > 20 or WATER_DIFF_RATE &lt; -20) then 'BOTH'
    when (OIL_PRESSURE_DIFF > 2 or OIL_PRESSURE_DIFF &lt; -2) and not (WATER_DIFF_RATE > 20 or WATER_DIFF_RATE &lt; -20) then 'PRESSURE'
    when not (OIL_PRESSURE_DIFF > 2 or OIL_PRESSURE_DIFF &lt; -2) and (WATER_DIFF_RATE > 20 or WATER_DIFF_RATE &lt; -20) then 'WATER'
    end TYPE_ID,
    sysdate
    from
    (select
    a.jh WELLNAME,
    a.rq RECORD_DATE,
    a.dwdm UNITCODE,
    a.scsj OPEN_TIME,
    b.scsj LAST_OPEN_TIME,
    a.gxyl TRUNK_PRESSURE,
    a.yy OIL_PRESSURE,
    a.ty CASING_PRESSURE,
    a.rpzsl PLAN_DAILY_WATER,
    a.rzsl DAILY_WATER,
    a.yy - c.yy OIL_PRESSURE_DIFF,
    a.rzsl - c.rzsl WATER_DIFF,
    decode(c.rzsl, 0, null, round((a.rzsl - c.rzsl)/ c.rzsl * 100, 2)) WATER_DIFF_RATE,
    (select dmmc from fla15 where dm = a.bzdm) CLOSE_REASON
    from
    (select * from ys_dba02 where rq = #{value}) a,
    (select * from ys_dba02 where rq = #{value} - 1) b,
    (select jh, avg(yy) yy, avg(rzsl) rzsl from ys_dba02 where rq >= #{value} - 30 and rq &lt;= #{value} group by jh) c
    where a.jh = b.jh(+)
    and a.jh = c.jh(+)
    and a.dwdm is not null) t
    where OIL_PRESSURE_DIFF > 2 or OIL_PRESSURE_DIFF &lt; -2
    or WATER_DIFF_RATE > 20 or WATER_DIFF_RATE &lt; -20
  </insert>

  <insert id="summaryAbnormalUnit">
    insert into con_abnormal_unit
    (unitcode, record_date, total_well, open_well, daily_water, pressure, water_abnormal, pressure_abnormal,
    open_well_diff, daily_water_diff, water_abnormal_diff, pressure_abnormal_diff, summary_date)
    select a.*,
    a.OPEN_WELL - b.OPEN_WELL OPEN_WELL_DIFF,
    a.DAILY_WATER - b.DAILY_WATER DAILY_WATER_DIFF,
    a.WATER_ABNORMAL - b.WATER_ABNORMAL WATER_ABNORMAL_DIFF,
    a.PRESSURE_ABNORMAL - b.PRESSURE_ABNORMAL PRESSURE_ABNORMAL_DIFF,
    sysdate
    from
    (select
    wb.${unitColumn} unitcode,
    a.rq record_date,
    count(1) TOTAL_WELL,
    sum(case when a.scsj > 0 then 1 end) OPEN_WELL,
    round(sum(a.rzsl), 2) DAILY_WATER,
    round(avg(a.yy), 2) PRESSURE,
    sum(case when b.type_id = 'WATER' or b.type_id = 'BOTH' then 1 end) WATER_ABNORMAL,
    sum(case when b.type_id = 'PRESSURE' or b.type_id = 'BOTH' then 1 end) PRESSURE_ABNORMAL
    from ys_dba02 a, CON_ABNORMAL_WELL b, water_well_basic wb
    where a.jh = wb.wellname
    and a.jh = b.wellname(+)
    and a.rq = b.record_date(+)
    and a.rq = #{date}
    group by wb.${unitColumn}, a.rq) a, con_abnormal_unit b
    where a.unitcode is not null
    and a.unitcode = b.unitcode(+)
    and a.record_date - 1 = b.record_date(+)
  </insert>

</mapper>