GDPDU: statt acc_trans_id die trans_id exportieren

[kivitendo-erp.git] / SL / GDPDU.pm

diff --git a/SL/GDPDU.pm b/SL/GDPDU.pm
index 12d872d..f96f73c 100644 (file)
--- a/SL/GDPDU.pm
+++ b/SL/GDPDU.pm
@@ -39,7 +39,7 @@ my %known_tables = (
 );
 
 my %datev_column_defs = (
 );
 
 my %datev_column_defs = (

-  acc_trans_id      => { type => 'Rose::DB::Object::Metadata::Column::Integer', text => t8('ID'), },
+  trans_id          => { type => 'Rose::DB::Object::Metadata::Column::Integer', text => t8('ID'), },

   amount            => { type => 'Rose::DB::Object::Metadata::Column::Numeric', text => t8('Amount'), },
   credit_accname    => { type => 'Rose::DB::Object::Metadata::Column::Text',    text => t8('Credit Account Name'), },
   credit_accno      => { type => 'Rose::DB::Object::Metadata::Column::Text',    text => t8('Credit Account'), },
   amount            => { type => 'Rose::DB::Object::Metadata::Column::Numeric', text => t8('Amount'), },
   credit_accname    => { type => 'Rose::DB::Object::Metadata::Column::Text',    text => t8('Credit Account Name'), },
   credit_accno      => { type => 'Rose::DB::Object::Metadata::Column::Text',    text => t8('Credit Account'), },


@@ -61,7 +61,7 @@ my %datev_column_defs = (
 );
 
 my @datev_columns = qw(
 );
 
 my @datev_columns = qw(

-  acc_trans_id
+  trans_id

   customer_id vendor_id
   name           vcnumber
   transdate    invnumber      amount
   customer_id vendor_id
   name           vcnumber
   transdate    invnumber      amount


@@ -405,8 +405,6 @@ sub do_datev_csv_export {
     $haben->{notes}    = ($haben->{memo} || $soll->{memo}) if $haben->{memo} || $soll->{memo};
     $haben->{notes}  //= '';
     $haben->{notes}    =  SL::HTML::Util->strip($haben->{notes});
     $haben->{notes}    = ($haben->{memo} || $soll->{memo}) if $haben->{memo} || $soll->{memo};
     $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($myconfig, abs($amount->{amount}),5),
 
     my %row            = (
       amount           => $::form->format_amount($myconfig, abs($amount->{amount}),5),


@@ -417,9 +415,11 @@ sub do_datev_csv_export {
       tax              => defined $amount->{net_amount} ? $::form->format_amount($myconfig, abs($amount->{amount}) - abs($amount->{net_amount}), 5) : 0,
       notes            => $haben->{notes},
       (map { ($_ => $tax->{$_})                    } qw(taxkey tax_accname tax_accno taxdescription)),
       tax              => defined $amount->{net_amount} ? $::form->format_amount($myconfig, abs($amount->{amount}) - abs($amount->{net_amount}), 5) : 0,
       notes            => $haben->{notes},
       (map { ($_ => $tax->{$_})                    } qw(taxkey tax_accname tax_accno taxdescription)),

-      (map { ($_ => ($haben->{$_} // $soll->{$_})) } qw(acc_trans_id invnumber name vcnumber transdate itime customer_id vendor_id)),
+      (map { ($_ => ($haben->{$_} // $soll->{$_})) } qw(trans_id invnumber name vcnumber transdate itime customer_id vendor_id)),

     );
 
     );
 

+    _normalize_cell($_) for values %row; # see CAVEATS
+

     $csv->print($fh, [ map { $row{$_} } @datev_columns ]);
   }
 
     $csv->print($fh, [ map { $row{$_} } @datev_columns ]);
   }
 


@@ -494,7 +494,7 @@ sub do_csv_export {
       $self->export_ids->{$table}{$keep_col} ||= {};
       $self->export_ids->{$table}{$keep_col}{$row->[$col_index{$keep_col}]}++;
     }
       $self->export_ids->{$table}{$keep_col} ||= {};
       $self->export_ids->{$table}{$keep_col}{$row->[$col_index{$keep_col}]}++;
     }

-    s/\r\n/ /g for @$row; # see CAVEATS
+    _normalize_cell($_) for @$row; # see CAVEATS

 
     $csv->print($fh, $row) or $csv->error_diag;
   }
 
     $csv->print($fh, $row) or $csv->error_diag;
   }


@@ -550,6 +550,11 @@ sub all_tables {
   $self->tables(\@export_table_order) if $yesno;
 }
 
   $self->tables(\@export_table_order) if $yesno;
 }
 

+sub _normalize_cell {
+  $_[0] =~ s/\r\n/ /g;
+  $_[0] =~ s/,/;/g;
+}
+

 sub init_files { +{} }
 sub init_export_ids { +{} }
 sub init_tempfiles { [] }
 sub init_files { +{} }
 sub init_export_ids { +{} }
 sub init_tempfiles { [] }


@@ -589,7 +594,7 @@ The name of the company, needed for the supplier header
 
 =item location
 
 
 =item location
 

-Location of the company, needed for the suupplier header
+Location of the company, needed for the supplier header

 
 =item from
 
 
 =item from
 


@@ -600,64 +605,64 @@ tables will be culled to match what is needed for these records.
 
 =item tables
 
 
 =item tables
 

-A list of tables to be exported.
+Ooptional list of tables to be exported. Defaults to all tables.

 
 =item all_tables
 
 
 =item all_tables
 

-Alternative to C<tables>, enables all known tables.
+Optional alternative to C<tables>, forces all known tables.

 
 =back
 
 =item C<generate_export>
 
 
 =back
 
 =item C<generate_export>
 

-Do the work. Will return an absolut path to a temp file where all export files
+Do the work. Will return an absolute path to a temp file where all export files

 are zipped together.
 
 =back
 
 =head1 CAVEATS
 
 are zipped together.
 
 =back
 
 =head1 CAVEATS
 

+Sigh. There are a lot of issues with the IDEA software that were found out by
+trial and error.
+
+=head2 Problems in the Specification
+

 =over 4
 
 =item *
 
 =over 4
 
 =item *
 

-Date format is shit. The official docs state that only C<YY>, C<YYYY>, C<MM>,
-and C<DD> are supported, timestamps do not exist.
+The specced date format is capable of only C<YY>, C<YYYY>, C<MM>,
+and C<DD>. There are no timestamps or timezones.

 
 =item *
 
 
 =item *
 

-Number parsing seems to be fragile. Official docs state that behaviour for too
-low C<Accuracy> settings is undefined. Accuracy of 0 is not taken to mean
-Integer but instead generates a warning for redudancy.
+Numbers have the same issue. There is not dedicated integer type, and hinting
+at an integer type by setting accuracy to 0 generates a warning for redundant
+accuracy.

 
 

-There is no dedicated integer type.
+Also the number parsing is documented to be fragile. Official docs state that
+behaviour for too low C<Accuracy> settings is undefined.

 
 =item *
 
 
 =item *
 

-Currently C<ar> and C<ap> have a foreign key to themself with the name
-C<storno_id>. If this foreign key is present in the C<INDEX.XML> then the
-storno records have to be too. Since this is extremely awkward to code and
-confusing for the examiner as to why there are records outside of the time
-range, this export skips all self-referential foreign keys.
-
-=item *
+Foreign key definition is broken. Instead of giving column maps it assumes that
+foreign keys map to the primary keys given for the target table, and in that
+order. Also the target table must be known in full before defining a foreign key.

 
 

-Documentation for foreign keys is extremely weird. Instead of giving column
-maps it assumes that foreign keys map to the primary keys given for the target
-table, and in that order. Foreign keys to keys that are not primary seems to be
-impossible. Changing type is also not allowed (which actually makes sense).
-Hopefully there are no bugs there.
+As a consequence any additional keys apart from primary keys are not possible.
+Self-referencing tables are also not possible.

 
 =item *
 
 
 =item *
 

-It's currently disallowed to export the whole dataset. It's not clear if this
-is wanted.
+The spec does not support splitting data sets into smaller chunks. For data
+sets that exceed 700MB the spec helpfully suggests: "Use a bigger medium, such
+as a DVD".

 
 =item *
 
 
 =item *
 

-It is not possible to set an empty C<DigiGroupingSymbol> since then the import
+It is not possible to set an empty C<DigitGroupingSymbol> since then the import

 will just work with the default. This was asked in their forum, and the
 will just work with the default. This was asked in their forum, and the

-response actually was:
+response actually was to use a bogus grouping symbol that is not used:

 
   Einfache Lösung: Definieren Sie das Tausendertrennzeichen als Komma, auch
   wenn es nicht verwendet wird. Sollten Sie das Komma bereits als Feldtrenner
 
   Einfache Lösung: Definieren Sie das Tausendertrennzeichen als Komma, auch
   wenn es nicht verwendet wird. Sollten Sie das Komma bereits als Feldtrenner


@@ -676,12 +681,11 @@ generates the error message:
 
 Instead we just use the implicit default RecordDelimiter CRLF.
 
 
 Instead we just use the implicit default RecordDelimiter CRLF.
 

-=item *
+=back

 
 

-Not confirmed yet:
+=head2 Bugs in the IDEA software

 
 

-Foreign keys seem only to work with previously defined tables (which would be
-utterly insane).
+=over 4

 
 =item *
 
 
 =item *
 


@@ -689,6 +693,48 @@ The CSV import library used in IDEA is not able to parse newlines (or more
 exactly RecordDelimiter) in data. So this export substites all of these with
 spaces.
 
 exactly RecordDelimiter) in data. So this export substites all of these with
 spaces.
 

+=item *
+
+Neither it is able to parse escaped C<ColumnDelimiter> in data. It just splits
+on that symbol no matter what surrounds or preceeds it.
+
+=back
+
+=head2 Problems outside of the software
+
+=over 4
+
+=item *
+
+The law states that "all business related data" should be made available. In
+practice there's no definition for what makes data "business related", and
+different auditors seems to want different data.
+
+Currently we export most of the transactional data with supplementing
+customers, vendors and chart of accounts.
+
+=item *
+
+While the standard explicitely state to provide data normalized, in practice
+autditors aren't trained database operators and can not create complex vies on
+normalized data on their own. The reason this works for other software is, that
+DATEV and SAP seem to have written import plugins for their internal formats in
+the IDEA software.
+
+So what is really exported is not unlike a DATEV export. Each transaction gets
+splitted into chunks of 2 positions (3 with tax on one side). Those get
+denormalized into a single data row with credfit/debit/tax fields. The charts
+get denormalized into it as well, in addition to their account number serving
+as a foreign key.
+
+Customers and vendors get denormalized into this as well, but are linked by ids
+to their tables. And the reason for this is...
+
+=item *
+
+Some auditors do not have a full license of the IDEA software, and
+can't do table joins.
+

 =back
 
 =head1 AUTHOR
 =back
 
 =head1 AUTHOR