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 my $do_sql_migration = 0;
  10 my ($check_sql, $sqlcode);
  11
  12 sub mydberror {
  13   my ($msg) = @_;
  14   die($dbup_locale->text("Database update error:") .
  15       "<br>$msg<br>" . $DBI::errstr);
  16 }
  17
  18 sub do_query {
  19   my ($query, $may_fail) = @_;
  20
  21   if (!$dbh->do($query)) {
  22     mydberror($query) unless ($may_fail);
  23     $dbh->rollback();
  24     $dbh->begin_work();
  25   }
  26 }
  27
  28
  29 sub print_question {
  30   print $main::form->parse_html_template("dbupgrade/warehouse_form");
  31 }
  32
  33 sub do_update {
  34   if (!$main::form->{do_migrate}
  35       && (selectfirst_array_query($main::form, $dbh, $check_sql))[0]) { # check if update is needed
  36     print_question();
  37     return 2;
  38   } else {
  39     if ($main::form->{do_migrate} eq 'Y') {
  40       # if yes, both warehouse and bin must be given
  41       if (!$main::form->{import_warehouse} || !$main::form->{bin_default}) {
  42         print_question();
  43         return 2;
  44       }
  45       # flag for extra code
  46       $do_sql_migration = 1;
  47     }
  48   }
  49   my $warehouse = $main::form->{import_warehouse} ne '' ? $main::form->{import_warehouse} : "Transfer";
  50   my $bin       = $main::form->{bin_default}      ne '' ? $main::form->{bin_default}      : "1";
  51
  52   $warehouse    = $dbh->quote($warehouse);
  53   $bin          = $dbh->quote($bin);
  54
  55   my $migration_code = <<EOF
  56
  57 -- Adjust warehouse
  58 INSERT INTO warehouse (description, sortkey, invalid) VALUES ($warehouse, 1, FALSE);
  59
  60 UPDATE tmp_parts SET bin = NULL WHERE bin = '';
  61
  62 -- Restore old onhand
  63 INSERT INTO bin
  64  (warehouse_id, description)
  65  (SELECT DISTINCT warehouse.id, COALESCE(bin, $bin)
  66    FROM warehouse, tmp_parts
  67    WHERE warehouse.description=$warehouse);
  68 INSERT INTO inventory
  69  (warehouse_id, parts_id, bin_id, qty, employee_id, trans_id, trans_type_id, chargenumber)
  70  (SELECT warehouse.id, tmp_parts.id, bin.id, onhand, (SELECT id FROM employee LIMIT 1), nextval('id'), transfer_type.id, ''
  71   FROM transfer_type, warehouse, tmp_parts, bin
  72   WHERE warehouse.description = $warehouse
  73     AND COALESCE(bin, $bin) = bin.description
  74     AND transfer_type.description = 'stock');
  75 EOF
  76 ;
  77
  78   # do standard code
  79   my $query  = $sqlcode;
  80      $query .= $migration_code if $do_sql_migration;
  81
  82   do_query($query);
  83
  84   return 1;
  85 }
  86
  87
  88
  89 $sqlcode = <<EOF
  90 -- Table "bin" for bins.
  91 CREATE TABLE bin (
  92   id integer NOT NULL DEFAULT nextval('id'),
  93   warehouse_id integer NOT NULL,
  94   description text,
  95   itime timestamp DEFAULT now(),
  96   mtime timestamp,
  97
  98   PRIMARY KEY (id),
  99   FOREIGN KEY (warehouse_id) REFERENCES warehouse (id)
 100 );
 101
 102 CREATE TRIGGER mtime_bin BEFORE UPDATE ON bin
 103     FOR EACH ROW EXECUTE PROCEDURE set_mtime();
 104
 105 -- Table "warehouse"
 106 ALTER TABLE warehouse ADD COLUMN sortkey integer;
 107 CREATE SEQUENCE tmp_counter;
 108 UPDATE warehouse SET sortkey = nextval('tmp_counter');
 109 DROP SEQUENCE tmp_counter;
 110
 111 ALTER TABLE warehouse ADD COLUMN invalid boolean;
 112 UPDATE warehouse SET invalid = 'f';
 113
 114 CREATE TRIGGER mtime_warehouse BEFORE UPDATE ON warehouse
 115     FOR EACH ROW EXECUTE PROCEDURE set_mtime();
 116
 117 -- Table "transfer_type"
 118 CREATE TABLE transfer_type (
 119   id integer NOT NULL DEFAULT nextval('id'),
 120   direction varchar(10) NOT NULL,
 121   description text,
 122   sortkey integer,
 123   itime timestamp DEFAULT now(),
 124   mtime timestamp,
 125
 126   PRIMARY KEY (id)
 127 );
 128
 129 CREATE TRIGGER mtime_transfer_type BEFORE UPDATE ON transfer_type
 130     FOR EACH ROW EXECUTE PROCEDURE set_mtime();
 131
 132 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('in', 'stock', 1);
 133 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('in', 'found', 2);
 134 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('in', 'correction', 3);
 135 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('out', 'used', 4);
 136 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('out', 'disposed', 5);
 137 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('out', 'back', 6);
 138 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('out', 'missing', 7);
 139 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('out', 'correction', 9);
 140 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('transfer', 'transfer', 10);
 141 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('transfer', 'correction', 11);
 142
 143 -- Modifications to "inventory".
 144 DELETE FROM inventory;
 145
 146 ALTER TABLE inventory ADD COLUMN bin_id integer;
 147 ALTER TABLE inventory ADD FOREIGN KEY (bin_id) REFERENCES bin (id);
 148 ALTER TABLE inventory ALTER COLUMN bin_id SET NOT NULL;
 149
 150 ALTER TABLE inventory DROP COLUMN qty;
 151 ALTER TABLE inventory ADD COLUMN qty numeric(25, 5);
 152
 153 ALTER TABLE inventory ALTER COLUMN parts_id SET NOT NULL;
 154 ALTER TABLE inventory ADD FOREIGN KEY (parts_id) REFERENCES parts(id);
 155
 156 ALTER TABLE inventory ALTER COLUMN warehouse_id SET NOT NULL;
 157 ALTER TABLE inventory ADD FOREIGN KEY (warehouse_id) REFERENCES warehouse(id);
 158
 159 ALTER TABLE inventory ALTER COLUMN employee_id SET NOT NULL;
 160 ALTER TABLE inventory ADD FOREIGN KEY (employee_id) REFERENCES employee (id);
 161
 162 ALTER TABLE inventory ADD COLUMN trans_id integer;
 163 ALTER TABLE inventory ALTER COLUMN trans_id SET NOT NULL;
 164
 165 ALTER TABLE inventory ADD COLUMN trans_type_id integer;
 166 ALTER TABLE inventory ALTER COLUMN trans_type_id SET NOT NULL;
 167 ALTER TABLE inventory ADD FOREIGN KEY (trans_type_id) REFERENCES transfer_type (id);
 168
 169 ALTER TABLE inventory ADD COLUMN project_id integer;
 170 ALTER TABLE inventory ADD FOREIGN KEY (project_id) REFERENCES project (id);
 171
 172 ALTER TABLE inventory ADD COLUMN chargenumber text;
 173 ALTER TABLE inventory ADD COLUMN comment text;
 174
 175 -- Let "onhand" in "parts" be calculated automatically by a trigger.
 176 SELECT id, onhand, bin INTO TEMP TABLE tmp_parts FROM parts WHERE onhand > 0;
 177 ALTER TABLE parts DROP COLUMN onhand;
 178 ALTER TABLE parts ADD COLUMN onhand numeric(25,5);
 179 UPDATE parts SET onhand = COALESCE((SELECT SUM(qty) FROM inventory WHERE inventory.parts_id = parts.id), 0);
 180
 181 ALTER TABLE parts ADD COLUMN stockable boolean;
 182 ALTER TABLE parts ALTER COLUMN stockable SET DEFAULT 'f';
 183 UPDATE parts SET stockable = 'f';
 184
 185 CREATE OR REPLACE FUNCTION update_onhand() RETURNS trigger AS '
 186 BEGIN
 187   IF tg_op = ''INSERT'' THEN
 188     UPDATE parts SET onhand = COALESCE(onhand, 0) + new.qty WHERE id = new.parts_id;
 189     RETURN new;
 190   ELSIF tg_op = ''DELETE'' THEN
 191     UPDATE parts SET onhand = COALESCE(onhand, 0) - old.qty WHERE id = old.parts_id;
 192     RETURN old;
 193   ELSE
 194     UPDATE parts SET onhand = COALESCE(onhand, 0) - old.qty + new.qty WHERE id = old.parts_id;
 195     RETURN new;
 196   END IF;
 197 END;
 198 ' LANGUAGE plpgsql;
 199
 200 CREATE TRIGGER trig_update_onhand
 201   AFTER INSERT OR UPDATE OR DELETE ON inventory
 202   FOR EACH ROW EXECUTE PROCEDURE update_onhand();
 203 EOF
 204 ;
 205
 206
 207 $check_sql = <<EOF
 208 SELECT COUNT(id) FROM parts WHERE onhand > 0;
 209 EOF
 210 ;
 211
 212 return do_update();