} else {
# Record paid amount.
- do_query($form, $dbh, qq|UPDATE ar SET paid = ? WHERE id = ?|, $form->{paid}, conv_i($form->{id}));
+ $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}));
}
# add paid transactions
my ($self, $form, $dbh) = @_;
- my @delete_oids;
+ my @delete_acc_trans_ids;
# Delete old payment entries from acc_trans.
my $query =
- qq|SELECT oid
+ qq|SELECT acc_trans_id
FROM acc_trans
WHERE (trans_id = ?) AND fx_transaction
UNION
- SELECT at.oid
+ SELECT at.acc_trans_id
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}));
+ push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
$query =
- qq|SELECT at.oid
+ qq|SELECT at.acc_trans_id
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
+ ORDER BY at.acc_trans_id
OFFSET 1|;
- push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
+ push @delete_acc_trans_ids, 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|)|;
+ if (@delete_acc_trans_ids) {
+ $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
do_query($form, $dbh, $query);
}
($form->{defaultcurrency}) = selectrow_query($form, $dbh, qq|SELECT curr FROM defaults|);
$form->{defaultcurrency} = (split m/:/, $form->{defaultcurrency})[0];
+ $form->{currency} = $form->{defaultcurrency} if ($form->{defaultcurrency} && ($form->{currency} =~ m/^\s*$/));
$form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});
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
+ ORDER BY at.acc_trans_id
LIMIT 1|;
($form->{ARselected}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
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}) {
}
}
- 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);
$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();
}
do_query($form, $dbh, $query, $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.oid|;
- my $rowref = selectall_hashref_query($form, $dbh, $query, $id);
+ $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);
# kill all entries containing payments, which are the last 2n rows, of which the last has link =~ /paid/
while ($rowref->[-1]{link} =~ /paid/) {