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;
24 use SL::DB::BankAccount;
25 use SL::DB::RecordTemplate;
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 => [ qw(callback transaction) ],
36 'scalar --get_set_init' => [ qw(models problems) ],
39 __PACKAGE__->run_before('check_auth');
49 my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted( query => [ obsolete => 0 ] );
51 $self->setup_search_action_bar;
52 $self->render('bank_transactions/search',
53 BANK_ACCOUNTS => $bank_accounts);
59 $self->make_filter_summary;
60 $self->prepare_report;
62 $self->setup_list_all_action_bar;
63 $self->report_generator_list_objects(report => $self->{report}, objects => $self->models->get);
69 if (!$::form->{filter}{bank_account}) {
70 flash('error', t8('No bank account chosen!'));
75 my $sort_by = $::form->{sort_by} || 'transdate';
76 $sort_by = 'transdate' if $sort_by eq 'proposal';
77 $sort_by .= $::form->{sort_dir} ? ' DESC' : ' ASC';
79 my $fromdate = $::locale->parse_date_to_object($::form->{filter}->{fromdate});
80 my $todate = $::locale->parse_date_to_object($::form->{filter}->{todate});
81 $todate->add( days => 1 ) if $todate;
84 push @where, (transdate => { ge => $fromdate }) if ($fromdate);
85 push @where, (transdate => { lt => $todate }) if ($todate);
86 my $bank_account = SL::DB::Manager::BankAccount->find_by( id => $::form->{filter}{bank_account} );
87 # bank_transactions no younger than starting date,
88 # including starting date (same search behaviour as fromdate)
89 # but OPEN invoices to be matched may be from before
90 if ( $bank_account->reconciliation_starting_date ) {
91 push @where, (transdate => { ge => $bank_account->reconciliation_starting_date });
94 my $bank_transactions = SL::DB::Manager::BankTransaction->get_all(
95 with_objects => [ 'local_bank_account', 'currency' ],
99 amount => {ne => \'invoice_amount'},
100 local_bank_account_id => $::form->{filter}{bank_account},
104 $main::lxdebug->message(LXDebug->DEBUG2(),"count bt=".scalar(@{$bank_transactions}." bank_account=".$bank_account->id." chart=".$bank_account->chart_id));
106 # credit notes have a negative amount, treat differently
107 my $all_open_ar_invoices = SL::DB::Manager::Invoice ->get_all(where => [ or => [ amount => { gt => \'paid' },
108 and => [ type => 'credit_note',
109 amount => { lt => \'paid' }
113 with_objects => ['customer','payment_terms']);
115 my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => [amount => { ne => \'paid' }], with_objects => ['vendor' ,'payment_terms']);
116 my $all_open_sepa_export_items = SL::DB::Manager::SepaExportItem->get_all(where => [chart_id => $bank_account->chart_id ,
117 'sepa_export.executed' => 0, 'sepa_export.closed' => 0 ], with_objects => ['sepa_export']);
118 $main::lxdebug->message(LXDebug->DEBUG2(),"count sepaexport=".scalar(@{$all_open_sepa_export_items}));
120 my @all_open_invoices;
121 # filter out invoices with less than 1 cent outstanding
122 push @all_open_invoices, map { $_->{is_ar}=1 ; $_ } grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ar_invoices };
123 push @all_open_invoices, map { $_->{is_ar}=0 ; $_ } grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
124 $main::lxdebug->message(LXDebug->DEBUG2(),"bank_account=".$::form->{filter}{bank_account}." invoices: ".scalar(@{ $all_open_ar_invoices }).
125 " + ".scalar(@{ $all_open_ap_invoices })." non fully paid=".scalar(@all_open_invoices)." transactions=".scalar(@{ $bank_transactions }));
127 my @all_sepa_invoices;
128 my @all_non_sepa_invoices;
130 # first collect sepa export items to open invoices
131 foreach my $open_invoice (@all_open_invoices){
132 # my @items = grep { $_->ap_id == $open_invoice->id || $_->ar_id == $open_invoice->id } @{$all_open_sepa_export_items};
133 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount,2);
134 $open_invoice->{skonto_type} = 'without_skonto';
135 foreach ( @{$all_open_sepa_export_items}) {
136 if ( $_->ap_id == $open_invoice->id || $_->ar_id == $open_invoice->id ) {
137 my $factor = ($_->ar_id == $open_invoice->id?1:-1);
138 $main::lxdebug->message(LXDebug->DEBUG2(),"exitem=".$_->id." for invoice ".$open_invoice->id." factor=".$factor);
139 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount*$factor,2);
140 $open_invoice->{sepa_export_item} = $_ ;
141 $open_invoice->{skonto_type} = $_->payment_type;
142 $sepa_exports{$_->sepa_export_id} ||= { count => 0, is_ar => 0, amount => 0, proposed => 0, invoices => [], item => $_ };
143 $sepa_exports{$_->sepa_export_id}->{count}++ ;
144 $sepa_exports{$_->sepa_export_id}->{is_ar}++ if $_->ar_id == $open_invoice->id;
145 $sepa_exports{$_->sepa_export_id}->{amount} += $_->amount * $factor;
146 push @{ $sepa_exports{$_->sepa_export_id}->{invoices} }, $open_invoice;
147 #$main::lxdebug->message(LXDebug->DEBUG2(),"amount for export id ".$_->sepa_export_id." = ".
148 # $sepa_exports{$_->sepa_export_id}->{amount}." count = ".
149 # $sepa_exports{$_->sepa_export_id}->{count}." is_ar = ".
150 # $sepa_exports{$_->sepa_export_id}->{is_ar} );
151 push @all_sepa_invoices , $open_invoice;
154 push @all_non_sepa_invoices , $open_invoice if ! $open_invoice->{sepa_export_item};
157 # try to match each bank_transaction with each of the possible open invoices
161 foreach my $bt (@{ $bank_transactions }) {
162 ## 5 Stellen hinter dem Komma auf 2 Stellen reduzieren
163 $bt->amount($bt->amount*1);
164 $bt->invoice_amount($bt->invoice_amount*1);
165 $main::lxdebug->message(LXDebug->DEBUG2(),"BT ".$bt->id." amount=".$bt->amount." invoice_amount=".$bt->invoice_amount." remote=". $bt->{remote_name});
167 $bt->{proposals} = [];
168 $bt->{rule_matches} = [];
170 $bt->{remote_name} .= $bt->{remote_name_1} if $bt->{remote_name_1};
172 if ( $self->is_collective_transaction($bt) ) {
173 foreach ( keys %sepa_exports) {
174 #$main::lxdebug->message(LXDebug->DEBUG2(),"Exp ID=".$_." compare sum amount ".($sepa_exports{$_}->{amount} *1) ." == ".($bt->amount * 1));
175 if ( $bt->transaction_code eq '191' && abs(($sepa_exports{$_}->{amount} * 1) - ($bt->amount * 1)) < 0.01 ) {
177 @{$bt->{proposals}} = @{$sepa_exports{$_}->{invoices}};
178 $bt->{agreement} = 20;
179 push(@{$bt->{rule_matches}},'sepa_export_item(20)');
180 $sepa_exports{$_}->{proposed}=1;
181 #$main::lxdebug->message(LXDebug->DEBUG2(),"has ".scalar($bt->{proposals})." invoices");
182 push(@proposals, $bt);
187 next unless $bt->{remote_name}; # bank has no name, usually fees, use create invoice to assign
189 foreach ( @{$all_open_sepa_export_items}) {
190 last if scalar (@all_sepa_invoices) == 0;
191 foreach my $open_invoice (@all_sepa_invoices){
192 if ( $_->ap_id == $open_invoice->id || $_->ar_id == $open_invoice->id ) {
193 #$main::lxdebug->message(LXDebug->DEBUG2(),"exitem2=".$_->id." for invoice ".$open_invoice->id);
194 my $factor = ( $_->ar_id == $open_invoice->id?1:-1);
195 $_->amount($_->amount*1);
196 #$main::lxdebug->message(LXDebug->DEBUG2(),"remote account '".$bt->{remote_account_number}."' bt_amount=".$bt->amount." factor=".$factor);
197 #$main::lxdebug->message(LXDebug->DEBUG2(),"compare with '".$_->vc_iban."' amount=".$_->amount);
198 if ( $bt->{remote_account_number} eq $_->vc_iban && abs(abs($_->amount) - abs($bt->amount)) < 0.01 ) {
200 $iban = $open_invoice->customer->iban if $open_invoice->is_sales;
201 $iban = $open_invoice->vendor->iban if ! $open_invoice->is_sales;
202 if($bt->{remote_account_number} eq $iban && abs(abs($open_invoice->amount) - abs($bt->amount)) < 0.01 ) {
203 ($open_invoice->{agreement}, $open_invoice->{rule_matches}) = $bt->get_agreement_with_invoice($open_invoice);
204 $open_invoice->{agreement} += 5;
205 $open_invoice->{rule_matches} .= 'sepa_export_item(5) ';
206 $main::lxdebug->message(LXDebug->DEBUG2(),"sepa invoice_id=".$open_invoice->id." agreement=".$open_invoice->{agreement}." rules matches=".$open_invoice->{rule_matches});
207 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount*$factor,2);
214 # try to match the current $bt to each of the open_invoices, saving the
215 # results of get_agreement_with_invoice in $open_invoice->{agreement} and
216 # $open_invoice->{rule_matches}.
218 # The values are overwritten each time a new bt is checked, so at the end
219 # of each bt the likely results are filtered and those values are stored in
220 # the arrays $bt->{proposals} and $bt->{rule_matches}, and the agreement
221 # score is stored in $bt->{agreement}
223 foreach my $open_invoice (@all_non_sepa_invoices){
224 ($open_invoice->{agreement}, $open_invoice->{rule_matches}) = $bt->get_agreement_with_invoice($open_invoice);
225 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount*($open_invoice->{is_ar}?1:-1),2);
226 $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;
230 my $min_agreement = 3; # suggestions must have at least this score
232 my $max_agreement = max map { $_->{agreement} } @all_open_invoices;
234 # add open_invoices with highest agreement into array $bt->{proposals}
235 if ( $max_agreement >= $min_agreement ) {
236 $bt->{proposals} = [ grep { $_->{agreement} == $max_agreement } @all_open_invoices ];
237 $bt->{agreement} = $max_agreement; #scalar @{ $bt->{proposals} } ? $agreement + 1 : '';
239 # store the rule_matches in a separate array, so they can be displayed in template
240 foreach ( @{ $bt->{proposals} } ) {
241 push(@{$bt->{rule_matches}}, $_->{rule_matches});
247 # separate filter for proposals (second tab, agreement >= 5 and exactly one match)
248 # to qualify as a proposal there has to be
249 # * agreement >= 5 TODO: make threshold configurable in configuration
250 # * there must be only one exact match
251 # * depending on whether sales or purchase the amount has to have the correct sign (so Gutschriften don't work?)
252 my $proposal_threshold = 5;
253 my @otherproposals = grep {
254 ($_->{agreement} >= $proposal_threshold)
255 && (1 == scalar @{ $_->{proposals} })
256 && (@{ $_->{proposals} }[0]->is_sales ? abs(@{ $_->{proposals} }[0]->amount - $_->amount) < 0.01
257 : abs(@{ $_->{proposals} }[0]->amount + $_->amount) < 0.01)
258 } @{ $bank_transactions };
260 push ( @proposals, @otherproposals);
262 # sort bank transaction proposals by quality (score) of proposal
263 $bank_transactions = [ sort { $a->{agreement} <=> $b->{agreement} } @{ $bank_transactions } ] if $::form->{sort_by} eq 'proposal' and $::form->{sort_dir} == 1;
264 $bank_transactions = [ sort { $b->{agreement} <=> $a->{agreement} } @{ $bank_transactions } ] if $::form->{sort_by} eq 'proposal' and $::form->{sort_dir} == 0;
266 $::request->layout->add_javascripts("kivi.BankTransaction.js");
267 $self->render('bank_transactions/list',
268 title => t8('Bank transactions MT940'),
269 BANK_TRANSACTIONS => $bank_transactions,
270 PROPOSALS => \@proposals,
271 bank_account => $bank_account,
272 ui_tab => scalar(@proposals) > 0?1:0,
276 sub action_assign_invoice {
279 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
281 $self->render('bank_transactions/assign_invoice',
283 title => t8('Assign invoice'),);
286 sub action_create_invoice {
288 my %myconfig = %main::myconfig;
290 $self->transaction(SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id}));
292 my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(account_number => $self->transaction->{remote_account_number});
293 my $use_vendor_filter = $self->transaction->{remote_account_number} && $vendor_of_transaction;
295 my $templates = SL::DB::Manager::RecordTemplate->get_all(
296 where => [ template_type => 'ap_transaction' ],
297 with_objects => [ qw(employee vendor) ],
301 $templates = [ grep { $_->vendor_id == $vendor_of_transaction->id } @{ $templates } ] if $use_vendor_filter;
303 $self->callback($self->url_for(
305 'filter.bank_account' => $::form->{filter}->{bank_account},
306 'filter.todate' => $::form->{filter}->{todate},
307 'filter.fromdate' => $::form->{filter}->{fromdate},
311 'bank_transactions/create_invoice',
313 title => t8('Create invoice'),
314 TEMPLATES => $templates,
315 vendor_id => $use_vendor_filter ? $vendor_of_transaction->id : undef,
316 vendor_name => $use_vendor_filter ? $vendor_of_transaction->name : undef,
320 sub action_ajax_payment_suggestion {
323 # based on a BankTransaction ID and a Invoice or PurchaseInvoice ID passed via $::form,
324 # create an HTML blob to be used by the js function add_invoices in templates/webpages/bank_transactions/list.html
325 # and return encoded as JSON
327 my $bt = SL::DB::Manager::BankTransaction->find_by( id => $::form->{bt_id} );
328 my $invoice = SL::DB::Manager::Invoice->find_by( id => $::form->{prop_id} ) || SL::DB::Manager::PurchaseInvoice->find_by( id => $::form->{prop_id} );
330 die unless $bt and $invoice;
332 my @select_options = $invoice->get_payment_select_options_for_bank_transaction($::form->{bt_id});
335 $html = $self->render(
336 'bank_transactions/_payment_suggestion', { output => 0 },
337 bt_id => $::form->{bt_id},
338 prop_id => $::form->{prop_id},
340 SELECT_OPTIONS => \@select_options,
343 $self->render(\ SL::JSON::to_json( { 'html' => "$html" } ), { layout => 0, type => 'json', process => 0 });
346 sub action_filter_templates {
349 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
350 my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(account_number => $self->{transaction}->{remote_account_number});
353 push @filter, ('vendor.id' => $::form->{vendor_id}) if $::form->{vendor_id};
354 push @filter, ('vendor.name' => { ilike => '%' . $::form->{vendor} . '%' }) if $::form->{vendor};
356 my $templates = SL::DB::Manager::RecordTemplate->get_all(
357 where => [ template_type => 'ap_transaction', (or => \@filter) x !!@filter ],
358 with_objects => [ qw(employee vendor) ],
361 $::form->{filter} //= {};
363 $self->callback($self->url_for(
365 'filter.bank_account' => $::form->{filter}->{bank_account},
366 'filter.todate' => $::form->{filter}->{todate},
367 'filter.fromdate' => $::form->{filter}->{fromdate},
370 my $output = $self->render(
371 'bank_transactions/_template_list',
373 TEMPLATES => $templates,
376 $self->render(\to_json({ html => $output }), { type => 'json', process => 0 });
379 sub action_ajax_add_list {
382 my @where_sale = (amount => { ne => \'paid' });
383 my @where_purchase = (amount => { ne => \'paid' });
385 if ($::form->{invnumber}) {
386 push @where_sale, (invnumber => { ilike => like($::form->{invnumber})});
387 push @where_purchase, (invnumber => { ilike => like($::form->{invnumber})});
390 if ($::form->{amount}) {
391 push @where_sale, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
392 push @where_purchase, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
395 if ($::form->{vcnumber}) {
396 push @where_sale, ('customer.customernumber' => { ilike => like($::form->{vcnumber})});
397 push @where_purchase, ('vendor.vendornumber' => { ilike => like($::form->{vcnumber})});
400 if ($::form->{vcname}) {
401 push @where_sale, ('customer.name' => { ilike => like($::form->{vcname})});
402 push @where_purchase, ('vendor.name' => { ilike => like($::form->{vcname})});
405 if ($::form->{transdatefrom}) {
406 my $fromdate = $::locale->parse_date_to_object($::form->{transdatefrom});
407 if ( ref($fromdate) eq 'DateTime' ) {
408 push @where_sale, ('transdate' => { ge => $fromdate});
409 push @where_purchase, ('transdate' => { ge => $fromdate});
413 if ($::form->{transdateto}) {
414 my $todate = $::locale->parse_date_to_object($::form->{transdateto});
415 if ( ref($todate) eq 'DateTime' ) {
416 $todate->add(days => 1);
417 push @where_sale, ('transdate' => { lt => $todate});
418 push @where_purchase, ('transdate' => { lt => $todate});
422 my $all_open_ar_invoices = SL::DB::Manager::Invoice ->get_all(where => \@where_sale, with_objects => 'customer');
423 my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => \@where_purchase, with_objects => 'vendor');
425 my @all_open_invoices = @{ $all_open_ar_invoices };
426 # add ap invoices, filtering out subcent open amounts
427 push @all_open_invoices, grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
429 @all_open_invoices = sort { $a->id <=> $b->id } @all_open_invoices;
431 my $output = $self->render(
432 'bank_transactions/add_list',
434 INVOICES => \@all_open_invoices,
437 my %result = ( count => 0, html => $output );
439 $self->render(\to_json(\%result), { type => 'json', process => 0 });
442 sub action_ajax_accept_invoices {
445 my @selected_invoices;
446 foreach my $invoice_id (@{ $::form->{invoice_id} || [] }) {
447 my $invoice_object = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
448 push @selected_invoices, $invoice_object;
452 'bank_transactions/invoices',
454 INVOICES => \@selected_invoices,
455 bt_id => $::form->{bt_id},
462 return 0 if !$::form->{invoice_ids};
464 my %invoice_hash = %{ delete $::form->{invoice_ids} }; # each key (the bt line with a bt_id) contains an array of invoice_ids
466 # e.g. three partial payments with bt_ids 54, 55 and 56 for invoice with id 74:
479 # or if the payment with bt_id 44 is used to pay invoices with ids 50, 51 and 52
481 # '44' => [ '50', '51', 52' ]
484 $::form->{invoice_skontos} ||= {}; # hash of arrays containing the payment types, could be empty
486 # a bank_transaction may be assigned to several invoices, i.e. a customer
487 # might pay several open invoices with one transaction
493 if ( $::form->{proposal_ids} ) {
494 foreach (@{ $::form->{proposal_ids} }) {
495 my $bank_transaction_id = $_;
496 my $invoice_ids = $invoice_hash{$_};
497 push @{ $self->problems }, $self->save_single_bank_transaction(
498 bank_transaction_id => $bank_transaction_id,
499 invoice_ids => $invoice_ids,
500 sources => ($::form->{sources} // {})->{$_},
501 memos => ($::form->{memos} // {})->{$_},
503 $count += scalar( @{$invoice_ids} );
506 while ( my ($bank_transaction_id, $invoice_ids) = each(%invoice_hash) ) {
507 push @{ $self->problems }, $self->save_single_bank_transaction(
508 bank_transaction_id => $bank_transaction_id,
509 invoice_ids => $invoice_ids,
510 sources => [ map { $::form->{"sources_${bank_transaction_id}_${_}"} } @{ $invoice_ids } ],
511 memos => [ map { $::form->{"memos_${bank_transaction_id}_${_}"} } @{ $invoice_ids } ],
513 $count += scalar( @{$invoice_ids} );
516 foreach (@{ $self->problems }) {
517 $count-- if $_->{result} eq 'error';
522 sub action_save_invoices {
524 my $count = $self->save_invoices();
526 flash('ok', t8('#1 invoice(s) saved.', $count));
528 $self->action_list();
531 sub action_save_proposals {
534 if ( $::form->{proposal_ids} ) {
535 my $propcount = scalar(@{ $::form->{proposal_ids} });
536 if ( $propcount > 0 ) {
537 my $count = $self->save_invoices();
539 flash('ok', t8('#1 proposal(s) with #2 invoice(s) saved.', $propcount, $count));
542 $self->action_list();
546 sub is_collective_transaction {
547 my ($self, $bt) = @_;
548 return $bt->transaction_code eq "191";
551 sub save_single_bank_transaction {
552 my ($self, %params) = @_;
556 bank_transaction => SL::DB::Manager::BankTransaction->find_by(id => $params{bank_transaction_id}),
560 if (!$data{bank_transaction}) {
564 message => $::locale->text('The ID #1 is not a valid database ID.', $data{bank_transaction_id}),
571 my $bt_id = $data{bank_transaction_id};
572 my $bank_transaction = $data{bank_transaction};
573 my $sign = $bank_transaction->amount < 0 ? -1 : 1;
574 my $amount_of_transaction = $sign * $bank_transaction->amount;
575 my $payment_received = $bank_transaction->amount > 0;
576 my $payment_sent = $bank_transaction->amount < 0;
579 foreach my $invoice_id (@{ $params{invoice_ids} }) {
580 my $invoice = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
585 message => $::locale->text("The ID #1 is not a valid database ID.", $invoice_id),
588 push @{ $data{invoices} }, $invoice;
591 if ( $payment_received
592 && any { ( $_->is_sales && ($_->amount < 0))
593 || (!$_->is_sales && ($_->amount > 0))
594 } @{ $data{invoices} }) {
598 message => $::locale->text("Received payments can only be posted for sales invoices and purchase credit notes."),
603 && any { ( $_->is_sales && ($_->amount > 0))
604 || (!$_->is_sales && ($_->amount < 0) && ($_->invoice_type eq 'purchase_invoice'))
605 } @{ $data{invoices} }) {
609 message => $::locale->text("Sent payments can only be posted for purchase invoices and sales credit notes."),
613 my $max_invoices = scalar(@{ $data{invoices} });
616 foreach my $invoice (@{ $data{invoices} }) {
617 my $source = ($data{sources} // [])->[$n_invoices];
618 my $memo = ($data{memos} // [])->[$n_invoices];
622 # Check if bank_transaction already has a link to the invoice, may only be linked once per invoice
623 # This might be caused by the user reloading a page and resending the form
624 if (_existing_record_link($bank_transaction, $invoice)) {
628 message => $::locale->text("Bank transaction with id #1 has already been linked to #2.", $bank_transaction->id, $invoice->displayable_name),
632 if (!$amount_of_transaction && $invoice->open_amount) {
636 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."),
641 if ( defined $::form->{invoice_skontos}->{"$bt_id"} ) {
642 $payment_type = shift(@{ $::form->{invoice_skontos}->{"$bt_id"} });
644 $payment_type = 'without_skonto';
648 # pay invoice or go to the next bank transaction if the amount is not sufficiently high
649 if ($invoice->open_amount <= $amount_of_transaction && $n_invoices < $max_invoices) {
650 my $open_amount = ($payment_type eq 'with_skonto_pt'?$invoice->amount_less_skonto:$invoice->open_amount);
651 # first calculate new bank transaction amount ...
652 if ($invoice->is_sales) {
653 $amount_of_transaction -= $sign * $open_amount;
654 $bank_transaction->invoice_amount($bank_transaction->invoice_amount + $open_amount);
656 $amount_of_transaction += $sign * $open_amount;
657 $bank_transaction->invoice_amount($bank_transaction->invoice_amount - $open_amount);
659 # ... and then pay the invoice
660 $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
661 trans_id => $invoice->id,
662 amount => $open_amount,
663 payment_type => $payment_type,
666 transdate => $bank_transaction->transdate->to_kivitendo);
667 } elsif (( $invoice->is_sales && $invoice->invoice_type eq 'credit_note' ) ||
668 (!$invoice->is_sales && $invoice->invoice_type eq 'ap_transaction' )) {
669 # no check for overpayment/multiple payments
670 $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
671 trans_id => $invoice->id,
672 amount => $invoice->open_amount,
673 payment_type => $payment_type,
676 transdate => $bank_transaction->transdate->to_kivitendo);
677 } else { # use the whole amount of the bank transaction for the invoice, overpay the invoice if necessary
678 my $overpaid_amount = $amount_of_transaction - $invoice->open_amount;
679 $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
680 trans_id => $invoice->id,
681 amount => $amount_of_transaction,
682 payment_type => $payment_type,
685 transdate => $bank_transaction->transdate->to_kivitendo);
686 $bank_transaction->invoice_amount($bank_transaction->amount);
687 $amount_of_transaction = 0;
689 if ($overpaid_amount >= 0.01) {
693 message => $::locale->text('Invoice #1 was overpaid by #2.', $invoice->invnumber, $::form->format_amount(\%::myconfig, $overpaid_amount, 2)),
697 # Record a record link from the bank transaction to the invoice
699 from_table => 'bank_transactions',
701 to_table => $invoice->is_sales ? 'ar' : 'ap',
702 to_id => $invoice->id,
705 SL::DB::RecordLink->new(@props)->save;
707 # "close" a sepa_export_item if it exists
708 # code duplicated in action_save_proposals!
709 # currently only works, if there is only exactly one open sepa_export_item
710 if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
711 if ( scalar @$seis == 1 ) {
712 # moved the execution and the check for sepa_export into a method,
713 # this isn't part of a transaction, though
714 $seis->[0]->set_executed if $invoice->id == $seis->[0]->arap_id;
719 $bank_transaction->save;
721 # 'undef' means 'no error' here.
726 my $rez = $data{bank_transaction}->db->with_transaction(sub {
728 $error = $worker->();
739 # Rollback Fehler nicht weiterreichen
743 return grep { $_ } ($error, @warnings);
751 $::auth->assert('bank_transaction');
758 sub make_filter_summary {
761 my $filter = $::form->{filter} || {};
765 [ $filter->{"transdate:date::ge"}, $::locale->text('Transdate') . " " . $::locale->text('From Date') ],
766 [ $filter->{"transdate:date::le"}, $::locale->text('Transdate') . " " . $::locale->text('To Date') ],
767 [ $filter->{"valutadate:date::ge"}, $::locale->text('Valutadate') . " " . $::locale->text('From Date') ],
768 [ $filter->{"valutadate:date::le"}, $::locale->text('Valutadate') . " " . $::locale->text('To Date') ],
769 [ $filter->{"amount:number"}, $::locale->text('Amount') ],
770 [ $filter->{"bank_account_id:integer"}, $::locale->text('Local bank account') ],
774 push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
777 $self->{filter_summary} = join ', ', @filter_strings;
783 my $callback = $self->models->get_callback;
785 my $report = SL::ReportGenerator->new(\%::myconfig, $::form);
786 $self->{report} = $report;
788 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);
789 my @sortable = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount purpose local_account_number local_bank_code);
792 transdate => { sub => sub { $_[0]->transdate_as_date } },
793 valutadate => { sub => sub { $_[0]->valutadate_as_date } },
795 remote_account_number => { },
796 remote_bank_code => { },
797 amount => { sub => sub { $_[0]->amount_as_number },
799 invoice_amount => { sub => sub { $_[0]->invoice_amount_as_number },
801 invoices => { sub => sub { $_[0]->linked_invoices } },
802 currency => { sub => sub { $_[0]->currency->name } },
804 local_account_number => { sub => sub { $_[0]->local_bank_account->account_number } },
805 local_bank_code => { sub => sub { $_[0]->local_bank_account->bank_code } },
806 local_bank_name => { sub => sub { $_[0]->local_bank_account->name } },
810 map { $column_defs{$_}->{text} ||= $::locale->text( $self->models->get_sort_spec->{$_}->{title} ) } keys %column_defs;
812 $report->set_options(
813 std_column_visibility => 1,
814 controller_class => 'BankTransaction',
815 output_format => 'HTML',
816 top_info_text => $::locale->text('Bank transactions'),
817 title => $::locale->text('Bank transactions'),
818 allow_pdf_export => 1,
819 allow_csv_export => 1,
821 $report->set_columns(%column_defs);
822 $report->set_column_order(@columns);
823 $report->set_export_options(qw(list_all filter));
824 $report->set_options_from_form;
825 $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
826 $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
828 my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted();
830 $report->set_options(
831 raw_top_info_text => $self->render('bank_transactions/report_top', { output => 0 }, BANK_ACCOUNTS => $bank_accounts),
832 raw_bottom_info_text => $self->render('bank_transactions/report_bottom', { output => 0 }),
836 sub _existing_record_link {
837 my ($bt, $invoice) = @_;
839 # check whether a record link from banktransaction $bt already exists to
840 # invoice $invoice, returns 1 if that is the case
842 die unless $bt->isa("SL::DB::BankTransaction") && ( $invoice->isa("SL::DB::Invoice") || $invoice->isa("SL::DB::PurchaseInvoice") );
844 my $linked_record_to_table = $invoice->is_sales ? 'Invoice' : 'PurchaseInvoice';
845 my $linked_records = $bt->linked_records( direction => 'to', to => $linked_record_to_table, query => [ id => $invoice->id ] );
847 return @$linked_records ? 1 : 0;
850 sub init_problems { [] }
855 SL::Controller::Helper::GetModels->new(
860 dir => 0, # 1 = ASC, 0 = DESC : default sort is newest at top
862 transdate => t8('Transdate'),
863 remote_name => t8('Remote name'),
864 amount => t8('Amount'),
865 invoice_amount => t8('Assigned'),
866 invoices => t8('Linked invoices'),
867 valutadate => t8('Valutadate'),
868 remote_account_number => t8('Remote account number'),
869 remote_bank_code => t8('Remote bank code'),
870 currency => t8('Currency'),
871 purpose => t8('Purpose'),
872 local_account_number => t8('Local account number'),
873 local_bank_code => t8('Local bank code'),
874 local_bank_name => t8('Bank account'),
876 with_objects => [ 'local_bank_account', 'currency' ],
880 sub load_ap_record_template_url {
881 my ($self, $template) = @_;
883 return $self->url_for(
884 controller => 'ap.pl',
885 action => 'load_record_template',
887 'form_defaults.amount_1' => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
888 'form_defaults.transdate' => $self->transaction->transdate_as_date,
889 'form_defaults.duedate' => $self->transaction->transdate_as_date,
890 'form_defaults.datepaid_1' => $self->transaction->transdate_as_date,
891 'form_defaults.paid_1' => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
892 'form_defaults.currency' => $self->transaction->currency->name,
893 'form_defaults.AP_paid_1' => $self->transaction->local_bank_account->chart->accno,
894 'form_defaults.callback' => $self->callback,
898 sub setup_search_action_bar {
899 my ($self, %params) = @_;
901 for my $bar ($::request->layout->get('actionbar')) {
905 submit => [ '#search_form', { action => 'BankTransaction/list' } ],
906 accesskey => 'enter',
912 sub setup_list_all_action_bar {
913 my ($self, %params) = @_;
915 for my $bar ($::request->layout->get('actionbar')) {
919 submit => [ '#filter_form', { action => 'BankTransaction/list_all' } ],
920 accesskey => 'enter',
935 SL::Controller::BankTransaction - Posting payments to invoices from
936 bank transactions imported earlier
942 =item C<save_single_bank_transaction %params>
944 Takes a bank transaction ID (as parameter C<bank_transaction_id> and
945 tries to post its amount to a certain number of invoices (parameter
946 C<invoice_ids>, an array ref of database IDs to purchase or sales
949 The whole function is wrapped in a database transaction. If an
950 exception occurs the bank transaction is not posted at all. The same
951 is true if the code detects an error during the execution, e.g. a bank
952 transaction that's already been posted earlier. In both cases the
953 database transaction will be rolled back.
955 If warnings but not errors occur the database transaction is still
958 The return value is an error object or C<undef> if the function
959 succeeded. The calling function will collect all warnings and errors
960 and display them in a nicely formatted table if any occurred.
962 An error object is a hash reference containing the following members:
966 =item * C<result> — can be either C<warning> or C<error>. Warnings are
967 displayed slightly different than errors.
969 =item * C<message> — a human-readable message included in the list of
970 errors meant as the description of why the problem happened
972 =item * C<bank_transaction_id>, C<invoice_ids> — the same parameters
973 that the function was called with
975 =item * C<bank_transaction> — the database object
976 (C<SL::DB::BankTransaction>) corresponding to C<bank_transaction_id>
978 =item * C<invoices> — an array ref of the database objects (either
979 C<SL::DB::Invoice> or C<SL::DB::PurchaseInvoice>) corresponding to
988 Niclas Zimmermann E<lt>niclas@kivitendo-premium.deE<gt>,
989 Geoffrey Richardson E<lt>information@richardson-bueren.deE<gt>