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