2 # @description: Diverse neue Tabellen und Spalten zur Mehrlagerfähigkeit inkl. Migration
3 # @depends: release_2_4_3
6 die("This script cannot be run from the command line.") unless ($main::form);
10 die($dbup_locale->text("Database update error:") .
11 "<br>$msg<br>" . $DBI::errstr);
15 my ($query, $may_fail) = @_;
17 if (!$dbh->do($query)) {
18 mydberror($query) unless ($may_fail);
24 $do_sql_migration = 0;
27 print $main::form->parse_html_template("dbupgrade/warehouse_form");
31 if (!$main::form->{do_migrate}
32 && (selectfirst_array_query($main::form, $dbh, $check_sql))[0]) { # check if update is needed
36 if ($main::form->{do_migrate} eq 'Y') {
37 # if yes, both warehouse and bin must be given
38 if (!$main::form->{import_warehouse} || !$main::form->{bin_default}) {
43 $do_sql_migration = 1;
46 my $warehouse = $main::form->{import_warehouse} ne '' ? $main::form->{import_warehouse} : "Transfer";
47 my $bin = $main::form->{bin_default} ne '' ? $main::form->{bin_default} : "1";
50 my $migration_code = <<EOF
53 INSERT INTO warehouse (description) VALUES ('$warehouse');
55 UPDATE tmp_parts SET bin = NULL WHERE bin = '';
59 (warehouse_id, description)
60 (SELECT DISTINCT warehouse.id, COALESCE(bin, '$bin')
61 FROM warehouse, tmp_parts
62 WHERE warehouse.description='$warehouse');
64 (warehouse_id, parts_id, bin_id, qty, employee_id, trans_id, trans_type_id)
65 (SELECT warehouse.id, tmp_parts.id, bin.id, onhand, (SELECT id FROM employee LIMIT 1), nextval('id'), transfer_type.id
66 FROM transfer_type, warehouse, tmp_parts, bin
67 WHERE warehouse.description = '$warehouse'
68 AND COALESCE(bin, '$bin') = bin.description
69 AND transfer_type.description = 'stock');
75 $query .= $migration_code if $do_sql_migration;
85 -- Table "bin" for bins.
87 id integer NOT NULL DEFAULT nextval('id'),
88 warehouse_id integer NOT NULL,
90 itime timestamp DEFAULT now(),
94 FOREIGN KEY (warehouse_id) REFERENCES warehouse (id)
97 CREATE TRIGGER mtime_bin BEFORE UPDATE ON bin
98 FOR EACH ROW EXECUTE PROCEDURE set_mtime();
101 ALTER TABLE warehouse ADD COLUMN sortkey integer;
102 CREATE SEQUENCE tmp_counter;
103 UPDATE warehouse SET sortkey = nextval('tmp_counter');
104 DROP SEQUENCE tmp_counter;
106 ALTER TABLE warehouse ADD COLUMN invalid boolean;
107 UPDATE warehouse SET invalid = 'f';
109 CREATE TRIGGER mtime_warehouse BEFORE UPDATE ON warehouse
110 FOR EACH ROW EXECUTE PROCEDURE set_mtime();
112 -- Table "transfer_type"
113 CREATE TABLE transfer_type (
114 id integer NOT NULL DEFAULT nextval('id'),
115 direction varchar(10) NOT NULL,
118 itime timestamp DEFAULT now(),
124 CREATE TRIGGER mtime_transfer_type BEFORE UPDATE ON transfer_type
125 FOR EACH ROW EXECUTE PROCEDURE set_mtime();
127 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('in', 'stock', 1);
128 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('in', 'found', 2);
129 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('in', 'correction', 3);
130 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('out', 'used', 4);
131 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('out', 'disposed', 5);
132 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('out', 'back', 6);
133 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('out', 'missing', 7);
134 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('out', 'correction', 9);
135 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('transfer', 'transfer', 10);
136 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('transfer', 'correction', 11);
138 -- Modifications to "inventory".
139 DELETE FROM inventory;
141 ALTER TABLE inventory ADD COLUMN bin_id integer;
142 ALTER TABLE inventory ADD FOREIGN KEY (bin_id) REFERENCES bin (id);
143 ALTER TABLE inventory ALTER COLUMN bin_id SET NOT NULL;
145 ALTER TABLE inventory DROP COLUMN qty;
146 ALTER TABLE inventory ADD COLUMN qty numeric(25, 5);
148 ALTER TABLE inventory ALTER COLUMN parts_id SET NOT NULL;
149 ALTER TABLE inventory ADD FOREIGN KEY (parts_id) REFERENCES parts(id);
151 ALTER TABLE inventory ALTER COLUMN warehouse_id SET NOT NULL;
152 ALTER TABLE inventory ADD FOREIGN KEY (warehouse_id) REFERENCES warehouse(id);
154 ALTER TABLE inventory ALTER COLUMN employee_id SET NOT NULL;
155 ALTER TABLE inventory ADD FOREIGN KEY (employee_id) REFERENCES employee (id);
157 ALTER TABLE inventory ADD COLUMN trans_id integer;
158 ALTER TABLE inventory ALTER COLUMN trans_id SET NOT NULL;
160 ALTER TABLE inventory ADD COLUMN trans_type_id integer;
161 ALTER TABLE inventory ALTER COLUMN trans_type_id SET NOT NULL;
162 ALTER TABLE inventory ADD FOREIGN KEY (trans_type_id) REFERENCES transfer_type (id);
164 ALTER TABLE inventory ADD COLUMN project_id integer;
165 ALTER TABLE inventory ADD FOREIGN KEY (project_id) REFERENCES project (id);
167 ALTER TABLE inventory ADD COLUMN chargenumber text;
168 ALTER TABLE inventory ADD COLUMN comment text;
170 -- Let "onhand" in "parts" be calculated automatically by a trigger.
171 SELECT id, onhand, bin INTO TEMP TABLE tmp_parts FROM parts WHERE onhand > 0;
172 ALTER TABLE parts DROP COLUMN onhand;
173 ALTER TABLE parts ADD COLUMN onhand numeric(25,5);
174 UPDATE parts SET onhand = COALESCE((SELECT SUM(qty) FROM inventory WHERE inventory.parts_id = parts.id), 0);
176 ALTER TABLE parts ADD COLUMN stockable boolean;
177 ALTER TABLE parts ALTER COLUMN stockable SET DEFAULT 'f';
178 UPDATE parts SET stockable = 'f';
180 CREATE OR REPLACE FUNCTION update_onhand() RETURNS trigger AS '
182 IF tg_op = ''INSERT'' THEN
183 UPDATE parts SET onhand = COALESCE(onhand, 0) + new.qty WHERE id = new.parts_id;
185 ELSIF tg_op = ''DELETE'' THEN
186 UPDATE parts SET onhand = COALESCE(onhand, 0) - old.qty WHERE id = old.parts_id;
189 UPDATE parts SET onhand = COALESCE(onhand, 0) - old.qty + new.qty WHERE id = old.parts_id;
195 CREATE TRIGGER trig_update_onhand
196 AFTER INSERT OR UPDATE OR DELETE ON inventory
197 FOR EACH ROW EXECUTE PROCEDURE update_onhand();
203 SELECT COUNT(id) FROM parts WHERE onhand > 0;