X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FAP.pm;h=3c8e2404f042ad76e448309ad0f132b0afc44875;hb=493457086f727b713cca06c25c1c3bedb92185af;hp=cb5f65b171862a2855b812202bb3a0f369ca1faa;hpb=923610728cc212616c8e2fd0b50ad0f555ce3cc5;p=kivitendo-erp.git diff --git a/SL/AP.pm b/SL/AP.pm index cb5f65b17..3c8e2404f 100644 --- a/SL/AP.pm +++ b/SL/AP.pm @@ -34,41 +34,45 @@ package AP; +use SL::DATEV qw(:CONSTANTS); use SL::DBUtils; +use SL::IO; +use SL::MoreCommon; +use SL::DB::Default; +use Data::Dumper; + +use strict; sub post_transaction { $main::lxdebug->enter_sub(); - my ($self, $myconfig, $form) = @_; - + my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_; + my $rc = 0; # return code auf false setzen # connect to database - my $dbh = $form->dbconnect_noauto($myconfig); + my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect_noauto($myconfig); my ($null, $taxrate, $amount); 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; if ($form->{currency} eq $form->{defaultcurrency}) { $form->{exchangerate} = 1; } else { - $exchangerate = - $form->check_exchangerate($myconfig, $form->{currency}, - $form->{transdate}, 'sell'); - - $form->{exchangerate} = - ($exchangerate) - ? $exchangerate - : $form->parse_amount($myconfig, $form->{exchangerate}); + $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, 'sell'); + $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}) { @@ -89,43 +93,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; @@ -157,108 +148,100 @@ sub post_transaction { # amount for total AP $form->{payables} = $form->{invtotal}; - my ($query, $sth); - - # if we have an id delete old records - if ($form->{id}) { - - # delete detail records - $query = qq|DELETE FROM acc_trans WHERE trans_id = ?|; - do_query($form, $dbh, $query, $form->{id}); + # update exchangerate + if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) { + $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0, + $form->{exchangerate}); + } - } else { - my $uid = rand() . time; + my ($query, $sth, @values); - $uid .= $form->{login}; + if (!$payments_only) { + # if we have an id delete old records + if ($form->{id}) { - $uid = substr($uid, 2, 75); + # delete detail records + $query = qq|DELETE FROM acc_trans WHERE trans_id = ?|; + do_query($form, $dbh, $query, $form->{id}); - $query = - qq|INSERT INTO ap (invnumber, employee_id) | . - qq|VALUES (?, (SELECT e.id FROM employee e WHERE e.login = ?))|; - do_query($form, $dbh, $query, $uid, $form->{login}); + } else { + my $uid = rand() . time; - $query = qq|SELECT a.id FROM ap a - WHERE a.invnumber = ?|; - ($form->{id}) = selectrow_query($form, $dbh, $query, $uid); - } + $uid .= $form->{login}; - $form->{invnumber} = $form->{id} unless $form->{invnumber}; - - $form->{datepaid} = $form->{transdate} unless ($form->{datepaid}); - my $datepaid = ($form->{invpaid} != 0) ? $form->{datepaid} : undef; - - $query = qq|UPDATE ap SET - invnumber = ?, - transdate = ?, - ordnumber = ?, - vendor_id = ?, - taxincluded = ?, - amount = ?, - duedate = ?, - paid = ?, - datepaid = ?, - netamount = ?, - curr = ?, - notes = ?, - department_id = ? - WHERE id = ?|; - my @values = ($form->{invnumber}, conv_date($form->{transdate}), - $form->{ordnumber}, conv_i($form->{vendor_id}), - $form->{taxincluded}, $form->{invtotal}, - conv_date($form->{duedate}), $form->{invpaid}, - conv_date($datepaid), $form->{netamount}, - $form->{currency}, $form->{notes}, - conv_i($form->{department_id}), $form->{id}); - do_query($form, $dbh, $query, @values); + $uid = substr($uid, 2, 75); - # update exchangerate - if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) { - $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0, - $form->{exchangerate}); - } + $query = + qq|INSERT INTO ap (invnumber, employee_id) | . + qq|VALUES (?, (SELECT e.id FROM employee e WHERE e.login = ?))|; + do_query($form, $dbh, $query, $uid, $form->{login}); - # add individual transactions - for $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}; + $query = qq|SELECT a.id FROM ap a + WHERE a.invnumber = ?|; + ($form->{id}) = selectrow_query($form, $dbh, $query, $uid); + } - # insert detail records in acc_trans - $query = - qq|INSERT INTO acc_trans | . - qq| (trans_id, chart_id, amount, transdate, project_id, taxkey)| . - qq|VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), | . - qq| ?, ?, ?, ?)|; - @values = ($form->{id}, $form->{AP_amounts}{"amount_$i"}, - $form->{"amount_$i"}, conv_date($form->{transdate}), - $project_id, $taxkey); - do_query($form, $dbh, $query, @values); + $form->{invnumber} = $form->{id} unless $form->{invnumber}; + + $query = qq|UPDATE ap SET + invnumber = ?, transdate = ?, ordnumber = ?, vendor_id = ?, taxincluded = ?, + amount = ?, duedate = ?, paid = ?, netamount = ?, + curr = ?, notes = ?, department_id = ?, storno = ?, storno_id = ?, + globalproject_id = ? + WHERE id = ?|; + @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}, + $form->{netamount}, + $form->{currency}, $form->{notes}, + conv_i($form->{department_id}), $form->{storno}, + $form->{storno_id}, conv_i($form->{globalproject_id}), + $form->{id}); + do_query($form, $dbh, $query, @values); + + # add individual transactions + for my $i (1 .. $form->{rowcount}) { + if ($form->{"amount_$i"} != 0) { + my $project_id; + $project_id = conv_i($form->{"project_id_$i"}); - if ($form->{"tax_$i"} != 0) { # insert detail records in acc_trans $query = - qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, | . - qq| project_id, taxkey) | . + qq|INSERT INTO acc_trans | . + qq| (trans_id, chart_id, amount, transdate, project_id, taxkey)| . qq|VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), | . qq| ?, ?, ?, ?)|; - @values = ($form->{id}, $form->{AP_amounts}{"tax_$i"}, - $form->{"tax_$i"}, conv_date($form->{transdate}), - $project_id, $taxkey); + @values = ($form->{id}, $form->{AP_amounts}{"amount_$i"}, + $form->{"amount_$i"}, conv_date($form->{transdate}), + $project_id, $form->{"taxkey_$i"}); do_query($form, $dbh, $query, @values); - } + if ($form->{"tax_$i"} != 0) { + # insert detail records in acc_trans + $query = + qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, | . + qq| project_id, taxkey) | . + qq|VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), | . + qq| ?, ?, ?, ?)|; + @values = ($form->{id}, $form->{AP_amounts}{"tax_$i"}, + $form->{"tax_$i"}, conv_date($form->{transdate}), + $project_id, $form->{"taxkey_$i"}); + do_query($form, $dbh, $query, @values); + } + + } } - } - # add payables - $query = - qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate) | . - qq|VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), ?, ?)|; - @values = ($form->{id}, $form->{AP_amounts}{payables}, $form->{payables}, - conv_date($form->{transdate})); - do_query($form, $dbh, $query, @values); + # add payables + $query = + qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey) | . + qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, | . + qq| (SELECT taxkey_id FROM chart WHERE accno = ?))|; + @values = ($form->{id}, $form->{AP_amounts}{payables}, $form->{payables}, + conv_date($form->{transdate}), $form->{AP_amounts}{payables}); + do_query($form, $dbh, $query, @values); + } # if there is no amount but a payment record a payable if ($form->{amount} == 0 && $form->{invtotal} == 0) { @@ -267,26 +250,26 @@ sub post_transaction { # add paid transactions for my $i (1 .. $form->{paidaccounts}) { + + if ($form->{"acc_trans_id_$i"} && $payments_only && (SL::DB::Default->get->payments_changeable == 0)) { + next; + } + if ($form->{"paid_$i"} != 0) { + my $project_id = conv_i($form->{"paid_project_id_$i"}); $exchangerate = 0; if ($form->{currency} eq $form->{defaultcurrency}) { $form->{"exchangerate_$i"} = 1; } else { - $exchangerate = - $form->check_exchangerate($myconfig, $form->{currency}, - $form->{"datepaid_$i"}, 'sell'); - - $form->{"exchangerate_$i"} = - ($exchangerate) - ? $exchangerate - : $form->parse_amount($myconfig, $form->{"exchangerate_$i"}); + $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'sell'); + $form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"}); } $form->{"AP_paid_$i"} =~ s/\"//g; # 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"}); @@ -300,21 +283,25 @@ sub post_transaction { 2); if ($form->{payables}) { $query = - qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate) | . - qq|VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), ?, ?)|; - @values = ($form->{id}, $form->{AP}{payables}, $amount, - conv_date($form->{"datepaid_$i"})); + 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, + conv_date($form->{"datepaid_$i"}), $project_id, + $form->{AP_payables}); do_query($form, $dbh, $query, @values); } $form->{payables} = $amount; # add payment + my $gldate = (conv_date($form->{"gldate_$i"}))? conv_date($form->{"gldate_$i"}) : conv_date($form->current_date($myconfig)); $query = - qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo) | . - qq|VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), ?, ?, ?, ?)|; - @values = ($form->{id}, $form->{AP}{"paid_$i"}, $form->{"paid_$i"}, - conv_date($form->{"datepaid_$i"}), $form->{"source_$i"}, - $form->{"memo_$i"}); + qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, 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_account_$i"}, $form->{"paid_$i"}, + conv_date($form->{"datepaid_$i"}), $gldate, $form->{"source_$i"}, + $form->{"memo_$i"}, $project_id, $form->{"AP_paid_account_$i"}); do_query($form, $dbh, $query, @values); # add exchange rate difference @@ -323,10 +310,12 @@ sub post_transaction { ($form->{"exchangerate_$i"} - 1), 2); if ($amount != 0) { $query = - qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, fx_transaction, cleared) | . - qq|VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), ?, ?, 't', 'f')|; - @values = ($form->{id}, $form->{AP}{"paid_$i"}, $amount, - conv_date($form->{"datepaid_$i"})); + 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_account_$i"}, $amount, + conv_date($form->{"datepaid_$i"}), $project_id, + $form->{"AP_paid_account_$i"}); do_query($form, $dbh, $query, @values); } @@ -338,11 +327,14 @@ sub post_transaction { if ($amount != 0) { $query = - qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, fx_transaction, cleared) | . - qq|VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), ?, ?, 't', 'f')|; + 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}, ($amount > 0) ? $form->{fxgain_accno} : $form->{fxloss_accno}, - $amount, conv_date($form->{"datepaid_$i"})); + $amount, conv_date($form->{"datepaid_$i"}), $project_id, + ($amount > 0) ? + $form->{fxgain_accno} : $form->{fxloss_accno}); do_query($form, $dbh, $query, @values); } @@ -355,8 +347,40 @@ sub post_transaction { } } - my $rc = $dbh->commit; - $dbh->disconnect; + if ($payments_only) { + $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})); + } + + IO->set_datepaid(table => 'ap', id => $form->{id}, dbh => $dbh); + + # safety check datev export + if ($::instance_conf->get_datev_check_on_ap_transaction) { + my $transdate = $::form->{transdate} ? DateTime->from_lxoffice($::form->{transdate}) : undef; + $transdate ||= DateTime->today; + + my $datev = SL::DATEV->new( + exporttype => DATEV_ET_BUCHUNGEN, + format => DATEV_FORMAT_KNE, + dbh => $dbh, + from => $transdate, + to => $transdate, + ); + + $datev->export; + + if ($datev->errors) { + $dbh->rollback; + die join "\n", $::locale->text('DATEV check returned errors:'), $datev->errors; + } + } + + 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(); @@ -366,18 +390,15 @@ sub post_transaction { sub delete_transaction { $main::lxdebug->enter_sub(); - my ($self, $myconfig, $form, $spool) = @_; + my ($self, $myconfig, $form) = @_; # connect to database my $dbh = $form->dbconnect_noauto($myconfig); + # acc_trans entries are deleted by database triggers. my $query = qq|DELETE FROM ap WHERE id = ?|; do_query($form, $dbh, $query, $form->{id}); - $query = qq|DELETE FROM acc_trans WHERE trans_id = ?|; - do_query($form, $dbh, $query, $form->{id}); - - # commit and redirect my $rc = $dbh->commit; $dbh->disconnect; @@ -392,20 +413,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; + my $where = ''; my @values; if ($form->{vendor_id}) { @@ -416,9 +449,12 @@ sub ap_transactions { push(@values, $form->like($form->{vendor})); } if ($form->{department}) { - my ($null, $department_id) = split /--/, $form->{department}; + # ähnlich wie commit 0bbfb33b6aa8e38bb6c81d1684ab7d08e5b5c5af abteilung + # wird so nicht mehr als zeichenkette zusammengebaut + # hätte zu ee9f9f9aa4c3b9d5d20ab10a45c12bcaa6aa78d0 auffallen können ;-) jan + #my ($null, $department_id) = split /--/, $form->{department}; $where .= " AND a.department_id = ?"; - push(@values, $department_id); + push(@values, $form->{department}); } if ($form->{invnumber}) { $where .= " AND a.invnumber ILIKE ?"; @@ -436,8 +472,12 @@ sub ap_transactions { $where .= qq|AND ((a.globalproject_id = ?) OR EXISTS | . qq| (SELECT * FROM invoice i | . - qq| WHERE i.project_id = ? AND i.trans_id = a.id))|; - push(@values, $form->{project_id}, $form->{project_id}); + qq| WHERE i.project_id = ? AND i.trans_id = a.id) | . + qq| OR EXISTS | . + qq| (SELECT * FROM acc_trans at | . + qq| WHERE at.project_id = ? AND at.trans_id = a.id)| . + qq| )|; + push(@values, $form->{project_id}, $form->{project_id}, $form->{project_id}); } if ($form->{transdatefrom}) { @@ -456,33 +496,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 $sortorder = join(', ', @a); + 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))) { - $sortorder = $form->{sort}; + 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"; + $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(); } @@ -507,6 +538,44 @@ sub get_transdate { $main::lxdebug->leave_sub(); } +sub _delete_payments { + $main::lxdebug->enter_sub(); + + my ($self, $form, $dbh) = @_; + + my @delete_acc_trans_ids; + + # Delete old payment entries from acc_trans. + my $query = + qq|SELECT acc_trans_id + FROM acc_trans + WHERE (trans_id = ?) AND fx_transaction + + UNION + + 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_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id})); + + $query = + 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.acc_trans_id + OFFSET 1|; + push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id})); + + 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); + } + + $main::lxdebug->leave_sub(); +} sub post_payment { $main::lxdebug->enter_sub(); @@ -516,149 +585,245 @@ sub post_payment { # connect to database, turn off autocommit my $dbh = $form->dbconnect_noauto($myconfig); - $form->{datepaid} = $form->{transdate}; + my (%payments, $old_form, $row, $item, $query, %keep_vars); - # total payments, don't move we need it here - for my $i (1 .. $form->{paidaccounts}) { - $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}); - $form->{paid} += $form->{"paid_$i"}; - $form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"}); + $old_form = save_form(); + + # Delete all entries in acc_trans from prior payments. + if (SL::DB::Default->get->payments_changeable != 0) { + $self->_delete_payments($form, $dbh); } - $form->{exchangerate} = - $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate}, - "buy"); + # Save the new payments the user made before cleaning up $form. + my $payments_re = '^datepaid_\d+$|^gldate_\d+$|^acc_trans_id_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^paid_project_id_\d+$|^AP_paid_\d+$|^paidaccounts$'; + map { $payments{$_} = $form->{$_} } grep m/$payments_re/, keys %{ $form }; - my (@values, $query); + # Clean up $form so that old content won't tamper the results. + %keep_vars = map { $_, 1 } qw(login password id); + map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form }; - my ($accno_ap) = split(/--/, $form->{APselected}); + # Retrieve the invoice from the database. + $form->create_links('AP', $myconfig, 'vendor', $dbh); - # record payments and offsetting AP - for my $i (1 .. $form->{paidaccounts}) { + # Restore the payment options from the user input. + map { $form->{$_} = $payments{$_} } keys %payments; - if ($form->{"paid_$i"} != 0) { - my ($accno) = split /--/, $form->{"AP_paid_$i"}; - $form->{"datepaid_$i"} = $form->{transdate} - unless ($form->{"datepaid_$i"}); - $form->{datepaid} = $form->{"datepaid_$i"}; + # Set up the content of $form in the way that AR::post_transaction() expects. - $exchangerate = 0; - if (($form->{currency} eq $form->{defaultcurrency}) || ($form->{defaultcurrency} eq "")) { - $form->{"exchangerate_$i"} = 1; - } else { - $exchangerate = - $form->check_exchangerate($myconfig, $form->{currency}, - $form->{"datepaid_$i"}, 'buy'); - - $form->{"exchangerate_$i"} = - ($exchangerate) - ? $exchangerate - : $form->parse_amount($myconfig, $form->{"exchangerate_$i"}); - } + $self->setup_form($form, 1); - # record AP - $amount = - $form->round_amount($form->{"paid_$i"} * $form->{"exchangerate"}, - 2) * -1; + $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate}); + $form->{defaultcurrency} = $form->get_default_currency($myconfig); + delete $form->{currency} unless $form->{defaultcurrency}; - $query = - qq|DELETE FROM acc_trans | . - qq|WHERE trans_id = ? | . - qq| AND chart_id = (SELECT c.id FROM chart c WHERE c.accno = ?) | . - qq| AND amount = ? AND transdate = ?|; - @values = ($form->{id}, $accno_ap, $amount, - conv_date($form->{"datepaid_$i"})); - do_query($form, $dbh, $query, @values); + # Get the AP accno. + $query = + qq|SELECT c.accno + 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.acc_trans_id + LIMIT 1|; - $query = - qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, project_id) | . - qq|VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), ?, ?, ?)|; - @values = ($form->{id}, $accno_ap, $amount, - conv_date($form->{"datepaid_$i"}), - conv_i($form->{"paid_project_id_$i"})); - do_query($form, $dbh, $query, @values); + ($form->{APselected}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id})); - $query = - qq|DELETE FROM acc_trans | . - qq|WHERE trans_id = ? | . - qq| AND chart_id = (SELECT c.id FROM chart c WHERE c.accno = ?) | . - qq| AND amount = ? AND transdate = ? AND source = ? AND memo = ?|; - @values = ($form->{id}, $accno, $form->{"paid_$i"}, - conv_date($form->{"datepaid_$i"}), $form->{"source_$i"}, - $form->{"memo_$i"}); - do_query($form, $dbh, $query, @values); + # Post the new payments. + $self->post_transaction($myconfig, $form, $dbh, 1); - $query = - qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, project_id) | . - qq|VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), ?, ?, ?, ?, ?)|; - @values = ($form->{id}, $accno, $form->{"paid_$i"}, - $form->{"datepaid_$i"}, - $form->{"source_$i"}, $form->{"memo_$i"}, - conv_i($form->{"paid_project_id_$i"})); - do_query($form, $dbh, $query, @values); + restore_form($old_form); - # gain/loss - $amount = - $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} * - $form->{"exchangerate_$i"}; - if ($amount > 0) { - $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } += - $amount; + my $rc = $dbh->commit(); + $dbh->disconnect(); + + $main::lxdebug->leave_sub(); + + return $rc; +} + +sub setup_form { + $main::lxdebug->enter_sub(); + + my ($self, $form, $for_post_payments) = @_; + + my ($exchangerate, $i, $j, $k, $key, $akey, $ref, $index, $taxamount, $totalamount, $totaltax, $totalwithholding, $withholdingrate, + $taxincluded, $tax, $diff); + + # forex + $form->{forex} = $form->{exchangerate}; + $exchangerate = ($form->{exchangerate}) ? $form->{exchangerate} : 1; + + foreach $key (keys %{ $form->{AP_links} }) { + foreach $ref (@{ $form->{AP_links}{$key} }) { + if ($key eq "AP_paid") { + $form->{"select$key"} .= "\n"; } else { - $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } += - $amount; + $form->{"select$key"} .= "\n"; } + } - $diff = 0; + $form->{$key} = $form->{"select$key"}; - # update exchange rate - if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) { - $form->update_exchangerate($dbh, $form->{currency}, - $form->{"datepaid_$i"}, - $form->{"exchangerate_$i"}, 0); + $j = 0; + $k = 0; + + # if there is a value we have an old entry + next unless $form->{acc_trans}{$key}; + + # do not use old entries for payments. They come from the form + # even if they are not changeable (then they are in hiddens) + next if $for_post_payments && $key eq "AP_paid"; + + for $i (1 .. scalar @{ $form->{acc_trans}{$key} }) { + + if ($key eq "AP_paid") { + $j++; + $form->{"AP_paid_$j"} = "$form->{acc_trans}{$key}->[$i-1]->{accno}--$form->{acc_trans}{$key}->[$i-1]->{description}"; + $form->{"acc_trans_id_$j"} = $form->{acc_trans}{$key}->[$i - 1]->{acc_trans_id}; + $form->{"paid_$j"} = $form->{acc_trans}{$key}->[$i - 1]->{amount}; + $form->{"datepaid_$j"} = $form->{acc_trans}{$key}->[$i - 1]->{transdate}; + $form->{"gldate_$j"} = $form->{acc_trans}{$key}->[$i - 1]->{gldate}; + $form->{"source_$j"} = $form->{acc_trans}{$key}->[$i - 1]->{source}; + $form->{"memo_$j"} = $form->{acc_trans}{$key}->[$i - 1]->{memo}; + + $form->{"exchangerate_$i"} = $form->{acc_trans}{$key}->[$i - 1]->{exchangerate}; + $form->{"forex_$j"} = $form->{"exchangerate_$i"}; + $form->{"AP_paid_$j"} = $form->{acc_trans}{$key}->[$i-1]->{accno}; + $form->{"paid_project_id_$j"} = $form->{acc_trans}{$key}->[$i - 1]->{project_id}; + $form->{paidaccounts}++; + + } else { + $akey = $key; + $akey =~ s/AP_//; + + if (($key eq "AP_tax") || ($key eq "AR_tax")) { + $form->{"${key}_$form->{acc_trans}{$key}->[$i-1]->{accno}"} = "$form->{acc_trans}{$key}->[$i-1]->{accno}--$form->{acc_trans}{$key}->[$i-1]->{description}"; + $form->{"${akey}_$form->{acc_trans}{$key}->[$i-1]->{accno}"} = $form->round_amount($form->{acc_trans}{$key}->[$i - 1]->{amount} / $exchangerate, 2); + + if ($form->{"$form->{acc_trans}{$key}->[$i-1]->{accno}_rate"} > 0) { + $totaltax += $form->{"${akey}_$form->{acc_trans}{$key}->[$i-1]->{accno}"}; + } else { + $totalwithholding += $form->{"${akey}_$form->{acc_trans}{$key}->[$i-1]->{accno}"}; + $withholdingrate += $form->{"$form->{acc_trans}{$key}->[$i-1]->{accno}_rate"}; + } + + $index = $form->{acc_trans}{$key}->[$i - 1]->{index}; + $form->{"tax_$index"} = $form->{acc_trans}{$key}->[$i - 1]->{amount} * -1; + $totaltax += $form->{"tax_$index"}; + + } else { + $k++; + $form->{"${akey}_$k"} = $form->round_amount($form->{acc_trans}{$key}->[$i - 1]->{amount} / $exchangerate, 2); + + if ($akey eq 'amount') { + $form->{rowcount}++; + $form->{"${akey}_$i"} *= -1; + $totalamount += $form->{"${akey}_$i"}; + $form->{taxrate} = $form->{acc_trans}{$key}->[$i - 1]->{rate}; + + $form->{"projectnumber_$k"} = "$form->{acc_trans}{$key}->[$i-1]->{projectnumber}"; + $form->{"oldprojectnumber_$k"} = $form->{"projectnumber_$k"}; + $form->{"project_id_$k"} = "$form->{acc_trans}{$key}->[$i-1]->{project_id}"; + } + + $form->{"${key}_$k"} = "$form->{acc_trans}{$key}->[$i-1]->{accno}--$form->{acc_trans}{$key}->[$i-1]->{description}"; + + my $q_description = quotemeta($form->{acc_trans}{$key}->[$i-1]->{description}); + $form->{"select${key}"} =~ + m/