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   # see pod
 562   if (@{ $bank_transaction->linked_invoices } || $bank_transaction->invoice_amount != 0) {
 563         return {
 564           %data,
 565           result  => 'error',
 566           message => $::locale->text("Bank transaction with id #1 has already been linked to one or more record and/or some amount is already assigned.", $bank_transaction->id),
 567         };
 568       }
 569   my (@warnings);
 570
 571   my $worker = sub {
 572     my $bt_id                 = $data{bank_transaction_id};
 573     my $sign                  = $bank_transaction->amount < 0 ? -1 : 1;
 574     my $amount_of_transaction = $sign * $bank_transaction->amount;
 575     my $payment_received      = $bank_transaction->amount > 0;
 576     my $payment_sent          = $bank_transaction->amount < 0;
 577
 578
 579     foreach my $invoice_id (@{ $params{invoice_ids} }) {
 580       my $invoice = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
 581       if (!$invoice) {
 582         return {
 583           %data,
 584           result  => 'error',
 585           message => $::locale->text("The ID #1 is not a valid database ID.", $invoice_id),
 586         };
 587       }
 588       push @{ $data{invoices} }, $invoice;
 589     }
 590
 591     if (   $payment_received
 592         && any {    ( $_->is_sales && ($_->amount < 0))
 593                  || (!$_->is_sales && ($_->amount > 0))
 594                } @{ $data{invoices} }) {
 595       return {
 596         %data,
 597         result  => 'error',
 598         message => $::locale->text("Received payments can only be posted for sales invoices and purchase credit notes."),
 599       };
 600     }
 601
 602     if (   $payment_sent
 603         && any {    ( $_->is_sales && ($_->amount > 0))
 604                  || (!$_->is_sales && ($_->amount < 0) && ($_->invoice_type eq 'purchase_invoice'))
 605                } @{ $data{invoices} }) {
 606       return {
 607         %data,
 608         result  => 'error',
 609         message => $::locale->text("Sent payments can only be posted for purchase invoices and sales credit notes."),
 610       };
 611     }
 612
 613     my $max_invoices = scalar(@{ $data{invoices} });
 614     my $n_invoices   = 0;
 615
 616     foreach my $invoice (@{ $data{invoices} }) {
 617       my $source = ($data{sources} // [])->[$n_invoices];
 618       my $memo   = ($data{memos}   // [])->[$n_invoices];
 619
 620       $n_invoices++ ;
 621
 622
 623       if (!$amount_of_transaction && $invoice->open_amount) {
 624         return {
 625           %data,
 626           result  => 'error',
 627           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."),
 628         };
 629       }
 630
 631       my $payment_type;
 632       if ( defined $::form->{invoice_skontos}->{"$bt_id"} ) {
 633         $payment_type = shift(@{ $::form->{invoice_skontos}->{"$bt_id"} });
 634       } else {
 635         $payment_type = 'without_skonto';
 636       };
 637
 638
 639       # pay invoice or go to the next bank transaction if the amount is not sufficiently high
 640       if ($invoice->open_amount <= $amount_of_transaction && $n_invoices < $max_invoices) {
 641         my $open_amount = ($payment_type eq 'with_skonto_pt'?$invoice->amount_less_skonto:$invoice->open_amount);
 642         # first calculate new bank transaction amount ...
 643         if ($invoice->is_sales) {
 644           $amount_of_transaction -= $sign * $open_amount;
 645           $bank_transaction->invoice_amount($bank_transaction->invoice_amount + $open_amount);
 646         } else {
 647           $amount_of_transaction += $sign * $open_amount;
 648           $bank_transaction->invoice_amount($bank_transaction->invoice_amount - $open_amount);
 649         }
 650         # ... and then pay the invoice
 651         my @acc_ids = $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
 652                               trans_id     => $invoice->id,
 653                               amount       => $open_amount,
 654                               payment_type => $payment_type,
 655                               source       => $source,
 656                               memo         => $memo,
 657                               transdate    => $bank_transaction->transdate->to_kivitendo);
 658         # ... and record the origin via BankTransactionAccTrans
 659         if (scalar(@acc_ids) != 2) {
 660           return {
 661             %data,
 662             result  => 'error',
 663             message => $::locale->text("Unable to book transactions for bank purpose #1", $bank_transaction->purpose),
 664           };
 665         }
 666         foreach my $acc_trans_id (@acc_ids) {
 667             my $id_type = $invoice->is_sales ? 'ar' : 'ap';
 668             my  %props_acc = (
 669               acc_trans_id        => $acc_trans_id,
 670               bank_transaction_id => $bank_transaction->id,
 671               $id_type            => $invoice->id,
 672             );
 673             SL::DB::BankTransactionAccTrans->new(%props_acc)->save;
 674         }
 675
 676
 677       } else {
 678       # use the whole amount of the bank transaction for the invoice, overpay the invoice if necessary
 679
 680         # $invoice->open_amount     is negative for credit_notes
 681         # $bank_transaction->amount is negative for outgoing transactions
 682         # so $amount_of_transaction is negative but needs positive
 683         # $invoice->open_amount may be negative for ap_transaction but may be positiv for negative ap_transaction
 684         # if $invoice->open_amount is negative $bank_transaction->amount is positve
 685         # if $invoice->open_amount is positive $bank_transaction->amount is negative
 686         # but amount of transaction is for both positive
 687
 688         $amount_of_transaction *= -1 if ($invoice->amount < 0);
 689
 690         # if we have a skonto case - the last invoice needs skonto
 691         $amount_of_transaction = $invoice->amount_less_skonto if ($payment_type eq 'with_skonto_pt');
 692
 693
 694         my $overpaid_amount = $amount_of_transaction - $invoice->open_amount;
 695         $invoice->pay_invoice(chart_id     => $bank_transaction->local_bank_account->chart_id,
 696                               trans_id     => $invoice->id,
 697                               amount       => $amount_of_transaction,
 698                               payment_type => $payment_type,
 699                               source       => $source,
 700                               memo         => $memo,
 701                               transdate    => $bank_transaction->transdate->to_kivitendo);
 702         $bank_transaction->invoice_amount($bank_transaction->amount);
 703         $amount_of_transaction = 0;
 704
 705         if ($overpaid_amount >= 0.01) {
 706           push @warnings, {
 707             %data,
 708             result  => 'warning',
 709             message => $::locale->text('Invoice #1 was overpaid by #2.', $invoice->invnumber, $::form->format_amount(\%::myconfig, $overpaid_amount, 2)),
 710           };
 711         }
 712       }
 713       # Record a record link from the bank transaction to the invoice
 714       my %props = (
 715         from_table => 'bank_transactions',
 716         from_id    => $bt_id,
 717         to_table   => $invoice->is_sales ? 'ar' : 'ap',
 718         to_id      => $invoice->id,
 719       );
 720       SL::DB::RecordLink->new(%props)->save;
 721
 722       # "close" a sepa_export_item if it exists
 723       # code duplicated in action_save_proposals!
 724       # currently only works, if there is only exactly one open sepa_export_item
 725       if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
 726         if ( scalar @$seis == 1 ) {
 727           # moved the execution and the check for sepa_export into a method,
 728           # this isn't part of a transaction, though
 729           $seis->[0]->set_executed if $invoice->id == $seis->[0]->arap_id;
 730         }
 731       }
 732
 733     }
 734     $bank_transaction->save;
 735
 736     # 'undef' means 'no error' here.
 737     return undef;
 738   };
 739
 740   my $error;
 741   my $rez = $data{bank_transaction}->db->with_transaction(sub {
 742     eval {
 743       $error = $worker->();
 744       1;
 745
 746     } or do {
 747       $error = {
 748         %data,
 749         result  => 'error',
 750         message => $@,
 751       };
 752     };
 753
 754     # Rollback Fehler nicht weiterreichen
 755     # die if $error;
 756     # aber einen rollback von hand
 757     $::lxdebug->message(LXDebug->DEBUG2(),"finish worker with ". ($error ? $error->{result} : '-'));
 758     $data{bank_transaction}->db->dbh->rollback if $error && $error->{result} eq 'error';
 759   });
 760
 761   return grep { $_ } ($error, @warnings);
 762 }
 763
 764 #
 765 # filters
 766 #
 767
 768 sub check_auth {
 769   $::auth->assert('bank_transaction');
 770 }
 771
 772 #
 773 # helpers
 774 #
 775
 776 sub make_filter_summary {
 777   my ($self) = @_;
 778
 779   my $filter = $::form->{filter} || {};
 780   my @filter_strings;
 781
 782   my @filters = (
 783     [ $filter->{"transdate:date::ge"},      $::locale->text('Transdate')  . " " . $::locale->text('From Date') ],
 784     [ $filter->{"transdate:date::le"},      $::locale->text('Transdate')  . " " . $::locale->text('To Date')   ],
 785     [ $filter->{"valutadate:date::ge"},     $::locale->text('Valutadate') . " " . $::locale->text('From Date') ],
 786     [ $filter->{"valutadate:date::le"},     $::locale->text('Valutadate') . " " . $::locale->text('To Date')   ],
 787     [ $filter->{"amount:number"},           $::locale->text('Amount')                                          ],
 788     [ $filter->{"bank_account_id:integer"}, $::locale->text('Local bank account')                              ],
 789   );
 790
 791   for (@filters) {
 792     push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
 793   }
 794
 795   $self->{filter_summary} = join ', ', @filter_strings;
 796 }
 797
 798 sub prepare_report {
 799   my ($self)      = @_;
 800
 801   my $callback    = $self->models->get_callback;
 802
 803   my $report      = SL::ReportGenerator->new(\%::myconfig, $::form);
 804   $self->{report} = $report;
 805
 806   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);
 807   my @sortable    = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount                                  purpose local_account_number local_bank_code);
 808
 809   my %column_defs = (
 810     transdate             => { sub   => sub { $_[0]->transdate_as_date } },
 811     valutadate            => { sub   => sub { $_[0]->valutadate_as_date } },
 812     remote_name           => { },
 813     remote_account_number => { },
 814     remote_bank_code      => { },
 815     amount                => { sub   => sub { $_[0]->amount_as_number },
 816                                align => 'right' },
 817     invoice_amount        => { sub   => sub { $_[0]->invoice_amount_as_number },
 818                                align => 'right' },
 819     invoices              => { sub   => sub { my @invnumbers; for my $obj (@{ $_[0]->linked_invoices }) {
 820                                                                 next unless $obj; push @invnumbers, $obj->invnumber } return \@invnumbers } },
 821     currency              => { sub   => sub { $_[0]->currency->name } },
 822     purpose               => { },
 823     local_account_number  => { sub   => sub { $_[0]->local_bank_account->account_number } },
 824     local_bank_code       => { sub   => sub { $_[0]->local_bank_account->bank_code } },
 825     local_bank_name       => { sub   => sub { $_[0]->local_bank_account->name } },
 826     id                    => {},
 827   );
 828
 829   map { $column_defs{$_}->{text} ||= $::locale->text( $self->models->get_sort_spec->{$_}->{title} ) } keys %column_defs;
 830
 831   $report->set_options(
 832     std_column_visibility => 1,
 833     controller_class      => 'BankTransaction',
 834     output_format         => 'HTML',
 835     top_info_text         => $::locale->text('Bank transactions'),
 836     title                 => $::locale->text('Bank transactions'),
 837     allow_pdf_export      => 1,
 838     allow_csv_export      => 1,
 839   );
 840   $report->set_columns(%column_defs);
 841   $report->set_column_order(@columns);
 842   $report->set_export_options(qw(list_all filter));
 843   $report->set_options_from_form;
 844   $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
 845   $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
 846
 847   my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted();
 848
 849   $report->set_options(
 850     raw_top_info_text     => $self->render('bank_transactions/report_top',    { output => 0 }, BANK_ACCOUNTS => $bank_accounts),
 851     raw_bottom_info_text  => $self->render('bank_transactions/report_bottom', { output => 0 }),
 852   );
 853 }
 854
 855 sub init_problems { [] }
 856
 857 sub init_models {
 858   my ($self) = @_;
 859
 860   SL::Controller::Helper::GetModels->new(
 861     controller => $self,
 862     sorted     => {
 863       _default => {
 864         by  => 'transdate',
 865         dir => 0,   # 1 = ASC, 0 = DESC : default sort is newest at top
 866       },
 867       transdate             => t8('Transdate'),
 868       remote_name           => t8('Remote name'),
 869       amount                => t8('Amount'),
 870       invoice_amount        => t8('Assigned'),
 871       invoices              => t8('Linked invoices'),
 872       valutadate            => t8('Valutadate'),
 873       remote_account_number => t8('Remote account number'),
 874       remote_bank_code      => t8('Remote bank code'),
 875       currency              => t8('Currency'),
 876       purpose               => t8('Purpose'),
 877       local_account_number  => t8('Local account number'),
 878       local_bank_code       => t8('Local bank code'),
 879       local_bank_name       => t8('Bank account'),
 880     },
 881     with_objects => [ 'local_bank_account', 'currency' ],
 882   );
 883 }
 884
 885 sub load_ap_record_template_url {
 886   my ($self, $template) = @_;
 887
 888   return $self->url_for(
 889     controller                           => 'ap.pl',
 890     action                               => 'load_record_template',
 891     id                                   => $template->id,
 892     'form_defaults.amount_1'             => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
 893     'form_defaults.transdate'            => $self->transaction->transdate_as_date,
 894     'form_defaults.duedate'              => $self->transaction->transdate_as_date,
 895     'form_defaults.no_payment_bookings'  => 1,
 896     'form_defaults.paid_1_suggestion'    => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
 897     'form_defaults.AP_paid_1_suggestion' => $self->transaction->local_bank_account->chart->accno,
 898     'form_defaults.callback'             => $self->callback,
 899   );
 900 }
 901
 902 sub load_gl_record_template_url {
 903   my ($self, $template) = @_;
 904
 905   return $self->url_for(
 906     controller                           => 'gl.pl',
 907     action                               => 'load_record_template',
 908     id                                   => $template->id,
 909     'form_defaults.amount_1'             => abs($self->transaction->amount), # always positive
 910     'form_defaults.transdate'            => $self->transaction->transdate_as_date,
 911     'form_defaults.callback'             => $self->callback,
 912     'form_defaults.bt_id'                => $self->transaction->id,
 913     'form_defaults.bt_chart_id'          => $self->transaction->local_bank_account->chart->id,
 914   );
 915 }
 916
 917 sub setup_search_action_bar {
 918   my ($self, %params) = @_;
 919
 920   for my $bar ($::request->layout->get('actionbar')) {
 921     $bar->add(
 922       action => [
 923         t8('Filter'),
 924         submit    => [ '#search_form', { action => 'BankTransaction/list' } ],
 925         accesskey => 'enter',
 926       ],
 927     );
 928   }
 929 }
 930
 931 sub setup_list_all_action_bar {
 932   my ($self, %params) = @_;
 933
 934   for my $bar ($::request->layout->get('actionbar')) {
 935     $bar->add(
 936       action => [
 937         t8('Filter'),
 938         submit    => [ '#filter_form', { action => 'BankTransaction/list_all' } ],
 939         accesskey => 'enter',
 940       ],
 941     );
 942   }
 943 }
 944
 945 1;
 946 __END__
 947
 948 =pod
 949
 950 =encoding utf8
 951
 952 =head1 NAME
 953
 954 SL::Controller::BankTransaction - Posting payments to invoices from
 955 bank transactions imported earlier
 956
 957 =head1 FUNCTIONS
 958
 959 =over 4
 960
 961 =item C<save_single_bank_transaction %params>
 962
 963 Takes a bank transaction ID (as parameter C<bank_transaction_id> and
 964 tries to post its amount to a certain number of invoices (parameter
 965 C<invoice_ids>, an array ref of database IDs to purchase or sales
 966 invoice objects).
 967
 968 This method cannot handle already partly assigned bank transactions, i.e.
 969 a bank transaction that has a invoice_amount <> 0 but not the fully
 970 transaction amount (invoice_amount == amount).
 971
 972 If the amount of the bank transaction is higher than the sum of
 973 the assigned invoices (1 .. n) the last invoice will be overpayed.
 974
 975 The whole function is wrapped in a database transaction. If an
 976 exception occurs the bank transaction is not posted at all. The same
 977 is true if the code detects an error during the execution, e.g. a bank
 978 transaction that's already been posted earlier. In both cases the
 979 database transaction will be rolled back.
 980
 981 If warnings but not errors occur the database transaction is still
 982 committed.
 983
 984 The return value is an error object or C<undef> if the function
 985 succeeded. The calling function will collect all warnings and errors
 986 and display them in a nicely formatted table if any occurred.
 987
 988 An error object is a hash reference containing the following members:
 989
 990 =over 2
 991
 992 =item * C<result> — can be either C<warning> or C<error>. Warnings are
 993 displayed slightly different than errors.
 994
 995 =item * C<message> — a human-readable message included in the list of
 996 errors meant as the description of why the problem happened
 997
 998 =item * C<bank_transaction_id>, C<invoice_ids> — the same parameters
 999 that the function was called with
1000
1001 =item * C<bank_transaction> — the database object
1002 (C<SL::DB::BankTransaction>) corresponding to C<bank_transaction_id>
1003
1004 =item * C<invoices> — an array ref of the database objects (either
1005 C<SL::DB::Invoice> or C<SL::DB::PurchaseInvoice>) corresponding to
1006 C<invoice_ids>
1007
1008 =back
1009
1010 =back
1011
1012 =head1 AUTHOR
1013
1014 Niclas Zimmermann E<lt>niclas@kivitendo-premium.deE<gt>,
1015 Geoffrey Richardson E<lt>information@richardson-bueren.deE<gt>
1016
1017 =cut