<?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,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">
        '%' ||#{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_DATE(${paramStr},'yyyy-MM-dd HH24:mi')
    </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)
        </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 !=''">
            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="getDeviceListInfo" resultType="com.casic.missiles.modular.system.dto.DeviceInfoDto">
        SELECT id,
               devcode,
               deviceName,
               deviceType,
               deviceTypeName,
               wellId,
               deptid,
               wellCode,
               POSITION,
               CASE WHEN 	bds.ONLINE_STATE ='1' 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 	bds.ONLINE_STATE ='1' 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, INSTALL_DATE AS "installDate",CASE WHEN 	bds.ONLINE_STATE ='1' THEN 1 ELSE 0 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>

    <select id="offlineListPage" resultType="MAP">
        SELECT
        bd.devcode AS devcode,
        bdt.TYPE_NAME AS deviceTypeName,
        bd.device_type AS deviceType,
        bwi.well_code AS wellCode,
        bds.last_data_ts AS lastDataTs,
        timestampdiff(
        DAY,
        bds.last_data_ts,
        now()) AS offlineDays
        FROM
        bus_device_status bds
        JOIN bus_device bd ON bds.device_id = bd.id
        JOIN bus_device_well bdw ON bdw.device_id = bds.device_id
        JOIN bus_well_info bwi ON bwi.ID = bdw.well_id
        JOIN base_device_type bdt on bdt.ID = bd.DEVICE_TYPE
        WHERE
        bds.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>

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

</mapper>
