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
| -- 先执行 energy_management_init.sql；新增预警规则、通知历史、通知模板和导出权限。
| -- 不创建示例规则，不向任何用户发送通知。可重复执行。
| CREATE TABLE IF NOT EXISTS mes_energy_alert_rule (
| id bigint NOT NULL AUTO_INCREMENT,
| name varchar(100) NOT NULL,
| energy_type varchar(20) NOT NULL,
| usage_area varchar(100) NOT NULL DEFAULT '',
| product_category varchar(50) NOT NULL DEFAULT '',
| metric varchar(20) NOT NULL COMMENT 'QUANTITY每日用量/AMOUNT每日费用',
| threshold decimal(29,3) NOT NULL,
| enabled bit(1) NOT NULL DEFAULT b'1',
| recipient_ids varchar(1100) NOT NULL COMMENT '站内通知接收人ID，逗号分隔',
| 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', tenant_id bigint NOT NULL DEFAULT 0,
| PRIMARY KEY(id), KEY idx_energy_rule_tenant(tenant_id,deleted,enabled)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='独立能耗每日预警规则';
|
| CREATE TABLE IF NOT EXISTS mes_energy_alert_log (
| id bigint NOT NULL AUTO_INCREMENT,
| rule_id bigint NOT NULL, rule_name varchar(100) NOT NULL,
| record_date date NOT NULL, energy_type varchar(20) NOT NULL,
| scope varchar(200) NOT NULL, metric varchar(20) NOT NULL,
| actual_value decimal(45,3) NOT NULL, threshold decimal(29,3) NOT NULL,
| recipient_id bigint NOT NULL, recipient_name varchar(100) NOT NULL,
| message_id bigint DEFAULT NULL,
| status varchar(20) NOT NULL COMMENT 'SENT成功/FAILED失败，可重试',
| error_message varchar(500) NOT NULL DEFAULT '',
| 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', tenant_id bigint NOT NULL DEFAULT 0,
| PRIMARY KEY(id),
| UNIQUE KEY uk_energy_daily_recipient(tenant_id,rule_id,record_date,recipient_id),
| KEY idx_energy_log_date(tenant_id,deleted,record_date,status)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='能耗预警通知历史及发送结果';
|
| START TRANSACTION;
| INSERT INTO system_notify_template(name,code,nickname,content,type,params,status,remark,creator,updater,deleted)
| SELECT '每日能耗超限预警','energy_daily_warning','能耗管理',
| '【能耗预警】{date}，{ruleName}：{scope}的{energyName}{metricName}为{actual}{unit}，超过每日阈值{threshold}{unit}。请核查台账及用能情况。',
| 1,'["date","ruleName","scope","energyName","metricName","actual","unit","threshold"]',0,'独立能耗管理每日阈值通知','1','1',0
| FROM DUAL WHERE NOT EXISTS(SELECT 1 FROM system_notify_template WHERE code='energy_daily_warning' AND deleted=0);
|
| SET @energy_root=(SELECT id FROM system_menu WHERE parent_id=0 AND path='/energy' AND deleted=0 LIMIT 1);
| SET @energy_record=(SELECT id FROM system_menu WHERE parent_id=@energy_root AND path='record' AND deleted=0 LIMIT 1);
| SET @energy_analysis=(SELECT id FROM system_menu WHERE parent_id=@energy_root AND path='analysis' AND deleted=0 LIMIT 1);
| SET @energy_alert=(SELECT id FROM system_menu WHERE parent_id=@energy_root AND path='alert' AND deleted=0 LIMIT 1);
| SET @energy_next=(SELECT MAX(id)+1 FROM system_menu);
| INSERT INTO system_menu(id,name,permission,type,sort,parent_id,path,icon,component,component_name,status,visible,keep_alive,always_show,creator,updater,deleted)
| SELECT @energy_next,'能耗预警','energy:alert:query',2,3,@energy_root,'alert','lucide:bell-ring','energy/alert/index','EnergyAlert',0,1,0,1,'1','1',0
| FROM DUAL WHERE @energy_alert IS NULL AND @energy_root IS NOT NULL;
| SET @energy_alert=(SELECT id FROM system_menu WHERE parent_id=@energy_root AND path='alert' AND deleted=0 LIMIT 1);
|
| SET @energy_next=(SELECT MAX(id)+1 FROM system_menu);
| INSERT INTO system_menu(id,name,permission,type,sort,parent_id,path,icon,component,component_name,status,visible,keep_alive,always_show,creator,updater,deleted)
| SELECT @energy_next,'新增预警规则','energy:alert:create',3,1,@energy_alert,'','','','',0,1,0,1,'1','1',0 FROM DUAL
| WHERE @energy_alert IS NOT NULL AND NOT EXISTS(SELECT 1 FROM system_menu WHERE permission='energy:alert:create' AND deleted=0);
| SET @energy_next=(SELECT MAX(id)+1 FROM system_menu);
| INSERT INTO system_menu(id,name,permission,type,sort,parent_id,path,icon,component,component_name,status,visible,keep_alive,always_show,creator,updater,deleted)
| SELECT @energy_next,'修改预警规则','energy:alert:update',3,2,@energy_alert,'','','','',0,1,0,1,'1','1',0 FROM DUAL
| WHERE @energy_alert IS NOT NULL AND NOT EXISTS(SELECT 1 FROM system_menu WHERE permission='energy:alert:update' AND deleted=0);
| SET @energy_next=(SELECT MAX(id)+1 FROM system_menu);
| INSERT INTO system_menu(id,name,permission,type,sort,parent_id,path,icon,component,component_name,status,visible,keep_alive,always_show,creator,updater,deleted)
| SELECT @energy_next,'删除预警规则','energy:alert:delete',3,3,@energy_alert,'','','','',0,1,0,1,'1','1',0 FROM DUAL
| WHERE @energy_alert IS NOT NULL AND NOT EXISTS(SELECT 1 FROM system_menu WHERE permission='energy:alert:delete' AND deleted=0);
| SET @energy_next=(SELECT MAX(id)+1 FROM system_menu);
| INSERT INTO system_menu(id,name,permission,type,sort,parent_id,path,icon,component,component_name,status,visible,keep_alive,always_show,creator,updater,deleted)
| SELECT @energy_next,'检查预警','energy:alert:check',3,4,@energy_alert,'','','','',0,1,0,1,'1','1',0 FROM DUAL
| WHERE @energy_alert IS NOT NULL AND NOT EXISTS(SELECT 1 FROM system_menu WHERE permission='energy:alert:check' AND deleted=0);
| SET @energy_next=(SELECT MAX(id)+1 FROM system_menu);
| INSERT INTO system_menu(id,name,permission,type,sort,parent_id,path,icon,component,component_name,status,visible,keep_alive,always_show,creator,updater,deleted)
| SELECT @energy_next,'导出能耗台账','energy:record:export',3,4,@energy_record,'','','','',0,1,0,1,'1','1',0 FROM DUAL
| WHERE @energy_record IS NOT NULL AND NOT EXISTS(SELECT 1 FROM system_menu WHERE permission='energy:record:export' AND deleted=0);
| SET @energy_next=(SELECT MAX(id)+1 FROM system_menu);
| INSERT INTO system_menu(id,name,permission,type,sort,parent_id,path,icon,component,component_name,status,visible,keep_alive,always_show,creator,updater,deleted)
| SELECT @energy_next,'导出能耗分析','energy:analysis:export',3,1,@energy_analysis,'','','','',0,1,0,1,'1','1',0 FROM DUAL
| WHERE @energy_analysis IS NOT NULL AND NOT EXISTS(SELECT 1 FROM system_menu WHERE permission='energy:analysis:export' AND deleted=0);
|
| INSERT INTO system_role_menu(role_id,menu_id,creator,updater,deleted,tenant_id)
| SELECT 1,m.id,'1','1',0,r.tenant_id FROM system_menu m JOIN system_role r ON r.id=1 AND r.deleted=0
| WHERE m.deleted=0 AND (m.id=@energy_alert OR m.parent_id=@energy_alert OR m.permission IN ('energy:record:export','energy:analysis:export'))
| AND NOT EXISTS(SELECT 1 FROM system_role_menu rm WHERE rm.role_id=1 AND rm.menu_id=m.id AND rm.deleted=0);
| COMMIT;
|
|
