No more " und keine abgeschnittenen Zeichen nach " Geprüft: Versandort, Transpor...

[kivitendo-erp.git] / SL / IS.pm

diff --git a/SL/IS.pm b/SL/IS.pm
index 7df695efb381baf1d469554900da0f7aad594768..f3c7855d17196f052f579aab13ec1aafe235ee76 100644 (file)
--- a/SL/IS.pm
+++ b/SL/IS.pm
@@ -34,27 +34,71 @@
 
 package IS;
 
 
 package IS;
 

-use Data::Dumper;
+#use strict;
+
+use List::Util qw(max);
+

 use SL::AM;
 use SL::AM;

+use SL::ARAP;
+use SL::CVar;
+use SL::Common;
+use SL::DBUtils;
+use SL::DO;
+use SL::MoreCommon;
+use SL::IC;
+use Data::Dumper;

 
 sub invoice_details {
   $main::lxdebug->enter_sub();
 
   my ($self, $myconfig, $form, $locale) = @_;
 
 
 sub invoice_details {
   $main::lxdebug->enter_sub();
 
   my ($self, $myconfig, $form, $locale) = @_;
 

-  $form->{duedate} = $form->{invdate} unless ($form->{duedate});
+  $form->{duedate} ||= $form->{invdate};

 
   # connect to database
   my $dbh = $form->dbconnect($myconfig);
 
   # connect to database
   my $dbh = $form->dbconnect($myconfig);

+  my $sth;

 
 

-  my $query = qq|SELECT date '$form->{duedate}' - date '$form->{invdate}'
-                 AS terms
-                FROM defaults|;
-  my $sth = $dbh->prepare($query);
-  $sth->execute || $form->dberror($query);
+  my $query = qq|SELECT date | . conv_dateq($form->{duedate}) . qq| - date | . conv_dateq($form->{invdate}) . qq| AS terms|;
+  ($form->{terms}) = selectrow_query($form, $dbh, $query);

 
 

-  ($form->{terms}) = $sth->fetchrow_array;
-  $sth->finish;
+  my (@project_ids, %projectnumbers);
+
+  push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
+
+  $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
+  my %price_factors;
+
+  foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
+    $price_factors{$pfac->{id}}  = $pfac;
+    $pfac->{factor}             *= 1;
+    $pfac->{formatted_factor}    = $form->format_amount($myconfig, $pfac->{factor});
+  }
+
+  # sort items by partsgroup
+  for my $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 $tax = 0;
   my $item;


@@ -89,6 +133,20 @@ sub invoice_details {
   my $subtotal_header = 0;
   my $subposition = 0;
 
   my $subtotal_header = 0;
   my $subposition = 0;
 

+  $form->{discount} = [];
+
+  IC->prepare_parts_for_printing();
+
+  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
+       price_factor price_factor_name partsgroup);
+
+  my @tax_arrays =
+    qw(taxbase tax taxdescription taxrate taxnumber);

 
   foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
     $i = $item->[0];
 
   foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
     $i = $item->[0];


@@ -97,13 +155,12 @@ sub invoice_details {
       push(@{ $form->{description} }, qq|$item->[1]|);
       $sameitem = $item->[1];
 
       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"});
 
     }
 
     $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
 

-    if ($form->{"qty_$i"} != 0) {
+    if ($form->{"id_$i"} != 0) {

 
       # add number, description and qty to $form->{number},
       if ($form->{"subtotal_$i"} && !$subtotal_header) {
 
       # add number, description and qty to $form->{number},
       if ($form->{"subtotal_$i"} && !$subtotal_header) {


@@ -119,36 +176,35 @@ sub invoice_details {
         $position = int($position);
         $position++;
       }
         $position = int($position);
         $position++;
       }

-      push(@{ $form->{runningnumber} }, $position);
-      push(@{ $form->{number} },        qq|$form->{"partnumber_$i"}|);
-      push(@{ $form->{serialnumber} },  qq|$form->{"serialnumber_$i"}|);
-      push(@{ $form->{bin} },           qq|$form->{"bin_$i"}|);
-      push(@{ $form->{"partnotes"} },   qq|$form->{"partnotes_$i"}|);
-      push(@{ $form->{description} },   qq|$form->{"description_$i"}|);
-      push(@{ $form->{longdescription} },   qq|$form->{"longdescription_$i"}|);
-      push(@{ $form->{qty} },
-           $form->format_amount($myconfig, $form->{"qty_$i"}));
-      push(@{ $form->{unit} },            qq|$form->{"unit_$i"}|);
-      push(@{ $form->{deliverydate_oe} }, qq|$form->{"deliverydate_$i"}|);
-
-      push(@{ $form->{sellprice} },    $form->{"sellprice_$i"});
-      push(@{ $form->{ordnumber_oe} }, qq|$form->{"ordnumber_$i"}|);
-      push(@{ $form->{transdate_oe} }, qq|$form->{"transdate_$i"}|);
-      push(@{ $form->{invnumber} }, qq|$form->{"invnumber"}|);
-      push(@{ $form->{invdate} }, qq|$form->{"invdate"}|);
+
+      my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
+
+      push @{ $form->{runningnumber} },     $position;
+      push @{ $form->{number} },            $form->{"partnumber_$i"};
+      push @{ $form->{serialnumber} },      $form->{"serialnumber_$i"};
+      push @{ $form->{bin} },               $form->{"bin_$i"};
+      push @{ $form->{"partnotes"} },       $form->{"partnotes_$i"};
+      push @{ $form->{description} },       $form->{"description_$i"};
+      push @{ $form->{longdescription} },   $form->{"longdescription_$i"};
+      push @{ $form->{qty} },               $form->format_amount($myconfig, $form->{"qty_$i"});
+      push @{ $form->{unit} },              $form->{"unit_$i"};
+      push @{ $form->{deliverydate_oe} },   $form->{"deliverydate_$i"};
+      push @{ $form->{sellprice} },         $form->{"sellprice_$i"};
+      push @{ $form->{ordnumber_oe} },      $form->{"ordnumber_$i"};
+      push @{ $form->{transdate_oe} },      $form->{"transdate_$i"};
+      push @{ $form->{invnumber} },         $form->{"invnumber"};
+      push @{ $form->{invdate} },           $form->{"invdate"};
+      push @{ $form->{price_factor} },      $price_factor->{formatted_factor};
+      push @{ $form->{price_factor_name} }, $price_factor->{description};
+      push @{ $form->{partsgroup} },        $form->{"partsgroup_$i"};

 
       if ($form->{lizenzen}) {
         if ($form->{"licensenumber_$i"}) {
 
       if ($form->{lizenzen}) {
         if ($form->{"licensenumber_$i"}) {

-          $query =
-            qq|SELECT l.licensenumber, l.validuntil FROM license l WHERE l.id = $form->{"licensenumber_$i"}|;
-          $sth = $dbh->prepare($query);
-          $sth->execute || $form->dberror($query);
-
-          ($licensenumber, $validuntil) = $sth->fetchrow_array;
+          $query = qq|SELECT licensenumber, validuntil FROM license WHERE id = ?|;
+          my ($licensenumber, $validuntil) = selectrow_query($form, $dbh, $query, conv_i($form->{"licensenumber_$i"}));

           push(@{ $form->{licensenumber} }, $licensenumber);
           push(@{ $form->{licensenumber} }, $licensenumber);

-          push(@{ $form->{validuntil} },
-               $locale->date($myconfig, $validuntil, 0));
-          $sth->finish;
+          push(@{ $form->{validuntil} }, $locale->date($myconfig, $validuntil, 0));
+

         } else {
           push(@{ $form->{licensenumber} }, "");
           push(@{ $form->{validuntil} },    "");
         } else {
           push(@{ $form->{licensenumber} }, "");
           push(@{ $form->{validuntil} },    "");


@@ -158,79 +214,57 @@ sub invoice_details {
       # listprice
       push(@{ $form->{listprice} }, $form->{"listprice_$i"});
 
       # listprice
       push(@{ $form->{listprice} }, $form->{"listprice_$i"});
 

-      my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
-      my ($dec) = ($sellprice =~ /\.(\d+)/);
-      $dec = length $dec;
-      my $decimalplaces = ($dec > 2) ? $dec : 2;
-
-      my $i_discount =
-        $form->round_amount(
-                            $sellprice * $form->parse_amount($myconfig,
-                                                 $form->{"discount_$i"}) / 100,
-                            $decimalplaces);
-
-      my $discount =
-        $form->round_amount($form->{"qty_$i"} * $i_discount, $decimalplaces);
+      my $sellprice     = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
+      my ($dec)         = ($sellprice =~ /\.(\d+)/);
+      my $decimalplaces = max 2, length($dec);

 
 

-      # keep a netprice as well, (sellprice - discount)
-      $form->{"netprice_$i"} = $sellprice - $i_discount;
+      my $parsed_discount      = $form->parse_amount($myconfig, $form->{"discount_$i"});
+      my $linetotal_exact      =                     $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
+      my $linetotal            = $form->round_amount($linetotal_exact, 2);
+      my $discount             = $form->round_amount($form->{"qty_$i"} * $sellprice * $parsed_discount / 100 / $price_factor->{factor} - ($linetotal - $linetotal_exact),
+                                                     $decimalplaces);
+      my $nodiscount_linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice / $price_factor->{factor}, 2);
+      $form->{"netprice_$i"}   = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);

 
 

-      push(@{ $form->{netprice} },
-           ($form->{"netprice_$i"} != 0)
-           ? $form->format_amount(
-                                 $myconfig, $form->{"netprice_$i"},
-                                 $decimalplaces
-             )
-           : " ");
+      push @{ $form->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';

 
 

-      my $linetotal =
-        $form->round_amount($form->{"qty_$i"} * $form->{"netprice_$i"}, 2);
+      $linetotal = ($linetotal != 0) ? $linetotal : '';

 
 

-      my $nodiscount_linetotal =
-        $form->round_amount($form->{"qty_$i"} * $sellprice, 2);
+      push @{ $form->{discount} },   ($discount  != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
+      push @{ $form->{p_discount} }, $form->{"discount_$i"};

 
 

-      $discount =
-        ($discount != 0)
-        ? $form->format_amount($myconfig, $discount * -1, $decimalplaces)
-        : " ";
-      $linetotal = ($linetotal != 0) ? $linetotal : " ";
+      $form->{total}            += $linetotal;
+      $form->{nodiscount_total} += $nodiscount_linetotal;
+      $form->{discount_total}   += $discount;

 
 

-      push(@{ $form->{discount} },   $discount);
-      push(@{ $form->{p_discount} }, $form->{"discount_$i"});
-      if (($form->{"discount_$i"} ne "") && ($form->{"discount_$i"} != 0)) {
-        $form->{discount_p} = $form->{"discount_$i"};
+      if ($subtotal_header) {
+        $discount_subtotal   += $linetotal;
+        $nodiscount_subtotal += $nodiscount_linetotal;

       }
       }

-      $form->{total} += $linetotal;
-      $discount_subtotal += $linetotal;
-      $form->{nodiscount_total} += $nodiscount_linetotal;
-      $nodiscount_subtotal += $nodiscount_linetotal;
-      $form->{discount_total} += $form->parse_amount($myconfig, $discount);

 
       if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
 
       if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {

-        $discount_subtotal = $form->format_amount($myconfig, $discount_subtotal, 2);
-        push(@{ $form->{discount_sub} },  $discount_subtotal);
-        $nodiscount_subtotal = $form->format_amount($myconfig, $nodiscount_subtotal, 2);
-        push(@{ $form->{nodiscount_sub} }, $nodiscount_subtotal);
-        $discount_subtotal = 0;
+        push @{ $form->{discount_sub} },   $form->format_amount($myconfig, $discount_subtotal,   2);
+        push @{ $form->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
+
+        $discount_subtotal   = 0;

         $nodiscount_subtotal = 0;
         $nodiscount_subtotal = 0;

-        $subtotal_header = 0;
+        $subtotal_header     = 0;
+

       } else {
       } else {

-        push(@{ $form->{discount_sub} }, "");
-        push(@{ $form->{nodiscount_sub} }, "");
+        push @{ $form->{discount_sub} },   "";
+        push @{ $form->{nodiscount_sub} }, "";

       }
 
       }
 

-      if ($linetotal == $netto_linetotal) {
+      if (!$form->{"discount_$i"}) {

         $nodiscount += $linetotal;
       }
 
         $nodiscount += $linetotal;
       }
 

-      push(@{ $form->{linetotal} },
-           $form->format_amount($myconfig, $linetotal, 2));
-      push(@{ $form->{nodiscount_linetotal} },
-           $form->format_amount($myconfig, $nodiscount_linetotal, 2));
+      push @{ $form->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
+      push @{ $form->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);

 
 

+      push(@{ $form->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});

 
 

-      
-      @taxaccounts = split / /, $form->{"taxaccounts_$i"};
+      @taxaccounts = split(/ /, $form->{"taxaccounts_$i"});

       $taxrate     = 0;
       $taxdiff     = 0;
 
       $taxrate     = 0;
       $taxdiff     = 0;
 


@@ -248,32 +282,28 @@ sub invoice_details {
 
       if ($form->round_amount($taxrate, 7) == 0) {
         if ($form->{taxincluded}) {
 
       if ($form->round_amount($taxrate, 7) == 0) {
         if ($form->{taxincluded}) {

-          foreach $item (@taxaccounts) {
-            $taxamount =
-              $form->round_amount($linetotal * $form->{"${item}_rate"} /
-                                    (1 + abs($form->{"${item}_rate"})),
-                                  2);
+          foreach my $accno (@taxaccounts) {
+            $taxamount            = $form->round_amount($linetotal * $form->{"${accno}_rate"} / (1 + abs($form->{"${accno}_rate"})), 2);

 
 

-            $taxaccounts{$item} += $taxamount;
-            $taxdiff            += $taxamount;
+            $taxaccounts{$accno} += $taxamount;
+            $taxdiff             += $taxamount;

 
 

-            $taxbase{$item} += $taxbase;
+            $taxbase{$accno}     += $taxbase;

           }
           $taxaccounts{ $taxaccounts[0] } += $taxdiff;
         } else {
           }
           $taxaccounts{ $taxaccounts[0] } += $taxdiff;
         } else {

-          foreach $item (@taxaccounts) {
-            $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
-            $taxbase{$item}     += $taxbase;
+          foreach my $accno (@taxaccounts) {
+            $taxaccounts{$accno} += $linetotal * $form->{"${accno}_rate"};
+            $taxbase{$accno}     += $taxbase;

           }
         }
       } else {
           }
         }
       } else {

-        foreach $item (@taxaccounts) {
-          $taxaccounts{$item} +=
-            $taxamount * $form->{"${item}_rate"} / $taxrate;
-          $taxbase{$item} += $taxbase;
+        foreach my $accno (@taxaccounts) {
+          $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
+          $taxbase{$accno}     += $taxbase;

         }
       }
         }
       }

-      $tax_rate = $taxrate * 100;
+      my $tax_rate = $taxrate * 100;

       push(@{ $form->{tax_rate} }, qq|$tax_rate|);
       if ($form->{"assembly_$i"}) {
         $sameitem = "";
       push(@{ $form->{tax_rate} }, qq|$tax_rate|);
       if ($form->{"assembly_$i"}) {
         $sameitem = "";


@@ -287,21 +317,17 @@ sub invoice_details {
           $sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|;
         }
 
           $sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|;
         }
 

-        $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty,
-                   pg.partsgroup
-                   FROM assembly a
-                   JOIN parts p ON (a.parts_id = p.id)
-                   LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
-                   WHERE a.bom = '1'
-                   AND a.id = '$form->{"id_$i"}'
-                   $sortorder|;
-        $sth = $dbh->prepare($query);
-        $sth->execute || $form->dberror($query);
-
-        while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
+        $query =
+          qq|SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup
+             FROM assembly a
+             JOIN parts p ON (a.parts_id = p.id)
+             LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
+             WHERE (a.bom = '1') AND (a.id = ?) $sortorder|;
+        $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
+
+        while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {

           if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
           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);
           }
             $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
             push(@{ $form->{description} }, $sameitem);
           }


@@ -312,8 +338,7 @@ sub invoice_details {
                $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}
                  )
                  . qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|);
                $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;
 
         }
         $sth->finish;


@@ -328,7 +353,7 @@ sub invoice_details {
     $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
 
     push(@{ $form->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
     $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"});
     push(@{ $form->{taxrate} },
           $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
     push(@{ $form->{taxnumber} }, $form->{"${item}_taxnumber"});


@@ -337,7 +362,7 @@ sub invoice_details {
   for my $i (1 .. $form->{paidaccounts}) {
     if ($form->{"paid_$i"}) {
       push(@{ $form->{payment} }, $form->{"paid_$i"});
   for my $i (1 .. $form->{paidaccounts}) {
     if ($form->{"paid_$i"}) {
       push(@{ $form->{payment} }, $form->{"paid_$i"});

-      my ($accno, $description) = split /--/, $form->{"AR_paid_$i"};
+      my ($accno, $description) = split(/--/, $form->{"AR_paid_$i"});

       push(@{ $form->{paymentaccount} }, $description);
       push(@{ $form->{paymentdate} },    $form->{"datepaid_$i"});
       push(@{ $form->{paymentsource} },  $form->{"source_$i"});
       push(@{ $form->{paymentaccount} }, $description);
       push(@{ $form->{paymentdate} },    $form->{"datepaid_$i"});
       push(@{ $form->{paymentsource} },  $form->{"source_$i"});


@@ -345,21 +370,25 @@ sub invoice_details {
       $form->{paid} += $form->parse_amount($myconfig, $form->{"paid_$i"});
     }
   }
       $form->{paid} += $form->parse_amount($myconfig, $form->{"paid_$i"});
     }
   }

+  if($form->{taxincluded}) {
+    $form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2);
+  }
+  else {
+    $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
+  }

 
 

-  $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
-  $yesdiscount = $form->{nodiscount_total} - $nodiscount;

   $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
   $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);

-  $form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
-  $form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
-  $form->{yesdiscount} = $form->format_amount($myconfig, $yesdiscount, 2);
-
-  $form->{invtotal} =
-    ($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
-  $form->{total} =
-    $form->format_amount($myconfig, $form->{invtotal} - $form->{paid}, 2);
+  $form->{discount_total}      = $form->format_amount($myconfig, $form->{discount_total}, 2);
+  $form->{nodiscount}          = $form->format_amount($myconfig, $nodiscount, 2);
+  $form->{yesdiscount}         = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
+
+  $form->{invtotal} = ($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
+  $form->{total}    = $form->format_amount($myconfig, $form->{invtotal} - $form->{paid}, 2);
+

   $form->{invtotal} = $form->format_amount($myconfig, $form->{invtotal}, 2);
   $form->{invtotal} = $form->format_amount($myconfig, $form->{invtotal}, 2);

+  $form->{paid}     = $form->format_amount($myconfig, $form->{paid}, 2);
+

   $form->set_payment_options($myconfig, $form->{invdate});
   $form->set_payment_options($myconfig, $form->{invdate});

-  $form->{paid} = $form->format_amount($myconfig, $form->{paid}, 2);

 
   $form->{username} = $myconfig->{name};
 
 
   $form->{username} = $myconfig->{name};
 


@@ -372,16 +401,10 @@ sub project_description {
   $main::lxdebug->enter_sub();
 
   my ($self, $dbh, $id) = @_;
   $main::lxdebug->enter_sub();
 
   my ($self, $dbh, $id) = @_;

+  my $form = \%main::form;

 
 

-  my $query = qq|SELECT p.description
-                 FROM project p
-                WHERE p.id = $id|;
-  my $sth = $dbh->prepare($query);
-  $sth->execute || $form->dberror($query);
-
-  ($_) = $sth->fetchrow_array;
-
-  $sth->finish;
+  my $query = qq|SELECT description FROM project WHERE id = ?|;
+  my ($description) = selectrow_query($form, $dbh, $query, conv_i($id));

 
   $main::lxdebug->leave_sub();
 
 
   $main::lxdebug->leave_sub();
 


@@ -391,7 +414,7 @@ sub project_description {
 sub customer_details {
   $main::lxdebug->enter_sub();
 
 sub customer_details {
   $main::lxdebug->enter_sub();
 

-  my ($self, $myconfig, $form) = @_;
+  my ($self, $myconfig, $form, @wanted_vars) = @_;

 
   # connect to database
   my $dbh = $form->dbconnect($myconfig);
 
   # connect to database
   my $dbh = $form->dbconnect($myconfig);


@@ -399,51 +422,64 @@ sub customer_details {
   # get contact id, set it if nessessary
   $form->{cp_id} *= 1;
 
   # get contact id, set it if nessessary
   $form->{cp_id} *= 1;
 

-  $contact = "";
+  my @values =  (conv_i($form->{customer_id}));
+
+  my $where = "";

   if ($form->{cp_id}) {
   if ($form->{cp_id}) {

-    $contact = "and cp.cp_id = $form->{cp_id}";
+    $where = qq| AND (cp.cp_id = ?) |;
+    push(@values, conv_i($form->{cp_id}));

   }
 
   # get rest for the customer
   }
 
   # get rest for the customer

-  my $query = qq|SELECT ct.*, cp.*, ct.notes as customernotes
-                 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|;
-  my $sth = $dbh->prepare($query);
-  $sth->execute || $form->dberror($query);
-
-  $ref = $sth->fetchrow_hashref(NAME_lc);
+  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 = ?) $where
+       ORDER BY cp.cp_id
+       LIMIT 1|;
+  my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);

 
   # remove id and taxincluded before copy back
   delete @$ref{qw(id taxincluded)};
 
   # remove id and taxincluded before copy back
   delete @$ref{qw(id taxincluded)};

+
+  @wanted_vars = grep({ $_ } @wanted_vars);
+  if (scalar(@wanted_vars) > 0) {
+    my %h_wanted_vars;
+    map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
+    map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
+  }
+

   map { $form->{$_} = $ref->{$_} } keys %$ref;
   map { $form->{$_} = $ref->{$_} } keys %$ref;

-  $sth->finish;

 
   if ($form->{delivery_customer_id}) {
 
   if ($form->{delivery_customer_id}) {

-    my $query = qq|SELECT ct.*, ct.notes as customernotes
-                 FROM customer ct
-                WHERE ct.id = $form->{delivery_customer_id} limit 1|;
-    my $sth = $dbh->prepare($query);
-    $sth->execute || $form->dberror($query);
-
-    $ref = $sth->fetchrow_hashref(NAME_lc);
+    $query =
+      qq|SELECT *, notes as customernotes
+         FROM customer
+         WHERE id = ?
+         LIMIT 1|;
+    $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));

 
 

-    $sth->finish;

     map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
   }
 
   if ($form->{delivery_vendor_id}) {
     map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
   }
 
   if ($form->{delivery_vendor_id}) {

-    my $query = qq|SELECT ct.*, ct.notes as customernotes
-                 FROM customer ct
-                WHERE ct.id = $form->{delivery_vendor_id} limit 1|;
-    my $sth = $dbh->prepare($query);
-    $sth->execute || $form->dberror($query);
-
-    $ref = $sth->fetchrow_hashref(NAME_lc);
+    $query =
+      qq|SELECT *, notes as customernotes
+         FROM customer
+         WHERE id = ?
+         LIMIT 1|;
+    $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));

 
 

-    $sth->finish;

     map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
   }
     map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
   }

+
+  my $custom_variables = CVar->get_custom_variables('dbh'      => $dbh,
+                                                    'module'   => 'CT',
+                                                    'trans_id' => $form->{customer_id});
+  map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
+

   $dbh->disconnect;
 
   $main::lxdebug->leave_sub();
   $dbh->disconnect;
 
   $main::lxdebug->leave_sub();


@@ -452,74 +488,53 @@ sub customer_details {
 sub post_invoice {
   $main::lxdebug->enter_sub();
 
 sub post_invoice {
   $main::lxdebug->enter_sub();
 

-  my ($self, $myconfig, $form) = @_;
+  my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;

 
   # connect to database, turn off autocommit
 
   # connect to database, turn off autocommit

-  my $dbh = $form->dbconnect_noauto($myconfig);
+  my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect_noauto($myconfig);

 
 

-  my ($query, $sth, $null, $project_id, $deliverydate);
+  my ($query, $sth, $null, $project_id, @values);

   my $exchangerate = 0;
 
   my $exchangerate = 0;
 

-  ($null, $form->{employee_id}) = split /--/, $form->{employee};
-  unless ($form->{employee_id}) {
+  if (!$form->{employee_id}) {

     $form->get_employee($dbh);
   }
     $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;
-  $form->{delivery_customer_id} *= 1;
-  $form->{delivery_vendor_id} *= 1;
-  $form->{storno} *= 1;
-  $form->{shipto_id} *= 1;
-
+  
+  $form->{defaultcurrency} = $form->get_default_currency($myconfig);

 
   ($null, $form->{department_id}) = split(/--/, $form->{department});
 
   ($null, $form->{department_id}) = split(/--/, $form->{department});

-  $form->{department_id} *= 1;
-
-  my $service_units = AM->retrieve_units($myconfig,$form,"service");
-  my $part_units = AM->retrieve_units($myconfig,$form,"dimension");
-
-
-
-  if ($form->{id}) {
-
-    &reverse_invoice($dbh, $form);
-
-  } else {
-    my $uid = rand() . time;

 
 

-    $uid .= $form->{login};
+  my $all_units = AM->retrieve_units($myconfig, $form);

 
 

-    $uid = substr($uid, 2, 75);
+  if (!$payments_only) {
+    if ($form->{id}) {
+      &reverse_invoice($dbh, $form);

 
 

-    $query = qq|INSERT INTO ar (invnumber, employee_id)
-                VALUES ('$uid', $form->{employee_id})|;
-    $dbh->do($query) || $form->dberror($query);
+    } else {
+      $query = qq|SELECT nextval('glid')|;
+      ($form->{"id"}) = selectrow_query($form, $dbh, $query);

 
 

-    $query = qq|SELECT a.id FROM ar a
-                WHERE a.invnumber = '$uid'|;
-    $sth = $dbh->prepare($query);
-    $sth->execute || $form->dberror($query);
+      $query = qq|INSERT INTO ar (id, invnumber) VALUES (?, ?)|;
+      do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"});

 
 

-    ($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 }
-    (qw(invnumber shippingpoint shipvia notes intnotes message));
-

   my ($netamount, $invoicediff) = (0, 0);
   my ($amount, $linetotal, $lastincomeaccno);
 
   my ($netamount, $invoicediff) = (0, 0);
   my ($amount, $linetotal, $lastincomeaccno);
 

-  if ($form->{currency} eq $form->{defaultcurrency}) {
+  my ($currencies)    = selectfirst_array_query($form, $dbh, qq|SELECT curr FROM defaults|);
+  my $defaultcurrency = (split m/:/, $currencies)[0];
+
+  if ($form->{currency} eq $defaultcurrency) {

     $form->{exchangerate} = 1;
   } else {
     $form->{exchangerate} = 1;
   } else {

-    $exchangerate =
-      $form->check_exchangerate($myconfig, $form->{currency},
-                                $form->{transdate}, 'buy');
+    $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, 'buy');

   }
 
   $form->{exchangerate} =
   }
 
   $form->{exchangerate} =


@@ -529,58 +544,59 @@ sub post_invoice {
 
   $form->{expense_inventory} = "";
 
 
   $form->{expense_inventory} = "";
 

+  my %baseunits;
+
+  $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
+  my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
+  my $price_factor;
+
+  $form->{amount_cogs} = {};
+

   foreach my $i (1 .. $form->{rowcount}) {
     if ($form->{type} eq "credit_note") {
       $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
   foreach my $i (1 .. $form->{rowcount}) {
     if ($form->{type} eq "credit_note") {
       $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;

-      $form->{shipped} = 1;   
+      $form->{shipped} = 1;

     } else {
       $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
     }
     my $basefactor;
     } else {
       $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
     }
     my $basefactor;

-    my $basqty;
+    my $baseqty;
+
+    $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
+    $form->{"marge_total_$i"} = $form->parse_amount($myconfig, $form->{"marge_total_$i"}) * 1;
+    $form->{"lastcost_$i"} = $form->{"lastcost_$i"} * 1;

 
     if ($form->{storno}) {
       $form->{"qty_$i"} *= -1;
     }
 
 
     if ($form->{storno}) {
       $form->{"qty_$i"} *= -1;
     }
 

-    if ($form->{"qty_$i"} != 0) {
-
-      # get item baseunit
-      $query = qq|SELECT p.unit
-                  FROM parts p
-                  WHERE p.id = $form->{"id_$i"}|;
-      $sth = $dbh->prepare($query);
-      $sth->execute || $form->dberror($query);
+    if ($form->{"id_$i"}) {
+      my $item_unit;

 
 

-      my ($item_unit) = $sth->fetchrow_array();
-      $sth->finish;
-
-      if ($form->{"inventory_accno_$i"}) {
-        if (defined($part_units->{$item_unit}->{factor}) && $part_units->{$item_unit}->{factor} ne '' && $part_units->{$item_unit}->{factor} ne '0') {
-          $basefactor = $part_units->{$form->{"unit_$i"}}->{factor} / $part_units->{$item_unit}->{factor};
-        } else {
-          $basefactor = 1;
-        }
-        $baseqty = $form->{"qty_$i"} * $basefactor;
+      if (defined($baseunits{$form->{"id_$i"}})) {
+        $item_unit = $baseunits{$form->{"id_$i"}};

       } else {
       } else {

-        if (defined($service_units->{$item_unit}->{factor}) && $service_units->{$item_unit}->{factor} ne '' && $service_units->{$item_unit}->{factor} ne '0') {
-          $basefactor = $service_units->{$form->{"unit_$i"}}->{factor} / $service_units->{$item_unit}->{factor};
-        } else {
-          $basefactor = 1;
-        }
-        $baseqty = $form->{"qty_$i"} * $basefactor;
+        # get item baseunit
+        $query = qq|SELECT unit FROM parts WHERE id = ?|;
+        ($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
+        $baseunits{$form->{"id_$i"}} = $item_unit;

       }
 
       }
 

-      map { $form->{"${_}_$i"} =~ s/\'/\'\'/g }
-        (qw(partnumber description unit));
-
-      # undo discount formatting
-      $form->{"discount_$i"} =
-        $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
+      if (defined($all_units->{$item_unit}->{factor})
+          && ($all_units->{$item_unit}->{factor} ne '')
+          && ($all_units->{$item_unit}->{factor} != 0)) {
+        $basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
+      } else {
+        $basefactor = 1;
+      }
+      $baseqty = $form->{"qty_$i"} * $basefactor;

 
       my ($allocated, $taxrate) = (0, 0);
       my $taxamount;
 
 
       my ($allocated, $taxrate) = (0, 0);
       my $taxamount;
 

+      # add tax rates
+      map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});
+

       # keep entered selling price
       my $fxsellprice =
         $form->parse_amount($myconfig, $form->{"sellprice_$i"});
       # keep entered selling price
       my $fxsellprice =
         $form->parse_amount($myconfig, $form->{"sellprice_$i"});


@@ -589,19 +605,15 @@ sub post_invoice {
       $dec = length $dec;
       my $decimalplaces = ($dec > 2) ? $dec : 2;
 
       $dec = length $dec;
       my $decimalplaces = ($dec > 2) ? $dec : 2;
 

-      # deduct discount
-      my $discount =
-        $form->round_amount($fxsellprice * $form->{"discount_$i"},
-                            $decimalplaces);
-      $form->{"sellprice_$i"} = $fxsellprice - $discount;
+      # undo discount formatting
+      $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;

 
 

-      # add tax rates
-      map { $taxrate += $form->{"${_}_rate"} } split / /,
-        $form->{"taxaccounts_$i"};
+      # deduct discount
+      $form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});

 
       # round linetotal to 2 decimal places
 
       # round linetotal to 2 decimal places

-      $linetotal =
-        $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2);
+      $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
+      $linetotal    = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);

 
       if ($form->{taxincluded}) {
         $taxamount = $linetotal * ($taxrate / (1 + $taxrate));
 
       if ($form->{taxincluded}) {
         $taxamount = $linetotal * ($taxrate / (1 + $taxrate));


@@ -617,16 +629,13 @@ sub post_invoice {
         map {
           $form->{amount}{ $form->{id} }{$_} +=
             $taxamount * $form->{"${_}_rate"} / $taxrate
         map {
           $form->{amount}{ $form->{id} }{$_} +=
             $taxamount * $form->{"${_}_rate"} / $taxrate

-        } split / /, $form->{"taxaccounts_$i"};
+        } split(/ /, $form->{"taxaccounts_$i"});

       }
 
       # add amount to income, $form->{amount}{trans_id}{accno}
       }
 
       # add amount to income, $form->{amount}{trans_id}{accno}

-      $amount =
-        $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate};
+      $amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;

 
 

-      $linetotal =
-        $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"}, 2) *
-        $form->{exchangerate};
+      $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};

       $linetotal = $form->round_amount($linetotal, 2);
 
       # this is the difference from the inventory
       $linetotal = $form->round_amount($linetotal, 2);
 
       # this is the difference from the inventory


@@ -642,84 +651,55 @@ sub post_invoice {
         $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
                             $decimalplaces);
 
         $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
                             $decimalplaces);
 

-      if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {
+      next if $payments_only;

 
 

-        # adjust parts onhand quantity
+      if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {

 
         if ($form->{"assembly_$i"}) {
 
         if ($form->{"assembly_$i"}) {

-
-          # do not update if assembly consists of all services
-          $query = qq|SELECT sum(p.inventory_accno_id)
-                     FROM parts p
-                     JOIN assembly a ON (a.parts_id = p.id)
-                     WHERE a.id = $form->{"id_$i"}|;
-          $sth = $dbh->prepare($query);
-          $sth->execute || $form->dberror($query);
-
-          if ($sth->fetchrow_array) {
-            $form->update_balance($dbh, "parts", "onhand",
-                                  qq|id = $form->{"id_$i"}|,
-                                  $baseqty * -1)
-              unless $form->{shipped};
-          }
-          $sth->finish;
-

           # record assembly item as allocated
           &process_assembly($dbh, $form, $form->{"id_$i"}, $baseqty);
           # record assembly item as allocated
           &process_assembly($dbh, $form, $form->{"id_$i"}, $baseqty);

-        } else {
-          $form->update_balance($dbh, "parts", "onhand",
-                                qq|id = $form->{"id_$i"}|,
-                                $baseqty * -1)
-            unless $form->{shipped};

 
 

-          $allocated = &cogs($dbh, $form, $form->{"id_$i"}, $baseqty, $basefactor);
+        } else {
+          $allocated = &cogs($dbh, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);

         }
       }
 
         }
       }
 

-      $project_id = 'NULL';
-      if ($form->{"projectnumber_$i"}) {
-        $project_id = $form->{"projectnumber_$i"};
-      }
-      $deliverydate =
-        ($form->{"deliverydate_$i"})
-        ? qq|'$form->{"deliverydate_$i"}'|
-        : "NULL";
-

       # get pricegroup_id and save it
       # get pricegroup_id and save it

-      ($null, my $pricegroup_id) = split /--/, $form->{"sellprice_pg_$i"};
+      ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});

       $pricegroup_id *= 1;
       $pricegroup_id *= 1;

-      my $subtotal = $form->{"subtotal_$i"} * 1;

 
       # save detail record in invoice table
 
       # save detail record in invoice table

-      $query = qq|INSERT INTO invoice (trans_id, parts_id, description,longdescription, qty,
-                  sellprice, fxsellprice, discount, allocated, assemblyitem,
-                 unit, deliverydate, project_id, serialnumber, pricegroup_id,
-                 ordnumber, transdate, cusordnumber, base_qty, subtotal)
-                 VALUES ($form->{id}, $form->{"id_$i"},
-                 '$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->{"serialnumber_$i"}', '$pricegroup_id',
-                 '$form->{"ordnumber_$i"}', '$form->{"transdate_$i"}', '$form->{"cusordnumber_$i"}', $baseqty, '$subtotal')|;
-      $dbh->do($query) || $form->dberror($query);
-
-      if ($form->{lizenzen}) {
-        if ($form->{"licensenumber_$i"}) {
-          $query =
-            qq|SELECT i.id FROM invoice i WHERE i.trans_id=$form->{id} ORDER BY i.oid DESC LIMIT 1|;
-          $sth = $dbh->prepare($query);
-          $sth->execute || $form->dberror($query);
-
-          ($invoice_row_id) = $sth->fetchrow_array;
-          $sth->finish;
-
-          $query =
-            qq|INSERT INTO licenseinvoice (trans_id, license_id) VALUES ($invoice_row_id, $form->{"licensenumber_$i"})|;
-          $dbh->do($query) || $form->dberror($query);
-        }
+      $query =
+        qq|INSERT INTO invoice (trans_id, parts_id, description, longdescription, qty,
+                                sellprice, fxsellprice, discount, allocated, assemblyitem,
+                                unit, deliverydate, project_id, serialnumber, pricegroup_id,
+                                ordnumber, transdate, cusordnumber, base_qty, subtotal,
+                                marge_percent, marge_total, lastcost,
+                                price_factor_id, price_factor, marge_price_factor)
+           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
+                   (SELECT factor FROM price_factors WHERE id = ?), ?)|;
+
+      @values = (conv_i($form->{id}), conv_i($form->{"id_$i"}),
+                 $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
+                 $form->{"sellprice_$i"}, $fxsellprice,
+                 $form->{"discount_$i"}, $allocated, 'f',
+                 $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
+                 $form->{"serialnumber_$i"}, conv_i($pricegroup_id),
+                 $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
+                 $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
+                 $form->{"marge_percent_$i"}, $form->{"marge_total_$i"},
+                 $form->{"lastcost_$i"},
+                 conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
+                 conv_i($form->{"marge_price_factor_$i"}));
+      do_query($form, $dbh, $query, @values);
+
+      if ($form->{lizenzen} && $form->{"licensenumber_$i"}) {
+        $query =
+          qq|INSERT INTO licenseinvoice (trans_id, license_id)
+             VALUES ((SELECT id FROM invoice WHERE trans_id = ? ORDER BY oid DESC LIMIT 1), ?)|;
+        @values = (conv_i($form->{"id"}), conv_i($form->{"licensenumber_$i"}));
+        do_query($form, $dbh, $query, @values);

       }
       }

-

     }
   }
 
     }
   }
 


@@ -747,7 +727,7 @@ sub post_invoice {
     $diff += $amount - $netamount * $form->{exchangerate};
     $netamount = $amount;
 
     $diff += $amount - $netamount * $form->{exchangerate};
     $netamount = $amount;
 

-    foreach my $item (split / /, $form->{taxaccounts}) {
+    foreach my $item (split(/ /, $form->{taxaccounts})) {

       $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
       $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
       $tax += $form->{amount}{ $form->{id} }{$item};
       $amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
       $form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
       $tax += $form->{amount}{ $form->{id} }{$item};


@@ -764,7 +744,7 @@ sub post_invoice {
     $amount    = $form->round_amount($netamount * $form->{exchangerate}, 2);
     $diff      = $amount - $netamount * $form->{exchangerate};
     $netamount = $amount;
     $amount    = $form->round_amount($netamount * $form->{exchangerate}, 2);
     $diff      = $amount - $netamount * $form->{exchangerate};
     $netamount = $amount;

-    foreach my $item (split / /, $form->{taxaccounts}) {
+    foreach my $item (split(/ /, $form->{taxaccounts})) {

       $form->{amount}{ $form->{id} }{$item} =
         $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
       $amount =
       $form->{amount}{ $form->{id} }{$item} =
         $form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
       $amount =


@@ -787,43 +767,69 @@ sub post_invoice {
   $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
 
   # update exchangerate
   $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
 
   # update exchangerate

-  if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
+  if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {

     $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
                                $form->{exchangerate}, 0);
   }
 
     $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
                                $form->{exchangerate}, 0);
   }
 

+  $project_id = conv_i($form->{"globalproject_id"});
+
+  foreach my $trans_id (keys %{ $form->{amount_cogs} }) {
+    foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
+      next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
+
+      $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
+
+      if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
+        $query =
+          qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
+               VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
+        @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
+        do_query($form, $dbh, $query, @values);
+        $form->{amount_cogs}{$trans_id}{$accno} = 0;
+      }
+    }
+
+    foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
+      $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
+
+      if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
+        $query =
+          qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
+               VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
+        @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
+        do_query($form, $dbh, $query, @values);
+      }
+    }
+  }
+

   foreach my $trans_id (keys %{ $form->{amount} }) {
     foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
   foreach my $trans_id (keys %{ $form->{amount} }) {
     foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {

-      next unless ($form->{expense_inventory} =~ /$accno/);
-      if (
-          ($form->{amount}{$trans_id}{$accno} =
-           $form->round_amount($form->{amount}{$trans_id}{$accno}, 2)
-          ) != 0
-        ) {
-        $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
-                   transdate, taxkey)
-                   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);
+      next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
+
+      $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
+
+      if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
+        $query =
+          qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
+             VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
+                     (SELECT taxkey_id  FROM chart WHERE accno = ?), ?)|;
+        @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
+        do_query($form, $dbh, $query, @values);

         $form->{amount}{$trans_id}{$accno} = 0;
       }
     }
 
     foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
         $form->{amount}{$trans_id}{$accno} = 0;
       }
     }
 
     foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {

-      if (
-          ($form->{amount}{$trans_id}{$accno} =
-           $form->round_amount($form->{amount}{$trans_id}{$accno}, 2)
-          ) != 0
-        ) {
-        $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount,
-                   transdate, taxkey)
-                   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);
+      $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
+
+      if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
+        $query =
+          qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
+             VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
+                     (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
+        @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
+        do_query($form, $dbh, $query, @values);

       }
     }
   }
       }
     }
   }


@@ -837,85 +843,90 @@ sub post_invoice {
     }
   }
 
     }
   }
 

