projekt kivitendo

  'As a result, the saved onhand values of the present goods can be stored into a warehouse designated by you, or will be reset for a proper warehouse tracking' => 'Als Konsequenz können die gespeicherten Mengen entweder in ein Lager überführt werden, oder für eine frische Lagerverwaltung resettet werden.',

  'Do you want to store the existing onhand values into a new warehouse?' => 'Möchten Sie die vorhandenen Mengendaten in ein Lager übertragen?',

  'Lx-Office is now able to manage warehouses instead of just tracking the amount of goods in your system.' => 'Lx-Office enthält jetzt auch echte Lagerverwaultung anstatt reiner Mengenzählung.',

  'Please specify a description for the warehouse designated for these goods.' => 'Bitte geben Sie den Namen des Ziellagers für die übernommenen Daten ein.',

  'Since bin is not enforced in the parts data, please specify a bin where goods without a specified bin will be put.' => 'Da Lagerplätze kein Pflichtfeld sind, geben Sie bitte einen Lagerplatz an, in dem Waren ohne spezifizierten Lagerplatz eingelagert werden sollen.',

  'This update will change the nature the onhand of goods is tracked.' => 'Dieses update ändert die Art und Weise wie Lagermengen gezält werden.',

  'Warehouse MIgration'         => 'Lagermigration',

# @tag: warehouse

# @description:  Diverse neue Tabellen und Spalten zur Mehrlagerfähigkeit inkl. Migration

# @depends: release_2_4_3

die("This script cannot be run from the command line.") unless ($main::form);

$do_sql_migration = 0;

sub print_question {

  print $main::form->parse_html_template("dbupgrade/warehouse_form");

}

sub do_update {

  if (!$main::form->{do_migrate}

      && (selectfirst_array_query($main::form, $dbh, $check_sql))[0]) { # check if update is needed

    print_question();

    return 2;

  } else {

    if ($main::form->{do_migrate} eq 'Y') {

      # if yes, both warehouse and bin must be given

      if (!$main::form->{import_warehouse} || !$main::form->{bin_default}) {

        print_question();

        return 2;

      }

      # flag for extra code

      $do_sql_migration = 1;

    }

  }

  my $warehouse = $main::form->{import_warehouse} ne '' ? $main::form->{import_warehouse} : "Transfer";

  my $bin       = $main::form->{bin_default}      ne '' ? $main::form->{bin_default}      : "1";

  my $migration_code = <<EOF

-- Warehouse anpassen

INSERT INTO warehouse (description) VALUES ('$warehouse');

UPDATE tmp_parts SET bin = NULL WHERE bin = '';

-- Warenbestand wiederherstellen

INSERT INTO bin 

 (warehouse_id, description) 

 (SELECT DISTINCT warehouse.id, COALESCE(bin, '$bin') 

   FROM warehouse, tmp_parts 

   WHERE warehouse.description='$warehouse');

INSERT INTO inventory 

 (warehouse_id, parts_id, bin_id, qty, employee_id, trans_id, trans_type_id)

 (SELECT warehouse.id, tmp_parts.id, bin.id, onhand, (SELECT id FROM employee LIMIT 1), nextval('id'), transfer_type.id 

  FROM transfer_type, warehouse, tmp_parts, bin

  WHERE warehouse.description = '$warehouse' 

    AND COALESCE(bin, '$bin') = bin.description 

    AND transfer_type.description = 'stock');

EOF

;

  # do standard code

  my $query  = $sqlcode;

     $query .= $migration_code if $do_sql_migration;

  do_query($main::form, $dbh, $query);

  return 1;

}

$sqlcode = <<EOF

-- Tabelle "bin" f?r Lagerpl?tze.

CREATE TABLE bin (

  id integer NOT NULL DEFAULT nextval('id'),

  warehouse_id integer NOT NULL,

  description text,

  itime timestamp DEFAULT now(),

  mtime timestamp,

  PRIMARY KEY (id),

  FOREIGN KEY (warehouse_id) REFERENCES warehouse (id)

);

CREATE TRIGGER mtime_bin BEFORE UPDATE ON bin

    FOR EACH ROW EXECUTE PROCEDURE set_mtime();

