<?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.pqs9900.mapper.terminal.TerminalStateMapper">

    <select id="getTerminalList" resultType="com.pqs9900.pojo.terminal.TerminalRVO">
        SELECT distinct
        pq.DEV_INDEX AS devIndex,
        pq.NAME AS name,
        pq.IP AS ip,
        dic.DIC_NAME AS devType,
        pq.STATUS as status,
        pq.UPDATETIME as updateTime,
        gd.NAME AS gdName,
        sub.NAME AS bdName,
        nvl(ed.ED_NAME,'/') as edName,
        nvl(fm.fm_number,nvl((select FM_NUMBER from PQS_FLOWMEAL where type = 0 and SECOND_TYPE = 0),500)) as baseFlow,
        nvl(fm2.fm_number,0) as extendFlow,
        nvl(pm.actualflow/1024/1024,0) as actualflow
        FROM
        PQ_DEVICE pq
        left join PQ_GDINFORMATION gd on gd.GD_INDEX = pq.GD_INDEX
        left join PQ_SUBSTATION sub on sub.SUB_INDEX = PQ.SUB_INDEX
        left join PQS_DICDATA dic on pq.DEVTYPE = dic.DIC_INDEX
        left join PQS_DEVEDASS ded on pq.DEV_INDEX = ded.DEV_INDEX and ded.STATE = 1 and ded.UPDATERESULT = 1
        left join PQS_EDDATA ed on ded.ED_INDEX = ed.ED_INDEX
        left join PQS_DEVFMASS dm on pq.DEV_INDEX = dm.DEV_INDEX
        left join PQS_FLOWMEAL fm on dm.FM_BASE_INDEX = fm.FM_INDEX
        left join PQS_FLOWMEAL fm2 on dm.FM_REAM_INDEX = fm2.FM_INDEX
        left join PQS_MONTHFLOW pm on pq.DEV_INDEX = pm.DEV_INDEX and to_char(pm.timeid,'yyyy-MM') = #{startTime}
        WHERE
        pq.DEVMODEL = 1
        <if test="devType!=null and devType!=''">
            and pq.DEVTYPE = #{devType}
        </if>
        <if test="status!=null">
            and pq.STATUS = #{status}
        </if>
        order by updateTime desc
    </select>


    <select id="getTerminalList2" resultType="com.pqs9900.pojo.terminal.TerminalRVO">
        SELECT
        pq.DEV_INDEX AS devIndex,
        pq.NAME AS name,
        pq.IP AS ip,
        dic.DIC_NAME AS devType,
        pq.DEVFLAG as devFlag,
        gd.NAME AS gdName,
        sub.NAME AS bdName,
        nvl(ed.ED_NAME,'/') as edName,
        nvl(fm.FM_NUMBER,(nvl((select FM_NUMBER from PQS_FLOWMEAL where type = 0 and SECOND_TYPE = 0),0))) as baseFlow,
        nvl(fm2.FM_NUMBER,0) as extendFlow,
        nvl(fs.NAME,nvl((select yy.name from PQS_FLOWSTRATEGY yy where type = 0),'空')) as strategyName,
        nvl(fs.type,nvl((select yy.type from PQS_FLOWSTRATEGY yy where type = 0),null)) as strategyType
        FROM
        PQ_DEVICE pq
        left join PQ_GDINFORMATION gd on gd.GD_INDEX = pq.GD_INDEX
        left join PQ_SUBSTATION sub on sub.SUB_INDEX = PQ.SUB_INDEX
        left join PQS_DICDATA dic on pq.DEVTYPE = dic.DIC_INDEX
        left join PQS_DEVEDASS ded on pq.DEV_INDEX = ded.DEV_INDEX and ded.STATE = 1 and ded.UPDATERESULT = 1
        left join PQS_EDDATA ed on ded.ED_INDEX = ed.ED_INDEX
        left join PQS_DEVFMASS dm on pq.DEV_INDEX = dm.DEV_INDEX
        left join PQS_FLOWMEAL fm on dm.FM_BASE_INDEX = fm.FM_INDEX
        left join PQS_FLOWMEAL fm2 on dm.FM_REAM_INDEX = fm2.FM_INDEX
        left join PQS_DEVFSASS ds on pq.DEV_INDEX=ds.DEV_INDEX
        left join PQS_FLOWSTRATEGY fs on ds.FS_INDEX = fs.FS_INDEX
        WHERE
        pq.DEVMODEL = 1
        <if test="list!=null">
            and pq.DEV_INDEX in
            <foreach collection="list" item="item" open="(" close=")" separator="," index="index">
                #{item}
            </foreach>
        </if>
        order by pq.updateTime desc
    </select>

    <update id="updateDevFlag">
        update pq_device set
        devflag = #{devFlag}
        where dev_index in
        <foreach collection="list" index="index" separator="," open="(" close=")" item="item">
            #{item}
        </foreach>
    </update>


    <delete id="delDevMealAss">
        delete from PQS_DEVFMASS
        where dev_index in
        <foreach collection="list" item="item" open="(" close=")" separator="," index="index">
            #{item}
        </foreach>
    </delete>

    <insert id="addDevMealAss">
        insert into PQS_DEVFMASS
        (dev_index,fm_base_index,
        fm_ream_index,
        updateTime,updateUser)
        <foreach collection="list" item="item" separator="UNION ALL">
            select
            #{item.devIndex},
            #{item.fmBaseIndex},
            #{item.fmReamIndex},
            #{item.updateTime},
            #{item.updateUser}
            from DUAL
        </foreach>
    </insert>

    <delete id="delStrategyAss">
        delete from pqs_devfsass
        where dev_index in
        <foreach collection="list" index="index" item="item" open="(" close=")" separator=",">
            #{item}
        </foreach>
    </delete>

    <insert id="addStrategyAss">
        insert into pqs_devfsass
        (dev_index,fs_index,updateTime,updateUser)
        <foreach collection="list" item="item" separator="UNION ALL">
            select
            #{item.devIndex},
            #{item.fsIndex},
            #{item.updateTime},
            #{item.updateUser}
            from DUAL
        </foreach>
    </insert>


    <!--流量详情页面-->

    <select id="getTerminalFlowDetail" resultType="com.pqs9900.pojo.terminal.FlowDetailRVO">
