Menu: Bei einzelner erp.ini nicht alles umkopieren.

[mfinanz.git] / SL / DATEV.pm

diff --git a/SL/DATEV.pm b/SL/DATEV.pm
index 50fb1c7a602f456792bb25fa60b6256a6107f47f..ca74cd287531d70a25c443a7dd566ed4873afb39 100644 (file)
--- a/SL/DATEV.pm
+++ b/SL/DATEV.pm
@@ -1,5 +1,5 @@
 #=====================================================================
 #=====================================================================

-# Lx-Office ERP
+# kivitendo ERP

 # Copyright (c) 2004
 #
 #  Author: Philip Reetz
 # Copyright (c) 2004
 #
 #  Author: Philip Reetz


@@ -31,7 +31,6 @@ use strict;
 
 use SL::DBUtils;
 use SL::DATEV::KNEFile;
 
 use SL::DBUtils;
 use SL::DATEV::KNEFile;

-use SL::Taxkeys;

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


@@ -193,6 +192,18 @@ sub to {
  return $self->{to};
 }
 
  return $self->{to};
 }
 

+sub trans_id {
+  my $self = shift;
+
+  if (@_) {
+    $self->{trans_id} = $_[0];
+  }
+
+  die "illegal trans_id passed for DATEV export: " . $self->{trans_id} . "\n" unless $self->{trans_id} =~ m/^\d+$/;
+
+  return $self->{trans_id};
+}
+

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


@@ -337,61 +348,79 @@ sub _sign {
 sub _get_transactions {
   $main::lxdebug->enter_sub();
   my $self     = shift;
 sub _get_transactions {
   $main::lxdebug->enter_sub();
   my $self     = shift;

-  my $fromto   =  shift;
+  my $fromto   = shift;

   my $progress_callback = shift || sub {};
 
   my $form     =  $main::form;
 
   my $progress_callback = shift || sub {};
 
   my $form     =  $main::form;
 

-  my ($notsplitindex);
+  my $trans_id_filter = '';
+
+  if ( $self->{trans_id} ) {
+    # ignore dates when trans_id is passed so that the entire transaction is
+    # checked, not just either the initial bookings or the subsequent payments
+    # (the transdates will likely differ)
+    $fromto = '';
+    $trans_id_filter = 'ac.trans_id = ' . $self->trans_id;
+  } else {
+    $fromto      =~ s/transdate/ac\.transdate/g;
+  };

 
 

-  $fromto      =~ s/transdate/ac\.transdate/g;
+  my ($notsplitindex);

 
 

-  my $taxkeys  = Taxkeys->new();

   my $filter   = '';            # Useful for debugging purposes
 
   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 $query    =
     qq|SELECT ac.acc_trans_id, ac.transdate, ac.trans_id,ar.id, ac.amount, ac.taxkey,
   my $filter   = '';            # Useful for debugging purposes
 
   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 $query    =
     qq|SELECT ac.acc_trans_id, ac.transdate, ac.trans_id,ar.id, ac.amount, ac.taxkey,

-         ar.invnumber, ar.duedate, ar.amount as umsatz,
-         ct.name,
-         c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, c.link,
-         ar.invoice
+         ar.invnumber, ar.duedate, ar.amount as umsatz, ar.deliverydate,
+         ct.name, ct.ustid,
+         c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
+         ar.invoice,
+         t.rate AS taxrate

        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)
        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)

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

+         $trans_id_filter

          $filter
 
        UNION ALL
 
        SELECT ac.acc_trans_id, ac.transdate, ac.trans_id,ap.id, ac.amount, ac.taxkey,
          $filter
 
        UNION ALL
 
        SELECT ac.acc_trans_id, ac.transdate, ac.trans_id,ap.id, ac.amount, ac.taxkey,

-         ap.invnumber, ap.duedate, ap.amount as umsatz,
-         ct.name,
-         c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, c.link,
-         ap.invoice
+         ap.invnumber, ap.duedate, ap.amount as umsatz, ap.deliverydate,
+         ct.name,ct.ustid,
+         c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
+         ap.invoice,
+         t.rate AS taxrate

        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)
        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)

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

+         $trans_id_filter

          $filter
 
        UNION ALL
 
        SELECT ac.acc_trans_id, ac.transdate, ac.trans_id,gl.id, ac.amount, ac.taxkey,
          $filter
 
        UNION ALL
 
        SELECT ac.acc_trans_id, ac.transdate, ac.trans_id,gl.id, ac.amount, ac.taxkey,

-         gl.reference AS invnumber, gl.transdate AS duedate, ac.amount as umsatz,
-         gl.description AS name,
-         c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, c.link,
-         FALSE AS invoice
+         gl.reference AS invnumber, gl.transdate AS duedate, ac.amount as umsatz, NULL as deliverydate,
+         gl.description AS name, NULL as ustid,
+         c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
+         FALSE AS invoice,
+         t.rate AS taxrate

        FROM acc_trans ac
        LEFT JOIN gl      ON (ac.trans_id  = gl.id)
        LEFT JOIN chart c ON (ac.chart_id  = c.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)

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

