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