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 my $dir = $::form->{sort_dir} ? 1 : -1;
234 $bank_transactions = [ sort { ($a->{agreement} <=> $b->{agreement}) * $dir } @{ $bank_transactions } ];
237 # for testing with t/bank/banktransaction.t :
238 if ( $::form->{dont_render_for_test} ) {
239 return ( $bank_transactions , \@proposals );
242 $::request->layout->add_javascripts("kivi.BankTransaction.js");
243 $self->render('bank_transactions/list',
244 title => t8('Bank transactions MT940'),
245 BANK_TRANSACTIONS => $bank_transactions,
246 PROPOSALS => \@proposals,
247 bank_account => $bank_account,
248 ui_tab => scalar(@proposals) > 0?1:0,
252 sub action_assign_invoice {
255 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
257 $self->render('bank_transactions/assign_invoice',
259 title => t8('Assign invoice'),);
262 sub action_create_invoice {
264 my %myconfig = %main::myconfig;
266 $self->transaction(SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id}));
268 my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(iban => $self->transaction->{remote_account_number});
269 my $use_vendor_filter = $self->transaction->{remote_account_number} && $vendor_of_transaction;
271 my $templates_ap = SL::DB::Manager::RecordTemplate->get_all(
272 where => [ template_type => 'ap_transaction' ],
273 with_objects => [ qw(employee vendor) ],
275 my $templates_gl = SL::DB::Manager::RecordTemplate->get_all(
276 query => [ template_type => 'gl_transaction',
277 chart_id => SL::DB::Manager::BankAccount->find_by(id => $self->transaction->local_bank_account_id)->chart_id,
279 with_objects => [ qw(employee record_template_items) ],
282 # pre filter templates_ap, if we have a vendor match (IBAN eq IBAN) - show and allow user to edit this via gui!
283 $templates_ap = [ grep { $_->vendor_id == $vendor_of_transaction->id } @{ $templates_ap } ] if $use_vendor_filter;
285 $self->callback($self->url_for(
287 'filter.bank_account' => $::form->{filter}->{bank_account},
288 'filter.todate' => $::form->{filter}->{todate},
289 'filter.fromdate' => $::form->{filter}->{fromdate},
293 'bank_transactions/create_invoice',
295 title => t8('Create invoice'),
296 TEMPLATES_GL => $use_vendor_filter && @{ $templates_ap } ? undef : $templates_gl,
297 TEMPLATES_AP => $templates_ap,
298 vendor_name => $use_vendor_filter && @{ $templates_ap } ? $vendor_of_transaction->name : undef,
302 sub action_ajax_payment_suggestion {
305 # based on a BankTransaction ID and a Invoice or PurchaseInvoice ID passed via $::form,
306 # create an HTML blob to be used by the js function add_invoices in templates/webpages/bank_transactions/list.html
307 # and return encoded as JSON
309 croak("Need bt_id") unless $::form->{bt_id};
311 my $invoice = SL::DB::Manager::Invoice->find_by( id => $::form->{prop_id} ) || SL::DB::Manager::PurchaseInvoice->find_by( id => $::form->{prop_id} );
313 croak("No valid invoice found") unless $invoice;
315 my $html = $self->render(
316 'bank_transactions/_payment_suggestion', { output => 0 },
317 bt_id => $::form->{bt_id},
321 $self->render(\ SL::JSON::to_json( { 'html' => "$html" } ), { layout => 0, type => 'json', process => 0 });
324 sub action_filter_templates {
327 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
329 my (@filter, @filter_ap);
331 # filter => gl and ap | filter_ap = ap (i.e. vendorname)
332 push @filter, ('template_name' => { ilike => '%' . $::form->{template} . '%' }) if $::form->{template};
333 push @filter, ('reference' => { ilike => '%' . $::form->{reference} . '%' }) if $::form->{reference};
334 push @filter_ap, ('vendor.name' => { ilike => '%' . $::form->{vendor} . '%' }) if $::form->{vendor};
335 push @filter_ap, @filter;
336 my $templates_gl = SL::DB::Manager::RecordTemplate->get_all(
337 query => [ template_type => 'gl_transaction',
338 chart_id => SL::DB::Manager::BankAccount->find_by(id => $self->transaction->local_bank_account_id)->chart_id,
339 (and => \@filter) x !!@filter
341 with_objects => [ qw(employee record_template_items) ],
344 my $templates_ap = SL::DB::Manager::RecordTemplate->get_all(
345 where => [ template_type => 'ap_transaction', (and => \@filter_ap) x !!@filter_ap ],
346 with_objects => [ qw(employee vendor) ],
348 $::form->{filter} //= {};
350 $self->callback($self->url_for(
352 'filter.bank_account' => $::form->{filter}->{bank_account},
353 'filter.todate' => $::form->{filter}->{todate},
354 'filter.fromdate' => $::form->{filter}->{fromdate},
357 my $output = $self->render(
358 'bank_transactions/_template_list',
360 TEMPLATES_AP => $templates_ap,
361 TEMPLATES_GL => $templates_gl,
364 $self->render(\to_json({ html => $output }), { type => 'json', process => 0 });
367 sub action_ajax_add_list {
370 my @where_sale = (amount => { ne => \'paid' });
371 my @where_purchase = (amount => { ne => \'paid' });
373 if ($::form->{invnumber}) {
374 push @where_sale, (invnumber => { ilike => like($::form->{invnumber})});
375 push @where_purchase, (invnumber => { ilike => like($::form->{invnumber})});
378 if ($::form->{amount}) {
379 push @where_sale, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
380 push @where_purchase, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
383 if ($::form->{vcnumber}) {
384 push @where_sale, ('customer.customernumber' => { ilike => like($::form->{vcnumber})});
385 push @where_purchase, ('vendor.vendornumber' => { ilike => like($::form->{vcnumber})});
388 if ($::form->{vcname}) {
389 push @where_sale, ('customer.name' => { ilike => like($::form->{vcname})});
390 push @where_purchase, ('vendor.name' => { ilike => like($::form->{vcname})});
393 if ($::form->{transdatefrom}) {
394 my $fromdate = $::locale->parse_date_to_object($::form->{transdatefrom});
395 if ( ref($fromdate) eq 'DateTime' ) {
396 push @where_sale, ('transdate' => { ge => $fromdate});
397 push @where_purchase, ('transdate' => { ge => $fromdate});
401 if ($::form->{transdateto}) {
402 my $todate = $::locale->parse_date_to_object($::form->{transdateto});
403 if ( ref($todate) eq 'DateTime' ) {
404 $todate->add(days => 1);
405 push @where_sale, ('transdate' => { lt => $todate});
406 push @where_purchase, ('transdate' => { lt => $todate});
410 my $all_open_ar_invoices = SL::DB::Manager::Invoice ->get_all(where => \@where_sale, with_objects => 'customer');
411 my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => \@where_purchase, with_objects => 'vendor');
413 my @all_open_invoices = @{ $all_open_ar_invoices };
414 # add ap invoices, filtering out subcent open amounts
415 push @all_open_invoices, grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
417 @all_open_invoices = sort { $a->id <=> $b->id } @all_open_invoices;
419 my $output = $self->render(
420 'bank_transactions/add_list',
422 INVOICES => \@all_open_invoices,
425 my %result = ( count => 0, html => $output );
427 $self->render(\to_json(\%result), { type => 'json', process => 0 });
430 sub action_ajax_accept_invoices {
433 my @selected_invoices;
434 foreach my $invoice_id (@{ $::form->{invoice_id} || [] }) {
435 my $invoice_object = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
436 push @selected_invoices, $invoice_object;
440 'bank_transactions/invoices',
442 INVOICES => \@selected_invoices,
443 bt_id => $::form->{bt_id},
450 return 0 if !$::form->{invoice_ids};
452 my %invoice_hash = %{ delete $::form->{invoice_ids} }; # each key (the bt line with a bt_id) contains an array of invoice_ids
454 # e.g. three partial payments with bt_ids 54, 55 and 56 for invoice with id 74:
467 # or if the payment with bt_id 44 is used to pay invoices with ids 50, 51 and 52
469 # '44' => [ '50', '51', 52' ]
472 $::form->{invoice_skontos} ||= {}; # hash of arrays containing the payment types, could be empty
474 # a bank_transaction may be assigned to several invoices, i.e. a customer
475 # might pay several open invoices with one transaction
481 if ( $::form->{proposal_ids} ) {
482 foreach (@{ $::form->{proposal_ids} }) {
483 my $bank_transaction_id = $_;
484 my $invoice_ids = $invoice_hash{$_};
485 push @{ $self->problems }, $self->save_single_bank_transaction(
486 bank_transaction_id => $bank_transaction_id,
487 invoice_ids => $invoice_ids,
488 sources => ($::form->{sources} // {})->{$_},
489 memos => ($::form->{memos} // {})->{$_},
491 $count += scalar( @{$invoice_ids} );
494 while ( my ($bank_transaction_id, $invoice_ids) = each(%invoice_hash) ) {
495 push @{ $self->problems }, $self->save_single_bank_transaction(
496 bank_transaction_id => $bank_transaction_id,
497 invoice_ids => $invoice_ids,
498 sources => [ map { $::form->{"sources_${bank_transaction_id}_${_}"} } @{ $invoice_ids } ],
499 memos => [ map { $::form->{"memos_${bank_transaction_id}_${_}"} } @{ $invoice_ids } ],
501 $count += scalar( @{$invoice_ids} );
504 my $max_count = $count;
505 foreach (@{ $self->problems }) {
506 $count-- if $_->{result} eq 'error';
508 return ($count, $max_count);
511 sub action_save_invoices {
513 my ($success_count, $max_count) = $self->save_invoices();
515 if ($success_count == $max_count) {
516 flash('ok', t8('#1 invoice(s) saved.', $success_count));
518 flash('error', t8('At least #1 invoice(s) not saved', $max_count - $success_count));
521 $self->action_list();
524 sub action_save_proposals {
527 if ( $::form->{proposal_ids} ) {
528 my $propcount = scalar(@{ $::form->{proposal_ids} });
529 if ( $propcount > 0 ) {
530 my $count = $self->save_invoices();
532 flash('ok', t8('#1 proposal(s) with #2 invoice(s) saved.', $propcount, $count));
535 $self->action_list();
539 sub save_single_bank_transaction {
540 my ($self, %params) = @_;
544 bank_transaction => SL::DB::Manager::BankTransaction->find_by(id => $params{bank_transaction_id}),
548 if (!$data{bank_transaction}) {
552 message => $::locale->text('The ID #1 is not a valid database ID.', $data{bank_transaction_id}),
556 my $bank_transaction = $data{bank_transaction};
558 if ($bank_transaction->closed_period) {
562 message => $::locale->text('Cannot post payment for a closed period!'),
568 my $bt_id = $data{bank_transaction_id};
569 my $sign = $bank_transaction->amount < 0 ? -1 : 1;
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];
616 # safety check invoice open
617 croak("Invoice closed. Cannot proceed.") unless ($invoice->open_amount);
619 if ( ($payment_sent && $bank_transaction->not_assigned_amount >= 0)
620 || ($payment_received && $bank_transaction->not_assigned_amount <= 0)) {
624 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."),
628 my ($payment_type, $free_skonto_amount);
629 if ( defined $::form->{invoice_skontos}->{"$bt_id"} ) {
630 $payment_type = shift(@{ $::form->{invoice_skontos}->{"$bt_id"} });
632 $payment_type = 'without_skonto';
635 if ($payment_type eq 'free_skonto') {
636 # parse user input > 0
637 if ($::form->parse_amount(\%::myconfig, $::form->{"free_skonto_amount"}->{"$bt_id"}{$invoice->id}) > 0) {
638 $free_skonto_amount = $::form->parse_amount(\%::myconfig, $::form->{"free_skonto_amount"}->{"$bt_id"}{$invoice->id});
643 message => $::locale->text("Free skonto amount has to be a positive number."),
648 # TODO rewrite this: really booked amount should be a return value of Payment.pm
649 # also this controller shouldnt care about how to calc skonto. we simply delegate the
650 # payment_type to the helper and get the corresponding bank_transaction values back
651 # hotfix to get the signs right - compare absolute values and later set the signs
652 # should be better done elsewhere - changing not_assigned_amount to abs feels seriously bogus
654 my $open_amount = $payment_type eq 'with_skonto_pt' ? $invoice->amount_less_skonto : $invoice->open_amount;
655 $open_amount = abs($open_amount);
656 $open_amount -= $free_skonto_amount if ($payment_type eq 'free_skonto');
657 my $not_assigned_amount = abs($bank_transaction->not_assigned_amount);
658 my $amount_for_booking = ($open_amount < $not_assigned_amount) ? $open_amount : $not_assigned_amount;
659 my $amount_for_payment = $amount_for_booking;
661 # get the right direction for the payment bookings (all amounts < 0 are stornos, credit notes or negative ap)
662 $amount_for_payment *= -1 if $invoice->amount < 0;
663 $free_skonto_amount *= -1 if ($free_skonto_amount && $invoice->amount < 0);
664 # get the right direction for the bank transaction
665 $amount_for_booking *= $sign;
667 $bank_transaction->invoice_amount($bank_transaction->invoice_amount + $amount_for_booking);
669 # ... and then pay the invoice
670 my @acc_ids = $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
671 trans_id => $invoice->id,
672 amount => $amount_for_payment,
673 payment_type => $payment_type,
676 skonto_amount => $free_skonto_amount,
677 transdate => $bank_transaction->valutadate->to_kivitendo);
678 # ... and record the origin via BankTransactionAccTrans
679 if (scalar(@acc_ids) < 2) {
683 message => $::locale->text("Unable to book transactions for bank purpose #1", $bank_transaction->purpose),
686 foreach my $acc_trans_id (@acc_ids) {
687 my $id_type = $invoice->is_sales ? 'ar' : 'ap';
689 acc_trans_id => $acc_trans_id,
690 bank_transaction_id => $bank_transaction->id,
691 $id_type => $invoice->id,
693 SL::DB::BankTransactionAccTrans->new(%props_acc)->save;
695 # Record a record link from the bank transaction to the invoice
697 from_table => 'bank_transactions',
699 to_table => $invoice->is_sales ? 'ar' : 'ap',
700 to_id => $invoice->id,
702 SL::DB::RecordLink->new(%props)->save;
704 # "close" a sepa_export_item if it exists
705 # code duplicated in action_save_proposals!
706 # currently only works, if there is only exactly one open sepa_export_item
707 if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
708 if ( scalar @$seis == 1 ) {
709 # moved the execution and the check for sepa_export into a method,
710 # this isn't part of a transaction, though
711 $seis->[0]->set_executed if $invoice->id == $seis->[0]->arap_id;
716 $bank_transaction->save;
718 # 'undef' means 'no error' here.
723 my $rez = $data{bank_transaction}->db->with_transaction(sub {
725 $error = $worker->();
736 # Rollback Fehler nicht weiterreichen
738 # aber einen rollback von hand
739 $::lxdebug->message(LXDebug->DEBUG2(),"finish worker with ". ($error ? $error->{result} : '-'));
740 $data{bank_transaction}->db->dbh->rollback if $error && $error->{result} eq 'error';
743 return grep { $_ } ($error, @warnings);
745 sub action_unlink_bank_transaction {
746 my ($self, %params) = @_;
748 croak("No bank transaction ids") unless scalar @{ $::form->{ids}} > 0;
752 foreach my $bt_id (@{ $::form->{ids}} ) {
754 my $bank_transaction = SL::DB::Manager::BankTransaction->find_by(id => $bt_id);
755 croak("No valid bank transaction found") unless (ref($bank_transaction) eq 'SL::DB::BankTransaction');
756 croak t8('Cannot unlink payment for a closed period!') if $bank_transaction->closed_period;
758 # everything in one transaction
759 my $rez = $bank_transaction->db->with_transaction(sub {
760 # 1. remove all reconciliations (due to underlying trigger, this has to be the first step)
761 my $rec_links = SL::DB::Manager::ReconciliationLink->get_all(where => [ bank_transaction_id => $bt_id ]);
762 $_->delete for @{ $rec_links };
765 foreach my $acc_trans_id_entry (@{ SL::DB::Manager::BankTransactionAccTrans->get_all(where => [bank_transaction_id => $bt_id ] )}) {
767 my $acc_trans = SL::DB::Manager::AccTransaction->get_all(where => [acc_trans_id => $acc_trans_id_entry->acc_trans_id]);
769 # save trans_id and type
770 die "no type" unless ($acc_trans_id_entry->ar_id || $acc_trans_id_entry->ap_id || $acc_trans_id_entry->gl_id);
771 $trans_ids{$acc_trans_id_entry->ar_id} = 'ar' if $acc_trans_id_entry->ar_id;
772 $trans_ids{$acc_trans_id_entry->ap_id} = 'ap' if $acc_trans_id_entry->ap_id;
773 $trans_ids{$acc_trans_id_entry->gl_id} = 'gl' if $acc_trans_id_entry->gl_id;
774 # 2. all good -> ready to delete acc_trans and bt_acc link
775 $acc_trans_id_entry->delete;
776 $_->delete for @{ $acc_trans };
778 # 3. update arap.paid (may not be 0, yet)
779 # or in case of gl, delete whole entry
780 while (my ($trans_id, $type) = each %trans_ids) {
782 SL::DB::Manager::GLTransaction->delete_all(where => [ id => $trans_id ]);
785 die ("invalid type") unless $type =~ m/^(ar|ap)$/;
787 # recalc and set paid via database query
788 my $query = qq|UPDATE $type SET paid =
789 (SELECT COALESCE(abs(sum(amount)),0) FROM acc_trans
791 AND chart_link ilike '%paid%')
794 die if (do_query($::form, $bank_transaction->db->dbh, $query, $trans_id, $trans_id) == -1);
796 # 4. and delete all (if any) record links
797 my $rl = SL::DB::Manager::RecordLink->delete_all(where => [ from_id => $bt_id, from_table => 'bank_transactions' ]);
799 # 5. finally reset this bank transaction
800 $bank_transaction->invoice_amount(0);
801 $bank_transaction->cleared(0);
802 $bank_transaction->save;
803 # 6. and add a log entry in history_erp
804 SL::DB::History->new(
805 trans_id => $bank_transaction->id,
806 snumbers => 'bank_transaction_unlink_' . $bank_transaction->id,
807 employee_id => SL::DB::Manager::Employee->current->id,
808 what_done => 'bank_transaction',
809 addition => 'UNLINKED',
814 }) || die t8('error while unlinking payment #1 : ', $bank_transaction->purpose) . $bank_transaction->db->error . "\n";
819 flash('ok', t8('#1 bank transaction bookings undone.', $success_count));
820 $self->action_list_all() unless $params{testcase};
827 $::auth->assert('bank_transaction');
834 sub make_filter_summary {
837 my $filter = $::form->{filter} || {};
841 [ $filter->{"transdate:date::ge"}, $::locale->text('Transdate') . " " . $::locale->text('From Date') ],
842 [ $filter->{"transdate:date::le"}, $::locale->text('Transdate') . " " . $::locale->text('To Date') ],
843 [ $filter->{"valutadate:date::ge"}, $::locale->text('Valutadate') . " " . $::locale->text('From Date') ],
844 [ $filter->{"valutadate:date::le"}, $::locale->text('Valutadate') . " " . $::locale->text('To Date') ],
845 [ $filter->{"amount:number"}, $::locale->text('Amount') ],
846 [ $filter->{"bank_account_id:integer"}, $::locale->text('Local bank account') ],
850 push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
853 $self->{filter_summary} = join ', ', @filter_strings;
859 my $callback = $self->models->get_callback;
861 my $report = SL::ReportGenerator->new(\%::myconfig, $::form);
862 $self->{report} = $report;
864 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);
865 my @sortable = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount purpose local_account_number local_bank_code);
868 ids => { raw_header_data => checkbox_tag("", id => "check_all", checkall => "[data-checkall=1]"),
870 raw_data => sub { if (@{ $_[0]->linked_invoices }) {
871 if ($_[0]->closed_period) {
872 html_tag('text', "X"); #, tooltip => t8('Bank Transaction is in a closed period.')),
874 checkbox_tag("ids[]", value => $_[0]->id, "data-checkall" => 1);
877 transdate => { sub => sub { $_[0]->transdate_as_date } },
878 valutadate => { sub => sub { $_[0]->valutadate_as_date } },
880 remote_account_number => { },
881 remote_bank_code => { },
882 amount => { sub => sub { $_[0]->amount_as_number },
884 invoice_amount => { sub => sub { $_[0]->invoice_amount_as_number },
886 invoices => { sub => sub { my @invnumbers; for my $obj (@{ $_[0]->linked_invoices }) {
887 next unless $obj; push @invnumbers, $obj->invnumber } return \@invnumbers } },
888 currency => { sub => sub { $_[0]->currency->name } },
890 local_account_number => { sub => sub { $_[0]->local_bank_account->account_number } },
891 local_bank_code => { sub => sub { $_[0]->local_bank_account->bank_code } },
892 local_bank_name => { sub => sub { $_[0]->local_bank_account->name } },
896 map { $column_defs{$_}->{text} ||= $::locale->text( $self->models->get_sort_spec->{$_}->{title} ) } keys %column_defs;
898 $report->set_options(
899 std_column_visibility => 1,
900 controller_class => 'BankTransaction',
901 output_format => 'HTML',
902 top_info_text => $::locale->text('Bank transactions'),
903 title => $::locale->text('Bank transactions'),
904 allow_pdf_export => 1,
905 allow_csv_export => 1,
907 $report->set_columns(%column_defs);
908 $report->set_column_order(@columns);
909 $report->set_export_options(qw(list_all filter));
910 $report->set_options_from_form;
911 $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
912 $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
914 my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted();
916 $report->set_options(
917 raw_top_info_text => $self->render('bank_transactions/report_top', { output => 0 }, BANK_ACCOUNTS => $bank_accounts),
918 raw_bottom_info_text => $self->render('bank_transactions/report_bottom', { output => 0 }),
922 sub init_problems { [] }
927 SL::Controller::Helper::GetModels->new(
932 dir => 0, # 1 = ASC, 0 = DESC : default sort is newest at top
935 transdate => t8('Transdate'),
936 remote_name => t8('Remote name'),
937 amount => t8('Amount'),
938 invoice_amount => t8('Assigned'),
939 invoices => t8('Linked invoices'),
940 valutadate => t8('Valutadate'),
941 remote_account_number => t8('Remote account number'),
942 remote_bank_code => t8('Remote bank code'),
943 currency => t8('Currency'),
944 purpose => t8('Purpose'),
945 local_account_number => t8('Local account number'),
946 local_bank_code => t8('Local bank code'),
947 local_bank_name => t8('Bank account'),
949 with_objects => [ 'local_bank_account', 'currency' ],
953 sub load_ap_record_template_url {
954 my ($self, $template) = @_;
956 return $self->url_for(
957 controller => 'ap.pl',
958 action => 'load_record_template',
960 'form_defaults.amount_1' => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
961 'form_defaults.transdate' => $self->transaction->transdate_as_date,
962 'form_defaults.duedate' => $self->transaction->transdate_as_date,
963 'form_defaults.no_payment_bookings' => 1,
964 'form_defaults.paid_1_suggestion' => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
965 'form_defaults.AP_paid_1_suggestion' => $self->transaction->local_bank_account->chart->accno,
966 'form_defaults.callback' => $self->callback,
970 sub load_gl_record_template_url {
971 my ($self, $template) = @_;
973 return $self->url_for(
974 controller => 'gl.pl',
975 action => 'load_record_template',
977 'form_defaults.amount_1' => abs($self->transaction->not_assigned_amount), # always positive
978 'form_defaults.transdate' => $self->transaction->transdate_as_date,
979 'form_defaults.callback' => $self->callback,
980 'form_defaults.bt_id' => $self->transaction->id,
981 'form_defaults.bt_chart_id' => $self->transaction->local_bank_account->chart->id,
982 'form_defaults.description' => $self->transaction->purpose,
986 sub setup_search_action_bar {
987 my ($self, %params) = @_;
989 for my $bar ($::request->layout->get('actionbar')) {
993 submit => [ '#search_form', { action => 'BankTransaction/list' } ],
994 accesskey => 'enter',
1000 sub setup_list_all_action_bar {
1001 my ($self, %params) = @_;
1003 for my $bar ($::request->layout->get('actionbar')) {
1006 action => [ t8('Actions') ],
1008 t8('Unlink bank transactions'),
1009 submit => [ '#form', { action => 'BankTransaction/unlink_bank_transaction' } ],
1010 checks => [ [ 'kivi.check_if_entries_selected', '[name="ids[]"]' ] ],
1011 disabled => $::instance_conf->get_payments_changeable ? t8('Cannot safely unlink bank transactions, please set the posting configuration for payments to unchangeable.') : undef,
1016 submit => [ '#filter_form', { action => 'BankTransaction/list_all' } ],
1017 accesskey => 'enter',
1032 SL::Controller::BankTransaction - Posting payments to invoices from
1033 bank transactions imported earlier
1039 =item C<save_single_bank_transaction %params>
1041 Takes a bank transaction ID (as parameter C<bank_transaction_id> and
1042 tries to post its amount to a certain number of invoices (parameter
1043 C<invoice_ids>, an array ref of database IDs to purchase or sales
1046 This method handles already partly assigned bank transactions.
1048 This method cannot handle already partly assigned bank transactions, i.e.
1049 a bank transaction that has a invoice_amount <> 0 but not the fully
1050 transaction amount (invoice_amount == amount).
1052 If the amount of the bank transaction is higher than the sum of
1053 the assigned invoices (1 .. n) the bank transaction will only be
1056 The whole function is wrapped in a database transaction. If an
1057 exception occurs the bank transaction is not posted at all. The same
1058 is true if the code detects an error during the execution, e.g. a bank
1059 transaction that's already been posted earlier. In both cases the
1060 database transaction will be rolled back.
1062 If warnings but not errors occur the database transaction is still
1065 The return value is an error object or C<undef> if the function
1066 succeeded. The calling function will collect all warnings and errors
1067 and display them in a nicely formatted table if any occurred.
1069 An error object is a hash reference containing the following members:
1073 =item * C<result> — can be either C<warning> or C<error>. Warnings are
1074 displayed slightly different than errors.
1076 =item * C<message> — a human-readable message included in the list of
1077 errors meant as the description of why the problem happened
1079 =item * C<bank_transaction_id>, C<invoice_ids> — the same parameters
1080 that the function was called with
1082 =item * C<bank_transaction> — the database object
1083 (C<SL::DB::BankTransaction>) corresponding to C<bank_transaction_id>
1085 =item * C<invoices> — an array ref of the database objects (either
1086 C<SL::DB::Invoice> or C<SL::DB::PurchaseInvoice>) corresponding to
1091 =item C<action_unlink_bank_transaction>
1093 Takes one or more bank transaction ID (as parameter C<form::ids>) and
1094 tries to revert all payment bookings including already cleared bookings.
1096 This method won't undo payments that are in a closed period and assumes
1097 that payments are not manually changed, i.e. only imported payments.
1099 GL-records will be deleted completely if a bank transaction was the source.
1101 TODO: we still rely on linked_records for the check boxes
1107 Niclas Zimmermann E<lt>niclas@kivitendo-premium.deE<gt>,
1108 Geoffrey Richardson E<lt>information@richardson-bueren.deE<gt>