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, @all_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,
 228                                       $open_invoice->amount * ($open_invoice->{is_ar} ? 1 : -1), 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       } else {
 670         # use the whole amount of the bank transaction for the invoice, overpay the invoice if necessary
 671
 672         # this catches credit_notes and negative sales invoices
 673         if ( $invoice->is_sales && $invoice->amount < 0 ) {
 674           # $invoice->open_amount     is negative for credit_notes
 675           # $bank_transaction->amount is negative for outgoing transactions
 676           # so $amount_of_transaction is negative but needs positive
 677           $amount_of_transaction *= -1;
 678
 679         } elsif (!$invoice->is_sales && $invoice->invoice_type eq 'ap_transaction' ) {
 680           # $invoice->open_amount may be negative for ap_transaction but may be positiv for negativ ap_transaction
 681           # if $invoice->open_amount is negative $bank_transaction->amount is positve
 682           # if $invoice->open_amount is positive $bank_transaction->amount is negative
 683           # but amount of transaction is for both positive
 684           $amount_of_transaction *= -1 if $invoice->open_amount == - $amount_of_transaction;
 685         }
 686
 687         my $overpaid_amount = $amount_of_transaction - $invoice->open_amount;
 688         $invoice->pay_invoice(chart_id     => $bank_transaction->local_bank_account->chart_id,
 689                               trans_id     => $invoice->id,
 690                               amount       => $amount_of_transaction,
 691                               payment_type => $payment_type,
 692                               source       => $source,
 693                               memo         => $memo,
 694                               transdate    => $bank_transaction->transdate->to_kivitendo);
 695         $bank_transaction->invoice_amount($bank_transaction->amount);
 696         $amount_of_transaction = 0;
 697
 698         if ($overpaid_amount >= 0.01) {
 699           push @warnings, {
 700             %data,
 701             result  => 'warning',
 702             message => $::locale->text('Invoice #1 was overpaid by #2.', $invoice->invnumber, $::form->format_amount(\%::myconfig, $overpaid_amount, 2)),
 703           };
 704         }
 705       }
 706       # Record a record link from the bank transaction to the invoice
 707       my @props = (
 708         from_table => 'bank_transactions',
 709         from_id    => $bt_id,
 710         to_table   => $invoice->is_sales ? 'ar' : 'ap',
 711         to_id      => $invoice->id,
 712       );
 713
 714       SL::DB::RecordLink->new(@props)->save;
 715
 716       # "close" a sepa_export_item if it exists
 717       # code duplicated in action_save_proposals!
 718       # currently only works, if there is only exactly one open sepa_export_item
 719       if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
 720         if ( scalar @$seis == 1 ) {
 721           # moved the execution and the check for sepa_export into a method,
 722           # this isn't part of a transaction, though
 723           $seis->[0]->set_executed if $invoice->id == $seis->[0]->arap_id;
 724         }
 725       }
 726
 727     }
 728     $bank_transaction->save;
 729
 730     # 'undef' means 'no error' here.
 731     return undef;
 732   };
 733
 734   my $error;
 735   my $rez = $data{bank_transaction}->db->with_transaction(sub {
 736     eval {
 737       $error = $worker->();
 738       1;
 739
 740     } or do {
 741       $error = {
 742         %data,
 743         result  => 'error',
 744         message => $@,
 745       };
 746     };
 747
 748     # Rollback Fehler nicht weiterreichen
 749     # die if $error;
 750   });
 751
 752   return grep { $_ } ($error, @warnings);
 753 }
 754
 755 #
 756 # filters
 757 #
 758
 759 sub check_auth {
 760   $::auth->assert('bank_transaction');
 761 }
 762
 763 #
 764 # helpers
 765 #
 766
 767 sub make_filter_summary {
 768   my ($self) = @_;
 769
 770   my $filter = $::form->{filter} || {};
 771   my @filter_strings;
 772
 773   my @filters = (
 774     [ $filter->{"transdate:date::ge"},      $::locale->text('Transdate')  . " " . $::locale->text('From Date') ],
 775     [ $filter->{"transdate:date::le"},      $::locale->text('Transdate')  . " " . $::locale->text('To Date')   ],
 776     [ $filter->{"valutadate:date::ge"},     $::locale->text('Valutadate') . " " . $::locale->text('From Date') ],
 777     [ $filter->{"valutadate:date::le"},     $::locale->text('Valutadate') . " " . $::locale->text('To Date')   ],
 778     [ $filter->{"amount:number"},           $::locale->text('Amount')                                          ],
 779     [ $filter->{"bank_account_id:integer"}, $::locale->text('Local bank account')                              ],
 780   );
 781
 782   for (@filters) {
 783     push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
 784   }
 785
 786   $self->{filter_summary} = join ', ', @filter_strings;
 787 }
 788
 789 sub prepare_report {
 790   my ($self)      = @_;
 791
 792   my $callback    = $self->models->get_callback;
 793
 794   my $report      = SL::ReportGenerator->new(\%::myconfig, $::form);
 795   $self->{report} = $report;
 796
 797   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);
 798   my @sortable    = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount                                  purpose local_account_number local_bank_code);
 799
 800   my %column_defs = (
 801     transdate             => { sub   => sub { $_[0]->transdate_as_date } },
 802     valutadate            => { sub   => sub { $_[0]->valutadate_as_date } },
 803     remote_name           => { },
 804     remote_account_number => { },
 805     remote_bank_code      => { },
 806     amount                => { sub   => sub { $_[0]->amount_as_number },
 807                                align => 'right' },
 808     invoice_amount        => { sub   => sub { $_[0]->invoice_amount_as_number },
 809                                align => 'right' },
 810     invoices              => { sub   => sub { $_[0]->linked_invoices } },
 811     currency              => { sub   => sub { $_[0]->currency->name } },
 812     purpose               => { },
 813     local_account_number  => { sub   => sub { $_[0]->local_bank_account->account_number } },
 814     local_bank_code       => { sub   => sub { $_[0]->local_bank_account->bank_code } },
 815     local_bank_name       => { sub   => sub { $_[0]->local_bank_account->name } },
 816     id                    => {},
 817   );
 818
 819   map { $column_defs{$_}->{text} ||= $::locale->text( $self->models->get_sort_spec->{$_}->{title} ) } keys %column_defs;
 820
 821   $report->set_options(
 822     std_column_visibility => 1,
 823     controller_class      => 'BankTransaction',
 824     output_format         => 'HTML',
 825     top_info_text         => $::locale->text('Bank transactions'),
 826     title                 => $::locale->text('Bank transactions'),
 827     allow_pdf_export      => 1,
 828     allow_csv_export      => 1,
 829   );
 830   $report->set_columns(%column_defs);
 831   $report->set_column_order(@columns);
 832   $report->set_export_options(qw(list_all filter));
 833   $report->set_options_from_form;
 834   $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
 835   $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
 836
 837   my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted();
 838
 839   $report->set_options(
 840     raw_top_info_text     => $self->render('bank_transactions/report_top',    { output => 0 }, BANK_ACCOUNTS => $bank_accounts),
 841     raw_bottom_info_text  => $self->render('bank_transactions/report_bottom', { output => 0 }),
 842   );
 843 }
 844
 845 sub _existing_record_link {
 846   my ($bt, $invoice) = @_;
 847
 848   # check whether a record link from banktransaction $bt already exists to
 849   # invoice $invoice, returns 1 if that is the case
 850
 851   die unless $bt->isa("SL::DB::BankTransaction") && ( $invoice->isa("SL::DB::Invoice") || $invoice->isa("SL::DB::PurchaseInvoice") );
 852
 853   my $linked_record_to_table = $invoice->is_sales ? 'Invoice' : 'PurchaseInvoice';
 854   my $linked_records = $bt->linked_records( direction => 'to', to => $linked_record_to_table, query => [ id => $invoice->id ]  );
 855
 856   return @$linked_records ? 1 : 0;
 857 };
 858
 859 sub init_problems { [] }
 860
 861 sub init_models {
 862   my ($self) = @_;
 863
 864   SL::Controller::Helper::GetModels->new(
 865     controller => $self,
 866     sorted     => {
 867       _default => {
 868         by  => 'transdate',
 869         dir => 0,   # 1 = ASC, 0 = DESC : default sort is newest at top
 870       },
 871       transdate             => t8('Transdate'),
 872       remote_name           => t8('Remote name'),
 873       amount                => t8('Amount'),
 874       invoice_amount        => t8('Assigned'),
 875       invoices              => t8('Linked invoices'),
 876       valutadate            => t8('Valutadate'),
 877       remote_account_number => t8('Remote account number'),
 878       remote_bank_code      => t8('Remote bank code'),
 879       currency              => t8('Currency'),
 880       purpose               => t8('Purpose'),
 881       local_account_number  => t8('Local account number'),
 882       local_bank_code       => t8('Local bank code'),
 883       local_bank_name       => t8('Bank account'),
 884     },
 885     with_objects => [ 'local_bank_account', 'currency' ],
 886   );
 887 }
 888
 889 sub load_ap_record_template_url {
 890   my ($self, $template) = @_;
 891
 892   return $self->url_for(
 893     controller                           => 'ap.pl',
 894     action                               => 'load_record_template',
 895     id                                   => $template->id,
 896     'form_defaults.amount_1'             => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
 897     'form_defaults.transdate'            => $self->transaction->transdate_as_date,
 898     'form_defaults.duedate'              => $self->transaction->transdate_as_date,
 899     'form_defaults.no_payment_bookings'  => 1,
 900     'form_defaults.paid_1_suggestion'    => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
 901     'form_defaults.AP_paid_1_suggestion' => $self->transaction->local_bank_account->chart->accno,
 902     'form_defaults.callback'             => $self->callback,
 903   );
 904 }
 905
 906 sub setup_search_action_bar {
 907   my ($self, %params) = @_;
 908
 909   for my $bar ($::request->layout->get('actionbar')) {
 910     $bar->add(
 911       action => [
 912         t8('Filter'),
 913         submit    => [ '#search_form', { action => 'BankTransaction/list' } ],
 914         accesskey => 'enter',
 915       ],
 916     );
 917   }
 918 }
 919
 920 sub setup_list_all_action_bar {
 921   my ($self, %params) = @_;
 922
 923   for my $bar ($::request->layout->get('actionbar')) {
 924     $bar->add(
 925       action => [
 926         t8('Filter'),
 927         submit    => [ '#filter_form', { action => 'BankTransaction/list_all' } ],
 928         accesskey => 'enter',
 929       ],
 930     );
 931   }
 932 }
 933
 934 1;
 935 __END__
 936
 937 =pod
 938
 939 =encoding utf8
 940
 941 =head1 NAME
 942
 943 SL::Controller::BankTransaction - Posting payments to invoices from
 944 bank transactions imported earlier
 945
 946 =head1 FUNCTIONS
 947
 948 =over 4
 949
 950 =item C<save_single_bank_transaction %params>
 951
 952 Takes a bank transaction ID (as parameter C<bank_transaction_id> and
 953 tries to post its amount to a certain number of invoices (parameter
 954 C<invoice_ids>, an array ref of database IDs to purchase or sales
 955 invoice objects).
 956
 957 The whole function is wrapped in a database transaction. If an
 958 exception occurs the bank transaction is not posted at all. The same
 959 is true if the code detects an error during the execution, e.g. a bank
 960 transaction that's already been posted earlier. In both cases the
 961 database transaction will be rolled back.
 962
 963 If warnings but not errors occur the database transaction is still
 964 committed.
 965
 966 The return value is an error object or C<undef> if the function
 967 succeeded. The calling function will collect all warnings and errors
 968 and display them in a nicely formatted table if any occurred.
 969
 970 An error object is a hash reference containing the following members:
 971
 972 =over 2
 973
 974 =item * C<result> — can be either C<warning> or C<error>. Warnings are
 975 displayed slightly different than errors.
 976
 977 =item * C<message> — a human-readable message included in the list of
 978 errors meant as the description of why the problem happened
 979
 980 =item * C<bank_transaction_id>, C<invoice_ids> — the same parameters
 981 that the function was called with
 982
 983 =item * C<bank_transaction> — the database object
 984 (C<SL::DB::BankTransaction>) corresponding to C<bank_transaction_id>
 985
 986 =item * C<invoices> — an array ref of the database objects (either
 987 C<SL::DB::Invoice> or C<SL::DB::PurchaseInvoice>) corresponding to
 988 C<invoice_ids>
 989
 990 =back
 991
 992 =back
 993
 994 =head1 AUTHOR
 995
 996 Niclas Zimmermann E<lt>niclas@kivitendo-premium.deE<gt>,
 997 Geoffrey Richardson E<lt>information@richardson-bueren.deE<gt>
 998
 999 =cut