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 callback => $callback,
312 sub action_ajax_payment_suggestion {
315 # based on a BankTransaction ID and a Invoice or PurchaseInvoice ID passed via $::form,
316 # create an HTML blob to be used by the js function add_invoices in templates/webpages/bank_transactions/list.html
317 # and return encoded as JSON
319 my $bt = SL::DB::Manager::BankTransaction->find_by( id => $::form->{bt_id} );
320 my $invoice = SL::DB::Manager::Invoice->find_by( id => $::form->{prop_id} ) || SL::DB::Manager::PurchaseInvoice->find_by( id => $::form->{prop_id} );
322 die unless $bt and $invoice;
324 my @select_options = $invoice->get_payment_select_options_for_bank_transaction($::form->{bt_id});
327 $html .= SL::Presenter->input_tag('invoice_ids.' . $::form->{bt_id} . '[]', $::form->{prop_id} , type => 'hidden');
328 $html .= SL::Presenter->escape(t8('Invno.') . ': ' . $invoice->invnumber . ' ');
329 $html .= SL::Presenter->escape(t8('Open amount') . ': ' . $::form->format_amount(\%::myconfig, $invoice->open_amount, 2) . ' ');
330 $html .= SL::Presenter->select_tag('invoice_skontos.' . $::form->{bt_id} . '[]',
332 value_key => 'payment_type',
333 title_key => 'display' )
335 $html .= '<a href=# onclick="delete_invoice(' . $::form->{bt_id} . ',' . $::form->{prop_id} . ');">x</a>';
336 $html = SL::Presenter->html_tag('div', $html, id => $::form->{bt_id} . '.' . $::form->{prop_id});
338 $self->render(\ SL::JSON::to_json( { 'html' => $html } ), { layout => 0, type => 'json', process => 0 });
341 sub action_filter_drafts {
344 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
345 my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(account_number => $self->{transaction}->{remote_account_number});
347 my $drafts = SL::DB::Manager::Draft->get_all(with_objects => 'employee');
351 foreach my $draft ( @{ $drafts } ) {
352 my $draft_as_object = YAML::Load($draft->form);
353 next unless $draft_as_object->{vendor_id}; # we cannot filter for vendor name, if this is a gl draft
355 my $vendor = SL::DB::Manager::Vendor->find_by(id => $draft_as_object->{vendor_id});
356 $draft->{vendor} = $vendor->name;
357 $draft->{vendor_id} = $vendor->id;
359 push @filtered_drafts, $draft;
362 my $vendor_name = $::form->{vendor};
363 my $vendor_id = $::form->{vendor_id};
366 @filtered_drafts = grep { $_->{vendor_id} == $vendor_id } @filtered_drafts if $vendor_id;
367 @filtered_drafts = grep { $_->{vendor} =~ /$vendor_name/i } @filtered_drafts if $vendor_name;
369 my $output = $self->render(
370 'bank_transactions/filter_drafts',
372 DRAFTS => \@filtered_drafts,
375 my %result = ( count => 0, html => $output );
377 $self->render(\to_json(\%result), { type => 'json', process => 0 });
380 sub action_ajax_add_list {
383 my @where_sale = (amount => { ne => \'paid' });
384 my @where_purchase = (amount => { ne => \'paid' });
386 if ($::form->{invnumber}) {
387 push @where_sale, (invnumber => { ilike => like($::form->{invnumber})});
388 push @where_purchase, (invnumber => { ilike => like($::form->{invnumber})});
391 if ($::form->{amount}) {
392 push @where_sale, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
393 push @where_purchase, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
396 if ($::form->{vcnumber}) {
397 push @where_sale, ('customer.customernumber' => { ilike => like($::form->{vcnumber})});
398 push @where_purchase, ('vendor.vendornumber' => { ilike => like($::form->{vcnumber})});
401 if ($::form->{vcname}) {
402 push @where_sale, ('customer.name' => { ilike => like($::form->{vcname})});
403 push @where_purchase, ('vendor.name' => { ilike => like($::form->{vcname})});
406 if ($::form->{transdatefrom}) {
407 my $fromdate = $::locale->parse_date_to_object($::form->{transdatefrom});
408 if ( ref($fromdate) eq 'DateTime' ) {
409 push @where_sale, ('transdate' => { ge => $fromdate});
410 push @where_purchase, ('transdate' => { ge => $fromdate});
414 if ($::form->{transdateto}) {
415 my $todate = $::locale->parse_date_to_object($::form->{transdateto});
416 if ( ref($todate) eq 'DateTime' ) {
417 $todate->add(days => 1);
418 push @where_sale, ('transdate' => { lt => $todate});
419 push @where_purchase, ('transdate' => { lt => $todate});
423 my $all_open_ar_invoices = SL::DB::Manager::Invoice ->get_all(where => \@where_sale, with_objects => 'customer');
424 my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => \@where_purchase, with_objects => 'vendor');
426 my @all_open_invoices = @{ $all_open_ar_invoices };
427 # add ap invoices, filtering out subcent open amounts
428 push @all_open_invoices, grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
430 @all_open_invoices = sort { $a->id <=> $b->id } @all_open_invoices;
432 my $output = $self->render(
433 'bank_transactions/add_list',
435 INVOICES => \@all_open_invoices,
438 my %result = ( count => 0, html => $output );
440 $self->render(\to_json(\%result), { type => 'json', process => 0 });
443 sub action_ajax_accept_invoices {
446 my @selected_invoices;
447 foreach my $invoice_id (@{ $::form->{invoice_id} || [] }) {
448 my $invoice_object = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
449 push @selected_invoices, $invoice_object;
453 'bank_transactions/invoices',
455 INVOICES => \@selected_invoices,
456 bt_id => $::form->{bt_id},
463 return 0 if !$::form->{invoice_ids};
465 my %invoice_hash = %{ delete $::form->{invoice_ids} }; # each key (the bt line with a bt_id) contains an array of invoice_ids
467 # e.g. three partial payments with bt_ids 54, 55 and 56 for invoice with id 74:
480 # or if the payment with bt_id 44 is used to pay invoices with ids 50, 51 and 52
482 # '44' => [ '50', '51', 52' ]
485 $::form->{invoice_skontos} ||= {}; # hash of arrays containing the payment types, could be empty
487 # a bank_transaction may be assigned to several invoices, i.e. a customer
488 # might pay several open invoices with one transaction
494 if ( $::form->{proposal_ids} ) {
495 foreach (@{ $::form->{proposal_ids} }) {
496 my $bank_transaction_id = $_;
497 my $invoice_ids = $invoice_hash{$_};
498 push @{ $self->problems }, $self->save_single_bank_transaction(
499 bank_transaction_id => $bank_transaction_id,
500 invoice_ids => $invoice_ids,
502 $count += scalar( @{$invoice_ids} );
505 while ( my ($bank_transaction_id, $invoice_ids) = each(%invoice_hash) ) {
506 push @{ $self->problems }, $self->save_single_bank_transaction(
507 bank_transaction_id => $bank_transaction_id,
508 invoice_ids => $invoice_ids,
510 $count += scalar( @{$invoice_ids} );
516 sub action_save_invoices {
518 my $count = $self->save_invoices();
520 flash('ok', t8('#1 invoice(s) saved.', $count));
522 $self->action_list();
525 sub action_save_proposals {
527 if ( $::form->{proposal_ids} ) {
528 my $propcount = scalar(@{ $::form->{proposal_ids} });
529 if ( $propcount > 0 ) {
530 my $count = $self->save_invoices();
532 flash('ok', t8('#1 proposal(s) with #2 invoice(s) saved.', $propcount, $count));
535 $self->action_list();
539 sub is_collective_transaction {
540 my ($self, $bt) = @_;
541 return $bt->transaction_code eq "191";
544 sub save_single_bank_transaction {
545 my ($self, %params) = @_;
549 bank_transaction => SL::DB::Manager::BankTransaction->find_by(id => $params{bank_transaction_id}),
553 if (!$data{bank_transaction}) {
557 message => $::locale->text('The ID #1 is not a valid database ID.', $data{bank_transaction_id}),
564 my $bt_id = $data{bank_transaction_id};
565 my $bank_transaction = $data{bank_transaction};
566 my $sign = $bank_transaction->amount < 0 ? -1 : 1;
567 my $amount_of_transaction = $sign * $bank_transaction->amount;
568 my $payment_received = $bank_transaction->amount > 0;
569 my $payment_sent = $bank_transaction->amount < 0;
571 foreach my $invoice_id (@{ $params{invoice_ids} }) {
572 my $invoice = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
577 message => $::locale->text("The ID #1 is not a valid database ID.", $invoice_id),
581 push @{ $data{invoices} }, $invoice;
584 if ( $payment_received
585 && any { ( $_->is_sales && ($_->amount < 0))
586 || (!$_->is_sales && ($_->amount > 0))
587 } @{ $data{invoices} }) {
591 message => $::locale->text("Received payments can only be posted for sales invoices and purchase credit notes."),
596 && any { ( $_->is_sales && ($_->amount > 0))
597 || (!$_->is_sales && ($_->amount < 0))
598 } @{ $data{invoices} }) {
602 message => $::locale->text("Sent payments can only be posted for purchase invoices and sales credit notes."),
606 my $max_invoices = scalar(@{ $data{invoices} });
609 foreach my $invoice (@{ $data{invoices} }) {
613 # Check if bank_transaction already has a link to the invoice, may only be linked once per invoice
614 # This might be caused by the user reloading a page and resending the form
615 if (_existing_record_link($bank_transaction, $invoice)) {
619 message => $::locale->text("Bank transaction with id #1 has already been linked to #2.", $bank_transaction->id, $invoice->displayable_name),
623 if (!$amount_of_transaction && $invoice->open_amount) {
627 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."),
632 if ( defined $::form->{invoice_skontos}->{"$bt_id"} ) {
633 $payment_type = shift(@{ $::form->{invoice_skontos}->{"$bt_id"} });
635 $payment_type = 'without_skonto';
638 # pay invoice or go to the next bank transaction if the amount is not sufficiently high
639 if ($invoice->open_amount <= $amount_of_transaction && $n_invoices < $max_invoices) {
640 my $open_amount = ($payment_type eq 'with_skonto_pt'?$invoice->amount_less_skonto:$invoice->open_amount);
641 # first calculate new bank transaction amount ...
642 if ($invoice->is_sales) {
643 $amount_of_transaction -= $sign * $open_amount;
644 $bank_transaction->invoice_amount($bank_transaction->invoice_amount + $open_amount);
646 $amount_of_transaction += $sign * $open_amount;
647 $bank_transaction->invoice_amount($bank_transaction->invoice_amount - $open_amount);
649 # ... and then pay the invoice
650 $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
651 trans_id => $invoice->id,
652 amount => $open_amount,
653 payment_type => $payment_type,
654 transdate => $bank_transaction->transdate->to_kivitendo);
655 } else { # use the whole amount of the bank transaction for the invoice, overpay the invoice if necessary
656 my $overpaid_amount = $amount_of_transaction - $invoice->open_amount;
657 $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
658 trans_id => $invoice->id,
659 amount => $amount_of_transaction,
660 payment_type => $payment_type,
661 transdate => $bank_transaction->transdate->to_kivitendo);
662 $bank_transaction->invoice_amount($bank_transaction->amount);
663 $amount_of_transaction = 0;
665 if ($overpaid_amount >= 0.01) {
669 message => $::locale->text('Invoice #1 was overpaid by #2.', $invoice->invnumber, $::form->format_amount(\%::myconfig, $overpaid_amount, 2)),
673 # Record a record link from the bank transaction to the invoice
675 from_table => 'bank_transactions',
677 to_table => $invoice->is_sales ? 'ar' : 'ap',
678 to_id => $invoice->id,
681 SL::DB::RecordLink->new(@props)->save;
683 # "close" a sepa_export_item if it exists
684 # code duplicated in action_save_proposals!
685 # currently only works, if there is only exactly one open sepa_export_item
686 if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
687 if ( scalar @$seis == 1 ) {
688 # moved the execution and the check for sepa_export into a method,
689 # this isn't part of a transaction, though
690 $seis->[0]->set_executed if $invoice->id == $seis->[0]->arap_id;
695 $bank_transaction->save;
697 # 'undef' means 'no error' here.
702 my $rez = $data{bank_transaction}->db->with_transaction(sub {
704 $error = $worker->();
718 return grep { $_ } ($error, @warnings);
726 $::auth->assert('bank_transaction');
733 sub make_filter_summary {
736 my $filter = $::form->{filter} || {};
740 [ $filter->{"transdate:date::ge"}, $::locale->text('Transdate') . " " . $::locale->text('From Date') ],
741 [ $filter->{"transdate:date::le"}, $::locale->text('Transdate') . " " . $::locale->text('To Date') ],
742 [ $filter->{"valutadate:date::ge"}, $::locale->text('Valutadate') . " " . $::locale->text('From Date') ],
743 [ $filter->{"valutadate:date::le"}, $::locale->text('Valutadate') . " " . $::locale->text('To Date') ],
744 [ $filter->{"amount:number"}, $::locale->text('Amount') ],
745 [ $filter->{"bank_account_id:integer"}, $::locale->text('Local bank account') ],
749 push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
752 $self->{filter_summary} = join ', ', @filter_strings;
758 my $callback = $self->models->get_callback;
760 my $report = SL::ReportGenerator->new(\%::myconfig, $::form);
761 $self->{report} = $report;
763 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);
764 my @sortable = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount purpose local_account_number local_bank_code);
767 transdate => { sub => sub { $_[0]->transdate_as_date } },
768 valutadate => { sub => sub { $_[0]->valutadate_as_date } },
770 remote_account_number => { },
771 remote_bank_code => { },
772 amount => { sub => sub { $_[0]->amount_as_number },
774 invoice_amount => { sub => sub { $_[0]->invoice_amount_as_number },
776 invoices => { sub => sub { $_[0]->linked_invoices } },
777 currency => { sub => sub { $_[0]->currency->name } },
779 local_account_number => { sub => sub { $_[0]->local_bank_account->account_number } },
780 local_bank_code => { sub => sub { $_[0]->local_bank_account->bank_code } },
781 local_bank_name => { sub => sub { $_[0]->local_bank_account->name } },
785 map { $column_defs{$_}->{text} ||= $::locale->text( $self->models->get_sort_spec->{$_}->{title} ) } keys %column_defs;
787 $report->set_options(
788 std_column_visibility => 1,
789 controller_class => 'BankTransaction',
790 output_format => 'HTML',
791 top_info_text => $::locale->text('Bank transactions'),
792 title => $::locale->text('Bank transactions'),
793 allow_pdf_export => 1,
794 allow_csv_export => 1,
796 $report->set_columns(%column_defs);
797 $report->set_column_order(@columns);
798 $report->set_export_options(qw(list_all filter));
799 $report->set_options_from_form;
800 $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
801 $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
803 my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted();
805 $report->set_options(
806 raw_top_info_text => $self->render('bank_transactions/report_top', { output => 0 }, BANK_ACCOUNTS => $bank_accounts),
807 raw_bottom_info_text => $self->render('bank_transactions/report_bottom', { output => 0 }),
811 sub _existing_record_link {
812 my ($bt, $invoice) = @_;
814 # check whether a record link from banktransaction $bt already exists to
815 # invoice $invoice, returns 1 if that is the case
817 die unless $bt->isa("SL::DB::BankTransaction") && ( $invoice->isa("SL::DB::Invoice") || $invoice->isa("SL::DB::PurchaseInvoice") );
819 my $linked_record_to_table = $invoice->is_sales ? 'Invoice' : 'PurchaseInvoice';
820 my $linked_records = $bt->linked_records( direction => 'to', to => $linked_record_to_table, query => [ id => $invoice->id ] );
822 return @$linked_records ? 1 : 0;
825 sub init_problems { [] }
830 SL::Controller::Helper::GetModels->new(
835 dir => 0, # 1 = ASC, 0 = DESC : default sort is newest at top
837 transdate => t8('Transdate'),
838 remote_name => t8('Remote name'),
839 amount => t8('Amount'),
840 invoice_amount => t8('Assigned'),
841 invoices => t8('Linked invoices'),
842 valutadate => t8('Valutadate'),
843 remote_account_number => t8('Remote account number'),
844 remote_bank_code => t8('Remote bank code'),
845 currency => t8('Currency'),
846 purpose => t8('Purpose'),
847 local_account_number => t8('Local account number'),
848 local_bank_code => t8('Local bank code'),
849 local_bank_name => t8('Bank account'),
851 with_objects => [ 'local_bank_account', 'currency' ],
864 SL::Controller::BankTransaction - Posting payments to invoices from
865 bank transactions imported earlier
871 =item C<save_single_bank_transaction %params>
873 Takes a bank transaction ID (as parameter C<bank_transaction_id> and
874 tries to post its amount to a certain number of invoices (parameter
875 C<invoice_ids>, an array ref of database IDs to purchase or sales
878 The whole function is wrapped in a database transaction. If an
879 exception occurs the bank transaction is not posted at all. The same
880 is true if the code detects an error during the execution, e.g. a bank
881 transaction that's already been posted earlier. In both cases the
882 database transaction will be rolled back.
884 If warnings but not errors occur the database transaction is still
887 The return value is an error object or C<undef> if the function
888 succeeded. The calling function will collect all warnings and errors
889 and display them in a nicely formatted table if any occurred.
891 An error object is a hash reference containing the following members:
895 =item * C<result> — can be either C<warning> or C<error>. Warnings are
896 displayed slightly different than errors.
898 =item * C<message> — a human-readable message included in the list of
899 errors meant as the description of why the problem happened
901 =item * C<bank_transaction_id>, C<invoice_ids> — the same parameters
902 that the function was called with
904 =item * C<bank_transaction> — the database object
905 (C<SL::DB::BankTransaction>) corresponding to C<bank_transaction_id>
907 =item * C<invoices> — an array ref of the database objects (either
908 C<SL::DB::Invoice> or C<SL::DB::PurchaseInvoice>) corresponding to
917 Niclas Zimmermann E<lt>niclas@kivitendo-premium.deE<gt>,
918 Geoffrey Richardson E<lt>information@richardson-bueren.deE<gt>