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