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

    <!-- 通用查询映射结果 -->
    <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"
    </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>

    <sql id="data_diff" databaseId="mysql">
         and datediff(now(),dg.UPTIME) <![CDATA[< ]]>  ${paramDay}
    </sql>

    <sql id="data_diff" databaseId="pgsql">
        AND date_part('day',now()-dg.UPTIME)   <![CDATA[< ]]>  ${paramDay}
    </sql>

    <sql id="data_diff" databaseId="oracle">
         and date_part(now(),dg.UPTIME) <![CDATA[< ]]>  ${paramDay}
    </sql>

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

    <sql id="date_toChar" databaseId="mysql">
       DATE_FORMAT(${paramTime},'%Y-%m-%d %H:%i:%s')
    </sql>

    <sql id="date_toChar" databaseId="pgsql">
       TO_CHAR(${paramTime},'yyyy-MM-dd HH24:mi:ss')
    </sql>

    <sql id="date_toChar" databaseId="oracle">
       TO_CHAR(${paramTime},'yyyy-MM-dd HH24:mi:ss')
    </sql>

    <sql id="char_toDate" databaseId="mysql">
       ${paramStr}
    </sql>

    <sql id="char_toDate" databaseId="pgsql">
      TO_TIMESTAMP(${paramStr},'yyyy-MM-dd hh24:mi:ss')::timestamp without time zone
    </sql>

    <sql id="char_toDate" databaseId="oracle">
       ${paramStr}
    </sql>

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

    <select id="getAlarmWellList" 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
            <include refid="keywords_sql"/>
            OR w.POSITION LIKE<include refid="keywords_sql"/>)
        </if>
        <if test="wellType != null and wellType !=''">
            and w.WELL_TYPE = #{wellType}
        </if>
        <if test="deptid != null and deptid != -1 ">
            and w.DEPTID = #{deptid}
        </if>
        AND w.VALID='1' and w.WELL_CODE in (SELECT DISTINCT well_code from alarm_records where status = '1')
    </select>

    <select id="getNotAlarmWellList" 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
            <include refid="keywords_sql"/>
            OR w.POSITION LIKE<include refid="keywords_sql"/>)
        </if>
        <if test="wellType != null and wellType !=''">
            and w.WELL_TYPE = #{wellType}
        </if>
        <if test="deptid != null and deptid != -1 ">
            and w.DEPTID = #{deptid}
        </if>
        AND w.VALID='1' and w.WELL_CODE not in (SELECT DISTINCT WELL_CODE from alarm_records where status = '1')
    </select>

    <select id="wellcoverData" resultType="com.casic.missiles.modular.system.dto.DeviceData"
            parameterType="com.baomidou.mybatisplus.extension.plugins.pagination.Page">
        SELECT descn AS data,
               logtime
        FROM data_wellcover
        WHERE devcode = #{devcode}
        ORDER BY logtime
                DESC LIMIT 1
    </select>

    <select id="liquidData" resultType="com.casic.missiles.modular.system.dto.DeviceData"
            parameterType="com.baomidou.mybatisplus.extension.plugins.pagination.Page">
        SELECT l.LIQUIDDATA AS data,
               l.UPTIME     AS logtime
        FROM data_liquid l
        WHERE DEVCODE = #{devcode}
        ORDER BY l.UPTIME
                DESC LIMIT 1
    </select>

    <select id="harmfulgasData" resultType="com.casic.missiles.modular.system.dto.DeviceData">
        SELECT CONCAT(CO, ', ', CH4, ', ', H2S, ', ', O2) AS data,
               UPTIME                                     AS logtime
        FROM data_harmful_gas
        WHERE DEVCODE = #{devcode}
        ORDER BY UPTIME
                DESC LIMIT 1
    </select>

    <select id="gasData" resultType="com.casic.missiles.modular.system.dto.DeviceData">
        SELECT STRENGTH AS data,
               UPTIME   AS logtime
        FROM data_gas
        WHERE DEVCODE = #{devcode}
        ORDER BY UPTIME  DESC
        LIMIT 1
    </select>


    <select id="temphumData" resultType="com.casic.missiles.modular.system.dto.DeviceData">
        SELECT CONCAT(TEMPERATURE, ', ', HUMIDITY) AS data,
               UPTIME                              AS logtime
        FROM data_temp_hum
        WHERE DEVCODE = #{devcode}
        ORDER BY UPTIME  DESC
        LIMIT 1
    </select>

    <select id="digData" resultType="com.casic.missiles.modular.system.dto.DeviceData">
        SELECT DDATA  AS data,
               UPTIME AS logtime
        FROM data_dig
        WHERE DEVCODE = #{devcode}
        ORDER BY UPTIME
                DESC LIMIT 1
    </select>

    <select id="noiseData" resultType="com.casic.missiles.modular.system.dto.DeviceData">
        SELECT DDATA  AS data,
               UPTIME AS logtime
        FROM data_noise
        WHERE DEVCODE = #{devcode}
        ORDER BY UPTIME
                DESC LIMIT 1
    </select>

    <select id="wellcoverlocaData" resultType="com.casic.missiles.modular.system.dto.DeviceData">
        SELECT CONCAT(LNG, ', ', LAT)   AS data,
               LOGTIME                AS logtime
        FROM data_wellcover_loca
        WHERE DEVCODE = #{devcode}
        ORDER BY UPTIME
         DESC LIMIT 1
    </select>

    <select id="bfztCountData" resultType="com.casic.missiles.modular.system.dto.BfztCountDataDto"
            parameterType="com.baomidou.mybatisplus.extension.plugins.pagination.Page">
        SELECT
        COUNT(id) total,
        SUM(CASE WHEN BFZT='1' THEN 1 ELSE 0 END ) bfWell,
        SUM(CASE WHEN BFZT='0' THEN 1 ELSE 0 END ) cfWell
        FROM bus_well_info
        WHERE VALID='1'
        <choose>
            <when test="isApp == true">
                AND responsible_dept
            </when>
            <when test="isApp == false">
                AND deptid
            </when>
        </choose>
        IN
        <foreach collection="deptIds" index="index" item="i" open="("
                 separator="," close=")">
            #{i}
        </foreach>
    </select>

    <select id="lgGasData" resultType="com.casic.missiles.modular.system.dto.DeviceData">
        SELECT STRENGTH AS data,
               UPTIME   AS logtime
        FROM data_gasliquid_gas
        WHERE DEVCODE = #{devcode}
        ORDER BY UPTIME
                DESC LIMIT 1
    </select>

    <select id="lgLiquidData" resultType="com.casic.missiles.modular.system.dto.DeviceData">
        SELECT LIQUIDDATA AS data,
               UPTIME     AS logtime
        FROM data_gasliquid_liquid
        WHERE DEVCODE = #{devcode}
        ORDER BY UPTIME
                DESC LIMIT 1
    </select>

    <select id="tubeData" resultType="com.casic.missiles.modular.system.dto.DeviceData">
        SELECT strength AS data,
               UPTIME     AS logtime
        FROM data_tube
        WHERE DEVCODE = #{devcode}
        ORDER BY UPTIME
                DESC LIMIT 1
    </select>

    <select id="H2sData" resultType="com.casic.missiles.modular.system.dto.DeviceData">
        SELECT strength AS data,
               UPTIME     AS logtime
        FROM data_h2s
        WHERE DEVCODE = #{devcode}
        ORDER BY UPTIME
                DESC LIMIT 1
    </select>

    <select id="gasDector" resultType="com.casic.missiles.modular.system.dto.DeviceData">
        SELECT strength  AS data,
               UPTIME     AS logtime
        FROM  data_gas_dector
        WHERE DEVCODE = #{devcode}
        ORDER BY UPTIME
        DESC LIMIT 1
    </select>

    <select id="hydrantData" resultType="com.casic.missiles.modular.system.dto.DeviceData">
        SELECT DESCN AS data,
              logtime     AS logtime
        FROM data_hydrant
        WHERE DEVCODE = #{devcode}
        ORDER BY logtime
        DESC LIMIT 1
    </select>

    <select id="selectLiquidDataScopePage"
            resultType="com.casic.missiles.modular.system.dto.ExcelModel.LiquidDataExcelDto"
            parameterType="com.baomidou.mybatisplus.extension.plugins.pagination.Page">
        SELECT DEVCODE,
        DEVICETYPE,
        <include refid="date_toChar">
            <property name="paramTime" value="LOGTIME"/>
        </include>
        AS "LOGTIME",
        <include refid="date_toChar">
            <property name="paramTime" value="UPTIME"/>
        </include>
        AS "UPTIME",
        LIQUIDDATA AS liquiddata,
        CELL,
        DESCN,
        DEPTID,
        WELLCODE,
        WELLID,
        DEVICEID,
        VALID,
        DEVICETYPENAME
        FROM liquid_view
        <where>
            <if test="deptid != null and deptid != -1 ">
                and deptid = #{deptid}
            </if>
            <if test="beginTime != null and beginTime != '' ">
                and UPTIME <![CDATA[ >= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{beginTime}"/>
                </include>
            </if>
            <if test="endTime != null and endTime != ''">
                and UPTIME <![CDATA[ <= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{endTime}"/>
                </include>
            </if>
            <if test="keywords != null and keywords !=''">
                and (DEVCODE like
                <include refid="keywords_sql"/>
                OR wellCode LIKE
                <include refid="keywords_sql"/>
                )
            </if>
        </where>
        ORDER BY UPTIME DESC
    </select>

    <select id="getWellliquiddataList"
            resultType="com.casic.missiles.modular.system.dto.ExcelModel.DataWellLiquidDto"
            parameterType="com.baomidou.mybatisplus.extension.plugins.pagination.Page">
        SELECT bd.devcode,
        <include refid="date_toChar">
            <property name="paramTime" value="dwl.LOGTIME"/>
        </include>
        AS "logtime",
        <include refid="date_toChar">
            <property name="paramTime" value="dwl.UPTIME"/>
        </include>
        AS "uptime",
        status AS status,
        bd.device_Type as "deviceType",
        cell,
        descn,
        deptid,
        bwi.well_code as "wellCode",
        type_name as "deviceTypeName"
        FROM data_well_liquid dwl
        JOIN bus_device bd ON bd.DEVCODE=dwl.DEVCODE
        JOIN bus_well_info bwi ON dwl.WELL_CODE = bwi.WELL_CODE
        JOIN base_device_type bdt ON bd.DEVICE_TYPE = bdt.ID
        where bd.valid='1' AND bwi.valid='1'
        <if test="deptid != null and deptid != -1 ">
            and deptid = #{deptid}
        </if>
        <if test="beginTime != null and beginTime != '' ">
            and dwl.UPTIME <![CDATA[ >= ]]>
            <include refid="char_toDate">
                <property name="paramStr" value="#{beginTime}"/>
            </include>
        </if>
        <if test="endTime != null and endTime != ''">
            and dwl.UPTIME <![CDATA[ <= ]]>
            <include refid="char_toDate">
                <property name="paramStr" value="#{endTime}"/>
            </include>
        </if>
        <if test="keywords != null and keywords !=''">
            and (dwl.devcode like
            <include refid="keywords_sql"/>
            OR dwl.well_code LIKE
            <include refid="keywords_sql"/>
            )
        </if>
        ORDER BY dwl.uptime DESC
    </select>

    <select id="selectGasLiquidDataLiquidScopePage"
            resultType="com.casic.missiles.modular.system.dto.ExcelModel.GasLiquidDataExcelDto">
        SELECT
        glview.DEVCODE AS devcode ,
        bd.DEVICE_TYPE AS "deviceType",
        bdt.TYPE_NAME AS "deviceTypeName",
        bwi.DEPTID AS deptid,
        glview.WELL_CODE AS "wellCode",
        glview.DESCN AS descn,
        glview.CELL AS cell,
        <include refid="date_toChar">
            <property name="paramTime" value="glview.UPTIME"/>
        </include>
        AS uptime,
        <include refid="date_toChar">
            <property name="paramTime" value="glview.LOGTIME"/>
        </include>
        AS logtime,
        glview.LIQUIDDATA AS strength
        FROM
        data_gasliquid_liquid glview
        LEFT JOIN bus_device bd ON glview.DEVCODE = bd.DEVCODE
        LEFT JOIN bus_well_info bwi ON glview.WELL_CODE = bwi.WELL_CODE
        LEFT JOIN base_device_type bdt ON bd.DEVICE_TYPE = bdt.ID
        <where>
            bwi.VALID = '1'
            <if test="deptIds != null">
                and bwi.DEPTID in
                <foreach collection="deptIds" index="index" item="i" open="(" separator="," close=")">
                    #{i}
                </foreach>
            </if>
            <if test="beginTime != null and beginTime != '' ">
                and glview.UPTIME <![CDATA[ >= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{beginTime}"/>
                </include>
            </if>
            <if test="endTime != null and endTime != ''">
                and glview.UPTIME <![CDATA[ <= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{endTime}"/>
                </include>
            </if>
            <if test="keywords != null and keywords !=''">
                and (glview.DEVCODE like
                <include refid="keywords_sql"/>
                OR glview.WELL_CODE LIKE
                <include refid="keywords_sql"/>
                )
            </if>
        </where>
        ORDER BY glview.UPTIME DESC
    </select>

    <select id="selectLiquidDataList" resultType="map">
        SELECT
        devcode,
        deviceType as "deviceType",
        deviceTypeName as "deviceTypeName",
        deptid,
        wellCode as "wellCode",
        descn,
        cell,
        uptime,
        logtime,
        liquiddata
        FROM
        (

        SELECT
        dl.DEVCODE AS devcode,
        bd.DEVICE_TYPE AS deviceType,
        bdt.TYPE_NAME AS deviceTypeName,
        bwi.DEPTID AS deptid,
        dl.WELL_CODE AS wellCode,
        dl.DESCN AS descn,
        dl.CELL AS cell,
        <include refid="date_toChar">
            <property name="paramTime" value="dl.UPTIME"/>
        </include>
        AS uptime,
        <include refid="date_toChar">
            <property name="paramTime" value="dl.LOGTIME"/>
        </include>
        AS logtime,
        dl.LIQUIDDATA AS liquiddata
        FROM
        data_liquid dl
        INNER JOIN bus_device bd ON dl.DEVCODE = bd.DEVCODE
        INNER JOIN bus_well_info bwi ON dl.WELL_CODE = bwi.WELL_CODE
        INNER JOIN base_device_type bdt ON bd.DEVICE_TYPE = bdt.ID
        <where>
            bd.VALID = '1' AND bwi.VALID = '1'
            <if test="wellCode != null and wellCode != ''">
                AND dl.WELL_CODE LIKE
                <include refid="wellCode_sql"/>
            </if>
            <if test="beginTime != null and beginTime != ''">
                AND dl.UPTIME <![CDATA[ >= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{beginTime}"/>
                </include>
            </if>
            <if test="endTime != null and endTime != ''">
                AND dl.UPTIME <![CDATA[ <= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{endTime}"/>
                </include>
            </if>
        </where>
        UNION ALL

        SELECT
        dgl.DEVCODE AS devcode,
        bd.DEVICE_TYPE AS deviceType,
        bdt.TYPE_NAME AS deviceTypeName,
        bwi.DEPTID AS deptid,
        dgl.WELL_CODE AS wellCode,
        dgl.DESCN AS descn,
        dgl.CELL AS cell,
        <include refid="date_toChar">
            <property name="paramTime" value="dgl.UPTIME"/>
        </include>
        AS uptime,
        <include refid="date_toChar">
            <property name="paramTime" value="dgl.LOGTIME"/>
        </include>
        AS logtime,
        dgl.LIQUIDDATA AS liquiddata
        FROM
        data_gasliquid_liquid dgl
        INNER JOIN bus_device bd ON dgl.DEVCODE = bd.DEVCODE
        INNER JOIN bus_well_info bwi ON dgl.WELL_CODE = bwi.WELL_CODE
        INNER JOIN base_device_type bdt ON bd.DEVICE_TYPE = bdt.ID
        <where>
            bd.VALID = '1' AND bwi.VALID = '1'
            <if test="wellCode != null and wellCode != ''">
                AND dgl.WELL_CODE LIKE
                <include refid="wellCode_sql"/>
            </if>
            <if test="beginTime != null and beginTime != ''">
                AND dgl.UPTIME <![CDATA[ >= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{beginTime}"/>
                </include>
            </if>
            <if test="endTime != null and endTime != ''">
                AND dgl.UPTIME <![CDATA[ <= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{endTime}"/>
                </include>
            </if>
        </where>
        ) t1
        ORDER BY uptime asc
    </select>

    <select id="selectTempDataList" resultType="map">
        SELECT
        dth.DEVCODE AS devcode,
        bd.DEVICE_TYPE AS "deviceType",
        bdt.TYPE_NAME AS "deviceTypeName",
        bwi.DEPTID AS "deptid",
        dth.WELL_CODE AS "wellCode",
        dth.DESCN AS descn,
        dth.CELL AS cell,
        dth.TEMPERATURE AS temperature,
        dth.HUMIDITY AS humidity,
        <include refid="date_toChar">
            <property name="paramTime" value="dth.UPTIME"/>
        </include>
        AS uptime,
        <include refid="date_toChar">
            <property name="paramTime" value="dth.LOGTIME"/>
        </include>
        AS logtime
        FROM
        data_temp_hum dth
        INNER JOIN bus_device bd ON dth.DEVCODE = bd.DEVCODE
        INNER JOIN bus_well_info bwi ON dth.WELL_CODE = bwi.WELL_CODE
        INNER JOIN base_device_type bdt ON bd.DEVICE_TYPE = bdt.ID
        <where>
            bd.VALID = '1' AND bwi.VALID = '1'
            <if test="wellCode != null and wellCode != ''">
                AND dth.WELL_CODE LIKE
                <include refid="wellCode_sql"/>
            </if>
            <if test="beginTime != null and beginTime != ''">
                AND dth.UPTIME <![CDATA[ >= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{beginTime}"/>
                </include>
            </if>
            <if test="endTime != null and endTime != ''">
                AND dth.UPTIME <![CDATA[ <= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{endTime}"/>
                </include>
            </if>
        </where>
        ORDER BY uptime asc
    </select>

    <select id="selectHarmfulDataList" resultType="map">
        SELECT
        dhg.DEVCODE AS devcode,
        bd.DEVICE_TYPE AS "deviceType",
        bdt.TYPE_NAME AS "deviceTypeName",
        bwi.DEPTID AS deptid,
        dhg.WELL_CODE AS "wellCode",
        dhg.DESCN AS descn,
        dhg.CO AS "CO",
        dhg.CH4 AS "CH4",
        dhg.H2S AS "H2S",
        dhg.O2 AS "O2",
        dhg.SWITCH AS switch,
        <include refid="date_toChar">
            <property name="paramTime" value="dhg.UPTIME"/>
        </include>
        AS uptime,
        <include refid="date_toChar">
            <property name="paramTime" value="dhg.LOGTIME"/>
        </include>
        AS logtime
        FROM
        data_harmful_gas dhg
        INNER JOIN bus_device bd ON dhg.DEVCODE = bd.DEVCODE
        INNER JOIN bus_well_info bwi ON dhg.WELL_CODE = bwi.WELL_CODE
        INNER JOIN base_device_type bdt ON bd.DEVICE_TYPE = bdt.ID
        <where>
            bd.VALID = '1' AND bwi.VALID = '1'
            <if test="wellCode != null and wellCode != ''">
                AND dhg.WELL_CODE LIKE
                <include refid="wellCode_sql"/>
            </if>
            <if test="beginTime != null and beginTime != ''">
                AND dhg.UPTIME <![CDATA[ >= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{beginTime}"/>
                </include>
            </if>
            <if test="endTime != null and endTime != ''">
                AND dhg.UPTIME <![CDATA[ <= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{endTime}"/>
                </include>
            </if>
        </where>
        ORDER BY uptime asc
    </select>

    <select id="selectGasDataList" resultType="map">
        SELECT
        devcode,
        deviceType AS "deviceType",
        deviceTypeName AS "deviceTypeName",
        deptid,
        wellCode AS "wellCode",
        descn,
        cell,
        uptime,
        logtime,
        strength
        FROM
        (
        SELECT
        dg.DEVCODE AS devcode,
        bd.DEVICE_TYPE AS deviceType,
        bdt.TYPE_NAME AS deviceTypeName,
        bwi.DEPTID AS deptid,
        dg.WELL_CODE AS wellCode,
        dg.DESCN AS descn,
        dg.CELL AS cell,
        <include refid="date_toChar">
            <property name="paramTime" value="dg.UPTIME"/>
        </include>
        AS uptime,
        <include refid="date_toChar">
            <property name="paramTime" value="dg.LOGTIME"/>
        </include>
        AS logtime,
        dg.STRENGTH AS strength
        FROM
        data_gas dg
        INNER JOIN bus_device bd ON dg.DEVCODE = bd.DEVCODE
        INNER JOIN bus_well_info bwi ON dg.WELL_CODE = bwi.WELL_CODE
        INNER JOIN base_device_type bdt ON bd.DEVICE_TYPE = bdt.ID
        <where>
            bd.VALID = '1' AND bwi.VALID = '1'
            <if test="wellCode != null and wellCode != ''">
                AND dg.WELL_CODE LIKE
                <include refid="wellCode_sql"/>
            </if>
            <if test="beginTime != null and beginTime != ''">
                AND dg.UPTIME <![CDATA[ >= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{beginTime}"/>
                </include>
            </if>
            <if test="endTime != null and endTime != ''">
                AND dg.UPTIME <![CDATA[ <= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{endTime}"/>
                </include>
            </if>
        </where>

        UNION ALL

        SELECT
        dgg.DEVCODE AS devcode,
        bd.DEVICE_TYPE AS deviceType,
        bdt.TYPE_NAME AS deviceTypeName,
        bwi.DEPTID AS deptid,
        dgg.WELL_CODE AS wellCode,
        dgg.DESCN AS descn,
        dgg.CELL AS cell,
        <include refid="date_toChar">
            <property name="paramTime" value="dgg.UPTIME"/>
        </include>
        AS uptime,
        <include refid="date_toChar">
            <property name="paramTime" value="dgg.LOGTIME"/>
        </include>
        AS logtime,
        dgg.STRENGTH AS strength
        FROM
        data_gasliquid_gas dgg
        INNER JOIN bus_device bd ON dgg.DEVCODE = bd.DEVCODE
        INNER JOIN bus_well_info bwi ON dgg.WELL_CODE = bwi.WELL_CODE
        INNER JOIN base_device_type bdt ON bd.DEVICE_TYPE = bdt.ID
        <where>
            bd.VALID = '1' AND bwi.VALID = '1'
            <if test="wellCode != null and wellCode != ''">
                AND dgg.WELL_CODE LIKE
                <include refid="wellCode_sql"/>
            </if>
            <if test="beginTime != null and beginTime != ''">
                AND dgg.UPTIME <![CDATA[ >= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{beginTime}"/>
                </include>
            </if>
            <if test="endTime != null and endTime != ''">
                AND dgg.UPTIME <![CDATA[ <= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{endTime}"/>
                </include>
            </if>
        </where>
        ) t1
        ORDER BY uptime asc
    </select>

    <select id="selectWellDataScopePage" resultType="com.casic.missiles.modular.system.dto.ExcelModel.WellCoverDataDto"
            parameterType="com.baomidou.mybatisplus.extension.plugins.pagination.Page">
        SELECT
        DEVCODE,
        deviceType AS "deviceType",
        <include refid="date_toChar">
            <property name="paramTime" value="LOGTIME"/>
        </include>
        as "LOGTIME",
        DESCN,
        STATUS,
        DEPTID,
        wellcode as "wellcode",
        wellId AS "wellId",
        deviceId AS "deviceId",
        valid,
        deviceTypeName AS "deviceTypeName"
        FROM wellcover_monitorview
        <where>
            <if test="deptid != null and deptid !=-1 ">
                and deptid = #{deptid}
            </if>
            <if test="beginTime != null and beginTime != '' ">
                and LOGTIME  <![CDATA[ >= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{beginTime}"/>
                </include>
            </if>
            <if test="endTime != null and endTime != ''">
                and LOGTIME <![CDATA[ <= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{endTime}"/>
                </include>
            </if>
            <if test="keywords != null and keywords !=''">
                and (DEVCODE like
                <include refid="keywords_sql"/>
                OR wellcode LIKE
                <include refid="keywords_sql"/>
                )
            </if>
        </where>
        ORDER BY logtime DESC
    </select>

    <select id="selectGasDataScopePage" resultType="com.casic.missiles.modular.system.dto.ExcelModel.DeviceDataExcelDto"
            parameterType="com.baomidou.mybatisplus.extension.plugins.pagination.Page">
        SELECT
        dg.DEVCODE AS devcode ,
        bd.DEVICE_TYPE AS "deviceType",
        bdt.TYPE_NAME AS "deviceTypeName",
        bwi.DEPTID AS deptid,
        dg.WELL_CODE AS "wellCode",
        dg.DESCN AS descn,
        dg.CELL AS cell,
        <include refid="date_toChar">
            <property name="paramTime" value="dg.UPTIME"/>
        </include>
        AS uptime,
        <include refid="date_toChar">
            <property name="paramTime" value="dg.LOGTIME"/>
        </include>
        AS logtime,
        dg.STRENGTH AS strength
        FROM
        data_gas dg
        INNER JOIN bus_device bd ON dg.DEVCODE = bd.DEVCODE
        INNER JOIN bus_well_info bwi ON dg.WELL_CODE = bwi.WELL_CODE
        INNER JOIN base_device_type bdt ON bd.DEVICE_TYPE = bdt.ID
        <where>
            bd.VALID = '1' AND bwi.VALID = '1'
            <if test="deptid != null and deptid != -1 ">
                and bwi.DEPTID = #{deptid}
            </if>
            <if test="beginTime != null and beginTime != '' ">
                and dg.UPTIME  <![CDATA[ >= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{beginTime}"/>
                </include>
            </if>
            <if test="endTime != null and endTime != ''">
                and dg.UPTIME <![CDATA[ <= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{endTime}"/>
                </include>
            </if>
            <if test="keywords != null and keywords !=''">
                and (dg.DEVCODE like
                <include refid="keywords_sql"/>
                OR dg.WELL_CODE LIKE
                <include refid="keywords_sql"/>
                )
            </if>
        </where>
        ORDER BY dg.UPTIME DESC
    </select>

    <select id="selectTubeDataScopePage"
            resultType="com.casic.missiles.modular.system.dto.ExcelModel.DeviceDataExcelDto">
        SELECT
        dg.DEVCODE AS devcode ,
        bd.DEVICE_TYPE AS "deviceType",
        bdt.TYPE_NAME AS "deviceTypeName",
        bwi.DEPTID AS deptid,
        dg.WELL_CODE AS "wellCode",
        dg.DESCN AS descn,
        dg.CELL AS cell,
        <include refid="date_toChar">
            <property name="paramTime" value="dg.UPTIME"/>
        </include>
        AS uptime,
        <include refid="date_toChar">
            <property name="paramTime" value="dg.LOGTIME"/>
        </include>
        AS logtime,
        dg.STRENGTH AS strength
        FROM
        data_tube dg
        INNER JOIN bus_device bd ON dg.DEVCODE = bd.DEVCODE
        INNER JOIN bus_well_info bwi ON dg.WELL_CODE = bwi.WELL_CODE
        INNER JOIN base_device_type bdt ON bd.DEVICE_TYPE = bdt.ID
        <where>
            bd.VALID = '1' AND bwi.VALID = '1'
            <if test="deptid != null and deptid != -1 ">
                and bwi.DEPTID = #{deptid}
            </if>
            <choose>
                <when test="beginTime != null and beginTime != '' and endTime != null and endTime != ''">
                    AND dg.UPTIME <![CDATA[ >= ]]>
                    <include refid="char_toDate">
                        <property name="paramStr" value="#{beginTime}"/>
                    </include>
                    AND dg.UPTIME <![CDATA[ <= ]]>
                    <include refid="char_toDate">
                        <property name="paramStr" value="#{endTime}"/>
                    </include>
                </when>
                <otherwise>
                    <include refid="data_diff">
                        <property name="paramDay" value="30"/>
                    </include>
                </otherwise>
            </choose>

            <if test="keywords != null and keywords !=''">
                and (dg.DEVCODE like
                <include refid="keywords_sql"/>
                OR dg.WELL_CODE LIKE
                <include refid="keywords_sql"/>
                )
            </if>
        </where>
        ORDER BY dg.UPTIME DESC
    </select>

    <select id="selectHarmfulDataScopePage" resultType="com.casic.missiles.modular.system.dto.ExcelModel.HarmfulDataDto"
            parameterType="com.baomidou.mybatisplus.extension.plugins.pagination.Page">
        SELECT
        dhg.DEVCODE AS devcode,
        bd.DEVICE_TYPE AS "deviceType",
        bdt.TYPE_NAME AS "deviceTypeName",
        bwi.DEPTID AS deptid,
        dhg.WELL_CODE AS "wellCode",
        dhg.DESCN AS descn,
        dhg.CO AS "CO",
        dhg.CH4 AS "CH4",
        dhg.H2S AS "H2S",
        dhg.O2 AS "O2",
        dhg.SWITCH AS "liquidSwitch",
        <include refid="date_toChar">
            <property name="paramTime" value="dhg.UPTIME"/>
        </include>
        AS uptime,
        <include refid="date_toChar">
            <property name="paramTime" value="dhg.LOGTIME"/>
        </include>
        AS logtime
        FROM
        data_harmful_gas dhg
        INNER JOIN bus_device bd ON dhg.DEVCODE = bd.DEVCODE
        INNER JOIN bus_well_info bwi ON dhg.WELL_CODE = bwi.WELL_CODE
        INNER JOIN base_device_type bdt ON bd.DEVICE_TYPE = bdt.ID
        <where>
            bd.VALID = '1' AND bwi.VALID = '1'
            <if test="deptid != null and deptid != -1 ">
                and bwi.DEPTID = #{deptid}
            </if>
            <if test="beginTime != null and beginTime != '' ">
                and dhg.UPTIME  <![CDATA[ >= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{beginTime}"/>
                </include>
            </if>
            <if test="endTime != null and endTime != ''">
                and dhg.UPTIME <![CDATA[ <= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{endTime}"/>
                </include>
            </if>
            <if test="keywords != null and keywords !=''">
                and (dhg.DEVCODE like
                <include refid="keywords_sql"/>
                OR dhg.WELL_CODE LIKE
                <include refid="keywords_sql"/>
                )
            </if>
        </where>
        ORDER BY dhg.UPTIME DESC
    </select>

    <select id="selectTempDataScopePage" resultType="com.casic.missiles.modular.system.dto.ExcelModel.TemhumDataDto"
            parameterType="com.baomidou.mybatisplus.extension.plugins.pagination.Page">
        SELECT
        dth.DEVCODE AS devcode,
        bd.DEVICE_TYPE AS "deviceType",
        bdt.TYPE_NAME AS "deviceTypeName",
        bwi.DEPTID AS deptid,
        dth.WELL_CODE AS "wellCode",
        dth.DESCN AS descn,
        dth.CELL AS cell,
        dth.TEMPERATURE AS temperature,
        dth.HUMIDITY AS humidity,
        <include refid="date_toChar">
            <property name="paramTime" value="dth.UPTIME"/>
        </include>
        AS uptime,
        <include refid="date_toChar">
            <property name="paramTime" value="dth.LOGTIME"/>
        </include>
        AS logtime
        FROM
        data_temp_hum dth
        INNER JOIN bus_device bd ON dth.DEVCODE = bd.DEVCODE
        INNER JOIN bus_well_info bwi ON dth.WELL_CODE = bwi.WELL_CODE
        INNER JOIN base_device_type bdt ON bd.DEVICE_TYPE = bdt.ID
        <where>
            bd.VALID = '1' AND bwi.VALID = '1'
            <if test="deptid != null and deptid != -1 ">
                and bwi.DEPTID = #{deptid}
            </if>
            <if test="beginTime != null and beginTime != '' ">
                and dth.UPTIME  <![CDATA[ >= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{beginTime}"/>
                </include>
            </if>
            <if test="endTime != null and endTime != ''">
                and dth.UPTIME  <![CDATA[ <= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{endTime}"/>
                </include>
            </if>
            <if test="keywords != null and keywords !=''">
                and (dth.DEVCODE like
                <include refid="keywords_sql"/>
                OR dth.WELL_CODE LIKE
                <include refid="keywords_sql"/>
                )
            </if>
        </where>
        ORDER BY dth.UPTIME DESC
    </select>

    <select id="selectTempPressDataScopePage"
            resultType="com.casic.missiles.modular.system.dto.ExcelModel.TemppressDataDto"
            parameterType="com.baomidou.mybatisplus.extension.plugins.pagination.Page">
        SELECT
        dth.DEVCODE AS "devcode",
        bd.DEVICE_TYPE AS "deviceType",
        bdt.TYPE_NAME AS "deviceTypeName",
        bwi.DEPTID AS deptid,
        dth.WELL_CODE AS "wellCode",
        dth.DESCN AS descn,
        dth.CELL AS cell,
        dth.TEMPERATURE AS temperature,
        dth.PRESS AS press,
        <include refid="date_toChar">
            <property name="paramTime" value="dth.UPTIME"/>
        </include>
        AS uptime,
        <include refid="date_toChar">
            <property name="paramTime" value="dth.LOGTIME"/>
        </include>
        AS logtime
        FROM
        data_temp_press dth
        INNER JOIN bus_device bd ON dth.DEVCODE = bd.DEVCODE
        INNER JOIN bus_well_info bwi ON dth.WELL_CODE = bwi.WELL_CODE
        INNER JOIN base_device_type bdt ON bd.DEVICE_TYPE = bdt.ID
        <where>
            bd.VALID = '1' AND bwi.VALID = '1'
            <if test="deptid != null and deptid != -1 ">
                and bwi.DEPTID = #{deptid}
            </if>
            <if test="beginTime != null and beginTime != '' ">
                and dth.UPTIME <![CDATA[ >= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{beginTime}"/>
                </include>
            </if>
            <if test="endTime != null and endTime != ''">
                and dth.UPTIME <![CDATA[ <= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{endTime}"/>
                </include>
            </if>
            <if test="keywords != null and keywords !=''">
                and (dth.DEVCODE like
                <include refid="keywords_sql"/>
                OR dth.WELL_CODE LIKE
                <include refid="keywords_sql"/>
                )
            </if>
        </where>
        ORDER BY dth.UPTIME DESC
    </select>

    <select id="selectDigDataScopePage" resultType="com.casic.missiles.modular.system.dto.ExcelModel.DigNoiseDataDto"
            parameterType="com.baomidou.mybatisplus.extension.plugins.pagination.Page">
        SELECT
        dd.DEVCODE AS devcode,
        bd.DEVICE_TYPE AS "deviceType",
        bdt.TYPE_NAME AS "deviceTypeName",
        bwi.DEPTID AS deptid,
        dd.WELL_CODE AS "wellCode",
        dd.DESCN AS descn,
        dd.DDATA AS ddata,
        dd.CELL AS cell,
        dd.FREQUENCY AS frequency,
        <include refid="date_toChar">
            <property name="paramTime" value="dd.UPTIME"/>
        </include>
        AS uptime,
        <include refid="date_toChar">
            <property name="paramTime" value="dd.LOGTIME"/>
        </include>
        AS logtime
        FROM
        data_dig dd
        INNER JOIN bus_device bd ON dd.DEVCODE = bd.DEVCODE
        INNER JOIN bus_well_info bwi ON dd.WELL_CODE = bwi.WELL_CODE
        INNER JOIN base_device_type bdt ON bd.DEVICE_TYPE = bdt.ID
        <where>
            bd.VALID = '1' AND bwi.VALID = '1'
            <if test="deptid != null and deptid != -1 ">
                and bwi.DEPTID = #{deptid}
            </if>
            <if test="beginTime != null and beginTime != '' ">
                and dd.UPTIME  <![CDATA[ >= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{beginTime}"/>
                </include>
            </if>
            <if test="endTime != null and endTime != ''">
                and dd.UPTIME  <![CDATA[ <= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{endTime}"/>
                </include>
            </if>
            <if test="keywords != null and keywords !=''">
                and (dd.DEVCODE like
                <include refid="keywords_sql"/>
                OR dd.WELL_CODE LIKE
                <include refid="keywords_sql"/>
                )
            </if>
        </where>
        ORDER BY dd.UPTIME DESC
    </select>

    <select id="selectWellLocDataScopePage"
            resultType="com.casic.missiles.modular.system.dto.ExcelModel.WellAppointDataDto"
            parameterType="com.baomidou.mybatisplus.extension.plugins.pagination.Page">
        SELECT
        dwl.DEVCODE AS devcode,
        bd.DEVICE_TYPE AS "deviceType",
        bdt.TYPE_NAME AS "deviceTypeName",
        bwi.DEPTID AS deptid,
        dwl.WELL_CODE AS "wellCode",
        dwl.DESCN AS descn,
        dwl.CELL AS cell,
        dwl.LAT AS lat,
        dwl.LNG AS lng,
        <include refid="date_toChar">
            <property name="paramTime" value="dwl.UPTIME"/>
        </include>
        AS uptime,
        <include refid="date_toChar">
            <property name="paramTime" value="dwl.LOGTIME"/>
        </include>
        AS logtime
        FROM
        data_wellcover_loca dwl
        INNER JOIN bus_device bd ON dwl.DEVCODE = bd.DEVCODE
        INNER JOIN bus_well_info bwi ON dwl.WELL_CODE = bwi.WELL_CODE
        INNER JOIN base_device_type bdt ON bd.DEVICE_TYPE = bdt.ID
        <where>
            bd.VALID = '1' AND bwi.VALID = '1'
            <if test="deptid != null and deptid != -1 ">
                and bwi.DEPTID = #{deptid}
            </if>
            <if test="beginTime != null and beginTime != '' ">
                and dwl.UPTIME  <![CDATA[ >= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{beginTime}"/>
                </include>
            </if>
            <if test="endTime != null and endTime != ''">
                and dwl.UPTIME <![CDATA[ <= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{endTime}"/>
                </include>
            </if>
            <if test="keywords != null and keywords !=''">
                and (dwl.DEVCODE like
                <include refid="keywords_sql"/>
                OR dwl.WELL_CODE LIKE
                <include refid="keywords_sql"/>
                )
            </if>
        </where>
        ORDER BY dwl.UPTIME DESC
    </select>

    <select id="selectNoiseDataScopePage" resultType="com.casic.missiles.modular.system.dto.ExcelModel.DigNoiseDataDto">
        SELECT
        dn.DEVCODE AS devcode,
        bd.DEVICE_TYPE AS "deviceType",
        bdt.TYPE_NAME AS "deviceTypeName",
        bwi.DEPTID AS deptid,
        dn.WELL_CODE AS "wellCode",
        dn.DESCN AS descn,
        dn.FREQUENCY AS frequency,
        dn.DDATA AS ddata,
        dn.CELL AS cell,
        <include refid="date_toChar">
            <property name="paramTime" value="dn.UPTIME"/>
        </include>
        AS uptime,
        <include refid="date_toChar">
            <property name="paramTime" value="dn.LOGTIME"/>
        </include>
        AS logtime
        FROM
        data_noise dn
        INNER JOIN bus_device bd ON dn.DEVCODE = bd.DEVCODE
        INNER JOIN bus_well_info bwi ON dn.WELL_CODE = bwi.WELL_CODE
        INNER JOIN base_device_type bdt ON bd.DEVICE_TYPE = bdt.ID
        <where>
            bd.VALID = '1' AND bwi.VALID = '1'
            <if test="deptid != null and deptid != -1 ">
                and bwi.DEPTID = #{deptid}
            </if>
            <if test="beginTime != null and beginTime != '' ">
                and dn.UPTIME <![CDATA[ >= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{beginTime}"/>
                </include>
            </if>
            <if test="endTime != null and endTime != ''">
                and dn.UPTIME <![CDATA[ <= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{endTime}"/>
                </include>
            </if>
            <if test="keywords != null and keywords !=''">
                and (dn.DEVCODE like
                <include refid="keywords_sql"/>
                OR dn.WELL_CODE LIKE
                <include refid="keywords_sql"/>
                )
            </if>
        </where>
        ORDER BY dn.UPTIME DESC
    </select>

    <select id="alarmsStaticsByDept" resultType="com.casic.missiles.modular.system.dto.AlarmDeptStaticsDto">
        SELECT
        SD.simple_name AS deptname,
        SA.alarmDevices,
        SA.alarmTimes,
        SD.ID AS "deptid"
        FROM sys_dept SD
        LEFT JOIN
        (
        SELECT
        count(1) AS alarmDevices,
        sum(NUM) AS alarmTimes,
        DEPTID
        FROM
        (
        SELECT
        count(1) AS NUM,DEPTID,b.DEVICE_ID
        FROM alarm_records a
        INNER JOIN bus_device_well b ON a.DEVICE_ID = b.DEVICE_ID
        INNER JOIN bus_well_info c ON c.ID = b.WELL_ID
        INNER JOIN bus_device d ON d.ID = b.DEVICE_ID
        WHERE
        b.VALID = '1' AND c.VALID = '1' AND d.VALID = '1'
        <if test="beginTime != null and beginTime != '' ">
            and a.ALARM_TIME <![CDATA[ >= ]]>
            <include refid="char_toDate">
                <property name="paramStr" value="#{beginTime}"/>
            </include>
        </if>
        <if test="endTime != null and endTime != ''">
            and a.ALARM_TIME <![CDATA[ <= ]]>
            <include refid="char_toDate">
                <property name="paramStr" value="#{endTime}"/>
            </include>
        </if>
        <if test="deviceType != null and deviceType != ''">
            and d.DEVICE_TYPE = #{deviceType}
        </if>
        GROUP BY
        c.DEPTID,
        b.DEVICE_ID
        ) A
        GROUP BY DEPTID
        ) SA ON SA.DEPTID = SD.ID
        <where>
            AND SD.dept_type = '03'
            <if test="deptid != -1 ">
                <choose>
                    <when test="searchCurr">
                        and SD.ID = #{deptid}
                    </when>
                    <otherwise>
                        AND SD.PID = #{deptid}
                    </otherwise>
                </choose>
            </if>
        </where>
    </select>

    <select id="selectGasLiquidDataGasScopePage"
            resultType="com.casic.missiles.modular.system.dto.ExcelModel.GasLiquidDataExcelDto">
        SELECT
        glview.DEVCODE AS devcode ,
        bd.DEVICE_TYPE AS "deviceType",
        bdt.TYPE_NAME AS "deviceTypeName",
        bwi.DEPTID AS deptid,
        glview.WELL_CODE AS "wellCode",
        glview.DESCN AS descn,
        glview.CELL AS cell,
        <include refid="date_toChar">
            <property name="paramTime" value="glview.UPTIME"/>
        </include>
        AS uptime,
        <include refid="date_toChar">
            <property name="paramTime" value="glview.LOGTIME"/>
        </include>
        AS logtime,
        glview.STRENGTH AS data
        FROM
        data_gasliquid_gas glview
        LEFT JOIN bus_device bd ON glview.DEVCODE = bd.DEVCODE
        LEFT JOIN bus_well_info bwi ON glview.WELL_CODE = bwi.WELL_CODE
        LEFT JOIN base_device_type bdt ON bd.DEVICE_TYPE = bdt.ID
        <where>
            bwi.VALID = '1'
            <if test="deptIds != null">
                and bwi.DEPTID in
                <foreach collection="deptIds" index="index" item="i" open="(" separator="," close=")">
                    #{i}
                </foreach>
            </if>
            <if test="beginTime != null and beginTime != '' ">
                and glview.UPTIME <![CDATA[ >= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{beginTime}"/>
                </include>
            </if>
            <if test="endTime != null and endTime != ''">
                and glview.UPTIME <![CDATA[ <= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{endTime}"/>
                </include>
            </if>
            <if test="keywords != null and keywords !=''">
                and (glview.DEVCODE like
                <include refid="keywords_sql"/>
                OR glview.WELL_CODE LIKE
                <include refid="keywords_sql"/>
                )
            </if>
        </where>
        ORDER BY glview.UPTIME DESC
    </select>

    <select id="getHydrantlist" resultType="com.casic.missiles.modular.system.dto.ExcelModel.HydrantDataDto">
        SELECT bd.DEVCODE AS devcode,bd.DEVICE_TYPE AS deviceType,bdt.TYPE_NAME AS
        "deviceTypeName",bwi.deptid,bwi.WELL_CODE AS "wellCode",
        dh.STATUS AS "status",
        <include refid="date_toChar">
            <property name="paramTime" value="dh.LOGTIME"/>
        </include>
        AS logtime,dh.DESCN AS descn
        FROM data_hydrant dh
        JOIN bus_device bd ON bd.DEVCODE= dh.DEVCODE
        JOIN bus_device_well bdw ON bdw.device_id=bd.id
        JOIN bus_well_info bwi ON bwi.id= bdw.well_id
        JOIN base_device_type bdt ON bd.DEVICE_TYPE=bdt.ID
        WHERE 1 = 1
        <if test="keywords != null and keywords !=''">
            AND (dh.DEVCODE LIKE
            <include refid="keywords_sql"/>
            OR dh.WELL_CODE LIKE<include refid="keywords_sql"/>)
        </if>
        <if test="deptid != null and deptid != -1 ">
            and bwi.DEPTID = #{deptid}
        </if>
        <if test="beginTime != null and beginTime != '' ">
            and dh.LOGTIME <![CDATA[ >= ]]>
            <include refid="char_toDate">
                <property name="paramStr" value="#{beginTime}"/>
            </include>
        </if>
        <if test="endTime != null and endTime != ''">
            and dh.LOGTIME <![CDATA[ <= ]]>
            <include refid="char_toDate">
                <property name="paramStr" value="#{endTime}"/>
            </include>
        </if>
        ORDER BY dh.LOGTIME DESC
    </select>

    <select id="selectH2sListDataScopePage" resultType="com.casic.missiles.modular.system.dto.ExcelModel.H2SDataDto">
        SELECT
        dh2s.DEVCODE AS devcode,
        dh2s.WELL_CODE AS "wellCode",
        bwi.WELL_NAME AS "wellName",
        dh2s.STRENGTH AS strength,
        dh2s.CELL AS cell,
        <include refid="date_toChar">
            <property name="paramTime" value="dh2s.UPTIME"/>
        </include>
        AS uptime,
        <include refid="date_toChar">
            <property name="paramTime" value="dh2s.LOGTIME"/>
        </include>
        AS logtime,
        bdt.TYPE_NAME AS "deviceTypeName",
        bd.device_type as deviceType,
        bwi.DEPTID AS deptid
        FROM data_h2s dh2s
        JOIN bus_well_info bwi ON bwi.WELL_CODE= dh2s.WELL_CODE
        JOIN bus_device bd ON dh2s.DEVCODE = bd.DEVCODE
        LEFT JOIN base_device_type bdt ON bd.DEVICE_TYPE = bdt.ID
        <where>
            bd.VALID = '1' AND bwi.VALID = '1'
            <if test="deptid != null and deptid != -1 ">
                and bwi.DEPTID = #{deptid}
            </if>
            <if test="beginTime != null and beginTime != '' ">
                and dh2s.UPTIME <![CDATA[ >= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{beginTime}"/>
                </include>
            </if>
            <if test="endTime != null and endTime != ''">
                and dh2s.UPTIME <![CDATA[ <= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{endTime}"/>
                </include>
            </if>
            <if test="keywords != null and keywords !=''">
                AND (dh2s.DEVCODE LIKE
                <include refid="keywords_sql"/>
                OR dh2s.WELL_CODE LIKE<include refid="keywords_sql"/>)
            </if>
        </where>
        ORDER BY  uptime DESC
    </select>

</mapper>
