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