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

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.casic.missiles.modular.system.model.Device">
        <id column="ID" property="id"/>
        <result column="DEVCODE" property="devcode"/>
        <result column="DEVICE_NAME" property="deviceName"/>
        <result column="ONLINE_STATE" property="onlineState"/>
        <result column="DEVICE_TYPE" property="deviceType"/>
        <result column="VALID" property="valid"/>
        <result column="INSTALL_DATE" property="installDate"/>
        <result column="LOGTIME" property="logtime"/>
        <result column="VERSION" property="version"/>
    </resultMap>
    <sql id="view_sql">
        ID,DEVCODE,DEVICENAME,DEVICETYPE,DEVICETYPENAME,WATCHTYPE,COMMUNICATION,MODELID,MODELNAME,DEPTID,DEPTNAME,WELLCODE,WELLNAME,POSITION,CONCENID,CONCENCODE,ONLINESTATE,VALID,TS,BFZT,wellId,
        <include refid="date_toChar">
            <property name="paramDate" value="INSTALLDATE"/>
        </include>
        AS "INSTALLDATE",INSTALL_HEIGHT AS "installHeight"
    </sql>

    <sql id="date_toChar" databaseId="mysql">
               DATE_FORMAT(${paramDate},'%Y-%m-%d')
    </sql>

    <sql id="date_toChar" databaseId="pgsql">
               TO_CHAR(${paramDate},'yyyy-mm-dd')
    </sql>

    <sql id="date_toChar" databaseId="oracle">
               TO_CHAR(${paramDate},'yyyy-mm-dd')
    </sql>

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

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

    <sql id="dateTime_toChar" databaseId="oracle">
               TO_CHAR(${paramTime},'yyyy-mm-dd hh24:mi:ss')
    </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="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>

    <select id="selectWellIdByCode" resultType="com.casic.missiles.modular.system.dto.DeviceWellDto">
        SELECT id as "wellId", WELL_CODE as "WELLCODE", DEPTID
        FROM bus_well_info
        WHERE VALID = '1'
          AND WELL_CODE = #{wellCode}
    </select>

    <select id="selectDeviceConcentorByCode" resultType="com.casic.missiles.modular.system.dto.DeviceConcentratorDto">
        SELECT id, CONCENCODE
        FROM bus_concentrator
        WHERE VALID = '1'
          AND CONCENCODE = #{concenCode}
    </select>

    <select id="selectDevicesByConfig" resultType="com.casic.missiles.modular.system.dto.DeviceCodeSelectDto"
    >
        SELECT
        a.id,
        a.devcode,
        a.DEVICE_NAME AS "deviceName",
        a.DEVICE_TYPE AS "deptType",
        a.INSTALL_HEIGHT AS "installHeight",
        b.TYPE_NAME AS "deviceTypeName",
        d.DEPTID
        FROM bus_device a
        INNER JOIN base_device_type b ON a.DEVICE_TYPE = b.ID
        INNER JOIN bus_device_well c ON c.DEVICE_ID = a.ID
        INNER JOIN bus_well_info d ON d.ID =c.WELL_ID
        WHERE a.VALID = '1' AND c.VALID = '1'
        <if test="deviceType != null and deviceType !=''">
            and a.device_Type = #{deviceType}
        </if>
        <if test='configStatus != null and configStatus =="0"'>
            and not EXISTS (
                SELECT 1 FROM bus_config
                WHERE bus_config.DEVICE_ID = a.ID
                AND  STATUS='0'
            )
        </if>
        ORDER by a.DEVCODE
    </select>

    <select id="getDeviceList" fetchSize="3000" resultType="com.casic.missiles.modular.system.dto.DeviceDto"
            parameterType="com.baomidou.mybatisplus.extension.plugins.pagination.Page">
        SELECT
        <include refid="view_sql"/>
        FROM device_view dv
        LEFT JOIN bus_device_status bds ON bds.DEVICE_ID=dv.ID
        WHERE VALID = '1'
        <if test="deviceType != null and deviceType !=''">
            and deviceType = #{deviceType}
        </if>
        <if test="deptid != null and deptid !=-1">
            and deptid = #{deptid}
        </if>
        <if test="isOnline != null and isOnline !='' and  isOnline != 1 ">
            and bds.ONLINE_STATE ='0'
        </if>
        <if test="isOnline != null and isOnline !='' and  isOnline != 0 ">
            and (bds.ONLINE_STATE ='1' or bds.ONLINE_STATE is null)
        </if>
        <if test="beginTime != null and beginTime != '' ">
            and installDate &gt;
            <include refid="dateTime_toChar">
                <property name="paramTime" value="#{beginTime}"/>
            </include>
        </if>
        <if test="endTime != null and endTime != ''">
            and installDate &lt;
            <include refid="dateTime_toChar">
                <property name="paramTime" 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>
        ORDER BY INSTALLDATE DESC
    </select>

    <select id="selectDataScopePage" fetchSize="3000" resultType="com.casic.missiles.modular.system.dto.DeviceDto"
            parameterType="com.baomidou.mybatisplus.extension.plugins.pagination.Page">
        SELECT
        <include refid="view_sql"/>
        FROM device_view dv
        LEFT JOIN bus_device_status bds ON bds.DEVICE_ID=dv.ID
        WHERE VALID = '1'
        <if test="deviceType != null and deviceType != -1">
            and deviceType = #{deviceType}
        </if>
        <if test="deptid != null and deptid != -1">
            and deptid = #{deptid}
        </if>
        <if test="isOnline != null and isOnline !='' and  isOnline != 1 ">
            and dv.onlinestate ='0'
        </if>
        <if test="isOnline != null and isOnline !='' and  isOnline != 0 ">
            and dv.onlinestate  <![CDATA[ <> ]]> '0'
        </if>
        <if test="beginTime != null and beginTime != '' ">
            and installDate &gt;
            <include refid="dateTime_toChar">
                <property name="paramTime" value="#{beginTime}"/>
            </include>
        </if>
        <if test="endTime != null and endTime != ''">
            and installDate &lt;
            <include refid="dateTime_toChar">
                <property name="paramTime" value="#{endTime}"/>
            </include>
        </if>
        <if test="keywords != null and keywords !=''">
            and (DEVCODE like
            <include refid="keywords_sql"/>
            OR wellCode LIKE
            <include refid="keywords_sql"/>
            OR wellName LIKE
            <include refid="keywords_sql"/>
            )
        </if>
        ORDER BY INSTALLDATE DESC
    </select>

    <select id="getDeviceListInfo" resultType="com.casic.missiles.modular.system.dto.DeviceInfoDto">
        SELECT id,
               devcode,
               deviceName,
               deviceType,
               deviceTypeName,
               wellId,
               deptid,
               wellCode,
               POSITION,
               CASE WHEN 	bds.ONLINE_STATE <![CDATA[ <> ]]> '0' THEN 1 ELSE 0 END  AS "onlineState",
               coordinateX,
               coordinateY,
               latBaidu,
               lngBaidu,
               latGaode,
               lngGaode
        FROM device_overview_view  dov
        LEFT JOIN  bus_device_status bds ON  bds.DEVICE_ID=dov.ID
        WHERE valid = '1'
    </select>

    <select id="selectDeviceById" resultType="com.casic.missiles.modular.system.dto.DeviceInfoDto">
        SELECT id,
               devcode,
               deviceName,
               devicetype,
               deviceTypeName,
               wellId,
               deptid,
               deptName,
               wellCode,
               POSITION as position,
              CASE WHEN 	dov.onlineState  <![CDATA[ <> ]]> '0' THEN 1 ELSE 0 END  AS "onlineState",
               coordinateX,
               coordinateY,
               latBaidu,
               lngBaidu,
               latGaode,
               lngGaode
        FROM device_overview_view  dov
        LEFT JOIN  bus_device_status bds ON  bds.DEVICE_ID=dov.ID
        WHERE valid = '1'
          and id = #{id}
        limit 1
    </select>

    <sql id="DeviceInWellInfo_Column_List">
        d.ID AS "deviceId", DEVCODE AS devcode,
        <include refid="date_toChar">
            <property name="paramDate" value="INSTALL_DATE"/>
        </include>
        AS "installDate",CASE WHEN d.ONLINE_STATE ='0' THEN 0 ELSE 1 END AS "onlineState",t.TYPE_NAME AS "typeName"
    </sql>

    <select id="getDeviceInWellInfo" resultType="com.casic.missiles.modular.system.dto.DeviceInWellInfo">
        SELECT
        <include refid="DeviceInWellInfo_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>

    <sql id="install_time_diff" databaseId="oracle">
         SYSDATE()-install_date
    </sql>

    <sql id="install_time_diff" databaseId="mysql">
         timestampdiff(DAY,NOW(),install_date)
    </sql>

    <sql id="install_time_diff" databaseId="pgsql">
      floor((extract(epoch    FROM  CURRENT_TIMESTAMP) - extract(epoch    FROM install_date))/86400)
    </sql>

    <select id="offlineListPage" resultType="java.util.Map">
        SELECT
        bd.devcode AS "devcode",
        bdt.TYPE_NAME AS "deviceTypeName",
        bd.device_type AS deviceType,
        bwi.well_code AS "wellCode",
        <include refid="date_toChar">
            <property name="paramDate" value="INSTALL_DATE"/>
        </include>
        AS "lastDataTs",
        <include refid="install_time_diff"/>
        AS "offlineDays"
        FROM bus_device bd
        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 bdt.ID = bd.DEVICE_TYPE
        WHERE bd.online_State = '0'
        <if test="deviceCode != null and deviceCode !=''">
            AND bd.DEVCODE = #{deviceCode}
        </if>
        <if test="deviceType != null and deviceType != -1">
            AND bd.device_type = #{deviceType}
        </if>
    </select>


    <sql id="time_diff" databaseId="oracle">
         SYSDATE()- Max(dt.logtime)
    </sql>

    <sql id="time_diff" databaseId="mysql">
         timestampdiff(DAY,NOW(),Max(dt.logtime))
    </sql>

    <sql id="time_diff" databaseId="pgsql">
     floor((extract(epoch    FROM CURRENT_TIMESTAMP ) - extract(epoch    FROM Max(dt.logtime)))/86400)
    </sql>

    <select id="getOfflineTimeList" resultType="java.util.Map">
        SELECT distinct(bd.devcode)  AS "devcode",Max(dt.logtime) AS "lastDataTs",
        <include refid="time_diff"/>
        AS "offlineDays"
        FROM ${tableName} dt
        JOIN bus_device bd ON dt.devcode=bd.devcode
        where online_state='0'
        GROUP BY bd.devcode
    </select>

    <select id="getDetailById" resultType="com.casic.missiles.modular.system.dto.DeviceDto">
        SELECT id as "wellId", WELL_CODE as "WELLCODE", DEPTID
        FROM bus_device
        WHERE ID = #{id}
    </select>

    <update id="logicDelete">
        update bus_device
        set    valid=#{currentTime}
        where id=#{id}
    </update>

    <select id="getDeviceType" resultType="java.util.Map">
        SELECT  DEVICE_TYPE AS  "deviceType",deptid AS "deptid"
        FROM config_dept_permission
    </select>

    <select id="getDeptList" resultType="java.util.Map">
        SELECT  id AS "id",pid AS "pid"
        FROM  sys_dept
    </select>


</mapper>
