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 sort_by => [ qw(template_name) ],
278 with_objects => [ qw(employee vendor) ],
280 my $templates_gl = SL::DB::Manager::RecordTemplate->get_all(
281 query => [ template_type => 'gl_transaction',
282 chart_id => SL::DB::Manager::BankAccount->find_by(id => $self->transaction->local_bank_account_id)->chart_id,
284 sort_by => [ qw(template_name) ],
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,
684 transdate => $bank_transaction->valutadate->to_kivitendo);
685 # ... and record the origin via BankTransactionAccTrans
686 if (scalar(@acc_ids) < 2) {
690 message => $::locale->text("Unable to book transactions for bank purpose #1", $bank_transaction->purpose),
693 foreach my $acc_trans_id (@acc_ids) {
694 my $id_type = $invoice->is_sales ? 'ar' : 'ap';
696 acc_trans_id => $acc_trans_id,
697 bank_transaction_id => $bank_transaction->id,
698 $id_type => $invoice->id,
700 SL::DB::BankTransactionAccTrans->new(%props_acc)->save;
702 # Record a record link from the bank transaction to the invoice
704 from_table => 'bank_transactions',
706 to_table => $invoice->is_sales ? 'ar' : 'ap',
707 to_id => $invoice->id,
709 SL::DB::RecordLink->new(%props)->save;
711 # "close" a sepa_export_item if it exists
712 # code duplicated in action_save_proposals!
713 # currently only works, if there is only exactly one open sepa_export_item
714 if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
715 if ( scalar @$seis == 1 ) {
716 # moved the execution and the check for sepa_export into a method,
717 # this isn't part of a transaction, though
718 $seis->[0]->set_executed if $invoice->id == $seis->[0]->arap_id;
723 $bank_transaction->save;
725 # 'undef' means 'no error' here.
730 my $rez = $data{bank_transaction}->db->with_transaction(sub {
732 $error = $worker->();
743 # Rollback Fehler nicht weiterreichen
745 # aber einen rollback von hand
746 $::lxdebug->message(LXDebug->DEBUG2(),"finish worker with ". ($error ? $error->{result} : '-'));
747 $data{bank_transaction}->db->dbh->rollback if $error && $error->{result} eq 'error';
750 return grep { $_ } ($error, @warnings);
752 sub action_unlink_bank_transaction {
753 my ($self, %params) = @_;
755 croak("No bank transaction ids") unless scalar @{ $::form->{ids}} > 0;
759 foreach my $bt_id (@{ $::form->{ids}} ) {
761 my $bank_transaction = SL::DB::Manager::BankTransaction->find_by(id => $bt_id);
762 croak("No valid bank transaction found") unless (ref($bank_transaction) eq 'SL::DB::BankTransaction');
763 croak t8('Cannot unlink payment for a closed period!') if $bank_transaction->closed_period;
765 # everything in one transaction
766 my $rez = $bank_transaction->db->with_transaction(sub {
767 # 1. remove all reconciliations (due to underlying trigger, this has to be the first step)
768 my $rec_links = SL::DB::Manager::ReconciliationLink->get_all(where => [ bank_transaction_id => $bt_id ]);
769 $_->delete for @{ $rec_links };
772 foreach my $acc_trans_id_entry (@{ SL::DB::Manager::BankTransactionAccTrans->get_all(where => [bank_transaction_id => $bt_id ] )}) {
774 my $acc_trans = SL::DB::Manager::AccTransaction->get_all(where => [acc_trans_id => $acc_trans_id_entry->acc_trans_id]);
776 # save trans_id and type
777 die "no type" unless ($acc_trans_id_entry->ar_id || $acc_trans_id_entry->ap_id || $acc_trans_id_entry->gl_id);
778 $trans_ids{$acc_trans_id_entry->ar_id} = 'ar' if $acc_trans_id_entry->ar_id;
779 $trans_ids{$acc_trans_id_entry->ap_id} = 'ap' if $acc_trans_id_entry->ap_id;
780 $trans_ids{$acc_trans_id_entry->gl_id} = 'gl' if $acc_trans_id_entry->gl_id;
781 # 2. all good -> ready to delete acc_trans and bt_acc link
782 $acc_trans_id_entry->delete;
783 $_->delete for @{ $acc_trans };
785 # 3. update arap.paid (may not be 0, yet)
786 # or in case of gl, delete whole entry
787 while (my ($trans_id, $type) = each %trans_ids) {
789 SL::DB::Manager::GLTransaction->delete_all(where => [ id => $trans_id ]);
792 die ("invalid type") unless $type =~ m/^(ar|ap)$/;
794 # recalc and set paid via database query
795 my $query = qq|UPDATE $type SET paid =
796 (SELECT COALESCE(abs(sum(amount)),0) FROM acc_trans
798 AND chart_link ilike '%paid%')
801 die if (do_query($::form, $bank_transaction->db->dbh, $query, $trans_id, $trans_id) == -1);
803 # 4. and delete all (if any) record links
804 my $rl = SL::DB::Manager::RecordLink->delete_all(where => [ from_id => $bt_id, from_table => 'bank_transactions' ]);
806 # 5. finally reset this bank transaction
807 $bank_transaction->invoice_amount(0);
808 $bank_transaction->cleared(0);
809 $bank_transaction->save;
810 # 6. and add a log entry in history_erp
811 SL::DB::History->new(
812 trans_id => $bank_transaction->id,
813 snumbers => 'bank_transaction_unlink_' . $bank_transaction->id,
814 employee_id => SL::DB::Manager::Employee->current->id,
815 what_done => 'bank_transaction',
816 addition => 'UNLINKED',
821 }) || die t8('error while unlinking payment #1 : ', $bank_transaction->purpose) . $bank_transaction->db->error . "\n";
826 flash('ok', t8('#1 bank transaction bookings undone.', $success_count));
827 $self->action_list_all() unless $params{testcase};
834 $::auth->assert('bank_transaction');
841 sub make_filter_summary {
844 my $filter = $::form->{filter} || {};
848 [ $filter->{"transdate:date::ge"}, $::locale->text('Transdate') . " " . $::locale->text('From Date') ],
849 [ $filter->{"transdate:date::le"}, $::locale->text('Transdate') . " " . $::locale->text('To Date') ],
850 [ $filter->{"valutadate:date::ge"}, $::locale->text('Valutadate') . " " . $::locale->text('From Date') ],
851 [ $filter->{"valutadate:date::le"}, $::locale->text('Valutadate') . " " . $::locale->text('To Date') ],
852 [ $filter->{"amount:number"}, $::locale->text('Amount') ],
853 [ $filter->{"bank_account_id:integer"}, $::locale->text('Local bank account') ],
854 [ $filter->{"remote_name:substr::ilike"}, $::locale->text('Remote name') ],
858 push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
861 $self->{filter_summary} = join ', ', @filter_strings;
867 my $callback = $self->models->get_callback;
869 my $report = SL::ReportGenerator->new(\%::myconfig, $::form);
870 $self->{report} = $report;
872 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);
873 my @sortable = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount purpose local_account_number local_bank_code);
876 ids => { raw_header_data => checkbox_tag("", id => "check_all", checkall => "[data-checkall=1]"),
878 raw_data => sub { if (@{ $_[0]->linked_invoices }) {
879 if ($_[0]->closed_period) {
880 html_tag('text', "X"); #, tooltip => t8('Bank Transaction is in a closed period.')),
882 checkbox_tag("ids[]", value => $_[0]->id, "data-checkall" => 1);
885 transdate => { sub => sub { $_[0]->transdate_as_date } },
886 valutadate => { sub => sub { $_[0]->valutadate_as_date } },
888 remote_account_number => { },
889 remote_bank_code => { },
890 amount => { sub => sub { $_[0]->amount_as_number },
892 invoice_amount => { sub => sub { $_[0]->invoice_amount_as_number },
894 invoices => { sub => sub { my @invnumbers; for my $obj (@{ $_[0]->linked_invoices }) {
895 next unless $obj; push @invnumbers, $obj->invnumber } return \@invnumbers } },
896 currency => { sub => sub { $_[0]->currency->name } },
898 local_account_number => { sub => sub { $_[0]->local_bank_account->account_number } },
899 local_bank_code => { sub => sub { $_[0]->local_bank_account->bank_code } },
900 local_bank_name => { sub => sub { $_[0]->local_bank_account->name } },
904 map { $column_defs{$_}->{text} ||= $::locale->text( $self->models->get_sort_spec->{$_}->{title} ) } keys %column_defs;
906 $report->set_options(
907 std_column_visibility => 1,
908 controller_class => 'BankTransaction',
909 output_format => 'HTML',
910 top_info_text => $::locale->text('Bank transactions'),
911 title => $::locale->text('Bank transactions'),
912 allow_pdf_export => 1,
913 allow_csv_export => 1,
915 $report->set_columns(%column_defs);
916 $report->set_column_order(@columns);
917 $report->set_export_options(qw(list_all filter));
918 $report->set_options_from_form;
919 $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
920 $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
922 my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted();
924 $report->set_options(
925 raw_top_info_text => $self->render('bank_transactions/report_top', { output => 0 }, BANK_ACCOUNTS => $bank_accounts),
926 raw_bottom_info_text => $self->render('bank_transactions/report_bottom', { output => 0 }),
930 sub init_problems { [] }
935 SL::Controller::Helper::GetModels->new(
940 dir => 0, # 1 = ASC, 0 = DESC : default sort is newest at top
943 transdate => t8('Transdate'),
944 remote_name => t8('Remote name'),
945 amount => t8('Amount'),
946 invoice_amount => t8('Assigned'),
947 invoices => t8('Linked invoices'),
948 valutadate => t8('Valutadate'),
949 remote_account_number => t8('Remote account number'),
950 remote_bank_code => t8('Remote bank code'),
951 currency => t8('Currency'),
952 purpose => t8('Purpose'),
953 local_account_number => t8('Local account number'),
954 local_bank_code => t8('Local bank code'),
955 local_bank_name => t8('Bank account'),
957 with_objects => [ 'local_bank_account', 'currency' ],
961 sub load_ap_record_template_url {
962 my ($self, $template) = @_;
964 return $self->url_for(
965 controller => 'ap.pl',
966 action => 'load_record_template',
968 'form_defaults.amount_1' => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
969 'form_defaults.transdate' => $self->transaction->transdate_as_date,
970 'form_defaults.duedate' => $self->transaction->transdate_as_date,
971 'form_defaults.no_payment_bookings' => 1,
972 'form_defaults.paid_1_suggestion' => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
973 'form_defaults.AP_paid_1_suggestion' => $self->transaction->local_bank_account->chart->accno,
974 'form_defaults.callback' => $self->callback,
978 sub load_gl_record_template_url {
979 my ($self, $template) = @_;
981 return $self->url_for(
982 controller => 'gl.pl',
983 action => 'load_record_template',
985 'form_defaults.amount_1' => abs($self->transaction->not_assigned_amount), # always positive
986 'form_defaults.transdate' => $self->transaction->transdate_as_date,
987 'form_defaults.callback' => $self->callback,
988 'form_defaults.bt_id' => $self->transaction->id,
989 'form_defaults.bt_chart_id' => $self->transaction->local_bank_account->chart->id,
990 'form_defaults.description' => $self->transaction->purpose,
994 sub setup_search_action_bar {
995 my ($self, %params) = @_;
997 for my $bar ($::request->layout->get('actionbar')) {
1001 submit => [ '#search_form', { action => 'BankTransaction/list' } ],
1002 accesskey => 'enter',
1008 sub setup_list_all_action_bar {
1009 my ($self, %params) = @_;
1011 for my $bar ($::request->layout->get('actionbar')) {
1014 action => [ t8('Actions') ],
1016 t8('Unlink bank transactions'),
1017 submit => [ '#form', { action => 'BankTransaction/unlink_bank_transaction' } ],
1018 checks => [ [ 'kivi.check_if_entries_selected', '[name="ids[]"]' ] ],
1019 disabled => $::instance_conf->get_payments_changeable ? t8('Cannot safely unlink bank transactions, please set the posting configuration for payments to unchangeable.') : undef,
1024 submit => [ '#filter_form', { action => 'BankTransaction/list_all' } ],
1025 accesskey => 'enter',
1040 SL::Controller::BankTransaction - Posting payments to invoices from
1041 bank transactions imported earlier
1047 =item C<save_single_bank_transaction %params>
1049 Takes a bank transaction ID (as parameter C<bank_transaction_id> and
1050 tries to post its amount to a certain number of invoices (parameter
1051 C<invoice_ids>, an array ref of database IDs to purchase or sales
1054 This method handles already partly assigned bank transactions.
1056 This method cannot handle already partly assigned bank transactions, i.e.
1057 a bank transaction that has a invoice_amount <> 0 but not the fully
1058 transaction amount (invoice_amount == amount).
1060 If the amount of the bank transaction is higher than the sum of
1061 the assigned invoices (1 .. n) the bank transaction will only be
1064 The whole function is wrapped in a database transaction. If an
1065 exception occurs the bank transaction is not posted at all. The same
1066 is true if the code detects an error during the execution, e.g. a bank
1067 transaction that's already been posted earlier. In both cases the
1068 database transaction will be rolled back.
1070 If warnings but not errors occur the database transaction is still
1073 The return value is an error object or C<undef> if the function
1074 succeeded. The calling function will collect all warnings and errors
1075 and display them in a nicely formatted table if any occurred.
1077 An error object is a hash reference containing the following members:
1081 =item * C<result> — can be either C<warning> or C<error>. Warnings are
1082 displayed slightly different than errors.
1084 =item * C<message> — a human-readable message included in the list of
1085 errors meant as the description of why the problem happened
1087 =item * C<bank_transaction_id>, C<invoice_ids> — the same parameters
1088 that the function was called with
1090 =item * C<bank_transaction> — the database object
1091 (C<SL::DB::BankTransaction>) corresponding to C<bank_transaction_id>
1093 =item * C<invoices> — an array ref of the database objects (either
1094 C<SL::DB::Invoice> or C<SL::DB::PurchaseInvoice>) corresponding to
1099 =item C<action_unlink_bank_transaction>
1101 Takes one or more bank transaction ID (as parameter C<form::ids>) and
1102 tries to revert all payment bookings including already cleared bookings.
1104 This method won't undo payments that are in a closed period and assumes
1105 that payments are not manually changed, i.e. only imported payments.
1107 GL-records will be deleted completely if a bank transaction was the source.
1109 TODO: we still rely on linked_records for the check boxes
1115 Niclas Zimmermann E<lt>niclas@kivitendo-premium.deE<gt>,
1116 Geoffrey Richardson E<lt>information@richardson-bueren.deE<gt>