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