-- Tabelle "warehouse"

ALTER TABLE warehouse ADD COLUMN sortkey integer;

CREATE SEQUENCE tmp_counter;

UPDATE warehouse SET sortkey = nextval('tmp_counter');

DROP SEQUENCE tmp_counter;

ALTER TABLE warehouse ADD COLUMN invalid boolean;

UPDATE warehouse SET invalid = 'f';

CREATE TRIGGER mtime_warehouse BEFORE UPDATE ON warehouse

    FOR EACH ROW EXECUTE PROCEDURE set_mtime();

-- Tabelle "transfer_type"

CREATE TABLE transfer_type (

  id integer NOT NULL DEFAULT nextval('id'),

  direction varchar(10) NOT NULL,

  description text,

  sortkey integer,

  itime timestamp DEFAULT now(),

  mtime timestamp,

  PRIMARY KEY (id)

);

CREATE TRIGGER mtime_transfer_type BEFORE UPDATE ON transfer_type

    FOR EACH ROW EXECUTE PROCEDURE set_mtime();

INSERT INTO transfer_type (direction, description, sortkey) VALUES ('in', 'stock', 1);

INSERT INTO transfer_type (direction, description, sortkey) VALUES ('in', 'found', 2);

INSERT INTO transfer_type (direction, description, sortkey) VALUES ('in', 'correction', 3);

INSERT INTO transfer_type (direction, description, sortkey) VALUES ('out', 'used', 4);

INSERT INTO transfer_type (direction, description, sortkey) VALUES ('out', 'disposed', 5);

INSERT INTO transfer_type (direction, description, sortkey) VALUES ('out', 'back', 6);

INSERT INTO transfer_type (direction, description, sortkey) VALUES ('out', 'missing', 7);

INSERT INTO transfer_type (direction, description, sortkey) VALUES ('out', 'correction', 9);

INSERT INTO transfer_type (direction, description, sortkey) VALUES ('transfer', 'transfer', 10);

INSERT INTO transfer_type (direction, description, sortkey) VALUES ('transfer', 'correction', 11);

-- Anpassungen an "inventory".

DELETE FROM inventory;

ALTER TABLE inventory ADD COLUMN bin_id integer;

ALTER TABLE inventory ADD FOREIGN KEY (bin_id) REFERENCES bin (id);

ALTER TABLE inventory ALTER COLUMN bin_id SET NOT NULL;

ALTER TABLE inventory DROP COLUMN qty;

ALTER TABLE inventory ADD COLUMN qty numeric(25, 5);

ALTER TABLE inventory ALTER COLUMN parts_id SET NOT NULL;

ALTER TABLE inventory ADD FOREIGN KEY (parts_id) REFERENCES parts(id);

ALTER TABLE inventory ALTER COLUMN warehouse_id SET NOT NULL;

ALTER TABLE inventory ADD FOREIGN KEY (warehouse_id) REFERENCES warehouse(id);

ALTER TABLE inventory ALTER COLUMN employee_id SET NOT NULL;

ALTER TABLE inventory ADD FOREIGN KEY (employee_id) REFERENCES employee (id);

ALTER TABLE inventory ADD COLUMN trans_id integer;

ALTER TABLE inventory ALTER COLUMN trans_id SET NOT NULL;

ALTER TABLE inventory ADD COLUMN trans_type_id integer;

ALTER TABLE inventory ALTER COLUMN trans_type_id SET NOT NULL;

ALTER TABLE inventory ADD FOREIGN KEY (trans_type_id) REFERENCES transfer_type (id);

ALTER TABLE inventory ADD COLUMN project_id integer;

ALTER TABLE inventory ADD FOREIGN KEY (project_id) REFERENCES project (id);

ALTER TABLE inventory ADD COLUMN chargenumber text;

ALTER TABLE inventory ADD COLUMN comment text;

-- "onhand" in "parts" ?ber einen Trigger automatisch berechnen lassen.

-- Vorher Warenbestand sichern JZ

SELECT id, onhand, bin INTO TEMP TABLE tmp_parts FROM parts WHERE onhand > 0;

