X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FDATEV.pm;h=406f09d76be5cc9ae04b8227be49104e8530782d;hb=e770cd180600810fecd3554e104546236a9c9597;hp=fb3ec74d22ce6fbad2e53c258b1e0008f32109f2;hpb=4dbb09950c9f5596646537c12d991c99086fe7c1;p=kivitendo-erp.git diff --git a/SL/DATEV.pm b/SL/DATEV.pm index fb3ec74d2..406f09d76 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,413 +24,638 @@ # Datev export module #====================================================================== -package DATEV; +package SL::DATEV; + +use utf8; +use strict; + +use SL::DBUtils; +use SL::DATEV::KNEFile; +use SL::Taxkeys; 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++, + }; +} -sub get_datev_stamm { - $main::lxdebug->enter_sub(); +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 ]); - my ($self, $myconfig, $form) = @_; - # connect to database - my $dbh = $form->dbconnect($myconfig); +sub new { + my $class = shift; + my %data = @_; - $query = qq|SELECT * FROM datev|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + my $obj = bless {}, $class; - my $ref = $sth->fetchrow_hashref(NAME_lc); - - map { $form->{$_} = $ref->{$_} } keys %$ref; - - $sth->finish; - $dbh->disconnect; - $main::lxdebug->leave_sub(); + $obj->$_($data{$_}) for keys %data; + + $obj; } +sub exporttype { + my $self = shift; + $self->{exporttype} = $_[0] if @_; + return $self->{exporttype}; +} -sub save_datev_stamm { - $main::lxdebug->enter_sub(); +sub has_exporttype { + defined $_[0]->{exporttype}; +} - my ($self, $myconfig, $form) = @_; +sub format { + my $self = shift; + $self->{format} = $_[0] if @_; + return $self->{format}; +} + +sub has_format { + defined $_[0]->{format}; +} - # connect to database - my $dbh = $form->dbconnect_noauto($myconfig); +sub _get_export_path { + $main::lxdebug->enter_sub(); - $query = qq|DELETE FROM datev|; - $dbh->do($query) || $form->dberror($query); + my ($a, $b) = gettimeofday(); + my $path = _get_path_for_download_token("${a}-${b}-${$}"); + + mkpath($path) unless (-d $path); - $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; - - $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) = @_; - - $fromto="transdate >= "; - - 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"; - #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 export_path { + my ($self) = @_; + + return $self->{export_path} ||= _get_path_for_download_token($self->{download_token}) || _get_export_path(); +} + +sub add_filenames { + my $self = shift; + push @{ $self->{filenames} ||= [] }, @_; +} + +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 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 "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."'"; - } + + $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; } - - elsif ($zeitraum eq "zeit") { - $fromto .= "'".$transdatefrom."' and transdate <= '".$transdateto."'"; + + $::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_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 $query = "INSERT INTO datev (" . join(', ', @columns) . ") VALUES (" . join(', ', ('?') x @columns) . ")"; + do_query($::form, $self->dbh, $query, map { $data->{$_} } @columns); + + $self->dbh->commit unless $self->provided_dbh; +} + +sub export { + my ($self) = @_; + my $result; + + die 'no format set!' unless $self->has_format; + + 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'; + } + + 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 get_transactions { +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 ($notsplitindex); + + $fromto =~ s/transdate/ac\.transdate/g; + + my $taxkeys = Taxkeys->new(); + 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, + c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link, + ar.invoice + 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) + WHERE (ar.id IS NOT NULL) + AND $fromto + $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, + c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link, + ap.invoice + 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) + WHERE (ap.id IS NOT NULL) + AND $fromto + $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, + c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link, + FALSE AS invoice + FROM acc_trans ac + LEFT JOIN gl ON (ac.trans_id = gl.id) + LEFT JOIN chart c ON (ac.chart_id = c.id) + WHERE (gl.id IS NOT NULL) + AND $fromto + $filter + + ORDER BY trans_id, acc_trans_id|; + + my $sth = prepare_execute_query($form, $self->dbh, $query); + $self->{DATEV} = []; + + my $counter = 0; + while (my $ref = $sth->fetchrow_hashref("NAME_lc")) { + $counter++; + if (($counter % 500) == 0) { + $progress_callback->($counter); + } - 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; - @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); + 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); - - #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; - } + + 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) { - print(STDERR $absumsatz, "ABSAUMSATZ\n"); - $form->error("Datev-Export fehlgeschlagen!"); + + 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(); -} -sub make_kne_data_header { - $main::lxdebug->enter_sub(); + my %taxid_taxkeys = (); + my $absumsatz = 0; + if (scalar(@{$trans}) <= 2) { + push @{ $self->{DATEV} }, $trans; + next; + } - my ($myconfig, $form, $fromto) = @_; - - # connect to database - my $dbh = $form->dbconnect($myconfig); + # determine at which array position the reference value (called absumsatz) is + # and which amount it has - my @a = localtime; - $jahr=$a[5]; + for my $j (0 .. (scalar(@{$trans}) - 1)) { - #Header - $anwendungsnr = ($fromto) ? "\x31\x31" : "\x31\x33" ; - while (length($form->{datentraegernr})<3) { - $form->{datentraegernr}= "\x30".$form->{datentraegernr}; - } - - $header="\x1D\x18\x31".$form->{datentraegernr}.$anwendungsnr; + # Three cases: + # 1: gl transaction (Dialogbuchung), invoice is false, no double split booking allowed - $dfvkz=$form->{dfvkz}; - while (length($dfvkz)<2) { - $dfvkz = "\x30".$dfvkz; - } - $header .= $dfvkz; + # 2: sales or vendor invoice (Verkaufs- und Einkaufsrechnung): invoice is + # true, instead of absumsatz use link AR/AP (there should only be one + # entry) - $beraternr=$form->{beraternr}; - while (length($beraternr)<7) { - $beraternr = "\x30".$beraternr; - } - $header .= $beraternr; + # 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 ) - $mandantennr=$form->{mandantennr}; - while (length($mandantennr)<5) { - $mandantennr = "\x30".$mandantennr; - } - $header .= $mandantennr; + # 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; + } - $abrechnungsnr=$form->{abrechnungsnr}.$jahr; - while (length($abrechnungsnr)<6) { - $abrechnungsnr = "\x30".$abrechnungsnr; - } - $header .= $abrechnungsnr; + # 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; + }; + } - $fromto =~ s/transdate|>=|and|\'|<=//g; - my ($from, $to) = split / /, $fromto; - $from =~ s/ //g; - $to =~ s/ //g; + my $ml = ($trans->[0]->{'umsatz'} > 0) ? 1 : -1; + my $rounding_error = 0; + my @taxed; + + # 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 + + 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] }; + + $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 @{ $self->{DATEV} }, [ \%new_trans, $trans->[$j] ]; + + } elsif (($j != $notsplitindex) && !$trans->[$j]->{is_tax}) { + my %tax_info = $taxkeys->get_full_tax_info('transdate' => $trans->[$j]->{transdate}, + 'deliverydate' => $trans->[$j]->{deliverydate}); + + my %new_trans = (); + map { $new_trans{$_} = $trans->[$notsplitindex]->{$_}; } keys %{ $trans->[$notsplitindex] }; + + my $tax_rate = $tax_info{taxkeys}->{ $trans->[$j]->{'taxkey'} }->{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'}; - if ($from ne "") { - my ($fday, $fmonth, $fyear) = split /\./, $from; - if (length($fmonth) < 2) { - $fmonth = "0".$fmonth; - } - if (length($fday) < 2) { - $fday = "0".$fday; + } 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}); - if ($to ne "") { - my ($tday, $tmonth, $tyear) = split /\./, $to; - if (length($tmonth) <2) { - $tmonth = "0".$tmonth; + $transaction->{diff} = $diff; + $transaction->{amount} += $diff; + $transaction->{umsatz} += $diff; + $absumsatz -= $diff; + $correction = 1; + + last; + } + + $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; + $sth->finish(); + + $::lxdebug->leave_sub; +} + +sub make_kne_data_header { + $main::lxdebug->enter_sub(); + + 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; } - $header .= $passwort; - $anwendungsinfo = "\x20" x 16; - $header .= $anwendungsinfo; - $inputinfo = "\x20" x 16; - $header .= $inputinfo; - + $header .= _fill($stamm->{passwort}, 4, '0'); + $header .= " " x 16; # Anwendungsinfo + $header .= " " x 16; # Inputinfo $header .= "\x79"; - + #Versionssatz - if ($form->{exporttype}==0) { - $versionssatz= "\xB5"."1,"; - } else { - $versionssatz= "\xB6"."1,";} - - - $query = qq| select accno from chart limit 1|; - $sth=$dbh->prepare($query); - $sth->execute || $form->dberror($query); - my $ref = $sth->fetchrow_hashref(NAME_lc); - - $accnolength = $ref->{accno}; - $sth->finish; - - $versionssatz .= length($accnolength); - $versionssatz .= ","; - $versionssatz .= length($accnolength); - $versionssatz .= ",SELF"."\x1C\x79"; - - $dbh->disconnect; + my $versionssatz = $self->exporttype == DATEV_ET_BUCHUNGEN ? "\xB5" . "1," : "\xB6" . "1,"; + + my $query = qq|SELECT accno FROM chart LIMIT 1|; + my $ref = selectfirst_hashref_query($form, $self->dbh, $query); - $header .= $versionssatz; + $versionssatz .= length $ref->{accno}; + $versionssatz .= ","; + $versionssatz .= length $ref->{accno}; + $versionssatz .= ",SELF" . "\x1C\x79"; + + $header .= $versionssatz; $main::lxdebug->leave_sub(); @@ -441,23 +666,23 @@ sub datetofour { $main::lxdebug->enter_sub(); my ($date, $six) = @_; - - ($day, $month, $year) = split /\./, $date; + + my ($day, $month, $year) = split(/\./, $date); if ($day =~ /^0/) { - $day = substr($day,1,1); + $day = substr($day, 1, 1); + } + if (length($month) < 2) { + $month = "0" . $month; } - if (length($month) <2) { - $month = "0".$month; - } - if (length($year) >2) { + if (length($year) > 2) { $year = substr($year, -2, 2); } - + if ($six) { - $date= $day.$month.$year; + $date = $day . $month . $year; } else { - $date= $day.$month; + $date = $day . $month; } $main::lxdebug->leave_sub(); @@ -465,50 +690,35 @@ sub datetofour { return $date; } -sub formatumsatz { - $main::lxdebug->enter_sub(); +sub trim_leading_zeroes { + my $str = shift; - my ($umsatz, $stellen) = @_; - - $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); + $str =~ s/^0+//g; - $main::lxdebug->leave_sub(); - - return $umsatz; + return $str; } sub make_ed_versionset { $main::lxdebug->enter_sub(); - my ($header, $filename, $blockcount, $fromto) = @_; - - $versionset="V".substr($filename,2,5); - $versionset.=substr($header,6,22); - if ($fromto ne "") { - $versionset.="0000".substr($header,28,19); - } else { $datum= "\x20" x 16; - $versionset .= $datum."001".substr($header,28,4); - } - while (length($blockcount) <5) { - $blockcount= "0".$blockcount; + my ($self, $header, $filename, $blockcount) = @_; + + my $versionset = "V" . substr($filename, 2, 5); + $versionset .= substr($header, 6, 22); + + if ($self->fromto) { + $versionset .= "0000" . substr($header, 28, 19); + } else { + my $datum = " " x 16; + $versionset .= $datum . "001" . substr($header, 28, 4); } - $versionset.=$blockcount; - $versionset.="001"; - $versionset.="\x20\x31"; - $versionset.= substr($header,-12,10)." "; - $versionset.= "\x20" x 53; - + + $versionset .= _fill($blockcount, 5, '0'); + $versionset .= "001"; + $versionset .= " 1"; + $versionset .= substr($header, -12, 10) . " "; + $versionset .= " " x 53; + $main::lxdebug->leave_sub(); return $versionset; @@ -517,34 +727,18 @@ 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.= " "; - } + my ($self, $form, $fileno) = @_; + + my $stamm = $self->get_datev_stamm; + + 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; - while (length($beraternummer) <7) { - $beraternummer.=" "; - } - - while (length($beratername) <9) { - $beratername.= " "; - } - - while (length($anzahl_dateien) <5) { - $anzahl_dateien = "0".$anzahl_dateien; - } - - - $ev_header=$datentraegernr."\x20\x20\x20".$beraternummer.$beratername."\x20"; - $ev_header.=$anzahl_dateien.$anzahl_dateien; - $ev_header.= "\x20" x 95; - - $main::lxdebug->leave_sub(); return $ev_header; @@ -553,301 +747,549 @@ sub make_ev_header { sub kne_buchungsexport { $main::lxdebug->enter_sub(); - my ($myconfig, $form) = @_; - - my $export_path="datev/"; - my $filename="ED00000"; - my $evfile="EV01"; - my @ed_versionsets; - my $fileno=0; - - $fromto= &get_dates($form->{zeitraum}, $form->{monat}, $form->{quartal}, $form->{transdatefrom}, $form->{transdateto}); - &get_transactions($myconfig, $form, $fromto); - - while (scalar(@{ $form->{DATEV}})) { - my $blockcount=1; - my $remaining_bytes=256; - my $total_bytes=256; - my $umsatzsumme=0; - my $buchungssatz = ""; + my ($self) = @_; + + my $form = $::form; + + my @filenames; + + my $filename = "ED00000"; + my $evfile = "EV01"; + my @ed_versionset; + my $fileno = 0; + + my $fromto = $self->fromto; + + $self->_get_transactions($fromto); + + return if $self->errors; + + my $counter = 0; + + while (scalar(@{ $self->{DATEV} || [] })) { + my $umsatzsumme = 0; $filename++; - my $ed_filename= $export_path.$filename; - open (ED, "> $ed_filename") or die "can't open outputfile: $!\n"; - $header=&make_kne_data_header($myconfig, $form, $fromto); - $remaining_bytes -= length($header); - - while (scalar(@{ $form->{DATEV}})>0) { - $transaction = shift @{ $form->{DATEV}}; - $trans_lines = scalar(@ {$transaction}); - $umsatz =0; - $gegenkonto=""; - $konto=""; - $belegfeld1=""; - $datum=""; - $waehrung=""; - $buchungstext=""; - $belegfeld2=""; - $datevautomatik=0; - $taxkey=0; - $charttax=0; - %umlaute = ('ä' => 'ae', 'ö' => 'oe', 'ü' => 'ue', 'Ä' => 'Ae', 'Ö' => 'Oe', 'Ü' => 'Ue', 'ß' => 'sz'); - - for (my $i=0; $i <$trans_lines; $i++) { - if (abs($transaction->[$i]->{'umsatz'}) > abs($umsatz)) { - $umsatz = $transaction->[$i]->{'umsatz'}; + my $ed_filename = $self->export_path . $filename; + push(@filenames, $filename); + my $header = $self->make_kne_data_header($form); + + my $kne_file = SL::DATEV::KNEFile->new(); + $kne_file->add_block($header); + + while (scalar(@{ $self->{DATEV} }) > 0) { + my $transaction = shift @{ $self->{DATEV} }; + my $trans_lines = scalar(@{$transaction}); + $counter++; + + my $umsatz = 0; + my $gegenkonto = ""; + my $konto = ""; + my $belegfeld1 = ""; + my $datum = ""; + my $waehrung = ""; + my $buchungstext = ""; + my $belegfeld2 = ""; + my $datevautomatik = 0; + my $taxkey = 0; + my $charttax = 0; + my ($haben, $soll); + my $iconv = $::locale->{iconv_utf8}; + my %umlaute = ($iconv->convert('ä') => 'ae', + $iconv->convert('ö') => 'oe', + $iconv->convert('ü') => 'ue', + $iconv->convert('Ä') => 'Ae', + $iconv->convert('Ö') => 'Oe', + $iconv->convert('Ü') => 'Ue', + $iconv->convert('ß') => 'sz'); + for (my $i = 0; $i < $trans_lines; $i++) { + if ($trans_lines == 2) { + if (abs($transaction->[$i]->{'amount'}) > abs($umsatz)) { + $umsatz = $transaction->[$i]->{'amount'}; + } + } else { + if (abs($transaction->[$i]->{'umsatz'}) > abs($umsatz)) { + $umsatz = $transaction->[$i]->{'umsatz'}; + } } if ($transaction->[$i]->{'datevautomatik'}) { - $datevautomatik=1; + $datevautomatik = 1; } if ($transaction->[$i]->{'taxkey'}) { $taxkey = $transaction->[$i]->{'taxkey'}; } if ($transaction->[$i]->{'charttax'}) { $charttax = $transaction->[$i]->{'charttax'}; - } - if (($transaction->[$i]->{'id'} eq $transaction->[$i]->{'chart_id'}) && ($trans_lines > 2)) { - undef($transaction->[$i]); - } elsif ($transaction->[$i]->{'amount'} >0) { + } + if ($transaction->[$i]->{'amount'} > 0) { $haben = $i; } else { $soll = $i; } } - - $umsatzsumme += $umsatz; - # Umwandlung von Umlauten und Sonderzeichen in erlaubte Zeichen bei Textfeldern - foreach $umlaut (keys(%umlaute)) { + # Umwandlung von Umlauten und Sonderzeichen in erlaubte Zeichen bei Textfeldern + foreach my $umlaut (keys(%umlaute)) { $transaction->[$haben]->{'invnumber'} =~ s/${umlaut}/${umlaute{$umlaut}}/g; - $transaction->[$haben]->{'name'} =~ s/${umlaut}/${umlaute{$umlaut}}/g; + $transaction->[$haben]->{'name'} =~ s/${umlaut}/${umlaute{$umlaut}}/g; } - - $transaction->[$haben]->{'invnumber'} =~ s/[^0-9A-Za-z\$\%\&\*\+\-\/]//g; - $transaction->[$haben]->{'name'} =~ s/[^0-9A-Za-z\$\%\&\*\+\-\ \/]//g; + $transaction->[$haben]->{'invnumber'} =~ s/[^0-9A-Za-z\$\%\&\*\+\-\/]//g; + $transaction->[$haben]->{'name'} =~ s/[^0-9A-Za-z\$\%\&\*\+\-\ \/]//g; - $transaction->[$haben]->{'invnumber'} = substr($transaction->[$haben]->{'invnumber'}, 0, 12); - $transaction->[$haben]->{'name'} = substr($transaction->[$haben]->{'name'}, 0, 30); + $transaction->[$haben]->{'invnumber'} = substr($transaction->[$haben]->{'invnumber'}, 0, 12); + $transaction->[$haben]->{'name'} = substr($transaction->[$haben]->{'name'}, 0, 30); $transaction->[$haben]->{'invnumber'} =~ s/\ *$//; - $transaction->[$haben]->{'name'} =~ s/\ *$//; - + $transaction->[$haben]->{'name'} =~ s/\ *$//; if ($trans_lines >= 2) { - $gegenkonto = "a".$transaction->[$haben]->{'accno'}; - $konto = "e".$transaction->[$soll]->{'accno'}; - if ($transaction->[$haben]->{'invnumber'} ne "") { - $belegfeld1= "\xBD".$transaction->[$haben]->{'invnumber'}."\x1C";} + $gegenkonto = "a" . trim_leading_zeroes($transaction->[$haben]->{'accno'}); + $konto = "e" . trim_leading_zeroes($transaction->[$soll]->{'accno'}); + if ($transaction->[$haben]->{'invnumber'} ne "") { + $belegfeld1 = "\xBD" . $transaction->[$haben]->{'invnumber'} . "\x1C"; + } $datum = "d"; - $datum .= &datetofour($transaction->[$haben]->{'transdate'},0); - $waehrung = "\xB3"."EUR"."\x1C"; - if ($transaction->[$haben]->{'name'} ne "") { - $buchungstext = "\x1E".$transaction->[$haben]->{'name'}."\x1C";} - if ($transaction->[$haben]->{'duedate'} ne "") { - $belegfeld2 = "\xBE".&datetofour($transaction->[$haben]->{'duedate'},1)."\x1C";} - } - - - if (($remaining_bytes- length("+".&formatumsatz($umsatz, 0))) <=6) { - $fuellzeichen = ($blockcount * 256 - length($buchungssatz.$header)); - $buchungssatz .= "\x00" x $fuellzeichen; - $blockcount++; - $total_bytes = ($blockcount) *256; - } - $vorzeichen = ($umsatz > 0) ? "+" : "-"; - $buchungssatz .=$vorzeichen.&formatumsatz($umsatz, 0); - $remaining_bytes = $total_bytes - length($buchungssatz.$header); - - if (($taxkey || $datevautomatik) && (!$datevautomatik || ($datevautomatik && ($charttax ne $taxkey)))) { - if (($remaining_bytes- length("\x6C"."11")) <=6) { - $fuellzeichen = ($blockcount * 256 - length($buchungssatz.$header)); - $buchungssatz .= "\x00" x $fuellzeichen; - $blockcount++; - $total_bytes = ($blockcount) *256; + $datum .= &datetofour($transaction->[$haben]->{'transdate'}, 0); + $waehrung = "\xB3" . "EUR" . "\x1C"; + if ($transaction->[$haben]->{'name'} ne "") { + $buchungstext = "\x1E" . $transaction->[$haben]->{'name'} . "\x1C"; } - if (!$datevautomatik) { - $buchungssatz .="\x6C".$taxkey; - } else { - $buchungssatz .="\x6C"."4"; + if ($transaction->[$haben]->{'duedate'} ne "") { + $belegfeld2 = "\xBE" . &datetofour($transaction->[$haben]->{'duedate'}, 1) . "\x1C"; } - $remaining_bytes = $total_bytes - length($buchungssatz.$header); - } - - if (($remaining_bytes- length($gegenkonto)) <=6) { - $fuellzeichen = ($blockcount * 256 - length($buchungssatz.$header)); - $buchungssatz .= "\x00" x $fuellzeichen; - $blockcount++; - $total_bytes = ($blockcount) *256; - } - $buchungssatz .= $gegenkonto; - $remaining_bytes = $total_bytes - length($buchungssatz.$header); - - if (($remaining_bytes- length($belegfeld1)) <=6) { - $fuellzeichen = ($blockcount * 256 - length($buchungssatz.$header)); - $buchungssatz .= "\x00" x $fuellzeichen; - $blockcount++; - $total_bytes = ($blockcount) *256; - } - $buchungssatz .= $belegfeld1; - $remaining_bytes = $total_bytes - length($buchungssatz.$header); - - if (($remaining_bytes- length($belegfeld2)) <=6) { - $fuellzeichen = ($blockcount * 256 - length($buchungssatz.$header)); - $buchungssatz .= "\x00" x $fuellzeichen; - $blockcount++; - $total_bytes = ($blockcount) *256; - } - $buchungssatz .= $belegfeld2; - $remaining_bytes = $total_bytes - length($buchungssatz.$header); - - if (($remaining_bytes- length($datum)) <=6) { - $fuellzeichen = ($blockcount * 256 - length($buchungssatz.$header)); - $buchungssatz .= "\x00" x $fuellzeichen; - $blockcount++; - $total_bytes = ($blockcount) *256; - } - $buchungssatz .= $datum; - $remaining_bytes = $total_bytes - length($buchungssatz.$header); - - if (($remaining_bytes- length($konto)) <=6) { - $fuellzeichen = ($blockcount * 256 - length($buchungssatz.$header)); - $buchungssatz .= "\x00" x $fuellzeichen; - $blockcount++; - $total_bytes = ($blockcount) *256; } - $buchungssatz .= $konto; - $remaining_bytes = $total_bytes - length($buchungssatz.$header); - - if (($remaining_bytes- length($buchungstext)) <=6) { - $fuellzeichen = ($blockcount * 256 - length($buchungssatz.$header)); - $buchungssatz .= "\x00" x $fuellzeichen; - $blockcount++; - $total_bytes = ($blockcount) *256; - } - $buchungssatz .= $buchungstext; - $remaining_bytes = $total_bytes - length($buchungssatz.$header); - - if (($remaining_bytes- (length($waehrung."\x79"))) <=6) { - $fuellzeichen = ($blockcount * 256 - length($buchungssatz.$header)); - $buchungssatz .= "\x00" x $fuellzeichen; - $blockcount++; - $total_bytes = ($blockcount) *256; + $umsatz = $kne_file->format_amount(abs($umsatz), 0); + $umsatzsumme += $umsatz; + $kne_file->add_block("+" . $umsatz); + + # Dies ist die einzige Stelle die datevautomatik auswertet. Was soll gesagt werden? + # Im Prinzip hat jeder acc_trans Eintrag einen Steuerschlüssel, außer, bei gewissen Fällen + # wie: Kreditorenbuchung mit negativen Vorzeichen, SEPA-Export oder Rechnungen die per + # Skript angelegt werden. + # Also falls ein Steuerschlüssel da ist und NICHT datevautomatik diesen Block hinzufügen. + # Oder aber datevautomatik ist WAHR, aber der Steuerschlüssel in der acc_trans weicht + # von dem in der Chart ab: Also wahrscheinlich Programmfehler (NULL übergeben, statt + # DATEV-Steuerschlüssel) oder der Steuerschlüssel des Kontos weicht WIRKLICH von dem Eintrag in der + # acc_trans ab. Gibt es für diesen Fall eine plausiblen Grund? + # + if ( ( $datevautomatik || $taxkey) + && (!$datevautomatik || ($datevautomatik && ($charttax ne $taxkey)))) { +# $kne_file->add_block("\x6C" . (!$datevautomatik ? $taxkey : "4")); + $kne_file->add_block("\x6C${taxkey}"); } - $buchungssatz .= $waehrung."\x79"; - $remaining_bytes = $total_bytes - length($buchungssatz.$header); - + + $kne_file->add_block($gegenkonto); + $kne_file->add_block($belegfeld1); + $kne_file->add_block($belegfeld2); + $kne_file->add_block($datum); + $kne_file->add_block($konto); + $kne_file->add_block($buchungstext); + $kne_file->add_block($waehrung . "\x79"); } - - $mandantenendsumme = "x".&formatumsatz($umsatzsumme,14)."\x79"."\x7a"; - $fuellzeichen = 256 - (length($header.$buchungssatz.$mandantenendsumme) % 256); - $dateiende = "\x00" x $fuellzeichen; - print (ED $header); - print (ED $buchungssatz); - print (ED $mandantenendsumme); - print (ED $dateiende); - close (ED); - - $ed_versionset[$fileno]= &make_ed_versionset($header, $filename, $blockcount, $fromto); + + my $mandantenendsumme = "x" . $kne_file->format_amount($umsatzsumme / 100.0, 14) . "\x79\x7a"; + + $kne_file->add_block($mandantenendsumme); + $kne_file->flush(); + + open(ED, ">", $ed_filename) or die "can't open outputfile: $!\n"; + print(ED $kne_file->get_data()); + close(ED); + + $ed_versionset[$fileno] = $self->make_ed_versionset($header, $filename, $kne_file->get_block_count()); $fileno++; } - + #Make EV Verwaltungsdatei - $ev_header= &make_ev_header($form, $fileno); - $ev_filename= $export_path.$evfile; - open (EV, "> $ev_filename") or die "can't open outputfile: EV01\n"; - print (EV $ev_header); - - foreach $file (@ed_versionset) { - print (EV $ed_versionset[$file]); + my $ev_header = $self->make_ev_header($form, $fileno); + my $ev_filename = $self->export_path . $evfile; + push(@filenames, $evfile); + open(EV, ">", $ev_filename) or die "can't open outputfile: EV01\n"; + print(EV $ev_header); + + foreach my $file (@ed_versionset) { + print(EV $ed_versionset[$file]); } - close (EV); + close(EV); ### + + $self->add_filenames(@filenames); + $main::lxdebug->leave_sub(); + + return { 'download_token' => $self->download_token, 'filenames' => \@filenames }; } sub kne_stammdatenexport { $main::lxdebug->enter_sub(); - my ($myconfig, $form) = @_; - $form->{abrechnungsnr}="99"; - - my $export_path="datev/"; - my $filename="ED00000"; - my $evfile="EV01"; - my @ed_versionsets; - my $fileno=1; - my $i=0; - my $blockcount=1; - my $remaining_bytes=256; - my $total_bytes=256; - my $buchungssatz = ""; + my ($self) = @_; + my $form = $::form; + + $self->get_datev_stamm->{abrechnungsnr} = "99"; + + my @filenames; + + my $filename = "ED00000"; + my $evfile = "EV01"; + my @ed_versionset; + my $fileno = 1; + my $i = 0; + my $blockcount = 1; + my $remaining_bytes = 256; + my $total_bytes = 256; + my $buchungssatz = ""; $filename++; - my $ed_filename= $export_path.$filename; - open (ED, "> $ed_filename") or die "can't open outputfile: $!\n"; - $header=&make_kne_data_header($myconfig, $form, ""); + my $ed_filename = $self->export_path . $filename; + push(@filenames, $filename); + open(ED, ">", $ed_filename) or die "can't open outputfile: $!\n"; + my $header = $self->make_kne_data_header($form); $remaining_bytes -= length($header); - - - # connect to database - my $dbh = $form->dbconnect($myconfig); - - - $query = qq|SELECT c.accno, c.description FROM chart c WHERE c.accno >=|.$dbh->quote($form->{accnofrom}).qq| - AND c.accno <= |.$dbh->quote($form->{accnoto}).qq| ORDER BY c.accno|; - - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { - if (($remaining_bytes- length("t".$ref->{'accno'})) <=6) { - $fuellzeichen = ($blockcount * 256 - length($buchungssatz.$header)); - $buchungssatz .= "\x00" x $fuellzeichen; - $blockcount++; - $total_bytes = ($blockcount) *256; - } - $buchungssatz .= "t".$ref->{'accno'}; - $remaining_bytes = $total_bytes - length($buchungssatz.$header); - $ref->{'description'} =~ s/[^0-9A-Za-z\$\%\&\*\+\-\/]//g; - $ref->{'description'} = substr($ref->{'description'}, 0, 40); - $ref->{'description'} =~ s/\ *$//; - - if (($remaining_bytes- length("\x1E".$ref->{'description'}."\x1C\x79")) <=6) { - $fuellzeichen = ($blockcount * 256 - length($buchungssatz.$header)); - $buchungssatz .= "\x00" x $fuellzeichen; - $blockcount++; - $total_bytes = ($blockcount) *256; - } - $buchungssatz .= "\x1E".$ref->{'description'}."\x1C\x79"; - $remaining_bytes = $total_bytes - length($buchungssatz.$header); + + my $fuellzeichen; + + my (@where, @values) = ((), ()); + if ($self->accnofrom) { + push @where, 'c.accno >= ?'; + push @values, $self->accnofrom; + } + if ($self->accnoto) { + push @where, 'c.accno <= ?'; + push @values, $self->accnoto; + } + + my $where_str = @where ? ' WHERE ' . join(' AND ', map { "($_)" } @where) : ''; + + my $query = qq|SELECT c.accno, c.description + FROM chart c + $where_str + ORDER BY c.accno|; + + my $sth = $self->dbh->prepare($query); + $sth->execute(@values) || $form->dberror($query); + + while (my $ref = $sth->fetchrow_hashref("NAME_lc")) { + if (($remaining_bytes - length("t" . $ref->{'accno'})) <= 6) { + $fuellzeichen = ($blockcount * 256 - length($buchungssatz . $header)); + $buchungssatz .= "\x00" x $fuellzeichen; + $blockcount++; + $total_bytes = ($blockcount) * 256; + } + $buchungssatz .= "t" . $ref->{'accno'}; + $remaining_bytes = $total_bytes - length($buchungssatz . $header); + $ref->{'description'} =~ s/[^0-9A-Za-z\$\%\&\*\+\-\/]//g; + $ref->{'description'} = substr($ref->{'description'}, 0, 40); + $ref->{'description'} =~ s/\ *$//; + + if ( + ($remaining_bytes - length("\x1E" . $ref->{'description'} . "\x1C\x79") + ) <= 6 + ) { + $fuellzeichen = ($blockcount * 256 - length($buchungssatz . $header)); + $buchungssatz .= "\x00" x $fuellzeichen; + $blockcount++; + $total_bytes = ($blockcount) * 256; + } + $buchungssatz .= "\x1E" . $ref->{'description'} . "\x1C\x79"; + $remaining_bytes = $total_bytes - length($buchungssatz . $header); } - + $sth->finish; - print (ED $header); - print (ED $buchungssatz); - $fuellzeichen = 256 - (length($header.$buchungssatz."z") % 256); - $dateiende = "\x00" x $fuellzeichen; - print (ED "z"); - print (ED $dateiende); - close (ED); - + print(ED $header); + print(ED $buchungssatz); + $fuellzeichen = 256 - (length($header . $buchungssatz . "z") % 256); + my $dateiende = "\x00" x $fuellzeichen; + print(ED "z"); + print(ED $dateiende); + close(ED); + #Make EV Verwaltungsdatei - $ed_versionset[0]= &make_ed_versionset($header, $filename, $blockcount, $fromto); - - $ev_header= &make_ev_header($form, $fileno); - $ev_filename= $export_path.$evfile; - open (EV, "> $ev_filename") or die "can't open outputfile: EV01\n"; - print (EV $ev_header); - - foreach $file (@ed_versionset) { - print (EV $ed_versionset[$file]); + $ed_versionset[0] = + $self->make_ed_versionset($header, $filename, $blockcount); + + my $ev_header = $self->make_ev_header($form, $fileno); + my $ev_filename = $self->export_path . $evfile; + push(@filenames, $evfile); + open(EV, ">", $ev_filename) or die "can't open outputfile: EV01\n"; + print(EV $ev_header); + + foreach my $file (@ed_versionset) { + print(EV $ed_versionset[$file]); } - close (EV); - - $dbh->disconnect; - ### + close(EV); + + $self->add_filenames(@filenames); $main::lxdebug->leave_sub(); + + return { 'download_token' => $self->download_token, 'filenames' => \@filenames }; +} + +sub DESTROY { + clean_temporary_directories(); } 1; + +__END__ + +=encoding utf-8 + +=head1 NAME + +SL::DATEV - kivitendo DATEV Export module + +=head1 SYNOPSIS + + use SL::DATEV qw(:CONSTANTS); + + my $datev = SL::DATEV->new( + exporttype => DATEV_ET_BUCHUNGEN, + format => DATEV_FORMAT_KNE, + from => $startdate, + to => $enddate, + ); + + my $datev = SL::DATEV->new( + exporttype => DATEV_ET_STAMM, + format => DATEV_FORMAT_KNE, + accnofrom => $start_account_number, + accnoto => $end_account_number, + ); + + # get or set datev stamm + my $hashref = $datev->get_datev_stamm; + $datev->save_datev_stamm($hashref); + + # manually clean up temporary directories + $datev->clean_temporary_directories; + + # export + $datev->export; + + if ($datev->errors) { + die join "\n", $datev->error; + } + + # get relevant data for saving the export: + my $dl_token = $datev->download_token; + my $path = $datev->export_path; + my @files = $datev->filenames; + + # retrieving an export at a later time + my $datev = SL::DATEV->new( + download_token => $dl_token_from_user, + ); + + my $path = $datev->export_path; + my @files = glob("$path/*"); + +=head1 DESCRIPTION + +This module implements the DATEV export standard. For usage see above. + +=head1 FUNCTIONS + +=over 4 + +=item new PARAMS + +Generic constructor. See section attributes for information about hat to pass. + +=item get_datev_stamm + +Loads DATEV Stammdaten and returns as hashref. + +=item save_datev_stamm HASHREF + +Saves DATEV Stammdaten from provided hashref. + +=item exporttype + +See L for possible values + +=item has_exporttype + +Returns true if an exporttype has been set. Without exporttype most report functions won't work. + +=item format + +Specifies the designated format of the export. Currently only KNE export is implemented. + +See L for possible values + +=item has_format + +Returns true if a format has been set. Without format most report functions won't work. + +=item download_token + +Returns a download token for this DATEV object. + +Note: If either a download_token or export_path were set at the creation these are infered, otherwise randomly generated. + +=item export_path + +Returns an export_path for this DATEV object. + +Note: If either a download_token or export_path were set at the creation these are infered, otherwise randomly generated. + +=item filenames + +Returns a list of filenames generated by this DATEV object. This only works if th files were generated during it's lifetime, not if the object was created from a download_token. + +=item net_gross_differences + +If there were any net gross differences during calculation they will be collected here. + +=item sum_net_gross_differences + +Sum of all differences. + +=item clean_temporary_directories + +Forces a garbage collection on previous exports which will delete all exports that are older than 8 hours. It will be automatically called on destruction of the object, but is advised to be called manually before delivering results of an export to the user. + +=item errors + +Returns a list of errors that occured. If no errors occured, the export was a success. + +=item export + +Exports data. You have to have set L and L or an error will +occur. OBE exports are currently not implemented. + +=back + +=head1 ATTRIBUTES + +This is a list of attributes set in either the C or a method of the same name. + +=over 4 + +=item dbh + +Set a database handle to use in the process. This allows for an export to be +done on a transaction in progress without committing first. + +=item exporttype + +See L for possible values. This MUST be set before export is called. + +=item format + +See L for possible values. This MUST be set before export is called. + +=item download_token + +Can be set on creation to retrieve a prior export for download. + +=item from + +=item to + +Set boundary dates for the export. Currently thse MUST be set for the export to work. + +=item accnofrom + +=item accnoto + +Set boundary account numbers for the export. Only useful for a stammdaten export. + +=back + +=head1 CONSTANTS + +=head2 Supplied to L + +=over 4 + +=item DATEV_ET_BUCHUNGEN + +=item DATEV_ET_STAMM + +=back + +=head2 Supplied to L. + +=over 4 + +=item DATEV_FORMAT_KNE + +=item DATEV_FORMAT_OBE + +=back + +=head1 ERROR HANDLING + +This module will die in the following cases: + +=over 4 + +=item * + +No or unrecognized exporttype or format was provided for an export + +=item * + +OBE rxport was called, which is not yet implemented. + +=item * + +general I/O errors + +=back + +Errors that occur during th actual export will be collected in L. The following types can occur at the moment: + +=over 4 + +=item * + +C. Exactly that, your ledger is unbalanced. Should never occur. + +=item * + +C This error occurs if a +transaction could not be reliably sorted out, or had rounding errors over the acceptable threshold. + +=back + +=head1 BUGS AND CAVEATS + +=over 4 + +=item * + +Handling of Vollvorlauf is currently not fully implemented. You must provide both from and to to get a working export. + +=item * + +OBE export is currently not implemented. + +=back + +=head1 TODO + +- handling of export_path and download token is a bit dodgy, clean that up. + +=head1 SEE ALSO + +L + +=head1 AUTHORS + +Philip Reetz Ep.reetz@linet-services.deE, + +Moritz Bunkus Em.bunkus@linet-services.deE, + +Jan Büren Ejan@lx-office-hosting.deE, + +Geoffrey Richardson Einformation@lx-office-hosting.deE, + +Sven Schöling Es.schoeling@linet-services.deE, + +Stephan Köhler + +=cut