Projekt

Allgemein

Profil

Herunterladen (26,7 KB) Statistiken
| Zweig: | Markierung: | Revision:

kivitendo/SL/GoBD.pm @ 77cb45b9

package SL::GoBD;

# TODO:
# optional: background jobable

use strict;
use utf8;

use parent qw(Rose::Object);

use Text::CSV_XS;
use XML::Writer;
use Archive::Zip;
use File::Temp ();
use File::Spec ();
use List::MoreUtils qw(any);
use List::UtilsBy qw(partition_by sort_by);

use SL::DB::Helper::ALL; # since we work on meta data, we need everything
use SL::DB::Helper::Mappings;
use SL::Locale::String qw(t8);
use SL::Version;

use Rose::Object::MakeMethods::Generic (
scalar => [ qw(from to writer company location) ],
'scalar --get_set_init' => [ qw(files tempfiles export_ids tables csv_headers) ],
);

# in this we find:
# key: table name
# name: short name, translated
# description: long description, translated
# columns: list of columns to export. export all columns if not present
# primary_key: override primary key
my %known_tables = (
chart => { name => t8('Charts'), description => t8('Chart of Accounts'), primary_key => 'accno', columns => [ qw(id accno description) ], },
customer => { name => t8('Customers'), description => t8('Customer Master Data'), columns => [ qw(id customernumber name department_1 department_2 street zipcode city country contact phone fax email notes taxnumber obsolete ustid) ] },
vendor => { name => t8('Vendors'), description => t8('Vendor Master Data'), columns => [ qw(id vendornumber name department_1 department_2 street zipcode city country contact phone fax email notes taxnumber obsolete ustid) ] },
);

my %column_titles = (
chart => {
id => t8('ID'),
accno => t8('Account Number'),
description => t8('Description'),
},
customer_vendor => {
id => t8('ID (lit)'),
name => t8('Name'),
department_1 => t8('Department 1'),
department_2 => t8('Department 2'),
street => t8('Street'),
zipcode => t8('Zipcode'),
city => t8('City'),
country => t8('Country'),
contact => t8('Contact'),
phone => t8('Phone'),
fax => t8('Fax'),
email => t8('E-mail'),
notes => t8('Notes'),
customernumber => t8('Customer Number'),
vendornumber => t8('Vendor Number'),
taxnumber => t8('Tax Number'),
obsolete => t8('Obsolete'),
ustid => t8('Tax ID number'),
},
);
$column_titles{$_} = $column_titles{customer_vendor} for qw(customer vendor);

my %datev_column_defs = (
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'), },
credit_amount => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Credit Amount'), },
credit_tax => { type => 'Rose::DB::Object::Metadata::Column::Numeric', text => t8('Credit Tax (lit)'), },
debit_accname => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Debit Account Name'), },
debit_accno => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Debit Account'), },
debit_amount => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Debit Amount'), },
debit_tax => { type => 'Rose::DB::Object::Metadata::Column::Numeric', text => t8('Debit Tax (lit)'), },
invnumber => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Reference'), },
name => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Name'), },
notes => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Notes'), },
tax => { type => 'Rose::DB::Object::Metadata::Column::Numeric', text => t8('Tax'), },
taxdescription => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('tax_taxdescription'), },
taxkey => { type => 'Rose::DB::Object::Metadata::Column::Integer', text => t8('Taxkey'), },
tax_accname => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Tax Account Name'), },
tax_accno => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Tax Account'), },
transdate => { type => 'Rose::DB::Object::Metadata::Column::Date', text => t8('Transdate'), },
vcnumber => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Customer/Vendor Number'), },
customer_id => { type => 'Rose::DB::Object::Metadata::Column::Integer', text => t8('Customer (database ID)'), },
vendor_id => { type => 'Rose::DB::Object::Metadata::Column::Integer', text => t8('Vendor (database ID)'), },
itime => { type => 'Rose::DB::Object::Metadata::Column::Date', text => t8('Create Date'), },
gldate => { type => 'Rose::DB::Object::Metadata::Column::Date', text => t8('Gldate'), },
);