-  # force AR entry if 0
-  #  $form->{amount}{$form->{id}}{$form->{AR}} = 1 if ($form->{amount}{$form->{id}}{$form->{AR}} == 0);
-

   # record payments and offsetting AR
   if (!$form->{storno}) {
     for my $i (1 .. $form->{paidaccounts}) {
   # record payments and offsetting AR
   if (!$form->{storno}) {
     for my $i (1 .. $form->{paidaccounts}) {

-  
-      if ($form->{"paid_$i"} != 0) {
-        my ($accno) = split /--/, $form->{"AR_paid_$i"};
-        $form->{"datepaid_$i"} = $form->{invdate}
-          unless ($form->{"datepaid_$i"});
-        $form->{datepaid} = $form->{"datepaid_$i"};
-  
-        $exchangerate = 0;
-  
-        if ($form->{currency} eq $form->{defaultcurrency}) {
-          $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"});
-        }
-  
-        # record AR
-        $amount =
-          $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff,
-                              2);
-  
-        if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
-          $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);
-        }
-  
-        # record payment
-        $form->{"paid_$i"} *= -1;
-  
-        $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);
-  
-        # exchangerate difference
-        $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
-          $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
-  
-        # 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;
-        }
-  
-        $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);
-        }
+
+      next if ($form->{"paid_$i"} == 0);
+
+      my ($accno) = split(/--/, $form->{"AR_paid_$i"});
+      $form->{"datepaid_$i"} = $form->{invdate}
+      unless ($form->{"datepaid_$i"});
+      $form->{datepaid} = $form->{"datepaid_$i"};
+
+      $exchangerate = 0;
+
+      if ($form->{currency} eq $defaultcurrency) {
+        $form->{"exchangerate_$i"} = 1;
+      } else {
+        $exchangerate              = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
+        $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
+      }
+
+      # record AR
+      $amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
+
+      if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
+        $query =
+        qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
+           VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
+                   (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
+        @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, $project_id);
+        do_query($form, $dbh, $query, @values);
+      }
+
+      # record payment
+      $form->{"paid_$i"} *= -1;
+
+      $query =
+      qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, taxkey, project_id)
+         VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?,
+                 (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
+      @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
+                 $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id);
+      do_query($form, $dbh, $query, @values);
+
+      # exchangerate difference
+      $form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
+      $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
+
+      # 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;
+      }
+
+      $diff = 0;
+
+      # update exchange rate
+      if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
+        $form->update_exchangerate($dbh, $form->{currency},
+                                   $form->{"datepaid_$i"},
+                                   $form->{"exchangerate_$i"}, 0);

       }
     }
       }
     }

