X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FDATEV.pm;h=29ef34f8e52431b35dd270fa6a89ccd3b48a9de5;hb=58b90d2d1159a4564f4ef0533669a506442e88eb;hp=f53e9fcc92554d4a3c37591b72dab896710f85a3;hpb=41250b507beff4ad9f5ca76c24e7bd7a4cdb8713;p=kivitendo-erp.git
diff --git a/SL/DATEV.pm b/SL/DATEV.pm
index f53e9fcc9..29ef34f8e 100644
--- a/SL/DATEV.pm
+++ b/SL/DATEV.pm
@@ -26,6 +26,8 @@
package DATEV;
+use SL::DBUtils;
+
use Data::Dumper;
sub get_datev_stamm {
@@ -83,17 +85,17 @@ sub kne_export {
$main::lxdebug->enter_sub();
my ($self, $myconfig, $form) = @_;
- my $rc;
+ my @rc;
if ($form->{exporttype} == 0) {
- $rc = &kne_buchungsexport($myconfig, $form);
+ @rc = &kne_buchungsexport($myconfig, $form);
} else {
- $rc = &kne_stammdatenexport($myconfig, $form);
+ @rc = &kne_stammdatenexport($myconfig, $form);
}
$main::lxdebug->leave_sub();
- return $rc;
+ return @rc;
}
sub obe_export {
@@ -205,144 +207,161 @@ sub get_dates {
}
elsif ($zeitraum eq "zeit") {
- $fromto .=
- "'" . $transdatefrom . "' and transdate <= '" . $transdateto . "'";
+ $fromto .= "'" . $transdatefrom . "' and transdate <= '" . $transdateto . "'";
+ my ($yy, $mm, $dd) = $main::locale->parse_date(\%main::myconfig, $transdatefrom);
+ $jahr = $yy;
}
$main::lxdebug->leave_sub();
- return $fromto;
+ return ($fromto, $jahr);
}
-sub get_transactions {
+sub _get_transactions {
$main::lxdebug->enter_sub();
- my ($myconfig, $form, $fromto) = @_;
-
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
-
- $fromto =~ s/transdate/ac\.transdate/g;
-
- $query = qq|SELECT taxkey, rate FROM tax|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
- $taxes{ $ref->{taxkey} } = $ref->{rate};
- }
-
- $sth->finish();
-
- $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;
+ my $fromto = shift;
+
+ my $myconfig = \%main::myconfig;
+ my $form = $main::form;
+
+ my $dbh = $form->get_standard_dbh($myconfig);
+
+ my @errors = ();
+
+ $fromto =~ s/transdate/ac\.transdate/g;
+
+ my %taxes = selectall_as_map($form, $dbh, qq|SELECT id, rate FROM tax|, 'id', 'rate');
+
+ my $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, t.id AS taxid, t.taxkey AS taxtaxkey
+ 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, t.id AS taxid, t.taxkey AS taxtaxkey
+ 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, t.id AS taxid, t.taxkey AS taxtaxkey
+ 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|;
+
+ my $sth = prepare_execute_query($form, $dbh, $query);
+
+ my @splits;
my $counter = 0;
- @splits;
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
- $count = 0;
- $firstrun = 1;
$counter++;
if (($counter % 500) == 0) {
print("$counter ");
}
- $count += $ref->{amount};
- push @{$i}, $ref;
+ my $trans = [ $ref ];
+
+ my $count = $ref->{amount};
+ my $firstrun = 1;
+
while (abs($count) > 0.01 || $firstrun) {
- $ref2 = $sth->fetchrow_hashref(NAME_lc);
+ my $ref2 = $sth->fetchrow_hashref(NAME_lc);
last unless ($ref2);
- $count += $ref2->{amount};
- push @{$i}, $ref2;
- $firstrun = 0;
+
+ push @{ $trans }, $ref2;
+
+ $count += $ref2->{amount};
+ $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;
- }
+
+ my %taxid_taxkeys = ();
+ my $absumsatz = 0;
+ if (scalar(@{$trans}) <= 2) {
+ push @{ $form->{DATEV} }, $trans;
+ next;
+ }
+
+ for my $j (0 .. (scalar(@{$trans}) - 1)) {
+ if (abs($trans->[$j]->{'amount'}) > abs($absumsatz)) {
+ $absumsatz = $trans->[$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 (($trans->[$j]->{'taxtaxkey'}) && ($trans->[$j]->{'taxid'})) {
+ $taxid_taxkeys{$trans->[$j]->{'taxtaxkey'}} = $trans->[$j]->{'taxid'};
}
- if (abs($absumsatz) > 0.01) {
- print(STDERR $absumsatz, "ABSAUMSATZ\n");
- $form->error("Datev-Export fehlgeschlagen!");
+ }
+
+ my $ml = ($trans->[0]->{'umsatz'} > 0) ? 1 : -1;
+ for my $j (0 .. (scalar(@{$trans}) - 1)) {
+ if ( ($j != $notsplitindex)
+ && ($trans->[$j]->{'chart_id'} eq "")
+ && ( $trans->[$j]->{'taxkey'} eq ""
+ || $trans->[$j]->{'taxkey'} eq "0"
+ || $trans->[$j]->{'taxkey'} eq "1"
+ || $trans->[$j]->{'taxkey'} eq "10"
+ || $trans->[$j]->{'taxkey'} eq "11")) {
+ my %new_trans = ();
+ map { $new_trans{$_} = $trans->[$notsplitindex]->{$_}; } keys %{ $trans->[$notsplitindex] };
+
+ $absumsatz += $trans->[$j]->{'amount'};
+ $new_trans{'amount'} = $trans->[$j]->{'amount'} * (-1);
+ $new_trans{'umsatz'} = abs($trans->[$j]->{'amount'}) * $ml;
+ $trans->[$j]->{'umsatz'} = abs($trans->[$j]->{'amount'}) * $ml;
+
+ push @splits, [ \%new_trans, $trans->[$j] ];
+ push @{ $form->{DATEV} }, $splits[-1];
+
+ } elsif (($j != $notsplitindex) && ($trans->[$j]->{'chart_id'} eq "")) {
+ $absumsatz += ($trans->[$j]->{'amount'} * (1 + $taxes{ $taxid_taxkeys{$trans->[$j]->{'taxkey'}} }));
+
+ my %new_trans = ();
+ map { $new_trans{$_} = $trans->[$notsplitindex]->{$_}; } keys %{ $trans->[$notsplitindex] };
+
+ my $tax_rate = 1 + $taxes{ $taxid_taxkeys{$trans->[$j]->{'taxkey'}} };
+ $new_trans{'amount'} = $form->round_amount(($trans->[$j]->{'amount'} * $tax_rate * -1), 2);
+ $new_trans{'umsatz'} = abs($form->round_amount(($trans->[$j]->{'amount'} * $tax_rate), 2)) * $ml;
+ $trans->[$j]->{'umsatz'} = abs($form->round_amount(($trans->[$j]->{'amount'} * $tax_rate), 2)) * $ml;
+
+ push @splits, [ \%new_trans, $trans->[$j] ];
+ push @{ $form->{DATEV} }, $splits[-1];
}
- } else {
- push @{ $form->{DATEV} }, \@{$i};
}
- $i++;
+
+ if (abs($absumsatz) > 0.01) {
+ push @errors, "Datev-Export fehlgeschlagen! Bei Transaktion $trans->[0]->{trans_id} ($absumsatz)\n";
+ }
}
- $sth->finish;
- $dbh->disconnect;
+
+ $sth->finish();
+
+ $form->error(join("
\n", @errors)) if (@errors);
$main::lxdebug->leave_sub();
}
@@ -350,13 +369,16 @@ sub get_transactions {
sub make_kne_data_header {
$main::lxdebug->enter_sub();
- my ($myconfig, $form, $fromto) = @_;
+ my ($myconfig, $form, $fromto, $start_jahr) = @_;
# connect to database
my $dbh = $form->dbconnect($myconfig);
- my @a = localtime;
- $jahr = $a[5];
+ my $jahr = $start_jahr;
+ if (!$jahr) {
+ my @a = localtime;
+ $jahr = $a[5];
+ }
#Header
$anwendungsnr = ($fromto) ? "\x31\x31" : "\x31\x33";
@@ -396,7 +418,7 @@ sub make_kne_data_header {
$to =~ s/ //g;
if ($from ne "") {
- my ($fday, $fmonth, $fyear) = split /\./, $from;
+ my ($fday, $fmonth, $fyear) = split(/\./, $from);
if (length($fmonth) < 2) {
$fmonth = "0" . $fmonth;
}
@@ -411,7 +433,7 @@ sub make_kne_data_header {
$header .= $from;
if ($to ne "") {
- my ($tday, $tmonth, $tyear) = split /\./, $to;
+ my ($tday, $tmonth, $tyear) = split(/\./, $to);
if (length($tmonth) < 2) {
$tmonth = "0" . $tmonth;
}
@@ -475,7 +497,7 @@ sub datetofour {
my ($date, $six) = @_;
- ($day, $month, $year) = split /\./, $date;
+ ($day, $month, $year) = split(/\./, $date);
if ($day =~ /^0/) {
$day = substr($day, 1, 1);
@@ -504,7 +526,7 @@ sub formatumsatz {
my ($umsatz, $stellen) = @_;
$umsatz =~ s/-//;
- ($vorkomma, $nachkomma) = split /\./, $umsatz;
+ ($vorkomma, $nachkomma) = split(/\./, $umsatz);
$umsatz = "";
if ($stellen > 0) {
for ($i = $stellen; $i >= $stellen + 2 - length($vorkomma); $i--) {
@@ -588,12 +610,14 @@ sub kne_buchungsexport {
my ($myconfig, $form) = @_;
- my $export_path = "datev/";
+ my @filenames;
+
+ my $export_path = $main::userspath . "/";
my $filename = "ED00000";
my $evfile = "EV01";
my @ed_versionsets;
my $fileno = 0;
-
+
$form->header;
print qq|
@@ -601,11 +625,11 @@ sub kne_buchungsexport {
Buchungssätze verarbeitet:
|;
- $fromto =
+ ($fromto, $start_jahr) =
&get_dates($form->{zeitraum}, $form->{monat},
$form->{quartal}, $form->{transdatefrom},
$form->{transdateto});
- &get_transactions($myconfig, $form, $fromto);
+ _get_transactions($fromto);
my $counter = 0;
print qq|
2. Durchlauf:|;
while (scalar(@{ $form->{DATEV} })) {
@@ -616,13 +640,14 @@ sub kne_buchungsexport {
my $buchungssatz = "";
$filename++;
my $ed_filename = $export_path . $filename;
+ push(@filenames, $filename);
open(ED, "> $ed_filename") or die "can't open outputfile: $!\n";
- $header = &make_kne_data_header($myconfig, $form, $fromto);
+ $header = &make_kne_data_header($myconfig, $form, $fromto, $start_jahr);
$remaining_bytes -= length($header);
while (scalar(@{ $form->{DATEV} }) > 0) {
- $transaction = shift @{ $form->{DATEV} };
- $trans_lines = scalar(@{$transaction});
+ $transaction = shift @{ $form->{DATEV} };
+ $trans_lines = scalar(@{$transaction});
$counter++;
if (($counter % 500) == 0) {
print("$counter ");
@@ -827,6 +852,7 @@ sub kne_buchungsexport {
#Make EV Verwaltungsdatei
$ev_header = &make_ev_header($form, $fileno);
$ev_filename = $export_path . $evfile;
+ push(@filenames, $evfile);
open(EV, "> $ev_filename") or die "can't open outputfile: EV01\n";
print(EV $ev_header);
@@ -834,11 +860,12 @@ sub kne_buchungsexport {
print(EV $ed_versionset[$file]);
}
close(EV);
-print qq|
Done.
-