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