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
| -- 独立能耗管理：新增台账及一级菜单，不关联其他业务表。
| -- 可重复执行。执行前请确认目标库，菜单只默认分配给超级管理员（role_id=1）。
| CREATE TABLE IF NOT EXISTS `mes_energy_record` (
| `id` bigint NOT NULL AUTO_INCREMENT COMMENT '记录编号',
| `record_date` date NOT NULL COMMENT '统计日期',
| `energy_type` varchar(20) NOT NULL COMMENT 'WATER水/ELECTRICITY电/STEAM蒸汽',
| `usage_area` varchar(100) NOT NULL COMMENT '使用区域（手工填写）',
| `product_category` varchar(50) NOT NULL COMMENT '产品类别（本模块）',
| `usage_purpose` varchar(100) NOT NULL COMMENT '用途/工序（手工填写）',
| `quantity` decimal(18,3) NOT NULL COMMENT '用量，水m³/电kWh/蒸汽t',
| `unit_price` decimal(12,4) NOT NULL DEFAULT 0 COMMENT '元/能源单位',
| `amount` decimal(26,2) NOT NULL DEFAULT 0 COMMENT '费用=用量*单价，四舍五入到分',
| `recorder` varchar(50) NOT NULL COMMENT '录入人姓名（手工填写）',
| `remark` 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`),
| KEY `idx_energy_date` (`tenant_id`, `deleted`, `record_date`, `energy_type`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='独立能耗手工台账';
|
| START TRANSACTION;
| SET @energy_root = (SELECT id FROM system_menu WHERE parent_id=0 AND path='/energy' AND deleted=b'0' LIMIT 1);
| SET @energy_next = (SELECT IFNULL(MAX(id),0)+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,create_time,updater,update_time,deleted)
| SELECT @energy_next,'能耗管理','',1,65,0,'/energy','lucide:zap','','',0,1,1,1,'1',NOW(),'1',NOW(),0 FROM DUAL WHERE @energy_root IS NULL;
| SET @energy_root = COALESCE(@energy_root,@energy_next);
|
| SET @energy_record = (SELECT id FROM system_menu WHERE parent_id=@energy_root AND path='record' AND deleted=b'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,create_time,updater,update_time,deleted)
| SELECT @energy_next,'能耗记录','energy:record:query',2,1,@energy_root,'record','lucide:clipboard-list','energy/record/index','EnergyRecord',0,1,0,1,'1',NOW(),'1',NOW(),0 FROM DUAL WHERE @energy_record IS NULL;
| SET @energy_record = COALESCE(@energy_record,@energy_next);
|
| SET @energy_analysis = (SELECT id FROM system_menu WHERE parent_id=@energy_root AND path='analysis' AND deleted=b'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,create_time,updater,update_time,deleted)
| SELECT @energy_next,'数据分析','energy:analysis:query',2,2,@energy_root,'analysis','lucide:chart-no-axes-combined','energy/analysis/index','EnergyAnalysis',0,1,0,1,'1',NOW(),'1',NOW(),0 FROM DUAL WHERE @energy_analysis IS NULL;
| SET @energy_analysis = COALESCE(@energy_analysis,@energy_next);
|
| 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,create_time,updater,update_time,deleted)
| SELECT @energy_next,'新增能耗记录','energy:record:create',3,1,@energy_record,'','','','',0,1,0,1,'1',NOW(),'1',NOW(),0 FROM DUAL
| WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE permission='energy:record:create' AND deleted=b'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,create_time,updater,update_time,deleted)
| SELECT @energy_next,'修改能耗记录','energy:record:update',3,2,@energy_record,'','','','',0,1,0,1,'1',NOW(),'1',NOW(),0 FROM DUAL
| WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE permission='energy:record:update' AND deleted=b'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,create_time,updater,update_time,deleted)
| SELECT @energy_next,'删除能耗记录','energy:record:delete',3,3,@energy_record,'','','','',0,1,0,1,'1',NOW(),'1',NOW(),0 FROM DUAL
| WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE permission='energy:record:delete' AND deleted=b'0');
|
| INSERT INTO system_role_menu (role_id,menu_id,creator,create_time,updater,update_time,deleted,tenant_id)
| SELECT 1,m.id,'1',NOW(),'1',NOW(),0,r.tenant_id FROM system_menu m JOIN system_role r ON r.id=1 AND r.deleted=b'0'
| WHERE m.deleted=b'0' AND (m.id=@energy_root OR m.parent_id=@energy_root OR m.parent_id=@energy_record)
| AND NOT EXISTS (SELECT 1 FROM system_role_menu rm WHERE rm.role_id=1 AND rm.menu_id=m.id AND rm.deleted=b'0');
| COMMIT;
|
|
