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