1 package SL::DATEV::CSV;
7 use Scalar::Util qw(looks_like_number);
11 use SL::Helper::DateTime;
12 use SL::Locale::String qw(t8);
14 use Rose::Object::MakeMethods::Generic (
15 scalar => [ qw(datev_lines from to locked warnings) ],
18 my @kivitendo_to_datev = (
20 kivi_datev_name => 'umsatz',
21 csv_header_name => t8('Transaction Value'),
25 input_check => sub { my ($input) = @_; return (looks_like_number($input) && length($input) <= 13 && $input > 0) },
26 formatter => \&_format_amount,
27 valid_check => sub { my ($check) = @_; return ($check =~ m/^\d{1,10}(\,\d{1,2})?$/) },
30 kivi_datev_name => 'soll_haben_kennzeichen',
31 csv_header_name => t8('Debit/Credit Label'),
36 input_check => sub { my ($check) = @_; return ($check =~ m/^(S|H)$/) },
37 formatter => sub { my ($input) = @_; return $input eq 'H' ? 'H' : 'S' },
38 valid_check => sub { my ($check) = @_; return ($check =~ m/^(S|H)$/) },
41 kivi_datev_name => 'waehrung',
42 csv_header_name => t8('Transaction Value Currency Code'),
46 input_check => sub { my ($check) = @_; return ($check eq '' || $check =~ m/^[A-Z]{3}$/) },
47 valid_check => sub { my ($check) = @_; return ($check =~ m/^[A-Z]{3}$/) },
50 kivi_datev_name => 'wechselkurs',
51 csv_header_name => t8('Exchange Rate'),
55 valid_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]*\.?[0-9]*$/) },
58 kivi_datev_name => 'not yet implemented',
59 csv_header_name => t8('Base Transaction Value'),
62 kivi_datev_name => 'not yet implemented',
63 csv_header_name => t8('Base Transaction Value Currency Code'),
66 kivi_datev_name => 'konto',
67 csv_header_name => t8('Account'),
71 input_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]{4,9}$/) },
74 kivi_datev_name => 'gegenkonto',
75 csv_header_name => t8('Contra Account'),
79 input_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]{4,9}$/) },
82 kivi_datev_name => 'buchungsschluessel',
83 csv_header_name => t8('Posting Key'),
87 input_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]{0,2}$/) },
90 kivi_datev_name => 'datum',
91 csv_header_name => t8('Invoice Date'),
95 input_check => sub { my ($check) = @_; return (ref (DateTime->from_kivitendo($check)) eq 'DateTime') },
96 formatter => sub { my ($input) = @_; return DateTime->from_kivitendo($input)->strftime('%d%m') },
97 valid_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]{4}$/) },
100 kivi_datev_name => 'belegfeld1',
101 csv_header_name => t8('Invoice Field 1'),
105 input_check => sub { my ($text) = @_; check_encoding($text); },
106 formatter => sub { my ($input) = @_; return substr($input, 0, 12) },
109 kivi_datev_name => 'belegfeld2',
110 csv_header_name => t8('Invoice Field 2'),
114 input_check => sub { my ($check) = @_; return 1 unless $check; return (ref (DateTime->from_kivitendo($check)) eq 'DateTime') },
115 formatter => sub { my ($input) = @_; return DateTime->from_kivitendo($input)->strftime('%d%m%y') },
116 valid_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]{6}$/) },
119 kivi_datev_name => 'not yet implemented',
120 csv_header_name => t8('Discount'),
124 kivi_datev_name => 'buchungstext',
125 csv_header_name => t8('Posting Text'),
129 input_check => sub { my ($text) = @_; return 1 unless $text; check_encoding($text); },
130 formatter => sub { my ($input) = @_; return substr($input, 0, 60) },
133 kivi_datev_name => 'not yet implemented',
136 kivi_datev_name => 'not yet implemented',
139 kivi_datev_name => 'not yet implemented',
142 kivi_datev_name => 'not yet implemented',
145 kivi_datev_name => 'not yet implemented',
148 kivi_datev_name => 'not yet implemented',
149 csv_header_name => t8('Link to invoice'),
150 max_length => 210, # DMS Application shortcut and GUID
152 # "8DB85C02-4CC3-FF3E-06D7-7F87EEECCF3A".
155 kivi_datev_name => 'not yet implemented',
158 kivi_datev_name => 'not yet implemented',
161 kivi_datev_name => 'not yet implemented',
164 kivi_datev_name => 'not yet implemented',
167 kivi_datev_name => 'not yet implemented',
170 kivi_datev_name => 'not yet implemented',
173 kivi_datev_name => 'not yet implemented',
176 kivi_datev_name => 'not yet implemented',
179 kivi_datev_name => 'not yet implemented',
182 kivi_datev_name => 'not yet implemented',
185 kivi_datev_name => 'not yet implemented',
188 kivi_datev_name => 'not yet implemented',
191 kivi_datev_name => 'not yet implemented',
194 kivi_datev_name => 'not yet implemented',
197 kivi_datev_name => 'not yet implemented',
200 kivi_datev_name => 'not yet implemented',
203 kivi_datev_name => 'kost1',
204 csv_header_name => t8('Cost Center'),
208 input_check => sub { my ($text) = @_; return 1 unless $text; check_encoding($text); },
209 formatter => sub { my ($input) = @_; return substr($input, 0, 8) },
212 kivi_datev_name => 'kost2',
213 csv_header_name => t8('Cost Center'),
217 input_check => sub { my ($text) = @_; return 1 unless $text; check_encoding($text); },
218 formatter => sub { my ($input) = @_; return substr($input, 0, 8) },
221 kivi_datev_name => 'not yet implemented',
222 csv_header_name => t8('KOST Quantity'),
225 valid_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]{0,9}$/) },
228 kivi_datev_name => 'ustid',
229 csv_header_name => t8('EU Member State and VAT ID Number'),
235 return 1 if ('' eq $ustid);
237 return ($ustid =~ m/^CH|^[A-Z]{2}\w{5,13}$/);
239 formatter => sub { my ($input) = @_; $input =~ s/\s//g; return $input },
242 return 1 if ('' eq $ustid);
243 return ($ustid =~ m/^CH|^[A-Z]{2}\w{5,13}$/);
252 croak(t8('We need a valid from date')) unless (ref $data{from} eq 'DateTime');
253 croak(t8('We need a valid to date')) unless (ref $data{to} eq 'DateTime');
254 croak(t8('We need a array of datev_lines')) unless (ref $data{datev_lines} eq 'ARRAY');
256 my $obj = bless {}, $class;
257 $obj->$_($data{$_}) for keys %data;
263 return undef unless $test;
265 encode('Windows-1252', $test, Encode::FB_CROAK|Encode::LEAVE_SRC);
272 sub _kivitendo_to_datev {
273 @kivitendo_to_datev, ({ kivi_datev_name => 'not yet implemented' }) x (116 - @kivitendo_to_datev);
281 # we can safely set these defaults
282 # TODO get length_of_accounts from DATEV.pm
283 my $today = DateTime->now_local;
284 my $created_on = $today->ymd('') . $today->hms('') . '000';
285 my $length_of_accounts = length(SL::DB::Manager::Chart->get_first(where => [charttype => 'A'])->accno) // 4;
286 my $default_curr = SL::DB::Default->get_default_currency;
288 # datev metadata and the string length limits
290 my %meta_datev_to_valid_length = (
296 my $datev = SL::DB::Manager::Datev->get_first();
298 while (my ($k, $v) = each %meta_datev_to_valid_length) {
299 next unless $datev->{$k};
300 $meta_datev{$k} = substr $datev->{$k}, 0, $v;
304 "EXTF", "510", 21, "Buchungsstapel", 7, $created_on, "", "ki",
305 "kivitendo-datev", "", $meta_datev{beraternr}, $meta_datev{mandantennr},
306 $self->first_day_of_fiscal_year->ymd(''), $length_of_accounts,
307 $self->from->ymd(''), $self->to->ymd(''), "", "", 1, "", $self->locked,
308 $default_curr, "", "", "",""
310 push @header, [ @header_row_1 ];
312 # second header row, just the column names
313 push @header, [ map { $_->{csv_header_name} } _kivitendo_to_datev() ];
321 my (@array_of_datev, @warnings);
322 my @csv_columns = _kivitendo_to_datev();
324 foreach my $row (@{ $self->datev_lines }) {
325 my @current_datev_row;
327 # 1. check all datev_lines and see if we have a defined value
328 # 2. if we don't have a defined value set a default if exists
330 foreach my $column (@csv_columns) {
331 if ($column->{kivi_datev_name} eq 'not yet implemented') {
332 push @current_datev_row, '';
335 my $data = $row->{$column->{kivi_datev_name}};
336 if (!defined $data) {
337 if (defined $column->{default}) {
338 $data = $column->{default};
340 die 'No sensible value or a sensible default found for the entry: ' . $column->{kivi_datev_name};
343 # checkpoint a: no undefined data. All strict checks now!
344 if (exists $column->{input_check} && !$column->{input_check}->($data)) {
345 die t8("Wrong field value '#1' for field '#2' for the transaction with amount '#3'",
346 $data, $column->{kivi_datev_name}, $row->{umsatz});
348 # checkpoint b: we can safely format the input
349 if ($column->{formatter}) {
350 $data = $column->{formatter}->($data);
352 # checkpoint c: all soft checks now, will pop up as a user warning
353 if (exists $column->{valid_check} && !$column->{valid_check}->($data)) {
354 push @warnings, t8("Wrong field value '#1' for field '#2' for the transaction" .
355 " with amount '#3'", $data, $column->{kivi_datev_name}, $row->{umsatz});
357 push @current_datev_row, $data;
359 push @array_of_datev, \@current_datev_row;
361 $self->warnings(\@warnings);
362 return \@array_of_datev;
368 $::form->format_amount({ numberformat => '1000,00' }, @_);
371 sub first_day_of_fiscal_year {
372 $_[0]->to->clone->truncate(to => 'year');
383 SL::DATEV::CSV - kivitendo DATEV CSV Specification
387 use SL::DATEV qw(:CONSTANTS);
390 my $startdate = DateTime->new(year => 2014, month => 9, day => 1);
391 my $enddate = DateTime->new(year => 2014, month => 9, day => 31);
392 my $datev = SL::DATEV->new(
393 exporttype => DATEV_ET_BUCHUNGEN,
394 format => DATEV_FORMAT_CSV,
398 $datev->generate_datev_data;
400 my $datev_csv = SL::DATEV::CSV->new(datev_lines => $datev->generate_datev_lines,
401 from => $datev->from,
403 locked => $datev->locked,
405 $datev_csv->header; # returns the required 2 rows of header ($aref = [ ["row1" ..], [ "row2" .. ] ]) as array of array
406 $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
407 $datev_csv->warnings; # returns warnings
410 # The above object methods can be directly chained to a CSV export function, like this:
411 my $csv_file = IO::File->new($somewhere_in_filesystem)') or die "Can't open: $!";
412 $csv->print($csv_file, $_) for @{ $datev_csv->header };
413 $csv->print($csv_file, $_) for @{ $datev_csv->lines };
415 $self->{warnings} = $datev_csv->warnings;
422 The parsing of the DATEV CSV is index based, therefore the correct
423 column must be present at the corresponding index, i.e.:
425 Field Name : Debit/Credit Label
426 Valid Values : 'S' or 'H'
429 The columns in C<@kivi_datev> are in the correct order and the
430 specific attributes are defined as a key value hash list for each entry.
432 The key names are the english translation according to the DATEV specs
433 (Leitfaden DATEV englisch).
435 The two attributes C<max_length> and C<type> are also set as specified
438 To link the structure to kivitendo data, each entry has the attribute C<kivi_datev_name>
439 which is by convention the key name as generated by DATEV->generate_datev_data.
440 A value of C<'not yet implemented'> indicates that this field has no
441 corresponding kivitendo data and will be given an empty value by DATEV->csv_buchungsexport.
446 This is an excerpt of the DATEV Format 2015 Specification for CSV-Header
451 The filename is subject to the following restrictions:
452 1. The filename must begin with the prefix DTVF_ or EXTF_.
453 2. The filename must end with .csv.
455 When exporting from or importing into DATEV applications, the filename is
456 marked with the prefix "DTVF_" (DATEV Format).
457 The prefix "DTVF_" is reserved for DATEV applications.
458 If you are using a third-party application to create a file in the DATEV format
459 that you want to import using batch processing, use the prefix "EXTF_"
462 =head2 File Structure
464 The file structure of the text file exported/imported is defined as follows
466 Line 1: Header (serves to assist in the interpretation of the following data)
468 Line 2: Headline (headline of the user data)
470 Line 3 – n: Records (user data)
472 For an valid example file take a look at doc/DATEV-2015/EXTF_Buchungsstapel.csv
475 =head2 Detailed Description
477 Line 1 must contain 11 fields.
479 Line 2 must contain 26 fields.
481 Line 3 - n: must contain 116 fields, a smaller subset is mandatory.
489 Constructor for CSV-DATEV export.
490 Checks mandantory params as described in section synopsis.
494 Helper function, returns true if a string is not empty and cp1252 encoded
495 For example some arabic utf-8 like ݐ will return false
499 Mostly all other header information are constants or metadata loaded
500 from SL::DB::Datev.pm.
502 Returns the first two entries for the header (see above: File Structure)
505 =item kivitendo_to_datev
507 Returns the data structure C<@datev_data> as an array
511 Lightweight wrapper for form->format_amount.
512 Expects a number in kivitendo database format and returns the same number
515 =item first_day_of_fiscal_year
517 Takes a look at $self->to to determine the first day of the fiscal year.
521 Generates the CSV-Format data for the CSV DATEV export and returns
522 an 2-dimensional array as an array_ref.
523 May additionally return a second array_ref with warnings.
525 Requires the same date fields as the constructor for a valid DATEV header.
527 Furthermore we assume that the first day of the fiscal year is
528 the first of January and we cannot guarantee that our data in kivitendo
529 is locked, that means a booking cannot be modified after a defined (vat tax)
531 Some validity checks (max_length and regex) will be done if the
532 data structure contains them and the field is defined.
534 To add or alter the structure of the data take a look at the C<@kivitendo_to_datev> structure.
540 One can circumevent the check of the warnings.quite easily,
541 becaus warnings are generated after the call to lines:
544 die if @{ $datev_csv->warnings };
545 somethin_with($datev_csv->lines);
548 my $lines = $datev_csv->lines;
549 die if @{ $datev_csv->warnings };
550 somethin_with($lines);