<?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="org.jeecg.modules.warning.mapper.GingerMapper">
    <!--	-->
    <select id="newParameterWarning" resultType="hashMap">
        SELECT C.JH,
               '默认'        YJMX,
               '新参数预警'     BJLB,
               C.WARN_TYPE YCLX,
               B.LSBJCS,
               C.WARN_TIME BJSJ,
               '[' || C.JH || ',' || C.WARN_TYPE || ']'||chr(10)||
               '钻头位置:' ||  ROUND(c.warn_bitdepth, 2) || 'm'||chr(10)||
               '异常原因:' || (CASE
                                  WHEN C.WARN_TYPE = '钩载变化异常' then ( case c.warn_condition
                                                                        when 'DrillDown' then '符合钩载变化异常预警条件'
                                                                        when 'Trip' then '符合钩载变化异常预警条件'
                                      end)
                                  WHEN C.WARN_TYPE = '扭矩变化异常'then '符合' || c.warn_des || '条件'
                                  WHEN C.WARN_TYPE = '泵压变化异常'then '符合泵压变化异常预警条件'
                                  WHEN C.WARN_TYPE = '总池体积变化异常'then '符合总池体积变化预警条件'
                   end) || '('||
               ( CASE
                     WHEN C.WARN_TYPE = '钩载变化异常' then ('当前点钩载:'||ROUND(c.warn_woh,2)||' KN, 参考柱钩载:'||ROUND(c.normal_woh,2)||' KN, 钻杆重量:'||ROUND(c.column_weight,2)||'KN, 大于报警阈值'||c.warn_value||' KN')
                     WHEN C.WARN_TYPE='扭矩变化异常' then ('当前点扭矩:'||ROUND(c.warn_woh,2)||' KN, 基准波动幅度:'||ROUND(c.normal_woh,2)||' KN, 变化量平均值:'|| ROUND(c.njbhl_per_torBig,2)||' KN, 大于波动报警阈值'||c.torgue_wave_threshold||'%'||',或大于变化量报警阈值'||c.torgue_variation_threshold||'%')
                     WHEN C.WARN_TYPE='泵压变化异常' then ('当前点立压:'||ROUND(c.warn_woh,2)||' Mpa, 上一点立压:'||ROUND(c.per_spp,2)||' MPa, 大于力压报警阈值:'||c.loop_drilling_spp||'%')
                     WHEN C.WARN_TYPE='总池体积变化异常' then('当前点总池体积'||ROUND(c.warn_woh,2)||' m^³, 起点总池体积'||ROUND(c.pit_start,2)||' m^³, 起点时间:'||TO_CHAR(c.pit_start_time,'yyyy-MM-dd HH24:mi:ss'))||', 计算差值:'||ROUND(c.pit_sub,2)||' m^³,大于报警阈值'||ROUND(c.pit_value,2)||' m^³'
                   end)||')' YCMS
        FROM (
            SELECT JH, WARN_TYPE, MAX(WARN_TIME) WARN_TIME FROM WARN_HISTORY T WHERE
           WARN_TIME BETWEEN TO_DATE(#{params.strDate}, 'yyyy/mm/dd') AND TO_DATE(#{params.endDate}, 'yyyy/mm/dd') + 1
        <choose>
        <when test="params.jhs!=null and params.jhs.size() >0">
            and jh in
            <foreach collection="params.jhs" item="item" index="index" open="(" separator="," close=")">
            #{item,jdbcType=VARCHAR}
            </foreach>
        </when>
            <when test="params.jhs==null and params.jhs.size() ==0">
                and jh=''
            </when>
        </choose>
            GROUP BY JH, WARN_TYPE) A
            LEFT JOIN (SELECT JH, WARN_TYPE, COUNT (1) LSBJCS FROM WARN_HISTORY where
             to_char(WARN_TIME,'yyyy/mm/dd') BETWEEN #{params.strDate} and  #{params.endDate}
                 GROUP BY JH, WARN_TYPE) B
        ON A.JH = B.JH AND A.WARN_TYPE = B.WARN_TYPE
            LEFT JOIN WARN_HISTORY C ON A.JH = C.JH AND A.WARN_TYPE = C.WARN_TYPE AND A.WARN_TIME = C.WARN_TIME
        ORDER BY A.JH
    </select>
<!--    BETWEEN TO_DATE(#{params.strDate}, 'yyyy/mm/dd') AND TO_DATE(#{params.endDate}, 'yyyy/mm/dd') + 1-->
</mapper>
