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 = '';
377 my $ar_department_id_filter = '';
378 my $ap_department_id_filter = '';
379 my $gl_department_id_filter = '';
380 if ( $form->{department_id} ) {
381 $ar_department_id_filter = " AND ar.department_id = ? ";
382 $ap_department_id_filter = " AND ap.department_id = ? ";
383 $gl_department_id_filter = " AND gl.department_id = ? ";
386 my ($gl_itime_filter, $ar_itime_filter, $ap_itime_filter);
387 if ( $form->{gldatefrom} ) {
388 $gl_itime_filter = " AND gl.itime >= ? ";
389 $ar_itime_filter = " AND ar.itime >= ? ";
390 $ap_itime_filter = " AND ap.itime >= ? ";
392 $gl_itime_filter = "";
393 $ar_itime_filter = "";
394 $ap_itime_filter = "";
397 if ( $self->{trans_id} ) {
398 # ignore dates when trans_id is passed so that the entire transaction is
399 # checked, not just either the initial bookings or the subsequent payments
400 # (the transdates will likely differ)
402 $trans_id_filter = 'ac.trans_id = ' . $self->trans_id;
404 $fromto =~ s/transdate/ac\.transdate/g;
409 my $filter = ''; # Useful for debugging purposes
411 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');
414 qq|SELECT ac.acc_trans_id, ac.transdate, ac.gldate, ac.trans_id,ar.id, ac.amount, ac.taxkey, ac.memo,
415 ar.invnumber, ar.duedate, ar.amount as umsatz, ar.deliverydate, ar.itime::date,
416 ct.name, ct.ustid, ct.customernumber AS vcnumber, ct.id AS customer_id, NULL AS vendor_id,
417 c.accno, c.description AS accname, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
419 t.rate AS taxrate, t.taxdescription,
421 tc.accno AS tax_accno, tc.description AS tax_accname,
424 project.projectnumber as projectnumber, project.description as projectdescription,
425 department.description as departmentdescription
427 LEFT JOIN ar ON (ac.trans_id = ar.id)
428 LEFT JOIN customer ct ON (ar.customer_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 LEFT JOIN department ON (department.id = ar.department_id)
433 LEFT JOIN project ON (project.id = ar.globalproject_id)
434 WHERE (ar.id IS NOT NULL)
438 $ar_department_id_filter
443 SELECT ac.acc_trans_id, ac.transdate, ac.gldate, ac.trans_id,ap.id, ac.amount, ac.taxkey, ac.memo,
444 ap.invnumber, ap.duedate, ap.amount as umsatz, ap.deliverydate, ap.itime::date,
445 ct.name, ct.ustid, ct.vendornumber AS vcnumber, NULL AS customer_id, ct.id AS vendor_id,
446 c.accno, c.description AS accname, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
448 t.rate AS taxrate, t.taxdescription,
450 tc.accno AS tax_accno, tc.description AS tax_accname,
453 project.projectnumber as projectnumber, project.description as projectdescription,
454 department.description as departmentdescription
456 LEFT JOIN ap ON (ac.trans_id = ap.id)
457 LEFT JOIN vendor ct ON (ap.vendor_id = ct.id)
458 LEFT JOIN chart c ON (ac.chart_id = c.id)
459 LEFT JOIN tax t ON (ac.tax_id = t.id)
460 LEFT JOIN chart tc ON (t.chart_id = tc.id)
461 LEFT JOIN department ON (department.id = ap.department_id)
462 LEFT JOIN project ON (project.id = ap.globalproject_id)
463 WHERE (ap.id IS NOT NULL)
467 $ap_department_id_filter
472 SELECT ac.acc_trans_id, ac.transdate, ac.gldate, ac.trans_id,gl.id, ac.amount, ac.taxkey, ac.memo,
473 gl.reference AS invnumber, gl.transdate AS duedate, ac.amount as umsatz, NULL as deliverydate, gl.itime::date,
474 gl.description AS name, NULL as ustid, '' AS vcname, NULL AS customer_id, NULL AS vendor_id,
475 c.accno, c.description AS accname, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
477 t.rate AS taxrate, t.taxdescription,
479 tc.accno AS tax_accno, tc.description AS tax_accname,
482 '' as projectnumber, '' as projectdescription,
483 department.description as departmentdescription
485 LEFT JOIN gl ON (ac.trans_id = gl.id)
486 LEFT JOIN chart c ON (ac.chart_id = c.id)
487 LEFT JOIN tax t ON (ac.tax_id = t.id)
488 LEFT JOIN chart tc ON (t.chart_id = tc.id)
489 LEFT JOIN department ON (department.id = gl.department_id)
490 WHERE (gl.id IS NOT NULL)
494 $gl_department_id_filter
497 ORDER BY trans_id, acc_trans_id|;
500 if ( $form->{gldatefrom} or $form->{department_id} ) {
503 if ( $form->{gldatefrom} ) {
504 my $glfromdate = $::locale->parse_date_to_object($form->{gldatefrom});
505 die "illegal data" unless ref($glfromdate) eq 'DateTime';
506 push(@query_args, $glfromdate);
508 if ( $form->{department_id} ) {
509 push(@query_args, $form->{department_id});
514 my $sth = prepare_execute_query($form, $self->dbh, $query, @query_args);
520 while ( $continue && (my $ref = $sth->fetchrow_hashref("NAME_lc")) ) {
521 last unless $ref; # for single transactions
523 if (($counter % 500) == 0) {
524 $progress_callback->($counter);
527 my $trans = [ $ref ];
529 my $count = $ref->{amount};
532 # if the amount of a booking in a group is smaller than 0.02, any tax
533 # amounts will likely be smaller than 1 cent, so go into subcent mode
534 my $subcent = abs($count) < 0.02;
536 # records from acc_trans are ordered by trans_id and acc_trans_id
537 # first check for unbalanced ledger inside one trans_id
538 # there may be several groups inside a trans_id, e.g. the original booking and the payment
539 # each group individually should be exactly balanced and each group
540 # individually needs its own datev lines
542 # keep fetching new acc_trans lines until the end of a balanced group is reached
543 while (abs($count) > 0.01 || $firstrun || ($subcent && abs($count) > 0.005)) {
544 my $ref2 = $sth->fetchrow_hashref("NAME_lc");
550 # check if trans_id of current acc_trans line is still the same as the
551 # trans_id of the first line in group, i.e. we haven't finished a 0-group
552 # before moving on to the next trans_id, error will likely be in the old
555 if ($ref2->{trans_id} != $trans->[0]->{trans_id}) {
556 require SL::DB::Manager::AccTransaction;
557 if ( $trans->[0]->{trans_id} ) {
558 my $acc_trans_obj = SL::DB::Manager::AccTransaction->get_first(where => [ trans_id => $trans->[0]->{trans_id} ]);
559 $self->add_error(t8("Export error in transaction #1: Unbalanced ledger before next transaction (#2)",
560 $acc_trans_obj->transaction_name, $ref2->{trans_id})
566 push @{ $trans }, $ref2;
568 $count += $ref2->{amount};
572 foreach my $i (0 .. scalar(@{ $trans }) - 1) {
573 my $ref = $trans->[$i];
574 my $prev_ref = 0 < $i ? $trans->[$i - 1] : undef;
575 if ( $all_taxchart_ids{$ref->{id}}
576 && ($ref->{link} =~ m/(?:AP_tax|AR_tax)/)
577 && ( ($prev_ref && $prev_ref->{taxkey} && (_sign($ref->{amount}) == _sign($prev_ref->{amount})))
578 || $ref->{invoice})) {
582 if ( !$ref->{invoice} # we have a non-invoice booking (=gl)
583 && $ref->{is_tax} # that has "is_tax" set
584 && !($prev_ref->{is_tax}) # previous line wasn't is_tax
585 && (_sign($ref->{amount}) == _sign($prev_ref->{amount}))) { # and sign same as previous sign
586 $trans->[$i - 1]->{tax_amount} = $ref->{amount};
591 if (scalar(@{$trans}) <= 2) {
592 push @{ $self->{DATEV} }, $trans;
596 # determine at which array position the reference value (called absumsatz) is
597 # and which amount it has
599 for my $j (0 .. (scalar(@{$trans}) - 1)) {
602 # 1: gl transaction (Dialogbuchung), invoice is false, no double split booking allowed
604 # 2: sales or vendor invoice (Verkaufs- und Einkaufsrechnung): invoice is
605 # true, instead of absumsatz use link AR/AP (there should only be one
608 # 3. AR/AP transaction (Kreditoren- und Debitorenbuchung): invoice is false,
609 # instead of absumsatz use link AR/AP (there should only be one, so jump
610 # out of search as soon as you find it )
613 # for gl-bookings no split is allowed and there is no AR/AP account, so we always use the maximum value as a reference
614 # for ap/ar bookings we can always search for AR/AP in link and use that
615 if ( ( not $trans->[$j]->{'invoice'} and abs($trans->[$j]->{'amount'}) > abs($absumsatz) )
616 or ($trans->[$j]->{'invoice'} and ($trans->[$j]->{'link'} eq 'AR' or $trans->[$j]->{'link'} eq 'AP'))) {
617 $absumsatz = $trans->[$j]->{'amount'};
622 # Problem: we can't distinguish between AR and AP and normal invoices via boolean "invoice"
623 # for AR and AP transaction exit the loop as soon as an AR or AP account is found
624 # there must be only one AR or AP chart in the booking
625 # since it is possible to do this kind of things with GL too, make sure those don't get aborted in case someone
626 # manually pays an invoice in GL.
627 if ($trans->[$j]->{table} ne 'gl' and ($trans->[$j]->{'link'} eq 'AR' or $trans->[$j]->{'link'} eq 'AP')) {
628 $notsplitindex = $j; # position in booking with highest amount
629 $absumsatz = $trans->[$j]->{'amount'};
634 my $ml = ($trans->[0]->{'umsatz'} > 0) ? 1 : -1;
635 my $rounding_error = 0;
638 # go through each line and determine if it is a tax booking or not
639 # skip all tax lines and notsplitindex line
640 # push all other accounts (e.g. income or expense) with corresponding taxkey
642 for my $j (0 .. (scalar(@{$trans}) - 1)) {
643 if ( ($j != $notsplitindex)
644 && !$trans->[$j]->{is_tax}
645 && ( $trans->[$j]->{'taxkey'} eq ""
646 || $trans->[$j]->{'taxkey'} eq "0"
647 || $trans->[$j]->{'taxkey'} eq "1"
648 || $trans->[$j]->{'taxkey'} eq "10"
649 || $trans->[$j]->{'taxkey'} eq "11")) {
651 map { $new_trans{$_} = $trans->[$notsplitindex]->{$_}; } keys %{ $trans->[$notsplitindex] };
653 $absumsatz += $trans->[$j]->{'amount'};
654 $new_trans{'amount'} = $trans->[$j]->{'amount'} * (-1);
655 $new_trans{'umsatz'} = abs($trans->[$j]->{'amount'}) * $ml;
656 $trans->[$j]->{'umsatz'} = abs($trans->[$j]->{'amount'}) * $ml;
658 push @{ $self->{DATEV} }, [ \%new_trans, $trans->[$j] ];
660 } elsif (($j != $notsplitindex) && !$trans->[$j]->{is_tax}) {
663 map { $new_trans{$_} = $trans->[$notsplitindex]->{$_}; } keys %{ $trans->[$notsplitindex] };
665 my $tax_rate = $trans->[$j]->{'taxrate'};
666 $new_trans{'net_amount'} = $trans->[$j]->{'amount'} * -1;
667 $new_trans{'tax_rate'} = 1 + $tax_rate;
669 if (!$trans->[$j]->{'invoice'}) {
670 $new_trans{'amount'} = $form->round_amount(-1 * ($trans->[$j]->{amount} + $trans->[$j]->{tax_amount}), 2);
671 $new_trans{'umsatz'} = abs($new_trans{'amount'}) * $ml;
672 $trans->[$j]->{'umsatz'} = $new_trans{'umsatz'};
673 $absumsatz += -1 * $new_trans{'amount'};
676 my $unrounded = $trans->[$j]->{'amount'} * (1 + $tax_rate) * -1 + $rounding_error;
677 my $rounded = $form->round_amount($unrounded, 2);
679 $rounding_error = $unrounded - $rounded;
680 $new_trans{'amount'} = $rounded;
681 $new_trans{'umsatz'} = abs($rounded) * $ml;
682 $trans->[$j]->{'umsatz'} = $new_trans{umsatz};
683 $absumsatz -= $rounded;
686 push @{ $self->{DATEV} }, [ \%new_trans, $trans->[$j] ];
687 push @taxed, $self->{DATEV}->[-1];
693 while ((abs($absumsatz) >= 0.01) && (abs($absumsatz) < 1.00)) {
694 if ($idx >= scalar @taxed) {
695 last if (!$correction);
701 my $transaction = $taxed[$idx]->[0];
703 my $old_amount = $transaction->{amount};
704 my $old_correction = $correction;
707 if (!$transaction->{diff}) {
708 @possible_diffs = (0.01, -0.01);
710 @possible_diffs = ($transaction->{diff});
713 foreach my $diff (@possible_diffs) {
714 my $net_amount = $form->round_amount(($transaction->{amount} + $diff) / $transaction->{tax_rate}, 2);
715 next if ($net_amount != $transaction->{net_amount});
717 $transaction->{diff} = $diff;
718 $transaction->{amount} += $diff;
719 $transaction->{umsatz} += $diff;
729 $absumsatz = $form->round_amount($absumsatz, 2);
730 if (abs($absumsatz) >= (0.01 * (1 + scalar @taxed))) {
731 require SL::DB::Manager::AccTransaction;
732 my $acc_trans_obj = SL::DB::Manager::AccTransaction->get_first(where => [ trans_id => $trans->[0]->{trans_id} ]);
733 $self->add_error(t8("Export error in transaction #1: Rounding error too large #2",
734 $acc_trans_obj->transaction_name, $absumsatz)
736 } elsif (abs($absumsatz) >= 0.01) {
737 $self->add_net_gross_differences($absumsatz);
743 $::lxdebug->leave_sub;
746 sub make_kne_data_header {
747 $main::lxdebug->enter_sub();
749 my ($self, $form) = @_;
752 my $stamm = $self->get_datev_stamm;
754 my $jahr = $self->from ? $self->from->year : DateTime->today->year;
757 my $header = "\x1D\x181";
758 $header .= _fill($stamm->{datentraegernr}, 3, ' ', 'left');
759 $header .= ($self->fromto) ? "11" : "13"; # Anwendungsnummer
760 $header .= _fill($stamm->{dfvkz}, 2, '0');
761 $header .= _fill($stamm->{beraternr}, 7, '0');
762 $header .= _fill($stamm->{mandantennr}, 5, '0');
763 $header .= _fill(($stamm->{abrechnungsnr} // '') . $jahr, 6, '0');
765 $header .= $self->from ? $self->from->strftime('%d%m%y') : '';
766 $header .= $self->to ? $self->to->strftime('%d%m%y') : '';
770 $header .= $primanota;
773 $header .= _fill($stamm->{passwort}, 4, '0');
774 $header .= " " x 16; # Anwendungsinfo
775 $header .= " " x 16; # Inputinfo
779 my $versionssatz = $self->exporttype == DATEV_ET_BUCHUNGEN ? "\xB5" . "1," : "\xB6" . "1,";
781 my $query = qq|SELECT accno FROM chart LIMIT 1|;
782 my $ref = selectfirst_hashref_query($form, $self->dbh, $query);
784 $versionssatz .= length $ref->{accno};
785 $versionssatz .= ",";
786 $versionssatz .= length $ref->{accno};
787 $versionssatz .= ",SELF" . "\x1C\x79";
789 $header .= $versionssatz;
791 $main::lxdebug->leave_sub();
797 $main::lxdebug->enter_sub();
799 my ($date, $six) = @_;
801 my ($day, $month, $year) = split(/\./, $date);
804 $day = substr($day, 1, 1);
806 if (length($month) < 2) {
807 $month = "0" . $month;
809 if (length($year) > 2) {
810 $year = substr($year, -2, 2);
814 $date = $day . $month . $year;
816 $date = $day . $month;
819 $main::lxdebug->leave_sub();
824 sub trim_leading_zeroes {
832 sub make_ed_versionset {
833 $main::lxdebug->enter_sub();
835 my ($self, $header, $filename, $blockcount) = @_;
837 my $versionset = "V" . substr($filename, 2, 5);
838 $versionset .= substr($header, 6, 22);
841 $versionset .= "0000" . substr($header, 28, 19);
843 my $datum = " " x 16;
844 $versionset .= $datum . "001" . substr($header, 28, 4);
847 $versionset .= _fill($blockcount, 5, '0');
848 $versionset .= "001";
850 $versionset .= substr($header, -12, 10) . " ";
851 $versionset .= " " x 53;
853 $main::lxdebug->leave_sub();
859 $main::lxdebug->enter_sub();
861 my ($self, $form, $fileno) = @_;
863 my $stamm = $self->get_datev_stamm;
865 my $ev_header = _fill($stamm->{datentraegernr}, 3, ' ', 'left');
867 $ev_header .= _fill($stamm->{beraternr}, 7, ' ', 'left');
868 $ev_header .= _fill($stamm->{beratername}, 9, ' ', 'left');
870 $ev_header .= (_fill($fileno, 5, '0')) x 2;
871 $ev_header .= " " x 95;
873 $main::lxdebug->leave_sub();
878 sub generate_datev_lines {
881 my @datev_lines = ();
883 foreach my $transaction ( @{ $self->{DATEV} } ) {
885 # each $transaction entry contains data from several acc_trans entries
886 # belonging to the same trans_id
888 my %datev_data = (); # data for one transaction
889 my $trans_lines = scalar(@{$transaction});
897 my $buchungstext = "";
899 my $datevautomatik = 0;
904 for (my $i = 0; $i < $trans_lines; $i++) {
905 if ($trans_lines == 2) {
906 if (abs($transaction->[$i]->{'amount'}) > abs($umsatz)) {
907 $umsatz = $transaction->[$i]->{'amount'};
910 if (abs($transaction->[$i]->{'umsatz'}) > abs($umsatz)) {
911 $umsatz = $transaction->[$i]->{'umsatz'};
914 if ($transaction->[$i]->{'datevautomatik'}) {
917 if ($transaction->[$i]->{'taxkey'}) {
918 $taxkey = $transaction->[$i]->{'taxkey'};
920 if ($transaction->[$i]->{'charttax'}) {
921 $charttax = $transaction->[$i]->{'charttax'};
923 if ($transaction->[$i]->{'amount'} > 0) {
930 if ($trans_lines >= 2) {
932 $datev_data{'gegenkonto'} = $transaction->[$haben]->{'accno'};
933 $datev_data{'konto'} = $transaction->[$soll]->{'accno'};
934 if ($transaction->[$haben]->{'invnumber'} ne "") {
935 $datev_data{belegfeld1} = $transaction->[$haben]->{'invnumber'};
937 $datev_data{datum} = $transaction->[$haben]->{'transdate'};
938 $datev_data{waehrung} = 'EUR';
940 if ($transaction->[$haben]->{'name'} ne "") {
941 $datev_data{buchungstext} = $transaction->[$haben]->{'name'};
943 if (($transaction->[$haben]->{'ustid'} // '') ne "") {
944 $datev_data{ustid} = $transaction->[$haben]->{'ustid'};
946 if (($transaction->[$haben]->{'duedate'} // '') ne "") {
947 $datev_data{belegfeld2} = $transaction->[$haben]->{'duedate'};
951 $datev_data{umsatz} = abs($umsatz); # sales invoices without tax have a different sign???
953 # Dies ist die einzige Stelle die datevautomatik auswertet. Was soll gesagt werden?
954 # Im Prinzip hat jeder acc_trans Eintrag einen Steuerschlüssel, außer, bei gewissen Fällen
955 # wie: Kreditorenbuchung mit negativen Vorzeichen, SEPA-Export oder Rechnungen die per
956 # Skript angelegt werden.
957 # Also falls ein Steuerschlüssel da ist und NICHT datevautomatik diesen Block hinzufügen.
958 # Oder aber datevautomatik ist WAHR, aber der Steuerschlüssel in der acc_trans weicht
959 # von dem in der Chart ab: Also wahrscheinlich Programmfehler (NULL übergeben, statt
960 # DATEV-Steuerschlüssel) oder der Steuerschlüssel des Kontos weicht WIRKLICH von dem Eintrag in der
961 # acc_trans ab. Gibt es für diesen Fall eine plausiblen Grund?
964 # only set buchungsschluessel if the following conditions are met:
965 if ( ( $datevautomatik || $taxkey)
966 && (!$datevautomatik || ($datevautomatik && ($charttax ne $taxkey)))) {
967 # $datev_data{buchungsschluessel} = !$datevautomatik ? $taxkey : "4";
968 $datev_data{buchungsschluessel} = $taxkey;
971 push(@datev_lines, \%datev_data);
974 # example of modifying export data:
975 # foreach my $datev_line ( @datev_lines ) {
976 # if ( $datev_line{"konto"} eq '1234' ) {
977 # $datev_line{"konto"} = '9999';
982 return \@datev_lines;
986 sub kne_buchungsexport {
987 $main::lxdebug->enter_sub();
995 my $filename = "ED00001";
999 my $ed_filename = $self->export_path . $filename;
1001 my $fromto = $self->fromto;
1003 $self->generate_datev_data(from_to => $self->fromto); # fetches data from db, transforms data and fills $self->{DATEV}
1004 return if $self->errors;
1006 my @datev_lines = @{ $self->generate_datev_lines };
1009 my $umsatzsumme = sum map { $_->{umsatz} } @datev_lines;
1011 # prepare kne file, everything gets stored in ED00001
1012 my $header = $self->make_kne_data_header($form);
1013 my $kne_file = SL::DATEV::KNEFile->new();
1014 $kne_file->add_block($header);
1016 my $iconv = $::locale->{iconv_utf8};
1017 my %umlaute = ($iconv->convert('ä') => 'ae',
1018 $iconv->convert('ö') => 'oe',
1019 $iconv->convert('ü') => 'ue',
1020 $iconv->convert('Ä') => 'Ae',
1021 $iconv->convert('Ö') => 'Oe',
1022 $iconv->convert('Ü') => 'Ue',
1023 $iconv->convert('ß') => 'sz');
1025 # add the data from @datev_lines to the kne_file, formatting as needed
1026 foreach my $kne ( @datev_lines ) {
1027 $kne_file->add_block("+" . $kne_file->format_amount(abs($kne->{umsatz}), 0));
1029 # only add buchungsschluessel if it was previously defined
1030 $kne_file->add_block("\x6C" . $kne->{buchungsschluessel}) if defined $kne->{buchungsschluessel};
1032 # ($kne->{gegenkonto}) = $kne->{gegenkonto} =~ /^(\d+)/;
1033 $kne_file->add_block("a" . trim_leading_zeroes($kne->{gegenkonto}));
1035 if ( $kne->{belegfeld1} ) {
1036 my $invnumber = $kne->{belegfeld1};
1037 foreach my $umlaut (keys(%umlaute)) {
1038 $invnumber =~ s/${umlaut}/${umlaute{$umlaut}}/g;
1040 $invnumber =~ s/[^0-9A-Za-z\$\%\&\*\+\-\/]//g;
1041 $invnumber = substr($invnumber, 0, 12);
1042 $invnumber =~ s/\ *$//;
1043 $kne_file->add_block("\xBD" . $invnumber . "\x1C");
1046 $kne_file->add_block("\xBE" . &datetofour($kne->{belegfeld2},1) . "\x1C");
1048 $kne_file->add_block("d" . &datetofour($kne->{datum},0));
1050 # ($kne->{konto}) = $kne->{konto} =~ /^(\d+)/;
1051 $kne_file->add_block("e" . trim_leading_zeroes($kne->{konto}));
1053 my $name = $kne->{buchungstext};
1054 foreach my $umlaut (keys(%umlaute)) {
1055 $name =~ s/${umlaut}/${umlaute{$umlaut}}/g;
1057 $name =~ s/[^0-9A-Za-z\$\%\&\*\+\-\ \/]//g;
1058 $name = substr($name, 0, 30);
1060 $kne_file->add_block("\x1E" . $name . "\x1C");
1062 $kne_file->add_block("\xBA" . $kne->{'ustid'} . "\x1C") if $kne->{'ustid'};
1064 $kne_file->add_block("\xB3" . $kne->{'waehrung'} . "\x1C" . "\x79");
1067 $umsatzsumme = $kne_file->format_amount(abs($umsatzsumme), 0);
1068 my $mandantenendsumme = "x" . $kne_file->format_amount($umsatzsumme / 100.0, 14) . "\x79\x7a";
1070 $kne_file->add_block($mandantenendsumme);
1073 open(ED, ">", $ed_filename) or die "can't open outputfile: $!\n";
1074 print(ED $kne_file->get_data());
1077 $ed_versionset[$fileno] = $self->make_ed_versionset($header, $filename, $kne_file->get_block_count());
1079 #Make EV Verwaltungsdatei
1080 my $ev_header = $self->make_ev_header($form, $fileno);
1081 my $ev_filename = $self->export_path . $evfile;
1082 push(@filenames, $evfile);
1083 open(EV, ">", $ev_filename) or die "can't open outputfile: EV01\n";
1084 print(EV $ev_header);
1086 foreach my $file (@ed_versionset) {
1092 $self->add_filenames(@filenames);
1094 $main::lxdebug->leave_sub();
1096 return { 'download_token' => $self->download_token, 'filenames' => \@filenames };
1099 sub kne_stammdatenexport {
1100 $main::lxdebug->enter_sub();
1105 $self->get_datev_stamm->{abrechnungsnr} = "99";
1109 my $filename = "ED00000";
1110 my $evfile = "EV01";
1115 my $remaining_bytes = 256;
1116 my $total_bytes = 256;
1117 my $buchungssatz = "";
1119 my $ed_filename = $self->export_path . $filename;
1120 push(@filenames, $filename);
1121 open(ED, ">", $ed_filename) or die "can't open outputfile: $!\n";
1122 my $header = $self->make_kne_data_header($form);
1123 $remaining_bytes -= length($header);
1127 my (@where, @values) = ((), ());
1128 if ($self->accnofrom) {
1129 push @where, 'c.accno >= ?';
1130 push @values, $self->accnofrom;
1132 if ($self->accnoto) {
1133 push @where, 'c.accno <= ?';
1134 push @values, $self->accnoto;
1137 my $where_str = @where ? ' WHERE ' . join(' AND ', map { "($_)" } @where) : '';
1139 my $query = qq|SELECT c.accno, c.description
1144 my $sth = $self->dbh->prepare($query);
1145 $sth->execute(@values) || $form->dberror($query);
1147 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
1148 if (($remaining_bytes - length("t" . $ref->{'accno'})) <= 6) {
1149 $fuellzeichen = ($blockcount * 256 - length($buchungssatz . $header));
1150 $buchungssatz .= "\x00" x $fuellzeichen;
1152 $total_bytes = ($blockcount) * 256;
1154 $buchungssatz .= "t" . $ref->{'accno'};
1155 $remaining_bytes = $total_bytes - length($buchungssatz . $header);
1156 $ref->{'description'} =~ s/[^0-9A-Za-z\$\%\&\*\+\-\/]//g;
1157 $ref->{'description'} = substr($ref->{'description'}, 0, 40);
1158 $ref->{'description'} =~ s/\ *$//;
1161 ($remaining_bytes - length("\x1E" . $ref->{'description'} . "\x1C\x79")
1164 $fuellzeichen = ($blockcount * 256 - length($buchungssatz . $header));
1165 $buchungssatz .= "\x00" x $fuellzeichen;
1167 $total_bytes = ($blockcount) * 256;
1169 $buchungssatz .= "\x1E" . $ref->{'description'} . "\x1C\x79";
1170 $remaining_bytes = $total_bytes - length($buchungssatz . $header);
1175 print(ED $buchungssatz);
1176 $fuellzeichen = 256 - (length($header . $buchungssatz . "z") % 256);
1177 my $dateiende = "\x00" x $fuellzeichen;
1179 print(ED $dateiende);
1182 #Make EV Verwaltungsdatei
1184 $self->make_ed_versionset($header, $filename, $blockcount);
1186 my $ev_header = $self->make_ev_header($form, $fileno);
1187 my $ev_filename = $self->export_path . $evfile;
1188 push(@filenames, $evfile);
1189 open(EV, ">", $ev_filename) or die "can't open outputfile: EV01\n";
1190 print(EV $ev_header);
1192 foreach my $file (@ed_versionset) {
1193 print(EV $ed_versionset[$file]);
1197 $self->add_filenames(@filenames);
1199 $main::lxdebug->leave_sub();
1201 return { 'download_token' => $self->download_token, 'filenames' => \@filenames };
1206 return $accno . ('0' x (6 - min(length($accno), 6)));
1209 sub csv_export_for_tax_accountant {
1212 $self->generate_datev_data(from_to => $self->fromto);
1214 foreach my $transaction (@{ $self->{DATEV} }) {
1215 foreach my $entry (@{ $transaction }) {
1216 $entry->{sortkey} = join '-', map { lc } (DateTime->from_kivitendo($entry->{transdate})->strftime('%Y%m%d'), $entry->{name}, $entry->{reference});
1221 partition_by { $_->[0]->{table} }
1222 sort_by { $_->[0]->{sortkey} }
1223 grep { 2 == scalar(@{ $_ }) }
1224 @{ $self->{DATEV} };
1227 acc_trans_id => { 'text' => $::locale->text('ID'), },
1228 amount => { 'text' => $::locale->text('Amount'), },
1229 credit_accname => { 'text' => $::locale->text('Credit Account Name'), },
1230 credit_accno => { 'text' => $::locale->text('Credit Account'), },
1231 debit_accname => { 'text' => $::locale->text('Debit Account Name'), },
1232 debit_accno => { 'text' => $::locale->text('Debit Account'), },
1233 invnumber => { 'text' => $::locale->text('Reference'), },
1234 name => { 'text' => $::locale->text('Name'), },
1235 notes => { 'text' => $::locale->text('Notes'), },
1236 tax => { 'text' => $::locale->text('Tax'), },
1237 taxkey => { 'text' => $::locale->text('Taxkey'), },
1238 tax_accname => { 'text' => $::locale->text('Tax Account Name'), },
1239 tax_accno => { 'text' => $::locale->text('Tax Account'), },
1240 transdate => { 'text' => $::locale->text('Transdate'), },
1241 vcnumber => { 'text' => $::locale->text('Customer/Vendor Number'), },
1245 acc_trans_id name vcnumber
1246 transdate invnumber amount
1247 debit_accno debit_accname
1248 credit_accno credit_accname
1250 tax_accno tax_accname taxkey
1254 my %filenames_by_type = (
1255 ar => $::locale->text('AR Transactions'),
1256 ap => $::locale->text('AP Transactions'),
1257 gl => $::locale->text('GL Transactions'),
1261 foreach my $type (qw(ap ar)) {
1265 filename => sprintf('%s %s - %s.csv', $filenames_by_type{$type}, $self->from->to_kivitendo, $self->to->to_kivitendo),
1266 csv => Text::CSV_XS->new({
1274 filename => sprintf('Zahlungen %s %s - %s.csv', $filenames_by_type{$type}, $self->from->to_kivitendo, $self->to->to_kivitendo),
1275 csv => Text::CSV_XS->new({
1283 foreach my $csv (values %csvs) {
1284 $csv->{out} = IO::File->new($self->export_path . '/' . $csv->{filename}, '>:encoding(utf8)') ;
1285 $csv->{csv}->print($csv->{out}, [ map { $column_defs{$_}->{text} } @columns ]);
1287 push @filenames, $csv->{filename};
1290 foreach my $transaction (@{ $transactions{$type} }) {
1291 my $is_payment = any { $_->{link} =~ m{A[PR]_paid} } @{ $transaction };
1292 my $csv = $is_payment ? $csvs{payments} : $csvs{invoices};
1294 my ($soll, $haben) = map { $transaction->[$_] } ($transaction->[0]->{amount} > 0 ? (1, 0) : (0, 1));
1295 my $tax = defined($soll->{tax_accno}) ? $soll : $haben;
1296 my $amount = defined($soll->{net_amount}) ? $soll : $haben;
1297 $haben->{notes} = ($haben->{memo} || $soll->{memo}) if $is_payment;
1298 $haben->{notes} //= '';
1299 $haben->{notes} = SL::HTML::Util->strip($haben->{notes});
1300 $haben->{notes} =~ s{\r}{}g;
1301 $haben->{notes} =~ s{\n+}{ }g;
1304 amount => $::form->format_amount({ numberformat => '1000,00' }, abs($amount->{amount}), 2),
1305 debit_accno => _format_accno($soll->{accno}),
1306 debit_accname => $soll->{accname},
1307 credit_accno => _format_accno($haben->{accno}),
1308 credit_accname => $haben->{accname},
1309 tax => $::form->format_amount({ numberformat => '1000,00' }, abs($amount->{amount}) - abs($amount->{net_amount}), 2),
1310 notes => $haben->{notes},
1311 (map { ($_ => $tax->{$_}) } qw(taxkey tax_accname tax_accno)),
1312 (map { ($_ => ($haben->{$_} // $soll->{$_})) } qw(acc_trans_id invnumber name vcnumber transdate)),
1315 $csv->{csv}->print($csv->{out}, [ map { $row{$_} } @columns ]);
1318 $_->{out}->close for values %csvs;
1321 $self->add_filenames(@filenames);
1323 return { download_token => $self->download_token, filenames => \@filenames };
1327 clean_temporary_directories();
1338 SL::DATEV - kivitendo DATEV Export module
1342 use SL::DATEV qw(:CONSTANTS);
1344 my $startdate = DateTime->new(year => 2014, month => 9, day => 1);
1345 my $enddate = DateTime->new(year => 2014, month => 9, day => 31);
1346 my $datev = SL::DATEV->new(
1347 exporttype => DATEV_ET_BUCHUNGEN,
1348 format => DATEV_FORMAT_KNE,
1353 # To only export transactions from a specific trans_id: (from and to are ignored)
1354 my $invoice = SL::DB::Manager::Invoice->find_by( invnumber => '216' );
1355 my $datev = SL::DATEV->new(
1356 exporttype => DATEV_ET_BUCHUNGEN,
1357 format => DATEV_FORMAT_KNE,
1358 trans_id => $invoice->trans_id,
1361 my $datev = SL::DATEV->new(
1362 exporttype => DATEV_ET_STAMM,
1363 format => DATEV_FORMAT_KNE,
1364 accnofrom => $start_account_number,
1365 accnoto => $end_account_number,
1368 # get or set datev stamm
1369 my $hashref = $datev->get_datev_stamm;
1370 $datev->save_datev_stamm($hashref);
1372 # manually clean up temporary directories older than 8 hours
1373 $datev->clean_temporary_directories;
1378 if ($datev->errors) {
1379 die join "\n", $datev->error;
1382 # get relevant data for saving the export:
1383 my $dl_token = $datev->download_token;
1384 my $path = $datev->export_path;
1385 my @files = $datev->filenames;
1387 # retrieving an export at a later time
1388 my $datev = SL::DATEV->new(
1389 download_token => $dl_token_from_user,
1392 my $path = $datev->export_path;
1393 my @files = glob("$path/*");
1395 # Only test the datev data of a specific trans_id, without generating an
1396 # export file, but filling $datev->errors if errors exist
1398 my $datev = SL::DATEV->new(
1399 trans_id => $invoice->trans_id,
1401 $datev->generate_datev_data;
1402 # if ($datev->errors) { ...
1407 This module implements the DATEV export standard. For usage see above.
1415 Generic constructor. See section attributes for information about what to pass.
1417 =item generate_datev_data
1419 Fetches all transactions from the database (via a trans_id or a date range),
1420 and does an initial transformation (e.g. filters out tax, determines
1421 the brutto amount, checks split transactions ...) and stores this data in
1424 If any errors are found these are collected in $self->errors.
1426 This function is needed for all the exports, but can be also called
1427 independently in order to check transactions for DATEV compatibility.
1429 =item generate_datev_lines
1431 Parse the data in $self->{DATEV} and transform it into a format that can be
1432 used by DATEV, e.g. determines Konto and Gegenkonto, the taxkey, ...
1434 The transformed data is returned as an arrayref, which is ready to be converted
1435 to a DATEV data format, e.g. KNE, OBE, CSV, ...
1437 At this stage the "DATEV rule" has already been applied to the taxkeys, i.e.
1438 entries with datevautomatik have an empty taxkey, as the taxkey is already
1439 determined by the chart.
1441 =item get_datev_stamm
1443 Loads DATEV Stammdaten and returns as hashref.
1445 =item save_datev_stamm HASHREF
1447 Saves DATEV Stammdaten from provided hashref.
1451 See L<CONSTANTS> for possible values
1453 =item has_exporttype
1455 Returns true if an exporttype has been set. Without exporttype most report functions won't work.
1459 Specifies the designated format of the export. Currently only KNE export is implemented.
1461 See L<CONSTANTS> for possible values
1465 Returns true if a format has been set. Without format most report functions won't work.
1467 =item download_token
1469 Returns a download token for this DATEV object.
1471 Note: If either a download_token or export_path were set at the creation these are infered, otherwise randomly generated.
1475 Returns an export_path for this DATEV object.
1477 Note: If either a download_token or export_path were set at the creation these are infered, otherwise randomly generated.
1481 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.
1483 =item net_gross_differences
1485 If there were any net gross differences during calculation they will be collected here.
1487 =item sum_net_gross_differences
1489 Sum of all differences.
1491 =item clean_temporary_directories
1493 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.
1497 Returns a list of errors that occured. If no errors occured, the export was a success.
1501 Exports data. You have to have set L<exporttype> and L<format> or an error will
1502 occur. OBE exports are currently not implemented.
1504 =item csv_export_for_tax_accountant
1506 Generates up to four downloadable csv files containing data about sales and
1507 purchase invoices, and their respective payments:
1510 my $startdate = DateTime->new(year => 2012, month => 1, day => 1);
1511 my $enddate = DateTime->new(year => 2012, month => 12, day => 31);
1512 SL::DATEV->new(from => $startdate, to => $enddate)->csv_export_for_tax_accountant;
1514 # 'download_token' => '1488551625-815654-22430',
1516 # 'Zahlungen Kreditorenbuchungen 2012-01-01 - 2012-12-31.csv',
1517 # 'Kreditorenbuchungen 2012-01-01 - 2012-12-31.csv',
1518 # 'Zahlungen Debitorenbuchungen 2012-01-01 - 2012-12-31.csv',
1519 # 'Debitorenbuchungen 2012-01-01 - 2012-12-31.csv'
1527 This is a list of attributes set in either the C<new> or a method of the same name.
1533 Set a database handle to use in the process. This allows for an export to be
1534 done on a transaction in progress without committing first.
1536 Note: If you don't want this code to commit, simply providing a dbh is not
1537 enough enymore. You'll have to wrap the call into a transaction yourself, so
1538 that the internal transaction does not commit.
1542 See L<CONSTANTS> for possible values. This MUST be set before export is called.
1546 See L<CONSTANTS> for possible values. This MUST be set before export is called.
1548 =item download_token
1550 Can be set on creation to retrieve a prior export for download.
1556 Set boundary dates for the export. Unless a trans_id is passed these MUST be
1557 set for the export to work.
1561 To check only one gl/ar/ap transaction, pass the trans_id. The attributes
1562 L<from> and L<to> are currently still needed for the query to be assembled
1569 Set boundary account numbers for the export. Only useful for a stammdaten export.
1575 =head2 Supplied to L<exporttype>
1579 =item DATEV_ET_BUCHUNGEN
1581 =item DATEV_ET_STAMM
1585 =head2 Supplied to L<format>.
1589 =item DATEV_FORMAT_KNE
1591 =item DATEV_FORMAT_OBE
1595 =head1 ERROR HANDLING
1597 This module will die in the following cases:
1603 No or unrecognized exporttype or format was provided for an export
1607 OBE export was called, which is not yet implemented.
1615 Errors that occur during th actual export will be collected in L<errors>. The following types can occur at the moment:
1621 C<Unbalanced Ledger!>. Exactly that, your ledger is unbalanced. Should never occur.
1625 C<Datev-Export fehlgeschlagen! Bei Transaktion %d (%f).> This error occurs if a
1626 transaction could not be reliably sorted out, or had rounding errors above the acceptable threshold.
1630 =head1 BUGS AND CAVEATS
1636 Handling of Vollvorlauf is currently not fully implemented. You must provide both from and to in order to get a working export.
1640 OBE export is currently not implemented.
1646 - handling of export_path and download token is a bit dodgy, clean that up.
1650 L<SL::DATEV::KNEFile>
1654 Philip Reetz E<lt>p.reetz@linet-services.deE<gt>,
1656 Moritz Bunkus E<lt>m.bunkus@linet-services.deE<gt>,
1658 Jan Büren E<lt>jan@lx-office-hosting.deE<gt>,
1660 Geoffrey Richardson E<lt>information@lx-office-hosting.deE<gt>,
1662 Sven Schöling E<lt>s.schoeling@linet-services.deE<gt>,