1 package SL::Controller::BankTransaction;
3 # idee- möglichkeit bankdaten zu übernehmen in stammdaten
4 # erst Kontenabgleich, um alle gl-Einträge wegzuhaben
7 use parent qw(SL::Controller::Base);
9 use SL::Controller::Helper::GetModels;
10 use SL::Controller::Helper::ReportGenerator;
11 use SL::ReportGenerator;
13 use SL::DB::BankTransaction;
14 use SL::Helper::Flash;
15 use SL::Locale::String;
18 use SL::DB::PurchaseInvoice;
19 use SL::DB::RecordLink;
20 use SL::DB::ReconciliationLink;
23 use SL::DB::AccTransaction;
24 use SL::DB::BankTransactionAccTrans;
26 use SL::DB::BankAccount;
27 use SL::DB::GLTransaction;
28 use SL::DB::RecordTemplate;
29 use SL::DB::SepaExportItem;
30 use SL::DBUtils qw(like do_query);
32 use SL::Presenter::Tag qw(checkbox_tag html_tag);
34 use List::UtilsBy qw(partition_by);
35 use List::MoreUtils qw(any);
36 use List::Util qw(max);
38 use Rose::Object::MakeMethods::Generic
40 scalar => [ qw(callback transaction) ],
41 'scalar --get_set_init' => [ qw(models problems) ],
44 __PACKAGE__->run_before('check_auth');
54 my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted( query => [ obsolete => 0 ] );
56 $self->setup_search_action_bar;
57 $self->render('bank_transactions/search',
58 BANK_ACCOUNTS => $bank_accounts);
64 $self->make_filter_summary;
65 $self->prepare_report;
67 $self->setup_list_all_action_bar;
68 $self->report_generator_list_objects(report => $self->{report}, objects => $self->models->get);
71 sub gather_bank_transactions_and_proposals {
72 my ($self, %params) = @_;
74 my $sort_by = $params{sort_by} || 'transdate';
75 $sort_by = 'transdate' if $sort_by eq 'proposal';
76 $sort_by .= $params{sort_dir} ? ' DESC' : ' ASC';
79 push @where, (transdate => { ge => $params{fromdate} }) if $params{fromdate};
80 push @where, (transdate => { lt => $params{todate} }) if $params{todate};
81 # bank_transactions no younger than starting date,
82 # including starting date (same search behaviour as fromdate)
83 # but OPEN invoices to be matched may be from before
84 if ( $params{bank_account}->reconciliation_starting_date ) {
85 push @where, (transdate => { ge => $params{bank_account}->reconciliation_starting_date });
88 my $bank_transactions = SL::DB::Manager::BankTransaction->get_all(
89 with_objects => [ 'local_bank_account', 'currency' ],
93 amount => {ne => \'invoice_amount'},
94 local_bank_account_id => $params{bank_account}->id,
99 # credit notes have a negative amount, treat differently
100 my $all_open_ar_invoices = SL::DB::Manager::Invoice ->get_all(where => [ or => [ amount => { gt => \'paid' },
101 and => [ type => 'credit_note',
102 amount => { lt => \'paid' }
106 with_objects => ['customer','payment_terms']);
108 my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => [amount => { ne => \'paid' }], with_objects => ['vendor' ,'payment_terms']);
109 my $all_open_sepa_export_items = SL::DB::Manager::SepaExportItem->get_all(where => [chart_id => $params{bank_account}->chart_id ,
110 'sepa_export.executed' => 0, 'sepa_export.closed' => 0 ], with_objects => ['sepa_export']);
112 my @all_open_invoices;
113 # filter out invoices with less than 1 cent outstanding
114 push @all_open_invoices, map { $_->{is_ar}=1 ; $_ } grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ar_invoices };
115 push @all_open_invoices, map { $_->{is_ar}=0 ; $_ } grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
118 my %sepa_export_items_by_id = partition_by { $_->ar_id || $_->ap_id } @$all_open_sepa_export_items;
120 # first collect sepa export items to open invoices
121 foreach my $open_invoice (@all_open_invoices){
122 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount,2);
123 $open_invoice->{skonto_type} = 'without_skonto';
124 foreach (@{ $sepa_export_items_by_id{ $open_invoice->id } || [] }) {
125 my $factor = ($_->ar_id == $open_invoice->id ? 1 : -1);
126 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount*$factor,2);
128 $open_invoice->{skonto_type} = $_->payment_type;
129 $sepa_exports{$_->sepa_export_id} ||= { count => 0, is_ar => 0, amount => 0, proposed => 0, invoices => [], item => $_ };
130 $sepa_exports{$_->sepa_export_id}->{count}++;
131 $sepa_exports{$_->sepa_export_id}->{is_ar}++ if $_->ar_id == $open_invoice->id;
132 $sepa_exports{$_->sepa_export_id}->{amount} += $_->amount * $factor;
133 push @{ $sepa_exports{$_->sepa_export_id}->{invoices} }, $open_invoice;
137 # try to match each bank_transaction with each of the possible open invoices
141 foreach my $bt (@{ $bank_transactions }) {
142 ## 5 Stellen hinter dem Komma auf 2 Stellen reduzieren
143 $bt->amount($bt->amount*1);
144 $bt->invoice_amount($bt->invoice_amount*1);
146 $bt->{proposals} = [];
147 $bt->{rule_matches} = [];
149 $bt->{remote_name} .= $bt->{remote_name_1} if $bt->{remote_name_1};
151 if ( $bt->is_batch_transaction ) {
153 foreach ( keys %sepa_exports) {
154 if ( abs(($sepa_exports{$_}->{amount} * 1) - ($bt->amount * 1)) < 0.01 ) {
156 @{$bt->{proposals}} = @{$sepa_exports{$_}->{invoices}};
157 $bt->{sepa_export_ok} = 1;
158 $sepa_exports{$_}->{proposed}=1;
159 push(@proposals, $bt);
165 # batch transaction has no remotename !!
168 # try to match the current $bt to each of the open_invoices, saving the
169 # results of get_agreement_with_invoice in $open_invoice->{agreement} and
170 # $open_invoice->{rule_matches}.
172 # The values are overwritten each time a new bt is checked, so at the end
173 # of each bt the likely results are filtered and those values are stored in
174 # the arrays $bt->{proposals} and $bt->{rule_matches}, and the agreement
175 # score is stored in $bt->{agreement}
177 foreach my $open_invoice (@all_open_invoices) {
178 ($open_invoice->{agreement}, $open_invoice->{rule_matches}) = $bt->get_agreement_with_invoice($open_invoice,
179 sepa_export_items => $all_open_sepa_export_items,
181 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,
182 $open_invoice->amount * ($open_invoice->{is_ar} ? 1 : -1), 2);
186 my $min_agreement = 3; # suggestions must have at least this score
188 my $max_agreement = max map { $_->{agreement} } @all_open_invoices;
190 # add open_invoices with highest agreement into array $bt->{proposals}
191 if ( $max_agreement >= $min_agreement ) {
192 $bt->{proposals} = [ grep { $_->{agreement} == $max_agreement } @all_open_invoices ];
193 $bt->{agreement} = $max_agreement; #scalar @{ $bt->{proposals} } ? $agreement + 1 : '';
195 # store the rule_matches in a separate array, so they can be displayed in template
196 foreach ( @{ $bt->{proposals} } ) {
197 push(@{$bt->{rule_matches}}, $_->{rule_matches});
203 # separate filter for proposals (second tab, agreement >= 5 and exactly one match)
204 # to qualify as a proposal there has to be
205 # * agreement >= 5 TODO: make threshold configurable in configuration
206 # * there must be only one exact match
207 my $proposal_threshold = 5;
208 my @otherproposals = grep {
209 ($_->{agreement} >= $proposal_threshold)
210 && (1 == scalar @{ $_->{proposals} })
211 } @{ $bank_transactions };
213 push @proposals, @otherproposals;
215 # sort bank transaction proposals by quality (score) of proposal
216 if ($params{sort_by} && $params{sort_by} eq 'proposal') {
217 my $dir = $params{sort_dir} ? 1 : -1;
218 $bank_transactions = [ sort { ($a->{agreement} <=> $b->{agreement}) * $dir } @{ $bank_transactions } ];
221 return ( $bank_transactions , \@proposals );
227 if (!$::form->{filter}{bank_account}) {
228 flash('error', t8('No bank account chosen!'));
229 $self->action_search;
233 my $bank_account = SL::DB::BankAccount->load_cached($::form->{filter}->{bank_account});
234 my $fromdate = $::locale->parse_date_to_object($::form->{filter}->{fromdate});
235 my $todate = $::locale->parse_date_to_object($::form->{filter}->{todate});
236 $todate->add( days => 1 ) if $todate;
238 my ($bank_transactions, $proposals) = $self->gather_bank_transactions_and_proposals(
239 bank_account => $bank_account,
240 fromdate => $fromdate,
242 sort_by => $::form->{sort_by},
243 sort_dir => $::form->{sort_dir},
246 $::request->layout->add_javascripts("kivi.BankTransaction.js");
247 $self->render('bank_transactions/list',
248 title => t8('Bank transactions MT940'),
249 BANK_TRANSACTIONS => $bank_transactions,
250 PROPOSALS => $proposals,
251 bank_account => $bank_account,
252 ui_tab => scalar(@{ $proposals }) > 0 ? 1 : 0,
256 sub action_assign_invoice {
259 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
261 $self->render('bank_transactions/assign_invoice',
263 title => t8('Assign invoice'),);
266 sub action_create_invoice {
268 my %myconfig = %main::myconfig;
270 $self->transaction(SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id}));
272 my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(iban => $self->transaction->{remote_account_number});
273 my $use_vendor_filter = $self->transaction->{remote_account_number} && $vendor_of_transaction;
275 my $templates_ap = SL::DB::Manager::RecordTemplate->get_all(
276 where => [ template_type => 'ap_transaction' ],
277 with_objects => [ qw(employee vendor) ],
279 my $templates_gl = SL::DB::Manager::RecordTemplate->get_all(
280 query => [ template_type => 'gl_transaction',
281 chart_id => SL::DB::Manager::BankAccount->find_by(id => $self->transaction->local_bank_account_id)->chart_id,
283 with_objects => [ qw(employee record_template_items) ],
286 # pre filter templates_ap, if we have a vendor match (IBAN eq IBAN) - show and allow user to edit this via gui!
287 $templates_ap = [ grep { $_->vendor_id == $vendor_of_transaction->id } @{ $templates_ap } ] if $use_vendor_filter;
289 $self->callback($self->url_for(
291 'filter.bank_account' => $::form->{filter}->{bank_account},
292 'filter.todate' => $::form->{filter}->{todate},
293 'filter.fromdate' => $::form->{filter}->{fromdate},
297 'bank_transactions/create_invoice',
299 title => t8('Create invoice'),
300 TEMPLATES_GL => $use_vendor_filter && @{ $templates_ap } ? undef : $templates_gl,
301 TEMPLATES_AP => $templates_ap,
302 vendor_name => $use_vendor_filter && @{ $templates_ap } ? $vendor_of_transaction->name : undef,
306 sub action_ajax_payment_suggestion {
309 # based on a BankTransaction ID and a Invoice or PurchaseInvoice ID passed via $::form,
310 # create an HTML blob to be used by the js function add_invoices in templates/webpages/bank_transactions/list.html
311 # and return encoded as JSON
313 croak("Need bt_id") unless $::form->{bt_id};
315 my $invoice = SL::DB::Manager::Invoice->find_by( id => $::form->{prop_id} ) || SL::DB::Manager::PurchaseInvoice->find_by( id => $::form->{prop_id} );
317 croak("No valid invoice found") unless $invoice;
319 my $html = $self->render(
320 'bank_transactions/_payment_suggestion', { output => 0 },
321 bt_id => $::form->{bt_id},
325 $self->render(\ SL::JSON::to_json( { 'html' => "$html" } ), { layout => 0, type => 'json', process => 0 });
328 sub action_filter_templates {
331 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
333 my (@filter, @filter_ap);
335 # filter => gl and ap | filter_ap = ap (i.e. vendorname)
336 push @filter, ('template_name' => { ilike => '%' . $::form->{template} . '%' }) if $::form->{template};
337 push @filter, ('reference' => { ilike => '%' . $::form->{reference} . '%' }) if $::form->{reference};
338 push @filter_ap, ('vendor.name' => { ilike => '%' . $::form->{vendor} . '%' }) if $::form->{vendor};
339 push @filter_ap, @filter;
340 my $templates_gl = SL::DB::Manager::RecordTemplate->get_all(
341 query => [ template_type => 'gl_transaction',
342 chart_id => SL::DB::Manager::BankAccount->find_by(id => $self->transaction->local_bank_account_id)->chart_id,
343 (and => \@filter) x !!@filter
345 with_objects => [ qw(employee record_template_items) ],
348 my $templates_ap = SL::DB::Manager::RecordTemplate->get_all(
349 where => [ template_type => 'ap_transaction', (and => \@filter_ap) x !!@filter_ap ],
350 with_objects => [ qw(employee vendor) ],
352 $::form->{filter} //= {};
354 $self->callback($self->url_for(
356 'filter.bank_account' => $::form->{filter}->{bank_account},
357 'filter.todate' => $::form->{filter}->{todate},
358 'filter.fromdate' => $::form->{filter}->{fromdate},
361 my $output = $self->render(
362 'bank_transactions/_template_list',
364 TEMPLATES_AP => $templates_ap,
365 TEMPLATES_GL => $templates_gl,
368 $self->render(\to_json({ html => $output }), { type => 'json', process => 0 });
371 sub action_ajax_add_list {
374 my @where_sale = (amount => { ne => \'paid' });
375 my @where_purchase = (amount => { ne => \'paid' });
377 if ($::form->{invnumber}) {
378 push @where_sale, (invnumber => { ilike => like($::form->{invnumber})});
379 push @where_purchase, (invnumber => { ilike => like($::form->{invnumber})});
382 if ($::form->{amount}) {
383 push @where_sale, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
384 push @where_purchase, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
387 if ($::form->{vcnumber}) {
388 push @where_sale, ('customer.customernumber' => { ilike => like($::form->{vcnumber})});
389 push @where_purchase, ('vendor.vendornumber' => { ilike => like($::form->{vcnumber})});
392 if ($::form->{vcname}) {
393 push @where_sale, ('customer.name' => { ilike => like($::form->{vcname})});
394 push @where_purchase, ('vendor.name' => { ilike => like($::form->{vcname})});
397 if ($::form->{transdatefrom}) {
398 my $fromdate = $::locale->parse_date_to_object($::form->{transdatefrom});
399 if ( ref($fromdate) eq 'DateTime' ) {
400 push @where_sale, ('transdate' => { ge => $fromdate});
401 push @where_purchase, ('transdate' => { ge => $fromdate});
405 if ($::form->{transdateto}) {
406 my $todate = $::locale->parse_date_to_object($::form->{transdateto});
407 if ( ref($todate) eq 'DateTime' ) {
408 $todate->add(days => 1);
409 push @where_sale, ('transdate' => { lt => $todate});
410 push @where_purchase, ('transdate' => { lt => $todate});
414 my $all_open_ar_invoices = SL::DB::Manager::Invoice ->get_all(where => \@where_sale, with_objects => 'customer');
415 my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => \@where_purchase, with_objects => 'vendor');
417 my @all_open_invoices = @{ $all_open_ar_invoices };
418 # add ap invoices, filtering out subcent open amounts
419 push @all_open_invoices, grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
421 @all_open_invoices = sort { $a->id <=> $b->id } @all_open_invoices;
423 my $output = $self->render(
424 'bank_transactions/add_list',
426 INVOICES => \@all_open_invoices,
429 my %result = ( count => 0, html => $output );
431 $self->render(\to_json(\%result), { type => 'json', process => 0 });
434 sub action_ajax_accept_invoices {
437 my @selected_invoices;
438 foreach my $invoice_id (@{ $::form->{invoice_id} || [] }) {
439 my $invoice_object = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
440 push @selected_invoices, $invoice_object;
444 'bank_transactions/invoices',
446 INVOICES => \@selected_invoices,
447 bt_id => $::form->{bt_id},
454 return 0 if !$::form->{invoice_ids};
456 my %invoice_hash = %{ delete $::form->{invoice_ids} }; # each key (the bt line with a bt_id) contains an array of invoice_ids
458 # e.g. three partial payments with bt_ids 54, 55 and 56 for invoice with id 74:
471 # or if the payment with bt_id 44 is used to pay invoices with ids 50, 51 and 52
473 # '44' => [ '50', '51', 52' ]
476 $::form->{invoice_skontos} ||= {}; # hash of arrays containing the payment types, could be empty
478 # a bank_transaction may be assigned to several invoices, i.e. a customer
479 # might pay several open invoices with one transaction
485 if ( $::form->{proposal_ids} ) {
486 foreach (@{ $::form->{proposal_ids} }) {
487 my $bank_transaction_id = $_;
488 my $invoice_ids = $invoice_hash{$_};
489 push @{ $self->problems }, $self->save_single_bank_transaction(
490 bank_transaction_id => $bank_transaction_id,
491 invoice_ids => $invoice_ids,
492 sources => ($::form->{sources} // {})->{$_},
493 memos => ($::form->{memos} // {})->{$_},
495 $count += scalar( @{$invoice_ids} );
498 while ( my ($bank_transaction_id, $invoice_ids) = each(%invoice_hash) ) {
499 push @{ $self->problems }, $self->save_single_bank_transaction(
500 bank_transaction_id => $bank_transaction_id,
501 invoice_ids => $invoice_ids,
502 sources => [ map { $::form->{"sources_${bank_transaction_id}_${_}"} } @{ $invoice_ids } ],
503 memos => [ map { $::form->{"memos_${bank_transaction_id}_${_}"} } @{ $invoice_ids } ],
505 $count += scalar( @{$invoice_ids} );
508 my $max_count = $count;
509 foreach (@{ $self->problems }) {
510 $count-- if $_->{result} eq 'error';
512 return ($count, $max_count);
515 sub action_save_invoices {
517 my ($success_count, $max_count) = $self->save_invoices();
519 if ($success_count == $max_count) {
520 flash('ok', t8('#1 invoice(s) saved.', $success_count));
522 flash('error', t8('At least #1 invoice(s) not saved', $max_count - $success_count));
525 $self->action_list();
528 sub action_save_proposals {
531 if ( $::form->{proposal_ids} ) {
532 my $propcount = scalar(@{ $::form->{proposal_ids} });
533 if ( $propcount > 0 ) {
534 my $count = $self->save_invoices();
536 flash('ok', t8('#1 proposal(s) with #2 invoice(s) saved.', $propcount, $count));
539 $self->action_list();
543 sub save_single_bank_transaction {
544 my ($self, %params) = @_;
548 bank_transaction => SL::DB::Manager::BankTransaction->find_by(id => $params{bank_transaction_id}),
552 if (!$data{bank_transaction}) {
556 message => $::locale->text('The ID #1 is not a valid database ID.', $data{bank_transaction_id}),
560 my $bank_transaction = $data{bank_transaction};
562 if ($bank_transaction->closed_period) {
566 message => $::locale->text('Cannot post payment for a closed period!'),
572 my $bt_id = $data{bank_transaction_id};
573 my $sign = $bank_transaction->amount < 0 ? -1 : 1;
574 my $payment_received = $bank_transaction->amount > 0;
575 my $payment_sent = $bank_transaction->amount < 0;
578 foreach my $invoice_id (@{ $params{invoice_ids} }) {
579 my $invoice = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
584 message => $::locale->text("The ID #1 is not a valid database ID.", $invoice_id),
587 push @{ $data{invoices} }, $invoice;
590 if ( $payment_received
591 && any { ( $_->is_sales && ($_->amount < 0))
592 || (!$_->is_sales && ($_->amount > 0))
593 } @{ $data{invoices} }) {
597 message => $::locale->text("Received payments can only be posted for sales invoices and purchase credit notes."),
602 && any { ( $_->is_sales && ($_->amount > 0))
603 || (!$_->is_sales && ($_->amount < 0) && ($_->invoice_type eq 'purchase_invoice'))
604 } @{ $data{invoices} }) {
608 message => $::locale->text("Sent payments can only be posted for purchase invoices and sales credit notes."),
612 my $max_invoices = scalar(@{ $data{invoices} });
615 foreach my $invoice (@{ $data{invoices} }) {
616 my $source = ($data{sources} // [])->[$n_invoices];
617 my $memo = ($data{memos} // [])->[$n_invoices];
620 # safety check invoice open
621 croak("Invoice closed. Cannot proceed.") unless ($invoice->open_amount);
623 if ( ($payment_sent && $bank_transaction->not_assigned_amount >= 0)
624 || ($payment_received && $bank_transaction->not_assigned_amount <= 0)) {
628 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."),
632 my ($payment_type, $free_skonto_amount);
633 if ( defined $::form->{invoice_skontos}->{"$bt_id"} ) {
634 $payment_type = shift(@{ $::form->{invoice_skontos}->{"$bt_id"} });
636 $payment_type = 'without_skonto';
639 if ($payment_type eq 'free_skonto') {
640 # parse user input > 0
641 if ($::form->parse_amount(\%::myconfig, $::form->{"free_skonto_amount"}->{"$bt_id"}{$invoice->id}) > 0) {
642 $free_skonto_amount = $::form->parse_amount(\%::myconfig, $::form->{"free_skonto_amount"}->{"$bt_id"}{$invoice->id});
647 message => $::locale->text("Free skonto amount has to be a positive number."),
652 # TODO rewrite this: really booked amount should be a return value of Payment.pm
653 # also this controller shouldnt care about how to calc skonto. we simply delegate the
654 # payment_type to the helper and get the corresponding bank_transaction values back
655 # hotfix to get the signs right - compare absolute values and later set the signs
656 # should be better done elsewhere - changing not_assigned_amount to abs feels seriously bogus
658 my $open_amount = $payment_type eq 'with_skonto_pt' ? $invoice->amount_less_skonto : $invoice->open_amount;
659 $open_amount = abs($open_amount);
660 $open_amount -= $free_skonto_amount if ($payment_type eq 'free_skonto');
661 my $not_assigned_amount = abs($bank_transaction->not_assigned_amount);
662 my $amount_for_booking = ($open_amount < $not_assigned_amount) ? $open_amount : $not_assigned_amount;
663 my $amount_for_payment = $amount_for_booking;
665 # get the right direction for the payment bookings (all amounts < 0 are stornos, credit notes or negative ap)
666 $amount_for_payment *= -1 if $invoice->amount < 0;
667 $free_skonto_amount *= -1 if ($free_skonto_amount && $invoice->amount < 0);
668 # get the right direction for the bank transaction
669 $amount_for_booking *= $sign;
671 $bank_transaction->invoice_amount($bank_transaction->invoice_amount + $amount_for_booking);
673 # ... and then pay the invoice
674 my @acc_ids = $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
675 trans_id => $invoice->id,
676 amount => $amount_for_payment,
677 payment_type => $payment_type,
680 skonto_amount => $free_skonto_amount,
681 transdate => $bank_transaction->valutadate->to_kivitendo);
682 # ... and record the origin via BankTransactionAccTrans
683 if (scalar(@acc_ids) < 2) {
687 message => $::locale->text("Unable to book transactions for bank purpose #1", $bank_transaction->purpose),
690 foreach my $acc_trans_id (@acc_ids) {
691 my $id_type = $invoice->is_sales ? 'ar' : 'ap';
693 acc_trans_id => $acc_trans_id,
694 bank_transaction_id => $bank_transaction->id,
695 $id_type => $invoice->id,
697 SL::DB::BankTransactionAccTrans->new(%props_acc)->save;
699 # Record a record link from the bank transaction to the invoice
701 from_table => 'bank_transactions',
703 to_table => $invoice->is_sales ? 'ar' : 'ap',
704 to_id => $invoice->id,
706 SL::DB::RecordLink->new(%props)->save;
708 # "close" a sepa_export_item if it exists
709 # code duplicated in action_save_proposals!
710 # currently only works, if there is only exactly one open sepa_export_item
711 if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
712 if ( scalar @$seis == 1 ) {
713 # moved the execution and the check for sepa_export into a method,
714 # this isn't part of a transaction, though
715 $seis->[0]->set_executed if $invoice->id == $seis->[0]->arap_id;
720 $bank_transaction->save;
722 # 'undef' means 'no error' here.
727 my $rez = $data{bank_transaction}->db->with_transaction(sub {
729 $error = $worker->();
740 # Rollback Fehler nicht weiterreichen
742 # aber einen rollback von hand
743 $::lxdebug->message(LXDebug->DEBUG2(),"finish worker with ". ($error ? $error->{result} : '-'));
744 $data{bank_transaction}->db->dbh->rollback if $error && $error->{result} eq 'error';
747 return grep { $_ } ($error, @warnings);
749 sub action_unlink_bank_transaction {
750 my ($self, %params) = @_;
752 croak("No bank transaction ids") unless scalar @{ $::form->{ids}} > 0;
756 foreach my $bt_id (@{ $::form->{ids}} ) {
758 my $bank_transaction = SL::DB::Manager::BankTransaction->find_by(id => $bt_id);
759 croak("No valid bank transaction found") unless (ref($bank_transaction) eq 'SL::DB::BankTransaction');
760 croak t8('Cannot unlink payment for a closed period!') if $bank_transaction->closed_period;
762 # everything in one transaction
763 my $rez = $bank_transaction->db->with_transaction(sub {
764 # 1. remove all reconciliations (due to underlying trigger, this has to be the first step)
765 my $rec_links = SL::DB::Manager::ReconciliationLink->get_all(where => [ bank_transaction_id => $bt_id ]);
766 $_->delete for @{ $rec_links };
769 foreach my $acc_trans_id_entry (@{ SL::DB::Manager::BankTransactionAccTrans->get_all(where => [bank_transaction_id => $bt_id ] )}) {
771 my $acc_trans = SL::DB::Manager::AccTransaction->get_all(where => [acc_trans_id => $acc_trans_id_entry->acc_trans_id]);
773 # save trans_id and type
774 die "no type" unless ($acc_trans_id_entry->ar_id || $acc_trans_id_entry->ap_id || $acc_trans_id_entry->gl_id);
775 $trans_ids{$acc_trans_id_entry->ar_id} = 'ar' if $acc_trans_id_entry->ar_id;
776 $trans_ids{$acc_trans_id_entry->ap_id} = 'ap' if $acc_trans_id_entry->ap_id;
777 $trans_ids{$acc_trans_id_entry->gl_id} = 'gl' if $acc_trans_id_entry->gl_id;
778 # 2. all good -> ready to delete acc_trans and bt_acc link
779 $acc_trans_id_entry->delete;
780 $_->delete for @{ $acc_trans };
782 # 3. update arap.paid (may not be 0, yet)
783 # or in case of gl, delete whole entry
784 while (my ($trans_id, $type) = each %trans_ids) {
786 SL::DB::Manager::GLTransaction->delete_all(where => [ id => $trans_id ]);
789 die ("invalid type") unless $type =~ m/^(ar|ap)$/;
791 # recalc and set paid via database query
792 my $query = qq|UPDATE $type SET paid =
793 (SELECT COALESCE(abs(sum(amount)),0) FROM acc_trans
795 AND chart_link ilike '%paid%')
798 die if (do_query($::form, $bank_transaction->db->dbh, $query, $trans_id, $trans_id) == -1);
800 # 4. and delete all (if any) record links
801 my $rl = SL::DB::Manager::RecordLink->delete_all(where => [ from_id => $bt_id, from_table => 'bank_transactions' ]);
803 # 5. finally reset this bank transaction
804 $bank_transaction->invoice_amount(0);
805 $bank_transaction->cleared(0);
806 $bank_transaction->save;
807 # 6. and add a log entry in history_erp
808 SL::DB::History->new(
809 trans_id => $bank_transaction->id,
810 snumbers => 'bank_transaction_unlink_' . $bank_transaction->id,
811 employee_id => SL::DB::Manager::Employee->current->id,
812 what_done => 'bank_transaction',
813 addition => 'UNLINKED',
818 }) || die t8('error while unlinking payment #1 : ', $bank_transaction->purpose) . $bank_transaction->db->error . "\n";
823 flash('ok', t8('#1 bank transaction bookings undone.', $success_count));
824 $self->action_list_all() unless $params{testcase};
831 $::auth->assert('bank_transaction');
838 sub make_filter_summary {
841 my $filter = $::form->{filter} || {};
845 [ $filter->{"transdate:date::ge"}, $::locale->text('Transdate') . " " . $::locale->text('From Date') ],
846 [ $filter->{"transdate:date::le"}, $::locale->text('Transdate') . " " . $::locale->text('To Date') ],
847 [ $filter->{"valutadate:date::ge"}, $::locale->text('Valutadate') . " " . $::locale->text('From Date') ],
848 [ $filter->{"valutadate:date::le"}, $::locale->text('Valutadate') . " " . $::locale->text('To Date') ],
849 [ $filter->{"amount:number"}, $::locale->text('Amount') ],
850 [ $filter->{"bank_account_id:integer"}, $::locale->text('Local bank account') ],
851 [ $filter->{"remote_name:substr::ilike"}, $::locale->text('Remote name') ],
855 push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
858 $self->{filter_summary} = join ', ', @filter_strings;
864 my $callback = $self->models->get_callback;
866 my $report = SL::ReportGenerator->new(\%::myconfig, $::form);
867 $self->{report} = $report;
869 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);
870 my @sortable = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount purpose local_account_number local_bank_code);
873 ids => { raw_header_data => checkbox_tag("", id => "check_all", checkall => "[data-checkall=1]"),
875 raw_data => sub { if (@{ $_[0]->linked_invoices }) {
876 if ($_[0]->closed_period) {
877 html_tag('text', "X"); #, tooltip => t8('Bank Transaction is in a closed period.')),
879 checkbox_tag("ids[]", value => $_[0]->id, "data-checkall" => 1);
882 transdate => { sub => sub { $_[0]->transdate_as_date } },
883 valutadate => { sub => sub { $_[0]->valutadate_as_date } },
885 remote_account_number => { },
886 remote_bank_code => { },
887 amount => { sub => sub { $_[0]->amount_as_number },
889 invoice_amount => { sub => sub { $_[0]->invoice_amount_as_number },
891 invoices => { sub => sub { my @invnumbers; for my $obj (@{ $_[0]->linked_invoices }) {
892 next unless $obj; push @invnumbers, $obj->invnumber } return \@invnumbers } },
893 currency => { sub => sub { $_[0]->currency->name } },
895 local_account_number => { sub => sub { $_[0]->local_bank_account->account_number } },
896 local_bank_code => { sub => sub { $_[0]->local_bank_account->bank_code } },
897 local_bank_name => { sub => sub { $_[0]->local_bank_account->name } },
901 map { $column_defs{$_}->{text} ||= $::locale->text( $self->models->get_sort_spec->{$_}->{title} ) } keys %column_defs;
903 $report->set_options(
904 std_column_visibility => 1,
905 controller_class => 'BankTransaction',
906 output_format => 'HTML',
907 top_info_text => $::locale->text('Bank transactions'),
908 title => $::locale->text('Bank transactions'),
909 allow_pdf_export => 1,
910 allow_csv_export => 1,
912 $report->set_columns(%column_defs);
913 $report->set_column_order(@columns);
914 $report->set_export_options(qw(list_all filter));
915 $report->set_options_from_form;
916 $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
917 $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
919 my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted();
921 $report->set_options(
922 raw_top_info_text => $self->render('bank_transactions/report_top', { output => 0 }, BANK_ACCOUNTS => $bank_accounts),
923 raw_bottom_info_text => $self->render('bank_transactions/report_bottom', { output => 0 }),
927 sub init_problems { [] }
932 SL::Controller::Helper::GetModels->new(
937 dir => 0, # 1 = ASC, 0 = DESC : default sort is newest at top
940 transdate => t8('Transdate'),
941 remote_name => t8('Remote name'),
942 amount => t8('Amount'),
943 invoice_amount => t8('Assigned'),
944 invoices => t8('Linked invoices'),
945 valutadate => t8('Valutadate'),
946 remote_account_number => t8('Remote account number'),
947 remote_bank_code => t8('Remote bank code'),
948 currency => t8('Currency'),
949 purpose => t8('Purpose'),
950 local_account_number => t8('Local account number'),
951 local_bank_code => t8('Local bank code'),
952 local_bank_name => t8('Bank account'),
954 with_objects => [ 'local_bank_account', 'currency' ],
958 sub load_ap_record_template_url {
959 my ($self, $template) = @_;
961 return $self->url_for(
962 controller => 'ap.pl',
963 action => 'load_record_template',
965 'form_defaults.amount_1' => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
966 'form_defaults.transdate' => $self->transaction->transdate_as_date,
967 'form_defaults.duedate' => $self->transaction->transdate_as_date,
968 'form_defaults.no_payment_bookings' => 1,
969 'form_defaults.paid_1_suggestion' => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
970 'form_defaults.AP_paid_1_suggestion' => $self->transaction->local_bank_account->chart->accno,
971 'form_defaults.callback' => $self->callback,
975 sub load_gl_record_template_url {
976 my ($self, $template) = @_;
978 return $self->url_for(
979 controller => 'gl.pl',
980 action => 'load_record_template',
982 'form_defaults.amount_1' => abs($self->transaction->not_assigned_amount), # always positive
983 'form_defaults.transdate' => $self->transaction->transdate_as_date,
984 'form_defaults.callback' => $self->callback,
985 'form_defaults.bt_id' => $self->transaction->id,
986 'form_defaults.bt_chart_id' => $self->transaction->local_bank_account->chart->id,
987 'form_defaults.description' => $self->transaction->purpose,
991 sub setup_search_action_bar {
992 my ($self, %params) = @_;
994 for my $bar ($::request->layout->get('actionbar')) {
998 submit => [ '#search_form', { action => 'BankTransaction/list' } ],
999 accesskey => 'enter',
1005 sub setup_list_all_action_bar {
1006 my ($self, %params) = @_;
1008 for my $bar ($::request->layout->get('actionbar')) {
1011 action => [ t8('Actions') ],
1013 t8('Unlink bank transactions'),
1014 submit => [ '#form', { action => 'BankTransaction/unlink_bank_transaction' } ],
1015 checks => [ [ 'kivi.check_if_entries_selected', '[name="ids[]"]' ] ],
1016 disabled => $::instance_conf->get_payments_changeable ? t8('Cannot safely unlink bank transactions, please set the posting configuration for payments to unchangeable.') : undef,
1021 submit => [ '#filter_form', { action => 'BankTransaction/list_all' } ],
1022 accesskey => 'enter',
1037 SL::Controller::BankTransaction - Posting payments to invoices from
1038 bank transactions imported earlier
1044 =item C<save_single_bank_transaction %params>
1046 Takes a bank transaction ID (as parameter C<bank_transaction_id> and
1047 tries to post its amount to a certain number of invoices (parameter
1048 C<invoice_ids>, an array ref of database IDs to purchase or sales
1051 This method handles already partly assigned bank transactions.
1053 This method cannot handle already partly assigned bank transactions, i.e.
1054 a bank transaction that has a invoice_amount <> 0 but not the fully
1055 transaction amount (invoice_amount == amount).
1057 If the amount of the bank transaction is higher than the sum of
1058 the assigned invoices (1 .. n) the bank transaction will only be
1061 The whole function is wrapped in a database transaction. If an
1062 exception occurs the bank transaction is not posted at all. The same
1063 is true if the code detects an error during the execution, e.g. a bank
1064 transaction that's already been posted earlier. In both cases the
1065 database transaction will be rolled back.
1067 If warnings but not errors occur the database transaction is still
1070 The return value is an error object or C<undef> if the function
1071 succeeded. The calling function will collect all warnings and errors
1072 and display them in a nicely formatted table if any occurred.
1074 An error object is a hash reference containing the following members:
1078 =item * C<result> — can be either C<warning> or C<error>. Warnings are
1079 displayed slightly different than errors.
1081 =item * C<message> — a human-readable message included in the list of
1082 errors meant as the description of why the problem happened
1084 =item * C<bank_transaction_id>, C<invoice_ids> — the same parameters
1085 that the function was called with
1087 =item * C<bank_transaction> — the database object
1088 (C<SL::DB::BankTransaction>) corresponding to C<bank_transaction_id>
1090 =item * C<invoices> — an array ref of the database objects (either
1091 C<SL::DB::Invoice> or C<SL::DB::PurchaseInvoice>) corresponding to
1096 =item C<action_unlink_bank_transaction>
1098 Takes one or more bank transaction ID (as parameter C<form::ids>) and
1099 tries to revert all payment bookings including already cleared bookings.
1101 This method won't undo payments that are in a closed period and assumes
1102 that payments are not manually changed, i.e. only imported payments.
1104 GL-records will be deleted completely if a bank transaction was the source.
1106 TODO: we still rely on linked_records for the check boxes
1112 Niclas Zimmermann E<lt>niclas@kivitendo-premium.deE<gt>,
1113 Geoffrey Richardson E<lt>information@richardson-bueren.deE<gt>