X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FIS.pm;h=6c0f71b10cecb5d5864aa2f56bbcff0ff35cf166;hb=7b825818e1875b623243daaef4ed436e5bbfb1ff;hp=f546b51078036e56789b0f11d09c5b27debdca39;hpb=b9b02c1a0d19519228670ffdc491df9064199ff5;p=kivitendo-erp.git

diff --git a/SL/IS.pm b/SL/IS.pm
index f546b5107..6c0f71b10 100644
--- a/SL/IS.pm
+++ b/SL/IS.pm
@@ -36,6 +36,8 @@ package IS;
 
 use Data::Dumper;
 use SL::AM;
+use SL::Common;
+use SL::DBUtils;
 
 sub invoice_details {
   $main::lxdebug->enter_sub();
@@ -56,6 +58,35 @@ sub invoice_details {
   ($form->{terms}) = $sth->fetchrow_array;
   $sth->finish;
 
+  my (@project_ids, %projectnumbers);
+
+  push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
+
+  # sort items by partsgroup
+  for $i (1 .. $form->{rowcount}) {
+    $partsgroup = "";
+    if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
+      $partsgroup = $form->{"partsgroup_$i"};
+    }
+    push @partsgroup, [$i, $partsgroup];
+    push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
+  }
+
+  if (@project_ids) {
+    $query = "SELECT id, projectnumber FROM project WHERE id IN (" .
+      join(", ", map({ "?" } @project_ids)) . ")";
+    $sth = $dbh->prepare($query);
+    $sth->execute(@project_ids) ||
+      $form->dberror($query . " (" . join(", ", @project_ids) . ")");
+    while (my $ref = $sth->fetchrow_hashref()) {
+      $projectnumbers{$ref->{id}} = $ref->{projectnumber};
+    }
+    $sth->finish();
+  }
+
+  $form->{"globalprojectnumber"} =
+    $projectnumbers{$form->{"globalproject_id"}};
+
   my $tax = 0;
   my $item;
   my $i;
@@ -89,6 +120,15 @@ sub invoice_details {
   my $subtotal_header = 0;
   my $subposition = 0;
 
+  my @arrays =
+    qw(runningnumber number description longdescription qty ship unit bin
+       deliverydate_oe ordnumber_oe transdate_oe licensenumber validuntil
+       partnotes serialnumber reqdate sellprice listprice netprice
+       discount p_discount discount_sub nodiscount_sub
+       linetotal  nodiscount_linetotal tax_rate projectnumber);
+
+  my @tax_arrays =
+    qw(taxbase tax taxdescription taxrate taxnumber);
 
   foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
     $i = $item->[0];
@@ -97,8 +137,7 @@ sub invoice_details {
       push(@{ $form->{description} }, qq|$item->[1]|);
       $sameitem = $item->[1];
 
-      map { push(@{ $form->{$_} }, "") }
-        qw(runningnumber number serialnumber bin partnotes qty unit deliverydate sellprice listprice netprice discount linetotal);
+      map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
     }
 
     $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
@@ -228,8 +267,8 @@ sub invoice_details {
       push(@{ $form->{nodiscount_linetotal} },
            $form->format_amount($myconfig, $nodiscount_linetotal, 2));
 
+      push(@{ $form->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
 
-      
       @taxaccounts = split / /, $form->{"taxaccounts_$i"};
       $taxrate     = 0;
       $taxdiff     = 0;
@@ -300,8 +339,7 @@ sub invoice_details {
 
         while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
           if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
-            map { push(@{ $form->{$_} }, "") }
-              qw(runningnumber number serialnumber unit qty bin sellprice listprice netprice discount linetotal nodiscount_linetotal);
+            map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
             $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
             push(@{ $form->{description} }, $sameitem);
           }
@@ -312,8 +350,7 @@ sub invoice_details {
                $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}
                  )
                  . qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|);
-          map { push(@{ $form->{$_} }, "") }
-            qw(number unit qty runningnumber serialnumber bin sellprice listprice netprice discount linetotal nodiscount_linetotal);
+          map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
 
         }
         $sth->finish;
@@ -328,7 +365,7 @@ sub invoice_details {
     $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
 
     push(@{ $form->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
-    push(@{ $form->{taxdescription} }, $form->{"${item}_description"});
+    push(@{ $form->{taxdescription} }, $form->{"${item}_description"}  . q{ } . 100 * $form->{"${item}_rate"} . q{%});
     push(@{ $form->{taxrate} },
           $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
     push(@{ $form->{taxnumber} }, $form->{"${item}_taxnumber"});
@@ -406,7 +443,7 @@ sub customer_details {
   }
 
   # get rest for the customer
-  my $query = qq|SELECT ct.*, cp.*, ct.notes as customernotes
+  my $query = qq|SELECT ct.*, cp.*, ct.notes as customernotes, ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail
                  FROM customer ct
                  LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
 		 WHERE ct.id = $form->{customer_id} $contact order by cp.cp_id limit 1|;
@@ -474,8 +511,6 @@ sub post_invoice {
     $form->get_employee($dbh);
   }
 
-  $form->{contact_id} = $form->{cp_id};
-  $form->{contact_id} *= 1;
   $form->{payment_id} *= 1;
   $form->{language_id} *= 1;
   $form->{taxzone_id} *= 1;
@@ -515,6 +550,12 @@ sub post_invoice {
 
     ($form->{id}) = $sth->fetchrow_array;
     $sth->finish;
+
+    if (!$form->{invnumber}) {
+      $form->{invnumber} =
+        $form->update_defaults($myconfig, $form->{type} eq "credit_note" ?
+                               "cnnumber" : "invnumber", $dbh);
+    }
   }
 
   map { $form->{$_} =~ s/\'/\'\'/g }
@@ -685,10 +726,6 @@ sub post_invoice {
         }
       }
 
-      $project_id = 'NULL';
-      if ($form->{"projectnumber_$i"}) {
-        $project_id = $form->{"projectnumber_$i"};
-      }
       $deliverydate =
         ($form->{"deliverydate_$i"})
         ? qq|'$form->{"deliverydate_$i"}'|
@@ -708,7 +745,7 @@ sub post_invoice {
 		  '$form->{"description_$i"}', '$form->{"longdescription_$i"}', $form->{"qty_$i"},
 		  $form->{"sellprice_$i"}, $fxsellprice,
 		  $form->{"discount_$i"}, $allocated, 'f',
-		  '$form->{"unit_$i"}', $deliverydate, (SELECT id from project where projectnumber = '$project_id'),
+		  '$form->{"unit_$i"}', $deliverydate, | . conv_i($form->{"project_id_$i"}, 'NULL') . qq|,
 		  '$form->{"serialnumber_$i"}', '$pricegroup_id',
 		  '$form->{"ordnumber_$i"}', '$form->{"transdate_$i"}', '$form->{"cusordnumber_$i"}', $baseqty, '$subtotal')|;
       $dbh->do($query) || $form->dberror($query);
@@ -801,6 +838,8 @@ sub post_invoice {
                                $form->{exchangerate}, 0);
   }
 
+  $project_id = conv_i($form->{"globalproject_id"});
+
   foreach my $trans_id (keys %{ $form->{amount} }) {
     foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
       next unless ($form->{expense_inventory} =~ /$accno/);
@@ -810,12 +849,12 @@ sub post_invoice {
           ) != 0
         ) {
         $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
-	            transdate, taxkey)
+	            transdate, taxkey, project_id)
 		    VALUES ($trans_id, (SELECT c.id FROM chart c
 		                        WHERE c.accno = '$accno'),
 		    $form->{amount}{$trans_id}{$accno}, '$form->{invdate}',
-                    (SELECT taxkey_id  FROM chart WHERE accno = '$accno'))|;
-        $dbh->do($query) || $form->dberror($query);
+                    (SELECT taxkey_id  FROM chart WHERE accno = '$accno'), ?)|;
+        do_query($form, $dbh, $query, $project_id);
         $form->{amount}{$trans_id}{$accno} = 0;
       }
     }
@@ -827,12 +866,12 @@ sub post_invoice {
           ) != 0
         ) {
         $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
-	            transdate, taxkey)
+	            transdate, taxkey, project_id)
 		    VALUES ($trans_id, (SELECT id FROM chart
 		                        WHERE accno = '$accno'),
 		    $form->{amount}{$trans_id}{$accno}, '$form->{invdate}',
-                    (SELECT taxkey_id  FROM chart WHERE accno = '$accno'))|;
-        $dbh->do($query) || $form->dberror($query);
+                    (SELECT taxkey_id  FROM chart WHERE accno = '$accno'), ?)|;
+        do_query($form, $dbh, $query, $project_id);
       }
     }
   }
