Form::read_logo gibt es in dieser Version nicht
[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::BankAccount;
25 use SL::DB::RecordTemplate;
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                  => [ qw(callback transaction) ],
36   'scalar --get_set_init' => [ qw(models problems) ],
37 );
38
39 __PACKAGE__->run_before('check_auth');
40
41
42 #
43 # actions
44 #
45
46 sub action_search {
47   my ($self) = @_;
48
49   my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted( query => [ obsolete => 0 ] );
50
51   $self->setup_search_action_bar;
52   $self->render('bank_transactions/search',
53                  BANK_ACCOUNTS => $bank_accounts);
54 }
55
56 sub action_list_all {
57   my ($self) = @_;
58
59   $self->make_filter_summary;
60   $self->prepare_report;
61
62   $self->setup_list_all_action_bar;
63   $self->report_generator_list_objects(report => $self->{report}, objects => $self->models->get);
64 }
65
66 sub action_list {
67   my ($self) = @_;
68
69   if (!$::form->{filter}{bank_account}) {
70     flash('error', t8('No bank account chosen!'));
71     $self->action_search;
72     return;
73   }
74
75   my $sort_by = $::form->{sort_by} || 'transdate';
76   $sort_by = 'transdate' if $sort_by eq 'proposal';
77   $sort_by .= $::form->{sort_dir} ? ' DESC' : ' ASC';
78
79   my $fromdate = $::locale->parse_date_to_object($::form->{filter}->{fromdate});
80   my $todate   = $::locale->parse_date_to_object($::form->{filter}->{todate});
81   $todate->add( days => 1 ) if $todate;
82
83   my @where = ();
84   push @where, (transdate => { ge => $fromdate }) if ($fromdate);
85   push @where, (transdate => { lt => $todate })   if ($todate);
86   my $bank_account = SL::DB::Manager::BankAccount->find_by( id => $::form->{filter}{bank_account} );
87   # bank_transactions no younger than starting date,
88   # including starting date (same search behaviour as fromdate)
89   # but OPEN invoices to be matched may be from before
90   if ( $bank_account->reconciliation_starting_date ) {
91     push @where, (transdate => { ge => $bank_account->reconciliation_starting_date });
92   };
93
94   my $bank_transactions = SL::DB::Manager::BankTransaction->get_all(
95     with_objects => [ 'local_bank_account', 'currency' ],
96     sort_by      => $sort_by,
97     limit        => 10000,
98     where        => [
99       amount                => {ne => \'invoice_amount'},
100       local_bank_account_id => $::form->{filter}{bank_account},
101       @where
102     ],
103   );
104   # credit notes have a negative amount, treat differently
105   my $all_open_ar_invoices = SL::DB::Manager::Invoice        ->get_all(where => [ or => [ amount => { gt => \'paid' },
106                                                                                           and => [ type    => 'credit_note',
107                                                                                                    amount  => { lt => \'paid' }
108                                                                                                  ],
109                                                                                         ],
110                                                                                 ],
111                                                                        with_objects => ['customer','payment_terms']);
112
113   my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => [amount => { ne => \'paid' }], with_objects => ['vendor'  ,'payment_terms']);
114   my $all_open_sepa_export_items = SL::DB::Manager::SepaExportItem->get_all(where => [chart_id => $bank_account->chart_id ,
115                                                                              'sepa_export.executed' => 0, 'sepa_export.closed' => 0 ], with_objects => ['sepa_export']);
116
117   my @all_open_invoices;
118   # filter out invoices with less than 1 cent outstanding
119   push @all_open_invoices, map { $_->{is_ar}=1 ; $_ } grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ar_invoices };
120   push @all_open_invoices, map { $_->{is_ar}=0 ; $_ } grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
121
122   my %sepa_exports;
123   # first collect sepa export items to open invoices
124   foreach my $open_invoice (@all_open_invoices){
125     $open_invoice->{realamount}  = $::form->format_amount(\%::myconfig,$open_invoice->amount,2);
126     $open_invoice->{skonto_type} = 'without_skonto';
127     foreach ( @{$all_open_sepa_export_items}) {
128       if (($_->ap_id && $_->ap_id == $open_invoice->id) || ($_->ar_id && $_->ar_id == $open_invoice->id)) {
129         my $factor                   = ($_->ar_id == $open_invoice->id ? 1 : -1);
130         #$main::lxdebug->message(LXDebug->DEBUG2(),"sepa_exitem=".$_->id." for invoice ".$open_invoice->id." factor=".$factor);
131         $open_invoice->{realamount}  = $::form->format_amount(\%::myconfig,$open_invoice->amount*$factor,2);
132         $open_invoice->{skonto_type} = $_->payment_type;
133         $sepa_exports{$_->sepa_export_id} ||= { count => 0, is_ar => 0, amount => 0, proposed => 0, invoices => [], item => $_ };
134         $sepa_exports{$_->sepa_export_id}->{count}++;
135         $sepa_exports{$_->sepa_export_id}->{is_ar}++ if  $_->ar_id == $open_invoice->id;
136         $sepa_exports{$_->sepa_export_id}->{amount} += $_->amount * $factor;
137         push @{ $sepa_exports{$_->sepa_export_id}->{invoices} }, $open_invoice;
138       }
139     }
140   }
141
142   # try to match each bank_transaction with each of the possible open invoices
143   # by awarding points
144   my @proposals;
145
146   foreach my $bt (@{ $bank_transactions }) {
147     ## 5 Stellen hinter dem Komma auf 2 Stellen reduzieren
148     $bt->amount($bt->amount*1);
149     $bt->invoice_amount($bt->invoice_amount*1);
150
151     $bt->{proposals}    = [];
152     $bt->{rule_matches} = [];
153
154     $bt->{remote_name} .= $bt->{remote_name_1} if $bt->{remote_name_1};
155
156     if ( $bt->is_batch_transaction ) {
157       foreach ( keys  %sepa_exports) {
158         if ( abs(($sepa_exports{$_}->{amount} * 1) - ($bt->amount * 1)) < 0.01 ) {
159           ## jupp
160           @{$bt->{proposals}} = @{$sepa_exports{$_}->{invoices}};
161           $bt->{sepa_export_ok} = 1;
162           $sepa_exports{$_}->{proposed}=1;
163           push(@proposals, $bt);
164           next;
165         }
166       }
167       # batch transaction has no remotename !!
168     } else {
169       next unless $bt->{remote_name};  # bank has no name, usually fees, use create invoice to assign
170     }
171
172     # try to match the current $bt to each of the open_invoices, saving the
173     # results of get_agreement_with_invoice in $open_invoice->{agreement} and
174     # $open_invoice->{rule_matches}.
175
176     # The values are overwritten each time a new bt is checked, so at the end
177     # of each bt the likely results are filtered and those values are stored in
178     # the arrays $bt->{proposals} and $bt->{rule_matches}, and the agreement
179     # score is stored in $bt->{agreement}
180
181     foreach my $open_invoice (@all_open_invoices) {
182       ($open_invoice->{agreement}, $open_invoice->{rule_matches}) = $bt->get_agreement_with_invoice($open_invoice);
183       $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,
184                                       $open_invoice->amount * ($open_invoice->{is_ar} ? 1 : -1), 2);
185     }
186
187     my $agreement = 15;
188     my $min_agreement = 3; # suggestions must have at least this score
189
190     my $max_agreement = max map { $_->{agreement} } @all_open_invoices;
191
192     # add open_invoices with highest agreement into array $bt->{proposals}
193     if ( $max_agreement >= $min_agreement ) {
194       $bt->{proposals} = [ grep { $_->{agreement} == $max_agreement } @all_open_invoices ];
195       $bt->{agreement} = $max_agreement; #scalar @{ $bt->{proposals} } ? $agreement + 1 : '';
196
197       # store the rule_matches in a separate array, so they can be displayed in template
198       foreach ( @{ $bt->{proposals} } ) {
199         push(@{$bt->{rule_matches}}, $_->{rule_matches});
200       };
201     };
202   }  # finished one bt
203   # finished all bt
204
205   # separate filter for proposals (second tab, agreement >= 5 and exactly one match)
206   # to qualify as a proposal there has to be
207   # * agreement >= 5  TODO: make threshold configurable in configuration
208   # * there must be only one exact match
209   # * depending on whether sales or purchase the amount has to have the correct sign (so Gutschriften don't work?)
210   my $proposal_threshold = 5;
211   my @otherproposals = grep {
212        ($_->{agreement} >= $proposal_threshold)
213     && (1 == scalar @{ $_->{proposals} })
214     && (@{ $_->{proposals} }[0]->is_sales ? abs(@{ $_->{proposals} }[0]->amount - $_->amount) < 0.01
215                                           : abs(@{ $_->{proposals} }[0]->amount + $_->amount) < 0.01)
216   } @{ $bank_transactions };
217
218   push @proposals, @otherproposals;
219
220   # sort bank transaction proposals by quality (score) of proposal
221   if ($::form->{sort_by} && $::form->{sort_by} eq 'proposal') {
222     if ($::form->{sort_dir}) {
223       $bank_transactions = [ sort { $a->{agreement} <=> $b->{agreement} } @{ $bank_transactions } ];
224     } else {
225       $bank_transactions = [ sort { $b->{agreement} <=> $a->{agreement} } @{ $bank_transactions } ];
226     }
227   }
228
229   # for testing with t/bank/banktransaction.t :
230   if ( $::form->{dont_render_for_test} ) {
231     return $bank_transactions;
232   }
233
234   $::request->layout->add_javascripts("kivi.BankTransaction.js");
235   $self->render('bank_transactions/list',
236                 title             => t8('Bank transactions MT940'),
237                 BANK_TRANSACTIONS => $bank_transactions,
238                 PROPOSALS         => \@proposals,
239                 bank_account      => $bank_account,
240                 ui_tab            => scalar(@proposals) > 0?1:0,
241               );
242 }
243
244 sub action_assign_invoice {
245   my ($self) = @_;
246
247   $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
248
249   $self->render('bank_transactions/assign_invoice',
250                 { layout => 0 },
251                 title => t8('Assign invoice'),);
252 }
253
254 sub action_create_invoice {
255   my ($self) = @_;
256   my %myconfig = %main::myconfig;
257
258   $self->transaction(SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id}));
259
260   # This was dead code: We compared vendor.account_name with bank_transaction.iban.
261   # This did never match (Kontonummer != IBAN). It's kivis 09/02 (2013) day
262   # If refactored/improved, also consider that vendor.iban should be normalized
263   # user may like to input strings like: 'AT 3333 3333 2222 1111' -> can be checked strictly
264   # at Vendor code because we need the correct data for all sepa exports.
265
266   my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(iban => $self->transaction->{remote_account_number});
267   my $use_vendor_filter     = $self->transaction->{remote_account_number} && $vendor_of_transaction;
268
269   my $templates_ap = SL::DB::Manager::RecordTemplate->get_all(
270     where        => [ template_type => 'ap_transaction' ],
271     with_objects => [ qw(employee vendor) ],
272   );
273   my $templates_gl = SL::DB::Manager::RecordTemplate->get_all(
274     query        => [ template_type => 'gl_transaction',
275                       chart_id      => SL::DB::Manager::BankAccount->find_by(id => $self->transaction->local_bank_account_id)->chart_id,
276                     ],
277     with_objects => [ qw(employee record_template_items) ],
278   );
279
280   # pre filter templates_ap, if we have a vendor match (IBAN eq IBAN) - show and allow user to edit this via gui!
281   $templates_ap = [ grep { $_->vendor_id == $vendor_of_transaction->id } @{ $templates_ap } ] if $use_vendor_filter;
282
283   $self->callback($self->url_for(
284     action                => 'list',
285     'filter.bank_account' => $::form->{filter}->{bank_account},
286     'filter.todate'       => $::form->{filter}->{todate},
287     'filter.fromdate'     => $::form->{filter}->{fromdate},
288   ));
289
290   $self->render(
291     'bank_transactions/create_invoice',
292     { layout => 0 },
293     title        => t8('Create invoice'),
294     TEMPLATES_GL => $use_vendor_filter && @{ $templates_ap } ? undef : $templates_gl,
295     TEMPLATES_AP => $templates_ap,
296     vendor_name  => $use_vendor_filter && @{ $templates_ap } ? $vendor_of_transaction->name : undef,
297   );
298 }
299
300 sub action_ajax_payment_suggestion {
301   my ($self) = @_;
302
303   # based on a BankTransaction ID and a Invoice or PurchaseInvoice ID passed via $::form,
304   # create an HTML blob to be used by the js function add_invoices in templates/webpages/bank_transactions/list.html
305   # and return encoded as JSON
306
307   my $bt      = SL::DB::Manager::BankTransaction->find_by( id => $::form->{bt_id} );
308   my $invoice = SL::DB::Manager::Invoice->find_by( id => $::form->{prop_id} ) || SL::DB::Manager::PurchaseInvoice->find_by( id => $::form->{prop_id} );
309
310   die unless $bt and $invoice;
311
312   my @select_options = $invoice->get_payment_select_options_for_bank_transaction($::form->{bt_id});
313
314   my $html;
315   $html = $self->render(
316     'bank_transactions/_payment_suggestion', { output => 0 },
317     bt_id          => $::form->{bt_id},
318     prop_id        => $::form->{prop_id},
319     invoice        => $invoice,
320     SELECT_OPTIONS => \@select_options,
321   );
322
323   $self->render(\ SL::JSON::to_json( { 'html' => "$html" } ), { layout => 0, type => 'json', process => 0 });
324 };
325
326 sub action_filter_templates {
327   my ($self) = @_;
328
329   $self->{transaction}      = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
330
331   my (@filter, @filter_ap);
332
333   # filter => gl and ap | filter_ap = ap (i.e. vendorname)
334   push @filter,    ('template_name' => { ilike => '%' . $::form->{template} . '%' })  if $::form->{template};
335   push @filter,    ('reference'     => { ilike => '%' . $::form->{reference} . '%' }) if $::form->{reference};
336   push @filter_ap, ('vendor.name'   => { ilike => '%' . $::form->{vendor} . '%' })    if $::form->{vendor};
337   push @filter_ap, @filter;
338   my $templates_gl = SL::DB::Manager::RecordTemplate->get_all(
339     query        => [ template_type => 'gl_transaction',
340                       chart_id      => SL::DB::Manager::BankAccount->find_by(id => $self->transaction->local_bank_account_id)->chart_id,
341                       (and => \@filter) x !!@filter
342                     ],
343     with_objects => [ qw(employee record_template_items) ],
344   );
345
346   my $templates_ap = SL::DB::Manager::RecordTemplate->get_all(
347     where        => [ template_type => 'ap_transaction', (and => \@filter_ap) x !!@filter_ap ],
348     with_objects => [ qw(employee vendor) ],
349   );
350   $::form->{filter} //= {};
351
352   $self->callback($self->url_for(
353     action                => 'list',
354     'filter.bank_account' => $::form->{filter}->{bank_account},
355     'filter.todate'       => $::form->{filter}->{todate},
356     'filter.fromdate'     => $::form->{filter}->{fromdate},
357   ));
358
359   my $output  = $self->render(
360     'bank_transactions/_template_list',
361     { output => 0 },
362     TEMPLATES_AP => $templates_ap,
363     TEMPLATES_GL => $templates_gl,
364   );
365
366   $self->render(\to_json({ html => $output }), { type => 'json', process => 0 });
367 }
368
369 sub action_ajax_add_list {
370   my ($self) = @_;
371
372   my @where_sale     = (amount => { ne => \'paid' });
373   my @where_purchase = (amount => { ne => \'paid' });
374
375   if ($::form->{invnumber}) {
376     push @where_sale,     (invnumber => { ilike => like($::form->{invnumber})});
377     push @where_purchase, (invnumber => { ilike => like($::form->{invnumber})});
378   }
379
380   if ($::form->{amount}) {
381     push @where_sale,     (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
382     push @where_purchase, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
383   }
384
385   if ($::form->{vcnumber}) {
386     push @where_sale,     ('customer.customernumber' => { ilike => like($::form->{vcnumber})});
387     push @where_purchase, ('vendor.vendornumber'     => { ilike => like($::form->{vcnumber})});
388   }
389
390   if ($::form->{vcname}) {
391     push @where_sale,     ('customer.name' => { ilike => like($::form->{vcname})});
392     push @where_purchase, ('vendor.name'   => { ilike => like($::form->{vcname})});
393   }
394
395   if ($::form->{transdatefrom}) {
396     my $fromdate = $::locale->parse_date_to_object($::form->{transdatefrom});
397     if ( ref($fromdate) eq 'DateTime' ) {
398       push @where_sale,     ('transdate' => { ge => $fromdate});
399       push @where_purchase, ('transdate' => { ge => $fromdate});
400     };
401   }
402
403   if ($::form->{transdateto}) {
404     my $todate = $::locale->parse_date_to_object($::form->{transdateto});
405     if ( ref($todate) eq 'DateTime' ) {
406       $todate->add(days => 1);
407       push @where_sale,     ('transdate' => { lt => $todate});
408       push @where_purchase, ('transdate' => { lt => $todate});
409     };
410   }
411
412   my $all_open_ar_invoices = SL::DB::Manager::Invoice        ->get_all(where => \@where_sale,     with_objects => 'customer');
413   my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => \@where_purchase, with_objects => 'vendor');
414
415   my @all_open_invoices = @{ $all_open_ar_invoices };
416   # add ap invoices, filtering out subcent open amounts
417   push @all_open_invoices, grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
418
419   @all_open_invoices = sort { $a->id <=> $b->id } @all_open_invoices;
420
421   my $output  = $self->render(
422     'bank_transactions/add_list',
423     { output => 0 },
424     INVOICES => \@all_open_invoices,
425   );
426
427   my %result = ( count => 0, html => $output );
428
429   $self->render(\to_json(\%result), { type => 'json', process => 0 });
430 }
431
432 sub action_ajax_accept_invoices {
433   my ($self) = @_;
434
435   my @selected_invoices;
436   foreach my $invoice_id (@{ $::form->{invoice_id} || [] }) {
437     my $invoice_object = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
438     push @selected_invoices, $invoice_object;
439   }
440
441   $self->render(
442     'bank_transactions/invoices',
443     { layout => 0 },
444     INVOICES => \@selected_invoices,
445     bt_id    => $::form->{bt_id},
446   );
447 }
448
449 sub save_invoices {
450   my ($self) = @_;
451
452   return 0 if !$::form->{invoice_ids};
453
454   my %invoice_hash = %{ delete $::form->{invoice_ids} };  # each key (the bt line with a bt_id) contains an array of invoice_ids
455
456   # e.g. three partial payments with bt_ids 54, 55 and 56 for invoice with id 74:
457   # $invoice_hash = {
458   #         '55' => [
459   #                 '74'
460   #               ],
461   #         '54' => [
462   #                 '74'
463   #               ],
464   #         '56' => [
465   #                 '74'
466   #               ]
467   #       };
468   #
469   # or if the payment with bt_id 44 is used to pay invoices with ids 50, 51 and 52
470   # $invoice_hash = {
471   #           '44' => [ '50', '51', 52' ]
472   #         };
473
474   $::form->{invoice_skontos} ||= {}; # hash of arrays containing the payment types, could be empty
475
476   # a bank_transaction may be assigned to several invoices, i.e. a customer
477   # might pay several open invoices with one transaction
478
479   $self->problems([]);
480
481   my $count = 0;
482
483   if ( $::form->{proposal_ids} ) {
484     foreach (@{ $::form->{proposal_ids} }) {
485       my  $bank_transaction_id = $_;
486       my  $invoice_ids = $invoice_hash{$_};
487       push @{ $self->problems }, $self->save_single_bank_transaction(
488         bank_transaction_id => $bank_transaction_id,
489         invoice_ids         => $invoice_ids,
490         sources             => ($::form->{sources} // {})->{$_},
491         memos               => ($::form->{memos}   // {})->{$_},
492       );
493       $count += scalar( @{$invoice_ids} );
494     }
495   } else {
496     while ( my ($bank_transaction_id, $invoice_ids) = each(%invoice_hash) ) {
497       push @{ $self->problems }, $self->save_single_bank_transaction(
498         bank_transaction_id => $bank_transaction_id,
499         invoice_ids         => $invoice_ids,
500         sources             => [  map { $::form->{"sources_${bank_transaction_id}_${_}"} } @{ $invoice_ids } ],
501         memos               => [  map { $::form->{"memos_${bank_transaction_id}_${_}"}   } @{ $invoice_ids } ],
502       );
503       $count += scalar( @{$invoice_ids} );
504     }
505   }
506   foreach (@{ $self->problems }) {
507     $count-- if $_->{result} eq 'error';
508   }
509   return $count;
510 }
511
512 sub action_save_invoices {
513   my ($self) = @_;
514   my $count = $self->save_invoices();
515
516   flash('ok', t8('#1 invoice(s) saved.', $count));
517
518   $self->action_list();
519 }
520
521 sub action_save_proposals {
522   my ($self) = @_;
523
524   if ( $::form->{proposal_ids} ) {
525     my $propcount = scalar(@{ $::form->{proposal_ids} });
526     if ( $propcount > 0 ) {
527       my $count = $self->save_invoices();
528
529       flash('ok', t8('#1 proposal(s) with #2 invoice(s) saved.',  $propcount, $count));
530     }
531   }
532   $self->action_list();
533
534 }
535
536 sub save_single_bank_transaction {
537   my ($self, %params) = @_;
538
539   my %data = (
540     %params,
541     bank_transaction => SL::DB::Manager::BankTransaction->find_by(id => $params{bank_transaction_id}),
542     invoices         => [],
543   );
544
545   if (!$data{bank_transaction}) {
546     return {
547       %data,
548       result => 'error',
549       message => $::locale->text('The ID #1 is not a valid database ID.', $data{bank_transaction_id}),
550     };
551   }
552
553   my (@warnings);
554
555   my $worker = sub {
556     my $bt_id                 = $data{bank_transaction_id};
557     my $bank_transaction      = $data{bank_transaction};
558     my $sign                  = $bank_transaction->amount < 0 ? -1 : 1;
559     my $amount_of_transaction = $sign * $bank_transaction->amount;
560     my $payment_received      = $bank_transaction->amount > 0;
561     my $payment_sent          = $bank_transaction->amount < 0;
562
563
564     foreach my $invoice_id (@{ $params{invoice_ids} }) {
565       my $invoice = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
566       if (!$invoice) {
567         return {
568           %data,
569           result  => 'error',
570           message => $::locale->text("The ID #1 is not a valid database ID.", $invoice_id),
571         };
572       }
573       push @{ $data{invoices} }, $invoice;
574     }
575
576     if (   $payment_received
577         && any {    ( $_->is_sales && ($_->amount < 0))
578                  || (!$_->is_sales && ($_->amount > 0))
579                } @{ $data{invoices} }) {
580       return {
581         %data,
582         result  => 'error',
583         message => $::locale->text("Received payments can only be posted for sales invoices and purchase credit notes."),
584       };
585     }
586
587     if (   $payment_sent
588         && any {    ( $_->is_sales && ($_->amount > 0))
589                  || (!$_->is_sales && ($_->amount < 0) && ($_->invoice_type eq 'purchase_invoice'))
590                } @{ $data{invoices} }) {
591       return {
592         %data,
593         result  => 'error',
594         message => $::locale->text("Sent payments can only be posted for purchase invoices and sales credit notes."),
595       };
596     }
597
598     my $max_invoices = scalar(@{ $data{invoices} });
599     my $n_invoices   = 0;
600
601     foreach my $invoice (@{ $data{invoices} }) {
602       my $source = ($data{sources} // [])->[$n_invoices];
603       my $memo   = ($data{memos}   // [])->[$n_invoices];
604
605       $n_invoices++ ;
606
607       # Check if bank_transaction already has a link to the invoice, may only be linked once per invoice
608       # This might be caused by the user reloading a page and resending the form
609       if (_existing_record_link($bank_transaction, $invoice)) {
610         return {
611           %data,
612           result  => 'error',
613           message => $::locale->text("Bank transaction with id #1 has already been linked to #2.", $bank_transaction->id, $invoice->displayable_name),
614         };
615       }
616
617       if (!$amount_of_transaction && $invoice->open_amount) {
618         return {
619           %data,
620           result  => 'error',
621           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."),
622         };
623       }
624
625       my $payment_type;
626       if ( defined $::form->{invoice_skontos}->{"$bt_id"} ) {
627         $payment_type = shift(@{ $::form->{invoice_skontos}->{"$bt_id"} });
628       } else {
629         $payment_type = 'without_skonto';
630       };
631
632
633       # pay invoice or go to the next bank transaction if the amount is not sufficiently high
634       if ($invoice->open_amount <= $amount_of_transaction && $n_invoices < $max_invoices) {
635         my $open_amount = ($payment_type eq 'with_skonto_pt'?$invoice->amount_less_skonto:$invoice->open_amount);
636         # first calculate new bank transaction amount ...
637         if ($invoice->is_sales) {
638           $amount_of_transaction -= $sign * $open_amount;
639           $bank_transaction->invoice_amount($bank_transaction->invoice_amount + $open_amount);
640         } else {
641           $amount_of_transaction += $sign * $open_amount;
642           $bank_transaction->invoice_amount($bank_transaction->invoice_amount - $open_amount);
643         }
644         # ... and then pay the invoice
645         $invoice->pay_invoice(chart_id     => $bank_transaction->local_bank_account->chart_id,
646                               trans_id     => $invoice->id,
647                               amount       => $open_amount,
648                               payment_type => $payment_type,
649                               source       => $source,
650                               memo         => $memo,
651                               transdate    => $bank_transaction->transdate->to_kivitendo);
652       } else {
653         # use the whole amount of the bank transaction for the invoice, overpay the invoice if necessary
654
655         # this catches credit_notes and negative sales invoices
656         if ( $invoice->is_sales && $invoice->amount < 0 ) {
657           # $invoice->open_amount     is negative for credit_notes
658           # $bank_transaction->amount is negative for outgoing transactions
659           # so $amount_of_transaction is negative but needs positive
660           $amount_of_transaction *= -1;
661
662         } elsif (!$invoice->is_sales && $invoice->invoice_type =~ m/ap_transaction|purchase_invoice/) {
663           # $invoice->open_amount may be negative for ap_transaction but may be positiv for negativ ap_transaction
664           # if $invoice->open_amount is negative $bank_transaction->amount is positve
665           # if $invoice->open_amount is positive $bank_transaction->amount is negative
666           # but amount of transaction is for both positive
667           $amount_of_transaction *= -1 if $invoice->open_amount == - $amount_of_transaction;
668         }
669
670         my $overpaid_amount = $amount_of_transaction - $invoice->open_amount;
671         $invoice->pay_invoice(chart_id     => $bank_transaction->local_bank_account->chart_id,
672                               trans_id     => $invoice->id,
673                               amount       => $amount_of_transaction,
674                               payment_type => $payment_type,
675                               source       => $source,
676                               memo         => $memo,
677                               transdate    => $bank_transaction->transdate->to_kivitendo);
678         $bank_transaction->invoice_amount($bank_transaction->amount);
679         $amount_of_transaction = 0;
680
681         if ($overpaid_amount >= 0.01) {
682           push @warnings, {
683             %data,
684             result  => 'warning',
685             message => $::locale->text('Invoice #1 was overpaid by #2.', $invoice->invnumber, $::form->format_amount(\%::myconfig, $overpaid_amount, 2)),
686           };
687         }
688       }
689       # Record a record link from the bank transaction to the invoice
690       my @props = (
691         from_table => 'bank_transactions',
692         from_id    => $bt_id,
693         to_table   => $invoice->is_sales ? 'ar' : 'ap',
694         to_id      => $invoice->id,
695       );
696
697       SL::DB::RecordLink->new(@props)->save;
698
699       # "close" a sepa_export_item if it exists
700       # code duplicated in action_save_proposals!
701       # currently only works, if there is only exactly one open sepa_export_item
702       if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
703         if ( scalar @$seis == 1 ) {
704           # moved the execution and the check for sepa_export into a method,
705           # this isn't part of a transaction, though
706           $seis->[0]->set_executed if $invoice->id == $seis->[0]->arap_id;
707         }
708       }
709
710     }
711     $bank_transaction->save;
712
713     # 'undef' means 'no error' here.
714     return undef;
715   };
716
717   my $error;
718   my $rez = $data{bank_transaction}->db->with_transaction(sub {
719     eval {
720       $error = $worker->();
721       1;
722
723     } or do {
724       $error = {
725         %data,
726         result  => 'error',
727         message => $@,
728       };
729     };
730
731     # Rollback Fehler nicht weiterreichen
732     # die if $error;
733   });
734
735   return grep { $_ } ($error, @warnings);
736 }
737
738 #
739 # filters
740 #
741
742 sub check_auth {
743   $::auth->assert('bank_transaction');
744 }
745
746 #
747 # helpers
748 #
749
750 sub make_filter_summary {
751   my ($self) = @_;
752
753   my $filter = $::form->{filter} || {};
754   my @filter_strings;
755
756   my @filters = (
757     [ $filter->{"transdate:date::ge"},      $::locale->text('Transdate')  . " " . $::locale->text('From Date') ],
758     [ $filter->{"transdate:date::le"},      $::locale->text('Transdate')  . " " . $::locale->text('To Date')   ],
759     [ $filter->{"valutadate:date::ge"},     $::locale->text('Valutadate') . " " . $::locale->text('From Date') ],
760     [ $filter->{"valutadate:date::le"},     $::locale->text('Valutadate') . " " . $::locale->text('To Date')   ],
761     [ $filter->{"amount:number"},           $::locale->text('Amount')                                          ],
762     [ $filter->{"bank_account_id:integer"}, $::locale->text('Local bank account')                              ],
763   );
764
765   for (@filters) {
766     push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
767   }
768
769   $self->{filter_summary} = join ', ', @filter_strings;
770 }
771
772 sub prepare_report {
773   my ($self)      = @_;
774
775   my $callback    = $self->models->get_callback;
776
777   my $report      = SL::ReportGenerator->new(\%::myconfig, $::form);
778   $self->{report} = $report;
779
780   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);
781   my @sortable    = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount                                  purpose local_account_number local_bank_code);
782
783   my %column_defs = (
784     transdate             => { sub   => sub { $_[0]->transdate_as_date } },
785     valutadate            => { sub   => sub { $_[0]->valutadate_as_date } },
786     remote_name           => { },
787     remote_account_number => { },
788     remote_bank_code      => { },
789     amount                => { sub   => sub { $_[0]->amount_as_number },
790                                align => 'right' },
791     invoice_amount        => { sub   => sub { $_[0]->invoice_amount_as_number },
792                                align => 'right' },
793     invoices              => { sub   => sub { $_[0]->linked_invoices } },
794     currency              => { sub   => sub { $_[0]->currency->name } },
795     purpose               => { },
796     local_account_number  => { sub   => sub { $_[0]->local_bank_account->account_number } },
797     local_bank_code       => { sub   => sub { $_[0]->local_bank_account->bank_code } },
798     local_bank_name       => { sub   => sub { $_[0]->local_bank_account->name } },
799     id                    => {},
800   );
801
802   map { $column_defs{$_}->{text} ||= $::locale->text( $self->models->get_sort_spec->{$_}->{title} ) } keys %column_defs;
803
804   $report->set_options(
805     std_column_visibility => 1,
806     controller_class      => 'BankTransaction',
807     output_format         => 'HTML',
808     top_info_text         => $::locale->text('Bank transactions'),
809     title                 => $::locale->text('Bank transactions'),
810     allow_pdf_export      => 1,
811     allow_csv_export      => 1,
812   );
813   $report->set_columns(%column_defs);
814   $report->set_column_order(@columns);
815   $report->set_export_options(qw(list_all filter));
816   $report->set_options_from_form;
817   $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
818   $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
819
820   my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted();
821
822   $report->set_options(
823     raw_top_info_text     => $self->render('bank_transactions/report_top',    { output => 0 }, BANK_ACCOUNTS => $bank_accounts),
824     raw_bottom_info_text  => $self->render('bank_transactions/report_bottom', { output => 0 }),
825   );
826 }
827
828 sub _existing_record_link {
829   my ($bt, $invoice) = @_;
830
831   # check whether a record link from banktransaction $bt already exists to
832   # invoice $invoice, returns 1 if that is the case
833
834   die unless $bt->isa("SL::DB::BankTransaction") && ( $invoice->isa("SL::DB::Invoice") || $invoice->isa("SL::DB::PurchaseInvoice") );
835
836   my $linked_record_to_table = $invoice->is_sales ? 'Invoice' : 'PurchaseInvoice';
837   my $linked_records = $bt->linked_records( direction => 'to', to => $linked_record_to_table, query => [ id => $invoice->id ]  );
838
839   return @$linked_records ? 1 : 0;
840 };
841
842 sub init_problems { [] }
843
844 sub init_models {
845   my ($self) = @_;
846
847   SL::Controller::Helper::GetModels->new(
848     controller => $self,
849     sorted     => {
850       _default => {
851         by  => 'transdate',
852         dir => 0,   # 1 = ASC, 0 = DESC : default sort is newest at top
853       },
854       transdate             => t8('Transdate'),
855       remote_name           => t8('Remote name'),
856       amount                => t8('Amount'),
857       invoice_amount        => t8('Assigned'),
858       invoices              => t8('Linked invoices'),
859       valutadate            => t8('Valutadate'),
860       remote_account_number => t8('Remote account number'),
861       remote_bank_code      => t8('Remote bank code'),
862       currency              => t8('Currency'),
863       purpose               => t8('Purpose'),
864       local_account_number  => t8('Local account number'),
865       local_bank_code       => t8('Local bank code'),
866       local_bank_name       => t8('Bank account'),
867     },
868     with_objects => [ 'local_bank_account', 'currency' ],
869   );
870 }
871
872 sub load_ap_record_template_url {
873   my ($self, $template) = @_;
874
875   return $self->url_for(
876     controller                           => 'ap.pl',
877     action                               => 'load_record_template',
878     id                                   => $template->id,
879     'form_defaults.amount_1'             => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
880     'form_defaults.transdate'            => $self->transaction->transdate_as_date,
881     'form_defaults.duedate'              => $self->transaction->transdate_as_date,
882     'form_defaults.no_payment_bookings'  => 1,
883     'form_defaults.paid_1_suggestion'    => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
884     'form_defaults.AP_paid_1_suggestion' => $self->transaction->local_bank_account->chart->accno,
885     'form_defaults.callback'             => $self->callback,
886   );
887 }
888
889 sub load_gl_record_template_url {
890   my ($self, $template) = @_;
891
892   return $self->url_for(
893     controller                           => 'gl.pl',
894     action                               => 'load_record_template',
895     id                                   => $template->id,
896     'form_defaults.amount_1'             => abs($self->transaction->amount), # always positive
897     'form_defaults.transdate'            => $self->transaction->transdate_as_date,
898     'form_defaults.callback'             => $self->callback,
899   );
900 }
901
902 sub setup_search_action_bar {
903   my ($self, %params) = @_;
904
905   for my $bar ($::request->layout->get('actionbar')) {
906     $bar->add(
907       action => [
908         t8('Filter'),
909         submit    => [ '#search_form', { action => 'BankTransaction/list' } ],
910         accesskey => 'enter',
911       ],
912     );
913   }
914 }
915
916 sub setup_list_all_action_bar {
917   my ($self, %params) = @_;
918
919   for my $bar ($::request->layout->get('actionbar')) {
920     $bar->add(
921       action => [
922         t8('Filter'),
923         submit    => [ '#filter_form', { action => 'BankTransaction/list_all' } ],
924         accesskey => 'enter',
925       ],
926     );
927   }
928 }
929
930 1;
931 __END__
932
933 =pod
934
935 =encoding utf8
936
937 =head1 NAME
938
939 SL::Controller::BankTransaction - Posting payments to invoices from
940 bank transactions imported earlier
941
942 =head1 FUNCTIONS
943
944 =over 4
945
946 =item C<save_single_bank_transaction %params>
947
948 Takes a bank transaction ID (as parameter C<bank_transaction_id> and
949 tries to post its amount to a certain number of invoices (parameter
950 C<invoice_ids>, an array ref of database IDs to purchase or sales
951 invoice objects).
952
953 The whole function is wrapped in a database transaction. If an
954 exception occurs the bank transaction is not posted at all. The same
955 is true if the code detects an error during the execution, e.g. a bank
956 transaction that's already been posted earlier. In both cases the
957 database transaction will be rolled back.
958
959 If warnings but not errors occur the database transaction is still
960 committed.
961
962 The return value is an error object or C<undef> if the function
963 succeeded. The calling function will collect all warnings and errors
964 and display them in a nicely formatted table if any occurred.
965
966 An error object is a hash reference containing the following members:
967
968 =over 2
969
970 =item * C<result> — can be either C<warning> or C<error>. Warnings are
971 displayed slightly different than errors.
972
973 =item * C<message> — a human-readable message included in the list of
974 errors meant as the description of why the problem happened
975
976 =item * C<bank_transaction_id>, C<invoice_ids> — the same parameters
977 that the function was called with
978
979 =item * C<bank_transaction> — the database object
980 (C<SL::DB::BankTransaction>) corresponding to C<bank_transaction_id>
981
982 =item * C<invoices> — an array ref of the database objects (either
983 C<SL::DB::Invoice> or C<SL::DB::PurchaseInvoice>) corresponding to
984 C<invoice_ids>
985
986 =back
987
988 =back
989
990 =head1 AUTHOR
991
992 Niclas Zimmermann E<lt>niclas@kivitendo-premium.deE<gt>,
993 Geoffrey Richardson E<lt>information@richardson-bueren.deE<gt>
994
995 =cut