my @datev_columns = qw(
trans_id
customer_id vendor_id
name vcnumber
transdate invnumber amount
debit_accno debit_accname debit_amount debit_tax
credit_accno credit_accname credit_amount credit_tax
taxdescription tax
tax_accno tax_accname taxkey
notes itime gldate
);

# rows in this listing are tiers.
# tables may depend on ids in a tier above them
my @export_table_order = qw(
ar ap gl oe delivery_orders
invoice orderitems delivery_order_items
customer vendor
parts
acc_trans
chart
);

# needed because the standard dbh sets datestyle german and we don't want to mess with that
my $date_format = 'DD.MM.YYYY';
my $number_format = '1000.00';

my $myconfig = { numberformat => $number_format };

# callbacks that produce the xml spec for these column types
my %column_types = (
'Rose::DB::Object::Metadata::Column::Integer' => sub { $_[0]->tag('Numeric') }, # see Caveats for integer issues
'Rose::DB::Object::Metadata::Column::BigInt' => sub { $_[0]->tag('Numeric') }, # see Caveats for integer issues
'Rose::DB::Object::Metadata::Column::Text' => sub { $_[0]->tag('AlphaNumeric') },
'Rose::DB::Object::Metadata::Column::Varchar' => sub { $_[0]->tag('AlphaNumeric') },
'Rose::DB::Object::Metadata::Column::Character' => sub { $_[0]->tag('AlphaNumeric') },
'Rose::DB::Object::Metadata::Column::Numeric' => sub { $_[0]->tag('Numeric', sub { $_[0]->tag('Accuracy', 5) }) },
'Rose::DB::Object::Metadata::Column::Date' => sub { $_[0]->tag('Date', sub { $_[0]->tag('Format', $date_format) }) },
'Rose::DB::Object::Metadata::Column::Timestamp' => sub { $_[0]->tag('Date', sub { $_[0]->tag('Format', $date_format) }) },
'Rose::DB::Object::Metadata::Column::Float' => sub { $_[0]->tag('Numeric') },
'Rose::DB::Object::Metadata::Column::Boolean' => sub { $_[0]
->tag('AlphaNumeric')
->tag('Map', sub { $_[0]
->tag('From', 1)
->tag('To', t8('true'))
})
->tag('Map', sub { $_[0]
->tag('From', 0)
->tag('To', t8('false'))
})
->tag('Map', sub { $_[0]
->tag('From', '')
->tag('To', t8('false'))
})
},
);

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

# verify data
$self->from && 'DateTime' eq ref $self->from or die 'need from date';
$self->to && 'DateTime' eq ref $self->to or die 'need to date';
$self->from <= $self->to or die 'from date must be earlier or equal than to date';
$self->tables && @{ $self->tables } or die 'need tables';
for (@{ $self->tables }) {
next if $known_tables{$_};
die "unknown table '$_'";
}

# get data from those tables and save to csv
# for that we need to build queries that fetch all the columns
for ($self->sorted_tables) {
$self->do_csv_export($_);
}

$self->do_datev_csv_export;

# write xml file
$self->do_xml_file;

# add dtd
$self->files->{'gdpdu-01-08-2002.dtd'} = File::Spec->catfile('users', 'gdpdu-01-08-2002.dtd');

# make zip
my ($fh, $zipfile) = File::Temp::tempfile();
my $zip = Archive::Zip->new;

while (my ($name, $file) = each %{ $self->files }) {
$zip->addFile($file, $name);
}

$zip->writeToFileHandle($fh) == Archive::Zip::AZ_OK() or die 'error writing zip file';
close($fh);

return $zipfile;
}

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

my ($fh, $filename) = File::Temp::tempfile();
binmode($fh, ':utf8');

$self->files->{'INDEX.XML'} = $filename;
push @{ $self->tempfiles }, $filename;

my $writer = XML::Writer->new(
OUTPUT => $fh,
ENCODING => 'UTF-8',
);

