Mobile: lokale css/fonts/js Resourcen.

[mfinanz.git] / SL / DATEV.pm

diff --git a/SL/DATEV.pm b/SL/DATEV.pm
index c5e081a80c570cf0c132b6de4da01cfe73f19993..1db417c9a42c03437695fbfeb85e745d57dcd1bd 100644 (file)
--- a/SL/DATEV.pm
+++ b/SL/DATEV.pm
@@ -31,10 +31,12 @@ use utf8;
 use strict;
 
 use SL::DBUtils;
 use strict;
 
 use SL::DBUtils;

-use SL::DATEV::KNEFile;
+use SL::DATEV::CSV;

 use SL::DB;
 use SL::HTML::Util ();
 use SL::DB;
 use SL::HTML::Util ();

+use SL::Iconv;

 use SL::Locale::String qw(t8);
 use SL::Locale::String qw(t8);

+use SL::VATIDNr;

 
 use Data::Dumper;
 use DateTime;
 
 use Data::Dumper;
 use DateTime;


@@ -214,6 +216,26 @@ sub trans_id {
   return $self->{trans_id};
 }
 
   return $self->{trans_id};
 }
 

+sub warnings {
+  my $self = shift;
+
+  if (@_) {
+    $self->{warnings} = [@_];
+  } else {
+   return $self->{warnings};
+  }
+}
+
+sub use_pk {
+ my $self = shift;
+
+ if (@_) {
+   $self->{use_pk} = $_[0];
+ }
+
+ return $self->{use_pk};
+}
+

 sub accnofrom {
  my $self = shift;
 
 sub accnofrom {
  my $self = shift;
 


@@ -265,29 +287,6 @@ sub clean_temporary_directories {
   $::lxdebug->leave_sub;
 }
 
   $::lxdebug->leave_sub;
 }
 

-sub _fill {
-  $main::lxdebug->enter_sub();
-
-  my $text      = shift // '';
-  my $field_len = shift;
-  my $fill_char = shift;
-  my $alignment = shift || 'right';
-
-  my $text_len  = length $text;
-
-  if ($field_len < $text_len) {
-    $text = substr $text, 0, $field_len;
-
-  } elsif ($field_len > $text_len) {
-    my $filler = ($fill_char) x ($field_len - $text_len);
-    $text      = $alignment eq 'right' ? $filler . $text : $text . $filler;
-  }
-
-  $main::lxdebug->leave_sub();
-
-  return $text;
-}
-

 sub get_datev_stamm {
   return $_[0]{stamm} ||= selectfirst_hashref_query($::form, $_[0]->dbh, 'SELECT * FROM datev');
 }
 sub get_datev_stamm {
   return $_[0]{stamm} ||= selectfirst_hashref_query($::form, $_[0]->dbh, 'SELECT * FROM datev');
 }


@@ -308,35 +307,66 @@ sub save_datev_stamm {
 
 sub export {
   my ($self) = @_;
 
 sub export {
   my ($self) = @_;

-  my $result;
-
-  die 'no format set!' unless $self->has_format;

 
 

-  if ($self->format == DATEV_FORMAT_CSV) {
-    $result = $self->csv_export;
-  } elsif ($self->format == DATEV_FORMAT_KNE) {
-    $result = $self->kne_export;
-  } elsif ($self->format == DATEV_FORMAT_OBE) {
-    $result = $self->obe_export;
-  } else {
-    die 'unrecognized export format';
-  }
-
-  return $result;
+  return $self->csv_export;

 }
 
 }
 

-sub kne_export {
+sub csv_export {

   my ($self) = @_;
   my $result;
 
   die 'no exporttype set!' unless $self->has_exporttype;
 
   if ($self->exporttype == DATEV_ET_BUCHUNGEN) {
   my ($self) = @_;
   my $result;
 
   die 'no exporttype set!' unless $self->has_exporttype;
 
   if ($self->exporttype == DATEV_ET_BUCHUNGEN) {

-    $result = $self->kne_buchungsexport;
-  } elsif ($self->exporttype == DATEV_ET_STAMM) {
-    $result = $self->kne_stammdatenexport;
-  } elsif ($self->exporttype == DATEV_ET_CSV) {
-    $result = $self->csv_export_for_tax_accountant;
+
+    $self->generate_datev_data(from_to => $self->fromto);
+    return if $self->errors;
+
+    my $datev_csv = SL::DATEV::CSV->new(
+      datev_lines  => $self->generate_datev_lines,
+      from         => $self->from,
+      to           => $self->to,
+      locked       => $self->locked,
+    );
+
+
+    my $filename = "EXTF_DATEV_kivitendo" . $self->from->ymd() . '-' . $self->to->ymd() . ".csv";
+
+    my $csv = Text::CSV_XS->new({
+                binary       => 1,
+                sep_char     => ";",
+                always_quote => 1,
+                eol          => "\r\n",
+              }) or die "Cannot use CSV: ".Text::CSV_XS->error_diag();
+
+    # get encoding from defaults - use cp1252 if DATEV strict export is used
+    my $enc = ($::instance_conf->get_datev_export_format eq 'cp1252') ? 'cp1252' : 'utf-8';
+    my $csv_file = IO::File->new($self->export_path . '/' . $filename, ">:encoding($enc)") or die "Can't open: $!";
+
+    $csv->print($csv_file, $_) for @{ $datev_csv->header };
+    $csv->print($csv_file, $_) for @{ $datev_csv->lines  };
+    $csv_file->close;
+    $self->{warnings} = $datev_csv->warnings;
+
+    # convert utf-8 to cp1252//translit if set
+    if ($::instance_conf->get_datev_export_format eq 'cp1252-translit') {
+
+      my $filename_translit = "EXTF_DATEV_kivitendo_translit" . $self->from->ymd() . '-' . $self->to->ymd() . ".csv";
+      open my $fh_in,  '<:encoding(UTF-8)',  $self->export_path . '/' . $filename or die "could not open $filename for reading: $!";
+      open my $fh_out, '>', $self->export_path . '/' . $filename_translit         or die "could not open $filename_translit for writing: $!";
+
+      my $converter = SL::Iconv->new("utf-8", "cp1252//translit");
+
+      print $fh_out $converter->convert($_) while <$fh_in>;
+      close $fh_in;
+      close $fh_out;
+
+      unlink $self->export_path . '/' . $filename or warn "Could not unlink $filename: $!";
+      $filename = $filename_translit;
+    }
+
+    return { download_token => $self->download_token, filenames => $filename };
+

   } else {
     die 'unrecognized exporttype';
   }
   } else {
     die 'unrecognized exporttype';
   }


@@ -344,14 +374,6 @@ sub kne_export {
   return $result;
 }
 
   return $result;
 }
 

-sub csv_export {
-  die 'not yet implemented';
-}
-
-sub obe_export {
-  die 'not yet implemented';
-}
-

 sub fromto {
   my ($self) = @_;
 
 sub fromto {
   my ($self) = @_;
 


@@ -364,6 +386,23 @@ sub _sign {
   $_[0] <=> 0;
 }
 
   $_[0] <=> 0;
 }
 

+sub locked {
+ my $self = shift;
+
+ if (@_) {
+   $self->{locked} = $_[0];
+ }
+ return $self->{locked};
+}
+sub imported {
+ my $self = shift;
+
+ if (@_) {
+   $self->{imported} = $_[0];
+ }
+ return $self->{imported};
+}
+

 sub generate_datev_data {
   $main::lxdebug->enter_sub();
 
 sub generate_datev_data {
   $main::lxdebug->enter_sub();
 


@@ -410,23 +449,38 @@ sub generate_datev_data {
 
   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');
 
 
   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');
 

+  my $ar_accno = "c.accno";
+  my $ap_accno = "c.accno";
+  if ( $self->use_pk ) {
+    $ar_accno = "CASE WHEN ac.chart_link = 'AR' THEN ct.customernumber ELSE c.accno END as accno";
+    $ap_accno = "CASE WHEN ac.chart_link = 'AP' THEN ct.vendornumber   ELSE c.accno END as accno";
+  }
+  my $gl_imported;
+  if ( !$self->imported ) {
+    $gl_imported = " AND NOT imported";
+  }
+

   my $query    =
     qq|SELECT ac.acc_trans_id, ac.transdate, ac.gldate, ac.trans_id,ar.id, ac.amount, ac.taxkey, ac.memo,
   my $query    =
     qq|SELECT ac.acc_trans_id, ac.transdate, ac.gldate, ac.trans_id,ar.id, ac.amount, ac.taxkey, ac.memo,

-         ar.invnumber, ar.duedate, ar.amount as umsatz, ar.deliverydate, ar.itime::date,
+         ar.invnumber, ar.duedate, ar.amount as umsatz, COALESCE(ar.tax_point, ar.deliverydate) AS deliverydate, ar.itime::date,

          ct.name, ct.ustid, ct.customernumber AS vcnumber, ct.id AS customer_id, NULL AS vendor_id,
          ct.name, ct.ustid, ct.customernumber AS vcnumber, ct.id AS customer_id, NULL AS vendor_id,

-         c.accno, c.description AS accname, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
+         $ar_accno, c.description AS accname, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,

          ar.invoice,
          t.rate AS taxrate, t.taxdescription,
          'ar' as table,
          tc.accno AS tax_accno, tc.description AS tax_accname,
          ar.department_id,
          ar.invoice,
          t.rate AS taxrate, t.taxdescription,
          'ar' as table,
          tc.accno AS tax_accno, tc.description AS tax_accname,
          ar.department_id,

-         ar.notes
+         ar.notes,
+         project.projectnumber as projectnumber, project.description as projectdescription,
+         department.description as departmentdescription

        FROM acc_trans ac
        LEFT JOIN ar          ON (ac.trans_id    = ar.id)
        LEFT JOIN customer ct ON (ar.customer_id = ct.id)
        LEFT JOIN chart c     ON (ac.chart_id    = c.id)
        LEFT JOIN tax t       ON (ac.tax_id      = t.id)
        LEFT JOIN chart tc    ON (t.chart_id     = tc.id)
        FROM acc_trans ac
        LEFT JOIN ar          ON (ac.trans_id    = ar.id)
        LEFT JOIN customer ct ON (ar.customer_id = ct.id)
        LEFT JOIN chart c     ON (ac.chart_id    = c.id)
        LEFT JOIN tax t       ON (ac.tax_id      = t.id)
        LEFT JOIN chart tc    ON (t.chart_id     = tc.id)

+       LEFT JOIN department  ON (department.id  = ar.department_id)
+       LEFT JOIN project     ON (project.id     = ar.globalproject_id)

        WHERE (ar.id IS NOT NULL)
          AND $fromto
          $trans_id_filter
        WHERE (ar.id IS NOT NULL)
          AND $fromto
          $trans_id_filter


@@ -437,21 +491,25 @@ sub generate_datev_data {
        UNION ALL
 
        SELECT ac.acc_trans_id, ac.transdate, ac.gldate, ac.trans_id,ap.id, ac.amount, ac.taxkey, ac.memo,
        UNION ALL
 
        SELECT ac.acc_trans_id, ac.transdate, ac.gldate, ac.trans_id,ap.id, ac.amount, ac.taxkey, ac.memo,

-         ap.invnumber, ap.duedate, ap.amount as umsatz, ap.deliverydate, ap.itime::date,
+         ap.invnumber, ap.duedate, ap.amount as umsatz, COALESCE(ap.tax_point, ap.deliverydate) AS deliverydate, ap.itime::date,

          ct.name, ct.ustid, ct.vendornumber AS vcnumber, NULL AS customer_id, ct.id AS vendor_id,
          ct.name, ct.ustid, ct.vendornumber AS vcnumber, NULL AS customer_id, ct.id AS vendor_id,

-         c.accno, c.description AS accname, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
+         $ap_accno, c.description AS accname, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,

          ap.invoice,
          t.rate AS taxrate, t.taxdescription,
          'ap' as table,
          tc.accno AS tax_accno, tc.description AS tax_accname,
          ap.department_id,
          ap.invoice,
          t.rate AS taxrate, t.taxdescription,
          'ap' as table,
          tc.accno AS tax_accno, tc.description AS tax_accname,
          ap.department_id,

-         ap.notes
+         ap.notes,
+         project.projectnumber as projectnumber, project.description as projectdescription,
+         department.description as departmentdescription

        FROM acc_trans ac
        LEFT JOIN ap        ON (ac.trans_id  = ap.id)
        LEFT JOIN vendor ct ON (ap.vendor_id = ct.id)
        LEFT JOIN chart c   ON (ac.chart_id  = c.id)
        LEFT JOIN tax t     ON (ac.tax_id    = t.id)
        LEFT JOIN chart tc    ON (t.chart_id     = tc.id)
        FROM acc_trans ac
        LEFT JOIN ap        ON (ac.trans_id  = ap.id)
        LEFT JOIN vendor ct ON (ap.vendor_id = ct.id)
        LEFT JOIN chart c   ON (ac.chart_id  = c.id)
        LEFT JOIN tax t     ON (ac.tax_id    = t.id)
        LEFT JOIN chart tc    ON (t.chart_id     = tc.id)

+       LEFT JOIN department  ON (department.id  = ap.department_id)
+       LEFT JOIN project     ON (project.id     = ap.globalproject_id)

        WHERE (ap.id IS NOT NULL)
          AND $fromto
          $trans_id_filter
        WHERE (ap.id IS NOT NULL)
          AND $fromto
          $trans_id_filter


@@ -462,7 +520,7 @@ sub generate_datev_data {
        UNION ALL
 
        SELECT ac.acc_trans_id, ac.transdate, ac.gldate, ac.trans_id,gl.id, ac.amount, ac.taxkey, ac.memo,
        UNION ALL
 
        SELECT ac.acc_trans_id, ac.transdate, ac.gldate, ac.trans_id,gl.id, ac.amount, ac.taxkey, ac.memo,

-         gl.reference AS invnumber, gl.transdate AS duedate, ac.amount as umsatz, NULL as deliverydate, gl.itime::date,
+         gl.reference AS invnumber, NULL AS duedate, ac.amount as umsatz, COALESCE(gl.tax_point, gl.deliverydate) AS deliverydate, gl.itime::date,

          gl.description AS name, NULL as ustid, '' AS vcname, NULL AS customer_id, NULL AS vendor_id,
          c.accno, c.description AS accname, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
          FALSE AS invoice,
          gl.description AS name, NULL as ustid, '' AS vcname, NULL AS customer_id, NULL AS vendor_id,
          c.accno, c.description AS accname, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
          FALSE AS invoice,


@@ -470,17 +528,21 @@ sub generate_datev_data {
          'gl' as table,
          tc.accno AS tax_accno, tc.description AS tax_accname,
          gl.department_id,
          'gl' as table,
          tc.accno AS tax_accno, tc.description AS tax_accname,
          gl.department_id,

-         gl.notes
+         gl.notes,
+         '' as projectnumber, '' as projectdescription,
+         department.description as departmentdescription

        FROM acc_trans ac
        LEFT JOIN gl      ON (ac.trans_id  = gl.id)
        LEFT JOIN chart c ON (ac.chart_id  = c.id)
        LEFT JOIN tax t   ON (ac.tax_id    = t.id)
        LEFT JOIN chart tc    ON (t.chart_id     = tc.id)
        FROM acc_trans ac
        LEFT JOIN gl      ON (ac.trans_id  = gl.id)
        LEFT JOIN chart c ON (ac.chart_id  = c.id)
        LEFT JOIN tax t   ON (ac.tax_id    = t.id)
        LEFT JOIN chart tc    ON (t.chart_id     = tc.id)

+       LEFT JOIN department  ON (department.id  = gl.department_id)

        WHERE (gl.id IS NOT NULL)
          AND $fromto
          $trans_id_filter
          $gl_itime_filter
          $gl_department_id_filter
        WHERE (gl.id IS NOT NULL)
          AND $fromto
          $trans_id_filter
          $gl_itime_filter
          $gl_department_id_filter

+         $gl_imported

          $filter
 
        ORDER BY trans_id, acc_trans_id|;
          $filter
 
        ORDER BY trans_id, acc_trans_id|;


@@ -732,138 +794,6 @@ sub generate_datev_data {
   $::lxdebug->leave_sub;
 }
 
   $::lxdebug->leave_sub;
 }
 

-sub make_kne_data_header {
-  $main::lxdebug->enter_sub();
-
-  my ($self, $form) = @_;
-  my ($primanota);
-
-  my $stamm = $self->get_datev_stamm;
-
-  my $jahr = $self->from ? $self->from->year : DateTime->today->year;
-
-  #Header
-  my $header  = "\x1D\x181";
-  $header    .= _fill($stamm->{datentraegernr}, 3, ' ', 'left');
-  $header    .= ($self->fromto) ? "11" : "13"; # Anwendungsnummer
-  $header    .= _fill($stamm->{dfvkz}, 2, '0');
-  $header    .= _fill($stamm->{beraternr}, 7, '0');
-  $header    .= _fill($stamm->{mandantennr}, 5, '0');
-  $header    .= _fill(($stamm->{abrechnungsnr} // '') . $jahr, 6, '0');
-
-  $header .= $self->from ? $self->from->strftime('%d%m%y') : '';
-  $header .= $self->to   ? $self->to->strftime('%d%m%y')   : '';
-
-  if ($self->fromto) {
-    $primanota = "001";
-    $header .= $primanota;
-  }
-
-  $header .= _fill($stamm->{passwort}, 4, '0');
-  $header .= " " x 16;       # Anwendungsinfo
-  $header .= " " x 16;       # Inputinfo
-  $header .= "\x79";
-
-  #Versionssatz
-  my $versionssatz  = $self->exporttype == DATEV_ET_BUCHUNGEN ? "\xB5" . "1," : "\xB6" . "1,";
-
-  my $query         = qq|SELECT accno FROM chart LIMIT 1|;
-  my $ref           = selectfirst_hashref_query($form, $self->dbh, $query);
-
-  $versionssatz    .= length $ref->{accno};
-  $versionssatz    .= ",";
-  $versionssatz    .= length $ref->{accno};
-  $versionssatz    .= ",SELF" . "\x1C\x79";
-
-  $header          .= $versionssatz;
-
-  $main::lxdebug->leave_sub();
-
-  return $header;
-}
-
-sub datetofour {
-  $main::lxdebug->enter_sub();
-
-  my ($date, $six) = @_;
-
-  my ($day, $month, $year) = split(/\./, $date);
-
-  if ($day =~ /^0/) {
-    $day = substr($day, 1, 1);
-  }
-  if (length($month) < 2) {
-    $month = "0" . $month;
-  }
-  if (length($year) > 2) {
-    $year = substr($year, -2, 2);
-  }
-
-  if ($six) {
-    $date = $day . $month . $year;
-  } else {
-    $date = $day . $month;
-  }
-
-  $main::lxdebug->leave_sub();
-
-  return $date;
-}
-
-sub trim_leading_zeroes {
-  my $str = shift;
-
-  $str =~ s/^0+//g;
-
-  return $str;
-}
-
-sub make_ed_versionset {
-  $main::lxdebug->enter_sub();
-
-  my ($self, $header, $filename, $blockcount) = @_;
-
-  my $versionset  = "V" . substr($filename, 2, 5);
-  $versionset    .= substr($header, 6, 22);
-
-  if ($self->fromto) {
-    $versionset .= "0000" . substr($header, 28, 19);
-  } else {
-    my $datum = " " x 16;
-    $versionset .= $datum . "001" . substr($header, 28, 4);
-  }
-
-  $versionset .= _fill($blockcount, 5, '0');
-  $versionset .= "001";
-  $versionset .= " 1";
-  $versionset .= substr($header, -12, 10) . "    ";
-  $versionset .= " " x 53;
-
-  $main::lxdebug->leave_sub();
-
-  return $versionset;
-}
-
-sub make_ev_header {
-  $main::lxdebug->enter_sub();
-
-  my ($self, $form, $fileno) = @_;
-
-  my $stamm = $self->get_datev_stamm;
-
-  my $ev_header  = _fill($stamm->{datentraegernr}, 3, ' ', 'left');
-  $ev_header    .= "   ";
-  $ev_header    .= _fill($stamm->{beraternr}, 7, ' ', 'left');
-  $ev_header    .= _fill($stamm->{beratername}, 9, ' ', 'left');
-  $ev_header    .= " ";
-  $ev_header    .= (_fill($fileno, 5, '0')) x 2;
-  $ev_header    .= " " x 95;
-
-  $main::lxdebug->leave_sub();
-
-  return $ev_header;
-}
-

 sub generate_datev_lines {
   my ($self) = @_;
 
 sub generate_datev_lines {
   my ($self) = @_;
 


@@ -918,6 +848,7 @@ sub generate_datev_lines {
 
     if ($trans_lines >= 2) {
 
 
     if ($trans_lines >= 2) {
 

+      # Personenkontenerweiterung: accno has already been replaced if use_pk was set

       $datev_data{'gegenkonto'} = $transaction->[$haben]->{'accno'};
       $datev_data{'konto'}      = $transaction->[$soll]->{'accno'};
       if ($transaction->[$haben]->{'invnumber'} ne "") {
       $datev_data{'gegenkonto'} = $transaction->[$haben]->{'accno'};
       $datev_data{'konto'}      = $transaction->[$soll]->{'accno'};
       if ($transaction->[$haben]->{'invnumber'} ne "") {


@@ -925,18 +856,34 @@ sub generate_datev_lines {
       }
       $datev_data{datum} = $transaction->[$haben]->{'transdate'};
       $datev_data{waehrung} = 'EUR';
       }
       $datev_data{datum} = $transaction->[$haben]->{'transdate'};
       $datev_data{waehrung} = 'EUR';

+      $datev_data{kost1} = $transaction->[$haben]->{'departmentdescription'};
+      $datev_data{kost2} = $transaction->[$haben]->{'projectdescription'};

 
       if ($transaction->[$haben]->{'name'} ne "") {
         $datev_data{buchungstext} = $transaction->[$haben]->{'name'};
       }
       if (($transaction->[$haben]->{'ustid'} // '') ne "") {
 
       if ($transaction->[$haben]->{'name'} ne "") {
         $datev_data{buchungstext} = $transaction->[$haben]->{'name'};
       }
       if (($transaction->[$haben]->{'ustid'} // '') ne "") {

-        $datev_data{ustid} = $transaction->[$haben]->{'ustid'};
+        $datev_data{ustid} = SL::VATIDNr->normalize($transaction->[$haben]->{'ustid'});

       }
       if (($transaction->[$haben]->{'duedate'} // '') ne "") {
         $datev_data{belegfeld2} = $transaction->[$haben]->{'duedate'};
       }
       }
       if (($transaction->[$haben]->{'duedate'} // '') ne "") {
         $datev_data{belegfeld2} = $transaction->[$haben]->{'duedate'};
       }

-    }

 
 

+      # if deliverydate exists, add it to datev export if it is
+      # * an ar/ap booking that is not a payment
+      # * a gl booking
+      if (    ($transaction->[$haben]->{'deliverydate'} // '') ne ''
+           && (
+                (    $transaction->[$haben]->{'table'} =~ /^(ar|ap)$/
+                  && $transaction->[$haben]->{'link'}  !~ m/_paid/
+                  && $transaction->[$soll]->{'link'}   !~ m/_paid/
+                )
+                || $transaction->[$haben]->{'table'} eq 'gl'
+              )
+         ) {
+        $datev_data{leistungsdatum} = $transaction->[$haben]->{'deliverydate'};
+      }
+    }

     $datev_data{umsatz} = abs($umsatz); # sales invoices without tax have a different sign???
 
     # Dies ist die einzige Stelle die datevautomatik auswertet. Was soll gesagt werden?
     $datev_data{umsatz} = abs($umsatz); # sales invoices without tax have a different sign???
 
     # Dies ist die einzige Stelle die datevautomatik auswertet. Was soll gesagt werden?


@@ -956,8 +903,10 @@ sub generate_datev_lines {
       # $datev_data{buchungsschluessel} = !$datevautomatik ? $taxkey : "4";
       $datev_data{buchungsschluessel} = $taxkey;
     }
       # $datev_data{buchungsschluessel} = !$datevautomatik ? $taxkey : "4";
       $datev_data{buchungsschluessel} = $taxkey;
     }

+    # set lock for each transaction
+    $datev_data{locked} = $self->locked;

 
 

-    push(@datev_lines, \%datev_data);
+    push(@datev_lines, \%datev_data) if $datev_data{umsatz};

   }
 
   # example of modifying export data:
   }
 
   # example of modifying export data:


@@ -971,345 +920,38 @@ sub generate_datev_lines {
   return \@datev_lines;
 }
 
   return \@datev_lines;
 }
 

-
-sub kne_buchungsexport {
-  $main::lxdebug->enter_sub();
-
+sub check_vcnumbers_are_valid_pk_numbers {

   my ($self) = @_;
 
   my ($self) = @_;
 

-  my $form = $::form;
-
-  my @filenames;
-
-  my $filename    = "ED00001";
-  my $evfile      = "EV01";
-  my @ed_versionset;
-  my $fileno      = 1;
-  my $ed_filename = $self->export_path . $filename;
-
-  my $fromto = $self->fromto;
-
-  $self->generate_datev_data(from_to => $self->fromto); # fetches data from db, transforms data and fills $self->{DATEV}
-  return if $self->errors;
-
-  my @datev_lines = @{ $self->generate_datev_lines };
-
-
-  my $umsatzsumme = sum map { $_->{umsatz} } @datev_lines;
-
-  # prepare kne file, everything gets stored in ED00001
-  my $header = $self->make_kne_data_header($form);
-  my $kne_file = SL::DATEV::KNEFile->new();
-  $kne_file->add_block($header);
-
-  my $iconv   = $::locale->{iconv_utf8};
-  my %umlaute = ($iconv->convert('ä') => 'ae',
-                 $iconv->convert('ö') => 'oe',
-                 $iconv->convert('ü') => 'ue',
-                 $iconv->convert('Ä') => 'Ae',
-                 $iconv->convert('Ö') => 'Oe',
-                 $iconv->convert('Ü') => 'Ue',
-                 $iconv->convert('ß') => 'sz');
-
-  # add the data from @datev_lines to the kne_file, formatting as needed
-  foreach my $kne ( @datev_lines ) {
-    $kne_file->add_block("+" . $kne_file->format_amount(abs($kne->{umsatz}), 0));
-
-    # only add buchungsschluessel if it was previously defined
-    $kne_file->add_block("\x6C" . $kne->{buchungsschluessel}) if defined $kne->{buchungsschluessel};
-
-    # ($kne->{gegenkonto}) = $kne->{gegenkonto} =~ /^(\d+)/;
-    $kne_file->add_block("a" . trim_leading_zeroes($kne->{gegenkonto}));
-
-    if ( $kne->{belegfeld1} ) {
-      my $invnumber = $kne->{belegfeld1};
-      foreach my $umlaut (keys(%umlaute)) {
-        $invnumber =~ s/${umlaut}/${umlaute{$umlaut}}/g;
-      }
-      $invnumber =~ s/[^0-9A-Za-z\$\%\&\*\+\-\/]//g;
-      $invnumber =  substr($invnumber, 0, 12);
-      $invnumber =~ s/\ *$//;
-      $kne_file->add_block("\xBD" . $invnumber . "\x1C");
-    }
-
-    $kne_file->add_block("\xBE" . &datetofour($kne->{belegfeld2},1) . "\x1C");
-
-    $kne_file->add_block("d" . &datetofour($kne->{datum},0));
-
-    # ($kne->{konto}) = $kne->{konto} =~ /^(\d+)/;
-    $kne_file->add_block("e" . trim_leading_zeroes($kne->{konto}));
-
-    my $name = $kne->{buchungstext};
-    foreach my $umlaut (keys(%umlaute)) {
-      $name =~ s/${umlaut}/${umlaute{$umlaut}}/g;
-    }
-    $name =~ s/[^0-9A-Za-z\$\%\&\*\+\-\ \/]//g;
-    $name =  substr($name, 0, 30);
-    $name =~ s/\ *$//;
-    $kne_file->add_block("\x1E" . $name . "\x1C");
-
-    $kne_file->add_block("\xBA" . $kne->{'ustid'}    . "\x1C") if $kne->{'ustid'};
-
-    $kne_file->add_block("\xB3" . $kne->{'waehrung'} . "\x1C" . "\x79");
-  };
-
-  $umsatzsumme          = $kne_file->format_amount(abs($umsatzsumme), 0);
-  my $mandantenendsumme = "x" . $kne_file->format_amount($umsatzsumme / 100.0, 14) . "\x79\x7a";
-
-  $kne_file->add_block($mandantenendsumme);
-  $kne_file->flush();
-
-  open(ED, ">", $ed_filename) or die "can't open outputfile: $!\n";
-  print(ED $kne_file->get_data());
-  close(ED);
-
-  $ed_versionset[$fileno] = $self->make_ed_versionset($header, $filename, $kne_file->get_block_count());
-
-  #Make EV Verwaltungsdatei
-  my $ev_header   = $self->make_ev_header($form, $fileno);
-  my $ev_filename = $self->export_path . $evfile;
-  push(@filenames, $evfile);
-  open(EV, ">", $ev_filename) or die "can't open outputfile: EV01\n";
-  print(EV $ev_header);
-
-  foreach my $file (@ed_versionset) {
-    print(EV $file);
-  }
-  close(EV);
-  ###
-
-  $self->add_filenames(@filenames);
-
-  $main::lxdebug->leave_sub();
-
-  return { 'download_token' => $self->download_token, 'filenames' => \@filenames };
+  # better use a class variable and set this in sub new (also needed in DATEV::CSV)
+  # calculation is also a bit more sane in sub check_valid_length_of_accounts
+  my $length_of_accounts = length(SL::DB::Manager::Chart->get_first(where => [charttype => 'A'])->accno) // 4;
+  my $pk_length = $length_of_accounts + 1;
+  my $query = <<"SQL";
+   SELECT customernumber AS vcnumber FROM customer WHERE customernumber !~ '^[[:digit:]]{$pk_length}\$'
+   UNION
+   SELECT vendornumber   AS vcnumber FROM vendor   WHERE vendornumber   !~ '^[[:digit:]]{$pk_length}\$'
+   LIMIT 1;
+SQL
+  my ($has_non_pk_accounts)  = selectrow_query($::form, SL::DB->client->dbh, $query);
+  return defined $has_non_pk_accounts ? 0 : 1;

 }
 
 }
 

-sub kne_stammdatenexport {
-  $main::lxdebug->enter_sub();

 
 

+sub check_valid_length_of_accounts {

   my ($self) = @_;
   my ($self) = @_;

-  my $form = $::form;
-
-  $self->get_datev_stamm->{abrechnungsnr} = "99";
-
-  my @filenames;
-
-  my $filename    = "ED00000";
-  my $evfile      = "EV01";
-  my @ed_versionset;
-  my $fileno          = 1;
-  my $i               = 0;
-  my $blockcount      = 1;
-  my $remaining_bytes = 256;
-  my $total_bytes     = 256;
-  my $buchungssatz    = "";
-  $filename++;
-  my $ed_filename = $self->export_path . $filename;
-  push(@filenames, $filename);
-  open(ED, ">", $ed_filename) or die "can't open outputfile: $!\n";
-  my $header = $self->make_kne_data_header($form);
-  $remaining_bytes -= length($header);
-
-  my $fuellzeichen;
-
-  my (@where, @values) = ((), ());
-  if ($self->accnofrom) {
-    push @where, 'c.accno >= ?';
-    push @values, $self->accnofrom;
-  }
-  if ($self->accnoto) {
-    push @where, 'c.accno <= ?';
-    push @values, $self->accnoto;
-  }
-
-  my $where_str = @where ? ' WHERE ' . join(' AND ', map { "($_)" } @where) : '';
-
-  my $query     = qq|SELECT c.accno, c.description
-                     FROM chart c
-                     $where_str
-                     ORDER BY c.accno|;
-
-  my $sth = $self->dbh->prepare($query);
-  $sth->execute(@values) || $form->dberror($query);
-
-  while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
-    if (($remaining_bytes - length("t" . $ref->{'accno'})) <= 6) {
-      $fuellzeichen = ($blockcount * 256 - length($buchungssatz . $header));
-      $buchungssatz .= "\x00" x $fuellzeichen;
-      $blockcount++;
-      $total_bytes = ($blockcount) * 256;
-    }
-    $buchungssatz .= "t" . $ref->{'accno'};
-    $remaining_bytes = $total_bytes - length($buchungssatz . $header);
-    $ref->{'description'} =~ s/[^0-9A-Za-z\$\%\&\*\+\-\/]//g;
-    $ref->{'description'} = substr($ref->{'description'}, 0, 40);
-    $ref->{'description'} =~ s/\ *$//;
-
-    if (
-        ($remaining_bytes - length("\x1E" . $ref->{'description'} . "\x1C\x79")
-        ) <= 6
-      ) {
-      $fuellzeichen = ($blockcount * 256 - length($buchungssatz . $header));
-      $buchungssatz .= "\x00" x $fuellzeichen;
-      $blockcount++;
-      $total_bytes = ($blockcount) * 256;
-    }
-    $buchungssatz .= "\x1E" . $ref->{'description'} . "\x1C\x79";
-    $remaining_bytes = $total_bytes - length($buchungssatz . $header);
-  }
-
-  $sth->finish;
-  print(ED $header);
-  print(ED $buchungssatz);
-  $fuellzeichen = 256 - (length($header . $buchungssatz . "z") % 256);
-  my $dateiende = "\x00" x $fuellzeichen;
-  print(ED "z");
-  print(ED $dateiende);
-  close(ED);
-
-  #Make EV Verwaltungsdatei
-  $ed_versionset[0] =
-    $self->make_ed_versionset($header, $filename, $blockcount);
-
-  my $ev_header = $self->make_ev_header($form, $fileno);
-  my $ev_filename = $self->export_path . $evfile;
-  push(@filenames, $evfile);
-  open(EV, ">", $ev_filename) or die "can't open outputfile: EV01\n";
-  print(EV $ev_header);
-
-  foreach my $file (@ed_versionset) {
-    print(EV $ed_versionset[$file]);
-  }
-  close(EV);
-
-  $self->add_filenames(@filenames);
-
-  $main::lxdebug->leave_sub();
-
-  return { 'download_token' => $self->download_token, 'filenames' => \@filenames };
-}
-
-sub _format_accno {
-  my ($accno) = @_;
-  return $accno . ('0' x (6 - min(length($accno), 6)));
-}
-
-sub csv_export_for_tax_accountant {
-  my ($self) = @_;
-
-  $self->generate_datev_data(from_to => $self->fromto);
-
-  foreach my $transaction (@{ $self->{DATEV} }) {
-    foreach my $entry (@{ $transaction }) {
-      $entry->{sortkey} = join '-', map { lc } (DateTime->from_kivitendo($entry->{transdate})->strftime('%Y%m%d'), $entry->{name}, $entry->{reference});
-    }
-  }
-
-  my %transactions =
-    partition_by { $_->[0]->{table} }
-    sort_by      { $_->[0]->{sortkey} }
-    grep         { 2 == scalar(@{ $_ }) }
-    @{ $self->{DATEV} };
-
-  my %column_defs = (
-    acc_trans_id      => { 'text' => $::locale->text('ID'), },
-    amount            => { 'text' => $::locale->text('Amount'), },
-    credit_accname    => { 'text' => $::locale->text('Credit Account Name'), },
-    credit_accno      => { 'text' => $::locale->text('Credit Account'), },
-    debit_accname     => { 'text' => $::locale->text('Debit Account Name'), },
-    debit_accno       => { 'text' => $::locale->text('Debit Account'), },
-    invnumber         => { 'text' => $::locale->text('Reference'), },
-    name              => { 'text' => $::locale->text('Name'), },
-    notes             => { 'text' => $::locale->text('Notes'), },
-    tax               => { 'text' => $::locale->text('Tax'), },
-    taxkey            => { 'text' => $::locale->text('Taxkey'), },
-    tax_accname       => { 'text' => $::locale->text('Tax Account Name'), },
-    tax_accno         => { 'text' => $::locale->text('Tax Account'), },
-    transdate         => { 'text' => $::locale->text('Transdate'), },
-    vcnumber          => { 'text' => $::locale->text('Customer/Vendor Number'), },
-  );
-
-  my @columns = qw(
-    acc_trans_id name           vcnumber
-    transdate    invnumber      amount
-    debit_accno  debit_accname
-    credit_accno credit_accname
-    tax
-    tax_accno    tax_accname    taxkey
-    notes
-  );
-
-  my %filenames_by_type = (
-    ar => $::locale->text('AR Transactions'),
-    ap => $::locale->text('AP Transactions'),
-    gl => $::locale->text('GL Transactions'),
-  );

 
 

-  my @filenames;
-  foreach my $type (qw(ap ar)) {
-    my %csvs = (
-      invoices   => {
-        content  => '',
-        filename => sprintf('%s %s - %s.csv', $filenames_by_type{$type}, $self->from->to_kivitendo, $self->to->to_kivitendo),
-        csv      => Text::CSV_XS->new({
-          binary   => 1,
-          eol      => "\n",
-          sep_char => ";",
-        }),
-      },
-      payments   => {
-        content  => '',
-        filename => sprintf('Zahlungen %s %s - %s.csv', $filenames_by_type{$type}, $self->from->to_kivitendo, $self->to->to_kivitendo),
-        csv      => Text::CSV_XS->new({
-          binary   => 1,
-          eol      => "\n",
-          sep_char => ";",
-        }),
-      },
-    );
-
-    foreach my $csv (values %csvs) {
-      $csv->{out} = IO::File->new($self->export_path . '/' . $csv->{filename}, '>:encoding(utf8)') ;
-      $csv->{csv}->print($csv->{out}, [ map { $column_defs{$_}->{text} } @columns ]);
-
-      push @filenames, $csv->{filename};
-    }
+  my $query = <<"SQL";
+  SELECT DISTINCT char_length (accno) FROM chart WHERE charttype='A' AND id in (select chart_id from acc_trans);
+SQL

 
 

-    foreach my $transaction (@{ $transactions{$type} }) {
-      my $is_payment     = any { $_->{link} =~ m{A[PR]_paid} } @{ $transaction };
-      my $csv            = $is_payment ? $csvs{payments} : $csvs{invoices};
-
-      my ($soll, $haben) = map { $transaction->[$_] } ($transaction->[0]->{amount} > 0 ? (1, 0) : (0, 1));
-      my $tax            = defined($soll->{tax_accno})  ? $soll : $haben;
-      my $amount         = defined($soll->{net_amount}) ? $soll : $haben;
-      $haben->{notes}    = ($haben->{memo} || $soll->{memo}) if $is_payment;
-      $haben->{notes}  //= '';
-      $haben->{notes}    =  SL::HTML::Util->strip($haben->{notes});
-      $haben->{notes}    =~ s{\r}{}g;
-      $haben->{notes}    =~ s{\n+}{ }g;
-
-      my %row            = (
-        amount           => $::form->format_amount({ numberformat => '1000,00' }, abs($amount->{amount}), 2),
-        debit_accno      => _format_accno($soll->{accno}),
-        debit_accname    => $soll->{accname},
-        credit_accno     => _format_accno($haben->{accno}),
-        credit_accname   => $haben->{accname},
-        tax              => $::form->format_amount({ numberformat => '1000,00' }, abs($amount->{amount}) - abs($amount->{net_amount}), 2),
-        notes            => $haben->{notes},
-        (map { ($_ => $tax->{$_})                    } qw(taxkey tax_accname tax_accno)),
-        (map { ($_ => ($haben->{$_} // $soll->{$_})) } qw(acc_trans_id invnumber name vcnumber transdate)),
-      );
-
-      $csv->{csv}->print($csv->{out}, [ map { $row{$_} } @columns ]);
-    }
-
-    $_->{out}->close for values %csvs;
+  my $accno_length = selectall_hashref_query($::form, SL::DB->client->dbh, $query);
+  if (1 < scalar @$accno_length) {
+    $::form->error(t8("Invalid combination of ledger account number length." .
+                      " Mismatch length of #1 with length of #2. Please check your account settings. ",
+                      $accno_length->[0]->{char_length}, $accno_length->[1]->{char_length}));

   }
   }

-
-  $self->add_filenames(@filenames);
-
-  return { download_token => $self->download_token, filenames => \@filenames };
+  return 1;

 }
 
 sub DESTROY {
 }
 
 sub DESTROY {


@@ -1483,7 +1125,7 @@ Forces a garbage collection on previous exports which will delete all exports th
 
 =item errors
 
 
 =item errors
 

-Returns a list of errors that occured. If no errors occured, the export was a success.
+Returns a list of errors that occurred. If no errors occurred, the export was a success.

 
 =item export
 
 
 =item export
 


@@ -1509,6 +1151,28 @@ Example:
   #                  ]
   # };
 
   #                  ]
   # };
 

+
+=item check_vcnumbers_are_valid_pk_numbers
+
+Returns 1 if all vcnumbers are suitable for the DATEV export, 0 if not.
+
+Finds the default length of charts (e.g. 4), adds 1 for the pk chart length
+(e.g. 5), and checks the database for any customers or vendors whose customer-
+or vendornumber doesn't consist of only numbers with exactly that length. E.g.
+for a chart length of four "10001" would be ok, but not "10001b" or "1000".
+
+All vcnumbers are checked, obsolete customers or vendors aren't exempt.
+
+There is also no check for the typical customer range 10000-69999 and the
+typical vendor range 70000-99999.
+
+=item check_valid_length_of_accounts
+
+Returns 1 if all currently booked accounts have only one common number length domain (e.g. 4 or 6).
+Will throw an error if more than one distinct size is detected.
+The error message gives a short hint with the value of the (at least)
+two mismatching number length domains.
+

 =back
 
 =head1 ATTRIBUTES
 =back
 
 =head1 ATTRIBUTES


@@ -1557,6 +1221,11 @@ correctly.
 
 Set boundary account numbers for the export. Only useful for a stammdaten export.
 
 
 Set boundary account numbers for the export. Only useful for a stammdaten export.
 

+=item locked
+
+Boolean if the transactions are locked (read-only in kivitenod) or not.
+Default value is false
+

 =back
 
 =head1 CONSTANTS
 =back
 
 =head1 CONSTANTS


@@ -1637,6 +1306,7 @@ OBE export is currently not implemented.
 =head1 SEE ALSO
 
 L<SL::DATEV::KNEFile>
 =head1 SEE ALSO
 
 L<SL::DATEV::KNEFile>

+L<SL::DATEV::CSV>

 
 =head1 AUTHORS
 
 
 =head1 AUTHORS