<?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.yeejoin.amos.boot.module.jxiop.biz.tdMapper2.FanHealthIndexMapper">

<insert id="saveBatchHealthIndexList">
    insert into
    ${tableName}
    using fan_health_index_data TAGS (#{analysisType})
    values
    <foreach collection="list" separator="," item="item" index="index">
        (
        now,
        #{item.recDate, jdbcType=VARCHAR},
        #{item.analysisObjType, jdbcType=VARCHAR},
        #{item.analysisObjSeq, jdbcType=VARCHAR},
        #{item.weight, jdbcType=FLOAT},
        #{item.healthIndex, jdbcType=FLOAT},
        #{item.healthLevel, jdbcType=VARCHAR},
        #{item.analysisStartTime, jdbcType=VARCHAR},
        #{item.analysisEndTime, jdbcType=VARCHAR},
        #{item.area, jdbcType=VARCHAR},
        #{item.station, jdbcType=VARCHAR},
        #{item.subSystem, jdbcType=VARCHAR},
        #{item.number, jdbcType=VARCHAR},
        #{item.equipmentName, jdbcType=VARCHAR},
        #{item.gatewayId, jdbcType=VARCHAR},
        #{item.indexAddress, jdbcType=VARCHAR},
        #{item.anomaly, jdbcType=FLOAT},
        #{item.pointName, jdbcType=VARCHAR},
        #{item.analysisTime, jdbcType=VARCHAR},
        #{item.kks, jdbcType=VARCHAR},
        #{item.orgCode, jdbcType=VARCHAR}
        )
    </foreach>
    </insert>

<select id="getInfoListByGroupByCdFan" resultType="com.yeejoin.amos.boot.module.jxiop.biz.tdengine.FanHealthIndex">
    select
        index_address,
        gateway_id ,
        station,
        analysis_obj_type,
        analysis_obj_seq,
        weight,
        area,
        sub_system,
        number,
        equipment_name,
        avg(anomaly) as anomaly,
        point_name,
        kks,
        org_code,
        AVG(health_index) as health_index
    from
        #{tableName}
    where
        analysis_obj_type = #{analysisObjectType}
      and ts > #{startTime}
    group by
        gateway_id,
        index_address,
        station,
        analysis_obj_type,
        analysis_obj_seq,
        weight,
        area,
        sub_system,
        number,
        equipment_name,
        point_name,
        kks,
        org_code
    </select>

<select id="getInfoListByGroupByZxtFan" resultType="com.yeejoin.amos.boot.module.jxiop.biz.tdengine.FanHealthIndex">
    select
        gateway_id ,
        station,
        '子系统' as analysis_obj_type,
        area,
        sub_system,
        equipment_name,
        number,
        avg(anomaly) as anomaly,
        org_code,
        AVG(health_index) as health_index
    from
        #{tableName}
    where
        analysis_obj_type = #{analysisObjectType}
      and ts > #{startTime}
    group by
        gateway_id,
        station,
        analysis_obj_type,
        area,
        sub_system,
        equipment_name,
        number,
        org_code
    </select>

<select id="getInfoListByGroupBySbFan" resultType="com.yeejoin.amos.boot.module.jxiop.biz.tdengine.FanHealthIndex">
    select
        gateway_id ,
        station,
        '设备' as analysis_obj_type,
        area,
        number,
        equipment_name,
        avg(anomaly) as anomaly,
        org_code,
        AVG(health_index) as health_index
    from
        #{tableName}
    where
        analysis_obj_type = #{analysisObjectType}
      and ts > #{startTime}
    group by
        gateway_id,
        station,
        analysis_obj_type,
        area,
        number,
        equipment_name,
        org_code
    </select>

<select id="getInfoListByGroupByCzFan" resultType="com.yeejoin.amos.boot.module.jxiop.biz.tdengine.FanHealthIndex">
    select
        gateway_id,
        station,
        '场站' as analysis_obj_type,
        area,
        avg(anomaly) as anomaly,
        org_code,
        AVG(health_index) as health_index
    from
        #{tableName}
    where
        analysis_obj_type = #{analysisObjectType}
      and ts > #{startTime}
    group by
        gateway_id,
        station,
        analysis_obj_type,
        area,
        org_code
    </select>

<select id="getInfoListByGroupByQyFan" resultType="com.yeejoin.amos.boot.module.jxiop.biz.tdengine.FanHealthIndex">
    select
        '片区' as analysis_obj_type,
        area,
        avg(anomaly) as anomaly,
        org_code,
        AVG(health_index) as health_index
    from
        #{tableName}
    where
        analysis_obj_type = #{analysisObjectType}
      and ts > #{startTime}
    group by
        analysis_obj_type,
        area,
        org_code
    </select>

<select id="getInfoByPage" resultType="com.yeejoin.amos.boot.module.jxiop.biz.tdengine.FanHealthIndex">
    SELECT * FROM fan_health_index_data
    <where>
        <if test="dto.analysisObjType!= null and dto.analysisObjType!= ''">analysis_obj_type = #{dto.analysisObjType}</if>
        <if test="dto.analysisType!= null and dto.analysisType!= ''">and analysis_type = #{dto.analysisType}</if>
        <if test="dto.endDate!= null and dto.endDate!= '' ">  and ts &lt;= #{dto.endDate} </if>
        <if test="dto.startDate!= null and dto.startDate!= ''">  and  ts &gt;= #{dto.startDate} </if>
        <if test="dto.area!= null and dto.area!= ''"> AND area = #{dto.area} </if>
        <if test="dto.number!= null and dto.number!= ''"> AND `number` = #{dto.number} </if>
        <if test="dto.pointName!= null and dto.pointName!= ''">AND point_name = #{dto.pointName} </if>
        <if test="dto.station!= null and dto.station!= ''">AND station = #{dto.station} </if>
        <if test="dto.healthLevel!= null and dto.healthLevel!= ''">AND health_level = #{dto.healthLevel} </if>
        <if test="dto.subSystem!= null and dto.subSystem!= ''">AND sub_system = #{dto.subSystem} </if>
        <if test="dto.equipmentName!= null and dto.equipmentName!= ''">AND equipment_name = #{dto.equipmentName}
        </if>
        <if test="dto.orgCode != null and dto.orgCode != ''">
          and  org_code like #{dto.orgCode}
        </if>
    </where>

    <if test="dto.orderColumns != null and dto.orderColumns != ''">
        order by ${dto.orderColumns}
    </if>
    <if test="(dto.orderColumns == null or dto.orderColumns == '') and dto.sortOne != null and dto.sortOne != ''">
        order by ${dto.sortOne}
    </if>
    limit #{dto.current}, #{dto.size}
</select>

<select id="getInfoByPageTotal" resultType="java.lang.Integer">
    SELECT count(1) FROM fan_health_index_data
    <where>
        <if test="dto.analysisObjType!= null and dto.analysisObjType!= ''">analysis_obj_type = #{dto.analysisObjType}</if>
        <if test="dto.analysisType!= null and dto.analysisType!= ''">and analysis_type = #{dto.analysisType}</if>
        <if test="dto.endDate!= null and dto.endDate!= '' ">  and ts &lt;= #{dto.endDate} </if>
        <if test="dto.startDate!= null and dto.startDate!= ''">  and  ts &gt;= #{dto.startDate} </if>
        <if test="dto.area!= null and dto.area!= ''"> AND area = #{dto.area} </if>
        <if test="dto.number!= null and dto.number!= ''"> AND `number` = #{dto.number} </if>
        <if test="dto.pointName!= null and dto.pointName!= ''">AND point_name = #{dto.pointName} </if>
        <if test="dto.station!= null and dto.station!= ''">AND station = #{dto.station} </if>
        <if test="dto.healthLevel!= null and dto.healthLevel!= ''">AND health_level = #{dto.healthLevel} </if>
        <if test="dto.subSystem!= null and dto.subSystem!= ''">AND sub_system = #{dto.subSystem} </if>
        <if test="dto.equipmentName!= null and dto.equipmentName!= ''">AND equipment_name = #{dto.equipmentName}
        </if>
        <if test="dto.orgCode != null and dto.orgCode != ''">
            and org_code like #{dto.orgCode}
        </if>
    </where>

    </select>

<select id="saveBatchHealthIndexLatestInfo" resultType="int">
    insert into
    ${tableName}
    values
    <foreach collection="list" separator="," item="item" index="index">
        (
        now,
        #{item.recDate, jdbcType=VARCHAR},
        #{item.analysisObjType, jdbcType=VARCHAR},
        #{item.analysisObjSeq, jdbcType=VARCHAR},
        #{item.weight, jdbcType=FLOAT},
        #{item.healthIndex, jdbcType=FLOAT},
        #{item.healthLevel, jdbcType=VARCHAR},
        #{item.analysisStartTime, jdbcType=VARCHAR},
        #{item.analysisEndTime, jdbcType=VARCHAR},
        #{item.area, jdbcType=VARCHAR},
        #{item.station, jdbcType=VARCHAR},
        #{item.subSystem, jdbcType=VARCHAR},
        #{item.number, jdbcType=VARCHAR},
        #{item.equipmentName, jdbcType=VARCHAR},
        #{item.gatewayId, jdbcType=VARCHAR},
        #{item.indexAddress, jdbcType=VARCHAR},
        #{item.anomaly, jdbcType=FLOAT},
        #{item.pointName, jdbcType=VARCHAR},
        #{item.analysisTime, jdbcType=VARCHAR},
        #{item.kks, jdbcType=VARCHAR},
        #{item.orgCode, jdbcType=VARCHAR}
        )
    </foreach>

    </select>

<delete id="deleteAllDataByTableName">
    delete  from ${tableName} where analysis_type = #{analysisType}
    </delete>

<select id="equipWarningRadarMapByTd" resultType="java.util.Map">
    select
    a.warningName,
    count(1) as num
    from
    (
    select
    warning_name as warningName
    from
    analysis_data.fan_warning_record
    <where>
        ((disposotion_state = '待确认')
        or (disposotion_state = '已确认'
        and ts > now - 3d - 8h))
        <if test="stationCode != null and stationCode != ''">
            and gateway_id = #{stationCode}
        </if>
    </where>
    union all
    (select
    warning_name as warningName
    from
    analysis_data.pv_warning_record
    <where>((disposotion_state = '待确认')
        or (
        disposotion_state = '已确认'
        and ts > now - 3d - 8h ))
        <if test="stationCode != null and stationCode != ''">
            and gateway_id = #{stationCode}
        </if>
    </where>)
    ) a
    group by
    a.warningName
    </select>

    <select id="getWarningInfoBySubSystemTd" resultType="java.util.Map">
        SELECT
        point_name as pointName,
        warning_name as warningName,
        last(ts)
        FROM
        fan_warning_record
        <where>
            disposotion_state = '待确认'
            <if test="subSystem != null and subSystem != ''">
                AND sub_system = #{subSystem}
            </if>
            <if test="gatewayId != null and gatewayId != ''">
                AND gateway_id = #{gatewayId}
            </if>
            <if test="equipmentName != null and equipmentName != ''">
                AND equipment_name like #{equipmentName}
            </if>
        </where>
        group by point_name
    </select>

    <select id="getRecDateByIndexAddressTs" resultType="java.lang.String">

        SELECT
            rec_date
        FROM
            ${tableName}
        WHERE
            index_address = #{indexAddress}
          AND disposotion_state = '待确认'
          and gateway_id = #{fanGatewayId}
        ORDER BY
            ts DESC
            LIMIT 1

    </select>

    <select id="getInfoListByTableNameTs" resultType="java.util.Map">
        SELECT
            rec_date as recDate,
            CEIL(health_index) as healthIndex
        FROM
            ${tableName}
        WHERE
            #{recDate} >= ts
          AND ts >=  #{date12}
          AND analysis_type = '按10分钟'
          AND index_address = #{indexAddress}
          AND gateway_id = #{fanGatewayId}
    </select>

    <select id="getPvWarningInfoBySubSystemTd" resultType="java.util.Map">
        SELECT
        point_name as pointName,
        warning_name as warningName,
        last(ts)
        FROM
        pv_warning_record
        <where>
            disposotion_state = '待确认'
            <if test="equipmentName != null and equipmentName != ''">
                AND equipment_name = #{equipmentName}
            </if>
            <if test="gatewayId != null and gatewayId != ''">
                AND gateway_id = #{gatewayId}
            </if>
        </where>
        group by point_name
    </select>

<select id="getInfoByDate" resultType="java.util.Map">
    select avg(b.health_index) as healthIndex,b.rec_date AS recDate from
    (SELECT
    health_index,
    rec_date
    FROM
    pv_health_index_day
    <where>
		rec_date IN 
		<foreach collection="list" separator="," item="item" index="index" open="(" close=")">
			#{item}
        </foreach>
        <if test="areaCode != null and areaCode != ''">
            AND area like #{areaCode}
            AND analysis_obj_type = '片区'
        </if>
        <if test="stationCode != null and stationCode != ''">
            AND gateway_id = #{stationCode}
            AND analysis_obj_type = '场站'
        </if>
        <if test="(stationCode == null or stationCode == '') and (areaCode == null or areaCode == '')">
            AND analysis_obj_type = '片区'
        </if>
    </where>
    UNION ALL
    (
    SELECT
    health_index,
    rec_date
    FROM
    fan_health_index_day
    <where>
		rec_date IN 
		<foreach collection="list" separator="," item="item" index="index" open="(" close=")">
			#{item}
        </foreach>
        <if test="areaCode != null and areaCode != ''">
            AND area like #{areaCode}
            AND analysis_obj_type = '片区'
        </if>
        <if test="stationCode != null and stationCode != ''">
            AND gateway_id = #{stationCode}
            AND analysis_obj_type = '场站'
        </if>
        <if test="(stationCode == null or stationCode == '') and (areaCode == null or areaCode == '')">
            AND analysis_obj_type = '片区'
        </if>
    </where>
    )) b GROUP BY b.rec_date
    </select>
    
    
    <select id="getInfoByHour" resultType="java.util.Map">		
    select avg(b.health_index) as healthIndex,b.rec_date AS recDate from
    (SELECT
    health_index,
    rec_date
    FROM
    pv_health_index_hour
    <where>
        rec_date IN 
        <foreach collection="list" separator="," item="item" index="index" open="(" close=")">
			#{item}
        </foreach>
        <if test="areaCode != null and areaCode != ''">
            AND area like #{areaCode}
            AND analysis_obj_type = '片区'
        </if>
        <if test="stationCode != null and stationCode != ''">
            AND gateway_id = #{stationCode}
            AND analysis_obj_type = '场站'
        </if>
        <if test="(stationCode == null or stationCode == '') and (areaCode == null or areaCode == '')">
            AND analysis_obj_type = '片区'
        </if>
    </where>
    UNION ALL
    (
    SELECT
    health_index,
    rec_date
    FROM
    fan_health_index_hour
    <where>
		rec_date IN 
        <foreach collection="list" separator="," item="item" index="index" open="(" close=")">
			#{item}
        </foreach>
        <if test="areaCode != null and areaCode != ''">
            AND area like #{areaCode}
            AND analysis_obj_type = '片区'
        </if>
        <if test="stationCode != null and stationCode != ''">
            AND gateway_id = #{stationCode}
            AND analysis_obj_type = '场站'
        </if>
        <if test="(stationCode == null or stationCode == '') and (areaCode == null or areaCode == '')">
            AND analysis_obj_type = '片区'
        </if>
    </where>
    )) b GROUP BY b.rec_date
    </select>
    
    <select id="getInfoByMoment" resultType="java.util.Map">
    select avg(b.health_index) as healthIndex,b.rec_date AS recDate from
    (SELECT
    health_index,
    rec_date
    FROM
    pv_health_index_moment
    <where>
		rec_date IN 
        <foreach collection="list" separator="," item="item" index="index" open="(" close=")">
			#{item}
        </foreach>
        <if test="areaCode != null and areaCode != ''">
            AND area like #{areaCode}
            AND analysis_obj_type = '片区'
        </if>
        <if test="stationCode != null and stationCode != ''">
            AND gateway_id = #{stationCode}
            AND analysis_obj_type = '场站'
        </if>
        <if test="(stationCode == null or stationCode == '') and (areaCode == null or areaCode == '')">
            AND analysis_obj_type = '片区'
        </if>
    </where>
    UNION ALL
    (
    SELECT
    health_index,
    rec_date
    FROM
    fan_health_index_moment
    <where>
		rec_date IN 
        <foreach collection="list" separator="," item="item" index="index" open="(" close=")">
			#{item}
        </foreach>
        <if test="areaCode != null and areaCode != ''">
            AND area like #{areaCode}
            AND analysis_obj_type = '片区'
        </if>
        <if test="stationCode != null and stationCode != ''">
            AND gateway_id = #{stationCode}
            AND analysis_obj_type = '场站'
        </if>
        <if test="(stationCode == null or stationCode == '') and (areaCode == null or areaCode == '')">
            AND analysis_obj_type = '片区'
        </if>
    </where>
    )) b GROUP BY b.rec_date
    </select>

<select id="getInfoListByGroupByQgFan" resultType="com.yeejoin.amos.boot.module.jxiop.biz.tdengine.FanHealthIndex">
    select
        '全域' as analysis_obj_type,
        avg(anomaly) as anomaly,
        org_code,
        AVG(health_index) as health_index
    from
        #{tableName}
    where
        analysis_obj_type = #{analysisObjectType}
      and ts > #{startTime}
    group by
        analysis_obj_type,
        org_code
</select>
</mapper>