$self->writer($writer);
$self->writer->xmlDecl('UTF-8');
$self->writer->doctype('DataSet', undef, "gdpdu-01-08-2002.dtd");
$self->tag('DataSet', sub { $self
->tag('Version', '1.0')
->tag('DataSupplier', sub { $self
->tag('Name', $self->client_name)
->tag('Location', $self->client_location)
->tag('Comment', $self->make_comment)
})
->tag('Media', sub { $self
->tag('Name', t8('DataSet #1', 1));
for (reverse $self->sorted_tables) { $self # see CAVEATS for table order
->table($_)
}
$self->do_datev_xml_table;
})
});
close($fh);
}

sub table {
my ($self, $table) = @_;
my $writer = $self->writer;

$self->tag('Table', sub { $self
->tag('URL', "$table.csv")
->tag('Name', $known_tables{$table}{name})
->tag('Description', $known_tables{$table}{description})
->tag('Validity', sub { $self
->tag('Range', sub { $self
->tag('From', $self->from->to_kivitendo(dateformat => 'dd.mm.yyyy'))
->tag('To', $self->to->to_kivitendo(dateformat => 'dd.mm.yyyy'))
})
->tag('Format', $date_format)
})
->tag('UTF8')
->tag('DecimalSymbol', '.')
->tag('DigitGroupingSymbol', '|') # see CAVEATS in documentation
->tag('Range', sub { $self
->tag('From', $self->csv_headers ? 2 : 1)
})
->tag('VariableLength', sub { $self
->tag('ColumnDelimiter', ',') # see CAVEATS for missing RecordDelimiter
->tag('TextEncapsulator', '"')
->columns($table)
->foreign_keys($table)
})
});
}

sub _table_columns {
my ($table) = @_;
my $package = SL::DB::Helper::Mappings::get_package_for_table($table);

my %white_list;
my $use_white_list = 0;
if ($known_tables{$table}{columns}) {
$use_white_list = 1;
$white_list{$_} = 1 for @{ $known_tables{$table}{columns} || [] };
}

# PrimaryKeys must come before regular columns, so partition first
partition_by {
$known_tables{$table}{primary_key}
? 1 * ($_ eq $known_tables{$table}{primary_key})
: 1 * $_->is_primary_key_member
} grep {
$use_white_list ? $white_list{$_->name} : 1
} $package->meta->columns;
}

sub columns {
my ($self, $table) = @_;

my %cols_by_primary_key = _table_columns($table);

for my $column (@{ $cols_by_primary_key{1} }) {
my $type = $column_types{ ref $column };

die "unknown col type @{[ ref $column ]}" unless $type;

$self->tag('VariablePrimaryKey', sub { $self
->tag('Name', $column_titles{$table}{$column->name});
$type->($self);
})
}

for my $column (@{ $cols_by_primary_key{0} }) {
my $type = $column_types{ ref $column };

die "unknown col type @{[ ref $column]}" unless $type;

$self->tag('VariableColumn', sub { $self
->tag('Name', $column_titles{$table}{$column->name});
$type->($self);
})
}

$self;
}

sub foreign_keys {
my ($self, $table) = @_;
my $package = SL::DB::Helper::Mappings::get_package_for_table($table);

my %requested = map { $_ => 1 } @{ $self->tables };

for my $rel ($package->meta->foreign_keys) {
next unless $requested{ $rel->class->meta->table };

# ok, now extract the columns used as foreign key
my %key_columns = $rel->key_columns;

if (1 != keys %key_columns) {
die "multi keys? we don't support this currently. fix it please";
}

if ($table eq $rel->class->meta->table) {
# self referential foreign keys are a PITA to export correctly. skip!
next;
}

$self->tag('ForeignKey', sub {
$_[0]->tag('Name', $column_titles{$table}{$_}) for keys %key_columns;
$_[0]->tag('References', $rel->class->meta->table);
});
}
}

sub do_datev_xml_table {
my ($self) = @_;
my $writer = $self->writer;

$self->tag('Table', sub { $self
->tag('URL', "transactions.csv")
->tag('Name', t8('Transactions'))
->tag('Description', t8('Transactions'))
->tag('Validity', sub { $self
->tag('Range', sub { $self
->tag('From', $self->from->to_kivitendo(dateformat => 'dd.mm.yyyy'))
->tag('To', $self->to->to_kivitendo(dateformat => 'dd.mm.yyyy'))
})
->tag('Format', $date_format)
})
->tag('UTF8')
->tag('DecimalSymbol', '.')
->tag('DigitGroupingSymbol', '|') # see CAVEATS in documentation
->tag('Range', sub { $self
->tag('From', $self->csv_headers ? 2 : 1)
})
->tag('VariableLength', sub { $self
->tag('ColumnDelimiter', ',') # see CAVEATS for missing RecordDelimiter
->tag('TextEncapsulator', '"')
->datev_columns
->datev_foreign_keys
})
});
}

