| 22 |
22 |
|
| 23 |
23 |
use Rose::Object::MakeMethods::Generic (
|
| 24 |
24 |
scalar => [ qw(from to writer company location) ],
|
| 25 |
|
'scalar --get_set_init' => [ qw(files tempfiles export_ids tables) ],
|
|
25 |
'scalar --get_set_init' => [ qw(files tempfiles export_ids tables csv_headers) ],
|
| 26 |
26 |
);
|
| 27 |
27 |
|
| 28 |
28 |
# in this we find:
|
| 29 |
29 |
# key: table name
|
| 30 |
30 |
# name: short name, translated
|
| 31 |
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
|
|
32 |
# columns: list of columns to export. export all columns if not present
|
|
33 |
# primary_key: override primary key
|
| 35 |
34 |
my %known_tables = (
|
| 36 |
35 |
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) ] },
|
|
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) ] },
|
| 39 |
38 |
);
|
| 40 |
39 |
|
|
40 |
my %column_titles = (
|
|
41 |
chart => {
|
|
42 |
id => t8('ID'),
|
|
43 |
accno => t8('Account Number'),
|
|
44 |
description => t8('Description'),
|
|
45 |
},
|
|
46 |
customer_vendor => {
|
|
47 |
id => t8('ID'),
|
|
48 |
name => t8('Name'),
|
|
49 |
department_1 => t8('Department 1'),
|
|
50 |
department_2 => t8('Department 2'),
|
|
51 |
street => t8('Street'),
|
|
52 |
zipcode => t8('Zipcode'),
|
|
53 |
city => t8('City'),
|
|
54 |
country => t8('Country'),
|
|
55 |
contact => t8('Contact'),
|
|
56 |
phone => t8('Phone'),
|
|
57 |
fax => t8('Fax'),
|
|
58 |
email => t8('E-mail'),
|
|
59 |
notes => t8('Notes'),
|
|
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'),
|
|
65 |
},
|
|
66 |
);
|
|
67 |
$column_titles{$_} = $column_titles{customer_vendor} for qw(customer vendor);
|
|
68 |
|
| 41 |
69 |
my %datev_column_defs = (
|
| 42 |
70 |
trans_id => { type => 'Rose::DB::Object::Metadata::Column::Integer', text => t8('ID'), },
|
| 43 |
71 |
amount => { type => 'Rose::DB::Object::Metadata::Column::Numeric', text => t8('Amount'), },
|
| ... | ... | |
| 211 |
239 |
->tag('UTF8')
|
| 212 |
240 |
->tag('DecimalSymbol', '.')
|
| 213 |
241 |
->tag('DigitGroupingSymbol', '|') # see CAVEATS in documentation
|
|
242 |
->tag('Range', sub { $self
|
|
243 |
->tag('From', $self->csv_headers ? 2 : 1)
|
|
244 |
})
|
| 214 |
245 |
->tag('VariableLength', sub { $self
|
| 215 |
246 |
->tag('ColumnDelimiter', ',') # see CAVEATS for missing RecordDelimiter
|
| 216 |
247 |
->tag('TextEncapsulator', '"')
|
| ... | ... | |
| 252 |
283 |
die "unknown col type @{[ ref $column ]}" unless $type;
|
| 253 |
284 |
|
| 254 |
285 |
$self->tag('VariablePrimaryKey', sub { $self
|
| 255 |
|
->tag('Name', $column->name);
|
|
286 |
->tag('Name', $column_titles{$table}{$column->name});
|
| 256 |
287 |
$type->($self);
|
| 257 |
288 |
})
|
| 258 |
289 |
}
|
| ... | ... | |
| 263 |
294 |
die "unknown col type @{[ ref $column]}" unless $type;
|
| 264 |
295 |
|
| 265 |
296 |
$self->tag('VariableColumn', sub { $self
|
| 266 |
|
->tag('Name', $column->name);
|
|
297 |
->tag('Name', $column_titles{$table}{$column->name});
|
| 267 |
298 |
$type->($self);
|
| 268 |
299 |
})
|
| 269 |
300 |
}
|
| ... | ... | |
| 293 |
324 |
}
|
| 294 |
325 |
|
| 295 |
326 |
$self->tag('ForeignKey', sub {
|
| 296 |
|
$_[0]->tag('Name', $_) for keys %key_columns;
|
|
327 |
$_[0]->tag('Name', $column_titles{$table}{$_}) for keys %key_columns;
|
| 297 |
328 |
$_[0]->tag('References', $rel->class->meta->table);
|
| 298 |
329 |
});
|
| 299 |
330 |
}
|
| ... | ... | |
| 317 |
348 |
->tag('UTF8')
|
| 318 |
349 |
->tag('DecimalSymbol', '.')
|
| 319 |
350 |
->tag('DigitGroupingSymbol', '|') # see CAVEATS in documentation
|
|
351 |
->tag('Range', sub { $self
|
|
352 |
->tag('From', $self->csv_headers ? 2 : 1)
|
|
353 |
})
|
| 320 |
354 |
->tag('VariableLength', sub { $self
|
| 321 |
355 |
->tag('ColumnDelimiter', ',') # see CAVEATS for missing RecordDelimiter
|
| 322 |
356 |
->tag('TextEncapsulator', '"')
|
| ... | ... | |
| 337 |
371 |
die "unknown col type @{[ $column ]}" unless $type;
|
| 338 |
372 |
|
| 339 |
373 |
$self->tag('VariablePrimaryKey', sub { $self
|
| 340 |
|
->tag('Name', $column);
|
|
374 |
->tag('Name', $datev_column_defs{$column}{text});
|
| 341 |
375 |
$type->($self);
|
| 342 |
376 |
})
|
| 343 |
377 |
}
|
| ... | ... | |
| 348 |
382 |
die "unknown col type @{[ ref $column]}" unless $type;
|
| 349 |
383 |
|
| 350 |
384 |
$self->tag('VariableColumn', sub { $self
|
| 351 |
|
->tag('Name', $column);
|
|
385 |
->tag('Name', $datev_column_defs{$column}{text});
|
| 352 |
386 |
$type->($self);
|
| 353 |
387 |
})
|
| 354 |
388 |
}
|
| ... | ... | |
| 360 |
394 |
my ($self) = @_;
|
| 361 |
395 |
# hard code weeee
|
| 362 |
396 |
$self->tag('ForeignKey', sub { $_[0]
|
| 363 |
|
->tag('Name', 'customer_id')
|
|
397 |
->tag('Name', $datev_column_defs{customer_id}{text})
|
| 364 |
398 |
->tag('References', 'customer')
|
| 365 |
399 |
});
|
| 366 |
400 |
$self->tag('ForeignKey', sub { $_[0]
|
| 367 |
|
->tag('Name', 'vendor_id')
|
|
401 |
->tag('Name', $datev_column_defs{vendor_id}{text})
|
| 368 |
402 |
->tag('References', 'vendor')
|
| 369 |
403 |
});
|
| 370 |
404 |
$self->tag('ForeignKey', sub { $_[0]
|
| 371 |
|
->tag('Name', $_)
|
|
405 |
->tag('Name', $datev_column_defs{$_}{text})
|
| 372 |
406 |
->tag('References', 'chart')
|
| 373 |
407 |
}) for qw(debit_accno credit_accno tax_accno);
|
| 374 |
408 |
}
|
| ... | ... | |
| 396 |
430 |
$self->files->{"transactions.csv"} = $filename;
|
| 397 |
431 |
push @{ $self->tempfiles }, $filename;
|
| 398 |
432 |
|
|
433 |
if ($self->csv_headers) {
|
|
434 |
$csv->print($fh, [ map { _normalize_cell($datev_column_defs{$_}{text}) } @datev_columns ]);
|
|
435 |
}
|
|
436 |
|
| 399 |
437 |
for my $transaction (@transactions) {
|
| 400 |
438 |
my $is_payment = any { $_->{link} =~ m{A[PR]_paid} } @{ $transaction };
|
| 401 |
439 |
|
| ... | ... | |
| 442 |
480 |
my @columns = (@{ $cols_by_primary_key{1} }, @{ $cols_by_primary_key{0} });
|
| 443 |
481 |
my %col_index = do { my $i = 0; map {; "$_" => $i++ } @columns };
|
| 444 |
482 |
|
|
483 |
if ($self->csv_headers) {
|
|
484 |
$csv->print($fh, [ map { _normalize_cell($column_titles{$table}{$_->name}) } @columns ]) or die $csv->error_diag;
|
|
485 |
}
|
|
486 |
|
| 445 |
487 |
# and normalize date stuff
|
| 446 |
488 |
my @select_tokens = map { (ref $_) =~ /Time/ ? $_->name . '::date' : $_->name } @columns;
|
| 447 |
489 |
|
| ... | ... | |
| 553 |
595 |
sub _normalize_cell {
|
| 554 |
596 |
$_[0] =~ s/\r\n/ /g;
|
| 555 |
597 |
$_[0] =~ s/,/;/g;
|
|
598 |
$_[0]
|
| 556 |
599 |
}
|
| 557 |
600 |
|
| 558 |
601 |
sub init_files { +{} }
|
| 559 |
602 |
sub init_export_ids { +{} }
|
| 560 |
603 |
sub init_tempfiles { [] }
|
| 561 |
604 |
sub init_tables { [ grep { $known_tables{$_} } @export_table_order ] }
|
|
605 |
sub init_csv_headers { 1 }
|
| 562 |
606 |
|
| 563 |
607 |
sub API_VERSION {
|
| 564 |
608 |
DateTime->new(year => 2002, month => 8, day => 14)->to_kivitendo;
|
| ... | ... | |
| 603 |
647 |
Will only include records in the specified date range. Data pulled from other
|
| 604 |
648 |
tables will be culled to match what is needed for these records.
|
| 605 |
649 |
|
|
650 |
=item csv_headers
|
|
651 |
|
|
652 |
Optional. If set, will include a header line in the exported CSV files. Default true.
|
|
653 |
|
| 606 |
654 |
=item tables
|
| 607 |
655 |
|
| 608 |
656 |
Ooptional list of tables to be exported. Defaults to all tables.
|
| ... | ... | |
| 698 |
746 |
Neither it is able to parse escaped C<ColumnDelimiter> in data. It just splits
|
| 699 |
747 |
on that symbol no matter what surrounds or preceeds it.
|
| 700 |
748 |
|
|
749 |
=item *
|
|
750 |
|
|
751 |
Despite the standard specifying UTF-8 as a valid encoding the IDEA software
|
|
752 |
will just downgrade everything to latin1.
|
|
753 |
|
| 701 |
754 |
=back
|
| 702 |
755 |
|
| 703 |
756 |
=head2 Problems outside of the software
|
GDPDU: lokalisierte Spaltennamen und CSV Header
Ausserdem:
- Vendor (database ID) war falsch lokalisiert
- vendor hat noch customernumber exportiert, exportiert jetzt
vendornumber
- Mehr Dokumentation