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   $warehouse    = $dbh->quote($warehouse);
  50   $bin          = $dbh->quote($bin);
  51
  52   my $migration_code = <<EOF
  53
  54 -- Adjust warehouse
  55 INSERT INTO warehouse (description, sortkey, invalid) VALUES ($warehouse, 1, FALSE);
  56
  57 UPDATE tmp_parts SET bin = NULL WHERE bin = '';
  58
  59 -- Restore old onhand
  60 INSERT INTO bin
  61  (warehouse_id, description)
  62  (SELECT DISTINCT warehouse.id, COALESCE(bin, $bin)
  63    FROM warehouse, tmp_parts
  64    WHERE warehouse.description=$warehouse);
  65 INSERT INTO inventory
  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');
  72 EOF
  73 ;
  74
  75   # do standard code
  76   my $query  = $sqlcode;
  77      $query .= $migration_code if $do_sql_migration;
  78
  79   do_query($query);
  80
  81   return 1;
  82 }
  83
  84
  85
  86 $sqlcode = <<EOF
  87 -- Table "bin" for bins.
  88 CREATE TABLE bin (
  89   id integer NOT NULL DEFAULT nextval('id'),
  90   warehouse_id integer NOT NULL,
  91   description text,
  92   itime timestamp DEFAULT now(),
  93   mtime timestamp,
  94
  95   PRIMARY KEY (id),
  96   FOREIGN KEY (warehouse_id) REFERENCES warehouse (id)
  97 );
  98
  99 CREATE TRIGGER mtime_bin BEFORE UPDATE ON bin
 100     FOR EACH ROW EXECUTE PROCEDURE set_mtime();
 101
 102 -- Table "warehouse"
 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;
 107
 108 ALTER TABLE warehouse ADD COLUMN invalid boolean;
 109 UPDATE warehouse SET invalid = 'f';
 110
 111 CREATE TRIGGER mtime_warehouse BEFORE UPDATE ON warehouse
 112     FOR EACH ROW EXECUTE PROCEDURE set_mtime();
 113
 114 -- Table "transfer_type"
 115 CREATE TABLE transfer_type (
 116   id integer NOT NULL DEFAULT nextval('id'),
 117   direction varchar(10) NOT NULL,
 118   description text,
 119   sortkey integer,
 120   itime timestamp DEFAULT now(),
 121   mtime timestamp,
 122
 123   PRIMARY KEY (id)
 124 );
 125
 126 CREATE TRIGGER mtime_transfer_type BEFORE UPDATE ON transfer_type
 127     FOR EACH ROW EXECUTE PROCEDURE set_mtime();
 128
 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);
 139
 140 -- Modifications to "inventory".
 141 DELETE FROM inventory;
 142
 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;
 146
 147 ALTER TABLE inventory DROP COLUMN qty;
 148 ALTER TABLE inventory ADD COLUMN qty numeric(25, 5);
 149
 150 ALTER TABLE inventory ALTER COLUMN parts_id SET NOT NULL;
 151 ALTER TABLE inventory ADD FOREIGN KEY (parts_id) REFERENCES parts(id);
 152
 153 ALTER TABLE inventory ALTER COLUMN warehouse_id SET NOT NULL;
 154 ALTER TABLE inventory ADD FOREIGN KEY (warehouse_id) REFERENCES warehouse(id);
 155
 156 ALTER TABLE inventory ALTER COLUMN employee_id SET NOT NULL;
 157 ALTER TABLE inventory ADD FOREIGN KEY (employee_id) REFERENCES employee (id);
 158
 159 ALTER TABLE inventory ADD COLUMN trans_id integer;
 160 ALTER TABLE inventory ALTER COLUMN trans_id SET NOT NULL;
 161
 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);
 165
 166 ALTER TABLE inventory ADD COLUMN project_id integer;
 167 ALTER TABLE inventory ADD FOREIGN KEY (project_id) REFERENCES project (id);
 168
 169 ALTER TABLE inventory ADD COLUMN chargenumber text;
 170 ALTER TABLE inventory ADD COLUMN comment text;
 171
 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);
 177
 178 ALTER TABLE parts ADD COLUMN stockable boolean;
 179 ALTER TABLE parts ALTER COLUMN stockable SET DEFAULT 'f';
 180 UPDATE parts SET stockable = 'f';
 181
 182 CREATE OR REPLACE FUNCTION update_onhand() RETURNS trigger AS '
 183 BEGIN
 184   IF tg_op = ''INSERT'' THEN
 185     UPDATE parts SET onhand = COALESCE(onhand, 0) + new.qty WHERE id = new.parts_id;
 186     RETURN new;
 187   ELSIF tg_op = ''DELETE'' THEN
 188     UPDATE parts SET onhand = COALESCE(onhand, 0) - old.qty WHERE id = old.parts_id;
 189     RETURN old;
 190   ELSE
 191     UPDATE parts SET onhand = COALESCE(onhand, 0) - old.qty + new.qty WHERE id = old.parts_id;
 192     RETURN new;
 193   END IF;
 194 END;
 195 ' LANGUAGE plpgsql;
 196
 197 CREATE TRIGGER trig_update_onhand
 198   AFTER INSERT OR UPDATE OR DELETE ON inventory
 199   FOR EACH ROW EXECUTE PROCEDURE update_onhand();
 200 EOF
 201 ;
 202
 203
 204 $check_sql = <<EOF
 205 SELECT COUNT(id) FROM parts WHERE onhand > 0;
 206 EOF
 207 ;
 208
 209 return do_update();