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