+
+  } else {                      # if (!$form->{storno})
+    $form->{marge_total} *= -1;
+  }
+
+  if ($payments_only) {
+    $query = qq|UPDATE ar SET paid = ?, datepaid = ? WHERE id = ?|;
+    do_query($form, $dbh, $query,  $form->{paid}, $form->{paid} ? conv_date($form->{datepaid}) : undef, conv_i($form->{id}));
+
+    if (!$provided_dbh) {
+      $dbh->commit();
+      $dbh->disconnect();
+    }
+
+    $main::lxdebug->leave_sub();
+    return;

   }
 
   # record exchange rate differences and gains/losses
   }
 
   # record exchange rate differences and gains/losses


@@ -927,99 +938,72 @@ sub post_invoice {
           ) != 0
         ) {
 
           ) != 0
         ) {
 

-        $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);
+        $query =
+          qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id)
+             VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
+             (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
+        @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, $project_id);
+        do_query($form, $dbh, $query, @values);

       }
     }
   }
 
   $amount = $netamount + $tax;
 
       }
     }
   }
 
   $amount = $netamount + $tax;
 

-  # set values which could be empty to 0
-  $form->{terms}       *= 1;
-  $form->{taxincluded} *= 1;
-  my $datepaid = ($form->{paid})    ? qq|'$form->{datepaid}'| : "NULL";
-  my $duedate  = ($form->{duedate}) ? qq|'$form->{duedate}'|  : "NULL";
-  my $deliverydate =
-    ($form->{deliverydate}) ? qq|'$form->{deliverydate}'| : "NULL";
-
-  # fill in subject if there is none
-  $form->{subject} = qq|$form->{label} $form->{invnumber}|
-    unless $form->{subject};
-
-  # if there is a message stuff it into the intnotes
-  my $cc  = "Cc: $form->{cc}\\r\n"   if $form->{cc};
-  my $bcc = "Bcc: $form->{bcc}\\r\n" if $form->{bcc};
-  my $now = scalar localtime;
-  $form->{intnotes} .= qq|\r
-\r| if $form->{intnotes};
-
-  $form->{intnotes} .= qq|[email]\r
-Date: $now
-To: $form->{email}\r
-$cc${bcc}Subject: $form->{subject}\r
-\r
-Message: $form->{message}\r| if $form->{message};
-

   # save AR record
   # save AR record

