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";
49 $warehouse = $dbh->quote($warehouse);
50 $bin = $dbh->quote($bin);
52 my $migration_code = <<EOF
55 INSERT INTO warehouse (description, sortkey, invalid) VALUES ($warehouse, 1, FALSE);
57 UPDATE tmp_parts SET bin = NULL WHERE bin = '';
61 (warehouse_id, description)
62 (SELECT DISTINCT warehouse.id, COALESCE(bin, $bin)
63 FROM warehouse, tmp_parts
64 WHERE warehouse.description=$warehouse);
66 (warehouse_id, parts_id, bin_id, qty, employee_id, trans_id, trans_type_id, chargenumber)
67 (SELECT warehouse.id, tmp_parts.id, bin.id, onhand, (SELECT id FROM employee LIMIT 1), nextval('id'), transfer_type.id, ''
68 FROM transfer_type, warehouse, tmp_parts, bin
69 WHERE warehouse.description = $warehouse
70 AND COALESCE(bin, $bin) = bin.description
71 AND transfer_type.description = 'stock');
77 $query .= $migration_code if $do_sql_migration;
87 -- Table "bin" for bins.
89 id integer NOT NULL DEFAULT nextval('id'),
90 warehouse_id integer NOT NULL,
92 itime timestamp DEFAULT now(),
96 FOREIGN KEY (warehouse_id) REFERENCES warehouse (id)
99 CREATE TRIGGER mtime_bin BEFORE UPDATE ON bin
100 FOR EACH ROW EXECUTE PROCEDURE set_mtime();
103 ALTER TABLE warehouse ADD COLUMN sortkey integer;
104 CREATE SEQUENCE tmp_counter;
105 UPDATE warehouse SET sortkey = nextval('tmp_counter');
106 DROP SEQUENCE tmp_counter;
108 ALTER TABLE warehouse ADD COLUMN invalid boolean;
109 UPDATE warehouse SET invalid = 'f';
111 CREATE TRIGGER mtime_warehouse BEFORE UPDATE ON warehouse
112 FOR EACH ROW EXECUTE PROCEDURE set_mtime();
114 -- Table "transfer_type"
115 CREATE TABLE transfer_type (
116 id integer NOT NULL DEFAULT nextval('id'),
117 direction varchar(10) NOT NULL,
120 itime timestamp DEFAULT now(),
126 CREATE TRIGGER mtime_transfer_type BEFORE UPDATE ON transfer_type
127 FOR EACH ROW EXECUTE PROCEDURE set_mtime();
129 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('in', 'stock', 1);
130 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('in', 'found', 2);
131 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('in', 'correction', 3);
132 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('out', 'used', 4);
133 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('out', 'disposed', 5);
134 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('out', 'back', 6);
135 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('out', 'missing', 7);
136 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('out', 'correction', 9);
137 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('transfer', 'transfer', 10);
138 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('transfer', 'correction', 11);
140 -- Modifications to "inventory".
141 DELETE FROM inventory;
143 ALTER TABLE inventory ADD COLUMN bin_id integer;
144 ALTER TABLE inventory ADD FOREIGN KEY (bin_id) REFERENCES bin (id);
145 ALTER TABLE inventory ALTER COLUMN bin_id SET NOT NULL;
147 ALTER TABLE inventory DROP COLUMN qty;
148 ALTER TABLE inventory ADD COLUMN qty numeric(25, 5);
150 ALTER TABLE inventory ALTER COLUMN parts_id SET NOT NULL;
151 ALTER TABLE inventory ADD FOREIGN KEY (parts_id) REFERENCES parts(id);
153 ALTER TABLE inventory ALTER COLUMN warehouse_id SET NOT NULL;
154 ALTER TABLE inventory ADD FOREIGN KEY (warehouse_id) REFERENCES warehouse(id);
156 ALTER TABLE inventory ALTER COLUMN employee_id SET NOT NULL;
157 ALTER TABLE inventory ADD FOREIGN KEY (employee_id) REFERENCES employee (id);
159 ALTER TABLE inventory ADD COLUMN trans_id integer;
160 ALTER TABLE inventory ALTER COLUMN trans_id SET NOT NULL;
162 ALTER TABLE inventory ADD COLUMN trans_type_id integer;
163 ALTER TABLE inventory ALTER COLUMN trans_type_id SET NOT NULL;
164 ALTER TABLE inventory ADD FOREIGN KEY (trans_type_id) REFERENCES transfer_type (id);
166 ALTER TABLE inventory ADD COLUMN project_id integer;
167 ALTER TABLE inventory ADD FOREIGN KEY (project_id) REFERENCES project (id);
169 ALTER TABLE inventory ADD COLUMN chargenumber text;
170 ALTER TABLE inventory ADD COLUMN comment text;
172 -- Let "onhand" in "parts" be calculated automatically by a trigger.
173 SELECT id, onhand, bin INTO TEMP TABLE tmp_parts FROM parts WHERE onhand > 0;
174 ALTER TABLE parts DROP COLUMN onhand;
175 ALTER TABLE parts ADD COLUMN onhand numeric(25,5);
176 UPDATE parts SET onhand = COALESCE((SELECT SUM(qty) FROM inventory WHERE inventory.parts_id = parts.id), 0);
178 ALTER TABLE parts ADD COLUMN stockable boolean;
179 ALTER TABLE parts ALTER COLUMN stockable SET DEFAULT 'f';
180 UPDATE parts SET stockable = 'f';
182 CREATE OR REPLACE FUNCTION update_onhand() RETURNS trigger AS '
184 IF tg_op = ''INSERT'' THEN
185 UPDATE parts SET onhand = COALESCE(onhand, 0) + new.qty WHERE id = new.parts_id;
187 ELSIF tg_op = ''DELETE'' THEN
188 UPDATE parts SET onhand = COALESCE(onhand, 0) - old.qty WHERE id = old.parts_id;
191 UPDATE parts SET onhand = COALESCE(onhand, 0) - old.qty + new.qty WHERE id = old.parts_id;
197 CREATE TRIGGER trig_update_onhand
198 AFTER INSERT OR UPDATE OR DELETE ON inventory
199 FOR EACH ROW EXECUTE PROCEDURE update_onhand();
205 SELECT COUNT(id) FROM parts WHERE onhand > 0;