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

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

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

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

    <sql id="char_toDate" databaseId="mysql">
       str_to_date(${paramStr},'%Y-%m-%d %H:%i')
    </sql>

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

    <sql id="char_toDate" databaseId="oracle">
        TO_DATE(${paramStr},'yyyy-mm-dd hh24:mi')
    </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="MAP">
      SELECT  bwi.WELL_CODE  AS  "wellCode",WELL_NAME AS  "wellName",DEEP AS  "deep",
                  LNG_GAODE  AS  "lngGaode",LAT_GAODE  AS  "latGaode",AREA  AS "area",POSITION  AS "position",
                  RESPONSIBLE_DEPT AS  "responsibleDept",PHOTOS  AS  "photos",WELL_TYPE AS  "wellType",STAFF AS "staff",TEL AS "tel" ,
                  bwi.VALID  AS  "valid",BFZT  AS  "bfzt",WELL_FLAG_1 AS  "groupId",DEPTID as  "deptid"
      FROM  bus_well_info  bwi,
      bus_device_well   bdw,
      bus_device  bd
      WHERE  bd.DEVICE_TYPE='16'
      AND   bdw.WELL_ID=bwi.ID
      AND   bd.ID=bdw.DEVICE_ID
    </select>

    <select id="deviceGroupsList" resultType="MAP">
      SELECT INSTALL_DATE AS "installDate",bwi.LNG_GAODE AS "lngGaode",bwi.LAT_GAODE AS "latGaode",bwi.WELL_FLAG_1 AS  "groupId",
             bwi.WELL_CODE  AS  "wellCode",bwi.Id AS  "wellId",DEPTID as  "deptid"
      FROM  bus_device    bd,
			bus_well_info bwi,
      bus_device_well bdw
      WHERE  bd.DEVICE_TYPE='16'
      AND   bdw.WELL_ID=bwi.ID
      AND  bdw.DEVICE_ID = bd.ID
	  GROUP BY   WELL_FLAG_1
    </select>

    <select id="groupDeviceListPage" resultType="MAP">
       SELECT    bd.DEVCODE AS "devcode",bd.DEVICE_NAME "deviceName",bwi.WELL_CODE  AS  "wellCode",WELL_NAME AS  "wellName",
                  RESPONSIBLE_DEPT AS  "responsibleDept",STAFF AS "staff",TEL AS "tel",
                  WELL_FLAG_1 AS  "groupId",DEPTID as  "deptid"
        FROM  bus_well_info  bwi,
        bus_device_well   bdw,
        bus_device  bd
        WHERE  bd.DEVICE_TYPE='16'
        AND   bwi.WELL_FLAG_1='1'
		GROUP BY  bd.DEVCODE
    </select>

    <select id="dataTubeByDevcode" resultType="MAP">
        SELECT DEVCODE AS "devcode",STRENGTH AS "strength",
        <include refid="date_toChar">
            <property name="paramTime" value="UPTIME"/>
        </include>
        AS "uptime",
        pci AS "pci",rsrp AS "rsrp",snr AS "snr",cell AS "cell",ID AS "tubeId"
        FROM data_tube
        WHERE DEVCODE=#{devcode}
        ORDER BY UPTIME desc
        LIMIT 1
    </select>

    <select id="tubeDataHistory" resultType="MAP">
        SELECT ID as "id",WELL_CODE as "wellCode",DEVCODE as "devcode",CELL AS "cell",STRENGTH AS "stregnth",
        <include refid="date_toChar">
            <property name="paramTime" value="UPTIME"/>
        </include>
        AS "uptime",pci AS "pci",rsrp AS "rsrp",snr AS "snr"
        FROM data_tube
        WHERE DEVCODE=#{devcode}
        <choose>
            <when test="beginTime != null and beginTime != '' and endTime != null and endTime != ''">
                AND UPTIME <![CDATA[ >= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{beginTime}"/>
                </include>
                AND UPTIME <![CDATA[ <= ]]>
                <include refid="char_toDate">
                    <property name="paramStr" value="#{endTime}"/>
                </include>
            </when>
            <otherwise>
                <include refid="data_diff">
                    <property name="paramDay" value="7"/>
                </include>
            </otherwise>
        </choose>
    </select>

    <select id="groupDeviceList" resultType="MAP">
        SELECT   bwi.WELL_CODE  AS  "wellCode",WELL_NAME AS  "wellName",DEEP AS  "deep",
                  LNG_GAODE  AS  "lngGaode",LAT_GAODE  AS  "latGaode",AREA  AS "area",POSITION  AS "position",
                  RESPONSIBLE_DEPT AS  "responsibleDept",PHOTOS  AS  "photos",WELL_TYPE AS  "wellType",STAFF AS "staff",TEL AS "tel" ,
                  bwi.VALID  AS  "valid",BFZT  AS  "bfzt",WELL_FLAG_1 AS  "groupId",DEPTID as  "deptid",STRENGTH AS "strength",data_tube_id  AS "data_tube_id"
        FROM  bus_well_info  bwi,
        bus_device_well   bdw,
        bus_device  bd
        LEFT JOIN  (SELECT   DEVCODE,STRENGTH,id  as  data_tube_id,DATE_FORMAT(UPTIME,'%Y-%m-%d %H:%i:%S') AS uptime
        FROM    data_tube
        WHERE   datediff(now(),UPTIME) <![CDATA[<= ]]> 3
        AND  datediff(now(),UPTIME) <![CDATA[>= ]]> 0
        GROUP BY  DEVCODE, STRENGTH,id,UPTIME) dzt ON dzt.DEVCODE=bd.DEVCODE
        WHERE  bd.DEVICE_TYPE='16'
        AND   bwi.WELL_FLAG_1=#{wellGroupId}
        AND   bdw.WELL_ID=bwi.ID
        AND   bd.ID=bdw.DEVICE_ID
    </select>

    <select id="deviceDetail" resultType="MAP">
        SELECT dt.ID as "id",dt.WELL_CODE as "wellCode",dt.DEVCODE as "devcode",dt.CELL AS "cell",dt.STRENGTH AS
        "stregnth",
        <include refid="date_toChar">
            <property name="paramTime" value="UPTIME"/>
        </include>
        AS "uptime", pci AS "pci",rsrp AS "rsrp",snr AS "snr",
        CASE WHEN bds.ONLINE_STATE ='1' THEN 1 ELSE 0 END AS "onlineState",
        LNG_GAODE AS "lngGaode",LAT_GAODE AS "latGaode",bd.DEVICE_NAME AS "deviceName",bwi.WELL_FLAG_1 AS "groupId"
        FROM data_tube dt,
        bus_well_info bwi,
        bus_device_well bdw,
        bus_device bd
        LEFT JOIN bus_device_status bds ON bds.DEVICE_ID=bd.ID
        WHERE dt.id=#{id}
        AND bdw.WELL_ID=bwi.ID
        AND bdw.DEVICE_ID = bd.ID
        AND bd.DEVCODE=dt.DEVCODE
    </select>

</mapper>