-  $query = qq|UPDATE ar set
-              invnumber = '$form->{invnumber}',
-             ordnumber = '$form->{ordnumber}',
-             quonumber = '$form->{quonumber}',
-             cusordnumber = '$form->{cusordnumber}',
-              transdate = '$form->{invdate}',
-              customer_id = $form->{customer_id},
-              amount = $amount,
-              netamount = $netamount,
-              paid = $form->{paid},
-             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},
-              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},
-              storno = '$form->{storno}',
-              cp_id = $form->{contact_id}
-              WHERE id = $form->{id}
-             |;
-  $dbh->do($query) || $form->dberror($query);
+  #erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb

 
 

+  $query = qq|UPDATE ar set
+                invnumber   = ?, ordnumber     = ?, quonumber     = ?, cusordnumber  = ?,
+                transdate   = ?, orddate       = ?, quodate       = ?, customer_id   = ?,
+                amount      = ?, netamount     = ?, paid          = ?, datepaid      = ?,
+                duedate     = ?, deliverydate  = ?, invoice       = ?, shippingpoint = ?,
+                shipvia     = ?, terms         = ?, notes         = ?, intnotes      = ?,
+                curr        = ?, department_id = ?, payment_id    = ?, taxincluded   = ?,
+                type        = ?, language_id   = ?, taxzone_id    = ?, shipto_id     = ?,
+                employee_id = ?, salesman_id   = ?, storno_id     = ?, storno        = ?,
+                cp_id       = ?, marge_total   = ?, marge_percent = ?, 
+                globalproject_id               = ?, delivery_customer_id             = ?,
+                transaction_description        = ?, delivery_vendor_id               = ?,
+               donumber    = ?
+              WHERE id = ?|;
+  @values = (          $form->{"invnumber"},           $form->{"ordnumber"},             $form->{"quonumber"},          $form->{"cusordnumber"},
+             conv_date($form->{"invdate"}),  conv_date($form->{"orddate"}),    conv_date($form->{"quodate"}),    conv_i($form->{"customer_id"}), 
+                       $amount,                        $netamount,                       $form->{"paid"},     conv_date($form->{"datepaid"}), 
+             conv_date($form->{"duedate"}),  conv_date($form->{"deliverydate"}),    '1',                                $form->{"shippingpoint"},
+                       $form->{"shipvia"},      conv_i($form->{"terms"}),                $form->{"notes"},              $form->{"intnotes"},
+                       $form->{"currency"},     conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}),        $form->{"taxincluded"} ? 't' : 'f',
+                       $form->{"type"},         conv_i($form->{"language_id"}),   conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
+                conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}),   conv_i($form->{storno_id}),           $form->{"storno"} ? 't' : 'f', 
+                conv_i($form->{"cp_id"}),            1 * $form->{marge_total} ,      1 * $form->{marge_percent},
+                conv_i($form->{"globalproject_id"}),                              conv_i($form->{"delivery_customer_id"}), 
+                       $form->{transaction_description},                          conv_i($form->{"delivery_vendor_id"}),
+                      $form->{"donumber"}, #das entsprechende feld lieferscheinnummer aus der html-form 12.02.09 jb
+                conv_i($form->{"id"}));
+  do_query($form, $dbh, $query, @values);
+  
+  if($form->{"formname"} eq "credit_note") {
+    for my $i (1 .. $form->{rowcount}) {
+      $query = qq|UPDATE parts SET onhand = onhand - ? WHERE id = ?|;
+      @values = (conv_i($form->{"qty_$i"}), conv_i($form->{"id_$i"}));
+      do_query($form, $dbh, $query, @values);
+    }
+  }
+  

   if ($form->{storno}) {
   if ($form->{storno}) {

-    $query = qq| update ar set paid=paid+amount where id=$form->{storno_id}|;
-    $dbh->do($query) || $form->dberror($query);
-    $query = qq| update ar set storno='$form->{storno}' where id=$form->{storno_id}|;
-    $dbh->do($query) || $form->dberror($query);
-    $query = qq§ update ar set intnotes='Rechnung storniert am $form->{invdate} ' || intnotes where id=$form->{storno_id}§;
-    $dbh->do($query) || $form->dberror($query);
-
-    $query = qq| update ar set paid=amount where id=$form->{id}|;
-    $dbh->do($query) || $form->dberror($query);
+    $query =
+      qq!UPDATE ar SET
+           paid = paid + amount,
+           storno = 't',
+           intnotes = ? || intnotes
+         WHERE id = ?!;
+    do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
+    do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));

   }
 
   }
 

-  $form->{pago_total} = $amount;
-

   # add shipto
   $form->{name} = $form->{customer};
   # add shipto
   $form->{name} = $form->{customer};

-  $form->{name} =~ s/--$form->{customer_id}//;
+  $form->{name} =~ s/--\Q$form->{customer_id}\E//;

 
   if (!$form->{shipto_id}) {
     $form->add_shipto($dbh, $form->{id}, "AR");
 
   if (!$form->{shipto_id}) {
     $form->add_shipto($dbh, $form->{id}, "AR");


@@ -1028,18 +1012,88 @@ Message: $form->{message}\r| if $form->{message};
   # save printed, emailed and queued
   $form->save_status($dbh);
 
   # save printed, emailed and queued
   $form->save_status($dbh);
 

-  if ($form->{webdav}) {
-    &webdav_folder($myconfig, $form);
+  Common::webdav_folder($form) if ($main::webdav);
+
+  # Link this record to the records it was created from.
+  RecordLinks->create_links('dbh'        => $dbh,
+                            'mode'       => 'ids',
+                            'from_table' => 'oe',
+                            'from_ids'   => $form->{convert_from_oe_ids},
+                            'to_table'   => 'ar',
+                            'to_id'      => $form->{id},
+    );
+  delete $form->{convert_from_oe_ids};
+
+  my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
+
+  if (scalar @convert_from_do_ids) {
+    DO->close_orders('dbh' => $dbh,
+                     'ids' => \@convert_from_do_ids);
+
+    RecordLinks->create_links('dbh'        => $dbh,
+                              'mode'       => 'ids',
+                              'from_table' => 'delivery_orders',
+                              'from_ids'   => \@convert_from_do_ids,
+                              'to_table'   => 'ar',
+                              'to_id'      => $form->{id},
+      );

   }
   }

+  delete $form->{convert_from_do_ids};

 
 

-  my $rc = $dbh->commit;
-  $dbh->disconnect;
+  ARAP->close_orders_if_billed('dbh'     => $dbh,
+                               'arap_id' => $form->{id},
+                               'table'   => 'ar',);
+
+  my $rc = 1;
+  if (!$provided_dbh) {
+    $dbh->commit();
+    $dbh->disconnect();
+  }

 
   $main::lxdebug->leave_sub();
 
   return $rc;
 }
 
 
   $main::lxdebug->leave_sub();
 
   return $rc;
 }
 

+sub _delete_payments {
+  $main::lxdebug->enter_sub();
+
+  my ($self, $form, $dbh) = @_;
+
+  my @delete_oids;
+
+  # Delete old payment entries from acc_trans.
+  my $query =
+    qq|SELECT oid
+       FROM acc_trans
+       WHERE (trans_id = ?) AND fx_transaction
+
+       UNION
+
+       SELECT at.oid
+       FROM acc_trans at
+       LEFT JOIN chart c ON (at.chart_id = c.id)
+       WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
+  push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
+
+  $query =
+    qq|SELECT at.oid
+       FROM acc_trans at
+       LEFT JOIN chart c ON (at.chart_id = c.id)
+       WHERE (trans_id = ?)
+         AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
+       ORDER BY at.oid
+       OFFSET 1|;
+  push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
+
+  if (@delete_oids) {
+    $query = qq|DELETE FROM acc_trans WHERE oid IN (| . join(", ", @delete_oids) . qq|)|;
+    do_query($form, $dbh, $query);
+  }
+
+  $main::lxdebug->leave_sub();
+}
+

 sub post_payment {
   $main::lxdebug->enter_sub();
 
 sub post_payment {
   $main::lxdebug->enter_sub();
 


@@ -1048,136 +1102,60 @@ sub post_payment {
   # connect to database, turn off autocommit
   my $dbh = $form->dbconnect_noauto($myconfig);
 
   # connect to database, turn off autocommit
   my $dbh = $form->dbconnect_noauto($myconfig);
 

-  $form->{datepaid} = $form->{invdate};
-
-  # 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->{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"} != 0) {
-      my ($accno) = split /--/, $form->{"AR_paid_$i"};
-      $form->{"datepaid_$i"} = $form->{invdate}
-        unless ($form->{"datepaid_$i"});
-      $form->{datepaid} = $form->{"datepaid_$i"};
+  my (%payments, $old_form, $row, $item, $query, %keep_vars);

 
 

-      $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"});
-      }
+  $old_form = save_form();

 
 

-      # record AR
-      $amount =
-        $form->round_amount($form->{"paid_$i"} * $form->{"exchangerate"},
-                            2);
+  # Delete all entries in acc_trans from prior payments.
+  $self->_delete_payments($form, $dbh);

 
 

+  # Save the new payments the user made before cleaning up $form.
+  map { $payments{$_} = $form->{$_} } grep m/^datepaid_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^AR_paid_\d+$|^paidaccounts$/, keys %{ $form };

 
 

-      $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);
+  # Clean up $form so that old content won't tamper the results.
+  %keep_vars = map { $_, 1 } qw(login password id);
+  map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };

 
 

-      $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);
+  # Retrieve the invoice from the database.
+  $self->retrieve_invoice($myconfig, $form);

 
 

+  # Set up the content of $form in the way that IS::post_invoice() expects.
+  $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});

 
 

-      # record payment
-      $form->{"paid_$i"} *= -1;
+  for $row (1 .. scalar @{ $form->{invoice_details} }) {
+    $item = $form->{invoice_details}->[$row - 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);
+    map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);

 
 

-      $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);
+    map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
+  }

 
 

+  $form->{rowcount} = scalar @{ $form->{invoice_details} };

 
 

-      # 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;
-      }
+  delete @{$form}{qw(invoice_details paidaccounts storno paid)};

 
 

-      $diff = 0;
+  # Restore the payment options from the user input.
+  map { $form->{$_} = $payments{$_} } keys %payments;

 
 

