X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FDATEV%2FCSV.pm;h=a4585de26731292533074139662d39a7843f31b5;hb=da8362118bc707e238beb24fcc1e6c09cf241456;hp=7738897b531cba1cc05a41e48f9476b7804d3c6a;hpb=850cb6b4b723772a97e5bb385a57723cd52432f3;p=kivitendo-erp.git diff --git a/SL/DATEV/CSV.pm b/SL/DATEV/CSV.pm index 7738897b5..a4585de26 100644 --- a/SL/DATEV/CSV.pm +++ b/SL/DATEV/CSV.pm @@ -1,14 +1,20 @@ package SL::DATEV::CSV; use strict; - -use SL::Locale::String qw(t8); -use SL::DB::Datev; - use Carp; use DateTime; -use Encode qw(decode); +use Encode qw(encode); +use Scalar::Util qw(looks_like_number); +use SL::DB::Datev; +use SL::DB::Chart; +use SL::Helper::DateTime; +use SL::Locale::String qw(t8); +use SL::Util qw(trim); + +use Rose::Object::MakeMethods::Generic ( + scalar => [ qw(datev_lines from to locked warnings) ], +); my @kivitendo_to_datev = ( { @@ -16,6 +22,9 @@ my @kivitendo_to_datev = ( csv_header_name => t8('Transaction Value'), max_length => 13, type => 'Value', + required => 1, + input_check => sub { my ($input) = @_; return (looks_like_number($input) && length($input) <= 13 && $input > 0) }, + formatter => \&_format_amount, valid_check => sub { my ($check) = @_; return ($check =~ m/^\d{1,10}(\,\d{1,2})?$/) }, }, { @@ -23,6 +32,10 @@ my @kivitendo_to_datev = ( csv_header_name => t8('Debit/Credit Label'), max_length => 1, type => 'Text', + required => 1, + default => 'S', + input_check => sub { my ($check) = @_; return ($check =~ m/^(S|H)$/) }, + formatter => sub { my ($input) = @_; return $input eq 'H' ? 'H' : 'S' }, valid_check => sub { my ($check) = @_; return ($check =~ m/^(S|H)$/) }, }, { @@ -30,6 +43,8 @@ my @kivitendo_to_datev = ( csv_header_name => t8('Transaction Value Currency Code'), max_length => 3, type => 'Text', + default => '', + input_check => sub { my ($check) = @_; return ($check eq '' || $check =~ m/^[A-Z]{3}$/) }, valid_check => sub { my ($check) = @_; return ($check =~ m/^[A-Z]{3}$/) }, }, { @@ -37,6 +52,7 @@ my @kivitendo_to_datev = ( csv_header_name => t8('Exchange Rate'), max_length => 11, type => 'Number', + default => '', valid_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]*\.?[0-9]*$/) }, }, { @@ -50,29 +66,35 @@ my @kivitendo_to_datev = ( { kivi_datev_name => 'konto', csv_header_name => t8('Account'), - max_length => 9, # May contain a maximum of 8 or 9 digits -> perldoc + max_length => 9, type => 'Account', - valid_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]{4,9}$/) }, + required => 1, + input_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]{4,9}$/) }, }, { kivi_datev_name => 'gegenkonto', csv_header_name => t8('Contra Account'), - max_length => 9, # May contain a maximum of 8 or 9 digits -> perldoc + max_length => 9, type => 'Account', - valid_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]{4,9}$/) }, + required => 1, + input_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]{4,9}$/) }, }, { kivi_datev_name => 'buchungsschluessel', csv_header_name => t8('Posting Key'), max_length => 2, type => 'Text', - valid_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]{0,2}$/) }, + default => '', + input_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]{0,2}$/) }, }, { kivi_datev_name => 'datum', csv_header_name => t8('Invoice Date'), max_length => 4, type => 'Date', + required => 1, + input_check => sub { my ($check) = @_; return (ref (DateTime->from_kivitendo($check)) eq 'DateTime') }, + formatter => sub { my ($input) = @_; return DateTime->from_kivitendo($input)->strftime('%d%m') }, valid_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]{4}$/) }, }, { @@ -80,14 +102,19 @@ my @kivitendo_to_datev = ( csv_header_name => t8('Invoice Field 1'), max_length => 12, type => 'Text', - valid_check => sub { my ($text) = @_; check_encoding($text); }, + default => '', + input_check => sub { my ($text) = @_; check_encoding($text); }, + formatter => sub { my ($input) = @_; return substr($input, 0, 12) }, }, { - kivi_datev_name => 'not yet implemented', + kivi_datev_name => 'belegfeld2', csv_header_name => t8('Invoice Field 2'), - max_length => 12, + max_length => 12, type => 'Text', - valid_check => sub { my ($check) = @_; return ($check =~ m/[ -~]{1,12}/) }, + default => '', + input_check => sub { my ($check) = @_; return 1 unless $check; return (ref (DateTime->from_kivitendo($check)) eq 'DateTime') }, + formatter => sub { my ($input) = @_; return undef unless $input; return trim(DateTime->from_kivitendo($input)->strftime('%e%m%y')) }, + valid_check => sub { my ($check) = @_; return 1 unless $check; return ($check =~ m/^[0-9]{5,6}$/) }, }, { kivi_datev_name => 'not yet implemented', @@ -95,11 +122,13 @@ my @kivitendo_to_datev = ( type => 'Value', }, { - kivi_datev_name => 'buchungsbes', + kivi_datev_name => 'buchungstext', csv_header_name => t8('Posting Text'), max_length => 60, type => 'Text', - valid_check => sub { my ($text) = @_; return 1 unless $text; check_encoding($text); }, + default => '', + input_check => sub { my ($text) = @_; return 1 unless $text; check_encoding($text); }, + formatter => sub { my ($input) = @_; return substr($input, 0, 60) }, }, # pos 14 { kivi_datev_name => 'not yet implemented', @@ -176,14 +205,18 @@ my @kivitendo_to_datev = ( csv_header_name => t8('Cost Center'), max_length => 8, type => 'Text', - valid_check => sub { my ($text) = @_; return 1 unless $text; check_encoding($text); }, + default => '', + input_check => sub { my ($text) = @_; return 1 unless $text; check_encoding($text); }, + formatter => sub { my ($input) = @_; return substr($input, 0, 8) }, }, # pos 37 { kivi_datev_name => 'kost2', csv_header_name => t8('Cost Center'), max_length => 8, type => 'Text', - valid_check => sub { my ($text) = @_; return 1 unless $text; check_encoding($text); }, + default => '', + input_check => sub { my ($text) = @_; return 1 unless $text; check_encoding($text); }, + formatter => sub { my ($input) = @_; return substr($input, 0, 8) }, }, # pos 38 { kivi_datev_name => 'not yet implemented', @@ -197,47 +230,58 @@ my @kivitendo_to_datev = ( csv_header_name => t8('EU Member State and VAT ID Number'), max_length => 15, type => 'Text', + default => '', + input_check => sub { + my ($ustid) = @_; + return 1 if ('' eq $ustid); + $ustid =~ s{\s+}{}g; + return ($ustid =~ m/^CH|^[A-Z]{2}\w{5,13}$/); + }, + formatter => sub { my ($input) = @_; $input =~ s/\s//g; return $input }, valid_check => sub { my ($ustid) = @_; - return 1 unless defined($ustid); + return 1 if ('' eq $ustid); return ($ustid =~ m/^CH|^[A-Z]{2}\w{5,13}$/); }, }, # pos 40 ); +sub new { + my $class = shift; + my %data = @_; + + 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'); + + my $obj = bless {}, $class; + $obj->$_($data{$_}) for keys %data; + $obj; +} + sub check_encoding { my ($test) = @_; return undef unless $test; if (eval { - decode('Windows-1252', $test, Encode::FB_CROAK|Encode::LEAVE_SRC); + encode('Windows-1252', $test, Encode::FB_CROAK|Encode::LEAVE_SRC); 1 }) { return 1; } } -sub kivitendo_to_datev { - my ($self) = @_; - - my $entries = scalar (@kivitendo_to_datev); - push @kivitendo_to_datev, { kivi_datev_name => 'not yet implemented' } for 1 .. (116 - $entries); - return @kivitendo_to_datev; +sub _kivitendo_to_datev { + @kivitendo_to_datev, ({ kivi_datev_name => 'not yet implemented' }) x (116 - @kivitendo_to_datev); } -sub generate_csv_header { - my ($self, %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}$/; - - # who knows if we want locking and when our fiscal year starts - croak "Wrong state of locking" unless $params{locked} =~ m/(0|1)/; - croak "No startdate of fiscal year" unless $params{first_day_of_fiscal_year} =~ m/^[0-9]{8}$/; +sub header { + my ($self) = @_; + my @header; # we can safely set these defaults - my $today = DateTime->now(time_zone => "local"); + # TODO get length_of_accounts from DATEV.pm + my $today = DateTime->now_local; my $created_on = $today->ymd('') . $today->hms('') . '000'; my $length_of_accounts = length(SL::DB::Manager::Chart->get_first(where => [charttype => 'A'])->accno) // 4; my $default_curr = SL::DB::Default->get_default_currency; @@ -253,24 +297,82 @@ sub generate_csv_header { my $datev = SL::DB::Manager::Datev->get_first(); while (my ($k, $v) = each %meta_datev_to_valid_length) { + next unless $datev->{$k}; $meta_datev{$k} = substr $datev->{$k}, 0, $v; } - my @header = ( - "EXTF", "300", 21, "Buchungsstapel", 7, $created_on, "", "ki", + my @header_row_1 = ( + "EXTF", "510", 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}, + $self->first_day_of_fiscal_year->ymd(''), $length_of_accounts, + $self->from->ymd(''), $self->to->ymd(''), "", "", 1, "", $self->locked, $default_curr, "", "", "","" ); + push @header, [ @header_row_1 ]; + + # second header row, just the column names + push @header, [ map { $_->{csv_header_name} } _kivitendo_to_datev() ]; + + return \@header; +} - return @header; +sub lines { + my ($self) = @_; + + my (@array_of_datev, @warnings); + my @csv_columns = _kivitendo_to_datev(); + + foreach my $row (@{ $self->datev_lines }) { + my @current_datev_row; + + # 1. check all datev_lines and see if we have a defined value + # 2. if we don't have a defined value set a default if exists + # 3. otherwise die + foreach my $column (@csv_columns) { + if ($column->{kivi_datev_name} eq 'not yet implemented') { + push @current_datev_row, ''; + next; + } + my $data = $row->{$column->{kivi_datev_name}}; + if (!defined $data) { + if (defined $column->{default}) { + $data = $column->{default}; + } else { + die 'No sensible value or a sensible default found for the entry: ' . $column->{kivi_datev_name}; + } + } + # checkpoint a: no undefined data. All strict checks now! + if (exists $column->{input_check} && !$column->{input_check}->($data)) { + die t8("Wrong field value '#1' for field '#2' for the transaction with amount '#3'", + $data, $column->{kivi_datev_name}, $row->{umsatz}); + } + # checkpoint b: we can safely format the input + if ($column->{formatter}) { + $data = $column->{formatter}->($data); + } + # checkpoint c: all soft checks now, will pop up as a user warning + if (exists $column->{valid_check} && !$column->{valid_check}->($data)) { + push @warnings, t8("Wrong field value '#1' for field '#2' for the transaction" . + " with amount '#3'", $data, $column->{kivi_datev_name}, $row->{umsatz}); + } + push @current_datev_row, $data; + } + push @array_of_datev, \@current_datev_row; + } + $self->warnings(\@warnings); + return \@array_of_datev; } +# helper + sub _format_amount { $::form->format_amount({ numberformat => '1000,00' }, @_); } +sub first_day_of_fiscal_year { + $_[0]->to->clone->truncate(to => 'year'); +} + 1; __END__ @@ -283,6 +385,41 @@ 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_csv = SL::DATEV::CSV->new(datev_lines => $datev->generate_datev_lines, + from => $datev->from, + to => $datev->to, + locked => $datev->locked, + ); + $datev_csv->header; # returns the required 2 rows of header ($aref = [ ["row1" ..], [ "row2" .. ] ]) as array of array + $datev_csv->lines; # returns an array_ref of rows of array_refs soll uns die ein Arrayref von Zeilen zurückgeben, die jeweils Arrayrefs sind + $datev_csv->warnings; # returns warnings + + + # The above object methods can be directly chained to a CSV export function, like this: + my $csv_file = IO::File->new($somewhere_in_filesystem)') or die "Can't open: $!"; + $csv->print($csv_file, $_) for @{ $datev_csv->header }; + $csv->print($csv_file, $_) for @{ $datev_csv->lines }; + $csv_file->close; + $self->{warnings} = $datev_csv->warnings; + + + + +=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,13 +485,17 @@ 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 For example some arabic utf-8 like ݐ will return false -=item generate_csv_header(from => 'YYYYDDMM', to => 'YYYYDDMM', locked => 0, - first_day_of_fiscal_year => 'YYYYDDMM') +=item header Mostly all other header information are constants or metadata loaded from SL::DB::Datev.pm. @@ -362,13 +503,6 @@ from SL::DB::Datev.pm. Returns the first two entries for the header (see above: File Structure) as an array. -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). - - =item kivitendo_to_datev Returns the data structure C<@datev_data> as an array @@ -379,4 +513,39 @@ Lightweight wrapper for form->format_amount. Expects a number in kivitendo database format and returns the same number in DATEV format. +=item first_day_of_fiscal_year + +Takes a look at $self->to to determine the first day of the fiscal year. + +=item lines + +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 + +=head1 TODO CAVEAT + +One can circumevent the check of the warnings.quite easily, +becaus warnings are generated after the call to lines: + + # WRONG usage + die if @{ $datev_csv->warnings }; + somethin_with($datev_csv->lines); + + # safe usage + my $lines = $datev_csv->lines; + die if @{ $datev_csv->warnings }; + somethin_with($lines);