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         sepa_export_items => $all_open_sepa_export_items,
 187       );
 188       $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,
 189                                       $open_invoice->amount * ($open_invoice->{is_ar} ? 1 : -1), 2);
 190     }
 191
 192     my $agreement = 15;
 193     my $min_agreement = 3; # suggestions must have at least this score
 194
 195     my $max_agreement = max map { $_->{agreement} } @all_open_invoices;
 196
 197     # add open_invoices with highest agreement into array $bt->{proposals}
 198     if ( $max_agreement >= $min_agreement ) {
 199       $bt->{proposals} = [ grep { $_->{agreement} == $max_agreement } @all_open_invoices ];
 200       $bt->{agreement} = $max_agreement; #scalar @{ $bt->{proposals} } ? $agreement + 1 : '';
 201
 202       # store the rule_matches in a separate array, so they can be displayed in template
 203       foreach ( @{ $bt->{proposals} } ) {
 204         push(@{$bt->{rule_matches}}, $_->{rule_matches});
 205       };
 206     };
 207   }  # finished one bt
 208   # finished all bt
 209
 210   # separate filter for proposals (second tab, agreement >= 5 and exactly one match)
 211   # to qualify as a proposal there has to be
 212   # * agreement >= 5  TODO: make threshold configurable in configuration
 213   # * there must be only one exact match
 214   # * depending on whether sales or purchase the amount has to have the correct sign (so Gutschriften don't work?)
 215   my $proposal_threshold = 5;
 216   my @otherproposals = grep {
 217        ($_->{agreement} >= $proposal_threshold)
 218     && (1 == scalar @{ $_->{proposals} })
 219     && (@{ $_->{proposals} }[0]->is_sales ? abs(@{ $_->{proposals} }[0]->amount - $_->amount) < 0.01
 220                                           : abs(@{ $_->{proposals} }[0]->amount + $_->amount) < 0.01)
 221   } @{ $bank_transactions };
 222
 223   push @proposals, @otherproposals;
 224
 225   # sort bank transaction proposals by quality (score) of proposal
 226   if ($::form->{sort_by} && $::form->{sort_by} eq 'proposal') {
 227     if ($::form->{sort_dir}) {
 228       $bank_transactions = [ sort { $a->{agreement} <=> $b->{agreement} } @{ $bank_transactions } ];
 229     } else {
 230       $bank_transactions = [ sort { $b->{agreement} <=> $a->{agreement} } @{ $bank_transactions } ];
 231     }
 232   }
 233
 234   # for testing with t/bank/banktransaction.t :
 235   if ( $::form->{dont_render_for_test} ) {
 236     return $bank_transactions;
 237   }
 238
 239   $::request->layout->add_javascripts("kivi.BankTransaction.js");
 240   $self->render('bank_transactions/list',
 241                 title             => t8('Bank transactions MT940'),
 242                 BANK_TRANSACTIONS => $bank_transactions,
 243                 PROPOSALS         => \@proposals,
 244                 bank_account      => $bank_account,
 245                 ui_tab            => scalar(@proposals) > 0?1:0,
 246               );
 247 }
 248
 249 sub action_assign_invoice {
 250   my ($self) = @_;
 251
 252   $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
 253
 254   $self->render('bank_transactions/assign_invoice',
 255                 { layout => 0 },
 256                 title => t8('Assign invoice'),);
 257 }
 258
 259 sub action_create_invoice {
 260   my ($self) = @_;
 261   my %myconfig = %main::myconfig;
 262
 263   $self->transaction(SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id}));
 264
 265   my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(iban => $self->transaction->{remote_account_number});
 266   my $use_vendor_filter     = $self->transaction->{remote_account_number} && $vendor_of_transaction;
 267
 268   my $templates_ap = SL::DB::Manager::RecordTemplate->get_all(
 269     where        => [ template_type => 'ap_transaction' ],
 270     with_objects => [ qw(employee vendor) ],
 271   );
 272   my $templates_gl = SL::DB::Manager::RecordTemplate->get_all(
 273     query        => [ template_type => 'gl_transaction',
 274                       chart_id      => SL::DB::Manager::BankAccount->find_by(id => $self->transaction->local_bank_account_id)->chart_id,
 275                     ],
 276     with_objects => [ qw(employee record_template_items) ],
 277   );
 278
 279   # pre filter templates_ap, if we have a vendor match (IBAN eq IBAN) - show and allow user to edit this via gui!
 280   $templates_ap = [ grep { $_->vendor_id == $vendor_of_transaction->id } @{ $templates_ap } ] if $use_vendor_filter;
 281
 282   $self->callback($self->url_for(
 283     action                => 'list',
 284     'filter.bank_account' => $::form->{filter}->{bank_account},
 285     'filter.todate'       => $::form->{filter}->{todate},
 286     'filter.fromdate'     => $::form->{filter}->{fromdate},
 287   ));
 288
 289   $self->render(
 290     'bank_transactions/create_invoice',
 291     { layout => 0 },
 292     title        => t8('Create invoice'),
 293     TEMPLATES_GL => $use_vendor_filter && @{ $templates_ap } ? undef : $templates_gl,
 294     TEMPLATES_AP => $templates_ap,
 295     vendor_name  => $use_vendor_filter && @{ $templates_ap } ? $vendor_of_transaction->name : undef,
 296     BT_ID        => $::form->{bt_id},
 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   my $max_count = $count;
 507   foreach (@{ $self->problems }) {
 508     $count-- if $_->{result} eq 'error';
 509   }
 510   return ($count, $max_count);
 511 }
 512
 513 sub action_save_invoices {
 514   my ($self) = @_;
 515   my ($success_count, $max_count) = $self->save_invoices();
 516
 517   if ($success_count == $max_count) {
 518     flash('ok', t8('#1 invoice(s) saved.', $success_count));
 519   } else {
 520     flash('error', t8('At least #1 invoice(s) not saved', $max_count - $success_count));
 521   }
 522
 523   $self->action_list();
 524 }
 525
 526 sub action_save_proposals {
 527   my ($self) = @_;
 528
 529   if ( $::form->{proposal_ids} ) {
 530     my $propcount = scalar(@{ $::form->{proposal_ids} });
 531     if ( $propcount > 0 ) {
 532       my $count = $self->save_invoices();
 533
 534       flash('ok', t8('#1 proposal(s) with #2 invoice(s) saved.',  $propcount, $count));
 535     }
 536   }
 537   $self->action_list();
 538
 539 }
 540
 541 sub save_single_bank_transaction {
 542   my ($self, %params) = @_;
 543
 544   my %data = (
 545     %params,
 546     bank_transaction => SL::DB::Manager::BankTransaction->find_by(id => $params{bank_transaction_id}),
 547     invoices         => [],
 548   );
 549
 550   if (!$data{bank_transaction}) {
 551     return {
 552       %data,
 553       result => 'error',
 554       message => $::locale->text('The ID #1 is not a valid database ID.', $data{bank_transaction_id}),
 555     };
 556   }
 557
 558   my (@warnings);
 559
 560   my $worker = sub {
 561     my $bt_id                 = $data{bank_transaction_id};
 562     my $bank_transaction      = $data{bank_transaction};
 563     my $sign                  = $bank_transaction->amount < 0 ? -1 : 1;
 564     my $amount_of_transaction = $sign * $bank_transaction->amount;
 565     my $payment_received      = $bank_transaction->amount > 0;
 566     my $payment_sent          = $bank_transaction->amount < 0;
 567
 568
 569     foreach my $invoice_id (@{ $params{invoice_ids} }) {
 570       my $invoice = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
 571       if (!$invoice) {
 572         return {
 573           %data,
 574           result  => 'error',
 575           message => $::locale->text("The ID #1 is not a valid database ID.", $invoice_id),
 576         };
 577       }
 578       push @{ $data{invoices} }, $invoice;
 579     }
 580
 581     if (   $payment_received
 582         && any {    ( $_->is_sales && ($_->amount < 0))
 583                  || (!$_->is_sales && ($_->amount > 0))
 584                } @{ $data{invoices} }) {
 585       return {
 586         %data,
 587         result  => 'error',
 588         message => $::locale->text("Received payments can only be posted for sales invoices and purchase credit notes."),
 589       };
 590     }
 591
 592     if (   $payment_sent
 593         && any {    ( $_->is_sales && ($_->amount > 0))
 594                  || (!$_->is_sales && ($_->amount < 0) && ($_->invoice_type eq 'purchase_invoice'))
 595                } @{ $data{invoices} }) {
 596       return {
 597         %data,
 598         result  => 'error',
 599         message => $::locale->text("Sent payments can only be posted for purchase invoices and sales credit notes."),
 600       };
 601     }
 602
 603     my $max_invoices = scalar(@{ $data{invoices} });
 604     my $n_invoices   = 0;
 605
 606     foreach my $invoice (@{ $data{invoices} }) {
 607       my $source = ($data{sources} // [])->[$n_invoices];
 608       my $memo   = ($data{memos}   // [])->[$n_invoices];
 609
 610       $n_invoices++ ;
 611
 612       # Check if bank_transaction already has a link to the invoice, may only be linked once per invoice
 613       # This might be caused by the user reloading a page and resending the form
 614       if (_existing_record_link($bank_transaction, $invoice)) {
 615         return {
 616           %data,
 617           result  => 'error',
 618           message => $::locale->text("Bank transaction with id #1 has already been linked to #2.", $bank_transaction->id, $invoice->displayable_name),
 619         };
 620       }
 621
 622       if (!$amount_of_transaction && $invoice->open_amount) {
 623         return {
 624           %data,
 625           result  => 'error',
 626           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."),
 627         };
 628       }
 629
 630       my $payment_type;
 631       if ( defined $::form->{invoice_skontos}->{"$bt_id"} ) {
 632         $payment_type = shift(@{ $::form->{invoice_skontos}->{"$bt_id"} });
 633       } else {
 634         $payment_type = 'without_skonto';
 635       };
 636
 637
 638       # pay invoice or go to the next bank transaction if the amount is not sufficiently high
 639       if ($invoice->open_amount <= $amount_of_transaction && $n_invoices < $max_invoices) {
 640         my $open_amount = ($payment_type eq 'with_skonto_pt'?$invoice->amount_less_skonto:$invoice->open_amount);
 641         # first calculate new bank transaction amount ...
 642         if ($invoice->is_sales) {
 643           $amount_of_transaction -= $sign * $open_amount;
 644           $bank_transaction->invoice_amount($bank_transaction->invoice_amount + $open_amount);
 645         } else {
 646           $amount_of_transaction += $sign * $open_amount;
 647           $bank_transaction->invoice_amount($bank_transaction->invoice_amount - $open_amount);
 648         }
 649         # ... and then pay the invoice
 650         $invoice->pay_invoice(chart_id     => $bank_transaction->local_bank_account->chart_id,
 651                               trans_id     => $invoice->id,
 652                               amount       => $open_amount,
 653                               payment_type => $payment_type,
 654                               source       => $source,
 655                               memo         => $memo,
 656                               transdate    => $bank_transaction->transdate->to_kivitendo);
 657       } else {
 658         # use the whole amount of the bank transaction for the invoice, overpay the invoice if necessary
 659
 660         # this catches credit_notes and negative sales invoices
 661         if ( $invoice->is_sales && $invoice->amount < 0 ) {
 662           # $invoice->open_amount     is negative for credit_notes
 663           # $bank_transaction->amount is negative for outgoing transactions
 664           # so $amount_of_transaction is negative but needs positive
 665           $amount_of_transaction *= -1;
 666
 667         } elsif (!$invoice->is_sales && $invoice->invoice_type =~ m/ap_transaction|purchase_invoice/) {
 668           # $invoice->open_amount may be negative for ap_transaction but may be positiv for negativ ap_transaction
 669           # if $invoice->open_amount is negative $bank_transaction->amount is positve
 670           # if $invoice->open_amount is positive $bank_transaction->amount is negative
 671           # but amount of transaction is for both positive
 672           $amount_of_transaction *= -1 if $invoice->open_amount == - $amount_of_transaction;
 673         }
 674
 675         my $overpaid_amount = $amount_of_transaction - $invoice->open_amount;
 676         $invoice->pay_invoice(chart_id     => $bank_transaction->local_bank_account->chart_id,
 677                               trans_id     => $invoice->id,
 678                               amount       => $amount_of_transaction,
 679                               payment_type => $payment_type,
 680                               source       => $source,
 681                               memo         => $memo,
 682                               transdate    => $bank_transaction->transdate->to_kivitendo);
 683         $bank_transaction->invoice_amount($bank_transaction->amount);
 684         $amount_of_transaction = 0;
 685
 686         if ($overpaid_amount >= 0.01) {
 687           push @warnings, {
 688             %data,
 689             result  => 'warning',
 690             message => $::locale->text('Invoice #1 was overpaid by #2.', $invoice->invnumber, $::form->format_amount(\%::myconfig, $overpaid_amount, 2)),
 691           };
 692         }
 693       }
 694       # Record a record link from the bank transaction to the invoice
 695       my @props = (
 696         from_table => 'bank_transactions',
 697         from_id    => $bt_id,
 698         to_table   => $invoice->is_sales ? 'ar' : 'ap',
 699         to_id      => $invoice->id,
 700       );
 701
 702       SL::DB::RecordLink->new(@props)->save;
 703
 704       # "close" a sepa_export_item if it exists
 705       # code duplicated in action_save_proposals!
 706       # currently only works, if there is only exactly one open sepa_export_item
 707       if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
 708         if ( scalar @$seis == 1 ) {
 709           # moved the execution and the check for sepa_export into a method,
 710           # this isn't part of a transaction, though
 711           $seis->[0]->set_executed if $invoice->id == $seis->[0]->arap_id;
 712         }
 713       }
 714
 715     }
 716     $bank_transaction->save;
 717
 718     # 'undef' means 'no error' here.
 719     return undef;
 720   };
 721
 722   my $error;
 723   my $rez = $data{bank_transaction}->db->with_transaction(sub {
 724     eval {
 725       $error = $worker->();
 726       1;
 727
 728     } or do {
 729       $error = {
 730         %data,
 731         result  => 'error',
 732         message => $@,
 733       };
 734     };
 735
 736     # Rollback Fehler nicht weiterreichen
 737     # die if $error;
 738     # aber einen rollback von hand
 739     $::lxdebug->message(LXDebug->DEBUG2(),"finish worker with ". ($error ? $error->{result} : '-'));
 740     $data{bank_transaction}->db->dbh->rollback if $error && $error->{result} eq '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, $bt_id) = @_;
 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     'form_defaults.bt_id'                => $self->transaction->id,
 908   );
 909 }
 910
 911 sub setup_search_action_bar {
 912   my ($self, %params) = @_;
 913
 914   for my $bar ($::request->layout->get('actionbar')) {
 915     $bar->add(
 916       action => [
 917         t8('Filter'),
 918         submit    => [ '#search_form', { action => 'BankTransaction/list' } ],
 919         accesskey => 'enter',
 920       ],
 921     );
 922   }
 923 }
 924
 925 sub setup_list_all_action_bar {
 926   my ($self, %params) = @_;
 927
 928   for my $bar ($::request->layout->get('actionbar')) {
 929     $bar->add(
 930       action => [
 931         t8('Filter'),
 932         submit    => [ '#filter_form', { action => 'BankTransaction/list_all' } ],
 933         accesskey => 'enter',
 934       ],
 935     );
 936   }
 937 }
 938
 939 1;
 940 __END__
 941
 942 =pod
 943
 944 =encoding utf8
 945
 946 =head1 NAME
 947
 948 SL::Controller::BankTransaction - Posting payments to invoices from
 949 bank transactions imported earlier
 950
 951 =head1 FUNCTIONS
 952
 953 =over 4
 954
 955 =item C<save_single_bank_transaction %params>
 956
 957 Takes a bank transaction ID (as parameter C<bank_transaction_id> and
 958 tries to post its amount to a certain number of invoices (parameter
 959 C<invoice_ids>, an array ref of database IDs to purchase or sales
 960 invoice objects).
 961
 962 This method cannot handle already partly assigned bank transactions, i.e.
 963 a bank transaction that has a invoice_amount <> 0 but not the fully
 964 transaction amount (invoice_amount == amount).
 965 Currently this state is impossible from the point of the user interface,
 966 but for double safety and further reliance posting an bank_transaction
 967 where some invoice_amount is already assigned or a RecordLink from
 968 bank to document exists will not be accepted.
 969
 970 If the amount of the bank transaction is higher than the sum of
 971 the assigned invoices (1 .. n) the last invoice will be overpayed.
 972
 973 Therefore this function implements not all valid uses cases.
 974
 975 The whole function is wrapped in a database transaction. If an
 976 exception occurs the bank transaction is not posted at all. The same
 977 is true if the code detects an error during the execution, e.g. a bank
 978 transaction that's already been posted earlier. In both cases the
 979 database transaction will be rolled back.
 980
 981 If warnings but not errors occur the database transaction is still
 982 committed.
 983
 984 The return value is an error object or C<undef> if the function
 985 succeeded. The calling function will collect all warnings and errors
 986 and display them in a nicely formatted table if any occurred.
 987
 988 An error object is a hash reference containing the following members:
 989
 990 =over 2
 991
 992 =item * C<result> — can be either C<warning> or C<error>. Warnings are
 993 displayed slightly different than errors.
 994
 995 =item * C<message> — a human-readable message included in the list of
 996 errors meant as the description of why the problem happened
 997
 998 =item * C<bank_transaction_id>, C<invoice_ids> — the same parameters
 999 that the function was called with
1000
1001 =item * C<bank_transaction> — the database object
1002 (C<SL::DB::BankTransaction>) corresponding to C<bank_transaction_id>
1003
1004 =item * C<invoices> — an array ref of the database objects (either
1005 C<SL::DB::Invoice> or C<SL::DB::PurchaseInvoice>) corresponding to
1006 C<invoice_ids>
1007
1008 =back
1009
1010 =back
1011
1012 =head1 AUTHOR
1013
1014 Niclas Zimmermann E<lt>niclas@kivitendo-premium.deE<gt>,
1015 Geoffrey Richardson E<lt>information@richardson-bueren.deE<gt>
1016
1017 =cut