X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;ds=inline;f=SL%2FAM.pm;h=f455e65a243d1c499b8e5c4e880479a0625ec41e;hb=9a795b8553bd43a379646bc6244c618e570556e9;hp=77a5955d229adaee0fcbf53ae481c1b8fa7b5586;hpb=828bd68326ee5bd732d3d01e9f166dacb7424657;p=kivitendo-erp.git

diff --git a/SL/AM.pm b/SL/AM.pm
index 77a5955d2..f455e65a2 100644
--- a/SL/AM.pm
+++ b/SL/AM.pm
@@ -45,19 +45,24 @@ sub get_account {
 
   my ($self, $myconfig, $form) = @_;
 
-  $form->{id} = "NULL" unless ($form->{id});
-
   # connect to database
   my $dbh = $form->dbconnect($myconfig);
-  my $query = qq§SELECT c.accno, c.description, c.charttype, c.gifi_accno,
-                 c.category,c.link, tk.taxkey_id, tk.pos_ustva, tk.tax_id,tk.tax_id||'--'||tk.taxkey_id AS tax, tk.startdate, c.pos_bilanz, c.pos_eur, c.new_chart_id, c.valid_from, c.pos_bwa
-                FROM chart c LEFT JOIN taxkeys tk
-                ON (c.id=tk.chart_id AND tk.id = (SELECT id from taxkeys where taxkeys.chart_id =c.id AND startdate<=current_date ORDER BY startdate desc LIMIT 1))
-                WHERE c.id = $form->{id}§;
-
+  my $query =
+    qq!SELECT c.accno, c.description, c.charttype, c.gifi_accno, c.category,! .
+    qq!  c.link, c.pos_bilanz, c.pos_eur, c.new_chart_id, c.valid_from, ! .
+    qq!  c.pos_bwa, ! .
+    qq!  tk.taxkey_id, tk.pos_ustva, tk.tax_id, ! .
+    qq!  tk.tax_id || '--' || tk.taxkey_id AS tax, tk.startdate ! .
+    qq!FROM chart c ! .
+    qq!LEFT JOIN taxkeys tk ! .
+    qq!ON (c.id=tk.chart_id AND tk.id = ! .
+    qq!  (SELECT id FROM taxkeys ! .
+    qq!   WHERE taxkeys.chart_id = c.id AND startdate <= current_date ! .
+    qq!   ORDER BY startdate DESC LIMIT 1)) ! .
+    qq!WHERE c.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);
 
