<?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.QualityCheckDao">
  <sql id="Base_Column_List">
    iqc.OBJECT_TYPE,iqc.OBJECT_NAME,RECORD_DATE,iqc.UNITCODE,OIL_STANDARD,OIL_ACTUAL,SUSPENSION_STANDARD,SUSPENSION_ACTUAL,SRB_STANDARD,SRB_ACTUAL,PARTICLE_STANDARD,
    PARTICLE_ACTUAL,CORROSION_STANDARD,CORROSION_ACTUAL,AUXILIARY_STANDARD,AUXILIARY_ACTUAL,AUXILIARY_NAME,CREATE_ON,su.unitname, su.fullname
  </sql>

  <select id="selectList" parameterType="QualityCheckParam" resultType="QualityCheck">
    select
    <include refid="Base_Column_List"/>
    from INDI_QUALITY_CHECK iqc,SYS_FLAG sf,SYS_UNITINFO su
    where iqc.unitcode in (select unitcode
    from SYS_UNITINFO
    start with UNITCODE = #{unitcode}
    connect by prior UNITCODE = P_UNITCODE)
    and RECORD_DATE >= #{testDate,jdbcType=TIMESTAMP}
    and RECORD_DATE &lt; #{testDate,jdbcType=TIMESTAMP} + 1
    and iqc.unitcode = su.unitcode
    <if test="(unionStation != null and unionStation!= '')
      or (sewageStation != null and sewageStation!= '')
      or (injectStation != null and injectStation!= '')
      or (allocateStation != null and allocateStation!= '')
      or (allocateStation != null and allocateStation!= '')">
    and (
      1 = 0
    <if test="unionStation != null and unionStation!= ''">
      or iqc.OBJECT_NAME = #{unionStation,jdbcType=VARCHAR}
    </if>
    <if test="sewageStation != null and sewageStation!= ''">
      or iqc.OBJECT_NAME = #{sewageStation,jdbcType=VARCHAR}
    </if>
    <if test="injectStation != null and injectStation!= ''">
      or iqc.OBJECT_NAME = #{injectStation,jdbcType=VARCHAR}
    </if>
    <if test="allocateStation != null and allocateStation!= ''">
      or iqc.OBJECT_NAME = #{allocateStation,jdbcType=VARCHAR}
    </if>
    <if test="waterWell != null and waterWell!= ''">
      or iqc.OBJECT_NAME = #{waterWell,jdbcType=VARCHAR}
    </if>
    )
    </if>
    and iqc.OBJECT_TYPE = sf.FLAG_CODE
    and sf.category_code = 'QUALITY_CHECK_TYPE'
    and iqc.UNITCODE = su.UNITCODE
  </select>

  <select id="searchList" parameterType="QualityCheckSearchParam" resultType="QualityCheck">
    select
    <include refid="Base_Column_List"/>
    from INDI_QUALITY_CHECK iqc,SYS_FLAG sf,SYS_UNITINFO su
    where iqc.unitcode in (select unitcode
    from SYS_UNITINFO
    start with UNITCODE = #{unitcode}
    connect by prior UNITCODE = P_UNITCODE)
    and RECORD_DATE >= #{startDate,jdbcType=TIMESTAMP}
    and RECORD_DATE &lt; #{endDate,jdbcType=TIMESTAMP} + 1
    and iqc.unitcode = su.unitcode
    <if test="(unionStation != null and unionStation!= '')
      or (sewageStation != null and sewageStation!= '')
      or (injectStation != null and injectStation!= '')
      or (allocateStation != null and allocateStation!= '')
      or (allocateStation != null and allocateStation!= '')">
      and (
      1 = 0
      <if test="unionStation != null and unionStation!= ''">
        or iqc.OBJECT_NAME = #{unionStation,jdbcType=VARCHAR}
      </if>
      <if test="sewageStation != null and sewageStation!= ''">
        or iqc.OBJECT_NAME = #{sewageStation,jdbcType=VARCHAR}
      </if>
      <if test="injectStation != null and injectStation!= ''">
        or iqc.OBJECT_NAME = #{injectStation,jdbcType=VARCHAR}
      </if>
      <if test="allocateStation != null and allocateStation!= ''">
        or iqc.OBJECT_NAME = #{allocateStation,jdbcType=VARCHAR}
      </if>
      <if test="waterWell != null and waterWell!= ''">
        or iqc.OBJECT_NAME = #{waterWell,jdbcType=VARCHAR}
      </if>
      )
    </if>
    and iqc.OBJECT_TYPE = sf.FLAG_CODE
    and sf.category_code = 'QUALITY_CHECK_TYPE'
    and iqc.UNITCODE = su.UNITCODE
  </select>

  <select id="sumList" parameterType="QualityCheckSearchParam" resultType="java.util.Map">
    SELECT
    ROUND(oil1, 2) oil1,
    ROUND(sus1, 2) sus1,
    ROUND(srb1, 2) srb1,
    ROUND(par1, 2) par1,
    ROUND(cor1, 2) cor1,
    ROUND(aux1, 2) aux1,
    ROUND(oil2, 2) oil2,
    ROUND(sus2, 2) sus2,
    ROUND(srb2, 2) srb2,
    ROUND(par2, 2) par2,
    ROUND(cor2, 2) cor2,
    ROUND(aux2, 2) aux2,

    ROUND(dbs1 / decode(zs1, 0, null, zs1) * 100, 2) oil3,
    ROUND(dbs2 / decode(zs2, 0, null, zs2) * 100, 2) sus3,
    ROUND(dbs3 / decode(zs3, 0, null, zs3) * 100, 2) srb3,
    ROUND(dbs4 / decode(zs4, 0, null, zs4) * 100, 2) par3,
    ROUND(dbs5 / decode(zs5, 0, null, zs5) * 100, 2) cor3,
    ROUND(dbs6 / decode(zs6, 0, null, zs6) * 100, 2) aux3,

    ROUND(oil4, 2) oil4,
    ROUND(sus4, 2) sus4,
    ROUND(srb4, 2) srb4,
    ROUND(par4, 2) par4,
    ROUND(cor4, 2) cor4,
    ROUND(aux4, 2) aux4
    from (select AVG(OIL_STANDARD) oil1,
    AVG(SUSPENSION_STANDARD) sus1,
    AVG(SRB_STANDARD) srb1,
    AVG(PARTICLE_STANDARD) par1,
    AVG(CORROSION_STANDARD) cor1,
    AVG(AUXILIARY_STANDARD) aux1,
    AVG(OIL_ACTUAL) oil2,
    AVG(SUSPENSION_ACTUAL) sus2,
    AVG(SRB_ACTUAL) srb2,
    AVG(PARTICLE_ACTUAL) par2,
    AVG(CORROSION_ACTUAL) cor2,
    AVG(AUXILIARY_ACTUAL) aux2,

    AVG(case when OIL_ACTUAL > OIL_STANDARD then OIL_ACTUAL end) oil4,
    AVG(case when SUSPENSION_ACTUAL > SUSPENSION_STANDARD then SUSPENSION_ACTUAL end) sus4,
    AVG(case when SRB_ACTUAL > SRB_STANDARD then SRB_ACTUAL end) srb4,
    AVG(case when PARTICLE_ACTUAL > PARTICLE_STANDARD then PARTICLE_ACTUAL end) par4,
    AVG(case when CORROSION_ACTUAL > CORROSION_STANDARD then CORROSION_ACTUAL end) cor4,
    AVG(case when AUXILIARY_ACTUAL >AUXILIARY_STANDARD then AUXILIARY_ACTUAL end) aux4,

    sum(case
    when OIL_STANDARD >= OIL_ACTUAL then
    1
    end) dbs1,
    sum(case
    when OIL_ACTUAL is not null and OIL_STANDARD is not null then
    1
    end) zs1,
    sum(case
    when SUSPENSION_STANDARD >= SUSPENSION_ACTUAL then
    1
    end) dbs2,
    sum(case
    when SUSPENSION_ACTUAL is not null and
    SUSPENSION_STANDARD is not null then
    1
    end) zs2,

    sum(case
    when SRB_STANDARD >= SRB_ACTUAL then
    1
    end) dbs3,
    sum(case
    when SRB_ACTUAL is not null and SRB_STANDARD is not null then
    1
    end) zs3,

    sum(case
    when PARTICLE_STANDARD >= PARTICLE_ACTUAL then
    1
    end) dbs4,
    sum(case
    when PARTICLE_ACTUAL is not null and
    PARTICLE_STANDARD is not null then
    1
    end) zs4,

    sum(case
    when CORROSION_STANDARD >= CORROSION_ACTUAL then
    1
    end) dbs5,
    sum(case
    when CORROSION_ACTUAL is not null and
    CORROSION_STANDARD is not null then
    1
    end) zs5,

    sum(case
    when AUXILIARY_STANDARD >= AUXILIARY_ACTUAL then
    1
    end) dbs6,
    sum(case
    when AUXILIARY_ACTUAL is not null and
    AUXILIARY_STANDARD is not null then
    1
    end) zs6
    FROM indi_quality_check iqc,SYS_UNITINFO su
    where iqc.unitcode in (select unitcode
    from SYS_UNITINFO
    start with UNITCODE = #{unitcode}
    connect by prior UNITCODE = P_UNITCODE)
    and RECORD_DATE >= #{startDate,jdbcType=TIMESTAMP}
    and RECORD_DATE &lt;= #{endDate,jdbcType=TIMESTAMP}
    and iqc.unitcode = su.unitcode
    <if test="(unionStation != null and unionStation!= '')
      or (sewageStation != null and sewageStation!= '')
      or (injectStation != null and injectStation!= '')
      or (allocateStation != null and allocateStation!= '')
      or (allocateStation != null and allocateStation!= '')">
      and (
      1 = 0
      <if test="unionStation != null and unionStation!= ''">
        or iqc.OBJECT_NAME = #{unionStation,jdbcType=VARCHAR}
      </if>
      <if test="sewageStation != null and sewageStation!= ''">
        or iqc.OBJECT_NAME = #{sewageStation,jdbcType=VARCHAR}
      </if>
      <if test="injectStation != null and injectStation!= ''">
        or iqc.OBJECT_NAME = #{injectStation,jdbcType=VARCHAR}
      </if>
      <if test="allocateStation != null and allocateStation!= ''">
        or iqc.OBJECT_NAME = #{allocateStation,jdbcType=VARCHAR}
      </if>
      <if test="waterWell != null and waterWell!= ''">
        or iqc.OBJECT_NAME = #{waterWell,jdbcType=VARCHAR}
      </if>
      )
    </if>
    and iqc.UNITCODE = su.UNITCODE)

  </select>

  <select id="selectObjectType"  resultType="ObjectTypes">
    select
    FLAG_CODE,FLAG_NAME,FLAG_VALUE,FLAG_ORDER,FLAG_TEXT1
    from sys_flag sf
    where sf.category_code = 'QUALITY_CHECK_TYPE'
    order by FLAG_ORDER
  </select>

  <select id="selectWaterStations" parameterType="WaterStationParam" resultType="WaterStation">
    select
    NAME,ORDER_NUM,AREA_CODE,STATION_TYPE
    from sys_station ss
    where AREA_CODE in (select unitcode
    from SYS_UNITINFO
    start with UNITCODE = #{areaCode}
    connect by prior UNITCODE = P_UNITCODE)
    and STATION_TYPE = #{stationType,jdbcType=VARCHAR}
    order by ORDER_NUM
  </select>

  <select id="selectStationByName" parameterType="String" resultType="WaterStation">
    select
    NAME,ORDER_NUM,AREA_CODE,STATION_TYPE
    from sys_station ss
    where name = #{name,jdbcType=VARCHAR}
  </select>

  <select id="selectWaterWells" parameterType="UnitFieldBlockWellParam" resultType="WellBasic">
    select
    WELLNAME, BLOCKCODE, UNITCODE, UNITNAME, FIELDCODE, BED, HBED1, HBED2, HO, HW, LIFTMODE,
    OVENCODE, PLANTCODE, STATIONCODE, UNITCODE_COMPANY companycode, UNITCODE_AREA areacode,
    POS_X, POS_Y
    from v_water_well_basic
    where unitcode in (select unitcode
    from SYS_UNITINFO
    start with UNITCODE = #{unitcode}
    connect by prior UNITCODE = P_UNITCODE)
    <if test="fieldcode != null and fieldcode!= ''">
      and fieldcode = #{fieldcode}
    </if>
    <if test="blockcode != null and blockcode!= ''">
      and blockcode = #{blockcode}
    </if>
    <if test="wellname != null and wellname!= ''">
      and WELLNAME like '%'||#{wellname}||'%'
    </if>
    order by wellname
  </select>

  <insert id="insert" parameterType="QualityCheck">
    insert into INDI_QUALITY_CHECK
    (OBJECT_TYPE, OBJECT_NAME,
    RECORD_DATE,UNITCODE,
    OIL_STANDARD,OIL_ACTUAL,
    SUSPENSION_STANDARD,SUSPENSION_ACTUAL,
    SRB_STANDARD,SRB_ACTUAL,
    PARTICLE_STANDARD,PARTICLE_ACTUAL,
    CORROSION_STANDARD,CORROSION_ACTUAL,
    AUXILIARY_STANDARD,AUXILIARY_ACTUAL,AUXILIARY_NAME,
    CREATE_ON)
    values (#{objectType,jdbcType=VARCHAR}, #{objectName,jdbcType=VARCHAR},
    #{recordDate,jdbcType=TIMESTAMP},#{unitCode,jdbcType=VARCHAR},
    #{oilStandard,jdbcType=DECIMAL},#{oilActual,jdbcType=DECIMAL},
    #{suspensionStandard,jdbcType=DECIMAL},#{suspensionActual,jdbcType=DECIMAL},
    #{srbStandard,jdbcType=DECIMAL},#{srbActual,jdbcType=DECIMAL},
    #{particleStandard,jdbcType=DECIMAL},#{particleActual,jdbcType=DECIMAL},
    #{corrosionStandard,jdbcType=DECIMAL},#{corrosionActual,jdbcType=DECIMAL},
    #{auxiliaryStandard,jdbcType=DECIMAL},#{auxiliaryActual,jdbcType=DECIMAL},#{auxiliaryName,jdbcType=VARCHAR},
    #{createOn,jdbcType=TIMESTAMP})
  </insert>

  <select id="selectByPrimaryKey" parameterType="QualityCheckParam"  resultType="QualityCheck">
    select OBJECT_TYPE, OBJECT_NAME, RECORD_DATE,UNITCODE,OIL_STANDARD,OIL_ACTUAL,SUSPENSION_STANDARD,SUSPENSION_ACTUAL,SRB_STANDARD,SRB_ACTUAL,PARTICLE_STANDARD,PARTICLE_ACTUAL,CORROSION_STANDARD,CORROSION_ACTUAL,AUXILIARY_STANDARD,AUXILIARY_ACTUAL,AUXILIARY_NAME,
      CREATE_ON
    from INDI_QUALITY_CHECK
    where OBJECT_NAME = #{objectName,jdbcType=VARCHAR}
    and RECORD_DATE = #{recordDate,jdbcType=TIMESTAMP}
  </select>

  <update id="updateData" parameterType="QualityCheck">
    update INDI_QUALITY_CHECK
    <set>
      <if test="oilStandard != null">
        OIL_STANDARD = #{oilStandard,jdbcType=DECIMAL},
      </if>
      <if test="oilActual != null">
        OIL_ACTUAL = #{oilActual,jdbcType=DECIMAL},
      </if>
      <if test="suspensionStandard != null">
        SUSPENSION_STANDARD = #{suspensionStandard,jdbcType=DECIMAL},
      </if>
      <if test="suspensionActual != null">
        SUSPENSION_ACTUAL = #{suspensionActual,jdbcType=DECIMAL},
      </if>
      <if test="srbStandard != null">
        SRB_STANDARD = #{srbStandard,jdbcType=DECIMAL},
      </if>
      <if test="srbActual != null">
        SRB_ACTUAL = #{srbActual,jdbcType=DECIMAL},
      </if>
      <if test="particleStandard != null">
        PARTICLE_STANDARD = #{particleStandard,jdbcType=DECIMAL},
      </if>
      <if test="particleActual != null">
        PARTICLE_ACTUAL = #{particleActual,jdbcType=DECIMAL},
      </if>
      <if test="corrosionStandard != null">
        CORROSION_STANDARD = #{corrosionStandard,jdbcType=DECIMAL},
      </if>
      <if test="corrosionActual != null">
        CORROSION_ACTUAL = #{corrosionActual,jdbcType=DECIMAL},
      </if>
      <if test="auxiliaryStandard != null">
        AUXILIARY_STANDARD = #{auxiliaryStandard,jdbcType=DECIMAL},
      </if>
      <if test="auxiliaryActual != null">
        AUXILIARY_ACTUAL = #{auxiliaryActual,jdbcType=DECIMAL},
      </if>
      <if test="auxiliaryName != null">
        AUXILIARY_NAME = #{auxiliaryName,jdbcType=VARCHAR},
      </if>
      <if test="createOn != null">
        CREATE_ON = #{createOn,jdbcType=TIMESTAMP},
      </if>
    </set>
    where OBJECT_NAME = #{objectName,jdbcType=VARCHAR}
    and RECORD_DATE = #{recordDate,jdbcType=TIMESTAMP}
  </update>

  <delete id="deleteByDate" parameterType="QualityCheckDelete">
    delete from INDI_QUALITY_CHECK
    where OBJECT_NAME = #{objectName,jdbcType=VARCHAR}
      and RECORD_DATE = #{recordDate,jdbcType=TIMESTAMP}
  </delete>

</mapper>