sub datev_columns {
my ($self, $table) = @_;

my %cols_by_primary_key = partition_by { 1 * $datev_column_defs{$_}{primary_key} } @datev_columns;

for my $column (@{ $cols_by_primary_key{1} }) {
my $type = $column_types{ $datev_column_defs{$column}{type} };

die "unknown col type @{[ $column ]}" unless $type;

$self->tag('VariablePrimaryKey', sub { $self
->tag('Name', $datev_column_defs{$column}{text});
$type->($self);
})
}

for my $column (@{ $cols_by_primary_key{0} }) {
my $type = $column_types{ $datev_column_defs{$column}{type} };

die "unknown col type @{[ ref $column]}" unless $type;

$self->tag('VariableColumn', sub { $self
->tag('Name', $datev_column_defs{$column}{text});
$type->($self);
})
}

$self;
}

sub datev_foreign_keys {
my ($self) = @_;
# hard code weeee
$self->tag('ForeignKey', sub { $_[0]
->tag('Name', $datev_column_defs{customer_id}{text})
->tag('References', 'customer')
});
$self->tag('ForeignKey', sub { $_[0]
->tag('Name', $datev_column_defs{vendor_id}{text})
->tag('References', 'vendor')
});
$self->tag('ForeignKey', sub { $_[0]
->tag('Name', $datev_column_defs{$_}{text})
->tag('References', 'chart')
}) for qw(debit_accno credit_accno tax_accno);
}

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

my $datev = SL::DATEV->new(from => $self->from, to => $self->to);

$datev->generate_datev_data(from_to => $datev->fromto);

if ($datev->errors) {
die [ $datev->errors ];
}

for my $transaction (@{ $datev->{DATEV} }) {
for my $entry (@{ $transaction }) {
$entry->{sortkey} = join '-', map { lc } (DateTime->from_kivitendo($entry->{transdate})->strftime('%Y%m%d'), $entry->{name}, $entry->{reference});
}
}

my @transactions = sort_by { $_->[0]->{sortkey} } @{ $datev->{DATEV} };

my $csv = Text::CSV_XS->new({ binary => 1, eol => "\r\n", sep_char => ",", quote_char => '"' });

my ($fh, $filename) = File::Temp::tempfile();
binmode($fh, ':utf8');

$self->files->{"transactions.csv"} = $filename;
push @{ $self->tempfiles }, $filename;

if ($self->csv_headers) {
$csv->print($fh, [ map { _normalize_cell($datev_column_defs{$_}{text}) } @datev_columns ]);
}

