X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FAP.pm;h=39a68a44fd1a147777d10ed01d5460667e03b8db;hb=0a382648a783af65ddf69cc02124379bf0c24844;hp=24054c8cb5b978945b4dd222a5a329f3870bcd63;hpb=c654c46b8b5f9475d2515750aa4bbb3fdddc63fc;p=kivitendo-erp.git diff --git a/SL/AP.pm b/SL/AP.pm index 24054c8cb..39a68a44f 100644 --- a/SL/AP.pm +++ b/SL/AP.pm @@ -35,13 +35,18 @@ package AP; use SL::DBUtils; +use SL::IO; use SL::MoreCommon; +use Data::Dumper; + +use strict; + sub post_transaction { $main::lxdebug->enter_sub(); my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_; - + my $rc = 0; # return code auf false setzen # connect to database my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect_noauto($myconfig); @@ -49,6 +54,7 @@ sub post_transaction { my $exchangerate = 0; $form->{defaultcurrency} = $form->get_default_currency($myconfig); + delete $form->{currency} unless $form->{defaultcurrency}; ($null, $form->{department_id}) = split(/--/, $form->{department}); $form->{department_id} *= 1; @@ -60,12 +66,12 @@ sub post_transaction { $form->{exchangerate} = $exchangerate || $form->parse_amount($myconfig, $form->{exchangerate}); } - for $i (1 .. $form->{rowcount}) { + for my $i (1 .. $form->{rowcount}) { $form->{AP_amounts}{"amount_$i"} = (split(/--/, $form->{"AP_amount_$i"}))[0]; } ($form->{AP_amounts}{payables}) = split(/--/, $form->{APselected}); - ($form->{AP}{payables}) = split(/--/, $form->{APselected}); + ($form->{AP_payables}) = split(/--/, $form->{APselected}); # reverse and parse amounts for my $i (1 .. $form->{rowcount}) { @@ -86,43 +92,30 @@ sub post_transaction { # taxincluded doesn't make sense if there is no amount $form->{taxincluded} = 0 if ($form->{amount} == 0); - for $i (1 .. $form->{rowcount}) { - ($form->{"tax_id_$i"}, $NULL) = split /--/, $form->{"taxchart_$i"}; + for my $i (1 .. $form->{rowcount}) { + ($form->{"tax_id_$i"}, undef) = split /--/, $form->{"taxchart_$i"}; - $query = + my $query = qq|SELECT c.accno, t.taxkey, t.rate | . qq|FROM tax t LEFT JOIN chart c on (c.id=t.chart_id) | . qq|WHERE t.id = ? | . qq|ORDER BY c.accno|; - $sth = $dbh->prepare($query); + my $sth = $dbh->prepare($query); $sth->execute($form->{"tax_id_$i"}) || $form->dberror($query . " (" . $form->{"tax_id_$i"} . ")"); - ($form->{AP_amounts}{"tax_$i"}, $form->{"taxkey_$i"}, $form->{"taxrate_$i"}) = - $sth->fetchrow_array; - $form->{AP_amounts}{"tax_$i"}{taxkey} = $form->{"taxkey_$i"}; - $form->{AP_amounts}{"amount_$i"}{taxkey} = $form->{"taxkey_$i"}; + ($form->{AP_amounts}{"tax_$i"}, $form->{"taxkey_$i"}, $form->{"taxrate_$i"}) = $sth->fetchrow_array(); $sth->finish; + + my ($tax, $diff); if ($form->{taxincluded} *= 1) { - if (!$form->{"korrektur_$i"}) { - $tax = - $form->{"amount_$i"} - - ($form->{"amount_$i"} / ($form->{"taxrate_$i"} + 1)); - } else { - $tax = $form->{"tax_$i"}; - } + $tax = $form->{"amount_$i"} - ($form->{"amount_$i"} / ($form->{"taxrate_$i"} + 1)); $amount = $form->{"amount_$i"} - $tax; $form->{"amount_$i"} = $form->round_amount($amount, 2); $diff += $amount - $form->{"amount_$i"}; $form->{"tax_$i"} = $form->round_amount($tax, 2); $form->{netamount} += $form->{"amount_$i"}; } else { - if (!$form->{"korrektur_$i"}) { - $form->{"tax_$i"} = $form->{"amount_$i"} * $form->{"taxrate_$i"}; - } else { - $tax = $form->{"tax_$i"}; - } - $form->{"tax_$i"} = - $form->round_amount($form->{"tax_$i"} * $form->{exchangerate}, 2); + $form->{"tax_$i"} = $form->{"amount_$i"} * $form->{"taxrate_$i"}; $form->{netamount} += $form->{"amount_$i"}; } $form->{total_tax} += $form->{"tax_$i"} * -1; @@ -154,16 +147,13 @@ sub post_transaction { # amount for total AP $form->{payables} = $form->{invtotal}; - $form->{datepaid} = $form->{transdate} unless ($form->{datepaid}); - my $datepaid = ($form->{invpaid} != 0) ? $form->{datepaid} : undef; - # update exchangerate if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) { $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0, $form->{exchangerate}); } - my ($query, $sth); + my ($query, $sth, @values); if (!$payments_only) { # if we have an id delete old records @@ -194,25 +184,24 @@ sub post_transaction { $query = qq|UPDATE ap SET invnumber = ?, transdate = ?, ordnumber = ?, vendor_id = ?, taxincluded = ?, - amount = ?, duedate = ?, paid = ?, datepaid = ?, netamount = ?, + amount = ?, duedate = ?, paid = ?, netamount = ?, curr = ?, notes = ?, department_id = ?, storno = ?, storno_id = ? WHERE id = ?|; - my @values = ($form->{invnumber}, conv_date($form->{transdate}), + @values = ($form->{invnumber}, conv_date($form->{transdate}), $form->{ordnumber}, conv_i($form->{vendor_id}), $form->{taxincluded} ? 't' : 'f', $form->{invtotal}, conv_date($form->{duedate}), $form->{invpaid}, - conv_date($datepaid), $form->{netamount}, + $form->{netamount}, $form->{currency}, $form->{notes}, conv_i($form->{department_id}), $form->{storno}, $form->{storno_id}, $form->{id}); do_query($form, $dbh, $query, @values); # add individual transactions - for $i (1 .. $form->{rowcount}) { + for my $i (1 .. $form->{rowcount}) { if ($form->{"amount_$i"} != 0) { my $project_id; $project_id = conv_i($form->{"project_id_$i"}); - $taxkey = $form->{AP_amounts}{"amount_$i"}{taxkey}; # insert detail records in acc_trans $query = @@ -222,7 +211,7 @@ sub post_transaction { qq| ?, ?, ?, ?)|; @values = ($form->{id}, $form->{AP_amounts}{"amount_$i"}, $form->{"amount_$i"}, conv_date($form->{transdate}), - $project_id, $taxkey); + $project_id, $form->{"taxkey_$i"}); do_query($form, $dbh, $query, @values); if ($form->{"tax_$i"} != 0) { @@ -234,7 +223,7 @@ sub post_transaction { qq| ?, ?, ?, ?)|; @values = ($form->{id}, $form->{AP_amounts}{"tax_$i"}, $form->{"tax_$i"}, conv_date($form->{transdate}), - $project_id, $taxkey); + $project_id, $form->{"taxkey_$i"}); do_query($form, $dbh, $query, @values); } @@ -272,7 +261,7 @@ sub post_transaction { # get paid account - ($form->{AP}{"paid_$i"}) = split(/--/, $form->{"AP_paid_$i"}); + ($form->{"AP_paid_account_$i"}) = split(/--/, $form->{"AP_paid_$i"}); $form->{"datepaid_$i"} = $form->{transdate} unless ($form->{"datepaid_$i"}); @@ -289,9 +278,9 @@ sub post_transaction { qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, project_id, taxkey) | . qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, | . qq| (SELECT taxkey_id FROM chart WHERE accno = ?))|; - @values = ($form->{id}, $form->{AP}{payables}, $amount, + @values = ($form->{id}, $form->{AP_payables}, $amount, conv_date($form->{"datepaid_$i"}), $project_id, - $form->{AP}{payables}); + $form->{AP_payables}); do_query($form, $dbh, $query, @values); } $form->{payables} = $amount; @@ -301,9 +290,9 @@ sub post_transaction { qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, project_id, taxkey) | . qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?, | . qq| (SELECT taxkey_id FROM chart WHERE accno = ?))|; - @values = ($form->{id}, $form->{AP}{"paid_$i"}, $form->{"paid_$i"}, + @values = ($form->{id}, $form->{"AP_paid_account_$i"}, $form->{"paid_$i"}, conv_date($form->{"datepaid_$i"}), $form->{"source_$i"}, - $form->{"memo_$i"}, $project_id, $form->{AP}{"paid_$i"}); + $form->{"memo_$i"}, $project_id, $form->{"AP_paid_account_$i"}); do_query($form, $dbh, $query, @values); # add exchange rate difference @@ -315,9 +304,9 @@ sub post_transaction { qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, fx_transaction, cleared, project_id, taxkey) | . qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 't', 'f', ?, | . qq| (SELECT taxkey_id FROM chart WHERE accno = ?))|; - @values = ($form->{id}, $form->{AP}{"paid_$i"}, $amount, + @values = ($form->{id}, $form->{"AP_paid_account_$i"}, $amount, conv_date($form->{"datepaid_$i"}), $project_id, - $form->{AP}{"paid_$i"}); + $form->{"AP_paid_account_$i"}); do_query($form, $dbh, $query, @values); } @@ -350,16 +339,19 @@ sub post_transaction { } if ($payments_only) { - $query = qq|UPDATE ap SET paid = ? WHERE id = ?|; - do_query($form, $dbh, $query, $form->{invpaid}, conv_i($form->{id})); + $query = qq|UPDATE ap SET paid = ?, datepaid = ? WHERE id = ?|; + do_query($form, $dbh, $query, $form->{invpaid}, $form->{invpaid} ? conv_date($form->{datepaid}) : undef, conv_i($form->{id})); } - my $rc = 1; + IO->set_datepaid(table => 'ap', id => $form->{id}, dbh => $dbh); + if (!$provided_dbh) { $dbh->commit(); $dbh->disconnect(); } + $rc = 1; # Den return-code auf true setzen, aber nur falls beim commit alles i.O. ist + $main::lxdebug->leave_sub(); return $rc; @@ -394,20 +386,32 @@ sub ap_transactions { my ($self, $myconfig, $form) = @_; # connect to database - my $dbh = $form->dbconnect($myconfig); + my $dbh = $form->get_standard_dbh($myconfig); my $query = qq|SELECT a.id, a.invnumber, a.transdate, a.duedate, a.amount, a.paid, | . qq| a.ordnumber, v.name, a.invoice, a.netamount, a.datepaid, a.notes, | . - qq| a.globalproject_id, | . + qq| a.globalproject_id, a.storno, a.storno_id, | . qq| pr.projectnumber AS globalprojectnumber, | . - qq| e.name AS employee | . + qq| e.name AS employee, | . + qq| v.vendornumber, v.country, v.ustid, | . + 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 ~ 'AP[[:>:]]' + AND at.trans_id = a.id + LIMIT 1 + ) AS charts } . qq|FROM ap a | . qq|JOIN vendor v ON (a.vendor_id = v.id) | . qq|LEFT JOIN employee e ON (a.employee_id = e.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 = v.taxzone_id)| . + qq|LEFT JOIN payment_terms pt ON (pt.id = v.payment_id)|; - my $where = qq| WHERE COALESCE(storno, false) != true |; + my $where = ''; my @values; if ($form->{vendor_id}) { @@ -458,34 +462,24 @@ sub ap_transactions { } if ($where) { -# substr($where, 0, 4) = "WHERE"; + substr($where, 0, 4, " WHERE "); $query .= $where; } - my @a = (transdate, invnumber, name); - push @a, "employee" if $self->{l_employee}; + 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); - if (grep({ $_ eq $form->{sort} } - qw(transdate id invnumber ordnumber name netamount tax amount - paid datepaid due duedate notes employee))) { + if (grep({ $_ eq $form->{sort} } qw(transdate id invnumber ordnumber name netamount tax amount paid datepaid due duedate notes employee transaction_description))) { $sortorder = $form->{sort} . " $sortdir"; } $query .= " 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->{AP} = []; - while (my $ap = $sth->fetchrow_hashref(NAME_lc)) { - push @{ $form->{AP} }, $ap; - } - - $sth->finish; - $dbh->disconnect; + $form->{AP} = [ @result ]; $main::lxdebug->leave_sub(); } @@ -515,34 +509,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 '%AP_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 = 'AP') OR (c.link LIKE '%:AP') OR (c.link LIKE 'AP:%')) - 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); } @@ -582,10 +576,9 @@ sub post_payment { $self->setup_form($form); - ($form->{defaultcurrency}) = selectrow_query($form, $dbh, qq|SELECT curr FROM defaults|); - $form->{defaultcurrency} = (split m/:/, $form->{defaultcurrency})[0]; - - $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate}); + $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate}); + $form->{defaultcurrency} = $form->get_default_currency($myconfig); + delete $form->{currency} unless $form->{defaultcurrency}; # Get the AP accno. $query = @@ -594,7 +587,7 @@ sub post_payment { LEFT JOIN chart c ON (at.chart_id = c.id) WHERE (trans_id = ?) AND ((c.link = 'AP') OR (c.link LIKE '%:AP') OR (c.link LIKE 'AP:%')) - ORDER BY at.oid + ORDER BY at.acc_trans_id LIMIT 1|; ($form->{APselected}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id})); @@ -617,7 +610,8 @@ sub setup_form { my ($self, $form) = @_; - my ($exchangerate, $i, $j, $k, $key, $akey, $ref, $index, $taxamount, $totalamount); + my ($exchangerate, $i, $j, $k, $key, $akey, $ref, $index, $taxamount, $totalamount, $totaltax, $totalwithholding, $withholdingrate, + $taxincluded, $tax, $diff); # forex $form->{forex} = $form->{exchangerate}; @@ -638,6 +632,7 @@ sub setup_form { $j = 0; $k = 0; + next unless $form->{acc_trans}{$key}; for $i (1 .. scalar @{ $form->{acc_trans}{$key} }) { if ($key eq "AP_paid") { @@ -765,8 +760,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/) { @@ -774,13 +769,15 @@ sub storno { } for my $row (@$rowref) { - delete @$row{qw(itime mtime link)}; + delete @$row{qw(itime mtime link acc_trans_id)}; $query = sprintf 'INSERT INTO acc_trans (%s) VALUES (%s)', join(', ', keys %$row), join(', ', map '?', values %$row); $row->{trans_id} = $new_id; $row->{amount} *= -1; do_query($form, $dbh, $query, (values %$row)); } + map { IO->set_datepaid(table => 'ap', id => $_, dbh => $dbh) } ($id, $new_id); + $dbh->commit; $main::lxdebug->leave_sub();