2 # @description: Diverse neue Tabellen und Spalten zur Mehrlagerfähigkeit inkl. Migration
3 # @depends: release_2_4_3
7 die("This script cannot be run from the command line.") unless ($main::form);
9 # import vars from caller
10 our ($dbup_locale, $dbup_myconfig, $dbh, $iconv);
11 my $do_sql_migration = 0;
12 my ($check_sql, $sqlcode);
16 die($dbup_locale->text("Database update error:") .
17 "<br>$msg<br>" . $DBI::errstr);
21 my ($query, $may_fail) = @_;
23 if (!$dbh->do($query)) {
24 mydberror($query) unless ($may_fail);
32 print $main::form->parse_html_template("dbupgrade/warehouse_form");
36 if (!$main::form->{do_migrate}
37 && (selectfirst_array_query($main::form, $dbh, $check_sql))[0]) { # check if update is needed
41 if ($main::form->{do_migrate} eq 'Y') {
42 # if yes, both warehouse and bin must be given
43 if (!$main::form->{import_warehouse} || !$main::form->{bin_default}) {
48 $do_sql_migration = 1;
51 my $warehouse = $main::form->{import_warehouse} ne '' ? $main::form->{import_warehouse} : "Transfer";
52 my $bin = $main::form->{bin_default} ne '' ? $main::form->{bin_default} : "1";
54 $warehouse = $dbh->quote($warehouse);
55 $bin = $dbh->quote($bin);
57 my $migration_code = <<EOF
60 INSERT INTO warehouse (description, sortkey, invalid) VALUES ($warehouse, 1, FALSE);
62 UPDATE tmp_parts SET bin = NULL WHERE bin = '';
66 (warehouse_id, description)
67 (SELECT DISTINCT warehouse.id, COALESCE(bin, $bin)
68 FROM warehouse, tmp_parts
69 WHERE warehouse.description=$warehouse);
71 (warehouse_id, parts_id, bin_id, qty, employee_id, trans_id, trans_type_id, chargenumber)
72 (SELECT warehouse.id, tmp_parts.id, bin.id, onhand, (SELECT id FROM employee LIMIT 1), nextval('id'), transfer_type.id, ''
73 FROM transfer_type, warehouse, tmp_parts, bin
74 WHERE warehouse.description = $warehouse
75 AND COALESCE(bin, $bin) = bin.description
76 AND transfer_type.description = 'stock');
82 $query .= $migration_code if $do_sql_migration;
92 -- Table "bin" for bins.
94 id integer NOT NULL DEFAULT nextval('id'),
95 warehouse_id integer NOT NULL,
97 itime timestamp DEFAULT now(),
101 FOREIGN KEY (warehouse_id) REFERENCES warehouse (id)
104 CREATE TRIGGER mtime_bin BEFORE UPDATE ON bin
105 FOR EACH ROW EXECUTE PROCEDURE set_mtime();
108 ALTER TABLE warehouse ADD COLUMN sortkey integer;
109 CREATE SEQUENCE tmp_counter;
110 UPDATE warehouse SET sortkey = nextval('tmp_counter');
111 DROP SEQUENCE tmp_counter;
113 ALTER TABLE warehouse ADD COLUMN invalid boolean;
114 UPDATE warehouse SET invalid = 'f';
116 CREATE TRIGGER mtime_warehouse BEFORE UPDATE ON warehouse
117 FOR EACH ROW EXECUTE PROCEDURE set_mtime();
119 -- Table "transfer_type"
120 CREATE TABLE transfer_type (
121 id integer NOT NULL DEFAULT nextval('id'),
122 direction varchar(10) NOT NULL,
125 itime timestamp DEFAULT now(),
131 CREATE TRIGGER mtime_transfer_type BEFORE UPDATE ON transfer_type
132 FOR EACH ROW EXECUTE PROCEDURE set_mtime();
134 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('in', 'stock', 1);
135 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('in', 'found', 2);
136 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('in', 'correction', 3);
137 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('out', 'used', 4);
138 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('out', 'disposed', 5);
139 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('out', 'back', 6);
140 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('out', 'missing', 7);
141 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('out', 'correction', 9);
142 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('transfer', 'transfer', 10);
143 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('transfer', 'correction', 11);
145 -- Modifications to "inventory".
146 DELETE FROM inventory;
148 ALTER TABLE inventory ADD COLUMN bin_id integer;
149 ALTER TABLE inventory ADD FOREIGN KEY (bin_id) REFERENCES bin (id);
150 ALTER TABLE inventory ALTER COLUMN bin_id SET NOT NULL;
152 ALTER TABLE inventory DROP COLUMN qty;
153 ALTER TABLE inventory ADD COLUMN qty numeric(25, 5);
155 ALTER TABLE inventory ALTER COLUMN parts_id SET NOT NULL;
156 ALTER TABLE inventory ADD FOREIGN KEY (parts_id) REFERENCES parts(id);
158 ALTER TABLE inventory ALTER COLUMN warehouse_id SET NOT NULL;
159 ALTER TABLE inventory ADD FOREIGN KEY (warehouse_id) REFERENCES warehouse(id);
161 ALTER TABLE inventory ALTER COLUMN employee_id SET NOT NULL;
162 ALTER TABLE inventory ADD FOREIGN KEY (employee_id) REFERENCES employee (id);
164 ALTER TABLE inventory ADD COLUMN trans_id integer;
165 ALTER TABLE inventory ALTER COLUMN trans_id SET NOT NULL;
167 ALTER TABLE inventory ADD COLUMN trans_type_id integer;
168 ALTER TABLE inventory ALTER COLUMN trans_type_id SET NOT NULL;
169 ALTER TABLE inventory ADD FOREIGN KEY (trans_type_id) REFERENCES transfer_type (id);
171 ALTER TABLE inventory ADD COLUMN project_id integer;
172 ALTER TABLE inventory ADD FOREIGN KEY (project_id) REFERENCES project (id);
174 ALTER TABLE inventory ADD COLUMN chargenumber text;
175 ALTER TABLE inventory ADD COLUMN comment text;
177 -- Let "onhand" in "parts" be calculated automatically by a trigger.
178 SELECT id, onhand, bin INTO TEMP TABLE tmp_parts FROM parts WHERE onhand > 0;
179 ALTER TABLE parts DROP COLUMN onhand;
180 ALTER TABLE parts ADD COLUMN onhand numeric(25,5);
181 UPDATE parts SET onhand = COALESCE((SELECT SUM(qty) FROM inventory WHERE inventory.parts_id = parts.id), 0);
183 ALTER TABLE parts ADD COLUMN stockable boolean;
184 ALTER TABLE parts ALTER COLUMN stockable SET DEFAULT 'f';
185 UPDATE parts SET stockable = 'f';
187 CREATE OR REPLACE FUNCTION update_onhand() RETURNS trigger AS '
189 IF tg_op = ''INSERT'' THEN
190 UPDATE parts SET onhand = COALESCE(onhand, 0) + new.qty WHERE id = new.parts_id;
192 ELSIF tg_op = ''DELETE'' THEN
193 UPDATE parts SET onhand = COALESCE(onhand, 0) - old.qty WHERE id = old.parts_id;
196 UPDATE parts SET onhand = COALESCE(onhand, 0) - old.qty + new.qty WHERE id = old.parts_id;
202 CREATE TRIGGER trig_update_onhand
203 AFTER INSERT OR UPDATE OR DELETE ON inventory
204 FOR EACH ROW EXECUTE PROCEDURE update_onhand();
210 SELECT COUNT(id) FROM parts WHERE onhand > 0;