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) ],
30 # name: short name, translated
31 # description: long description, translated
32 # transdate: column used to filter from/to, empty if table is filtered otherwise
33 # keep: arrayref of columns that should be saved for further referencing
34 # tables: arrayref with one column and one or many table.column references that were kept earlier
36 chart => { name => t8('Charts'), description => t8('Chart of Accounts'), primary_key => 'accno', columns => [ qw(id accno description) ], },
37 customer => { name => t8('Customers'), description => t8('Customer Master Data'), columns => [ qw(id name department_1 department_2 street zipcode city country contact phone fax email notes customernumber taxnumber obsolete ustid) ] },
38 vendor => { name => t8('Vendors'), description => t8('Vendor Master Data'), columns => [ qw(id name department_1 department_2 street zipcode city country contact phone fax email notes customernumber taxnumber obsolete ustid) ] },
41 my %datev_column_defs = (
42 acc_trans_id => { type => 'Rose::DB::Object::Metadata::Column::Integer', text => t8('ID'), },
43 amount => { type => 'Rose::DB::Object::Metadata::Column::Numeric', text => t8('Amount'), },
44 credit_accname => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Credit Account Name'), },
45 credit_accno => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Credit Account'), },
46 debit_accname => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Debit Account Name'), },
47 debit_accno => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Debit Account'), },
48 invnumber => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Reference'), },
49 name => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Name'), },
50 notes => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Notes'), },
51 tax => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Tax'), },
52 taxdescription => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('tax_taxdescription'), },
53 taxkey => { type => 'Rose::DB::Object::Metadata::Column::Integer', text => t8('Taxkey'), },
54 tax_accname => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Tax Account Name'), },
55 tax_accno => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Tax Account'), },
56 transdate => { type => 'Rose::DB::Object::Metadata::Column::Date', text => t8('Invoice Date'), },
57 vcnumber => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Customer/Vendor Number'), },
58 customer_id => { type => 'Rose::DB::Object::Metadata::Column::Integer', text => t8('Customer (database ID)'), },
59 vendor_id => { type => 'Rose::DB::Object::Metadata::Column::Integer', text => t8('Vendor (database ID)'), },
60 itime => { type => 'Rose::DB::Object::Metadata::Column::Date', text => t8('Create Date'), },
63 my @datev_columns = qw(
67 transdate invnumber amount
68 debit_accno debit_accname
69 credit_accno credit_accname
71 tax_accno tax_accname taxkey
75 # rows in this listing are tiers.
76 # tables may depend on ids in a tier above them
77 my @export_table_order = qw(
78 ar ap gl oe delivery_orders
79 invoice orderitems delivery_order_items
86 # needed because the standard dbh sets datestyle german and we don't want to mess with that
87 my $date_format = 'DD.MM.YYYY';
88 my $number_format = '1000.00';
90 my $myconfig = { numberformat => $number_format };
92 # callbacks that produce the xml spec for these column types
94 'Rose::DB::Object::Metadata::Column::Integer' => sub { $_[0]->tag('Numeric') }, # see Caveats for integer issues
95 'Rose::DB::Object::Metadata::Column::BigInt' => sub { $_[0]->tag('Numeric') }, # see Caveats for integer issues
96 'Rose::DB::Object::Metadata::Column::Text' => sub { $_[0]->tag('AlphaNumeric') },
97 'Rose::DB::Object::Metadata::Column::Varchar' => sub { $_[0]->tag('AlphaNumeric') },
98 'Rose::DB::Object::Metadata::Column::Character' => sub { $_[0]->tag('AlphaNumeric') },
99 'Rose::DB::Object::Metadata::Column::Numeric' => sub { $_[0]->tag('Numeric', sub { $_[0]->tag('Accuracy', 5) }) },
100 'Rose::DB::Object::Metadata::Column::Date' => sub { $_[0]->tag('Date', sub { $_[0]->tag('Format', $date_format) }) },
101 'Rose::DB::Object::Metadata::Column::Timestamp' => sub { $_[0]->tag('Date', sub { $_[0]->tag('Format', $date_format) }) },
102 'Rose::DB::Object::Metadata::Column::Float' => sub { $_[0]->tag('Numeric') },
103 'Rose::DB::Object::Metadata::Column::Boolean' => sub { $_[0]
104 ->tag('AlphaNumeric')
105 ->tag('Map', sub { $_[0]
107 ->tag('To', t8('true'))
109 ->tag('Map', sub { $_[0]
111 ->tag('To', t8('false'))
113 ->tag('Map', sub { $_[0]
115 ->tag('To', t8('false'))
120 sub generate_export {
124 $self->from && 'DateTime' eq ref $self->from or die 'need from date';
125 $self->to && 'DateTime' eq ref $self->to or die 'need to date';
126 $self->from <= $self->to or die 'from date must be earlier or equal than to date';
127 $self->tables && @{ $self->tables } or die 'need tables';
128 for (@{ $self->tables }) {
129 next if $known_tables{$_};
130 die "unknown table '$_'";
133 # get data from those tables and save to csv
134 # for that we need to build queries that fetch all the columns
135 for ($self->sorted_tables) {
136 $self->do_csv_export($_);
139 $self->do_datev_csv_export;
145 $self->files->{'gdpdu-01-08-2002.dtd'} = File::Spec->catfile('users', 'gdpdu-01-08-2002.dtd');
148 my ($fh, $zipfile) = File::Temp::tempfile();
149 my $zip = Archive::Zip->new;
151 while (my ($name, $file) = each %{ $self->files }) {
152 $zip->addFile($file, $name);
155 $zip->writeToFileHandle($fh) == Archive::Zip::AZ_OK() or die 'error writing zip file';
164 my ($fh, $filename) = File::Temp::tempfile();
165 binmode($fh, ':utf8');
167 $self->files->{'INDEX.XML'} = $filename;
168 push @{ $self->tempfiles }, $filename;
170 my $writer = XML::Writer->new(
175 $self->writer($writer);
176 $self->writer->xmlDecl('UTF-8');
177 $self->writer->doctype('DataSet', undef, "gdpdu-01-08-2002.dtd");
178 $self->tag('DataSet', sub { $self
179 ->tag('Version', '1.0')
180 ->tag('DataSupplier', sub { $self
181 ->tag('Name', $self->client_name)
182 ->tag('Location', $self->client_location)
183 ->tag('Comment', $self->make_comment)
185 ->tag('Media', sub { $self
186 ->tag('Name', t8('DataSet #1', 1));
187 for (reverse $self->sorted_tables) { $self # see CAVEATS for table order
190 $self->do_datev_xml_table;
197 my ($self, $table) = @_;
198 my $writer = $self->writer;
200 $self->tag('Table', sub { $self
201 ->tag('URL', "$table.csv")
202 ->tag('Name', $known_tables{$table}{name})
203 ->tag('Description', $known_tables{$table}{description})
204 ->tag('Validity', sub { $self
205 ->tag('Range', sub { $self
206 ->tag('From', $self->from->to_kivitendo(dateformat => 'dd.mm.yyyy'))
207 ->tag('To', $self->to->to_kivitendo(dateformat => 'dd.mm.yyyy'))
209 ->tag('Format', $date_format)
212 ->tag('DecimalSymbol', '.')
213 ->tag('DigitGroupingSymbol', '|') # see CAVEATS in documentation
214 ->tag('VariableLength', sub { $self
215 ->tag('ColumnDelimiter', ',') # see CAVEATS for missing RecordDelimiter
216 ->tag('TextEncapsulator', '"')
218 ->foreign_keys($table)
225 my $package = SL::DB::Helper::Mappings::get_package_for_table($table);
228 my $use_white_list = 0;
229 if ($known_tables{$table}{columns}) {
231 $white_list{$_} = 1 for @{ $known_tables{$table}{columns} || [] };
234 # PrimaryKeys must come before regular columns, so partition first
236 $known_tables{$table}{primary_key}
237 ? 1 * ($_ eq $known_tables{$table}{primary_key})
238 : 1 * $_->is_primary_key_member
240 $use_white_list ? $white_list{$_->name} : 1
241 } $package->meta->columns;
245 my ($self, $table) = @_;
247 my %cols_by_primary_key = _table_columns($table);
249 for my $column (@{ $cols_by_primary_key{1} }) {
250 my $type = $column_types{ ref $column };
252 die "unknown col type @{[ ref $column ]}" unless $type;
254 $self->tag('VariablePrimaryKey', sub { $self
255 ->tag('Name', $column->name);
260 for my $column (@{ $cols_by_primary_key{0} }) {
261 my $type = $column_types{ ref $column };
263 die "unknown col type @{[ ref $column]}" unless $type;
265 $self->tag('VariableColumn', sub { $self
266 ->tag('Name', $column->name);
275 my ($self, $table) = @_;
276 my $package = SL::DB::Helper::Mappings::get_package_for_table($table);
278 my %requested = map { $_ => 1 } @{ $self->tables };
280 for my $rel ($package->meta->foreign_keys) {
281 next unless $requested{ $rel->class->meta->table };
283 # ok, now extract the columns used as foreign key
284 my %key_columns = $rel->key_columns;
286 if (1 != keys %key_columns) {
287 die "multi keys? we don't support this currently. fix it please";
290 if ($table eq $rel->class->meta->table) {
291 # self referential foreign keys are a PITA to export correctly. skip!
295 $self->tag('ForeignKey', sub {
296 $_[0]->tag('Name', $_) for keys %key_columns;
297 $_[0]->tag('References', $rel->class->meta->table);
302 sub do_datev_xml_table {
304 my $writer = $self->writer;
306 $self->tag('Table', sub { $self
307 ->tag('URL', "transactions.csv")
308 ->tag('Name', t8('Transactions'))
309 ->tag('Description', t8('Transactions'))
310 ->tag('Validity', sub { $self
311 ->tag('Range', sub { $self
312 ->tag('From', $self->from->to_kivitendo(dateformat => 'dd.mm.yyyy'))
313 ->tag('To', $self->to->to_kivitendo(dateformat => 'dd.mm.yyyy'))
315 ->tag('Format', $date_format)
318 ->tag('DecimalSymbol', '.')
319 ->tag('DigitGroupingSymbol', '|') # see CAVEATS in documentation
320 ->tag('VariableLength', sub { $self
321 ->tag('ColumnDelimiter', ',') # see CAVEATS for missing RecordDelimiter
322 ->tag('TextEncapsulator', '"')
330 my ($self, $table) = @_;
332 my %cols_by_primary_key = partition_by { 1 * $datev_column_defs{$_}{primary_key} } @datev_columns;
334 for my $column (@{ $cols_by_primary_key{1} }) {
335 my $type = $column_types{ $datev_column_defs{$column}{type} };
337 die "unknown col type @{[ $column ]}" unless $type;
339 $self->tag('VariablePrimaryKey', sub { $self
340 ->tag('Name', $column);
345 for my $column (@{ $cols_by_primary_key{0} }) {
346 my $type = $column_types{ $datev_column_defs{$column}{type} };
348 die "unknown col type @{[ ref $column]}" unless $type;
350 $self->tag('VariableColumn', sub { $self
351 ->tag('Name', $column);
359 sub datev_foreign_keys {
362 $self->tag('ForeignKey', sub { $_[0]
363 ->tag('Name', 'customer_id')
364 ->tag('References', 'customer')
366 $self->tag('ForeignKey', sub { $_[0]
367 ->tag('Name', 'vendor_id')
368 ->tag('References', 'vendor')
370 $self->tag('ForeignKey', sub { $_[0]
372 ->tag('References', 'chart')
373 }) for qw(debit_accno credit_accno tax_accno);
376 sub do_datev_csv_export {
379 my $datev = SL::DATEV->new(from => $self->from, to => $self->to);
381 $datev->_get_transactions(from_to => $datev->fromto);
383 for my $transaction (@{ $datev->{DATEV} }) {
384 for my $entry (@{ $transaction }) {
385 $entry->{sortkey} = join '-', map { lc } (DateTime->from_kivitendo($entry->{transdate})->strftime('%Y%m%d'), $entry->{name}, $entry->{reference});
389 my @transactions = sort_by { $_->[0]->{sortkey} } @{ $datev->{DATEV} };
391 my $csv = Text::CSV_XS->new({ binary => 1, eol => "\r\n", sep_char => ",", quote_char => '"' });
393 my ($fh, $filename) = File::Temp::tempfile();
394 binmode($fh, ':utf8');
396 $self->files->{"transactions.csv"} = $filename;
397 push @{ $self->tempfiles }, $filename;
399 for my $transaction (@transactions) {
400 my $is_payment = any { $_->{link} =~ m{A[PR]_paid} } @{ $transaction };
402 my ($soll, $haben) = map { $transaction->[$_] } ($transaction->[0]->{amount} > 0 ? (1, 0) : (0, 1));
403 my $tax = defined($soll->{tax_amount}) ? $soll : defined($haben->{tax_amount}) ? $haben : {};
404 my $amount = defined($soll->{net_amount}) ? $soll : $haben;
405 $haben->{notes} = ($haben->{memo} || $soll->{memo}) if $haben->{memo} || $soll->{memo};
406 $haben->{notes} //= '';
407 $haben->{notes} = SL::HTML::Util->strip($haben->{notes});
410 amount => $::form->format_amount($myconfig, abs($amount->{amount}),5),
411 debit_accno => $soll->{accno},
412 debit_accname => $soll->{accname},
413 credit_accno => $haben->{accno},
414 credit_accname => $haben->{accname},
415 tax => defined $amount->{net_amount} ? $::form->format_amount($myconfig, abs($amount->{amount}) - abs($amount->{net_amount}), 5) : 0,
416 notes => $haben->{notes},
417 (map { ($_ => $tax->{$_}) } qw(taxkey tax_accname tax_accno taxdescription)),
418 (map { ($_ => ($haben->{$_} // $soll->{$_})) } qw(acc_trans_id invnumber name vcnumber transdate itime customer_id vendor_id)),
421 _normalize_cell($_) for values %row; # see CAVEATS
423 $csv->print($fh, [ map { $row{$_} } @datev_columns ]);
426 # and build xml spec for it
430 my ($self, $table) = @_;
432 my $csv = Text::CSV_XS->new({ binary => 1, eol => "\r\n", sep_char => ",", quote_char => '"' });
434 my ($fh, $filename) = File::Temp::tempfile();
435 binmode($fh, ':utf8');
437 $self->files->{"$table.csv"} = $filename;
438 push @{ $self->tempfiles }, $filename;
440 # in the right order (primary keys first)
441 my %cols_by_primary_key = _table_columns($table);
442 my @columns = (@{ $cols_by_primary_key{1} }, @{ $cols_by_primary_key{0} });
443 my %col_index = do { my $i = 0; map {; "$_" => $i++ } @columns };
445 # and normalize date stuff
446 my @select_tokens = map { (ref $_) =~ /Time/ ? $_->name . '::date' : $_->name } @columns;
450 if ($known_tables{$table}{transdate}) {
452 push @where_tokens, "$known_tables{$table}{transdate} >= ?";
453 push @values, $self->from;
456 push @where_tokens, "$known_tables{$table}{transdate} <= ?";
457 push @values, $self->to;
460 if ($known_tables{$table}{tables}) {
461 my ($col, @col_specs) = @{ $known_tables{$table}{tables} };
464 my ($ftable, $fkey) = split /\./, $_;
465 if (!exists $self->export_ids->{$ftable}{$fkey}) {
466 # check if we forgot to keep it
467 if (!grep { $_ eq $fkey } @{ $known_tables{$ftable}{keep} || [] }) {
468 die "unknown table spec '$_' for table $table, did you forget to keep $fkey in $ftable?"
470 # hmm, most likely just an empty set.
471 $self->export_ids->{$ftable}{$fkey} = {};
474 $ids{$_}++ for keys %{ $self->export_ids->{$ftable}{$fkey} };
477 push @where_tokens, "$col IN (@{[ join ',', ('?') x keys %ids ]})";
478 push @values, keys %ids;
480 push @where_tokens, '1=0';
484 my $where_clause = @where_tokens ? 'WHERE ' . join ' AND ', @where_tokens : '';
486 my $query = "SELECT " . join(', ', @select_tokens) . " FROM $table $where_clause";
488 my $sth = $::form->get_standard_dbh->prepare($query);
489 $sth->execute(@values) or die "error executing query $query: " . $sth->errstr;
491 while (my $row = $sth->fetch) {
492 for my $keep_col (@{ $known_tables{$table}{keep} || [] }) {
493 next if !$row->[$col_index{$keep_col}];
494 $self->export_ids->{$table}{$keep_col} ||= {};
495 $self->export_ids->{$table}{$keep_col}{$row->[$col_index{$keep_col}]}++;
497 _normalize_cell($_) for @$row; # see CAVEATS
499 $csv->print($fh, $row) or $csv->error_diag;
505 my ($self, $tag, $content) = @_;
507 $self->writer->startTag($tag);
508 if ('CODE' eq ref $content) {
511 $self->writer->characters($content);
513 $self->writer->endTag;
518 my $gdpdu_version = API_VERSION();
519 my $kivi_version = $::form->read_version;
520 my $person = $::myconfig{name};
521 my $contact = join ', ',
522 (t8("Email") . ": $::myconfig{email}" ) x!! $::myconfig{email},
523 (t8("Tel") . ": $::myconfig{tel}" ) x!! $::myconfig{tel},
524 (t8("Fax") . ": $::myconfig{fax}" ) x!! $::myconfig{fax};
526 t8('DataSet for GDPdU version #1. Created with kivitendo #2 by #3 (#4)',
527 $gdpdu_version, $kivi_version, $person, $contact
535 sub client_location {
542 my %given = map { $_ => 1 } @{ $self->tables };
544 grep { $given{$_} } @export_table_order;
548 my ($self, $yesno) = @_;
550 $self->tables(\@export_table_order) if $yesno;
553 sub _normalize_cell {
558 sub init_files { +{} }
559 sub init_export_ids { +{} }
560 sub init_tempfiles { [] }
561 sub init_tables { [ grep { $known_tables{$_} } @export_table_order ] }
564 DateTime->new(year => 2002, month => 8, day => 14)->to_kivitendo;
568 unlink $_ for @{ $_[0]->tempfiles || [] };
579 SL::GDPDU - IDEA export generator
587 Create new export object. C<PARAMS> may contain:
593 The name of the company, needed for the supplier header
597 Location of the company, needed for the supplier header
603 Will only include records in the specified date range. Data pulled from other
604 tables will be culled to match what is needed for these records.
608 Ooptional list of tables to be exported. Defaults to all tables.
612 Optional alternative to C<tables>, forces all known tables.
616 =item C<generate_export>
618 Do the work. Will return an absolute path to a temp file where all export files
625 Sigh. There are a lot of issues with the IDEA software that were found out by
628 =head2 Problems in the Specification
634 The specced date format is capable of only C<YY>, C<YYYY>, C<MM>,
635 and C<DD>. There are no timestamps or timezones.
639 Numbers have the same issue. There is not dedicated integer type, and hinting
640 at an integer type by setting accuracy to 0 generates a warning for redundant
643 Also the number parsing is documented to be fragile. Official docs state that
644 behaviour for too low C<Accuracy> settings is undefined.
648 Foreign key definition is broken. Instead of giving column maps it assumes that
649 foreign keys map to the primary keys given for the target table, and in that
650 order. Also the target table must be known in full before defining a foreign key.
652 As a consequence any additional keys apart from primary keys are not possible.
653 Self-referencing tables are also not possible.
657 The spec does not support splitting data sets into smaller chunks. For data
658 sets that exceed 700MB the spec helpfully suggests: "Use a bigger medium, such
663 It is not possible to set an empty C<DigitGroupingSymbol> since then the import
664 will just work with the default. This was asked in their forum, and the
665 response actually was to use a bogus grouping symbol that is not used:
667 Einfache Lösung: Definieren Sie das Tausendertrennzeichen als Komma, auch
668 wenn es nicht verwendet wird. Sollten Sie das Komma bereits als Feldtrenner
669 verwenden, so wählen Sie als Tausendertrennzeichen eine Alternative wie das
672 L<http://www.gdpdu-portal.com/forum/index.php?mode=thread&id=1392>
676 It is not possible to define a C<RecordDelimiter> with XML entities. 

677 generates the error message:
679 C<RecordDelimiter>-Wert (
) sollte immer aus ein oder zwei Zeichen
682 Instead we just use the implicit default RecordDelimiter CRLF.
686 =head2 Bugs in the IDEA software
692 The CSV import library used in IDEA is not able to parse newlines (or more
693 exactly RecordDelimiter) in data. So this export substites all of these with
698 Neither it is able to parse escaped C<ColumnDelimiter> in data. It just splits
699 on that symbol no matter what surrounds or preceeds it.
703 =head2 Problems outside of the software
709 The law states that "all business related data" should be made available. In
710 practice there's no definition for what makes data "business related", and
711 different auditors seems to want different data.
713 Currently we export most of the transactional data with supplementing
714 customers, vendors and chart of accounts.
718 While the standard explicitely state to provide data normalized, in practice
719 autditors aren't trained database operators and can not create complex vies on
720 normalized data on their own. The reason this works for other software is, that
721 DATEV and SAP seem to have written import plugins for their internal formats in
724 So what is really exported is not unlike a DATEV export. Each transaction gets
725 splitted into chunks of 2 positions (3 with tax on one side). Those get
726 denormalized into a single data row with credfit/debit/tax fields. The charts
727 get denormalized into it as well, in addition to their account number serving
730 Customers and vendors get denormalized into this as well, but are linked by ids
731 to their tables. And the reason for this is...
735 Some auditors do not have a full license of the IDEA software, and
736 can't do table joins.
742 Sven Schöling E<lt>s.schoeling@linet-services.deE<gt>