SL/Controller/BankTransaction.pm

   1 package SL::Controller::BankTransaction;
   2
   3 # idee- möglichkeit bankdaten zu übernehmen in stammdaten
   4 # erst Kontenabgleich, um alle gl-Einträge wegzuhaben
   5 use strict;
   6
   7 use parent qw(SL::Controller::Base);
   8
   9 use SL::Controller::Helper::GetModels;
  10 use SL::Controller::Helper::ReportGenerator;
  11 use SL::ReportGenerator;
  12
  13 use SL::DB::BankTransaction;
  14 use SL::Helper::Flash;
  15 use SL::Locale::String;
  16 use SL::SEPA;
  17 use SL::DB::Invoice;
  18 use SL::DB::PurchaseInvoice;
  19 use SL::DB::RecordLink;
  20 use SL::JSON;
  21 use SL::DB::Chart;
  22 use SL::DB::AccTransaction;
  23 use SL::DB::Tax;
  24 use SL::DB::BankAccount;
  25 use SL::DB::RecordTemplate;
  26 use SL::DB::SepaExportItem;
  27 use SL::DBUtils qw(like);
  28
  29 use List::MoreUtils qw(any);
  30 use List::Util qw(max);
  31
  32 use Rose::Object::MakeMethods::Generic
  33 (
  34   scalar                  => [ qw(callback transaction) ],
  35   'scalar --get_set_init' => [ qw(models problems) ],
  36 );
  37
  38 __PACKAGE__->run_before('check_auth');
  39
  40
  41 #
  42 # actions
  43 #
  44
  45 sub action_search {
  46   my ($self) = @_;
  47
  48   my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted( query => [ obsolete => 0 ] );
  49
  50   $self->setup_search_action_bar;
  51   $self->render('bank_transactions/search',
  52                  BANK_ACCOUNTS => $bank_accounts);
  53 }
  54
  55 sub action_list_all {
  56   my ($self) = @_;
  57
  58   $self->make_filter_summary;
  59   $self->prepare_report;
  60
  61   $self->setup_list_all_action_bar;
  62   $self->report_generator_list_objects(report => $self->{report}, objects => $self->models->get);
  63 }
  64
  65 sub action_list {
  66   my ($self) = @_;
  67
  68   if (!$::form->{filter}{bank_account}) {
  69     flash('error', t8('No bank account chosen!'));
  70     $self->action_search;
  71     return;
  72   }
  73
  74   my $sort_by = $::form->{sort_by} || 'transdate';
  75   $sort_by = 'transdate' if $sort_by eq 'proposal';
  76   $sort_by .= $::form->{sort_dir} ? ' DESC' : ' ASC';
  77
  78   my $fromdate = $::locale->parse_date_to_object($::form->{filter}->{fromdate});
  79   my $todate   = $::locale->parse_date_to_object($::form->{filter}->{todate});
  80   $todate->add( days => 1 ) if $todate;
  81
  82   my @where = ();
  83   push @where, (transdate => { ge => $fromdate }) if ($fromdate);
  84   push @where, (transdate => { lt => $todate })   if ($todate);
  85   my $bank_account = SL::DB::Manager::BankAccount->find_by( id => $::form->{filter}{bank_account} );
  86   # bank_transactions no younger than starting date,
  87   # including starting date (same search behaviour as fromdate)
  88   # but OPEN invoices to be matched may be from before
  89   if ( $bank_account->reconciliation_starting_date ) {
  90     push @where, (transdate => { ge => $bank_account->reconciliation_starting_date });
  91   };
  92
  93   my $bank_transactions = SL::DB::Manager::BankTransaction->get_all(
  94     with_objects => [ 'local_bank_account', 'currency' ],
  95     sort_by      => $sort_by,
  96     limit        => 10000,
  97     where        => [
  98       amount                => {ne => \'invoice_amount'},
  99       local_bank_account_id => $::form->{filter}{bank_account},
 100       @where
 101     ],
 102   );
 103   # credit notes have a negative amount, treat differently
 104   my $all_open_ar_invoices = SL::DB::Manager::Invoice        ->get_all(where => [ or => [ amount => { gt => \'paid' },
 105                                                                                           and => [ type    => 'credit_note',
 106                                                                                                    amount  => { lt => \'paid' }
 107                                                                                                  ],
 108                                                                                         ],
 109                                                                                 ],
 110                                                                        with_objects => ['customer','payment_terms']);
 111
 112   my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => [amount => { ne => \'paid' }], with_objects => ['vendor'  ,'payment_terms']);
 113   my $all_open_sepa_export_items = SL::DB::Manager::SepaExportItem->get_all(where => [chart_id => $bank_account->chart_id ,
 114                                                                              'sepa_export.executed' => 0, 'sepa_export.closed' => 0 ], with_objects => ['sepa_export']);
 115
 116   my @all_open_invoices;
 117   # filter out invoices with less than 1 cent outstanding
 118   push @all_open_invoices, map { $_->{is_ar}=1 ; $_ } grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ar_invoices };
 119   push @all_open_invoices, map { $_->{is_ar}=0 ; $_ } grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
 120
 121   my %sepa_exports;
 122   # first collect sepa export items to open invoices
 123   foreach my $open_invoice (@all_open_invoices){
 124     $open_invoice->{realamount}  = $::form->format_amount(\%::myconfig,$open_invoice->amount,2);
 125     $open_invoice->{skonto_type} = 'without_skonto';
 126     foreach ( @{$all_open_sepa_export_items}) {
 127       if (($_->ap_id && $_->ap_id == $open_invoice->id) || ($_->ar_id && $_->ar_id == $open_invoice->id)) {
 128         my $factor                   = ($_->ar_id == $open_invoice->id ? 1 : -1);
 129         #$main::lxdebug->message(LXDebug->DEBUG2(),"sepa_exitem=".$_->id." for invoice ".$open_invoice->id." factor=".$factor);
 130         $open_invoice->{realamount}  = $::form->format_amount(\%::myconfig,$open_invoice->amount*$factor,2);
 131         $open_invoice->{skonto_type} = $_->payment_type;
 132         $sepa_exports{$_->sepa_export_id} ||= { count => 0, is_ar => 0, amount => 0, proposed => 0, invoices => [], item => $_ };
 133         $sepa_exports{$_->sepa_export_id}->{count}++;
 134         $sepa_exports{$_->sepa_export_id}->{is_ar}++ if  $_->ar_id == $open_invoice->id;
 135         $sepa_exports{$_->sepa_export_id}->{amount} += $_->amount * $factor;
 136         push @{ $sepa_exports{$_->sepa_export_id}->{invoices} }, $open_invoice;
 137       }
 138     }
 139   }
 140
 141   # try to match each bank_transaction with each of the possible open invoices
 142   # by awarding points
 143   my @proposals;
 144
 145   foreach my $bt (@{ $bank_transactions }) {
 146     ## 5 Stellen hinter dem Komma auf 2 Stellen reduzieren
 147     $bt->amount($bt->amount*1);
 148     $bt->invoice_amount($bt->invoice_amount*1);
 149
 150     $bt->{proposals}    = [];
 151     $bt->{rule_matches} = [];
 152
 153     $bt->{remote_name} .= $bt->{remote_name_1} if $bt->{remote_name_1};
 154
 155     if ( $bt->is_batch_transaction ) {
 156       foreach ( keys  %sepa_exports) {
 157         if ( abs(($sepa_exports{$_}->{amount} * 1) - ($bt->amount * 1)) < 0.01 ) {
 158           ## jupp
 159           @{$bt->{proposals}} = @{$sepa_exports{$_}->{invoices}};
 160           $bt->{sepa_export_ok} = 1;
 161           $sepa_exports{$_}->{proposed}=1;
 162           push(@proposals, $bt);
 163           next;
 164         }
 165       }
 166       # batch transaction has no remotename !!
 167     } else {
 168       next unless $bt->{remote_name};  # bank has no name, usually fees, use create invoice to assign
 169     }
 170
 171     # try to match the current $bt to each of the open_invoices, saving the
 172     # results of get_agreement_with_invoice in $open_invoice->{agreement} and
 173     # $open_invoice->{rule_matches}.
 174
 175     # The values are overwritten each time a new bt is checked, so at the end
 176     # of each bt the likely results are filtered and those values are stored in
 177     # the arrays $bt->{proposals} and $bt->{rule_matches}, and the agreement
 178     # score is stored in $bt->{agreement}
 179
 180     foreach my $open_invoice (@all_open_invoices) {
 181       ($open_invoice->{agreement}, $open_invoice->{rule_matches}) = $bt->get_agreement_with_invoice($open_invoice);
 182       $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,
 183                                       $open_invoice->amount * ($open_invoice->{is_ar} ? 1 : -1), 2);
 184     }
 185
 186     my $agreement = 15;
 187     my $min_agreement = 3; # suggestions must have at least this score
 188
 189     my $max_agreement = max map { $_->{agreement} } @all_open_invoices;
 190
 191     # add open_invoices with highest agreement into array $bt->{proposals}
 192     if ( $max_agreement >= $min_agreement ) {
 193       $bt->{proposals} = [ grep { $_->{agreement} == $max_agreement } @all_open_invoices ];
 194       $bt->{agreement} = $max_agreement; #scalar @{ $bt->{proposals} } ? $agreement + 1 : '';
 195
 196       # store the rule_matches in a separate array, so they can be displayed in template
 197       foreach ( @{ $bt->{proposals} } ) {
 198         push(@{$bt->{rule_matches}}, $_->{rule_matches});
 199       };
 200     };
 201   }  # finished one bt
 202   # finished all bt
 203
 204   # separate filter for proposals (second tab, agreement >= 5 and exactly one match)
 205   # to qualify as a proposal there has to be
 206   # * agreement >= 5  TODO: make threshold configurable in configuration
 207   # * there must be only one exact match
 208   # * depending on whether sales or purchase the amount has to have the correct sign (so Gutschriften don't work?)
 209   my $proposal_threshold = 5;
 210   my @otherproposals = grep {
 211        ($_->{agreement} >= $proposal_threshold)
 212     && (1 == scalar @{ $_->{proposals} })
 213     && (@{ $_->{proposals} }[0]->is_sales ? abs(@{ $_->{proposals} }[0]->amount - $_->amount) < 0.01
 214                                           : abs(@{ $_->{proposals} }[0]->amount + $_->amount) < 0.01)
 215   } @{ $bank_transactions };
 216
 217   push @proposals, @otherproposals;
 218
 219   # sort bank transaction proposals by quality (score) of proposal
 220   if ($::form->{sort_by} && $::form->{sort_by} eq 'proposal') {
 221     if ($::form->{sort_dir}) {
 222       $bank_transactions = [ sort { $a->{agreement} <=> $b->{agreement} } @{ $bank_transactions } ];
 223     } else {
 224       $bank_transactions = [ sort { $b->{agreement} <=> $a->{agreement} } @{ $bank_transactions } ];
 225     }
 226   }
 227
 228   # for testing with t/bank/banktransaction.t :
 229   if ( $::form->{dont_render_for_test} ) {
 230     return $bank_transactions;
 231   }
 232
 233   $::request->layout->add_javascripts("kivi.BankTransaction.js");
 234   $self->render('bank_transactions/list',
 235                 title             => t8('Bank transactions MT940'),
 236                 BANK_TRANSACTIONS => $bank_transactions,
 237                 PROPOSALS         => \@proposals,
 238                 bank_account      => $bank_account,
 239                 ui_tab            => scalar(@proposals) > 0?1:0,
 240               );
 241 }
 242
 243 sub action_assign_invoice {
 244   my ($self) = @_;
 245
 246   $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
 247
 248   $self->render('bank_transactions/assign_invoice',
 249                 { layout => 0 },
 250                 title => t8('Assign invoice'),);
 251 }
 252
 253 sub action_create_invoice {
 254   my ($self) = @_;
 255   my %myconfig = %main::myconfig;
 256
 257   $self->transaction(SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id}));
 258
 259   # This was dead code: We compared vendor.account_name with bank_transaction.iban.
 260   # This did never match (Kontonummer != IBAN). It's kivis 09/02 (2013) day
 261   # If refactored/improved, also consider that vendor.iban should be normalized
 262   # user may like to input strings like: 'AT 3333 3333 2222 1111' -> can be checked strictly
 263   # at Vendor code because we need the correct data for all sepa exports.
 264
 265   my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(iban => $self->transaction->{remote_account_number});
 266   my $use_vendor_filter     = $self->transaction->{remote_account_number} && $vendor_of_transaction;
 267
 268   my $templates_ap = SL::DB::Manager::RecordTemplate->get_all(
 269     where        => [ template_type => 'ap_transaction' ],
 270     with_objects => [ qw(employee vendor) ],
 271   );
 272   my $templates_gl = SL::DB::Manager::RecordTemplate->get_all(
 273     query        => [ template_type => 'gl_transaction',
 274                       chart_id      => SL::DB::Manager::BankAccount->find_by(id => $self->transaction->local_bank_account_id)->chart_id,
 275                     ],
 276     with_objects => [ qw(employee record_template_items) ],
 277   );
 278
 279   # pre filter templates_ap, if we have a vendor match (IBAN eq IBAN) - show and allow user to edit this via gui!
 280   $templates_ap = [ grep { $_->vendor_id == $vendor_of_transaction->id } @{ $templates_ap } ] if $use_vendor_filter;
 281
 282   $self->callback($self->url_for(
 283     action                => 'list',
 284     'filter.bank_account' => $::form->{filter}->{bank_account},
 285     'filter.todate'       => $::form->{filter}->{todate},
 286     'filter.fromdate'     => $::form->{filter}->{fromdate},
 287   ));
 288
 289   $self->render(
 290     'bank_transactions/create_invoice',
 291     { layout => 0 },
 292     title        => t8('Create invoice'),
 293     TEMPLATES_GL => $use_vendor_filter && @{ $templates_ap } ? undef : $templates_gl,
 294     TEMPLATES_AP => $templates_ap,
 295     vendor_name  => $use_vendor_filter && @{ $templates_ap } ? $vendor_of_transaction->name : undef,
 296   );
 297 }
 298
 299 sub action_ajax_payment_suggestion {
 300   my ($self) = @_;
 301
 302   # based on a BankTransaction ID and a Invoice or PurchaseInvoice ID passed via $::form,
 303   # create an HTML blob to be used by the js function add_invoices in templates/webpages/bank_transactions/list.html
 304   # and return encoded as JSON
 305
 306   my $bt      = SL::DB::Manager::BankTransaction->find_by( id => $::form->{bt_id} );
 307   my $invoice = SL::DB::Manager::Invoice->find_by( id => $::form->{prop_id} ) || SL::DB::Manager::PurchaseInvoice->find_by( id => $::form->{prop_id} );
 308
 309   die unless $bt and $invoice;
 310
 311   my @select_options = $invoice->get_payment_select_options_for_bank_transaction($::form->{bt_id});
 312
 313   my $html;
 314   $html = $self->render(
 315     'bank_transactions/_payment_suggestion', { output => 0 },
 316     bt_id          => $::form->{bt_id},
 317     prop_id        => $::form->{prop_id},
 318     invoice        => $invoice,
 319     SELECT_OPTIONS => \@select_options,
 320   );
 321
 322   $self->render(\ SL::JSON::to_json( { 'html' => "$html" } ), { layout => 0, type => 'json', process => 0 });
 323 };
 324
 325 sub action_filter_templates {
 326   my ($self) = @_;
 327
 328   $self->{transaction}      = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
 329
 330   my (@filter, @filter_ap);
 331
 332   # filter => gl and ap | filter_ap = ap (i.e. vendorname)
 333   push @filter,    ('template_name' => { ilike => '%' . $::form->{template} . '%' })  if $::form->{template};
 334   push @filter,    ('reference'     => { ilike => '%' . $::form->{reference} . '%' }) if $::form->{reference};
 335   push @filter_ap, ('vendor.name'   => { ilike => '%' . $::form->{vendor} . '%' })    if $::form->{vendor};
 336   push @filter_ap, @filter;
 337   my $templates_gl = SL::DB::Manager::RecordTemplate->get_all(
 338     query        => [ template_type => 'gl_transaction',
 339                       chart_id      => SL::DB::Manager::BankAccount->find_by(id => $self->transaction->local_bank_account_id)->chart_id,
 340                       (and => \@filter) x !!@filter
 341                     ],
 342     with_objects => [ qw(employee record_template_items) ],
 343   );
 344
 345   my $templates_ap = SL::DB::Manager::RecordTemplate->get_all(
 346     where        => [ template_type => 'ap_transaction', (and => \@filter_ap) x !!@filter_ap ],
 347     with_objects => [ qw(employee vendor) ],
 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 =~ m/ap_transaction|purchase_invoice/) {
 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