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?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));
186 #$main::lxdebug->message(LXDebug->DEBUG2(),"compare with '".$_->vc_iban."' amount=".$_->amount);
187 if ( $bt->{remote_account_number} eq $_->vc_iban && abs(( $_->amount *1 ) - ($bt->amount * $factor)) < 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." 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 .= '<a href=# onclick="kivi.BankTransaction.delete_invoice(' . $::form->{bt_id} . ',' . $::form->{prop_id} . ');">x</a>';
341 $html = SL::Presenter->html_tag('div', $html, id => $::form->{bt_id} . '.' . $::form->{prop_id});
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;
576 foreach my $invoice_id (@{ $params{invoice_ids} }) {
577 my $invoice = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
582 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';
643 # pay invoice or go to the next bank transaction if the amount is not sufficiently high
644 if ($invoice->open_amount <= $amount_of_transaction && $n_invoices < $max_invoices) {
645 my $open_amount = ($payment_type eq 'with_skonto_pt'?$invoice->amount_less_skonto:$invoice->open_amount);
646 # first calculate new bank transaction amount ...
647 if ($invoice->is_sales) {
648 $amount_of_transaction -= $sign * $open_amount;
649 $bank_transaction->invoice_amount($bank_transaction->invoice_amount + $open_amount);
651 $amount_of_transaction += $sign * $open_amount;
652 $bank_transaction->invoice_amount($bank_transaction->invoice_amount - $open_amount);
654 # ... and then pay the invoice
655 $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
656 trans_id => $invoice->id,
657 amount => $open_amount,
658 payment_type => $payment_type,
659 transdate => $bank_transaction->transdate->to_kivitendo);
660 } else { # use the whole amount of the bank transaction for the invoice, overpay the invoice if necessary
661 my $overpaid_amount = $amount_of_transaction - $invoice->open_amount;
662 $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
663 trans_id => $invoice->id,
664 amount => $amount_of_transaction,
665 payment_type => $payment_type,
666 transdate => $bank_transaction->transdate->to_kivitendo);
667 $bank_transaction->invoice_amount($bank_transaction->amount);
668 $amount_of_transaction = 0;
670 if ($overpaid_amount >= 0.01) {
674 message => $::locale->text('Invoice #1 was overpaid by #2.', $invoice->invnumber, $::form->format_amount(\%::myconfig, $overpaid_amount, 2)),
678 # Record a record link from the bank transaction to the invoice
680 from_table => 'bank_transactions',
682 to_table => $invoice->is_sales ? 'ar' : 'ap',
683 to_id => $invoice->id,
686 SL::DB::RecordLink->new(@props)->save;
688 # "close" a sepa_export_item if it exists
689 # code duplicated in action_save_proposals!
690 # currently only works, if there is only exactly one open sepa_export_item
691 if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
692 if ( scalar @$seis == 1 ) {
693 # moved the execution and the check for sepa_export into a method,
694 # this isn't part of a transaction, though
695 $seis->[0]->set_executed if $invoice->id == $seis->[0]->arap_id;
700 $bank_transaction->save;
702 # 'undef' means 'no error' here.
707 my $rez = $data{bank_transaction}->db->with_transaction(sub {
709 $error = $worker->();
723 return grep { $_ } ($error, @warnings);
731 $::auth->assert('bank_transaction');
738 sub make_filter_summary {
741 my $filter = $::form->{filter} || {};
745 [ $filter->{"transdate:date::ge"}, $::locale->text('Transdate') . " " . $::locale->text('From Date') ],
746 [ $filter->{"transdate:date::le"}, $::locale->text('Transdate') . " " . $::locale->text('To Date') ],
747 [ $filter->{"valutadate:date::ge"}, $::locale->text('Valutadate') . " " . $::locale->text('From Date') ],
748 [ $filter->{"valutadate:date::le"}, $::locale->text('Valutadate') . " " . $::locale->text('To Date') ],
749 [ $filter->{"amount:number"}, $::locale->text('Amount') ],
750 [ $filter->{"bank_account_id:integer"}, $::locale->text('Local bank account') ],
754 push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
757 $self->{filter_summary} = join ', ', @filter_strings;
763 my $callback = $self->models->get_callback;
765 my $report = SL::ReportGenerator->new(\%::myconfig, $::form);
766 $self->{report} = $report;
768 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);
769 my @sortable = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount purpose local_account_number local_bank_code);
772 transdate => { sub => sub { $_[0]->transdate_as_date } },
773 valutadate => { sub => sub { $_[0]->valutadate_as_date } },
775 remote_account_number => { },
776 remote_bank_code => { },
777 amount => { sub => sub { $_[0]->amount_as_number },
779 invoice_amount => { sub => sub { $_[0]->invoice_amount_as_number },
781 invoices => { sub => sub { $_[0]->linked_invoices } },
782 currency => { sub => sub { $_[0]->currency->name } },
784 local_account_number => { sub => sub { $_[0]->local_bank_account->account_number } },
785 local_bank_code => { sub => sub { $_[0]->local_bank_account->bank_code } },
786 local_bank_name => { sub => sub { $_[0]->local_bank_account->name } },
790 map { $column_defs{$_}->{text} ||= $::locale->text( $self->models->get_sort_spec->{$_}->{title} ) } keys %column_defs;
792 $report->set_options(
793 std_column_visibility => 1,
794 controller_class => 'BankTransaction',
795 output_format => 'HTML',
796 top_info_text => $::locale->text('Bank transactions'),
797 title => $::locale->text('Bank transactions'),
798 allow_pdf_export => 1,
799 allow_csv_export => 1,
801 $report->set_columns(%column_defs);
802 $report->set_column_order(@columns);
803 $report->set_export_options(qw(list_all filter));
804 $report->set_options_from_form;
805 $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
806 $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
808 my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted();
810 $report->set_options(
811 raw_top_info_text => $self->render('bank_transactions/report_top', { output => 0 }, BANK_ACCOUNTS => $bank_accounts),
812 raw_bottom_info_text => $self->render('bank_transactions/report_bottom', { output => 0 }),
816 sub _existing_record_link {
817 my ($bt, $invoice) = @_;
819 # check whether a record link from banktransaction $bt already exists to
820 # invoice $invoice, returns 1 if that is the case
822 die unless $bt->isa("SL::DB::BankTransaction") && ( $invoice->isa("SL::DB::Invoice") || $invoice->isa("SL::DB::PurchaseInvoice") );
824 my $linked_record_to_table = $invoice->is_sales ? 'Invoice' : 'PurchaseInvoice';
825 my $linked_records = $bt->linked_records( direction => 'to', to => $linked_record_to_table, query => [ id => $invoice->id ] );
827 return @$linked_records ? 1 : 0;
830 sub init_problems { [] }
835 SL::Controller::Helper::GetModels->new(
840 dir => 0, # 1 = ASC, 0 = DESC : default sort is newest at top
842 transdate => t8('Transdate'),
843 remote_name => t8('Remote name'),
844 amount => t8('Amount'),
845 invoice_amount => t8('Assigned'),
846 invoices => t8('Linked invoices'),
847 valutadate => t8('Valutadate'),
848 remote_account_number => t8('Remote account number'),
849 remote_bank_code => t8('Remote bank code'),
850 currency => t8('Currency'),
851 purpose => t8('Purpose'),
852 local_account_number => t8('Local account number'),
853 local_bank_code => t8('Local bank code'),
854 local_bank_name => t8('Bank account'),
856 with_objects => [ 'local_bank_account', 'currency' ],
869 SL::Controller::BankTransaction - Posting payments to invoices from
870 bank transactions imported earlier
876 =item C<save_single_bank_transaction %params>
878 Takes a bank transaction ID (as parameter C<bank_transaction_id> and
879 tries to post its amount to a certain number of invoices (parameter
880 C<invoice_ids>, an array ref of database IDs to purchase or sales
883 The whole function is wrapped in a database transaction. If an
884 exception occurs the bank transaction is not posted at all. The same
885 is true if the code detects an error during the execution, e.g. a bank
886 transaction that's already been posted earlier. In both cases the
887 database transaction will be rolled back.
889 If warnings but not errors occur the database transaction is still
892 The return value is an error object or C<undef> if the function
893 succeeded. The calling function will collect all warnings and errors
894 and display them in a nicely formatted table if any occurred.
896 An error object is a hash reference containing the following members:
900 =item * C<result> — can be either C<warning> or C<error>. Warnings are
901 displayed slightly different than errors.
903 =item * C<message> — a human-readable message included in the list of
904 errors meant as the description of why the problem happened
906 =item * C<bank_transaction_id>, C<invoice_ids> — the same parameters
907 that the function was called with
909 =item * C<bank_transaction> — the database object
910 (C<SL::DB::BankTransaction>) corresponding to C<bank_transaction_id>
912 =item * C<invoices> — an array ref of the database objects (either
913 C<SL::DB::Invoice> or C<SL::DB::PurchaseInvoice>) corresponding to
922 Niclas Zimmermann E<lt>niclas@kivitendo-premium.deE<gt>,
923 Geoffrey Richardson E<lt>information@richardson-bueren.deE<gt>