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 use SL::Presenter;
  29
  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
 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   # first collect sepa export items to open invoices
 125   foreach my $open_invoice (@all_open_invoices){
 126     $open_invoice->{realamount}  = $::form->format_amount(\%::myconfig,$open_invoice->amount,2);
 127     $open_invoice->{skonto_type} = 'without_skonto';
 128     foreach ( @{$all_open_sepa_export_items}) {
 129       if ( $_->ap_id == $open_invoice->id ||  $_->ar_id == $open_invoice->id ) {
 130         my $factor                   = ($_->ar_id == $open_invoice->id ? 1 : -1);
 131         #$main::lxdebug->message(LXDebug->DEBUG2(),"sepa_exitem=".$_->id." for invoice ".$open_invoice->id." factor=".$factor);
 132         $open_invoice->{realamount}  = $::form->format_amount(\%::myconfig,$open_invoice->amount*$factor,2);
 133         push @{$open_invoice->{sepa_export_item}}, $_;
 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
 144   # try to match each bank_transaction with each of the possible open invoices
 145   # by awarding points
 146   my @proposals;
 147
 148   foreach my $bt (@{ $bank_transactions }) {
 149     ## 5 Stellen hinter dem Komma auf 2 Stellen reduzieren
 150     $bt->amount($bt->amount*1);
 151     $bt->invoice_amount($bt->invoice_amount*1);
 152
 153     $bt->{proposals}    = [];
 154     $bt->{rule_matches} = [];
 155
 156     $bt->{remote_name} .= $bt->{remote_name_1} if $bt->{remote_name_1};
 157
 158     if ( $bt->is_batch_transaction ) {
 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           next;
 167         }
 168       }
 169       # batch transaction has no remotename !!
 170     } else {
 171       next unless $bt->{remote_name};  # bank has no name, usually fees, use create invoice to assign
 172     }
 173
 174     # try to match the current $bt to each of the open_invoices, saving the
 175     # results of get_agreement_with_invoice in $open_invoice->{agreement} and
 176     # $open_invoice->{rule_matches}.
 177
 178     # The values are overwritten each time a new bt is checked, so at the end
 179     # of each bt the likely results are filtered and those values are stored in
 180     # the arrays $bt->{proposals} and $bt->{rule_matches}, and the agreement
 181     # score is stored in $bt->{agreement}
 182
 183     foreach my $open_invoice (@all_open_invoices) {
 184       ($open_invoice->{agreement}, $open_invoice->{rule_matches}) = $bt->get_agreement_with_invoice($open_invoice);
 185       $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,
 186                                       $open_invoice->amount * ($open_invoice->{is_ar} ? 1 : -1), 2);
 187     }
 188
 189     my $agreement = 15;
 190     my $min_agreement = 3; # suggestions must have at least this score
 191
 192     my $max_agreement = max map { $_->{agreement} } @all_open_invoices;
 193
 194     # add open_invoices with highest agreement into array $bt->{proposals}
 195     if ( $max_agreement >= $min_agreement ) {
 196       $bt->{proposals} = [ grep { $_->{agreement} == $max_agreement } @all_open_invoices ];
 197       $bt->{agreement} = $max_agreement; #scalar @{ $bt->{proposals} } ? $agreement + 1 : '';
 198
 199       # store the rule_matches in a separate array, so they can be displayed in template
 200       foreach ( @{ $bt->{proposals} } ) {
 201         push(@{$bt->{rule_matches}}, $_->{rule_matches});
 202       };
 203     };
 204   }  # finished one bt
 205   # finished all bt
 206
 207   # separate filter for proposals (second tab, agreement >= 5 and exactly one match)
 208   # to qualify as a proposal there has to be
 209   # * agreement >= 5  TODO: make threshold configurable in configuration
 210   # * there must be only one exact match
 211   # * depending on whether sales or purchase the amount has to have the correct sign (so Gutschriften don't work?)
 212   my $proposal_threshold = 5;
 213   my @otherproposals = grep {
 214        ($_->{agreement} >= $proposal_threshold)
 215     && (1 == scalar @{ $_->{proposals} })
 216     && (@{ $_->{proposals} }[0]->is_sales ? abs(@{ $_->{proposals} }[0]->amount - $_->amount) < 0.01
 217                                           : abs(@{ $_->{proposals} }[0]->amount + $_->amount) < 0.01)
 218   } @{ $bank_transactions };
 219
 220   push  @proposals, @otherproposals;
 221
 222   # sort bank transaction proposals by quality (score) of proposal
 223   $bank_transactions = [ sort { $a->{agreement} <=> $b->{agreement} } @{ $bank_transactions } ] if $::form->{sort_by} eq 'proposal' and $::form->{sort_dir} == 1;
 224   $bank_transactions = [ sort { $b->{agreement} <=> $a->{agreement} } @{ $bank_transactions } ] if $::form->{sort_by} eq 'proposal' and $::form->{sort_dir} == 0;
 225
 226   # for testing with t/bank/banktransaction.t :
 227   if ( $::form->{dont_render_for_test} ) {
 228     return $bank_transactions;
 229   }
 230
 231   $::request->layout->add_javascripts("kivi.BankTransaction.js");
 232   $self->render('bank_transactions/list',
 233                 title             => t8('Bank transactions MT940'),
 234                 BANK_TRANSACTIONS => $bank_transactions,
 235                 PROPOSALS         => \@proposals,
 236                 bank_account      => $bank_account,
 237                 ui_tab            => scalar(@proposals) > 0?1:0,
 238               );
 239 }
 240
 241 sub action_assign_invoice {
 242   my ($self) = @_;
 243
 244   $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
 245
 246   $self->render('bank_transactions/assign_invoice',
 247                 { layout => 0 },
 248                 title => t8('Assign invoice'),);
 249 }
 250
 251 sub action_create_invoice {
 252   my ($self) = @_;
 253   my %myconfig = %main::myconfig;
 254
 255   $self->transaction(SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id}));
 256
 257   # This was dead code: We compared vendor.account_name with bank_transaction.iban.
 258   # This did never match (Kontonummer != IBAN). It's kivis 09/02 (2013) day
 259   # If refactored/improved, also consider that vendor.iban should be normalized
 260   # user may like to input strings like: 'AT 3333 3333 2222 1111' -> can be checked strictly
 261   # at Vendor code because we need the correct data for all sepa exports.
 262
 263   my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(iban => $self->transaction->{remote_account_number});
 264   my $use_vendor_filter     = $self->transaction->{remote_account_number} && $vendor_of_transaction;
 265
 266   my $templates_ap = SL::DB::Manager::RecordTemplate->get_all(
 267     where        => [ template_type => 'ap_transaction' ],
 268     with_objects => [ qw(employee vendor) ],
 269   );
 270   my $templates_gl = SL::DB::Manager::RecordTemplate->get_all(
 271     query        => [ template_type => 'gl_transaction',
 272                       chart_id      => SL::DB::Manager::BankAccount->find_by(id => $self->transaction->local_bank_account_id)->chart_id,
 273                     ],
 274     with_objects => [ qw(employee record_template_items) ],
 275   );
 276
 277   # pre filter templates_ap, if we have a vendor match (IBAN eq IBAN) - show and allow user to edit this via gui!
 278   $templates_ap = [ grep { $_->vendor_id == $vendor_of_transaction->id } @{ $templates_ap } ] if $use_vendor_filter;
 279
 280   $self->callback($self->url_for(
 281     action                => 'list',
 282     'filter.bank_account' => $::form->{filter}->{bank_account},
 283     'filter.todate'       => $::form->{filter}->{todate},
 284     'filter.fromdate'     => $::form->{filter}->{fromdate},
 285   ));
 286
 287   $self->render(
 288     'bank_transactions/create_invoice',
 289     { layout => 0 },
 290     title        => t8('Create invoice'),
 291     TEMPLATES_GL => $use_vendor_filter ? undef : $templates_gl,
 292     TEMPLATES_AP => $templates_ap,
 293     vendor_name  => $use_vendor_filter ? $vendor_of_transaction->name : undef,
 294   );
 295 }
 296
 297 sub action_ajax_payment_suggestion {
 298   my ($self) = @_;
 299
 300   # based on a BankTransaction ID and a Invoice or PurchaseInvoice ID passed via $::form,
 301   # create an HTML blob to be used by the js function add_invoices in templates/webpages/bank_transactions/list.html
 302   # and return encoded as JSON
 303
 304   my $bt      = SL::DB::Manager::BankTransaction->find_by( id => $::form->{bt_id} );
 305   my $invoice = SL::DB::Manager::Invoice->find_by( id => $::form->{prop_id} ) || SL::DB::Manager::PurchaseInvoice->find_by( id => $::form->{prop_id} );
 306
 307   die unless $bt and $invoice;
 308
 309   my @select_options = $invoice->get_payment_select_options_for_bank_transaction($::form->{bt_id});
 310
 311   my $html;
 312   $html = $self->render(
 313     'bank_transactions/_payment_suggestion', { output => 0 },
 314     bt_id          => $::form->{bt_id},
 315     prop_id        => $::form->{prop_id},
 316     invoice        => $invoice,
 317     SELECT_OPTIONS => \@select_options,
 318   );
 319
 320   $self->render(\ SL::JSON::to_json( { 'html' => "$html" } ), { layout => 0, type => 'json', process => 0 });
 321 };
 322
 323 sub action_filter_templates {
 324   my ($self) = @_;
 325
 326   $self->{transaction}      = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
 327
 328   my @filter;
 329   push @filter, ('vendor.name'   => { ilike => '%' . $::form->{vendor} . '%' })    if $::form->{vendor};
 330   push @filter, ('template_name' => { ilike => '%' . $::form->{template} . '%' })  if $::form->{template};
 331   push @filter, ('reference'     => { ilike => '%' . $::form->{reference} . '%' }) if $::form->{reference};
 332
 333   my $templates_ap = SL::DB::Manager::RecordTemplate->get_all(
 334     where        => [ template_type => 'ap_transaction', (and => \@filter) x !!@filter ],
 335     with_objects => [ qw(employee vendor) ],
 336   );
 337   my $templates_gl = SL::DB::Manager::RecordTemplate->get_all(
 338     query        => [ template_type => 'gl_transaction',
 339                       chart_id      => SL::DB::Manager::BankAccount->find_by(id => $self->transaction->local_bank_account_id)->chart_id,
 340                       (and => \@filter) x !!@filter
 341                     ],
 342     with_objects => [ qw(employee record_template_items) ],
 343   );
 344
 345   $::form->{filter} //= {};
 346
 347   $self->callback($self->url_for(
 348     action                => 'list',
 349     'filter.bank_account' => $::form->{filter}->{bank_account},
 350     'filter.todate'       => $::form->{filter}->{todate},
 351     'filter.fromdate'     => $::form->{filter}->{fromdate},
 352   ));
 353
 354   my $output  = $self->render(
 355     'bank_transactions/_template_list',
 356     { output => 0 },
 357     TEMPLATES_AP => $templates_ap,
 358     TEMPLATES_GL => $templates_gl,
 359   );
 360
 361   $self->render(\to_json({ html => $output }), { type => 'json', process => 0 });
 362 }
 363
 364 sub action_ajax_add_list {
 365   my ($self) = @_;
 366
 367   my @where_sale     = (amount => { ne => \'paid' });
 368   my @where_purchase = (amount => { ne => \'paid' });
 369
 370   if ($::form->{invnumber}) {
 371     push @where_sale,     (invnumber => { ilike => like($::form->{invnumber})});
 372     push @where_purchase, (invnumber => { ilike => like($::form->{invnumber})});
 373   }
 374
 375   if ($::form->{amount}) {
 376     push @where_sale,     (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
 377     push @where_purchase, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
 378   }
 379
 380   if ($::form->{vcnumber}) {
 381     push @where_sale,     ('customer.customernumber' => { ilike => like($::form->{vcnumber})});
 382     push @where_purchase, ('vendor.vendornumber'     => { ilike => like($::form->{vcnumber})});
 383   }
 384
 385   if ($::form->{vcname}) {
 386     push @where_sale,     ('customer.name' => { ilike => like($::form->{vcname})});
 387     push @where_purchase, ('vendor.name'   => { ilike => like($::form->{vcname})});
 388   }
 389
 390   if ($::form->{transdatefrom}) {
 391     my $fromdate = $::locale->parse_date_to_object($::form->{transdatefrom});
 392     if ( ref($fromdate) eq 'DateTime' ) {
 393       push @where_sale,     ('transdate' => { ge => $fromdate});
 394       push @where_purchase, ('transdate' => { ge => $fromdate});
 395     };
 396   }
 397
 398   if ($::form->{transdateto}) {
 399     my $todate = $::locale->parse_date_to_object($::form->{transdateto});
 400     if ( ref($todate) eq 'DateTime' ) {
 401       $todate->add(days => 1);
 402       push @where_sale,     ('transdate' => { lt => $todate});
 403       push @where_purchase, ('transdate' => { lt => $todate});
 404     };
 405   }
 406
 407   my $all_open_ar_invoices = SL::DB::Manager::Invoice        ->get_all(where => \@where_sale,     with_objects => 'customer');
 408   my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => \@where_purchase, with_objects => 'vendor');
 409
 410   my @all_open_invoices = @{ $all_open_ar_invoices };
 411   # add ap invoices, filtering out subcent open amounts
 412   push @all_open_invoices, grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
 413
 414   @all_open_invoices = sort { $a->id <=> $b->id } @all_open_invoices;
 415
 416   my $output  = $self->render(
 417     'bank_transactions/add_list',
 418     { output => 0 },
 419     INVOICES => \@all_open_invoices,
 420   );
 421
 422   my %result = ( count => 0, html => $output );
 423
 424   $self->render(\to_json(\%result), { type => 'json', process => 0 });
 425 }
 426
 427 sub action_ajax_accept_invoices {
 428   my ($self) = @_;
 429
 430   my @selected_invoices;
 431   foreach my $invoice_id (@{ $::form->{invoice_id} || [] }) {
 432     my $invoice_object = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
 433     push @selected_invoices, $invoice_object;
 434   }
 435
 436   $self->render(
 437     'bank_transactions/invoices',
 438     { layout => 0 },
 439     INVOICES => \@selected_invoices,
 440     bt_id    => $::form->{bt_id},
 441   );
 442 }
 443
 444 sub save_invoices {
 445   my ($self) = @_;
 446
 447   return 0 if !$::form->{invoice_ids};
 448
 449   my %invoice_hash = %{ delete $::form->{invoice_ids} };  # each key (the bt line with a bt_id) contains an array of invoice_ids
 450
 451   # e.g. three partial payments with bt_ids 54, 55 and 56 for invoice with id 74:
 452   # $invoice_hash = {
 453   #         '55' => [
 454   #                 '74'
 455   #               ],
 456   #         '54' => [
 457   #                 '74'
 458   #               ],
 459   #         '56' => [
 460   #                 '74'
 461   #               ]
 462   #       };
 463   #
 464   # or if the payment with bt_id 44 is used to pay invoices with ids 50, 51 and 52
 465   # $invoice_hash = {
 466   #           '44' => [ '50', '51', 52' ]
 467   #         };
 468
 469   $::form->{invoice_skontos} ||= {}; # hash of arrays containing the payment types, could be empty
 470
 471   # a bank_transaction may be assigned to several invoices, i.e. a customer
 472   # might pay several open invoices with one transaction
 473
 474   $self->problems([]);
 475
 476   my $count = 0;
 477
 478   if ( $::form->{proposal_ids} ) {
 479     foreach (@{ $::form->{proposal_ids} }) {
 480       my  $bank_transaction_id = $_;
 481       my  $invoice_ids = $invoice_hash{$_};
 482       push @{ $self->problems }, $self->save_single_bank_transaction(
 483         bank_transaction_id => $bank_transaction_id,
 484         invoice_ids         => $invoice_ids,
 485         sources             => ($::form->{sources} // {})->{$_},
 486         memos               => ($::form->{memos}   // {})->{$_},
 487       );
 488       $count += scalar( @{$invoice_ids} );
 489     }
 490   } else {
 491     while ( my ($bank_transaction_id, $invoice_ids) = each(%invoice_hash) ) {
 492       push @{ $self->problems }, $self->save_single_bank_transaction(
 493         bank_transaction_id => $bank_transaction_id,
 494         invoice_ids         => $invoice_ids,
 495         sources             => [  map { $::form->{"sources_${bank_transaction_id}_${_}"} } @{ $invoice_ids } ],
 496         memos               => [  map { $::form->{"memos_${bank_transaction_id}_${_}"}   } @{ $invoice_ids } ],
 497       );
 498       $count += scalar( @{$invoice_ids} );
 499     }
 500   }
 501   foreach (@{ $self->problems }) {
 502     $count-- if $_->{result} eq 'error';
 503   }
 504   return $count;
 505 }
 506
 507 sub action_save_invoices {
 508   my ($self) = @_;
 509   my $count = $self->save_invoices();
 510
 511   flash('ok', t8('#1 invoice(s) saved.', $count));
 512
 513   $self->action_list();
 514 }
 515
 516 sub action_save_proposals {
 517   my ($self) = @_;
 518
 519   if ( $::form->{proposal_ids} ) {
 520     my $propcount = scalar(@{ $::form->{proposal_ids} });
 521     if ( $propcount > 0 ) {
 522       my $count = $self->save_invoices();
 523
 524       flash('ok', t8('#1 proposal(s) with #2 invoice(s) saved.',  $propcount, $count));
 525     }
 526   }
 527   $self->action_list();
 528
 529 }
 530
 531 sub save_single_bank_transaction {
 532   my ($self, %params) = @_;
 533
 534   my %data = (
 535     %params,
 536     bank_transaction => SL::DB::Manager::BankTransaction->find_by(id => $params{bank_transaction_id}),
 537     invoices         => [],
 538   );
 539
 540   if (!$data{bank_transaction}) {
 541     return {
 542       %data,
 543       result => 'error',
 544       message => $::locale->text('The ID #1 is not a valid database ID.', $data{bank_transaction_id}),
 545     };
 546   }
 547
 548   my (@warnings);
 549
 550   my $worker = sub {
 551     my $bt_id                 = $data{bank_transaction_id};
 552     my $bank_transaction      = $data{bank_transaction};
 553     my $sign                  = $bank_transaction->amount < 0 ? -1 : 1;
 554     my $amount_of_transaction = $sign * $bank_transaction->amount;
 555     my $payment_received      = $bank_transaction->amount > 0;
 556     my $payment_sent          = $bank_transaction->amount < 0;
 557
 558
 559     foreach my $invoice_id (@{ $params{invoice_ids} }) {
 560       my $invoice = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
 561       if (!$invoice) {
 562         return {
 563           %data,
 564           result  => 'error',
 565           message => $::locale->text("The ID #1 is not a valid database ID.", $invoice_id),
 566         };
 567       }
 568       push @{ $data{invoices} }, $invoice;
 569     }
 570
 571     if (   $payment_received
 572         && any {    ( $_->is_sales && ($_->amount < 0))
 573                  || (!$_->is_sales && ($_->amount > 0))
 574                } @{ $data{invoices} }) {
 575       return {
 576         %data,
 577         result  => 'error',
 578         message => $::locale->text("Received payments can only be posted for sales invoices and purchase credit notes."),
 579       };
 580     }
 581
 582     if (   $payment_sent
 583         && any {    ( $_->is_sales && ($_->amount > 0))
 584                  || (!$_->is_sales && ($_->amount < 0) && ($_->invoice_type eq 'purchase_invoice'))
 585                } @{ $data{invoices} }) {
 586       return {
 587         %data,
 588         result  => 'error',
 589         message => $::locale->text("Sent payments can only be posted for purchase invoices and sales credit notes."),
 590       };
 591     }
 592
 593     my $max_invoices = scalar(@{ $data{invoices} });
 594     my $n_invoices   = 0;
 595
 596     foreach my $invoice (@{ $data{invoices} }) {
 597       my $source = ($data{sources} // [])->[$n_invoices];
 598       my $memo   = ($data{memos}   // [])->[$n_invoices];
 599
 600       $n_invoices++ ;
 601
 602       # Check if bank_transaction already has a link to the invoice, may only be linked once per invoice
 603       # This might be caused by the user reloading a page and resending the form
 604       if (_existing_record_link($bank_transaction, $invoice)) {
 605         return {
 606           %data,
 607           result  => 'error',
 608           message => $::locale->text("Bank transaction with id #1 has already been linked to #2.", $bank_transaction->id, $invoice->displayable_name),
 609         };
 610       }
 611
 612       if (!$amount_of_transaction && $invoice->open_amount) {
 613         return {
 614           %data,
 615           result  => 'error',
 616           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."),
 617         };
 618       }
 619
 620       my $payment_type;
 621       if ( defined $::form->{invoice_skontos}->{"$bt_id"} ) {
 622         $payment_type = shift(@{ $::form->{invoice_skontos}->{"$bt_id"} });
 623       } else {
 624         $payment_type = 'without_skonto';
 625       };
 626
 627
 628       # pay invoice or go to the next bank transaction if the amount is not sufficiently high
 629       if ($invoice->open_amount <= $amount_of_transaction && $n_invoices < $max_invoices) {
 630         my $open_amount = ($payment_type eq 'with_skonto_pt'?$invoice->amount_less_skonto:$invoice->open_amount);
 631         # first calculate new bank transaction amount ...
 632         if ($invoice->is_sales) {
 633           $amount_of_transaction -= $sign * $open_amount;
 634           $bank_transaction->invoice_amount($bank_transaction->invoice_amount + $open_amount);
 635         } else {
 636           $amount_of_transaction += $sign * $open_amount;
 637           $bank_transaction->invoice_amount($bank_transaction->invoice_amount - $open_amount);
 638         }
 639         # ... and then pay the invoice
 640         $invoice->pay_invoice(chart_id     => $bank_transaction->local_bank_account->chart_id,
 641                               trans_id     => $invoice->id,
 642                               amount       => $open_amount,
 643                               payment_type => $payment_type,
 644                               source       => $source,
 645                               memo         => $memo,
 646                               transdate    => $bank_transaction->transdate->to_kivitendo);
 647       } else {
 648         # use the whole amount of the bank transaction for the invoice, overpay the invoice if necessary
 649
 650         # this catches credit_notes and negative sales invoices
 651         if ( $invoice->is_sales && $invoice->amount < 0 ) {
 652           # $invoice->open_amount     is negative for credit_notes
 653           # $bank_transaction->amount is negative for outgoing transactions
 654           # so $amount_of_transaction is negative but needs positive
 655           $amount_of_transaction *= -1;
 656
 657         } elsif (!$invoice->is_sales && $invoice->invoice_type eq 'ap_transaction' ) {
 658           # $invoice->open_amount may be negative for ap_transaction but may be positiv for negativ ap_transaction
 659           # if $invoice->open_amount is negative $bank_transaction->amount is positve
 660           # if $invoice->open_amount is positive $bank_transaction->amount is negative
 661           # but amount of transaction is for both positive
 662           $amount_of_transaction *= -1 if $invoice->open_amount == - $amount_of_transaction;
 663         }
 664
 665         my $overpaid_amount = $amount_of_transaction - $invoice->open_amount;
 666         $invoice->pay_invoice(chart_id     => $bank_transaction->local_bank_account->chart_id,
 667                               trans_id     => $invoice->id,
 668                               amount       => $amount_of_transaction,
 669                               payment_type => $payment_type,
 670                               source       => $source,
 671                               memo         => $memo,
 672                               transdate    => $bank_transaction->transdate->to_kivitendo);
 673         $bank_transaction->invoice_amount($bank_transaction->amount);
 674         $amount_of_transaction = 0;
 675
 676         if ($overpaid_amount >= 0.01) {
 677           push @warnings, {
 678             %data,
 679             result  => 'warning',
 680             message => $::locale->text('Invoice #1 was overpaid by #2.', $invoice->invnumber, $::form->format_amount(\%::myconfig, $overpaid_amount, 2)),
 681           };
 682         }
 683       }
 684       # Record a record link from the bank transaction to the invoice
 685       my @props = (
 686         from_table => 'bank_transactions',
 687         from_id    => $bt_id,
 688         to_table   => $invoice->is_sales ? 'ar' : 'ap',
 689         to_id      => $invoice->id,
 690       );
 691
 692       SL::DB::RecordLink->new(@props)->save;
 693
 694       # "close" a sepa_export_item if it exists
 695       # code duplicated in action_save_proposals!
 696       # currently only works, if there is only exactly one open sepa_export_item
 697       if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
 698         if ( scalar @$seis == 1 ) {
 699           # moved the execution and the check for sepa_export into a method,
 700           # this isn't part of a transaction, though
 701           $seis->[0]->set_executed if $invoice->id == $seis->[0]->arap_id;
 702         }
 703       }
 704
 705     }
 706     $bank_transaction->save;
 707
 708     # 'undef' means 'no error' here.
 709     return undef;
 710   };
 711
 712   my $error;
 713   my $rez = $data{bank_transaction}->db->with_transaction(sub {
 714     eval {
 715       $error = $worker->();
 716       1;
 717
 718     } or do {
 719       $error = {
 720         %data,
 721         result  => 'error',
 722         message => $@,
 723       };
 724     };
 725
 726     # Rollback Fehler nicht weiterreichen
 727     # die if $error;
 728   });
 729
 730   return grep { $_ } ($error, @warnings);
 731 }
 732
 733 #
 734 # filters
 735 #
 736
 737 sub check_auth {
 738   $::auth->assert('bank_transaction');
 739 }
 740
 741 #
 742 # helpers
 743 #
 744
 745 sub make_filter_summary {
 746   my ($self) = @_;
 747
 748   my $filter = $::form->{filter} || {};
 749   my @filter_strings;
 750
 751   my @filters = (
 752     [ $filter->{"transdate:date::ge"},      $::locale->text('Transdate')  . " " . $::locale->text('From Date') ],
 753     [ $filter->{"transdate:date::le"},      $::locale->text('Transdate')  . " " . $::locale->text('To Date')   ],
 754     [ $filter->{"valutadate:date::ge"},     $::locale->text('Valutadate') . " " . $::locale->text('From Date') ],
 755     [ $filter->{"valutadate:date::le"},     $::locale->text('Valutadate') . " " . $::locale->text('To Date')   ],
 756     [ $filter->{"amount:number"},           $::locale->text('Amount')                                          ],
 757     [ $filter->{"bank_account_id:integer"}, $::locale->text('Local bank account')                              ],
 758   );
 759
 760   for (@filters) {
 761     push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
 762   }
 763
 764   $self->{filter_summary} = join ', ', @filter_strings;
 765 }
 766
 767 sub prepare_report {
 768   my ($self)      = @_;
 769
 770   my $callback    = $self->models->get_callback;
 771
 772   my $report      = SL::ReportGenerator->new(\%::myconfig, $::form);
 773   $self->{report} = $report;
 774
 775   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);
 776   my @sortable    = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount                                  purpose local_account_number local_bank_code);
 777
 778   my %column_defs = (
 779     transdate             => { sub   => sub { $_[0]->transdate_as_date } },
 780     valutadate            => { sub   => sub { $_[0]->valutadate_as_date } },
 781     remote_name           => { },
 782     remote_account_number => { },
 783     remote_bank_code      => { },
 784     amount                => { sub   => sub { $_[0]->amount_as_number },
 785                                align => 'right' },
 786     invoice_amount        => { sub   => sub { $_[0]->invoice_amount_as_number },
 787                                align => 'right' },
 788     invoices              => { sub   => sub { $_[0]->linked_invoices } },
 789     currency              => { sub   => sub { $_[0]->currency->name } },
 790     purpose               => { },
 791     local_account_number  => { sub   => sub { $_[0]->local_bank_account->account_number } },
 792     local_bank_code       => { sub   => sub { $_[0]->local_bank_account->bank_code } },
 793     local_bank_name       => { sub   => sub { $_[0]->local_bank_account->name } },
 794     id                    => {},
 795   );
 796
 797   map { $column_defs{$_}->{text} ||= $::locale->text( $self->models->get_sort_spec->{$_}->{title} ) } keys %column_defs;
 798
 799   $report->set_options(
 800     std_column_visibility => 1,
 801     controller_class      => 'BankTransaction',
 802     output_format         => 'HTML',
 803     top_info_text         => $::locale->text('Bank transactions'),
 804     title                 => $::locale->text('Bank transactions'),
 805     allow_pdf_export      => 1,
 806     allow_csv_export      => 1,
 807   );
 808   $report->set_columns(%column_defs);
 809   $report->set_column_order(@columns);
 810   $report->set_export_options(qw(list_all filter));
 811   $report->set_options_from_form;
 812   $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
 813   $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
 814
 815   my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted();
 816
 817   $report->set_options(
 818     raw_top_info_text     => $self->render('bank_transactions/report_top',    { output => 0 }, BANK_ACCOUNTS => $bank_accounts),
 819     raw_bottom_info_text  => $self->render('bank_transactions/report_bottom', { output => 0 }),
 820   );
 821 }
 822
 823 sub _existing_record_link {
 824   my ($bt, $invoice) = @_;
 825
 826   # check whether a record link from banktransaction $bt already exists to
 827   # invoice $invoice, returns 1 if that is the case
 828
 829   die unless $bt->isa("SL::DB::BankTransaction") && ( $invoice->isa("SL::DB::Invoice") || $invoice->isa("SL::DB::PurchaseInvoice") );
 830
 831   my $linked_record_to_table = $invoice->is_sales ? 'Invoice' : 'PurchaseInvoice';
 832   my $linked_records = $bt->linked_records( direction => 'to', to => $linked_record_to_table, query => [ id => $invoice->id ]  );
 833
 834   return @$linked_records ? 1 : 0;
 835 };
 836
 837 sub init_problems { [] }
 838
 839 sub init_models {
 840   my ($self) = @_;
 841
 842   SL::Controller::Helper::GetModels->new(
 843     controller => $self,
 844     sorted     => {
 845       _default => {
 846         by  => 'transdate',
 847         dir => 0,   # 1 = ASC, 0 = DESC : default sort is newest at top
 848       },
 849       transdate             => t8('Transdate'),
 850       remote_name           => t8('Remote name'),
 851       amount                => t8('Amount'),
 852       invoice_amount        => t8('Assigned'),
 853       invoices              => t8('Linked invoices'),
 854       valutadate            => t8('Valutadate'),
 855       remote_account_number => t8('Remote account number'),
 856       remote_bank_code      => t8('Remote bank code'),
 857       currency              => t8('Currency'),
 858       purpose               => t8('Purpose'),
 859       local_account_number  => t8('Local account number'),
 860       local_bank_code       => t8('Local bank code'),
 861       local_bank_name       => t8('Bank account'),
 862     },
 863     with_objects => [ 'local_bank_account', 'currency' ],
 864   );
 865 }
 866
 867 sub load_ap_record_template_url {
 868   my ($self, $template) = @_;
 869
 870   return $self->url_for(
 871     controller                           => 'ap.pl',
 872     action                               => 'load_record_template',
 873     id                                   => $template->id,
 874     'form_defaults.amount_1'             => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
 875     'form_defaults.transdate'            => $self->transaction->transdate_as_date,
 876     'form_defaults.duedate'              => $self->transaction->transdate_as_date,
 877     'form_defaults.no_payment_bookings'  => 1,
 878     'form_defaults.paid_1_suggestion'    => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
 879     'form_defaults.AP_paid_1_suggestion' => $self->transaction->local_bank_account->chart->accno,
 880     'form_defaults.callback'             => $self->callback,
 881   );
 882 }
 883
 884 sub load_gl_record_template_url {
 885   my ($self, $template) = @_;
 886
 887   return $self->url_for(
 888     controller                           => 'gl.pl',
 889     action                               => 'load_record_template',
 890     id                                   => $template->id,
 891     'form_defaults.amount_1'             => abs($self->transaction->amount), # always positive
 892     'form_defaults.transdate'            => $self->transaction->transdate_as_date,
 893     'form_defaults.callback'             => $self->callback,
 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 The whole function is wrapped in a database transaction. If an
 949 exception occurs the bank transaction is not posted at all. The same
 950 is true if the code detects an error during the execution, e.g. a bank
 951 transaction that's already been posted earlier. In both cases the
 952 database transaction will be rolled back.
 953
 954 If warnings but not errors occur the database transaction is still
 955 committed.
 956
 957 The return value is an error object or C<undef> if the function
 958 succeeded. The calling function will collect all warnings and errors
 959 and display them in a nicely formatted table if any occurred.
 960
 961 An error object is a hash reference containing the following members:
 962
 963 =over 2
 964
 965 =item * C<result> — can be either C<warning> or C<error>. Warnings are
 966 displayed slightly different than errors.
 967
 968 =item * C<message> — a human-readable message included in the list of
 969 errors meant as the description of why the problem happened
 970
 971 =item * C<bank_transaction_id>, C<invoice_ids> — the same parameters
 972 that the function was called with
 973
 974 =item * C<bank_transaction> — the database object
 975 (C<SL::DB::BankTransaction>) corresponding to C<bank_transaction_id>
 976
 977 =item * C<invoices> — an array ref of the database objects (either
 978 C<SL::DB::Invoice> or C<SL::DB::PurchaseInvoice>) corresponding to
 979 C<invoice_ids>
 980
 981 =back
 982
 983 =back
 984
 985 =head1 AUTHOR
 986
 987 Niclas Zimmermann E<lt>niclas@kivitendo-premium.deE<gt>,
 988 Geoffrey Richardson E<lt>information@richardson-bueren.deE<gt>
 989
 990 =cut