langchao_fayuan/all.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;


truncate table bi_itm_hardware_resources;
insert into bi_itm_hardware_resources
SELECT
	IzGoY.itm_hardware_resources_id,
	IzGoY.itm_hardware_resources_type,
	case 
	when IzGoY.itm_hardware_resources_type = '000100001' then '物理机'
	when IzGoY.itm_hardware_resources_type = '000100002' then '云主机' 
	when IzGoY.itm_hardware_resources_type = '000100004' then '宿主机' 
	when substr(IzGoY.itm_hardware_resources_type,1,4) = '0001' then '其他主机' 
	when IzGoY.itm_hardware_resources_type = '000200001' then '交换机' 
	when IzGoY.itm_hardware_resources_type = '000200002' then '路由器' 
	when IzGoY.itm_hardware_resources_type = '000300001' then '存储设备' 
	when substr(IzGoY.itm_hardware_resources_type,1,4) = '0004' then '安全设备'
	else '非资产设备' end as itm_hardware_resources_cntype,
	case when IzGoY.itm_hardware_resources_areacode is null then '无区域' else IzGoY.itm_hardware_resources_areacode end as itm_hardware_resources_areacode,
	IzGoY.ITM_HARDWARE_RESOURCES_ASSNO,
	IzGoY.ITM_HARDWARE_RESOURCES_CODE,
	IzGoY.ITM_HARDWARE_RESOURCES_NAME,
	IzGoY.ITM_HARDWARE_RESOURCES_IP,
	IzGoY.ITM_HARDWARE_RESOURCES_STATUS,
	case when IzGoY.itm_monitor_flag = '1' then '监控' else '未监控' end as itm_monitor_flag,
	case when IzGoY.create_time is null then IzGoY.update_time else IzGoY.create_time end as create_time,
	XVuSA. NAME,
-- 	dHSPj.resclasscnname,
	dHSPj.ITM_HARDWARE_RESOURCES_TYPE_NAME as resclasscnname,
	AIiKi.ITM_APPCATAGORY_NAME as NET_NAME,
	case when thd.ITM_HARDWARE_RESOURCES_NAME is null then '无机房' else thd.ITM_HARDWARE_RESOURCES_NAME end as ROOM_JIFANG,
	Qfqdl.UID,
	Qfqdl.pinpai,
	Qfqdl.xinghao,
	Qfqdl.mac,
	Qfqdl.guoziguishurenyuan,
	Qfqdl.guoziguishubumen,
	case when IzGoY.itm_hardware_resources_type = '000100002' then Qfqdl.business_director else Qfqdl.yunweirenyuan end as yunweirenyuan,
	Qfqdl.servicename,
	Qfqdl.relatedappid,
	case when IzGoY.itm_hardware_resources_type = '000100002' then Qfqdl.contractor else Qfqdl.mantancevendor end as mantancevendor,
	IzGoY.cloud
FROM
	itm_hardware_resources IzGoY
