# @description: Diverse neue Tabellen und Spalten zur Mehrlagerfähigkeit inkl. Migration
# @depends: release_2_4_3
+use strict;
die("This script cannot be run from the command line.") unless ($main::form);
-$do_sql_migration = 0;
+
+my $do_sql_migration = 0;
+my ($check_sql, $sqlcode);
+
+sub mydberror {
+ my ($msg) = @_;
+ die($dbup_locale->text("Database update error:") .
+ "<br>$msg<br>" . $DBI::errstr);
+}
+
+sub do_query {
+ my ($query, $may_fail) = @_;
+
+ if (!$dbh->do($query)) {
+ mydberror($query) unless ($may_fail);
+ $dbh->rollback();
+ $dbh->begin_work();
+ }
+}
+
sub print_question {
print $main::form->parse_html_template("dbupgrade/warehouse_form");
my $warehouse = $main::form->{import_warehouse} ne '' ? $main::form->{import_warehouse} : "Transfer";
my $bin = $main::form->{bin_default} ne '' ? $main::form->{bin_default} : "1";
+ $warehouse = $dbh->quote($warehouse);
+ $bin = $dbh->quote($bin);
my $migration_code = <<EOF
--- Warehouse anpassen
-INSERT INTO warehouse (description) VALUES ('$warehouse');
+-- Adjust warehouse
+INSERT INTO warehouse (description, sortkey, invalid) VALUES ($warehouse, 1, FALSE);
UPDATE tmp_parts SET bin = NULL WHERE bin = '';
--- Warenbestand wiederherstellen
-INSERT INTO bin
- (warehouse_id, description)
- (SELECT DISTINCT warehouse.id, COALESCE(bin, '$bin')
- FROM warehouse, tmp_parts
- WHERE warehouse.description='$warehouse');
-INSERT INTO inventory
- (warehouse_id, parts_id, bin_id, qty, employee_id, trans_id, trans_type_id)
- (SELECT warehouse.id, tmp_parts.id, bin.id, onhand, (SELECT id FROM employee LIMIT 1), nextval('id'), transfer_type.id
+-- Restore old onhand
+INSERT INTO bin
+ (warehouse_id, description)
+ (SELECT DISTINCT warehouse.id, COALESCE(bin, $bin)
+ FROM warehouse, tmp_parts
+ WHERE warehouse.description=$warehouse);
+INSERT INTO inventory
+ (warehouse_id, parts_id, bin_id, qty, employee_id, trans_id, trans_type_id, chargenumber)
+ (SELECT warehouse.id, tmp_parts.id, bin.id, onhand, (SELECT id FROM employee LIMIT 1), nextval('id'), transfer_type.id, ''
FROM transfer_type, warehouse, tmp_parts, bin
- WHERE warehouse.description = '$warehouse'
- AND COALESCE(bin, '$bin') = bin.description
+ WHERE warehouse.description = $warehouse
+ AND COALESCE(bin, $bin) = bin.description
AND transfer_type.description = 'stock');
EOF
;
my $query = $sqlcode;
$query .= $migration_code if $do_sql_migration;
- do_query($main::form, $dbh, $query);
+ do_query($query);
return 1;
}
$sqlcode = <<EOF
--- Tabelle "bin" für Lagerplätze.
+-- Table "bin" for bins.
CREATE TABLE bin (
id integer NOT NULL DEFAULT nextval('id'),
warehouse_id integer NOT NULL,
CREATE TRIGGER mtime_bin BEFORE UPDATE ON bin
FOR EACH ROW EXECUTE PROCEDURE set_mtime();
--- Tabelle "warehouse"
+-- Table "warehouse"
ALTER TABLE warehouse ADD COLUMN sortkey integer;
CREATE SEQUENCE tmp_counter;
UPDATE warehouse SET sortkey = nextval('tmp_counter');
CREATE TRIGGER mtime_warehouse BEFORE UPDATE ON warehouse
FOR EACH ROW EXECUTE PROCEDURE set_mtime();
--- Tabelle "transfer_type"
+-- Table "transfer_type"
CREATE TABLE transfer_type (
id integer NOT NULL DEFAULT nextval('id'),
direction varchar(10) NOT NULL,
INSERT INTO transfer_type (direction, description, sortkey) VALUES ('transfer', 'transfer', 10);
INSERT INTO transfer_type (direction, description, sortkey) VALUES ('transfer', 'correction', 11);
--- Anpassungen an "inventory".
+-- Modifications to "inventory".
DELETE FROM inventory;
ALTER TABLE inventory ADD COLUMN bin_id integer;
ALTER TABLE inventory ADD COLUMN chargenumber text;
ALTER TABLE inventory ADD COLUMN comment text;
--- "onhand" in "parts" über einen Trigger automatisch berechnen lassen.
--- Vorher Warenbestand sichern JZ
+-- Let "onhand" in "parts" be calculated automatically by a trigger.
SELECT id, onhand, bin INTO TEMP TABLE tmp_parts FROM parts WHERE onhand > 0;
ALTER TABLE parts DROP COLUMN onhand;
ALTER TABLE parts ADD COLUMN onhand numeric(25,5);