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},
303 'bank_transactions/create_invoice',
305 title => t8('Create invoice'),
306 TEMPLATES_GL => $use_vendor_filter && @{ $templates_ap } ? undef : $templates_gl,
307 TEMPLATES_AP => $templates_ap,
308 vendor_name => $use_vendor_filter && @{ $templates_ap } ? $vendor_of_transaction->name : undef,
312 sub action_ajax_payment_suggestion {
315 # based on a BankTransaction ID and a Invoice or PurchaseInvoice ID passed via $::form,
316 # create an HTML blob to be used by the js function add_invoices in templates/webpages/bank_transactions/list.html
317 # and return encoded as JSON
319 croak("Need bt_id") unless $::form->{bt_id};
321 my $invoice = SL::DB::Manager::Invoice->find_by( id => $::form->{prop_id} ) || SL::DB::Manager::PurchaseInvoice->find_by( id => $::form->{prop_id} );
323 croak("No valid invoice found") unless $invoice;
325 my $html = $self->render(
326 'bank_transactions/_payment_suggestion', { output => 0 },
327 bt_id => $::form->{bt_id},
331 $self->render(\ SL::JSON::to_json( { 'html' => "$html" } ), { layout => 0, type => 'json', process => 0 });
334 sub action_filter_templates {
337 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
339 my (@filter, @filter_ap);
341 # filter => gl and ap | filter_ap = ap (i.e. vendorname)
342 push @filter, ('template_name' => { ilike => '%' . $::form->{template} . '%' }) if $::form->{template};
343 push @filter, ('reference' => { ilike => '%' . $::form->{reference} . '%' }) if $::form->{reference};
344 push @filter_ap, ('vendor.name' => { ilike => '%' . $::form->{vendor} . '%' }) if $::form->{vendor};
345 push @filter_ap, @filter;
346 my $templates_gl = SL::DB::Manager::RecordTemplate->get_all(
347 query => [ template_type => 'gl_transaction',
348 chart_id => SL::DB::Manager::BankAccount->find_by(id => $self->transaction->local_bank_account_id)->chart_id,
349 (and => \@filter) x !!@filter
351 with_objects => [ qw(employee record_template_items) ],
354 my $templates_ap = SL::DB::Manager::RecordTemplate->get_all(
355 where => [ template_type => 'ap_transaction', (and => \@filter_ap) x !!@filter_ap ],
356 with_objects => [ qw(employee vendor) ],
358 $::form->{filter} //= {};
360 $self->callback($self->url_for(
362 'filter.bank_account' => $::form->{filter}->{bank_account},
363 'filter.todate' => $::form->{filter}->{todate},
364 'filter.fromdate' => $::form->{filter}->{fromdate},
367 my $output = $self->render(
368 'bank_transactions/_template_list',
370 TEMPLATES_AP => $templates_ap,
371 TEMPLATES_GL => $templates_gl,
374 $self->render(\to_json({ html => $output }), { type => 'json', process => 0 });
377 sub action_ajax_add_list {
380 my @where_sale = (amount => { ne => \'paid' });
381 my @where_purchase = (amount => { ne => \'paid' });
383 if ($::form->{invnumber}) {
384 push @where_sale, (invnumber => { ilike => like($::form->{invnumber})});
385 push @where_purchase, (invnumber => { ilike => like($::form->{invnumber})});
388 if ($::form->{amount}) {
389 push @where_sale, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
390 push @where_purchase, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
393 if ($::form->{vcnumber}) {
394 push @where_sale, ('customer.customernumber' => { ilike => like($::form->{vcnumber})});
395 push @where_purchase, ('vendor.vendornumber' => { ilike => like($::form->{vcnumber})});
398 if ($::form->{vcname}) {
399 push @where_sale, ('customer.name' => { ilike => like($::form->{vcname})});
400 push @where_purchase, ('vendor.name' => { ilike => like($::form->{vcname})});
403 if ($::form->{transdatefrom}) {
404 my $fromdate = $::locale->parse_date_to_object($::form->{transdatefrom});
405 if ( ref($fromdate) eq 'DateTime' ) {
406 push @where_sale, ('transdate' => { ge => $fromdate});
407 push @where_purchase, ('transdate' => { ge => $fromdate});
411 if ($::form->{transdateto}) {
412 my $todate = $::locale->parse_date_to_object($::form->{transdateto});
413 if ( ref($todate) eq 'DateTime' ) {
414 $todate->add(days => 1);
415 push @where_sale, ('transdate' => { lt => $todate});
416 push @where_purchase, ('transdate' => { lt => $todate});
420 my $all_open_ar_invoices = SL::DB::Manager::Invoice ->get_all(where => \@where_sale, with_objects => 'customer');
421 my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => \@where_purchase, with_objects => 'vendor');
423 my @all_open_invoices = @{ $all_open_ar_invoices };
424 # add ap invoices, filtering out subcent open amounts
425 push @all_open_invoices, grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
427 @all_open_invoices = sort { $a->id <=> $b->id } @all_open_invoices;
429 my $output = $self->render(
430 'bank_transactions/add_list',
432 INVOICES => \@all_open_invoices,
435 my %result = ( count => 0, html => $output );
437 $self->render(\to_json(\%result), { type => 'json', process => 0 });
440 sub action_ajax_accept_invoices {
443 my @selected_invoices;
444 foreach my $invoice_id (@{ $::form->{invoice_id} || [] }) {
445 my $invoice_object = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
446 push @selected_invoices, $invoice_object;
450 'bank_transactions/invoices',
452 INVOICES => \@selected_invoices,
453 bt_id => $::form->{bt_id},
460 return 0 if !$::form->{invoice_ids};
462 my %invoice_hash = %{ delete $::form->{invoice_ids} }; # each key (the bt line with a bt_id) contains an array of invoice_ids
464 # e.g. three partial payments with bt_ids 54, 55 and 56 for invoice with id 74:
477 # or if the payment with bt_id 44 is used to pay invoices with ids 50, 51 and 52
479 # '44' => [ '50', '51', 52' ]
482 $::form->{invoice_skontos} ||= {}; # hash of arrays containing the payment types, could be empty
484 # a bank_transaction may be assigned to several invoices, i.e. a customer
485 # might pay several open invoices with one transaction
491 if ( $::form->{proposal_ids} ) {
492 foreach (@{ $::form->{proposal_ids} }) {
493 my $bank_transaction_id = $_;
494 my $invoice_ids = $invoice_hash{$_};
495 push @{ $self->problems }, $self->save_single_bank_transaction(
496 bank_transaction_id => $bank_transaction_id,
497 invoice_ids => $invoice_ids,
498 sources => ($::form->{sources} // {})->{$_},
499 memos => ($::form->{memos} // {})->{$_},
501 $count += scalar( @{$invoice_ids} );
504 while ( my ($bank_transaction_id, $invoice_ids) = each(%invoice_hash) ) {
505 push @{ $self->problems }, $self->save_single_bank_transaction(
506 bank_transaction_id => $bank_transaction_id,
507 invoice_ids => $invoice_ids,
508 sources => [ map { $::form->{"sources_${bank_transaction_id}_${_}"} } @{ $invoice_ids } ],
509 memos => [ map { $::form->{"memos_${bank_transaction_id}_${_}"} } @{ $invoice_ids } ],
511 $count += scalar( @{$invoice_ids} );
514 my $max_count = $count;
515 foreach (@{ $self->problems }) {
516 $count-- if $_->{result} eq 'error';
518 return ($count, $max_count);
521 sub action_save_invoices {
523 my ($success_count, $max_count) = $self->save_invoices();
525 if ($success_count == $max_count) {
526 flash('ok', t8('#1 invoice(s) saved.', $success_count));
528 flash('error', t8('At least #1 invoice(s) not saved', $max_count - $success_count));
531 $self->action_list();
534 sub action_save_proposals {
537 if ( $::form->{proposal_ids} ) {
538 my $propcount = scalar(@{ $::form->{proposal_ids} });
539 if ( $propcount > 0 ) {
540 my $count = $self->save_invoices();
542 flash('ok', t8('#1 proposal(s) with #2 invoice(s) saved.', $propcount, $count));
545 $self->action_list();
549 sub save_single_bank_transaction {
550 my ($self, %params) = @_;
554 bank_transaction => SL::DB::Manager::BankTransaction->find_by(id => $params{bank_transaction_id}),
558 if (!$data{bank_transaction}) {
562 message => $::locale->text('The ID #1 is not a valid database ID.', $data{bank_transaction_id}),
566 my $bank_transaction = $data{bank_transaction};
568 if ($bank_transaction->closed_period) {
572 message => $::locale->text('Cannot post payment for a closed period!'),
578 my $bt_id = $data{bank_transaction_id};
579 my $sign = $bank_transaction->amount < 0 ? -1 : 1;
580 my $payment_received = $bank_transaction->amount > 0;
581 my $payment_sent = $bank_transaction->amount < 0;
584 foreach my $invoice_id (@{ $params{invoice_ids} }) {
585 my $invoice = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
590 message => $::locale->text("The ID #1 is not a valid database ID.", $invoice_id),
593 push @{ $data{invoices} }, $invoice;
596 if ( $payment_received
597 && any { ( $_->is_sales && ($_->amount < 0))
598 || (!$_->is_sales && ($_->amount > 0))
599 } @{ $data{invoices} }) {
603 message => $::locale->text("Received payments can only be posted for sales invoices and purchase credit notes."),
608 && any { ( $_->is_sales && ($_->amount > 0))
609 || (!$_->is_sales && ($_->amount < 0) && ($_->invoice_type eq 'purchase_invoice'))
610 } @{ $data{invoices} }) {
614 message => $::locale->text("Sent payments can only be posted for purchase invoices and sales credit notes."),
618 my $max_invoices = scalar(@{ $data{invoices} });
621 foreach my $invoice (@{ $data{invoices} }) {
622 my $source = ($data{sources} // [])->[$n_invoices];
623 my $memo = ($data{memos} // [])->[$n_invoices];
626 # safety check invoice open
627 croak("Invoice closed. Cannot proceed.") unless ($invoice->open_amount);
629 if ( ($payment_sent && $bank_transaction->not_assigned_amount >= 0)
630 || ($payment_received && $bank_transaction->not_assigned_amount <= 0)) {
634 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."),
638 my ($payment_type, $free_skonto_amount);
639 if ( defined $::form->{invoice_skontos}->{"$bt_id"} ) {
640 $payment_type = shift(@{ $::form->{invoice_skontos}->{"$bt_id"} });
642 $payment_type = 'without_skonto';
645 if ($payment_type eq 'free_skonto') {
646 # parse user input > 0
647 if ($::form->parse_amount(\%::myconfig, $::form->{"free_skonto_amount"}->{"$bt_id"}{$invoice->id}) > 0) {
648 $free_skonto_amount = $::form->parse_amount(\%::myconfig, $::form->{"free_skonto_amount"}->{"$bt_id"}{$invoice->id});
653 message => $::locale->text("Free skonto amount has to be a positive number."),
658 # TODO rewrite this: really booked amount should be a return value of Payment.pm
659 # also this controller shouldnt care about how to calc skonto. we simply delegate the
660 # payment_type to the helper and get the corresponding bank_transaction values back
661 # hotfix to get the signs right - compare absolute values and later set the signs
662 # should be better done elsewhere - changing not_assigned_amount to abs feels seriously bogus
664 my $open_amount = $payment_type eq 'with_skonto_pt' ? $invoice->amount_less_skonto : $invoice->open_amount;
665 $open_amount = abs($open_amount);
666 $open_amount -= $free_skonto_amount if ($payment_type eq 'free_skonto');
667 my $not_assigned_amount = abs($bank_transaction->not_assigned_amount);
668 my $amount_for_booking = ($open_amount < $not_assigned_amount) ? $open_amount : $not_assigned_amount;
669 my $amount_for_payment = $amount_for_booking;
671 # get the right direction for the payment bookings (all amounts < 0 are stornos, credit notes or negative ap)
672 $amount_for_payment *= -1 if $invoice->amount < 0;
673 $free_skonto_amount *= -1 if ($free_skonto_amount && $invoice->amount < 0);
674 # get the right direction for the bank transaction
675 $amount_for_booking *= $sign;
677 $bank_transaction->invoice_amount($bank_transaction->invoice_amount + $amount_for_booking);
679 # ... and then pay the invoice
680 my @acc_ids = $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
681 trans_id => $invoice->id,
682 amount => $amount_for_payment,
683 payment_type => $payment_type,
686 skonto_amount => $free_skonto_amount,
688 transdate => $bank_transaction->valutadate->to_kivitendo);
689 # ... and record the origin via BankTransactionAccTrans
690 if (scalar(@acc_ids) < 2) {
694 message => $::locale->text("Unable to book transactions for bank purpose #1", $bank_transaction->purpose),
697 foreach my $acc_trans_id (@acc_ids) {
698 my $id_type = $invoice->is_sales ? 'ar' : 'ap';
700 acc_trans_id => $acc_trans_id,
701 bank_transaction_id => $bank_transaction->id,
702 $id_type => $invoice->id,
704 SL::DB::BankTransactionAccTrans->new(%props_acc)->save;
706 # Record a record link from the bank transaction to the invoice
708 from_table => 'bank_transactions',
710 to_table => $invoice->is_sales ? 'ar' : 'ap',
711 to_id => $invoice->id,
713 SL::DB::RecordLink->new(%props)->save;
715 # "close" a sepa_export_item if it exists
716 # code duplicated in action_save_proposals!
717 # currently only works, if there is only exactly one open sepa_export_item
718 if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
719 if ( scalar @$seis == 1 ) {
720 # moved the execution and the check for sepa_export into a method,
721 # this isn't part of a transaction, though
722 $seis->[0]->set_executed if $invoice->id == $seis->[0]->arap_id;
727 $bank_transaction->save;
729 # 'undef' means 'no error' here.
734 my $rez = $data{bank_transaction}->db->with_transaction(sub {
736 $error = $worker->();
747 # Rollback Fehler nicht weiterreichen
749 # aber einen rollback von hand
750 $::lxdebug->message(LXDebug->DEBUG2(),"finish worker with ". ($error ? $error->{result} : '-'));
751 $data{bank_transaction}->db->dbh->rollback if $error && $error->{result} eq 'error';
754 return grep { $_ } ($error, @warnings);
756 sub action_unlink_bank_transaction {
757 my ($self, %params) = @_;
759 croak("No bank transaction ids") unless scalar @{ $::form->{ids}} > 0;
763 foreach my $bt_id (@{ $::form->{ids}} ) {
765 my $bank_transaction = SL::DB::Manager::BankTransaction->find_by(id => $bt_id);
766 croak("No valid bank transaction found") unless (ref($bank_transaction) eq 'SL::DB::BankTransaction');
767 croak t8('Cannot unlink payment for a closed period!') if $bank_transaction->closed_period;
769 # everything in one transaction
770 my $rez = $bank_transaction->db->with_transaction(sub {
771 # 1. remove all reconciliations (due to underlying trigger, this has to be the first step)
772 my $rec_links = SL::DB::Manager::ReconciliationLink->get_all(where => [ bank_transaction_id => $bt_id ]);
773 $_->delete for @{ $rec_links };
776 foreach my $acc_trans_id_entry (@{ SL::DB::Manager::BankTransactionAccTrans->get_all(where => [bank_transaction_id => $bt_id ] )}) {
778 my $acc_trans = SL::DB::Manager::AccTransaction->get_all(where => [acc_trans_id => $acc_trans_id_entry->acc_trans_id]);
780 # save trans_id and type
781 die "no type" unless ($acc_trans_id_entry->ar_id || $acc_trans_id_entry->ap_id || $acc_trans_id_entry->gl_id);
782 $trans_ids{$acc_trans_id_entry->ar_id} = 'ar' if $acc_trans_id_entry->ar_id;
783 $trans_ids{$acc_trans_id_entry->ap_id} = 'ap' if $acc_trans_id_entry->ap_id;
784 $trans_ids{$acc_trans_id_entry->gl_id} = 'gl' if $acc_trans_id_entry->gl_id;
785 # 2. all good -> ready to delete acc_trans and bt_acc link
786 $acc_trans_id_entry->delete;
787 $_->delete for @{ $acc_trans };
789 # 3. update arap.paid (may not be 0, yet)
790 # or in case of gl, delete whole entry
791 while (my ($trans_id, $type) = each %trans_ids) {
793 SL::DB::Manager::GLTransaction->delete_all(where => [ id => $trans_id ]);
796 die ("invalid type") unless $type =~ m/^(ar|ap)$/;
798 # recalc and set paid via database query
799 my $query = qq|UPDATE $type SET paid =
800 (SELECT COALESCE(abs(sum(amount)),0) FROM acc_trans
802 AND chart_link ilike '%paid%')
805 die if (do_query($::form, $bank_transaction->db->dbh, $query, $trans_id, $trans_id) == -1);
807 # 4. and delete all (if any) record links
808 my $rl = SL::DB::Manager::RecordLink->delete_all(where => [ from_id => $bt_id, from_table => 'bank_transactions' ]);
810 # 5. finally reset this bank transaction
811 $bank_transaction->invoice_amount(0);
812 $bank_transaction->cleared(0);
813 $bank_transaction->save;
814 # 6. and add a log entry in history_erp
815 SL::DB::History->new(
816 trans_id => $bank_transaction->id,
817 snumbers => 'bank_transaction_unlink_' . $bank_transaction->id,
818 employee_id => SL::DB::Manager::Employee->current->id,
819 what_done => 'bank_transaction',
820 addition => 'UNLINKED',
825 }) || die t8('error while unlinking payment #1 : ', $bank_transaction->purpose) . $bank_transaction->db->error . "\n";
830 flash('ok', t8('#1 bank transaction bookings undone.', $success_count));
831 $self->action_list_all() unless $params{testcase};
838 $::auth->assert('bank_transaction');
845 sub make_filter_summary {
848 my $filter = $::form->{filter} || {};
852 [ $filter->{"transdate:date::ge"}, $::locale->text('Transdate') . " " . $::locale->text('From Date') ],
853 [ $filter->{"transdate:date::le"}, $::locale->text('Transdate') . " " . $::locale->text('To Date') ],
854 [ $filter->{"valutadate:date::ge"}, $::locale->text('Valutadate') . " " . $::locale->text('From Date') ],
855 [ $filter->{"valutadate:date::le"}, $::locale->text('Valutadate') . " " . $::locale->text('To Date') ],
856 [ $filter->{"amount:number"}, $::locale->text('Amount') ],
857 [ $filter->{"bank_account_id:integer"}, $::locale->text('Local bank account') ],
858 [ $filter->{"remote_name:substr::ilike"}, $::locale->text('Remote name') ],
862 push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
865 $self->{filter_summary} = join ', ', @filter_strings;
871 my $callback = $self->models->get_callback;
873 my $report = SL::ReportGenerator->new(\%::myconfig, $::form);
874 $self->{report} = $report;
876 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);
877 my @sortable = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount purpose local_account_number local_bank_code);
880 ids => { raw_header_data => checkbox_tag("", id => "check_all", checkall => "[data-checkall=1]"),
882 raw_data => sub { if (@{ $_[0]->linked_invoices }) {
883 if ($_[0]->closed_period) {
884 html_tag('text', "X"); #, tooltip => t8('Bank Transaction is in a closed period.')),
886 checkbox_tag("ids[]", value => $_[0]->id, "data-checkall" => 1);
889 transdate => { sub => sub { $_[0]->transdate_as_date } },
890 valutadate => { sub => sub { $_[0]->valutadate_as_date } },
892 remote_account_number => { },
893 remote_bank_code => { },
894 amount => { sub => sub { $_[0]->amount_as_number },
896 invoice_amount => { sub => sub { $_[0]->invoice_amount_as_number },
898 invoices => { sub => sub { my @invnumbers; for my $obj (@{ $_[0]->linked_invoices }) {
899 next unless $obj; push @invnumbers, $obj->invnumber } return \@invnumbers } },
900 currency => { sub => sub { $_[0]->currency->name } },
902 local_account_number => { sub => sub { $_[0]->local_bank_account->account_number } },
903 local_bank_code => { sub => sub { $_[0]->local_bank_account->bank_code } },
904 local_bank_name => { sub => sub { $_[0]->local_bank_account->name } },
908 map { $column_defs{$_}->{text} ||= $::locale->text( $self->models->get_sort_spec->{$_}->{title} ) } keys %column_defs;
910 $report->set_options(
911 std_column_visibility => 1,
912 controller_class => 'BankTransaction',
913 output_format => 'HTML',
914 top_info_text => $::locale->text('Bank transactions'),
915 title => $::locale->text('Bank transactions'),
916 allow_pdf_export => 1,
917 allow_csv_export => 1,
919 $report->set_columns(%column_defs);
920 $report->set_column_order(@columns);
921 $report->set_export_options(qw(list_all filter));
922 $report->set_options_from_form;
923 $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
924 $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
926 my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted();
928 $report->set_options(
929 raw_top_info_text => $self->render('bank_transactions/report_top', { output => 0 }, BANK_ACCOUNTS => $bank_accounts),
930 raw_bottom_info_text => $self->render('bank_transactions/report_bottom', { output => 0 }),
934 sub init_problems { [] }
939 SL::Controller::Helper::GetModels->new(
944 dir => 0, # 1 = ASC, 0 = DESC : default sort is newest at top
947 transdate => t8('Transdate'),
948 remote_name => t8('Remote name'),
949 amount => t8('Amount'),
950 invoice_amount => t8('Assigned'),
951 invoices => t8('Linked invoices'),
952 valutadate => t8('Valutadate'),
953 remote_account_number => t8('Remote account number'),
954 remote_bank_code => t8('Remote bank code'),
955 currency => t8('Currency'),
956 purpose => t8('Purpose'),
957 local_account_number => t8('Local account number'),
958 local_bank_code => t8('Local bank code'),
959 local_bank_name => t8('Bank account'),
961 with_objects => [ 'local_bank_account', 'currency' ],
965 sub load_ap_record_template_url {
966 my ($self, $template) = @_;
968 return $self->url_for(
969 controller => 'ap.pl',
970 action => 'load_record_template',
972 'form_defaults.amount_1' => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
973 'form_defaults.transdate' => $self->transaction->transdate_as_date,
974 'form_defaults.duedate' => $self->transaction->transdate_as_date,
975 'form_defaults.no_payment_bookings' => 1,
976 'form_defaults.paid_1_suggestion' => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
977 'form_defaults.AP_paid_1_suggestion' => $self->transaction->local_bank_account->chart->accno,
978 'form_defaults.callback' => $self->callback,
982 sub load_gl_record_template_url {
983 my ($self, $template) = @_;
985 return $self->url_for(
986 controller => 'gl.pl',
987 action => 'load_record_template',
989 'form_defaults.amount_1' => abs($self->transaction->not_assigned_amount), # always positive
990 'form_defaults.transdate' => $self->transaction->transdate_as_date,
991 'form_defaults.callback' => $self->callback,
992 'form_defaults.bt_id' => $self->transaction->id,
993 'form_defaults.bt_chart_id' => $self->transaction->local_bank_account->chart->id,
994 'form_defaults.description' => $self->transaction->purpose,
998 sub setup_search_action_bar {
999 my ($self, %params) = @_;
1001 for my $bar ($::request->layout->get('actionbar')) {
1005 submit => [ '#search_form', { action => 'BankTransaction/list' } ],
1006 accesskey => 'enter',
1012 sub setup_list_all_action_bar {
1013 my ($self, %params) = @_;
1015 for my $bar ($::request->layout->get('actionbar')) {
1018 action => [ t8('Actions') ],
1020 t8('Unlink bank transactions'),
1021 submit => [ '#form', { action => 'BankTransaction/unlink_bank_transaction' } ],
1022 checks => [ [ 'kivi.check_if_entries_selected', '[name="ids[]"]' ] ],
1023 disabled => $::instance_conf->get_payments_changeable ? t8('Cannot safely unlink bank transactions, please set the posting configuration for payments to unchangeable.') : undef,
1028 submit => [ '#filter_form', { action => 'BankTransaction/list_all' } ],
1029 accesskey => 'enter',
1044 SL::Controller::BankTransaction - Posting payments to invoices from
1045 bank transactions imported earlier
1051 =item C<save_single_bank_transaction %params>
1053 Takes a bank transaction ID (as parameter C<bank_transaction_id> and
1054 tries to post its amount to a certain number of invoices (parameter
1055 C<invoice_ids>, an array ref of database IDs to purchase or sales
1058 This method handles already partly assigned bank transactions.
1060 This method cannot handle already partly assigned bank transactions, i.e.
1061 a bank transaction that has a invoice_amount <> 0 but not the fully
1062 transaction amount (invoice_amount == amount).
1064 If the amount of the bank transaction is higher than the sum of
1065 the assigned invoices (1 .. n) the bank transaction will only be
1068 The whole function is wrapped in a database transaction. If an
1069 exception occurs the bank transaction is not posted at all. The same
1070 is true if the code detects an error during the execution, e.g. a bank
1071 transaction that's already been posted earlier. In both cases the
1072 database transaction will be rolled back.
1074 If warnings but not errors occur the database transaction is still
1077 The return value is an error object or C<undef> if the function
1078 succeeded. The calling function will collect all warnings and errors
1079 and display them in a nicely formatted table if any occurred.
1081 An error object is a hash reference containing the following members:
1085 =item * C<result> — can be either C<warning> or C<error>. Warnings are
1086 displayed slightly different than errors.
1088 =item * C<message> — a human-readable message included in the list of
1089 errors meant as the description of why the problem happened
1091 =item * C<bank_transaction_id>, C<invoice_ids> — the same parameters
1092 that the function was called with
1094 =item * C<bank_transaction> — the database object
1095 (C<SL::DB::BankTransaction>) corresponding to C<bank_transaction_id>
1097 =item * C<invoices> — an array ref of the database objects (either
1098 C<SL::DB::Invoice> or C<SL::DB::PurchaseInvoice>) corresponding to
1103 =item C<action_unlink_bank_transaction>
1105 Takes one or more bank transaction ID (as parameter C<form::ids>) and
1106 tries to revert all payment bookings including already cleared bookings.
1108 This method won't undo payments that are in a closed period and assumes
1109 that payments are not manually changed, i.e. only imported payments.
1111 GL-records will be deleted completely if a bank transaction was the source.
1113 TODO: we still rely on linked_records for the check boxes
1119 Niclas Zimmermann E<lt>niclas@kivitendo-premium.deE<gt>,
1120 Geoffrey Richardson E<lt>information@richardson-bueren.deE<gt>