-      # update exchange rate
-      if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
-        $form->update_exchangerate($dbh, $form->{currency},
-                                   $form->{"datepaid_$i"},
-                                   $form->{"exchangerate_$i"}, 0);
-      }
-    }
-  }
+  # Get the AR accno (which is normally done by Form::create_links()).
+  $query =
+    qq|SELECT c.accno
+       FROM acc_trans at
+       LEFT JOIN chart c ON (at.chart_id = c.id)
+       WHERE (trans_id = ?)
+         AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
+       ORDER BY at.oid
+       LIMIT 1|;

 
 

-  # 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);
-      }
-    }
-  }
-  my $datepaid = ($form->{paid})    ? qq|'$form->{datepaid}'| : "NULL";
+  ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));

 
 

-  # save AR record
-  my $query = qq|UPDATE ar set
-              paid = $form->{paid},
-             datepaid = $datepaid
-              WHERE id=$form->{id}|;
+  # Post the new payments.
+  $self->post_invoice($myconfig, $form, $dbh, 1);

 
 

-  $dbh->do($query) || $form->dberror($query);
+  restore_form($old_form);

 
 

-  my $rc = $dbh->commit;
-  $dbh->disconnect;
+  my $rc = $dbh->commit();
+  $dbh->disconnect();

 
   $main::lxdebug->leave_sub();
 
 
   $main::lxdebug->leave_sub();
 


@@ -1189,25 +1167,21 @@ sub process_assembly {
 
   my ($dbh, $form, $id, $totalqty) = @_;
 
 
   my ($dbh, $form, $id, $totalqty) = @_;
 

-  my $query = qq|SELECT a.parts_id, a.qty, p.assembly,
-                 p.partnumber, p.description, p.unit,
-                 p.inventory_accno_id, p.income_accno_id,
-                p.expense_accno_id
-                 FROM assembly a
-                JOIN parts p ON (a.parts_id = p.id)
-                WHERE a.id = $id|;
-  my $sth = $dbh->prepare($query);
-  $sth->execute || $form->dberror($query);
+  my $query =
+    qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
+         p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
+       FROM assembly a
+       JOIN parts p ON (a.parts_id = p.id)
+       WHERE (a.id = ?)|;
+  my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));

 
 

-  while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
+  while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {

 
     my $allocated = 0;
 
     $ref->{inventory_accno_id} *= 1;
     $ref->{expense_accno_id}   *= 1;
 
 
     my $allocated = 0;
 
     $ref->{inventory_accno_id} *= 1;
     $ref->{expense_accno_id}   *= 1;
 

-    map { $ref->{$_} =~ s/\'/\'\'/g } (qw(partnumber description unit));
-

     # multiply by number of assemblies
     $ref->{qty} *= $totalqty;
 
     # multiply by number of assemblies
     $ref->{qty} *= $totalqty;
 


@@ -1221,13 +1195,11 @@ sub process_assembly {
     }
 
     # save detail record for individual assembly item in invoice table
     }
 
     # save detail record for individual assembly item in invoice table

-    $query = qq|INSERT INTO invoice (trans_id, description, parts_id, qty,
-                sellprice, fxsellprice, allocated, assemblyitem, unit)
-               VALUES
-               ($form->{id}, '$ref->{description}',
-               $ref->{parts_id}, $ref->{qty}, 0, 0, $allocated, 't',
-               '$ref->{unit}')|;
-    $dbh->do($query) || $form->dberror($query);
+    $query =
+      qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
+         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
+    my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
+    do_query($form, $dbh, $query, @values);

 
   }
 
 
   }
 


@@ -1239,49 +1211,52 @@ sub process_assembly {
 sub cogs {
   $main::lxdebug->enter_sub();
 
 sub cogs {
   $main::lxdebug->enter_sub();
 

-  my ($dbh, $form, $id, $totalqty, $basefactor) = @_;
-
-  my $query = qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice,
-                   (SELECT c.accno FROM chart c
-                   WHERE p.inventory_accno_id = c.id) AS inventory_accno,
-                  (SELECT c.accno FROM chart c
-                   WHERE p.expense_accno_id = c.id) AS expense_accno
-                 FROM invoice i, parts p
-                 WHERE i.parts_id = p.id
-                 AND i.parts_id = $id
-                 AND (i.base_qty + i.allocated) < 0
-                 ORDER BY trans_id|;
-  my $sth = $dbh->prepare($query);
-  $sth->execute || $form->dberror($query);
+  my ($dbh, $form, $id, $totalqty, $basefactor, $row) = @_;
+  $form->{taxzone_id} *=1;
+  my $transdate  = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
+  my $taxzone_id = $form->{"taxzone_id"} * 1;
+  my $query =
+    qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice,
+         c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
+         c2.accno AS    income_accno, c2.new_chart_id AS    income_new_chart, date($transdate) - c2.valid_from AS    income_valid,
+         c3.accno AS   expense_accno, c3.new_chart_id AS   expense_new_chart, date($transdate) - c3.valid_from AS   expense_valid
+       FROM invoice i, parts p
+       LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
+       LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
+       LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
+       WHERE (i.parts_id = p.id)
+         AND (i.parts_id = ?)
+         AND ((i.base_qty + i.allocated) < 0)
+       ORDER BY trans_id|;
+  my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));

 
   my $allocated = 0;
   my $qty;
 
 
   my $allocated = 0;
   my $qty;
 

-  while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
+  while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {

     if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
       $qty = $totalqty;
     }
 
     if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
       $qty = $totalqty;
     }
 

-    $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|,
-                          $qty);
+    $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);

 
     # total expenses and inventory
     # sellprice is the cost of the item
 
     # total expenses and inventory
     # sellprice is the cost of the item

-    $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / $basefactor, 2);
-
-    if (!$eur) {
+    my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / $basefactor, 2);

 
 

+    if (!$main::eur) {
+      $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};

       # add to expense
       # add to expense

-      $form->{amount}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
+      $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;

       $form->{expense_inventory} .= " " . $ref->{expense_accno};
       $form->{expense_inventory} .= " " . $ref->{expense_accno};

-
+      $ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};

       # deduct inventory
       # deduct inventory

-      $form->{amount}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
+      $form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;

       $form->{expense_inventory} .= " " . $ref->{inventory_accno};
     }
 
     # add allocated
       $form->{expense_inventory} .= " " . $ref->{inventory_accno};
     }
 
     # add allocated

-    $allocated += -$qty;
+    $allocated -= $qty;

 
     last if (($totalqty -= $qty) <= 0);
   }
 
     last if (($totalqty -= $qty) <= 0);
   }


@@ -1299,77 +1274,54 @@ sub reverse_invoice {
   my ($dbh, $form) = @_;
 
   # reverse inventory items
   my ($dbh, $form) = @_;
 
   # reverse inventory items

-  my $query = qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly,
-                p.inventory_accno_id
-                 FROM invoice i
-                JOIN parts p ON (i.parts_id = p.id)
-                WHERE i.trans_id = $form->{id}|;
-  my $sth = $dbh->prepare($query);
-  $sth->execute || $form->dberror($query);
+  my $query =
+    qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
+       FROM invoice i
+       JOIN parts p ON (i.parts_id = p.id)
+       WHERE i.trans_id = ?|;
+  my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));

 
 

-  while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
-
-    if ($ref->{inventory_accno_id} || $ref->{assembly}) {
-
-      # if the invoice item is not an assemblyitem adjust parts onhand
-      if (!$ref->{assemblyitem}) {
-
-        # adjust onhand in parts table
-        $form->update_balance($dbh, "parts", "onhand",
-                              qq|id = $ref->{parts_id}|,
-                              $ref->{qty});
-      }
-
-      # loop if it is an assembly
-      next if ($ref->{assembly});
+  while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {

 
 

+    if ($ref->{inventory_accno_id}) {

       # de-allocated purchases
       # de-allocated purchases

-      $query = qq|SELECT i.id, i.trans_id, i.allocated
-                  FROM invoice i
-                 WHERE i.parts_id = $ref->{parts_id}
-                 AND i.allocated > 0
-                 ORDER BY i.trans_id DESC|;
-      my $sth = $dbh->prepare($query);
-      $sth->execute || $form->dberror($query);
-
-      while (my $inhref = $sth->fetchrow_hashref(NAME_lc)) {
-        $qty = $ref->{qty};
+      $query =
+        qq|SELECT i.id, i.trans_id, i.allocated
+           FROM invoice i
+           WHERE (i.parts_id = ?) AND (i.allocated > 0)
+           ORDER BY i.trans_id DESC|;
+      my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
+
+      while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
+        my $qty = $ref->{qty};

         if (($ref->{qty} - $inhref->{allocated}) > 0) {
           $qty = $inhref->{allocated};
         }
 
         # update invoice
         if (($ref->{qty} - $inhref->{allocated}) > 0) {
           $qty = $inhref->{allocated};
         }
 
         # update invoice

-        $form->update_balance($dbh, "invoice", "allocated",
-                              qq|id = $inhref->{id}|,
-                              $qty * -1);
+        $form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);

 
         last if (($ref->{qty} -= $qty) <= 0);
       }
 
         last if (($ref->{qty} -= $qty) <= 0);
       }

-      $sth->finish;
+      $sth2->finish;

     }
   }
 
   $sth->finish;
 
   # delete acc_trans
     }
   }
 
   $sth->finish;
 
   # delete acc_trans

-  $query = qq|DELETE FROM acc_trans
-              WHERE trans_id = $form->{id}|;
-  $dbh->do($query) || $form->dberror($query);
-
-  # delete invoice entries
-  $query = qq|DELETE FROM invoice
-              WHERE trans_id = $form->{id}|;
-  $dbh->do($query) || $form->dberror($query);
+  my @values = (conv_i($form->{id}));
+  do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
+  do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);

 
   if ($form->{lizenzen}) {
 
   if ($form->{lizenzen}) {

-    $query = qq|DELETE FROM licenseinvoice
-              WHERE trans_id in (SELECT id FROM invoice WHERE trans_id = $form->{id})|;
-    $dbh->do($query) || $form->dberror($query);
+    $query =
+      qq|DELETE FROM licenseinvoice
+         WHERE trans_id in (SELECT id FROM invoice WHERE trans_id = ?)|;
+    do_query($form, $dbh, $query, @values);

   }
 
   }
 

-  $query = qq|DELETE FROM shipto
-              WHERE trans_id = $form->{id} AND module = 'AR'|;
-  $dbh->do($query) || $form->dberror($query);
+  do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);

 
   $main::lxdebug->leave_sub();
 }
 
   $main::lxdebug->leave_sub();
 }


@@ -1384,37 +1336,22 @@ sub delete_invoice {
 
   &reverse_invoice($dbh, $form);
 
 
   &reverse_invoice($dbh, $form);
 

+  my @values = (conv_i($form->{id}));
+

   # delete AR record
   # delete AR record

-  my $query = qq|DELETE FROM ar
-                 WHERE id = $form->{id}|;
-  $dbh->do($query) || $form->dberror($query);
+  do_query($form, $dbh, qq|DELETE FROM ar WHERE id = ?|, @values);

 
   # delete spool files
 
   # delete spool files

-  $query = qq|SELECT s.spoolfile FROM status s
-              WHERE s.trans_id = $form->{id}|;
-  my $sth = $dbh->prepare($query);
-  $sth->execute || $self->dberror($query);
-
-  my $spoolfile;
-  my @spoolfiles = ();
-
-  while (($spoolfile) = $sth->fetchrow_array) {
-    push @spoolfiles, $spoolfile;
-  }
-  $sth->finish;
+  my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);

 
   # delete status entries
 
   # delete status entries

-  $query = qq|DELETE FROM status
-              WHERE trans_id = $form->{id}|;
-  $dbh->do($query) || $form->dberror($query);
+  do_query($form, $dbh, qq|DELETE FROM status WHERE trans_id = ?|, @values);

 
   my $rc = $dbh->commit;
   $dbh->disconnect;
 
   if ($rc) {
 
   my $rc = $dbh->commit;
   $dbh->disconnect;
 
   if ($rc) {

-    foreach $spoolfile (@spoolfiles) {
-      unlink "$spool/$spoolfile" if $spoolfile;
-    }
+    map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;

   }
 
   $main::lxdebug->leave_sub();
   }
 
   $main::lxdebug->leave_sub();


