<?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.newWarning.mapper.HookLoadWarnLogMapper">
    <select id="getOneWarning" resultType="org.jeecg.modules.newWarning.entity.NewWarnHistory">

        select *
        from (
        (
            select jh, WARN_TIME,WARN_DES, '钩载变化异常' as WARN_TYPE from HOOK_LOAD_WARN_LOG
                where jh = #{jh}
                and WARN_TIME &gt;= TO_DATE(#{startTime}, 'yyyy-MM-dd HH24:mi:ss')
                and  WARN_TIME  &lt;= TO_DATE(#{endTime}, 'yyyy-MM-dd HH24:mi:ss')
        )
              UNION
        (
            select jh, WARN_TIME,WARN_DES, '总池体积变化异常' as WARN_TYPE from TOTAL_POOL_VOLUME_WARN_LOG
                where jh = #{jh}
                  and WARN_TIME &gt;= TO_DATE(#{startTime}, 'yyyy-MM-dd HH24:mi:ss')
                  and  WARN_TIME  &lt;= TO_DATE(#{endTime}, 'yyyy-MM-dd HH24:mi:ss')
        )
        UNION
        (
            select jh, WARN_TIME,WARN_DES, '泵压变化异常' as WARN_TYPE from PUMPING_PRESSURE_WARN_LOG
            where jh = #{jh}
              and WARN_TIME &gt;= TO_DATE(#{startTime}, 'yyyy-MM-dd HH24:mi:ss')
              and  WARN_TIME  &lt;= TO_DATE(#{endTime}, 'yyyy-MM-dd HH24:mi:ss')
        )
        UNION
        (
            select jh, WARN_START_TIME warnTime ,WARN_DES, '扭矩变化异常' as WARN_TYPE from TORQUE_WARN_LOG
            where jh = #{jh}
              and WARN_START_TIME &gt;= TO_DATE(#{startTime}, 'yyyy-MM-dd HH24:mi:ss')
              and  WARN_START_TIME  &lt;= TO_DATE(#{endTime}, 'yyyy-MM-dd HH24:mi:ss')
        )

        )

        where ROWNUM = 1
        order by WARN_TIME asc
    </select>

    <select id="getWarnLogList" resultType="org.jeecg.modules.newWarning.dto.WarnLogComprehensiveDto">
        SELECT
            k.jh,
            k.yjlb,
            k.zxbjsj,
            k.lsbjcs,
            k.yjms
        FROM
            (
                SELECT
                    c.jh,
                    '钩载变化异常' AS yjlb,
                    c.zxbjsj,
                    i.lsbjcs,
                    f.warn_des AS yjms
                FROM
                    (
                        SELECT
                            b.jh,
                            MAX( b.WARN_TIME ) AS zxbjsj
                        FROM
                            ( SELECT DISTINCT a.jh, NVL2( a.warn_time, a.warn_time, a.MOBIL_DRILLING_TOOLS_START_TIME ) AS warn_time FROM HOOK_LOAD_WARN_LOG a ) b
                        WHERE
                            b.warn_time BETWEEN TO_DATE(#{params.strDate}, 'yyyy-mm-dd' )
                                AND TO_DATE(#{params.endDate}, 'yyyy-mm-dd' )
                        GROUP BY
                            jh
                    ) c
                        LEFT JOIN (
                        SELECT
                            d.JH,
                            COUNT( 1 ) LSBJCS
                        FROM
                            (
                                SELECT
                                    h.JH,
                                    h.warn_time
                                FROM
                                    ( SELECT DISTINCT g.jh, NVL2( g.warn_time, g.warn_time, g.MOBIL_DRILLING_TOOLS_START_TIME ) AS warn_time FROM HOOK_LOAD_WARN_LOG g ) h
                                WHERE
                                    h.WARN_TIME BETWEEN TO_DATE(#{params.strDate}, 'yyyy-mm-dd' )
                                        AND TO_DATE(#{params.endDate}, 'yyyy-mm-dd' )
                            ) d
                        GROUP BY
                            d.jh
                    ) i ON i.jh = c.jh
                        LEFT JOIN (
                        SELECT
                            *
                        FROM
                            ( SELECT DISTINCT e.jh, NVL2( e.warn_time, e.warn_time, e.MOBIL_DRILLING_TOOLS_START_TIME ) AS warn_time, WARN_DES FROM HOOK_LOAD_WARN_LOG e ) j
                        WHERE
                            j.WARN_TIME BETWEEN TO_DATE(#{params.strDate}, 'yyyy-mm-dd' )
                                AND TO_DATE(#{params.endDate}, 'yyyy-mm-dd' )
                    ) f ON f.warn_time = c.zxbjsj
                        AND f.jh = c.jh
            ) k
        WHERE 1=1
        <choose>
            <when test="params.jhs!=null and params.jhs.size() >0">
                and  k.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  k.jh =''
            </when>
        </choose>
    </select>
    <select id="getWarnLogCount" resultType="org.jeecg.modules.newWarning.dto.WarnLogStatisticsDto">
        SELECT
            jh,
            zxbjrq,
            bjcs
        FROM
            (
                SELECT
                    b.jh,
                    max( b.warn_time ) AS zxbjrq,
                    COUNT( 1 ) AS bjcs
                FROM
                    ( SELECT DISTINCT a.jh, NVL2( a.warn_time, a.warn_time, a.MOBIL_DRILLING_TOOLS_START_TIME ) AS warn_time FROM HOOK_LOAD_WARN_LOG a ) b
                GROUP BY
                    jh
            )
        <if test="ew != null">
            ${ew.customSqlSegment}
        </if>
    </select>
</mapper>
