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