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