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') ],
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>