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