Revision ea711360
Von Moritz Bunkus vor etwa 16 Jahren hinzugefügt
| SL/DATEV.pm | ||
|---|---|---|
| 216 | 216 |
return $fromto; |
| 217 | 217 |
} |
| 218 | 218 |
|
| 219 |
sub get_transactions {
|
|
| 219 |
sub _get_transactions {
|
|
| 220 | 220 |
$main::lxdebug->enter_sub(); |
| 221 | 221 |
|
| 222 |
my $dbh = $form->get_standard_dbh($myconfig);
|
|
| 222 |
my $fromto = shift;
|
|
| 223 | 223 |
|
| 224 |
$fromto =~ s/transdate/ac\.transdate/g; |
|
| 224 |
my $myconfig = \%main::myconfig; |
|
| 225 |
my $form = $main::form; |
|
| 225 | 226 |
|
| 226 |
my %taxes = selectall_as_map($form, $dbh, qq|SELECT id, rate FROM tax|, 'id', 'rate');
|
|
| 227 |
my $dbh = $form->get_standard_dbh($myconfig);
|
|
| 227 | 228 |
|
| 228 |
my $query = |
|
| 229 |
$fromto =~ s/transdate/ac\.transdate/g; |
|
| 230 |
|
|
| 231 |
my %taxes = selectall_as_map($form, $dbh, qq|SELECT id, rate FROM tax|, 'id', 'rate'); |
|
| 232 |
|
|
| 233 |
my $query = |
|
| 229 | 234 |
qq|SELECT ac.oid, ac.transdate, ac.trans_id,ar.id, ac.amount, ac.taxkey, |
| 230 | 235 |
ar.invnumber, ar.duedate, ar.amount as umsatz, |
| 231 | 236 |
ct.name, |
| ... | ... | |
| 268 | 273 |
|
| 269 | 274 |
ORDER BY trans_id, oid|; |
| 270 | 275 |
|
| 271 |
my $sth = prepare_execute_query($form, $dbh, $query); |
|
| 276 |
my $sth = prepare_execute_query($form, $dbh, $query);
|
|
| 272 | 277 |
|
| 273 |
$i = 0; |
|
| 274 |
$g = 0; |
|
| 278 |
my $g = 0; |
|
| 275 | 279 |
my $counter = 0; |
| 276 | 280 |
my @splits; |
| 277 | 281 |
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
|
| 278 |
$count = 0; |
|
| 279 |
$firstrun = 1; |
|
| 280 | 282 |
$counter++; |
| 281 | 283 |
if (($counter % 500) == 0) {
|
| 282 | 284 |
print("$counter ");
|
| 283 | 285 |
} |
| 284 | 286 |
|
| 285 |
$count += $ref->{amount};
|
|
| 286 |
push @{$i}, $ref;
|
|
| 287 |
my $i = [ $ref ]; |
|
| 288 |
|
|
| 289 |
my $count = $ref->{amount};
|
|
| 290 |
my $firstrun = 1; |
|
| 291 |
|
|
| 287 | 292 |
while (abs($count) > 0.01 || $firstrun) {
|
| 288 |
$ref2 = $sth->fetchrow_hashref(NAME_lc); |
|
| 293 |
my $ref2 = $sth->fetchrow_hashref(NAME_lc);
|
|
| 289 | 294 |
last unless ($ref2); |
| 290 |
$count += $ref2->{amount};
|
|
| 295 |
|
|
| 291 | 296 |
push @{$i}, $ref2;
|
| 292 |
$firstrun = 0; |
|
| 297 |
|
|
| 298 |
$count += $ref2->{amount};
|
|
| 299 |
$firstrun = 0; |
|
| 293 | 300 |
} |
| 301 |
|
|
| 294 | 302 |
my %taxid_taxkeys = (); |
| 295 |
$absumsatz = 0; |
|
| 296 |
if (scalar(@{$i}) > 2) {
|
|
| 297 |
for my $j (0 .. (scalar(@{$i}) - 1)) {
|
|
| 298 |
if (abs($i->[$j]->{'amount'}) > abs($absumsatz)) {
|
|
| 299 |
$absumsatz = $i->[$j]->{'amount'};
|
|
| 300 |
$notsplitindex = $j; |
|
| 301 |
} |
|
| 302 |
if (($i->[$j]->{'taxtaxkey'}) && ($i->[$j]->{'taxid'})) {
|
|
| 303 |
$taxid_taxkeys{$i->[$j]->{'taxtaxkey'}} = $i->[$j]->{'taxid'};
|
|
| 304 |
} |
|
| 305 |
} |
|
| 306 |
$ml = ($i->[0]->{'umsatz'} > 0) ? 1 : -1;
|
|
| 307 |
for my $j (0 .. (scalar(@{$i}) - 1)) {
|
|
| 308 |
if ( ($j != $notsplitindex) |
|
| 309 |
&& ($i->[$j]->{'chart_id'} eq "")
|
|
| 310 |
&& ( $i->[$j]->{'taxkey'} eq ""
|
|
| 311 |
|| $i->[$j]->{'taxkey'} eq "0"
|
|
| 312 |
|| $i->[$j]->{'taxkey'} eq "1"
|
|
| 313 |
|| $i->[$j]->{'taxkey'} eq "10"
|
|
| 314 |
|| $i->[$j]->{'taxkey'} eq "11")) {
|
|
| 315 |
my %blubb = {};
|
|
| 316 |
map({ $blubb{$_} = $i->[$notsplitindex]->{$_}; } keys(%{ $i->[$notsplitindex] }));
|
|
| 317 |
|
|
| 318 |
$absumsatz += $i->[$j]->{'amount'};
|
|
| 319 |
$blubb{'amount'} = $i->[$j]->{'amount'} * (-1);
|
|
| 320 |
$blubb{'umsatz'} = abs($i->[$j]->{'amount'}) * $ml;
|
|
| 321 |
$i->[$j]->{'umsatz'} = abs($i->[$j]->{'amount'}) * $ml;
|
|
| 322 |
|
|
| 323 |
push @{ $splits[$g] }, \%blubb; #$i->[$notsplitindex];
|
|
| 324 |
push @{ $splits[$g] }, $i->[$j];
|
|
| 325 |
push @{ $form->{DATEV} }, \@{ $splits[$g] };
|
|
| 326 |
|
|
| 327 |
$g++; |
|
| 328 |
|
|
| 329 |
} elsif (($j != $notsplitindex) && ($i->[$j]->{'chart_id'} eq "")) {
|
|
| 330 |
$absumsatz += ($i->[$j]->{'amount'} * (1 + $taxes{ $taxid_taxkeys{$i->[$j]->{'taxkey'}} }));
|
|
| 331 |
|
|
| 332 |
my %blubb = {};
|
|
| 333 |
map({ $blubb{$_} = $i->[$notsplitindex]->{$_}; } keys(%{ $i->[$notsplitindex] }));
|
|
| 334 |
|
|
| 335 |
$test = 1 + $taxes{ $taxid_taxkeys{$i->[$j]->{'taxkey'}} };
|
|
| 336 |
$blubb{'amount'} = $form->round_amount(($i->[$j]->{'amount'} * $test * -1), 2);
|
|
| 337 |
$blubb{'umsatz'} = abs($form->round_amount(($i->[$j]->{'amount'} * $test), 2)) * $ml;
|
|
| 338 |
$i->[$j]->{'umsatz'} = abs($form->round_amount(($i->[$j]->{'amount'} * $test), 2)) * $ml;
|
|
| 339 |
|
|
| 340 |
push @{ $splits[$g] }, \%blubb;
|
|
| 341 |
push @{ $splits[$g] }, $i->[$j];
|
|
| 342 |
push @{ $form->{DATEV} }, \@{ $splits[$g] };
|
|
| 343 |
$g++; |
|
| 303 |
my $absumsatz = 0; |
|
| 304 |
if (scalar(@{$i}) <= 2) {
|
|
| 305 |
push @{ $form->{DATEV} }, \@{$i};
|
|
| 306 |
next; |
|
| 307 |
} |
|
| 344 | 308 |
|
| 345 |
} else {
|
|
| 346 |
next; |
|
| 347 |
} |
|
| 309 |
for my $j (0 .. (scalar(@{$i}) - 1)) {
|
|
| 310 |
if (abs($i->[$j]->{'amount'}) > abs($absumsatz)) {
|
|
| 311 |
$absumsatz = $i->[$j]->{'amount'};
|
|
| 312 |
$notsplitindex = $j; |
|
| 348 | 313 |
} |
| 349 |
if (abs($absumsatz) > 0.01) {
|
|
| 350 |
$form->error("Datev-Export fehlgeschlagen! Bei Transaktion $i->[0]->{trans_id} $absumsatz\n");
|
|
| 314 |
if (($i->[$j]->{'taxtaxkey'}) && ($i->[$j]->{'taxid'})) {
|
|
| 315 |
$taxid_taxkeys{$i->[$j]->{'taxtaxkey'}} = $i->[$j]->{'taxid'};
|
|
| 351 | 316 |
} |
| 352 |
} else {
|
|
| 353 |
push @{ $form->{DATEV} }, \@{$i};
|
|
| 354 | 317 |
} |
| 355 |
$i++; |
|
| 318 |
$ml = ($i->[0]->{'umsatz'} > 0) ? 1 : -1;
|
|
| 319 |
for my $j (0 .. (scalar(@{$i}) - 1)) {
|
|
| 320 |
if ( ($j != $notsplitindex) |
|
| 321 |
&& ($i->[$j]->{'chart_id'} eq "")
|
|
| 322 |
&& ( $i->[$j]->{'taxkey'} eq ""
|
|
| 323 |
|| $i->[$j]->{'taxkey'} eq "0"
|
|
| 324 |
|| $i->[$j]->{'taxkey'} eq "1"
|
|
| 325 |
|| $i->[$j]->{'taxkey'} eq "10"
|
|
| 326 |
|| $i->[$j]->{'taxkey'} eq "11")) {
|
|
| 327 |
my %blubb = {};
|
|
| 328 |
map({ $blubb{$_} = $i->[$notsplitindex]->{$_}; } keys(%{ $i->[$notsplitindex] }));
|
|
| 329 |
|
|
| 330 |
$absumsatz += $i->[$j]->{'amount'};
|
|
| 331 |
$blubb{'amount'} = $i->[$j]->{'amount'} * (-1);
|
|
| 332 |
$blubb{'umsatz'} = abs($i->[$j]->{'amount'}) * $ml;
|
|
| 333 |
$i->[$j]->{'umsatz'} = abs($i->[$j]->{'amount'}) * $ml;
|
|
| 334 |
|
|
| 335 |
push @{ $splits[$g] }, \%blubb; #$i->[$notsplitindex];
|
|
| 336 |
push @{ $splits[$g] }, $i->[$j];
|
|
| 337 |
push @{ $form->{DATEV} }, \@{ $splits[$g] };
|
|
| 338 |
|
|
| 339 |
$g++; |
|
| 340 |
|
|
| 341 |
} elsif (($j != $notsplitindex) && ($i->[$j]->{'chart_id'} eq "")) {
|
|
| 342 |
$absumsatz += ($i->[$j]->{'amount'} * (1 + $taxes{ $taxid_taxkeys{$i->[$j]->{'taxkey'}} }));
|
|
| 343 |
|
|
| 344 |
my %blubb = {};
|
|
| 345 |
map({ $blubb{$_} = $i->[$notsplitindex]->{$_}; } keys(%{ $i->[$notsplitindex] }));
|
|
| 346 |
|
|
| 347 |
$test = 1 + $taxes{ $taxid_taxkeys{$i->[$j]->{'taxkey'}} };
|
|
| 348 |
$blubb{'amount'} = $form->round_amount(($i->[$j]->{'amount'} * $test * -1), 2);
|
|
| 349 |
$blubb{'umsatz'} = abs($form->round_amount(($i->[$j]->{'amount'} * $test), 2)) * $ml;
|
|
| 350 |
$i->[$j]->{'umsatz'} = abs($form->round_amount(($i->[$j]->{'amount'} * $test), 2)) * $ml;
|
|
| 351 |
|
|
| 352 |
push @{ $splits[$g] }, \%blubb;
|
|
| 353 |
push @{ $splits[$g] }, $i->[$j];
|
|
| 354 |
push @{ $form->{DATEV} }, \@{ $splits[$g] };
|
|
| 355 |
$g++; |
|
| 356 |
} |
|
| 357 |
} |
|
| 358 |
|
|
| 359 |
if (abs($absumsatz) > 0.01) {
|
|
| 360 |
$form->error("Datev-Export fehlgeschlagen! Bei Transaktion $i->[0]->{trans_id} $absumsatz\n");
|
|
| 361 |
} |
|
| 356 | 362 |
} |
| 357 | 363 |
$sth->finish; |
| 358 | 364 |
$dbh->disconnect; |
| ... | ... | |
| 620 | 626 |
&get_dates($form->{zeitraum}, $form->{monat},
|
| 621 | 627 |
$form->{quartal}, $form->{transdatefrom},
|
| 622 | 628 |
$form->{transdateto});
|
| 623 |
&get_transactions($myconfig, $form, $fromto);
|
|
| 629 |
_get_transactions($fromto);
|
|
| 624 | 630 |
my $counter = 0; |
| 625 | 631 |
print qq|<br>2. Durchlauf:|; |
| 626 | 632 |
while (scalar(@{ $form->{DATEV} })) {
|
Auch abrufbar als: Unified diff
Kosmetik; Variablen lokal deklarieren.