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