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, map { $_->{is_ar}=1 ; $_ } grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ar_invoices };
112 push @all_open_invoices, map { $_->{is_ar}=0 ; $_ } 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 my $factor = ( $_->ar_id == $open_invoice->id>0?1:-1);
127 #$main::lxdebug->message(LXDebug->DEBUG2(),"exitem=".$_->id." for invoice ".$open_invoice->id." factor=".$factor);
128 $open_invoice->{sepa_export_item} = $_ ;
129 $open_invoice->{skonto_type} = $_->payment_type;
130 $sepa_exports{$_->sepa_export_id} ||= { count => 0, is_ar => 0, amount => 0, proposed => 0, invoices => [], item => $_ };
131 $sepa_exports{$_->sepa_export_id}->{count}++ ;
132 $sepa_exports{$_->sepa_export_id}->{is_ar}++ if $_->ar_id == $open_invoice->id;
133 $sepa_exports{$_->sepa_export_id}->{amount} += $_->amount * $factor;
134 push ( @{ $sepa_exports{$_->sepa_export_id}->{invoices}} , $open_invoice );
135 #$main::lxdebug->message(LXDebug->DEBUG2(),"amount for export id ".$_->sepa_export_id." = ".
136 # $sepa_exports{$_->sepa_export_id}->{amount}." count = ".
137 # $sepa_exports{$_->sepa_export_id}->{count}." is_ar = ".
138 # $sepa_exports{$_->sepa_export_id}->{is_ar} );
139 push @all_sepa_invoices , $open_invoice;
142 push @all_non_sepa_invoices , $open_invoice if ! $open_invoice->{sepa_export_item};
145 # try to match each bank_transaction with each of the possible open invoices
147 @all_open_invoices = @all_non_sepa_invoices;
150 foreach my $bt (@{ $bank_transactions }) {
151 ## 5 Stellen hinter dem Komma auf 2 Stellen reduzieren
152 $bt->amount($bt->amount*1);
153 $bt->invoice_amount($bt->invoice_amount*1);
154 $main::lxdebug->message(LXDebug->DEBUG2(),"BT ".$bt->id." amount=".$bt->amount." invoice_amount=".$bt->invoice_amount." remote=". $bt->{remote_name});
156 $bt->{proposals} = [];
158 $bt->{remote_name} .= $bt->{remote_name_1} if $bt->{remote_name_1};
160 if ( $self->is_collective_transaction($bt) ) {
161 foreach ( keys %sepa_exports) {
162 #$main::lxdebug->message(LXDebug->DEBUG2(),"Exp ID=".$_." compare sum amount ".($sepa_exports{$_}->{amount} *1) ." == ".($bt->amount * 1));
163 if ( $bt->transactioncode eq '191' && abs(($sepa_exports{$_}->{amount} * 1) - ($bt->amount * 1)) < 0.01 ) {
165 $bt->{proposals} = $sepa_exports{$_}->{invoices} ;
166 $bt->{agreement} = 20;
167 $bt->{rule_matches} = 'sepa_export_item(20)';
168 $sepa_exports{$_}->{proposed}=1;
169 #$main::lxdebug->message(LXDebug->DEBUG2(),"has ".scalar($bt->{proposals})." invoices");
170 push(@proposals, $bt);
175 next unless $bt->{remote_name}; # bank has no name, usually fees, use create invoice to assign
177 foreach ( @{$all_open_sepa_export_items}) {
178 last if scalar (@all_sepa_invoices) == 0;
179 foreach my $open_invoice (@all_sepa_invoices){
180 if ( $_->ap_id == $open_invoice->id || $_->ar_id == $open_invoice->id ) {
181 #$main::lxdebug->message(LXDebug->DEBUG2(),"exitem2=".$_->id." for invoice ".$open_invoice->id);
182 my $factor = ( $_->ar_id == $open_invoice->id?1:-1);
183 $_->amount($_->amount*1);
184 #$main::lxdebug->message(LXDebug->DEBUG2(),"remote account '".$bt->{remote_account_number}."' bt_amount=". ($bt->amount * $factor));
185 #$main::lxdebug->message(LXDebug->DEBUG2(),"compare with '".$_->vc_iban."' amount=".$_->amount);
186 if ( $bt->{remote_account_number} eq $_->vc_iban && abs(( $_->amount *1 ) - ($bt->amount * $factor)) < 0.01 ) {
187 push ($bt->{proposals},$open_invoice );
188 $bt->{agreement} = 20;
189 $bt->{rule_matches} = 'sepa_export_item(20)';
190 #$main::lxdebug->message(LXDebug->DEBUG2(),"found invoice");
191 push(@proposals, $bt);
192 @all_sepa_invoices = grep { $_ != $open_invoice } @all_sepa_invoices;
199 # try to match the current $bt to each of the open_invoices, saving the
200 # results of get_agreement_with_invoice in $open_invoice->{agreement} and
201 # $open_invoice->{rule_matches}.
203 # The values are overwritten each time a new bt is checked, so at the end
204 # of each bt the likely results are filtered and those values are stored in
205 # the arrays $bt->{proposals} and $bt->{rule_matches}, and the agreement
206 # score is stored in $bt->{agreement}
208 foreach my $open_invoice (@all_open_invoices){
209 ($open_invoice->{agreement}, $open_invoice->{rule_matches}) = $bt->get_agreement_with_invoice($open_invoice);
210 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount*($open_invoice->{is_ar}?1:-1),2);
211 # $main::lxdebug->message(LXDebug->DEBUG2(),"agreement=".$open_invoice->{agreement}." rules matches=".$open_invoice->{rule_matches});
215 my $min_agreement = 3; # suggestions must have at least this score
217 my $max_agreement = max map { $_->{agreement} } @all_open_invoices;
219 # add open_invoices with highest agreement into array $bt->{proposals}
220 if ( $max_agreement >= $min_agreement ) {
221 $bt->{proposals} = [ grep { $_->{agreement} == $max_agreement } @all_open_invoices ];
222 $bt->{agreement} = $max_agreement; #scalar @{ $bt->{proposals} } ? $agreement + 1 : '';
224 # store the rule_matches in a separate array, so they can be displayed in template
225 foreach ( @{ $bt->{proposals} } ) {
226 push(@{$bt->{rule_matches}}, $_->{rule_matches});
232 # separate filter for proposals (second tab, agreement >= 5 and exactly one match)
233 # to qualify as a proposal there has to be
234 # * agreement >= 5 TODO: make threshold configurable in configuration
235 # * there must be only one exact match
236 # * depending on whether sales or purchase the amount has to have the correct sign (so Gutschriften don't work?)
237 my $proposal_threshold = 5;
238 my @otherproposals = grep {
239 ($_->{agreement} >= $proposal_threshold)
240 && (1 == scalar @{ $_->{proposals} })
241 && (@{ $_->{proposals} }[0]->is_sales ? abs(@{ $_->{proposals} }[0]->amount - $_->amount) < 0.01
242 : abs(@{ $_->{proposals} }[0]->amount + $_->amount) < 0.01)
243 } @{ $bank_transactions };
245 push ( @proposals, @otherproposals);
247 # sort bank transaction proposals by quality (score) of proposal
248 $bank_transactions = [ sort { $a->{agreement} <=> $b->{agreement} } @{ $bank_transactions } ] if $::form->{sort_by} eq 'proposal' and $::form->{sort_dir} == 1;
249 $bank_transactions = [ sort { $b->{agreement} <=> $a->{agreement} } @{ $bank_transactions } ] if $::form->{sort_by} eq 'proposal' and $::form->{sort_dir} == 0;
252 $self->render('bank_transactions/list',
253 title => t8('Bank transactions MT940'),
254 BANK_TRANSACTIONS => $bank_transactions,
255 PROPOSALS => \@proposals,
256 bank_account => $bank_account,
257 ui_tab => scalar(@proposals) > 0?1:0,
261 sub action_assign_invoice {
264 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
266 $self->render('bank_transactions/assign_invoice',
268 title => t8('Assign invoice'),);
271 sub action_create_invoice {
273 my %myconfig = %main::myconfig;
275 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
276 my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(account_number => $self->{transaction}->{remote_account_number});
278 my $use_vendor_filter = $self->{transaction}->{remote_account_number} && $vendor_of_transaction;
280 my $drafts = SL::DB::Manager::Draft->get_all(where => [ module => 'ap'] , with_objects => 'employee');
284 foreach my $draft ( @{ $drafts } ) {
285 my $draft_as_object = YAML::Load($draft->form);
286 my $vendor = SL::DB::Manager::Vendor->find_by(id => $draft_as_object->{vendor_id});
287 $draft->{vendor} = $vendor->name;
288 $draft->{vendor_id} = $vendor->id;
289 push @filtered_drafts, $draft;
293 @filtered_drafts = grep { $_->{vendor_id} == $vendor_of_transaction->id } @filtered_drafts if $use_vendor_filter;
295 my $all_vendors = SL::DB::Manager::Vendor->get_all();
296 my $callback = $self->url_for(action => 'list',
297 'filter.bank_account' => $::form->{filter}->{bank_account},
298 'filter.todate' => $::form->{filter}->{todate},
299 'filter.fromdate' => $::form->{filter}->{fromdate});
302 'bank_transactions/create_invoice',
304 title => t8('Create invoice'),
305 DRAFTS => \@filtered_drafts,
306 vendor_id => $use_vendor_filter ? $vendor_of_transaction->id : undef,
307 vendor_name => $use_vendor_filter ? $vendor_of_transaction->name : undef,
308 ALL_VENDORS => $all_vendors,
309 limit => $myconfig{vclimit},
310 callback => $callback,
314 sub action_ajax_payment_suggestion {
317 # based on a BankTransaction ID and a Invoice or PurchaseInvoice ID passed via $::form,
318 # create an HTML blob to be used by the js function add_invoices in templates/webpages/bank_transactions/list.html
319 # and return encoded as JSON
321 my $bt = SL::DB::Manager::BankTransaction->find_by( id => $::form->{bt_id} );
322 my $invoice = SL::DB::Manager::Invoice->find_by( id => $::form->{prop_id} ) || SL::DB::Manager::PurchaseInvoice->find_by( id => $::form->{prop_id} );
324 die unless $bt and $invoice;
326 my @select_options = $invoice->get_payment_select_options_for_bank_transaction($::form->{bt_id});
329 $html .= SL::Presenter->input_tag('invoice_ids.' . $::form->{bt_id} . '[]', $::form->{prop_id} , type => 'hidden');
330 $html .= SL::Presenter->escape(t8('Invno.') . ': ' . $invoice->invnumber . ' ');
331 $html .= SL::Presenter->escape(t8('Open amount') . ': ' . $::form->format_amount(\%::myconfig, $invoice->open_amount, 2) . ' ');
332 $html .= SL::Presenter->select_tag('invoice_skontos.' . $::form->{bt_id} . '[]',
334 value_key => 'payment_type',
335 title_key => 'display' )
337 $html .= '<a href=# onclick="delete_invoice(' . $::form->{bt_id} . ',' . $::form->{prop_id} . ');">x</a>';
338 $html = SL::Presenter->html_tag('div', $html, id => $::form->{bt_id} . '.' . $::form->{prop_id});
340 $self->render(\ SL::JSON::to_json( { 'html' => $html } ), { layout => 0, type => 'json', process => 0 });
343 sub action_filter_drafts {
346 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
347 my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(account_number => $self->{transaction}->{remote_account_number});
349 my $drafts = SL::DB::Manager::Draft->get_all(with_objects => 'employee');
353 foreach my $draft ( @{ $drafts } ) {
354 my $draft_as_object = YAML::Load($draft->form);
355 next unless $draft_as_object->{vendor_id}; # we cannot filter for vendor name, if this is a gl draft
357 my $vendor = SL::DB::Manager::Vendor->find_by(id => $draft_as_object->{vendor_id});
358 $draft->{vendor} = $vendor->name;
359 $draft->{vendor_id} = $vendor->id;
361 push @filtered_drafts, $draft;
364 my $vendor_name = $::form->{vendor};
365 my $vendor_id = $::form->{vendor_id};
368 @filtered_drafts = grep { $_->{vendor_id} == $vendor_id } @filtered_drafts if $vendor_id;
369 @filtered_drafts = grep { $_->{vendor} =~ /$vendor_name/i } @filtered_drafts if $vendor_name;
371 my $output = $self->render(
372 'bank_transactions/filter_drafts',
374 DRAFTS => \@filtered_drafts,
377 my %result = ( count => 0, html => $output );
379 $self->render(\to_json(\%result), { type => 'json', process => 0 });
382 sub action_ajax_add_list {
385 my @where_sale = (amount => { ne => \'paid' });
386 my @where_purchase = (amount => { ne => \'paid' });
388 if ($::form->{invnumber}) {
389 push @where_sale, (invnumber => { ilike => like($::form->{invnumber})});
390 push @where_purchase, (invnumber => { ilike => like($::form->{invnumber})});
393 if ($::form->{amount}) {
394 push @where_sale, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
395 push @where_purchase, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
398 if ($::form->{vcnumber}) {
399 push @where_sale, ('customer.customernumber' => { ilike => like($::form->{vcnumber})});
400 push @where_purchase, ('vendor.vendornumber' => { ilike => like($::form->{vcnumber})});
403 if ($::form->{vcname}) {
404 push @where_sale, ('customer.name' => { ilike => like($::form->{vcname})});
405 push @where_purchase, ('vendor.name' => { ilike => like($::form->{vcname})});
408 if ($::form->{transdatefrom}) {
409 my $fromdate = $::locale->parse_date_to_object($::form->{transdatefrom});
410 if ( ref($fromdate) eq 'DateTime' ) {
411 push @where_sale, ('transdate' => { ge => $fromdate});
412 push @where_purchase, ('transdate' => { ge => $fromdate});
416 if ($::form->{transdateto}) {
417 my $todate = $::locale->parse_date_to_object($::form->{transdateto});
418 if ( ref($todate) eq 'DateTime' ) {
419 $todate->add(days => 1);
420 push @where_sale, ('transdate' => { lt => $todate});
421 push @where_purchase, ('transdate' => { lt => $todate});
425 my $all_open_ar_invoices = SL::DB::Manager::Invoice ->get_all(where => \@where_sale, with_objects => 'customer');
426 my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => \@where_purchase, with_objects => 'vendor');
428 my @all_open_invoices = @{ $all_open_ar_invoices };
429 # add ap invoices, filtering out subcent open amounts
430 push @all_open_invoices, grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
432 @all_open_invoices = sort { $a->id <=> $b->id } @all_open_invoices;
434 my $output = $self->render(
435 'bank_transactions/add_list',
437 INVOICES => \@all_open_invoices,
440 my %result = ( count => 0, html => $output );
442 $self->render(\to_json(\%result), { type => 'json', process => 0 });
445 sub action_ajax_accept_invoices {
448 my @selected_invoices;
449 foreach my $invoice_id (@{ $::form->{invoice_id} || [] }) {
450 my $invoice_object = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
451 push @selected_invoices, $invoice_object;
455 'bank_transactions/invoices',
457 INVOICES => \@selected_invoices,
458 bt_id => $::form->{bt_id},
465 return 0 if !$::form->{invoice_ids};
467 my %invoice_hash = %{ delete $::form->{invoice_ids} }; # each key (the bt line with a bt_id) contains an array of invoice_ids
469 # e.g. three partial payments with bt_ids 54, 55 and 56 for invoice with id 74:
482 # or if the payment with bt_id 44 is used to pay invoices with ids 50, 51 and 52
484 # '44' => [ '50', '51', 52' ]
487 $::form->{invoice_skontos} ||= {}; # hash of arrays containing the payment types, could be empty
489 # a bank_transaction may be assigned to several invoices, i.e. a customer
490 # might pay several open invoices with one transaction
496 if ( $::form->{proposal_ids} ) {
497 foreach (@{ $::form->{proposal_ids} }) {
498 my $bank_transaction_id = $_;
499 my $invoice_ids = $invoice_hash{$_};
500 push @{ $self->problems }, $self->save_single_bank_transaction(
501 bank_transaction_id => $bank_transaction_id,
502 invoice_ids => $invoice_ids,
504 $count += scalar( @{$invoice_ids} );
507 while ( my ($bank_transaction_id, $invoice_ids) = each(%invoice_hash) ) {
508 push @{ $self->problems }, $self->save_single_bank_transaction(
509 bank_transaction_id => $bank_transaction_id,
510 invoice_ids => $invoice_ids,
512 $count += scalar( @{$invoice_ids} );
518 sub action_save_invoices {
520 my $count = $self->save_invoices();
522 flash('ok', t8('#1 invoice(s) saved.', $count));
524 $self->action_list();
527 sub action_save_proposals {
529 if ( $::form->{proposal_ids} ) {
530 my $propcount = scalar(@{ $::form->{proposal_ids} });
531 if ( $propcount > 0 ) {
532 my $count = $self->save_invoices();
534 flash('ok', t8('#1 proposal(s) with #2 invoice(s) saved.', $propcount, $count));
537 $self->action_list();
541 sub is_collective_transaction {
542 my ($self, $bt) = @_;
543 return $bt->transactioncode eq "191";
546 sub save_single_bank_transaction {
547 my ($self, %params) = @_;
551 bank_transaction => SL::DB::Manager::BankTransaction->find_by(id => $params{bank_transaction_id}),
555 if (!$data{bank_transaction}) {
559 message => $::locale->text('The ID #1 is not a valid database ID.', $data{bank_transaction_id}),
566 my $bt_id = $data{bank_transaction_id};
567 my $bank_transaction = $data{bank_transaction};
568 my $sign = $bank_transaction->amount < 0 ? -1 : 1;
569 my $amount_of_transaction = $sign * $bank_transaction->amount;
570 my $payment_received = $bank_transaction->amount > 0;
571 my $payment_sent = $bank_transaction->amount < 0;
573 foreach my $invoice_id (@{ $params{invoice_ids} }) {
574 my $invoice = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
579 message => $::locale->text("The ID #1 is not a valid database ID.", $invoice_id),
583 push @{ $data{invoices} }, $invoice;
586 if ( $payment_received
587 && any { ( $_->is_sales && ($_->amount < 0))
588 || (!$_->is_sales && ($_->amount > 0))
589 } @{ $data{invoices} }) {
593 message => $::locale->text("Received payments can only be posted for sales invoices and purchase credit notes."),
598 && any { ( $_->is_sales && ($_->amount > 0))
599 || (!$_->is_sales && ($_->amount < 0))
600 } @{ $data{invoices} }) {
604 message => $::locale->text("Sent payments can only be posted for purchase invoices and sales credit notes."),
608 my $max_invoices = scalar(@{ $data{invoices} });
611 foreach my $invoice (@{ $data{invoices} }) {
615 # Check if bank_transaction already has a link to the invoice, may only be linked once per invoice
616 # This might be caused by the user reloading a page and resending the form
617 if (_existing_record_link($bank_transaction, $invoice)) {
621 message => $::locale->text("Bank transaction with id #1 has already been linked to #2.", $bank_transaction->id, $invoice->displayable_name),
625 if (!$amount_of_transaction && $invoice->open_amount) {
629 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."),
634 if ( defined $::form->{invoice_skontos}->{"$bt_id"} ) {
635 $payment_type = shift(@{ $::form->{invoice_skontos}->{"$bt_id"} });
637 $payment_type = 'without_skonto';
640 # pay invoice or go to the next bank transaction if the amount is not sufficiently high
641 if ($invoice->open_amount <= $amount_of_transaction && $n_invoices < $max_invoices) {
642 my $open_amount = ($payment_type eq 'with_skonto_pt'?$invoice->amount_less_skonto:$invoice->open_amount);
643 # first calculate new bank transaction amount ...
644 if ($invoice->is_sales) {
645 $amount_of_transaction -= $sign * $open_amount;
646 $bank_transaction->invoice_amount($bank_transaction->invoice_amount + $open_amount);
648 $amount_of_transaction += $sign * $open_amount;
649 $bank_transaction->invoice_amount($bank_transaction->invoice_amount - $open_amount);
651 # ... and then pay the invoice
652 $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
653 trans_id => $invoice->id,
654 amount => $open_amount,
655 payment_type => $payment_type,
656 transdate => $bank_transaction->transdate->to_kivitendo);
657 } else { # use the whole amount of the bank transaction for the invoice, overpay the invoice if necessary
658 my $overpaid_amount = $amount_of_transaction - $invoice->open_amount;
659 $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
660 trans_id => $invoice->id,
661 amount => $amount_of_transaction,
662 payment_type => $payment_type,
663 transdate => $bank_transaction->transdate->to_kivitendo);
664 $bank_transaction->invoice_amount($bank_transaction->amount);
665 $amount_of_transaction = 0;
667 if ($overpaid_amount >= 0.01) {
671 message => $::locale->text('Invoice #1 was overpaid by #2.', $invoice->invnumber, $::form->format_amount(\%::myconfig, $overpaid_amount, 2)),
675 # Record a record link from the bank transaction to the invoice
677 from_table => 'bank_transactions',
679 to_table => $invoice->is_sales ? 'ar' : 'ap',
680 to_id => $invoice->id,
683 SL::DB::RecordLink->new(@props)->save;
685 # "close" a sepa_export_item if it exists
686 # code duplicated in action_save_proposals!
687 # currently only works, if there is only exactly one open sepa_export_item
688 if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
689 if ( scalar @$seis == 1 ) {
690 # moved the execution and the check for sepa_export into a method,
691 # this isn't part of a transaction, though
692 $seis->[0]->set_executed if $invoice->id == $seis->[0]->arap_id;
697 $bank_transaction->save;
699 # 'undef' means 'no error' here.
704 my $rez = $data{bank_transaction}->db->with_transaction(sub {
706 $error = $worker->();
720 return grep { $_ } ($error, @warnings);
728 $::auth->assert('bank_transaction');
735 sub make_filter_summary {
738 my $filter = $::form->{filter} || {};
742 [ $filter->{"transdate:date::ge"}, $::locale->text('Transdate') . " " . $::locale->text('From Date') ],
743 [ $filter->{"transdate:date::le"}, $::locale->text('Transdate') . " " . $::locale->text('To Date') ],
744 [ $filter->{"valutadate:date::ge"}, $::locale->text('Valutadate') . " " . $::locale->text('From Date') ],
745 [ $filter->{"valutadate:date::le"}, $::locale->text('Valutadate') . " " . $::locale->text('To Date') ],
746 [ $filter->{"amount:number"}, $::locale->text('Amount') ],
747 [ $filter->{"bank_account_id:integer"}, $::locale->text('Local bank account') ],
751 push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
754 $self->{filter_summary} = join ', ', @filter_strings;
760 my $callback = $self->models->get_callback;
762 my $report = SL::ReportGenerator->new(\%::myconfig, $::form);
763 $self->{report} = $report;
765 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);
766 my @sortable = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount purpose local_account_number local_bank_code);
769 transdate => { sub => sub { $_[0]->transdate_as_date } },
770 valutadate => { sub => sub { $_[0]->valutadate_as_date } },
772 remote_account_number => { },
773 remote_bank_code => { },
774 amount => { sub => sub { $_[0]->amount_as_number },
776 invoice_amount => { sub => sub { $_[0]->invoice_amount_as_number },
778 invoices => { sub => sub { $_[0]->linked_invoices } },
779 currency => { sub => sub { $_[0]->currency->name } },
781 local_account_number => { sub => sub { $_[0]->local_bank_account->account_number } },
782 local_bank_code => { sub => sub { $_[0]->local_bank_account->bank_code } },
783 local_bank_name => { sub => sub { $_[0]->local_bank_account->name } },
787 map { $column_defs{$_}->{text} ||= $::locale->text( $self->models->get_sort_spec->{$_}->{title} ) } keys %column_defs;
789 $report->set_options(
790 std_column_visibility => 1,
791 controller_class => 'BankTransaction',
792 output_format => 'HTML',
793 top_info_text => $::locale->text('Bank transactions'),
794 title => $::locale->text('Bank transactions'),
795 allow_pdf_export => 1,
796 allow_csv_export => 1,
798 $report->set_columns(%column_defs);
799 $report->set_column_order(@columns);
800 $report->set_export_options(qw(list_all filter));
801 $report->set_options_from_form;
802 $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
803 $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
805 my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted();
807 $report->set_options(
808 raw_top_info_text => $self->render('bank_transactions/report_top', { output => 0 }, BANK_ACCOUNTS => $bank_accounts),
809 raw_bottom_info_text => $self->render('bank_transactions/report_bottom', { output => 0 }),
813 sub _existing_record_link {
814 my ($bt, $invoice) = @_;
816 # check whether a record link from banktransaction $bt already exists to
817 # invoice $invoice, returns 1 if that is the case
819 die unless $bt->isa("SL::DB::BankTransaction") && ( $invoice->isa("SL::DB::Invoice") || $invoice->isa("SL::DB::PurchaseInvoice") );
821 my $linked_record_to_table = $invoice->is_sales ? 'Invoice' : 'PurchaseInvoice';
822 my $linked_records = $bt->linked_records( direction => 'to', to => $linked_record_to_table, query => [ id => $invoice->id ] );
824 return @$linked_records ? 1 : 0;
827 sub init_problems { [] }
832 SL::Controller::Helper::GetModels->new(
837 dir => 0, # 1 = ASC, 0 = DESC : default sort is newest at top
839 transdate => t8('Transdate'),
840 remote_name => t8('Remote name'),
841 amount => t8('Amount'),
842 invoice_amount => t8('Assigned'),
843 invoices => t8('Linked invoices'),
844 valutadate => t8('Valutadate'),
845 remote_account_number => t8('Remote account number'),
846 remote_bank_code => t8('Remote bank code'),
847 currency => t8('Currency'),
848 purpose => t8('Purpose'),
849 local_account_number => t8('Local account number'),
850 local_bank_code => t8('Local bank code'),
851 local_bank_name => t8('Bank account'),
853 with_objects => [ 'local_bank_account', 'currency' ],
866 SL::Controller::BankTransaction - Posting payments to invoices from
867 bank transactions imported earlier
873 =item C<save_single_bank_transaction %params>
875 Takes a bank transaction ID (as parameter C<bank_transaction_id> and
876 tries to post its amount to a certain number of invoices (parameter
877 C<invoice_ids>, an array ref of database IDs to purchase or sales
880 The whole function is wrapped in a database transaction. If an
881 exception occurs the bank transaction is not posted at all. The same
882 is true if the code detects an error during the execution, e.g. a bank
883 transaction that's already been posted earlier. In both cases the
884 database transaction will be rolled back.
886 If warnings but not errors occur the database transaction is still
889 The return value is an error object or C<undef> if the function
890 succeeded. The calling function will collect all warnings and errors
891 and display them in a nicely formatted table if any occurred.
893 An error object is a hash reference containing the following members:
897 =item * C<result> — can be either C<warning> or C<error>. Warnings are
898 displayed slightly different than errors.
900 =item * C<message> — a human-readable message included in the list of
901 errors meant as the description of why the problem happened
903 =item * C<bank_transaction_id>, C<invoice_ids> — the same parameters
904 that the function was called with
906 =item * C<bank_transaction> — the database object
907 (C<SL::DB::BankTransaction>) corresponding to C<bank_transaction_id>
909 =item * C<invoices> — an array ref of the database objects (either
910 C<SL::DB::Invoice> or C<SL::DB::PurchaseInvoice>) corresponding to
919 Niclas Zimmermann E<lt>niclas@kivitendo-premium.deE<gt>,
920 Geoffrey Richardson E<lt>information@richardson-bueren.deE<gt>