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=297b14ac06bad41b778a1cbcaaaf980384ce5bd3;hb=1de53cbf8a926ae3ae89bc5466ab660df82484b2;hp=9387b0e1d70e5a26e1ee7e343d6afec17205b6b7;hpb=1960c18caac0a9d6a295c2e52116c7752c947cc1;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 9387b0e1d..297b14ac0 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 ($main::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 (($main::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 ($main::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 ($main::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 {
@@ -271,8 +302,12 @@ sub display_create_bgs_dialog {
     $entry->{"eur"} = $main::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,
                                    }));
 }
@@ -292,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,12 +348,40 @@ sub create_buchungsgruppen {
     }
 
     mydoquery($query, @values);
+
+    $form->{"new_buchungsgruppen_id_$i"} = $id;
   }
 
   $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();
+}
+
 sub retrieve_std_inventory_accno_id {
   my $query;
 
@@ -334,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);
   }
 
@@ -352,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;
 }
 
@@ -360,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.
@@ -382,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();
 
@@ -393,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})) {
@@ -400,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"));