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 !!
167 next unless $bt->{remote_name}; # bank has no name, usually fees, use create invoice to assign
170 # try to match the current $bt to each of the open_invoices, saving the
171 # results of get_agreement_with_invoice in $open_invoice->{agreement} and
172 # $open_invoice->{rule_matches}.
174 # The values are overwritten each time a new bt is checked, so at the end
175 # of each bt the likely results are filtered and those values are stored in
176 # the arrays $bt->{proposals} and $bt->{rule_matches}, and the agreement
177 # score is stored in $bt->{agreement}
179 foreach my $open_invoice (@all_open_invoices) {
180 ($open_invoice->{agreement}, $open_invoice->{rule_matches}) = $bt->get_agreement_with_invoice($open_invoice,
181 sepa_export_items => $all_open_sepa_export_items,
183 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,
184 $open_invoice->amount * ($open_invoice->{is_ar} ? 1 : -1), 2);
188 my $min_agreement = 3; # suggestions must have at least this score
190 my $max_agreement = max map { $_->{agreement} } @all_open_invoices;
192 # add open_invoices with highest agreement into array $bt->{proposals}
193 if ( $max_agreement >= $min_agreement ) {
194 $bt->{proposals} = [ grep { $_->{agreement} == $max_agreement } @all_open_invoices ];
195 $bt->{agreement} = $max_agreement; #scalar @{ $bt->{proposals} } ? $agreement + 1 : '';
197 # store the rule_matches in a separate array, so they can be displayed in template
198 foreach ( @{ $bt->{proposals} } ) {
199 push(@{$bt->{rule_matches}}, $_->{rule_matches});
205 # separate filter for proposals (second tab, agreement >= 5 and exactly one match)
206 # to qualify as a proposal there has to be
207 # * agreement >= 5 TODO: make threshold configurable in configuration
208 # * there must be only one exact match
209 my $proposal_threshold = 5;
210 my @otherproposals = grep {
211 ($_->{agreement} >= $proposal_threshold)
212 && (1 == scalar @{ $_->{proposals} })
213 } @{ $bank_transactions };
215 push @proposals, @otherproposals;
217 # sort bank transaction proposals by quality (score) of proposal
218 if ($params{sort_by} && $params{sort_by} eq 'proposal') {
219 my $dir = $params{sort_dir} ? 1 : -1;
220 $bank_transactions = [ sort { ($a->{agreement} <=> $b->{agreement}) * $dir } @{ $bank_transactions } ];
223 return ( $bank_transactions , \@proposals );
229 if (!$::form->{filter}{bank_account}) {
230 flash('error', t8('No bank account chosen!'));
231 $self->action_search;
235 my $bank_account = SL::DB::BankAccount->load_cached($::form->{filter}->{bank_account});
236 my $fromdate = $::locale->parse_date_to_object($::form->{filter}->{fromdate});
237 my $todate = $::locale->parse_date_to_object($::form->{filter}->{todate});
238 $todate->add( days => 1 ) if $todate;
240 my ($bank_transactions, $proposals) = $self->gather_bank_transactions_and_proposals(
241 bank_account => $bank_account,
242 fromdate => $fromdate,
244 sort_by => $::form->{sort_by},
245 sort_dir => $::form->{sort_dir},
248 $::request->layout->add_javascripts("kivi.BankTransaction.js");
249 $self->render('bank_transactions/list',
250 title => t8('Bank transactions MT940'),
251 BANK_TRANSACTIONS => $bank_transactions,
252 PROPOSALS => $proposals,
253 bank_account => $bank_account,
254 ui_tab => scalar(@{ $proposals }) > 0 ? 1 : 0,
258 sub action_assign_invoice {
261 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
263 $self->render('bank_transactions/assign_invoice',
265 title => t8('Assign invoice'),);
268 sub action_create_invoice {
270 my %myconfig = %main::myconfig;
272 $self->transaction(SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id}));
274 my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(iban => $self->transaction->{remote_account_number});
275 my $use_vendor_filter = $self->transaction->{remote_account_number} && $vendor_of_transaction;
277 my $templates_ap = SL::DB::Manager::RecordTemplate->get_all(
278 where => [ template_type => 'ap_transaction' ],
279 with_objects => [ qw(employee vendor) ],
281 my $templates_gl = SL::DB::Manager::RecordTemplate->get_all(
282 query => [ template_type => 'gl_transaction',
283 chart_id => SL::DB::Manager::BankAccount->find_by(id => $self->transaction->local_bank_account_id)->chart_id,
285 with_objects => [ qw(employee record_template_items) ],
288 # pre filter templates_ap, if we have a vendor match (IBAN eq IBAN) - show and allow user to edit this via gui!
289 $templates_ap = [ grep { $_->vendor_id == $vendor_of_transaction->id } @{ $templates_ap } ] if $use_vendor_filter;
291 $self->callback($self->url_for(
293 'filter.bank_account' => $::form->{filter}->{bank_account},
294 'filter.todate' => $::form->{filter}->{todate},
295 'filter.fromdate' => $::form->{filter}->{fromdate},
299 'bank_transactions/create_invoice',
301 title => t8('Create invoice'),
302 TEMPLATES_GL => $use_vendor_filter && @{ $templates_ap } ? undef : $templates_gl,
303 TEMPLATES_AP => $templates_ap,
304 vendor_name => $use_vendor_filter && @{ $templates_ap } ? $vendor_of_transaction->name : undef,
308 sub action_ajax_payment_suggestion {
311 # based on a BankTransaction ID and a Invoice or PurchaseInvoice ID passed via $::form,
312 # create an HTML blob to be used by the js function add_invoices in templates/webpages/bank_transactions/list.html
313 # and return encoded as JSON
315 croak("Need bt_id") unless $::form->{bt_id};
317 my $invoice = SL::DB::Manager::Invoice->find_by( id => $::form->{prop_id} ) || SL::DB::Manager::PurchaseInvoice->find_by( id => $::form->{prop_id} );
319 croak("No valid invoice found") unless $invoice;
321 my $html = $self->render(
322 'bank_transactions/_payment_suggestion', { output => 0 },
323 bt_id => $::form->{bt_id},
327 $self->render(\ SL::JSON::to_json( { 'html' => "$html" } ), { layout => 0, type => 'json', process => 0 });
330 sub action_filter_templates {
333 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
335 my (@filter, @filter_ap);
337 # filter => gl and ap | filter_ap = ap (i.e. vendorname)
338 push @filter, ('template_name' => { ilike => '%' . $::form->{template} . '%' }) if $::form->{template};
339 push @filter, ('reference' => { ilike => '%' . $::form->{reference} . '%' }) if $::form->{reference};
340 push @filter_ap, ('vendor.name' => { ilike => '%' . $::form->{vendor} . '%' }) if $::form->{vendor};
341 push @filter_ap, @filter;
342 my $templates_gl = SL::DB::Manager::RecordTemplate->get_all(
343 query => [ template_type => 'gl_transaction',
344 chart_id => SL::DB::Manager::BankAccount->find_by(id => $self->transaction->local_bank_account_id)->chart_id,
345 (and => \@filter) x !!@filter
347 with_objects => [ qw(employee record_template_items) ],
350 my $templates_ap = SL::DB::Manager::RecordTemplate->get_all(
351 where => [ template_type => 'ap_transaction', (and => \@filter_ap) x !!@filter_ap ],
352 with_objects => [ qw(employee vendor) ],
354 $::form->{filter} //= {};
356 $self->callback($self->url_for(
358 'filter.bank_account' => $::form->{filter}->{bank_account},
359 'filter.todate' => $::form->{filter}->{todate},
360 'filter.fromdate' => $::form->{filter}->{fromdate},
363 my $output = $self->render(
364 'bank_transactions/_template_list',
366 TEMPLATES_AP => $templates_ap,
367 TEMPLATES_GL => $templates_gl,
370 $self->render(\to_json({ html => $output }), { type => 'json', process => 0 });
373 sub action_ajax_add_list {
376 my @where_sale = (amount => { ne => \'paid' });
377 my @where_purchase = (amount => { ne => \'paid' });
379 if ($::form->{invnumber}) {
380 push @where_sale, (invnumber => { ilike => like($::form->{invnumber})});
381 push @where_purchase, (invnumber => { ilike => like($::form->{invnumber})});
384 if ($::form->{amount}) {
385 push @where_sale, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
386 push @where_purchase, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
389 if ($::form->{vcnumber}) {
390 push @where_sale, ('customer.customernumber' => { ilike => like($::form->{vcnumber})});
391 push @where_purchase, ('vendor.vendornumber' => { ilike => like($::form->{vcnumber})});
394 if ($::form->{vcname}) {
395 push @where_sale, ('customer.name' => { ilike => like($::form->{vcname})});
396 push @where_purchase, ('vendor.name' => { ilike => like($::form->{vcname})});
399 if ($::form->{transdatefrom}) {
400 my $fromdate = $::locale->parse_date_to_object($::form->{transdatefrom});
401 if ( ref($fromdate) eq 'DateTime' ) {
402 push @where_sale, ('transdate' => { ge => $fromdate});
403 push @where_purchase, ('transdate' => { ge => $fromdate});
407 if ($::form->{transdateto}) {
408 my $todate = $::locale->parse_date_to_object($::form->{transdateto});
409 if ( ref($todate) eq 'DateTime' ) {
410 $todate->add(days => 1);
411 push @where_sale, ('transdate' => { lt => $todate});
412 push @where_purchase, ('transdate' => { lt => $todate});
416 my $all_open_ar_invoices = SL::DB::Manager::Invoice ->get_all(where => \@where_sale, with_objects => 'customer');
417 my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => \@where_purchase, with_objects => 'vendor');
419 my @all_open_invoices = @{ $all_open_ar_invoices };
420 # add ap invoices, filtering out subcent open amounts
421 push @all_open_invoices, grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
423 @all_open_invoices = sort { $a->id <=> $b->id } @all_open_invoices;
425 my $output = $self->render(
426 'bank_transactions/add_list',
428 INVOICES => \@all_open_invoices,
431 my %result = ( count => 0, html => $output );
433 $self->render(\to_json(\%result), { type => 'json', process => 0 });
436 sub action_ajax_accept_invoices {
439 my @selected_invoices;
440 foreach my $invoice_id (@{ $::form->{invoice_id} || [] }) {
441 my $invoice_object = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
442 push @selected_invoices, $invoice_object;
446 'bank_transactions/invoices',
448 INVOICES => \@selected_invoices,
449 bt_id => $::form->{bt_id},
456 return 0 if !$::form->{invoice_ids};
458 my %invoice_hash = %{ delete $::form->{invoice_ids} }; # each key (the bt line with a bt_id) contains an array of invoice_ids
460 # e.g. three partial payments with bt_ids 54, 55 and 56 for invoice with id 74:
473 # or if the payment with bt_id 44 is used to pay invoices with ids 50, 51 and 52
475 # '44' => [ '50', '51', 52' ]
478 $::form->{invoice_skontos} ||= {}; # hash of arrays containing the payment types, could be empty
480 # a bank_transaction may be assigned to several invoices, i.e. a customer
481 # might pay several open invoices with one transaction
487 if ( $::form->{proposal_ids} ) {
488 foreach (@{ $::form->{proposal_ids} }) {
489 my $bank_transaction_id = $_;
490 my $invoice_ids = $invoice_hash{$_};
491 push @{ $self->problems }, $self->save_single_bank_transaction(
492 bank_transaction_id => $bank_transaction_id,
493 invoice_ids => $invoice_ids,
494 sources => ($::form->{sources} // {})->{$_},
495 memos => ($::form->{memos} // {})->{$_},
497 $count += scalar( @{$invoice_ids} );
500 while ( my ($bank_transaction_id, $invoice_ids) = each(%invoice_hash) ) {
501 push @{ $self->problems }, $self->save_single_bank_transaction(
502 bank_transaction_id => $bank_transaction_id,
503 invoice_ids => $invoice_ids,
504 sources => [ map { $::form->{"sources_${bank_transaction_id}_${_}"} } @{ $invoice_ids } ],
505 memos => [ map { $::form->{"memos_${bank_transaction_id}_${_}"} } @{ $invoice_ids } ],
507 $count += scalar( @{$invoice_ids} );
510 my $max_count = $count;
511 foreach (@{ $self->problems }) {
512 $count-- if $_->{result} eq 'error';
514 return ($count, $max_count);
517 sub action_save_invoices {
519 my ($success_count, $max_count) = $self->save_invoices();
521 if ($success_count == $max_count) {
522 flash('ok', t8('#1 invoice(s) saved.', $success_count));
524 flash('error', t8('At least #1 invoice(s) not saved', $max_count - $success_count));
527 $self->action_list();
530 sub action_save_proposals {
533 if ( $::form->{proposal_ids} ) {
534 my $propcount = scalar(@{ $::form->{proposal_ids} });
535 if ( $propcount > 0 ) {
536 my $count = $self->save_invoices();
538 flash('ok', t8('#1 proposal(s) with #2 invoice(s) saved.', $propcount, $count));
541 $self->action_list();
545 sub save_single_bank_transaction {
546 my ($self, %params) = @_;
550 bank_transaction => SL::DB::Manager::BankTransaction->find_by(id => $params{bank_transaction_id}),
554 if (!$data{bank_transaction}) {
558 message => $::locale->text('The ID #1 is not a valid database ID.', $data{bank_transaction_id}),
562 my $bank_transaction = $data{bank_transaction};
564 if ($bank_transaction->closed_period) {
568 message => $::locale->text('Cannot post payment for a closed period!'),
574 my $bt_id = $data{bank_transaction_id};
575 my $sign = $bank_transaction->amount < 0 ? -1 : 1;
576 my $payment_received = $bank_transaction->amount > 0;
577 my $payment_sent = $bank_transaction->amount < 0;
580 foreach my $invoice_id (@{ $params{invoice_ids} }) {
581 my $invoice = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
586 message => $::locale->text("The ID #1 is not a valid database ID.", $invoice_id),
589 push @{ $data{invoices} }, $invoice;
592 if ( $payment_received
593 && any { ( $_->is_sales && ($_->amount < 0))
594 || (!$_->is_sales && ($_->amount > 0))
595 } @{ $data{invoices} }) {
599 message => $::locale->text("Received payments can only be posted for sales invoices and purchase credit notes."),
604 && any { ( $_->is_sales && ($_->amount > 0))
605 || (!$_->is_sales && ($_->amount < 0) && ($_->invoice_type eq 'purchase_invoice'))
606 } @{ $data{invoices} }) {
610 message => $::locale->text("Sent payments can only be posted for purchase invoices and sales credit notes."),
614 my $max_invoices = scalar(@{ $data{invoices} });
617 foreach my $invoice (@{ $data{invoices} }) {
618 my $source = ($data{sources} // [])->[$n_invoices];
619 my $memo = ($data{memos} // [])->[$n_invoices];
622 # safety check invoice open
623 croak("Invoice closed. Cannot proceed.") unless ($invoice->open_amount);
625 if ( ($payment_sent && $bank_transaction->not_assigned_amount >= 0)
626 || ($payment_received && $bank_transaction->not_assigned_amount <= 0)) {
630 message => $::locale->text("A payment can only be posted for multiple invoices if the amount to post is equal to or bigger than the sum of the open amounts of the affected invoices."),
634 my ($payment_type, $free_skonto_amount);
635 if ( defined $::form->{invoice_skontos}->{"$bt_id"} ) {
636 $payment_type = shift(@{ $::form->{invoice_skontos}->{"$bt_id"} });
638 $payment_type = 'without_skonto';
641 if ($payment_type eq 'free_skonto') {
642 # parse user input > 0
643 if ($::form->parse_amount(\%::myconfig, $::form->{"free_skonto_amount"}->{"$bt_id"}{$invoice->id}) > 0) {
644 $free_skonto_amount = $::form->parse_amount(\%::myconfig, $::form->{"free_skonto_amount"}->{"$bt_id"}{$invoice->id});
649 message => $::locale->text("Free skonto amount has to be a positive number."),
654 # TODO rewrite this: really booked amount should be a return value of Payment.pm
655 # also this controller shouldnt care about how to calc skonto. we simply delegate the
656 # payment_type to the helper and get the corresponding bank_transaction values back
657 # hotfix to get the signs right - compare absolute values and later set the signs
658 # should be better done elsewhere - changing not_assigned_amount to abs feels seriously bogus
660 my $open_amount = $payment_type eq 'with_skonto_pt' ? $invoice->amount_less_skonto : $invoice->open_amount;
661 $open_amount = abs($open_amount);
662 $open_amount -= $free_skonto_amount if ($payment_type eq 'free_skonto');
663 my $not_assigned_amount = abs($bank_transaction->not_assigned_amount);
664 my $amount_for_booking = ($open_amount < $not_assigned_amount) ? $open_amount : $not_assigned_amount;
665 my $amount_for_payment = $amount_for_booking;
667 # get the right direction for the payment bookings (all amounts < 0 are stornos, credit notes or negative ap)
668 $amount_for_payment *= -1 if $invoice->amount < 0;
669 $free_skonto_amount *= -1 if ($free_skonto_amount && $invoice->amount < 0);
670 # get the right direction for the bank transaction
671 $amount_for_booking *= $sign;
673 $bank_transaction->invoice_amount($bank_transaction->invoice_amount + $amount_for_booking);
675 # ... and then pay the invoice
676 my @acc_ids = $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
677 trans_id => $invoice->id,
678 amount => $amount_for_payment,
679 payment_type => $payment_type,
682 skonto_amount => $free_skonto_amount,
683 transdate => $bank_transaction->valutadate->to_kivitendo);
684 # ... and record the origin via BankTransactionAccTrans
685 if (scalar(@acc_ids) < 2) {
689 message => $::locale->text("Unable to book transactions for bank purpose #1", $bank_transaction->purpose),
692 foreach my $acc_trans_id (@acc_ids) {
693 my $id_type = $invoice->is_sales ? 'ar' : 'ap';
695 acc_trans_id => $acc_trans_id,
696 bank_transaction_id => $bank_transaction->id,
697 $id_type => $invoice->id,
699 SL::DB::BankTransactionAccTrans->new(%props_acc)->save;
701 # Record a record link from the bank transaction to the invoice
703 from_table => 'bank_transactions',
705 to_table => $invoice->is_sales ? 'ar' : 'ap',
706 to_id => $invoice->id,
708 SL::DB::RecordLink->new(%props)->save;
710 # "close" a sepa_export_item if it exists
711 # code duplicated in action_save_proposals!
712 # currently only works, if there is only exactly one open sepa_export_item
713 if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
714 if ( scalar @$seis == 1 ) {
715 # moved the execution and the check for sepa_export into a method,
716 # this isn't part of a transaction, though
717 $seis->[0]->set_executed if $invoice->id == $seis->[0]->arap_id;
722 $bank_transaction->save;
724 # 'undef' means 'no error' here.
729 my $rez = $data{bank_transaction}->db->with_transaction(sub {
731 $error = $worker->();
742 # Rollback Fehler nicht weiterreichen
744 # aber einen rollback von hand
745 $::lxdebug->message(LXDebug->DEBUG2(),"finish worker with ". ($error ? $error->{result} : '-'));
746 $data{bank_transaction}->db->dbh->rollback if $error && $error->{result} eq 'error';
749 return grep { $_ } ($error, @warnings);
751 sub action_unlink_bank_transaction {
752 my ($self, %params) = @_;
754 croak("No bank transaction ids") unless scalar @{ $::form->{ids}} > 0;
758 foreach my $bt_id (@{ $::form->{ids}} ) {
760 my $bank_transaction = SL::DB::Manager::BankTransaction->find_by(id => $bt_id);
761 croak("No valid bank transaction found") unless (ref($bank_transaction) eq 'SL::DB::BankTransaction');
762 croak t8('Cannot unlink payment for a closed period!') if $bank_transaction->closed_period;
764 # everything in one transaction
765 my $rez = $bank_transaction->db->with_transaction(sub {
766 # 1. remove all reconciliations (due to underlying trigger, this has to be the first step)
767 my $rec_links = SL::DB::Manager::ReconciliationLink->get_all(where => [ bank_transaction_id => $bt_id ]);
768 $_->delete for @{ $rec_links };
771 foreach my $acc_trans_id_entry (@{ SL::DB::Manager::BankTransactionAccTrans->get_all(where => [bank_transaction_id => $bt_id ] )}) {
773 my $acc_trans = SL::DB::Manager::AccTransaction->get_all(where => [acc_trans_id => $acc_trans_id_entry->acc_trans_id]);
775 # save trans_id and type
776 die "no type" unless ($acc_trans_id_entry->ar_id || $acc_trans_id_entry->ap_id || $acc_trans_id_entry->gl_id);
777 $trans_ids{$acc_trans_id_entry->ar_id} = 'ar' if $acc_trans_id_entry->ar_id;
778 $trans_ids{$acc_trans_id_entry->ap_id} = 'ap' if $acc_trans_id_entry->ap_id;
779 $trans_ids{$acc_trans_id_entry->gl_id} = 'gl' if $acc_trans_id_entry->gl_id;
780 # 2. all good -> ready to delete acc_trans and bt_acc link
781 $acc_trans_id_entry->delete;
782 $_->delete for @{ $acc_trans };
784 # 3. update arap.paid (may not be 0, yet)
785 # or in case of gl, delete whole entry
786 while (my ($trans_id, $type) = each %trans_ids) {
788 SL::DB::Manager::GLTransaction->delete_all(where => [ id => $trans_id ]);
791 die ("invalid type") unless $type =~ m/^(ar|ap)$/;
793 # recalc and set paid via database query
794 my $query = qq|UPDATE $type SET paid =
795 (SELECT COALESCE(abs(sum(amount)),0) FROM acc_trans
797 AND chart_link ilike '%paid%')
800 die if (do_query($::form, $bank_transaction->db->dbh, $query, $trans_id, $trans_id) == -1);
802 # 4. and delete all (if any) record links
803 my $rl = SL::DB::Manager::RecordLink->delete_all(where => [ from_id => $bt_id, from_table => 'bank_transactions' ]);
805 # 5. finally reset this bank transaction
806 $bank_transaction->invoice_amount(0);
807 $bank_transaction->cleared(0);
808 $bank_transaction->save;
809 # 6. and add a log entry in history_erp
810 SL::DB::History->new(
811 trans_id => $bank_transaction->id,
812 snumbers => 'bank_transaction_unlink_' . $bank_transaction->id,
813 employee_id => SL::DB::Manager::Employee->current->id,
814 what_done => 'bank_transaction',
815 addition => 'UNLINKED',
820 }) || die t8('error while unlinking payment #1 : ', $bank_transaction->purpose) . $bank_transaction->db->error . "\n";
825 flash('ok', t8('#1 bank transaction bookings undone.', $success_count));
826 $self->action_list_all() unless $params{testcase};
833 $::auth->assert('bank_transaction');
840 sub make_filter_summary {
843 my $filter = $::form->{filter} || {};
847 [ $filter->{"transdate:date::ge"}, $::locale->text('Transdate') . " " . $::locale->text('From Date') ],
848 [ $filter->{"transdate:date::le"}, $::locale->text('Transdate') . " " . $::locale->text('To Date') ],
849 [ $filter->{"valutadate:date::ge"}, $::locale->text('Valutadate') . " " . $::locale->text('From Date') ],
850 [ $filter->{"valutadate:date::le"}, $::locale->text('Valutadate') . " " . $::locale->text('To Date') ],
851 [ $filter->{"amount:number"}, $::locale->text('Amount') ],
852 [ $filter->{"bank_account_id:integer"}, $::locale->text('Local bank account') ],
853 [ $filter->{"remote_name:substr::ilike"}, $::locale->text('Remote name') ],
857 push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
860 $self->{filter_summary} = join ', ', @filter_strings;
866 my $callback = $self->models->get_callback;
868 my $report = SL::ReportGenerator->new(\%::myconfig, $::form);
869 $self->{report} = $report;
871 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);
872 my @sortable = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount purpose local_account_number local_bank_code);
875 ids => { raw_header_data => checkbox_tag("", id => "check_all", checkall => "[data-checkall=1]"),
877 raw_data => sub { if (@{ $_[0]->linked_invoices }) {
878 if ($_[0]->closed_period) {
879 html_tag('text', "X"); #, tooltip => t8('Bank Transaction is in a closed period.')),
881 checkbox_tag("ids[]", value => $_[0]->id, "data-checkall" => 1);
884 transdate => { sub => sub { $_[0]->transdate_as_date } },
885 valutadate => { sub => sub { $_[0]->valutadate_as_date } },
887 remote_account_number => { },
888 remote_bank_code => { },
889 amount => { sub => sub { $_[0]->amount_as_number },
891 invoice_amount => { sub => sub { $_[0]->invoice_amount_as_number },
893 invoices => { sub => sub { my @invnumbers; for my $obj (@{ $_[0]->linked_invoices }) {
894 next unless $obj; push @invnumbers, $obj->invnumber } return \@invnumbers } },
895 currency => { sub => sub { $_[0]->currency->name } },
897 local_account_number => { sub => sub { $_[0]->local_bank_account->account_number } },
898 local_bank_code => { sub => sub { $_[0]->local_bank_account->bank_code } },
899 local_bank_name => { sub => sub { $_[0]->local_bank_account->name } },
903 map { $column_defs{$_}->{text} ||= $::locale->text( $self->models->get_sort_spec->{$_}->{title} ) } keys %column_defs;
905 $report->set_options(
906 std_column_visibility => 1,
907 controller_class => 'BankTransaction',
908 output_format => 'HTML',
909 top_info_text => $::locale->text('Bank transactions'),
910 title => $::locale->text('Bank transactions'),
911 allow_pdf_export => 1,
912 allow_csv_export => 1,
914 $report->set_columns(%column_defs);
915 $report->set_column_order(@columns);
916 $report->set_export_options(qw(list_all filter));
917 $report->set_options_from_form;
918 $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
919 $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
921 my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted();
923 $report->set_options(
924 raw_top_info_text => $self->render('bank_transactions/report_top', { output => 0 }, BANK_ACCOUNTS => $bank_accounts),
925 raw_bottom_info_text => $self->render('bank_transactions/report_bottom', { output => 0 }),
929 sub init_problems { [] }
934 SL::Controller::Helper::GetModels->new(
939 dir => 0, # 1 = ASC, 0 = DESC : default sort is newest at top
942 transdate => t8('Transdate'),
943 remote_name => t8('Remote name'),
944 amount => t8('Amount'),
945 invoice_amount => t8('Assigned'),
946 invoices => t8('Linked invoices'),
947 valutadate => t8('Valutadate'),
948 remote_account_number => t8('Remote account number'),
949 remote_bank_code => t8('Remote bank code'),
950 currency => t8('Currency'),
951 purpose => t8('Purpose'),
952 local_account_number => t8('Local account number'),
953 local_bank_code => t8('Local bank code'),
954 local_bank_name => t8('Bank account'),
956 with_objects => [ 'local_bank_account', 'currency' ],
960 sub load_ap_record_template_url {
961 my ($self, $template) = @_;
963 return $self->url_for(
964 controller => 'ap.pl',
965 action => 'load_record_template',
967 'form_defaults.amount_1' => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
968 'form_defaults.transdate' => $self->transaction->transdate_as_date,
969 'form_defaults.duedate' => $self->transaction->transdate_as_date,
970 'form_defaults.no_payment_bookings' => 1,
971 'form_defaults.paid_1_suggestion' => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
972 'form_defaults.AP_paid_1_suggestion' => $self->transaction->local_bank_account->chart->accno,
973 'form_defaults.callback' => $self->callback,
977 sub load_gl_record_template_url {
978 my ($self, $template) = @_;
980 return $self->url_for(
981 controller => 'gl.pl',
982 action => 'load_record_template',
984 'form_defaults.amount_1' => abs($self->transaction->not_assigned_amount), # always positive
985 'form_defaults.transdate' => $self->transaction->transdate_as_date,
986 'form_defaults.callback' => $self->callback,
987 'form_defaults.bt_id' => $self->transaction->id,
988 'form_defaults.bt_chart_id' => $self->transaction->local_bank_account->chart->id,
989 'form_defaults.description' => $self->transaction->purpose,
993 sub setup_search_action_bar {
994 my ($self, %params) = @_;
996 for my $bar ($::request->layout->get('actionbar')) {
1000 submit => [ '#search_form', { action => 'BankTransaction/list' } ],
1001 accesskey => 'enter',
1007 sub setup_list_all_action_bar {
1008 my ($self, %params) = @_;
1010 for my $bar ($::request->layout->get('actionbar')) {
1013 action => [ t8('Actions') ],
1015 t8('Unlink bank transactions'),
1016 submit => [ '#form', { action => 'BankTransaction/unlink_bank_transaction' } ],
1017 checks => [ [ 'kivi.check_if_entries_selected', '[name="ids[]"]' ] ],
1018 disabled => $::instance_conf->get_payments_changeable ? t8('Cannot safely unlink bank transactions, please set the posting configuration for payments to unchangeable.') : undef,
1023 submit => [ '#filter_form', { action => 'BankTransaction/list_all' } ],
1024 accesskey => 'enter',
1039 SL::Controller::BankTransaction - Posting payments to invoices from
1040 bank transactions imported earlier
1046 =item C<save_single_bank_transaction %params>
1048 Takes a bank transaction ID (as parameter C<bank_transaction_id> and
1049 tries to post its amount to a certain number of invoices (parameter
1050 C<invoice_ids>, an array ref of database IDs to purchase or sales
1053 This method handles already partly assigned bank transactions.
1055 This method cannot handle already partly assigned bank transactions, i.e.
1056 a bank transaction that has a invoice_amount <> 0 but not the fully
1057 transaction amount (invoice_amount == amount).
1059 If the amount of the bank transaction is higher than the sum of
1060 the assigned invoices (1 .. n) the bank transaction will only be
1063 The whole function is wrapped in a database transaction. If an
1064 exception occurs the bank transaction is not posted at all. The same
1065 is true if the code detects an error during the execution, e.g. a bank
1066 transaction that's already been posted earlier. In both cases the
1067 database transaction will be rolled back.
1069 If warnings but not errors occur the database transaction is still
1072 The return value is an error object or C<undef> if the function
1073 succeeded. The calling function will collect all warnings and errors
1074 and display them in a nicely formatted table if any occurred.
1076 An error object is a hash reference containing the following members:
1080 =item * C<result> — can be either C<warning> or C<error>. Warnings are
1081 displayed slightly different than errors.
1083 =item * C<message> — a human-readable message included in the list of
1084 errors meant as the description of why the problem happened
1086 =item * C<bank_transaction_id>, C<invoice_ids> — the same parameters
1087 that the function was called with
1089 =item * C<bank_transaction> — the database object
1090 (C<SL::DB::BankTransaction>) corresponding to C<bank_transaction_id>
1092 =item * C<invoices> — an array ref of the database objects (either
1093 C<SL::DB::Invoice> or C<SL::DB::PurchaseInvoice>) corresponding to
1098 =item C<action_unlink_bank_transaction>
1100 Takes one or more bank transaction ID (as parameter C<form::ids>) and
1101 tries to revert all payment bookings including already cleared bookings.
1103 This method won't undo payments that are in a closed period and assumes
1104 that payments are not manually changed, i.e. only imported payments.
1106 GL-records will be deleted completely if a bank transaction was the source.
1108 TODO: we still rely on linked_records for the check boxes
1114 Niclas Zimmermann E<lt>niclas@kivitendo-premium.deE<gt>,
1115 Geoffrey Richardson E<lt>information@richardson-bueren.deE<gt>