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::DB::ReconciliationLink;
  21 use SL::JSON;
  22 use SL::DB::Chart;
  23 use SL::DB::AccTransaction;
  24 use SL::DB::BankTransactionAccTrans;
  25 use SL::DB::Tax;
  26 use SL::DB::BankAccount;
  27 use SL::DB::GLTransaction;
  28 use SL::DB::RecordTemplate;
  29 use SL::DB::SepaExportItem;
  30 use SL::DBUtils qw(like do_query);
  31
  32 use SL::Presenter::Tag qw(checkbox_tag html_tag);
  33 use Carp;
  34 use List::UtilsBy qw(partition_by);
  35 use List::MoreUtils qw(any);
  36 use List::Util qw(max);
  37
  38 use Rose::Object::MakeMethods::Generic
  39 (
  40   scalar                  => [ qw(callback transaction) ],
  41   'scalar --get_set_init' => [ qw(models problems) ],
  42 );
  43
  44 __PACKAGE__->run_before('check_auth');
  45
  46
  47 #
  48 # actions
  49 #
  50
  51 sub action_search {
  52   my ($self) = @_;
  53
  54   my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted( query => [ obsolete => 0 ] );
  55
  56   $self->setup_search_action_bar;
  57   $self->render('bank_transactions/search',
  58                  BANK_ACCOUNTS => $bank_accounts,
  59                  title         => t8('Search bank transactions'),
  60                );
  61 }
  62
  63 sub action_list_all {
  64   my ($self) = @_;
  65
  66   $self->make_filter_summary;
  67   $self->prepare_report;
  68
  69   $self->setup_list_all_action_bar;
  70   $self->report_generator_list_objects(report => $self->{report}, objects => $self->models->get);
  71 }
  72
  73 sub gather_bank_transactions_and_proposals {
  74   my ($self, %params) = @_;
  75
  76   my $sort_by = $params{sort_by} || 'transdate';
  77   $sort_by = 'transdate' if $sort_by eq 'proposal';
  78   $sort_by .= $params{sort_dir} ? ' DESC' : ' ASC';
  79
  80   my @where = ();
  81   push @where, (transdate => { ge => $params{fromdate} }) if $params{fromdate};
  82   push @where, (transdate => { lt => $params{todate} })   if $params{todate};
  83   # bank_transactions no younger than starting date,
  84   # including starting date (same search behaviour as fromdate)
  85   # but OPEN invoices to be matched may be from before
  86   if ( $params{bank_account}->reconciliation_starting_date ) {
  87     push @where, (transdate => { ge => $params{bank_account}->reconciliation_starting_date });
  88   };
  89
  90   my $bank_transactions = SL::DB::Manager::BankTransaction->get_all(
  91     with_objects => [ 'local_bank_account', 'currency' ],
  92     sort_by      => $sort_by,
  93     limit        => 10000,
  94     where        => [
  95       amount                => {ne => \'invoice_amount'},      # '} make emacs happy
  96       local_bank_account_id => $params{bank_account}->id,
  97       cleared               => 0,
  98       @where
  99     ],
 100   );
 101
 102   my $has_batch_transaction = (grep { $_->is_batch_transaction } @{ $bank_transactions }) ? 1 : undef;
 103
 104   # credit notes have a negative amount, treat differently
 105   my $all_open_ar_invoices = SL::DB::Manager::Invoice->get_all(where        => [ or => [ amount => { gt => \'paid' },                 # '} make emacs happy
 106                                                                                          and    => [ type    => 'credit_note',
 107                                                                                                      amount  => { lt => \'paid' }     # '} make emacs happy
 108                                                                                          ],
 109                                                                                  ],
 110                                                                ],
 111                                                                with_objects => ['customer','payment_terms']);
 112
 113   my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where        => [amount => { ne => \'paid' }],                 #  '}] make emacs happy
 114                                                                        with_objects => ['vendor'  ,'payment_terms']);
 115
 116   my @all_open_invoices;
 117   # filter out invoices with less than 1 cent outstanding
 118   push @all_open_invoices, map { $_->{is_ar}=1 ; $_ } grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ar_invoices };
 119   push @all_open_invoices, map { $_->{is_ar}=0 ; $_ } grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
 120
 121
 122   my (%sepa_exports, %sepa_export_items_by_id, $all_open_sepa_export_items);
 123   if ($has_batch_transaction) {
 124     $all_open_sepa_export_items = SL::DB::Manager::SepaExportItem->get_all(where        => [chart_id               => $params{bank_account}->chart_id ,
 125                                                                                              'sepa_export.executed' => 0,
 126                                                                                              'sepa_export.closed'   => 0
 127                                                                             ],
 128                                                                             with_objects => ['sepa_export']);
 129     %sepa_export_items_by_id = partition_by { $_->ar_id || $_->ap_id } @$all_open_sepa_export_items;
 130
 131     # first collect sepa export items to open invoices
 132     foreach my $open_invoice (@all_open_invoices){
 133       $open_invoice->{realamount}  = $::form->format_amount(\%::myconfig,$open_invoice->amount,2);
 134       $open_invoice->{skonto_type} = 'without_skonto';
 135       foreach (@{ $sepa_export_items_by_id{ $open_invoice->id } || [] }) {
 136         my $factor                   = ($_->ar_id == $open_invoice->id ? 1 : -1);
 137         $open_invoice->{realamount}  = $::form->format_amount(\%::myconfig,$open_invoice->amount*$factor,2);
 138
 139         $open_invoice->{skonto_type} = $_->payment_type;
 140         $sepa_exports{$_->sepa_export_id} ||= { count => 0, is_ar => 0, amount => 0, proposed => 0, invoices => [], item => $_ };
 141         $sepa_exports{$_->sepa_export_id}->{count}++;
 142         $sepa_exports{$_->sepa_export_id}->{is_ar}++ if  $_->ar_id == $open_invoice->id;
 143         $sepa_exports{$_->sepa_export_id}->{amount} += $_->amount * $factor;
 144         push @{ $sepa_exports{$_->sepa_export_id}->{invoices} }, $open_invoice;
 145       }
 146     }
 147   }
 148   # try to match each bank_transaction with each of the possible open invoices
 149   # by awarding points
 150   my @proposals;
 151
 152   foreach my $bt (@{ $bank_transactions }) {
 153     ## 5 Stellen hinter dem Komma auf 2 Stellen reduzieren
 154     $bt->amount($bt->amount*1);
 155     $bt->invoice_amount($bt->invoice_amount*1);
 156
 157     $bt->{proposals}    = [];
 158     $bt->{rule_matches} = [];
 159
 160     $bt->{remote_name} .= $bt->{remote_name_1} if $bt->{remote_name_1};
 161
 162     if ($has_batch_transaction && $bt->is_batch_transaction ) {
 163       my $found=0;
 164       foreach ( keys  %sepa_exports) {
 165         if ( abs(($sepa_exports{$_}->{amount} * 1) - ($bt->amount * 1)) < 0.01 ) {
 166           ## jupp
 167           @{$bt->{proposals}} = @{$sepa_exports{$_}->{invoices}};
 168           $bt->{sepa_export_ok} = 1;
 169           $sepa_exports{$_}->{proposed}=1;
 170           push(@proposals, $bt);
 171           $found=1;
 172           last;
 173         }
 174       }
 175       next if $found;
 176       # batch transaction has no remotename !!
 177     }
 178
 179     # try to match the current $bt to each of the open_invoices, saving the
 180     # results of get_agreement_with_invoice in $open_invoice->{agreement} and
 181     # $open_invoice->{rule_matches}.
 182
 183     # The values are overwritten each time a new bt is checked, so at the end
 184     # of each bt the likely results are filtered and those values are stored in
 185     # the arrays $bt->{proposals} and $bt->{rule_matches}, and the agreement
 186     # score is stored in $bt->{agreement}
 187
 188     foreach my $open_invoice (@all_open_invoices) {
 189
 190       ($open_invoice->{agreement}, $open_invoice->{rule_matches}) = $bt->get_agreement_with_invoice($open_invoice);
 191
 192       $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,
 193                                       $open_invoice->amount * ($open_invoice->{is_ar} ? 1 : -1), 2);
 194     }
 195
 196     my $agreement = 15;
 197     my $min_agreement = 3; # suggestions must have at least this score
 198
 199     my $max_agreement = max map { $_->{agreement} } @all_open_invoices;
 200
 201     # add open_invoices with highest agreement into array $bt->{proposals}
 202     if ( $max_agreement >= $min_agreement ) {
 203       $bt->{proposals} = [ grep { $_->{agreement} == $max_agreement } @all_open_invoices ];
 204       $bt->{agreement} = $max_agreement; #scalar @{ $bt->{proposals} } ? $agreement + 1 : '';
 205
 206       # store the rule_matches in a separate array, so they can be displayed in template
 207       foreach ( @{ $bt->{proposals} } ) {
 208         push(@{$bt->{rule_matches}}, $_->{rule_matches});
 209       };
 210     };
 211   }  # finished one bt
 212   # finished all bt
 213
 214   # separate filter for proposals (second tab, agreement >= 5 and exactly one match)
 215   # to qualify as a proposal there has to be
 216   # * agreement >= 5  TODO: make threshold configurable in configuration
 217   # * there must be only one exact match
 218   my $proposal_threshold = 5;
 219   my @otherproposals = grep {
 220        ($_->{agreement} >= $proposal_threshold)
 221     && (1 == scalar @{ $_->{proposals} })
 222     && ($_->{proposals}->[0]->forex == 0)      # nyi forex invoices for automatic booking
 223   } @{ $bank_transactions };
 224
 225   push @proposals, @otherproposals;
 226
 227   # sort bank transaction proposals by quality (score) of proposal
 228   if ($params{sort_by} && $params{sort_by} eq 'proposal') {
 229     my $dir = $params{sort_dir} ? 1 : -1;
 230     $bank_transactions = [ sort { ($a->{agreement} <=> $b->{agreement}) * $dir } @{ $bank_transactions } ];
 231   }
 232
 233   return ( $bank_transactions , \@proposals );
 234 }
 235
 236 sub action_list {
 237   my ($self) = @_;
 238
 239   if (!$::form->{filter}{bank_account}) {
 240     flash('error', t8('No bank account chosen!'));
 241     $self->action_search;
 242     return;
 243   }
 244
 245   my $bank_account = SL::DB::BankAccount->load_cached($::form->{filter}->{bank_account});
 246   my $fromdate     = $::locale->parse_date_to_object($::form->{filter}->{fromdate});
 247   my $todate       = $::locale->parse_date_to_object($::form->{filter}->{todate});
 248   $todate->add( days => 1 ) if $todate;
 249
 250   my ($bank_transactions, $proposals) = $self->gather_bank_transactions_and_proposals(
 251     bank_account => $bank_account,
 252     fromdate     => $fromdate,
 253     todate       => $todate,
 254     sort_by      => $::form->{sort_by},
 255     sort_dir     => $::form->{sort_dir},
 256   );
 257
 258   my $ui_tab =   $::instance_conf->get_no_bank_proposals ? 0
 259                : scalar(@{ $proposals }) > 0             ? 1
 260                : 0;
 261
 262   $::request->layout->add_javascripts("kivi.BankTransaction.js");
 263   $self->render('bank_transactions/list',
 264                 title             => t8('Bank transactions MT940'),
 265                 BANK_TRANSACTIONS => $bank_transactions,
 266                 PROPOSALS         => $proposals,
 267                 bank_account      => $bank_account,
 268                 ui_tab            => $ui_tab,
 269               );
 270 }
 271
 272 sub action_assign_invoice {
 273   my ($self) = @_;
 274
 275   $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
 276
 277   $self->render('bank_transactions/assign_invoice',
 278                 { layout => 0 },
 279                 title => t8('Assign invoice'),);
 280 }
 281
 282 sub action_create_invoice {
 283   my ($self) = @_;
 284   my %myconfig = %main::myconfig;
 285
 286   $self->transaction(SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id}));
 287
 288   my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(iban => $self->transaction->{remote_account_number});
 289   my $use_vendor_filter     = $self->transaction->{remote_account_number} && $vendor_of_transaction;
 290
 291   my $templates_ap = SL::DB::Manager::RecordTemplate->get_all(
 292     where        => [ template_type => 'ap_transaction' ],
 293     sort_by      => [ qw(template_name) ],
 294     with_objects => [ qw(employee vendor) ],
 295   );
 296   my $templates_gl = SL::DB::Manager::RecordTemplate->get_all(
 297     query        => [ template_type => 'gl_transaction',
 298                       chart_id      => SL::DB::Manager::BankAccount->find_by(id => $self->transaction->local_bank_account_id)->chart_id,
 299                     ],
 300     sort_by      => [ qw(template_name) ],
 301     with_objects => [ qw(employee record_template_items) ],
 302   );
 303
 304   # pre filter templates_ap, if we have a vendor match (IBAN eq IBAN) - show and allow user to edit this via gui!
 305   $templates_ap = [ grep { $_->vendor_id == $vendor_of_transaction->id } @{ $templates_ap } ] if $use_vendor_filter;
 306
 307   $self->callback($self->url_for(
 308     action                => 'list',
 309     'filter.bank_account' => $::form->{filter}->{bank_account},
 310     'filter.todate'       => $::form->{filter}->{todate},
 311     'filter.fromdate'     => $::form->{filter}->{fromdate},
 312   ));
 313
 314   # if we have exactly one ap match, use this directly
 315   if ($use_vendor_filter && 1 == scalar @{ $templates_ap }) {
 316     $self->redirect_to($self->load_ap_record_template_url($templates_ap->[0]));
 317
 318   } else {
 319     my $dialog_html = $self->render(
 320       'bank_transactions/create_invoice',
 321       { layout => 0, output => 0 },
 322       title        => t8('Create invoice'),
 323       TEMPLATES_GL => $use_vendor_filter && @{ $templates_ap } ? undef : $templates_gl,
 324       TEMPLATES_AP => $templates_ap,
 325       vendor_name  => $use_vendor_filter && @{ $templates_ap } ? $vendor_of_transaction->name : undef,
 326     );
 327     $self->js->run('kivi.BankTransaction.show_create_invoice_dialog', $dialog_html)->render;
 328   }
 329 }
 330
 331 sub action_ajax_payment_suggestion {
 332   my ($self) = @_;
 333
 334   # based on a BankTransaction ID and a Invoice or PurchaseInvoice ID passed via $::form,
 335   # create an HTML blob to be used by the js function add_invoices in templates/webpages/bank_transactions/list.html
 336   # and return encoded as JSON
 337
 338   croak("Need bt_id") unless $::form->{bt_id};
 339
 340   my $invoice = SL::DB::Manager::Invoice->find_by( id => $::form->{prop_id} ) || SL::DB::Manager::PurchaseInvoice->find_by( id => $::form->{prop_id} );
 341
 342   croak("No valid invoice found") unless $invoice;
 343
 344   my $html = $self->render(
 345     'bank_transactions/_payment_suggestion', { output => 0 },
 346     bt_id          => $::form->{bt_id},
 347     invoice        => $invoice,
 348   );
 349
 350   $self->render(\ SL::JSON::to_json( { 'html' => "$html" } ), { layout => 0, type => 'json', process => 0 });
 351 };
 352
 353 sub action_filter_templates {
 354   my ($self) = @_;
 355
 356   $self->{transaction}      = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
 357
 358   my (@filter, @filter_ap);
 359
 360   # filter => gl and ap | filter_ap = ap (i.e. vendorname)
 361   push @filter,    ('template_name' => { ilike => '%' . $::form->{template} . '%' })  if $::form->{template};
 362   push @filter,    ('reference'     => { ilike => '%' . $::form->{reference} . '%' }) if $::form->{reference};
 363   push @filter_ap, ('vendor.name'   => { ilike => '%' . $::form->{vendor} . '%' })    if $::form->{vendor};
 364   push @filter_ap, @filter;
 365   my $templates_gl = SL::DB::Manager::RecordTemplate->get_all(
 366     query        => [ template_type => 'gl_transaction',
 367                       chart_id      => SL::DB::Manager::BankAccount->find_by(id => $self->transaction->local_bank_account_id)->chart_id,
 368                       (and => \@filter) x !!@filter
 369                     ],
 370     with_objects => [ qw(employee record_template_items) ],
 371   );
 372
 373   my $templates_ap = SL::DB::Manager::RecordTemplate->get_all(
 374     where        => [ template_type => 'ap_transaction', (and => \@filter_ap) x !!@filter_ap ],
 375     with_objects => [ qw(employee vendor) ],
 376   );
 377   $::form->{filter} //= {};
 378
 379   $self->callback($self->url_for(
 380     action                => 'list',
 381     'filter.bank_account' => $::form->{filter}->{bank_account},
 382     'filter.todate'       => $::form->{filter}->{todate},
 383     'filter.fromdate'     => $::form->{filter}->{fromdate},
 384   ));
 385
 386   my $output  = $self->render(
 387     'bank_transactions/_template_list',
 388     { output => 0 },
 389     TEMPLATES_AP => $templates_ap,
 390     TEMPLATES_GL => $templates_gl,
 391   );
 392
 393   $self->render(\to_json({ html => $output }), { type => 'json', process => 0 });
 394 }
 395
 396 sub action_ajax_add_list {
 397   my ($self) = @_;
 398
 399   my @where_sale     = (amount => { ne => \'paid' });
 400   my @where_purchase = (amount => { ne => \'paid' });
 401
 402   if ($::form->{invnumber}) {
 403     push @where_sale,     (invnumber => { ilike => like($::form->{invnumber})});
 404     push @where_purchase, (invnumber => { ilike => like($::form->{invnumber})});
 405   }
 406
 407   if ($::form->{amount}) {
 408     push @where_sale,     (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
 409     push @where_purchase, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
 410   }
 411
 412   if ($::form->{vcnumber}) {
 413     push @where_sale,     ('customer.customernumber' => { ilike => like($::form->{vcnumber})});
 414     push @where_purchase, ('vendor.vendornumber'     => { ilike => like($::form->{vcnumber})});
 415   }
 416
 417   if ($::form->{vcname}) {
 418     push @where_sale,     ('customer.name' => { ilike => like($::form->{vcname})});
 419     push @where_purchase, ('vendor.name'   => { ilike => like($::form->{vcname})});
 420   }
 421
 422   if ($::form->{transdatefrom}) {
 423     my $fromdate = $::locale->parse_date_to_object($::form->{transdatefrom});
 424     if ( ref($fromdate) eq 'DateTime' ) {
 425       push @where_sale,     ('transdate' => { ge => $fromdate});
 426       push @where_purchase, ('transdate' => { ge => $fromdate});
 427     };
 428   }
 429
 430   if ($::form->{transdateto}) {
 431     my $todate = $::locale->parse_date_to_object($::form->{transdateto});
 432     if ( ref($todate) eq 'DateTime' ) {
 433       $todate->add(days => 1);
 434       push @where_sale,     ('transdate' => { lt => $todate});
 435       push @where_purchase, ('transdate' => { lt => $todate});
 436     };
 437   }
 438
 439   my $all_open_ar_invoices = SL::DB::Manager::Invoice        ->get_all(where => \@where_sale,     with_objects => 'customer');
 440   my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => \@where_purchase, with_objects => 'vendor');
 441
 442   my @all_open_invoices = @{ $all_open_ar_invoices };
 443   # add ap invoices, filtering out subcent open amounts
 444   push @all_open_invoices, grep { abs($_->amount - $_->paid) >= 0.005 } @{ $all_open_ap_invoices };
 445
 446   @all_open_invoices = sort { $a->id <=> $b->id } @all_open_invoices;
 447
 448   my $output  = $self->render(
 449     'bank_transactions/add_list',
 450     { output => 0 },
 451     INVOICES => \@all_open_invoices,
 452   );
 453
 454   my %result = ( count => 0, html => $output );
 455
 456   $self->render(\to_json(\%result), { type => 'json', process => 0 });
 457 }
 458
 459 sub action_ajax_accept_invoices {
 460   my ($self) = @_;
 461
 462   my @selected_invoices;
 463   foreach my $invoice_id (@{ $::form->{invoice_id} || [] }) {
 464     my $invoice_object = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
 465     push @selected_invoices, $invoice_object;
 466   }
 467
 468   $self->render(
 469     'bank_transactions/invoices',
 470     { layout => 0 },
 471     INVOICES => \@selected_invoices,
 472     bt_id    => $::form->{bt_id},
 473   );
 474 }
 475
 476 sub save_invoices {
 477   my ($self) = @_;
 478
 479   return 0 if !$::form->{invoice_ids};
 480
 481   my %invoice_hash = %{ delete $::form->{invoice_ids} };  # each key (the bt line with a bt_id) contains an array of invoice_ids
 482
 483   # e.g. three partial payments with bt_ids 54, 55 and 56 for invoice with id 74:
 484   # $invoice_hash = {
 485   #         '55' => [
 486   #                 '74'
 487   #               ],
 488   #         '54' => [
 489   #                 '74'
 490   #               ],
 491   #         '56' => [
 492   #                 '74'
 493   #               ]
 494   #       };
 495   #
 496   # or if the payment with bt_id 44 is used to pay invoices with ids 50, 51 and 52
 497   # $invoice_hash = {
 498   #           '44' => [ '50', '51', 52' ]
 499   #         };
 500
 501   $::form->{invoice_skontos} ||= {}; # hash of arrays containing the payment types, could be empty
 502
 503   # a bank_transaction may be assigned to several invoices, i.e. a customer
 504   # might pay several open invoices with one transaction
 505
 506   $self->problems([]);
 507
 508   my $count = 0;
 509
 510   if ( $::form->{proposal_ids} ) {
 511     foreach (@{ $::form->{proposal_ids} }) {
 512       my  $bank_transaction_id = $_;
 513       my  $invoice_ids = $invoice_hash{$_};
 514       push @{ $self->problems }, $self->save_single_bank_transaction(
 515         bank_transaction_id => $bank_transaction_id,
 516         invoice_ids         => $invoice_ids,
 517         sources             => ($::form->{sources} // {})->{$_},
 518         memos               => ($::form->{memos}   // {})->{$_},
 519       );
 520       $count += scalar( @{$invoice_ids} );
 521     }
 522   } else {
 523     while ( my ($bank_transaction_id, $invoice_ids) = each(%invoice_hash) ) {
 524       push @{ $self->problems }, $self->save_single_bank_transaction(
 525         bank_transaction_id => $bank_transaction_id,
 526         invoice_ids         => $invoice_ids,
 527         sources             => [  map { $::form->{"sources_${bank_transaction_id}_${_}"}           } @{ $invoice_ids } ],
 528         memos               => [  map { $::form->{"memos_${bank_transaction_id}_${_}"}             } @{ $invoice_ids } ],
 529         book_fx_bank_fees   => [  map { $::form->{"book_fx_bank_fees_${bank_transaction_id}_${_}"} } @{ $invoice_ids } ],
 530         currency_ids        => [  map { $::form->{"currency_id_${bank_transaction_id}_${_}"}       } @{ $invoice_ids } ],
 531         exchangerates       => [  map { $::form->parse_amount(\%::myconfig, $::form->{"exchangerate_${bank_transaction_id}_${_}"}) } @{ $invoice_ids } ],
 532       );
 533       $count += scalar( @{$invoice_ids} );
 534     }
 535   }
 536   my $max_count = $count;
 537   foreach (@{ $self->problems }) {
 538     $count-- if $_->{result} eq 'error';
 539   }
 540   return ($count, $max_count);
 541 }
 542
 543 sub action_save_invoices {
 544   my ($self) = @_;
 545   my ($success_count, $max_count) = $self->save_invoices();
 546
 547   if ($success_count == $max_count) {
 548     flash('ok', t8('#1 invoice(s) saved.', $success_count));
 549   } else {
 550     flash('error', t8('At least #1 invoice(s) not saved', $max_count - $success_count));
 551   }
 552
 553   $self->action_list();
 554 }
 555
 556 sub action_save_proposals {
 557   my ($self) = @_;
 558
 559   if ( $::form->{proposal_ids} ) {
 560     my $propcount = scalar(@{ $::form->{proposal_ids} });
 561     if ( $propcount > 0 ) {
 562       my $count = $self->save_invoices();
 563
 564       flash('ok', t8('#1 proposal(s) with #2 invoice(s) saved.',  $propcount, $count));
 565     }
 566   }
 567   $self->action_list();
 568
 569 }
 570
 571 sub save_single_bank_transaction {
 572   my ($self, %params) = @_;
 573
 574   my %data = (
 575     %params,
 576     bank_transaction => SL::DB::Manager::BankTransaction->find_by(id => $params{bank_transaction_id}),
 577     invoices         => [],
 578   );
 579
 580   if (!$data{bank_transaction}) {
 581     return {
 582       %data,
 583       result => 'error',
 584       message => $::locale->text('The ID #1 is not a valid database ID.', $data{bank_transaction_id}),
 585     };
 586   }
 587
 588   my $bank_transaction = $data{bank_transaction};
 589
 590   if ($bank_transaction->closed_period) {
 591     return {
 592       %data,
 593       result => 'error',
 594       message => $::locale->text('Cannot post payment for a closed period!'),
 595     };
 596   }
 597   my (@warnings);
 598
 599   my $transit_items_account = SL::DB::Manager::Chart->find_by(id => SL::DB::Default->get->transit_items_chart_id);
 600
 601   my $worker = sub {
 602     my $bt_id                 = $data{bank_transaction_id};
 603     my $sign                  = $bank_transaction->amount < 0 ? -1 : 1;
 604     my $payment_received      = $bank_transaction->amount > 0;
 605     my $payment_sent          = $bank_transaction->amount < 0;
 606     my ($has_negative_record, $has_positive_record);
 607
 608
 609     foreach my $invoice_id (@{ $params{invoice_ids} }) {
 610       my $invoice = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
 611       if (!$invoice) {
 612         return {
 613           %data,
 614           result  => 'error',
 615           message => $::locale->text("The ID #1 is not a valid database ID.", $invoice_id),
 616         };
 617       }
 618       $has_positive_record = 1 if $invoice->amount > 0; # invoice
 619       $has_negative_record = 1 if $invoice->amount < 0; # credit_note
 620       push @{ $data{invoices} }, $invoice;
 621     }
 622
 623     if (ref $transit_items_account eq 'SL::DB::Chart' && $has_positive_record
 624         &&           scalar @{ $data{invoices} } == 2 && $has_negative_record) {
 625
 626       $self->_check_and_book_credit_note(
 627         invoices      => $data{invoices},
 628         chart_id      => $transit_items_account->id,
 629         bt_id         => $bt_id,
 630         transdate     => $bank_transaction->valutadate,
 631         transit_chart => $transit_items_account         );
 632
 633     }
 634     if (   $payment_received
 635         && any {    ( $_->is_sales && ($_->amount < 0))
 636                  || (!$_->is_sales && ($_->amount > 0))
 637                } @{ $data{invoices} }) {
 638       return {
 639         %data,
 640         result  => 'error',
 641         message => $::locale->text("Received payments can only be posted for sales invoices and purchase credit notes."),
 642       };
 643     }
 644
 645     if (   $payment_sent
 646         && any {    ( $_->is_sales && ($_->amount > 0))
 647                  || (!$_->is_sales && ($_->amount < 0) && ($_->invoice_type eq 'purchase_invoice'))
 648                } @{ $data{invoices} }) {
 649       return {
 650         %data,
 651         result  => 'error',
 652         message => $::locale->text("Sent payments can only be posted for purchase invoices and sales credit notes."),
 653       };
 654     }
 655
 656     my $max_invoices = scalar(@{ $data{invoices} });
 657     my $n_invoices   = 0;
 658
 659     foreach my $invoice (@{ $data{invoices} }) {
 660       my $source  = ($data{sources} // [])->[$n_invoices];
 661       my $memo    = ($data{memos}   // [])->[$n_invoices];
 662       my $fx_rate = ($data{exchangerates} // [])->[$n_invoices];
 663       my $fx_book = ($data{book_fx_bank_fees}   // [])->[$n_invoices];
 664       my $currency_id = ($data{currency_ids}   // [])->[$n_invoices];
 665
 666       $n_invoices++ ;
 667       # safety check invoice open
 668       croak("Invoice closed. Cannot proceed.") unless ($invoice->open_amount);
 669
 670       if (   ($payment_sent     && $bank_transaction->not_assigned_amount >= 0)
 671           || ($payment_received && $bank_transaction->not_assigned_amount <= 0)) {
 672         return {
 673           %data,
 674           result  => 'error',
 675           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."),
 676         };
 677       }
 678
 679       my ($payment_type, $free_skonto_amount);
 680       if ( defined $::form->{invoice_skontos}->{"$bt_id"} ) {
 681         $payment_type = shift(@{ $::form->{invoice_skontos}->{"$bt_id"} }) || '';
 682       } else {
 683         $payment_type = 'without_skonto';
 684       }
 685       # hack payment type use free_skonto for with_fuzzy_skonto
 686       if ($payment_type eq 'with_fuzzy_skonto_pt') {
 687         $free_skonto_amount = abs($invoice->open_amount - abs($bank_transaction->not_assigned_amount));
 688         die "Invalid state for fuzzy skonto amount" unless $free_skonto_amount > 0;
 689         $payment_type = 'free_skonto';  # we have a valid free_skonto amount, therefore go ...
 690       } elsif ($payment_type eq 'free_skonto') {
 691         # parse user input > 0
 692         if ($::form->parse_amount(\%::myconfig, $::form->{"free_skonto_amount"}->{"$bt_id"}{$invoice->id}) > 0) {
 693           $free_skonto_amount = $::form->parse_amount(\%::myconfig, $::form->{"free_skonto_amount"}->{"$bt_id"}{$invoice->id});
 694         } else {
 695           return {
 696             %data,
 697             result  => 'error',
 698             message => $::locale->text("Free skonto amount has to be a positive number."),
 699           };
 700         }
 701       }
 702     # pay invoice
 703     # TODO rewrite this: really booked amount should be a return value of Payment.pm
 704     # -> quick and dirty done -> really booked amount is the first element of return array
 705     # also this controller shouldnt care about how to calc skonto. we simply delegate the
 706     # payment_type to the helper and get the corresponding bank_transaction values back
 707     # hotfix to get the signs right - compare absolute values and later set the signs
 708     # should be better done elsewhere - changing not_assigned_amount to abs feels seriously bogus
 709     # default open amount
 710     my $open_amount = $payment_type eq 'with_skonto_pt' ? $invoice->amount_less_skonto : $invoice->open_amount;
 711     # if fx calc new open amount with skonto pt and set new exchange rate (default or for bank_transaction)
 712     if ($fx_rate > 0) {
 713       # 1. set new open amount
 714       die "Exchangerate without currency"                     unless $currency_id;
 715       die "Invoice currency differs from user input currency" unless $currency_id == $invoice->currency->id;
 716       $open_amount  = $payment_type eq 'with_skonto_pt' ? $invoice->amount_less_skonto_fx($fx_rate) : $invoice->open_amount_fx($fx_rate);
 717       # 2. set daily default or custom record exchange rate
 718       my $default_rate = $invoice->get_exchangerate_for_bank_transaction($bank_transaction->id);
 719       if (!$default_rate) { # set new daily default
 720         my $buysell = $invoice->is_sales ? 'buy' : 'sell';
 721         my $ex = SL::DB::Manager::Exchangerate->find_by(currency_id => $currency_id,
 722                                                         transdate => $bank_transaction->valutadate)
 723               ||              SL::DB::Exchangerate->new(currency_id => $currency_id,
 724                                                         transdate   => $bank_transaction->valutadate);
 725         $ex->update_attributes($buysell => $fx_rate);
 726         $bank_transaction->exchangerate(undef);       # maybe user reassigned bank_transaction
 727       } elsif ($default_rate != $fx_rate) {           # set record (banktransaction) exchangerate
 728         $bank_transaction->exchangerate($fx_rate);    # custom rate, will be displayed in ap, ir, is
 729       } elsif (abs($default_rate - $fx_rate) < 0.001) {
 730         # last valid state default rate is (nearly) the same as user input -> do nothing
 731       } else { die "Invalid exchange rate state:" . $default_rate . " " . $fx_rate; }
 732     } # end fx hook
 733
 734     # open amount is in default currency -> free_skonto is in default currency, no need to change
 735     $open_amount            = abs($open_amount);
 736     $open_amount           -= $free_skonto_amount if ($payment_type eq 'free_skonto');
 737     my $not_assigned_amount = abs($bank_transaction->not_assigned_amount);
 738     my $amount_for_booking  = ($open_amount < $not_assigned_amount) ? $open_amount : $not_assigned_amount;
 739     my $fx_fee_amount       = $fx_book && ($open_amount < $not_assigned_amount) ? $not_assigned_amount - $open_amount : 0;
 740     my $amount_for_payment  = $amount_for_booking;
 741     # add booking amount
 742     # $amount_for_booking
 743
 744     # get the right direction for the payment bookings (all amounts < 0 are stornos, credit notes or negative ap)
 745     $amount_for_payment *= -1 if $invoice->amount < 0;
 746     $free_skonto_amount *= -1 if ($free_skonto_amount && $invoice->amount < 0);
 747     # get the right direction for the bank transaction
 748     # sign is simply the sign of amount in bank_transactions: positive for increase and negative for decrease
 749     $amount_for_booking *= $sign;
 750
 751     # ... and then pay the invoice
 752     my @acc_ids = $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
 753                           trans_id      => $invoice->id,
 754                           amount        => $amount_for_payment,
 755                           payment_type  => $payment_type,
 756                           source        => $source,
 757                           memo          => $memo,
 758                           skonto_amount => $free_skonto_amount,
 759                           exchangerate  => $fx_rate,
 760                           fx_book       => $fx_book,
 761                           fx_fee_amount => $fx_fee_amount,
 762                           currency_id   => $currency_id,
 763                           bt_id         => $bt_id,
 764                           transdate     => $bank_transaction->valutadate->to_kivitendo);
 765     # First element is the booked amount for accno bank
 766     my $bank_amount = shift @acc_ids;
 767
 768     if (!$invoice->forex) {
 769       # die "Invalid state, calculated invoice_amount differs from expected invoice amount" unless (abs($bank_amount->{return_bank_amount}) - abs($amount_for_booking) < 0.001);
 770       $bank_transaction->invoice_amount($bank_transaction->invoice_amount + $amount_for_booking);
 771     } else {
 772       die "Invalid state, calculated invoice_amount differs from expected invoice amount: $amount_for_booking <> " . $bank_amount->{return_bank_amount}
 773         unless $fx_book || (abs($bank_amount->{return_bank_amount}) - abs($amount_for_booking) < 0.005);
 774       $bank_transaction->invoice_amount($bank_transaction->invoice_amount + $bank_amount->{return_bank_amount});
 775       #$bank_transaction->invoice_amount($bank_transaction->invoice_amount + $amount_for_booking);
 776     }
 777     # ... and record the origin via BankTransactionAccTrans
 778     if (scalar(@acc_ids) < 2) {
 779       return {
 780         %data,
 781         result  => 'error',
 782         message => $::locale->text("Unable to book transactions for bank purpose #1", $bank_transaction->purpose),
 783       };
 784     }
 785     foreach my $acc_trans_id (@acc_ids) {
 786         my $id_type = $invoice->is_sales ? 'ar' : 'ap';
 787         my  %props_acc = (
 788           acc_trans_id        => $acc_trans_id,
 789           bank_transaction_id => $bank_transaction->id,
 790           $id_type            => $invoice->id,
 791         );
 792         SL::DB::BankTransactionAccTrans->new(%props_acc)->save;
 793     }
 794       # Record a record link from the bank transaction to the invoice
 795       my %props = (
 796         from_table => 'bank_transactions',
 797         from_id    => $bt_id,
 798         to_table   => $invoice->is_sales ? 'ar' : 'ap',
 799         to_id      => $invoice->id,
 800       );
 801       SL::DB::RecordLink->new(%props)->save;
 802
 803       # "close" a sepa_export_item if it exists
 804       # code duplicated in action_save_proposals!
 805       # currently only works, if there is only exactly one open sepa_export_item
 806       if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
 807         if ( scalar @$seis == 1 ) {
 808           # moved the execution and the check for sepa_export into a method,
 809           # this isn't part of a transaction, though
 810           $seis->[0]->set_executed if $invoice->id == $seis->[0]->arap_id;
 811         }
 812       }
 813
 814     }
 815     $bank_transaction->save;
 816
 817     # 'undef' means 'no error' here.
 818     return undef;
 819   };
 820
 821   my $error;
 822   my $rez = $data{bank_transaction}->db->with_transaction(sub {
 823     eval {
 824       $error = $worker->();
 825       1;
 826
 827     } or do {
 828       $error = {
 829         %data,
 830         result  => 'error',
 831         message => $@,
 832       };
 833     };
 834
 835     # Rollback Fehler nicht weiterreichen
 836     # die if $error;
 837     # aber einen rollback von hand
 838     $::lxdebug->message(LXDebug->DEBUG2(),"finish worker with ". ($error ? $error->{result} : '-'));
 839     $data{bank_transaction}->db->dbh->rollback if $error && $error->{result} eq 'error';
 840   });
 841
 842   return grep { $_ } ($error, @warnings);
 843 }
 844 sub action_unlink_bank_transaction {
 845   my ($self, %params) = @_;
 846
 847   croak("No bank transaction ids") unless scalar @{ $::form->{ids}} > 0;
 848
 849   my $success_count;
 850
 851   foreach my $bt_id (@{ $::form->{ids}} )  {
 852
 853     my $bank_transaction = SL::DB::Manager::BankTransaction->find_by(id => $bt_id);
 854     croak("No valid bank transaction found") unless (ref($bank_transaction)  eq 'SL::DB::BankTransaction');
 855     croak t8('Cannot unlink payment for a closed period!') if $bank_transaction->closed_period;
 856
 857     # everything in one transaction
 858     my $rez = $bank_transaction->db->with_transaction(sub {
 859       # 1. remove all reconciliations (due to underlying trigger, this has to be the first step)
 860       my $rec_links = SL::DB::Manager::ReconciliationLink->get_all(where => [ bank_transaction_id => $bt_id ]);
 861       $_->delete for @{ $rec_links };
 862
 863       my %trans_ids;
 864       foreach my $acc_trans_id_entry (@{ SL::DB::Manager::BankTransactionAccTrans->get_all(where => [bank_transaction_id => $bt_id ] )}) {
 865
 866         my $acc_trans = SL::DB::Manager::AccTransaction->get_all(where => [acc_trans_id => $acc_trans_id_entry->acc_trans_id]);
 867
 868         # save trans_id and type
 869         die "no type" unless ($acc_trans_id_entry->ar_id || $acc_trans_id_entry->ap_id || $acc_trans_id_entry->gl_id);
 870         $trans_ids{$acc_trans_id_entry->ar_id} = 'ar' if $acc_trans_id_entry->ar_id;
 871         $trans_ids{$acc_trans_id_entry->ap_id} = 'ap' if $acc_trans_id_entry->ap_id;
 872         $trans_ids{$acc_trans_id_entry->gl_id} = 'gl' if $acc_trans_id_entry->gl_id;
 873         # 2. all good -> ready to delete acc_trans and bt_acc link
 874         $acc_trans_id_entry->delete;
 875         $_->delete for @{ $acc_trans };
 876       }
 877       # 3. update arap.paid (may not be 0, yet)
 878       #    or in case of gl, delete whole entry
 879       while (my ($trans_id, $type) = each %trans_ids) {
 880         if ($type eq 'gl') {
 881           SL::DB::Manager::GLTransaction->delete_all(where => [ id => $trans_id ]);
 882           next;
 883         }
 884         die ("invalid type") unless $type =~ m/^(ar|ap)$/;
 885
 886         # recalc and set paid via database query
 887         my $query = qq|UPDATE $type SET paid =
 888                         (SELECT COALESCE(abs(sum(amount)),0) FROM acc_trans
 889                          WHERE trans_id = ?
 890                          AND (chart_link ilike '%paid%'
 891                               OR chart_id IN (SELECT fxgain_accno_id from defaults)
 892                               OR chart_id IN (SELECT fxloss_accno_id from defaults)
 893                              )
 894                         )
 895                         WHERE id = ?|;
 896
 897         die if (do_query($::form, $bank_transaction->db->dbh, $query, $trans_id, $trans_id) == -1);
 898
 899         # undo datepaid if no payment exists
 900         $query = qq|UPDATE $type SET datepaid = null WHERE ID = ? AND paid = 0|;
 901         die if (do_query($::form, $bank_transaction->db->dbh, $query, $trans_id) == -1);
 902       }
 903       # 4. and delete all (if any) record links
 904       my $rl = SL::DB::Manager::RecordLink->delete_all(where => [ from_id => $bt_id, from_table => 'bank_transactions' ]);
 905
 906       # 5. finally reset  this bank transaction
 907       $bank_transaction->invoice_amount(0);
 908       $bank_transaction->exchangerate(undef);
 909       $bank_transaction->cleared(0);
 910       $bank_transaction->save;
 911       # 6. and add a log entry in history_erp
 912       SL::DB::History->new(
 913         trans_id    => $bank_transaction->id,
 914         snumbers    => 'bank_transaction_unlink_' . $bank_transaction->id,
 915         employee_id => SL::DB::Manager::Employee->current->id,
 916         what_done   => 'bank_transaction',
 917         addition    => 'UNLINKED',
 918       )->save();
 919
 920       1;
 921
 922     }) || die t8('error while unlinking payment #1 : ', $bank_transaction->purpose) . $bank_transaction->db->error . "\n";
 923
 924     $success_count++;
 925   }
 926
 927   flash('ok', t8('#1 bank transaction bookings undone.', $success_count));
 928   $self->action_list_all() unless $params{testcase};
 929 }
 930 #
 931 # filters
 932 #
 933
 934 sub check_auth {
 935   $::auth->assert('bank_transaction');
 936 }
 937
 938 #
 939 # helpers
 940 #
 941
 942 sub make_filter_summary {
 943   my ($self) = @_;
 944
 945   my $filter = $::form->{filter} || {};
 946   my @filter_strings;
 947
 948   my @filters = (
 949     [ $filter->{"transdate:date::ge"},      $::locale->text('Transdate')  . " " . $::locale->text('From Date') ],
 950     [ $filter->{"transdate:date::le"},      $::locale->text('Transdate')  . " " . $::locale->text('To Date')   ],
 951     [ $filter->{"valutadate:date::ge"},     $::locale->text('Valutadate') . " " . $::locale->text('From Date') ],
 952     [ $filter->{"valutadate:date::le"},     $::locale->text('Valutadate') . " " . $::locale->text('To Date')   ],
 953     [ $filter->{"amount:number"},           $::locale->text('Amount')                                          ],
 954     [ $filter->{"bank_account_id:integer"}, $::locale->text('Local bank account')                              ],
 955     [ $filter->{"remote_name:substr::ilike"}, $::locale->text('Remote name')                                   ],
 956     [ $filter->{"remote_account_number:substr::ilike"}, $::locale->text('Remote account number')               ],
 957     [ $filter->{"remote_bank_code:substr::ilike"}     , $::locale->text('Remote bank code')                    ],
 958     [ $filter->{"purpose:substr::ilike"}              , $::locale->text('Purpose')                             ],
 959   );
 960
 961   for (@filters) {
 962     push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
 963   }
 964
 965   $self->{filter_summary} = join ', ', @filter_strings;
 966 }
 967
 968 sub prepare_report {
 969   my ($self)       = @_;
 970
 971   my $callback     = $self->models->get_callback;
 972
 973   my $report       = SL::ReportGenerator->new(\%::myconfig, $::form);
 974   $report->{title} = t8('Bank transactions');
 975   $self->{report}  = $report;
 976
 977   my @columns      = qw(ids local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount invoice_amount invoices currency purpose end_to_end_id local_account_number local_bank_code id);
 978   my @sortable     = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount                                  purpose local_account_number local_bank_code);
 979
 980   my %column_defs  = (
 981     ids                 => { raw_header_data => checkbox_tag("", id => "check_all", checkall  => "[data-checkall=1]"),
 982                              'align'         => 'center',
 983                              raw_data        => sub { if (@{ $_[0]->linked_invoices }) {
 984                                                         if ($_[0]->closed_period) {
 985                                                           html_tag('text', "X"); #, tooltip => t8('Bank Transaction is in a closed period.')),
 986                                                         } else {
 987                                                           checkbox_tag("ids[]", value => $_[0]->id, "data-checkall" => 1);
 988                                                         }
 989                                                 } } },
 990     transdate             => { sub   => sub { $_[0]->transdate_as_date } },
 991     valutadate            => { sub   => sub { $_[0]->valutadate_as_date } },
 992     remote_name           => { },
 993     remote_account_number => { },
 994     remote_bank_code      => { },
 995     amount                => { sub   => sub { $_[0]->amount_as_number },
 996                                align => 'right' },
 997     invoice_amount        => { sub   => sub { $_[0]->invoice_amount_as_number },
 998                                align => 'right' },
 999     invoices              => { sub      => sub { my @invnumbers; for my $obj (@{ $_[0]->linked_invoices }) {
1000                                                                 next unless $obj; push @invnumbers, $obj->invnumber } return \@invnumbers },
1001                                obj_link => sub { my @links;      for my $obj (@{ $_[0]->linked_invoices }) {
1002                                                                 next unless $obj; my $script =  ref $obj eq 'SL::DB::GLTransaction' ? 'gl.pl'
1003                                                                                             :   $obj->is_sales &&  $obj->invoice    ? 'is.pl'
1004                                                                                             :   $obj->is_sales && !$obj->invoice    ? 'ar.pl'
1005                                                                                             :  !$obj->is_sales &&  $obj->invoice    ? 'ir.pl'
1006                                                                                             :  !$obj->is_sales && !$obj->invoice    ? 'ap.pl'
1007                                                                                             :  die "Invalid invoice state for link";
1008                                                                 push @links,$script . "?action=edit&id=" . $obj->id } return \@links }
1009                              },
1010     currency              => { sub   => sub { $_[0]->currency->name } },
1011     purpose               => { },
1012     local_account_number  => { sub   => sub { $_[0]->local_bank_account->account_number } },
1013     local_bank_code       => { sub   => sub { $_[0]->local_bank_account->bank_code } },
1014     local_bank_name       => { sub   => sub { $_[0]->local_bank_account->name } },
1015     end_to_end_id         => { sub   => sub { $_[0]->end_to_end_id }, text => $::locale->text('End to end ID') },
1016     id                    => {},
1017   );
1018
1019   map { $column_defs{$_}->{text} ||= $::locale->text( $self->models->get_sort_spec->{$_}->{title} ) } keys %column_defs;
1020
1021   $report->set_options(
1022     std_column_visibility => 1,
1023     controller_class      => 'BankTransaction',
1024     output_format         => 'HTML',
1025     top_info_text         => $::locale->text('Bank transactions'),
1026     title                 => $::locale->text('Bank transactions'),
1027     allow_pdf_export      => 1,
1028     allow_csv_export      => 1,
1029   );
1030   $report->set_columns(%column_defs);
1031   $report->set_column_order(@columns);
1032   $report->set_export_options(qw(list_all filter));
1033   $report->set_options_from_form;
1034   $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
1035   $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
1036
1037   my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted();
1038
1039   $report->set_options(
1040     raw_top_info_text     => $self->render('bank_transactions/report_top',    { output => 0 }, BANK_ACCOUNTS => $bank_accounts),
1041     raw_bottom_info_text  => $self->render('bank_transactions/report_bottom', { output => 0 }),
1042   );
1043 }
1044
1045 sub _check_and_book_credit_note {
1046   my $self   = shift;
1047   my %params = @_;
1048   Common::check_params(\%params, qw(chart_id transdate bt_id invoices transit_chart));
1049
1050   croak "No invoice "              unless (ref $params{invoices}->[0] eq 'SL::DB::PurchaseInvoice')
1051                                        || (ref $params{invoices}->[0] eq 'SL::DB::Invoice'        );
1052   croak "Not a valid date"         unless ref $params{transdate}      eq 'DateTime';
1053   croak "Not a valid chart"        unless ref $params{transit_chart}  eq 'SL::DB::Chart';
1054   croak "Need exactly two records" unless scalar @{ $params{invoices} } == 2;
1055
1056
1057   my ($has_one_credit_note, $has_one_invoice, $amount, $credit_note_index, $credit_note_no, $invoice_no);
1058   my $index = 0;
1059   foreach my $invoice (@{ $params{invoices} }) {
1060     if (   ( $invoice->is_sales && $invoice->type         eq 'credit_note')
1061         || (!$invoice->is_sales && $invoice->invoice_type eq 'purchase_credit_note')) {
1062       #     credit_notes          | purchase_credit_note
1063       #  -1397.11000 | AR         |     504.74000 |  AP
1064       #   1397.11000 | AR_paid    |    -504.74000 |  AP_paid
1065
1066       my $mult = $invoice->is_sales ? -1 : 1;  # multiplier for getting the right sign for credit_notes
1067       $amount  = ($invoice->amount - $invoice->paid) * $mult;
1068       #          (-200             - (-10))          * $mult = AR_paid (positive) |AP_paid (negative)
1069
1070       $has_one_credit_note += 1;
1071       $credit_note_index    = $index;
1072       $credit_note_no       = $invoice->invnumber;
1073     } else {
1074       $has_one_invoice     += 1;
1075       $invoice_no           = $invoice->invnumber;
1076     }
1077     $index++;
1078   }
1079   die "Invalid state" unless ($has_one_credit_note == 1 && $has_one_invoice == 1);
1080
1081   foreach my $invoice (@{ $params{invoices} }) {
1082     my $is_credit_note = $invoice->is_credit_note ?  1 : undef;
1083     my $sign           = $invoice->is_credit_note ?  1 : -1;  # correct sign for bookings
1084     my $paid_sign      = $invoice->is_credit_note ? -1 :  1;  # paid is always negative for credit_note
1085
1086     my $new_acc_trans = SL::DB::AccTransaction->new(trans_id   => $invoice->id,
1087                                                     chart_id   => $params{transit_chart}->id,
1088                                                     chart_link => $params{transit_chart}->link,
1089                                                     amount     => $amount * $sign,
1090                                                     transdate  => $params{transdate},
1091                                                     source     => $is_credit_note ?  $invoice_no : $credit_note_no,
1092                                                     memo       => t8('Automatically assigned with bank transaction'),
1093                                                     taxkey     => 0,
1094                                                     tax_id     => SL::DB::Manager::Tax->find_by(taxkey => 0)->id);
1095
1096     my $arap_booking= SL::DB::AccTransaction->new(trans_id   => $invoice->id,
1097                                                   chart_id   => $invoice->reference_account->id,
1098                                                   chart_link => $invoice->reference_account->link,
1099                                                   amount     => $amount * $sign * -1,
1100                                                   transdate  => $params{transdate},
1101                                                   source     => '',
1102                                                   taxkey     => 0,
1103                                                   tax_id     => SL::DB::Manager::Tax->find_by(taxkey => 0)->id);
1104     $new_acc_trans->save;
1105     $arap_booking->save;
1106     $invoice->update_attributes(paid => $invoice->paid + (abs($amount) * $paid_sign), datepaid => $params{transdate});
1107
1108     # link both acc_trans transactions
1109     my $id_type = $invoice->is_sales ? 'ar' : 'ap';
1110     my  %props_acc = (
1111                        acc_trans_id        => $new_acc_trans->acc_trans_id,
1112                        bank_transaction_id => $params{bt_id},
1113                        $id_type            => $invoice->id,
1114                      );
1115     SL::DB::BankTransactionAccTrans->new(%props_acc)->save;
1116         %props_acc = (
1117                        acc_trans_id        => $arap_booking->acc_trans_id,
1118                        bank_transaction_id => $params{bt_id},
1119                        $id_type            => $invoice->id,
1120                      );
1121     SL::DB::BankTransactionAccTrans->new(%props_acc)->save;
1122     # done
1123
1124     # Record a record link from the bank transaction to the credit note
1125     if ($invoice->invoice_type =~ m/credit_note/) {
1126       my %props = (
1127         from_table => 'bank_transactions',
1128         from_id    => $params{bt_id},
1129         to_table   => $id_type,
1130         to_id      => $invoice->id,
1131       );
1132       SL::DB::RecordLink->new(%props)->save;
1133     }
1134   }
1135   # throw away the credit note
1136   splice @{ $params{invoices} }, $credit_note_index, 1;
1137   # and return nothing. hook is completely done
1138 }
1139
1140 sub init_problems { [] }
1141
1142 sub init_models {
1143   my ($self) = @_;
1144
1145   SL::Controller::Helper::GetModels->new(
1146     controller => $self,
1147     sorted     => {
1148       _default => {
1149         by  => 'transdate',
1150         dir => 0,   # 1 = ASC, 0 = DESC : default sort is newest at top
1151       },
1152       id                    => t8('ID'),
1153       transdate             => t8('Transdate'),
1154       remote_name           => t8('Remote name'),
1155       amount                => t8('Amount'),
1156       invoice_amount        => t8('Assigned'),
1157       invoices              => t8('Linked invoices'),
1158       valutadate            => t8('Valutadate'),
1159       remote_account_number => t8('Remote account number'),
1160       remote_bank_code      => t8('Remote bank code'),
1161       currency              => t8('Currency'),
1162       purpose               => t8('Purpose'),
1163       local_account_number  => t8('Local account number'),
1164       local_bank_code       => t8('Local bank code'),
1165       local_bank_name       => t8('Bank account'),
1166     },
1167     with_objects => [ 'local_bank_account', 'currency' ],
1168   );
1169 }
1170
1171 sub load_ap_record_template_url {
1172   my ($self, $template) = @_;
1173
1174   return $self->url_for(
1175     controller                           => 'ap.pl',
1176     action                               => 'load_record_template',
1177     id                                   => $template->id,
1178     'form_defaults.amount_1'             => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
1179     'form_defaults.transdate'            => $self->transaction->transdate_as_date,
1180     'form_defaults.duedate'              => $self->transaction->transdate_as_date,
1181     'form_defaults.no_payment_bookings'  => 1,
1182     'form_defaults.paid_1_suggestion'    => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
1183     'form_defaults.AP_paid_1_suggestion' => $self->transaction->local_bank_account->chart->accno,
1184     'form_defaults.callback'             => $self->callback,
1185     'form_defaults.notes'                => $self->convert_purpose_for_template($template, $self->transaction->purpose),
1186   );
1187 }
1188
1189 sub load_gl_record_template_url {
1190   my ($self, $template) = @_;
1191
1192   return $self->url_for(
1193     controller                           => 'gl.pl',
1194     action                               => 'load_record_template',
1195     id                                   => $template->id,
1196     'form_defaults.amount_1'             => abs($self->transaction->not_assigned_amount), # always positive
1197     'form_defaults.transdate'            => $self->transaction->transdate_as_date,
1198     'form_defaults.callback'             => $self->callback,
1199     'form_defaults.bt_id'                => $self->transaction->id,
1200     'form_defaults.bt_chart_id'          => $self->transaction->local_bank_account->chart->id,
1201     'form_defaults.description'          => $self->convert_purpose_for_template($template, $self->transaction->purpose),
1202   );
1203 }
1204
1205 sub convert_purpose_for_template {
1206   my ($self, $template, $purpose) = @_;
1207
1208   # enter custom code here
1209
1210   return $purpose;
1211 }
1212
1213 sub setup_search_action_bar {
1214   my ($self, %params) = @_;
1215
1216   for my $bar ($::request->layout->get('actionbar')) {
1217     $bar->add(
1218       action => [
1219         t8('Filter'),
1220         submit    => [ '#search_form', { action => 'BankTransaction/list' } ],
1221         accesskey => 'enter',
1222       ],
1223     );
1224   }
1225 }
1226
1227 sub setup_list_all_action_bar {
1228   my ($self, %params) = @_;
1229
1230   for my $bar ($::request->layout->get('actionbar')) {
1231     $bar->add(
1232       combobox => [
1233         action => [ t8('Actions') ],
1234         action => [
1235           t8('Unlink bank transactions'),
1236             submit => [ '#form', { action => 'BankTransaction/unlink_bank_transaction' } ],
1237             checks => [ [ 'kivi.check_if_entries_selected', '[name="ids[]"]' ] ],
1238             disabled  => $::instance_conf->get_payments_changeable ? t8('Cannot safely unlink bank transactions, please set the posting configuration for payments to unchangeable.') : undef,
1239           ],
1240         ],
1241         action => [
1242           t8('Filter'),
1243           submit    => [ '#filter_form', { action => 'BankTransaction/list_all' } ],
1244         accesskey => 'enter',
1245       ],
1246     );
1247   }
1248 }
1249
1250 1;
1251 __END__
1252
1253 =pod
1254
1255 =encoding utf8
1256
1257 =head1 NAME
1258
1259 SL::Controller::BankTransaction - Posting payments to invoices from
1260 bank transactions imported earlier
1261
1262 =head1 FUNCTIONS
1263
1264 =over 4
1265
1266 =item C<save_single_bank_transaction %params>
1267
1268 Takes a bank transaction ID (as parameter C<bank_transaction_id> and
1269 tries to post its amount to a certain number of invoices (parameter
1270 C<invoice_ids>, an array ref of database IDs to purchase or sales
1271 invoice objects).
1272
1273 This method handles already partly assigned bank transactions.
1274
1275 This method cannot handle already partly assigned bank transactions, i.e.
1276 a bank transaction that has a invoice_amount <> 0 but not the fully
1277 transaction amount (invoice_amount == amount).
1278
1279 If the amount of the bank transaction is higher than the sum of
1280 the assigned invoices (1 .. n) the bank transaction will only be
1281 partly assigned.
1282
1283 The whole function is wrapped in a database transaction. If an
1284 exception occurs the bank transaction is not posted at all. The same
1285 is true if the code detects an error during the execution, e.g. a bank
1286 transaction that's already been posted earlier. In both cases the
1287 database transaction will be rolled back.
1288
1289 If warnings but not errors occur the database transaction is still
1290 committed.
1291
1292 The return value is an error object or C<undef> if the function
1293 succeeded. The calling function will collect all warnings and errors
1294 and display them in a nicely formatted table if any occurred.
1295
1296 An error object is a hash reference containing the following members:
1297
1298 =over 2
1299
1300 =item * C<result> — can be either C<warning> or C<error>. Warnings are
1301 displayed slightly different than errors.
1302
1303 =item * C<message> — a human-readable message included in the list of
1304 errors meant as the description of why the problem happened
1305
1306 =item * C<bank_transaction_id>, C<invoice_ids> — the same parameters
1307 that the function was called with
1308
1309 =item * C<bank_transaction> — the database object
1310 (C<SL::DB::BankTransaction>) corresponding to C<bank_transaction_id>
1311
1312 =item * C<invoices> — an array ref of the database objects (either
1313 C<SL::DB::Invoice> or C<SL::DB::PurchaseInvoice>) corresponding to
1314 C<invoice_ids>
1315
1316 =back
1317
1318 =item C<action_unlink_bank_transaction>
1319
1320 Takes one or more bank transaction ID (as parameter C<form::ids>) and
1321 tries to revert all payment bookings including already cleared bookings.
1322
1323 This method won't undo payments that are in a closed period and assumes
1324 that payments are not manually changed, i.e. only imported payments.
1325
1326 GL-records will be deleted completely if a bank transaction was the source.
1327
1328 TODO: we still rely on linked_records for the check boxes
1329
1330 =item C<convert_purpose_for_template>
1331
1332 This method can be used to parse, filter and convert the bank transaction's
1333 purpose string before it will be assigned to the description field of a
1334 gl transaction or to the notes field of an ap transaction.
1335 You have to write your own custom code.
1336
1337 =item C<_check_and_book_credit_note>
1338
1339 This method takes a array of invoices with two entries one one valid credit note
1340 and books the amount of the credit note against the invoice via the default
1341 transfer items account (i.e. SKR04 1370) and adds a source and memo entry for the
1342 payment booking.
1343 Logical and visual linking of the payment booking and credit note record to the bank
1344 transaction will also be done (necessary cond. for unlinking a bank transaction).
1345 If the methods success the credit note will be deleted from
1346 the original caller's array and he can further process the data without pondering
1347 about the removed credit note data.
1348
1349 =back
1350
1351 =head1 AUTHOR
1352
1353 Niclas Zimmermann E<lt>niclas@kivitendo-premium.deE<gt>,
1354 Geoffrey Richardson E<lt>information@richardson-bueren.deE<gt>
1355
1356 =cut