SL/Controller/BankTransaction.pm

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