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);
71 sub gather_bank_transactions_and_proposals {
72 my ($self, %params) = @_;
74 my $sort_by = $params{sort_by} || 'transdate';
75 $sort_by = 'transdate' if $sort_by eq 'proposal';
76 $sort_by .= $params{sort_dir} ? ' DESC' : ' ASC';
79 push @where, (transdate => { ge => $params{fromdate} }) if $params{fromdate};
80 push @where, (transdate => { lt => $params{todate} }) if $params{todate};
81 # bank_transactions no younger than starting date,
82 # including starting date (same search behaviour as fromdate)
83 # but OPEN invoices to be matched may be from before
84 if ( $params{bank_account}->reconciliation_starting_date ) {
85 push @where, (transdate => { ge => $params{bank_account}->reconciliation_starting_date });
88 my $bank_transactions = SL::DB::Manager::BankTransaction->get_all(
89 with_objects => [ 'local_bank_account', 'currency' ],
93 amount => {ne => \'invoice_amount'}, # '} make emacs happy
94 local_bank_account_id => $params{bank_account}->id,
99 # credit notes have a negative amount, treat differently
100 my $all_open_ar_invoices = SL::DB::Manager::Invoice->get_all(where => [ or => [ amount => { gt => \'paid' }, # '} make emacs happy
101 and => [ type => 'credit_note',
102 amount => { lt => \'paid' } # '} make emacs happy
106 with_objects => ['customer','payment_terms']);
108 my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => [amount => { ne => \'paid' }], # '}] make emacs happy
109 with_objects => ['vendor' ,'payment_terms']);
110 my $all_open_sepa_export_items = SL::DB::Manager::SepaExportItem->get_all(where => [chart_id => $params{bank_account}->chart_id ,
111 'sepa_export.executed' => 0,
112 'sepa_export.closed' => 0
114 with_objects => ['sepa_export']);
116 my @all_open_invoices;
117 # filter out invoices with less than 1 cent outstanding
118 push @all_open_invoices, map { $_->{is_ar}=1 ; $_ } grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ar_invoices };
119 push @all_open_invoices, map { $_->{is_ar}=0 ; $_ } grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
122 my %sepa_export_items_by_id = partition_by { $_->ar_id || $_->ap_id } @$all_open_sepa_export_items;
124 # first collect sepa export items to open invoices
125 foreach my $open_invoice (@all_open_invoices){
126 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount,2);
127 $open_invoice->{skonto_type} = 'without_skonto';
128 foreach (@{ $sepa_export_items_by_id{ $open_invoice->id } || [] }) {
129 my $factor = ($_->ar_id == $open_invoice->id ? 1 : -1);
130 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount*$factor,2);
132 $open_invoice->{skonto_type} = $_->payment_type;
133 $sepa_exports{$_->sepa_export_id} ||= { count => 0, is_ar => 0, amount => 0, proposed => 0, invoices => [], item => $_ };
134 $sepa_exports{$_->sepa_export_id}->{count}++;
135 $sepa_exports{$_->sepa_export_id}->{is_ar}++ if $_->ar_id == $open_invoice->id;
136 $sepa_exports{$_->sepa_export_id}->{amount} += $_->amount * $factor;
137 push @{ $sepa_exports{$_->sepa_export_id}->{invoices} }, $open_invoice;
141 # try to match each bank_transaction with each of the possible open invoices
145 foreach my $bt (@{ $bank_transactions }) {
146 ## 5 Stellen hinter dem Komma auf 2 Stellen reduzieren
147 $bt->amount($bt->amount*1);
148 $bt->invoice_amount($bt->invoice_amount*1);
150 $bt->{proposals} = [];
151 $bt->{rule_matches} = [];
153 $bt->{remote_name} .= $bt->{remote_name_1} if $bt->{remote_name_1};
155 if ( $bt->is_batch_transaction ) {
157 foreach ( keys %sepa_exports) {
158 if ( abs(($sepa_exports{$_}->{amount} * 1) - ($bt->amount * 1)) < 0.01 ) {
160 @{$bt->{proposals}} = @{$sepa_exports{$_}->{invoices}};
161 $bt->{sepa_export_ok} = 1;
162 $sepa_exports{$_}->{proposed}=1;
163 push(@proposals, $bt);
169 # batch transaction has no remotename !!
172 # try to match the current $bt to each of the open_invoices, saving the
173 # results of get_agreement_with_invoice in $open_invoice->{agreement} and
174 # $open_invoice->{rule_matches}.
176 # The values are overwritten each time a new bt is checked, so at the end
177 # of each bt the likely results are filtered and those values are stored in
178 # the arrays $bt->{proposals} and $bt->{rule_matches}, and the agreement
179 # score is stored in $bt->{agreement}
181 foreach my $open_invoice (@all_open_invoices) {
182 ($open_invoice->{agreement}, $open_invoice->{rule_matches}) = $bt->get_agreement_with_invoice($open_invoice,
183 sepa_export_items => $all_open_sepa_export_items,
185 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,
186 $open_invoice->amount * ($open_invoice->{is_ar} ? 1 : -1), 2);
190 my $min_agreement = 3; # suggestions must have at least this score
192 my $max_agreement = max map { $_->{agreement} } @all_open_invoices;
194 # add open_invoices with highest agreement into array $bt->{proposals}
195 if ( $max_agreement >= $min_agreement ) {
196 $bt->{proposals} = [ grep { $_->{agreement} == $max_agreement } @all_open_invoices ];
197 $bt->{agreement} = $max_agreement; #scalar @{ $bt->{proposals} } ? $agreement + 1 : '';
199 # store the rule_matches in a separate array, so they can be displayed in template
200 foreach ( @{ $bt->{proposals} } ) {
201 push(@{$bt->{rule_matches}}, $_->{rule_matches});
207 # separate filter for proposals (second tab, agreement >= 5 and exactly one match)
208 # to qualify as a proposal there has to be
209 # * agreement >= 5 TODO: make threshold configurable in configuration
210 # * there must be only one exact match
211 my $proposal_threshold = 5;
212 my @otherproposals = grep {
213 ($_->{agreement} >= $proposal_threshold)
214 && (1 == scalar @{ $_->{proposals} })
215 } @{ $bank_transactions };
217 push @proposals, @otherproposals;
219 # sort bank transaction proposals by quality (score) of proposal
220 if ($params{sort_by} && $params{sort_by} eq 'proposal') {
221 my $dir = $params{sort_dir} ? 1 : -1;
222 $bank_transactions = [ sort { ($a->{agreement} <=> $b->{agreement}) * $dir } @{ $bank_transactions } ];
225 return ( $bank_transactions , \@proposals );
231 if (!$::form->{filter}{bank_account}) {
232 flash('error', t8('No bank account chosen!'));
233 $self->action_search;
237 my $bank_account = SL::DB::BankAccount->load_cached($::form->{filter}->{bank_account});
238 my $fromdate = $::locale->parse_date_to_object($::form->{filter}->{fromdate});
239 my $todate = $::locale->parse_date_to_object($::form->{filter}->{todate});
240 $todate->add( days => 1 ) if $todate;
242 my ($bank_transactions, $proposals) = $self->gather_bank_transactions_and_proposals(
243 bank_account => $bank_account,
244 fromdate => $fromdate,
246 sort_by => $::form->{sort_by},
247 sort_dir => $::form->{sort_dir},
250 $::request->layout->add_javascripts("kivi.BankTransaction.js");
251 $self->render('bank_transactions/list',
252 title => t8('Bank transactions MT940'),
253 BANK_TRANSACTIONS => $bank_transactions,
254 PROPOSALS => $proposals,
255 bank_account => $bank_account,
256 ui_tab => scalar(@{ $proposals }) > 0 ? 1 : 0,
260 sub action_assign_invoice {
263 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
265 $self->render('bank_transactions/assign_invoice',
267 title => t8('Assign invoice'),);
270 sub action_create_invoice {
272 my %myconfig = %main::myconfig;
274 $self->transaction(SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id}));
276 my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(iban => $self->transaction->{remote_account_number});
277 my $use_vendor_filter = $self->transaction->{remote_account_number} && $vendor_of_transaction;
279 my $templates_ap = SL::DB::Manager::RecordTemplate->get_all(
280 where => [ template_type => 'ap_transaction' ],
281 sort_by => [ qw(template_name) ],
282 with_objects => [ qw(employee vendor) ],
284 my $templates_gl = SL::DB::Manager::RecordTemplate->get_all(
285 query => [ template_type => 'gl_transaction',
286 chart_id => SL::DB::Manager::BankAccount->find_by(id => $self->transaction->local_bank_account_id)->chart_id,
288 sort_by => [ qw(template_name) ],
289 with_objects => [ qw(employee record_template_items) ],
292 # pre filter templates_ap, if we have a vendor match (IBAN eq IBAN) - show and allow user to edit this via gui!
293 $templates_ap = [ grep { $_->vendor_id == $vendor_of_transaction->id } @{ $templates_ap } ] if $use_vendor_filter;
295 $self->callback($self->url_for(
297 'filter.bank_account' => $::form->{filter}->{bank_account},
298 'filter.todate' => $::form->{filter}->{todate},
299 'filter.fromdate' => $::form->{filter}->{fromdate},
302 # if we have exactly one ap match, use this directly
303 if (1 == scalar @{ $templates_ap }) {
304 $self->redirect_to($self->load_ap_record_template_url($templates_ap->[0]));
307 my $dialog_html = $self->render(
308 'bank_transactions/create_invoice',
309 { layout => 0, output => 0 },
310 title => t8('Create invoice'),
311 TEMPLATES_GL => $use_vendor_filter && @{ $templates_ap } ? undef : $templates_gl,
312 TEMPLATES_AP => $templates_ap,
313 vendor_name => $use_vendor_filter && @{ $templates_ap } ? $vendor_of_transaction->name : undef,
315 $self->js->run('kivi.BankTransaction.show_create_invoice_dialog', $dialog_html)->render;
319 sub action_ajax_payment_suggestion {
322 # based on a BankTransaction ID and a Invoice or PurchaseInvoice ID passed via $::form,
323 # create an HTML blob to be used by the js function add_invoices in templates/webpages/bank_transactions/list.html
324 # and return encoded as JSON
326 croak("Need bt_id") unless $::form->{bt_id};
328 my $invoice = SL::DB::Manager::Invoice->find_by( id => $::form->{prop_id} ) || SL::DB::Manager::PurchaseInvoice->find_by( id => $::form->{prop_id} );
330 croak("No valid invoice found") unless $invoice;
332 my $html = $self->render(
333 'bank_transactions/_payment_suggestion', { output => 0 },
334 bt_id => $::form->{bt_id},
338 $self->render(\ SL::JSON::to_json( { 'html' => "$html" } ), { layout => 0, type => 'json', process => 0 });
341 sub action_filter_templates {
344 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
346 my (@filter, @filter_ap);
348 # filter => gl and ap | filter_ap = ap (i.e. vendorname)
349 push @filter, ('template_name' => { ilike => '%' . $::form->{template} . '%' }) if $::form->{template};
350 push @filter, ('reference' => { ilike => '%' . $::form->{reference} . '%' }) if $::form->{reference};
351 push @filter_ap, ('vendor.name' => { ilike => '%' . $::form->{vendor} . '%' }) if $::form->{vendor};
352 push @filter_ap, @filter;
353 my $templates_gl = SL::DB::Manager::RecordTemplate->get_all(
354 query => [ template_type => 'gl_transaction',
355 chart_id => SL::DB::Manager::BankAccount->find_by(id => $self->transaction->local_bank_account_id)->chart_id,
356 (and => \@filter) x !!@filter
358 with_objects => [ qw(employee record_template_items) ],
361 my $templates_ap = SL::DB::Manager::RecordTemplate->get_all(
362 where => [ template_type => 'ap_transaction', (and => \@filter_ap) x !!@filter_ap ],
363 with_objects => [ qw(employee vendor) ],
365 $::form->{filter} //= {};
367 $self->callback($self->url_for(
369 'filter.bank_account' => $::form->{filter}->{bank_account},
370 'filter.todate' => $::form->{filter}->{todate},
371 'filter.fromdate' => $::form->{filter}->{fromdate},
374 my $output = $self->render(
375 'bank_transactions/_template_list',
377 TEMPLATES_AP => $templates_ap,
378 TEMPLATES_GL => $templates_gl,
381 $self->render(\to_json({ html => $output }), { type => 'json', process => 0 });
384 sub action_ajax_add_list {
387 my @where_sale = (amount => { ne => \'paid' });
388 my @where_purchase = (amount => { ne => \'paid' });
390 if ($::form->{invnumber}) {
391 push @where_sale, (invnumber => { ilike => like($::form->{invnumber})});
392 push @where_purchase, (invnumber => { ilike => like($::form->{invnumber})});
395 if ($::form->{amount}) {
396 push @where_sale, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
397 push @where_purchase, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
400 if ($::form->{vcnumber}) {
401 push @where_sale, ('customer.customernumber' => { ilike => like($::form->{vcnumber})});
402 push @where_purchase, ('vendor.vendornumber' => { ilike => like($::form->{vcnumber})});
405 if ($::form->{vcname}) {
406 push @where_sale, ('customer.name' => { ilike => like($::form->{vcname})});
407 push @where_purchase, ('vendor.name' => { ilike => like($::form->{vcname})});
410 if ($::form->{transdatefrom}) {
411 my $fromdate = $::locale->parse_date_to_object($::form->{transdatefrom});
412 if ( ref($fromdate) eq 'DateTime' ) {
413 push @where_sale, ('transdate' => { ge => $fromdate});
414 push @where_purchase, ('transdate' => { ge => $fromdate});
418 if ($::form->{transdateto}) {
419 my $todate = $::locale->parse_date_to_object($::form->{transdateto});
420 if ( ref($todate) eq 'DateTime' ) {
421 $todate->add(days => 1);
422 push @where_sale, ('transdate' => { lt => $todate});
423 push @where_purchase, ('transdate' => { lt => $todate});
427 my $all_open_ar_invoices = SL::DB::Manager::Invoice ->get_all(where => \@where_sale, with_objects => 'customer');
428 my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => \@where_purchase, with_objects => 'vendor');
430 my @all_open_invoices = @{ $all_open_ar_invoices };
431 # add ap invoices, filtering out subcent open amounts
432 push @all_open_invoices, grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
434 @all_open_invoices = sort { $a->id <=> $b->id } @all_open_invoices;
436 my $output = $self->render(
437 'bank_transactions/add_list',
439 INVOICES => \@all_open_invoices,
442 my %result = ( count => 0, html => $output );
444 $self->render(\to_json(\%result), { type => 'json', process => 0 });
447 sub action_ajax_accept_invoices {
450 my @selected_invoices;
451 foreach my $invoice_id (@{ $::form->{invoice_id} || [] }) {
452 my $invoice_object = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
453 push @selected_invoices, $invoice_object;
457 'bank_transactions/invoices',
459 INVOICES => \@selected_invoices,
460 bt_id => $::form->{bt_id},
467 return 0 if !$::form->{invoice_ids};
469 my %invoice_hash = %{ delete $::form->{invoice_ids} }; # each key (the bt line with a bt_id) contains an array of invoice_ids
471 # e.g. three partial payments with bt_ids 54, 55 and 56 for invoice with id 74:
484 # or if the payment with bt_id 44 is used to pay invoices with ids 50, 51 and 52
486 # '44' => [ '50', '51', 52' ]
489 $::form->{invoice_skontos} ||= {}; # hash of arrays containing the payment types, could be empty
491 # a bank_transaction may be assigned to several invoices, i.e. a customer
492 # might pay several open invoices with one transaction
498 if ( $::form->{proposal_ids} ) {
499 foreach (@{ $::form->{proposal_ids} }) {
500 my $bank_transaction_id = $_;
501 my $invoice_ids = $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 => ($::form->{sources} // {})->{$_},
506 memos => ($::form->{memos} // {})->{$_},
508 $count += scalar( @{$invoice_ids} );
511 while ( my ($bank_transaction_id, $invoice_ids) = each(%invoice_hash) ) {
512 push @{ $self->problems }, $self->save_single_bank_transaction(
513 bank_transaction_id => $bank_transaction_id,
514 invoice_ids => $invoice_ids,
515 sources => [ map { $::form->{"sources_${bank_transaction_id}_${_}"} } @{ $invoice_ids } ],
516 memos => [ map { $::form->{"memos_${bank_transaction_id}_${_}"} } @{ $invoice_ids } ],
518 $count += scalar( @{$invoice_ids} );
521 my $max_count = $count;
522 foreach (@{ $self->problems }) {
523 $count-- if $_->{result} eq 'error';
525 return ($count, $max_count);
528 sub action_save_invoices {
530 my ($success_count, $max_count) = $self->save_invoices();
532 if ($success_count == $max_count) {
533 flash('ok', t8('#1 invoice(s) saved.', $success_count));
535 flash('error', t8('At least #1 invoice(s) not saved', $max_count - $success_count));
538 $self->action_list();
541 sub action_save_proposals {
544 if ( $::form->{proposal_ids} ) {
545 my $propcount = scalar(@{ $::form->{proposal_ids} });
546 if ( $propcount > 0 ) {
547 my $count = $self->save_invoices();
549 flash('ok', t8('#1 proposal(s) with #2 invoice(s) saved.', $propcount, $count));
552 $self->action_list();
556 sub save_single_bank_transaction {
557 my ($self, %params) = @_;
561 bank_transaction => SL::DB::Manager::BankTransaction->find_by(id => $params{bank_transaction_id}),
565 if (!$data{bank_transaction}) {
569 message => $::locale->text('The ID #1 is not a valid database ID.', $data{bank_transaction_id}),
573 my $bank_transaction = $data{bank_transaction};
575 if ($bank_transaction->closed_period) {
579 message => $::locale->text('Cannot post payment for a closed period!'),
585 my $bt_id = $data{bank_transaction_id};
586 my $sign = $bank_transaction->amount < 0 ? -1 : 1;
587 my $payment_received = $bank_transaction->amount > 0;
588 my $payment_sent = $bank_transaction->amount < 0;
591 foreach my $invoice_id (@{ $params{invoice_ids} }) {
592 my $invoice = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
597 message => $::locale->text("The ID #1 is not a valid database ID.", $invoice_id),
600 push @{ $data{invoices} }, $invoice;
603 if ( $payment_received
604 && any { ( $_->is_sales && ($_->amount < 0))
605 || (!$_->is_sales && ($_->amount > 0))
606 } @{ $data{invoices} }) {
610 message => $::locale->text("Received payments can only be posted for sales invoices and purchase credit notes."),
615 && any { ( $_->is_sales && ($_->amount > 0))
616 || (!$_->is_sales && ($_->amount < 0) && ($_->invoice_type eq 'purchase_invoice'))
617 } @{ $data{invoices} }) {
621 message => $::locale->text("Sent payments can only be posted for purchase invoices and sales credit notes."),
625 my $max_invoices = scalar(@{ $data{invoices} });
628 foreach my $invoice (@{ $data{invoices} }) {
629 my $source = ($data{sources} // [])->[$n_invoices];
630 my $memo = ($data{memos} // [])->[$n_invoices];
633 # safety check invoice open
634 croak("Invoice closed. Cannot proceed.") unless ($invoice->open_amount);
636 if ( ($payment_sent && $bank_transaction->not_assigned_amount >= 0)
637 || ($payment_received && $bank_transaction->not_assigned_amount <= 0)) {
641 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."),
645 my ($payment_type, $free_skonto_amount);
646 if ( defined $::form->{invoice_skontos}->{"$bt_id"} ) {
647 $payment_type = shift(@{ $::form->{invoice_skontos}->{"$bt_id"} });
649 $payment_type = 'without_skonto';
652 if ($payment_type eq 'free_skonto') {
653 # parse user input > 0
654 if ($::form->parse_amount(\%::myconfig, $::form->{"free_skonto_amount"}->{"$bt_id"}{$invoice->id}) > 0) {
655 $free_skonto_amount = $::form->parse_amount(\%::myconfig, $::form->{"free_skonto_amount"}->{"$bt_id"}{$invoice->id});
660 message => $::locale->text("Free skonto amount has to be a positive number."),
665 # TODO rewrite this: really booked amount should be a return value of Payment.pm
666 # also this controller shouldnt care about how to calc skonto. we simply delegate the
667 # payment_type to the helper and get the corresponding bank_transaction values back
668 # hotfix to get the signs right - compare absolute values and later set the signs
669 # should be better done elsewhere - changing not_assigned_amount to abs feels seriously bogus
671 my $open_amount = $payment_type eq 'with_skonto_pt' ? $invoice->amount_less_skonto : $invoice->open_amount;
672 $open_amount = abs($open_amount);
673 $open_amount -= $free_skonto_amount if ($payment_type eq 'free_skonto');
674 my $not_assigned_amount = abs($bank_transaction->not_assigned_amount);
675 my $amount_for_booking = ($open_amount < $not_assigned_amount) ? $open_amount : $not_assigned_amount;
676 my $amount_for_payment = $amount_for_booking;
678 # get the right direction for the payment bookings (all amounts < 0 are stornos, credit notes or negative ap)
679 $amount_for_payment *= -1 if $invoice->amount < 0;
680 $free_skonto_amount *= -1 if ($free_skonto_amount && $invoice->amount < 0);
681 # get the right direction for the bank transaction
682 $amount_for_booking *= $sign;
684 $bank_transaction->invoice_amount($bank_transaction->invoice_amount + $amount_for_booking);
686 # ... and then pay the invoice
687 my @acc_ids = $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
688 trans_id => $invoice->id,
689 amount => $amount_for_payment,
690 payment_type => $payment_type,
693 skonto_amount => $free_skonto_amount,
695 transdate => $bank_transaction->valutadate->to_kivitendo);
696 # ... and record the origin via BankTransactionAccTrans
697 if (scalar(@acc_ids) < 2) {
701 message => $::locale->text("Unable to book transactions for bank purpose #1", $bank_transaction->purpose),
704 foreach my $acc_trans_id (@acc_ids) {
705 my $id_type = $invoice->is_sales ? 'ar' : 'ap';
707 acc_trans_id => $acc_trans_id,
708 bank_transaction_id => $bank_transaction->id,
709 $id_type => $invoice->id,
711 SL::DB::BankTransactionAccTrans->new(%props_acc)->save;
713 # Record a record link from the bank transaction to the invoice
715 from_table => 'bank_transactions',
717 to_table => $invoice->is_sales ? 'ar' : 'ap',
718 to_id => $invoice->id,
720 SL::DB::RecordLink->new(%props)->save;
722 # "close" a sepa_export_item if it exists
723 # code duplicated in action_save_proposals!
724 # currently only works, if there is only exactly one open sepa_export_item
725 if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
726 if ( scalar @$seis == 1 ) {
727 # moved the execution and the check for sepa_export into a method,
728 # this isn't part of a transaction, though
729 $seis->[0]->set_executed if $invoice->id == $seis->[0]->arap_id;
734 $bank_transaction->save;
736 # 'undef' means 'no error' here.
741 my $rez = $data{bank_transaction}->db->with_transaction(sub {
743 $error = $worker->();
754 # Rollback Fehler nicht weiterreichen
756 # aber einen rollback von hand
757 $::lxdebug->message(LXDebug->DEBUG2(),"finish worker with ". ($error ? $error->{result} : '-'));
758 $data{bank_transaction}->db->dbh->rollback if $error && $error->{result} eq 'error';
761 return grep { $_ } ($error, @warnings);
763 sub action_unlink_bank_transaction {
764 my ($self, %params) = @_;
766 croak("No bank transaction ids") unless scalar @{ $::form->{ids}} > 0;
770 foreach my $bt_id (@{ $::form->{ids}} ) {
772 my $bank_transaction = SL::DB::Manager::BankTransaction->find_by(id => $bt_id);
773 croak("No valid bank transaction found") unless (ref($bank_transaction) eq 'SL::DB::BankTransaction');
774 croak t8('Cannot unlink payment for a closed period!') if $bank_transaction->closed_period;
776 # everything in one transaction
777 my $rez = $bank_transaction->db->with_transaction(sub {
778 # 1. remove all reconciliations (due to underlying trigger, this has to be the first step)
779 my $rec_links = SL::DB::Manager::ReconciliationLink->get_all(where => [ bank_transaction_id => $bt_id ]);
780 $_->delete for @{ $rec_links };
783 foreach my $acc_trans_id_entry (@{ SL::DB::Manager::BankTransactionAccTrans->get_all(where => [bank_transaction_id => $bt_id ] )}) {
785 my $acc_trans = SL::DB::Manager::AccTransaction->get_all(where => [acc_trans_id => $acc_trans_id_entry->acc_trans_id]);
787 # save trans_id and type
788 die "no type" unless ($acc_trans_id_entry->ar_id || $acc_trans_id_entry->ap_id || $acc_trans_id_entry->gl_id);
789 $trans_ids{$acc_trans_id_entry->ar_id} = 'ar' if $acc_trans_id_entry->ar_id;
790 $trans_ids{$acc_trans_id_entry->ap_id} = 'ap' if $acc_trans_id_entry->ap_id;
791 $trans_ids{$acc_trans_id_entry->gl_id} = 'gl' if $acc_trans_id_entry->gl_id;
792 # 2. all good -> ready to delete acc_trans and bt_acc link
793 $acc_trans_id_entry->delete;
794 $_->delete for @{ $acc_trans };
796 # 3. update arap.paid (may not be 0, yet)
797 # or in case of gl, delete whole entry
798 while (my ($trans_id, $type) = each %trans_ids) {
800 SL::DB::Manager::GLTransaction->delete_all(where => [ id => $trans_id ]);
803 die ("invalid type") unless $type =~ m/^(ar|ap)$/;
805 # recalc and set paid via database query
806 my $query = qq|UPDATE $type SET paid =
807 (SELECT COALESCE(abs(sum(amount)),0) FROM acc_trans
809 AND chart_link ilike '%paid%')
812 die if (do_query($::form, $bank_transaction->db->dbh, $query, $trans_id, $trans_id) == -1);
814 # 4. and delete all (if any) record links
815 my $rl = SL::DB::Manager::RecordLink->delete_all(where => [ from_id => $bt_id, from_table => 'bank_transactions' ]);
817 # 5. finally reset this bank transaction
818 $bank_transaction->invoice_amount(0);
819 $bank_transaction->cleared(0);
820 $bank_transaction->save;
821 # 6. and add a log entry in history_erp
822 SL::DB::History->new(
823 trans_id => $bank_transaction->id,
824 snumbers => 'bank_transaction_unlink_' . $bank_transaction->id,
825 employee_id => SL::DB::Manager::Employee->current->id,
826 what_done => 'bank_transaction',
827 addition => 'UNLINKED',
832 }) || die t8('error while unlinking payment #1 : ', $bank_transaction->purpose) . $bank_transaction->db->error . "\n";
837 flash('ok', t8('#1 bank transaction bookings undone.', $success_count));
838 $self->action_list_all() unless $params{testcase};
845 $::auth->assert('bank_transaction');
852 sub make_filter_summary {
855 my $filter = $::form->{filter} || {};
859 [ $filter->{"transdate:date::ge"}, $::locale->text('Transdate') . " " . $::locale->text('From Date') ],
860 [ $filter->{"transdate:date::le"}, $::locale->text('Transdate') . " " . $::locale->text('To Date') ],
861 [ $filter->{"valutadate:date::ge"}, $::locale->text('Valutadate') . " " . $::locale->text('From Date') ],
862 [ $filter->{"valutadate:date::le"}, $::locale->text('Valutadate') . " " . $::locale->text('To Date') ],
863 [ $filter->{"amount:number"}, $::locale->text('Amount') ],
864 [ $filter->{"bank_account_id:integer"}, $::locale->text('Local bank account') ],
865 [ $filter->{"remote_name:substr::ilike"}, $::locale->text('Remote name') ],
869 push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
872 $self->{filter_summary} = join ', ', @filter_strings;
878 my $callback = $self->models->get_callback;
880 my $report = SL::ReportGenerator->new(\%::myconfig, $::form);
881 $self->{report} = $report;
883 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);
884 my @sortable = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount purpose local_account_number local_bank_code);
887 ids => { raw_header_data => checkbox_tag("", id => "check_all", checkall => "[data-checkall=1]"),
889 raw_data => sub { if (@{ $_[0]->linked_invoices }) {
890 if ($_[0]->closed_period) {
891 html_tag('text', "X"); #, tooltip => t8('Bank Transaction is in a closed period.')),
893 checkbox_tag("ids[]", value => $_[0]->id, "data-checkall" => 1);
896 transdate => { sub => sub { $_[0]->transdate_as_date } },
897 valutadate => { sub => sub { $_[0]->valutadate_as_date } },
899 remote_account_number => { },
900 remote_bank_code => { },
901 amount => { sub => sub { $_[0]->amount_as_number },
903 invoice_amount => { sub => sub { $_[0]->invoice_amount_as_number },
905 invoices => { sub => sub { my @invnumbers; for my $obj (@{ $_[0]->linked_invoices }) {
906 next unless $obj; push @invnumbers, $obj->invnumber } return \@invnumbers } },
907 currency => { sub => sub { $_[0]->currency->name } },
909 local_account_number => { sub => sub { $_[0]->local_bank_account->account_number } },
910 local_bank_code => { sub => sub { $_[0]->local_bank_account->bank_code } },
911 local_bank_name => { sub => sub { $_[0]->local_bank_account->name } },
915 map { $column_defs{$_}->{text} ||= $::locale->text( $self->models->get_sort_spec->{$_}->{title} ) } keys %column_defs;
917 $report->set_options(
918 std_column_visibility => 1,
919 controller_class => 'BankTransaction',
920 output_format => 'HTML',
921 top_info_text => $::locale->text('Bank transactions'),
922 title => $::locale->text('Bank transactions'),
923 allow_pdf_export => 1,
924 allow_csv_export => 1,
926 $report->set_columns(%column_defs);
927 $report->set_column_order(@columns);
928 $report->set_export_options(qw(list_all filter));
929 $report->set_options_from_form;
930 $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
931 $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
933 my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted();
935 $report->set_options(
936 raw_top_info_text => $self->render('bank_transactions/report_top', { output => 0 }, BANK_ACCOUNTS => $bank_accounts),
937 raw_bottom_info_text => $self->render('bank_transactions/report_bottom', { output => 0 }),
941 sub init_problems { [] }
946 SL::Controller::Helper::GetModels->new(
951 dir => 0, # 1 = ASC, 0 = DESC : default sort is newest at top
954 transdate => t8('Transdate'),
955 remote_name => t8('Remote name'),
956 amount => t8('Amount'),
957 invoice_amount => t8('Assigned'),
958 invoices => t8('Linked invoices'),
959 valutadate => t8('Valutadate'),
960 remote_account_number => t8('Remote account number'),
961 remote_bank_code => t8('Remote bank code'),
962 currency => t8('Currency'),
963 purpose => t8('Purpose'),
964 local_account_number => t8('Local account number'),
965 local_bank_code => t8('Local bank code'),
966 local_bank_name => t8('Bank account'),
968 with_objects => [ 'local_bank_account', 'currency' ],
972 sub load_ap_record_template_url {
973 my ($self, $template) = @_;
975 return $self->url_for(
976 controller => 'ap.pl',
977 action => 'load_record_template',
979 'form_defaults.amount_1' => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
980 'form_defaults.transdate' => $self->transaction->transdate_as_date,
981 'form_defaults.duedate' => $self->transaction->transdate_as_date,
982 'form_defaults.no_payment_bookings' => 1,
983 'form_defaults.paid_1_suggestion' => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
984 'form_defaults.AP_paid_1_suggestion' => $self->transaction->local_bank_account->chart->accno,
985 'form_defaults.callback' => $self->callback,
986 'form_defaults.notes' => $self->convert_purpose_for_template($template, $self->transaction->purpose),
990 sub load_gl_record_template_url {
991 my ($self, $template) = @_;
993 return $self->url_for(
994 controller => 'gl.pl',
995 action => 'load_record_template',
997 'form_defaults.amount_1' => abs($self->transaction->not_assigned_amount), # always positive
998 'form_defaults.transdate' => $self->transaction->transdate_as_date,
999 'form_defaults.callback' => $self->callback,
1000 'form_defaults.bt_id' => $self->transaction->id,
1001 'form_defaults.bt_chart_id' => $self->transaction->local_bank_account->chart->id,
1002 'form_defaults.description' => $self->convert_purpose_for_template($template, $self->transaction->purpose),
1006 sub convert_purpose_for_template {
1007 my ($self, $template, $purpose) = @_;
1009 # enter custom code here
1014 sub setup_search_action_bar {
1015 my ($self, %params) = @_;
1017 for my $bar ($::request->layout->get('actionbar')) {
1021 submit => [ '#search_form', { action => 'BankTransaction/list' } ],
1022 accesskey => 'enter',
1028 sub setup_list_all_action_bar {
1029 my ($self, %params) = @_;
1031 for my $bar ($::request->layout->get('actionbar')) {
1034 action => [ t8('Actions') ],
1036 t8('Unlink bank transactions'),
1037 submit => [ '#form', { action => 'BankTransaction/unlink_bank_transaction' } ],
1038 checks => [ [ 'kivi.check_if_entries_selected', '[name="ids[]"]' ] ],
1039 disabled => $::instance_conf->get_payments_changeable ? t8('Cannot safely unlink bank transactions, please set the posting configuration for payments to unchangeable.') : undef,
1044 submit => [ '#filter_form', { action => 'BankTransaction/list_all' } ],
1045 accesskey => 'enter',
1060 SL::Controller::BankTransaction - Posting payments to invoices from
1061 bank transactions imported earlier
1067 =item C<save_single_bank_transaction %params>
1069 Takes a bank transaction ID (as parameter C<bank_transaction_id> and
1070 tries to post its amount to a certain number of invoices (parameter
1071 C<invoice_ids>, an array ref of database IDs to purchase or sales
1074 This method handles already partly assigned bank transactions.
1076 This method cannot handle already partly assigned bank transactions, i.e.
1077 a bank transaction that has a invoice_amount <> 0 but not the fully
1078 transaction amount (invoice_amount == amount).
1080 If the amount of the bank transaction is higher than the sum of
1081 the assigned invoices (1 .. n) the bank transaction will only be
1084 The whole function is wrapped in a database transaction. If an
1085 exception occurs the bank transaction is not posted at all. The same
1086 is true if the code detects an error during the execution, e.g. a bank
1087 transaction that's already been posted earlier. In both cases the
1088 database transaction will be rolled back.
1090 If warnings but not errors occur the database transaction is still
1093 The return value is an error object or C<undef> if the function
1094 succeeded. The calling function will collect all warnings and errors
1095 and display them in a nicely formatted table if any occurred.
1097 An error object is a hash reference containing the following members:
1101 =item * C<result> — can be either C<warning> or C<error>. Warnings are
1102 displayed slightly different than errors.
1104 =item * C<message> — a human-readable message included in the list of
1105 errors meant as the description of why the problem happened
1107 =item * C<bank_transaction_id>, C<invoice_ids> — the same parameters
1108 that the function was called with
1110 =item * C<bank_transaction> — the database object
1111 (C<SL::DB::BankTransaction>) corresponding to C<bank_transaction_id>
1113 =item * C<invoices> — an array ref of the database objects (either
1114 C<SL::DB::Invoice> or C<SL::DB::PurchaseInvoice>) corresponding to
1119 =item C<action_unlink_bank_transaction>
1121 Takes one or more bank transaction ID (as parameter C<form::ids>) and
1122 tries to revert all payment bookings including already cleared bookings.
1124 This method won't undo payments that are in a closed period and assumes
1125 that payments are not manually changed, i.e. only imported payments.
1127 GL-records will be deleted completely if a bank transaction was the source.
1129 TODO: we still rely on linked_records for the check boxes
1131 =item C<convert_purpose_for_template>
1133 This method can be used to parse, filter and convert the bank transaction's
1134 purpose string before it will be assigned to the description field of a
1135 gl transaction or to the notes field of an ap transaction.
1136 You have to write your own custom code.
1142 Niclas Zimmermann E<lt>niclas@kivitendo-premium.deE<gt>,
1143 Geoffrey Richardson E<lt>information@richardson-bueren.deE<gt>