@@ -93,16 +98,13 @@ sub get_account {
 
   $sth->finish;
   if ($form->{id}) {
-
-    $where = " WHERE link='$form->{link}'";
-
-
     # get new accounts
     $query = qq|SELECT id, accno,description
-                FROM chart $where|;
+                FROM chart WHERE link = ?|;
     $sth = $dbh->prepare($query);
-    $sth->execute || $form->dberror($query);
+    $sth->execute($form->{link}) || $form->dberror($query . " ($form->{link})");
 
+    $form->{NEWACCOUNT} = [];
     while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
       push @{ $form->{NEWACCOUNT} }, $ref;
     }
@@ -111,9 +113,9 @@ sub get_account {
   }
   # check if we have any transactions
   $query = qq|SELECT a.trans_id FROM acc_trans a
-              WHERE a.chart_id = $form->{id}|;
+              WHERE a.chart_id = ?|;
   $sth = $dbh->prepare($query);
-  $sth->execute || $form->dberror($query);
+  $sth->execute($form->{id}) || $form->dberror($query . " ($form->{id})");
 
   ($form->{orphaned}) = $sth->fetchrow_array;
   $form->{orphaned} = !$form->{orphaned};
@@ -123,11 +125,8 @@ sub get_account {
   $form->{new_chart_valid} = 0;
   if ($form->{new_chart_id}) {
     $query = qq|SELECT current_date-valid_from FROM chart
-              WHERE id = $form->{id}|;
-    $sth = $dbh->prepare($query);
-    $sth->execute || $form->dberror($query);
-
-    my ($count) = $sth->fetchrow_array;
+                WHERE id = ?|;
+    my ($count) = selectrow_query($form, $dbh, $query, $form->{id});
     if ($count >=0) {
       $form->{new_chart_valid} = 1;
     }
@@ -167,9 +166,6 @@ sub save_account {
   }
   chop $form->{link};
 
-  # if we have an id then replace the old record
-  $form->{description} =~ s/\'/\'\'/g;
-
   # strip blanks from accno
   map { $form->{$_} =~ s/ //g; } qw(accno);
 
@@ -179,95 +175,74 @@ sub save_account {
     $form->{id} = "";
   }
 
-  map({ $form->{$_} = "NULL" unless ($form->{$_}); }
-      qw(pos_ustva pos_bwa pos_bilanz pos_eur new_chart_id));
-  my($tax_id, $taxkey) = split /--/, $form->{tax};
-  $form->{valid_from} = ($form->{valid_from}) ? "'$form->{valid_from}'" : "NULL";
-  my $startdate = ($form->{startdate}) ? "'$form->{startdate}'" : "'1970-01-01'";
+  my @values;
+
+  my ($tax_id, $taxkey) = split(/--/, $form->{tax});
+  my $startdate = $form->{startdate} ? $form->{startdate} : "1970-01-01";
+
   if ($form->{id} && $form->{orphaned}) {
     $query = qq|UPDATE chart SET
-                accno = '$form->{accno}',
-		description = '$form->{description}',
-		charttype = '$form->{charttype}',
-		gifi_accno = '$form->{gifi_accno}',
-		category = '$form->{category}',
-		link = '$form->{link}',
-                taxkey_id = $taxkey,
-                pos_ustva = $form->{pos_ustva},
-                pos_bwa   = $form->{pos_bwa},
-                pos_bilanz = $form->{pos_bilanz},
-                pos_eur = $form->{pos_eur},
-                new_chart_id = $form->{new_chart_id},
-                valid_from = $form->{valid_from}
-		WHERE id = $form->{id}|;
+                accno = ?, description = ?, charttype = ?,
+		gifi_accno = ?, category = ?, link = ?,
+                taxkey_id = ?,
+                pos_ustva = ?, pos_bwa   = ?, pos_bilanz = ?,
+                pos_eur = ?, new_chart_id = ?, valid_from = ?
+		WHERE id = ?|;
+    @values = ($form->{accno}, $form->{description}, $form->{charttype},
+	       $form->{gifi_accno}, $form->{category}, $form->{link},
+	       conv_i($taxkey),
+	       conv_i($form->{pos_ustva}), conv_i($form->{pos_bwa}),
+	       conv_i($form->{pos_bilanz}), conv_i($form->{pos_eur}),
+	       conv_i($form->{new_chart_id}),
+	       conv_date($form->{valid_from}),
+	       $form->{id});
+
   } elsif ($form->{id} && !$form->{new_chart_valid}) {
-     $query = qq|UPDATE chart SET
-                new_chart_id = $form->{new_chart_id},
-                valid_from = $form->{valid_from}
-		WHERE id = $form->{id}|;
+    $query = qq|UPDATE chart SET new_chart_id = ?, valid_from = ?
+		WHERE id = ?|;
+    @values = (conv_i($form->{new_chart_id}), conv_date($form->{valid_from}),
+	       $form->{id});
   } else {
-
     $query = qq|INSERT INTO chart
-                (accno, description, charttype, gifi_accno, category, link, taxkey_id, pos_ustva, pos_bwa, pos_bilanz,pos_eur, new_chart_id, valid_from)
-                VALUES ('$form->{accno}', '$form->{description}',
-		'$form->{charttype}', '$form->{gifi_accno}',
-		'$form->{category}', '$form->{link}', $taxkey, $form->{pos_ustva}, $form->{pos_bwa}, $form->{pos_bilanz}, $form->{pos_eur}, $form->{new_chart_id}, $form->{valid_from})|;
+                (accno, description, charttype,
+                 gifi_accno, category, link,
+                 taxkey_id,
+                 pos_ustva, pos_bwa, pos_bilanz, pos_eur,
+                 new_chart_id, valid_from)
+                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|;
+    @values = ($form->{accno}, $form->{description}, $form->{charttype},
+	       $form->{gifi_accno}, $form->{category}, $form->{link},
+	       conv_i($taxkey),
+	       conv_i($form->{pos_ustva}), conv_i($form->{pos_bwa}),
+	       conv_i($form->{pos_bilanz}), conv_i($form->{pos_eur}),
+	       conv_i($form->{new_chart_id}),
+	       conv_date($form->{valid_from}));
+
   }
-  $dbh->do($query) || $form->dberror($query);
+  do_query($form, $dbh, $query, @values);
 
   #Save Taxes
   if (!$form->{id}) {
-    $query = qq|INSERT INTO taxkeys (chart_id,tax_id,taxkey_id, pos_ustva, startdate) VALUES ((SELECT id FROM chart where accno='$form->{accno}'), $tax_id, $taxkey,$form->{pos_ustva}, $startdate)|;
-    $dbh->do($query) || $form->dberror($query);
+    $query =
+      qq|INSERT INTO taxkeys | .
+      qq|(chart_id, tax_id, taxkey_id, pos_ustva, startdate) | .
+      qq|VALUES ((SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?)|;
+    do_query($form, $dbh, $query,
+	     $form->{accno}, conv_i($tax_id), conv_i($taxkey),
+	     conv_i($form->{pos_ustva}), conv_date($startdate));
+
   } else {
-    $query = qq|DELETE FROM taxkeys WHERE chart_id=$form->{id} AND tax_id=$tax_id|;
-    $dbh->do($query) || $form->dberror($query);
-    $query = qq|INSERT INTO taxkeys (chart_id,tax_id,taxkey_id, pos_ustva, startdate) VALUES ($form->{id}, $tax_id, $taxkey,$form->{pos_ustva}, $startdate)|;
-    $dbh->do($query) || $form->dberror($query);
-  }
+    $query = qq|DELETE FROM taxkeys WHERE chart_id = ? AND tax_id = ?|;
+    do_query($form, $dbh, $query, $form->{id}, conv_i($tax_id));
 
-#   if ($form->{IC_taxpart} || $form->{IC_taxservice} || $form->{CT_tax}) {
-#
-#     my $chart_id = $form->{id};
-#
-#     unless ($form->{id}) {
-#
-#       # get id from chart
-#       $query = qq|SELECT c.id
-#                   FROM chart c
-# 		  WHERE c.accno = '$form->{accno}'|;
-#       $sth = $dbh->prepare($query);
-#       $sth->execute || $form->dberror($query);
-#
-#       ($chart_id) = $sth->fetchrow_array;
-#       $sth->finish;
-#     }
-#
-#     # add account if it doesn't exist in tax
-#     $query = qq|SELECT t.chart_id
-#                 FROM tax t
-# 		WHERE t.chart_id = $chart_id|;
-#     $sth = $dbh->prepare($query);
-#     $sth->execute || $form->dberror($query);
-#
-#     my ($tax_id) = $sth->fetchrow_array;
-#     $sth->finish;
-#
-#     # add tax if it doesn't exist
-#     unless ($tax_id) {
-#       $query = qq|INSERT INTO tax (chart_id, rate)
-#                   VALUES ($chart_id, 0)|;
-#       $dbh->do($query) || $form->dberror($query);
-#     }
-#   } else {
-#
-#     # remove tax
-#     if ($form->{id}) {
-#       $query = qq|DELETE FROM tax
-# 		  WHERE chart_id = $form->{id}|;
-#       $dbh->do($query) || $form->dberror($query);
-#     }
-#   }
+    $query =
+      qq|INSERT INTO taxkeys | .
+      qq|(chart_id, tax_id, taxkey_id, pos_ustva, startdate) | .
+      qq|VALUES (?, ?, ?, ?, ?)|;
+    do_query($form, $dbh, $query,
+	     $form->{id}, conv_i($tax_id), conv_i($taxkey),
+	     conv_i($form->{pos_ustva}), conv_date($startdate));
+  }
 
   # commit
   my $rc = $dbh->commit;
@@ -287,48 +262,35 @@ sub delete_account {
   my $dbh = $form->dbconnect_noauto($myconfig);
 
   my $query = qq|SELECT count(*) FROM acc_trans a
-                 WHERE a.chart_id = $form->{id}|;
-  my $sth = $dbh->prepare($query);
-  $sth->execute || $form->dberror($query);
+                 WHERE a.chart_id = ?|;
+  my ($count) = selectrow_query($form, $dbh, $query, $form->{id});
 
-  if ($sth->fetchrow_array) {
-    $sth->finish;
+  if ($count) {
     $dbh->disconnect;
     $main::lxdebug->leave_sub();
     return;
   }
-  $sth->finish;
-
-  # delete chart of account record
-  $query = qq|DELETE FROM chart
-              WHERE id = $form->{id}|;
-  $dbh->do($query) || $form->dberror($query);
 
   # set inventory_accno_id, income_accno_id, expense_accno_id to defaults
-  $query = qq|UPDATE parts
-              SET inventory_accno_id =
-	                 (SELECT inventory_accno_id FROM defaults)
-	      WHERE inventory_accno_id = $form->{id}|;
-  $dbh->do($query) || $form->dberror($query);
-
-  $query = qq|UPDATE parts
-              SET income_accno_id =
-	                 (SELECT income_accno_id FROM defaults)
-	      WHERE income_accno_id = $form->{id}|;
-  $dbh->do($query) || $form->dberror($query);
-
-  $query = qq|UPDATE parts
-              SET expense_accno_id =
-	                 (SELECT expense_accno_id FROM defaults)
-	      WHERE expense_accno_id = $form->{id}|;
-  $dbh->do($query) || $form->dberror($query);
+  foreach my $type (qw(inventory income expense)) {
+    $query =
+      qq|UPDATE parts | .
+      qq|SET ${type}_accno_id = (SELECT ${type}_accno_id FROM defaults) | .
+      qq|WHERE ${type}_accno_id = ?|;
+    do_query($form, $dbh, $query, $form->{id});
+  }
 
   foreach my $table (qw(partstax customertax vendortax tax)) {
     $query = qq|DELETE FROM $table
-		WHERE chart_id = $form->{id}|;
-    $dbh->do($query) || $form->dberror($query);
+                WHERE chart_id = ?|;
+    do_query($form, $dbh, $query, $form->{id});
   }
 
+  # delete chart of account record
+  $query = qq|DELETE FROM chart
+              WHERE id = ?|;
+  do_query($form, $dbh, $query, $form->{id});
+
   # commit and redirect
   my $rc = $dbh->commit;
   $dbh->disconnect;
@@ -338,114 +300,6 @@ sub delete_account {
   return $rc;
 }
 
-sub gifi_accounts {
-  $main::lxdebug->enter_sub();
-
-  my ($self, $myconfig, $form) = @_;
-
-  # connect to database
-  my $dbh = $form->dbconnect($myconfig);
-
-  my $query = qq|SELECT accno, description
-                 FROM gifi
-		 ORDER BY accno|;
-
-  $sth = $dbh->prepare($query);
-  $sth->execute || $form->dberror($query);
-
-  while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
-    push @{ $form->{ALL} }, $ref;
-  }
-
-  $sth->finish;
-  $dbh->disconnect;
-
-  $main::lxdebug->leave_sub();
-}
-
-sub get_gifi {
-  $main::lxdebug->enter_sub();
-
-  my ($self, $myconfig, $form) = @_;
-
-  # connect to database
-  my $dbh = $form->dbconnect($myconfig);
-
-  my $query = qq|SELECT g.accno, g.description
-                 FROM gifi g
-	         WHERE g.accno = '$form->{accno}'|;
-  my $sth = $dbh->prepare($query);
-  $sth->execute || $form->dberror($query);
-
-  my $ref = $sth->fetchrow_hashref(NAME_lc);
-
-  map { $form->{$_} = $ref->{$_} } keys %$ref;
-
-  $sth->finish;
-
-  # check for transactions
-  $query = qq|SELECT count(*) FROM acc_trans a, chart c, gifi g
-              WHERE c.gifi_accno = g.accno
-	      AND a.chart_id = c.id
-	      AND g.accno = '$form->{accno}'|;
-  $sth = $dbh->prepare($query);
-  $sth->execute || $form->dberror($query);
-
-  ($form->{orphaned}) = $sth->fetchrow_array;
-  $sth->finish;
-  $form->{orphaned} = !$form->{orphaned};
-
-  $dbh->disconnect;
-
-  $main::lxdebug->leave_sub();
-}
-
-sub save_gifi {
-  $main::lxdebug->enter_sub();
-
-  my ($self, $myconfig, $form) = @_;
-
-  # connect to database
-  my $dbh = $form->dbconnect($myconfig);
-
-  $form->{description} =~ s/\'/\'\'/g;
-
-  # id is the old account number!
-  if ($form->{id}) {
-    $query = qq|UPDATE gifi SET
-                accno = '$form->{accno}',
-		description = '$form->{description}'
-		WHERE accno = '$form->{id}'|;
-  } else {
-    $query = qq|INSERT INTO gifi
-                (accno, description)
-                VALUES ('$form->{accno}', '$form->{description}')|;
-  }
-  $dbh->do($query) || $form->dberror($query);
-
-  $dbh->disconnect;
-
-  $main::lxdebug->leave_sub();
-}
-
-sub delete_gifi {
-  $main::lxdebug->enter_sub();
-
-  my ($self, $myconfig, $form) = @_;
-
-  # connect to database
-  my $dbh = $form->dbconnect($myconfig);
-
-  # id is the old account number!
-  $query = qq|DELETE FROM gifi
-	      WHERE accno = '$form->{id}'|;
-  $dbh->do($query) || $form->dberror($query);
-
-  $dbh->disconnect;
-
-  $main::lxdebug->leave_sub();
-}
-
 sub warehouses {
   $main::lxdebug->enter_sub();
 
@@ -974,13 +828,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;
@@ -1154,7 +1008,7 @@ sub save_buchungsgruppe {
                 WHERE id = ?|;
     push(@values, $form->{id});
   } else {
-    $query = qq|SELECT MAX(sortkey) + 1 FROM buchungsgruppen|;
+    $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);
@@ -1360,9 +1214,30 @@ sub get_payment {
 
   my $ref = $sth->fetchrow_hashref(NAME_lc);
   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();
@@ -1374,34 +1249,62 @@ sub save_payment {
   my ($self, $myconfig, $form) = @_;
 
   # connect to database
-  my $dbh = $form->dbconnect($myconfig);
+  my $dbh = $form->dbconnect_noauto($myconfig);
 
+  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);
+
+  } else {
+    $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->{percent_skonto} * 1,
+                $form->{id});
+  do_query($form, $dbh, $query, @values);
 
-  my $query;
-  # id is the old record
-  if ($form->{id}) {
-    $query = qq|UPDATE payment_terms SET
-                description = ?, description_long = ?,
-                ranking = ?,
-                terms_netto = ?, terms_skonto = ?,
-                percent_skonto = ?
-                WHERE id = ?|;
-    push(@values, $form->{"id"});
-  } else {
-    $query = qq|SELECT MAX(sortkey) + 1 FROM payment_terms|;
-    my ($sortkey) = selectrow_query($form, $dbh, $query);
-    $query = qq|INSERT INTO payment_terms
-                (description, description_long, ranking,
-                 terms_netto, terms_skonto, percent_skonto, sortkey)
-                VALUES (?, ?, ?, ?, ?, ?, ?)|;
-    push(@values, $sortkey);
+  $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);
   }
-  do_query($form, $dbh, $query, @values);
+  $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();
@@ -1413,11 +1316,16 @@ sub delete_payment {
   my ($self, $myconfig, $form) = @_;
 
   # connect to database
-  my $dbh = $form->dbconnect($myconfig);
+  my $dbh = $form->dbconnect_noauto($myconfig);
 
-  my $query = qq|DELETE FROM payment_terms WHERE id = ?|;
+  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();
@@ -2230,7 +2138,7 @@ sub unit_select_data {
     push(@{$select}, { "name" => "", "base_unit" => "", "factor" => "", "selected" => "" });
   }
 
-  foreach my $unit (sort({ lc($a) cmp lc($b) } keys(%{$units}))) {
+  foreach my $unit (sort({ $a->{"sortkey"} <=> $b->{"sortkey"} } keys(%{$units}))) {
     push(@{$select}, { "name" => $unit,
                        "base_unit" => $units->{$unit}->{"base_unit"},
                        "factor" => $units->{$unit}->{"factor"},
@@ -2249,7 +2157,7 @@ sub unit_select_html {
 
   my $select = "<select name=${name}>";
 
-  foreach my $unit (sort({ lc($a) cmp lc($b) } keys(%{$units}))) {
+  foreach my $unit (sort({ $a->{"sortkey"} <=> $b->{"sortkey"} } keys(%{$units}))) {
     if (!$convertible_into ||
         ($units->{$convertible_into} &&
          ($units->{$convertible_into}->{"base_unit"} eq $units->{$unit}->{"base_unit"}))) {
@@ -2270,8 +2178,12 @@ sub add_unit {
 
   my $dbh = $form->dbconnect_noauto($myconfig);
 
-  my $query = "INSERT INTO units (name, base_unit, factor, type) VALUES (?, ?, ?, ?)";
-  $dbh->do($query, undef, $name, $base_unit, $factor, $type) || $form->dberror($query . " ($name, $base_unit, $factor, $type)");
+  my $query = qq|SELECT COALESCE(MAX(sortkey), 0) + 1 FROM units|;
+  my ($sortkey) = selectrow_query($form, $dbh, $query);
+
+  $query = "INSERT INTO units (name, base_unit, factor, type, sortkey) " .
+    "VALUES (?, ?, ?, ?, ?)";
+  do_query($form, $dbh, $query, $name, $base_unit, $factor, $type, $sortkey);
 
   if ($languages) {
     $query = "INSERT INTO units_language (unit, language_id, localized, localized_plural) VALUES (?, ?, ?, ?)";
@@ -2347,4 +2259,41 @@ sub save_units {
   $main::lxdebug->leave_sub();
 }
 
+sub swap_units {
+  $main::lxdebug->enter_sub();
+
+  my ($self, $myconfig, $form, $dir, $name_1, $unit_type) = @_;
+
+  my $dbh = $form->dbconnect_noauto($myconfig);
+
+  my $query;
+
+  $query = qq|SELECT sortkey FROM units WHERE name = ?|;
+  my ($sortkey_1) = selectrow_query($form, $dbh, $query, $name_1);
+
+  $query =
+    qq|SELECT sortkey FROM units | .
+    qq|WHERE sortkey | . ($dir eq "down" ? ">" : "<") . qq| ? AND type = ? | .
+    qq|ORDER BY sortkey | . ($dir eq "down" ? "ASC" : "DESC") . qq| LIMIT 1|;
+  my ($sortkey_2) = selectrow_query($form, $dbh, $query, $sortkey_1, $unit_type);
+
+  if (defined($sortkey_1)) {
+    $query = qq|SELECT name FROM units WHERE sortkey = ${sortkey_2}|;
+    my ($name_2) = selectrow_query($form, $dbh, $query);
+
+    if (defined($name_2)) {
+      $query = qq|UPDATE units SET sortkey = ? WHERE name = ?|;
+      my $sth = $dbh->prepare($query);
+
+      do_statement($form, $sth, $query, $sortkey_1, $name_2);
+      do_statement($form, $sth, $query, $sortkey_2, $name_1);
+    }
+  }
+
+  $dbh->commit();
+  $dbh->disconnect();
+
+  $main::lxdebug->leave_sub();
+}
+
 1;