X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FAP.pm;h=804c9b47826dbc46980572f8e4defab35757dec3;hb=357d134d265637fb37ed2cd47da2ce3ef44d015a;hp=d490739f72953f038314b7efb0298fc1a1f6ac53;hpb=4ee4670cb59cc318fd0d67ac19dd147dd63b87c7;p=kivitendo-erp.git diff --git a/SL/AP.pm b/SL/AP.pm index d490739f7..804c9b478 100644 --- a/SL/AP.pm +++ b/SL/AP.pm @@ -34,39 +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}) { @@ -87,35 +93,31 @@ 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->{"taxkey_$i"}, $NULL) = split /--/, $form->{"taxchart_$i"}; + for my $i (1 .. $form->{rowcount}) { + ($form->{"tax_id_$i"}, undef) = split /--/, $form->{"taxchart_$i"}; - $query = - qq| SELECT c.accno, t.rate FROM chart c, tax t where c.id=t.chart_id AND t.taxkey=$form->{"taxkey_$i"}|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - ($form->{AP_amounts}{"tax_$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"}; + 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|; + 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(); $sth->finish; - if (!$form->{"korrektur_$i"}) { - if ($form->{taxincluded} *= 1) { - $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 { - $form->{"tax_$i"} = $form->{"amount_$i"} * $form->{"taxrate_$i"}; - $form->{"tax_$i"} = - $form->round_amount($form->{"tax_$i"} * $form->{exchangerate}, 2); - $form->{netamount} += $form->{"amount_$i"}; - } + + my ($tax, $diff); + if ($form->{taxincluded} *= 1) { + $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 { + $form->{"tax_$i"} = $form->{"amount_$i"} * $form->{"taxrate_$i"}; + $form->{netamount} += $form->{"amount_$i"}; } $form->{total_tax} += $form->{"tax_$i"} * -1; } @@ -146,113 +148,101 @@ 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 = $form->{id}|; - - $dbh->do($query) || $form->dberror($query); - - } else { - my $uid = rand() . time; - - $uid .= $form->{login}; - - $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) - VALUES ('$uid', (SELECT e.id FROM employee e - WHERE e.login = '$form->{login}') )|; - $dbh->do($query) || $form->dberror($query); + my ($query, $sth, @values); - $query = qq|SELECT a.id FROM ap a - WHERE a.invnumber = '$uid'|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + if (!$payments_only) { + # if we have an id delete old records + if ($form->{id}) { - ($form->{id}) = $sth->fetchrow_array; - $sth->finish; + # delete detail records + $query = qq|DELETE FROM acc_trans WHERE trans_id = ?|; + do_query($form, $dbh, $query, $form->{id}); - } + } else { + my $uid = rand() . time; - $form->{invnumber} = $form->{id} unless $form->{invnumber}; - - # escape ' - map { $form->{$_} =~ s/\'/\'\'/g } qw(invnumber ordnumber notes); - - $form->{datepaid} = $form->{transdate} unless ($form->{datepaid}); - my $datepaid = ($form->{invpaid} != 0) ? qq|'$form->{datepaid}'| : 'NULL'; - - $query = qq|UPDATE ap SET - invnumber = '$form->{invnumber}', - transdate = '$form->{transdate}', - ordnumber = '$form->{ordnumber}', - vendor_id = $form->{vendor_id}, - taxincluded = '$form->{taxincluded}', - amount = $form->{invtotal}, - duedate = '$form->{duedate}', - paid = $form->{invpaid}, - datepaid = $datepaid, - netamount = $form->{netamount}, - curr = '$form->{currency}', - notes = '$form->{notes}', - department_id = $form->{department_id} - WHERE id = $form->{id} - |; - $dbh->do($query) || $form->dberror($query); + $uid .= $form->{login}; - # update exchangerate - if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) { - $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0, - $form->{exchangerate}); - } + $uid = substr($uid, 2, 75); - # add individual transactions - for $i (1 .. $form->{rowcount}) { - if ($form->{"amount_$i"} != 0) { - $project_id = 'NULL'; - if ("amount_$i" =~ /amount_/) { - if ($form->{"project_id_$i"} && $form->{"projectnumber_$i"}) { - $project_id = $form->{"project_id_$i"}; - } - } - if ("amount_$i" =~ /amount/) { - $taxkey = $form->{AP_amounts}{"amount_$i"}{taxkey}; - } + $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}); - # insert detail records in acc_trans - $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, - project_id, taxkey) - VALUES ($form->{id}, (SELECT c.id FROM chart c - WHERE c.accno = '$form->{AP_amounts}{"amount_$i"}'), - $form->{"amount_$i"}, '$form->{transdate}', $project_id, '$taxkey')|; - $dbh->do($query) || $form->dberror($query); + $query = qq|SELECT a.id FROM ap a + WHERE a.invnumber = ?|; + ($form->{id}) = selectrow_query($form, $dbh, $query, $uid); + } - if ($form->{"tax_$i"} != 0) { + $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"}); # insert detail records in acc_trans $query = - qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, - project_id, taxkey) - VALUES ($form->{id}, (SELECT c.id FROM chart c - WHERE c.accno = '$form->{AP_amounts}{"tax_$i"}'), - $form->{"tax_$i"}, '$form->{transdate}', $project_id, '$taxkey')|; - $dbh->do($query) || $form->dberror($query); - } + qq|INSERT INTO acc_trans | . + qq| (trans_id, chart_id, amount, transdate, project_id, taxkey, tax_id)| . + qq|VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), | . + qq| ?, ?, ?, ?, (SELECT tax_id FROM taxkeys WHERE taxkey_id= (SELECT taxkey_id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; + @values = ($form->{id}, $form->{AP_amounts}{"amount_$i"}, + $form->{"amount_$i"}, conv_date($form->{transdate}), + $project_id, $form->{"taxkey_$i"}, $form->{AP_amounts}{"amount_$i"}, conv_date($form->{transdate})); + 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, tax_id) | . + qq|VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), | . + qq| ?, ?, ?, ?, (SELECT tax_id FROM taxkeys WHERE taxkey_id= (SELECT taxkey_id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; + @values = ($form->{id}, $form->{AP_amounts}{"tax_$i"}, + $form->{"tax_$i"}, conv_date($form->{transdate}), + $project_id, $form->{"taxkey_$i"}, $form->{AP_amounts}{"tax_$i"}, conv_date($form->{transdate})); + do_query($form, $dbh, $query, @values); + } + } } - } - # add payables - $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, - project_id) - VALUES ($form->{id}, (SELECT c.id FROM chart c - WHERE c.accno = '$form->{AP_amounts}{payables}'), - $form->{payables}, '$form->{transdate}', $project_id)|; - $dbh->do($query) || $form->dberror($query); + # add payables + $query = + qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, tax_id) | . + qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, | . + qq| (SELECT taxkey_id FROM chart WHERE accno = ?),| . + qq| (SELECT tax_id FROM taxkeys WHERE taxkey_id= (SELECT taxkey_id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; + @values = ($form->{id}, $form->{AP_amounts}{payables}, $form->{payables}, + conv_date($form->{transdate}), $form->{AP_amounts}{payables}, $form->{AP_amounts}{payables}, conv_date($form->{transdate})); + do_query($form, $dbh, $query, @values); + } # if there is no amount but a payment record a payable if ($form->{amount} == 0 && $form->{invtotal} == 0) { @@ -261,26 +251,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"}); @@ -293,59 +283,67 @@ sub post_transaction { $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} * -1, 2); if ($form->{payables}) { - $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, - transdate) - VALUES ($form->{id}, - (SELECT c.id FROM chart c - WHERE c.accno = '$form->{AP}{payables}'), - $amount, '$form->{"datepaid_$i"}')|; - $dbh->do($query) || $form->dberror($query); + $query = + qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, project_id, taxkey, tax_id) | . + qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, | . + qq| (SELECT taxkey_id FROM chart WHERE accno = ?),| . + qq| (SELECT tax_id FROM taxkeys WHERE taxkey_id= (SELECT taxkey_id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; + @values = ($form->{id}, $form->{AP_payables}, $amount, + conv_date($form->{"datepaid_$i"}), $project_id, + $form->{AP_payables}, $form->{AP_payables}, conv_date($form->{"datepaid_$i"})); + do_query($form, $dbh, $query, @values); } $form->{payables} = $amount; - $form->{"memo_$i"} =~ s/\'/\'\'/g; - # add payment - $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, - transdate, source, memo) - VALUES ($form->{id}, - (SELECT c.id FROM chart c - WHERE c.accno = '$form->{AP}{"paid_$i"}'), - $form->{"paid_$i"}, '$form->{"datepaid_$i"}', - '$form->{"source_$i"}', '$form->{"memo_$i"}')|; - $dbh->do($query) || $form->dberror($query); + 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, gldate, source, memo, project_id, taxkey, tax_id) | . + qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?, ?, | . + qq| (SELECT taxkey_id FROM chart WHERE accno = ?), | . + qq| (SELECT tax_id FROM taxkeys WHERE taxkey_id= (SELECT taxkey_id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; + @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"}, + $form->{"AP_paid_account_$i"}, conv_date($form->{"datepaid_$i"})); + do_query($form, $dbh, $query, @values); # add exchange rate difference $amount = - $form->round_amount( - $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1), - 2); + $form->round_amount($form->{"paid_$i"} * + ($form->{"exchangerate_$i"} - 1), 2); if ($amount != 0) { - $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, - transdate, fx_transaction, cleared) - VALUES ($form->{id}, - (SELECT c.id FROM chart c - WHERE c.accno = '$form->{AP}{"paid_$i"}'), - $amount, '$form->{"datepaid_$i"}', '1', '0')|; - - $dbh->do($query) || $form->dberror($query); + $query = + qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, fx_transaction, cleared, project_id, taxkey, tax_id) | . + qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 't', 'f', ?, | . + qq| (SELECT taxkey_id FROM chart WHERE accno = ?), | . + qq| (SELECT tax_id FROM taxkeys WHERE taxkey_id= (SELECT taxkey_id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; + @values = ($form->{id}, $form->{"AP_paid_account_$i"}, $amount, + conv_date($form->{"datepaid_$i"}), $project_id, + $form->{"AP_paid_account_$i"}, + $form->{"AP_paid_account_$i"}, conv_date($form->{"datepaid_$i"})); + do_query($form, $dbh, $query, @values); } # exchangerate gain/loss $amount = - $form->round_amount( - $form->{"paid_$i"} * - ($form->{exchangerate} - $form->{"exchangerate_$i"}), - 2); + $form->round_amount($form->{"paid_$i"} * + ($form->{exchangerate} - + $form->{"exchangerate_$i"}), 2); if ($amount != 0) { - $accno = ($amount > 0) ? $form->{fxgain_accno} : $form->{fxloss_accno}; - $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, - transdate, fx_transaction, cleared) - VALUES ($form->{id}, (SELECT c.id FROM chart c - WHERE c.accno = '$accno'), - $amount, '$form->{"datepaid_$i"}', '1', '0')|; - $dbh->do($query) || $form->dberror($query); + $query = + qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, fx_transaction, cleared, project_id, taxkey, tax_id) | . + qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 't', 'f', ?, | . + qq| (SELECT taxkey_id FROM chart WHERE accno = ?)| . + qq| (SELECT tax_id FROM taxkeys WHERE taxkey_id= (SELECT taxkey_id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; + @values = ($form->{id}, ($amount > 0) ? + $form->{fxgain_accno} : $form->{fxloss_accno}, + $amount, conv_date($form->{"datepaid_$i"}), $project_id, + ($amount > 0) ? + $form->{fxgain_accno} : $form->{fxloss_accno}, + ($amount > 0) ? $form->{fxgain_accno} : $form->{fxloss_accno}, conv_date($form->{"datepaid_$i"})); + do_query($form, $dbh, $query, @values); } # update exchange rate record @@ -357,8 +355,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(); @@ -368,16 +398,16 @@ 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); - my $query = qq|DELETE FROM ap WHERE id = $form->{id}|; - $dbh->do($query) || $form->dberror($query); + my $query = qq|DELETE FROM ap WHERE id = ?|; + do_query($form, $dbh, $query, $form->{id}); - $query = qq|DELETE FROM acc_trans WHERE trans_id = $form->{id}|; - $dbh->do($query) || $form->dberror($query); + $query = qq|DELETE FROM acc_trans WHERE trans_id = ?|; + do_query($form, $dbh, $query, $form->{id}); # commit and redirect my $rc = $dbh->commit; @@ -394,46 +424,81 @@ sub ap_transactions { my ($self, $myconfig, $form) = @_; # connect to database - my $dbh = $form->dbconnect($myconfig); - - my $query = qq|SELECT a.id, a.invnumber, a.transdate, a.duedate, - a.amount, a.paid, a.ordnumber, v.name, a.invoice, - a.netamount, a.datepaid, a.notes, e.name AS employee - FROM ap a - JOIN vendor v ON (a.vendor_id = v.id) - LEFT JOIN employee e ON (a.employee_id = e.id)|; - - my $where = "1 = 1"; + 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, a.storno, a.storno_id, | . + qq| pr.projectnumber AS globalprojectnumber, | . + 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 tax_zones tz ON (tz.id = v.taxzone_id)| . + qq|LEFT JOIN payment_terms pt ON (pt.id = v.payment_id)|; + + my $where = ''; + my @values; if ($form->{vendor_id}) { - $where .= " AND a.vendor_id = $form->{vendor_id}"; - } else { - if ($form->{vendor}) { - my $vendor = $form->like(lc $form->{vendor}); - $where .= " AND lower(v.name) LIKE '$vendor'"; - } + $where .= " AND a.vendor_id = ?"; + push(@values, $form->{vendor_id}); + } elsif ($form->{vendor}) { + $where .= " AND v.name ILIKE ?"; + push(@values, $form->like($form->{vendor})); } if ($form->{department}) { - my ($null, $department_id) = split /--/, $form->{department}; - $where .= " AND a.department_id = $department_id"; + # ä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, $form->{department}); } if ($form->{invnumber}) { - my $invnumber = $form->like(lc $form->{invnumber}); - $where .= " AND lower(a.invnumber) LIKE '$invnumber'"; + $where .= " AND a.invnumber ILIKE ?"; + push(@values, $form->like($form->{invnumber})); } if ($form->{ordnumber}) { - my $ordnumber = $form->like(lc $form->{ordnumber}); - $where .= " AND lower(a.ordnumber) LIKE '$ordnumber'"; + $where .= " AND a.ordnumber ILIKE ?"; + push(@values, $form->like($form->{ordnumber})); } if ($form->{notes}) { - my $notes = $form->like(lc $form->{notes}); - $where .= " AND lower(a.notes) LIKE '$notes'"; + $where .= " AND lower(a.notes) LIKE ?"; + push(@values, $form->like($form->{notes})); + } + if ($form->{project_id}) { + $where .= + qq|AND ((a.globalproject_id = ?) OR EXISTS | . + qq| (SELECT * FROM invoice i | . + 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}); } - $where .= " AND a.transdate >= '$form->{transdatefrom}'" - if $form->{transdatefrom}; - $where .= " AND a.transdate <= '$form->{transdateto}'" - if $form->{transdateto}; + if ($form->{transdatefrom}) { + $where .= " AND a.transdate >= ?"; + push(@values, $form->{transdatefrom}); + } + if ($form->{transdateto}) { + $where .= " AND a.transdate <= ?"; + push(@values, $form->{transdateto}); + } if ($form->{open} || $form->{closed}) { unless ($form->{open} && $form->{closed}) { $where .= " AND a.amount <> a.paid" if ($form->{open}); @@ -441,26 +506,336 @@ sub ap_transactions { } } - my @a = (transdate, invnumber, name); - push @a, "employee" if $self->{l_employee}; - my $sortorder = join ', ', $form->sort_columns(@a); - $sortorder = $form->{sort} if $form->{sort}; - - $query .= "WHERE $where - ORDER by $sortorder"; + if ($where) { + substr($where, 0, 4, " WHERE "); + $query .= $where; + } - my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + 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); - while (my $ap = $sth->fetchrow_hashref(NAME_lc)) { - push @{ $form->{AP} }, $ap; + 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"; } - $sth->finish; + $query .= " ORDER BY $sortorder"; + + my @result = selectall_hashref_query($form, $dbh, $query, @values); + + $form->{AP} = [ @result ]; + + $main::lxdebug->leave_sub(); +} + +sub get_transdate { + $main::lxdebug->enter_sub(); + + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + my $query = + "SELECT COALESCE(" . + " (SELECT transdate FROM ap WHERE id = " . + " (SELECT MAX(id) FROM ap) LIMIT 1), " . + " current_date)"; + ($form->{transdate}) = $dbh->selectrow_array($query); + $dbh->disconnect; $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(); + + my ($self, $myconfig, $form, $locale) = @_; + + # connect to database, turn off autocommit + my $dbh = $form->dbconnect_noauto($myconfig); + + my (%payments, $old_form, $row, $item, $query, %keep_vars); + + $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); + } + + # 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 }; + + # 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 }; + + # Retrieve the invoice from the database. + $form->create_links('AP', $myconfig, 'vendor', $dbh); + + # Restore the payment options from the user input. + map { $form->{$_} = $payments{$_} } keys %payments; + + # Set up the content of $form in the way that AR::post_transaction() expects. + + $self->setup_form($form, 1); + + $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 = + 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|; + + ($form->{APselected}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id})); + + # Post the new payments. + $self->post_transaction($myconfig, $form, $dbh, 1); + + restore_form($old_form); + + 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->{"select$key"} .= "\n"; + } + } + + $form->{$key} = $form->{"select$key"}; + + $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/