for my $transaction (@transactions) {
my $is_payment = any { $_->{link} =~ m{A[PR]_paid} } @{ $transaction };

my ($soll, $haben) = map { $transaction->[$_] } ($transaction->[0]->{amount} > 0 ? (1, 0) : (0, 1));
my $tax = defined($soll->{tax_accno}) ? $soll : defined($haben->{tax_accno}) ? $haben : {};
my $amount = defined($soll->{net_amount}) ? $soll : $haben;
$haben->{notes} = ($haben->{memo} || $soll->{memo}) if $haben->{memo} || $soll->{memo};
$haben->{notes} //= '';
$haben->{notes} = SL::HTML::Util->strip($haben->{notes});

my $tax_amount = defined $amount->{net_amount} ? abs($amount->{amount}) - abs($amount->{net_amount}) : 0;

$tax = {} if abs($tax_amount) < 0.001;

my %row = (
amount => $::form->format_amount($myconfig, abs($amount->{amount}),5),
debit_accno => $soll->{accno},
debit_accname => $soll->{accname},
debit_amount => $::form->format_amount($myconfig, abs(-$soll->{amount}),5),
debit_tax => $soll->{tax_accno} ? $::form->format_amount($myconfig, $tax_amount, 5) : 0,
credit_accno => $haben->{accno},
credit_accname => $haben->{accname},
credit_amount => $::form->format_amount($myconfig, abs($haben->{amount}),5),,
credit_tax => $haben->{tax_accno} ? $::form->format_amount($myconfig, $tax_amount, 5) : 0,
tax => $::form->format_amount($myconfig, $tax_amount, 5),
notes => $haben->{notes},
(map { ($_ => $tax->{$_}) } qw(taxkey tax_accname tax_accno taxdescription)),
(map { ($_ => ($haben->{$_} // $soll->{$_})) } qw(trans_id invnumber name vcnumber transdate gldate itime customer_id vendor_id)),
);

# if ($row{debit_amount} + $row{debit_tax} - ($row{credit_amount} + $row{credit_tax}) > 0.005) {
# $::lxdebug->dump(0, "broken taxes", [ $transaction, \%row, $row{debit_amount} + $row{debit_tax}, $row{credit_amount} + $row{credit_tax} ]);
# }

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

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

# and build xml spec for it
}

sub do_csv_export {
my ($self, $table) = @_;

my $csv = Text::CSV_XS->new({ binary => 1, eol => "\r\n", sep_char => ",", quote_char => '"' });

my ($fh, $filename) = File::Temp::tempfile();
binmode($fh, ':utf8');

$self->files->{"$table.csv"} = $filename;
push @{ $self->tempfiles }, $filename;

# in the right order (primary keys first)
my %cols_by_primary_key = _table_columns($table);
my @columns = (@{ $cols_by_primary_key{1} }, @{ $cols_by_primary_key{0} });
my %col_index = do { my $i = 0; map {; "$_" => $i++ } @columns };

if ($self->csv_headers) {
$csv->print($fh, [ map { _normalize_cell($column_titles{$table}{$_->name}) } @columns ]) or die $csv->error_diag;
}

# and normalize date stuff
my @select_tokens = map { (ref $_) =~ /Time/ ? $_->name . '::date' : $_->name } @columns;

my @where_tokens;
my @values;
if ($known_tables{$table}{transdate}) {
if ($self->from) {
push @where_tokens, "$known_tables{$table}{transdate} >= ?";
push @values, $self->from;
}
if ($self->to) {
push @where_tokens, "$known_tables{$table}{transdate} <= ?";
push @values, $self->to;
}
}
if ($known_tables{$table}{tables}) {
my ($col, @col_specs) = @{ $known_tables{$table}{tables} };
my %ids;
for (@col_specs) {
my ($ftable, $fkey) = split /\./, $_;
if (!exists $self->export_ids->{$ftable}{$fkey}) {
# check if we forgot to keep it
if (!grep { $_ eq $fkey } @{ $known_tables{$ftable}{keep} || [] }) {
die "unknown table spec '$_' for table $table, did you forget to keep $fkey in $ftable?"
} else {
# hmm, most likely just an empty set.
$self->export_ids->{$ftable}{$fkey} = {};
}
}
$ids{$_}++ for keys %{ $self->export_ids->{$ftable}{$fkey} };
}
if (keys %ids) {
push @where_tokens, "$col IN (@{[ join ',', ('?') x keys %ids ]})";
push @values, keys %ids;
} else {
push @where_tokens, '1=0';
}
}

my $where_clause = @where_tokens ? 'WHERE ' . join ' AND ', @where_tokens : '';

my $query = "SELECT " . join(', ', @select_tokens) . " FROM $table $where_clause";

my $sth = $::form->get_standard_dbh->prepare($query);
$sth->execute(@values) or $::form->dberror($query);

while (my $row = $sth->fetch) {
for my $keep_col (@{ $known_tables{$table}{keep} || [] }) {
next if !$row->[$col_index{$keep_col}];
$self->export_ids->{$table}{$keep_col} ||= {};
$self->export_ids->{$table}{$keep_col}{$row->[$col_index{$keep_col}]}++;
}
_normalize_cell($_) for @$row; # see CAVEATS

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

sub tag {
my ($self, $tag, $content) = @_;

$self->writer->startTag($tag);
if ('CODE' eq ref $content) {
$content->($self);
} else {
$self->writer->characters($content);
}
$self->writer->endTag;
return $self;
}

sub make_comment {
my $gobd_version = API_VERSION();
my $kivi_version = SL::Version->get_version;
my $person = $::myconfig{name};
my $contact = join ', ',
(t8("Email") . ": $::myconfig{email}" ) x!! $::myconfig{email},
(t8("Tel") . ": $::myconfig{tel}" ) x!! $::myconfig{tel},
(t8("Fax") . ": $::myconfig{fax}" ) x!! $::myconfig{fax};

t8('DataSet for GoBD version #1. Created with kivitendo #2 by #3 (#4)',
$gobd_version, $kivi_version, $person, $contact
);
}

sub client_name {
$_[0]->company
}

sub client_location {
$_[0]->location
}

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

my %given = map { $_ => 1 } @{ $self->tables };

grep { $given{$_} } @export_table_order;
}

sub all_tables {
my ($self, $yesno) = @_;

$self->tables(\@export_table_order) if $yesno;
}

sub _normalize_cell {
$_[0] =~ s/\r\n/ /g;
$_[0] =~ s/,/;/g;
$_[0] =~ s/"/'/g;
$_[0] =~ s/!/./g;
$_[0]
}

sub init_files { +{} }
sub init_export_ids { +{} }
sub init_tempfiles { [] }
sub init_tables { [ grep { $known_tables{$_} } @export_table_order ] }
sub init_csv_headers { 1 }

sub API_VERSION {
DateTime->new(year => 2002, month => 8, day => 14)->to_kivitendo;
}

sub DESTROY {
unlink $_ for @{ $_[0]->tempfiles || [] };
}

1;

__END__

=encoding utf-8

=head1 NAME

SL::GoBD - IDEA export generator

=head1 FUNCTIONS

=over 4

=item C<new PARAMS>

Create new export object. C<PARAMS> may contain:

=over 4

=item company

The name of the company, needed for the supplier header

=item location

Location of the company, needed for the supplier header

=item from

=item to

Will only include records in the specified date range. Data pulled from other
tables will be culled to match what is needed for these records.

=item csv_headers

Optional. If set, will include a header line in the exported CSV files. Default true.

=item tables

Ooptional list of tables to be exported. Defaults to all tables.

=item all_tables

Optional alternative to C<tables>, forces all known tables.

=back

=item C<generate_export>

Do the work. Will return an absolute path to a temp file where all export files
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 *

The specced date format is capable of only C<YY>, C<YYYY>, C<MM>,
and C<DD>. There are no timestamps or timezones.

=item *

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.

Also the number parsing is documented to be fragile. Official docs state that
behaviour for too low C<Accuracy> settings is undefined.

=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.

As a consequence any additional keys apart from primary keys are not possible.
Self-referencing tables are also not possible.

=item *

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 *

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
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
verwenden, so wählen Sie als Tausendertrennzeichen eine Alternative wie das
Pipe-Symbol |.

L<http://www.gdpdu-portal.com/forum/index.php?mode=thread&id=1392>

=item *

It is not possible to define a C<RecordDelimiter> with XML entities. &#x0A;
generates the error message:

C<RecordDelimiter>-Wert (&#x0A;) sollte immer aus ein oder zwei Zeichen
bestehen.

Instead we just use the implicit default RecordDelimiter CRLF.

=back

=head2 Bugs in the IDEA software

=over 4

=item *

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.

=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.

=item *

Oh and of course C<TextEncapsulator> is also not allowed in data. It's just
stripped at the beginning and end of data.

=item *

And the character "!" is used internally as a warning signal and must not be
present in the data as well.

=item *

C<VariableLength> data is truncated on import to 512 bytes (Note: it said
characters, but since they are mutilating data into a single byte encoding
anyway, they most likely meant bytes). The auditor recommends splitting into
multiple columns.

=item *

Despite the standard specifying UTF-8 as a valid encoding the IDEA software
will just downgrade everything to latin1.

=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

Sven Schöling E<lt>s.schoeling@linet-services.deE<gt>

=cut
(34-34/85)