From 1d559eff1e8a7efba3d21704d93b8cb62749de75 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Jan=20B=C3=BCren?= Date: Thu, 2 Nov 2017 10:44:16 +0100 Subject: [PATCH] DATEV: csv_buchungsexport nach DATEV::CSV.pm ausgelagert MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Testfälle angepasst. POD angepasst. Details: DATEV.pm - Klassenvariable locked hinzugefügt. - Aufruf der CSV-Klasse anstatt der internen Methode CSV.pm - Konstruktor wie in DATEV.pm ergänzt und um minimale Pflichtfeldprüfung ergänzt. - datetofour durch SL::Helper::DateTime ersetzt - Helper _format_amount auch aufrufen - Routinen umbenannt (pseudoprivat mit Unterstrich) - Prüfung auf locked als perlish boolean - _csv_buchungsexport um zweiten return array_ref mit warnungen ergänzt t/datev/* - Testfälle enstprechend dem neuen API-Call umgeschrieben - Einen Testfall zur Überprüfung von keiner Warnung ergänzt --- SL/DATEV.pm | 130 ++++++++++----------------------- SL/DATEV/CSV.pm | 138 +++++++++++++++++++++++++++++++++--- t/datev/datev_format_2018.t | 28 ++++++-- t/datev/invoices.t | 19 ++++- 4 files changed, 206 insertions(+), 109 deletions(-) diff --git a/SL/DATEV.pm b/SL/DATEV.pm index b0e9936e7..0c5101541 100644 --- a/SL/DATEV.pm +++ b/SL/DATEV.pm @@ -372,7 +372,30 @@ sub csv_export { die 'no exporttype set!' unless $self->has_exporttype; if ($self->exporttype == DATEV_ET_BUCHUNGEN) { - _csv_buchungsexport_to_file($self, data => $self->csv_buchungsexport); + + $self->generate_datev_data(from_to => $self->fromto); + return if $self->errors; + + my $datev_ref = SL::DATEV::CSV->new(datev_lines => $self->generate_datev_lines, + from => $self->from, + to => $self->to, + locked => $self->locked, + ); + + my $filename = "EXTF_DATEV_kivitendo" . $self->from->ymd() . '-' . $self->to->ymd() . ".csv"; + + my $csv = Text::CSV_XS->new({ + binary => 1, + sep_char => ";", + always_quote => 1, + eol => "\r\n", + }) or die "Cannot use CSV: ".Text::CSV_XS->error_diag(); + + my $csv_file = IO::File->new($self->export_path . '/' . $filename, '>:encoding(cp1252)') or die "Can't open: $!"; + $csv->print($csv_file, $_) for @{ $datev_ref }; + $csv_file->close; + + return { download_token => $self->download_token, filenames => $filename }; } elsif ($self->exporttype == DATEV_ET_STAMM) { die 'will never be implemented'; @@ -404,6 +427,15 @@ sub _sign { $_[0] <=> 0; } +sub locked { + my $self = shift; + + if (@_) { + $self->{locked} = $_[0]; + } + return $self->{locked}; +} + sub generate_datev_data { $main::lxdebug->enter_sub(); @@ -1370,97 +1402,6 @@ sub csv_export_for_tax_accountant { return { download_token => $self->download_token, filenames => \@filenames }; } -sub csv_buchungsexport { - my $self = shift; - my %params = @_; - - $self->generate_datev_data(from_to => $self->fromto); - return if $self->errors; - - my @datev_lines = @{ $self->generate_datev_lines }; - - my @csv_columns = SL::DATEV::CSV->kivitendo_to_datev(); - my @csv_headers = SL::DATEV::CSV->generate_csv_header( - from => $self->from->ymd(''), - to => $self->to->ymd(''), - first_day_of_fiscal_year => $self->to->year . '0101', - locked => 0 - ); - - my @array_of_datev; - - # 2 Headers - push @array_of_datev, \@csv_headers; - push @array_of_datev, [ map { $_->{csv_header_name} } @csv_columns ]; - - my @warnings; - foreach my $row ( @datev_lines ) { - my @current_datev_row; - - # shorten strings - if ($row->{belegfeld1}) { - $row->{buchungsbes} = $row->{belegfeld1} if $row->{belegfeld1}; - $row->{belegfeld1} = substr($row->{belegfeld1}, 0, 12); - $row->{buchungsbes} = substr($row->{buchungsbes}, 0, 60); - } - - $row->{datum} = datetofour($row->{datum}, 0); - $row->{kost1} = substr($row->{kost1}, 0, 8) if $row->{kost1}; - $row->{kost2} = substr($row->{kost2}, 0, 8) if $row->{kost2}; - - # , as decimal point and trim for UstID - $row->{umsatz} =~ s/\./,/; - $row->{ustid} =~ s/\s//g if $row->{ustid}; # trim whitespace - - foreach my $column (@csv_columns) { - if (exists $column->{max_length} && $column->{kivi_datev_name} ne 'not yet implemented') { - # check max length - die "Incorrect length of field" if length($row->{ $column->{kivi_datev_name} }) > $column->{max_length}; - } - if (exists $column->{valid_check} && $column->{kivi_datev_name} ne 'not yet implemented') { - # more checks, listed as user warnings - push @warnings, t8("Wrong field value '#1' for field '#2' for the transaction" . - " with amount '#3'",$row->{ $column->{kivi_datev_name} }, - $column->{kivi_datev_name},$row->{umsatz}) - unless ($column->{valid_check}->($row->{ $column->{kivi_datev_name} })); - } - push @current_datev_row, $row->{ $column->{kivi_datev_name} }; - } - push @array_of_datev, \@current_datev_row; - } - $self->warnings(@warnings) if @warnings; - return \@array_of_datev; -} - -sub _csv_buchungsexport_to_file { - my $self = shift; - my %params = @_; - - # we can definitely deny shorter data structures - croak ("Need at least 2 rows for header info") unless scalar @{ $params{data} } > 1; - - my $filename = "EXTF_DATEV_kivitendo" . $self->from->ymd() . '-' . $self->to->ymd() . ".csv"; - my @data = \$params{data}; - - my $csv = Text::CSV_XS->new({ - binary => 1, - sep_char => ";", - always_quote => 1, - eol => "\r\n", - }) or die "Cannot use CSV: ".Text::CSV_XS->error_diag(); - - if ($csv->version >= 1.18) { - # get rid of stupid datev warnings in "Validity program" - $csv->quote_empty(1); - } - - my $csv_file = IO::File->new($self->export_path . '/' . $filename, '>:encoding(cp1252)') or die "Can't open: $!"; - $csv->print($csv_file, $_) for @{ $params{data} }; - $csv_file->close; - - return { download_token => $self->download_token, filenames => $params{filename} }; -} - sub check_vcnumbers_are_valid_pk_numbers { my ($self) = @_; @@ -1763,6 +1704,11 @@ correctly. Set boundary account numbers for the export. Only useful for a stammdaten export. +=item locked + +Boolean if the transactions are locked (read-only in kivitenod) or not. +Default value is false + =back =head1 CONSTANTS diff --git a/SL/DATEV/CSV.pm b/SL/DATEV/CSV.pm index 7738897b5..3ea826cf3 100644 --- a/SL/DATEV/CSV.pm +++ b/SL/DATEV/CSV.pm @@ -4,6 +4,7 @@ use strict; use SL::Locale::String qw(t8); use SL::DB::Datev; +use SL::Helper::DateTime; use Carp; use DateTime; @@ -205,6 +206,26 @@ my @kivitendo_to_datev = ( }, # pos 40 ); +sub new { + my $class = shift; + my %data = @_; + + my $obj = bless {}, $class; + + croak(t8('We need a valid from date')) unless (ref $data{from} eq 'DateTime'); + croak(t8('We need a valid to date')) unless (ref $data{to} eq 'DateTime'); + croak(t8('We need a array of datev_lines')) unless (ref $data{datev_lines} eq 'ARRAY'); + + # TODO no params here, better class variables/values + return _csv_buchungsexport(from => $data{from}, + to => $data{to}, + datev_lines => $data{datev_lines}, + locked => $data{locked}, + ); + + $obj; +} + sub check_encoding { my ($test) = @_; return undef unless $test; @@ -216,7 +237,7 @@ sub check_encoding { } } -sub kivitendo_to_datev { +sub _kivitendo_to_datev { my ($self) = @_; my $entries = scalar (@kivitendo_to_datev); @@ -224,15 +245,16 @@ sub kivitendo_to_datev { return @kivitendo_to_datev; } -sub generate_csv_header { - my ($self, %params) = @_; +sub _generate_csv_header { + my %params = @_; # we need from and to in YYYYDDMM - croak "Wrong format for from" unless $params{from} =~ m/^[0-9]{8}$/; - croak "Wrong format for to" unless $params{to} =~ m/^[0-9]{8}$/; + croak "Wrong format for from $params{from}" unless $params{from} =~ m/^[0-9]{8}$/; + croak "Wrong format for to $params{to}" unless $params{to} =~ m/^[0-9]{8}$/; # who knows if we want locking and when our fiscal year starts - croak "Wrong state of locking" unless $params{locked} =~ m/(0|1)/; + # croak "Wrong state of locking" unless $params{locked} =~ m/^(0|1)$/; + my $locked = defined($params{locked}) ? 1 : 0; croak "No startdate of fiscal year" unless $params{first_day_of_fiscal_year} =~ m/^[0-9]{8}$/; @@ -260,13 +282,69 @@ sub generate_csv_header { "EXTF", "300", 21, "Buchungsstapel", 7, $created_on, "", "ki", "kivitendo-datev", "", $meta_datev{beraternr}, $meta_datev{mandantennr}, $params{first_day_of_fiscal_year}, $length_of_accounts, - $params{from}, $params{to}, "", "", 1, "", $params{locked}, + $params{from}, $params{to}, "", "", 1, "", $locked, $default_curr, "", "", "","" ); return @header; } +sub _csv_buchungsexport { + my %params = @_; + + my @csv_columns = _kivitendo_to_datev(); + my @csv_headers = _generate_csv_header( + from => $params{from}->ymd(''), + to => $params{to}->ymd(''), + first_day_of_fiscal_year => $params{to}->year . '0101', + locked => $params{locked} + ); + + my @array_of_datev; + + # 2 Headers + push @array_of_datev, \@csv_headers; + push @array_of_datev, [ map { $_->{csv_header_name} } @csv_columns ]; + + my @warnings; + foreach my $row (@{ $params{datev_lines} }) { + my @current_datev_row; + + # shorten strings + if ($row->{belegfeld1}) { + $row->{buchungsbes} = $row->{belegfeld1} if $row->{belegfeld1}; + $row->{belegfeld1} = substr($row->{belegfeld1}, 0, 12); + $row->{buchungsbes} = substr($row->{buchungsbes}, 0, 60); + } + + $row->{datum} = DateTime->from_kivitendo($row->{datum})->strftime('%d%m'); + + $row->{kost1} = substr($row->{kost1}, 0, 8) if $row->{kost1}; + $row->{kost2} = substr($row->{kost2}, 0, 8) if $row->{kost2}; + + # , as decimal point and trim for UstID + $row->{umsatz} = _format_amount($row->{umsatz}); + $row->{ustid} =~ s/\s//g if $row->{ustid}; # trim whitespace + + foreach my $column (@csv_columns) { + if (exists $column->{max_length} && $column->{kivi_datev_name} ne 'not yet implemented') { + # check max length + die "Incorrect length of field" if length($row->{ $column->{kivi_datev_name} }) > $column->{max_length}; + } + if (exists $column->{valid_check} && $column->{kivi_datev_name} ne 'not yet implemented') { + # more checks, listed as user warnings + push @warnings, t8("Wrong field value '#1' for field '#2' for the transaction" . + " with amount '#3'",$row->{ $column->{kivi_datev_name} }, + $column->{kivi_datev_name},$row->{umsatz}) + unless ($column->{valid_check}->($row->{ $column->{kivi_datev_name} })); + } + push @current_datev_row, $row->{ $column->{kivi_datev_name} }; + } + push @array_of_datev, \@current_datev_row; + } + return (\@array_of_datev, \@warnings); +} + sub _format_amount { $::form->format_amount({ numberformat => '1000,00' }, @_); } @@ -283,6 +361,27 @@ SL::DATEV::CSV - kivitendo DATEV CSV Specification =head1 SYNOPSIS + use SL::DATEV qw(:CONSTANTS); + use SL::DATEV::CSV; + + my $startdate = DateTime->new(year => 2014, month => 9, day => 1); + my $enddate = DateTime->new(year => 2014, month => 9, day => 31); + my $datev = SL::DATEV->new( + exporttype => DATEV_ET_BUCHUNGEN, + format => DATEV_FORMAT_CSV, + from => $startdate, + to => $enddate, + ); + $datev->generate_datev_data; + + my $datev_ref = SL::DATEV::CSV->new(datev_lines => $datev->generate_datev_lines, + from => $datev->from, + to => $datev->to, + locked => $datev->locked, + ); + +=head1 DESCRIPTION + The parsing of the DATEV CSV is index based, therefore the correct column must be present at the corresponding index, i.e.: Index 2 @@ -348,6 +447,11 @@ Line 3 - n: must contain 116 fields, a smaller subset is mandatory. =over 4 +=item new PARAMS + +Constructor for CSV-DATEV export. +Checks mandantory params as described in section synopsis. + =item check_encoding Helper function, returns true if a string is not empty and cp1252 encoded @@ -366,7 +470,7 @@ All params are mandatory: C, C and C have to be in YYYYDDMM date string format. -Furthermore C needs to be a boolean in number format (0|1). +Furthermore C is a perlish boolean. =item kivitendo_to_datev @@ -379,4 +483,22 @@ Lightweight wrapper for form->format_amount. Expects a number in kivitendo database format and returns the same number in DATEV format. +=item _csv_buchungsexport + +Generates the CSV-Format data for the CSV DATEV export and returns +an 2-dimensional array as an array_ref. +May additionally return a second array_ref with warnings. + +Requires the same date fields as the constructor for a valid DATEV header. + +Furthermore we assume that the first day of the fiscal year is +the first of January and we cannot guarantee that our data in kivitendo +is locked, that means a booking cannot be modified after a defined (vat tax) +period. +Some validity checks (max_length and regex) will be done if the +data structure contains them and the field is defined. + +To add or alter the structure of the data take a look at the C<@kivitendo_to_datev> structure. + + =back diff --git a/t/datev/datev_format_2018.t b/t/datev/datev_format_2018.t index 3660b28fd..958f7bd82 100644 --- a/t/datev/datev_format_2018.t +++ b/t/datev/datev_format_2018.t @@ -68,8 +68,12 @@ $datev1->generate_datev_lines; # check conversion to csv $datev1->from($startdate); $datev1->to($enddate); -$datev1->csv_buchungsexport(); -my @warnings = $datev1->warnings; +my ($datev_ref, $warnings_ref) = SL::DATEV::CSV->new(datev_lines => $datev1->generate_datev_lines, + from => $startdate, + to => $enddate, + locked => $datev1->locked, + ); +my @warnings = $warnings_ref; is($warnings[0]->[0]->{untranslated}, 'Wrong field value \'#1\' for field \'#2\' for the transaction with amount \'#3\'', 'wrong_encoding'); @@ -87,9 +91,16 @@ $datev3->from($startdate); $datev3->to($enddate); $datev3->generate_datev_data; $datev3->generate_datev_lines; -$datev3->csv_buchungsexport; +my ($datev_ref2, $warnings_ref2) = SL::DATEV::CSV->new(datev_lines => $datev3->generate_datev_lines, + from => $startdate, + to => $enddate, + locked => $datev3->locked, + ); + + + @warnings = []; -@warnings = $datev3->warnings; +@warnings = $warnings_ref2; is($warnings[0]->[0]->{untranslated}, 'Wrong field value \'#1\' for field \'#2\' for the transaction with amount \'#3\'', 'mixed_wrong_encoding'); @@ -157,9 +168,14 @@ $datev2->to($enddate); $datev2->generate_datev_data; $datev2->generate_datev_lines; -my @data_csv = splice @{ $datev2->csv_buchungsexport() }, 2, 5; -@data_csv = sort { $a->[0] <=> $b->[0] } @data_csv; +my ($datev_ref3, $warnings_ref3) = SL::DATEV::CSV->new(datev_lines => $datev2->generate_datev_lines, + from => $startdate, + to => $enddate, + locked => $datev2->locked, + ); +my @data_csv = splice @{ $datev_ref3 }, 2, 5; +@data_csv = sort { $a->[0] cmp $b->[0] } @data_csv; my $cp1252_posting_text = SL::Iconv::convert("UTF-8", "CP1252", 'Reisekosten März 2018'); cmp_bag($data_csv[0], [ 100, 'H', 'EUR', undef, undef, undef, '4660', '1000', 9, '1703', 'Reisekosten ', diff --git a/t/datev/invoices.t b/t/datev/invoices.t index 6b06196ab..68acb0537 100644 --- a/t/datev/invoices.t +++ b/t/datev/invoices.t @@ -91,7 +91,9 @@ cmp_bag \@data_datev, [ }, { 'belegfeld1' => "\x{de} sales \x{a5}& inv\x{f6}ice", - 'buchungstext' => 'Testcustomer', + + +'buchungstext' => 'Testcustomer', 'buchungstext' => 'Testcustomer', 'datum' => '05.01.2017', 'gegenkonto' => '1400', @@ -152,11 +154,22 @@ my $enddate = DateTime->new(year => 2017, month => 12, day => 31); # check conversion to csv $datev1->from($startdate); $datev1->to($enddate); -$datev1->use_pk(0); # reset use_pk for csv_buchungsexport +# reset use_pk for csv_buchungsexport +$datev1->use_pk(0); +$datev1->generate_datev_data; + + +my ($datev_ref, $w_ref) = SL::DATEV::CSV->new(datev_lines => $datev1->generate_datev_lines, + from => $startdate, + to => $enddate, + locked => $datev1->locked, + ); +# warnings should be undef -> no array elements at all +is(scalar @{ $w_ref }, 0); # splice away the header, because sort won't do # we need sort, because pay_invoice is not acc_trans_id order safe -my @data_csv = splice @{ $datev1->csv_buchungsexport() }, 2, 5; +my @data_csv = splice @{ $datev_ref }, 2, 5; @data_csv = sort { $a->[0] cmp $b->[0] } @data_csv; my $cp1252_belegfeld1 = SL::Iconv::convert("UTF-8", "CP1252", 'Þ sales ¥& i'); -- 2.20.1