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