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
| -- 设备备件管理：独立备件库存，不改动现有物料仓库库存。
| CREATE TABLE IF NOT EXISTS mes_dv_spare_part (
| id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
| code VARCHAR(64) NOT NULL, name VARCHAR(128) NOT NULL,
| specification VARCHAR(255) DEFAULT '', unit VARCHAR(32) NOT NULL,
| location VARCHAR(128) DEFAULT '',
| stock_quantity DECIMAL(18,3) NOT NULL DEFAULT 0,
| safety_quantity DECIMAL(18,3) NOT NULL DEFAULT 0,
| status TINYINT NOT NULL DEFAULT 0, remark VARCHAR(500) 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',
| active_code VARCHAR(64) GENERATED ALWAYS AS (CASE WHEN deleted=b'0' THEN code ELSE NULL END) STORED,
| UNIQUE KEY uk_active_code(active_code),
| CONSTRAINT chk_spare_stock CHECK(stock_quantity>=0 AND safety_quantity>=0),
| CONSTRAINT chk_spare_status CHECK(status IN (0,1))
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='设备备件台账';
| CREATE TABLE IF NOT EXISTS mes_dv_spare_movement (
| id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
| request_key VARCHAR(64) NOT NULL,
| spare_part_id BIGINT NOT NULL, spare_part_code VARCHAR(64) NOT NULL,
| spare_part_name VARCHAR(128) NOT NULL, specification VARCHAR(255) DEFAULT '', unit VARCHAR(32) NOT NULL,
| type TINYINT NOT NULL, quantity DECIMAL(18,3) NOT NULL,
| before_quantity DECIMAL(18,3) NOT NULL, after_quantity DECIMAL(18,3) NOT NULL,
| repair_id BIGINT NULL, repair_code VARCHAR(64) NULL, machinery_id BIGINT NULL,
| machinery_name VARCHAR(128) NULL, original_id BIGINT NULL,
| receiver_user_id BIGINT NULL, receiver_name VARCHAR(128) NULL,
| operator_user_id BIGINT NOT NULL, remark VARCHAR(500) NOT 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',
| UNIQUE KEY uk_request_key(request_key),
| KEY idx_spare_time(spare_part_id,create_time),
| KEY idx_repair(repair_id), KEY idx_original(original_id),
| CONSTRAINT chk_spare_movement CHECK(quantity>0 AND before_quantity>=0 AND after_quantity>=0 AND type IN(1,2,3,4))
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='备件库存与领用流水';
| INSERT INTO system_menu(name,permission,type,sort,parent_id,path,icon,component,component_name,status,visible,keep_alive,always_show,creator,updater)
| SELECT '备件台账','mes:dv-spare:query',2,8,repair.parent_id,'spare','ep:box','mes/dv/spare/index','MesDvSpare',0,b'1',b'1',b'1','1','1'
| FROM system_menu repair WHERE repair.component='mes/dv/repair/index' AND repair.deleted=0
| AND NOT EXISTS(SELECT 1 FROM system_menu m WHERE m.component='mes/dv/spare/index' AND m.deleted=0) LIMIT 1;
| INSERT INTO system_menu(name,permission,type,sort,parent_id,path,icon,component,component_name,status,visible,keep_alive,always_show,creator,updater)
| SELECT '领用记录','mes:dv-spare:record',2,9,repair.parent_id,'spare-record','ep:tickets','mes/dv/spare-record/index','MesDvSpareRecord',0,b'1',b'1',b'1','1','1'
| FROM system_menu repair WHERE repair.component='mes/dv/repair/index' AND repair.deleted=0
| AND NOT EXISTS(SELECT 1 FROM system_menu m WHERE m.component='mes/dv/spare-record/index' AND m.deleted=0) LIMIT 1;
| INSERT INTO system_menu(name,permission,type,sort,parent_id,path,icon,component,status,visible,keep_alive,always_show,creator,updater)
| SELECT '新增备件','mes:dv-spare:create',3,1,m.id,'','','',0,b'1',b'1',b'1','1','1'
| FROM system_menu m WHERE m.component='mes/dv/spare/index' AND m.deleted=0
| AND NOT EXISTS(SELECT 1 FROM system_menu b WHERE b.permission='mes:dv-spare:create' AND b.deleted=0) LIMIT 1;
| INSERT INTO system_menu(name,permission,type,sort,parent_id,path,icon,component,status,visible,keep_alive,always_show,creator,updater)
| SELECT '编辑备件','mes:dv-spare:update',3,2,m.id,'','','',0,b'1',b'1',b'1','1','1'
| FROM system_menu m WHERE m.component='mes/dv/spare/index' AND m.deleted=0
| AND NOT EXISTS(SELECT 1 FROM system_menu b WHERE b.permission='mes:dv-spare:update' AND b.deleted=0) LIMIT 1;
| INSERT INTO system_menu(name,permission,type,sort,parent_id,path,icon,component,status,visible,keep_alive,always_show,creator,updater)
| SELECT '删除备件','mes:dv-spare:delete',3,3,m.id,'','','',0,b'1',b'1',b'1','1','1'
| FROM system_menu m WHERE m.component='mes/dv/spare/index' AND m.deleted=0
| AND NOT EXISTS(SELECT 1 FROM system_menu b WHERE b.permission='mes:dv-spare:delete' AND b.deleted=0) LIMIT 1;
| INSERT INTO system_menu(name,permission,type,sort,parent_id,path,icon,component,status,visible,keep_alive,always_show,creator,updater)
| SELECT '备件入库','mes:dv-spare:receive',3,4,m.id,'','','',0,b'1',b'1',b'1','1','1'
| FROM system_menu m WHERE m.component='mes/dv/spare/index' AND m.deleted=0
| AND NOT EXISTS(SELECT 1 FROM system_menu b WHERE b.permission='mes:dv-spare:receive' AND b.deleted=0) LIMIT 1;
| INSERT INTO system_menu(name,permission,type,sort,parent_id,path,icon,component,status,visible,keep_alive,always_show,creator,updater)
| SELECT '维修领用','mes:dv-spare:issue',3,5,m.id,'','','',0,b'1',b'1',b'1','1','1'
| FROM system_menu m WHERE m.component='mes/dv/spare/index' AND m.deleted=0
| AND NOT EXISTS(SELECT 1 FROM system_menu b WHERE b.permission='mes:dv-spare:issue' AND b.deleted=0) LIMIT 1;
| INSERT INTO system_menu(name,permission,type,sort,parent_id,path,icon,component,status,visible,keep_alive,always_show,creator,updater)
| SELECT '备件退回','mes:dv-spare:return',3,6,m.id,'','','',0,b'1',b'1',b'1','1','1'
| FROM system_menu m WHERE m.component='mes/dv/spare/index' AND m.deleted=0
| AND NOT EXISTS(SELECT 1 FROM system_menu b WHERE b.permission='mes:dv-spare:return' AND b.deleted=0) LIMIT 1;
| -- 普通角色权限需通过角色管理明确配置；本脚本不批量扩大现有角色的权限。
|
|
