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

    <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_DATE(${paramStr},'yyyy-MM-dd HH24:mi:ss')
    </sql>

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

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

    <select id="getGasDectorListPage" resultType="com.casic.missiles.modular.system.model.GasDectorDataDto">
        SELECT bd.DEVCODE AS devcode,dgd.IMSI AS imsi,dgd.GAS_SENSOR_STATE AS "gasSensorState",
        dgd.GAS_SAMPLE_VALUE AS "gasSampleValue",sinr, rsrp, pci, dgd.HEARTBEAT_TIME AS "heartbeatTime",
        dgd.CELL_ID AS "cellId",dgd.BATTERY_VOLTAGE AS "batteryVoltage",dgd.BATTERY_VALUE AS "batteryValue",
        iccid,
        <include refid="date_toChar">
            <property name="paramTime" value="dgd.UPTIME"/>
        </include>
        AS uptime,dgd.ID AS id,
        <include refid="date_toChar">
            <property name="paramTime" value="dgd.LOGTIME"/>
        </include>
        AS logtime,
        bd.DEVICE_TYPE AS "deviceType",bwi.WELL_CODE AS "wellCode",bwi.deptid,bdt.TYPE_NAME AS "deviceTypeName"
        FROM data_gas_dector dgd, bus_device bd
        INNER JOIN base_device_type bdt ON bd.DEVICE_TYPE=bdt.ID
        INNER JOIN bus_device_well bdw ON bd.ID=bdw.DEVICE_ID
        INNER JOIN bus_well_info bwi ON bdw.WELL_ID=bwi.ID
        WHERE bd.DEVCODE=dgd.DEVCODE

        <if test="deptid != null and  deptid != -1 ">
            and bwi.DEPTID = #{deptid}
        </if>
        <if test="beginTime != null and beginTime != '' ">
            and dgd.LOGTIME  <![CDATA[ >= ]]>
            <include refid="char_toDate">
                <property name="paramStr" value="#{beginTime}"/>
            </include>
        </if>
        <if test="endTime != null and endTime != ''">
            and dgd.LOGTIME <![CDATA[ <= ]]>
            <include refid="char_toDate">
                <property name="paramStr" value="#{endTime}"/>
            </include>
        </if>
        <if test="keywords != null and keywords !=''">
            and (bd.DEVCODE like
            <include refid="keywords_sql"/>
            OR bwi.wellCode LIKE
            <include refid="keywords_sql"/>
            )
        </if>
        ORDER BY dgd.LOGTIME DESC
    </select>

    <select id="getPantiltDataListPage" resultType="com.casic.missiles.modular.system.model.PantiltDataDto">
        SELECT dp.ID AS id,dp.DEVCODE AS devcode,dp.dircetion,dp.pitch,dp.concentration,
        <include refid="date_toChar">
            <property name="paramTime" value="dp.UPTIME"/>
        </include>
        AS uptime,dp.ID AS id,
        <include refid="date_toChar">
            <property name="paramTime" value="dp.LOGTIME"/>
        </include>
        AS logtime,bd.DEVICE_TYPE AS "deviceType",bwi.WELL_CODE AS "positionCode",bwi.deptid,bdt.TYPE_NAME AS
        "deviceTypeName"
        FROM data_pantilt dp, bus_device bd
        INNER JOIN base_device_type bdt ON bd.DEVICE_TYPE=bdt.ID
        INNER JOIN bus_device_well bdw ON bd.ID=bdw.DEVICE_ID
        INNER JOIN bus_well_info bwi ON bdw.WELL_ID=bwi.ID
        WHERE bd.DEVCODE=dp.DEVCODE
        <if test="deptid != null and  deptid != -1 ">
            and bwi.DEPTID = #{deptid}
        </if>
        <if test="beginTime != null and beginTime != '' ">
            and dp.LOGTIME  <![CDATA[ >= ]]>
            <include refid="char_toDate">
                <property name="paramStr" value="#{beginTime}"/>
            </include>
        </if>
        <if test="endTime != null and endTime != ''">
            and dp.LOGTIME <![CDATA[ <= ]]>
            <include refid="char_toDate">
                <property name="paramStr" value="#{endTime}"/>
            </include>
        </if>
        <if test="keywords != null and keywords !=''">
            and (bd.DEVCODE like
            <include refid="keywords_sql"/>
            OR bwi.wellCode LIKE
            <include refid="keywords_sql"/>
            )
        </if>
        ORDER BY dp.LOGTIME DESC
    </select>

    <select id="getDeviceType"  resultType="String">
        SELECT  DEVICE_TYPE
        FROM config_dept_permission
        WHERE  DEPTID=#{deptid}
    </select>


    <select id="countRecordsByTime"  resultType="Map">
        SELECT COUNT(1)  AS  "recodCounts"
        FROM ${tableName}   tn
        JOIN  bus_device  bd       ON  bd.DEVCODE=tn.DEVCODE
        JOIN  bus_device_well bdw  ON   bd.ID=bdw.DEVICE_ID
        JOIN  bus_well_info   bwi  ON   bwi.ID=bdw.WELL_ID
        WHERE 1=1
        <if test="beginTime != null and beginTime != '' ">
            and tn.LOGTIME <![CDATA[ >= ]]>
            <include refid="char_toDate">
                <property name="paramStr" value="#{beginTime}"/>
            </include>
        </if>
        <if test="endTime != null and endTime != ''">
            and tn.LOGTIME <![CDATA[ <= ]]>
            <include refid="char_toDate">
                <property name="paramStr" value="#{endTime}"/>
            </include>
        </if>
    </select>

    <select id="countRecordsToday" resultType="int">
        SELECT COUNT(*)
        from bus_device_status  bds
        JOIN bus_device_well bdw  on bdw.DEVICE_ID = bds.DEVICE_ID
        JOIN bus_well_info bwi  on bwi.ID = bdw.WELL_ID
        WHERE
        bdw.VALID='1' and bwi.VALID = '1'
        <if test="beginTime != null and beginTime != '' ">
            and bds.LAST_DATA_TS <![CDATA[ >= ]]>
            <include refid="char_toDate">
                <property name="paramStr" value="#{beginTime}"/>
            </include>
        </if>
        <if test="endTime != null and endTime != ''">
            and bds.LAST_DATA_TS <![CDATA[ <= ]]>
            <include refid="char_toDate">
                <property name="paramStr" value="#{endTime}"/>
            </include>
        </if>
    </select>

</mapper>