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