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;
22 use SL::DB::AccTransaction;
25 use SL::DB::BankAccount;
26 use SL::DB::SepaExportItem;
27 use SL::DBUtils qw(like);
30 use List::MoreUtils qw(any);
31 use List::Util qw(max);
33 use Rose::Object::MakeMethods::Generic
35 'scalar --get_set_init' => [ qw(models problems) ],
38 __PACKAGE__->run_before('check_auth');
48 my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted( query => [ obsolete => 0 ] );
50 $self->render('bank_transactions/search',
51 BANK_ACCOUNTS => $bank_accounts);
57 $self->make_filter_summary;
58 $self->prepare_report;
60 $self->report_generator_list_objects(report => $self->{report}, objects => $self->models->get);
66 if (!$::form->{filter}{bank_account}) {
67 flash('error', t8('No bank account chosen!'));
72 my $sort_by = $::form->{sort_by} || 'transdate';
73 $sort_by = 'transdate' if $sort_by eq 'proposal';
74 $sort_by .= $::form->{sort_dir} ? ' DESC' : ' ASC';
76 my $fromdate = $::locale->parse_date_to_object($::form->{filter}->{fromdate});
77 my $todate = $::locale->parse_date_to_object($::form->{filter}->{todate});
78 $todate->add( days => 1 ) if $todate;
81 push @where, (transdate => { ge => $fromdate }) if ($fromdate);
82 push @where, (transdate => { lt => $todate }) if ($todate);
83 my $bank_account = SL::DB::Manager::BankAccount->find_by( id => $::form->{filter}{bank_account} );
84 # bank_transactions no younger than starting date,
85 # including starting date (same search behaviour as fromdate)
86 # but OPEN invoices to be matched may be from before
87 if ( $bank_account->reconciliation_starting_date ) {
88 push @where, (transdate => { ge => $bank_account->reconciliation_starting_date });
91 my $bank_transactions = SL::DB::Manager::BankTransaction->get_all(
92 with_objects => [ 'local_bank_account', 'currency' ],
96 amount => {ne => \'invoice_amount'},
97 local_bank_account_id => $::form->{filter}{bank_account},
101 $main::lxdebug->message(LXDebug->DEBUG2(),"count bt=".scalar(@{$bank_transactions}." bank_account=".$bank_account->id." chart=".$bank_account->chart_id));
103 my $all_open_ar_invoices = SL::DB::Manager::Invoice ->get_all(where => [amount => { gt => \'paid' }], with_objects => ['customer','payment_terms']);
104 my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => [amount => { gt => \'paid' }], with_objects => ['vendor' ,'payment_terms']);
105 my $all_open_sepa_export_items = SL::DB::Manager::SepaExportItem->get_all(where => [chart_id => $bank_account->chart_id ,
106 'sepa_export.executed' => 0, 'sepa_export.closed' => 0 ], with_objects => ['sepa_export']);
107 $main::lxdebug->message(LXDebug->DEBUG2(),"count sepaexport=".scalar(@{$all_open_sepa_export_items}));
109 my @all_open_invoices;
110 # filter out invoices with less than 1 cent outstanding
111 push @all_open_invoices, grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ar_invoices };
112 push @all_open_invoices, grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
113 $main::lxdebug->message(LXDebug->DEBUG2(),"bank_account=".$::form->{filter}{bank_account}." invoices: ".scalar(@{ $all_open_ar_invoices }).
114 " + ".scalar(@{ $all_open_ap_invoices })." transactions=".scalar(@{ $bank_transactions }));
116 my @all_sepa_invoices;
117 my @all_non_sepa_invoices;
119 # first collect sepa export items to open invoices
120 foreach my $open_invoice (@all_open_invoices){
121 # my @items = grep { $_->ap_id == $open_invoice->id || $_->ar_id == $open_invoice->id } @{$all_open_sepa_export_items};
122 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount,2);
123 $open_invoice->{skonto_type} = 'without_skonto';
124 foreach ( @{$all_open_sepa_export_items}) {
125 if ( $_->ap_id == $open_invoice->id || $_->ar_id == $open_invoice->id ) {
126 #$main::lxdebug->message(LXDebug->DEBUG2(),"exitem=".$_->id." for invoice ".$open_invoice->id);
127 $open_invoice->{sepa_export_item} = $_ ;
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;
133 push ( @{ $sepa_exports{$_->sepa_export_id}->{invoices}} , $open_invoice );
134 #$main::lxdebug->message(LXDebug->DEBUG2(),"amount for export id ".$_->sepa_export_id." = ".
135 # $sepa_exports{$_->sepa_export_id}->{amount}." count = ".
136 # $sepa_exports{$_->sepa_export_id}->{count}." is_ar = ".
137 # $sepa_exports{$_->sepa_export_id}->{is_ar} );
138 push @all_sepa_invoices , $open_invoice;
141 push @all_non_sepa_invoices , $open_invoice if ! $open_invoice->{sepa_export_item};
144 # try to match each bank_transaction with each of the possible open invoices
146 @all_open_invoices = @all_non_sepa_invoices;
149 foreach my $bt (@{ $bank_transactions }) {
150 ## 5 Stellen hinter dem Komma auf 2 Stellen reduzieren
151 $bt->amount($bt->amount*1);
152 $bt->invoice_amount($bt->invoice_amount*1);
153 $main::lxdebug->message(LXDebug->DEBUG2(),"BT ".$bt->id." amount=".$bt->amount." invoice_amount=".$bt->invoice_amount." remote=". $bt->{remote_name});
155 $bt->{proposals} = [];
157 $bt->{remote_name} .= $bt->{remote_name_1} if $bt->{remote_name_1};
159 if ( $self->is_collective_transaction($bt) ) {
160 foreach ( keys %sepa_exports) {
161 my $factor = ($sepa_exports{$_}->{is_ar}>0?1:-1);
162 #$main::lxdebug->message(LXDebug->DEBUG2(),"Exp ID=".$_." factor=".$factor." compare sum amount ".($sepa_exports{$_}->{amount} *1) ." == ".($bt->amount * $factor));
163 if ( $bt->transactioncode eq '191' && ($sepa_exports{$_}->{amount} * 1) eq ($bt->amount * $factor) ) {
165 $bt->{proposals} = $sepa_exports{$_}->{invoices} ;
166 $sepa_exports{$_}->{proposed}=1;
167 #$main::lxdebug->message(LXDebug->DEBUG2(),"has ".scalar($bt->{proposals})." invoices");
168 push(@proposals, $bt);
173 next unless $bt->{remote_name}; # bank has no name, usually fees, use create invoice to assign
175 foreach ( keys %sepa_exports) {
176 my $factor = ($sepa_exports{$_}->{is_ar}>0?1:-1);
177 #$main::lxdebug->message(LXDebug->DEBUG2(),"exp count=".$sepa_exports{$_}->{count}." factor=".$factor." proposed=".$sepa_exports{$_}->{proposed});
178 if ( $sepa_exports{$_}->{count} == 1 ) {
179 my $oinvoice = @{ $sepa_exports{$_}->{invoices}}[0];
180 my $eitem = $sepa_exports{$_}->{item};
181 $eitem->amount($eitem->amount*1);
182 #$main::lxdebug->message(LXDebug->DEBUG2(),"remote account '".$bt->{remote_account_number}."' bt_amount=". ($bt->amount * $factor));
183 #$main::lxdebug->message(LXDebug->DEBUG2(),"compare with '".$eitem->vc_iban."' amount=".$eitem->amount);
184 if ( $bt->{remote_account_number} eq $eitem->vc_iban && $eitem->amount eq ($bt->amount * $factor)) {
186 $bt->{proposals} = $sepa_exports{$_}->{invoices} ;
187 #$main::lxdebug->message(LXDebug->DEBUG2(),"found invoice");
188 $sepa_exports{$_}->{proposed}=1;
189 push(@proposals, $bt);
195 # try to match the current $bt to each of the open_invoices, saving the
196 # results of get_agreement_with_invoice in $open_invoice->{agreement} and
197 # $open_invoice->{rule_matches}.
199 # The values are overwritten each time a new bt is checked, so at the end
200 # of each bt the likely results are filtered and those values are stored in
201 # the arrays $bt->{proposals} and $bt->{rule_matches}, and the agreement
202 # score is stored in $bt->{agreement}
204 foreach my $open_invoice (@all_open_invoices){
205 ($open_invoice->{agreement}, $open_invoice->{rule_matches}) = $bt->get_agreement_with_invoice($open_invoice);
206 # $main::lxdebug->message(LXDebug->DEBUG2(),"agreement=".$open_invoice->{agreement}." rules matches=".$open_invoice->{rule_matches});
210 my $min_agreement = 3; # suggestions must have at least this score
212 my $max_agreement = max map { $_->{agreement} } @all_open_invoices;
214 # add open_invoices with highest agreement into array $bt->{proposals}
215 if ( $max_agreement >= $min_agreement ) {
216 $bt->{proposals} = [ grep { $_->{agreement} == $max_agreement } @all_open_invoices ];
217 $bt->{agreement} = $max_agreement; #scalar @{ $bt->{proposals} } ? $agreement + 1 : '';
219 # store the rule_matches in a separate array, so they can be displayed in template
220 foreach ( @{ $bt->{proposals} } ) {
221 push(@{$bt->{rule_matches}}, $_->{rule_matches});
227 # separate filter for proposals (second tab, agreement >= 5 and exactly one match)
228 # to qualify as a proposal there has to be
229 # * agreement >= 5 TODO: make threshold configurable in configuration
230 # * there must be only one exact match
231 # * depending on whether sales or purchase the amount has to have the correct sign (so Gutschriften don't work?)
232 my $proposal_threshold = 5;
233 my @otherproposals = grep {
234 ($_->{agreement} >= $proposal_threshold)
235 && (1 == scalar @{ $_->{proposals} })
236 && (@{ $_->{proposals} }[0]->is_sales ? abs(@{ $_->{proposals} }[0]->amount - $_->amount) < 0.01
237 : abs(@{ $_->{proposals} }[0]->amount + $_->amount) < 0.01)
238 } @{ $bank_transactions };
240 push ( @proposals, @otherproposals);
242 # sort bank transaction proposals by quality (score) of proposal
243 $bank_transactions = [ sort { $a->{agreement} <=> $b->{agreement} } @{ $bank_transactions } ] if $::form->{sort_by} eq 'proposal' and $::form->{sort_dir} == 1;
244 $bank_transactions = [ sort { $b->{agreement} <=> $a->{agreement} } @{ $bank_transactions } ] if $::form->{sort_by} eq 'proposal' and $::form->{sort_dir} == 0;
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});
271 my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(account_number => $self->{transaction}->{remote_account_number});
273 my $use_vendor_filter = $self->{transaction}->{remote_account_number} && $vendor_of_transaction;
275 my $drafts = SL::DB::Manager::Draft->get_all(where => [ module => 'ap'] , with_objects => 'employee');
279 foreach my $draft ( @{ $drafts } ) {
280 my $draft_as_object = YAML::Load($draft->form);
281 my $vendor = SL::DB::Manager::Vendor->find_by(id => $draft_as_object->{vendor_id});
282 $draft->{vendor} = $vendor->name;
283 $draft->{vendor_id} = $vendor->id;
284 push @filtered_drafts, $draft;
288 @filtered_drafts = grep { $_->{vendor_id} == $vendor_of_transaction->id } @filtered_drafts if $use_vendor_filter;
290 my $all_vendors = SL::DB::Manager::Vendor->get_all();
291 my $callback = $self->url_for(action => 'list',
292 'filter.bank_account' => $::form->{filter}->{bank_account},
293 'filter.todate' => $::form->{filter}->{todate},
294 'filter.fromdate' => $::form->{filter}->{fromdate});
297 'bank_transactions/create_invoice',
299 title => t8('Create invoice'),
300 DRAFTS => \@filtered_drafts,
301 vendor_id => $use_vendor_filter ? $vendor_of_transaction->id : undef,
302 vendor_name => $use_vendor_filter ? $vendor_of_transaction->name : undef,
303 ALL_VENDORS => $all_vendors,
304 limit => $myconfig{vclimit},
305 callback => $callback,
309 sub action_ajax_payment_suggestion {
312 # based on a BankTransaction ID and a Invoice or PurchaseInvoice ID passed via $::form,
313 # create an HTML blob to be used by the js function add_invoices in templates/webpages/bank_transactions/list.html
314 # and return encoded as JSON
316 my $bt = SL::DB::Manager::BankTransaction->find_by( id => $::form->{bt_id} );
317 my $invoice = SL::DB::Manager::Invoice->find_by( id => $::form->{prop_id} ) || SL::DB::Manager::PurchaseInvoice->find_by( id => $::form->{prop_id} );
319 die unless $bt and $invoice;
321 my @select_options = $invoice->get_payment_select_options_for_bank_transaction($::form->{bt_id});
324 $html .= SL::Presenter->input_tag('invoice_ids.' . $::form->{bt_id} . '[]', $::form->{prop_id} , type => 'hidden');
325 $html .= SL::Presenter->escape(t8('Invno.') . ': ' . $invoice->invnumber . ' ');
326 $html .= SL::Presenter->escape(t8('Open amount') . ': ' . $::form->format_amount(\%::myconfig, $invoice->open_amount, 2) . ' ');
327 $html .= SL::Presenter->select_tag('invoice_skontos.' . $::form->{bt_id} . '[]',
329 value_key => 'payment_type',
330 title_key => 'display' )
332 $html .= '<a href=# onclick="delete_invoice(' . $::form->{bt_id} . ',' . $::form->{prop_id} . ');">x</a>';
333 $html = SL::Presenter->html_tag('div', $html, id => $::form->{bt_id} . '.' . $::form->{prop_id});
335 $self->render(\ SL::JSON::to_json( { 'html' => $html } ), { layout => 0, type => 'json', process => 0 });
338 sub action_filter_drafts {
341 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
342 my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(account_number => $self->{transaction}->{remote_account_number});
344 my $drafts = SL::DB::Manager::Draft->get_all(with_objects => 'employee');
348 foreach my $draft ( @{ $drafts } ) {
349 my $draft_as_object = YAML::Load($draft->form);
350 next unless $draft_as_object->{vendor_id}; # we cannot filter for vendor name, if this is a gl draft
352 my $vendor = SL::DB::Manager::Vendor->find_by(id => $draft_as_object->{vendor_id});
353 $draft->{vendor} = $vendor->name;
354 $draft->{vendor_id} = $vendor->id;
356 push @filtered_drafts, $draft;
359 my $vendor_name = $::form->{vendor};
360 my $vendor_id = $::form->{vendor_id};
363 @filtered_drafts = grep { $_->{vendor_id} == $vendor_id } @filtered_drafts if $vendor_id;
364 @filtered_drafts = grep { $_->{vendor} =~ /$vendor_name/i } @filtered_drafts if $vendor_name;
366 my $output = $self->render(
367 'bank_transactions/filter_drafts',
369 DRAFTS => \@filtered_drafts,
372 my %result = ( count => 0, html => $output );
374 $self->render(\to_json(\%result), { type => 'json', process => 0 });
377 sub action_ajax_add_list {
380 my @where_sale = (amount => { ne => \'paid' });
381 my @where_purchase = (amount => { ne => \'paid' });
383 if ($::form->{invnumber}) {
384 push @where_sale, (invnumber => { ilike => like($::form->{invnumber})});
385 push @where_purchase, (invnumber => { ilike => like($::form->{invnumber})});
388 if ($::form->{amount}) {
389 push @where_sale, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
390 push @where_purchase, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
393 if ($::form->{vcnumber}) {
394 push @where_sale, ('customer.customernumber' => { ilike => like($::form->{vcnumber})});
395 push @where_purchase, ('vendor.vendornumber' => { ilike => like($::form->{vcnumber})});
398 if ($::form->{vcname}) {
399 push @where_sale, ('customer.name' => { ilike => like($::form->{vcname})});
400 push @where_purchase, ('vendor.name' => { ilike => like($::form->{vcname})});
403 if ($::form->{transdatefrom}) {
404 my $fromdate = $::locale->parse_date_to_object($::form->{transdatefrom});
405 if ( ref($fromdate) eq 'DateTime' ) {
406 push @where_sale, ('transdate' => { ge => $fromdate});
407 push @where_purchase, ('transdate' => { ge => $fromdate});
411 if ($::form->{transdateto}) {
412 my $todate = $::locale->parse_date_to_object($::form->{transdateto});
413 if ( ref($todate) eq 'DateTime' ) {
414 $todate->add(days => 1);
415 push @where_sale, ('transdate' => { lt => $todate});
416 push @where_purchase, ('transdate' => { lt => $todate});
420 my $all_open_ar_invoices = SL::DB::Manager::Invoice ->get_all(where => \@where_sale, with_objects => 'customer');
421 my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => \@where_purchase, with_objects => 'vendor');
423 my @all_open_invoices = @{ $all_open_ar_invoices };
424 # add ap invoices, filtering out subcent open amounts
425 push @all_open_invoices, grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
427 @all_open_invoices = sort { $a->id <=> $b->id } @all_open_invoices;
429 my $output = $self->render(
430 'bank_transactions/add_list',
432 INVOICES => \@all_open_invoices,
435 my %result = ( count => 0, html => $output );
437 $self->render(\to_json(\%result), { type => 'json', process => 0 });
440 sub action_ajax_accept_invoices {
443 my @selected_invoices;
444 foreach my $invoice_id (@{ $::form->{invoice_id} || [] }) {
445 my $invoice_object = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
446 push @selected_invoices, $invoice_object;
450 'bank_transactions/invoices',
452 INVOICES => \@selected_invoices,
453 bt_id => $::form->{bt_id},
460 return 0 if !$::form->{invoice_ids};
462 my %invoice_hash = %{ delete $::form->{invoice_ids} }; # each key (the bt line with a bt_id) contains an array of invoice_ids
464 # e.g. three partial payments with bt_ids 54, 55 and 56 for invoice with id 74:
477 # or if the payment with bt_id 44 is used to pay invoices with ids 50, 51 and 52
479 # '44' => [ '50', '51', 52' ]
482 $::form->{invoice_skontos} ||= {}; # hash of arrays containing the payment types, could be empty
484 # a bank_transaction may be assigned to several invoices, i.e. a customer
485 # might pay several open invoices with one transaction
491 if ( $::form->{proposal_ids} ) {
492 foreach (@{ $::form->{proposal_ids} }) {
493 my $bank_transaction_id = $_;
494 my $invoice_ids = $invoice_hash{$_};
495 push @{ $self->problems }, $self->save_single_bank_transaction(
496 bank_transaction_id => $bank_transaction_id,
497 invoice_ids => $invoice_ids,
499 $count += scalar( @{$invoice_ids} );
502 while ( my ($bank_transaction_id, $invoice_ids) = each(%invoice_hash) ) {
503 push @{ $self->problems }, $self->save_single_bank_transaction(
504 bank_transaction_id => $bank_transaction_id,
505 invoice_ids => $invoice_ids,
507 $count += scalar( @{$invoice_ids} );
513 sub action_save_invoices {
515 my $count = $self->save_invoices();
517 flash('ok', t8('#1 invoice(s) saved.', $count));
519 $self->action_list();
522 sub action_save_proposals {
524 if ( $::form->{proposal_ids} ) {
525 my $propcount = scalar(@{ $::form->{proposal_ids} });
526 if ( $propcount > 0 ) {
527 my $count = $self->save_invoices();
529 flash('ok', t8('#1 proposal(s) with #2 invoice(s) saved.', $propcount, $count));
532 $self->action_list();
536 sub is_collective_transaction {
537 my ($self, $bt) = @_;
538 return $bt->transactioncode eq "191";
541 sub save_single_bank_transaction {
542 my ($self, %params) = @_;
546 bank_transaction => SL::DB::Manager::BankTransaction->find_by(id => $params{bank_transaction_id}),
550 if (!$data{bank_transaction}) {
554 message => $::locale->text('The ID #1 is not a valid database ID.', $data{bank_transaction_id}),
561 my $bt_id = $data{bank_transaction_id};
562 my $bank_transaction = $data{bank_transaction};
563 my $sign = $bank_transaction->amount < 0 ? -1 : 1;
564 my $amount_of_transaction = $sign * $bank_transaction->amount;
565 my $payment_received = $bank_transaction->amount > 0;
566 my $payment_sent = $bank_transaction->amount < 0;
568 foreach my $invoice_id (@{ $params{invoice_ids} }) {
569 my $invoice = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
574 message => $::locale->text("The ID #1 is not a valid database ID.", $invoice_id),
578 push @{ $data{invoices} }, $invoice;
581 if ( $payment_received
582 && any { ( $_->is_sales && ($_->amount < 0))
583 || (!$_->is_sales && ($_->amount > 0))
584 } @{ $data{invoices} }) {
588 message => $::locale->text("Received payments can only be posted for sales invoices and purchase credit notes."),
593 && any { ( $_->is_sales && ($_->amount > 0))
594 || (!$_->is_sales && ($_->amount < 0))
595 } @{ $data{invoices} }) {
599 message => $::locale->text("Sent payments can only be posted for purchase invoices and sales credit notes."),
603 my $max_invoices = scalar(@{ $data{invoices} });
606 foreach my $invoice (@{ $data{invoices} }) {
610 # Check if bank_transaction already has a link to the invoice, may only be linked once per invoice
611 # This might be caused by the user reloading a page and resending the form
612 if (_existing_record_link($bank_transaction, $invoice)) {
616 message => $::locale->text("Bank transaction with id #1 has already been linked to #2.", $bank_transaction->id, $invoice->displayable_name),
620 if (!$amount_of_transaction && $invoice->open_amount) {
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."),
629 if ( defined $::form->{invoice_skontos}->{"$bt_id"} ) {
630 $payment_type = shift(@{ $::form->{invoice_skontos}->{"$bt_id"} });
632 $payment_type = 'without_skonto';
635 # pay invoice or go to the next bank transaction if the amount is not sufficiently high
636 if ($invoice->open_amount <= $amount_of_transaction && $n_invoices < $max_invoices) {
637 my $open_amount = ($payment_type eq 'with_skonto_pt'?$invoice->amount_less_skonto:$invoice->open_amount);
638 # first calculate new bank transaction amount ...
639 if ($invoice->is_sales) {
640 $amount_of_transaction -= $sign * $open_amount;
641 $bank_transaction->invoice_amount($bank_transaction->invoice_amount + $open_amount);
643 $amount_of_transaction += $sign * $open_amount;
644 $bank_transaction->invoice_amount($bank_transaction->invoice_amount - $open_amount);
646 # ... and then pay the invoice
647 $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
648 trans_id => $invoice->id,
649 amount => $open_amount,
650 payment_type => $payment_type,
651 transdate => $bank_transaction->transdate->to_kivitendo);
652 } else { # use the whole amount of the bank transaction for the invoice, overpay the invoice if necessary
653 my $overpaid_amount = $amount_of_transaction - $invoice->open_amount;
654 $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
655 trans_id => $invoice->id,
656 amount => $amount_of_transaction,
657 payment_type => $payment_type,
658 transdate => $bank_transaction->transdate->to_kivitendo);
659 $bank_transaction->invoice_amount($bank_transaction->amount);
660 $amount_of_transaction = 0;
662 if ($overpaid_amount >= 0.01) {
666 message => $::locale->text('Invoice #1 was overpaid by #2.', $invoice->invnumber, $::form->format_amount(\%::myconfig, $overpaid_amount, 2)),
671 # Record a record link from the bank transaction to the invoice
673 from_table => 'bank_transactions',
675 to_table => $invoice->is_sales ? 'ar' : 'ap',
676 to_id => $invoice->id,
679 SL::DB::RecordLink->new(@props)->save;
681 # "close" a sepa_export_item if it exists
682 # code duplicated in action_save_proposals!
683 # currently only works, if there is only exactly one open sepa_export_item
684 if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
685 if ( scalar @$seis == 1 ) {
686 # moved the execution and the check for sepa_export into a method,
687 # this isn't part of a transaction, though
688 $seis->[0]->set_executed if $invoice->id == $seis->[0]->arap_id;
693 $bank_transaction->save;
695 # 'undef' means 'no error' here.
700 my $rez = $data{bank_transaction}->db->with_transaction(sub {
702 $error = $worker->();
716 return grep { $_ } ($error, @warnings);
724 $::auth->assert('bank_transaction');
731 sub make_filter_summary {
734 my $filter = $::form->{filter} || {};
738 [ $filter->{"transdate:date::ge"}, $::locale->text('Transdate') . " " . $::locale->text('From Date') ],
739 [ $filter->{"transdate:date::le"}, $::locale->text('Transdate') . " " . $::locale->text('To Date') ],
740 [ $filter->{"valutadate:date::ge"}, $::locale->text('Valutadate') . " " . $::locale->text('From Date') ],
741 [ $filter->{"valutadate:date::le"}, $::locale->text('Valutadate') . " " . $::locale->text('To Date') ],
742 [ $filter->{"amount:number"}, $::locale->text('Amount') ],
743 [ $filter->{"bank_account_id:integer"}, $::locale->text('Local bank account') ],
747 push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
750 $self->{filter_summary} = join ', ', @filter_strings;
756 my $callback = $self->models->get_callback;
758 my $report = SL::ReportGenerator->new(\%::myconfig, $::form);
759 $self->{report} = $report;
761 my @columns = qw(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);
762 my @sortable = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount purpose local_account_number local_bank_code);
765 transdate => { sub => sub { $_[0]->transdate_as_date } },
766 valutadate => { sub => sub { $_[0]->valutadate_as_date } },
768 remote_account_number => { },
769 remote_bank_code => { },
770 amount => { sub => sub { $_[0]->amount_as_number },
772 invoice_amount => { sub => sub { $_[0]->invoice_amount_as_number },
774 invoices => { sub => sub { $_[0]->linked_invoices } },
775 currency => { sub => sub { $_[0]->currency->name } },
777 local_account_number => { sub => sub { $_[0]->local_bank_account->account_number } },
778 local_bank_code => { sub => sub { $_[0]->local_bank_account->bank_code } },
779 local_bank_name => { sub => sub { $_[0]->local_bank_account->name } },
783 map { $column_defs{$_}->{text} ||= $::locale->text( $self->models->get_sort_spec->{$_}->{title} ) } keys %column_defs;
785 $report->set_options(
786 std_column_visibility => 1,
787 controller_class => 'BankTransaction',
788 output_format => 'HTML',
789 top_info_text => $::locale->text('Bank transactions'),
790 title => $::locale->text('Bank transactions'),
791 allow_pdf_export => 1,
792 allow_csv_export => 1,
794 $report->set_columns(%column_defs);
795 $report->set_column_order(@columns);
796 $report->set_export_options(qw(list_all filter));
797 $report->set_options_from_form;
798 $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
799 $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
801 my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted();
803 $report->set_options(
804 raw_top_info_text => $self->render('bank_transactions/report_top', { output => 0 }, BANK_ACCOUNTS => $bank_accounts),
805 raw_bottom_info_text => $self->render('bank_transactions/report_bottom', { output => 0 }),
809 sub _existing_record_link {
810 my ($bt, $invoice) = @_;
812 # check whether a record link from banktransaction $bt already exists to
813 # invoice $invoice, returns 1 if that is the case
815 die unless $bt->isa("SL::DB::BankTransaction") && ( $invoice->isa("SL::DB::Invoice") || $invoice->isa("SL::DB::PurchaseInvoice") );
817 my $linked_record_to_table = $invoice->is_sales ? 'Invoice' : 'PurchaseInvoice';
818 my $linked_records = $bt->linked_records( direction => 'to', to => $linked_record_to_table, query => [ id => $invoice->id ] );
820 return @$linked_records ? 1 : 0;
823 sub init_problems { [] }
828 SL::Controller::Helper::GetModels->new(
833 dir => 0, # 1 = ASC, 0 = DESC : default sort is newest at top
835 transdate => t8('Transdate'),
836 remote_name => t8('Remote name'),
837 amount => t8('Amount'),
838 invoice_amount => t8('Assigned'),
839 invoices => t8('Linked invoices'),
840 valutadate => t8('Valutadate'),
841 remote_account_number => t8('Remote account number'),
842 remote_bank_code => t8('Remote bank code'),
843 currency => t8('Currency'),
844 purpose => t8('Purpose'),
845 local_account_number => t8('Local account number'),
846 local_bank_code => t8('Local bank code'),
847 local_bank_name => t8('Bank account'),
849 with_objects => [ 'local_bank_account', 'currency' ],
862 SL::Controller::BankTransaction - Posting payments to invoices from
863 bank transactions imported earlier
869 =item C<save_single_bank_transaction %params>
871 Takes a bank transaction ID (as parameter C<bank_transaction_id> and
872 tries to post its amount to a certain number of invoices (parameter
873 C<invoice_ids>, an array ref of database IDs to purchase or sales
876 The whole function is wrapped in a database transaction. If an
877 exception occurs the bank transaction is not posted at all. The same
878 is true if the code detects an error during the execution, e.g. a bank
879 transaction that's already been posted earlier. In both cases the
880 database transaction will be rolled back.
882 If warnings but not errors occur the database transaction is still
885 The return value is an error object or C<undef> if the function
886 succeeded. The calling function will collect all warnings and errors
887 and display them in a nicely formatted table if any occurred.
889 An error object is a hash reference containing the following members:
893 =item * C<result> — can be either C<warning> or C<error>. Warnings are
894 displayed slightly different than errors.
896 =item * C<message> — a human-readable message included in the list of
897 errors meant as the description of why the problem happened
899 =item * C<bank_transaction_id>, C<invoice_ids> — the same parameters
900 that the function was called with
902 =item * C<bank_transaction> — the database object
903 (C<SL::DB::BankTransaction>) corresponding to C<bank_transaction_id>
905 =item * C<invoices> — an array ref of the database objects (either
906 C<SL::DB::Invoice> or C<SL::DB::PurchaseInvoice>) corresponding to
915 Niclas Zimmermann E<lt>niclas@kivitendo-premium.deE<gt>,
916 Geoffrey Richardson E<lt>information@richardson-bueren.deE<gt>