@@ -881,23 +920,25 @@ sub post_invoice {
   
         if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
           $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
-                      transdate)
+                      transdate, taxkey, project_id)
                       VALUES ($form->{id}, (SELECT c.id FROM chart c
-                                          WHERE c.accno = '$form->{AR}'),
-                      $amount, '$form->{"datepaid_$i"}')|;
-          $dbh->do($query) || $form->dberror($query);
+                                          WHERE c.accno = ?),
+                      $amount, '$form->{"datepaid_$i"}',
+                      (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
+          do_query($form, $dbh, $query, $form->{AR}, $form->{AR}, $project_id);
         }
   
         # record payment
         $form->{"paid_$i"} *= -1;
   
         $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
-                    source, memo)
+                    source, memo, taxkey, project_id)
                     VALUES ($form->{id}, (SELECT c.id FROM chart c
-                                        WHERE c.accno = '$accno'),
+                                        WHERE c.accno = ?),
                     $form->{"paid_$i"}, '$form->{"datepaid_$i"}',
-                    '$form->{"source_$i"}', '$form->{"memo_$i"}')|;
-        $dbh->do($query) || $form->dberror($query);
+                    '$form->{"source_$i"}', '$form->{"memo_$i"}',
+                    (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
+        do_query($form, $dbh, $query, $accno, $accno, $project_id);
   
         # exchangerate difference
         $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
@@ -937,12 +978,13 @@ sub post_invoice {
         ) {
 
         $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
-	            transdate, cleared, fx_transaction)
+	            transdate, cleared, fx_transaction, taxkey, project_id)
 		    VALUES ($form->{id},
 		           (SELECT c.id FROM chart c
-		            WHERE c.accno = '$accno'),
-		    $form->{fx}{$accno}{$transdate}, '$transdate', '0', '1')|;
-        $dbh->do($query) || $form->dberror($query);
+		            WHERE c.accno = ?),
+		    $form->{fx}{$accno}{$transdate}, '$transdate', '0', '1',
+                    (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
+        do_query($form, $dbh, $query, $accno, $accno, $project_id);
       }
     }
   }
