X-Git-Url: http://wagnertech.de/gitweb/gitweb.cgi/mfinanz.git/blobdiff_plain/d05eb259dd6b68cc75c29aa32961cdfce40bc45e..0eeecc3c6833a618a9f1b9c94e0331335f8a7987:/SL/AM.pm

diff --git a/SL/AM.pm b/SL/AM.pm
index 8d6730e31..a0a15899c 100644
--- a/SL/AM.pm
+++ b/SL/AM.pm
@@ -974,13 +974,13 @@ sub delete_language {
   # connect to database
   my $dbh = $form->dbconnect_noauto($myconfig);
 
-  my $query = "DELETE FROM units_language WHERE language_id = ?";
-  $dbh->do($query, undef, $form->{"id"}) ||
-    $form->dberror($query . " ($form->{id})");
+  foreach my $table (qw(translation_payment_terms units_language)) {
+    my $query = qq|DELETE FROM $table WHERE language_id = ?|;
+    do_query($form, $dbh, $query, $form->{"id"});
+  }
 
   $query = "DELETE FROM language WHERE id = ?";
-  $dbh->do($query, undef, $form->{"id"}) ||
-    $form->dberror($query . " ($form->{id})");
+  do_query($form, $dbh, $query, $form->{"id"});
 
   $dbh->commit();
   $dbh->disconnect;
@@ -997,13 +997,32 @@ sub buchungsgruppe {
   # connect to database
   my $dbh = $form->dbconnect($myconfig);
 
-  my $query = qq|SELECT id, description, inventory_accno_id, (select accno from chart where id=inventory_accno_id) as inventory_accno, income_accno_id_0, (select accno from chart where id=income_accno_id_0) as income_accno_0,  expense_accno_id_0, (select accno from chart where id=expense_accno_id_0) as expense_accno_0, income_accno_id_1, (select accno from chart where id=income_accno_id_1) as income_accno_1,  expense_accno_id_1, (select accno from chart where id=expense_accno_id_1) as expense_accno_1,  income_accno_id_2, (select accno from chart where id=income_accno_id_2) as income_accno_2,  expense_accno_id_2, (select accno from chart where id=expense_accno_id_2) as expense_accno_2,  income_accno_id_3, (select accno from chart where id=income_accno_id_3) as income_accno_3,  expense_accno_id_3, (select accno from chart where id=expense_accno_id_3) as expense_accno_3
-                  FROM buchungsgruppen
-		 ORDER BY id|;
+  my $query = qq|SELECT id, description,
+                 inventory_accno_id,
+                 (SELECT accno FROM chart WHERE id = inventory_accno_id) AS inventory_accno,
+                 income_accno_id_0,
+                 (SELECT accno FROM chart WHERE id = income_accno_id_0) AS income_accno_0,
+                 expense_accno_id_0,
+                 (SELECT accno FROM chart WHERE id = expense_accno_id_0) AS expense_accno_0,
+                 income_accno_id_1,
+                 (SELECT accno FROM chart WHERE id = income_accno_id_1) AS income_accno_1,
+                 expense_accno_id_1,
+                 (SELECT accno FROM chart WHERE id = expense_accno_id_1) AS expense_accno_1,
+                 income_accno_id_2,
+                 (SELECT accno FROM chart WHERE id = income_accno_id_2) AS income_accno_2,
+                 expense_accno_id_2,
+                 (select accno FROM chart WHERE id = expense_accno_id_2) AS expense_accno_2,
+                 income_accno_id_3,
+                 (SELECT accno FROM chart WHERE id = income_accno_id_3) AS income_accno_3,
+                 expense_accno_id_3,
+                 (SELECT accno FROM chart WHERE id = expense_accno_id_3) AS expense_accno_3
+                 FROM buchungsgruppen
+                 ORDER BY sortkey|;
 
   $sth = $dbh->prepare($query);
   $sth->execute || $form->dberror($query);
 
+  $form->{ALL} = [];
   while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
     push @{ $form->{ALL} }, $ref;
   }
@@ -1122,6 +1141,8 @@ sub save_buchungsgruppe {
                 $form->{income_accno_id_2}, $form->{expense_accno_id_2},
                 $form->{income_accno_id_3}, $form->{expense_accno_id_3});
 
+  my $query;
+
   # id is the old record
   if ($form->{id}) {
     $query = qq|UPDATE buchungsgruppen SET
@@ -1133,13 +1154,18 @@ sub save_buchungsgruppe {
                 WHERE id = ?|;
     push(@values, $form->{id});
   } else {
+    $query = qq|SELECT COALESCE(MAX(sortkey) + 1, 1) FROM buchungsgruppen|;
+    my ($sortkey) = $dbh->selectrow_array($query);
+    $form->dberror($query) if ($dbh->err);
+    push(@values, $sortkey);
     $query = qq|INSERT INTO buchungsgruppen
                 (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 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|;
+                income_accno_id_3, expense_accno_id_3,
+                sortkey)
+                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|;
   }
   do_query($form, $dbh, $query, @values);
 
@@ -1164,6 +1190,37 @@ sub delete_buchungsgruppe {
   $main::lxdebug->leave_sub();
 }
 
+sub swap_sortkeys {
+  $main::lxdebug->enter_sub();
+
+  my ($self, $myconfig, $form, $table) = @_;
+
+  # connect to database
+  my $dbh = $form->dbconnect_noauto($myconfig);
+
+  my $query =
+    qq|SELECT
+       (SELECT sortkey FROM $table WHERE id = ?) AS sortkey1,
+       (SELECT sortkey FROM $table WHERE id = ?) AS sortkey2|;
+  my @values = ($form->{"id1"}, $form->{"id2"});
+  my @sortkeys = selectrow_query($form, $dbh, $query, @values);
+  $main::lxdebug->dump(0, "v", \@values);
+  $main::lxdebug->dump(0, "s", \@sortkeys);
+
+  $query = qq|UPDATE $table SET sortkey = ? WHERE id = ?|;
+  my $sth = $dbh->prepare($query);
+  $sth->execute($sortkeys[1], $form->{"id1"}) ||
+    $form->dberror($query . " ($sortkeys[1], $form->{id1})");
+  $sth->execute($sortkeys[0], $form->{"id2"}) ||
+    $form->dberror($query . " ($sortkeys[0], $form->{id2})");
+  $sth->finish();
+
+  $dbh->commit();
+  $dbh->disconnect;
+
+  $main::lxdebug->leave_sub();
+}
+
 sub printer {
   $main::lxdebug->enter_sub();
 
@@ -1273,15 +1330,13 @@ sub payment {
   # connect to database
   my $dbh = $form->dbconnect($myconfig);
 
-  my $query = qq|SELECT *
-                 FROM payment_terms
-		 ORDER BY id|;
+  my $query = qq|SELECT * FROM payment_terms ORDER BY sortkey|;
 
   $sth = $dbh->prepare($query);
   $sth->execute || $form->dberror($query);
 
+  $form->{ALL} = [];
   while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
-    $ref->{percent_skonto} = $form->format_amount($myconfig,($ref->{percent_skonto} * 100));
     push @{ $form->{ALL} }, $ref;
   }
 
@@ -1299,20 +1354,36 @@ sub get_payment {
   # connect to database
   my $dbh = $form->dbconnect($myconfig);
 
-  my $query =
-    qq|SELECT *
-                 FROM payment_terms
-	         WHERE id = $form->{id}|;
+  my $query = qq|SELECT * FROM payment_terms WHERE id = ?|;
   my $sth = $dbh->prepare($query);
-  $sth->execute || $form->dberror($query);
+  $sth->execute($form->{"id"}) || $form->dberror($query . " ($form->{id})");
 
   my $ref = $sth->fetchrow_hashref(NAME_lc);
-  $ref->{percent_skonto} = $form->format_amount($myconfig,($ref->{percent_skonto} * 100));
-
   map { $form->{$_} = $ref->{$_} } keys %$ref;
+  $sth->finish();
+
+  $query =
+    qq|SELECT t.language_id, t.description_long, l.description AS language | .
+    qq|FROM translation_payment_terms t | .
+    qq|LEFT JOIN language l ON t.language_id = l.id | .
+    qq|WHERE t.payment_terms_id = ? | .
+    qq|UNION | .
+    qq|SELECT l.id AS language_id, NULL AS description_long, | .
+    qq|l.description AS language | .
+    qq|FROM language l|;
+  $sth = $dbh->prepare($query);
+  $sth->execute($form->{"id"}) || $form->dberror($query . " ($form->{id})");
 
+  my %mapping;
+  while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
+    $mapping{ $ref->{"language_id"} } = $ref
+      unless (defined($mapping{ $ref->{"language_id"} }));
+  }
   $sth->finish;
 
+  $form->{"TRANSLATION"} = [sort({ $a->{"language"} cmp $b->{"language"} }
+                                 values(%mapping))];
+
   $dbh->disconnect;
 
   $main::lxdebug->leave_sub();
@@ -1324,35 +1395,62 @@ sub save_payment {
   my ($self, $myconfig, $form) = @_;
 
   # connect to database
-  my $dbh = $form->dbconnect($myconfig);
+  my $dbh = $form->dbconnect_noauto($myconfig);
 
-  $form->{description} =~ s/\'/\'\'/g;
-  $form->{description_long} =~ s/\'/\'\'/g;
-  $percentskonto = $form->parse_amount($myconfig, $form->{percent_skonto}) /100;
-  $form->{ranking} *= 1;
-  $form->{terms_netto} *= 1;
-  $form->{terms_skonto} *= 1;
-  $form->{percent_skonto} *= 1;
+  my $query;
 
+  if (!$form->{id}) {
+    $query = qq|SELECT nextval('id'), COALESCE(MAX(sortkey) + 1, 1) | .
+      qq|FROM payment_terms|;
+    my $sortkey;
+    ($form->{id}, $sortkey) = selectrow_query($form, $dbh, $query);
 
+    $query = qq|INSERT INTO payment_terms (id, sortkey) VALUES (?, ?)|;
+    do_query($form, $dbh, $query, $form->{id}, $sortkey);
 
-  # id is the old record
-  if ($form->{id}) {
-    $query = qq|UPDATE payment_terms SET
-		description = '$form->{description}',
-                ranking = $form->{ranking},
-		description_long = '$form->{description_long}',
-		terms_netto = $form->{terms_netto},
-                terms_skonto = $form->{terms_skonto},
-                percent_skonto = $percentskonto
-		WHERE id = $form->{id}|;
   } else {
-    $query = qq|INSERT INTO payment_terms
-                (description, ranking, description_long, terms_netto, terms_skonto, percent_skonto)
-                VALUES ('$form->{description}', $form->{ranking}, '$form->{description_long}', $form->{terms_netto}, $form->{terms_skonto}, $percentskonto)|;
+    $query =
+      qq|DELETE FROM translation_payment_terms | .
+      qq|WHERE payment_terms_id = ?|;
+    do_query($form, $dbh, $query, $form->{"id"});
+  }
+
+  $query = qq|UPDATE payment_terms SET
+              description = ?, description_long = ?,
+              ranking = ?,
+              terms_netto = ?, terms_skonto = ?,
+              percent_skonto = ?
+              WHERE id = ?|;
+  my @values = ($form->{description}, $form->{description_long},
+                $form->{ranking} * 1,
+                $form->{terms_netto} * 1, $form->{terms_skonto} * 1,
+                $form->{percent_skonto} * 1,
+                $form->{id});
+  do_query($form, $dbh, $query, @values);
+
+  $query = qq|SELECT id FROM language|;
+  my @language_ids;
+  my $sth = $dbh->prepare($query);
+  $sth->execute() || $form->dberror($query);
+
+  while (my ($id) = $sth->fetchrow_array()) {
+    push(@language_ids, $id);
   }
-  $dbh->do($query) || $form->dberror($query);
+  $sth->finish();
+
+  $query =
+    qq|INSERT INTO translation_payment_terms | .
+    qq|(language_id, payment_terms_id, description_long) | .
+    qq|VALUES (?, ?, ?)|;
+  $sth = $dbh->prepare($query);
 
+  foreach my $language_id (@language_ids) {
+    do_statement($form, $sth, $query, $language_id, $form->{"id"},
+                 $form->{"description_long_${language_id}"});
+  }
+  $sth->finish();
+
+  $dbh->commit();
   $dbh->disconnect;
 
   $main::lxdebug->leave_sub();
@@ -1364,12 +1462,16 @@ sub delete_payment {
   my ($self, $myconfig, $form) = @_;
 
   # connect to database
-  my $dbh = $form->dbconnect($myconfig);
+  my $dbh = $form->dbconnect_noauto($myconfig);
 
-  $query = qq|DELETE FROM payment_terms
-	      WHERE id = $form->{id}|;
-  $dbh->do($query) || $form->dberror($query);
+  my $query =
+    qq|DELETE FROM translation_payment_terms WHERE payment_terms_id = ?|;
+  do_query($form, $dbh, $query, $form->{"id"});
+
+  $query = qq|DELETE FROM payment_terms WHERE id = ?|;
+  do_query($form, $dbh, $query, $form->{"id"});
 
+  $dbh->commit();
   $dbh->disconnect;
 
   $main::lxdebug->leave_sub();