+         $trans_id_filter

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


@@ -400,7 +429,10 @@ sub _get_transactions {
   $self->{DATEV} = [];
 
   my $counter = 0;
   $self->{DATEV} = [];
 
   my $counter = 0;

-  while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
+  my $continue = 1; #
+  my $name;
+  while ( $continue && (my $ref = $sth->fetchrow_hashref("NAME_lc")) ) {
+    last unless $ref;  # for single transactions

     $counter++;
     if (($counter % 500) == 0) {
       $progress_callback->($counter);
     $counter++;
     if (($counter % 500) == 0) {
       $progress_callback->($counter);


@@ -424,13 +456,27 @@ sub _get_transactions {
     # keep fetching new acc_trans lines until the end of a balanced group is reached
     while (abs($count) > 0.01 || $firstrun || ($subcent && abs($count) > 0.005)) {
       my $ref2 = $sth->fetchrow_hashref("NAME_lc");
     # keep fetching new acc_trans lines until the end of a balanced group is reached
     while (abs($count) > 0.01 || $firstrun || ($subcent && abs($count) > 0.005)) {
       my $ref2 = $sth->fetchrow_hashref("NAME_lc");

-      last unless ($ref2);
+      unless ( $ref2 ) {
+        $continue = 0;
+        last;
+      };

 
       # check if trans_id of current acc_trans line is still the same as the
 
       # check if trans_id of current acc_trans line is still the same as the

-      # trans_id of the first line in group
+      # trans_id of the first line in group, i.e. we haven't finished a 0-group
+      # before moving on to the next trans_id, error will likely be in the old
+      # trans_id.

 
       if ($ref2->{trans_id} != $trans->[0]->{trans_id}) {
 
       if ($ref2->{trans_id} != $trans->[0]->{trans_id}) {

-        $self->add_error("Unbalanced ledger! old trans_id " . $trans->[0]->{trans_id} . " new trans_id " . $ref2->{trans_id} . " count $count");
+        require SL::DB::Manager::AccTransaction;
+        if ( $trans->[0]->{trans_id} ) {
+          my $acc_trans_old_obj  = SL::DB::Manager::AccTransaction->get_first(where => [ trans_id => $trans->[0]->{trans_id} ]);
+          $self->add_error("Unbalanced ledger! Old: " . $acc_trans_old_obj->transaction_name) if ref($acc_trans_old_obj);
+        };
+        if ( $ref2->{trans_id} ) {
+          my $acc_trans_curr_obj = SL::DB::Manager::AccTransaction->get_first(where => [ trans_id => $ref2->{trans_id} ]);
+          $self->add_error("Unbalanced ledger! New:" . $acc_trans_curr_obj->transaction_name) if ref($acc_trans_curr_obj);
+        };
+        $self->add_error("count: $count");

         return;
       }
 
         return;
       }
 


@@ -458,18 +504,17 @@ sub _get_transactions {
       }
     }
 
       }
     }
 

-    my %taxid_taxkeys = ();

     my $absumsatz     = 0;
     if (scalar(@{$trans}) <= 2) {
       push @{ $self->{DATEV} }, $trans;
       next;
     }
 
     my $absumsatz     = 0;
     if (scalar(@{$trans}) <= 2) {
       push @{ $self->{DATEV} }, $trans;
       next;
     }
 

-    # determine at which array position the reference value (called absumsatz) is 
+    # determine at which array position the reference value (called absumsatz) is

     # and which amount it has
 
     for my $j (0 .. (scalar(@{$trans}) - 1)) {
     # and which amount it has
 
     for my $j (0 .. (scalar(@{$trans}) - 1)) {

-      
+

       # Three cases:
       # 1: gl transaction (Dialogbuchung), invoice is false, no double split booking allowed
 
       # Three cases:
       # 1: gl transaction (Dialogbuchung), invoice is false, no double split booking allowed
 


@@ -528,12 +573,11 @@ sub _get_transactions {
         push @{ $self->{DATEV} }, [ \%new_trans, $trans->[$j] ];
 
       } elsif (($j != $notsplitindex) && !$trans->[$j]->{is_tax}) {
         push @{ $self->{DATEV} }, [ \%new_trans, $trans->[$j] ];
 
       } elsif (($j != $notsplitindex) && !$trans->[$j]->{is_tax}) {

-        my %tax_info = $taxkeys->get_full_tax_info('transdate' => $trans->[$j]->{transdate});

 
         my %new_trans = ();
         map { $new_trans{$_} = $trans->[$notsplitindex]->{$_}; } keys %{ $trans->[$notsplitindex] };
 
 
         my %new_trans = ();
         map { $new_trans{$_} = $trans->[$notsplitindex]->{$_}; } keys %{ $trans->[$notsplitindex] };
 

-        my $tax_rate              = $tax_info{taxkeys}->{ $trans->[$j]->{'taxkey'} }->{taxrate};
+        my $tax_rate              = $trans->[$j]->{'taxrate'};

         $new_trans{'net_amount'}  = $trans->[$j]->{'amount'} * -1;
         $new_trans{'tax_rate'}    = 1 + $tax_rate;
 
         $new_trans{'net_amount'}  = $trans->[$j]->{'amount'} * -1;
         $new_trans{'tax_rate'}    = 1 + $tax_rate;
 


@@ -599,7 +643,9 @@ sub _get_transactions {
 
     $absumsatz = $form->round_amount($absumsatz, 2);
     if (abs($absumsatz) >= (0.01 * (1 + scalar @taxed))) {
 
     $absumsatz = $form->round_amount($absumsatz, 2);
     if (abs($absumsatz) >= (0.01 * (1 + scalar @taxed))) {

-      $self->add_error("Datev-Export fehlgeschlagen! Bei Transaktion $trans->[0]->{trans_id} ($absumsatz)");
+      require SL::DB::Manager::AccTransaction;
+      my $acc_trans_obj  = SL::DB::Manager::AccTransaction->get_first(where => [ trans_id => $trans->[0]->{trans_id} ]);
+      $self->add_error("Datev-Export fehlgeschlagen! Bei Transaktion " . $acc_trans_obj->transaction_name . " ($absumsatz)");

 
     } elsif (abs($absumsatz) >= 0.01) {
       $self->add_net_gross_differences($absumsatz);
 
     } elsif (abs($absumsatz) >= 0.01) {
       $self->add_net_gross_differences($absumsatz);


@@ -791,6 +837,7 @@ sub kne_buchungsexport {
       my $datevautomatik = 0;
       my $taxkey         = 0;
       my $charttax       = 0;
       my $datevautomatik = 0;
       my $taxkey         = 0;
       my $charttax       = 0;

+      my $ustid          ="";

       my ($haben, $soll);
       my $iconv          = $::locale->{iconv_utf8};
       my %umlaute = ($iconv->convert('ä') => 'ae',
       my ($haben, $soll);
       my $iconv          = $::locale->{iconv_utf8};
       my %umlaute = ($iconv->convert('ä') => 'ae',


@@ -825,7 +872,6 @@ sub kne_buchungsexport {
           $soll = $i;
         }
       }
           $soll = $i;
         }
       }

-

       # Umwandlung von Umlauten und Sonderzeichen in erlaubte Zeichen bei Textfeldern
       foreach my $umlaut (keys(%umlaute)) {
         $transaction->[$haben]->{'invnumber'} =~ s/${umlaut}/${umlaute{$umlaut}}/g;
       # Umwandlung von Umlauten und Sonderzeichen in erlaubte Zeichen bei Textfeldern
       foreach my $umlaut (keys(%umlaute)) {
         $transaction->[$haben]->{'invnumber'} =~ s/${umlaut}/${umlaute{$umlaut}}/g;


@@ -853,6 +899,9 @@ sub kne_buchungsexport {
         if ($transaction->[$haben]->{'name'} ne "") {
           $buchungstext = "\x1E" . $transaction->[$haben]->{'name'} . "\x1C";
         }
         if ($transaction->[$haben]->{'name'} ne "") {
           $buchungstext = "\x1E" . $transaction->[$haben]->{'name'} . "\x1C";
         }

+        if ($transaction->[$haben]->{'ustid'} ne "") {
+          $ustid = "\xBA" . $transaction->[$haben]->{'ustid'} . "\x1C";
+        }

         if ($transaction->[$haben]->{'duedate'} ne "") {
           $belegfeld2 = "\xBE" . &datetofour($transaction->[$haben]->{'duedate'}, 1) . "\x1C";
         }
         if ($transaction->[$haben]->{'duedate'} ne "") {
           $belegfeld2 = "\xBE" . &datetofour($transaction->[$haben]->{'duedate'}, 1) . "\x1C";
         }


@@ -884,6 +933,7 @@ sub kne_buchungsexport {
       $kne_file->add_block($datum);
       $kne_file->add_block($konto);
       $kne_file->add_block($buchungstext);
       $kne_file->add_block($datum);
       $kne_file->add_block($konto);
       $kne_file->add_block($buchungstext);

+      $kne_file->add_block($ustid);

       $kne_file->add_block($waehrung . "\x79");
     }
 
       $kne_file->add_block($waehrung . "\x79");
     }
 


@@ -1037,12 +1087,14 @@ __END__
 
 =head1 NAME
 
 
 =head1 NAME
 

-SL::DATEV - Lx-Office DATEV Export module
+SL::DATEV - kivitendo DATEV Export module

 
 =head1 SYNOPSIS
 
   use SL::DATEV qw(:CONSTANTS);
 
 
 =head1 SYNOPSIS
 
   use SL::DATEV qw(:CONSTANTS);
 

+  my $startdate = DateTime->new(year => 2014, month => 9, day => 1);
+  my $enddate   = DateTime->new(year => 2014, month => 9, day => 31);

   my $datev = SL::DATEV->new(
     exporttype => DATEV_ET_BUCHUNGEN,
     format     => DATEV_FORMAT_KNE,
   my $datev = SL::DATEV->new(
     exporttype => DATEV_ET_BUCHUNGEN,
     format     => DATEV_FORMAT_KNE,


@@ -1050,6 +1102,14 @@ SL::DATEV - Lx-Office DATEV Export module
     to         => $enddate,
   );
 
     to         => $enddate,
   );
 

+  # To only export transactions from a specific trans_id: (from and to are ignored)
+  my $invoice = SL::DB::Manager::Invoice->find_by( invnumber => '216' );
+  my $datev = SL::DATEV->new(
+    exporttype => DATEV_ET_BUCHUNGEN,
+    format     => DATEV_FORMAT_KNE,
+    trans_id   => $invoice->trans_id,
+  );
+

   my $datev = SL::DATEV->new(
     exporttype => DATEV_ET_STAMM,
     format     => DATEV_FORMAT_KNE,
   my $datev = SL::DATEV->new(
     exporttype => DATEV_ET_STAMM,
     format     => DATEV_FORMAT_KNE,


@@ -1061,7 +1121,7 @@ SL::DATEV - Lx-Office DATEV Export module
   my $hashref = $datev->get_datev_stamm;
   $datev->save_datev_stamm($hashref);
 
   my $hashref = $datev->get_datev_stamm;
   $datev->save_datev_stamm($hashref);
 

-  # manually clean up temporary directories
+  # manually clean up temporary directories older than 8 hours

   $datev->clean_temporary_directories;
 
   # export
   $datev->clean_temporary_directories;
 
   # export


@@ -1094,7 +1154,7 @@ This module implements the DATEV export standard. For usage see above.
 
 =item new PARAMS
 
 
 =item new PARAMS
 

-Generic constructor. See section attributes for information about hat to pass.
+Generic constructor. See section attributes for information about what to pass.

 
 =item get_datev_stamm
 
 
 =item get_datev_stamm
 


@@ -1136,7 +1196,7 @@ Note: If either a download_token or export_path were set at the creation these a
 
 =item filenames
 
 
 =item filenames
 

-Returns a list of filenames generated by this DATEV object. This only works if th files were generated during it's lifetime, not if the object was created from a download_token.
+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.

 
 =item net_gross_differences
 
 
 =item net_gross_differences
 


@@ -1188,7 +1248,14 @@ Can be set on creation to retrieve a prior export for download.
 
 =item to
 
 
 =item to
 

-Set boundary dates for the export. Currently thse MUST be set for the export to work.
+Set boundary dates for the export. Unless a trans_id is passed these MUST be
+set for the export to work.
+
+=item trans_id
+
+To check only one gl/ar/ap transaction, pass the trans_id. The attributes
+L<from> and L<to> are currently still needed for the query to be assembled
+correctly.

 
 =item accnofrom
 
 
 =item accnofrom
 


@@ -1232,7 +1299,7 @@ No or unrecognized exporttype or format was provided for an export
 
 =item *
 
 
 =item *
 

-OBE rxport was called, which is not yet implemented.
+OBE export was called, which is not yet implemented.

 
 =item *
 
 
 =item *
 


@@ -1251,7 +1318,7 @@ C<Unbalanced Ledger!>. Exactly that, your ledger is unbalanced. Should never occ
 =item *
 
 C<Datev-Export fehlgeschlagen! Bei Transaktion %d (%f).>  This error occurs if a
 =item *
 
 C<Datev-Export fehlgeschlagen! Bei Transaktion %d (%f).>  This error occurs if a

-transaction could not be reliably sorted out, or had rounding errors over the acceptable threshold.
+transaction could not be reliably sorted out, or had rounding errors above the acceptable threshold.

 
 =back
 
 
 =back
 


@@ -1261,7 +1328,7 @@ transaction could not be reliably sorted out, or had rounding errors over the ac
 
 =item *
 
 
 =item *
 

-Handling of Vollvorlauf is currently not fully implemented. You must provide both from and to to get a working export.
+Handling of Vollvorlauf is currently not fully implemented. You must provide both from and to in order to get a working export.

 
 =item *
 
 
 =item *