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) ],
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->render('reconciliation/search');
}
sub action_reconciliation {
$self->_get_balances;
$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) });
+ title => t8('Reconciliation'));
}
sub action_load_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 });
$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;
}
$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 });
$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;
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' ]);
$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;
#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];
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) {
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 => { gt => $self->{bank_account}->reconciliation_starting_date });
+ push @bb_where, (transdate => { gt => $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} );
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} );
$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');
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 );
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 );
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;
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';
@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;
}
sub init_BANK_ACCOUNTS {
- SL::DB::Manager::BankAccount->get_all();
+ SL::DB::Manager::BankAccount->get_all_sorted( query => [ obsolete => 0 ] );
}
1;
projects / kivitendo-erp.git / blobdiff