X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=sql%2FPg-upgrade%2FPg-upgrade-2.2.0.33-2.2.0.34.pl;h=cc9a0f32252c2c99a3e63327472b5478a802c25d;hb=3d7898cf2bab46c72face3011460d093d8778b32;hp=8c3e9d28f6d1ce1e10e0e021b08a13b44c3295c3;hpb=b563c6721aa4aed5128e319ad78b344b5f2120d5;p=kivitendo-erp.git

diff --git a/sql/Pg-upgrade/Pg-upgrade-2.2.0.33-2.2.0.34.pl b/sql/Pg-upgrade/Pg-upgrade-2.2.0.33-2.2.0.34.pl
index 8c3e9d28f..cc9a0f322 100644
--- a/sql/Pg-upgrade/Pg-upgrade-2.2.0.33-2.2.0.34.pl
+++ b/sql/Pg-upgrade/Pg-upgrade-2.2.0.33-2.2.0.34.pl
@@ -1,5 +1,7 @@
 #!/usr/bin/perl
 
+# Datenbankupgrade: Einfuehrung von Buchungsgruppen
+
 die("This script cannot be run from the command line.") unless ($main::form);
 
 sub mydberror {
@@ -52,13 +54,18 @@ sub set_ic_links {
 
 sub force_inventory_accno_id_for_parts {
   my $query =
-    "UPDATE parts SET inventory_accno_id = " .
-    "(SELECT bg.inventory_accno_id " .
-    " FROM buchungsgruppen bg " .
-    " WHERE bg.description = 'Standard 16%') " .
-    "WHERE (NOT inventory_accno_id ISNULL) AND (inventory_accno_id > 0)";
+    "SELECT inventory_accno_id " .
+    "FROM buchungsgruppen " .
+    "WHERE description = 'Standard 16%'";
+
+  my ($bg_id) = $dbh->selectrow_array($query);
 
-  $dbh->do($query) || mydberror($query);
+  if ($bg_id) {
+    $query =
+      "UPDATE parts SET inventory_accno_id = $bg_id " .
+      "WHERE (NOT inventory_accno_id ISNULL) AND (inventory_accno_id > 0)";
+    $dbh->do($query) || mydberror($query);
+  }
 }
 
 sub retrieve_accounts {
@@ -150,20 +157,25 @@ sub update_known_buchungsgruppen {
   my $query =
     "SELECT id, inventory_accno_id, income_accno_id, expense_accno_id " .
     "FROM parts " .
-    "WHERE NOT inventory_accno_id ISNULL AND (inventory_accno_id > 0)";
+    "WHERE NOT inventory_accno_id ISNULL AND (inventory_accno_id > 0) ";
   my $sth = $dbh->prepare($query);
   $sth->execute() || mydberror($query);
 
-  my $query_update = "UPDATE parts SET buchungsgruppen_id = ? WHERE id = ?";
+  my $query_update = "UPDATE parts SET buchungsgruppen_id = ?";
+  $query_update .= ", inventory_accno_id = ?" if $::lx_office_conf{system}->{eur};
+  $query_update .= " WHERE id = ?";
   my $sth_update = $dbh->prepare($query_update);
 
   while (my $ref = $sth->fetchrow_hashref()) {
     foreach my $bg (@{$buchungsgruppen}) {
-      if (($ref->{"inventory_accno_id"} == $bg->{"inventory_accno_id"}) &&
+      if (($::lx_office_conf{system}->{eur} ||
+           ($ref->{"inventory_accno_id"} == $bg->{"inventory_accno_id"})) &&
           ($ref->{"income_accno_id"} == $bg->{"income_accno_id_0"}) &&
           ($ref->{"expense_accno_id"} == $bg->{"expense_accno_id_0"})) {
-        $sth_update->execute($bg->{"id"}, $ref->{"id"}) ||
-          mydberror($query_update . " ($bg->{id}, $ref->{id})");
+        my @values = ($bg->{"id"}, $ref->{"id"});
+        splice(@values, 1, 0, $bg->{"inventory_accno_id"}) if $::lx_office_conf{system}->{eur};
+        $sth_update->execute(@values) ||
+          mydberror($query_update . " (" . join(", ", @values) . ")");
         last;
       }
     }
@@ -173,7 +185,8 @@ sub update_known_buchungsgruppen {
   my $query =
     "SELECT id, inventory_accno_id, income_accno_id, expense_accno_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";
   my $sth = $dbh->prepare($query);
   $sth->execute() || mydberror($query);
 
@@ -181,8 +194,10 @@ sub update_known_buchungsgruppen {
     foreach my $bg (@{$buchungsgruppen}) {
       if (($ref->{"income_accno_id"} == $bg->{"income_accno_id_0"}) &&
           ($ref->{"expense_accno_id"} == $bg->{"expense_accno_id_0"})) {
-        $sth_update->execute($bg->{"id"}, $ref->{"id"}) ||
-          mydberror($query_update . " ($bg->{id}, $ref->{id})");
+        my @values = ($bg->{"id"}, $ref->{"id"});
+        splice(@values, 1, 0, undef) if $::lx_office_conf{system}->{eur};
+        $sth_update->execute(@values) ||
+          mydberror($query_update . " (" . join(", ", @values) . ")");
         last;
       }
     }
@@ -194,7 +209,7 @@ sub update_known_buchungsgruppen {
 sub retrieve_unknown_accno_combinations {
   my ($buchungsgruppen) = @_;
 
-  my (@parts, @services, $sth, $query, $ref);
+  my (@parts, @services, @assemblies, $sth, $query, $ref);
 
   $query =
     "SELECT DISTINCT " .
@@ -233,10 +248,10 @@ sub retrieve_unknown_accno_combinations {
     "c2.accno AS income_accno, c2.description AS income_description, " .
     "c3.accno AS expense_accno, c3.description AS expense_description " .
     "FROM parts p " .
-    "LEFT JOIN chart c1 ON p.inventory_accno_id = c1.id " .
     "LEFT JOIN chart c2 ON p.income_accno_id = c2.id " .
     "LEFT JOIN chart c3 ON p.expense_accno_id = c3.id " .
-    "WHERE inventory_accno_id ISNULL OR (inventory_accno_id = 0)";
+    "WHERE (inventory_accno_id ISNULL OR (inventory_accno_id = 0)) AND " .
+    " NOT assembly";
 
   $sth = $dbh->prepare($query);
   $sth->execute() || mydberror($query);
@@ -256,7 +271,23 @@ sub retrieve_unknown_accno_combinations {
   }
   $sth->finish();
 
-  return (\@parts, \@services);
+  $query =
+    "SELECT DISTINCT " .
+    "p.income_accno_id, " .
+    "c.accno AS income_accno, c.description AS income_description " .
+    "FROM parts p " .
+    "LEFT JOIN chart c ON p.income_accno_id = c.id " .
+    "WHERE p.assembly AND " .
+    " (p.buchungsgruppen_id ISNULL OR (p.buchungsgruppen_id = 0))";
+
+  $sth = $dbh->prepare($query);
+  $sth->execute() || mydberror($query);
+
+  while ($ref = $sth->fetchrow_hashref()) {
+    push(@assemblies, $ref);
+  }
+
+  return (\@parts, \@services, \@assemblies);
 }
 
 sub display_create_bgs_dialog {
@@ -268,11 +299,15 @@ sub display_create_bgs_dialog {
     $entry->{"ACC_INVENTORY"} = $acc_inventory;
     $entry->{"ACC_INCOME"} = $acc_income;
     $entry->{"ACC_EXPENSE"} = $acc_expense;
-    $entry->{"eur"} = $main::eur;
+    $entry->{"eur"} = $::lx_office_conf{system}->{eur};
   }
 
+  # $form->parse_html_template("dbupgrade/buchungsgruppen_parts")
+  # $form->parse_html_template("dbupgrade/buchungsgruppen_services")
+  # $form->parse_html_template("dbupgrade/buchungsgruppen_assemblies")
+
   print($form->parse_html_template("dbupgrade/buchungsgruppen_${type}",
-                                   { "LIST" => $list,
+                                   { "LIST"            => $list,
                                      "BUCHUNGSGRUPPEN" => $buchungsgruppen,
                                    }));
 }
@@ -280,7 +315,6 @@ sub display_create_bgs_dialog {
 sub create_buchungsgruppen {
   my $form = $main::form;
 
-  $main::lxdebug->dump(0, "gaby", $form);
   for (my $i = 1; $i <= $form->{"rowcount"}; $i++) {
     next unless ($form->{"description_$i"} &&
                  $form->{"inventory_accno_id_$i"} &&
@@ -293,15 +327,17 @@ sub create_buchungsgruppen {
                  $form->{"income_accno_id_3_$i"} &&
                  $form->{"expense_accno_id_3_$i"});
 
-    my $query =
+    my $query = "SELECT nextval('id')";
+    my ($id) = $dbh->selectrow_array($query);
+    $query =
       "INSERT INTO buchungsgruppen (" .
-      "description, inventory_accno_id, " .
+      "id, description, inventory_accno_id, " .
       "income_accno_id_0, expense_accno_id_0, " .
       "income_accno_id_1, expense_accno_id_1, " .
       "income_accno_id_2, expense_accno_id_2, " .
       "income_accno_id_3, expense_accno_id_3) " .
-      "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
-    my @values = ($form->{"description_$i"});
+      "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
+    my @values = ($id, $form->{"description_$i"});
 
     foreach my $acc (qw(inventory_accno_id
                         income_accno_id_0 expense_accno_id_0
@@ -311,14 +347,38 @@ sub create_buchungsgruppen {
       push(@values, (split(/--/, $form->{"${acc}_${i}"}))[0]);
     }
 
-    $main::lxdebug->dump(0, "klaus$i", \@values);
-
     mydoquery($query, @values);
-    $main::lxdebug->message(0, "nachklausi$i");
+
+    $form->{"new_buchungsgruppen_id_$i"} = $id;
   }
 
-  $main::lxdebug->message(0, "commit: " . $dbh->commit());
-  $main::lxdebug->message(0, "errstr: " . $dbh->errstr);
+  $dbh->commit();
+  $dbh->begin_work();
+}
+
+sub assign_buchungsgruppen_for_assemblies {
+  my ($query, $sth, $i);
+
+  $query =
+    "UPDATE parts " .
+    "SET buchungsgruppen_id = ? " .
+    "WHERE assembly AND " .
+    "(buchungsgruppen_id ISNULL OR (buchungsgruppen_id = 0)) AND " .
+    "(income_accno_id = ?)";
+  $sth = $dbh->prepare($query);
+
+  for ($i = 1; $i <= $form->{"rowcount"}; $i++) {
+    next unless ($form->{"new_buchungsgruppen_id_$i"});
+
+    my @values = ($form->{"new_buchungsgruppen_id_$i"},
+                  $form->{"income_accno_id_0_$i"});
+    $sth->execute(@values) ||
+      mydberror($query . " (" . join(", ", @values) . ")");
+  }
+
+  $sth->finish();
+
+  $dbh->commit();
   $dbh->begin_work();
 }
 
@@ -339,7 +399,7 @@ sub retrieve_std_inventory_accno_id {
 
   my $inventory_accno_id;
   if ($inventory_accno) {
-    $query = "SELECT id FROM chart WHERE accno = $inventory_accno";
+    $query = "SELECT id FROM chart WHERE accno = '$inventory_accno'";
     ($inventory_accno_id) = $dbh->selectrow_array($query);
   }
 
@@ -357,6 +417,8 @@ sub retrieve_std_inventory_accno_id {
     $sth->finish();
   }
 
+  $inventory_accno_id = 1 unless ($inventory_accno_id);
+
   $form->{"std_inventory_accno_id"} = $inventory_accno_id;
 }
 
@@ -365,6 +427,11 @@ sub do_update {
     create_buchungsgruppen();
   }
 
+  if ($main::form->{"action2"} eq "create_buchungsgruppen_assemblies") {
+    create_buchungsgruppen();
+    assign_buchungsgruppen_for_assemblies();
+  }
+
   retrieve_std_inventory_accno_id();
 
   # Set all taxzone_id columns = 0.
@@ -372,7 +439,7 @@ sub do_update {
 
   # If balancing is off then force parts.inventory_accno_id to
   # a single value for parts.
-  force_inventory_accno_id_for_parts() if ($main::eur);
+  force_inventory_accno_id_for_parts() if $::lx_office_conf{system}->{eur};
 
   # Force "IC" to be present in chart.link for all accounts
   # which have been used as inventory accounts in parts.
@@ -387,7 +454,8 @@ sub do_update {
   # Retrieve all distinct combinations of inventory_accno_id,
   # income_accno_id and expense_accno_id for which there's no
   # Buchungsgruppe. Then let the user create new ones.
-  ($parts, $services) = retrieve_unknown_accno_combinations($buchungsgruppen);
+  ($parts, $services, $assemblies) =
+    retrieve_unknown_accno_combinations($buchungsgruppen);
 
   my ($acc_inventory, $acc_income, $acc_expense) = retrieve_accounts();
 
@@ -398,6 +466,8 @@ sub do_update {
                               $acc_inventory, $acc_income, $acc_expense,
                               $buchungsgruppen);
     return 2;
+  } else {
+    print($form->parse_html_template("dbupgrade/buchungsgruppen_parts_done"));
   }
 
   if (scalar(@{$services})) {
@@ -405,6 +475,17 @@ sub do_update {
                               $acc_inventory, $acc_income, $acc_expense,
                               $buchungsgruppen);
     return 2;
+  } else {
+    print($form->parse_html_template("dbupgrade/buchungsgruppen_services_done"));
+  }
+
+  if (scalar(@{$assemblies})) {
+    display_create_bgs_dialog("assemblies", $assemblies,
+                              $acc_inventory, $acc_income, $acc_expense,
+                              $buchungsgruppen);
+    return 2;
+  } else {
+    print($form->parse_html_template("dbupgrade/buchungsgruppen_assemblies_done"));
   }
 
   print($form->parse_html_template("dbupgrade/buchungsgruppen_footer"));