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->amount($bt->amount*1);
 153     $bt->invoice_amount($bt->invoice_amount*1);
 154     $main::lxdebug->message(LXDebug->DEBUG2(),"BT ".$bt->id." amount=".$bt->amount." invoice_amount=".$bt->invoice_amount." remote=". $bt->{remote_name});
 155
 156     $bt->{proposals}    = [];
 157     $bt->{rule_matches} = [];
 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     callback    => $callback,
 309   );
 310 }
 311
 312 sub action_ajax_payment_suggestion {
 313   my ($self) = @_;
 314
 315   # based on a BankTransaction ID and a Invoice or PurchaseInvoice ID passed via $::form,
 316   # create an HTML blob to be used by the js function add_invoices in templates/webpages/bank_transactions/list.html
 317   # and return encoded as JSON
 318
 319   my $bt      = SL::DB::Manager::BankTransaction->find_by( id => $::form->{bt_id} );
 320   my $invoice = SL::DB::Manager::Invoice->find_by( id => $::form->{prop_id} ) || SL::DB::Manager::PurchaseInvoice->find_by( id => $::form->{prop_id} );
 321
 322   die unless $bt and $invoice;
 323
 324   my @select_options = $invoice->get_payment_select_options_for_bank_transaction($::form->{bt_id});
 325
 326   my $html;
 327   $html .= SL::Presenter->input_tag('invoice_ids.' . $::form->{bt_id} . '[]', $::form->{prop_id} , type => 'hidden');
 328   $html .= SL::Presenter->escape(t8('Invno.')      . ': ' . $invoice->invnumber . ' ');
 329   $html .= SL::Presenter->escape(t8('Open amount') . ': ' . $::form->format_amount(\%::myconfig, $invoice->open_amount, 2) . ' ');
 330   $html .= SL::Presenter->select_tag('invoice_skontos.' . $::form->{bt_id} . '[]',
 331                                      \@select_options,
 332                                      value_key => 'payment_type',
 333                                      title_key => 'display' )
 334     if @select_options;
 335   $html .= '<a href=# onclick="delete_invoice(' . $::form->{bt_id} . ',' . $::form->{prop_id} . ');">x</a>';
 336   $html = SL::Presenter->html_tag('div', $html, id => $::form->{bt_id} . '.' . $::form->{prop_id});
 337
 338   $self->render(\ SL::JSON::to_json( { 'html' => $html } ), { layout => 0, type => 'json', process => 0 });
 339 };
 340
 341 sub action_filter_drafts {
 342   my ($self) = @_;
 343
 344   $self->{transaction}      = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
 345   my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(account_number => $self->{transaction}->{remote_account_number});
 346
 347   my $drafts                = SL::DB::Manager::Draft->get_all(with_objects => 'employee');
 348
 349   my @filtered_drafts;
 350
 351   foreach my $draft ( @{ $drafts } ) {
 352     my $draft_as_object = YAML::Load($draft->form);
 353     next unless $draft_as_object->{vendor_id};  # we cannot filter for vendor name, if this is a gl draft
 354
 355     my $vendor          = SL::DB::Manager::Vendor->find_by(id => $draft_as_object->{vendor_id});
 356     $draft->{vendor}    = $vendor->name;
 357     $draft->{vendor_id} = $vendor->id;
 358
 359     push @filtered_drafts, $draft;
 360   }
 361
 362   my $vendor_name = $::form->{vendor};
 363   my $vendor_id   = $::form->{vendor_id};
 364
 365   #Filter drafts
 366   @filtered_drafts = grep { $_->{vendor_id} == $vendor_id      } @filtered_drafts if $vendor_id;
 367   @filtered_drafts = grep { $_->{vendor}    =~ /$vendor_name/i } @filtered_drafts if $vendor_name;
 368
 369   my $output  = $self->render(
 370     'bank_transactions/filter_drafts',
 371     { output => 0 },
 372     DRAFTS => \@filtered_drafts,
 373   );
 374
 375   my %result = ( count => 0, html => $output );
 376
 377   $self->render(\to_json(\%result), { type => 'json', process => 0 });
 378 }
 379
 380 sub action_ajax_add_list {
 381   my ($self) = @_;
 382
 383   my @where_sale     = (amount => { ne => \'paid' });
 384   my @where_purchase = (amount => { ne => \'paid' });
 385
 386   if ($::form->{invnumber}) {
 387     push @where_sale,     (invnumber => { ilike => like($::form->{invnumber})});
 388     push @where_purchase, (invnumber => { ilike => like($::form->{invnumber})});
 389   }
 390
 391   if ($::form->{amount}) {
 392     push @where_sale,     (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
 393     push @where_purchase, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
 394   }
 395
 396   if ($::form->{vcnumber}) {
 397     push @where_sale,     ('customer.customernumber' => { ilike => like($::form->{vcnumber})});
 398     push @where_purchase, ('vendor.vendornumber'     => { ilike => like($::form->{vcnumber})});
 399   }
 400
 401   if ($::form->{vcname}) {
 402     push @where_sale,     ('customer.name' => { ilike => like($::form->{vcname})});
 403     push @where_purchase, ('vendor.name'   => { ilike => like($::form->{vcname})});
 404   }
 405
 406   if ($::form->{transdatefrom}) {
 407     my $fromdate = $::locale->parse_date_to_object($::form->{transdatefrom});
 408     if ( ref($fromdate) eq 'DateTime' ) {
 409       push @where_sale,     ('transdate' => { ge => $fromdate});
 410       push @where_purchase, ('transdate' => { ge => $fromdate});
 411     };
 412   }
 413
 414   if ($::form->{transdateto}) {
 415     my $todate = $::locale->parse_date_to_object($::form->{transdateto});
 416     if ( ref($todate) eq 'DateTime' ) {
 417       $todate->add(days => 1);
 418       push @where_sale,     ('transdate' => { lt => $todate});
 419       push @where_purchase, ('transdate' => { lt => $todate});
 420     };
 421   }
 422
 423   my $all_open_ar_invoices = SL::DB::Manager::Invoice        ->get_all(where => \@where_sale,     with_objects => 'customer');
 424   my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => \@where_purchase, with_objects => 'vendor');
 425
 426   my @all_open_invoices = @{ $all_open_ar_invoices };
 427   # add ap invoices, filtering out subcent open amounts
 428   push @all_open_invoices, grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
 429
 430   @all_open_invoices = sort { $a->id <=> $b->id } @all_open_invoices;
 431
 432   my $output  = $self->render(
 433     'bank_transactions/add_list',
 434     { output => 0 },
 435     INVOICES => \@all_open_invoices,
 436   );
 437
 438   my %result = ( count => 0, html => $output );
 439
 440   $self->render(\to_json(\%result), { type => 'json', process => 0 });
 441 }
 442
 443 sub action_ajax_accept_invoices {
 444   my ($self) = @_;
 445
 446   my @selected_invoices;
 447   foreach my $invoice_id (@{ $::form->{invoice_id} || [] }) {
 448     my $invoice_object = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
 449     push @selected_invoices, $invoice_object;
 450   }
 451
 452   $self->render(
 453     'bank_transactions/invoices',
 454     { layout => 0 },
 455     INVOICES => \@selected_invoices,
 456     bt_id    => $::form->{bt_id},
 457   );
 458 }
 459
 460 sub save_invoices {
 461   my ($self) = @_;
 462
 463   return 0 if !$::form->{invoice_ids};
 464
 465   my %invoice_hash = %{ delete $::form->{invoice_ids} };  # each key (the bt line with a bt_id) contains an array of invoice_ids
 466
 467   # e.g. three partial payments with bt_ids 54, 55 and 56 for invoice with id 74:
 468   # $invoice_hash = {
 469   #         '55' => [
 470   #                 '74'
 471   #               ],
 472   #         '54' => [
 473   #                 '74'
 474   #               ],
 475   #         '56' => [
 476   #                 '74'
 477   #               ]
 478   #       };
 479   #
 480   # or if the payment with bt_id 44 is used to pay invoices with ids 50, 51 and 52
 481   # $invoice_hash = {
 482   #           '44' => [ '50', '51', 52' ]
 483   #         };
 484
 485   $::form->{invoice_skontos} ||= {}; # hash of arrays containing the payment types, could be empty
 486
 487   # a bank_transaction may be assigned to several invoices, i.e. a customer
 488   # might pay several open invoices with one transaction
 489
 490   $self->problems([]);
 491
 492   my $count = 0;
 493
 494   if ( $::form->{proposal_ids} ) {
 495     foreach (@{ $::form->{proposal_ids} }) {
 496       my  $bank_transaction_id = $_;
 497       my  $invoice_ids = $invoice_hash{$_};
 498       push @{ $self->problems }, $self->save_single_bank_transaction(
 499         bank_transaction_id => $bank_transaction_id,
 500         invoice_ids         => $invoice_ids,
 501       );
 502       $count += scalar( @{$invoice_ids} );
 503     }
 504   } else {
 505     while ( my ($bank_transaction_id, $invoice_ids) = each(%invoice_hash) ) {
 506       push @{ $self->problems }, $self->save_single_bank_transaction(
 507         bank_transaction_id => $bank_transaction_id,
 508         invoice_ids         => $invoice_ids,
 509       );
 510       $count += scalar( @{$invoice_ids} );
 511     }
 512   }
 513   return $count;
 514 }
 515
 516 sub action_save_invoices {
 517   my ($self) = @_;
 518   my $count = $self->save_invoices();
 519
 520   flash('ok', t8('#1 invoice(s) saved.', $count));
 521
 522   $self->action_list();
 523 }
 524
 525 sub action_save_proposals {
 526   my ($self) = @_;
 527   if ( $::form->{proposal_ids} ) {
 528     my $propcount = scalar(@{ $::form->{proposal_ids} });
 529     if ( $propcount > 0 ) {
 530       my $count = $self->save_invoices();
 531
 532       flash('ok', t8('#1 proposal(s) with #2 invoice(s) saved.',  $propcount, $count));
 533     }
 534   }
 535   $self->action_list();
 536
 537 }
 538
 539 sub is_collective_transaction {
 540   my ($self, $bt) = @_;
 541   return $bt->transaction_code eq "191";
 542 }
 543
 544 sub save_single_bank_transaction {
 545   my ($self, %params) = @_;
 546
 547   my %data = (
 548     %params,
 549     bank_transaction => SL::DB::Manager::BankTransaction->find_by(id => $params{bank_transaction_id}),
 550     invoices         => [],
 551   );
 552
 553   if (!$data{bank_transaction}) {
 554     return {
 555       %data,
 556       result => 'error',
 557       message => $::locale->text('The ID #1 is not a valid database ID.', $data{bank_transaction_id}),
 558     };
 559   }
 560
 561   my (@warnings);
 562
 563   my $worker = sub {
 564     my $bt_id                 = $data{bank_transaction_id};
 565     my $bank_transaction      = $data{bank_transaction};
 566     my $sign                  = $bank_transaction->amount < 0 ? -1 : 1;
 567     my $amount_of_transaction = $sign * $bank_transaction->amount;
 568     my $payment_received      = $bank_transaction->amount > 0;
 569     my $payment_sent          = $bank_transaction->amount < 0;
 570
 571     foreach my $invoice_id (@{ $params{invoice_ids} }) {
 572       my $invoice = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
 573       if (!$invoice) {
 574         return {
 575           %data,
 576           result  => 'error',
 577           message => $::locale->text("The ID #1 is not a valid database ID.", $invoice_id),
 578         };
 579       }
 580
 581       push @{ $data{invoices} }, $invoice;
 582     }
 583
 584     if (   $payment_received
 585         && any {    ( $_->is_sales && ($_->amount < 0))
 586                  || (!$_->is_sales && ($_->amount > 0))
 587                } @{ $data{invoices} }) {
 588       return {
 589         %data,
 590         result  => 'error',
 591         message => $::locale->text("Received payments can only be posted for sales invoices and purchase credit notes."),
 592       };
 593     }
 594
 595     if (   $payment_sent
 596         && any {    ( $_->is_sales && ($_->amount > 0))
 597                  || (!$_->is_sales && ($_->amount < 0))
 598                } @{ $data{invoices} }) {
 599       return {
 600         %data,
 601         result  => 'error',
 602         message => $::locale->text("Sent payments can only be posted for purchase invoices and sales credit notes."),
 603       };
 604     }
 605
 606     my $max_invoices = scalar(@{ $data{invoices} });
 607     my $n_invoices   = 0;
 608
 609     foreach my $invoice (@{ $data{invoices} }) {
 610
 611       $n_invoices++ ;
 612
 613       # Check if bank_transaction already has a link to the invoice, may only be linked once per invoice
 614       # This might be caused by the user reloading a page and resending the form
 615       if (_existing_record_link($bank_transaction, $invoice)) {
 616         return {
 617           %data,
 618           result  => 'error',
 619           message => $::locale->text("Bank transaction with id #1 has already been linked to #2.", $bank_transaction->id, $invoice->displayable_name),
 620         };
 621       }
 622
 623       if (!$amount_of_transaction && $invoice->open_amount) {
 624         return {
 625           %data,
 626           result  => 'error',
 627           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."),
 628         };
 629       }
 630
 631       my $payment_type;
 632       if ( defined $::form->{invoice_skontos}->{"$bt_id"} ) {
 633         $payment_type = shift(@{ $::form->{invoice_skontos}->{"$bt_id"} });
 634       } else {
 635         $payment_type = 'without_skonto';
 636       };
 637
 638       # pay invoice or go to the next bank transaction if the amount is not sufficiently high
 639       if ($invoice->open_amount <= $amount_of_transaction && $n_invoices < $max_invoices) {
 640         my $open_amount = ($payment_type eq 'with_skonto_pt'?$invoice->amount_less_skonto:$invoice->open_amount);
 641         # first calculate new bank transaction amount ...
 642         if ($invoice->is_sales) {
 643           $amount_of_transaction -= $sign * $open_amount;
 644           $bank_transaction->invoice_amount($bank_transaction->invoice_amount + $open_amount);
 645         } else {
 646           $amount_of_transaction += $sign * $open_amount;
 647           $bank_transaction->invoice_amount($bank_transaction->invoice_amount - $open_amount);
 648         }
 649         # ... and then pay the invoice
 650         $invoice->pay_invoice(chart_id     => $bank_transaction->local_bank_account->chart_id,
 651                               trans_id     => $invoice->id,
 652                               amount       => $open_amount,
 653                               payment_type => $payment_type,
 654                               transdate    => $bank_transaction->transdate->to_kivitendo);
 655       } else { # use the whole amount of the bank transaction for the invoice, overpay the invoice if necessary
 656         my $overpaid_amount = $amount_of_transaction - $invoice->open_amount;
 657         $invoice->pay_invoice(chart_id     => $bank_transaction->local_bank_account->chart_id,
 658                               trans_id     => $invoice->id,
 659                               amount       => $amount_of_transaction,
 660                               payment_type => $payment_type,
 661                               transdate    => $bank_transaction->transdate->to_kivitendo);
 662         $bank_transaction->invoice_amount($bank_transaction->amount);
 663         $amount_of_transaction = 0;
 664
 665         if ($overpaid_amount >= 0.01) {
 666           push @warnings, {
 667             %data,
 668             result  => 'warning',
 669             message => $::locale->text('Invoice #1 was overpaid by #2.', $invoice->invnumber, $::form->format_amount(\%::myconfig, $overpaid_amount, 2)),
 670           };
 671         }
 672       }
 673       # Record a record link from the bank transaction to the invoice
 674       my @props = (
 675         from_table => 'bank_transactions',
 676         from_id    => $bt_id,
 677         to_table   => $invoice->is_sales ? 'ar' : 'ap',
 678         to_id      => $invoice->id,
 679       );
 680
 681       SL::DB::RecordLink->new(@props)->save;
 682
 683       # "close" a sepa_export_item if it exists
 684       # code duplicated in action_save_proposals!
 685       # currently only works, if there is only exactly one open sepa_export_item
 686       if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
 687         if ( scalar @$seis == 1 ) {
 688           # moved the execution and the check for sepa_export into a method,
 689           # this isn't part of a transaction, though
 690           $seis->[0]->set_executed if $invoice->id == $seis->[0]->arap_id;
 691         }
 692       }
 693
 694     }
 695     $bank_transaction->save;
 696
 697     # 'undef' means 'no error' here.
 698     return undef;
 699   };
 700
 701   my $error;
 702   my $rez = $data{bank_transaction}->db->with_transaction(sub {
 703     eval {
 704       $error = $worker->();
 705       1;
 706
 707     } or do {
 708       $error = {
 709         %data,
 710         result  => 'error',
 711         message => $@,
 712       };
 713     };
 714
 715     die if $error;
 716   });
 717
 718   return grep { $_ } ($error, @warnings);
 719 }
 720
 721 #
 722 # filters
 723 #
 724
 725 sub check_auth {
 726   $::auth->assert('bank_transaction');
 727 }
 728
 729 #
 730 # helpers
 731 #
 732
 733 sub make_filter_summary {
 734   my ($self) = @_;
 735
 736   my $filter = $::form->{filter} || {};
 737   my @filter_strings;
 738
 739   my @filters = (
 740     [ $filter->{"transdate:date::ge"},      $::locale->text('Transdate')  . " " . $::locale->text('From Date') ],
 741     [ $filter->{"transdate:date::le"},      $::locale->text('Transdate')  . " " . $::locale->text('To Date')   ],
 742     [ $filter->{"valutadate:date::ge"},     $::locale->text('Valutadate') . " " . $::locale->text('From Date') ],
 743     [ $filter->{"valutadate:date::le"},     $::locale->text('Valutadate') . " " . $::locale->text('To Date')   ],
 744     [ $filter->{"amount:number"},           $::locale->text('Amount')                                          ],
 745     [ $filter->{"bank_account_id:integer"}, $::locale->text('Local bank account')                              ],
 746   );
 747
 748   for (@filters) {
 749     push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
 750   }
 751
 752   $self->{filter_summary} = join ', ', @filter_strings;
 753 }
 754
 755 sub prepare_report {
 756   my ($self)      = @_;
 757
 758   my $callback    = $self->models->get_callback;
 759
 760   my $report      = SL::ReportGenerator->new(\%::myconfig, $::form);
 761   $self->{report} = $report;
 762
 763   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);
 764   my @sortable    = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount                                  purpose local_account_number local_bank_code);
 765
 766   my %column_defs = (
 767     transdate             => { sub   => sub { $_[0]->transdate_as_date } },
 768     valutadate            => { sub   => sub { $_[0]->valutadate_as_date } },
 769     remote_name           => { },
 770     remote_account_number => { },
 771     remote_bank_code      => { },
 772     amount                => { sub   => sub { $_[0]->amount_as_number },
 773                                align => 'right' },
 774     invoice_amount        => { sub   => sub { $_[0]->invoice_amount_as_number },
 775                                align => 'right' },
 776     invoices              => { sub   => sub { $_[0]->linked_invoices } },
 777     currency              => { sub   => sub { $_[0]->currency->name } },
 778     purpose               => { },
 779     local_account_number  => { sub   => sub { $_[0]->local_bank_account->account_number } },
 780     local_bank_code       => { sub   => sub { $_[0]->local_bank_account->bank_code } },
 781     local_bank_name       => { sub   => sub { $_[0]->local_bank_account->name } },
 782     id                    => {},
 783   );
 784
 785   map { $column_defs{$_}->{text} ||= $::locale->text( $self->models->get_sort_spec->{$_}->{title} ) } keys %column_defs;
 786
 787   $report->set_options(
 788     std_column_visibility => 1,
 789     controller_class      => 'BankTransaction',
 790     output_format         => 'HTML',
 791     top_info_text         => $::locale->text('Bank transactions'),
 792     title                 => $::locale->text('Bank transactions'),
 793     allow_pdf_export      => 1,
 794     allow_csv_export      => 1,
 795   );
 796   $report->set_columns(%column_defs);
 797   $report->set_column_order(@columns);
 798   $report->set_export_options(qw(list_all filter));
 799   $report->set_options_from_form;
 800   $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
 801   $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
 802
 803   my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted();
 804
 805   $report->set_options(
 806     raw_top_info_text     => $self->render('bank_transactions/report_top',    { output => 0 }, BANK_ACCOUNTS => $bank_accounts),
 807     raw_bottom_info_text  => $self->render('bank_transactions/report_bottom', { output => 0 }),
 808   );
 809 }
 810
 811 sub _existing_record_link {
 812   my ($bt, $invoice) = @_;
 813
 814   # check whether a record link from banktransaction $bt already exists to
 815   # invoice $invoice, returns 1 if that is the case
 816
 817   die unless $bt->isa("SL::DB::BankTransaction") && ( $invoice->isa("SL::DB::Invoice") || $invoice->isa("SL::DB::PurchaseInvoice") );
 818
 819   my $linked_record_to_table = $invoice->is_sales ? 'Invoice' : 'PurchaseInvoice';
 820   my $linked_records = $bt->linked_records( direction => 'to', to => $linked_record_to_table, query => [ id => $invoice->id ]  );
 821
 822   return @$linked_records ? 1 : 0;
 823 };
 824
 825 sub init_problems { [] }
 826
 827 sub init_models {
 828   my ($self) = @_;
 829
 830   SL::Controller::Helper::GetModels->new(
 831     controller => $self,
 832     sorted     => {
 833       _default => {
 834         by  => 'transdate',
 835         dir => 0,   # 1 = ASC, 0 = DESC : default sort is newest at top
 836       },
 837       transdate             => t8('Transdate'),
 838       remote_name           => t8('Remote name'),
 839       amount                => t8('Amount'),
 840       invoice_amount        => t8('Assigned'),
 841       invoices              => t8('Linked invoices'),
 842       valutadate            => t8('Valutadate'),
 843       remote_account_number => t8('Remote account number'),
 844       remote_bank_code      => t8('Remote bank code'),
 845       currency              => t8('Currency'),
 846       purpose               => t8('Purpose'),
 847       local_account_number  => t8('Local account number'),
 848       local_bank_code       => t8('Local bank code'),
 849       local_bank_name       => t8('Bank account'),
 850     },
 851     with_objects => [ 'local_bank_account', 'currency' ],
 852   );
 853 }
 854
 855 1;
 856 __END__
 857
 858 =pod
 859
 860 =encoding utf8
 861
 862 =head1 NAME
 863
 864 SL::Controller::BankTransaction - Posting payments to invoices from
 865 bank transactions imported earlier
 866
 867 =head1 FUNCTIONS
 868
 869 =over 4
 870
 871 =item C<save_single_bank_transaction %params>
 872
 873 Takes a bank transaction ID (as parameter C<bank_transaction_id> and
 874 tries to post its amount to a certain number of invoices (parameter
 875 C<invoice_ids>, an array ref of database IDs to purchase or sales
 876 invoice objects).
 877
 878 The whole function is wrapped in a database transaction. If an
 879 exception occurs the bank transaction is not posted at all. The same
 880 is true if the code detects an error during the execution, e.g. a bank
 881 transaction that's already been posted earlier. In both cases the
 882 database transaction will be rolled back.
 883
 884 If warnings but not errors occur the database transaction is still
 885 committed.
 886
 887 The return value is an error object or C<undef> if the function
 888 succeeded. The calling function will collect all warnings and errors
 889 and display them in a nicely formatted table if any occurred.
 890
 891 An error object is a hash reference containing the following members:
 892
 893 =over 2
 894
 895 =item * C<result> — can be either C<warning> or C<error>. Warnings are
 896 displayed slightly different than errors.
 897
 898 =item * C<message> — a human-readable message included in the list of
 899 errors meant as the description of why the problem happened
 900
 901 =item * C<bank_transaction_id>, C<invoice_ids> — the same parameters
 902 that the function was called with
 903
 904 =item * C<bank_transaction> — the database object
 905 (C<SL::DB::BankTransaction>) corresponding to C<bank_transaction_id>
 906
 907 =item * C<invoices> — an array ref of the database objects (either
 908 C<SL::DB::Invoice> or C<SL::DB::PurchaseInvoice>) corresponding to
 909 C<invoice_ids>
 910
 911 =back
 912
 913 =back
 914
 915 =head1 AUTHOR
 916
 917 Niclas Zimmermann E<lt>niclas@kivitendo-premium.deE<gt>,
 918 Geoffrey Richardson E<lt>information@richardson-bueren.deE<gt>
 919
 920 =cut