|
Line 0
Link Here
|
|
|
1 |
-- Stock Rotation Rotas |
| 2 |
|
| 3 |
CREATE TABLE IF NOT EXISTS stockrotationrotas ( |
| 4 |
rota_id int(11) auto_increment, -- Stockrotation rota ID |
| 5 |
title varchar(100) NOT NULL, -- Title for this rota |
| 6 |
description text NOT NULL default '', -- Description for this rota |
| 7 |
cyclical tinyint(1) NOT NULL default 0, -- Should items on this rota keep cycling? |
| 8 |
active tinyint(1) NOT NULL default 0, -- Is this rota currently active? |
| 9 |
PRIMARY KEY (`rota_id`) |
| 10 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 11 |
|
| 12 |
-- Stock Rotation Stages |
| 13 |
|
| 14 |
CREATE TABLE IF NOT EXISTS stockrotationstages ( |
| 15 |
stage_id int(11) auto_increment, -- Unique stage ID |
| 16 |
position int(11) NOT NULL, -- The position of this stage within its rota |
| 17 |
rota_id int(11) NOT NULL, -- The rota this stage belongs to |
| 18 |
branchcode_id varchar(10) NOT NULL, -- Branch this stage relates to |
| 19 |
duration int(11) NOT NULL default 4, -- The number of days items shoud occupy this stage |
| 20 |
PRIMARY KEY (`stage_id`), |
| 21 |
CONSTRAINT `stockrotationstages_rifk` |
| 22 |
FOREIGN KEY (`rota_id`) |
| 23 |
REFERENCES `stockrotationrotas` (`rota_id`) |
| 24 |
ON UPDATE CASCADE ON DELETE CASCADE, |
| 25 |
CONSTRAINT `stockrotationstages_bifk` |
| 26 |
FOREIGN KEY (`branchcode_id`) |
| 27 |
REFERENCES `branches` (`branchcode`) |
| 28 |
ON UPDATE CASCADE ON DELETE CASCADE |
| 29 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 30 |
|
| 31 |
-- Stock Rotation Items |
| 32 |
|
| 33 |
CREATE TABLE IF NOT EXISTS stockrotationitems ( |
| 34 |
itemnumber_id int(11) NOT NULL, -- Itemnumber to link to a stage & rota |
| 35 |
stage_id int(11) NOT NULL, -- stage ID to link the item to |
| 36 |
indemand tinyint(1) NOT NULL default 0, -- Should this item be skipped for rotation? |
| 37 |
PRIMARY KEY (itemnumber_id), |
| 38 |
CONSTRAINT `stockrotationitems_iifk` |
| 39 |
FOREIGN KEY (`itemnumber_id`) |
| 40 |
REFERENCES `items` (`itemnumber`) |
| 41 |
ON UPDATE CASCADE ON DELETE CASCADE, |
| 42 |
CONSTRAINT `stockrotationitems_sifk` |
| 43 |
FOREIGN KEY (`stage_id`) |
| 44 |
REFERENCES `stockrotationstages` (`stage_id`) |
| 45 |
ON UPDATE CASCADE ON DELETE CASCADE |
| 46 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 47 |
|
| 48 |
-- System preferences |
| 49 |
|
| 50 |
INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type) VALUES |
| 51 |
('StockRotation','0','If ON, enables the stock rotation module','','YesNo'), |
| 52 |
('RotationPreventTransfers','0','If ON, prevent any transfers for items on stock rotation rotas, except for stock rotation transfers','','YesNo'); |
| 53 |
|
| 54 |
-- Permissions |
| 55 |
|
| 56 |
INSERT IGNORE INTO userflags (bit, flag, flagdesc, defaulton) VALUES |
| 57 |
(21, 'stockrotation', 'Manage stockrotation operations', 0); |
| 58 |
|
| 59 |
INSERT IGNORE INTO permissions (module_bit, code, description) VALUES |
| 60 |
(21, 'can_edit_rotas', 'Create, edit and delete rotas'), |
| 61 |
(21, 'can_add_items_rotas', 'Add and remove items from rotas'); |