@@ -978,36 +1020,39 @@ Message: $form->{message}\r| if $form->{message};
   # save AR record
   $query = qq|UPDATE ar set
               invnumber = '$form->{invnumber}',
-	      ordnumber = '$form->{ordnumber}',
-	      quonumber = '$form->{quonumber}',
-	      cusordnumber = '$form->{cusordnumber}',
+              ordnumber = '$form->{ordnumber}',
+              quonumber = '$form->{quonumber}',
+              cusordnumber = '$form->{cusordnumber}',
               transdate = '$form->{invdate}',
+              orddate = | . conv_dateq($form->{orddate}) . qq|,
+              quodate = | . conv_dateq($form->{quodate}) . qq|,
               customer_id = $form->{customer_id},
               amount = $amount,
               netamount = $netamount,
               paid = $form->{paid},
-	      datepaid = $datepaid,
-	      duedate = $duedate,
+              datepaid = $datepaid,
+              duedate = $duedate,
               deliverydate = $deliverydate,
-	      invoice = '1',
-	      shippingpoint = '$form->{shippingpoint}',
-	      shipvia = '$form->{shipvia}',
-	      terms = $form->{terms},
-	      notes = '$form->{notes}',
-	      intnotes = '$form->{intnotes}',
-	      taxincluded = '$form->{taxincluded}',
-	      curr = '$form->{currency}',
-	      department_id = $form->{department_id},
-	      payment_id = $form->{payment_id},
+              invoice = '1',
+              shippingpoint = '$form->{shippingpoint}',
+              shipvia = '$form->{shipvia}',
+              terms = $form->{terms},
+              notes = '$form->{notes}',
+              intnotes = '$form->{intnotes}',
+              taxincluded = '$form->{taxincluded}',
+              curr = '$form->{currency}',
+              department_id = $form->{department_id},
+              payment_id = $form->{payment_id},
               type = '$form->{type}',