SELECT
    nvl (actualflow / 1024/1024, 0 ) AS actualflow,
    to_char(timeid,'MM') AS timeid,
    nvl (statflow / 1024/1024, 0 ) AS statflow,
    nvl(
    (
    select
      b.fm_number
    FROM
  PQS_DEVFMASS a1
  LEFT JOIN PQS_FLOWMEAL b ON a1.fm_base_index = b.fm_index
  where a1.dev_index = #{devIndex}
  ),(nvl (( SELECT FM_NUMBER FROM PQS_FLOWMEAL WHERE type = 0 AND SECOND_TYPE = 0 ), 0)))as baseFlow,
    nvl(
    ( select nvl ( c.fm_number, 0 )
FROM
  PQS_DEVFMASS a
  LEFT JOIN PQS_FLOWMEAL c ON a.fm_ream_index = c.fm_index
  where a.dev_index = #{devIndex}
  ),0)AS extendFlow
  FROM
    PQS_MONTHFLOW
WHERE
dev_index = #{devIndex}
and to_char(timeid,'YYYY-MM')=to_char(to_date(#{startTime},'YYYY-MM-dd'),'YYYY-MM')

	</select>


    <!--流量统计-->

    <select id="getFlowStatiscsList" resultType="com.pqs9900.pojo.terminal.FlowStatisticsRVO">
        SELECT
        alls.* ,(alls.ALLFLOW-alls.ACTUALFLOW)/alls.ALLFLOW as remains
        FROM(
        SELECT
        a.dev_index as devIndex,
        a.name as devName,
        nvl(b.actualflow/1024/1024,0) as actualflow,
        (
        nvl ( d.fm_number, nvl ( ( SELECT FM_NUMBER FROM PQS_FLOWMEAL WHERE type = 0 AND SECOND_TYPE = 0 ), 0 ) ) + nvl
        ( e.fm_number, 0 )
        ) AS allflow
        FROM
        pq_device a
        LEFT JOIN pqs_monthflow b ON a.dev_index = b.dev_index
        AND to_char ( b.timeid, 'yyyy-MM' ) = to_char(to_date(#{startTime},'yyyy-MM-dd'), 'yyyy-MM' )
        LEFT JOIN pqs_devfmass c ON a.dev_index = c.dev_index
        LEFT JOIN pqs_flowmeal d ON c.fm_base_index = d.fm_index
        LEFT JOIN pqs_flowmeal e ON c.fm_ream_index = e.fm_index
        WHERE devmodel = 1
        and a.dev_index in
        <foreach collection="list" item="item" index="index" open="(" separator="," close=")">
            #{item}
        </foreach>
        )alls order by
        <if test="mealBi!=null and mealBi!=''">
            remains ${mealBi}
        </if>
        <if test="mealAll!=null and mealAll!=''">
            allflow ${mealAll}
        </if>
        <if test="mealUse!=null and mealUse!=''">
            actualflow ${mealUse}
        </if>
    </select>

    <!--统计流量图表-->
    <select id="getDayFlowList" resultType="com.pqs9900.pojo.terminal.FlowStatisticsRVO">
        SELECT
	to_char ( timeid, 'yyyy-MM-dd' ) as timeids,
	sum( actualflow )
FROM
	PQS_ACTUALFLOW
WHERE
	dev_index = #{devIndex}
	AND to_char ( timeid, 'yyyy-MM' ) = to_char ( to_date ( #{startTime}, 'yyyy-MM' ), 'yyyy-MM' )
GROUP BY
	to_char ( timeid, 'yyyy-MM-dd' )
ORDER BY
	to_char ( timeid, 'yyyy-MM-dd' )
    </select>

    <!--通过终端id获取前置ip-->
    <select id="getIpByTerminal" resultType="string">
        select b.node_ip from pq_device a
        left join NodeInformation b on a.node_index = b.node_index
        where a.devmodel=1
        and b.node_status=1
        and a.dev_index=#{devIndex}
    </select>


    <!--获取需要重启的前置ip-->
    <select id="getPushInfoList" resultType="string">
	SELECT
	a.node_ip
FROM
	pqs_pushinfo a
	INNER JOIN NODEINFORMATION b ON a.node_ip = b.node_ip
WHERE
	b.node_status = 1
GROUP BY
	a.node_ip
	</select>

</mapper>

