- 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| 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| e2.name AS salesman | .
+ qq| e2.name AS salesman, | .
+ qq| tz.description AS taxzone, | .
+ qq| pt.description AS payment_terms, | .
+ qq{ ch.accno || ' -- ' || ch.description 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|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)| .
+ qq|LEFT JOIN acc_trans at ON (at.trans_id = a.id)| .
+ qq|INNER JOIN chart ch ON (ch.id = at.chart_id AND ch.link ~ 'AR[[:>:]]')|;
push @a, "employee" if $form->{l_employee};
my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
my $sortorder = join(', ', map { "$_ $sortdir" } @a);
push @a, "employee" if $form->{l_employee};
my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
my $sortorder = join(', ', map { "$_ $sortdir" } @a);
- my $sth = $dbh->prepare($query);
- $sth->execute(@values) ||
- $form->dberror($query . " (" . join(", ", @values) . ")");
+ my @result = selectall_hashref_query($form, $dbh, $query, @values);
# 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|;
# 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/) {
# kill all entries containing payments, which are the last 2n rows, of which the last has link =~ /paid/
while ($rowref->[-1]{link} =~ /paid/) {