X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;ds=sidebyside;f=SL%2FAR.pm;h=a211b1fcf6001e81168632305f00760d18c1006d;hb=1c603341fc02e3a5a7b5126cd7df6478d2e34700;hp=14fa809c4d86ea4a4597bdc9a8a6bdb09d733047;hpb=39da7f8cf4f0db564e9e6e9d8f5bd8aedcc5aaea;p=kivitendo-erp.git diff --git a/SL/AR.pm b/SL/AR.pm index 14fa809c4..a211b1fcf 100644 --- a/SL/AR.pm +++ b/SL/AR.pm @@ -196,7 +196,8 @@ sub post_transaction { } 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 @@ -279,34 +280,34 @@ sub _delete_payments { 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); } @@ -348,6 +349,7 @@ sub post_payment { ($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}); @@ -358,7 +360,7 @@ sub post_payment { 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})); @@ -405,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}) { @@ -468,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); @@ -479,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(); } @@ -649,8 +654,8 @@ sub storno { 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/) {