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