SL/Controller/BankTransaction.pm

   1 package SL::Controller::BankTransaction;
   2
   3 # idee- möglichkeit bankdaten zu übernehmen in stammdaten
   4 # erst Kontenabgleich, um alle gl-Einträge wegzuhaben
   5 use strict;
   6
   7 use parent qw(SL::Controller::Base);
   8
   9 use SL::Controller::Helper::GetModels;
  10 use SL::Controller::Helper::ReportGenerator;
  11 use SL::ReportGenerator;
  12
  13 use SL::DB::BankTransaction;
  14 use SL::Helper::Flash;
  15 use SL::Locale::String;
  16 use SL::SEPA;
  17 use SL::DB::Invoice;
  18 use SL::DB::PurchaseInvoice;
  19 use SL::DB::RecordLink;
  20 use SL::JSON;
  21 use SL::DB::Chart;
  22 use SL::DB::AccTransaction;
  23 use SL::DB::Tax;
  24 use SL::DB::Draft;
  25 use SL::DB::BankAccount;
  26 use SL::DB::SepaExportItem;
  27 use SL::DBUtils qw(like);
  28 use SL::Presenter;
  29
  30 use List::MoreUtils qw(any);
  31 use List::Util qw(max);
  32
  33 use Rose::Object::MakeMethods::Generic
  34 (
  35   'scalar --get_set_init' => [ qw(models problems) ],
  36 );
  37
  38 __PACKAGE__->run_before('check_auth');
  39
  40
  41 #
  42 # actions
  43 #
  44
  45 sub action_search {
  46   my ($self) = @_;
  47
  48   my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted( query => [ obsolete => 0 ] );
  49
  50   $self->render('bank_transactions/search',
  51                  BANK_ACCOUNTS => $bank_accounts);
  52 }
  53
  54 sub action_list_all {
  55   my ($self) = @_;
  56
  57   $self->make_filter_summary;
  58   $self->prepare_report;
  59
  60   $self->report_generator_list_objects(report => $self->{report}, objects => $self->models->get);
  61 }
  62
  63 sub action_list {
  64   my ($self) = @_;
  65
  66   if (!$::form->{filter}{bank_account}) {
  67     flash('error', t8('No bank account chosen!'));
  68     $self->action_search;
  69     return;
  70   }
  71
  72   my $sort_by = $::form->{sort_by} || 'transdate';
  73   $sort_by = 'transdate' if $sort_by eq 'proposal';
  74   $sort_by .= $::form->{sort_dir} ? ' DESC' : ' ASC';
  75
  76   my $fromdate = $::locale->parse_date_to_object($::form->{filter}->{fromdate});
  77   my $todate   = $::locale->parse_date_to_object($::form->{filter}->{todate});
  78   $todate->add( days => 1 ) if $todate;
  79
  80   my @where = ();
  81   push @where, (transdate => { ge => $fromdate }) if ($fromdate);
  82   push @where, (transdate => { lt => $todate })   if ($todate);
  83   my $bank_account = SL::DB::Manager::BankAccount->find_by( id => $::form->{filter}{bank_account} );
  84   # bank_transactions no younger than starting date,
  85   # including starting date (same search behaviour as fromdate)
  86   # but OPEN invoices to be matched may be from before
  87   if ( $bank_account->reconciliation_starting_date ) {
  88     push @where, (transdate => { ge => $bank_account->reconciliation_starting_date });
  89   };
  90
  91   my $bank_transactions = SL::DB::Manager::BankTransaction->get_all(
  92     with_objects => [ 'local_bank_account', 'currency' ],
  93     sort_by      => $sort_by,
  94     limit        => 10000,
  95     where        => [
  96       amount                => {ne => \'invoice_amount'},
  97       local_bank_account_id => $::form->{filter}{bank_account},
  98       @where
  99     ],
 100   );
 101   $main::lxdebug->message(LXDebug->DEBUG2(),"count bt=".scalar(@{$bank_transactions}." bank_account=".$bank_account->id." chart=".$bank_account->chart_id));
 102
 103   my $all_open_ar_invoices = SL::DB::Manager::Invoice        ->get_all(where => [amount => { ne => \'paid' }], with_objects => ['customer','payment_terms']);
 104   my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => [amount => { ne => \'paid' }], with_objects => ['vendor'  ,'payment_terms']);
 105   my $all_open_sepa_export_items = SL::DB::Manager::SepaExportItem->get_all(where => [chart_id => $bank_account->chart_id ,
 106                                                                              'sepa_export.executed' => 0, 'sepa_export.closed' => 0 ], with_objects => ['sepa_export']);
 107   $main::lxdebug->message(LXDebug->DEBUG2(),"count sepaexport=".scalar(@{$all_open_sepa_export_items}));
 108
 109   my @all_open_invoices;
 110   # filter out invoices with less than 1 cent outstanding
 111   push @all_open_invoices, map { $_->{is_ar}=1 ; $_ } grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ar_invoices };
 112   push @all_open_invoices, map { $_->{is_ar}=0 ; $_ } grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
 113   $main::lxdebug->message(LXDebug->DEBUG2(),"bank_account=".$::form->{filter}{bank_account}." invoices: ".scalar(@{ $all_open_ar_invoices }).
 114                               " + ".scalar(@{ $all_open_ap_invoices })." non fully paid=".scalar(@all_open_invoices)." transactions=".scalar(@{ $bank_transactions }));
 115
 116   my @all_sepa_invoices;
 117   my @all_non_sepa_invoices;
 118   my %sepa_exports;
 119   # first collect sepa export items to open invoices
 120   foreach my $open_invoice (@all_open_invoices){
 121     #    my @items =  grep { $_->ap_id == $open_invoice->id ||  $_->ar_id == $open_invoice->id } @{$all_open_sepa_export_items};
 122     $open_invoice->{realamount}  = $::form->format_amount(\%::myconfig,$open_invoice->amount,2);
 123     $open_invoice->{skonto_type} = 'without_skonto';
 124     foreach ( @{$all_open_sepa_export_items}) {
 125       if ( $_->ap_id == $open_invoice->id ||  $_->ar_id == $open_invoice->id ) {
 126         my $factor = ($_->ar_id == $open_invoice->id?1:-1);
 127         $main::lxdebug->message(LXDebug->DEBUG2(),"exitem=".$_->id." for invoice ".$open_invoice->id." factor=".$factor);
 128         $open_invoice->{realamount}  = $::form->format_amount(\%::myconfig,$open_invoice->amount*$factor,2);
 129         $open_invoice->{sepa_export_item} = $_ ;
 130         $open_invoice->{skonto_type} = $_->payment_type;
 131         $sepa_exports{$_->sepa_export_id} ||= { count => 0, is_ar => 0, amount => 0, proposed => 0, invoices => [], item => $_ };
 132         $sepa_exports{$_->sepa_export_id}->{count}++ ;
 133         $sepa_exports{$_->sepa_export_id}->{is_ar}++ if  $_->ar_id == $open_invoice->id;
 134         $sepa_exports{$_->sepa_export_id}->{amount} += $_->amount * $factor;
 135         push @{ $sepa_exports{$_->sepa_export_id}->{invoices} }, $open_invoice;
 136         #$main::lxdebug->message(LXDebug->DEBUG2(),"amount for export id ".$_->sepa_export_id." = ".
 137         #                          $sepa_exports{$_->sepa_export_id}->{amount}." count = ".
 138         #                          $sepa_exports{$_->sepa_export_id}->{count}." is_ar = ".
 139         #                          $sepa_exports{$_->sepa_export_id}->{is_ar} );
 140         push @all_sepa_invoices , $open_invoice;
 141       }
 142     }
 143     push @all_non_sepa_invoices , $open_invoice if ! $open_invoice->{sepa_export_item};
 144   }
 145
 146   # try to match each bank_transaction with each of the possible open invoices
 147   # by awarding points
 148   my @proposals;
 149
 150   foreach my $bt (@{ $bank_transactions }) {
 151     ## 5 Stellen hinter dem Komma auf 2 Stellen reduzieren
 152     $bt->amount($bt->amount*1);
 153     $bt->invoice_amount($bt->invoice_amount*1);
 154     $main::lxdebug->message(LXDebug->DEBUG2(),"BT ".$bt->id." amount=".$bt->amount." invoice_amount=".$bt->invoice_amount." remote=". $bt->{remote_name});
 155
 156     $bt->{proposals}    = [];
 157     $bt->{rule_matches} = [];
 158
 159     $bt->{remote_name} .= $bt->{remote_name_1} if $bt->{remote_name_1};
 160
 161     if ( $self->is_collective_transaction($bt) ) {
 162       foreach ( keys  %sepa_exports) {
 163         #$main::lxdebug->message(LXDebug->DEBUG2(),"Exp ID=".$_." compare sum amount ".($sepa_exports{$_}->{amount} *1) ." == ".($bt->amount * 1));
 164         if ( $bt->transaction_code eq '191' && abs(($sepa_exports{$_}->{amount} * 1) - ($bt->amount * 1)) < 0.01 ) {
 165           ## jupp
 166           @{$bt->{proposals}} = @{$sepa_exports{$_}->{invoices}};
 167           $bt->{agreement}    = 20;
 168           push(@{$bt->{rule_matches}},'sepa_export_item(20)');
 169           $sepa_exports{$_}->{proposed}=1;
 170           #$main::lxdebug->message(LXDebug->DEBUG2(),"has ".scalar($bt->{proposals})." invoices");
 171           push(@proposals, $bt);
 172           next;
 173         }
 174       }
 175     }
 176     next unless $bt->{remote_name};  # bank has no name, usually fees, use create invoice to assign
 177
 178     foreach ( @{$all_open_sepa_export_items}) {
 179       last if scalar (@all_sepa_invoices) == 0;
 180       foreach my $open_invoice (@all_sepa_invoices){
 181         if ( $_->ap_id == $open_invoice->id ||  $_->ar_id == $open_invoice->id ) {
 182           #$main::lxdebug->message(LXDebug->DEBUG2(),"exitem2=".$_->id." for invoice ".$open_invoice->id);
 183           my $factor = ( $_->ar_id == $open_invoice->id?1:-1);
 184           $_->amount($_->amount*1);
 185           #$main::lxdebug->message(LXDebug->DEBUG2(),"remote account '".$bt->{remote_account_number}."' bt_amount=".$bt->amount." factor=".$factor);
 186           #$main::lxdebug->message(LXDebug->DEBUG2(),"compare with   '".$_->vc_iban."'    amount=".$_->amount);
 187           if ( $bt->{remote_account_number} eq $_->vc_iban && abs(abs($_->amount) - abs($bt->amount)) < 0.01 ) {
 188             my $iban;
 189             $iban = $open_invoice->customer->iban if $open_invoice->is_sales;
 190             $iban = $open_invoice->vendor->iban   if ! $open_invoice->is_sales;
 191             if($bt->{remote_account_number} eq $iban && abs(abs($open_invoice->amount) - abs($bt->amount)) < 0.01 ) {
 192               ($open_invoice->{agreement}, $open_invoice->{rule_matches}) = $bt->get_agreement_with_invoice($open_invoice);
 193               $open_invoice->{agreement} += 5;
 194               $open_invoice->{rule_matches} .= 'sepa_export_item(5) ';
 195               $main::lxdebug->message(LXDebug->DEBUG2(),"sepa invoice_id=".$open_invoice->id." agreement=".$open_invoice->{agreement}." rules matches=".$open_invoice->{rule_matches});
 196               $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount*$factor,2);
 197             }
 198           }
 199         }
 200       }
 201     }
 202
 203     # try to match the current $bt to each of the open_invoices, saving the
 204     # results of get_agreement_with_invoice in $open_invoice->{agreement} and
 205     # $open_invoice->{rule_matches}.
 206
 207     # The values are overwritten each time a new bt is checked, so at the end
 208     # of each bt the likely results are filtered and those values are stored in
 209     # the arrays $bt->{proposals} and $bt->{rule_matches}, and the agreement
 210     # score is stored in $bt->{agreement}
 211
 212     foreach my $open_invoice (@all_non_sepa_invoices){
 213       ($open_invoice->{agreement}, $open_invoice->{rule_matches}) = $bt->get_agreement_with_invoice($open_invoice);
 214       $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount*($open_invoice->{is_ar}?1:-1),2);
 215       $main::lxdebug->message(LXDebug->DEBUG2(),"nons invoice_id=".$open_invoice->id." amount=".$open_invoice->amount." agreement=".$open_invoice->{agreement}." rules matches=".$open_invoice->{rule_matches}) if $open_invoice->{agreement} > 2;
 216     };
 217
 218     my $agreement = 15;
 219     my $min_agreement = 3; # suggestions must have at least this score
 220
 221     my $max_agreement = max map { $_->{agreement} } @all_open_invoices;
 222
 223     # add open_invoices with highest agreement into array $bt->{proposals}
 224     if ( $max_agreement >= $min_agreement ) {
 225       $bt->{proposals} = [ grep { $_->{agreement} == $max_agreement } @all_open_invoices ];
 226       $bt->{agreement} = $max_agreement; #scalar @{ $bt->{proposals} } ? $agreement + 1 : '';
 227
 228       # store the rule_matches in a separate array, so they can be displayed in template
 229       foreach ( @{ $bt->{proposals} } ) {
 230         push(@{$bt->{rule_matches}}, $_->{rule_matches});
 231       };
 232     };
 233   }  # finished one bt
 234   # finished all bt
 235
 236   # separate filter for proposals (second tab, agreement >= 5 and exactly one match)
 237   # to qualify as a proposal there has to be
 238   # * agreement >= 5  TODO: make threshold configurable in configuration
 239   # * there must be only one exact match
 240   # * depending on whether sales or purchase the amount has to have the correct sign (so Gutschriften don't work?)
 241   my $proposal_threshold = 5;
 242   my @otherproposals = grep {
 243        ($_->{agreement} >= $proposal_threshold)
 244     && (1 == scalar @{ $_->{proposals} })
 245     && (@{ $_->{proposals} }[0]->is_sales ? abs(@{ $_->{proposals} }[0]->amount - $_->amount) < 0.01
 246                                           : abs(@{ $_->{proposals} }[0]->amount + $_->amount) < 0.01)
 247   } @{ $bank_transactions };
 248
 249   push ( @proposals, @otherproposals);
 250
 251   # sort bank transaction proposals by quality (score) of proposal
 252   $bank_transactions = [ sort { $a->{agreement} <=> $b->{agreement} } @{ $bank_transactions } ] if $::form->{sort_by} eq 'proposal' and $::form->{sort_dir} == 1;
 253   $bank_transactions = [ sort { $b->{agreement} <=> $a->{agreement} } @{ $bank_transactions } ] if $::form->{sort_by} eq 'proposal' and $::form->{sort_dir} == 0;
 254
 255   $::request->layout->add_javascripts("kivi.BankTransaction.js");
 256   $self->render('bank_transactions/list',
 257                 title             => t8('Bank transactions MT940'),
 258                 BANK_TRANSACTIONS => $bank_transactions,
 259                 PROPOSALS         => \@proposals,
 260                 bank_account      => $bank_account,
 261                 ui_tab            => scalar(@proposals) > 0?1:0,
 262               );
 263 }
 264
 265 sub action_assign_invoice {
 266   my ($self) = @_;
 267
 268   $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
 269
 270   $self->render('bank_transactions/assign_invoice',
 271                 { layout => 0 },
 272                 title => t8('Assign invoice'),);
 273 }
 274
 275 sub action_create_invoice {
 276   my ($self) = @_;
 277   my %myconfig = %main::myconfig;
 278
 279   $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
 280   my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(account_number => $self->{transaction}->{remote_account_number});
 281
 282   my $use_vendor_filter = $self->{transaction}->{remote_account_number} && $vendor_of_transaction;
 283
 284   my $drafts = SL::DB::Manager::Draft->get_all(where => [ module => 'ap'] , with_objects => 'employee');
 285
 286   my @filtered_drafts;
 287
 288   foreach my $draft ( @{ $drafts } ) {
 289     my $draft_as_object = YAML::Load($draft->form);
 290     my $vendor = SL::DB::Manager::Vendor->find_by(id => $draft_as_object->{vendor_id});
 291     $draft->{vendor} = $vendor->name;
 292     $draft->{vendor_id} = $vendor->id;
 293     push @filtered_drafts, $draft;
 294   }
 295
 296   #Filter drafts
 297   @filtered_drafts = grep { $_->{vendor_id} == $vendor_of_transaction->id } @filtered_drafts if $use_vendor_filter;
 298
 299   my $all_vendors = SL::DB::Manager::Vendor->get_all();
 300   my $callback    = $self->url_for(action                => 'list',
 301                                    'filter.bank_account' => $::form->{filter}->{bank_account},
 302                                    'filter.todate'       => $::form->{filter}->{todate},
 303                                    'filter.fromdate'     => $::form->{filter}->{fromdate});
 304
 305   $self->render(
 306     'bank_transactions/create_invoice',
 307     { layout => 0 },
 308     title       => t8('Create invoice'),
 309     DRAFTS      => \@filtered_drafts,
 310     vendor_id   => $use_vendor_filter ? $vendor_of_transaction->id   : undef,
 311     vendor_name => $use_vendor_filter ? $vendor_of_transaction->name : undef,
 312     ALL_VENDORS => $all_vendors,
 313     callback    => $callback,
 314   );
 315 }
 316
 317 sub action_ajax_payment_suggestion {
 318   my ($self) = @_;
 319
 320   # based on a BankTransaction ID and a Invoice or PurchaseInvoice ID passed via $::form,
 321   # create an HTML blob to be used by the js function add_invoices in templates/webpages/bank_transactions/list.html
 322   # and return encoded as JSON
 323
 324   my $bt      = SL::DB::Manager::BankTransaction->find_by( id => $::form->{bt_id} );
 325   my $invoice = SL::DB::Manager::Invoice->find_by( id => $::form->{prop_id} ) || SL::DB::Manager::PurchaseInvoice->find_by( id => $::form->{prop_id} );
 326
 327   die unless $bt and $invoice;
 328
 329   my @select_options = $invoice->get_payment_select_options_for_bank_transaction($::form->{bt_id});
 330
 331   my $html;
 332   $html .= SL::Presenter->input_tag('invoice_ids.' . $::form->{bt_id} . '[]', $::form->{prop_id} , type => 'hidden');
 333   $html .= SL::Presenter->escape(t8('Invno.')      . ': ' . $invoice->invnumber . ' ');
 334   $html .= SL::Presenter->escape(t8('Open amount') . ': ' . $::form->format_amount(\%::myconfig, $invoice->open_amount, 2) . ' ');
 335   $html .= SL::Presenter->select_tag('invoice_skontos.' . $::form->{bt_id} . '[]',
 336                                      \@select_options,
 337                                      value_key => 'payment_type',
 338                                      title_key => 'display' )
 339     if @select_options;
 340   $html .= SL::Presenter->html_tag('a', 'x', href => '#', onclick => "kivi.BankTransaction.delete_invoice(" . $::form->{bt_id} . ',' . $::form->{prop_id} . ")");
 341   $html = SL::Presenter->html_tag('div', $html, id => $::form->{bt_id} . '.' . $::form->{prop_id}, 'data-invoice-amount' => $invoice->open_amount * 1);
 342
 343   $self->render(\ SL::JSON::to_json( { 'html' => $html } ), { layout => 0, type => 'json', process => 0 });
 344 };
 345
 346 sub action_filter_drafts {
 347   my ($self) = @_;
 348
 349   $self->{transaction}      = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
 350   my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(account_number => $self->{transaction}->{remote_account_number});
 351
 352   my $drafts                = SL::DB::Manager::Draft->get_all(with_objects => 'employee');
 353
 354   my @filtered_drafts;
 355
 356   foreach my $draft ( @{ $drafts } ) {
 357     my $draft_as_object = YAML::Load($draft->form);
 358     next unless $draft_as_object->{vendor_id};  # we cannot filter for vendor name, if this is a gl draft
 359
 360     my $vendor          = SL::DB::Manager::Vendor->find_by(id => $draft_as_object->{vendor_id});
 361     $draft->{vendor}    = $vendor->name;
 362     $draft->{vendor_id} = $vendor->id;
 363
 364     push @filtered_drafts, $draft;
 365   }
 366
 367   my $vendor_name = $::form->{vendor};
 368   my $vendor_id   = $::form->{vendor_id};
 369
 370   #Filter drafts
 371   @filtered_drafts = grep { $_->{vendor_id} == $vendor_id      } @filtered_drafts if $vendor_id;
 372   @filtered_drafts = grep { $_->{vendor}    =~ /$vendor_name/i } @filtered_drafts if $vendor_name;
 373
 374   my $output  = $self->render(
 375     'bank_transactions/filter_drafts',
 376     { output => 0 },
 377     DRAFTS => \@filtered_drafts,
 378   );
 379
 380   my %result = ( count => 0, html => $output );
 381
 382   $self->render(\to_json(\%result), { type => 'json', process => 0 });
 383 }
 384
 385 sub action_ajax_add_list {
 386   my ($self) = @_;
 387
 388   my @where_sale     = (amount => { ne => \'paid' });
 389   my @where_purchase = (amount => { ne => \'paid' });
 390
 391   if ($::form->{invnumber}) {
 392     push @where_sale,     (invnumber => { ilike => like($::form->{invnumber})});
 393     push @where_purchase, (invnumber => { ilike => like($::form->{invnumber})});
 394   }
 395
 396   if ($::form->{amount}) {
 397     push @where_sale,     (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
 398     push @where_purchase, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
 399   }
 400
 401   if ($::form->{vcnumber}) {
 402     push @where_sale,     ('customer.customernumber' => { ilike => like($::form->{vcnumber})});
 403     push @where_purchase, ('vendor.vendornumber'     => { ilike => like($::form->{vcnumber})});
 404   }
 405
 406   if ($::form->{vcname}) {
 407     push @where_sale,     ('customer.name' => { ilike => like($::form->{vcname})});
 408     push @where_purchase, ('vendor.name'   => { ilike => like($::form->{vcname})});
 409   }
 410
 411   if ($::form->{transdatefrom}) {
 412     my $fromdate = $::locale->parse_date_to_object($::form->{transdatefrom});
 413     if ( ref($fromdate) eq 'DateTime' ) {
 414       push @where_sale,     ('transdate' => { ge => $fromdate});
 415       push @where_purchase, ('transdate' => { ge => $fromdate});
 416     };
 417   }
 418
 419   if ($::form->{transdateto}) {
 420     my $todate = $::locale->parse_date_to_object($::form->{transdateto});
 421     if ( ref($todate) eq 'DateTime' ) {
 422       $todate->add(days => 1);
 423       push @where_sale,     ('transdate' => { lt => $todate});
 424       push @where_purchase, ('transdate' => { lt => $todate});
 425     };
 426   }
 427
 428   my $all_open_ar_invoices = SL::DB::Manager::Invoice        ->get_all(where => \@where_sale,     with_objects => 'customer');
 429   my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => \@where_purchase, with_objects => 'vendor');
 430
 431   my @all_open_invoices = @{ $all_open_ar_invoices };
 432   # add ap invoices, filtering out subcent open amounts
 433   push @all_open_invoices, grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
 434
 435   @all_open_invoices = sort { $a->id <=> $b->id } @all_open_invoices;
 436
 437   my $output  = $self->render(
 438     'bank_transactions/add_list',
 439     { output => 0 },
 440     INVOICES => \@all_open_invoices,
 441   );
 442
 443   my %result = ( count => 0, html => $output );
 444
 445   $self->render(\to_json(\%result), { type => 'json', process => 0 });
 446 }
 447
 448 sub action_ajax_accept_invoices {
 449   my ($self) = @_;
 450
 451   my @selected_invoices;
 452   foreach my $invoice_id (@{ $::form->{invoice_id} || [] }) {
 453     my $invoice_object = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
 454     push @selected_invoices, $invoice_object;
 455   }
 456
 457   $self->render(
 458     'bank_transactions/invoices',
 459     { layout => 0 },
 460     INVOICES => \@selected_invoices,
 461     bt_id    => $::form->{bt_id},
 462   );
 463 }
 464
 465 sub save_invoices {
 466   my ($self) = @_;
 467
 468   return 0 if !$::form->{invoice_ids};
 469
 470   my %invoice_hash = %{ delete $::form->{invoice_ids} };  # each key (the bt line with a bt_id) contains an array of invoice_ids
 471
 472   # e.g. three partial payments with bt_ids 54, 55 and 56 for invoice with id 74:
 473   # $invoice_hash = {
 474   #         '55' => [
 475   #                 '74'
 476   #               ],
 477   #         '54' => [
 478   #                 '74'
 479   #               ],
 480   #         '56' => [
 481   #                 '74'
 482   #               ]
 483   #       };
 484   #
 485   # or if the payment with bt_id 44 is used to pay invoices with ids 50, 51 and 52
 486   # $invoice_hash = {
 487   #           '44' => [ '50', '51', 52' ]
 488   #         };
 489
 490   $::form->{invoice_skontos} ||= {}; # hash of arrays containing the payment types, could be empty
 491
 492   # a bank_transaction may be assigned to several invoices, i.e. a customer
 493   # might pay several open invoices with one transaction
 494
 495   $self->problems([]);
 496
 497   my $count = 0;
 498
 499   if ( $::form->{proposal_ids} ) {
 500     foreach (@{ $::form->{proposal_ids} }) {
 501       my  $bank_transaction_id = $_;
 502       my  $invoice_ids = $invoice_hash{$_};
 503       push @{ $self->problems }, $self->save_single_bank_transaction(
 504         bank_transaction_id => $bank_transaction_id,
 505         invoice_ids         => $invoice_ids,
 506       );
 507       $count += scalar( @{$invoice_ids} );
 508     }
 509   } else {
 510     while ( my ($bank_transaction_id, $invoice_ids) = each(%invoice_hash) ) {
 511       push @{ $self->problems }, $self->save_single_bank_transaction(
 512         bank_transaction_id => $bank_transaction_id,
 513         invoice_ids         => $invoice_ids,
 514       );
 515       $count += scalar( @{$invoice_ids} );
 516     }
 517   }
 518   return $count;
 519 }
 520
 521 sub action_save_invoices {
 522   my ($self) = @_;
 523   my $count = $self->save_invoices();
 524
 525   flash('ok', t8('#1 invoice(s) saved.', $count));
 526
 527   $self->action_list();
 528 }
 529
 530 sub action_save_proposals {
 531   my ($self) = @_;
 532   if ( $::form->{proposal_ids} ) {
 533     my $propcount = scalar(@{ $::form->{proposal_ids} });
 534     if ( $propcount > 0 ) {
 535       my $count = $self->save_invoices();
 536
 537       flash('ok', t8('#1 proposal(s) with #2 invoice(s) saved.',  $propcount, $count));
 538     }
 539   }
 540   $self->action_list();
 541
 542 }
 543
 544 sub is_collective_transaction {
 545   my ($self, $bt) = @_;
 546   return $bt->transaction_code eq "191";
 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 (@warnings);
 567
 568   my $worker = sub {
 569     my $bt_id                 = $data{bank_transaction_id};
 570     my $bank_transaction      = $data{bank_transaction};
 571     my $sign                  = $bank_transaction->amount < 0 ? -1 : 1;
 572     my $amount_of_transaction = $sign * $bank_transaction->amount;
 573     my $payment_received      = $bank_transaction->amount > 0;
 574     my $payment_sent          = $bank_transaction->amount < 0;
 575
 576
 577     foreach my $invoice_id (@{ $params{invoice_ids} }) {
 578       my $invoice = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
 579       if (!$invoice) {
 580         return {
 581           %data,
 582           result  => 'error',
 583           message => $::locale->text("The ID #1 is not a valid database ID.", $invoice_id),
 584         };
 585       }
 586       push @{ $data{invoices} }, $invoice;
 587     }
 588
 589     if (   $payment_received
 590         && any {    ( $_->is_sales && ($_->amount < 0))
 591                  || (!$_->is_sales && ($_->amount > 0))
 592                } @{ $data{invoices} }) {
 593       return {
 594         %data,
 595         result  => 'error',
 596         message => $::locale->text("Received payments can only be posted for sales invoices and purchase credit notes."),
 597       };
 598     }
 599
 600     if (   $payment_sent
 601         && any {    ( $_->is_sales && ($_->amount > 0))
 602                  || (!$_->is_sales && ($_->amount < 0))
 603                } @{ $data{invoices} }) {
 604       return {
 605         %data,
 606         result  => 'error',
 607         message => $::locale->text("Sent payments can only be posted for purchase invoices and sales credit notes."),
 608       };
 609     }
 610
 611     my $max_invoices = scalar(@{ $data{invoices} });
 612     my $n_invoices   = 0;
 613
 614     foreach my $invoice (@{ $data{invoices} }) {
 615
 616       $n_invoices++ ;
 617
 618       # Check if bank_transaction already has a link to the invoice, may only be linked once per invoice
 619       # This might be caused by the user reloading a page and resending the form
 620       if (_existing_record_link($bank_transaction, $invoice)) {
 621         return {
 622           %data,
 623           result  => 'error',
 624           message => $::locale->text("Bank transaction with id #1 has already been linked to #2.", $bank_transaction->id, $invoice->displayable_name),
 625         };
 626       }
 627
 628       if (!$amount_of_transaction && $invoice->open_amount) {
 629         return {
 630           %data,
 631           result  => 'error',
 632           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."),
 633         };
 634       }
 635
 636       my $payment_type;
 637       if ( defined $::form->{invoice_skontos}->{"$bt_id"} ) {
 638         $payment_type = shift(@{ $::form->{invoice_skontos}->{"$bt_id"} });
 639       } else {
 640         $payment_type = 'without_skonto';
 641       };
 642
 643
 644       # pay invoice or go to the next bank transaction if the amount is not sufficiently high
 645       if ($invoice->open_amount <= $amount_of_transaction && $n_invoices < $max_invoices) {
 646         my $open_amount = ($payment_type eq 'with_skonto_pt'?$invoice->amount_less_skonto:$invoice->open_amount);
 647         # first calculate new bank transaction amount ...
 648         if ($invoice->is_sales) {
 649           $amount_of_transaction -= $sign * $open_amount;
 650           $bank_transaction->invoice_amount($bank_transaction->invoice_amount + $open_amount);
 651         } else {
 652           $amount_of_transaction += $sign * $open_amount;
 653           $bank_transaction->invoice_amount($bank_transaction->invoice_amount - $open_amount);
 654         }
 655         # ... and then pay the invoice
 656         $invoice->pay_invoice(chart_id     => $bank_transaction->local_bank_account->chart_id,
 657                               trans_id     => $invoice->id,
 658                               amount       => $open_amount,
 659                               payment_type => $payment_type,
 660                               transdate    => $bank_transaction->transdate->to_kivitendo);
 661       } else { # use the whole amount of the bank transaction for the invoice, overpay the invoice if necessary
 662         if ( $invoice->is_sales && $invoice->invoice_type eq 'credit_note' ) {
 663           $amount_of_transaction *= -1;
 664         }
 665         my $overpaid_amount = $amount_of_transaction - $invoice->open_amount;
 666         $invoice->pay_invoice(chart_id     => $bank_transaction->local_bank_account->chart_id,
 667                               trans_id     => $invoice->id,
 668                               amount       => $amount_of_transaction,
 669                               payment_type => $payment_type,
 670                               transdate    => $bank_transaction->transdate->to_kivitendo);
 671         $bank_transaction->invoice_amount($bank_transaction->amount);
 672         $amount_of_transaction = 0;
 673
 674         if ($overpaid_amount >= 0.01) {
 675           push @warnings, {
 676             %data,
 677             result  => 'warning',
 678             message => $::locale->text('Invoice #1 was overpaid by #2.', $invoice->invnumber, $::form->format_amount(\%::myconfig, $overpaid_amount, 2)),
 679           };
 680         }
 681       }
 682       # Record a record link from the bank transaction to the invoice
 683       my @props = (
 684         from_table => 'bank_transactions',
 685         from_id    => $bt_id,
 686         to_table   => $invoice->is_sales ? 'ar' : 'ap',
 687         to_id      => $invoice->id,
 688       );
 689
 690       SL::DB::RecordLink->new(@props)->save;
 691
 692       # "close" a sepa_export_item if it exists
 693       # code duplicated in action_save_proposals!
 694       # currently only works, if there is only exactly one open sepa_export_item
 695       if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
 696         if ( scalar @$seis == 1 ) {
 697           # moved the execution and the check for sepa_export into a method,
 698           # this isn't part of a transaction, though
 699           $seis->[0]->set_executed if $invoice->id == $seis->[0]->arap_id;
 700         }
 701       }
 702
 703     }
 704     $bank_transaction->save;
 705
 706     # 'undef' means 'no error' here.
 707     return undef;
 708   };
 709
 710   my $error;
 711   my $rez = $data{bank_transaction}->db->with_transaction(sub {
 712     eval {
 713       $error = $worker->();
 714       1;
 715
 716     } or do {
 717       $error = {
 718         %data,
 719         result  => 'error',
 720         message => $@,
 721       };
 722     };
 723
 724     die if $error;
 725   });
 726
 727   return grep { $_ } ($error, @warnings);
 728 }
 729
 730 #
 731 # filters
 732 #
 733
 734 sub check_auth {
 735   $::auth->assert('bank_transaction');
 736 }
 737
 738 #
 739 # helpers
 740 #
 741
 742 sub make_filter_summary {
 743   my ($self) = @_;
 744
 745   my $filter = $::form->{filter} || {};
 746   my @filter_strings;
 747
 748   my @filters = (
 749     [ $filter->{"transdate:date::ge"},      $::locale->text('Transdate')  . " " . $::locale->text('From Date') ],
 750     [ $filter->{"transdate:date::le"},      $::locale->text('Transdate')  . " " . $::locale->text('To Date')   ],
 751     [ $filter->{"valutadate:date::ge"},     $::locale->text('Valutadate') . " " . $::locale->text('From Date') ],
 752     [ $filter->{"valutadate:date::le"},     $::locale->text('Valutadate') . " " . $::locale->text('To Date')   ],
 753     [ $filter->{"amount:number"},           $::locale->text('Amount')                                          ],
 754     [ $filter->{"bank_account_id:integer"}, $::locale->text('Local bank account')                              ],
 755   );
 756
 757   for (@filters) {
 758     push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
 759   }
 760
 761   $self->{filter_summary} = join ', ', @filter_strings;
 762 }
 763
 764 sub prepare_report {
 765   my ($self)      = @_;
 766
 767   my $callback    = $self->models->get_callback;
 768
 769   my $report      = SL::ReportGenerator->new(\%::myconfig, $::form);
 770   $self->{report} = $report;
 771
 772   my @columns     = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount invoice_amount invoices currency purpose local_account_number local_bank_code id);
 773   my @sortable    = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount                                  purpose local_account_number local_bank_code);
 774
 775   my %column_defs = (
 776     transdate             => { sub   => sub { $_[0]->transdate_as_date } },
 777     valutadate            => { sub   => sub { $_[0]->valutadate_as_date } },
 778     remote_name           => { },
 779     remote_account_number => { },
 780     remote_bank_code      => { },
 781     amount                => { sub   => sub { $_[0]->amount_as_number },
 782                                align => 'right' },
 783     invoice_amount        => { sub   => sub { $_[0]->invoice_amount_as_number },
 784                                align => 'right' },
 785     invoices              => { sub   => sub { $_[0]->linked_invoices } },
 786     currency              => { sub   => sub { $_[0]->currency->name } },
 787     purpose               => { },
 788     local_account_number  => { sub   => sub { $_[0]->local_bank_account->account_number } },
 789     local_bank_code       => { sub   => sub { $_[0]->local_bank_account->bank_code } },
 790     local_bank_name       => { sub   => sub { $_[0]->local_bank_account->name } },
 791     id                    => {},
 792   );
 793
 794   map { $column_defs{$_}->{text} ||= $::locale->text( $self->models->get_sort_spec->{$_}->{title} ) } keys %column_defs;
 795
 796   $report->set_options(
 797     std_column_visibility => 1,
 798     controller_class      => 'BankTransaction',
 799     output_format         => 'HTML',
 800     top_info_text         => $::locale->text('Bank transactions'),
 801     title                 => $::locale->text('Bank transactions'),
 802     allow_pdf_export      => 1,
 803     allow_csv_export      => 1,
 804   );
 805   $report->set_columns(%column_defs);
 806   $report->set_column_order(@columns);
 807   $report->set_export_options(qw(list_all filter));
 808   $report->set_options_from_form;
 809   $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
 810   $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
 811
 812   my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted();
 813
 814   $report->set_options(
 815     raw_top_info_text     => $self->render('bank_transactions/report_top',    { output => 0 }, BANK_ACCOUNTS => $bank_accounts),
 816     raw_bottom_info_text  => $self->render('bank_transactions/report_bottom', { output => 0 }),
 817   );
 818 }
 819
 820 sub _existing_record_link {
 821   my ($bt, $invoice) = @_;
 822
 823   # check whether a record link from banktransaction $bt already exists to
 824   # invoice $invoice, returns 1 if that is the case
 825
 826   die unless $bt->isa("SL::DB::BankTransaction") && ( $invoice->isa("SL::DB::Invoice") || $invoice->isa("SL::DB::PurchaseInvoice") );
 827
 828   my $linked_record_to_table = $invoice->is_sales ? 'Invoice' : 'PurchaseInvoice';
 829   my $linked_records = $bt->linked_records( direction => 'to', to => $linked_record_to_table, query => [ id => $invoice->id ]  );
 830
 831   return @$linked_records ? 1 : 0;
 832 };
 833
 834 sub init_problems { [] }
 835
 836 sub init_models {
 837   my ($self) = @_;
 838
 839   SL::Controller::Helper::GetModels->new(
 840     controller => $self,
 841     sorted     => {
 842       _default => {
 843         by  => 'transdate',
 844         dir => 0,   # 1 = ASC, 0 = DESC : default sort is newest at top
 845       },
 846       transdate             => t8('Transdate'),
 847       remote_name           => t8('Remote name'),
 848       amount                => t8('Amount'),
 849       invoice_amount        => t8('Assigned'),
 850       invoices              => t8('Linked invoices'),
 851       valutadate            => t8('Valutadate'),
 852       remote_account_number => t8('Remote account number'),
 853       remote_bank_code      => t8('Remote bank code'),
 854       currency              => t8('Currency'),
 855       purpose               => t8('Purpose'),
 856       local_account_number  => t8('Local account number'),
 857       local_bank_code       => t8('Local bank code'),
 858       local_bank_name       => t8('Bank account'),
 859     },
 860     with_objects => [ 'local_bank_account', 'currency' ],
 861   );
 862 }
 863
 864 1;
 865 __END__
 866
 867 =pod
 868
 869 =encoding utf8
 870
 871 =head1 NAME
 872
 873 SL::Controller::BankTransaction - Posting payments to invoices from
 874 bank transactions imported earlier
 875
 876 =head1 FUNCTIONS
 877
 878 =over 4
 879
 880 =item C<save_single_bank_transaction %params>
 881
 882 Takes a bank transaction ID (as parameter C<bank_transaction_id> and
 883 tries to post its amount to a certain number of invoices (parameter
 884 C<invoice_ids>, an array ref of database IDs to purchase or sales
 885 invoice objects).
 886
 887 The whole function is wrapped in a database transaction. If an
 888 exception occurs the bank transaction is not posted at all. The same
 889 is true if the code detects an error during the execution, e.g. a bank
 890 transaction that's already been posted earlier. In both cases the
 891 database transaction will be rolled back.
 892
 893 If warnings but not errors occur the database transaction is still
 894 committed.
 895
 896 The return value is an error object or C<undef> if the function
 897 succeeded. The calling function will collect all warnings and errors
 898 and display them in a nicely formatted table if any occurred.
 899
 900 An error object is a hash reference containing the following members:
 901
 902 =over 2
 903
 904 =item * C<result> — can be either C<warning> or C<error>. Warnings are
 905 displayed slightly different than errors.
 906
 907 =item * C<message> — a human-readable message included in the list of
 908 errors meant as the description of why the problem happened
 909
 910 =item * C<bank_transaction_id>, C<invoice_ids> — the same parameters
 911 that the function was called with
 912
 913 =item * C<bank_transaction> — the database object
 914 (C<SL::DB::BankTransaction>) corresponding to C<bank_transaction_id>
 915
 916 =item * C<invoices> — an array ref of the database objects (either
 917 C<SL::DB::Invoice> or C<SL::DB::PurchaseInvoice>) corresponding to
 918 C<invoice_ids>
 919
 920 =back
 921
 922 =back
 923
 924 =head1 AUTHOR
 925
 926 Niclas Zimmermann E<lt>niclas@kivitendo-premium.deE<gt>,
 927 Geoffrey Richardson E<lt>information@richardson-bueren.deE<gt>
 928
 929 =cut