-	      language_id = $form->{language_id},
-	      taxzone_id = $form->{taxzone_id},
-	      shipto_id = $form->{shipto_id},
-	      delivery_customer_id = $form->{delivery_customer_id},
-	      delivery_vendor_id = $form->{delivery_vendor_id},
-	      employee_id = $form->{employee_id},
+              language_id = $form->{language_id},
+              taxzone_id = $form->{taxzone_id},
+              shipto_id = $form->{shipto_id},
+              delivery_customer_id = $form->{delivery_customer_id},
+              delivery_vendor_id = $form->{delivery_vendor_id},
+              employee_id = $form->{employee_id},
               storno = '$form->{storno}',
-              cp_id = $form->{contact_id}
+              globalproject_id = | . conv_i($form->{"globalproject_id"}, 'NULL') . qq|,
+              cp_id = | . conv_i($form->{"cp_id"}, 'NULL') . qq|
               WHERE id = $form->{id}
              |;
   $dbh->do($query) || $form->dberror($query);
@@ -1037,9 +1082,7 @@ Message: $form->{message}\r| if $form->{message};
   # save printed, emailed and queued
   $form->save_status($dbh);
 
-  if ($form->{webdav}) {
-    &webdav_folder($myconfig, $form);
-  }
+  Common::webdav_folder($form) if ($main::webdav);
 
   my $rc = $dbh->commit;
   $dbh->disconnect;
