Revision 88d162cc
Von Martin Helmling martin.helmling@octosoft.eu vor mehr als 7 Jahren hinzugefügt
| SL/Controller/BankTransaction.pm | ||
|---|---|---|
| 23 | 23 |
use SL::DB::Tax; |
| 24 | 24 |
use SL::DB::Draft; |
| 25 | 25 |
use SL::DB::BankAccount; |
| 26 |
use SL::DB::SepaExportItem; |
|
| 26 | 27 |
use SL::DBUtils qw(like); |
| 27 | 28 |
use SL::Presenter; |
| 28 | 29 |
|
| ... | ... | |
| 97 | 98 |
@where |
| 98 | 99 |
], |
| 99 | 100 |
); |
| 101 |
$main::lxdebug->message(LXDebug->DEBUG2(),"count bt=".scalar(@{$bank_transactions}." bank_account=".$bank_account->id." chart=".$bank_account->chart_id));
|
|
| 100 | 102 |
|
| 101 |
my $all_open_ar_invoices = SL::DB::Manager::Invoice ->get_all(where => [amount => { gt => \'paid' }], with_objects => 'customer');
|
|
| 102 |
my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => [amount => { gt => \'paid' }], with_objects => 'vendor');
|
|
| 103 |
my $all_open_ar_invoices = SL::DB::Manager::Invoice ->get_all(where => [amount => { gt => \'paid' }], with_objects => ['customer','payment_terms']);
|
|
| 104 |
my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => [amount => { gt => \'paid' }], with_objects => ['vendor' ,'payment_terms']);
|
|
| 105 |
my $all_open_sepa_export_items = SL::DB::Manager::SepaExportItem->get_all(where => [chart_id => $bank_account->chart_id , |
|
| 106 |
'sepa_export.executed' => 0, 'sepa_export.closed' => 0 ], with_objects => ['sepa_export']); |
|
| 107 |
$main::lxdebug->message(LXDebug->DEBUG2(),"count sepaexport=".scalar(@{$all_open_sepa_export_items}));
|
|
| 103 | 108 |
|
| 104 | 109 |
my @all_open_invoices; |
| 105 | 110 |
# filter out invoices with less than 1 cent outstanding |
| 106 | 111 |
push @all_open_invoices, grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ar_invoices };
|
| 107 | 112 |
push @all_open_invoices, grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
|
| 113 |
$main::lxdebug->message(LXDebug->DEBUG2(),"bank_account=".$::form->{filter}{bank_account}." invoices: ".scalar(@{ $all_open_ar_invoices }).
|
|
| 114 |
" + ".scalar(@{ $all_open_ap_invoices })." transactions=".scalar(@{ $bank_transactions }));
|
|
| 115 |
|
|
| 116 |
my @all_sepa_invoices; |
|
| 117 |
my @all_non_sepa_invoices; |
|
| 118 |
my %sepa_exports; |
|
| 119 |
# first collect sepa export items to open invoices |
|
| 120 |
foreach my $open_invoice (@all_open_invoices){
|
|
| 121 |
# my @items = grep { $_->ap_id == $open_invoice->id || $_->ar_id == $open_invoice->id } @{$all_open_sepa_export_items};
|
|
| 122 |
$open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount,2);
|
|
| 123 |
$open_invoice->{skonto_type} = 'without_skonto';
|
|
| 124 |
foreach ( @{$all_open_sepa_export_items}) {
|
|
| 125 |
if ( $_->ap_id == $open_invoice->id || $_->ar_id == $open_invoice->id ) {
|
|
| 126 |
#$main::lxdebug->message(LXDebug->DEBUG2(),"exitem=".$_->id." for invoice ".$open_invoice->id); |
|
| 127 |
$open_invoice->{sepa_export_item} = $_ ;
|
|
| 128 |
$open_invoice->{skonto_type} = $_->payment_type;
|
|
| 129 |
$sepa_exports{$_->sepa_export_id} ||= { count => 0, is_ar => 0, amount => 0, proposed => 0, invoices => [], item => $_ };
|
|
| 130 |
$sepa_exports{$_->sepa_export_id}->{count}++ ;
|
|
| 131 |
$sepa_exports{$_->sepa_export_id}->{is_ar}++ if $_->ar_id == $open_invoice->id;
|
|
| 132 |
$sepa_exports{$_->sepa_export_id}->{amount} += $_->amount;
|
|
| 133 |
push ( @{ $sepa_exports{$_->sepa_export_id}->{invoices}} , $open_invoice );
|
|
| 134 |
#$main::lxdebug->message(LXDebug->DEBUG2(),"amount for export id ".$_->sepa_export_id." = ". |
|
| 135 |
# $sepa_exports{$_->sepa_export_id}->{amount}." count = ".
|
|
| 136 |
# $sepa_exports{$_->sepa_export_id}->{count}." is_ar = ".
|
|
| 137 |
# $sepa_exports{$_->sepa_export_id}->{is_ar} );
|
|
| 138 |
push @all_sepa_invoices , $open_invoice; |
|
| 139 |
} |
|
| 140 |
} |
|
| 141 |
push @all_non_sepa_invoices , $open_invoice if ! $open_invoice->{sepa_export_item};
|
|
| 142 |
} |
|
| 108 | 143 |
|
| 109 | 144 |
# try to match each bank_transaction with each of the possible open invoices |
| 110 | 145 |
# by awarding points |
| 146 |
@all_open_invoices = @all_non_sepa_invoices; |
|
| 147 |
my @proposals; |
|
| 111 | 148 |
|
| 112 | 149 |
foreach my $bt (@{ $bank_transactions }) {
|
| 113 |
next unless $bt->{remote_name}; # bank has no name, usually fees, use create invoice to assign
|
|
| 150 |
## 5 Stellen hinter dem Komma auf 2 Stellen reduzieren |
|
| 151 |
$bt->amount($bt->amount*1); |
|
| 152 |
$bt->invoice_amount($bt->invoice_amount*1); |
|
| 153 |
$main::lxdebug->message(LXDebug->DEBUG2(),"BT ".$bt->id." amount=".$bt->amount." invoice_amount=".$bt->invoice_amount." remote=". $bt->{remote_name});
|
|
| 154 |
|
|
| 155 |
$bt->{proposals} = [];
|
|
| 114 | 156 |
|
| 115 | 157 |
$bt->{remote_name} .= $bt->{remote_name_1} if $bt->{remote_name_1};
|
| 116 | 158 |
|
| 159 |
if ( $self->is_collective_transaction($bt) ) {
|
|
| 160 |
foreach ( keys %sepa_exports) {
|
|
| 161 |
my $factor = ($sepa_exports{$_}->{is_ar}>0?1:-1);
|
|
| 162 |
#$main::lxdebug->message(LXDebug->DEBUG2(),"Exp ID=".$_." factor=".$factor." compare sum amount ".($sepa_exports{$_}->{amount} *1) ." == ".($bt->amount * $factor));
|
|
| 163 |
if ( $bt->transactioncode eq '191' && ($sepa_exports{$_}->{amount} * 1) eq ($bt->amount * $factor) ) {
|
|
| 164 |
## jupp |
|
| 165 |
$bt->{proposals} = $sepa_exports{$_}->{invoices} ;
|
|
| 166 |
$sepa_exports{$_}->{proposed}=1;
|
|
| 167 |
#$main::lxdebug->message(LXDebug->DEBUG2(),"has ".scalar($bt->{proposals})." invoices");
|
|
| 168 |
push(@proposals, $bt); |
|
| 169 |
next; |
|
| 170 |
} |
|
| 171 |
} |
|
| 172 |
} |
|
| 173 |
next unless $bt->{remote_name}; # bank has no name, usually fees, use create invoice to assign
|
|
| 174 |
|
|
| 175 |
foreach ( keys %sepa_exports) {
|
|
| 176 |
my $factor = ($sepa_exports{$_}->{is_ar}>0?1:-1);
|
|
| 177 |
#$main::lxdebug->message(LXDebug->DEBUG2(),"exp count=".$sepa_exports{$_}->{count}." factor=".$factor." proposed=".$sepa_exports{$_}->{proposed});
|
|
| 178 |
if ( $sepa_exports{$_}->{count} == 1 ) {
|
|
| 179 |
my $oinvoice = @{ $sepa_exports{$_}->{invoices}}[0];
|
|
| 180 |
my $eitem = $sepa_exports{$_}->{item};
|
|
| 181 |
$eitem->amount($eitem->amount*1); |
|
| 182 |
#$main::lxdebug->message(LXDebug->DEBUG2(),"remote account '".$bt->{remote_account_number}."' bt_amount=". ($bt->amount * $factor));
|
|
| 183 |
#$main::lxdebug->message(LXDebug->DEBUG2(),"compare with '".$eitem->vc_iban."' amount=".$eitem->amount); |
|
| 184 |
if ( $bt->{remote_account_number} eq $eitem->vc_iban && $eitem->amount eq ($bt->amount * $factor)) {
|
|
| 185 |
## jupp |
|
| 186 |
$bt->{proposals} = $sepa_exports{$_}->{invoices} ;
|
|
| 187 |
#$main::lxdebug->message(LXDebug->DEBUG2(),"found invoice"); |
|
| 188 |
$sepa_exports{$_}->{proposed}=1;
|
|
| 189 |
push(@proposals, $bt); |
|
| 190 |
next; |
|
| 191 |
} |
|
| 192 |
} |
|
| 193 |
} |
|
| 194 |
|
|
| 117 | 195 |
# try to match the current $bt to each of the open_invoices, saving the |
| 118 | 196 |
# results of get_agreement_with_invoice in $open_invoice->{agreement} and
|
| 119 | 197 |
# $open_invoice->{rule_matches}.
|
| ... | ... | |
| 125 | 203 |
|
| 126 | 204 |
foreach my $open_invoice (@all_open_invoices){
|
| 127 | 205 |
($open_invoice->{agreement}, $open_invoice->{rule_matches}) = $bt->get_agreement_with_invoice($open_invoice);
|
| 206 |
# $main::lxdebug->message(LXDebug->DEBUG2(),"agreement=".$open_invoice->{agreement}." rules matches=".$open_invoice->{rule_matches});
|
|
| 128 | 207 |
}; |
| 129 | 208 |
|
| 130 |
$bt->{proposals} = [];
|
|
| 131 |
|
|
| 132 | 209 |
my $agreement = 15; |
| 133 | 210 |
my $min_agreement = 3; # suggestions must have at least this score |
| 134 | 211 |
|
| ... | ... | |
| 153 | 230 |
# * there must be only one exact match |
| 154 | 231 |
# * depending on whether sales or purchase the amount has to have the correct sign (so Gutschriften don't work?) |
| 155 | 232 |
my $proposal_threshold = 5; |
| 156 |
my @proposals = grep {
|
|
| 233 |
my @otherproposals = grep {
|
|
| 157 | 234 |
($_->{agreement} >= $proposal_threshold)
|
| 158 | 235 |
&& (1 == scalar @{ $_->{proposals} })
|
| 159 | 236 |
&& (@{ $_->{proposals} }[0]->is_sales ? abs(@{ $_->{proposals} }[0]->amount - $_->amount) < 0.01
|
| 160 | 237 |
: abs(@{ $_->{proposals} }[0]->amount + $_->amount) < 0.01)
|
| 161 | 238 |
} @{ $bank_transactions };
|
| 162 | 239 |
|
| 240 |
push ( @proposals, @otherproposals); |
|
| 241 |
|
|
| 163 | 242 |
# sort bank transaction proposals by quality (score) of proposal |
| 164 | 243 |
$bank_transactions = [ sort { $a->{agreement} <=> $b->{agreement} } @{ $bank_transactions } ] if $::form->{sort_by} eq 'proposal' and $::form->{sort_dir} == 1;
|
| 165 | 244 |
$bank_transactions = [ sort { $b->{agreement} <=> $a->{agreement} } @{ $bank_transactions } ] if $::form->{sort_by} eq 'proposal' and $::form->{sort_dir} == 0;
|
| ... | ... | |
| 169 | 248 |
title => t8('Bank transactions MT940'),
|
| 170 | 249 |
BANK_TRANSACTIONS => $bank_transactions, |
| 171 | 250 |
PROPOSALS => \@proposals, |
| 172 |
bank_account => $bank_account ); |
|
| 251 |
bank_account => $bank_account, |
|
| 252 |
ui_tab => scalar(@proposals) > 0?1:0, |
|
| 253 |
); |
|
| 173 | 254 |
} |
| 174 | 255 |
|
| 175 | 256 |
sub action_assign_invoice {
|
| ... | ... | |
| 373 | 454 |
); |
| 374 | 455 |
} |
| 375 | 456 |
|
| 376 |
sub action_save_invoices {
|
|
| 457 |
sub save_invoices {
|
|
| 377 | 458 |
my ($self) = @_; |
| 378 | 459 |
|
| 379 |
my $invoice_hash = delete $::form->{invoice_ids}; # each key (the bt line with a bt_id) contains an array of invoice_ids
|
|
| 460 |
return 0 if !$::form->{invoice_ids};
|
|
| 461 |
|
|
| 462 |
my %invoice_hash = %{ delete $::form->{invoice_ids} }; # each key (the bt line with a bt_id) contains an array of invoice_ids
|
|
| 380 | 463 |
|
| 381 | 464 |
# e.g. three partial payments with bt_ids 54, 55 and 56 for invoice with id 74: |
| 382 | 465 |
# $invoice_hash = {
|
| ... | ... | |
| 403 | 486 |
|
| 404 | 487 |
$self->problems([]); |
| 405 | 488 |
|
| 406 |
while ( my ($bank_transaction_id, $invoice_ids) = each(%$invoice_hash) ) {
|
|
| 407 |
push @{ $self->problems }, $self->save_single_bank_transaction(
|
|
| 408 |
bank_transaction_id => $bank_transaction_id, |
|
| 409 |
invoice_ids => $invoice_ids, |
|
| 410 |
); |
|
| 489 |
my $count = 0; |
|
| 490 |
|
|
| 491 |
if ( $::form->{proposal_ids} ) {
|
|
| 492 |
foreach (@{ $::form->{proposal_ids} }) {
|
|
| 493 |
my $bank_transaction_id = $_; |
|
| 494 |
my $invoice_ids = $invoice_hash{$_};
|
|
| 495 |
push @{ $self->problems }, $self->save_single_bank_transaction(
|
|
| 496 |
bank_transaction_id => $bank_transaction_id, |
|
| 497 |
invoice_ids => $invoice_ids, |
|
| 498 |
); |
|
| 499 |
$count += scalar( @{$invoice_ids} );
|
|
| 500 |
} |
|
| 501 |
} else {
|
|
| 502 |
while ( my ($bank_transaction_id, $invoice_ids) = each(%invoice_hash) ) {
|
|
| 503 |
push @{ $self->problems }, $self->save_single_bank_transaction(
|
|
| 504 |
bank_transaction_id => $bank_transaction_id, |
|
| 505 |
invoice_ids => $invoice_ids, |
|
| 506 |
); |
|
| 507 |
$count += scalar( @{$invoice_ids} );
|
|
| 508 |
} |
|
| 411 | 509 |
} |
| 510 |
return $count; |
|
| 511 |
} |
|
| 512 |
|
|
| 513 |
sub action_save_invoices {
|
|
| 514 |
my ($self) = @_; |
|
| 515 |
my $count = $self->save_invoices(); |
|
| 516 |
|
|
| 517 |
flash('ok', t8('#1 invoice(s) saved.', $count));
|
|
| 412 | 518 |
|
| 413 | 519 |
$self->action_list(); |
| 414 | 520 |
} |
| 415 | 521 |
|
| 522 |
sub action_save_proposals {
|
|
| 523 |
my ($self) = @_; |
|
| 524 |
if ( $::form->{proposal_ids} ) {
|
|
| 525 |
my $propcount = scalar(@{ $::form->{proposal_ids} });
|
|
| 526 |
if ( $propcount > 0 ) {
|
|
| 527 |
my $count = $self->save_invoices(); |
|
| 528 |
|
|
| 529 |
flash('ok', t8('#1 proposal(s) with #2 invoice(s) saved.', $propcount, $count));
|
|
| 530 |
} |
|
| 531 |
} |
|
| 532 |
$self->action_list(); |
|
| 533 |
|
|
| 534 |
} |
|
| 535 |
|
|
| 536 |
sub is_collective_transaction {
|
|
| 537 |
my ($self, $bt) = @_; |
|
| 538 |
return $bt->transactioncode eq "191"; |
|
| 539 |
} |
|
| 540 |
|
|
| 416 | 541 |
sub save_single_bank_transaction {
|
| 417 | 542 |
my ($self, %params) = @_; |
| 418 | 543 |
|
| ... | ... | |
| 509 | 634 |
|
| 510 | 635 |
# pay invoice or go to the next bank transaction if the amount is not sufficiently high |
| 511 | 636 |
if ($invoice->open_amount <= $amount_of_transaction && $n_invoices < $max_invoices) {
|
| 637 |
my $open_amount = ($payment_type eq 'with_skonto_pt'?$invoice->amount_less_skonto:$invoice->open_amount); |
|
| 512 | 638 |
# first calculate new bank transaction amount ... |
| 513 | 639 |
if ($invoice->is_sales) {
|
| 514 |
$amount_of_transaction -= $sign * $invoice->open_amount;
|
|
| 515 |
$bank_transaction->invoice_amount($bank_transaction->invoice_amount + $invoice->open_amount);
|
|
| 640 |
$amount_of_transaction -= $sign * $open_amount; |
|
| 641 |
$bank_transaction->invoice_amount($bank_transaction->invoice_amount + $open_amount); |
|
| 516 | 642 |
} else {
|
| 517 |
$amount_of_transaction += $sign * $invoice->open_amount;
|
|
| 518 |
$bank_transaction->invoice_amount($bank_transaction->invoice_amount - $invoice->open_amount);
|
|
| 643 |
$amount_of_transaction += $sign * $open_amount; |
|
| 644 |
$bank_transaction->invoice_amount($bank_transaction->invoice_amount - $open_amount); |
|
| 519 | 645 |
} |
| 520 | 646 |
# ... and then pay the invoice |
| 521 | 647 |
$invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id, |
| 522 | 648 |
trans_id => $invoice->id, |
| 523 |
amount => $invoice->open_amount,
|
|
| 649 |
amount => $open_amount, |
|
| 524 | 650 |
payment_type => $payment_type, |
| 525 | 651 |
transdate => $bank_transaction->transdate->to_kivitendo); |
| 526 | 652 |
} else { # use the whole amount of the bank transaction for the invoice, overpay the invoice if necessary
|
| ... | ... | |
| 590 | 716 |
return grep { $_ } ($error, @warnings);
|
| 591 | 717 |
} |
| 592 | 718 |
|
| 593 |
sub action_save_proposals {
|
|
| 594 |
my ($self) = @_; |
|
| 595 |
|
|
| 596 |
foreach my $bt_id (@{ $::form->{proposal_ids} }) {
|
|
| 597 |
my $bt = SL::DB::Manager::BankTransaction->find_by(id => $bt_id); |
|
| 598 |
|
|
| 599 |
my $arap = SL::DB::Manager::Invoice->find_by(id => $::form->{"proposed_invoice_$bt_id"});
|
|
| 600 |
$arap = SL::DB::Manager::PurchaseInvoice->find_by(id => $::form->{"proposed_invoice_$bt_id"}) if not defined $arap;
|
|
| 601 |
|
|
| 602 |
# check for existing record_link for that $bt and $arap |
|
| 603 |
# do this before any changes to $bt are made |
|
| 604 |
die t8("Bank transaction with id #1 has already been linked to #2.", $bt->id, $arap->displayable_name)
|
|
| 605 |
if _existing_record_link($bt, $arap); |
|
| 606 |
|
|
| 607 |
#mark bt as booked |
|
| 608 |
$bt->invoice_amount($bt->amount); |
|
| 609 |
$bt->save; |
|
| 610 |
|
|
| 611 |
#pay invoice |
|
| 612 |
$arap->pay_invoice(chart_id => $bt->local_bank_account->chart_id, |
|
| 613 |
trans_id => $arap->id, |
|
| 614 |
amount => $arap->amount, |
|
| 615 |
transdate => $bt->transdate->to_kivitendo); |
|
| 616 |
$arap->save; |
|
| 617 |
|
|
| 618 |
#create record link |
|
| 619 |
my @props = ( |
|
| 620 |
from_table => 'bank_transactions', |
|
| 621 |
from_id => $bt_id, |
|
| 622 |
to_table => $arap->is_sales ? 'ar' : 'ap', |
|
| 623 |
to_id => $arap->id, |
|
| 624 |
); |
|
| 625 |
|
|
| 626 |
SL::DB::RecordLink->new(@props)->save; |
|
| 627 |
|
|
| 628 |
# code duplicated in action_save_invoices! |
|
| 629 |
# "close" a sepa_export_item if it exists |
|
| 630 |
# currently only works, if there is only exactly one open sepa_export_item |
|
| 631 |
if ( my $seis = $arap->find_sepa_export_items({ executed => 0 }) ) {
|
|
| 632 |
if ( scalar @$seis == 1 ) {
|
|
| 633 |
# moved the execution and the check for sepa_export into a method, |
|
| 634 |
# this isn't part of a transaction, though |
|
| 635 |
$seis->[0]->set_executed if $arap->id == $seis->[0]->arap_id; |
|
| 636 |
} |
|
| 637 |
} |
|
| 638 |
} |
|
| 639 |
|
|
| 640 |
flash('ok', t8('#1 proposal(s) saved.', scalar @{ $::form->{proposal_ids} }));
|
|
| 641 |
|
|
| 642 |
$self->action_list(); |
|
| 643 |
} |
|
| 644 |
|
|
| 645 | 719 |
# |
| 646 | 720 |
# filters |
| 647 | 721 |
# |
| SL/Controller/CsvImport/BankTransaction.pm | ||
|---|---|---|
| 83 | 83 |
}; |
| 84 | 84 |
} |
| 85 | 85 |
|
| 86 |
sub _displayable_columns {
|
|
| 87 |
( |
|
| 88 |
{ name => 'local_bank_code', description => $::locale->text('Own bank code') },
|
|
| 89 |
{ name => 'local_account_number', description => $::locale->text('Own bank account number or IBAN') },
|
|
| 90 |
{ name => 'local_bank_account_id', description => $::locale->text('ID of own bank account') },
|
|
| 91 |
{ name => 'remote_bank_code', description => $::locale->text('Bank code of the goal/source') },
|
|
| 92 |
{ name => 'remote_account_number', description => $::locale->text('Account number of the goal/source') },
|
|
| 93 |
{ name => 'transdate', description => $::locale->text('Date of transaction') },
|
|
| 94 |
{ name => 'valutadate', description => $::locale->text('Valuta date') },
|
|
| 95 |
{ name => 'amount', description => $::locale->text('Amount') },
|
|
| 96 |
{ name => 'currency', description => $::locale->text('Currency') },
|
|
| 97 |
{ name => 'currency_id', description => $::locale->text('Currency (database ID)') },
|
|
| 98 |
{ name => 'remote_name', description => $::locale->text('Name of the goal/source (if field names remote_name and remote_name_1 exist they will be combined into field "remote_name")') },
|
|
| 99 |
{ name => 'purpose', description => $::locale->text('Purpose (if field names purpose, purpose1, purpose2 ... exist they will all combined into the field "purpose")') }
|
|
| 100 |
); |
|
| 101 |
} |
|
| 102 |
|
|
| 86 | 103 |
sub setup_displayable_columns {
|
| 87 | 104 |
my ($self) = @_; |
| 88 | 105 |
|
| 89 | 106 |
$self->SUPER::setup_displayable_columns; |
| 90 | 107 |
|
| 91 |
$self->add_displayable_columns({ name => 'local_bank_code', description => $::locale->text('Own bank code') },
|
|
| 92 |
{ name => 'local_account_number', description => $::locale->text('Own bank account number or IBAN') },
|
|
| 93 |
{ name => 'local_bank_account_id', description => $::locale->text('ID of own bank account') },
|
|
| 94 |
{ name => 'remote_bank_code', description => $::locale->text('Bank code of the goal/source') },
|
|
| 95 |
{ name => 'remote_account_number', description => $::locale->text('Account number of the goal/source') },
|
|
| 96 |
{ name => 'transdate', description => $::locale->text('Date of transaction') },
|
|
| 97 |
{ name => 'valutadate', description => $::locale->text('Valuta date') },
|
|
| 98 |
{ name => 'amount', description => $::locale->text('Amount') },
|
|
| 99 |
{ name => 'currency', description => $::locale->text('Currency') },
|
|
| 100 |
{ name => 'currency_id', description => $::locale->text('Currency (database ID)') },
|
|
| 101 |
{ name => 'remote_name', description => $::locale->text('Name of the goal/source (if field names remote_name and remote_name_1 exist they will be combined into field "remote_name")') },
|
|
| 102 |
{ name => 'purpose', description => $::locale->text('Purpose (if field names purpose, purpose1, purpose2 ... exist they will all combined into the field "purpose")') },
|
|
| 103 |
{ name => 'transactionCode', description => $::locale->text('Transaction Code') },
|
|
| 104 |
{ name => 'transactionText', description => $::locale->text('Transaction Text') },
|
|
| 105 |
); |
|
| 108 |
$self->add_displayable_columns($self->_displayable_columns); |
|
| 106 | 109 |
} |
| 107 | 110 |
|
| 108 | 111 |
sub check_bank_account {
|
| SL/DB/BankTransaction.pm | ||
|---|---|---|
| 112 | 112 |
if ( $invoice->skonto_date && abs(abs($invoice->amount_less_skonto) - abs($self->amount)) < 0.01) {
|
| 113 | 113 |
$agreement += $points{skonto_exact_amount};
|
| 114 | 114 |
$rule_matches .= 'skonto_exact_amount(' . $points{'skonto_exact_amount'} . ') ';
|
| 115 |
$invoice->{skonto_type} = 'with_skonto_pt';
|
|
| 115 | 116 |
}; |
| 116 | 117 |
|
| 117 | 118 |
#search invoice number in purpose |
| ... | ... | |
| 208 | 209 |
}; |
| 209 | 210 |
}; |
| 210 | 211 |
|
| 211 |
# if there is exactly one non-executed sepa_export_item for the invoice |
|
| 212 |
if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
|
|
| 213 |
if ( scalar @$seis == 1 ) {
|
|
| 214 |
my $sei = $seis->[0]; |
|
| 215 |
|
|
| 216 |
# test for amount and id matching only, sepa transfer date and bank |
|
| 217 |
# transaction date needn't match |
|
| 218 |
my $arap = $invoice->is_sales ? 'ar' : 'ap'; |
|
| 219 |
if ( abs($self->amount) == ($sei->amount) |
|
| 220 |
&& $invoice->id == $sei->arap_id |
|
| 221 |
) {
|
|
| 222 |
$agreement += $points{sepa_export_item};
|
|
| 223 |
$rule_matches .= 'sepa_export_item(' . $points{'sepa_export_item'} . ') ';
|
|
| 224 |
}; |
|
| 225 |
} else {
|
|
| 226 |
# zero or more than one sepa_export_item, do nothing for this invoice |
|
| 227 |
# zero: do nothing, no sepa_export_item exists, no match |
|
| 228 |
# more than one: does this ever apply? Currently you can't create sepa |
|
| 229 |
# exports for invoices that already have a non-executed sepa_export |
|
| 230 |
}; |
|
| 231 |
}; |
|
| 212 |
# # if there is exactly one non-executed sepa_export_item for the invoice
|
|
| 213 |
# if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
|
|
| 214 |
# if ( scalar @$seis == 1 ) {
|
|
| 215 |
# my $sei = $seis->[0];
|
|
| 216 |
# |
|
| 217 |
# # test for amount and id matching only, sepa transfer date and bank
|
|
| 218 |
# # transaction date needn't match
|
|
| 219 |
# my $arap = $invoice->is_sales ? 'ar' : 'ap';
|
|
| 220 |
# if ( abs($self->amount) == ($sei->amount)
|
|
| 221 |
# && $invoice->id == $sei->arap_id
|
|
| 222 |
# ) {
|
|
| 223 |
# $agreement += $points{sepa_export_item};
|
|
| 224 |
# $rule_matches .= 'sepa_export_item(' . $points{'sepa_export_item'} . ') ';
|
|
| 225 |
# };
|
|
| 226 |
# } else {
|
|
| 227 |
# # zero or more than one sepa_export_item, do nothing for this invoice
|
|
| 228 |
# # zero: do nothing, no sepa_export_item exists, no match
|
|
| 229 |
# # more than one: does this ever apply? Currently you can't create sepa
|
|
| 230 |
# # exports for invoices that already have a non-executed sepa_export
|
|
| 231 |
# };
|
|
| 232 |
# };
|
|
| 232 | 233 |
|
| 233 | 234 |
return ($agreement,$rule_matches); |
| 234 | 235 |
}; |
| SL/DB/Helper/Payment.pm | ||
|---|---|---|
| 94 | 94 |
if ( $params{'payment_type'} eq 'difference_as_skonto' ) {
|
| 95 | 95 |
croak "amount $params{amount} doesn't match open amount " . $self->open_amount . ", diff = " . ($params{amount}-$self->open_amount) if $params{amount} && abs($self->open_amount - $params{amount} ) > 0.0000001;
|
| 96 | 96 |
} elsif ( $params{'payment_type'} eq 'with_skonto_pt' ) {
|
| 97 |
croak "amount $params{amount} doesn't match amount less skonto: " . $self->open_amount . "\n" if $params{amount} && abs($self->amount_less_skonto - $params{amount} ) > 0.0000001;
|
|
| 97 |
croak "amount $params{amount} doesn't match amount less skonto: " . $self->amount_less_skonto . "\n" if $params{amount} && abs($self->amount_less_skonto - $params{amount} ) > 0.0000001;
|
|
| 98 | 98 |
croak "payment type with_skonto_pt can't be used if payments have already been made" if $self->paid != 0; |
| 99 | 99 |
}; |
| 100 | 100 |
|
| ... | ... | |
| 635 | 635 |
die unless $bt; |
| 636 | 636 |
|
| 637 | 637 |
my $open_amount = $self->open_amount; |
| 638 |
|
|
| 638 |
#$main::lxdebug->message(LXDebug->DEBUG2(),"skonto_date=".$self->skonto_date." open amount=".$open_amount); |
|
| 639 | 639 |
my @options; |
| 640 | 640 |
if ( $open_amount && # invoice amount not 0 |
| 641 | 641 |
$self->skonto_date && # check whether skonto applies |
| 642 |
abs(abs($self->amount_less_skonto) - abs($bt->amount)) < 0.01 && |
|
| 642 |
( abs(abs($self->amount_less_skonto) - abs($bt->amount)) < 0.01 || |
|
| 643 |
( $bt->transactioncode eq "191" && abs($self->amount_less_skonto) < abs($bt->amount) )) && |
|
| 643 | 644 |
$self->check_skonto_configuration) {
|
| 644 | 645 |
if ( $self->within_skonto_period($bt->transdate) ) {
|
| 645 | 646 |
push(@options, { payment_type => 'without_skonto', display => t8('without skonto') });
|
| SL/SEPA.pm | ||
|---|---|---|
| 46 | 46 |
|
| 47 | 47 |
COALESCE(vc.iban, '') <> '' AND COALESCE(vc.bic, '') <> '' ${mandate} AS vc_bank_info_ok,
|
| 48 | 48 |
|
| 49 |
${arap}.amount - ${arap}.paid - COALESCE(open_transfers.amount, 0) AS open_amount
|
|
| 49 |
${arap}.amount - ${arap}.paid - COALESCE(open_transfers.amount, 0) AS open_amount,
|
|
| 50 |
COALESCE(open_transfers.amount, 0) AS transfer_amount, |
|
| 51 |
pt.description as pt_description |
|
| 50 | 52 |
|
| 51 | 53 |
FROM ${arap}
|
| 52 | 54 |
LEFT JOIN ${vc} vc ON (${arap}.${vc}_id = vc.id)
|
| ... | ... | |
| 64 | 66 |
|
| 65 | 67 |
ORDER BY lower(vc.name) ASC, lower(${arap}.invnumber) ASC
|
| 66 | 68 |
|; |
| 69 |
# $main::lxdebug->message(LXDebug->DEBUG2(),"sepa add query:".$query); |
|
| 67 | 70 |
|
| 68 | 71 |
my $results = selectall_hashref_query($form, $dbh, $query); |
| 69 | 72 |
|
| locale/de/all | ||
|---|---|---|
| 20 | 20 |
'#1 additional part(s)' => '#1 zusätzliche(r) Artikel', |
| 21 | 21 |
'#1 dunnings have been deleted' => '#1 Mahnung(en) wurden gelöscht', |
| 22 | 22 |
'#1 h' => '#1 h', |
| 23 |
'#1 invoice(s) saved.' => '#1 Rechnung(en) abgespeichert.', |
|
| 23 | 24 |
'#1 of #2 importable objects were imported.' => '#1 von #2 importierbaren Objekten wurden importiert.', |
| 24 | 25 |
'#1 prices were updated.' => '#1 Preise wurden aktualisiert.', |
| 25 | 26 |
'#1 proposal(s) saved.' => '#1 Vorschläge gespeichert.', |
| 27 |
'#1 proposal(s) with #2 invoice(s) saved.' => '#1 Vorschlag(e) mit #2 Rechnung(en) abgespeichert', |
|
| 26 | 28 |
'#1 section(s)' => '#1 Abschnitt(e)', |
| 27 | 29 |
'#1 text block(s) back' => '#1 Textlock/-blöcke vorne', |
| 28 | 30 |
'#1 text block(s) front' => '#1 Textblock/-blöcke hinten', |
| templates/webpages/bank_transactions/list.html | ||
|---|---|---|
| 14 | 14 |
[% IF FORM.filter.todate %] [% 'to (date)' | $T8 %] [% FORM.filter.todate %][% END %] |
| 15 | 15 |
</p> |
| 16 | 16 |
|
| 17 |
<form method="post" id="list_form"> |
|
| 18 |
[% L.hidden_tag('filter.bank_account', FORM.filter.bank_account) %]
|
|
| 19 |
[% L.hidden_tag('filter.fromdate', FORM.filter.fromdate) %]
|
|
| 20 |
[% L.hidden_tag('filter.todate', FORM.filter.todate) %]
|
|
| 21 |
|
|
| 22 |
<div class="tabwidget"> |
|
| 17 |
<div id="bt_tabs" class="tabwidget"> |
|
| 23 | 18 |
<ul> |
| 24 |
<li><a href="#all" onclick="show_invoice_button();">[% 'All transactions' | $T8 %]</a></li>
|
|
| 25 |
<li><a href="#automatic" onclick="show_proposal_button();">[% 'Proposals' | $T8 %]</a></li>
|
|
| 19 |
<li><a href="#all">[% 'All transactions' | $T8 %]</a></li> |
|
| 20 |
<li><a href="#automatic">[% 'Proposals' | $T8 %]</a></li> |
|
| 26 | 21 |
</ul> |
| 27 | 22 |
|
| 28 | 23 |
<div id="all">[% PROCESS "bank_transactions/tabs/all.html" %]</div> |
| 29 | 24 |
<div id="automatic">[% PROCESS "bank_transactions/tabs/automatic.html" %]</div> |
| 30 | 25 |
</div> |
| 31 | 26 |
|
| 32 |
[% L.hidden_tag('action', 'BankTransaction/dispatch') %]
|
|
| 33 |
[% L.submit_tag('action_save_invoices', LxERP.t8('Save invoices')) %]
|
|
| 34 |
[% L.submit_tag('action_save_proposals', LxERP.t8('Save proposals'), style='display: none') %]
|
|
| 35 |
|
|
| 36 |
</form> |
|
| 37 | 27 |
|
| 38 | 28 |
<script type="text/javascript"> |
| 39 | 29 |
<!-- |
| ... | ... | |
| 49 | 39 |
}); |
| 50 | 40 |
}); |
| 51 | 41 |
|
| 52 |
function show_invoice_button () {
|
|
| 53 |
$("#action_save_proposals").hide();
|
|
| 54 |
$("#action_save_invoices").show();
|
|
| 55 |
} |
|
| 56 |
|
|
| 57 |
function show_proposal_button () {
|
|
| 58 |
$("#action_save_invoices").hide();
|
|
| 59 |
$("#action_save_proposals").show();
|
|
| 60 |
} |
|
| 61 |
|
|
| 62 | 42 |
function assign_invoice(bt_id) {
|
| 63 | 43 |
kivi.popup_dialog({
|
| 64 | 44 |
url: 'controller.pl?action=BankTransaction/assign_invoice', |
| ... | ... | |
| 103 | 83 |
return true; |
| 104 | 84 |
} |
| 105 | 85 |
|
| 86 |
$.cookie('jquery_ui_tab_bt_tabs', [% ui_tab %] );
|
|
| 106 | 87 |
//--> |
| 107 | 88 |
</script> |
| templates/webpages/bank_transactions/tabs/all.html | ||
|---|---|---|
| 1 | 1 |
[%- USE HTML -%][%- USE LxERP -%][%- USE L -%][%- USE T8 -%] |
| 2 | 2 |
|
| 3 | 3 |
[% SET debug=1 %] |
| 4 |
<form method="post" id="list_form"> |
|
| 5 |
[% L.hidden_tag('filter.bank_account', FORM.filter.bank_account) %]
|
|
| 6 |
[% L.hidden_tag('filter.fromdate', FORM.filter.fromdate) %]
|
|
| 7 |
[% L.hidden_tag('filter.todate', FORM.filter.todate) %]
|
|
| 8 |
[% L.hidden_tag('action', 'BankTransaction/dispatch') %]
|
|
| 9 |
[% L.hidden_tag('ui_tab', ui_tab) %]
|
|
| 4 | 10 |
|
| 5 | 11 |
<table id="bt_list"> |
| 6 | 12 |
<thead> |
| ... | ... | |
| 45 | 51 |
[% END %] |
| 46 | 52 |
</th> |
| 47 | 53 |
<th>[% 'Purpose' | $T8 %]</th> |
| 54 |
<th>[% 'Type' | $T8 %]</th> |
|
| 48 | 55 |
<th>[% IF FORM.sort_by == 'remote_account_number'%] |
| 49 | 56 |
<a href="controller.pl?action=BankTransaction/list&filter.bank_account=[% bank_account.id %]&sort_by=remote_account_number&sort_dir=[% 1 - FORM.sort_dir %]" class="sort_link"> |
| 50 | 57 |
[% 'Remote account number' | $T8 %][% IF FORM.sort_dir == 0 %]<img border="0" src="image/down.png">[% ELSE %]<img border="0" src="image/up.png">[% END %]</a> |
| ... | ... | |
| 86 | 93 |
[% FOREACH prop = bt.proposals %] |
| 87 | 94 |
<div name='[% prop.id %]'> |
| 88 | 95 |
<a href=# onclick="add_invoices('[% bt.id %]', '[% prop.id %]', '[% HTML.escape(prop.invnumber) %]');"
|
| 89 |
title="<table><tr><th></th><th>[% 'Suggested invoice' | $T8 %][% IF !prop.is_sales %] ([% 'AP' | $T8 %])[% END %]</th><th>[% 'Bank transaction' | $T8 %]</th></tr><tr><th>[% 'Amount' | $T8 %]</th><td>[% LxERP.format_amount(prop.amount, 2) %] ([% 'open' | $T8 %]: [% LxERP.format_amount(prop.open_amount, 2) %])</td><td>[% LxERP.format_amount(bt.amount, 2) %]</td></tr>[% IF prop.skonto_date %]<tr><th>[% 'Payment terms' | $T8 %]</th><td>[% LxERP.format_amount(prop.amount_less_skonto, 2) %] [% 'until' | $T8 %] [% HTML.escape(prop.skonto_date.to_kivitendo) %] ([% prop.percent_skonto * 100 %] %)</td><td></td></tr>[% END %]<tr><th>[% 'Customer/Vendor' | $T8 %]</th><td>[% HTML.escape(prop.customer.displayable_name) %][% HTML.escape(prop.vendor.displayable_name) %]</td><td>[% HTML.escape(bt.remote_name) %]</td></tr><tr><th>[% 'Invoice Date' | $T8 %]</th><td>[% HTML.escape(prop.transdate_as_date) %]</td><td>[% HTML.escape(bt.transdate_as_date) %] ([% HTML.escape(bt.transdate.utc_rd_days - prop.transdate.utc_rd_days) %])</td></tr><tr><th>[% 'Invoice Number' | $T8 %]</th><td>[% HTML.escape(prop.invnumber) %]</td><td>[% HTML.escape(bt.purpose) %]</td></tr></table>" |
|
| 96 |
title="<table><tr><th></th><th>[% 'Suggested invoice' | $T8 %][% IF !prop.is_sales %] ([% 'AP' | $T8 %])[% END %]</th><th>[% 'Bank transaction' | $T8 %]</th></tr><tr><th>[% 'Amount' | $T8 %]</th><td>[% LxERP.format_amount(prop.amount, 2) %] ([% 'open' | $T8 %]: [% LxERP.format_amount(prop.open_amount, 2) %])</td><td>[% LxERP.format_amount(bt.absamount, 2) %]</td></tr>[% IF prop.skonto_date %]<tr><th>[% 'Payment terms' | $T8 %]</th><td>[% LxERP.format_amount(prop.amount_less_skonto, 2) %] [% 'until' | $T8 %] [% HTML.escape(prop.skonto_date.to_kivitendo) %] ([% prop.percent_skonto * 100 %] %)</td><td></td></tr>[% END %]<tr><th>[% 'Customer/Vendor' | $T8 %]</th><td>[% HTML.escape(prop.customer.displayable_name) %][% HTML.escape(prop.vendor.displayable_name) %]</td><td>[% HTML.escape(bt.remote_name) %]</td></tr><tr><th>[% 'Invoice Date' | $T8 %]</th><td>[% HTML.escape(prop.transdate_as_date) %]</td><td>[% HTML.escape(bt.transdate_as_date) %] ([% HTML.escape(bt.transdate.utc_rd_days - prop.transdate.utc_rd_days) %])</td></tr><tr><th>[% 'Invoice Number' | $T8 %]</th><td>[% HTML.escape(prop.invnumber) %]</td><td>[% HTML.escape(bt.purpose) %]</td></tr></table>"
|
|
| 90 | 97 |
class="[% IF bt.agreement >= 5 %]green[% ELSIF bt.agreement < 5 and bt.agreement >= 3 %]orange[% ELSE %]red[% END %] tooltipster-html">←[% HTML.escape(prop.invnumber)%]</a></div> |
| 91 | 98 |
[% END %] |
| 92 | 99 |
</td> |
| ... | ... | |
| 95 | 102 |
<td align=right>[% bt.invoice_amount_as_number %]</td> |
| 96 | 103 |
<td>[% HTML.escape(bt.remote_name) %]</td> |
| 97 | 104 |
<td>[% HTML.escape(bt.purpose) %]</td> |
| 105 |
<td>[% HTML.escape(bt.transactiontext) %]</td> |
|
| 98 | 106 |
<td>[% HTML.escape(bt.remote_account_number) %]</td> |
| 99 | 107 |
<td>[% HTML.escape(bt.remote_bank_code) %]</td> |
| 100 | 108 |
<td align=right>[% bt.valutadate_as_date %]</td> |
| ... | ... | |
| 103 | 111 |
[%- END %] |
| 104 | 112 |
</tbody> |
| 105 | 113 |
</table> |
| 114 |
[% L.submit_tag('action_save_invoices', LxERP.t8('Save invoices')) %]
|
|
| 115 |
|
|
| 116 |
</form> |
|
| templates/webpages/bank_transactions/tabs/automatic.html | ||
|---|---|---|
| 1 | 1 |
[%- USE HTML -%][%- USE LxERP -%][%- USE L -%][%- USE T8 -%] |
| 2 | 2 |
|
| 3 |
<form method="post" id="list_form"> |
|
| 4 |
[% L.hidden_tag('filter.bank_account', FORM.filter.bank_account) %]
|
|
| 5 |
[% L.hidden_tag('filter.fromdate', FORM.filter.fromdate) %]
|
|
| 6 |
[% L.hidden_tag('filter.todate', FORM.filter.todate) %]
|
|
| 7 |
[% L.hidden_tag('action', 'BankTransaction/dispatch') %]
|
|
| 8 |
[% L.hidden_tag('ui_tab', ui_tab) %]
|
|
| 9 |
|
|
| 3 | 10 |
<table> |
| 4 | 11 |
<thead> |
| 5 | 12 |
<tr class="listheading"> |
| ... | ... | |
| 9 | 16 |
<th>[% 'ID' | $T8 %]</th> |
| 10 | 17 |
<th>[% 'Transdate' | $T8 %]</th> |
| 11 | 18 |
<th>[% 'Amount' | $T8 %]</th> |
| 19 |
<th>[% 'Skonto' | $T8 %]</th> |
|
| 12 | 20 |
<th>[% 'Purpose/Reference' | $T8 %]</th> |
| 13 | 21 |
<th>[% 'Customer/Vendor/Remote name' | $T8 %]</th> |
| 14 | 22 |
</tr> |
| ... | ... | |
| 21 | 29 |
[% FOREACH proposal = PROPOSALS %] |
| 22 | 30 |
<tbody class="listrow"> |
| 23 | 31 |
<tr> |
| 24 |
<td rowspan=2 style="valign:center;">
|
|
| 32 |
<td rowspan=[% proposal.rowspan %] style="valign:center;">
|
|
| 25 | 33 |
[% L.checkbox_tag('proposal_ids[]', checked=0, value=proposal.id) %]
|
| 26 | 34 |
</td> |
| 27 | 35 |
|
| 28 |
<td>[% 'Bank transaction' | $T8 %]</td>
|
|
| 36 |
<td>[% HTML.escape(proposal.transactiontext) %]</td>
|
|
| 29 | 37 |
<td>[% proposal.id %]</td> |
| 30 | 38 |
<td>[% proposal.transdate_as_date %]</td> |
| 31 |
<td>[% proposal.amount_as_number %]</td> |
|
| 39 |
<td align="right">[% proposal.amount_as_number %]</td> |
|
| 40 |
<td></td> |
|
| 32 | 41 |
<td>[% HTML.escape(proposal.purpose) %]</td> |
| 33 | 42 |
<td>[% HTML.escape(proposal.remote_name) %]</td> |
| 34 | 43 |
</tr> |
| ... | ... | |
| 36 | 45 |
[% FOREACH proposed_invoice = proposal.proposals %] |
| 37 | 46 |
<tr> |
| 38 | 47 |
|
| 48 |
<td></td> |
|
| 39 | 49 |
<td>[% 'Invoice' | $T8 %]</td> |
| 40 | 50 |
<td>[% proposed_invoice.id %]</td> |
| 41 |
<td>[% proposed_invoice.transdate_as_date %]</td> |
|
| 42 |
<td>[% proposed_invoice.amount_as_number %]</td> |
|
| 51 |
<td>[% proposed_invoice.transdate_as_date %] |
|
| 52 |
[% L.hidden_tag("invoice_ids." _ proposal.id _ "[]", proposed_invoice.id) %]</td>
|
|
| 53 |
<td align="right">[% proposed_invoice.realamount %]</td> |
|
| 54 |
<td>[% proposed_invoice.skonto_type | $T8 %] |
|
| 55 |
[% L.hidden_tag("invoice_skontos." _ proposal.id _ "[]", proposed_invoice.skonto_type) %]</td>
|
|
| 43 | 56 |
<td>[% proposed_invoice.link %]</td> |
| 44 | 57 |
<td>[% HTML.escape(proposed_invoice.customer.name) %][% HTML.escape(proposed_invoice.vendor.name) %]</td> |
| 45 | 58 |
</tr> |
| 46 |
[% L.hidden_tag("proposed_invoice_" _ proposal.id, proposed_invoice.id) %]
|
|
| 47 |
[% END %]
|
|
| 59 |
[% END %]
|
|
| 60 |
<tr><td style="height:10px"></td></tr>
|
|
| 48 | 61 |
</tbody> |
| 49 | 62 |
[% END %] |
| 50 | 63 |
[% END %] |
| 51 | 64 |
</table> |
| 65 |
[% L.submit_tag('action_save_proposals', LxERP.t8('Save proposals')) %]
|
|
| 66 |
|
|
| 67 |
</form> |
|
Auch abrufbar als: Unified diff
Bankimport: Behandlung von Sammelüberweisungen
Generell werden die SEPA Export-Items aus der Punktebewertung herausgenommn,
dafür wird eine exaktere Prüfung auch mittels des Transaktionstyps ermittelt.
Dadurch werden auch Sammellastschriften/Überweisungen erkannt.
Setzen von Skontotyp, kein Prüfen der Sepaitems mehr in >get_agreement_with_invoice