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->setup_search_action_bar;
52 $self->render('bank_transactions/search',
53 BANK_ACCOUNTS => $bank_accounts);
59 $self->make_filter_summary;
60 $self->prepare_report;
62 $self->setup_list_all_action_bar;
63 $self->report_generator_list_objects(report => $self->{report}, objects => $self->models->get);
69 if (!$::form->{filter}{bank_account}) {
70 flash('error', t8('No bank account chosen!'));
75 my $sort_by = $::form->{sort_by} || 'transdate';
76 $sort_by = 'transdate' if $sort_by eq 'proposal';
77 $sort_by .= $::form->{sort_dir} ? ' DESC' : ' ASC';
79 my $fromdate = $::locale->parse_date_to_object($::form->{filter}->{fromdate});
80 my $todate = $::locale->parse_date_to_object($::form->{filter}->{todate});
81 $todate->add( days => 1 ) if $todate;
84 push @where, (transdate => { ge => $fromdate }) if ($fromdate);
85 push @where, (transdate => { lt => $todate }) if ($todate);
86 my $bank_account = SL::DB::Manager::BankAccount->find_by( id => $::form->{filter}{bank_account} );
87 # bank_transactions no younger than starting date,
88 # including starting date (same search behaviour as fromdate)
89 # but OPEN invoices to be matched may be from before
90 if ( $bank_account->reconciliation_starting_date ) {
91 push @where, (transdate => { ge => $bank_account->reconciliation_starting_date });
94 my $bank_transactions = SL::DB::Manager::BankTransaction->get_all(
95 with_objects => [ 'local_bank_account', 'currency' ],
99 amount => {ne => \'invoice_amount'},
100 local_bank_account_id => $::form->{filter}{bank_account},
104 $main::lxdebug->message(LXDebug->DEBUG2(),"count bt=".scalar(@{$bank_transactions}." bank_account=".$bank_account->id." chart=".$bank_account->chart_id));
106 # credit notes have a negative amount, treat differently
107 my $all_open_ar_invoices = SL::DB::Manager::Invoice ->get_all(where => [ or => [ amount => { gt => \'paid' },
108 and => [ type => 'credit_note',
109 amount => { lt => \'paid' }
113 with_objects => ['customer','payment_terms']);
115 my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => [amount => { ne => \'paid' }], with_objects => ['vendor' ,'payment_terms']);
116 my $all_open_sepa_export_items = SL::DB::Manager::SepaExportItem->get_all(where => [chart_id => $bank_account->chart_id ,
117 'sepa_export.executed' => 0, 'sepa_export.closed' => 0 ], with_objects => ['sepa_export']);
118 $main::lxdebug->message(LXDebug->DEBUG2(),"count sepaexport=".scalar(@{$all_open_sepa_export_items}));
120 my @all_open_invoices;
121 # filter out invoices with less than 1 cent outstanding
122 push @all_open_invoices, map { $_->{is_ar}=1 ; $_ } grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ar_invoices };
123 push @all_open_invoices, map { $_->{is_ar}=0 ; $_ } grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
124 $main::lxdebug->message(LXDebug->DEBUG2(),"bank_account=".$::form->{filter}{bank_account}." invoices: ".scalar(@{ $all_open_ar_invoices }).
125 " + ".scalar(@{ $all_open_ap_invoices })." non fully paid=".scalar(@all_open_invoices)." transactions=".scalar(@{ $bank_transactions }));
127 my @all_sepa_invoices;
128 my @all_non_sepa_invoices;
130 # first collect sepa export items to open invoices
131 foreach my $open_invoice (@all_open_invoices){
132 # my @items = grep { $_->ap_id == $open_invoice->id || $_->ar_id == $open_invoice->id } @{$all_open_sepa_export_items};
133 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount,2);
134 $open_invoice->{skonto_type} = 'without_skonto';
135 foreach ( @{$all_open_sepa_export_items}) {
136 if ( $_->ap_id == $open_invoice->id || $_->ar_id == $open_invoice->id ) {
137 my $factor = ($_->ar_id == $open_invoice->id?1:-1);
138 $main::lxdebug->message(LXDebug->DEBUG2(),"exitem=".$_->id." for invoice ".$open_invoice->id." factor=".$factor);
139 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount*$factor,2);
140 $open_invoice->{sepa_export_item} = $_ ;
141 $open_invoice->{skonto_type} = $_->payment_type;
142 $sepa_exports{$_->sepa_export_id} ||= { count => 0, is_ar => 0, amount => 0, proposed => 0, invoices => [], item => $_ };
143 $sepa_exports{$_->sepa_export_id}->{count}++ ;
144 $sepa_exports{$_->sepa_export_id}->{is_ar}++ if $_->ar_id == $open_invoice->id;
145 $sepa_exports{$_->sepa_export_id}->{amount} += $_->amount * $factor;
146 push @{ $sepa_exports{$_->sepa_export_id}->{invoices} }, $open_invoice;
147 #$main::lxdebug->message(LXDebug->DEBUG2(),"amount for export id ".$_->sepa_export_id." = ".
148 # $sepa_exports{$_->sepa_export_id}->{amount}." count = ".
149 # $sepa_exports{$_->sepa_export_id}->{count}." is_ar = ".
150 # $sepa_exports{$_->sepa_export_id}->{is_ar} );
151 push @all_sepa_invoices , $open_invoice;
154 push @all_non_sepa_invoices , $open_invoice if ! $open_invoice->{sepa_export_item};
157 # try to match each bank_transaction with each of the possible open invoices
161 foreach my $bt (@{ $bank_transactions }) {
162 ## 5 Stellen hinter dem Komma auf 2 Stellen reduzieren
163 $bt->amount($bt->amount*1);
164 $bt->invoice_amount($bt->invoice_amount*1);
165 $main::lxdebug->message(LXDebug->DEBUG2(),"BT ".$bt->id." amount=".$bt->amount." invoice_amount=".$bt->invoice_amount." remote=". $bt->{remote_name});
167 $bt->{proposals} = [];
168 $bt->{rule_matches} = [];
170 $bt->{remote_name} .= $bt->{remote_name_1} if $bt->{remote_name_1};
172 if ( $self->is_collective_transaction($bt) ) {
173 foreach ( keys %sepa_exports) {
174 #$main::lxdebug->message(LXDebug->DEBUG2(),"Exp ID=".$_." compare sum amount ".($sepa_exports{$_}->{amount} *1) ." == ".($bt->amount * 1));
175 if ( $bt->transaction_code eq '191' && abs(($sepa_exports{$_}->{amount} * 1) - ($bt->amount * 1)) < 0.01 ) {
177 @{$bt->{proposals}} = @{$sepa_exports{$_}->{invoices}};
178 $bt->{agreement} = 20;
179 push(@{$bt->{rule_matches}},'sepa_export_item(20)');
180 $sepa_exports{$_}->{proposed}=1;
181 #$main::lxdebug->message(LXDebug->DEBUG2(),"has ".scalar($bt->{proposals})." invoices");
182 push(@proposals, $bt);
187 next unless $bt->{remote_name}; # bank has no name, usually fees, use create invoice to assign
189 foreach ( @{$all_open_sepa_export_items}) {
190 last if scalar (@all_sepa_invoices) == 0;
191 foreach my $open_invoice (@all_sepa_invoices){
192 $open_invoice->{agreement} = 0;
193 $open_invoice->{rule_matches} ='';
194 if ( $_->ap_id == $open_invoice->id || $_->ar_id == $open_invoice->id ) {
195 #$main::lxdebug->message(LXDebug->DEBUG2(),"exitem2=".$_->id." for invoice ".$open_invoice->id);
196 my $factor = ( $_->ar_id == $open_invoice->id?1:-1);
197 $_->amount($_->amount*1);
198 #$main::lxdebug->message(LXDebug->DEBUG2(),"remote account '".$bt->{remote_account_number}."' bt_amount=".$bt->amount." factor=".$factor);
199 #$main::lxdebug->message(LXDebug->DEBUG2(),"compare with '".$_->vc_iban."' amount=".$_->amount);
200 if ( $bt->{remote_account_number} eq $_->vc_iban && abs(abs($_->amount) - abs($bt->amount)) < 0.01 ) {
202 $iban = $open_invoice->customer->iban if $open_invoice->is_sales;
203 $iban = $open_invoice->vendor->iban if ! $open_invoice->is_sales;
204 if($bt->{remote_account_number} eq $iban && abs(abs($open_invoice->amount) - abs($bt->amount)) < 0.01 ) {
205 ($open_invoice->{agreement}, $open_invoice->{rule_matches}) = $bt->get_agreement_with_invoice($open_invoice);
206 $open_invoice->{agreement} += 5;
207 $open_invoice->{rule_matches} .= 'sepa_export_item(5) ';
208 $main::lxdebug->message(LXDebug->DEBUG2(),"sepa invoice_id=".$open_invoice->id." agreement=".$open_invoice->{agreement}." rules matches=".$open_invoice->{rule_matches});
209 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount*$factor,2);
216 # try to match the current $bt to each of the open_invoices, saving the
217 # results of get_agreement_with_invoice in $open_invoice->{agreement} and
218 # $open_invoice->{rule_matches}.
220 # The values are overwritten each time a new bt is checked, so at the end
221 # of each bt the likely results are filtered and those values are stored in
222 # the arrays $bt->{proposals} and $bt->{rule_matches}, and the agreement
223 # score is stored in $bt->{agreement}
225 foreach my $open_invoice (@all_non_sepa_invoices){
226 ($open_invoice->{agreement}, $open_invoice->{rule_matches}) = $bt->get_agreement_with_invoice($open_invoice);
227 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount*($open_invoice->{is_ar}?1:-1),2);
228 $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;
232 my $min_agreement = 3; # suggestions must have at least this score
234 my $max_agreement = max map { $_->{agreement} } @all_open_invoices;
236 # add open_invoices with highest agreement into array $bt->{proposals}
237 if ( $max_agreement >= $min_agreement ) {
238 $bt->{proposals} = [ grep { $_->{agreement} == $max_agreement } @all_open_invoices ];
239 $bt->{agreement} = $max_agreement; #scalar @{ $bt->{proposals} } ? $agreement + 1 : '';
241 # store the rule_matches in a separate array, so they can be displayed in template
242 foreach ( @{ $bt->{proposals} } ) {
243 push(@{$bt->{rule_matches}}, $_->{rule_matches});
249 # separate filter for proposals (second tab, agreement >= 5 and exactly one match)
250 # to qualify as a proposal there has to be
251 # * agreement >= 5 TODO: make threshold configurable in configuration
252 # * there must be only one exact match
253 # * depending on whether sales or purchase the amount has to have the correct sign (so Gutschriften don't work?)
254 my $proposal_threshold = 5;
255 my @otherproposals = grep {
256 ($_->{agreement} >= $proposal_threshold)
257 && (1 == scalar @{ $_->{proposals} })
258 && (@{ $_->{proposals} }[0]->is_sales ? abs(@{ $_->{proposals} }[0]->amount - $_->amount) < 0.01
259 : abs(@{ $_->{proposals} }[0]->amount + $_->amount) < 0.01)
260 } @{ $bank_transactions };
262 push ( @proposals, @otherproposals);
264 # sort bank transaction proposals by quality (score) of proposal
265 $bank_transactions = [ sort { $a->{agreement} <=> $b->{agreement} } @{ $bank_transactions } ] if $::form->{sort_by} eq 'proposal' and $::form->{sort_dir} == 1;
266 $bank_transactions = [ sort { $b->{agreement} <=> $a->{agreement} } @{ $bank_transactions } ] if $::form->{sort_by} eq 'proposal' and $::form->{sort_dir} == 0;
268 $::request->layout->add_javascripts("kivi.BankTransaction.js");
269 $self->render('bank_transactions/list',
270 title => t8('Bank transactions MT940'),
271 BANK_TRANSACTIONS => $bank_transactions,
272 PROPOSALS => \@proposals,
273 bank_account => $bank_account,
274 ui_tab => scalar(@proposals) > 0?1:0,
278 sub action_assign_invoice {
281 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
283 $self->render('bank_transactions/assign_invoice',
285 title => t8('Assign invoice'),);
288 sub action_create_invoice {
290 my %myconfig = %main::myconfig;
292 $self->transaction(SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id}));
294 my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(account_number => $self->transaction->{remote_account_number});
295 my $use_vendor_filter = $self->transaction->{remote_account_number} && $vendor_of_transaction;
297 my $templates = SL::DB::Manager::RecordTemplate->get_all(
298 where => [ template_type => 'ap_transaction' ],
299 with_objects => [ qw(employee vendor) ],
303 $templates = [ grep { $_->vendor_id == $vendor_of_transaction->id } @{ $templates } ] if $use_vendor_filter;
305 $self->callback($self->url_for(
307 'filter.bank_account' => $::form->{filter}->{bank_account},
308 'filter.todate' => $::form->{filter}->{todate},
309 'filter.fromdate' => $::form->{filter}->{fromdate},
313 'bank_transactions/create_invoice',
315 title => t8('Create invoice'),
316 TEMPLATES => $templates,
317 vendor_id => $use_vendor_filter ? $vendor_of_transaction->id : undef,
318 vendor_name => $use_vendor_filter ? $vendor_of_transaction->name : undef,
322 sub action_ajax_payment_suggestion {
325 # based on a BankTransaction ID and a Invoice or PurchaseInvoice ID passed via $::form,
326 # create an HTML blob to be used by the js function add_invoices in templates/webpages/bank_transactions/list.html
327 # and return encoded as JSON
329 my $bt = SL::DB::Manager::BankTransaction->find_by( id => $::form->{bt_id} );
330 my $invoice = SL::DB::Manager::Invoice->find_by( id => $::form->{prop_id} ) || SL::DB::Manager::PurchaseInvoice->find_by( id => $::form->{prop_id} );
332 die unless $bt and $invoice;
334 my @select_options = $invoice->get_payment_select_options_for_bank_transaction($::form->{bt_id});
337 $html = $self->render(
338 'bank_transactions/_payment_suggestion', { output => 0 },
339 bt_id => $::form->{bt_id},
340 prop_id => $::form->{prop_id},
342 SELECT_OPTIONS => \@select_options,
345 $self->render(\ SL::JSON::to_json( { 'html' => "$html" } ), { layout => 0, type => 'json', process => 0 });
348 sub action_filter_templates {
351 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
352 my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(account_number => $self->{transaction}->{remote_account_number});
355 push @filter, ('vendor.id' => $::form->{vendor_id}) if $::form->{vendor_id};
356 push @filter, ('vendor.name' => { ilike => '%' . $::form->{vendor} . '%' }) if $::form->{vendor};
358 my $templates = SL::DB::Manager::RecordTemplate->get_all(
359 where => [ template_type => 'ap_transaction', (or => \@filter) x !!@filter ],
360 with_objects => [ qw(employee vendor) ],
363 $::form->{filter} //= {};
365 $self->callback($self->url_for(
367 'filter.bank_account' => $::form->{filter}->{bank_account},
368 'filter.todate' => $::form->{filter}->{todate},
369 'filter.fromdate' => $::form->{filter}->{fromdate},
372 my $output = $self->render(
373 'bank_transactions/_template_list',
375 TEMPLATES => $templates,
378 $self->render(\to_json({ html => $output }), { type => 'json', process => 0 });
381 sub action_ajax_add_list {
384 my @where_sale = (amount => { ne => \'paid' });
385 my @where_purchase = (amount => { ne => \'paid' });
387 if ($::form->{invnumber}) {
388 push @where_sale, (invnumber => { ilike => like($::form->{invnumber})});
389 push @where_purchase, (invnumber => { ilike => like($::form->{invnumber})});
392 if ($::form->{amount}) {
393 push @where_sale, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
394 push @where_purchase, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
397 if ($::form->{vcnumber}) {
398 push @where_sale, ('customer.customernumber' => { ilike => like($::form->{vcnumber})});
399 push @where_purchase, ('vendor.vendornumber' => { ilike => like($::form->{vcnumber})});
402 if ($::form->{vcname}) {
403 push @where_sale, ('customer.name' => { ilike => like($::form->{vcname})});
404 push @where_purchase, ('vendor.name' => { ilike => like($::form->{vcname})});
407 if ($::form->{transdatefrom}) {
408 my $fromdate = $::locale->parse_date_to_object($::form->{transdatefrom});
409 if ( ref($fromdate) eq 'DateTime' ) {
410 push @where_sale, ('transdate' => { ge => $fromdate});
411 push @where_purchase, ('transdate' => { ge => $fromdate});
415 if ($::form->{transdateto}) {
416 my $todate = $::locale->parse_date_to_object($::form->{transdateto});
417 if ( ref($todate) eq 'DateTime' ) {
418 $todate->add(days => 1);
419 push @where_sale, ('transdate' => { lt => $todate});
420 push @where_purchase, ('transdate' => { lt => $todate});
424 my $all_open_ar_invoices = SL::DB::Manager::Invoice ->get_all(where => \@where_sale, with_objects => 'customer');
425 my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => \@where_purchase, with_objects => 'vendor');
427 my @all_open_invoices = @{ $all_open_ar_invoices };
428 # add ap invoices, filtering out subcent open amounts
429 push @all_open_invoices, grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
431 @all_open_invoices = sort { $a->id <=> $b->id } @all_open_invoices;
433 my $output = $self->render(
434 'bank_transactions/add_list',
436 INVOICES => \@all_open_invoices,
439 my %result = ( count => 0, html => $output );
441 $self->render(\to_json(\%result), { type => 'json', process => 0 });
444 sub action_ajax_accept_invoices {
447 my @selected_invoices;
448 foreach my $invoice_id (@{ $::form->{invoice_id} || [] }) {
449 my $invoice_object = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
450 push @selected_invoices, $invoice_object;
454 'bank_transactions/invoices',
456 INVOICES => \@selected_invoices,
457 bt_id => $::form->{bt_id},
464 return 0 if !$::form->{invoice_ids};
466 my %invoice_hash = %{ delete $::form->{invoice_ids} }; # each key (the bt line with a bt_id) contains an array of invoice_ids
468 # e.g. three partial payments with bt_ids 54, 55 and 56 for invoice with id 74:
481 # or if the payment with bt_id 44 is used to pay invoices with ids 50, 51 and 52
483 # '44' => [ '50', '51', 52' ]
486 $::form->{invoice_skontos} ||= {}; # hash of arrays containing the payment types, could be empty
488 # a bank_transaction may be assigned to several invoices, i.e. a customer
489 # might pay several open invoices with one transaction
495 if ( $::form->{proposal_ids} ) {
496 foreach (@{ $::form->{proposal_ids} }) {
497 my $bank_transaction_id = $_;
498 my $invoice_ids = $invoice_hash{$_};
499 push @{ $self->problems }, $self->save_single_bank_transaction(
500 bank_transaction_id => $bank_transaction_id,
501 invoice_ids => $invoice_ids,
502 sources => ($::form->{sources} // {})->{$_},
503 memos => ($::form->{memos} // {})->{$_},
505 $count += scalar( @{$invoice_ids} );
508 while ( my ($bank_transaction_id, $invoice_ids) = each(%invoice_hash) ) {
509 push @{ $self->problems }, $self->save_single_bank_transaction(
510 bank_transaction_id => $bank_transaction_id,
511 invoice_ids => $invoice_ids,
512 sources => [ map { $::form->{"sources_${bank_transaction_id}_${_}"} } @{ $invoice_ids } ],
513 memos => [ map { $::form->{"memos_${bank_transaction_id}_${_}"} } @{ $invoice_ids } ],
515 $count += scalar( @{$invoice_ids} );
518 foreach (@{ $self->problems }) {
519 $count-- if $_->{result} eq 'error';
524 sub action_save_invoices {
526 my $count = $self->save_invoices();
528 flash('ok', t8('#1 invoice(s) saved.', $count));
530 $self->action_list();
533 sub action_save_proposals {
536 if ( $::form->{proposal_ids} ) {
537 my $propcount = scalar(@{ $::form->{proposal_ids} });
538 if ( $propcount > 0 ) {
539 my $count = $self->save_invoices();
541 flash('ok', t8('#1 proposal(s) with #2 invoice(s) saved.', $propcount, $count));
544 $self->action_list();
548 sub is_collective_transaction {
549 my ($self, $bt) = @_;
550 return $bt->transaction_code eq "191";
553 sub save_single_bank_transaction {
554 my ($self, %params) = @_;
558 bank_transaction => SL::DB::Manager::BankTransaction->find_by(id => $params{bank_transaction_id}),
562 if (!$data{bank_transaction}) {
566 message => $::locale->text('The ID #1 is not a valid database ID.', $data{bank_transaction_id}),
573 my $bt_id = $data{bank_transaction_id};
574 my $bank_transaction = $data{bank_transaction};
575 my $sign = $bank_transaction->amount < 0 ? -1 : 1;
576 my $amount_of_transaction = $sign * $bank_transaction->amount;
577 my $payment_received = $bank_transaction->amount > 0;
578 my $payment_sent = $bank_transaction->amount < 0;
581 foreach my $invoice_id (@{ $params{invoice_ids} }) {
582 my $invoice = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
587 message => $::locale->text("The ID #1 is not a valid database ID.", $invoice_id),
590 push @{ $data{invoices} }, $invoice;
593 if ( $payment_received
594 && any { ( $_->is_sales && ($_->amount < 0))
595 || (!$_->is_sales && ($_->amount > 0))
596 } @{ $data{invoices} }) {
600 message => $::locale->text("Received payments can only be posted for sales invoices and purchase credit notes."),
605 && any { ( $_->is_sales && ($_->amount > 0))
606 || (!$_->is_sales && ($_->amount < 0) && ($_->invoice_type eq 'purchase_invoice'))
607 } @{ $data{invoices} }) {
611 message => $::locale->text("Sent payments can only be posted for purchase invoices and sales credit notes."),
615 my $max_invoices = scalar(@{ $data{invoices} });
618 foreach my $invoice (@{ $data{invoices} }) {
619 my $source = ($data{sources} // [])->[$n_invoices];
620 my $memo = ($data{memos} // [])->[$n_invoices];
624 # Check if bank_transaction already has a link to the invoice, may only be linked once per invoice
625 # This might be caused by the user reloading a page and resending the form
626 if (_existing_record_link($bank_transaction, $invoice)) {
630 message => $::locale->text("Bank transaction with id #1 has already been linked to #2.", $bank_transaction->id, $invoice->displayable_name),
634 if (!$amount_of_transaction && $invoice->open_amount) {
638 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."),
643 if ( defined $::form->{invoice_skontos}->{"$bt_id"} ) {
644 $payment_type = shift(@{ $::form->{invoice_skontos}->{"$bt_id"} });
646 $payment_type = 'without_skonto';
650 # pay invoice or go to the next bank transaction if the amount is not sufficiently high
651 if ($invoice->open_amount <= $amount_of_transaction && $n_invoices < $max_invoices) {
652 my $open_amount = ($payment_type eq 'with_skonto_pt'?$invoice->amount_less_skonto:$invoice->open_amount);
653 # first calculate new bank transaction amount ...
654 if ($invoice->is_sales) {
655 $amount_of_transaction -= $sign * $open_amount;
656 $bank_transaction->invoice_amount($bank_transaction->invoice_amount + $open_amount);
658 $amount_of_transaction += $sign * $open_amount;
659 $bank_transaction->invoice_amount($bank_transaction->invoice_amount - $open_amount);
661 # ... and then pay the invoice
662 $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
663 trans_id => $invoice->id,
664 amount => $open_amount,
665 payment_type => $payment_type,
668 transdate => $bank_transaction->transdate->to_kivitendo);
670 # use the whole amount of the bank transaction for the invoice, overpay the invoice if necessary
672 # this catches credit_notes and negative sales invoices
673 if ( $invoice->is_sales && $invoice->amount < 0 ) {
674 # $invoice->open_amount is negative for credit_notes
675 # $bank_transaction->amount is negative for outgoing transactions
676 # so $amount_of_transaction is negative but needs positive
677 $amount_of_transaction *= -1;
679 } elsif (!$invoice->is_sales && $invoice->invoice_type eq 'ap_transaction' ) {
680 # $invoice->open_amount may be negative for ap_transaction but may be positiv for negativ ap_transaction
681 # if $invoice->open_amount is negative $bank_transaction->amount is positve
682 # if $invoice->open_amount is positive $bank_transaction->amount is negative
683 # but amount of transaction is for both positive
684 $amount_of_transaction *= -1 if $invoice->open_amount == - $amount_of_transaction;
687 my $overpaid_amount = $amount_of_transaction - $invoice->open_amount;
688 $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
689 trans_id => $invoice->id,
690 amount => $amount_of_transaction,
691 payment_type => $payment_type,
694 transdate => $bank_transaction->transdate->to_kivitendo);
695 $bank_transaction->invoice_amount($bank_transaction->amount);
696 $amount_of_transaction = 0;
698 if ($overpaid_amount >= 0.01) {
702 message => $::locale->text('Invoice #1 was overpaid by #2.', $invoice->invnumber, $::form->format_amount(\%::myconfig, $overpaid_amount, 2)),
706 # Record a record link from the bank transaction to the invoice
708 from_table => 'bank_transactions',
710 to_table => $invoice->is_sales ? 'ar' : 'ap',
711 to_id => $invoice->id,
714 SL::DB::RecordLink->new(@props)->save;
716 # "close" a sepa_export_item if it exists
717 # code duplicated in action_save_proposals!
718 # currently only works, if there is only exactly one open sepa_export_item
719 if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
720 if ( scalar @$seis == 1 ) {
721 # moved the execution and the check for sepa_export into a method,
722 # this isn't part of a transaction, though
723 $seis->[0]->set_executed if $invoice->id == $seis->[0]->arap_id;
728 $bank_transaction->save;
730 # 'undef' means 'no error' here.
735 my $rez = $data{bank_transaction}->db->with_transaction(sub {
737 $error = $worker->();
748 # Rollback Fehler nicht weiterreichen
752 return grep { $_ } ($error, @warnings);
760 $::auth->assert('bank_transaction');
767 sub make_filter_summary {
770 my $filter = $::form->{filter} || {};
774 [ $filter->{"transdate:date::ge"}, $::locale->text('Transdate') . " " . $::locale->text('From Date') ],
775 [ $filter->{"transdate:date::le"}, $::locale->text('Transdate') . " " . $::locale->text('To Date') ],
776 [ $filter->{"valutadate:date::ge"}, $::locale->text('Valutadate') . " " . $::locale->text('From Date') ],
777 [ $filter->{"valutadate:date::le"}, $::locale->text('Valutadate') . " " . $::locale->text('To Date') ],
778 [ $filter->{"amount:number"}, $::locale->text('Amount') ],
779 [ $filter->{"bank_account_id:integer"}, $::locale->text('Local bank account') ],
783 push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
786 $self->{filter_summary} = join ', ', @filter_strings;
792 my $callback = $self->models->get_callback;
794 my $report = SL::ReportGenerator->new(\%::myconfig, $::form);
795 $self->{report} = $report;
797 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);
798 my @sortable = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount purpose local_account_number local_bank_code);
801 transdate => { sub => sub { $_[0]->transdate_as_date } },
802 valutadate => { sub => sub { $_[0]->valutadate_as_date } },
804 remote_account_number => { },
805 remote_bank_code => { },
806 amount => { sub => sub { $_[0]->amount_as_number },
808 invoice_amount => { sub => sub { $_[0]->invoice_amount_as_number },
810 invoices => { sub => sub { $_[0]->linked_invoices } },
811 currency => { sub => sub { $_[0]->currency->name } },
813 local_account_number => { sub => sub { $_[0]->local_bank_account->account_number } },
814 local_bank_code => { sub => sub { $_[0]->local_bank_account->bank_code } },
815 local_bank_name => { sub => sub { $_[0]->local_bank_account->name } },
819 map { $column_defs{$_}->{text} ||= $::locale->text( $self->models->get_sort_spec->{$_}->{title} ) } keys %column_defs;
821 $report->set_options(
822 std_column_visibility => 1,
823 controller_class => 'BankTransaction',
824 output_format => 'HTML',
825 top_info_text => $::locale->text('Bank transactions'),
826 title => $::locale->text('Bank transactions'),
827 allow_pdf_export => 1,
828 allow_csv_export => 1,
830 $report->set_columns(%column_defs);
831 $report->set_column_order(@columns);
832 $report->set_export_options(qw(list_all filter));
833 $report->set_options_from_form;
834 $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
835 $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
837 my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted();
839 $report->set_options(
840 raw_top_info_text => $self->render('bank_transactions/report_top', { output => 0 }, BANK_ACCOUNTS => $bank_accounts),
841 raw_bottom_info_text => $self->render('bank_transactions/report_bottom', { output => 0 }),
845 sub _existing_record_link {
846 my ($bt, $invoice) = @_;
848 # check whether a record link from banktransaction $bt already exists to
849 # invoice $invoice, returns 1 if that is the case
851 die unless $bt->isa("SL::DB::BankTransaction") && ( $invoice->isa("SL::DB::Invoice") || $invoice->isa("SL::DB::PurchaseInvoice") );
853 my $linked_record_to_table = $invoice->is_sales ? 'Invoice' : 'PurchaseInvoice';
854 my $linked_records = $bt->linked_records( direction => 'to', to => $linked_record_to_table, query => [ id => $invoice->id ] );
856 return @$linked_records ? 1 : 0;
859 sub init_problems { [] }
864 SL::Controller::Helper::GetModels->new(
869 dir => 0, # 1 = ASC, 0 = DESC : default sort is newest at top
871 transdate => t8('Transdate'),
872 remote_name => t8('Remote name'),
873 amount => t8('Amount'),
874 invoice_amount => t8('Assigned'),
875 invoices => t8('Linked invoices'),
876 valutadate => t8('Valutadate'),
877 remote_account_number => t8('Remote account number'),
878 remote_bank_code => t8('Remote bank code'),
879 currency => t8('Currency'),
880 purpose => t8('Purpose'),
881 local_account_number => t8('Local account number'),
882 local_bank_code => t8('Local bank code'),
883 local_bank_name => t8('Bank account'),
885 with_objects => [ 'local_bank_account', 'currency' ],
889 sub load_ap_record_template_url {
890 my ($self, $template) = @_;
892 return $self->url_for(
893 controller => 'ap.pl',
894 action => 'load_record_template',
896 'form_defaults.amount_1' => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
897 'form_defaults.transdate' => $self->transaction->transdate_as_date,
898 'form_defaults.duedate' => $self->transaction->transdate_as_date,
899 'form_defaults.no_payment_bookings' => 1,
900 'form_defaults.paid_1_suggestion' => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
901 'form_defaults.AP_paid_1_suggestion' => $self->transaction->local_bank_account->chart->accno,
902 'form_defaults.callback' => $self->callback,
906 sub setup_search_action_bar {
907 my ($self, %params) = @_;
909 for my $bar ($::request->layout->get('actionbar')) {
913 submit => [ '#search_form', { action => 'BankTransaction/list' } ],
914 accesskey => 'enter',
920 sub setup_list_all_action_bar {
921 my ($self, %params) = @_;
923 for my $bar ($::request->layout->get('actionbar')) {
927 submit => [ '#filter_form', { action => 'BankTransaction/list_all' } ],
928 accesskey => 'enter',
943 SL::Controller::BankTransaction - Posting payments to invoices from
944 bank transactions imported earlier
950 =item C<save_single_bank_transaction %params>
952 Takes a bank transaction ID (as parameter C<bank_transaction_id> and
953 tries to post its amount to a certain number of invoices (parameter
954 C<invoice_ids>, an array ref of database IDs to purchase or sales
957 The whole function is wrapped in a database transaction. If an
958 exception occurs the bank transaction is not posted at all. The same
959 is true if the code detects an error during the execution, e.g. a bank
960 transaction that's already been posted earlier. In both cases the
961 database transaction will be rolled back.
963 If warnings but not errors occur the database transaction is still
966 The return value is an error object or C<undef> if the function
967 succeeded. The calling function will collect all warnings and errors
968 and display them in a nicely formatted table if any occurred.
970 An error object is a hash reference containing the following members:
974 =item * C<result> — can be either C<warning> or C<error>. Warnings are
975 displayed slightly different than errors.
977 =item * C<message> — a human-readable message included in the list of
978 errors meant as the description of why the problem happened
980 =item * C<bank_transaction_id>, C<invoice_ids> — the same parameters
981 that the function was called with
983 =item * C<bank_transaction> — the database object
984 (C<SL::DB::BankTransaction>) corresponding to C<bank_transaction_id>
986 =item * C<invoices> — an array ref of the database objects (either
987 C<SL::DB::Invoice> or C<SL::DB::PurchaseInvoice>) corresponding to
996 Niclas Zimmermann E<lt>niclas@kivitendo-premium.deE<gt>,
997 Geoffrey Richardson E<lt>information@richardson-bueren.deE<gt>