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' },
37 customer => { name => t8('Customers'), description => t8('Customer Master Data'), },
38 vendor => { name => t8('Vendors'), description => t8('Vendor Master Data'), },
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 taxkey => { type => 'Rose::DB::Object::Metadata::Column::Integer', text => t8('Taxkey'), },
53 tax_accname => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Tax Account Name'), },
54 tax_accno => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Tax Account'), },
55 transdate => { type => 'Rose::DB::Object::Metadata::Column::Date', text => t8('Invoice Date'), },
56 vcnumber => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Customer/Vendor Number'), },
57 customer_id => { type => 'Rose::DB::Object::Metadata::Column::Integer', text => t8('Customer ID'), },
58 vendor_id => { type => 'Rose::DB::Object::Metadata::Column::Integer', text => t8('Vendor ID'), },
61 my @datev_columns = qw(
65 transdate invnumber amount
66 debit_accno debit_accname
67 credit_accno credit_accname
69 tax_accno tax_accname taxkey
73 # rows in this listing are tiers.
74 # tables may depend on ids in a tier above them
75 my @export_table_order = qw(
76 ar ap gl oe delivery_orders
77 invoice orderitems delivery_order_items
84 # needed because the standard dbh sets datestyle german and we don't want to mess with that
85 my $date_format = 'DD.MM.YYYY';
87 # callbacks that produce the xml spec for these column types
89 'Rose::DB::Object::Metadata::Column::Integer' => sub { $_[0]->tag('Numeric') }, # see Caveats for integer issues
90 'Rose::DB::Object::Metadata::Column::BigInt' => sub { $_[0]->tag('Numeric') }, # see Caveats for integer issues
91 'Rose::DB::Object::Metadata::Column::Text' => sub { $_[0]->tag('AlphaNumeric') },
92 'Rose::DB::Object::Metadata::Column::Varchar' => sub { $_[0]->tag('AlphaNumeric') },
93 'Rose::DB::Object::Metadata::Column::Character' => sub { $_[0]->tag('AlphaNumeric') },
94 'Rose::DB::Object::Metadata::Column::Numeric' => sub { $_[0]->tag('Numeric', sub { $_[0]->tag('Accuracy', 5) }) },
95 'Rose::DB::Object::Metadata::Column::Date' => sub { $_[0]->tag('Date', sub { $_[0]->tag('Format', $date_format) }) },
96 'Rose::DB::Object::Metadata::Column::Timestamp' => sub { $_[0]->tag('Date', sub { $_[0]->tag('Format', $date_format) }) },
97 'Rose::DB::Object::Metadata::Column::Float' => sub { $_[0]->tag('Numeric') },
98 'Rose::DB::Object::Metadata::Column::Boolean' => sub { $_[0]
100 ->tag('Map', sub { $_[0]
102 ->tag('To', t8('true'))
104 ->tag('Map', sub { $_[0]
106 ->tag('To', t8('false'))
108 ->tag('Map', sub { $_[0]
110 ->tag('To', t8('false'))
115 sub generate_export {
119 $self->from && 'DateTime' eq ref $self->from or die 'need from date';
120 $self->to && 'DateTime' eq ref $self->to or die 'need to date';
121 $self->from <= $self->to or die 'from date must be earlier or equal than to date';
122 $self->tables && @{ $self->tables } or die 'need tables';
123 for (@{ $self->tables }) {
124 next if $known_tables{$_};
125 die "unknown table '$_'";
128 # get data from those tables and save to csv
129 # for that we need to build queries that fetch all the columns
130 for ($self->sorted_tables) {
131 $self->do_csv_export($_);
134 $self->do_datev_csv_export;
140 $self->files->{'gdpdu-01-08-2002.dtd'} = File::Spec->catfile('users', 'gdpdu-01-08-2002.dtd');
143 my ($fh, $zipfile) = File::Temp::tempfile();
144 my $zip = Archive::Zip->new;
146 while (my ($name, $file) = each %{ $self->files }) {
147 $zip->addFile($file, $name);
150 $zip->writeToFileHandle($fh) == Archive::Zip::AZ_OK() or die 'error writing zip file';
159 my ($fh, $filename) = File::Temp::tempfile();
160 binmode($fh, ':utf8');
162 $self->files->{'INDEX.XML'} = $filename;
163 push @{ $self->tempfiles }, $filename;
165 my $writer = XML::Writer->new(
170 $self->writer($writer);
171 $self->writer->xmlDecl('UTF-8');
172 $self->writer->doctype('DataSet', undef, "gdpdu-01-08-2002.dtd");
173 $self->tag('DataSet', sub { $self
174 ->tag('Version', '1.0')
175 ->tag('DataSupplier', sub { $self
176 ->tag('Name', $self->client_name)
177 ->tag('Location', $self->client_location)
178 ->tag('Comment', $self->make_comment)
180 ->tag('Media', sub { $self
181 ->tag('Name', t8('DataSet #1', 1));
182 for (reverse $self->sorted_tables) { $self # see CAVEATS for table order
185 $self->do_datev_xml_table;
192 my ($self, $table) = @_;
193 my $writer = $self->writer;
195 $self->tag('Table', sub { $self
196 ->tag('URL', "$table.csv")
197 ->tag('Name', $known_tables{$table}{name})
198 ->tag('Description', $known_tables{$table}{description})
199 ->tag('Validity', sub { $self
200 ->tag('Range', sub { $self
201 ->tag('From', $self->from->to_kivitendo(dateformat => 'dd.mm.yyyy'))
202 ->tag('To', $self->to->to_kivitendo(dateformat => 'dd.mm.yyyy'))
204 ->tag('Format', $date_format)
207 ->tag('DecimalSymbol', '.')
208 ->tag('DigitGroupingSymbol', '|') # see CAVEATS in documentation
209 ->tag('VariableLength', sub { $self
210 ->tag('ColumnDelimiter', ',') # see CAVEATS for missing RecordDelimiter
211 ->tag('TextEncapsulator', '"')
213 ->foreign_keys($table)
220 my $package = SL::DB::Helper::Mappings::get_package_for_table($table);
222 # PrimaryKeys must come before regular columns, so partition first
224 $known_tables{$table}{primary_key}
225 ? 1 * ($_ eq $known_tables{$table}{primary_key})
226 : 1 * $_->is_primary_key_member
227 } $package->meta->columns;
231 my ($self, $table) = @_;
233 my %cols_by_primary_key = _table_columns($table);
235 for my $column (@{ $cols_by_primary_key{1} }) {
236 my $type = $column_types{ ref $column };
238 die "unknown col type @{[ ref $column ]}" unless $type;
240 $self->tag('VariablePrimaryKey', sub { $self
241 ->tag('Name', $column->name);
246 for my $column (@{ $cols_by_primary_key{0} }) {
247 my $type = $column_types{ ref $column };
249 die "unknown col type @{[ ref $column]}" unless $type;
251 $self->tag('VariableColumn', sub { $self
252 ->tag('Name', $column->name);
261 my ($self, $table) = @_;
262 my $package = SL::DB::Helper::Mappings::get_package_for_table($table);
264 my %requested = map { $_ => 1 } @{ $self->tables };
266 for my $rel ($package->meta->foreign_keys) {
267 next unless $requested{ $rel->class->meta->table };
269 # ok, now extract the columns used as foreign key
270 my %key_columns = $rel->key_columns;
272 if (1 != keys %key_columns) {
273 die "multi keys? we don't support this currently. fix it please";
276 if ($table eq $rel->class->meta->table) {
277 # self referential foreign keys are a PITA to export correctly. skip!
281 $self->tag('ForeignKey', sub {
282 $_[0]->tag('Name', $_) for keys %key_columns;
283 $_[0]->tag('References', $rel->class->meta->table);
288 sub do_datev_xml_table {
290 my $writer = $self->writer;
292 $self->tag('Table', sub { $self
293 ->tag('URL', "transaction.csv")
294 ->tag('Name', t8('Transactions'))
295 ->tag('Description', t8('Transactions'))
296 ->tag('Validity', sub { $self
297 ->tag('Range', sub { $self
298 ->tag('From', $self->from->to_kivitendo(dateformat => 'dd.mm.yyyy'))
299 ->tag('To', $self->to->to_kivitendo(dateformat => 'dd.mm.yyyy'))
301 ->tag('Format', $date_format)
304 ->tag('DecimalSymbol', '.')
305 ->tag('DigitGroupingSymbol', '|') # see CAVEATS in documentation
306 ->tag('VariableLength', sub { $self
307 ->tag('ColumnDelimiter', ',') # see CAVEATS for missing RecordDelimiter
308 ->tag('TextEncapsulator', '"')
316 my ($self, $table) = @_;
318 my %cols_by_primary_key = partition_by { $datev_column_defs{$_}{primary_key} } @datev_columns;
319 $::lxdebug->dump(0, "cols", \%cols_by_primary_key);
321 for my $column (@{ $cols_by_primary_key{1} }) {
322 my $type = $column_types{ $datev_column_defs{$column}{type} };
324 die "unknown col type @{[ $column ]}" unless $type;
326 $self->tag('VariablePrimaryKey', sub { $self
327 ->tag('Name', $column);
332 for my $column (@{ $cols_by_primary_key{''} }) {
333 my $type = $column_types{ $datev_column_defs{$column}{type} };
335 die "unknown col type @{[ ref $column]}" unless $type;
337 $self->tag('VariableColumn', sub { $self
338 ->tag('Name', $column);
346 sub datev_foreign_keys {
349 $self->tag('ForeignKey', sub { $_[0]
350 ->tag('Name', 'customer_id')
351 ->tag('References', 'customer')
353 $self->tag('ForeignKey', sub { $_[0]
354 ->tag('Name', 'vendor_id')
355 ->tag('References', 'vendor')
357 $self->tag('ForeignKey', sub { $_[0]
359 ->tag('References', 'chart')
360 }) for qw(debit_accno credit_accno tax_accno);
363 sub do_datev_csv_export {
366 my $datev = SL::DATEV->new(from => $self->from, to => $self->to);
368 $datev->_get_transactions(from_to => $datev->fromto);
370 for my $transaction (@{ $datev->{DATEV} }) {
371 for my $entry (@{ $transaction }) {
372 $entry->{sortkey} = join '-', map { lc } (DateTime->from_kivitendo($entry->{transdate})->strftime('%Y%m%d'), $entry->{name}, $entry->{reference});
376 my @transactions = sort_by { $_->[0]->{sortkey} } @{ $datev->{DATEV} };
378 my $csv = Text::CSV_XS->new({
384 my ($fh, $filename) = File::Temp::tempfile();
385 binmode($fh, ':utf8');
387 $self->files->{"transactions.csv"} = $filename;
388 push @{ $self->tempfiles }, $filename;
390 for my $transaction (@transactions) {
391 my $is_payment = any { $_->{link} =~ m{A[PR]_paid} } @{ $transaction };
393 my ($soll, $haben) = map { $transaction->[$_] } ($transaction->[0]->{amount} > 0 ? (1, 0) : (0, 1));
394 my $tax = defined($soll->{tax_accno}) ? $soll : $haben;
395 my $amount = defined($soll->{net_amount}) ? $soll : $haben;
396 $haben->{notes} = ($haben->{memo} || $soll->{memo}) if $haben->{memo} || $soll->{memo};
397 $haben->{notes} //= '';
398 $haben->{notes} = SL::HTML::Util->strip($haben->{notes});
399 $haben->{notes} =~ s{\r}{}g;
400 $haben->{notes} =~ s{\n+}{ }g;
403 customer_id => $soll->{customer_id} || $haben->{customer_id},
404 vendor_id => $soll->{vendor_id} || $haben->{vendor_id},
405 amount => abs($amount->{amount}),
406 debit_accno => $soll->{accno},
407 debit_accname => $soll->{accname},
408 credit_accno => $haben->{accno},
409 credit_accname => $haben->{accname},
410 tax => abs($amount->{amount}) - abs($amount->{net_amount}),
411 notes => $haben->{notes},
412 (map { ($_ => $tax->{$_}) } qw(taxkey tax_accname tax_accno)),
413 (map { ($_ => ($haben->{$_} // $soll->{$_})) } qw(acc_trans_id invnumber name vcnumber transdate)),
416 $csv->print($fh, [ map { $row{$_} } @datev_columns ]);
419 # and build xml spec for it
423 my ($self, $table) = @_;
425 my $csv = Text::CSV_XS->new({ binary => 1, eol => "\r\n", sep_char => ",", quote_char => '"' });
427 my ($fh, $filename) = File::Temp::tempfile();
428 binmode($fh, ':utf8');
430 $self->files->{"$table.csv"} = $filename;
431 push @{ $self->tempfiles }, $filename;
433 # in the right order (primary keys first)
434 my %cols_by_primary_key = _table_columns($table);
435 my @columns = (@{ $cols_by_primary_key{1} }, @{ $cols_by_primary_key{0} });
436 my %col_index = do { my $i = 0; map {; "$_" => $i++ } @columns };
438 # and normalize date stuff
439 my @select_tokens = map { (ref $_) =~ /Time/ ? $_->name . '::date' : $_->name } @columns;
443 if ($known_tables{$table}{transdate}) {
445 push @where_tokens, "$known_tables{$table}{transdate} >= ?";
446 push @values, $self->from;
449 push @where_tokens, "$known_tables{$table}{transdate} <= ?";
450 push @values, $self->to;
453 if ($known_tables{$table}{tables}) {
454 my ($col, @col_specs) = @{ $known_tables{$table}{tables} };
457 my ($ftable, $fkey) = split /\./, $_;
458 if (!exists $self->export_ids->{$ftable}{$fkey}) {
459 # check if we forgot to keep it
460 if (!grep { $_ eq $fkey } @{ $known_tables{$ftable}{keep} || [] }) {
461 die "unknown table spec '$_' for table $table, did you forget to keep $fkey in $ftable?"
463 # hmm, most likely just an empty set.
464 $self->export_ids->{$ftable}{$fkey} = {};
467 $ids{$_}++ for keys %{ $self->export_ids->{$ftable}{$fkey} };
470 push @where_tokens, "$col IN (@{[ join ',', ('?') x keys %ids ]})";
471 push @values, keys %ids;
473 push @where_tokens, '1=0';
477 my $where_clause = @where_tokens ? 'WHERE ' . join ' AND ', @where_tokens : '';
479 my $query = "SELECT " . join(', ', @select_tokens) . " FROM $table $where_clause";
481 my $sth = $::form->get_standard_dbh->prepare($query);
482 $sth->execute(@values) or die "error executing query $query: " . $sth->errstr;
484 while (my $row = $sth->fetch) {
485 for my $keep_col (@{ $known_tables{$table}{keep} || [] }) {
486 next if !$row->[$col_index{$keep_col}];
487 $self->export_ids->{$table}{$keep_col} ||= {};
488 $self->export_ids->{$table}{$keep_col}{$row->[$col_index{$keep_col}]}++;
490 s/\r\n/ /g for @$row; # see CAVEATS
492 $csv->print($fh, $row) or $csv->error_diag;
498 my ($self, $tag, $content) = @_;
500 $self->writer->startTag($tag);
501 if ('CODE' eq ref $content) {
504 $self->writer->characters($content);
506 $self->writer->endTag;
511 my $gdpdu_version = API_VERSION();
512 my $kivi_version = $::form->read_version;
513 my $person = $::myconfig{name};
514 my $contact = join ', ',
515 (t8("Email") . ": $::myconfig{email}" ) x!! $::myconfig{email},
516 (t8("Tel") . ": $::myconfig{tel}" ) x!! $::myconfig{tel},
517 (t8("Fax") . ": $::myconfig{fax}" ) x!! $::myconfig{fax};
519 t8('DataSet for GDPdU version #1. Created with kivitendo #2 by #3 (#4)',
520 $gdpdu_version, $kivi_version, $person, $contact
528 sub client_location {
535 my %given = map { $_ => 1 } @{ $self->tables };
537 grep { $given{$_} } @export_table_order;
541 my ($self, $yesno) = @_;
543 $self->tables(\@export_table_order) if $yesno;
546 sub init_files { +{} }
547 sub init_export_ids { +{} }
548 sub init_tempfiles { [] }
549 sub init_tables { [ grep { $known_tables{$_} } @export_table_order ] }
552 DateTime->new(year => 2002, month => 8, day => 14)->to_kivitendo;
556 unlink $_ for @{ $_[0]->tempfiles || [] };
567 SL::GDPDU - IDEA export generator
575 Create new export object. C<PARAMS> may contain:
581 The name of the company, needed for the supplier header
585 Location of the company, needed for the suupplier header
591 Will only include records in the specified date range. Data pulled from other
592 tables will be culled to match what is needed for these records.
596 A list of tables to be exported.
600 Alternative to C<tables>, enables all known tables.
604 =item C<generate_export>
606 Do the work. Will return an absolut path to a temp file where all export files
617 Date format is shit. The official docs state that only C<YY>, C<YYYY>, C<MM>,
618 and C<DD> are supported, timestamps do not exist.
622 Number parsing seems to be fragile. Official docs state that behaviour for too
623 low C<Accuracy> settings is undefined. Accuracy of 0 is not taken to mean
624 Integer but instead generates a warning for redudancy.
626 There is no dedicated integer type.
630 Currently C<ar> and C<ap> have a foreign key to themself with the name
631 C<storno_id>. If this foreign key is present in the C<INDEX.XML> then the
632 storno records have to be too. Since this is extremely awkward to code and
633 confusing for the examiner as to why there are records outside of the time
634 range, this export skips all self-referential foreign keys.
638 Documentation for foreign keys is extremely weird. Instead of giving column
639 maps it assumes that foreign keys map to the primary keys given for the target
640 table, and in that order. Foreign keys to keys that are not primary seems to be
641 impossible. Changing type is also not allowed (which actually makes sense).
642 Hopefully there are no bugs there.
646 It's currently disallowed to export the whole dataset. It's not clear if this
651 It is not possible to set an empty C<DigiGroupingSymbol> since then the import
652 will just work with the default. This was asked in their forum, and the
653 response actually was:
655 Einfache Lösung: Definieren Sie das Tausendertrennzeichen als Komma, auch
656 wenn es nicht verwendet wird. Sollten Sie das Komma bereits als Feldtrenner
657 verwenden, so wählen Sie als Tausendertrennzeichen eine Alternative wie das
660 L<http://www.gdpdu-portal.com/forum/index.php?mode=thread&id=1392>
664 It is not possible to define a C<RecordDelimiter> with XML entities. 

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