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;
37 use SL::HTML::Util ();
39 use SL::Locale::String qw(t8);
43 use Exporter qw(import);
46 use List::MoreUtils qw(any);
47 use List::Util qw(min max sum);
48 use List::UtilsBy qw(partition_by sort_by);
50 use Time::HiRes qw(gettimeofday);
55 DATEV_ET_BUCHUNGEN => $i++,
56 DATEV_ET_STAMM => $i++,
59 DATEV_FORMAT_KNE => $i++,
60 DATEV_FORMAT_OBE => $i++,
61 DATEV_FORMAT_CSV => $i++,
65 my @export_constants = qw(DATEV_ET_BUCHUNGEN DATEV_ET_STAMM DATEV_ET_CSV DATEV_FORMAT_KNE DATEV_FORMAT_OBE DATEV_FORMAT_CSV);
66 our @EXPORT_OK = (@export_constants);
67 our %EXPORT_TAGS = (CONSTANTS => [ @export_constants ]);
74 my $obj = bless {}, $class;
76 $obj->$_($data{$_}) for keys %data;
83 $self->{exporttype} = $_[0] if @_;
84 return $self->{exporttype};
88 defined $_[0]->{exporttype};
93 $self->{format} = $_[0] if @_;
94 return $self->{format};
98 defined $_[0]->{format};
101 sub _get_export_path {
102 $main::lxdebug->enter_sub();
104 my ($a, $b) = gettimeofday();
105 my $path = _get_path_for_download_token("${a}-${b}-${$}");
107 mkpath($path) unless (-d $path);
109 $main::lxdebug->leave_sub();
114 sub _get_path_for_download_token {
115 $main::lxdebug->enter_sub();
117 my $token = shift || '';
120 if ($token =~ m|^(\d+)-(\d+)-(\d+)$|) {
121 $path = $::lx_office_conf{paths}->{userspath} . "/datev-export-${1}-${2}-${3}/";
124 $main::lxdebug->leave_sub();
129 sub _get_download_token_for_path {
130 $main::lxdebug->enter_sub();
135 if ($path =~ m|.*datev-export-(\d+)-(\d+)-(\d+)/?$|) {
136 $token = "${1}-${2}-${3}";
139 $main::lxdebug->leave_sub();
146 $self->{download_token} = $_[0] if @_;
147 return $self->{download_token} ||= _get_download_token_for_path($self->export_path);
153 return $self->{export_path} ||= _get_path_for_download_token($self->{download_token}) || _get_export_path();
158 push @{ $self->{filenames} ||= [] }, @_;
162 return @{ $_[0]{filenames} || [] };
167 push @{ $self->{errors} ||= [] }, @_;
171 return @{ $_[0]{errors} || [] };
174 sub add_net_gross_differences {
176 push @{ $self->{net_gross_differences} ||= [] }, @_;
179 sub net_gross_differences {
180 return @{ $_[0]{net_gross_differences} || [] };
183 sub sum_net_gross_differences {
184 return sum $_[0]->net_gross_differences;
191 $self->{from} = $_[0];
194 return $self->{from};
211 $self->{trans_id} = $_[0];
214 die "illegal trans_id passed for DATEV export: " . $self->{trans_id} . "\n" unless $self->{trans_id} =~ m/^\d+$/;
216 return $self->{trans_id};
223 $self->{warnings} = [@_];
225 return $self->{warnings};
233 $self->{use_pk} = $_[0];
236 return $self->{use_pk};
243 $self->{accnofrom} = $_[0];
246 return $self->{accnofrom};
253 $self->{accnoto} = $_[0];
256 return $self->{accnoto};
264 $self->{dbh} = $_[0];
265 $self->{provided_dbh} = 1;
268 $self->{dbh} ||= SL::DB->client->dbh;
275 sub clean_temporary_directories {
276 $::lxdebug->enter_sub;
278 foreach my $path (glob($::lx_office_conf{paths}->{userspath} . "/datev-export-*")) {
279 next unless -d $path;
281 my $mtime = (stat($path))[9];
282 next if ((time() - $mtime) < 8 * 60 * 60);
287 $::lxdebug->leave_sub;
291 $main::lxdebug->enter_sub();
293 my $text = shift // '';
294 my $field_len = shift;
295 my $fill_char = shift;
296 my $alignment = shift || 'right';
298 my $text_len = length $text;
300 if ($field_len < $text_len) {
301 $text = substr $text, 0, $field_len;
303 } elsif ($field_len > $text_len) {
304 my $filler = ($fill_char) x ($field_len - $text_len);
305 $text = $alignment eq 'right' ? $filler . $text : $text . $filler;
308 $main::lxdebug->leave_sub();
313 sub get_datev_stamm {
314 return $_[0]{stamm} ||= selectfirst_hashref_query($::form, $_[0]->dbh, 'SELECT * FROM datev');
317 sub save_datev_stamm {
318 my ($self, $data) = @_;
320 SL::DB->client->with_transaction(sub {
321 do_query($::form, $self->dbh, 'DELETE FROM datev');
323 my @columns = qw(beraternr beratername dfvkz mandantennr datentraegernr abrechnungsnr);
325 my $query = "INSERT INTO datev (" . join(', ', @columns) . ") VALUES (" . join(', ', ('?') x @columns) . ")";
326 do_query($::form, $self->dbh, $query, map { $data->{$_} } @columns);
328 }) or do { die SL::DB->client->error };
335 die 'no format set!' unless $self->has_format;
337 if ($self->format == DATEV_FORMAT_CSV) {
338 $result = $self->csv_export;
339 } elsif ($self->format == DATEV_FORMAT_KNE) {
340 $result = $self->kne_export;
341 } elsif ($self->format == DATEV_FORMAT_OBE) {
342 $result = $self->obe_export;
344 die 'unrecognized export format';
354 die 'no exporttype set!' unless $self->has_exporttype;
356 if ($self->exporttype == DATEV_ET_BUCHUNGEN) {
357 $result = $self->kne_buchungsexport;
358 } elsif ($self->exporttype == DATEV_ET_STAMM) {
359 $result = $self->kne_stammdatenexport;
360 } elsif ($self->exporttype == DATEV_ET_CSV) {
361 $result = $self->csv_export_for_tax_accountant;
363 die 'unrecognized exporttype';
373 die 'no exporttype set!' unless $self->has_exporttype;
375 if ($self->exporttype == DATEV_ET_BUCHUNGEN) {
377 $self->generate_datev_data(from_to => $self->fromto);
378 return if $self->errors;
380 my $datev_csv = SL::DATEV::CSV->new(
381 datev_lines => $self->generate_datev_lines,
384 locked => $self->locked,
388 my $filename = "EXTF_DATEV_kivitendo" . $self->from->ymd() . '-' . $self->to->ymd() . ".csv";
390 my $csv = Text::CSV_XS->new({
395 }) or die "Cannot use CSV: ".Text::CSV_XS->error_diag();
397 # get encoding from defaults - use cp1252 if DATEV strict export is used
398 my $enc = ($::instance_conf->get_datev_export_format eq 'cp1252') ? 'cp1252' : 'utf-8';
399 my $csv_file = IO::File->new($self->export_path . '/' . $filename, ">:encoding($enc)") or die "Can't open: $!";
401 $csv->print($csv_file, $_) for @{ $datev_csv->header };
402 $csv->print($csv_file, $_) for @{ $datev_csv->lines };
404 $self->{warnings} = $datev_csv->warnings;
406 # convert utf-8 to cp1252//translit if set
407 if ($::instance_conf->get_datev_export_format eq 'cp1252-translit') {
409 my $filename_translit = "EXTF_DATEV_kivitendo_translit" . $self->from->ymd() . '-' . $self->to->ymd() . ".csv";
410 open my $fh_in, '<:encoding(UTF-8)', $self->export_path . '/' . $filename or die "could not open $filename for reading: $!";
411 open my $fh_out, '>', $self->export_path . '/' . $filename_translit or die "could not open $filename_translit for writing: $!";
413 my $converter = SL::Iconv->new("utf-8", "cp1252//translit");
415 print $fh_out $converter->convert($_) while <$fh_in>;
419 unlink $self->export_path . '/' . $filename or warn "Could not unlink $filename: $!";
420 $filename = $filename_translit;
423 return { download_token => $self->download_token, filenames => $filename };
425 } elsif ($self->exporttype == DATEV_ET_STAMM) {
426 die 'will never be implemented';
427 # 'Background: Export should only contain non
428 # DATEV-Charts and DATEV import will only
429 # import new Charts.'
430 } elsif ($self->exporttype == DATEV_ET_CSV) {
431 $result = $self->csv_export_for_tax_accountant;
433 die 'unrecognized exporttype';
440 die 'not yet implemented';
446 return unless $self->from && $self->to;
448 return "transdate >= '" . $self->from->to_lxoffice . "' and transdate <= '" . $self->to->to_lxoffice . "'";
459 $self->{locked} = $_[0];
461 return $self->{locked};
464 sub generate_datev_data {
465 $main::lxdebug->enter_sub();
467 my ($self, %params) = @_;
468 my $fromto = $params{from_to} // '';
469 my $progress_callback = $params{progress_callback} || sub {};
471 my $form = $main::form;
473 my $trans_id_filter = '';
474 my $ar_department_id_filter = '';
475 my $ap_department_id_filter = '';
476 my $gl_department_id_filter = '';
477 if ( $form->{department_id} ) {
478 $ar_department_id_filter = " AND ar.department_id = ? ";
479 $ap_department_id_filter = " AND ap.department_id = ? ";
480 $gl_department_id_filter = " AND gl.department_id = ? ";
483 my ($gl_itime_filter, $ar_itime_filter, $ap_itime_filter);
484 if ( $form->{gldatefrom} ) {
485 $gl_itime_filter = " AND gl.itime >= ? ";
486 $ar_itime_filter = " AND ar.itime >= ? ";
487 $ap_itime_filter = " AND ap.itime >= ? ";
489 $gl_itime_filter = "";
490 $ar_itime_filter = "";
491 $ap_itime_filter = "";
494 if ( $self->{trans_id} ) {
495 # ignore dates when trans_id is passed so that the entire transaction is
496 # checked, not just either the initial bookings or the subsequent payments
497 # (the transdates will likely differ)
499 $trans_id_filter = 'ac.trans_id = ' . $self->trans_id;
501 $fromto =~ s/transdate/ac\.transdate/g;
506 my $filter = ''; # Useful for debugging purposes
508 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');
510 my $ar_accno = "c.accno";
511 my $ap_accno = "c.accno";
512 if ( $self->use_pk ) {
513 $ar_accno = "CASE WHEN ac.chart_link = 'AR' THEN ct.customernumber ELSE c.accno END as accno";
514 $ap_accno = "CASE WHEN ac.chart_link = 'AP' THEN ct.vendornumber ELSE c.accno END as accno";
518 qq|SELECT ac.acc_trans_id, ac.transdate, ac.gldate, ac.trans_id,ar.id, ac.amount, ac.taxkey, ac.memo,
519 ar.invnumber, ar.duedate, ar.amount as umsatz, ar.deliverydate, ar.itime::date,
520 ct.name, ct.ustid, ct.customernumber AS vcnumber, ct.id AS customer_id, NULL AS vendor_id,
521 $ar_accno, c.description AS accname, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
523 t.rate AS taxrate, t.taxdescription,
525 tc.accno AS tax_accno, tc.description AS tax_accname,
528 project.projectnumber as projectnumber, project.description as projectdescription,
529 department.description as departmentdescription
531 LEFT JOIN ar ON (ac.trans_id = ar.id)
532 LEFT JOIN customer ct ON (ar.customer_id = ct.id)
533 LEFT JOIN chart c ON (ac.chart_id = c.id)
534 LEFT JOIN tax t ON (ac.tax_id = t.id)
535 LEFT JOIN chart tc ON (t.chart_id = tc.id)
536 LEFT JOIN department ON (department.id = ar.department_id)
537 LEFT JOIN project ON (project.id = ar.globalproject_id)
538 WHERE (ar.id IS NOT NULL)
542 $ar_department_id_filter
547 SELECT ac.acc_trans_id, ac.transdate, ac.gldate, ac.trans_id,ap.id, ac.amount, ac.taxkey, ac.memo,
548 ap.invnumber, ap.duedate, ap.amount as umsatz, ap.deliverydate, ap.itime::date,
549 ct.name, ct.ustid, ct.vendornumber AS vcnumber, NULL AS customer_id, ct.id AS vendor_id,
550 $ap_accno, c.description AS accname, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
552 t.rate AS taxrate, t.taxdescription,
554 tc.accno AS tax_accno, tc.description AS tax_accname,
557 project.projectnumber as projectnumber, project.description as projectdescription,
558 department.description as departmentdescription
560 LEFT JOIN ap ON (ac.trans_id = ap.id)
561 LEFT JOIN vendor ct ON (ap.vendor_id = ct.id)
562 LEFT JOIN chart c ON (ac.chart_id = c.id)
563 LEFT JOIN tax t ON (ac.tax_id = t.id)
564 LEFT JOIN chart tc ON (t.chart_id = tc.id)
565 LEFT JOIN department ON (department.id = ap.department_id)
566 LEFT JOIN project ON (project.id = ap.globalproject_id)
567 WHERE (ap.id IS NOT NULL)
571 $ap_department_id_filter
576 SELECT ac.acc_trans_id, ac.transdate, ac.gldate, ac.trans_id,gl.id, ac.amount, ac.taxkey, ac.memo,
577 gl.reference AS invnumber, NULL AS duedate, ac.amount as umsatz, NULL as deliverydate, gl.itime::date,
578 gl.description AS name, NULL as ustid, '' AS vcname, NULL AS customer_id, NULL AS vendor_id,
579 c.accno, c.description AS accname, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
581 t.rate AS taxrate, t.taxdescription,
583 tc.accno AS tax_accno, tc.description AS tax_accname,
586 '' as projectnumber, '' as projectdescription,
587 department.description as departmentdescription
589 LEFT JOIN gl ON (ac.trans_id = gl.id)
590 LEFT JOIN chart c ON (ac.chart_id = c.id)
591 LEFT JOIN tax t ON (ac.tax_id = t.id)
592 LEFT JOIN chart tc ON (t.chart_id = tc.id)
593 LEFT JOIN department ON (department.id = gl.department_id)
594 WHERE (gl.id IS NOT NULL)
598 $gl_department_id_filter
601 ORDER BY trans_id, acc_trans_id|;
604 if ( $form->{gldatefrom} or $form->{department_id} ) {
607 if ( $form->{gldatefrom} ) {
608 my $glfromdate = $::locale->parse_date_to_object($form->{gldatefrom});
609 die "illegal data" unless ref($glfromdate) eq 'DateTime';
610 push(@query_args, $glfromdate);
612 if ( $form->{department_id} ) {
613 push(@query_args, $form->{department_id});
618 my $sth = prepare_execute_query($form, $self->dbh, $query, @query_args);
624 while ( $continue && (my $ref = $sth->fetchrow_hashref("NAME_lc")) ) {
625 last unless $ref; # for single transactions
627 if (($counter % 500) == 0) {
628 $progress_callback->($counter);
631 my $trans = [ $ref ];
633 my $count = $ref->{amount};
636 # if the amount of a booking in a group is smaller than 0.02, any tax
637 # amounts will likely be smaller than 1 cent, so go into subcent mode
638 my $subcent = abs($count) < 0.02;
640 # records from acc_trans are ordered by trans_id and acc_trans_id
641 # first check for unbalanced ledger inside one trans_id
642 # there may be several groups inside a trans_id, e.g. the original booking and the payment
643 # each group individually should be exactly balanced and each group
644 # individually needs its own datev lines
646 # keep fetching new acc_trans lines until the end of a balanced group is reached
647 while (abs($count) > 0.01 || $firstrun || ($subcent && abs($count) > 0.005)) {
648 my $ref2 = $sth->fetchrow_hashref("NAME_lc");
654 # check if trans_id of current acc_trans line is still the same as the
655 # trans_id of the first line in group, i.e. we haven't finished a 0-group
656 # before moving on to the next trans_id, error will likely be in the old
659 if ($ref2->{trans_id} != $trans->[0]->{trans_id}) {
660 require SL::DB::Manager::AccTransaction;
661 if ( $trans->[0]->{trans_id} ) {
662 my $acc_trans_obj = SL::DB::Manager::AccTransaction->get_first(where => [ trans_id => $trans->[0]->{trans_id} ]);
663 $self->add_error(t8("Export error in transaction #1: Unbalanced ledger before next transaction (#2)",
664 $acc_trans_obj->transaction_name, $ref2->{trans_id})
670 push @{ $trans }, $ref2;
672 $count += $ref2->{amount};
676 foreach my $i (0 .. scalar(@{ $trans }) - 1) {
677 my $ref = $trans->[$i];
678 my $prev_ref = 0 < $i ? $trans->[$i - 1] : undef;
679 if ( $all_taxchart_ids{$ref->{id}}
680 && ($ref->{link} =~ m/(?:AP_tax|AR_tax)/)
681 && ( ($prev_ref && $prev_ref->{taxkey} && (_sign($ref->{amount}) == _sign($prev_ref->{amount})))
682 || $ref->{invoice})) {
686 if ( !$ref->{invoice} # we have a non-invoice booking (=gl)
687 && $ref->{is_tax} # that has "is_tax" set
688 && !($prev_ref->{is_tax}) # previous line wasn't is_tax
689 && (_sign($ref->{amount}) == _sign($prev_ref->{amount}))) { # and sign same as previous sign
690 $trans->[$i - 1]->{tax_amount} = $ref->{amount};
695 if (scalar(@{$trans}) <= 2) {
696 push @{ $self->{DATEV} }, $trans;
700 # determine at which array position the reference value (called absumsatz) is
701 # and which amount it has
703 for my $j (0 .. (scalar(@{$trans}) - 1)) {
706 # 1: gl transaction (Dialogbuchung), invoice is false, no double split booking allowed
708 # 2: sales or vendor invoice (Verkaufs- und Einkaufsrechnung): invoice is
709 # true, instead of absumsatz use link AR/AP (there should only be one
712 # 3. AR/AP transaction (Kreditoren- und Debitorenbuchung): invoice is false,
713 # instead of absumsatz use link AR/AP (there should only be one, so jump
714 # out of search as soon as you find it )
717 # for gl-bookings no split is allowed and there is no AR/AP account, so we always use the maximum value as a reference
718 # for ap/ar bookings we can always search for AR/AP in link and use that
719 if ( ( not $trans->[$j]->{'invoice'} and abs($trans->[$j]->{'amount'}) > abs($absumsatz) )
720 or ($trans->[$j]->{'invoice'} and ($trans->[$j]->{'link'} eq 'AR' or $trans->[$j]->{'link'} eq 'AP'))) {
721 $absumsatz = $trans->[$j]->{'amount'};
726 # Problem: we can't distinguish between AR and AP and normal invoices via boolean "invoice"
727 # for AR and AP transaction exit the loop as soon as an AR or AP account is found
728 # there must be only one AR or AP chart in the booking
729 # since it is possible to do this kind of things with GL too, make sure those don't get aborted in case someone
730 # manually pays an invoice in GL.
731 if ($trans->[$j]->{table} ne 'gl' and ($trans->[$j]->{'link'} eq 'AR' or $trans->[$j]->{'link'} eq 'AP')) {
732 $notsplitindex = $j; # position in booking with highest amount
733 $absumsatz = $trans->[$j]->{'amount'};
738 my $ml = ($trans->[0]->{'umsatz'} > 0) ? 1 : -1;
739 my $rounding_error = 0;
742 # go through each line and determine if it is a tax booking or not
743 # skip all tax lines and notsplitindex line
744 # push all other accounts (e.g. income or expense) with corresponding taxkey
746 for my $j (0 .. (scalar(@{$trans}) - 1)) {
747 if ( ($j != $notsplitindex)
748 && !$trans->[$j]->{is_tax}
749 && ( $trans->[$j]->{'taxkey'} eq ""
750 || $trans->[$j]->{'taxkey'} eq "0"
751 || $trans->[$j]->{'taxkey'} eq "1"
752 || $trans->[$j]->{'taxkey'} eq "10"
753 || $trans->[$j]->{'taxkey'} eq "11")) {
755 map { $new_trans{$_} = $trans->[$notsplitindex]->{$_}; } keys %{ $trans->[$notsplitindex] };
757 $absumsatz += $trans->[$j]->{'amount'};
758 $new_trans{'amount'} = $trans->[$j]->{'amount'} * (-1);
759 $new_trans{'umsatz'} = abs($trans->[$j]->{'amount'}) * $ml;
760 $trans->[$j]->{'umsatz'} = abs($trans->[$j]->{'amount'}) * $ml;
762 push @{ $self->{DATEV} }, [ \%new_trans, $trans->[$j] ];
764 } elsif (($j != $notsplitindex) && !$trans->[$j]->{is_tax}) {
767 map { $new_trans{$_} = $trans->[$notsplitindex]->{$_}; } keys %{ $trans->[$notsplitindex] };
769 my $tax_rate = $trans->[$j]->{'taxrate'};
770 $new_trans{'net_amount'} = $trans->[$j]->{'amount'} * -1;
771 $new_trans{'tax_rate'} = 1 + $tax_rate;
773 if (!$trans->[$j]->{'invoice'}) {
774 $new_trans{'amount'} = $form->round_amount(-1 * ($trans->[$j]->{amount} + $trans->[$j]->{tax_amount}), 2);
775 $new_trans{'umsatz'} = abs($new_trans{'amount'}) * $ml;
776 $trans->[$j]->{'umsatz'} = $new_trans{'umsatz'};
777 $absumsatz += -1 * $new_trans{'amount'};
780 my $unrounded = $trans->[$j]->{'amount'} * (1 + $tax_rate) * -1 + $rounding_error;
781 my $rounded = $form->round_amount($unrounded, 2);
783 $rounding_error = $unrounded - $rounded;
784 $new_trans{'amount'} = $rounded;
785 $new_trans{'umsatz'} = abs($rounded) * $ml;
786 $trans->[$j]->{'umsatz'} = $new_trans{umsatz};
787 $absumsatz -= $rounded;
790 push @{ $self->{DATEV} }, [ \%new_trans, $trans->[$j] ];
791 push @taxed, $self->{DATEV}->[-1];
797 while ((abs($absumsatz) >= 0.01) && (abs($absumsatz) < 1.00)) {
798 if ($idx >= scalar @taxed) {
799 last if (!$correction);
805 my $transaction = $taxed[$idx]->[0];
807 my $old_amount = $transaction->{amount};
808 my $old_correction = $correction;
811 if (!$transaction->{diff}) {
812 @possible_diffs = (0.01, -0.01);
814 @possible_diffs = ($transaction->{diff});
817 foreach my $diff (@possible_diffs) {
818 my $net_amount = $form->round_amount(($transaction->{amount} + $diff) / $transaction->{tax_rate}, 2);
819 next if ($net_amount != $transaction->{net_amount});
821 $transaction->{diff} = $diff;
822 $transaction->{amount} += $diff;
823 $transaction->{umsatz} += $diff;
833 $absumsatz = $form->round_amount($absumsatz, 2);
834 if (abs($absumsatz) >= (0.01 * (1 + scalar @taxed))) {
835 require SL::DB::Manager::AccTransaction;
836 my $acc_trans_obj = SL::DB::Manager::AccTransaction->get_first(where => [ trans_id => $trans->[0]->{trans_id} ]);
837 $self->add_error(t8("Export error in transaction #1: Rounding error too large #2",
838 $acc_trans_obj->transaction_name, $absumsatz)
840 } elsif (abs($absumsatz) >= 0.01) {
841 $self->add_net_gross_differences($absumsatz);
847 $::lxdebug->leave_sub;
850 sub make_kne_data_header {
851 $main::lxdebug->enter_sub();
853 my ($self, $form) = @_;
856 my $stamm = $self->get_datev_stamm;
858 my $jahr = $self->from ? $self->from->year : DateTime->today->year;
861 my $header = "\x1D\x181";
862 $header .= _fill($stamm->{datentraegernr}, 3, ' ', 'left');
863 $header .= ($self->fromto) ? "11" : "13"; # Anwendungsnummer
864 $header .= _fill($stamm->{dfvkz}, 2, '0');
865 $header .= _fill($stamm->{beraternr}, 7, '0');
866 $header .= _fill($stamm->{mandantennr}, 5, '0');
867 $header .= _fill(($stamm->{abrechnungsnr} // '') . $jahr, 6, '0');
869 $header .= $self->from ? $self->from->strftime('%d%m%y') : '';
870 $header .= $self->to ? $self->to->strftime('%d%m%y') : '';
874 $header .= $primanota;
877 $header .= _fill($stamm->{passwort}, 4, '0');
878 $header .= " " x 16; # Anwendungsinfo
879 $header .= " " x 16; # Inputinfo
883 my $versionssatz = $self->exporttype == DATEV_ET_BUCHUNGEN ? "\xB5" . "1," : "\xB6" . "1,";
885 my $query = qq|SELECT accno FROM chart LIMIT 1|;
886 my $ref = selectfirst_hashref_query($form, $self->dbh, $query);
888 $versionssatz .= length $ref->{accno};
889 $versionssatz .= ",";
890 $versionssatz .= length $ref->{accno};
891 $versionssatz .= ",SELF" . "\x1C\x79";
893 $header .= $versionssatz;
895 $main::lxdebug->leave_sub();
901 $main::lxdebug->enter_sub();
903 my ($date, $six) = @_;
905 my ($day, $month, $year) = split(/\./, $date);
907 if (length($month) < 2) {
908 $month = "0" . $month;
910 if (length($year) > 2) {
911 $year = substr($year, -2, 2);
915 $date = $day . $month . $year;
917 $date = $day . $month;
920 $main::lxdebug->leave_sub();
925 sub trim_leading_zeroes {
933 sub make_ed_versionset {
934 $main::lxdebug->enter_sub();
936 my ($self, $header, $filename, $blockcount) = @_;
938 my $versionset = "V" . substr($filename, 2, 5);
939 $versionset .= substr($header, 6, 22);
942 $versionset .= "0000" . substr($header, 28, 19);
944 my $datum = " " x 16;
945 $versionset .= $datum . "001" . substr($header, 28, 4);
948 $versionset .= _fill($blockcount, 5, '0');
949 $versionset .= "001";
951 $versionset .= substr($header, -12, 10) . " ";
952 $versionset .= " " x 53;
954 $main::lxdebug->leave_sub();
960 $main::lxdebug->enter_sub();
962 my ($self, $form, $fileno) = @_;
964 my $stamm = $self->get_datev_stamm;
966 my $ev_header = _fill($stamm->{datentraegernr}, 3, ' ', 'left');
968 $ev_header .= _fill($stamm->{beraternr}, 7, ' ', 'left');
969 $ev_header .= _fill($stamm->{beratername}, 9, ' ', 'left');
971 $ev_header .= (_fill($fileno, 5, '0')) x 2;
972 $ev_header .= " " x 95;
974 $main::lxdebug->leave_sub();
979 sub generate_datev_lines {
982 my @datev_lines = ();
984 foreach my $transaction ( @{ $self->{DATEV} } ) {
986 # each $transaction entry contains data from several acc_trans entries
987 # belonging to the same trans_id
989 my %datev_data = (); # data for one transaction
990 my $trans_lines = scalar(@{$transaction});
998 my $buchungstext = "";
1000 my $datevautomatik = 0;
1005 for (my $i = 0; $i < $trans_lines; $i++) {
1006 if ($trans_lines == 2) {
1007 if (abs($transaction->[$i]->{'amount'}) > abs($umsatz)) {
1008 $umsatz = $transaction->[$i]->{'amount'};
1011 if (abs($transaction->[$i]->{'umsatz'}) > abs($umsatz)) {
1012 $umsatz = $transaction->[$i]->{'umsatz'};
1015 if ($transaction->[$i]->{'datevautomatik'}) {
1016 $datevautomatik = 1;
1018 if ($transaction->[$i]->{'taxkey'}) {
1019 $taxkey = $transaction->[$i]->{'taxkey'};
1021 if ($transaction->[$i]->{'charttax'}) {
1022 $charttax = $transaction->[$i]->{'charttax'};
1024 if ($transaction->[$i]->{'amount'} > 0) {
1031 if ($trans_lines >= 2) {
1033 # Personenkontenerweiterung: accno has already been replaced if use_pk was set
1034 $datev_data{'gegenkonto'} = $transaction->[$haben]->{'accno'};
1035 $datev_data{'konto'} = $transaction->[$soll]->{'accno'};
1036 if ($transaction->[$haben]->{'invnumber'} ne "") {
1037 $datev_data{belegfeld1} = $transaction->[$haben]->{'invnumber'};
1039 $datev_data{datum} = $transaction->[$haben]->{'transdate'};
1040 $datev_data{waehrung} = 'EUR';
1041 $datev_data{kost1} = $transaction->[$haben]->{'departmentdescription'};
1042 $datev_data{kost2} = $transaction->[$haben]->{'projectdescription'};
1044 if ($transaction->[$haben]->{'name'} ne "") {
1045 $datev_data{buchungstext} = $transaction->[$haben]->{'name'};
1047 if (($transaction->[$haben]->{'ustid'} // '') ne "") {
1048 $datev_data{ustid} = $transaction->[$haben]->{'ustid'};
1050 if (($transaction->[$haben]->{'duedate'} // '') ne "") {
1051 $datev_data{belegfeld2} = $transaction->[$haben]->{'duedate'};
1054 $datev_data{umsatz} = abs($umsatz); # sales invoices without tax have a different sign???
1056 # Dies ist die einzige Stelle die datevautomatik auswertet. Was soll gesagt werden?
1057 # Im Prinzip hat jeder acc_trans Eintrag einen Steuerschlüssel, außer, bei gewissen Fällen
1058 # wie: Kreditorenbuchung mit negativen Vorzeichen, SEPA-Export oder Rechnungen die per
1059 # Skript angelegt werden.
1060 # Also falls ein Steuerschlüssel da ist und NICHT datevautomatik diesen Block hinzufügen.
1061 # Oder aber datevautomatik ist WAHR, aber der Steuerschlüssel in der acc_trans weicht
1062 # von dem in der Chart ab: Also wahrscheinlich Programmfehler (NULL übergeben, statt
1063 # DATEV-Steuerschlüssel) oder der Steuerschlüssel des Kontos weicht WIRKLICH von dem Eintrag in der
1064 # acc_trans ab. Gibt es für diesen Fall eine plausiblen Grund?
1067 # only set buchungsschluessel if the following conditions are met:
1068 if ( ( $datevautomatik || $taxkey)
1069 && (!$datevautomatik || ($datevautomatik && ($charttax ne $taxkey)))) {
1070 # $datev_data{buchungsschluessel} = !$datevautomatik ? $taxkey : "4";
1071 $datev_data{buchungsschluessel} = $taxkey;
1073 # set lock for each transaction
1074 $datev_data{locked} = $self->locked;
1076 push(@datev_lines, \%datev_data) if $datev_data{umsatz};
1079 # example of modifying export data:
1080 # foreach my $datev_line ( @datev_lines ) {
1081 # if ( $datev_line{"konto"} eq '1234' ) {
1082 # $datev_line{"konto"} = '9999';
1087 return \@datev_lines;
1091 sub kne_buchungsexport {
1092 $main::lxdebug->enter_sub();
1100 my $filename = "ED00001";
1101 my $evfile = "EV01";
1104 my $ed_filename = $self->export_path . $filename;
1106 my $fromto = $self->fromto;
1108 $self->generate_datev_data(from_to => $self->fromto); # fetches data from db, transforms data and fills $self->{DATEV}
1109 return if $self->errors;
1111 my @datev_lines = @{ $self->generate_datev_lines };
1114 my $umsatzsumme = sum map { $_->{umsatz} } @datev_lines;
1116 # prepare kne file, everything gets stored in ED00001
1117 my $header = $self->make_kne_data_header($form);
1118 my $kne_file = SL::DATEV::KNEFile->new();
1119 $kne_file->add_block($header);
1121 my $iconv = $::locale->{iconv_utf8};
1122 my %umlaute = ($iconv->convert('ä') => 'ae',
1123 $iconv->convert('ö') => 'oe',
1124 $iconv->convert('ü') => 'ue',
1125 $iconv->convert('Ä') => 'Ae',
1126 $iconv->convert('Ö') => 'Oe',
1127 $iconv->convert('Ü') => 'Ue',
1128 $iconv->convert('ß') => 'sz');
1130 # add the data from @datev_lines to the kne_file, formatting as needed
1131 foreach my $kne ( @datev_lines ) {
1132 $kne_file->add_block("+" . $kne_file->format_amount(abs($kne->{umsatz}), 0));
1134 # only add buchungsschluessel if it was previously defined
1135 $kne_file->add_block("\x6C" . $kne->{buchungsschluessel}) if defined $kne->{buchungsschluessel};
1137 # ($kne->{gegenkonto}) = $kne->{gegenkonto} =~ /^(\d+)/;
1138 $kne_file->add_block("a" . trim_leading_zeroes($kne->{gegenkonto}));
1140 if ( $kne->{belegfeld1} ) {
1141 my $invnumber = $kne->{belegfeld1};
1142 foreach my $umlaut (keys(%umlaute)) {
1143 $invnumber =~ s/${umlaut}/${umlaute{$umlaut}}/g;
1145 $invnumber =~ s/[^0-9A-Za-z\$\%\&\*\+\-\/]//g;
1146 $invnumber = substr($invnumber, 0, 12);
1147 $invnumber =~ s/\ *$//;
1148 $kne_file->add_block("\xBD" . $invnumber . "\x1C");
1151 $kne_file->add_block("\xBE" . &datetofour($kne->{belegfeld2},1) . "\x1C");
1153 $kne_file->add_block("d" . &datetofour($kne->{datum},0));
1155 # ($kne->{konto}) = $kne->{konto} =~ /^(\d+)/;
1156 $kne_file->add_block("e" . trim_leading_zeroes($kne->{konto}));
1158 my $name = $kne->{buchungstext};
1159 foreach my $umlaut (keys(%umlaute)) {
1160 $name =~ s/${umlaut}/${umlaute{$umlaut}}/g;
1162 $name =~ s/[^0-9A-Za-z\$\%\&\*\+\-\ \/]//g;
1163 $name = substr($name, 0, 30);
1165 $kne_file->add_block("\x1E" . $name . "\x1C");
1167 $kne_file->add_block("\xBA" . $kne->{'ustid'} . "\x1C") if $kne->{'ustid'};
1169 $kne_file->add_block("\xB3" . $kne->{'waehrung'} . "\x1C" . "\x79");
1172 $umsatzsumme = $kne_file->format_amount(abs($umsatzsumme), 0);
1173 my $mandantenendsumme = "x" . $kne_file->format_amount($umsatzsumme / 100.0, 14) . "\x79\x7a";
1175 $kne_file->add_block($mandantenendsumme);
1178 open(ED, ">", $ed_filename) or die "can't open outputfile: $!\n";
1179 print(ED $kne_file->get_data());
1182 $ed_versionset[$fileno] = $self->make_ed_versionset($header, $filename, $kne_file->get_block_count());
1184 #Make EV Verwaltungsdatei
1185 my $ev_header = $self->make_ev_header($form, $fileno);
1186 my $ev_filename = $self->export_path . $evfile;
1187 push(@filenames, $evfile);
1188 open(EV, ">", $ev_filename) or die "can't open outputfile: EV01\n";
1189 print(EV $ev_header);
1191 foreach my $file (@ed_versionset) {
1197 $self->add_filenames(@filenames);
1199 $main::lxdebug->leave_sub();
1201 return { 'download_token' => $self->download_token, 'filenames' => \@filenames };
1204 sub kne_stammdatenexport {
1205 $main::lxdebug->enter_sub();
1210 $self->get_datev_stamm->{abrechnungsnr} = "99";
1214 my $filename = "ED00000";
1215 my $evfile = "EV01";
1220 my $remaining_bytes = 256;
1221 my $total_bytes = 256;
1222 my $buchungssatz = "";
1224 my $ed_filename = $self->export_path . $filename;
1225 push(@filenames, $filename);
1226 open(ED, ">", $ed_filename) or die "can't open outputfile: $!\n";
1227 my $header = $self->make_kne_data_header($form);
1228 $remaining_bytes -= length($header);
1232 my (@where, @values) = ((), ());
1233 if ($self->accnofrom) {
1234 push @where, 'c.accno >= ?';
1235 push @values, $self->accnofrom;
1237 if ($self->accnoto) {
1238 push @where, 'c.accno <= ?';
1239 push @values, $self->accnoto;
1242 my $where_str = @where ? ' WHERE ' . join(' AND ', map { "($_)" } @where) : '';
1244 my $query = qq|SELECT c.accno, c.description
1249 my $sth = $self->dbh->prepare($query);
1250 $sth->execute(@values) || $form->dberror($query);
1252 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
1253 if (($remaining_bytes - length("t" . $ref->{'accno'})) <= 6) {
1254 $fuellzeichen = ($blockcount * 256 - length($buchungssatz . $header));
1255 $buchungssatz .= "\x00" x $fuellzeichen;
1257 $total_bytes = ($blockcount) * 256;
1259 $buchungssatz .= "t" . $ref->{'accno'};
1260 $remaining_bytes = $total_bytes - length($buchungssatz . $header);
1261 $ref->{'description'} =~ s/[^0-9A-Za-z\$\%\&\*\+\-\/]//g;
1262 $ref->{'description'} = substr($ref->{'description'}, 0, 40);
1263 $ref->{'description'} =~ s/\ *$//;
1266 ($remaining_bytes - length("\x1E" . $ref->{'description'} . "\x1C\x79")
1269 $fuellzeichen = ($blockcount * 256 - length($buchungssatz . $header));
1270 $buchungssatz .= "\x00" x $fuellzeichen;
1272 $total_bytes = ($blockcount) * 256;
1274 $buchungssatz .= "\x1E" . $ref->{'description'} . "\x1C\x79";
1275 $remaining_bytes = $total_bytes - length($buchungssatz . $header);
1280 print(ED $buchungssatz);
1281 $fuellzeichen = 256 - (length($header . $buchungssatz . "z") % 256);
1282 my $dateiende = "\x00" x $fuellzeichen;
1284 print(ED $dateiende);
1287 #Make EV Verwaltungsdatei
1289 $self->make_ed_versionset($header, $filename, $blockcount);
1291 my $ev_header = $self->make_ev_header($form, $fileno);
1292 my $ev_filename = $self->export_path . $evfile;
1293 push(@filenames, $evfile);
1294 open(EV, ">", $ev_filename) or die "can't open outputfile: EV01\n";
1295 print(EV $ev_header);
1297 foreach my $file (@ed_versionset) {
1298 print(EV $ed_versionset[$file]);
1302 $self->add_filenames(@filenames);
1304 $main::lxdebug->leave_sub();
1306 return { 'download_token' => $self->download_token, 'filenames' => \@filenames };
1311 return $accno . ('0' x (6 - min(length($accno), 6)));
1314 sub csv_export_for_tax_accountant {
1317 $self->generate_datev_data(from_to => $self->fromto);
1319 foreach my $transaction (@{ $self->{DATEV} }) {
1320 foreach my $entry (@{ $transaction }) {
1321 $entry->{sortkey} = join '-', map { lc } (DateTime->from_kivitendo($entry->{transdate})->strftime('%Y%m%d'), $entry->{name}, $entry->{reference});
1326 partition_by { $_->[0]->{table} }
1327 sort_by { $_->[0]->{sortkey} }
1328 grep { 2 == scalar(@{ $_ }) }
1329 @{ $self->{DATEV} };
1332 acc_trans_id => { 'text' => $::locale->text('ID'), },
1333 amount => { 'text' => $::locale->text('Amount'), },
1334 credit_accname => { 'text' => $::locale->text('Credit Account Name'), },
1335 credit_accno => { 'text' => $::locale->text('Credit Account'), },
1336 debit_accname => { 'text' => $::locale->text('Debit Account Name'), },
1337 debit_accno => { 'text' => $::locale->text('Debit Account'), },
1338 invnumber => { 'text' => $::locale->text('Reference'), },
1339 name => { 'text' => $::locale->text('Name'), },
1340 notes => { 'text' => $::locale->text('Notes'), },
1341 tax => { 'text' => $::locale->text('Tax'), },
1342 taxkey => { 'text' => $::locale->text('Taxkey'), },
1343 tax_accname => { 'text' => $::locale->text('Tax Account Name'), },
1344 tax_accno => { 'text' => $::locale->text('Tax Account'), },
1345 transdate => { 'text' => $::locale->text('Transdate'), },
1346 vcnumber => { 'text' => $::locale->text('Customer/Vendor Number'), },
1350 acc_trans_id name vcnumber
1351 transdate invnumber amount
1352 debit_accno debit_accname
1353 credit_accno credit_accname
1355 tax_accno tax_accname taxkey
1359 my %filenames_by_type = (
1360 ar => $::locale->text('AR Transactions'),
1361 ap => $::locale->text('AP Transactions'),
1362 gl => $::locale->text('GL Transactions'),
1366 foreach my $type (qw(ap ar)) {
1370 filename => sprintf('%s %s - %s.csv', $filenames_by_type{$type}, $self->from->to_kivitendo, $self->to->to_kivitendo),
1371 csv => Text::CSV_XS->new({
1379 filename => sprintf('Zahlungen %s %s - %s.csv', $filenames_by_type{$type}, $self->from->to_kivitendo, $self->to->to_kivitendo),
1380 csv => Text::CSV_XS->new({
1388 foreach my $csv (values %csvs) {
1389 $csv->{out} = IO::File->new($self->export_path . '/' . $csv->{filename}, '>:encoding(utf8)') ;
1390 $csv->{csv}->print($csv->{out}, [ map { $column_defs{$_}->{text} } @columns ]);
1392 push @filenames, $csv->{filename};
1395 foreach my $transaction (@{ $transactions{$type} }) {
1396 my $is_payment = any { $_->{link} =~ m{A[PR]_paid} } @{ $transaction };
1397 my $csv = $is_payment ? $csvs{payments} : $csvs{invoices};
1399 my ($soll, $haben) = map { $transaction->[$_] } ($transaction->[0]->{amount} > 0 ? (1, 0) : (0, 1));
1400 my $tax = defined($soll->{tax_accno}) ? $soll : $haben;
1401 my $amount = defined($soll->{net_amount}) ? $soll : $haben;
1402 $haben->{notes} = ($haben->{memo} || $soll->{memo}) if $is_payment;
1403 $haben->{notes} //= '';
1404 $haben->{notes} = SL::HTML::Util->strip($haben->{notes});
1405 $haben->{notes} =~ s{\r}{}g;
1406 $haben->{notes} =~ s{\n+}{ }g;
1409 amount => $::form->format_amount({ numberformat => '1000,00' }, abs($amount->{amount}), 2),
1410 debit_accno => _format_accno($soll->{accno}),
1411 debit_accname => $soll->{accname},
1412 credit_accno => _format_accno($haben->{accno}),
1413 credit_accname => $haben->{accname},
1414 tax => $::form->format_amount({ numberformat => '1000,00' }, abs($amount->{amount}) - abs($amount->{net_amount}), 2),
1415 notes => $haben->{notes},
1416 (map { ($_ => $tax->{$_}) } qw(taxkey tax_accname tax_accno)),
1417 (map { ($_ => ($haben->{$_} // $soll->{$_})) } qw(acc_trans_id invnumber name vcnumber transdate)),
1420 $csv->{csv}->print($csv->{out}, [ map { $row{$_} } @columns ]);
1423 $_->{out}->close for values %csvs;
1426 $self->add_filenames(@filenames);
1428 return { download_token => $self->download_token, filenames => \@filenames };
1431 sub check_vcnumbers_are_valid_pk_numbers {
1434 # better use a class variable and set this in sub new (also needed in DATEV::CSV)
1435 # calculation is also a bit more sane in sub check_valid_length_of_accounts
1436 my $length_of_accounts = length(SL::DB::Manager::Chart->get_first(where => [charttype => 'A'])->accno) // 4;
1437 my $pk_length = $length_of_accounts + 1;
1438 my $query = <<"SQL";
1439 SELECT customernumber AS vcnumber FROM customer WHERE customernumber !~ '^[[:digit:]]{$pk_length}\$'
1441 SELECT vendornumber AS vcnumber FROM vendor WHERE vendornumber !~ '^[[:digit:]]{$pk_length}\$'
1444 my ($has_non_pk_accounts) = selectrow_query($::form, SL::DB->client->dbh, $query);
1445 return defined $has_non_pk_accounts ? 0 : 1;
1449 sub check_valid_length_of_accounts {
1452 my $query = <<"SQL";
1453 SELECT DISTINCT char_length (accno) FROM chart WHERE charttype='A' AND id in (select chart_id from acc_trans);
1456 my $accno_length = selectall_hashref_query($::form, SL::DB->client->dbh, $query);
1457 if (1 < scalar @$accno_length) {
1458 $::form->error(t8("Invalid combination of ledger account number length." .
1459 " Mismatch length of #1 with length of #2. Please check your account settings. ",
1460 $accno_length->[0]->{char_length}, $accno_length->[1]->{char_length}));
1466 clean_temporary_directories();
1477 SL::DATEV - kivitendo DATEV Export module
1481 use SL::DATEV qw(:CONSTANTS);
1483 my $startdate = DateTime->new(year => 2014, month => 9, day => 1);
1484 my $enddate = DateTime->new(year => 2014, month => 9, day => 31);
1485 my $datev = SL::DATEV->new(
1486 exporttype => DATEV_ET_BUCHUNGEN,
1487 format => DATEV_FORMAT_KNE,
1492 # To only export transactions from a specific trans_id: (from and to are ignored)
1493 my $invoice = SL::DB::Manager::Invoice->find_by( invnumber => '216' );
1494 my $datev = SL::DATEV->new(
1495 exporttype => DATEV_ET_BUCHUNGEN,
1496 format => DATEV_FORMAT_KNE,
1497 trans_id => $invoice->trans_id,
1500 my $datev = SL::DATEV->new(
1501 exporttype => DATEV_ET_STAMM,
1502 format => DATEV_FORMAT_KNE,
1503 accnofrom => $start_account_number,
1504 accnoto => $end_account_number,
1507 # get or set datev stamm
1508 my $hashref = $datev->get_datev_stamm;
1509 $datev->save_datev_stamm($hashref);
1511 # manually clean up temporary directories older than 8 hours
1512 $datev->clean_temporary_directories;
1517 if ($datev->errors) {
1518 die join "\n", $datev->error;
1521 # get relevant data for saving the export:
1522 my $dl_token = $datev->download_token;
1523 my $path = $datev->export_path;
1524 my @files = $datev->filenames;
1526 # retrieving an export at a later time
1527 my $datev = SL::DATEV->new(
1528 download_token => $dl_token_from_user,
1531 my $path = $datev->export_path;
1532 my @files = glob("$path/*");
1534 # Only test the datev data of a specific trans_id, without generating an
1535 # export file, but filling $datev->errors if errors exist
1537 my $datev = SL::DATEV->new(
1538 trans_id => $invoice->trans_id,
1540 $datev->generate_datev_data;
1541 # if ($datev->errors) { ...
1546 This module implements the DATEV export standard. For usage see above.
1554 Generic constructor. See section attributes for information about what to pass.
1556 =item generate_datev_data
1558 Fetches all transactions from the database (via a trans_id or a date range),
1559 and does an initial transformation (e.g. filters out tax, determines
1560 the brutto amount, checks split transactions ...) and stores this data in
1563 If any errors are found these are collected in $self->errors.
1565 This function is needed for all the exports, but can be also called
1566 independently in order to check transactions for DATEV compatibility.
1568 =item generate_datev_lines
1570 Parse the data in $self->{DATEV} and transform it into a format that can be
1571 used by DATEV, e.g. determines Konto and Gegenkonto, the taxkey, ...
1573 The transformed data is returned as an arrayref, which is ready to be converted
1574 to a DATEV data format, e.g. KNE, OBE, CSV, ...
1576 At this stage the "DATEV rule" has already been applied to the taxkeys, i.e.
1577 entries with datevautomatik have an empty taxkey, as the taxkey is already
1578 determined by the chart.
1580 =item get_datev_stamm
1582 Loads DATEV Stammdaten and returns as hashref.
1584 =item save_datev_stamm HASHREF
1586 Saves DATEV Stammdaten from provided hashref.
1590 See L<CONSTANTS> for possible values
1592 =item has_exporttype
1594 Returns true if an exporttype has been set. Without exporttype most report functions won't work.
1598 Specifies the designated format of the export. Currently only KNE export is implemented.
1600 See L<CONSTANTS> for possible values
1604 Returns true if a format has been set. Without format most report functions won't work.
1606 =item download_token
1608 Returns a download token for this DATEV object.
1610 Note: If either a download_token or export_path were set at the creation these are infered, otherwise randomly generated.
1614 Returns an export_path for this DATEV object.
1616 Note: If either a download_token or export_path were set at the creation these are infered, otherwise randomly generated.
1620 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.
1622 =item net_gross_differences
1624 If there were any net gross differences during calculation they will be collected here.
1626 =item sum_net_gross_differences
1628 Sum of all differences.
1630 =item clean_temporary_directories
1632 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.
1636 Returns a list of errors that occured. If no errors occured, the export was a success.
1640 Exports data. You have to have set L<exporttype> and L<format> or an error will
1641 occur. OBE exports are currently not implemented.
1643 =item csv_export_for_tax_accountant
1645 Generates up to four downloadable csv files containing data about sales and
1646 purchase invoices, and their respective payments:
1649 my $startdate = DateTime->new(year => 2012, month => 1, day => 1);
1650 my $enddate = DateTime->new(year => 2012, month => 12, day => 31);
1651 SL::DATEV->new(from => $startdate, to => $enddate)->csv_export_for_tax_accountant;
1653 # 'download_token' => '1488551625-815654-22430',
1655 # 'Zahlungen Kreditorenbuchungen 2012-01-01 - 2012-12-31.csv',
1656 # 'Kreditorenbuchungen 2012-01-01 - 2012-12-31.csv',
1657 # 'Zahlungen Debitorenbuchungen 2012-01-01 - 2012-12-31.csv',
1658 # 'Debitorenbuchungen 2012-01-01 - 2012-12-31.csv'
1663 =item check_vcnumbers_are_valid_pk_numbers
1665 Returns 1 if all vcnumbers are suitable for the DATEV export, 0 if not.
1667 Finds the default length of charts (e.g. 4), adds 1 for the pk chart length
1668 (e.g. 5), and checks the database for any customers or vendors whose customer-
1669 or vendornumber doesn't consist of only numbers with exactly that length. E.g.
1670 for a chart length of four "10001" would be ok, but not "10001b" or "1000".
1672 All vcnumbers are checked, obsolete customers or vendors aren't exempt.
1674 There is also no check for the typical customer range 10000-69999 and the
1675 typical vendor range 70000-99999.
1677 =item check_valid_length_of_accounts
1679 Returns 1 if all currently booked accounts have only one common number length domain (e.g. 4 or 6).
1680 Will throw an error if more than one distinct size is detected.
1681 The error message gives a short hint with the value of the (at least)
1682 two mismatching number length domains.
1688 This is a list of attributes set in either the C<new> or a method of the same name.
1694 Set a database handle to use in the process. This allows for an export to be
1695 done on a transaction in progress without committing first.
1697 Note: If you don't want this code to commit, simply providing a dbh is not
1698 enough enymore. You'll have to wrap the call into a transaction yourself, so
1699 that the internal transaction does not commit.
1703 See L<CONSTANTS> for possible values. This MUST be set before export is called.
1707 See L<CONSTANTS> for possible values. This MUST be set before export is called.
1709 =item download_token
1711 Can be set on creation to retrieve a prior export for download.
1717 Set boundary dates for the export. Unless a trans_id is passed these MUST be
1718 set for the export to work.
1722 To check only one gl/ar/ap transaction, pass the trans_id. The attributes
1723 L<from> and L<to> are currently still needed for the query to be assembled
1730 Set boundary account numbers for the export. Only useful for a stammdaten export.
1734 Boolean if the transactions are locked (read-only in kivitenod) or not.
1735 Default value is false
1741 =head2 Supplied to L<exporttype>
1745 =item DATEV_ET_BUCHUNGEN
1747 =item DATEV_ET_STAMM
1751 =head2 Supplied to L<format>.
1755 =item DATEV_FORMAT_KNE
1757 =item DATEV_FORMAT_OBE
1761 =head1 ERROR HANDLING
1763 This module will die in the following cases:
1769 No or unrecognized exporttype or format was provided for an export
1773 OBE export was called, which is not yet implemented.
1781 Errors that occur during th actual export will be collected in L<errors>. The following types can occur at the moment:
1787 C<Unbalanced Ledger!>. Exactly that, your ledger is unbalanced. Should never occur.
1791 C<Datev-Export fehlgeschlagen! Bei Transaktion %d (%f).> This error occurs if a
1792 transaction could not be reliably sorted out, or had rounding errors above the acceptable threshold.
1796 =head1 BUGS AND CAVEATS
1802 Handling of Vollvorlauf is currently not fully implemented. You must provide both from and to in order to get a working export.
1806 OBE export is currently not implemented.
1812 - handling of export_path and download token is a bit dodgy, clean that up.
1816 L<SL::DATEV::KNEFile>
1821 Philip Reetz E<lt>p.reetz@linet-services.deE<gt>,
1823 Moritz Bunkus E<lt>m.bunkus@linet-services.deE<gt>,
1825 Jan Büren E<lt>jan@lx-office-hosting.deE<gt>,
1827 Geoffrey Richardson E<lt>information@lx-office-hosting.deE<gt>,
1829 Sven Schöling E<lt>s.schoeling@linet-services.deE<gt>,