consts.java

sla性能采集

package com.asiainfo.ainx.slaconf.util;

public class Consts {
	public static final String SLA_ORACLE="sla.oracle";
	/**
	 * 根据查询集合综合显示链路指标的SLA信息
	 */

	public static final String RES_LINK="select n.id as res_id,n.alias,o.sysname " +
			"as from_sysname,p.ifname as from_ifname,to_char(n.bandwidth/1000000, '999999999.99') as bandwidth from reslinks n,resnodes o," +
			"resinterfaces p where n.from_nodeid=o.id and n.from_ifid=p.id and n.id in ";
	/**
	 * 返回扄1�7有link的sla信息
	 */
	public static final String RES_ALL_LINK="select distinct v.res_id,v.name,v.from_sysname,v.from_ifname,v.bandwidth " +
			"from (select n.id as res_id,n.name,o.sysname as from_sysname,p.ifname as from_ifname,to_char(n.bandwidth/1000000, '999999999.99') as bandwidth " +
			"from reslinks n,resnodes o,resinterfaces p where n.from_nodeid=o.id and n.from_ifid=p.id) " +
			"v,sla_config l where v.res_id=l.res_id and l.user_id = ?";
	
	public static final String RES_ALL_LINK_0="select distinct v.res_id,v.name,v.from_sysname,v.from_ifname,v.bandwidth " +
	"from (select n.id as res_id,n.name,o.sysname as from_sysname,p.ifname as from_ifname,to_char(n.bandwidth/1000000, '999999999.99') as bandwidth " +
	"from reslinks n,resnodes o,resinterfaces p where n.from_nodeid=o.id and n.from_ifid=p.id) " +
	"v,sla_config l where v.res_id=l.res_id ";
	
	/**
	 * 根据查询集合综合显示设备指标的SLA信息
	 */

	public static final String RES_NODE="select o.id as res_id,o.sysname,o.snmpaddr,o.alias from resnodes o where o.id in";
	/**
	 * 返回扄1�7有node的sla信息
	 */
	public static final String RES_ALL_NODE="select distinct o.id as res_id,o.sysname,o.snmpaddr,o.alias from resnodes o,sla_config b where o.id = b.res_id and b.user_id = ? ";

	public static final String RES_ALL_NODE_0="select distinct o.id as res_id,o.sysname,o.snmpaddr,o.alias from resnodes o,sla_config b where o.id = b.res_id ";

	
	/**
	 * 查询链路相关的SLA指标的数釄1�7
	 */
	public static final String RES_COUNT_SLA_LINK="select count(*) from item_define a where a.restype=3";
	/**
	 * 查询
	 */
	public static final String RES_SLAS_LINK="select distinct a.itemid from item_define a where a.restype=3";
	public static final String RES_SLAS_NODE="select distinct a.itemid from item_define a where a.restype=5";
   /**
    * 查询设备相关的SLA指标的数釄1�7
    */
	public static final String RES_COUNT_SLA_NODE="select count(*) from item_define a where a.restype=5";
	/**
	 * 填充设备名称下拉列表框用
	 */
	public static final String SELECT_ALL_SYSNAME = "SELECT  a.childid id, a.alias alias, a.status status, b.nxoid" +
	" FROM (" +
	" SELECT aa.parentid, aa.childid , ab.alias, " +
	"      ab.status status" +
	"      FROM resparchild aa, resnodes ab      " +
	"      WHERE aa.childid = ab.ID AND aa.childtype = 1" +
	" UNION" +
	" SELECT resparchild.parentid, resparchild.childid, reslinks.alias NAME," +
	"	 reslinks.status status" +
	"   FROM resparchild, reslinks" +
	" WHERE resparchild.childid = reslinks.ID AND resparchild.childtype = 1" +
	"  ) a," +
	" (SELECT nxoid, visiable, canman, canmod, cangrant, isowner" +
	" FROM (SELECT   ba.nxoid, MAX (ba.visible) visiable," +
	"	 MAX (ba.canman) canman, MAX (ba.canmod) canmod," +
	"    MAX (ba.cangrant) cangrant, MAX (ba.isowner) isowner" +
	"    FROM (SELECT DISTINCT acl1.nxoid, acl1.visible," +
	"    acl1.canman, acl1.canmod," +
	"    acl1.cangrant, acl1.isowner" +
	"    FROM nodeaclitem acl1" +
	" WHERE acl1.nxuid = ? AND acl1.nxutype = 1" +
	" UNION" +
	" SELECT DISTINCT acl.nxoid, acl.visible, acl.canman," +
	"    acl.canmod, acl.cangrant,acl.isowner" +
	" FROM nodeaclitem acl" +
	" WHERE acl.nxutype = 2" +
	"      AND acl.nxuid IN ( SELECT g.groupid" +
	"      FROM cmnxuser u, cmnxugroup g, cmnxugmember m" +
	"   WHERE u.userid = ? AND u.userid = m.userid AND m.groupid = g.groupid)) ba" +
	"  GROUP BY ba.nxoid) bb" +
	" WHERE canman > -1 OR canmod > -1 OR cangrant > -1 OR isowner > -1) b" +
	" WHERE (a.parentid = b.nxoid and a.childid not in(select res_id from sla_config)) ";

