4 # optional: background jobable
9 use parent qw(Rose::Object);
16 use List::MoreUtils qw(any);
17 use List::UtilsBy qw(partition_by sort_by);
19 use SL::DB::Helper::ALL; # since we work on meta data, we need everything
20 use SL::DB::Helper::Mappings;
21 use SL::Locale::String qw(t8);
23 use Rose::Object::MakeMethods::Generic (
24 scalar => [ qw(from to writer company location) ],
25 'scalar --get_set_init' => [ qw(files tempfiles export_ids tables csv_headers) ],
30 # name: short name, translated
31 # description: long description, translated
32 # columns: list of columns to export. export all columns if not present
33 # primary_key: override primary key
35 chart => { name => t8('Charts'), description => t8('Chart of Accounts'), primary_key => 'accno', columns => [ qw(id accno description) ], },
36 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) ] },
37 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) ] },
43 accno => t8('Account Number'),
44 description => t8('Description'),
49 department_1 => t8('Department 1'),
50 department_2 => t8('Department 2'),
51 street => t8('Street'),
52 zipcode => t8('Zipcode'),
54 country => t8('Country'),
55 contact => t8('Contact'),
58 email => t8('E-mail'),
60 customernumber => t8('Customer Number'),
61 vendornumber => t8('Vendor Number'),
62 taxnumber => t8('Tax Number'),
63 obsolete => t8('Obsolete'),
64 ustid => t8('Tax ID number'),
67 $column_titles{$_} = $column_titles{customer_vendor} for qw(customer vendor);
69 my %datev_column_defs = (
70 trans_id => { type => 'Rose::DB::Object::Metadata::Column::Integer', text => t8('ID'), },
71 amount => { type => 'Rose::DB::Object::Metadata::Column::Numeric', text => t8('Amount'), },
72 credit_accname => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Credit Account Name'), },
73 credit_accno => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Credit Account'), },
74 credit_amount => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Credit Amount'), },
75 credit_tax => { type => 'Rose::DB::Object::Metadata::Column::Numeric', text => t8('Credit Tax (lit)'), },
76 debit_accname => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Debit Account Name'), },
77 debit_accno => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Debit Account'), },
78 debit_amount => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Debit Amount'), },
79 debit_tax => { type => 'Rose::DB::Object::Metadata::Column::Numeric', text => t8('Debit Tax (lit)'), },
80 invnumber => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Reference'), },
81 name => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Name'), },
82 notes => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Notes'), },
83 tax => { type => 'Rose::DB::Object::Metadata::Column::Numeric', text => t8('Tax'), },
84 taxdescription => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('tax_taxdescription'), },
85 taxkey => { type => 'Rose::DB::Object::Metadata::Column::Integer', text => t8('Taxkey'), },
86 tax_accname => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Tax Account Name'), },
87 tax_accno => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Tax Account'), },
88 transdate => { type => 'Rose::DB::Object::Metadata::Column::Date', text => t8('Invoice Date'), },
89 vcnumber => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Customer/Vendor Number'), },
90 customer_id => { type => 'Rose::DB::Object::Metadata::Column::Integer', text => t8('Customer (database ID)'), },
91 vendor_id => { type => 'Rose::DB::Object::Metadata::Column::Integer', text => t8('Vendor (database ID)'), },
92 itime => { type => 'Rose::DB::Object::Metadata::Column::Date', text => t8('Create Date'), },
93 gldate => { type => 'Rose::DB::Object::Metadata::Column::Date', text => t8('Booking Date'), },
96 my @datev_columns = qw(
100 transdate invnumber amount
101 debit_accno debit_accname debit_amount debit_tax
102 credit_accno credit_accname credit_amount credit_tax
104 tax_accno tax_accname taxkey
108 # rows in this listing are tiers.
109 # tables may depend on ids in a tier above them
110 my @export_table_order = qw(
111 ar ap gl oe delivery_orders
112 invoice orderitems delivery_order_items
119 # needed because the standard dbh sets datestyle german and we don't want to mess with that
120 my $date_format = 'DD.MM.YYYY';
121 my $number_format = '1000.00';
123 my $myconfig = { numberformat => $number_format };
125 # callbacks that produce the xml spec for these column types
127 'Rose::DB::Object::Metadata::Column::Integer' => sub { $_[0]->tag('Numeric') }, # see Caveats for integer issues
128 'Rose::DB::Object::Metadata::Column::BigInt' => sub { $_[0]->tag('Numeric') }, # see Caveats for integer issues
129 'Rose::DB::Object::Metadata::Column::Text' => sub { $_[0]->tag('AlphaNumeric') },
130 'Rose::DB::Object::Metadata::Column::Varchar' => sub { $_[0]->tag('AlphaNumeric') },
131 'Rose::DB::Object::Metadata::Column::Character' => sub { $_[0]->tag('AlphaNumeric') },
132 'Rose::DB::Object::Metadata::Column::Numeric' => sub { $_[0]->tag('Numeric', sub { $_[0]->tag('Accuracy', 5) }) },
133 'Rose::DB::Object::Metadata::Column::Date' => sub { $_[0]->tag('Date', sub { $_[0]->tag('Format', $date_format) }) },
134 'Rose::DB::Object::Metadata::Column::Timestamp' => sub { $_[0]->tag('Date', sub { $_[0]->tag('Format', $date_format) }) },
135 'Rose::DB::Object::Metadata::Column::Float' => sub { $_[0]->tag('Numeric') },
136 'Rose::DB::Object::Metadata::Column::Boolean' => sub { $_[0]
137 ->tag('AlphaNumeric')
138 ->tag('Map', sub { $_[0]
140 ->tag('To', t8('true'))
142 ->tag('Map', sub { $_[0]
144 ->tag('To', t8('false'))
146 ->tag('Map', sub { $_[0]
148 ->tag('To', t8('false'))
153 sub generate_export {
157 $self->from && 'DateTime' eq ref $self->from or die 'need from date';
158 $self->to && 'DateTime' eq ref $self->to or die 'need to date';
159 $self->from <= $self->to or die 'from date must be earlier or equal than to date';
160 $self->tables && @{ $self->tables } or die 'need tables';
161 for (@{ $self->tables }) {
162 next if $known_tables{$_};
163 die "unknown table '$_'";
166 # get data from those tables and save to csv
167 # for that we need to build queries that fetch all the columns
168 for ($self->sorted_tables) {
169 $self->do_csv_export($_);
172 $self->do_datev_csv_export;
178 $self->files->{'gdpdu-01-08-2002.dtd'} = File::Spec->catfile('users', 'gdpdu-01-08-2002.dtd');
181 my ($fh, $zipfile) = File::Temp::tempfile();
182 my $zip = Archive::Zip->new;
184 while (my ($name, $file) = each %{ $self->files }) {
185 $zip->addFile($file, $name);
188 $zip->writeToFileHandle($fh) == Archive::Zip::AZ_OK() or die 'error writing zip file';
197 my ($fh, $filename) = File::Temp::tempfile();
198 binmode($fh, ':utf8');
200 $self->files->{'INDEX.XML'} = $filename;
201 push @{ $self->tempfiles }, $filename;
203 my $writer = XML::Writer->new(
208 $self->writer($writer);
209 $self->writer->xmlDecl('UTF-8');
210 $self->writer->doctype('DataSet', undef, "gdpdu-01-08-2002.dtd");
211 $self->tag('DataSet', sub { $self
212 ->tag('Version', '1.0')
213 ->tag('DataSupplier', sub { $self
214 ->tag('Name', $self->client_name)
215 ->tag('Location', $self->client_location)
216 ->tag('Comment', $self->make_comment)
218 ->tag('Media', sub { $self
219 ->tag('Name', t8('DataSet #1', 1));
220 for (reverse $self->sorted_tables) { $self # see CAVEATS for table order
223 $self->do_datev_xml_table;
230 my ($self, $table) = @_;
231 my $writer = $self->writer;
233 $self->tag('Table', sub { $self
234 ->tag('URL', "$table.csv")
235 ->tag('Name', $known_tables{$table}{name})
236 ->tag('Description', $known_tables{$table}{description})
237 ->tag('Validity', sub { $self
238 ->tag('Range', sub { $self
239 ->tag('From', $self->from->to_kivitendo(dateformat => 'dd.mm.yyyy'))
240 ->tag('To', $self->to->to_kivitendo(dateformat => 'dd.mm.yyyy'))
242 ->tag('Format', $date_format)
245 ->tag('DecimalSymbol', '.')
246 ->tag('DigitGroupingSymbol', '|') # see CAVEATS in documentation
247 ->tag('Range', sub { $self
248 ->tag('From', $self->csv_headers ? 2 : 1)
250 ->tag('VariableLength', sub { $self
251 ->tag('ColumnDelimiter', ',') # see CAVEATS for missing RecordDelimiter
252 ->tag('TextEncapsulator', '"')
254 ->foreign_keys($table)
261 my $package = SL::DB::Helper::Mappings::get_package_for_table($table);
264 my $use_white_list = 0;
265 if ($known_tables{$table}{columns}) {
267 $white_list{$_} = 1 for @{ $known_tables{$table}{columns} || [] };
270 # PrimaryKeys must come before regular columns, so partition first
272 $known_tables{$table}{primary_key}
273 ? 1 * ($_ eq $known_tables{$table}{primary_key})
274 : 1 * $_->is_primary_key_member
276 $use_white_list ? $white_list{$_->name} : 1
277 } $package->meta->columns;
281 my ($self, $table) = @_;
283 my %cols_by_primary_key = _table_columns($table);
285 for my $column (@{ $cols_by_primary_key{1} }) {
286 my $type = $column_types{ ref $column };
288 die "unknown col type @{[ ref $column ]}" unless $type;
290 $self->tag('VariablePrimaryKey', sub { $self
291 ->tag('Name', $column_titles{$table}{$column->name});
296 for my $column (@{ $cols_by_primary_key{0} }) {
297 my $type = $column_types{ ref $column };
299 die "unknown col type @{[ ref $column]}" unless $type;
301 $self->tag('VariableColumn', sub { $self
302 ->tag('Name', $column_titles{$table}{$column->name});
311 my ($self, $table) = @_;
312 my $package = SL::DB::Helper::Mappings::get_package_for_table($table);
314 my %requested = map { $_ => 1 } @{ $self->tables };
316 for my $rel ($package->meta->foreign_keys) {
317 next unless $requested{ $rel->class->meta->table };
319 # ok, now extract the columns used as foreign key
320 my %key_columns = $rel->key_columns;
322 if (1 != keys %key_columns) {
323 die "multi keys? we don't support this currently. fix it please";
326 if ($table eq $rel->class->meta->table) {
327 # self referential foreign keys are a PITA to export correctly. skip!
331 $self->tag('ForeignKey', sub {
332 $_[0]->tag('Name', $column_titles{$table}{$_}) for keys %key_columns;
333 $_[0]->tag('References', $rel->class->meta->table);
338 sub do_datev_xml_table {
340 my $writer = $self->writer;
342 $self->tag('Table', sub { $self
343 ->tag('URL', "transactions.csv")
344 ->tag('Name', t8('Transactions'))
345 ->tag('Description', t8('Transactions'))
346 ->tag('Validity', sub { $self
347 ->tag('Range', sub { $self
348 ->tag('From', $self->from->to_kivitendo(dateformat => 'dd.mm.yyyy'))
349 ->tag('To', $self->to->to_kivitendo(dateformat => 'dd.mm.yyyy'))
351 ->tag('Format', $date_format)
354 ->tag('DecimalSymbol', '.')
355 ->tag('DigitGroupingSymbol', '|') # see CAVEATS in documentation
356 ->tag('Range', sub { $self
357 ->tag('From', $self->csv_headers ? 2 : 1)
359 ->tag('VariableLength', sub { $self
360 ->tag('ColumnDelimiter', ',') # see CAVEATS for missing RecordDelimiter
361 ->tag('TextEncapsulator', '"')
369 my ($self, $table) = @_;
371 my %cols_by_primary_key = partition_by { 1 * $datev_column_defs{$_}{primary_key} } @datev_columns;
373 for my $column (@{ $cols_by_primary_key{1} }) {
374 my $type = $column_types{ $datev_column_defs{$column}{type} };
376 die "unknown col type @{[ $column ]}" unless $type;
378 $self->tag('VariablePrimaryKey', sub { $self
379 ->tag('Name', $datev_column_defs{$column}{text});
384 for my $column (@{ $cols_by_primary_key{0} }) {
385 my $type = $column_types{ $datev_column_defs{$column}{type} };
387 die "unknown col type @{[ ref $column]}" unless $type;
389 $self->tag('VariableColumn', sub { $self
390 ->tag('Name', $datev_column_defs{$column}{text});
398 sub datev_foreign_keys {
401 $self->tag('ForeignKey', sub { $_[0]
402 ->tag('Name', $datev_column_defs{customer_id}{text})
403 ->tag('References', 'customer')
405 $self->tag('ForeignKey', sub { $_[0]
406 ->tag('Name', $datev_column_defs{vendor_id}{text})
407 ->tag('References', 'vendor')
409 $self->tag('ForeignKey', sub { $_[0]
410 ->tag('Name', $datev_column_defs{$_}{text})
411 ->tag('References', 'chart')
412 }) for qw(debit_accno credit_accno tax_accno);
415 sub do_datev_csv_export {
418 my $datev = SL::DATEV->new(from => $self->from, to => $self->to);
420 $datev->_get_transactions(from_to => $datev->fromto);
422 for my $transaction (@{ $datev->{DATEV} }) {
423 for my $entry (@{ $transaction }) {
424 $entry->{sortkey} = join '-', map { lc } (DateTime->from_kivitendo($entry->{transdate})->strftime('%Y%m%d'), $entry->{name}, $entry->{reference});
428 my @transactions = sort_by { $_->[0]->{sortkey} } @{ $datev->{DATEV} };
430 my $csv = Text::CSV_XS->new({ binary => 1, eol => "\r\n", sep_char => ",", quote_char => '"' });
432 my ($fh, $filename) = File::Temp::tempfile();
433 binmode($fh, ':utf8');
435 $self->files->{"transactions.csv"} = $filename;
436 push @{ $self->tempfiles }, $filename;
438 if ($self->csv_headers) {
439 $csv->print($fh, [ map { _normalize_cell($datev_column_defs{$_}{text}) } @datev_columns ]);
442 for my $transaction (@transactions) {
443 my $is_payment = any { $_->{link} =~ m{A[PR]_paid} } @{ $transaction };
445 my ($soll, $haben) = map { $transaction->[$_] } ($transaction->[0]->{amount} > 0 ? (1, 0) : (0, 1));
446 my $tax = defined($soll->{tax_accno}) ? $soll : defined($haben->{tax_accno}) ? $haben : {};
447 my $amount = defined($soll->{net_amount}) ? $soll : $haben;
448 $haben->{notes} = ($haben->{memo} || $soll->{memo}) if $haben->{memo} || $soll->{memo};
449 $haben->{notes} //= '';
450 $haben->{notes} = SL::HTML::Util->strip($haben->{notes});
452 my $tax_amount = defined $amount->{net_amount} ? abs($amount->{amount}) - abs($amount->{net_amount}) : 0;
454 $tax = {} if abs($tax_amount) < 0.001;
457 amount => $::form->format_amount($myconfig, abs($amount->{amount}),5),
458 debit_accno => $soll->{accno},
459 debit_accname => $soll->{accname},
460 debit_amount => $::form->format_amount($myconfig, abs(-$soll->{amount}),5),
461 debit_tax => $soll->{tax_accno} ? $::form->format_amount($myconfig, $tax_amount, 5) : 0,
462 credit_accno => $haben->{accno},
463 credit_accname => $haben->{accname},
464 credit_amount => $::form->format_amount($myconfig, abs($haben->{amount}),5),,
465 credit_tax => $haben->{tax_accno} ? $::form->format_amount($myconfig, $tax_amount, 5) : 0,
466 tax => $::form->format_amount($myconfig, $tax_amount, 5),
467 notes => $haben->{notes},
468 (map { ($_ => $tax->{$_}) } qw(taxkey tax_accname tax_accno taxdescription)),
469 (map { ($_ => ($haben->{$_} // $soll->{$_})) } qw(trans_id invnumber name vcnumber transdate gldate itime customer_id vendor_id)),
472 # if ($row{debit_amount} + $row{debit_tax} - ($row{credit_amount} + $row{credit_tax}) > 0.005) {
473 # $::lxdebug->dump(0, "broken taxes", [ $transaction, \%row, $row{debit_amount} + $row{debit_tax}, $row{credit_amount} + $row{credit_tax} ]);
476 _normalize_cell($_) for values %row; # see CAVEATS
478 $csv->print($fh, [ map { $row{$_} } @datev_columns ]);
481 # and build xml spec for it
485 my ($self, $table) = @_;
487 my $csv = Text::CSV_XS->new({ binary => 1, eol => "\r\n", sep_char => ",", quote_char => '"' });
489 my ($fh, $filename) = File::Temp::tempfile();
490 binmode($fh, ':utf8');
492 $self->files->{"$table.csv"} = $filename;
493 push @{ $self->tempfiles }, $filename;
495 # in the right order (primary keys first)
496 my %cols_by_primary_key = _table_columns($table);
497 my @columns = (@{ $cols_by_primary_key{1} }, @{ $cols_by_primary_key{0} });
498 my %col_index = do { my $i = 0; map {; "$_" => $i++ } @columns };
500 if ($self->csv_headers) {
501 $csv->print($fh, [ map { _normalize_cell($column_titles{$table}{$_->name}) } @columns ]) or die $csv->error_diag;
504 # and normalize date stuff
505 my @select_tokens = map { (ref $_) =~ /Time/ ? $_->name . '::date' : $_->name } @columns;
509 if ($known_tables{$table}{transdate}) {
511 push @where_tokens, "$known_tables{$table}{transdate} >= ?";
512 push @values, $self->from;
515 push @where_tokens, "$known_tables{$table}{transdate} <= ?";
516 push @values, $self->to;
519 if ($known_tables{$table}{tables}) {
520 my ($col, @col_specs) = @{ $known_tables{$table}{tables} };
523 my ($ftable, $fkey) = split /\./, $_;
524 if (!exists $self->export_ids->{$ftable}{$fkey}) {
525 # check if we forgot to keep it
526 if (!grep { $_ eq $fkey } @{ $known_tables{$ftable}{keep} || [] }) {
527 die "unknown table spec '$_' for table $table, did you forget to keep $fkey in $ftable?"
529 # hmm, most likely just an empty set.
530 $self->export_ids->{$ftable}{$fkey} = {};
533 $ids{$_}++ for keys %{ $self->export_ids->{$ftable}{$fkey} };
536 push @where_tokens, "$col IN (@{[ join ',', ('?') x keys %ids ]})";
537 push @values, keys %ids;
539 push @where_tokens, '1=0';
543 my $where_clause = @where_tokens ? 'WHERE ' . join ' AND ', @where_tokens : '';
545 my $query = "SELECT " . join(', ', @select_tokens) . " FROM $table $where_clause";
547 my $sth = $::form->get_standard_dbh->prepare($query);
548 $sth->execute(@values) or die "error executing query $query: " . $sth->errstr;
550 while (my $row = $sth->fetch) {
551 for my $keep_col (@{ $known_tables{$table}{keep} || [] }) {
552 next if !$row->[$col_index{$keep_col}];
553 $self->export_ids->{$table}{$keep_col} ||= {};
554 $self->export_ids->{$table}{$keep_col}{$row->[$col_index{$keep_col}]}++;
556 _normalize_cell($_) for @$row; # see CAVEATS
558 $csv->print($fh, $row) or $csv->error_diag;
564 my ($self, $tag, $content) = @_;
566 $self->writer->startTag($tag);
567 if ('CODE' eq ref $content) {
570 $self->writer->characters($content);
572 $self->writer->endTag;
577 my $gobd_version = API_VERSION();
578 my $kivi_version = $::form->read_version;
579 my $person = $::myconfig{name};
580 my $contact = join ', ',
581 (t8("Email") . ": $::myconfig{email}" ) x!! $::myconfig{email},
582 (t8("Tel") . ": $::myconfig{tel}" ) x!! $::myconfig{tel},
583 (t8("Fax") . ": $::myconfig{fax}" ) x!! $::myconfig{fax};
585 t8('DataSet for GoBD version #1. Created with kivitendo #2 by #3 (#4)',
586 $gobd_version, $kivi_version, $person, $contact
594 sub client_location {
601 my %given = map { $_ => 1 } @{ $self->tables };
603 grep { $given{$_} } @export_table_order;
607 my ($self, $yesno) = @_;
609 $self->tables(\@export_table_order) if $yesno;
612 sub _normalize_cell {
620 sub init_files { +{} }
621 sub init_export_ids { +{} }
622 sub init_tempfiles { [] }
623 sub init_tables { [ grep { $known_tables{$_} } @export_table_order ] }
624 sub init_csv_headers { 1 }
627 DateTime->new(year => 2002, month => 8, day => 14)->to_kivitendo;
631 unlink $_ for @{ $_[0]->tempfiles || [] };
642 SL::GoBD - IDEA export generator
650 Create new export object. C<PARAMS> may contain:
656 The name of the company, needed for the supplier header
660 Location of the company, needed for the supplier header
666 Will only include records in the specified date range. Data pulled from other
667 tables will be culled to match what is needed for these records.
671 Optional. If set, will include a header line in the exported CSV files. Default true.
675 Ooptional list of tables to be exported. Defaults to all tables.
679 Optional alternative to C<tables>, forces all known tables.
683 =item C<generate_export>
685 Do the work. Will return an absolute path to a temp file where all export files
692 Sigh. There are a lot of issues with the IDEA software that were found out by
695 =head2 Problems in the Specification
701 The specced date format is capable of only C<YY>, C<YYYY>, C<MM>,
702 and C<DD>. There are no timestamps or timezones.
706 Numbers have the same issue. There is not dedicated integer type, and hinting
707 at an integer type by setting accuracy to 0 generates a warning for redundant
710 Also the number parsing is documented to be fragile. Official docs state that
711 behaviour for too low C<Accuracy> settings is undefined.
715 Foreign key definition is broken. Instead of giving column maps it assumes that
716 foreign keys map to the primary keys given for the target table, and in that
717 order. Also the target table must be known in full before defining a foreign key.
719 As a consequence any additional keys apart from primary keys are not possible.
720 Self-referencing tables are also not possible.
724 The spec does not support splitting data sets into smaller chunks. For data
725 sets that exceed 700MB the spec helpfully suggests: "Use a bigger medium, such
730 It is not possible to set an empty C<DigitGroupingSymbol> since then the import
731 will just work with the default. This was asked in their forum, and the
732 response actually was to use a bogus grouping symbol that is not used:
734 Einfache Lösung: Definieren Sie das Tausendertrennzeichen als Komma, auch
735 wenn es nicht verwendet wird. Sollten Sie das Komma bereits als Feldtrenner
736 verwenden, so wählen Sie als Tausendertrennzeichen eine Alternative wie das
739 L<http://www.gdpdu-portal.com/forum/index.php?mode=thread&id=1392>
743 It is not possible to define a C<RecordDelimiter> with XML entities. 

744 generates the error message:
746 C<RecordDelimiter>-Wert (
) sollte immer aus ein oder zwei Zeichen
749 Instead we just use the implicit default RecordDelimiter CRLF.
753 =head2 Bugs in the IDEA software
759 The CSV import library used in IDEA is not able to parse newlines (or more
760 exactly RecordDelimiter) in data. So this export substites all of these with
765 Neither it is able to parse escaped C<ColumnDelimiter> in data. It just splits
766 on that symbol no matter what surrounds or preceeds it.
770 Oh and of course C<TextEncapsulator> is also not allowed in data. It's just
771 stripped at the beginning and end of data.
775 And the character "!" is used internally as a warning signal and must not be
776 present in the data as well.
780 C<VariableLength> data is truncated on import to 512 bytes (Note: it said
781 characters, but since they are mutilating data into a single byte encoding
782 anyway, they most likely meant bytes). The auditor recommends splitting into
787 Despite the standard specifying UTF-8 as a valid encoding the IDEA software
788 will just downgrade everything to latin1.
792 =head2 Problems outside of the software
798 The law states that "all business related data" should be made available. In
799 practice there's no definition for what makes data "business related", and
800 different auditors seems to want different data.
802 Currently we export most of the transactional data with supplementing
803 customers, vendors and chart of accounts.
807 While the standard explicitely state to provide data normalized, in practice
808 autditors aren't trained database operators and can not create complex vies on
809 normalized data on their own. The reason this works for other software is, that
810 DATEV and SAP seem to have written import plugins for their internal formats in
813 So what is really exported is not unlike a DATEV export. Each transaction gets
814 splitted into chunks of 2 positions (3 with tax on one side). Those get
815 denormalized into a single data row with credfit/debit/tax fields. The charts
816 get denormalized into it as well, in addition to their account number serving
819 Customers and vendors get denormalized into this as well, but are linked by ids
820 to their tables. And the reason for this is...
824 Some auditors do not have a full license of the IDEA software, and
825 can't do table joins.
831 Sven Schöling E<lt>s.schoeling@linet-services.deE<gt>