ALTER TABLE parts DROP COLUMN onhand;

ALTER TABLE parts ADD COLUMN onhand numeric(25,5);

UPDATE parts SET onhand = COALESCE((SELECT SUM(qty) FROM inventory WHERE inventory.parts_id = parts.id), 0);

ALTER TABLE parts ADD COLUMN stockable boolean;

ALTER TABLE parts ALTER COLUMN stockable SET DEFAULT 'f';

UPDATE parts SET stockable = 'f';

CREATE OR REPLACE FUNCTION update_onhand() RETURNS trigger AS '

BEGIN

  IF tg_op = ''INSERT'' THEN

    UPDATE parts SET onhand = COALESCE(onhand, 0) + new.qty WHERE id = new.parts_id;

    RETURN new;

  ELSIF tg_op = ''DELETE'' THEN

    UPDATE parts SET onhand = COALESCE(onhand, 0) - old.qty WHERE id = old.parts_id;

    RETURN old;

  ELSE

    UPDATE parts SET onhand = COALESCE(onhand, 0) - old.qty + new.qty WHERE id = old.parts_id;

    RETURN new;

  END IF;

END;

' LANGUAGE plpgsql;

CREATE TRIGGER trig_update_onhand

  AFTER INSERT OR UPDATE OR DELETE ON inventory

  FOR EACH ROW EXECUTE PROCEDURE update_onhand();

EOF

;

$check_sql = <<EOF

SELECT COUNT(id) FROM parts WHERE onhand > 0;

EOF

;

return do_update();

[% USE HTML %]<div class="listtop">Lagermigration</div>

<form action="[% script %]" method="POST">

<p>Lx-Office enth&auml;lt jetzt auch echte Lagerverwaultung anstatt reiner Mengenz&auml;hlung. 

Dieses update ändert die Art und Weise wie Lagermengen gezält werden. 

Als Konsequenz k&ouml;nnen die gespeicherten Mengen entweder in ein Lager &uuml;berf&uuml;hrt werden, oder f&uuml;r eine frische Lagerverwaltung resettet werden.</p>

<p>M&ouml;chten Sie die vorhandenen Mengendaten in ein Lager &uuml;bertragen?</p>

<input id=do_migrate_Y name=do_migrate type=radio value=Y>&nbsp;<label for=do_migrate_Y>Ja</label>

<input id=do_migrate_N name=do_migrate type=radio value=N checked>&nbsp;<label for=do_migrate_N>Nein</label>

<p>Bitte geben Sie den Namen des Ziellagers f&uuml;r die &uuml;bernommenen Daten ein.</p>

<input name=import_warehouse size=50>

<p>Da Lagerpl&auml;tze kein Pflichtfeld sind, geben Sie bitte einen Lagerplatz an, in dem Waren ohne spezifizierten Lagerplatz eingelagert werden sollen.<p>

<input name=bin_default size=50>

<p><input type=submit value="Weiter"></p>

[% USE HTML %]<div class="listtop"><translate>Warehouse MIgration</translate></div>

<form action="[% script %]" method="POST">

<p><translate>Lx-Office is now able to manage warehouses instead of just tracking the amount of goods in your system.</translate> 

<translate>This update will change the nature the onhand of goods is tracked.</translate> 

<translate>As a result, the saved onhand values of the present goods can be stored into a warehouse designated by you, or will be reset for a proper warehouse tracking</translate></p>

<p><translate>Do you want to store the existing onhand values into a new warehouse?</translate></p>

<input id=do_migrate_Y name=do_migrate type=radio value=Y>&nbsp;<label for=do_migrate_Y><translate>Yes</translate></label>

<input id=do_migrate_N name=do_migrate type=radio value=N checked>&nbsp;<label for=do_migrate_N><translate>No</translate></label>

<p><translate>Please specify a description for the warehouse designated for these goods.</translate></p>

<input name=import_warehouse size=50>

<p><translate>Since bin is not enforced in the parts data, please specify a bin where goods without a specified bin will be put.</translate><p>

<input name=bin_default size=50>

<p><input type=submit value="<translate>Continue</translate>"></p>