Merge branch 'master' of ssh://lx-office/~/lx-office-erp

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

diff --git a/SL/AR.pm b/SL/AR.pm
index 7ee12fa..a211b1f 100644 (file)
--- a/SL/AR.pm
+++ b/SL/AR.pm
@@ -407,26 +407,37 @@ sub ar_transactions {
   my ($self, $myconfig, $form) = @_;
 
   # connect to database
-  my $dbh = $form->dbconnect($myconfig);
+  my $dbh = $form->get_standard_dbh($myconfig);
 
   my @values;
 
   my $query =
-    qq|SELECT a.id, a.invnumber, a.ordnumber, a.transdate, | .
+    qq|SELECT DISTINCT a.id, a.invnumber, a.ordnumber, a.transdate, | .
     qq|  a.duedate, a.netamount, a.amount, a.paid, | .
     qq|  a.invoice, a.datepaid, a.terms, a.notes, a.shipvia, | .
     qq|  a.shippingpoint, a.storno, a.storno_id, a.globalproject_id, | .
     qq|  a.marge_total, a.marge_percent, | .
     qq|  a.transaction_description, | .
     qq|  pr.projectnumber AS globalprojectnumber, | .
-    qq|  c.name, | .
+    qq|  c.name, c.customernumber, c.country, c.ustid, | .
     qq|  e.name AS employee, | .
-    qq|  e2.name AS salesman | .
+    qq|  e2.name AS salesman, | .
+    qq|  tz.description AS taxzone, | .
+    qq|  pt.description AS payment_terms, | .
+    qq{  ( SELECT ch.accno || ' -- ' || ch.description
+           FROM acc_trans at
+           LEFT JOIN chart ch ON ch.id = at.chart_id
+           WHERE ch.link ~ 'AR[[:>:]]'
+            AND at.trans_id = a.id
+            LIMIT 1
+          ) AS charts } .
     qq|FROM ar a | .
     qq|JOIN customer c ON (a.customer_id = c.id) | .
     qq|LEFT JOIN employee e ON (a.employee_id = e.id) | .
     qq|LEFT JOIN employee e2 ON (a.salesman_id = e2.id) | .
-    qq|LEFT JOIN project pr ON (a.globalproject_id = pr.id)|;
+    qq|LEFT JOIN project pr ON (a.globalproject_id = pr.id)| .
+    qq|LEFT JOIN tax_zones tz ON (tz.id = c.taxzone_id)| .
+    qq|LEFT JOIN payment_terms pt ON (pt.id = c.payment_id)|;
 
   my $where = "1 = 1";
   if ($form->{customer_id}) {
@@ -470,7 +481,7 @@ sub ar_transactions {
     }
   }
 
-  my @a = (transdate, invnumber, name);
+  my @a = qw(transdate invnumber name);
   push @a, "employee" if $form->{l_employee};
   my $sortdir   = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
   my $sortorder = join(', ', map { "$_ $sortdir" } @a);
@@ -481,17 +492,9 @@ sub ar_transactions {
 
   $query .= " WHERE $where ORDER BY $sortorder";
 
-  my $sth = $dbh->prepare($query);
-  $sth->execute(@values) ||
-    $form->dberror($query . " (" . join(", ", @values) . ")");
+  my @result = selectall_hashref_query($form, $dbh, $query, @values);
 
-  $form->{AR} = [];
-  while (my $ar = $sth->fetchrow_hashref(NAME_lc)) {
-    push @{ $form->{AR} }, $ar;
-  }
-
-  $sth->finish;
-  $dbh->disconnect;
+  $form->{AR} = [ @result ];
 
   $main::lxdebug->leave_sub();
 }
@@ -652,7 +655,7 @@ sub storno {
 
   # now copy acc_trans entries
   $query = qq|SELECT a.*, c.link FROM acc_trans a LEFT JOIN chart c ON a.chart_id = c.id WHERE a.trans_id = ? ORDER BY a.acc_trans_id|;
-  my $rowref = selectall_hashref_query($form, $dbh, $query, $id); 
+  my $rowref = selectall_hashref_query($form, $dbh, $query, $id);
 
   # kill all entries containing payments, which are the last 2n rows, of which the last has link =~ /paid/
   while ($rowref->[-1]{link} =~ /paid/) {