1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
| -- =========================================
| -- 决策支持模块初始化脚本
| -- 4 张新表 + 菜单 + 角色授权
| -- 电力数转 phase11
| -- =========================================
|
| -- 1. KPI指标定义表
| CREATE TABLE IF NOT EXISTS `bi_kpi_definition` (
| `id` bigint NOT NULL AUTO_INCREMENT,
| `code` varchar(100) NOT NULL COMMENT 'KPI编码',
| `name` varchar(200) NOT NULL COMMENT 'KPI名称',
| `category` varchar(50) NOT NULL COMMENT '分类：power_supply/device_operation/production/quality/procurement/safety',
| `unit` varchar(50) DEFAULT NULL COMMENT '单位',
| `data_source` varchar(50) NOT NULL DEFAULT 'sql' COMMENT '数据来源：sql/interface/manual',
| `query_sql` text COMMENT '查询SQL',
| `chart_type` varchar(50) DEFAULT 'number' COMMENT '默认图表类型',
| `threshold_warn` decimal(20,4) DEFAULT NULL COMMENT '预警阈值-警告',
| `threshold_critical` decimal(20,4) DEFAULT NULL COMMENT '预警阈值-严重',
| `comparison_operator` varchar(10) DEFAULT '>' COMMENT '比较运算符：>/</>=/<=',
| `refresh_interval` int DEFAULT 300 COMMENT '刷新间隔(秒)',
| `sort` int DEFAULT 0,
| `status` tinyint DEFAULT 1 COMMENT '状态：0-禁用 1-启用',
| `remark` varchar(500) DEFAULT NULL,
| `creator` varchar(64) DEFAULT '',
| `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
| `updater` varchar(64) DEFAULT '',
| `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
| `deleted` bit(1) NOT NULL DEFAULT b'0',
| PRIMARY KEY (`id`),
| UNIQUE KEY `uk_code` (`code`),
| KEY `idx_category` (`category`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='KPI指标定义表';
|
| -- 2. 预警规则表
| CREATE TABLE IF NOT EXISTS `bi_alert_rule` (
| `id` bigint NOT NULL AUTO_INCREMENT,
| `kpi_id` bigint NOT NULL COMMENT '关联KPI指标ID',
| `name` varchar(200) NOT NULL COMMENT '规则名称',
| `severity` tinyint NOT NULL DEFAULT 1 COMMENT '严重级别：1-警告 2-严重 3-紧急',
| `comparison_operator` varchar(10) NOT NULL DEFAULT '>' COMMENT '比较运算符',
| `threshold_value` decimal(20,4) NOT NULL COMMENT '阈值',
| `notification_type` varchar(100) DEFAULT NULL COMMENT '通知方式：system/message/email(逗号分隔)',
| `enabled` tinyint DEFAULT 1 COMMENT '是否启用：0-禁用 1-启用',
| `remark` varchar(500) DEFAULT NULL,
| `creator` varchar(64) DEFAULT '',
| `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
| `updater` varchar(64) DEFAULT '',
| `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
| `deleted` bit(1) NOT NULL DEFAULT b'0',
| PRIMARY KEY (`id`),
| KEY `idx_kpi_id` (`kpi_id`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='预警规则表';
|
| -- 3. 预警记录表
| CREATE TABLE IF NOT EXISTS `bi_alert_record` (
| `id` bigint NOT NULL AUTO_INCREMENT,
| `rule_id` bigint NOT NULL COMMENT '关联规则ID',
| `kpi_id` bigint NOT NULL COMMENT '关联KPI指标ID',
| `kpi_code` varchar(100) NOT NULL COMMENT 'KPI编码(冗余)',
| `kpi_name` varchar(200) NOT NULL COMMENT 'KPI名称(冗余)',
| `actual_value` decimal(20,4) NOT NULL COMMENT '实际值',
| `threshold_value` decimal(20,4) NOT NULL COMMENT '阈值(冗余)',
| `severity` tinyint NOT NULL DEFAULT 1 COMMENT '严重级别',
| `alert_message` varchar(500) DEFAULT NULL COMMENT '预警消息',
| `status` tinyint NOT NULL DEFAULT 0 COMMENT '状态：0-未处理 1-已确认 2-已处理',
| `handler_id` bigint DEFAULT NULL COMMENT '处理人ID',
| `handle_time` datetime DEFAULT NULL COMMENT '处理时间',
| `handle_remark` varchar(500) DEFAULT NULL COMMENT '处理备注',
| `triggered_time` datetime NOT NULL COMMENT '触发时间',
| `creator` varchar(64) DEFAULT '',
| `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
| `updater` varchar(64) DEFAULT '',
| `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
| `deleted` bit(1) NOT NULL DEFAULT b'0',
| PRIMARY KEY (`id`),
| KEY `idx_kpi_code` (`kpi_code`),
| KEY `idx_status` (`status`),
| KEY `idx_triggered_time` (`triggered_time`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='预警记录表';
|
| -- 4. 负荷预测数据表
| CREATE TABLE IF NOT EXISTS `bi_forecast_data` (
| `id` bigint NOT NULL AUTO_INCREMENT,
| `forecast_code` varchar(100) NOT NULL COMMENT '预测编码，如：load_forecast_daily',
| `forecast_name` varchar(200) NOT NULL COMMENT '预测名称',
| `point_time` datetime NOT NULL COMMENT '预测时间点',
| `forecast_value` decimal(20,4) NOT NULL COMMENT '预测值',
| `actual_value` decimal(20,4) DEFAULT NULL COMMENT '实际值(回填用于校验)',
| `lower_bound` decimal(20,4) DEFAULT NULL COMMENT '置信区间下限',
| `upper_bound` decimal(20,4) DEFAULT NULL COMMENT '置信区间上限',
| `confidence_level` decimal(5,2) DEFAULT 95.00 COMMENT '置信水平(%)',
| `model_version` varchar(50) DEFAULT NULL COMMENT '模型版本',
| `dimension` varchar(100) DEFAULT NULL COMMENT '维度，如：region/line/station',
| `dimension_value` varchar(100) DEFAULT NULL COMMENT '维度值',
| `remark` varchar(500) DEFAULT NULL,
| `creator` varchar(64) DEFAULT '',
| `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
| `updater` varchar(64) DEFAULT '',
| `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
| `deleted` bit(1) NOT NULL DEFAULT b'0',
| PRIMARY KEY (`id`),
| KEY `idx_forecast_code` (`forecast_code`),
| KEY `idx_point_time` (`point_time`),
| KEY `idx_dimension` (`dimension`, `dimension_value`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='负荷预测数据表';
|
| -- =========================================
| -- 菜单
| -- =========================================
| -- 挂在现有「决策支持」(1063000) 下
| -- 菜单 ID 从 1064000 起
|
| -- 先删除已存在的菜单（幂等）
| DELETE FROM `system_menu` WHERE `id` BETWEEN 1064000 AND 1064099 AND `deleted` = b'0';
|
| -- 电力KPI看板
| INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `status`, `visible`, `keep_alive`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
| (1064000, '电力KPI看板', '', 2, 6, 1063000, 'kpi-dashboard', 'ep:data-board', 'bi/decision/kpi-dashboard/index', 0, b'1', b'1', '1', NOW(), '1', NOW(), b'0');
|
| -- 预警中心
| INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `status`, `visible`, `keep_alive`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
| (1064001, '预警中心', '', 2, 7, 1063000, 'alert-center', 'ep:warning', 'bi/decision/alert-center/index', 0, b'1', b'1', '1', NOW(), '1', NOW(), b'0');
|
| -- 负荷预测
| INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `status`, `visible`, `keep_alive`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
| (1064002, '负荷预测', '', 2, 8, 1063000, 'forecast-analysis', 'ep:trend-charts', 'bi/decision/forecast-analysis/index', 0, b'1', b'1', '1', NOW(), '1', NOW(), b'0');
|
| -- 趋势分析
| INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `status`, `visible`, `keep_alive`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
| (1064003, '趋势分析', '', 2, 9, 1063000, 'trend-analysis', 'ep:data-line', 'bi/decision/trend-analysis/index', 0, b'1', b'1', '1', NOW(), '1', NOW(), b'0');
|
| -- KPI指标定义（挂在配置管理 1063006 下）
| INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `status`, `visible`, `keep_alive`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
| (1064004, 'KPI指标定义', '', 2, 3, 1063006, 'kpi-config', 'ep:set-up', 'bi/decision/kpi-config/index', 0, b'1', b'1', '1', NOW(), '1', NOW(), b'0');
|
| -- 预警规则配置（挂在配置管理 1063006 下）
| INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `status`, `visible`, `keep_alive`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
| (1064005, '预警规则配置', '', 2, 3, 1063006, 'alert-rule', 'ep:bell', 'bi/decision/alert-rule/index', 0, b'1', b'1', '1', NOW(), '1', NOW(), b'0');
|
| -- 按钮权限
| -- KPI指标定义 按钮
| INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `status`, `visible`, `keep_alive`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
| (1064061, 'KPI指标查询', 'bi:decision:kpi:query', 3, 1, 1064004, '', '', '', 0, b'1', b'1', '1', NOW(), '1', NOW(), b'0');
| INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `status`, `visible`, `keep_alive`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
| (1064062, 'KPI指标新增', 'bi:decision:kpi:create', 3, 2, 1064004, '', '', '', 0, b'1', b'1', '1', NOW(), '1', NOW(), b'0');
| INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `status`, `visible`, `keep_alive`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
| (1064063, 'KPI指标修改', 'bi:decision:kpi:update', 3, 3, 1064004, '', '', '', 0, b'1', b'1', '1', NOW(), '1', NOW(), b'0');
| INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `status`, `visible`, `keep_alive`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
| (1064064, 'KPI指标删除', 'bi:decision:kpi:delete', 3, 4, 1064004, '', '', '', 0, b'1', b'1', '1', NOW(), '1', NOW(), b'0');
|
| -- 预警规则配置 按钮
| INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `status`, `visible`, `keep_alive`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
| (1064071, '预警规则查询', 'bi:decision:alert-rule:query', 3, 1, 1064005, '', '', '', 0, b'1', b'1', '1', NOW(), '1', NOW(), b'0');
| INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `status`, `visible`, `keep_alive`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
| (1064072, '预警规则新增', 'bi:decision:alert-rule:create', 3, 2, 1064005, '', '', '', 0, b'1', b'1', '1', NOW(), '1', NOW(), b'0');
| INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `status`, `visible`, `keep_alive`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
| (1064073, '预警规则修改', 'bi:decision:alert-rule:update', 3, 3, 1064005, '', '', '', 0, b'1', b'1', '1', NOW(), '1', NOW(), b'0');
| INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `status`, `visible`, `keep_alive`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
| (1064074, '预警规则删除', 'bi:decision:alert-rule:delete', 3, 4, 1064005, '', '', '', 0, b'1', b'1', '1', NOW(), '1', NOW(), b'0');
|
| -- 预警中心 按钮
| INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `status`, `visible`, `keep_alive`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
| (1064081, '预警记录查询', 'bi:decision:alert:query', 3, 1, 1064001, '', '', '', 0, b'1', b'1', '1', NOW(), '1', NOW(), b'0');
| INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `status`, `visible`, `keep_alive`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
| (1064082, '预警确认', 'bi:decision:alert:confirm', 3, 2, 1064001, '', '', '', 0, b'1', b'1', '1', NOW(), '1', NOW(), b'0');
| INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `status`, `visible`, `keep_alive`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
| (1064083, '预警处理', 'bi:decision:alert:handle', 3, 3, 1064001, '', '', '', 0, b'1', b'1', '1', NOW(), '1', NOW(), b'0');
|
| -- =========================================
| -- 角色授权（角色 2=超级管理员, 109/111 参见已有配置）
| -- =========================================
| -- 超级管理员 全部授权
| INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`)
| SELECT r.id, m.id, '1', NOW()
| FROM `system_role` r CROSS JOIN `system_menu` m
| WHERE r.id IN (2) AND m.id BETWEEN 1064000 AND 1064099 AND m.deleted = b'0'
| AND NOT EXISTS (SELECT 1 FROM `system_role_menu` rm WHERE rm.role_id = r.id AND rm.menu_id = m.id);
|
|
