SL/Controller/BankTransaction.pm

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