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