BUGFIX: Kreditorenbuchungen: Fehler beim Nutzen von Drafts
[kivitendo-erp.git] / SL / Controller / BankTransaction.pm
1 package SL::Controller::BankTransaction;
2
3 # idee- möglichkeit bankdaten zu übernehmen in stammdaten
4 # erst Kontenabgleich, um alle gl-Einträge wegzuhaben
5 use strict;
6
7 use parent qw(SL::Controller::Base);
8
9 use SL::Controller::Helper::GetModels;
10 use SL::Controller::Helper::ReportGenerator;
11 use SL::ReportGenerator;
12
13 use SL::DB::BankTransaction;
14 use SL::Helper::Flash;
15 use SL::Locale::String;
16 use SL::SEPA;
17 use SL::DB::Invoice;
18 use SL::DB::PurchaseInvoice;
19 use SL::DB::RecordLink;
20 use SL::JSON;
21 use SL::DB::Chart;
22 use SL::DB::AccTransaction;
23 use SL::DB::Tax;
24 use SL::DB::Draft;
25 use SL::DB::BankAccount;
26 use SL::DB::SepaExportItem;
27 use SL::DBUtils qw(like);
28 use SL::Presenter;
29
30 use List::MoreUtils qw(any);
31 use List::Util qw(max);
32
33 use Rose::Object::MakeMethods::Generic
34 (
35   'scalar --get_set_init' => [ qw(models problems) ],
36 );
37
38 __PACKAGE__->run_before('check_auth');
39
40
41 #
42 # actions
43 #
44
45 sub action_search {
46   my ($self) = @_;
47
48   my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted( query => [ obsolete => 0 ] );
49
50   $self->render('bank_transactions/search',
51                  BANK_ACCOUNTS => $bank_accounts);
52 }
53
54 sub action_list_all {
55   my ($self) = @_;
56
57   $self->make_filter_summary;
58   $self->prepare_report;
59
60   $self->report_generator_list_objects(report => $self->{report}, objects => $self->models->get);
61 }
62
63 sub action_list {
64   my ($self) = @_;
65
66   if (!$::form->{filter}{bank_account}) {
67     flash('error', t8('No bank account chosen!'));
68     $self->action_search;
69     return;
70   }
71
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';
75
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;
79
80   my @where = ();
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 });
89   };
90
91   my $bank_transactions = SL::DB::Manager::BankTransaction->get_all(
92     with_objects => [ 'local_bank_account', 'currency' ],
93     sort_by      => $sort_by,
94     limit        => 10000,
95     where        => [
96       amount                => {ne => \'invoice_amount'},
97       local_bank_account_id => $::form->{filter}{bank_account},
98       @where
99     ],
100   );
101   $main::lxdebug->message(LXDebug->DEBUG2(),"count bt=".scalar(@{$bank_transactions}." bank_account=".$bank_account->id." chart=".$bank_account->chart_id));
102
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}));
108
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 }));
115
116   my @all_sepa_invoices;
117   my @all_non_sepa_invoices;
118   my %sepa_exports;
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         $open_invoice->{realamount}  = $::form->format_amount(\%::myconfig,$open_invoice->amount*$factor,2);
128         $main::lxdebug->message(LXDebug->DEBUG2(),"exitem=".$_->id." for invoice ".$open_invoice->id." factor=".$factor);
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;
141       }
142     }
143     push @all_non_sepa_invoices , $open_invoice if ! $open_invoice->{sepa_export_item};
144   }
145
146   # try to match each bank_transaction with each of the possible open invoices
147   # by awarding points
148   @all_open_invoices = @all_non_sepa_invoices;
149   my @proposals;
150
151   foreach my $bt (@{ $bank_transactions }) {
152     ## 5 Stellen hinter dem Komma auf 2 Stellen reduzieren
153     $bt->amount($bt->amount*1);
154     $bt->invoice_amount($bt->invoice_amount*1);
155     $main::lxdebug->message(LXDebug->DEBUG2(),"BT ".$bt->id." amount=".$bt->amount." invoice_amount=".$bt->invoice_amount." remote=". $bt->{remote_name});
156
157     $bt->{proposals} = [];
158
159     $bt->{remote_name} .= $bt->{remote_name_1} if $bt->{remote_name_1};
160
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->transactioncode eq '191' && ($sepa_exports{$_}->{amount} * 1) eq ($bt->amount * 1) ) {
165           ## jupp
166           $bt->{proposals} = $sepa_exports{$_}->{invoices} ;
167           $bt->{agreement}    = 20;
168           $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);
172           next;
173         }
174       }
175     }
176     next unless $bt->{remote_name};  # bank has no name, usually fees, use create invoice to assign
177
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 && $_->amount eq ($bt->amount * $factor)) {
188             push ($bt->{proposals},$open_invoice );
189             $bt->{agreement}    = 20;
190             $bt->{rule_matches} = 'sepa_export_item(20)';
191             #$main::lxdebug->message(LXDebug->DEBUG2(),"found invoice");
192             @all_sepa_invoices = grep { $_ != $open_invoice } @all_sepa_invoices;
193             last;
194           }
195         }
196       }
197     }
198
199     # try to match the current $bt to each of the open_invoices, saving the
200     # results of get_agreement_with_invoice in $open_invoice->{agreement} and
201     # $open_invoice->{rule_matches}.
202
203     # The values are overwritten each time a new bt is checked, so at the end
204     # of each bt the likely results are filtered and those values are stored in
205     # the arrays $bt->{proposals} and $bt->{rule_matches}, and the agreement
206     # score is stored in $bt->{agreement}
207
208     foreach my $open_invoice (@all_open_invoices){
209       ($open_invoice->{agreement}, $open_invoice->{rule_matches}) = $bt->get_agreement_with_invoice($open_invoice);
210       $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount*($open_invoice->{is_ar}?1:-1),2);
211        # $main::lxdebug->message(LXDebug->DEBUG2(),"agreement=".$open_invoice->{agreement}." rules matches=".$open_invoice->{rule_matches});
212     };
213
214     my $agreement = 15;
215     my $min_agreement = 3; # suggestions must have at least this score
216
217     my $max_agreement = max map { $_->{agreement} } @all_open_invoices;
218
219     # add open_invoices with highest agreement into array $bt->{proposals}
220     if ( $max_agreement >= $min_agreement ) {
221       $bt->{proposals} = [ grep { $_->{agreement} == $max_agreement } @all_open_invoices ];
222       $bt->{agreement} = $max_agreement; #scalar @{ $bt->{proposals} } ? $agreement + 1 : '';
223
224       # store the rule_matches in a separate array, so they can be displayed in template
225       foreach ( @{ $bt->{proposals} } ) {
226         push(@{$bt->{rule_matches}}, $_->{rule_matches});
227       };
228     };
229   }  # finished one bt
230   # finished all bt
231
232   # separate filter for proposals (second tab, agreement >= 5 and exactly one match)
233   # to qualify as a proposal there has to be
234   # * agreement >= 5  TODO: make threshold configurable in configuration
235   # * there must be only one exact match
236   # * depending on whether sales or purchase the amount has to have the correct sign (so Gutschriften don't work?)
237   my $proposal_threshold = 5;
238   my @otherproposals = grep {
239        ($_->{agreement} >= $proposal_threshold)
240     && (1 == scalar @{ $_->{proposals} })
241     && (@{ $_->{proposals} }[0]->is_sales ? abs(@{ $_->{proposals} }[0]->amount - $_->amount) < 0.01
242                                           : abs(@{ $_->{proposals} }[0]->amount + $_->amount) < 0.01)
243   } @{ $bank_transactions };
244
245   push ( @proposals, @otherproposals);
246
247   # sort bank transaction proposals by quality (score) of proposal
248   $bank_transactions = [ sort { $a->{agreement} <=> $b->{agreement} } @{ $bank_transactions } ] if $::form->{sort_by} eq 'proposal' and $::form->{sort_dir} == 1;
249   $bank_transactions = [ sort { $b->{agreement} <=> $a->{agreement} } @{ $bank_transactions } ] if $::form->{sort_by} eq 'proposal' and $::form->{sort_dir} == 0;
250
251
252   $self->render('bank_transactions/list',
253                 title             => t8('Bank transactions MT940'),
254                 BANK_TRANSACTIONS => $bank_transactions,
255                 PROPOSALS         => \@proposals,
256                 bank_account      => $bank_account,
257                 ui_tab            => scalar(@proposals) > 0?1:0,
258               );
259 }
260
261 sub action_assign_invoice {
262   my ($self) = @_;
263
264   $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
265
266   $self->render('bank_transactions/assign_invoice',
267                 { layout => 0 },
268                 title => t8('Assign invoice'),);
269 }
270
271 sub action_create_invoice {
272   my ($self) = @_;
273   my %myconfig = %main::myconfig;
274
275   $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
276   my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(account_number => $self->{transaction}->{remote_account_number});
277
278   my $use_vendor_filter = $self->{transaction}->{remote_account_number} && $vendor_of_transaction;
279
280   my $drafts = SL::DB::Manager::Draft->get_all(where => [ module => 'ap'] , with_objects => 'employee');
281
282   my @filtered_drafts;
283
284   foreach my $draft ( @{ $drafts } ) {
285     my $draft_as_object = YAML::Load($draft->form);
286     my $vendor = SL::DB::Manager::Vendor->find_by(id => $draft_as_object->{vendor_id});
287     $draft->{vendor} = $vendor->name;
288     $draft->{vendor_id} = $vendor->id;
289     push @filtered_drafts, $draft;
290   }
291
292   #Filter drafts
293   @filtered_drafts = grep { $_->{vendor_id} == $vendor_of_transaction->id } @filtered_drafts if $use_vendor_filter;
294
295   my $all_vendors = SL::DB::Manager::Vendor->get_all();
296   my $callback    = $self->url_for(action                => 'list',
297                                    'filter.bank_account' => $::form->{filter}->{bank_account},
298                                    'filter.todate'       => $::form->{filter}->{todate},
299                                    'filter.fromdate'     => $::form->{filter}->{fromdate});
300
301   $self->render(
302     'bank_transactions/create_invoice',
303     { layout => 0 },
304     title       => t8('Create invoice'),
305     DRAFTS      => \@filtered_drafts,
306     vendor_id   => $use_vendor_filter ? $vendor_of_transaction->id   : undef,
307     vendor_name => $use_vendor_filter ? $vendor_of_transaction->name : undef,
308     ALL_VENDORS => $all_vendors,
309     limit       => $myconfig{vclimit},
310     callback    => $callback,
311   );
312 }
313
314 sub action_ajax_payment_suggestion {
315   my ($self) = @_;
316
317   # based on a BankTransaction ID and a Invoice or PurchaseInvoice ID passed via $::form,
318   # create an HTML blob to be used by the js function add_invoices in templates/webpages/bank_transactions/list.html
319   # and return encoded as JSON
320
321   my $bt      = SL::DB::Manager::BankTransaction->find_by( id => $::form->{bt_id} );
322   my $invoice = SL::DB::Manager::Invoice->find_by( id => $::form->{prop_id} ) || SL::DB::Manager::PurchaseInvoice->find_by( id => $::form->{prop_id} );
323
324   die unless $bt and $invoice;
325
326   my @select_options = $invoice->get_payment_select_options_for_bank_transaction($::form->{bt_id});
327
328   my $html;
329   $html .= SL::Presenter->input_tag('invoice_ids.' . $::form->{bt_id} . '[]', $::form->{prop_id} , type => 'hidden');
330   $html .= SL::Presenter->escape(t8('Invno.')      . ': ' . $invoice->invnumber . ' ');
331   $html .= SL::Presenter->escape(t8('Open amount') . ': ' . $::form->format_amount(\%::myconfig, $invoice->open_amount, 2) . ' ');
332   $html .= SL::Presenter->select_tag('invoice_skontos.' . $::form->{bt_id} . '[]',
333                                      \@select_options,
334                                      value_key => 'payment_type',
335                                      title_key => 'display' )
336     if @select_options;
337   $html .= '<a href=# onclick="delete_invoice(' . $::form->{bt_id} . ',' . $::form->{prop_id} . ');">x</a>';
338   $html = SL::Presenter->html_tag('div', $html, id => $::form->{bt_id} . '.' . $::form->{prop_id});
339
340   $self->render(\ SL::JSON::to_json( { 'html' => $html } ), { layout => 0, type => 'json', process => 0 });
341 };
342
343 sub action_filter_drafts {
344   my ($self) = @_;
345
346   $self->{transaction}      = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
347   my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(account_number => $self->{transaction}->{remote_account_number});
348
349   my $drafts                = SL::DB::Manager::Draft->get_all(with_objects => 'employee');
350
351   my @filtered_drafts;
352
353   foreach my $draft ( @{ $drafts } ) {
354     my $draft_as_object = YAML::Load($draft->form);
355     next unless $draft_as_object->{vendor_id};  # we cannot filter for vendor name, if this is a gl draft
356
357     my $vendor          = SL::DB::Manager::Vendor->find_by(id => $draft_as_object->{vendor_id});
358     $draft->{vendor}    = $vendor->name;
359     $draft->{vendor_id} = $vendor->id;
360
361     push @filtered_drafts, $draft;
362   }
363
364   my $vendor_name = $::form->{vendor};
365   my $vendor_id   = $::form->{vendor_id};
366
367   #Filter drafts
368   @filtered_drafts = grep { $_->{vendor_id} == $vendor_id      } @filtered_drafts if $vendor_id;
369   @filtered_drafts = grep { $_->{vendor}    =~ /$vendor_name/i } @filtered_drafts if $vendor_name;
370
371   my $output  = $self->render(
372     'bank_transactions/filter_drafts',
373     { output => 0 },
374     DRAFTS => \@filtered_drafts,
375   );
376
377   my %result = ( count => 0, html => $output );
378
379   $self->render(\to_json(\%result), { type => 'json', process => 0 });
380 }
381
382 sub action_ajax_add_list {
383   my ($self) = @_;
384
385   my @where_sale     = (amount => { ne => \'paid' });
386   my @where_purchase = (amount => { ne => \'paid' });
387
388   if ($::form->{invnumber}) {
389     push @where_sale,     (invnumber => { ilike => like($::form->{invnumber})});
390     push @where_purchase, (invnumber => { ilike => like($::form->{invnumber})});
391   }
392
393   if ($::form->{amount}) {
394     push @where_sale,     (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
395     push @where_purchase, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
396   }
397
398   if ($::form->{vcnumber}) {
399     push @where_sale,     ('customer.customernumber' => { ilike => like($::form->{vcnumber})});
400     push @where_purchase, ('vendor.vendornumber'     => { ilike => like($::form->{vcnumber})});
401   }
402
403   if ($::form->{vcname}) {
404     push @where_sale,     ('customer.name' => { ilike => like($::form->{vcname})});
405     push @where_purchase, ('vendor.name'   => { ilike => like($::form->{vcname})});
406   }
407
408   if ($::form->{transdatefrom}) {
409     my $fromdate = $::locale->parse_date_to_object($::form->{transdatefrom});
410     if ( ref($fromdate) eq 'DateTime' ) {
411       push @where_sale,     ('transdate' => { ge => $fromdate});
412       push @where_purchase, ('transdate' => { ge => $fromdate});
413     };
414   }
415
416   if ($::form->{transdateto}) {
417     my $todate = $::locale->parse_date_to_object($::form->{transdateto});
418     if ( ref($todate) eq 'DateTime' ) {
419       $todate->add(days => 1);
420       push @where_sale,     ('transdate' => { lt => $todate});
421       push @where_purchase, ('transdate' => { lt => $todate});
422     };
423   }
424
425   my $all_open_ar_invoices = SL::DB::Manager::Invoice        ->get_all(where => \@where_sale,     with_objects => 'customer');
426   my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => \@where_purchase, with_objects => 'vendor');
427
428   my @all_open_invoices = @{ $all_open_ar_invoices };
429   # add ap invoices, filtering out subcent open amounts
430   push @all_open_invoices, grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
431
432   @all_open_invoices = sort { $a->id <=> $b->id } @all_open_invoices;
433
434   my $output  = $self->render(
435     'bank_transactions/add_list',
436     { output => 0 },
437     INVOICES => \@all_open_invoices,
438   );
439
440   my %result = ( count => 0, html => $output );
441
442   $self->render(\to_json(\%result), { type => 'json', process => 0 });
443 }
444
445 sub action_ajax_accept_invoices {
446   my ($self) = @_;
447
448   my @selected_invoices;
449   foreach my $invoice_id (@{ $::form->{invoice_id} || [] }) {
450     my $invoice_object = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
451     push @selected_invoices, $invoice_object;
452   }
453
454   $self->render(
455     'bank_transactions/invoices',
456     { layout => 0 },
457     INVOICES => \@selected_invoices,
458     bt_id    => $::form->{bt_id},
459   );
460 }
461
462 sub save_invoices {
463   my ($self) = @_;
464
465   return 0 if !$::form->{invoice_ids};
466
467   my %invoice_hash = %{ delete $::form->{invoice_ids} };  # each key (the bt line with a bt_id) contains an array of invoice_ids
468
469   # e.g. three partial payments with bt_ids 54, 55 and 56 for invoice with id 74:
470   # $invoice_hash = {
471   #         '55' => [
472   #                 '74'
473   #               ],
474   #         '54' => [
475   #                 '74'
476   #               ],
477   #         '56' => [
478   #                 '74'
479   #               ]
480   #       };
481   #
482   # or if the payment with bt_id 44 is used to pay invoices with ids 50, 51 and 52
483   # $invoice_hash = {
484   #           '44' => [ '50', '51', 52' ]
485   #         };
486
487   $::form->{invoice_skontos} ||= {}; # hash of arrays containing the payment types, could be empty
488
489   # a bank_transaction may be assigned to several invoices, i.e. a customer
490   # might pay several open invoices with one transaction
491
492   $self->problems([]);
493
494   my $count = 0;
495
496   if ( $::form->{proposal_ids} ) {
497     foreach (@{ $::form->{proposal_ids} }) {
498       my  $bank_transaction_id = $_;
499       my  $invoice_ids = $invoice_hash{$_};
500       push @{ $self->problems }, $self->save_single_bank_transaction(
501         bank_transaction_id => $bank_transaction_id,
502         invoice_ids         => $invoice_ids,
503       );
504       $count += scalar( @{$invoice_ids} );
505     }
506   } else {
507     while ( my ($bank_transaction_id, $invoice_ids) = each(%invoice_hash) ) {
508       push @{ $self->problems }, $self->save_single_bank_transaction(
509         bank_transaction_id => $bank_transaction_id,
510         invoice_ids         => $invoice_ids,
511       );
512       $count += scalar( @{$invoice_ids} );
513     }
514   }
515   return $count;
516 }
517
518 sub action_save_invoices {
519   my ($self) = @_;
520   my $count = $self->save_invoices();
521
522   flash('ok', t8('#1 invoice(s) saved.', $count));
523
524   $self->action_list();
525 }
526
527 sub action_save_proposals {
528   my ($self) = @_;
529   if ( $::form->{proposal_ids} ) {
530     my $propcount = scalar(@{ $::form->{proposal_ids} });
531     if ( $propcount > 0 ) {
532       my $count = $self->save_invoices();
533
534       flash('ok', t8('#1 proposal(s) with #2 invoice(s) saved.',  $propcount, $count));
535     }
536   }
537   $self->action_list();
538
539 }
540
541 sub is_collective_transaction {
542   my ($self, $bt) = @_;
543   return $bt->transactioncode eq "191";
544 }
545
546 sub save_single_bank_transaction {
547   my ($self, %params) = @_;
548
549   my %data = (
550     %params,
551     bank_transaction => SL::DB::Manager::BankTransaction->find_by(id => $params{bank_transaction_id}),
552     invoices         => [],
553   );
554
555   if (!$data{bank_transaction}) {
556     return {
557       %data,
558       result => 'error',
559       message => $::locale->text('The ID #1 is not a valid database ID.', $data{bank_transaction_id}),
560     };
561   }
562
563   my (@warnings);
564
565   my $worker = sub {
566     my $bt_id                 = $data{bank_transaction_id};
567     my $bank_transaction      = $data{bank_transaction};
568     my $sign                  = $bank_transaction->amount < 0 ? -1 : 1;
569     my $amount_of_transaction = $sign * $bank_transaction->amount;
570     my $payment_received      = $bank_transaction->amount > 0;
571     my $payment_sent          = $bank_transaction->amount < 0;
572
573     foreach my $invoice_id (@{ $params{invoice_ids} }) {
574       my $invoice = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
575       if (!$invoice) {
576         return {
577           %data,
578           result  => 'error',
579           message => $::locale->text("The ID #1 is not a valid database ID.", $invoice_id),
580         };
581       }
582
583       push @{ $data{invoices} }, $invoice;
584     }
585
586     if (   $payment_received
587         && any {    ( $_->is_sales && ($_->amount < 0))
588                  || (!$_->is_sales && ($_->amount > 0))
589                } @{ $data{invoices} }) {
590       return {
591         %data,
592         result  => 'error',
593         message => $::locale->text("Received payments can only be posted for sales invoices and purchase credit notes."),
594       };
595     }
596
597     if (   $payment_sent
598         && any {    ( $_->is_sales && ($_->amount > 0))
599                  || (!$_->is_sales && ($_->amount < 0))
600                } @{ $data{invoices} }) {
601       return {
602         %data,
603         result  => 'error',
604         message => $::locale->text("Sent payments can only be posted for purchase invoices and sales credit notes."),
605       };
606     }
607
608     my $max_invoices = scalar(@{ $data{invoices} });
609     my $n_invoices   = 0;
610
611     foreach my $invoice (@{ $data{invoices} }) {
612
613       $n_invoices++ ;
614
615       # Check if bank_transaction already has a link to the invoice, may only be linked once per invoice
616       # This might be caused by the user reloading a page and resending the form
617       if (_existing_record_link($bank_transaction, $invoice)) {
618         return {
619           %data,
620           result  => 'error',
621           message => $::locale->text("Bank transaction with id #1 has already been linked to #2.", $bank_transaction->id, $invoice->displayable_name),
622         };
623       }
624
625       if (!$amount_of_transaction && $invoice->open_amount) {
626         return {
627           %data,
628           result  => 'error',
629           message => $::locale->text("A payment can only be posted for multiple invoices if the amount to post is equal to or bigger than the sum of the open amounts of the affected invoices."),
630         };
631       }
632
633       my $payment_type;
634       if ( defined $::form->{invoice_skontos}->{"$bt_id"} ) {
635         $payment_type = shift(@{ $::form->{invoice_skontos}->{"$bt_id"} });
636       } else {
637         $payment_type = 'without_skonto';
638       };
639
640       # pay invoice or go to the next bank transaction if the amount is not sufficiently high
641       if ($invoice->open_amount <= $amount_of_transaction && $n_invoices < $max_invoices) {
642         my $open_amount = ($payment_type eq 'with_skonto_pt'?$invoice->amount_less_skonto:$invoice->open_amount);
643         # first calculate new bank transaction amount ...
644         if ($invoice->is_sales) {
645           $amount_of_transaction -= $sign * $open_amount;
646           $bank_transaction->invoice_amount($bank_transaction->invoice_amount + $open_amount);
647         } else {
648           $amount_of_transaction += $sign * $open_amount;
649           $bank_transaction->invoice_amount($bank_transaction->invoice_amount - $open_amount);
650         }
651         # ... and then pay the invoice
652         $invoice->pay_invoice(chart_id     => $bank_transaction->local_bank_account->chart_id,
653                               trans_id     => $invoice->id,
654                               amount       => $open_amount,
655                               payment_type => $payment_type,
656                               transdate    => $bank_transaction->transdate->to_kivitendo);
657       } else { # use the whole amount of the bank transaction for the invoice, overpay the invoice if necessary
658         my $overpaid_amount = $amount_of_transaction - $invoice->open_amount;
659         $invoice->pay_invoice(chart_id     => $bank_transaction->local_bank_account->chart_id,
660                               trans_id     => $invoice->id,
661                               amount       => $amount_of_transaction,
662                               payment_type => $payment_type,
663                               transdate    => $bank_transaction->transdate->to_kivitendo);
664         $bank_transaction->invoice_amount($bank_transaction->amount);
665         $amount_of_transaction = 0;
666
667         if ($overpaid_amount >= 0.01) {
668           push @warnings, {
669             %data,
670             result  => 'warning',
671             message => $::locale->text('Invoice #1 was overpaid by #2.', $invoice->invnumber, $::form->format_amount(\%::myconfig, $overpaid_amount, 2)),
672           };
673         }
674       }
675
676       # Record a record link from the bank transaction to the invoice
677       my @props = (
678         from_table => 'bank_transactions',
679         from_id    => $bt_id,
680         to_table   => $invoice->is_sales ? 'ar' : 'ap',
681         to_id      => $invoice->id,
682       );
683
684       SL::DB::RecordLink->new(@props)->save;
685
686       # "close" a sepa_export_item if it exists
687       # code duplicated in action_save_proposals!
688       # currently only works, if there is only exactly one open sepa_export_item
689       if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
690         if ( scalar @$seis == 1 ) {
691           # moved the execution and the check for sepa_export into a method,
692           # this isn't part of a transaction, though
693           $seis->[0]->set_executed if $invoice->id == $seis->[0]->arap_id;
694         }
695       }
696
697     }
698     $bank_transaction->save;
699
700     # 'undef' means 'no error' here.
701     return undef;
702   };
703
704   my $error;
705   my $rez = $data{bank_transaction}->db->with_transaction(sub {
706     eval {
707       $error = $worker->();
708       1;
709
710     } or do {
711       $error = {
712         %data,
713         result  => 'error',
714         message => $@,
715       };
716     };
717
718     die if $error;
719   });
720
721   return grep { $_ } ($error, @warnings);
722 }
723
724 #
725 # filters
726 #
727
728 sub check_auth {
729   $::auth->assert('bank_transaction');
730 }
731
732 #
733 # helpers
734 #
735
736 sub make_filter_summary {
737   my ($self) = @_;
738
739   my $filter = $::form->{filter} || {};
740   my @filter_strings;
741
742   my @filters = (
743     [ $filter->{"transdate:date::ge"},      $::locale->text('Transdate')  . " " . $::locale->text('From Date') ],
744     [ $filter->{"transdate:date::le"},      $::locale->text('Transdate')  . " " . $::locale->text('To Date')   ],
745     [ $filter->{"valutadate:date::ge"},     $::locale->text('Valutadate') . " " . $::locale->text('From Date') ],
746     [ $filter->{"valutadate:date::le"},     $::locale->text('Valutadate') . " " . $::locale->text('To Date')   ],
747     [ $filter->{"amount:number"},           $::locale->text('Amount')                                          ],
748     [ $filter->{"bank_account_id:integer"}, $::locale->text('Local bank account')                              ],
749   );
750
751   for (@filters) {
752     push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
753   }
754
755   $self->{filter_summary} = join ', ', @filter_strings;
756 }
757
758 sub prepare_report {
759   my ($self)      = @_;
760
761   my $callback    = $self->models->get_callback;
762
763   my $report      = SL::ReportGenerator->new(\%::myconfig, $::form);
764   $self->{report} = $report;
765
766   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);
767   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
769   my %column_defs = (
770     transdate             => { sub   => sub { $_[0]->transdate_as_date } },
771     valutadate            => { sub   => sub { $_[0]->valutadate_as_date } },
772     remote_name           => { },
773     remote_account_number => { },
774     remote_bank_code      => { },
775     amount                => { sub   => sub { $_[0]->amount_as_number },
776                                align => 'right' },
777     invoice_amount        => { sub   => sub { $_[0]->invoice_amount_as_number },
778                                align => 'right' },
779     invoices              => { sub   => sub { $_[0]->linked_invoices } },
780     currency              => { sub   => sub { $_[0]->currency->name } },
781     purpose               => { },
782     local_account_number  => { sub   => sub { $_[0]->local_bank_account->account_number } },
783     local_bank_code       => { sub   => sub { $_[0]->local_bank_account->bank_code } },
784     local_bank_name       => { sub   => sub { $_[0]->local_bank_account->name } },
785     id                    => {},
786   );
787
788   map { $column_defs{$_}->{text} ||= $::locale->text( $self->models->get_sort_spec->{$_}->{title} ) } keys %column_defs;
789
790   $report->set_options(
791     std_column_visibility => 1,
792     controller_class      => 'BankTransaction',
793     output_format         => 'HTML',
794     top_info_text         => $::locale->text('Bank transactions'),
795     title                 => $::locale->text('Bank transactions'),
796     allow_pdf_export      => 1,
797     allow_csv_export      => 1,
798   );
799   $report->set_columns(%column_defs);
800   $report->set_column_order(@columns);
801   $report->set_export_options(qw(list_all filter));
802   $report->set_options_from_form;
803   $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
804   $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
805
806   my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted();
807
808   $report->set_options(
809     raw_top_info_text     => $self->render('bank_transactions/report_top',    { output => 0 }, BANK_ACCOUNTS => $bank_accounts),
810     raw_bottom_info_text  => $self->render('bank_transactions/report_bottom', { output => 0 }),
811   );
812 }
813
814 sub _existing_record_link {
815   my ($bt, $invoice) = @_;
816
817   # check whether a record link from banktransaction $bt already exists to
818   # invoice $invoice, returns 1 if that is the case
819
820   die unless $bt->isa("SL::DB::BankTransaction") && ( $invoice->isa("SL::DB::Invoice") || $invoice->isa("SL::DB::PurchaseInvoice") );
821
822   my $linked_record_to_table = $invoice->is_sales ? 'Invoice' : 'PurchaseInvoice';
823   my $linked_records = $bt->linked_records( direction => 'to', to => $linked_record_to_table, query => [ id => $invoice->id ]  );
824
825   return @$linked_records ? 1 : 0;
826 };
827
828 sub init_problems { [] }
829
830 sub init_models {
831   my ($self) = @_;
832
833   SL::Controller::Helper::GetModels->new(
834     controller => $self,
835     sorted     => {
836       _default => {
837         by  => 'transdate',
838         dir => 0,   # 1 = ASC, 0 = DESC : default sort is newest at top
839       },
840       transdate             => t8('Transdate'),
841       remote_name           => t8('Remote name'),
842       amount                => t8('Amount'),
843       invoice_amount        => t8('Assigned'),
844       invoices              => t8('Linked invoices'),
845       valutadate            => t8('Valutadate'),
846       remote_account_number => t8('Remote account number'),
847       remote_bank_code      => t8('Remote bank code'),
848       currency              => t8('Currency'),
849       purpose               => t8('Purpose'),
850       local_account_number  => t8('Local account number'),
851       local_bank_code       => t8('Local bank code'),
852       local_bank_name       => t8('Bank account'),
853     },
854     with_objects => [ 'local_bank_account', 'currency' ],
855   );
856 }
857
858 1;
859 __END__
860
861 =pod
862
863 =encoding utf8
864
865 =head1 NAME
866
867 SL::Controller::BankTransaction - Posting payments to invoices from
868 bank transactions imported earlier
869
870 =head1 FUNCTIONS
871
872 =over 4
873
874 =item C<save_single_bank_transaction %params>
875
876 Takes a bank transaction ID (as parameter C<bank_transaction_id> and
877 tries to post its amount to a certain number of invoices (parameter
878 C<invoice_ids>, an array ref of database IDs to purchase or sales
879 invoice objects).
880
881 The whole function is wrapped in a database transaction. If an
882 exception occurs the bank transaction is not posted at all. The same
883 is true if the code detects an error during the execution, e.g. a bank
884 transaction that's already been posted earlier. In both cases the
885 database transaction will be rolled back.
886
887 If warnings but not errors occur the database transaction is still
888 committed.
889
890 The return value is an error object or C<undef> if the function
891 succeeded. The calling function will collect all warnings and errors
892 and display them in a nicely formatted table if any occurred.
893
894 An error object is a hash reference containing the following members:
895
896 =over 2
897
898 =item * C<result> — can be either C<warning> or C<error>. Warnings are
899 displayed slightly different than errors.
900
901 =item * C<message> — a human-readable message included in the list of
902 errors meant as the description of why the problem happened
903
904 =item * C<bank_transaction_id>, C<invoice_ids> — the same parameters
905 that the function was called with
906
907 =item * C<bank_transaction> — the database object
908 (C<SL::DB::BankTransaction>) corresponding to C<bank_transaction_id>
909
910 =item * C<invoices> — an array ref of the database objects (either
911 C<SL::DB::Invoice> or C<SL::DB::PurchaseInvoice>) corresponding to
912 C<invoice_ids>
913
914 =back
915
916 =back
917
918 =head1 AUTHOR
919
920 Niclas Zimmermann E<lt>niclas@kivitendo-premium.deE<gt>,
921 Geoffrey Richardson E<lt>information@richardson-bueren.deE<gt>
922
923 =cut