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);
29 use List::MoreUtils qw(any);
30 use List::Util qw(max);
32 use Rose::Object::MakeMethods::Generic
34 scalar => [ qw(callback transaction) ],
35 'scalar --get_set_init' => [ qw(models problems) ],
38 __PACKAGE__->run_before('check_auth');
48 my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted( query => [ obsolete => 0 ] );
50 $self->setup_search_action_bar;
51 $self->render('bank_transactions/search',
52 BANK_ACCOUNTS => $bank_accounts);
58 $self->make_filter_summary;
59 $self->prepare_report;
61 $self->setup_list_all_action_bar;
62 $self->report_generator_list_objects(report => $self->{report}, objects => $self->models->get);
68 if (!$::form->{filter}{bank_account}) {
69 flash('error', t8('No bank account chosen!'));
74 my $sort_by = $::form->{sort_by} || 'transdate';
75 $sort_by = 'transdate' if $sort_by eq 'proposal';
76 $sort_by .= $::form->{sort_dir} ? ' DESC' : ' ASC';
78 my $fromdate = $::locale->parse_date_to_object($::form->{filter}->{fromdate});
79 my $todate = $::locale->parse_date_to_object($::form->{filter}->{todate});
80 $todate->add( days => 1 ) if $todate;
83 push @where, (transdate => { ge => $fromdate }) if ($fromdate);
84 push @where, (transdate => { lt => $todate }) if ($todate);
85 my $bank_account = SL::DB::Manager::BankAccount->find_by( id => $::form->{filter}{bank_account} );
86 # bank_transactions no younger than starting date,
87 # including starting date (same search behaviour as fromdate)
88 # but OPEN invoices to be matched may be from before
89 if ( $bank_account->reconciliation_starting_date ) {
90 push @where, (transdate => { ge => $bank_account->reconciliation_starting_date });
93 my $bank_transactions = SL::DB::Manager::BankTransaction->get_all(
94 with_objects => [ 'local_bank_account', 'currency' ],
98 amount => {ne => \'invoice_amount'},
99 local_bank_account_id => $::form->{filter}{bank_account},
103 # credit notes have a negative amount, treat differently
104 my $all_open_ar_invoices = SL::DB::Manager::Invoice ->get_all(where => [ or => [ amount => { gt => \'paid' },
105 and => [ type => 'credit_note',
106 amount => { lt => \'paid' }
110 with_objects => ['customer','payment_terms']);
112 my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => [amount => { ne => \'paid' }], with_objects => ['vendor' ,'payment_terms']);
113 my $all_open_sepa_export_items = SL::DB::Manager::SepaExportItem->get_all(where => [chart_id => $bank_account->chart_id ,
114 'sepa_export.executed' => 0, 'sepa_export.closed' => 0 ], with_objects => ['sepa_export']);
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 };
122 # first collect sepa export items to open invoices
123 foreach my $open_invoice (@all_open_invoices){
124 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount,2);
125 $open_invoice->{skonto_type} = 'without_skonto';
126 foreach ( @{$all_open_sepa_export_items}) {
127 if (($_->ap_id && $_->ap_id == $open_invoice->id) || ($_->ar_id && $_->ar_id == $open_invoice->id)) {
128 my $factor = ($_->ar_id == $open_invoice->id ? 1 : -1);
129 #$main::lxdebug->message(LXDebug->DEBUG2(),"sepa_exitem=".$_->id." for invoice ".$open_invoice->id." factor=".$factor);
130 $open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount*$factor,2);
131 $open_invoice->{skonto_type} = $_->payment_type;
132 $sepa_exports{$_->sepa_export_id} ||= { count => 0, is_ar => 0, amount => 0, proposed => 0, invoices => [], item => $_ };
133 $sepa_exports{$_->sepa_export_id}->{count}++;
134 $sepa_exports{$_->sepa_export_id}->{is_ar}++ if $_->ar_id == $open_invoice->id;
135 $sepa_exports{$_->sepa_export_id}->{amount} += $_->amount * $factor;
136 push @{ $sepa_exports{$_->sepa_export_id}->{invoices} }, $open_invoice;
141 # try to match each bank_transaction with each of the possible open invoices
145 foreach my $bt (@{ $bank_transactions }) {
146 ## 5 Stellen hinter dem Komma auf 2 Stellen reduzieren
147 $bt->amount($bt->amount*1);
148 $bt->invoice_amount($bt->invoice_amount*1);
150 $bt->{proposals} = [];
151 $bt->{rule_matches} = [];
153 $bt->{remote_name} .= $bt->{remote_name_1} if $bt->{remote_name_1};
155 if ( $bt->is_batch_transaction ) {
157 foreach ( keys %sepa_exports) {
158 if ( abs(($sepa_exports{$_}->{amount} * 1) - ($bt->amount * 1)) < 0.01 ) {
160 @{$bt->{proposals}} = @{$sepa_exports{$_}->{invoices}};
161 $bt->{sepa_export_ok} = 1;
162 $sepa_exports{$_}->{proposed}=1;
163 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 if ($::form->{sort_by} && $::form->{sort_by} eq 'proposal') {
224 if ($::form->{sort_dir}) {
225 $bank_transactions = [ sort { $a->{agreement} <=> $b->{agreement} } @{ $bank_transactions } ];
227 $bank_transactions = [ sort { $b->{agreement} <=> $a->{agreement} } @{ $bank_transactions } ];
231 # for testing with t/bank/banktransaction.t :
232 if ( $::form->{dont_render_for_test} ) {
233 return $bank_transactions;
236 $::request->layout->add_javascripts("kivi.BankTransaction.js");
237 $self->render('bank_transactions/list',
238 title => t8('Bank transactions MT940'),
239 BANK_TRANSACTIONS => $bank_transactions,
240 PROPOSALS => \@proposals,
241 bank_account => $bank_account,
242 ui_tab => scalar(@proposals) > 0?1:0,
246 sub action_assign_invoice {
249 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
251 $self->render('bank_transactions/assign_invoice',
253 title => t8('Assign invoice'),);
256 sub action_create_invoice {
258 my %myconfig = %main::myconfig;
260 $self->transaction(SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id}));
262 # This was dead code: We compared vendor.account_name with bank_transaction.iban.
263 # This did never match (Kontonummer != IBAN). It's kivis 09/02 (2013) day
264 # If refactored/improved, also consider that vendor.iban should be normalized
265 # user may like to input strings like: 'AT 3333 3333 2222 1111' -> can be checked strictly
266 # at Vendor code because we need the correct data for all sepa exports.
268 my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(iban => $self->transaction->{remote_account_number});
269 my $use_vendor_filter = $self->transaction->{remote_account_number} && $vendor_of_transaction;
271 my $templates_ap = SL::DB::Manager::RecordTemplate->get_all(
272 where => [ template_type => 'ap_transaction' ],
273 with_objects => [ qw(employee vendor) ],
275 my $templates_gl = SL::DB::Manager::RecordTemplate->get_all(
276 query => [ template_type => 'gl_transaction',
277 chart_id => SL::DB::Manager::BankAccount->find_by(id => $self->transaction->local_bank_account_id)->chart_id,
279 with_objects => [ qw(employee record_template_items) ],
282 # pre filter templates_ap, if we have a vendor match (IBAN eq IBAN) - show and allow user to edit this via gui!
283 $templates_ap = [ grep { $_->vendor_id == $vendor_of_transaction->id } @{ $templates_ap } ] if $use_vendor_filter;
285 $self->callback($self->url_for(
287 'filter.bank_account' => $::form->{filter}->{bank_account},
288 'filter.todate' => $::form->{filter}->{todate},
289 'filter.fromdate' => $::form->{filter}->{fromdate},
293 'bank_transactions/create_invoice',
295 title => t8('Create invoice'),
296 TEMPLATES_GL => $use_vendor_filter && @{ $templates_ap } ? undef : $templates_gl,
297 TEMPLATES_AP => $templates_ap,
298 vendor_name => $use_vendor_filter && @{ $templates_ap } ? $vendor_of_transaction->name : undef,
302 sub action_ajax_payment_suggestion {
305 # based on a BankTransaction ID and a Invoice or PurchaseInvoice ID passed via $::form,
306 # create an HTML blob to be used by the js function add_invoices in templates/webpages/bank_transactions/list.html
307 # and return encoded as JSON
309 my $bt = SL::DB::Manager::BankTransaction->find_by( id => $::form->{bt_id} );
310 my $invoice = SL::DB::Manager::Invoice->find_by( id => $::form->{prop_id} ) || SL::DB::Manager::PurchaseInvoice->find_by( id => $::form->{prop_id} );
312 die unless $bt and $invoice;
314 my @select_options = $invoice->get_payment_select_options_for_bank_transaction($::form->{bt_id});
317 $html = $self->render(
318 'bank_transactions/_payment_suggestion', { output => 0 },
319 bt_id => $::form->{bt_id},
320 prop_id => $::form->{prop_id},
322 SELECT_OPTIONS => \@select_options,
325 $self->render(\ SL::JSON::to_json( { 'html' => "$html" } ), { layout => 0, type => 'json', process => 0 });
328 sub action_filter_templates {
331 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
333 my (@filter, @filter_ap);
335 # filter => gl and ap | filter_ap = ap (i.e. vendorname)
336 push @filter, ('template_name' => { ilike => '%' . $::form->{template} . '%' }) if $::form->{template};
337 push @filter, ('reference' => { ilike => '%' . $::form->{reference} . '%' }) if $::form->{reference};
338 push @filter_ap, ('vendor.name' => { ilike => '%' . $::form->{vendor} . '%' }) if $::form->{vendor};
339 push @filter_ap, @filter;
340 my $templates_gl = SL::DB::Manager::RecordTemplate->get_all(
341 query => [ template_type => 'gl_transaction',
342 chart_id => SL::DB::Manager::BankAccount->find_by(id => $self->transaction->local_bank_account_id)->chart_id,
343 (and => \@filter) x !!@filter
345 with_objects => [ qw(employee record_template_items) ],
348 my $templates_ap = SL::DB::Manager::RecordTemplate->get_all(
349 where => [ template_type => 'ap_transaction', (and => \@filter_ap) x !!@filter_ap ],
350 with_objects => [ qw(employee vendor) ],
352 $::form->{filter} //= {};
354 $self->callback($self->url_for(
356 'filter.bank_account' => $::form->{filter}->{bank_account},
357 'filter.todate' => $::form->{filter}->{todate},
358 'filter.fromdate' => $::form->{filter}->{fromdate},
361 my $output = $self->render(
362 'bank_transactions/_template_list',
364 TEMPLATES_AP => $templates_ap,
365 TEMPLATES_GL => $templates_gl,
368 $self->render(\to_json({ html => $output }), { type => 'json', process => 0 });
371 sub action_ajax_add_list {
374 my @where_sale = (amount => { ne => \'paid' });
375 my @where_purchase = (amount => { ne => \'paid' });
377 if ($::form->{invnumber}) {
378 push @where_sale, (invnumber => { ilike => like($::form->{invnumber})});
379 push @where_purchase, (invnumber => { ilike => like($::form->{invnumber})});
382 if ($::form->{amount}) {
383 push @where_sale, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
384 push @where_purchase, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
387 if ($::form->{vcnumber}) {
388 push @where_sale, ('customer.customernumber' => { ilike => like($::form->{vcnumber})});
389 push @where_purchase, ('vendor.vendornumber' => { ilike => like($::form->{vcnumber})});
392 if ($::form->{vcname}) {
393 push @where_sale, ('customer.name' => { ilike => like($::form->{vcname})});
394 push @where_purchase, ('vendor.name' => { ilike => like($::form->{vcname})});
397 if ($::form->{transdatefrom}) {
398 my $fromdate = $::locale->parse_date_to_object($::form->{transdatefrom});
399 if ( ref($fromdate) eq 'DateTime' ) {
400 push @where_sale, ('transdate' => { ge => $fromdate});
401 push @where_purchase, ('transdate' => { ge => $fromdate});
405 if ($::form->{transdateto}) {
406 my $todate = $::locale->parse_date_to_object($::form->{transdateto});
407 if ( ref($todate) eq 'DateTime' ) {
408 $todate->add(days => 1);
409 push @where_sale, ('transdate' => { lt => $todate});
410 push @where_purchase, ('transdate' => { lt => $todate});
414 my $all_open_ar_invoices = SL::DB::Manager::Invoice ->get_all(where => \@where_sale, with_objects => 'customer');
415 my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => \@where_purchase, with_objects => 'vendor');
417 my @all_open_invoices = @{ $all_open_ar_invoices };
418 # add ap invoices, filtering out subcent open amounts
419 push @all_open_invoices, grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
421 @all_open_invoices = sort { $a->id <=> $b->id } @all_open_invoices;
423 my $output = $self->render(
424 'bank_transactions/add_list',
426 INVOICES => \@all_open_invoices,
429 my %result = ( count => 0, html => $output );
431 $self->render(\to_json(\%result), { type => 'json', process => 0 });
434 sub action_ajax_accept_invoices {
437 my @selected_invoices;
438 foreach my $invoice_id (@{ $::form->{invoice_id} || [] }) {
439 my $invoice_object = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
440 push @selected_invoices, $invoice_object;
444 'bank_transactions/invoices',
446 INVOICES => \@selected_invoices,
447 bt_id => $::form->{bt_id},
454 return 0 if !$::form->{invoice_ids};
456 my %invoice_hash = %{ delete $::form->{invoice_ids} }; # each key (the bt line with a bt_id) contains an array of invoice_ids
458 # e.g. three partial payments with bt_ids 54, 55 and 56 for invoice with id 74:
471 # or if the payment with bt_id 44 is used to pay invoices with ids 50, 51 and 52
473 # '44' => [ '50', '51', 52' ]
476 $::form->{invoice_skontos} ||= {}; # hash of arrays containing the payment types, could be empty
478 # a bank_transaction may be assigned to several invoices, i.e. a customer
479 # might pay several open invoices with one transaction
485 if ( $::form->{proposal_ids} ) {
486 foreach (@{ $::form->{proposal_ids} }) {
487 my $bank_transaction_id = $_;
488 my $invoice_ids = $invoice_hash{$_};
489 push @{ $self->problems }, $self->save_single_bank_transaction(
490 bank_transaction_id => $bank_transaction_id,
491 invoice_ids => $invoice_ids,
492 sources => ($::form->{sources} // {})->{$_},
493 memos => ($::form->{memos} // {})->{$_},
495 $count += scalar( @{$invoice_ids} );
498 while ( my ($bank_transaction_id, $invoice_ids) = each(%invoice_hash) ) {
499 push @{ $self->problems }, $self->save_single_bank_transaction(
500 bank_transaction_id => $bank_transaction_id,
501 invoice_ids => $invoice_ids,
502 sources => [ map { $::form->{"sources_${bank_transaction_id}_${_}"} } @{ $invoice_ids } ],
503 memos => [ map { $::form->{"memos_${bank_transaction_id}_${_}"} } @{ $invoice_ids } ],
505 $count += scalar( @{$invoice_ids} );
508 my $max_count = $count;
509 foreach (@{ $self->problems }) {
510 $count-- if $_->{result} eq 'error';
512 return ($count, $max_count);
515 sub action_save_invoices {
517 my ($success_count, $max_count) = $self->save_invoices();
519 if ($success_count == $max_count) {
520 flash('ok', t8('#1 invoice(s) saved.', $success_count));
522 flash('error', t8('At least #1 invoice(s) not saved', $max_count - $success_count));
525 $self->action_list();
528 sub action_save_proposals {
531 if ( $::form->{proposal_ids} ) {
532 my $propcount = scalar(@{ $::form->{proposal_ids} });
533 if ( $propcount > 0 ) {
534 my $count = $self->save_invoices();
536 flash('ok', t8('#1 proposal(s) with #2 invoice(s) saved.', $propcount, $count));
539 $self->action_list();
543 sub save_single_bank_transaction {
544 my ($self, %params) = @_;
548 bank_transaction => SL::DB::Manager::BankTransaction->find_by(id => $params{bank_transaction_id}),
552 if (!$data{bank_transaction}) {
556 message => $::locale->text('The ID #1 is not a valid database ID.', $data{bank_transaction_id}),
563 my $bt_id = $data{bank_transaction_id};
564 my $bank_transaction = $data{bank_transaction};
565 my $sign = $bank_transaction->amount < 0 ? -1 : 1;
566 my $amount_of_transaction = $sign * $bank_transaction->amount;
567 my $payment_received = $bank_transaction->amount > 0;
568 my $payment_sent = $bank_transaction->amount < 0;
571 foreach my $invoice_id (@{ $params{invoice_ids} }) {
572 my $invoice = SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
577 message => $::locale->text("The ID #1 is not a valid database ID.", $invoice_id),
580 push @{ $data{invoices} }, $invoice;
583 if ( $payment_received
584 && any { ( $_->is_sales && ($_->amount < 0))
585 || (!$_->is_sales && ($_->amount > 0))
586 } @{ $data{invoices} }) {
590 message => $::locale->text("Received payments can only be posted for sales invoices and purchase credit notes."),
595 && any { ( $_->is_sales && ($_->amount > 0))
596 || (!$_->is_sales && ($_->amount < 0) && ($_->invoice_type eq 'purchase_invoice'))
597 } @{ $data{invoices} }) {
601 message => $::locale->text("Sent payments can only be posted for purchase invoices and sales credit notes."),
605 my $max_invoices = scalar(@{ $data{invoices} });
608 foreach my $invoice (@{ $data{invoices} }) {
609 my $source = ($data{sources} // [])->[$n_invoices];
610 my $memo = ($data{memos} // [])->[$n_invoices];
614 # Check if bank_transaction already has a link to the invoice, may only be linked once per invoice
615 # This might be caused by the user reloading a page and resending the form
616 if (_existing_record_link($bank_transaction, $invoice)) {
620 message => $::locale->text("Bank transaction with id #1 has already been linked to #2.", $bank_transaction->id, $invoice->displayable_name),
624 if (!$amount_of_transaction && $invoice->open_amount) {
628 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."),
633 if ( defined $::form->{invoice_skontos}->{"$bt_id"} ) {
634 $payment_type = shift(@{ $::form->{invoice_skontos}->{"$bt_id"} });
636 $payment_type = 'without_skonto';
640 # pay invoice or go to the next bank transaction if the amount is not sufficiently high
641 if ($invoice->open_amount <= $amount_of_transaction && $n_invoices < $max_invoices) {
642 my $open_amount = ($payment_type eq 'with_skonto_pt'?$invoice->amount_less_skonto:$invoice->open_amount);
643 # first calculate new bank transaction amount ...
644 if ($invoice->is_sales) {
645 $amount_of_transaction -= $sign * $open_amount;
646 $bank_transaction->invoice_amount($bank_transaction->invoice_amount + $open_amount);
648 $amount_of_transaction += $sign * $open_amount;
649 $bank_transaction->invoice_amount($bank_transaction->invoice_amount - $open_amount);
651 # ... and then pay the invoice
652 $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
653 trans_id => $invoice->id,
654 amount => $open_amount,
655 payment_type => $payment_type,
658 transdate => $bank_transaction->transdate->to_kivitendo);
660 # use the whole amount of the bank transaction for the invoice, overpay the invoice if necessary
662 # this catches credit_notes and negative sales invoices
663 if ( $invoice->is_sales && $invoice->amount < 0 ) {
664 # $invoice->open_amount is negative for credit_notes
665 # $bank_transaction->amount is negative for outgoing transactions
666 # so $amount_of_transaction is negative but needs positive
667 $amount_of_transaction *= -1;
669 } elsif (!$invoice->is_sales && $invoice->invoice_type =~ m/ap_transaction|purchase_invoice/) {
670 # $invoice->open_amount may be negative for ap_transaction but may be positiv for negativ ap_transaction
671 # if $invoice->open_amount is negative $bank_transaction->amount is positve
672 # if $invoice->open_amount is positive $bank_transaction->amount is negative
673 # but amount of transaction is for both positive
674 $amount_of_transaction *= -1 if $invoice->open_amount == - $amount_of_transaction;
677 my $overpaid_amount = $amount_of_transaction - $invoice->open_amount;
678 $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
679 trans_id => $invoice->id,
680 amount => $amount_of_transaction,
681 payment_type => $payment_type,
684 transdate => $bank_transaction->transdate->to_kivitendo);
685 $bank_transaction->invoice_amount($bank_transaction->amount);
686 $amount_of_transaction = 0;
688 if ($overpaid_amount >= 0.01) {
692 message => $::locale->text('Invoice #1 was overpaid by #2.', $invoice->invnumber, $::form->format_amount(\%::myconfig, $overpaid_amount, 2)),
696 # Record a record link from the bank transaction to the invoice
698 from_table => 'bank_transactions',
700 to_table => $invoice->is_sales ? 'ar' : 'ap',
701 to_id => $invoice->id,
704 SL::DB::RecordLink->new(@props)->save;
706 # "close" a sepa_export_item if it exists
707 # code duplicated in action_save_proposals!
708 # currently only works, if there is only exactly one open sepa_export_item
709 if ( my $seis = $invoice->find_sepa_export_items({ executed => 0 }) ) {
710 if ( scalar @$seis == 1 ) {
711 # moved the execution and the check for sepa_export into a method,
712 # this isn't part of a transaction, though
713 $seis->[0]->set_executed if $invoice->id == $seis->[0]->arap_id;
718 $bank_transaction->save;
720 # 'undef' means 'no error' here.
725 my $rez = $data{bank_transaction}->db->with_transaction(sub {
727 $error = $worker->();
738 # Rollback Fehler nicht weiterreichen
742 return grep { $_ } ($error, @warnings);
750 $::auth->assert('bank_transaction');
757 sub make_filter_summary {
760 my $filter = $::form->{filter} || {};
764 [ $filter->{"transdate:date::ge"}, $::locale->text('Transdate') . " " . $::locale->text('From Date') ],
765 [ $filter->{"transdate:date::le"}, $::locale->text('Transdate') . " " . $::locale->text('To Date') ],
766 [ $filter->{"valutadate:date::ge"}, $::locale->text('Valutadate') . " " . $::locale->text('From Date') ],
767 [ $filter->{"valutadate:date::le"}, $::locale->text('Valutadate') . " " . $::locale->text('To Date') ],
768 [ $filter->{"amount:number"}, $::locale->text('Amount') ],
769 [ $filter->{"bank_account_id:integer"}, $::locale->text('Local bank account') ],
773 push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
776 $self->{filter_summary} = join ', ', @filter_strings;
782 my $callback = $self->models->get_callback;
784 my $report = SL::ReportGenerator->new(\%::myconfig, $::form);
785 $self->{report} = $report;
787 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);
788 my @sortable = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount purpose local_account_number local_bank_code);
791 transdate => { sub => sub { $_[0]->transdate_as_date } },
792 valutadate => { sub => sub { $_[0]->valutadate_as_date } },
794 remote_account_number => { },
795 remote_bank_code => { },
796 amount => { sub => sub { $_[0]->amount_as_number },
798 invoice_amount => { sub => sub { $_[0]->invoice_amount_as_number },
800 invoices => { sub => sub { $_[0]->linked_invoices } },
801 currency => { sub => sub { $_[0]->currency->name } },
803 local_account_number => { sub => sub { $_[0]->local_bank_account->account_number } },
804 local_bank_code => { sub => sub { $_[0]->local_bank_account->bank_code } },
805 local_bank_name => { sub => sub { $_[0]->local_bank_account->name } },
809 map { $column_defs{$_}->{text} ||= $::locale->text( $self->models->get_sort_spec->{$_}->{title} ) } keys %column_defs;
811 $report->set_options(
812 std_column_visibility => 1,
813 controller_class => 'BankTransaction',
814 output_format => 'HTML',
815 top_info_text => $::locale->text('Bank transactions'),
816 title => $::locale->text('Bank transactions'),
817 allow_pdf_export => 1,
818 allow_csv_export => 1,
820 $report->set_columns(%column_defs);
821 $report->set_column_order(@columns);
822 $report->set_export_options(qw(list_all filter));
823 $report->set_options_from_form;
824 $self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
825 $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
827 my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted();
829 $report->set_options(
830 raw_top_info_text => $self->render('bank_transactions/report_top', { output => 0 }, BANK_ACCOUNTS => $bank_accounts),
831 raw_bottom_info_text => $self->render('bank_transactions/report_bottom', { output => 0 }),
835 sub _existing_record_link {
836 my ($bt, $invoice) = @_;
838 # check whether a record link from banktransaction $bt already exists to
839 # invoice $invoice, returns 1 if that is the case
841 die unless $bt->isa("SL::DB::BankTransaction") && ( $invoice->isa("SL::DB::Invoice") || $invoice->isa("SL::DB::PurchaseInvoice") );
843 my $linked_record_to_table = $invoice->is_sales ? 'Invoice' : 'PurchaseInvoice';
844 my $linked_records = $bt->linked_records( direction => 'to', to => $linked_record_to_table, query => [ id => $invoice->id ] );
846 return @$linked_records ? 1 : 0;
849 sub init_problems { [] }
854 SL::Controller::Helper::GetModels->new(
859 dir => 0, # 1 = ASC, 0 = DESC : default sort is newest at top
861 transdate => t8('Transdate'),
862 remote_name => t8('Remote name'),
863 amount => t8('Amount'),
864 invoice_amount => t8('Assigned'),
865 invoices => t8('Linked invoices'),
866 valutadate => t8('Valutadate'),
867 remote_account_number => t8('Remote account number'),
868 remote_bank_code => t8('Remote bank code'),
869 currency => t8('Currency'),
870 purpose => t8('Purpose'),
871 local_account_number => t8('Local account number'),
872 local_bank_code => t8('Local bank code'),
873 local_bank_name => t8('Bank account'),
875 with_objects => [ 'local_bank_account', 'currency' ],
879 sub load_ap_record_template_url {
880 my ($self, $template) = @_;
882 return $self->url_for(
883 controller => 'ap.pl',
884 action => 'load_record_template',
886 'form_defaults.amount_1' => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
887 'form_defaults.transdate' => $self->transaction->transdate_as_date,
888 'form_defaults.duedate' => $self->transaction->transdate_as_date,
889 'form_defaults.no_payment_bookings' => 1,
890 'form_defaults.paid_1_suggestion' => $::form->format_amount(\%::myconfig, -1 * $self->transaction->amount, 2),
891 'form_defaults.AP_paid_1_suggestion' => $self->transaction->local_bank_account->chart->accno,
892 'form_defaults.callback' => $self->callback,
896 sub load_gl_record_template_url {
897 my ($self, $template) = @_;
899 return $self->url_for(
900 controller => 'gl.pl',
901 action => 'load_record_template',
903 'form_defaults.amount_1' => abs($self->transaction->amount), # always positive
904 'form_defaults.transdate' => $self->transaction->transdate_as_date,
905 'form_defaults.callback' => $self->callback,
909 sub setup_search_action_bar {
910 my ($self, %params) = @_;
912 for my $bar ($::request->layout->get('actionbar')) {
916 submit => [ '#search_form', { action => 'BankTransaction/list' } ],
917 accesskey => 'enter',
923 sub setup_list_all_action_bar {
924 my ($self, %params) = @_;
926 for my $bar ($::request->layout->get('actionbar')) {
930 submit => [ '#filter_form', { action => 'BankTransaction/list_all' } ],
931 accesskey => 'enter',
946 SL::Controller::BankTransaction - Posting payments to invoices from
947 bank transactions imported earlier
953 =item C<save_single_bank_transaction %params>
955 Takes a bank transaction ID (as parameter C<bank_transaction_id> and
956 tries to post its amount to a certain number of invoices (parameter
957 C<invoice_ids>, an array ref of database IDs to purchase or sales
960 The whole function is wrapped in a database transaction. If an
961 exception occurs the bank transaction is not posted at all. The same
962 is true if the code detects an error during the execution, e.g. a bank
963 transaction that's already been posted earlier. In both cases the
964 database transaction will be rolled back.
966 If warnings but not errors occur the database transaction is still
969 The return value is an error object or C<undef> if the function
970 succeeded. The calling function will collect all warnings and errors
971 and display them in a nicely formatted table if any occurred.
973 An error object is a hash reference containing the following members:
977 =item * C<result> — can be either C<warning> or C<error>. Warnings are
978 displayed slightly different than errors.
980 =item * C<message> — a human-readable message included in the list of
981 errors meant as the description of why the problem happened
983 =item * C<bank_transaction_id>, C<invoice_ids> — the same parameters
984 that the function was called with
986 =item * C<bank_transaction> — the database object
987 (C<SL::DB::BankTransaction>) corresponding to C<bank_transaction_id>
989 =item * C<invoices> — an array ref of the database objects (either
990 C<SL::DB::Invoice> or C<SL::DB::PurchaseInvoice>) corresponding to
999 Niclas Zimmermann E<lt>niclas@kivitendo-premium.deE<gt>,
1000 Geoffrey Richardson E<lt>information@richardson-bueren.deE<gt>