sql/Pg-upgrade2/warehouse.pl

   1 # @tag: warehouse
   2 # @description:  Diverse neue Tabellen und Spalten zur Mehrlagerf&auml;higkeit inkl. Migration
   3 # @depends: release_2_4_3
   4
   5
   6 die("This script cannot be run from the command line.") unless ($main::form);
   7
   8 sub mydberror {
   9   my ($msg) = @_;
  10   die($dbup_locale->text("Database update error:") .
  11       "<br>$msg<br>" . $DBI::errstr);
  12 }
  13
  14 sub do_query {
  15   my ($query, $may_fail) = @_;
  16
  17   if (!$dbh->do($query)) {
  18     mydberror($query) unless ($may_fail);
  19     $dbh->rollback();
  20     $dbh->begin_work();
  21   }
  22 }
  23
  24 $do_sql_migration = 0;
  25
  26 sub print_question {
  27   print $main::form->parse_html_template("dbupgrade/warehouse_form");
  28 }
  29
  30 sub do_update {
  31   if (!$main::form->{do_migrate}
  32       && (selectfirst_array_query($main::form, $dbh, $check_sql))[0]) { # check if update is needed
  33     print_question();
  34     return 2;
  35   } else {
  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}) {
  39         print_question();
  40         return 2;
  41       }
  42       # flag for extra code
  43       $do_sql_migration = 1;
  44     }
  45   }
  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";
  48
  49
  50   my $migration_code = <<EOF
  51
  52 -- Adjust warehouse
  53 INSERT INTO warehouse (description) VALUES ('$warehouse');
  54
  55 UPDATE tmp_parts SET bin = NULL WHERE bin = '';
  56
  57 -- Restore old onhand
  58 INSERT INTO bin
  59  (warehouse_id, description)
  60  (SELECT DISTINCT warehouse.id, COALESCE(bin, '$bin')
  61    FROM warehouse, tmp_parts
  62    WHERE warehouse.description='$warehouse');
  63 INSERT INTO inventory
  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');
  70 EOF
  71 ;
  72
  73   # do standard code
  74   my $query  = $sqlcode;
  75      $query .= $migration_code if $do_sql_migration;
  76
  77   do_query($query);
  78
  79   return 1;
  80 }
  81
  82
  83
  84 $sqlcode = <<EOF
  85 -- Table "bin" for bins.
  86 CREATE TABLE bin (
  87   id integer NOT NULL DEFAULT nextval('id'),
  88   warehouse_id integer NOT NULL,
  89   description text,
  90   itime timestamp DEFAULT now(),
  91   mtime timestamp,
  92
  93   PRIMARY KEY (id),
  94   FOREIGN KEY (warehouse_id) REFERENCES warehouse (id)
  95 );
  96
  97 CREATE TRIGGER mtime_bin BEFORE UPDATE ON bin
  98     FOR EACH ROW EXECUTE PROCEDURE set_mtime();
  99
 100 -- Table "warehouse"
 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;
 105
 106 ALTER TABLE warehouse ADD COLUMN invalid boolean;
 107 UPDATE warehouse SET invalid = 'f';
 108
 109 CREATE TRIGGER mtime_warehouse BEFORE UPDATE ON warehouse
 110     FOR EACH ROW EXECUTE PROCEDURE set_mtime();
 111
 112 -- Table "transfer_type"
 113 CREATE TABLE transfer_type (
 114   id integer NOT NULL DEFAULT nextval('id'),
 115   direction varchar(10) NOT NULL,
 116   description text,
 117   sortkey integer,
 118   itime timestamp DEFAULT now(),
 119   mtime timestamp,
 120
 121   PRIMARY KEY (id)
 122 );
 123
 124 CREATE TRIGGER mtime_transfer_type BEFORE UPDATE ON transfer_type
 125     FOR EACH ROW EXECUTE PROCEDURE set_mtime();
 126
 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);
 137
 138 -- Modifications to "inventory".
 139 DELETE FROM inventory;
 140
 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;
 144
 145 ALTER TABLE inventory DROP COLUMN qty;
 146 ALTER TABLE inventory ADD COLUMN qty numeric(25, 5);
 147
 148 ALTER TABLE inventory ALTER COLUMN parts_id SET NOT NULL;
 149 ALTER TABLE inventory ADD FOREIGN KEY (parts_id) REFERENCES parts(id);
 150
 151 ALTER TABLE inventory ALTER COLUMN warehouse_id SET NOT NULL;
 152 ALTER TABLE inventory ADD FOREIGN KEY (warehouse_id) REFERENCES warehouse(id);
 153
 154 ALTER TABLE inventory ALTER COLUMN employee_id SET NOT NULL;
 155 ALTER TABLE inventory ADD FOREIGN KEY (employee_id) REFERENCES employee (id);
 156
 157 ALTER TABLE inventory ADD COLUMN trans_id integer;
 158 ALTER TABLE inventory ALTER COLUMN trans_id SET NOT NULL;
 159
 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);
 163
 164 ALTER TABLE inventory ADD COLUMN project_id integer;
 165 ALTER TABLE inventory ADD FOREIGN KEY (project_id) REFERENCES project (id);
 166
 167 ALTER TABLE inventory ADD COLUMN chargenumber text;
 168 ALTER TABLE inventory ADD COLUMN comment text;
 169
 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);
 175
 176 ALTER TABLE parts ADD COLUMN stockable boolean;
 177 ALTER TABLE parts ALTER COLUMN stockable SET DEFAULT 'f';
 178 UPDATE parts SET stockable = 'f';
 179
 180 CREATE OR REPLACE FUNCTION update_onhand() RETURNS trigger AS '
 181 BEGIN
 182   IF tg_op = ''INSERT'' THEN
 183     UPDATE parts SET onhand = COALESCE(onhand, 0) + new.qty WHERE id = new.parts_id;
 184     RETURN new;
 185   ELSIF tg_op = ''DELETE'' THEN
 186     UPDATE parts SET onhand = COALESCE(onhand, 0) - old.qty WHERE id = old.parts_id;
 187     RETURN old;
 188   ELSE
 189     UPDATE parts SET onhand = COALESCE(onhand, 0) - old.qty + new.qty WHERE id = old.parts_id;
 190     RETURN new;
 191   END IF;
 192 END;
 193 ' LANGUAGE plpgsql;
 194
 195 CREATE TRIGGER trig_update_onhand
 196   AFTER INSERT OR UPDATE OR DELETE ON inventory
 197   FOR EACH ROW EXECUTE PROCEDURE update_onhand();
 198 EOF
 199 ;
 200
 201
 202 $check_sql = <<EOF
 203 SELECT COUNT(id) FROM parts WHERE onhand > 0;
 204 EOF
 205 ;
 206
 207 return do_update();