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);
29 use List::MoreUtils qw(any);
30 use List::Util qw(max);
32 use Rose::Object::MakeMethods::Generic
34 scalar => [ qw(callback transaction) ],
35 'scalar --get_set_init' => [ qw(models problems) ],
38 __PACKAGE__->run_before('check_auth');
48 my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted( query => [ obsolete => 0 ] );
50 $self->setup_search_action_bar;
51 $self->render('bank_transactions/search',
52 BANK_ACCOUNTS => $bank_accounts);
58 $self->make_filter_summary;
59 $self->prepare_report;
61 $self->setup_list_all_action_bar;
62 $self->report_generator_list_objects(report => $self->{report}, objects => $self->models->get);
68 if (!$::form->{filter}{bank_account}) {
69 flash('error', t8('No bank account chosen!'));
74 my $sort_by = $::form->{sort_by} || 'transdate';
75 $sort_by = 'transdate' if $sort_by eq 'proposal';
76 $sort_by .= $::form->{sort_dir} ? ' DESC' : ' ASC';
78 my $fromdate = $::locale->parse_date_to_object($::form->{filter}->{fromdate});
79 my $todate = $::locale->parse_date_to_object($::form->{filter}->{todate});
80 $todate->add( days => 1 ) if $todate;
83 push @where, (transdate => { ge => $fromdate }) if ($fromdate);
84 push @where, (transdate => { lt => $todate }) if ($todate);
85 my $bank_account = SL::DB::Manager::BankAccount->find_by( id => $::form->{filter}{bank_account} );
86 # bank_transactions no younger than starting date,
87 # including starting date (same search behaviour as fromdate)
88 # but OPEN invoices to be matched may be from before
89 if ( $bank_account->reconciliation_starting_date ) {
90 push @where, (transdate => { ge => $bank_account->reconciliation_starting_date });
93 my $bank_transactions = SL::DB::Manager::BankTransaction->get_all(
94 with_objects => [ 'local_bank_account', 'currency' ],
98 amount => {ne => \'invoice_amount'},
99 local_bank_account_id => $::form->{filter}{bank_account},
103 # credit notes have a negative amount, treat differently
104 my $all_open_ar_invoices = SL::DB::Manager::Invoice ->get_all(where => [ or => [ amount => { gt => \'paid' },
105 and => [ type => 'credit_note',
106 amount => { lt => \'paid' }
110 with_objects => ['customer','payment_terms']);
112 my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => [amount => { ne => \'paid' }], with_objects => ['vendor' ,'payment_terms']);
113 my $all_open_sepa_export_items = SL::DB::Manager::SepaExportItem->get_all(where => [chart_id => $bank_account->chart_id ,
114 'sepa_export.executed' => 0, 'sepa_export.closed' => 0 ], with_objects => ['sepa_export']);
116 my @all_open_invoices;
117 # filter out invoices with less than 1 cent outstanding
118 push @all_open_invoices, map { $_->{is_ar}=1 ; $_ } grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ar_invoices };
119 push @all_open_invoices, map { $_->{is_ar}=0 ; $_ } grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
122 # first collect sepa export items to open invoices
123 foreach my $open_invoice (@all_open_invoices){
124 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount,2);
125 $open_invoice->{skonto_type} = 'without_skonto';
126 foreach ( @{$all_open_sepa_export_items}) {
127 if (($_->ap_id && $_->ap_id == $open_invoice->id) || ($_->ar_id && $_->ar_id == $open_invoice->id)) {
128 my $factor = ($_->ar_id == $open_invoice->id ? 1 : -1);
129 #$main::lxdebug->message(LXDebug->DEBUG2(),"sepa_exitem=".$_->id." for invoice ".$open_invoice->id." factor=".$factor);
130 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount*$factor,2);
131 $open_invoice->{skonto_type} = $_->payment_type;
132 $sepa_exports{$_->sepa_export_id} ||= { count => 0, is_ar => 0, amount => 0, proposed => 0, invoices => [], item => $_ };
133 $sepa_exports{$_->sepa_export_id}->{count}++;
134 $sepa_exports{$_->sepa_export_id}->{is_ar}++ if $_->ar_id == $open_invoice->id;
135 $sepa_exports{$_->sepa_export_id}->{amount} += $_->amount * $factor;
136 push @{ $sepa_exports{$_->sepa_export_id}->{invoices} }, $open_invoice;
141 # try to match each bank_transaction with each of the possible open invoices
145 foreach my $bt (@{ $bank_transactions }) {
146 ## 5 Stellen hinter dem Komma auf 2 Stellen reduzieren
147 $bt->amount($bt->amount*1);
148 $bt->invoice_amount($bt->invoice_amount*1);
150 $bt->{proposals} = [];
151 $bt->{rule_matches} = [];
153 $bt->{remote_name} .= $bt->{remote_name_1} if $bt->{remote_name_1};
155 if ( $bt->is_batch_transaction ) {
156 foreach ( keys %sepa_exports) {
157 if ( abs(($sepa_exports{$_}->{amount} * 1) - ($bt->amount * 1)) < 0.01 ) {
159 @{$bt->{proposals}} = @{$sepa_exports{$_}->{invoices}};
160 $bt->{sepa_export_ok} = 1;
161 $sepa_exports{$_}->{proposed}=1;
162 push(@proposals, $bt);
166 # batch transaction has no remotename !!
168 next unless $bt->{remote_name}; # bank has no name, usually fees, use create invoice to assign
171 # try to match the current $bt to each of the open_invoices, saving the
172 # results of get_agreement_with_invoice in $open_invoice->{agreement} and
173 # $open_invoice->{rule_matches}.
175 # The values are overwritten each time a new bt is checked, so at the end
176 # of each bt the likely results are filtered and those values are stored in
177 # the arrays $bt->{proposals} and $bt->{rule_matches}, and the agreement
178 # score is stored in $bt->{agreement}
180 foreach my $open_invoice (@all_open_invoices) {
181 ($open_invoice->{agreement}, $open_invoice->{rule_matches}) = $bt->get_agreement_with_invoice($open_invoice);
182 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,
183 $open_invoice->amount * ($open_invoice->{is_ar} ? 1 : -1), 2);
187 my $min_agreement = 3; # suggestions must have at least this score
189 my $max_agreement = max map { $_->{agreement} } @all_open_invoices;
191 # add open_invoices with highest agreement into array $bt->{proposals}
192 if ( $max_agreement >= $min_agreement ) {
193 $bt->{proposals} = [ grep { $_->{agreement} == $max_agreement } @all_open_invoices ];
194 $bt->{agreement} = $max_agreement; #scalar @{ $bt->{proposals} } ? $agreement + 1 : '';
196 # store the rule_matches in a separate array, so they can be displayed in template
197 foreach ( @{ $bt->{proposals} } ) {
198 push(@{$bt->{rule_matches}}, $_->{rule_matches});
204 # separate filter for proposals (second tab, agreement >= 5 and exactly one match)
205 # to qualify as a proposal there has to be
206 # * agreement >= 5 TODO: make threshold configurable in configuration
207 # * there must be only one exact match
208 # * depending on whether sales or purchase the amount has to have the correct sign (so Gutschriften don't work?)
209 my $proposal_threshold = 5;
210 my @otherproposals = grep {
211 ($_->{agreement} >= $proposal_threshold)
212 && (1 == scalar @{ $_->{proposals} })
213 && (@{ $_->{proposals} }[0]->is_sales ? abs(@{ $_->{proposals} }[0]->amount - $_->amount) < 0.01
214 : abs(@{ $_->{proposals} }[0]->amount + $_->amount) < 0.01)
215 } @{ $bank_transactions };
217 push @proposals, @otherproposals;
219 # sort bank transaction proposals by quality (score) of proposal
220 if ($::form->{sort_by} && $::form->{sort_by} eq 'proposal') {
221 if ($::form->{sort_dir}) {
222 $bank_transactions = [ sort { $a->{agreement} <=> $b->{agreement} } @{ $bank_transactions } ];
224 $bank_transactions = [ sort { $b->{agreement} <=> $a->{agreement} } @{ $bank_transactions } ];
228 # for testing with t/bank/banktransaction.t :
229 if ( $::form->{dont_render_for_test} ) {
230 return $bank_transactions;
233 $::request->layout->add_javascripts("kivi.BankTransaction.js");
234 $self->render('bank_transactions/list',
235 title => t8('Bank transactions MT940'),
236 BANK_TRANSACTIONS => $bank_transactions,
237 PROPOSALS => \@proposals,
238 bank_account => $bank_account,
239 ui_tab => scalar(@proposals) > 0?1:0,
243 sub action_assign_invoice {
246 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
248 $self->render('bank_transactions/assign_invoice',
250 title => t8('Assign invoice'),);
253 sub action_create_invoice {
255 my %myconfig = %main::myconfig;
257 $self->transaction(SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id}));
259 # This was dead code: We compared vendor.account_name with bank_transaction.iban.
260 # This did never match (Kontonummer != IBAN). It's kivis 09/02 (2013) day
261 # If refactored/improved, also consider that vendor.iban should be normalized
262 # user may like to input strings like: 'AT 3333 3333 2222 1111' -> can be checked strictly
263 # at Vendor code because we need the correct data for all sepa exports.
265 my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(iban => $self->transaction->{remote_account_number});
266 my $use_vendor_filter = $self->transaction->{remote_account_number} && $vendor_of_transaction;
268 my $templates_ap = SL::DB::Manager::RecordTemplate->get_all(
269 where => [ template_type => 'ap_transaction' ],
270 with_objects => [ qw(employee vendor) ],
272 my $templates_gl = SL::DB::Manager::RecordTemplate->get_all(
273 query => [ template_type => 'gl_transaction',
274 chart_id => SL::DB::Manager::BankAccount->find_by(id => $self->transaction->local_bank_account_id)->chart_id,
276 with_objects => [ qw(employee record_template_items) ],
279 # pre filter templates_ap, if we have a vendor match (IBAN eq IBAN) - show and allow user to edit this via gui!
280 $templates_ap = [ grep { $_->vendor_id == $vendor_of_transaction->id } @{ $templates_ap } ] if $use_vendor_filter;
282 $self->callback($self->url_for(
284 'filter.bank_account' => $::form->{filter}->{bank_account},
285 'filter.todate' => $::form->{filter}->{todate},
286 'filter.fromdate' => $::form->{filter}->{fromdate},
290 'bank_transactions/create_invoice',
292 title => t8('Create invoice'),
293 TEMPLATES_GL => $use_vendor_filter && @{ $templates_ap } ? undef : $templates_gl,
294 TEMPLATES_AP => $templates_ap,
295 vendor_name => $use_vendor_filter && @{ $templates_ap } ? $vendor_of_transaction->name : undef,
299 sub action_ajax_payment_suggestion {
302 # based on a BankTransaction ID and a Invoice or PurchaseInvoice ID passed via $::form,
303 # create an HTML blob to be used by the js function add_invoices in templates/webpages/bank_transactions/list.html
304 # and return encoded as JSON
306 my $bt = SL::DB::Manager::BankTransaction->find_by( id => $::form->{bt_id} );
307 my $invoice = SL::DB::Manager::Invoice->find_by( id => $::form->{prop_id} ) || SL::DB::Manager::PurchaseInvoice->find_by( id => $::form->{prop_id} );
309 die unless $bt and $invoice;
311 my @select_options = $invoice->get_payment_select_options_for_bank_transaction($::form->{bt_id});
314 $html = $self->render(
315 'bank_transactions/_payment_suggestion', { output => 0 },
316 bt_id => $::form->{bt_id},
317 prop_id => $::form->{prop_id},
319 SELECT_OPTIONS => \@select_options,
322 $self->render(\ SL::JSON::to_json( { 'html' => "$html" } ), { layout => 0, type => 'json', process => 0 });
325 sub action_filter_templates {
328 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
330 my (@filter, @filter_ap);
332 # filter => gl and ap | filter_ap = ap (i.e. vendorname)
333 push @filter, ('template_name' => { ilike => '%' . $::form->{template} . '%' }) if $::form->{template};
334 push @filter, ('reference' => { ilike => '%' . $::form->{reference} . '%' }) if $::form->{reference};
335 push @filter_ap, ('vendor.name' => { ilike => '%' . $::form->{vendor} . '%' }) if $::form->{vendor};
336 push @filter_ap, @filter;
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 my $templates_ap = SL::DB::Manager::RecordTemplate->get_all(
346 where => [ template_type => 'ap_transaction', (and => \@filter_ap) x !!@filter_ap ],
347 with_objects => [ qw(employee vendor) ],
349 $::form->{filter} //= {};
351 $self->callback($self->url_for(
353 'filter.bank_account' => $::form->{filter}->{bank_account},
354 'filter.todate' => $::form->{filter}->{todate},
355 'filter.fromdate' => $::form->{filter}->{fromdate},
358 my $output = $self->render(
359 'bank_transactions/_template_list',
361 TEMPLATES_AP => $templates_ap,
362 TEMPLATES_GL => $templates_gl,
365 $self->render(\to_json({ html => $output }), { type => 'json', process => 0 });
368 sub action_ajax_add_list {
371 my @where_sale = (amount => { ne => \'paid' });
372 my @where_purchase = (amount => { ne => \'paid' });
374 if ($::form->{invnumber}) {
375 push @where_sale, (invnumber => { ilike => like($::form->{invnumber})});
376 push @where_purchase, (invnumber => { ilike => like($::form->{invnumber})});
379 if ($::form->{amount}) {
380 push @where_sale, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
381 push @where_purchase, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
384 if ($::form->{vcnumber}) {
385 push @where_sale, ('customer.customernumber' => { ilike => like($::form->{vcnumber})});
386 push @where_purchase, ('vendor.vendornumber' => { ilike => like($::form->{vcnumber})});
389 if ($::form->{vcname}) {
390 push @where_sale, ('customer.name' => { ilike => like($::form->{vcname})});
391 push @where_purchase, ('vendor.name' => { ilike => like($::form->{vcname})});
394 if ($::form->{transdatefrom}) {
395 my $fromdate = $::locale->parse_date_to_object($::form->{transdatefrom});
396 if ( ref($fromdate) eq 'DateTime' ) {
397 push @where_sale, ('transdate' => { ge => $fromdate});
398 push @where_purchase, ('transdate' => { ge => $fromdate});
402 if ($::form->{transdateto}) {
403 my $todate = $::locale->parse_date_to_object($::form->{transdateto});
404 if ( ref($todate) eq 'DateTime' ) {
405 $todate->add(days => 1);
406 push @where_sale, ('transdate' => { lt => $todate});
407 push @where_purchase, ('transdate' => { lt => $todate});
411 my $all_open_ar_invoices = SL::DB::Manager::Invoice ->get_all(where => \@where_sale, with_objects => 'customer');
412 my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => \@where_purchase, with_objects => 'vendor');
414 my @all_open_invoices = @{ $all_open_ar_invoices };
415 # add ap invoices, filtering out subcent open amounts
416 push @all_open_invoices, grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
418 @all_open_invoices = sort { $a->id <=> $b->id } @all_open_invoices;
420 my $output = $self->render(
421 'bank_transactions/add_list',
423 INVOICES => \@all_open_invoices,
426 my %result = ( count => 0, html => $output );
428 $self->render(\to_json(\%result), { type => 'json', process => 0 });
431 sub action_ajax_accept_invoices {
434 my @selected_invoices;
435 foreach my $invoice_id (@{ $::form->{invoice_id} || [] }) {
436 my $invoice_object = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
437 push @selected_invoices, $invoice_object;
441 'bank_transactions/invoices',
443 INVOICES => \@selected_invoices,
444 bt_id => $::form->{bt_id},
451 return 0 if !$::form->{invoice_ids};
453 my %invoice_hash = %{ delete $::form->{invoice_ids} }; # each key (the bt line with a bt_id) contains an array of invoice_ids
455 # e.g. three partial payments with bt_ids 54, 55 and 56 for invoice with id 74:
468 # or if the payment with bt_id 44 is used to pay invoices with ids 50, 51 and 52
470 # '44' => [ '50', '51', 52' ]
473 $::form->{invoice_skontos} ||= {}; # hash of arrays containing the payment types, could be empty
475 # a bank_transaction may be assigned to several invoices, i.e. a customer
476 # might pay several open invoices with one transaction
482 if ( $::form->{proposal_ids} ) {
483 foreach (@{ $::form->{proposal_ids} }) {
484 my $bank_transaction_id = $_;
485 my $invoice_ids = $invoice_hash{$_};
486 push @{ $self->problems }, $self->save_single_bank_transaction(
487 bank_transaction_id => $bank_transaction_id,
488 invoice_ids => $invoice_ids,
489 sources => ($::form->{sources} // {})->{$_},
490 memos => ($::form->{memos} // {})->{$_},
492 $count += scalar( @{$invoice_ids} );
495 while ( my ($bank_transaction_id, $invoice_ids) = each(%invoice_hash) ) {
496 push @{ $self->problems }, $self->save_single_bank_transaction(
497 bank_transaction_id => $bank_transaction_id,
498 invoice_ids => $invoice_ids,
499 sources => [ map { $::form->{"sources_${bank_transaction_id}_${_}"} } @{ $invoice_ids } ],
500 memos => [ map { $::form->{"memos_${bank_transaction_id}_${_}"} } @{ $invoice_ids } ],
502 $count += scalar( @{$invoice_ids} );
505 foreach (@{ $self->problems }) {
506 $count-- if $_->{result} eq 'error';
511 sub action_save_invoices {
513 my $count = $self->save_invoices();
515 flash('ok', t8('#1 invoice(s) saved.', $count));
517 $self->action_list();
520 sub action_save_proposals {
523 if ( $::form->{proposal_ids} ) {
524 my $propcount = scalar(@{ $::form->{proposal_ids} });
525 if ( $propcount > 0 ) {
526 my $count = $self->save_invoices();
528 flash('ok', t8('#1 proposal(s) with #2 invoice(s) saved.', $propcount, $count));
531 $self->action_list();
535 sub save_single_bank_transaction {
536 my ($self, %params) = @_;
540 bank_transaction => SL::DB::Manager::BankTransaction->find_by(id => $params{bank_transaction_id}),
544 if (!$data{bank_transaction}) {
548 message => $::locale->text('The ID #1 is not a valid database ID.', $data{bank_transaction_id}),
555 my $bt_id = $data{bank_transaction_id};
556 my $bank_transaction = $data{bank_transaction};
557 my $sign = $bank_transaction->amount < 0 ? -1 : 1;
558 my $amount_of_transaction = $sign * $bank_transaction->amount;
559 my $payment_received = $bank_transaction->amount > 0;
560 my $payment_sent = $bank_transaction->amount < 0;
563 foreach my $invoice_id (@{ $params{invoice_ids} }) {
564 my $invoice = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
569 message => $::locale->text("The ID #1 is not a valid database ID.", $invoice_id),
572 push @{ $data{invoices} }, $invoice;
575 if ( $payment_received
576 && any { ( $_->is_sales && ($_->amount < 0))
577 || (!$_->is_sales && ($_->amount > 0))
578 } @{ $data{invoices} }) {
582 message => $::locale->text("Received payments can only be posted for sales invoices and purchase credit notes."),
587 && any { ( $_->is_sales && ($_->amount > 0))
588 || (!$_->is_sales && ($_->amount < 0) && ($_->invoice_type eq 'purchase_invoice'))
589 } @{ $data{invoices} }) {
593 message => $::locale->text("Sent payments can only be posted for purchase invoices and sales credit notes."),
597 my $max_invoices = scalar(@{ $data{invoices} });
600 foreach my $invoice (@{ $data{invoices} }) {
601 my $source = ($data{sources} // [])->[$n_invoices];
602 my $memo = ($data{memos} // [])->[$n_invoices];
606 # Check if bank_transaction already has a link to the invoice, may only be linked once per invoice
607 # This might be caused by the user reloading a page and resending the form
608 if (_existing_record_link($bank_transaction, $invoice)) {
612 message => $::locale->text("Bank transaction with id #1 has already been linked to #2.", $bank_transaction->id, $invoice->displayable_name),
616 if (!$amount_of_transaction && $invoice->open_amount) {
620 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."),
625 if ( defined $::form->{invoice_skontos}->{"$bt_id"} ) {
626 $payment_type = shift(@{ $::form->{invoice_skontos}->{"$bt_id"} });
628 $payment_type = 'without_skonto';
632 # pay invoice or go to the next bank transaction if the amount is not sufficiently high
633 if ($invoice->open_amount <= $amount_of_transaction && $n_invoices < $max_invoices) {
634 my $open_amount = ($payment_type eq 'with_skonto_pt'?$invoice->amount_less_skonto:$invoice->open_amount);
635 # first calculate new bank transaction amount ...
636 if ($invoice->is_sales) {
637 $amount_of_transaction -= $sign * $open_amount;
638 $bank_transaction->invoice_amount($bank_transaction->invoice_amount + $open_amount);
640 $amount_of_transaction += $sign * $open_amount;
641 $bank_transaction->invoice_amount($bank_transaction->invoice_amount - $open_amount);
643 # ... and then pay the invoice
644 $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
645 trans_id => $invoice->id,
646 amount => $open_amount,
647 payment_type => $payment_type,
650 transdate => $bank_transaction->transdate->to_kivitendo);
652 # use the whole amount of the bank transaction for the invoice, overpay the invoice if necessary
654 # this catches credit_notes and negative sales invoices
655 if ( $invoice->is_sales && $invoice->amount < 0 ) {
656 # $invoice->open_amount is negative for credit_notes
657 # $bank_transaction->amount is negative for outgoing transactions
658 # so $amount_of_transaction is negative but needs positive
659 $amount_of_transaction *= -1;
661 } elsif (!$invoice->is_sales && $invoice->invoice_type =~ m/ap_transaction|purchase_invoice/) {
662 # $invoice->open_amount may be negative for ap_transaction but may be positiv for negativ ap_transaction
663 # if $invoice->open_amount is negative $bank_transaction->amount is positve
664 # if $invoice->open_amount is positive $bank_transaction->amount is negative
665 # but amount of transaction is for both positive
666 $amount_of_transaction *= -1 if $invoice->open_amount == - $amount_of_transaction;
669 my $overpaid_amount = $amount_of_transaction - $invoice->open_amount;
670 $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
671 trans_id => $invoice->id,
672 amount => $amount_of_transaction,
673 payment_type => $payment_type,
676 transdate => $bank_transaction->transdate->to_kivitendo);
677 $bank_transaction->invoice_amount($bank_transaction->amount);
678 $amount_of_transaction = 0;
680 if ($overpaid_amount >= 0.01) {
684 message => $::locale->text('Invoice #1 was overpaid by #2.', $invoice->invnumber, $::form->format_amount(\%::myconfig, $overpaid_amount, 2)),
688 # Record a record link from the bank transaction to the invoice
690 from_table => 'bank_transactions',
692 to_table => $invoice->is_sales ? 'ar' : 'ap',
693 to_id => $invoice->id,
696 SL::DB::RecordLink->new(@props)->save;
698 # "close" a sepa_export_item if it exists
699 # code duplicated in action_save_proposals!
700 # currently only works, if there is only exactly one open sepa_export_item
701 if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
702 if ( scalar @$seis == 1 ) {
703 # moved the execution and the check for sepa_export into a method,
704 # this isn't part of a transaction, though
705 $seis->[0]->set_executed if $invoice->id == $seis->[0]->arap_id;
710 $bank_transaction->save;
712 # 'undef' means 'no error' here.
717 my $rez = $data{bank_transaction}->db->with_transaction(sub {
719 $error = $worker->();
730 # Rollback Fehler nicht weiterreichen
734 return grep { $_ } ($error, @warnings);
742 $::auth->assert('bank_transaction');
749 sub make_filter_summary {
752 my $filter = $::form->{filter} || {};
756 [ $filter->{"transdate:date::ge"}, $::locale->text('Transdate') . " " . $::locale->text('From Date') ],
757 [ $filter->{"transdate:date::le"}, $::locale->text('Transdate') . " " . $::locale->text('To Date') ],
758 [ $filter->{"valutadate:date::ge"}, $::locale->text('Valutadate') . " " . $::locale->text('From Date') ],
759 [ $filter->{"valutadate:date::le"}, $::locale->text('Valutadate') . " " . $::locale->text('To Date') ],
760 [ $filter->{"amount:number"}, $::locale->text('Amount') ],
761 [ $filter->{"bank_account_id:integer"}, $::locale->text('Local bank account') ],
765 push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
768 $self->{filter_summary} = join ', ', @filter_strings;
774 my $callback = $self->models->get_callback;
776 my $report = SL::ReportGenerator->new(\%::myconfig, $::form);
777 $self->{report} = $report;
779 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);
780 my @sortable = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount purpose local_account_number local_bank_code);
783 transdate => { sub => sub { $_[0]->transdate_as_date } },
784 valutadate => { sub => sub { $_[0]->valutadate_as_date } },
786 remote_account_number => { },
787 remote_bank_code => { },
788 amount => { sub => sub { $_[0]->amount_as_number },
790 invoice_amount => { sub => sub { $_[0]->invoice_amount_as_number },
792 invoices => { sub => sub { $_[0]->linked_invoices } },
793 currency => { sub => sub { $_[0]->currency->name } },
795 local_account_number => { sub => sub { $_[0]->local_bank_account->account_number } },
796 local_bank_code => { sub => sub { $_[0]->local_bank_account->bank_code } },
797 local_bank_name => { sub => sub { $_[0]->local_bank_account->name } },
801 map { $column_defs{$_}->{text} ||= $::locale->text( $self->models->get_sort_spec->{$_}->{title} ) } keys %column_defs;
803 $report->set_options(
804 std_column_visibility => 1,
805 controller_class => 'BankTransaction',
806 output_format => 'HTML',
807 top_info_text => $::locale->text('Bank transactions'),
808 title => $::locale->text('Bank transactions'),
809 allow_pdf_export => 1,
810 allow_csv_export => 1,
812 $report->set_columns(%column_defs);
813 $report->set_column_order(@columns);
814 $report->set_export_options(qw(list_all filter));
815 $report->set_options_from_form;
816 $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
817 $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
819 my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted();
821 $report->set_options(
822 raw_top_info_text => $self->render('bank_transactions/report_top', { output => 0 }, BANK_ACCOUNTS => $bank_accounts),
823 raw_bottom_info_text => $self->render('bank_transactions/report_bottom', { output => 0 }),
827 sub _existing_record_link {
828 my ($bt, $invoice) = @_;
830 # check whether a record link from banktransaction $bt already exists to
831 # invoice $invoice, returns 1 if that is the case
833 die unless $bt->isa("SL::DB::BankTransaction") && ( $invoice->isa("SL::DB::Invoice") || $invoice->isa("SL::DB::PurchaseInvoice") );
835 my $linked_record_to_table = $invoice->is_sales ? 'Invoice' : 'PurchaseInvoice';
836 my $linked_records = $bt->linked_records( direction => 'to', to => $linked_record_to_table, query => [ id => $invoice->id ] );
838 return @$linked_records ? 1 : 0;
841 sub init_problems { [] }
846 SL::Controller::Helper::GetModels->new(
851 dir => 0, # 1 = ASC, 0 = DESC : default sort is newest at top
853 transdate => t8('Transdate'),
854 remote_name => t8('Remote name'),
855 amount => t8('Amount'),
856 invoice_amount => t8('Assigned'),
857 invoices => t8('Linked invoices'),
858 valutadate => t8('Valutadate'),
859 remote_account_number => t8('Remote account number'),
860 remote_bank_code => t8('Remote bank code'),
861 currency => t8('Currency'),
862 purpose => t8('Purpose'),
863 local_account_number => t8('Local account number'),
864 local_bank_code => t8('Local bank code'),
865 local_bank_name => t8('Bank account'),
867 with_objects => [ 'local_bank_account', 'currency' ],
871 sub load_ap_record_template_url {
872 my ($self, $template) = @_;
874 return $self->url_for(
875 controller => 'ap.pl',
876 action => 'load_record_template',
878 'form_defaults.amount_1' => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
879 'form_defaults.transdate' => $self->transaction->transdate_as_date,
880 'form_defaults.duedate' => $self->transaction->transdate_as_date,
881 'form_defaults.no_payment_bookings' => 1,
882 'form_defaults.paid_1_suggestion' => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
883 'form_defaults.AP_paid_1_suggestion' => $self->transaction->local_bank_account->chart->accno,
884 'form_defaults.callback' => $self->callback,
888 sub load_gl_record_template_url {
889 my ($self, $template) = @_;
891 return $self->url_for(
892 controller => 'gl.pl',
893 action => 'load_record_template',
895 'form_defaults.amount_1' => abs($self->transaction->amount), # always positive
896 'form_defaults.transdate' => $self->transaction->transdate_as_date,
897 'form_defaults.callback' => $self->callback,
901 sub setup_search_action_bar {
902 my ($self, %params) = @_;
904 for my $bar ($::request->layout->get('actionbar')) {
908 submit => [ '#search_form', { action => 'BankTransaction/list' } ],
909 accesskey => 'enter',
915 sub setup_list_all_action_bar {
916 my ($self, %params) = @_;
918 for my $bar ($::request->layout->get('actionbar')) {
922 submit => [ '#filter_form', { action => 'BankTransaction/list_all' } ],
923 accesskey => 'enter',
938 SL::Controller::BankTransaction - Posting payments to invoices from
939 bank transactions imported earlier
945 =item C<save_single_bank_transaction %params>
947 Takes a bank transaction ID (as parameter C<bank_transaction_id> and
948 tries to post its amount to a certain number of invoices (parameter
949 C<invoice_ids>, an array ref of database IDs to purchase or sales
952 The whole function is wrapped in a database transaction. If an
953 exception occurs the bank transaction is not posted at all. The same
954 is true if the code detects an error during the execution, e.g. a bank
955 transaction that's already been posted earlier. In both cases the
956 database transaction will be rolled back.
958 If warnings but not errors occur the database transaction is still
961 The return value is an error object or C<undef> if the function
962 succeeded. The calling function will collect all warnings and errors
963 and display them in a nicely formatted table if any occurred.
965 An error object is a hash reference containing the following members:
969 =item * C<result> — can be either C<warning> or C<error>. Warnings are
970 displayed slightly different than errors.
972 =item * C<message> — a human-readable message included in the list of
973 errors meant as the description of why the problem happened
975 =item * C<bank_transaction_id>, C<invoice_ids> — the same parameters
976 that the function was called with
978 =item * C<bank_transaction> — the database object
979 (C<SL::DB::BankTransaction>) corresponding to C<bank_transaction_id>
981 =item * C<invoices> — an array ref of the database objects (either
982 C<SL::DB::Invoice> or C<SL::DB::PurchaseInvoice>) corresponding to
991 Niclas Zimmermann E<lt>niclas@kivitendo-premium.deE<gt>,
992 Geoffrey Richardson E<lt>information@richardson-bueren.deE<gt>