@@ -1430,191 +1367,143 @@ sub retrieve_invoice {
   # connect to database
   my $dbh = $form->dbconnect_noauto($myconfig);
 
   # connect to database
   my $dbh = $form->dbconnect_noauto($myconfig);
 

-  my $query;
+  my ($sth, $ref, $query);

 
 

-  if ($form->{id}) {
+  my $query_transdate = ", current_date AS invdate" if !$form->{id};

 
 

-    # get default accounts and last invoice number
-    $query = qq|SELECT (SELECT c.accno FROM chart c
-                        WHERE d.inventory_accno_id = c.id) AS inventory_accno,
-                      (SELECT c.accno FROM chart c
-                       WHERE d.income_accno_id = c.id) AS income_accno,
-                      (SELECT c.accno FROM chart c
-                       WHERE d.expense_accno_id = c.id) AS expense_accno,
-                      (SELECT c.accno FROM chart c
-                       WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
-                      (SELECT c.accno FROM chart c
-                       WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
-                d.curr AS currencies
-               FROM defaults d|;
-  } else {
-    $query = qq|SELECT (SELECT c.accno FROM chart c
-                        WHERE d.inventory_accno_id = c.id) AS inventory_accno,
-                      (SELECT c.accno FROM chart c
-                       WHERE d.income_accno_id = c.id) AS income_accno,
-                      (SELECT c.accno FROM chart c
-                       WHERE d.expense_accno_id = c.id) AS expense_accno,
-                      (SELECT c.accno FROM chart c
-                       WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
-                      (SELECT c.accno FROM chart c
-                       WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
-                d.curr AS currencies, current_date AS invdate
-                FROM defaults d|;
-  }
-  my $sth = $dbh->prepare($query);
-  $sth->execute || $form->dberror($query);
+  $query =
+    qq|SELECT
+         (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
+         (SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id)    AS income_accno,
+         (SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id)   AS expense_accno,
+         (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id)    AS fxgain_accno,
+         (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id)    AS fxloss_accno,
+         d.curr AS currencies
+         ${query_transdate}
+       FROM defaults d|;

 
 

-  my $ref = $sth->fetchrow_hashref(NAME_lc);
-  map { $form->{$_} = $ref->{$_} } keys %$ref;
-  $sth->finish;
+  $ref = selectfirst_hashref_query($form, $dbh, $query);
+  map { $form->{$_} = $ref->{$_} } keys %{ $ref };

 
   if ($form->{id}) {
 
   if ($form->{id}) {

+    my $id = conv_i($form->{id});

 
     # retrieve invoice
 
     # retrieve invoice

-    $query = qq|SELECT a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
-                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,
-               a.employee_id, e.name AS employee, a.payment_id, a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type
-               FROM ar a
-               LEFT JOIN employee e ON (e.id = a.employee_id)
-               WHERE a.id = $form->{id}|;
-    $sth = $dbh->prepare($query);
-    $sth->execute || $form->dberror($query);
+    #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb

 
 

-    $ref = $sth->fetchrow_hashref(NAME_lc);
-    map { $form->{$_} = $ref->{$_} } keys %$ref;
-    $sth->finish;
+    $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,
+           a.employee_id, a.salesman_id, a.payment_id,
+           a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
+           a.transaction_description,
+           a.marge_total, a.marge_percent,
+           e.name AS employee, a.donumber
+         FROM ar a
+         LEFT JOIN employee e ON (e.id = a.employee_id)
+         WHERE a.id = ?|;
+    $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
+    map { $form->{$_} = $ref->{$_} } keys %{ $ref };
+
+
+    $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");

 
 

-    $form->{exchangerate} =
-      $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate},
-                              "buy");

     # get shipto
     # get shipto

-    $query = qq|SELECT s.* FROM shipto s
-                WHERE s.trans_id = $form->{id} AND s.module = 'AR'|;
-    $sth = $dbh->prepare($query);
-    $sth->execute || $form->dberror($query);
-
-    $ref = $sth->fetchrow_hashref(NAME_lc);
-    delete($ref->{id});
-    map { $form->{$_} = $ref->{$_} } keys %$ref;
-    $sth->finish;
-
-   if ($form->{delivery_customer_id}) {
-      $query = qq|SELECT name FROM customer WHERE id=$form->{delivery_customer_id}|;
-      $sth = $dbh->prepare($query);
-      $sth->execute || $form->dberror($query);
-      ($form->{delivery_customer_string}) = $sth->fetchrow_array();
-      $sth->finish;
-    }
-
-    if ($form->{delivery_vendor_id}) {
-      $query = qq|SELECT name FROM customer WHERE id=$form->{delivery_vendor_id}|;
-      $sth = $dbh->prepare($query);
-      $sth->execute || $form->dberror($query);
-      ($form->{delivery_vendor_string}) = $sth->fetchrow_array();
-      $sth->finish;
+    $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
+    $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
+    delete $ref->{id};
+    map { $form->{$_} = $ref->{$_} } keys %{ $ref };
+
+    foreach my $vc (qw(customer vendor)) {
+      next if !$form->{"delivery_${vc}_id"};
+      ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);

     }
 
     # get printed, emailed
     }
 
     # get printed, emailed

-    $query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname
-                FROM status s
-                WHERE s.trans_id = $form->{id}|;
-    $sth = $dbh->prepare($query);
-    $sth->execute || $form->dberror($query);
+    $query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
+    $sth = prepare_execute_query($form, $dbh, $query, $id);

 
 

-    while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+    while ($ref = $sth->fetchrow_hashref('NAME_lc')) {

       $form->{printed} .= "$ref->{formname} " if $ref->{printed};
       $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
       $form->{printed} .= "$ref->{formname} " if $ref->{printed};
       $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};

-      $form->{queued} .= "$ref->{formname} $ref->{spoolfile} "
-        if $ref->{spoolfile};
+      $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};

     }
     $sth->finish;
     map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
 
     }
     $sth->finish;
     map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
 

-    my $transdate =
-      $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
-      $form->{invdate} ? $dbh->quote($form->{invdate}) :
-      "current_date";
+    my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
+                  : $form->{invdate}      ? $dbh->quote($form->{invdate})
+                  :                         "current_date";
+     
+
+    my $taxzone_id = $form->{taxzone_id} *= 1;
+    $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);

 
 

-    if (!$form->{taxzone_id}) {
-      $form->{taxzone_id} = 0;
-    }

     # retrieve individual items
     # retrieve individual items

-    $query = qq|SELECT  
-                c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
-               c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate)  - c2.valid_from as income_valid,
-               c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
-                i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice,
-               i.discount, i.parts_id AS id, i.unit, i.deliverydate,
-               i.project_id, pr.projectnumber, i.serialnumber,
-               p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, i.id AS invoice_pos,
-               pg.partsgroup, i.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=i.pricegroup_id) as pricegroup,
-               i.ordnumber, i.transdate, i.cusordnumber, p.formel, i.subtotal
-               FROM invoice i
-               JOIN parts p ON (i.parts_id = p.id)
-               LEFT JOIN project pr ON (i.project_id = pr.id)
-               LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
-               LEFT JOIN chart c1 ON ((select inventory_accno_id from buchungsgruppen where id=p.buchungsgruppen_id) = c1.id)
-               LEFT JOIN chart c2 ON ((select income_accno_id_$form->{taxzone_id} from buchungsgruppen where id=p.buchungsgruppen_id) = c2.id)
-               LEFT JOIN chart c3 ON ((select expense_accno_id_$form->{taxzone_id} from buchungsgruppen where id=p.buchungsgruppen_id) = c3.id)
-               WHERE i.trans_id = $form->{id}
-               AND NOT i.assemblyitem = '1'
-               ORDER BY i.id|;
-    $sth = $dbh->prepare($query);
+    $query =
+      qq|SELECT
+           c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
+           c2.accno AS income_accno,    c2.new_chart_id AS income_new_chart,    date($transdate) - c2.valid_from as income_valid,
+           c3.accno AS expense_accno,   c3.new_chart_id AS expense_new_chart,   date($transdate) - c3.valid_from AS expense_valid,

 
 

-    $sth->execute || $form->dberror($query);
-    while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
-      if (!$ref->{"part_inventory_accno_id"}) {
-        map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
-      }
-      delete($ref->{"part_inventory_accno_id"});
+           i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
+           i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
+           i.price_factor_id, i.price_factor, i.marge_price_factor,
+           p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel,
+           pr.projectnumber, pg.partsgroup, prg.pricegroup

 
 

-    while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >=0)) {
-      my $query = qq| SELECT accno AS inventory_accno, new_chart_id AS inventory_new_chart, date($transdate) - valid_from AS inventory_valid FROM chart WHERE id = $ref->{inventory_new_chart}|;
-      my $stw = $dbh->prepare($query);
-      $stw->execute || $form->dberror($query);
-      ($ref->{inventory_accno}, $ref->{inventory_new_chart}, $ref->{inventory_valid}) = $stw->fetchrow_array;
-      $stw->finish;
-    }
+         FROM invoice i
+         LEFT JOIN parts p ON (i.parts_id = p.id)
+         LEFT JOIN project pr ON (i.project_id = pr.id)
+         LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
+         LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)

 
 

-    while ($ref->{income_new_chart} && ($ref->{income_valid} >=0)) {
-      my $query = qq| SELECT accno AS income_accno, new_chart_id AS income_new_chart, date($transdate) - valid_from AS income_valid FROM chart WHERE id = $ref->{income_new_chart}|;
-      my $stw = $dbh->prepare($query);
-      $stw->execute || $form->dberror($query);
-      ($ref->{income_accno}, $ref->{income_new_chart}, $ref->{income_valid}) = $stw->fetchrow_array;
-      $stw->finish;
-    }
+         LEFT JOIN chart c1 ON ((SELECT inventory_accno_id             FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
+         LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id}  FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
+         LEFT JOIN chart c3 ON ((SELECT expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)

 
 

-    while ($ref->{expense_new_chart} && ($ref->{expense_valid} >=0)) {
-      my $query = qq| SELECT accno AS expense_accno, new_chart_id AS expense_new_chart, date($transdate) - valid_from AS expense_valid FROM chart WHERE id = $ref->{expense_new_chart}|;
-      my $stw = $dbh->prepare($query);
-      $stw->execute || $form->dberror($query);
-      ($ref->{expense_accno}, $ref->{expense_new_chart}, $ref->{expense_valid}) = $stw->fetchrow_array;
-      $stw->finish;
-    }
+         WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;
+
+    $sth = prepare_execute_query($form, $dbh, $query, $id);
+
+    while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
+      map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
+      delete($ref->{"part_inventory_accno_id"});
+
+      foreach my $type (qw(inventory income expense)) {
+        while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
+          my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
+          @$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
+        }
+      }

 
       # get tax rates and description
 
       # get tax rates and description

-      $accno_id =
-        ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
-    $query = qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
-             FROM tax t LEFT JOIN chart c on (c.id=t.chart_id)
-             WHERE t.id in (SELECT tk.tax_id from taxkeys tk where tk.chart_id = (SELECT id from chart WHERE accno='$accno_id') AND startdate<=$transdate ORDER BY startdate desc LIMIT 1)
-             ORDER BY c.accno|;
-      $stw = $dbh->prepare($query);
-      $stw->execute || $form->dberror($query);
+      my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
+      $query =
+        qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
+           LEFT JOIN chart c ON (c.id = t.chart_id)
+           WHERE t.id IN
+             (SELECT tk.tax_id FROM taxkeys tk
+              WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) 
+                AND startdate <= date($transdate)
+              ORDER BY startdate DESC LIMIT 1)
+           ORDER BY c.accno|;
+      my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);

       $ref->{taxaccounts} = "";
       my $i=0;
       $ref->{taxaccounts} = "";
       my $i=0;

-      while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
+      while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {

 
 

-        #    if ($customertax{$ref->{accno}}) {

         if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
           $i++;
           $ptr->{accno} = $i;
         }
         $ref->{taxaccounts} .= "$ptr->{accno} ";
 
         if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
           $i++;
           $ptr->{accno} = $i;
         }
         $ref->{taxaccounts} .= "$ptr->{accno} ";
 

-        if (!($form->{taxaccounts} =~ /$ptr->{accno}/)) {
+        if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {

           $form->{"$ptr->{accno}_rate"}        = $ptr->{rate};
           $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
           $form->{"$ptr->{accno}_taxnumber"}   = $ptr->{taxnumber};
           $form->{"$ptr->{accno}_rate"}        = $ptr->{rate};
           $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
           $form->{"$ptr->{accno}_taxnumber"}   = $ptr->{taxnumber};


@@ -1624,29 +1513,20 @@ sub retrieve_invoice {
       }
 
       if ($form->{lizenzen}) {
       }
 
       if ($form->{lizenzen}) {

-        $query = qq|SELECT l.licensenumber, l.id AS licenseid
-                FROM license l, licenseinvoice li
-                WHERE l.id = li.license_id AND li.trans_id = $ref->{invoice_pos}|;
-        $stg = $dbh->prepare($query);
-        $stg->execute || $form->dberror($query);
-        ($licensenumber, $licenseid) = $stg->fetchrow_array();
-        $ref->{lizenzen} =
-          "<option value=\"$licenseid\">$licensenumber</option>";
-        $stg->finish();
-      }
-      if ($form->{type} eq "credit_note") {
-        $ref->{qty} *= -1;
+        $query = qq|SELECT l.licensenumber, l.id AS licenseid FROM license l, licenseinvoice li WHERE l.id = li.license_id AND li.trans_id = ?|;
+        my ($licensenumber, $licenseid) = selectrow_query($form, $dbh, $query, conv_i($ref->{invoice_pos}));
+        $ref->{lizenzen} = "<option value=\"$licenseid\">$licensenumber</option>";

       }
 
       }
 

+      $ref->{qty} *= -1 if $form->{type} eq "credit_note";
+

       chop $ref->{taxaccounts};
       push @{ $form->{invoice_details} }, $ref;
       $stw->finish;
     }
     $sth->finish;
 
       chop $ref->{taxaccounts};
       push @{ $form->{invoice_details} }, $ref;
       $stw->finish;
     }
     $sth->finish;
 

-    if ($form->{webdav}) {
-      &webdav_folder($myconfig, $form);
-    }
+    Common::webdav_folder($form) if ($main::webdav);

   }
 
   my $rc = $dbh->commit;
   }
 
   my $rc = $dbh->commit;


@@ -1668,90 +1548,74 @@ sub get_customer {
   my $dateformat = $myconfig->{dateformat};
   $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
 
   my $dateformat = $myconfig->{dateformat};
   $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
 

-  my $duedate =
-    ($form->{invdate})
-    ? "to_date('$form->{invdate}', '$dateformat')"
-    : "current_date";
-
-  $form->{customer_id} *= 1;
+  my (@values, $duedate, $ref, $query);

 
 

-  # 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,
-                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);
-
-  map { $form->{$_} = $ref->{$_} } keys %$ref;
-  $sth->finish;
+  if ($form->{invdate}) {
+    $duedate = "to_date(?, '$dateformat')";
+    push @values, $form->{invdate};
+  } else {
+    $duedate = "current_date";
+  }

 
 

-  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);
+  my $cid = conv_i($form->{customer_id});
+  my $payment_id;

 
 

-  $sth->execute || $form->dberror($query);
-  
-  $ref = $sth->fetchrow_hashref(NAME_lc);
+  if ($form->{payment_id}) {
+    $payment_id = "(pt.id = ?) OR";
+    push @values, conv_i($form->{payment_id});
+  }

 
 

+  # get customer
+  $query =
+    qq|SELECT
+         c.id AS customer_id, c.name AS customer, c.discount, c.creditlimit, c.terms,
+         c.email, c.cc, c.bcc, c.language_id, c.payment_id,
+         c.street, c.zipcode, c.city, c.country,
+         c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id,
+         $duedate + COALESCE(pt.terms_netto, 0) AS duedate,
+         b.discount AS tradediscount, b.description AS business
+       FROM customer c
+       LEFT JOIN business b ON (b.id = c.business_id)
+       LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
+       WHERE c.id = ?|;
+  push @values, $cid;
+  $ref = selectfirst_hashref_query($form, $dbh, $query, @values);

   map { $form->{$_} = $ref->{$_} } keys %$ref;
   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);

 
 

+  $query =
+    qq|SELECT sum(amount - paid) AS dunning_amount
+       FROM ar
+       WHERE (paid < amount)
+         AND (customer_id = ?)
+         AND (dunning_config_id IS NOT NULL)|;
+  $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);

   map { $form->{$_} = $ref->{$_} } keys %$ref;
   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 $stw = $dbh->prepare($query);
