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     with_objects => [ qw(employee record_template_items) ],
 284   );
 285
 286   # pre filter templates_ap, if we have a vendor match (IBAN eq IBAN) - show and allow user to edit this via gui!
 287   $templates_ap = [ grep { $_->vendor_id == $vendor_of_transaction->id } @{ $templates_ap } ] if $use_vendor_filter;
 288
 289   $self->callback($self->url_for(
 290     action                => 'list',
 291     'filter.bank_account' => $::form->{filter}->{bank_account},
 292     'filter.todate'       => $::form->{filter}->{todate},
 293     'filter.fromdate'     => $::form->{filter}->{fromdate},
 294   ));
 295
 296   $self->render(
 297     'bank_transactions/create_invoice',
 298     { layout => 0 },
 299     title        => t8('Create invoice'),
 300     TEMPLATES_GL => $use_vendor_filter && @{ $templates_ap } ? undef : $templates_gl,
 301     TEMPLATES_AP => $templates_ap,
 302     vendor_name  => $use_vendor_filter && @{ $templates_ap } ? $vendor_of_transaction->name : undef,
 303   );
 304 }
 305
 306 sub action_ajax_payment_suggestion {
 307   my ($self) = @_;
 308
 309   # based on a BankTransaction ID and a Invoice or PurchaseInvoice ID passed via $::form,
 310   # create an HTML blob to be used by the js function add_invoices in templates/webpages/bank_transactions/list.html
 311   # and return encoded as JSON
 312
 313   croak("Need bt_id") unless $::form->{bt_id};
 314
 315   my $invoice = SL::DB::Manager::Invoice->find_by( id => $::form->{prop_id} ) || SL::DB::Manager::PurchaseInvoice->find_by( id => $::form->{prop_id} );
 316
 317   croak("No valid invoice found") unless $invoice;
 318
 319   my $html = $self->render(
 320     'bank_transactions/_payment_suggestion', { output => 0 },
 321     bt_id          => $::form->{bt_id},
 322     invoice        => $invoice,
 323   );
 324
 325   $self->render(\ SL::JSON::to_json( { 'html' => "$html" } ), { layout => 0, type => 'json', process => 0 });
 326 };
 327
 328 sub action_filter_templates {
 329   my ($self) = @_;
 330
 331   $self->{transaction}      = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
 332
 333   my (@filter, @filter_ap);
 334
 335   # filter => gl and ap | filter_ap = ap (i.e. vendorname)
 336   push @filter,    ('template_name' => { ilike => '%' . $::form->{template} . '%' })  if $::form->{template};
 337   push @filter,    ('reference'     => { ilike => '%' . $::form->{reference} . '%' }) if $::form->{reference};
 338   push @filter_ap, ('vendor.name'   => { ilike => '%' . $::form->{vendor} . '%' })    if $::form->{vendor};
 339   push @filter_ap, @filter;
 340   my $templates_gl = SL::DB::Manager::RecordTemplate->get_all(
 341     query        => [ template_type => 'gl_transaction',
 342                       chart_id      => SL::DB::Manager::BankAccount->find_by(id => $self->transaction->local_bank_account_id)->chart_id,
 343                       (and => \@filter) x !!@filter
 344                     ],
 345     with_objects => [ qw(employee record_template_items) ],
 346   );
 347
 348   my $templates_ap = SL::DB::Manager::RecordTemplate->get_all(
 349     where        => [ template_type => 'ap_transaction', (and => \@filter_ap) x !!@filter_ap ],
 350     with_objects => [ qw(employee vendor) ],
 351   );
 352   $::form->{filter} //= {};
 353
 354   $self->callback($self->url_for(
 355     action                => 'list',
 356     'filter.bank_account' => $::form->{filter}->{bank_account},
 357     'filter.todate'       => $::form->{filter}->{todate},
 358     'filter.fromdate'     => $::form->{filter}->{fromdate},
 359   ));
 360
 361   my $output  = $self->render(
 362     'bank_transactions/_template_list',
 363     { output => 0 },
 364     TEMPLATES_AP => $templates_ap,
 365     TEMPLATES_GL => $templates_gl,
 366   );
 367
 368   $self->render(\to_json({ html => $output }), { type => 'json', process => 0 });
 369 }
 370
 371 sub action_ajax_add_list {
 372   my ($self) = @_;
 373
 374   my @where_sale     = (amount => { ne => \'paid' });
 375   my @where_purchase = (amount => { ne => \'paid' });
 376
 377   if ($::form->{invnumber}) {
 378     push @where_sale,     (invnumber => { ilike => like($::form->{invnumber})});
 379     push @where_purchase, (invnumber => { ilike => like($::form->{invnumber})});
 380   }
 381
 382   if ($::form->{amount}) {
 383     push @where_sale,     (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
 384     push @where_purchase, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
 385   }
 386
 387   if ($::form->{vcnumber}) {
 388     push @where_sale,     ('customer.customernumber' => { ilike => like($::form->{vcnumber})});
 389     push @where_purchase, ('vendor.vendornumber'     => { ilike => like($::form->{vcnumber})});
 390   }
 391
 392   if ($::form->{vcname}) {
 393     push @where_sale,     ('customer.name' => { ilike => like($::form->{vcname})});
 394     push @where_purchase, ('vendor.name'   => { ilike => like($::form->{vcname})});
 395   }
 396
 397   if ($::form->{transdatefrom}) {
 398     my $fromdate = $::locale->parse_date_to_object($::form->{transdatefrom});
 399     if ( ref($fromdate) eq 'DateTime' ) {
 400       push @where_sale,     ('transdate' => { ge => $fromdate});
 401       push @where_purchase, ('transdate' => { ge => $fromdate});
 402     };
 403   }
 404
 405   if ($::form->{transdateto}) {
 406     my $todate = $::locale->parse_date_to_object($::form->{transdateto});
 407     if ( ref($todate) eq 'DateTime' ) {
 408       $todate->add(days => 1);
 409       push @where_sale,     ('transdate' => { lt => $todate});
 410       push @where_purchase, ('transdate' => { lt => $todate});
 411     };
 412   }
 413
 414   my $all_open_ar_invoices = SL::DB::Manager::Invoice        ->get_all(where => \@where_sale,     with_objects => 'customer');
 415   my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => \@where_purchase, with_objects => 'vendor');
 416
 417   my @all_open_invoices = @{ $all_open_ar_invoices };
 418   # add ap invoices, filtering out subcent open amounts
 419   push @all_open_invoices, grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
 420
 421   @all_open_invoices = sort { $a->id <=> $b->id } @all_open_invoices;
 422
 423   my $output  = $self->render(
 424     'bank_transactions/add_list',
 425     { output => 0 },
 426     INVOICES => \@all_open_invoices,
 427   );
 428
 429   my %result = ( count => 0, html => $output );
 430
 431   $self->render(\to_json(\%result), { type => 'json', process => 0 });
 432 }
 433
 434 sub action_ajax_accept_invoices {
 435   my ($self) = @_;
 436
 437   my @selected_invoices;
 438   foreach my $invoice_id (@{ $::form->{invoice_id} || [] }) {
 439     my $invoice_object = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
 440     push @selected_invoices, $invoice_object;
 441   }
 442
 443   $self->render(
 444     'bank_transactions/invoices',
 445     { layout => 0 },
 446     INVOICES => \@selected_invoices,
 447     bt_id    => $::form->{bt_id},
 448   );
 449 }
 450
 451 sub save_invoices {
 452   my ($self) = @_;
 453
 454   return 0 if !$::form->{invoice_ids};
 455
 456   my %invoice_hash = %{ delete $::form->{invoice_ids} };  # each key (the bt line with a bt_id) contains an array of invoice_ids
 457
 458   # e.g. three partial payments with bt_ids 54, 55 and 56 for invoice with id 74:
 459   # $invoice_hash = {
 460   #         '55' => [
 461   #                 '74'
 462   #               ],
 463   #         '54' => [
 464   #                 '74'
 465   #               ],
 466   #         '56' => [
 467   #                 '74'
 468   #               ]
 469   #       };
 470   #
 471   # or if the payment with bt_id 44 is used to pay invoices with ids 50, 51 and 52
 472   # $invoice_hash = {
 473   #           '44' => [ '50', '51', 52' ]
 474   #         };
 475
 476   $::form->{invoice_skontos} ||= {}; # hash of arrays containing the payment types, could be empty
 477
 478   # a bank_transaction may be assigned to several invoices, i.e. a customer
 479   # might pay several open invoices with one transaction
 480
 481   $self->problems([]);
 482
 483   my $count = 0;
 484
 485   if ( $::form->{proposal_ids} ) {
 486     foreach (@{ $::form->{proposal_ids} }) {
 487       my  $bank_transaction_id = $_;
 488       my  $invoice_ids = $invoice_hash{$_};
 489       push @{ $self->problems }, $self->save_single_bank_transaction(
 490         bank_transaction_id => $bank_transaction_id,
 491         invoice_ids         => $invoice_ids,
 492         sources             => ($::form->{sources} // {})->{$_},
 493         memos               => ($::form->{memos}   // {})->{$_},
 494       );
 495       $count += scalar( @{$invoice_ids} );
 496     }
 497   } else {
 498     while ( my ($bank_transaction_id, $invoice_ids) = each(%invoice_hash) ) {
 499       push @{ $self->problems }, $self->save_single_bank_transaction(
 500         bank_transaction_id => $bank_transaction_id,
 501         invoice_ids         => $invoice_ids,
 502         sources             => [  map { $::form->{"sources_${bank_transaction_id}_${_}"} } @{ $invoice_ids } ],
 503         memos               => [  map { $::form->{"memos_${bank_transaction_id}_${_}"}   } @{ $invoice_ids } ],
 504       );
 505       $count += scalar( @{$invoice_ids} );
 506     }
 507   }
 508   my $max_count = $count;
 509   foreach (@{ $self->problems }) {
 510     $count-- if $_->{result} eq 'error';
 511   }
 512   return ($count, $max_count);
 513 }
 514
 515 sub action_save_invoices {
 516   my ($self) = @_;
 517   my ($success_count, $max_count) = $self->save_invoices();
 518
 519   if ($success_count == $max_count) {
 520     flash('ok', t8('#1 invoice(s) saved.', $success_count));
 521   } else {
 522     flash('error', t8('At least #1 invoice(s) not saved', $max_count - $success_count));
 523   }
 524
 525   $self->action_list();
 526 }
 527
 528 sub action_save_proposals {
 529   my ($self) = @_;
 530
 531   if ( $::form->{proposal_ids} ) {
 532     my $propcount = scalar(@{ $::form->{proposal_ids} });
 533     if ( $propcount > 0 ) {
 534       my $count = $self->save_invoices();
 535
 536       flash('ok', t8('#1 proposal(s) with #2 invoice(s) saved.',  $propcount, $count));
 537     }
 538   }
 539   $self->action_list();
 540
 541 }
 542
 543 sub save_single_bank_transaction {
 544   my ($self, %params) = @_;
 545
 546   my %data = (
 547     %params,
 548     bank_transaction => SL::DB::Manager::BankTransaction->find_by(id => $params{bank_transaction_id}),
 549     invoices         => [],
 550   );
 551
 552   if (!$data{bank_transaction}) {
 553     return {
 554       %data,
 555       result => 'error',
 556       message => $::locale->text('The ID #1 is not a valid database ID.', $data{bank_transaction_id}),
 557     };
 558   }
 559
 560   my $bank_transaction = $data{bank_transaction};
 561
 562   if ($bank_transaction->closed_period) {
 563     return {
 564       %data,
 565       result => 'error',
 566       message => $::locale->text('Cannot post payment for a closed period!'),
 567     };
 568   }
 569   my (@warnings);
 570
 571   my $worker = sub {
 572     my $bt_id                 = $data{bank_transaction_id};
 573     my $sign                  = $bank_transaction->amount < 0 ? -1 : 1;
 574     my $payment_received      = $bank_transaction->amount > 0;
 575     my $payment_sent          = $bank_transaction->amount < 0;
 576
 577
 578     foreach my $invoice_id (@{ $params{invoice_ids} }) {
 579       my $invoice = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
 580       if (!$invoice) {
 581         return {
 582           %data,
 583           result  => 'error',
 584           message => $::locale->text("The ID #1 is not a valid database ID.", $invoice_id),
 585         };
 586       }
 587       push @{ $data{invoices} }, $invoice;
 588     }
 589
 590     if (   $payment_received
 591         && any {    ( $_->is_sales && ($_->amount < 0))
 592                  || (!$_->is_sales && ($_->amount > 0))
 593                } @{ $data{invoices} }) {
 594       return {
 595         %data,
 596         result  => 'error',
 597         message => $::locale->text("Received payments can only be posted for sales invoices and purchase credit notes."),
 598       };
 599     }
 600
 601     if (   $payment_sent
 602         && any {    ( $_->is_sales && ($_->amount > 0))
 603                  || (!$_->is_sales && ($_->amount < 0) && ($_->invoice_type eq 'purchase_invoice'))
 604                } @{ $data{invoices} }) {
 605       return {
 606         %data,
 607         result  => 'error',
 608         message => $::locale->text("Sent payments can only be posted for purchase invoices and sales credit notes."),
 609       };
 610     }
 611
 612     my $max_invoices = scalar(@{ $data{invoices} });
 613     my $n_invoices   = 0;
 614
 615     foreach my $invoice (@{ $data{invoices} }) {
 616       my $source = ($data{sources} // [])->[$n_invoices];
 617       my $memo   = ($data{memos}   // [])->[$n_invoices];
 618
 619       $n_invoices++ ;
 620       # safety check invoice open
 621       croak("Invoice closed. Cannot proceed.") unless ($invoice->open_amount);
 622
 623       if (   ($payment_sent     && $bank_transaction->not_assigned_amount >= 0)
 624           || ($payment_received && $bank_transaction->not_assigned_amount <= 0)) {
 625         return {
 626           %data,
 627           result  => 'error',
 628           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."),
 629         };
 630       }
 631
 632       my ($payment_type, $free_skonto_amount);
 633       if ( defined $::form->{invoice_skontos}->{"$bt_id"} ) {
 634         $payment_type = shift(@{ $::form->{invoice_skontos}->{"$bt_id"} });
 635       } else {
 636         $payment_type = 'without_skonto';
 637       }
 638
 639       if ($payment_type eq 'free_skonto') {
 640         # parse user input > 0
 641         if ($::form->parse_amount(\%::myconfig, $::form->{"free_skonto_amount"}->{"$bt_id"}{$invoice->id}) > 0) {
 642           $free_skonto_amount = $::form->parse_amount(\%::myconfig, $::form->{"free_skonto_amount"}->{"$bt_id"}{$invoice->id});
 643         } else {
 644           return {
 645             %data,
 646             result  => 'error',
 647             message => $::locale->text("Free skonto amount has to be a positive number."),
 648           };
 649         }
 650       }
 651     # pay invoice
 652     # TODO rewrite this: really booked amount should be a return value of Payment.pm
 653     # also this controller shouldnt care about how to calc skonto. we simply delegate the
 654     # payment_type to the helper and get the corresponding bank_transaction values back
 655     # hotfix to get the signs right - compare absolute values and later set the signs
 656     # should be better done elsewhere - changing not_assigned_amount to abs feels seriously bogus
 657
 658     my $open_amount = $payment_type eq 'with_skonto_pt' ? $invoice->amount_less_skonto : $invoice->open_amount;
 659     $open_amount            = abs($open_amount);
 660     $open_amount           -= $free_skonto_amount if ($payment_type eq 'free_skonto');
 661     my $not_assigned_amount = abs($bank_transaction->not_assigned_amount);
 662     my $amount_for_booking  = ($open_amount < $not_assigned_amount) ? $open_amount : $not_assigned_amount;
 663     my $amount_for_payment  = $amount_for_booking;
 664
 665     # get the right direction for the payment bookings (all amounts < 0 are stornos, credit notes or negative ap)
 666     $amount_for_payment *= -1 if $invoice->amount < 0;
 667     $free_skonto_amount *= -1 if ($free_skonto_amount && $invoice->amount < 0);
 668     # get the right direction for the bank transaction
 669     $amount_for_booking *= $sign;
 670
 671     $bank_transaction->invoice_amount($bank_transaction->invoice_amount + $amount_for_booking);
 672
 673     # ... and then pay the invoice
 674     my @acc_ids = $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
 675                           trans_id      => $invoice->id,
 676                           amount        => $amount_for_payment,
 677                           payment_type  => $payment_type,
 678                           source        => $source,
 679                           memo          => $memo,
 680                           skonto_amount => $free_skonto_amount,
 681                           transdate     => $bank_transaction->valutadate->to_kivitendo);
 682     # ... and record the origin via BankTransactionAccTrans
 683     if (scalar(@acc_ids) < 2) {
 684       return {
 685         %data,
 686         result  => 'error',
 687         message => $::locale->text("Unable to book transactions for bank purpose #1", $bank_transaction->purpose),
 688       };
 689     }
 690     foreach my $acc_trans_id (@acc_ids) {
 691         my $id_type = $invoice->is_sales ? 'ar' : 'ap';
 692         my  %props_acc = (
 693           acc_trans_id        => $acc_trans_id,
 694           bank_transaction_id => $bank_transaction->id,
 695           $id_type            => $invoice->id,
 696         );
 697         SL::DB::BankTransactionAccTrans->new(%props_acc)->save;
 698     }
 699       # Record a record link from the bank transaction to the invoice
 700       my %props = (
 701         from_table => 'bank_transactions',
 702         from_id    => $bt_id,
 703         to_table   => $invoice->is_sales ? 'ar' : 'ap',
 704         to_id      => $invoice->id,
 705       );
 706       SL::DB::RecordLink->new(%props)->save;
 707
 708       # "close" a sepa_export_item if it exists
 709       # code duplicated in action_save_proposals!
 710       # currently only works, if there is only exactly one open sepa_export_item
 711       if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
 712         if ( scalar @$seis == 1 ) {
 713           # moved the execution and the check for sepa_export into a method,
 714           # this isn't part of a transaction, though
 715           $seis->[0]->set_executed if $invoice->id == $seis->[0]->arap_id;
 716         }
 717       }
 718
 719     }
 720     $bank_transaction->save;
 721
 722     # 'undef' means 'no error' here.
 723     return undef;
 724   };
 725
 726   my $error;
 727   my $rez = $data{bank_transaction}->db->with_transaction(sub {
 728     eval {
 729       $error = $worker->();
 730       1;
 731
 732     } or do {
 733       $error = {
 734         %data,
 735         result  => 'error',
 736         message => $@,
 737       };
 738     };
 739
 740     # Rollback Fehler nicht weiterreichen
 741     # die if $error;
 742     # aber einen rollback von hand
 743     $::lxdebug->message(LXDebug->DEBUG2(),"finish worker with ". ($error ? $error->{result} : '-'));
 744     $data{bank_transaction}->db->dbh->rollback if $error && $error->{result} eq 'error';
 745   });
 746
 747   return grep { $_ } ($error, @warnings);
 748 }
 749 sub action_unlink_bank_transaction {
 750   my ($self, %params) = @_;
 751
 752   croak("No bank transaction ids") unless scalar @{ $::form->{ids}} > 0;
 753
 754   my $success_count;
 755
 756   foreach my $bt_id (@{ $::form->{ids}} )  {
 757
 758     my $bank_transaction = SL::DB::Manager::BankTransaction->find_by(id => $bt_id);
 759     croak("No valid bank transaction found") unless (ref($bank_transaction)  eq 'SL::DB::BankTransaction');
 760     croak t8('Cannot unlink payment for a closed period!') if $bank_transaction->closed_period;
 761
 762     # everything in one transaction
 763     my $rez = $bank_transaction->db->with_transaction(sub {
 764       # 1. remove all reconciliations (due to underlying trigger, this has to be the first step)
 765       my $rec_links = SL::DB::Manager::ReconciliationLink->get_all(where => [ bank_transaction_id => $bt_id ]);
 766       $_->delete for @{ $rec_links };
 767
 768       my %trans_ids;
 769       foreach my $acc_trans_id_entry (@{ SL::DB::Manager::BankTransactionAccTrans->get_all(where => [bank_transaction_id => $bt_id ] )}) {
 770
 771         my $acc_trans = SL::DB::Manager::AccTransaction->get_all(where => [acc_trans_id => $acc_trans_id_entry->acc_trans_id]);
 772
 773         # save trans_id and type
 774         die "no type" unless ($acc_trans_id_entry->ar_id || $acc_trans_id_entry->ap_id || $acc_trans_id_entry->gl_id);
 775         $trans_ids{$acc_trans_id_entry->ar_id} = 'ar' if $acc_trans_id_entry->ar_id;
 776         $trans_ids{$acc_trans_id_entry->ap_id} = 'ap' if $acc_trans_id_entry->ap_id;
 777         $trans_ids{$acc_trans_id_entry->gl_id} = 'gl' if $acc_trans_id_entry->gl_id;
 778         # 2. all good -> ready to delete acc_trans and bt_acc link
 779         $acc_trans_id_entry->delete;
 780         $_->delete for @{ $acc_trans };
 781       }
 782       # 3. update arap.paid (may not be 0, yet)
 783       #    or in case of gl, delete whole entry
 784       while (my ($trans_id, $type) = each %trans_ids) {
 785         if ($type eq 'gl') {
 786           SL::DB::Manager::GLTransaction->delete_all(where => [ id => $trans_id ]);
 787           next;
 788         }
 789         die ("invalid type") unless $type =~ m/^(ar|ap)$/;
 790
 791         # recalc and set paid via database query
 792         my $query = qq|UPDATE $type SET paid =
 793                         (SELECT COALESCE(abs(sum(amount)),0) FROM acc_trans
 794                          WHERE trans_id = ?
 795                          AND chart_link ilike '%paid%')
 796                        WHERE id = ?|;
 797
 798         die if (do_query($::form, $bank_transaction->db->dbh, $query, $trans_id, $trans_id) == -1);
 799       }
 800       # 4. and delete all (if any) record links
 801       my $rl = SL::DB::Manager::RecordLink->delete_all(where => [ from_id => $bt_id, from_table => 'bank_transactions' ]);
 802
 803       # 5. finally reset  this bank transaction
 804       $bank_transaction->invoice_amount(0);
 805       $bank_transaction->cleared(0);
 806       $bank_transaction->save;
 807       # 6. and add a log entry in history_erp
 808       SL::DB::History->new(
 809         trans_id    => $bank_transaction->id,
 810         snumbers    => 'bank_transaction_unlink_' . $bank_transaction->id,
 811         employee_id => SL::DB::Manager::Employee->current->id,
 812         what_done   => 'bank_transaction',
 813         addition    => 'UNLINKED',
 814       )->save();
 815
 816       1;
 817
 818     }) || die t8('error while unlinking payment #1 : ', $bank_transaction->purpose) . $bank_transaction->db->error . "\n";
 819
 820     $success_count++;
 821   }
 822
 823   flash('ok', t8('#1 bank transaction bookings undone.', $success_count));
 824   $self->action_list_all() unless $params{testcase};
 825 }
 826 #
 827 # filters
 828 #
 829
 830 sub check_auth {
 831   $::auth->assert('bank_transaction');
 832 }
 833
 834 #
 835 # helpers
 836 #
 837
 838 sub make_filter_summary {
 839   my ($self) = @_;
 840
 841   my $filter = $::form->{filter} || {};
 842   my @filter_strings;
 843
 844   my @filters = (
 845     [ $filter->{"transdate:date::ge"},      $::locale->text('Transdate')  . " " . $::locale->text('From Date') ],
 846     [ $filter->{"transdate:date::le"},      $::locale->text('Transdate')  . " " . $::locale->text('To Date')   ],
 847     [ $filter->{"valutadate:date::ge"},     $::locale->text('Valutadate') . " " . $::locale->text('From Date') ],
 848     [ $filter->{"valutadate:date::le"},     $::locale->text('Valutadate') . " " . $::locale->text('To Date')   ],
 849     [ $filter->{"amount:number"},           $::locale->text('Amount')                                          ],
 850     [ $filter->{"bank_account_id:integer"}, $::locale->text('Local bank account')                              ],
 851     [ $filter->{"remote_name:substr::ilike"}, $::locale->text('Remote name')                                   ],
 852   );
 853
 854   for (@filters) {
 855     push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
 856   }
 857
 858   $self->{filter_summary} = join ', ', @filter_strings;
 859 }
 860
 861 sub prepare_report {
 862   my ($self)      = @_;
 863
 864   my $callback    = $self->models->get_callback;
 865
 866   my $report      = SL::ReportGenerator->new(\%::myconfig, $::form);
 867   $self->{report} = $report;
 868
 869   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);
 870   my @sortable    = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount                                  purpose local_account_number local_bank_code);
 871
 872   my %column_defs = (
 873     ids                 => { raw_header_data => checkbox_tag("", id => "check_all", checkall  => "[data-checkall=1]"),
 874                              'align'         => 'center',
 875                              raw_data        => sub { if (@{ $_[0]->linked_invoices }) {
 876                                                         if ($_[0]->closed_period) {
 877                                                           html_tag('text', "X"); #, tooltip => t8('Bank Transaction is in a closed period.')),
 878                                                         } else {
 879                                                           checkbox_tag("ids[]", value => $_[0]->id, "data-checkall" => 1);
 880                                                         }
 881                                                 } } },
 882     transdate             => { sub   => sub { $_[0]->transdate_as_date } },
 883     valutadate            => { sub   => sub { $_[0]->valutadate_as_date } },
 884     remote_name           => { },
 885     remote_account_number => { },
 886     remote_bank_code      => { },
 887     amount                => { sub   => sub { $_[0]->amount_as_number },
 888                                align => 'right' },
 889     invoice_amount        => { sub   => sub { $_[0]->invoice_amount_as_number },
 890                                align => 'right' },
 891     invoices              => { sub   => sub { my @invnumbers; for my $obj (@{ $_[0]->linked_invoices }) {
 892                                                                 next unless $obj; push @invnumbers, $obj->invnumber } return \@invnumbers } },
 893     currency              => { sub   => sub { $_[0]->currency->name } },
 894     purpose               => { },
 895     local_account_number  => { sub   => sub { $_[0]->local_bank_account->account_number } },
 896     local_bank_code       => { sub   => sub { $_[0]->local_bank_account->bank_code } },
 897     local_bank_name       => { sub   => sub { $_[0]->local_bank_account->name } },
 898     id                    => {},
 899   );
 900
 901   map { $column_defs{$_}->{text} ||= $::locale->text( $self->models->get_sort_spec->{$_}->{title} ) } keys %column_defs;
 902
 903   $report->set_options(
 904     std_column_visibility => 1,
 905     controller_class      => 'BankTransaction',
 906     output_format         => 'HTML',
 907     top_info_text         => $::locale->text('Bank transactions'),
 908     title                 => $::locale->text('Bank transactions'),
 909     allow_pdf_export      => 1,
 910     allow_csv_export      => 1,
 911   );
 912   $report->set_columns(%column_defs);
 913   $report->set_column_order(@columns);
 914   $report->set_export_options(qw(list_all filter));
 915   $report->set_options_from_form;
 916   $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
 917   $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
 918
 919   my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted();
 920
 921   $report->set_options(
 922     raw_top_info_text     => $self->render('bank_transactions/report_top',    { output => 0 }, BANK_ACCOUNTS => $bank_accounts),
 923     raw_bottom_info_text  => $self->render('bank_transactions/report_bottom', { output => 0 }),
 924   );
 925 }
 926
 927 sub init_problems { [] }
 928
 929 sub init_models {
 930   my ($self) = @_;
 931
 932   SL::Controller::Helper::GetModels->new(
 933     controller => $self,
 934     sorted     => {
 935       _default => {
 936         by  => 'transdate',
 937         dir => 0,   # 1 = ASC, 0 = DESC : default sort is newest at top
 938       },
 939       id                    => t8('ID'),
 940       transdate             => t8('Transdate'),
 941       remote_name           => t8('Remote name'),
 942       amount                => t8('Amount'),
 943       invoice_amount        => t8('Assigned'),
 944       invoices              => t8('Linked invoices'),
 945       valutadate            => t8('Valutadate'),
 946       remote_account_number => t8('Remote account number'),
 947       remote_bank_code      => t8('Remote bank code'),
 948       currency              => t8('Currency'),
 949       purpose               => t8('Purpose'),
 950       local_account_number  => t8('Local account number'),
 951       local_bank_code       => t8('Local bank code'),
 952       local_bank_name       => t8('Bank account'),
 953     },
 954     with_objects => [ 'local_bank_account', 'currency' ],
 955   );
 956 }
 957
 958 sub load_ap_record_template_url {
 959   my ($self, $template) = @_;
 960
 961   return $self->url_for(
 962     controller                           => 'ap.pl',
 963     action                               => 'load_record_template',
 964     id                                   => $template->id,
 965     'form_defaults.amount_1'             => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
 966     'form_defaults.transdate'            => $self->transaction->transdate_as_date,
 967     'form_defaults.duedate'              => $self->transaction->transdate_as_date,
 968     'form_defaults.no_payment_bookings'  => 1,
 969     'form_defaults.paid_1_suggestion'    => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
 970     'form_defaults.AP_paid_1_suggestion' => $self->transaction->local_bank_account->chart->accno,
 971     'form_defaults.callback'             => $self->callback,
 972   );
 973 }
 974
 975 sub load_gl_record_template_url {
 976   my ($self, $template) = @_;
 977
 978   return $self->url_for(
 979     controller                           => 'gl.pl',
 980     action                               => 'load_record_template',
 981     id                                   => $template->id,
 982     'form_defaults.amount_1'             => abs($self->transaction->not_assigned_amount), # always positive
 983     'form_defaults.transdate'            => $self->transaction->transdate_as_date,
 984     'form_defaults.callback'             => $self->callback,
 985     'form_defaults.bt_id'                => $self->transaction->id,
 986     'form_defaults.bt_chart_id'          => $self->transaction->local_bank_account->chart->id,
 987     'form_defaults.description'          => $self->transaction->purpose,
 988   );
 989 }
 990
 991 sub setup_search_action_bar {
 992   my ($self, %params) = @_;
 993
 994   for my $bar ($::request->layout->get('actionbar')) {
 995     $bar->add(
 996       action => [
 997         t8('Filter'),
 998         submit    => [ '#search_form', { action => 'BankTransaction/list' } ],
 999         accesskey => 'enter',
1000       ],
1001     );
1002   }
1003 }
1004
1005 sub setup_list_all_action_bar {
1006   my ($self, %params) = @_;
1007
1008   for my $bar ($::request->layout->get('actionbar')) {
1009     $bar->add(
1010       combobox => [
1011         action => [ t8('Actions') ],
1012         action => [
1013           t8('Unlink bank transactions'),
1014             submit => [ '#form', { action => 'BankTransaction/unlink_bank_transaction' } ],
1015             checks => [ [ 'kivi.check_if_entries_selected', '[name="ids[]"]' ] ],
1016             disabled  => $::instance_conf->get_payments_changeable ? t8('Cannot safely unlink bank transactions, please set the posting configuration for payments to unchangeable.') : undef,
1017           ],
1018         ],
1019         action => [
1020           t8('Filter'),
1021           submit    => [ '#filter_form', { action => 'BankTransaction/list_all' } ],
1022         accesskey => 'enter',
1023       ],
1024     );
1025   }
1026 }
1027
1028 1;
1029 __END__
1030
1031 =pod
1032
1033 =encoding utf8
1034
1035 =head1 NAME
1036
1037 SL::Controller::BankTransaction - Posting payments to invoices from
1038 bank transactions imported earlier
1039
1040 =head1 FUNCTIONS
1041
1042 =over 4
1043
1044 =item C<save_single_bank_transaction %params>
1045
1046 Takes a bank transaction ID (as parameter C<bank_transaction_id> and
1047 tries to post its amount to a certain number of invoices (parameter
1048 C<invoice_ids>, an array ref of database IDs to purchase or sales
1049 invoice objects).
1050
1051 This method handles already partly assigned bank transactions.
1052
1053 This method cannot handle already partly assigned bank transactions, i.e.
1054 a bank transaction that has a invoice_amount <> 0 but not the fully
1055 transaction amount (invoice_amount == amount).
1056
1057 If the amount of the bank transaction is higher than the sum of
1058 the assigned invoices (1 .. n) the bank transaction will only be
1059 partly assigned.
1060
1061 The whole function is wrapped in a database transaction. If an
1062 exception occurs the bank transaction is not posted at all. The same
1063 is true if the code detects an error during the execution, e.g. a bank
1064 transaction that's already been posted earlier. In both cases the
1065 database transaction will be rolled back.
1066
1067 If warnings but not errors occur the database transaction is still
1068 committed.
1069
1070 The return value is an error object or C<undef> if the function
1071 succeeded. The calling function will collect all warnings and errors
1072 and display them in a nicely formatted table if any occurred.
1073
1074 An error object is a hash reference containing the following members:
1075
1076 =over 2
1077
1078 =item * C<result> — can be either C<warning> or C<error>. Warnings are
1079 displayed slightly different than errors.
1080
1081 =item * C<message> — a human-readable message included in the list of
1082 errors meant as the description of why the problem happened
1083
1084 =item * C<bank_transaction_id>, C<invoice_ids> — the same parameters
1085 that the function was called with
1086
1087 =item * C<bank_transaction> — the database object
1088 (C<SL::DB::BankTransaction>) corresponding to C<bank_transaction_id>
1089
1090 =item * C<invoices> — an array ref of the database objects (either
1091 C<SL::DB::Invoice> or C<SL::DB::PurchaseInvoice>) corresponding to
1092 C<invoice_ids>
1093
1094 =back
1095
1096 =item C<action_unlink_bank_transaction>
1097
1098 Takes one or more bank transaction ID (as parameter C<form::ids>) and
1099 tries to revert all payment bookings including already cleared bookings.
1100
1101 This method won't undo payments that are in a closed period and assumes
1102 that payments are not manually changed, i.e. only imported payments.
1103
1104 GL-records will be deleted completely if a bank transaction was the source.
1105
1106 TODO: we still rely on linked_records for the check boxes
1107
1108 =back
1109
1110 =head1 AUTHOR
1111
1112 Niclas Zimmermann E<lt>niclas@kivitendo-premium.deE<gt>,
1113 Geoffrey Richardson E<lt>information@richardson-bueren.deE<gt>
1114
1115 =cut