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