SL/Controller/BankTransaction.pm

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