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},
105 # credit notes have a negative amount, treat differently
106 my $all_open_ar_invoices = SL::DB::Manager::Invoice ->get_all(where => [ or => [ amount => { gt => \'paid' },
107 and => [ type => 'credit_note',
108 amount => { lt => \'paid' }
112 with_objects => ['customer','payment_terms']);
114 my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => [amount => { ne => \'paid' }], with_objects => ['vendor' ,'payment_terms']);
115 my $all_open_sepa_export_items = SL::DB::Manager::SepaExportItem->get_all(where => [chart_id => $bank_account->chart_id ,
116 'sepa_export.executed' => 0, 'sepa_export.closed' => 0 ], with_objects => ['sepa_export']);
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 };
124 # first collect sepa export items to open invoices
125 foreach my $open_invoice (@all_open_invoices){
126 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount,2);
127 $open_invoice->{skonto_type} = 'without_skonto';
128 foreach ( @{$all_open_sepa_export_items}) {
129 if ( $_->ap_id == $open_invoice->id || $_->ar_id == $open_invoice->id ) {
130 my $factor = ($_->ar_id == $open_invoice->id ? 1 : -1);
131 #$main::lxdebug->message(LXDebug->DEBUG2(),"sepa_exitem=".$_->id." for invoice ".$open_invoice->id." factor=".$factor);
132 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount*$factor,2);
133 $open_invoice->{skonto_type} = $_->payment_type;
134 $sepa_exports{$_->sepa_export_id} ||= { count => 0, is_ar => 0, amount => 0, proposed => 0, invoices => [], item => $_ };
135 $sepa_exports{$_->sepa_export_id}->{count}++;
136 $sepa_exports{$_->sepa_export_id}->{is_ar}++ if $_->ar_id == $open_invoice->id;
137 $sepa_exports{$_->sepa_export_id}->{amount} += $_->amount * $factor;
138 push @{ $sepa_exports{$_->sepa_export_id}->{invoices} }, $open_invoice;
143 # try to match each bank_transaction with each of the possible open invoices
147 foreach my $bt (@{ $bank_transactions }) {
148 ## 5 Stellen hinter dem Komma auf 2 Stellen reduzieren
149 $bt->amount($bt->amount*1);
150 $bt->invoice_amount($bt->invoice_amount*1);
152 $bt->{proposals} = [];
153 $bt->{rule_matches} = [];
155 $bt->{remote_name} .= $bt->{remote_name_1} if $bt->{remote_name_1};
157 if ( $bt->is_batch_transaction ) {
158 foreach ( keys %sepa_exports) {
159 if ( abs(($sepa_exports{$_}->{amount} * 1) - ($bt->amount * 1)) < 0.01 ) {
161 @{$bt->{proposals}} = @{$sepa_exports{$_}->{invoices}};
162 $bt->{sepa_export_ok} = 1;
163 $sepa_exports{$_}->{proposed}=1;
164 push(@proposals, $bt);
168 # batch transaction has no remotename !!
170 next unless $bt->{remote_name}; # bank has no name, usually fees, use create invoice to assign
173 # try to match the current $bt to each of the open_invoices, saving the
174 # results of get_agreement_with_invoice in $open_invoice->{agreement} and
175 # $open_invoice->{rule_matches}.
177 # The values are overwritten each time a new bt is checked, so at the end
178 # of each bt the likely results are filtered and those values are stored in
179 # the arrays $bt->{proposals} and $bt->{rule_matches}, and the agreement
180 # score is stored in $bt->{agreement}
182 foreach my $open_invoice (@all_open_invoices) {
183 ($open_invoice->{agreement}, $open_invoice->{rule_matches}) = $bt->get_agreement_with_invoice($open_invoice);
184 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,
185 $open_invoice->amount * ($open_invoice->{is_ar} ? 1 : -1), 2);
189 my $min_agreement = 3; # suggestions must have at least this score
191 my $max_agreement = max map { $_->{agreement} } @all_open_invoices;
193 # add open_invoices with highest agreement into array $bt->{proposals}
194 if ( $max_agreement >= $min_agreement ) {
195 $bt->{proposals} = [ grep { $_->{agreement} == $max_agreement } @all_open_invoices ];
196 $bt->{agreement} = $max_agreement; #scalar @{ $bt->{proposals} } ? $agreement + 1 : '';
198 # store the rule_matches in a separate array, so they can be displayed in template
199 foreach ( @{ $bt->{proposals} } ) {
200 push(@{$bt->{rule_matches}}, $_->{rule_matches});
206 # separate filter for proposals (second tab, agreement >= 5 and exactly one match)
207 # to qualify as a proposal there has to be
208 # * agreement >= 5 TODO: make threshold configurable in configuration
209 # * there must be only one exact match
210 # * depending on whether sales or purchase the amount has to have the correct sign (so Gutschriften don't work?)
211 my $proposal_threshold = 5;
212 my @otherproposals = grep {
213 ($_->{agreement} >= $proposal_threshold)
214 && (1 == scalar @{ $_->{proposals} })
215 && (@{ $_->{proposals} }[0]->is_sales ? abs(@{ $_->{proposals} }[0]->amount - $_->amount) < 0.01
216 : abs(@{ $_->{proposals} }[0]->amount + $_->amount) < 0.01)
217 } @{ $bank_transactions };
219 push @proposals, @otherproposals;
221 # sort bank transaction proposals by quality (score) of proposal
222 $bank_transactions = [ sort { $a->{agreement} <=> $b->{agreement} } @{ $bank_transactions } ] if $::form->{sort_by} eq 'proposal' and $::form->{sort_dir} == 1;
223 $bank_transactions = [ sort { $b->{agreement} <=> $a->{agreement} } @{ $bank_transactions } ] if $::form->{sort_by} eq 'proposal' and $::form->{sort_dir} == 0;
225 # for testing with t/bank/banktransaction.t :
226 if ( $::form->{dont_render_for_test} ) {
227 return $bank_transactions;
230 $::request->layout->add_javascripts("kivi.BankTransaction.js");
231 $self->render('bank_transactions/list',
232 title => t8('Bank transactions MT940'),
233 BANK_TRANSACTIONS => $bank_transactions,
234 PROPOSALS => \@proposals,
235 bank_account => $bank_account,
236 ui_tab => scalar(@proposals) > 0?1:0,
240 sub action_assign_invoice {
243 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
245 $self->render('bank_transactions/assign_invoice',
247 title => t8('Assign invoice'),);
250 sub action_create_invoice {
252 my %myconfig = %main::myconfig;
254 $self->transaction(SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id}));
256 # This was dead code: We compared vendor.account_name with bank_transaction.iban.
257 # This did never match (Kontonummer != IBAN). It's kivis 09/02 (2013) day
258 # If refactored/improved, also consider that vendor.iban should be normalized
259 # user may like to input strings like: 'AT 3333 3333 2222 1111' -> can be checked strictly
260 # at Vendor code because we need the correct data for all sepa exports.
262 my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(iban => $self->transaction->{remote_account_number});
263 my $use_vendor_filter = $self->transaction->{remote_account_number} && $vendor_of_transaction;
265 my $templates_ap = SL::DB::Manager::RecordTemplate->get_all(
266 where => [ template_type => 'ap_transaction' ],
267 with_objects => [ qw(employee vendor) ],
269 my $templates_gl = SL::DB::Manager::RecordTemplate->get_all(
270 query => [ template_type => 'gl_transaction',
271 chart_id => SL::DB::Manager::BankAccount->find_by(id => $self->transaction->local_bank_account_id)->chart_id,
273 with_objects => [ qw(employee record_template_items) ],
276 # pre filter templates_ap, if we have a vendor match (IBAN eq IBAN) - show and allow user to edit this via gui!
277 $templates_ap = [ grep { $_->vendor_id == $vendor_of_transaction->id } @{ $templates_ap } ] if $use_vendor_filter;
279 $self->callback($self->url_for(
281 'filter.bank_account' => $::form->{filter}->{bank_account},
282 'filter.todate' => $::form->{filter}->{todate},
283 'filter.fromdate' => $::form->{filter}->{fromdate},
287 'bank_transactions/create_invoice',
289 title => t8('Create invoice'),
290 TEMPLATES_GL => $use_vendor_filter ? undef : $templates_gl,
291 TEMPLATES_AP => $templates_ap,
292 vendor_name => $use_vendor_filter ? $vendor_of_transaction->name : undef,
296 sub action_ajax_payment_suggestion {
299 # based on a BankTransaction ID and a Invoice or PurchaseInvoice ID passed via $::form,
300 # create an HTML blob to be used by the js function add_invoices in templates/webpages/bank_transactions/list.html
301 # and return encoded as JSON
303 my $bt = SL::DB::Manager::BankTransaction->find_by( id => $::form->{bt_id} );
304 my $invoice = SL::DB::Manager::Invoice->find_by( id => $::form->{prop_id} ) || SL::DB::Manager::PurchaseInvoice->find_by( id => $::form->{prop_id} );
306 die unless $bt and $invoice;
308 my @select_options = $invoice->get_payment_select_options_for_bank_transaction($::form->{bt_id});
311 $html = $self->render(
312 'bank_transactions/_payment_suggestion', { output => 0 },
313 bt_id => $::form->{bt_id},
314 prop_id => $::form->{prop_id},
316 SELECT_OPTIONS => \@select_options,
319 $self->render(\ SL::JSON::to_json( { 'html' => "$html" } ), { layout => 0, type => 'json', process => 0 });
322 sub action_filter_templates {
325 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
328 push @filter, ('vendor.name' => { ilike => '%' . $::form->{vendor} . '%' }) if $::form->{vendor};
329 push @filter, ('template_name' => { ilike => '%' . $::form->{template} . '%' }) if $::form->{template};
330 push @filter, ('reference' => { ilike => '%' . $::form->{reference} . '%' }) if $::form->{reference};
332 my $templates_ap = SL::DB::Manager::RecordTemplate->get_all(
333 where => [ template_type => 'ap_transaction', (and => \@filter) x !!@filter ],
334 with_objects => [ qw(employee vendor) ],
336 my $templates_gl = SL::DB::Manager::RecordTemplate->get_all(
337 query => [ template_type => 'gl_transaction',
338 chart_id => SL::DB::Manager::BankAccount->find_by(id => $self->transaction->local_bank_account_id)->chart_id,
339 (and => \@filter) x !!@filter
341 with_objects => [ qw(employee record_template_items) ],
344 $::form->{filter} //= {};
346 $self->callback($self->url_for(
348 'filter.bank_account' => $::form->{filter}->{bank_account},
349 'filter.todate' => $::form->{filter}->{todate},
350 'filter.fromdate' => $::form->{filter}->{fromdate},
353 my $output = $self->render(
354 'bank_transactions/_template_list',
356 TEMPLATES_AP => $templates_ap,
357 TEMPLATES_GL => $templates_gl,
360 $self->render(\to_json({ html => $output }), { type => 'json', process => 0 });
363 sub action_ajax_add_list {
366 my @where_sale = (amount => { ne => \'paid' });
367 my @where_purchase = (amount => { ne => \'paid' });
369 if ($::form->{invnumber}) {
370 push @where_sale, (invnumber => { ilike => like($::form->{invnumber})});
371 push @where_purchase, (invnumber => { ilike => like($::form->{invnumber})});
374 if ($::form->{amount}) {
375 push @where_sale, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
376 push @where_purchase, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
379 if ($::form->{vcnumber}) {
380 push @where_sale, ('customer.customernumber' => { ilike => like($::form->{vcnumber})});
381 push @where_purchase, ('vendor.vendornumber' => { ilike => like($::form->{vcnumber})});
384 if ($::form->{vcname}) {
385 push @where_sale, ('customer.name' => { ilike => like($::form->{vcname})});
386 push @where_purchase, ('vendor.name' => { ilike => like($::form->{vcname})});
389 if ($::form->{transdatefrom}) {
390 my $fromdate = $::locale->parse_date_to_object($::form->{transdatefrom});
391 if ( ref($fromdate) eq 'DateTime' ) {
392 push @where_sale, ('transdate' => { ge => $fromdate});
393 push @where_purchase, ('transdate' => { ge => $fromdate});
397 if ($::form->{transdateto}) {
398 my $todate = $::locale->parse_date_to_object($::form->{transdateto});
399 if ( ref($todate) eq 'DateTime' ) {
400 $todate->add(days => 1);
401 push @where_sale, ('transdate' => { lt => $todate});
402 push @where_purchase, ('transdate' => { lt => $todate});
406 my $all_open_ar_invoices = SL::DB::Manager::Invoice ->get_all(where => \@where_sale, with_objects => 'customer');
407 my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => \@where_purchase, with_objects => 'vendor');
409 my @all_open_invoices = @{ $all_open_ar_invoices };
410 # add ap invoices, filtering out subcent open amounts
411 push @all_open_invoices, grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
413 @all_open_invoices = sort { $a->id <=> $b->id } @all_open_invoices;
415 my $output = $self->render(
416 'bank_transactions/add_list',
418 INVOICES => \@all_open_invoices,
421 my %result = ( count => 0, html => $output );
423 $self->render(\to_json(\%result), { type => 'json', process => 0 });
426 sub action_ajax_accept_invoices {
429 my @selected_invoices;
430 foreach my $invoice_id (@{ $::form->{invoice_id} || [] }) {
431 my $invoice_object = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
432 push @selected_invoices, $invoice_object;
436 'bank_transactions/invoices',
438 INVOICES => \@selected_invoices,
439 bt_id => $::form->{bt_id},
446 return 0 if !$::form->{invoice_ids};
448 my %invoice_hash = %{ delete $::form->{invoice_ids} }; # each key (the bt line with a bt_id) contains an array of invoice_ids
450 # e.g. three partial payments with bt_ids 54, 55 and 56 for invoice with id 74:
463 # or if the payment with bt_id 44 is used to pay invoices with ids 50, 51 and 52
465 # '44' => [ '50', '51', 52' ]
468 $::form->{invoice_skontos} ||= {}; # hash of arrays containing the payment types, could be empty
470 # a bank_transaction may be assigned to several invoices, i.e. a customer
471 # might pay several open invoices with one transaction
477 if ( $::form->{proposal_ids} ) {
478 foreach (@{ $::form->{proposal_ids} }) {
479 my $bank_transaction_id = $_;
480 my $invoice_ids = $invoice_hash{$_};
481 push @{ $self->problems }, $self->save_single_bank_transaction(
482 bank_transaction_id => $bank_transaction_id,
483 invoice_ids => $invoice_ids,
484 sources => ($::form->{sources} // {})->{$_},
485 memos => ($::form->{memos} // {})->{$_},
487 $count += scalar( @{$invoice_ids} );
490 while ( my ($bank_transaction_id, $invoice_ids) = each(%invoice_hash) ) {
491 push @{ $self->problems }, $self->save_single_bank_transaction(
492 bank_transaction_id => $bank_transaction_id,
493 invoice_ids => $invoice_ids,
494 sources => [ map { $::form->{"sources_${bank_transaction_id}_${_}"} } @{ $invoice_ids } ],
495 memos => [ map { $::form->{"memos_${bank_transaction_id}_${_}"} } @{ $invoice_ids } ],
497 $count += scalar( @{$invoice_ids} );
500 foreach (@{ $self->problems }) {
501 $count-- if $_->{result} eq 'error';
506 sub action_save_invoices {
508 my $count = $self->save_invoices();
510 flash('ok', t8('#1 invoice(s) saved.', $count));
512 $self->action_list();
515 sub action_save_proposals {
518 if ( $::form->{proposal_ids} ) {
519 my $propcount = scalar(@{ $::form->{proposal_ids} });
520 if ( $propcount > 0 ) {
521 my $count = $self->save_invoices();
523 flash('ok', t8('#1 proposal(s) with #2 invoice(s) saved.', $propcount, $count));
526 $self->action_list();
530 sub save_single_bank_transaction {
531 my ($self, %params) = @_;
535 bank_transaction => SL::DB::Manager::BankTransaction->find_by(id => $params{bank_transaction_id}),
539 if (!$data{bank_transaction}) {
543 message => $::locale->text('The ID #1 is not a valid database ID.', $data{bank_transaction_id}),
550 my $bt_id = $data{bank_transaction_id};
551 my $bank_transaction = $data{bank_transaction};
552 my $sign = $bank_transaction->amount < 0 ? -1 : 1;
553 my $amount_of_transaction = $sign * $bank_transaction->amount;
554 my $payment_received = $bank_transaction->amount > 0;
555 my $payment_sent = $bank_transaction->amount < 0;
558 foreach my $invoice_id (@{ $params{invoice_ids} }) {
559 my $invoice = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
564 message => $::locale->text("The ID #1 is not a valid database ID.", $invoice_id),
567 push @{ $data{invoices} }, $invoice;
570 if ( $payment_received
571 && any { ( $_->is_sales && ($_->amount < 0))
572 || (!$_->is_sales && ($_->amount > 0))
573 } @{ $data{invoices} }) {
577 message => $::locale->text("Received payments can only be posted for sales invoices and purchase credit notes."),
582 && any { ( $_->is_sales && ($_->amount > 0))
583 || (!$_->is_sales && ($_->amount < 0) && ($_->invoice_type eq 'purchase_invoice'))
584 } @{ $data{invoices} }) {
588 message => $::locale->text("Sent payments can only be posted for purchase invoices and sales credit notes."),
592 my $max_invoices = scalar(@{ $data{invoices} });
595 foreach my $invoice (@{ $data{invoices} }) {
596 my $source = ($data{sources} // [])->[$n_invoices];
597 my $memo = ($data{memos} // [])->[$n_invoices];
601 # Check if bank_transaction already has a link to the invoice, may only be linked once per invoice
602 # This might be caused by the user reloading a page and resending the form
603 if (_existing_record_link($bank_transaction, $invoice)) {
607 message => $::locale->text("Bank transaction with id #1 has already been linked to #2.", $bank_transaction->id, $invoice->displayable_name),
611 if (!$amount_of_transaction && $invoice->open_amount) {
615 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."),
620 if ( defined $::form->{invoice_skontos}->{"$bt_id"} ) {
621 $payment_type = shift(@{ $::form->{invoice_skontos}->{"$bt_id"} });
623 $payment_type = 'without_skonto';
627 # pay invoice or go to the next bank transaction if the amount is not sufficiently high
628 if ($invoice->open_amount <= $amount_of_transaction && $n_invoices < $max_invoices) {
629 my $open_amount = ($payment_type eq 'with_skonto_pt'?$invoice->amount_less_skonto:$invoice->open_amount);
630 # first calculate new bank transaction amount ...
631 if ($invoice->is_sales) {
632 $amount_of_transaction -= $sign * $open_amount;
633 $bank_transaction->invoice_amount($bank_transaction->invoice_amount + $open_amount);
635 $amount_of_transaction += $sign * $open_amount;
636 $bank_transaction->invoice_amount($bank_transaction->invoice_amount - $open_amount);
638 # ... and then pay the invoice
639 $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
640 trans_id => $invoice->id,
641 amount => $open_amount,
642 payment_type => $payment_type,
645 transdate => $bank_transaction->transdate->to_kivitendo);
647 # use the whole amount of the bank transaction for the invoice, overpay the invoice if necessary
649 # this catches credit_notes and negative sales invoices
650 if ( $invoice->is_sales && $invoice->amount < 0 ) {
651 # $invoice->open_amount is negative for credit_notes
652 # $bank_transaction->amount is negative for outgoing transactions
653 # so $amount_of_transaction is negative but needs positive
654 $amount_of_transaction *= -1;
656 } elsif (!$invoice->is_sales && $invoice->invoice_type eq 'ap_transaction' ) {
657 # $invoice->open_amount may be negative for ap_transaction but may be positiv for negativ ap_transaction
658 # if $invoice->open_amount is negative $bank_transaction->amount is positve
659 # if $invoice->open_amount is positive $bank_transaction->amount is negative
660 # but amount of transaction is for both positive
661 $amount_of_transaction *= -1 if $invoice->open_amount == - $amount_of_transaction;
664 my $overpaid_amount = $amount_of_transaction - $invoice->open_amount;
665 $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
666 trans_id => $invoice->id,
667 amount => $amount_of_transaction,
668 payment_type => $payment_type,
671 transdate => $bank_transaction->transdate->to_kivitendo);
672 $bank_transaction->invoice_amount($bank_transaction->amount);
673 $amount_of_transaction = 0;
675 if ($overpaid_amount >= 0.01) {
679 message => $::locale->text('Invoice #1 was overpaid by #2.', $invoice->invnumber, $::form->format_amount(\%::myconfig, $overpaid_amount, 2)),
683 # Record a record link from the bank transaction to the invoice
685 from_table => 'bank_transactions',
687 to_table => $invoice->is_sales ? 'ar' : 'ap',
688 to_id => $invoice->id,
691 SL::DB::RecordLink->new(@props)->save;
693 # "close" a sepa_export_item if it exists
694 # code duplicated in action_save_proposals!
695 # currently only works, if there is only exactly one open sepa_export_item
696 if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
697 if ( scalar @$seis == 1 ) {
698 # moved the execution and the check for sepa_export into a method,
699 # this isn't part of a transaction, though
700 $seis->[0]->set_executed if $invoice->id == $seis->[0]->arap_id;
705 $bank_transaction->save;
707 # 'undef' means 'no error' here.
712 my $rez = $data{bank_transaction}->db->with_transaction(sub {
714 $error = $worker->();
725 # Rollback Fehler nicht weiterreichen
729 return grep { $_ } ($error, @warnings);
737 $::auth->assert('bank_transaction');
744 sub make_filter_summary {
747 my $filter = $::form->{filter} || {};
751 [ $filter->{"transdate:date::ge"}, $::locale->text('Transdate') . " " . $::locale->text('From Date') ],
752 [ $filter->{"transdate:date::le"}, $::locale->text('Transdate') . " " . $::locale->text('To Date') ],
753 [ $filter->{"valutadate:date::ge"}, $::locale->text('Valutadate') . " " . $::locale->text('From Date') ],
754 [ $filter->{"valutadate:date::le"}, $::locale->text('Valutadate') . " " . $::locale->text('To Date') ],
755 [ $filter->{"amount:number"}, $::locale->text('Amount') ],
756 [ $filter->{"bank_account_id:integer"}, $::locale->text('Local bank account') ],
760 push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
763 $self->{filter_summary} = join ', ', @filter_strings;
769 my $callback = $self->models->get_callback;
771 my $report = SL::ReportGenerator->new(\%::myconfig, $::form);
772 $self->{report} = $report;
774 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);
775 my @sortable = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount purpose local_account_number local_bank_code);
778 transdate => { sub => sub { $_[0]->transdate_as_date } },
779 valutadate => { sub => sub { $_[0]->valutadate_as_date } },
781 remote_account_number => { },
782 remote_bank_code => { },
783 amount => { sub => sub { $_[0]->amount_as_number },
785 invoice_amount => { sub => sub { $_[0]->invoice_amount_as_number },
787 invoices => { sub => sub { $_[0]->linked_invoices } },
788 currency => { sub => sub { $_[0]->currency->name } },
790 local_account_number => { sub => sub { $_[0]->local_bank_account->account_number } },
791 local_bank_code => { sub => sub { $_[0]->local_bank_account->bank_code } },
792 local_bank_name => { sub => sub { $_[0]->local_bank_account->name } },
796 map { $column_defs{$_}->{text} ||= $::locale->text( $self->models->get_sort_spec->{$_}->{title} ) } keys %column_defs;
798 $report->set_options(
799 std_column_visibility => 1,
800 controller_class => 'BankTransaction',
801 output_format => 'HTML',
802 top_info_text => $::locale->text('Bank transactions'),
803 title => $::locale->text('Bank transactions'),
804 allow_pdf_export => 1,
805 allow_csv_export => 1,
807 $report->set_columns(%column_defs);
808 $report->set_column_order(@columns);
809 $report->set_export_options(qw(list_all filter));
810 $report->set_options_from_form;
811 $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
812 $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
814 my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted();
816 $report->set_options(
817 raw_top_info_text => $self->render('bank_transactions/report_top', { output => 0 }, BANK_ACCOUNTS => $bank_accounts),
818 raw_bottom_info_text => $self->render('bank_transactions/report_bottom', { output => 0 }),
822 sub _existing_record_link {
823 my ($bt, $invoice) = @_;
825 # check whether a record link from banktransaction $bt already exists to
826 # invoice $invoice, returns 1 if that is the case
828 die unless $bt->isa("SL::DB::BankTransaction") && ( $invoice->isa("SL::DB::Invoice") || $invoice->isa("SL::DB::PurchaseInvoice") );
830 my $linked_record_to_table = $invoice->is_sales ? 'Invoice' : 'PurchaseInvoice';
831 my $linked_records = $bt->linked_records( direction => 'to', to => $linked_record_to_table, query => [ id => $invoice->id ] );
833 return @$linked_records ? 1 : 0;
836 sub init_problems { [] }
841 SL::Controller::Helper::GetModels->new(
846 dir => 0, # 1 = ASC, 0 = DESC : default sort is newest at top
848 transdate => t8('Transdate'),
849 remote_name => t8('Remote name'),
850 amount => t8('Amount'),
851 invoice_amount => t8('Assigned'),
852 invoices => t8('Linked invoices'),
853 valutadate => t8('Valutadate'),
854 remote_account_number => t8('Remote account number'),
855 remote_bank_code => t8('Remote bank code'),
856 currency => t8('Currency'),
857 purpose => t8('Purpose'),
858 local_account_number => t8('Local account number'),
859 local_bank_code => t8('Local bank code'),
860 local_bank_name => t8('Bank account'),
862 with_objects => [ 'local_bank_account', 'currency' ],
866 sub load_ap_record_template_url {
867 my ($self, $template) = @_;
869 return $self->url_for(
870 controller => 'ap.pl',
871 action => 'load_record_template',
873 'form_defaults.amount_1' => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
874 'form_defaults.transdate' => $self->transaction->transdate_as_date,
875 'form_defaults.duedate' => $self->transaction->transdate_as_date,
876 'form_defaults.no_payment_bookings' => 1,
877 'form_defaults.paid_1_suggestion' => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
878 'form_defaults.AP_paid_1_suggestion' => $self->transaction->local_bank_account->chart->accno,
879 'form_defaults.callback' => $self->callback,
883 sub load_gl_record_template_url {
884 my ($self, $template) = @_;
886 return $self->url_for(
887 controller => 'gl.pl',
888 action => 'load_record_template',
890 'form_defaults.amount_1' => abs($self->transaction->amount), # always positive
891 'form_defaults.transdate' => $self->transaction->transdate_as_date,
892 'form_defaults.callback' => $self->callback,
896 sub setup_search_action_bar {
897 my ($self, %params) = @_;
899 for my $bar ($::request->layout->get('actionbar')) {
903 submit => [ '#search_form', { action => 'BankTransaction/list' } ],
904 accesskey => 'enter',
910 sub setup_list_all_action_bar {
911 my ($self, %params) = @_;
913 for my $bar ($::request->layout->get('actionbar')) {
917 submit => [ '#filter_form', { action => 'BankTransaction/list_all' } ],
918 accesskey => 'enter',
933 SL::Controller::BankTransaction - Posting payments to invoices from
934 bank transactions imported earlier
940 =item C<save_single_bank_transaction %params>
942 Takes a bank transaction ID (as parameter C<bank_transaction_id> and
943 tries to post its amount to a certain number of invoices (parameter
944 C<invoice_ids>, an array ref of database IDs to purchase or sales
947 The whole function is wrapped in a database transaction. If an
948 exception occurs the bank transaction is not posted at all. The same
949 is true if the code detects an error during the execution, e.g. a bank
950 transaction that's already been posted earlier. In both cases the
951 database transaction will be rolled back.
953 If warnings but not errors occur the database transaction is still
956 The return value is an error object or C<undef> if the function
957 succeeded. The calling function will collect all warnings and errors
958 and display them in a nicely formatted table if any occurred.
960 An error object is a hash reference containing the following members:
964 =item * C<result> — can be either C<warning> or C<error>. Warnings are
965 displayed slightly different than errors.
967 =item * C<message> — a human-readable message included in the list of
968 errors meant as the description of why the problem happened
970 =item * C<bank_transaction_id>, C<invoice_ids> — the same parameters
971 that the function was called with
973 =item * C<bank_transaction> — the database object
974 (C<SL::DB::BankTransaction>) corresponding to C<bank_transaction_id>
976 =item * C<invoices> — an array ref of the database objects (either
977 C<SL::DB::Invoice> or C<SL::DB::PurchaseInvoice>) corresponding to
986 Niclas Zimmermann E<lt>niclas@kivitendo-premium.deE<gt>,
987 Geoffrey Richardson E<lt>information@richardson-bueren.deE<gt>