SET NAMES 'GBK';
DELIMITER ;//


/*二级路由发现日志表*/
DROP TABLE IF EXISTS HIS_LEVEL2_ROUTER_FINDER_LOG;
CREATE TABLE HIS_LEVEL2_ROUTER_FINDER_LOG (
  `SU_SEQ` int(11) NOT NULL AUTO_INCREMENT,/*序号*/
  `ROUTER_IP` varchar(20) ,/*路由IP*/
  `ROUTER_MAC` varchar(20) ,/*路由MAC*/
  `FIND_TIME` datetime ,/*发现时间*/
  `LEVEL2_ROUTER_START_FLAG` int(11) NOT NULL DEFAULT '-1',/*二级路由是否启用标志*/
  PRIMARY KEY (`SU_SEQ`),
  KEY `IDX_HIS_LEVEL2_ROUTER_FINDER_LOG_ROUTER_IP` (`ROUTER_IP`),
  KEY `IDX_HIS_LEVEL2_ROUTER_FINDER_LOG_ROUTER_MAC` (`ROUTER_MAC`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk;
SET character_set_client = @saved_cs_client;

DELETE FROM CFG_APP_TYPE WHERE TBL_KEY_NAME in ('MOBILE_APP','MOBILE_APP_SUB');
DELETE FROM CFG_APP_TYPE WHERE TBL_KEY_NAME in ('VPN','VPN_SUB');
DELETE FROM CFG_APP_TYPE WHERE TBL_KEY_NAME in ('OVERWALL','OVERWALL_SUB');
DELETE FROM CFG_APP_TYPE WHERE TBL_KEY_NAME in ('RESERVED6','RESERVED6_SUB');
DELETE FROM CFG_APP_TYPE WHERE TBL_KEY_NAME in ('RESERVED7','RESERVED7_SUB');

/*增加移动APP业务相关表*/
DELETE FROM CFG_APP_TYPE WHERE APP_TYPE=19110000;
DELETE FROM CFG_APP_TYPE WHERE APP_TYPE=19110001;

INSERT INTO CFG_APP_TYPE (APP_TYPE, APP_KIND, DESCRIPTION, TBL_KEY_NAME, APP_KEY_NAME, STATUS, REMARK, SORT_ID) VALUES 
  (19110000,1,'移动APP业务','MOBILE_APP','',1,NULL,90),
  (19110001,1,'移动APP业务子业务','MOBILE_APP_SUB','',1,NULL,91);

DELETE FROM CFG_APP_TABLE_ANALYSE_TYPE WHERE TBL_KEY_NAME='MOBILE_APP';
DELETE FROM CFG_APP_TABLE_ANALYSE_TYPE WHERE TBL_KEY_NAME='MOBILE_APP_SUB';
INSERT INTO CFG_APP_TABLE_ANALYSE_TYPE (APP_KIND, TBL_KEY_NAME, METRIC_GROUP, ANALYSE_TYPE, DESCRIPTION) VALUES 
  (1,'MOBILE_APP','BASE','USR','客户端设备角度'),
  (1,'MOBILE_APP_SUB','BASE','USR','客户端设备角度');
INSERT INTO CFG_APP_TABLE_ANALYSE_TYPE (APP_KIND, TBL_KEY_NAME, METRIC_GROUP, ANALYSE_TYPE, DESCRIPTION) VALUES 
  (1,'MOBILE_APP','BASE','ORG','组织角度'),
  (1,'MOBILE_APP_SUB','BASE','ORG','组织角度');

DELETE FROM CFG_APP_TABLE_ANALYSE_DETAIL WHERE TBL_KEY_NAME='MOBILE_APP';
DELETE FROM CFG_APP_TABLE_ANALYSE_DETAIL WHERE TBL_KEY_NAME='MOBILE_APP_SUB';
INSERT INTO CFG_APP_TABLE_ANALYSE_DETAIL (TBL_KEY_NAME, METRIC_GROUP, ANALYSE_TYPE, CFG_TYPE, 
APP_KIND, CFG_NAME, DESCRIPTION, FIELD_INFO, TOPN_FLAG, SORT_ID) 
SELECT B.TBL_KEY_NAME, A.METRIC_GROUP, A.ANALYSE_TYPE, A.CFG_TYPE, A.APP_KIND,
A.CFG_NAME, A.DESCRIPTION, A.FIELD_INFO, A.TOPN_FLAG, A.SORT_ID
FROM CFG_APP_TABLE_ANALYSE_DETAIL A , (SELECT DISTINCT TBL_KEY_NAME FROM CFG_APP_TYPE ) B
WHERE A.TBL_KEY_NAME='APP' AND 
( B.TBL_KEY_NAME='MOBILE_APP' OR B.TBL_KEY_NAME='MOBILE_APP_SUB');

/*建表*/
CALL PROC_TOTAL_CALL_CREATE_TABLES('NETEMS','MOBILE_APP',1);
CALL PROC_TOTAL_CALL_CREATE_TABLES('NETEMS','MOBILE_APP_SUB',1);

/*增加VPN业务相关表*/
DELETE FROM CFG_APP_TYPE WHERE APP_TYPE=10130000;
DELETE FROM CFG_APP_TYPE WHERE APP_TYPE=10130001;

INSERT INTO CFG_APP_TYPE (APP_TYPE, APP_KIND, DESCRIPTION, TBL_KEY_NAME, APP_KEY_NAME, STATUS, REMARK, SORT_ID) VALUES 
  (10130000,1,'VPN业务','VPN','',1,NULL,90),
  (10130001,1,'VPN业务子业务','VPN_SUB','',1,NULL,91);

DELETE FROM CFG_APP_TABLE_ANALYSE_TYPE WHERE TBL_KEY_NAME='VPN';
DELETE FROM CFG_APP_TABLE_ANALYSE_TYPE WHERE TBL_KEY_NAME='VPN_SUB';
INSERT INTO CFG_APP_TABLE_ANALYSE_TYPE (APP_KIND, TBL_KEY_NAME, METRIC_GROUP, ANALYSE_TYPE, DESCRIPTION) VALUES 
  (1,'VPN','BASE','USR','客户端设备角度'),
  (1,'VPN_SUB','BASE','USR','客户端设备角度');
INSERT INTO CFG_APP_TABLE_ANALYSE_TYPE (APP_KIND, TBL_KEY_NAME, METRIC_GROUP, ANALYSE_TYPE, DESCRIPTION) VALUES 
  (1,'VPN','BASE','ORG','组织角度'),
  (1,'VPN_SUB','BASE','ORG','组织角度');

DELETE FROM CFG_APP_TABLE_ANALYSE_DETAIL WHERE TBL_KEY_NAME='VPN';
DELETE FROM CFG_APP_TABLE_ANALYSE_DETAIL WHERE TBL_KEY_NAME='VPN_SUB';
INSERT INTO CFG_APP_TABLE_ANALYSE_DETAIL (TBL_KEY_NAME, METRIC_GROUP, ANALYSE_TYPE, CFG_TYPE, 
APP_KIND, CFG_NAME, DESCRIPTION, FIELD_INFO, TOPN_FLAG, SORT_ID) 
SELECT B.TBL_KEY_NAME, A.METRIC_GROUP, A.ANALYSE_TYPE, A.CFG_TYPE, A.APP_KIND,
A.CFG_NAME, A.DESCRIPTION, A.FIELD_INFO, A.TOPN_FLAG, A.SORT_ID
FROM CFG_APP_TABLE_ANALYSE_DETAIL A , (SELECT DISTINCT TBL_KEY_NAME FROM CFG_APP_TYPE ) B
WHERE A.TBL_KEY_NAME='APP' AND 
( B.TBL_KEY_NAME='VPN' OR B.TBL_KEY_NAME='VPN_SUB');

/*建表*/
CALL PROC_TOTAL_CALL_CREATE_TABLES('NETEMS','VPN',1);
CALL PROC_TOTAL_CALL_CREATE_TABLES('NETEMS','VPN_SUB',1);

/*增加翻墙业务相关表*/
DELETE FROM CFG_APP_TYPE WHERE APP_TYPE=19120000;
DELETE FROM CFG_APP_TYPE WHERE APP_TYPE=19120001;

INSERT INTO CFG_APP_TYPE (APP_TYPE, APP_KIND, DESCRIPTION, TBL_KEY_NAME, APP_KEY_NAME, STATUS, REMARK, SORT_ID) VALUES 
  (19120000,1,'翻墙业务','OVERWALL','',1,NULL,90),
  (19120001,1,'翻墙业务子业务','OVERWALL_SUB','',1,NULL,91);

DELETE FROM CFG_APP_TABLE_ANALYSE_TYPE WHERE TBL_KEY_NAME='OVERWALL';
DELETE FROM CFG_APP_TABLE_ANALYSE_TYPE WHERE TBL_KEY_NAME='OVERWALL_SUB';
INSERT INTO CFG_APP_TABLE_ANALYSE_TYPE (APP_KIND, TBL_KEY_NAME, METRIC_GROUP, ANALYSE_TYPE, DESCRIPTION) VALUES 
  (1,'OVERWALL','BASE','USR','客户端设备角度'),
  (1,'OVERWALL_SUB','BASE','USR','客户端设备角度');
INSERT INTO CFG_APP_TABLE_ANALYSE_TYPE (APP_KIND, TBL_KEY_NAME, METRIC_GROUP, ANALYSE_TYPE, DESCRIPTION) VALUES 
  (1,'OVERWALL','BASE','ORG','组织角度'),
  (1,'OVERWALL_SUB','BASE','ORG','组织角度');

DELETE FROM CFG_APP_TABLE_ANALYSE_DETAIL WHERE TBL_KEY_NAME='OVERWALL';
DELETE FROM CFG_APP_TABLE_ANALYSE_DETAIL WHERE TBL_KEY_NAME='OVERWALL_SUB';
INSERT INTO CFG_APP_TABLE_ANALYSE_DETAIL (TBL_KEY_NAME, METRIC_GROUP, ANALYSE_TYPE, CFG_TYPE, 
APP_KIND, CFG_NAME, DESCRIPTION, FIELD_INFO, TOPN_FLAG, SORT_ID) 
SELECT B.TBL_KEY_NAME, A.METRIC_GROUP, A.ANALYSE_TYPE, A.CFG_TYPE, A.APP_KIND,
A.CFG_NAME, A.DESCRIPTION, A.FIELD_INFO, A.TOPN_FLAG, A.SORT_ID
FROM CFG_APP_TABLE_ANALYSE_DETAIL A , (SELECT DISTINCT TBL_KEY_NAME FROM CFG_APP_TYPE ) B
WHERE A.TBL_KEY_NAME='APP' AND 
( B.TBL_KEY_NAME='OVERWALL' OR B.TBL_KEY_NAME='OVERWALL_SUB');

/*建表*/
CALL PROC_TOTAL_CALL_CREATE_TABLES('NETEMS','OVERWALL',1);
CALL PROC_TOTAL_CALL_CREATE_TABLES('NETEMS','OVERWALL_SUB',1);



/*增加预留业务6相关表*/
DELETE FROM CFG_APP_TYPE WHERE APP_TYPE=10180000;
DELETE FROM CFG_APP_TYPE WHERE APP_TYPE=10180001;

INSERT INTO CFG_APP_TYPE (APP_TYPE, APP_KIND, DESCRIPTION, TBL_KEY_NAME, APP_KEY_NAME, STATUS, REMARK, SORT_ID) VALUES 
  (10180000,1,'预留业务6','RESERVED6','',1,NULL,90),
  (10180001,1,'预留业务6子业务','RESERVED6_SUB','',1,NULL,91);

DELETE FROM CFG_APP_TABLE_ANALYSE_TYPE WHERE TBL_KEY_NAME='RESERVED6';
DELETE FROM CFG_APP_TABLE_ANALYSE_TYPE WHERE TBL_KEY_NAME='RESERVED6_SUB';
INSERT INTO CFG_APP_TABLE_ANALYSE_TYPE (APP_KIND, TBL_KEY_NAME, METRIC_GROUP, ANALYSE_TYPE, DESCRIPTION) VALUES 
  (1,'RESERVED6','BASE','USR','客户端设备角度'),
  (1,'RESERVED6_SUB','BASE','USR','客户端设备角度');
INSERT INTO CFG_APP_TABLE_ANALYSE_TYPE (APP_KIND, TBL_KEY_NAME, METRIC_GROUP, ANALYSE_TYPE, DESCRIPTION) VALUES 
  (1,'RESERVED6','BASE','ORG','组织角度'),
  (1,'RESERVED6_SUB','BASE','ORG','组织角度');

DELETE FROM CFG_APP_TABLE_ANALYSE_DETAIL WHERE TBL_KEY_NAME='RESERVED6';
DELETE FROM CFG_APP_TABLE_ANALYSE_DETAIL WHERE TBL_KEY_NAME='RESERVED6_SUB';
INSERT INTO CFG_APP_TABLE_ANALYSE_DETAIL (TBL_KEY_NAME, METRIC_GROUP, ANALYSE_TYPE, CFG_TYPE, 
APP_KIND, CFG_NAME, DESCRIPTION, FIELD_INFO, TOPN_FLAG, SORT_ID) 
SELECT B.TBL_KEY_NAME, A.METRIC_GROUP, A.ANALYSE_TYPE, A.CFG_TYPE, A.APP_KIND,
A.CFG_NAME, A.DESCRIPTION, A.FIELD_INFO, A.TOPN_FLAG, A.SORT_ID
FROM CFG_APP_TABLE_ANALYSE_DETAIL A , (SELECT DISTINCT TBL_KEY_NAME FROM CFG_APP_TYPE ) B
WHERE A.TBL_KEY_NAME='APP' AND 
( B.TBL_KEY_NAME='RESERVED6' OR B.TBL_KEY_NAME='RESERVED6_SUB');

/*建表*/
CALL PROC_TOTAL_CALL_CREATE_TABLES('NETEMS','RESERVED6',1);
CALL PROC_TOTAL_CALL_CREATE_TABLES('NETEMS','RESERVED6_SUB',1);

/*增加预留业务7相关表*/
DELETE FROM CFG_APP_TYPE WHERE APP_TYPE=10190000;
DELETE FROM CFG_APP_TYPE WHERE APP_TYPE=10190001;

INSERT INTO CFG_APP_TYPE (APP_TYPE, APP_KIND, DESCRIPTION, TBL_KEY_NAME, APP_KEY_NAME, STATUS, REMARK, SORT_ID) VALUES 
  (10190000,1,'预留业务7','RESERVED7','',1,NULL,90),
  (10190001,1,'预留业务7子业务','RESERVED7_SUB','',1,NULL,91);

DELETE FROM CFG_APP_TABLE_ANALYSE_TYPE WHERE TBL_KEY_NAME='RESERVED7';
DELETE FROM CFG_APP_TABLE_ANALYSE_TYPE WHERE TBL_KEY_NAME='RESERVED7_SUB';
INSERT INTO CFG_APP_TABLE_ANALYSE_TYPE (APP_KIND, TBL_KEY_NAME, METRIC_GROUP, ANALYSE_TYPE, DESCRIPTION) VALUES 
  (1,'RESERVED7','BASE','USR','客户端设备角度'),
  (1,'RESERVED7_SUB','BASE','USR','客户端设备角度');
INSERT INTO CFG_APP_TABLE_ANALYSE_TYPE (APP_KIND, TBL_KEY_NAME, METRIC_GROUP, ANALYSE_TYPE, DESCRIPTION) VALUES 
  (1,'RESERVED7','BASE','ORG','组织角度'),
  (1,'RESERVED7_SUB','BASE','ORG','组织角度');

DELETE FROM CFG_APP_TABLE_ANALYSE_DETAIL WHERE TBL_KEY_NAME='RESERVED7';
DELETE FROM CFG_APP_TABLE_ANALYSE_DETAIL WHERE TBL_KEY_NAME='RESERVED7_SUB';
INSERT INTO CFG_APP_TABLE_ANALYSE_DETAIL (TBL_KEY_NAME, METRIC_GROUP, ANALYSE_TYPE, CFG_TYPE, 
APP_KIND, CFG_NAME, DESCRIPTION, FIELD_INFO, TOPN_FLAG, SORT_ID) 
SELECT B.TBL_KEY_NAME, A.METRIC_GROUP, A.ANALYSE_TYPE, A.CFG_TYPE, A.APP_KIND,
A.CFG_NAME, A.DESCRIPTION, A.FIELD_INFO, A.TOPN_FLAG, A.SORT_ID
FROM CFG_APP_TABLE_ANALYSE_DETAIL A , (SELECT DISTINCT TBL_KEY_NAME FROM CFG_APP_TYPE ) B
WHERE A.TBL_KEY_NAME='APP' AND 
( B.TBL_KEY_NAME='RESERVED7' OR B.TBL_KEY_NAME='RESERVED7_SUB');

/*建表*/
CALL PROC_TOTAL_CALL_CREATE_TABLES('NETEMS','RESERVED7',1);
CALL PROC_TOTAL_CALL_CREATE_TABLES('NETEMS','RESERVED7_SUB',1);




UPDATE CFG_PRODUCT_VARIABLES SET VALUE='1.1.2.9' WHERE VARIABLE_NAME='DB_VERSION';
