X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FController%2FReconciliation.pm;h=c9d415f1e62184cf8b1dbb59f0aa693decd82d12;hb=3fdf284406a544178c13603e0da5c8848bac6301;hp=cd5707b206ac6897fffd170d5650b8f3af877bd0;hpb=6a12a968761127af91e9da8db7579be2836bcaaa;p=kivitendo-erp.git diff --git a/SL/Controller/Reconciliation.pm b/SL/Controller/Reconciliation.pm index cd5707b20..c9d415f1e 100644 --- a/SL/Controller/Reconciliation.pm +++ b/SL/Controller/Reconciliation.pm @@ -10,9 +10,10 @@ use SL::Controller::Helper::ParseFilter; use SL::Helper::Flash; use SL::DB::BankTransaction; -use SL::DB::BankAccount; +use SL::DB::Manager::BankAccount; use SL::DB::AccTransaction; use SL::DB::ReconciliationLink; +use List::Util qw(sum); use Rose::Object::MakeMethods::Generic ( 'scalar --get_set_init' => [ qw(cleared BANK_ACCOUNTS) ], @@ -28,28 +29,23 @@ __PACKAGE__->run_before('_bank_account'); sub action_search { my ($self) = @_; - $self->render('reconciliation/search', - label_sub => sub { t8('#1 - Account number #2, bank code #3, #4', - $_[0]->name, - $_[0]->bank, - $_[0]->account_number, - $_[0]->bank_code) }); + $self->setup_search_action_bar; + $self->render('reconciliation/search'); } sub action_reconciliation { my ($self) = @_; + $self->_get_proposals; + $self->_get_linked_transactions; $self->_get_balances; + $self->setup_reconciliation_action_bar; $self->render('reconciliation/form', - title => t8('Reconciliation'), - label_sub => sub { t8('#1 - Account number #2, bank code #3, #4', - $_[0]->name, - $_[0]->bank, - $_[0]->account_number, - $_[0]->bank_code) }); + ui_tab => scalar(@{$self->{PROPOSALS}}) > 0?1:0, + title => t8('Reconciliation')); } sub action_load_overview { @@ -74,11 +70,11 @@ sub action_filter_overview { $self->_get_balances; my $output = $self->render('reconciliation/_linked_transactions', { output => 0 }); - my %result = ( html => $output, - absolut_bt_balance => $::form->format_amount(\%::myconfig, $self->{absolut_bt_balance}, 2), + my %result = ( html => $output, + absolut_bt_balance => $::form->format_amount(\%::myconfig, $self->{absolut_bt_balance}, 2), absolut_bb_balance => $::form->format_amount(\%::myconfig, -1 * $self->{absolut_bb_balance}, 2), - bt_balance => $::form->format_amount(\%::myconfig, $self->{bt_balance}, 2), - bb_balance => $::form->format_amount(\%::myconfig, -1 * $self->{bb_balance}, 2) + bt_balance => $::form->format_amount(\%::myconfig, $self->{bt_balance}, 2), + bb_balance => $::form->format_amount(\%::myconfig, -1 * $self->{bb_balance}, 2) ); $self->render(\to_json(\%result), { type => 'json', process => 0 }); @@ -100,20 +96,20 @@ sub action_update_reconciliation_table { $self->render(\to_json(\%result), { type => 'json', process => 0 }); } -sub action_reconciliate { +sub action_reconcile { my ($self) = @_; #Check elements my @errors = $self->_get_elements_and_validate; if (@errors) { - unshift(@errors, (t8('Could not reconciliate chosen elements!'))); + unshift(@errors, (t8('Could not reconcile chosen elements!'))); flash('error', @errors); $self->action_reconciliation; return; } - $self->_reconciliate; + $self->_reconcile; $self->action_reconciliation; } @@ -140,11 +136,11 @@ sub action_delete_reconciliation { $self->_get_balances; my $output = $self->render('reconciliation/_linked_transactions', { output => 0 }); - my %result = ( html => $output, - absolut_bt_balance => $::form->format_amount(\%::myconfig, $self->{absolut_bt_balance}, 2), - absolut_bb_balance => $::form->format_amount(\%::myconfig, -1 * $self->{absolut_bb_balance}, 2), - bt_balance => $::form->format_amount(\%::myconfig, $self->{bt_balance}, 2), - bb_balance => $::form->format_amount(\%::myconfig, -1 * $self->{bb_balance}, 2) + my %result = ( html => $output, + absolut_bt_balance => $::form->format_amount(\%::myconfig, $self ->{absolut_bt_balance}, 2), + absolut_bb_balance => $::form->format_amount(\%::myconfig, -1 * $self ->{absolut_bb_balance}, 2), + bt_balance => $::form->format_amount(\%::myconfig, $self ->{bt_balance}, 2), + bb_balance => $::form->format_amount(\%::myconfig, -1 * $self ->{bb_balance}, 2) ); $self->render(\to_json(\%result), { type => 'json', process => 0 }); @@ -168,17 +164,17 @@ sub action_filter_proposals { $self->_get_proposals; my $output = $self->render('reconciliation/proposals', { output => 0 }); - my %result = ( html => $output, - absolut_bt_balance => $::form->format_amount(\%::myconfig, $self->{absolut_bt_balance}, 2), - absolut_bb_balance => $::form->format_amount(\%::myconfig, -1 * $self->{absolut_bb_balance}, 2), - bt_balance => $::form->format_amount(\%::myconfig, $self->{bt_balance}, 2), - bb_balance => $::form->format_amount(\%::myconfig, -1 * $self->{bb_balance}, 2) + my %result = ( html => $output, + absolut_bt_balance => $::form->format_amount(\%::myconfig, $self ->{absolut_bt_balance}, 2), + absolut_bb_balance => $::form->format_amount(\%::myconfig, -1 * $self ->{absolut_bb_balance}, 2), + bt_balance => $::form->format_amount(\%::myconfig, $self ->{bt_balance}, 2), + bb_balance => $::form->format_amount(\%::myconfig, -1 * $self ->{bb_balance}, 2) ); $self->render(\to_json(\%result), { type => 'json', process => 0 }); } -sub action_reconciliate_proposals { +sub action_reconcile_proposals { my ($self) = @_; my $counter = 0; @@ -229,6 +225,15 @@ sub _bank_account { sub _get_proposals { my ($self) = @_; + # reconciliation suggestion is based on: + # * record_link exists (was paid by bank transaction) + # or acc_trans entry exists where + # * amount is exactly the same + # * date is the same + # * IBAN or account number have to match exactly (cv details, no spaces) + # * not a gl storno + # * there is exactly one match for all conditions + $self->_filter_to_where; my $bank_transactions = SL::DB::Manager::BankTransaction->get_all(where => [ @{ $self->{bt_where} }, cleared => '0' ]); @@ -243,6 +248,7 @@ sub _get_proposals { $proposal->{BT} = $bt; $proposal->{BB} = []; + # first of all check if any of the bank_transactions are already linked (i.e. were paid via bank transactions) my $linked_records = SL::DB::Manager::RecordLink->get_all(where => [ from_table => 'bank_transactions', from_id => $bt->id ]); foreach my $linked_record (@{ $linked_records }) { my $invoice; @@ -268,18 +274,27 @@ sub _get_proposals { #add proposal if something in acc_trans was found #otherwise try to find another entry in acc_trans and add it - if (scalar @{ $proposal->{BB} } and !$check_sum) { + # for linked_records we allow a slight difference / imprecision, for acc_trans search we don't + if (scalar @{ $proposal->{BB} } and abs($check_sum) <= 0.01 ) { push @proposals, $proposal; } elsif (!scalar @{ $proposal->{BB} }) { + # use account_number and iban for matching remote account number + # don't suggest gl stornos (ar and ap stornos shouldn't have any payments) + + my @account_number_match = ( + ( 'ar.customer.iban' => $bt->remote_account_number ), + ( 'ar.customer.account_number' => $bt->remote_account_number ), + ( 'ap.vendor.iban' => $bt->remote_account_number ), + ( 'ap.vendor.account_number' => $bt->remote_account_number ), + ( 'gl.storno' => '0' ), + ); + my $acc_transactions = SL::DB::Manager::AccTransaction->get_all(where => [ @{ $self->{bb_where} }, amount => -1 * $bt->amount, cleared => '0', - or => [ - and => [ 'ar.customer.account_number' => $bt->remote_account_number, - 'ar.customer.bank_code' => $bt->remote_bank_code, ], - and => [ 'ap.vendor.account_number' => $bt->remote_account_number, - 'ap.vendor.bank_code' => $bt->remote_bank_code, ], - 'gl.storno' => '0' ]], + 'transdate' => $bt->transdate, + or => [ @account_number_match ] + ], with_objects => [ 'ar', 'ap', 'ar.customer', 'ap.vendor', 'gl' ]); if (scalar @{ $acc_transactions } == 1) { push @{ $proposal->{BB} }, @{ $acc_transactions }[0]; @@ -328,7 +343,7 @@ sub _get_elements_and_validate { push @elements, $bb; } - if ($::form->round_amount($bt_sum + $bb_sum) != 0) { + if ($::form->round_amount($bt_sum + $bb_sum, 2) != 0) { push @errors, t8('Out of balance!'); } @@ -339,17 +354,17 @@ sub _get_elements_and_validate { return @errors; } -sub _reconciliate { +sub _reconcile { my ($self) = @_; - #1. Step: Set AccTrans and BankTransactions to 'cleared' + # 1. step: set AccTrans and BankTransactions to 'cleared' foreach my $element (@{ $self->{ELEMENTS} }) { $element->cleared('1'); $element->invoice_amount($element->amount) if $element->isa('SL::DB::BankTransaction'); $element->save; } - #2. Step: Insert entry in reconciliation_links + # 2. step: insert entry in reconciliation_links my $rec_group = SL::DB::Manager::ReconciliationLink->get_new_rec_group(); #There is either a 1:n relation or a n:1 relation if (scalar @{ $::form->{bt_ids} } == 1) { @@ -378,32 +393,41 @@ sub _filter_to_where { my %filter = @{ $parse_filter{query} }; my (@rl_where, @bt_where, @bb_where); - @rl_where = ('bank_transaction.local_bank_account_id' => $filter{local_bank_account_id}); + @rl_where = ('bank_transaction.local_bank_account_id' => $filter{local_bank_account_id}); @bt_where = (local_bank_account_id => $filter{local_bank_account_id}); @bb_where = (chart_id => $self->{bank_account}->chart_id); if ($filter{fromdate} and $filter{todate}) { - push @rl_where, (or => [ and => [ 'acc_tran.transdate' => $filter{fromdate}, - 'acc_tran.transdate' => $filter{todate} ], + push @rl_where, (or => [ and => [ 'acc_trans.transdate' => $filter{fromdate}, + 'acc_trans.transdate' => $filter{todate} ], and => [ 'bank_transaction.transdate' => $filter{fromdate}, - 'bank_transaction.transdate' => $filter{todate} ] ] ); + 'bank_transaction.transdate' => $filter{todate} ] ] ); - push @bt_where, (transdate => $filter{todate} ); - push @bt_where, (transdate => $filter{fromdate} ); - push @bb_where, (transdate => $filter{todate} ); - push @bb_where, (transdate => $filter{fromdate} ); + push @bt_where, (transdate => $filter{todate} ); + push @bt_where, (transdate => $filter{fromdate} ); + push @bb_where, (transdate => $filter{todate} ); + push @bb_where, (transdate => $filter{fromdate} ); } + if ( $self->{bank_account}->reconciliation_starting_date ) { + push @bt_where, (transdate => { ge => $self->{bank_account}->reconciliation_starting_date }); + push @bb_where, (transdate => { ge => $self->{bank_account}->reconciliation_starting_date }); + } + + # don't try to reconcile opening and closing balance transactions + push @bb_where, ('acc_trans.ob_transaction' => 0); + push @bb_where, ('acc_trans.cb_transaction' => 0); + if ($filter{fromdate} and not $filter{todate}) { - push @rl_where, (or => [ 'acc_tran.transdate' => $filter{fromdate}, + push @rl_where, (or => [ 'acc_trans.transdate' => $filter{fromdate}, 'bank_transaction.transdate' => $filter{fromdate} ] ); push @bt_where, (transdate => $filter{fromdate} ); push @bb_where, (transdate => $filter{fromdate} ); } if ($filter{todate} and not $filter{fromdate}) { - push @rl_where, ( or => [ 'acc_tran.transdate' => $filter{todate} , + push @rl_where, ( or => [ 'acc_trans.transdate' => $filter{todate} , 'bank_transaction.transdate' => $filter{todate} ] ); push @bt_where, (transdate => $filter{todate} ); push @bb_where, (transdate => $filter{todate} ); @@ -411,7 +435,7 @@ sub _filter_to_where { if ($filter{cleared}) { $filter{cleared} = $filter{cleared} eq 'FALSE' ? '0' : '1'; - push @rl_where, ('acc_tran.cleared' => $filter{cleared} ); + push @rl_where, ('acc_trans.cleared' => $filter{cleared} ); push @bt_where, (cleared => $filter{cleared} ); push @bb_where, (cleared => $filter{cleared} ); @@ -428,6 +452,9 @@ sub _get_linked_transactions { $self->_filter_to_where; my (@where, @bt_where, @bb_where); + # don't try to reconcile opening and closing balances + # instead use an offset in configuration + @where = (@{ $self->{rl_where} }); @bt_where = (@{ $self->{bt_where} }, cleared => '0'); @bb_where = (@{ $self->{bb_where} }, cleared => '0'); @@ -437,17 +464,17 @@ sub _get_linked_transactions { my $reconciliation_groups = SL::DB::Manager::ReconciliationLink->get_all(distinct => 1, select => ['rec_group'], where => \@where, - with_objects => ['bank_transaction', 'acc_tran']); + with_objects => ['bank_transaction', 'acc_trans']); - my $fromdate = $::locale->parse_date_to_object(\%::myconfig, $::form->{filter}->{fromdate_date__ge}); - my $todate = $::locale->parse_date_to_object(\%::myconfig, $::form->{filter}->{todate_date__le}); + my $fromdate = $::locale->parse_date_to_object($::form->{filter}->{fromdate_date__ge}); + my $todate = $::locale->parse_date_to_object($::form->{filter}->{todate_date__le}); foreach my $rec_group (@{ $reconciliation_groups }) { - my $linked_transactions = SL::DB::Manager::ReconciliationLink->get_all(where => [rec_group => $rec_group->rec_group], with_objects => ['bank_transaction', 'acc_tran']); + my $linked_transactions = SL::DB::Manager::ReconciliationLink->get_all(where => [rec_group => $rec_group->rec_group], with_objects => ['bank_transaction', 'acc_trans']); my $line; my $first_transaction = shift @{ $linked_transactions }; my $first_bt = $first_transaction->bank_transaction; - my $first_bb = $first_transaction->acc_tran; + my $first_bb = $first_transaction->acc_trans; if (defined $fromdate) { $first_bt->{class} = 'out_of_balance' if ( $first_bt->transdate lt $fromdate ); @@ -466,7 +493,7 @@ sub _get_linked_transactions { my ($previous_bt_id, $previous_acc_trans_id) = ($first_transaction->bank_transaction_id, $first_transaction->acc_trans_id); foreach my $linked_transaction (@{ $linked_transactions }) { my $bank_transaction = $linked_transaction->bank_transaction; - my $acc_transaction = $linked_transaction->acc_tran; + my $acc_transaction = $linked_transaction->acc_trans; if (defined $fromdate) { $bank_transaction->{class} = 'out_of_balance' if ( $bank_transaction->transdate lt $fromdate ); $acc_transaction->{class} = 'out_of_balance' if ( $acc_transaction->transdate lt $fromdate ); @@ -485,7 +512,7 @@ sub _get_linked_transactions { push @rows, $line; } - #add non-cleared bank transactions + # add non-cleared bank transactions my $bank_transactions = SL::DB::Manager::BankTransaction->get_all(where => \@bt_where); foreach my $bt (@{ $bank_transactions }) { my $line; @@ -495,10 +522,10 @@ sub _get_linked_transactions { push @rows, $line; } - #add non-cleared bookings on bank + # add non-cleared bookings on bank my $bookings_on_bank = SL::DB::Manager::AccTransaction->get_all(where => \@bb_where); foreach my $bb (@{ $bookings_on_bank }) { - if ($::form->{filter}->{show_stornos} or !$bb->get_transaction->storno) { + if ($::form->{filter}->{show_stornos} or !$bb->record->storno) { my $line; $line->{BB} = [ $bb ]; $line->{type} = 'BB'; @@ -539,33 +566,46 @@ sub _get_balances { @bt_where = @{ $self->{bt_where} }; @bb_where = @{ $self->{bb_where} }; - my $bank_transactions = SL::DB::Manager::BankTransaction->get_all(where => \@bt_where ); - my $payments = SL::DB::Manager::AccTransaction ->get_all(where => \@bb_where ); - - #for absolute balance get all bookings till todate - my $todate = $::locale->parse_date_to_object(\%::myconfig, $::form->{filter}->{todate_date__le}); - my @all_bt_where = (local_bank_account_id => $self->{bank_account}->id); my @all_bb_where = (chart_id => $self->{bank_account}->chart_id); + my ($bt_balance, $bb_balance) = (0,0); + my ($absolut_bt_balance, $absolut_bb_balance) = (0,0); + + if ( $self->{bank_account}->reconciliation_starting_date ) { + $bt_balance = $self->{bank_account}->reconciliation_starting_balance; + $bb_balance = $self->{bank_account}->reconciliation_starting_balance * -1; + $absolut_bt_balance = $self->{bank_account}->reconciliation_starting_balance; + $absolut_bb_balance = $self->{bank_account}->reconciliation_starting_balance * -1; + + push @all_bt_where, ( transdate => { gt => $self->{bank_account}->reconciliation_starting_date }); + push @all_bb_where, ( transdate => { gt => $self->{bank_account}->reconciliation_starting_date }); + } + + my $bank_transactions = SL::DB::Manager::BankTransaction->get_all(where => \@bt_where ); + my $payments = SL::DB::Manager::AccTransaction ->get_all(where => \@bb_where ); + + # for absolute balance get all bookings until todate + my $todate = $::locale->parse_date_to_object($::form->{filter}->{todate_date__le}); + my $fromdate = $::locale->parse_date_to_object($::form->{filter}->{fromdate_date__le}); + if ($todate) { push @all_bt_where, (transdate => { le => $todate }); push @all_bb_where, (transdate => { le => $todate }); } my $all_bank_transactions = SL::DB::Manager::BankTransaction->get_all(where => \@all_bt_where); - my $all_payments = SL::DB::Manager::AccTransaction ->get_all(where => \@all_bb_where); + my $all_payments = SL::DB::Manager::AccTransaction ->get_all(where => \@all_bb_where); - my ($bt_balance, $bb_balance) = (0,0); - my ($absolut_bt_balance, $absolut_bb_balance) = (0,0); + $bt_balance += sum map { $_->amount } @{ $bank_transactions }; + $bb_balance += sum map { $_->amount if ($::form->{filter}->{show_stornos} or !$_->record->storno) } @{ $payments }; + + $absolut_bt_balance += sum map { $_->amount } @{ $all_bank_transactions }; + $absolut_bb_balance += sum map { $_->amount } @{ $all_payments }; - map { $bt_balance += $_->amount } @{ $bank_transactions }; - map { $bb_balance += $_->amount if ($::form->{filter}->{show_stornos} or !$_->get_transaction->storno) } @{ $payments }; - map { $absolut_bt_balance += $_->amount } @{ $all_bank_transactions }; - map { $absolut_bb_balance += $_->amount } @{ $all_payments }; - $self->{bt_balance} = $bt_balance || 0; - $self->{bb_balance} = $bb_balance || 0; + $self->{bt_balance} = $bt_balance || 0; + $self->{bb_balance} = $bb_balance || 0; $self->{absolut_bt_balance} = $absolut_bt_balance || 0; $self->{absolut_bb_balance} = $absolut_bb_balance || 0; @@ -579,7 +619,35 @@ sub init_cleared { } sub init_BANK_ACCOUNTS { - SL::DB::Manager::BankAccount->get_all(); + SL::DB::Manager::BankAccount->get_all_sorted( query => [ obsolete => 0 ] ); +} + +sub setup_search_action_bar { + my ($self, %params) = @_; + + for my $bar ($::request->layout->get('actionbar')) { + $bar->add( + action => [ + t8('Show'), + submit => [ '#search_form', { action => 'Reconciliation/reconciliation' } ], + accesskey => 'enter', + ], + ); + } +} + +sub setup_reconciliation_action_bar { + my ($self, %params) = @_; + + for my $bar ($::request->layout->get('actionbar')) { + $bar->add( + action => [ + t8('Filter'), + call => [ 'filter_table' ], + accesskey => 'enter', + ], + ); + } } 1;