1 package SL::Controller::BankTransaction;
3 # idee- möglichkeit bankdaten zu übernehmen in stammdaten
4 # erst Kontenabgleich, um alle gl-Einträge wegzuhaben
7 use parent qw(SL::Controller::Base);
9 use SL::Controller::Helper::GetModels;
10 use SL::Controller::Helper::ReportGenerator;
11 use SL::ReportGenerator;
13 use SL::DB::BankTransaction;
14 use SL::Helper::Flash;
15 use SL::Locale::String;
18 use SL::DB::PurchaseInvoice;
19 use SL::DB::RecordLink;
22 use SL::DB::AccTransaction;
24 use SL::DB::BankAccount;
25 use SL::DB::RecordTemplate;
26 use SL::DB::SepaExportItem;
27 use SL::DBUtils qw(like);
30 use List::MoreUtils qw(any);
31 use List::Util qw(max);
33 use Rose::Object::MakeMethods::Generic
35 scalar => [ qw(callback transaction) ],
36 'scalar --get_set_init' => [ qw(models problems) ],
39 __PACKAGE__->run_before('check_auth');
49 my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted( query => [ obsolete => 0 ] );
51 $self->setup_search_action_bar;
52 $self->render('bank_transactions/search',
53 BANK_ACCOUNTS => $bank_accounts);
59 $self->make_filter_summary;
60 $self->prepare_report;
62 $self->setup_list_all_action_bar;
63 $self->report_generator_list_objects(report => $self->{report}, objects => $self->models->get);
69 if (!$::form->{filter}{bank_account}) {
70 flash('error', t8('No bank account chosen!'));
75 my $sort_by = $::form->{sort_by} || 'transdate';
76 $sort_by = 'transdate' if $sort_by eq 'proposal';
77 $sort_by .= $::form->{sort_dir} ? ' DESC' : ' ASC';
79 my $fromdate = $::locale->parse_date_to_object($::form->{filter}->{fromdate});
80 my $todate = $::locale->parse_date_to_object($::form->{filter}->{todate});
81 $todate->add( days => 1 ) if $todate;
84 push @where, (transdate => { ge => $fromdate }) if ($fromdate);
85 push @where, (transdate => { lt => $todate }) if ($todate);
86 my $bank_account = SL::DB::Manager::BankAccount->find_by( id => $::form->{filter}{bank_account} );
87 # bank_transactions no younger than starting date,
88 # including starting date (same search behaviour as fromdate)
89 # but OPEN invoices to be matched may be from before
90 if ( $bank_account->reconciliation_starting_date ) {
91 push @where, (transdate => { ge => $bank_account->reconciliation_starting_date });
94 my $bank_transactions = SL::DB::Manager::BankTransaction->get_all(
95 with_objects => [ 'local_bank_account', 'currency' ],
99 amount => {ne => \'invoice_amount'},
100 local_bank_account_id => $::form->{filter}{bank_account},
105 # credit notes have a negative amount, treat differently
106 my $all_open_ar_invoices = SL::DB::Manager::Invoice ->get_all(where => [ or => [ amount => { gt => \'paid' },
107 and => [ type => 'credit_note',
108 amount => { lt => \'paid' }
112 with_objects => ['customer','payment_terms']);
114 my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => [amount => { ne => \'paid' }], with_objects => ['vendor' ,'payment_terms']);
115 my $all_open_sepa_export_items = SL::DB::Manager::SepaExportItem->get_all(where => [chart_id => $bank_account->chart_id ,
116 'sepa_export.executed' => 0, 'sepa_export.closed' => 0 ], with_objects => ['sepa_export']);
118 my @all_open_invoices;
119 # filter out invoices with less than 1 cent outstanding
120 push @all_open_invoices, map { $_->{is_ar}=1 ; $_ } grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ar_invoices };
121 push @all_open_invoices, map { $_->{is_ar}=0 ; $_ } grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
124 # first collect sepa export items to open invoices
125 foreach my $open_invoice (@all_open_invoices){
126 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount,2);
127 $open_invoice->{skonto_type} = 'without_skonto';
128 foreach ( @{$all_open_sepa_export_items}) {
129 if ( $_->ap_id == $open_invoice->id || $_->ar_id == $open_invoice->id ) {
130 my $factor = ($_->ar_id == $open_invoice->id?1:-1);
131 #$main::lxdebug->message(LXDebug->DEBUG2(),"sepa_exitem=".$_->id." for invoice ".$open_invoice->id." factor=".$factor);
132 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount*$factor,2);
133 push @{$open_invoice->{sepa_export_item}} , $_ ;
134 $open_invoice->{skonto_type} = $_->payment_type;
135 $sepa_exports{$_->sepa_export_id} ||= { count => 0, is_ar => 0, amount => 0, proposed => 0, invoices => [], item => $_ };
136 $sepa_exports{$_->sepa_export_id}->{count}++ ;
137 $sepa_exports{$_->sepa_export_id}->{is_ar}++ if $_->ar_id == $open_invoice->id;
138 $sepa_exports{$_->sepa_export_id}->{amount} += $_->amount * $factor;
139 push @{ $sepa_exports{$_->sepa_export_id}->{invoices} }, $open_invoice;
144 # try to match each bank_transaction with each of the possible open invoices
148 foreach my $bt (@{ $bank_transactions }) {
149 ## 5 Stellen hinter dem Komma auf 2 Stellen reduzieren
150 $bt->amount($bt->amount*1);
151 $bt->invoice_amount($bt->invoice_amount*1);
153 $bt->{proposals} = [];
154 $bt->{rule_matches} = [];
156 $bt->{remote_name} .= $bt->{remote_name_1} if $bt->{remote_name_1};
158 if ( $bt->is_batch_transaction ) {
159 foreach ( keys %sepa_exports) {
160 if ( abs(($sepa_exports{$_}->{amount} * 1) - ($bt->amount * 1)) < 0.01 ) {
162 @{$bt->{proposals}} = @{$sepa_exports{$_}->{invoices}};
163 $bt->{sepa_export_ok} = 1;
164 $sepa_exports{$_}->{proposed}=1;
165 push(@proposals, $bt);
169 # batch transaction has no remotename !!
171 next unless $bt->{remote_name}; # bank has no name, usually fees, use create invoice to assign
174 # try to match the current $bt to each of the open_invoices, saving the
175 # results of get_agreement_with_invoice in $open_invoice->{agreement} and
176 # $open_invoice->{rule_matches}.
178 # The values are overwritten each time a new bt is checked, so at the end
179 # of each bt the likely results are filtered and those values are stored in
180 # the arrays $bt->{proposals} and $bt->{rule_matches}, and the agreement
181 # score is stored in $bt->{agreement}
183 foreach my $open_invoice (@all_open_invoices) {
184 ($open_invoice->{agreement}, $open_invoice->{rule_matches}) = $bt->get_agreement_with_invoice($open_invoice);
185 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,
186 $open_invoice->amount * ($open_invoice->{is_ar} ? 1 : -1), 2);
190 my $min_agreement = 3; # suggestions must have at least this score
192 my $max_agreement = max map { $_->{agreement} } @all_open_invoices;
194 # add open_invoices with highest agreement into array $bt->{proposals}
195 if ( $max_agreement >= $min_agreement ) {
196 $bt->{proposals} = [ grep { $_->{agreement} == $max_agreement } @all_open_invoices ];
197 $bt->{agreement} = $max_agreement; #scalar @{ $bt->{proposals} } ? $agreement + 1 : '';
199 # store the rule_matches in a separate array, so they can be displayed in template
200 foreach ( @{ $bt->{proposals} } ) {
201 push(@{$bt->{rule_matches}}, $_->{rule_matches});
207 # separate filter for proposals (second tab, agreement >= 5 and exactly one match)
208 # to qualify as a proposal there has to be
209 # * agreement >= 5 TODO: make threshold configurable in configuration
210 # * there must be only one exact match
211 # * depending on whether sales or purchase the amount has to have the correct sign (so Gutschriften don't work?)
212 my $proposal_threshold = 5;
213 my @otherproposals = grep {
214 ($_->{agreement} >= $proposal_threshold)
215 && (1 == scalar @{ $_->{proposals} })
216 && (@{ $_->{proposals} }[0]->is_sales ? abs(@{ $_->{proposals} }[0]->amount - $_->amount) < 0.01
217 : abs(@{ $_->{proposals} }[0]->amount + $_->amount) < 0.01)
218 } @{ $bank_transactions };
220 push @proposals, @otherproposals;
222 # sort bank transaction proposals by quality (score) of proposal
223 $bank_transactions = [ sort { $a->{agreement} <=> $b->{agreement} } @{ $bank_transactions } ] if $::form->{sort_by} eq 'proposal' and $::form->{sort_dir} == 1;
224 $bank_transactions = [ sort { $b->{agreement} <=> $a->{agreement} } @{ $bank_transactions } ] if $::form->{sort_by} eq 'proposal' and $::form->{sort_dir} == 0;
226 # for testing with t/bank/banktransaction.t :
227 if ( $::form->{dont_render_for_test} ) {
228 return $bank_transactions;
231 $::request->layout->add_javascripts("kivi.BankTransaction.js");
232 $self->render('bank_transactions/list',
233 title => t8('Bank transactions MT940'),
234 BANK_TRANSACTIONS => $bank_transactions,
235 PROPOSALS => \@proposals,
236 bank_account => $bank_account,
237 ui_tab => scalar(@proposals) > 0?1:0,
241 sub action_assign_invoice {
244 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
246 $self->render('bank_transactions/assign_invoice',
248 title => t8('Assign invoice'),);
251 sub action_create_invoice {
253 my %myconfig = %main::myconfig;
255 $self->transaction(SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id}));
257 my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(account_number => $self->transaction->{remote_account_number});
258 my $use_vendor_filter = $self->transaction->{remote_account_number} && $vendor_of_transaction;
260 my $templates = SL::DB::Manager::RecordTemplate->get_all(
261 where => [ template_type => 'ap_transaction' ],
262 with_objects => [ qw(employee vendor) ],
266 $templates = [ grep { $_->vendor_id == $vendor_of_transaction->id } @{ $templates } ] if $use_vendor_filter;
268 $self->callback($self->url_for(
270 'filter.bank_account' => $::form->{filter}->{bank_account},
271 'filter.todate' => $::form->{filter}->{todate},
272 'filter.fromdate' => $::form->{filter}->{fromdate},
276 'bank_transactions/create_invoice',
278 title => t8('Create invoice'),
279 TEMPLATES => $templates,
280 vendor_id => $use_vendor_filter ? $vendor_of_transaction->id : undef,
281 vendor_name => $use_vendor_filter ? $vendor_of_transaction->name : undef,
285 sub action_ajax_payment_suggestion {
288 # based on a BankTransaction ID and a Invoice or PurchaseInvoice ID passed via $::form,
289 # create an HTML blob to be used by the js function add_invoices in templates/webpages/bank_transactions/list.html
290 # and return encoded as JSON
292 my $bt = SL::DB::Manager::BankTransaction->find_by( id => $::form->{bt_id} );
293 my $invoice = SL::DB::Manager::Invoice->find_by( id => $::form->{prop_id} ) || SL::DB::Manager::PurchaseInvoice->find_by( id => $::form->{prop_id} );
295 die unless $bt and $invoice;
297 my @select_options = $invoice->get_payment_select_options_for_bank_transaction($::form->{bt_id});
300 $html = $self->render(
301 'bank_transactions/_payment_suggestion', { output => 0 },
302 bt_id => $::form->{bt_id},
303 prop_id => $::form->{prop_id},
305 SELECT_OPTIONS => \@select_options,
308 $self->render(\ SL::JSON::to_json( { 'html' => "$html" } ), { layout => 0, type => 'json', process => 0 });
311 sub action_filter_templates {
314 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
315 my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(account_number => $self->{transaction}->{remote_account_number});
318 push @filter, ('vendor.id' => $::form->{vendor_id}) if $::form->{vendor_id};
319 push @filter, ('vendor.name' => { ilike => '%' . $::form->{vendor} . '%' }) if $::form->{vendor};
321 my $templates = SL::DB::Manager::RecordTemplate->get_all(
322 where => [ template_type => 'ap_transaction', (or => \@filter) x !!@filter ],
323 with_objects => [ qw(employee vendor) ],
326 $::form->{filter} //= {};
328 $self->callback($self->url_for(
330 'filter.bank_account' => $::form->{filter}->{bank_account},
331 'filter.todate' => $::form->{filter}->{todate},
332 'filter.fromdate' => $::form->{filter}->{fromdate},
335 my $output = $self->render(
336 'bank_transactions/_template_list',
338 TEMPLATES => $templates,
341 $self->render(\to_json({ html => $output }), { type => 'json', process => 0 });
344 sub action_ajax_add_list {
347 my @where_sale = (amount => { ne => \'paid' });
348 my @where_purchase = (amount => { ne => \'paid' });
350 if ($::form->{invnumber}) {
351 push @where_sale, (invnumber => { ilike => like($::form->{invnumber})});
352 push @where_purchase, (invnumber => { ilike => like($::form->{invnumber})});
355 if ($::form->{amount}) {
356 push @where_sale, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
357 push @where_purchase, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
360 if ($::form->{vcnumber}) {
361 push @where_sale, ('customer.customernumber' => { ilike => like($::form->{vcnumber})});
362 push @where_purchase, ('vendor.vendornumber' => { ilike => like($::form->{vcnumber})});
365 if ($::form->{vcname}) {
366 push @where_sale, ('customer.name' => { ilike => like($::form->{vcname})});
367 push @where_purchase, ('vendor.name' => { ilike => like($::form->{vcname})});
370 if ($::form->{transdatefrom}) {
371 my $fromdate = $::locale->parse_date_to_object($::form->{transdatefrom});
372 if ( ref($fromdate) eq 'DateTime' ) {
373 push @where_sale, ('transdate' => { ge => $fromdate});
374 push @where_purchase, ('transdate' => { ge => $fromdate});
378 if ($::form->{transdateto}) {
379 my $todate = $::locale->parse_date_to_object($::form->{transdateto});
380 if ( ref($todate) eq 'DateTime' ) {
381 $todate->add(days => 1);
382 push @where_sale, ('transdate' => { lt => $todate});
383 push @where_purchase, ('transdate' => { lt => $todate});
387 my $all_open_ar_invoices = SL::DB::Manager::Invoice ->get_all(where => \@where_sale, with_objects => 'customer');
388 my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => \@where_purchase, with_objects => 'vendor');
390 my @all_open_invoices = @{ $all_open_ar_invoices };
391 # add ap invoices, filtering out subcent open amounts
392 push @all_open_invoices, grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
394 @all_open_invoices = sort { $a->id <=> $b->id } @all_open_invoices;
396 my $output = $self->render(
397 'bank_transactions/add_list',
399 INVOICES => \@all_open_invoices,
402 my %result = ( count => 0, html => $output );
404 $self->render(\to_json(\%result), { type => 'json', process => 0 });
407 sub action_ajax_accept_invoices {
410 my @selected_invoices;
411 foreach my $invoice_id (@{ $::form->{invoice_id} || [] }) {
412 my $invoice_object = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
413 push @selected_invoices, $invoice_object;
417 'bank_transactions/invoices',
419 INVOICES => \@selected_invoices,
420 bt_id => $::form->{bt_id},
427 return 0 if !$::form->{invoice_ids};
429 my %invoice_hash = %{ delete $::form->{invoice_ids} }; # each key (the bt line with a bt_id) contains an array of invoice_ids
431 # e.g. three partial payments with bt_ids 54, 55 and 56 for invoice with id 74:
444 # or if the payment with bt_id 44 is used to pay invoices with ids 50, 51 and 52
446 # '44' => [ '50', '51', 52' ]
449 $::form->{invoice_skontos} ||= {}; # hash of arrays containing the payment types, could be empty
451 # a bank_transaction may be assigned to several invoices, i.e. a customer
452 # might pay several open invoices with one transaction
458 if ( $::form->{proposal_ids} ) {
459 foreach (@{ $::form->{proposal_ids} }) {
460 my $bank_transaction_id = $_;
461 my $invoice_ids = $invoice_hash{$_};
462 push @{ $self->problems }, $self->save_single_bank_transaction(
463 bank_transaction_id => $bank_transaction_id,
464 invoice_ids => $invoice_ids,
465 sources => ($::form->{sources} // {})->{$_},
466 memos => ($::form->{memos} // {})->{$_},
468 $count += scalar( @{$invoice_ids} );
471 while ( my ($bank_transaction_id, $invoice_ids) = each(%invoice_hash) ) {
472 push @{ $self->problems }, $self->save_single_bank_transaction(
473 bank_transaction_id => $bank_transaction_id,
474 invoice_ids => $invoice_ids,
475 sources => [ map { $::form->{"sources_${bank_transaction_id}_${_}"} } @{ $invoice_ids } ],
476 memos => [ map { $::form->{"memos_${bank_transaction_id}_${_}"} } @{ $invoice_ids } ],
478 $count += scalar( @{$invoice_ids} );
481 foreach (@{ $self->problems }) {
482 $count-- if $_->{result} eq 'error';
487 sub action_save_invoices {
489 my $count = $self->save_invoices();
491 flash('ok', t8('#1 invoice(s) saved.', $count));
493 $self->action_list();
496 sub action_save_proposals {
499 if ( $::form->{proposal_ids} ) {
500 my $propcount = scalar(@{ $::form->{proposal_ids} });
501 if ( $propcount > 0 ) {
502 my $count = $self->save_invoices();
504 flash('ok', t8('#1 proposal(s) with #2 invoice(s) saved.', $propcount, $count));
507 $self->action_list();
511 sub save_single_bank_transaction {
512 my ($self, %params) = @_;
516 bank_transaction => SL::DB::Manager::BankTransaction->find_by(id => $params{bank_transaction_id}),
520 if (!$data{bank_transaction}) {
524 message => $::locale->text('The ID #1 is not a valid database ID.', $data{bank_transaction_id}),
531 my $bt_id = $data{bank_transaction_id};
532 my $bank_transaction = $data{bank_transaction};
533 my $sign = $bank_transaction->amount < 0 ? -1 : 1;
534 my $amount_of_transaction = $sign * $bank_transaction->amount;
535 my $payment_received = $bank_transaction->amount > 0;
536 my $payment_sent = $bank_transaction->amount < 0;
539 foreach my $invoice_id (@{ $params{invoice_ids} }) {
540 my $invoice = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
545 message => $::locale->text("The ID #1 is not a valid database ID.", $invoice_id),
548 push @{ $data{invoices} }, $invoice;
551 if ( $payment_received
552 && any { ( $_->is_sales && ($_->amount < 0))
553 || (!$_->is_sales && ($_->amount > 0))
554 } @{ $data{invoices} }) {
558 message => $::locale->text("Received payments can only be posted for sales invoices and purchase credit notes."),
563 && any { ( $_->is_sales && ($_->amount > 0))
564 || (!$_->is_sales && ($_->amount < 0) && ($_->invoice_type eq 'purchase_invoice'))
565 } @{ $data{invoices} }) {
569 message => $::locale->text("Sent payments can only be posted for purchase invoices and sales credit notes."),
573 my $max_invoices = scalar(@{ $data{invoices} });
576 foreach my $invoice (@{ $data{invoices} }) {
577 my $source = ($data{sources} // [])->[$n_invoices];
578 my $memo = ($data{memos} // [])->[$n_invoices];
582 # Check if bank_transaction already has a link to the invoice, may only be linked once per invoice
583 # This might be caused by the user reloading a page and resending the form
584 if (_existing_record_link($bank_transaction, $invoice)) {
588 message => $::locale->text("Bank transaction with id #1 has already been linked to #2.", $bank_transaction->id, $invoice->displayable_name),
592 if (!$amount_of_transaction && $invoice->open_amount) {
596 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."),
601 if ( defined $::form->{invoice_skontos}->{"$bt_id"} ) {
602 $payment_type = shift(@{ $::form->{invoice_skontos}->{"$bt_id"} });
604 $payment_type = 'without_skonto';
608 # pay invoice or go to the next bank transaction if the amount is not sufficiently high
609 if ($invoice->open_amount <= $amount_of_transaction && $n_invoices < $max_invoices) {
610 my $open_amount = ($payment_type eq 'with_skonto_pt'?$invoice->amount_less_skonto:$invoice->open_amount);
611 # first calculate new bank transaction amount ...
612 if ($invoice->is_sales) {
613 $amount_of_transaction -= $sign * $open_amount;
614 $bank_transaction->invoice_amount($bank_transaction->invoice_amount + $open_amount);
616 $amount_of_transaction += $sign * $open_amount;
617 $bank_transaction->invoice_amount($bank_transaction->invoice_amount - $open_amount);
619 # ... and then pay the invoice
620 $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
621 trans_id => $invoice->id,
622 amount => $open_amount,
623 payment_type => $payment_type,
626 transdate => $bank_transaction->transdate->to_kivitendo);
628 # use the whole amount of the bank transaction for the invoice, overpay the invoice if necessary
630 # this catches credit_notes and negative sales invoices
631 if ( $invoice->is_sales && $invoice->amount < 0 ) {
632 # $invoice->open_amount is negative for credit_notes
633 # $bank_transaction->amount is negative for outgoing transactions
634 # so $amount_of_transaction is negative but needs positive
635 $amount_of_transaction *= -1;
637 } elsif (!$invoice->is_sales && $invoice->invoice_type eq 'ap_transaction' ) {
638 # $invoice->open_amount may be negative for ap_transaction but may be positiv for negativ ap_transaction
639 # if $invoice->open_amount is negative $bank_transaction->amount is positve
640 # if $invoice->open_amount is positive $bank_transaction->amount is negative
641 # but amount of transaction is for both positive
642 $amount_of_transaction *= -1 if $invoice->open_amount == - $amount_of_transaction;
645 my $overpaid_amount = $amount_of_transaction - $invoice->open_amount;
646 $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
647 trans_id => $invoice->id,
648 amount => $amount_of_transaction,
649 payment_type => $payment_type,
652 transdate => $bank_transaction->transdate->to_kivitendo);
653 $bank_transaction->invoice_amount($bank_transaction->amount);
654 $amount_of_transaction = 0;
656 if ($overpaid_amount >= 0.01) {
660 message => $::locale->text('Invoice #1 was overpaid by #2.', $invoice->invnumber, $::form->format_amount(\%::myconfig, $overpaid_amount, 2)),
664 # Record a record link from the bank transaction to the invoice
666 from_table => 'bank_transactions',
668 to_table => $invoice->is_sales ? 'ar' : 'ap',
669 to_id => $invoice->id,
672 SL::DB::RecordLink->new(@props)->save;
674 # "close" a sepa_export_item if it exists
675 # code duplicated in action_save_proposals!
676 # currently only works, if there is only exactly one open sepa_export_item
677 if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
678 if ( scalar @$seis == 1 ) {
679 # moved the execution and the check for sepa_export into a method,
680 # this isn't part of a transaction, though
681 $seis->[0]->set_executed if $invoice->id == $seis->[0]->arap_id;
686 $bank_transaction->save;
688 # 'undef' means 'no error' here.
693 my $rez = $data{bank_transaction}->db->with_transaction(sub {
695 $error = $worker->();
706 # Rollback Fehler nicht weiterreichen
710 return grep { $_ } ($error, @warnings);
718 $::auth->assert('bank_transaction');
725 sub make_filter_summary {
728 my $filter = $::form->{filter} || {};
732 [ $filter->{"transdate:date::ge"}, $::locale->text('Transdate') . " " . $::locale->text('From Date') ],
733 [ $filter->{"transdate:date::le"}, $::locale->text('Transdate') . " " . $::locale->text('To Date') ],
734 [ $filter->{"valutadate:date::ge"}, $::locale->text('Valutadate') . " " . $::locale->text('From Date') ],
735 [ $filter->{"valutadate:date::le"}, $::locale->text('Valutadate') . " " . $::locale->text('To Date') ],
736 [ $filter->{"amount:number"}, $::locale->text('Amount') ],
737 [ $filter->{"bank_account_id:integer"}, $::locale->text('Local bank account') ],
741 push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
744 $self->{filter_summary} = join ', ', @filter_strings;
750 my $callback = $self->models->get_callback;
752 my $report = SL::ReportGenerator->new(\%::myconfig, $::form);
753 $self->{report} = $report;
755 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);
756 my @sortable = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount purpose local_account_number local_bank_code);
759 transdate => { sub => sub { $_[0]->transdate_as_date } },
760 valutadate => { sub => sub { $_[0]->valutadate_as_date } },
762 remote_account_number => { },
763 remote_bank_code => { },
764 amount => { sub => sub { $_[0]->amount_as_number },
766 invoice_amount => { sub => sub { $_[0]->invoice_amount_as_number },
768 invoices => { sub => sub { $_[0]->linked_invoices } },
769 currency => { sub => sub { $_[0]->currency->name } },
771 local_account_number => { sub => sub { $_[0]->local_bank_account->account_number } },
772 local_bank_code => { sub => sub { $_[0]->local_bank_account->bank_code } },
773 local_bank_name => { sub => sub { $_[0]->local_bank_account->name } },
777 map { $column_defs{$_}->{text} ||= $::locale->text( $self->models->get_sort_spec->{$_}->{title} ) } keys %column_defs;
779 $report->set_options(
780 std_column_visibility => 1,
781 controller_class => 'BankTransaction',
782 output_format => 'HTML',
783 top_info_text => $::locale->text('Bank transactions'),
784 title => $::locale->text('Bank transactions'),
785 allow_pdf_export => 1,
786 allow_csv_export => 1,
788 $report->set_columns(%column_defs);
789 $report->set_column_order(@columns);
790 $report->set_export_options(qw(list_all filter));
791 $report->set_options_from_form;
792 $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
793 $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
795 my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted();
797 $report->set_options(
798 raw_top_info_text => $self->render('bank_transactions/report_top', { output => 0 }, BANK_ACCOUNTS => $bank_accounts),
799 raw_bottom_info_text => $self->render('bank_transactions/report_bottom', { output => 0 }),
803 sub _existing_record_link {
804 my ($bt, $invoice) = @_;
806 # check whether a record link from banktransaction $bt already exists to
807 # invoice $invoice, returns 1 if that is the case
809 die unless $bt->isa("SL::DB::BankTransaction") && ( $invoice->isa("SL::DB::Invoice") || $invoice->isa("SL::DB::PurchaseInvoice") );
811 my $linked_record_to_table = $invoice->is_sales ? 'Invoice' : 'PurchaseInvoice';
812 my $linked_records = $bt->linked_records( direction => 'to', to => $linked_record_to_table, query => [ id => $invoice->id ] );
814 return @$linked_records ? 1 : 0;
817 sub init_problems { [] }
822 SL::Controller::Helper::GetModels->new(
827 dir => 0, # 1 = ASC, 0 = DESC : default sort is newest at top
829 transdate => t8('Transdate'),
830 remote_name => t8('Remote name'),
831 amount => t8('Amount'),
832 invoice_amount => t8('Assigned'),
833 invoices => t8('Linked invoices'),
834 valutadate => t8('Valutadate'),
835 remote_account_number => t8('Remote account number'),
836 remote_bank_code => t8('Remote bank code'),
837 currency => t8('Currency'),
838 purpose => t8('Purpose'),
839 local_account_number => t8('Local account number'),
840 local_bank_code => t8('Local bank code'),
841 local_bank_name => t8('Bank account'),
843 with_objects => [ 'local_bank_account', 'currency' ],
847 sub load_ap_record_template_url {
848 my ($self, $template) = @_;
850 return $self->url_for(
851 controller => 'ap.pl',
852 action => 'load_record_template',
854 'form_defaults.amount_1' => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
855 'form_defaults.transdate' => $self->transaction->transdate_as_date,
856 'form_defaults.duedate' => $self->transaction->transdate_as_date,
857 'form_defaults.no_payment_bookings' => 1,
858 'form_defaults.paid_1_suggestion' => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
859 'form_defaults.AP_paid_1_suggestion' => $self->transaction->local_bank_account->chart->accno,
860 'form_defaults.callback' => $self->callback,
864 sub setup_search_action_bar {
865 my ($self, %params) = @_;
867 for my $bar ($::request->layout->get('actionbar')) {
871 submit => [ '#search_form', { action => 'BankTransaction/list' } ],
872 accesskey => 'enter',
878 sub setup_list_all_action_bar {
879 my ($self, %params) = @_;
881 for my $bar ($::request->layout->get('actionbar')) {
885 submit => [ '#filter_form', { action => 'BankTransaction/list_all' } ],
886 accesskey => 'enter',
901 SL::Controller::BankTransaction - Posting payments to invoices from
902 bank transactions imported earlier
908 =item C<save_single_bank_transaction %params>
910 Takes a bank transaction ID (as parameter C<bank_transaction_id> and
911 tries to post its amount to a certain number of invoices (parameter
912 C<invoice_ids>, an array ref of database IDs to purchase or sales
915 The whole function is wrapped in a database transaction. If an
916 exception occurs the bank transaction is not posted at all. The same
917 is true if the code detects an error during the execution, e.g. a bank
918 transaction that's already been posted earlier. In both cases the
919 database transaction will be rolled back.
921 If warnings but not errors occur the database transaction is still
924 The return value is an error object or C<undef> if the function
925 succeeded. The calling function will collect all warnings and errors
926 and display them in a nicely formatted table if any occurred.
928 An error object is a hash reference containing the following members:
932 =item * C<result> — can be either C<warning> or C<error>. Warnings are
933 displayed slightly different than errors.
935 =item * C<message> — a human-readable message included in the list of
936 errors meant as the description of why the problem happened
938 =item * C<bank_transaction_id>, C<invoice_ids> — the same parameters
939 that the function was called with
941 =item * C<bank_transaction> — the database object
942 (C<SL::DB::BankTransaction>) corresponding to C<bank_transaction_id>
944 =item * C<invoices> — an array ref of the database objects (either
945 C<SL::DB::Invoice> or C<SL::DB::PurchaseInvoice>) corresponding to
954 Niclas Zimmermann E<lt>niclas@kivitendo-premium.deE<gt>,
955 Geoffrey Richardson E<lt>information@richardson-bueren.deE<gt>