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