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 { my @invnumbers; for my $obj (@{ $_[0]->linked_invoices }) {
 801                                                                 next unless $obj; push @invnumbers, $obj->invnumber } return \@invnumbers } },
 802     currency              => { sub   => sub { $_[0]->currency->name } },
 803     purpose               => { },
 804     local_account_number  => { sub   => sub { $_[0]->local_bank_account->account_number } },
 805     local_bank_code       => { sub   => sub { $_[0]->local_bank_account->bank_code } },
 806     local_bank_name       => { sub   => sub { $_[0]->local_bank_account->name } },
 807     id                    => {},
 808   );
 809
 810   map { $column_defs{$_}->{text} ||= $::locale->text( $self->models->get_sort_spec->{$_}->{title} ) } keys %column_defs;
 811
 812   $report->set_options(
 813     std_column_visibility => 1,
 814     controller_class      => 'BankTransaction',
 815     output_format         => 'HTML',
 816     top_info_text         => $::locale->text('Bank transactions'),
 817     title                 => $::locale->text('Bank transactions'),
 818     allow_pdf_export      => 1,
 819     allow_csv_export      => 1,
 820   );
 821   $report->set_columns(%column_defs);
 822   $report->set_column_order(@columns);
 823   $report->set_export_options(qw(list_all filter));
 824   $report->set_options_from_form;
 825   $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
 826   $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
 827
 828   my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted();
 829
 830   $report->set_options(
 831     raw_top_info_text     => $self->render('bank_transactions/report_top',    { output => 0 }, BANK_ACCOUNTS => $bank_accounts),
 832     raw_bottom_info_text  => $self->render('bank_transactions/report_bottom', { output => 0 }),
 833   );
 834 }
 835
 836 sub init_problems { [] }
 837
 838 sub init_models {
 839   my ($self) = @_;
 840
 841   SL::Controller::Helper::GetModels->new(
 842     controller => $self,
 843     sorted     => {
 844       _default => {
 845         by  => 'transdate',
 846         dir => 0,   # 1 = ASC, 0 = DESC : default sort is newest at top
 847       },
 848       transdate             => t8('Transdate'),
 849       remote_name           => t8('Remote name'),
 850       amount                => t8('Amount'),
 851       invoice_amount        => t8('Assigned'),
 852       invoices              => t8('Linked invoices'),
 853       valutadate            => t8('Valutadate'),
 854       remote_account_number => t8('Remote account number'),
 855       remote_bank_code      => t8('Remote bank code'),
 856       currency              => t8('Currency'),
 857       purpose               => t8('Purpose'),
 858       local_account_number  => t8('Local account number'),
 859       local_bank_code       => t8('Local bank code'),
 860       local_bank_name       => t8('Bank account'),
 861     },
 862     with_objects => [ 'local_bank_account', 'currency' ],
 863   );
 864 }
 865
 866 sub load_ap_record_template_url {
 867   my ($self, $template) = @_;
 868
 869   return $self->url_for(
 870     controller                           => 'ap.pl',
 871     action                               => 'load_record_template',
 872     id                                   => $template->id,
 873     'form_defaults.amount_1'             => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
 874     'form_defaults.transdate'            => $self->transaction->transdate_as_date,
 875     'form_defaults.duedate'              => $self->transaction->transdate_as_date,
 876     'form_defaults.no_payment_bookings'  => 1,
 877     'form_defaults.paid_1_suggestion'    => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
 878     'form_defaults.AP_paid_1_suggestion' => $self->transaction->local_bank_account->chart->accno,
 879     'form_defaults.callback'             => $self->callback,
 880   );
 881 }
 882
 883 sub load_gl_record_template_url {
 884   my ($self, $template) = @_;
 885
 886   return $self->url_for(
 887     controller                           => 'gl.pl',
 888     action                               => 'load_record_template',
 889     id                                   => $template->id,
 890     'form_defaults.amount_1'             => abs($self->transaction->amount), # always positive
 891     'form_defaults.transdate'            => $self->transaction->transdate_as_date,
 892     'form_defaults.callback'             => $self->callback,
 893     'form_defaults.bt_id'                => $self->transaction->id,
 894     'form_defaults.bt_chart_id'          => $self->transaction->local_bank_account->chart->id,
 895   );
 896 }
 897
 898 sub setup_search_action_bar {
 899   my ($self, %params) = @_;
 900
 901   for my $bar ($::request->layout->get('actionbar')) {
 902     $bar->add(
 903       action => [
 904         t8('Filter'),
 905         submit    => [ '#search_form', { action => 'BankTransaction/list' } ],
 906         accesskey => 'enter',
 907       ],
 908     );
 909   }
 910 }
 911
 912 sub setup_list_all_action_bar {
 913   my ($self, %params) = @_;
 914
 915   for my $bar ($::request->layout->get('actionbar')) {
 916     $bar->add(
 917       action => [
 918         t8('Filter'),
 919         submit    => [ '#filter_form', { action => 'BankTransaction/list_all' } ],
 920         accesskey => 'enter',
 921       ],
 922     );
 923   }
 924 }
 925
 926 1;
 927 __END__
 928
 929 =pod
 930
 931 =encoding utf8
 932
 933 =head1 NAME
 934
 935 SL::Controller::BankTransaction - Posting payments to invoices from
 936 bank transactions imported earlier
 937
 938 =head1 FUNCTIONS
 939
 940 =over 4
 941
 942 =item C<save_single_bank_transaction %params>
 943
 944 Takes a bank transaction ID (as parameter C<bank_transaction_id> and
 945 tries to post its amount to a certain number of invoices (parameter
 946 C<invoice_ids>, an array ref of database IDs to purchase or sales
 947 invoice objects).
 948
 949 This method cannot handle already partly assigned bank transactions, i.e.
 950 a bank transaction that has a invoice_amount <> 0 but not the fully
 951 transaction amount (invoice_amount == amount).
 952
 953 If the amount of the bank transaction is higher than the sum of
 954 the assigned invoices (1 .. n) the last invoice will be overpayed.
 955
 956 The whole function is wrapped in a database transaction. If an
 957 exception occurs the bank transaction is not posted at all. The same
 958 is true if the code detects an error during the execution, e.g. a bank
 959 transaction that's already been posted earlier. In both cases the
 960 database transaction will be rolled back.
 961
 962 If warnings but not errors occur the database transaction is still
 963 committed.
 964
 965 The return value is an error object or C<undef> if the function
 966 succeeded. The calling function will collect all warnings and errors
 967 and display them in a nicely formatted table if any occurred.
 968
 969 An error object is a hash reference containing the following members:
 970
 971 =over 2
 972
 973 =item * C<result> — can be either C<warning> or C<error>. Warnings are
 974 displayed slightly different than errors.
 975
 976 =item * C<message> — a human-readable message included in the list of
 977 errors meant as the description of why the problem happened
 978
 979 =item * C<bank_transaction_id>, C<invoice_ids> — the same parameters
 980 that the function was called with
 981
 982 =item * C<bank_transaction> — the database object
 983 (C<SL::DB::BankTransaction>) corresponding to C<bank_transaction_id>
 984
 985 =item * C<invoices> — an array ref of the database objects (either
 986 C<SL::DB::Invoice> or C<SL::DB::PurchaseInvoice>) corresponding to
 987 C<invoice_ids>
 988
 989 =back
 990
 991 =back
 992
 993 =head1 AUTHOR
 994
 995 Niclas Zimmermann E<lt>niclas@kivitendo-premium.deE<gt>,
 996 Geoffrey Richardson E<lt>information@richardson-bueren.deE<gt>
 997
 998 =cut