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 # This was dead code: We compared vendor.account_name with bank_transaction.iban.
266 # This did never match (Kontonummer != IBAN). It's kivis 09/02 (2013) day
267 # If refactored/improved, also consider that vendor.iban should be normalized
268 # user may like to input strings like: 'AT 3333 3333 2222 1111' -> can be checked strictly
269 # at Vendor code because we need the correct data for all sepa exports.
271 my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(iban => $self->transaction->{remote_account_number});
272 my $use_vendor_filter = $self->transaction->{remote_account_number} && $vendor_of_transaction;
274 my $templates_ap = SL::DB::Manager::RecordTemplate->get_all(
275 where => [ template_type => 'ap_transaction' ],
276 with_objects => [ qw(employee vendor) ],
278 my $templates_gl = SL::DB::Manager::RecordTemplate->get_all(
279 query => [ template_type => 'gl_transaction',
280 chart_id => SL::DB::Manager::BankAccount->find_by(id => $self->transaction->local_bank_account_id)->chart_id,
282 with_objects => [ qw(employee record_template_items) ],
285 # pre filter templates_ap, if we have a vendor match (IBAN eq IBAN) - show and allow user to edit this via gui!
286 $templates_ap = [ grep { $_->vendor_id == $vendor_of_transaction->id } @{ $templates_ap } ] if $use_vendor_filter;
288 $self->callback($self->url_for(
290 'filter.bank_account' => $::form->{filter}->{bank_account},
291 'filter.todate' => $::form->{filter}->{todate},
292 'filter.fromdate' => $::form->{filter}->{fromdate},
296 'bank_transactions/create_invoice',
298 title => t8('Create invoice'),
299 TEMPLATES_GL => $use_vendor_filter && @{ $templates_ap } ? undef : $templates_gl,
300 TEMPLATES_AP => $templates_ap,
301 vendor_name => $use_vendor_filter && @{ $templates_ap } ? $vendor_of_transaction->name : undef,
305 sub action_ajax_payment_suggestion {
308 # based on a BankTransaction ID and a Invoice or PurchaseInvoice ID passed via $::form,
309 # create an HTML blob to be used by the js function add_invoices in templates/webpages/bank_transactions/list.html
310 # and return encoded as JSON
312 my $bt = SL::DB::Manager::BankTransaction->find_by( id => $::form->{bt_id} );
313 my $invoice = SL::DB::Manager::Invoice->find_by( id => $::form->{prop_id} ) || SL::DB::Manager::PurchaseInvoice->find_by( id => $::form->{prop_id} );
315 die unless $bt and $invoice;
317 my @select_options = $invoice->get_payment_select_options_for_bank_transaction($::form->{bt_id});
320 $html = $self->render(
321 'bank_transactions/_payment_suggestion', { output => 0 },
322 bt_id => $::form->{bt_id},
323 prop_id => $::form->{prop_id},
325 SELECT_OPTIONS => \@select_options,
328 $self->render(\ SL::JSON::to_json( { 'html' => "$html" } ), { layout => 0, type => 'json', process => 0 });
331 sub action_filter_templates {
334 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
336 my (@filter, @filter_ap);
338 # filter => gl and ap | filter_ap = ap (i.e. vendorname)
339 push @filter, ('template_name' => { ilike => '%' . $::form->{template} . '%' }) if $::form->{template};
340 push @filter, ('reference' => { ilike => '%' . $::form->{reference} . '%' }) if $::form->{reference};
341 push @filter_ap, ('vendor.name' => { ilike => '%' . $::form->{vendor} . '%' }) if $::form->{vendor};
342 push @filter_ap, @filter;
343 my $templates_gl = SL::DB::Manager::RecordTemplate->get_all(
344 query => [ template_type => 'gl_transaction',
345 chart_id => SL::DB::Manager::BankAccount->find_by(id => $self->transaction->local_bank_account_id)->chart_id,
346 (and => \@filter) x !!@filter
348 with_objects => [ qw(employee record_template_items) ],
351 my $templates_ap = SL::DB::Manager::RecordTemplate->get_all(
352 where => [ template_type => 'ap_transaction', (and => \@filter_ap) x !!@filter_ap ],
353 with_objects => [ qw(employee vendor) ],
355 $::form->{filter} //= {};
357 $self->callback($self->url_for(
359 'filter.bank_account' => $::form->{filter}->{bank_account},
360 'filter.todate' => $::form->{filter}->{todate},
361 'filter.fromdate' => $::form->{filter}->{fromdate},
364 my $output = $self->render(
365 'bank_transactions/_template_list',
367 TEMPLATES_AP => $templates_ap,
368 TEMPLATES_GL => $templates_gl,
371 $self->render(\to_json({ html => $output }), { type => 'json', process => 0 });
374 sub action_ajax_add_list {
377 my @where_sale = (amount => { ne => \'paid' });
378 my @where_purchase = (amount => { ne => \'paid' });
380 if ($::form->{invnumber}) {
381 push @where_sale, (invnumber => { ilike => like($::form->{invnumber})});
382 push @where_purchase, (invnumber => { ilike => like($::form->{invnumber})});
385 if ($::form->{amount}) {
386 push @where_sale, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
387 push @where_purchase, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
390 if ($::form->{vcnumber}) {
391 push @where_sale, ('customer.customernumber' => { ilike => like($::form->{vcnumber})});
392 push @where_purchase, ('vendor.vendornumber' => { ilike => like($::form->{vcnumber})});
395 if ($::form->{vcname}) {
396 push @where_sale, ('customer.name' => { ilike => like($::form->{vcname})});
397 push @where_purchase, ('vendor.name' => { ilike => like($::form->{vcname})});
400 if ($::form->{transdatefrom}) {
401 my $fromdate = $::locale->parse_date_to_object($::form->{transdatefrom});
402 if ( ref($fromdate) eq 'DateTime' ) {
403 push @where_sale, ('transdate' => { ge => $fromdate});
404 push @where_purchase, ('transdate' => { ge => $fromdate});
408 if ($::form->{transdateto}) {
409 my $todate = $::locale->parse_date_to_object($::form->{transdateto});
410 if ( ref($todate) eq 'DateTime' ) {
411 $todate->add(days => 1);
412 push @where_sale, ('transdate' => { lt => $todate});
413 push @where_purchase, ('transdate' => { lt => $todate});
417 my $all_open_ar_invoices = SL::DB::Manager::Invoice ->get_all(where => \@where_sale, with_objects => 'customer');
418 my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => \@where_purchase, with_objects => 'vendor');
420 my @all_open_invoices = @{ $all_open_ar_invoices };
421 # add ap invoices, filtering out subcent open amounts
422 push @all_open_invoices, grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
424 @all_open_invoices = sort { $a->id <=> $b->id } @all_open_invoices;
426 my $output = $self->render(
427 'bank_transactions/add_list',
429 INVOICES => \@all_open_invoices,
432 my %result = ( count => 0, html => $output );
434 $self->render(\to_json(\%result), { type => 'json', process => 0 });
437 sub action_ajax_accept_invoices {
440 my @selected_invoices;
441 foreach my $invoice_id (@{ $::form->{invoice_id} || [] }) {
442 my $invoice_object = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
443 push @selected_invoices, $invoice_object;
447 'bank_transactions/invoices',
449 INVOICES => \@selected_invoices,
450 bt_id => $::form->{bt_id},
457 return 0 if !$::form->{invoice_ids};
459 my %invoice_hash = %{ delete $::form->{invoice_ids} }; # each key (the bt line with a bt_id) contains an array of invoice_ids
461 # e.g. three partial payments with bt_ids 54, 55 and 56 for invoice with id 74:
474 # or if the payment with bt_id 44 is used to pay invoices with ids 50, 51 and 52
476 # '44' => [ '50', '51', 52' ]
479 $::form->{invoice_skontos} ||= {}; # hash of arrays containing the payment types, could be empty
481 # a bank_transaction may be assigned to several invoices, i.e. a customer
482 # might pay several open invoices with one transaction
488 if ( $::form->{proposal_ids} ) {
489 foreach (@{ $::form->{proposal_ids} }) {
490 my $bank_transaction_id = $_;
491 my $invoice_ids = $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 => ($::form->{sources} // {})->{$_},
496 memos => ($::form->{memos} // {})->{$_},
498 $count += scalar( @{$invoice_ids} );
501 while ( my ($bank_transaction_id, $invoice_ids) = each(%invoice_hash) ) {
502 push @{ $self->problems }, $self->save_single_bank_transaction(
503 bank_transaction_id => $bank_transaction_id,
504 invoice_ids => $invoice_ids,
505 sources => [ map { $::form->{"sources_${bank_transaction_id}_${_}"} } @{ $invoice_ids } ],
506 memos => [ map { $::form->{"memos_${bank_transaction_id}_${_}"} } @{ $invoice_ids } ],
508 $count += scalar( @{$invoice_ids} );
511 my $max_count = $count;
512 foreach (@{ $self->problems }) {
513 $count-- if $_->{result} eq 'error';
515 return ($count, $max_count);
518 sub action_save_invoices {
520 my ($success_count, $max_count) = $self->save_invoices();
522 if ($success_count == $max_count) {
523 flash('ok', t8('#1 invoice(s) saved.', $success_count));
525 flash('error', t8('At least #1 invoice(s) not saved', $max_count - $success_count));
528 $self->action_list();
531 sub action_save_proposals {
534 if ( $::form->{proposal_ids} ) {
535 my $propcount = scalar(@{ $::form->{proposal_ids} });
536 if ( $propcount > 0 ) {
537 my $count = $self->save_invoices();
539 flash('ok', t8('#1 proposal(s) with #2 invoice(s) saved.', $propcount, $count));
542 $self->action_list();
546 sub save_single_bank_transaction {
547 my ($self, %params) = @_;
551 bank_transaction => SL::DB::Manager::BankTransaction->find_by(id => $params{bank_transaction_id}),
555 if (!$data{bank_transaction}) {
559 message => $::locale->text('The ID #1 is not a valid database ID.', $data{bank_transaction_id}),
566 my $bt_id = $data{bank_transaction_id};
567 my $bank_transaction = $data{bank_transaction};
568 my $sign = $bank_transaction->amount < 0 ? -1 : 1;
569 my $amount_of_transaction = $sign * $bank_transaction->amount;
570 my $payment_received = $bank_transaction->amount > 0;
571 my $payment_sent = $bank_transaction->amount < 0;
574 foreach my $invoice_id (@{ $params{invoice_ids} }) {
575 my $invoice = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
580 message => $::locale->text("The ID #1 is not a valid database ID.", $invoice_id),
583 push @{ $data{invoices} }, $invoice;
586 if ( $payment_received
587 && any { ( $_->is_sales && ($_->amount < 0))
588 || (!$_->is_sales && ($_->amount > 0))
589 } @{ $data{invoices} }) {
593 message => $::locale->text("Received payments can only be posted for sales invoices and purchase credit notes."),
598 && any { ( $_->is_sales && ($_->amount > 0))
599 || (!$_->is_sales && ($_->amount < 0) && ($_->invoice_type eq 'purchase_invoice'))
600 } @{ $data{invoices} }) {
604 message => $::locale->text("Sent payments can only be posted for purchase invoices and sales credit notes."),
608 my $max_invoices = scalar(@{ $data{invoices} });
611 foreach my $invoice (@{ $data{invoices} }) {
612 my $source = ($data{sources} // [])->[$n_invoices];
613 my $memo = ($data{memos} // [])->[$n_invoices];
617 # Check if bank_transaction already has a link to the invoice, may only be linked once per invoice
618 # This might be caused by the user reloading a page and resending the form
619 if (_existing_record_link($bank_transaction, $invoice)) {
623 message => $::locale->text("Bank transaction with id #1 has already been linked to #2.", $bank_transaction->id, $invoice->displayable_name),
627 if (!$amount_of_transaction && $invoice->open_amount) {
631 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."),
636 if ( defined $::form->{invoice_skontos}->{"$bt_id"} ) {
637 $payment_type = shift(@{ $::form->{invoice_skontos}->{"$bt_id"} });
639 $payment_type = 'without_skonto';
643 # pay invoice or go to the next bank transaction if the amount is not sufficiently high
644 if ($invoice->open_amount <= $amount_of_transaction && $n_invoices < $max_invoices) {
645 my $open_amount = ($payment_type eq 'with_skonto_pt'?$invoice->amount_less_skonto:$invoice->open_amount);
646 # first calculate new bank transaction amount ...
647 if ($invoice->is_sales) {
648 $amount_of_transaction -= $sign * $open_amount;
649 $bank_transaction->invoice_amount($bank_transaction->invoice_amount + $open_amount);
651 $amount_of_transaction += $sign * $open_amount;
652 $bank_transaction->invoice_amount($bank_transaction->invoice_amount - $open_amount);
654 # ... and then pay the invoice
655 $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
656 trans_id => $invoice->id,
657 amount => $open_amount,
658 payment_type => $payment_type,
661 transdate => $bank_transaction->transdate->to_kivitendo);
663 # use the whole amount of the bank transaction for the invoice, overpay the invoice if necessary
665 # this catches credit_notes and negative sales invoices
666 if ( $invoice->is_sales && $invoice->amount < 0 ) {
667 # $invoice->open_amount is negative for credit_notes
668 # $bank_transaction->amount is negative for outgoing transactions
669 # so $amount_of_transaction is negative but needs positive
670 $amount_of_transaction *= -1;
672 } elsif (!$invoice->is_sales && $invoice->invoice_type =~ m/ap_transaction|purchase_invoice/) {
673 # $invoice->open_amount may be negative for ap_transaction but may be positiv for negativ ap_transaction
674 # if $invoice->open_amount is negative $bank_transaction->amount is positve
675 # if $invoice->open_amount is positive $bank_transaction->amount is negative
676 # but amount of transaction is for both positive
677 $amount_of_transaction *= -1 if $invoice->open_amount == - $amount_of_transaction;
680 my $overpaid_amount = $amount_of_transaction - $invoice->open_amount;
681 $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
682 trans_id => $invoice->id,
683 amount => $amount_of_transaction,
684 payment_type => $payment_type,
687 transdate => $bank_transaction->transdate->to_kivitendo);
688 $bank_transaction->invoice_amount($bank_transaction->amount);
689 $amount_of_transaction = 0;
691 if ($overpaid_amount >= 0.01) {
695 message => $::locale->text('Invoice #1 was overpaid by #2.', $invoice->invnumber, $::form->format_amount(\%::myconfig, $overpaid_amount, 2)),
699 # Record a record link from the bank transaction to the invoice
701 from_table => 'bank_transactions',
703 to_table => $invoice->is_sales ? 'ar' : 'ap',
704 to_id => $invoice->id,
707 SL::DB::RecordLink->new(@props)->save;
709 # "close" a sepa_export_item if it exists
710 # code duplicated in action_save_proposals!
711 # currently only works, if there is only exactly one open sepa_export_item
712 if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
713 if ( scalar @$seis == 1 ) {
714 # moved the execution and the check for sepa_export into a method,
715 # this isn't part of a transaction, though
716 $seis->[0]->set_executed if $invoice->id == $seis->[0]->arap_id;
721 $bank_transaction->save;
723 # 'undef' means 'no error' here.
728 my $rez = $data{bank_transaction}->db->with_transaction(sub {
730 $error = $worker->();
741 # Rollback Fehler nicht weiterreichen
743 # aber einen rollback von hand
744 $::lxdebug->message(LXDebug->DEBUG2(),"finish worker with ". ($error ? $error->{result} : '-'));
745 $data{bank_transaction}->db->dbh->rollback if $error && $error->{result} eq 'error';
748 return grep { $_ } ($error, @warnings);
756 $::auth->assert('bank_transaction');
763 sub make_filter_summary {
766 my $filter = $::form->{filter} || {};
770 [ $filter->{"transdate:date::ge"}, $::locale->text('Transdate') . " " . $::locale->text('From Date') ],
771 [ $filter->{"transdate:date::le"}, $::locale->text('Transdate') . " " . $::locale->text('To Date') ],
772 [ $filter->{"valutadate:date::ge"}, $::locale->text('Valutadate') . " " . $::locale->text('From Date') ],
773 [ $filter->{"valutadate:date::le"}, $::locale->text('Valutadate') . " " . $::locale->text('To Date') ],
774 [ $filter->{"amount:number"}, $::locale->text('Amount') ],
775 [ $filter->{"bank_account_id:integer"}, $::locale->text('Local bank account') ],
779 push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
782 $self->{filter_summary} = join ', ', @filter_strings;
788 my $callback = $self->models->get_callback;
790 my $report = SL::ReportGenerator->new(\%::myconfig, $::form);
791 $self->{report} = $report;
793 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);
794 my @sortable = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount purpose local_account_number local_bank_code);
797 transdate => { sub => sub { $_[0]->transdate_as_date } },
798 valutadate => { sub => sub { $_[0]->valutadate_as_date } },
800 remote_account_number => { },
801 remote_bank_code => { },
802 amount => { sub => sub { $_[0]->amount_as_number },
804 invoice_amount => { sub => sub { $_[0]->invoice_amount_as_number },
806 invoices => { sub => sub { $_[0]->linked_invoices } },
807 currency => { sub => sub { $_[0]->currency->name } },
809 local_account_number => { sub => sub { $_[0]->local_bank_account->account_number } },
810 local_bank_code => { sub => sub { $_[0]->local_bank_account->bank_code } },
811 local_bank_name => { sub => sub { $_[0]->local_bank_account->name } },
815 map { $column_defs{$_}->{text} ||= $::locale->text( $self->models->get_sort_spec->{$_}->{title} ) } keys %column_defs;
817 $report->set_options(
818 std_column_visibility => 1,
819 controller_class => 'BankTransaction',
820 output_format => 'HTML',
821 top_info_text => $::locale->text('Bank transactions'),
822 title => $::locale->text('Bank transactions'),
823 allow_pdf_export => 1,
824 allow_csv_export => 1,
826 $report->set_columns(%column_defs);
827 $report->set_column_order(@columns);
828 $report->set_export_options(qw(list_all filter));
829 $report->set_options_from_form;
830 $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
831 $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
833 my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted();
835 $report->set_options(
836 raw_top_info_text => $self->render('bank_transactions/report_top', { output => 0 }, BANK_ACCOUNTS => $bank_accounts),
837 raw_bottom_info_text => $self->render('bank_transactions/report_bottom', { output => 0 }),
841 sub _existing_record_link {
842 my ($bt, $invoice) = @_;
844 # check whether a record link from banktransaction $bt already exists to
845 # invoice $invoice, returns 1 if that is the case
847 die unless $bt->isa("SL::DB::BankTransaction") && ( $invoice->isa("SL::DB::Invoice") || $invoice->isa("SL::DB::PurchaseInvoice") );
849 my $linked_record_to_table = $invoice->is_sales ? 'Invoice' : 'PurchaseInvoice';
850 my $linked_records = $bt->linked_records( direction => 'to', to => $linked_record_to_table, query => [ id => $invoice->id ] );
852 return @$linked_records ? 1 : 0;
855 sub init_problems { [] }
860 SL::Controller::Helper::GetModels->new(
865 dir => 0, # 1 = ASC, 0 = DESC : default sort is newest at top
867 transdate => t8('Transdate'),
868 remote_name => t8('Remote name'),
869 amount => t8('Amount'),
870 invoice_amount => t8('Assigned'),
871 invoices => t8('Linked invoices'),
872 valutadate => t8('Valutadate'),
873 remote_account_number => t8('Remote account number'),
874 remote_bank_code => t8('Remote bank code'),
875 currency => t8('Currency'),
876 purpose => t8('Purpose'),
877 local_account_number => t8('Local account number'),
878 local_bank_code => t8('Local bank code'),
879 local_bank_name => t8('Bank account'),
881 with_objects => [ 'local_bank_account', 'currency' ],
885 sub load_ap_record_template_url {
886 my ($self, $template) = @_;
888 return $self->url_for(
889 controller => 'ap.pl',
890 action => 'load_record_template',
892 'form_defaults.amount_1' => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
893 'form_defaults.transdate' => $self->transaction->transdate_as_date,
894 'form_defaults.duedate' => $self->transaction->transdate_as_date,
895 'form_defaults.no_payment_bookings' => 1,
896 'form_defaults.paid_1_suggestion' => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
897 'form_defaults.AP_paid_1_suggestion' => $self->transaction->local_bank_account->chart->accno,
898 'form_defaults.callback' => $self->callback,
902 sub load_gl_record_template_url {
903 my ($self, $template) = @_;
905 return $self->url_for(
906 controller => 'gl.pl',
907 action => 'load_record_template',
909 'form_defaults.amount_1' => abs($self->transaction->amount), # always positive
910 'form_defaults.transdate' => $self->transaction->transdate_as_date,
911 'form_defaults.callback' => $self->callback,
915 sub setup_search_action_bar {
916 my ($self, %params) = @_;
918 for my $bar ($::request->layout->get('actionbar')) {
922 submit => [ '#search_form', { action => 'BankTransaction/list' } ],
923 accesskey => 'enter',
929 sub setup_list_all_action_bar {
930 my ($self, %params) = @_;
932 for my $bar ($::request->layout->get('actionbar')) {
936 submit => [ '#filter_form', { action => 'BankTransaction/list_all' } ],
937 accesskey => 'enter',
952 SL::Controller::BankTransaction - Posting payments to invoices from
953 bank transactions imported earlier
959 =item C<save_single_bank_transaction %params>
961 Takes a bank transaction ID (as parameter C<bank_transaction_id> and
962 tries to post its amount to a certain number of invoices (parameter
963 C<invoice_ids>, an array ref of database IDs to purchase or sales
966 The whole function is wrapped in a database transaction. If an
967 exception occurs the bank transaction is not posted at all. The same
968 is true if the code detects an error during the execution, e.g. a bank
969 transaction that's already been posted earlier. In both cases the
970 database transaction will be rolled back.
972 If warnings but not errors occur the database transaction is still
975 The return value is an error object or C<undef> if the function
976 succeeded. The calling function will collect all warnings and errors
977 and display them in a nicely formatted table if any occurred.
979 An error object is a hash reference containing the following members:
983 =item * C<result> — can be either C<warning> or C<error>. Warnings are
984 displayed slightly different than errors.
986 =item * C<message> — a human-readable message included in the list of
987 errors meant as the description of why the problem happened
989 =item * C<bank_transaction_id>, C<invoice_ids> — the same parameters
990 that the function was called with
992 =item * C<bank_transaction> — the database object
993 (C<SL::DB::BankTransaction>) corresponding to C<bank_transaction_id>
995 =item * C<invoices> — an array ref of the database objects (either
996 C<SL::DB::Invoice> or C<SL::DB::PurchaseInvoice>) corresponding to
1005 Niclas Zimmermann E<lt>niclas@kivitendo-premium.deE<gt>,
1006 Geoffrey Richardson E<lt>information@richardson-bueren.deE<gt>