-    $stw->execute || $form->dberror($query);
-    ($old_ranking, $new_ranking) = $stw->fetchrow_array;
-    $stw->finish;
-    if ($new_ranking > $old_ranking) {
-      $form->{payment_id} =$form->{customer_payment_id};
-    }
-  }
-  if ($form->{payment_id} eq "") {
-    $form->{payment_id} =$form->{customer_payment_id};
-  }
+  $query =
+    qq|SELECT dnn.dunning_description AS max_dunning_level
+       FROM dunning_config dnn
+       WHERE id IN (SELECT dunning_config_id
+                    FROM ar
+                    WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
+       ORDER BY dunning_level DESC LIMIT 1|;
+  $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
+  map { $form->{$_} = $ref->{$_} } keys %$ref;

 
   $form->{creditremaining} = $form->{creditlimit};
 
   $form->{creditremaining} = $form->{creditlimit};

-  $query = qq|SELECT SUM(a.amount - a.paid)
-             FROM ar a
-             WHERE a.customer_id = $form->{customer_id}|;
-  $sth = $dbh->prepare($query);
-  $sth->execute || $form->dberror($query);
-
-  ($form->{creditremaining}) -= $sth->fetchrow_array;
-
-  $sth->finish;
-
-  $query = qq|SELECT o.amount,
-                (SELECT e.buy FROM exchangerate e
-                WHERE e.curr = o.curr
-                AND e.transdate = o.transdate)
-             FROM oe o
-             WHERE o.customer_id = $form->{customer_id}
-             AND o.quotation = '0'
-             AND o.closed = '0'|;
-  $sth = $dbh->prepare($query);
-  $sth->execute || $form->dberror($query);
+  $query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
+  my ($value) = selectrow_query($form, $dbh, $query, $cid);
+  $form->{creditremaining} -= $value;
+
+  $query =
+    qq|SELECT o.amount,
+         (SELECT e.buy FROM exchangerate e
+          WHERE e.curr = o.curr
+            AND e.transdate = o.transdate)
+       FROM oe o
+       WHERE o.customer_id = ?
+         AND o.quotation = '0'
+         AND o.closed = '0'|;
+  my $sth = prepare_execute_query($form, $dbh, $query, $cid);

 
   while (my ($amount, $exch) = $sth->fetchrow_array) {
     $exch = 1 unless $exch;
 
   while (my ($amount, $exch) = $sth->fetchrow_array) {
     $exch = 1 unless $exch;


@@ -1759,79 +1623,53 @@ sub get_customer {
   }
   $sth->finish;
 
   }
   $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->{$_} }
   # get shipto if we did not converted an order or invoice
   if (!$form->{shipto}) {
     map { delete $form->{$_} }

-      qw(shiptoname shiptodepartment_1 shiptodepartment_2 shiptostreet shiptozipcode shiptocity shiptocountry shiptocontact shiptophone shiptofax shiptoemail);
+      qw(shiptoname shiptodepartment_1 shiptodepartment_2
+         shiptostreet shiptozipcode shiptocity shiptocountry
+         shiptocontact shiptophone shiptofax shiptoemail);

 
 

-    $query = qq|SELECT s.* FROM shipto s
-                WHERE s.trans_id = $form->{customer_id} AND s.module = 'CT'|;
-    $sth = $dbh->prepare($query);
-    $sth->execute || $form->dberror($query);
-
-    $ref = $sth->fetchrow_hashref(NAME_lc);
-    undef($ref->{id});
+    $query = qq|SELECT * FROM shipto WHERE trans_id = ? AND module = 'CT'|;
+    $ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
+    delete $ref->{id};

     map { $form->{$_} = $ref->{$_} } keys %$ref;
     map { $form->{$_} = $ref->{$_} } keys %$ref;

-    $sth->finish;
-  }
-
-  # get taxes we charge for this customer
-  $query = qq|SELECT c.accno
-              FROM chart c
-             JOIN customertax ct ON (ct.chart_id = c.id)
-             WHERE ct.customer_id = $form->{customer_id}|;
-  $sth = $dbh->prepare($query);
-  $sth->execute || $form->dberror($query);
-
-  my $customertax = ();
-  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
-    $customertax{ $ref->{accno} } = 1;
-  }
-  $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)/) {
 
   # 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
-                FROM chart c
-               JOIN acc_trans ac ON (ac.chart_id = c.id)
-               JOIN ar a ON (a.id = ac.trans_id)
-               WHERE a.customer_id = $form->{customer_id}
-               AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
-               AND a.id IN (SELECT max(a2.id) FROM ar a2
-                            WHERE a2.customer_id = $form->{customer_id})|;
-    $sth = $dbh->prepare($query);
-    $sth->execute || $form->dberror($query);
+    $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)
+         WHERE a.customer_id = ?
+           AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
+           AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
+    $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);

 
     my $i = 0;
 
     my $i = 0;

-    while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+    while ($ref = $sth->fetchrow_hashref('NAME_lc')) {

       if ($ref->{category} eq 'I') {
         $i++;
         $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
       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
+               LEFT JOIN tax t ON tk.tax_id = t.id
+               WHERE (tk.chart_id = ?) AND (startdate <= date(?))
+               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') {
       }
       if ($ref->{category} eq 'A') {

-        $form->{ARselected} = $form->{AR_1} =
-          "$ref->{accno}--$ref->{description}";
+        $form->{ARselected} = $form->{AR_1} = $ref->{accno};

       }
     }
     $sth->finish;
       }
     }
     $sth->finish;


@@ -1853,57 +1691,82 @@ sub retrieve_item {
 
   my $i = $form->{rowcount};
 
 
   my $i = $form->{rowcount};
 

-  my $where = "NOT p.obsolete = '1'";
+  my $where = qq|NOT p.obsolete = '1'|;
+  my @values;

 
 

-  if ($form->{"partnumber_$i"}) {
-    my $partnumber = $form->like(lc $form->{"partnumber_$i"});
-    $where .= " AND lower(p.partnumber) LIKE '$partnumber'";
-  }
-  if ($form->{"description_$i"}) {
-    my $description = $form->like(lc $form->{"description_$i"});
-    $where .= " AND lower(p.description) LIKE '$description'";
-  }
-
-  if ($form->{"partsgroup_$i"}) {
-    my $partsgroup = $form->like(lc $form->{"partsgroup_$i"});
-    $where .= " AND lower(pg.partsgroup) LIKE '$partsgroup'";
+  foreach my $column (qw(p.partnumber p.description pgpartsgroup)) {
+    my ($table, $field) = split m/\./, $column;
+    next if !$form->{"${field}_${i}"};
+    $where .= qq| AND lower(${column}) ILIKE ?|;
+    push @values, '%' . $form->{"${field}_${i}"} . '%';

   }
 
   if ($form->{"description_$i"}) {
   }
 
   if ($form->{"description_$i"}) {

-    $where .= " ORDER BY p.description";
+    $where .= qq| ORDER BY p.description|;

   } else {
   } else {

-    $where .= " ORDER BY p.partnumber";
+    $where .= qq| ORDER BY p.partnumber|;

   }
 
   my $transdate;
   if ($form->{type} eq "invoice") {
     $transdate =
       $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
   }
 
   my $transdate;
   if ($form->{type} eq "invoice") {
     $transdate =
       $form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :

-      $form->{invdate} ? $dbh->quote($form->{invdate}) :
-      "current_date";
+      $form->{invdate}      ? $dbh->quote($form->{invdate}) :
+                              "current_date";

   } else {
     $transdate =
   } else {
     $transdate =

-      $form->{transdate} ? $dbh->quote($form->{transdate}) :
-      "current_date";
+      $form->{transdate}    ? $dbh->quote($form->{transdate}) :
+                              "current_date";

   }
 
   }
 

-  my $query = qq|SELECT p.id, p.partnumber, p.description, p.sellprice,
-                        p.listprice, p.inventory_accno_id,
-                        c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid,
-                       c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate)  - c2.valid_from as income_valid,
-                       c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid,
-                p.unit, p.assembly, p.bin, p.onhand, p.notes AS partnotes, p.notes AS longdescription, p.not_discountable,
-                pg.partsgroup, p.formel, p.payment_id AS part_payment_id
-                 FROM parts p
-                LEFT JOIN chart c1 ON ((select inventory_accno_id from buchungsgruppen where id=p.buchungsgruppen_id) = c1.id)
-                LEFT JOIN chart c2 ON ((select income_accno_id_$form->{taxzone_id} from buchungsgruppen where id=p.buchungsgruppen_id) = c2.id)
-                LEFT JOIN chart c3 ON ((select expense_accno_id_$form->{taxzone_id} from buchungsgruppen where id=p.buchungsgruppen_id) = c3.id)
-                LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
-                WHERE $where|;
-  my $sth = $dbh->prepare($query);
-  $sth->execute || $form->dberror($query);
-
-  while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
+  my $taxzone_id = $form->{taxzone_id} * 1;
+  $taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
+
+  my $query =
+    qq|SELECT
+         p.id, p.partnumber, p.description, p.sellprice,
+         p.listprice, p.inventory_accno_id, p.lastcost,
+
+         c1.accno AS inventory_accno,
+         c1.new_chart_id AS inventory_new_chart,
+         date($transdate) - c1.valid_from AS inventory_valid,
+
+         c2.accno AS income_accno,
+         c2.new_chart_id AS income_new_chart,
+         date($transdate)  - c2.valid_from AS income_valid,
+
+         c3.accno AS expense_accno,
+         c3.new_chart_id AS expense_new_chart,
+         date($transdate) - c3.valid_from AS expense_valid,
+
+         p.unit, p.assembly, p.bin, p.onhand,
+         p.notes AS partnotes, p.notes AS longdescription,
+         p.not_discountable, p.formel, p.payment_id AS part_payment_id,
+         p.price_factor_id,
+
+         pfac.factor AS price_factor,
+
+         pg.partsgroup
+
+       FROM parts p
+       LEFT JOIN chart c1 ON
+         ((SELECT inventory_accno_id
+           FROM buchungsgruppen
+           WHERE id = p.buchungsgruppen_id) = c1.id)
+       LEFT JOIN chart c2 ON
+         ((SELECT income_accno_id_${taxzone_id}
+           FROM buchungsgruppen
+           WHERE id = p.buchungsgruppen_id) = c2.id)
+       LEFT JOIN chart c3 ON
+         ((SELECT expense_accno_id_${taxzone_id}
+           FROM buchungsgruppen
+           WHERE id = p.buchungsgruppen_id) = c3.id)
+       LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
+       LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
+       WHERE $where|;
+  my $sth = prepare_execute_query($form, $dbh, $query, @values);
+
+  while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {

 
     # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
     # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
 
     # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
     # es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das


@@ -1913,67 +1776,53 @@ sub retrieve_item {
     }
     delete($ref->{inventory_accno_id});
 
     }
     delete($ref->{inventory_accno_id});
 

-    #set expense_accno=inventory_accno if they are different => bilanz
-
-
-    while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >=0)) {
-      my $query = qq| SELECT accno AS inventory_accno, new_chart_id AS inventory_new_chart, date($transdate) - valid_from AS inventory_valid FROM chart WHERE id = $ref->{inventory_new_chart}|;
-      my $stw = $dbh->prepare($query);
-      $stw->execute || $form->dberror($query);
-      ($ref->{inventory_accno}, $ref->{inventory_new_chart}, $ref->{inventory_valid}) = $stw->fetchrow_array;
-      $stw->finish;
-    }
-
-    while ($ref->{income_new_chart} && ($ref->{income_valid} >=0)) {
-      my $query = qq| SELECT accno AS income_accno, new_chart_id AS income_new_chart, date($transdate) - valid_from AS income_valid FROM chart WHERE id = $ref->{income_new_chart}|;
-      my $stw = $dbh->prepare($query);
-      $stw->execute || $form->dberror($query);
-      ($ref->{income_accno}, $ref->{income_new_chart}, $ref->{income_valid}) = $stw->fetchrow_array;
-      $stw->finish;
-    }
-
-    while ($ref->{expense_new_chart} && ($ref->{expense_valid} >=0)) {
-      my $query = qq| SELECT accno AS expense_accno, new_chart_id AS expense_new_chart, date($transdate) - valid_from AS expense_valid FROM chart WHERE id = $ref->{expense_new_chart}|;
-      my $stw = $dbh->prepare($query);
-      $stw->execute || $form->dberror($query);
-      ($ref->{expense_accno}, $ref->{expense_new_chart}, $ref->{expense_valid}) = $stw->fetchrow_array;
-      $stw->finish;
+    foreach my $type (qw(inventory income expense)) {
+      while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
+        my $query =
+          qq|SELECT accno, new_chart_id, date($transdate) - valid_from
+             FROM chart
+             WHERE id = ?|;
+        ($ref->{"${type}_accno"},
+         $ref->{"${type}_new_chart"},
+         $ref->{"${type}_valid"})
+          = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
+      }

     }
 
     }
 

-    #check whether payment_terms are better than old payment_terms
-    if (($form->{payment_id} ne "") && ($ref->{part_payment_id} ne "")) {
-      my $query = qq|select (select ranking from payment_terms WHERE id = $form->{payment_id}), (select ranking from payment_terms WHERE id = $ref->{part_payment_id})|;
-      my $stw = $dbh->prepare($query);
-      $stw->execute || $form->dberror($query);
-      ($old_ranking, $new_ranking) = $stw->fetchrow_array;
-      $stw->finish;
-      if ($new_ranking <= $old_ranking) {
-        $ref->{part_payment_id} = "";
-      }
+    if ($form->{payment_id} eq "") {
+      $form->{payment_id} = $form->{part_payment_id};

     }
 
     # get tax rates and description
     }
 
     # get tax rates and description

-    $accno_id =
-      ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
-    $query = qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
-             FROM tax t LEFT JOIN chart c on (c.id=t.chart_id)
-             WHERE t.id in (SELECT tk.tax_id from taxkeys tk where tk.chart_id = (SELECT id from chart WHERE accno='$accno_id') AND startdate<=$transdate ORDER BY startdate desc LIMIT 1)
-             ORDER BY c.accno|;
-    $stw = $dbh->prepare($query);
-    $stw->execute || $form->dberror($query);
+    my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
+    $query =
+      qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
+         FROM tax t
+         LEFT JOIN chart c ON (c.id = t.chart_id)
+         WHERE t.id in
+           (SELECT tk.tax_id
+            FROM taxkeys tk
+            WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
+              AND startdate <= ?
+            ORDER BY startdate DESC
+            LIMIT 1)
+         ORDER BY c.accno|;
+    @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
+    my $stw = $dbh->prepare($query);
+    $stw->execute(@values) || $form->dberror($query);

 
     $ref->{taxaccounts} = "";
     my $i = 0;
 
     $ref->{taxaccounts} = "";
     my $i = 0;

