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 => { ne => \'paid' }], with_objects => ['customer','payment_terms']);
104 my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => [amount => { ne => \'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 })." non fully paid=".scalar(@all_open_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?1:-1);
127 $main::lxdebug->message(LXDebug->DEBUG2(),"exitem=".$_->id." for invoice ".$open_invoice->id." factor=".$factor);
128 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount*$factor,2);
129 $open_invoice->{sepa_export_item} = $_ ;
130 $open_invoice->{skonto_type} = $_->payment_type;
131 $sepa_exports{$_->sepa_export_id} ||= { count => 0, is_ar => 0, amount => 0, proposed => 0, invoices => [], item => $_ };
132 $sepa_exports{$_->sepa_export_id}->{count}++ ;
133 $sepa_exports{$_->sepa_export_id}->{is_ar}++ if $_->ar_id == $open_invoice->id;
134 $sepa_exports{$_->sepa_export_id}->{amount} += $_->amount * $factor;
135 push @{ $sepa_exports{$_->sepa_export_id}->{invoices} }, $open_invoice;
136 #$main::lxdebug->message(LXDebug->DEBUG2(),"amount for export id ".$_->sepa_export_id." = ".
137 # $sepa_exports{$_->sepa_export_id}->{amount}." count = ".
138 # $sepa_exports{$_->sepa_export_id}->{count}." is_ar = ".
139 # $sepa_exports{$_->sepa_export_id}->{is_ar} );
140 push @all_sepa_invoices , $open_invoice;
143 push @all_non_sepa_invoices , $open_invoice if ! $open_invoice->{sepa_export_item};
146 # try to match each bank_transaction with each of the possible open 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} = [];
157 $bt->{rule_matches} = [];
159 $bt->{remote_name} .= $bt->{remote_name_1} if $bt->{remote_name_1};
161 if ( $self->is_collective_transaction($bt) ) {
162 foreach ( keys %sepa_exports) {
163 #$main::lxdebug->message(LXDebug->DEBUG2(),"Exp ID=".$_." compare sum amount ".($sepa_exports{$_}->{amount} *1) ." == ".($bt->amount * 1));
164 if ( $bt->transaction_code eq '191' && abs(($sepa_exports{$_}->{amount} * 1) - ($bt->amount * 1)) < 0.01 ) {
166 @{$bt->{proposals}} = @{$sepa_exports{$_}->{invoices}};
167 $bt->{agreement} = 20;
168 push(@{$bt->{rule_matches}},'sepa_export_item(20)');
169 $sepa_exports{$_}->{proposed}=1;
170 #$main::lxdebug->message(LXDebug->DEBUG2(),"has ".scalar($bt->{proposals})." invoices");
171 push(@proposals, $bt);
176 next unless $bt->{remote_name}; # bank has no name, usually fees, use create invoice to assign
178 foreach ( @{$all_open_sepa_export_items}) {
179 last if scalar (@all_sepa_invoices) == 0;
180 foreach my $open_invoice (@all_sepa_invoices){
181 if ( $_->ap_id == $open_invoice->id || $_->ar_id == $open_invoice->id ) {
182 #$main::lxdebug->message(LXDebug->DEBUG2(),"exitem2=".$_->id." for invoice ".$open_invoice->id);
183 my $factor = ( $_->ar_id == $open_invoice->id?1:-1);
184 $_->amount($_->amount*1);
185 #$main::lxdebug->message(LXDebug->DEBUG2(),"remote account '".$bt->{remote_account_number}."' bt_amount=".$bt->amount." factor=".$factor);
186 #$main::lxdebug->message(LXDebug->DEBUG2(),"compare with '".$_->vc_iban."' amount=".$_->amount);
187 if ( $bt->{remote_account_number} eq $_->vc_iban && abs(abs($_->amount) - abs($bt->amount)) < 0.01 ) {
189 $iban = $open_invoice->customer->iban if $open_invoice->is_sales;
190 $iban = $open_invoice->vendor->iban if ! $open_invoice->is_sales;
191 if($bt->{remote_account_number} eq $iban && abs(abs($open_invoice->amount) - abs($bt->amount)) < 0.01 ) {
192 ($open_invoice->{agreement}, $open_invoice->{rule_matches}) = $bt->get_agreement_with_invoice($open_invoice);
193 $open_invoice->{agreement} += 5;
194 $open_invoice->{rule_matches} .= 'sepa_export_item(5) ';
195 $main::lxdebug->message(LXDebug->DEBUG2(),"sepa invoice_id=".$open_invoice->id." agreement=".$open_invoice->{agreement}." rules matches=".$open_invoice->{rule_matches});
196 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount*$factor,2);
203 # try to match the current $bt to each of the open_invoices, saving the
204 # results of get_agreement_with_invoice in $open_invoice->{agreement} and
205 # $open_invoice->{rule_matches}.
207 # The values are overwritten each time a new bt is checked, so at the end
208 # of each bt the likely results are filtered and those values are stored in
209 # the arrays $bt->{proposals} and $bt->{rule_matches}, and the agreement
210 # score is stored in $bt->{agreement}
212 foreach my $open_invoice (@all_non_sepa_invoices){
213 ($open_invoice->{agreement}, $open_invoice->{rule_matches}) = $bt->get_agreement_with_invoice($open_invoice);
214 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount*($open_invoice->{is_ar}?1:-1),2);
215 $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;
219 my $min_agreement = 3; # suggestions must have at least this score
221 my $max_agreement = max map { $_->{agreement} } @all_open_invoices;
223 # add open_invoices with highest agreement into array $bt->{proposals}
224 if ( $max_agreement >= $min_agreement ) {
225 $bt->{proposals} = [ grep { $_->{agreement} == $max_agreement } @all_open_invoices ];
226 $bt->{agreement} = $max_agreement; #scalar @{ $bt->{proposals} } ? $agreement + 1 : '';
228 # store the rule_matches in a separate array, so they can be displayed in template
229 foreach ( @{ $bt->{proposals} } ) {
230 push(@{$bt->{rule_matches}}, $_->{rule_matches});
236 # separate filter for proposals (second tab, agreement >= 5 and exactly one match)
237 # to qualify as a proposal there has to be
238 # * agreement >= 5 TODO: make threshold configurable in configuration
239 # * there must be only one exact match
240 # * depending on whether sales or purchase the amount has to have the correct sign (so Gutschriften don't work?)
241 my $proposal_threshold = 5;
242 my @otherproposals = grep {
243 ($_->{agreement} >= $proposal_threshold)
244 && (1 == scalar @{ $_->{proposals} })
245 && (@{ $_->{proposals} }[0]->is_sales ? abs(@{ $_->{proposals} }[0]->amount - $_->amount) < 0.01
246 : abs(@{ $_->{proposals} }[0]->amount + $_->amount) < 0.01)
247 } @{ $bank_transactions };
249 push ( @proposals, @otherproposals);
251 # sort bank transaction proposals by quality (score) of proposal
252 $bank_transactions = [ sort { $a->{agreement} <=> $b->{agreement} } @{ $bank_transactions } ] if $::form->{sort_by} eq 'proposal' and $::form->{sort_dir} == 1;
253 $bank_transactions = [ sort { $b->{agreement} <=> $a->{agreement} } @{ $bank_transactions } ] if $::form->{sort_by} eq 'proposal' and $::form->{sort_dir} == 0;
255 $::request->layout->add_javascripts("kivi.BankTransaction.js");
256 $self->render('bank_transactions/list',
257 title => t8('Bank transactions MT940'),
258 BANK_TRANSACTIONS => $bank_transactions,
259 PROPOSALS => \@proposals,
260 bank_account => $bank_account,
261 ui_tab => scalar(@proposals) > 0?1:0,
265 sub action_assign_invoice {
268 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
270 $self->render('bank_transactions/assign_invoice',
272 title => t8('Assign invoice'),);
275 sub action_create_invoice {
277 my %myconfig = %main::myconfig;
279 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
280 my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(account_number => $self->{transaction}->{remote_account_number});
282 my $use_vendor_filter = $self->{transaction}->{remote_account_number} && $vendor_of_transaction;
284 my $drafts = SL::DB::Manager::Draft->get_all(where => [ module => 'ap'] , with_objects => 'employee');
288 foreach my $draft ( @{ $drafts } ) {
289 my $draft_as_object = YAML::Load($draft->form);
290 my $vendor = SL::DB::Manager::Vendor->find_by(id => $draft_as_object->{vendor_id});
291 $draft->{vendor} = $vendor->name;
292 $draft->{vendor_id} = $vendor->id;
293 push @filtered_drafts, $draft;
297 @filtered_drafts = grep { $_->{vendor_id} == $vendor_of_transaction->id } @filtered_drafts if $use_vendor_filter;
299 my $all_vendors = SL::DB::Manager::Vendor->get_all();
300 my $callback = $self->url_for(action => 'list',
301 'filter.bank_account' => $::form->{filter}->{bank_account},
302 'filter.todate' => $::form->{filter}->{todate},
303 'filter.fromdate' => $::form->{filter}->{fromdate});
306 'bank_transactions/create_invoice',
308 title => t8('Create invoice'),
309 DRAFTS => \@filtered_drafts,
310 vendor_id => $use_vendor_filter ? $vendor_of_transaction->id : undef,
311 vendor_name => $use_vendor_filter ? $vendor_of_transaction->name : undef,
312 ALL_VENDORS => $all_vendors,
313 callback => $callback,
317 sub action_ajax_payment_suggestion {
320 # based on a BankTransaction ID and a Invoice or PurchaseInvoice ID passed via $::form,
321 # create an HTML blob to be used by the js function add_invoices in templates/webpages/bank_transactions/list.html
322 # and return encoded as JSON
324 my $bt = SL::DB::Manager::BankTransaction->find_by( id => $::form->{bt_id} );
325 my $invoice = SL::DB::Manager::Invoice->find_by( id => $::form->{prop_id} ) || SL::DB::Manager::PurchaseInvoice->find_by( id => $::form->{prop_id} );
327 die unless $bt and $invoice;
329 my @select_options = $invoice->get_payment_select_options_for_bank_transaction($::form->{bt_id});
332 $html .= SL::Presenter->input_tag('invoice_ids.' . $::form->{bt_id} . '[]', $::form->{prop_id} , type => 'hidden');
333 $html .= SL::Presenter->escape(t8('Invno.') . ': ' . $invoice->invnumber . ' ');
334 $html .= SL::Presenter->escape(t8('Open amount') . ': ' . $::form->format_amount(\%::myconfig, $invoice->open_amount, 2) . ' ');
335 $html .= SL::Presenter->select_tag('invoice_skontos.' . $::form->{bt_id} . '[]',
337 value_key => 'payment_type',
338 title_key => 'display' )
340 $html .= SL::Presenter->html_tag('a', 'x', href => '#', onclick => "kivi.BankTransaction.delete_invoice(" . $::form->{bt_id} . ',' . $::form->{prop_id} . ")");
341 $html = SL::Presenter->html_tag('div', $html, id => $::form->{bt_id} . '.' . $::form->{prop_id}, 'data-invoice-amount' => $invoice->open_amount * 1);
343 $self->render(\ SL::JSON::to_json( { 'html' => $html } ), { layout => 0, type => 'json', process => 0 });
346 sub action_filter_drafts {
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});
352 my $drafts = SL::DB::Manager::Draft->get_all(with_objects => 'employee');
356 foreach my $draft ( @{ $drafts } ) {
357 my $draft_as_object = YAML::Load($draft->form);
358 next unless $draft_as_object->{vendor_id}; # we cannot filter for vendor name, if this is a gl draft
360 my $vendor = SL::DB::Manager::Vendor->find_by(id => $draft_as_object->{vendor_id});
361 $draft->{vendor} = $vendor->name;
362 $draft->{vendor_id} = $vendor->id;
364 push @filtered_drafts, $draft;
367 my $vendor_name = $::form->{vendor};
368 my $vendor_id = $::form->{vendor_id};
371 @filtered_drafts = grep { $_->{vendor_id} == $vendor_id } @filtered_drafts if $vendor_id;
372 @filtered_drafts = grep { $_->{vendor} =~ /$vendor_name/i } @filtered_drafts if $vendor_name;
374 my $output = $self->render(
375 'bank_transactions/filter_drafts',
377 DRAFTS => \@filtered_drafts,
380 my %result = ( count => 0, html => $output );
382 $self->render(\to_json(\%result), { type => 'json', process => 0 });
385 sub action_ajax_add_list {
388 my @where_sale = (amount => { ne => \'paid' });
389 my @where_purchase = (amount => { ne => \'paid' });
391 if ($::form->{invnumber}) {
392 push @where_sale, (invnumber => { ilike => like($::form->{invnumber})});
393 push @where_purchase, (invnumber => { ilike => like($::form->{invnumber})});
396 if ($::form->{amount}) {
397 push @where_sale, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
398 push @where_purchase, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
401 if ($::form->{vcnumber}) {
402 push @where_sale, ('customer.customernumber' => { ilike => like($::form->{vcnumber})});
403 push @where_purchase, ('vendor.vendornumber' => { ilike => like($::form->{vcnumber})});
406 if ($::form->{vcname}) {
407 push @where_sale, ('customer.name' => { ilike => like($::form->{vcname})});
408 push @where_purchase, ('vendor.name' => { ilike => like($::form->{vcname})});
411 if ($::form->{transdatefrom}) {
412 my $fromdate = $::locale->parse_date_to_object($::form->{transdatefrom});
413 if ( ref($fromdate) eq 'DateTime' ) {
414 push @where_sale, ('transdate' => { ge => $fromdate});
415 push @where_purchase, ('transdate' => { ge => $fromdate});
419 if ($::form->{transdateto}) {
420 my $todate = $::locale->parse_date_to_object($::form->{transdateto});
421 if ( ref($todate) eq 'DateTime' ) {
422 $todate->add(days => 1);
423 push @where_sale, ('transdate' => { lt => $todate});
424 push @where_purchase, ('transdate' => { lt => $todate});
428 my $all_open_ar_invoices = SL::DB::Manager::Invoice ->get_all(where => \@where_sale, with_objects => 'customer');
429 my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => \@where_purchase, with_objects => 'vendor');
431 my @all_open_invoices = @{ $all_open_ar_invoices };
432 # add ap invoices, filtering out subcent open amounts
433 push @all_open_invoices, grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
435 @all_open_invoices = sort { $a->id <=> $b->id } @all_open_invoices;
437 my $output = $self->render(
438 'bank_transactions/add_list',
440 INVOICES => \@all_open_invoices,
443 my %result = ( count => 0, html => $output );
445 $self->render(\to_json(\%result), { type => 'json', process => 0 });
448 sub action_ajax_accept_invoices {
451 my @selected_invoices;
452 foreach my $invoice_id (@{ $::form->{invoice_id} || [] }) {
453 my $invoice_object = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
454 push @selected_invoices, $invoice_object;
458 'bank_transactions/invoices',
460 INVOICES => \@selected_invoices,
461 bt_id => $::form->{bt_id},
468 return 0 if !$::form->{invoice_ids};
470 my %invoice_hash = %{ delete $::form->{invoice_ids} }; # each key (the bt line with a bt_id) contains an array of invoice_ids
472 # e.g. three partial payments with bt_ids 54, 55 and 56 for invoice with id 74:
485 # or if the payment with bt_id 44 is used to pay invoices with ids 50, 51 and 52
487 # '44' => [ '50', '51', 52' ]
490 $::form->{invoice_skontos} ||= {}; # hash of arrays containing the payment types, could be empty
492 # a bank_transaction may be assigned to several invoices, i.e. a customer
493 # might pay several open invoices with one transaction
499 if ( $::form->{proposal_ids} ) {
500 foreach (@{ $::form->{proposal_ids} }) {
501 my $bank_transaction_id = $_;
502 my $invoice_ids = $invoice_hash{$_};
503 push @{ $self->problems }, $self->save_single_bank_transaction(
504 bank_transaction_id => $bank_transaction_id,
505 invoice_ids => $invoice_ids,
507 $count += scalar( @{$invoice_ids} );
510 while ( my ($bank_transaction_id, $invoice_ids) = each(%invoice_hash) ) {
511 push @{ $self->problems }, $self->save_single_bank_transaction(
512 bank_transaction_id => $bank_transaction_id,
513 invoice_ids => $invoice_ids,
515 $count += scalar( @{$invoice_ids} );
521 sub action_save_invoices {
523 my $count = $self->save_invoices();
525 flash('ok', t8('#1 invoice(s) saved.', $count));
527 $self->action_list();
530 sub action_save_proposals {
532 if ( $::form->{proposal_ids} ) {
533 my $propcount = scalar(@{ $::form->{proposal_ids} });
534 if ( $propcount > 0 ) {
535 my $count = $self->save_invoices();
537 flash('ok', t8('#1 proposal(s) with #2 invoice(s) saved.', $propcount, $count));
540 $self->action_list();
544 sub is_collective_transaction {
545 my ($self, $bt) = @_;
546 return $bt->transaction_code eq "191";
549 sub save_single_bank_transaction {
550 my ($self, %params) = @_;
554 bank_transaction => SL::DB::Manager::BankTransaction->find_by(id => $params{bank_transaction_id}),
558 if (!$data{bank_transaction}) {
562 message => $::locale->text('The ID #1 is not a valid database ID.', $data{bank_transaction_id}),
569 my $bt_id = $data{bank_transaction_id};
570 my $bank_transaction = $data{bank_transaction};
571 my $sign = $bank_transaction->amount < 0 ? -1 : 1;
572 my $amount_of_transaction = $sign * $bank_transaction->amount;
573 my $payment_received = $bank_transaction->amount > 0;
574 my $payment_sent = $bank_transaction->amount < 0;
577 foreach my $invoice_id (@{ $params{invoice_ids} }) {
578 my $invoice = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
583 message => $::locale->text("The ID #1 is not a valid database ID.", $invoice_id),
586 push @{ $data{invoices} }, $invoice;
589 if ( $payment_received
590 && any { ( $_->is_sales && ($_->amount < 0))
591 || (!$_->is_sales && ($_->amount > 0))
592 } @{ $data{invoices} }) {
596 message => $::locale->text("Received payments can only be posted for sales invoices and purchase credit notes."),
601 && any { ( $_->is_sales && ($_->amount > 0))
602 || (!$_->is_sales && ($_->amount < 0))
603 } @{ $data{invoices} }) {
607 message => $::locale->text("Sent payments can only be posted for purchase invoices and sales credit notes."),
611 my $max_invoices = scalar(@{ $data{invoices} });
614 foreach my $invoice (@{ $data{invoices} }) {
618 # Check if bank_transaction already has a link to the invoice, may only be linked once per invoice
619 # This might be caused by the user reloading a page and resending the form
620 if (_existing_record_link($bank_transaction, $invoice)) {
624 message => $::locale->text("Bank transaction with id #1 has already been linked to #2.", $bank_transaction->id, $invoice->displayable_name),
628 if (!$amount_of_transaction && $invoice->open_amount) {
632 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."),
637 if ( defined $::form->{invoice_skontos}->{"$bt_id"} ) {
638 $payment_type = shift(@{ $::form->{invoice_skontos}->{"$bt_id"} });
640 $payment_type = 'without_skonto';
644 # pay invoice or go to the next bank transaction if the amount is not sufficiently high
645 if ($invoice->open_amount <= $amount_of_transaction && $n_invoices < $max_invoices) {
646 my $open_amount = ($payment_type eq 'with_skonto_pt'?$invoice->amount_less_skonto:$invoice->open_amount);
647 # first calculate new bank transaction amount ...
648 if ($invoice->is_sales) {
649 $amount_of_transaction -= $sign * $open_amount;
650 $bank_transaction->invoice_amount($bank_transaction->invoice_amount + $open_amount);
652 $amount_of_transaction += $sign * $open_amount;
653 $bank_transaction->invoice_amount($bank_transaction->invoice_amount - $open_amount);
655 # ... and then pay the invoice
656 $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
657 trans_id => $invoice->id,
658 amount => $open_amount,
659 payment_type => $payment_type,
660 transdate => $bank_transaction->transdate->to_kivitendo);
661 } else { # use the whole amount of the bank transaction for the invoice, overpay the invoice if necessary
662 if ( $invoice->is_sales && $invoice->invoice_type eq 'credit_note' ) {
663 $amount_of_transaction *= -1;
665 my $overpaid_amount = $amount_of_transaction - $invoice->open_amount;
666 $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
667 trans_id => $invoice->id,
668 amount => $amount_of_transaction,
669 payment_type => $payment_type,
670 transdate => $bank_transaction->transdate->to_kivitendo);
671 $bank_transaction->invoice_amount($bank_transaction->amount);
672 $amount_of_transaction = 0;
674 if ($overpaid_amount >= 0.01) {
678 message => $::locale->text('Invoice #1 was overpaid by #2.', $invoice->invnumber, $::form->format_amount(\%::myconfig, $overpaid_amount, 2)),
682 # Record a record link from the bank transaction to the invoice
684 from_table => 'bank_transactions',
686 to_table => $invoice->is_sales ? 'ar' : 'ap',
687 to_id => $invoice->id,
690 SL::DB::RecordLink->new(@props)->save;
692 # "close" a sepa_export_item if it exists
693 # code duplicated in action_save_proposals!
694 # currently only works, if there is only exactly one open sepa_export_item
695 if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
696 if ( scalar @$seis == 1 ) {
697 # moved the execution and the check for sepa_export into a method,
698 # this isn't part of a transaction, though
699 $seis->[0]->set_executed if $invoice->id == $seis->[0]->arap_id;
704 $bank_transaction->save;
706 # 'undef' means 'no error' here.
711 my $rez = $data{bank_transaction}->db->with_transaction(sub {
713 $error = $worker->();
727 return grep { $_ } ($error, @warnings);
735 $::auth->assert('bank_transaction');
742 sub make_filter_summary {
745 my $filter = $::form->{filter} || {};
749 [ $filter->{"transdate:date::ge"}, $::locale->text('Transdate') . " " . $::locale->text('From Date') ],
750 [ $filter->{"transdate:date::le"}, $::locale->text('Transdate') . " " . $::locale->text('To Date') ],
751 [ $filter->{"valutadate:date::ge"}, $::locale->text('Valutadate') . " " . $::locale->text('From Date') ],
752 [ $filter->{"valutadate:date::le"}, $::locale->text('Valutadate') . " " . $::locale->text('To Date') ],
753 [ $filter->{"amount:number"}, $::locale->text('Amount') ],
754 [ $filter->{"bank_account_id:integer"}, $::locale->text('Local bank account') ],
758 push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
761 $self->{filter_summary} = join ', ', @filter_strings;
767 my $callback = $self->models->get_callback;
769 my $report = SL::ReportGenerator->new(\%::myconfig, $::form);
770 $self->{report} = $report;
772 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);
773 my @sortable = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount purpose local_account_number local_bank_code);
776 transdate => { sub => sub { $_[0]->transdate_as_date } },
777 valutadate => { sub => sub { $_[0]->valutadate_as_date } },
779 remote_account_number => { },
780 remote_bank_code => { },
781 amount => { sub => sub { $_[0]->amount_as_number },
783 invoice_amount => { sub => sub { $_[0]->invoice_amount_as_number },
785 invoices => { sub => sub { $_[0]->linked_invoices } },
786 currency => { sub => sub { $_[0]->currency->name } },
788 local_account_number => { sub => sub { $_[0]->local_bank_account->account_number } },
789 local_bank_code => { sub => sub { $_[0]->local_bank_account->bank_code } },
790 local_bank_name => { sub => sub { $_[0]->local_bank_account->name } },
794 map { $column_defs{$_}->{text} ||= $::locale->text( $self->models->get_sort_spec->{$_}->{title} ) } keys %column_defs;
796 $report->set_options(
797 std_column_visibility => 1,
798 controller_class => 'BankTransaction',
799 output_format => 'HTML',
800 top_info_text => $::locale->text('Bank transactions'),
801 title => $::locale->text('Bank transactions'),
802 allow_pdf_export => 1,
803 allow_csv_export => 1,
805 $report->set_columns(%column_defs);
806 $report->set_column_order(@columns);
807 $report->set_export_options(qw(list_all filter));
808 $report->set_options_from_form;
809 $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
810 $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
812 my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted();
814 $report->set_options(
815 raw_top_info_text => $self->render('bank_transactions/report_top', { output => 0 }, BANK_ACCOUNTS => $bank_accounts),
816 raw_bottom_info_text => $self->render('bank_transactions/report_bottom', { output => 0 }),
820 sub _existing_record_link {
821 my ($bt, $invoice) = @_;
823 # check whether a record link from banktransaction $bt already exists to
824 # invoice $invoice, returns 1 if that is the case
826 die unless $bt->isa("SL::DB::BankTransaction") && ( $invoice->isa("SL::DB::Invoice") || $invoice->isa("SL::DB::PurchaseInvoice") );
828 my $linked_record_to_table = $invoice->is_sales ? 'Invoice' : 'PurchaseInvoice';
829 my $linked_records = $bt->linked_records( direction => 'to', to => $linked_record_to_table, query => [ id => $invoice->id ] );
831 return @$linked_records ? 1 : 0;
834 sub init_problems { [] }
839 SL::Controller::Helper::GetModels->new(
844 dir => 0, # 1 = ASC, 0 = DESC : default sort is newest at top
846 transdate => t8('Transdate'),
847 remote_name => t8('Remote name'),
848 amount => t8('Amount'),
849 invoice_amount => t8('Assigned'),
850 invoices => t8('Linked invoices'),
851 valutadate => t8('Valutadate'),
852 remote_account_number => t8('Remote account number'),
853 remote_bank_code => t8('Remote bank code'),
854 currency => t8('Currency'),
855 purpose => t8('Purpose'),
856 local_account_number => t8('Local account number'),
857 local_bank_code => t8('Local bank code'),
858 local_bank_name => t8('Bank account'),
860 with_objects => [ 'local_bank_account', 'currency' ],
873 SL::Controller::BankTransaction - Posting payments to invoices from
874 bank transactions imported earlier
880 =item C<save_single_bank_transaction %params>
882 Takes a bank transaction ID (as parameter C<bank_transaction_id> and
883 tries to post its amount to a certain number of invoices (parameter
884 C<invoice_ids>, an array ref of database IDs to purchase or sales
887 The whole function is wrapped in a database transaction. If an
888 exception occurs the bank transaction is not posted at all. The same
889 is true if the code detects an error during the execution, e.g. a bank
890 transaction that's already been posted earlier. In both cases the
891 database transaction will be rolled back.
893 If warnings but not errors occur the database transaction is still
896 The return value is an error object or C<undef> if the function
897 succeeded. The calling function will collect all warnings and errors
898 and display them in a nicely formatted table if any occurred.
900 An error object is a hash reference containing the following members:
904 =item * C<result> — can be either C<warning> or C<error>. Warnings are
905 displayed slightly different than errors.
907 =item * C<message> — a human-readable message included in the list of
908 errors meant as the description of why the problem happened
910 =item * C<bank_transaction_id>, C<invoice_ids> — the same parameters
911 that the function was called with
913 =item * C<bank_transaction> — the database object
914 (C<SL::DB::BankTransaction>) corresponding to C<bank_transaction_id>
916 =item * C<invoices> — an array ref of the database objects (either
917 C<SL::DB::Invoice> or C<SL::DB::PurchaseInvoice>) corresponding to
926 Niclas Zimmermann E<lt>niclas@kivitendo-premium.deE<gt>,
927 Geoffrey Richardson E<lt>information@richardson-bueren.deE<gt>