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::BankAccount;
  25 use SL::DB::RecordTemplate;
  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                  => [ qw(callback transaction) ],
  36   'scalar --get_set_init' => [ qw(models problems) ],
  37 );
  38
  39 __PACKAGE__->run_before('check_auth');
  40
  41
  42 #
  43 # actions
  44 #
  45
  46 sub action_search {
  47   my ($self) = @_;
  48
  49   my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted( query => [ obsolete => 0 ] );
  50
  51   $self->render('bank_transactions/search',
  52                  BANK_ACCOUNTS => $bank_accounts);
  53 }
  54
  55 sub action_list_all {
  56   my ($self) = @_;
  57
  58   $self->make_filter_summary;
  59   $self->prepare_report;
  60
  61   $self->report_generator_list_objects(report => $self->{report}, objects => $self->models->get);
  62 }
  63
  64 sub action_list {
  65   my ($self) = @_;
  66
  67   if (!$::form->{filter}{bank_account}) {
  68     flash('error', t8('No bank account chosen!'));
  69     $self->action_search;
  70     return;
  71   }
  72
  73   my $sort_by = $::form->{sort_by} || 'transdate';
  74   $sort_by = 'transdate' if $sort_by eq 'proposal';
  75   $sort_by .= $::form->{sort_dir} ? ' DESC' : ' ASC';
  76
  77   my $fromdate = $::locale->parse_date_to_object($::form->{filter}->{fromdate});
  78   my $todate   = $::locale->parse_date_to_object($::form->{filter}->{todate});
  79   $todate->add( days => 1 ) if $todate;
  80
  81   my @where = ();
  82   push @where, (transdate => { ge => $fromdate }) if ($fromdate);
  83   push @where, (transdate => { lt => $todate })   if ($todate);
  84   my $bank_account = SL::DB::Manager::BankAccount->find_by( id => $::form->{filter}{bank_account} );
  85   # bank_transactions no younger than starting date,
  86   # including starting date (same search behaviour as fromdate)
  87   # but OPEN invoices to be matched may be from before
  88   if ( $bank_account->reconciliation_starting_date ) {
  89     push @where, (transdate => { ge => $bank_account->reconciliation_starting_date });
  90   };
  91
  92   my $bank_transactions = SL::DB::Manager::BankTransaction->get_all(
  93     with_objects => [ 'local_bank_account', 'currency' ],
  94     sort_by      => $sort_by,
  95     limit        => 10000,
  96     where        => [
  97       amount                => {ne => \'invoice_amount'},
  98       local_bank_account_id => $::form->{filter}{bank_account},
  99       @where
 100     ],
 101   );
 102   $main::lxdebug->message(LXDebug->DEBUG2(),"count bt=".scalar(@{$bank_transactions}." bank_account=".$bank_account->id." chart=".$bank_account->chart_id));
 103
 104   # credit notes have a negative amount, treat differently
 105   my $all_open_ar_invoices = SL::DB::Manager::Invoice        ->get_all(where => [ or => [ amount => { gt => \'paid' },
 106                                                                                           and => [ type    => 'credit_note',
 107                                                                                                    amount  => { lt => \'paid' }
 108                                                                                                  ],
 109                                                                                         ],
 110                                                                                 ],
 111                                                                        with_objects => ['customer','payment_terms']);
 112
 113   my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => [amount => { ne => \'paid' }], with_objects => ['vendor'  ,'payment_terms']);
 114   my $all_open_sepa_export_items = SL::DB::Manager::SepaExportItem->get_all(where => [chart_id => $bank_account->chart_id ,
 115                                                                              'sepa_export.executed' => 0, 'sepa_export.closed' => 0 ], with_objects => ['sepa_export']);
 116   $main::lxdebug->message(LXDebug->DEBUG2(),"count sepaexport=".scalar(@{$all_open_sepa_export_items}));
 117
 118   my @all_open_invoices;
 119   # filter out invoices with less than 1 cent outstanding
 120   push @all_open_invoices, map { $_->{is_ar}=1 ; $_ } grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ar_invoices };
 121   push @all_open_invoices, map { $_->{is_ar}=0 ; $_ } grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
 122   $main::lxdebug->message(LXDebug->DEBUG2(),"bank_account=".$::form->{filter}{bank_account}." invoices: ".scalar(@{ $all_open_ar_invoices }).
 123                               " + ".scalar(@{ $all_open_ap_invoices })." non fully paid=".scalar(@all_open_invoices)." transactions=".scalar(@{ $bank_transactions }));
 124
 125   my @all_sepa_invoices;
 126   my @all_non_sepa_invoices;
 127   my %sepa_exports;
 128   # first collect sepa export items to open invoices
 129   foreach my $open_invoice (@all_open_invoices){
 130     #    my @items =  grep { $_->ap_id == $open_invoice->id ||  $_->ar_id == $open_invoice->id } @{$all_open_sepa_export_items};
 131     $open_invoice->{realamount}  = $::form->format_amount(\%::myconfig,$open_invoice->amount,2);
 132     $open_invoice->{skonto_type} = 'without_skonto';
 133     foreach ( @{$all_open_sepa_export_items}) {
 134       if ( $_->ap_id == $open_invoice->id ||  $_->ar_id == $open_invoice->id ) {
 135         my $factor = ($_->ar_id == $open_invoice->id?1:-1);
 136         $main::lxdebug->message(LXDebug->DEBUG2(),"exitem=".$_->id." for invoice ".$open_invoice->id." factor=".$factor);
 137         $open_invoice->{realamount}  = $::form->format_amount(\%::myconfig,$open_invoice->amount*$factor,2);
 138         $open_invoice->{sepa_export_item} = $_ ;
 139         $open_invoice->{skonto_type} = $_->payment_type;
 140         $sepa_exports{$_->sepa_export_id} ||= { count => 0, is_ar => 0, amount => 0, proposed => 0, invoices => [], item => $_ };
 141         $sepa_exports{$_->sepa_export_id}->{count}++ ;
 142         $sepa_exports{$_->sepa_export_id}->{is_ar}++ if  $_->ar_id == $open_invoice->id;
 143         $sepa_exports{$_->sepa_export_id}->{amount} += $_->amount * $factor;
 144         push @{ $sepa_exports{$_->sepa_export_id}->{invoices} }, $open_invoice;
 145         #$main::lxdebug->message(LXDebug->DEBUG2(),"amount for export id ".$_->sepa_export_id." = ".
 146         #                          $sepa_exports{$_->sepa_export_id}->{amount}." count = ".
 147         #                          $sepa_exports{$_->sepa_export_id}->{count}." is_ar = ".
 148         #                          $sepa_exports{$_->sepa_export_id}->{is_ar} );
 149         push @all_sepa_invoices , $open_invoice;
 150       }
 151     }
 152     push @all_non_sepa_invoices , $open_invoice if ! $open_invoice->{sepa_export_item};
 153   }
 154
 155   # try to match each bank_transaction with each of the possible open invoices
 156   # by awarding points
 157   my @proposals;
 158
 159   foreach my $bt (@{ $bank_transactions }) {
 160     ## 5 Stellen hinter dem Komma auf 2 Stellen reduzieren
 161     $bt->amount($bt->amount*1);
 162     $bt->invoice_amount($bt->invoice_amount*1);
 163     $main::lxdebug->message(LXDebug->DEBUG2(),"BT ".$bt->id." amount=".$bt->amount." invoice_amount=".$bt->invoice_amount." remote=". $bt->{remote_name});
 164
 165     $bt->{proposals}    = [];
 166     $bt->{rule_matches} = [];
 167
 168     $bt->{remote_name} .= $bt->{remote_name_1} if $bt->{remote_name_1};
 169
 170     if ( $self->is_collective_transaction($bt) ) {
 171       foreach ( keys  %sepa_exports) {
 172         #$main::lxdebug->message(LXDebug->DEBUG2(),"Exp ID=".$_." compare sum amount ".($sepa_exports{$_}->{amount} *1) ." == ".($bt->amount * 1));
 173         if ( $bt->transaction_code eq '191' && abs(($sepa_exports{$_}->{amount} * 1) - ($bt->amount * 1)) < 0.01 ) {
 174           ## jupp
 175           @{$bt->{proposals}} = @{$sepa_exports{$_}->{invoices}};
 176           $bt->{agreement}    = 20;
 177           push(@{$bt->{rule_matches}},'sepa_export_item(20)');
 178           $sepa_exports{$_}->{proposed}=1;
 179           #$main::lxdebug->message(LXDebug->DEBUG2(),"has ".scalar($bt->{proposals})." invoices");
 180           push(@proposals, $bt);
 181           next;
 182         }
 183       }
 184     }
 185     next unless $bt->{remote_name};  # bank has no name, usually fees, use create invoice to assign
 186
 187     foreach ( @{$all_open_sepa_export_items}) {
 188       last if scalar (@all_sepa_invoices) == 0;
 189       foreach my $open_invoice (@all_sepa_invoices){
 190         if ( $_->ap_id == $open_invoice->id ||  $_->ar_id == $open_invoice->id ) {
 191           #$main::lxdebug->message(LXDebug->DEBUG2(),"exitem2=".$_->id." for invoice ".$open_invoice->id);
 192           my $factor = ( $_->ar_id == $open_invoice->id?1:-1);
 193           $_->amount($_->amount*1);
 194           #$main::lxdebug->message(LXDebug->DEBUG2(),"remote account '".$bt->{remote_account_number}."' bt_amount=".$bt->amount." factor=".$factor);
 195           #$main::lxdebug->message(LXDebug->DEBUG2(),"compare with   '".$_->vc_iban."'    amount=".$_->amount);
 196           if ( $bt->{remote_account_number} eq $_->vc_iban && abs(abs($_->amount) - abs($bt->amount)) < 0.01 ) {
 197             my $iban;
 198             $iban = $open_invoice->customer->iban if $open_invoice->is_sales;
 199             $iban = $open_invoice->vendor->iban   if ! $open_invoice->is_sales;
 200             if($bt->{remote_account_number} eq $iban && abs(abs($open_invoice->amount) - abs($bt->amount)) < 0.01 ) {
 201               ($open_invoice->{agreement}, $open_invoice->{rule_matches}) = $bt->get_agreement_with_invoice($open_invoice);
 202               $open_invoice->{agreement} += 5;
 203               $open_invoice->{rule_matches} .= 'sepa_export_item(5) ';
 204               $main::lxdebug->message(LXDebug->DEBUG2(),"sepa invoice_id=".$open_invoice->id." agreement=".$open_invoice->{agreement}." rules matches=".$open_invoice->{rule_matches});
 205               $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount*$factor,2);
 206             }
 207           }
 208         }
 209       }
 210     }
 211
 212     # try to match the current $bt to each of the open_invoices, saving the
 213     # results of get_agreement_with_invoice in $open_invoice->{agreement} and
 214     # $open_invoice->{rule_matches}.
 215
 216     # The values are overwritten each time a new bt is checked, so at the end
 217     # of each bt the likely results are filtered and those values are stored in
 218     # the arrays $bt->{proposals} and $bt->{rule_matches}, and the agreement
 219     # score is stored in $bt->{agreement}
 220
 221     foreach my $open_invoice (@all_non_sepa_invoices){
 222       ($open_invoice->{agreement}, $open_invoice->{rule_matches}) = $bt->get_agreement_with_invoice($open_invoice);
 223       $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount*($open_invoice->{is_ar}?1:-1),2);
 224       $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;
 225     };
 226
 227     my $agreement = 15;
 228     my $min_agreement = 3; # suggestions must have at least this score
 229
 230     my $max_agreement = max map { $_->{agreement} } @all_open_invoices;
 231
 232     # add open_invoices with highest agreement into array $bt->{proposals}
 233     if ( $max_agreement >= $min_agreement ) {
 234       $bt->{proposals} = [ grep { $_->{agreement} == $max_agreement } @all_open_invoices ];
 235       $bt->{agreement} = $max_agreement; #scalar @{ $bt->{proposals} } ? $agreement + 1 : '';
 236
 237       # store the rule_matches in a separate array, so they can be displayed in template
 238       foreach ( @{ $bt->{proposals} } ) {
 239         push(@{$bt->{rule_matches}}, $_->{rule_matches});
 240       };
 241     };
 242   }  # finished one bt
 243   # finished all bt
 244
 245   # separate filter for proposals (second tab, agreement >= 5 and exactly one match)
 246   # to qualify as a proposal there has to be
 247   # * agreement >= 5  TODO: make threshold configurable in configuration
 248   # * there must be only one exact match
 249   # * depending on whether sales or purchase the amount has to have the correct sign (so Gutschriften don't work?)
 250   my $proposal_threshold = 5;
 251   my @otherproposals = grep {
 252        ($_->{agreement} >= $proposal_threshold)
 253     && (1 == scalar @{ $_->{proposals} })
 254     && (@{ $_->{proposals} }[0]->is_sales ? abs(@{ $_->{proposals} }[0]->amount - $_->amount) < 0.01
 255                                           : abs(@{ $_->{proposals} }[0]->amount + $_->amount) < 0.01)
 256   } @{ $bank_transactions };
 257
 258   push ( @proposals, @otherproposals);
 259
 260   # sort bank transaction proposals by quality (score) of proposal
 261   $bank_transactions = [ sort { $a->{agreement} <=> $b->{agreement} } @{ $bank_transactions } ] if $::form->{sort_by} eq 'proposal' and $::form->{sort_dir} == 1;
 262   $bank_transactions = [ sort { $b->{agreement} <=> $a->{agreement} } @{ $bank_transactions } ] if $::form->{sort_by} eq 'proposal' and $::form->{sort_dir} == 0;
 263
 264   $::request->layout->add_javascripts("kivi.BankTransaction.js");
 265   $self->render('bank_transactions/list',
 266                 title             => t8('Bank transactions MT940'),
 267                 BANK_TRANSACTIONS => $bank_transactions,
 268                 PROPOSALS         => \@proposals,
 269                 bank_account      => $bank_account,
 270                 ui_tab            => scalar(@proposals) > 0?1:0,
 271               );
 272 }
 273
 274 sub action_assign_invoice {
 275   my ($self) = @_;
 276
 277   $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
 278
 279   $self->render('bank_transactions/assign_invoice',
 280                 { layout => 0 },
 281                 title => t8('Assign invoice'),);
 282 }
 283
 284 sub action_create_invoice {
 285   my ($self) = @_;
 286   my %myconfig = %main::myconfig;
 287
 288   $self->transaction(SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id}));
 289
 290   my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(account_number => $self->transaction->{remote_account_number});
 291   my $use_vendor_filter     = $self->transaction->{remote_account_number} && $vendor_of_transaction;
 292
 293   my $templates             = SL::DB::Manager::RecordTemplate->get_all(
 294     where        => [ template_type => 'ap_transaction' ],
 295     with_objects => [ qw(employee vendor) ],
 296   );
 297
 298   #Filter templates
 299   $templates = [ grep { $_->vendor_id == $vendor_of_transaction->id } @{ $templates } ] if $use_vendor_filter;
 300
 301   $self->callback($self->url_for(
 302     action                => 'list',
 303     'filter.bank_account' => $::form->{filter}->{bank_account},
 304     'filter.todate'       => $::form->{filter}->{todate},
 305     'filter.fromdate'     => $::form->{filter}->{fromdate},
 306   ));
 307
 308   $self->render(
 309     'bank_transactions/create_invoice',
 310     { layout => 0 },
 311     title       => t8('Create invoice'),
 312     TEMPLATES   => $templates,
 313     vendor_id   => $use_vendor_filter ? $vendor_of_transaction->id   : undef,
 314     vendor_name => $use_vendor_filter ? $vendor_of_transaction->name : undef,
 315   );
 316 }
 317
 318 sub action_ajax_payment_suggestion {
 319   my ($self) = @_;
 320
 321   # based on a BankTransaction ID and a Invoice or PurchaseInvoice ID passed via $::form,
 322   # create an HTML blob to be used by the js function add_invoices in templates/webpages/bank_transactions/list.html
 323   # and return encoded as JSON
 324
 325   my $bt      = SL::DB::Manager::BankTransaction->find_by( id => $::form->{bt_id} );
 326   my $invoice = SL::DB::Manager::Invoice->find_by( id => $::form->{prop_id} ) || SL::DB::Manager::PurchaseInvoice->find_by( id => $::form->{prop_id} );
 327
 328   die unless $bt and $invoice;
 329
 330   my @select_options = $invoice->get_payment_select_options_for_bank_transaction($::form->{bt_id});
 331
 332   my $html;
 333   $html = $self->render(
 334     'bank_transactions/_payment_suggestion', { output => 0 },
 335     bt_id          => $::form->{bt_id},
 336     prop_id        => $::form->{prop_id},
 337     invoice        => $invoice,
 338     SELECT_OPTIONS => \@select_options,
 339   );
 340
 341   $self->render(\ SL::JSON::to_json( { 'html' => "$html" } ), { layout => 0, type => 'json', process => 0 });
 342 };
 343
 344 sub action_filter_templates {
 345   my ($self) = @_;
 346
 347   $self->{transaction}      = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
 348   my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(account_number => $self->{transaction}->{remote_account_number});
 349
 350   my @filter;
 351   push @filter, ('vendor.id'   => $::form->{vendor_id})                       if $::form->{vendor_id};
 352   push @filter, ('vendor.name' => { ilike => '%' . $::form->{vendor} . '%' }) if $::form->{vendor};
 353
 354   my $templates = SL::DB::Manager::RecordTemplate->get_all(
 355     where        => [ template_type => 'ap_transaction', (or => \@filter) x !!@filter ],
 356     with_objects => [ qw(employee vendor) ],
 357   );
 358
 359   $::form->{filter} //= {};
 360
 361   $self->callback($self->url_for(
 362     action                => 'list',
 363     'filter.bank_account' => $::form->{filter}->{bank_account},
 364     'filter.todate'       => $::form->{filter}->{todate},
 365     'filter.fromdate'     => $::form->{filter}->{fromdate},
 366   ));
 367
 368   my $output  = $self->render(
 369     'bank_transactions/_template_list',
 370     { output => 0 },
 371     TEMPLATES => $templates,
 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   foreach (@{ $self->problems }) {
 515     $count-- if $_->{result} eq 'error';
 516   }
 517   return $count;
 518 }
 519
 520 sub action_save_invoices {
 521   my ($self) = @_;
 522   my $count = $self->save_invoices();
 523
 524   flash('ok', t8('#1 invoice(s) saved.', $count));
 525
 526   $self->action_list();
 527 }
 528
 529 sub action_save_proposals {
 530   my ($self) = @_;
 531
 532   if ( $::form->{proposal_ids} ) {
 533     my $propcount = scalar(@{ $::form->{proposal_ids} });
 534     if ( $propcount > 0 ) {
 535       my $count = $self->save_invoices();
 536
 537       flash('ok', t8('#1 proposal(s) with #2 invoice(s) saved.',  $propcount, $count));
 538     }
 539   }
 540   $self->action_list();
 541
 542 }
 543
 544 sub 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) && ($_->invoice_type eq 'purchase_invoice'))
 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       my $source = ($data{sources} // [])->[$n_invoices];
 616       my $memo   = ($data{memos}   // [])->[$n_invoices];
 617
 618       $n_invoices++ ;
 619
 620       # Check if bank_transaction already has a link to the invoice, may only be linked once per invoice
 621       # This might be caused by the user reloading a page and resending the form
 622       if (_existing_record_link($bank_transaction, $invoice)) {
 623         return {
 624           %data,
 625           result  => 'error',
 626           message => $::locale->text("Bank transaction with id #1 has already been linked to #2.", $bank_transaction->id, $invoice->displayable_name),
 627         };
 628       }
 629
 630       if (!$amount_of_transaction && $invoice->open_amount) {
 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;
 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
 646       # pay invoice or go to the next bank transaction if the amount is not sufficiently high
 647       if ($invoice->open_amount <= $amount_of_transaction && $n_invoices < $max_invoices) {
 648         my $open_amount = ($payment_type eq 'with_skonto_pt'?$invoice->amount_less_skonto:$invoice->open_amount);
 649         # first calculate new bank transaction amount ...
 650         if ($invoice->is_sales) {
 651           $amount_of_transaction -= $sign * $open_amount;
 652           $bank_transaction->invoice_amount($bank_transaction->invoice_amount + $open_amount);
 653         } else {
 654           $amount_of_transaction += $sign * $open_amount;
 655           $bank_transaction->invoice_amount($bank_transaction->invoice_amount - $open_amount);
 656         }
 657         # ... and then pay the invoice
 658         $invoice->pay_invoice(chart_id     => $bank_transaction->local_bank_account->chart_id,
 659                               trans_id     => $invoice->id,
 660                               amount       => $open_amount,
 661                               payment_type => $payment_type,
 662                               source       => $source,
 663                               memo         => $memo,
 664                               transdate    => $bank_transaction->transdate->to_kivitendo);
 665       } elsif (( $invoice->is_sales && $invoice->invoice_type eq 'credit_note' ) ||
 666                (!$invoice->is_sales && $invoice->invoice_type eq 'ap_transaction' )) {
 667         # no check for overpayment/multiple payments
 668         $invoice->pay_invoice(chart_id     => $bank_transaction->local_bank_account->chart_id,
 669                               trans_id     => $invoice->id,
 670                               amount       => $invoice->open_amount,
 671                               payment_type => $payment_type,
 672                               source       => $source,
 673                               memo         => $memo,
 674                               transdate    => $bank_transaction->transdate->to_kivitendo);
 675       } else { # use the whole amount of the bank transaction for the invoice, overpay the invoice if necessary
 676         my $overpaid_amount = $amount_of_transaction - $invoice->open_amount;
 677         $invoice->pay_invoice(chart_id     => $bank_transaction->local_bank_account->chart_id,
 678                               trans_id     => $invoice->id,
 679                               amount       => $amount_of_transaction,
 680                               payment_type => $payment_type,
 681                               source       => $source,
 682                               memo         => $memo,
 683                               transdate    => $bank_transaction->transdate->to_kivitendo);
 684         $bank_transaction->invoice_amount($bank_transaction->amount);
 685         $amount_of_transaction = 0;
 686
 687         if ($overpaid_amount >= 0.01) {
 688           push @warnings, {
 689             %data,
 690             result  => 'warning',
 691             message => $::locale->text('Invoice #1 was overpaid by #2.', $invoice->invnumber, $::form->format_amount(\%::myconfig, $overpaid_amount, 2)),
 692           };
 693         }
 694       }
 695       # Record a record link from the bank transaction to the invoice
 696       my @props = (
 697         from_table => 'bank_transactions',
 698         from_id    => $bt_id,
 699         to_table   => $invoice->is_sales ? 'ar' : 'ap',
 700         to_id      => $invoice->id,
 701       );
 702
 703       SL::DB::RecordLink->new(@props)->save;
 704
 705       # "close" a sepa_export_item if it exists
 706       # code duplicated in action_save_proposals!
 707       # currently only works, if there is only exactly one open sepa_export_item
 708       if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
 709         if ( scalar @$seis == 1 ) {
 710           # moved the execution and the check for sepa_export into a method,
 711           # this isn't part of a transaction, though
 712           $seis->[0]->set_executed if $invoice->id == $seis->[0]->arap_id;
 713         }
 714       }
 715
 716     }
 717     $bank_transaction->save;
 718
 719     # 'undef' means 'no error' here.
 720     return undef;
 721   };
 722
 723   my $error;
 724   my $rez = $data{bank_transaction}->db->with_transaction(sub {
 725     eval {
 726       $error = $worker->();
 727       1;
 728
 729     } or do {
 730       $error = {
 731         %data,
 732         result  => 'error',
 733         message => $@,
 734       };
 735     };
 736
 737     # Rollback Fehler nicht weiterreichen
 738     # die if $error;
 739   });
 740
 741   return grep { $_ } ($error, @warnings);
 742 }
 743
 744 #
 745 # filters
 746 #
 747
 748 sub check_auth {
 749   $::auth->assert('bank_transaction');
 750 }
 751
 752 #
 753 # helpers
 754 #
 755
 756 sub make_filter_summary {
 757   my ($self) = @_;
 758
 759   my $filter = $::form->{filter} || {};
 760   my @filter_strings;
 761
 762   my @filters = (
 763     [ $filter->{"transdate:date::ge"},      $::locale->text('Transdate')  . " " . $::locale->text('From Date') ],
 764     [ $filter->{"transdate:date::le"},      $::locale->text('Transdate')  . " " . $::locale->text('To Date')   ],
 765     [ $filter->{"valutadate:date::ge"},     $::locale->text('Valutadate') . " " . $::locale->text('From Date') ],
 766     [ $filter->{"valutadate:date::le"},     $::locale->text('Valutadate') . " " . $::locale->text('To Date')   ],
 767     [ $filter->{"amount:number"},           $::locale->text('Amount')                                          ],
 768     [ $filter->{"bank_account_id:integer"}, $::locale->text('Local bank account')                              ],
 769   );
 770
 771   for (@filters) {
 772     push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
 773   }
 774
 775   $self->{filter_summary} = join ', ', @filter_strings;
 776 }
 777
 778 sub prepare_report {
 779   my ($self)      = @_;
 780
 781   my $callback    = $self->models->get_callback;
 782
 783   my $report      = SL::ReportGenerator->new(\%::myconfig, $::form);
 784   $self->{report} = $report;
 785
 786   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);
 787   my @sortable    = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount                                  purpose local_account_number local_bank_code);
 788
 789   my %column_defs = (
 790     transdate             => { sub   => sub { $_[0]->transdate_as_date } },
 791     valutadate            => { sub   => sub { $_[0]->valutadate_as_date } },
 792     remote_name           => { },
 793     remote_account_number => { },
 794     remote_bank_code      => { },
 795     amount                => { sub   => sub { $_[0]->amount_as_number },
 796                                align => 'right' },
 797     invoice_amount        => { sub   => sub { $_[0]->invoice_amount_as_number },
 798                                align => 'right' },
 799     invoices              => { sub   => sub { $_[0]->linked_invoices } },
 800     currency              => { sub   => sub { $_[0]->currency->name } },
 801     purpose               => { },
 802     local_account_number  => { sub   => sub { $_[0]->local_bank_account->account_number } },
 803     local_bank_code       => { sub   => sub { $_[0]->local_bank_account->bank_code } },
 804     local_bank_name       => { sub   => sub { $_[0]->local_bank_account->name } },
 805     id                    => {},
 806   );
 807
 808   map { $column_defs{$_}->{text} ||= $::locale->text( $self->models->get_sort_spec->{$_}->{title} ) } keys %column_defs;
 809
 810   $report->set_options(
 811     std_column_visibility => 1,
 812     controller_class      => 'BankTransaction',
 813     output_format         => 'HTML',
 814     top_info_text         => $::locale->text('Bank transactions'),
 815     title                 => $::locale->text('Bank transactions'),
 816     allow_pdf_export      => 1,
 817     allow_csv_export      => 1,
 818   );
 819   $report->set_columns(%column_defs);
 820   $report->set_column_order(@columns);
 821   $report->set_export_options(qw(list_all filter));
 822   $report->set_options_from_form;
 823   $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
 824   $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
 825
 826   my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted();
 827
 828   $report->set_options(
 829     raw_top_info_text     => $self->render('bank_transactions/report_top',    { output => 0 }, BANK_ACCOUNTS => $bank_accounts),
 830     raw_bottom_info_text  => $self->render('bank_transactions/report_bottom', { output => 0 }),
 831   );
 832 }
 833
 834 sub _existing_record_link {
 835   my ($bt, $invoice) = @_;
 836
 837   # check whether a record link from banktransaction $bt already exists to
 838   # invoice $invoice, returns 1 if that is the case
 839
 840   die unless $bt->isa("SL::DB::BankTransaction") && ( $invoice->isa("SL::DB::Invoice") || $invoice->isa("SL::DB::PurchaseInvoice") );
 841
 842   my $linked_record_to_table = $invoice->is_sales ? 'Invoice' : 'PurchaseInvoice';
 843   my $linked_records = $bt->linked_records( direction => 'to', to => $linked_record_to_table, query => [ id => $invoice->id ]  );
 844
 845   return @$linked_records ? 1 : 0;
 846 };
 847
 848 sub init_problems { [] }
 849
 850 sub init_models {
 851   my ($self) = @_;
 852
 853   SL::Controller::Helper::GetModels->new(
 854     controller => $self,
 855     sorted     => {
 856       _default => {
 857         by  => 'transdate',
 858         dir => 0,   # 1 = ASC, 0 = DESC : default sort is newest at top
 859       },
 860       transdate             => t8('Transdate'),
 861       remote_name           => t8('Remote name'),
 862       amount                => t8('Amount'),
 863       invoice_amount        => t8('Assigned'),
 864       invoices              => t8('Linked invoices'),
 865       valutadate            => t8('Valutadate'),
 866       remote_account_number => t8('Remote account number'),
 867       remote_bank_code      => t8('Remote bank code'),
 868       currency              => t8('Currency'),
 869       purpose               => t8('Purpose'),
 870       local_account_number  => t8('Local account number'),
 871       local_bank_code       => t8('Local bank code'),
 872       local_bank_name       => t8('Bank account'),
 873     },
 874     with_objects => [ 'local_bank_account', 'currency' ],
 875   );
 876 }
 877
 878 sub load_ap_record_template_url {
 879   my ($self, $template) = @_;
 880
 881   return $self->url_for(
 882     controller                 => 'ap.pl',
 883     action                     => 'load_record_template',
 884     id                         => $template->id,
 885     'form_defaults.amount_1'   => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
 886     'form_defaults.transdate'  => $self->transaction->transdate_as_date,
 887     'form_defaults.duedate'    => $self->transaction->transdate_as_date,
 888     'form_defaults.datepaid_1' => $self->transaction->transdate_as_date,
 889     'form_defaults.paid_1'     => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
 890     'form_defaults.currency'   => $self->transaction->currency->name,
 891     'form_defaults.AP_paid_1'  => $self->transaction->local_bank_account->chart->accno,
 892     'form_defaults.callback'   => $self->callback,
 893   );
 894 }
 895
 896 1;
 897 __END__
 898
 899 =pod
 900
 901 =encoding utf8
 902
 903 =head1 NAME
 904
 905 SL::Controller::BankTransaction - Posting payments to invoices from
 906 bank transactions imported earlier
 907
 908 =head1 FUNCTIONS
 909
 910 =over 4
 911
 912 =item C<save_single_bank_transaction %params>
 913
 914 Takes a bank transaction ID (as parameter C<bank_transaction_id> and
 915 tries to post its amount to a certain number of invoices (parameter
 916 C<invoice_ids>, an array ref of database IDs to purchase or sales
 917 invoice objects).
 918
 919 The whole function is wrapped in a database transaction. If an
 920 exception occurs the bank transaction is not posted at all. The same
 921 is true if the code detects an error during the execution, e.g. a bank
 922 transaction that's already been posted earlier. In both cases the
 923 database transaction will be rolled back.
 924
 925 If warnings but not errors occur the database transaction is still
 926 committed.
 927
 928 The return value is an error object or C<undef> if the function
 929 succeeded. The calling function will collect all warnings and errors
 930 and display them in a nicely formatted table if any occurred.
 931
 932 An error object is a hash reference containing the following members:
 933
 934 =over 2
 935
 936 =item * C<result> — can be either C<warning> or C<error>. Warnings are
 937 displayed slightly different than errors.
 938
 939 =item * C<message> — a human-readable message included in the list of
 940 errors meant as the description of why the problem happened
 941
 942 =item * C<bank_transaction_id>, C<invoice_ids> — the same parameters
 943 that the function was called with
 944
 945 =item * C<bank_transaction> — the database object
 946 (C<SL::DB::BankTransaction>) corresponding to C<bank_transaction_id>
 947
 948 =item * C<invoices> — an array ref of the database objects (either
 949 C<SL::DB::Invoice> or C<SL::DB::PurchaseInvoice>) corresponding to
 950 C<invoice_ids>
 951
 952 =back
 953
 954 =back
 955
 956 =head1 AUTHOR
 957
 958 Niclas Zimmermann E<lt>niclas@kivitendo-premium.deE<gt>,
 959 Geoffrey Richardson E<lt>information@richardson-bueren.deE<gt>
 960
 961 =cut