X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=sql%2FPg-upgrade%2FPg-upgrade-2.2.0.25-2.2.0.26.pl;h=c014a66ce8b5116957f8a1b38f4f15d342973aff;hb=82349c1ed1027244965c0da6ecdd8793d365d01a;hp=b5e8a98e4f36f4e7fcc3e284d3efdb9c99efc18f;hpb=5ee0531b6b1228c0c6173c57ec74316c4f0891af;p=kivitendo-erp.git

diff --git a/sql/Pg-upgrade/Pg-upgrade-2.2.0.25-2.2.0.26.pl b/sql/Pg-upgrade/Pg-upgrade-2.2.0.25-2.2.0.26.pl
index b5e8a98e4..c014a66ce 100644
--- a/sql/Pg-upgrade/Pg-upgrade-2.2.0.25-2.2.0.26.pl
+++ b/sql/Pg-upgrade/Pg-upgrade-2.2.0.25-2.2.0.26.pl
@@ -1,8 +1,8 @@
 #!/usr/bin/perl
 
-die("This script cannot be run from the command line.") unless ($main::form);
+# Datenbankupgrade: Einfuehrung von Einheiten
 
-use SL::AM;
+die("This script cannot be run from the command line.") unless ($main::form);
 
 sub mydberror {
   my ($msg) = @_;
@@ -13,11 +13,79 @@ sub mydberror {
 sub myshowerror {
   my ($msg) = @_;
 
-  print($main::form->parse_html_template("dbupgrade/units_error",
-                                         { "message" => $msg }));
+  print $main::form->parse_html_template("dbupgrade/units_error", { "message" => $msg });
   return 2;
 }
 
+sub get_base_unit {
+  my ($units, $unit_name, $factor) = @_;
+
+  $factor = 1 unless ($factor);
+
+  my $unit = $units->{$unit_name};
+
+  if (!defined($unit) || !$unit->{"base_unit"} ||
+      ($unit_name eq $unit->{"base_unit"})) {
+    return ($unit_name, $factor);
+  }
+
+  return get_base_unit($units, $unit->{"base_unit"}, $factor * $unit->{"factor"});
+}
+
+sub retrieve_units {
+  my ($myconfig, $form, $type, $prefix) = @_;
+
+  my $query = "SELECT *, base_unit AS original_base_unit FROM units";
+  my @values;
+  if ($type) {
+    $query .= " WHERE (type = ?)";
+    @values = ($type);
+  }
+
+  my $sth = $dbh->prepare($query);
+  $sth->execute(@values) || $form->dberror($query . " (" . join(", ", @values) . ")");
+
+  my $units = {};
+  while (my $ref = $sth->fetchrow_hashref()) {
+    $units->{$ref->{"name"}} = $ref;
+  }
+  $sth->finish();
+
+  my $query_lang = "SELECT id, template_code FROM language ORDER BY description";
+  $sth = $dbh->prepare($query_lang);
+  $sth->execute() || $form->dberror($query_lang);
+  my @languages;
+  while ($ref = $sth->fetchrow_hashref()) {
+    push(@languages, $ref);
+  }
+  $sth->finish();
+
+  foreach my $unit (values(%{$units})) {
+    ($unit->{"${prefix}base_unit"}, $unit->{"${prefix}factor"}) = get_base_unit($units, $unit->{"name"});
+  }
+
+  return $units;
+}
+
+sub unit_select_data {
+  my ($units, $selected, $empty_entry) = @_;
+
+  my $select = [];
+
+  if ($empty_entry) {
+    push(@{$select}, { "name" => "", "base_unit" => "", "factor" => "", "selected" => "" });
+  }
+
+  foreach my $unit (sort({ lc($a) cmp lc($b) } keys(%{$units}))) {
+    push(@{$select}, { "name" => $unit,
+                       "base_unit" => $units->{$unit}->{"base_unit"},
+                       "factor" => $units->{$unit}->{"factor"},
+                       "selected" => ($unit eq $selected) ? "selected" : "" });
+  }
+
+  return $select;
+}
+
 sub update_units_add_unit {
   my $form = $main::form;
 
@@ -25,10 +93,10 @@ sub update_units_add_unit {
 
   return myshowerror($dbup_locale->text("The name is missing."))
     if ($form->{"new_name"} eq "");
-  my $units = AM->retrieve_units(\%dbup_myconfig, $form);
+  my $units = retrieve_units(\%dbup_myconfig, $form);
   return myshowerror($dbup_locale->text("A unit with this name does already exist."))
     if ($units->{$form->{"new_name"}});
-  $units = AM->retrieve_units(\%dbup_myconfig, $form, $form->{"unit_type"});
+  $units = retrieve_units(\%dbup_myconfig, $form, $form->{"unit_type"});
 
   my ($base_unit, $factor);
   if ($form->{"new_base_unit"}) {
@@ -90,6 +158,10 @@ sub update_units_assign_known {
     "pc" => "Stck",
     "pcs" => "Stck",
     "ea" => "Stck",
+
+    "h" => "Std",
+    "stunde" => "Std",
+    "tage" => "Tag",
     );
 
   my $i = 1;
@@ -112,12 +184,12 @@ sub update_units_steps_1_2 {
     my ($query, $sth, $ref);
 
     if ($table eq "parts") {
-      $query = "SELECT unit, inventory_accno_id FROM parts " .
+      $query = "SELECT unit, inventory_accno_id, assembly FROM parts " .
         "WHERE NOT ((unit = '') OR unit ISNULL OR " .
         "           unit IN (SELECT name FROM units))";
 
     } else {
-      $query = "SELECT t.unit, p.inventory_accno_id " .
+      $query = "SELECT t.unit, p.inventory_accno_id, p.assembly " .
         "FROM $table t " .
         "LEFT JOIN parts p ON p.id = t.parts_id " .
         "WHERE NOT ((t.unit = '') OR t.unit ISNULL OR " .
@@ -127,7 +199,7 @@ sub update_units_steps_1_2 {
     $sth->execute() || mydberror($query);
 
     while ($ref = $sth->fetchrow_hashref()) {
-      if ($ref->{"inventory_accno_id"}) {
+      if ($ref->{"inventory_accno_id"} || $ref->{"assembly"}) {
         $unknown_dimension_units{$ref->{"unit"}} = 1;
 
       } else {
@@ -139,41 +211,41 @@ sub update_units_steps_1_2 {
   }
 
   if (scalar(keys(%unknown_dimension_units)) != 0) {
-    my $units = AM->retrieve_units(\%dbup_myconfig, $form, "dimension");
-    my $ddbox = AM->unit_select_data($units, undef, 1);
+    my $units = retrieve_units(\%dbup_myconfig, $form, "dimension");
+    my $ddbox = unit_select_data($units, undef, 1);
 
     my @unknown_parts;
     map({ push(@unknown_parts, { "name" => $_, "NEW_UNITS" => $ddbox }); }
         sort({ lc($a) cmp lc($b) } keys(%unknown_dimension_units)));
 
-    print($form->parse_html_template("dbupgrade/units_parts",
+    print $form->parse_html_template("dbupgrade/units_parts",
                                      { "NEW_BASE_UNIT_DDBOX" => $ddbox,
-                                       "UNKNOWN_PART_UNITS" => \@unknown_parts,
-                                     }));
+                                       "UNKNOWN_PART_UNITS"  => \@unknown_parts,
+                                     });
 
     return 2;
 
   } else {
-    print($form->parse_html_template("dbupgrade/units_parts_done"));
+    print $form->parse_html_template("dbupgrade/units_parts_done");
   }
 
   if (scalar(keys(%unknown_service_units)) != 0) {
-    my $units = AM->retrieve_units(\%dbup_myconfig, $form, "service");
-    my $ddbox = AM->unit_select_data($units, undef, 1);
+    my $units = retrieve_units(\%dbup_myconfig, $form, "service");
+    my $ddbox = unit_select_data($units, undef, 1);
 
     my @unknown_services;
     map({ push(@unknown_services, { "name" => $_, "NEW_UNITS" => $ddbox }); }
         sort({ lc($a) cmp lc($b) } keys(%unknown_service_units)));
 
-    print($form->parse_html_template("dbupgrade/units_services",
+    print $form->parse_html_template("dbupgrade/units_services",
                                      { "NEW_BASE_UNIT_DDBOX" => $ddbox,
-                                       "UNKNOWN_PART_UNITS" => \@unknown_services,
-                                     }));
+                                       "UNKNOWN_PART_UNITS"  => \@unknown_services,
+                                     });
 
     return 2;
 
   } else {
-    print($form->parse_html_template("dbupgrade/units_services_done"));
+    print $form->parse_html_template("dbupgrade/units_services_done");
   }
 
   return 0;
@@ -191,20 +263,20 @@ sub update_units_step_3 {
   my ($has_unassigned) = $dbh->selectrow_array($query);
 
   if ($has_unassigned) {
-    my $dimension_units = AM->retrieve_units(\%dbup_myconfig, $form,
+    my $dimension_units = retrieve_units(\%dbup_myconfig, $form,
                                              "dimension");
-    my $dimension_ddbox = AM->unit_select_data($dimension_units);
+    my $dimension_ddbox = unit_select_data($dimension_units);
 
-    my $service_units = AM->retrieve_units(\%dbup_myconfig, $form, "service");
-    my $service_ddbox = AM->unit_select_data($service_units);
+    my $service_units = retrieve_units(\%dbup_myconfig, $form, "service");
+    my $service_ddbox = unit_select_data($service_units);
 
-    print($form->parse_html_template("dbupgrade/units_set_default",
+    print $form->parse_html_template("dbupgrade/units_set_default",
                                      { "DIMENSION_DDBOX" => $dimension_ddbox,
-                                       "SERVICE_DDBOX" => $service_ddbox }));
+                                       "SERVICE_DDBOX"   => $service_ddbox });
     return 2;
 
   } else {
-    print($form->parse_html_template("dbupgrade/units_set_default_done"));
+    print $form->parse_html_template("dbupgrade/units_set_default_done");
     return 1;
   }
 }
@@ -221,12 +293,14 @@ sub update_units_set_default {
     if ($table eq "parts") {
       $query = "UPDATE $table SET unit = " .
         $dbh->quote($form->{"default_dimension_unit"}) . " " .
-        "WHERE ((unit ISNULL) OR (unit = '')) AND (inventory_accno_id > 0)";
+        "WHERE ((unit ISNULL) OR (unit = '')) AND " .
+        "(assembly OR (inventory_accno_id > 0))";
     } else {
       $query = "UPDATE $table SET unit = " .
         $dbh->quote($form->{"default_dimension_unit"}) . " " .
         "WHERE ((unit ISNULL) OR (unit = '')) AND " .
-        "parts_id IN (SELECT id FROM parts WHERE (inventory_accno_id > 0))";
+        "parts_id IN (SELECT id FROM parts WHERE " .
+        "(assembly OR (inventory_accno_id > 0)))";
     }
 
     $dbh->do($query) || mydberror($query);
@@ -235,13 +309,15 @@ sub update_units_set_default {
       $query = "UPDATE $table SET unit = " .
         $dbh->quote($form->{"default_service_unit"}) . " " .
         "WHERE ((unit ISNULL) OR (unit = '')) AND " .
-        "(inventory_accno_id ISNULL) OR (inventory_accno_id = 0)";
+        "((inventory_accno_id ISNULL) OR (inventory_accno_id = 0)) AND " .
+        "NOT assembly";
     } else {
       $query = "UPDATE $table SET unit = " .
         $dbh->quote($form->{"default_service_unit"}) . " " .
         "WHERE ((unit ISNULL) OR (unit = '')) AND " .
         "parts_id IN (SELECT id FROM parts " .
-        "WHERE (inventory_accno_id ISNULL) OR (inventory_accno_id = 0))";
+        "WHERE ((inventory_accno_id ISNULL) OR (inventory_accno_id = 0)) " .
+        "AND NOT assembly)";
     }
 
     $dbh->do($query) || mydberror($query);
@@ -253,7 +329,7 @@ sub update_units {
 
   my $res;
 
-  print($form->parse_html_template("dbupgrade/units_header"));
+  print $form->parse_html_template("dbupgrade/units_header");
 
   if ($form->{"action2"} eq "add_unit") {
     $res = update_units_add_unit();