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::BankAccount;
14 use SL::DB::AccTransaction;
15 use SL::DB::ReconciliationLink;
17 use Rose::Object::MakeMethods::Generic (
18 'scalar --get_set_init' => [ qw(cleared BANK_ACCOUNTS) ],
21 __PACKAGE__->run_before('check_auth');
22 __PACKAGE__->run_before('_bank_account');
31 $self->render('reconciliation/search',
32 label_sub => sub { t8('#1 - Account number #2, bank code #3, #4',
35 $_[0]->account_number,
39 sub action_reconciliation {
42 $self->_get_linked_transactions;
46 $self->render('reconciliation/form',
47 title => t8('Reconciliation'),
48 label_sub => sub { t8('#1 - Account number #2, bank code #3, #4',
51 $_[0]->account_number,
55 sub action_load_overview {
58 $self->_get_proposals;
60 $self->_get_linked_transactions;
64 my $output = $self->render('reconciliation/tabs/overview', { output => 0 });
65 my %result = ( html => $output );
67 $self->render(\to_json(\%result), { type => 'json', process => 0 });
70 sub action_filter_overview {
73 $self->_get_linked_transactions;
76 my $output = $self->render('reconciliation/_linked_transactions', { output => 0 });
77 my %result = ( html => $output,
78 absolut_bt_balance => $::form->format_amount(\%::myconfig, $self->{absolut_bt_balance}, 2),
79 absolut_bb_balance => $::form->format_amount(\%::myconfig, -1 * $self->{absolut_bb_balance}, 2),
80 bt_balance => $::form->format_amount(\%::myconfig, $self->{bt_balance}, 2),
81 bb_balance => $::form->format_amount(\%::myconfig, -1 * $self->{bb_balance}, 2)
84 $self->render(\to_json(\%result), { type => 'json', process => 0 });
87 sub action_update_reconciliation_table {
90 my @errors = $self->_get_elements_and_validate();
92 my $output = $self->render('reconciliation/assigning_table', { output => 0 },
93 bt_sum => $::form->format_amount(\%::myconfig, $self->{bt_sum}, 2),
94 bb_sum => $::form->format_amount(\%::myconfig, -1 * $self->{bb_sum}, 2),
95 show_button => !@errors
98 my %result = ( html => $output );
100 $self->render(\to_json(\%result), { type => 'json', process => 0 });
103 sub action_reconciliate {
107 my @errors = $self->_get_elements_and_validate;
110 unshift(@errors, (t8('Could not reconciliate chosen elements!')));
111 flash('error', @errors);
112 $self->action_reconciliation;
116 $self->_reconciliate;
118 $self->action_reconciliation;
121 sub action_delete_reconciliation {
124 my $rec_links = SL::DB::Manager::ReconciliationLink->get_all(where => [ rec_group => $::form->{rec_group} ]);
126 foreach my $rec_link (@{ $rec_links }) {
127 my $bank_transaction = SL::DB::Manager::BankTransaction->find_by( id => $rec_link->bank_transaction_id );
128 my $acc_transaction = SL::DB::Manager::AccTransaction ->find_by( acc_trans_id => $rec_link->acc_trans_id );
130 $bank_transaction->cleared('0');
131 $acc_transaction->cleared('0');
133 $bank_transaction->save;
134 $acc_transaction->save;
139 $self->_get_linked_transactions;
140 $self->_get_balances;
142 my $output = $self->render('reconciliation/_linked_transactions', { output => 0 });
143 my %result = ( html => $output,
144 absolut_bt_balance => $::form->format_amount(\%::myconfig, $self->{absolut_bt_balance}, 2),
145 absolut_bb_balance => $::form->format_amount(\%::myconfig, -1 * $self->{absolut_bb_balance}, 2),
146 bt_balance => $::form->format_amount(\%::myconfig, $self->{bt_balance}, 2),
147 bb_balance => $::form->format_amount(\%::myconfig, -1 * $self->{bb_balance}, 2)
150 $self->render(\to_json(\%result), { type => 'json', process => 0 });
153 sub action_load_proposals {
156 $self->_get_proposals;
158 my $output = $self->render('reconciliation/tabs/automatic', { output => 0 });
159 my %result = ( html => $output );
161 $self->render(\to_json(\%result), { type => 'json', process => 0 });
164 sub action_filter_proposals {
167 $self->_get_balances;
168 $self->_get_proposals;
170 my $output = $self->render('reconciliation/proposals', { output => 0 });
171 my %result = ( html => $output,
172 absolut_bt_balance => $::form->format_amount(\%::myconfig, $self->{absolut_bt_balance}, 2),
173 absolut_bb_balance => $::form->format_amount(\%::myconfig, -1 * $self->{absolut_bb_balance}, 2),
174 bt_balance => $::form->format_amount(\%::myconfig, $self->{bt_balance}, 2),
175 bb_balance => $::form->format_amount(\%::myconfig, -1 * $self->{bb_balance}, 2)
178 $self->render(\to_json(\%result), { type => 'json', process => 0 });
181 sub action_reconciliate_proposals {
186 foreach my $bt_id ( @{ $::form->{bt_ids} }) {
187 my $rec_group = SL::DB::Manager::ReconciliationLink->get_new_rec_group();
188 my $bank_transaction = SL::DB::Manager::BankTransaction->find_by(id => $bt_id);
189 $bank_transaction->cleared('1');
190 if ( $bank_transaction->isa('SL::DB::BankTransaction') ) {
191 $bank_transaction->invoice_amount($bank_transaction->amount);
193 $bank_transaction->save;
194 foreach my $acc_trans_id (@{ $::form->{proposal_list}->{$bt_id}->{BB} }) {
195 SL::DB::ReconciliationLink->new(
196 rec_group => $rec_group,
197 bank_transaction_id => $bt_id,
198 acc_trans_id => $acc_trans_id
200 my $acc_trans = SL::DB::Manager::AccTransaction->find_by(acc_trans_id => $acc_trans_id);
201 $acc_trans->cleared('1');
207 flash('ok', t8('#1 proposal(s) saved.', $counter));
209 $self->action_reconciliation;
217 $::auth->assert('bank_transaction');
222 $self->{bank_account} = SL::DB::Manager::BankAccount->find_by(id => $::form->{filter}->{"local_bank_account_id:number"});
232 $self->_filter_to_where;
234 my $bank_transactions = SL::DB::Manager::BankTransaction->get_all(where => [ @{ $self->{bt_where} }, cleared => '0' ]);
240 foreach my $bt (@{ $bank_transactions }) {
241 $check_sum = $bt->amount;
243 $proposal->{BT} = $bt;
244 $proposal->{BB} = [];
246 my $linked_records = SL::DB::Manager::RecordLink->get_all(where => [ from_table => 'bank_transactions', from_id => $bt->id ]);
247 foreach my $linked_record (@{ $linked_records }) {
249 if ($linked_record->to_table eq 'ar') {
250 $invoice = SL::DB::Manager::Invoice->find_by(id => $linked_record->to_id);
252 my $payments = SL::DB::Manager::AccTransaction->get_all(where => [ trans_id => $invoice->id, chart_link => { like => '%AR_paid%' }, transdate => $bt->transdate ]);
253 foreach my $payment (@{ $payments }) {
254 $check_sum += $payment->amount;
255 push @{ $proposal->{BB} }, $payment;
258 if ($linked_record->to_table eq 'ap') {
259 $invoice = SL::DB::Manager::PurchaseInvoice->find_by(id => $linked_record->to_id);
261 my $payments = SL::DB::Manager::AccTransaction->get_all(where => [ trans_id => $invoice->id, chart_link => { like => '%AP_paid%' }, transdate => $bt->transdate ]);
262 foreach my $payment (@{ $payments }) {
263 $check_sum += $payment->amount;
264 push @{ $proposal->{BB} }, $payment;
269 #add proposal if something in acc_trans was found
270 #otherwise try to find another entry in acc_trans and add it
271 if (scalar @{ $proposal->{BB} } and !$check_sum) {
272 push @proposals, $proposal;
273 } elsif (!scalar @{ $proposal->{BB} }) {
274 my $acc_transactions = SL::DB::Manager::AccTransaction->get_all(where => [ @{ $self->{bb_where} },
275 amount => -1 * $bt->amount,
278 and => [ 'ar.customer.account_number' => $bt->remote_account_number,
279 'ar.customer.bank_code' => $bt->remote_bank_code, ],
280 and => [ 'ap.vendor.account_number' => $bt->remote_account_number,
281 'ap.vendor.bank_code' => $bt->remote_bank_code, ],
282 'gl.storno' => '0' ]],
283 with_objects => [ 'ar', 'ap', 'ar.customer', 'ap.vendor', 'gl' ]);
284 if (scalar @{ $acc_transactions } == 1) {
285 push @{ $proposal->{BB} }, @{ $acc_transactions }[0];
286 push @proposals, $proposal;
291 $self->{PROPOSALS} = \@proposals;
294 sub _get_elements_and_validate {
299 if ( not defined $::form->{bt_ids} ) {
300 push @errors, t8('No bank account chosen!');
303 if ( not defined $::form->{bb_ids} ) {
304 push @errors, t8('No transaction on chart bank chosen!');
308 if (scalar @{ $::form->{bt_ids} } > 1 and scalar @{ $::form->{bb_ids} } > 1) {
309 push @errors, t8('No 1:n or n:1 relation');
314 my ($bt_sum, $bb_sum) = (0,0);
316 foreach my $bt_id (@{ $::form->{bt_ids} }) {
317 my $bt = SL::DB::Manager::BankTransaction->find_by(id => $bt_id);
319 $bt_sum += $bt->amount;
323 foreach my $bb_id (@{ $::form->{bb_ids} }) {
324 my $bb = SL::DB::Manager::AccTransaction->find_by(acc_trans_id => $bb_id);
326 $bb->{id} = $bb->acc_trans_id;
327 $bb_sum += $bb->amount;
331 if ($::form->round_amount($bt_sum + $bb_sum) != 0) {
332 push @errors, t8('Out of balance!');
335 $self->{ELEMENTS} = \@elements;
336 $self->{bt_sum} = $bt_sum;
337 $self->{bb_sum} = $bb_sum;
345 #1. Step: Set AccTrans and BankTransactions to 'cleared'
346 foreach my $element (@{ $self->{ELEMENTS} }) {
347 $element->cleared('1');
348 $element->invoice_amount($element->amount) if $element->isa('SL::DB::BankTransaction');
352 #2. Step: Insert entry in reconciliation_links
353 my $rec_group = SL::DB::Manager::ReconciliationLink->get_new_rec_group();
354 #There is either a 1:n relation or a n:1 relation
355 if (scalar @{ $::form->{bt_ids} } == 1) {
356 my $bt_id = @{ $::form->{bt_ids} }[0];
357 foreach my $bb_id (@{ $::form->{bb_ids} }) {
358 my $rec_link = SL::DB::ReconciliationLink->new(bank_transaction_id => $bt_id,
359 acc_trans_id => $bb_id,
360 rec_group => $rec_group);
364 my $bb_id = @{ $::form->{bb_ids} }[0];
365 foreach my $bt_id (@{ $::form->{bt_ids} }) {
366 my $rec_link = SL::DB::ReconciliationLink->new(bank_transaction_id => $bt_id,
367 acc_trans_id => $bb_id,
368 rec_group => $rec_group);
374 sub _filter_to_where {
377 my %parse_filter = parse_filter($::form->{filter});
378 my %filter = @{ $parse_filter{query} };
380 my (@rl_where, @bt_where, @bb_where);
381 @rl_where = ('bank_transaction.local_bank_account_id' => $filter{local_bank_account_id});
382 @bt_where = (local_bank_account_id => $filter{local_bank_account_id});
383 @bb_where = (chart_id => $self->{bank_account}->chart_id);
385 if ($filter{fromdate} and $filter{todate}) {
387 push @rl_where, (or => [ and => [ 'acc_tran.transdate' => $filter{fromdate},
388 'acc_tran.transdate' => $filter{todate} ],
389 and => [ 'bank_transaction.transdate' => $filter{fromdate},
390 'bank_transaction.transdate' => $filter{todate} ] ] );
392 push @bt_where, (transdate => $filter{todate} );
393 push @bt_where, (transdate => $filter{fromdate} );
394 push @bb_where, (transdate => $filter{todate} );
395 push @bb_where, (transdate => $filter{fromdate} );
398 if ($filter{fromdate} and not $filter{todate}) {
399 push @rl_where, (or => [ 'acc_tran.transdate' => $filter{fromdate},
400 'bank_transaction.transdate' => $filter{fromdate} ] );
401 push @bt_where, (transdate => $filter{fromdate} );
402 push @bb_where, (transdate => $filter{fromdate} );
405 if ($filter{todate} and not $filter{fromdate}) {
406 push @rl_where, ( or => [ 'acc_tran.transdate' => $filter{todate} ,
407 'bank_transaction.transdate' => $filter{todate} ] );
408 push @bt_where, (transdate => $filter{todate} );
409 push @bb_where, (transdate => $filter{todate} );
412 if ($filter{cleared}) {
413 $filter{cleared} = $filter{cleared} eq 'FALSE' ? '0' : '1';
414 push @rl_where, ('acc_tran.cleared' => $filter{cleared} );
416 push @bt_where, (cleared => $filter{cleared} );
417 push @bb_where, (cleared => $filter{cleared} );
420 $self->{rl_where} = \@rl_where;
421 $self->{bt_where} = \@bt_where;
422 $self->{bb_where} = \@bb_where;
425 sub _get_linked_transactions {
428 $self->_filter_to_where;
430 my (@where, @bt_where, @bb_where);
431 @where = (@{ $self->{rl_where} });
432 @bt_where = (@{ $self->{bt_where} }, cleared => '0');
433 @bb_where = (@{ $self->{bb_where} }, cleared => '0');
437 my $reconciliation_groups = SL::DB::Manager::ReconciliationLink->get_all(distinct => 1,
438 select => ['rec_group'],
440 with_objects => ['bank_transaction', 'acc_tran']);
442 my $fromdate = $::locale->parse_date_to_object(\%::myconfig, $::form->{filter}->{fromdate_date__ge});
443 my $todate = $::locale->parse_date_to_object(\%::myconfig, $::form->{filter}->{todate_date__le});
445 foreach my $rec_group (@{ $reconciliation_groups }) {
446 my $linked_transactions = SL::DB::Manager::ReconciliationLink->get_all(where => [rec_group => $rec_group->rec_group], with_objects => ['bank_transaction', 'acc_tran']);
448 my $first_transaction = shift @{ $linked_transactions };
449 my $first_bt = $first_transaction->bank_transaction;
450 my $first_bb = $first_transaction->acc_tran;
452 if (defined $fromdate) {
453 $first_bt->{class} = 'out_of_balance' if ( $first_bt->transdate lt $fromdate );
454 $first_bb->{class} = 'out_of_balance' if ( $first_bb->transdate lt $fromdate );
456 if (defined $todate) {
457 $first_bt->{class} = 'out_of_balance' if ( $first_bt->transdate gt $todate );
458 $first_bb->{class} = 'out_of_balance' if ( $first_bb->transdate gt $todate );
460 $line->{BT} = [ $first_bt ];
461 $line->{BB} = [ $first_bb ];
462 $line->{rec_group} = $first_transaction->rec_group;
463 $line->{type} = 'Link';
465 #add the rest of transaction of this group
466 my ($previous_bt_id, $previous_acc_trans_id) = ($first_transaction->bank_transaction_id, $first_transaction->acc_trans_id);
467 foreach my $linked_transaction (@{ $linked_transactions }) {
468 my $bank_transaction = $linked_transaction->bank_transaction;
469 my $acc_transaction = $linked_transaction->acc_tran;
470 if (defined $fromdate) {
471 $bank_transaction->{class} = 'out_of_balance' if ( $bank_transaction->transdate lt $fromdate );
472 $acc_transaction->{class} = 'out_of_balance' if ( $acc_transaction->transdate lt $fromdate );
474 if (defined $todate) {
475 $bank_transaction->{class} = 'out_of_balance' if ( $bank_transaction->transdate gt $todate );
476 $acc_transaction->{class} = 'out_of_balance' if ( $acc_transaction->transdate gt $todate );
478 if ($bank_transaction->id != $previous_bt_id) {
479 push @{ $line->{BT} }, $bank_transaction;
481 if ($acc_transaction->acc_trans_id != $previous_acc_trans_id) {
482 push @{ $line->{BB} }, $acc_transaction;
488 #add non-cleared bank transactions
489 my $bank_transactions = SL::DB::Manager::BankTransaction->get_all(where => \@bt_where);
490 foreach my $bt (@{ $bank_transactions }) {
492 $line->{BT} = [ $bt ];
493 $line->{type} = 'BT';
494 $line->{id} = $bt->id;
498 #add non-cleared bookings on bank
499 my $bookings_on_bank = SL::DB::Manager::AccTransaction->get_all(where => \@bb_where);
500 foreach my $bb (@{ $bookings_on_bank }) {
501 if ($::form->{filter}->{show_stornos} or !$bb->get_transaction->storno) {
503 $line->{BB} = [ $bb ];
504 $line->{type} = 'BB';
505 $line->{id} = $bb->acc_trans_id;
511 @rows = sort sort_by_transdate @rows;
513 $self->{LINKED_TRANSACTIONS} = \@rows;
516 sub sort_by_transdate {
517 if ($a->{BT} and $b->{BT}) {
518 return $a->{BT}[0]->amount <=> $b->{BT}[0]->amount if $a->{BT}[0]->transdate eq $b->{BT}[0]->transdate;
519 return $a->{BT}[0]->transdate cmp $b->{BT}[0]->transdate;
522 return $a->{BT}[0]->amount <=> (-1 * $b->{BB}[0]->amount) if $a->{BT}[0]->transdate eq $b->{BB}[0]->transdate;
523 return $a->{BT}[0]->transdate cmp $b->{BB}[0]->transdate;
526 return (-1 * $a->{BB}[0]->amount) <=> $b->{BT}[0]->amount if $a->{BB}[0]->transdate eq $b->{BT}[0]->transdate;
527 return $a->{BB}[0]->transdate cmp $b->{BT}[0]->transdate;
529 return (-1 * $a->{BB}[0]->amount) <=> (-1 * $b->{BB}[0]->amount) if $a->{BB}[0]->transdate eq $b->{BB}[0]->transdate;
530 return $a->{BB}[0]->transdate cmp $b->{BB}[0]->transdate;
536 $self->_filter_to_where;
538 my (@bt_where, @bb_where);
539 @bt_where = @{ $self->{bt_where} };
540 @bb_where = @{ $self->{bb_where} };
542 my $bank_transactions = SL::DB::Manager::BankTransaction->get_all(where => \@bt_where );
543 my $payments = SL::DB::Manager::AccTransaction ->get_all(where => \@bb_where );
545 #for absolute balance get all bookings till todate
546 my $todate = $::locale->parse_date_to_object(\%::myconfig, $::form->{filter}->{todate_date__le});
548 my @all_bt_where = (local_bank_account_id => $self->{bank_account}->id);
549 my @all_bb_where = (chart_id => $self->{bank_account}->chart_id);
552 push @all_bt_where, (transdate => { le => $todate });
553 push @all_bb_where, (transdate => { le => $todate });
556 my $all_bank_transactions = SL::DB::Manager::BankTransaction->get_all(where => \@all_bt_where);
557 my $all_payments = SL::DB::Manager::AccTransaction ->get_all(where => \@all_bb_where);
559 my ($bt_balance, $bb_balance) = (0,0);
560 my ($absolut_bt_balance, $absolut_bb_balance) = (0,0);
562 map { $bt_balance += $_->amount } @{ $bank_transactions };
563 map { $bb_balance += $_->amount if ($::form->{filter}->{show_stornos} or !$_->get_transaction->storno) } @{ $payments };
564 map { $absolut_bt_balance += $_->amount } @{ $all_bank_transactions };
565 map { $absolut_bb_balance += $_->amount } @{ $all_payments };
567 $self->{bt_balance} = $bt_balance || 0;
568 $self->{bb_balance} = $bb_balance || 0;
569 $self->{absolut_bt_balance} = $absolut_bt_balance || 0;
570 $self->{absolut_bb_balance} = $absolut_bb_balance || 0;
572 $self->{difference} = $bt_balance + $bb_balance;
576 [ { title => t8("all"), value => '' },
577 { title => t8("cleared"), value => 'TRUE' },
578 { title => t8("uncleared"), value => 'FALSE' }, ]
581 sub init_BANK_ACCOUNTS {
582 SL::DB::Manager::BankAccount->get_all();