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