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

    <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>


    <select id="deviceList" resultType="com.casic.missiles.modular.system.model.DeviceMapVO">
        SELECT id AS "id",lat AS "lat",lng AS "lng",devicename AS "name",dv.devcode as "code",
        position AS "location",onlineState AS "status",wellName AS "wellName"
        FROM device_view dv
        WHERE 1=1
        <if test="deviceType != null and deviceType !=''">
            AND deviceType=#{deviceType}
        </if>
        <if test="keywords != null and keywords !=''">
            and (dv.DEVCODE like
            <include refid="keywords_sql"/>
            OR wellCode LIKE
            <include refid="keywords_sql"/>
            )
        </if>

    </select>

    <select id="deviceValue" resultType="com.casic.missiles.modular.system.model.DeviceValueMapVO">
        SELECT strength AS "value",devcode AS "code",
        <include refid="date_toChar">
            <property name="paramTime" value="LOGTIME"/>
        </include>
        AS "logtime"
        FROM data_h2s
        WHERE devcode =#{devcode}
        order by logtime desc
        limit 1
    </select>

</mapper>