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