# 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'), },
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(
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.
# 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 = (
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;
}
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
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} };
})
}
- 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;
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');
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 ]);
}
$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;
}
$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 { [] }
exactly RecordDelimiter) in data. So this export substites all of these with
spaces.
+=item *
+
+Neither it is able to parse escaped C<ColumnDelimiter> 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
projects / kivitendo-erp.git / blobdiff