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