@@ -1050,9 +1093,7 @@ Message: $form->{message}\r| if $form->{message};
 }
 
 sub post_payment {
-  $main::lxdebug->enter_sub();
-
-  my ($self, $myconfig, $form, $locale) = @_;
+  $main::lxdebug->enter_sub() and my ($self, $myconfig, $form, $locale) = @_;
 
   # connect to database, turn off autocommit
   my $dbh = $form->dbconnect_noauto($myconfig);
@@ -1061,136 +1102,89 @@ sub post_payment {
 
   # total payments, don't move we need it here
   for my $i (1 .. $form->{paidaccounts}) {
-    if ($form->{type} eq "credit_note") {
-      $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
-    } else {
-      $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
-    } 
-    $form->{paid} += $form->{"paid_$i"};
-    $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
+    $form->{"paid_$i"}  = $form->parse_amount($myconfig, $form->{"paid_$i"});
+    $form->{"paid_$i"} *= -1                                 if ($form->{type} eq "credit_note");
+    $form->{"paid"}    += $form->{"paid_$i"};
+    $form->{"datepaid"} = $form->{"datepaid_$i"}             if ($form->{"datepaid_$i"});
   }
 
-  $form->{exchangerate} =
-      $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate},
-                              "buy");
+  $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
 
   # record payments and offsetting AR
   for my $i (1 .. $form->{paidaccounts}) {
+    if ($form->{"paid_$i"}) {
 
-    if ($form->{"paid_$i"} != 0) {
       my ($accno) = split /--/, $form->{"AR_paid_$i"};
-      $form->{"datepaid_$i"} = $form->{invdate}
-        unless ($form->{"datepaid_$i"});
+      $form->{"datepaid_$i"} = $form->{invdate} unless ($form->{"datepaid_$i"});
       $form->{datepaid} = $form->{"datepaid_$i"};
 
       $exchangerate = 0;
       if (($form->{currency} eq $form->{defaultcurrency}) || ($form->{defaultcurrency} eq "")) {
         $form->{"exchangerate_$i"} = 1;
       } else {
-        $exchangerate =
-          $form->check_exchangerate($myconfig, $form->{currency},
-                                    $form->{"datepaid_$i"}, 'buy');
-
-        $form->{"exchangerate_$i"} =
-          ($exchangerate)
-          ? $exchangerate
-          : $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
+        $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
+        $form->{"exchangerate_$i"} = ($exchangerate) ? $exchangerate : $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
       }
 
       # record AR
-      $amount =
-        $form->round_amount($form->{"paid_$i"} * $form->{"exchangerate"},
-                            2);
-
-
-      $query = qq|DELETE FROM acc_trans WHERE trans_id=$form->{id} AND chart_id=(SELECT c.id FROM chart c
-                                      WHERE c.accno = '$form->{AR}') AND amount=$amount AND transdate='$form->{"datepaid_$i"}'|;
-      $dbh->do($query) || $form->dberror($query);
-
-      $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
-                  transdate)
-                  VALUES ($form->{id}, (SELECT c.id FROM chart c
-                                      WHERE c.accno = '$form->{AR}'),
-                  $amount, '$form->{"datepaid_$i"}')|;
-      $dbh->do($query) || $form->dberror($query);
+      $amount = $form->round_amount($form->{"paid_$i"} * $form->{"exchangerate"}, 2);
 
+      $query = qq|DELETE FROM acc_trans WHERE trans_id = ? AND chart_id = (SELECT c.id FROM chart c WHERE c.accno = ?) AND amount = ? AND transdate = ?|;
+      do_query($form, $dbh, $query, $form->{id}, $form->{AR}, $amount, $form->{"datepaid_$i"});
+      $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, project_id, taxkey) 
+                  VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, (SELECT taxkey_id FROM chart WHERE accno = ?))|;
+      do_query($form, $dbh, $query, $form->{id}, $form->{AR}, $amount, $form->{"datepaid_$i"}, conv_i($form->{"globalproject_id"}), $accno);
 
       # record payment
       $form->{"paid_$i"} *= -1;
 
-      $query = qq|DELETE FROM acc_trans WHERE trans_id=$form->{id} AND chart_id=(SELECT c.id FROM chart c
-                                      WHERE c.accno = '$accno') AND amount=$form->{"paid_$i"} AND transdate='$form->{"datepaid_$i"}' AND source='$form->{"source_$i"}' AND memo='$form->{"memo_$i"}'|;
-      $dbh->do($query) || $form->dberror($query);
-
-      $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
-                  source, memo)
-                  VALUES ($form->{id}, (SELECT c.id FROM chart c
-		                      WHERE c.accno = '$accno'),
-		  $form->{"paid_$i"}, '$form->{"datepaid_$i"}',
-		  '$form->{"source_$i"}', '$form->{"memo_$i"}')|;
-      $dbh->do($query) || $form->dberror($query);
-
+      $query = qq|DELETE FROM acc_trans WHERE trans_id = ? AND chart_id = (SELECT c.id FROM chart c WHERE c.accno = ?) AND amount = ? AND transdate = ? AND source = ? AND memo = ?|;
+      do_query($form, $dbh, $query, $form->{id}, $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"}, $form->{"source_$i"}, $form->{"memo_$i"});
+      $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, project_id, taxkey) 
+                  VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), ?, ?, ?, ?, ?, (SELECT taxkey_id FROM chart WHERE accno = ?))|;
+      do_query($form, $dbh, $query, $form->{id}, $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"}, $form->{"source_$i"}, $form->{"memo_$i"}, 
+               conv_i($form->{"globalproject_id"}), $accno);
 
       # gain/loss
-      $amount =
-        $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
-        $form->{"exchangerate_$i"};
-      if ($amount > 0) {
-        $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
-          $amount;
-      } else {
-        $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
-          $amount;
-      }
+      $amount = $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} * $form->{"exchangerate_$i"};
+      $form->{fx}{ $form->{($amount > 0 ? 'fxgain_accno' : 'fxloss_accno')} }{ $form->{"datepaid_$i"} } += $amount;
 
       $diff = 0;
 
       # update exchange rate
       if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
