<?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.sys.dao.WellBasicDao">
  <sql id="Base_Column_List">
    wellname, blockcode, unitcode, unitname, fieldcode, bed, stationcode,
    unitcode_company companycode, well_type, unitcode_area areacode, unitcode_group
  </sql>

  <select id="selectWaterWells" parameterType="UnitFieldBlockWellParam" resultType="WellBasic">
    select
    <include refid="Base_Column_List" />
    from v_water_well_basic
    <where>
      <if test="unitcode != null and unitcode!= ''">
        and unitcode in (select unitcode
        from SYS_UNITINFO
        start with UNITCODE = #{unitcode}
        connect by prior UNITCODE = P_UNITCODE)
      </if>
      <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>
    </where>
    order by wellname
  </select>

  <select id="selectWellBasic" parameterType="String" resultType="WellBasic">
    select
    <include refid="Base_Column_List" />
    from water_well_basic
    where wellname = #{value}
  </select>

  <select id="selectWellBasicByNames" parameterType="List" resultType="WellBasic">
    select
    <include refid="Base_Column_List" />
    from water_well_basic
    where wellname in
    <foreach collection="list" item = "code" open="(" separator="," close=")" index="index">
      #{code}
    </foreach>
  </select>

  <select id="selectNoGroupWellList" parameterType="String" resultType="WellBasic">
    select
    jh wellname, substr(mqjb, 0, 1) category
    from ys_daa01
    where (mqjb like '1%' or mqjb like '3%')
    and dwdm in (select unitcode
      from SYS_UNITINFO
      start with UNITCODE = #{value}
      connect by prior UNITCODE = P_UNITCODE)
    and not exists
    (select 1 from well_group where ',' || oil_wells || ',' like '%,' || ys_daa01.jh || ',%')
    and not exists
    (select 1 from well_group where ',' || water_wells || ',' like '%,' || ys_daa01.jh || ',%')
    order by jh
  </select>
</mapper>
