X-Git-Url: http://wagnertech.de/gitweb/gitweb.cgi/kivitendo-erp.git/blobdiff_plain/00b6dc2240564add9114864655e96eb7cc6dde4f..e1f93c184a9f6fe0826306383f23c454011dc9b3:/SL/GDPDU.pm diff --git a/SL/GDPDU.pm b/SL/GDPDU.pm index 0978df9f3..8a2ae27bf 100644 --- a/SL/GDPDU.pm +++ b/SL/GDPDU.pm @@ -33,13 +33,13 @@ use Rose::Object::MakeMethods::Generic ( # keep: arrayref of columns that should be saved for further referencing # tables: arrayref with one column and one or many table.column references that were kept earlier my %known_tables = ( - chart => { name => t8('Charts'), description => t8('Chart of Accounts'), primary_key => 'accno' }, - customer => { name => t8('Customers'), description => t8('Customer Master Data'), }, - vendor => { name => t8('Vendors'), description => t8('Vendor Master Data'), }, + chart => { name => t8('Charts'), description => t8('Chart of Accounts'), primary_key => 'accno', columns => [ qw(id accno description) ], }, + 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) ] }, + 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) ] }, ); my %datev_column_defs = ( - acc_trans_id => { type => 'Rose::DB::Object::Metadata::Column::Integer', text => t8('ID'), primary_key => 1 }, + acc_trans_id => { type => 'Rose::DB::Object::Metadata::Column::Integer', text => t8('ID'), }, amount => { type => 'Rose::DB::Object::Metadata::Column::Numeric', text => t8('Amount'), }, credit_accname => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Credit Account Name'), }, credit_accno => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Credit Account'), }, @@ -49,13 +49,15 @@ my %datev_column_defs = ( name => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Name'), }, notes => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Notes'), }, tax => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Tax'), }, + taxdescription => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('tax_taxdescription'), }, taxkey => { type => 'Rose::DB::Object::Metadata::Column::Integer', text => t8('Taxkey'), }, tax_accname => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Tax Account Name'), }, tax_accno => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Tax Account'), }, transdate => { type => 'Rose::DB::Object::Metadata::Column::Date', text => t8('Invoice Date'), }, vcnumber => { type => 'Rose::DB::Object::Metadata::Column::Text', text => t8('Customer/Vendor Number'), }, - customer_id => { type => 'Rose::DB::Object::Metadata::Column::Integer', text => t8('Customer ID'), }, - vendor_id => { type => 'Rose::DB::Object::Metadata::Column::Integer', text => t8('Vendor ID'), }, + customer_id => { type => 'Rose::DB::Object::Metadata::Column::Integer', text => t8('Customer (database ID)'), }, + vendor_id => { type => 'Rose::DB::Object::Metadata::Column::Integer', text => t8('Vendor (database ID)'), }, + itime => { type => 'Rose::DB::Object::Metadata::Column::Date', text => t8('Create Date'), }, ); my @datev_columns = qw( @@ -65,9 +67,9 @@ my @datev_columns = qw( transdate invnumber amount debit_accno debit_accname credit_accno credit_accname - tax + taxdescription tax tax_accno tax_accname taxkey - notes + notes itime ); # rows in this listing are tiers. @@ -83,6 +85,9 @@ my @export_table_order = qw( # needed because the standard dbh sets datestyle german and we don't want to mess with that my $date_format = 'DD.MM.YYYY'; +my $number_format = '1000.00'; + +my $myconfig = { numberformat => $number_format }; # callbacks that produce the xml spec for these column types my %column_types = ( @@ -219,11 +224,20 @@ sub _table_columns { my ($table) = @_; my $package = SL::DB::Helper::Mappings::get_package_for_table($table); + my %white_list; + my $use_white_list = 0; + if ($known_tables{$table}{columns}) { + $use_white_list = 1; + $white_list{$_} = 1 for @{ $known_tables{$table}{columns} || [] }; + } + # PrimaryKeys must come before regular columns, so partition first partition_by { $known_tables{$table}{primary_key} ? 1 * ($_ eq $known_tables{$table}{primary_key}) : 1 * $_->is_primary_key_member + } grep { + $use_white_list ? $white_list{$_->name} : 1 } $package->meta->columns; } @@ -290,7 +304,7 @@ sub do_datev_xml_table { my $writer = $self->writer; $self->tag('Table', sub { $self - ->tag('URL', "transaction.csv") + ->tag('URL', "transactions.csv") ->tag('Name', t8('Transactions')) ->tag('Description', t8('Transactions')) ->tag('Validity', sub { $self @@ -315,8 +329,7 @@ sub do_datev_xml_table { sub datev_columns { my ($self, $table) = @_; - my %cols_by_primary_key = partition_by { $datev_column_defs{$_}{primary_key} } @datev_columns; - $::lxdebug->dump(0, "cols", \%cols_by_primary_key); + my %cols_by_primary_key = partition_by { 1 * $datev_column_defs{$_}{primary_key} } @datev_columns; for my $column (@{ $cols_by_primary_key{1} }) { my $type = $column_types{ $datev_column_defs{$column}{type} }; @@ -329,7 +342,7 @@ sub datev_columns { }) } - for my $column (@{ $cols_by_primary_key{''} }) { + for my $column (@{ $cols_by_primary_key{0} }) { my $type = $column_types{ $datev_column_defs{$column}{type} }; die "unknown col type @{[ ref $column]}" unless $type; @@ -375,11 +388,7 @@ sub do_datev_csv_export { my @transactions = sort_by { $_->[0]->{sortkey} } @{ $datev->{DATEV} }; - my $csv = Text::CSV_XS->new({ - binary => 1, - eol => "\n", - sep_char => ";", - }); + my $csv = Text::CSV_XS->new({ binary => 1, eol => "\r\n", sep_char => ",", quote_char => '"' }); my ($fh, $filename) = File::Temp::tempfile(); binmode($fh, ':utf8'); @@ -391,28 +400,26 @@ sub do_datev_csv_export { my $is_payment = any { $_->{link} =~ m{A[PR]_paid} } @{ $transaction }; my ($soll, $haben) = map { $transaction->[$_] } ($transaction->[0]->{amount} > 0 ? (1, 0) : (0, 1)); - my $tax = defined($soll->{tax_accno}) ? $soll : $haben; + my $tax = defined($soll->{tax_amount}) ? $soll : defined($haben->{tax_amount}) ? $haben : {}; my $amount = defined($soll->{net_amount}) ? $soll : $haben; $haben->{notes} = ($haben->{memo} || $soll->{memo}) if $haben->{memo} || $soll->{memo}; $haben->{notes} //= ''; $haben->{notes} = SL::HTML::Util->strip($haben->{notes}); - $haben->{notes} =~ s{\r}{}g; - $haben->{notes} =~ s{\n+}{ }g; my %row = ( - customer_id => $soll->{customer_id} || $haben->{customer_id}, - vendor_id => $soll->{vendor_id} || $haben->{vendor_id}, - amount => abs($amount->{amount}), + amount => $::form->format_amount($myconfig, abs($amount->{amount}),5), debit_accno => $soll->{accno}, debit_accname => $soll->{accname}, credit_accno => $haben->{accno}, credit_accname => $haben->{accname}, - tax => abs($amount->{amount}) - abs($amount->{net_amount}), + tax => defined $amount->{net_amount} ? $::form->format_amount($myconfig, abs($amount->{amount}) - abs($amount->{net_amount}), 5) : 0, notes => $haben->{notes}, - (map { ($_ => $tax->{$_}) } qw(taxkey tax_accname tax_accno)), - (map { ($_ => ($haben->{$_} // $soll->{$_})) } qw(acc_trans_id invnumber name vcnumber transdate)), + (map { ($_ => $tax->{$_}) } qw(taxkey tax_accname tax_accno taxdescription)), + (map { ($_ => ($haben->{$_} // $soll->{$_})) } qw(acc_trans_id invnumber name vcnumber transdate itime customer_id vendor_id)), ); + _normalize_cell($_) for values %row; # see CAVEATS + $csv->print($fh, [ map { $row{$_} } @datev_columns ]); } @@ -487,7 +494,7 @@ sub do_csv_export { $self->export_ids->{$table}{$keep_col} ||= {}; $self->export_ids->{$table}{$keep_col}{$row->[$col_index{$keep_col}]}++; } - s/\r\n/ /g for @$row; # see CAVEATS + _normalize_cell($_) for @$row; # see CAVEATS $csv->print($fh, $row) or $csv->error_diag; } @@ -543,6 +550,11 @@ sub all_tables { $self->tables(\@export_table_order) if $yesno; } +sub _normalize_cell { + $_[0] =~ s/\r\n/ /g; + $_[0] =~ s/,/;/g; +} + sub init_files { +{} } sub init_export_ids { +{} } sub init_tempfiles { [] } @@ -682,6 +694,17 @@ The CSV import library used in IDEA is not able to parse newlines (or more exactly RecordDelimiter) in data. So this export substites all of these with spaces. +=item * + +Neither it is able to parse escaped C in data. It just splits +on that symbol no matter what surrounds or preceeds it. + +=item * + +Fun fact: Some auditors do not have a full license of the IDEA software, and +can't do table joins. So it's best to provide denormalized data for them, so +that the auditor may infer which object is meant. + =back =head1 AUTHOR