1 package SL::Controller::BankTransaction;
3 # idee- möglichkeit bankdaten zu übernehmen in stammdaten
4 # erst Kontenabgleich, um alle gl-Einträge wegzuhaben
7 use parent qw(SL::Controller::Base);
9 use SL::Controller::Helper::GetModels;
10 use SL::Controller::Helper::ReportGenerator;
11 use SL::ReportGenerator;
13 use SL::DB::BankTransaction;
14 use SL::Helper::Flash;
15 use SL::Locale::String;
18 use SL::DB::PurchaseInvoice;
19 use SL::DB::RecordLink;
22 use SL::DB::AccTransaction;
24 use SL::DB::BankAccount;
25 use SL::DB::RecordTemplate;
26 use SL::DB::SepaExportItem;
27 use SL::DBUtils qw(like);
30 use List::MoreUtils qw(any);
31 use List::Util qw(max);
33 use Rose::Object::MakeMethods::Generic
35 scalar => [ qw(callback transaction) ],
36 'scalar --get_set_init' => [ qw(models problems) ],
39 __PACKAGE__->run_before('check_auth');
49 my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted( query => [ obsolete => 0 ] );
51 $self->render('bank_transactions/search',
52 BANK_ACCOUNTS => $bank_accounts);
58 $self->make_filter_summary;
59 $self->prepare_report;
61 $self->report_generator_list_objects(report => $self->{report}, objects => $self->models->get);
67 if (!$::form->{filter}{bank_account}) {
68 flash('error', t8('No bank account chosen!'));
73 my $sort_by = $::form->{sort_by} || 'transdate';
74 $sort_by = 'transdate' if $sort_by eq 'proposal';
75 $sort_by .= $::form->{sort_dir} ? ' DESC' : ' ASC';
77 my $fromdate = $::locale->parse_date_to_object($::form->{filter}->{fromdate});
78 my $todate = $::locale->parse_date_to_object($::form->{filter}->{todate});
79 $todate->add( days => 1 ) if $todate;
82 push @where, (transdate => { ge => $fromdate }) if ($fromdate);
83 push @where, (transdate => { lt => $todate }) if ($todate);
84 my $bank_account = SL::DB::Manager::BankAccount->find_by( id => $::form->{filter}{bank_account} );
85 # bank_transactions no younger than starting date,
86 # including starting date (same search behaviour as fromdate)
87 # but OPEN invoices to be matched may be from before
88 if ( $bank_account->reconciliation_starting_date ) {
89 push @where, (transdate => { ge => $bank_account->reconciliation_starting_date });
92 my $bank_transactions = SL::DB::Manager::BankTransaction->get_all(
93 with_objects => [ 'local_bank_account', 'currency' ],
97 amount => {ne => \'invoice_amount'},
98 local_bank_account_id => $::form->{filter}{bank_account},
102 $main::lxdebug->message(LXDebug->DEBUG2(),"count bt=".scalar(@{$bank_transactions}." bank_account=".$bank_account->id." chart=".$bank_account->chart_id));
104 # credit notes have a negative amount, treat differently
105 my $all_open_ar_invoices = SL::DB::Manager::Invoice ->get_all(where => [ or => [ amount => { gt => \'paid' },
106 and => [ type => 'credit_note',
107 amount => { lt => \'paid' }
111 with_objects => ['customer','payment_terms']);
113 my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => [amount => { ne => \'paid' }], with_objects => ['vendor' ,'payment_terms']);
114 my $all_open_sepa_export_items = SL::DB::Manager::SepaExportItem->get_all(where => [chart_id => $bank_account->chart_id ,
115 'sepa_export.executed' => 0, 'sepa_export.closed' => 0 ], with_objects => ['sepa_export']);
116 $main::lxdebug->message(LXDebug->DEBUG2(),"count sepaexport=".scalar(@{$all_open_sepa_export_items}));
118 my @all_open_invoices;
119 # filter out invoices with less than 1 cent outstanding
120 push @all_open_invoices, map { $_->{is_ar}=1 ; $_ } grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ar_invoices };
121 push @all_open_invoices, map { $_->{is_ar}=0 ; $_ } grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
122 $main::lxdebug->message(LXDebug->DEBUG2(),"bank_account=".$::form->{filter}{bank_account}." invoices: ".scalar(@{ $all_open_ar_invoices }).
123 " + ".scalar(@{ $all_open_ap_invoices })." non fully paid=".scalar(@all_open_invoices)." transactions=".scalar(@{ $bank_transactions }));
125 my @all_sepa_invoices;
126 my @all_non_sepa_invoices;
128 # first collect sepa export items to open invoices
129 foreach my $open_invoice (@all_open_invoices){
130 # my @items = grep { $_->ap_id == $open_invoice->id || $_->ar_id == $open_invoice->id } @{$all_open_sepa_export_items};
131 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount,2);
132 $open_invoice->{skonto_type} = 'without_skonto';
133 foreach ( @{$all_open_sepa_export_items}) {
134 if ( $_->ap_id == $open_invoice->id || $_->ar_id == $open_invoice->id ) {
135 my $factor = ($_->ar_id == $open_invoice->id?1:-1);
136 $main::lxdebug->message(LXDebug->DEBUG2(),"exitem=".$_->id." for invoice ".$open_invoice->id." factor=".$factor);
137 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount*$factor,2);
138 $open_invoice->{sepa_export_item} = $_ ;
139 $open_invoice->{skonto_type} = $_->payment_type;
140 $sepa_exports{$_->sepa_export_id} ||= { count => 0, is_ar => 0, amount => 0, proposed => 0, invoices => [], item => $_ };
141 $sepa_exports{$_->sepa_export_id}->{count}++ ;
142 $sepa_exports{$_->sepa_export_id}->{is_ar}++ if $_->ar_id == $open_invoice->id;
143 $sepa_exports{$_->sepa_export_id}->{amount} += $_->amount * $factor;
144 push @{ $sepa_exports{$_->sepa_export_id}->{invoices} }, $open_invoice;
145 #$main::lxdebug->message(LXDebug->DEBUG2(),"amount for export id ".$_->sepa_export_id." = ".
146 # $sepa_exports{$_->sepa_export_id}->{amount}." count = ".
147 # $sepa_exports{$_->sepa_export_id}->{count}." is_ar = ".
148 # $sepa_exports{$_->sepa_export_id}->{is_ar} );
149 push @all_sepa_invoices , $open_invoice;
152 push @all_non_sepa_invoices , $open_invoice if ! $open_invoice->{sepa_export_item};
155 # try to match each bank_transaction with each of the possible open invoices
159 foreach my $bt (@{ $bank_transactions }) {
160 ## 5 Stellen hinter dem Komma auf 2 Stellen reduzieren
161 $bt->amount($bt->amount*1);
162 $bt->invoice_amount($bt->invoice_amount*1);
163 $main::lxdebug->message(LXDebug->DEBUG2(),"BT ".$bt->id." amount=".$bt->amount." invoice_amount=".$bt->invoice_amount." remote=". $bt->{remote_name});
165 $bt->{proposals} = [];
166 $bt->{rule_matches} = [];
168 $bt->{remote_name} .= $bt->{remote_name_1} if $bt->{remote_name_1};
170 if ( $self->is_collective_transaction($bt) ) {
171 foreach ( keys %sepa_exports) {
172 #$main::lxdebug->message(LXDebug->DEBUG2(),"Exp ID=".$_." compare sum amount ".($sepa_exports{$_}->{amount} *1) ." == ".($bt->amount * 1));
173 if ( $bt->transaction_code eq '191' && abs(($sepa_exports{$_}->{amount} * 1) - ($bt->amount * 1)) < 0.01 ) {
175 @{$bt->{proposals}} = @{$sepa_exports{$_}->{invoices}};
176 $bt->{agreement} = 20;
177 push(@{$bt->{rule_matches}},'sepa_export_item(20)');
178 $sepa_exports{$_}->{proposed}=1;
179 #$main::lxdebug->message(LXDebug->DEBUG2(),"has ".scalar($bt->{proposals})." invoices");
180 push(@proposals, $bt);
185 next unless $bt->{remote_name}; # bank has no name, usually fees, use create invoice to assign
187 foreach ( @{$all_open_sepa_export_items}) {
188 last if scalar (@all_sepa_invoices) == 0;
189 foreach my $open_invoice (@all_sepa_invoices){
190 if ( $_->ap_id == $open_invoice->id || $_->ar_id == $open_invoice->id ) {
191 #$main::lxdebug->message(LXDebug->DEBUG2(),"exitem2=".$_->id." for invoice ".$open_invoice->id);
192 my $factor = ( $_->ar_id == $open_invoice->id?1:-1);
193 $_->amount($_->amount*1);
194 #$main::lxdebug->message(LXDebug->DEBUG2(),"remote account '".$bt->{remote_account_number}."' bt_amount=".$bt->amount." factor=".$factor);
195 #$main::lxdebug->message(LXDebug->DEBUG2(),"compare with '".$_->vc_iban."' amount=".$_->amount);
196 if ( $bt->{remote_account_number} eq $_->vc_iban && abs(abs($_->amount) - abs($bt->amount)) < 0.01 ) {
198 $iban = $open_invoice->customer->iban if $open_invoice->is_sales;
199 $iban = $open_invoice->vendor->iban if ! $open_invoice->is_sales;
200 if($bt->{remote_account_number} eq $iban && abs(abs($open_invoice->amount) - abs($bt->amount)) < 0.01 ) {
201 ($open_invoice->{agreement}, $open_invoice->{rule_matches}) = $bt->get_agreement_with_invoice($open_invoice);
202 $open_invoice->{agreement} += 5;
203 $open_invoice->{rule_matches} .= 'sepa_export_item(5) ';
204 $main::lxdebug->message(LXDebug->DEBUG2(),"sepa invoice_id=".$open_invoice->id." agreement=".$open_invoice->{agreement}." rules matches=".$open_invoice->{rule_matches});
205 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount*$factor,2);
212 # try to match the current $bt to each of the open_invoices, saving the
213 # results of get_agreement_with_invoice in $open_invoice->{agreement} and
214 # $open_invoice->{rule_matches}.
216 # The values are overwritten each time a new bt is checked, so at the end
217 # of each bt the likely results are filtered and those values are stored in
218 # the arrays $bt->{proposals} and $bt->{rule_matches}, and the agreement
219 # score is stored in $bt->{agreement}
221 foreach my $open_invoice (@all_non_sepa_invoices){
222 ($open_invoice->{agreement}, $open_invoice->{rule_matches}) = $bt->get_agreement_with_invoice($open_invoice);
223 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount*($open_invoice->{is_ar}?1:-1),2);
224 $main::lxdebug->message(LXDebug->DEBUG2(),"nons invoice_id=".$open_invoice->id." amount=".$open_invoice->amount." agreement=".$open_invoice->{agreement}." rules matches=".$open_invoice->{rule_matches}) if $open_invoice->{agreement} > 2;
228 my $min_agreement = 3; # suggestions must have at least this score
230 my $max_agreement = max map { $_->{agreement} } @all_open_invoices;
232 # add open_invoices with highest agreement into array $bt->{proposals}
233 if ( $max_agreement >= $min_agreement ) {
234 $bt->{proposals} = [ grep { $_->{agreement} == $max_agreement } @all_open_invoices ];
235 $bt->{agreement} = $max_agreement; #scalar @{ $bt->{proposals} } ? $agreement + 1 : '';
237 # store the rule_matches in a separate array, so they can be displayed in template
238 foreach ( @{ $bt->{proposals} } ) {
239 push(@{$bt->{rule_matches}}, $_->{rule_matches});
245 # separate filter for proposals (second tab, agreement >= 5 and exactly one match)
246 # to qualify as a proposal there has to be
247 # * agreement >= 5 TODO: make threshold configurable in configuration
248 # * there must be only one exact match
249 # * depending on whether sales or purchase the amount has to have the correct sign (so Gutschriften don't work?)
250 my $proposal_threshold = 5;
251 my @otherproposals = grep {
252 ($_->{agreement} >= $proposal_threshold)
253 && (1 == scalar @{ $_->{proposals} })
254 && (@{ $_->{proposals} }[0]->is_sales ? abs(@{ $_->{proposals} }[0]->amount - $_->amount) < 0.01
255 : abs(@{ $_->{proposals} }[0]->amount + $_->amount) < 0.01)
256 } @{ $bank_transactions };
258 push ( @proposals, @otherproposals);
260 # sort bank transaction proposals by quality (score) of proposal
261 $bank_transactions = [ sort { $a->{agreement} <=> $b->{agreement} } @{ $bank_transactions } ] if $::form->{sort_by} eq 'proposal' and $::form->{sort_dir} == 1;
262 $bank_transactions = [ sort { $b->{agreement} <=> $a->{agreement} } @{ $bank_transactions } ] if $::form->{sort_by} eq 'proposal' and $::form->{sort_dir} == 0;
264 $::request->layout->add_javascripts("kivi.BankTransaction.js");
265 $self->render('bank_transactions/list',
266 title => t8('Bank transactions MT940'),
267 BANK_TRANSACTIONS => $bank_transactions,
268 PROPOSALS => \@proposals,
269 bank_account => $bank_account,
270 ui_tab => scalar(@proposals) > 0?1:0,
274 sub action_assign_invoice {
277 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
279 $self->render('bank_transactions/assign_invoice',
281 title => t8('Assign invoice'),);
284 sub action_create_invoice {
286 my %myconfig = %main::myconfig;
288 $self->transaction(SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id}));
290 my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(account_number => $self->transaction->{remote_account_number});
291 my $use_vendor_filter = $self->transaction->{remote_account_number} && $vendor_of_transaction;
293 my $templates = SL::DB::Manager::RecordTemplate->get_all(
294 where => [ template_type => 'ap_transaction' ],
295 with_objects => [ qw(employee vendor) ],
299 $templates = [ grep { $_->vendor_id == $vendor_of_transaction->id } @{ $templates } ] if $use_vendor_filter;
301 $self->callback($self->url_for(
303 'filter.bank_account' => $::form->{filter}->{bank_account},
304 'filter.todate' => $::form->{filter}->{todate},
305 'filter.fromdate' => $::form->{filter}->{fromdate},
309 'bank_transactions/create_invoice',
311 title => t8('Create invoice'),
312 TEMPLATES => $templates,
313 vendor_id => $use_vendor_filter ? $vendor_of_transaction->id : undef,
314 vendor_name => $use_vendor_filter ? $vendor_of_transaction->name : undef,
318 sub action_ajax_payment_suggestion {
321 # based on a BankTransaction ID and a Invoice or PurchaseInvoice ID passed via $::form,
322 # create an HTML blob to be used by the js function add_invoices in templates/webpages/bank_transactions/list.html
323 # and return encoded as JSON
325 my $bt = SL::DB::Manager::BankTransaction->find_by( id => $::form->{bt_id} );
326 my $invoice = SL::DB::Manager::Invoice->find_by( id => $::form->{prop_id} ) || SL::DB::Manager::PurchaseInvoice->find_by( id => $::form->{prop_id} );
328 die unless $bt and $invoice;
330 my @select_options = $invoice->get_payment_select_options_for_bank_transaction($::form->{bt_id});
333 $html = $self->render(
334 'bank_transactions/_payment_suggestion', { output => 0 },
335 bt_id => $::form->{bt_id},
336 prop_id => $::form->{prop_id},
338 SELECT_OPTIONS => \@select_options,
341 $self->render(\ SL::JSON::to_json( { 'html' => "$html" } ), { layout => 0, type => 'json', process => 0 });
344 sub action_filter_templates {
347 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
348 my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(account_number => $self->{transaction}->{remote_account_number});
351 push @filter, ('vendor.id' => $::form->{vendor_id}) if $::form->{vendor_id};
352 push @filter, ('vendor.name' => { ilike => '%' . $::form->{vendor} . '%' }) if $::form->{vendor};
354 my $templates = SL::DB::Manager::RecordTemplate->get_all(
355 where => [ template_type => 'ap_transaction', (or => \@filter) x !!@filter ],
356 with_objects => [ qw(employee vendor) ],
359 $::form->{filter} //= {};
361 $self->callback($self->url_for(
363 'filter.bank_account' => $::form->{filter}->{bank_account},
364 'filter.todate' => $::form->{filter}->{todate},
365 'filter.fromdate' => $::form->{filter}->{fromdate},
368 my $output = $self->render(
369 'bank_transactions/_template_list',
371 TEMPLATES => $templates,
374 $self->render(\to_json({ html => $output }), { type => 'json', process => 0 });
377 sub action_ajax_add_list {
380 my @where_sale = (amount => { ne => \'paid' });
381 my @where_purchase = (amount => { ne => \'paid' });
383 if ($::form->{invnumber}) {
384 push @where_sale, (invnumber => { ilike => like($::form->{invnumber})});
385 push @where_purchase, (invnumber => { ilike => like($::form->{invnumber})});
388 if ($::form->{amount}) {
389 push @where_sale, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
390 push @where_purchase, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
393 if ($::form->{vcnumber}) {
394 push @where_sale, ('customer.customernumber' => { ilike => like($::form->{vcnumber})});
395 push @where_purchase, ('vendor.vendornumber' => { ilike => like($::form->{vcnumber})});
398 if ($::form->{vcname}) {
399 push @where_sale, ('customer.name' => { ilike => like($::form->{vcname})});
400 push @where_purchase, ('vendor.name' => { ilike => like($::form->{vcname})});
403 if ($::form->{transdatefrom}) {
404 my $fromdate = $::locale->parse_date_to_object($::form->{transdatefrom});
405 if ( ref($fromdate) eq 'DateTime' ) {
406 push @where_sale, ('transdate' => { ge => $fromdate});
407 push @where_purchase, ('transdate' => { ge => $fromdate});
411 if ($::form->{transdateto}) {
412 my $todate = $::locale->parse_date_to_object($::form->{transdateto});
413 if ( ref($todate) eq 'DateTime' ) {
414 $todate->add(days => 1);
415 push @where_sale, ('transdate' => { lt => $todate});
416 push @where_purchase, ('transdate' => { lt => $todate});
420 my $all_open_ar_invoices = SL::DB::Manager::Invoice ->get_all(where => \@where_sale, with_objects => 'customer');
421 my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => \@where_purchase, with_objects => 'vendor');
423 my @all_open_invoices = @{ $all_open_ar_invoices };
424 # add ap invoices, filtering out subcent open amounts
425 push @all_open_invoices, grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
427 @all_open_invoices = sort { $a->id <=> $b->id } @all_open_invoices;
429 my $output = $self->render(
430 'bank_transactions/add_list',
432 INVOICES => \@all_open_invoices,
435 my %result = ( count => 0, html => $output );
437 $self->render(\to_json(\%result), { type => 'json', process => 0 });
440 sub action_ajax_accept_invoices {
443 my @selected_invoices;
444 foreach my $invoice_id (@{ $::form->{invoice_id} || [] }) {
445 my $invoice_object = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
446 push @selected_invoices, $invoice_object;
450 'bank_transactions/invoices',
452 INVOICES => \@selected_invoices,
453 bt_id => $::form->{bt_id},
460 return 0 if !$::form->{invoice_ids};
462 my %invoice_hash = %{ delete $::form->{invoice_ids} }; # each key (the bt line with a bt_id) contains an array of invoice_ids
464 # e.g. three partial payments with bt_ids 54, 55 and 56 for invoice with id 74:
477 # or if the payment with bt_id 44 is used to pay invoices with ids 50, 51 and 52
479 # '44' => [ '50', '51', 52' ]
482 $::form->{invoice_skontos} ||= {}; # hash of arrays containing the payment types, could be empty
484 # a bank_transaction may be assigned to several invoices, i.e. a customer
485 # might pay several open invoices with one transaction
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 sources => ($::form->{sources} // {})->{$_},
499 memos => ($::form->{memos} // {})->{$_},
501 $count += scalar( @{$invoice_ids} );
504 while ( my ($bank_transaction_id, $invoice_ids) = each(%invoice_hash) ) {
505 push @{ $self->problems }, $self->save_single_bank_transaction(
506 bank_transaction_id => $bank_transaction_id,
507 invoice_ids => $invoice_ids,
508 sources => [ map { $::form->{"sources_${bank_transaction_id}_${_}"} } @{ $invoice_ids } ],
509 memos => [ map { $::form->{"memos_${bank_transaction_id}_${_}"} } @{ $invoice_ids } ],
511 $count += scalar( @{$invoice_ids} );
514 foreach (@{ $self->problems }) {
515 $count-- if $_->{result} eq 'error';
520 sub action_save_invoices {
522 my $count = $self->save_invoices();
524 flash('ok', t8('#1 invoice(s) saved.', $count));
526 $self->action_list();
529 sub action_save_proposals {
532 if ( $::form->{proposal_ids} ) {
533 my $propcount = scalar(@{ $::form->{proposal_ids} });
534 if ( $propcount > 0 ) {
535 my $count = $self->save_invoices();
537 flash('ok', t8('#1 proposal(s) with #2 invoice(s) saved.', $propcount, $count));
540 $self->action_list();
544 sub is_collective_transaction {
545 my ($self, $bt) = @_;
546 return $bt->transaction_code eq "191";
549 sub save_single_bank_transaction {
550 my ($self, %params) = @_;
554 bank_transaction => SL::DB::Manager::BankTransaction->find_by(id => $params{bank_transaction_id}),
558 if (!$data{bank_transaction}) {
562 message => $::locale->text('The ID #1 is not a valid database ID.', $data{bank_transaction_id}),
569 my $bt_id = $data{bank_transaction_id};
570 my $bank_transaction = $data{bank_transaction};
571 my $sign = $bank_transaction->amount < 0 ? -1 : 1;
572 my $amount_of_transaction = $sign * $bank_transaction->amount;
573 my $payment_received = $bank_transaction->amount > 0;
574 my $payment_sent = $bank_transaction->amount < 0;
577 foreach my $invoice_id (@{ $params{invoice_ids} }) {
578 my $invoice = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
583 message => $::locale->text("The ID #1 is not a valid database ID.", $invoice_id),
586 push @{ $data{invoices} }, $invoice;
589 if ( $payment_received
590 && any { ( $_->is_sales && ($_->amount < 0))
591 || (!$_->is_sales && ($_->amount > 0))
592 } @{ $data{invoices} }) {
596 message => $::locale->text("Received payments can only be posted for sales invoices and purchase credit notes."),
601 && any { ( $_->is_sales && ($_->amount > 0))
602 || (!$_->is_sales && ($_->amount < 0) && ($_->invoice_type eq 'purchase_invoice'))
603 } @{ $data{invoices} }) {
607 message => $::locale->text("Sent payments can only be posted for purchase invoices and sales credit notes."),
611 my $max_invoices = scalar(@{ $data{invoices} });
614 foreach my $invoice (@{ $data{invoices} }) {
615 my $source = ($data{sources} // [])->[$n_invoices];
616 my $memo = ($data{memos} // [])->[$n_invoices];
620 # Check if bank_transaction already has a link to the invoice, may only be linked once per invoice
621 # This might be caused by the user reloading a page and resending the form
622 if (_existing_record_link($bank_transaction, $invoice)) {
626 message => $::locale->text("Bank transaction with id #1 has already been linked to #2.", $bank_transaction->id, $invoice->displayable_name),
630 if (!$amount_of_transaction && $invoice->open_amount) {
634 message => $::locale->text("A payment can only be posted for multiple invoices if the amount to post is equal to or bigger than the sum of the open amounts of the affected invoices."),
639 if ( defined $::form->{invoice_skontos}->{"$bt_id"} ) {
640 $payment_type = shift(@{ $::form->{invoice_skontos}->{"$bt_id"} });
642 $payment_type = 'without_skonto';
646 # pay invoice or go to the next bank transaction if the amount is not sufficiently high
647 if ($invoice->open_amount <= $amount_of_transaction && $n_invoices < $max_invoices) {
648 my $open_amount = ($payment_type eq 'with_skonto_pt'?$invoice->amount_less_skonto:$invoice->open_amount);
649 # first calculate new bank transaction amount ...
650 if ($invoice->is_sales) {
651 $amount_of_transaction -= $sign * $open_amount;
652 $bank_transaction->invoice_amount($bank_transaction->invoice_amount + $open_amount);
654 $amount_of_transaction += $sign * $open_amount;
655 $bank_transaction->invoice_amount($bank_transaction->invoice_amount - $open_amount);
657 # ... and then pay the invoice
658 $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
659 trans_id => $invoice->id,
660 amount => $open_amount,
661 payment_type => $payment_type,
664 transdate => $bank_transaction->transdate->to_kivitendo);
665 } elsif (( $invoice->is_sales && $invoice->invoice_type eq 'credit_note' ) ||
666 (!$invoice->is_sales && $invoice->invoice_type eq 'ap_transaction' )) {
667 # no check for overpayment/multiple payments
668 $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
669 trans_id => $invoice->id,
670 amount => $invoice->open_amount,
671 payment_type => $payment_type,
674 transdate => $bank_transaction->transdate->to_kivitendo);
675 } else { # use the whole amount of the bank transaction for the invoice, overpay the invoice if necessary
676 my $overpaid_amount = $amount_of_transaction - $invoice->open_amount;
677 $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
678 trans_id => $invoice->id,
679 amount => $amount_of_transaction,
680 payment_type => $payment_type,
683 transdate => $bank_transaction->transdate->to_kivitendo);
684 $bank_transaction->invoice_amount($bank_transaction->amount);
685 $amount_of_transaction = 0;
687 if ($overpaid_amount >= 0.01) {
691 message => $::locale->text('Invoice #1 was overpaid by #2.', $invoice->invnumber, $::form->format_amount(\%::myconfig, $overpaid_amount, 2)),
695 # Record a record link from the bank transaction to the invoice
697 from_table => 'bank_transactions',
699 to_table => $invoice->is_sales ? 'ar' : 'ap',
700 to_id => $invoice->id,
703 SL::DB::RecordLink->new(@props)->save;
705 # "close" a sepa_export_item if it exists
706 # code duplicated in action_save_proposals!
707 # currently only works, if there is only exactly one open sepa_export_item
708 if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
709 if ( scalar @$seis == 1 ) {
710 # moved the execution and the check for sepa_export into a method,
711 # this isn't part of a transaction, though
712 $seis->[0]->set_executed if $invoice->id == $seis->[0]->arap_id;
717 $bank_transaction->save;
719 # 'undef' means 'no error' here.
724 my $rez = $data{bank_transaction}->db->with_transaction(sub {
726 $error = $worker->();
737 # Rollback Fehler nicht weiterreichen
741 return grep { $_ } ($error, @warnings);
749 $::auth->assert('bank_transaction');
756 sub make_filter_summary {
759 my $filter = $::form->{filter} || {};
763 [ $filter->{"transdate:date::ge"}, $::locale->text('Transdate') . " " . $::locale->text('From Date') ],
764 [ $filter->{"transdate:date::le"}, $::locale->text('Transdate') . " " . $::locale->text('To Date') ],
765 [ $filter->{"valutadate:date::ge"}, $::locale->text('Valutadate') . " " . $::locale->text('From Date') ],
766 [ $filter->{"valutadate:date::le"}, $::locale->text('Valutadate') . " " . $::locale->text('To Date') ],
767 [ $filter->{"amount:number"}, $::locale->text('Amount') ],
768 [ $filter->{"bank_account_id:integer"}, $::locale->text('Local bank account') ],
772 push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
775 $self->{filter_summary} = join ', ', @filter_strings;
781 my $callback = $self->models->get_callback;
783 my $report = SL::ReportGenerator->new(\%::myconfig, $::form);
784 $self->{report} = $report;
786 my @columns = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount invoice_amount invoices currency purpose local_account_number local_bank_code id);
787 my @sortable = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount purpose local_account_number local_bank_code);
790 transdate => { sub => sub { $_[0]->transdate_as_date } },
791 valutadate => { sub => sub { $_[0]->valutadate_as_date } },
793 remote_account_number => { },
794 remote_bank_code => { },
795 amount => { sub => sub { $_[0]->amount_as_number },
797 invoice_amount => { sub => sub { $_[0]->invoice_amount_as_number },
799 invoices => { sub => sub { $_[0]->linked_invoices } },
800 currency => { sub => sub { $_[0]->currency->name } },
802 local_account_number => { sub => sub { $_[0]->local_bank_account->account_number } },
803 local_bank_code => { sub => sub { $_[0]->local_bank_account->bank_code } },
804 local_bank_name => { sub => sub { $_[0]->local_bank_account->name } },
808 map { $column_defs{$_}->{text} ||= $::locale->text( $self->models->get_sort_spec->{$_}->{title} ) } keys %column_defs;
810 $report->set_options(
811 std_column_visibility => 1,
812 controller_class => 'BankTransaction',
813 output_format => 'HTML',
814 top_info_text => $::locale->text('Bank transactions'),
815 title => $::locale->text('Bank transactions'),
816 allow_pdf_export => 1,
817 allow_csv_export => 1,
819 $report->set_columns(%column_defs);
820 $report->set_column_order(@columns);
821 $report->set_export_options(qw(list_all filter));
822 $report->set_options_from_form;
823 $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
824 $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
826 my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted();
828 $report->set_options(
829 raw_top_info_text => $self->render('bank_transactions/report_top', { output => 0 }, BANK_ACCOUNTS => $bank_accounts),
830 raw_bottom_info_text => $self->render('bank_transactions/report_bottom', { output => 0 }),
834 sub _existing_record_link {
835 my ($bt, $invoice) = @_;
837 # check whether a record link from banktransaction $bt already exists to
838 # invoice $invoice, returns 1 if that is the case
840 die unless $bt->isa("SL::DB::BankTransaction") && ( $invoice->isa("SL::DB::Invoice") || $invoice->isa("SL::DB::PurchaseInvoice") );
842 my $linked_record_to_table = $invoice->is_sales ? 'Invoice' : 'PurchaseInvoice';
843 my $linked_records = $bt->linked_records( direction => 'to', to => $linked_record_to_table, query => [ id => $invoice->id ] );
845 return @$linked_records ? 1 : 0;
848 sub init_problems { [] }
853 SL::Controller::Helper::GetModels->new(
858 dir => 0, # 1 = ASC, 0 = DESC : default sort is newest at top
860 transdate => t8('Transdate'),
861 remote_name => t8('Remote name'),
862 amount => t8('Amount'),
863 invoice_amount => t8('Assigned'),
864 invoices => t8('Linked invoices'),
865 valutadate => t8('Valutadate'),
866 remote_account_number => t8('Remote account number'),
867 remote_bank_code => t8('Remote bank code'),
868 currency => t8('Currency'),
869 purpose => t8('Purpose'),
870 local_account_number => t8('Local account number'),
871 local_bank_code => t8('Local bank code'),
872 local_bank_name => t8('Bank account'),
874 with_objects => [ 'local_bank_account', 'currency' ],
878 sub load_ap_record_template_url {
879 my ($self, $template) = @_;
881 return $self->url_for(
882 controller => 'ap.pl',
883 action => 'load_record_template',
885 'form_defaults.amount_1' => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
886 'form_defaults.transdate' => $self->transaction->transdate_as_date,
887 'form_defaults.duedate' => $self->transaction->transdate_as_date,
888 'form_defaults.datepaid_1' => $self->transaction->transdate_as_date,
889 'form_defaults.paid_1' => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
890 'form_defaults.currency' => $self->transaction->currency->name,
891 'form_defaults.AP_paid_1' => $self->transaction->local_bank_account->chart->accno,
892 'form_defaults.callback' => $self->callback,
905 SL::Controller::BankTransaction - Posting payments to invoices from
906 bank transactions imported earlier
912 =item C<save_single_bank_transaction %params>
914 Takes a bank transaction ID (as parameter C<bank_transaction_id> and
915 tries to post its amount to a certain number of invoices (parameter
916 C<invoice_ids>, an array ref of database IDs to purchase or sales
919 The whole function is wrapped in a database transaction. If an
920 exception occurs the bank transaction is not posted at all. The same
921 is true if the code detects an error during the execution, e.g. a bank
922 transaction that's already been posted earlier. In both cases the
923 database transaction will be rolled back.
925 If warnings but not errors occur the database transaction is still
928 The return value is an error object or C<undef> if the function
929 succeeded. The calling function will collect all warnings and errors
930 and display them in a nicely formatted table if any occurred.
932 An error object is a hash reference containing the following members:
936 =item * C<result> — can be either C<warning> or C<error>. Warnings are
937 displayed slightly different than errors.
939 =item * C<message> — a human-readable message included in the list of
940 errors meant as the description of why the problem happened
942 =item * C<bank_transaction_id>, C<invoice_ids> — the same parameters
943 that the function was called with
945 =item * C<bank_transaction> — the database object
946 (C<SL::DB::BankTransaction>) corresponding to C<bank_transaction_id>
948 =item * C<invoices> — an array ref of the database objects (either
949 C<SL::DB::Invoice> or C<SL::DB::PurchaseInvoice>) corresponding to
958 Niclas Zimmermann E<lt>niclas@kivitendo-premium.deE<gt>,
959 Geoffrey Richardson E<lt>information@richardson-bueren.deE<gt>