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)'), },
62 my @datev_columns = qw(
66 transdate invnumber amount
67 debit_accno debit_accname
68 credit_accno credit_accname
70 tax_accno tax_accname taxkey
74 # rows in this listing are tiers.
75 # tables may depend on ids in a tier above them
76 my @export_table_order = qw(
77 ar ap gl oe delivery_orders
78 invoice orderitems delivery_order_items
85 # needed because the standard dbh sets datestyle german and we don't want to mess with that
86 my $date_format = 'DD.MM.YYYY';
88 # callbacks that produce the xml spec for these column types
90 'Rose::DB::Object::Metadata::Column::Integer' => sub { $_[0]->tag('Numeric') }, # see Caveats for integer issues
91 'Rose::DB::Object::Metadata::Column::BigInt' => sub { $_[0]->tag('Numeric') }, # see Caveats for integer issues
92 'Rose::DB::Object::Metadata::Column::Text' => sub { $_[0]->tag('AlphaNumeric') },
93 'Rose::DB::Object::Metadata::Column::Varchar' => sub { $_[0]->tag('AlphaNumeric') },
94 'Rose::DB::Object::Metadata::Column::Character' => sub { $_[0]->tag('AlphaNumeric') },
95 'Rose::DB::Object::Metadata::Column::Numeric' => sub { $_[0]->tag('Numeric', sub { $_[0]->tag('Accuracy', 5) }) },
96 'Rose::DB::Object::Metadata::Column::Date' => sub { $_[0]->tag('Date', sub { $_[0]->tag('Format', $date_format) }) },
97 'Rose::DB::Object::Metadata::Column::Timestamp' => sub { $_[0]->tag('Date', sub { $_[0]->tag('Format', $date_format) }) },
98 'Rose::DB::Object::Metadata::Column::Float' => sub { $_[0]->tag('Numeric') },
99 'Rose::DB::Object::Metadata::Column::Boolean' => sub { $_[0]
100 ->tag('AlphaNumeric')
101 ->tag('Map', sub { $_[0]
103 ->tag('To', t8('true'))
105 ->tag('Map', sub { $_[0]
107 ->tag('To', t8('false'))
109 ->tag('Map', sub { $_[0]
111 ->tag('To', t8('false'))
116 sub generate_export {
120 $self->from && 'DateTime' eq ref $self->from or die 'need from date';
121 $self->to && 'DateTime' eq ref $self->to or die 'need to date';
122 $self->from <= $self->to or die 'from date must be earlier or equal than to date';
123 $self->tables && @{ $self->tables } or die 'need tables';
124 for (@{ $self->tables }) {
125 next if $known_tables{$_};
126 die "unknown table '$_'";
129 # get data from those tables and save to csv
130 # for that we need to build queries that fetch all the columns
131 for ($self->sorted_tables) {
132 $self->do_csv_export($_);
135 $self->do_datev_csv_export;
141 $self->files->{'gdpdu-01-08-2002.dtd'} = File::Spec->catfile('users', 'gdpdu-01-08-2002.dtd');
144 my ($fh, $zipfile) = File::Temp::tempfile();
145 my $zip = Archive::Zip->new;
147 while (my ($name, $file) = each %{ $self->files }) {
148 $zip->addFile($file, $name);
151 $zip->writeToFileHandle($fh) == Archive::Zip::AZ_OK() or die 'error writing zip file';
160 my ($fh, $filename) = File::Temp::tempfile();
161 binmode($fh, ':utf8');
163 $self->files->{'INDEX.XML'} = $filename;
164 push @{ $self->tempfiles }, $filename;
166 my $writer = XML::Writer->new(
171 $self->writer($writer);
172 $self->writer->xmlDecl('UTF-8');
173 $self->writer->doctype('DataSet', undef, "gdpdu-01-08-2002.dtd");
174 $self->tag('DataSet', sub { $self
175 ->tag('Version', '1.0')
176 ->tag('DataSupplier', sub { $self
177 ->tag('Name', $self->client_name)
178 ->tag('Location', $self->client_location)
179 ->tag('Comment', $self->make_comment)
181 ->tag('Media', sub { $self
182 ->tag('Name', t8('DataSet #1', 1));
183 for (reverse $self->sorted_tables) { $self # see CAVEATS for table order
186 $self->do_datev_xml_table;
193 my ($self, $table) = @_;
194 my $writer = $self->writer;
196 $self->tag('Table', sub { $self
197 ->tag('URL', "$table.csv")
198 ->tag('Name', $known_tables{$table}{name})
199 ->tag('Description', $known_tables{$table}{description})
200 ->tag('Validity', sub { $self
201 ->tag('Range', sub { $self
202 ->tag('From', $self->from->to_kivitendo(dateformat => 'dd.mm.yyyy'))
203 ->tag('To', $self->to->to_kivitendo(dateformat => 'dd.mm.yyyy'))
205 ->tag('Format', $date_format)
208 ->tag('DecimalSymbol', '.')
209 ->tag('DigitGroupingSymbol', '|') # see CAVEATS in documentation
210 ->tag('VariableLength', sub { $self
211 ->tag('ColumnDelimiter', ',') # see CAVEATS for missing RecordDelimiter
212 ->tag('TextEncapsulator', '"')
214 ->foreign_keys($table)
221 my $package = SL::DB::Helper::Mappings::get_package_for_table($table);
224 my $use_white_list = 0;
225 if ($known_tables{$table}{columns}) {
227 $white_list{$_} = 1 for @{ $known_tables{$table}{columns} || [] };
230 # PrimaryKeys must come before regular columns, so partition first
232 $known_tables{$table}{primary_key}
233 ? 1 * ($_ eq $known_tables{$table}{primary_key})
234 : 1 * $_->is_primary_key_member
236 $use_white_list ? $white_list{$_->name} : 1
237 } $package->meta->columns;
241 my ($self, $table) = @_;
243 my %cols_by_primary_key = _table_columns($table);
245 for my $column (@{ $cols_by_primary_key{1} }) {
246 my $type = $column_types{ ref $column };
248 die "unknown col type @{[ ref $column ]}" unless $type;
250 $self->tag('VariablePrimaryKey', sub { $self
251 ->tag('Name', $column->name);
256 for my $column (@{ $cols_by_primary_key{0} }) {
257 my $type = $column_types{ ref $column };
259 die "unknown col type @{[ ref $column]}" unless $type;
261 $self->tag('VariableColumn', sub { $self
262 ->tag('Name', $column->name);
271 my ($self, $table) = @_;
272 my $package = SL::DB::Helper::Mappings::get_package_for_table($table);
274 my %requested = map { $_ => 1 } @{ $self->tables };
276 for my $rel ($package->meta->foreign_keys) {
277 next unless $requested{ $rel->class->meta->table };
279 # ok, now extract the columns used as foreign key
280 my %key_columns = $rel->key_columns;
282 if (1 != keys %key_columns) {
283 die "multi keys? we don't support this currently. fix it please";
286 if ($table eq $rel->class->meta->table) {
287 # self referential foreign keys are a PITA to export correctly. skip!
291 $self->tag('ForeignKey', sub {
292 $_[0]->tag('Name', $_) for keys %key_columns;
293 $_[0]->tag('References', $rel->class->meta->table);
298 sub do_datev_xml_table {
300 my $writer = $self->writer;
302 $self->tag('Table', sub { $self
303 ->tag('URL', "transaction.csv")
304 ->tag('Name', t8('Transactions'))
305 ->tag('Description', t8('Transactions'))
306 ->tag('Validity', sub { $self
307 ->tag('Range', sub { $self
308 ->tag('From', $self->from->to_kivitendo(dateformat => 'dd.mm.yyyy'))
309 ->tag('To', $self->to->to_kivitendo(dateformat => 'dd.mm.yyyy'))
311 ->tag('Format', $date_format)
314 ->tag('DecimalSymbol', '.')
315 ->tag('DigitGroupingSymbol', '|') # see CAVEATS in documentation
316 ->tag('VariableLength', sub { $self
317 ->tag('ColumnDelimiter', ',') # see CAVEATS for missing RecordDelimiter
318 ->tag('TextEncapsulator', '"')
326 my ($self, $table) = @_;
328 my %cols_by_primary_key = partition_by { 1 * $datev_column_defs{$_}{primary_key} } @datev_columns;
329 $::lxdebug->dump(0, "cols", \%cols_by_primary_key);
331 for my $column (@{ $cols_by_primary_key{1} }) {
332 my $type = $column_types{ $datev_column_defs{$column}{type} };
334 die "unknown col type @{[ $column ]}" unless $type;
336 $self->tag('VariablePrimaryKey', sub { $self
337 ->tag('Name', $column);
342 for my $column (@{ $cols_by_primary_key{0} }) {
343 my $type = $column_types{ $datev_column_defs{$column}{type} };
345 die "unknown col type @{[ ref $column]}" unless $type;
347 $self->tag('VariableColumn', sub { $self
348 ->tag('Name', $column);
356 sub datev_foreign_keys {
359 $self->tag('ForeignKey', sub { $_[0]
360 ->tag('Name', 'customer_id')
361 ->tag('References', 'customer')
363 $self->tag('ForeignKey', sub { $_[0]
364 ->tag('Name', 'vendor_id')
365 ->tag('References', 'vendor')
367 $self->tag('ForeignKey', sub { $_[0]
369 ->tag('References', 'chart')
370 }) for qw(debit_accno credit_accno tax_accno);
373 sub do_datev_csv_export {
376 my $datev = SL::DATEV->new(from => $self->from, to => $self->to);
378 $datev->_get_transactions(from_to => $datev->fromto);
380 for my $transaction (@{ $datev->{DATEV} }) {
381 for my $entry (@{ $transaction }) {
382 $entry->{sortkey} = join '-', map { lc } (DateTime->from_kivitendo($entry->{transdate})->strftime('%Y%m%d'), $entry->{name}, $entry->{reference});
386 my @transactions = sort_by { $_->[0]->{sortkey} } @{ $datev->{DATEV} };
388 my $csv = Text::CSV_XS->new({ binary => 1, eol => "\r\n", sep_char => ",", quote_char => '"' });
390 my ($fh, $filename) = File::Temp::tempfile();
391 binmode($fh, ':utf8');
393 $self->files->{"transactions.csv"} = $filename;
394 push @{ $self->tempfiles }, $filename;
396 for my $transaction (@transactions) {
397 my $is_payment = any { $_->{link} =~ m{A[PR]_paid} } @{ $transaction };
399 my ($soll, $haben) = map { $transaction->[$_] } ($transaction->[0]->{amount} > 0 ? (1, 0) : (0, 1));
400 my $tax = defined($soll->{tax_accno}) ? $soll : $haben;
401 my $amount = defined($soll->{net_amount}) ? $soll : $haben;
402 $haben->{notes} = ($haben->{memo} || $soll->{memo}) if $haben->{memo} || $soll->{memo};
403 $haben->{notes} //= '';
404 $haben->{notes} = SL::HTML::Util->strip($haben->{notes});
405 $haben->{notes} =~ s{\r}{}g;
406 $haben->{notes} =~ s{\n+}{ }g;
409 customer_id => $soll->{customer_id} || $haben->{customer_id},
410 vendor_id => $soll->{vendor_id} || $haben->{vendor_id},
411 amount => abs($amount->{amount}),
412 debit_accno => $soll->{accno},
413 debit_accname => $soll->{accname},
414 credit_accno => $haben->{accno},
415 credit_accname => $haben->{accname},
416 tax => defined $amount->{net_amount} ? abs($amount->{amount}) - abs($amount->{net_amount}) : 0,
417 taxdescription => defined($soll->{tax_accno}) ? $soll->{taxdescription} : $haben->{taxdescription},
418 notes => $haben->{notes},
419 (map { ($_ => $tax->{$_}) } qw(taxkey tax_accname tax_accno)),
420 (map { ($_ => ($haben->{$_} // $soll->{$_})) } qw(acc_trans_id invnumber name vcnumber transdate)),
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 s/\r\n/ /g 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 init_files { +{} }
554 sub init_export_ids { +{} }
555 sub init_tempfiles { [] }
556 sub init_tables { [ grep { $known_tables{$_} } @export_table_order ] }
559 DateTime->new(year => 2002, month => 8, day => 14)->to_kivitendo;
563 unlink $_ for @{ $_[0]->tempfiles || [] };
574 SL::GDPDU - IDEA export generator
582 Create new export object. C<PARAMS> may contain:
588 The name of the company, needed for the supplier header
592 Location of the company, needed for the suupplier header
598 Will only include records in the specified date range. Data pulled from other
599 tables will be culled to match what is needed for these records.
603 A list of tables to be exported.
607 Alternative to C<tables>, enables all known tables.
611 =item C<generate_export>
613 Do the work. Will return an absolut path to a temp file where all export files
624 Date format is shit. The official docs state that only C<YY>, C<YYYY>, C<MM>,
625 and C<DD> are supported, timestamps do not exist.
629 Number parsing seems to be fragile. Official docs state that behaviour for too
630 low C<Accuracy> settings is undefined. Accuracy of 0 is not taken to mean
631 Integer but instead generates a warning for redudancy.
633 There is no dedicated integer type.
637 Currently C<ar> and C<ap> have a foreign key to themself with the name
638 C<storno_id>. If this foreign key is present in the C<INDEX.XML> then the
639 storno records have to be too. Since this is extremely awkward to code and
640 confusing for the examiner as to why there are records outside of the time
641 range, this export skips all self-referential foreign keys.
645 Documentation for foreign keys is extremely weird. Instead of giving column
646 maps it assumes that foreign keys map to the primary keys given for the target
647 table, and in that order. Foreign keys to keys that are not primary seems to be
648 impossible. Changing type is also not allowed (which actually makes sense).
649 Hopefully there are no bugs there.
653 It's currently disallowed to export the whole dataset. It's not clear if this
658 It is not possible to set an empty C<DigiGroupingSymbol> since then the import
659 will just work with the default. This was asked in their forum, and the
660 response actually was:
662 Einfache Lösung: Definieren Sie das Tausendertrennzeichen als Komma, auch
663 wenn es nicht verwendet wird. Sollten Sie das Komma bereits als Feldtrenner
664 verwenden, so wählen Sie als Tausendertrennzeichen eine Alternative wie das
667 L<http://www.gdpdu-portal.com/forum/index.php?mode=thread&id=1392>
671 It is not possible to define a C<RecordDelimiter> with XML entities. 

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