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