<?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="com.casic.missiles.modular.system.dao.BusWellInfoMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.casic.missiles.modular.system.model.BusWellInfo">
        <id column="ID" property="id"/>
        <result column="WELL_CODE" property="wellCode"/>
        <result column="WELL_NAME" property="wellName"/>
        <result column="DEEP" property="deep"/>
        <result column="COORDINATE_X" property="coordinateX"/>
        <result column="COORDINATE_Y" property="coordinateY"/>
        <result column="LAT_BAIDU" property="latBaidu"/>
        <result column="LNG_BAIDU" property="lngBaidu"/>
        <result column="LNG_GAODE" property="lngGaode"/>
        <result column="LAT_GAODE" property="latGaode"/>
        <result column="AREA" property="area"/>
        <result column="POSITION" property="position"/>
        <result column="DEPTID" property="deptid"/>
        <result column="PHOTOS" property="photos"/>
        <result column="NOTES" property="notes"/>
        <result column="WELL_TYPE" property="wellType"/>
        <result column="STAFF" property="staff"/>
        <result column="TEL" property="tel"/>
        <result column="TS" property="ts"/>
        <result column="VALID" property="valid"/>
        <result column="BFZT" property="bfzt"/>
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        ID AS id, WELL_CODE AS "wellCode", WELL_NAME AS "wellName", DEEP AS deep,
        COORDINATE_X AS "coordinateX", COORDINATE_Y AS "coordinateY", LAT_BAIDU AS "latBaidu",
        LNG_BAIDU AS "lngBaidu", LNG_GAODE AS "lngGaode", LAT_GAODE AS "latGaode", AREA AS area,
         POSITION AS position, DEPTID AS deptid, PHOTOS AS photos, NOTES AS notes,
         WELL_TYPE AS "wellType", STAFF AS staff, TEL AS tel, TS AS ts, VALID AS valid,
          BFZT AS bfzt,RESPONSIBLE_DEPT AS "responsibleDept",ROAD  AS road
    </sql>

    <sql id="Info_Column_List">
        ID AS id, WELL_CODE AS "wellCode", WELL_NAME AS "wellName",
         DEEP AS deep, COORDINATE_X AS "coordinateX", COORDINATE_Y AS "coordinateY",
          LAT_BAIDU AS "latBaidu", LNG_BAIDU AS "lngBaidu", LNG_GAODE AS "lngGaode",
          LAT_GAODE AS "latGaode", AREA AS area, POSITION AS position,
           DEPTID AS deptid, PHOTOS AS photos, NOTES AS notes, WELL_TYPE AS "wellType",
            STAFF AS staff, TEL AS tel, TS AS ts, VALID AS valid, BFZT AS bfzt,
            d1.simplename AS "deptName",d2.id AS "responsibleDept",d2.simplename AS "responsibleDeptName"
    </sql>

    <select id="info" resultType="map" parameterType="com.baomidou.mybatisplus.extension.plugins.pagination.Page">
        SELECT
        <include refid="Info_Column_List"/>
        FROM bus_well_info w
        LEFT OUTER JOIN sys_dept d1
        ON w.ID=d1.id
        LEFT OUTER JOIN sys_dept d2
        ON w.STAFF = d1.id
        WHERE w.id=#{id}
    </select>

    <select id="selectDataScopePage" resultType="com.casic.missiles.modular.system.model.BusWellInfo"
            parameterType="com.baomidou.mybatisplus.extension.plugins.pagination.Page">
        SELECT
        <include refid="Base_Column_List"/>,
        COALESCE (devicecount,0) AS "deviceCount"
        FROM bus_well_info w
        LEFT JOIN
        (SELECT COUNT(1) AS devicecount,well_id
        FROM bus_device_well d WHERE d.VALID = '1' GROUP BY well_id) c
        ON w.id = c.well_id
        WHERE w.VALID = '1'
        <if test="wellType != null and wellType !=''">
            and w.well_Type = #{wellType}
        </if>
        <if test="deptid != null and deptid !=-1 ">
            and w.deptid = #{deptid}
        </if>
        <if test="bfzt != null and bfzt != '' ">
            and w.bfzt = #{bfzt}
        </if>
        <if test="keywords != null and keywords !=''">
            and (w.WELL_CODE like
            <include refid="keywords_sql"/>
            or w.POSITION like
            <include refid="keywords_sql"/>
            )
        </if>
    </select>

    <sql id="keywords_sql" databaseId="oracle">
        '%'||#{keywords}||'%'
    </sql>
    <sql id="keywords_sql" databaseId="mysql">
        CONCAT('%',#{keywords},'%')
    </sql>
    <sql id="keywords_sql" databaseId="pgsql">
        CONCAT('%',#{keywords},'%')
    </sql>


    <sql id="WatchData_Column_List">
        d.ID  AS "deviceId", DEVCODE AS devcode, INSTALL_DATE AS "installDate",
        CASE WHEN 	bds.ONLINE_STATE ='1' THEN 1 ELSE 0 END  AS "onlineState", t.WATCH_TYPE AS "watchType", t.id as type
    </sql>

    <select id="watchData" resultType="com.casic.missiles.modular.system.dto.WatchDataDto"
            parameterType="com.baomidou.mybatisplus.extension.plugins.pagination.Page">
        SELECT
        <include refid="WatchData_Column_List"/>
        FROM bus_device d
        LEFT OUTER JOIN base_device_type t  ON d.DEVICE_TYPE = t.ID
        LEFT JOIN  bus_device_status bds ON  bds.DEVICE_ID=d.ID
        WHERE d.ID
        IN (
        SELECT dw.DEVICE_ID
        FROM bus_device_well dw
        WHERE dw.WELL_ID = #{id}
        AND dw.VALID='1'
        )
    </select>


    <select id="selectDataScope" resultType="com.casic.missiles.modular.system.dto.BatchExportDataDto"
            parameterType="com.baomidou.mybatisplus.extension.plugins.pagination.Page">
        SELECT w.WELL_CODE AS "wellCode", w.WELL_NAME AS "wellName",
        w.deep, w.WELL_TYPE AS "wellType",w.deptid AS "deptId",
        w.bfzt, w.position,w.id
        FROM bus_well_info w
        WHERE 1 = 1
        <if test="keywords != null and keywords !=''">
            AND w.WELL_CODE LIKE CONCAT('%',#{keywords},'%')
        </if>
        <if test="wellType != null and wellType !=''">
            and w.WELL_TYPE = #{wellType}
        </if>
        <if test="deptid != null and deptid !=''">
            and w.DEPTID = #{deptid}
        </if>
        AND w.VALID='1'
    </select>

    <select id="getBatchExportData" fetchSize="1000"
            resultType="com.casic.missiles.modular.system.dto.BatchExportDataDto"
            parameterType="com.baomidou.mybatisplus.extension.plugins.pagination.Page">
        SELECT w.WELL_CODE AS "wellCode", w.WELL_NAME AS "wellName", w.deep,
        w.WELL_TYPE AS "wellType",w.deptid AS "deptId",
        w.bfzt, w.position,w.id, IFNULL(deviceCount,0) AS "deviceCount"
        FROM bus_well_info w
        LEFT JOIN
        (SELECT COUNT(1) AS deviceCount ,well_id
        FROM bus_device_well d WHERE d.VALID = '1' GROUP BY well_id) c
        ON w.id = c.well_id
        WHERE w.VALID = '1'
        <if test="keywords != null and keywords !=''">
            AND w.WELL_CODE LIKE
            <include refid="keywords_sql"/>
        </if>
        <if test="wellType != null and wellType !=''">
            and w.WELL_TYPE = #{wellType}
        </if>
        <if test="deptid != null and deptid !=''">
            and w.DEPTID = #{deptid}
        </if>
    </select>

    <select id="deviceCountData" resultType="Integer"
            parameterType="com.baomidou.mybatisplus.extension.plugins.pagination.Page">
        SELECT COUNT(1)
        FROM bus_device_well d
        WHERE d.VALID = '1'
        <if test="id != null and id !=''">
            AND d.WELL_ID = #{id}
        </if>
    </select>

    <select id="deptNameData" resultType="String"
            parameterType="com.baomidou.mybatisplus.extension.plugins.pagination.Page">
        SELECT d.simplename
        FROM sys_dept d
        WHERE 1 = 1
        <if test="deptid != null and deptid !=''">
            AND d.id = #{deptid}
        </if>
    </select>

    <select id="getAreaId" resultType="String"
            parameterType="com.baomidou.mybatisplus.extension.plugins.pagination.Page">
        SELECT a.id
        FROM sys_area r,
        sys_area a
        WHERE r.area_name = #{qu}
        AND a.area_name LIKE <include refid="area_sql"/>
        AND r.id = a.pid
    </select>


    <sql id="area_sql" databaseId="oracle">
        '%'||#{area}||'%'
    </sql>
    <sql id="area_sql" databaseId="mysql">
        CONCAT('%',#{area},'%')
    </sql>
    <sql id="area_sql" databaseId="pgsql">
        CONCAT('%',#{area},'%')
    </sql>

    <select id="getAreaPId" resultType="String"
            parameterType="com.baomidou.mybatisplus.extension.plugins.pagination.Page">
        SELECT a.pid
        FROM sys_area a
        WHERE a.id = #{id}
    </select>

    <select id="getWellList" resultType="com.casic.missiles.modular.system.dto.BusWellInfoDto">
        SELECT
        <include refid="Base_Column_List"/>
        FROM bus_well_info w WHERE 1 = 1
        <if test="keywords != null and keywords !=''">
            AND (w.WELL_CODE LIKE CONCAT('%',#{keywords},'%') OR w.POSITION LIKE CONCAT('%',#{keywords},'%'))
        </if>
        <if test="wellType != null and wellType !=''">
            and w.WELL_TYPE = #{wellType}
        </if>
        <if test="deptid != null and deptid !=''">
            and w.DEPTID = #{deptid}
        </if>
        AND w.VALID='1'
    </select>

    <update id="armingById">
        UPDATE  bus_well_info
        SET bfzt = #{bfzt}
        WHERE VALID = '1'
        AND ID=#{id}
    </update>

    <update id="updateBatchBfcf">
        UPDATE
        bus_well_info
        SET bfzt = #{bfzt}
        WHERE VALID = '1'
        <if test="wellType != null and wellType !=''">
            and well_Type = #{wellType}
        </if>
        <if test="deptid != null and deptid !=''">
            and deptid = #{deptid}
        </if>
        <if test="keywords != null and keywords !=''">
            and (WELL_CODE like
            <include refid="keywords_sql"/>
            or POSITION like
            <include refid="keywords_sql"/>
            )
        </if>

        <if test="deptIds != null">
            AND deptid IN
            <foreach collection="deptIds" index="index" item="i" open="(" separator="," close=")">
                #{i}
            </foreach>
        </if>
    </update>

    <select id="isExistWellCode" resultType="Boolean">
        SELECT COUNT(1)
        FROM bus_well_info w
        WHERE VALID = '1'
          AND w.WELL_CODE = #{wellCode}
    </select>

    <select id="getWellListAll" resultType="com.casic.missiles.modular.system.model.BusWellInfo">
        SELECT
        <include refid="Base_Column_List"/>
        FROM bus_well_info
    </select>


</mapper>
