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