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 use strict;
   6
   7 die("This script cannot be run from the command line.") unless ($main::form);
   8
   9 # import vars from caller
  10 our ($dbup_locale, $dbup_myconfig, $dbh, $iconv);
  11 my $do_sql_migration = 0;
  12 my ($check_sql, $sqlcode);
  13
  14 sub mydberror {
  15   my ($msg) = @_;
  16   die($dbup_locale->text("Database update error:") .
  17       "<br>$msg<br>" . $DBI::errstr);
  18 }
  19
  20 sub do_query {
  21   my ($query, $may_fail) = @_;
  22
  23   if (!$dbh->do($query)) {
  24     mydberror($query) unless ($may_fail);
  25     $dbh->rollback();
  26     $dbh->begin_work();
  27   }
  28 }
  29
  30
  31 sub print_question {
  32   print $main::form->parse_html_template("dbupgrade/warehouse_form");
  33 }
  34
  35 sub do_update {
  36   if (!$main::form->{do_migrate}
  37       && (selectfirst_array_query($main::form, $dbh, $check_sql))[0]) { # check if update is needed
  38     print_question();
  39     return 2;
  40   } else {
  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}) {
  44         print_question();
  45         return 2;
  46       }
  47       # flag for extra code
  48       $do_sql_migration = 1;
  49     }
  50   }
  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";
  53
  54   $warehouse    = $dbh->quote($warehouse);
  55   $bin          = $dbh->quote($bin);
  56
  57   my $migration_code = <<EOF
  58
  59 -- Adjust warehouse
  60 INSERT INTO warehouse (description, sortkey, invalid) VALUES ($warehouse, 1, FALSE);
  61
  62 UPDATE tmp_parts SET bin = NULL WHERE bin = '';
  63
  64 -- Restore old onhand
  65 INSERT INTO bin
  66  (warehouse_id, description)
  67  (SELECT DISTINCT warehouse.id, COALESCE(bin, $bin)
  68    FROM warehouse, tmp_parts
  69    WHERE warehouse.description=$warehouse);
  70 INSERT INTO inventory
  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');
  77 EOF
  78 ;
  79
  80   # do standard code
  81   my $query  = $sqlcode;
  82      $query .= $migration_code if $do_sql_migration;
  83
  84   do_query($query);
  85
  86   return 1;
  87 }
  88
  89
  90
  91 $sqlcode = <<EOF
  92 -- Table "bin" for bins.
  93 CREATE TABLE bin (
  94   id integer NOT NULL DEFAULT nextval('id'),
  95   warehouse_id integer NOT NULL,
  96   description text,
  97   itime timestamp DEFAULT now(),
  98   mtime timestamp,
  99
 100   PRIMARY KEY (id),
 101   FOREIGN KEY (warehouse_id) REFERENCES warehouse (id)
 102 );
 103
 104 CREATE TRIGGER mtime_bin BEFORE UPDATE ON bin
 105     FOR EACH ROW EXECUTE PROCEDURE set_mtime();
 106
 107 -- Table "warehouse"
 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;
 112
 113 ALTER TABLE warehouse ADD COLUMN invalid boolean;
 114 UPDATE warehouse SET invalid = 'f';
 115
 116 CREATE TRIGGER mtime_warehouse BEFORE UPDATE ON warehouse
 117     FOR EACH ROW EXECUTE PROCEDURE set_mtime();
 118
 119 -- Table "transfer_type"
 120 CREATE TABLE transfer_type (
 121   id integer NOT NULL DEFAULT nextval('id'),
 122   direction varchar(10) NOT NULL,
 123   description text,
 124   sortkey integer,
 125   itime timestamp DEFAULT now(),
 126   mtime timestamp,
 127
 128   PRIMARY KEY (id)
 129 );
 130
 131 CREATE TRIGGER mtime_transfer_type BEFORE UPDATE ON transfer_type
 132     FOR EACH ROW EXECUTE PROCEDURE set_mtime();
 133
 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);
 144
 145 -- Modifications to "inventory".
 146 DELETE FROM inventory;
 147
 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;
 151
 152 ALTER TABLE inventory DROP COLUMN qty;
 153 ALTER TABLE inventory ADD COLUMN qty numeric(25, 5);
 154
 155 ALTER TABLE inventory ALTER COLUMN parts_id SET NOT NULL;
 156 ALTER TABLE inventory ADD FOREIGN KEY (parts_id) REFERENCES parts(id);
 157
 158 ALTER TABLE inventory ALTER COLUMN warehouse_id SET NOT NULL;
 159 ALTER TABLE inventory ADD FOREIGN KEY (warehouse_id) REFERENCES warehouse(id);
 160
 161 ALTER TABLE inventory ALTER COLUMN employee_id SET NOT NULL;
 162 ALTER TABLE inventory ADD FOREIGN KEY (employee_id) REFERENCES employee (id);
 163
 164 ALTER TABLE inventory ADD COLUMN trans_id integer;
 165 ALTER TABLE inventory ALTER COLUMN trans_id SET NOT NULL;
 166
 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);
 170
 171 ALTER TABLE inventory ADD COLUMN project_id integer;
 172 ALTER TABLE inventory ADD FOREIGN KEY (project_id) REFERENCES project (id);
 173
 174 ALTER TABLE inventory ADD COLUMN chargenumber text;
 175 ALTER TABLE inventory ADD COLUMN comment text;
 176
 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);
 182
 183 ALTER TABLE parts ADD COLUMN stockable boolean;
 184 ALTER TABLE parts ALTER COLUMN stockable SET DEFAULT 'f';
 185 UPDATE parts SET stockable = 'f';
 186
 187 CREATE OR REPLACE FUNCTION update_onhand() RETURNS trigger AS '
 188 BEGIN
 189   IF tg_op = ''INSERT'' THEN
 190     UPDATE parts SET onhand = COALESCE(onhand, 0) + new.qty WHERE id = new.parts_id;
 191     RETURN new;
 192   ELSIF tg_op = ''DELETE'' THEN
 193     UPDATE parts SET onhand = COALESCE(onhand, 0) - old.qty WHERE id = old.parts_id;
 194     RETURN old;
 195   ELSE
 196     UPDATE parts SET onhand = COALESCE(onhand, 0) - old.qty + new.qty WHERE id = old.parts_id;
 197     RETURN new;
 198   END IF;
 199 END;
 200 ' LANGUAGE plpgsql;
 201
 202 CREATE TRIGGER trig_update_onhand
 203   AFTER INSERT OR UPDATE OR DELETE ON inventory
 204   FOR EACH ROW EXECUTE PROCEDURE update_onhand();
 205 EOF
 206 ;
 207
 208
 209 $check_sql = <<EOF
 210 SELECT COUNT(id) FROM parts WHERE onhand > 0;
 211 EOF
 212 ;
 213
 214 return do_update();