X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FGDPDU.pm;h=f96f73cf29915e97ce61971f90980b5a14e65248;hb=2ecfde54bd40a15f430010faa28ec81383e4eec6;hp=36ddebf45092cb711779e794b8602efe1510cebf;hpb=ba9e2e01d2662182f04da256e0ff7ce258428332;p=kivitendo-erp.git diff --git a/SL/GDPDU.pm b/SL/GDPDU.pm index 36ddebf45..f96f73cf2 100644 --- a/SL/GDPDU.pm +++ b/SL/GDPDU.pm @@ -39,7 +39,7 @@ my %known_tables = ( ); my %datev_column_defs = ( - acc_trans_id => { type => 'Rose::DB::Object::Metadata::Column::Integer', text => t8('ID'), primary_key => 1 }, + 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,25 +49,27 @@ 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( - acc_trans_id + trans_id customer_id vendor_id name vcnumber 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 = ( @@ -299,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 @@ -324,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} }; @@ -338,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; @@ -396,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(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 ]); } @@ -492,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; } @@ -548,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 { [] } @@ -587,7 +594,7 @@ The name of the company, needed for the supplier header =item location -Location of the company, needed for the suupplier header +Location of the company, needed for the supplier header =item from @@ -598,64 +605,64 @@ tables will be culled to match what is needed for these records. =item tables -A list of tables to be exported. +Ooptional list of tables to be exported. Defaults to all tables. =item all_tables -Alternative to C, enables all known tables. +Optional alternative to C, forces all known tables. =back =item C -Do the work. Will return an absolut path to a temp file where all export files +Do the work. Will return an absolute path to a temp file where all export files are zipped together. =back =head1 CAVEATS +Sigh. There are a lot of issues with the IDEA software that were found out by +trial and error. + +=head2 Problems in the Specification + =over 4 =item * -Date format is shit. The official docs state that only C, C, C, -and C
are supported, timestamps do not exist. +The specced date format is capable of only C, C, C, +and C
. There are no timestamps or timezones. =item * -Number parsing seems to be fragile. Official docs state that behaviour for too -low C settings is undefined. Accuracy of 0 is not taken to mean -Integer but instead generates a warning for redudancy. +Numbers have the same issue. There is not dedicated integer type, and hinting +at an integer type by setting accuracy to 0 generates a warning for redundant +accuracy. -There is no dedicated integer type. +Also the number parsing is documented to be fragile. Official docs state that +behaviour for too low C settings is undefined. =item * -Currently C and C have a foreign key to themself with the name -C. If this foreign key is present in the C then the -storno records have to be too. Since this is extremely awkward to code and -confusing for the examiner as to why there are records outside of the time -range, this export skips all self-referential foreign keys. - -=item * +Foreign key definition is broken. Instead of giving column maps it assumes that +foreign keys map to the primary keys given for the target table, and in that +order. Also the target table must be known in full before defining a foreign key. -Documentation for foreign keys is extremely weird. Instead of giving column -maps it assumes that foreign keys map to the primary keys given for the target -table, and in that order. Foreign keys to keys that are not primary seems to be -impossible. Changing type is also not allowed (which actually makes sense). -Hopefully there are no bugs there. +As a consequence any additional keys apart from primary keys are not possible. +Self-referencing tables are also not possible. =item * -It's currently disallowed to export the whole dataset. It's not clear if this -is wanted. +The spec does not support splitting data sets into smaller chunks. For data +sets that exceed 700MB the spec helpfully suggests: "Use a bigger medium, such +as a DVD". =item * -It is not possible to set an empty C since then the import +It is not possible to set an empty C since then the import will just work with the default. This was asked in their forum, and the -response actually was: +response actually was to use a bogus grouping symbol that is not used: Einfache Lösung: Definieren Sie das Tausendertrennzeichen als Komma, auch wenn es nicht verwendet wird. Sollten Sie das Komma bereits als Feldtrenner @@ -674,12 +681,11 @@ generates the error message: Instead we just use the implicit default RecordDelimiter CRLF. -=item * +=back -Not confirmed yet: +=head2 Bugs in the IDEA software -Foreign keys seem only to work with previously defined tables (which would be -utterly insane). +=over 4 =item * @@ -687,6 +693,48 @@ 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. + +=back + +=head2 Problems outside of the software + +=over 4 + +=item * + +The law states that "all business related data" should be made available. In +practice there's no definition for what makes data "business related", and +different auditors seems to want different data. + +Currently we export most of the transactional data with supplementing +customers, vendors and chart of accounts. + +=item * + +While the standard explicitely state to provide data normalized, in practice +autditors aren't trained database operators and can not create complex vies on +normalized data on their own. The reason this works for other software is, that +DATEV and SAP seem to have written import plugins for their internal formats in +the IDEA software. + +So what is really exported is not unlike a DATEV export. Each transaction gets +splitted into chunks of 2 positions (3 with tax on one side). Those get +denormalized into a single data row with credfit/debit/tax fields. The charts +get denormalized into it as well, in addition to their account number serving +as a foreign key. + +Customers and vendors get denormalized into this as well, but are linked by ids +to their tables. And the reason for this is... + +=item * + +Some auditors do not have a full license of the IDEA software, and +can't do table joins. + =back =head1 AUTHOR