1 package SL::Controller::Reconciliation;
5 use parent qw(SL::Controller::Base);
7 use SL::Locale::String;
9 use SL::Controller::Helper::ParseFilter;
10 use SL::Helper::Flash;
12 use SL::DB::BankTransaction;
13 use SL::DB::Manager::BankAccount;
14 use SL::DB::AccTransaction;
15 use SL::DB::ReconciliationLink;
16 use List::Util qw(sum);
18 use Rose::Object::MakeMethods::Generic (
19 'scalar --get_set_init' => [ qw(cleared BANK_ACCOUNTS) ],
22 __PACKAGE__->run_before('check_auth');
23 __PACKAGE__->run_before('_bank_account');
32 $self->setup_search_action_bar;
33 $self->render('reconciliation/search');
36 sub action_reconciliation {
39 $self->_get_proposals;
41 $self->_get_linked_transactions;
45 $self->setup_reconciliation_action_bar;
46 $self->render('reconciliation/form',
47 ui_tab => scalar(@{$self->{PROPOSALS}}) > 0?1:0,
48 title => t8('Reconciliation'));
51 sub action_load_overview {
54 $self->_get_proposals;
56 $self->_get_linked_transactions;
60 my $output = $self->render('reconciliation/tabs/overview', { output => 0 });
61 my %result = ( html => $output );
63 $self->render(\to_json(\%result), { type => 'json', process => 0 });
66 sub action_filter_overview {
69 $self->_get_linked_transactions;
72 my $output = $self->render('reconciliation/_linked_transactions', { output => 0 });
73 my %result = ( html => $output,
74 absolut_bt_balance => $::form->format_amount(\%::myconfig, $self->{absolut_bt_balance}, 2),
75 absolut_bb_balance => $::form->format_amount(\%::myconfig, -1 * $self->{absolut_bb_balance}, 2),
76 bt_balance => $::form->format_amount(\%::myconfig, $self->{bt_balance}, 2),
77 bb_balance => $::form->format_amount(\%::myconfig, -1 * $self->{bb_balance}, 2)
80 $self->render(\to_json(\%result), { type => 'json', process => 0 });
83 sub action_update_reconciliation_table {
86 my @errors = $self->_get_elements_and_validate();
88 my $output = $self->render('reconciliation/assigning_table', { output => 0 },
89 bt_sum => $::form->format_amount(\%::myconfig, $self->{bt_sum}, 2),
90 bb_sum => $::form->format_amount(\%::myconfig, -1 * $self->{bb_sum}, 2),
94 my %result = ( html => $output );
96 $self->render(\to_json(\%result), { type => 'json', process => 0 });
99 sub action_reconcile {
103 my @errors = $self->_get_elements_and_validate;
106 unshift(@errors, (t8('Could not reconcile chosen elements!')));
107 flash('error', @errors);
108 $self->action_reconciliation;
114 $self->action_reconciliation;
117 sub action_delete_reconciliation {
120 my $rec_links = SL::DB::Manager::ReconciliationLink->get_all(where => [ rec_group => $::form->{rec_group} ]);
122 foreach my $rec_link (@{ $rec_links }) {
123 my $bank_transaction = SL::DB::Manager::BankTransaction->find_by( id => $rec_link->bank_transaction_id );
124 my $acc_transaction = SL::DB::Manager::AccTransaction ->find_by( acc_trans_id => $rec_link->acc_trans_id );
126 $bank_transaction->cleared('0');
127 $acc_transaction->cleared('0');
129 $bank_transaction->save;
130 $acc_transaction->save;
135 $self->_get_linked_transactions;
136 $self->_get_balances;
138 my $output = $self->render('reconciliation/_linked_transactions', { output => 0 });
139 my %result = ( html => $output,
140 absolut_bt_balance => $::form->format_amount(\%::myconfig, $self ->{absolut_bt_balance}, 2),
141 absolut_bb_balance => $::form->format_amount(\%::myconfig, -1 * $self ->{absolut_bb_balance}, 2),
142 bt_balance => $::form->format_amount(\%::myconfig, $self ->{bt_balance}, 2),
143 bb_balance => $::form->format_amount(\%::myconfig, -1 * $self ->{bb_balance}, 2)
146 $self->render(\to_json(\%result), { type => 'json', process => 0 });
149 sub action_load_proposals {
152 $self->_get_proposals;
154 my $output = $self->render('reconciliation/tabs/automatic', { output => 0 });
155 my %result = ( html => $output );
157 $self->render(\to_json(\%result), { type => 'json', process => 0 });
160 sub action_filter_proposals {
163 $self->_get_balances;
164 $self->_get_proposals;
166 my $output = $self->render('reconciliation/proposals', { output => 0 });
167 my %result = ( html => $output,
168 absolut_bt_balance => $::form->format_amount(\%::myconfig, $self ->{absolut_bt_balance}, 2),
169 absolut_bb_balance => $::form->format_amount(\%::myconfig, -1 * $self ->{absolut_bb_balance}, 2),
170 bt_balance => $::form->format_amount(\%::myconfig, $self ->{bt_balance}, 2),
171 bb_balance => $::form->format_amount(\%::myconfig, -1 * $self ->{bb_balance}, 2)
174 $self->render(\to_json(\%result), { type => 'json', process => 0 });
177 sub action_reconcile_proposals {
182 foreach my $bt_id ( @{ $::form->{bt_ids} }) {
183 my $rec_group = SL::DB::Manager::ReconciliationLink->get_new_rec_group();
184 my $bank_transaction = SL::DB::Manager::BankTransaction->find_by(id => $bt_id);
185 $bank_transaction->cleared('1');
186 if ( $bank_transaction->isa('SL::DB::BankTransaction') ) {
187 $bank_transaction->invoice_amount($bank_transaction->amount);
189 $bank_transaction->save;
190 foreach my $acc_trans_id (@{ $::form->{proposal_list}->{$bt_id}->{BB} }) {
191 SL::DB::ReconciliationLink->new(
192 rec_group => $rec_group,
193 bank_transaction_id => $bt_id,
194 acc_trans_id => $acc_trans_id
196 my $acc_trans = SL::DB::Manager::AccTransaction->find_by(acc_trans_id => $acc_trans_id);
197 $acc_trans->cleared('1');
203 flash('ok', t8('#1 proposal(s) saved.', $counter));
205 $self->action_reconciliation;
213 $::auth->assert('bank_transaction');
218 $self->{bank_account} = SL::DB::Manager::BankAccount->find_by(id => $::form->{filter}->{"local_bank_account_id:number"});
228 # reconciliation suggestion is based on:
229 # * record_link exists (was paid by bank transaction)
230 # or acc_trans entry exists where
231 # * amount is exactly the same
233 # * IBAN or account number have to match exactly (cv details, no spaces)
235 # * there is exactly one match for all conditions
237 $self->_filter_to_where;
239 my $bank_transactions = SL::DB::Manager::BankTransaction->get_all(where => [ @{ $self->{bt_where} }, cleared => '0' ]);
245 foreach my $bt (@{ $bank_transactions }) {
246 $check_sum = $bt->amount;
248 $proposal->{BT} = $bt;
249 $proposal->{BB} = [];
251 # first of all check if any of the bank_transactions are already linked (i.e. were paid via bank transactions)
252 my $linked_records = SL::DB::Manager::RecordLink->get_all(where => [ from_table => 'bank_transactions', from_id => $bt->id ]);
253 foreach my $linked_record (@{ $linked_records }) {
255 if ($linked_record->to_table eq 'ar') {
256 $invoice = SL::DB::Manager::Invoice->find_by(id => $linked_record->to_id);
258 my $payments = SL::DB::Manager::AccTransaction->get_all(where => [ trans_id => $invoice->id, chart_link => { like => '%AR_paid%' }, transdate => $bt->transdate ]);
259 foreach my $payment (@{ $payments }) {
260 $check_sum += $payment->amount;
261 push @{ $proposal->{BB} }, $payment;
264 if ($linked_record->to_table eq 'ap') {
265 $invoice = SL::DB::Manager::PurchaseInvoice->find_by(id => $linked_record->to_id);
267 my $payments = SL::DB::Manager::AccTransaction->get_all(where => [ trans_id => $invoice->id, chart_link => { like => '%AP_paid%' }, transdate => $bt->transdate ]);
268 foreach my $payment (@{ $payments }) {
269 $check_sum += $payment->amount;
270 push @{ $proposal->{BB} }, $payment;
275 #add proposal if something in acc_trans was found
276 #otherwise try to find another entry in acc_trans and add it
277 # for linked_records we allow a slight difference / imprecision, for acc_trans search we don't
278 if (scalar @{ $proposal->{BB} } and abs($check_sum) <= 0.01 ) {
279 push @proposals, $proposal;
280 } elsif (!scalar @{ $proposal->{BB} }) {
281 # use account_number and iban for matching remote account number
282 # don't suggest gl stornos (ar and ap stornos shouldn't have any payments)
284 my @account_number_match = (
285 ( 'ar.customer.iban' => $bt->remote_account_number ),
286 ( 'ar.customer.account_number' => $bt->remote_account_number ),
287 ( 'ap.vendor.iban' => $bt->remote_account_number ),
288 ( 'ap.vendor.account_number' => $bt->remote_account_number ),
289 ( 'gl.storno' => '0' ),
292 my $acc_transactions = SL::DB::Manager::AccTransaction->get_all(where => [ @{ $self->{bb_where} },
293 amount => -1 * $bt->amount,
295 'transdate' => $bt->transdate,
296 or => [ @account_number_match ]
298 with_objects => [ 'ar', 'ap', 'ar.customer', 'ap.vendor', 'gl' ]);
299 if (scalar @{ $acc_transactions } == 1) {
300 push @{ $proposal->{BB} }, @{ $acc_transactions }[0];
301 push @proposals, $proposal;
306 $self->{PROPOSALS} = \@proposals;
309 sub _get_elements_and_validate {
314 if ( not defined $::form->{bt_ids} ) {
315 push @errors, t8('No bank account chosen!');
318 if ( not defined $::form->{bb_ids} ) {
319 push @errors, t8('No transaction on chart bank chosen!');
323 if (scalar @{ $::form->{bt_ids} } > 1 and scalar @{ $::form->{bb_ids} } > 1) {
324 push @errors, t8('No 1:n or n:1 relation');
329 my ($bt_sum, $bb_sum) = (0,0);
331 foreach my $bt_id (@{ $::form->{bt_ids} }) {
332 my $bt = SL::DB::Manager::BankTransaction->find_by(id => $bt_id);
334 $bt_sum += $bt->amount;
338 foreach my $bb_id (@{ $::form->{bb_ids} }) {
339 my $bb = SL::DB::Manager::AccTransaction->find_by(acc_trans_id => $bb_id);
341 $bb->{id} = $bb->acc_trans_id;
342 $bb_sum += $bb->amount;
346 if ($::form->round_amount($bt_sum + $bb_sum, 2) != 0) {
347 push @errors, t8('Out of balance!'), t8('Sum of bank #1 and sum of bookings #2',$bt_sum, $bb_sum);
350 $self->{ELEMENTS} = \@elements;
351 $self->{bt_sum} = $bt_sum;
352 $self->{bb_sum} = $bb_sum;
360 # 1. step: set AccTrans and BankTransactions to 'cleared'
361 foreach my $element (@{ $self->{ELEMENTS} }) {
362 $element->cleared('1');
363 # veto either invoice_amount is fully assigned or not! No state tricks in later workflow!
364 # invoice_amount should be a distinct sign, that some bookings were really made from a bank transaction
365 # $element->invoice_amount($element->amount) if $element->isa('SL::DB::BankTransaction');
369 # 2. step: insert entry in reconciliation_links
370 my $rec_group = SL::DB::Manager::ReconciliationLink->get_new_rec_group();
371 #There is either a 1:n relation or a n:1 relation
372 if (scalar @{ $::form->{bt_ids} } == 1) {
373 my $bt_id = @{ $::form->{bt_ids} }[0];
374 foreach my $bb_id (@{ $::form->{bb_ids} }) {
375 my $rec_link = SL::DB::ReconciliationLink->new(bank_transaction_id => $bt_id,
376 acc_trans_id => $bb_id,
377 rec_group => $rec_group);
381 my $bb_id = @{ $::form->{bb_ids} }[0];
382 foreach my $bt_id (@{ $::form->{bt_ids} }) {
383 my $rec_link = SL::DB::ReconciliationLink->new(bank_transaction_id => $bt_id,
384 acc_trans_id => $bb_id,
385 rec_group => $rec_group);
391 sub _filter_to_where {
394 my %parse_filter = parse_filter($::form->{filter});
395 my %filter = @{ $parse_filter{query} };
397 my (@rl_where, @bt_where, @bb_where);
398 @rl_where = ('bank_transaction.local_bank_account_id' => $filter{local_bank_account_id});
399 @bt_where = (local_bank_account_id => $filter{local_bank_account_id});
400 @bb_where = (chart_id => $self->{bank_account}->chart_id);
402 if ($filter{fromdate} and $filter{todate}) {
404 push @rl_where, (or => [ and => [ 'acc_trans.transdate' => $filter{fromdate},
405 'acc_trans.transdate' => $filter{todate} ],
406 and => [ 'bank_transaction.transdate' => $filter{fromdate},
407 'bank_transaction.transdate' => $filter{todate} ] ] );
409 push @bt_where, (transdate => $filter{todate} );
410 push @bt_where, (transdate => $filter{fromdate} );
411 push @bb_where, (transdate => $filter{todate} );
412 push @bb_where, (transdate => $filter{fromdate} );
415 if ( $self->{bank_account}->reconciliation_starting_date ) {
416 push @bt_where, (transdate => { ge => $self->{bank_account}->reconciliation_starting_date });
417 push @bb_where, (transdate => { ge => $self->{bank_account}->reconciliation_starting_date });
420 # don't try to reconcile opening and closing balance transactions
421 push @bb_where, ('acc_trans.ob_transaction' => 0);
422 push @bb_where, ('acc_trans.cb_transaction' => 0);
424 if ($filter{fromdate} and not $filter{todate}) {
425 push @rl_where, (or => [ 'acc_trans.transdate' => $filter{fromdate},
426 'bank_transaction.transdate' => $filter{fromdate} ] );
427 push @bt_where, (transdate => $filter{fromdate} );
428 push @bb_where, (transdate => $filter{fromdate} );
431 if ($filter{todate} and not $filter{fromdate}) {
432 push @rl_where, ( or => [ 'acc_trans.transdate' => $filter{todate} ,
433 'bank_transaction.transdate' => $filter{todate} ] );
434 push @bt_where, (transdate => $filter{todate} );
435 push @bb_where, (transdate => $filter{todate} );
438 if ($filter{cleared}) {
439 $filter{cleared} = $filter{cleared} eq 'FALSE' ? '0' : '1';
440 push @rl_where, ('acc_trans.cleared' => $filter{cleared} );
442 push @bt_where, (cleared => $filter{cleared} );
443 push @bb_where, (cleared => $filter{cleared} );
446 $self->{rl_where} = \@rl_where;
447 $self->{bt_where} = \@bt_where;
448 $self->{bb_where} = \@bb_where;
451 sub _get_linked_transactions {
454 $self->_filter_to_where;
456 my (@where, @bt_where, @bb_where);
457 # don't try to reconcile opening and closing balances
458 # instead use an offset in configuration
460 @where = (@{ $self->{rl_where} });
461 @bt_where = (@{ $self->{bt_where} }, cleared => '0');
462 @bb_where = (@{ $self->{bb_where} }, cleared => '0');
466 my $reconciliation_groups = SL::DB::Manager::ReconciliationLink->get_all(distinct => 1,
467 select => ['rec_group'],
469 with_objects => ['bank_transaction', 'acc_trans']);
471 my $fromdate = $::locale->parse_date_to_object($::form->{filter}->{fromdate_date__ge});
472 my $todate = $::locale->parse_date_to_object($::form->{filter}->{todate_date__le});
474 foreach my $rec_group (@{ $reconciliation_groups }) {
475 my $linked_transactions = SL::DB::Manager::ReconciliationLink->get_all(where => [rec_group => $rec_group->rec_group], with_objects => ['bank_transaction', 'acc_trans']);
477 my $first_transaction = shift @{ $linked_transactions };
478 my $first_bt = $first_transaction->bank_transaction;
479 my $first_bb = $first_transaction->acc_trans;
481 if (defined $fromdate) {
482 $first_bt->{class} = 'out_of_balance' if ( $first_bt->transdate lt $fromdate );
483 $first_bb->{class} = 'out_of_balance' if ( $first_bb->transdate lt $fromdate );
485 if (defined $todate) {
486 $first_bt->{class} = 'out_of_balance' if ( $first_bt->transdate gt $todate );
487 $first_bb->{class} = 'out_of_balance' if ( $first_bb->transdate gt $todate );
489 $line->{BT} = [ $first_bt ];
490 $line->{BB} = [ $first_bb ];
491 $line->{rec_group} = $first_transaction->rec_group;
492 $line->{type} = 'Link';
494 #add the rest of transaction of this group
495 my ($previous_bt_id, $previous_acc_trans_id) = ($first_transaction->bank_transaction_id, $first_transaction->acc_trans_id);
496 foreach my $linked_transaction (@{ $linked_transactions }) {
497 my $bank_transaction = $linked_transaction->bank_transaction;
498 my $acc_transaction = $linked_transaction->acc_trans;
499 if (defined $fromdate) {
500 $bank_transaction->{class} = 'out_of_balance' if ( $bank_transaction->transdate lt $fromdate );
501 $acc_transaction->{class} = 'out_of_balance' if ( $acc_transaction->transdate lt $fromdate );
503 if (defined $todate) {
504 $bank_transaction->{class} = 'out_of_balance' if ( $bank_transaction->transdate gt $todate );
505 $acc_transaction->{class} = 'out_of_balance' if ( $acc_transaction->transdate gt $todate );
507 if ($bank_transaction->id != $previous_bt_id) {
508 push @{ $line->{BT} }, $bank_transaction;
510 if ($acc_transaction->acc_trans_id != $previous_acc_trans_id) {
511 push @{ $line->{BB} }, $acc_transaction;
517 # add non-cleared bank transactions
518 my $bank_transactions = SL::DB::Manager::BankTransaction->get_all(where => \@bt_where);
519 foreach my $bt (@{ $bank_transactions }) {
521 $line->{BT} = [ $bt ];
522 $line->{type} = 'BT';
523 $line->{id} = $bt->id;
527 # add non-cleared bookings on bank
528 my $bookings_on_bank = SL::DB::Manager::AccTransaction->get_all(where => \@bb_where);
529 foreach my $bb (@{ $bookings_on_bank }) {
530 if ($::form->{filter}->{show_stornos} or !$bb->record->storno) {
532 $line->{BB} = [ $bb ];
533 $line->{type} = 'BB';
534 $line->{id} = $bb->acc_trans_id;
540 @rows = sort sort_by_transdate @rows;
542 $self->{LINKED_TRANSACTIONS} = \@rows;
545 sub sort_by_transdate {
546 if ($a->{BT} and $b->{BT}) {
547 return $a->{BT}[0]->amount <=> $b->{BT}[0]->amount if $a->{BT}[0]->transdate eq $b->{BT}[0]->transdate;
548 return $a->{BT}[0]->transdate cmp $b->{BT}[0]->transdate;
551 return $a->{BT}[0]->amount <=> (-1 * $b->{BB}[0]->amount) if $a->{BT}[0]->transdate eq $b->{BB}[0]->transdate;
552 return $a->{BT}[0]->transdate cmp $b->{BB}[0]->transdate;
555 return (-1 * $a->{BB}[0]->amount) <=> $b->{BT}[0]->amount if $a->{BB}[0]->transdate eq $b->{BT}[0]->transdate;
556 return $a->{BB}[0]->transdate cmp $b->{BT}[0]->transdate;
558 return (-1 * $a->{BB}[0]->amount) <=> (-1 * $b->{BB}[0]->amount) if $a->{BB}[0]->transdate eq $b->{BB}[0]->transdate;
559 return $a->{BB}[0]->transdate cmp $b->{BB}[0]->transdate;
565 $self->_filter_to_where;
567 my (@bt_where, @bb_where);
568 @bt_where = @{ $self->{bt_where} };
569 @bb_where = @{ $self->{bb_where} };
571 my @all_bt_where = (local_bank_account_id => $self->{bank_account}->id);
572 my @all_bb_where = (chart_id => $self->{bank_account}->chart_id);
574 my ($bt_balance, $bb_balance) = (0,0);
575 my ($absolut_bt_balance, $absolut_bb_balance) = (0,0);
577 if ( $self->{bank_account}->reconciliation_starting_date ) {
578 $bt_balance = $self->{bank_account}->reconciliation_starting_balance;
579 $bb_balance = $self->{bank_account}->reconciliation_starting_balance * -1;
580 $absolut_bt_balance = $self->{bank_account}->reconciliation_starting_balance;
581 $absolut_bb_balance = $self->{bank_account}->reconciliation_starting_balance * -1;
583 push @all_bt_where, ( transdate => { gt => $self->{bank_account}->reconciliation_starting_date });
584 push @all_bb_where, ( transdate => { gt => $self->{bank_account}->reconciliation_starting_date });
587 my $bank_transactions = SL::DB::Manager::BankTransaction->get_all(where => \@bt_where );
588 my $payments = SL::DB::Manager::AccTransaction ->get_all(where => \@bb_where );
590 # for absolute balance get all bookings until todate
591 my $todate = $::locale->parse_date_to_object($::form->{filter}->{todate_date__le});
592 my $fromdate = $::locale->parse_date_to_object($::form->{filter}->{fromdate_date__le});
595 push @all_bt_where, (transdate => { le => $todate });
596 push @all_bb_where, (transdate => { le => $todate });
599 my $all_bank_transactions = SL::DB::Manager::BankTransaction->get_all(where => \@all_bt_where);
600 my $all_payments = SL::DB::Manager::AccTransaction ->get_all(where => \@all_bb_where);
602 $bt_balance += sum map { $_->amount } @{ $bank_transactions };
603 $bb_balance += sum map { $_->amount if ($::form->{filter}->{show_stornos} or !$_->record->storno) } @{ $payments };
605 $absolut_bt_balance += sum map { $_->amount } @{ $all_bank_transactions };
606 $absolut_bb_balance += sum map { $_->amount } @{ $all_payments };
609 $self->{bt_balance} = $bt_balance || 0;
610 $self->{bb_balance} = $bb_balance || 0;
611 $self->{absolut_bt_balance} = $absolut_bt_balance || 0;
612 $self->{absolut_bb_balance} = $absolut_bb_balance || 0;
614 $self->{difference} = $bt_balance + $bb_balance;
618 [ { title => t8("all"), value => '' },
619 { title => t8("cleared"), value => 'TRUE' },
620 { title => t8("uncleared"), value => 'FALSE' }, ]
623 sub init_BANK_ACCOUNTS {
624 SL::DB::Manager::BankAccount->get_all_sorted( query => [ obsolete => 0 ] );
627 sub setup_search_action_bar {
628 my ($self, %params) = @_;
630 for my $bar ($::request->layout->get('actionbar')) {
634 submit => [ '#search_form', { action => 'Reconciliation/reconciliation' } ],
635 accesskey => 'enter',
641 sub setup_reconciliation_action_bar {
642 my ($self, %params) = @_;
644 for my $bar ($::request->layout->get('actionbar')) {
648 call => [ 'filter_table' ],
649 accesskey => 'enter',