GDPDU: Zellennormalisierung auch für transactions

[kivitendo-erp.git] / SL / GDPDU.pm

diff --git a/SL/GDPDU.pm b/SL/GDPDU.pm
index e9878ae34a8d0bd6067e46b60ca60ca4db8b5413..8a2ae27bfc6f89fe547d6cfed8233df5c3f7e47d 100644 (file)
--- a/SL/GDPDU.pm
+++ b/SL/GDPDU.pm
@@ -39,7 +39,7 @@ my %known_tables = (
 );
 
 my %datev_column_defs = (
 );
 
 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'), },
   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'), },


@@ -57,6 +57,7 @@ my %datev_column_defs = (
   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 (database ID)'), },
   vendor_id         => { type => 'Rose::DB::Object::Metadata::Column::Integer', text => t8('Vendor (database ID)'), },
   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 (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(
 );
 
 my @datev_columns = qw(


@@ -68,7 +69,7 @@ my @datev_columns = qw(
   credit_accno credit_accname
   taxdescription tax
   tax_accno    tax_accname    taxkey
   credit_accno credit_accname
   taxdescription tax
   tax_accno    tax_accname    taxkey

-  notes
+  notes itime

 );
 
 # rows in this listing are tiers.
 );
 
 # rows in this listing are tiers.


@@ -84,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';
 
 # 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 = (
 
 # callbacks that produce the xml spec for these column types
 my %column_types = (


@@ -300,7 +304,7 @@ sub do_datev_xml_table {
   my $writer = $self->writer;
 
   $self->tag('Table', sub { $self
   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
     ->tag('Name', t8('Transactions'))
     ->tag('Description', t8('Transactions'))
     ->tag('Validity', sub { $self


@@ -326,7 +330,6 @@ sub datev_columns {
   my ($self, $table) = @_;
 
   my %cols_by_primary_key = partition_by { 1 * $datev_column_defs{$_}{primary_key} } @datev_columns;
   my ($self, $table) = @_;
 
   my %cols_by_primary_key = partition_by { 1 * $datev_column_defs{$_}{primary_key} } @datev_columns;

-  $::lxdebug->dump(0,  "cols", \%cols_by_primary_key);

 
   for my $column (@{ $cols_by_primary_key{1} }) {
     my $type = $column_types{ $datev_column_defs{$column}{type} };
 
   for my $column (@{ $cols_by_primary_key{1} }) {
     my $type = $column_types{ $datev_column_defs{$column}{type} };


@@ -397,29 +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 $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});
     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            = (
 
     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},
       debit_accno      => $soll->{accno},
       debit_accname    => $soll->{accname},
       credit_accno     => $haben->{accno},
       credit_accname   => $haben->{accname},

-      tax              => defined $amount->{net_amount} ? abs($amount->{amount}) - abs($amount->{net_amount}) : 0,
-      taxdescription   => defined($soll->{tax_accno}) ? $soll->{taxdescription} : $haben->{taxdescription},
+      tax              => defined $amount->{net_amount} ? $::form->format_amount($myconfig, abs($amount->{amount}) - abs($amount->{net_amount}), 5) : 0,

       notes            => $haben->{notes},
       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 ]);
   }
 
     $csv->print($fh, [ map { $row{$_} } @datev_columns ]);
   }
 


@@ -494,7 +494,7 @@ sub do_csv_export {
       $self->export_ids->{$table}{$keep_col} ||= {};
       $self->export_ids->{$table}{$keep_col}{$row->[$col_index{$keep_col}]}++;
     }
       $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;
   }
 
     $csv->print($fh, $row) or $csv->error_diag;
   }


@@ -550,6 +550,11 @@ sub all_tables {
   $self->tables(\@export_table_order) if $yesno;
 }
 
   $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 { [] }
 sub init_files { +{} }
 sub init_export_ids { +{} }
 sub init_tempfiles { [] }


@@ -689,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.
 
 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
 =back
 
 =head1 AUTHOR