	//	"SELECT distinct a.id,a.alias FROM resnodes a where a.id not in (select distinct b.res_id from sla_config b)";
	/** 
	 * 根据用户输入动�1�7�过滤设备名秄1�7
	 */
	public static final String SELECT_NODE_BYNAME = "SELECT a.id, a.alias FROM resnodes a where lower(a.alias) like lower(?)";
	/**
	 * 根据用户输入动�1�7�过滤link名称
	 */
	public static final String SELECT_LINK_BYNAME = "SELECT a.id, a.alias FROM reslinks a where lower(a.alias) like lower(?)";
     /**
      * 根据设备号返回sla信息
      */
	public static final String RES_SLA_BY_ID="select a.res_id,a.item_type,a.lower,a.upper,a.lower_level,a.upper_level from sla_config a where a.res_id=? and a.user_id=?";
	
	public static final String RES_SLA_BY_ID_0="select a.res_id,a.item_type,a.lower,a.upper,a.lower_level,a.upper_level from sla_config a where a.res_id=?";
	
	/**
	 * 返回没被选择的链路名，用于初始加轄1�7
	 */
	public static final String RES_ALL_LINK_CORD ="SELECT  a.childid id, a.alias alias, a.status status, b.nxoid" +
			" FROM (" +
			" SELECT aa.parentid, aa.childid , ab.alias, " +
			"      ab.status status" +
			"      FROM resparchild aa, resnodes ab      " +
			"      WHERE aa.childid = ab.ID AND aa.childtype = 2" +
			" UNION" +
			" SELECT resparchild.parentid, resparchild.childid, reslinks.alias NAME," +
			"	 reslinks.status status" +
			"   FROM resparchild, reslinks" +
			" WHERE resparchild.childid = reslinks.ID AND resparchild.childtype = 2" +
			"  ) a," +
			" (SELECT nxoid, visiable, canman, canmod, cangrant, isowner" +
			" FROM (SELECT   ba.nxoid, MAX (ba.visible) visiable," +
			"	 MAX (ba.canman) canman, MAX (ba.canmod) canmod," +
			"    MAX (ba.cangrant) cangrant, MAX (ba.isowner) isowner" +
			"    FROM (SELECT DISTINCT acl1.nxoid, acl1.visible," +
			"    acl1.canman, acl1.canmod," +
			"    acl1.cangrant, acl1.isowner" +
			"    FROM nodeaclitem acl1" +
			" WHERE acl1.nxuid = ? AND acl1.nxutype = 1" +
			" UNION" +
			" SELECT DISTINCT acl.nxoid, acl.visible, acl.canman," +
			"    acl.canmod, acl.cangrant,acl.isowner" +
			" FROM nodeaclitem acl" +
			" WHERE acl.nxutype = 2" +
			"      AND acl.nxuid IN ( SELECT g.groupid" +
			"      FROM cmnxuser u, cmnxugroup g, cmnxugmember m" +
			"   WHERE u.userid = ? AND u.userid = m.userid AND m.groupid = g.groupid)) ba" +
			"  GROUP BY ba.nxoid) bb" +
			" WHERE canman > -1 OR canmod > -1 OR cangrant > -1 OR isowner > -1) b" +
			" WHERE (a.parentid = b.nxoid and a.childid not in(select res_id from sla_config)) ";
   
	/**
	 * return all nodes name
	 */
	public static final String RES_ALL_NODES_CORD="select a.id,a.alias from resnodes a where id not in (select res_id from sla_config)";
	
	/**
	 * 返回被�1�7�择的链路名
	 */
	public static final String RES_SEL_LINK_CORD="select distinct a.res_id,b.alias from sla_config a, reslinks b,item_define c where a.item_type = c.itemid and c.restype=3 and a.res_id = b.id";
	/**
	 * 返回被�1�7�择的设处1�7
	 */		
	public static final String RES_SEL_NODE_CORD="select distinct a.res_id,b.alias from sla_config a, resnodes b,item_define c where a.item_type = c.itemid and c.restype=5 and a.res_id = b.id";
	
	/**
	 * return all link slaname
	 */
	public static final String RES_ALL_LINK_SLA="select distinct a.itemid,a.itemname,a.itemunit from item_define a where a.restype=3";
	/**
	 * return all node slaname
	 */
	public static final String RES_ALL_NODE_SLA="select distinct a.itemid,a.itemname,a.itemunit from item_define a where a.restype=5";

    /**
     * delete a cord
     */
	public static final String DELETE_FROM_SLA="delete from sla_config a where a.item_type in (select itemid from item_define where restype=?)";
	/**
	 * insert a cord into sla
	 */
	public static final String INSERT_INTO_SLA="insert into sla_config(RES_ID,RES_TYPE,ITEM_TYPE,UPPER,LOWER,UPPER_LEVEL,LOWER_LEVEL,USER_ID) values(?,?,?,?,?,?,?,?)";
}