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