Revision 15f58ff3
Von Kivitendo Admin vor mehr als 9 Jahren hinzugefügt
| SL/AM.pm | ||
|---|---|---|
| 45 | 45 |
use SL::DB::AuthUser; |
| 46 | 46 |
use SL::DB::Default; |
| 47 | 47 |
use SL::DB::Employee; |
| 48 |
use SL::DB::Chart; |
|
| 48 | 49 |
use SL::GenericTranslations; |
| 49 | 50 |
|
| 50 | 51 |
use strict; |
| ... | ... | |
| 1286 | 1287 |
t.taxdescription, |
| 1287 | 1288 |
round(t.rate * 100, 2) AS rate, |
| 1288 | 1289 |
(SELECT accno FROM chart WHERE id = chart_id) AS taxnumber, |
| 1289 |
(SELECT description FROM chart WHERE id = chart_id) AS account_description |
|
| 1290 |
(SELECT description FROM chart WHERE id = chart_id) AS account_description, |
|
| 1291 |
(SELECT accno FROM chart WHERE id = skonto_sales_chart_id) AS skonto_chart_accno, |
|
| 1292 |
(SELECT description FROM chart WHERE id = skonto_sales_chart_id) AS skonto_chart_description, |
|
| 1293 |
(SELECT accno FROM chart WHERE id = skonto_purchase_chart_id) AS skonto_chart_purchase_accno, |
|
| 1294 |
(SELECT description FROM chart WHERE id = skonto_purchase_chart_id) AS skonto_chart_purchase_description |
|
| 1290 | 1295 |
FROM tax t |
| 1291 | 1296 |
ORDER BY taxkey, rate|; |
| 1292 | 1297 |
|
| ... | ... | |
| 1328 | 1333 |
push @{ $form->{ACCOUNTS} }, $ref;
|
| 1329 | 1334 |
} |
| 1330 | 1335 |
|
| 1336 |
$form->{AR_PAID} = SL::DB::Manager::Chart->get_all(where => [ link => { like => '%AR_paid%' } ], sort_by => 'accno ASC');
|
|
| 1337 |
$form->{AP_PAID} = SL::DB::Manager::Chart->get_all(where => [ link => { like => '%AP_paid%' } ], sort_by => 'accno ASC');
|
|
| 1338 |
|
|
| 1339 |
$form->{skontochart_value_title_sub} = sub {
|
|
| 1340 |
my $item = shift; |
|
| 1341 |
return [ |
|
| 1342 |
$item->{id},
|
|
| 1343 |
$item->{accno} .' '. $item->{description},
|
|
| 1344 |
]; |
|
| 1345 |
}; |
|
| 1346 |
|
|
| 1331 | 1347 |
$sth->finish; |
| 1332 | 1348 |
|
| 1333 | 1349 |
$dbh->disconnect; |
| ... | ... | |
| 1350 | 1366 |
chart_id, |
| 1351 | 1367 |
chart_categories, |
| 1352 | 1368 |
(id IN (SELECT tax_id |
| 1353 |
FROM acc_trans)) AS tax_already_used |
|
| 1369 |
FROM acc_trans)) AS tax_already_used, |
|
| 1370 |
skonto_sales_chart_id, |
|
| 1371 |
skonto_purchase_chart_id |
|
| 1354 | 1372 |
FROM tax |
| 1355 | 1373 |
WHERE id = ? |; |
| 1356 | 1374 |
|
| ... | ... | |
| 1414 | 1432 |
$chart_categories .= 'E' if $form->{expense};
|
| 1415 | 1433 |
$chart_categories .= 'C' if $form->{costs};
|
| 1416 | 1434 |
|
| 1417 |
my @values = ($form->{taxkey}, $form->{taxdescription}, $form->{rate}, conv_i($form->{chart_id}), conv_i($form->{chart_id}), $chart_categories);
|
|
| 1435 |
my @values = ($form->{taxkey}, $form->{taxdescription}, $form->{rate}, conv_i($form->{chart_id}), conv_i($form->{chart_id}), conv_i($form->{skonto_sales_chart_id}), conv_i($form->{skonto_purchase_chart_id}), $chart_categories);
|
|
| 1418 | 1436 |
if ($form->{id} ne "") {
|
| 1419 | 1437 |
$query = qq|UPDATE tax SET |
| 1420 |
taxkey = ?, |
|
| 1421 |
taxdescription = ?, |
|
| 1422 |
rate = ?, |
|
| 1423 |
chart_id = ?, |
|
| 1424 |
taxnumber = (SELECT accno FROM chart WHERE id= ? ), |
|
| 1425 |
chart_categories = ? |
|
| 1438 |
taxkey = ?, |
|
| 1439 |
taxdescription = ?, |
|
| 1440 |
rate = ?, |
|
| 1441 |
chart_id = ?, |
|
| 1442 |
taxnumber = (SELECT accno FROM chart WHERE id = ? ), |
|
| 1443 |
skonto_sales_chart_id = ?, |
|
| 1444 |
skonto_purchase_chart_id = ?, |
|
| 1445 |
chart_categories = ? |
|
| 1426 | 1446 |
WHERE id = ?|; |
| 1427 | 1447 |
|
| 1428 | 1448 |
} else {
|
| ... | ... | |
| 1434 | 1454 |
rate, |
| 1435 | 1455 |
chart_id, |
| 1436 | 1456 |
taxnumber, |
| 1457 |
skonto_sales_chart_id, |
|
| 1458 |
skonto_purchase_chart_id, |
|
| 1437 | 1459 |
chart_categories, |
| 1438 | 1460 |
id |
| 1439 | 1461 |
) |
| 1440 |
VALUES (?, ?, ?, ?, (SELECT accno FROM chart WHERE id = ?), ?, ?)|; |
|
| 1462 |
VALUES (?, ?, ?, ?, (SELECT accno FROM chart WHERE id = ?), ?, ?, ?, ?)|;
|
|
| 1441 | 1463 |
} |
| 1442 | 1464 |
push(@values, $form->{id});
|
| 1443 | 1465 |
do_query($form, $dbh, $query, @values); |
| SL/Controller/BankAccount.pm | ||
|---|---|---|
| 1 |
SL::Controller::BankAccount; |
|
| 1 |
package SL::Controller::BankAccount;
|
|
| 2 | 2 |
|
| 3 | 3 |
use strict; |
| 4 | 4 |
|
| SL/Controller/BankTransaction.pm | ||
|---|---|---|
| 23 | 23 |
use SL::DB::Tax; |
| 24 | 24 |
use SL::DB::Draft; |
| 25 | 25 |
use SL::DB::BankAccount; |
| 26 |
use SL::Presenter; |
|
| 27 |
use List::Util qw(max); |
|
| 26 | 28 |
|
| 27 | 29 |
use Rose::Object::MakeMethods::Generic |
| 28 | 30 |
( |
| ... | ... | |
| 39 | 41 |
sub action_search {
|
| 40 | 42 |
my ($self) = @_; |
| 41 | 43 |
|
| 42 |
my $bank_accounts = SL::DB::Manager::BankAccount->get_all();
|
|
| 44 |
my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted( query => [ obsolete => 0 ] );
|
|
| 43 | 45 |
|
| 44 | 46 |
$self->render('bank_transactions/search',
|
| 45 |
label_sub => sub { t8('#1 - Account number #2, bank code #3, #4', $_[0]->name, $_[0]->account_number, $_[0]->bank_code, $_[0]->bank, )},
|
|
| 46 | 47 |
BANK_ACCOUNTS => $bank_accounts); |
| 47 | 48 |
} |
| 48 | 49 |
|
| 49 | 50 |
sub action_list_all {
|
| 50 | 51 |
my ($self) = @_; |
| 51 | 52 |
|
| 52 |
my $transactions = $self->models->get; |
|
| 53 |
|
|
| 54 | 53 |
$self->make_filter_summary; |
| 55 | 54 |
$self->prepare_report; |
| 56 | 55 |
|
| 57 |
$self->report_generator_list_objects(report => $self->{report}, objects => $transactions);
|
|
| 56 |
$self->report_generator_list_objects(report => $self->{report}, objects => $self->models->get);
|
|
| 58 | 57 |
} |
| 59 | 58 |
|
| 60 | 59 |
sub action_list {
|
| ... | ... | |
| 70 | 69 |
$sort_by = 'transdate' if $sort_by eq 'proposal'; |
| 71 | 70 |
$sort_by .= $::form->{sort_dir} ? ' DESC' : ' ASC';
|
| 72 | 71 |
|
| 73 |
my $fromdate = $::locale->parse_date_to_object(\%::myconfig, $::form->{filter}->{fromdate});
|
|
| 74 |
my $todate = $::locale->parse_date_to_object(\%::myconfig, $::form->{filter}->{todate});
|
|
| 72 |
my $fromdate = $::locale->parse_date_to_object($::form->{filter}->{fromdate});
|
|
| 73 |
my $todate = $::locale->parse_date_to_object($::form->{filter}->{todate});
|
|
| 75 | 74 |
$todate->add( days => 1 ) if $todate; |
| 76 | 75 |
|
| 77 | 76 |
my @where = (); |
| 78 | 77 |
push @where, (transdate => { ge => $fromdate }) if ($fromdate);
|
| 79 | 78 |
push @where, (transdate => { lt => $todate }) if ($todate);
|
| 79 |
my $bank_account = SL::DB::Manager::BankAccount->find_by( id => $::form->{filter}{bank_account} );
|
|
| 80 |
# bank_transactions no younger than starting date, |
|
| 81 |
# but OPEN invoices to be matched may be from before |
|
| 82 |
if ( $bank_account->reconciliation_starting_date ) {
|
|
| 83 |
push @where, (transdate => { gt => $bank_account->reconciliation_starting_date });
|
|
| 84 |
}; |
|
| 80 | 85 |
|
| 81 | 86 |
my $bank_transactions = SL::DB::Manager::BankTransaction->get_all(where => [ amount => {ne => \'invoice_amount'},
|
| 82 | 87 |
local_bank_account_id => $::form->{filter}{bank_account},
|
| ... | ... | |
| 88 | 93 |
my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => [amount => { gt => \'paid' }], with_objects => 'vendor');
|
| 89 | 94 |
|
| 90 | 95 |
my @all_open_invoices; |
| 91 |
push @all_open_invoices, @{ $all_open_ar_invoices };
|
|
| 92 |
push @all_open_invoices, @{ $all_open_ap_invoices };
|
|
| 96 |
# filter out invoices with less than 1 cent outstanding |
|
| 97 |
push @all_open_invoices, grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ar_invoices };
|
|
| 98 |
push @all_open_invoices, grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
|
|
| 99 |
|
|
| 100 |
# try to match each bank_transaction with each of the possible open invoices |
|
| 101 |
# by awarding points |
|
| 93 | 102 |
|
| 94 | 103 |
foreach my $bt (@{ $bank_transactions }) {
|
| 95 | 104 |
next unless $bt->{remote_name}; # bank has no name, usually fees, use create invoice to assign
|
| 96 |
foreach my $open_invoice (@all_open_invoices){
|
|
| 97 |
$open_invoice->{agreement} = 0;
|
|
| 98 |
|
|
| 99 |
#compare banking arrangements |
|
| 100 |
my ($bank_code, $account_number); |
|
| 101 |
$bank_code = $open_invoice->customer->bank_code if $open_invoice->is_sales; |
|
| 102 |
$account_number = $open_invoice->customer->account_number if $open_invoice->is_sales; |
|
| 103 |
$bank_code = $open_invoice->vendor->bank_code if ! $open_invoice->is_sales; |
|
| 104 |
$account_number = $open_invoice->vendor->account_number if ! $open_invoice->is_sales; |
|
| 105 |
($bank_code eq $bt->remote_bank_code |
|
| 106 |
&& $account_number eq $bt->remote_account_number) ? ($open_invoice->{agreement} += 2) : ();
|
|
| 107 |
|
|
| 108 |
my $datediff = $bt->transdate->{utc_rd_days} - $open_invoice->transdate->{utc_rd_days};
|
|
| 109 |
$open_invoice->{datediff} = $datediff;
|
|
| 110 |
|
|
| 111 |
#compare amount |
|
| 112 |
# (abs($open_invoice->amount) == abs($bt->amount)) ? ($open_invoice->{agreement} += 2) : ();
|
|
| 113 |
# do we need double abs here? |
|
| 114 |
(abs(abs($open_invoice->amount) - abs($bt->amount)) < 0.01) ? ($open_invoice->{agreement} += 4) : ();
|
|
| 115 |
|
|
| 116 |
#search invoice number in purpose |
|
| 117 |
my $invnumber = $open_invoice->invnumber; |
|
| 118 |
# possible improvement: match has to have more than 1 character? |
|
| 119 |
$bt->purpose =~ /\b$invnumber\b/i ? ($open_invoice->{agreement} += 2) : ();
|
|
| 120 |
|
|
| 121 |
#check sign |
|
| 122 |
if ( $open_invoice->is_sales && $bt->amount < 0 ) {
|
|
| 123 |
$open_invoice->{agreement} -= 1;
|
|
| 124 |
}; |
|
| 125 |
if ( ! $open_invoice->is_sales && $bt->amount > 0 ) {
|
|
| 126 |
$open_invoice->{agreement} -= 1;
|
|
| 127 |
}; |
|
| 128 | 105 |
|
| 129 |
#search customer/vendor number in purpose |
|
| 130 |
my $cvnumber; |
|
| 131 |
$cvnumber = $open_invoice->customer->customernumber if $open_invoice->is_sales; |
|
| 132 |
$cvnumber = $open_invoice->vendor->vendornumber if ! $open_invoice->is_sales; |
|
| 133 |
$bt->purpose =~ /\b$cvnumber\b/i ? ($open_invoice->{agreement}++) : ();
|
|
| 134 |
|
|
| 135 |
#compare customer/vendor name and account holder |
|
| 136 |
my $cvname; |
|
| 137 |
$cvname = $open_invoice->customer->name if $open_invoice->is_sales; |
|
| 138 |
$cvname = $open_invoice->vendor->name if ! $open_invoice->is_sales; |
|
| 139 |
$bt->remote_name =~ /\b$cvname\b/i ? ($open_invoice->{agreement}++) : ();
|
|
| 140 |
|
|
| 141 |
#Compare transdate of bank_transaction with transdate of invoice |
|
| 142 |
#Check if words in remote_name appear in cvname |
|
| 143 |
$open_invoice->{agreement} += &check_string($bt->remote_name,$cvname);
|
|
| 144 |
|
|
| 145 |
$open_invoice->{agreement} -= 1 if $datediff < -5; # dies hebelt eventuell Vorkasse aus
|
|
| 146 |
$open_invoice->{agreement} += 1 if $datediff < 30; # dies hebelt eventuell Vorkasse aus
|
|
| 147 |
|
|
| 148 |
# only if we already have a good agreement, let date further change value of agreement. |
|
| 149 |
# this is so that if there are several open invoices which are all equal (rent jan, rent feb...) the one with the best date match is chose over the others |
|
| 150 |
# another way around this is to just pre-filter by periods instead of matching everything |
|
| 151 |
if ( $open_invoice->{agreement} > 5 ) {
|
|
| 152 |
if ( $datediff == 0 ) {
|
|
| 153 |
$open_invoice->{agreement} += 3;
|
|
| 154 |
} elsif ( $datediff > 0 and $datediff <= 14 ) {
|
|
| 155 |
$open_invoice->{agreement} += 2;
|
|
| 156 |
} elsif ( $datediff >14 and $datediff < 35) {
|
|
| 157 |
$open_invoice->{agreement} += 1;
|
|
| 158 |
} elsif ( $datediff >34 and $datediff < 120) {
|
|
| 159 |
$open_invoice->{agreement} += 1;
|
|
| 160 |
} elsif ( $datediff < 0 ) {
|
|
| 161 |
$open_invoice->{agreement} -= 1;
|
|
| 162 |
} else {
|
|
| 163 |
# e.g. datediff > 120 |
|
| 164 |
}; |
|
| 165 |
}; |
|
| 106 |
$bt->{remote_name} .= $bt->{remote_name_1} if $bt->{remote_name_1};
|
|
| 107 |
|
|
| 108 |
# try to match the current $bt to each of the open_invoices, saving the |
|
| 109 |
# results of get_agreement_with_invoice in $open_invoice->{agreement} and
|
|
| 110 |
# $open_invoice->{rule_matches}.
|
|
| 111 |
|
|
| 112 |
# The values are overwritten each time a new bt is checked, so at the end |
|
| 113 |
# of each bt the likely results are filtered and those values are stored in |
|
| 114 |
# the arrays $bt->{proposals} and $bt->{rule_matches}, and the agreement
|
|
| 115 |
# score is stored in $bt->{agreement}
|
|
| 166 | 116 |
|
| 167 |
#if ($open_invoice->transdate->{utc_rd_days} == $bt->transdate->{utc_rd_days}) {
|
|
| 168 |
#$open_invoice->{agreement} += 4;
|
|
| 169 |
#print FH "found matching date for invoice " . $open_invoice->invnumber . " ( " . $bt->transdate->{utc_rd_days} . " . \n";
|
|
| 170 |
#} elsif (($open_invoice->transdate->{utc_rd_days} + 30) < $bt->transdate->{utc_rd_days}) {
|
|
| 171 |
#$open_invoice->{agreement} -= 1;
|
|
| 172 |
#} else {
|
|
| 173 |
#$open_invoice->{agreement} -= 2;
|
|
| 174 |
#print FH "found nomatch date -2 for invoice " . $open_invoice->invnumber . " ( " . $bt->transdate->{utc_rd_days} . " . \n";
|
|
| 175 |
#}; |
|
| 176 |
#print FH "agreement after date_agreement: " . $open_invoice->{agreement} . "\n";
|
|
| 117 |
foreach my $open_invoice (@all_open_invoices){
|
|
| 118 |
($open_invoice->{agreement}, $open_invoice->{rule_matches}) = $bt->get_agreement_with_invoice($open_invoice);
|
|
| 119 |
}; |
|
| 177 | 120 |
|
| 121 |
$bt->{proposals} = [];
|
|
| 178 | 122 |
|
| 123 |
my $agreement = 15; |
|
| 124 |
my $min_agreement = 3; # suggestions must have at least this score |
|
| 179 | 125 |
|
| 180 |
} |
|
| 181 |
# finished going through all open_invoices |
|
| 126 |
my $max_agreement = max map { $_->{agreement} } @all_open_invoices;
|
|
| 182 | 127 |
|
| 183 |
# go through each bt
|
|
| 184 |
# for each open_invoice try to match it to each open_invoice and store agreement in $open_invoice->{agreement} (which gets overwritten each time for each bt)
|
|
| 185 |
# calculate
|
|
| 186 |
#
|
|
| 128 |
# add open_invoices with highest agreement into array $bt->{proposals}
|
|
| 129 |
if ( $max_agreement >= $min_agreement ) {
|
|
| 130 |
$bt->{proposals} = [ grep { $_->{agreement} == $max_agreement } @all_open_invoices ];
|
|
| 131 |
$bt->{agreement} = $max_agreement; #scalar @{ $bt->{proposals} } ? $agreement + 1 : '';
|
|
| 187 | 132 |
|
| 188 |
$bt->{proposals} = [];
|
|
| 189 |
my $agreement = 11; |
|
| 190 |
# wird nie ausgeführt, bzw. nur ganz am Ende |
|
| 191 |
# oder einmal am Anfang? |
|
| 192 |
# es werden maximal 7 vorschläge gemacht? |
|
| 193 |
# 7 mal wird geprüft, ob etwas passt |
|
| 194 |
while (scalar @{ $bt->{proposals} } < 1 && $agreement-- > 0) {
|
|
| 195 |
$bt->{proposals} = [ grep { $_->{agreement} > $agreement } @all_open_invoices ];
|
|
| 196 |
#Kann wahrscheinlich weg: |
|
| 197 |
# map { $_->{style} = "green" } @{ $bt->{proposals} } if $agreement >= 5;
|
|
| 198 |
# map { $_->{style} = "orange" } @{ $bt->{proposals} } if $agreement < 5 and $agreement >= 3;
|
|
| 199 |
# map { $_->{style} = "red" } @{ $bt->{proposals} } if $agreement < 3;
|
|
| 200 |
$bt->{agreement} = $agreement; # agreement value at cutoff, will correspond to several results if threshold is 7 and several are already above 7
|
|
| 201 |
} |
|
| 133 |
# store the rule_matches in a separate array, so they can be displayed in template |
|
| 134 |
foreach ( @{ $bt->{proposals} } ) {
|
|
| 135 |
push(@{$bt->{rule_matches}}, $_->{rule_matches});
|
|
| 136 |
}; |
|
| 137 |
}; |
|
| 202 | 138 |
} # finished one bt |
| 203 | 139 |
# finished all bt |
| 204 | 140 |
|
| 205 | 141 |
# separate filter for proposals (second tab, agreement >= 5 and exactly one match) |
| 206 | 142 |
# to qualify as a proposal there has to be |
| 207 |
# * agreement >= 5 |
|
| 208 |
# * there must be only one exact match
|
|
| 143 |
# * agreement >= 5 TODO: make threshold configurable in configuration
|
|
| 144 |
# * there must be only one exact match |
|
| 209 | 145 |
# * depending on whether sales or purchase the amount has to have the correct sign (so Gutschriften don't work?) |
| 210 |
|
|
| 211 |
my @proposals = grep { $_->{agreement} >= 5
|
|
| 146 |
my $proposal_threshold = 5; |
|
| 147 |
my @proposals = grep { $_->{agreement} >= $proposal_threshold
|
|
| 212 | 148 |
and 1 == scalar @{ $_->{proposals} }
|
| 213 | 149 |
and (@{ $_->{proposals} }[0]->is_sales ? abs(@{ $_->{proposals} }[0]->amount - $_->amount) < 0.01 : abs(@{ $_->{proposals} }[0]->amount + $_->amount) < 0.01) } @{ $bank_transactions };
|
| 214 | 150 |
|
| 215 |
#Sort bank transactions by quality of proposal
|
|
| 151 |
# sort bank transaction proposals by quality (score) of proposal
|
|
| 216 | 152 |
$bank_transactions = [ sort { $a->{agreement} <=> $b->{agreement} } @{ $bank_transactions } ] if $::form->{sort_by} eq 'proposal' and $::form->{sort_dir} == 1;
|
| 217 | 153 |
$bank_transactions = [ sort { $b->{agreement} <=> $a->{agreement} } @{ $bank_transactions } ] if $::form->{sort_by} eq 'proposal' and $::form->{sort_dir} == 0;
|
| 218 | 154 |
|
| 219 | 155 |
|
| 220 | 156 |
$self->render('bank_transactions/list',
|
| 221 |
title => t8('List of bank transactions'),
|
|
| 157 |
title => t8('Bank transactions MT940'),
|
|
| 222 | 158 |
BANK_TRANSACTIONS => $bank_transactions, |
| 223 | 159 |
PROPOSALS => \@proposals, |
| 224 |
bank_account => SL::DB::Manager::BankAccount->find_by(id => $::form->{filter}{bank_account}) );
|
|
| 160 |
bank_account => $bank_account );
|
|
| 225 | 161 |
} |
| 226 | 162 |
|
| 227 |
sub check_string {
|
|
| 228 |
my $bankstring = shift; |
|
| 229 |
my $namestring = shift; |
|
| 230 |
return 0 unless $bankstring and $namestring; |
|
| 231 |
|
|
| 232 |
my @bankwords = grep(/^\w+$/, split(/\b/,$bankstring)); |
|
| 233 |
|
|
| 234 |
my $match = 0; |
|
| 235 |
foreach my $bankword ( @bankwords ) {
|
|
| 236 |
# only try to match strings with more than 2 characters |
|
| 237 |
next unless length($bankword)>2; |
|
| 238 |
if ( $namestring =~ /\b$bankword\b/i ) {
|
|
| 239 |
$match++; |
|
| 240 |
}; |
|
| 241 |
}; |
|
| 242 |
return $match; |
|
| 243 |
}; |
|
| 244 |
|
|
| 245 | 163 |
sub action_assign_invoice {
|
| 246 | 164 |
my ($self) = @_; |
| 247 | 165 |
|
| ... | ... | |
| 289 | 207 |
); |
| 290 | 208 |
} |
| 291 | 209 |
|
| 210 |
sub action_ajax_payment_suggestion {
|
|
| 211 |
my ($self) = @_; |
|
| 212 |
|
|
| 213 |
# based on a BankTransaction ID and a Invoice or PurchaseInvoice ID passed via $::form, |
|
| 214 |
# create an HTML blob to be used by the js function add_invoices in templates/webpages/bank_transactions/list.html |
|
| 215 |
# and return encoded as JSON |
|
| 216 |
|
|
| 217 |
my $bt = SL::DB::Manager::BankTransaction->find_by( id => $::form->{bt_id} );
|
|
| 218 |
my $invoice = SL::DB::Manager::Invoice->find_by( id => $::form->{prop_id} );
|
|
| 219 |
$invoice = SL::DB::Manager::PurchaseInvoice->find_By( id => $::form->{prop_id} ) unless $invoice;
|
|
| 220 |
|
|
| 221 |
die unless $bt and $invoice; |
|
| 222 |
|
|
| 223 |
my @select_options = $invoice->get_payment_select_options_for_bank_transaction($::form->{bt_id});
|
|
| 224 |
|
|
| 225 |
my $html; |
|
| 226 |
$html .= SL::Presenter->input_tag('invoice_ids.' . $::form->{bt_id} . '[]', $::form->{prop_id} , type => 'hidden');
|
|
| 227 |
$html .= SL::Presenter->escape( $invoice->invnumber ); |
|
| 228 |
$html .= SL::Presenter->select_tag('invoice_skontos.' . $::form->{bt_id} . '[]', \@select_options,
|
|
| 229 |
value_key => 'payment_type', |
|
| 230 |
title_key => 'display' ) if @select_options; |
|
| 231 |
$html .= '<a href=# onclick="delete_invoice(' . $::form->{bt_id} . ',' . $::form->{prop_id} . ');">x</a>';
|
|
| 232 |
$html = SL::Presenter->html_tag('div', $html, id => $::form->{bt_id} . '.' . $::form->{prop_id});
|
|
| 233 |
|
|
| 234 |
$self->render(\ SL::JSON::to_json( { 'html' => $html } ), { layout => 0, type => 'json', process => 0 });
|
|
| 235 |
}; |
|
| 236 |
|
|
| 292 | 237 |
sub action_filter_drafts {
|
| 293 | 238 |
my ($self) = @_; |
| 294 | 239 |
|
| ... | ... | |
| 352 | 297 |
} |
| 353 | 298 |
|
| 354 | 299 |
if ($::form->{transdatefrom}) {
|
| 355 |
my $fromdate = $::locale->parse_date_to_object(\%::myconfig, $::form->{transdatefrom});
|
|
| 356 |
push @where_sale, ('transdate' => { ge => $fromdate});
|
|
| 357 |
push @where_purchase, ('transdate' => { ge => $fromdate});
|
|
| 300 |
my $fromdate = $::locale->parse_date_to_object($::form->{transdatefrom});
|
|
| 301 |
if ( ref($fromdate) eq 'DateTime' ) {
|
|
| 302 |
push @where_sale, ('transdate' => { ge => $fromdate});
|
|
| 303 |
push @where_purchase, ('transdate' => { ge => $fromdate});
|
|
| 304 |
}; |
|
| 358 | 305 |
} |
| 359 | 306 |
|
| 360 | 307 |
if ($::form->{transdateto}) {
|
| 361 |
my $todate = $::locale->parse_date_to_object(\%::myconfig, $::form->{transdateto});
|
|
| 362 |
$todate->add(days => 1); |
|
| 363 |
push @where_sale, ('transdate' => { lt => $todate});
|
|
| 364 |
push @where_purchase, ('transdate' => { lt => $todate});
|
|
| 308 |
my $todate = $::locale->parse_date_to_object($::form->{transdateto});
|
|
| 309 |
if ( ref($todate) eq 'DateTime' ) {
|
|
| 310 |
$todate->add(days => 1); |
|
| 311 |
push @where_sale, ('transdate' => { lt => $todate});
|
|
| 312 |
push @where_purchase, ('transdate' => { lt => $todate});
|
|
| 313 |
}; |
|
| 365 | 314 |
} |
| 366 | 315 |
|
| 367 | 316 |
my $all_open_ar_invoices = SL::DB::Manager::Invoice->get_all(where => \@where_sale, with_objects => 'customer'); |
| 368 | 317 |
my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => \@where_purchase, with_objects => 'vendor'); |
| 369 | 318 |
|
| 370 | 319 |
my @all_open_invoices; |
| 371 |
push @all_open_invoices, @{ $all_open_ar_invoices };
|
|
| 372 |
push @all_open_invoices, @{ $all_open_ap_invoices };
|
|
| 320 |
# filter out subcent differences from ap invoices
|
|
| 321 |
push @all_open_invoices, grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
|
|
| 373 | 322 |
|
| 374 | 323 |
@all_open_invoices = sort { $a->id <=> $b->id } @all_open_invoices;
|
| 375 |
#my $all_open_invoices = SL::DB::Manager::Invoice->get_all(where => \@where); |
|
| 376 | 324 |
|
| 377 | 325 |
my $output = $self->render( |
| 378 | 326 |
'bank_transactions/add_list', |
| ... | ... | |
| 404 | 352 |
sub action_save_invoices {
|
| 405 | 353 |
my ($self) = @_; |
| 406 | 354 |
|
| 407 |
my $invoice_hash = delete $::form->{invoice_ids};
|
|
| 355 |
my $invoice_hash = delete $::form->{invoice_ids}; # each key (the bt line with a bt_id) contains an array of invoice_ids
|
|
| 356 |
my $skonto_hash = delete $::form->{invoice_skontos} || {}; # array containing the payment type, could be empty
|
|
| 408 | 357 |
|
| 409 | 358 |
while ( my ($bt_id, $invoice_ids) = each(%$invoice_hash) ) {
|
| 410 | 359 |
my $bank_transaction = SL::DB::Manager::BankTransaction->find_by(id => $bt_id); |
| ... | ... | |
| 423 | 372 |
return 1; } @invoices if $bank_transaction->amount < 0; |
| 424 | 373 |
|
| 425 | 374 |
foreach my $invoice (@invoices) {
|
| 375 |
my $payment_type; |
|
| 376 |
if ( @$skonto_hash{"$bt_id"} ) {
|
|
| 377 |
$payment_type = shift( @$skonto_hash{"$bt_id"} );
|
|
| 378 |
} else {
|
|
| 379 |
$payment_type = 'without_skonto'; |
|
| 380 |
}; |
|
| 426 | 381 |
if ($amount_of_transaction == 0) {
|
| 427 |
flash('warning', $::locale->text('There are invoices which could not be payed by bank transaction #1 (Account number: #2, bank code: #3)!',
|
|
| 382 |
flash('warning', $::locale->text('There are invoices which could not be paid by bank transaction #1 (Account number: #2, bank code: #3)!',
|
|
| 428 | 383 |
$bank_transaction->purpose, |
| 429 | 384 |
$bank_transaction->remote_account_number, |
| 430 | 385 |
$bank_transaction->remote_bank_code)); |
| ... | ... | |
| 432 | 387 |
} |
| 433 | 388 |
#pay invoice or go to the next bank transaction if the amount is not sufficiently high |
| 434 | 389 |
if ($invoice->amount <= $amount_of_transaction) {
|
| 435 |
$invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id, trans_id => $invoice->id, amount => $invoice->amount, transdate => $bank_transaction->transdate); |
|
| 390 |
$invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id, |
|
| 391 |
trans_id => $invoice->id, |
|
| 392 |
amount => $invoice->amount, |
|
| 393 |
payment_type => $payment_type, |
|
| 394 |
transdate => $bank_transaction->transdate->to_kivitendo); |
|
| 436 | 395 |
if ($invoice->is_sales) {
|
| 437 | 396 |
$amount_of_transaction -= $sign * $invoice->amount; |
| 438 | 397 |
$bank_transaction->invoice_amount($bank_transaction->invoice_amount + $invoice->amount); |
| ... | ... | |
| 441 | 400 |
$bank_transaction->invoice_amount($bank_transaction->invoice_amount - $invoice->amount); |
| 442 | 401 |
} |
| 443 | 402 |
} else {
|
| 444 |
$invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id, trans_id => $invoice->id, amount => $amount_of_transaction, transdate => $bank_transaction->transdate); |
|
| 403 |
$invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id, |
|
| 404 |
trans_id => $invoice->id, |
|
| 405 |
amount => $amount_of_transaction, |
|
| 406 |
payment_type => $payment_type, |
|
| 407 |
transdate => $bank_transaction->transdate->to_kivitendo); |
|
| 445 | 408 |
$bank_transaction->invoice_amount($bank_transaction->amount) if $invoice->is_sales; |
| 446 | 409 |
$bank_transaction->invoice_amount($bank_transaction->amount) if !$invoice->is_sales; |
| 447 | 410 |
$amount_of_transaction = 0; |
| ... | ... | |
| 480 | 443 |
$arap->pay_invoice(chart_id => $bt->local_bank_account->chart_id, |
| 481 | 444 |
trans_id => $arap->id, |
| 482 | 445 |
amount => $arap->amount, |
| 483 |
transdate => $bt->transdate); |
|
| 446 |
transdate => $bt->transdate->to_kivitendo);
|
|
| 484 | 447 |
$arap->save; |
| 485 | 448 |
|
| 486 | 449 |
#create record link |
| ... | ... | |
| 520 | 483 |
my @filter_strings; |
| 521 | 484 |
|
| 522 | 485 |
my @filters = ( |
| 523 |
[ $filter->{"transdate:date::ge"}, $::locale->text('Transdate') . " " . $::locale->text('From Date') ],
|
|
| 524 |
[ $filter->{"transdate:date::le"}, $::locale->text('Transdate') . " " . $::locale->text('To Date') ],
|
|
| 486 |
[ $filter->{"transdate:date::ge"}, $::locale->text('Transdate') . " " . $::locale->text('From Date') ],
|
|
| 487 |
[ $filter->{"transdate:date::le"}, $::locale->text('Transdate') . " " . $::locale->text('To Date') ],
|
|
| 525 | 488 |
[ $filter->{"valutadate:date::ge"}, $::locale->text('Valutadate') . " " . $::locale->text('From Date') ],
|
| 526 | 489 |
[ $filter->{"valutadate:date::le"}, $::locale->text('Valutadate') . " " . $::locale->text('To Date') ],
|
| 527 |
[ $filter->{"amount:number"}, $::locale->text('Amount') ],
|
|
| 528 |
[ $filter->{"bank_account_id:integer"}, $::locale->text('Local bank account') ],
|
|
| 490 |
[ $filter->{"amount:number"}, $::locale->text('Amount') ],
|
|
| 491 |
[ $filter->{"bank_account_id:integer"}, $::locale->text('Local bank account') ],
|
|
| 529 | 492 |
); |
| 530 | 493 |
|
| 531 | 494 |
for (@filters) {
|
| ... | ... | |
| 543 | 506 |
my $report = SL::ReportGenerator->new(\%::myconfig, $::form); |
| 544 | 507 |
$self->{report} = $report;
|
| 545 | 508 |
|
| 546 |
my @columns = qw(transdate valudate remote_name remote_account_number remote_bank_code amount invoice_amount invoices currency purpose local_account_number local_bank_code id); |
|
| 547 |
my @sortable = qw(transdate valudate remote_name remote_account_number remote_bank_code amount purpose local_account_number local_bank_code); |
|
| 509 |
my @columns = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount invoice_amount invoices currency purpose local_account_number local_bank_code id);
|
|
| 510 |
my @sortable = qw(local_bank_name transdate valudate remote_name remote_account_number remote_bank_code amount purpose local_account_number local_bank_code);
|
|
| 548 | 511 |
|
| 549 | 512 |
my %column_defs = ( |
| 550 | 513 |
transdate => { sub => sub { $_[0]->transdate_as_date } },
|
| ... | ... | |
| 561 | 524 |
purpose => { },
|
| 562 | 525 |
local_account_number => { sub => sub { $_[0]->local_bank_account->account_number } },
|
| 563 | 526 |
local_bank_code => { sub => sub { $_[0]->local_bank_account->bank_code } },
|
| 527 |
local_bank_name => { sub => sub { $_[0]->local_bank_account->name } },
|
|
| 564 | 528 |
id => {},
|
| 565 | 529 |
); |
| 566 | 530 |
|
| ... | ... | |
| 577 | 541 |
); |
| 578 | 542 |
$report->set_columns(%column_defs); |
| 579 | 543 |
$report->set_column_order(@columns); |
| 580 |
$report->set_export_options(qw(list filter)); |
|
| 544 |
$report->set_export_options(qw(list_all filter));
|
|
| 581 | 545 |
$report->set_options_from_form; |
| 582 |
$self->models->disable_pagination if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
|
|
| 546 |
$self->models->disable_plugin('paginated') if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
|
|
| 583 | 547 |
$self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable); |
| 584 | 548 |
|
| 585 |
my $bank_accounts = SL::DB::Manager::BankAccount->get_all(); |
|
| 586 |
my $label_sub = sub { t8('#1 - Account number #2, bank code #3, #4', $_[0]->name, $_[0]->account_number, $_[0]->bank_code, $_[0]->bank )};
|
|
| 549 |
my $bank_accounts = SL::DB::Manager::BankAccount->get_all_sorted(); |
|
| 587 | 550 |
|
| 588 | 551 |
$report->set_options( |
| 589 |
raw_top_info_text => $self->render('bank_transactions/report_top', { output => 0 }, BANK_ACCOUNTS => $bank_accounts, label_sub => $label_sub),
|
|
| 552 |
raw_top_info_text => $self->render('bank_transactions/report_top', { output => 0 }, BANK_ACCOUNTS => $bank_accounts),
|
|
| 590 | 553 |
raw_bottom_info_text => $self->render('bank_transactions/report_bottom', { output => 0 }),
|
| 591 | 554 |
); |
| 592 | 555 |
} |
| ... | ... | |
| 599 | 562 |
sorted => {
|
| 600 | 563 |
_default => {
|
| 601 | 564 |
by => 'transdate', |
| 602 |
dir => 1,
|
|
| 565 |
dir => 0, # 1 = ASC, 0 = DESC : default sort is newest at top
|
|
| 603 | 566 |
}, |
| 604 | 567 |
transdate => t8('Transdate'),
|
| 605 | 568 |
remote_name => t8('Remote name'),
|
| ... | ... | |
| 613 | 576 |
purpose => t8('Purpose'),
|
| 614 | 577 |
local_account_number => t8('Local account number'),
|
| 615 | 578 |
local_bank_code => t8('Local bank code'),
|
| 579 |
local_bank_name => t8('Bank account'),
|
|
| 616 | 580 |
}, |
| 617 | 581 |
with_objects => [ 'local_bank_account', 'currency' ], |
| 618 | 582 |
); |
| SL/Controller/CsvImport.pm | ||
|---|---|---|
| 224 | 224 |
sub check_type {
|
| 225 | 225 |
my ($self) = @_; |
| 226 | 226 |
|
| 227 |
die "Invalid CSV import type" if none { $_ eq $::form->{profile}->{type} } qw(parts inventories customers_vendors addresses contacts projects orders);
|
|
| 227 |
die "Invalid CSV import type" if none { $_ eq $::form->{profile}->{type} } qw(parts inventories customers_vendors addresses contacts projects orders bank_transactions mt940);
|
|
| 228 | 228 |
$self->type($::form->{profile}->{type});
|
| 229 | 229 |
} |
| 230 | 230 |
|
| ... | ... | |
| 292 | 292 |
|
| 293 | 293 |
$self->profile_from_form; |
| 294 | 294 |
|
| 295 |
|
|
| 296 | 295 |
if ( $::form->{file} && $::form->{FILENAME} =~ /\.940$/ ) {
|
| 297 | 296 |
my $mt940_file = SL::SessionFile->new($::form->{FILENAME}, mode => '>');
|
| 298 | 297 |
$mt940_file->fh->print($::form->{file});
|
| 299 | 298 |
$mt940_file->fh->close; |
| 300 | 299 |
|
| 301 |
my $aqbin = '/usr/bin/aqbanking-cli'; |
|
| 300 |
my $aqbin = $::lx_office_conf{applications}->{aqbanking};
|
|
| 301 |
die "Can't find aqbanking-cli, please check your configuration file.\n" unless -f $aqbin; |
|
| 302 | 302 |
my $cmd = "$aqbin --cfgdir=\"users\" import --importer=\"swift\" --profile=\"SWIFT-MT940\" -f " . $mt940_file->file_name . " | $aqbin --cfgdir=\"users\" listtrans --exporter=\"csv\" --profile=\"AqMoney2\" "; |
| 303 | 303 |
my $converted_mt940; |
| 304 | 304 |
open(MT, "$cmd |"); |
| SL/Controller/CsvImport/BankTransaction.pm | ||
|---|---|---|
| 12 | 12 |
|
| 13 | 13 |
use Rose::Object::MakeMethods::Generic |
| 14 | 14 |
( |
| 15 |
'scalar --get_set_init' => [ qw(table bank_accounts_by) ],
|
|
| 15 |
'scalar --get_set_init' => [ qw(bank_accounts_by) ], |
|
| 16 | 16 |
); |
| 17 | 17 |
|
| 18 | 18 |
sub init_class {
|
| ... | ... | |
| 23 | 23 |
sub init_bank_accounts_by {
|
| 24 | 24 |
my ($self) = @_; |
| 25 | 25 |
|
| 26 |
return { map { my $col = $_; ( $col => { map { ( $_->$col => $_ ) } @{ $self->all_bank_accounts } } ) } qw(id account_number) };
|
|
| 26 |
return { map { my $col = $_; ( $col => { map { ( $_->$col => $_ ) } @{ $self->all_bank_accounts } } ) } qw(id account_number iban) };
|
|
| 27 | 27 |
} |
| 28 | 28 |
|
| 29 | 29 |
sub check_objects {
|
| 30 | 30 |
my ($self) = @_; |
| 31 | 31 |
|
| 32 | 32 |
$self->controller->track_progress(phase => 'building data', progress => 0); |
| 33 |
my $update_policy = $self->controller->profile->get('update_policy') || 'skip';
|
|
| 33 | 34 |
|
| 34 | 35 |
my $i; |
| 35 | 36 |
my $num_data = scalar @{ $self->controller->data };
|
| ... | ... | |
| 38 | 39 |
|
| 39 | 40 |
$self->check_bank_account($entry); |
| 40 | 41 |
$self->check_currency($entry, take_default => 1); |
| 41 |
|
|
| 42 | 42 |
$self->join_purposes($entry); |
| 43 |
#TODO: adde checks für die Variablen |
|
| 43 |
$self->join_remote_names($entry); |
|
| 44 |
$self->check_existing($entry) unless @{ $entry->{errors} };
|
|
| 44 | 45 |
} continue {
|
| 45 | 46 |
$i++; |
| 46 | 47 |
} |
| 47 | 48 |
|
| 48 |
$self->add_cvar_raw_data_columns; |
|
| 49 |
$self->add_info_columns({ header => $::locale->text('Bank account'), method => 'local_bank_name' });
|
|
| 50 |
} |
|
| 51 |
|
|
| 52 |
sub check_existing {
|
|
| 53 |
my ($self, $entry) = @_; |
|
| 54 |
|
|
| 55 |
my $object = $entry->{object};
|
|
| 56 |
|
|
| 57 |
# for each imported entry (line) we make a database call to find existing entries |
|
| 58 |
# we don't use the init_by hash because we have to check several fields |
|
| 59 |
# this means that we can't detect duplicates in the import file |
|
| 60 |
|
|
| 61 |
if ( $object->amount ) {
|
|
| 62 |
# check for same |
|
| 63 |
# * purpose |
|
| 64 |
# * transdate |
|
| 65 |
# * remote_account_number (may be empty for records of our own bank) |
|
| 66 |
# * amount |
|
| 67 |
my $num; |
|
| 68 |
if ( $num = SL::DB::Manager::BankTransaction->get_all_count(query =>[ remote_account_number => $object->remote_account_number, transdate => $object->transdate, purpose => $object->purpose, amount => $object->amount] ) ) {
|
|
| 69 |
push(@{$entry->{errors}}, $::locale->text('Skipping due to existing bank transaction in database'));
|
|
| 70 |
}; |
|
| 71 |
} else {
|
|
| 72 |
push(@{$entry->{errors}}, $::locale->text('Skipping because transfer amount is empty.'));
|
|
| 73 |
}; |
|
| 49 | 74 |
} |
| 50 | 75 |
|
| 51 | 76 |
sub setup_displayable_columns {
|
| ... | ... | |
| 53 | 78 |
|
| 54 | 79 |
$self->SUPER::setup_displayable_columns; |
| 55 | 80 |
|
| 56 |
$self->add_displayable_columns({ name => 'transaction_id', description => $::locale->text('Transaction ID') },
|
|
| 57 |
{ name => 'local_bank_code', description => $::locale->text('Own bank code') },
|
|
| 58 |
{ name => 'local_account_number', description => $::locale->text('Own bank account number') },
|
|
| 59 |
{ name => 'local_bank_account_id', description => $::locale->text('ID of own bank account') },
|
|
| 60 |
{ name => 'remote_bank_code', description => $::locale->text('Bank code of the goal/source') },
|
|
| 61 |
{ name => 'remote_account_number', description => $::locale->text('Account number of the goal/source') },
|
|
| 62 |
{ name => 'transdate', description => $::locale->text('Date of transaction') },
|
|
| 63 |
{ name => 'valutadate', description => $::locale->text('Valuta') },
|
|
| 64 |
{ name => 'amount', description => $::locale->text('Amount') },
|
|
| 65 |
{ name => 'currency', description => $::locale->text('Currency') },
|
|
| 66 |
{ name => 'currency_id', description => $::locale->text('Currency (database ID)') },
|
|
| 67 |
{ name => 'remote_name', description => $::locale->text('Name of the goal/source') },
|
|
| 68 |
{ name => 'remote_name_1', description => $::locale->text('Name of the goal/source') },
|
|
| 69 |
{ name => 'purpose', description => $::locale->text('Purpose') },
|
|
| 70 |
); |
|
| 81 |
# TODO: don't show fields cleared, invoice_amount and transaction_id in the help text, as these should not be imported |
|
| 82 |
$self->add_displayable_columns({ name => 'local_bank_code', description => $::locale->text('Own bank code') },
|
|
| 83 |
{ name => 'local_account_number', description => $::locale->text('Own bank account number or IBAN') },
|
|
| 84 |
{ name => 'local_bank_account_id', description => $::locale->text('ID of own bank account') },
|
|
| 85 |
{ name => 'remote_bank_code', description => $::locale->text('Bank code of the goal/source') },
|
|
| 86 |
{ name => 'remote_account_number', description => $::locale->text('Account number of the goal/source') },
|
|
| 87 |
{ name => 'transdate', description => $::locale->text('Date of transaction') },
|
|
| 88 |
{ name => 'valutadate', description => $::locale->text('Valuta date') },
|
|
| 89 |
{ name => 'amount', description => $::locale->text('Amount') },
|
|
| 90 |
{ name => 'currency', description => $::locale->text('Currency') },
|
|
| 91 |
{ name => 'currency_id', description => $::locale->text('Currency (database ID)') },
|
|
| 92 |
{ name => 'remote_name', description => $::locale->text('Name of the goal/source (if field names remote_name and remote_name_1 exist they will be combined into field "remote_name")') },
|
|
| 93 |
{ name => 'purpose', description => $::locale->text('Purpose (if field names purpose, purpose1, purpose2 ... exist they will all combined into the field "purpose")') },
|
|
| 94 |
); |
|
| 71 | 95 |
} |
| 72 | 96 |
|
| 73 | 97 |
sub check_bank_account {
|
| ... | ... | |
| 75 | 99 |
|
| 76 | 100 |
my $object = $entry->{object};
|
| 77 | 101 |
|
| 78 |
# Check whether or not local_bank_account ID is valid. |
|
| 102 |
# Check whether or not local_bank_account ID exists and is valid.
|
|
| 79 | 103 |
if ($object->local_bank_account_id && !$self->bank_accounts_by->{id}->{ $object->local_bank_account_id }) {
|
| 80 | 104 |
push @{ $entry->{errors} }, $::locale->text('Error: Invalid local bank account');
|
| 81 | 105 |
return 0; |
| ... | ... | |
| 95 | 119 |
|
| 96 | 120 |
} |
| 97 | 121 |
|
| 98 |
# Map account information to ID if given. |
|
| 122 |
# Map account information to ID via local_account_number if no local_bank_account_id was given |
|
| 123 |
# local_account_number checks for match of account number or IBAN |
|
| 99 | 124 |
if (!$object->local_bank_account_id && $entry->{raw_data}->{local_account_number}) {
|
| 100 | 125 |
my $bank_account = $self->bank_accounts_by->{account_number}->{ $entry->{raw_data}->{local_account_number} };
|
| 126 |
if (!$bank_account) {
|
|
| 127 |
$bank_account = $self->bank_accounts_by->{iban}->{ $entry->{raw_data}->{local_account_number} };
|
|
| 128 |
}; |
|
| 101 | 129 |
if (!$bank_account) {
|
| 102 | 130 |
push @{ $entry->{errors} }, $::locale->text('Error: Invalid local bank account');
|
| 103 | 131 |
return 0; |
| ... | ... | |
| 108 | 136 |
} |
| 109 | 137 |
|
| 110 | 138 |
$object->local_bank_account_id($bank_account->id); |
| 139 |
$entry->{info_data}->{local_bank_name} = $bank_account->name;
|
|
| 111 | 140 |
} |
| 112 | 141 |
|
| 113 | 142 |
return $object->local_bank_account_id ? 1 : 0; |
| ... | ... | |
| 119 | 148 |
my $object = $entry->{object};
|
| 120 | 149 |
|
| 121 | 150 |
my $purpose = join('', $entry->{raw_data}->{purpose},
|
| 122 |
$entry->{raw_data}->{purpose1},
|
|
| 123 |
$entry->{raw_data}->{purpose2},
|
|
| 124 |
$entry->{raw_data}->{purpose3},
|
|
| 125 |
$entry->{raw_data}->{purpose4},
|
|
| 126 |
$entry->{raw_data}->{purpose5},
|
|
| 127 |
$entry->{raw_data}->{purpose6},
|
|
| 128 |
$entry->{raw_data}->{purpose7},
|
|
| 129 |
$entry->{raw_data}->{purpose8},
|
|
| 130 |
$entry->{raw_data}->{purpose9},
|
|
| 131 |
$entry->{raw_data}->{purpose10},
|
|
| 132 |
$entry->{raw_data}->{purpose11} );
|
|
| 151 |
$entry->{raw_data}->{purpose1},
|
|
| 152 |
$entry->{raw_data}->{purpose2},
|
|
| 153 |
$entry->{raw_data}->{purpose3},
|
|
| 154 |
$entry->{raw_data}->{purpose4},
|
|
| 155 |
$entry->{raw_data}->{purpose5},
|
|
| 156 |
$entry->{raw_data}->{purpose6},
|
|
| 157 |
$entry->{raw_data}->{purpose7},
|
|
| 158 |
$entry->{raw_data}->{purpose8},
|
|
| 159 |
$entry->{raw_data}->{purpose9},
|
|
| 160 |
$entry->{raw_data}->{purpose10},
|
|
| 161 |
$entry->{raw_data}->{purpose11} );
|
|
| 133 | 162 |
$object->purpose($purpose); |
| 163 |
|
|
| 164 |
} |
|
| 165 |
|
|
| 166 |
sub join_remote_names {
|
|
| 167 |
my ($self, $entry) = @_; |
|
| 168 |
|
|
| 169 |
my $object = $entry->{object};
|
|
| 170 |
|
|
| 171 |
my $remote_name = join('', $entry->{raw_data}->{remote_name},
|
|
| 172 |
$entry->{raw_data}->{remote_name_1} );
|
|
| 173 |
$object->remote_name($remote_name); |
|
| 134 | 174 |
} |
| 135 | 175 |
|
| 136 | 176 |
1; |
| SL/Controller/CsvImport/Base.pm | ||
|---|---|---|
| 146 | 146 |
sub init_all_bank_accounts {
|
| 147 | 147 |
my ($self) = @_; |
| 148 | 148 |
|
| 149 |
return SL::DB::Manager::BankAccount->get_all; |
|
| 149 |
return SL::DB::Manager::BankAccount->get_all_sorted( query => [ obsolete => 0 ] );
|
|
| 150 | 150 |
} |
| 151 | 151 |
|
| 152 | 152 |
sub init_payment_terms_by {
|
| SL/Controller/Reconciliation.pm | ||
|---|---|---|
| 10 | 10 |
use SL::Helper::Flash; |
| 11 | 11 |
|
| 12 | 12 |
use SL::DB::BankTransaction; |
| 13 |
use SL::DB::BankAccount; |
|
| 13 |
use SL::DB::Manager::BankAccount;
|
|
| 14 | 14 |
use SL::DB::AccTransaction; |
| 15 | 15 |
use SL::DB::ReconciliationLink; |
| 16 |
use List::Util qw(sum); |
|
| 16 | 17 |
|
| 17 | 18 |
use Rose::Object::MakeMethods::Generic ( |
| 18 | 19 |
'scalar --get_set_init' => [ qw(cleared BANK_ACCOUNTS) ], |
| ... | ... | |
| 28 | 29 |
sub action_search {
|
| 29 | 30 |
my ($self) = @_; |
| 30 | 31 |
|
| 31 |
$self->render('reconciliation/search',
|
|
| 32 |
label_sub => sub { t8('#1 - Account number #2, bank code #3, #4',
|
|
| 33 |
$_[0]->name, |
|
| 34 |
$_[0]->bank, |
|
| 35 |
$_[0]->account_number, |
|
| 36 |
$_[0]->bank_code) }); |
|
| 32 |
$self->render('reconciliation/search');
|
|
| 37 | 33 |
} |
| 38 | 34 |
|
| 39 | 35 |
sub action_reconciliation {
|
| ... | ... | |
| 44 | 40 |
$self->_get_balances; |
| 45 | 41 |
|
| 46 | 42 |
$self->render('reconciliation/form',
|
| 47 |
title => t8('Reconciliation'),
|
|
| 48 |
label_sub => sub { t8('#1 - Account number #2, bank code #3, #4',
|
|
| 49 |
$_[0]->name, |
|
| 50 |
$_[0]->bank, |
|
| 51 |
$_[0]->account_number, |
|
| 52 |
$_[0]->bank_code) }); |
|
| 43 |
title => t8('Reconciliation'));
|
|
| 53 | 44 |
} |
| 54 | 45 |
|
| 55 | 46 |
sub action_load_overview {
|
| ... | ... | |
| 74 | 65 |
$self->_get_balances; |
| 75 | 66 |
|
| 76 | 67 |
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),
|
|
| 68 |
my %result = ( html => $output,
|
|
| 69 |
absolut_bt_balance => $::form->format_amount(\%::myconfig, $self->{absolut_bt_balance}, 2),
|
|
| 79 | 70 |
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)
|
|
| 71 |
bt_balance => $::form->format_amount(\%::myconfig, $self->{bt_balance}, 2),
|
|
| 72 |
bb_balance => $::form->format_amount(\%::myconfig, -1 * $self->{bb_balance}, 2)
|
|
| 82 | 73 |
); |
| 83 | 74 |
|
| 84 | 75 |
$self->render(\to_json(\%result), { type => 'json', process => 0 });
|
| ... | ... | |
| 100 | 91 |
$self->render(\to_json(\%result), { type => 'json', process => 0 });
|
| 101 | 92 |
} |
| 102 | 93 |
|
| 103 |
sub action_reconciliate {
|
|
| 94 |
sub action_reconcile {
|
|
| 104 | 95 |
my ($self) = @_; |
| 105 | 96 |
|
| 106 | 97 |
#Check elements |
| 107 | 98 |
my @errors = $self->_get_elements_and_validate; |
| 108 | 99 |
|
| 109 | 100 |
if (@errors) {
|
| 110 |
unshift(@errors, (t8('Could not reconciliate chosen elements!')));
|
|
| 101 |
unshift(@errors, (t8('Could not reconcile chosen elements!')));
|
|
| 111 | 102 |
flash('error', @errors);
|
| 112 | 103 |
$self->action_reconciliation; |
| 113 | 104 |
return; |
| 114 | 105 |
} |
| 115 | 106 |
|
| 116 |
$self->_reconciliate;
|
|
| 107 |
$self->_reconcile; |
|
| 117 | 108 |
|
| 118 | 109 |
$self->action_reconciliation; |
| 119 | 110 |
} |
| ... | ... | |
| 140 | 131 |
$self->_get_balances; |
| 141 | 132 |
|
| 142 | 133 |
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)
|
|
| 134 |
my %result = ( html => $output,
|
|
| 135 |
absolut_bt_balance => $::form->format_amount(\%::myconfig, $self ->{absolut_bt_balance}, 2),
|
|
| 136 |
absolut_bb_balance => $::form->format_amount(\%::myconfig, -1 * $self ->{absolut_bb_balance}, 2),
|
|
| 137 |
bt_balance => $::form->format_amount(\%::myconfig, $self ->{bt_balance}, 2),
|
|
| 138 |
bb_balance => $::form->format_amount(\%::myconfig, -1 * $self ->{bb_balance}, 2)
|
|
| 148 | 139 |
); |
| 149 | 140 |
|
| 150 | 141 |
$self->render(\to_json(\%result), { type => 'json', process => 0 });
|
| ... | ... | |
| 168 | 159 |
$self->_get_proposals; |
| 169 | 160 |
|
| 170 | 161 |
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)
|
|
| 162 |
my %result = ( html => $output,
|
|
| 163 |
absolut_bt_balance => $::form->format_amount(\%::myconfig, $self ->{absolut_bt_balance}, 2),
|
|
| 164 |
absolut_bb_balance => $::form->format_amount(\%::myconfig, -1 * $self ->{absolut_bb_balance}, 2),
|
|
| 165 |
bt_balance => $::form->format_amount(\%::myconfig, $self ->{bt_balance}, 2),
|
|
| 166 |
bb_balance => $::form->format_amount(\%::myconfig, -1 * $self ->{bb_balance}, 2)
|
|
| 176 | 167 |
); |
| 177 | 168 |
|
| 178 | 169 |
$self->render(\to_json(\%result), { type => 'json', process => 0 });
|
| 179 | 170 |
} |
| 180 | 171 |
|
| 181 |
sub action_reconciliate_proposals {
|
|
| 172 |
sub action_reconcile_proposals {
|
|
| 182 | 173 |
my ($self) = @_; |
| 183 | 174 |
|
| 184 | 175 |
my $counter = 0; |
| ... | ... | |
| 229 | 220 |
sub _get_proposals {
|
| 230 | 221 |
my ($self) = @_; |
| 231 | 222 |
|
| 223 |
# reconciliation suggestion is based on: |
|
| 224 |
# * record_link exists (was paid by bank transaction) |
|
| 225 |
# or acc_trans entry exists where |
|
| 226 |
# * amount is exactly the same |
|
| 227 |
# * date is the same |
|
| 228 |
# * IBAN or account number have to match exactly (cv details, no spaces) |
|
| 229 |
# * not a gl storno |
|
| 230 |
# * there is exactly one match for all conditions |
|
| 231 |
|
|
| 232 | 232 |
$self->_filter_to_where; |
| 233 | 233 |
|
| 234 | 234 |
my $bank_transactions = SL::DB::Manager::BankTransaction->get_all(where => [ @{ $self->{bt_where} }, cleared => '0' ]);
|
| ... | ... | |
| 243 | 243 |
$proposal->{BT} = $bt;
|
| 244 | 244 |
$proposal->{BB} = [];
|
| 245 | 245 |
|
| 246 |
# first of all check if any of the bank_transactions are already linked (i.e. were paid via bank transactions) |
|
| 246 | 247 |
my $linked_records = SL::DB::Manager::RecordLink->get_all(where => [ from_table => 'bank_transactions', from_id => $bt->id ]); |
| 247 | 248 |
foreach my $linked_record (@{ $linked_records }) {
|
| 248 | 249 |
my $invoice; |
| ... | ... | |
| 268 | 269 |
|
| 269 | 270 |
#add proposal if something in acc_trans was found |
| 270 | 271 |
#otherwise try to find another entry in acc_trans and add it |
| 271 |
if (scalar @{ $proposal->{BB} } and !$check_sum) {
|
|
| 272 |
# for linked_records we allow a slight difference / imprecision, for acc_trans search we don't |
|
| 273 |
if (scalar @{ $proposal->{BB} } and abs($check_sum) <= 0.01 ) {
|
|
| 272 | 274 |
push @proposals, $proposal; |
| 273 | 275 |
} elsif (!scalar @{ $proposal->{BB} }) {
|
| 276 |
# use account_number and iban for matching remote account number |
|
| 277 |
# don't suggest gl stornos (ar and ap stornos shouldn't have any payments) |
|
| 278 |
|
|
| 279 |
my @account_number_match = ( |
|
| 280 |
( 'ar.customer.iban' => $bt->remote_account_number ), |
|
| 281 |
( 'ar.customer.account_number' => $bt->remote_account_number ), |
|
| 282 |
( 'ap.vendor.iban' => $bt->remote_account_number ), |
|
| 283 |
( 'ap.vendor.account_number' => $bt->remote_account_number ), |
|
| 284 |
( 'gl.storno' => '0' ), |
|
| 285 |
); |
|
| 286 |
|
|
| 274 | 287 |
my $acc_transactions = SL::DB::Manager::AccTransaction->get_all(where => [ @{ $self->{bb_where} },
|
| 275 | 288 |
amount => -1 * $bt->amount, |
| 276 | 289 |
cleared => '0', |
| 277 |
or => [ |
|
| 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' ]], |
|
| 290 |
'transdate' => $bt->transdate, |
|
| 291 |
or => [ @account_number_match ] |
|
| 292 |
], |
|
| 283 | 293 |
with_objects => [ 'ar', 'ap', 'ar.customer', 'ap.vendor', 'gl' ]); |
| 284 | 294 |
if (scalar @{ $acc_transactions } == 1) {
|
| 285 | 295 |
push @{ $proposal->{BB} }, @{ $acc_transactions }[0];
|
| ... | ... | |
| 339 | 349 |
return @errors; |
| 340 | 350 |
} |
| 341 | 351 |
|
| 342 |
sub _reconciliate {
|
|
| 352 |
sub _reconcile {
|
|
| 343 | 353 |
my ($self) = @_; |
| 344 | 354 |
|
| 345 |
#1. Step: Set AccTrans and BankTransactions to 'cleared'
|
|
| 355 |
# 1. step: set AccTrans and BankTransactions to 'cleared'
|
|
| 346 | 356 |
foreach my $element (@{ $self->{ELEMENTS} }) {
|
| 347 | 357 |
$element->cleared('1');
|
| 348 | 358 |
$element->invoice_amount($element->amount) if $element->isa('SL::DB::BankTransaction');
|
| 349 | 359 |
$element->save; |
| 350 | 360 |
} |
| 351 | 361 |
|
| 352 |
#2. Step: Insert entry in reconciliation_links
|
|
| 362 |
# 2. step: insert entry in reconciliation_links
|
|
| 353 | 363 |
my $rec_group = SL::DB::Manager::ReconciliationLink->get_new_rec_group(); |
| 354 | 364 |
#There is either a 1:n relation or a n:1 relation |
| 355 | 365 |
if (scalar @{ $::form->{bt_ids} } == 1) {
|
| ... | ... | |
| 378 | 388 |
my %filter = @{ $parse_filter{query} };
|
| 379 | 389 |
|
| 380 | 390 |
my (@rl_where, @bt_where, @bb_where); |
| 381 |
@rl_where = ('bank_transaction.local_bank_account_id' => $filter{local_bank_account_id});
|
|
| 391 |
@rl_where = ('bank_transaction.local_bank_account_id' => $filter{local_bank_account_id});
|
|
| 382 | 392 |
@bt_where = (local_bank_account_id => $filter{local_bank_account_id});
|
| 383 | 393 |
@bb_where = (chart_id => $self->{bank_account}->chart_id);
|
| 384 | 394 |
|
| 385 | 395 |
if ($filter{fromdate} and $filter{todate}) {
|
| 386 | 396 |
|
| 387 |
push @rl_where, (or => [ and => [ 'acc_tran.transdate' => $filter{fromdate},
|
|
| 388 |
'acc_tran.transdate' => $filter{todate} ],
|
|
| 397 |
push @rl_where, (or => [ and => [ 'acc_trans.transdate' => $filter{fromdate},
|
|
| 398 |
'acc_trans.transdate' => $filter{todate} ],
|
|
| 389 | 399 |
and => [ 'bank_transaction.transdate' => $filter{fromdate},
|
| 390 |
'bank_transaction.transdate' => $filter{todate} ] ] );
|
|
| 400 |
'bank_transaction.transdate' => $filter{todate} ] ] );
|
|
| 391 | 401 |
|
| 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} );
|
|
| 402 |
push @bt_where, (transdate => $filter{todate} );
|
|
| 403 |
push @bt_where, (transdate => $filter{fromdate} );
|
|
| 404 |
push @bb_where, (transdate => $filter{todate} );
|
|
| 405 |
push @bb_where, (transdate => $filter{fromdate} );
|
|
| 406 |
} |
|
| 407 |
|
|
| 408 |
if ( $self->{bank_account}->reconciliation_starting_date ) {
|
|
| 409 |
push @bt_where, (transdate => { gt => $self->{bank_account}->reconciliation_starting_date });
|
|
| 410 |
push @bb_where, (transdate => { gt => $self->{bank_account}->reconciliation_starting_date });
|
|
| 396 | 411 |
} |
| 397 | 412 |
|
| 413 |
# don't try to reconcile opening and closing balance transactions |
|
| 414 |
push @bb_where, ('acc_trans.ob_transaction' => 0);
|
|
| 415 |
push @bb_where, ('acc_trans.cb_transaction' => 0);
|
|
| 416 |
|
|
| 398 | 417 |
if ($filter{fromdate} and not $filter{todate}) {
|
| 399 |
push @rl_where, (or => [ 'acc_tran.transdate' => $filter{fromdate},
|
|
| 418 |
push @rl_where, (or => [ 'acc_trans.transdate' => $filter{fromdate},
|
|
| 400 | 419 |
'bank_transaction.transdate' => $filter{fromdate} ] );
|
| 401 | 420 |
push @bt_where, (transdate => $filter{fromdate} );
|
| 402 | 421 |
push @bb_where, (transdate => $filter{fromdate} );
|
| 403 | 422 |
} |
| 404 | 423 |
|
| 405 | 424 |
if ($filter{todate} and not $filter{fromdate}) {
|
| 406 |
push @rl_where, ( or => [ 'acc_tran.transdate' => $filter{todate} ,
|
|
| 425 |
push @rl_where, ( or => [ 'acc_trans.transdate' => $filter{todate} ,
|
|
| 407 | 426 |
'bank_transaction.transdate' => $filter{todate} ] );
|
| 408 | 427 |
push @bt_where, (transdate => $filter{todate} );
|
| 409 | 428 |
push @bb_where, (transdate => $filter{todate} );
|
| ... | ... | |
| 411 | 430 |
|
| 412 | 431 |
if ($filter{cleared}) {
|
| 413 | 432 |
$filter{cleared} = $filter{cleared} eq 'FALSE' ? '0' : '1';
|
| 414 |
push @rl_where, ('acc_tran.cleared' => $filter{cleared} );
|
|
| 433 |
push @rl_where, ('acc_trans.cleared' => $filter{cleared} );
|
|
| 415 | 434 |
|
| 416 | 435 |
push @bt_where, (cleared => $filter{cleared} );
|
| 417 | 436 |
push @bb_where, (cleared => $filter{cleared} );
|
| ... | ... | |
| 428 | 447 |
$self->_filter_to_where; |
| 429 | 448 |
|
| 430 | 449 |
my (@where, @bt_where, @bb_where); |
| 450 |
# don't try to reconcile opening and closing balances |
|
| 451 |
# instead use an offset in configuration |
|
| 452 |
|
|
| 431 | 453 |
@where = (@{ $self->{rl_where} });
|
| 432 | 454 |
@bt_where = (@{ $self->{bt_where} }, cleared => '0');
|
| 433 | 455 |
@bb_where = (@{ $self->{bb_where} }, cleared => '0');
|
| ... | ... | |
| 437 | 459 |
my $reconciliation_groups = SL::DB::Manager::ReconciliationLink->get_all(distinct => 1, |
| 438 | 460 |
select => ['rec_group'], |
| 439 | 461 |
where => \@where, |
| 440 |
with_objects => ['bank_transaction', 'acc_tran']); |
|
| 462 |
with_objects => ['bank_transaction', 'acc_trans']);
|
|
| 441 | 463 |
|
| 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});
|
|
| 464 |
my $fromdate = $::locale->parse_date_to_object($::form->{filter}->{fromdate_date__ge});
|
|
| 465 |
my $todate = $::locale->parse_date_to_object($::form->{filter}->{todate_date__le});
|
|
| 444 | 466 |
|
| 445 | 467 |
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']); |
|
| 468 |
my $linked_transactions = SL::DB::Manager::ReconciliationLink->get_all(where => [rec_group => $rec_group->rec_group], with_objects => ['bank_transaction', 'acc_trans']);
|
|
| 447 | 469 |
my $line; |
| 448 | 470 |
my $first_transaction = shift @{ $linked_transactions };
|
| 449 | 471 |
my $first_bt = $first_transaction->bank_transaction; |
| 450 |
my $first_bb = $first_transaction->acc_tran; |
|
| 472 |
my $first_bb = $first_transaction->acc_trans;
|
|
| 451 | 473 |
|
| 452 | 474 |
if (defined $fromdate) {
|
| 453 | 475 |
$first_bt->{class} = 'out_of_balance' if ( $first_bt->transdate lt $fromdate );
|
| ... | ... | |
| 466 | 488 |
my ($previous_bt_id, $previous_acc_trans_id) = ($first_transaction->bank_transaction_id, $first_transaction->acc_trans_id); |
| 467 | 489 |
foreach my $linked_transaction (@{ $linked_transactions }) {
|
| 468 | 490 |
my $bank_transaction = $linked_transaction->bank_transaction; |
| 469 |
my $acc_transaction = $linked_transaction->acc_tran; |
|
| 491 |
my $acc_transaction = $linked_transaction->acc_trans;
|
|
| 470 | 492 |
if (defined $fromdate) {
|
| 471 | 493 |
$bank_transaction->{class} = 'out_of_balance' if ( $bank_transaction->transdate lt $fromdate );
|
| 472 | 494 |
$acc_transaction->{class} = 'out_of_balance' if ( $acc_transaction->transdate lt $fromdate );
|
| ... | ... | |
| 485 | 507 |
push @rows, $line; |
| 486 | 508 |
} |
| 487 | 509 |
|
| 488 |
#add non-cleared bank transactions |
|
| 510 |
# add non-cleared bank transactions
|
|
| 489 | 511 |
my $bank_transactions = SL::DB::Manager::BankTransaction->get_all(where => \@bt_where); |
| 490 | 512 |
foreach my $bt (@{ $bank_transactions }) {
|
| 491 | 513 |
my $line; |
| ... | ... | |
| 495 | 517 |
push @rows, $line; |
| 496 | 518 |
} |
| 497 | 519 |
|
| 498 |
#add non-cleared bookings on bank |
|
| 520 |
# add non-cleared bookings on bank
|
|
| 499 | 521 |
my $bookings_on_bank = SL::DB::Manager::AccTransaction->get_all(where => \@bb_where); |
| 500 | 522 |
foreach my $bb (@{ $bookings_on_bank }) {
|
| 501 |
if ($::form->{filter}->{show_stornos} or !$bb->get_transaction->storno) {
|
|
| 523 |
if ($::form->{filter}->{show_stornos} or !$bb->record->storno) {
|
|
| 502 | 524 |
my $line; |
| 503 | 525 |
$line->{BB} = [ $bb ];
|
| 504 | 526 |
$line->{type} = 'BB';
|
| ... | ... | |
| 539 | 561 |
@bt_where = @{ $self->{bt_where} };
|
| 540 | 562 |
@bb_where = @{ $self->{bb_where} };
|
| 541 | 563 |
|
| 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 ); |
|
| 544 |
|
|
| 545 |
#for absolute balance get all bookings till todate |
|
| 546 |
my $todate = $::locale->parse_date_to_object(\%::myconfig, $::form->{filter}->{todate_date__le});
|
|
| 547 |
|
|
| 548 | 564 |
my @all_bt_where = (local_bank_account_id => $self->{bank_account}->id);
|
| 549 | 565 |
my @all_bb_where = (chart_id => $self->{bank_account}->chart_id);
|
| 550 | 566 |
|
| 567 |
my ($bt_balance, $bb_balance) = (0,0); |
|
| 568 |
my ($absolut_bt_balance, $absolut_bb_balance) = (0,0); |
|
| 569 |
|
|
| 570 |
if ( $self->{bank_account}->reconciliation_starting_date ) {
|
|
| 571 |
$bt_balance = $self->{bank_account}->reconciliation_starting_balance;
|
|
| 572 |
$bb_balance = $self->{bank_account}->reconciliation_starting_balance * -1;
|
|
| 573 |
$absolut_bt_balance = $self->{bank_account}->reconciliation_starting_balance;
|
|
| 574 |
$absolut_bb_balance = $self->{bank_account}->reconciliation_starting_balance * -1;
|
|
| 575 |
|
|
| 576 |
push @all_bt_where, ( transdate => { gt => $self->{bank_account}->reconciliation_starting_date });
|
|
| 577 |
push @all_bb_where, ( transdate => { gt => $self->{bank_account}->reconciliation_starting_date });
|
|
| 578 |
} |
|
| 579 |
|
|
| 580 |
my $bank_transactions = SL::DB::Manager::BankTransaction->get_all(where => \@bt_where ); |
|
| 581 |
my $payments = SL::DB::Manager::AccTransaction ->get_all(where => \@bb_where ); |
|
| 582 |
|
|
| 583 |
# for absolute balance get all bookings until todate |
|
| 584 |
my $todate = $::locale->parse_date_to_object($::form->{filter}->{todate_date__le});
|
|
| 585 |
my $fromdate = $::locale->parse_date_to_object($::form->{filter}->{fromdate_date__le});
|
|
| 586 |
|
|
| 551 | 587 |
if ($todate) {
|
| 552 | 588 |
push @all_bt_where, (transdate => { le => $todate });
|
| 553 | 589 |
push @all_bb_where, (transdate => { le => $todate });
|
| 554 | 590 |
} |
| 555 | 591 |
|
| 556 | 592 |
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); |
|
| 593 |
my $all_payments = SL::DB::Manager::AccTransaction ->get_all(where => \@all_bb_where);
|
|
| 558 | 594 |
|
| 559 |
my ($bt_balance, $bb_balance) = (0,0); |
|
| 560 |
my ($absolut_bt_balance, $absolut_bb_balance) = (0,0); |
|
| 595 |
$bt_balance += sum map { $_->amount } @{ $bank_transactions };
|
|
| 596 |
$bb_balance += sum map { $_->amount if ($::form->{filter}->{show_stornos} or !$_->record->storno) } @{ $payments };
|
|
| 597 |
|
|
| 598 |
$absolut_bt_balance += sum map { $_->amount } @{ $all_bank_transactions };
|
|
| 599 |
$absolut_bb_balance += sum map { $_->amount } @{ $all_payments };
|
|
| 561 | 600 |
|
| 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 };
|
|
| 566 | 601 |
|
| 567 |
$self->{bt_balance} = $bt_balance || 0;
|
|
| 568 |
$self->{bb_balance} = $bb_balance || 0;
|
|
| 602 |
$self->{bt_balance} = $bt_balance || 0;
|
|
| 603 |
$self->{bb_balance} = $bb_balance || 0;
|
|
| 569 | 604 |
$self->{absolut_bt_balance} = $absolut_bt_balance || 0;
|
| 570 | 605 |
$self->{absolut_bb_balance} = $absolut_bb_balance || 0;
|
| 571 | 606 |
|
| ... | ... | |
| 579 | 614 |
} |
| 580 | 615 |
|
| 581 | 616 |
sub init_BANK_ACCOUNTS {
|
| 582 |
SL::DB::Manager::BankAccount->get_all();
|
|
| 617 |
SL::DB::Manager::BankAccount->get_all_sorted( query => [ obsolete => 0 ] );
|
|
| 583 | 618 |
} |
| 584 | 619 |
|
| 585 | 620 |
1; |
| SL/DB/BankAccount.pm | ||
|---|---|---|
| 24 | 24 |
# chart_id) |
| 25 | 25 |
|
| 26 | 26 |
my $chart_id = $self->chart_id; |
| 27 |
my $chart = SL::DB::Chart->new( id => $chart_id );
|
|
| 28 |
if ( $chart->load(speculative => 1) ) {
|
|
| 27 |
my $chart = SL::DB::Manager::Chart->find_by( id => $chart_id );
|
|
| 28 |
if ( $chart ) {
|
|
| 29 | 29 |
my $linked_bank = SL::DB::Manager::BankAccount->find_by( chart_id => $chart_id ); |
| 30 | 30 |
if ( $linked_bank ) {
|
| 31 | 31 |
if ( not $self->{id} or ( $self->{id} && $linked_bank->id != $self->{id} )) {
|
| ... | ... | |
| 42 | 42 |
return @errors; |
| 43 | 43 |
} |
| 44 | 44 |
|
| 45 |
sub displayable_name {
|
|
| 46 |
my ($self) = @_; |
|
| 47 |
|
|
| 48 |
return join ' ', grep $_, $self->name, $self->bank, $self->iban; |
|
| 49 |
} |
|
| 50 |
|
|
| 45 | 51 |
1; |
| SL/DB/BankTransaction.pm | ||
|---|---|---|
| 9 | 9 |
use SL::DB::Manager::BankTransaction; |
| 10 | 10 |
use SL::DB::Helper::LinkedRecords; |
| 11 | 11 |
|
| 12 |
__PACKAGE__->meta->initialize; |
|
| 12 |
require SL::DB::Invoice; |
|
| 13 |
require SL::DB::PurchaseInvoice; |
|
| 13 | 14 |
|
| 14 |
use SL::DB::Invoice; |
|
| 15 |
use SL::DB::PurchaseInvoice; |
|
| 15 |
__PACKAGE__->meta->initialize; |
|
| 16 | 16 |
|
| 17 |
use Data::Dumper; |
|
| 18 | 17 |
|
| 19 | 18 |
# Creates get_all, get_all_count, get_all_iterator, delete_all and update_all. |
| 20 | 19 |
#__PACKAGE__->meta->make_manager_class; |
| ... | ... | |
| 44 | 43 |
push @linked_invoices, SL::DB::Manager::PurchaseInvoice->find_by(id => $record_link->to_id)->invnumber if $record_link->to_table eq 'ap'; |
| 45 | 44 |
} |
| 46 | 45 |
|
| 47 |
# $main::lxdebug->message(0, "linked invoices sind: " . Dumper(@linked_invoices)); |
|
| 48 |
# $main::lxdebug->message(0, "record_links sind: " . Dumper($record_links)); |
|
| 49 |
|
|
| 50 | 46 |
return [ @linked_invoices ]; |
| 51 | 47 |
} |
| 52 | 48 |
|
| 49 |
sub get_agreement_with_invoice {
|
|
| 50 |
my ($self, $invoice) = @_; |
|
| 51 |
|
|
| 52 |
die "first argument is not an invoice object" |
|
| 53 |
unless ref($invoice) eq 'SL::DB::Invoice' or ref($invoice) eq 'SL::DB::PurchaseInvoice'; |
|
| 54 |
|
|
| 55 |
my %points = ( |
|
| 56 |
cust_vend_name_in_purpose => 1, |
|
| 57 |
cust_vend_number_in_purpose => 1, |
|
| 58 |
datebonus0 => 3, |
|
| 59 |
datebonus14 => 2, |
|
Auch abrufbar als: Unified diff
Sammelcommit Bankerweiterung und Skonto
Überarbeitung der Bankerweiterung vom Stand Niclas, und Einführung von
Bezahlung mit Skonto (alter payment Branch). Mehr Details siehe
changelog.
- Debug-Modus in manchen Templates für zusätzliche Tabellenfelder, muß im Template angeschaltet werden: [% SET debug=1 %]
Rechnung zuweisen:- Filterdatum bei Rechnungszuweisung repariert
- bank_transactions vor reconciliation_starting_date ignorieren
- Rechnungen mit offenem Betrag < 1 Cent ignorieren
- Reihenfolge der Bankfelder in Tabelle angepasst
Bankbuchungenacc_tran in acc_trans umbenannt in MetaSetup/ReconciliationLink
Kontenabgleich - EB- und SB-Buchungen ignorieren
Payment Helper mit Skontomodus und Skontoautomatik
neuer DB Helper zum Bezahlen von Rechnungsobjekten: pay_invoice
Drei Bezahlarten:Neue Helpermethoden rund um Rechnungen für Einkauf und Verkauf.
Für das automatische Verbuchen von Skonto muß für jeden Steuertyp ein
Skontoautomatikkonto für Verkauf oder Einkauf konfiguriert (bei Steuerschlüssel
0 und 1 beides).
Skontomodi und pay_invoice für SEPA umgesetzt
Beim Auswählen von Rechnungen für den SEPA-Lauf kann nun auch Skonto
berücksichtigt werden.
Berichte Bankbewegungen - Export repariert
Bankkonten - Validierung beim Speichern temporär ausgeschaltet
CsvImport bank transactions - show name of bank in preview
Csv-Import Bank transaction - join remote names
like for purpose, join remote_name and remote_name_1 into one field
Punktesystem in Hash pflegen, und die Regeln, die matchen,
protokollieren, wird aber noch nirgends angezeigt.
Zahlungsauswahl bei el. Kontoauszug
im Tooltip auch Skonto-Information anzeigen
beim Auswählen Dropdown mit Bezahltyp anzeigen
pay_invoice aus Helper nutzt transdate_to_kivitendo
Bericht Bankbuchungen - Name des Bankkontos in eigener Spalte anzeigen
Übersetzung für Kontenabgleich - Bank und Buchung vertauscht
Kontenabgleich - bei Vorschlägen Rechnungsnummer verlinken
Kontenabgleich - "nicht abgeglichen" als Default
Vorschläge Kontenabgleich - Beschriftung und Template Default
Bankerweiterung CSV Import - Standardeinstellungen
Bankerweiterung - in Tooltip Skonto nur bei Bedarf anzeigen
BankAccounts Upgrade - bestehende Konten auf obsolete false setzen
displayable_name für BankAccount
kann im Bankkonto select_tag als title_key statt label_sub verwendet
werden.
SEPA - Umstellung auf Bankkonten-Controller
Bankerweiterung - BankAccount Dropdowns mit displayable_name
statt label_subs
Bankerweiterung - Übersetzung korrigiert
bank_accounts mit obsolete NOT NULL und DEFAULT FALSE intialisieren
SEPA payment - noch offene SEPA-Überweisungen mit berücksichtigen
Beim SEPA-Einzug und der SEPA-Überweisung wurden beim offenen Betrag die
noch nicht geschlossenen SEPA Aufträge mit berücksichtigt. Dies wird
jetzt auch bei der Skonto-Erweiterung berücksichtigt.
Dies ist v.A. dann wichtig, wenn man eine Rechnung in mehreren Schritten
per SEPA-Überweisung bezahlen möchte, oder vielleicht von mehreren
Bankkonten aus.
Beim SEPA-Einzug kommt der Fall wohl eher nicht vor.
Skontoerweiterung - Übersetzungen nachgepflegt
Bankerweiterung - Zahlungsverkehr Menü überarbeitet
CSV-Import der Bankbuchungen nach Menü "Zahlungsverkehr" verschoben
Reihenfolge geändert, entspricht der üblichen Abarbeitungsfolge:
Import -> Verbuchen -> Abgleich
SEPA Export - kein "Differenz als Skonto" vorschlagen
bei SEPA Export soll immer Geld fließen, wenn noch ein Betrag offen ist,
der nicht bezahlt werden soll, muß dies außerhalb des SEPA-Exports
verbucht werden.
Nur die Optionen "ohne Skonto" ohne "mit Skonto nach ZB" machen Sinn.
BankTransaction - diverse Änderungen
Mouseover Tooltip in der Punktespalte
Kontenabgleich verbessert
(Rundungsungenauigkeiten im Subcent-Bereich) tolerieren
Bankerweiterung: Bei Rechnung zuweisen Skontosumme anzeigen
Kontoauszug verbuchen: add_invoices um Parameter skonto erweitert
Wenn Zahlbetrag mit Skontobetrag übereinstimmt dann Dropdown mit
"mit Skonto nach ZB" vorausgewählt übergeben.
Kann an der Stelle im Template aber noch nicht prüfen, ob
Zahlungseingangsdatum innerhalb Skontofrist liegt.
Kontoauszug verbuchen - Payment Dropdown konditional
nur anzeigen, wenn überhaupt Skonto in Frage kommt
Es wird aber immer noch nicht auf Datum überprüft
bank_transactions: itime hinzugefügt
wird aber noch nirgends verwendet
Kontenabgleich - Gesamtsaldobeschriftungen waren vertauscht
BankTransaction-Controller: Paginaten beim CSV-/PDF-Export ausschalten
Analog zu Commit 06837707
Kontenabgleich - reconciliate nach reconcile umbenannt
Bankauszug verbuchen - leeren Konteninhaber nicht matchen
BankTransaction - Idee für Negativpunkt für Überzahlung
Kontoauszug verbuchen - korrekter Titel
Kontoauszug verbuchen - offenen Betrag berücksichtigen und anzeigen
und auf leere Regex-Ausdrücke prüfen
Payment Helper - Fließkommadifferenzen berücksichtigen
in pay_invoice für die Fälle "Differenz als Skonto" und "mit Skonto nach
ZB".
Moved BankTransaction matching from Controller to DB
There is now a new function in SL::DB::BankTransaction called
get_agreement_with_invoice that takes a Invoice or PurchaseInvoice
object as an argument and calculates the agreement.
Kontoauszug verbuchen - bei Zuweisung Zahlungsart berücksichtigen
Übernimmt man einen Vorschlag, wird nun per AJAX geprüft, ob die
Kombination aus $bt und $invoice mit Skonto verbucht wird oder nicht.
Es wird ein Optionsliste für eine select als HTML-Blog zurückgeliefert
und unter "Zugewiesene Rechnungen" eingefügt.
Wenn der Zahlungsbetrag genau dem Rechnungsbetrag abzgl. Skonto
entspricht, und die Zahlung innerhalb der Skontofrist erfolgt ist, wird
"mit Skonto nach ZB" vorausgewählt". Ist die Skontofrist vorbei wird
"ohne Skonto" vorausgewählt, dadurch bleibt der Skontobetrag offen,
sofern man nicht manuell auf "mit Skonto nach ZB" umstellt.
Gibt es für die Rechnung keine Skontooption so wird auch keine Dropdown
angezeigt.
Kontoauszug verbuchen - Übersetzung für "Add invoices" korrigiert
BankTransaction - Offene Subcent-Rechnungen rausfiltern
damit diese nicht mehr zum Zuweisen zur Verfügung stehen.
Payment Helper - Export get_payment_select_options_for_bank_transaction
EXPORT_OK angepasst und alle Methoden in SL::DB::Invoice und
SL::DB::PurchaseInvoice importieren
Doku von SL/DB/Helper/Payment.pm angepasst
Kontauszug verbuchen - max agreement refactored
Nicht mehr von einer hohen Zahl in einer Schleife herunterzählen,
sondern den Wert der höchsten Matches per map und max raussuchen und
direkt danach die Vorschläge greppen.
Kontoauszug verbuchen - Refactoring von Rechnung zuweisen (html)
SL/DB/Helper/Payment.pm mit Hilfe des Presenters (für die Tags)
als HTML Blob zusammengebaut
ist jetzt ein Array, mit dem direkt das select_tag erstellt werden kann.
übergeben, und im AJAX code als data.html ausgelesen.
Reconciliation auf record umgestellt
Reconciliation - ajax as POST bei Vorschlägen, weil url zu lang werden
kann