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 sv_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 => 'not yet implemented',
110 csv_header_name => t8('Invoice Field 2'),
114 valid_check => sub { my ($check) = @_; return ($check =~ m/[ -~]{1,12}/) },
117 kivi_datev_name => 'not yet implemented',
118 csv_header_name => t8('Discount'),
122 kivi_datev_name => 'buchungstext',
123 csv_header_name => t8('Posting Text'),
127 input_check => sub { my ($text) = @_; return 1 unless $text; check_encoding($text); },
128 formatter => sub { my ($input) = @_; return substr($input, 0, 60) },
131 kivi_datev_name => 'not yet implemented',
134 kivi_datev_name => 'not yet implemented',
137 kivi_datev_name => 'not yet implemented',
140 kivi_datev_name => 'not yet implemented',
143 kivi_datev_name => 'not yet implemented',
146 kivi_datev_name => 'not yet implemented',
147 csv_header_name => t8('Link to invoice'),
148 max_length => 210, # DMS Application shortcut and GUID
150 # "8DB85C02-4CC3-FF3E-06D7-7F87EEECCF3A".
153 kivi_datev_name => 'not yet implemented',
156 kivi_datev_name => 'not yet implemented',
159 kivi_datev_name => 'not yet implemented',
162 kivi_datev_name => 'not yet implemented',
165 kivi_datev_name => 'not yet implemented',
168 kivi_datev_name => 'not yet implemented',
171 kivi_datev_name => 'not yet implemented',
174 kivi_datev_name => 'not yet implemented',
177 kivi_datev_name => 'not yet implemented',
180 kivi_datev_name => 'not yet implemented',
183 kivi_datev_name => 'not yet implemented',
186 kivi_datev_name => 'not yet implemented',
189 kivi_datev_name => 'not yet implemented',
192 kivi_datev_name => 'not yet implemented',
195 kivi_datev_name => 'not yet implemented',
198 kivi_datev_name => 'not yet implemented',
201 kivi_datev_name => 'kost1',
202 csv_header_name => t8('Cost Center'),
206 input_check => sub { my ($text) = @_; return 1 unless $text; check_encoding($text); },
207 formatter => sub { my ($input) = @_; return substr($input, 0, 8) },
210 kivi_datev_name => 'kost2',
211 csv_header_name => t8('Cost Center'),
215 input_check => sub { my ($text) = @_; return 1 unless $text; check_encoding($text); },
216 formatter => sub { my ($input) = @_; return substr($input, 0, 8) },
219 kivi_datev_name => 'not yet implemented',
220 csv_header_name => t8('KOST Quantity'),
223 valid_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]{0,9}$/) },
226 kivi_datev_name => 'ustid',
227 csv_header_name => t8('EU Member State and VAT ID Number'),
233 return 1 if ('' eq $ustid);
235 return ($ustid =~ m/^CH|^[A-Z]{2}\w{5,13}$/);
237 formatter => sub { my ($input) = @_; $input =~ s/\s//g; return $input },
240 return 1 if ('' eq $ustid);
241 return ($ustid =~ m/^CH|^[A-Z]{2}\w{5,13}$/);
250 croak(t8('We need a valid from date')) unless (ref $data{from} eq 'DateTime');
251 croak(t8('We need a valid to date')) unless (ref $data{to} eq 'DateTime');
252 croak(t8('We need a array of datev_lines')) unless (ref $data{datev_lines} eq 'ARRAY');
254 my $obj = bless {}, $class;
255 $obj->$_($data{$_}) for keys %data;
261 return undef unless $test;
263 decode('Windows-1252', $test, Encode::FB_CROAK|Encode::LEAVE_SRC);
270 sub _kivitendo_to_datev {
271 @kivitendo_to_datev, ({ kivi_datev_name => 'not yet implemented' }) x (116 - @kivitendo_to_datev);
279 # we can safely set these defaults
280 # TODO get length_of_accounts from DATEV.pm
281 my $today = DateTime->now_local;
282 my $created_on = $today->ymd('') . $today->hms('') . '000';
283 my $length_of_accounts = length(SL::DB::Manager::Chart->get_first(where => [charttype => 'A'])->accno) // 4;
284 my $default_curr = SL::DB::Default->get_default_currency;
286 # datev metadata and the string length limits
288 my %meta_datev_to_valid_length = (
294 my $datev = SL::DB::Manager::Datev->get_first();
296 while (my ($k, $v) = each %meta_datev_to_valid_length) {
297 next unless $datev->{$k};
298 $meta_datev{$k} = substr $datev->{$k}, 0, $v;
302 "EXTF", "510", 21, "Buchungsstapel", 7, $created_on, "", "ki",
303 "kivitendo-datev", "", $meta_datev{beraternr}, $meta_datev{mandantennr},
304 $self->first_day_of_fiscal_year->ymd(''), $length_of_accounts,
305 $self->from->ymd(''), $self->to->ymd(''), "", "", 1, "", $self->locked,
306 $default_curr, "", "", "",""
308 push @header, [ @header_row_1 ];
310 # second header row, just the column names
311 push @header, [ map { $_->{csv_header_name} } _kivitendo_to_datev() ];
319 my (@array_of_datev, @warnings);
320 my @csv_columns = _kivitendo_to_datev();
322 foreach my $row (@{ $self->datev_lines }) {
323 my @current_datev_row;
325 # 1. check all datev_lines and see if we have a defined value
326 # 2. if we don't have a defined value set a default if exists
328 foreach my $column (@csv_columns) {
329 if ($column->{kivi_datev_name} eq 'not yet implemented') {
330 push @current_datev_row, '';
333 my $data = $row->{$column->{kivi_datev_name}};
334 if (!defined $data) {
335 if (defined $column->{default}) {
336 $data = $column->{default};
338 die 'No sensible value or a sensible default found for the entry: ' . $column->{kivi_datev_name};
341 # checkpoint a: no undefined data. All strict checks now!
342 if (exists $column->{input_check} && !$column->{input_check}->($data)) {
343 die t8("Wrong field value '#1' for field '#2' for the transaction with amount '#3'",
344 $data, $column->{kivi_datev_name}, $row->{umsatz});
346 # checkpoint b: we can safely format the input
347 if ($column->{formatter}) {
348 $data = $column->{formatter}->($data);
350 # checkpoint c: all soft checks now, will pop up as a user warning
351 if (exists $column->{valid_check} && !$column->{valid_check}->($data)) {
352 push @warnings, t8("Wrong field value '#1' for field '#2' for the transaction" .
353 " with amount '#3'", $data, $column->{kivi_datev_name}, $row->{umsatz});
355 push @current_datev_row, $data;
357 push @array_of_datev, \@current_datev_row;
359 $self->warnings(\@warnings);
360 return \@array_of_datev;
366 $::form->format_amount({ numberformat => '1000,00' }, @_);
369 sub first_day_of_fiscal_year {
370 $_[0]->to->clone->truncate(to => 'year');
381 SL::DATEV::CSV - kivitendo DATEV CSV Specification
385 use SL::DATEV qw(:CONSTANTS);
388 my $startdate = DateTime->new(year => 2014, month => 9, day => 1);
389 my $enddate = DateTime->new(year => 2014, month => 9, day => 31);
390 my $datev = SL::DATEV->new(
391 exporttype => DATEV_ET_BUCHUNGEN,
392 format => DATEV_FORMAT_CSV,
396 $datev->generate_datev_data;
398 my $datev_csv = SL::DATEV::CSV->new(datev_lines => $datev->generate_datev_lines,
399 from => $datev->from,
401 locked => $datev->locked,
403 $datev_csv->header; # returns the required 2 rows of header ($aref = [ ["row1" ..], [ "row2" .. ] ]) as array of array
404 $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
405 $datev_csv->warnings; # returns warnings
408 # The above object methods can be directly chained to a CSV export function, like this:
409 my $csv_file = IO::File->new($somewhere_in_filesystem)') or die "Can't open: $!";
410 $csv->print($csv_file, $_) for @{ $datev_csv->header };
411 $csv->print($csv_file, $_) for @{ $datev_csv->lines };
413 $self->{warnings} = $datev_csv->warnings;
420 The parsing of the DATEV CSV is index based, therefore the correct
421 column must be present at the corresponding index, i.e.:
423 Field Name : Debit/Credit Label
424 Valid Values : 'S' or 'H'
427 The columns in C<@kivi_datev> are in the correct order and the
428 specific attributes are defined as a key value hash list for each entry.
430 The key names are the english translation according to the DATEV specs
431 (Leitfaden DATEV englisch).
433 The two attributes C<max_length> and C<type> are also set as specified
436 To link the structure to kivitendo data, each entry has the attribute C<kivi_datev_name>
437 which is by convention the key name as generated by DATEV->generate_datev_data.
438 A value of C<'not yet implemented'> indicates that this field has no
439 corresponding kivitendo data and will be given an empty value by DATEV->csv_buchungsexport.
444 This is an excerpt of the DATEV Format 2015 Specification for CSV-Header
449 The filename is subject to the following restrictions:
450 1. The filename must begin with the prefix DTVF_ or EXTF_.
451 2. The filename must end with .csv.
453 When exporting from or importing into DATEV applications, the filename is
454 marked with the prefix "DTVF_" (DATEV Format).
455 The prefix "DTVF_" is reserved for DATEV applications.
456 If you are using a third-party application to create a file in the DATEV format
457 that you want to import using batch processing, use the prefix "EXTF_"
460 =head2 File Structure
462 The file structure of the text file exported/imported is defined as follows
464 Line 1: Header (serves to assist in the interpretation of the following data)
466 Line 2: Headline (headline of the user data)
468 Line 3 – n: Records (user data)
470 For an valid example file take a look at doc/DATEV-2015/EXTF_Buchungsstapel.csv
473 =head2 Detailed Description
475 Line 1 must contain 11 fields.
477 Line 2 must contain 26 fields.
479 Line 3 - n: must contain 116 fields, a smaller subset is mandatory.
487 Constructor for CSV-DATEV export.
488 Checks mandantory params as described in section synopsis.
492 Helper function, returns true if a string is not empty and cp1252 encoded
493 For example some arabic utf-8 like ݐ will return false
497 Mostly all other header information are constants or metadata loaded
498 from SL::DB::Datev.pm.
500 Returns the first two entries for the header (see above: File Structure)
503 =item kivitendo_to_datev
505 Returns the data structure C<@datev_data> as an array
509 Lightweight wrapper for form->format_amount.
510 Expects a number in kivitendo database format and returns the same number
513 =item first_day_of_fiscal_year
515 Takes a look at $self->to to determine the first day of the fiscal year.
519 Generates the CSV-Format data for the CSV DATEV export and returns
520 an 2-dimensional array as an array_ref.
521 May additionally return a second array_ref with warnings.
523 Requires the same date fields as the constructor for a valid DATEV header.
525 Furthermore we assume that the first day of the fiscal year is
526 the first of January and we cannot guarantee that our data in kivitendo
527 is locked, that means a booking cannot be modified after a defined (vat tax)
529 Some validity checks (max_length and regex) will be done if the
530 data structure contains them and the field is defined.
532 To add or alter the structure of the data take a look at the C<@kivitendo_to_datev> structure.
538 One can circumevent the check of the warnings.quite easily,
539 becaus warnings are generated after the call to lines:
542 die if @{ $datev_csv->warnings };
543 somethin_with($datev_csv->lines);
546 my $lines = $datev_csv->lines;
547 die if @{ $datev_csv->warnings };
548 somethin_with($lines);