<?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.tongran.system.mapper.RmResourceRegistrationMapper">

    <resultMap type="RmResourceRegistration" id="RmResourceRegistrationResult">
        <result property="id"    column="id"    />
        <result property="clientId"    column="client_id"    />
        <result property="hardwareSn"    column="hardware_sn"    />
        <result property="businessName"    column="business_name"    />
        <result property="logicalNodeId"    column="logical_node_id"    />
        <result property="multiPublicIpStatus"    column="multi_public_ip_status"    />
        <result property="registrationStatus"    column="registration_status"    />
        <result property="onlineStatus"    column="online_status"    />
        <result property="resourceType"    column="resource_type"    />
        <result property="agentVersion"    column="agent_version"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateTime"    column="update_time"    />
        <result property="heartbeatCount"    column="heartbeat_count"    />
        <result property="heartbeatInterval"    column="heartbeat_interval"    />
        <result property="onboardTime"    column="onboard_time"    />
        <result property="remark"    column="remark"    />
        <result property="createBy"    column="create_by"    />
        <result property="updateBy"    column="update_by"    />
        <result property="reportedBandwidth"    column="reported_bandwidth"    />
        <result property="delAlarmTime"    column="del_alarm_time"    />
        <result property="frpcConnectionStatus"    column="frpc_connection_status"    />
        <result property="frpcRemotePort"    column="frpc_remote_port"    />
        <result property="frpcServerAddr"    column="frpc_server_addr"    />
    </resultMap>

    <sql id="selectRmResourceRegistrationVo">
        select id, client_id, hardware_sn, business_name, logical_node_id, multi_public_ip_status, registration_status, online_status, resource_type, agent_version, create_time, update_time, heartbeat_count, heartbeat_interval, onboard_time, remark, create_by, update_by, reported_bandwidth, del_alarm_time from rm_resource_registration
    </sql>

    <select id="selectRmResourceRegistrationList" parameterType="RmResourceRegistration" resultMap="RmResourceRegistrationResult">
        <include refid="selectRmResourceRegistrationVo"/>
        <where>
            and del_flag=0
            <if test="clientId != null  and clientId != ''"> and client_id = #{clientId}</if>
            <if test="hardwareSn != null  and hardwareSn != ''"> and hardware_sn = #{hardwareSn}</if>
            <if test="businessName != null  and businessName != ''"> and business_name like concat('%', #{businessName}, '%')</if>
            <if test="logicalNodeId != null  and logicalNodeId != ''"> and logical_node_id = #{logicalNodeId}</if>
            <if test="multiPublicIpStatus != null  and multiPublicIpStatus != ''"> and multi_public_ip_status = #{multiPublicIpStatus}</if>
            <if test="registrationStatus != null  and registrationStatus != ''"> and registration_status = #{registrationStatus}</if>
            <if test="onlineStatus != null  and onlineStatus != ''"> and online_status = #{onlineStatus}</if>
            <if test="resourceType != null  and resourceType != ''"> and resource_type = #{resourceType}</if>
            <if test="agentVersion != null  and agentVersion != ''"> and agent_version = #{agentVersion}</if>
            <if test="heartbeatCount != null "> and heartbeat_count = #{heartbeatCount}</if>
            <if test="heartbeatInterval != null "> and heartbeat_interval = #{heartbeatInterval}</if>
            <if test="onboardTime != null "> and onboard_time = #{onboardTime}</if>
        </where>
        order by create_time desc
    </select>

    <select id="selectRmResourceRegistrationById" parameterType="Long" resultType="RmResourceRegistration">
        select
            a.id,
            a.hardware_sn as hardwareSn,
            a.agent_version as agentVersion,
            a.registration_status as registrationStatus,
            a.online_status as onlineStatus,
            a.create_time as createTime,
            a.update_time as updateTime,
            a.client_id as clientId,
            a.business_name as businessName,
            a.logical_node_id as logicalNodeId,
            a.multi_public_ip_status as multiPublicIpStatus,
            a.heartbeat_count as heartbeatCount,
            a.heartbeat_interval as heartbeatInterval,
            a.onboard_time as onboardTime,
            a.remark as remark,
            a.reported_bandwidth as reportedBandwidth,
            a.del_alarm_time as delAlarmTime,
            b.machine_code as machineCode,
            (select bandwidth_result from eps_node_bandwidth
             where client_id=a.client_id and calculation_mode='1000' and bandwidth_type=1
               AND create_time = DATE(DATE_SUB(NOW(), INTERVAL 1 DAY)) limit 1) as bandwidthResult
        from rm_resource_registration a
            left join rm_registration_machine b on a.client_id = b.client_id
        where a.id = #{id}
    </select>

    <insert id="insertRmResourceRegistration" parameterType="RmResourceRegistration" useGeneratedKeys="true" keyProperty="id">
        insert into rm_resource_registration
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="clientId != null">client_id,</if>
            <if test="hardwareSn != null">hardware_sn,</if>
            <if test="businessName != null">business_name,</if>
            <if test="logicalNodeId != null">logical_node_id,</if>
            <if test="multiPublicIpStatus != null">multi_public_ip_status,</if>
            <if test="registrationStatus != null">registration_status,</if>
            <if test="onlineStatus != null">online_status,</if>
            <if test="resourceType != null">resource_type,</if>
            <if test="agentVersion != null">agent_version,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="heartbeatCount != null">heartbeat_count,</if>
            <if test="heartbeatInterval != null">heartbeat_interval,</if>
            <if test="onboardTime != null">onboard_time,</if>
            <if test="remark != null">remark,</if>
            <if test="createBy != null">create_by,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="reportedBandwidth != null">reported_bandwidth,</if>
            <if test="delAlarmTime != null">del_alarm_time,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="clientId != null">#{clientId},</if>
            <if test="hardwareSn != null">#{hardwareSn},</if>
            <if test="businessName != null">#{businessName},</if>
            <if test="logicalNodeId != null">#{logicalNodeId},</if>
            <if test="multiPublicIpStatus != null">#{multiPublicIpStatus},</if>
            <if test="registrationStatus != null">#{registrationStatus},</if>
            <if test="onlineStatus != null">#{onlineStatus},</if>
            <if test="resourceType != null">#{resourceType},</if>
            <if test="agentVersion != null">#{agentVersion},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="heartbeatCount != null">#{heartbeatCount},</if>
            <if test="heartbeatInterval != null">#{heartbeatInterval},</if>
            <if test="onboardTime != null">#{onboardTime},</if>
            <if test="remark != null">#{remark},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="reportedBandwidth != null">#{reportedBandwidth},</if>
            <if test="delAlarmTime != null">#{delAlarmTime},</if>
        </trim>
    </insert>

    <update id="updateRmResourceRegistration" parameterType="RmResourceRegistration">
        update rm_resource_registration
        <trim prefix="SET" suffixOverrides=",">
            <if test="clientId != null">client_id = #{clientId},</if>
            <if test="hardwareSn != null">hardware_sn = #{hardwareSn},</if>
            <if test="businessName != null">business_name = #{businessName},</if>
            <if test="logicalNodeId != null">logical_node_id = #{logicalNodeId},</if>
            <if test="multiPublicIpStatus != null">multi_public_ip_status = #{multiPublicIpStatus},</if>
            <if test="registrationStatus != null">registration_status = #{registrationStatus},</if>
            <if test="onlineStatus != null">online_status = #{onlineStatus},</if>
            <if test="resourceType != null">resource_type = #{resourceType},</if>
            <if test="agentVersion != null">agent_version = #{agentVersion},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="heartbeatCount != null">heartbeat_count = #{heartbeatCount},</if>
            <if test="heartbeatInterval != null">heartbeat_interval = #{heartbeatInterval},</if>
            <if test="onboardTime != null">onboard_time = #{onboardTime},</if>
            <if test="remark != null">remark = #{remark},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="reportedBandwidth != null">reported_bandwidth = #{reportedBandwidth},</if>
            <if test="delAlarmTime != null">del_alarm_time = #{delAlarmTime},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteRmResourceRegistrationById" parameterType="Long">
        delete from rm_resource_registration where id = #{id}
    </delete>

    <delete id="deleteRmResourceRegistrationByIds" parameterType="String">
        delete from rm_resource_registration where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>
    <select id="countBySn" parameterType="String">
        select count(1) from rm_resource_registration
        where hardware_sn = #{hardwareSn}
    </select>
    <update id="updateStatusByResource" parameterType="RmResourceRegistration">
        update rm_resource_registration
        <trim prefix="SET" suffixOverrides=",">
            <if test="clientId != null">client_id = #{clientId},</if>
            <if test="hardwareSn != null">hardware_sn = #{hardwareSn},</if>
            <if test="businessName != null">business_name = #{businessName},</if>
            <if test="logicalNodeId != null">logical_node_id = #{logicalNodeId},</if>
            <if test="multiPublicIpStatus != null">multi_public_ip_status = #{multiPublicIpStatus},</if>
            <if test="registrationStatus != null">registration_status = #{registrationStatus},</if>
            <if test="onlineStatus != null">online_status = #{onlineStatus},</if>
            <if test="resourceType != null">resource_type = #{resourceType},</if>
            <if test="agentVersion != null">agent_version = #{agentVersion},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="heartbeatCount != null">heartbeat_count = #{heartbeatCount},</if>
            <if test="heartbeatInterval != null">heartbeat_interval = #{heartbeatInterval},</if>
            <if test="onboardTime != null">onboard_time = #{onboardTime},</if>
            <if test="remark != null">remark = #{remark},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="reportedBandwidth != null">reported_bandwidth = #{reportedBandwidth},</if>
        </trim>
        <where>
            <choose>
                <when test="hardwareSn != null">
                    and hardware_sn = #{hardwareSn}
                </when>
                <when test="clientId != null">
                    and client_id = #{clientId}
                </when>
                <otherwise>
                    and 1=0 <!-- 如果两个条件都不满足，则不更新任何记录 -->
                </otherwise>
            </choose>
        </where>
    </update>
    <select id="getRegistrationByIds" parameterType="String" resultMap="RmResourceRegistrationResult">
        <include refid="selectRmResourceRegistrationVo"/>
        where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>

    <select id="selectRegistMsgByClientId" parameterType="RmResourceRegistration" resultMap="RmResourceRegistrationResult">
        <include refid="selectRmResourceRegistrationVo"/>
        <where>
            <if test="clientId != null  and clientId != ''"> and client_id = #{clientId}</if>
        </where>
        order by create_time desc
    </select>
    <select id="getAllLogicalNodeId" parameterType="RmResourceRegistration" resultType="java.util.Map">
        select logical_node_id logicalNodeId from rm_resource_registration where logical_node_id != ''
        group by logical_node_id
    </select>
    <select id="getRegistrationTableInfoList" parameterType="RmResourceRegistration" resultType="RmResourceRegistration">
        select
        a.id,
        a.hardware_sn as hardwareSn,
        a.agent_version as agentVersion,
        a.registration_status as registrationStatus,
        a.online_status as onlineStatus,
        a.create_time as createTime,
        a.update_time as updateTime,
        a.client_id as clientId,
        a.business_name as businessName,
        a.logical_node_id as logicalNodeId,
        a.multi_public_ip_status as multiPublicIpStatus,
        a.heartbeat_count as heartbeatCount,
        a.heartbeat_interval as heartbeatInterval,
        a.onboard_time as onboardTime,
        a.remark as remark,
        a.reported_bandwidth as reportedBandwidth,
        a.del_alarm_time as delAlarmTime,
        b.machine_code as machineCode,
        c.frpc_connection_status frpcConnectionStatus,
        c.frpc_remote_port frpcRemotePort,
        c.frpc_server_addr frpcServerAddr,
        (select bandwidth_result from eps_node_bandwidth
                                 where client_id=a.client_id and calculation_mode='1000' and bandwidth_type='1'
                                   AND create_time = DATE(DATE_SUB(NOW(), INTERVAL 1 DAY)) limit 1) as bandwidthResult
        from rm_resource_registration a
        left join rm_registration_machine b on a.client_id = b.client_id
        left join rm_frpc_config_manage c on a.client_id=c.client_id
        <where>
            and a.del_flag=0
            <if test="hardwareSn != null  and hardwareSn != ''"> and a.hardware_sn = #{hardwareSn}</if>
            <if test="resourceType != null  and resourceType != ''"> and a.resource_type = #{resourceType}</if>
            <if test="agentVersion != null  and agentVersion != ''"> and a.agent_version = #{agentVersion}</if>
            <if test="registrationStatus != null  and registrationStatus != ''"> and a.registration_status = #{registrationStatus}</if>
            <if test="onlineStatus != null  and onlineStatus != ''"> and a.online_status = #{onlineStatus}</if>
            <if test="clientId != null  and clientId != ''"> and a.client_id = #{clientId}</if>
            <if test="businessName != null  and businessName != ''"> and a.business_name like concat('%', #{businessName}, '%')</if>
            <if test="logicalNodeId != null  and logicalNodeId != ''"> and a.logical_node_id = #{logicalNodeId}</if>
            <if test="multiPublicIpStatus != null  and multiPublicIpStatus != ''"> and a.multi_public_ip_status = #{multiPublicIpStatus}</if>
            <if test="businessEmpty"> and a.business_name is null</if>
            <if test="logicalNodeEmpty"> and a.logical_node_id is null</if>
        </where>
        order by a.create_time desc
    </select>
    <update id="updateRemark" parameterType="RmResourceRegistration">
        update rm_resource_registration set remark = #{remark}
        where client_id in
        <foreach collection="deployDevice.split(',')" item="clientId" open="(" separator="," close=")">
            #{clientId}
        </foreach>
    </update>
    <update id="addRemark" parameterType="RmResourceRegistration">
        update rm_resource_registration set remark =
        CASE
        WHEN remark IS NULL OR remark = '' THEN #{remark}
        ELSE CONCAT(remark, ',', #{remark})
        END
        where client_id in
        <foreach collection="deployDevice.split(',')" item="clientId" open="(" separator="," close=")">
            #{clientId}
        </foreach>
    </update>
    <update id="addReportedBandwidth" parameterType="RmResourceRegistration">
        update rm_resource_registration set reported_bandwidth = #{reportedBandwidth}
        where id =#{id}
    </update>
    <select id="countChildNetwork" resultType="java.lang.Integer">
        select count(1) from rm_network_interface_child
        where client_id = #{clientId} and parent_interface = #{interfaceName}
    </select>
    <select id="getCpuUtil" resultType="RmResourceRegistration">
        SELECT
        d.client_id as clientId,
        d.cores as cpuCores,
        d.uti as cpuUtil
        FROM initial_cpu_info d
        INNER JOIN (
        SELECT
        client_id,
        MAX(create_time) as latest_time
        FROM initial_cpu_info
        WHERE client_id in
        <foreach collection="clientIdsStr.split(',')" item="clientId" open="(" separator="," close=")">
            #{clientId}
        </foreach>
        GROUP BY client_id
        ) latest ON d.client_id = latest.client_id
        AND d.create_time = latest.latest_time
    </select>
    <select id="getMemUtil" resultType="RmResourceRegistration">
        SELECT
        d.client_id as clientId,
        ROUND(d.collect_value, 1) as memUtil
        FROM initial_system_other_collect_data d
        INNER JOIN (
        SELECT
        client_id,
        MAX(create_time) as max_time
        FROM initial_system_other_collect_data
        WHERE collect_type='memoryUtilizationCollect'
        AND client_id in
        <foreach collection="clientIdsStr.split(',')" item="clientId" open="(" separator="," close=")">
            #{clientId}
        </foreach>
        GROUP BY client_id
        ) latest ON d.client_id = latest.client_id
        AND d.create_time = latest.max_time
        AND d.collect_type='memoryUtilizationCollect'
    </select>
    <update id="removeAlarmFlag" parameterType="RmResourceRegistration">
        update rm_resource_registration set del_alarm_time = now()
        where id =#{id}
    </update>
    <update id="removeServer" parameterType="String">
        update rm_resource_registration set del_flag=1 where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </update>
    <select id="getMaxAgentVersion" resultType="RmResourceRegistration">
        select agent_version maxAgentVersion from rm_resource_registration where del_flag=0
        ORDER BY
            CAST(SUBSTRING_INDEX(agent_version, '.', 1) AS UNSIGNED) DESC,
            CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(agent_version, '.', 2), '.', -1) AS UNSIGNED) DESC,
            CAST(SUBSTRING_INDEX(agent_version, '.', -1) AS UNSIGNED) DESC
        limit 1
    </select>
</mapper>