-        $form->update_exchangerate($dbh, $form->{currency},
-                                   $form->{"datepaid_$i"},
-                                   $form->{"exchangerate_$i"}, 0);
+        $form->update_exchangerate($dbh, $form->{currency}, $form->{"datepaid_$i"}, $form->{"exchangerate_$i"}, 0);
       }
+
     }
   }
 
   # record exchange rate differences and gains/losses
   foreach my $accno (keys %{ $form->{fx} }) {
     foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
-      if (
-          ($form->{fx}{$accno}{$transdate} =
-           $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
-          ) != 0
-        ) {
-        $query = qq|DELETE FROM acc_trans WHERE trans_id=$form->{id} AND chart_id=(SELECT c.id FROM chart c
-                                        WHERE c.accno = '$accno') AND amount=$form->{fx}{$accno}{$transdate} AND transdate='$transdate' AND cleared='0' AND fx_transaction='1'|;
-        $dbh->do($query) || $form->dberror($query);
-        $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
-	            transdate, cleared, fx_transaction)
-		    VALUES ($form->{id},
-		           (SELECT c.id FROM chart c
-		            WHERE c.accno = '$accno'),
-		    $form->{fx}{$accno}{$transdate}, '$transdate', '0', '1')|;
-        $dbh->do($query) || $form->dberror($query);
+
+      if ($form->{fx}{$accno}{$transdate} = $form->round_amount($form->{fx}{$accno}{$transdate}, 2)) { # '=' is no typo, it's an assignment
+        $query = qq|DELETE FROM acc_trans WHERE trans_id = ? AND chart_id = (SELECT c.id FROM chart c WHERE c.accno = ?) 
+                                                AND amount = ? AND transdate = ? AND cleared = ? AND fx_transaction = ?|;
+        do_query($form, $dbh, $query, $form->{id}, $accno, $form->{fx}{$accno}{$transdate}, $transdate, 0, 1);
+        $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, project_id, taxkey)
+		                   VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), ?, ?, ?, ?, ?, (SELECT taxkey_id FROM chart WHERE accno = ?))|;
+        do_query($form, $dbh, $query, $form->{id}, $accno, $form->{fx}{$accno}{$transdate}, $transdate, 0, 1, conv_i($form->{"globalproject_id"}), $accno);
       }
+
     }
   }
-  my $datepaid = ($form->{paid})    ? qq|'$form->{datepaid}'| : "NULL";
 
   # save AR record
-  my $query = qq|UPDATE ar set
-              paid = $form->{paid},
-	      datepaid = $datepaid
-              WHERE id=$form->{id}|;
+  delete $form->{datepaid} unless $form->{paid};
 
-  $dbh->do($query) || $form->dberror($query);
+  my $query = qq|UPDATE ar set paid = ?, datepaid = ? WHERE id = ?|;
+  do_query($form, $dbh, $query, $form->{paid}, $form->{datepaid}, $form->{id});
 
   my $rc = $dbh->commit;
   $dbh->disconnect;
 