-    while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
+    while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {

 
 

-      #    if ($customertax{$ref->{accno}}) {
+      #    if ($customertax{$ref->{accno}}) 

       if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
         $i++;
         $ptr->{accno} = $i;
       }
       $ref->{taxaccounts} .= "$ptr->{accno} ";
 
       if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
         $i++;
         $ptr->{accno} = $i;
       }
       $ref->{taxaccounts} .= "$ptr->{accno} ";
 

-      if (!($form->{taxaccounts} =~ /$ptr->{accno}/)) {
+      if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {

         $form->{"$ptr->{accno}_rate"}        = $ptr->{rate};
         $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
         $form->{"$ptr->{accno}_taxnumber"}   = $ptr->{taxnumber};
         $form->{"$ptr->{accno}_rate"}        = $ptr->{rate};
         $form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
         $form->{"$ptr->{accno}_taxnumber"}   = $ptr->{taxnumber};


@@ -1985,41 +1834,47 @@ sub retrieve_item {
     $stw->finish;
     chop $ref->{taxaccounts};
     if ($form->{language_id}) {
     $stw->finish;
     chop $ref->{taxaccounts};
     if ($form->{language_id}) {

-      $query = qq|SELECT tr.translation, tr.longdescription
-                FROM translation tr
-                WHERE tr.language_id=$form->{language_id} AND tr.parts_id=$ref->{id}|;
-      $stw = $dbh->prepare($query);
-      $stw->execute || $form->dberror($query);
-      my ($translation, $longdescription) = $stw->fetchrow_array();
+      $query =
+        qq|SELECT tr.translation, tr.longdescription
+           FROM translation tr
+           WHERE tr.language_id = ? AND tr.parts_id = ?|;
+      @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
+      my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);

       if ($translation ne "") {
         $ref->{description} = $translation;
         $ref->{longdescription} = $longdescription;
 
       } else {
       if ($translation ne "") {
         $ref->{description} = $translation;
         $ref->{longdescription} = $longdescription;
 
       } else {

-        $query = qq|SELECT tr.translation, tr.longdescription
-                FROM translation tr
-                WHERE tr.language_id in (select id from language where article_code=(select article_code from language where id = $form->{language_id})) AND tr.parts_id=$ref->{id} LIMIT 1|;
-        $stg = $dbh->prepare($query);
-        $stg->execute || $form->dberror($query);
-        my ($translation) = $stg->fetchrow_array();
+        $query =
+          qq|SELECT tr.translation, tr.longdescription
+             FROM translation tr
+             WHERE tr.language_id IN
+               (SELECT id
+                FROM language
+                WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
+               AND tr.parts_id = ?
+             LIMIT 1|;
+        @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
+        my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);

         if ($translation ne "") {
           $ref->{description} = $translation;
           $ref->{longdescription} = $longdescription;
         }
         if ($translation ne "") {
           $ref->{description} = $translation;
           $ref->{longdescription} = $longdescription;
         }

-        $stg->finish;

       }
       }

-      $stw->finish;

     }
 
     }
 

+    $ref->{onhand} *= 1;
+

     push @{ $form->{item_list} }, $ref;
 
     if ($form->{lizenzen}) {
       if ($ref->{inventory_accno} > 0) {
         $query =
     push @{ $form->{item_list} }, $ref;
 
     if ($form->{lizenzen}) {
       if ($ref->{inventory_accno} > 0) {
         $query =

-          qq| SELECT l.* FROM license l WHERE l.parts_id = $ref->{id} AND NOT l.id IN (SELECT li.license_id FROM licenseinvoice li)|;
-        $stw = $dbh->prepare($query);
-        $stw->execute || $form->dberror($query);
-        while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
+          qq|SELECT l.*
+             FROM license l
+             WHERE l.parts_id = ? AND NOT l.id IN (SELECT li.license_id FROM licenseinvoice li)|;
+        my $stw = prepare_execute_query($form, $dbh, $query, conv_i($ref->{id}));
+        while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {

           push @{ $form->{LIZENZEN}{ $ref->{id} } }, $ptr;
         }
         $stw->finish;
           push @{ $form->{LIZENZEN}{ $ref->{id} } }, $ptr;
         }
         $stw->finish;


@@ -2050,8 +1905,6 @@ sub get_pricegroups_for_parts {
 
   my $i  = 1;
   my $id = 0;
 
   my $i  = 1;
   my $id = 0;

-  my $dimension_units = AM->retrieve_units($myconfig, $form, "dimension");
-  my $service_units = AM->retrieve_units($myconfig, $form, "service");

   my $all_units = AM->retrieve_units($myconfig, $form);
   while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
     $form->{"PRICES"}{$i} = [];
   my $all_units = AM->retrieve_units($myconfig, $form);
   while (($form->{"id_$i"}) or ($form->{"new_id_$i"})) {
     $form->{"PRICES"}{$i} = [];


@@ -2063,42 +1916,38 @@ sub get_pricegroups_for_parts {
       $id = $form->{"new_id_$i"};
     }
 
       $id = $form->{"new_id_$i"};
     }
 

-    ($price, $selectedpricegroup_id) = split /--/,
-      $form->{"sellprice_pg_$i"};
+    my ($price, $selectedpricegroup_id) = split(/--/,
+      $form->{"sellprice_pg_$i"});

 
 

-    $pricegroup_old = $form->{"pricegroup_old_$i"};
+    my $pricegroup_old = $form->{"pricegroup_old_$i"};

     $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
     $form->{"old_pricegroup_$i"} = $pricegroup_old;
     $form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
     $form->{"old_pricegroup_$i"} = $pricegroup_old;

-    $price_new = $form->{"price_new_$i"};

 
 

-    $price_old = $form->{"price_old_$i"};
-    $query =
-      qq|SELECT pricegroup_id, (SELECT p.sellprice from parts p where p.id = $id) as default_sellprice,(SELECT pg.pricegroup FROM pricegroup pg WHERE id=pricegroup_id) AS pricegroup, price, '' AS selected FROM prices WHERE parts_id = $id UNION SELECT 0 as pricegroup_id,(SELECT sellprice FROM parts WHERE id=$id) as default_sellprice,'' as pricegroup, (SELECT DISTINCT sellprice from parts where id=$id) as price, 'selected' AS selected from prices ORDER BY pricegroup|;
+    my $price_new = $form->{"price_new_$i"};
+    my $price_old = $form->{"price_old_$i"};

 
 

-    $pkq = $dbh->prepare($query);
-    $pkq->execute || $form->dberror($query);

     if (!$form->{"unit_old_$i"}) {
       # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
       # Einheit, wie sie in den Stammdaten hinterlegt wurde.
       # Es sollte also angenommen werden, dass diese ausgewaehlt war.
       $form->{"unit_old_$i"} = $form->{"unit_$i"};
     }
     if (!$form->{"unit_old_$i"}) {
       # Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
       # Einheit, wie sie in den Stammdaten hinterlegt wurde.
       # Es sollte also angenommen werden, dass diese ausgewaehlt war.
       $form->{"unit_old_$i"} = $form->{"unit_$i"};
     }

- 
+

     # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
     # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
     $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
 
     # Die zuletzt ausgewaehlte mit der aktuell ausgewaehlten Einheit
     # vergleichen und bei Unterschied den Preis entsprechend umrechnen.
     $form->{"selected_unit_$i"} = $form->{"unit_$i"} unless ($form->{"selected_unit_$i"});
 

-    my $check_units = $form->{"inventory_accno_$i"} ? $dimension_units : $service_units;
-    if (!$check_units->{$form->{"selected_unit_$i"}} ||
-        ($check_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne
+    if (!$all_units->{$form->{"selected_unit_$i"}} ||
+        ($all_units->{$form->{"selected_unit_$i"}}->{"base_unit"} ne

          $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
       # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
       # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
       # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
       $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
     }
          $all_units->{$form->{"unit_old_$i"}}->{"base_unit"})) {
       # Die ausgewaehlte Einheit ist fuer diesen Artikel nicht gueltig
       # (z.B. Dimensionseinheit war ausgewaehlt, es handelt sich aber
       # um eine Dienstleistung). Dann keinerlei Umrechnung vornehmen.
       $form->{"unit_old_$i"} = $form->{"selected_unit_$i"} = $form->{"unit_$i"};
     }

+

     my $basefactor = 1;
     my $basefactor = 1;

-    
+

     if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
       if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
           $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {
     if ($form->{"unit_old_$i"} ne $form->{"selected_unit_$i"}) {
       if (defined($all_units->{$form->{"unit_old_$i"}}->{"factor"}) &&
           $all_units->{$form->{"unit_old_$i"}}->{"factor"}) {


@@ -2106,13 +1955,36 @@ sub get_pricegroups_for_parts {
           $all_units->{$form->{"unit_old_$i"}}->{"factor"};
       }
     }
           $all_units->{$form->{"unit_old_$i"}}->{"factor"};
       }
     }

+

     if (!$form->{"basefactor_$i"}) {
       $form->{"basefactor_$i"} = 1;
     }
     if (!$form->{"basefactor_$i"}) {
       $form->{"basefactor_$i"} = 1;
     }

-    while ($pkr = $pkq->fetchrow_hashref(NAME_lc)) {

 
 

-      #       push @{ $form->{PRICES}{$id} }, $pkr;
-      #push @{ $form->{PRICES}{$i} }, $pkr;
+    my $query =
+      qq|SELECT
+           pricegroup_id,
+           (SELECT p.sellprice FROM parts p WHERE p.id = ?) AS default_sellprice,
+           (SELECT pg.pricegroup FROM pricegroup pg WHERE id = pricegroup_id) AS pricegroup,
+           price,
+           '' AS selected
+          FROM prices
+          WHERE parts_id = ?
+
+          UNION
+
+          SELECT
+            0 as pricegroup_id,
+            (SELECT sellprice FROM parts WHERE id = ?) AS default_sellprice,
+            '' AS pricegroup,
+            (SELECT DISTINCT sellprice FROM parts where id = ?) AS price,
+            'selected' AS selected
+          FROM prices
+
+          ORDER BY pricegroup|;
+    my @values = (conv_i($id), conv_i($id), conv_i($id), conv_i($id));
+    my $pkq = prepare_execute_query($form, $dbh, $query, @values);
+
+    while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {

       $pkr->{id}       = $id;
       $pkr->{selected} = '';
 
       $pkr->{id}       = $id;
       $pkr->{selected} = '';
 


@@ -2123,9 +1995,9 @@ sub get_pricegroups_for_parts {
       }
 
       $pkr->{price} *= $form->{"basefactor_$i"};
       }
 
       $pkr->{price} *= $form->{"basefactor_$i"};

-      
+

       $pkr->{price} *= $basefactor;
       $pkr->{price} *= $basefactor;

- 
+

       $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
 
       if ($selectedpricegroup_id eq undef) {
       $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
 
       if ($selectedpricegroup_id eq undef) {


@@ -2143,12 +2015,9 @@ sub get_pricegroups_for_parts {
             $form->{"sellprice_$i"} = $pkr->{price};
           }
 
             $form->{"sellprice_$i"} = $pkr->{price};
           }
 

-        } else {
-          if ($pkr->{price} == $pkr->{default_sellprice}) {
-
-            $pkr->{price}    = $form->{"sellprice_$i"};
-            $pkr->{selected} = ' selected';
-          }
+        } elsif ($pkr->{price} == $pkr->{default_sellprice}) {
+          $pkr->{price}    = $form->{"sellprice_$i"};
+          $pkr->{selected} = ' selected';

         }
       }
 
         }
       }
 


@@ -2157,23 +2026,18 @@ sub get_pricegroups_for_parts {
           if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
             $pkr->{selected}  = ' selected';
           }
           if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
             $pkr->{selected}  = ' selected';
           }

-        } else {
-          if (($price_new != $form->{"sellprice_$i"}) and ($price_new ne 0)) {
-            if ($pkr->{pricegroup_id} == 0) {
-              $pkr->{price}     = $form->{"sellprice_$i"};
-              $pkr->{selected}  = ' selected';
-            }
+        } elsif (($price_new != $form->{"sellprice_$i"}) and ($price_new ne 0)) {
+          if ($pkr->{pricegroup_id} == 0) {
+            $pkr->{price}     = $form->{"sellprice_$i"};
+            $pkr->{selected}  = ' selected';
+          }
+        } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
+          $pkr->{selected}  = ' selected';
+          if (    ($pkr->{pricegroup_id} == 0)
+              and ($pkr->{price} == $form->{"sellprice_$i"})) {
+            # $pkr->{price}                         = $form->{"sellprice_$i"};

           } else {
           } else {

-            if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
-              $pkr->{selected}  = ' selected';
-              if (    ($pkr->{pricegroup_id} == 0)
-                  and ($pkr->{price} == $form->{"sellprice_$i"})) {
-
-                # $pkr->{price}                         = $form->{"sellprice_$i"};
-              } else {
-                $pkr->{price} = $form->{"sellprice_$i"};
-              }
-            }
+            $pkr->{price} = $form->{"sellprice_$i"};

           }
         }
       }
           }
         }
       }


@@ -2192,36 +2056,50 @@ sub get_pricegroups_for_parts {
   $main::lxdebug->leave_sub();
 }
 
   $main::lxdebug->leave_sub();
 }
 

-sub webdav_folder {
+sub has_storno {

   $main::lxdebug->enter_sub();
 
   $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 storno_id = ?|;
+  my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
+
+  $dbh->disconnect();

 
   $main::lxdebug->leave_sub();
 
   $main::lxdebug->leave_sub();

+
+  return $result;

 }
 
 }
 

-1;
+sub is_storno {
+  $main::lxdebug->enter_sub();
+
+  my ($self, $myconfig, $form, $table, $id) = @_;
+
+  $main::lxdebug->leave_sub() and return 0 unless ($id);
+
+  # 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, $id);

 
 

+  $dbh->disconnect();
+
+  $main::lxdebug->leave_sub();
+
+  return $result;
+}
+
+1;