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