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             = SL::DB::Manager::RecordTemplate->get_all(
 267     where        => [ template_type => 'ap_transaction' ],
 268     with_objects => [ qw(employee vendor) ],
 269   );
 270
 271   #Filter templates
 272   $templates = [ grep { $_->vendor_id == $vendor_of_transaction->id } @{ $templates } ] if $use_vendor_filter;
 273
 274   $self->callback($self->url_for(
 275     action                => 'list',
 276     'filter.bank_account' => $::form->{filter}->{bank_account},
 277     'filter.todate'       => $::form->{filter}->{todate},
 278     'filter.fromdate'     => $::form->{filter}->{fromdate},
 279   ));
 280
 281   $self->render(
 282     'bank_transactions/create_invoice',
 283     { layout => 0 },
 284     title       => t8('Create invoice'),
 285     TEMPLATES   => $templates,
 286     vendor_name => $use_vendor_filter ? $vendor_of_transaction->name : undef,
 287   );
 288 }
 289
 290 sub action_ajax_payment_suggestion {
 291   my ($self) = @_;
 292
 293   # based on a BankTransaction ID and a Invoice or PurchaseInvoice ID passed via $::form,
 294   # create an HTML blob to be used by the js function add_invoices in templates/webpages/bank_transactions/list.html
 295   # and return encoded as JSON
 296
 297   my $bt      = SL::DB::Manager::BankTransaction->find_by( id => $::form->{bt_id} );
 298   my $invoice = SL::DB::Manager::Invoice->find_by( id => $::form->{prop_id} ) || SL::DB::Manager::PurchaseInvoice->find_by( id => $::form->{prop_id} );
 299
 300   die unless $bt and $invoice;
 301
 302   my @select_options = $invoice->get_payment_select_options_for_bank_transaction($::form->{bt_id});
 303
 304   my $html;
 305   $html = $self->render(
 306     'bank_transactions/_payment_suggestion', { output => 0 },
 307     bt_id          => $::form->{bt_id},
 308     prop_id        => $::form->{prop_id},
 309     invoice        => $invoice,
 310     SELECT_OPTIONS => \@select_options,
 311   );
 312
 313   $self->render(\ SL::JSON::to_json( { 'html' => "$html" } ), { layout => 0, type => 'json', process => 0 });
 314 };
 315
 316 sub action_filter_templates {
 317   my ($self) = @_;
 318
 319   $self->{transaction}      = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
 320
 321   my @filter;
 322   push @filter, ('vendor.name' => { ilike => '%' . $::form->{vendor} . '%' }) if $::form->{vendor};
 323
 324   my $templates = SL::DB::Manager::RecordTemplate->get_all(
 325     where        => [ template_type => 'ap_transaction', (or => \@filter) x !!@filter ],
 326     with_objects => [ qw(employee vendor) ],
 327   );
 328
 329   $::form->{filter} //= {};
 330
 331   $self->callback($self->url_for(
 332     action                => 'list',
 333     'filter.bank_account' => $::form->{filter}->{bank_account},
 334     'filter.todate'       => $::form->{filter}->{todate},
 335     'filter.fromdate'     => $::form->{filter}->{fromdate},
 336   ));
 337
 338   my $output  = $self->render(
 339     'bank_transactions/_template_list',
 340     { output => 0 },
 341     TEMPLATES => $templates,
 342   );
 343
 344   $self->render(\to_json({ html => $output }), { type => 'json', process => 0 });
 345 }
 346
 347 sub action_ajax_add_list {
 348   my ($self) = @_;
 349
 350   my @where_sale     = (amount => { ne => \'paid' });
 351   my @where_purchase = (amount => { ne => \'paid' });
 352
 353   if ($::form->{invnumber}) {
 354     push @where_sale,     (invnumber => { ilike => like($::form->{invnumber})});
 355     push @where_purchase, (invnumber => { ilike => like($::form->{invnumber})});
 356   }
 357
 358   if ($::form->{amount}) {
 359     push @where_sale,     (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
 360     push @where_purchase, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
 361   }
 362
 363   if ($::form->{vcnumber}) {
 364     push @where_sale,     ('customer.customernumber' => { ilike => like($::form->{vcnumber})});
 365     push @where_purchase, ('vendor.vendornumber'     => { ilike => like($::form->{vcnumber})});
 366   }
 367
 368   if ($::form->{vcname}) {
 369     push @where_sale,     ('customer.name' => { ilike => like($::form->{vcname})});
 370     push @where_purchase, ('vendor.name'   => { ilike => like($::form->{vcname})});
 371   }
 372
 373   if ($::form->{transdatefrom}) {
 374     my $fromdate = $::locale->parse_date_to_object($::form->{transdatefrom});
 375     if ( ref($fromdate) eq 'DateTime' ) {
 376       push @where_sale,     ('transdate' => { ge => $fromdate});
 377       push @where_purchase, ('transdate' => { ge => $fromdate});
 378     };
 379   }
 380
 381   if ($::form->{transdateto}) {
 382     my $todate = $::locale->parse_date_to_object($::form->{transdateto});
 383     if ( ref($todate) eq 'DateTime' ) {
 384       $todate->add(days => 1);
 385       push @where_sale,     ('transdate' => { lt => $todate});
 386       push @where_purchase, ('transdate' => { lt => $todate});
 387     };
 388   }
 389
 390   my $all_open_ar_invoices = SL::DB::Manager::Invoice        ->get_all(where => \@where_sale,     with_objects => 'customer');
 391   my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => \@where_purchase, with_objects => 'vendor');
 392
 393   my @all_open_invoices = @{ $all_open_ar_invoices };
 394   # add ap invoices, filtering out subcent open amounts
 395   push @all_open_invoices, grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
 396
 397   @all_open_invoices = sort { $a->id <=> $b->id } @all_open_invoices;
 398
 399   my $output  = $self->render(
 400     'bank_transactions/add_list',
 401     { output => 0 },
 402     INVOICES => \@all_open_invoices,
 403   );
 404
 405   my %result = ( count => 0, html => $output );
 406
 407   $self->render(\to_json(\%result), { type => 'json', process => 0 });
 408 }
 409
 410 sub action_ajax_accept_invoices {
 411   my ($self) = @_;
 412
 413   my @selected_invoices;
 414   foreach my $invoice_id (@{ $::form->{invoice_id} || [] }) {
 415     my $invoice_object = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
 416     push @selected_invoices, $invoice_object;
 417   }
 418
 419   $self->render(
 420     'bank_transactions/invoices',
 421     { layout => 0 },
 422     INVOICES => \@selected_invoices,
 423     bt_id    => $::form->{bt_id},
 424   );
 425 }
 426
 427 sub save_invoices {
 428   my ($self) = @_;
 429
 430   return 0 if !$::form->{invoice_ids};
 431
 432   my %invoice_hash = %{ delete $::form->{invoice_ids} };  # each key (the bt line with a bt_id) contains an array of invoice_ids
 433
 434   # e.g. three partial payments with bt_ids 54, 55 and 56 for invoice with id 74:
 435   # $invoice_hash = {
 436   #         '55' => [
 437   #                 '74'
 438   #               ],
 439   #         '54' => [
 440   #                 '74'
 441   #               ],
 442   #         '56' => [
 443   #                 '74'
 444   #               ]
 445   #       };
 446   #
 447   # or if the payment with bt_id 44 is used to pay invoices with ids 50, 51 and 52
 448   # $invoice_hash = {
 449   #           '44' => [ '50', '51', 52' ]
 450   #         };
 451
 452   $::form->{invoice_skontos} ||= {}; # hash of arrays containing the payment types, could be empty
 453
 454   # a bank_transaction may be assigned to several invoices, i.e. a customer
 455   # might pay several open invoices with one transaction
 456
 457   $self->problems([]);
 458
 459   my $count = 0;
 460
 461   if ( $::form->{proposal_ids} ) {
 462     foreach (@{ $::form->{proposal_ids} }) {
 463       my  $bank_transaction_id = $_;
 464       my  $invoice_ids = $invoice_hash{$_};
 465       push @{ $self->problems }, $self->save_single_bank_transaction(
 466         bank_transaction_id => $bank_transaction_id,
 467         invoice_ids         => $invoice_ids,
 468         sources             => ($::form->{sources} // {})->{$_},
 469         memos               => ($::form->{memos}   // {})->{$_},
 470       );
 471       $count += scalar( @{$invoice_ids} );
 472     }
 473   } else {
 474     while ( my ($bank_transaction_id, $invoice_ids) = each(%invoice_hash) ) {
 475       push @{ $self->problems }, $self->save_single_bank_transaction(
 476         bank_transaction_id => $bank_transaction_id,
 477         invoice_ids         => $invoice_ids,
 478         sources             => [  map { $::form->{"sources_${bank_transaction_id}_${_}"} } @{ $invoice_ids } ],
 479         memos               => [  map { $::form->{"memos_${bank_transaction_id}_${_}"}   } @{ $invoice_ids } ],
 480       );
 481       $count += scalar( @{$invoice_ids} );
 482     }
 483   }
 484   foreach (@{ $self->problems }) {
 485     $count-- if $_->{result} eq 'error';
 486   }
 487   return $count;
 488 }
 489
 490 sub action_save_invoices {
 491   my ($self) = @_;
 492   my $count = $self->save_invoices();
 493
 494   flash('ok', t8('#1 invoice(s) saved.', $count));
 495
 496   $self->action_list();
 497 }
 498
 499 sub action_save_proposals {
 500   my ($self) = @_;
 501
 502   if ( $::form->{proposal_ids} ) {
 503     my $propcount = scalar(@{ $::form->{proposal_ids} });
 504     if ( $propcount > 0 ) {
 505       my $count = $self->save_invoices();
 506
 507       flash('ok', t8('#1 proposal(s) with #2 invoice(s) saved.',  $propcount, $count));
 508     }
 509   }
 510   $self->action_list();
 511
 512 }
 513
 514 sub save_single_bank_transaction {
 515   my ($self, %params) = @_;
 516
 517   my %data = (
 518     %params,
 519     bank_transaction => SL::DB::Manager::BankTransaction->find_by(id => $params{bank_transaction_id}),
 520     invoices         => [],
 521   );
 522
 523   if (!$data{bank_transaction}) {
 524     return {
 525       %data,
 526       result => 'error',
 527       message => $::locale->text('The ID #1 is not a valid database ID.', $data{bank_transaction_id}),
 528     };
 529   }
 530
 531   my (@warnings);
 532
 533   my $worker = sub {
 534     my $bt_id                 = $data{bank_transaction_id};
 535     my $bank_transaction      = $data{bank_transaction};
 536     my $sign                  = $bank_transaction->amount < 0 ? -1 : 1;
 537     my $amount_of_transaction = $sign * $bank_transaction->amount;
 538     my $payment_received      = $bank_transaction->amount > 0;
 539     my $payment_sent          = $bank_transaction->amount < 0;
 540
 541
 542     foreach my $invoice_id (@{ $params{invoice_ids} }) {
 543       my $invoice = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
 544       if (!$invoice) {
 545         return {
 546           %data,
 547           result  => 'error',
 548           message => $::locale->text("The ID #1 is not a valid database ID.", $invoice_id),
 549         };
 550       }
 551       push @{ $data{invoices} }, $invoice;
 552     }
 553
 554     if (   $payment_received
 555         && any {    ( $_->is_sales && ($_->amount < 0))
 556                  || (!$_->is_sales && ($_->amount > 0))
 557                } @{ $data{invoices} }) {
 558       return {
 559         %data,
 560         result  => 'error',
 561         message => $::locale->text("Received payments can only be posted for sales invoices and purchase credit notes."),
 562       };
 563     }
 564
 565     if (   $payment_sent
 566         && any {    ( $_->is_sales && ($_->amount > 0))
 567                  || (!$_->is_sales && ($_->amount < 0) && ($_->invoice_type eq 'purchase_invoice'))
 568                } @{ $data{invoices} }) {
 569       return {
 570         %data,
 571         result  => 'error',
 572         message => $::locale->text("Sent payments can only be posted for purchase invoices and sales credit notes."),
 573       };
 574     }
 575
 576     my $max_invoices = scalar(@{ $data{invoices} });
 577     my $n_invoices   = 0;
 578
 579     foreach my $invoice (@{ $data{invoices} }) {
 580       my $source = ($data{sources} // [])->[$n_invoices];
 581       my $memo   = ($data{memos}   // [])->[$n_invoices];
 582
 583       $n_invoices++ ;
 584
 585       # Check if bank_transaction already has a link to the invoice, may only be linked once per invoice
 586       # This might be caused by the user reloading a page and resending the form
 587       if (_existing_record_link($bank_transaction, $invoice)) {
 588         return {
 589           %data,
 590           result  => 'error',
 591           message => $::locale->text("Bank transaction with id #1 has already been linked to #2.", $bank_transaction->id, $invoice->displayable_name),
 592         };
 593       }
 594
 595       if (!$amount_of_transaction && $invoice->open_amount) {
 596         return {
 597           %data,
 598           result  => 'error',
 599           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."),
 600         };
 601       }
 602
 603       my $payment_type;
 604       if ( defined $::form->{invoice_skontos}->{"$bt_id"} ) {
 605         $payment_type = shift(@{ $::form->{invoice_skontos}->{"$bt_id"} });
 606       } else {
 607         $payment_type = 'without_skonto';
 608       };
 609
 610
 611       # pay invoice or go to the next bank transaction if the amount is not sufficiently high
 612       if ($invoice->open_amount <= $amount_of_transaction && $n_invoices < $max_invoices) {
 613         my $open_amount = ($payment_type eq 'with_skonto_pt'?$invoice->amount_less_skonto:$invoice->open_amount);
 614         # first calculate new bank transaction amount ...
 615         if ($invoice->is_sales) {
 616           $amount_of_transaction -= $sign * $open_amount;
 617           $bank_transaction->invoice_amount($bank_transaction->invoice_amount + $open_amount);
 618         } else {
 619           $amount_of_transaction += $sign * $open_amount;
 620           $bank_transaction->invoice_amount($bank_transaction->invoice_amount - $open_amount);
 621         }
 622         # ... and then pay the invoice
 623         $invoice->pay_invoice(chart_id     => $bank_transaction->local_bank_account->chart_id,
 624                               trans_id     => $invoice->id,
 625                               amount       => $open_amount,
 626                               payment_type => $payment_type,
 627                               source       => $source,
 628                               memo         => $memo,
 629                               transdate    => $bank_transaction->transdate->to_kivitendo);
 630       } else {
 631         # use the whole amount of the bank transaction for the invoice, overpay the invoice if necessary
 632
 633         # this catches credit_notes and negative sales invoices
 634         if ( $invoice->is_sales && $invoice->amount < 0 ) {
 635           # $invoice->open_amount     is negative for credit_notes
 636           # $bank_transaction->amount is negative for outgoing transactions
 637           # so $amount_of_transaction is negative but needs positive
 638           $amount_of_transaction *= -1;
 639
 640         } elsif (!$invoice->is_sales && $invoice->invoice_type eq 'ap_transaction' ) {
 641           # $invoice->open_amount may be negative for ap_transaction but may be positiv for negativ ap_transaction
 642           # if $invoice->open_amount is negative $bank_transaction->amount is positve
 643           # if $invoice->open_amount is positive $bank_transaction->amount is negative
 644           # but amount of transaction is for both positive
 645           $amount_of_transaction *= -1 if $invoice->open_amount == - $amount_of_transaction;
 646         }
 647
 648         my $overpaid_amount = $amount_of_transaction - $invoice->open_amount;
 649         $invoice->pay_invoice(chart_id     => $bank_transaction->local_bank_account->chart_id,
 650                               trans_id     => $invoice->id,
 651                               amount       => $amount_of_transaction,
 652                               payment_type => $payment_type,
 653                               source       => $source,
 654                               memo         => $memo,
 655                               transdate    => $bank_transaction->transdate->to_kivitendo);
 656         $bank_transaction->invoice_amount($bank_transaction->amount);
 657         $amount_of_transaction = 0;
 658
 659         if ($overpaid_amount >= 0.01) {
 660           push @warnings, {
 661             %data,
 662             result  => 'warning',
 663             message => $::locale->text('Invoice #1 was overpaid by #2.', $invoice->invnumber, $::form->format_amount(\%::myconfig, $overpaid_amount, 2)),
 664           };
 665         }
 666       }
 667       # Record a record link from the bank transaction to the invoice
 668       my @props = (
 669         from_table => 'bank_transactions',
 670         from_id    => $bt_id,
 671         to_table   => $invoice->is_sales ? 'ar' : 'ap',
 672         to_id      => $invoice->id,
 673       );
 674
 675       SL::DB::RecordLink->new(@props)->save;
 676
 677       # "close" a sepa_export_item if it exists
 678       # code duplicated in action_save_proposals!
 679       # currently only works, if there is only exactly one open sepa_export_item
 680       if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
 681         if ( scalar @$seis == 1 ) {
 682           # moved the execution and the check for sepa_export into a method,
 683           # this isn't part of a transaction, though
 684           $seis->[0]->set_executed if $invoice->id == $seis->[0]->arap_id;
 685         }
 686       }
 687
 688     }
 689     $bank_transaction->save;
 690
 691     # 'undef' means 'no error' here.
 692     return undef;
 693   };
 694
 695   my $error;
 696   my $rez = $data{bank_transaction}->db->with_transaction(sub {
 697     eval {
 698       $error = $worker->();
 699       1;
 700
 701     } or do {
 702       $error = {
 703         %data,
 704         result  => 'error',
 705         message => $@,
 706       };
 707     };
 708
 709     # Rollback Fehler nicht weiterreichen
 710     # die if $error;
 711   });
 712
 713   return grep { $_ } ($error, @warnings);
 714 }
 715
 716 #
 717 # filters
 718 #
 719
 720 sub check_auth {
 721   $::auth->assert('bank_transaction');
 722 }
 723
 724 #
 725 # helpers
 726 #
 727
 728 sub make_filter_summary {
 729   my ($self) = @_;
 730
 731   my $filter = $::form->{filter} || {};
 732   my @filter_strings;
 733
 734   my @filters = (
 735     [ $filter->{"transdate:date::ge"},      $::locale->text('Transdate')  . " " . $::locale->text('From Date') ],
 736     [ $filter->{"transdate:date::le"},      $::locale->text('Transdate')  . " " . $::locale->text('To Date')   ],
 737     [ $filter->{"valutadate:date::ge"},     $::locale->text('Valutadate') . " " . $::locale->text('From Date') ],
 738     [ $filter->{"valutadate:date::le"},     $::locale->text('Valutadate') . " " . $::locale->text('To Date')   ],
 739     [ $filter->{"amount:number"},           $::locale->text('Amount')                                          ],
 740     [ $filter->{"bank_account_id:integer"}, $::locale->text('Local bank account')                              ],
 741   );
 742
 743   for (@filters) {
 744     push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
 745   }
 746
 747   $self->{filter_summary} = join ', ', @filter_strings;
 748 }
 749
 750 sub prepare_report {
 751   my ($self)      = @_;
 752
 753   my $callback    = $self->models->get_callback;
 754
 755   my $report      = SL::ReportGenerator->new(\%::myconfig, $::form);
 756   $self->{report} = $report;
 757
 758   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);
 759   my @sortable    = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount                                  purpose local_account_number local_bank_code);
 760
 761   my %column_defs = (
 762     transdate             => { sub   => sub { $_[0]->transdate_as_date } },
 763     valutadate            => { sub   => sub { $_[0]->valutadate_as_date } },
 764     remote_name           => { },
 765     remote_account_number => { },
 766     remote_bank_code      => { },
 767     amount                => { sub   => sub { $_[0]->amount_as_number },
 768                                align => 'right' },
 769     invoice_amount        => { sub   => sub { $_[0]->invoice_amount_as_number },
 770                                align => 'right' },
 771     invoices              => { sub   => sub { $_[0]->linked_invoices } },
 772     currency              => { sub   => sub { $_[0]->currency->name } },
 773     purpose               => { },
 774     local_account_number  => { sub   => sub { $_[0]->local_bank_account->account_number } },
 775     local_bank_code       => { sub   => sub { $_[0]->local_bank_account->bank_code } },
 776     local_bank_name       => { sub   => sub { $_[0]->local_bank_account->name } },
 777     id                    => {},
 778   );
 779
 780   map { $column_defs{$_}->{text} ||= $::locale->text( $self->models->get_sort_spec->{$_}->{title} ) } keys %column_defs;
 781
 782   $report->set_options(
 783     std_column_visibility => 1,
 784     controller_class      => 'BankTransaction',
 785     output_format         => 'HTML',
 786     top_info_text         => $::locale->text('Bank transactions'),
 787     title                 => $::locale->text('Bank transactions'),
 788     allow_pdf_export      => 1,
 789     allow_csv_export      => 1,
 790   );
 791   $report->set_columns(%column_defs);
 792   $report->set_column_order(@columns);
 793   $report->set_export_options(qw(list_all filter));
 794   $report->set_options_from_form;
 795   $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
 796   $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
 797
 798   my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted();
 799
 800   $report->set_options(
 801     raw_top_info_text     => $self->render('bank_transactions/report_top',    { output => 0 }, BANK_ACCOUNTS => $bank_accounts),
 802     raw_bottom_info_text  => $self->render('bank_transactions/report_bottom', { output => 0 }),
 803   );
 804 }
 805
 806 sub _existing_record_link {
 807   my ($bt, $invoice) = @_;
 808
 809   # check whether a record link from banktransaction $bt already exists to
 810   # invoice $invoice, returns 1 if that is the case
 811
 812   die unless $bt->isa("SL::DB::BankTransaction") && ( $invoice->isa("SL::DB::Invoice") || $invoice->isa("SL::DB::PurchaseInvoice") );
 813
 814   my $linked_record_to_table = $invoice->is_sales ? 'Invoice' : 'PurchaseInvoice';
 815   my $linked_records = $bt->linked_records( direction => 'to', to => $linked_record_to_table, query => [ id => $invoice->id ]  );
 816
 817   return @$linked_records ? 1 : 0;
 818 };
 819
 820 sub init_problems { [] }
 821
 822 sub init_models {
 823   my ($self) = @_;
 824
 825   SL::Controller::Helper::GetModels->new(
 826     controller => $self,
 827     sorted     => {
 828       _default => {
 829         by  => 'transdate',
 830         dir => 0,   # 1 = ASC, 0 = DESC : default sort is newest at top
 831       },
 832       transdate             => t8('Transdate'),
 833       remote_name           => t8('Remote name'),
 834       amount                => t8('Amount'),
 835       invoice_amount        => t8('Assigned'),
 836       invoices              => t8('Linked invoices'),
 837       valutadate            => t8('Valutadate'),
 838       remote_account_number => t8('Remote account number'),
 839       remote_bank_code      => t8('Remote bank code'),
 840       currency              => t8('Currency'),
 841       purpose               => t8('Purpose'),
 842       local_account_number  => t8('Local account number'),
 843       local_bank_code       => t8('Local bank code'),
 844       local_bank_name       => t8('Bank account'),
 845     },
 846     with_objects => [ 'local_bank_account', 'currency' ],
 847   );
 848 }
 849
 850 sub load_ap_record_template_url {
 851   my ($self, $template) = @_;
 852
 853   return $self->url_for(
 854     controller                           => 'ap.pl',
 855     action                               => 'load_record_template',
 856     id                                   => $template->id,
 857     'form_defaults.amount_1'             => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
 858     'form_defaults.transdate'            => $self->transaction->transdate_as_date,
 859     'form_defaults.duedate'              => $self->transaction->transdate_as_date,
 860     'form_defaults.no_payment_bookings'  => 1,
 861     'form_defaults.paid_1_suggestion'    => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
 862     'form_defaults.AP_paid_1_suggestion' => $self->transaction->local_bank_account->chart->accno,
 863     'form_defaults.callback'             => $self->callback,
 864   );
 865 }
 866
 867 sub setup_search_action_bar {
 868   my ($self, %params) = @_;
 869
 870   for my $bar ($::request->layout->get('actionbar')) {
 871     $bar->add(
 872       action => [
 873         t8('Filter'),
 874         submit    => [ '#search_form', { action => 'BankTransaction/list' } ],
 875         accesskey => 'enter',
 876       ],
 877     );
 878   }
 879 }
 880
 881 sub setup_list_all_action_bar {
 882   my ($self, %params) = @_;
 883
 884   for my $bar ($::request->layout->get('actionbar')) {
 885     $bar->add(
 886       action => [
 887         t8('Filter'),
 888         submit    => [ '#filter_form', { action => 'BankTransaction/list_all' } ],
 889         accesskey => 'enter',
 890       ],
 891     );
 892   }
 893 }
 894
 895 1;
 896 __END__
 897
 898 =pod
 899
 900 =encoding utf8
 901
 902 =head1 NAME
 903
 904 SL::Controller::BankTransaction - Posting payments to invoices from
 905 bank transactions imported earlier
 906
 907 =head1 FUNCTIONS
 908
 909 =over 4
 910
 911 =item C<save_single_bank_transaction %params>
 912
 913 Takes a bank transaction ID (as parameter C<bank_transaction_id> and
 914 tries to post its amount to a certain number of invoices (parameter
 915 C<invoice_ids>, an array ref of database IDs to purchase or sales
 916 invoice objects).
 917
 918 The whole function is wrapped in a database transaction. If an
 919 exception occurs the bank transaction is not posted at all. The same
 920 is true if the code detects an error during the execution, e.g. a bank
 921 transaction that's already been posted earlier. In both cases the
 922 database transaction will be rolled back.
 923
 924 If warnings but not errors occur the database transaction is still
 925 committed.
 926
 927 The return value is an error object or C<undef> if the function
 928 succeeded. The calling function will collect all warnings and errors
 929 and display them in a nicely formatted table if any occurred.
 930
 931 An error object is a hash reference containing the following members:
 932
 933 =over 2
 934
 935 =item * C<result> — can be either C<warning> or C<error>. Warnings are
 936 displayed slightly different than errors.
 937
 938 =item * C<message> — a human-readable message included in the list of
 939 errors meant as the description of why the problem happened
 940
 941 =item * C<bank_transaction_id>, C<invoice_ids> — the same parameters
 942 that the function was called with
 943
 944 =item * C<bank_transaction> — the database object
 945 (C<SL::DB::BankTransaction>) corresponding to C<bank_transaction_id>
 946
 947 =item * C<invoices> — an array ref of the database objects (either
 948 C<SL::DB::Invoice> or C<SL::DB::PurchaseInvoice>) corresponding to
 949 C<invoice_ids>
 950
 951 =back
 952
 953 =back
 954
 955 =head1 AUTHOR
 956
 957 Niclas Zimmermann E<lt>niclas@kivitendo-premium.deE<gt>,
 958 Geoffrey Richardson E<lt>information@richardson-bueren.deE<gt>
 959
 960 =cut