langchao_fayuan/create.sql

use lawe;

	-- （1）基础设施

-- 建表语句
DROP TABLE IF EXISTS `bi_itm_hardware_resources`;
CREATE TABLE `bi_itm_hardware_resources` (
  `itm_hardware_resources_id` varchar(100) DEFAULT NULL COMMENT '设备ID',
  `itm_hardware_resources_type` varchar(100) DEFAULT NULL COMMENT '设备型号ID',
  `itm_hardware_resources_cntype` varchar(100) DEFAULT NULL COMMENT '设备型号归类',
  `itm_hardware_resources_areacode` varchar(100) DEFAULT NULL COMMENT '设备区域',
  `ITM_HARDWARE_RESOURCES_ASSNO` varchar(100) DEFAULT NULL COMMENT '国资码暂时用来存储设备所属应用名称',
  `ITM_HARDWARE_RESOURCES_CODE` varchar(200) DEFAULT NULL COMMENT '设备编码',
  `ITM_HARDWARE_RESOURCES_NAME` varchar(255) DEFAULT NULL COMMENT '设备名称',
  `ITM_HARDWARE_RESOURCES_IP` varchar(64) DEFAULT NULL COMMENT '设备IP',
  `ITM_HARDWARE_RESOURCES_STATUS` varchar(64) DEFAULT NULL COMMENT '使用状态：在用，停用',
  `itm_monitor_flag` varchar(10) DEFAULT NULL COMMENT '监控状态：监控，未监控',
  `create_time` datetime DEFAULT NULL COMMENT '设备创建时间',
  `NAME` varchar(512) DEFAULT NULL COMMENT '设备名称',
  `resclasscnname` varchar(255) DEFAULT NULL COMMENT '设备类型',
  `NET_NAME` varchar(64) DEFAULT NULL COMMENT '网系',
  `ROOM_JIFANG` varchar(64) DEFAULT NULL COMMENT '所属机房',
  `UID` varchar(100) DEFAULT NULL COMMENT 'UID',
  `pinpai` varchar(255) DEFAULT NULL COMMENT '设备品牌',
  `xinghao` varchar(255) DEFAULT NULL COMMENT '设备型号',
  `mac` varchar(255) DEFAULT NULL COMMENT 'mac地址',
  `guoziguishurenyuan` varchar(255) DEFAULT NULL COMMENT '国资归属人员',
  `guoziguishubumen` varchar(255) DEFAULT NULL COMMENT '国资归属部门',
  `yunweirenyuan` varchar(255) DEFAULT NULL COMMENT '运维人员',
  `servicename` varchar(255) DEFAULT NULL COMMENT '服务app名',
  `relatedappid` varchar(255) DEFAULT NULL COMMENT '关联appid',
  `mantancevendor` varchar(255) DEFAULT NULL COMMENT '运维厂商',
  `cloud` varchar(255) DEFAULT NULL COMMENT '云平台',
  KEY `bi_itm_hardware_resources_itm_hardware_resources_id_IDX` (`itm_hardware_resources_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;






	-- （2）基础设施告警、基础设施故障告警、网络中断趋势、应用系统告警趋势、应用系统故障趋势

-- 建表语句
DROP TABLE IF EXISTS `bi_alm_group_by_time`;
CREATE TABLE `bi_alm_group_by_time` (
  `dtype` varchar(5) NOT NULL DEFAULT '' COMMENT '日期格式',
  `thedvalue` varchar(17) DEFAULT NULL COMMENT '告警时间',
  `alarm_object_ip` varchar(300) DEFAULT NULL COMMENT '告警设备IP',
  `alarm_object_type` varchar(50) NOT NULL DEFAULT '' COMMENT '设备类型',
  `ITM_HARDWARE_RESOURCES_AREACODE` varchar(64) DEFAULT NULL COMMENT '告警设备区域',
  `ITM_HARDWARE_RESOURCES_ASSNO` varchar(100) DEFAULT NULL COMMENT '国资编码',
  `ITM_HARDWARE_RESOURCES_CODE` longtext COMMENT '设备编码',
  `ITM_HARDWARE_RESOURCES_NAME` varchar(512) DEFAULT NULL COMMENT '设备名称',
  `ITM_HARDWARE_RESOURCES_IP` varchar(300) DEFAULT NULL COMMENT '设备IP',
  `pinpai` varchar(255) DEFAULT NULL COMMENT '设备品牌',
  `xinghao` varchar(255) DEFAULT NULL COMMENT '设备型号',
  `ITM_HARDWARE_RESOURCES_NETNAME` varchar(255) DEFAULT NULL COMMENT '网系名称',
  `ITM_HARDWARE_RESOURCES_STATUS` varchar(64) DEFAULT NULL COMMENT '设备状态',
  `severity_type` varchar(2) NOT NULL DEFAULT '' COMMENT '告警等级',
  `title` varchar(256) DEFAULT NULL COMMENT '告警标题',
  `alarm_text` mediumtext COMMENT '告警内容',
  `guoziguishubumen` varchar(255) DEFAULT NULL COMMENT '国资归属部门',
  `yunweirenyuan` varchar(255) DEFAULT NULL COMMENT '运维人员',
  `alarm_count` double DEFAULT NULL COMMENT '告警次数',
  `alarm_time` decimal(58,4) DEFAULT NULL COMMENT '告警时长(h)',
  `alarm_zy_num` decimal(42,0) DEFAULT NULL COMMENT '故障次数',
  `total_zy_num` decimal(29,8) DEFAULT NULL COMMENT '总资产数（取平均）',
  `process_type_name` varchar(64) DEFAULT NULL COMMENT '故障类型',
  `app_id` varchar(64) DEFAULT NULL COMMENT '应用ID',
  `cloud` varchar(255) DEFAULT NULL COMMENT '云平台'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 涉及视图
create or replace view `v_bi_alm_all_alarm` as
select
    `t_alm_active_alarm`.`alarm_object_ip` as `alarm_object_ip`,
    `t_alm_active_alarm`.`alarm_object_id` as `alarm_object_id`,
    `t_alm_active_alarm`.`alarm_object_type` as `alarm_object_codetype`,
    (case
        when (`t_alm_active_alarm`.`alarm_object_type` = '000100001') then '物理机'
        when (`t_alm_active_alarm`.`alarm_object_type` = '000100002') then '云主机'
        when (`t_alm_active_alarm`.`alarm_object_type` = '000100004') then '宿主机'
        when (substr(`t_alm_active_alarm`.`alarm_object_type`, 1, 4) = '0001') then '其他主机'
        when (`t_alm_active_alarm`.`alarm_object_type` = '000200001') then '交换机'
        when (`t_alm_active_alarm`.`alarm_object_type` = '000200002') then '路由器'
        when (`t_alm_active_alarm`.`alarm_object_type` = '000300001') then '存储设备'
        when (substr(`t_alm_active_alarm`.`alarm_object_type`, 1, 4) = '0004') then '安全设备'
        when (substr(`t_alm_active_alarm`.`alarm_object_type`, 1, 4) = '0014') then '网络'
        when ((`t_alm_active_alarm`.`alarm_object_type` like '%0012%')
        or (`t_alm_active_alarm`.`alarm_object_type` like '%app%')) then '应用'
        else '其它未知类型'
    end) as `alarm_object_type`,
    `t_alm_active_alarm`.`alarm_object_name` as `alarm_object_name`,
    (`t_alm_active_alarm`.`alarm_count` + 0) as `alarm_count`,
    `t_alm_active_alarm`.`severity_id` as `severity_id`,
    `t_alm_active_alarm`.`occur_time` as `occur_time`,
    (case
        when isnull(`t_alm_active_alarm`.`update_time`) then `t_alm_active_alarm`.`insert_time`
        else `t_alm_active_alarm`.`update_time`
    end) as `end_time`,
    `t_alm_active_alarm`.`title` as `title`,
    `t_alm_active_alarm`.`alarm_text` as `alarm_text`,
    `t_alm_active_alarm`.`clr_status` as `clr_status`,
    1 as `is_active_alarm`
from
    `t_alm_active_alarm`

    

	
	




DROP TABLE IF EXISTS `bi_work_flow_group_row`;
CREATE TABLE `bi_work_flow_group_row` (
	`dtype` varchar(5) CHARACTER SET utf8mb3 NOT NULL DEFAULT '',
	`thedvalue` varchar(15) CHARACTER SET utf8mb3 DEFAULT NULL,
	`process_type_name` varchar(255) DEFAULT NULL,
  	`app_id` varchar(64) DEFAULT NULL,
	`app_name` varchar(64) DEFAULT NULL,
	`serve_area` varchar(32) DEFAULT NULL,
	`serve_department` varchar(32) DEFAULT NULL,
	`serve_firm` varchar(32) DEFAULT NULL,
	`agg` varchar(10) DEFAULT NULL,
	`unit` varchar(20) DEFAULT NULL,
	`kind` varchar(40) NOT NULL DEFAULT '',
	`value` double DEFAULT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

	

-- （3）基础设施监控覆盖率趋势

-- 建表语句

DROP TABLE IF EXISTS `bi_hardware_fugai`;
CREATE TABLE `bi_hardware_fugai` (
  `dtype` varchar(5) NOT NULL DEFAULT '' COMMENT '日期类型',
  `thedvalue` varchar(20) DEFAULT NULL COMMENT '日期',
  `itm_hardware_resources_id` varchar(200) DEFAULT '' COMMENT '告警设备ID',
  `itm_hardware_resources_type` varchar(64) DEFAULT NULL COMMENT '设备类型',
  `itm_hardware_resources_cntype` varchar(5) DEFAULT '' COMMENT '设备类型中文',
  `itm_hardware_resources_areacode` varchar(64) DEFAULT NULL COMMENT '告警设备区域',
  `ITM_HARDWARE_RESOURCES_ASSNO` varchar(100) DEFAULT NULL COMMENT '国资编码',
  `ITM_HARDWARE_RESOURCES_CODE` varchar(200) DEFAULT NULL COMMENT '设备编码',
  `ITM_HARDWARE_RESOURCES_NAME` varchar(255) DEFAULT NULL COMMENT '设备名称',
  `ITM_HARDWARE_RESOURCES_IP` varchar(64) DEFAULT NULL COMMENT '设备IP',
  `ITM_HARDWARE_RESOURCES_STATUS` varchar(64) DEFAULT NULL COMMENT '设备状态',
  `itm_monitor_flag` varchar(64) DEFAULT NULL COMMENT '是否进行监控 0未纳入监控 1纳入监控',
  `create_time` datetime DEFAULT NULL COMMENT '创建日期',
  `NAME` varchar(512) DEFAULT NULL COMMENT '所属组织',
  `resclasscnname` varchar(255) DEFAULT NULL COMMENT '所属类型（网系）',
  `UID` varchar(100) DEFAULT NULL COMMENT 'UID 同告警设备ID',
  `pinpai` varchar(255) DEFAULT NULL COMMENT '设备品牌',
  `xinghao` varchar(255) DEFAULT NULL COMMENT '设备型号',
  `mac` varchar(255) DEFAULT NULL COMMENT 'MAC地址',
  `guoziguishurenyuan` varchar(255) DEFAULT NULL COMMENT '国资归属人员',
  `guoziguishubumen` varchar(255) DEFAULT NULL COMMENT '国资归属部门',
  `yunweirenyuan` varchar(255) DEFAULT NULL COMMENT '运维人员',
  `ITM_HARDWARE_RESOURCES_NETNAME` varchar(100) DEFAULT NULL,
  `cloud` varchar(255) DEFAULT NULL COMMENT '云平台'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;





	-- （4）基础设施资源利用率趋势、服务器资源负载趋势、网络设备带宽利用率趋势、网络设备效能分析、磁盘性能分析、存储设备资源利用率趋势

-- 建表语句

DROP TABLE IF EXISTS `bi_app_stat_group_by_time`;
CREATE TABLE `bi_app_stat_group_by_time` (
  `dtype` varchar(5) NOT NULL DEFAULT '' COMMENT '日期类型',
  `thedvalue` varchar(15) DEFAULT NULL COMMENT '日期',
  `dhour` varchar(15) DEFAULT NULL COMMENT '小时',
  `itm_hardware_resources_id` varchar(200) NOT NULL DEFAULT '' COMMENT '告警设备ID',
  `itm_hardware_resources_type` varchar(64) DEFAULT NULL COMMENT '设备类型',
  `itm_hardware_resources_cntype` varchar(5) NOT NULL DEFAULT '' COMMENT '设备类型中文',
  `QGorZG` varchar(10) DEFAULT NULL COMMENT '全国/最高',
  `itm_hardware_resources_areacode` varchar(64) DEFAULT NULL COMMENT '告警设备区域',
  `ITM_HARDWARE_RESOURCES_ASSNO` varchar(100) DEFAULT NULL COMMENT '国资编码',
  `ITM_HARDWARE_RESOURCES_CODE` varchar(200) DEFAULT NULL COMMENT '设备编码',
  `ITM_HARDWARE_RESOURCES_NAME` varchar(255) DEFAULT NULL COMMENT '设备名称',
  `ITM_HARDWARE_RESOURCES_IP` varchar(64) DEFAULT NULL COMMENT '设备IP',
  `ITM_HARDWARE_RESOURCES_STATUS` varchar(64) DEFAULT NULL COMMENT '设备状态',
  `ITM_HARDWARE_RESOURCES_NETNAME` varchar(255) DEFAULT NULL COMMENT '网系名称',
  `itm_monitor_flag` varchar(64) DEFAULT NULL COMMENT '是否进行监控 0未纳入监控 1纳入监控',
  `create_time` datetime DEFAULT NULL COMMENT '设备创建时间',
  `NAME` varchar(512) DEFAULT NULL COMMENT '所属组织',
  `resclasscnname` varchar(255) DEFAULT NULL COMMENT '所属类型（网系）',
  `UID` varchar(100) DEFAULT NULL COMMENT 'UID 同告警设备ID',
  `pinpai` varchar(255) DEFAULT NULL COMMENT '设备品牌',
  `xinghao` varchar(255) DEFAULT NULL COMMENT '设备型号',
  `mac` varchar(255) DEFAULT NULL COMMENT 'MAC地址',
  `guoziguishurenyuan` varchar(255) DEFAULT NULL COMMENT '国资归属人员',
  `guoziguishubumen` varchar(255) DEFAULT NULL COMMENT '国资归属部门',
  `yunweirenyuan` varchar(255) DEFAULT NULL COMMENT '运维人员',
  `IND_VALUE_Network_Cpurate` decimal(20,6) DEFAULT NULL COMMENT 'cpu利用率（原始）',
  `IND_VALUE_Network_Cpurate_High` decimal(16,2) DEFAULT NULL COMMENT 'CPU利用率峰值（原始）',
  `IND_VALUE_Network_Memrate` decimal(20,6) DEFAULT NULL COMMENT '内存利用率（原始）',
  `IND_VALUE_Network_Memrate_High` decimal(16,2) DEFAULT NULL COMMENT '内存利用率峰值（原始）',
  `IND_VALUE_HOST_DISK_TOTAL` decimal(38,4) DEFAULT NULL COMMENT '磁盘总大小（原始）',
  `IND_VALUE_HOST_DISK_USED` decimal(38,4) DEFAULT NULL COMMENT '已使用磁盘大小（原始）',
  `IND_VALUE_HOST_DISK_UTILIZATION` decimal(16,4) DEFAULT NULL COMMENT '磁盘利用率峰值（原始）',
  `Band_widthrate` decimal(38,4) DEFAULT NULL COMMENT '实时带宽利用率',
  `Bandwidthrate_High` decimal(38,4) DEFAULT NULL COMMENT '带宽峰值用率',
  `Network_delay` decimal(38,4) DEFAULT NULL COMMENT '网络时延',
  `Network_flow` decimal(38,4) DEFAULT NULL COMMENT '端口流量',
  `Network_PacketErrorrate` decimal(38,4) DEFAULT NULL COMMENT '丢包率',
  `Network_PacketLossrate` decimal(38,4) DEFAULT NULL COMMENT '错包率',
  `Write_rate` decimal(38,4) DEFAULT NULL COMMENT '读写速率',
  `network_Ifinrate` decimal(38,4) DEFAULT NULL COMMENT '下行利用率',
  `network_Ifortrate` decimal(38,4) DEFAULT NULL COMMENT '上行利用率',
  `Device_total` decimal(38,4) DEFAULT NULL COMMENT '设备总量',
  `Device_used` decimal(38,4) DEFAULT NULL COMMENT '设备已用容量',
  `Device_UTILIZATION` decimal(38,4) DEFAULT NULL COMMENT '资源利用率',
  `cloud` varchar(255) DEFAULT NULL COMMENT '云平台',
  KEY `bi_app_stat_group_by_time_dtype_IDX` (`dtype`,`thedvalue`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `bi_save_tempbgtime`;
CREATE TABLE `bi_save_tempbgtime` (
  `bg_time` varchar(300) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


drop procedure if exists wk_app_stat_group_by_time;
delimiter $$
create procedure wk_app_stat_group_by_time(in bg_time varchar(300), in ed_time varchar(300))    
begin 
	delete from bi_app_stat_group_by_time where dtype = 'HOUR' and thedvalue >= concat(bg_time,'00') and thedvalue < concat(ed_time,'00');
	delete from bi_app_stat_group_by_time where dtype = 'DAY' and thedvalue >= bg_time and thedvalue < ed_time;
	delete from bi_app_stat_group_by_time where dtype = 'MONTH' and thedvalue >= date_format(STR_TO_DATE(bg_time,'%Y%m%d'),'%Y%m') and thedvalue <= date_format(DATE_ADD(STR_TO_DATE(ed_time,'%Y%m%d'), INTERVAL -1 day),'%Y%m');
	delete from bi_app_stat_group_by_time where dtype = 'YEAR' and thedvalue >= date_format(STR_TO_DATE(bg_time,'%Y%m%d'),'%Y') and thedvalue <= date_format(DATE_ADD(STR_TO_DATE(ed_time,'%Y%m%d'), INTERVAL -1 day),'%Y');
	insert into bi_app_stat_group_by_time
	select 
		'HOUR' as dtype,
		date_format(b.IND_VALUE_TIME,'%Y%m%d%H') as thedvalue,
		date_format(b.IND_VALUE_TIME,'%H') as dhour,
		a.itm_hardware_resources_id,a.itm_hardware_resources_type,a.itm_hardware_resources_cntype,case when a.NAME = '最高人民法院' then '最高' else '全国' end QGorZG,a.itm_hardware_resources_areacode,
		a.ITM_HARDWARE_RESOURCES_ASSNO,a.ITM_HARDWARE_RESOURCES_CODE,a.ITM_HARDWARE_RESOURCES_NAME,a.ITM_HARDWARE_RESOURCES_IP,a.ITM_HARDWARE_RESOURCES_STATUS,a.NET_NAME as ITM_HARDWARE_RESOURCES_NETNAME
		,a.itm_monitor_flag,a.create_time,a.NAME,a.resclasscnname,a.UID,a.pinpai,a.xinghao,a.mac
		,a.guoziguishurenyuan,a.guoziguishubumen,a.yunweirenyuan,
		avg(b.IND_VALUE_Network_Cpurate) as IND_VALUE_Network_Cpurate,max(b.IND_VALUE_Network_Cpurate_High) as IND_VALUE_Network_Cpurate_High,
		avg(IND_VALUE_Network_Memrate) as IND_VALUE_Network_Memrate,max(IND_VALUE_Network_Memrate_High) as IND_VALUE_Network_Memrate_High,
		0.0 as IND_VALUE_HOST_DISK_TOTAL, 0.0 as IND_VALUE_HOST_DISK_USED,0.0 as IND_VALUE_HOST_DISK_UTILIZATION,
		avg(IND_VALUE_Network_Bandwidthrate) as Band_widthrate,
		max(IND_VALUE_Network_Bandwidthrate_High) as Bandwidthrate_High,
		0 as Network_delay, 
		avg(TRANSSIZE_Up + TRANSSIZE_Down) as Network_flow,
		avg(IND_VALUE_Network_PacketErrorrate) as Network_PacketErrorrate,
		avg(IND_VALUE_Network_PacketLossrate) as Network_PacketLossrate,
		avg(IND_VALUE_Network_Bandwidthrate) as Write_rate,
		avg(IND_VALUE_Network_Ifinrate) as network_Ifinrate,
		avg(IND_VALUE_Network_Ifoutrate) as network_Ifortrate,
		0.0 as Device_total,
		0.0 as Device_used,
		0.0 as Device_UTILIZATION,
		a.cloud
	from bi_itm_hardware_resources a
	inner join app_network_stat  b
	on a.itm_hardware_resources_id = b.INT_ID
	where b.IND_VALUE_TIME >= date_format(STR_TO_DATE(bg_time,'%Y%m%d'),'%Y-%m-%d') and b.IND_VALUE_TIME < date_format(STR_TO_DATE(ed_time,'%Y%m%d'),'%Y-%m-%d')
	GROUP BY date_format(b.IND_VALUE_TIME,'%Y%m%d%H'),date_format(b.IND_VALUE_TIME,'%H'),
		a.itm_hardware_resources_id,a.itm_hardware_resources_type,a.itm_hardware_resources_cntype,case when a.NAME = '最高人民法院' then '最高' else '全国' end,a.itm_hardware_resources_areacode,
		a.ITM_HARDWARE_RESOURCES_ASSNO,a.ITM_HARDWARE_RESOURCES_CODE,a.ITM_HARDWARE_RESOURCES_NAME,a.ITM_HARDWARE_RESOURCES_IP,a.ITM_HARDWARE_RESOURCES_STATUS,a.NET_NAME
		,a.itm_monitor_flag,a.create_time,a.NAME,a.resclasscnname,a.UID,a.pinpai,a.xinghao,a.mac
		,a.guoziguishurenyuan,a.guoziguishubumen,a.yunweirenyuan,a.cloud
	union all
	select 
		'HOUR' as dtype,
		date_format(b.IND_VALUE_TIME,'%Y%m%d%H') as thedvalue,
		date_format(b.IND_VALUE_TIME,'%H') as dhour,
		a.itm_hardware_resources_id,a.itm_hardware_resources_type,a.itm_hardware_resources_cntype,case when a.NAME = '最高人民法院' then '最高' else '全国' end as QGorZG,a.itm_hardware_resources_areacode,
		a.ITM_HARDWARE_RESOURCES_ASSNO,a.ITM_HARDWARE_RESOURCES_CODE,a.ITM_HARDWARE_RESOURCES_NAME,a.ITM_HARDWARE_RESOURCES_IP,a.ITM_HARDWARE_RESOURCES_STATUS,a.NET_NAME as ITM_HARDWARE_RESOURCES_NETNAME
		,a.itm_monitor_flag,a.create_time,a.NAME,a.resclasscnname,a.UID,a.pinpai,a.xinghao,a.mac
		,a.guoziguishurenyuan,a.guoziguishubumen,a.yunweirenyuan,
		0.0 as IND_VALUE_Network_Cpurate,0.0 as IND_VALUE_Network_Cpurate_High,0.0 as IND_VALUE_Network_Memrate,0.0 as IND_VALUE_Network_Memrate_High,
		sum(b.IND_VALUE_HOST_DISK_TOTAL) as IND_VALUE_HOST_DISK_TOTAL, sum(b.IND_VALUE_HOST_DISK_USED) as IND_VALUE_HOST_DISK_USED,
		max(b.IND_VALUE_HOST_DISK_UTILIZATION) as IND_VALUE_HOST_DISK_UTILIZATION,
		0.0 as Band_widthrate,
		0.0 as Bandwidthrate_High,
		0.0 as Network_delay,
		0.0 as Network_flow,
		0.0 as Network_PacketErrorrate,
		0.0 as Network_PacketLossrate,
		0.0 as Write_rate,
		0.0 as network_Ifinrate,
		0.0 as network_Ifortrate,
		max(IND_VALUE_HOST_DISK_TOTAL) as Device_total,
		max(IND_VALUE_HOST_DISK_USED) as Device_used,
		max(b.IND_VALUE_HOST_DISK_UTILIZATION) as Device_UTILIZATION,
		a.cloud
	from bi_itm_hardware_resources a
	inner join app_host_stat  b
	on a.itm_hardware_resources_id = b.INT_ID
	where b.IND_VALUE_TIME >= date_format(STR_TO_DATE(bg_time,'%Y%m%d'),'%Y-%m-%d') and b.IND_VALUE_TIME < date_format(STR_TO_DATE(ed_time,'%Y%m%d'),'%Y-%m-%d')
	GROUP BY date_format(b.IND_VALUE_TIME,'%Y%m%d%H'),date_format(b.IND_VALUE_TIME,'%H'),
		a.itm_hardware_resources_id,a.itm_hardware_resources_type,a.itm_hardware_resources_cntype,case when a.NAME = '最高人民法院' then '最高' else '全国' end,a.itm_hardware_resources_areacode,
		a.ITM_HARDWARE_RESOURCES_ASSNO,a.ITM_HARDWARE_RESOURCES_CODE,a.ITM_HARDWARE_RESOURCES_NAME,a.ITM_HARDWARE_RESOURCES_IP,a.ITM_HARDWARE_RESOURCES_STATUS,a.NET_NAME
		,a.itm_monitor_flag,a.CREATE_TIME,a.NAME,a.resclasscnname,a.UID,a.pinpai,a.xinghao,a.mac
		,a.guoziguishurenyuan,a.guoziguishubumen,a.yunweirenyuan,a.cloud;
	
	insert into bi_app_stat_group_by_time
	select 
		'DAY' as dtype,
		substr(thedvalue,1,8) as thedvalue,
		dhour ,itm_hardware_resources_id ,itm_hardware_resources_type ,itm_hardware_resources_cntype ,QGorZG ,itm_hardware_resources_areacode ,
		ITM_HARDWARE_RESOURCES_ASSNO ,ITM_HARDWARE_RESOURCES_CODE ,ITM_HARDWARE_RESOURCES_NAME ,ITM_HARDWARE_RESOURCES_IP ,ITM_HARDWARE_RESOURCES_STATUS ,
		ITM_HARDWARE_RESOURCES_NETNAME ,itm_monitor_flag ,create_time ,NAME ,resclasscnname ,UID ,pinpai ,xinghao ,mac ,guoziguishurenyuan ,guoziguishubumen ,yunweirenyuan,
		avg(IND_VALUE_Network_Cpurate) as IND_VALUE_Network_Cpurate,max(IND_VALUE_Network_Cpurate_High) as IND_VALUE_Network_Cpurate_High,
		avg(IND_VALUE_Network_Memrate) as IND_VALUE_Network_Memrate,max(IND_VALUE_Network_Memrate_High) as IND_VALUE_Network_Memrate_High,
		sum(IND_VALUE_HOST_DISK_TOTAL) as IND_VALUE_HOST_DISK_TOTAL, sum(IND_VALUE_HOST_DISK_USED) as IND_VALUE_HOST_DISK_USED,
		max(IND_VALUE_HOST_DISK_UTILIZATION) as IND_VALUE_HOST_DISK_UTILIZATION,
		avg(Band_widthrate) as Band_widthrate,
		max(Bandwidthrate_High) as Bandwidthrate_High,
		0 as Network_delay, 
		0 as Network_flow,
		avg(Network_PacketErrorrate) as Network_PacketErrorrate,
		avg(Network_PacketLossrate) as Network_PacketLossrate,
		0 as Write_rate,
		avg(network_Ifinrate) as network_Ifinrate,
		avg(network_Ifortrate) as network_Ifortrate,
		max(Device_total) as Device_total,
		max(Device_used) as Device_used,
		max(Device_UTILIZATION) as Device_UTILIZATION,
		cloud
	from bi_app_stat_group_by_time
	where dtype = 'HOUR' and thedvalue >= concat(bg_time,'00') and thedvalue < concat(ed_time,'00')
	group by substr(thedvalue,1,8),dhour ,itm_hardware_resources_id ,itm_hardware_resources_type ,itm_hardware_resources_cntype ,QGorZG ,itm_hardware_resources_areacode ,
		ITM_HARDWARE_RESOURCES_ASSNO ,ITM_HARDWARE_RESOURCES_CODE ,ITM_HARDWARE_RESOURCES_NAME ,ITM_HARDWARE_RESOURCES_IP ,ITM_HARDWARE_RESOURCES_STATUS ,
		ITM_HARDWARE_RESOURCES_NETNAME ,itm_monitor_flag ,create_time ,NAME ,resclasscnname ,UID ,pinpai ,xinghao ,mac ,guoziguishurenyuan ,guoziguishubumen ,yunweirenyuan, cloud;
	
	insert into bi_app_stat_group_by_time
	select 
		'MONTH' as dtype,
		substr(thedvalue,1,6) as thedvalue,
		dhour ,itm_hardware_resources_id ,itm_hardware_resources_type ,itm_hardware_resources_cntype ,QGorZG ,itm_hardware_resources_areacode ,
		ITM_HARDWARE_RESOURCES_ASSNO ,ITM_HARDWARE_RESOURCES_CODE ,ITM_HARDWARE_RESOURCES_NAME ,ITM_HARDWARE_RESOURCES_IP ,ITM_HARDWARE_RESOURCES_STATUS ,
		ITM_HARDWARE_RESOURCES_NETNAME ,itm_monitor_flag ,create_time ,NAME ,resclasscnname ,UID ,pinpai ,xinghao ,mac ,guoziguishurenyuan ,guoziguishubumen ,yunweirenyuan,
		avg(IND_VALUE_Network_Cpurate) as IND_VALUE_Network_Cpurate,max(IND_VALUE_Network_Cpurate_High) as IND_VALUE_Network_Cpurate_High,
		avg(IND_VALUE_Network_Memrate) as IND_VALUE_Network_Memrate,max(IND_VALUE_Network_Memrate_High) as IND_VALUE_Network_Memrate_High,
		sum(IND_VALUE_HOST_DISK_TOTAL) as IND_VALUE_HOST_DISK_TOTAL, sum(IND_VALUE_HOST_DISK_USED) as IND_VALUE_HOST_DISK_USED,
		max(IND_VALUE_HOST_DISK_UTILIZATION) as IND_VALUE_HOST_DISK_UTILIZATION,
		avg(Band_widthrate) as Band_widthrate,
		max(Bandwidthrate_High) as Bandwidthrate_High,
		0 as Network_delay, 
		0 as Network_flow,
		avg(Network_PacketErrorrate) as Network_PacketErrorrate,
		avg(Network_PacketLossrate) as Network_PacketLossrate,
		0 as Write_rate,
		avg(network_Ifinrate) as network_Ifinrate,
		avg(network_Ifortrate) as network_Ifortrate,
		max(Device_total) as Device_total,
		max(Device_used) as Device_used,
		max(Device_UTILIZATION) as Device_UTILIZATION,
		cloud
	from bi_app_stat_group_by_time
	where dtype = 'DAY' and thedvalue >= bg_time and thedvalue < ed_time
	group by substr(thedvalue,1,6),dhour ,itm_hardware_resources_id ,itm_hardware_resources_type ,itm_hardware_resources_cntype ,QGorZG ,itm_hardware_resources_areacode ,
		ITM_HARDWARE_RESOURCES_ASSNO ,ITM_HARDWARE_RESOURCES_CODE ,ITM_HARDWARE_RESOURCES_NAME ,ITM_HARDWARE_RESOURCES_IP ,ITM_HARDWARE_RESOURCES_STATUS ,
		ITM_HARDWARE_RESOURCES_NETNAME ,itm_monitor_flag ,create_time ,NAME ,resclasscnname ,UID ,pinpai ,xinghao ,mac ,guoziguishurenyuan ,guoziguishubumen ,yunweirenyuan, cloud;
	
	insert into bi_app_stat_group_by_time
	select 
		'YEAR' as dtype,
		substr(thedvalue,1,4) as thedvalue,
		dhour ,itm_hardware_resources_id ,itm_hardware_resources_type ,itm_hardware_resources_cntype ,QGorZG ,itm_hardware_resources_areacode ,
		ITM_HARDWARE_RESOURCES_ASSNO ,ITM_HARDWARE_RESOURCES_CODE ,ITM_HARDWARE_RESOURCES_NAME ,ITM_HARDWARE_RESOURCES_IP ,ITM_HARDWARE_RESOURCES_STATUS ,
		ITM_HARDWARE_RESOURCES_NETNAME ,itm_monitor_flag ,create_time ,NAME ,resclasscnname ,UID ,pinpai ,xinghao ,mac ,guoziguishurenyuan ,guoziguishubumen ,yunweirenyuan,
		avg(IND_VALUE_Network_Cpurate) as IND_VALUE_Network_Cpurate,max(IND_VALUE_Network_Cpurate_High) as IND_VALUE_Network_Cpurate_High,
		avg(IND_VALUE_Network_Memrate) as IND_VALUE_Network_Memrate,max(IND_VALUE_Network_Memrate_High) as IND_VALUE_Network_Memrate_High,
		sum(IND_VALUE_HOST_DISK_TOTAL) as IND_VALUE_HOST_DISK_TOTAL, sum(IND_VALUE_HOST_DISK_USED) as IND_VALUE_HOST_DISK_USED,
		max(IND_VALUE_HOST_DISK_UTILIZATION) as IND_VALUE_HOST_DISK_UTILIZATION,
		avg(Band_widthrate) as Band_widthrate,
		max(Bandwidthrate_High) as Bandwidthrate_High,
		0 as Network_delay, 
		0 as Network_flow,
		avg(Network_PacketErrorrate) as Network_PacketErrorrate,
		avg(Network_PacketLossrate) as Network_PacketLossrate,
		0 as Write_rate,
		avg(network_Ifinrate) as network_Ifinrate,
		avg(network_Ifortrate) as network_Ifortrate,
		max(Device_total) as Device_total,
		max(Device_used) as Device_used,
		max(Device_UTILIZATION) as Device_UTILIZATION,
		cloud
	from bi_app_stat_group_by_time
	where dtype = 'MONTH' and thedvalue >= date_format(STR_TO_DATE(bg_time,'%Y%m%d'),'%Y%m') and thedvalue <= date_format(DATE_ADD(STR_TO_DATE(ed_time,'%Y%m%d'), INTERVAL -1 day),'%Y%m')
	group by substr(thedvalue,1,4),dhour ,itm_hardware_resources_id ,itm_hardware_resources_type ,itm_hardware_resources_cntype ,QGorZG ,itm_hardware_resources_areacode ,
		ITM_HARDWARE_RESOURCES_ASSNO ,ITM_HARDWARE_RESOURCES_CODE ,ITM_HARDWARE_RESOURCES_NAME ,ITM_HARDWARE_RESOURCES_IP ,ITM_HARDWARE_RESOURCES_STATUS ,
		ITM_HARDWARE_RESOURCES_NETNAME ,itm_monitor_flag ,create_time ,NAME ,resclasscnname ,UID ,pinpai ,xinghao ,mac ,guoziguishurenyuan ,guoziguishubumen ,yunweirenyuan, cloud;
end $$
delimiter ;


-- 循环执行（按天循环运行，并将执行完成的结果插入bi_save_tempbgtime）
drop procedure if exists wk_app_stat_group_by_time_xunhuan;
delimiter $$
create procedure wk_app_stat_group_by_time_xunhuan(in bg_time varchar(300), in ed_time varchar(300))
begin
	declare i varchar(300);
	set i = bg_time;
	while i < ed_time do
		call wk_app_stat_group_by_time(i, date_format(DATE_ADD(STR_TO_DATE(i,'%Y%m%d'), INTERVAL 1 day),'%Y%m%d'));
		insert into bi_save_tempbgtime values(i);
		set i = date_format(DATE_ADD(STR_TO_DATE(i,'%Y%m%d'), INTERVAL 1 day),'%Y%m%d');
	end while;
end $$
delimiter ;


-- 按时间段一次运行（只包含开始时间，不包含结束时间，输入格式为20230801这种格式）


truncate table bi_save_tempbgtime;
call wk_app_stat_group_by_time_xunhuan('20230101','20231230');





	
	
	
	-- （5）四级法院网络中断趋势

-- 建表语句
DROP TABLE IF EXISTS `bi_pub_organ_siji`;
CREATE TABLE `bi_pub_organ_siji` (
  `ID` varchar(32) DEFAULT NULL,
  `CODE` varchar(64) DEFAULT NULL,
  `FYJB1` varchar(512) DEFAULT NULL,
  `FYJB2` varchar(512) DEFAULT NULL,
  `FYJB3` varchar(512) DEFAULT NULL,
  `FYJB4` varchar(512) DEFAULT NULL,
  `FYJB1_CODE` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `FYJB2_CODE` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `FYJB3_CODE` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `FYJB4_CODE` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `organ_type` varchar(6) NOT NULL DEFAULT '',
  `COURT_NUM` varchar(255) DEFAULT NULL,
  `COURT_TYPE` varchar(36) DEFAULT NULL,
  `SHORT_NAME` varchar(255) DEFAULT NULL,
  `STATUS` char(1) DEFAULT NULL,
  `PUB_LONGITUDE` varchar(255) DEFAULT NULL,
  `PUB_LATITUDE` varchar(255) DEFAULT NULL,
  `PUB_ORGAN_IP` varchar(100) DEFAULT NULL,
  `FYJB1_JC` varchar(512) DEFAULT NULL,
  `FYJB2_JC` varchar(512) DEFAULT NULL,
  `FYJB3_JC` varchar(512) DEFAULT NULL,
  `FYJB4_JC` varchar(512) DEFAULT NULL,
  KEY `index_1` (`CODE`) USING BTREE,
  KEY `index_2` (`FYJB2`) USING BTREE,
  KEY `index_3` (`FYJB3`) USING BTREE,
  KEY `index_4` (`FYJB4`) USING BTREE,
  KEY `index_5` (`FYJB2_JC`) USING BTREE,
  KEY `index_6` (`FYJB3_JC`) USING BTREE,
  KEY `index_7` (`FYJB4_JC`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

	

-- 建表语句

DROP TABLE IF EXISTS `bi_sijifayuan_alm_group_by_time`;
CREATE TABLE `bi_sijifayuan_alm_group_by_time` (
  `dtype` varchar(5) NOT NULL DEFAULT '' COMMENT '日期类型',
  `thedvalue` varchar(15) DEFAULT NULL COMMENT '日期',
  `dhour` varchar(7) DEFAULT NULL COMMENT '日期',
  `alarm_object_id` varchar(64) DEFAULT NULL COMMENT '告警ID（网络CODE）',
  `alarm_object_ip` varchar(300) DEFAULT NULL COMMENT '告警IP（网络IP）',
  `alarm_object_type` varchar(6) NOT NULL DEFAULT '' COMMENT '告警对象类型',
  `severity_type` varchar(2) NOT NULL DEFAULT '' COMMENT '告警级别',
  `title` varchar(256) DEFAULT NULL COMMENT '告警标题',
  `alarm_text` mediumtext COMMENT '告警标题',
  `is_active_alarm` bigint(20) NOT NULL DEFAULT '0' COMMENT '告警正文',
  `FYJB1` varchar(512) DEFAULT NULL COMMENT '1级法院名称',
  `FYJB2` varchar(512) DEFAULT NULL COMMENT '2级法院名称',
  `FYJB3` varchar(512) DEFAULT NULL COMMENT '3级法院名称',
  `FYJB4` varchar(512) DEFAULT NULL COMMENT '4级法院名称',
  `FYJB1_CODE` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '1级法院CODE',
  `FYJB2_CODE` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '2级法院CODE',
  `FYJB3_CODE` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '3级法院CODE',
  `FYJB4_CODE` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '4级法院CODE',
  `organ_type` varchar(6) DEFAULT NULL COMMENT '法院级别',
  `COURT_NUM` varchar(255) DEFAULT NULL,
  `COURT_TYPE` varchar(36) DEFAULT NULL,
  `SHORT_NAME` varchar(255) DEFAULT NULL,
  `STATUS` char(1) DEFAULT NULL,
  `PUB_LONGITUDE` varchar(255) DEFAULT NULL,
  `PUB_LATITUDE` varchar(255) DEFAULT NULL,
  `PUB_ORGAN_IP` varchar(100) DEFAULT NULL,
  `alarm_count` double DEFAULT NULL COMMENT '告警次数',
  `alarm_time` decimal(65,5) DEFAULT NULL COMMENT '告警时长',
  `alarm_zy_num` decimal(42,0) DEFAULT NULL COMMENT '中断次数（故障次数）',
  `total_zy_num` decimal(24,4) DEFAULT NULL COMMENT '总资产数（取平均）',
  `pingall` double(17,0) DEFAULT NULL COMMENT 'ping次数',
  `pingdown` double(17,0) DEFAULT NULL COMMENT 'ping不通次数'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



	
	-- (6) 虚拟化资源分配趋势、虚拟化资源使用趋势
	
-- 建表语句

DROP TABLE IF EXISTS `bi_host_stat`;
CREATE TABLE `bi_host_stat` (
	`dtype` VARCHAR ( 10 ) NOT NULL DEFAULT '',
	`thedvalue` VARCHAR ( 10 ) DEFAULT NULL,
	`COMPANY` VARCHAR ( 30 ) DEFAULT NULL COMMENT '数据来源',
	`target` VARCHAR ( 20 ) NOT NULL DEFAULT '',
	`dim` VARCHAR ( 20 ) NOT NULL DEFAULT '',
	`dim_value` DECIMAL ( 50, 20 ) DEFAULT NULL,
	`group_agg` VARCHAR ( 10 ) NOT NULL DEFAULT '',
	`unit` VARCHAR ( 10 ) NOT NULL DEFAULT '单位' ,
	 `cloud` varchar(255) DEFAULT NULL COMMENT '云平台'
) ENGINE = InnoDB DEFAULT CHARSET = utf8;




	-- （7）设备回收趋势

-- 建表语句
DROP TABLE IF EXISTS `bi_recycle_trend`;
CREATE TABLE `bi_recycle_trend` (
  `dtype` varchar(5) NOT NULL DEFAULT '' COMMENT '日期类型',
  `thedvalue` varchar(15) DEFAULT NULL COMMENT '日期',
  `itm_hardware_resources_cntype` varchar(15) DEFAULT NULL COMMENT '设备类型',
  `NET_NAME` varchar(50) DEFAULT NULL COMMENT '网系',
  `total` decimal(38,4) DEFAULT NULL COMMENT '设备回收数',
  `total_recycled` decimal(38,4) DEFAULT NULL COMMENT '已回收数',
  `total_no_recycled` decimal(38,4) DEFAULT NULL COMMENT '未回收数'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- https://blog.csdn.net/helloxiaozhe/article/details/78570016
set sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';


	
	
	
	-- （8）应用系统监控覆盖率趋势
	
-- 建表语句
DROP TABLE IF EXISTS bi_app_fugai;
CREATE TABLE `bi_app_fugai` (
	`dtype` varchar(5) NOT NULL DEFAULT '' COMMENT '日期类型',
	`thedvalue` varchar(15) DEFAULT NULL COMMENT '日期',
	`ITM_APP_ID` varchar(255) NOT NULL,
	`ITM_APP_CODE` varchar(45) DEFAULT NULL COMMENT '编码',
	`ITM_APP_NAME` varchar(200) DEFAULT NULL COMMENT '应用名称',
	`ITM_APP_SHORTNAME` varchar(100) DEFAULT NULL COMMENT '应用简称',
	`ITM_APP_DESC` text COMMENT '系统简介',
	`ITM_APP_DEPT` varchar(300) DEFAULT NULL COMMENT '归口部门',
	`ITM_APP_COURTCODE` varchar(45) DEFAULT NULL COMMENT '承建单位\n例如：最高法、山东省高法\n取法院系统的组织机构编码',
	`ITM_NETGROUP_ID` varchar(30) DEFAULT NULL COMMENT '应用所属网系\n更新字典表\nNG000：外部专网\nNG001：法院专网\nNG002：互联网\nNG003：移动专网\nNG004：涉密内网',
	`ITM_APPCATAGORY_ID` varchar(45) DEFAULT NULL,
	`ITM_APP_VENDOR` varchar(45) DEFAULT NULL COMMENT '供应商：浪潮、太极等',
	`ITM_APP_UPTIME` datetime DEFAULT NULL COMMENT '上线时间',
	`ITM_APP_VERSION` varchar(45) DEFAULT NULL,
	`ITM_APP_MAINTENANCE_ID` varchar(45) DEFAULT NULL COMMENT '运维团队,关联运维表，使用内码连接\n',
	`ITM_APP_ORDER` int(45) DEFAULT NULL COMMENT '应用顺序，展示',
	`ITM_APP_NETID` varchar(45) DEFAULT NULL COMMENT '专网编号',
	`ITM_APP_ICONSRC` varchar(100) DEFAULT NULL COMMENT '应用分类\n三类和25类字典表表现',
	`ITM_APP_SYSTEMURL` varchar(300) DEFAULT NULL,
	`ORGAN_ID` varchar(30) DEFAULT NULL COMMENT '机构id，关联机构表',
	`IS_GLOBAL` varchar(45) DEFAULT NULL COMMENT '是否是全局\n如果是全局，则拓扑图是全局的\n如果是非全局，需要维护各自的',
	`ITM_APP_PID` varchar(45) DEFAULT NULL COMMENT '父类系统\n',
	`PLATFORM` varchar(100) DEFAULT NULL COMMENT '七大平台',
	`PRIORITY` varchar(10) DEFAULT NULL COMMENT '优先级:核心-high;重要-important;一般-low',
	`ITM_APP_IP` varchar(30) DEFAULT NULL COMMENT '应用所在服务器IP',
	`ITM_APP_PORT` varchar(10) DEFAULT NULL COMMENT '应用所在服务器端口',
	`ITM_APP_WEBROOT` varchar(100) DEFAULT NULL COMMENT '应用访问路径根目录',
	`ITM_LOCATION` varchar(100) DEFAULT NULL COMMENT '位置信息',
	`ITM_APP_ASSURE_LEVEL` varchar(1) DEFAULT NULL COMMENT '应用保障级别1一级保障',
	`SHOW_TYPE` varchar(10) DEFAULT NULL,
	`ITM_SHOW_FLAG` varchar(10) DEFAULT NULL,
	`int_id` int(22) DEFAULT NULL COMMENT '其他系统关联用ID',
	`INSERT_TIME` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '记录插入时间',
	`mantance_vendor` varchar(64) DEFAULT NULL,
	`mantance_duty` varchar(64) DEFAULT NULL,
	`mantance_duty_phone` varchar(128) DEFAULT NULL,
	`start_date` datetime DEFAULT NULL,
	`mantance_year_number` int(16) DEFAULT NULL,
	`ne_type` varchar(64) DEFAULT NULL,
	`creator` varchar(64) DEFAULT NULL,
	`create_time` datetime DEFAULT NULL,
	`modifier` varchar(64) DEFAULT NULL,
	`modify_time` datetime DEFAULT NULL,
	`time_stamp` datetime DEFAULT NULL,
	`stateflag` mediumtext,
	`ruuid` varchar(128) NOT NULL,
	`dataType` varchar(50) DEFAULT NULL,
	`login_action` text,
	`localize_flag` varchar(32) DEFAULT NULL COMMENT '是否国产化，1为国产化，0为非国产化',
	`current_stat` varchar(32) DEFAULT NULL,
	`contract` varchar(64) DEFAULT NULL,
	`emergency_flag` varchar(32) DEFAULT NULL,
	`monitor_flag` varchar(32) DEFAULT NULL,
	`development_language` varchar(32) DEFAULT NULL,
	`app_type` varchar(64) DEFAULT NULL,
	`contract_money` varchar(32) DEFAULT NULL,
	`unified_login` varchar(12) DEFAULT NULL COMMENT '统一登录:0:以接入统一桌面;1:未接入统一桌面',
	`stateflag1` varchar(20) DEFAULT NULL COMMENT '应用状态',
	`PRIORITY1` varchar(20) DEFAULT NULL COMMENT '重要程度',
	`app_type_name` varchar(20) DEFAULT NULL COMMENT '分类',
	`net_name` varchar(20) DEFAULT NULL COMMENT '网系',
	`cloud_flag` varchar(20) DEFAULT NULL COMMENT '应用对应资源是否上云1为上云，0为不上云',
	`organ_name` varchar(200) DEFAULT NULL COMMENT '区域',
	`itm_hardware_resources_areacode` varchar(100) DEFAULT NULL COMMENT '设备区域',
	`on_line` varchar(255) DEFAULT NULL,
  	`cloud` varchar(255) DEFAULT NULL COMMENT '云平台'
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
	
	




	-- （9）基础设施上线趋势

-- 建表语句

DROP TABLE IF EXISTS `bi_online_trend`;
CREATE TABLE `bi_online_trend` (
  `dtype` varchar(5) NOT NULL DEFAULT '' COMMENT '日期类型',
  `thedvalue` varchar(15) DEFAULT NULL COMMENT '日期',
  `itm_hardware_resources_areacode` varchar(100) DEFAULT NULL COMMENT '区域/机房',
  `itm_hardware_resources_cntype` varchar(15) DEFAULT NULL COMMENT '设备类型',
  `itm_hardware_resources_name` varchar(100) DEFAULT NULL COMMENT '设备名称',
  `NET_NAME` varchar(50) DEFAULT NULL COMMENT '网系',
  `total` decimal(38,4) DEFAULT NULL COMMENT '上线总量',
  `ended_total` decimal(38, 4) DEFAULT NULL COMMENT '退网总量',
  `ending_total` decimal(38, 4) DEFAULT NULL COMMENT '退网中总量'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

set sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

	-- （10）操作次数趋势、响应时间趋势、应用系统用户趋势

-- 建表语句


DROP TABLE IF EXISTS `bi_app_log_result`;
CREATE TABLE `bi_app_log_result` (
  `dtype` varchar(5) NOT NULL DEFAULT '' COMMENT '日期类型',
  `thedvalue` varchar(15) DEFAULT NULL COMMENT '日期',
  `dhour` varchar(15) DEFAULT NULL COMMENT '小时',
  `PRIORITY` varchar(20) DEFAULT NULL COMMENT '重要程度',
  `app_type_name` varchar(50) DEFAULT NULL COMMENT '分类',
  `net_name` varchar(50) DEFAULT NULL COMMENT '网系',
  `id` bigint(20) NOT NULL DEFAULT '0' COMMENT '自增标记，用于数据同步',
  `app_id` varchar(200) DEFAULT NULL COMMENT '应用ID',
  `app_name` varchar(200) DEFAULT NULL COMMENT '应用名称',
  `orgin_id` varchar(32) DEFAULT NULL COMMENT '组织ID',
  `net_id` varchar(30) DEFAULT NULL,
  `independent_visits_num` varchar(10) DEFAULT NULL COMMENT '独立访客数/活跃用户数',
  `visits_num` varchar(10) DEFAULT NULL COMMENT '访客数',
  `oper_num` varchar(10) DEFAULT NULL COMMENT '操作次数/访问量',
  `slow_oper` varchar(10) DEFAULT NULL COMMENT '慢操作占比',
  `min_resp_time` varchar(10) DEFAULT NULL COMMENT '最小响应时间(毫秒)',
  `max_resp_time` varchar(10) DEFAULT NULL COMMENT '最大响应时间(毫秒)',
  `avg_resp_time` varchar(10) DEFAULT NULL COMMENT '平均响应时间(毫秒）',
  `time_stamp` varchar(32) DEFAULT NULL COMMENT '时间戳',
  `monitor_time` varchar(32) DEFAULT NULL COMMENT '推送时间',
  `territory_visitor` varchar(32) DEFAULT NULL COMMENT '地域访问数',
  `reg_user_num` varchar(32) DEFAULT NULL COMMENT '注册用户数',
  `sign_num` varbinary(32) DEFAULT NULL COMMENT '登录次数',
  `new_reg_user_num` varchar(32) DEFAULT NULL COMMENT '新注册用户数',
  `time_type` varchar(32) DEFAULT NULL COMMENT '类型（hour：小时 day：天 month：月 国内：domestic  国外：foreign）',
  `create_time` varchar(32) DEFAULT NULL COMMENT '插入时间',
  `organ_name` varchar(200) DEFAULT NULL COMMENT '区域'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


	


	-- （16）应用漏洞变化趋势

-- 建表语句

DROP TABLE IF EXISTS `bi_panoramic_view_app_ld_trend`;
CREATE TABLE `bi_panoramic_view_app_ld_trend` (
  `dtype` varchar(5) NOT NULL DEFAULT '' COMMENT '日期类型',
  `thedvalue` varchar(8) DEFAULT NULL COMMENT '日期类型',
  `app_name` varchar(128) DEFAULT NULL COMMENT '应用名称',
  `score_count` varchar(53) DEFAULT NULL COMMENT '指标值',
  `NET_NAME` varchar(30) DEFAULT NULL COMMENT '网系名称',
  `score_name` varchar(25) DEFAULT NULL COMMENT '覆盖率,合格率'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


	
DROP TABLE IF EXISTS `bi_panoramic_new_trend`;
CREATE TABLE `bi_panoramic_new_trend` (
  `dtype` varchar(10) CHARACTER SET utf8mb4 NOT NULL DEFAULT '' COMMENT '日期类型',
  `thedvalue` varchar(20) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '日期',
  `app_name` varchar(50) DEFAULT NULL COMMENT '设备资产名称',
  `score_name` varchar(50) DEFAULT NULL COMMENT '选择对象',
  `the_score` double DEFAULT NULL  COMMENT  '网系名称',
	`NET_NAME`  varchar(100) DEFAULT NULL COMMENT  '网系名称',
	`organ_name`  varchar(100) DEFAULT NULL COMMENT  '区域'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;




	-- （20）综合安全评分趋势

-- 建表语句

DROP TABLE IF EXISTS `bi_panoramic_view_safe_trend`;
CREATE TABLE `bi_panoramic_view_safe_trend` (
  `dtype` varchar(10) CHARACTER SET utf8mb4 NOT NULL DEFAULT '' COMMENT '日期类型',
  `thedvalue` varchar(20) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '日期',
  `id` varchar(50) NOT NULL DEFAULT '0' COMMENT '设备名称暂时没有数据',
  `score_name` varchar(50) CHARACTER SET utf8mb4 NOT NULL DEFAULT '' COMMENT '指标名称',
  `the_score` double DEFAULT NULL COMMENT '指标值'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

	
	-- （21）网络攻击变化趋势

-- 建表语句

DROP TABLE IF EXISTS `bi_panoramic_view_net_attack_trend`;
CREATE TABLE `bi_panoramic_view_net_attack_trend` (
  `dtype` varchar(10) CHARACTER SET utf8mb4 NOT NULL DEFAULT '' COMMENT '日期类型',
  `thedvalue` varchar(20) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '日期',
  `score_name` varchar(50) CHARACTER SET utf8mb4 NOT NULL DEFAULT '' COMMENT '指标名称',
  `the_score` double DEFAULT NULL COMMENT '指标值'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

	

	-- （22）设备漏洞变化趋势
-- 建表语句


DROP TABLE IF EXISTS `bi_panoramic_view_sb_ld_trend`;
CREATE TABLE `bi_panoramic_view_sb_ld_trend` (
  `dtype` varchar(10) CHARACTER SET utf8mb4 NOT NULL DEFAULT '' COMMENT '日期类型',
  `thedvalue` varchar(20) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '日期',
  `app_name` varchar(50) NOT NULL DEFAULT '0' COMMENT '设备名称暂时没有数据',
  `score_name` varchar(50) CHARACTER SET utf8mb4 NOT NULL DEFAULT '',
  `the_score` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


	

	-- （23）设备准入变化趋势、双因子认证变化趋势、纳入4A变化趋势

-- 建表语句

DROP TABLE IF EXISTS `bi_panoramic_view_shaung_yin_a4_trend`;
CREATE TABLE `bi_panoramic_view_shaung_yin_a4_trend` (
  `dtype` varchar(10) CHARACTER SET utf8mb4 NOT NULL DEFAULT '' COMMENT '日期类型',
  `thedvalue` varchar(20) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '日期',
  `id` varchar(50) NOT NULL DEFAULT '0' COMMENT '设备名称暂时没有数据',
  `score_name` varchar(50) CHARACTER SET utf8mb4 NOT NULL DEFAULT '' COMMENT '指标名称',
  `the_score` double DEFAULT NULL COMMENT '指标值'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

	

	


-- （24）运维工单总量趋势、工单按时解决率趋势、各类工单未按时解决排名、工单满意度趋势、工单处理耗时排名、工单平均处理时长

-- 建表语句

DROP TABLE IF EXISTS `bi_work_flow_group_by_time`;
CREATE TABLE `bi_work_flow_group_by_time` (
  `dtype` varchar(5) NOT NULL DEFAULT '' COMMENT '日期类型',
  `thedvalue` varchar(15) DEFAULT NULL COMMENT '日期',
  `process_type_name` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '流程定义类型名称',
  `serve_area` varchar(32) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '所属区域',
  `serve_department` varchar(32) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '所属部门',
  `serve_firm` varchar(32) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '所属厂商',
  `total_works` bigint(21) NOT NULL DEFAULT '0' COMMENT '工单总量',
  `total_works_end` decimal(23,0) DEFAULT NULL COMMENT '已解决总量',
  `total_works_noend` decimal(23,0) DEFAULT NULL COMMENT '未解决总量',
  `total_works_notimeout` decimal(23,0) DEFAULT NULL COMMENT '未超时总量',
  `total_works_timeout` decimal(23,0) DEFAULT NULL COMMENT '超时总量',
  `total_time_consuming` double DEFAULT NULL COMMENT '总耗时',
  `avg_time_consuming` double DEFAULT NULL COMMENT '平均耗时',
  `satisfaction` double DEFAULT NULL COMMENT '满意度',
  `app_id` varchar(20) DEFAULT NULL COMMENT 'appID'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


	-- （26）最高法院效能分析排序
-- 建表语句

DROP TABLE IF EXISTS `bi_app_stat_group_transposed`;
CREATE TABLE `bi_app_stat_group_transposed` (
  `thedvalue` varchar(20) DEFAULT NULL COMMENT '日期',
  `itm_hardware_resources_id` varchar(200) NOT NULL DEFAULT '' COMMENT '告警设备ID',
  `ITM_HARDWARE_RESOURCES_NAME` varchar(255) DEFAULT NULL COMMENT '设备名称',
  `col_name` varchar(200) CHARACTER SET utf8mb4 NOT NULL DEFAULT '',
  `col_value` decimal(24,10) DEFAULT NULL,
  KEY `idx_1` (`thedvalue`,`col_name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;




	-- （27）应用系统业务指标

-- 建表语句

DROP TABLE IF EXISTS `bi_app_tsc_stat`;
CREATE TABLE `bi_app_tsc_stat` (
  `dtype` varchar(10) NOT NULL DEFAULT '' COMMENT '日期类型',
  `thedvalue` varchar(12) DEFAULT NULL COMMENT '日期',
  `app_id` varchar(20) NOT NULL DEFAULT '' COMMENT 'appID',
  `app_name` varchar(255) NOT NULL DEFAULT '' COMMENT 'app名称',
  `dim` varchar(255) NOT NULL DEFAULT '' COMMENT '维度',
  `dim_tuli` varchar(255) DEFAULT NULL COMMENT '图例',
  `dim_value` decimal(20,2) DEFAULT NULL COMMENT '指标值',
  `dim_value_unit` varchar(255) DEFAULT NULL COMMENT '指标单位',
  `value_calculate` varchar(255) DEFAULT NULL COMMENT '指标计算方式',
  `dim_tuli_value` decimal(20,2) DEFAULT NULL COMMENT '图例值'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

drop procedure if exists wk_app_new;
delimiter $$
create procedure wk_app_new()
begin 
	declare i int default 0;
	declare continue_handler int default 0;
	declare c_app_id varchar(200);
	declare c_app_name varchar(200);
	declare c_fieldname varchar(64);
	declare c_fielddesc varchar(64);
	declare c_statmodel varchar(255);
	declare c_quotaUnit varchar(10);
	declare c1 cursor for
	select 
		distinct a.APP_ID,a.APP_NAME,b.fieldname,b.fielddesc,b.statmodel,b.quotaUnit 
	from 
		(select distinct ITM_APP_ID as APP_ID,ITM_APP_NAME as APP_NAME from itm_app) a 
	left join 
		(select a.appid,a.fieldname,a.fielddesc,a.statmodel,a.quotaUnit
		from itm_app_tsc_set a where transtype = '62' and showType = 'line') b
	on a.APP_ID = b.appid
	where (fieldname is not null) and (statmodel is not null and statmodel != '');
	declare c2 cursor for
	select 
		distinct a.APP_ID,a.APP_NAME,b.fieldname,b.fielddesc,b.statmodel,b.quotaUnit 
	from 
		(select distinct ITM_APP_ID as APP_ID,ITM_APP_NAME as APP_NAME from itm_app) a 
	left join 
		(select distinct a.appid,a.fieldname,b.fielddesc,a.statmodel,a.quotaUnit
	from 
	(select appid,fieldname,fielddesc,statmodel,quotaUnit,showType from itm_app_tsc_set where transtype = '62') a
	left join 
	(select appid,fieldname,fielddesc,statmodel,quotaUnit,showType from itm_app_tsc_set where transtype = '62') b
	on a.appid = b.appid and a.fieldname = b.fieldname
	where a.showType = 'bar' and b.showType = 'line') b
	on a.APP_ID = b.appid
	where (fieldname is not null) and (statmodel is not null and statmodel != '');
	declare continue handler for not found set i=1;
	declare continue handler for sqlexception,sqlwarning set continue_handler=1;
	open c1;
	while i = 0 do
		fetch c1 into c_app_id,c_app_name,c_fieldname,c_fielddesc,c_statmodel,c_quotaUnit;
		set @need_sql=concat("
		insert into bi_app_tsc_stat
		select \'MONTH\' as dtype,date_format(TRANSED_DT,\'%Y%m\') as thedvalue,\'',c_app_id,'\' as app_id,\'',c_app_name,'\' as app_name,\'',c_fielddesc,'\' as dim,\'',c_fielddesc,'\' as dim_tuli,'
		,c_statmodel,'(',c_fieldname,') as dim_value,\'',c_quotaUnit,'\' as dim_value_unit,\'',c_statmodel,'\' as value_calculate,null as dim_tuli_value
		from t_ind_obj_app_tsc_stat where APP_ID = \'',c_app_id, '\' and TRANS_TYPE = \'62\' and (CEXT0 is null or CEXT0 = \'\') 
		group by date_format(TRANSED_DT,\'%Y%m\')");
		PREPARE final_sql FROM @need_sql;
		EXECUTE final_sql;
-- 	if continue_handler = 1 THEN
-- 		insert into bi_app_tsc_default values(c_app_id,c_app_name,c_fieldname,c_fielddesc,c_statmodel,c_quotaUnit);
-- 	end if;
	end while;
	close c1;
	set i = 0;
	open c2;
	while i = 0 do
		fetch c2 into c_app_id,c_app_name,c_fieldname,c_fielddesc,c_statmodel,c_quotaUnit;
		set @need_sql=concat("
		insert into bi_app_tsc_stat
		select \'MONTH\' as dtype,date_format(TRANSED_DT,\'%Y%m\') as thedvalue,\'',c_app_id,'\' as app_id,\'',c_app_name,'\' as app_name,\'',c_fielddesc,'\' as dim,CEXT0 as dim_tuli,
		null as dim_value,\'',c_quotaUnit,'\' as dim_value_unit,\'',c_statmodel,'\' as value_calculate,',c_statmodel,'(',c_fieldname,') as dim_value
		from t_ind_obj_app_tsc_stat where APP_ID = \'',c_app_id, '\' and TRANS_TYPE = \'62\' and CEXT0 is not null and cext0 != \'\'
		group by date_format(TRANSED_DT,\'%Y%m\'), CEXT0");
		PREPARE final_sql FROM @need_sql;
		EXECUTE final_sql;
-- 	if continue_handler = 1 THEN
-- 		insert into bi_app_tsc_default values(c_app_id,c_app_name,c_fieldname,c_fielddesc,c_statmodel,c_quotaUnit);
-- 	end if;
	end while;
	close c2;
end $$
delimiter ;

-- truncate table bi_app_tsc_default;
truncate table bi_app_tsc_stat;
call wk_app_new();




-- （28） 工单报表

-- 建表语句

DROP TABLE IF EXISTS `bi_workflow`;
CREATE TABLE `bi_workflow` (
  `before_handler` varchar(255) DEFAULT NULL,
  `a_id` varchar(255) NOT NULL COMMENT '主键id',
  `theme` varchar(255) DEFAULT NULL COMMENT '工单主题',
  `proposer_phone` varchar(255) DEFAULT NULL COMMENT '申请人联系方式',
  `proposer_name` varchar(255) DEFAULT NULL COMMENT '申请人姓名',
  `proposer` varchar(255) DEFAULT NULL COMMENT '申请人id',
  `serial_number` varchar(255) DEFAULT NULL COMMENT '工单流水号',
  `process_key` varchar(255) DEFAULT NULL COMMENT '流程定义的key',
  `serve_type` varchar(255) DEFAULT NULL COMMENT '服务类型',
  `process_id` varchar(255) DEFAULT NULL COMMENT '流程定义id',
  `parent_process_type_name` varchar(255) DEFAULT NULL COMMENT '流程定义类型名称',
  `process_type` varchar(255) DEFAULT NULL COMMENT '流程定义类型名称',
  `setting_data` longtext COMMENT '流程定义设置信息json格式数据',
  `process_type_name` varchar(64) DEFAULT NULL,
  `parent_process_type` varchar(64) DEFAULT NULL,
  `form_key` varchar(255) DEFAULT NULL,
  `form_data` longtext COMMENT '对应的自定义表单json格式数据',
  `urg_degree` varchar(255) DEFAULT NULL COMMENT '工单紧急程度',
  `start_account` varchar(255) DEFAULT NULL COMMENT '发起人',
  `start_account_id` varchar(255) DEFAULT NULL COMMENT '发起人id',
  `start_account_phone` varchar(32) DEFAULT NULL,
  `expect_time` varchar(255) DEFAULT NULL COMMENT '期望完成时间',
  `end_time` varchar(255) DEFAULT NULL COMMENT '完成时间',
  `start_time` varchar(255) DEFAULT NULL COMMENT '开始时间',
  `process_status` varchar(255) DEFAULT NULL COMMENT '流程状态',
  `handler` longtext COMMENT '待处理人',
  `next_handler` varchar(255) DEFAULT NULL COMMENT '下一环节处理人',
  `attention` varchar(64) DEFAULT NULL COMMENT '关注状态',
  `node_id` varchar(64) DEFAULT NULL COMMENT '流程节点id',
  `press` tinyint(4) DEFAULT '0' COMMENT '催办状态 0表示未催办 1表示催办',
  `opinion` varchar(255) DEFAULT NULL COMMENT '审批意见',
  `result` int(11) DEFAULT NULL COMMENT '审批结果',
  `form_id` varchar(255) DEFAULT NULL COMMENT '自定义表单id',
  `proc_def_id` varchar(255) DEFAULT NULL COMMENT '流程部署id',
  `relevance` varchar(64) DEFAULT NULL COMMENT '关联状态',
  `handler_name` varchar(255) DEFAULT NULL COMMENT '待处理人姓名',
  `next_handler_name` varchar(255) DEFAULT NULL COMMENT '下一节点处理人姓名',
  `node_type` varchar(255) DEFAULT NULL COMMENT '节点类型',
  `update_time` varchar(255) DEFAULT NULL COMMENT '更新时间',
  `appraise` tinyint(4) DEFAULT NULL COMMENT '工单是否已评价 0已评价 1未评价',
  `failure_type` varchar(255) DEFAULT NULL,
  `satisfaction` varchar(32) DEFAULT NULL COMMENT '满意度',
  `serve_department` varchar(32) DEFAULT NULL COMMENT '发起人所属部门',
  `serve_firm` varchar(32) DEFAULT NULL COMMENT '发起人所属厂商',
  `serve_area` varchar(32) DEFAULT NULL COMMENT '发起人所属区域',
  `time_consuming` varchar(32) DEFAULT NULL COMMENT '工单耗时',
  `time_out` varchar(32) DEFAULT NULL COMMENT '是否超时 0超时 1未超时',
  `performance` varchar(255) DEFAULT NULL COMMENT '绩效得分',
  `proposer_serve_department` varchar(255) DEFAULT NULL COMMENT '申请人所属部门',
  `proposer_serve_firm` varchar(255) DEFAULT NULL COMMENT '申请人所属厂商',
  `proposer_serve_area` varchar(255) DEFAULT NULL COMMENT '申请人所属区域',
  `start_user` varchar(255) DEFAULT NULL,
  `order_desc` varchar(255) DEFAULT NULL COMMENT '工单描述',
  `cocall` varchar(255) DEFAULT NULL,
  `handler_firm` longtext,
  `b_id` int(11) DEFAULT '0',
  `work_id` varchar(32) DEFAULT NULL COMMENT '工单id',
  `alarm_uuid` varchar(255) DEFAULT NULL COMMENT '告警uuid',
  `alarm_type` varchar(32) DEFAULT NULL COMMENT '告警类型',
  `object_id` varchar(255) DEFAULT NULL,
  `app_id` varchar(64) DEFAULT NULL,
  `c` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT '设备名称'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



	-- 结构化数据容量分析

-- 建表语句

DROP TABLE IF EXISTS `bi_structured_data_capacity`;
CREATE TABLE `bi_structured_data_capacity` (
	`dtype` varchar(5) NOT NULL DEFAULT '' COMMENT '日期类型',
	`thedvalue` varchar(20) DEFAULT NULL COMMENT '日期',
	`NET_ID` varchar(64) DEFAULT NULL COMMENT '网系ID',
	`NET_NAME` varchar(64) DEFAULT NULL COMMENT '网系名称',
	`ORGAN_ID` varchar(100) DEFAULT NULL COMMENT '区域ID',
	`ORGAN_NAME` varchar(100) DEFAULT NULL COMMENT '区域名称',
	`totalsize` varchar(255) DEFAULT NULL COMMENT '当前数据库对应数量存储量 单位是GB'
) ENGINE = InnoDB DEFAULT CHARSET = utf8;



	-- 存储使用率占比分析

-- 建表语句

DROP TABLE IF EXISTS `bi_storage_usage_rate`;
CREATE TABLE `bi_storage_usage_rate` (
	`dtype` varchar(5) NOT NULL DEFAULT '' COMMENT '日期类型',
	`thedvalue` varchar(20) DEFAULT NULL COMMENT '日期',
	`NET_ID` varchar(64) DEFAULT NULL COMMENT '网系ID',
	`NET_NAME` varchar(64) DEFAULT NULL COMMENT '网系名称',
	`ORGAN_ID` varchar(100) DEFAULT NULL COMMENT '区域ID',
	`ORGAN_NAME` varchar(100) DEFAULT NULL COMMENT '区域名称',
	`used` decimal(20,10) DEFAULT NULL COMMENT '存储使用量',
	`total` decimal(20,10) DEFAULT NULL COMMENT '存储总量',
	`usage_rate` decimal(20,10) DEFAULT NULL COMMENT '存储使用率'
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

	
	-- 应用数据分析

-- 建表语句

DROP TABLE IF EXISTS `bi_app_data_analysis`;
CREATE TABLE `bi_app_data_analysis` (
	`dtype` varchar(5) NOT NULL DEFAULT '' COMMENT '日期类型',
	`thedvalue` varchar(20) DEFAULT NULL COMMENT '日期',
	`NET_ID` varchar(64) DEFAULT NULL COMMENT '网系ID',
	`NET_NAME` varchar(64) DEFAULT NULL COMMENT '网系名称',
	`ORGAN_ID` varchar(100) DEFAULT NULL COMMENT '区域ID',
	`ORGAN_NAME` varchar(100) DEFAULT NULL COMMENT '区域名称',
	`app_id` varchar(64) DEFAULT NULL COMMENT '应用ID',
	`app_name` varchar(255) DEFAULT NULL COMMENT '应用名称',
	`priority` varchar(10) DEFAULT NULL COMMENT '优先级:核心-high;重要-important;一般-low',
	`visit_count` decimal(20,2) DEFAULT NULL COMMENT '访问量',
	`user_count` decimal(20,2) DEFAULT NULL COMMENT '用户量',
	`quality` decimal(20,6) DEFAULT NULL COMMENT '数据质量'
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

	




	-- 应用备份覆盖率分析

-- 建表语句
DROP TABLE IF EXISTS `bi_app_backup_coverage`;
CREATE TABLE `bi_app_backup_coverage` (
	`dtype` varchar(5) NOT NULL DEFAULT '' COMMENT '日期类型',
	`thedvalue` varchar(20) DEFAULT NULL COMMENT '日期',
	`NET_ID` varchar(64) DEFAULT NULL COMMENT '网系ID',
	`NET_NAME` varchar(64) DEFAULT NULL COMMENT '网系名称',
	`ORGAN_ID` varchar(100) DEFAULT NULL COMMENT '区域ID',
	`ORGAN_NAME` varchar(100) DEFAULT NULL COMMENT '区域名称',
	`app_id` varchar(64) DEFAULT NULL COMMENT '应用ID',
	`app_name` varchar(255) DEFAULT NULL COMMENT '应用名称',
	`priority` varchar(10) DEFAULT NULL COMMENT '优先级:核心-high;重要-important;一般-low',
	`bak_size` decimal(20,10) DEFAULT NULL COMMENT '备份数据容量',
	`total_size` decimal(20,10) DEFAULT NULL COMMENT '总容量',
	`backup_coverage` decimal(20,10) DEFAULT NULL COMMENT '备份覆盖率'
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

	

	
	
	
set sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
DROP TABLE IF EXISTS `bi_app_resource_group_by_time`;
CREATE TABLE `bi_app_resource_group_by_time` (
   `dtype` varchar(5) NOT NULL DEFAULT '' COMMENT '日期类型',
   `thedvalue` varchar(15) DEFAULT NULL COMMENT '日期',
    `dhour` varchar(15) DEFAULT NULL comment '小时',
    `ITM_APP_ID` varchar(255) NOT NULL COMMENT '应用ID',
    `ITM_APP_CODE` varchar(45) DEFAULT NULL COMMENT '编码',
    `ITM_APP_NAME` varchar(200) DEFAULT NULL COMMENT '应用名称',
    `ITM_APP_SHORTNAME` varchar(100) DEFAULT NULL COMMENT '应用简称',
    `ITM_APP_DESC` text COMMENT '系统简介',
    `ITM_APP_DEPT` varchar(300) DEFAULT NULL COMMENT '归口部门',
    `ITM_APP_COURTCODE` varchar(45) DEFAULT NULL COMMENT '承建单位\n例如：最高法、山东省高法\n取法院系统的组织机构编码',
    `ITM_NETGROUP_ID` varchar(30) DEFAULT NULL COMMENT '应用所属网系\n更新字典表\nNG000：外部专网\nNG001：法院专网\nNG002：互联网\nNG003：移动专网\nNG004：涉密内网',
    `ITM_APPCATAGORY_ID` varchar(45) DEFAULT NULL,
    `ITM_APP_VENDOR` varchar(45) DEFAULT NULL COMMENT '供应商：浪潮、太极等',
    `ITM_APP_UPTIME` datetime DEFAULT NULL COMMENT '上线时间',
    `ITM_APP_VERSION` varchar(45) DEFAULT NULL,
    `ITM_APP_MAINTENANCE_ID` varchar(45) DEFAULT NULL COMMENT '运维团队,关联运维表，使用内码连接\n',
    `ITM_APP_ORDER` int(45) DEFAULT NULL COMMENT '应用顺序，展示',
    `ITM_APP_NETID` varchar(45) DEFAULT NULL COMMENT '专网编号',
    `ITM_APP_ICONSRC` varchar(100) DEFAULT NULL COMMENT '应用分类\n三类和25类字典表表现',
	`ITM_APP_SYSTEMURL` varchar(100) DEFAULT NULL COMMENT '',
    `ORGAN_ID` varchar(30) DEFAULT NULL COMMENT '机构id，关联机构表',
    `IS_GLOBAL` varchar(45) DEFAULT NULL COMMENT '是否是全局\n如果是全局，则拓扑图是全局的\n如果是非全局，需要维护各自的',
    `ITM_APP_PID` varchar(45) DEFAULT NULL COMMENT '父类系统\n',
    `PLATFORM` varchar(100) DEFAULT NULL COMMENT '七大平台',
    `PRIORITY` varchar(10) DEFAULT NULL COMMENT '优先级:核心-high,重要-important,一般-low',
    `ITM_APP_IP` varchar(30) DEFAULT NULL COMMENT '应用所在服务器IP',
    `ITM_APP_PORT` varchar(10) DEFAULT NULL COMMENT '应用所在服务器端口',
    `ITM_APP_WEBROOT` varchar(100) DEFAULT NULL COMMENT '应用访问路径根目录',
    `ITM_LOCATION` varchar(100) DEFAULT NULL COMMENT '位置信息',
    `ITM_APP_ASSURE_LEVEL` varchar(1) DEFAULT NULL COMMENT '应用保障级别1一级保障',
    `SHOW_TYPE` varchar(10) DEFAULT NULL,
    `ITM_SHOW_FLAG` varchar(10) DEFAULT NULL,
    `int_id` int(22) DEFAULT NULL COMMENT '其他系统关联用ID',
    `INSERT_TIME` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '记录插入时间',
    `mantance_vendor` varchar(64) DEFAULT NULL,
    `mantance_duty` varchar(64) DEFAULT NULL,
    `mantance_duty_phone` varchar(128) DEFAULT NULL,
    `start_date` datetime DEFAULT NULL,
    `mantance_year_number` int(16) DEFAULT NULL,
    `ne_type` varchar(64) DEFAULT NULL,
    `creator` varchar(64) DEFAULT NULL,
    `create_time` datetime DEFAULT NULL,
    `modifier` varchar(64) DEFAULT NULL,
    `modify_time` datetime DEFAULT NULL,
    `time_stamp` datetime DEFAULT NULL,
	`stateflag` mediumtext,		
    `ruuid` varchar(128) NOT NULL, 
	`data_type` varchar(50) DEFAULT NULL,
    `login_action` text,
    `localize_flag` varchar(32) DEFAULT NULL,
    `current_stat` varchar(32) DEFAULT NULL,
    `contract` varchar(64) DEFAULT NULL,
    `emergency_flag` varchar(32) DEFAULT NULL,
    `monitor_flag` varchar(32) DEFAULT NULL,
    `development_language` varchar(32) DEFAULT NULL,
    `app_type` varchar(64) DEFAULT NULL,
    `contract_money` varchar(32) DEFAULT NULL,
    `unified_login` varchar(12) DEFAULT NULL COMMENT '统一登录:0:以接入统一桌面,1:未接入统一桌面',
	`img_url` varchar(50) DEFAULT NULL,
    `dataType` text,
    `on_line` mediumtext,
    `stateflag1` varchar(20) DEFAULT NULL COMMENT '应用状态',
    `PRIORITY1` varchar(20) DEFAULT NULL COMMENT '重要程度',
    `app_type_name` varchar(20) DEFAULT NULL COMMENT '分类',
    `net_name` varchar(20) DEFAULT NULL COMMENT '网系',
    `IND_VALUE_Network_Cpurate` decimal(20 ,6) DEFAULT NULL comment 'cpu利用率（原始）',
    `IND_VALUE_Network_Cpurate_High` decimal(16 ,2) DEFAULT NULL comment 'CPU利用率峰值（原始）',
    `IND_VALUE_Network_Memrate` decimal(20 ,6) DEFAULT NULL comment '内存利用率（原始）',
    `IND_VALUE_Network_Memrate_High` decimal(16 ,2) DEFAULT NULL comment '内存利用率峰值（原始）',
    `IND_VALUE_HOST_DISK_TOTAL` decimal(38 ,4) DEFAULT NULL comment '磁盘总大小（原始）',
    `IND_VALUE_HOST_DISK_USED` decimal(38 ,4) DEFAULT NULL comment '已使用磁盘大小（原始）',
    `IND_VALUE_HOST_DISK_UTILIZATION` decimal(16 ,4) DEFAULT NULL comment '磁盘利用率峰值（原始）',
    `Band_widthrate` decimal(38,4) DEFAULT NULL COMMENT '实时带宽利用率',
    `Bandwidthrate_High` decimal(38,4) DEFAULT NULL COMMENT '带宽峰值用率',
    `Network_delay` decimal(38,4) DEFAULT NULL COMMENT '网络时延',
    `Network_flow` decimal(38,4) DEFAULT NULL COMMENT '端口流量',
    `Network_PacketErrorrate` decimal(38,4) DEFAULT NULL COMMENT '丢包率',
    `Network_PacketLossrate` decimal(38,4) DEFAULT NULL COMMENT '错包率',
    `Write_rate` decimal(38,4) DEFAULT NULL COMMENT '读写速率',
    `network_Ifinrate` decimal(38,4) DEFAULT NULL COMMENT '下行利用率',
    `network_Ifortrate` decimal(38,4) DEFAULT NULL COMMENT '上行利用率',
    `Device_total` decimal(38,4) DEFAULT NULL COMMENT '设备总量',
    `Device_used` decimal(38,4) DEFAULT NULL COMMENT '设备已用容量',
    `Device_UTILIZATION` decimal(38,4) DEFAULT NULL COMMENT '资源利用率'
)ENGINE = InnoDB DEFAULT CHARSET = utf8;




DROP TABLE IF EXISTS `bi_app_access_stat`;
CREATE TABLE `bi_app_access_stat` (
	`dtype` varchar(5) NOT NULL DEFAULT '' COMMENT '日期类型',
	`thedvalue` varchar(20) DEFAULT NULL COMMENT '日期',
	`NET_ID` varchar(64) DEFAULT NULL COMMENT '网系ID',
	`NET_NAME` varchar(64) DEFAULT NULL COMMENT '网系名称',
	`ORGAN_ID` varchar(100) DEFAULT NULL COMMENT '区域ID/数据来源',
	`ORGAN_NAME` varchar(100) DEFAULT NULL COMMENT '区域名称',
	`app_id` varchar(64) DEFAULT NULL COMMENT '应用ID',
	`app_name` varchar(255) DEFAULT NULL COMMENT '应用名称',
	`visit_count` decimal(20,10) DEFAULT NULL COMMENT '访问数',
	`operate_count` decimal(20,10) DEFAULT NULL COMMENT '操作量',
	 `priority` varchar(10) DEFAULT NULL COMMENT '应用等级:核心-high;重要-important;一般-low',
	  `app_type` varchar(255) DEFAULT NULL COMMENT '应用分类'
) ENGINE = InnoDB DEFAULT CHARSET = utf8;