1 #=====================================================================
6 # Email: p.reetz@linet-services.de
7 # Web: http://www.lx-office.org
10 # This program is free software; you can redistribute it and/or modify
11 # it under the terms of the GNU General Public License as published by
12 # the Free Software Foundation; either version 2 of the License, or
13 # (at your option) any later version.
15 # This program is distributed in the hope that it will be useful,
16 # but WITHOUT ANY WARRANTY; without even the implied warranty of
17 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
18 # GNU General Public License for more details.
19 # You should have received a copy of the GNU General Public License
20 # along with this program; if not, write to the Free Software
21 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
22 #======================================================================
25 #======================================================================
33 use SL::DATEV::KNEFile;
38 use Exporter qw(import);
40 use List::Util qw(max sum);
41 use Time::HiRes qw(gettimeofday);
46 DATEV_ET_BUCHUNGEN => $i++,
47 DATEV_ET_STAMM => $i++,
49 DATEV_FORMAT_KNE => $i++,
50 DATEV_FORMAT_OBE => $i++,
54 my @export_constants = qw(DATEV_ET_BUCHUNGEN DATEV_ET_STAMM DATEV_FORMAT_KNE DATEV_FORMAT_OBE);
55 our @EXPORT_OK = (@export_constants);
56 our %EXPORT_TAGS = (CONSTANTS => [ @export_constants ]);
63 my $obj = bless {}, $class;
65 $obj->$_($data{$_}) for keys %data;
72 $self->{exporttype} = $_[0] if @_;
73 return $self->{exporttype};
77 defined $_[0]->{exporttype};
82 $self->{format} = $_[0] if @_;
83 return $self->{format};
87 defined $_[0]->{format};
90 sub _get_export_path {
91 $main::lxdebug->enter_sub();
93 my ($a, $b) = gettimeofday();
94 my $path = _get_path_for_download_token("${a}-${b}-${$}");
96 mkpath($path) unless (-d $path);
98 $main::lxdebug->leave_sub();
103 sub _get_path_for_download_token {
104 $main::lxdebug->enter_sub();
106 my $token = shift || '';
109 if ($token =~ m|^(\d+)-(\d+)-(\d+)$|) {
110 $path = $::lx_office_conf{paths}->{userspath} . "/datev-export-${1}-${2}-${3}/";
113 $main::lxdebug->leave_sub();
118 sub _get_download_token_for_path {
119 $main::lxdebug->enter_sub();
124 if ($path =~ m|.*datev-export-(\d+)-(\d+)-(\d+)/?$|) {
125 $token = "${1}-${2}-${3}";
128 $main::lxdebug->leave_sub();
135 $self->{download_token} = $_[0] if @_;
136 return $self->{download_token} ||= _get_download_token_for_path($self->export_path);
142 return $self->{export_path} ||= _get_path_for_download_token($self->{download_token}) || _get_export_path();
147 push @{ $self->{filenames} ||= [] }, @_;
151 return @{ $_[0]{filenames} || [] };
156 push @{ $self->{errors} ||= [] }, @_;
160 return @{ $_[0]{errors} || [] };
163 sub add_net_gross_differences {
165 push @{ $self->{net_gross_differences} ||= [] }, @_;
168 sub net_gross_differences {
169 return @{ $_[0]{net_gross_differences} || [] };
172 sub sum_net_gross_differences {
173 return sum $_[0]->net_gross_differences;
180 $self->{from} = $_[0];
183 return $self->{from};
200 $self->{accnofrom} = $_[0];
203 return $self->{accnofrom};
210 $self->{accnoto} = $_[0];
213 return $self->{accnoto};
221 $self->{dbh} = $_[0];
222 $self->{provided_dbh} = 1;
225 $self->{dbh} ||= $::form->get_standard_dbh;
232 sub clean_temporary_directories {
233 $::lxdebug->enter_sub;
235 foreach my $path (glob($::lx_office_conf{paths}->{userspath} . "/datev-export-*")) {
236 next unless -d $path;
238 my $mtime = (stat($path))[9];
239 next if ((time() - $mtime) < 8 * 60 * 60);
244 $::lxdebug->leave_sub;
248 $main::lxdebug->enter_sub();
251 my $field_len = shift;
252 my $fill_char = shift;
253 my $alignment = shift || 'right';
255 my $text_len = length $text;
257 if ($field_len < $text_len) {
258 $text = substr $text, 0, $field_len;
260 } elsif ($field_len > $text_len) {
261 my $filler = ($fill_char) x ($field_len - $text_len);
262 $text = $alignment eq 'right' ? $filler . $text : $text . $filler;
265 $main::lxdebug->leave_sub();
270 sub get_datev_stamm {
271 return $_[0]{stamm} ||= selectfirst_hashref_query($::form, $_[0]->dbh, 'SELECT * FROM datev');
274 sub save_datev_stamm {
275 my ($self, $data) = @_;
277 do_query($::form, $self->dbh, 'DELETE FROM datev');
279 my @columns = qw(beraternr beratername dfvkz mandantennr datentraegernr abrechnungsnr);
281 my $query = "INSERT INTO datev (" . join(', ', @columns) . ") VALUES (" . join(', ', ('?') x @columns) . ")";
282 do_query($::form, $self->dbh, $query, map { $data->{$_} } @columns);
284 $self->dbh->commit unless $self->provided_dbh;
291 die 'no format set!' unless $self->has_format;
293 if ($self->format == DATEV_FORMAT_KNE) {
294 $result = $self->kne_export;
295 } elsif ($self->format == DATEV_FORMAT_OBE) {
296 $result = $self->obe_export;
298 die 'unrecognized export format';
308 die 'no exporttype set!' unless $self->has_exporttype;
310 if ($self->exporttype == DATEV_ET_BUCHUNGEN) {
311 $result = $self->kne_buchungsexport;
312 } elsif ($self->exporttype == DATEV_ET_STAMM) {
313 $result = $self->kne_stammdatenexport;
315 die 'unrecognized exporttype';
322 die 'not yet implemented';
328 return unless $self->from && $self->to;
330 return "transdate >= '" . $self->from->to_lxoffice . "' and transdate <= '" . $self->to->to_lxoffice . "'";
337 sub _get_transactions {
338 $main::lxdebug->enter_sub();
341 my $progress_callback = shift || sub {};
343 my $form = $main::form;
347 $fromto =~ s/transdate/ac\.transdate/g;
349 my $taxkeys = Taxkeys->new();
350 my $filter = ''; # Useful for debugging purposes
352 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');
355 qq|SELECT ac.acc_trans_id, ac.transdate, ac.trans_id,ar.id, ac.amount, ac.taxkey,
356 ar.invnumber, ar.duedate, ar.amount as umsatz, ar.deliverydate,
358 c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
361 LEFT JOIN ar ON (ac.trans_id = ar.id)
362 LEFT JOIN customer ct ON (ar.customer_id = ct.id)
363 LEFT JOIN chart c ON (ac.chart_id = c.id)
364 WHERE (ar.id IS NOT NULL)
370 SELECT ac.acc_trans_id, ac.transdate, ac.trans_id,ap.id, ac.amount, ac.taxkey,
371 ap.invnumber, ap.duedate, ap.amount as umsatz, ap.deliverydate,
373 c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
376 LEFT JOIN ap ON (ac.trans_id = ap.id)
377 LEFT JOIN vendor ct ON (ap.vendor_id = ct.id)
378 LEFT JOIN chart c ON (ac.chart_id = c.id)
379 WHERE (ap.id IS NOT NULL)
385 SELECT ac.acc_trans_id, ac.transdate, ac.trans_id,gl.id, ac.amount, ac.taxkey,
386 gl.reference AS invnumber, gl.transdate AS duedate, ac.amount as umsatz, NULL as deliverydate,
387 gl.description AS name,
388 c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
391 LEFT JOIN gl ON (ac.trans_id = gl.id)
392 LEFT JOIN chart c ON (ac.chart_id = c.id)
393 WHERE (gl.id IS NOT NULL)
397 ORDER BY trans_id, acc_trans_id|;
399 my $sth = prepare_execute_query($form, $self->dbh, $query);
403 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
405 if (($counter % 500) == 0) {
406 $progress_callback->($counter);
409 my $trans = [ $ref ];
411 my $count = $ref->{amount};
414 # if the amount of a booking in a group is smaller than 0.02, any tax
415 # amounts will likely be smaller than 1 cent, so go into subcent mode
416 my $subcent = abs($count) < 0.02;
418 # records from acc_trans are ordered by trans_id and acc_trans_id
419 # first check for unbalanced ledger inside one trans_id
420 # there may be several groups inside a trans_id, e.g. the original booking and the payment
421 # each group individually should be exactly balanced and each group
422 # individually needs its own datev lines
424 # keep fetching new acc_trans lines until the end of a balanced group is reached
425 while (abs($count) > 0.01 || $firstrun || ($subcent && abs($count) > 0.005)) {
426 my $ref2 = $sth->fetchrow_hashref("NAME_lc");
429 # check if trans_id of current acc_trans line is still the same as the
430 # trans_id of the first line in group
432 if ($ref2->{trans_id} != $trans->[0]->{trans_id}) {
433 $self->add_error("Unbalanced ledger! old trans_id " . $trans->[0]->{trans_id} . " new trans_id " . $ref2->{trans_id} . " count $count");
437 push @{ $trans }, $ref2;
439 $count += $ref2->{amount};
443 foreach my $i (0 .. scalar(@{ $trans }) - 1) {
444 my $ref = $trans->[$i];
445 my $prev_ref = 0 < $i ? $trans->[$i - 1] : undef;
446 if ( $all_taxchart_ids{$ref->{id}}
447 && ($ref->{link} =~ m/(?:AP_tax|AR_tax)/)
448 && ( ($prev_ref && $prev_ref->{taxkey} && (_sign($ref->{amount}) == _sign($prev_ref->{amount})))
449 || $ref->{invoice})) {
453 if ( !$ref->{invoice} # we have a non-invoice booking (=gl)
454 && $ref->{is_tax} # that has "is_tax" set
455 && !($prev_ref->{is_tax}) # previous line wasn't is_tax
456 && (_sign($ref->{amount}) == _sign($prev_ref->{amount}))) { # and sign same as previous sign
457 $trans->[$i - 1]->{tax_amount} = $ref->{amount};
461 my %taxid_taxkeys = ();
463 if (scalar(@{$trans}) <= 2) {
464 push @{ $self->{DATEV} }, $trans;
468 # determine at which array position the reference value (called absumsatz) is
469 # and which amount it has
471 for my $j (0 .. (scalar(@{$trans}) - 1)) {
474 # 1: gl transaction (Dialogbuchung), invoice is false, no double split booking allowed
476 # 2: sales or vendor invoice (Verkaufs- und Einkaufsrechnung): invoice is
477 # true, instead of absumsatz use link AR/AP (there should only be one
480 # 3. AR/AP transaction (Kreditoren- und Debitorenbuchung): invoice is false,
481 # instead of absumsatz use link AR/AP (there should only be one, so jump
482 # out of search as soon as you find it )
485 # for gl-bookings no split is allowed and there is no AR/AP account, so we always use the maximum value as a reference
486 # for ap/ar bookings we can always search for AR/AP in link and use that
487 if ( ( not $trans->[$j]->{'invoice'} and abs($trans->[$j]->{'amount'}) > abs($absumsatz) )
488 or ($trans->[$j]->{'invoice'} and ($trans->[$j]->{'link'} eq 'AR' or $trans->[$j]->{'link'} eq 'AP'))) {
489 $absumsatz = $trans->[$j]->{'amount'};
494 # Problem: we can't distinguish between AR and AP and normal invoices via boolean "invoice"
495 # for AR and AP transaction exit the loop as soon as an AR or AP account is found
496 # there must be only one AR or AP chart in the booking
497 if ( $trans->[$j]->{'link'} eq 'AR' or $trans->[$j]->{'link'} eq 'AP') {
498 $notsplitindex = $j; # position in booking with highest amount
499 $absumsatz = $trans->[$j]->{'amount'};
504 my $ml = ($trans->[0]->{'umsatz'} > 0) ? 1 : -1;
505 my $rounding_error = 0;
508 # go through each line and determine if it is a tax booking or not
509 # skip all tax lines and notsplitindex line
510 # push all other accounts (e.g. income or expense) with corresponding taxkey
512 for my $j (0 .. (scalar(@{$trans}) - 1)) {
513 if ( ($j != $notsplitindex)
514 && !$trans->[$j]->{is_tax}
515 && ( $trans->[$j]->{'taxkey'} eq ""
516 || $trans->[$j]->{'taxkey'} eq "0"
517 || $trans->[$j]->{'taxkey'} eq "1"
518 || $trans->[$j]->{'taxkey'} eq "10"
519 || $trans->[$j]->{'taxkey'} eq "11")) {
521 map { $new_trans{$_} = $trans->[$notsplitindex]->{$_}; } keys %{ $trans->[$notsplitindex] };
523 $absumsatz += $trans->[$j]->{'amount'};
524 $new_trans{'amount'} = $trans->[$j]->{'amount'} * (-1);
525 $new_trans{'umsatz'} = abs($trans->[$j]->{'amount'}) * $ml;
526 $trans->[$j]->{'umsatz'} = abs($trans->[$j]->{'amount'}) * $ml;
528 push @{ $self->{DATEV} }, [ \%new_trans, $trans->[$j] ];
530 } elsif (($j != $notsplitindex) && !$trans->[$j]->{is_tax}) {
531 my %tax_info = $taxkeys->get_full_tax_info('transdate' => $trans->[$j]->{transdate},
532 'deliverydate' => $trans->[$j]->{deliverydate});
535 map { $new_trans{$_} = $trans->[$notsplitindex]->{$_}; } keys %{ $trans->[$notsplitindex] };
537 my $tax_rate = $tax_info{taxkeys}->{ $trans->[$j]->{'taxkey'} }->{taxrate};
538 $new_trans{'net_amount'} = $trans->[$j]->{'amount'} * -1;
539 $new_trans{'tax_rate'} = 1 + $tax_rate;
541 if (!$trans->[$j]->{'invoice'}) {
542 $new_trans{'amount'} = $form->round_amount(-1 * ($trans->[$j]->{amount} + $trans->[$j]->{tax_amount}), 2);
543 $new_trans{'umsatz'} = abs($new_trans{'amount'}) * $ml;
544 $trans->[$j]->{'umsatz'} = $new_trans{'umsatz'};
545 $absumsatz += -1 * $new_trans{'amount'};
548 my $unrounded = $trans->[$j]->{'amount'} * (1 + $tax_rate) * -1 + $rounding_error;
549 my $rounded = $form->round_amount($unrounded, 2);
551 $rounding_error = $unrounded - $rounded;
552 $new_trans{'amount'} = $rounded;
553 $new_trans{'umsatz'} = abs($rounded) * $ml;
554 $trans->[$j]->{'umsatz'} = $new_trans{umsatz};
555 $absumsatz -= $rounded;
558 push @{ $self->{DATEV} }, [ \%new_trans, $trans->[$j] ];
559 push @taxed, $self->{DATEV}->[-1];
565 while ((abs($absumsatz) >= 0.01) && (abs($absumsatz) < 1.00)) {
566 if ($idx >= scalar @taxed) {
567 last if (!$correction);
573 my $transaction = $taxed[$idx]->[0];
575 my $old_amount = $transaction->{amount};
576 my $old_correction = $correction;
579 if (!$transaction->{diff}) {
580 @possible_diffs = (0.01, -0.01);
582 @possible_diffs = ($transaction->{diff});
585 foreach my $diff (@possible_diffs) {
586 my $net_amount = $form->round_amount(($transaction->{amount} + $diff) / $transaction->{tax_rate}, 2);
587 next if ($net_amount != $transaction->{net_amount});
589 $transaction->{diff} = $diff;
590 $transaction->{amount} += $diff;
591 $transaction->{umsatz} += $diff;
601 $absumsatz = $form->round_amount($absumsatz, 2);
602 if (abs($absumsatz) >= (0.01 * (1 + scalar @taxed))) {
603 $self->add_error("Datev-Export fehlgeschlagen! Bei Transaktion $trans->[0]->{trans_id} ($absumsatz)");
605 } elsif (abs($absumsatz) >= 0.01) {
606 $self->add_net_gross_differences($absumsatz);
612 $::lxdebug->leave_sub;
615 sub make_kne_data_header {
616 $main::lxdebug->enter_sub();
618 my ($self, $form) = @_;
621 my $stamm = $self->get_datev_stamm;
623 my $jahr = $self->from ? $self->from->year : DateTime->today->year;
626 my $header = "\x1D\x181";
627 $header .= _fill($stamm->{datentraegernr}, 3, ' ', 'left');
628 $header .= ($self->fromto) ? "11" : "13"; # Anwendungsnummer
629 $header .= _fill($stamm->{dfvkz}, 2, '0');
630 $header .= _fill($stamm->{beraternr}, 7, '0');
631 $header .= _fill($stamm->{mandantennr}, 5, '0');
632 $header .= _fill($stamm->{abrechnungsnr} . $jahr, 6, '0');
634 $header .= $self->from ? $self->from->strftime('%d%m%y') : '';
635 $header .= $self->to ? $self->to->strftime('%d%m%y') : '';
639 $header .= $primanota;
642 $header .= _fill($stamm->{passwort}, 4, '0');
643 $header .= " " x 16; # Anwendungsinfo
644 $header .= " " x 16; # Inputinfo
648 my $versionssatz = $self->exporttype == DATEV_ET_BUCHUNGEN ? "\xB5" . "1," : "\xB6" . "1,";
650 my $query = qq|SELECT accno FROM chart LIMIT 1|;
651 my $ref = selectfirst_hashref_query($form, $self->dbh, $query);
653 $versionssatz .= length $ref->{accno};
654 $versionssatz .= ",";
655 $versionssatz .= length $ref->{accno};
656 $versionssatz .= ",SELF" . "\x1C\x79";
658 $header .= $versionssatz;
660 $main::lxdebug->leave_sub();
666 $main::lxdebug->enter_sub();
668 my ($date, $six) = @_;
670 my ($day, $month, $year) = split(/\./, $date);
673 $day = substr($day, 1, 1);
675 if (length($month) < 2) {
676 $month = "0" . $month;
678 if (length($year) > 2) {
679 $year = substr($year, -2, 2);
683 $date = $day . $month . $year;
685 $date = $day . $month;
688 $main::lxdebug->leave_sub();
693 sub trim_leading_zeroes {
701 sub make_ed_versionset {
702 $main::lxdebug->enter_sub();
704 my ($self, $header, $filename, $blockcount) = @_;
706 my $versionset = "V" . substr($filename, 2, 5);
707 $versionset .= substr($header, 6, 22);
710 $versionset .= "0000" . substr($header, 28, 19);
712 my $datum = " " x 16;
713 $versionset .= $datum . "001" . substr($header, 28, 4);
716 $versionset .= _fill($blockcount, 5, '0');
717 $versionset .= "001";
719 $versionset .= substr($header, -12, 10) . " ";
720 $versionset .= " " x 53;
722 $main::lxdebug->leave_sub();
728 $main::lxdebug->enter_sub();
730 my ($self, $form, $fileno) = @_;
732 my $stamm = $self->get_datev_stamm;
734 my $ev_header = _fill($stamm->{datentraegernr}, 3, ' ', 'left');
736 $ev_header .= _fill($stamm->{beraternr}, 7, ' ', 'left');
737 $ev_header .= _fill($stamm->{beratername}, 9, ' ', 'left');
739 $ev_header .= (_fill($fileno, 5, '0')) x 2;
740 $ev_header .= " " x 95;
742 $main::lxdebug->leave_sub();
747 sub kne_buchungsexport {
748 $main::lxdebug->enter_sub();
756 my $filename = "ED00000";
761 my $fromto = $self->fromto;
763 $self->_get_transactions($fromto);
765 return if $self->errors;
769 while (scalar(@{ $self->{DATEV} || [] })) {
772 my $ed_filename = $self->export_path . $filename;
773 push(@filenames, $filename);
774 my $header = $self->make_kne_data_header($form);
776 my $kne_file = SL::DATEV::KNEFile->new();
777 $kne_file->add_block($header);
779 while (scalar(@{ $self->{DATEV} }) > 0) {
780 my $transaction = shift @{ $self->{DATEV} };
781 my $trans_lines = scalar(@{$transaction});
790 my $buchungstext = "";
792 my $datevautomatik = 0;
796 my $iconv = $::locale->{iconv_utf8};
797 my %umlaute = ($iconv->convert('ä') => 'ae',
798 $iconv->convert('ö') => 'oe',
799 $iconv->convert('ü') => 'ue',
800 $iconv->convert('Ä') => 'Ae',
801 $iconv->convert('Ö') => 'Oe',
802 $iconv->convert('Ü') => 'Ue',
803 $iconv->convert('ß') => 'sz');
804 for (my $i = 0; $i < $trans_lines; $i++) {
805 if ($trans_lines == 2) {
806 if (abs($transaction->[$i]->{'amount'}) > abs($umsatz)) {
807 $umsatz = $transaction->[$i]->{'amount'};
810 if (abs($transaction->[$i]->{'umsatz'}) > abs($umsatz)) {
811 $umsatz = $transaction->[$i]->{'umsatz'};
814 if ($transaction->[$i]->{'datevautomatik'}) {
817 if ($transaction->[$i]->{'taxkey'}) {
818 $taxkey = $transaction->[$i]->{'taxkey'};
820 if ($transaction->[$i]->{'charttax'}) {
821 $charttax = $transaction->[$i]->{'charttax'};
823 if ($transaction->[$i]->{'amount'} > 0) {
830 # Umwandlung von Umlauten und Sonderzeichen in erlaubte Zeichen bei Textfeldern
831 foreach my $umlaut (keys(%umlaute)) {
832 $transaction->[$haben]->{'invnumber'} =~ s/${umlaut}/${umlaute{$umlaut}}/g;
833 $transaction->[$haben]->{'name'} =~ s/${umlaut}/${umlaute{$umlaut}}/g;
836 $transaction->[$haben]->{'invnumber'} =~ s/[^0-9A-Za-z\$\%\&\*\+\-\/]//g;
837 $transaction->[$haben]->{'name'} =~ s/[^0-9A-Za-z\$\%\&\*\+\-\ \/]//g;
839 $transaction->[$haben]->{'invnumber'} = substr($transaction->[$haben]->{'invnumber'}, 0, 12);
840 $transaction->[$haben]->{'name'} = substr($transaction->[$haben]->{'name'}, 0, 30);
841 $transaction->[$haben]->{'invnumber'} =~ s/\ *$//;
842 $transaction->[$haben]->{'name'} =~ s/\ *$//;
844 if ($trans_lines >= 2) {
846 $gegenkonto = "a" . trim_leading_zeroes($transaction->[$haben]->{'accno'});
847 $konto = "e" . trim_leading_zeroes($transaction->[$soll]->{'accno'});
848 if ($transaction->[$haben]->{'invnumber'} ne "") {
849 $belegfeld1 = "\xBD" . $transaction->[$haben]->{'invnumber'} . "\x1C";
852 $datum .= &datetofour($transaction->[$haben]->{'transdate'}, 0);
853 $waehrung = "\xB3" . "EUR" . "\x1C";
854 if ($transaction->[$haben]->{'name'} ne "") {
855 $buchungstext = "\x1E" . $transaction->[$haben]->{'name'} . "\x1C";
857 if ($transaction->[$haben]->{'duedate'} ne "") {
858 $belegfeld2 = "\xBE" . &datetofour($transaction->[$haben]->{'duedate'}, 1) . "\x1C";
862 $umsatz = $kne_file->format_amount(abs($umsatz), 0);
863 $umsatzsumme += $umsatz;
864 $kne_file->add_block("+" . $umsatz);
866 # Dies ist die einzige Stelle die datevautomatik auswertet. Was soll gesagt werden?
867 # Im Prinzip hat jeder acc_trans Eintrag einen Steuerschlüssel, außer, bei gewissen Fällen
868 # wie: Kreditorenbuchung mit negativen Vorzeichen, SEPA-Export oder Rechnungen die per
869 # Skript angelegt werden.
870 # Also falls ein Steuerschlüssel da ist und NICHT datevautomatik diesen Block hinzufügen.
871 # Oder aber datevautomatik ist WAHR, aber der Steuerschlüssel in der acc_trans weicht
872 # von dem in der Chart ab: Also wahrscheinlich Programmfehler (NULL übergeben, statt
873 # DATEV-Steuerschlüssel) oder der Steuerschlüssel des Kontos weicht WIRKLICH von dem Eintrag in der
874 # acc_trans ab. Gibt es für diesen Fall eine plausiblen Grund?
876 if ( ( $datevautomatik || $taxkey)
877 && (!$datevautomatik || ($datevautomatik && ($charttax ne $taxkey)))) {
878 # $kne_file->add_block("\x6C" . (!$datevautomatik ? $taxkey : "4"));
879 $kne_file->add_block("\x6C${taxkey}");
882 $kne_file->add_block($gegenkonto);
883 $kne_file->add_block($belegfeld1);
884 $kne_file->add_block($belegfeld2);
885 $kne_file->add_block($datum);
886 $kne_file->add_block($konto);
887 $kne_file->add_block($buchungstext);
888 $kne_file->add_block($waehrung . "\x79");
891 my $mandantenendsumme = "x" . $kne_file->format_amount($umsatzsumme / 100.0, 14) . "\x79\x7a";
893 $kne_file->add_block($mandantenendsumme);
896 open(ED, ">", $ed_filename) or die "can't open outputfile: $!\n";
897 print(ED $kne_file->get_data());
900 $ed_versionset[$fileno] = $self->make_ed_versionset($header, $filename, $kne_file->get_block_count());
904 #Make EV Verwaltungsdatei
905 my $ev_header = $self->make_ev_header($form, $fileno);
906 my $ev_filename = $self->export_path . $evfile;
907 push(@filenames, $evfile);
908 open(EV, ">", $ev_filename) or die "can't open outputfile: EV01\n";
909 print(EV $ev_header);
911 foreach my $file (@ed_versionset) {
912 print(EV $ed_versionset[$file]);
917 $self->add_filenames(@filenames);
919 $main::lxdebug->leave_sub();
921 return { 'download_token' => $self->download_token, 'filenames' => \@filenames };
924 sub kne_stammdatenexport {
925 $main::lxdebug->enter_sub();
930 $self->get_datev_stamm->{abrechnungsnr} = "99";
934 my $filename = "ED00000";
940 my $remaining_bytes = 256;
941 my $total_bytes = 256;
942 my $buchungssatz = "";
944 my $ed_filename = $self->export_path . $filename;
945 push(@filenames, $filename);
946 open(ED, ">", $ed_filename) or die "can't open outputfile: $!\n";
947 my $header = $self->make_kne_data_header($form);
948 $remaining_bytes -= length($header);
952 my (@where, @values) = ((), ());
953 if ($self->accnofrom) {
954 push @where, 'c.accno >= ?';
955 push @values, $self->accnofrom;
957 if ($self->accnoto) {
958 push @where, 'c.accno <= ?';
959 push @values, $self->accnoto;
962 my $where_str = @where ? ' WHERE ' . join(' AND ', map { "($_)" } @where) : '';
964 my $query = qq|SELECT c.accno, c.description
969 my $sth = $self->dbh->prepare($query);
970 $sth->execute(@values) || $form->dberror($query);
972 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
973 if (($remaining_bytes - length("t" . $ref->{'accno'})) <= 6) {
974 $fuellzeichen = ($blockcount * 256 - length($buchungssatz . $header));
975 $buchungssatz .= "\x00" x $fuellzeichen;
977 $total_bytes = ($blockcount) * 256;
979 $buchungssatz .= "t" . $ref->{'accno'};
980 $remaining_bytes = $total_bytes - length($buchungssatz . $header);
981 $ref->{'description'} =~ s/[^0-9A-Za-z\$\%\&\*\+\-\/]//g;
982 $ref->{'description'} = substr($ref->{'description'}, 0, 40);
983 $ref->{'description'} =~ s/\ *$//;
986 ($remaining_bytes - length("\x1E" . $ref->{'description'} . "\x1C\x79")
989 $fuellzeichen = ($blockcount * 256 - length($buchungssatz . $header));
990 $buchungssatz .= "\x00" x $fuellzeichen;
992 $total_bytes = ($blockcount) * 256;
994 $buchungssatz .= "\x1E" . $ref->{'description'} . "\x1C\x79";
995 $remaining_bytes = $total_bytes - length($buchungssatz . $header);
1000 print(ED $buchungssatz);
1001 $fuellzeichen = 256 - (length($header . $buchungssatz . "z") % 256);
1002 my $dateiende = "\x00" x $fuellzeichen;
1004 print(ED $dateiende);
1007 #Make EV Verwaltungsdatei
1009 $self->make_ed_versionset($header, $filename, $blockcount);
1011 my $ev_header = $self->make_ev_header($form, $fileno);
1012 my $ev_filename = $self->export_path . $evfile;
1013 push(@filenames, $evfile);
1014 open(EV, ">", $ev_filename) or die "can't open outputfile: EV01\n";
1015 print(EV $ev_header);
1017 foreach my $file (@ed_versionset) {
1018 print(EV $ed_versionset[$file]);
1022 $self->add_filenames(@filenames);
1024 $main::lxdebug->leave_sub();
1026 return { 'download_token' => $self->download_token, 'filenames' => \@filenames };
1030 clean_temporary_directories();
1041 SL::DATEV - kivitendo DATEV Export module
1045 use SL::DATEV qw(:CONSTANTS);
1047 my $datev = SL::DATEV->new(
1048 exporttype => DATEV_ET_BUCHUNGEN,
1049 format => DATEV_FORMAT_KNE,
1054 my $datev = SL::DATEV->new(
1055 exporttype => DATEV_ET_STAMM,
1056 format => DATEV_FORMAT_KNE,
1057 accnofrom => $start_account_number,
1058 accnoto => $end_account_number,
1061 # get or set datev stamm
1062 my $hashref = $datev->get_datev_stamm;
1063 $datev->save_datev_stamm($hashref);
1065 # manually clean up temporary directories
1066 $datev->clean_temporary_directories;
1071 if ($datev->errors) {
1072 die join "\n", $datev->error;
1075 # get relevant data for saving the export:
1076 my $dl_token = $datev->download_token;
1077 my $path = $datev->export_path;
1078 my @files = $datev->filenames;
1080 # retrieving an export at a later time
1081 my $datev = SL::DATEV->new(
1082 download_token => $dl_token_from_user,
1085 my $path = $datev->export_path;
1086 my @files = glob("$path/*");
1090 This module implements the DATEV export standard. For usage see above.
1098 Generic constructor. See section attributes for information about hat to pass.
1100 =item get_datev_stamm
1102 Loads DATEV Stammdaten and returns as hashref.
1104 =item save_datev_stamm HASHREF
1106 Saves DATEV Stammdaten from provided hashref.
1110 See L<CONSTANTS> for possible values
1112 =item has_exporttype
1114 Returns true if an exporttype has been set. Without exporttype most report functions won't work.
1118 Specifies the designated format of the export. Currently only KNE export is implemented.
1120 See L<CONSTANTS> for possible values
1124 Returns true if a format has been set. Without format most report functions won't work.
1126 =item download_token
1128 Returns a download token for this DATEV object.
1130 Note: If either a download_token or export_path were set at the creation these are infered, otherwise randomly generated.
1134 Returns an export_path for this DATEV object.
1136 Note: If either a download_token or export_path were set at the creation these are infered, otherwise randomly generated.
1140 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.
1142 =item net_gross_differences
1144 If there were any net gross differences during calculation they will be collected here.
1146 =item sum_net_gross_differences
1148 Sum of all differences.
1150 =item clean_temporary_directories
1152 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.
1156 Returns a list of errors that occured. If no errors occured, the export was a success.
1160 Exports data. You have to have set L<exporttype> and L<format> or an error will
1161 occur. OBE exports are currently not implemented.
1167 This is a list of attributes set in either the C<new> or a method of the same name.
1173 Set a database handle to use in the process. This allows for an export to be
1174 done on a transaction in progress without committing first.
1178 See L<CONSTANTS> for possible values. This MUST be set before export is called.
1182 See L<CONSTANTS> for possible values. This MUST be set before export is called.
1184 =item download_token
1186 Can be set on creation to retrieve a prior export for download.
1192 Set boundary dates for the export. Currently thse MUST be set for the export to work.
1198 Set boundary account numbers for the export. Only useful for a stammdaten export.
1204 =head2 Supplied to L<exporttype>
1208 =item DATEV_ET_BUCHUNGEN
1210 =item DATEV_ET_STAMM
1214 =head2 Supplied to L<format>.
1218 =item DATEV_FORMAT_KNE
1220 =item DATEV_FORMAT_OBE
1224 =head1 ERROR HANDLING
1226 This module will die in the following cases:
1232 No or unrecognized exporttype or format was provided for an export
1236 OBE rxport was called, which is not yet implemented.
1244 Errors that occur during th actual export will be collected in L<errors>. The following types can occur at the moment:
1250 C<Unbalanced Ledger!>. Exactly that, your ledger is unbalanced. Should never occur.
1254 C<Datev-Export fehlgeschlagen! Bei Transaktion %d (%f).> This error occurs if a
1255 transaction could not be reliably sorted out, or had rounding errors over the acceptable threshold.
1259 =head1 BUGS AND CAVEATS
1265 Handling of Vollvorlauf is currently not fully implemented. You must provide both from and to to get a working export.
1269 OBE export is currently not implemented.
1275 - handling of export_path and download token is a bit dodgy, clean that up.
1279 L<SL::DATEV::KNEFile>
1283 Philip Reetz E<lt>p.reetz@linet-services.deE<gt>,
1285 Moritz Bunkus E<lt>m.bunkus@linet-services.deE<gt>,
1287 Jan Büren E<lt>jan@lx-office-hosting.deE<gt>,
1289 Geoffrey Richardson E<lt>information@lx-office-hosting.deE<gt>,
1291 Sven Schöling E<lt>s.schoeling@linet-services.deE<gt>,