- $query =
- qq|SELECT ac.oid, ac.transdate, ac.trans_id,ar.id, ac.amount, ac.taxkey, ar.invnumber, ar.duedate, ar.amount as umsatz,
- ct.name, c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, t.chart_id, t.rate FROM acc_trans ac,ar ar, customer ct,
- chart c LEFT JOIN tax t ON
- (t.chart_id=c.id)WHERE $fromto AND ac.trans_id=ar.id AND ac.trans_id=ar.id
- AND ar.customer_id=ct.id AND ac.chart_id=c.id
- UNION ALL
- SELECT ac.oid, ac.transdate, ac.trans_id,ap.id, ac.amount, ac.taxkey, ap.invnumber, ap.duedate, ap.amount as umsatz,
- ct.name, c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, t.chart_id, t.rate FROM acc_trans ac, ap ap, vendor ct, chart c LEFT JOIN tax t ON
- (t.chart_id=c.id)
- WHERE $fromto AND ac.trans_id=ap.id AND ap.vendor_id=ct.id AND ac.chart_id=c.id
- UNION ALL
- SELECT ac.oid, ac.transdate, ac.trans_id,gl.id, ac.amount, ac.taxkey, gl.reference AS invnumber, gl.transdate AS duedate, ac.amount as umsatz,
- gl.description AS name, c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, t.chart_id, t.rate FROM acc_trans ac, gl gl,
- chart c LEFT JOIN tax t ON
- (t.chart_id=c.id) WHERE $fromto AND ac.trans_id=gl.id AND ac.chart_id=c.id
- ORDER BY trans_id, oid|;
-
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
- $i = 0;
- $g = 0;
- @splits;
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
- $count = 0;
- $firstrun = 1;
- $count += $ref->{amount};
- push @{$i}, $ref;
- while (abs($count) > 0.01 || $firstrun) {
- $ref2 = $sth->fetchrow_hashref(NAME_lc);
- last unless ($ref2);
- $count += $ref2->{amount};
- push @{$i}, $ref2;
- $firstrun = 0;
- }
- $absumsatz = 0;
- if (scalar(@{$i}) > 2) {
- for my $j (0 .. (scalar(@{$i}) - 1)) {
- if (abs($i->[$j]->{'amount'}) > abs($absumsatz)) {
- $absumsatz = $i->[$j]->{'amount'};
- $notsplitindex = $j;
- }
- }
- $ml = ($i->[0]->{'umsatz'} > 0) ? 1 : -1;
- for my $j (0 .. (scalar(@{$i}) - 1)) {
- if ( ($j != $notsplitindex)
- && ($i->[$j]->{'chart_id'} eq "")
- && ( $i->[$j]->{'taxkey'} eq ""
- || $i->[$j]->{'taxkey'} eq "0"
- || $i->[$j]->{'taxkey'} eq "1"
- || $i->[$j]->{'taxkey'} eq "10"
- || $i->[$j]->{'taxkey'} eq "11")
- ) {
- my %blubb = {};
- map({ $blubb{$_} = $i->[$notsplitindex]->{$_}; }
- keys(%{ $i->[$notsplitindex] }));
- $absumsatz += $i->[$j]->{'amount'};
- $blubb{'amount'} = $i->[$j]->{'amount'} * (-1);
- $blubb{'umsatz'} = abs($i->[$j]->{'amount'}) * $ml;
- $i->[$j]->{'umsatz'} = abs($i->[$j]->{'amount'}) * $ml;
- push @{ $splits[$g] }, \%blubb; #$i->[$notsplitindex];
- push @{ $splits[$g] }, $i->[$j];
- push @{ $form->{DATEV} }, \@{ $splits[$g] };
- $g++;
- } elsif (($j != $notsplitindex) && ($i->[$j]->{'chart_id'} eq "")) {
- $absumsatz +=
- ($i->[$j]->{'amount'} * (1 + $taxes{ $i->[$j]->{'taxkey'} }));
- my %blubb = {};
- map({ $blubb{$_} = $i->[$notsplitindex]->{$_}; }
- keys(%{ $i->[$notsplitindex] }));
- $test = 1 + $taxes{ $i->[$j]->{'taxkey'} };
- $blubb{'amount'} =
- $form->round_amount(($i->[$j]->{'amount'} * $test * -1), 2);
-
- #print(STDERR $test, " Taxrate\n\n");
- $blubb{'umsatz'} =
- abs($form->round_amount(($i->[$j]->{'amount'} * $test), 2)) * $ml;
-
- $i->[$j]->{'umsatz'} =
- abs($form->round_amount(($i->[$j]->{'amount'} * $test), 2)) * $ml;
-
- #print(STDERR $i->[$j]->{'umsatz'}, " Steuer Umsatz\n");
- #print(STDERR $i->[$j]->{'amount'}, " Steuer Betrag\n");
- #print(STDERR $blubb{'umsatz'}, " Umsatz NOTSPLIT\n");
- push @{ $splits[$g] }, \%blubb;
- push @{ $splits[$g] }, $i->[$j];
- push @{ $form->{DATEV} }, \@{ $splits[$g] };
- $g++;
- } else {
- next;
- }
- }
- if (abs($absumsatz) > 0.01) {
- print(STDERR $absumsatz, "ABSAUMSATZ\n");
- $form->error("Datev-Export fehlgeschlagen!");
- }
- } else {
- push @{ $form->{DATEV} }, \@{$i};
- }
- $i++;