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

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.casic.missiles.modular.system.model.AlarmRule">
        <id column="ID" property="id"/>
        <result column="DEVCODE" property="devcode"/>
        <result column="HIGHVALUE" property="highvalue"/>
        <result column="LOWVALUE" property="lowvalue"/>
        <result column="OVERTIME" property="overtime"/>
        <result column="SALTATION" property="saltation"/>
        <result column="SENSORCODE" property="sensorcode"/>
        <result column="GRADE" property="grade"/>
        <result column="TS" property="ts"/>
    </resultMap>


    <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="Base_Column_List">
        ID AS id, DEVCODE AS devcode, HIGHVALUE AS highvalue, LOWVALUE AS lowvalue, OVERTIME AS overtime,
         SALTATION AS saltation, SENSORCODE AS sensorcode, GRADE AS grade, TS AS ts
    </sql>

    <sql id="tsdate_toChar" databaseId="mysql">
        DATE_FORMAT( ar.TS,'%Y-%m-%d %H:%i:%s')
    </sql>

    <sql id="tsdate_toChar" databaseId="pgsql">
       TO_CHAR(ar.TS,'yyyy-mm-dd hh24:mi:ss')
    </sql>

    <sql id="tsdate_toChar" databaseId="oracle">
       TO_CHAR(ar.TS,'yyyy-mm-dd hh24:mi:ss')
    </sql>

    <select id="ruleListViaParam" resultType="map">
        SELECT ar.ID as id,ar.HIGHVALUE as "highValue",ar.LOWVALUE as "lowValue",
        bd.ID AS "devId",bd.DEVCODE as "devcode",
        bd.DEVICE_TYPE as "deviceType",bwi.ID AS "wellId",bwi.WELL_NAME as "wellName",
        COALESCE (ar.OPERATOR,'') AS "user",
        bwi.WELL_CODE as "wellCode",bwi.DEEP as "deep",bwi.DEPTID as "deptid",
        COALESCE (<include refid="tsdate_toChar"/>,'') AS "ts"
        FROM alarm_rule ar
        LEFT JOIN bus_device bd ON ar.DEVCODE = bd.DEVCODE
        LEFT JOIN bus_device_well bdw ON bd.ID = bdw.DEVICE_ID
        LEFT JOIN bus_well_info bwi ON bdw.WELL_ID = bwi.ID
        WHERE bd.VALID = '1' AND bdw.VALID ='1'
        <if test="keywords!='' and keywords!=null">
            AND (ar.DEVCODE LIKE
            <include refid="keywords_sql"/>
            OR bwi.WELL_CODE LIKE
            <include refid="keywords_sql"/>)
        </if>
        <if test="deviceType!='' and deviceType!=null">
            AND bd.DEVICE_TYPE= #{deviceType}
        </if>
    </select>


    <update id="updateRule" flushCache="true">
        UPDATE alarm_rule
        set TS = now(),
        <if test="highValue!=99999">
            HIGHVALUE=#{highValue}
        </if>
        <if test="highValue!=99999 and lowValue!=-99999">
            ,
        </if>
        <if test="lowValue!=-99999">
            LOWVALUE=#{lowValue}
        </if>
        WHERE ID=#{id}
    </update>

    <select id="getNoAlarmDevice" resultType="com.casic.missiles.modular.system.dto.AlarmRuleDTO">
       SELECT d.id AS id,d.DEVCODE AS devcode,d.deviceName AS "deviceName",
        d.deviceType AS "deviceType",
        d.deviceTypeName AS "deviceTypeName",
        d.DEPTNAME AS "deptName",d.DEPTID AS "deptId"
        FROM device_view  d
        WHERE d.devcode NOT IN (
        SELECT r.devcode FROM alarm_rule r)
        AND d.VALID='1' AND d.deviceType IN
        (SELECT  ID
         FROM  base_device_type
         WHERE ALARM_RULE_TYPE='1')
        <if test="devcode!='' and devcode!=null">
            AND  DEVCODE= #{devcode}
        </if>
    </select>

</mapper>
