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
 622       if (!$amount_of_transaction && $invoice->open_amount) {
 623         return {
 624           %data,
 625           result  => 'error',
 626           message => $::locale->text("A payment can only be posted for multiple invoices if the amount to post is equal to or bigger than the sum of the open amounts of the affected invoices."),
 627         };
 628       }
 629
 630       my $payment_type;
 631       if ( defined $::form->{invoice_skontos}->{"$bt_id"} ) {
 632         $payment_type = shift(@{ $::form->{invoice_skontos}->{"$bt_id"} });
 633       } else {
 634         $payment_type = 'without_skonto';
 635       };
 636
 637
 638       # pay invoice or go to the next bank transaction if the amount is not sufficiently high
 639       if ($invoice->open_amount <= $amount_of_transaction && $n_invoices < $max_invoices) {
 640         my $open_amount = ($payment_type eq 'with_skonto_pt'?$invoice->amount_less_skonto:$invoice->open_amount);
 641         # first calculate new bank transaction amount ...
 642         if ($invoice->is_sales) {
 643           $amount_of_transaction -= $sign * $open_amount;
 644           $bank_transaction->invoice_amount($bank_transaction->invoice_amount + $open_amount);
 645         } else {
 646           $amount_of_transaction += $sign * $open_amount;
 647           $bank_transaction->invoice_amount($bank_transaction->invoice_amount - $open_amount);
 648         }
 649         # ... and then pay the invoice
 650         $invoice->pay_invoice(chart_id     => $bank_transaction->local_bank_account->chart_id,
 651                               trans_id     => $invoice->id,
 652                               amount       => $open_amount,
 653                               payment_type => $payment_type,
 654                               source       => $source,
 655                               memo         => $memo,
 656                               transdate    => $bank_transaction->transdate->to_kivitendo);
 657       } else {
 658         # use the whole amount of the bank transaction for the invoice, overpay the invoice if necessary
 659
 660         # this catches credit_notes and negative sales invoices
 661         if ( $invoice->is_sales && $invoice->amount < 0 ) {
 662           # $invoice->open_amount     is negative for credit_notes
 663           # $bank_transaction->amount is negative for outgoing transactions
 664           # so $amount_of_transaction is negative but needs positive
 665           $amount_of_transaction *= -1;
 666
 667         } elsif (!$invoice->is_sales && $invoice->invoice_type =~ m/ap_transaction|purchase_invoice/) {
 668           # $invoice->open_amount may be negative for ap_transaction but may be positiv for negativ ap_transaction
 669           # if $invoice->open_amount is negative $bank_transaction->amount is positve
 670           # if $invoice->open_amount is positive $bank_transaction->amount is negative
 671           # but amount of transaction is for both positive
 672           $amount_of_transaction *= -1 if $invoice->open_amount == - $amount_of_transaction;
 673         }
 674
 675         my $overpaid_amount = $amount_of_transaction - $invoice->open_amount;
 676         $invoice->pay_invoice(chart_id     => $bank_transaction->local_bank_account->chart_id,
 677                               trans_id     => $invoice->id,
 678                               amount       => $amount_of_transaction,
 679                               payment_type => $payment_type,
 680                               source       => $source,
 681                               memo         => $memo,
 682                               transdate    => $bank_transaction->transdate->to_kivitendo);
 683         $bank_transaction->invoice_amount($bank_transaction->amount);
 684         $amount_of_transaction = 0;
 685
 686         if ($overpaid_amount >= 0.01) {
 687           push @warnings, {
 688             %data,
 689             result  => 'warning',
 690             message => $::locale->text('Invoice #1 was overpaid by #2.', $invoice->invnumber, $::form->format_amount(\%::myconfig, $overpaid_amount, 2)),
 691           };
 692         }
 693       }
 694       # Record a record link from the bank transaction to the invoice
 695       my @props = (
 696         from_table => 'bank_transactions',
 697         from_id    => $bt_id,
 698         to_table   => $invoice->is_sales ? 'ar' : 'ap',
 699         to_id      => $invoice->id,
 700       );
 701
 702       SL::DB::RecordLink->new(@props)->save;
 703
 704       # "close" a sepa_export_item if it exists
 705       # code duplicated in action_save_proposals!
 706       # currently only works, if there is only exactly one open sepa_export_item
 707       if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
 708         if ( scalar @$seis == 1 ) {
 709           # moved the execution and the check for sepa_export into a method,
 710           # this isn't part of a transaction, though
 711           $seis->[0]->set_executed if $invoice->id == $seis->[0]->arap_id;
 712         }
 713       }
 714
 715     }
 716     $bank_transaction->save;
 717
 718     # 'undef' means 'no error' here.
 719     return undef;
 720   };
 721
 722   my $error;
 723   my $rez = $data{bank_transaction}->db->with_transaction(sub {
 724     eval {
 725       $error = $worker->();
 726       1;
 727
 728     } or do {
 729       $error = {
 730         %data,
 731         result  => 'error',
 732         message => $@,
 733       };
 734     };
 735
 736     # Rollback Fehler nicht weiterreichen
 737     # die if $error;
 738     # aber einen rollback von hand
 739     $::lxdebug->message(LXDebug->DEBUG2(),"finish worker with ". ($error ? $error->{result} : '-'));
 740     $data{bank_transaction}->db->dbh->rollback if $error && $error->{result} eq 'error';
 741   });
 742
 743   return grep { $_ } ($error, @warnings);
 744 }
 745
 746 #
 747 # filters
 748 #
 749
 750 sub check_auth {
 751   $::auth->assert('bank_transaction');
 752 }
 753
 754 #
 755 # helpers
 756 #
 757
 758 sub make_filter_summary {
 759   my ($self) = @_;
 760
 761   my $filter = $::form->{filter} || {};
 762   my @filter_strings;
 763
 764   my @filters = (
 765     [ $filter->{"transdate:date::ge"},      $::locale->text('Transdate')  . " " . $::locale->text('From Date') ],
 766     [ $filter->{"transdate:date::le"},      $::locale->text('Transdate')  . " " . $::locale->text('To Date')   ],
 767     [ $filter->{"valutadate:date::ge"},     $::locale->text('Valutadate') . " " . $::locale->text('From Date') ],
 768     [ $filter->{"valutadate:date::le"},     $::locale->text('Valutadate') . " " . $::locale->text('To Date')   ],
 769     [ $filter->{"amount:number"},           $::locale->text('Amount')                                          ],
 770     [ $filter->{"bank_account_id:integer"}, $::locale->text('Local bank account')                              ],
 771   );
 772
 773   for (@filters) {
 774     push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
 775   }
 776
 777   $self->{filter_summary} = join ', ', @filter_strings;
 778 }
 779
 780 sub prepare_report {
 781   my ($self)      = @_;
 782
 783   my $callback    = $self->models->get_callback;
 784
 785   my $report      = SL::ReportGenerator->new(\%::myconfig, $::form);
 786   $self->{report} = $report;
 787
 788   my @columns     = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount invoice_amount invoices currency purpose local_account_number local_bank_code id);
 789   my @sortable    = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount                                  purpose local_account_number local_bank_code);
 790
 791   my %column_defs = (
 792     transdate             => { sub   => sub { $_[0]->transdate_as_date } },
 793     valutadate            => { sub   => sub { $_[0]->valutadate_as_date } },
 794     remote_name           => { },
 795     remote_account_number => { },
 796     remote_bank_code      => { },
 797     amount                => { sub   => sub { $_[0]->amount_as_number },
 798                                align => 'right' },
 799     invoice_amount        => { sub   => sub { $_[0]->invoice_amount_as_number },
 800                                align => 'right' },
 801     invoices              => { sub   => sub { $_[0]->linked_invoices } },
 802     currency              => { sub   => sub { $_[0]->currency->name } },
 803     purpose               => { },
 804     local_account_number  => { sub   => sub { $_[0]->local_bank_account->account_number } },
 805     local_bank_code       => { sub   => sub { $_[0]->local_bank_account->bank_code } },
 806     local_bank_name       => { sub   => sub { $_[0]->local_bank_account->name } },
 807     id                    => {},
 808   );
 809
 810   map { $column_defs{$_}->{text} ||= $::locale->text( $self->models->get_sort_spec->{$_}->{title} ) } keys %column_defs;
 811
 812   $report->set_options(
 813     std_column_visibility => 1,
 814     controller_class      => 'BankTransaction',
 815     output_format         => 'HTML',
 816     top_info_text         => $::locale->text('Bank transactions'),
 817     title                 => $::locale->text('Bank transactions'),
 818     allow_pdf_export      => 1,
 819     allow_csv_export      => 1,
 820   );
 821   $report->set_columns(%column_defs);
 822   $report->set_column_order(@columns);
 823   $report->set_export_options(qw(list_all filter));
 824   $report->set_options_from_form;
 825   $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
 826   $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
 827
 828   my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted();
 829
 830   $report->set_options(
 831     raw_top_info_text     => $self->render('bank_transactions/report_top',    { output => 0 }, BANK_ACCOUNTS => $bank_accounts),
 832     raw_bottom_info_text  => $self->render('bank_transactions/report_bottom', { output => 0 }),
 833   );
 834 }
 835
 836 sub 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, $bt_id) = @_;
 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   );
 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 Currently this state is impossible from the point of the user interface,
 952 but for double safety and further reliance posting an bank_transaction
 953 where some invoice_amount is already assigned or a RecordLink from
 954 bank to document exists will not be accepted.
 955
 956 If the amount of the bank transaction is higher than the sum of
 957 the assigned invoices (1 .. n) the last invoice will be overpayed.
 958
 959 Therefore this function implements not all valid uses cases.
 960
 961 The whole function is wrapped in a database transaction. If an
 962 exception occurs the bank transaction is not posted at all. The same
 963 is true if the code detects an error during the execution, e.g. a bank
 964 transaction that's already been posted earlier. In both cases the
 965 database transaction will be rolled back.
 966
 967 If warnings but not errors occur the database transaction is still
 968 committed.
 969
 970 The return value is an error object or C<undef> if the function
 971 succeeded. The calling function will collect all warnings and errors
 972 and display them in a nicely formatted table if any occurred.
 973
 974 An error object is a hash reference containing the following members:
 975
 976 =over 2
 977
 978 =item * C<result> — can be either C<warning> or C<error>. Warnings are
 979 displayed slightly different than errors.
 980
 981 =item * C<message> — a human-readable message included in the list of
 982 errors meant as the description of why the problem happened
 983
 984 =item * C<bank_transaction_id>, C<invoice_ids> — the same parameters
 985 that the function was called with
 986
 987 =item * C<bank_transaction> — the database object
 988 (C<SL::DB::BankTransaction>) corresponding to C<bank_transaction_id>
 989
 990 =item * C<invoices> — an array ref of the database objects (either
 991 C<SL::DB::Invoice> or C<SL::DB::PurchaseInvoice>) corresponding to
 992 C<invoice_ids>
 993
 994 =back
 995
 996 =back
 997
 998 =head1 AUTHOR
 999
1000 Niclas Zimmermann E<lt>niclas@kivitendo-premium.deE<gt>,
1001 Geoffrey Richardson E<lt>information@richardson-bueren.deE<gt>
1002
1003 =cut