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);
74 if (!$::form->{filter}{bank_account}) {
75 flash('error', t8('No bank account chosen!'));
80 my $sort_by = $::form->{sort_by} || 'transdate';
81 $sort_by = 'transdate' if $sort_by eq 'proposal';
82 $sort_by .= $::form->{sort_dir} ? ' DESC' : ' ASC';
84 my $fromdate = $::locale->parse_date_to_object($::form->{filter}->{fromdate});
85 my $todate = $::locale->parse_date_to_object($::form->{filter}->{todate});
86 $todate->add( days => 1 ) if $todate;
89 push @where, (transdate => { ge => $fromdate }) if ($fromdate);
90 push @where, (transdate => { lt => $todate }) if ($todate);
91 my $bank_account = SL::DB::Manager::BankAccount->find_by( id => $::form->{filter}{bank_account} );
92 # bank_transactions no younger than starting date,
93 # including starting date (same search behaviour as fromdate)
94 # but OPEN invoices to be matched may be from before
95 if ( $bank_account->reconciliation_starting_date ) {
96 push @where, (transdate => { ge => $bank_account->reconciliation_starting_date });
99 my $bank_transactions = SL::DB::Manager::BankTransaction->get_all(
100 with_objects => [ 'local_bank_account', 'currency' ],
104 amount => {ne => \'invoice_amount'},
105 local_bank_account_id => $::form->{filter}{bank_account},
110 # credit notes have a negative amount, treat differently
111 my $all_open_ar_invoices = SL::DB::Manager::Invoice ->get_all(where => [ or => [ amount => { gt => \'paid' },
112 and => [ type => 'credit_note',
113 amount => { lt => \'paid' }
117 with_objects => ['customer','payment_terms']);
119 my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => [amount => { ne => \'paid' }], with_objects => ['vendor' ,'payment_terms']);
120 my $all_open_sepa_export_items = SL::DB::Manager::SepaExportItem->get_all(where => [chart_id => $bank_account->chart_id ,
121 'sepa_export.executed' => 0, 'sepa_export.closed' => 0 ], with_objects => ['sepa_export']);
123 my @all_open_invoices;
124 # filter out invoices with less than 1 cent outstanding
125 push @all_open_invoices, map { $_->{is_ar}=1 ; $_ } grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ar_invoices };
126 push @all_open_invoices, map { $_->{is_ar}=0 ; $_ } grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
129 my %sepa_export_items_by_id = partition_by { $_->ar_id || $_->ap_id } @$all_open_sepa_export_items;
131 # first collect sepa export items to open invoices
132 foreach my $open_invoice (@all_open_invoices){
133 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount,2);
134 $open_invoice->{skonto_type} = 'without_skonto';
135 foreach (@{ $sepa_export_items_by_id{ $open_invoice->id } || [] }) {
136 my $factor = ($_->ar_id == $open_invoice->id ? 1 : -1);
137 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount*$factor,2);
139 $open_invoice->{skonto_type} = $_->payment_type;
140 $sepa_exports{$_->sepa_export_id} ||= { count => 0, is_ar => 0, amount => 0, proposed => 0, invoices => [], item => $_ };
141 $sepa_exports{$_->sepa_export_id}->{count}++;
142 $sepa_exports{$_->sepa_export_id}->{is_ar}++ if $_->ar_id == $open_invoice->id;
143 $sepa_exports{$_->sepa_export_id}->{amount} += $_->amount * $factor;
144 push @{ $sepa_exports{$_->sepa_export_id}->{invoices} }, $open_invoice;
148 # try to match each bank_transaction with each of the possible open invoices
152 foreach my $bt (@{ $bank_transactions }) {
153 ## 5 Stellen hinter dem Komma auf 2 Stellen reduzieren
154 $bt->amount($bt->amount*1);
155 $bt->invoice_amount($bt->invoice_amount*1);
157 $bt->{proposals} = [];
158 $bt->{rule_matches} = [];
160 $bt->{remote_name} .= $bt->{remote_name_1} if $bt->{remote_name_1};
162 if ( $bt->is_batch_transaction ) {
164 foreach ( keys %sepa_exports) {
165 if ( abs(($sepa_exports{$_}->{amount} * 1) - ($bt->amount * 1)) < 0.01 ) {
167 @{$bt->{proposals}} = @{$sepa_exports{$_}->{invoices}};
168 $bt->{sepa_export_ok} = 1;
169 $sepa_exports{$_}->{proposed}=1;
170 push(@proposals, $bt);
176 # batch transaction has no remotename !!
178 next unless $bt->{remote_name}; # bank has no name, usually fees, use create invoice to assign
181 # try to match the current $bt to each of the open_invoices, saving the
182 # results of get_agreement_with_invoice in $open_invoice->{agreement} and
183 # $open_invoice->{rule_matches}.
185 # The values are overwritten each time a new bt is checked, so at the end
186 # of each bt the likely results are filtered and those values are stored in
187 # the arrays $bt->{proposals} and $bt->{rule_matches}, and the agreement
188 # score is stored in $bt->{agreement}
190 foreach my $open_invoice (@all_open_invoices) {
191 ($open_invoice->{agreement}, $open_invoice->{rule_matches}) = $bt->get_agreement_with_invoice($open_invoice,
192 sepa_export_items => $all_open_sepa_export_items,
194 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,
195 $open_invoice->amount * ($open_invoice->{is_ar} ? 1 : -1), 2);
199 my $min_agreement = 3; # suggestions must have at least this score
201 my $max_agreement = max map { $_->{agreement} } @all_open_invoices;
203 # add open_invoices with highest agreement into array $bt->{proposals}
204 if ( $max_agreement >= $min_agreement ) {
205 $bt->{proposals} = [ grep { $_->{agreement} == $max_agreement } @all_open_invoices ];
206 $bt->{agreement} = $max_agreement; #scalar @{ $bt->{proposals} } ? $agreement + 1 : '';
208 # store the rule_matches in a separate array, so they can be displayed in template
209 foreach ( @{ $bt->{proposals} } ) {
210 push(@{$bt->{rule_matches}}, $_->{rule_matches});
216 # separate filter for proposals (second tab, agreement >= 5 and exactly one match)
217 # to qualify as a proposal there has to be
218 # * agreement >= 5 TODO: make threshold configurable in configuration
219 # * there must be only one exact match
220 # * depending on whether sales or purchase the amount has to have the correct sign (so Gutschriften don't work?)
221 my $proposal_threshold = 5;
222 my @otherproposals = grep {
223 ($_->{agreement} >= $proposal_threshold)
224 && (1 == scalar @{ $_->{proposals} })
225 && (@{ $_->{proposals} }[0]->is_sales ? abs(@{ $_->{proposals} }[0]->amount - $_->amount) < 0.01
226 : abs(@{ $_->{proposals} }[0]->amount + $_->amount) < 0.01)
227 } @{ $bank_transactions };
229 push @proposals, @otherproposals;
231 # sort bank transaction proposals by quality (score) of proposal
232 if ($::form->{sort_by} && $::form->{sort_by} eq 'proposal') {
233 if ($::form->{sort_dir}) {
234 $bank_transactions = [ sort { $a->{agreement} <=> $b->{agreement} } @{ $bank_transactions } ];
236 $bank_transactions = [ sort { $b->{agreement} <=> $a->{agreement} } @{ $bank_transactions } ];
240 # for testing with t/bank/banktransaction.t :
241 if ( $::form->{dont_render_for_test} ) {
242 return ( $bank_transactions , \@proposals );
245 $::request->layout->add_javascripts("kivi.BankTransaction.js");
246 $self->render('bank_transactions/list',
247 title => t8('Bank transactions MT940'),
248 BANK_TRANSACTIONS => $bank_transactions,
249 PROPOSALS => \@proposals,
250 bank_account => $bank_account,
251 ui_tab => scalar(@proposals) > 0?1:0,
255 sub action_assign_invoice {
258 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
260 $self->render('bank_transactions/assign_invoice',
262 title => t8('Assign invoice'),);
265 sub action_create_invoice {
267 my %myconfig = %main::myconfig;
269 $self->transaction(SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id}));
271 my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(iban => $self->transaction->{remote_account_number});
272 my $use_vendor_filter = $self->transaction->{remote_account_number} && $vendor_of_transaction;
274 my $templates_ap = SL::DB::Manager::RecordTemplate->get_all(
275 where => [ template_type => 'ap_transaction' ],
276 with_objects => [ qw(employee vendor) ],
278 my $templates_gl = SL::DB::Manager::RecordTemplate->get_all(
279 query => [ template_type => 'gl_transaction',
280 chart_id => SL::DB::Manager::BankAccount->find_by(id => $self->transaction->local_bank_account_id)->chart_id,
282 with_objects => [ qw(employee record_template_items) ],
285 # pre filter templates_ap, if we have a vendor match (IBAN eq IBAN) - show and allow user to edit this via gui!
286 $templates_ap = [ grep { $_->vendor_id == $vendor_of_transaction->id } @{ $templates_ap } ] if $use_vendor_filter;
288 $self->callback($self->url_for(
290 'filter.bank_account' => $::form->{filter}->{bank_account},
291 'filter.todate' => $::form->{filter}->{todate},
292 'filter.fromdate' => $::form->{filter}->{fromdate},
296 'bank_transactions/create_invoice',
298 title => t8('Create invoice'),
299 TEMPLATES_GL => $use_vendor_filter && @{ $templates_ap } ? undef : $templates_gl,
300 TEMPLATES_AP => $templates_ap,
301 vendor_name => $use_vendor_filter && @{ $templates_ap } ? $vendor_of_transaction->name : undef,
305 sub action_ajax_payment_suggestion {
308 # based on a BankTransaction ID and a Invoice or PurchaseInvoice ID passed via $::form,
309 # create an HTML blob to be used by the js function add_invoices in templates/webpages/bank_transactions/list.html
310 # and return encoded as JSON
312 croak("Need bt_id") unless $::form->{bt_id};
314 my $invoice = SL::DB::Manager::Invoice->find_by( id => $::form->{prop_id} ) || SL::DB::Manager::PurchaseInvoice->find_by( id => $::form->{prop_id} );
316 croak("No valid invoice found") unless $invoice;
318 my $html = $self->render(
319 'bank_transactions/_payment_suggestion', { output => 0 },
320 bt_id => $::form->{bt_id},
324 $self->render(\ SL::JSON::to_json( { 'html' => "$html" } ), { layout => 0, type => 'json', process => 0 });
327 sub action_filter_templates {
330 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
332 my (@filter, @filter_ap);
334 # filter => gl and ap | filter_ap = ap (i.e. vendorname)
335 push @filter, ('template_name' => { ilike => '%' . $::form->{template} . '%' }) if $::form->{template};
336 push @filter, ('reference' => { ilike => '%' . $::form->{reference} . '%' }) if $::form->{reference};
337 push @filter_ap, ('vendor.name' => { ilike => '%' . $::form->{vendor} . '%' }) if $::form->{vendor};
338 push @filter_ap, @filter;
339 my $templates_gl = SL::DB::Manager::RecordTemplate->get_all(
340 query => [ template_type => 'gl_transaction',
341 chart_id => SL::DB::Manager::BankAccount->find_by(id => $self->transaction->local_bank_account_id)->chart_id,
342 (and => \@filter) x !!@filter
344 with_objects => [ qw(employee record_template_items) ],
347 my $templates_ap = SL::DB::Manager::RecordTemplate->get_all(
348 where => [ template_type => 'ap_transaction', (and => \@filter_ap) x !!@filter_ap ],
349 with_objects => [ qw(employee vendor) ],
351 $::form->{filter} //= {};
353 $self->callback($self->url_for(
355 'filter.bank_account' => $::form->{filter}->{bank_account},
356 'filter.todate' => $::form->{filter}->{todate},
357 'filter.fromdate' => $::form->{filter}->{fromdate},
360 my $output = $self->render(
361 'bank_transactions/_template_list',
363 TEMPLATES_AP => $templates_ap,
364 TEMPLATES_GL => $templates_gl,
367 $self->render(\to_json({ html => $output }), { type => 'json', process => 0 });
370 sub action_ajax_add_list {
373 my @where_sale = (amount => { ne => \'paid' });
374 my @where_purchase = (amount => { ne => \'paid' });
376 if ($::form->{invnumber}) {
377 push @where_sale, (invnumber => { ilike => like($::form->{invnumber})});
378 push @where_purchase, (invnumber => { ilike => like($::form->{invnumber})});
381 if ($::form->{amount}) {
382 push @where_sale, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
383 push @where_purchase, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
386 if ($::form->{vcnumber}) {
387 push @where_sale, ('customer.customernumber' => { ilike => like($::form->{vcnumber})});
388 push @where_purchase, ('vendor.vendornumber' => { ilike => like($::form->{vcnumber})});
391 if ($::form->{vcname}) {
392 push @where_sale, ('customer.name' => { ilike => like($::form->{vcname})});
393 push @where_purchase, ('vendor.name' => { ilike => like($::form->{vcname})});
396 if ($::form->{transdatefrom}) {
397 my $fromdate = $::locale->parse_date_to_object($::form->{transdatefrom});
398 if ( ref($fromdate) eq 'DateTime' ) {
399 push @where_sale, ('transdate' => { ge => $fromdate});
400 push @where_purchase, ('transdate' => { ge => $fromdate});
404 if ($::form->{transdateto}) {
405 my $todate = $::locale->parse_date_to_object($::form->{transdateto});
406 if ( ref($todate) eq 'DateTime' ) {
407 $todate->add(days => 1);
408 push @where_sale, ('transdate' => { lt => $todate});
409 push @where_purchase, ('transdate' => { lt => $todate});
413 my $all_open_ar_invoices = SL::DB::Manager::Invoice ->get_all(where => \@where_sale, with_objects => 'customer');
414 my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => \@where_purchase, with_objects => 'vendor');
416 my @all_open_invoices = @{ $all_open_ar_invoices };
417 # add ap invoices, filtering out subcent open amounts
418 push @all_open_invoices, grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
420 @all_open_invoices = sort { $a->id <=> $b->id } @all_open_invoices;
422 my $output = $self->render(
423 'bank_transactions/add_list',
425 INVOICES => \@all_open_invoices,
428 my %result = ( count => 0, html => $output );
430 $self->render(\to_json(\%result), { type => 'json', process => 0 });
433 sub action_ajax_accept_invoices {
436 my @selected_invoices;
437 foreach my $invoice_id (@{ $::form->{invoice_id} || [] }) {
438 my $invoice_object = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
439 push @selected_invoices, $invoice_object;
443 'bank_transactions/invoices',
445 INVOICES => \@selected_invoices,
446 bt_id => $::form->{bt_id},
453 return 0 if !$::form->{invoice_ids};
455 my %invoice_hash = %{ delete $::form->{invoice_ids} }; # each key (the bt line with a bt_id) contains an array of invoice_ids
457 # e.g. three partial payments with bt_ids 54, 55 and 56 for invoice with id 74:
470 # or if the payment with bt_id 44 is used to pay invoices with ids 50, 51 and 52
472 # '44' => [ '50', '51', 52' ]
475 $::form->{invoice_skontos} ||= {}; # hash of arrays containing the payment types, could be empty
477 # a bank_transaction may be assigned to several invoices, i.e. a customer
478 # might pay several open invoices with one transaction
484 if ( $::form->{proposal_ids} ) {
485 foreach (@{ $::form->{proposal_ids} }) {
486 my $bank_transaction_id = $_;
487 my $invoice_ids = $invoice_hash{$_};
488 push @{ $self->problems }, $self->save_single_bank_transaction(
489 bank_transaction_id => $bank_transaction_id,
490 invoice_ids => $invoice_ids,
491 sources => ($::form->{sources} // {})->{$_},
492 memos => ($::form->{memos} // {})->{$_},
494 $count += scalar( @{$invoice_ids} );
497 while ( my ($bank_transaction_id, $invoice_ids) = each(%invoice_hash) ) {
498 push @{ $self->problems }, $self->save_single_bank_transaction(
499 bank_transaction_id => $bank_transaction_id,
500 invoice_ids => $invoice_ids,
501 sources => [ map { $::form->{"sources_${bank_transaction_id}_${_}"} } @{ $invoice_ids } ],
502 memos => [ map { $::form->{"memos_${bank_transaction_id}_${_}"} } @{ $invoice_ids } ],
504 $count += scalar( @{$invoice_ids} );
507 my $max_count = $count;
508 foreach (@{ $self->problems }) {
509 $count-- if $_->{result} eq 'error';
511 return ($count, $max_count);
514 sub action_save_invoices {
516 my ($success_count, $max_count) = $self->save_invoices();
518 if ($success_count == $max_count) {
519 flash('ok', t8('#1 invoice(s) saved.', $success_count));
521 flash('error', t8('At least #1 invoice(s) not saved', $max_count - $success_count));
524 $self->action_list();
527 sub action_save_proposals {
530 if ( $::form->{proposal_ids} ) {
531 my $propcount = scalar(@{ $::form->{proposal_ids} });
532 if ( $propcount > 0 ) {
533 my $count = $self->save_invoices();
535 flash('ok', t8('#1 proposal(s) with #2 invoice(s) saved.', $propcount, $count));
538 $self->action_list();
542 sub save_single_bank_transaction {
543 my ($self, %params) = @_;
547 bank_transaction => SL::DB::Manager::BankTransaction->find_by(id => $params{bank_transaction_id}),
551 if (!$data{bank_transaction}) {
555 message => $::locale->text('The ID #1 is not a valid database ID.', $data{bank_transaction_id}),
559 my $bank_transaction = $data{bank_transaction};
561 if ($bank_transaction->closed_period) {
565 message => $::locale->text('Cannot post payment for a closed period!'),
571 my $bt_id = $data{bank_transaction_id};
572 my $sign = $bank_transaction->amount < 0 ? -1 : 1;
573 my $payment_received = $bank_transaction->amount > 0;
574 my $payment_sent = $bank_transaction->amount < 0;
577 foreach my $invoice_id (@{ $params{invoice_ids} }) {
578 my $invoice = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
583 message => $::locale->text("The ID #1 is not a valid database ID.", $invoice_id),
586 push @{ $data{invoices} }, $invoice;
589 if ( $payment_received
590 && any { ( $_->is_sales && ($_->amount < 0))
591 || (!$_->is_sales && ($_->amount > 0))
592 } @{ $data{invoices} }) {
596 message => $::locale->text("Received payments can only be posted for sales invoices and purchase credit notes."),
601 && any { ( $_->is_sales && ($_->amount > 0))
602 || (!$_->is_sales && ($_->amount < 0) && ($_->invoice_type eq 'purchase_invoice'))
603 } @{ $data{invoices} }) {
607 message => $::locale->text("Sent payments can only be posted for purchase invoices and sales credit notes."),
611 my $max_invoices = scalar(@{ $data{invoices} });
614 foreach my $invoice (@{ $data{invoices} }) {
615 my $source = ($data{sources} // [])->[$n_invoices];
616 my $memo = ($data{memos} // [])->[$n_invoices];
619 # safety check invoice open
620 croak("Invoice closed. Cannot proceed.") unless ($invoice->open_amount);
622 if ( ($payment_sent && $bank_transaction->not_assigned_amount >= 0)
623 || ($payment_received && $bank_transaction->not_assigned_amount <= 0)) {
627 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."),
631 my ($payment_type, $free_skonto_amount);
632 if ( defined $::form->{invoice_skontos}->{"$bt_id"} ) {
633 $payment_type = shift(@{ $::form->{invoice_skontos}->{"$bt_id"} });
635 $payment_type = 'without_skonto';
638 if ($payment_type eq 'free_skonto') {
639 # parse user input > 0
640 if ($::form->parse_amount(\%::myconfig, $::form->{"free_skonto_amount"}->{"$bt_id"}{$invoice->id}) > 0) {
641 $free_skonto_amount = $::form->parse_amount(\%::myconfig, $::form->{"free_skonto_amount"}->{"$bt_id"}{$invoice->id});
646 message => $::locale->text("Free skonto amount has to be a positive number."),
651 # TODO rewrite this: really booked amount should be a return value of Payment.pm
652 # also this controller shouldnt care about how to calc skonto. we simply delegate the
653 # payment_type to the helper and get the corresponding bank_transaction values back
654 # hotfix to get the signs right - compare absolute values and later set the signs
655 # should be better done elsewhere - changing not_assigned_amount to abs feels seriously bogus
657 my $open_amount = $payment_type eq 'with_skonto_pt' ? $invoice->amount_less_skonto : $invoice->open_amount;
658 $open_amount = abs($open_amount);
659 $open_amount -= $free_skonto_amount if ($payment_type eq 'free_skonto');
660 my $not_assigned_amount = abs($bank_transaction->not_assigned_amount);
661 my $amount_for_booking = ($open_amount < $not_assigned_amount) ? $open_amount : $not_assigned_amount;
662 my $amount_for_payment = $amount_for_booking;
664 # get the right direction for the payment bookings (all amounts < 0 are stornos, credit notes or negative ap)
665 $amount_for_payment *= -1 if $invoice->amount < 0;
666 $free_skonto_amount *= -1 if ($free_skonto_amount && $invoice->amount < 0);
667 # get the right direction for the bank transaction
668 $amount_for_booking *= $sign;
670 $bank_transaction->invoice_amount($bank_transaction->invoice_amount + $amount_for_booking);
672 # ... and then pay the invoice
673 my @acc_ids = $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
674 trans_id => $invoice->id,
675 amount => $amount_for_payment,
676 payment_type => $payment_type,
679 skonto_amount => $free_skonto_amount,
680 transdate => $bank_transaction->valutadate->to_kivitendo);
681 # ... and record the origin via BankTransactionAccTrans
682 if (scalar(@acc_ids) < 2) {
686 message => $::locale->text("Unable to book transactions for bank purpose #1", $bank_transaction->purpose),
689 foreach my $acc_trans_id (@acc_ids) {
690 my $id_type = $invoice->is_sales ? 'ar' : 'ap';
692 acc_trans_id => $acc_trans_id,
693 bank_transaction_id => $bank_transaction->id,
694 $id_type => $invoice->id,
696 SL::DB::BankTransactionAccTrans->new(%props_acc)->save;
698 # Record a record link from the bank transaction to the invoice
700 from_table => 'bank_transactions',
702 to_table => $invoice->is_sales ? 'ar' : 'ap',
703 to_id => $invoice->id,
705 SL::DB::RecordLink->new(%props)->save;
707 # "close" a sepa_export_item if it exists
708 # code duplicated in action_save_proposals!
709 # currently only works, if there is only exactly one open sepa_export_item
710 if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
711 if ( scalar @$seis == 1 ) {
712 # moved the execution and the check for sepa_export into a method,
713 # this isn't part of a transaction, though
714 $seis->[0]->set_executed if $invoice->id == $seis->[0]->arap_id;
719 $bank_transaction->save;
721 # 'undef' means 'no error' here.
726 my $rez = $data{bank_transaction}->db->with_transaction(sub {
728 $error = $worker->();
739 # Rollback Fehler nicht weiterreichen
741 # aber einen rollback von hand
742 $::lxdebug->message(LXDebug->DEBUG2(),"finish worker with ". ($error ? $error->{result} : '-'));
743 $data{bank_transaction}->db->dbh->rollback if $error && $error->{result} eq 'error';
746 return grep { $_ } ($error, @warnings);
748 sub action_unlink_bank_transaction {
749 my ($self, %params) = @_;
751 croak("No bank transaction ids") unless scalar @{ $::form->{ids}} > 0;
755 foreach my $bt_id (@{ $::form->{ids}} ) {
757 my $bank_transaction = SL::DB::Manager::BankTransaction->find_by(id => $bt_id);
758 croak("No valid bank transaction found") unless (ref($bank_transaction) eq 'SL::DB::BankTransaction');
759 croak t8('Cannot unlink payment for a closed period!') if $bank_transaction->closed_period;
761 # everything in one transaction
762 my $rez = $bank_transaction->db->with_transaction(sub {
763 # 1. remove all reconciliations (due to underlying trigger, this has to be the first step)
764 my $rec_links = SL::DB::Manager::ReconciliationLink->get_all(where => [ bank_transaction_id => $bt_id ]);
765 $_->delete for @{ $rec_links };
768 foreach my $acc_trans_id_entry (@{ SL::DB::Manager::BankTransactionAccTrans->get_all(where => [bank_transaction_id => $bt_id ] )}) {
770 my $acc_trans = SL::DB::Manager::AccTransaction->get_all(where => [acc_trans_id => $acc_trans_id_entry->acc_trans_id]);
772 # save trans_id and type
773 die "no type" unless ($acc_trans_id_entry->ar_id || $acc_trans_id_entry->ap_id || $acc_trans_id_entry->gl_id);
774 $trans_ids{$acc_trans_id_entry->ar_id} = 'ar' if $acc_trans_id_entry->ar_id;
775 $trans_ids{$acc_trans_id_entry->ap_id} = 'ap' if $acc_trans_id_entry->ap_id;
776 $trans_ids{$acc_trans_id_entry->gl_id} = 'gl' if $acc_trans_id_entry->gl_id;
777 # 2. all good -> ready to delete acc_trans and bt_acc link
778 $acc_trans_id_entry->delete;
779 $_->delete for @{ $acc_trans };
781 # 3. update arap.paid (may not be 0, yet)
782 # or in case of gl, delete whole entry
783 while (my ($trans_id, $type) = each %trans_ids) {
785 SL::DB::Manager::GLTransaction->delete_all(where => [ id => $trans_id ]);
788 die ("invalid type") unless $type =~ m/^(ar|ap)$/;
790 # recalc and set paid via database query
791 my $query = qq|UPDATE $type SET paid =
792 (SELECT COALESCE(abs(sum(amount)),0) FROM acc_trans
794 AND chart_link ilike '%paid%')
797 die if (do_query($::form, $bank_transaction->db->dbh, $query, $trans_id, $trans_id) == -1);
799 # 4. and delete all (if any) record links
800 my $rl = SL::DB::Manager::RecordLink->delete_all(where => [ from_id => $bt_id, from_table => 'bank_transactions' ]);
802 # 5. finally reset this bank transaction
803 $bank_transaction->invoice_amount(0);
804 $bank_transaction->cleared(0);
805 $bank_transaction->save;
806 # 6. and add a log entry in history_erp
807 SL::DB::History->new(
808 trans_id => $bank_transaction->id,
809 snumbers => 'bank_transaction_unlink_' . $bank_transaction->id,
810 employee_id => SL::DB::Manager::Employee->current->id,
811 what_done => 'bank_transaction',
812 addition => 'UNLINKED',
817 }) || die t8('error while unlinking payment #1 : ', $bank_transaction->purpose) . $bank_transaction->db->error . "\n";
822 flash('ok', t8('#1 bank transaction bookings undone.', $success_count));
823 $self->action_list_all() unless $params{testcase};
830 $::auth->assert('bank_transaction');
837 sub make_filter_summary {
840 my $filter = $::form->{filter} || {};
844 [ $filter->{"transdate:date::ge"}, $::locale->text('Transdate') . " " . $::locale->text('From Date') ],
845 [ $filter->{"transdate:date::le"}, $::locale->text('Transdate') . " " . $::locale->text('To Date') ],
846 [ $filter->{"valutadate:date::ge"}, $::locale->text('Valutadate') . " " . $::locale->text('From Date') ],
847 [ $filter->{"valutadate:date::le"}, $::locale->text('Valutadate') . " " . $::locale->text('To Date') ],
848 [ $filter->{"amount:number"}, $::locale->text('Amount') ],
849 [ $filter->{"bank_account_id:integer"}, $::locale->text('Local bank account') ],
853 push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
856 $self->{filter_summary} = join ', ', @filter_strings;
862 my $callback = $self->models->get_callback;
864 my $report = SL::ReportGenerator->new(\%::myconfig, $::form);
865 $self->{report} = $report;
867 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);
868 my @sortable = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount purpose local_account_number local_bank_code);
871 ids => { raw_header_data => checkbox_tag("", id => "check_all", checkall => "[data-checkall=1]"),
873 raw_data => sub { if (@{ $_[0]->linked_invoices }) {
874 if ($_[0]->closed_period) {
875 html_tag('text', "X"); #, tooltip => t8('Bank Transaction is in a closed period.')),
877 checkbox_tag("ids[]", value => $_[0]->id, "data-checkall" => 1);
880 transdate => { sub => sub { $_[0]->transdate_as_date } },
881 valutadate => { sub => sub { $_[0]->valutadate_as_date } },
883 remote_account_number => { },
884 remote_bank_code => { },
885 amount => { sub => sub { $_[0]->amount_as_number },
887 invoice_amount => { sub => sub { $_[0]->invoice_amount_as_number },
889 invoices => { sub => sub { my @invnumbers; for my $obj (@{ $_[0]->linked_invoices }) {
890 next unless $obj; push @invnumbers, $obj->invnumber } return \@invnumbers } },
891 currency => { sub => sub { $_[0]->currency->name } },
893 local_account_number => { sub => sub { $_[0]->local_bank_account->account_number } },
894 local_bank_code => { sub => sub { $_[0]->local_bank_account->bank_code } },
895 local_bank_name => { sub => sub { $_[0]->local_bank_account->name } },
899 map { $column_defs{$_}->{text} ||= $::locale->text( $self->models->get_sort_spec->{$_}->{title} ) } keys %column_defs;
901 $report->set_options(
902 std_column_visibility => 1,
903 controller_class => 'BankTransaction',
904 output_format => 'HTML',
905 top_info_text => $::locale->text('Bank transactions'),
906 title => $::locale->text('Bank transactions'),
907 allow_pdf_export => 1,
908 allow_csv_export => 1,
910 $report->set_columns(%column_defs);
911 $report->set_column_order(@columns);
912 $report->set_export_options(qw(list_all filter));
913 $report->set_options_from_form;
914 $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
915 $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
917 my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted();
919 $report->set_options(
920 raw_top_info_text => $self->render('bank_transactions/report_top', { output => 0 }, BANK_ACCOUNTS => $bank_accounts),
921 raw_bottom_info_text => $self->render('bank_transactions/report_bottom', { output => 0 }),
925 sub init_problems { [] }
930 SL::Controller::Helper::GetModels->new(
935 dir => 0, # 1 = ASC, 0 = DESC : default sort is newest at top
938 transdate => t8('Transdate'),
939 remote_name => t8('Remote name'),
940 amount => t8('Amount'),
941 invoice_amount => t8('Assigned'),
942 invoices => t8('Linked invoices'),
943 valutadate => t8('Valutadate'),
944 remote_account_number => t8('Remote account number'),
945 remote_bank_code => t8('Remote bank code'),
946 currency => t8('Currency'),
947 purpose => t8('Purpose'),
948 local_account_number => t8('Local account number'),
949 local_bank_code => t8('Local bank code'),
950 local_bank_name => t8('Bank account'),
952 with_objects => [ 'local_bank_account', 'currency' ],
956 sub load_ap_record_template_url {
957 my ($self, $template) = @_;
959 return $self->url_for(
960 controller => 'ap.pl',
961 action => 'load_record_template',
963 'form_defaults.amount_1' => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
964 'form_defaults.transdate' => $self->transaction->transdate_as_date,
965 'form_defaults.duedate' => $self->transaction->transdate_as_date,
966 'form_defaults.no_payment_bookings' => 1,
967 'form_defaults.paid_1_suggestion' => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
968 'form_defaults.AP_paid_1_suggestion' => $self->transaction->local_bank_account->chart->accno,
969 'form_defaults.callback' => $self->callback,
973 sub load_gl_record_template_url {
974 my ($self, $template) = @_;
976 return $self->url_for(
977 controller => 'gl.pl',
978 action => 'load_record_template',
980 'form_defaults.amount_1' => abs($self->transaction->not_assigned_amount), # always positive
981 'form_defaults.transdate' => $self->transaction->transdate_as_date,
982 'form_defaults.callback' => $self->callback,
983 'form_defaults.bt_id' => $self->transaction->id,
984 'form_defaults.bt_chart_id' => $self->transaction->local_bank_account->chart->id,
985 'form_defaults.description' => $self->transaction->purpose,
989 sub setup_search_action_bar {
990 my ($self, %params) = @_;
992 for my $bar ($::request->layout->get('actionbar')) {
996 submit => [ '#search_form', { action => 'BankTransaction/list' } ],
997 accesskey => 'enter',
1003 sub setup_list_all_action_bar {
1004 my ($self, %params) = @_;
1006 for my $bar ($::request->layout->get('actionbar')) {
1009 action => [ t8('Actions') ],
1011 t8('Unlink bank transactions'),
1012 submit => [ '#form', { action => 'BankTransaction/unlink_bank_transaction' } ],
1013 checks => [ [ 'kivi.check_if_entries_selected', '[name="ids[]"]' ] ],
1014 disabled => $::instance_conf->get_payments_changeable ? t8('Cannot safely unlink bank transactions, please set the posting configuration for payments to unchangeable.') : undef,
1019 submit => [ '#filter_form', { action => 'BankTransaction/list_all' } ],
1020 accesskey => 'enter',
1035 SL::Controller::BankTransaction - Posting payments to invoices from
1036 bank transactions imported earlier
1042 =item C<save_single_bank_transaction %params>
1044 Takes a bank transaction ID (as parameter C<bank_transaction_id> and
1045 tries to post its amount to a certain number of invoices (parameter
1046 C<invoice_ids>, an array ref of database IDs to purchase or sales
1049 This method handles already partly assigned bank transactions.
1051 This method cannot handle already partly assigned bank transactions, i.e.
1052 a bank transaction that has a invoice_amount <> 0 but not the fully
1053 transaction amount (invoice_amount == amount).
1055 If the amount of the bank transaction is higher than the sum of
1056 the assigned invoices (1 .. n) the bank transaction will only be
1059 The whole function is wrapped in a database transaction. If an
1060 exception occurs the bank transaction is not posted at all. The same
1061 is true if the code detects an error during the execution, e.g. a bank
1062 transaction that's already been posted earlier. In both cases the
1063 database transaction will be rolled back.
1065 If warnings but not errors occur the database transaction is still
1068 The return value is an error object or C<undef> if the function
1069 succeeded. The calling function will collect all warnings and errors
1070 and display them in a nicely formatted table if any occurred.
1072 An error object is a hash reference containing the following members:
1076 =item * C<result> — can be either C<warning> or C<error>. Warnings are
1077 displayed slightly different than errors.
1079 =item * C<message> — a human-readable message included in the list of
1080 errors meant as the description of why the problem happened
1082 =item * C<bank_transaction_id>, C<invoice_ids> — the same parameters
1083 that the function was called with
1085 =item * C<bank_transaction> — the database object
1086 (C<SL::DB::BankTransaction>) corresponding to C<bank_transaction_id>
1088 =item * C<invoices> — an array ref of the database objects (either
1089 C<SL::DB::Invoice> or C<SL::DB::PurchaseInvoice>) corresponding to
1094 =item C<action_unlink_bank_transaction>
1096 Takes one or more bank transaction ID (as parameter C<form::ids>) and
1097 tries to revert all payment bookings including already cleared bookings.
1099 This method won't undo payments that are in a closed period and assumes
1100 that payments are not manually changed, i.e. only imported payments.
1102 GL-records will be deleted completely if a bank transaction was the source.
1104 TODO: we still rely on linked_records for the check boxes
1110 Niclas Zimmermann E<lt>niclas@kivitendo-premium.deE<gt>,
1111 Geoffrey Richardson E<lt>information@richardson-bueren.deE<gt>