|
package SL::Controller::BankTransaction;
|
|
|
|
# idee- möglichkeit bankdaten zu übernehmen in stammdaten
|
|
# erst Kontenabgleich, um alle gl-Einträge wegzuhaben
|
|
use strict;
|
|
|
|
use parent qw(SL::Controller::Base);
|
|
|
|
use SL::Controller::Helper::GetModels;
|
|
use SL::Controller::Helper::ReportGenerator;
|
|
use SL::ReportGenerator;
|
|
|
|
use SL::DB::BankTransaction;
|
|
use SL::Helper::Flash;
|
|
use SL::Locale::String;
|
|
use SL::SEPA;
|
|
use SL::DB::Invoice;
|
|
use SL::DB::PurchaseInvoice;
|
|
use SL::DB::RecordLink;
|
|
use SL::DB::ReconciliationLink;
|
|
use SL::JSON;
|
|
use SL::DB::Chart;
|
|
use SL::DB::AccTransaction;
|
|
use SL::DB::BankTransactionAccTrans;
|
|
use SL::DB::Tax;
|
|
use SL::DB::BankAccount;
|
|
use SL::DB::GLTransaction;
|
|
use SL::DB::RecordTemplate;
|
|
use SL::DB::SepaExportItem;
|
|
use SL::DBUtils qw(like do_query);
|
|
|
|
use SL::Presenter::Tag qw(checkbox_tag html_tag);
|
|
use Carp;
|
|
use List::UtilsBy qw(partition_by);
|
|
use List::MoreUtils qw(any);
|
|
use List::Util qw(max);
|
|
|
|
use Rose::Object::MakeMethods::Generic
|
|
(
|
|
scalar => [ qw(callback transaction) ],
|
|
'scalar --get_set_init' => [ qw(models problems) ],
|
|
);
|
|
|
|
__PACKAGE__->run_before('check_auth');
|
|
|
|
|
|
#
|
|
# actions
|
|
#
|
|
|
|
sub action_search {
|
|
my ($self) = @_;
|
|
|
|
my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted( query => [ obsolete => 0 ] );
|
|
|
|
$self->setup_search_action_bar;
|
|
$self->render('bank_transactions/search',
|
|
BANK_ACCOUNTS => $bank_accounts);
|
|
}
|
|
|
|
sub action_list_all {
|
|
my ($self) = @_;
|
|
|
|
$self->make_filter_summary;
|
|
$self->prepare_report;
|
|
|
|
$self->setup_list_all_action_bar;
|
|
$self->report_generator_list_objects(report => $self->{report}, objects => $self->models->get);
|
|
}
|
|
|
|
sub gather_bank_transactions_and_proposals {
|
|
my ($self, %params) = @_;
|
|
|
|
my $sort_by = $params{sort_by} || 'transdate';
|
|
$sort_by = 'transdate' if $sort_by eq 'proposal';
|
|
$sort_by .= $params{sort_dir} ? ' DESC' : ' ASC';
|
|
|
|
my @where = ();
|
|
push @where, (transdate => { ge => $params{fromdate} }) if $params{fromdate};
|
|
push @where, (transdate => { lt => $params{todate} }) if $params{todate};
|
|
# bank_transactions no younger than starting date,
|
|
# including starting date (same search behaviour as fromdate)
|
|
# but OPEN invoices to be matched may be from before
|
|
if ( $params{bank_account}->reconciliation_starting_date ) {
|
|
push @where, (transdate => { ge => $params{bank_account}->reconciliation_starting_date });
|
|
};
|
|
|
|
my $bank_transactions = SL::DB::Manager::BankTransaction->get_all(
|
|
with_objects => [ 'local_bank_account', 'currency' ],
|
|
sort_by => $sort_by,
|
|
limit => 10000,
|
|
where => [
|
|
amount => {ne => \'invoice_amount'}, # '} make emacs happy
|
|
local_bank_account_id => $params{bank_account}->id,
|
|
cleared => 0,
|
|
@where
|
|
],
|
|
);
|
|
# credit notes have a negative amount, treat differently
|
|
my $all_open_ar_invoices = SL::DB::Manager::Invoice->get_all(where => [ or => [ amount => { gt => \'paid' }, # '} make emacs happy
|
|
and => [ type => 'credit_note',
|
|
amount => { lt => \'paid' } # '} make emacs happy
|
|
],
|
|
],
|
|
],
|
|
with_objects => ['customer','payment_terms']);
|
|
|
|
my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => [amount => { ne => \'paid' }], # '}] make emacs happy
|
|
with_objects => ['vendor' ,'payment_terms']);
|
|
my $all_open_sepa_export_items = SL::DB::Manager::SepaExportItem->get_all(where => [chart_id => $params{bank_account}->chart_id ,
|
|
'sepa_export.executed' => 0,
|
|
'sepa_export.closed' => 0
|
|
],
|
|
with_objects => ['sepa_export']);
|
|
|
|
my @all_open_invoices;
|
|
# filter out invoices with less than 1 cent outstanding
|
|
push @all_open_invoices, map { $_->{is_ar}=1 ; $_ } grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ar_invoices };
|
|
push @all_open_invoices, map { $_->{is_ar}=0 ; $_ } grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
|
|
|
|
my %sepa_exports;
|
|
my %sepa_export_items_by_id = partition_by { $_->ar_id || $_->ap_id } @$all_open_sepa_export_items;
|
|
|
|
# first collect sepa export items to open invoices
|
|
foreach my $open_invoice (@all_open_invoices){
|
|
$open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount,2);
|
|
$open_invoice->{skonto_type} = 'without_skonto';
|
|
foreach (@{ $sepa_export_items_by_id{ $open_invoice->id } || [] }) {
|
|
my $factor = ($_->ar_id == $open_invoice->id ? 1 : -1);
|
|
$open_invoice->{realamount} = $::form->format_amount(\%::myconfig,$open_invoice->amount*$factor,2);
|
|
|
|
$open_invoice->{skonto_type} = $_->payment_type;
|
|
$sepa_exports{$_->sepa_export_id} ||= { count => 0, is_ar => 0, amount => 0, proposed => 0, invoices => [], item => $_ };
|
|
$sepa_exports{$_->sepa_export_id}->{count}++;
|
|
$sepa_exports{$_->sepa_export_id}->{is_ar}++ if $_->ar_id == $open_invoice->id;
|
|
$sepa_exports{$_->sepa_export_id}->{amount} += $_->amount * $factor;
|
|
push @{ $sepa_exports{$_->sepa_export_id}->{invoices} }, $open_invoice;
|
|
}
|
|
}
|
|
|
|
# try to match each bank_transaction with each of the possible open invoices
|
|
# by awarding points
|
|
my @proposals;
|
|
|
|
foreach my $bt (@{ $bank_transactions }) {
|
|
## 5 Stellen hinter dem Komma auf 2 Stellen reduzieren
|
|
$bt->amount($bt->amount*1);
|
|
$bt->invoice_amount($bt->invoice_amount*1);
|
|
|
|
$bt->{proposals} = [];
|
|
$bt->{rule_matches} = [];
|
|
|
|
$bt->{remote_name} .= $bt->{remote_name_1} if $bt->{remote_name_1};
|
|
|
|
if ( $bt->is_batch_transaction ) {
|
|
my $found=0;
|
|
foreach ( keys %sepa_exports) {
|
|
if ( abs(($sepa_exports{$_}->{amount} * 1) - ($bt->amount * 1)) < 0.01 ) {
|
|
## jupp
|
|
@{$bt->{proposals}} = @{$sepa_exports{$_}->{invoices}};
|
|
$bt->{sepa_export_ok} = 1;
|
|
$sepa_exports{$_}->{proposed}=1;
|
|
push(@proposals, $bt);
|
|
$found=1;
|
|
last;
|
|
}
|
|
}
|
|
next if $found;
|
|
# batch transaction has no remotename !!
|
|
}
|
|
|
|
# try to match the current $bt to each of the open_invoices, saving the
|
|
# results of get_agreement_with_invoice in $open_invoice->{agreement} and
|
|
# $open_invoice->{rule_matches}.
|
|
|
|
# The values are overwritten each time a new bt is checked, so at the end
|
|
# of each bt the likely results are filtered and those values are stored in
|
|
# the arrays $bt->{proposals} and $bt->{rule_matches}, and the agreement
|
|
# score is stored in $bt->{agreement}
|
|
|
|
foreach my $open_invoice (@all_open_invoices) {
|
|
($open_invoice->{agreement}, $open_invoice->{rule_matches}) = $bt->get_agreement_with_invoice($open_invoice,
|
|
sepa_export_items => $all_open_sepa_export_items,
|
|
);
|
|
$open_invoice->{realamount} = $::form->format_amount(\%::myconfig,
|
|
$open_invoice->amount * ($open_invoice->{is_ar} ? 1 : -1), 2);
|
|
}
|
|
|
|
my $agreement = 15;
|
|
my $min_agreement = 3; # suggestions must have at least this score
|
|
|
|
my $max_agreement = max map { $_->{agreement} } @all_open_invoices;
|
|
|
|
# add open_invoices with highest agreement into array $bt->{proposals}
|
|
if ( $max_agreement >= $min_agreement ) {
|
|
$bt->{proposals} = [ grep { $_->{agreement} == $max_agreement } @all_open_invoices ];
|
|
$bt->{agreement} = $max_agreement; #scalar @{ $bt->{proposals} } ? $agreement + 1 : '';
|
|
|
|
# store the rule_matches in a separate array, so they can be displayed in template
|
|
foreach ( @{ $bt->{proposals} } ) {
|
|
push(@{$bt->{rule_matches}}, $_->{rule_matches});
|
|
};
|
|
};
|
|
} # finished one bt
|
|
# finished all bt
|
|
|
|
# separate filter for proposals (second tab, agreement >= 5 and exactly one match)
|
|
# to qualify as a proposal there has to be
|
|
# * agreement >= 5 TODO: make threshold configurable in configuration
|
|
# * there must be only one exact match
|
|
my $proposal_threshold = 5;
|
|
my @otherproposals = grep {
|
|
($_->{agreement} >= $proposal_threshold)
|
|
&& (1 == scalar @{ $_->{proposals} })
|
|
&& ($_->{proposals}->[0]->forex == 0) # nyi forex invoices for automatic booking
|
|
} @{ $bank_transactions };
|
|
|
|
push @proposals, @otherproposals;
|
|
|
|
# sort bank transaction proposals by quality (score) of proposal
|
|
if ($params{sort_by} && $params{sort_by} eq 'proposal') {
|
|
my $dir = $params{sort_dir} ? 1 : -1;
|
|
$bank_transactions = [ sort { ($a->{agreement} <=> $b->{agreement}) * $dir } @{ $bank_transactions } ];
|
|
}
|
|
|
|
return ( $bank_transactions , \@proposals );
|
|
}
|
|
|
|
sub action_list {
|
|
my ($self) = @_;
|
|
|
|
if (!$::form->{filter}{bank_account}) {
|
|
flash('error', t8('No bank account chosen!'));
|
|
$self->action_search;
|
|
return;
|
|
}
|
|
|
|
my $bank_account = SL::DB::BankAccount->load_cached($::form->{filter}->{bank_account});
|
|
my $fromdate = $::locale->parse_date_to_object($::form->{filter}->{fromdate});
|
|
my $todate = $::locale->parse_date_to_object($::form->{filter}->{todate});
|
|
$todate->add( days => 1 ) if $todate;
|
|
|
|
my ($bank_transactions, $proposals) = $self->gather_bank_transactions_and_proposals(
|
|
bank_account => $bank_account,
|
|
fromdate => $fromdate,
|
|
todate => $todate,
|
|
sort_by => $::form->{sort_by},
|
|
sort_dir => $::form->{sort_dir},
|
|
);
|
|
|
|
$::request->layout->add_javascripts("kivi.BankTransaction.js");
|
|
$self->render('bank_transactions/list',
|
|
title => t8('Bank transactions MT940'),
|
|
BANK_TRANSACTIONS => $bank_transactions,
|
|
PROPOSALS => $proposals,
|
|
bank_account => $bank_account,
|
|
ui_tab => scalar(@{ $proposals }) > 0 ? 1 : 0,
|
|
);
|
|
}
|
|
|
|
sub action_assign_invoice {
|
|
my ($self) = @_;
|
|
|
|
$self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
|
|
|
|
$self->render('bank_transactions/assign_invoice',
|
|
{ layout => 0 },
|
|
title => t8('Assign invoice'),);
|
|
}
|
|
|
|
sub action_create_invoice {
|
|
my ($self) = @_;
|
|
my %myconfig = %main::myconfig;
|
|
|
|
$self->transaction(SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id}));
|
|
|
|
my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(iban => $self->transaction->{remote_account_number});
|
|
my $use_vendor_filter = $self->transaction->{remote_account_number} && $vendor_of_transaction;
|
|
|
|
my $templates_ap = SL::DB::Manager::RecordTemplate->get_all(
|
|
where => [ template_type => 'ap_transaction' ],
|
|
sort_by => [ qw(template_name) ],
|
|
with_objects => [ qw(employee vendor) ],
|
|
);
|
|
my $templates_gl = SL::DB::Manager::RecordTemplate->get_all(
|
|
query => [ template_type => 'gl_transaction',
|
|
chart_id => SL::DB::Manager::BankAccount->find_by(id => $self->transaction->local_bank_account_id)->chart_id,
|
|
],
|
|
sort_by => [ qw(template_name) ],
|
|
with_objects => [ qw(employee record_template_items) ],
|
|
);
|
|
|
|
# pre filter templates_ap, if we have a vendor match (IBAN eq IBAN) - show and allow user to edit this via gui!
|
|
$templates_ap = [ grep { $_->vendor_id == $vendor_of_transaction->id } @{ $templates_ap } ] if $use_vendor_filter;
|
|
|
|
$self->callback($self->url_for(
|
|
action => 'list',
|
|
'filter.bank_account' => $::form->{filter}->{bank_account},
|
|
'filter.todate' => $::form->{filter}->{todate},
|
|
'filter.fromdate' => $::form->{filter}->{fromdate},
|
|
));
|
|
|
|
# if we have exactly one ap match, use this directly
|
|
if (1 == scalar @{ $templates_ap }) {
|
|
$self->redirect_to($self->load_ap_record_template_url($templates_ap->[0]));
|
|
|
|
} else {
|
|
my $dialog_html = $self->render(
|
|
'bank_transactions/create_invoice',
|
|
{ layout => 0, output => 0 },
|
|
title => t8('Create invoice'),
|
|
TEMPLATES_GL => $use_vendor_filter && @{ $templates_ap } ? undef : $templates_gl,
|
|
TEMPLATES_AP => $templates_ap,
|
|
vendor_name => $use_vendor_filter && @{ $templates_ap } ? $vendor_of_transaction->name : undef,
|
|
);
|
|
$self->js->run('kivi.BankTransaction.show_create_invoice_dialog', $dialog_html)->render;
|
|
}
|
|
}
|
|
|
|
sub action_ajax_payment_suggestion {
|
|
my ($self) = @_;
|
|
|
|
# based on a BankTransaction ID and a Invoice or PurchaseInvoice ID passed via $::form,
|
|
# create an HTML blob to be used by the js function add_invoices in templates/webpages/bank_transactions/list.html
|
|
# and return encoded as JSON
|
|
|
|
croak("Need bt_id") unless $::form->{bt_id};
|
|
|
|
my $invoice = SL::DB::Manager::Invoice->find_by( id => $::form->{prop_id} ) || SL::DB::Manager::PurchaseInvoice->find_by( id => $::form->{prop_id} );
|
|
|
|
croak("No valid invoice found") unless $invoice;
|
|
|
|
my $html = $self->render(
|
|
'bank_transactions/_payment_suggestion', { output => 0 },
|
|
bt_id => $::form->{bt_id},
|
|
invoice => $invoice,
|
|
);
|
|
|
|
$self->render(\ SL::JSON::to_json( { 'html' => "$html" } ), { layout => 0, type => 'json', process => 0 });
|
|
};
|
|
|
|
sub action_filter_templates {
|
|
my ($self) = @_;
|
|
|
|
$self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
|
|
|
|
my (@filter, @filter_ap);
|
|
|
|
# filter => gl and ap | filter_ap = ap (i.e. vendorname)
|
|
push @filter, ('template_name' => { ilike => '%' . $::form->{template} . '%' }) if $::form->{template};
|
|
push @filter, ('reference' => { ilike => '%' . $::form->{reference} . '%' }) if $::form->{reference};
|
|
push @filter_ap, ('vendor.name' => { ilike => '%' . $::form->{vendor} . '%' }) if $::form->{vendor};
|
|
push @filter_ap, @filter;
|
|
my $templates_gl = SL::DB::Manager::RecordTemplate->get_all(
|
|
query => [ template_type => 'gl_transaction',
|
|
chart_id => SL::DB::Manager::BankAccount->find_by(id => $self->transaction->local_bank_account_id)->chart_id,
|
|
(and => \@filter) x !!@filter
|
|
],
|
|
with_objects => [ qw(employee record_template_items) ],
|
|
);
|
|
|
|
my $templates_ap = SL::DB::Manager::RecordTemplate->get_all(
|
|
where => [ template_type => 'ap_transaction', (and => \@filter_ap) x !!@filter_ap ],
|
|
with_objects => [ qw(employee vendor) ],
|
|
);
|
|
$::form->{filter} //= {};
|
|
|
|
$self->callback($self->url_for(
|
|
action => 'list',
|
|
'filter.bank_account' => $::form->{filter}->{bank_account},
|
|
'filter.todate' => $::form->{filter}->{todate},
|
|
'filter.fromdate' => $::form->{filter}->{fromdate},
|
|
));
|
|
|
|
my $output = $self->render(
|
|
'bank_transactions/_template_list',
|
|
{ output => 0 },
|
|
TEMPLATES_AP => $templates_ap,
|
|
TEMPLATES_GL => $templates_gl,
|
|
);
|
|
|
|
$self->render(\to_json({ html => $output |