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_sellprice($open_invoice->amount * ($open_invoice->{is_ar} ? 1 : -1));
192 my $min_agreement = 3; # suggestions must have at least this score
194 my $max_agreement = max map { $_->{agreement} } @all_open_invoices;
196 # add open_invoices with highest agreement into array $bt->{proposals}
197 if ( $max_agreement >= $min_agreement ) {
198 $bt->{proposals} = [ grep { $_->{agreement} == $max_agreement } @all_open_invoices ];
199 $bt->{agreement} = $max_agreement; #scalar @{ $bt->{proposals} } ? $agreement + 1 : '';
201 # store the rule_matches in a separate array, so they can be displayed in template
202 foreach ( @{ $bt->{proposals} } ) {
203 push(@{$bt->{rule_matches}}, $_->{rule_matches});
209 # separate filter for proposals (second tab, agreement >= 5 and exactly one match)
210 # to qualify as a proposal there has to be
211 # * agreement >= 5 TODO: make threshold configurable in configuration
212 # * there must be only one exact match
213 # * depending on whether sales or purchase the amount has to have the correct sign (so Gutschriften don't work?)
214 my $proposal_threshold = 5;
215 my @otherproposals = grep {
216 ($_->{agreement} >= $proposal_threshold)
217 && (1 == scalar @{ $_->{proposals} })
218 && (@{ $_->{proposals} }[0]->is_sales ? abs(@{ $_->{proposals} }[0]->amount - $_->amount) < 0.01
219 : abs(@{ $_->{proposals} }[0]->amount + $_->amount) < 0.01)
220 } @{ $bank_transactions };
222 push @proposals, @otherproposals;
224 # sort bank transaction proposals by quality (score) of proposal
225 if ($::form->{sort_by} && $::form->{sort_by} eq 'proposal') {
226 if ($::form->{sort_dir}) {
227 $bank_transactions = [ sort { $a->{agreement} <=> $b->{agreement} } @{ $bank_transactions } ];
229 $bank_transactions = [ sort { $b->{agreement} <=> $a->{agreement} } @{ $bank_transactions } ];
233 # for testing with t/bank/banktransaction.t :
234 if ( $::form->{dont_render_for_test} ) {
235 return $bank_transactions;
238 $::request->layout->add_javascripts("kivi.BankTransaction.js");
239 $self->render('bank_transactions/list',
240 title => t8('Bank transactions MT940'),
241 BANK_TRANSACTIONS => $bank_transactions,
242 PROPOSALS => \@proposals,
243 bank_account => $bank_account,
244 ui_tab => scalar(@proposals) > 0?1:0,
248 sub action_assign_invoice {
251 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
253 $self->render('bank_transactions/assign_invoice',
255 title => t8('Assign invoice'),);
258 sub action_create_invoice {
260 my %myconfig = %main::myconfig;
262 $self->transaction(SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id}));
264 # This was dead code: We compared vendor.account_name with bank_transaction.iban.
265 # This did never match (Kontonummer != IBAN). It's kivis 09/02 (2013) day
266 # If refactored/improved, also consider that vendor.iban should be normalized
267 # user may like to input strings like: 'AT 3333 3333 2222 1111' -> can be checked strictly
268 # at Vendor code because we need the correct data for all sepa exports.
270 my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(iban => $self->transaction->{remote_account_number});
271 my $use_vendor_filter = $self->transaction->{remote_account_number} && $vendor_of_transaction;
273 my $templates_ap = SL::DB::Manager::RecordTemplate->get_all(
274 where => [ template_type => 'ap_transaction' ],
275 with_objects => [ qw(employee vendor) ],
277 my $templates_gl = SL::DB::Manager::RecordTemplate->get_all(
278 query => [ template_type => 'gl_transaction',
279 chart_id => SL::DB::Manager::BankAccount->find_by(id => $self->transaction->local_bank_account_id)->chart_id,
281 with_objects => [ qw(employee record_template_items) ],
284 # pre filter templates_ap, if we have a vendor match (IBAN eq IBAN) - show and allow user to edit this via gui!
285 $templates_ap = [ grep { $_->vendor_id == $vendor_of_transaction->id } @{ $templates_ap } ] if $use_vendor_filter;
287 $self->callback($self->url_for(
289 'filter.bank_account' => $::form->{filter}->{bank_account},
290 'filter.todate' => $::form->{filter}->{todate},
291 'filter.fromdate' => $::form->{filter}->{fromdate},
295 'bank_transactions/create_invoice',
297 title => t8('Create invoice'),
298 TEMPLATES_GL => $use_vendor_filter && @{ $templates_ap } ? undef : $templates_gl,
299 TEMPLATES_AP => $templates_ap,
300 vendor_name => $use_vendor_filter && @{ $templates_ap } ? $vendor_of_transaction->name : undef,
304 sub action_ajax_payment_suggestion {
307 # based on a BankTransaction ID and a Invoice or PurchaseInvoice ID passed via $::form,
308 # create an HTML blob to be used by the js function add_invoices in templates/webpages/bank_transactions/list.html
309 # and return encoded as JSON
311 my $bt = SL::DB::Manager::BankTransaction->find_by( id => $::form->{bt_id} );
312 my $invoice = SL::DB::Manager::Invoice->find_by( id => $::form->{prop_id} ) || SL::DB::Manager::PurchaseInvoice->find_by( id => $::form->{prop_id} );
314 die unless $bt and $invoice;
316 my @select_options = $invoice->get_payment_select_options_for_bank_transaction($::form->{bt_id});
319 $html = $self->render(
320 'bank_transactions/_payment_suggestion', { output => 0 },
321 bt_id => $::form->{bt_id},
322 prop_id => $::form->{prop_id},
324 SELECT_OPTIONS => \@select_options,
327 $self->render(\ SL::JSON::to_json( { 'html' => "$html" } ), { layout => 0, type => 'json', process => 0 });
330 sub action_filter_templates {
333 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
335 my (@filter, @filter_ap);
337 # filter => gl and ap | filter_ap = ap (i.e. vendorname)
338 push @filter, ('template_name' => { ilike => '%' . $::form->{template} . '%' }) if $::form->{template};
339 push @filter, ('reference' => { ilike => '%' . $::form->{reference} . '%' }) if $::form->{reference};
340 push @filter_ap, ('vendor.name' => { ilike => '%' . $::form->{vendor} . '%' }) if $::form->{vendor};
341 push @filter_ap, @filter;
342 my $templates_gl = SL::DB::Manager::RecordTemplate->get_all(
343 query => [ template_type => 'gl_transaction',
344 chart_id => SL::DB::Manager::BankAccount->find_by(id => $self->transaction->local_bank_account_id)->chart_id,
345 (and => \@filter) x !!@filter
347 with_objects => [ qw(employee record_template_items) ],
350 my $templates_ap = SL::DB::Manager::RecordTemplate->get_all(
351 where => [ template_type => 'ap_transaction', (and => \@filter_ap) x !!@filter_ap ],
352 with_objects => [ qw(employee vendor) ],
354 $::form->{filter} //= {};
356 $self->callback($self->url_for(
358 'filter.bank_account' => $::form->{filter}->{bank_account},
359 'filter.todate' => $::form->{filter}->{todate},
360 'filter.fromdate' => $::form->{filter}->{fromdate},
363 my $output = $self->render(
364 'bank_transactions/_template_list',
366 TEMPLATES_AP => $templates_ap,
367 TEMPLATES_GL => $templates_gl,
370 $self->render(\to_json({ html => $output }), { type => 'json', process => 0 });
373 sub action_ajax_add_list {
376 my @where_sale = (amount => { ne => \'paid' });
377 my @where_purchase = (amount => { ne => \'paid' });
379 if ($::form->{invnumber}) {
380 push @where_sale, (invnumber => { ilike => like($::form->{invnumber})});
381 push @where_purchase, (invnumber => { ilike => like($::form->{invnumber})});
384 if ($::form->{amount}) {
385 push @where_sale, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
386 push @where_purchase, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
389 if ($::form->{vcnumber}) {
390 push @where_sale, ('customer.customernumber' => { ilike => like($::form->{vcnumber})});
391 push @where_purchase, ('vendor.vendornumber' => { ilike => like($::form->{vcnumber})});
394 if ($::form->{vcname}) {
395 push @where_sale, ('customer.name' => { ilike => like($::form->{vcname})});
396 push @where_purchase, ('vendor.name' => { ilike => like($::form->{vcname})});
399 if ($::form->{transdatefrom}) {
400 my $fromdate = $::locale->parse_date_to_object($::form->{transdatefrom});
401 if ( ref($fromdate) eq 'DateTime' ) {
402 push @where_sale, ('transdate' => { ge => $fromdate});
403 push @where_purchase, ('transdate' => { ge => $fromdate});
407 if ($::form->{transdateto}) {
408 my $todate = $::locale->parse_date_to_object($::form->{transdateto});
409 if ( ref($todate) eq 'DateTime' ) {
410 $todate->add(days => 1);
411 push @where_sale, ('transdate' => { lt => $todate});
412 push @where_purchase, ('transdate' => { lt => $todate});
416 my $all_open_ar_invoices = SL::DB::Manager::Invoice ->get_all(where => \@where_sale, with_objects => 'customer');
417 my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => \@where_purchase, with_objects => 'vendor');
419 my @all_open_invoices = @{ $all_open_ar_invoices };
420 # add ap invoices, filtering out subcent open amounts
421 push @all_open_invoices, grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
423 @all_open_invoices = sort { $a->id <=> $b->id } @all_open_invoices;
425 my $output = $self->render(
426 'bank_transactions/add_list',
428 INVOICES => \@all_open_invoices,
431 my %result = ( count => 0, html => $output );
433 $self->render(\to_json(\%result), { type => 'json', process => 0 });
436 sub action_ajax_accept_invoices {
439 my @selected_invoices;
440 foreach my $invoice_id (@{ $::form->{invoice_id} || [] }) {
441 my $invoice_object = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
442 push @selected_invoices, $invoice_object;
446 'bank_transactions/invoices',
448 INVOICES => \@selected_invoices,
449 bt_id => $::form->{bt_id},
456 return 0 if !$::form->{invoice_ids};
458 my %invoice_hash = %{ delete $::form->{invoice_ids} }; # each key (the bt line with a bt_id) contains an array of invoice_ids
460 # e.g. three partial payments with bt_ids 54, 55 and 56 for invoice with id 74:
473 # or if the payment with bt_id 44 is used to pay invoices with ids 50, 51 and 52
475 # '44' => [ '50', '51', 52' ]
478 $::form->{invoice_skontos} ||= {}; # hash of arrays containing the payment types, could be empty
480 # a bank_transaction may be assigned to several invoices, i.e. a customer
481 # might pay several open invoices with one transaction
487 if ( $::form->{proposal_ids} ) {
488 foreach (@{ $::form->{proposal_ids} }) {
489 my $bank_transaction_id = $_;
490 my $invoice_ids = $invoice_hash{$_};
491 push @{ $self->problems }, $self->save_single_bank_transaction(
492 bank_transaction_id => $bank_transaction_id,
493 invoice_ids => $invoice_ids,
494 sources => ($::form->{sources} // {})->{$_},
495 memos => ($::form->{memos} // {})->{$_},
497 $count += scalar( @{$invoice_ids} );
500 while ( my ($bank_transaction_id, $invoice_ids) = each(%invoice_hash) ) {
501 push @{ $self->problems }, $self->save_single_bank_transaction(
502 bank_transaction_id => $bank_transaction_id,
503 invoice_ids => $invoice_ids,
504 sources => [ map { $::form->{"sources_${bank_transaction_id}_${_}"} } @{ $invoice_ids } ],
505 memos => [ map { $::form->{"memos_${bank_transaction_id}_${_}"} } @{ $invoice_ids } ],
507 $count += scalar( @{$invoice_ids} );
510 my $max_count = $count;
511 foreach (@{ $self->problems }) {
512 $count-- if $_->{result} eq 'error';
514 return ($count, $max_count);
517 sub action_save_invoices {
519 my ($success_count, $max_count) = $self->save_invoices();
521 if ($success_count == $max_count) {
522 flash('ok', t8('#1 invoice(s) saved.', $success_count));
524 flash('error', t8('At least #1 invoice(s) not saved', $max_count - $success_count));
527 $self->action_list();
530 sub action_save_proposals {
533 if ( $::form->{proposal_ids} ) {
534 my $propcount = scalar(@{ $::form->{proposal_ids} });
535 if ( $propcount > 0 ) {
536 my $count = $self->save_invoices();
538 flash('ok', t8('#1 proposal(s) with #2 invoice(s) saved.', $propcount, $count));
541 $self->action_list();
545 sub save_single_bank_transaction {
546 my ($self, %params) = @_;
550 bank_transaction => SL::DB::Manager::BankTransaction->find_by(id => $params{bank_transaction_id}),
554 if (!$data{bank_transaction}) {
558 message => $::locale->text('The ID #1 is not a valid database ID.', $data{bank_transaction_id}),
565 my $bt_id = $data{bank_transaction_id};
566 my $bank_transaction = $data{bank_transaction};
567 my $sign = $bank_transaction->amount < 0 ? -1 : 1;
568 my $amount_of_transaction = $sign * $bank_transaction->amount;
569 my $payment_received = $bank_transaction->amount > 0;
570 my $payment_sent = $bank_transaction->amount < 0;
573 foreach my $invoice_id (@{ $params{invoice_ids} }) {
574 my $invoice = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
579 message => $::locale->text("The ID #1 is not a valid database ID.", $invoice_id),
582 push @{ $data{invoices} }, $invoice;
585 if ( $payment_received
586 && any { ( $_->is_sales && ($_->amount < 0))
587 || (!$_->is_sales && ($_->amount > 0))
588 } @{ $data{invoices} }) {
592 message => $::locale->text("Received payments can only be posted for sales invoices and purchase credit notes."),
597 && any { ( $_->is_sales && ($_->amount > 0))
598 || (!$_->is_sales && ($_->amount < 0) && ($_->invoice_type eq 'purchase_invoice'))
599 } @{ $data{invoices} }) {
603 message => $::locale->text("Sent payments can only be posted for purchase invoices and sales credit notes."),
607 my $max_invoices = scalar(@{ $data{invoices} });
610 foreach my $invoice (@{ $data{invoices} }) {
611 my $source = ($data{sources} // [])->[$n_invoices];
612 my $memo = ($data{memos} // [])->[$n_invoices];
616 # Check if bank_transaction already has a link to the invoice, may only be linked once per invoice
617 # This might be caused by the user reloading a page and resending the form
618 if (_existing_record_link($bank_transaction, $invoice)) {
622 message => $::locale->text("Bank transaction with id #1 has already been linked to #2.", $bank_transaction->id, $invoice->displayable_name),
626 if (!$amount_of_transaction && $invoice->open_amount) {
630 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."),
635 if ( defined $::form->{invoice_skontos}->{"$bt_id"} ) {
636 $payment_type = shift(@{ $::form->{invoice_skontos}->{"$bt_id"} });
638 $payment_type = 'without_skonto';
642 # pay invoice or go to the next bank transaction if the amount is not sufficiently high
643 if ($invoice->open_amount <= $amount_of_transaction && $n_invoices < $max_invoices) {
644 my $open_amount = ($payment_type eq 'with_skonto_pt'?$invoice->amount_less_skonto:$invoice->open_amount);
645 # first calculate new bank transaction amount ...
646 if ($invoice->is_sales) {
647 $amount_of_transaction -= $sign * $open_amount;
648 $bank_transaction->invoice_amount($bank_transaction->invoice_amount + $open_amount);
650 $amount_of_transaction += $sign * $open_amount;
651 $bank_transaction->invoice_amount($bank_transaction->invoice_amount - $open_amount);
653 # ... and then pay the invoice
654 $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
655 trans_id => $invoice->id,
656 amount => $open_amount,
657 payment_type => $payment_type,
660 transdate => $bank_transaction->transdate->to_kivitendo);
662 # use the whole amount of the bank transaction for the invoice, overpay the invoice if necessary
664 # this catches credit_notes and negative sales invoices
665 if ( $invoice->is_sales && $invoice->amount < 0 ) {
666 # $invoice->open_amount is negative for credit_notes
667 # $bank_transaction->amount is negative for outgoing transactions
668 # so $amount_of_transaction is negative but needs positive
669 $amount_of_transaction *= -1;
671 } elsif (!$invoice->is_sales && $invoice->invoice_type =~ m/ap_transaction|purchase_invoice/) {
672 # $invoice->open_amount may be negative for ap_transaction but may be positiv for negativ ap_transaction
673 # if $invoice->open_amount is negative $bank_transaction->amount is positve
674 # if $invoice->open_amount is positive $bank_transaction->amount is negative
675 # but amount of transaction is for both positive
676 $amount_of_transaction *= -1 if $invoice->open_amount == - $amount_of_transaction;
679 my $overpaid_amount = $amount_of_transaction - $invoice->open_amount;
680 $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
681 trans_id => $invoice->id,
682 amount => $amount_of_transaction,
683 payment_type => $payment_type,
686 transdate => $bank_transaction->transdate->to_kivitendo);
687 $bank_transaction->invoice_amount($bank_transaction->amount);
688 $amount_of_transaction = 0;
690 if ($overpaid_amount >= 0.01) {
694 message => $::locale->text('Invoice #1 was overpaid by #2.', $invoice->invnumber, $::form->format_amount(\%::myconfig, $overpaid_amount, 2)),
698 # Record a record link from the bank transaction to the invoice
700 from_table => 'bank_transactions',
702 to_table => $invoice->is_sales ? 'ar' : 'ap',
703 to_id => $invoice->id,
706 SL::DB::RecordLink->new(@props)->save;
708 # "close" a sepa_export_item if it exists
709 # code duplicated in action_save_proposals!
710 # currently only works, if there is only exactly one open sepa_export_item
711 if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
712 if ( scalar @$seis == 1 ) {
713 # moved the execution and the check for sepa_export into a method,
714 # this isn't part of a transaction, though
715 $seis->[0]->set_executed if $invoice->id == $seis->[0]->arap_id;
720 $bank_transaction->save;
722 # 'undef' means 'no error' here.
727 my $rez = $data{bank_transaction}->db->with_transaction(sub {
729 $error = $worker->();
740 # Rollback Fehler nicht weiterreichen
744 return grep { $_ } ($error, @warnings);
752 $::auth->assert('bank_transaction');
759 sub make_filter_summary {
762 my $filter = $::form->{filter} || {};
766 [ $filter->{"transdate:date::ge"}, $::locale->text('Transdate') . " " . $::locale->text('From Date') ],
767 [ $filter->{"transdate:date::le"}, $::locale->text('Transdate') . " " . $::locale->text('To Date') ],
768 [ $filter->{"valutadate:date::ge"}, $::locale->text('Valutadate') . " " . $::locale->text('From Date') ],
769 [ $filter->{"valutadate:date::le"}, $::locale->text('Valutadate') . " " . $::locale->text('To Date') ],
770 [ $filter->{"amount:number"}, $::locale->text('Amount') ],
771 [ $filter->{"bank_account_id:integer"}, $::locale->text('Local bank account') ],
775 push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
778 $self->{filter_summary} = join ', ', @filter_strings;
784 my $callback = $self->models->get_callback;
786 my $report = SL::ReportGenerator->new(\%::myconfig, $::form);
787 $self->{report} = $report;
789 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);
790 my @sortable = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount purpose local_account_number local_bank_code);
793 transdate => { sub => sub { $_[0]->transdate_as_date } },
794 valutadate => { sub => sub { $_[0]->valutadate_as_date } },
796 remote_account_number => { },
797 remote_bank_code => { },
798 amount => { sub => sub { $_[0]->amount_as_number },
800 invoice_amount => { sub => sub { $_[0]->invoice_amount_as_number },
802 invoices => { sub => sub { $_[0]->linked_invoices } },
803 currency => { sub => sub { $_[0]->currency->name } },
805 local_account_number => { sub => sub { $_[0]->local_bank_account->account_number } },
806 local_bank_code => { sub => sub { $_[0]->local_bank_account->bank_code } },
807 local_bank_name => { sub => sub { $_[0]->local_bank_account->name } },
811 map { $column_defs{$_}->{text} ||= $::locale->text( $self->models->get_sort_spec->{$_}->{title} ) } keys %column_defs;
813 $report->set_options(
814 std_column_visibility => 1,
815 controller_class => 'BankTransaction',
816 output_format => 'HTML',
817 top_info_text => $::locale->text('Bank transactions'),
818 title => $::locale->text('Bank transactions'),
819 allow_pdf_export => 1,
820 allow_csv_export => 1,
822 $report->set_columns(%column_defs);
823 $report->set_column_order(@columns);
824 $report->set_export_options(qw(list_all filter));
825 $report->set_options_from_form;
826 $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
827 $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
829 my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted();
831 $report->set_options(
832 raw_top_info_text => $self->render('bank_transactions/report_top', { output => 0 }, BANK_ACCOUNTS => $bank_accounts),
833 raw_bottom_info_text => $self->render('bank_transactions/report_bottom', { output => 0 }),
837 sub _existing_record_link {
838 my ($bt, $invoice) = @_;
840 # check whether a record link from banktransaction $bt already exists to
841 # invoice $invoice, returns 1 if that is the case
843 die unless $bt->isa("SL::DB::BankTransaction") && ( $invoice->isa("SL::DB::Invoice") || $invoice->isa("SL::DB::PurchaseInvoice") );
845 my $linked_record_to_table = $invoice->is_sales ? 'Invoice' : 'PurchaseInvoice';
846 my $linked_records = $bt->linked_records( direction => 'to', to => $linked_record_to_table, query => [ id => $invoice->id ] );
848 return @$linked_records ? 1 : 0;
851 sub init_problems { [] }
856 SL::Controller::Helper::GetModels->new(
861 dir => 0, # 1 = ASC, 0 = DESC : default sort is newest at top
863 transdate => t8('Transdate'),
864 remote_name => t8('Remote name'),
865 amount => t8('Amount'),
866 invoice_amount => t8('Assigned'),
867 invoices => t8('Linked invoices'),
868 valutadate => t8('Valutadate'),
869 remote_account_number => t8('Remote account number'),
870 remote_bank_code => t8('Remote bank code'),
871 currency => t8('Currency'),
872 purpose => t8('Purpose'),
873 local_account_number => t8('Local account number'),
874 local_bank_code => t8('Local bank code'),
875 local_bank_name => t8('Bank account'),
877 with_objects => [ 'local_bank_account', 'currency' ],
881 sub load_ap_record_template_url {
882 my ($self, $template) = @_;
884 return $self->url_for(
885 controller => 'ap.pl',
886 action => 'load_record_template',
888 'form_defaults.amount_1' => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
889 'form_defaults.transdate' => $self->transaction->transdate_as_date,
890 'form_defaults.duedate' => $self->transaction->transdate_as_date,
891 'form_defaults.no_payment_bookings' => 1,
892 'form_defaults.paid_1_suggestion' => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
893 'form_defaults.AP_paid_1_suggestion' => $self->transaction->local_bank_account->chart->accno,
894 'form_defaults.callback' => $self->callback,
898 sub load_gl_record_template_url {
899 my ($self, $template) = @_;
901 return $self->url_for(
902 controller => 'gl.pl',
903 action => 'load_record_template',
905 'form_defaults.amount_1' => abs($self->transaction->amount), # always positive
906 'form_defaults.transdate' => $self->transaction->transdate_as_date,
907 'form_defaults.callback' => $self->callback,
911 sub setup_search_action_bar {
912 my ($self, %params) = @_;
914 for my $bar ($::request->layout->get('actionbar')) {
918 submit => [ '#search_form', { action => 'BankTransaction/list' } ],
919 accesskey => 'enter',
925 sub setup_list_all_action_bar {
926 my ($self, %params) = @_;
928 for my $bar ($::request->layout->get('actionbar')) {
932 submit => [ '#filter_form', { action => 'BankTransaction/list_all' } ],
933 accesskey => 'enter',
948 SL::Controller::BankTransaction - Posting payments to invoices from
949 bank transactions imported earlier
955 =item C<save_single_bank_transaction %params>
957 Takes a bank transaction ID (as parameter C<bank_transaction_id> and
958 tries to post its amount to a certain number of invoices (parameter
959 C<invoice_ids>, an array ref of database IDs to purchase or sales
962 The whole function is wrapped in a database transaction. If an
963 exception occurs the bank transaction is not posted at all. The same
964 is true if the code detects an error during the execution, e.g. a bank
965 transaction that's already been posted earlier. In both cases the
966 database transaction will be rolled back.
968 If warnings but not errors occur the database transaction is still
971 The return value is an error object or C<undef> if the function
972 succeeded. The calling function will collect all warnings and errors
973 and display them in a nicely formatted table if any occurred.
975 An error object is a hash reference containing the following members:
979 =item * C<result> — can be either C<warning> or C<error>. Warnings are
980 displayed slightly different than errors.
982 =item * C<message> — a human-readable message included in the list of
983 errors meant as the description of why the problem happened
985 =item * C<bank_transaction_id>, C<invoice_ids> — the same parameters
986 that the function was called with
988 =item * C<bank_transaction> — the database object
989 (C<SL::DB::BankTransaction>) corresponding to C<bank_transaction_id>
991 =item * C<invoices> — an array ref of the database objects (either
992 C<SL::DB::Invoice> or C<SL::DB::PurchaseInvoice>) corresponding to
1001 Niclas Zimmermann E<lt>niclas@kivitendo-premium.deE<gt>,
1002 Geoffrey Richardson E<lt>information@richardson-bueren.deE<gt>