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       } else {
 670         # use the whole amount of the bank transaction for the invoice, overpay the invoice if necessary
 671
 672         if ( $invoice->is_sales && $invoice->invoice_type eq 'credit_note' ) {
 673           # $invoice->open_amount     is negative for credit_notes
 674           # $bank_transaction->amount is negative for outgoing transactions
 675           # so $amount_of_transaction is negative but needs positive
 676           $amount_of_transaction *= -1;
 677
 678         } elsif (!$invoice->is_sales && $invoice->invoice_type eq 'ap_transaction' ) {
 679           # $invoice->open_amount may be negative for ap_transaction but may be positiv for negativ ap_transaction
 680           # if $invoice->open_amount is negative $bank_transaction->amount is positve
 681           # if $invoice->open_amount is positive $bank_transaction->amount is negative
 682           # but amount of transaction is for both positive
 683           $amount_of_transaction *= -1 if $invoice->open_amount == - $amount_of_transaction;
 684         }
 685
 686         my $overpaid_amount = $amount_of_transaction - $invoice->open_amount;
 687         $invoice->pay_invoice(chart_id     => $bank_transaction->local_bank_account->chart_id,
 688                               trans_id     => $invoice->id,
 689                               amount       => $amount_of_transaction,
 690                               payment_type => $payment_type,
 691                               source       => $source,
 692                               memo         => $memo,
 693                               transdate    => $bank_transaction->transdate->to_kivitendo);
 694         $bank_transaction->invoice_amount($bank_transaction->amount);
 695         $amount_of_transaction = 0;
 696
 697         if ($overpaid_amount >= 0.01) {
 698           push @warnings, {
 699             %data,
 700             result  => 'warning',
 701             message => $::locale->text('Invoice #1 was overpaid by #2.', $invoice->invnumber, $::form->format_amount(\%::myconfig, $overpaid_amount, 2)),
 702           };
 703         }
 704       }
 705       # Record a record link from the bank transaction to the invoice
 706       my @props = (
 707         from_table => 'bank_transactions',
 708         from_id    => $bt_id,
 709         to_table   => $invoice->is_sales ? 'ar' : 'ap',
 710         to_id      => $invoice->id,
 711       );
 712
 713       SL::DB::RecordLink->new(@props)->save;
 714
 715       # "close" a sepa_export_item if it exists
 716       # code duplicated in action_save_proposals!
 717       # currently only works, if there is only exactly one open sepa_export_item
 718       if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
 719         if ( scalar @$seis == 1 ) {
 720           # moved the execution and the check for sepa_export into a method,
 721           # this isn't part of a transaction, though
 722           $seis->[0]->set_executed if $invoice->id == $seis->[0]->arap_id;
 723         }
 724       }
 725
 726     }
 727     $bank_transaction->save;
 728
 729     # 'undef' means 'no error' here.
 730     return undef;
 731   };
 732
 733   my $error;
 734   my $rez = $data{bank_transaction}->db->with_transaction(sub {
 735     eval {
 736       $error = $worker->();
 737       1;
 738
 739     } or do {
 740       $error = {
 741         %data,
 742         result  => 'error',
 743         message => $@,
 744       };
 745     };
 746
 747     # Rollback Fehler nicht weiterreichen
 748     # die if $error;
 749   });
 750
 751   return grep { $_ } ($error, @warnings);
 752 }
 753
 754 #
 755 # filters
 756 #
 757
 758 sub check_auth {
 759   $::auth->assert('bank_transaction');
 760 }
 761
 762 #
 763 # helpers
 764 #
 765
 766 sub make_filter_summary {
 767   my ($self) = @_;
 768
 769   my $filter = $::form->{filter} || {};
 770   my @filter_strings;
 771
 772   my @filters = (
 773     [ $filter->{"transdate:date::ge"},      $::locale->text('Transdate')  . " " . $::locale->text('From Date') ],
 774     [ $filter->{"transdate:date::le"},      $::locale->text('Transdate')  . " " . $::locale->text('To Date')   ],
 775     [ $filter->{"valutadate:date::ge"},     $::locale->text('Valutadate') . " " . $::locale->text('From Date') ],
 776     [ $filter->{"valutadate:date::le"},     $::locale->text('Valutadate') . " " . $::locale->text('To Date')   ],
 777     [ $filter->{"amount:number"},           $::locale->text('Amount')                                          ],
 778     [ $filter->{"bank_account_id:integer"}, $::locale->text('Local bank account')                              ],
 779   );
 780
 781   for (@filters) {
 782     push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
 783   }
 784
 785   $self->{filter_summary} = join ', ', @filter_strings;
 786 }
 787
 788 sub prepare_report {
 789   my ($self)      = @_;
 790
 791   my $callback    = $self->models->get_callback;
 792
 793   my $report      = SL::ReportGenerator->new(\%::myconfig, $::form);
 794   $self->{report} = $report;
 795
 796   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);
 797   my @sortable    = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount                                  purpose local_account_number local_bank_code);
 798
 799   my %column_defs = (
 800     transdate             => { sub   => sub { $_[0]->transdate_as_date } },
 801     valutadate            => { sub   => sub { $_[0]->valutadate_as_date } },
 802     remote_name           => { },
 803     remote_account_number => { },
 804     remote_bank_code      => { },
 805     amount                => { sub   => sub { $_[0]->amount_as_number },
 806                                align => 'right' },
 807     invoice_amount        => { sub   => sub { $_[0]->invoice_amount_as_number },
 808                                align => 'right' },
 809     invoices              => { sub   => sub { $_[0]->linked_invoices } },
 810     currency              => { sub   => sub { $_[0]->currency->name } },
 811     purpose               => { },
 812     local_account_number  => { sub   => sub { $_[0]->local_bank_account->account_number } },
 813     local_bank_code       => { sub   => sub { $_[0]->local_bank_account->bank_code } },
 814     local_bank_name       => { sub   => sub { $_[0]->local_bank_account->name } },
 815     id                    => {},
 816   );
 817
 818   map { $column_defs{$_}->{text} ||= $::locale->text( $self->models->get_sort_spec->{$_}->{title} ) } keys %column_defs;
 819
 820   $report->set_options(
 821     std_column_visibility => 1,
 822     controller_class      => 'BankTransaction',
 823     output_format         => 'HTML',
 824     top_info_text         => $::locale->text('Bank transactions'),
 825     title                 => $::locale->text('Bank transactions'),
 826     allow_pdf_export      => 1,
 827     allow_csv_export      => 1,
 828   );
 829   $report->set_columns(%column_defs);
 830   $report->set_column_order(@columns);
 831   $report->set_export_options(qw(list_all filter));
 832   $report->set_options_from_form;
 833   $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
 834   $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
 835
 836   my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted();
 837
 838   $report->set_options(
 839     raw_top_info_text     => $self->render('bank_transactions/report_top',    { output => 0 }, BANK_ACCOUNTS => $bank_accounts),
 840     raw_bottom_info_text  => $self->render('bank_transactions/report_bottom', { output => 0 }),
 841   );
 842 }
 843
 844 sub _existing_record_link {
 845   my ($bt, $invoice) = @_;
 846
 847   # check whether a record link from banktransaction $bt already exists to
 848   # invoice $invoice, returns 1 if that is the case
 849
 850   die unless $bt->isa("SL::DB::BankTransaction") && ( $invoice->isa("SL::DB::Invoice") || $invoice->isa("SL::DB::PurchaseInvoice") );
 851
 852   my $linked_record_to_table = $invoice->is_sales ? 'Invoice' : 'PurchaseInvoice';
 853   my $linked_records = $bt->linked_records( direction => 'to', to => $linked_record_to_table, query => [ id => $invoice->id ]  );
 854
 855   return @$linked_records ? 1 : 0;
 856 };
 857
 858 sub init_problems { [] }
 859
 860 sub init_models {
 861   my ($self) = @_;
 862
 863   SL::Controller::Helper::GetModels->new(
 864     controller => $self,
 865     sorted     => {
 866       _default => {
 867         by  => 'transdate',
 868         dir => 0,   # 1 = ASC, 0 = DESC : default sort is newest at top
 869       },
 870       transdate             => t8('Transdate'),
 871       remote_name           => t8('Remote name'),
 872       amount                => t8('Amount'),
 873       invoice_amount        => t8('Assigned'),
 874       invoices              => t8('Linked invoices'),
 875       valutadate            => t8('Valutadate'),
 876       remote_account_number => t8('Remote account number'),
 877       remote_bank_code      => t8('Remote bank code'),
 878       currency              => t8('Currency'),
 879       purpose               => t8('Purpose'),
 880       local_account_number  => t8('Local account number'),
 881       local_bank_code       => t8('Local bank code'),
 882       local_bank_name       => t8('Bank account'),
 883     },
 884     with_objects => [ 'local_bank_account', 'currency' ],
 885   );
 886 }
 887
 888 sub load_ap_record_template_url {
 889   my ($self, $template) = @_;
 890
 891   return $self->url_for(
 892     controller                 => 'ap.pl',
 893     action                     => 'load_record_template',
 894     id                         => $template->id,
 895     'form_defaults.amount_1'   => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
 896     'form_defaults.transdate'  => $self->transaction->transdate_as_date,
 897     'form_defaults.duedate'    => $self->transaction->transdate_as_date,
 898     'form_defaults.datepaid_1' => $self->transaction->transdate_as_date,
 899     'form_defaults.paid_1'     => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
 900     'form_defaults.currency'   => $self->transaction->currency->name,
 901     'form_defaults.AP_paid_1'  => $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