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., 51 Franklin Street, Fifth Floor, Boston,
23 #======================================================================
26 #======================================================================
34 use SL::DATEV::KNEFile;
36 use SL::HTML::Util ();
37 use SL::Locale::String qw(t8);
41 use Exporter qw(import);
44 use List::MoreUtils qw(any);
45 use List::Util qw(min max sum);
46 use List::UtilsBy qw(partition_by sort_by);
48 use Time::HiRes qw(gettimeofday);
53 DATEV_ET_BUCHUNGEN => $i++,
54 DATEV_ET_STAMM => $i++,
57 DATEV_FORMAT_KNE => $i++,
58 DATEV_FORMAT_OBE => $i++,
59 DATEV_FORMAT_CSV => $i++,
63 my @export_constants = qw(DATEV_ET_BUCHUNGEN DATEV_ET_STAMM DATEV_ET_CSV DATEV_FORMAT_KNE DATEV_FORMAT_OBE DATEV_FORMAT_CSV);
64 our @EXPORT_OK = (@export_constants);
65 our %EXPORT_TAGS = (CONSTANTS => [ @export_constants ]);
72 my $obj = bless {}, $class;
74 $obj->$_($data{$_}) for keys %data;
81 $self->{exporttype} = $_[0] if @_;
82 return $self->{exporttype};
86 defined $_[0]->{exporttype};
91 $self->{format} = $_[0] if @_;
92 return $self->{format};
96 defined $_[0]->{format};
99 sub _get_export_path {
100 $main::lxdebug->enter_sub();
102 my ($a, $b) = gettimeofday();
103 my $path = _get_path_for_download_token("${a}-${b}-${$}");
105 mkpath($path) unless (-d $path);
107 $main::lxdebug->leave_sub();
112 sub _get_path_for_download_token {
113 $main::lxdebug->enter_sub();
115 my $token = shift || '';
118 if ($token =~ m|^(\d+)-(\d+)-(\d+)$|) {
119 $path = $::lx_office_conf{paths}->{userspath} . "/datev-export-${1}-${2}-${3}/";
122 $main::lxdebug->leave_sub();
127 sub _get_download_token_for_path {
128 $main::lxdebug->enter_sub();
133 if ($path =~ m|.*datev-export-(\d+)-(\d+)-(\d+)/?$|) {
134 $token = "${1}-${2}-${3}";
137 $main::lxdebug->leave_sub();
144 $self->{download_token} = $_[0] if @_;
145 return $self->{download_token} ||= _get_download_token_for_path($self->export_path);
151 return $self->{export_path} ||= _get_path_for_download_token($self->{download_token}) || _get_export_path();
156 push @{ $self->{filenames} ||= [] }, @_;
160 return @{ $_[0]{filenames} || [] };
165 push @{ $self->{errors} ||= [] }, @_;
169 return @{ $_[0]{errors} || [] };
172 sub add_net_gross_differences {
174 push @{ $self->{net_gross_differences} ||= [] }, @_;
177 sub net_gross_differences {
178 return @{ $_[0]{net_gross_differences} || [] };
181 sub sum_net_gross_differences {
182 return sum $_[0]->net_gross_differences;
189 $self->{from} = $_[0];
192 return $self->{from};
209 $self->{trans_id} = $_[0];
212 die "illegal trans_id passed for DATEV export: " . $self->{trans_id} . "\n" unless $self->{trans_id} =~ m/^\d+$/;
214 return $self->{trans_id};
221 $self->{accnofrom} = $_[0];
224 return $self->{accnofrom};
231 $self->{accnoto} = $_[0];
234 return $self->{accnoto};
242 $self->{dbh} = $_[0];
243 $self->{provided_dbh} = 1;
246 $self->{dbh} ||= SL::DB->client->dbh;
253 sub clean_temporary_directories {
254 $::lxdebug->enter_sub;
256 foreach my $path (glob($::lx_office_conf{paths}->{userspath} . "/datev-export-*")) {
257 next unless -d $path;
259 my $mtime = (stat($path))[9];
260 next if ((time() - $mtime) < 8 * 60 * 60);
265 $::lxdebug->leave_sub;
269 $main::lxdebug->enter_sub();
271 my $text = shift // '';
272 my $field_len = shift;
273 my $fill_char = shift;
274 my $alignment = shift || 'right';
276 my $text_len = length $text;
278 if ($field_len < $text_len) {
279 $text = substr $text, 0, $field_len;
281 } elsif ($field_len > $text_len) {
282 my $filler = ($fill_char) x ($field_len - $text_len);
283 $text = $alignment eq 'right' ? $filler . $text : $text . $filler;
286 $main::lxdebug->leave_sub();
291 sub get_datev_stamm {
292 return $_[0]{stamm} ||= selectfirst_hashref_query($::form, $_[0]->dbh, 'SELECT * FROM datev');
295 sub save_datev_stamm {
296 my ($self, $data) = @_;
298 SL::DB->client->with_transaction(sub {
299 do_query($::form, $self->dbh, 'DELETE FROM datev');
301 my @columns = qw(beraternr beratername dfvkz mandantennr datentraegernr abrechnungsnr);
303 my $query = "INSERT INTO datev (" . join(', ', @columns) . ") VALUES (" . join(', ', ('?') x @columns) . ")";
304 do_query($::form, $self->dbh, $query, map { $data->{$_} } @columns);
306 }) or do { die SL::DB->client->error };
313 die 'no format set!' unless $self->has_format;
315 if ($self->format == DATEV_FORMAT_CSV) {
316 $result = $self->csv_export;
317 } elsif ($self->format == DATEV_FORMAT_KNE) {
318 $result = $self->kne_export;
319 } elsif ($self->format == DATEV_FORMAT_OBE) {
320 $result = $self->obe_export;
322 die 'unrecognized export format';
332 die 'no exporttype set!' unless $self->has_exporttype;
334 if ($self->exporttype == DATEV_ET_BUCHUNGEN) {
335 $result = $self->kne_buchungsexport;
336 } elsif ($self->exporttype == DATEV_ET_STAMM) {
337 $result = $self->kne_stammdatenexport;
338 } elsif ($self->exporttype == DATEV_ET_CSV) {
339 $result = $self->csv_export_for_tax_accountant;
341 die 'unrecognized exporttype';
348 die 'not yet implemented';
352 die 'not yet implemented';
358 return unless $self->from && $self->to;
360 return "transdate >= '" . $self->from->to_lxoffice . "' and transdate <= '" . $self->to->to_lxoffice . "'";
367 sub generate_datev_data {
368 $main::lxdebug->enter_sub();
370 my ($self, %params) = @_;
371 my $fromto = $params{from_to};
372 my $progress_callback = $params{progress_callback} || sub {};
374 my $form = $main::form;
376 my $trans_id_filter = '';
378 if ( $self->{trans_id} ) {
379 # ignore dates when trans_id is passed so that the entire transaction is
380 # checked, not just either the initial bookings or the subsequent payments
381 # (the transdates will likely differ)
383 $trans_id_filter = 'ac.trans_id = ' . $self->trans_id;
385 $fromto =~ s/transdate/ac\.transdate/g;
390 my $filter = ''; # Useful for debugging purposes
392 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');
395 qq|SELECT ac.acc_trans_id, ac.transdate, ac.gldate, ac.trans_id,ar.id, ac.amount, ac.taxkey, ac.memo,
396 ar.invnumber, ar.duedate, ar.amount as umsatz, ar.deliverydate, ar.itime::date,
397 ct.name, ct.ustid, ct.customernumber AS vcnumber, ct.id AS customer_id, NULL AS vendor_id,
398 c.accno, c.description AS accname, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
400 t.rate AS taxrate, t.taxdescription,
402 tc.accno AS tax_accno, tc.description AS tax_accname,
405 LEFT JOIN ar ON (ac.trans_id = ar.id)
406 LEFT JOIN customer ct ON (ar.customer_id = ct.id)
407 LEFT JOIN chart c ON (ac.chart_id = c.id)
408 LEFT JOIN tax t ON (ac.tax_id = t.id)
409 LEFT JOIN chart tc ON (t.chart_id = tc.id)
410 WHERE (ar.id IS NOT NULL)
417 SELECT ac.acc_trans_id, ac.transdate, ac.gldate, ac.trans_id,ap.id, ac.amount, ac.taxkey, ac.memo,
418 ap.invnumber, ap.duedate, ap.amount as umsatz, ap.deliverydate, ap.itime::date,
419 ct.name, ct.ustid, ct.vendornumber AS vcnumber, NULL AS customer_id, ct.id AS vendor_id,
420 c.accno, c.description AS accname, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
422 t.rate AS taxrate, t.taxdescription,
424 tc.accno AS tax_accno, tc.description AS tax_accname,
427 LEFT JOIN ap ON (ac.trans_id = ap.id)
428 LEFT JOIN vendor ct ON (ap.vendor_id = ct.id)
429 LEFT JOIN chart c ON (ac.chart_id = c.id)
430 LEFT JOIN tax t ON (ac.tax_id = t.id)
431 LEFT JOIN chart tc ON (t.chart_id = tc.id)
432 WHERE (ap.id IS NOT NULL)
439 SELECT ac.acc_trans_id, ac.transdate, ac.gldate, ac.trans_id,gl.id, ac.amount, ac.taxkey, ac.memo,
440 gl.reference AS invnumber, gl.transdate AS duedate, ac.amount as umsatz, NULL as deliverydate, gl.itime::date,
441 gl.description AS name, NULL as ustid, '' AS vcname, NULL AS customer_id, NULL AS vendor_id,
442 c.accno, c.description AS accname, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
444 t.rate AS taxrate, t.taxdescription,
446 tc.accno AS tax_accno, tc.description AS tax_accname,
449 LEFT JOIN gl ON (ac.trans_id = gl.id)
450 LEFT JOIN chart c ON (ac.chart_id = c.id)
451 LEFT JOIN tax t ON (ac.tax_id = t.id)
452 LEFT JOIN chart tc ON (t.chart_id = tc.id)
453 WHERE (gl.id IS NOT NULL)
458 ORDER BY trans_id, acc_trans_id|;
460 my $sth = prepare_execute_query($form, $self->dbh, $query);
466 while ( $continue && (my $ref = $sth->fetchrow_hashref("NAME_lc")) ) {
467 last unless $ref; # for single transactions
469 if (($counter % 500) == 0) {
470 $progress_callback->($counter);
473 my $trans = [ $ref ];
475 my $count = $ref->{amount};
478 # if the amount of a booking in a group is smaller than 0.02, any tax
479 # amounts will likely be smaller than 1 cent, so go into subcent mode
480 my $subcent = abs($count) < 0.02;
482 # records from acc_trans are ordered by trans_id and acc_trans_id
483 # first check for unbalanced ledger inside one trans_id
484 # there may be several groups inside a trans_id, e.g. the original booking and the payment
485 # each group individually should be exactly balanced and each group
486 # individually needs its own datev lines
488 # keep fetching new acc_trans lines until the end of a balanced group is reached
489 while (abs($count) > 0.01 || $firstrun || ($subcent && abs($count) > 0.005)) {
490 my $ref2 = $sth->fetchrow_hashref("NAME_lc");
496 # check if trans_id of current acc_trans line is still the same as the
497 # trans_id of the first line in group, i.e. we haven't finished a 0-group
498 # before moving on to the next trans_id, error will likely be in the old
501 if ($ref2->{trans_id} != $trans->[0]->{trans_id}) {
502 require SL::DB::Manager::AccTransaction;
503 if ( $trans->[0]->{trans_id} ) {
504 my $acc_trans_obj = SL::DB::Manager::AccTransaction->get_first(where => [ trans_id => $trans->[0]->{trans_id} ]);
505 $self->add_error(t8("Export error in transaction #1: Unbalanced ledger before next transaction (#2)",
506 $acc_trans_obj->transaction_name, $ref2->{trans_id})
512 push @{ $trans }, $ref2;
514 $count += $ref2->{amount};
518 foreach my $i (0 .. scalar(@{ $trans }) - 1) {
519 my $ref = $trans->[$i];
520 my $prev_ref = 0 < $i ? $trans->[$i - 1] : undef;
521 if ( $all_taxchart_ids{$ref->{id}}
522 && ($ref->{link} =~ m/(?:AP_tax|AR_tax)/)
523 && ( ($prev_ref && $prev_ref->{taxkey} && (_sign($ref->{amount}) == _sign($prev_ref->{amount})))
524 || $ref->{invoice})) {
528 if ( !$ref->{invoice} # we have a non-invoice booking (=gl)
529 && $ref->{is_tax} # that has "is_tax" set
530 && !($prev_ref->{is_tax}) # previous line wasn't is_tax
531 && (_sign($ref->{amount}) == _sign($prev_ref->{amount}))) { # and sign same as previous sign
532 $trans->[$i - 1]->{tax_amount} = $ref->{amount};
537 if (scalar(@{$trans}) <= 2) {
538 push @{ $self->{DATEV} }, $trans;
542 # determine at which array position the reference value (called absumsatz) is
543 # and which amount it has
545 for my $j (0 .. (scalar(@{$trans}) - 1)) {
548 # 1: gl transaction (Dialogbuchung), invoice is false, no double split booking allowed
550 # 2: sales or vendor invoice (Verkaufs- und Einkaufsrechnung): invoice is
551 # true, instead of absumsatz use link AR/AP (there should only be one
554 # 3. AR/AP transaction (Kreditoren- und Debitorenbuchung): invoice is false,
555 # instead of absumsatz use link AR/AP (there should only be one, so jump
556 # out of search as soon as you find it )
559 # for gl-bookings no split is allowed and there is no AR/AP account, so we always use the maximum value as a reference
560 # for ap/ar bookings we can always search for AR/AP in link and use that
561 if ( ( not $trans->[$j]->{'invoice'} and abs($trans->[$j]->{'amount'}) > abs($absumsatz) )
562 or ($trans->[$j]->{'invoice'} and ($trans->[$j]->{'link'} eq 'AR' or $trans->[$j]->{'link'} eq 'AP'))) {
563 $absumsatz = $trans->[$j]->{'amount'};
568 # Problem: we can't distinguish between AR and AP and normal invoices via boolean "invoice"
569 # for AR and AP transaction exit the loop as soon as an AR or AP account is found
570 # there must be only one AR or AP chart in the booking
571 # since it is possible to do this kind of things with GL too, make sure those don't get aborted in case someone
572 # manually pays an invoice in GL.
573 if ($trans->[$j]->{table} ne 'gl' and ($trans->[$j]->{'link'} eq 'AR' or $trans->[$j]->{'link'} eq 'AP')) {
574 $notsplitindex = $j; # position in booking with highest amount
575 $absumsatz = $trans->[$j]->{'amount'};
580 my $ml = ($trans->[0]->{'umsatz'} > 0) ? 1 : -1;
581 my $rounding_error = 0;
584 # go through each line and determine if it is a tax booking or not
585 # skip all tax lines and notsplitindex line
586 # push all other accounts (e.g. income or expense) with corresponding taxkey
588 for my $j (0 .. (scalar(@{$trans}) - 1)) {
589 if ( ($j != $notsplitindex)
590 && !$trans->[$j]->{is_tax}
591 && ( $trans->[$j]->{'taxkey'} eq ""
592 || $trans->[$j]->{'taxkey'} eq "0"
593 || $trans->[$j]->{'taxkey'} eq "1"
594 || $trans->[$j]->{'taxkey'} eq "10"
595 || $trans->[$j]->{'taxkey'} eq "11")) {
597 map { $new_trans{$_} = $trans->[$notsplitindex]->{$_}; } keys %{ $trans->[$notsplitindex] };
599 $absumsatz += $trans->[$j]->{'amount'};
600 $new_trans{'amount'} = $trans->[$j]->{'amount'} * (-1);
601 $new_trans{'umsatz'} = abs($trans->[$j]->{'amount'}) * $ml;
602 $trans->[$j]->{'umsatz'} = abs($trans->[$j]->{'amount'}) * $ml;
604 push @{ $self->{DATEV} }, [ \%new_trans, $trans->[$j] ];
606 } elsif (($j != $notsplitindex) && !$trans->[$j]->{is_tax}) {
609 map { $new_trans{$_} = $trans->[$notsplitindex]->{$_}; } keys %{ $trans->[$notsplitindex] };
611 my $tax_rate = $trans->[$j]->{'taxrate'};
612 $new_trans{'net_amount'} = $trans->[$j]->{'amount'} * -1;
613 $new_trans{'tax_rate'} = 1 + $tax_rate;
615 if (!$trans->[$j]->{'invoice'}) {
616 $new_trans{'amount'} = $form->round_amount(-1 * ($trans->[$j]->{amount} + $trans->[$j]->{tax_amount}), 2);
617 $new_trans{'umsatz'} = abs($new_trans{'amount'}) * $ml;
618 $trans->[$j]->{'umsatz'} = $new_trans{'umsatz'};
619 $absumsatz += -1 * $new_trans{'amount'};
622 my $unrounded = $trans->[$j]->{'amount'} * (1 + $tax_rate) * -1 + $rounding_error;
623 my $rounded = $form->round_amount($unrounded, 2);
625 $rounding_error = $unrounded - $rounded;
626 $new_trans{'amount'} = $rounded;
627 $new_trans{'umsatz'} = abs($rounded) * $ml;
628 $trans->[$j]->{'umsatz'} = $new_trans{umsatz};
629 $absumsatz -= $rounded;
632 push @{ $self->{DATEV} }, [ \%new_trans, $trans->[$j] ];
633 push @taxed, $self->{DATEV}->[-1];
639 while ((abs($absumsatz) >= 0.01) && (abs($absumsatz) < 1.00)) {
640 if ($idx >= scalar @taxed) {
641 last if (!$correction);
647 my $transaction = $taxed[$idx]->[0];
649 my $old_amount = $transaction->{amount};
650 my $old_correction = $correction;
653 if (!$transaction->{diff}) {
654 @possible_diffs = (0.01, -0.01);
656 @possible_diffs = ($transaction->{diff});
659 foreach my $diff (@possible_diffs) {
660 my $net_amount = $form->round_amount(($transaction->{amount} + $diff) / $transaction->{tax_rate}, 2);
661 next if ($net_amount != $transaction->{net_amount});
663 $transaction->{diff} = $diff;
664 $transaction->{amount} += $diff;
665 $transaction->{umsatz} += $diff;
675 $absumsatz = $form->round_amount($absumsatz, 2);
676 if (abs($absumsatz) >= (0.01 * (1 + scalar @taxed))) {
677 require SL::DB::Manager::AccTransaction;
678 my $acc_trans_obj = SL::DB::Manager::AccTransaction->get_first(where => [ trans_id => $trans->[0]->{trans_id} ]);
679 $self->add_error(t8("Export error in transaction #1: Rounding error too large #2",
680 $acc_trans_obj->transaction_name, $absumsatz)
682 } elsif (abs($absumsatz) >= 0.01) {
683 $self->add_net_gross_differences($absumsatz);
689 $::lxdebug->leave_sub;
692 sub make_kne_data_header {
693 $main::lxdebug->enter_sub();
695 my ($self, $form) = @_;
698 my $stamm = $self->get_datev_stamm;
700 my $jahr = $self->from ? $self->from->year : DateTime->today->year;
703 my $header = "\x1D\x181";
704 $header .= _fill($stamm->{datentraegernr}, 3, ' ', 'left');
705 $header .= ($self->fromto) ? "11" : "13"; # Anwendungsnummer
706 $header .= _fill($stamm->{dfvkz}, 2, '0');
707 $header .= _fill($stamm->{beraternr}, 7, '0');
708 $header .= _fill($stamm->{mandantennr}, 5, '0');
709 $header .= _fill(($stamm->{abrechnungsnr} // '') . $jahr, 6, '0');
711 $header .= $self->from ? $self->from->strftime('%d%m%y') : '';
712 $header .= $self->to ? $self->to->strftime('%d%m%y') : '';
716 $header .= $primanota;
719 $header .= _fill($stamm->{passwort}, 4, '0');
720 $header .= " " x 16; # Anwendungsinfo
721 $header .= " " x 16; # Inputinfo
725 my $versionssatz = $self->exporttype == DATEV_ET_BUCHUNGEN ? "\xB5" . "1," : "\xB6" . "1,";
727 my $query = qq|SELECT accno FROM chart LIMIT 1|;
728 my $ref = selectfirst_hashref_query($form, $self->dbh, $query);
730 $versionssatz .= length $ref->{accno};
731 $versionssatz .= ",";
732 $versionssatz .= length $ref->{accno};
733 $versionssatz .= ",SELF" . "\x1C\x79";
735 $header .= $versionssatz;
737 $main::lxdebug->leave_sub();
743 $main::lxdebug->enter_sub();
745 my ($date, $six) = @_;
747 my ($day, $month, $year) = split(/\./, $date);
750 $day = substr($day, 1, 1);
752 if (length($month) < 2) {
753 $month = "0" . $month;
755 if (length($year) > 2) {
756 $year = substr($year, -2, 2);
760 $date = $day . $month . $year;
762 $date = $day . $month;
765 $main::lxdebug->leave_sub();
770 sub trim_leading_zeroes {
778 sub make_ed_versionset {
779 $main::lxdebug->enter_sub();
781 my ($self, $header, $filename, $blockcount) = @_;
783 my $versionset = "V" . substr($filename, 2, 5);
784 $versionset .= substr($header, 6, 22);
787 $versionset .= "0000" . substr($header, 28, 19);
789 my $datum = " " x 16;
790 $versionset .= $datum . "001" . substr($header, 28, 4);
793 $versionset .= _fill($blockcount, 5, '0');
794 $versionset .= "001";
796 $versionset .= substr($header, -12, 10) . " ";
797 $versionset .= " " x 53;
799 $main::lxdebug->leave_sub();
805 $main::lxdebug->enter_sub();
807 my ($self, $form, $fileno) = @_;
809 my $stamm = $self->get_datev_stamm;
811 my $ev_header = _fill($stamm->{datentraegernr}, 3, ' ', 'left');
813 $ev_header .= _fill($stamm->{beraternr}, 7, ' ', 'left');
814 $ev_header .= _fill($stamm->{beratername}, 9, ' ', 'left');
816 $ev_header .= (_fill($fileno, 5, '0')) x 2;
817 $ev_header .= " " x 95;
819 $main::lxdebug->leave_sub();
824 sub generate_datev_lines {
827 my @datev_lines = ();
829 foreach my $transaction ( @{ $self->{DATEV} } ) {
831 # each $transaction entry contains data from several acc_trans entries
832 # belonging to the same trans_id
834 my %datev_data = (); # data for one transaction
835 my $trans_lines = scalar(@{$transaction});
843 my $buchungstext = "";
845 my $datevautomatik = 0;
850 for (my $i = 0; $i < $trans_lines; $i++) {
851 if ($trans_lines == 2) {
852 if (abs($transaction->[$i]->{'amount'}) > abs($umsatz)) {
853 $umsatz = $transaction->[$i]->{'amount'};
856 if (abs($transaction->[$i]->{'umsatz'}) > abs($umsatz)) {
857 $umsatz = $transaction->[$i]->{'umsatz'};
860 if ($transaction->[$i]->{'datevautomatik'}) {
863 if ($transaction->[$i]->{'taxkey'}) {
864 $taxkey = $transaction->[$i]->{'taxkey'};
866 if ($transaction->[$i]->{'charttax'}) {
867 $charttax = $transaction->[$i]->{'charttax'};
869 if ($transaction->[$i]->{'amount'} > 0) {
876 if ($trans_lines >= 2) {
878 $datev_data{'gegenkonto'} = $transaction->[$haben]->{'accno'};
879 $datev_data{'konto'} = $transaction->[$soll]->{'accno'};
880 if ($transaction->[$haben]->{'invnumber'} ne "") {
881 $datev_data{belegfeld1} = $transaction->[$haben]->{'invnumber'};
883 $datev_data{datum} = $transaction->[$haben]->{'transdate'};
884 $datev_data{waehrung} = 'EUR';
886 if ($transaction->[$haben]->{'name'} ne "") {
887 $datev_data{buchungstext} = $transaction->[$haben]->{'name'};
889 if (($transaction->[$haben]->{'ustid'} // '') ne "") {
890 $datev_data{ustid} = $transaction->[$haben]->{'ustid'};
892 if (($transaction->[$haben]->{'duedate'} // '') ne "") {
893 $datev_data{belegfeld2} = $transaction->[$haben]->{'duedate'};
897 $datev_data{umsatz} = abs($umsatz); # sales invoices without tax have a different sign???
899 # Dies ist die einzige Stelle die datevautomatik auswertet. Was soll gesagt werden?
900 # Im Prinzip hat jeder acc_trans Eintrag einen Steuerschlüssel, außer, bei gewissen Fällen
901 # wie: Kreditorenbuchung mit negativen Vorzeichen, SEPA-Export oder Rechnungen die per
902 # Skript angelegt werden.
903 # Also falls ein Steuerschlüssel da ist und NICHT datevautomatik diesen Block hinzufügen.
904 # Oder aber datevautomatik ist WAHR, aber der Steuerschlüssel in der acc_trans weicht
905 # von dem in der Chart ab: Also wahrscheinlich Programmfehler (NULL übergeben, statt
906 # DATEV-Steuerschlüssel) oder der Steuerschlüssel des Kontos weicht WIRKLICH von dem Eintrag in der
907 # acc_trans ab. Gibt es für diesen Fall eine plausiblen Grund?
910 # only set buchungsschluessel if the following conditions are met:
911 if ( ( $datevautomatik || $taxkey)
912 && (!$datevautomatik || ($datevautomatik && ($charttax ne $taxkey)))) {
913 # $datev_data{buchungsschluessel} = !$datevautomatik ? $taxkey : "4";
914 $datev_data{buchungsschluessel} = $taxkey;
917 push(@datev_lines, \%datev_data);
920 # example of modifying export data:
921 # foreach my $datev_line ( @datev_lines ) {
922 # if ( $datev_line{"konto"} eq '1234' ) {
923 # $datev_line{"konto"} = '9999';
928 return \@datev_lines;
932 sub kne_buchungsexport {
933 $main::lxdebug->enter_sub();
941 my $filename = "ED00001";
945 my $ed_filename = $self->export_path . $filename;
947 my $fromto = $self->fromto;
949 $self->generate_datev_data(from_to => $self->fromto); # fetches data from db, transforms data and fills $self->{DATEV}
950 return if $self->errors;
952 my @datev_lines = @{ $self->generate_datev_lines };
955 my $umsatzsumme = sum map { $_->{umsatz} } @datev_lines;
957 # prepare kne file, everything gets stored in ED00001
958 my $header = $self->make_kne_data_header($form);
959 my $kne_file = SL::DATEV::KNEFile->new();
960 $kne_file->add_block($header);
962 my $iconv = $::locale->{iconv_utf8};
963 my %umlaute = ($iconv->convert('ä') => 'ae',
964 $iconv->convert('ö') => 'oe',
965 $iconv->convert('ü') => 'ue',
966 $iconv->convert('Ä') => 'Ae',
967 $iconv->convert('Ö') => 'Oe',
968 $iconv->convert('Ü') => 'Ue',
969 $iconv->convert('ß') => 'sz');
971 # add the data from @datev_lines to the kne_file, formatting as needed
972 foreach my $kne ( @datev_lines ) {
973 $kne_file->add_block("+" . $kne_file->format_amount(abs($kne->{umsatz}), 0));
975 # only add buchungsschluessel if it was previously defined
976 $kne_file->add_block("\x6C" . $kne->{buchungsschluessel}) if defined $kne->{buchungsschluessel};
978 # ($kne->{gegenkonto}) = $kne->{gegenkonto} =~ /^(\d+)/;
979 $kne_file->add_block("a" . trim_leading_zeroes($kne->{gegenkonto}));
981 if ( $kne->{belegfeld1} ) {
982 my $invnumber = $kne->{belegfeld1};
983 foreach my $umlaut (keys(%umlaute)) {
984 $invnumber =~ s/${umlaut}/${umlaute{$umlaut}}/g;
986 $invnumber =~ s/[^0-9A-Za-z\$\%\&\*\+\-\/]//g;
987 $invnumber = substr($invnumber, 0, 12);
988 $invnumber =~ s/\ *$//;
989 $kne_file->add_block("\xBD" . $invnumber . "\x1C");
992 $kne_file->add_block("\xBE" . &datetofour($kne->{belegfeld2},1) . "\x1C");
994 $kne_file->add_block("d" . &datetofour($kne->{datum},0));
996 # ($kne->{konto}) = $kne->{konto} =~ /^(\d+)/;
997 $kne_file->add_block("e" . trim_leading_zeroes($kne->{konto}));
999 my $name = $kne->{buchungstext};
1000 foreach my $umlaut (keys(%umlaute)) {
1001 $name =~ s/${umlaut}/${umlaute{$umlaut}}/g;
1003 $name =~ s/[^0-9A-Za-z\$\%\&\*\+\-\ \/]//g;
1004 $name = substr($name, 0, 30);
1006 $kne_file->add_block("\x1E" . $name . "\x1C");
1008 $kne_file->add_block("\xBA" . $kne->{'ustid'} . "\x1C") if $kne->{'ustid'};
1010 $kne_file->add_block("\xB3" . $kne->{'waehrung'} . "\x1C" . "\x79");
1013 $umsatzsumme = $kne_file->format_amount(abs($umsatzsumme), 0);
1014 my $mandantenendsumme = "x" . $kne_file->format_amount($umsatzsumme / 100.0, 14) . "\x79\x7a";
1016 $kne_file->add_block($mandantenendsumme);
1019 open(ED, ">", $ed_filename) or die "can't open outputfile: $!\n";
1020 print(ED $kne_file->get_data());
1023 $ed_versionset[$fileno] = $self->make_ed_versionset($header, $filename, $kne_file->get_block_count());
1025 #Make EV Verwaltungsdatei
1026 my $ev_header = $self->make_ev_header($form, $fileno);
1027 my $ev_filename = $self->export_path . $evfile;
1028 push(@filenames, $evfile);
1029 open(EV, ">", $ev_filename) or die "can't open outputfile: EV01\n";
1030 print(EV $ev_header);
1032 foreach my $file (@ed_versionset) {
1038 $self->add_filenames(@filenames);
1040 $main::lxdebug->leave_sub();
1042 return { 'download_token' => $self->download_token, 'filenames' => \@filenames };
1045 sub kne_stammdatenexport {
1046 $main::lxdebug->enter_sub();
1051 $self->get_datev_stamm->{abrechnungsnr} = "99";
1055 my $filename = "ED00000";
1056 my $evfile = "EV01";
1061 my $remaining_bytes = 256;
1062 my $total_bytes = 256;
1063 my $buchungssatz = "";
1065 my $ed_filename = $self->export_path . $filename;
1066 push(@filenames, $filename);
1067 open(ED, ">", $ed_filename) or die "can't open outputfile: $!\n";
1068 my $header = $self->make_kne_data_header($form);
1069 $remaining_bytes -= length($header);
1073 my (@where, @values) = ((), ());
1074 if ($self->accnofrom) {
1075 push @where, 'c.accno >= ?';
1076 push @values, $self->accnofrom;
1078 if ($self->accnoto) {
1079 push @where, 'c.accno <= ?';
1080 push @values, $self->accnoto;
1083 my $where_str = @where ? ' WHERE ' . join(' AND ', map { "($_)" } @where) : '';
1085 my $query = qq|SELECT c.accno, c.description
1090 my $sth = $self->dbh->prepare($query);
1091 $sth->execute(@values) || $form->dberror($query);
1093 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
1094 if (($remaining_bytes - length("t" . $ref->{'accno'})) <= 6) {
1095 $fuellzeichen = ($blockcount * 256 - length($buchungssatz . $header));
1096 $buchungssatz .= "\x00" x $fuellzeichen;
1098 $total_bytes = ($blockcount) * 256;
1100 $buchungssatz .= "t" . $ref->{'accno'};
1101 $remaining_bytes = $total_bytes - length($buchungssatz . $header);
1102 $ref->{'description'} =~ s/[^0-9A-Za-z\$\%\&\*\+\-\/]//g;
1103 $ref->{'description'} = substr($ref->{'description'}, 0, 40);
1104 $ref->{'description'} =~ s/\ *$//;
1107 ($remaining_bytes - length("\x1E" . $ref->{'description'} . "\x1C\x79")
1110 $fuellzeichen = ($blockcount * 256 - length($buchungssatz . $header));
1111 $buchungssatz .= "\x00" x $fuellzeichen;
1113 $total_bytes = ($blockcount) * 256;
1115 $buchungssatz .= "\x1E" . $ref->{'description'} . "\x1C\x79";
1116 $remaining_bytes = $total_bytes - length($buchungssatz . $header);
1121 print(ED $buchungssatz);
1122 $fuellzeichen = 256 - (length($header . $buchungssatz . "z") % 256);
1123 my $dateiende = "\x00" x $fuellzeichen;
1125 print(ED $dateiende);
1128 #Make EV Verwaltungsdatei
1130 $self->make_ed_versionset($header, $filename, $blockcount);
1132 my $ev_header = $self->make_ev_header($form, $fileno);
1133 my $ev_filename = $self->export_path . $evfile;
1134 push(@filenames, $evfile);
1135 open(EV, ">", $ev_filename) or die "can't open outputfile: EV01\n";
1136 print(EV $ev_header);
1138 foreach my $file (@ed_versionset) {
1139 print(EV $ed_versionset[$file]);
1143 $self->add_filenames(@filenames);
1145 $main::lxdebug->leave_sub();
1147 return { 'download_token' => $self->download_token, 'filenames' => \@filenames };
1152 return $accno . ('0' x (6 - min(length($accno), 6)));
1155 sub csv_export_for_tax_accountant {
1158 $self->_get_transactions(from_to => $self->fromto);
1160 foreach my $transaction (@{ $self->{DATEV} }) {
1161 foreach my $entry (@{ $transaction }) {
1162 $entry->{sortkey} = join '-', map { lc } (DateTime->from_kivitendo($entry->{transdate})->strftime('%Y%m%d'), $entry->{name}, $entry->{reference});
1167 partition_by { $_->[0]->{table} }
1168 sort_by { $_->[0]->{sortkey} }
1169 grep { 2 == scalar(@{ $_ }) }
1170 @{ $self->{DATEV} };
1173 acc_trans_id => { 'text' => $::locale->text('ID'), },
1174 amount => { 'text' => $::locale->text('Amount'), },
1175 credit_accname => { 'text' => $::locale->text('Credit Account Name'), },
1176 credit_accno => { 'text' => $::locale->text('Credit Account'), },
1177 debit_accname => { 'text' => $::locale->text('Debit Account Name'), },
1178 debit_accno => { 'text' => $::locale->text('Debit Account'), },
1179 invnumber => { 'text' => $::locale->text('Reference'), },
1180 name => { 'text' => $::locale->text('Name'), },
1181 notes => { 'text' => $::locale->text('Notes'), },
1182 tax => { 'text' => $::locale->text('Tax'), },
1183 taxkey => { 'text' => $::locale->text('Taxkey'), },
1184 tax_accname => { 'text' => $::locale->text('Tax Account Name'), },
1185 tax_accno => { 'text' => $::locale->text('Tax Account'), },
1186 transdate => { 'text' => $::locale->text('Invoice Date'), },
1187 vcnumber => { 'text' => $::locale->text('Customer/Vendor Number'), },
1191 acc_trans_id name vcnumber
1192 transdate invnumber amount
1193 debit_accno debit_accname
1194 credit_accno credit_accname
1196 tax_accno tax_accname taxkey
1200 my %filenames_by_type = (
1201 ar => $::locale->text('AR Transactions'),
1202 ap => $::locale->text('AP Transactions'),
1203 gl => $::locale->text('GL Transactions'),
1207 foreach my $type (qw(ap ar)) {
1211 filename => sprintf('%s %s - %s.csv', $filenames_by_type{$type}, $self->from->to_kivitendo, $self->to->to_kivitendo),
1212 csv => Text::CSV_XS->new({
1220 filename => sprintf('Zahlungen %s %s - %s.csv', $filenames_by_type{$type}, $self->from->to_kivitendo, $self->to->to_kivitendo),
1221 csv => Text::CSV_XS->new({
1229 foreach my $csv (values %csvs) {
1230 $csv->{out} = IO::File->new($self->export_path . '/' . $csv->{filename}, '>:encoding(utf8)') ;
1231 $csv->{csv}->print($csv->{out}, [ map { $column_defs{$_}->{text} } @columns ]);
1233 push @filenames, $csv->{filename};
1236 foreach my $transaction (@{ $transactions{$type} }) {
1237 my $is_payment = any { $_->{link} =~ m{A[PR]_paid} } @{ $transaction };
1238 my $csv = $is_payment ? $csvs{payments} : $csvs{invoices};
1240 my ($soll, $haben) = map { $transaction->[$_] } ($transaction->[0]->{amount} > 0 ? (1, 0) : (0, 1));
1241 my $tax = defined($soll->{tax_accno}) ? $soll : $haben;
1242 my $amount = defined($soll->{net_amount}) ? $soll : $haben;
1243 $haben->{notes} = ($haben->{memo} || $soll->{memo}) if $is_payment;
1244 $haben->{notes} //= '';
1245 $haben->{notes} = SL::HTML::Util->strip($haben->{notes});
1246 $haben->{notes} =~ s{\r}{}g;
1247 $haben->{notes} =~ s{\n+}{ }g;
1250 amount => $::form->format_amount({ numberformat => '1000,00' }, abs($amount->{amount}), 2),
1251 debit_accno => _format_accno($soll->{accno}),
1252 debit_accname => $soll->{accname},
1253 credit_accno => _format_accno($haben->{accno}),
1254 credit_accname => $haben->{accname},
1255 tax => $::form->format_amount({ numberformat => '1000,00' }, abs($amount->{amount}) - abs($amount->{net_amount}), 2),
1256 notes => $haben->{notes},
1257 (map { ($_ => $tax->{$_}) } qw(taxkey tax_accname tax_accno)),
1258 (map { ($_ => ($haben->{$_} // $soll->{$_})) } qw(acc_trans_id invnumber name vcnumber transdate)),
1261 $csv->{csv}->print($csv->{out}, [ map { $row{$_} } @columns ]);
1264 $_->{out}->close for values %csvs;
1267 $self->add_filenames(@filenames);
1269 return { download_token => $self->download_token, filenames => \@filenames };
1273 clean_temporary_directories();
1284 SL::DATEV - kivitendo DATEV Export module
1288 use SL::DATEV qw(:CONSTANTS);
1290 my $startdate = DateTime->new(year => 2014, month => 9, day => 1);
1291 my $enddate = DateTime->new(year => 2014, month => 9, day => 31);
1292 my $datev = SL::DATEV->new(
1293 exporttype => DATEV_ET_BUCHUNGEN,
1294 format => DATEV_FORMAT_KNE,
1299 # To only export transactions from a specific trans_id: (from and to are ignored)
1300 my $invoice = SL::DB::Manager::Invoice->find_by( invnumber => '216' );
1301 my $datev = SL::DATEV->new(
1302 exporttype => DATEV_ET_BUCHUNGEN,
1303 format => DATEV_FORMAT_KNE,
1304 trans_id => $invoice->trans_id,
1307 my $datev = SL::DATEV->new(
1308 exporttype => DATEV_ET_STAMM,
1309 format => DATEV_FORMAT_KNE,
1310 accnofrom => $start_account_number,
1311 accnoto => $end_account_number,
1314 # get or set datev stamm
1315 my $hashref = $datev->get_datev_stamm;
1316 $datev->save_datev_stamm($hashref);
1318 # manually clean up temporary directories older than 8 hours
1319 $datev->clean_temporary_directories;
1324 if ($datev->errors) {
1325 die join "\n", $datev->error;
1328 # get relevant data for saving the export:
1329 my $dl_token = $datev->download_token;
1330 my $path = $datev->export_path;
1331 my @files = $datev->filenames;
1333 # retrieving an export at a later time
1334 my $datev = SL::DATEV->new(
1335 download_token => $dl_token_from_user,
1338 my $path = $datev->export_path;
1339 my @files = glob("$path/*");
1341 # Only test the datev data of a specific trans_id, without generating an
1342 # export file, but filling $datev->errors if errors exist
1344 my $datev = SL::DATEV->new(
1345 trans_id => $invoice->trans_id,
1347 $datev->generate_datev_data;
1348 # if ($datev->errors) { ...
1353 This module implements the DATEV export standard. For usage see above.
1361 Generic constructor. See section attributes for information about what to pass.
1363 =item generate_datev_data
1365 Fetches all transactions from the database (via a trans_id or a date range),
1366 and does an initial transformation (e.g. filters out tax, determines
1367 the brutto amount, checks split transactions ...) and stores this data in
1370 If any errors are found these are collected in $self->errors.
1372 This function is needed for all the exports, but can be also called
1373 independently in order to check transactions for DATEV compatibility.
1375 =item generate_datev_lines
1377 Parse the data in $self->{DATEV} and transform it into a format that can be
1378 used by DATEV, e.g. determines Konto and Gegenkonto, the taxkey, ...
1380 The transformed data is returned as an arrayref, which is ready to be converted
1381 to a DATEV data format, e.g. KNE, OBE, CSV, ...
1383 At this stage the "DATEV rule" has already been applied to the taxkeys, i.e.
1384 entries with datevautomatik have an empty taxkey, as the taxkey is already
1385 determined by the chart.
1387 =item get_datev_stamm
1389 Loads DATEV Stammdaten and returns as hashref.
1391 =item save_datev_stamm HASHREF
1393 Saves DATEV Stammdaten from provided hashref.
1397 See L<CONSTANTS> for possible values
1399 =item has_exporttype
1401 Returns true if an exporttype has been set. Without exporttype most report functions won't work.
1405 Specifies the designated format of the export. Currently only KNE export is implemented.
1407 See L<CONSTANTS> for possible values
1411 Returns true if a format has been set. Without format most report functions won't work.
1413 =item download_token
1415 Returns a download token for this DATEV object.
1417 Note: If either a download_token or export_path were set at the creation these are infered, otherwise randomly generated.
1421 Returns an export_path for this DATEV object.
1423 Note: If either a download_token or export_path were set at the creation these are infered, otherwise randomly generated.
1427 Returns a list of filenames generated by this DATEV object. This only works if the files were generated during its lifetime, not if the object was created from a download_token.
1429 =item net_gross_differences
1431 If there were any net gross differences during calculation they will be collected here.
1433 =item sum_net_gross_differences
1435 Sum of all differences.
1437 =item clean_temporary_directories
1439 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.
1443 Returns a list of errors that occured. If no errors occured, the export was a success.
1447 Exports data. You have to have set L<exporttype> and L<format> or an error will
1448 occur. OBE exports are currently not implemented.
1454 This is a list of attributes set in either the C<new> or a method of the same name.
1460 Set a database handle to use in the process. This allows for an export to be
1461 done on a transaction in progress without committing first.
1463 Note: If you don't want this code to commit, simply providing a dbh is not
1464 enough enymore. You'll have to wrap the call into a transaction yourself, so
1465 that the internal transaction does not commit.
1469 See L<CONSTANTS> for possible values. This MUST be set before export is called.
1473 See L<CONSTANTS> for possible values. This MUST be set before export is called.
1475 =item download_token
1477 Can be set on creation to retrieve a prior export for download.
1483 Set boundary dates for the export. Unless a trans_id is passed these MUST be
1484 set for the export to work.
1488 To check only one gl/ar/ap transaction, pass the trans_id. The attributes
1489 L<from> and L<to> are currently still needed for the query to be assembled
1496 Set boundary account numbers for the export. Only useful for a stammdaten export.
1502 =head2 Supplied to L<exporttype>
1506 =item DATEV_ET_BUCHUNGEN
1508 =item DATEV_ET_STAMM
1512 =head2 Supplied to L<format>.
1516 =item DATEV_FORMAT_KNE
1518 =item DATEV_FORMAT_OBE
1522 =head1 ERROR HANDLING
1524 This module will die in the following cases:
1530 No or unrecognized exporttype or format was provided for an export
1534 OBE export was called, which is not yet implemented.
1542 Errors that occur during th actual export will be collected in L<errors>. The following types can occur at the moment:
1548 C<Unbalanced Ledger!>. Exactly that, your ledger is unbalanced. Should never occur.
1552 C<Datev-Export fehlgeschlagen! Bei Transaktion %d (%f).> This error occurs if a
1553 transaction could not be reliably sorted out, or had rounding errors above the acceptable threshold.
1557 =head1 BUGS AND CAVEATS
1563 Handling of Vollvorlauf is currently not fully implemented. You must provide both from and to in order to get a working export.
1567 OBE export is currently not implemented.
1573 - handling of export_path and download token is a bit dodgy, clean that up.
1577 L<SL::DATEV::KNEFile>
1581 Philip Reetz E<lt>p.reetz@linet-services.deE<gt>,
1583 Moritz Bunkus E<lt>m.bunkus@linet-services.deE<gt>,
1585 Jan Büren E<lt>jan@lx-office-hosting.deE<gt>,
1587 Geoffrey Richardson E<lt>information@lx-office-hosting.deE<gt>,
1589 Sven Schöling E<lt>s.schoeling@linet-services.deE<gt>,