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'), primary_key => 1 },
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';
89 # callbacks that produce the xml spec for these column types
91 'Rose::DB::Object::Metadata::Column::Integer' => sub { $_[0]->tag('Numeric') }, # see Caveats for integer issues
92 'Rose::DB::Object::Metadata::Column::BigInt' => sub { $_[0]->tag('Numeric') }, # see Caveats for integer issues
93 'Rose::DB::Object::Metadata::Column::Text' => sub { $_[0]->tag('AlphaNumeric') },
94 'Rose::DB::Object::Metadata::Column::Varchar' => sub { $_[0]->tag('AlphaNumeric') },
95 'Rose::DB::Object::Metadata::Column::Character' => sub { $_[0]->tag('AlphaNumeric') },
96 'Rose::DB::Object::Metadata::Column::Numeric' => sub { $_[0]->tag('Numeric', sub { $_[0]->tag('Accuracy', 5) }) },
97 'Rose::DB::Object::Metadata::Column::Date' => sub { $_[0]->tag('Date', sub { $_[0]->tag('Format', $date_format) }) },
98 'Rose::DB::Object::Metadata::Column::Timestamp' => sub { $_[0]->tag('Date', sub { $_[0]->tag('Format', $date_format) }) },
99 'Rose::DB::Object::Metadata::Column::Float' => sub { $_[0]->tag('Numeric') },
100 'Rose::DB::Object::Metadata::Column::Boolean' => sub { $_[0]
101 ->tag('AlphaNumeric')
102 ->tag('Map', sub { $_[0]
104 ->tag('To', t8('true'))
106 ->tag('Map', sub { $_[0]
108 ->tag('To', t8('false'))
110 ->tag('Map', sub { $_[0]
112 ->tag('To', t8('false'))
117 sub generate_export {
121 $self->from && 'DateTime' eq ref $self->from or die 'need from date';
122 $self->to && 'DateTime' eq ref $self->to or die 'need to date';
123 $self->from <= $self->to or die 'from date must be earlier or equal than to date';
124 $self->tables && @{ $self->tables } or die 'need tables';
125 for (@{ $self->tables }) {
126 next if $known_tables{$_};
127 die "unknown table '$_'";
130 # get data from those tables and save to csv
131 # for that we need to build queries that fetch all the columns
132 for ($self->sorted_tables) {
133 $self->do_csv_export($_);
136 $self->do_datev_csv_export;
142 $self->files->{'gdpdu-01-08-2002.dtd'} = File::Spec->catfile('users', 'gdpdu-01-08-2002.dtd');
145 my ($fh, $zipfile) = File::Temp::tempfile();
146 my $zip = Archive::Zip->new;
148 while (my ($name, $file) = each %{ $self->files }) {
149 $zip->addFile($file, $name);
152 $zip->writeToFileHandle($fh) == Archive::Zip::AZ_OK() or die 'error writing zip file';
161 my ($fh, $filename) = File::Temp::tempfile();
162 binmode($fh, ':utf8');
164 $self->files->{'INDEX.XML'} = $filename;
165 push @{ $self->tempfiles }, $filename;
167 my $writer = XML::Writer->new(
172 $self->writer($writer);
173 $self->writer->xmlDecl('UTF-8');
174 $self->writer->doctype('DataSet', undef, "gdpdu-01-08-2002.dtd");
175 $self->tag('DataSet', sub { $self
176 ->tag('Version', '1.0')
177 ->tag('DataSupplier', sub { $self
178 ->tag('Name', $self->client_name)
179 ->tag('Location', $self->client_location)
180 ->tag('Comment', $self->make_comment)
182 ->tag('Media', sub { $self
183 ->tag('Name', t8('DataSet #1', 1));
184 for (reverse $self->sorted_tables) { $self # see CAVEATS for table order
187 $self->do_datev_xml_table;
194 my ($self, $table) = @_;
195 my $writer = $self->writer;
197 $self->tag('Table', sub { $self
198 ->tag('URL', "$table.csv")
199 ->tag('Name', $known_tables{$table}{name})
200 ->tag('Description', $known_tables{$table}{description})
201 ->tag('Validity', sub { $self
202 ->tag('Range', sub { $self
203 ->tag('From', $self->from->to_kivitendo(dateformat => 'dd.mm.yyyy'))
204 ->tag('To', $self->to->to_kivitendo(dateformat => 'dd.mm.yyyy'))
206 ->tag('Format', $date_format)
209 ->tag('DecimalSymbol', '.')
210 ->tag('DigitGroupingSymbol', '|') # see CAVEATS in documentation
211 ->tag('VariableLength', sub { $self
212 ->tag('ColumnDelimiter', ',') # see CAVEATS for missing RecordDelimiter
213 ->tag('TextEncapsulator', '"')
215 ->foreign_keys($table)
222 my $package = SL::DB::Helper::Mappings::get_package_for_table($table);
225 my $use_white_list = 0;
226 if ($known_tables{$table}{columns}) {
228 $white_list{$_} = 1 for @{ $known_tables{$table}{columns} || [] };
231 # PrimaryKeys must come before regular columns, so partition first
233 $known_tables{$table}{primary_key}
234 ? 1 * ($_ eq $known_tables{$table}{primary_key})
235 : 1 * $_->is_primary_key_member
237 $use_white_list ? $white_list{$_->name} : 1
238 } $package->meta->columns;
242 my ($self, $table) = @_;
244 my %cols_by_primary_key = _table_columns($table);
246 for my $column (@{ $cols_by_primary_key{1} }) {
247 my $type = $column_types{ ref $column };
249 die "unknown col type @{[ ref $column ]}" unless $type;
251 $self->tag('VariablePrimaryKey', sub { $self
252 ->tag('Name', $column->name);
257 for my $column (@{ $cols_by_primary_key{0} }) {
258 my $type = $column_types{ ref $column };
260 die "unknown col type @{[ ref $column]}" unless $type;
262 $self->tag('VariableColumn', sub { $self
263 ->tag('Name', $column->name);
272 my ($self, $table) = @_;
273 my $package = SL::DB::Helper::Mappings::get_package_for_table($table);
275 my %requested = map { $_ => 1 } @{ $self->tables };
277 for my $rel ($package->meta->foreign_keys) {
278 next unless $requested{ $rel->class->meta->table };
280 # ok, now extract the columns used as foreign key
281 my %key_columns = $rel->key_columns;
283 if (1 != keys %key_columns) {
284 die "multi keys? we don't support this currently. fix it please";
287 if ($table eq $rel->class->meta->table) {
288 # self referential foreign keys are a PITA to export correctly. skip!
292 $self->tag('ForeignKey', sub {
293 $_[0]->tag('Name', $_) for keys %key_columns;
294 $_[0]->tag('References', $rel->class->meta->table);
299 sub do_datev_xml_table {
301 my $writer = $self->writer;
303 $self->tag('Table', sub { $self
304 ->tag('URL', "transaction.csv")
305 ->tag('Name', t8('Transactions'))
306 ->tag('Description', t8('Transactions'))
307 ->tag('Validity', sub { $self
308 ->tag('Range', sub { $self
309 ->tag('From', $self->from->to_kivitendo(dateformat => 'dd.mm.yyyy'))
310 ->tag('To', $self->to->to_kivitendo(dateformat => 'dd.mm.yyyy'))
312 ->tag('Format', $date_format)
315 ->tag('DecimalSymbol', '.')
316 ->tag('DigitGroupingSymbol', '|') # see CAVEATS in documentation
317 ->tag('VariableLength', sub { $self
318 ->tag('ColumnDelimiter', ',') # see CAVEATS for missing RecordDelimiter
319 ->tag('TextEncapsulator', '"')
327 my ($self, $table) = @_;
329 my %cols_by_primary_key = partition_by { 1 * $datev_column_defs{$_}{primary_key} } @datev_columns;
330 $::lxdebug->dump(0, "cols", \%cols_by_primary_key);
332 for my $column (@{ $cols_by_primary_key{1} }) {
333 my $type = $column_types{ $datev_column_defs{$column}{type} };
335 die "unknown col type @{[ $column ]}" unless $type;
337 $self->tag('VariablePrimaryKey', sub { $self
338 ->tag('Name', $column);
343 for my $column (@{ $cols_by_primary_key{0} }) {
344 my $type = $column_types{ $datev_column_defs{$column}{type} };
346 die "unknown col type @{[ ref $column]}" unless $type;
348 $self->tag('VariableColumn', sub { $self
349 ->tag('Name', $column);
357 sub datev_foreign_keys {
360 $self->tag('ForeignKey', sub { $_[0]
361 ->tag('Name', 'customer_id')
362 ->tag('References', 'customer')
364 $self->tag('ForeignKey', sub { $_[0]
365 ->tag('Name', 'vendor_id')
366 ->tag('References', 'vendor')
368 $self->tag('ForeignKey', sub { $_[0]
370 ->tag('References', 'chart')
371 }) for qw(debit_accno credit_accno tax_accno);
374 sub do_datev_csv_export {
377 my $datev = SL::DATEV->new(from => $self->from, to => $self->to);
379 $datev->_get_transactions(from_to => $datev->fromto);
381 for my $transaction (@{ $datev->{DATEV} }) {
382 for my $entry (@{ $transaction }) {
383 $entry->{sortkey} = join '-', map { lc } (DateTime->from_kivitendo($entry->{transdate})->strftime('%Y%m%d'), $entry->{name}, $entry->{reference});
387 my @transactions = sort_by { $_->[0]->{sortkey} } @{ $datev->{DATEV} };
389 my $csv = Text::CSV_XS->new({ binary => 1, eol => "\r\n", sep_char => ",", quote_char => '"' });
391 my ($fh, $filename) = File::Temp::tempfile();
392 binmode($fh, ':utf8');
394 $self->files->{"transactions.csv"} = $filename;
395 push @{ $self->tempfiles }, $filename;
397 for my $transaction (@transactions) {
398 my $is_payment = any { $_->{link} =~ m{A[PR]_paid} } @{ $transaction };
400 my ($soll, $haben) = map { $transaction->[$_] } ($transaction->[0]->{amount} > 0 ? (1, 0) : (0, 1));
401 my $tax = defined($soll->{tax_accno}) ? $soll : $haben;
402 my $amount = defined($soll->{net_amount}) ? $soll : $haben;
403 $haben->{notes} = ($haben->{memo} || $soll->{memo}) if $haben->{memo} || $soll->{memo};
404 $haben->{notes} //= '';
405 $haben->{notes} = SL::HTML::Util->strip($haben->{notes});
406 $haben->{notes} =~ s{\r}{}g;
407 $haben->{notes} =~ s{\n+}{ }g;
410 customer_id => $soll->{customer_id} || $haben->{customer_id},
411 vendor_id => $soll->{vendor_id} || $haben->{vendor_id},
412 amount => abs($amount->{amount}),
413 debit_accno => $soll->{accno},
414 debit_accname => $soll->{accname},
415 credit_accno => $haben->{accno},
416 credit_accname => $haben->{accname},
417 tax => defined $amount->{net_amount} ? abs($amount->{amount}) - abs($amount->{net_amount}) : 0,
418 taxdescription => defined($soll->{tax_accno}) ? $soll->{taxdescription} : $haben->{taxdescription},
419 notes => $haben->{notes},
420 itime => $soll->{itime},
421 (map { ($_ => $tax->{$_}) } qw(taxkey tax_accname tax_accno)),
422 (map { ($_ => ($haben->{$_} // $soll->{$_})) } qw(acc_trans_id invnumber name vcnumber transdate)),
425 $csv->print($fh, [ map { $row{$_} } @datev_columns ]);
428 # and build xml spec for it
432 my ($self, $table) = @_;
434 my $csv = Text::CSV_XS->new({ binary => 1, eol => "\r\n", sep_char => ",", quote_char => '"' });
436 my ($fh, $filename) = File::Temp::tempfile();
437 binmode($fh, ':utf8');
439 $self->files->{"$table.csv"} = $filename;
440 push @{ $self->tempfiles }, $filename;
442 # in the right order (primary keys first)
443 my %cols_by_primary_key = _table_columns($table);
444 my @columns = (@{ $cols_by_primary_key{1} }, @{ $cols_by_primary_key{0} });
445 my %col_index = do { my $i = 0; map {; "$_" => $i++ } @columns };
447 # and normalize date stuff
448 my @select_tokens = map { (ref $_) =~ /Time/ ? $_->name . '::date' : $_->name } @columns;
452 if ($known_tables{$table}{transdate}) {
454 push @where_tokens, "$known_tables{$table}{transdate} >= ?";
455 push @values, $self->from;
458 push @where_tokens, "$known_tables{$table}{transdate} <= ?";
459 push @values, $self->to;
462 if ($known_tables{$table}{tables}) {
463 my ($col, @col_specs) = @{ $known_tables{$table}{tables} };
466 my ($ftable, $fkey) = split /\./, $_;
467 if (!exists $self->export_ids->{$ftable}{$fkey}) {
468 # check if we forgot to keep it
469 if (!grep { $_ eq $fkey } @{ $known_tables{$ftable}{keep} || [] }) {
470 die "unknown table spec '$_' for table $table, did you forget to keep $fkey in $ftable?"
472 # hmm, most likely just an empty set.
473 $self->export_ids->{$ftable}{$fkey} = {};
476 $ids{$_}++ for keys %{ $self->export_ids->{$ftable}{$fkey} };
479 push @where_tokens, "$col IN (@{[ join ',', ('?') x keys %ids ]})";
480 push @values, keys %ids;
482 push @where_tokens, '1=0';
486 my $where_clause = @where_tokens ? 'WHERE ' . join ' AND ', @where_tokens : '';
488 my $query = "SELECT " . join(', ', @select_tokens) . " FROM $table $where_clause";
490 my $sth = $::form->get_standard_dbh->prepare($query);
491 $sth->execute(@values) or die "error executing query $query: " . $sth->errstr;
493 while (my $row = $sth->fetch) {
494 for my $keep_col (@{ $known_tables{$table}{keep} || [] }) {
495 next if !$row->[$col_index{$keep_col}];
496 $self->export_ids->{$table}{$keep_col} ||= {};
497 $self->export_ids->{$table}{$keep_col}{$row->[$col_index{$keep_col}]}++;
499 s/\r\n/ /g for @$row; # see CAVEATS
501 $csv->print($fh, $row) or $csv->error_diag;
507 my ($self, $tag, $content) = @_;
509 $self->writer->startTag($tag);
510 if ('CODE' eq ref $content) {
513 $self->writer->characters($content);
515 $self->writer->endTag;
520 my $gdpdu_version = API_VERSION();
521 my $kivi_version = $::form->read_version;
522 my $person = $::myconfig{name};
523 my $contact = join ', ',
524 (t8("Email") . ": $::myconfig{email}" ) x!! $::myconfig{email},
525 (t8("Tel") . ": $::myconfig{tel}" ) x!! $::myconfig{tel},
526 (t8("Fax") . ": $::myconfig{fax}" ) x!! $::myconfig{fax};
528 t8('DataSet for GDPdU version #1. Created with kivitendo #2 by #3 (#4)',
529 $gdpdu_version, $kivi_version, $person, $contact
537 sub client_location {
544 my %given = map { $_ => 1 } @{ $self->tables };
546 grep { $given{$_} } @export_table_order;
550 my ($self, $yesno) = @_;
552 $self->tables(\@export_table_order) if $yesno;
555 sub init_files { +{} }
556 sub init_export_ids { +{} }
557 sub init_tempfiles { [] }
558 sub init_tables { [ grep { $known_tables{$_} } @export_table_order ] }
561 DateTime->new(year => 2002, month => 8, day => 14)->to_kivitendo;
565 unlink $_ for @{ $_[0]->tempfiles || [] };
576 SL::GDPDU - IDEA export generator
584 Create new export object. C<PARAMS> may contain:
590 The name of the company, needed for the supplier header
594 Location of the company, needed for the suupplier header
600 Will only include records in the specified date range. Data pulled from other
601 tables will be culled to match what is needed for these records.
605 A list of tables to be exported.
609 Alternative to C<tables>, enables all known tables.
613 =item C<generate_export>
615 Do the work. Will return an absolut path to a temp file where all export files
626 Date format is shit. The official docs state that only C<YY>, C<YYYY>, C<MM>,
627 and C<DD> are supported, timestamps do not exist.
631 Number parsing seems to be fragile. Official docs state that behaviour for too
632 low C<Accuracy> settings is undefined. Accuracy of 0 is not taken to mean
633 Integer but instead generates a warning for redudancy.
635 There is no dedicated integer type.
639 Currently C<ar> and C<ap> have a foreign key to themself with the name
640 C<storno_id>. If this foreign key is present in the C<INDEX.XML> then the
641 storno records have to be too. Since this is extremely awkward to code and
642 confusing for the examiner as to why there are records outside of the time
643 range, this export skips all self-referential foreign keys.
647 Documentation for foreign keys is extremely weird. Instead of giving column
648 maps it assumes that foreign keys map to the primary keys given for the target
649 table, and in that order. Foreign keys to keys that are not primary seems to be
650 impossible. Changing type is also not allowed (which actually makes sense).
651 Hopefully there are no bugs there.
655 It's currently disallowed to export the whole dataset. It's not clear if this
660 It is not possible to set an empty C<DigiGroupingSymbol> since then the import
661 will just work with the default. This was asked in their forum, and the
662 response actually was:
664 Einfache Lösung: Definieren Sie das Tausendertrennzeichen als Komma, auch
665 wenn es nicht verwendet wird. Sollten Sie das Komma bereits als Feldtrenner
666 verwenden, so wählen Sie als Tausendertrennzeichen eine Alternative wie das
669 L<http://www.gdpdu-portal.com/forum/index.php?mode=thread&id=1392>
673 It is not possible to define a C<RecordDelimiter> with XML entities. 

674 generates the error message:
676 C<RecordDelimiter>-Wert (
) sollte immer aus ein oder zwei Zeichen
679 Instead we just use the implicit default RecordDelimiter CRLF.
685 Foreign keys seem only to work with previously defined tables (which would be
690 The CSV import library used in IDEA is not able to parse newlines (or more
691 exactly RecordDelimiter) in data. So this export substites all of these with
698 Sven Schöling E<lt>s.schoeling@linet-services.deE<gt>