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::UtilsBy qw(partition_by);
30 use List::MoreUtils qw(any);
31 use List::Util qw(max);
33 use Rose::Object::MakeMethods::Generic
35 scalar => [ qw(callback transaction) ],
36 'scalar --get_set_init' => [ qw(models problems) ],
39 __PACKAGE__->run_before('check_auth');
49 my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted( query => [ obsolete => 0 ] );
51 $self->setup_search_action_bar;
52 $self->render('bank_transactions/search',
53 BANK_ACCOUNTS => $bank_accounts);
59 $self->make_filter_summary;
60 $self->prepare_report;
62 $self->setup_list_all_action_bar;
63 $self->report_generator_list_objects(report => $self->{report}, objects => $self->models->get);
69 if (!$::form->{filter}{bank_account}) {
70 flash('error', t8('No bank account chosen!'));
75 my $sort_by = $::form->{sort_by} || 'transdate';
76 $sort_by = 'transdate' if $sort_by eq 'proposal';
77 $sort_by .= $::form->{sort_dir} ? ' DESC' : ' ASC';
79 my $fromdate = $::locale->parse_date_to_object($::form->{filter}->{fromdate});
80 my $todate = $::locale->parse_date_to_object($::form->{filter}->{todate});
81 $todate->add( days => 1 ) if $todate;
84 push @where, (transdate => { ge => $fromdate }) if ($fromdate);
85 push @where, (transdate => { lt => $todate }) if ($todate);
86 my $bank_account = SL::DB::Manager::BankAccount->find_by( id => $::form->{filter}{bank_account} );
87 # bank_transactions no younger than starting date,
88 # including starting date (same search behaviour as fromdate)
89 # but OPEN invoices to be matched may be from before
90 if ( $bank_account->reconciliation_starting_date ) {
91 push @where, (transdate => { ge => $bank_account->reconciliation_starting_date });
94 my $bank_transactions = SL::DB::Manager::BankTransaction->get_all(
95 with_objects => [ 'local_bank_account', 'currency' ],
99 amount => {ne => \'invoice_amount'},
100 local_bank_account_id => $::form->{filter}{bank_account},
104 # credit notes have a negative amount, treat differently
105 my $all_open_ar_invoices = SL::DB::Manager::Invoice ->get_all(where => [ or => [ amount => { gt => \'paid' },
106 and => [ type => 'credit_note',
107 amount => { lt => \'paid' }
111 with_objects => ['customer','payment_terms']);
113 my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => [amount => { ne => \'paid' }], with_objects => ['vendor' ,'payment_terms']);
114 my $all_open_sepa_export_items = SL::DB::Manager::SepaExportItem->get_all(where => [chart_id => $bank_account->chart_id ,
115 'sepa_export.executed' => 0, 'sepa_export.closed' => 0 ], with_objects => ['sepa_export']);
117 my @all_open_invoices;
118 # filter out invoices with less than 1 cent outstanding
119 push @all_open_invoices, map { $_->{is_ar}=1 ; $_ } grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ar_invoices };
120 push @all_open_invoices, map { $_->{is_ar}=0 ; $_ } grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
123 my %sepa_export_items_by_id = partition_by { $_->ar_id || $_->ap_id } @$all_open_sepa_export_items;
125 # first collect sepa export items to open invoices
126 foreach my $open_invoice (@all_open_invoices){
127 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount,2);
128 $open_invoice->{skonto_type} = 'without_skonto';
129 foreach (@{ $sepa_export_items_by_id{ $open_invoice->id } || [] }) {
130 my $factor = ($_->ar_id == $open_invoice->id ? 1 : -1);
131 $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;
142 # try to match each bank_transaction with each of the possible open invoices
146 foreach my $bt (@{ $bank_transactions }) {
147 ## 5 Stellen hinter dem Komma auf 2 Stellen reduzieren
148 $bt->amount($bt->amount*1);
149 $bt->invoice_amount($bt->invoice_amount*1);
151 $bt->{proposals} = [];
152 $bt->{rule_matches} = [];
154 $bt->{remote_name} .= $bt->{remote_name_1} if $bt->{remote_name_1};
156 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);
170 # batch transaction has no remotename !!
172 next unless $bt->{remote_name}; # bank has no name, usually fees, use create invoice to assign
175 # try to match the current $bt to each of the open_invoices, saving the
176 # results of get_agreement_with_invoice in $open_invoice->{agreement} and
177 # $open_invoice->{rule_matches}.
179 # The values are overwritten each time a new bt is checked, so at the end
180 # of each bt the likely results are filtered and those values are stored in
181 # the arrays $bt->{proposals} and $bt->{rule_matches}, and the agreement
182 # score is stored in $bt->{agreement}
184 foreach my $open_invoice (@all_open_invoices) {
185 ($open_invoice->{agreement}, $open_invoice->{rule_matches}) = $bt->get_agreement_with_invoice($open_invoice,
186 sepa_export_items => $all_open_sepa_export_items,
188 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,
189 $open_invoice->amount * ($open_invoice->{is_ar} ? 1 : -1), 2);
193 my $min_agreement = 3; # suggestions must have at least this score
195 my $max_agreement = max map { $_->{agreement} } @all_open_invoices;
197 # add open_invoices with highest agreement into array $bt->{proposals}
198 if ( $max_agreement >= $min_agreement ) {
199 $bt->{proposals} = [ grep { $_->{agreement} == $max_agreement } @all_open_invoices ];
200 $bt->{agreement} = $max_agreement; #scalar @{ $bt->{proposals} } ? $agreement + 1 : '';
202 # store the rule_matches in a separate array, so they can be displayed in template
203 foreach ( @{ $bt->{proposals} } ) {
204 push(@{$bt->{rule_matches}}, $_->{rule_matches});
210 # separate filter for proposals (second tab, agreement >= 5 and exactly one match)
211 # to qualify as a proposal there has to be
212 # * agreement >= 5 TODO: make threshold configurable in configuration
213 # * there must be only one exact match
214 # * depending on whether sales or purchase the amount has to have the correct sign (so Gutschriften don't work?)
215 my $proposal_threshold = 5;
216 my @otherproposals = grep {
217 ($_->{agreement} >= $proposal_threshold)
218 && (1 == scalar @{ $_->{proposals} })
219 && (@{ $_->{proposals} }[0]->is_sales ? abs(@{ $_->{proposals} }[0]->amount - $_->amount) < 0.01
220 : abs(@{ $_->{proposals} }[0]->amount + $_->amount) < 0.01)
221 } @{ $bank_transactions };
223 push @proposals, @otherproposals;
225 # sort bank transaction proposals by quality (score) of proposal
226 if ($::form->{sort_by} && $::form->{sort_by} eq 'proposal') {
227 if ($::form->{sort_dir}) {
228 $bank_transactions = [ sort { $a->{agreement} <=> $b->{agreement} } @{ $bank_transactions } ];
230 $bank_transactions = [ sort { $b->{agreement} <=> $a->{agreement} } @{ $bank_transactions } ];
234 # for testing with t/bank/banktransaction.t :
235 if ( $::form->{dont_render_for_test} ) {
236 return $bank_transactions;
239 $::request->layout->add_javascripts("kivi.BankTransaction.js");
240 $self->render('bank_transactions/list',
241 title => t8('Bank transactions MT940'),
242 BANK_TRANSACTIONS => $bank_transactions,
243 PROPOSALS => \@proposals,
244 bank_account => $bank_account,
245 ui_tab => scalar(@proposals) > 0?1:0,
249 sub action_assign_invoice {
252 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
254 $self->render('bank_transactions/assign_invoice',
256 title => t8('Assign invoice'),);
259 sub action_create_invoice {
261 my %myconfig = %main::myconfig;
263 $self->transaction(SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id}));
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 my $max_count = $count;
506 foreach (@{ $self->problems }) {
507 $count-- if $_->{result} eq 'error';
509 return ($count, $max_count);
512 sub action_save_invoices {
514 my ($success_count, $max_count) = $self->save_invoices();
516 if ($success_count == $max_count) {
517 flash('ok', t8('#1 invoice(s) saved.', $success_count));
519 flash('error', t8('At least #1 invoice(s) not saved', $max_count - $success_count));
522 $self->action_list();
525 sub action_save_proposals {
528 if ( $::form->{proposal_ids} ) {
529 my $propcount = scalar(@{ $::form->{proposal_ids} });
530 if ( $propcount > 0 ) {
531 my $count = $self->save_invoices();
533 flash('ok', t8('#1 proposal(s) with #2 invoice(s) saved.', $propcount, $count));
536 $self->action_list();
540 sub save_single_bank_transaction {
541 my ($self, %params) = @_;
545 bank_transaction => SL::DB::Manager::BankTransaction->find_by(id => $params{bank_transaction_id}),
549 if (!$data{bank_transaction}) {
553 message => $::locale->text('The ID #1 is not a valid database ID.', $data{bank_transaction_id}),
557 my $bank_transaction = $data{bank_transaction};
560 if (@{ $bank_transaction->linked_invoices } || $bank_transaction->invoice_amount != 0) {
564 message => $::locale->text("Bank transaction with id #1 has already been linked to one or more record and/or some amount is already assigned.", $bank_transaction->id),
570 my $bt_id = $data{bank_transaction_id};
571 my $sign = $bank_transaction->amount < 0 ? -1 : 1;
572 my $amount_of_transaction = $sign * $bank_transaction->amount;
573 my $payment_received = $bank_transaction->amount > 0;
574 my $payment_sent = $bank_transaction->amount < 0;
577 foreach my $invoice_id (@{ $params{invoice_ids} }) {
578 my $invoice = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
583 message => $::locale->text("The ID #1 is not a valid database ID.", $invoice_id),
586 push @{ $data{invoices} }, $invoice;
589 if ( $payment_received
590 && any { ( $_->is_sales && ($_->amount < 0))
591 || (!$_->is_sales && ($_->amount > 0))
592 } @{ $data{invoices} }) {
596 message => $::locale->text("Received payments can only be posted for sales invoices and purchase credit notes."),
601 && any { ( $_->is_sales && ($_->amount > 0))
602 || (!$_->is_sales && ($_->amount < 0) && ($_->invoice_type eq 'purchase_invoice'))
603 } @{ $data{invoices} }) {
607 message => $::locale->text("Sent payments can only be posted for purchase invoices and sales credit notes."),
611 my $max_invoices = scalar(@{ $data{invoices} });
614 foreach my $invoice (@{ $data{invoices} }) {
615 my $source = ($data{sources} // [])->[$n_invoices];
616 my $memo = ($data{memos} // [])->[$n_invoices];
621 if (!$amount_of_transaction && $invoice->open_amount) {
625 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."),
630 if ( defined $::form->{invoice_skontos}->{"$bt_id"} ) {
631 $payment_type = shift(@{ $::form->{invoice_skontos}->{"$bt_id"} });
633 $payment_type = 'without_skonto';
637 # pay invoice or go to the next bank transaction if the amount is not sufficiently high
638 if ($invoice->open_amount <= $amount_of_transaction && $n_invoices < $max_invoices) {
639 my $open_amount = ($payment_type eq 'with_skonto_pt'?$invoice->amount_less_skonto:$invoice->open_amount);
640 # first calculate new bank transaction amount ...
641 if ($invoice->is_sales) {
642 $amount_of_transaction -= $sign * $open_amount;
643 $bank_transaction->invoice_amount($bank_transaction->invoice_amount + $open_amount);
645 $amount_of_transaction += $sign * $open_amount;
646 $bank_transaction->invoice_amount($bank_transaction->invoice_amount - $open_amount);
648 # ... and then pay the invoice
649 $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
650 trans_id => $invoice->id,
651 amount => $open_amount,
652 payment_type => $payment_type,
655 transdate => $bank_transaction->transdate->to_kivitendo);
657 # use the whole amount of the bank transaction for the invoice, overpay the invoice if necessary
659 # this catches credit_notes and negative sales invoices
660 if ( $invoice->is_sales && $invoice->amount < 0 ) {
661 # $invoice->open_amount is negative for credit_notes
662 # $bank_transaction->amount is negative for outgoing transactions
663 # so $amount_of_transaction is negative but needs positive
664 $amount_of_transaction *= -1;
666 } elsif (!$invoice->is_sales && $invoice->invoice_type =~ m/ap_transaction|purchase_invoice/) {
667 # $invoice->open_amount may be negative for ap_transaction but may be positiv for negativ ap_transaction
668 # if $invoice->open_amount is negative $bank_transaction->amount is positve
669 # if $invoice->open_amount is positive $bank_transaction->amount is negative
670 # but amount of transaction is for both positive
671 $amount_of_transaction *= -1 if $invoice->open_amount == - $amount_of_transaction;
674 my $overpaid_amount = $amount_of_transaction - $invoice->open_amount;
675 $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
676 trans_id => $invoice->id,
677 amount => $amount_of_transaction,
678 payment_type => $payment_type,
681 transdate => $bank_transaction->transdate->to_kivitendo);
682 $bank_transaction->invoice_amount($bank_transaction->amount);
683 $amount_of_transaction = 0;
685 if ($overpaid_amount >= 0.01) {
689 message => $::locale->text('Invoice #1 was overpaid by #2.', $invoice->invnumber, $::form->format_amount(\%::myconfig, $overpaid_amount, 2)),
693 # Record a record link from the bank transaction to the invoice
695 from_table => 'bank_transactions',
697 to_table => $invoice->is_sales ? 'ar' : 'ap',
698 to_id => $invoice->id,
701 SL::DB::RecordLink->new(@props)->save;
703 # "close" a sepa_export_item if it exists
704 # code duplicated in action_save_proposals!
705 # currently only works, if there is only exactly one open sepa_export_item
706 if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
707 if ( scalar @$seis == 1 ) {
708 # moved the execution and the check for sepa_export into a method,
709 # this isn't part of a transaction, though
710 $seis->[0]->set_executed if $invoice->id == $seis->[0]->arap_id;
715 $bank_transaction->save;
717 # 'undef' means 'no error' here.
722 my $rez = $data{bank_transaction}->db->with_transaction(sub {
724 $error = $worker->();
735 # Rollback Fehler nicht weiterreichen
737 # aber einen rollback von hand
738 $::lxdebug->message(LXDebug->DEBUG2(),"finish worker with ". ($error ? $error->{result} : '-'));
739 $data{bank_transaction}->db->dbh->rollback if $error && $error->{result} eq 'error';
742 return grep { $_ } ($error, @warnings);
750 $::auth->assert('bank_transaction');
757 sub make_filter_summary {
760 my $filter = $::form->{filter} || {};
764 [ $filter->{"transdate:date::ge"}, $::locale->text('Transdate') . " " . $::locale->text('From Date') ],
765 [ $filter->{"transdate:date::le"}, $::locale->text('Transdate') . " " . $::locale->text('To Date') ],
766 [ $filter->{"valutadate:date::ge"}, $::locale->text('Valutadate') . " " . $::locale->text('From Date') ],
767 [ $filter->{"valutadate:date::le"}, $::locale->text('Valutadate') . " " . $::locale->text('To Date') ],
768 [ $filter->{"amount:number"}, $::locale->text('Amount') ],
769 [ $filter->{"bank_account_id:integer"}, $::locale->text('Local bank account') ],
773 push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
776 $self->{filter_summary} = join ', ', @filter_strings;
782 my $callback = $self->models->get_callback;
784 my $report = SL::ReportGenerator->new(\%::myconfig, $::form);
785 $self->{report} = $report;
787 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);
788 my @sortable = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount purpose local_account_number local_bank_code);
791 transdate => { sub => sub { $_[0]->transdate_as_date } },
792 valutadate => { sub => sub { $_[0]->valutadate_as_date } },
794 remote_account_number => { },
795 remote_bank_code => { },
796 amount => { sub => sub { $_[0]->amount_as_number },
798 invoice_amount => { sub => sub { $_[0]->invoice_amount_as_number },
800 invoices => { sub => sub { $_[0]->linked_invoices } },
801 currency => { sub => sub { $_[0]->currency->name } },
803 local_account_number => { sub => sub { $_[0]->local_bank_account->account_number } },
804 local_bank_code => { sub => sub { $_[0]->local_bank_account->bank_code } },
805 local_bank_name => { sub => sub { $_[0]->local_bank_account->name } },
809 map { $column_defs{$_}->{text} ||= $::locale->text( $self->models->get_sort_spec->{$_}->{title} ) } keys %column_defs;
811 $report->set_options(
812 std_column_visibility => 1,
813 controller_class => 'BankTransaction',
814 output_format => 'HTML',
815 top_info_text => $::locale->text('Bank transactions'),
816 title => $::locale->text('Bank transactions'),
817 allow_pdf_export => 1,
818 allow_csv_export => 1,
820 $report->set_columns(%column_defs);
821 $report->set_column_order(@columns);
822 $report->set_export_options(qw(list_all filter));
823 $report->set_options_from_form;
824 $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
825 $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
827 my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted();
829 $report->set_options(
830 raw_top_info_text => $self->render('bank_transactions/report_top', { output => 0 }, BANK_ACCOUNTS => $bank_accounts),
831 raw_bottom_info_text => $self->render('bank_transactions/report_bottom', { output => 0 }),
835 sub init_problems { [] }
840 SL::Controller::Helper::GetModels->new(
845 dir => 0, # 1 = ASC, 0 = DESC : default sort is newest at top
847 transdate => t8('Transdate'),
848 remote_name => t8('Remote name'),
849 amount => t8('Amount'),
850 invoice_amount => t8('Assigned'),
851 invoices => t8('Linked invoices'),
852 valutadate => t8('Valutadate'),
853 remote_account_number => t8('Remote account number'),
854 remote_bank_code => t8('Remote bank code'),
855 currency => t8('Currency'),
856 purpose => t8('Purpose'),
857 local_account_number => t8('Local account number'),
858 local_bank_code => t8('Local bank code'),
859 local_bank_name => t8('Bank account'),
861 with_objects => [ 'local_bank_account', 'currency' ],
865 sub load_ap_record_template_url {
866 my ($self, $template) = @_;
868 return $self->url_for(
869 controller => 'ap.pl',
870 action => 'load_record_template',
872 'form_defaults.amount_1' => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
873 'form_defaults.transdate' => $self->transaction->transdate_as_date,
874 'form_defaults.duedate' => $self->transaction->transdate_as_date,
875 'form_defaults.no_payment_bookings' => 1,
876 'form_defaults.paid_1_suggestion' => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
877 'form_defaults.AP_paid_1_suggestion' => $self->transaction->local_bank_account->chart->accno,
878 'form_defaults.callback' => $self->callback,
882 sub load_gl_record_template_url {
883 my ($self, $template) = @_;
885 return $self->url_for(
886 controller => 'gl.pl',
887 action => 'load_record_template',
889 'form_defaults.amount_1' => abs($self->transaction->amount), # always positive
890 'form_defaults.transdate' => $self->transaction->transdate_as_date,
891 'form_defaults.callback' => $self->callback,
892 'form_defaults.bt_id' => $self->transaction->id,
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 This method cannot handle already partly assigned bank transactions, i.e.
948 a bank transaction that has a invoice_amount <> 0 but not the fully
949 transaction amount (invoice_amount == amount).
950 Currently this state is impossible from the point of the user interface,
951 but for double safety and further reliance posting an bank_transaction
952 where some invoice_amount is already assigned or a RecordLink from
953 bank to document exists will not be accepted.
955 If the amount of the bank transaction is higher than the sum of
956 the assigned invoices (1 .. n) the last invoice will be overpayed.
958 Therefore this function implements not all valid uses cases.
960 The whole function is wrapped in a database transaction. If an
961 exception occurs the bank transaction is not posted at all. The same
962 is true if the code detects an error during the execution, e.g. a bank
963 transaction that's already been posted earlier. In both cases the
964 database transaction will be rolled back.
966 If warnings but not errors occur the database transaction is still
969 The return value is an error object or C<undef> if the function
970 succeeded. The calling function will collect all warnings and errors
971 and display them in a nicely formatted table if any occurred.
973 An error object is a hash reference containing the following members:
977 =item * C<result> — can be either C<warning> or C<error>. Warnings are
978 displayed slightly different than errors.
980 =item * C<message> — a human-readable message included in the list of
981 errors meant as the description of why the problem happened
983 =item * C<bank_transaction_id>, C<invoice_ids> — the same parameters
984 that the function was called with
986 =item * C<bank_transaction> — the database object
987 (C<SL::DB::BankTransaction>) corresponding to C<bank_transaction_id>
989 =item * C<invoices> — an array ref of the database objects (either
990 C<SL::DB::Invoice> or C<SL::DB::PurchaseInvoice>) corresponding to
999 Niclas Zimmermann E<lt>niclas@kivitendo-premium.deE<gt>,
1000 Geoffrey Richardson E<lt>information@richardson-bueren.deE<gt>