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