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

  <update id="syncWaterWell">
    merge into water_well_basic T1
    USING (SELECT * FROM v_well_basic where well_type like '3%'
            or wellname in (select jh from ys_dba02 where rq = trunc(sysdate-1))
            or wellname in (select wellname from water_well_basic)) T2 ON
        (T1.wellname = t2.wellname)
    WHEN MATCHED THEN
      update
       set blockcode = T2.blockcode,
           unitcode = T2.unitcode,
           unitname = T2.unitname,
           fieldcode = T2.fieldcode,
           bed = T2.bed,
           stationcode = T2.stationcode,
           unitcode_company = T2.unitcode_company,
           well_type = T2.well_type,
           unitcode_area = T2.unitcode_area,
           unitcode_group = T2.unitcode_group
    WHEN NOT MATCHED THEN
      insert
        (wellname, blockcode, unitcode, unitname, fieldcode, bed, stationcode, unitcode_company, well_type, unitcode_area, unitcode_group)
      values
        (T2.wellname, T2.blockcode, T2.unitcode, T2.unitname, T2.fieldcode, T2.bed, T2.stationcode, T2.unitcode_company, T2.well_type, T2.unitcode_area, T2.unitcode_group)
  </update>

  <insert id="syncSingleWellDaily">
    insert into well_section_daily
    (wellname, record_date, unitcode, well_type, section, nozzle, pp, pc, pt,
    plan_daily_water, daily_water, water_well_classify, inject_type_ys, open_time,
    remark_code, remark_code1, layer, remark, plan_section, well_water)
    select b.jh wellname, b.rq record_date, c.dwdm unitcode,
      case
        when (c.mqjb is null or c.mqjb not like 'X%') and (b.zsmd is null or b.zsmd not in ('边底水驱','消化污水')) then '1'
        when c.mqjb like 'X%' and (b.zsmd is null or b.zsmd not in ('边底水驱','消化污水')) then '0'
        when (c.mqjb is null or c.mqjb not like 'X%') and b.zsmd = '消化污水' then '2'
        when (c.mqjb is null or c.mqjb not like 'X%') and b.zsmd = '边底水驱' then '3'
        when c.mqjb like 'X%' and b.zsmd = '消化污水' then '4'
        when c.mqjb like 'X%' and b.zsmd = '边底水驱' then '5'
      end well_type,
      1 section, null nozzle, b.gxyl pp, b.ty pc, b.yy pt,
      b.rpzsl plan_daily_water, b.rzsl daily_water,
      case when b.bz like '%一类井%' then '1'
      when b.bz like '%二类井%' then '2'
      when b.bz like '%三类井%' then '3'
      when b.bz like '%四类井%' then '4' end water_well_classify,
      b.zsfs inject_type_ys,
      b.scsj open_time, b.bzdm remark_code, b.bzdm1 remark_code,
      b.zscw,
      b.bz,
      b.pzcds,
      b.rzsl
    from ys_dba02 b
    left join ys_daa01 c on b.jh = c.jh
    where b.rq >= #{startDate,jdbcType=TIMESTAMP}
    and b.rq &lt;= #{endDate,jdbcType=TIMESTAMP}
    and b.zsfs in
    <foreach collection="injectCodes" item = "code" open="(" separator="," close=")" index="index">
      #{code}
    </foreach>
  </insert>

  <insert id="syncWellSectionDaily">
    insert into well_section_daily
    (wellname, record_date, unitcode, well_type, section, nozzle, pp, pc, pt,
    plan_daily_water, daily_water, water_well_classify, inject_type_ys, open_time,
    remark_code, remark_code1, layer, remark, plan_section, well_water)
    select a.jh wellname, a.rq record_date, c.dwdm unitcode,
      case
        when (c.mqjb is null or c.mqjb not like 'X%') and (b.zsmd is null or b.zsmd not in ('边底水驱','消化污水')) then '1'
        when c.mqjb like 'X%' and (b.zsmd is null or b.zsmd not in ('边底水驱','消化污水')) then '0'
        when (c.mqjb is null or c.mqjb not like 'X%') and b.zsmd = '消化污水' then '2'
        when (c.mqjb is null or c.mqjb not like 'X%') and b.zsmd = '边底水驱' then '3'
        when c.mqjb like 'X%' and b.zsmd = '消化污水' then '4'
        when c.mqjb like 'X%' and b.zsmd = '边底水驱' then '5'
      end well_type,
      a.cdxh section, a.cdsz nozzle, b.gxyl pp, b.ty pc, b.yy pt,
      a.cdrpsl plan_daily_water, a.cdrzsl daily_water,
      case when b.bz like '%一类井%' then '1'
      when b.bz like '%二类井%' then '2'
      when b.bz like '%三类井%' then '3'
      when b.bz like '%四类井%' then '4' end water_well_classify,
      b.zsfs inject_type_ys,
      b.scsj open_time, b.bzdm remark_code, b.bzdm1 remark_code,
      a.zscw,
      b.bz,
      b.pzcds,
      b.rzsl
    from dba021 a
    inner join ys_dba02 b on a.jh = b.jh and a.rq = b.rq
    left join ys_daa01 c on a.jh = c.jh
    where a.rq >= #{startDate,jdbcType=TIMESTAMP}
    and a.rq &lt;= #{endDate,jdbcType=TIMESTAMP}
    and b.zsfs in
    <foreach collection="injectCodes" item = "code" open="(" separator="," close=")" index="index">
      #{code}
    </foreach>
  </insert>

  <select id="selectWellSectionMonthBasic" resultType="WellSectionMonth">
    select wellname, #{dateRange.name} month, unitcode, well_type, section, nozzle, inject_type, layer, remark
    from (
      select t.*, dense_rank() over(partition by wellname order by record_date desc) rn
      from well_section_daily t
      where record_date >= #{dateRange.start}
      and record_date &lt;= #{dateRange.end}
      <if test="unitcode != null and unitcode != ''">
        and t.unitcode in (select unitcode from sys_unitinfo start with unitcode = #{unitcode} connect by prior unitcode = p_unitcode)
      </if>
    ) t
    where rn = 1
  </select>

  <select id="summaryByWell" resultType="WellSectionMonth">
    select t.*,
    case when day_num >= 1 and month_water > 0.5 then '1' else '0' end section_open
    from (select t.wellname, #{dateRange.name} month,
    sum(well_water) month_water,
    trunc(sum(open_time)/24, 1) day_num,
    max(water_well_classify) water_well_classify,
    case when
    sum(case when
    remark like '%大修%' or
    remark like '%调剖%' or
    remark like '%挤素%' or
    remark like '%化堵%' or
    remark like '%酸化%' or
    remark like '%示踪剂%'
    then 1 end) > 0 then '1' else '0' end remark_no_check
    from
    (select t.*, row_number() over(partition by wellname, record_date order by section) rn
    from well_section_daily t
    where t.record_date >= #{dateRange.start}
    and t.record_date &lt;= #{dateRange.end}
    <if test="unitcode != null and unitcode != ''">
      and t.unitcode in (select unitcode from sys_unitinfo start with unitcode = #{unitcode} connect by prior unitcode = p_unitcode)
    </if>
    ) t
    where rn = 1
    group by t.wellname) t
  </select>

  <select id="summaryWellSectionMonth" resultType="WellSectionMonth">
    select wellname, month, section, pp, pc, pt, daily_water, month_water, day_num,
    case when day_num > 0 then round(month_water/day_num, 2) end daily_ability,
    plan_daily_water
    from (
      select t.wellname, #{dateRange.name} month, t.section,
      round(avg(pp), 2) pp, round(avg(pc), 2) pc, round(avg(pt), 2) pt,
      max(t.plan_daily_water) plan_daily_water,
      round(avg(daily_water), 2) daily_water,
      sum(daily_water) month_water,
      trunc(sum(open_time)/24, 1) day_num
      from well_section_daily t,
      (select wellname, section, inject_type_ys, plan_daily_water
      from (select t.*, row_number() over(partition by wellname, section order by num desc) rn
      from (select wellname,
      section,
      max(inject_type_ys) inject_type_ys,
      plan_daily_water,
      count(1) num
      from (select t.*, rank() over(partition by wellname order by max_date desc) rn
      from (select t.*, max(record_date) over(partition by wellname, inject_type_ys) max_date
      from well_section_daily t
      where record_date >= #{dateRange.start}
      and record_date &lt;= #{dateRange.end}
      and open_time > 0 and well_water > 0
      <if test="unitcode != null and unitcode != ''">
        and t.unitcode in (select unitcode from sys_unitinfo start with unitcode = #{unitcode} connect by prior unitcode = p_unitcode)
      </if>
      ) t)
      where rn = 1
      group by wellname, section, plan_daily_water) t) t
      where rn = 1
      ) pd
      where t.record_date >= #{dateRange.start}
      and t.record_date &lt;= #{dateRange.end}
      and t.wellname = pd.wellname
      and t.section = pd.section
      and t.plan_daily_water = pd.plan_daily_water
      group by t.wellname, t.section
    ) t
  </select>

  <select id="selectInjectTechnology" resultType="SysInjectTechnology">
    select DM, LB, DMMC, ZSGY, TXSG, FTPD
    from SYS_INJECT_TECHNOLOGY
  </select>
</mapper>
