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->{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 ) {
188 ($open_invoice->{agreement}, $open_invoice->{rule_matches}) = $bt->get_agreement_with_invoice($open_invoice);
189 $open_invoice->{agreement} += 5;
190 $open_invoice->{rule_matches} .= 'sepa_export_item(5) ';
191 $main::lxdebug->message(LXDebug->DEBUG2(),"sepa invoice_id=".$open_invoice->id." agreement=".$open_invoice->{agreement}." rules matches=".$open_invoice->{rule_matches});
192 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount*$factor,2);
198 # try to match the current $bt to each of the open_invoices, saving the
199 # results of get_agreement_with_invoice in $open_invoice->{agreement} and
200 # $open_invoice->{rule_matches}.
202 # The values are overwritten each time a new bt is checked, so at the end
203 # of each bt the likely results are filtered and those values are stored in
204 # the arrays $bt->{proposals} and $bt->{rule_matches}, and the agreement
205 # score is stored in $bt->{agreement}
207 foreach my $open_invoice (@all_non_sepa_invoices){
208 ($open_invoice->{agreement}, $open_invoice->{rule_matches}) = $bt->get_agreement_with_invoice($open_invoice);
209 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount*($open_invoice->{is_ar}?1:-1),2);
210 $main::lxdebug->message(LXDebug->DEBUG2(),"nons invoice_id=".$open_invoice->id." agreement=".$open_invoice->{agreement}." rules matches=".$open_invoice->{rule_matches});
214 my $min_agreement = 3; # suggestions must have at least this score
216 my $max_agreement = max map { $_->{agreement} } @all_open_invoices;
218 # add open_invoices with highest agreement into array $bt->{proposals}
219 if ( $max_agreement >= $min_agreement ) {
220 $bt->{proposals} = [ grep { $_->{agreement} == $max_agreement } @all_open_invoices ];
221 $bt->{agreement} = $max_agreement; #scalar @{ $bt->{proposals} } ? $agreement + 1 : '';
223 # store the rule_matches in a separate array, so they can be displayed in template
224 foreach ( @{ $bt->{proposals} } ) {
225 push(@{$bt->{rule_matches}}, $_->{rule_matches});
231 # separate filter for proposals (second tab, agreement >= 5 and exactly one match)
232 # to qualify as a proposal there has to be
233 # * agreement >= 5 TODO: make threshold configurable in configuration
234 # * there must be only one exact match
235 # * depending on whether sales or purchase the amount has to have the correct sign (so Gutschriften don't work?)
236 my $proposal_threshold = 5;
237 my @otherproposals = grep {
238 ($_->{agreement} >= $proposal_threshold)
239 && (1 == scalar @{ $_->{proposals} })
240 && (@{ $_->{proposals} }[0]->is_sales ? abs(@{ $_->{proposals} }[0]->amount - $_->amount) < 0.01
241 : abs(@{ $_->{proposals} }[0]->amount + $_->amount) < 0.01)
242 } @{ $bank_transactions };
244 push ( @proposals, @otherproposals);
246 # sort bank transaction proposals by quality (score) of proposal
247 $bank_transactions = [ sort { $a->{agreement} <=> $b->{agreement} } @{ $bank_transactions } ] if $::form->{sort_by} eq 'proposal' and $::form->{sort_dir} == 1;
248 $bank_transactions = [ sort { $b->{agreement} <=> $a->{agreement} } @{ $bank_transactions } ] if $::form->{sort_by} eq 'proposal' and $::form->{sort_dir} == 0;
251 $self->render('bank_transactions/list',
252 title => t8('Bank transactions MT940'),
253 BANK_TRANSACTIONS => $bank_transactions,
254 PROPOSALS => \@proposals,
255 bank_account => $bank_account,
256 ui_tab => scalar(@proposals) > 0?1:0,
260 sub action_assign_invoice {
263 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
265 $self->render('bank_transactions/assign_invoice',
267 title => t8('Assign invoice'),);
270 sub action_create_invoice {
272 my %myconfig = %main::myconfig;
274 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
275 my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(account_number => $self->{transaction}->{remote_account_number});
277 my $use_vendor_filter = $self->{transaction}->{remote_account_number} && $vendor_of_transaction;
279 my $drafts = SL::DB::Manager::Draft->get_all(where => [ module => 'ap'] , with_objects => 'employee');
283 foreach my $draft ( @{ $drafts } ) {
284 my $draft_as_object = YAML::Load($draft->form);
285 my $vendor = SL::DB::Manager::Vendor->find_by(id => $draft_as_object->{vendor_id});
286 $draft->{vendor} = $vendor->name;
287 $draft->{vendor_id} = $vendor->id;
288 push @filtered_drafts, $draft;
292 @filtered_drafts = grep { $_->{vendor_id} == $vendor_of_transaction->id } @filtered_drafts if $use_vendor_filter;
294 my $all_vendors = SL::DB::Manager::Vendor->get_all();
295 my $callback = $self->url_for(action => 'list',
296 'filter.bank_account' => $::form->{filter}->{bank_account},
297 'filter.todate' => $::form->{filter}->{todate},
298 'filter.fromdate' => $::form->{filter}->{fromdate});
301 'bank_transactions/create_invoice',
303 title => t8('Create invoice'),
304 DRAFTS => \@filtered_drafts,
305 vendor_id => $use_vendor_filter ? $vendor_of_transaction->id : undef,
306 vendor_name => $use_vendor_filter ? $vendor_of_transaction->name : undef,
307 ALL_VENDORS => $all_vendors,
308 limit => $myconfig{vclimit},
309 callback => $callback,
313 sub action_ajax_payment_suggestion {
316 # based on a BankTransaction ID and a Invoice or PurchaseInvoice ID passed via $::form,
317 # create an HTML blob to be used by the js function add_invoices in templates/webpages/bank_transactions/list.html
318 # and return encoded as JSON
320 my $bt = SL::DB::Manager::BankTransaction->find_by( id => $::form->{bt_id} );
321 my $invoice = SL::DB::Manager::Invoice->find_by( id => $::form->{prop_id} ) || SL::DB::Manager::PurchaseInvoice->find_by( id => $::form->{prop_id} );
323 die unless $bt and $invoice;
325 my @select_options = $invoice->get_payment_select_options_for_bank_transaction($::form->{bt_id});
328 $html .= SL::Presenter->input_tag('invoice_ids.' . $::form->{bt_id} . '[]', $::form->{prop_id} , type => 'hidden');
329 $html .= SL::Presenter->escape(t8('Invno.') . ': ' . $invoice->invnumber . ' ');
330 $html .= SL::Presenter->escape(t8('Open amount') . ': ' . $::form->format_amount(\%::myconfig, $invoice->open_amount, 2) . ' ');
331 $html .= SL::Presenter->select_tag('invoice_skontos.' . $::form->{bt_id} . '[]',
333 value_key => 'payment_type',
334 title_key => 'display' )
336 $html .= '<a href=# onclick="delete_invoice(' . $::form->{bt_id} . ',' . $::form->{prop_id} . ');">x</a>';
337 $html = SL::Presenter->html_tag('div', $html, id => $::form->{bt_id} . '.' . $::form->{prop_id});
339 $self->render(\ SL::JSON::to_json( { 'html' => $html } ), { layout => 0, type => 'json', process => 0 });
342 sub action_filter_drafts {
345 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
346 my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(account_number => $self->{transaction}->{remote_account_number});
348 my $drafts = SL::DB::Manager::Draft->get_all(with_objects => 'employee');
352 foreach my $draft ( @{ $drafts } ) {
353 my $draft_as_object = YAML::Load($draft->form);
354 next unless $draft_as_object->{vendor_id}; # we cannot filter for vendor name, if this is a gl draft
356 my $vendor = SL::DB::Manager::Vendor->find_by(id => $draft_as_object->{vendor_id});
357 $draft->{vendor} = $vendor->name;
358 $draft->{vendor_id} = $vendor->id;
360 push @filtered_drafts, $draft;
363 my $vendor_name = $::form->{vendor};
364 my $vendor_id = $::form->{vendor_id};
367 @filtered_drafts = grep { $_->{vendor_id} == $vendor_id } @filtered_drafts if $vendor_id;
368 @filtered_drafts = grep { $_->{vendor} =~ /$vendor_name/i } @filtered_drafts if $vendor_name;
370 my $output = $self->render(
371 'bank_transactions/filter_drafts',
373 DRAFTS => \@filtered_drafts,
376 my %result = ( count => 0, html => $output );
378 $self->render(\to_json(\%result), { type => 'json', process => 0 });
381 sub action_ajax_add_list {
384 my @where_sale = (amount => { ne => \'paid' });
385 my @where_purchase = (amount => { ne => \'paid' });
387 if ($::form->{invnumber}) {
388 push @where_sale, (invnumber => { ilike => like($::form->{invnumber})});
389 push @where_purchase, (invnumber => { ilike => like($::form->{invnumber})});
392 if ($::form->{amount}) {
393 push @where_sale, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
394 push @where_purchase, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
397 if ($::form->{vcnumber}) {
398 push @where_sale, ('customer.customernumber' => { ilike => like($::form->{vcnumber})});
399 push @where_purchase, ('vendor.vendornumber' => { ilike => like($::form->{vcnumber})});
402 if ($::form->{vcname}) {
403 push @where_sale, ('customer.name' => { ilike => like($::form->{vcname})});
404 push @where_purchase, ('vendor.name' => { ilike => like($::form->{vcname})});
407 if ($::form->{transdatefrom}) {
408 my $fromdate = $::locale->parse_date_to_object($::form->{transdatefrom});
409 if ( ref($fromdate) eq 'DateTime' ) {
410 push @where_sale, ('transdate' => { ge => $fromdate});
411 push @where_purchase, ('transdate' => { ge => $fromdate});
415 if ($::form->{transdateto}) {
416 my $todate = $::locale->parse_date_to_object($::form->{transdateto});
417 if ( ref($todate) eq 'DateTime' ) {
418 $todate->add(days => 1);
419 push @where_sale, ('transdate' => { lt => $todate});
420 push @where_purchase, ('transdate' => { lt => $todate});
424 my $all_open_ar_invoices = SL::DB::Manager::Invoice ->get_all(where => \@where_sale, with_objects => 'customer');
425 my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => \@where_purchase, with_objects => 'vendor');
427 my @all_open_invoices = @{ $all_open_ar_invoices };
428 # add ap invoices, filtering out subcent open amounts
429 push @all_open_invoices, grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
431 @all_open_invoices = sort { $a->id <=> $b->id } @all_open_invoices;
433 my $output = $self->render(
434 'bank_transactions/add_list',
436 INVOICES => \@all_open_invoices,
439 my %result = ( count => 0, html => $output );
441 $self->render(\to_json(\%result), { type => 'json', process => 0 });
444 sub action_ajax_accept_invoices {
447 my @selected_invoices;
448 foreach my $invoice_id (@{ $::form->{invoice_id} || [] }) {
449 my $invoice_object = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
450 push @selected_invoices, $invoice_object;
454 'bank_transactions/invoices',
456 INVOICES => \@selected_invoices,
457 bt_id => $::form->{bt_id},
464 return 0 if !$::form->{invoice_ids};
466 my %invoice_hash = %{ delete $::form->{invoice_ids} }; # each key (the bt line with a bt_id) contains an array of invoice_ids
468 # e.g. three partial payments with bt_ids 54, 55 and 56 for invoice with id 74:
481 # or if the payment with bt_id 44 is used to pay invoices with ids 50, 51 and 52
483 # '44' => [ '50', '51', 52' ]
486 $::form->{invoice_skontos} ||= {}; # hash of arrays containing the payment types, could be empty
488 # a bank_transaction may be assigned to several invoices, i.e. a customer
489 # might pay several open invoices with one transaction
495 if ( $::form->{proposal_ids} ) {
496 foreach (@{ $::form->{proposal_ids} }) {
497 my $bank_transaction_id = $_;
498 my $invoice_ids = $invoice_hash{$_};
499 push @{ $self->problems }, $self->save_single_bank_transaction(
500 bank_transaction_id => $bank_transaction_id,
501 invoice_ids => $invoice_ids,
503 $count += scalar( @{$invoice_ids} );
506 while ( my ($bank_transaction_id, $invoice_ids) = each(%invoice_hash) ) {
507 push @{ $self->problems }, $self->save_single_bank_transaction(
508 bank_transaction_id => $bank_transaction_id,
509 invoice_ids => $invoice_ids,
511 $count += scalar( @{$invoice_ids} );
517 sub action_save_invoices {
519 my $count = $self->save_invoices();
521 flash('ok', t8('#1 invoice(s) saved.', $count));
523 $self->action_list();
526 sub action_save_proposals {
528 if ( $::form->{proposal_ids} ) {
529 my $propcount = scalar(@{ $::form->{proposal_ids} });
530 if ( $propcount > 0 ) {
531 my $count = $self->save_invoices();
533 flash('ok', t8('#1 proposal(s) with #2 invoice(s) saved.', $propcount, $count));
536 $self->action_list();
540 sub is_collective_transaction {
541 my ($self, $bt) = @_;
542 return $bt->transaction_code eq "191";
545 sub save_single_bank_transaction {
546 my ($self, %params) = @_;
550 bank_transaction => SL::DB::Manager::BankTransaction->find_by(id => $params{bank_transaction_id}),
554 if (!$data{bank_transaction}) {
558 message => $::locale->text('The ID #1 is not a valid database ID.', $data{bank_transaction_id}),
565 my $bt_id = $data{bank_transaction_id};
566 my $bank_transaction = $data{bank_transaction};
567 my $sign = $bank_transaction->amount < 0 ? -1 : 1;
568 my $amount_of_transaction = $sign * $bank_transaction->amount;
569 my $payment_received = $bank_transaction->amount > 0;
570 my $payment_sent = $bank_transaction->amount < 0;
572 foreach my $invoice_id (@{ $params{invoice_ids} }) {
573 my $invoice = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
578 message => $::locale->text("The ID #1 is not a valid database ID.", $invoice_id),
582 push @{ $data{invoices} }, $invoice;
585 if ( $payment_received
586 && any { ( $_->is_sales && ($_->amount < 0))
587 || (!$_->is_sales && ($_->amount > 0))
588 } @{ $data{invoices} }) {
592 message => $::locale->text("Received payments can only be posted for sales invoices and purchase credit notes."),
597 && any { ( $_->is_sales && ($_->amount > 0))
598 || (!$_->is_sales && ($_->amount < 0))
599 } @{ $data{invoices} }) {
603 message => $::locale->text("Sent payments can only be posted for purchase invoices and sales credit notes."),
607 my $max_invoices = scalar(@{ $data{invoices} });
610 foreach my $invoice (@{ $data{invoices} }) {
614 # Check if bank_transaction already has a link to the invoice, may only be linked once per invoice
615 # This might be caused by the user reloading a page and resending the form
616 if (_existing_record_link($bank_transaction, $invoice)) {
620 message => $::locale->text("Bank transaction with id #1 has already been linked to #2.", $bank_transaction->id, $invoice->displayable_name),
624 if (!$amount_of_transaction && $invoice->open_amount) {
628 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."),
633 if ( defined $::form->{invoice_skontos}->{"$bt_id"} ) {
634 $payment_type = shift(@{ $::form->{invoice_skontos}->{"$bt_id"} });
636 $payment_type = 'without_skonto';
639 # pay invoice or go to the next bank transaction if the amount is not sufficiently high
640 if ($invoice->open_amount <= $amount_of_transaction && $n_invoices < $max_invoices) {
641 my $open_amount = ($payment_type eq 'with_skonto_pt'?$invoice->amount_less_skonto:$invoice->open_amount);
642 # first calculate new bank transaction amount ...
643 if ($invoice->is_sales) {
644 $amount_of_transaction -= $sign * $open_amount;
645 $bank_transaction->invoice_amount($bank_transaction->invoice_amount + $open_amount);
647 $amount_of_transaction += $sign * $open_amount;
648 $bank_transaction->invoice_amount($bank_transaction->invoice_amount - $open_amount);
650 # ... and then pay the invoice
651 $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
652 trans_id => $invoice->id,
653 amount => $open_amount,
654 payment_type => $payment_type,
655 transdate => $bank_transaction->transdate->to_kivitendo);
656 } else { # use the whole amount of the bank transaction for the invoice, overpay the invoice if necessary
657 my $overpaid_amount = $amount_of_transaction - $invoice->open_amount;
658 $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
659 trans_id => $invoice->id,
660 amount => $amount_of_transaction,
661 payment_type => $payment_type,
662 transdate => $bank_transaction->transdate->to_kivitendo);
663 $bank_transaction->invoice_amount($bank_transaction->amount);
664 $amount_of_transaction = 0;
666 if ($overpaid_amount >= 0.01) {
670 message => $::locale->text('Invoice #1 was overpaid by #2.', $invoice->invnumber, $::form->format_amount(\%::myconfig, $overpaid_amount, 2)),
674 # Record a record link from the bank transaction to the invoice
676 from_table => 'bank_transactions',
678 to_table => $invoice->is_sales ? 'ar' : 'ap',
679 to_id => $invoice->id,
682 SL::DB::RecordLink->new(@props)->save;
684 # "close" a sepa_export_item if it exists
685 # code duplicated in action_save_proposals!
686 # currently only works, if there is only exactly one open sepa_export_item
687 if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
688 if ( scalar @$seis == 1 ) {
689 # moved the execution and the check for sepa_export into a method,
690 # this isn't part of a transaction, though
691 $seis->[0]->set_executed if $invoice->id == $seis->[0]->arap_id;
696 $bank_transaction->save;
698 # 'undef' means 'no error' here.
703 my $rez = $data{bank_transaction}->db->with_transaction(sub {
705 $error = $worker->();
719 return grep { $_ } ($error, @warnings);
727 $::auth->assert('bank_transaction');
734 sub make_filter_summary {
737 my $filter = $::form->{filter} || {};
741 [ $filter->{"transdate:date::ge"}, $::locale->text('Transdate') . " " . $::locale->text('From Date') ],
742 [ $filter->{"transdate:date::le"}, $::locale->text('Transdate') . " " . $::locale->text('To Date') ],
743 [ $filter->{"valutadate:date::ge"}, $::locale->text('Valutadate') . " " . $::locale->text('From Date') ],
744 [ $filter->{"valutadate:date::le"}, $::locale->text('Valutadate') . " " . $::locale->text('To Date') ],
745 [ $filter->{"amount:number"}, $::locale->text('Amount') ],
746 [ $filter->{"bank_account_id:integer"}, $::locale->text('Local bank account') ],
750 push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
753 $self->{filter_summary} = join ', ', @filter_strings;
759 my $callback = $self->models->get_callback;
761 my $report = SL::ReportGenerator->new(\%::myconfig, $::form);
762 $self->{report} = $report;
764 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);
765 my @sortable = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount purpose local_account_number local_bank_code);
768 transdate => { sub => sub { $_[0]->transdate_as_date } },
769 valutadate => { sub => sub { $_[0]->valutadate_as_date } },
771 remote_account_number => { },
772 remote_bank_code => { },
773 amount => { sub => sub { $_[0]->amount_as_number },
775 invoice_amount => { sub => sub { $_[0]->invoice_amount_as_number },
777 invoices => { sub => sub { $_[0]->linked_invoices } },
778 currency => { sub => sub { $_[0]->currency->name } },
780 local_account_number => { sub => sub { $_[0]->local_bank_account->account_number } },
781 local_bank_code => { sub => sub { $_[0]->local_bank_account->bank_code } },
782 local_bank_name => { sub => sub { $_[0]->local_bank_account->name } },
786 map { $column_defs{$_}->{text} ||= $::locale->text( $self->models->get_sort_spec->{$_}->{title} ) } keys %column_defs;
788 $report->set_options(
789 std_column_visibility => 1,
790 controller_class => 'BankTransaction',
791 output_format => 'HTML',
792 top_info_text => $::locale->text('Bank transactions'),
793 title => $::locale->text('Bank transactions'),
794 allow_pdf_export => 1,
795 allow_csv_export => 1,
797 $report->set_columns(%column_defs);
798 $report->set_column_order(@columns);
799 $report->set_export_options(qw(list_all filter));
800 $report->set_options_from_form;
801 $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
802 $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
804 my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted();
806 $report->set_options(
807 raw_top_info_text => $self->render('bank_transactions/report_top', { output => 0 }, BANK_ACCOUNTS => $bank_accounts),
808 raw_bottom_info_text => $self->render('bank_transactions/report_bottom', { output => 0 }),
812 sub _existing_record_link {
813 my ($bt, $invoice) = @_;
815 # check whether a record link from banktransaction $bt already exists to
816 # invoice $invoice, returns 1 if that is the case
818 die unless $bt->isa("SL::DB::BankTransaction") && ( $invoice->isa("SL::DB::Invoice") || $invoice->isa("SL::DB::PurchaseInvoice") );
820 my $linked_record_to_table = $invoice->is_sales ? 'Invoice' : 'PurchaseInvoice';
821 my $linked_records = $bt->linked_records( direction => 'to', to => $linked_record_to_table, query => [ id => $invoice->id ] );
823 return @$linked_records ? 1 : 0;
826 sub init_problems { [] }
831 SL::Controller::Helper::GetModels->new(
836 dir => 0, # 1 = ASC, 0 = DESC : default sort is newest at top
838 transdate => t8('Transdate'),
839 remote_name => t8('Remote name'),
840 amount => t8('Amount'),
841 invoice_amount => t8('Assigned'),
842 invoices => t8('Linked invoices'),
843 valutadate => t8('Valutadate'),
844 remote_account_number => t8('Remote account number'),
845 remote_bank_code => t8('Remote bank code'),
846 currency => t8('Currency'),
847 purpose => t8('Purpose'),
848 local_account_number => t8('Local account number'),
849 local_bank_code => t8('Local bank code'),
850 local_bank_name => t8('Bank account'),
852 with_objects => [ 'local_bank_account', 'currency' ],
865 SL::Controller::BankTransaction - Posting payments to invoices from
866 bank transactions imported earlier
872 =item C<save_single_bank_transaction %params>
874 Takes a bank transaction ID (as parameter C<bank_transaction_id> and
875 tries to post its amount to a certain number of invoices (parameter
876 C<invoice_ids>, an array ref of database IDs to purchase or sales
879 The whole function is wrapped in a database transaction. If an
880 exception occurs the bank transaction is not posted at all. The same
881 is true if the code detects an error during the execution, e.g. a bank
882 transaction that's already been posted earlier. In both cases the
883 database transaction will be rolled back.
885 If warnings but not errors occur the database transaction is still
888 The return value is an error object or C<undef> if the function
889 succeeded. The calling function will collect all warnings and errors
890 and display them in a nicely formatted table if any occurred.
892 An error object is a hash reference containing the following members:
896 =item * C<result> — can be either C<warning> or C<error>. Warnings are
897 displayed slightly different than errors.
899 =item * C<message> — a human-readable message included in the list of
900 errors meant as the description of why the problem happened
902 =item * C<bank_transaction_id>, C<invoice_ids> — the same parameters
903 that the function was called with
905 =item * C<bank_transaction> — the database object
906 (C<SL::DB::BankTransaction>) corresponding to C<bank_transaction_id>
908 =item * C<invoices> — an array ref of the database objects (either
909 C<SL::DB::Invoice> or C<SL::DB::PurchaseInvoice>) corresponding to
918 Niclas Zimmermann E<lt>niclas@kivitendo-premium.deE<gt>,
919 Geoffrey Richardson E<lt>information@richardson-bueren.deE<gt>