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