GDPDU: Zellennormalisierung auch für transactions

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

diff --git a/SL/GDPDU.pm b/SL/GDPDU.pm
index 36ddebf45092cb711779e794b8602efe1510cebf..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'), },


@@ -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'), },
   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'), },
   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(
 );
 
 my @datev_columns = qw(


@@ -65,9 +67,9 @@ my @datev_columns = qw(
   transdate    invnumber      amount
   debit_accno  debit_accname
   credit_accno credit_accname
   transdate    invnumber      amount
   debit_accno  debit_accname
   credit_accno credit_accname

-  tax
+  taxdescription tax

   tax_accno    tax_accname    taxkey
   tax_accno    tax_accname    taxkey

-  notes
+  notes itime

 );
 
 # rows in this listing are tiers.
 );
 
 # 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';
 
 # 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 = (


@@ -299,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


@@ -324,8 +329,7 @@ sub do_datev_xml_table {
 sub datev_columns {
   my ($self, $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} };
 
   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;
     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 $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              => 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},
       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 ]);
   }
 


@@ -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}]}++;
     }
       $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;
   }


@@ -548,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 { [] }


@@ -687,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