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::RecordTemplate;
28 use SL::DB::SepaExportItem;
29 use SL::DBUtils qw(like do_query);
31 use SL::Presenter::Tag qw(checkbox_tag);
33 use List::UtilsBy qw(partition_by);
34 use List::MoreUtils qw(any);
35 use List::Util qw(max);
37 use Rose::Object::MakeMethods::Generic
39 scalar => [ qw(callback transaction) ],
40 'scalar --get_set_init' => [ qw(models problems) ],
43 __PACKAGE__->run_before('check_auth');
53 my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted( query => [ obsolete => 0 ] );
55 $self->setup_search_action_bar;
56 $self->render('bank_transactions/search',
57 BANK_ACCOUNTS => $bank_accounts);
63 $self->make_filter_summary;
64 $self->prepare_report;
66 $self->setup_list_all_action_bar;
67 $self->report_generator_list_objects(report => $self->{report}, objects => $self->models->get);
73 if (!$::form->{filter}{bank_account}) {
74 flash('error', t8('No bank account chosen!'));
79 my $sort_by = $::form->{sort_by} || 'transdate';
80 $sort_by = 'transdate' if $sort_by eq 'proposal';
81 $sort_by .= $::form->{sort_dir} ? ' DESC' : ' ASC';
83 my $fromdate = $::locale->parse_date_to_object($::form->{filter}->{fromdate});
84 my $todate = $::locale->parse_date_to_object($::form->{filter}->{todate});
85 $todate->add( days => 1 ) if $todate;
88 push @where, (transdate => { ge => $fromdate }) if ($fromdate);
89 push @where, (transdate => { lt => $todate }) if ($todate);
90 my $bank_account = SL::DB::Manager::BankAccount->find_by( id => $::form->{filter}{bank_account} );
91 # bank_transactions no younger than starting date,
92 # including starting date (same search behaviour as fromdate)
93 # but OPEN invoices to be matched may be from before
94 if ( $bank_account->reconciliation_starting_date ) {
95 push @where, (transdate => { ge => $bank_account->reconciliation_starting_date });
98 my $bank_transactions = SL::DB::Manager::BankTransaction->get_all(
99 with_objects => [ 'local_bank_account', 'currency' ],
103 amount => {ne => \'invoice_amount'},
104 local_bank_account_id => $::form->{filter}{bank_account},
109 # credit notes have a negative amount, treat differently
110 my $all_open_ar_invoices = SL::DB::Manager::Invoice ->get_all(where => [ or => [ amount => { gt => \'paid' },
111 and => [ type => 'credit_note',
112 amount => { lt => \'paid' }
116 with_objects => ['customer','payment_terms']);
118 my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => [amount => { ne => \'paid' }], with_objects => ['vendor' ,'payment_terms']);
119 my $all_open_sepa_export_items = SL::DB::Manager::SepaExportItem->get_all(where => [chart_id => $bank_account->chart_id ,
120 'sepa_export.executed' => 0, 'sepa_export.closed' => 0 ], with_objects => ['sepa_export']);
122 my @all_open_invoices;
123 # filter out invoices with less than 1 cent outstanding
124 push @all_open_invoices, map { $_->{is_ar}=1 ; $_ } grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ar_invoices };
125 push @all_open_invoices, map { $_->{is_ar}=0 ; $_ } grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
128 my %sepa_export_items_by_id = partition_by { $_->ar_id || $_->ap_id } @$all_open_sepa_export_items;
130 # first collect sepa export items to open invoices
131 foreach my $open_invoice (@all_open_invoices){
132 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount,2);
133 $open_invoice->{skonto_type} = 'without_skonto';
134 foreach (@{ $sepa_export_items_by_id{ $open_invoice->id } || [] }) {
135 my $factor = ($_->ar_id == $open_invoice->id ? 1 : -1);
136 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount*$factor,2);
138 $open_invoice->{skonto_type} = $_->payment_type;
139 $sepa_exports{$_->sepa_export_id} ||= { count => 0, is_ar => 0, amount => 0, proposed => 0, invoices => [], item => $_ };
140 $sepa_exports{$_->sepa_export_id}->{count}++;
141 $sepa_exports{$_->sepa_export_id}->{is_ar}++ if $_->ar_id == $open_invoice->id;
142 $sepa_exports{$_->sepa_export_id}->{amount} += $_->amount * $factor;
143 push @{ $sepa_exports{$_->sepa_export_id}->{invoices} }, $open_invoice;
147 # try to match each bank_transaction with each of the possible open invoices
151 foreach my $bt (@{ $bank_transactions }) {
152 ## 5 Stellen hinter dem Komma auf 2 Stellen reduzieren
153 $bt->amount($bt->amount*1);
154 $bt->invoice_amount($bt->invoice_amount*1);
156 $bt->{proposals} = [];
157 $bt->{rule_matches} = [];
159 $bt->{remote_name} .= $bt->{remote_name_1} if $bt->{remote_name_1};
161 if ( $bt->is_batch_transaction ) {
163 foreach ( keys %sepa_exports) {
164 if ( abs(($sepa_exports{$_}->{amount} * 1) - ($bt->amount * 1)) < 0.01 ) {
166 @{$bt->{proposals}} = @{$sepa_exports{$_}->{invoices}};
167 $bt->{sepa_export_ok} = 1;
168 $sepa_exports{$_}->{proposed}=1;
169 push(@proposals, $bt);
175 # batch transaction has no remotename !!
177 next unless $bt->{remote_name}; # bank has no name, usually fees, use create invoice to assign
180 # try to match the current $bt to each of the open_invoices, saving the
181 # results of get_agreement_with_invoice in $open_invoice->{agreement} and
182 # $open_invoice->{rule_matches}.
184 # The values are overwritten each time a new bt is checked, so at the end
185 # of each bt the likely results are filtered and those values are stored in
186 # the arrays $bt->{proposals} and $bt->{rule_matches}, and the agreement
187 # score is stored in $bt->{agreement}
189 foreach my $open_invoice (@all_open_invoices) {
190 ($open_invoice->{agreement}, $open_invoice->{rule_matches}) = $bt->get_agreement_with_invoice($open_invoice,
191 sepa_export_items => $all_open_sepa_export_items,
193 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,
194 $open_invoice->amount * ($open_invoice->{is_ar} ? 1 : -1), 2);
198 my $min_agreement = 3; # suggestions must have at least this score
200 my $max_agreement = max map { $_->{agreement} } @all_open_invoices;
202 # add open_invoices with highest agreement into array $bt->{proposals}
203 if ( $max_agreement >= $min_agreement ) {
204 $bt->{proposals} = [ grep { $_->{agreement} == $max_agreement } @all_open_invoices ];
205 $bt->{agreement} = $max_agreement; #scalar @{ $bt->{proposals} } ? $agreement + 1 : '';
207 # store the rule_matches in a separate array, so they can be displayed in template
208 foreach ( @{ $bt->{proposals} } ) {
209 push(@{$bt->{rule_matches}}, $_->{rule_matches});
215 # separate filter for proposals (second tab, agreement >= 5 and exactly one match)
216 # to qualify as a proposal there has to be
217 # * agreement >= 5 TODO: make threshold configurable in configuration
218 # * there must be only one exact match
219 # * depending on whether sales or purchase the amount has to have the correct sign (so Gutschriften don't work?)
220 my $proposal_threshold = 5;
221 my @otherproposals = grep {
222 ($_->{agreement} >= $proposal_threshold)
223 && (1 == scalar @{ $_->{proposals} })
224 && (@{ $_->{proposals} }[0]->is_sales ? abs(@{ $_->{proposals} }[0]->amount - $_->amount) < 0.01
225 : abs(@{ $_->{proposals} }[0]->amount + $_->amount) < 0.01)
226 } @{ $bank_transactions };
228 push @proposals, @otherproposals;
230 # sort bank transaction proposals by quality (score) of proposal
231 if ($::form->{sort_by} && $::form->{sort_by} eq 'proposal') {
232 if ($::form->{sort_dir}) {
233 $bank_transactions = [ sort { $a->{agreement} <=> $b->{agreement} } @{ $bank_transactions } ];
235 $bank_transactions = [ sort { $b->{agreement} <=> $a->{agreement} } @{ $bank_transactions } ];
239 # for testing with t/bank/banktransaction.t :
240 if ( $::form->{dont_render_for_test} ) {
241 return ( $bank_transactions , \@proposals );
244 $::request->layout->add_javascripts("kivi.BankTransaction.js");
245 $self->render('bank_transactions/list',
246 title => t8('Bank transactions MT940'),
247 BANK_TRANSACTIONS => $bank_transactions,
248 PROPOSALS => \@proposals,
249 bank_account => $bank_account,
250 ui_tab => scalar(@proposals) > 0?1:0,
254 sub action_assign_invoice {
257 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
259 $self->render('bank_transactions/assign_invoice',
261 title => t8('Assign invoice'),);
264 sub action_create_invoice {
266 my %myconfig = %main::myconfig;
268 $self->transaction(SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id}));
270 my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(iban => $self->transaction->{remote_account_number});
271 my $use_vendor_filter = $self->transaction->{remote_account_number} && $vendor_of_transaction;
273 my $templates_ap = SL::DB::Manager::RecordTemplate->get_all(
274 where => [ template_type => 'ap_transaction' ],
275 with_objects => [ qw(employee vendor) ],
277 my $templates_gl = SL::DB::Manager::RecordTemplate->get_all(
278 query => [ template_type => 'gl_transaction',
279 chart_id => SL::DB::Manager::BankAccount->find_by(id => $self->transaction->local_bank_account_id)->chart_id,
281 with_objects => [ qw(employee record_template_items) ],
284 # pre filter templates_ap, if we have a vendor match (IBAN eq IBAN) - show and allow user to edit this via gui!
285 $templates_ap = [ grep { $_->vendor_id == $vendor_of_transaction->id } @{ $templates_ap } ] if $use_vendor_filter;
287 $self->callback($self->url_for(
289 'filter.bank_account' => $::form->{filter}->{bank_account},
290 'filter.todate' => $::form->{filter}->{todate},
291 'filter.fromdate' => $::form->{filter}->{fromdate},
295 'bank_transactions/create_invoice',
297 title => t8('Create invoice'),
298 TEMPLATES_GL => $use_vendor_filter && @{ $templates_ap } ? undef : $templates_gl,
299 TEMPLATES_AP => $templates_ap,
300 vendor_name => $use_vendor_filter && @{ $templates_ap } ? $vendor_of_transaction->name : undef,
304 sub action_ajax_payment_suggestion {
307 # based on a BankTransaction ID and a Invoice or PurchaseInvoice ID passed via $::form,
308 # create an HTML blob to be used by the js function add_invoices in templates/webpages/bank_transactions/list.html
309 # and return encoded as JSON
311 my $bt = SL::DB::Manager::BankTransaction->find_by( id => $::form->{bt_id} );
312 my $invoice = SL::DB::Manager::Invoice->find_by( id => $::form->{prop_id} ) || SL::DB::Manager::PurchaseInvoice->find_by( id => $::form->{prop_id} );
314 die unless $bt and $invoice;
316 my @select_options = $invoice->get_payment_select_options_for_bank_transaction($::form->{bt_id});
319 $html = $self->render(
320 'bank_transactions/_payment_suggestion', { output => 0 },
321 bt_id => $::form->{bt_id},
322 prop_id => $::form->{prop_id},
324 SELECT_OPTIONS => \@select_options,
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};
567 my $bt_id = $data{bank_transaction_id};
568 my $sign = $bank_transaction->amount < 0 ? -1 : 1;
569 my $not_assigned_amount = $bank_transaction->not_assigned_amount;
570 my $payment_received = $bank_transaction->amount > 0;
571 my $payment_sent = $bank_transaction->amount < 0;
574 foreach my $invoice_id (@{ $params{invoice_ids} }) {
575 my $invoice = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
580 message => $::locale->text("The ID #1 is not a valid database ID.", $invoice_id),
583 push @{ $data{invoices} }, $invoice;
586 if ( $payment_received
587 && any { ( $_->is_sales && ($_->amount < 0))
588 || (!$_->is_sales && ($_->amount > 0))
589 } @{ $data{invoices} }) {
593 message => $::locale->text("Received payments can only be posted for sales invoices and purchase credit notes."),
598 && any { ( $_->is_sales && ($_->amount > 0))
599 || (!$_->is_sales && ($_->amount < 0) && ($_->invoice_type eq 'purchase_invoice'))
600 } @{ $data{invoices} }) {
604 message => $::locale->text("Sent payments can only be posted for purchase invoices and sales credit notes."),
608 my $max_invoices = scalar(@{ $data{invoices} });
611 foreach my $invoice (@{ $data{invoices} }) {
612 my $source = ($data{sources} // [])->[$n_invoices];
613 my $memo = ($data{memos} // [])->[$n_invoices];
618 if (!$not_assigned_amount && $invoice->open_amount) {
622 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."),
627 if ( defined $::form->{invoice_skontos}->{"$bt_id"} ) {
628 $payment_type = shift(@{ $::form->{invoice_skontos}->{"$bt_id"} });
630 $payment_type = 'without_skonto';
633 # TODO rewrite this: really booked amount should be a return value of Payment.pm
634 # also this controller shouldnt care about how to calc skonto. we simply delegate the
635 # payment_type to the helper and get the corresponding bank_transaction values back
636 # hotfix to get the signs right - compare absolute values and later set the signs
637 # should be better done elsewhere - changing not_assigned_amount to abs feels seriously bogus
639 my $open_amount = $payment_type eq 'with_skonto_pt' ? $invoice->amount_less_skonto : $invoice->open_amount;
640 $open_amount = abs($open_amount);
641 $not_assigned_amount = abs($not_assigned_amount);
642 my $amount_for_booking = ($open_amount < $not_assigned_amount) ? $open_amount : $not_assigned_amount;
643 my $amount_for_payment = $amount_for_booking;
645 # get the right direction for the payment bookings (all amounts < 0 are stornos, credit notes or negative ap)
646 $amount_for_payment *= -1 if $invoice->amount < 0;
647 # get the right direction for the bank transaction
648 $amount_for_booking *= $sign;
650 $bank_transaction->invoice_amount($bank_transaction->invoice_amount + $amount_for_booking);
652 # ... and then pay the invoice
653 my @acc_ids = $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
654 trans_id => $invoice->id,
655 amount => $amount_for_payment,
656 payment_type => $payment_type,
659 transdate => $bank_transaction->transdate->to_kivitendo);
660 # ... and record the origin via BankTransactionAccTrans
661 if (scalar(@acc_ids) < 2) {
665 message => $::locale->text("Unable to book transactions for bank purpose #1", $bank_transaction->purpose),
668 foreach my $acc_trans_id (@acc_ids) {
669 my $id_type = $invoice->is_sales ? 'ar' : 'ap';
671 acc_trans_id => $acc_trans_id,
672 bank_transaction_id => $bank_transaction->id,
673 $id_type => $invoice->id,
675 SL::DB::BankTransactionAccTrans->new(%props_acc)->save;
677 # Record a record link from the bank transaction to the invoice
679 from_table => 'bank_transactions',
681 to_table => $invoice->is_sales ? 'ar' : 'ap',
682 to_id => $invoice->id,
684 SL::DB::RecordLink->new(%props)->save;
686 # "close" a sepa_export_item if it exists
687 # code duplicated in action_save_proposals!
688 # currently only works, if there is only exactly one open sepa_export_item
689 if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
690 if ( scalar @$seis == 1 ) {
691 # moved the execution and the check for sepa_export into a method,
692 # this isn't part of a transaction, though
693 $seis->[0]->set_executed if $invoice->id == $seis->[0]->arap_id;
698 $bank_transaction->save;
700 # 'undef' means 'no error' here.
705 my $rez = $data{bank_transaction}->db->with_transaction(sub {
707 $error = $worker->();
718 # Rollback Fehler nicht weiterreichen
720 # aber einen rollback von hand
721 $::lxdebug->message(LXDebug->DEBUG2(),"finish worker with ". ($error ? $error->{result} : '-'));
722 $data{bank_transaction}->db->dbh->rollback if $error && $error->{result} eq 'error';
725 return grep { $_ } ($error, @warnings);
727 sub action_unlink_bank_transaction {
728 my ($self, %params) = @_;
730 croak("No bank transaction ids") unless scalar @{ $::form->{ids}} > 0;
732 my $closedto = $::locale->parse_date_to_object($::instance_conf->get_closedto);
735 foreach my $bt_id (@{ $::form->{ids}} ) {
737 my $bank_transaction = SL::DB::Manager::BankTransaction->find_by(id => $bt_id);
738 croak("No valid bank transaction found") unless (ref($bank_transaction) eq 'SL::DB::BankTransaction');
740 # everything in one transaction
741 my $rez = $bank_transaction->db->with_transaction(sub {
742 # 1. remove all reconciliations (due to underlying trigger, this has to be the first step)
743 my $rec_links = SL::DB::Manager::ReconciliationLink->get_all(where => [ bank_transaction_id => $bt_id ]);
744 $_->delete for @{ $rec_links };
747 foreach my $acc_trans_id_entry (@{ SL::DB::Manager::BankTransactionAccTrans->get_all(where => [bank_transaction_id => $bt_id ] )}) {
749 my $acc_trans = SL::DB::Manager::AccTransaction->get_all(where => [acc_trans_id => $acc_trans_id_entry->acc_trans_id]);
750 # check closedto for acc trans entries
751 croak t8('Cannot unlink payment for a closed period!') if (ref $closedto && grep { $_->transdate < $closedto } @{ $acc_trans } );
753 # save trans_id and type
754 die "no type" unless ($acc_trans_id_entry->ar_id || $acc_trans_id_entry->ap_id || $acc_trans_id_entry->gl_id);
755 $trans_ids{$acc_trans_id_entry->ar_id} = 'ar' if $acc_trans_id_entry->ar_id;
756 $trans_ids{$acc_trans_id_entry->ap_id} = 'ap' if $acc_trans_id_entry->ap_id;
757 $trans_ids{$acc_trans_id_entry->gl_id} = 'gl' if $acc_trans_id_entry->gl_id;
759 # 2. all good -> ready to delete acc_trans and bt_acc link
760 $acc_trans_id_entry->delete;
761 $_->delete for @{ $acc_trans };
763 # 3. update arap.paid (may not be 0, yet)
764 while (my ($trans_id, $type) = each %trans_ids) {
765 next if $type eq 'gl';
766 die ("invalid type") unless $type =~ m/^(ar|ap)$/;
768 # recalc and set paid via database query
769 my $query = qq|UPDATE $type SET paid =
770 (SELECT COALESCE(abs(sum(amount)),0) FROM acc_trans
772 AND chart_link ilike '%paid%')|;
774 die if (do_query($::form, $bank_transaction->db->dbh, $query, $trans_id) == -1);
776 # 4. and delete all (if any) record links
777 my $rl = SL::DB::Manager::RecordLink->delete_all(where => [ from_id => $bt_id, from_table => 'bank_transactions' ]);
779 # 5. finally reset this bank transaction
780 $bank_transaction->invoice_amount(0);
781 $bank_transaction->cleared(0);
782 $bank_transaction->save;
786 }) || die t8('error while unlinking payment #1 : ', $bank_transaction->purpose) . $bank_transaction->db->error . "\n";
791 flash('ok', t8('#1 bank transaction bookings undone.', $success_count));
792 $self->action_list_all() unless $params{testcase};
799 $::auth->assert('bank_transaction');
806 sub make_filter_summary {
809 my $filter = $::form->{filter} || {};
813 [ $filter->{"transdate:date::ge"}, $::locale->text('Transdate') . " " . $::locale->text('From Date') ],
814 [ $filter->{"transdate:date::le"}, $::locale->text('Transdate') . " " . $::locale->text('To Date') ],
815 [ $filter->{"valutadate:date::ge"}, $::locale->text('Valutadate') . " " . $::locale->text('From Date') ],
816 [ $filter->{"valutadate:date::le"}, $::locale->text('Valutadate') . " " . $::locale->text('To Date') ],
817 [ $filter->{"amount:number"}, $::locale->text('Amount') ],
818 [ $filter->{"bank_account_id:integer"}, $::locale->text('Local bank account') ],
822 push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
825 $self->{filter_summary} = join ', ', @filter_strings;
831 my $callback = $self->models->get_callback;
833 my $report = SL::ReportGenerator->new(\%::myconfig, $::form);
834 $self->{report} = $report;
836 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);
837 my @sortable = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount purpose local_account_number local_bank_code);
840 ids => { raw_header_data => checkbox_tag("", id => "check_all", checkall => "[data-checkall=1]"),
842 raw_data => sub { if (@{ $_[0]->linked_invoices } && !(grep {ref ($_) eq 'SL::DB::GLTransaction' } @{ $_[0]->linked_invoices })) {
843 checkbox_tag("ids[]", value => $_[0]->id, "data-checkall" => 1); } } },
844 transdate => { sub => sub { $_[0]->transdate_as_date } },
845 valutadate => { sub => sub { $_[0]->valutadate_as_date } },
847 remote_account_number => { },
848 remote_bank_code => { },
849 amount => { sub => sub { $_[0]->amount_as_number },
851 invoice_amount => { sub => sub { $_[0]->invoice_amount_as_number },
853 invoices => { sub => sub { my @invnumbers; for my $obj (@{ $_[0]->linked_invoices }) {
854 next unless $obj; push @invnumbers, $obj->invnumber } return \@invnumbers } },
855 currency => { sub => sub { $_[0]->currency->name } },
857 local_account_number => { sub => sub { $_[0]->local_bank_account->account_number } },
858 local_bank_code => { sub => sub { $_[0]->local_bank_account->bank_code } },
859 local_bank_name => { sub => sub { $_[0]->local_bank_account->name } },
863 map { $column_defs{$_}->{text} ||= $::locale->text( $self->models->get_sort_spec->{$_}->{title} ) } keys %column_defs;
865 $report->set_options(
866 std_column_visibility => 1,
867 controller_class => 'BankTransaction',
868 output_format => 'HTML',
869 top_info_text => $::locale->text('Bank transactions'),
870 title => $::locale->text('Bank transactions'),
871 allow_pdf_export => 1,
872 allow_csv_export => 1,
874 $report->set_columns(%column_defs);
875 $report->set_column_order(@columns);
876 $report->set_export_options(qw(list_all filter));
877 $report->set_options_from_form;
878 $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
879 $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
881 my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted();
883 $report->set_options(
884 raw_top_info_text => $self->render('bank_transactions/report_top', { output => 0 }, BANK_ACCOUNTS => $bank_accounts),
885 raw_bottom_info_text => $self->render('bank_transactions/report_bottom', { output => 0 }),
889 sub init_problems { [] }
894 SL::Controller::Helper::GetModels->new(
899 dir => 0, # 1 = ASC, 0 = DESC : default sort is newest at top
902 transdate => t8('Transdate'),
903 remote_name => t8('Remote name'),
904 amount => t8('Amount'),
905 invoice_amount => t8('Assigned'),
906 invoices => t8('Linked invoices'),
907 valutadate => t8('Valutadate'),
908 remote_account_number => t8('Remote account number'),
909 remote_bank_code => t8('Remote bank code'),
910 currency => t8('Currency'),
911 purpose => t8('Purpose'),
912 local_account_number => t8('Local account number'),
913 local_bank_code => t8('Local bank code'),
914 local_bank_name => t8('Bank account'),
916 with_objects => [ 'local_bank_account', 'currency' ],
920 sub load_ap_record_template_url {
921 my ($self, $template) = @_;
923 return $self->url_for(
924 controller => 'ap.pl',
925 action => 'load_record_template',
927 'form_defaults.amount_1' => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
928 'form_defaults.transdate' => $self->transaction->transdate_as_date,
929 'form_defaults.duedate' => $self->transaction->transdate_as_date,
930 'form_defaults.no_payment_bookings' => 1,
931 'form_defaults.paid_1_suggestion' => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
932 'form_defaults.AP_paid_1_suggestion' => $self->transaction->local_bank_account->chart->accno,
933 'form_defaults.callback' => $self->callback,
937 sub load_gl_record_template_url {
938 my ($self, $template) = @_;
940 return $self->url_for(
941 controller => 'gl.pl',
942 action => 'load_record_template',
944 'form_defaults.amount_1' => abs($self->transaction->not_assigned_amount), # always positive
945 'form_defaults.transdate' => $self->transaction->transdate_as_date,
946 'form_defaults.callback' => $self->callback,
947 'form_defaults.bt_id' => $self->transaction->id,
948 'form_defaults.bt_chart_id' => $self->transaction->local_bank_account->chart->id,
952 sub setup_search_action_bar {
953 my ($self, %params) = @_;
955 for my $bar ($::request->layout->get('actionbar')) {
959 submit => [ '#search_form', { action => 'BankTransaction/list' } ],
960 accesskey => 'enter',
966 sub setup_list_all_action_bar {
967 my ($self, %params) = @_;
969 for my $bar ($::request->layout->get('actionbar')) {
972 action => [ t8('Actions') ],
974 t8('Unlink bank transactions'),
975 submit => [ '#form', { action => 'BankTransaction/unlink_bank_transaction' } ],
976 checks => [ [ 'kivi.check_if_entries_selected', '[name="ids[]"]' ] ],
977 disabled => $::instance_conf->get_payments_changeable ? t8('Cannot safely unlink bank transactions, please set the posting configuration for payments to unchangeable.') : undef,
982 submit => [ '#filter_form', { action => 'BankTransaction/list_all' } ],
983 accesskey => 'enter',
998 SL::Controller::BankTransaction - Posting payments to invoices from
999 bank transactions imported earlier
1005 =item C<save_single_bank_transaction %params>
1007 Takes a bank transaction ID (as parameter C<bank_transaction_id> and
1008 tries to post its amount to a certain number of invoices (parameter
1009 C<invoice_ids>, an array ref of database IDs to purchase or sales
1012 This method handles already partly assigned bank transactions.
1014 This method cannot handle already partly assigned bank transactions, i.e.
1015 a bank transaction that has a invoice_amount <> 0 but not the fully
1016 transaction amount (invoice_amount == amount).
1018 If the amount of the bank transaction is higher than the sum of
1019 the assigned invoices (1 .. n) the bank transaction will only be
1022 The whole function is wrapped in a database transaction. If an
1023 exception occurs the bank transaction is not posted at all. The same
1024 is true if the code detects an error during the execution, e.g. a bank
1025 transaction that's already been posted earlier. In both cases the
1026 database transaction will be rolled back.
1028 If warnings but not errors occur the database transaction is still
1031 The return value is an error object or C<undef> if the function
1032 succeeded. The calling function will collect all warnings and errors
1033 and display them in a nicely formatted table if any occurred.
1035 An error object is a hash reference containing the following members:
1039 =item * C<result> — can be either C<warning> or C<error>. Warnings are
1040 displayed slightly different than errors.
1042 =item * C<message> — a human-readable message included in the list of
1043 errors meant as the description of why the problem happened
1045 =item * C<bank_transaction_id>, C<invoice_ids> — the same parameters
1046 that the function was called with
1048 =item * C<bank_transaction> — the database object
1049 (C<SL::DB::BankTransaction>) corresponding to C<bank_transaction_id>
1051 =item * C<invoices> — an array ref of the database objects (either
1052 C<SL::DB::Invoice> or C<SL::DB::PurchaseInvoice>) corresponding to
1057 =item C<action_unlink_bank_transaction>
1059 Takes one or more bank transaction ID (as parameter C<form::ids>) and
1060 tries to revert all payment bookings including already cleared bookings.
1062 This method won't undo payments that are in a closed period and assumes
1063 that payments are not manually changed, i.e. only imported payments.
1065 GL-records will be deleted completely if a bank transaction was the source.
1067 TODO: we still rely on linked_records for the check boxes
1073 Niclas Zimmermann E<lt>niclas@kivitendo-premium.deE<gt>,
1074 Geoffrey Richardson E<lt>information@richardson-bueren.deE<gt>