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'},
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' },
101 and => [ type => 'credit_note',
102 amount => { lt => \'paid' }
106 with_objects => ['customer','payment_terms']);
108 my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => [amount => { ne => \'paid' }], with_objects => ['vendor' ,'payment_terms']);
109 my $all_open_sepa_export_items = SL::DB::Manager::SepaExportItem->get_all(where => [chart_id => $params{bank_account}->chart_id ,
110 'sepa_export.executed' => 0, 'sepa_export.closed' => 0 ], with_objects => ['sepa_export']);
112 my @all_open_invoices;
113 # filter out invoices with less than 1 cent outstanding
114 push @all_open_invoices, map { $_->{is_ar}=1 ; $_ } grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ar_invoices };
115 push @all_open_invoices, map { $_->{is_ar}=0 ; $_ } grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
118 my %sepa_export_items_by_id = partition_by { $_->ar_id || $_->ap_id } @$all_open_sepa_export_items;
120 # first collect sepa export items to open invoices
121 foreach my $open_invoice (@all_open_invoices){
122 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount,2);
123 $open_invoice->{skonto_type} = 'without_skonto';
124 foreach (@{ $sepa_export_items_by_id{ $open_invoice->id } || [] }) {
125 my $factor = ($_->ar_id == $open_invoice->id ? 1 : -1);
126 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount*$factor,2);
128 $open_invoice->{skonto_type} = $_->payment_type;
129 $sepa_exports{$_->sepa_export_id} ||= { count => 0, is_ar => 0, amount => 0, proposed => 0, invoices => [], item => $_ };
130 $sepa_exports{$_->sepa_export_id}->{count}++;
131 $sepa_exports{$_->sepa_export_id}->{is_ar}++ if $_->ar_id == $open_invoice->id;
132 $sepa_exports{$_->sepa_export_id}->{amount} += $_->amount * $factor;
133 push @{ $sepa_exports{$_->sepa_export_id}->{invoices} }, $open_invoice;
137 # try to match each bank_transaction with each of the possible open invoices
141 foreach my $bt (@{ $bank_transactions }) {
142 ## 5 Stellen hinter dem Komma auf 2 Stellen reduzieren
143 $bt->amount($bt->amount*1);
144 $bt->invoice_amount($bt->invoice_amount*1);
146 $bt->{proposals} = [];
147 $bt->{rule_matches} = [];
149 $bt->{remote_name} .= $bt->{remote_name_1} if $bt->{remote_name_1};
151 if ( $bt->is_batch_transaction ) {
153 foreach ( keys %sepa_exports) {
154 if ( abs(($sepa_exports{$_}->{amount} * 1) - ($bt->amount * 1)) < 0.01 ) {
156 @{$bt->{proposals}} = @{$sepa_exports{$_}->{invoices}};
157 $bt->{sepa_export_ok} = 1;
158 $sepa_exports{$_}->{proposed}=1;
159 push(@proposals, $bt);
165 # batch transaction has no remotename !!
168 # try to match the current $bt to each of the open_invoices, saving the
169 # results of get_agreement_with_invoice in $open_invoice->{agreement} and
170 # $open_invoice->{rule_matches}.
172 # The values are overwritten each time a new bt is checked, so at the end
173 # of each bt the likely results are filtered and those values are stored in
174 # the arrays $bt->{proposals} and $bt->{rule_matches}, and the agreement
175 # score is stored in $bt->{agreement}
177 foreach my $open_invoice (@all_open_invoices) {
178 ($open_invoice->{agreement}, $open_invoice->{rule_matches}) = $bt->get_agreement_with_invoice($open_invoice,
179 sepa_export_items => $all_open_sepa_export_items,
181 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,
182 $open_invoice->amount * ($open_invoice->{is_ar} ? 1 : -1), 2);
186 my $min_agreement = 3; # suggestions must have at least this score
188 my $max_agreement = max map { $_->{agreement} } @all_open_invoices;
190 # add open_invoices with highest agreement into array $bt->{proposals}
191 if ( $max_agreement >= $min_agreement ) {
192 $bt->{proposals} = [ grep { $_->{agreement} == $max_agreement } @all_open_invoices ];
193 $bt->{agreement} = $max_agreement; #scalar @{ $bt->{proposals} } ? $agreement + 1 : '';
195 # store the rule_matches in a separate array, so they can be displayed in template
196 foreach ( @{ $bt->{proposals} } ) {
197 push(@{$bt->{rule_matches}}, $_->{rule_matches});
203 # separate filter for proposals (second tab, agreement >= 5 and exactly one match)
204 # to qualify as a proposal there has to be
205 # * agreement >= 5 TODO: make threshold configurable in configuration
206 # * there must be only one exact match
207 my $proposal_threshold = 5;
208 my @otherproposals = grep {
209 ($_->{agreement} >= $proposal_threshold)
210 && (1 == scalar @{ $_->{proposals} })
211 } @{ $bank_transactions };
213 push @proposals, @otherproposals;
215 # sort bank transaction proposals by quality (score) of proposal
216 if ($params{sort_by} && $params{sort_by} eq 'proposal') {
217 my $dir = $params{sort_dir} ? 1 : -1;
218 $bank_transactions = [ sort { ($a->{agreement} <=> $b->{agreement}) * $dir } @{ $bank_transactions } ];
221 return ( $bank_transactions , \@proposals );
227 if (!$::form->{filter}{bank_account}) {
228 flash('error', t8('No bank account chosen!'));
229 $self->action_search;
233 my $bank_account = SL::DB::BankAccount->load_cached($::form->{filter}->{bank_account});
234 my $fromdate = $::locale->parse_date_to_object($::form->{filter}->{fromdate});
235 my $todate = $::locale->parse_date_to_object($::form->{filter}->{todate});
236 $todate->add( days => 1 ) if $todate;
238 my ($bank_transactions, $proposals) = $self->gather_bank_transactions_and_proposals(
239 bank_account => $bank_account,
240 fromdate => $fromdate,
242 sort_by => $::form->{sort_by},
243 sort_dir => $::form->{sort_dir},
246 $::request->layout->add_javascripts("kivi.BankTransaction.js");
247 $self->render('bank_transactions/list',
248 title => t8('Bank transactions MT940'),
249 BANK_TRANSACTIONS => $bank_transactions,
250 PROPOSALS => $proposals,
251 bank_account => $bank_account,
252 ui_tab => scalar(@{ $proposals }) > 0 ? 1 : 0,
256 sub action_assign_invoice {
259 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
261 $self->render('bank_transactions/assign_invoice',
263 title => t8('Assign invoice'),);
266 sub action_create_invoice {
268 my %myconfig = %main::myconfig;
270 $self->transaction(SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id}));
272 my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(iban => $self->transaction->{remote_account_number});
273 my $use_vendor_filter = $self->transaction->{remote_account_number} && $vendor_of_transaction;
275 my $templates_ap = SL::DB::Manager::RecordTemplate->get_all(
276 where => [ template_type => 'ap_transaction' ],
277 with_objects => [ qw(employee vendor) ],
279 my $templates_gl = SL::DB::Manager::RecordTemplate->get_all(
280 query => [ template_type => 'gl_transaction',
281 chart_id => SL::DB::Manager::BankAccount->find_by(id => $self->transaction->local_bank_account_id)->chart_id,
283 sort_by => [ qw(template_name) ],
284 with_objects => [ qw(employee record_template_items) ],
287 # pre filter templates_ap, if we have a vendor match (IBAN eq IBAN) - show and allow user to edit this via gui!
288 $templates_ap = [ grep { $_->vendor_id == $vendor_of_transaction->id } @{ $templates_ap } ] if $use_vendor_filter;
290 $self->callback($self->url_for(
292 'filter.bank_account' => $::form->{filter}->{bank_account},
293 'filter.todate' => $::form->{filter}->{todate},
294 'filter.fromdate' => $::form->{filter}->{fromdate},
298 'bank_transactions/create_invoice',
300 title => t8('Create invoice'),
301 TEMPLATES_GL => $use_vendor_filter && @{ $templates_ap } ? undef : $templates_gl,
302 TEMPLATES_AP => $templates_ap,
303 vendor_name => $use_vendor_filter && @{ $templates_ap } ? $vendor_of_transaction->name : undef,
307 sub action_ajax_payment_suggestion {
310 # based on a BankTransaction ID and a Invoice or PurchaseInvoice ID passed via $::form,
311 # create an HTML blob to be used by the js function add_invoices in templates/webpages/bank_transactions/list.html
312 # and return encoded as JSON
314 croak("Need bt_id") unless $::form->{bt_id};
316 my $invoice = SL::DB::Manager::Invoice->find_by( id => $::form->{prop_id} ) || SL::DB::Manager::PurchaseInvoice->find_by( id => $::form->{prop_id} );
318 croak("No valid invoice found") unless $invoice;
320 my $html = $self->render(
321 'bank_transactions/_payment_suggestion', { output => 0 },
322 bt_id => $::form->{bt_id},
326 $self->render(\ SL::JSON::to_json( { 'html' => "$html" } ), { layout => 0, type => 'json', process => 0 });
329 sub action_filter_templates {
332 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
334 my (@filter, @filter_ap);
336 # filter => gl and ap | filter_ap = ap (i.e. vendorname)
337 push @filter, ('template_name' => { ilike => '%' . $::form->{template} . '%' }) if $::form->{template};
338 push @filter, ('reference' => { ilike => '%' . $::form->{reference} . '%' }) if $::form->{reference};
339 push @filter_ap, ('vendor.name' => { ilike => '%' . $::form->{vendor} . '%' }) if $::form->{vendor};
340 push @filter_ap, @filter;
341 my $templates_gl = SL::DB::Manager::RecordTemplate->get_all(
342 query => [ template_type => 'gl_transaction',
343 chart_id => SL::DB::Manager::BankAccount->find_by(id => $self->transaction->local_bank_account_id)->chart_id,
344 (and => \@filter) x !!@filter
346 with_objects => [ qw(employee record_template_items) ],
349 my $templates_ap = SL::DB::Manager::RecordTemplate->get_all(
350 where => [ template_type => 'ap_transaction', (and => \@filter_ap) x !!@filter_ap ],
351 with_objects => [ qw(employee vendor) ],
353 $::form->{filter} //= {};
355 $self->callback($self->url_for(
357 'filter.bank_account' => $::form->{filter}->{bank_account},
358 'filter.todate' => $::form->{filter}->{todate},
359 'filter.fromdate' => $::form->{filter}->{fromdate},
362 my $output = $self->render(
363 'bank_transactions/_template_list',
365 TEMPLATES_AP => $templates_ap,
366 TEMPLATES_GL => $templates_gl,
369 $self->render(\to_json({ html => $output }), { type => 'json', process => 0 });
372 sub action_ajax_add_list {
375 my @where_sale = (amount => { ne => \'paid' });
376 my @where_purchase = (amount => { ne => \'paid' });
378 if ($::form->{invnumber}) {
379 push @where_sale, (invnumber => { ilike => like($::form->{invnumber})});
380 push @where_purchase, (invnumber => { ilike => like($::form->{invnumber})});
383 if ($::form->{amount}) {
384 push @where_sale, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
385 push @where_purchase, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
388 if ($::form->{vcnumber}) {
389 push @where_sale, ('customer.customernumber' => { ilike => like($::form->{vcnumber})});
390 push @where_purchase, ('vendor.vendornumber' => { ilike => like($::form->{vcnumber})});
393 if ($::form->{vcname}) {
394 push @where_sale, ('customer.name' => { ilike => like($::form->{vcname})});
395 push @where_purchase, ('vendor.name' => { ilike => like($::form->{vcname})});
398 if ($::form->{transdatefrom}) {
399 my $fromdate = $::locale->parse_date_to_object($::form->{transdatefrom});
400 if ( ref($fromdate) eq 'DateTime' ) {
401 push @where_sale, ('transdate' => { ge => $fromdate});
402 push @where_purchase, ('transdate' => { ge => $fromdate});
406 if ($::form->{transdateto}) {
407 my $todate = $::locale->parse_date_to_object($::form->{transdateto});
408 if ( ref($todate) eq 'DateTime' ) {
409 $todate->add(days => 1);
410 push @where_sale, ('transdate' => { lt => $todate});
411 push @where_purchase, ('transdate' => { lt => $todate});
415 my $all_open_ar_invoices = SL::DB::Manager::Invoice ->get_all(where => \@where_sale, with_objects => 'customer');
416 my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => \@where_purchase, with_objects => 'vendor');
418 my @all_open_invoices = @{ $all_open_ar_invoices };
419 # add ap invoices, filtering out subcent open amounts
420 push @all_open_invoices, grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
422 @all_open_invoices = sort { $a->id <=> $b->id } @all_open_invoices;
424 my $output = $self->render(
425 'bank_transactions/add_list',
427 INVOICES => \@all_open_invoices,
430 my %result = ( count => 0, html => $output );
432 $self->render(\to_json(\%result), { type => 'json', process => 0 });
435 sub action_ajax_accept_invoices {
438 my @selected_invoices;
439 foreach my $invoice_id (@{ $::form->{invoice_id} || [] }) {
440 my $invoice_object = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
441 push @selected_invoices, $invoice_object;
445 'bank_transactions/invoices',
447 INVOICES => \@selected_invoices,
448 bt_id => $::form->{bt_id},
455 return 0 if !$::form->{invoice_ids};
457 my %invoice_hash = %{ delete $::form->{invoice_ids} }; # each key (the bt line with a bt_id) contains an array of invoice_ids
459 # e.g. three partial payments with bt_ids 54, 55 and 56 for invoice with id 74:
472 # or if the payment with bt_id 44 is used to pay invoices with ids 50, 51 and 52
474 # '44' => [ '50', '51', 52' ]
477 $::form->{invoice_skontos} ||= {}; # hash of arrays containing the payment types, could be empty
479 # a bank_transaction may be assigned to several invoices, i.e. a customer
480 # might pay several open invoices with one transaction
486 if ( $::form->{proposal_ids} ) {
487 foreach (@{ $::form->{proposal_ids} }) {
488 my $bank_transaction_id = $_;
489 my $invoice_ids = $invoice_hash{$_};
490 push @{ $self->problems }, $self->save_single_bank_transaction(
491 bank_transaction_id => $bank_transaction_id,
492 invoice_ids => $invoice_ids,
493 sources => ($::form->{sources} // {})->{$_},
494 memos => ($::form->{memos} // {})->{$_},
496 $count += scalar( @{$invoice_ids} );
499 while ( my ($bank_transaction_id, $invoice_ids) = each(%invoice_hash) ) {
500 push @{ $self->problems }, $self->save_single_bank_transaction(
501 bank_transaction_id => $bank_transaction_id,
502 invoice_ids => $invoice_ids,
503 sources => [ map { $::form->{"sources_${bank_transaction_id}_${_}"} } @{ $invoice_ids } ],
504 memos => [ map { $::form->{"memos_${bank_transaction_id}_${_}"} } @{ $invoice_ids } ],
506 $count += scalar( @{$invoice_ids} );
509 my $max_count = $count;
510 foreach (@{ $self->problems }) {
511 $count-- if $_->{result} eq 'error';
513 return ($count, $max_count);
516 sub action_save_invoices {
518 my ($success_count, $max_count) = $self->save_invoices();
520 if ($success_count == $max_count) {
521 flash('ok', t8('#1 invoice(s) saved.', $success_count));
523 flash('error', t8('At least #1 invoice(s) not saved', $max_count - $success_count));
526 $self->action_list();
529 sub action_save_proposals {
532 if ( $::form->{proposal_ids} ) {
533 my $propcount = scalar(@{ $::form->{proposal_ids} });
534 if ( $propcount > 0 ) {
535 my $count = $self->save_invoices();
537 flash('ok', t8('#1 proposal(s) with #2 invoice(s) saved.', $propcount, $count));
540 $self->action_list();
544 sub save_single_bank_transaction {
545 my ($self, %params) = @_;
549 bank_transaction => SL::DB::Manager::BankTransaction->find_by(id => $params{bank_transaction_id}),
553 if (!$data{bank_transaction}) {
557 message => $::locale->text('The ID #1 is not a valid database ID.', $data{bank_transaction_id}),
561 my $bank_transaction = $data{bank_transaction};
563 if ($bank_transaction->closed_period) {
567 message => $::locale->text('Cannot post payment for a closed period!'),
573 my $bt_id = $data{bank_transaction_id};
574 my $sign = $bank_transaction->amount < 0 ? -1 : 1;
575 my $payment_received = $bank_transaction->amount > 0;
576 my $payment_sent = $bank_transaction->amount < 0;
579 foreach my $invoice_id (@{ $params{invoice_ids} }) {
580 my $invoice = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
585 message => $::locale->text("The ID #1 is not a valid database ID.", $invoice_id),
588 push @{ $data{invoices} }, $invoice;
591 if ( $payment_received
592 && any { ( $_->is_sales && ($_->amount < 0))
593 || (!$_->is_sales && ($_->amount > 0))
594 } @{ $data{invoices} }) {
598 message => $::locale->text("Received payments can only be posted for sales invoices and purchase credit notes."),
603 && any { ( $_->is_sales && ($_->amount > 0))
604 || (!$_->is_sales && ($_->amount < 0) && ($_->invoice_type eq 'purchase_invoice'))
605 } @{ $data{invoices} }) {
609 message => $::locale->text("Sent payments can only be posted for purchase invoices and sales credit notes."),
613 my $max_invoices = scalar(@{ $data{invoices} });
616 foreach my $invoice (@{ $data{invoices} }) {
617 my $source = ($data{sources} // [])->[$n_invoices];
618 my $memo = ($data{memos} // [])->[$n_invoices];
621 # safety check invoice open
622 croak("Invoice closed. Cannot proceed.") unless ($invoice->open_amount);
624 if ( ($payment_sent && $bank_transaction->not_assigned_amount >= 0)
625 || ($payment_received && $bank_transaction->not_assigned_amount <= 0)) {
629 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."),
633 my ($payment_type, $free_skonto_amount);
634 if ( defined $::form->{invoice_skontos}->{"$bt_id"} ) {
635 $payment_type = shift(@{ $::form->{invoice_skontos}->{"$bt_id"} });
637 $payment_type = 'without_skonto';
640 if ($payment_type eq 'free_skonto') {
641 # parse user input > 0
642 if ($::form->parse_amount(\%::myconfig, $::form->{"free_skonto_amount"}->{"$bt_id"}{$invoice->id}) > 0) {
643 $free_skonto_amount = $::form->parse_amount(\%::myconfig, $::form->{"free_skonto_amount"}->{"$bt_id"}{$invoice->id});
648 message => $::locale->text("Free skonto amount has to be a positive number."),
653 # TODO rewrite this: really booked amount should be a return value of Payment.pm
654 # also this controller shouldnt care about how to calc skonto. we simply delegate the
655 # payment_type to the helper and get the corresponding bank_transaction values back
656 # hotfix to get the signs right - compare absolute values and later set the signs
657 # should be better done elsewhere - changing not_assigned_amount to abs feels seriously bogus
659 my $open_amount = $payment_type eq 'with_skonto_pt' ? $invoice->amount_less_skonto : $invoice->open_amount;
660 $open_amount = abs($open_amount);
661 $open_amount -= $free_skonto_amount if ($payment_type eq 'free_skonto');
662 my $not_assigned_amount = abs($bank_transaction->not_assigned_amount);
663 my $amount_for_booking = ($open_amount < $not_assigned_amount) ? $open_amount : $not_assigned_amount;
664 my $amount_for_payment = $amount_for_booking;
666 # get the right direction for the payment bookings (all amounts < 0 are stornos, credit notes or negative ap)
667 $amount_for_payment *= -1 if $invoice->amount < 0;
668 $free_skonto_amount *= -1 if ($free_skonto_amount && $invoice->amount < 0);
669 # get the right direction for the bank transaction
670 $amount_for_booking *= $sign;
672 $bank_transaction->invoice_amount($bank_transaction->invoice_amount + $amount_for_booking);
674 # ... and then pay the invoice
675 my @acc_ids = $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
676 trans_id => $invoice->id,
677 amount => $amount_for_payment,
678 payment_type => $payment_type,
681 skonto_amount => $free_skonto_amount,
682 transdate => $bank_transaction->valutadate->to_kivitendo);
683 # ... and record the origin via BankTransactionAccTrans
684 if (scalar(@acc_ids) < 2) {
688 message => $::locale->text("Unable to book transactions for bank purpose #1", $bank_transaction->purpose),
691 foreach my $acc_trans_id (@acc_ids) {
692 my $id_type = $invoice->is_sales ? 'ar' : 'ap';
694 acc_trans_id => $acc_trans_id,
695 bank_transaction_id => $bank_transaction->id,
696 $id_type => $invoice->id,
698 SL::DB::BankTransactionAccTrans->new(%props_acc)->save;
700 # Record a record link from the bank transaction to the invoice
702 from_table => 'bank_transactions',
704 to_table => $invoice->is_sales ? 'ar' : 'ap',
705 to_id => $invoice->id,
707 SL::DB::RecordLink->new(%props)->save;
709 # "close" a sepa_export_item if it exists
710 # code duplicated in action_save_proposals!
711 # currently only works, if there is only exactly one open sepa_export_item
712 if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
713 if ( scalar @$seis == 1 ) {
714 # moved the execution and the check for sepa_export into a method,
715 # this isn't part of a transaction, though
716 $seis->[0]->set_executed if $invoice->id == $seis->[0]->arap_id;
721 $bank_transaction->save;
723 # 'undef' means 'no error' here.
728 my $rez = $data{bank_transaction}->db->with_transaction(sub {
730 $error = $worker->();
741 # Rollback Fehler nicht weiterreichen
743 # aber einen rollback von hand
744 $::lxdebug->message(LXDebug->DEBUG2(),"finish worker with ". ($error ? $error->{result} : '-'));
745 $data{bank_transaction}->db->dbh->rollback if $error && $error->{result} eq 'error';
748 return grep { $_ } ($error, @warnings);
750 sub action_unlink_bank_transaction {
751 my ($self, %params) = @_;
753 croak("No bank transaction ids") unless scalar @{ $::form->{ids}} > 0;
757 foreach my $bt_id (@{ $::form->{ids}} ) {
759 my $bank_transaction = SL::DB::Manager::BankTransaction->find_by(id => $bt_id);
760 croak("No valid bank transaction found") unless (ref($bank_transaction) eq 'SL::DB::BankTransaction');
761 croak t8('Cannot unlink payment for a closed period!') if $bank_transaction->closed_period;
763 # everything in one transaction
764 my $rez = $bank_transaction->db->with_transaction(sub {
765 # 1. remove all reconciliations (due to underlying trigger, this has to be the first step)
766 my $rec_links = SL::DB::Manager::ReconciliationLink->get_all(where => [ bank_transaction_id => $bt_id ]);
767 $_->delete for @{ $rec_links };
770 foreach my $acc_trans_id_entry (@{ SL::DB::Manager::BankTransactionAccTrans->get_all(where => [bank_transaction_id => $bt_id ] )}) {
772 my $acc_trans = SL::DB::Manager::AccTransaction->get_all(where => [acc_trans_id => $acc_trans_id_entry->acc_trans_id]);
774 # save trans_id and type
775 die "no type" unless ($acc_trans_id_entry->ar_id || $acc_trans_id_entry->ap_id || $acc_trans_id_entry->gl_id);
776 $trans_ids{$acc_trans_id_entry->ar_id} = 'ar' if $acc_trans_id_entry->ar_id;
777 $trans_ids{$acc_trans_id_entry->ap_id} = 'ap' if $acc_trans_id_entry->ap_id;
778 $trans_ids{$acc_trans_id_entry->gl_id} = 'gl' if $acc_trans_id_entry->gl_id;
779 # 2. all good -> ready to delete acc_trans and bt_acc link
780 $acc_trans_id_entry->delete;
781 $_->delete for @{ $acc_trans };
783 # 3. update arap.paid (may not be 0, yet)
784 # or in case of gl, delete whole entry
785 while (my ($trans_id, $type) = each %trans_ids) {
787 SL::DB::Manager::GLTransaction->delete_all(where => [ id => $trans_id ]);
790 die ("invalid type") unless $type =~ m/^(ar|ap)$/;
792 # recalc and set paid via database query
793 my $query = qq|UPDATE $type SET paid =
794 (SELECT COALESCE(abs(sum(amount)),0) FROM acc_trans
796 AND chart_link ilike '%paid%')
799 die if (do_query($::form, $bank_transaction->db->dbh, $query, $trans_id, $trans_id) == -1);
801 # 4. and delete all (if any) record links
802 my $rl = SL::DB::Manager::RecordLink->delete_all(where => [ from_id => $bt_id, from_table => 'bank_transactions' ]);
804 # 5. finally reset this bank transaction
805 $bank_transaction->invoice_amount(0);
806 $bank_transaction->cleared(0);
807 $bank_transaction->save;
808 # 6. and add a log entry in history_erp
809 SL::DB::History->new(
810 trans_id => $bank_transaction->id,
811 snumbers => 'bank_transaction_unlink_' . $bank_transaction->id,
812 employee_id => SL::DB::Manager::Employee->current->id,
813 what_done => 'bank_transaction',
814 addition => 'UNLINKED',
819 }) || die t8('error while unlinking payment #1 : ', $bank_transaction->purpose) . $bank_transaction->db->error . "\n";
824 flash('ok', t8('#1 bank transaction bookings undone.', $success_count));
825 $self->action_list_all() unless $params{testcase};
832 $::auth->assert('bank_transaction');
839 sub make_filter_summary {
842 my $filter = $::form->{filter} || {};
846 [ $filter->{"transdate:date::ge"}, $::locale->text('Transdate') . " " . $::locale->text('From Date') ],
847 [ $filter->{"transdate:date::le"}, $::locale->text('Transdate') . " " . $::locale->text('To Date') ],
848 [ $filter->{"valutadate:date::ge"}, $::locale->text('Valutadate') . " " . $::locale->text('From Date') ],
849 [ $filter->{"valutadate:date::le"}, $::locale->text('Valutadate') . " " . $::locale->text('To Date') ],
850 [ $filter->{"amount:number"}, $::locale->text('Amount') ],
851 [ $filter->{"bank_account_id:integer"}, $::locale->text('Local bank account') ],
852 [ $filter->{"remote_name:substr::ilike"}, $::locale->text('Remote name') ],
856 push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
859 $self->{filter_summary} = join ', ', @filter_strings;
865 my $callback = $self->models->get_callback;
867 my $report = SL::ReportGenerator->new(\%::myconfig, $::form);
868 $self->{report} = $report;
870 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);
871 my @sortable = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount purpose local_account_number local_bank_code);
874 ids => { raw_header_data => checkbox_tag("", id => "check_all", checkall => "[data-checkall=1]"),
876 raw_data => sub { if (@{ $_[0]->linked_invoices }) {
877 if ($_[0]->closed_period) {
878 html_tag('text', "X"); #, tooltip => t8('Bank Transaction is in a closed period.')),
880 checkbox_tag("ids[]", value => $_[0]->id, "data-checkall" => 1);
883 transdate => { sub => sub { $_[0]->transdate_as_date } },
884 valutadate => { sub => sub { $_[0]->valutadate_as_date } },
886 remote_account_number => { },
887 remote_bank_code => { },
888 amount => { sub => sub { $_[0]->amount_as_number },
890 invoice_amount => { sub => sub { $_[0]->invoice_amount_as_number },
892 invoices => { sub => sub { my @invnumbers; for my $obj (@{ $_[0]->linked_invoices }) {
893 next unless $obj; push @invnumbers, $obj->invnumber } return \@invnumbers } },
894 currency => { sub => sub { $_[0]->currency->name } },
896 local_account_number => { sub => sub { $_[0]->local_bank_account->account_number } },
897 local_bank_code => { sub => sub { $_[0]->local_bank_account->bank_code } },
898 local_bank_name => { sub => sub { $_[0]->local_bank_account->name } },
902 map { $column_defs{$_}->{text} ||= $::locale->text( $self->models->get_sort_spec->{$_}->{title} ) } keys %column_defs;
904 $report->set_options(
905 std_column_visibility => 1,
906 controller_class => 'BankTransaction',
907 output_format => 'HTML',
908 top_info_text => $::locale->text('Bank transactions'),
909 title => $::locale->text('Bank transactions'),
910 allow_pdf_export => 1,
911 allow_csv_export => 1,
913 $report->set_columns(%column_defs);
914 $report->set_column_order(@columns);
915 $report->set_export_options(qw(list_all filter));
916 $report->set_options_from_form;
917 $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
918 $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
920 my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted();
922 $report->set_options(
923 raw_top_info_text => $self->render('bank_transactions/report_top', { output => 0 }, BANK_ACCOUNTS => $bank_accounts),
924 raw_bottom_info_text => $self->render('bank_transactions/report_bottom', { output => 0 }),
928 sub init_problems { [] }
933 SL::Controller::Helper::GetModels->new(
938 dir => 0, # 1 = ASC, 0 = DESC : default sort is newest at top
941 transdate => t8('Transdate'),
942 remote_name => t8('Remote name'),
943 amount => t8('Amount'),
944 invoice_amount => t8('Assigned'),
945 invoices => t8('Linked invoices'),
946 valutadate => t8('Valutadate'),
947 remote_account_number => t8('Remote account number'),
948 remote_bank_code => t8('Remote bank code'),
949 currency => t8('Currency'),
950 purpose => t8('Purpose'),
951 local_account_number => t8('Local account number'),
952 local_bank_code => t8('Local bank code'),
953 local_bank_name => t8('Bank account'),
955 with_objects => [ 'local_bank_account', 'currency' ],
959 sub load_ap_record_template_url {
960 my ($self, $template) = @_;
962 return $self->url_for(
963 controller => 'ap.pl',
964 action => 'load_record_template',
966 'form_defaults.amount_1' => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
967 'form_defaults.transdate' => $self->transaction->transdate_as_date,
968 'form_defaults.duedate' => $self->transaction->transdate_as_date,
969 'form_defaults.no_payment_bookings' => 1,
970 'form_defaults.paid_1_suggestion' => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
971 'form_defaults.AP_paid_1_suggestion' => $self->transaction->local_bank_account->chart->accno,
972 'form_defaults.callback' => $self->callback,
976 sub load_gl_record_template_url {
977 my ($self, $template) = @_;
979 return $self->url_for(
980 controller => 'gl.pl',
981 action => 'load_record_template',
983 'form_defaults.amount_1' => abs($self->transaction->not_assigned_amount), # always positive
984 'form_defaults.transdate' => $self->transaction->transdate_as_date,
985 'form_defaults.callback' => $self->callback,
986 'form_defaults.bt_id' => $self->transaction->id,
987 'form_defaults.bt_chart_id' => $self->transaction->local_bank_account->chart->id,
988 'form_defaults.description' => $self->transaction->purpose,
992 sub setup_search_action_bar {
993 my ($self, %params) = @_;
995 for my $bar ($::request->layout->get('actionbar')) {
999 submit => [ '#search_form', { action => 'BankTransaction/list' } ],
1000 accesskey => 'enter',
1006 sub setup_list_all_action_bar {
1007 my ($self, %params) = @_;
1009 for my $bar ($::request->layout->get('actionbar')) {
1012 action => [ t8('Actions') ],
1014 t8('Unlink bank transactions'),
1015 submit => [ '#form', { action => 'BankTransaction/unlink_bank_transaction' } ],
1016 checks => [ [ 'kivi.check_if_entries_selected', '[name="ids[]"]' ] ],
1017 disabled => $::instance_conf->get_payments_changeable ? t8('Cannot safely unlink bank transactions, please set the posting configuration for payments to unchangeable.') : undef,
1022 submit => [ '#filter_form', { action => 'BankTransaction/list_all' } ],
1023 accesskey => 'enter',
1038 SL::Controller::BankTransaction - Posting payments to invoices from
1039 bank transactions imported earlier
1045 =item C<save_single_bank_transaction %params>
1047 Takes a bank transaction ID (as parameter C<bank_transaction_id> and
1048 tries to post its amount to a certain number of invoices (parameter
1049 C<invoice_ids>, an array ref of database IDs to purchase or sales
1052 This method handles already partly assigned bank transactions.
1054 This method cannot handle already partly assigned bank transactions, i.e.
1055 a bank transaction that has a invoice_amount <> 0 but not the fully
1056 transaction amount (invoice_amount == amount).
1058 If the amount of the bank transaction is higher than the sum of
1059 the assigned invoices (1 .. n) the bank transaction will only be
1062 The whole function is wrapped in a database transaction. If an
1063 exception occurs the bank transaction is not posted at all. The same
1064 is true if the code detects an error during the execution, e.g. a bank
1065 transaction that's already been posted earlier. In both cases the
1066 database transaction will be rolled back.
1068 If warnings but not errors occur the database transaction is still
1071 The return value is an error object or C<undef> if the function
1072 succeeded. The calling function will collect all warnings and errors
1073 and display them in a nicely formatted table if any occurred.
1075 An error object is a hash reference containing the following members:
1079 =item * C<result> — can be either C<warning> or C<error>. Warnings are
1080 displayed slightly different than errors.
1082 =item * C<message> — a human-readable message included in the list of
1083 errors meant as the description of why the problem happened
1085 =item * C<bank_transaction_id>, C<invoice_ids> — the same parameters
1086 that the function was called with
1088 =item * C<bank_transaction> — the database object
1089 (C<SL::DB::BankTransaction>) corresponding to C<bank_transaction_id>
1091 =item * C<invoices> — an array ref of the database objects (either
1092 C<SL::DB::Invoice> or C<SL::DB::PurchaseInvoice>) corresponding to
1097 =item C<action_unlink_bank_transaction>
1099 Takes one or more bank transaction ID (as parameter C<form::ids>) and
1100 tries to revert all payment bookings including already cleared bookings.
1102 This method won't undo payments that are in a closed period and assumes
1103 that payments are not manually changed, i.e. only imported payments.
1105 GL-records will be deleted completely if a bank transaction was the source.
1107 TODO: we still rely on linked_records for the check boxes
1113 Niclas Zimmermann E<lt>niclas@kivitendo-premium.deE<gt>,
1114 Geoffrey Richardson E<lt>information@richardson-bueren.deE<gt>