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 # credit notes have a negative amount, treat differently
104 my $all_open_ar_invoices = SL::DB::Manager::Invoice ->get_all(where => [ or => [ amount => { gt => \'paid' },
105 and => [ type => 'credit_note',
106 amount => { lt => \'paid' }
110 with_objects => ['customer','payment_terms']);
112 my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => [amount => { ne => \'paid' }], with_objects => ['vendor' ,'payment_terms']);
113 my $all_open_sepa_export_items = SL::DB::Manager::SepaExportItem->get_all(where => [chart_id => $bank_account->chart_id ,
114 'sepa_export.executed' => 0, 'sepa_export.closed' => 0 ], with_objects => ['sepa_export']);
115 $main::lxdebug->message(LXDebug->DEBUG2(),"count sepaexport=".scalar(@{$all_open_sepa_export_items}));
117 my @all_open_invoices;
118 # filter out invoices with less than 1 cent outstanding
119 push @all_open_invoices, map { $_->{is_ar}=1 ; $_ } grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ar_invoices };
120 push @all_open_invoices, map { $_->{is_ar}=0 ; $_ } grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
121 $main::lxdebug->message(LXDebug->DEBUG2(),"bank_account=".$::form->{filter}{bank_account}." invoices: ".scalar(@{ $all_open_ar_invoices }).
122 " + ".scalar(@{ $all_open_ap_invoices })." non fully paid=".scalar(@all_open_invoices)." transactions=".scalar(@{ $bank_transactions }));
124 my @all_sepa_invoices;
125 my @all_non_sepa_invoices;
127 # first collect sepa export items to open invoices
128 foreach my $open_invoice (@all_open_invoices){
129 # my @items = grep { $_->ap_id == $open_invoice->id || $_->ar_id == $open_invoice->id } @{$all_open_sepa_export_items};
130 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount,2);
131 $open_invoice->{skonto_type} = 'without_skonto';
132 foreach ( @{$all_open_sepa_export_items}) {
133 if ( $_->ap_id == $open_invoice->id || $_->ar_id == $open_invoice->id ) {
134 my $factor = ($_->ar_id == $open_invoice->id?1:-1);
135 $main::lxdebug->message(LXDebug->DEBUG2(),"exitem=".$_->id." for invoice ".$open_invoice->id." factor=".$factor);
136 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount*$factor,2);
137 $open_invoice->{sepa_export_item} = $_ ;
138 $open_invoice->{skonto_type} = $_->payment_type;
139 $sepa_exports{$_->sepa_export_id} ||= { count => 0, is_ar => 0, amount => 0, proposed => 0, invoices => [], item => $_ };
140 $sepa_exports{$_->sepa_export_id}->{count}++ ;
141 $sepa_exports{$_->sepa_export_id}->{is_ar}++ if $_->ar_id == $open_invoice->id;
142 $sepa_exports{$_->sepa_export_id}->{amount} += $_->amount * $factor;
143 push @{ $sepa_exports{$_->sepa_export_id}->{invoices} }, $open_invoice;
144 #$main::lxdebug->message(LXDebug->DEBUG2(),"amount for export id ".$_->sepa_export_id." = ".
145 # $sepa_exports{$_->sepa_export_id}->{amount}." count = ".
146 # $sepa_exports{$_->sepa_export_id}->{count}." is_ar = ".
147 # $sepa_exports{$_->sepa_export_id}->{is_ar} );
148 push @all_sepa_invoices , $open_invoice;
151 push @all_non_sepa_invoices , $open_invoice if ! $open_invoice->{sepa_export_item};
154 # try to match each bank_transaction with each of the possible open invoices
158 foreach my $bt (@{ $bank_transactions }) {
159 ## 5 Stellen hinter dem Komma auf 2 Stellen reduzieren
160 $bt->amount($bt->amount*1);
161 $bt->invoice_amount($bt->invoice_amount*1);
162 $main::lxdebug->message(LXDebug->DEBUG2(),"BT ".$bt->id." amount=".$bt->amount." invoice_amount=".$bt->invoice_amount." remote=". $bt->{remote_name});
164 $bt->{proposals} = [];
165 $bt->{rule_matches} = [];
167 $bt->{remote_name} .= $bt->{remote_name_1} if $bt->{remote_name_1};
169 if ( $self->is_collective_transaction($bt) ) {
170 foreach ( keys %sepa_exports) {
171 #$main::lxdebug->message(LXDebug->DEBUG2(),"Exp ID=".$_." compare sum amount ".($sepa_exports{$_}->{amount} *1) ." == ".($bt->amount * 1));
172 if ( $bt->transaction_code eq '191' && abs(($sepa_exports{$_}->{amount} * 1) - ($bt->amount * 1)) < 0.01 ) {
174 @{$bt->{proposals}} = @{$sepa_exports{$_}->{invoices}};
175 $bt->{agreement} = 20;
176 push(@{$bt->{rule_matches}},'sepa_export_item(20)');
177 $sepa_exports{$_}->{proposed}=1;
178 #$main::lxdebug->message(LXDebug->DEBUG2(),"has ".scalar($bt->{proposals})." invoices");
179 push(@proposals, $bt);
184 next unless $bt->{remote_name}; # bank has no name, usually fees, use create invoice to assign
186 foreach ( @{$all_open_sepa_export_items}) {
187 last if scalar (@all_sepa_invoices) == 0;
188 foreach my $open_invoice (@all_sepa_invoices){
189 if ( $_->ap_id == $open_invoice->id || $_->ar_id == $open_invoice->id ) {
190 #$main::lxdebug->message(LXDebug->DEBUG2(),"exitem2=".$_->id." for invoice ".$open_invoice->id);
191 my $factor = ( $_->ar_id == $open_invoice->id?1:-1);
192 $_->amount($_->amount*1);
193 #$main::lxdebug->message(LXDebug->DEBUG2(),"remote account '".$bt->{remote_account_number}."' bt_amount=".$bt->amount." factor=".$factor);
194 #$main::lxdebug->message(LXDebug->DEBUG2(),"compare with '".$_->vc_iban."' amount=".$_->amount);
195 if ( $bt->{remote_account_number} eq $_->vc_iban && abs(abs($_->amount) - abs($bt->amount)) < 0.01 ) {
197 $iban = $open_invoice->customer->iban if $open_invoice->is_sales;
198 $iban = $open_invoice->vendor->iban if ! $open_invoice->is_sales;
199 if($bt->{remote_account_number} eq $iban && abs(abs($open_invoice->amount) - abs($bt->amount)) < 0.01 ) {
200 ($open_invoice->{agreement}, $open_invoice->{rule_matches}) = $bt->get_agreement_with_invoice($open_invoice);
201 $open_invoice->{agreement} += 5;
202 $open_invoice->{rule_matches} .= 'sepa_export_item(5) ';
203 $main::lxdebug->message(LXDebug->DEBUG2(),"sepa invoice_id=".$open_invoice->id." agreement=".$open_invoice->{agreement}." rules matches=".$open_invoice->{rule_matches});
204 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount*$factor,2);
211 # try to match the current $bt to each of the open_invoices, saving the
212 # results of get_agreement_with_invoice in $open_invoice->{agreement} and
213 # $open_invoice->{rule_matches}.
215 # The values are overwritten each time a new bt is checked, so at the end
216 # of each bt the likely results are filtered and those values are stored in
217 # the arrays $bt->{proposals} and $bt->{rule_matches}, and the agreement
218 # score is stored in $bt->{agreement}
220 foreach my $open_invoice (@all_non_sepa_invoices){
221 ($open_invoice->{agreement}, $open_invoice->{rule_matches}) = $bt->get_agreement_with_invoice($open_invoice);
222 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount*($open_invoice->{is_ar}?1:-1),2);
223 $main::lxdebug->message(LXDebug->DEBUG2(),"nons invoice_id=".$open_invoice->id." amount=".$open_invoice->amount." agreement=".$open_invoice->{agreement}." rules matches=".$open_invoice->{rule_matches}) if $open_invoice->{agreement} > 2;
227 my $min_agreement = 3; # suggestions must have at least this score
229 my $max_agreement = max map { $_->{agreement} } @all_open_invoices;
231 # add open_invoices with highest agreement into array $bt->{proposals}
232 if ( $max_agreement >= $min_agreement ) {
233 $bt->{proposals} = [ grep { $_->{agreement} == $max_agreement } @all_open_invoices ];
234 $bt->{agreement} = $max_agreement; #scalar @{ $bt->{proposals} } ? $agreement + 1 : '';
236 # store the rule_matches in a separate array, so they can be displayed in template
237 foreach ( @{ $bt->{proposals} } ) {
238 push(@{$bt->{rule_matches}}, $_->{rule_matches});
244 # separate filter for proposals (second tab, agreement >= 5 and exactly one match)
245 # to qualify as a proposal there has to be
246 # * agreement >= 5 TODO: make threshold configurable in configuration
247 # * there must be only one exact match
248 # * depending on whether sales or purchase the amount has to have the correct sign (so Gutschriften don't work?)
249 my $proposal_threshold = 5;
250 my @otherproposals = grep {
251 ($_->{agreement} >= $proposal_threshold)
252 && (1 == scalar @{ $_->{proposals} })
253 && (@{ $_->{proposals} }[0]->is_sales ? abs(@{ $_->{proposals} }[0]->amount - $_->amount) < 0.01
254 : abs(@{ $_->{proposals} }[0]->amount + $_->amount) < 0.01)
255 } @{ $bank_transactions };
257 push ( @proposals, @otherproposals);
259 # sort bank transaction proposals by quality (score) of proposal
260 $bank_transactions = [ sort { $a->{agreement} <=> $b->{agreement} } @{ $bank_transactions } ] if $::form->{sort_by} eq 'proposal' and $::form->{sort_dir} == 1;
261 $bank_transactions = [ sort { $b->{agreement} <=> $a->{agreement} } @{ $bank_transactions } ] if $::form->{sort_by} eq 'proposal' and $::form->{sort_dir} == 0;
263 $::request->layout->add_javascripts("kivi.BankTransaction.js");
264 $self->render('bank_transactions/list',
265 title => t8('Bank transactions MT940'),
266 BANK_TRANSACTIONS => $bank_transactions,
267 PROPOSALS => \@proposals,
268 bank_account => $bank_account,
269 ui_tab => scalar(@proposals) > 0?1:0,
273 sub action_assign_invoice {
276 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
278 $self->render('bank_transactions/assign_invoice',
280 title => t8('Assign invoice'),);
283 sub action_create_invoice {
285 my %myconfig = %main::myconfig;
287 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
288 my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(account_number => $self->{transaction}->{remote_account_number});
290 my $use_vendor_filter = $self->{transaction}->{remote_account_number} && $vendor_of_transaction;
292 my $drafts = SL::DB::Manager::Draft->get_all(where => [ module => 'ap'] , with_objects => 'employee');
296 foreach my $draft ( @{ $drafts } ) {
297 my $draft_as_object = YAML::Load($draft->form);
298 my $vendor = SL::DB::Manager::Vendor->find_by(id => $draft_as_object->{vendor_id});
299 $draft->{vendor} = $vendor->name;
300 $draft->{vendor_id} = $vendor->id;
301 push @filtered_drafts, $draft;
305 @filtered_drafts = grep { $_->{vendor_id} == $vendor_of_transaction->id } @filtered_drafts if $use_vendor_filter;
307 my $all_vendors = SL::DB::Manager::Vendor->get_all();
308 my $callback = $self->url_for(action => 'list',
309 'filter.bank_account' => $::form->{filter}->{bank_account},
310 'filter.todate' => $::form->{filter}->{todate},
311 'filter.fromdate' => $::form->{filter}->{fromdate});
314 'bank_transactions/create_invoice',
316 title => t8('Create invoice'),
317 DRAFTS => \@filtered_drafts,
318 vendor_id => $use_vendor_filter ? $vendor_of_transaction->id : undef,
319 vendor_name => $use_vendor_filter ? $vendor_of_transaction->name : undef,
320 ALL_VENDORS => $all_vendors,
321 callback => $callback,
325 sub action_ajax_payment_suggestion {
328 # based on a BankTransaction ID and a Invoice or PurchaseInvoice ID passed via $::form,
329 # create an HTML blob to be used by the js function add_invoices in templates/webpages/bank_transactions/list.html
330 # and return encoded as JSON
332 my $bt = SL::DB::Manager::BankTransaction->find_by( id => $::form->{bt_id} );
333 my $invoice = SL::DB::Manager::Invoice->find_by( id => $::form->{prop_id} ) || SL::DB::Manager::PurchaseInvoice->find_by( id => $::form->{prop_id} );
335 die unless $bt and $invoice;
337 my @select_options = $invoice->get_payment_select_options_for_bank_transaction($::form->{bt_id});
340 $html .= SL::Presenter->input_tag('invoice_ids.' . $::form->{bt_id} . '[]', $::form->{prop_id} , type => 'hidden');
341 $html .= SL::Presenter->escape(t8('Invno.') . ': ' . $invoice->invnumber . ' ');
342 $html .= SL::Presenter->escape(t8('Open amount') . ': ' . $::form->format_amount(\%::myconfig, $invoice->open_amount, 2) . ' ');
343 $html .= SL::Presenter->select_tag('invoice_skontos.' . $::form->{bt_id} . '[]',
345 value_key => 'payment_type',
346 title_key => 'display' )
348 $html .= SL::Presenter->html_tag('a', 'x', href => '#', onclick => "kivi.BankTransaction.delete_invoice(" . $::form->{bt_id} . ',' . $::form->{prop_id} . ")");
349 $html = SL::Presenter->html_tag('div', $html, id => $::form->{bt_id} . '.' . $::form->{prop_id}, 'data-invoice-amount' => $invoice->open_amount * 1);
351 $self->render(\ SL::JSON::to_json( { 'html' => $html } ), { layout => 0, type => 'json', process => 0 });
354 sub action_filter_drafts {
357 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
358 my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(account_number => $self->{transaction}->{remote_account_number});
360 my $drafts = SL::DB::Manager::Draft->get_all(with_objects => 'employee');
364 foreach my $draft ( @{ $drafts } ) {
365 my $draft_as_object = YAML::Load($draft->form);
366 next unless $draft_as_object->{vendor_id}; # we cannot filter for vendor name, if this is a gl draft
368 my $vendor = SL::DB::Manager::Vendor->find_by(id => $draft_as_object->{vendor_id});
369 $draft->{vendor} = $vendor->name;
370 $draft->{vendor_id} = $vendor->id;
372 push @filtered_drafts, $draft;
375 my $vendor_name = $::form->{vendor};
376 my $vendor_id = $::form->{vendor_id};
379 @filtered_drafts = grep { $_->{vendor_id} == $vendor_id } @filtered_drafts if $vendor_id;
380 @filtered_drafts = grep { $_->{vendor} =~ /$vendor_name/i } @filtered_drafts if $vendor_name;
382 my $output = $self->render(
383 'bank_transactions/filter_drafts',
385 DRAFTS => \@filtered_drafts,
388 my %result = ( count => 0, html => $output );
390 $self->render(\to_json(\%result), { type => 'json', process => 0 });
393 sub action_ajax_add_list {
396 my @where_sale = (amount => { ne => \'paid' });
397 my @where_purchase = (amount => { ne => \'paid' });
399 if ($::form->{invnumber}) {
400 push @where_sale, (invnumber => { ilike => like($::form->{invnumber})});
401 push @where_purchase, (invnumber => { ilike => like($::form->{invnumber})});
404 if ($::form->{amount}) {
405 push @where_sale, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
406 push @where_purchase, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
409 if ($::form->{vcnumber}) {
410 push @where_sale, ('customer.customernumber' => { ilike => like($::form->{vcnumber})});
411 push @where_purchase, ('vendor.vendornumber' => { ilike => like($::form->{vcnumber})});
414 if ($::form->{vcname}) {
415 push @where_sale, ('customer.name' => { ilike => like($::form->{vcname})});
416 push @where_purchase, ('vendor.name' => { ilike => like($::form->{vcname})});
419 if ($::form->{transdatefrom}) {
420 my $fromdate = $::locale->parse_date_to_object($::form->{transdatefrom});
421 if ( ref($fromdate) eq 'DateTime' ) {
422 push @where_sale, ('transdate' => { ge => $fromdate});
423 push @where_purchase, ('transdate' => { ge => $fromdate});
427 if ($::form->{transdateto}) {
428 my $todate = $::locale->parse_date_to_object($::form->{transdateto});
429 if ( ref($todate) eq 'DateTime' ) {
430 $todate->add(days => 1);
431 push @where_sale, ('transdate' => { lt => $todate});
432 push @where_purchase, ('transdate' => { lt => $todate});
436 my $all_open_ar_invoices = SL::DB::Manager::Invoice ->get_all(where => \@where_sale, with_objects => 'customer');
437 my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => \@where_purchase, with_objects => 'vendor');
439 my @all_open_invoices = @{ $all_open_ar_invoices };
440 # add ap invoices, filtering out subcent open amounts
441 push @all_open_invoices, grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
443 @all_open_invoices = sort { $a->id <=> $b->id } @all_open_invoices;
445 my $output = $self->render(
446 'bank_transactions/add_list',
448 INVOICES => \@all_open_invoices,
451 my %result = ( count => 0, html => $output );
453 $self->render(\to_json(\%result), { type => 'json', process => 0 });
456 sub action_ajax_accept_invoices {
459 my @selected_invoices;
460 foreach my $invoice_id (@{ $::form->{invoice_id} || [] }) {
461 my $invoice_object = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
462 push @selected_invoices, $invoice_object;
466 'bank_transactions/invoices',
468 INVOICES => \@selected_invoices,
469 bt_id => $::form->{bt_id},
476 return 0 if !$::form->{invoice_ids};
478 my %invoice_hash = %{ delete $::form->{invoice_ids} }; # each key (the bt line with a bt_id) contains an array of invoice_ids
480 # e.g. three partial payments with bt_ids 54, 55 and 56 for invoice with id 74:
493 # or if the payment with bt_id 44 is used to pay invoices with ids 50, 51 and 52
495 # '44' => [ '50', '51', 52' ]
498 $::form->{invoice_skontos} ||= {}; # hash of arrays containing the payment types, could be empty
500 # a bank_transaction may be assigned to several invoices, i.e. a customer
501 # might pay several open invoices with one transaction
507 if ( $::form->{proposal_ids} ) {
508 foreach (@{ $::form->{proposal_ids} }) {
509 my $bank_transaction_id = $_;
510 my $invoice_ids = $invoice_hash{$_};
511 push @{ $self->problems }, $self->save_single_bank_transaction(
512 bank_transaction_id => $bank_transaction_id,
513 invoice_ids => $invoice_ids,
514 sources => ($::form->{sources} // {})->{$_},
515 memos => ($::form->{memos} // {})->{$_},
517 $count += scalar( @{$invoice_ids} );
520 while ( my ($bank_transaction_id, $invoice_ids) = each(%invoice_hash) ) {
521 push @{ $self->problems }, $self->save_single_bank_transaction(
522 bank_transaction_id => $bank_transaction_id,
523 invoice_ids => $invoice_ids,
525 $count += scalar( @{$invoice_ids} );
531 sub action_save_invoices {
533 my $count = $self->save_invoices();
535 flash('ok', t8('#1 invoice(s) saved.', $count));
537 $self->action_list();
540 sub action_save_proposals {
543 if ( $::form->{proposal_ids} ) {
544 my $propcount = scalar(@{ $::form->{proposal_ids} });
545 if ( $propcount > 0 ) {
546 my $count = $self->save_invoices();
548 flash('ok', t8('#1 proposal(s) with #2 invoice(s) saved.', $propcount, $count));
551 $self->action_list();
555 sub is_collective_transaction {
556 my ($self, $bt) = @_;
557 return $bt->transaction_code eq "191";
560 sub save_single_bank_transaction {
561 my ($self, %params) = @_;
565 bank_transaction => SL::DB::Manager::BankTransaction->find_by(id => $params{bank_transaction_id}),
569 if (!$data{bank_transaction}) {
573 message => $::locale->text('The ID #1 is not a valid database ID.', $data{bank_transaction_id}),
580 my $bt_id = $data{bank_transaction_id};
581 my $bank_transaction = $data{bank_transaction};
582 my $sign = $bank_transaction->amount < 0 ? -1 : 1;
583 my $amount_of_transaction = $sign * $bank_transaction->amount;
584 my $payment_received = $bank_transaction->amount > 0;
585 my $payment_sent = $bank_transaction->amount < 0;
588 foreach my $invoice_id (@{ $params{invoice_ids} }) {
589 my $invoice = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
594 message => $::locale->text("The ID #1 is not a valid database ID.", $invoice_id),
597 push @{ $data{invoices} }, $invoice;
600 if ( $payment_received
601 && any { ( $_->is_sales && ($_->amount < 0))
602 || (!$_->is_sales && ($_->amount > 0))
603 } @{ $data{invoices} }) {
607 message => $::locale->text("Received payments can only be posted for sales invoices and purchase credit notes."),
612 && any { ( $_->is_sales && ($_->amount > 0))
613 || (!$_->is_sales && ($_->amount < 0))
614 } @{ $data{invoices} }) {
618 message => $::locale->text("Sent payments can only be posted for purchase invoices and sales credit notes."),
622 my $max_invoices = scalar(@{ $data{invoices} });
625 foreach my $invoice (@{ $data{invoices} }) {
626 my $source = ($data{sources} // [])->[$n_invoices];
627 my $memo = ($data{memos} // [])->[$n_invoices];
631 # Check if bank_transaction already has a link to the invoice, may only be linked once per invoice
632 # This might be caused by the user reloading a page and resending the form
633 if (_existing_record_link($bank_transaction, $invoice)) {
637 message => $::locale->text("Bank transaction with id #1 has already been linked to #2.", $bank_transaction->id, $invoice->displayable_name),
641 if (!$amount_of_transaction && $invoice->open_amount) {
645 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."),
650 if ( defined $::form->{invoice_skontos}->{"$bt_id"} ) {
651 $payment_type = shift(@{ $::form->{invoice_skontos}->{"$bt_id"} });
653 $payment_type = 'without_skonto';
657 # pay invoice or go to the next bank transaction if the amount is not sufficiently high
658 if ($invoice->open_amount <= $amount_of_transaction && $n_invoices < $max_invoices) {
659 my $open_amount = ($payment_type eq 'with_skonto_pt'?$invoice->amount_less_skonto:$invoice->open_amount);
660 # first calculate new bank transaction amount ...
661 if ($invoice->is_sales) {
662 $amount_of_transaction -= $sign * $open_amount;
663 $bank_transaction->invoice_amount($bank_transaction->invoice_amount + $open_amount);
665 $amount_of_transaction += $sign * $open_amount;
666 $bank_transaction->invoice_amount($bank_transaction->invoice_amount - $open_amount);
668 # ... and then pay the invoice
669 $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
670 trans_id => $invoice->id,
671 amount => $open_amount,
672 payment_type => $payment_type,
675 transdate => $bank_transaction->transdate->to_kivitendo);
676 } elsif ( $invoice->is_sales && $invoice->type eq 'credit_note' ) {
677 # no check for overpayment/multiple payments
678 $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
679 trans_id => $invoice->id,
680 amount => $invoice->open_amount,
681 payment_type => $payment_type,
684 transdate => $bank_transaction->transdate->to_kivitendo);
685 } else { # use the whole amount of the bank transaction for the invoice, overpay the invoice if necessary
686 my $overpaid_amount = $amount_of_transaction - $invoice->open_amount;
687 $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
688 trans_id => $invoice->id,
689 amount => $amount_of_transaction,
690 payment_type => $payment_type,
693 transdate => $bank_transaction->transdate->to_kivitendo);
694 $bank_transaction->invoice_amount($bank_transaction->amount);
695 $amount_of_transaction = 0;
697 if ($overpaid_amount >= 0.01) {
701 message => $::locale->text('Invoice #1 was overpaid by #2.', $invoice->invnumber, $::form->format_amount(\%::myconfig, $overpaid_amount, 2)),
705 # Record a record link from the bank transaction to the invoice
707 from_table => 'bank_transactions',
709 to_table => $invoice->is_sales ? 'ar' : 'ap',
710 to_id => $invoice->id,
713 SL::DB::RecordLink->new(@props)->save;
715 # "close" a sepa_export_item if it exists
716 # code duplicated in action_save_proposals!
717 # currently only works, if there is only exactly one open sepa_export_item
718 if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
719 if ( scalar @$seis == 1 ) {
720 # moved the execution and the check for sepa_export into a method,
721 # this isn't part of a transaction, though
722 $seis->[0]->set_executed if $invoice->id == $seis->[0]->arap_id;
727 $bank_transaction->save;
729 # 'undef' means 'no error' here.
734 my $rez = $data{bank_transaction}->db->with_transaction(sub {
736 $error = $worker->();
750 return grep { $_ } ($error, @warnings);
758 $::auth->assert('bank_transaction');
765 sub make_filter_summary {
768 my $filter = $::form->{filter} || {};
772 [ $filter->{"transdate:date::ge"}, $::locale->text('Transdate') . " " . $::locale->text('From Date') ],
773 [ $filter->{"transdate:date::le"}, $::locale->text('Transdate') . " " . $::locale->text('To Date') ],
774 [ $filter->{"valutadate:date::ge"}, $::locale->text('Valutadate') . " " . $::locale->text('From Date') ],
775 [ $filter->{"valutadate:date::le"}, $::locale->text('Valutadate') . " " . $::locale->text('To Date') ],
776 [ $filter->{"amount:number"}, $::locale->text('Amount') ],
777 [ $filter->{"bank_account_id:integer"}, $::locale->text('Local bank account') ],
781 push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
784 $self->{filter_summary} = join ', ', @filter_strings;
790 my $callback = $self->models->get_callback;
792 my $report = SL::ReportGenerator->new(\%::myconfig, $::form);
793 $self->{report} = $report;
795 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);
796 my @sortable = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount purpose local_account_number local_bank_code);
799 transdate => { sub => sub { $_[0]->transdate_as_date } },
800 valutadate => { sub => sub { $_[0]->valutadate_as_date } },
802 remote_account_number => { },
803 remote_bank_code => { },
804 amount => { sub => sub { $_[0]->amount_as_number },
806 invoice_amount => { sub => sub { $_[0]->invoice_amount_as_number },
808 invoices => { sub => sub { $_[0]->linked_invoices } },
809 currency => { sub => sub { $_[0]->currency->name } },
811 local_account_number => { sub => sub { $_[0]->local_bank_account->account_number } },
812 local_bank_code => { sub => sub { $_[0]->local_bank_account->bank_code } },
813 local_bank_name => { sub => sub { $_[0]->local_bank_account->name } },
817 map { $column_defs{$_}->{text} ||= $::locale->text( $self->models->get_sort_spec->{$_}->{title} ) } keys %column_defs;
819 $report->set_options(
820 std_column_visibility => 1,
821 controller_class => 'BankTransaction',
822 output_format => 'HTML',
823 top_info_text => $::locale->text('Bank transactions'),
824 title => $::locale->text('Bank transactions'),
825 allow_pdf_export => 1,
826 allow_csv_export => 1,
828 $report->set_columns(%column_defs);
829 $report->set_column_order(@columns);
830 $report->set_export_options(qw(list_all filter));
831 $report->set_options_from_form;
832 $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
833 $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
835 my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted();
837 $report->set_options(
838 raw_top_info_text => $self->render('bank_transactions/report_top', { output => 0 }, BANK_ACCOUNTS => $bank_accounts),
839 raw_bottom_info_text => $self->render('bank_transactions/report_bottom', { output => 0 }),
843 sub _existing_record_link {
844 my ($bt, $invoice) = @_;
846 # check whether a record link from banktransaction $bt already exists to
847 # invoice $invoice, returns 1 if that is the case
849 die unless $bt->isa("SL::DB::BankTransaction") && ( $invoice->isa("SL::DB::Invoice") || $invoice->isa("SL::DB::PurchaseInvoice") );
851 my $linked_record_to_table = $invoice->is_sales ? 'Invoice' : 'PurchaseInvoice';
852 my $linked_records = $bt->linked_records( direction => 'to', to => $linked_record_to_table, query => [ id => $invoice->id ] );
854 return @$linked_records ? 1 : 0;
857 sub init_problems { [] }
862 SL::Controller::Helper::GetModels->new(
867 dir => 0, # 1 = ASC, 0 = DESC : default sort is newest at top
869 transdate => t8('Transdate'),
870 remote_name => t8('Remote name'),
871 amount => t8('Amount'),
872 invoice_amount => t8('Assigned'),
873 invoices => t8('Linked invoices'),
874 valutadate => t8('Valutadate'),
875 remote_account_number => t8('Remote account number'),
876 remote_bank_code => t8('Remote bank code'),
877 currency => t8('Currency'),
878 purpose => t8('Purpose'),
879 local_account_number => t8('Local account number'),
880 local_bank_code => t8('Local bank code'),
881 local_bank_name => t8('Bank account'),
883 with_objects => [ 'local_bank_account', 'currency' ],
896 SL::Controller::BankTransaction - Posting payments to invoices from
897 bank transactions imported earlier
903 =item C<save_single_bank_transaction %params>
905 Takes a bank transaction ID (as parameter C<bank_transaction_id> and
906 tries to post its amount to a certain number of invoices (parameter
907 C<invoice_ids>, an array ref of database IDs to purchase or sales
910 The whole function is wrapped in a database transaction. If an
911 exception occurs the bank transaction is not posted at all. The same
912 is true if the code detects an error during the execution, e.g. a bank
913 transaction that's already been posted earlier. In both cases the
914 database transaction will be rolled back.
916 If warnings but not errors occur the database transaction is still
919 The return value is an error object or C<undef> if the function
920 succeeded. The calling function will collect all warnings and errors
921 and display them in a nicely formatted table if any occurred.
923 An error object is a hash reference containing the following members:
927 =item * C<result> — can be either C<warning> or C<error>. Warnings are
928 displayed slightly different than errors.
930 =item * C<message> — a human-readable message included in the list of
931 errors meant as the description of why the problem happened
933 =item * C<bank_transaction_id>, C<invoice_ids> — the same parameters
934 that the function was called with
936 =item * C<bank_transaction> — the database object
937 (C<SL::DB::BankTransaction>) corresponding to C<bank_transaction_id>
939 =item * C<invoices> — an array ref of the database objects (either
940 C<SL::DB::Invoice> or C<SL::DB::PurchaseInvoice>) corresponding to
949 Niclas Zimmermann E<lt>niclas@kivitendo-premium.deE<gt>,
950 Geoffrey Richardson E<lt>information@richardson-bueren.deE<gt>