X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FDATEV.pm;h=e79c08012a7fe07ef42fc89966f6325e3b490f6d;hb=b8ee6b6ed46e55095b955ee1800b8a4b8d8ccc3f;hp=c2549dc7f2e4f869d76daa4ebde77fa7c7ce6aac;hpb=43550a3d3b924a75703d06ab297d05109c06cb37;p=kivitendo-erp.git diff --git a/SL/DATEV.pm b/SL/DATEV.pm index c2549dc7f..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,446 +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'); +} - my ($myconfig, $form, $fromto) = @_; +sub save_datev_stamm { + my ($self, $data) = @_; + + do_query($::form, $self->dbh, 'DELETE FROM datev'); + + my @columns = qw(beraternr beratername dfvkz mandantennr datentraegernr abrechnungsnr); - # connect to database - my $dbh = $form->dbconnect($myconfig); + my $query = "INSERT INTO datev (" . join(', ', @columns) . ") VALUES (" . join(', ', ('?') x @columns) . ")"; + do_query($::form, $self->dbh, $query, map { $data->{$_} } @columns); - $fromto =~ s/transdate/ac\.transdate/g; + $self->dbh->commit unless $self->provided_dbh; +} + +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); - - #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(); -} + 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}); - 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; - } - $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(); @@ -475,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); @@ -498,50 +708,34 @@ sub datetofour { return $date; } -sub formatumsatz { - $main::lxdebug->enter_sub(); - - 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); +sub trim_leading_zeroes { + my $str = shift; - $main::lxdebug->leave_sub(); + $str =~ s/^0+//g; - return $umsatz; + return $str; } sub make_ed_versionset { $main::lxdebug->enter_sub(); - my ($header, $filename, $blockcount, $fromto) = @_; + my ($self, $header, $filename, $blockcount) = @_; + + my $versionset = "V" . substr($filename, 2, 5); + $versionset .= substr($header, 6, 22); - $versionset = "V" . substr($filename, 2, 5); - $versionset .= substr($header, 6, 22); - if ($fromto ne "") { + 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(); @@ -551,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 .= " "; - } + my ($self, $form, $fileno) = @_; - while (length($beratername) < 9) { - $beratername .= " "; - } - - 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(); @@ -586,70 +765,70 @@ sub make_ev_header { sub kne_buchungsexport { $main::lxdebug->enter_sub(); - my ($myconfig, $form) = @_; + my ($self) = @_; + + my $form = $::form; + + my @filenames; - my $export_path = "datev/"; my $filename = "ED00000"; my $evfile = "EV01"; - my @ed_versionsets; + my @ed_versionset; my $fileno = 0; - - $form->header; - print qq| - - Export in Bearbeitung
- Buchungssätze verarbeitet: -|; - - $fromto = - &get_dates($form->{zeitraum}, $form->{monat}, - $form->{quartal}, $form->{transdatefrom}, - $form->{transdateto}); - &get_transactions($myconfig, $form, $fromto); + + my $fromto = $self->fromto; + + $self->_get_transactions($fromto); + + return if $self->errors; + my $counter = 0; - print qq|
2. Durchlauf:|; - while (scalar(@{ $form->{DATEV} })) { - my $blockcount = 1; - my $remaining_bytes = 256; - my $total_bytes = 256; - my $umsatzsumme = 0; - my $buchungssatz = ""; + + 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}); - $counter++; - if (($counter % 500) == 0) { - print("$counter "); - } + my $ed_filename = $self->export_path . $filename; + push(@filenames, $filename); + my $header = $self->make_kne_data_header($form); - $umsatz = 0; - $gegenkonto = ""; - $konto = ""; - $belegfeld1 = ""; - $datum = ""; - $waehrung = ""; - $buchungstext = ""; - $belegfeld2 = ""; - $datevautomatik = 0; - $taxkey = 0; - $charttax = 0; - %umlaute = ('ä' => 'ae', - 'ö' => 'oe', - 'ü' => 'ue', - 'Ä' => 'Ae', - 'Ö' => 'Oe', - 'Ü' => 'Ue', - 'ß' => 'sz'); + 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 $ustid =""; + 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 (abs($transaction->[$i]->{'umsatz'}) > abs($umsatz)) { - $umsatz = $transaction->[$i]->{'umsatz'}; + 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; @@ -660,42 +839,32 @@ sub kne_buchungsexport { 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 { + } else { $soll = $i; } } - - $umsatzsumme += abs($umsatz); - # Umwandlung von Umlauten und Sonderzeichen in erlaubte Zeichen bei Textfeldern - foreach $umlaut (keys(%umlaute)) { - $transaction->[$haben]->{'invnumber'} =~ - s/${umlaut}/${umlaute{$umlaut}}/g; - $transaction->[$haben]->{'name'} =~ s/${umlaut}/${umlaute{$umlaut}}/g; + foreach my $umlaut (keys(%umlaute)) { + $transaction->[$haben]->{'invnumber'} =~ 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]->{'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/\ *$//; if ($trans_lines >= 2) { - $gegenkonto = "a" . $transaction->[$haben]->{'accno'}; - $konto = "e" . $transaction->[$soll]->{'accno'}; + $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"; + $belegfeld1 = "\xBD" . $transaction->[$haben]->{'invnumber'} . "\x1C"; } $datum = "d"; $datum .= &datetofour($transaction->[$haben]->{'transdate'}, 0); @@ -703,148 +872,90 @@ sub kne_buchungsexport { if ($transaction->[$haben]->{'name'} ne "") { $buchungstext = "\x1E" . $transaction->[$haben]->{'name'} . "\x1C"; } + if ($transaction->[$haben]->{'ustid'} ne "") { + $ustid = "\xBA" . $transaction->[$haben]->{'ustid'} . "\x1C"; + } if ($transaction->[$haben]->{'duedate'} ne "") { - $belegfeld2 = "\xBE" - . &datetofour($transaction->[$haben]->{'duedate'}, 1) . "\x1C"; + $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; - } - $umsatz = abs($umsatz); - $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; - } - if (!$datevautomatik) { - $buchungssatz .= "\x6C" . $taxkey; - } else { - $buchungssatz .= "\x6C" . "4"; - } - $remaining_bytes = $total_bytes - length($buchungssatz . $header); + $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}"); } - 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; - } - $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($ustid); + $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); + 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] = - &make_ed_versionset($header, $filename, $blockcount, $fromto); + $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"; + 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 $file (@ed_versionset) { + foreach my $file (@ed_versionset) { print(EV $ed_versionset[$file]); } close(EV); -print qq|
Done.
- -|; ### + + $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 ($self) = @_; + my $form = $::form; + + $self->get_datev_stamm->{abrechnungsnr} = "99"; + + my @filenames; - my $export_path = "datev/"; my $filename = "ED00000"; my $evfile = "EV01"; - my @ed_versionsets; + my @ed_versionset; my $fileno = 1; my $i = 0; my $blockcount = 1; @@ -852,25 +963,35 @@ sub kne_stammdatenexport { 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); + my $fuellzeichen; - $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|; + 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) : ''; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + my $query = qq|SELECT c.accno, c.description + FROM chart c + $where_str + ORDER BY c.accno|; - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + 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; @@ -900,29 +1021,297 @@ sub kne_stammdatenexport { print(ED $header); print(ED $buchungssatz); $fuellzeichen = 256 - (length($header . $buchungssatz . "z") % 256); - $dateiende = "\x00" x $fuellzeichen; + 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); + $self->make_ed_versionset($header, $filename, $blockcount); - $ev_header = &make_ev_header($form, $fileno); - $ev_filename = $export_path . $evfile; - open(EV, "> $ev_filename") or die "can't open outputfile: EV01\n"; + 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 $file (@ed_versionset) { + foreach my $file (@ed_versionset) { print(EV $ed_versionset[$file]); } close(EV); - $dbh->disconnect; - ### + $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