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         sources             => ($::form->{sources} // {})->{$_},
 515         memos               => ($::form->{memos}   // {})->{$_},
 516       );
 517       $count += scalar( @{$invoice_ids} );
 518     }
 519   } else {
 520     while ( my ($bank_transaction_id, $invoice_ids) = each(%invoice_hash) ) {
 521       push @{ $self->problems }, $self->save_single_bank_transaction(
 522         bank_transaction_id => $bank_transaction_id,
 523         invoice_ids         => $invoice_ids,
 524       );
 525       $count += scalar( @{$invoice_ids} );
 526     }
 527   }
 528   return $count;
 529 }
 530
 531 sub action_save_invoices {
 532   my ($self) = @_;
 533   my $count = $self->save_invoices();
 534
 535   flash('ok', t8('#1 invoice(s) saved.', $count));
 536
 537   $self->action_list();
 538 }
 539
 540 sub action_save_proposals {
 541   my ($self) = @_;
 542
 543   if ( $::form->{proposal_ids} ) {
 544     my $propcount = scalar(@{ $::form->{proposal_ids} });
 545     if ( $propcount > 0 ) {
 546       my $count = $self->save_invoices();
 547
 548       flash('ok', t8('#1 proposal(s) with #2 invoice(s) saved.',  $propcount, $count));
 549     }
 550   }
 551   $self->action_list();
 552
 553 }
 554
 555 sub is_collective_transaction {
 556   my ($self, $bt) = @_;
 557   return $bt->transaction_code eq "191";
 558 }
 559
 560 sub save_single_bank_transaction {
 561   my ($self, %params) = @_;
 562
 563   my %data = (
 564     %params,
 565     bank_transaction => SL::DB::Manager::BankTransaction->find_by(id => $params{bank_transaction_id}),
 566     invoices         => [],
 567   );
 568
 569   if (!$data{bank_transaction}) {
 570     return {
 571       %data,
 572       result => 'error',
 573       message => $::locale->text('The ID #1 is not a valid database ID.', $data{bank_transaction_id}),
 574     };
 575   }
 576
 577   my (@warnings);
 578
 579   my $worker = sub {
 580     my $bt_id                 = $data{bank_transaction_id};
 581     my $bank_transaction      = $data{bank_transaction};
 582     my $sign                  = $bank_transaction->amount < 0 ? -1 : 1;
 583     my $amount_of_transaction = $sign * $bank_transaction->amount;
 584     my $payment_received      = $bank_transaction->amount > 0;
 585     my $payment_sent          = $bank_transaction->amount < 0;
 586
 587
 588     foreach my $invoice_id (@{ $params{invoice_ids} }) {
 589       my $invoice = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
 590       if (!$invoice) {
 591         return {
 592           %data,
 593           result  => 'error',
 594           message => $::locale->text("The ID #1 is not a valid database ID.", $invoice_id),
 595         };
 596       }
 597       push @{ $data{invoices} }, $invoice;
 598     }
 599
 600     if (   $payment_received
 601         && any {    ( $_->is_sales && ($_->amount < 0))
 602                  || (!$_->is_sales && ($_->amount > 0))
 603                } @{ $data{invoices} }) {
 604       return {
 605         %data,
 606         result  => 'error',
 607         message => $::locale->text("Received payments can only be posted for sales invoices and purchase credit notes."),
 608       };
 609     }
 610
 611     if (   $payment_sent
 612         && any {    ( $_->is_sales && ($_->amount > 0))
 613                  || (!$_->is_sales && ($_->amount < 0))
 614                } @{ $data{invoices} }) {
 615       return {
 616         %data,
 617         result  => 'error',
 618         message => $::locale->text("Sent payments can only be posted for purchase invoices and sales credit notes."),
 619       };
 620     }
 621
 622     my $max_invoices = scalar(@{ $data{invoices} });
 623     my $n_invoices   = 0;
 624
 625     foreach my $invoice (@{ $data{invoices} }) {
 626       my $source = ($data{sources} // [])->[$n_invoices];
 627       my $memo   = ($data{memos}   // [])->[$n_invoices];
 628
 629       $n_invoices++ ;
 630
 631       # Check if bank_transaction already has a link to the invoice, may only be linked once per invoice
 632       # This might be caused by the user reloading a page and resending the form
 633       if (_existing_record_link($bank_transaction, $invoice)) {
 634         return {
 635           %data,
 636           result  => 'error',
 637           message => $::locale->text("Bank transaction with id #1 has already been linked to #2.", $bank_transaction->id, $invoice->displayable_name),
 638         };
 639       }
 640
 641       if (!$amount_of_transaction && $invoice->open_amount) {
 642         return {
 643           %data,
 644           result  => 'error',
 645           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."),
 646         };
 647       }
 648
 649       my $payment_type;
 650       if ( defined $::form->{invoice_skontos}->{"$bt_id"} ) {
 651         $payment_type = shift(@{ $::form->{invoice_skontos}->{"$bt_id"} });
 652       } else {
 653         $payment_type = 'without_skonto';
 654       };
 655
 656
 657       # pay invoice or go to the next bank transaction if the amount is not sufficiently high
 658       if ($invoice->open_amount <= $amount_of_transaction && $n_invoices < $max_invoices) {
 659         my $open_amount = ($payment_type eq 'with_skonto_pt'?$invoice->amount_less_skonto:$invoice->open_amount);
 660         # first calculate new bank transaction amount ...
 661         if ($invoice->is_sales) {
 662           $amount_of_transaction -= $sign * $open_amount;
 663           $bank_transaction->invoice_amount($bank_transaction->invoice_amount + $open_amount);
 664         } else {
 665           $amount_of_transaction += $sign * $open_amount;
 666           $bank_transaction->invoice_amount($bank_transaction->invoice_amount - $open_amount);
 667         }
 668         # ... and then pay the invoice
 669         $invoice->pay_invoice(chart_id     => $bank_transaction->local_bank_account->chart_id,
 670                               trans_id     => $invoice->id,
 671                               amount       => $open_amount,
 672                               payment_type => $payment_type,
 673                               source       => $source,
 674                               memo         => $memo,
 675                               transdate    => $bank_transaction->transdate->to_kivitendo);
 676       } elsif ( $invoice->is_sales && $invoice->type eq 'credit_note' ) {
 677         # no check for overpayment/multiple payments
 678         $invoice->pay_invoice(chart_id     => $bank_transaction->local_bank_account->chart_id,
 679                               trans_id     => $invoice->id,
 680                               amount       => $invoice->open_amount,
 681                               payment_type => $payment_type,
 682                               source       => $source,
 683                               memo         => $memo,
 684                               transdate    => $bank_transaction->transdate->to_kivitendo);
 685       } else { # use the whole amount of the bank transaction for the invoice, overpay the invoice if necessary
 686         my $overpaid_amount = $amount_of_transaction - $invoice->open_amount;
 687         $invoice->pay_invoice(chart_id     => $bank_transaction->local_bank_account->chart_id,
 688                               trans_id     => $invoice->id,
 689                               amount       => $amount_of_transaction,
 690                               payment_type => $payment_type,
 691                               source       => $source,
 692                               memo         => $memo,
 693                               transdate    => $bank_transaction->transdate->to_kivitendo);
 694         $bank_transaction->invoice_amount($bank_transaction->amount);
 695         $amount_of_transaction = 0;
 696
 697         if ($overpaid_amount >= 0.01) {
 698           push @warnings, {
 699             %data,
 700             result  => 'warning',
 701             message => $::locale->text('Invoice #1 was overpaid by #2.', $invoice->invnumber, $::form->format_amount(\%::myconfig, $overpaid_amount, 2)),
 702           };
 703         }
 704       }
 705       # Record a record link from the bank transaction to the invoice
 706       my @props = (
 707         from_table => 'bank_transactions',
 708         from_id    => $bt_id,
 709         to_table   => $invoice->is_sales ? 'ar' : 'ap',
 710         to_id      => $invoice->id,
 711       );
 712
 713       SL::DB::RecordLink->new(@props)->save;
 714
 715       # "close" a sepa_export_item if it exists
 716       # code duplicated in action_save_proposals!
 717       # currently only works, if there is only exactly one open sepa_export_item
 718       if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
 719         if ( scalar @$seis == 1 ) {
 720           # moved the execution and the check for sepa_export into a method,
 721           # this isn't part of a transaction, though
 722           $seis->[0]->set_executed if $invoice->id == $seis->[0]->arap_id;
 723         }
 724       }
 725
 726     }
 727     $bank_transaction->save;
 728
 729     # 'undef' means 'no error' here.
 730     return undef;
 731   };
 732
 733   my $error;
 734   my $rez = $data{bank_transaction}->db->with_transaction(sub {
 735     eval {
 736       $error = $worker->();
 737       1;
 738
 739     } or do {
 740       $error = {
 741         %data,
 742         result  => 'error',
 743         message => $@,
 744       };
 745     };
 746
 747     die if $error;
 748   });
 749
 750   return grep { $_ } ($error, @warnings);
 751 }
 752
 753 #
 754 # filters
 755 #
 756
 757 sub check_auth {
 758   $::auth->assert('bank_transaction');
 759 }
 760
 761 #
 762 # helpers
 763 #
 764
 765 sub make_filter_summary {
 766   my ($self) = @_;
 767
 768   my $filter = $::form->{filter} || {};
 769   my @filter_strings;
 770
 771   my @filters = (
 772     [ $filter->{"transdate:date::ge"},      $::locale->text('Transdate')  . " " . $::locale->text('From Date') ],
 773     [ $filter->{"transdate:date::le"},      $::locale->text('Transdate')  . " " . $::locale->text('To Date')   ],
 774     [ $filter->{"valutadate:date::ge"},     $::locale->text('Valutadate') . " " . $::locale->text('From Date') ],
 775     [ $filter->{"valutadate:date::le"},     $::locale->text('Valutadate') . " " . $::locale->text('To Date')   ],
 776     [ $filter->{"amount:number"},           $::locale->text('Amount')                                          ],
 777     [ $filter->{"bank_account_id:integer"}, $::locale->text('Local bank account')                              ],
 778   );
 779
 780   for (@filters) {
 781     push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
 782   }
 783
 784   $self->{filter_summary} = join ', ', @filter_strings;
 785 }
 786
 787 sub prepare_report {
 788   my ($self)      = @_;
 789
 790   my $callback    = $self->models->get_callback;
 791
 792   my $report      = SL::ReportGenerator->new(\%::myconfig, $::form);
 793   $self->{report} = $report;
 794
 795   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);
 796   my @sortable    = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount                                  purpose local_account_number local_bank_code);
 797
 798   my %column_defs = (
 799     transdate             => { sub   => sub { $_[0]->transdate_as_date } },
 800     valutadate            => { sub   => sub { $_[0]->valutadate_as_date } },
 801     remote_name           => { },
 802     remote_account_number => { },
 803     remote_bank_code      => { },
 804     amount                => { sub   => sub { $_[0]->amount_as_number },
 805                                align => 'right' },
 806     invoice_amount        => { sub   => sub { $_[0]->invoice_amount_as_number },
 807                                align => 'right' },
 808     invoices              => { sub   => sub { $_[0]->linked_invoices } },
 809     currency              => { sub   => sub { $_[0]->currency->name } },
 810     purpose               => { },
 811     local_account_number  => { sub   => sub { $_[0]->local_bank_account->account_number } },
 812     local_bank_code       => { sub   => sub { $_[0]->local_bank_account->bank_code } },
 813     local_bank_name       => { sub   => sub { $_[0]->local_bank_account->name } },
 814     id                    => {},
 815   );
 816
 817   map { $column_defs{$_}->{text} ||= $::locale->text( $self->models->get_sort_spec->{$_}->{title} ) } keys %column_defs;
 818
 819   $report->set_options(
 820     std_column_visibility => 1,
 821     controller_class      => 'BankTransaction',
 822     output_format         => 'HTML',
 823     top_info_text         => $::locale->text('Bank transactions'),
 824     title                 => $::locale->text('Bank transactions'),
 825     allow_pdf_export      => 1,
 826     allow_csv_export      => 1,
 827   );
 828   $report->set_columns(%column_defs);
 829   $report->set_column_order(@columns);
 830   $report->set_export_options(qw(list_all filter));
 831   $report->set_options_from_form;
 832   $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
 833   $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
 834
 835   my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted();
 836
 837   $report->set_options(
 838     raw_top_info_text     => $self->render('bank_transactions/report_top',    { output => 0 }, BANK_ACCOUNTS => $bank_accounts),
 839     raw_bottom_info_text  => $self->render('bank_transactions/report_bottom', { output => 0 }),
 840   );
 841 }
 842
 843 sub _existing_record_link {
 844   my ($bt, $invoice) = @_;
 845
 846   # check whether a record link from banktransaction $bt already exists to
 847   # invoice $invoice, returns 1 if that is the case
 848
 849   die unless $bt->isa("SL::DB::BankTransaction") && ( $invoice->isa("SL::DB::Invoice") || $invoice->isa("SL::DB::PurchaseInvoice") );
 850
 851   my $linked_record_to_table = $invoice->is_sales ? 'Invoice' : 'PurchaseInvoice';
 852   my $linked_records = $bt->linked_records( direction => 'to', to => $linked_record_to_table, query => [ id => $invoice->id ]  );
 853
 854   return @$linked_records ? 1 : 0;
 855 };
 856
 857 sub init_problems { [] }
 858
 859 sub init_models {
 860   my ($self) = @_;
 861
 862   SL::Controller::Helper::GetModels->new(
 863     controller => $self,
 864     sorted     => {
 865       _default => {
 866         by  => 'transdate',
 867         dir => 0,   # 1 = ASC, 0 = DESC : default sort is newest at top
 868       },
 869       transdate             => t8('Transdate'),
 870       remote_name           => t8('Remote name'),
 871       amount                => t8('Amount'),
 872       invoice_amount        => t8('Assigned'),
 873       invoices              => t8('Linked invoices'),
 874       valutadate            => t8('Valutadate'),
 875       remote_account_number => t8('Remote account number'),
 876       remote_bank_code      => t8('Remote bank code'),
 877       currency              => t8('Currency'),
 878       purpose               => t8('Purpose'),
 879       local_account_number  => t8('Local account number'),
 880       local_bank_code       => t8('Local bank code'),
 881       local_bank_name       => t8('Bank account'),
 882     },
 883     with_objects => [ 'local_bank_account', 'currency' ],
 884   );
 885 }
 886
 887 1;
 888 __END__
 889
 890 =pod
 891
 892 =encoding utf8
 893
 894 =head1 NAME
 895
 896 SL::Controller::BankTransaction - Posting payments to invoices from
 897 bank transactions imported earlier
 898
 899 =head1 FUNCTIONS
 900
 901 =over 4
 902
 903 =item C<save_single_bank_transaction %params>
 904
 905 Takes a bank transaction ID (as parameter C<bank_transaction_id> and
 906 tries to post its amount to a certain number of invoices (parameter
 907 C<invoice_ids>, an array ref of database IDs to purchase or sales
 908 invoice objects).
 909
 910 The whole function is wrapped in a database transaction. If an
 911 exception occurs the bank transaction is not posted at all. The same
 912 is true if the code detects an error during the execution, e.g. a bank
 913 transaction that's already been posted earlier. In both cases the
 914 database transaction will be rolled back.
 915
 916 If warnings but not errors occur the database transaction is still
 917 committed.
 918
 919 The return value is an error object or C<undef> if the function
 920 succeeded. The calling function will collect all warnings and errors
 921 and display them in a nicely formatted table if any occurred.
 922
 923 An error object is a hash reference containing the following members:
 924
 925 =over 2
 926
 927 =item * C<result> — can be either C<warning> or C<error>. Warnings are
 928 displayed slightly different than errors.
 929
 930 =item * C<message> — a human-readable message included in the list of
 931 errors meant as the description of why the problem happened
 932
 933 =item * C<bank_transaction_id>, C<invoice_ids> — the same parameters
 934 that the function was called with
 935
 936 =item * C<bank_transaction> — the database object
 937 (C<SL::DB::BankTransaction>) corresponding to C<bank_transaction_id>
 938
 939 =item * C<invoices> — an array ref of the database objects (either
 940 C<SL::DB::Invoice> or C<SL::DB::PurchaseInvoice>) corresponding to
 941 C<invoice_ids>
 942
 943 =back
 944
 945 =back
 946
 947 =head1 AUTHOR
 948
 949 Niclas Zimmermann E<lt>niclas@kivitendo-premium.deE<gt>,
 950 Geoffrey Richardson E<lt>information@richardson-bueren.deE<gt>
 951
 952 =cut