X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FDATEV.pm;h=e79c08012a7fe07ef42fc89966f6325e3b490f6d;hb=99bbf349b825fc48cdb71288e0979ab687770227;hp=c83112feac8a46affab33e50b53a193f8132b648;hpb=39f3d12c0cc462f8b110966095cbc2ffef8ad8f6;p=kivitendo-erp.git diff --git a/SL/DATEV.pm b/SL/DATEV.pm index c83112fea..e79c08012 100644 --- a/SL/DATEV.pm +++ b/SL/DATEV.pm @@ -1,5 +1,5 @@ #===================================================================== -# Lx-Office ERP +# kivitendo ERP # Copyright (c) 2004 # # Author: Philip Reetz @@ -24,441 +24,656 @@ # Datev export module #====================================================================== -package DATEV; +package SL::DATEV; + +use utf8; +use strict; + +use SL::DBUtils; +use SL::DATEV::KNEFile; use Data::Dumper; +use DateTime; +use Exporter qw(import); +use File::Path; +use List::Util qw(max sum); +use Time::HiRes qw(gettimeofday); + +{ + my $i = 0; + use constant { + DATEV_ET_BUCHUNGEN => $i++, + DATEV_ET_STAMM => $i++, + + DATEV_FORMAT_KNE => $i++, + DATEV_FORMAT_OBE => $i++, + }; +} + +my @export_constants = qw(DATEV_ET_BUCHUNGEN DATEV_ET_STAMM DATEV_FORMAT_KNE DATEV_FORMAT_OBE); +our @EXPORT_OK = (@export_constants); +our %EXPORT_TAGS = (CONSTANTS => [ @export_constants ]); -sub get_datev_stamm { - $main::lxdebug->enter_sub(); - my ($self, $myconfig, $form) = @_; +sub new { + my $class = shift; + my %data = @_; - # connect to database - my $dbh = $form->dbconnect($myconfig); + my $obj = bless {}, $class; - $query = qq|SELECT * FROM datev|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + $obj->$_($data{$_}) for keys %data; - my $ref = $sth->fetchrow_hashref(NAME_lc); + $obj; +} - map { $form->{$_} = $ref->{$_} } keys %$ref; +sub exporttype { + my $self = shift; + $self->{exporttype} = $_[0] if @_; + return $self->{exporttype}; +} - $sth->finish; - $dbh->disconnect; - $main::lxdebug->leave_sub(); +sub has_exporttype { + defined $_[0]->{exporttype}; } -sub save_datev_stamm { +sub format { + my $self = shift; + $self->{format} = $_[0] if @_; + return $self->{format}; +} + +sub has_format { + defined $_[0]->{format}; +} + +sub _get_export_path { $main::lxdebug->enter_sub(); - my ($self, $myconfig, $form) = @_; - - # connect to database - my $dbh = $form->dbconnect_noauto($myconfig); - - $query = qq|DELETE FROM datev|; - $dbh->do($query) || $form->dberror($query); - - $query = qq|INSERT INTO datev - (beraternr, beratername, dfvkz, mandantennr, datentraegernr, abrechnungsnr) VALUES - (| - . $dbh->quote($form->{beraternr}) . qq|,| - . $dbh->quote($form->{beratername}) . qq|,| - . $dbh->quote($form->{dfvkz}) . qq|, - | - . $dbh->quote($form->{mandantennr}) . qq|,| - . $dbh->quote($form->{datentraegernr}) . qq|,| - . $dbh->quote($form->{abrechnungsnr}) . qq|)|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - $sth->finish; + my ($a, $b) = gettimeofday(); + my $path = _get_path_for_download_token("${a}-${b}-${$}"); + + mkpath($path) unless (-d $path); - $dbh->commit; - $dbh->disconnect; $main::lxdebug->leave_sub(); + + return $path; } -sub kne_export { +sub _get_path_for_download_token { $main::lxdebug->enter_sub(); - my ($self, $myconfig, $form) = @_; - my @rc; + my $token = shift || ''; + my $path; - if ($form->{exporttype} == 0) { - @rc = &kne_buchungsexport($myconfig, $form); - } else { - @rc = &kne_stammdatenexport($myconfig, $form); + if ($token =~ m|^(\d+)-(\d+)-(\d+)$|) { + $path = $::lx_office_conf{paths}->{userspath} . "/datev-export-${1}-${2}-${3}/"; } $main::lxdebug->leave_sub(); - return @rc; + return $path; } -sub obe_export { +sub _get_download_token_for_path { $main::lxdebug->enter_sub(); - my ($self, $myconfig, $form) = @_; + my $path = shift; + my $token; + + if ($path =~ m|.*datev-export-(\d+)-(\d+)-(\d+)/?$|) { + $token = "${1}-${2}-${3}"; + } - # connect to database - my $dbh = $form->dbconnect_noauto($myconfig); - $dbh->commit; - $dbh->disconnect; $main::lxdebug->leave_sub(); + + return $token; } -sub get_dates { - $main::lxdebug->enter_sub(); +sub download_token { + my $self = shift; + $self->{download_token} = $_[0] if @_; + return $self->{download_token} ||= _get_download_token_for_path($self->export_path); +} - my ($zeitraum, $monat, $quartal, $transdatefrom, $transdateto) = @_; +sub export_path { + my ($self) = @_; - $fromto = "transdate >= "; + return $self->{export_path} ||= _get_path_for_download_token($self->{download_token}) || _get_export_path(); +} - my @a = localtime; - $a[5] += 1900; - $jahr = $a[5]; - if ($zeitraum eq "monat") { - SWITCH: { - $monat eq "1" && do { - $form->{fromdate} = "1.1.$jahr"; - $form->{todate} = "31.1.$jahr"; - last SWITCH; - }; - $monat eq "2" && do { - $form->{fromdate} = "1.2.$jahr"; +sub add_filenames { + my $self = shift; + push @{ $self->{filenames} ||= [] }, @_; +} - #this works from 1901 to 2099, 1900 and 2100 fail. - $leap = ($jahr % 4 == 0) ? "29" : "28"; - $form->{todate} = "$leap.2.$jahr"; - last SWITCH; - }; - $monat eq "3" && do { - $form->{fromdate} = "1.3.$jahr"; - $form->{todate} = "31.3.$jahr"; - last SWITCH; - }; - $monat eq "4" && do { - $form->{fromdate} = "1.4.$jahr"; - $form->{todate} = "30.4.$jahr"; - last SWITCH; - }; - $monat eq "5" && do { - $form->{fromdate} = "1.5.$jahr"; - $form->{todate} = "31.5.$jahr"; - last SWITCH; - }; - $monat eq "6" && do { - $form->{fromdate} = "1.6.$jahr"; - $form->{todate} = "30.6.$jahr"; - last SWITCH; - }; - $monat eq "7" && do { - $form->{fromdate} = "1.7.$jahr"; - $form->{todate} = "31.7.$jahr"; - last SWITCH; - }; - $monat eq "8" && do { - $form->{fromdate} = "1.8.$jahr"; - $form->{todate} = "31.8.$jahr"; - last SWITCH; - }; - $monat eq "9" && do { - $form->{fromdate} = "1.9.$jahr"; - $form->{todate} = "30.9.$jahr"; - last SWITCH; - }; - $monat eq "10" && do { - $form->{fromdate} = "1.10.$jahr"; - $form->{todate} = "31.10.$jahr"; - last SWITCH; - }; - $monat eq "11" && do { - $form->{fromdate} = "1.11.$jahr"; - $form->{todate} = "30.11.$jahr"; - last SWITCH; - }; - $monat eq "12" && do { - $form->{fromdate} = "1.12.$jahr"; - $form->{todate} = "31.12.$jahr"; - last SWITCH; - }; - } - $fromto .= - "'" . $form->{fromdate} . "' and transdate <= '" . $form->{todate} . "'"; +sub filenames { + return @{ $_[0]{filenames} || [] }; +} + +sub add_error { + my $self = shift; + push @{ $self->{errors} ||= [] }, @_; +} + +sub errors { + return @{ $_[0]{errors} || [] }; +} + +sub add_net_gross_differences { + my $self = shift; + push @{ $self->{net_gross_differences} ||= [] }, @_; +} + +sub net_gross_differences { + return @{ $_[0]{net_gross_differences} || [] }; +} + +sub sum_net_gross_differences { + return sum $_[0]->net_gross_differences; +} + +sub from { + my $self = shift; + + if (@_) { + $self->{from} = $_[0]; + } + + return $self->{from}; +} + +sub to { + my $self = shift; + + if (@_) { + $self->{to} = $_[0]; + } + + return $self->{to}; +} + +sub trans_id { + my $self = shift; + + if (@_) { + $self->{trans_id} = $_[0]; } - elsif ($zeitraum eq "quartal") { - if ($quartal == 1) { - $fromto .= - "'01.01." . $jahr . "' and transdate <= '31.03." . $jahr . "'"; - } elsif ($quartal == 2) { - $fromto .= - "'01.04." . $jahr . "' and transdate <= '30.06." . $jahr . "'"; - } elsif ($quartal == 3) { - $fromto .= - "'01.07." . $jahr . "' and transdate <= '30.09." . $jahr . "'"; - } elsif ($quartal == 4) { - $fromto .= - "'01.10." . $jahr . "' and transdate <= '31.12." . $jahr . "'"; - } + return $self->{trans_id}; +} + +sub accnofrom { + my $self = shift; + + if (@_) { + $self->{accnofrom} = $_[0]; + } + + return $self->{accnofrom}; +} + +sub accnoto { + my $self = shift; + + if (@_) { + $self->{accnoto} = $_[0]; + } + + return $self->{accnoto}; +} + + +sub dbh { + my $self = shift; + + if (@_) { + $self->{dbh} = $_[0]; + $self->{provided_dbh} = 1; } - elsif ($zeitraum eq "zeit") { - $fromto .= - "'" . $transdatefrom . "' and transdate <= '" . $transdateto . "'"; + $self->{dbh} ||= $::form->get_standard_dbh; +} + +sub provided_dbh { + $_[0]{provided_dbh}; +} + +sub clean_temporary_directories { + $::lxdebug->enter_sub; + + foreach my $path (glob($::lx_office_conf{paths}->{userspath} . "/datev-export-*")) { + next unless -d $path; + + my $mtime = (stat($path))[9]; + next if ((time() - $mtime) < 8 * 60 * 60); + + rmtree $path; + } + + $::lxdebug->leave_sub; +} + +sub _fill { + $main::lxdebug->enter_sub(); + + my $text = shift; + my $field_len = shift; + my $fill_char = shift; + my $alignment = shift || 'right'; + + my $text_len = length $text; + + if ($field_len < $text_len) { + $text = substr $text, 0, $field_len; + + } elsif ($field_len > $text_len) { + my $filler = ($fill_char) x ($field_len - $text_len); + $text = $alignment eq 'right' ? $filler . $text : $text . $filler; } $main::lxdebug->leave_sub(); - return $fromto; + return $text; } -sub get_transactions { - $main::lxdebug->enter_sub(); +sub get_datev_stamm { + return $_[0]{stamm} ||= selectfirst_hashref_query($::form, $_[0]->dbh, 'SELECT * FROM datev'); +} + +sub save_datev_stamm { + my ($self, $data) = @_; + + do_query($::form, $self->dbh, 'DELETE FROM datev'); + + my @columns = qw(beraternr beratername dfvkz mandantennr datentraegernr abrechnungsnr); - my ($myconfig, $form, $fromto) = @_; + my $query = "INSERT INTO datev (" . join(', ', @columns) . ") VALUES (" . join(', ', ('?') x @columns) . ")"; + do_query($::form, $self->dbh, $query, map { $data->{$_} } @columns); - # connect to database - my $dbh = $form->dbconnect($myconfig); + $self->dbh->commit unless $self->provided_dbh; +} - $fromto =~ s/transdate/ac\.transdate/g; +sub export { + my ($self) = @_; + my $result; - $query = qq|SELECT taxkey, rate FROM tax|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + die 'no format set!' unless $self->has_format; - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { - $taxes{ $ref->{taxkey} } = $ref->{rate}; + if ($self->format == DATEV_FORMAT_KNE) { + $result = $self->kne_export; + } elsif ($self->format == DATEV_FORMAT_OBE) { + $result = $self->obe_export; + } else { + die 'unrecognized export format'; } - $sth->finish(); + return $result; +} + +sub kne_export { + my ($self) = @_; + my $result; + + die 'no exporttype set!' unless $self->has_exporttype; + + if ($self->exporttype == DATEV_ET_BUCHUNGEN) { + $result = $self->kne_buchungsexport; + } elsif ($self->exporttype == DATEV_ET_STAMM) { + $result = $self->kne_stammdatenexport; + } else { + die 'unrecognized exporttype'; + } + + return $result; +} + +sub obe_export { + die 'not yet implemented'; +} + +sub fromto { + my ($self) = @_; + + return unless $self->from && $self->to; + + return "transdate >= '" . $self->from->to_lxoffice . "' and transdate <= '" . $self->to->to_lxoffice . "'"; +} + +sub _sign { + $_[0] <=> 0; +} + +sub _get_transactions { + $main::lxdebug->enter_sub(); + my $self = shift; + my $fromto = shift; + my $progress_callback = shift || sub {}; + + my $form = $main::form; + + my $trans_id_filter = ''; + + $trans_id_filter = 'AND ac.trans_id = ' . $self->trans_id if $self->trans_id; + + my ($notsplitindex); + + $fromto =~ s/transdate/ac\.transdate/g; + + my $filter = ''; # Useful for debugging purposes + + my %all_taxchart_ids = selectall_as_map($form, $self->dbh, qq|SELECT DISTINCT chart_id, TRUE AS is_set FROM tax|, 'chart_id', 'is_set'); + + my $query = + qq|SELECT ac.acc_trans_id, ac.transdate, ac.trans_id,ar.id, ac.amount, ac.taxkey, + ar.invnumber, ar.duedate, ar.amount as umsatz, ar.deliverydate, + ct.name, ct.ustid, + c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link, + ar.invoice, + t.rate AS taxrate + FROM acc_trans ac + LEFT JOIN ar ON (ac.trans_id = ar.id) + LEFT JOIN customer ct ON (ar.customer_id = ct.id) + LEFT JOIN chart c ON (ac.chart_id = c.id) + LEFT JOIN tax t ON (ac.tax_id = t.id) + WHERE (ar.id IS NOT NULL) + AND $fromto + $trans_id_filter + $filter + + UNION ALL + + SELECT ac.acc_trans_id, ac.transdate, ac.trans_id,ap.id, ac.amount, ac.taxkey, + ap.invnumber, ap.duedate, ap.amount as umsatz, ap.deliverydate, + ct.name,ct.ustid, + c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link, + ap.invoice, + t.rate AS taxrate + FROM acc_trans ac + LEFT JOIN ap ON (ac.trans_id = ap.id) + LEFT JOIN vendor ct ON (ap.vendor_id = ct.id) + LEFT JOIN chart c ON (ac.chart_id = c.id) + LEFT JOIN tax t ON (ac.tax_id = t.id) + WHERE (ap.id IS NOT NULL) + AND $fromto + $trans_id_filter + $filter + + UNION ALL + + SELECT ac.acc_trans_id, ac.transdate, ac.trans_id,gl.id, ac.amount, ac.taxkey, + gl.reference AS invnumber, gl.transdate AS duedate, ac.amount as umsatz, NULL as deliverydate, + gl.description AS name, NULL as ustid, + c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link, + FALSE AS invoice, + t.rate AS taxrate + FROM acc_trans ac + LEFT JOIN gl ON (ac.trans_id = gl.id) + LEFT JOIN chart c ON (ac.chart_id = c.id) + LEFT JOIN tax t ON (ac.tax_id = t.id) + WHERE (gl.id IS NOT NULL) + AND $fromto + $trans_id_filter + $filter + + ORDER BY trans_id, acc_trans_id|; + + my $sth = prepare_execute_query($form, $self->dbh, $query); + $self->{DATEV} = []; - $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 $counter = 0; - @splits; - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { - $count = 0; - $firstrun = 1; + while (my $ref = $sth->fetchrow_hashref("NAME_lc")) { $counter++; if (($counter % 500) == 0) { - print("$counter "); + $progress_callback->($counter); } - $count += $ref->{amount}; - push @{$i}, $ref; - while (abs($count) > 0.01 || $firstrun) { - $ref2 = $sth->fetchrow_hashref(NAME_lc); + my $trans = [ $ref ]; + + my $count = $ref->{amount}; + my $firstrun = 1; + + # if the amount of a booking in a group is smaller than 0.02, any tax + # amounts will likely be smaller than 1 cent, so go into subcent mode + my $subcent = abs($count) < 0.02; + + # records from acc_trans are ordered by trans_id and acc_trans_id + # first check for unbalanced ledger inside one trans_id + # there may be several groups inside a trans_id, e.g. the original booking and the payment + # each group individually should be exactly balanced and each group + # individually needs its own datev lines + + # keep fetching new acc_trans lines until the end of a balanced group is reached + while (abs($count) > 0.01 || $firstrun || ($subcent && abs($count) > 0.005)) { + my $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; - } + + # check if trans_id of current acc_trans line is still the same as the + # trans_id of the first line in group + + if ($ref2->{trans_id} != $trans->[0]->{trans_id}) { + $self->add_error("Unbalanced ledger! old trans_id " . $trans->[0]->{trans_id} . " new trans_id " . $ref2->{trans_id} . " count $count"); + return; } - $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); - - $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; - - push @{ $splits[$g] }, \%blubb; - push @{ $splits[$g] }, $i->[$j]; - push @{ $form->{DATEV} }, \@{ $splits[$g] }; - $g++; - } else { - next; - } + + push @{ $trans }, $ref2; + + $count += $ref2->{amount}; + $firstrun = 0; + } + + foreach my $i (0 .. scalar(@{ $trans }) - 1) { + my $ref = $trans->[$i]; + my $prev_ref = 0 < $i ? $trans->[$i - 1] : undef; + if ( $all_taxchart_ids{$ref->{id}} + && ($ref->{link} =~ m/(?:AP_tax|AR_tax)/) + && ( ($prev_ref && $prev_ref->{taxkey} && (_sign($ref->{amount}) == _sign($prev_ref->{amount}))) + || $ref->{invoice})) { + $ref->{is_tax} = 1; } - if (abs($absumsatz) > 0.01) { - $form->error("Datev-Export fehlgeschlagen! Bei Transaktion $i->[0]->{trans_id}\n"); + + if ( !$ref->{invoice} # we have a non-invoice booking (=gl) + && $ref->{is_tax} # that has "is_tax" set + && !($prev_ref->{is_tax}) # previous line wasn't is_tax + && (_sign($ref->{amount}) == _sign($prev_ref->{amount}))) { # and sign same as previous sign + $trans->[$i - 1]->{tax_amount} = $ref->{amount}; } - } else { - push @{ $form->{DATEV} }, \@{$i}; } - $i++; - } - $sth->finish; - $dbh->disconnect; - $main::lxdebug->leave_sub(); -} + my $absumsatz = 0; + if (scalar(@{$trans}) <= 2) { + push @{ $self->{DATEV} }, $trans; + next; + } -sub make_kne_data_header { - $main::lxdebug->enter_sub(); + # determine at which array position the reference value (called absumsatz) is + # and which amount it has - my ($myconfig, $form, $fromto) = @_; + for my $j (0 .. (scalar(@{$trans}) - 1)) { - # connect to database - my $dbh = $form->dbconnect($myconfig); + # Three cases: + # 1: gl transaction (Dialogbuchung), invoice is false, no double split booking allowed - my @a = localtime; - $jahr = $a[5]; + # 2: sales or vendor invoice (Verkaufs- und Einkaufsrechnung): invoice is + # true, instead of absumsatz use link AR/AP (there should only be one + # entry) - #Header - $anwendungsnr = ($fromto) ? "\x31\x31" : "\x31\x33"; - while (length($form->{datentraegernr}) < 3) { - $form->{datentraegernr} = "\x30" . $form->{datentraegernr}; - } + # 3. AR/AP transaction (Kreditoren- und Debitorenbuchung): invoice is false, + # instead of absumsatz use link AR/AP (there should only be one, so jump + # out of search as soon as you find it ) - $header = "\x1D\x18\x31" . $form->{datentraegernr} . $anwendungsnr; + # case 1 and 2 + # for gl-bookings no split is allowed and there is no AR/AP account, so we always use the maximum value as a reference + # for ap/ar bookings we can always search for AR/AP in link and use that + if ( ( not $trans->[$j]->{'invoice'} and abs($trans->[$j]->{'amount'}) > abs($absumsatz) ) + or ($trans->[$j]->{'invoice'} and ($trans->[$j]->{'link'} eq 'AR' or $trans->[$j]->{'link'} eq 'AP'))) { + $absumsatz = $trans->[$j]->{'amount'}; + $notsplitindex = $j; + } - $dfvkz = $form->{dfvkz}; - while (length($dfvkz) < 2) { - $dfvkz = "\x30" . $dfvkz; - } - $header .= $dfvkz; + # case 3 + # Problem: we can't distinguish between AR and AP and normal invoices via boolean "invoice" + # for AR and AP transaction exit the loop as soon as an AR or AP account is found + # there must be only one AR or AP chart in the booking + if ( $trans->[$j]->{'link'} eq 'AR' or $trans->[$j]->{'link'} eq 'AP') { + $notsplitindex = $j; # position in booking with highest amount + $absumsatz = $trans->[$j]->{'amount'}; + last; + }; + } - $beraternr = $form->{beraternr}; - while (length($beraternr) < 7) { - $beraternr = "\x30" . $beraternr; - } - $header .= $beraternr; + my $ml = ($trans->[0]->{'umsatz'} > 0) ? 1 : -1; + my $rounding_error = 0; + my @taxed; - $mandantennr = $form->{mandantennr}; - while (length($mandantennr) < 5) { - $mandantennr = "\x30" . $mandantennr; - } - $header .= $mandantennr; + # go through each line and determine if it is a tax booking or not + # skip all tax lines and notsplitindex line + # push all other accounts (e.g. income or expense) with corresponding taxkey - $abrechnungsnr = $form->{abrechnungsnr} . $jahr; - while (length($abrechnungsnr) < 6) { - $abrechnungsnr = "\x30" . $abrechnungsnr; - } - $header .= $abrechnungsnr; + for my $j (0 .. (scalar(@{$trans}) - 1)) { + if ( ($j != $notsplitindex) + && !$trans->[$j]->{is_tax} + && ( $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] }; - $fromto =~ s/transdate|>=|and|\'|<=//g; - my ($from, $to) = split / /, $fromto; - $from =~ s/ //g; - $to =~ s/ //g; + $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; - if ($from ne "") { - my ($fday, $fmonth, $fyear) = split(/\./, $from); - if (length($fmonth) < 2) { - $fmonth = "0" . $fmonth; - } - if (length($fday) < 2) { - $fday = "0" . $fday; + push @{ $self->{DATEV} }, [ \%new_trans, $trans->[$j] ]; + + } elsif (($j != $notsplitindex) && !$trans->[$j]->{is_tax}) { + + my %new_trans = (); + map { $new_trans{$_} = $trans->[$notsplitindex]->{$_}; } keys %{ $trans->[$notsplitindex] }; + + my $tax_rate = $trans->[$j]->{'taxrate'}; + $new_trans{'net_amount'} = $trans->[$j]->{'amount'} * -1; + $new_trans{'tax_rate'} = 1 + $tax_rate; + + if (!$trans->[$j]->{'invoice'}) { + $new_trans{'amount'} = $form->round_amount(-1 * ($trans->[$j]->{amount} + $trans->[$j]->{tax_amount}), 2); + $new_trans{'umsatz'} = abs($new_trans{'amount'}) * $ml; + $trans->[$j]->{'umsatz'} = $new_trans{'umsatz'}; + $absumsatz += -1 * $new_trans{'amount'}; + + } else { + my $unrounded = $trans->[$j]->{'amount'} * (1 + $tax_rate) * -1 + $rounding_error; + my $rounded = $form->round_amount($unrounded, 2); + + $rounding_error = $unrounded - $rounded; + $new_trans{'amount'} = $rounded; + $new_trans{'umsatz'} = abs($rounded) * $ml; + $trans->[$j]->{'umsatz'} = $new_trans{umsatz}; + $absumsatz -= $rounded; + } + + push @{ $self->{DATEV} }, [ \%new_trans, $trans->[$j] ]; + push @taxed, $self->{DATEV}->[-1]; + } } - $from = $fday . $fmonth . substr($fyear, -2, 2); - } else { - $from = ""; - } - $header .= $from; + my $idx = 0; + my $correction = 0; + while ((abs($absumsatz) >= 0.01) && (abs($absumsatz) < 1.00)) { + if ($idx >= scalar @taxed) { + last if (!$correction); + + $correction = 0; + $idx = 0; + } + + my $transaction = $taxed[$idx]->[0]; + + my $old_amount = $transaction->{amount}; + my $old_correction = $correction; + my @possible_diffs; + + if (!$transaction->{diff}) { + @possible_diffs = (0.01, -0.01); + } else { + @possible_diffs = ($transaction->{diff}); + } + + foreach my $diff (@possible_diffs) { + my $net_amount = $form->round_amount(($transaction->{amount} + $diff) / $transaction->{tax_rate}, 2); + next if ($net_amount != $transaction->{net_amount}); + + $transaction->{diff} = $diff; + $transaction->{amount} += $diff; + $transaction->{umsatz} += $diff; + $absumsatz -= $diff; + $correction = 1; + + last; + } - if ($to ne "") { - my ($tday, $tmonth, $tyear) = split(/\./, $to); - if (length($tmonth) < 2) { - $tmonth = "0" . $tmonth; + $idx++; } - if (length($tday) < 2) { - $tday = "0" . $tday; + + $absumsatz = $form->round_amount($absumsatz, 2); + if (abs($absumsatz) >= (0.01 * (1 + scalar @taxed))) { + $self->add_error("Datev-Export fehlgeschlagen! Bei Transaktion $trans->[0]->{trans_id} ($absumsatz)"); + + } elsif (abs($absumsatz) >= 0.01) { + $self->add_net_gross_differences($absumsatz); } - $to = $tday . $tmonth . substr($tyear, -2, 2); - } else { - $to = ""; - } - $header .= $to; - if ($fromto ne "") { - $primanota = "\x30\x30\x31"; - $header .= $primanota; } - $passwort = $form->{passwort}; - while (length($passwort) < 4) { - $passwort = "\x30" . $passwort; - } - $header .= $passwort; + $sth->finish(); - $anwendungsinfo = "\x20" x 16; - $header .= $anwendungsinfo; - $inputinfo = "\x20" x 16; - $header .= $inputinfo; + $::lxdebug->leave_sub; +} - $header .= "\x79"; +sub make_kne_data_header { + $main::lxdebug->enter_sub(); - #Versionssatz - if ($form->{exporttype} == 0) { - $versionssatz = "\xB5" . "1,"; - } else { - $versionssatz = "\xB6" . "1,"; + my ($self, $form) = @_; + my ($primanota); + + my $stamm = $self->get_datev_stamm; + + my $jahr = $self->from ? $self->from->year : DateTime->today->year; + + #Header + my $header = "\x1D\x181"; + $header .= _fill($stamm->{datentraegernr}, 3, ' ', 'left'); + $header .= ($self->fromto) ? "11" : "13"; # Anwendungsnummer + $header .= _fill($stamm->{dfvkz}, 2, '0'); + $header .= _fill($stamm->{beraternr}, 7, '0'); + $header .= _fill($stamm->{mandantennr}, 5, '0'); + $header .= _fill($stamm->{abrechnungsnr} . $jahr, 6, '0'); + + $header .= $self->from ? $self->from->strftime('%d%m%y') : ''; + $header .= $self->to ? $self->to->strftime('%d%m%y') : ''; + + if ($self->fromto) { + $primanota = "001"; + $header .= $primanota; } - $query = qq| select accno from chart limit 1|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - my $ref = $sth->fetchrow_hashref(NAME_lc); + $header .= _fill($stamm->{passwort}, 4, '0'); + $header .= " " x 16; # Anwendungsinfo + $header .= " " x 16; # Inputinfo + $header .= "\x79"; - $accnolength = $ref->{accno}; - $sth->finish; + #Versionssatz + my $versionssatz = $self->exporttype == DATEV_ET_BUCHUNGEN ? "\xB5" . "1," : "\xB6" . "1,"; - $versionssatz .= length($accnolength); - $versionssatz .= ","; - $versionssatz .= length($accnolength); - $versionssatz .= ",SELF" . "\x1C\x79"; + my $query = qq|SELECT accno FROM chart LIMIT 1|; + my $ref = selectfirst_hashref_query($form, $self->dbh, $query); - $dbh->disconnect; + $versionssatz .= length $ref->{accno}; + $versionssatz .= ","; + $versionssatz .= length $ref->{accno}; + $versionssatz .= ",SELF" . "\x1C\x79"; - $header .= $versionssatz; + $header .= $versionssatz; $main::lxdebug->leave_sub(); @@ -470,7 +685,7 @@ sub datetofour { my ($date, $six) = @_; - ($day, $month, $year) = split(/\./, $date); + my ($day, $month, $year) = split(/\./, $date); if ($day =~ /^0/) { $day = substr($day, 1, 1); @@ -493,50 +708,34 @@ sub datetofour { return $date; } -sub formatumsatz { - $main::lxdebug->enter_sub(); +sub trim_leading_zeroes { + my $str = shift; - my ($umsatz, $stellen) = @_; + $str =~ s/^0+//g; - $umsatz =~ s/-//; - ($vorkomma, $nachkomma) = split(/\./, $umsatz); - $umsatz = ""; - if ($stellen > 0) { - for ($i = $stellen; $i >= $stellen + 2 - length($vorkomma); $i--) { - $umsatz .= "0"; - } - } - for ($i = 3; $i > length($nachkomma); $i--) { - $nachkomma .= "0"; - } - $umsatz = $vorkomma . substr($nachkomma, 0, 2); - - $main::lxdebug->leave_sub(); - - return $umsatz; + return $str; } sub make_ed_versionset { $main::lxdebug->enter_sub(); - my ($header, $filename, $blockcount, $fromto) = @_; + my ($self, $header, $filename, $blockcount) = @_; - $versionset = "V" . substr($filename, 2, 5); - $versionset .= substr($header, 6, 22); - if ($fromto ne "") { + my $versionset = "V" . substr($filename, 2, 5); + $versionset .= substr($header, 6, 22); + + if ($self->fromto) { $versionset .= "0000" . substr($header, 28, 19); } else { - $datum = "\x20" x 16; + my $datum = " " x 16; $versionset .= $datum . "001" . substr($header, 28, 4); } - while (length($blockcount) < 5) { - $blockcount = "0" . $blockcount; - } - $versionset .= $blockcount; + + $versionset .= _fill($blockcount, 5, '0'); $versionset .= "001"; - $versionset .= "\x20\x31"; + $versionset .= " 1"; $versionset .= substr($header, -12, 10) . " "; - $versionset .= "\x20" x 53; + $versionset .= " " x 53; $main::lxdebug->leave_sub(); @@ -546,32 +745,17 @@ sub make_ed_versionset { sub make_ev_header { $main::lxdebug->enter_sub(); - my ($form, $fileno) = @_; - $datentraegernr = $form->{datentraegernr}; - $beraternummer = $form->{beraternr}; - $beratername = $form->{beratername}; - $anzahl_dateien = $fileno; - - while (length($datentraegernr) < 3) { - $datentraegernr .= " "; - } - - while (length($beraternummer) < 7) { - $beraternummer .= " "; - } - - while (length($beratername) < 9) { - $beratername .= " "; - } + my ($self, $form, $fileno) = @_; - while (length($anzahl_dateien) < 5) { - $anzahl_dateien = "0" . $anzahl_dateien; - } + my $stamm = $self->get_datev_stamm; - $ev_header = - $datentraegernr . "\x20\x20\x20" . $beraternummer . $beratername . "\x20"; - $ev_header .= $anzahl_dateien . $anzahl_dateien; - $ev_header .= "\x20" x 95; + my $ev_header = _fill($stamm->{datentraegernr}, 3, ' ', 'left'); + $ev_header .= " "; + $ev_header .= _fill($stamm->{beraternr}, 7, ' ', 'left'); + $ev_header .= _fill($stamm->{beratername}, 9, ' ', 'left'); + $ev_header .= " "; + $ev_header .= (_fill($fileno, 5, '0')) x 2; + $ev_header .= " " x 95; $main::lxdebug->leave_sub(); @@ -581,70 +765,61 @@ sub make_ev_header { sub kne_buchungsexport { $main::lxdebug->enter_sub(); - my ($myconfig, $form) = @_; + my ($self) = @_; + + my $form = $::form; my @filenames; - my $export_path = $main::userspath . "/"; my $filename = "ED00000"; my $evfile = "EV01"; - my @ed_versionsets; + my @ed_versionset; my $fileno = 0; - $form->header; - print qq| - -
Export in Bearbeitung