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       cleared               => 0,
 102       @where
 103     ],
 104   );
 105   # credit notes have a negative amount, treat differently
 106   my $all_open_ar_invoices = SL::DB::Manager::Invoice        ->get_all(where => [ or => [ amount => { gt => \'paid' },
 107                                                                                           and => [ type    => 'credit_note',
 108                                                                                                    amount  => { lt => \'paid' }
 109                                                                                                  ],
 110                                                                                         ],
 111                                                                                 ],
 112                                                                        with_objects => ['customer','payment_terms']);
 113
 114   my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => [amount => { ne => \'paid' }], with_objects => ['vendor'  ,'payment_terms']);
 115   my $all_open_sepa_export_items = SL::DB::Manager::SepaExportItem->get_all(where => [chart_id => $bank_account->chart_id ,
 116                                                                              'sepa_export.executed' => 0, 'sepa_export.closed' => 0 ], with_objects => ['sepa_export']);
 117
 118   my @all_open_invoices;
 119   # filter out invoices with less than 1 cent outstanding
 120   push @all_open_invoices, map { $_->{is_ar}=1 ; $_ } grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ar_invoices };
 121   push @all_open_invoices, map { $_->{is_ar}=0 ; $_ } grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
 122
 123   my %sepa_exports;
 124   my %sepa_export_items_by_id = partition_by { $_->ar_id || $_->ap_id } @$all_open_sepa_export_items;
 125
 126   # first collect sepa export items to open invoices
 127   foreach my $open_invoice (@all_open_invoices){
 128     $open_invoice->{realamount}  = $::form->format_amount(\%::myconfig,$open_invoice->amount,2);
 129     $open_invoice->{skonto_type} = 'without_skonto';
 130     foreach (@{ $sepa_export_items_by_id{ $open_invoice->id } || [] }) {
 131       my $factor                   = ($_->ar_id == $open_invoice->id ? 1 : -1);
 132       $open_invoice->{realamount}  = $::form->format_amount(\%::myconfig,$open_invoice->amount*$factor,2);
 133
 134       $open_invoice->{skonto_type} = $_->payment_type;
 135       $sepa_exports{$_->sepa_export_id} ||= { count => 0, is_ar => 0, amount => 0, proposed => 0, invoices => [], item => $_ };
 136       $sepa_exports{$_->sepa_export_id}->{count}++;
 137       $sepa_exports{$_->sepa_export_id}->{is_ar}++ if  $_->ar_id == $open_invoice->id;
 138       $sepa_exports{$_->sepa_export_id}->{amount} += $_->amount * $factor;
 139       push @{ $sepa_exports{$_->sepa_export_id}->{invoices} }, $open_invoice;
 140     }
 141   }
 142
 143   # try to match each bank_transaction with each of the possible open invoices
 144   # by awarding points
 145   my @proposals;
 146
 147   foreach my $bt (@{ $bank_transactions }) {
 148     ## 5 Stellen hinter dem Komma auf 2 Stellen reduzieren
 149     $bt->amount($bt->amount*1);
 150     $bt->invoice_amount($bt->invoice_amount*1);
 151
 152     $bt->{proposals}    = [];
 153     $bt->{rule_matches} = [];
 154
 155     $bt->{remote_name} .= $bt->{remote_name_1} if $bt->{remote_name_1};
 156
 157     if ( $bt->is_batch_transaction ) {
 158       my $found=0;
 159       foreach ( keys  %sepa_exports) {
 160         if ( abs(($sepa_exports{$_}->{amount} * 1) - ($bt->amount * 1)) < 0.01 ) {
 161           ## jupp
 162           @{$bt->{proposals}} = @{$sepa_exports{$_}->{invoices}};
 163           $bt->{sepa_export_ok} = 1;
 164           $sepa_exports{$_}->{proposed}=1;
 165           push(@proposals, $bt);
 166           $found=1;
 167           last;
 168         }
 169       }
 170       next if $found;
 171       # batch transaction has no remotename !!
 172     } else {
 173       next unless $bt->{remote_name};  # bank has no name, usually fees, use create invoice to assign
 174     }
 175
 176     # try to match the current $bt to each of the open_invoices, saving the
 177     # results of get_agreement_with_invoice in $open_invoice->{agreement} and
 178     # $open_invoice->{rule_matches}.
 179
 180     # The values are overwritten each time a new bt is checked, so at the end
 181     # of each bt the likely results are filtered and those values are stored in
 182     # the arrays $bt->{proposals} and $bt->{rule_matches}, and the agreement
 183     # score is stored in $bt->{agreement}
 184
 185     foreach my $open_invoice (@all_open_invoices) {
 186       ($open_invoice->{agreement}, $open_invoice->{rule_matches}) = $bt->get_agreement_with_invoice($open_invoice,
 187         sepa_export_items => $all_open_sepa_export_items,
 188       );
 189       $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,
 190                                       $open_invoice->amount * ($open_invoice->{is_ar} ? 1 : -1), 2);
 191     }
 192
 193     my $agreement = 15;
 194     my $min_agreement = 3; # suggestions must have at least this score
 195
 196     my $max_agreement = max map { $_->{agreement} } @all_open_invoices;
 197
 198     # add open_invoices with highest agreement into array $bt->{proposals}
 199     if ( $max_agreement >= $min_agreement ) {
 200       $bt->{proposals} = [ grep { $_->{agreement} == $max_agreement } @all_open_invoices ];
 201       $bt->{agreement} = $max_agreement; #scalar @{ $bt->{proposals} } ? $agreement + 1 : '';
 202
 203       # store the rule_matches in a separate array, so they can be displayed in template
 204       foreach ( @{ $bt->{proposals} } ) {
 205         push(@{$bt->{rule_matches}}, $_->{rule_matches});
 206       };
 207     };
 208   }  # finished one bt
 209   # finished all bt
 210
 211   # separate filter for proposals (second tab, agreement >= 5 and exactly one match)
 212   # to qualify as a proposal there has to be
 213   # * agreement >= 5  TODO: make threshold configurable in configuration
 214   # * there must be only one exact match
 215   # * depending on whether sales or purchase the amount has to have the correct sign (so Gutschriften don't work?)
 216   my $proposal_threshold = 5;
 217   my @otherproposals = grep {
 218        ($_->{agreement} >= $proposal_threshold)
 219     && (1 == scalar @{ $_->{proposals} })
 220     && (@{ $_->{proposals} }[0]->is_sales ? abs(@{ $_->{proposals} }[0]->amount - $_->amount) < 0.01
 221                                           : abs(@{ $_->{proposals} }[0]->amount + $_->amount) < 0.01)
 222   } @{ $bank_transactions };
 223
 224   push @proposals, @otherproposals;
 225
 226   # sort bank transaction proposals by quality (score) of proposal
 227   if ($::form->{sort_by} && $::form->{sort_by} eq 'proposal') {
 228     if ($::form->{sort_dir}) {
 229       $bank_transactions = [ sort { $a->{agreement} <=> $b->{agreement} } @{ $bank_transactions } ];
 230     } else {
 231       $bank_transactions = [ sort { $b->{agreement} <=> $a->{agreement} } @{ $bank_transactions } ];
 232     }
 233   }
 234
 235   # for testing with t/bank/banktransaction.t :
 236   if ( $::form->{dont_render_for_test} ) {
 237     return ( $bank_transactions , \@proposals );
 238   }
 239
 240   $::request->layout->add_javascripts("kivi.BankTransaction.js");
 241   $self->render('bank_transactions/list',
 242                 title             => t8('Bank transactions MT940'),
 243                 BANK_TRANSACTIONS => $bank_transactions,
 244                 PROPOSALS         => \@proposals,
 245                 bank_account      => $bank_account,
 246                 ui_tab            => scalar(@proposals) > 0?1:0,
 247               );
 248 }
 249
 250 sub action_assign_invoice {
 251   my ($self) = @_;
 252
 253   $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
 254
 255   $self->render('bank_transactions/assign_invoice',
 256                 { layout => 0 },
 257                 title => t8('Assign invoice'),);
 258 }
 259
 260 sub action_create_invoice {
 261   my ($self) = @_;
 262   my %myconfig = %main::myconfig;
 263
 264   $self->transaction(SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id}));
 265
 266   my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(iban => $self->transaction->{remote_account_number});
 267   my $use_vendor_filter     = $self->transaction->{remote_account_number} && $vendor_of_transaction;
 268
 269   my $templates_ap = SL::DB::Manager::RecordTemplate->get_all(
 270     where        => [ template_type => 'ap_transaction' ],
 271     with_objects => [ qw(employee vendor) ],
 272   );
 273   my $templates_gl = SL::DB::Manager::RecordTemplate->get_all(
 274     query        => [ template_type => 'gl_transaction',
 275                       chart_id      => SL::DB::Manager::BankAccount->find_by(id => $self->transaction->local_bank_account_id)->chart_id,
 276                     ],
 277     with_objects => [ qw(employee record_template_items) ],
 278   );
 279
 280   # pre filter templates_ap, if we have a vendor match (IBAN eq IBAN) - show and allow user to edit this via gui!
 281   $templates_ap = [ grep { $_->vendor_id == $vendor_of_transaction->id } @{ $templates_ap } ] if $use_vendor_filter;
 282
 283   $self->callback($self->url_for(
 284     action                => 'list',
 285     'filter.bank_account' => $::form->{filter}->{bank_account},
 286     'filter.todate'       => $::form->{filter}->{todate},
 287     'filter.fromdate'     => $::form->{filter}->{fromdate},
 288   ));
 289
 290   $self->render(
 291     'bank_transactions/create_invoice',
 292     { layout => 0 },
 293     title        => t8('Create invoice'),
 294     TEMPLATES_GL => $use_vendor_filter && @{ $templates_ap } ? undef : $templates_gl,
 295     TEMPLATES_AP => $templates_ap,
 296     vendor_name  => $use_vendor_filter && @{ $templates_ap } ? $vendor_of_transaction->name : undef,
 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
 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         # $invoice->open_amount     is negative for credit_notes
 661         # $bank_transaction->amount is negative for outgoing transactions
 662         # so $amount_of_transaction is negative but needs positive
 663         # $invoice->open_amount may be negative for ap_transaction but may be positiv for negative ap_transaction
 664         # if $invoice->open_amount is negative $bank_transaction->amount is positve
 665         # if $invoice->open_amount is positive $bank_transaction->amount is negative
 666         # but amount of transaction is for both positive
 667
 668         $amount_of_transaction *= -1 if ($invoice->amount < 0);
 669
 670         # if we have a skonto case - the last invoice needs skonto
 671         $amount_of_transaction = $invoice->amount_less_skonto if ($payment_type eq 'with_skonto_pt');
 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     'form_defaults.bt_chart_id'          => $self->transaction->local_bank_account->chart->id,
 894   );
 895 }
 896
 897 sub setup_search_action_bar {
 898   my ($self, %params) = @_;
 899
 900   for my $bar ($::request->layout->get('actionbar')) {
 901     $bar->add(
 902       action => [
 903         t8('Filter'),
 904         submit    => [ '#search_form', { action => 'BankTransaction/list' } ],
 905         accesskey => 'enter',
 906       ],
 907     );
 908   }
 909 }
 910
 911 sub setup_list_all_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    => [ '#filter_form', { action => 'BankTransaction/list_all' } ],
 919         accesskey => 'enter',
 920       ],
 921     );
 922   }
 923 }
 924
 925 1;
 926 __END__
 927
 928 =pod
 929
 930 =encoding utf8
 931
 932 =head1 NAME
 933
 934 SL::Controller::BankTransaction - Posting payments to invoices from
 935 bank transactions imported earlier
 936
 937 =head1 FUNCTIONS
 938
 939 =over 4
 940
 941 =item C<save_single_bank_transaction %params>
 942
 943 Takes a bank transaction ID (as parameter C<bank_transaction_id> and
 944 tries to post its amount to a certain number of invoices (parameter
 945 C<invoice_ids>, an array ref of database IDs to purchase or sales
 946 invoice objects).
 947
 948 This method cannot handle already partly assigned bank transactions, i.e.
 949 a bank transaction that has a invoice_amount <> 0 but not the fully
 950 transaction amount (invoice_amount == amount).
 951
 952 If the amount of the bank transaction is higher than the sum of
 953 the assigned invoices (1 .. n) the last invoice will be overpayed.
 954
 955 The whole function is wrapped in a database transaction. If an
 956 exception occurs the bank transaction is not posted at all. The same
 957 is true if the code detects an error during the execution, e.g. a bank
 958 transaction that's already been posted earlier. In both cases the
 959 database transaction will be rolled back.
 960
 961 If warnings but not errors occur the database transaction is still
 962 committed.
 963
 964 The return value is an error object or C<undef> if the function
 965 succeeded. The calling function will collect all warnings and errors
 966 and display them in a nicely formatted table if any occurred.
 967
 968 An error object is a hash reference containing the following members:
 969
 970 =over 2
 971
 972 =item * C<result> — can be either C<warning> or C<error>. Warnings are
 973 displayed slightly different than errors.
 974
 975 =item * C<message> — a human-readable message included in the list of
 976 errors meant as the description of why the problem happened
 977
 978 =item * C<bank_transaction_id>, C<invoice_ids> — the same parameters
 979 that the function was called with
 980
 981 =item * C<bank_transaction> — the database object
 982 (C<SL::DB::BankTransaction>) corresponding to C<bank_transaction_id>
 983
 984 =item * C<invoices> — an array ref of the database objects (either
 985 C<SL::DB::Invoice> or C<SL::DB::PurchaseInvoice>) corresponding to
 986 C<invoice_ids>
 987
 988 =back
 989
 990 =back
 991
 992 =head1 AUTHOR
 993
 994 Niclas Zimmermann E<lt>niclas@kivitendo-premium.deE<gt>,
 995 Geoffrey Richardson E<lt>information@richardson-bueren.deE<gt>
 996
 997 =cut