<?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.ruoyi.system.mapper.EpsNodeBandwidthMapper">

    <resultMap type="EpsNodeBandwidth" id="EpsNodeBandwidthResult">
        <result property="id"    column="id"    />
        <result property="nodeName"    column="node_name"    />
        <result property="hardwareSn"    column="hardware_sn"    />
        <result property="calculationMode"    column="calculation_mode"    />
        <result property="bandwidthType"    column="bandwidth_type"    />
        <result property="bandwidthResult"    column="bandwidth_result"    />
        <result property="bandwidth95Daily"    column="bandwidth_95_daily"    />
        <result property="bandwidth95Monthly"    column="bandwidth_95_monthly"    />
        <result property="avgMonthlyBandwidth95"    column="avg_monthly_bandwidth_95"    />
        <result property="packageBandwidthDaily"    column="package_bandwidth_daily"    />
        <result property="machineFlow"    column="machine_flow"    />
        <result property="uplinkSwitch"    column="uplink_switch"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateTime"    column="update_time"    />
        <result property="switchSn"    column="switch_sn"    />
        <result property="interfaceName"    column="interface_name"    />
        <result property="resourceType"    column="resource_type"    />
        <result property="interfaceLinkDeviceType"    column="interface_link_device_type"    />
        <result property="effectiveBandwidth95Daily"    column="effective_bandwidth_95_daily"    />
        <result property="effectiveBandwidth95Monthly"    column="effective_bandwidth_95_monthly"    />
        <result property="effectiveAvgMonthlyBandwidth95"    column="effective_avg_monthly_bandwidth_95"    />
        <result property="businessName"    column="business_name"    />
        <result property="businessId"    column="business_id"    />
        <result property="remark1"    column="remark1"    />
        <result property="createDatetime"    column="create_datetime"    />
        <result property="clientId"    column="client_id"    />
        <result property="serverClientId"    column="server_client_id"    />
        <result property="createBy"    column="create_by"    />
        <result property="updateBy"    column="update_by"    />
    </resultMap>

    <sql id="selectEpsNodeBandwidthVo">
        select id, node_name, hardware_sn, calculation_mode, bandwidth_type, bandwidth_result, bandwidth_95_daily, bandwidth_95_monthly, avg_monthly_bandwidth_95, package_bandwidth_daily, machine_flow, uplink_switch, create_time, update_time, switch_sn, interface_name, resource_type, interface_link_device_type, effective_bandwidth_95_daily, effective_bandwidth_95_monthly, effective_avg_monthly_bandwidth_95, business_name, business_id, remark1, create_datetime, client_id, server_client_id, create_by, update_by from eps_node_bandwidth
    </sql>

    <select id="selectEpsNodeBandwidthList" parameterType="EpsNodeBandwidth" resultMap="EpsNodeBandwidthResult">
        <include refid="selectEpsNodeBandwidthVo"/>
        <where>
            <if test="nodeName != null  and nodeName != ''"> and node_name like concat('%', #{nodeName}, '%')</if>
            <if test="hardwareSn != null  and hardwareSn != ''"> and hardware_sn = #{hardwareSn}</if>
            <if test="calculationMode != null  and calculationMode != ''"> and calculation_mode = #{calculationMode}</if>
            <if test="bandwidthType != null  and bandwidthType != ''"> and bandwidth_type = #{bandwidthType}</if>
            <if test="bandwidthResult != null "> and bandwidth_result = #{bandwidthResult}</if>
            <if test="bandwidth95Daily != null "> and bandwidth_95_daily = #{bandwidth95Daily}</if>
            <if test="bandwidth95Monthly != null "> and bandwidth_95_monthly = #{bandwidth95Monthly}</if>
            <if test="avgMonthlyBandwidth95 != null "> and avg_monthly_bandwidth_95 = #{avgMonthlyBandwidth95}</if>
            <if test="packageBandwidthDaily != null "> and package_bandwidth_daily = #{packageBandwidthDaily}</if>
            <if test="machineFlow != null "> and machine_flow = #{machineFlow}</if>
            <if test="uplinkSwitch != null  and uplinkSwitch != ''"> and uplink_switch like concat('%', #{uplinkSwitch}, '%')</if>
            <if test="switchSn != null  and switchSn != ''"> and switch_sn = #{switchSn}</if>
            <if test="interfaceName != null  and interfaceName != ''"> and interface_name like concat('%', #{interfaceName}, '%')</if>
            <if test="resourceType != null  and resourceType != ''"> and resource_type = #{resourceType}</if>
            <if test="interfaceLinkDeviceType != null  and interfaceLinkDeviceType != ''"> and interface_link_device_type = #{interfaceLinkDeviceType}</if>
            <if test="effectiveBandwidth95Daily != null "> and effective_bandwidth_95_daily = #{effectiveBandwidth95Daily}</if>
            <if test="effectiveBandwidth95Monthly != null "> and effective_bandwidth_95_monthly = #{effectiveBandwidth95Monthly}</if>
            <if test="effectiveAvgMonthlyBandwidth95 != null "> and effective_avg_monthly_bandwidth_95 = #{effectiveAvgMonthlyBandwidth95}</if>
            <if test="businessName != null  and businessName != ''"> and business_name like concat('%', #{businessName}, '%')</if>
            <if test="businessId != null  and businessId != ''"> and business_id = #{businessId}</if>
            <if test="remark1 != null  and remark1 != ''"> and remark1 = #{remark1}</if>
            <if test="createDatetime != null "> and create_datetime = #{createDatetime}</if>
            <if test="clientId != null  and clientId != ''"> and client_id = #{clientId}</if>
            <if test="serverClientId != null  and serverClientId != ''"> and server_client_id = #{serverClientId}</if>
            <if test="startTime != null  and startTime != ''"> and create_time &gt;= #{startTime}</if>
            <if test="endTime != null  and endTime != ''"> and create_time &lt;= #{endTime}</if>
            <if test="createTime != null"> and create_time = #{createTime}</if>
            <if test="nodeNames != null and nodeNames.size() > 0">
                AND node_name IN
                <foreach collection="nodeNames" item="name" open="(" separator="," close=")">
                    #{name}
                </foreach>
            </if>
            <if test="switchNames != null and switchNames.size() > 0">
                AND uplink_switch IN
                <foreach collection="switchNames" item="switchName" open="(" separator="," close=")">
                    #{switchName}
                </foreach>
            </if>
        </where>
         order by create_time desc
    </select>

    <select id="selectEpsNodeBandwidthById" parameterType="Long" resultMap="EpsNodeBandwidthResult">
        <include refid="selectEpsNodeBandwidthVo"/>
        where id = #{id}
    </select>

    <insert id="insertEpsNodeBandwidth" parameterType="EpsNodeBandwidth" useGeneratedKeys="true" keyProperty="id">
        insert into eps_node_bandwidth
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="nodeName != null">node_name,</if>
            <if test="hardwareSn != null">hardware_sn,</if>
            <if test="calculationMode != null">calculation_mode,</if>
            <if test="bandwidthType != null">bandwidth_type,</if>
            <if test="bandwidthResult != null">bandwidth_result,</if>
            <if test="bandwidth95Daily != null">bandwidth_95_daily,</if>
            <if test="bandwidth95Monthly != null">bandwidth_95_monthly,</if>
            <if test="avgMonthlyBandwidth95 != null">avg_monthly_bandwidth_95,</if>
            <if test="packageBandwidthDaily != null">package_bandwidth_daily,</if>
            <if test="machineFlow != null">machine_flow,</if>
            <if test="uplinkSwitch != null">uplink_switch,</if>
            <if test="switchSn != null">switch_sn,</if>
            <if test="interfaceName != null">interface_name,</if>
            <if test="resourceType != null">resource_type,</if>
            <if test="interfaceLinkDeviceType != null">interface_link_device_type,</if>
            <if test="effectiveBandwidth95Daily != null">effective_bandwidth_95_daily,</if>
            <if test="effectiveBandwidth95Monthly != null">effective_bandwidth_95_monthly,</if>
            <if test="effectiveAvgMonthlyBandwidth95 != null">effective_avg_monthly_bandwidth_95,</if>
            <if test="businessName != null">business_name,</if>
            <if test="businessId != null">business_id,</if>
            <if test="remark1 != null">remark1,</if>
            <if test="createDatetime != null">create_datetime,</if>
            <if test="clientId != null">client_id,</if>
            <if test="serverClientId != null">server_client_id,</if>
            <if test="createBy != null">create_by,</if>
            <if test="updateBy != null">update_by,</if>
            create_time,
            update_time,
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="nodeName != null">#{nodeName},</if>
            <if test="hardwareSn != null">#{hardwareSn},</if>
            <if test="calculationMode != null">#{calculationMode},</if>
            <if test="bandwidthType != null">#{bandwidthType},</if>
            <if test="bandwidthResult != null">#{bandwidthResult},</if>
            <if test="bandwidth95Daily != null">#{bandwidth95Daily},</if>
            <if test="bandwidth95Monthly != null">#{bandwidth95Monthly},</if>
            <if test="avgMonthlyBandwidth95 != null">#{avgMonthlyBandwidth95},</if>
            <if test="packageBandwidthDaily != null">#{packageBandwidthDaily},</if>
            <if test="machineFlow != null">#{machineFlow},</if>
            <if test="uplinkSwitch != null">#{uplinkSwitch},</if>
            <if test="switchSn != null">#{switchSn},</if>
            <if test="interfaceName != null">#{interfaceName},</if>
            <if test="resourceType != null">#{resourceType},</if>
            <if test="interfaceLinkDeviceType != null">#{interfaceLinkDeviceType},</if>
            <if test="effectiveBandwidth95Daily != null">#{effectiveBandwidth95Daily},</if>
            <if test="effectiveBandwidth95Monthly != null">#{effectiveBandwidth95Monthly},</if>
            <if test="effectiveAvgMonthlyBandwidth95 != null">#{effectiveAvgMonthlyBandwidth95},</if>
            <if test="businessName != null">#{businessName},</if>
            <if test="businessId != null">#{businessId},</if>
            <if test="remark1 != null">#{remark1},</if>
            <if test="createDatetime != null">#{createDatetime},</if>
            <if test="clientId != null">#{clientId},</if>
            <if test="serverClientId != null">#{serverClientId},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <choose>
                <when test="createTime != null">
                    #{createTime},
                </when>
                <otherwise>
                    NOW(),
                </otherwise>
            </choose>
            <choose>
                <when test="updateTime != null">
                    #{updateTime},
                </when>
                <otherwise>
                    NOW(),
                </otherwise>
            </choose>
        </trim>
    </insert>

    <update id="updateEpsNodeBandwidth" parameterType="EpsNodeBandwidth">
        update eps_node_bandwidth
        <trim prefix="SET" suffixOverrides=",">
            <if test="nodeName != null">node_name = #{nodeName},</if>
            <if test="hardwareSn != null">hardware_sn = #{hardwareSn},</if>
            <if test="calculationMode != null">calculation_mode = #{calculationMode},</if>
            <if test="bandwidthType != null">bandwidth_type = #{bandwidthType},</if>
            <if test="bandwidthResult != null">bandwidth_result = #{bandwidthResult},</if>
            <if test="bandwidth95Daily != null">bandwidth_95_daily = #{bandwidth95Daily},</if>
            <if test="bandwidth95Monthly != null">bandwidth_95_monthly = #{bandwidth95Monthly},</if>
            <if test="avgMonthlyBandwidth95 != null">avg_monthly_bandwidth_95 = #{avgMonthlyBandwidth95},</if>
            <if test="packageBandwidthDaily != null">package_bandwidth_daily = #{packageBandwidthDaily},</if>
            <if test="machineFlow != null">machine_flow = #{machineFlow},</if>
            <if test="uplinkSwitch != null">uplink_switch = #{uplinkSwitch},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="switchSn != null">switch_sn = #{switchSn},</if>
            <if test="interfaceName != null">interface_name = #{interfaceName},</if>
            <if test="resourceType != null">resource_type = #{resourceType},</if>
            <if test="interfaceLinkDeviceType != null">interface_link_device_type = #{interfaceLinkDeviceType},</if>
            <if test="effectiveBandwidth95Daily != null">effective_bandwidth_95_daily = #{effectiveBandwidth95Daily},</if>
            <if test="effectiveBandwidth95Monthly != null">effective_bandwidth_95_monthly = #{effectiveBandwidth95Monthly},</if>
            <if test="effectiveAvgMonthlyBandwidth95 != null">effective_avg_monthly_bandwidth_95 = #{effectiveAvgMonthlyBandwidth95},</if>
            <if test="businessName != null">business_name = #{businessName},</if>
            <if test="businessId != null">business_id = #{businessId},</if>
            <if test="remark1 != null">remark1 = #{remark1},</if>
            <if test="createDatetime != null">create_datetime = #{createDatetime},</if>
            <if test="clientId != null">client_id = #{clientId},</if>
            <if test="serverClientId != null">server_client_id = #{serverClientId},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
        </trim>
        where id = #{id}
    </update>
    <update id="updateEpsNodeBandwidthByServerSn" parameterType="EpsNodeBandwidth">
        update eps_node_bandwidth
        <trim prefix="SET" suffixOverrides=",">
            <if test="nodeName != null">node_name = #{nodeName},</if>
        </trim>
        where hardware_sn = #{hardwareSn}
    </update>
    <update id="updateEpsNodeBandwidthBySwitchSn" parameterType="EpsNodeBandwidth">
        update eps_node_bandwidth
        <trim prefix="SET" suffixOverrides=",">
            <if test="uplinkSwitch != null">uplink_switch = #{uplinkSwitch},</if>
        </trim>
        where switch_sn = #{switchSn}
    </update>

    <delete id="deleteEpsNodeBandwidthById" parameterType="Long">
        delete from eps_node_bandwidth where id = #{id}
    </delete>

    <delete id="deleteEpsNodeBandwidthByIds" parameterType="String">
        delete from eps_node_bandwidth where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <select id="calculateAvg" parameterType="EpsNodeBandwidth" resultType="EpsNodeBandwidth">
        select sum(ifnull(bandwidth_95_daily,0)) bandwidth95Daily,
               sum(ifnull(effective_bandwidth_95_daily,0)) effectiveBandwidth95Daily
        from eps_node_bandwidth
        <where>
            <if test="startTime != null">
                and create_time &gt;= #{startTime}
            </if>
            <if test="endTime != null">
                and create_time &lt;= #{endTime}
            </if>
            <if test="monthTime != null">
                and left(create_time,7) = #{monthTime}
            </if>
            <if test="nodeName != '' and nodeName != null">
                and node_name = #{nodeName}
            </if>
            <if test="hardwareSn != '' and hardwareSn != null">
                and hardware_sn = #{hardwareSn}
            </if>
            <if test="switchSn != '' and switchSn != null">
                and switch_sn = #{switchSn}
            </if>
            <if test="interfaceName != '' and interfaceName != null">
                and interface_name = #{interfaceName}
            </if>
            <if test="businessName != '' and businessName != null">
                and business_name = #{businessName}
            </if>
            <if test="businessId != '' and businessId != null">
                and business_id = #{businessId}
            </if>
            <if test="calculationMode != '' and calculationMode != null">
                and calculation_mode = #{calculationMode}
            </if>
            <if test="clientId != '' and clientId != null">
                and client_id = #{clientId}
            </if>
        </where>
    </select>

    <select id="getAvgDetailMsg" parameterType="EpsNodeBandwidth" resultMap="EpsNodeBandwidthResult">
        <include refid="selectEpsNodeBandwidthVo"/>
        <where>
            <if test="createTime != null">
                and left(create_time,7) = left(#{createTime},7)
            </if>
            <if test="hardwareSn != '' and hardwareSn != null">
                and hardware_sn = #{hardwareSn}
            </if>
            <if test="nodeName != '' and nodeName != null">
                and node_name = #{nodeName}
            </if>
            <if test="businessName != '' and businessName != null">
                and business_name = #{businessName}
            </if>
            <if test="businessId != '' and businessId != null">
                and business_id = #{businessId}
            </if>
            <if test="clientId != '' and clientId != null">
                and client_id = #{clientId}
            </if>
        </where>
    </select>

    <select id="countByAvgMsg" parameterType="EpsNodeBandwidth">
        select count(1) from from eps_node_bandwidth
        <where>
            <if test="createTime != null">
                and left(create_time,7) = #{createTime}
            </if>
            <if test="nodeName != '' and nodeName != null">
                and node_name = #{nodeName}
            </if>
            <if test="businessName != '' and businessName != null">
                and business_name = #{businessName}
            </if>
            <if test="businessId != '' and businessId != null">
                and business_id = #{businessId}
            </if>
            <if test="clientId != '' and clientId != null">
                and client_id = #{clientId}
            </if>
        </where>
    </select>
    <select id="getListByMonitorView" parameterType="EpsNodeBandwidth" resultMap="EpsNodeBandwidthResult">
        <include refid="selectEpsNodeBandwidthVo"/>
        <where>
            <if test="hardwareSn != null  and hardwareSn != ''"> and hardware_sn = #{hardwareSn}</if>
            <if test="calculationMode != null  and calculationMode != ''"> and calculation_mode = #{calculationMode}</if>
            <if test="bandwidthType != null  and bandwidthType != ''"> and bandwidth_type = #{bandwidthType}</if>
            <if test="resourceType != null  and resourceType != ''"> and resource_type = #{resourceType}</if>
            <if test="startTime != null  and startTime != ''"> and create_time &gt;= #{startTime}</if>
            <if test="endTime != null  and endTime != ''"> and create_time &lt;= #{endTime}</if>
            <if test="clientIds != '' and clientIds != null">
                and client_id in
                <foreach collection="clientIds.split(',')" item="clientId" open="(" separator="," close=")">
                    #{clientId}
                </foreach>
            </if>
        </where>
        order by bandwidth_95_daily asc
    </select>
</mapper>