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 $bank_transaction = $data{bank_transaction};
 559
 560   # see pod
 561   if (@{ $bank_transaction->linked_invoices } || $bank_transaction->invoice_amount != 0) {
 562         return {
 563           %data,
 564           result  => 'error',
 565           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),
 566         };
 567       }
 568   my (@warnings);
 569
 570   my $worker = sub {
 571     my $bt_id                 = $data{bank_transaction_id};
 572     my $sign                  = $bank_transaction->amount < 0 ? -1 : 1;
 573     my $amount_of_transaction = $sign * $bank_transaction->amount;
 574     my $payment_received      = $bank_transaction->amount > 0;
 575     my $payment_sent          = $bank_transaction->amount < 0;
 576
 577
 578     foreach my $invoice_id (@{ $params{invoice_ids} }) {
 579       my $invoice = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
 580       if (!$invoice) {
 581         return {
 582           %data,
 583           result  => 'error',
 584           message => $::locale->text("The ID #1 is not a valid database ID.", $invoice_id),
 585         };
 586       }
 587       push @{ $data{invoices} }, $invoice;
 588     }
 589
 590     if (   $payment_received
 591         && any {    ( $_->is_sales && ($_->amount < 0))
 592                  || (!$_->is_sales && ($_->amount > 0))
 593                } @{ $data{invoices} }) {
 594       return {
 595         %data,
 596         result  => 'error',
 597         message => $::locale->text("Received payments can only be posted for sales invoices and purchase credit notes."),
 598       };
 599     }
 600
 601     if (   $payment_sent
 602         && any {    ( $_->is_sales && ($_->amount > 0))
 603                  || (!$_->is_sales && ($_->amount < 0) && ($_->invoice_type eq 'purchase_invoice'))
 604                } @{ $data{invoices} }) {
 605       return {
 606         %data,
 607         result  => 'error',
 608         message => $::locale->text("Sent payments can only be posted for purchase invoices and sales credit notes."),
 609       };
 610     }
 611
 612     my $max_invoices = scalar(@{ $data{invoices} });
 613     my $n_invoices   = 0;
 614
 615     foreach my $invoice (@{ $data{invoices} }) {
 616       my $source = ($data{sources} // [])->[$n_invoices];
 617       my $memo   = ($data{memos}   // [])->[$n_invoices];
 618
 619       $n_invoices++ ;
 620
 621       # Check if bank_transaction already has a link to the invoice, may only be linked once per invoice
 622       # This might be caused by the user reloading a page and resending the form
 623       if (_existing_record_link($bank_transaction, $invoice)) {
 624         return {
 625           %data,
 626           result  => 'error',
 627           message => $::locale->text("Bank transaction with id #1 has already been linked to #2.", $bank_transaction->id, $invoice->displayable_name),
 628         };
 629       }
 630
 631       if (!$amount_of_transaction && $invoice->open_amount) {
 632         return {
 633           %data,
 634           result  => 'error',
 635           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."),
 636         };
 637       }
 638
 639       my $payment_type;
 640       if ( defined $::form->{invoice_skontos}->{"$bt_id"} ) {
 641         $payment_type = shift(@{ $::form->{invoice_skontos}->{"$bt_id"} });
 642       } else {
 643         $payment_type = 'without_skonto';
 644       };
 645
 646
 647       # pay invoice or go to the next bank transaction if the amount is not sufficiently high
 648       if ($invoice->open_amount <= $amount_of_transaction && $n_invoices < $max_invoices) {
 649         my $open_amount = ($payment_type eq 'with_skonto_pt'?$invoice->amount_less_skonto:$invoice->open_amount);
 650         # first calculate new bank transaction amount ...
 651         if ($invoice->is_sales) {
 652           $amount_of_transaction -= $sign * $open_amount;
 653           $bank_transaction->invoice_amount($bank_transaction->invoice_amount + $open_amount);
 654         } else {
 655           $amount_of_transaction += $sign * $open_amount;
 656           $bank_transaction->invoice_amount($bank_transaction->invoice_amount - $open_amount);
 657         }
 658         # ... and then pay the invoice
 659         $invoice->pay_invoice(chart_id     => $bank_transaction->local_bank_account->chart_id,
 660                               trans_id     => $invoice->id,
 661                               amount       => $open_amount,
 662                               payment_type => $payment_type,
 663                               source       => $source,
 664                               memo         => $memo,
 665                               transdate    => $bank_transaction->transdate->to_kivitendo);
 666       } else {
 667         # use the whole amount of the bank transaction for the invoice, overpay the invoice if necessary
 668
 669         # this catches credit_notes and negative sales invoices
 670         if ( $invoice->is_sales && $invoice->amount < 0 ) {
 671           # $invoice->open_amount     is negative for credit_notes
 672           # $bank_transaction->amount is negative for outgoing transactions
 673           # so $amount_of_transaction is negative but needs positive
 674           $amount_of_transaction *= -1;
 675
 676         } elsif (!$invoice->is_sales && $invoice->invoice_type =~ m/ap_transaction|purchase_invoice/) {
 677           # $invoice->open_amount may be negative for ap_transaction but may be positiv for negativ ap_transaction
 678           # if $invoice->open_amount is negative $bank_transaction->amount is positve
 679           # if $invoice->open_amount is positive $bank_transaction->amount is negative
 680           # but amount of transaction is for both positive
 681           $amount_of_transaction *= -1 if $invoice->open_amount == - $amount_of_transaction;
 682         }
 683
 684         my $overpaid_amount = $amount_of_transaction - $invoice->open_amount;
 685         $invoice->pay_invoice(chart_id     => $bank_transaction->local_bank_account->chart_id,
 686                               trans_id     => $invoice->id,
 687                               amount       => $amount_of_transaction,
 688                               payment_type => $payment_type,
 689                               source       => $source,
 690                               memo         => $memo,
 691                               transdate    => $bank_transaction->transdate->to_kivitendo);
 692         $bank_transaction->invoice_amount($bank_transaction->amount);
 693         $amount_of_transaction = 0;
 694
 695         if ($overpaid_amount >= 0.01) {
 696           push @warnings, {
 697             %data,
 698             result  => 'warning',
 699             message => $::locale->text('Invoice #1 was overpaid by #2.', $invoice->invnumber, $::form->format_amount(\%::myconfig, $overpaid_amount, 2)),
 700           };
 701         }
 702       }
 703       # Record a record link from the bank transaction to the invoice
 704       my @props = (
 705         from_table => 'bank_transactions',
 706         from_id    => $bt_id,
 707         to_table   => $invoice->is_sales ? 'ar' : 'ap',
 708         to_id      => $invoice->id,
 709       );
 710
 711       SL::DB::RecordLink->new(@props)->save;
 712
 713       # "close" a sepa_export_item if it exists
 714       # code duplicated in action_save_proposals!
 715       # currently only works, if there is only exactly one open sepa_export_item
 716       if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
 717         if ( scalar @$seis == 1 ) {
 718           # moved the execution and the check for sepa_export into a method,
 719           # this isn't part of a transaction, though
 720           $seis->[0]->set_executed if $invoice->id == $seis->[0]->arap_id;
 721         }
 722       }
 723
 724     }
 725     $bank_transaction->save;
 726
 727     # 'undef' means 'no error' here.
 728     return undef;
 729   };
 730
 731   my $error;
 732   my $rez = $data{bank_transaction}->db->with_transaction(sub {
 733     eval {
 734       $error = $worker->();
 735       1;
 736
 737     } or do {
 738       $error = {
 739         %data,
 740         result  => 'error',
 741         message => $@,
 742       };
 743     };
 744
 745     # Rollback Fehler nicht weiterreichen
 746     # die if $error;
 747     # aber einen rollback von hand
 748     $::lxdebug->message(LXDebug->DEBUG2(),"finish worker with ". ($error ? $error->{result} : '-'));
 749     $data{bank_transaction}->db->dbh->rollback if $error && $error->{result} eq 'error';
 750   });
 751
 752   return grep { $_ } ($error, @warnings);
 753 }
 754
 755 #
 756 # filters
 757 #
 758
 759 sub check_auth {
 760   $::auth->assert('bank_transaction');
 761 }
 762
 763 #
 764 # helpers
 765 #
 766
 767 sub make_filter_summary {
 768   my ($self) = @_;
 769
 770   my $filter = $::form->{filter} || {};
 771   my @filter_strings;
 772
 773   my @filters = (
 774     [ $filter->{"transdate:date::ge"},      $::locale->text('Transdate')  . " " . $::locale->text('From Date') ],
 775     [ $filter->{"transdate:date::le"},      $::locale->text('Transdate')  . " " . $::locale->text('To Date')   ],
 776     [ $filter->{"valutadate:date::ge"},     $::locale->text('Valutadate') . " " . $::locale->text('From Date') ],
 777     [ $filter->{"valutadate:date::le"},     $::locale->text('Valutadate') . " " . $::locale->text('To Date')   ],
 778     [ $filter->{"amount:number"},           $::locale->text('Amount')                                          ],
 779     [ $filter->{"bank_account_id:integer"}, $::locale->text('Local bank account')                              ],
 780   );
 781
 782   for (@filters) {
 783     push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
 784   }
 785
 786   $self->{filter_summary} = join ', ', @filter_strings;
 787 }
 788
 789 sub prepare_report {
 790   my ($self)      = @_;
 791
 792   my $callback    = $self->models->get_callback;
 793
 794   my $report      = SL::ReportGenerator->new(\%::myconfig, $::form);
 795   $self->{report} = $report;
 796
 797   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);
 798   my @sortable    = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount                                  purpose local_account_number local_bank_code);
 799
 800   my %column_defs = (
 801     transdate             => { sub   => sub { $_[0]->transdate_as_date } },
 802     valutadate            => { sub   => sub { $_[0]->valutadate_as_date } },
 803     remote_name           => { },
 804     remote_account_number => { },
 805     remote_bank_code      => { },
 806     amount                => { sub   => sub { $_[0]->amount_as_number },
 807                                align => 'right' },
 808     invoice_amount        => { sub   => sub { $_[0]->invoice_amount_as_number },
 809                                align => 'right' },
 810     invoices              => { sub   => sub { $_[0]->linked_invoices } },
 811     currency              => { sub   => sub { $_[0]->currency->name } },
 812     purpose               => { },
 813     local_account_number  => { sub   => sub { $_[0]->local_bank_account->account_number } },
 814     local_bank_code       => { sub   => sub { $_[0]->local_bank_account->bank_code } },
 815     local_bank_name       => { sub   => sub { $_[0]->local_bank_account->name } },
 816     id                    => {},
 817   );
 818
 819   map { $column_defs{$_}->{text} ||= $::locale->text( $self->models->get_sort_spec->{$_}->{title} ) } keys %column_defs;
 820
 821   $report->set_options(
 822     std_column_visibility => 1,
 823     controller_class      => 'BankTransaction',
 824     output_format         => 'HTML',
 825     top_info_text         => $::locale->text('Bank transactions'),
 826     title                 => $::locale->text('Bank transactions'),
 827     allow_pdf_export      => 1,
 828     allow_csv_export      => 1,
 829   );
 830   $report->set_columns(%column_defs);
 831   $report->set_column_order(@columns);
 832   $report->set_export_options(qw(list_all filter));
 833   $report->set_options_from_form;
 834   $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
 835   $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
 836
 837   my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted();
 838
 839   $report->set_options(
 840     raw_top_info_text     => $self->render('bank_transactions/report_top',    { output => 0 }, BANK_ACCOUNTS => $bank_accounts),
 841     raw_bottom_info_text  => $self->render('bank_transactions/report_bottom', { output => 0 }),
 842   );
 843 }
 844
 845 sub _existing_record_link {
 846   my ($bt, $invoice) = @_;
 847
 848   # check whether a record link from banktransaction $bt already exists to
 849   # invoice $invoice, returns 1 if that is the case
 850
 851   die unless $bt->isa("SL::DB::BankTransaction") && ( $invoice->isa("SL::DB::Invoice") || $invoice->isa("SL::DB::PurchaseInvoice") );
 852
 853   my $linked_record_to_table = $invoice->is_sales ? 'Invoice' : 'PurchaseInvoice';
 854   my $linked_records = $bt->linked_records( direction => 'to', to => $linked_record_to_table, query => [ id => $invoice->id ]  );
 855
 856   return @$linked_records ? 1 : 0;
 857 };
 858
 859 sub init_problems { [] }
 860
 861 sub init_models {
 862   my ($self) = @_;
 863
 864   SL::Controller::Helper::GetModels->new(
 865     controller => $self,
 866     sorted     => {
 867       _default => {
 868         by  => 'transdate',
 869         dir => 0,   # 1 = ASC, 0 = DESC : default sort is newest at top
 870       },
 871       transdate             => t8('Transdate'),
 872       remote_name           => t8('Remote name'),
 873       amount                => t8('Amount'),
 874       invoice_amount        => t8('Assigned'),
 875       invoices              => t8('Linked invoices'),
 876       valutadate            => t8('Valutadate'),
 877       remote_account_number => t8('Remote account number'),
 878       remote_bank_code      => t8('Remote bank code'),
 879       currency              => t8('Currency'),
 880       purpose               => t8('Purpose'),
 881       local_account_number  => t8('Local account number'),
 882       local_bank_code       => t8('Local bank code'),
 883       local_bank_name       => t8('Bank account'),
 884     },
 885     with_objects => [ 'local_bank_account', 'currency' ],
 886   );
 887 }
 888
 889 sub load_ap_record_template_url {
 890   my ($self, $template) = @_;
 891
 892   return $self->url_for(
 893     controller                           => 'ap.pl',
 894     action                               => 'load_record_template',
 895     id                                   => $template->id,
 896     'form_defaults.amount_1'             => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
 897     'form_defaults.transdate'            => $self->transaction->transdate_as_date,
 898     'form_defaults.duedate'              => $self->transaction->transdate_as_date,
 899     'form_defaults.no_payment_bookings'  => 1,
 900     'form_defaults.paid_1_suggestion'    => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
 901     'form_defaults.AP_paid_1_suggestion' => $self->transaction->local_bank_account->chart->accno,
 902     'form_defaults.callback'             => $self->callback,
 903   );
 904 }
 905
 906 sub load_gl_record_template_url {
 907   my ($self, $template, $bt_id) = @_;
 908
 909   return $self->url_for(
 910     controller                           => 'gl.pl',
 911     action                               => 'load_record_template',
 912     id                                   => $template->id,
 913     'form_defaults.amount_1'             => abs($self->transaction->amount), # always positive
 914     'form_defaults.transdate'            => $self->transaction->transdate_as_date,
 915     'form_defaults.callback'             => $self->callback,
 916     'form_defaults.bt_id'                => $self->transaction->id,
 917   );
 918 }
 919
 920 sub setup_search_action_bar {
 921   my ($self, %params) = @_;
 922
 923   for my $bar ($::request->layout->get('actionbar')) {
 924     $bar->add(
 925       action => [
 926         t8('Filter'),
 927         submit    => [ '#search_form', { action => 'BankTransaction/list' } ],
 928         accesskey => 'enter',
 929       ],
 930     );
 931   }
 932 }
 933
 934 sub setup_list_all_action_bar {
 935   my ($self, %params) = @_;
 936
 937   for my $bar ($::request->layout->get('actionbar')) {
 938     $bar->add(
 939       action => [
 940         t8('Filter'),
 941         submit    => [ '#filter_form', { action => 'BankTransaction/list_all' } ],
 942         accesskey => 'enter',
 943       ],
 944     );
 945   }
 946 }
 947
 948 1;
 949 __END__
 950
 951 =pod
 952
 953 =encoding utf8
 954
 955 =head1 NAME
 956
 957 SL::Controller::BankTransaction - Posting payments to invoices from
 958 bank transactions imported earlier
 959
 960 =head1 FUNCTIONS
 961
 962 =over 4
 963
 964 =item C<save_single_bank_transaction %params>
 965
 966 Takes a bank transaction ID (as parameter C<bank_transaction_id> and
 967 tries to post its amount to a certain number of invoices (parameter
 968 C<invoice_ids>, an array ref of database IDs to purchase or sales
 969 invoice objects).
 970
 971 This method cannot handle already partly assigned bank transactions, i.e.
 972 a bank transaction that has a invoice_amount <> 0 but not the fully
 973 transaction amount (invoice_amount == amount).
 974 Currently this state is impossible from the point of the user interface,
 975 but for double safety and further reliance posting an bank_transaction
 976 where some invoice_amount is already assigned or a RecordLink from
 977 bank to document exists will not be accepted.
 978
 979 If the amount of the bank transaction is higher than the sum of
 980 the assigned invoices (1 .. n) the last invoice will be overpayed.
 981
 982 Therefore this function implements not all valid uses cases.
 983
 984 The whole function is wrapped in a database transaction. If an
 985 exception occurs the bank transaction is not posted at all. The same
 986 is true if the code detects an error during the execution, e.g. a bank
 987 transaction that's already been posted earlier. In both cases the
 988 database transaction will be rolled back.
 989
 990 If warnings but not errors occur the database transaction is still
 991 committed.
 992
 993 The return value is an error object or C<undef> if the function
 994 succeeded. The calling function will collect all warnings and errors
 995 and display them in a nicely formatted table if any occurred.
 996
 997 An error object is a hash reference containing the following members:
 998
 999 =over 2
1000
1001 =item * C<result> — can be either C<warning> or C<error>. Warnings are
1002 displayed slightly different than errors.
1003
1004 =item * C<message> — a human-readable message included in the list of
1005 errors meant as the description of why the problem happened
1006
1007 =item * C<bank_transaction_id>, C<invoice_ids> — the same parameters
1008 that the function was called with
1009
1010 =item * C<bank_transaction> — the database object
1011 (C<SL::DB::BankTransaction>) corresponding to C<bank_transaction_id>
1012
1013 =item * C<invoices> — an array ref of the database objects (either
1014 C<SL::DB::Invoice> or C<SL::DB::PurchaseInvoice>) corresponding to
1015 C<invoice_ids>
1016
1017 =back
1018
1019 =back
1020
1021 =head1 AUTHOR
1022
1023 Niclas Zimmermann E<lt>niclas@kivitendo-premium.deE<gt>,
1024 Geoffrey Richardson E<lt>information@richardson-bueren.deE<gt>
1025
1026 =cut