-  $main::lxdebug->leave_sub();
-
-  return $rc;
+  $main::lxdebug->leave_sub() and return $rc;
 }
 
 sub process_assembly {
@@ -1484,6 +1478,7 @@ sub retrieve_invoice {
 
     # retrieve invoice
     $query = qq|SELECT a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
+                a.orddate, a.quodate, a.globalproject_id,
                 a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
                 a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
 		a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
@@ -1656,9 +1651,7 @@ sub retrieve_invoice {
     }
     $sth->finish;
 
-    if ($form->{webdav}) {
-      &webdav_folder($myconfig, $form);
-    }
+    Common::webdav_folder($form) if ($main::webdav);
   }
 
   my $rc = $dbh->commit;
@@ -1691,46 +1684,29 @@ sub get_customer {
   my $query = qq|SELECT c.name AS customer, c.discount, c.creditlimit, c.terms,
                  c.email, c.cc, c.bcc, c.language_id, c.payment_id AS customer_payment_id,
 		 c.street, c.zipcode, c.city, c.country,
-	         $duedate + c.terms AS duedate, c.notes AS intnotes,
+	         $duedate + COALESCE(pt.terms_netto, 0) AS duedate, c.notes AS intnotes,
 		 b.discount AS tradediscount, b.description AS business, c.klass as customer_klass, c.taxzone_id
                  FROM customer c
 		 LEFT JOIN business b ON (b.id = c.business_id)
-	         WHERE c.id = $form->{customer_id}|;
-  my $sth = $dbh->prepare($query);
-  $sth->execute || $form->dberror($query);
-
-  $ref = $sth->fetchrow_hashref(NAME_lc);
-
+                 LEFT JOIN payment_terms pt ON c.payment_id = pt.id
+	         WHERE c.id = ?|;
+  $ref = selectfirst_hashref_query($form, $dbh, $query, $form->{customer_id});
   map { $form->{$_} = $ref->{$_} } keys %$ref;
-  $sth->finish;
-
-  my $query = qq|SELECT sum(a.amount-a.paid) AS dunning_amount FROM ar a WHERE a.paid < a.amount AND a.customer_id=$form->{customer_id} AND a.dunning_id IS NOT NULL|;
-  my $sth = $dbh->prepare($query);
-
-  $sth->execute || $form->dberror($query);
-  
-  $ref = $sth->fetchrow_hashref(NAME_lc);
 
+  my $query = qq|SELECT sum(a.amount - a.paid) AS dunning_amount FROM ar a 
+                 WHERE a.paid < a.amount AND a.customer_id = ? AND a.dunning_id IS NOT NULL|;
+  $ref = selectfirst_hashref_query($form, $dbh, $query, $form->{customer_id});
   map { $form->{$_} = $ref->{$_} } keys %$ref;
-  $sth->finish;
-
-  #print(STDERR "DUNNING AMOUTN $form->{dunning_amount}\n");
-
-  my $query = qq|SELECT dnn.dunning_description AS max_dunning_level FROM dunning_config dnn WHERE id in (select dunning_id from ar WHERE paid < amount AND customer_id=$form->{customer_id} AND dunning_id IS NOT NULL) ORDER BY dunning_level DESC LIMIT 1|;
-  my $sth = $dbh->prepare($query);
-
-  $sth->execute || $form->dberror($query);
-
-  $ref = $sth->fetchrow_hashref(NAME_lc);
 
+  my $query = qq|SELECT dnn.dunning_description AS max_dunning_level FROM dunning_config dnn 
+                 WHERE id in (SELECT dunning_id from ar WHERE paid < amount AND customer_id = ? AND dunning_id IS NOT NULL)
+                 ORDER BY dunning_level DESC LIMIT 1|;
+  $ref = selectfirst_hashref_query($form, $dbh, $query, $form->{customer_id});
   map { $form->{$_} = $ref->{$_} } keys %$ref;
-  $sth->finish;
-  #print(STDERR "LEVEL $form->{max_dunning_level}\n");
-
 
   #check whether payment_terms are better than old payment_terms
   if (($form->{payment_id} ne "") && ($form->{customer_payment_id} ne "")) {
-    my $query = qq|select (select ranking from payment_terms WHERE id = $form->{payment_id}), (select ranking from payment_terms WHERE id = $form->{customer_payment_id})|;
+    my $query = qq|SELECT (SELECT ranking from payment_terms WHERE id = $form->{payment_id}), (SELECT ranking FROM payment_terms WHERE id = $form->{customer_payment_id})|;
     my $stw = $dbh->prepare($query);
     $stw->execute || $form->dberror($query);
     ($old_ranking, $new_ranking) = $stw->fetchrow_array;
@@ -1771,14 +1747,6 @@ sub get_customer {
   }
   $sth->finish;
 
-  $form->get_contacts($dbh, $form->{customer_id});
-  $form->{cp_id} *= 1;
-
-  # get contact if selected
-  if ($form->{cp_id}) {
-    $form->get_contact($dbh, $form->{cp_id});
-  }
-
   # get shipto if we did not converted an order or invoice
   if (!$form->{shipto}) {
     map { delete $form->{$_} }
@@ -1809,22 +1777,9 @@ sub get_customer {
   }
   $sth->finish;
 
-  # get shipping addresses
-  $query = qq|SELECT s.shipto_id,s.shiptoname,s.shiptodepartment_1
-              FROM shipto s
-	      WHERE s.trans_id = $form->{customer_id}|;
-  $sth = $dbh->prepare($query);
-  $sth->execute || $form->dberror($query);
-
-  my $customertax = ();
-  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
-    push(@{ $form->{SHIPTO} }, $ref);
-  }
-  $sth->finish;
-
   # setup last accounts used for this customer
   if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
-    $query = qq|SELECT c.accno, c.description, c.link, c.category
+    $query = qq|SELECT c.id, c.accno, c.description, c.link, c.category
                 FROM chart c
 		JOIN acc_trans ac ON (ac.chart_id = c.id)
 		JOIN ar a ON (a.id = ac.trans_id)
@@ -1840,10 +1795,21 @@ sub get_customer {
       if ($ref->{category} eq 'I') {
         $i++;
         $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
+
+        if ($form->{initial_transdate}) {
+          my $tax_query =
+            qq|SELECT tk.tax_id, t.rate FROM taxkeys tk | .
+            qq|LEFT JOIN tax t ON tk.tax_id = t.id | .
+            qq|WHERE tk.chart_id = ? AND startdate <= ? | .
+            qq|ORDER BY tk.startdate DESC LIMIT 1|;
+          my ($tax_id, $rate) =
+            selectrow_query($form, $dbh, $tax_query, $ref->{id},
+                            $form->{initial_transdate});
+          $form->{"taxchart_$i"} = "${tax_id}--${rate}";
+        }
       }
       if ($ref->{category} eq 'A') {
-        $form->{ARselected} = $form->{AR_1} =
-          "$ref->{accno}--$ref->{description}";
+        $form->{ARselected} = $form->{AR_1} = $ref->{accno};
       }
     }
     $sth->finish;
@@ -2204,35 +2170,27 @@ sub get_pricegroups_for_parts {
   $main::lxdebug->leave_sub();
 }
 
-sub webdav_folder {
+sub has_storno {
   $main::lxdebug->enter_sub();
 
-  my ($myconfig, $form) = @_;
+  my ($self, $myconfig, $form, $table) = @_;
 
-SWITCH: {
-    $path = "webdav/rechnungen/" . $form->{invnumber}, last SWITCH
-      if ($form->{vc} eq "customer");
-    $path = "webdav/einkaufsrechnungen/" . $form->{invnumber}, last SWITCH
-      if ($form->{vc} eq "vendor");
-  }
+  $main::lxdebug->leave_sub() and return 0 unless ($form->{id});
 
-  if (!-d $path) {
-    mkdir($path, 0770) or die "can't make directory $!\n";
-  } else {
-    if ($form->{id}) {
-      @files = <$path/*>;
-      foreach $file (@files) {
-        $file =~ /\/([^\/]*)$/;
-        $fname = $1;
-        $ENV{'SCRIPT_NAME'} =~ /\/([^\/]*)\//;
-        $lxerp = $1;
-        $link  = "http://" . $ENV{'SERVER_NAME'} . "/" . $lxerp . "/" . $file;
-        $form->{WEBDAV}{$fname} = $link;
-      }
-    }
-  }
+  # make sure there's no funny stuff in $table
+  # ToDO: die when this happens and throw an error
+  $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
+
+  my $dbh = $form->dbconnect($myconfig);
+
+  my $query = qq|SELECT storno FROM $table WHERE id = ?|;
+  my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
+
+  $dbh->disconnect();
 
   $main::lxdebug->leave_sub();
+
+  return $result;
 }
 
 1;