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;
20 use SL::DB::ReconciliationLink;
23 use SL::DB::AccTransaction;
24 use SL::DB::BankTransactionAccTrans;
26 use SL::DB::BankAccount;
27 use SL::DB::GLTransaction;
28 use SL::DB::RecordTemplate;
29 use SL::DB::SepaExportItem;
30 use SL::DBUtils qw(like do_query);
32 use SL::Presenter::Tag qw(checkbox_tag html_tag);
34 use List::UtilsBy qw(partition_by);
35 use List::MoreUtils qw(any);
36 use List::Util qw(max);
38 use Rose::Object::MakeMethods::Generic
40 scalar => [ qw(callback transaction) ],
41 'scalar --get_set_init' => [ qw(models problems) ],
44 __PACKAGE__->run_before('check_auth');
54 my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted( query => [ obsolete => 0 ] );
56 $self->setup_search_action_bar;
57 $self->render('bank_transactions/search',
58 BANK_ACCOUNTS => $bank_accounts);
64 $self->make_filter_summary;
65 $self->prepare_report;
67 $self->setup_list_all_action_bar;
68 $self->report_generator_list_objects(report => $self->{report}, objects => $self->models->get);
74 if (!$::form->{filter}{bank_account}) {
75 flash('error', t8('No bank account chosen!'));
80 my $sort_by = $::form->{sort_by} || 'transdate';
81 $sort_by = 'transdate' if $sort_by eq 'proposal';
82 $sort_by .= $::form->{sort_dir} ? ' DESC' : ' ASC';
84 my $fromdate = $::locale->parse_date_to_object($::form->{filter}->{fromdate});
85 my $todate = $::locale->parse_date_to_object($::form->{filter}->{todate});
86 $todate->add( days => 1 ) if $todate;
89 push @where, (transdate => { ge => $fromdate }) if ($fromdate);
90 push @where, (transdate => { lt => $todate }) if ($todate);
91 my $bank_account = SL::DB::Manager::BankAccount->find_by( id => $::form->{filter}{bank_account} );
92 # bank_transactions no younger than starting date,
93 # including starting date (same search behaviour as fromdate)
94 # but OPEN invoices to be matched may be from before
95 if ( $bank_account->reconciliation_starting_date ) {
96 push @where, (transdate => { ge => $bank_account->reconciliation_starting_date });
99 my $bank_transactions = SL::DB::Manager::BankTransaction->get_all(
100 with_objects => [ 'local_bank_account', 'currency' ],
104 amount => {ne => \'invoice_amount'},
105 local_bank_account_id => $::form->{filter}{bank_account},
110 # credit notes have a negative amount, treat differently
111 my $all_open_ar_invoices = SL::DB::Manager::Invoice ->get_all(where => [ or => [ amount => { gt => \'paid' },
112 and => [ type => 'credit_note',
113 amount => { lt => \'paid' }
117 with_objects => ['customer','payment_terms']);
119 my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => [amount => { ne => \'paid' }], with_objects => ['vendor' ,'payment_terms']);
120 my $all_open_sepa_export_items = SL::DB::Manager::SepaExportItem->get_all(where => [chart_id => $bank_account->chart_id ,
121 'sepa_export.executed' => 0, 'sepa_export.closed' => 0 ], with_objects => ['sepa_export']);
123 my @all_open_invoices;
124 # filter out invoices with less than 1 cent outstanding
125 push @all_open_invoices, map { $_->{is_ar}=1 ; $_ } grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ar_invoices };
126 push @all_open_invoices, map { $_->{is_ar}=0 ; $_ } grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
129 my %sepa_export_items_by_id = partition_by { $_->ar_id || $_->ap_id } @$all_open_sepa_export_items;
131 # first collect sepa export items to open invoices
132 foreach my $open_invoice (@all_open_invoices){
133 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount,2);
134 $open_invoice->{skonto_type} = 'without_skonto';
135 foreach (@{ $sepa_export_items_by_id{ $open_invoice->id } || [] }) {
136 my $factor = ($_->ar_id == $open_invoice->id ? 1 : -1);
137 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount*$factor,2);
139 $open_invoice->{skonto_type} = $_->payment_type;
140 $sepa_exports{$_->sepa_export_id} ||= { count => 0, is_ar => 0, amount => 0, proposed => 0, invoices => [], item => $_ };
141 $sepa_exports{$_->sepa_export_id}->{count}++;
142 $sepa_exports{$_->sepa_export_id}->{is_ar}++ if $_->ar_id == $open_invoice->id;
143 $sepa_exports{$_->sepa_export_id}->{amount} += $_->amount * $factor;
144 push @{ $sepa_exports{$_->sepa_export_id}->{invoices} }, $open_invoice;
148 # try to match each bank_transaction with each of the possible open invoices
152 foreach my $bt (@{ $bank_transactions }) {
153 ## 5 Stellen hinter dem Komma auf 2 Stellen reduzieren
154 $bt->amount($bt->amount*1);
155 $bt->invoice_amount($bt->invoice_amount*1);
157 $bt->{proposals} = [];
158 $bt->{rule_matches} = [];
160 $bt->{remote_name} .= $bt->{remote_name_1} if $bt->{remote_name_1};
162 if ( $bt->is_batch_transaction ) {
164 foreach ( keys %sepa_exports) {
165 if ( abs(($sepa_exports{$_}->{amount} * 1) - ($bt->amount * 1)) < 0.01 ) {
167 @{$bt->{proposals}} = @{$sepa_exports{$_}->{invoices}};
168 $bt->{sepa_export_ok} = 1;
169 $sepa_exports{$_}->{proposed}=1;
170 push(@proposals, $bt);
176 # batch transaction has no remotename !!
178 next unless $bt->{remote_name}; # bank has no name, usually fees, use create invoice to assign
181 # try to match the current $bt to each of the open_invoices, saving the
182 # results of get_agreement_with_invoice in $open_invoice->{agreement} and
183 # $open_invoice->{rule_matches}.
185 # The values are overwritten each time a new bt is checked, so at the end
186 # of each bt the likely results are filtered and those values are stored in
187 # the arrays $bt->{proposals} and $bt->{rule_matches}, and the agreement
188 # score is stored in $bt->{agreement}
190 foreach my $open_invoice (@all_open_invoices) {
191 ($open_invoice->{agreement}, $open_invoice->{rule_matches}) = $bt->get_agreement_with_invoice($open_invoice,
192 sepa_export_items => $all_open_sepa_export_items,
194 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,
195 $open_invoice->amount * ($open_invoice->{is_ar} ? 1 : -1), 2);
199 my $min_agreement = 3; # suggestions must have at least this score
201 my $max_agreement = max map { $_->{agreement} } @all_open_invoices;
203 # add open_invoices with highest agreement into array $bt->{proposals}
204 if ( $max_agreement >= $min_agreement ) {
205 $bt->{proposals} = [ grep { $_->{agreement} == $max_agreement } @all_open_invoices ];
206 $bt->{agreement} = $max_agreement; #scalar @{ $bt->{proposals} } ? $agreement + 1 : '';
208 # store the rule_matches in a separate array, so they can be displayed in template
209 foreach ( @{ $bt->{proposals} } ) {
210 push(@{$bt->{rule_matches}}, $_->{rule_matches});
216 # separate filter for proposals (second tab, agreement >= 5 and exactly one match)
217 # to qualify as a proposal there has to be
218 # * agreement >= 5 TODO: make threshold configurable in configuration
219 # * there must be only one exact match
220 # * depending on whether sales or purchase the amount has to have the correct sign (so Gutschriften don't work?)
221 my $proposal_threshold = 5;
222 my @otherproposals = grep {
223 ($_->{agreement} >= $proposal_threshold)
224 && (1 == scalar @{ $_->{proposals} })
225 && (@{ $_->{proposals} }[0]->is_sales ? abs(@{ $_->{proposals} }[0]->amount - $_->amount) < 0.01
226 : abs(@{ $_->{proposals} }[0]->amount + $_->amount) < 0.01)
227 } @{ $bank_transactions };
229 push @proposals, @otherproposals;
231 # sort bank transaction proposals by quality (score) of proposal
232 if ($::form->{sort_by} && $::form->{sort_by} eq 'proposal') {
233 if ($::form->{sort_dir}) {
234 $bank_transactions = [ sort { $a->{agreement} <=> $b->{agreement} } @{ $bank_transactions } ];
236 $bank_transactions = [ sort { $b->{agreement} <=> $a->{agreement} } @{ $bank_transactions } ];
240 # for testing with t/bank/banktransaction.t :
241 if ( $::form->{dont_render_for_test} ) {
242 return ( $bank_transactions , \@proposals );
245 $::request->layout->add_javascripts("kivi.BankTransaction.js");
246 $self->render('bank_transactions/list',
247 title => t8('Bank transactions MT940'),
248 BANK_TRANSACTIONS => $bank_transactions,
249 PROPOSALS => \@proposals,
250 bank_account => $bank_account,
251 ui_tab => scalar(@proposals) > 0?1:0,
255 sub action_assign_invoice {
258 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
260 $self->render('bank_transactions/assign_invoice',
262 title => t8('Assign invoice'),);
265 sub action_create_invoice {
267 my %myconfig = %main::myconfig;
269 $self->transaction(SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id}));
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}),
563 my $bank_transaction = $data{bank_transaction};
565 if ($bank_transaction->closed_period) {
569 message => $::locale->text('Cannot post payment for a closed period!'),
575 my $bt_id = $data{bank_transaction_id};
576 my $sign = $bank_transaction->amount < 0 ? -1 : 1;
577 my $not_assigned_amount = $bank_transaction->not_assigned_amount;
578 my $payment_received = $bank_transaction->amount > 0;
579 my $payment_sent = $bank_transaction->amount < 0;
582 foreach my $invoice_id (@{ $params{invoice_ids} }) {
583 my $invoice = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
588 message => $::locale->text("The ID #1 is not a valid database ID.", $invoice_id),
591 push @{ $data{invoices} }, $invoice;
594 if ( $payment_received
595 && any { ( $_->is_sales && ($_->amount < 0))
596 || (!$_->is_sales && ($_->amount > 0))
597 } @{ $data{invoices} }) {
601 message => $::locale->text("Received payments can only be posted for sales invoices and purchase credit notes."),
606 && any { ( $_->is_sales && ($_->amount > 0))
607 || (!$_->is_sales && ($_->amount < 0) && ($_->invoice_type eq 'purchase_invoice'))
608 } @{ $data{invoices} }) {
612 message => $::locale->text("Sent payments can only be posted for purchase invoices and sales credit notes."),
616 my $max_invoices = scalar(@{ $data{invoices} });
619 foreach my $invoice (@{ $data{invoices} }) {
620 my $source = ($data{sources} // [])->[$n_invoices];
621 my $memo = ($data{memos} // [])->[$n_invoices];
626 if (!$not_assigned_amount && $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';
641 # TODO rewrite this: really booked amount should be a return value of Payment.pm
642 # also this controller shouldnt care about how to calc skonto. we simply delegate the
643 # payment_type to the helper and get the corresponding bank_transaction values back
644 # hotfix to get the signs right - compare absolute values and later set the signs
645 # should be better done elsewhere - changing not_assigned_amount to abs feels seriously bogus
647 my $open_amount = $payment_type eq 'with_skonto_pt' ? $invoice->amount_less_skonto : $invoice->open_amount;
648 $open_amount = abs($open_amount);
649 $not_assigned_amount = abs($not_assigned_amount);
650 my $amount_for_booking = ($open_amount < $not_assigned_amount) ? $open_amount : $not_assigned_amount;
651 my $amount_for_payment = $amount_for_booking;
653 # get the right direction for the payment bookings (all amounts < 0 are stornos, credit notes or negative ap)
654 $amount_for_payment *= -1 if $invoice->amount < 0;
655 # get the right direction for the bank transaction
656 $amount_for_booking *= $sign;
658 $bank_transaction->invoice_amount($bank_transaction->invoice_amount + $amount_for_booking);
660 # ... and then pay the invoice
661 my @acc_ids = $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
662 trans_id => $invoice->id,
663 amount => $amount_for_payment,
664 payment_type => $payment_type,
667 transdate => $bank_transaction->valutadate->to_kivitendo);
668 # ... and record the origin via BankTransactionAccTrans
669 if (scalar(@acc_ids) < 2) {
673 message => $::locale->text("Unable to book transactions for bank purpose #1", $bank_transaction->purpose),
676 foreach my $acc_trans_id (@acc_ids) {
677 my $id_type = $invoice->is_sales ? 'ar' : 'ap';
679 acc_trans_id => $acc_trans_id,
680 bank_transaction_id => $bank_transaction->id,
681 $id_type => $invoice->id,
683 SL::DB::BankTransactionAccTrans->new(%props_acc)->save;
685 # Record a record link from the bank transaction to the invoice
687 from_table => 'bank_transactions',
689 to_table => $invoice->is_sales ? 'ar' : 'ap',
690 to_id => $invoice->id,
692 SL::DB::RecordLink->new(%props)->save;
694 # "close" a sepa_export_item if it exists
695 # code duplicated in action_save_proposals!
696 # currently only works, if there is only exactly one open sepa_export_item
697 if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
698 if ( scalar @$seis == 1 ) {
699 # moved the execution and the check for sepa_export into a method,
700 # this isn't part of a transaction, though
701 $seis->[0]->set_executed if $invoice->id == $seis->[0]->arap_id;
706 $bank_transaction->save;
708 # 'undef' means 'no error' here.
713 my $rez = $data{bank_transaction}->db->with_transaction(sub {
715 $error = $worker->();
726 # Rollback Fehler nicht weiterreichen
728 # aber einen rollback von hand
729 $::lxdebug->message(LXDebug->DEBUG2(),"finish worker with ". ($error ? $error->{result} : '-'));
730 $data{bank_transaction}->db->dbh->rollback if $error && $error->{result} eq 'error';
733 return grep { $_ } ($error, @warnings);
735 sub action_unlink_bank_transaction {
736 my ($self, %params) = @_;
738 croak("No bank transaction ids") unless scalar @{ $::form->{ids}} > 0;
742 foreach my $bt_id (@{ $::form->{ids}} ) {
744 my $bank_transaction = SL::DB::Manager::BankTransaction->find_by(id => $bt_id);
745 croak("No valid bank transaction found") unless (ref($bank_transaction) eq 'SL::DB::BankTransaction');
746 croak t8('Cannot unlink payment for a closed period!') if $bank_transaction->closed_period;
748 # everything in one transaction
749 my $rez = $bank_transaction->db->with_transaction(sub {
750 # 1. remove all reconciliations (due to underlying trigger, this has to be the first step)
751 my $rec_links = SL::DB::Manager::ReconciliationLink->get_all(where => [ bank_transaction_id => $bt_id ]);
752 $_->delete for @{ $rec_links };
755 foreach my $acc_trans_id_entry (@{ SL::DB::Manager::BankTransactionAccTrans->get_all(where => [bank_transaction_id => $bt_id ] )}) {
757 my $acc_trans = SL::DB::Manager::AccTransaction->get_all(where => [acc_trans_id => $acc_trans_id_entry->acc_trans_id]);
759 # save trans_id and type
760 die "no type" unless ($acc_trans_id_entry->ar_id || $acc_trans_id_entry->ap_id || $acc_trans_id_entry->gl_id);
761 $trans_ids{$acc_trans_id_entry->ar_id} = 'ar' if $acc_trans_id_entry->ar_id;
762 $trans_ids{$acc_trans_id_entry->ap_id} = 'ap' if $acc_trans_id_entry->ap_id;
763 $trans_ids{$acc_trans_id_entry->gl_id} = 'gl' if $acc_trans_id_entry->gl_id;
764 # 2. all good -> ready to delete acc_trans and bt_acc link
765 $acc_trans_id_entry->delete;
766 $_->delete for @{ $acc_trans };
768 # 3. update arap.paid (may not be 0, yet)
769 # or in case of gl, delete whole entry
770 while (my ($trans_id, $type) = each %trans_ids) {
772 SL::DB::Manager::GLTransaction->delete_all(where => [ id => $trans_id ]);
775 die ("invalid type") unless $type =~ m/^(ar|ap)$/;
777 # recalc and set paid via database query
778 my $query = qq|UPDATE $type SET paid =
779 (SELECT COALESCE(abs(sum(amount)),0) FROM acc_trans
781 AND chart_link ilike '%paid%')|;
783 die if (do_query($::form, $bank_transaction->db->dbh, $query, $trans_id) == -1);
785 # 4. and delete all (if any) record links
786 my $rl = SL::DB::Manager::RecordLink->delete_all(where => [ from_id => $bt_id, from_table => 'bank_transactions' ]);
788 # 5. finally reset this bank transaction
789 $bank_transaction->invoice_amount(0);
790 $bank_transaction->cleared(0);
791 $bank_transaction->save;
795 }) || die t8('error while unlinking payment #1 : ', $bank_transaction->purpose) . $bank_transaction->db->error . "\n";
800 flash('ok', t8('#1 bank transaction bookings undone.', $success_count));
801 $self->action_list_all() unless $params{testcase};
808 $::auth->assert('bank_transaction');
815 sub make_filter_summary {
818 my $filter = $::form->{filter} || {};
822 [ $filter->{"transdate:date::ge"}, $::locale->text('Transdate') . " " . $::locale->text('From Date') ],
823 [ $filter->{"transdate:date::le"}, $::locale->text('Transdate') . " " . $::locale->text('To Date') ],
824 [ $filter->{"valutadate:date::ge"}, $::locale->text('Valutadate') . " " . $::locale->text('From Date') ],
825 [ $filter->{"valutadate:date::le"}, $::locale->text('Valutadate') . " " . $::locale->text('To Date') ],
826 [ $filter->{"amount:number"}, $::locale->text('Amount') ],
827 [ $filter->{"bank_account_id:integer"}, $::locale->text('Local bank account') ],
831 push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
834 $self->{filter_summary} = join ', ', @filter_strings;
840 my $callback = $self->models->get_callback;
842 my $report = SL::ReportGenerator->new(\%::myconfig, $::form);
843 $self->{report} = $report;
845 my @columns = qw(ids 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);
846 my @sortable = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount purpose local_account_number local_bank_code);
849 ids => { raw_header_data => checkbox_tag("", id => "check_all", checkall => "[data-checkall=1]"),
851 raw_data => sub { if (@{ $_[0]->linked_invoices }) {
852 if ($_[0]->closed_period) {
853 html_tag('text', "X"); #, tooltip => t8('Bank Transaction is in a closed period.')),
855 checkbox_tag("ids[]", value => $_[0]->id, "data-checkall" => 1);
858 transdate => { sub => sub { $_[0]->transdate_as_date } },
859 valutadate => { sub => sub { $_[0]->valutadate_as_date } },
861 remote_account_number => { },
862 remote_bank_code => { },
863 amount => { sub => sub { $_[0]->amount_as_number },
865 invoice_amount => { sub => sub { $_[0]->invoice_amount_as_number },
867 invoices => { sub => sub { my @invnumbers; for my $obj (@{ $_[0]->linked_invoices }) {
868 next unless $obj; push @invnumbers, $obj->invnumber } return \@invnumbers } },
869 currency => { sub => sub { $_[0]->currency->name } },
871 local_account_number => { sub => sub { $_[0]->local_bank_account->account_number } },
872 local_bank_code => { sub => sub { $_[0]->local_bank_account->bank_code } },
873 local_bank_name => { sub => sub { $_[0]->local_bank_account->name } },
877 map { $column_defs{$_}->{text} ||= $::locale->text( $self->models->get_sort_spec->{$_}->{title} ) } keys %column_defs;
879 $report->set_options(
880 std_column_visibility => 1,
881 controller_class => 'BankTransaction',
882 output_format => 'HTML',
883 top_info_text => $::locale->text('Bank transactions'),
884 title => $::locale->text('Bank transactions'),
885 allow_pdf_export => 1,
886 allow_csv_export => 1,
888 $report->set_columns(%column_defs);
889 $report->set_column_order(@columns);
890 $report->set_export_options(qw(list_all filter));
891 $report->set_options_from_form;
892 $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
893 $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
895 my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted();
897 $report->set_options(
898 raw_top_info_text => $self->render('bank_transactions/report_top', { output => 0 }, BANK_ACCOUNTS => $bank_accounts),
899 raw_bottom_info_text => $self->render('bank_transactions/report_bottom', { output => 0 }),
903 sub init_problems { [] }
908 SL::Controller::Helper::GetModels->new(
913 dir => 0, # 1 = ASC, 0 = DESC : default sort is newest at top
916 transdate => t8('Transdate'),
917 remote_name => t8('Remote name'),
918 amount => t8('Amount'),
919 invoice_amount => t8('Assigned'),
920 invoices => t8('Linked invoices'),
921 valutadate => t8('Valutadate'),
922 remote_account_number => t8('Remote account number'),
923 remote_bank_code => t8('Remote bank code'),
924 currency => t8('Currency'),
925 purpose => t8('Purpose'),
926 local_account_number => t8('Local account number'),
927 local_bank_code => t8('Local bank code'),
928 local_bank_name => t8('Bank account'),
930 with_objects => [ 'local_bank_account', 'currency' ],
934 sub load_ap_record_template_url {
935 my ($self, $template) = @_;
937 return $self->url_for(
938 controller => 'ap.pl',
939 action => 'load_record_template',
941 'form_defaults.amount_1' => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
942 'form_defaults.transdate' => $self->transaction->transdate_as_date,
943 'form_defaults.duedate' => $self->transaction->transdate_as_date,
944 'form_defaults.no_payment_bookings' => 1,
945 'form_defaults.paid_1_suggestion' => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
946 'form_defaults.AP_paid_1_suggestion' => $self->transaction->local_bank_account->chart->accno,
947 'form_defaults.callback' => $self->callback,
951 sub load_gl_record_template_url {
952 my ($self, $template) = @_;
954 return $self->url_for(
955 controller => 'gl.pl',
956 action => 'load_record_template',
958 'form_defaults.amount_1' => abs($self->transaction->not_assigned_amount), # always positive
959 'form_defaults.transdate' => $self->transaction->transdate_as_date,
960 'form_defaults.callback' => $self->callback,
961 'form_defaults.bt_id' => $self->transaction->id,
962 'form_defaults.bt_chart_id' => $self->transaction->local_bank_account->chart->id,
966 sub setup_search_action_bar {
967 my ($self, %params) = @_;
969 for my $bar ($::request->layout->get('actionbar')) {
973 submit => [ '#search_form', { action => 'BankTransaction/list' } ],
974 accesskey => 'enter',
980 sub setup_list_all_action_bar {
981 my ($self, %params) = @_;
983 for my $bar ($::request->layout->get('actionbar')) {
986 action => [ t8('Actions') ],
988 t8('Unlink bank transactions'),
989 submit => [ '#form', { action => 'BankTransaction/unlink_bank_transaction' } ],
990 checks => [ [ 'kivi.check_if_entries_selected', '[name="ids[]"]' ] ],
991 disabled => $::instance_conf->get_payments_changeable ? t8('Cannot safely unlink bank transactions, please set the posting configuration for payments to unchangeable.') : undef,
996 submit => [ '#filter_form', { action => 'BankTransaction/list_all' } ],
997 accesskey => 'enter',
1012 SL::Controller::BankTransaction - Posting payments to invoices from
1013 bank transactions imported earlier
1019 =item C<save_single_bank_transaction %params>
1021 Takes a bank transaction ID (as parameter C<bank_transaction_id> and
1022 tries to post its amount to a certain number of invoices (parameter
1023 C<invoice_ids>, an array ref of database IDs to purchase or sales
1026 This method handles already partly assigned bank transactions.
1028 This method cannot handle already partly assigned bank transactions, i.e.
1029 a bank transaction that has a invoice_amount <> 0 but not the fully
1030 transaction amount (invoice_amount == amount).
1032 If the amount of the bank transaction is higher than the sum of
1033 the assigned invoices (1 .. n) the bank transaction will only be
1036 The whole function is wrapped in a database transaction. If an
1037 exception occurs the bank transaction is not posted at all. The same
1038 is true if the code detects an error during the execution, e.g. a bank
1039 transaction that's already been posted earlier. In both cases the
1040 database transaction will be rolled back.
1042 If warnings but not errors occur the database transaction is still
1045 The return value is an error object or C<undef> if the function
1046 succeeded. The calling function will collect all warnings and errors
1047 and display them in a nicely formatted table if any occurred.
1049 An error object is a hash reference containing the following members:
1053 =item * C<result> — can be either C<warning> or C<error>. Warnings are
1054 displayed slightly different than errors.
1056 =item * C<message> — a human-readable message included in the list of
1057 errors meant as the description of why the problem happened
1059 =item * C<bank_transaction_id>, C<invoice_ids> — the same parameters
1060 that the function was called with
1062 =item * C<bank_transaction> — the database object
1063 (C<SL::DB::BankTransaction>) corresponding to C<bank_transaction_id>
1065 =item * C<invoices> — an array ref of the database objects (either
1066 C<SL::DB::Invoice> or C<SL::DB::PurchaseInvoice>) corresponding to
1071 =item C<action_unlink_bank_transaction>
1073 Takes one or more bank transaction ID (as parameter C<form::ids>) and
1074 tries to revert all payment bookings including already cleared bookings.
1076 This method won't undo payments that are in a closed period and assumes
1077 that payments are not manually changed, i.e. only imported payments.
1079 GL-records will be deleted completely if a bank transaction was the source.
1081 TODO: we still rely on linked_records for the check boxes
1087 Niclas Zimmermann E<lt>niclas@kivitendo-premium.deE<gt>,
1088 Geoffrey Richardson E<lt>information@richardson-bueren.deE<gt>