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);
44 use Exporter qw(import);
47 use List::MoreUtils qw(any);
48 use List::Util qw(min max sum);
49 use List::UtilsBy qw(partition_by sort_by);
51 use Time::HiRes qw(gettimeofday);
56 DATEV_ET_BUCHUNGEN => $i++,
57 DATEV_ET_STAMM => $i++,
60 DATEV_FORMAT_KNE => $i++,
61 DATEV_FORMAT_OBE => $i++,
62 DATEV_FORMAT_CSV => $i++,
66 my @export_constants = qw(DATEV_ET_BUCHUNGEN DATEV_ET_STAMM DATEV_ET_CSV DATEV_FORMAT_KNE DATEV_FORMAT_OBE DATEV_FORMAT_CSV);
67 our @EXPORT_OK = (@export_constants);
68 our %EXPORT_TAGS = (CONSTANTS => [ @export_constants ]);
75 my $obj = bless {}, $class;
77 $obj->$_($data{$_}) for keys %data;
84 $self->{exporttype} = $_[0] if @_;
85 return $self->{exporttype};
89 defined $_[0]->{exporttype};
94 $self->{format} = $_[0] if @_;
95 return $self->{format};
99 defined $_[0]->{format};
102 sub _get_export_path {
103 $main::lxdebug->enter_sub();
105 my ($a, $b) = gettimeofday();
106 my $path = _get_path_for_download_token("${a}-${b}-${$}");
108 mkpath($path) unless (-d $path);
110 $main::lxdebug->leave_sub();
115 sub _get_path_for_download_token {
116 $main::lxdebug->enter_sub();
118 my $token = shift || '';
121 if ($token =~ m|^(\d+)-(\d+)-(\d+)$|) {
122 $path = $::lx_office_conf{paths}->{userspath} . "/datev-export-${1}-${2}-${3}/";
125 $main::lxdebug->leave_sub();
130 sub _get_download_token_for_path {
131 $main::lxdebug->enter_sub();
136 if ($path =~ m|.*datev-export-(\d+)-(\d+)-(\d+)/?$|) {
137 $token = "${1}-${2}-${3}";
140 $main::lxdebug->leave_sub();
147 $self->{download_token} = $_[0] if @_;
148 return $self->{download_token} ||= _get_download_token_for_path($self->export_path);
154 return $self->{export_path} ||= _get_path_for_download_token($self->{download_token}) || _get_export_path();
159 push @{ $self->{filenames} ||= [] }, @_;
163 return @{ $_[0]{filenames} || [] };
168 push @{ $self->{errors} ||= [] }, @_;
172 return @{ $_[0]{errors} || [] };
175 sub add_net_gross_differences {
177 push @{ $self->{net_gross_differences} ||= [] }, @_;
180 sub net_gross_differences {
181 return @{ $_[0]{net_gross_differences} || [] };
184 sub sum_net_gross_differences {
185 return sum $_[0]->net_gross_differences;
192 $self->{from} = $_[0];
195 return $self->{from};
212 $self->{trans_id} = $_[0];
215 die "illegal trans_id passed for DATEV export: " . $self->{trans_id} . "\n" unless $self->{trans_id} =~ m/^\d+$/;
217 return $self->{trans_id};
224 $self->{warnings} = [@_];
226 return $self->{warnings};
234 $self->{use_pk} = $_[0];
237 return $self->{use_pk};
244 $self->{accnofrom} = $_[0];
247 return $self->{accnofrom};
254 $self->{accnoto} = $_[0];
257 return $self->{accnoto};
265 $self->{dbh} = $_[0];
266 $self->{provided_dbh} = 1;
269 $self->{dbh} ||= SL::DB->client->dbh;
276 sub clean_temporary_directories {
277 $::lxdebug->enter_sub;
279 foreach my $path (glob($::lx_office_conf{paths}->{userspath} . "/datev-export-*")) {
280 next unless -d $path;
282 my $mtime = (stat($path))[9];
283 next if ((time() - $mtime) < 8 * 60 * 60);
288 $::lxdebug->leave_sub;
292 $main::lxdebug->enter_sub();
294 my $text = shift // '';
295 my $field_len = shift;
296 my $fill_char = shift;
297 my $alignment = shift || 'right';
299 my $text_len = length $text;
301 if ($field_len < $text_len) {
302 $text = substr $text, 0, $field_len;
304 } elsif ($field_len > $text_len) {
305 my $filler = ($fill_char) x ($field_len - $text_len);
306 $text = $alignment eq 'right' ? $filler . $text : $text . $filler;
309 $main::lxdebug->leave_sub();
314 sub get_datev_stamm {
315 return $_[0]{stamm} ||= selectfirst_hashref_query($::form, $_[0]->dbh, 'SELECT * FROM datev');
318 sub save_datev_stamm {
319 my ($self, $data) = @_;
321 SL::DB->client->with_transaction(sub {
322 do_query($::form, $self->dbh, 'DELETE FROM datev');
324 my @columns = qw(beraternr beratername dfvkz mandantennr datentraegernr abrechnungsnr);
326 my $query = "INSERT INTO datev (" . join(', ', @columns) . ") VALUES (" . join(', ', ('?') x @columns) . ")";
327 do_query($::form, $self->dbh, $query, map { $data->{$_} } @columns);
329 }) or do { die SL::DB->client->error };
336 die 'no format set!' unless $self->has_format;
338 if ($self->format == DATEV_FORMAT_CSV) {
339 $result = $self->csv_export;
340 } elsif ($self->format == DATEV_FORMAT_KNE) {
341 $result = $self->kne_export;
342 } elsif ($self->format == DATEV_FORMAT_OBE) {
343 $result = $self->obe_export;
345 die 'unrecognized export format';
355 die 'no exporttype set!' unless $self->has_exporttype;
357 if ($self->exporttype == DATEV_ET_BUCHUNGEN) {
358 $result = $self->kne_buchungsexport;
359 } elsif ($self->exporttype == DATEV_ET_STAMM) {
360 $result = $self->kne_stammdatenexport;
361 } elsif ($self->exporttype == DATEV_ET_CSV) {
362 $result = $self->csv_export_for_tax_accountant;
364 die 'unrecognized exporttype';
374 die 'no exporttype set!' unless $self->has_exporttype;
376 if ($self->exporttype == DATEV_ET_BUCHUNGEN) {
378 $self->generate_datev_data(from_to => $self->fromto);
379 return if $self->errors;
381 my $datev_csv = SL::DATEV::CSV->new(
382 datev_lines => $self->generate_datev_lines,
385 locked => $self->locked,
389 my $filename = "EXTF_DATEV_kivitendo" . $self->from->ymd() . '-' . $self->to->ymd() . ".csv";
391 my $csv = Text::CSV_XS->new({
396 }) or die "Cannot use CSV: ".Text::CSV_XS->error_diag();
398 # get encoding from defaults - use cp1252 if DATEV strict export is used
399 my $enc = ($::instance_conf->get_datev_export_format eq 'cp1252') ? 'cp1252' : 'utf-8';
400 my $csv_file = IO::File->new($self->export_path . '/' . $filename, ">:encoding($enc)") or die "Can't open: $!";
402 $csv->print($csv_file, $_) for @{ $datev_csv->header };
403 $csv->print($csv_file, $_) for @{ $datev_csv->lines };
405 $self->{warnings} = $datev_csv->warnings;
407 # convert utf-8 to cp1252//translit if set
408 if ($::instance_conf->get_datev_export_format eq 'cp1252-translit') {
410 my $filename_translit = "EXTF_DATEV_kivitendo_translit" . $self->from->ymd() . '-' . $self->to->ymd() . ".csv";
411 open my $fh_in, '<:encoding(UTF-8)', $self->export_path . '/' . $filename or die "could not open $filename for reading: $!";
412 open my $fh_out, '>', $self->export_path . '/' . $filename_translit or die "could not open $filename_translit for writing: $!";
414 my $converter = SL::Iconv->new("utf-8", "cp1252//translit");
416 print $fh_out $converter->convert($_) while <$fh_in>;
420 unlink $self->export_path . '/' . $filename or warn "Could not unlink $filename: $!";
421 $filename = $filename_translit;
424 return { download_token => $self->download_token, filenames => $filename };
426 } elsif ($self->exporttype == DATEV_ET_STAMM) {
427 die 'will never be implemented';
428 # 'Background: Export should only contain non
429 # DATEV-Charts and DATEV import will only
430 # import new Charts.'
431 } elsif ($self->exporttype == DATEV_ET_CSV) {
432 $result = $self->csv_export_for_tax_accountant;
434 die 'unrecognized exporttype';
441 die 'not yet implemented';
447 return unless $self->from && $self->to;
449 return "transdate >= '" . $self->from->to_lxoffice . "' and transdate <= '" . $self->to->to_lxoffice . "'";
460 $self->{locked} = $_[0];
462 return $self->{locked};
465 sub generate_datev_data {
466 $main::lxdebug->enter_sub();
468 my ($self, %params) = @_;
469 my $fromto = $params{from_to} // '';
470 my $progress_callback = $params{progress_callback} || sub {};
472 my $form = $main::form;
474 my $trans_id_filter = '';
475 my $ar_department_id_filter = '';
476 my $ap_department_id_filter = '';
477 my $gl_department_id_filter = '';
478 if ( $form->{department_id} ) {
479 $ar_department_id_filter = " AND ar.department_id = ? ";
480 $ap_department_id_filter = " AND ap.department_id = ? ";
481 $gl_department_id_filter = " AND gl.department_id = ? ";
484 my ($gl_itime_filter, $ar_itime_filter, $ap_itime_filter);
485 if ( $form->{gldatefrom} ) {
486 $gl_itime_filter = " AND gl.itime >= ? ";
487 $ar_itime_filter = " AND ar.itime >= ? ";
488 $ap_itime_filter = " AND ap.itime >= ? ";
490 $gl_itime_filter = "";
491 $ar_itime_filter = "";
492 $ap_itime_filter = "";
495 if ( $self->{trans_id} ) {
496 # ignore dates when trans_id is passed so that the entire transaction is
497 # checked, not just either the initial bookings or the subsequent payments
498 # (the transdates will likely differ)
500 $trans_id_filter = 'ac.trans_id = ' . $self->trans_id;
502 $fromto =~ s/transdate/ac\.transdate/g;
507 my $filter = ''; # Useful for debugging purposes
509 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');
511 my $ar_accno = "c.accno";
512 my $ap_accno = "c.accno";
513 if ( $self->use_pk ) {
514 $ar_accno = "CASE WHEN ac.chart_link = 'AR' THEN ct.customernumber ELSE c.accno END as accno";
515 $ap_accno = "CASE WHEN ac.chart_link = 'AP' THEN ct.vendornumber ELSE c.accno END as accno";
519 qq|SELECT ac.acc_trans_id, ac.transdate, ac.gldate, ac.trans_id,ar.id, ac.amount, ac.taxkey, ac.memo,
520 ar.invnumber, ar.duedate, ar.amount as umsatz, ar.deliverydate, ar.itime::date,
521 ct.name, ct.ustid, ct.customernumber AS vcnumber, ct.id AS customer_id, NULL AS vendor_id,
522 $ar_accno, c.description AS accname, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
524 t.rate AS taxrate, t.taxdescription,
526 tc.accno AS tax_accno, tc.description AS tax_accname,
529 project.projectnumber as projectnumber, project.description as projectdescription,
530 department.description as departmentdescription
532 LEFT JOIN ar ON (ac.trans_id = ar.id)
533 LEFT JOIN customer ct ON (ar.customer_id = ct.id)
534 LEFT JOIN chart c ON (ac.chart_id = c.id)
535 LEFT JOIN tax t ON (ac.tax_id = t.id)
536 LEFT JOIN chart tc ON (t.chart_id = tc.id)
537 LEFT JOIN department ON (department.id = ar.department_id)
538 LEFT JOIN project ON (project.id = ar.globalproject_id)
539 WHERE (ar.id IS NOT NULL)
543 $ar_department_id_filter
548 SELECT ac.acc_trans_id, ac.transdate, ac.gldate, ac.trans_id,ap.id, ac.amount, ac.taxkey, ac.memo,
549 ap.invnumber, ap.duedate, ap.amount as umsatz, ap.deliverydate, ap.itime::date,
550 ct.name, ct.ustid, ct.vendornumber AS vcnumber, NULL AS customer_id, ct.id AS vendor_id,
551 $ap_accno, c.description AS accname, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
553 t.rate AS taxrate, t.taxdescription,
555 tc.accno AS tax_accno, tc.description AS tax_accname,
558 project.projectnumber as projectnumber, project.description as projectdescription,
559 department.description as departmentdescription
561 LEFT JOIN ap ON (ac.trans_id = ap.id)
562 LEFT JOIN vendor ct ON (ap.vendor_id = ct.id)
563 LEFT JOIN chart c ON (ac.chart_id = c.id)
564 LEFT JOIN tax t ON (ac.tax_id = t.id)
565 LEFT JOIN chart tc ON (t.chart_id = tc.id)
566 LEFT JOIN department ON (department.id = ap.department_id)
567 LEFT JOIN project ON (project.id = ap.globalproject_id)
568 WHERE (ap.id IS NOT NULL)
572 $ap_department_id_filter
577 SELECT ac.acc_trans_id, ac.transdate, ac.gldate, ac.trans_id,gl.id, ac.amount, ac.taxkey, ac.memo,
578 gl.reference AS invnumber, NULL AS duedate, ac.amount as umsatz, gl.deliverydate, gl.itime::date,
579 gl.description AS name, NULL as ustid, '' AS vcname, NULL AS customer_id, NULL AS vendor_id,
580 c.accno, c.description AS accname, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
582 t.rate AS taxrate, t.taxdescription,
584 tc.accno AS tax_accno, tc.description AS tax_accname,
587 '' as projectnumber, '' as projectdescription,
588 department.description as departmentdescription
590 LEFT JOIN gl ON (ac.trans_id = gl.id)
591 LEFT JOIN chart c ON (ac.chart_id = c.id)
592 LEFT JOIN tax t ON (ac.tax_id = t.id)
593 LEFT JOIN chart tc ON (t.chart_id = tc.id)
594 LEFT JOIN department ON (department.id = gl.department_id)
595 WHERE (gl.id IS NOT NULL)
599 $gl_department_id_filter
602 ORDER BY trans_id, acc_trans_id|;
605 if ( $form->{gldatefrom} or $form->{department_id} ) {
608 if ( $form->{gldatefrom} ) {
609 my $glfromdate = $::locale->parse_date_to_object($form->{gldatefrom});
610 die "illegal data" unless ref($glfromdate) eq 'DateTime';
611 push(@query_args, $glfromdate);
613 if ( $form->{department_id} ) {
614 push(@query_args, $form->{department_id});
619 my $sth = prepare_execute_query($form, $self->dbh, $query, @query_args);
625 while ( $continue && (my $ref = $sth->fetchrow_hashref("NAME_lc")) ) {
626 last unless $ref; # for single transactions
628 if (($counter % 500) == 0) {
629 $progress_callback->($counter);
632 my $trans = [ $ref ];
634 my $count = $ref->{amount};
637 # if the amount of a booking in a group is smaller than 0.02, any tax
638 # amounts will likely be smaller than 1 cent, so go into subcent mode
639 my $subcent = abs($count) < 0.02;
641 # records from acc_trans are ordered by trans_id and acc_trans_id
642 # first check for unbalanced ledger inside one trans_id
643 # there may be several groups inside a trans_id, e.g. the original booking and the payment
644 # each group individually should be exactly balanced and each group
645 # individually needs its own datev lines
647 # keep fetching new acc_trans lines until the end of a balanced group is reached
648 while (abs($count) > 0.01 || $firstrun || ($subcent && abs($count) > 0.005)) {
649 my $ref2 = $sth->fetchrow_hashref("NAME_lc");
655 # check if trans_id of current acc_trans line is still the same as the
656 # trans_id of the first line in group, i.e. we haven't finished a 0-group
657 # before moving on to the next trans_id, error will likely be in the old
660 if ($ref2->{trans_id} != $trans->[0]->{trans_id}) {
661 require SL::DB::Manager::AccTransaction;
662 if ( $trans->[0]->{trans_id} ) {
663 my $acc_trans_obj = SL::DB::Manager::AccTransaction->get_first(where => [ trans_id => $trans->[0]->{trans_id} ]);
664 $self->add_error(t8("Export error in transaction #1: Unbalanced ledger before next transaction (#2)",
665 $acc_trans_obj->transaction_name, $ref2->{trans_id})
671 push @{ $trans }, $ref2;
673 $count += $ref2->{amount};
677 foreach my $i (0 .. scalar(@{ $trans }) - 1) {
678 my $ref = $trans->[$i];
679 my $prev_ref = 0 < $i ? $trans->[$i - 1] : undef;
680 if ( $all_taxchart_ids{$ref->{id}}
681 && ($ref->{link} =~ m/(?:AP_tax|AR_tax)/)
682 && ( ($prev_ref && $prev_ref->{taxkey} && (_sign($ref->{amount}) == _sign($prev_ref->{amount})))
683 || $ref->{invoice})) {
687 if ( !$ref->{invoice} # we have a non-invoice booking (=gl)
688 && $ref->{is_tax} # that has "is_tax" set
689 && !($prev_ref->{is_tax}) # previous line wasn't is_tax
690 && (_sign($ref->{amount}) == _sign($prev_ref->{amount}))) { # and sign same as previous sign
691 $trans->[$i - 1]->{tax_amount} = $ref->{amount};
696 if (scalar(@{$trans}) <= 2) {
697 push @{ $self->{DATEV} }, $trans;
701 # determine at which array position the reference value (called absumsatz) is
702 # and which amount it has
704 for my $j (0 .. (scalar(@{$trans}) - 1)) {
707 # 1: gl transaction (Dialogbuchung), invoice is false, no double split booking allowed
709 # 2: sales or vendor invoice (Verkaufs- und Einkaufsrechnung): invoice is
710 # true, instead of absumsatz use link AR/AP (there should only be one
713 # 3. AR/AP transaction (Kreditoren- und Debitorenbuchung): invoice is false,
714 # instead of absumsatz use link AR/AP (there should only be one, so jump
715 # out of search as soon as you find it )
718 # for gl-bookings no split is allowed and there is no AR/AP account, so we always use the maximum value as a reference
719 # for ap/ar bookings we can always search for AR/AP in link and use that
720 if ( ( not $trans->[$j]->{'invoice'} and abs($trans->[$j]->{'amount'}) > abs($absumsatz) )
721 or ($trans->[$j]->{'invoice'} and ($trans->[$j]->{'link'} eq 'AR' or $trans->[$j]->{'link'} eq 'AP'))) {
722 $absumsatz = $trans->[$j]->{'amount'};
727 # Problem: we can't distinguish between AR and AP and normal invoices via boolean "invoice"
728 # for AR and AP transaction exit the loop as soon as an AR or AP account is found
729 # there must be only one AR or AP chart in the booking
730 # since it is possible to do this kind of things with GL too, make sure those don't get aborted in case someone
731 # manually pays an invoice in GL.
732 if ($trans->[$j]->{table} ne 'gl' and ($trans->[$j]->{'link'} eq 'AR' or $trans->[$j]->{'link'} eq 'AP')) {
733 $notsplitindex = $j; # position in booking with highest amount
734 $absumsatz = $trans->[$j]->{'amount'};
739 my $ml = ($trans->[0]->{'umsatz'} > 0) ? 1 : -1;
740 my $rounding_error = 0;
743 # go through each line and determine if it is a tax booking or not
744 # skip all tax lines and notsplitindex line
745 # push all other accounts (e.g. income or expense) with corresponding taxkey
747 for my $j (0 .. (scalar(@{$trans}) - 1)) {
748 if ( ($j != $notsplitindex)
749 && !$trans->[$j]->{is_tax}
750 && ( $trans->[$j]->{'taxkey'} eq ""
751 || $trans->[$j]->{'taxkey'} eq "0"
752 || $trans->[$j]->{'taxkey'} eq "1"
753 || $trans->[$j]->{'taxkey'} eq "10"
754 || $trans->[$j]->{'taxkey'} eq "11")) {
756 map { $new_trans{$_} = $trans->[$notsplitindex]->{$_}; } keys %{ $trans->[$notsplitindex] };
758 $absumsatz += $trans->[$j]->{'amount'};
759 $new_trans{'amount'} = $trans->[$j]->{'amount'} * (-1);
760 $new_trans{'umsatz'} = abs($trans->[$j]->{'amount'}) * $ml;
761 $trans->[$j]->{'umsatz'} = abs($trans->[$j]->{'amount'}) * $ml;
763 push @{ $self->{DATEV} }, [ \%new_trans, $trans->[$j] ];
765 } elsif (($j != $notsplitindex) && !$trans->[$j]->{is_tax}) {
768 map { $new_trans{$_} = $trans->[$notsplitindex]->{$_}; } keys %{ $trans->[$notsplitindex] };
770 my $tax_rate = $trans->[$j]->{'taxrate'};
771 $new_trans{'net_amount'} = $trans->[$j]->{'amount'} * -1;
772 $new_trans{'tax_rate'} = 1 + $tax_rate;
774 if (!$trans->[$j]->{'invoice'}) {
775 $new_trans{'amount'} = $form->round_amount(-1 * ($trans->[$j]->{amount} + $trans->[$j]->{tax_amount}), 2);
776 $new_trans{'umsatz'} = abs($new_trans{'amount'}) * $ml;
777 $trans->[$j]->{'umsatz'} = $new_trans{'umsatz'};
778 $absumsatz += -1 * $new_trans{'amount'};
781 my $unrounded = $trans->[$j]->{'amount'} * (1 + $tax_rate) * -1 + $rounding_error;
782 my $rounded = $form->round_amount($unrounded, 2);
784 $rounding_error = $unrounded - $rounded;
785 $new_trans{'amount'} = $rounded;
786 $new_trans{'umsatz'} = abs($rounded) * $ml;
787 $trans->[$j]->{'umsatz'} = $new_trans{umsatz};
788 $absumsatz -= $rounded;
791 push @{ $self->{DATEV} }, [ \%new_trans, $trans->[$j] ];
792 push @taxed, $self->{DATEV}->[-1];
798 while ((abs($absumsatz) >= 0.01) && (abs($absumsatz) < 1.00)) {
799 if ($idx >= scalar @taxed) {
800 last if (!$correction);
806 my $transaction = $taxed[$idx]->[0];
808 my $old_amount = $transaction->{amount};
809 my $old_correction = $correction;
812 if (!$transaction->{diff}) {
813 @possible_diffs = (0.01, -0.01);
815 @possible_diffs = ($transaction->{diff});
818 foreach my $diff (@possible_diffs) {
819 my $net_amount = $form->round_amount(($transaction->{amount} + $diff) / $transaction->{tax_rate}, 2);
820 next if ($net_amount != $transaction->{net_amount});
822 $transaction->{diff} = $diff;
823 $transaction->{amount} += $diff;
824 $transaction->{umsatz} += $diff;
834 $absumsatz = $form->round_amount($absumsatz, 2);
835 if (abs($absumsatz) >= (0.01 * (1 + scalar @taxed))) {
836 require SL::DB::Manager::AccTransaction;
837 my $acc_trans_obj = SL::DB::Manager::AccTransaction->get_first(where => [ trans_id => $trans->[0]->{trans_id} ]);
838 $self->add_error(t8("Export error in transaction #1: Rounding error too large #2",
839 $acc_trans_obj->transaction_name, $absumsatz)
841 } elsif (abs($absumsatz) >= 0.01) {
842 $self->add_net_gross_differences($absumsatz);
848 $::lxdebug->leave_sub;
851 sub make_kne_data_header {
852 $main::lxdebug->enter_sub();
854 my ($self, $form) = @_;
857 my $stamm = $self->get_datev_stamm;
859 my $jahr = $self->from ? $self->from->year : DateTime->today->year;
862 my $header = "\x1D\x181";
863 $header .= _fill($stamm->{datentraegernr}, 3, ' ', 'left');
864 $header .= ($self->fromto) ? "11" : "13"; # Anwendungsnummer
865 $header .= _fill($stamm->{dfvkz}, 2, '0');
866 $header .= _fill($stamm->{beraternr}, 7, '0');
867 $header .= _fill($stamm->{mandantennr}, 5, '0');
868 $header .= _fill(($stamm->{abrechnungsnr} // '') . $jahr, 6, '0');
870 $header .= $self->from ? $self->from->strftime('%d%m%y') : '';
871 $header .= $self->to ? $self->to->strftime('%d%m%y') : '';
875 $header .= $primanota;
878 $header .= _fill($stamm->{passwort}, 4, '0');
879 $header .= " " x 16; # Anwendungsinfo
880 $header .= " " x 16; # Inputinfo
884 my $versionssatz = $self->exporttype == DATEV_ET_BUCHUNGEN ? "\xB5" . "1," : "\xB6" . "1,";
886 my $query = qq|SELECT accno FROM chart LIMIT 1|;
887 my $ref = selectfirst_hashref_query($form, $self->dbh, $query);
889 $versionssatz .= length $ref->{accno};
890 $versionssatz .= ",";
891 $versionssatz .= length $ref->{accno};
892 $versionssatz .= ",SELF" . "\x1C\x79";
894 $header .= $versionssatz;
896 $main::lxdebug->leave_sub();
902 $main::lxdebug->enter_sub();
904 my ($date, $six) = @_;
906 my ($day, $month, $year) = split(/\./, $date);
908 if (length($month) < 2) {
909 $month = "0" . $month;
911 if (length($year) > 2) {
912 $year = substr($year, -2, 2);
916 $date = $day . $month . $year;
918 $date = $day . $month;
921 $main::lxdebug->leave_sub();
926 sub trim_leading_zeroes {
934 sub make_ed_versionset {
935 $main::lxdebug->enter_sub();
937 my ($self, $header, $filename, $blockcount) = @_;
939 my $versionset = "V" . substr($filename, 2, 5);
940 $versionset .= substr($header, 6, 22);
943 $versionset .= "0000" . substr($header, 28, 19);
945 my $datum = " " x 16;
946 $versionset .= $datum . "001" . substr($header, 28, 4);
949 $versionset .= _fill($blockcount, 5, '0');
950 $versionset .= "001";
952 $versionset .= substr($header, -12, 10) . " ";
953 $versionset .= " " x 53;
955 $main::lxdebug->leave_sub();
961 $main::lxdebug->enter_sub();
963 my ($self, $form, $fileno) = @_;
965 my $stamm = $self->get_datev_stamm;
967 my $ev_header = _fill($stamm->{datentraegernr}, 3, ' ', 'left');
969 $ev_header .= _fill($stamm->{beraternr}, 7, ' ', 'left');
970 $ev_header .= _fill($stamm->{beratername}, 9, ' ', 'left');
972 $ev_header .= (_fill($fileno, 5, '0')) x 2;
973 $ev_header .= " " x 95;
975 $main::lxdebug->leave_sub();
980 sub generate_datev_lines {
983 my @datev_lines = ();
985 foreach my $transaction ( @{ $self->{DATEV} } ) {
987 # each $transaction entry contains data from several acc_trans entries
988 # belonging to the same trans_id
990 my %datev_data = (); # data for one transaction
991 my $trans_lines = scalar(@{$transaction});
999 my $buchungstext = "";
1000 my $belegfeld2 = "";
1001 my $datevautomatik = 0;
1006 for (my $i = 0; $i < $trans_lines; $i++) {
1007 if ($trans_lines == 2) {
1008 if (abs($transaction->[$i]->{'amount'}) > abs($umsatz)) {
1009 $umsatz = $transaction->[$i]->{'amount'};
1012 if (abs($transaction->[$i]->{'umsatz'}) > abs($umsatz)) {
1013 $umsatz = $transaction->[$i]->{'umsatz'};
1016 if ($transaction->[$i]->{'datevautomatik'}) {
1017 $datevautomatik = 1;
1019 if ($transaction->[$i]->{'taxkey'}) {
1020 $taxkey = $transaction->[$i]->{'taxkey'};
1022 if ($transaction->[$i]->{'charttax'}) {
1023 $charttax = $transaction->[$i]->{'charttax'};
1025 if ($transaction->[$i]->{'amount'} > 0) {
1032 if ($trans_lines >= 2) {
1034 # Personenkontenerweiterung: accno has already been replaced if use_pk was set
1035 $datev_data{'gegenkonto'} = $transaction->[$haben]->{'accno'};
1036 $datev_data{'konto'} = $transaction->[$soll]->{'accno'};
1037 if ($transaction->[$haben]->{'invnumber'} ne "") {
1038 $datev_data{belegfeld1} = $transaction->[$haben]->{'invnumber'};
1040 $datev_data{datum} = $transaction->[$haben]->{'transdate'};
1041 $datev_data{waehrung} = 'EUR';
1042 $datev_data{kost1} = $transaction->[$haben]->{'departmentdescription'};
1043 $datev_data{kost2} = $transaction->[$haben]->{'projectdescription'};
1045 if ($transaction->[$haben]->{'name'} ne "") {
1046 $datev_data{buchungstext} = $transaction->[$haben]->{'name'};
1048 if (($transaction->[$haben]->{'ustid'} // '') ne "") {
1049 $datev_data{ustid} = SL::VATIDNr->normalize($transaction->[$haben]->{'ustid'});
1051 if (($transaction->[$haben]->{'duedate'} // '') ne "") {
1052 $datev_data{belegfeld2} = $transaction->[$haben]->{'duedate'};
1054 if (($transaction->[$haben]->{'deliverydate'} // '') ne "" &&
1055 $transaction->[$haben]->{'link'} !~ m/_paid/ &&
1056 $transaction->[$soll]->{'link'} !~ m/_paid/ ) {
1057 $datev_data{leistungsdatum} = $transaction->[$haben]->{'deliverydate'};
1060 $datev_data{umsatz} = abs($umsatz); # sales invoices without tax have a different sign???
1062 # Dies ist die einzige Stelle die datevautomatik auswertet. Was soll gesagt werden?
1063 # Im Prinzip hat jeder acc_trans Eintrag einen Steuerschlüssel, außer, bei gewissen Fällen
1064 # wie: Kreditorenbuchung mit negativen Vorzeichen, SEPA-Export oder Rechnungen die per
1065 # Skript angelegt werden.
1066 # Also falls ein Steuerschlüssel da ist und NICHT datevautomatik diesen Block hinzufügen.
1067 # Oder aber datevautomatik ist WAHR, aber der Steuerschlüssel in der acc_trans weicht
1068 # von dem in der Chart ab: Also wahrscheinlich Programmfehler (NULL übergeben, statt
1069 # DATEV-Steuerschlüssel) oder der Steuerschlüssel des Kontos weicht WIRKLICH von dem Eintrag in der
1070 # acc_trans ab. Gibt es für diesen Fall eine plausiblen Grund?
1073 # only set buchungsschluessel if the following conditions are met:
1074 if ( ( $datevautomatik || $taxkey)
1075 && (!$datevautomatik || ($datevautomatik && ($charttax ne $taxkey)))) {
1076 # $datev_data{buchungsschluessel} = !$datevautomatik ? $taxkey : "4";
1077 $datev_data{buchungsschluessel} = $taxkey;
1079 # set lock for each transaction
1080 $datev_data{locked} = $self->locked;
1082 push(@datev_lines, \%datev_data) if $datev_data{umsatz};
1085 # example of modifying export data:
1086 # foreach my $datev_line ( @datev_lines ) {
1087 # if ( $datev_line{"konto"} eq '1234' ) {
1088 # $datev_line{"konto"} = '9999';
1093 return \@datev_lines;
1097 sub kne_buchungsexport {
1098 $main::lxdebug->enter_sub();
1106 my $filename = "ED00001";
1107 my $evfile = "EV01";
1110 my $ed_filename = $self->export_path . $filename;
1112 my $fromto = $self->fromto;
1114 $self->generate_datev_data(from_to => $self->fromto); # fetches data from db, transforms data and fills $self->{DATEV}
1115 return if $self->errors;
1117 my @datev_lines = @{ $self->generate_datev_lines };
1120 my $umsatzsumme = sum map { $_->{umsatz} } @datev_lines;
1122 # prepare kne file, everything gets stored in ED00001
1123 my $header = $self->make_kne_data_header($form);
1124 my $kne_file = SL::DATEV::KNEFile->new();
1125 $kne_file->add_block($header);
1127 my $iconv = $::locale->{iconv_utf8};
1128 my %umlaute = ($iconv->convert('ä') => 'ae',
1129 $iconv->convert('ö') => 'oe',
1130 $iconv->convert('ü') => 'ue',
1131 $iconv->convert('Ä') => 'Ae',
1132 $iconv->convert('Ö') => 'Oe',
1133 $iconv->convert('Ü') => 'Ue',
1134 $iconv->convert('ß') => 'sz');
1136 # add the data from @datev_lines to the kne_file, formatting as needed
1137 foreach my $kne ( @datev_lines ) {
1138 $kne_file->add_block("+" . $kne_file->format_amount(abs($kne->{umsatz}), 0));
1140 # only add buchungsschluessel if it was previously defined
1141 $kne_file->add_block("\x6C" . $kne->{buchungsschluessel}) if defined $kne->{buchungsschluessel};
1143 # ($kne->{gegenkonto}) = $kne->{gegenkonto} =~ /^(\d+)/;
1144 $kne_file->add_block("a" . trim_leading_zeroes($kne->{gegenkonto}));
1146 if ( $kne->{belegfeld1} ) {
1147 my $invnumber = $kne->{belegfeld1};
1148 foreach my $umlaut (keys(%umlaute)) {
1149 $invnumber =~ s/${umlaut}/${umlaute{$umlaut}}/g;
1151 $invnumber =~ s/[^0-9A-Za-z\$\%\&\*\+\-\/]//g;
1152 $invnumber = substr($invnumber, 0, 12);
1153 $invnumber =~ s/\ *$//;
1154 $kne_file->add_block("\xBD" . $invnumber . "\x1C");
1157 $kne_file->add_block("\xBE" . &datetofour($kne->{belegfeld2},1) . "\x1C");
1159 $kne_file->add_block("d" . &datetofour($kne->{datum},0));
1161 # ($kne->{konto}) = $kne->{konto} =~ /^(\d+)/;
1162 $kne_file->add_block("e" . trim_leading_zeroes($kne->{konto}));
1164 my $name = $kne->{buchungstext};
1165 foreach my $umlaut (keys(%umlaute)) {
1166 $name =~ s/${umlaut}/${umlaute{$umlaut}}/g;
1168 $name =~ s/[^0-9A-Za-z\$\%\&\*\+\-\ \/]//g;
1169 $name = substr($name, 0, 30);
1171 $kne_file->add_block("\x1E" . $name . "\x1C");
1173 $kne_file->add_block("\xBA" . SL::VATIDNr->normalize($kne->{'ustid'}) . "\x1C") if $kne->{'ustid'};
1175 $kne_file->add_block("\xB3" . $kne->{'waehrung'} . "\x1C" . "\x79");
1178 $umsatzsumme = $kne_file->format_amount(abs($umsatzsumme), 0);
1179 my $mandantenendsumme = "x" . $kne_file->format_amount($umsatzsumme / 100.0, 14) . "\x79\x7a";
1181 $kne_file->add_block($mandantenendsumme);
1184 open(ED, ">", $ed_filename) or die "can't open outputfile: $!\n";
1185 print(ED $kne_file->get_data());
1188 $ed_versionset[$fileno] = $self->make_ed_versionset($header, $filename, $kne_file->get_block_count());
1190 #Make EV Verwaltungsdatei
1191 my $ev_header = $self->make_ev_header($form, $fileno);
1192 my $ev_filename = $self->export_path . $evfile;
1193 push(@filenames, $evfile);
1194 open(EV, ">", $ev_filename) or die "can't open outputfile: EV01\n";
1195 print(EV $ev_header);
1197 foreach my $file (@ed_versionset) {
1203 $self->add_filenames(@filenames);
1205 $main::lxdebug->leave_sub();
1207 return { 'download_token' => $self->download_token, 'filenames' => \@filenames };
1210 sub kne_stammdatenexport {
1211 $main::lxdebug->enter_sub();
1216 $self->get_datev_stamm->{abrechnungsnr} = "99";
1220 my $filename = "ED00000";
1221 my $evfile = "EV01";
1226 my $remaining_bytes = 256;
1227 my $total_bytes = 256;
1228 my $buchungssatz = "";
1230 my $ed_filename = $self->export_path . $filename;
1231 push(@filenames, $filename);
1232 open(ED, ">", $ed_filename) or die "can't open outputfile: $!\n";
1233 my $header = $self->make_kne_data_header($form);
1234 $remaining_bytes -= length($header);
1238 my (@where, @values) = ((), ());
1239 if ($self->accnofrom) {
1240 push @where, 'c.accno >= ?';
1241 push @values, $self->accnofrom;
1243 if ($self->accnoto) {
1244 push @where, 'c.accno <= ?';
1245 push @values, $self->accnoto;
1248 my $where_str = @where ? ' WHERE ' . join(' AND ', map { "($_)" } @where) : '';
1250 my $query = qq|SELECT c.accno, c.description
1255 my $sth = $self->dbh->prepare($query);
1256 $sth->execute(@values) || $form->dberror($query);
1258 while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
1259 if (($remaining_bytes - length("t" . $ref->{'accno'})) <= 6) {
1260 $fuellzeichen = ($blockcount * 256 - length($buchungssatz . $header));
1261 $buchungssatz .= "\x00" x $fuellzeichen;
1263 $total_bytes = ($blockcount) * 256;
1265 $buchungssatz .= "t" . $ref->{'accno'};
1266 $remaining_bytes = $total_bytes - length($buchungssatz . $header);
1267 $ref->{'description'} =~ s/[^0-9A-Za-z\$\%\&\*\+\-\/]//g;
1268 $ref->{'description'} = substr($ref->{'description'}, 0, 40);
1269 $ref->{'description'} =~ s/\ *$//;
1272 ($remaining_bytes - length("\x1E" . $ref->{'description'} . "\x1C\x79")
1275 $fuellzeichen = ($blockcount * 256 - length($buchungssatz . $header));
1276 $buchungssatz .= "\x00" x $fuellzeichen;
1278 $total_bytes = ($blockcount) * 256;
1280 $buchungssatz .= "\x1E" . $ref->{'description'} . "\x1C\x79";
1281 $remaining_bytes = $total_bytes - length($buchungssatz . $header);
1286 print(ED $buchungssatz);
1287 $fuellzeichen = 256 - (length($header . $buchungssatz . "z") % 256);
1288 my $dateiende = "\x00" x $fuellzeichen;
1290 print(ED $dateiende);
1293 #Make EV Verwaltungsdatei
1295 $self->make_ed_versionset($header, $filename, $blockcount);
1297 my $ev_header = $self->make_ev_header($form, $fileno);
1298 my $ev_filename = $self->export_path . $evfile;
1299 push(@filenames, $evfile);
1300 open(EV, ">", $ev_filename) or die "can't open outputfile: EV01\n";
1301 print(EV $ev_header);
1303 foreach my $file (@ed_versionset) {
1304 print(EV $ed_versionset[$file]);
1308 $self->add_filenames(@filenames);
1310 $main::lxdebug->leave_sub();
1312 return { 'download_token' => $self->download_token, 'filenames' => \@filenames };
1317 return $accno . ('0' x (6 - min(length($accno), 6)));
1320 sub csv_export_for_tax_accountant {
1323 $self->generate_datev_data(from_to => $self->fromto);
1325 foreach my $transaction (@{ $self->{DATEV} }) {
1326 foreach my $entry (@{ $transaction }) {
1327 $entry->{sortkey} = join '-', map { lc } (DateTime->from_kivitendo($entry->{transdate})->strftime('%Y%m%d'), $entry->{name}, $entry->{reference});
1332 partition_by { $_->[0]->{table} }
1333 sort_by { $_->[0]->{sortkey} }
1334 grep { 2 == scalar(@{ $_ }) }
1335 @{ $self->{DATEV} };
1338 acc_trans_id => { 'text' => $::locale->text('ID'), },
1339 amount => { 'text' => $::locale->text('Amount'), },
1340 credit_accname => { 'text' => $::locale->text('Credit Account Name'), },
1341 credit_accno => { 'text' => $::locale->text('Credit Account'), },
1342 debit_accname => { 'text' => $::locale->text('Debit Account Name'), },
1343 debit_accno => { 'text' => $::locale->text('Debit Account'), },
1344 invnumber => { 'text' => $::locale->text('Reference'), },
1345 name => { 'text' => $::locale->text('Name'), },
1346 notes => { 'text' => $::locale->text('Notes'), },
1347 tax => { 'text' => $::locale->text('Tax'), },
1348 taxkey => { 'text' => $::locale->text('Taxkey'), },
1349 tax_accname => { 'text' => $::locale->text('Tax Account Name'), },
1350 tax_accno => { 'text' => $::locale->text('Tax Account'), },
1351 transdate => { 'text' => $::locale->text('Transdate'), },
1352 vcnumber => { 'text' => $::locale->text('Customer/Vendor Number'), },
1356 acc_trans_id name vcnumber
1357 transdate invnumber amount
1358 debit_accno debit_accname
1359 credit_accno credit_accname
1361 tax_accno tax_accname taxkey
1365 my %filenames_by_type = (
1366 ar => $::locale->text('AR Transactions'),
1367 ap => $::locale->text('AP Transactions'),
1368 gl => $::locale->text('GL Transactions'),
1372 foreach my $type (qw(ap ar)) {
1376 filename => sprintf('%s %s - %s.csv', $filenames_by_type{$type}, $self->from->to_kivitendo, $self->to->to_kivitendo),
1377 csv => Text::CSV_XS->new({
1385 filename => sprintf('Zahlungen %s %s - %s.csv', $filenames_by_type{$type}, $self->from->to_kivitendo, $self->to->to_kivitendo),
1386 csv => Text::CSV_XS->new({
1394 foreach my $csv (values %csvs) {
1395 $csv->{out} = IO::File->new($self->export_path . '/' . $csv->{filename}, '>:encoding(utf8)') ;
1396 $csv->{csv}->print($csv->{out}, [ map { $column_defs{$_}->{text} } @columns ]);
1398 push @filenames, $csv->{filename};
1401 foreach my $transaction (@{ $transactions{$type} }) {
1402 my $is_payment = any { $_->{link} =~ m{A[PR]_paid} } @{ $transaction };
1403 my $csv = $is_payment ? $csvs{payments} : $csvs{invoices};
1405 my ($soll, $haben) = map { $transaction->[$_] } ($transaction->[0]->{amount} > 0 ? (1, 0) : (0, 1));
1406 my $tax = defined($soll->{tax_accno}) ? $soll : $haben;
1407 my $amount = defined($soll->{net_amount}) ? $soll : $haben;
1408 $haben->{notes} = ($haben->{memo} || $soll->{memo}) if $is_payment;
1409 $haben->{notes} //= '';
1410 $haben->{notes} = SL::HTML::Util->strip($haben->{notes});
1411 $haben->{notes} =~ s{\r}{}g;
1412 $haben->{notes} =~ s{\n+}{ }g;
1415 amount => $::form->format_amount({ numberformat => '1000,00' }, abs($amount->{amount}), 2),
1416 debit_accno => _format_accno($soll->{accno}),
1417 debit_accname => $soll->{accname},
1418 credit_accno => _format_accno($haben->{accno}),
1419 credit_accname => $haben->{accname},
1420 tax => $::form->format_amount({ numberformat => '1000,00' }, abs($amount->{amount}) - abs($amount->{net_amount}), 2),
1421 notes => $haben->{notes},
1422 (map { ($_ => $tax->{$_}) } qw(taxkey tax_accname tax_accno)),
1423 (map { ($_ => ($haben->{$_} // $soll->{$_})) } qw(acc_trans_id invnumber name vcnumber transdate)),
1426 $csv->{csv}->print($csv->{out}, [ map { $row{$_} } @columns ]);
1429 $_->{out}->close for values %csvs;
1432 $self->add_filenames(@filenames);
1434 return { download_token => $self->download_token, filenames => \@filenames };
1437 sub check_vcnumbers_are_valid_pk_numbers {
1440 # better use a class variable and set this in sub new (also needed in DATEV::CSV)
1441 # calculation is also a bit more sane in sub check_valid_length_of_accounts
1442 my $length_of_accounts = length(SL::DB::Manager::Chart->get_first(where => [charttype => 'A'])->accno) // 4;
1443 my $pk_length = $length_of_accounts + 1;
1444 my $query = <<"SQL";
1445 SELECT customernumber AS vcnumber FROM customer WHERE customernumber !~ '^[[:digit:]]{$pk_length}\$'
1447 SELECT vendornumber AS vcnumber FROM vendor WHERE vendornumber !~ '^[[:digit:]]{$pk_length}\$'
1450 my ($has_non_pk_accounts) = selectrow_query($::form, SL::DB->client->dbh, $query);
1451 return defined $has_non_pk_accounts ? 0 : 1;
1455 sub check_valid_length_of_accounts {
1458 my $query = <<"SQL";
1459 SELECT DISTINCT char_length (accno) FROM chart WHERE charttype='A' AND id in (select chart_id from acc_trans);
1462 my $accno_length = selectall_hashref_query($::form, SL::DB->client->dbh, $query);
1463 if (1 < scalar @$accno_length) {
1464 $::form->error(t8("Invalid combination of ledger account number length." .
1465 " Mismatch length of #1 with length of #2. Please check your account settings. ",
1466 $accno_length->[0]->{char_length}, $accno_length->[1]->{char_length}));
1472 clean_temporary_directories();
1483 SL::DATEV - kivitendo DATEV Export module
1487 use SL::DATEV qw(:CONSTANTS);
1489 my $startdate = DateTime->new(year => 2014, month => 9, day => 1);
1490 my $enddate = DateTime->new(year => 2014, month => 9, day => 31);
1491 my $datev = SL::DATEV->new(
1492 exporttype => DATEV_ET_BUCHUNGEN,
1493 format => DATEV_FORMAT_KNE,
1498 # To only export transactions from a specific trans_id: (from and to are ignored)
1499 my $invoice = SL::DB::Manager::Invoice->find_by( invnumber => '216' );
1500 my $datev = SL::DATEV->new(
1501 exporttype => DATEV_ET_BUCHUNGEN,
1502 format => DATEV_FORMAT_KNE,
1503 trans_id => $invoice->trans_id,
1506 my $datev = SL::DATEV->new(
1507 exporttype => DATEV_ET_STAMM,
1508 format => DATEV_FORMAT_KNE,
1509 accnofrom => $start_account_number,
1510 accnoto => $end_account_number,
1513 # get or set datev stamm
1514 my $hashref = $datev->get_datev_stamm;
1515 $datev->save_datev_stamm($hashref);
1517 # manually clean up temporary directories older than 8 hours
1518 $datev->clean_temporary_directories;
1523 if ($datev->errors) {
1524 die join "\n", $datev->error;
1527 # get relevant data for saving the export:
1528 my $dl_token = $datev->download_token;
1529 my $path = $datev->export_path;
1530 my @files = $datev->filenames;
1532 # retrieving an export at a later time
1533 my $datev = SL::DATEV->new(
1534 download_token => $dl_token_from_user,
1537 my $path = $datev->export_path;
1538 my @files = glob("$path/*");
1540 # Only test the datev data of a specific trans_id, without generating an
1541 # export file, but filling $datev->errors if errors exist
1543 my $datev = SL::DATEV->new(
1544 trans_id => $invoice->trans_id,
1546 $datev->generate_datev_data;
1547 # if ($datev->errors) { ...
1552 This module implements the DATEV export standard. For usage see above.
1560 Generic constructor. See section attributes for information about what to pass.
1562 =item generate_datev_data
1564 Fetches all transactions from the database (via a trans_id or a date range),
1565 and does an initial transformation (e.g. filters out tax, determines
1566 the brutto amount, checks split transactions ...) and stores this data in
1569 If any errors are found these are collected in $self->errors.
1571 This function is needed for all the exports, but can be also called
1572 independently in order to check transactions for DATEV compatibility.
1574 =item generate_datev_lines
1576 Parse the data in $self->{DATEV} and transform it into a format that can be
1577 used by DATEV, e.g. determines Konto and Gegenkonto, the taxkey, ...
1579 The transformed data is returned as an arrayref, which is ready to be converted
1580 to a DATEV data format, e.g. KNE, OBE, CSV, ...
1582 At this stage the "DATEV rule" has already been applied to the taxkeys, i.e.
1583 entries with datevautomatik have an empty taxkey, as the taxkey is already
1584 determined by the chart.
1586 =item get_datev_stamm
1588 Loads DATEV Stammdaten and returns as hashref.
1590 =item save_datev_stamm HASHREF
1592 Saves DATEV Stammdaten from provided hashref.
1596 See L<CONSTANTS> for possible values
1598 =item has_exporttype
1600 Returns true if an exporttype has been set. Without exporttype most report functions won't work.
1604 Specifies the designated format of the export. Currently only KNE export is implemented.
1606 See L<CONSTANTS> for possible values
1610 Returns true if a format has been set. Without format most report functions won't work.
1612 =item download_token
1614 Returns a download token 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 an export_path for this DATEV object.
1622 Note: If either a download_token or export_path were set at the creation these are infered, otherwise randomly generated.
1626 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.
1628 =item net_gross_differences
1630 If there were any net gross differences during calculation they will be collected here.
1632 =item sum_net_gross_differences
1634 Sum of all differences.
1636 =item clean_temporary_directories
1638 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.
1642 Returns a list of errors that occurred. If no errors occurred, the export was a success.
1646 Exports data. You have to have set L<exporttype> and L<format> or an error will
1647 occur. OBE exports are currently not implemented.
1649 =item csv_export_for_tax_accountant
1651 Generates up to four downloadable csv files containing data about sales and
1652 purchase invoices, and their respective payments:
1655 my $startdate = DateTime->new(year => 2012, month => 1, day => 1);
1656 my $enddate = DateTime->new(year => 2012, month => 12, day => 31);
1657 SL::DATEV->new(from => $startdate, to => $enddate)->csv_export_for_tax_accountant;
1659 # 'download_token' => '1488551625-815654-22430',
1661 # 'Zahlungen Kreditorenbuchungen 2012-01-01 - 2012-12-31.csv',
1662 # 'Kreditorenbuchungen 2012-01-01 - 2012-12-31.csv',
1663 # 'Zahlungen Debitorenbuchungen 2012-01-01 - 2012-12-31.csv',
1664 # 'Debitorenbuchungen 2012-01-01 - 2012-12-31.csv'
1669 =item check_vcnumbers_are_valid_pk_numbers
1671 Returns 1 if all vcnumbers are suitable for the DATEV export, 0 if not.
1673 Finds the default length of charts (e.g. 4), adds 1 for the pk chart length
1674 (e.g. 5), and checks the database for any customers or vendors whose customer-
1675 or vendornumber doesn't consist of only numbers with exactly that length. E.g.
1676 for a chart length of four "10001" would be ok, but not "10001b" or "1000".
1678 All vcnumbers are checked, obsolete customers or vendors aren't exempt.
1680 There is also no check for the typical customer range 10000-69999 and the
1681 typical vendor range 70000-99999.
1683 =item check_valid_length_of_accounts
1685 Returns 1 if all currently booked accounts have only one common number length domain (e.g. 4 or 6).
1686 Will throw an error if more than one distinct size is detected.
1687 The error message gives a short hint with the value of the (at least)
1688 two mismatching number length domains.
1694 This is a list of attributes set in either the C<new> or a method of the same name.
1700 Set a database handle to use in the process. This allows for an export to be
1701 done on a transaction in progress without committing first.
1703 Note: If you don't want this code to commit, simply providing a dbh is not
1704 enough enymore. You'll have to wrap the call into a transaction yourself, so
1705 that the internal transaction does not commit.
1709 See L<CONSTANTS> for possible values. This MUST be set before export is called.
1713 See L<CONSTANTS> for possible values. This MUST be set before export is called.
1715 =item download_token
1717 Can be set on creation to retrieve a prior export for download.
1723 Set boundary dates for the export. Unless a trans_id is passed these MUST be
1724 set for the export to work.
1728 To check only one gl/ar/ap transaction, pass the trans_id. The attributes
1729 L<from> and L<to> are currently still needed for the query to be assembled
1736 Set boundary account numbers for the export. Only useful for a stammdaten export.
1740 Boolean if the transactions are locked (read-only in kivitenod) or not.
1741 Default value is false
1747 =head2 Supplied to L<exporttype>
1751 =item DATEV_ET_BUCHUNGEN
1753 =item DATEV_ET_STAMM
1757 =head2 Supplied to L<format>.
1761 =item DATEV_FORMAT_KNE
1763 =item DATEV_FORMAT_OBE
1767 =head1 ERROR HANDLING
1769 This module will die in the following cases:
1775 No or unrecognized exporttype or format was provided for an export
1779 OBE export was called, which is not yet implemented.
1787 Errors that occur during th actual export will be collected in L<errors>. The following types can occur at the moment:
1793 C<Unbalanced Ledger!>. Exactly that, your ledger is unbalanced. Should never occur.
1797 C<Datev-Export fehlgeschlagen! Bei Transaktion %d (%f).> This error occurs if a
1798 transaction could not be reliably sorted out, or had rounding errors above the acceptable threshold.
1802 =head1 BUGS AND CAVEATS
1808 Handling of Vollvorlauf is currently not fully implemented. You must provide both from and to in order to get a working export.
1812 OBE export is currently not implemented.
1818 - handling of export_path and download token is a bit dodgy, clean that up.
1822 L<SL::DATEV::KNEFile>
1827 Philip Reetz E<lt>p.reetz@linet-services.deE<gt>,
1829 Moritz Bunkus E<lt>m.bunkus@linet-services.deE<gt>,
1831 Jan Büren E<lt>jan@lx-office-hosting.deE<gt>,
1833 Geoffrey Richardson E<lt>information@lx-office-hosting.deE<gt>,
1835 Sven Schöling E<lt>s.schoeling@linet-services.deE<gt>,