LEFT JOIN
(SELECT
	ITM_HARDWARE_RESOURCES_ID AS UID,
	max((CASE WHEN (ITM_HARDWARE_RESOURCES_PROCODE = 'brand_name') THEN ITM_HARDWARE_RESOURCES_VALUE ELSE '' END)) AS pinpai,
	max((CASE WHEN (ITM_HARDWARE_RESOURCES_PROCODE = 'model') THEN ITM_HARDWARE_RESOURCES_VALUE ELSE '' END)) AS xinghao,
	max((CASE WHEN (ITM_HARDWARE_RESOURCES_PROCODE = 'macaddress') THEN ITM_HARDWARE_RESOURCES_VALUE ELSE '' END)) AS mac,
	max((CASE WHEN (ITM_HARDWARE_RESOURCES_PROCODE = 'manager') THEN ITM_HARDWARE_RESOURCES_VALUE ELSE '' END)) AS guoziguishurenyuan,
	max((CASE WHEN (ITM_HARDWARE_RESOURCES_PROCODE = 'department') THEN ITM_HARDWARE_RESOURCES_VALUE ELSE '' END)) AS guoziguishubumen,
	max((CASE WHEN (ITM_HARDWARE_RESOURCES_PROCODE = 'mantance_duty') THEN ITM_HARDWARE_RESOURCES_VALUE ELSE '' END)) AS yunweirenyuan,
	max((CASE WHEN (ITM_HARDWARE_RESOURCES_PROCODE = 'service_name') THEN ITM_HARDWARE_RESOURCES_VALUE ELSE '' END)) AS servicename,
	max((CASE WHEN (ITM_HARDWARE_RESOURCES_PROCODE = 'related_app_id') THEN ITM_HARDWARE_RESOURCES_VALUE ELSE '' END)) AS relatedappid,
	max((CASE WHEN (ITM_HARDWARE_RESOURCES_PROCODE = 'mantance_vendor') THEN ITM_HARDWARE_RESOURCES_VALUE ELSE '' END)) AS mantancevendor,
	max((CASE WHEN (ITM_HARDWARE_RESOURCES_PROCODE = 'business_director') THEN ITM_HARDWARE_RESOURCES_VALUE ELSE '' END)) AS business_director,
	max((CASE WHEN (ITM_HARDWARE_RESOURCES_PROCODE = 'contractor') THEN ITM_HARDWARE_RESOURCES_VALUE ELSE '' END)) AS contractor
FROM
	itm_hardware_resources_value
GROUP BY
	ITM_HARDWARE_RESOURCES_ID
) Qfqdl ON IzGoY.ITM_HARDWARE_RESOURCES_ID = Qfqdl.UID
LEFT JOIN pub_organ XVuSA ON IzGoY.ORGAN_ID = XVuSA. CODE
LEFT JOIN itm_appcatagory AIiKi ON IzGoY.ITM_HARDWARE_RESOURCES_NETCODE = AIiKi.ITM_APPCATAGORY_CODE
-- LEFT JOIN (select distinct case when substr(resclassenname,1,2) = 'P_' then substr(resclassenname,3) else resclassenname end as resclassenname,resclasscnname from m_resclass) dHSPj ON IzGoY.ne_type = dHSPj.resclassenname
left join (select ITM_HARDWARE_RESOURCES_TYPE,ITM_HARDWARE_RESOURCES_TYPE_NAME from itm_hardware_resources_type) dHSPj on IzGoY.ITM_HARDWARE_RESOURCES_TYPE = dHSPj.ITM_HARDWARE_RESOURCES_TYPE
left join itm_hardware_resources thd
on IzGoY.ITM_HARDWARE_RESOURCES_ROOMCODE = thd.itm_hardware_resources_id where IzGoY.ITM_HARDWARE_RESOURCES_PTYPE IN ('0001','0002','000300001','0004')
AND IzGoY.ITM_HARDWARE_RESOURCES_STATUS != '报废'
and IzGoY.stateflag='0';




	-- （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`
union all
select
    `t_alm_history_alarm`.`alarm_object_ip` as `alarm_object_ip`,
    `t_alm_history_alarm`.`alarm_object_id` as `alarm_object_id`,
    `t_alm_history_alarm`.`alarm_object_type` as `alarm_object_codetype`,
    (case
        when (`t_alm_history_alarm`.`alarm_object_type` = '000100001') then '物理机'
        when (`t_alm_history_alarm`.`alarm_object_type` = '000100002') then '云主机'
        when (`t_alm_history_alarm`.`alarm_object_type` = '000100004') then '宿主机'
        when (substr(`t_alm_history_alarm`.`alarm_object_type`, 1, 4) = '0001') then '其他主机'
        when (`t_alm_history_alarm`.`alarm_object_type` = '000200001') then '交换机'
        when (`t_alm_history_alarm`.`alarm_object_type` = '000200002') then '路由器'
        when (`t_alm_history_alarm`.`alarm_object_type` = '000300001') then '存储设备'
        when (substr(`t_alm_history_alarm`.`alarm_object_type`, 1, 4) = '0004') then '安全设备'
        when (substr(`t_alm_history_alarm`.`alarm_object_type`, 1, 4) = '0014') then '网络'
        when ((`t_alm_history_alarm`.`alarm_object_type` LIKE '%0012%')
        or (`t_alm_history_alarm`.`alarm_object_type` like '%app%')) then '应用'
        else '其它未知类型'
    end) as `alarm_object_type`,
    `t_alm_history_alarm`.`alarm_object_name` as `alarm_object_name`,
    (`t_alm_history_alarm`.`alarm_count` + 0) as `alarm_count`,
    `t_alm_history_alarm`.`severity_id` as `severity_id`,
    `t_alm_history_alarm`.`occur_time` as `occur_time`,
    (case
        when isnull(`t_alm_history_alarm`.`clear_time`) then `t_alm_history_alarm`.`insert_time`
        else `t_alm_history_alarm`.`clear_time`
    end) as `end_time`,
    `t_alm_history_alarm`.`title` as `title`,
    `t_alm_history_alarm`.`alarm_text` as `alarm_text`,
    `t_alm_history_alarm`.`clr_status` as `clr_status`,
    0 as `is_active_alarm`
from
    `t_alm_history_alarm`;
    

	truncate table bi_alm_group_by_time;
	insert into bi_alm_group_by_time 
	select 
		'HOUR' AS dtype,
		DATE_FORMAT(occur_time,'%Y%m%d%H') as thedvalue,
		alarm_object_ip,alarm_object_type,ITM_HARDWARE_RESOURCES_AREACODE,ITM_HARDWARE_RESOURCES_ASSNO,ITM_HARDWARE_RESOURCES_CODE,
		ITM_HARDWARE_RESOURCES_NAME,ITM_HARDWARE_RESOURCES_IP,pinpai,xinghao,ITM_HARDWARE_RESOURCES_NETNAME,ITM_HARDWARE_RESOURCES_STATUS,severity_type,title,alarm_text,guoziguishubumen,yunweirenyuan,
		sum(alarm_count) as alarm_count,
		sum(alarm_time) as alarm_time,count(1) as alarm_zy_num,
		(select count(1) from bi_itm_hardware_resources where itm_hardware_resources_cntype != '非资产设备') as total_zy_num,
		process_type_name, app_id,m.cloud
	from (
	select 
		a.occur_time,
		a.alarm_object_id,
		a.alarm_object_ip,
		a.alarm_object_type,
		b.ITM_HARDWARE_RESOURCES_AREACODE,
		b.ITM_HARDWARE_RESOURCES_ASSNO,
		b.ITM_HARDWARE_RESOURCES_CODE,
		b.ITM_HARDWARE_RESOURCES_NAME,
		b.ITM_HARDWARE_RESOURCES_IP,
		b.pinpai,
		b.xinghao,b.NET_NAME as ITM_HARDWARE_RESOURCES_NETNAME,b.ITM_HARDWARE_RESOURCES_STATUS,
		case a.severity_id when 1 then '致命' when 2 then '严重' when 3 then '一般' else '未知' end as severity_type,
		a.title,
		a.alarm_text,
		b.guoziguishubumen,
		b.yunweirenyuan,
		a.is_active_alarm,
		a.alarm_count,
		timestampdiff(MINUTE,a.occur_time,a.end_time)*1.0/60 as alarm_time,
		wf.process_type_name,wrr.app_id,b.cloud
	from (select * from v_bi_alm_all_alarm where alarm_object_type not in ('网络','应用')) a
	left join bi_itm_hardware_resources b on a.alarm_object_id = b.itm_hardware_resources_id 
	LEFT JOIN icm_bpm.t_work_resource_real wrr on wrr.object_id = a.alarm_object_id
	left join icm_bpm.t_work_flow wf on wf.id=wrr.work_id) m
	group by 
		DATE_FORMAT(occur_time,'%Y%m%d%H'),
		alarm_object_ip,alarm_object_type,ITM_HARDWARE_RESOURCES_AREACODE,ITM_HARDWARE_RESOURCES_ASSNO,ITM_HARDWARE_RESOURCES_CODE,
		ITM_HARDWARE_RESOURCES_NAME,ITM_HARDWARE_RESOURCES_IP,pinpai,xinghao,ITM_HARDWARE_RESOURCES_NETNAME,ITM_HARDWARE_RESOURCES_STATUS,severity_type,title,alarm_text,guoziguishubumen,yunweirenyuan,process_type_name,app_id,m.cloud;
		
insert into bi_alm_group_by_time
	select 
		'DAY' AS dtype,
		substr(thedvalue,1,8) as thedvalue,
		alarm_object_ip,alarm_object_type,ITM_HARDWARE_RESOURCES_AREACODE,ITM_HARDWARE_RESOURCES_ASSNO,ITM_HARDWARE_RESOURCES_CODE,
		ITM_HARDWARE_RESOURCES_NAME,ITM_HARDWARE_RESOURCES_IP,pinpai,xinghao,ITM_HARDWARE_RESOURCES_NETNAME,ITM_HARDWARE_RESOURCES_STATUS,severity_type,title,alarm_text,guoziguishubumen,yunweirenyuan,
		sum(alarm_count) as alarm_count,sum(alarm_time) as alarm_time,sum(alarm_zy_num) as alarm_zy_num,max(total_zy_num) as total_zy_num, process_type_name, app_id, cloud
	from bi_alm_group_by_time where dtype = 'HOUR' and alarm_object_type not in ('网络','应用')
	group by 
	substr(thedvalue,1,8),
	alarm_object_ip,alarm_object_type,ITM_HARDWARE_RESOURCES_AREACODE,ITM_HARDWARE_RESOURCES_ASSNO,ITM_HARDWARE_RESOURCES_CODE,
	ITM_HARDWARE_RESOURCES_NAME,ITM_HARDWARE_RESOURCES_IP,pinpai,xinghao,ITM_HARDWARE_RESOURCES_NETNAME,ITM_HARDWARE_RESOURCES_STATUS,severity_type,title,alarm_text,guoziguishubumen,yunweirenyuan,process_type_name,app_id,cloud;
	
	insert into bi_alm_group_by_time
		select 
		'MONTH' AS dtype,
		substr(thedvalue,1,6) as thedvalue,
		alarm_object_ip,alarm_object_type,ITM_HARDWARE_RESOURCES_AREACODE,ITM_HARDWARE_RESOURCES_ASSNO,ITM_HARDWARE_RESOURCES_CODE,
		ITM_HARDWARE_RESOURCES_NAME,ITM_HARDWARE_RESOURCES_IP,pinpai,xinghao,ITM_HARDWARE_RESOURCES_NETNAME,ITM_HARDWARE_RESOURCES_STATUS,severity_type,title,alarm_text,guoziguishubumen,yunweirenyuan,
		sum(alarm_count) as alarm_count,sum(alarm_time) as alarm_time,sum(alarm_zy_num) as alarm_zy_num,max(total_zy_num) as total_zy_num,process_type_name, app_id,cloud
	from bi_alm_group_by_time  where dtype = 'DAY' and alarm_object_type not in ('网络','应用')
	group by 
	substr(thedvalue,1,6),
	alarm_object_ip,alarm_object_type,ITM_HARDWARE_RESOURCES_AREACODE,ITM_HARDWARE_RESOURCES_ASSNO,ITM_HARDWARE_RESOURCES_CODE,
	ITM_HARDWARE_RESOURCES_NAME,ITM_HARDWARE_RESOURCES_IP,pinpai,xinghao,ITM_HARDWARE_RESOURCES_NETNAME,ITM_HARDWARE_RESOURCES_STATUS,severity_type,title,alarm_text,guoziguishubumen,yunweirenyuan,process_type_name,app_id,cloud;
	
	insert into bi_alm_group_by_time
	select 
		'YEAR' AS dtype,
		substr(thedvalue,1,4) as thedvalue,
		alarm_object_ip,alarm_object_type,ITM_HARDWARE_RESOURCES_AREACODE,ITM_HARDWARE_RESOURCES_ASSNO,ITM_HARDWARE_RESOURCES_CODE,
		ITM_HARDWARE_RESOURCES_NAME,ITM_HARDWARE_RESOURCES_IP,pinpai,xinghao,ITM_HARDWARE_RESOURCES_NETNAME,ITM_HARDWARE_RESOURCES_STATUS,severity_type,title,alarm_text,guoziguishubumen,yunweirenyuan,
		sum(alarm_count) as alarm_count,sum(alarm_time) as alarm_time,sum(alarm_zy_num) as alarm_zy_num,max(total_zy_num) as total_zy_num,process_type_name,app_id,cloud
	from bi_alm_group_by_time 
	where dtype = 'MONTH' and alarm_object_type not in ('网络','应用')
	group by 
	substr(thedvalue,1,4),
	alarm_object_ip,alarm_object_type,ITM_HARDWARE_RESOURCES_AREACODE,ITM_HARDWARE_RESOURCES_ASSNO,ITM_HARDWARE_RESOURCES_CODE,
	ITM_HARDWARE_RESOURCES_NAME,ITM_HARDWARE_RESOURCES_IP,pinpai,xinghao,ITM_HARDWARE_RESOURCES_NETNAME,ITM_HARDWARE_RESOURCES_STATUS,severity_type,title,alarm_text,guoziguishubumen,yunweirenyuan,process_type_name,app_id,cloud;


	insert into bi_alm_group_by_time (dtype,thedvalue,itm_hardware_resources_name,itm_hardware_resources_ip,itm_hardware_resources_areacode,
	pinpai,itm_hardware_resources_status,severity_type,title,alarm_text,guoziguishubumen,yunweirenyuan,xinghao,alarm_object_type,alarm_count,alarm_time,alarm_zy_num,total_zy_num,process_type_name,app_id
	)
	select 
		'HOUR' AS dtype,
		DATE_FORMAT(occur_time,'%Y%m%d%H') as thedvalue,
	  	m.itm_app_name as itm_hardware_resources_name,
		m.itm_app_systemurl as itm_hardware_resources_ip,
		m.net_name as itm_hardware_resources_areacode,
		m.priority as  pinpai,
		m.stateflag as itm_hardware_resources_status,
		m.severity_type as severity_type,
		m.title as title,
		m.alarm_text as alarm_text,
		m.mantancevendor as guoziguishubumen,
		m.yunweirenyuan as yunweirenyuan,
		m.app_type_name as xinghao,
		'应用' as alarm_object_type,
		sum(alarm_count) as alarm_count,
		sum(alarm_time) as alarm_time,count(1) as alarm_zy_num,
		(select count(1) from itm_app) as total_zy_num,process_type_name,app_id
	from (
	select 
		a.occur_time,
		a.alarm_object_id,
		case a.severity_id when 1 then '致命' when 2 then '严重' when 3 then '一般' else '未知' end as severity_type,
		a.title,
		a.alarm_text,
		a.is_active_alarm,
		a.alarm_count,
		timestampdiff(MINUTE,a.occur_time,a.end_time)*1.0/60 as alarm_time,
		b.itm_app_name as itm_app_name,
		b.itm_app_systemurl as itm_app_systemurl,
		case b.stateflag when 0 then '已应用' else '未应用' end as stateflag,
		case b.PRIORITY WHEN 'low' then '一般应用' when 'important' then '重要应用' when 'high' then '核心应用' else '未知' end as PRIORITY,
		d.name as app_type_name, 
		e.name as net_name,
		'' as mantancevendor,
		b.mantance_duty as yunweirenyuan,
		wf.process_type_name,wrr.app_id
	from (select * from v_bi_alm_all_alarm where alarm_object_type ='应用') a
	left join itm_app b
	on a.alarm_object_id = b.ITM_APP_ID 
	LEFT JOIN icm_bpm.t_work_resource_real wrr on wrr.object_id = a.alarm_object_id
	left join icm_bpm.t_work_flow wf on wf.id=wrr.work_id
	LEFT JOIN m_com_dict d on b.app_type=d.value
	LEFT JOIN m_com_dict e on e.value = b.ITM_NETGROUP_ID 
	WHERE d.dict_index='app_type' and e.dict_index='itm_resources_netcode'
	) m
	group by 
		DATE_FORMAT(occur_time,'%Y%m%d%H'),
		pinpai,m.itm_app_name,m.itm_app_systemurl,m.net_name,m.priority,m.stateflag,m.severity_type,m.title,m.alarm_text,m.mantancevendor,m.yunweirenyuan,m.app_type_name,process_type_name,app_id;
	
	insert into bi_alm_group_by_time
	select 
		'DAY' AS dtype,
		substr(thedvalue,1,8) as thedvalue,
		alarm_object_ip,alarm_object_type,ITM_HARDWARE_RESOURCES_AREACODE,ITM_HARDWARE_RESOURCES_ASSNO,ITM_HARDWARE_RESOURCES_CODE,
		ITM_HARDWARE_RESOURCES_NAME,ITM_HARDWARE_RESOURCES_IP,pinpai,xinghao,ITM_HARDWARE_RESOURCES_NETNAME,ITM_HARDWARE_RESOURCES_STATUS,severity_type,title,alarm_text,guoziguishubumen,yunweirenyuan,
		sum(alarm_count) as alarm_count,sum(alarm_time) as alarm_time,sum(alarm_zy_num) as alarm_zy_num,max(total_zy_num) as total_zy_num, process_type_name, app_id,cloud
	from bi_alm_group_by_time where dtype = 'HOUR' and alarm_object_type = '应用'
	group by 
	substr(thedvalue,1,8),
	alarm_object_ip,alarm_object_type,ITM_HARDWARE_RESOURCES_AREACODE,ITM_HARDWARE_RESOURCES_ASSNO,ITM_HARDWARE_RESOURCES_CODE,
	ITM_HARDWARE_RESOURCES_NAME,ITM_HARDWARE_RESOURCES_IP,pinpai,xinghao,ITM_HARDWARE_RESOURCES_NETNAME,ITM_HARDWARE_RESOURCES_STATUS,severity_type,title,alarm_text,guoziguishubumen,yunweirenyuan,process_type_name,app_id,cloud;
	
	insert into bi_alm_group_by_time
	select 
		'MONTH' AS dtype,
		substr(thedvalue,1,6) as thedvalue,
		alarm_object_ip,alarm_object_type,ITM_HARDWARE_RESOURCES_AREACODE,ITM_HARDWARE_RESOURCES_ASSNO,ITM_HARDWARE_RESOURCES_CODE,
		ITM_HARDWARE_RESOURCES_NAME,ITM_HARDWARE_RESOURCES_IP,pinpai,xinghao,ITM_HARDWARE_RESOURCES_NETNAME,ITM_HARDWARE_RESOURCES_STATUS,severity_type,title,alarm_text,guoziguishubumen,yunweirenyuan,
		sum(alarm_count) as alarm_count,sum(alarm_time) as alarm_time,sum(alarm_zy_num) as alarm_zy_num,max(total_zy_num) as total_zy_num, process_type_name, app_id,cloud
	from bi_alm_group_by_time  where dtype = 'DAY' and alarm_object_type = '应用'
	group by 
	substr(thedvalue,1,6),
	alarm_object_ip,alarm_object_type,ITM_HARDWARE_RESOURCES_AREACODE,ITM_HARDWARE_RESOURCES_ASSNO,ITM_HARDWARE_RESOURCES_CODE,
	ITM_HARDWARE_RESOURCES_NAME,ITM_HARDWARE_RESOURCES_IP,pinpai,xinghao,ITM_HARDWARE_RESOURCES_NETNAME,ITM_HARDWARE_RESOURCES_STATUS,severity_type,title,alarm_text,guoziguishubumen,yunweirenyuan,process_type_name, app_id,cloud;
	
	insert into bi_alm_group_by_time
	select 
		'YEAR' AS dtype,
		substr(thedvalue,1,4) as thedvalue,
		alarm_object_ip,alarm_object_type,ITM_HARDWARE_RESOURCES_AREACODE,ITM_HARDWARE_RESOURCES_ASSNO,ITM_HARDWARE_RESOURCES_CODE,
		ITM_HARDWARE_RESOURCES_NAME,ITM_HARDWARE_RESOURCES_IP,pinpai,xinghao,ITM_HARDWARE_RESOURCES_NETNAME,ITM_HARDWARE_RESOURCES_STATUS,severity_type,title,alarm_text,guoziguishubumen,yunweirenyuan,
		sum(alarm_count) as alarm_count,sum(alarm_time) as alarm_time,sum(alarm_zy_num) as alarm_zy_num,max(total_zy_num) as total_zy_num,process_type_name, app_id,cloud
	from bi_alm_group_by_time 
	where dtype = 'MONTH' and alarm_object_type = '应用'
	group by 
	substr(thedvalue,1,4),
	alarm_object_ip,alarm_object_type,ITM_HARDWARE_RESOURCES_AREACODE,ITM_HARDWARE_RESOURCES_ASSNO,ITM_HARDWARE_RESOURCES_CODE,
	ITM_HARDWARE_RESOURCES_NAME,ITM_HARDWARE_RESOURCES_IP,pinpai,xinghao,ITM_HARDWARE_RESOURCES_NETNAME,ITM_HARDWARE_RESOURCES_STATUS,severity_type,title,alarm_text,guoziguishubumen,yunweirenyuan,process_type_name, app_id,cloud;
	




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;

	truncate table bi_work_flow_group_row;
	insert into bi_work_flow_group_row 
	select m.dtype, m.thedvalue,m.process_type_name,m.app_id,ia.ITM_APP_NAME,m.serve_area,m.serve_department,m.serve_firm, 'SUM', '件', 'total_works' as 'kind', total_works as 'value'
	from bi_work_flow_group_by_time m
	left join itm_app ia on ia.ITM_APP_ID=m.app_id
	union all
	select m.dtype, m.thedvalue,m.process_type_name,m.app_id,ia.ITM_APP_NAME,m.serve_area,m.serve_department,m.serve_firm, 'SUM', '件', 'total_works_end' as 'kind', total_works_end as 'value'
	from bi_work_flow_group_by_time m
	left join itm_app ia on ia.ITM_APP_ID=m.app_id
	union all
	select m.dtype, m.thedvalue,m.process_type_name,m.app_id,ia.ITM_APP_NAME,m.serve_area,m.serve_department,m.serve_firm, 'SUM', '件', 'total_works_noend' as 'kind', total_works_noend as 'value'
	from bi_work_flow_group_by_time m
	left join itm_app ia on ia.ITM_APP_ID=m.app_id
	union all
	select m.dtype, m.thedvalue,m.process_type_name,m.app_id,ia.ITM_APP_NAME,m.serve_area,m.serve_department,m.serve_firm, 'SUM', '件', 'total_works_notimeout' as 'kind', total_works_notimeout as 'value'
	from bi_work_flow_group_by_time m
	left join itm_app ia on ia.ITM_APP_ID=m.app_id
	union all
	select m.dtype, m.thedvalue,m.process_type_name,m.app_id,ia.ITM_APP_NAME,m.serve_area,m.serve_department,m.serve_firm, 'SUM', '件', 'total_works_timeout' as 'kind', total_works_timeout as 'value'
	from bi_work_flow_group_by_time m
	left join itm_app ia on ia.ITM_APP_ID=m.app_id
	union all
	select m.dtype, m.thedvalue,m.process_type_name,m.app_id,ia.ITM_APP_NAME,m.serve_area,m.serve_department,m.serve_firm, 'SUM', '秒', 'total_time_consuming' as 'kind', total_time_consuming as 'value'
	from bi_work_flow_group_by_time m
	left join itm_app ia on ia.ITM_APP_ID=m.app_id
	union all
	select m.dtype, m.thedvalue,m.process_type_name,m.app_id,ia.ITM_APP_NAME,m.serve_area,m.serve_department,m.serve_firm, 'AVG', '秒', 'avg_time_consuming' as 'kind', avg_time_consuming as 'value'
	from bi_work_flow_group_by_time m
	left join itm_app ia on ia.ITM_APP_ID=m.app_id
	union all
	select m.dtype, m.thedvalue,m.process_type_name,m.app_id,ia.ITM_APP_NAME,m.serve_area,m.serve_department,m.serve_firm, 'AVG', '分', 'satisfaction' as 'kind', satisfaction as 'value'
	from bi_work_flow_group_by_time m
	left join itm_app ia on ia.ITM_APP_ID=m.app_id
	union all
	select m.dtype, m.thedvalue,m.process_type_name,m.app_id,ia.ITM_APP_NAME,m.serve_area,m.serve_department,m.serve_firm, 'AVG', '%', 'total_works_notimeout_pct' as 'kind', 100*total_works_notimeout/total_works as 'value'
	from bi_work_flow_group_by_time m
	left join itm_app ia on ia.ITM_APP_ID=m.app_id
	union all
	select m.dtype, m.thedvalue,m.process_type_name,m.app_id,ia.ITM_APP_NAME,m.serve_area,m.serve_department,m.serve_firm, 'AVG', '%', 'total_works_timeout_pct' as 'kind', 100*total_works_timeout/total_works as 'value'
	from bi_work_flow_group_by_time m
	left join itm_app ia on ia.ITM_APP_ID=m.app_id
	union all
	select m.dtype, m.thedvalue,m.process_type_name,m.app_id,ia.ITM_APP_NAME,m.serve_area,m.serve_department,m.serve_firm, 'AVG', '%', 'total_works_end_pct' as 'kind', 100*total_works_end/total_works as 'value'
	from bi_work_flow_group_by_time m
	left join itm_app ia on ia.ITM_APP_ID=m.app_id;


-- （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;


truncate table bi_hardware_fugai;
insert into bi_hardware_fugai
select 'MONTH' AS dtype, a.thedvalue, b.itm_hardware_resources_id,b.itm_hardware_resources_type,b.itm_hardware_resources_cntype,b.itm_hardware_resources_areacode,b.ITM_HARDWARE_RESOURCES_ASSNO,b.ITM_HARDWARE_RESOURCES_CODE
	,b.ITM_HARDWARE_RESOURCES_NAME,b.ITM_HARDWARE_RESOURCES_IP,b.ITM_HARDWARE_RESOURCES_STATUS,b.itm_monitor_flag,b.create_time
	,b.NAME,b.resclasscnname,b.UID,b.pinpai,b.xinghao,b.mac
	,b.guoziguishurenyuan,b.guoziguishubumen,b.yunweirenyuan,b.NET_NAME as ITM_HARDWARE_RESOURCES_NETNAME,b.cloud  from (
select distinct date_format(dt_d,'%Y%m') as thedvalue from bi_m_date where dt_d >= '2021-01-01' and dt_d < '2023-12-31') a
left join bi_itm_hardware_resources b
on a.thedvalue >= date_format(b.create_time,'%Y%m')
UNION ALL
select 'YEAR' AS dtype, a.thedvalue, b.itm_hardware_resources_id,b.itm_hardware_resources_type,b.itm_hardware_resources_cntype,b.itm_hardware_resources_areacode,b.ITM_HARDWARE_RESOURCES_ASSNO,b.ITM_HARDWARE_RESOURCES_CODE
	,b.ITM_HARDWARE_RESOURCES_NAME,b.ITM_HARDWARE_RESOURCES_IP,b.ITM_HARDWARE_RESOURCES_STATUS,b.itm_monitor_flag,b.create_time
	,b.NAME,b.resclasscnname,b.UID,b.pinpai,b.xinghao,b.mac
	,b.guoziguishurenyuan,b.guoziguishubumen,b.yunweirenyuan,b.NET_NAME as ITM_HARDWARE_RESOURCES_NETNAME,b.cloud  from (
select distinct date_format(dt_d,'%Y') as thedvalue from bi_m_date where dt_d >= '2021-01-01' and dt_d < '2023-12-31') a
left join bi_itm_hardware_resources b
on a.thedvalue >= date_format(b.create_time,'%Y')
UNION ALL
select 'DAY' AS dtype, a.thedvalue, b.itm_hardware_resources_id,b.itm_hardware_resources_type,b.itm_hardware_resources_cntype,b.itm_hardware_resources_areacode,b.ITM_HARDWARE_RESOURCES_ASSNO,b.ITM_HARDWARE_RESOURCES_CODE
	,b.ITM_HARDWARE_RESOURCES_NAME,b.ITM_HARDWARE_RESOURCES_IP,b.ITM_HARDWARE_RESOURCES_STATUS,b.itm_monitor_flag,b.create_time
	,b.NAME,b.resclasscnname,b.UID,b.pinpai,b.xinghao,b.mac
	,b.guoziguishurenyuan,b.guoziguishubumen,b.yunweirenyuan,b.NET_NAME as ITM_HARDWARE_RESOURCES_NETNAME,b.cloud  from (
select distinct date_format(dt_d,'%Y%m%d') as thedvalue from bi_m_date where dt_d >= '2021-01-01' and dt_d < '2023-12-31') a
left join bi_itm_hardware_resources b
on a.thedvalue >= date_format(b.create_time,'%Y%m%d');




	-- （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;

	truncate table bi_pub_organ_siji;
	
	insert into bi_pub_organ_siji
	select 
		m.ID,m.CODE,m.NAME AS FYJB1,'-' as FYJB2,'-' as FYJB3,'-' as FYJB4,a.org_code as FYJB1_CODE,'-' as FYJB2_CODE,'-' as FYJB3_CODE,'-' as FYJB4_CODE,
		'FYJB.1' as organ_type,m.COURT_NUM,m.COURT_TYPE,m.SHORT_NAME,m.STATUS,m.PUB_LONGITUDE,m.PUB_LATITUDE,m.PUB_ORGAN_IP,
		m.SHORT_NAME AS FYJB1_JC,'-' as FYJB2_JC,'-' as FYJB3_JC,'-' as FYJB4_JC
	from pub_organ_tree a
	left join pub_organ m
	on a.org_code = m.code
	where a.parent_code = '0'
	union all
	select 
		m.ID,m.CODE,l.NAME AS FYJB1,m.NAME as FYJB2,'-' as FYJB3,'-' as FYJB4,b.org_code as FYJB1_CODE,a.org_code as FYJB2_CODE,'-' as FYJB3_CODE,'-' as FYJB4_CODE,
		'FYJB.2' as organ_type,m.COURT_NUM,m.COURT_TYPE,m.SHORT_NAME,m.STATUS,m.PUB_LONGITUDE,m.PUB_LATITUDE,m.PUB_ORGAN_IP,
		l.SHORT_NAME AS FYJB1_JC,m.SHORT_NAME as FYJB2_JC,'-' as FYJB3_JC,'-' as FYJB4_JC
	from pub_organ_tree a
	left join pub_organ_tree b
	on a.parent_code = b.org_code
	left join pub_organ l
	on b.org_code = l.code
	left join pub_organ m
	on a.org_code = m.code
	where b.parent_code = '0'
	union all
	select 
		m.ID,m.CODE,k.NAME AS FYJB1,l.NAME as FYJB2,m.NAME as FYJB3,'-' as FYJB4,c.org_code as FYJB1_CODE,b.org_code as FYJB2_CODE,a.org_code as FYJB3_CODE,'-' as FYJB4_CODE,
		'FYJB.3' as organ_type,m.COURT_NUM,m.COURT_TYPE,m.SHORT_NAME,m.STATUS,m.PUB_LONGITUDE,m.PUB_LATITUDE,m.PUB_ORGAN_IP,
		k.SHORT_NAME AS FYJB1_JC,l.SHORT_NAME as FYJB2_JC,m.SHORT_NAME as FYJB3_JC,'-' as FYJB4_JC
	from pub_organ_tree a
	left join pub_organ_tree b
	on a.parent_code = b.org_code
	left join pub_organ_tree c
	on b.parent_code = c.org_code
	left join pub_organ k
	on c.org_code = k.code
	left join pub_organ l
	on b.org_code = l.code
	left join pub_organ m
	on a.org_code = m.code
	where c.parent_code = '0'
	union all
	select 
		m.ID,m.CODE,j.NAME AS FYJB1,k.NAME as FYJB2,l.NAME as FYJB3,m.NAME as FYJB4,d.org_code as FYJB1_CODE,c.org_code as FYJB2_CODE,b.org_code as FYJB3_CODE,a.org_code as FYJB4_CODE,
		'FYJB.4' as organ_type,m.COURT_NUM,m.COURT_TYPE,m.SHORT_NAME,m.STATUS,m.PUB_LONGITUDE,m.PUB_LATITUDE,m.PUB_ORGAN_IP,
		j.SHORT_NAME AS FYJB1_JC,k.SHORT_NAME as FYJB2_JC,l.SHORT_NAME as FYJB3_JC,m.SHORT_NAME as FYJB4_JC
	from pub_organ_tree a
	left join pub_organ_tree b
	on a.parent_code = b.org_code
	left join pub_organ_tree c
	on b.parent_code = c.org_code
	left join pub_organ_tree d
	on c.parent_code = d.org_code
	left join pub_organ j
	on d.org_code = j.code
	left join pub_organ k
	on c.org_code = k.code
	left join pub_organ l
	on b.org_code = l.code
	left join pub_organ m
	on a.org_code = m.code
	where d.parent_code = '0';


-- 建表语句

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;


truncate table bi_sijifayuan_alm_group_by_time;
	insert into bi_sijifayuan_alm_group_by_time
	select 
		'HOUR' AS dtype,
		date_format(occur_time,'%Y%m%d%H')  as thedvalue,
		date_format(occur_time,'%H')  as dhour,
		alarm_object_id,
		alarm_object_ip,
		alarm_object_type,severity_type,	 
		title,
		alarm_text,
		is_active_alarm,
		FYJB1,FYJB2,FYJB3,FYJB4,FYJB1_CODE,FYJB2_CODE,FYJB3_CODE,FYJB4_CODE,organ_type,COURT_NUM,COURT_TYPE,SHORT_NAME,STATUS,PUB_LONGITUDE,PUB_LATITUDE,PUB_ORGAN_IP,
		sum(alarm_count) as alarm_count,
		sum(alarm_time) as alarm_time,
		count(1) as alarm_zy_num,
		(select count(1) from bi_itm_hardware_resources where itm_hardware_resources_cntype != '非资产设备') as total_zy_num,
		0 as pingall,
		0 as pingdown
	from (
	select 
		 a.occur_time,
		 a.alarm_object_id,
		 a.alarm_object_ip,
		 a.alarm_object_type,	
		case a.severity_id when 1 then '致命' when 2 then '严重' when 3 then '一般' else '未知' end as severity_type,
		 a.title,
		 a.alarm_text,
		 a.is_active_alarm,
		 a.alarm_count,
		 timestampdiff(MINUTE,a.occur_time,a.end_time)*1.0/60 as alarm_time,
		b.FYJB1,b.FYJB2,b.FYJB3,b.FYJB4,b.FYJB1_CODE,b.FYJB2_CODE,b.FYJB3_CODE,b.FYJB4_CODE,b.organ_type,b.COURT_NUM,b.COURT_TYPE,b.SHORT_NAME,b.STATUS,b.PUB_LONGITUDE,b.PUB_LATITUDE,b.PUB_ORGAN_IP
	from (select * from v_bi_alm_all_alarm  where alarm_object_type in ('网络','路由器','交换机')) a
	left join bi_pub_organ_siji b
	on a.alarm_object_id = b.CODE) m
	group by 
		date_format(occur_time,'%Y%m%d%H') ,
		date_format(occur_time,'%H') ,
		alarm_object_id,
		alarm_object_ip,
		alarm_object_type,severity_type,
		title,
		alarm_text,
		is_active_alarm,
		FYJB1,FYJB2,FYJB3,FYJB4,FYJB1_CODE,FYJB2_CODE,FYJB3_CODE,FYJB4_CODE,organ_type,COURT_NUM,COURT_TYPE,SHORT_NAME,STATUS,PUB_LONGITUDE,PUB_LATITUDE,PUB_ORGAN_IP;
	
	insert into bi_sijifayuan_alm_group_by_time
	select 
		'DAY' AS dtype,
		substring(thedvalue,1,8) AS thedvalue,
		dhour as dhour,
		alarm_object_id,
		alarm_object_ip,
		alarm_object_type,severity_type,	 
		title,
		alarm_text,
		is_active_alarm,
		FYJB1,FYJB2,FYJB3,FYJB4,FYJB1_CODE,FYJB2_CODE,FYJB3_CODE,FYJB4_CODE,organ_type,COURT_NUM,COURT_TYPE,SHORT_NAME,STATUS,PUB_LONGITUDE,PUB_LATITUDE,PUB_ORGAN_IP,
		sum(alarm_count) as alarm_count,
		sum(alarm_time) as alarm_time,
		SUM(alarm_zy_num) as alarm_zy_num,
		AVG(total_zy_num) as total_zy_num,
		SUM(pingall) as pingall,
		SUM(pingdown) as pingdown
	FROM bi_sijifayuan_alm_group_by_time
	where dtype = 'HOUR'
	GROUP BY 
		substring(thedvalue,1,8),
		dhour,
		alarm_object_id,
		alarm_object_ip,
		alarm_object_type,severity_type,	 
		title,
		alarm_text,
		is_active_alarm,
		FYJB1,FYJB2,FYJB3,FYJB4,FYJB1_CODE,FYJB2_CODE,FYJB3_CODE,FYJB4_CODE,organ_type,COURT_NUM,COURT_TYPE,SHORT_NAME,STATUS,PUB_LONGITUDE,PUB_LATITUDE,PUB_ORGAN_IP;
	
	insert into bi_sijifayuan_alm_group_by_time
	select 
		'MONTH' AS dtype,
		substring(thedvalue,1,6) AS thedvalue,
		dhour as dhour,
		alarm_object_id,
		alarm_object_ip,
		alarm_object_type,severity_type,	 
		title,
		alarm_text,
		is_active_alarm,
		FYJB1,FYJB2,FYJB3,FYJB4,FYJB1_CODE,FYJB2_CODE,FYJB3_CODE,FYJB4_CODE,organ_type,COURT_NUM,COURT_TYPE,SHORT_NAME,STATUS,PUB_LONGITUDE,PUB_LATITUDE,PUB_ORGAN_IP,
		sum(alarm_count) as alarm_count,
		sum(alarm_time) as alarm_time,
		SUM(alarm_zy_num) as alarm_zy_num,
		AVG(total_zy_num) as total_zy_num,
		SUM(pingall) as pingall,
		SUM(pingdown) as pingdown
	FROM bi_sijifayuan_alm_group_by_time
	where dtype = 'DAY'
	GROUP BY 
		substring(thedvalue,1,6),
		dhour,
		alarm_object_id,
		alarm_object_ip,
		alarm_object_type,severity_type,	 
		title,
		alarm_text,
		is_active_alarm,
		FYJB1,FYJB2,FYJB3,FYJB4,FYJB1_CODE,FYJB2_CODE,FYJB3_CODE,FYJB4_CODE,organ_type,COURT_NUM,COURT_TYPE,SHORT_NAME,STATUS,PUB_LONGITUDE,PUB_LATITUDE,PUB_ORGAN_IP;
	
	insert into bi_sijifayuan_alm_group_by_time
	select 
		'YEAR' AS dtype,
		substring(thedvalue,1,4) AS thedvalue,
		dhour as dhour,
		alarm_object_id,
		alarm_object_ip,
		alarm_object_type,severity_type,	 
		title,
		alarm_text,
		is_active_alarm,
		FYJB1,FYJB2,FYJB3,FYJB4,FYJB1_CODE,FYJB2_CODE,FYJB3_CODE,FYJB4_CODE,organ_type,COURT_NUM,COURT_TYPE,SHORT_NAME,STATUS,PUB_LONGITUDE,PUB_LATITUDE,PUB_ORGAN_IP,
		sum(alarm_count) as alarm_count,
		sum(alarm_time) as alarm_time,
		SUM(alarm_zy_num) as alarm_zy_num,
		AVG(total_zy_num) as total_zy_num,
		SUM(pingall) as pingall,
		SUM(pingdown) as pingdown
	FROM bi_sijifayuan_alm_group_by_time
	where dtype = 'MONTH'
	GROUP BY 
		substring(thedvalue,1,4),
		dhour,
		alarm_object_id,
		alarm_object_ip,
		alarm_object_type,severity_type,	 
		title,
		alarm_text,
		is_active_alarm,
		FYJB1,FYJB2,FYJB3,FYJB4,FYJB1_CODE,FYJB2_CODE,FYJB3_CODE,FYJB4_CODE,organ_type,COURT_NUM,COURT_TYPE,SHORT_NAME,STATUS,PUB_LONGITUDE,PUB_LATITUDE,PUB_ORGAN_IP;
	
	
	
	
	-- (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;

TRUNCATE table bi_host_stat;	
INSERT INTO bi_host_stat 
select 
		'HOUR' as dtype,
		a.thedvalue,
		a.COMPANY,
case b.mm when 1 then 'CPU' when 2 then '内存' when 3 then '硬盘'when 4 then 'CPU' when 5 then '内存' when  6 then '硬盘'when 7 then 'CPU' when 8 then '内存' when 9 then '硬盘' when 10 then 'CPU' when 11 then 		   		'内存' when 12 then '硬盘' else '' end as target,		
case b.mm when 1 then '额定量' when 2 then '额定量' when 3 then '额定量' when 4 then '分配量' 
			when 5 then '分配量' when 6 then '分配量'when 7 then '剩余可用' when 8 then '剩余可用' when 9 then '剩余可用' when 10 then '分配占比' when 11 then '分配占比' when 12 then '分配占比' else '' end as dim,		
round(case b.mm when 1 then dim1 when 2 then dim2 when 3 then dim3 when 4 then dim4 when 5 then dim5 when 6 then dim6 when 7 then dim7 when 8 then dim8 when 9 then dim9 when 10 then IF(dim1<>0,dim4/dim1,0) when 11 
			then IF(dim2<>0,dim5/dim2,0) when 12 then IF(dim3<>0,dim6/dim3,0) else null end ,2) as dim_value,				
case b.mm when 1 then 'avg' when 2 then 'max' when 3 then 'max'when 4 then 'avg' when 5 then 'max' when 6 then 	'max'when 7 then 'avg' when 8 then 'max' when 9 then 'max' else 'avg' end as group_agg,
case b.mm when 1 then 'GB' when 2 then 'GB' when 3 then 'GB' when 4 then 'GB' 
  when 5 then 'GB' when 6 then 'GB'when 7 then 'GB' when 8 then 'GB' when 9 then 'GB' when 10 then '%' when 11 	then '%' when 12 then '%' else '' end as unit,
	cloud from (
	SELECT
		date_format( temp1.CREATE_TIME, '%Y%m%d%H' ) AS thedvalue,
	CASE
			temp1.COMPANY 
			WHEN 'huawei' THEN
			'太极云资源池' 
			WHEN 'huawei-xc' THEN
			'信创云资源池' 
			WHEN 'shouxin' THEN
			'首信云资源池' 
			WHEN 'unicom' THEN
			'联通云资源池' ELSE '其它' 
		END AS COMPANY,
		avg( IND_VALUE_HOST_CPU_UTILIZATION ) AS dim1,
		MAX( IND_VALUE_HOST_MEMORY_TOTAL ) AS dim2,
		MAX( IND_VALUE_HOST_DISK_TOTAL ) AS dim3,
		avg( IND_VALUE_HOST_CPU_TOP ) AS dim4,
		MAX( IND_VALUE_HOST_MEMORY_USED ) AS dim5,
		MAX( IND_VALUE_HOST_DISK_USED ) AS dim6,
		avg( IND_VALUE_HOST_CPU_UTILIZATION_LOW ) AS dim7,
		MAX( IND_VALUE_HOST_MEMORY_UNUSED ) AS dim8,
		MAX( IND_VALUE_HOST_DISK_UNUSED ) AS dim9,
		temp2.cloud 
	FROM
		app_host_stat temp1
		LEFT JOIN itm_hardware_resources AS temp2 ON temp1.int_id = temp2.ITM_HARDWARE_RESOURCES_ID 
	WHERE
		temp1.COMPANY IS NOT NULL 
	GROUP BY
		temp1.COMPANY,
		date_format( temp1.CREATE_TIME, '%Y%m%d%H' ),
		temp2.cloud 
	) a
			cross join
			(select 1 mm 
			union all select 2 
			union all select 3
			union all select 4
			union all select 5
			union all select 6
			union all select 7
			union all select 8
			union all select 9
			union all select 10
			union all select 11
			union all select 12) b; 

INSERT INTO bi_host_stat 
	select 'DAY' as dtype,substring(thedvalue,1,8) AS thedvalue,COMPANY,target,dim,AVG(dim_value),group_agg,unit,cloud  
	from bi_host_stat 
	where group_agg = 'avg' AND dtype = 'HOUR'
	GROUP BY substring(thedvalue,1,8),COMPANY,target,dim,group_agg,unit,cloud  
	UNION ALL
	select 'DAY' as dtype,substring(thedvalue,1,8) AS thedvalue,COMPANY,target,dim,MAX(dim_value),group_agg,unit,cloud  
	from bi_host_stat 
	where group_agg = 'MAX' AND dtype = 'HOUR'
	GROUP BY substring(thedvalue,1,8),COMPANY,target,dim,group_agg,unit,cloud  ;
	
	INSERT INTO bi_host_stat 
	select 'MONTH' as dtype,substring(thedvalue,1,6) AS thedvalue,COMPANY,target,dim,AVG(dim_value),group_agg,unit,cloud    
	from bi_host_stat 
	where group_agg = 'avg' AND dtype = 'DAY'
	GROUP BY substring(thedvalue,1,6),COMPANY,target,dim,group_agg,unit,cloud  
	UNION ALL
	select 'MONTH' as dtype,substring(thedvalue,1,6) AS thedvalue,COMPANY,target,dim,MAX(dim_value),group_agg,unit,cloud    
	from bi_host_stat 
	where group_agg = 'MAX' AND dtype = 'DAY'
	GROUP BY substring(thedvalue,1,6),COMPANY,target,dim,group_agg,unit,cloud;
	
	INSERT INTO bi_host_stat
	select 'YEAR' as dtype,substring(thedvalue,1,4) AS thedvalue,COMPANY,target,dim,AVG(dim_value),group_agg,unit,cloud    
	from bi_host_stat 
	where group_agg = 'avg' AND dtype = 'MONTH'
	GROUP BY substring(thedvalue,1,4),COMPANY,target,dim,group_agg,unit,cloud  
	UNION ALL
	select 'YEAR' as dtype,substring(thedvalue,1,4) AS thedvalue,COMPANY,target,dim,MAX(dim_value),group_agg,unit,cloud    
	from bi_host_stat 
	where group_agg = 'MAX' AND dtype = 'MONTH'
	GROUP BY substring(thedvalue,1,4),COMPANY,target,dim,group_agg,unit,cloud;



	-- （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';

TRUNCATE table bi_recycle_trend;
	insert into bi_recycle_trend
	SELECT 
		'HOUR' as dtype,
		date_format(a.start_time,'%Y%m%d%H')  as thedvalue,
		c.itm_hardware_resources_cntype,
		c.NET_NAME,
		count(1) as total,
		sum(case when a.end_time is not null then 1 else 0 end) as total_recycled,
		sum(case when a.end_time is null then 1 else 0 end) as total_no_recycled
	from icm_bpm.t_work_flow a
	LEFT JOIN icm_bpm.t_work_resource_real b on a.id = b.work_id
	-- LEFT JOIN v_bi_itm_hardware_resources c on c.itm_hardware_resources_id=b.resource_id
	left join (select itm_hardware_resources_ip,itm_hardware_resources_cntype,NET_NAME from (
	select itm_hardware_resources_ip,itm_hardware_resources_cntype,NET_NAME from bi_itm_hardware_resources) a
	group by itm_hardware_resources_ip) c on c.itm_hardware_resources_ip = b.object_id 
	WHERE a.process_type_name='基础设施退网'
	GROUP BY date_format(a.start_time,'%Y%m%d%H') ,c.itm_hardware_resources_cntype,c.NET_NAME;
	
	insert into bi_recycle_trend
	select 
		'DAY' as dtype,
		substr(thedvalue,1,8) as thedvalue,
		itm_hardware_resources_cntype,
		NET_NAME,
		sum(total) as total,
		sum(total_recycled) as total_recycled,
		sum(total_no_recycled) as total_no_recycled
	from bi_recycle_trend
	where dtype = 'HOUR'
	GROUP BY substr(thedvalue,1,8),itm_hardware_resources_cntype,
		NET_NAME;
	
	insert into bi_recycle_trend
	select 
		'MONTH' as dtype,
		substr(thedvalue,1,6) as thedvalue,
		itm_hardware_resources_cntype,
		NET_NAME,
		sum(total) as total,
		sum(total_recycled) as total_recycled,
		sum(total_no_recycled) as total_no_recycled
	from bi_recycle_trend
	where dtype = 'DAY'
	GROUP BY substr(thedvalue,1,6),itm_hardware_resources_cntype,
		NET_NAME;

	insert into bi_recycle_trend
	select 
		'YEAR' as dtype,
		substr(thedvalue,1,4) as thedvalue,
		itm_hardware_resources_cntype,
		NET_NAME,
		sum(total) as total,
		sum(total_recycled) as total_recycled,
		sum(total_no_recycled) as total_no_recycled
	from bi_recycle_trend
	where dtype = 'MONTH'
	GROUP BY substr(thedvalue,1,4),itm_hardware_resources_cntype,
		NET_NAME;
	
	
	
	
	-- （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;
	
	TRUNCATE table bi_app_fugai;
	insert into bi_app_fugai
select 'DAY' AS dtype, a.thedvalue, b.ITM_APP_ID ,b.ITM_APP_CODE ,b.ITM_APP_NAME ,b.ITM_APP_SHORTNAME ,b.ITM_APP_DESC ,b.ITM_APP_DEPT ,b.ITM_APP_COURTCODE ,
	b.ITM_NETGROUP_ID ,b.ITM_APPCATAGORY_ID ,b.ITM_APP_VENDOR ,b.ITM_APP_UPTIME ,b.ITM_APP_VERSION ,b.ITM_APP_MAINTENANCE_ID ,b.ITM_APP_ORDER ,b.ITM_APP_NETID ,
	b.ITM_APP_ICONSRC ,b.ITM_APP_SYSTEMURL ,b.ORGAN_ID ,b.IS_GLOBAL ,b.ITM_APP_PID ,b.PLATFORM ,b.PRIORITY ,b.ITM_APP_IP ,b.ITM_APP_PORT ,b.ITM_APP_WEBROOT ,
	b.ITM_LOCATION ,b.ITM_APP_ASSURE_LEVEL ,b.SHOW_TYPE ,b.ITM_SHOW_FLAG ,b.int_id ,b.INSERT_TIME ,b.mantance_vendor ,b.mantance_duty ,b.mantance_duty_phone ,
	b.start_date ,b.mantance_year_number ,b.ne_type ,b.creator ,b.create_time ,b.modifier ,b.modify_time ,b.time_stamp ,b.stateflag ,b.ruuid ,b.data_Type ,b.login_action ,
	b.localize_flag ,b.current_stat ,b.contract ,b.emergency_flag ,b.monitor_flag ,b.development_language ,b.app_type ,b.contract_money ,b.unified_login, 
case b.stateflag when 0 then '已应用' else '未应用' end as stateflag1,
case b.PRIORITY WHEN 'low' then '一般应用' when 'important' then '重要应用' when 'high' then '核心应用' else '未知' end as PRIORITY1,
c.name as app_type_name, 
d.net_name as net_name,
e.cloud_flag as cloud_flag,
f.`SHORT_NAME` as organ_name,
e.itm_hardware_resources_areacode as itm_hardware_resources_areacode,
b.on_line as on_line,
e.cloud
from (
select distinct date_format(dt_d,'%Y%m%d') as thedvalue from bi_m_date where dt_d >= '2021-01-01' and dt_d < '2023-11-09') a
left join itm_app b
on a.thedvalue >= date_format(b.create_time,'%Y%m%d')
LEFT JOIN m_com_dict c on b.app_type=c.value
LEFT JOIN itm_organ_net d on d.NET_ID = b.ITM_NETGROUP_ID
-- 只要app的一个资源上云了 就说明上云了
LEFT JOIN (SELECT servicename,itm_hardware_resources_areacode,concat(max(CASE WHEN itm_hardware_resources_cntype = '云主机' then 1 else 0 end),'') as cloud_flag,cloud
from bi_itm_hardware_resources where servicename is not null and servicename != '' group by servicename,itm_hardware_resources_areacode,cloud) e
on b.ITM_APP_NAME= e.servicename
left join pub_organ f
on b.ORGAN_ID = f.`CODE`
WHERE c.dict_index='app_type'
UNION ALL
select 'MONTH' AS dtype, a.thedvalue, b.ITM_APP_ID ,b.ITM_APP_CODE ,b.ITM_APP_NAME ,b.ITM_APP_SHORTNAME ,b.ITM_APP_DESC ,b.ITM_APP_DEPT ,b.ITM_APP_COURTCODE ,
	b.ITM_NETGROUP_ID ,b.ITM_APPCATAGORY_ID ,b.ITM_APP_VENDOR ,b.ITM_APP_UPTIME ,b.ITM_APP_VERSION ,b.ITM_APP_MAINTENANCE_ID ,b.ITM_APP_ORDER ,b.ITM_APP_NETID ,
	b.ITM_APP_ICONSRC ,b.ITM_APP_SYSTEMURL ,b.ORGAN_ID ,b.IS_GLOBAL ,b.ITM_APP_PID ,b.PLATFORM ,b.PRIORITY ,b.ITM_APP_IP ,b.ITM_APP_PORT ,b.ITM_APP_WEBROOT ,
	b.ITM_LOCATION ,b.ITM_APP_ASSURE_LEVEL ,b.SHOW_TYPE ,b.ITM_SHOW_FLAG ,b.int_id ,b.INSERT_TIME ,b.mantance_vendor ,b.mantance_duty ,b.mantance_duty_phone ,
	b.start_date ,b.mantance_year_number ,b.ne_type ,b.creator ,b.create_time ,b.modifier ,b.modify_time ,b.time_stamp ,b.stateflag ,b.ruuid ,b.data_Type ,b.login_action ,
	b.localize_flag ,b.current_stat ,b.contract ,b.emergency_flag ,b.monitor_flag ,b.development_language ,b.app_type ,b.contract_money ,b.unified_login, 
case b.stateflag when 0 then '已应用' else '未应用' end as stateflag1,
case b.PRIORITY WHEN 'low' then '一般应用' when 'important' then '重要应用' when 'high' then '核心应用' else '未知' end as PRIORITY1,
c.name as app_type_name, 
d.net_name as net_name,
e.cloud_flag as cloud_flag,
f.`SHORT_NAME` as organ_name,
e.itm_hardware_resources_areacode as itm_hardware_resources_areacode,
b.on_line as on_line,
e.cloud
from (
select distinct date_format(dt_d,'%Y%m') as thedvalue from bi_m_date where dt_d >= '2021-01-01' and dt_d < '2023-11-09') a
left join itm_app b
on a.thedvalue >= date_format(b.create_time,'%Y%m')
LEFT JOIN m_com_dict c on b.app_type=c.value
LEFT JOIN itm_organ_net d on d.NET_ID = b.ITM_NETGROUP_ID  
-- 只要app的一个资源上云了 就说明上云了
LEFT JOIN (SELECT servicename,itm_hardware_resources_areacode,concat(max(CASE WHEN itm_hardware_resources_cntype = '云主机' then 1 else 0 end),'') as cloud_flag,cloud
from bi_itm_hardware_resources where servicename is not null and servicename != '' group by servicename,itm_hardware_resources_areacode,cloud) e
on b.ITM_APP_NAME= e.servicename
left join pub_organ f
on b.ORGAN_ID = f.`CODE`
WHERE c.dict_index='app_type'
UNION ALL
select 'YEAR' AS dtype, a.thedvalue, b.ITM_APP_ID ,b.ITM_APP_CODE ,b.ITM_APP_NAME ,b.ITM_APP_SHORTNAME ,b.ITM_APP_DESC ,b.ITM_APP_DEPT ,b.ITM_APP_COURTCODE ,
	b.ITM_NETGROUP_ID ,b.ITM_APPCATAGORY_ID ,b.ITM_APP_VENDOR ,b.ITM_APP_UPTIME ,b.ITM_APP_VERSION ,b.ITM_APP_MAINTENANCE_ID ,b.ITM_APP_ORDER ,b.ITM_APP_NETID ,
	b.ITM_APP_ICONSRC ,b.ITM_APP_SYSTEMURL ,b.ORGAN_ID ,b.IS_GLOBAL ,b.ITM_APP_PID ,b.PLATFORM ,b.PRIORITY ,b.ITM_APP_IP ,b.ITM_APP_PORT ,b.ITM_APP_WEBROOT ,
	b.ITM_LOCATION ,b.ITM_APP_ASSURE_LEVEL ,b.SHOW_TYPE ,b.ITM_SHOW_FLAG ,b.int_id ,b.INSERT_TIME ,b.mantance_vendor ,b.mantance_duty ,b.mantance_duty_phone ,
	b.start_date ,b.mantance_year_number ,b.ne_type ,b.creator ,b.create_time ,b.modifier ,b.modify_time ,b.time_stamp ,b.stateflag ,b.ruuid ,b.data_Type ,b.login_action ,
	b.localize_flag ,b.current_stat ,b.contract ,b.emergency_flag ,b.monitor_flag ,b.development_language ,b.app_type ,b.contract_money ,b.unified_login, 
case b.stateflag when 0 then '已应用' else '未应用' end as stateflag1,
case b.PRIORITY WHEN 'low' then '一般应用' when 'important' then '重要应用' when 'high' then '核心应用' else '未知' end as PRIORITY1,
c.name as app_type_name, 
d.net_name as net_name,
e.cloud_flag as cloud_flag,
f.`SHORT_NAME` as organ_name,
e.itm_hardware_resources_areacode as itm_hardware_resources_areacode,
b.on_line as on_line,
e.cloud
from (
select distinct date_format(dt_d,'%Y') as thedvalue from bi_m_date where dt_d >= '2021-01-01' and dt_d < '2023-11-09') a
left join itm_app b
on a.thedvalue >= date_format(b.create_time,'%Y')
LEFT JOIN m_com_dict c on b.app_type=c.value
LEFT JOIN itm_organ_net d on d.NET_ID = b.ITM_NETGROUP_ID
-- 只要app的一个资源上云了 就说明上云了
LEFT JOIN (SELECT servicename,itm_hardware_resources_areacode,concat(max(CASE WHEN itm_hardware_resources_cntype = '云主机' then 1 else 0 end),'') as cloud_flag, cloud
from bi_itm_hardware_resources where servicename is not null and servicename != '' group by servicename,itm_hardware_resources_areacode,cloud) e
on b.ITM_APP_NAME= e.servicename
left join pub_organ f
on b.ORGAN_ID = f.`CODE`
WHERE c.dict_index='app_type';





	-- （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';
truncate table bi_online_trend;
	insert into bi_online_trend
		SELECT 
		'HOUR' as dtype,
		date_format(a.end_time,'%Y%m%d%H') as thedvalue,
		c.itm_hardware_resources_areacode,
		c.itm_hardware_resources_cntype,
		c.itm_hardware_resources_name,
		c.NET_NAME,
		SUM(CASE WHEN a.process_type_name='基础设施入网' and a.end_time is not null THEN 1 ELSE 0 END) as 'total',
		SUM(CASE WHEN a.process_type_name='基础设施退网' and a.end_time is not null THEN 1 ELSE 0 END) as 'ended_total',
		SUM(CASE WHEN a.process_type_name='基础设施退网' and a.end_time is null THEN 1 ELSE 0 END) as 'ending_total'
	from icm_bpm.t_work_flow a
	LEFT JOIN icm_bpm.t_work_resource_real b on a.id = b.work_id
	left join (
		select itm_hardware_resources_id,itm_hardware_resources_cntype,NET_NAME,itm_hardware_resources_areacode,itm_hardware_resources_name from (
		select itm_hardware_resources_id,itm_hardware_resources_cntype,NET_NAME,itm_hardware_resources_areacode, itm_hardware_resources_name from bi_itm_hardware_resources) a
		group by itm_hardware_resources_id
	) c on c.itm_hardware_resources_id = b.object_id 
	GROUP BY date_format(a.end_time,'%Y%m%d%H'),c.itm_hardware_resources_areacode,c.itm_hardware_resources_cntype,c.NET_NAME;

	insert into bi_online_trend
	select 
		'DAY' as dtype,
		substr(thedvalue,1,8) as thedvalue,
		itm_hardware_resources_areacode,
		itm_hardware_resources_cntype,
		itm_hardware_resources_name,
		NET_NAME,
		sum(total) as total,
		sum(ended_total) as ended_total,
		sum(ending_total) as ending_total
	from bi_online_trend
	where dtype = 'HOUR'
	GROUP BY substr(thedvalue,1,8),itm_hardware_resources_areacode,itm_hardware_resources_cntype,NET_NAME;

	insert into bi_online_trend
	select 
		'MONTH' as dtype,
		substr(thedvalue,1,6) as thedvalue,
		itm_hardware_resources_areacode,
		itm_hardware_resources_cntype,
		itm_hardware_resources_name,
		NET_NAME,
		sum(total) as total,
		sum(ended_total) as ended_total,
		sum(ending_total) as ending_total
	from bi_online_trend
	where dtype = 'DAY'
	GROUP BY substr(thedvalue,1,6),itm_hardware_resources_areacode,itm_hardware_resources_cntype,NET_NAME;

	insert into bi_online_trend
	select 
		'YEAR' as dtype,
		substr(thedvalue,1,4) as thedvalue,
		itm_hardware_resources_areacode,
		itm_hardware_resources_cntype,
		itm_hardware_resources_name,
		NET_NAME,
		sum(total) as total,
		sum(ended_total) as ended_total,
		sum(ending_total) as ending_total
	from bi_online_trend
	where dtype = 'MONTH'
	GROUP BY substr(thedvalue,1,4),itm_hardware_resources_areacode,itm_hardware_resources_cntype,NET_NAME;



	-- （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;


	truncate table bi_app_log_result;
	insert into bi_app_log_result
	select 
		'HOUR' as dtype,
		date_format(a.monitor_time,'%Y%m%d%H') as thedvalue,
		date_format(a.monitor_time,'%H') as dhour,
		case b.PRIORITY WHEN 'low' then '一般应用' when 'important' then '重要应用' when 'high' then '核心应用' else '未知' end as PRIORITY,
		d.name as app_type_name, 
		c.net_name as net_name, 
		a.id ,a.app_id ,a.app_name ,a.orgin_id ,a.net_id,sum(a.independent_visits_num) as independent_visits_num,sum(a.visits_num) as visits_num,
		sum(a.oper_num) as oper_num,avg(a.slow_oper + 0.0) as slow_oper,min(a.min_resp_time) as min_resp_time,max(max_resp_time) as max_resp_time,
		avg(a.avg_resp_time + 0.0) as avg_resp_time,date_format(a.monitor_time,'%H') as time_stamp,'' as monitor_time,
		sum(a.territory_visitor) as territory_visitor,sum(a.reg_user_num) as reg_user_num,'' as sign_num,sum(a.new_reg_user_num) as new_reg_user_num,
		'' as time_type,'' as create_time,
		e.`SHORT_NAME` as organ_name
	from t_ind_obj_app_log_result a
	LEFT JOIN itm_app b on a.app_id =b.ITM_APP_ID
	LEFT JOIN itm_organ_net c on c.NET_ID = a.net_id
	inner JOIN (select * from m_com_dict where dict_index = 'app_type') d on b.app_type=d.value
	left join pub_organ e
	on b.ORGAN_ID = e.`CODE`
	WHERE 
	time_type in('hour')
	group by 
	date_format(a.monitor_time,'%Y%m%d%H'),date_format(a.monitor_time,'%H'),
	case b.PRIORITY WHEN 'low' then '一般应用' when 'important' then '重要应用' when 'high' then '核心应用' else '未知' end,d.name,c.net_name,a.id ,a.app_id ,a.app_name ,a.orgin_id ,a.net_id,e.`SHORT_NAME`;
	
	insert into bi_app_log_result
	select 
		'DAY' as dtype,
		substring(thedvalue,1,8) AS thedvalue,
		dhour,PRIORITY,app_type_name,net_name,id ,app_id ,app_name ,orgin_id ,net_id,sum(independent_visits_num) as independent_visits_num,sum(visits_num) as visits_num,
		sum(oper_num) as oper_num,avg(slow_oper + 0.0) as slow_oper,min(min_resp_time) as min_resp_time,max(max_resp_time) as max_resp_time,
		avg(avg_resp_time + 0.0) as avg_resp_time,dhour as time_stamp,'' as monitor_time,
		sum(territory_visitor) as territory_visitor,sum(reg_user_num) as reg_user_num,'' as sign_num,sum(new_reg_user_num) as new_reg_user_num,
		'' as time_type,'' as create_time,organ_name
	from bi_app_log_result
	where dtype = 'HOUR'
	group by substring(thedvalue,1,8),dhour,PRIORITY,app_type_name,net_name,id ,app_id ,app_name ,orgin_id ,net_id,organ_name;
	
	insert into bi_app_log_result
	select 
		'MONTH' as dtype,
		substring(thedvalue,1,6) AS thedvalue,
		dhour,PRIORITY,app_type_name,net_name,id ,app_id ,app_name ,orgin_id ,net_id,sum(independent_visits_num) as independent_visits_num,sum(visits_num) as visits_num,
		sum(oper_num) as oper_num,avg(slow_oper + 0.0) as slow_oper,min(min_resp_time) as min_resp_time,max(max_resp_time) as max_resp_time,
		avg(avg_resp_time + 0.0) as avg_resp_time,dhour as time_stamp,'' as monitor_time,
		sum(territory_visitor) as territory_visitor,sum(reg_user_num) as reg_user_num,'' as sign_num,sum(new_reg_user_num) as new_reg_user_num,
		'' as time_type,'' as create_time,organ_name
	from bi_app_log_result
	where dtype = 'DAY'
	group by substring(thedvalue,1,6),dhour,PRIORITY,app_type_name,net_name,id ,app_id ,app_name ,orgin_id ,net_id,organ_name;

	insert into bi_app_log_result
	select 
		'YEAR' as dtype,
		substring(thedvalue,1,4) AS thedvalue,
		dhour,PRIORITY,app_type_name,net_name,id ,app_id ,app_name ,orgin_id ,net_id,sum(independent_visits_num) as independent_visits_num,sum(visits_num) as visits_num,
		sum(oper_num) as oper_num,avg(slow_oper + 0.0) as slow_oper,min(min_resp_time) as min_resp_time,max(max_resp_time) as max_resp_time,
		avg(avg_resp_time + 0.0) as avg_resp_time,dhour as time_stamp,'' as monitor_time,
		sum(territory_visitor) as territory_visitor,sum(reg_user_num) as reg_user_num,'' as sign_num,sum(new_reg_user_num) as new_reg_user_num,
		'' as time_type,'' as create_time,organ_name
	from bi_app_log_result
	where dtype = 'MONTH'
	group by substring(thedvalue,1,4),dhour,PRIORITY,app_type_name,net_name,id ,app_id ,app_name ,orgin_id ,net_id,organ_name;



	-- （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;


	truncate table bi_panoramic_view_app_ld_trend;
	insert into bi_panoramic_view_app_ld_trend
	select 
		'DAY' as dtype,date_format(savi.insert_time,'%Y%m%d') as thedvalue,
		savi.name as app_name,
	    max(savi.vul_total_count) as score_count,
		ion.NET_NAME as NET_NAME,
	    mcd.name as score_name
	from safe_app_vul_info savi
	LEFT JOIN itm_app ia on savi.name = ia.ITM_APP_NAME
	LEFT JOIN m_com_dict mcd on ia.app_type = mcd.dispindex and mcd.dict_index='app_type'
	LEFT JOIN itm_organ_net ion on ia.ITM_NETGROUP_ID = ion.NET_ID
	GROUP BY date_format(savi.insert_time,'%Y%m%d'),savi.name,ion.NET_NAME,mcd.name;
	
	insert into bi_panoramic_view_app_ld_trend
	select 
		'MONTH' as dtype,
		substr(thedvalue,1,6) as thedvalue,
		app_name,
	    sum(score_count) as score_count,
		NET_NAME,
	    score_name
	from bi_panoramic_view_app_ld_trend
	where dtype = 'DAY'
	GROUP BY substr(thedvalue,1,6),app_name,NET_NAME,score_name;
	
	insert into bi_panoramic_view_app_ld_trend
	select 
		'YEAR' as dtype,
		substr(thedvalue,1,4) as thedvalue,
		app_name,
	    sum(score_count) as score_count,
		NET_NAME,
	    score_name
	from bi_panoramic_view_app_ld_trend
	where dtype = 'MONTH'
	GROUP BY substr(thedvalue,1,4),app_name,NET_NAME,score_name;


-- 建表语句
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;


-- 用cross join进行的列转行
	truncate table bi_panoramic_new_trend;
	insert into bi_panoramic_new_trend
	select 
		'DAY' as dtype,
		thedvalue,
		'' as app_name,
		case b.mm when 1 then '安全设备' when 2 then '网络设备' when 3 then '终端PC' when 4 then '服务器' else '' end as score_name,
		case b.mm when 1 then security_device_vul_total_count when 2 then network_device_vul_total_count when 3 then terminal_vul_total_count when 4 then server_vul_total_count else 0.0 end as the_score,
		null as NET_NAME,
		null as organ_name
	from 
	(select 
		date_format(insert_time,'%Y%m%d') as thedvalue,
		max(security_device_vul_total_count) + 0.0 as security_device_vul_total_count,
		max(network_device_vul_total_count) + 0.0 as network_device_vul_total_count,
		max(terminal_vul_total_count) + 0.0 as terminal_vul_total_count,
		max(server_vul_total_count) + 0.0 as server_vul_total_count
	from safe_index_info_1 
	group by date_format(insert_time,'%Y%m%d'),id) a
	cross join
	(select 1 mm 
	union all select 2 
	union all select 3
	union all select 4) b
	union all
	select 
		'DAY' as dtype,
		date_format(savi.insert_time,'%Y%m%d') as thedvalue,
		savi.name as app_name,
		'应用系统' as score_name,
	    max(savi.vul_total_count) as the_score,
		ion.NET_NAME as NET_NAME,
	    f.name as organ_name
	from safe_app_vul_info savi
	LEFT JOIN itm_app ia on savi.name = ia.ITM_APP_NAME
	LEFT JOIN m_com_dict mcd on ia.app_type = mcd.dispindex and mcd.dict_index='app_type'
	LEFT JOIN itm_organ_net ion on ia.ITM_NETGROUP_ID = ion.NET_ID
	left join pub_organ f on ia.ORGAN_ID = f.`CODE`
	GROUP BY date_format(savi.insert_time,'%Y%m%d'),savi.name,ion.NET_NAME,f.name;
	
	insert into bi_panoramic_new_trend
	select 'MONTH' as dtype,substring(thedvalue,1,6) AS thedvalue,
				 app_name,score_name,avg(the_score) as the_score,NET_NAME,organ_name
	from bi_panoramic_new_trend 
	where dtype = 'DAY'
	GROUP BY substring(thedvalue,1,6),app_name,score_name,NET_NAME,organ_name;
	
	insert into bi_panoramic_new_trend
	select 'YEAR' as dtype,substring(thedvalue,1,4) AS thedvalue,
				 app_name,score_name,avg(the_score) as the_score,NET_NAME,organ_name
	from bi_panoramic_new_trend
	where dtype = 'MONTH'
	GROUP BY substring(thedvalue,1,4),app_name,score_name,NET_NAME,organ_name;


	-- （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;

	truncate table bi_panoramic_view_safe_trend;
	insert into bi_panoramic_view_safe_trend
	select 
		'DAY' as dtype,
		thedvalue,
		'' as id,
		case b.mm when 1 then '综合分' when 2 then '网络资源明晰度评分' when 3 then '资产脆弱性安全指数得分' when 4 then '网络攻击安全指数得分' 
		when 5 then '边界安全指数得分' when 6 then '应用系统合规性得分' when 7 then '运维风险可控度得分' else '' end as score_name,
		case b.mm when 1 then global_score when 2 then network_resource_score when 3 then asset_vul_score when 4 then network_security_score 
		when 5 then border_security_score when 6 then application_system_score when 7 then operation_risk_score else 0.0 end as the_score
	from 
	(select 
		date_format(insert_time,'%Y%m%d') as thedvalue,
		max(global_score) + 0.0 as global_score,
		max(network_resource_score) + 0.0 as network_resource_score,
		max(asset_vul_score) + 0.0 as asset_vul_score,
		max(network_security_score) + 0.0 as network_security_score,
		max(border_security_score) + 0.0 as border_security_score,
		max(application_system_score) + 0.0 as application_system_score,
		max(operation_risk_score) + 0.0 as operation_risk_score
	from safe_index_score 
	group by date_format(insert_time,'%Y%m%d'),id) a
	cross join
	(select 1 mm 
	union all select 2 
	union all select 3
	union all select 4
	union all select 5
	union all select 6
	union all select 7) b;
	
	insert into bi_panoramic_view_safe_trend
	select 'MONTH' as dtype,substring(thedvalue,1,6) AS thedvalue,
				 '' as id,score_name,avg(the_score) as the_score
	from bi_panoramic_view_safe_trend
	where dtype = 'DAY'
	GROUP BY substring(thedvalue,1,6),score_name;
	
	insert into bi_panoramic_view_safe_trend
	select 'YEAR' as dtype,substring(thedvalue,1,4) AS thedvalue,
				 '' as id,score_name,avg(the_score) as the_score
	from bi_panoramic_view_safe_trend 
	where dtype = 'MONTH'
	GROUP BY substring(thedvalue,1,4),score_name;

	-- （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;

	truncate table bi_panoramic_view_net_attack_trend;
	insert into bi_panoramic_view_net_attack_trend
	select 
		'DAY' as dtype,
		thedvalue,
		case b.mm when 1 then '网络攻击尝试数' when 2 then '网络攻击成功数' when 3 then '网络攻击结果不明数' when 4 then '恶意程序数' else '' end as score_name,
		case b.mm when 1 then network_attack_try_count when 2 then network_attack_success_count when 3 then network_attack_unknown_count when 4 then server_vul_total_count else 0.0 end as the_score
	from 
	(select 
		date_format(insert_time,'%Y%m%d') as thedvalue,
		max(network_attack_try_count) + 0.0 as network_attack_try_count,
		max(network_attack_success_count) + 0.0 as network_attack_success_count,
		max(network_attack_unknown_count) + 0.0 as network_attack_unknown_count,
		max(server_vul_total_count) + 0.0 as server_vul_total_count
	from safe_index_info_1 
	group by date_format(insert_time,'%Y%m%d'),id) a
	cross join
	(select 1 mm 
	union all select 2 
	union all select 3
	union all select 4) b;
	
	insert into bi_panoramic_view_net_attack_trend
	select 'MONTH' as dtype,substring(thedvalue,1,6) AS thedvalue,
				 score_name,avg(the_score) as the_score
	from bi_panoramic_view_net_attack_trend 
	where dtype = 'DAY'
	GROUP BY substring(thedvalue,1,6),score_name;
	
	insert into bi_panoramic_view_net_attack_trend
	select 'YEAR' as dtype,substring(thedvalue,1,4) AS thedvalue,
				 score_name,avg(the_score) as the_score
	from bi_panoramic_view_net_attack_trend 
	where dtype = 'MONTH'
	GROUP BY substring(thedvalue,1,4),score_name;

	-- （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;


	truncate table bi_panoramic_view_sb_ld_trend;
	insert into bi_panoramic_view_sb_ld_trend
	select 
		'DAY' as dtype,
		thedvalue,
		'' as app_name,
		case b.mm when 1 then '安全设备漏洞' when 2 then '网络设备漏洞' when 3 then '终端PC漏洞' when 4 then '服务器漏洞' else '' end as score_name,
		case b.mm when 1 then security_device_vul_total_count when 2 then network_device_vul_total_count when 3 then terminal_vul_total_count when 4 then server_vul_total_count else 0.0 end as the_score
	from 
	(select 
		date_format(insert_time,'%Y%m%d') as thedvalue,
		max(security_device_vul_total_count) + 0.0 as security_device_vul_total_count,
		max(network_device_vul_total_count) + 0.0 as network_device_vul_total_count,
		max(terminal_vul_total_count) + 0.0 as terminal_vul_total_count,
		max(server_vul_total_count) + 0.0 as server_vul_total_count
	from safe_index_info_1 
	group by date_format(insert_time,'%Y%m%d'),id) a
	cross join
	(select 1 mm 
	union all select 2 
	union all select 3
	union all select 4) b;
	
	insert into bi_panoramic_view_sb_ld_trend
	select 'MONTH' as dtype,substring(thedvalue,1,6) AS thedvalue,
				 '' as app_name,score_name,avg(the_score) as the_score
	from bi_panoramic_view_sb_ld_trend 
	where dtype = 'DAY'
	GROUP BY substring(thedvalue,1,6),score_name;
	
	insert into bi_panoramic_view_sb_ld_trend
	select 'YEAR' as dtype,substring(thedvalue,1,4) AS thedvalue,
				 '' as app_name,score_name,avg(the_score) as the_score
	from bi_panoramic_view_sb_ld_trend 
	where dtype = 'MONTH'
	GROUP BY substring(thedvalue,1,4),score_name;

	-- （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;

	truncate table bi_panoramic_view_shaung_yin_a4_trend;
	insert into bi_panoramic_view_shaung_yin_a4_trend
	select 
		'DAY' as dtype,
		thedvalue,
		case b.mm when 1 then '终端PC' when 2 then '终端PC' when 3 then '终端PC' when 4 then '' 
		when 5 then '' when 6 then '' when 7 then '' when 8 then '' else '' end as id,
		case b.mm when 1 then '入网数' when 2 then '准入数' when 3 then '审计数' when 4 then '安全设备双因子认证' 
		when 5 then '网络设备双因子认证' when 6 then '服务器双因子认证' when 7 then '服务器4A' when 8 then '应用4A' else '' end as score_name,
		case b.mm when 1 then dim1 when 2 then dim2 when 3 then dim3 when 4 then dim4 
		when 5 then dim5 when 6 then dim6 when 7 then dim7 when 8 then dim8 else 0.0 end as the_score
	from 
	(select 
		date_format(insert_time,'%Y%m%d') as thedvalue,
		max(terminal_count) + 0.0 as dim1,
		max(terminal_admittance_count) + 0.0 as dim2,
		max(terminal_audit_count) + 0.0 as dim3,
		max(1.0 - security_device_unused_eactor_percent) + 0.0 as dim4,
		max(1.0 - network_device_unused_factor_percent) + 0.0 as dim5,
		max(1.0 - server_unused_factor_percent) + 0.0 as dim6,
		max(1.0 - case when server_resource_failure_count + server_resource_success_count = 0 then 0.0 else server_resource_failure_count/(server_resource_failure_count + server_resource_success_count) * 1.0 end) + 0.0 as dim7,
		max(1.0 - application_unused_factor_percent) as dim8
	from safe_index_info_1 
	group by date_format(insert_time,'%Y%m%d'),id) a
	cross join
	(select 1 mm 
	union all select 2 
	union all select 3
	union all select 4
	union all select 5
	union all select 6
	union all select 7
	union all select 8) b;
	
	insert into bi_panoramic_view_shaung_yin_a4_trend
	select 'MONTH' as dtype,substring(thedvalue,1,6) AS thedvalue,
				 id,score_name,avg(the_score) as the_score
	from bi_panoramic_view_shaung_yin_a4_trend 
	where dtype = 'DAY'
	GROUP BY substring(thedvalue,1,6),score_name,id;
	
	insert into bi_panoramic_view_shaung_yin_a4_trend
	select 'YEAR' as dtype,substring(thedvalue,1,4) AS thedvalue,
				 id,score_name,avg(the_score) as the_score
	from bi_panoramic_view_shaung_yin_a4_trend
	where dtype = 'MONTH'
	GROUP BY substring(thedvalue,1,4),score_name,id;

	


-- （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;

truncate table bi_work_flow_group_by_time;
insert into bi_work_flow_group_by_time
	select 
	  	'HOUR' as dtype,
		date_format(a.start_time,'%Y%m%d%H') as thedvalue,     --  str_to_date
		a.process_type_name,
	 	a.serve_area,
		c.display_name as serve_department,
		d.manufacturer_name as serve_firm,
		count(*) as total_works,
		sum(case when end_time is not null then 1 else 0 end) as total_works_end,
		sum(case when end_time is not null then 0 else 1 end) as total_works_noend,
	 	sum(case when time_out =1 then 1 else 0 end) as total_works_notimeout,
	 	sum(case when time_out =0 then 1 else 0 end) as total_works_timeout,
		sum(time_consuming) as total_time_consuming,
	 	avg(time_consuming + 0.0) as avg_time_consuming,
	  	avg(satisfaction + 0.0) as satisfaction,
		b.app_id
	from icm_bpm.t_work_flow a
	left join icm_bpm.t_work_resource_real b
	on a.id = b.work_id
	left join icm.sys_org c
	on a.serve_department = c.org_code
	left join icm.cfg_manufacturer d
	on a.handler_firm = d.id
	group by 
		date_format(a.start_time,'%Y%m%d%H'),
		a.process_type_name,
		a.serve_area,
		c.display_name,
		d.manufacturer_name,b.app_id;
	
	insert into bi_work_flow_group_by_time
	select 
		'DAY' as dtype,substring(thedvalue,1,8) AS thedvalue,
		process_type_name,
	 	serve_area,
		serve_department,
		serve_firm,
		sum(total_works) as total_works,
		sum(total_works_end) as total_works_end,
		sum(total_works_noend) as total_works_noend,
	 	sum(total_works_notimeout) as total_works_notimeout,
	 	sum(total_works_timeout) as total_works_timeout,
		sum(total_time_consuming) as total_time_consuming,
	 	avg(avg_time_consuming + 0.0) as avg_time_consuming,
	  	avg(satisfaction + 0.0) as satisfaction,
		app_id
	from bi_work_flow_group_by_time
	where dtype = 'HOUR'
	group by 
		substring(thedvalue,1,8),
		process_type_name,
		serve_area,
		serve_department,
		serve_firm,app_id;
		
	insert into bi_work_flow_group_by_time
	select 
		'MONTH' as dtype,substring(thedvalue,1,6) AS thedvalue,
		process_type_name,
	 	serve_area,
		serve_department,
		serve_firm,
		sum(total_works) as total_works,
		sum(total_works_end) as total_works_end,
		sum(total_works_noend) as total_works_noend,
	 	sum(total_works_notimeout) as total_works_notimeout,
	 	sum(total_works_timeout) as total_works_timeout,
		sum(total_time_consuming) as total_time_consuming,
	 	avg(avg_time_consuming + 0.0) as avg_time_consuming,
	  	avg(satisfaction + 0.0) as satisfaction,
		app_id
	from bi_work_flow_group_by_time
	where dtype = 'DAY'
	group by 
		substring(thedvalue,1,6),
		process_type_name,
		serve_area,
		serve_department,
		serve_firm,app_id;
	
	insert into bi_work_flow_group_by_time
	select 
		'YEAR' as dtype,substring(thedvalue,1,4) AS thedvalue,
		process_type_name,
	 	serve_area,
		serve_department,
		serve_firm,
		sum(total_works) as total_works,
		sum(total_works_end) as total_works_end,
		sum(total_works_noend) as total_works_noend,
	 	sum(total_works_notimeout) as total_works_notimeout,
	 	sum(total_works_timeout) as total_works_timeout,
		sum(total_time_consuming) as total_time_consuming,
	 	avg(avg_time_consuming + 0.0) as avg_time_consuming,
	  	avg(satisfaction + 0.0) as satisfaction,
		app_id
	from bi_work_flow_group_by_time
	where dtype = 'MONTH'
	group by 
		substring(thedvalue,1,4),
		process_type_name,
		serve_area,
		serve_department,
		serve_firm,app_id;	


	-- （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;


truncate table bi_app_stat_group_transposed;
	insert into bi_app_stat_group_transposed
	select 
		thedvalue,itm_hardware_resources_id,ITM_HARDWARE_RESOURCES_NAME,
		case b.mm when 1 then 'CPU平均利用率' when 2 then 'CPU峰值利用率' when 3 then '内存平均利用率' when 4 then '内存峰值利用率' 
		when 5 then '磁盘利用率' when 6 then '带宽平均利用率' when 7 then '带宽峰值利用率' when 8 then '交换机错包率'  when 9 then '交换机丢包率' else '' end as col_name,
		case b.mm when 1 then dim1 when 2 then dim2 when 3 then dim3 when 4 then dim4 
		when 5 then dim5 when 6 then dim6 when 7 then dim7 when 8 then dim8 when 9 then dim9 else 0.0 end as col_value
	from (
	select  
		thedvalue,itm_hardware_resources_id,ITM_HARDWARE_RESOURCES_NAME,
		avg(IND_VALUE_Network_Cpurate) as dim1,max(IND_VALUE_Network_Cpurate_High) as dim2,avg(IND_VALUE_Network_Memrate) as dim3,avg(IND_VALUE_Network_Memrate_High) as dim4,
		avg(IND_VALUE_HOST_DISK_UTILIZATION) as dim5,avg(Band_widthrate) as dim6,max(Bandwidthrate_High) as dim7,avg(Network_PacketLossrate) as dim8,avg(Network_PacketErrorrate) as dim9
	from bi_app_stat_group_by_time 
	where dtype = 'DAY' and QGorZG = '最高'
	group by thedvalue,itm_hardware_resources_id,ITM_HARDWARE_RESOURCES_NAME) a
	cross join
	(select 1 mm 
	union all select 2 
	union all select 3
	union all select 4
	union all select 5
	union all select 6
	union all select 7
	union all select 8
	union all select 9) b;



	-- （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();

delete from bi_app_tsc_stat where dtype = 'YEAR';
insert into bi_app_tsc_stat
select 'YEAR' as dtype,substring(thedvalue,1,4) as thedvalue,app_id,app_name,dim,dim_tuli,sum(dim_value) as dim_value,dim_value_unit,value_calculate,sum(dim_tuli_value) as dim_tuli_value
from bi_app_tsc_stat where dtype = 'MONTH' and value_calculate = 'SUM'
group by substring(thedvalue,1,4),  app_id, app_name, dim,dim_tuli,dim_value_unit,value_calculate
union all
select 'YEAR' as dtype,substring(thedvalue,1,4) as thedvalue,app_id,app_name,dim,dim_tuli,avg(dim_value) as dim_value,dim_value_unit,value_calculate,avg(dim_tuli_value) as dim_tuli_value
from bi_app_tsc_stat where dtype = 'MONTH' and value_calculate = 'AVG'
group by substring(thedvalue,1,4), app_id, app_name, dim,dim_tuli,dim_value_unit,value_calculate
union all 
select 'YEAR' as dtype,substring(thedvalue,1,4) as thedvalue,app_id,app_name,dim,dim_tuli,max(dim_value) as dim_value,dim_value_unit,value_calculate,max(dim_tuli_value) as dim_tuli_value
from bi_app_tsc_stat where dtype = 'MONTH' and value_calculate = 'MAX'
group by substring(thedvalue,1,4), app_id, app_name, dim,dim_tuli,dim_value_unit,value_calculate
union all 
select 'YEAR' as dtype,substring(thedvalue,1,4) as thedvalue,app_id,app_name,dim,dim_tuli,min(dim_value) as dim_value,dim_value_unit,value_calculate ,min(dim_tuli_value) as dim_tuli_value
from bi_app_tsc_stat where dtype = 'MONTH' and value_calculate = 'MIN'
group by substring(thedvalue,1,4), app_id, app_name, dim,dim_tuli,dim_value_unit,value_calculate;



-- （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;

truncate table bi_workflow;
insert into bi_workflow
select '',a.id as a_id,theme,proposer_phone,proposer_name,proposer,serial_number,process_key,serve_type,process_id,parent_process_type_name,process_type,setting_data,process_type_name,parent_process_type,form_key,form_data,urg_degree,start_account,start_account_id,start_account_phone,expect_time,end_time,start_time,process_status,handler,next_handler,attention,node_id,press,opinion,result,form_id,proc_def_id,relevance,handler_name,next_handler_name,node_type,a.update_time,appraise,failure_type,satisfaction,serve_department,serve_firm,serve_area,time_consuming,
time_out,performance,proposer_serve_department,proposer_serve_firm,proposer_serve_area,start_user,order_desc,cocall,handler_firm,b.id as b_id,work_id,alarm_uuid,alarm_type,object_id,app_id,ihr.ITM_HARDWARE_RESOURCES_NAME c
from icm_bpm.t_work_flow a
left join icm_bpm.t_work_resource_real b on a.id = b.work_id
left join itm_hardware_resources ihr  on ihr.itm_hardware_resources_id = b.object_id;

	-- 结构化数据容量分析

-- 建表语句

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;

	truncate table bi_structured_data_capacity;
	insert into bi_structured_data_capacity
	select 
		'DAY' as dtype,
		date_format(IND_VALUE_TIME,'%Y%m%d') as thedvalue,
		adds.NETID,
		d.name,
		adds.ORGAN_ID,
		po.short_name,
		SUM(adds.totalsize)
	from app_db_detail_stat adds 
	left join (select value,name from m_com_dict where dict_index='itm_resources_netcode') d on d.value=adds.NETID
	left join pub_organ po on po.ID=adds.ORGAN_ID
	group by date_format(IND_VALUE_TIME,'%Y%m%d'),adds.NETID,adds.ORGAN_ID,d.name,po.short_name;
	
	insert into bi_structured_data_capacity
	select 'MONTH' as dtype,substring(thedvalue,1,6) AS thedvalue,
		NET_ID,NET_NAME, ORGAN_ID,ORGAN_NAME, SUM(totalsize)
	from bi_structured_data_capacity 
	where dtype = 'DAY'
	GROUP BY substring(thedvalue,1,6),NET_ID,NET_NAME,ORGAN_ID,ORGAN_NAME;

	insert into bi_structured_data_capacity
	select 'YEAR' as dtype,substring(thedvalue,1,4) AS thedvalue,
		NET_ID,NET_NAME, ORGAN_ID,ORGAN_NAME, SUM(totalsize)
	from bi_structured_data_capacity 
	where dtype = 'MONTH'
	GROUP BY substring(thedvalue,1,4),NET_ID,NET_NAME,ORGAN_ID,ORGAN_NAME;


	-- 存储使用率占比分析

-- 建表语句

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;

	truncate table bi_storage_usage_rate;
	insert into bi_storage_usage_rate
	select 
		'DAY' as dtype,
		date_format(IND_VALUE_TIME,'%Y%m%d') as thedvalue,
		asds.NETID,
		d.name,
		asds.ORGAN_ID,
		po.short_name,
		SUM(asds.IND_VALUE_StorageDevice_UsedSpace),
		SUM(asds.IND_VALUE_StorageDevice_TotalSpace),
		SUM(asds.IND_VALUE_StorageDevice_UsedSpace) / SUM(asds.IND_VALUE_StorageDevice_TotalSpace) as 'usage_rate'
	from app_storage_day_stat asds
	left join (select value,name from m_com_dict where dict_index='itm_resources_netcode') d on d.value=asds.NETID
	left join pub_organ po on po.ID=asds.ORGAN_ID
	group by date_format(IND_VALUE_TIME,'%Y%m%d'),asds.NETID,asds.ORGAN_ID,d.name,po.short_name;
	
	insert into bi_storage_usage_rate
	select 'MONTH' as dtype,substring(thedvalue,1,6) AS thedvalue,
		NET_ID, NET_NAME,ORGAN_ID,ORGAN_NAME, SUM(used), SUM(total), SUM(used)/SUM(total)
	from bi_storage_usage_rate 
	where dtype = 'DAY'
	GROUP BY substring(thedvalue,1,6),NET_ID,ORGAN_ID,NET_NAME,ORGAN_NAME;

	insert into bi_storage_usage_rate
	select 'YEAR' as dtype,substring(thedvalue,1,4) AS thedvalue,
		NET_ID, NET_NAME,ORGAN_ID,ORGAN_NAME, SUM(used), SUM(total), SUM(used)/SUM(total)
	from bi_storage_usage_rate 
	where dtype = 'MONTH'
	GROUP BY substring(thedvalue,1,4),NET_ID,ORGAN_ID,NET_NAME,ORGAN_NAME;


	-- 应用数据分析

-- 建表语句

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;

	truncate table bi_app_data_analysis;
	insert into bi_app_data_analysis
	select 
		'MONTH' as dtype,
		date_format(visit_time,'%Y%m') as thedvalue,
		ia.ITM_NETGROUP_ID,
		d.name,
		arl.organ_id,
		po.short_name,
		arl.app_id,
		ia.itm_app_name,
		ia.PRIORITY,
		sum(arl.visit_count),
		sum(arl.user_count),
		0.0
	from app_region_log arl
	left join itm_app ia on ia.ITM_APP_ID=arl.app_id
	left join (select value,name from m_com_dict where dict_index='itm_resources_netcode') d on d.value=ia.ITM_NETGROUP_ID
	left join pub_organ po on po.id=CAST(arl.organ_id AS CHAR)
	group by date_format(visit_time,'%Y%m'),ia.ITM_NETGROUP_ID,arl.organ_id,arl.app_id,ia.itm_app_name,ia.PRIORITY,d.name,po.short_name;

	insert into bi_app_data_analysis
	select 
		'MONTH' as dtype,
		replace(aadq.`month`,'-','') as thedvalue,
		ia.ITM_NETGROUP_ID,
		d.name,
		ia.organ_id,
		po.name,
		ia.ITM_APP_ID,
		ia.itm_app_name,
		ia.PRIORITY,
		0,
		0,
		sum(aadq.quality)
	from t_asset_app_data_quality aadq
	left join itm_app ia on ia.ITM_APP_ID=aadq.app_id
	left join (select value,name from m_com_dict where dict_index='itm_resources_netcode') d on d.value=ia.ITM_NETGROUP_ID
	left join pub_organ po on po.ID=ia.organ_id
	group by replace(aadq.`month`,'-',''),ia.ITM_NETGROUP_ID,ia.organ_id,ia.ITM_APP_ID,ia.itm_app_name,ia.PRIORITY,d.name,po.name;

	insert into bi_app_data_analysis
	select 'YEAR' as dtype,substring(thedvalue,1,4) AS thedvalue,
		NET_ID,NET_NAME,ORGAN_ID,ORGAN_NAME,app_id,app_name,priority,sum(visit_count),sum(user_count),sum(quality)
	from bi_app_data_analysis 
	where dtype = 'MONTH'
	GROUP BY substring(thedvalue,1,4),NET_ID,NET_NAME,ORGAN_ID,ORGAN_NAME,app_id,app_name,priority;




	-- 应用备份覆盖率分析

-- 建表语句
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;

	truncate table bi_app_backup_coverage;
	insert into bi_app_backup_coverage
	select 
		'MONTH' as dtype,
		replace(abi.`month`,'-','') as thedvalue,
		ia.ITM_NETGROUP_ID,
		d.name,
		ia.ORGAN_ID,
		po.short_name,
		abi.app_id,
		ia.ITM_APP_NAME,
		ia.PRIORITY,
		sum(abi.bak_size),
		sum(abi.total_size),
		IF(sum(abi.total_size)<>0, sum(abi.bak_size) / sum(abi.total_size), 0)
	from t_asset_app_bak_info abi
	left join itm_app ia on ia.ITM_APP_ID=abi.app_id
	left join (select value,name from m_com_dict where dict_index='itm_resources_netcode') d on d.value=ia.ITM_NETGROUP_ID
	left join pub_organ po on po.id=ia.ORGAN_ID
	group by replace(abi.`month`,'-',''),ia.ITM_NETGROUP_ID,ia.ORGAN_ID,abi.app_id,ia.ITM_APP_NAME,ia.PRIORITY,d.name,po.short_name;

	insert into bi_app_backup_coverage
	select 'YEAR' as dtype,substring(thedvalue,1,4) AS thedvalue,
		NET_ID,NET_NAME,ORGAN_ID,ORGAN_NAME,app_id,app_name,priority,
		sum(bak_size),
		sum(total_size),
		IF(sum(total_size)<>0, sum(bak_size) / sum(total_size), 0)
	from bi_app_backup_coverage 
	where dtype = 'MONTH'
	GROUP BY substring(thedvalue,1,4),NET_ID,NET_NAME,ORGAN_ID,ORGAN_NAME,app_id,app_name,priority;
	
	
	
	
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;
insert into bi_app_resource_group_by_time
select 
	'HOUR' as dtype,
	date_format(b.CREATE_TIME,'%Y%m%d%H') as thedvalue,
	date_format(b.CREATE_TIME,'%H') as dhour,
	a.*,

case a.stateflag when 0 then '已应用' else '未应用' end as stateflag1,
case a.PRIORITY WHEN 'low' then '一般应用' when 'important' then '重要应用' when 'high' then '核心应用' else '未知' end as PRIORITY1,
d.name as app_type_name, 
e.net_name as net_name, 
	
	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,
	rand(10) * 100 as Band_widthrate,
	case when rand(10) * 100 + rand(10) * 10 > 100 then 100 else rand(10) * 100 + rand(10) * 10 end as Bandwidthrate_High,
	rand() * 100 as Network_delay,
	rand() * 10000 as Network_flow,
	rand() as Network_PacketErrorrate,
	rand() as Network_PacketLossrate,
	rand() * 10000 as Write_rate,
	rand() as network_Ifinrate,
	rand() as network_Ifortrate,
	0.0 * rand(20) * 10 as Device_total,
	0.0 * rand(20) * 10 as Device_used,
	0.0 as Device_UTILIZATION
from itm_app a
inner join (select * from bi_itm_hardware_resources where servicename !='') c
on a.ITM_APP_NAME = c.servicename
-- inner join bi_itm_hardware_resources f
-- on a.ruuid = c.relatedappid
inner join app_network_stat  b
on c.itm_hardware_resources_id = b.INT_ID
inner JOIN m_com_dict d on a.app_type=d.value
inner JOIN itm_organ_net e on e.NET_ID = a.ITM_NETGROUP_ID  
GROUP BY a.ITM_APP_NAME,date_format(b.CREATE_TIME,'%Y%m%d%H'),date_format(b.CREATE_TIME,'%H'),net_name

UNION ALL
select 
	'HOUR' as dtype,
	date_format(b.CREATE_TIME,'%Y%m%d%H') as thedvalue,
	date_format(b.CREATE_TIME,'%H') as dhour,
	a.*,

case a.stateflag when 0 then '已应用' else '未应用' end as stateflag1,
case a.PRIORITY WHEN 'low' then '一般应用' when 'important' then '重要应用' when 'high' then '核心应用' else '未知' end as PRIORITY1,
d.name as app_type_name, 
e.net_name as net_name, 
	
	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,
	rand(10) * 100 as Band_widthrate,
	case when rand(10) * 100 + rand(10) * 10 > 100 then 100 else rand(10) * 100 + rand(10) * 10 end as Bandwidthrate_High,
	rand() * 100 as Network_delay,
	rand() * 10000 as Network_flow,
	rand() as Network_PacketErrorrate,
	rand() as Network_PacketLossrate,
	rand() * 10000 as Write_rate,
	rand() as network_Ifinrate,
	rand() as network_Ifortrate,
	0.0 * rand(20) * 10 as Device_total,
	0.0 * rand(20) * 10 as Device_used,
	0.0 as Device_UTILIZATION
from itm_app a
inner join (select * from bi_itm_hardware_resources where servicename = '') c
on a.ruuid = c.relatedappid
-- inner join bi_itm_hardware_resources f
-- on a.ruuid = c.relatedappid
inner join app_network_stat  b
on c.itm_hardware_resources_id = b.INT_ID
inner JOIN m_com_dict d on a.app_type=d.value
inner JOIN itm_organ_net e on e.NET_ID = a.ITM_NETGROUP_ID  
GROUP BY a.ITM_APP_NAME,date_format(b.CREATE_TIME,'%Y%m%d%H'),date_format(b.CREATE_TIME,'%H'),net_name

union all
select 
	'DAY' as dtype,
	date_format(b.CREATE_TIME,'%Y%m%d') as thedvalue,
	date_format(b.CREATE_TIME,'%H') as dhour,
	a.*,
case a.stateflag when 0 then '已应用' else '未应用' end as stateflag1,
case a.PRIORITY WHEN 'low' then '一般应用' when 'important' then '重要应用' when 'high' then '核心应用' else '未知' end as PRIORITY1,
d.name as app_type_name, 
e.net_name as net_name, 
	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,
	rand(10) * 100 as Band_widthrate,
	case when rand(10) * 100 + rand(10) * 10 > 100 then 100 else rand(10) * 100 + rand(10) * 10 end as Bandwidthrate_High,
	rand() * 100 as Network_delay,
	rand() * 10000 as Network_flow,
	rand() as Network_PacketErrorrate,
	rand() as Network_PacketLossrate,
	rand() * 10000 as Write_rate,
	rand() as network_Ifinrate,
	rand() as network_Ifortrate,
	0.0 * rand(20) * 10 as Device_total,
	0.0 * rand(20) * 10 as Device_used,
	0.0 as Device_UTILIZATION
from itm_app a
inner join (select * from bi_itm_hardware_resources where servicename !='') c
on a.ITM_APP_NAME = c.servicename
inner join app_network_stat  b
on c.itm_hardware_resources_id = b.INT_ID
LEFT JOIN m_com_dict d on a.app_type=d.value
LEFT JOIN itm_organ_net e on e.NET_ID = a.ITM_NETGROUP_ID  
WHERE d.dict_index='app_type'

GROUP BY a.ITM_APP_NAME,date_format(b.CREATE_TIME,'%Y%m%d'),date_format(b.CREATE_TIME,'%H')

union all
select 
	'DAY' as dtype,
	date_format(b.CREATE_TIME,'%Y%m%d') as thedvalue,
	date_format(b.CREATE_TIME,'%H') as dhour,
	a.*,

case a.stateflag when 0 then '已应用' else '未应用' end as stateflag1,
case a.PRIORITY WHEN 'low' then '一般应用' when 'important' then '重要应用' when 'high' then '核心应用' else '未知' end as PRIORITY1,
d.name as app_type_name, 
e.net_name as net_name, 
	
	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,
	rand(10) * 100 as Band_widthrate,
	case when rand(10) * 100 + rand(10) * 10 > 100 then 100 else rand(10) * 100 + rand(10) * 10 end as Bandwidthrate_High,
	rand() * 100 as Network_delay,
	rand() * 10000 as Network_flow,
	rand() as Network_PacketErrorrate,
	rand() as Network_PacketLossrate,
	rand() * 10000 as Write_rate,
	rand() as network_Ifinrate,
	rand() as network_Ifortrate,
	0.0 * rand(20) * 10 as Device_total,
	0.0 * rand(20) * 10 as Device_used,
	0.0 as Device_UTILIZATION
from itm_app a
inner join (select * from bi_itm_hardware_resources where servicename = '') c
on a.ruuid = c.relatedappid
-- inner join bi_itm_hardware_resources f
-- on a.ruuid = c.relatedappid
inner join app_network_stat  b
on c.itm_hardware_resources_id = b.INT_ID
inner JOIN m_com_dict d on a.app_type=d.value
inner JOIN itm_organ_net e on e.NET_ID = a.ITM_NETGROUP_ID  
GROUP BY a.ITM_APP_NAME,date_format(b.CREATE_TIME,'%Y%m%d'),date_format(b.CREATE_TIME,'%H'),net_name





union all
select 
	'WEEK' as dtype,
	date_format(b.CREATE_TIME,'%Y%u') as thedvalue,
	date_format(b.CREATE_TIME,'%H') as dhour,
	a.*,
  case a.stateflag when 0 then '已应用' else '未应用' end as stateflag1,
  case a.PRIORITY WHEN 'low' then '一般应用' when 'important' then '重要应用' when 'high' then '核心应用' else '未知' end as PRIORITY1,
  d.name as app_type_name, 
  e.net_name as net_name, 
	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,
	rand(10) * 100 as Band_widthrate,
	case when rand(10) * 100 + rand(10) * 10 > 100 then 100 else rand(10) * 100 + rand(10) * 10 end as Bandwidthrate_High,
	rand() * 100 as Network_delay,
	rand() * 10000 as Network_flow,
	rand() as Network_PacketErrorrate,
	rand() as Network_PacketLossrate,
	rand() * 10000 as Write_rate,
	rand() as network_Ifinrate,
	rand() as network_Ifortrate,
	0.0 * rand(20) * 10 as Device_total,
	0.0 * rand(20) * 10 as Device_used,
	0.0 as Device_UTILIZATION
from itm_app a
inner join (select * from bi_itm_hardware_resources where servicename != '') c
on a.ITM_APP_NAME = c.servicename
inner join app_network_stat  b
on c.itm_hardware_resources_id = b.INT_ID
LEFT JOIN m_com_dict d on a.app_type=d.value
LEFT JOIN itm_organ_net e on e.NET_ID = a.ITM_NETGROUP_ID  
WHERE d.dict_index='app_type'

GROUP BY a.ITM_APP_NAME,date_format(b.CREATE_TIME,'%Y%u'),date_format(b.CREATE_TIME,'%H')
union all
select 
	'WEEK' as dtype,
	date_format(b.CREATE_TIME,'%Y%u') as thedvalue,
	date_format(b.CREATE_TIME,'%H') as dhour,
	a.*,
  case a.stateflag when 0 then '已应用' else '未应用' end as stateflag1,
  case a.PRIORITY WHEN 'low' then '一般应用' when 'important' then '重要应用' when 'high' then '核心应用' else '未知' end as PRIORITY1,
  d.name as app_type_name, 
  e.net_name as net_name, 
	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,
	rand(10) * 100 as Band_widthrate,
	case when rand(10) * 100 + rand(10) * 10 > 100 then 100 else rand(10) * 100 + rand(10) * 10 end as Bandwidthrate_High,
	rand() * 100 as Network_delay,
	rand() * 10000 as Network_flow,
	rand() as Network_PacketErrorrate,
	rand() as Network_PacketLossrate,
	rand() * 10000 as Write_rate,
	rand() as network_Ifinrate,
	rand() as network_Ifortrate,
	0.0 * rand(20) * 10 as Device_total,
	0.0 * rand(20) * 10 as Device_used,
	0.0 as Device_UTILIZATION
from itm_app a
inner join (select * from bi_itm_hardware_resources where servicename = '') c
on a.ruuid = c.relatedappid
inner join app_network_stat  b
on c.itm_hardware_resources_id = b.INT_ID
LEFT JOIN m_com_dict d on a.app_type=d.value
LEFT JOIN itm_organ_net e on e.NET_ID = a.ITM_NETGROUP_ID  
WHERE d.dict_index='app_type'
GROUP BY a.ITM_APP_NAME,date_format(b.CREATE_TIME,'%Y%u'),date_format(b.CREATE_TIME,'%H')



union all
select 
	'MONTH' as dtype,
	date_format(b.CREATE_TIME,'%Y%m') as thedvalue,
	date_format(b.CREATE_TIME,'%H') as dhour,
	a.*,
	case a.stateflag when 0 then '已应用' else '未应用' end as stateflag1,
  case a.PRIORITY WHEN 'low' then '一般应用' when 'important' then '重要应用' when 'high' then '核心应用' else '未知' end as PRIORITY1,
  d.name as app_type_name, 
  e.net_name as net_name, 
	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,
	rand(10) * 100 as Band_widthrate,
	case when rand(10) * 100 + rand(10) * 10 > 100 then 100 else rand(10) * 100 + rand(10) * 10 end as Bandwidthrate_High,
	rand() * 100 as Network_delay,
	rand() * 10000 as Network_flow,
	rand() as Network_PacketErrorrate,
	rand() as Network_PacketLossrate,
	rand() * 10000 as Write_rate,
	rand() as network_Ifinrate,
	rand() as network_Ifortrate,
	0.0 * rand(20) * 10 as Device_total,
	0.0 * rand(20) * 10 as Device_used,
	0.0 as Device_UTILIZATION
from itm_app a
inner join (select * from bi_itm_hardware_resources where servicename != '') c
on a.ITM_APP_NAME = c.servicename
inner join app_network_stat  b
on c.itm_hardware_resources_id = b.INT_ID
LEFT JOIN m_com_dict d on a.app_type=d.value
LEFT JOIN itm_organ_net e on e.NET_ID = a.ITM_NETGROUP_ID  
WHERE d.dict_index='app_type'

GROUP BY a.ITM_APP_NAME,date_format(b.CREATE_TIME,'%Y%m'),date_format(b.CREATE_TIME,'%H')


union all
select 
	'MONTH' as dtype,
	date_format(b.CREATE_TIME,'%Y%m') as thedvalue,
	date_format(b.CREATE_TIME,'%H') as dhour,
	a.*,
	case a.stateflag when 0 then '已应用' else '未应用' end as stateflag1,
  case a.PRIORITY WHEN 'low' then '一般应用' when 'important' then '重要应用' when 'high' then '核心应用' else '未知' end as PRIORITY1,
  d.name as app_type_name, 
  e.net_name as net_name, 
	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,
	rand(10) * 100 as Band_widthrate,
	case when rand(10) * 100 + rand(10) * 10 > 100 then 100 else rand(10) * 100 + rand(10) * 10 end as Bandwidthrate_High,
	rand() * 100 as Network_delay,
	rand() * 10000 as Network_flow,
	rand() as Network_PacketErrorrate,
	rand() as Network_PacketLossrate,
	rand() * 10000 as Write_rate,
	rand() as network_Ifinrate,
	rand() as network_Ifortrate,
	0.0 * rand(20) * 10 as Device_total,
	0.0 * rand(20) * 10 as Device_used,
	0.0 as Device_UTILIZATION
from itm_app a
inner join (select * from bi_itm_hardware_resources where servicename = '') c
on a.ruuid = c.relatedappid
inner join app_network_stat  b
on c.itm_hardware_resources_id = b.INT_ID
LEFT JOIN m_com_dict d on a.app_type=d.value
LEFT JOIN itm_organ_net e on e.NET_ID = a.ITM_NETGROUP_ID  
WHERE d.dict_index='app_type'

GROUP BY a.ITM_APP_NAME,date_format(b.CREATE_TIME,'%Y%m'),date_format(b.CREATE_TIME,'%H')

union all
select 
	'YEAR' as dtype,
	date_format(b.CREATE_TIME,'%Y') as thedvalue,
	date_format(b.CREATE_TIME,'%H') as dhour,
	a.*,
	case a.stateflag when 0 then '已应用' else '未应用' end as stateflag1,
  case a.PRIORITY WHEN 'low' then '一般应用' when 'important' then '重要应用' when 'high' then '核心应用' else '未知' end as PRIORITY1,
  d.name as app_type_name, 
  e.net_name as net_name, 
	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,
	rand(10) * 100 as Band_widthrate,
	case when rand(10) * 100 + rand(10) * 10 > 100 then 100 else rand(10) * 100 + rand(10) * 10 end as Bandwidthrate_High,
	rand() * 100 as Network_delay,
	rand() * 10000 as Network_flow,
	rand() as Network_PacketErrorrate,
	rand() as Network_PacketLossrate,
	rand() * 10000 as Write_rate,
	rand() as network_Ifinrate,
	rand() as network_Ifortrate,
	0.0 * rand(20) * 10 as Device_total,
	0.0 * rand(20) * 10 as Device_used,
	0.0 as Device_UTILIZATION
from itm_app a
inner join (select * from bi_itm_hardware_resources where servicename != '') c
on a.ITM_APP_NAME = c.servicename
inner join app_network_stat  b
on c.itm_hardware_resources_id = b.INT_ID
LEFT JOIN m_com_dict d on a.app_type=d.value
LEFT JOIN itm_organ_net e on e.NET_ID = a.ITM_NETGROUP_ID  
WHERE d.dict_index='app_type'

GROUP BY a.ITM_APP_NAME,date_format(b.CREATE_TIME,'%Y'),date_format(b.CREATE_TIME,'%H')
union ALL

select 
	'YEAR' as dtype,
	date_format(b.CREATE_TIME,'%Y') as thedvalue,
	date_format(b.CREATE_TIME,'%H') as dhour,
	a.*,
	case a.stateflag when 0 then '已应用' else '未应用' end as stateflag1,
  case a.PRIORITY WHEN 'low' then '一般应用' when 'important' then '重要应用' when 'high' then '核心应用' else '未知' end as PRIORITY1,
  d.name as app_type_name, 
  e.net_name as net_name, 
	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,
	rand(10) * 100 as Band_widthrate,
	case when rand(10) * 100 + rand(10) * 10 > 100 then 100 else rand(10) * 100 + rand(10) * 10 end as Bandwidthrate_High,
	rand() * 100 as Network_delay,
	rand() * 10000 as Network_flow,
	rand() as Network_PacketErrorrate,
	rand() as Network_PacketLossrate,
	rand() * 10000 as Write_rate,
	rand() as network_Ifinrate,
	rand() as network_Ifortrate,
	0.0 * rand(20) * 10 as Device_total,
	0.0 * rand(20) * 10 as Device_used,
	0.0 as Device_UTILIZATION
from itm_app a
inner join (select * from bi_itm_hardware_resources where servicename = '') c
on a.ruuid = c.relatedappid
inner join app_network_stat  b
on c.itm_hardware_resources_id = b.INT_ID
LEFT JOIN m_com_dict d on a.app_type=d.value
LEFT JOIN itm_organ_net e on e.NET_ID = a.ITM_NETGROUP_ID  
WHERE d.dict_index='app_type'

GROUP BY a.ITM_APP_NAME,date_format(b.CREATE_TIME,'%Y'),date_format(b.CREATE_TIME,'%H');



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;

insert into bi_app_access_stat
SELECT
	'HOUR' AS dtype,
  DATE_FORMAT(A.time_mark,'%Y%m%d%H') as thedvalue,
	B.ITM_NETGROUP_ID AS NET_ID,
	C.NAME AS net_name,
	A.ORGAN_ID AS ORGAN_ID,
	D.short_name AS ORGAN_name,
	A.app_id as app_id,
	B.ITM_APP_NAME AS app_name,
	sum(A.visit_count) as visit_count,
	sum(A.operate_count) as operate_count ,
CASE
		B.PRIORITY 
		WHEN 'low' THEN
		'一般应用' 
		WHEN 'important' THEN
		'重要应用' 
		WHEN 'high' THEN
		'核心应用' ELSE '未知' 
	END AS PRIORITY,
	F.NAME
FROM  app_visit_log_region as A
	LEFT JOIN itm_app B on A.app_id=B.itm_app_id
	LEFT JOIN ( SELECT VALUE, NAME FROM m_com_dict WHERE dict_index = 'itm_resources_netcode' ) C ON C.VALUE = B.ITM_NETGROUP_ID
	LEFT JOIN pub_organ D ON D.id = A.ORGAN_ID
	LEFT JOIN ( SELECT temp1.ITM_APP_ID, temp2.NAME FROM itm_app temp1 LEFT JOIN m_com_dict temp2 ON temp1.app_type = temp2.VALUE ) AS F ON A.app_id = F.itm_app_id 
GROUP BY
	A.app_id,
	A.ORGAN_ID,
	B.ITM_NETGROUP_ID,
	B.ITM_APP_NAME,
	C.NAME,
	D.short_name,
	B.priority,
	F.NAME,
  B.PRIORITY,
	A.time_mark,
	DATE_FORMAT(A.time_mark,'%Y%m%d%H');
  


insert into bi_app_access_stat
SELECT
	'DAY' AS dtype,
  substring(thedvalue,1,8) AS thedvalue, 
	NET_ID,
	 net_name,
	 ORGAN_ID,
	 ORGAN_name,
	app_id,
	app_name,
	sum(visit_count) as visit_count,
	sum(operate_count) as operate_count ,
CASE
		PRIORITY 
		WHEN 'low' THEN
		'一般应用' 
		WHEN 'important' THEN
		'重要应用' 
		WHEN 'high' THEN
		'核心应用' ELSE '未知' 
	END AS PRIORITY,
	app_type
FROM bi_app_access_stat WHERE dtype = 'HOUR'
GROUP BY
	dtype,
	thedvalue,
	NET_ID,
	net_name,
	ORGAN_ID,
	ORGAN_name,
	app_id,
	app_name,
	priority,
	app_type,
	substring(thedvalue,1,8); 
  

insert into bi_app_access_stat
SELECT
	'MONTH' AS dtype,
  substring(thedvalue,1,6) AS thedvalue, 
	NET_ID,
	 net_name,
	 ORGAN_ID,
	 ORGAN_name,
	app_id,
	app_name,
	sum(visit_count) as visit_count,
	sum(operate_count) as operate_count ,
CASE
		PRIORITY 
		WHEN 'low' THEN
		'一般应用' 
		WHEN 'important' THEN
		'重要应用' 
		WHEN 'high' THEN
		'核心应用' ELSE '未知' 
	END AS PRIORITY,
	app_type
FROM bi_app_access_stat WHERE dtype = 'DAY'
GROUP BY
	dtype,
	thedvalue,
	NET_ID,
	net_name,
	ORGAN_ID,
	ORGAN_name,
	app_id,
	app_name,
	priority,
	app_type,
	substring(thedvalue,1,6); 
 

 
insert into bi_app_access_stat
SELECT
	'YEAR' AS dtype,
  substring(thedvalue,1,4) AS thedvalue, 
	NET_ID,
	 net_name,
	 ORGAN_ID,
	 ORGAN_name,
	app_id,
	app_name,
	sum(visit_count) as visit_count,
	sum(operate_count) as operate_count ,
CASE
		PRIORITY 
		WHEN 'low' THEN
		'一般应用' 
		WHEN 'important' THEN
		'重要应用' 
		WHEN 'high' THEN
		'核心应用' ELSE '未知' 
	END AS PRIORITY,
	app_type
FROM bi_app_access_stat WHERE dtype = 'MONTH'
GROUP BY
	dtype,
	thedvalue,
	NET_ID,
	net_name,
	ORGAN_ID,
	ORGAN_name,
	app_id,
	app_name,
	priority,
	app_type,
	substring(thedvalue,1,4) 


-- 响应时间趋势
DROP TABLE IF EXISTS `bi_app_response_time_stat`;
CREATE TABLE `bi_app_response_time_stat` (
	`dtype` varchar(5) NOT NULL DEFAULT '' COMMENT '日期类型',
	`thedvalue` varchar(20) DEFAULT NULL COMMENT '日期',
	`dhour` varchar(15) 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 '应用名称',
	`response_time` 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;


	insert into bi_app_response_time_stat
	select 
		'HOUR' as dtype,
		date_format(a.CREATE_TIME,'%Y%m%d%H') as thedvalue,
		date_format(a.CREATE_TIME,'%H') as dhour,
		b.ITM_NETGROUP_ID as NET_ID,
		d.name as NET_NAME,
		a.ORGAN_ID as ORGAN_ID,
		e.`SHORT_NAME` as organ_name,
		a.app_id as app_id,
		b.ITM_APP_NAME as app_name,
		avg(a.resp_time) as response_time,
		case b.PRIORITY WHEN 'low' then '一般应用' when 'important' then '重要应用' when 'high' then '核心应用' else '未知' end as priority,
		e.name as app_type
	from app_app_detail_stat a
		LEFT JOIN itm_app b on a.app_id =b.ITM_APP_ID
		LEFT JOIN itm_organ_net c on c.NET_ID = a.net_id
		inner JOIN (select * from m_com_dict where dict_index = 'app_type') d on b.app_type=d.value
		left join pub_organ e on b.ORGAN_ID = e.`CODE`
	group by 
		date_format(a.monitortime,'%Y%m%d%H'),date_format(a.monitortime,'%H'),
		b.PRIORITY,
		d.name,c.net_name,a.id ,a.app_id ,a.app_name ,a.ORGAN_ID ,a.net_id,e.`SHORT_NAME`,e.`NAME`;


	insert into bi_app_response_time_stat
	select 'DAY' as dtype,substring(thedvalue,1,8) AS thedvalue,
			dhour, NET_ID, NET_NAME, ORGAN_ID, ORGAN_NAME, app_id, app_name, avg(response_time) as response_time, priority, app_type
		from bi_app_response_time_stat 
		where dtype = 'HOUR'
		GROUP BY substring(thedvalue,1,8),dhour, NET_ID, NET_NAME, ORGAN_ID, ORGAN_NAME, app_id, app_name,priority, app_type;

	insert into bi_app_response_time_stat
	select 'MONTH' as dtype,substring(thedvalue,1,6) AS thedvalue,
			dhour, NET_ID, NET_NAME, ORGAN_ID, ORGAN_NAME, app_id, app_name, avg(response_time) as response_time, priority, app_type
		from bi_app_response_time_stat 
		where dtype = 'DAY'
		GROUP BY substring(thedvalue,1,6),dhour, NET_ID, NET_NAME, ORGAN_ID, ORGAN_NAME, app_id, app_name,priority, app_type;

	insert into bi_app_response_time_stat
	select 'YEAR' as dtype,substring(thedvalue,1,4) AS thedvalue,
			dhour, NET_ID, NET_NAME, ORGAN_ID, ORGAN_NAME, app_id, app_name, avg(response_time) as response_time, priority, app_type
		from bi_app_response_time_stat 
		where dtype = 'MONTH'
		GROUP BY substring(thedvalue,1,4),dhour, NET_ID, NET_NAME, ORGAN_ID, ORGAN_NAME, app_id, app_name,priority, app_type;