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 push @{$open_invoice->{sepa_export_item}}, $_;
134 $open_invoice->{skonto_type} = $_->payment_type;
135 $sepa_exports{$_->sepa_export_id} ||= { count => 0, is_ar => 0, amount => 0, proposed => 0, invoices => [], item => $_ };
136 $sepa_exports{$_->sepa_export_id}->{count}++;
137 $sepa_exports{$_->sepa_export_id}->{is_ar}++ if $_->ar_id == $open_invoice->id;
138 $sepa_exports{$_->sepa_export_id}->{amount} += $_->amount * $factor;
139 push @{ $sepa_exports{$_->sepa_export_id}->{invoices} }, $open_invoice;
144 # try to match each bank_transaction with each of the possible open invoices
148 foreach my $bt (@{ $bank_transactions }) {
149 ## 5 Stellen hinter dem Komma auf 2 Stellen reduzieren
150 $bt->amount($bt->amount*1);
151 $bt->invoice_amount($bt->invoice_amount*1);
153 $bt->{proposals} = [];
154 $bt->{rule_matches} = [];
156 $bt->{remote_name} .= $bt->{remote_name_1} if $bt->{remote_name_1};
158 if ( $bt->is_batch_transaction ) {
159 foreach ( keys %sepa_exports) {
160 if ( abs(($sepa_exports{$_}->{amount} * 1) - ($bt->amount * 1)) < 0.01 ) {
162 @{$bt->{proposals}} = @{$sepa_exports{$_}->{invoices}};
163 $bt->{sepa_export_ok} = 1;
164 $sepa_exports{$_}->{proposed}=1;
165 push(@proposals, $bt);
169 # batch transaction has no remotename !!
171 next unless $bt->{remote_name}; # bank has no name, usually fees, use create invoice to assign
174 # try to match the current $bt to each of the open_invoices, saving the
175 # results of get_agreement_with_invoice in $open_invoice->{agreement} and
176 # $open_invoice->{rule_matches}.
178 # The values are overwritten each time a new bt is checked, so at the end
179 # of each bt the likely results are filtered and those values are stored in
180 # the arrays $bt->{proposals} and $bt->{rule_matches}, and the agreement
181 # score is stored in $bt->{agreement}
183 foreach my $open_invoice (@all_open_invoices) {
184 ($open_invoice->{agreement}, $open_invoice->{rule_matches}) = $bt->get_agreement_with_invoice($open_invoice);
185 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,
186 $open_invoice->amount * ($open_invoice->{is_ar} ? 1 : -1), 2);
190 my $min_agreement = 3; # suggestions must have at least this score
192 my $max_agreement = max map { $_->{agreement} } @all_open_invoices;
194 # add open_invoices with highest agreement into array $bt->{proposals}
195 if ( $max_agreement >= $min_agreement ) {
196 $bt->{proposals} = [ grep { $_->{agreement} == $max_agreement } @all_open_invoices ];
197 $bt->{agreement} = $max_agreement; #scalar @{ $bt->{proposals} } ? $agreement + 1 : '';
199 # store the rule_matches in a separate array, so they can be displayed in template
200 foreach ( @{ $bt->{proposals} } ) {
201 push(@{$bt->{rule_matches}}, $_->{rule_matches});
207 # separate filter for proposals (second tab, agreement >= 5 and exactly one match)
208 # to qualify as a proposal there has to be
209 # * agreement >= 5 TODO: make threshold configurable in configuration
210 # * there must be only one exact match
211 # * depending on whether sales or purchase the amount has to have the correct sign (so Gutschriften don't work?)
212 my $proposal_threshold = 5;
213 my @otherproposals = grep {
214 ($_->{agreement} >= $proposal_threshold)
215 && (1 == scalar @{ $_->{proposals} })
216 && (@{ $_->{proposals} }[0]->is_sales ? abs(@{ $_->{proposals} }[0]->amount - $_->amount) < 0.01
217 : abs(@{ $_->{proposals} }[0]->amount + $_->amount) < 0.01)
218 } @{ $bank_transactions };
220 push @proposals, @otherproposals;
222 # sort bank transaction proposals by quality (score) of proposal
223 $bank_transactions = [ sort { $a->{agreement} <=> $b->{agreement} } @{ $bank_transactions } ] if $::form->{sort_by} eq 'proposal' and $::form->{sort_dir} == 1;
224 $bank_transactions = [ sort { $b->{agreement} <=> $a->{agreement} } @{ $bank_transactions } ] if $::form->{sort_by} eq 'proposal' and $::form->{sort_dir} == 0;
226 # for testing with t/bank/banktransaction.t :
227 if ( $::form->{dont_render_for_test} ) {
228 return $bank_transactions;
231 $::request->layout->add_javascripts("kivi.BankTransaction.js");
232 $self->render('bank_transactions/list',
233 title => t8('Bank transactions MT940'),
234 BANK_TRANSACTIONS => $bank_transactions,
235 PROPOSALS => \@proposals,
236 bank_account => $bank_account,
237 ui_tab => scalar(@proposals) > 0?1:0,
241 sub action_assign_invoice {
244 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
246 $self->render('bank_transactions/assign_invoice',
248 title => t8('Assign invoice'),);
251 sub action_create_invoice {
253 my %myconfig = %main::myconfig;
255 $self->transaction(SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id}));
257 # This was dead code: We compared vendor.account_name with bank_transaction.iban.
258 # This did never match (Kontonummer != IBAN). It's kivis 09/02 (2013) day
259 # If refactored/improved, also consider that vendor.iban should be normalized
260 # user may like to input strings like: 'AT 3333 3333 2222 1111' -> can be checked strictly
261 # at Vendor code because we need the correct data for all sepa exports.
263 my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(iban => $self->transaction->{remote_account_number});
264 my $use_vendor_filter = $self->transaction->{remote_account_number} && $vendor_of_transaction;
266 my $templates_ap = SL::DB::Manager::RecordTemplate->get_all(
267 where => [ template_type => 'ap_transaction' ],
268 with_objects => [ qw(employee vendor) ],
270 my $templates_gl = SL::DB::Manager::RecordTemplate->get_all(
271 query => [ template_type => 'gl_transaction',
272 chart_id => SL::DB::Manager::BankAccount->find_by(id => $self->transaction->local_bank_account_id)->chart_id,
274 with_objects => [ qw(employee record_template_items) ],
277 # pre filter templates_ap, if we have a vendor match (IBAN eq IBAN) - show and allow user to edit this via gui!
278 $templates_ap = [ grep { $_->vendor_id == $vendor_of_transaction->id } @{ $templates_ap } ] if $use_vendor_filter;
280 $self->callback($self->url_for(
282 'filter.bank_account' => $::form->{filter}->{bank_account},
283 'filter.todate' => $::form->{filter}->{todate},
284 'filter.fromdate' => $::form->{filter}->{fromdate},
288 'bank_transactions/create_invoice',
290 title => t8('Create invoice'),
291 TEMPLATES_GL => $use_vendor_filter ? undef : $templates_gl,
292 TEMPLATES_AP => $templates_ap,
293 vendor_name => $use_vendor_filter ? $vendor_of_transaction->name : undef,
297 sub action_ajax_payment_suggestion {
300 # based on a BankTransaction ID and a Invoice or PurchaseInvoice ID passed via $::form,
301 # create an HTML blob to be used by the js function add_invoices in templates/webpages/bank_transactions/list.html
302 # and return encoded as JSON
304 my $bt = SL::DB::Manager::BankTransaction->find_by( id => $::form->{bt_id} );
305 my $invoice = SL::DB::Manager::Invoice->find_by( id => $::form->{prop_id} ) || SL::DB::Manager::PurchaseInvoice->find_by( id => $::form->{prop_id} );
307 die unless $bt and $invoice;
309 my @select_options = $invoice->get_payment_select_options_for_bank_transaction($::form->{bt_id});
312 $html = $self->render(
313 'bank_transactions/_payment_suggestion', { output => 0 },
314 bt_id => $::form->{bt_id},
315 prop_id => $::form->{prop_id},
317 SELECT_OPTIONS => \@select_options,
320 $self->render(\ SL::JSON::to_json( { 'html' => "$html" } ), { layout => 0, type => 'json', process => 0 });
323 sub action_filter_templates {
326 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
329 push @filter, ('vendor.name' => { ilike => '%' . $::form->{vendor} . '%' }) if $::form->{vendor};
330 push @filter, ('template_name' => { ilike => '%' . $::form->{template} . '%' }) if $::form->{template};
331 push @filter, ('reference' => { ilike => '%' . $::form->{reference} . '%' }) if $::form->{reference};
333 my $templates_ap = SL::DB::Manager::RecordTemplate->get_all(
334 where => [ template_type => 'ap_transaction', (and => \@filter) x !!@filter ],
335 with_objects => [ qw(employee vendor) ],
337 my $templates_gl = SL::DB::Manager::RecordTemplate->get_all(
338 query => [ template_type => 'gl_transaction',
339 chart_id => SL::DB::Manager::BankAccount->find_by(id => $self->transaction->local_bank_account_id)->chart_id,
340 (and => \@filter) x !!@filter
342 with_objects => [ qw(employee record_template_items) ],
345 $::form->{filter} //= {};
347 $self->callback($self->url_for(
349 'filter.bank_account' => $::form->{filter}->{bank_account},
350 'filter.todate' => $::form->{filter}->{todate},
351 'filter.fromdate' => $::form->{filter}->{fromdate},
354 my $output = $self->render(
355 'bank_transactions/_template_list',
357 TEMPLATES_AP => $templates_ap,
358 TEMPLATES_GL => $templates_gl,
361 $self->render(\to_json({ html => $output }), { type => 'json', process => 0 });
364 sub action_ajax_add_list {
367 my @where_sale = (amount => { ne => \'paid' });
368 my @where_purchase = (amount => { ne => \'paid' });
370 if ($::form->{invnumber}) {
371 push @where_sale, (invnumber => { ilike => like($::form->{invnumber})});
372 push @where_purchase, (invnumber => { ilike => like($::form->{invnumber})});
375 if ($::form->{amount}) {
376 push @where_sale, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
377 push @where_purchase, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
380 if ($::form->{vcnumber}) {
381 push @where_sale, ('customer.customernumber' => { ilike => like($::form->{vcnumber})});
382 push @where_purchase, ('vendor.vendornumber' => { ilike => like($::form->{vcnumber})});
385 if ($::form->{vcname}) {
386 push @where_sale, ('customer.name' => { ilike => like($::form->{vcname})});
387 push @where_purchase, ('vendor.name' => { ilike => like($::form->{vcname})});
390 if ($::form->{transdatefrom}) {
391 my $fromdate = $::locale->parse_date_to_object($::form->{transdatefrom});
392 if ( ref($fromdate) eq 'DateTime' ) {
393 push @where_sale, ('transdate' => { ge => $fromdate});
394 push @where_purchase, ('transdate' => { ge => $fromdate});
398 if ($::form->{transdateto}) {
399 my $todate = $::locale->parse_date_to_object($::form->{transdateto});
400 if ( ref($todate) eq 'DateTime' ) {
401 $todate->add(days => 1);
402 push @where_sale, ('transdate' => { lt => $todate});
403 push @where_purchase, ('transdate' => { lt => $todate});
407 my $all_open_ar_invoices = SL::DB::Manager::Invoice ->get_all(where => \@where_sale, with_objects => 'customer');
408 my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => \@where_purchase, with_objects => 'vendor');
410 my @all_open_invoices = @{ $all_open_ar_invoices };
411 # add ap invoices, filtering out subcent open amounts
412 push @all_open_invoices, grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
414 @all_open_invoices = sort { $a->id <=> $b->id } @all_open_invoices;
416 my $output = $self->render(
417 'bank_transactions/add_list',
419 INVOICES => \@all_open_invoices,
422 my %result = ( count => 0, html => $output );
424 $self->render(\to_json(\%result), { type => 'json', process => 0 });
427 sub action_ajax_accept_invoices {
430 my @selected_invoices;
431 foreach my $invoice_id (@{ $::form->{invoice_id} || [] }) {
432 my $invoice_object = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
433 push @selected_invoices, $invoice_object;
437 'bank_transactions/invoices',
439 INVOICES => \@selected_invoices,
440 bt_id => $::form->{bt_id},
447 return 0 if !$::form->{invoice_ids};
449 my %invoice_hash = %{ delete $::form->{invoice_ids} }; # each key (the bt line with a bt_id) contains an array of invoice_ids
451 # e.g. three partial payments with bt_ids 54, 55 and 56 for invoice with id 74:
464 # or if the payment with bt_id 44 is used to pay invoices with ids 50, 51 and 52
466 # '44' => [ '50', '51', 52' ]
469 $::form->{invoice_skontos} ||= {}; # hash of arrays containing the payment types, could be empty
471 # a bank_transaction may be assigned to several invoices, i.e. a customer
472 # might pay several open invoices with one transaction
478 if ( $::form->{proposal_ids} ) {
479 foreach (@{ $::form->{proposal_ids} }) {
480 my $bank_transaction_id = $_;
481 my $invoice_ids = $invoice_hash{$_};
482 push @{ $self->problems }, $self->save_single_bank_transaction(
483 bank_transaction_id => $bank_transaction_id,
484 invoice_ids => $invoice_ids,
485 sources => ($::form->{sources} // {})->{$_},
486 memos => ($::form->{memos} // {})->{$_},
488 $count += scalar( @{$invoice_ids} );
491 while ( my ($bank_transaction_id, $invoice_ids) = each(%invoice_hash) ) {
492 push @{ $self->problems }, $self->save_single_bank_transaction(
493 bank_transaction_id => $bank_transaction_id,
494 invoice_ids => $invoice_ids,
495 sources => [ map { $::form->{"sources_${bank_transaction_id}_${_}"} } @{ $invoice_ids } ],
496 memos => [ map { $::form->{"memos_${bank_transaction_id}_${_}"} } @{ $invoice_ids } ],
498 $count += scalar( @{$invoice_ids} );
501 foreach (@{ $self->problems }) {
502 $count-- if $_->{result} eq 'error';
507 sub action_save_invoices {
509 my $count = $self->save_invoices();
511 flash('ok', t8('#1 invoice(s) saved.', $count));
513 $self->action_list();
516 sub action_save_proposals {
519 if ( $::form->{proposal_ids} ) {
520 my $propcount = scalar(@{ $::form->{proposal_ids} });
521 if ( $propcount > 0 ) {
522 my $count = $self->save_invoices();
524 flash('ok', t8('#1 proposal(s) with #2 invoice(s) saved.', $propcount, $count));
527 $self->action_list();
531 sub save_single_bank_transaction {
532 my ($self, %params) = @_;
536 bank_transaction => SL::DB::Manager::BankTransaction->find_by(id => $params{bank_transaction_id}),
540 if (!$data{bank_transaction}) {
544 message => $::locale->text('The ID #1 is not a valid database ID.', $data{bank_transaction_id}),
551 my $bt_id = $data{bank_transaction_id};
552 my $bank_transaction = $data{bank_transaction};
553 my $sign = $bank_transaction->amount < 0 ? -1 : 1;
554 my $amount_of_transaction = $sign * $bank_transaction->amount;
555 my $payment_received = $bank_transaction->amount > 0;
556 my $payment_sent = $bank_transaction->amount < 0;
559 foreach my $invoice_id (@{ $params{invoice_ids} }) {
560 my $invoice = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
565 message => $::locale->text("The ID #1 is not a valid database ID.", $invoice_id),
568 push @{ $data{invoices} }, $invoice;
571 if ( $payment_received
572 && any { ( $_->is_sales && ($_->amount < 0))
573 || (!$_->is_sales && ($_->amount > 0))
574 } @{ $data{invoices} }) {
578 message => $::locale->text("Received payments can only be posted for sales invoices and purchase credit notes."),
583 && any { ( $_->is_sales && ($_->amount > 0))
584 || (!$_->is_sales && ($_->amount < 0) && ($_->invoice_type eq 'purchase_invoice'))
585 } @{ $data{invoices} }) {
589 message => $::locale->text("Sent payments can only be posted for purchase invoices and sales credit notes."),
593 my $max_invoices = scalar(@{ $data{invoices} });
596 foreach my $invoice (@{ $data{invoices} }) {
597 my $source = ($data{sources} // [])->[$n_invoices];
598 my $memo = ($data{memos} // [])->[$n_invoices];
602 # Check if bank_transaction already has a link to the invoice, may only be linked once per invoice
603 # This might be caused by the user reloading a page and resending the form
604 if (_existing_record_link($bank_transaction, $invoice)) {
608 message => $::locale->text("Bank transaction with id #1 has already been linked to #2.", $bank_transaction->id, $invoice->displayable_name),
612 if (!$amount_of_transaction && $invoice->open_amount) {
616 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."),
621 if ( defined $::form->{invoice_skontos}->{"$bt_id"} ) {
622 $payment_type = shift(@{ $::form->{invoice_skontos}->{"$bt_id"} });
624 $payment_type = 'without_skonto';
628 # pay invoice or go to the next bank transaction if the amount is not sufficiently high
629 if ($invoice->open_amount <= $amount_of_transaction && $n_invoices < $max_invoices) {
630 my $open_amount = ($payment_type eq 'with_skonto_pt'?$invoice->amount_less_skonto:$invoice->open_amount);
631 # first calculate new bank transaction amount ...
632 if ($invoice->is_sales) {
633 $amount_of_transaction -= $sign * $open_amount;
634 $bank_transaction->invoice_amount($bank_transaction->invoice_amount + $open_amount);
636 $amount_of_transaction += $sign * $open_amount;
637 $bank_transaction->invoice_amount($bank_transaction->invoice_amount - $open_amount);
639 # ... and then pay the invoice
640 $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
641 trans_id => $invoice->id,
642 amount => $open_amount,
643 payment_type => $payment_type,
646 transdate => $bank_transaction->transdate->to_kivitendo);
648 # use the whole amount of the bank transaction for the invoice, overpay the invoice if necessary
650 # this catches credit_notes and negative sales invoices
651 if ( $invoice->is_sales && $invoice->amount < 0 ) {
652 # $invoice->open_amount is negative for credit_notes
653 # $bank_transaction->amount is negative for outgoing transactions
654 # so $amount_of_transaction is negative but needs positive
655 $amount_of_transaction *= -1;
657 } elsif (!$invoice->is_sales && $invoice->invoice_type eq 'ap_transaction' ) {
658 # $invoice->open_amount may be negative for ap_transaction but may be positiv for negativ ap_transaction
659 # if $invoice->open_amount is negative $bank_transaction->amount is positve
660 # if $invoice->open_amount is positive $bank_transaction->amount is negative
661 # but amount of transaction is for both positive
662 $amount_of_transaction *= -1 if $invoice->open_amount == - $amount_of_transaction;
665 my $overpaid_amount = $amount_of_transaction - $invoice->open_amount;
666 $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
667 trans_id => $invoice->id,
668 amount => $amount_of_transaction,
669 payment_type => $payment_type,
672 transdate => $bank_transaction->transdate->to_kivitendo);
673 $bank_transaction->invoice_amount($bank_transaction->amount);
674 $amount_of_transaction = 0;
676 if ($overpaid_amount >= 0.01) {
680 message => $::locale->text('Invoice #1 was overpaid by #2.', $invoice->invnumber, $::form->format_amount(\%::myconfig, $overpaid_amount, 2)),
684 # Record a record link from the bank transaction to the invoice
686 from_table => 'bank_transactions',
688 to_table => $invoice->is_sales ? 'ar' : 'ap',
689 to_id => $invoice->id,
692 SL::DB::RecordLink->new(@props)->save;
694 # "close" a sepa_export_item if it exists
695 # code duplicated in action_save_proposals!
696 # currently only works, if there is only exactly one open sepa_export_item
697 if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
698 if ( scalar @$seis == 1 ) {
699 # moved the execution and the check for sepa_export into a method,
700 # this isn't part of a transaction, though
701 $seis->[0]->set_executed if $invoice->id == $seis->[0]->arap_id;
706 $bank_transaction->save;
708 # 'undef' means 'no error' here.
713 my $rez = $data{bank_transaction}->db->with_transaction(sub {
715 $error = $worker->();
726 # Rollback Fehler nicht weiterreichen
730 return grep { $_ } ($error, @warnings);
738 $::auth->assert('bank_transaction');
745 sub make_filter_summary {
748 my $filter = $::form->{filter} || {};
752 [ $filter->{"transdate:date::ge"}, $::locale->text('Transdate') . " " . $::locale->text('From Date') ],
753 [ $filter->{"transdate:date::le"}, $::locale->text('Transdate') . " " . $::locale->text('To Date') ],
754 [ $filter->{"valutadate:date::ge"}, $::locale->text('Valutadate') . " " . $::locale->text('From Date') ],
755 [ $filter->{"valutadate:date::le"}, $::locale->text('Valutadate') . " " . $::locale->text('To Date') ],
756 [ $filter->{"amount:number"}, $::locale->text('Amount') ],
757 [ $filter->{"bank_account_id:integer"}, $::locale->text('Local bank account') ],
761 push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
764 $self->{filter_summary} = join ', ', @filter_strings;
770 my $callback = $self->models->get_callback;
772 my $report = SL::ReportGenerator->new(\%::myconfig, $::form);
773 $self->{report} = $report;
775 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);
776 my @sortable = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount purpose local_account_number local_bank_code);
779 transdate => { sub => sub { $_[0]->transdate_as_date } },
780 valutadate => { sub => sub { $_[0]->valutadate_as_date } },
782 remote_account_number => { },
783 remote_bank_code => { },
784 amount => { sub => sub { $_[0]->amount_as_number },
786 invoice_amount => { sub => sub { $_[0]->invoice_amount_as_number },
788 invoices => { sub => sub { $_[0]->linked_invoices } },
789 currency => { sub => sub { $_[0]->currency->name } },
791 local_account_number => { sub => sub { $_[0]->local_bank_account->account_number } },
792 local_bank_code => { sub => sub { $_[0]->local_bank_account->bank_code } },
793 local_bank_name => { sub => sub { $_[0]->local_bank_account->name } },
797 map { $column_defs{$_}->{text} ||= $::locale->text( $self->models->get_sort_spec->{$_}->{title} ) } keys %column_defs;
799 $report->set_options(
800 std_column_visibility => 1,
801 controller_class => 'BankTransaction',
802 output_format => 'HTML',
803 top_info_text => $::locale->text('Bank transactions'),
804 title => $::locale->text('Bank transactions'),
805 allow_pdf_export => 1,
806 allow_csv_export => 1,
808 $report->set_columns(%column_defs);
809 $report->set_column_order(@columns);
810 $report->set_export_options(qw(list_all filter));
811 $report->set_options_from_form;
812 $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
813 $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
815 my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted();
817 $report->set_options(
818 raw_top_info_text => $self->render('bank_transactions/report_top', { output => 0 }, BANK_ACCOUNTS => $bank_accounts),
819 raw_bottom_info_text => $self->render('bank_transactions/report_bottom', { output => 0 }),
823 sub _existing_record_link {
824 my ($bt, $invoice) = @_;
826 # check whether a record link from banktransaction $bt already exists to
827 # invoice $invoice, returns 1 if that is the case
829 die unless $bt->isa("SL::DB::BankTransaction") && ( $invoice->isa("SL::DB::Invoice") || $invoice->isa("SL::DB::PurchaseInvoice") );
831 my $linked_record_to_table = $invoice->is_sales ? 'Invoice' : 'PurchaseInvoice';
832 my $linked_records = $bt->linked_records( direction => 'to', to => $linked_record_to_table, query => [ id => $invoice->id ] );
834 return @$linked_records ? 1 : 0;
837 sub init_problems { [] }
842 SL::Controller::Helper::GetModels->new(
847 dir => 0, # 1 = ASC, 0 = DESC : default sort is newest at top
849 transdate => t8('Transdate'),
850 remote_name => t8('Remote name'),
851 amount => t8('Amount'),
852 invoice_amount => t8('Assigned'),
853 invoices => t8('Linked invoices'),
854 valutadate => t8('Valutadate'),
855 remote_account_number => t8('Remote account number'),
856 remote_bank_code => t8('Remote bank code'),
857 currency => t8('Currency'),
858 purpose => t8('Purpose'),
859 local_account_number => t8('Local account number'),
860 local_bank_code => t8('Local bank code'),
861 local_bank_name => t8('Bank account'),
863 with_objects => [ 'local_bank_account', 'currency' ],
867 sub load_ap_record_template_url {
868 my ($self, $template) = @_;
870 return $self->url_for(
871 controller => 'ap.pl',
872 action => 'load_record_template',
874 'form_defaults.amount_1' => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
875 'form_defaults.transdate' => $self->transaction->transdate_as_date,
876 'form_defaults.duedate' => $self->transaction->transdate_as_date,
877 'form_defaults.no_payment_bookings' => 1,
878 'form_defaults.paid_1_suggestion' => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
879 'form_defaults.AP_paid_1_suggestion' => $self->transaction->local_bank_account->chart->accno,
880 'form_defaults.callback' => $self->callback,
884 sub load_gl_record_template_url {
885 my ($self, $template) = @_;
887 return $self->url_for(
888 controller => 'gl.pl',
889 action => 'load_record_template',
891 'form_defaults.amount_1' => -1 * $self->transaction->amount,
892 'form_defaults.transdate' => $self->transaction->transdate_as_date,
893 'form_defaults.callback' => $self->callback,
897 sub setup_search_action_bar {
898 my ($self, %params) = @_;
900 for my $bar ($::request->layout->get('actionbar')) {
904 submit => [ '#search_form', { action => 'BankTransaction/list' } ],
905 accesskey => 'enter',
911 sub setup_list_all_action_bar {
912 my ($self, %params) = @_;
914 for my $bar ($::request->layout->get('actionbar')) {
918 submit => [ '#filter_form', { action => 'BankTransaction/list_all' } ],
919 accesskey => 'enter',
934 SL::Controller::BankTransaction - Posting payments to invoices from
935 bank transactions imported earlier
941 =item C<save_single_bank_transaction %params>
943 Takes a bank transaction ID (as parameter C<bank_transaction_id> and
944 tries to post its amount to a certain number of invoices (parameter
945 C<invoice_ids>, an array ref of database IDs to purchase or sales
948 The whole function is wrapped in a database transaction. If an
949 exception occurs the bank transaction is not posted at all. The same
950 is true if the code detects an error during the execution, e.g. a bank
951 transaction that's already been posted earlier. In both cases the
952 database transaction will be rolled back.
954 If warnings but not errors occur the database transaction is still
957 The return value is an error object or C<undef> if the function
958 succeeded. The calling function will collect all warnings and errors
959 and display them in a nicely formatted table if any occurred.
961 An error object is a hash reference containing the following members:
965 =item * C<result> — can be either C<warning> or C<error>. Warnings are
966 displayed slightly different than errors.
968 =item * C<message> — a human-readable message included in the list of
969 errors meant as the description of why the problem happened
971 =item * C<bank_transaction_id>, C<invoice_ids> — the same parameters
972 that the function was called with
974 =item * C<bank_transaction> — the database object
975 (C<SL::DB::BankTransaction>) corresponding to C<bank_transaction_id>
977 =item * C<invoices> — an array ref of the database objects (either
978 C<SL::DB::Invoice> or C<SL::DB::PurchaseInvoice>) corresponding to
987 Niclas Zimmermann E<lt>niclas@kivitendo-premium.deE<gt>,
988 Geoffrey Richardson E<lt>information@richardson-bueren.deE<gt>