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);
13 use SL::Util qw(trim);
15 use Rose::Object::MakeMethods::Generic (
16 scalar => [ qw(datev_lines from to locked warnings) ],
19 my @kivitendo_to_datev = (
21 kivi_datev_name => 'umsatz',
22 csv_header_name => t8('Transaction Value'),
26 input_check => sub { my ($input) = @_; return (looks_like_number($input) && length($input) <= 13 && $input > 0) },
27 formatter => \&_format_amount,
28 valid_check => sub { my ($check) = @_; return ($check =~ m/^\d{1,10}(\,\d{1,2})?$/) },
31 kivi_datev_name => 'soll_haben_kennzeichen',
32 csv_header_name => t8('Debit/Credit Label'),
37 input_check => sub { my ($check) = @_; return ($check =~ m/^(S|H)$/) },
38 formatter => sub { my ($input) = @_; return $input eq 'H' ? 'H' : 'S' },
39 valid_check => sub { my ($check) = @_; return ($check =~ m/^(S|H)$/) },
42 kivi_datev_name => 'waehrung',
43 csv_header_name => t8('Transaction Value Currency Code'),
47 input_check => sub { my ($check) = @_; return ($check eq '' || $check =~ m/^[A-Z]{3}$/) },
48 valid_check => sub { my ($check) = @_; return ($check =~ m/^[A-Z]{3}$/) },
51 kivi_datev_name => 'wechselkurs',
52 csv_header_name => t8('Exchange Rate'),
56 valid_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]*\.?[0-9]*$/) },
59 kivi_datev_name => 'not yet implemented',
60 csv_header_name => t8('Base Transaction Value'),
63 kivi_datev_name => 'not yet implemented',
64 csv_header_name => t8('Base Transaction Value Currency Code'),
67 kivi_datev_name => 'konto',
68 csv_header_name => t8('Account'),
72 input_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]{4,9}$/) },
75 kivi_datev_name => 'gegenkonto',
76 csv_header_name => t8('Contra Account'),
80 input_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]{4,9}$/) },
83 kivi_datev_name => 'buchungsschluessel',
84 csv_header_name => t8('Posting Key'),
88 input_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]{0,2}$/) },
91 kivi_datev_name => 'datum',
92 csv_header_name => t8('Invoice Date'),
96 input_check => sub { my ($check) = @_; return (ref (DateTime->from_kivitendo($check)) eq 'DateTime') },
97 formatter => sub { my ($input) = @_; return DateTime->from_kivitendo($input)->strftime('%d%m') },
98 valid_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]{4}$/) },
101 kivi_datev_name => 'belegfeld1',
102 csv_header_name => t8('Invoice Field 1'),
106 input_check => sub { return 1 unless $::instance_conf->get_datev_export_format eq 'cp1252';
107 my ($text) = @_; check_encoding($text); },
108 valid_check => sub { return 1 if $::instance_conf->get_datev_export_format eq 'cp1252';
109 my ($text) = @_; check_encoding($text); },
110 formatter => sub { my ($input) = @_; return substr($input, 0, 12) },
113 kivi_datev_name => 'belegfeld2',
114 csv_header_name => t8('Invoice Field 2'),
118 input_check => sub { my ($check) = @_; return 1 unless $check; return (ref (DateTime->from_kivitendo($check)) eq 'DateTime') },
119 formatter => sub { my ($input) = @_; return '' unless $input; return trim(DateTime->from_kivitendo($input)->strftime('%e%m%y')) },
120 valid_check => sub { my ($check) = @_; return 1 unless $check; return ($check =~ m/^[0-9]{5,6}$/) },
123 kivi_datev_name => 'not yet implemented',
124 csv_header_name => t8('Discount'),
128 kivi_datev_name => 'buchungstext',
129 csv_header_name => t8('Posting Text'),
133 input_check => sub { return 1 unless $::instance_conf->get_datev_export_format eq 'cp1252';
134 my ($text) = @_; check_encoding($text); },
135 valid_check => sub { return 1 if $::instance_conf->get_datev_export_format eq 'cp1252';
136 my ($text) = @_; check_encoding($text); },
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',
151 kivi_datev_name => 'not yet implemented',
154 kivi_datev_name => 'not yet implemented',
155 csv_header_name => t8('Link to invoice'),
156 max_length => 210, # DMS Application shortcut and GUID
158 # "8DB85C02-4CC3-FF3E-06D7-7F87EEECCF3A".
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 => 'not yet implemented',
206 kivi_datev_name => 'not yet implemented',
209 kivi_datev_name => 'kost1',
210 csv_header_name => t8('Cost Center'),
214 input_check => sub { my ($text) = @_; return 1 unless $text; check_encoding($text); },
215 formatter => sub { my ($input) = @_; return substr($input, 0, 8) },
218 kivi_datev_name => 'kost2',
219 csv_header_name => t8('Cost Center'),
223 input_check => sub { my ($text) = @_; return 1 unless $text; check_encoding($text); },
224 formatter => sub { my ($input) = @_; return substr($input, 0, 8) },
227 kivi_datev_name => 'not yet implemented',
228 csv_header_name => t8('KOST Quantity'),
231 valid_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]{0,9}$/) },
234 kivi_datev_name => 'ustid',
235 csv_header_name => t8('EU Member State and VAT ID Number'),
241 return 1 if ('' eq $ustid);
243 return ($ustid =~ m/^CH|^[A-Z]{2}\w{5,13}$/);
245 formatter => sub { my ($input) = @_; $input =~ s/\s//g; return $input },
248 return 1 if ('' eq $ustid);
249 return ($ustid =~ m/^CH|^[A-Z]{2}\w{5,13}$/);
258 croak(t8('We need a valid from date')) unless (ref $data{from} eq 'DateTime');
259 croak(t8('We need a valid to date')) unless (ref $data{to} eq 'DateTime');
260 croak(t8('We need a array of datev_lines')) unless (ref $data{datev_lines} eq 'ARRAY');
262 my $obj = bless {}, $class;
263 $obj->$_($data{$_}) for keys %data;
269 return undef unless $test;
271 encode('Windows-1252', $test, Encode::FB_CROAK|Encode::LEAVE_SRC);
278 sub _kivitendo_to_datev {
279 @kivitendo_to_datev, ({ kivi_datev_name => 'not yet implemented' }) x (116 - @kivitendo_to_datev);
287 # we can safely set these defaults
288 # TODO get length_of_accounts from DATEV.pm
289 my $today = DateTime->now_local;
290 my $created_on = $today->ymd('') . $today->hms('') . '000';
291 my $length_of_accounts = length(SL::DB::Manager::Chart->get_first(where => [charttype => 'A'])->accno) // 4;
292 my $default_curr = SL::DB::Default->get_default_currency;
294 # datev metadata and the string length limits
296 my %meta_datev_to_valid_length = (
302 my $datev = SL::DB::Manager::Datev->get_first();
304 while (my ($k, $v) = each %meta_datev_to_valid_length) {
305 next unless $datev->{$k};
306 $meta_datev{$k} = substr $datev->{$k}, 0, $v;
310 "EXTF", "510", 21, "Buchungsstapel", 7, $created_on, "", "ki",
311 "kivitendo-datev", "", $meta_datev{beraternr}, $meta_datev{mandantennr},
312 $self->first_day_of_fiscal_year->ymd(''), $length_of_accounts,
313 $self->from->ymd(''), $self->to->ymd(''), "", "", 1, "", $self->locked,
314 $default_curr, "", "", "",""
316 push @header, [ @header_row_1 ];
318 # second header row, just the column names
319 push @header, [ map { $_->{csv_header_name} } _kivitendo_to_datev() ];
327 my (@array_of_datev, @warnings);
328 my @csv_columns = _kivitendo_to_datev();
330 foreach my $row (@{ $self->datev_lines }) {
331 my @current_datev_row;
333 # 1. check all datev_lines and see if we have a defined value
334 # 2. if we don't have a defined value set a default if exists
336 foreach my $column (@csv_columns) {
337 if ($column->{kivi_datev_name} eq 'not yet implemented') {
338 push @current_datev_row, '';
341 my $data = $row->{$column->{kivi_datev_name}};
342 if (!defined $data) {
343 if (defined $column->{default}) {
344 $data = $column->{default};
346 die 'No sensible value or a sensible default found for the entry: ' . $column->{kivi_datev_name};
349 # checkpoint a: no undefined data. All strict checks now!
350 if (exists $column->{input_check} && !$column->{input_check}->($data)) {
351 die t8("Wrong field value '#1' for field '#2' for the transaction with amount '#3'",
352 $data, $column->{kivi_datev_name}, $row->{umsatz});
354 # checkpoint b: we can safely format the input
355 if ($column->{formatter}) {
356 $data = $column->{formatter}->($data);
358 # checkpoint c: all soft checks now, will pop up as a user warning
359 if (exists $column->{valid_check} && !$column->{valid_check}->($data)) {
360 push @warnings, t8("Wrong field value '#1' for field '#2' for the transaction" .
361 " with amount '#3'", $data, $column->{kivi_datev_name}, $row->{umsatz});
363 push @current_datev_row, $data;
365 push @array_of_datev, \@current_datev_row;
367 $self->warnings(\@warnings);
368 return \@array_of_datev;
374 $::form->format_amount({ numberformat => '1000,00' }, @_);
377 sub first_day_of_fiscal_year {
378 $_[0]->to->clone->truncate(to => 'year');
389 SL::DATEV::CSV - kivitendo DATEV CSV Specification
393 use SL::DATEV qw(:CONSTANTS);
396 my $startdate = DateTime->new(year => 2014, month => 9, day => 1);
397 my $enddate = DateTime->new(year => 2014, month => 9, day => 31);
398 my $datev = SL::DATEV->new(
399 exporttype => DATEV_ET_BUCHUNGEN,
400 format => DATEV_FORMAT_CSV,
404 $datev->generate_datev_data;
406 my $datev_csv = SL::DATEV::CSV->new(datev_lines => $datev->generate_datev_lines,
407 from => $datev->from,
409 locked => $datev->locked,
411 $datev_csv->header; # returns the required 2 rows of header ($aref = [ ["row1" ..], [ "row2" .. ] ]) as array of array
412 $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
413 $datev_csv->warnings; # returns warnings
416 # The above object methods can be directly chained to a CSV export function, like this:
417 my $csv_file = IO::File->new($somewhere_in_filesystem)') or die "Can't open: $!";
418 $csv->print($csv_file, $_) for @{ $datev_csv->header };
419 $csv->print($csv_file, $_) for @{ $datev_csv->lines };
421 $self->{warnings} = $datev_csv->warnings;
428 The parsing of the DATEV CSV is index based, therefore the correct
429 column must be present at the corresponding index, i.e.:
431 Field Name : Debit/Credit Label
432 Valid Values : 'S' or 'H'
435 The columns in C<@kivi_datev> are in the correct order and the
436 specific attributes are defined as a key value hash list for each entry.
438 The key names are the english translation according to the DATEV specs
439 (Leitfaden DATEV englisch).
441 The two attributes C<max_length> and C<type> are also set as specified
444 To link the structure to kivitendo data, each entry has the attribute C<kivi_datev_name>
445 which is by convention the key name as generated by DATEV->generate_datev_data.
446 A value of C<'not yet implemented'> indicates that this field has no
447 corresponding kivitendo data and will be given an empty value by DATEV->csv_buchungsexport.
452 This is an excerpt of the DATEV Format 2015 Specification for CSV-Header
457 The filename is subject to the following restrictions:
458 1. The filename must begin with the prefix DTVF_ or EXTF_.
459 2. The filename must end with .csv.
461 When exporting from or importing into DATEV applications, the filename is
462 marked with the prefix "DTVF_" (DATEV Format).
463 The prefix "DTVF_" is reserved for DATEV applications.
464 If you are using a third-party application to create a file in the DATEV format
465 that you want to import using batch processing, use the prefix "EXTF_"
468 =head2 File Structure
470 The file structure of the text file exported/imported is defined as follows
472 Line 1: Header (serves to assist in the interpretation of the following data)
474 Line 2: Headline (headline of the user data)
476 Line 3 – n: Records (user data)
478 For an valid example file take a look at doc/DATEV-2015/EXTF_Buchungsstapel.csv
481 =head2 Detailed Description
483 Line 1 must contain 11 fields.
485 Line 2 must contain 26 fields.
487 Line 3 - n: must contain 116 fields, a smaller subset is mandatory.
495 Constructor for CSV-DATEV export.
496 Checks mandantory params as described in section synopsis.
500 Helper function, returns true if a string is not empty and cp1252 encoded
501 For example some arabic utf-8 like ݐ will return false
505 Mostly all other header information are constants or metadata loaded
506 from SL::DB::Datev.pm.
508 Returns the first two entries for the header (see above: File Structure)
511 =item kivitendo_to_datev
513 Returns the data structure C<@datev_data> as an array
517 Lightweight wrapper for form->format_amount.
518 Expects a number in kivitendo database format and returns the same number
521 =item first_day_of_fiscal_year
523 Takes a look at $self->to to determine the first day of the fiscal year.
527 Generates the CSV-Format data for the CSV DATEV export and returns
528 an 2-dimensional array as an array_ref.
529 May additionally return a second array_ref with warnings.
531 Requires the same date fields as the constructor for a valid DATEV header.
533 Furthermore we assume that the first day of the fiscal year is
534 the first of January and we cannot guarantee that our data in kivitendo
535 is locked, that means a booking cannot be modified after a defined (vat tax)
537 Some validity checks (max_length and regex) will be done if the
538 data structure contains them and the field is defined.
540 To add or alter the structure of the data take a look at the C<@kivitendo_to_datev> structure.
546 One can circumevent the check of the warnings.quite easily,
547 becaus warnings are generated after the call to lines:
550 die if @{ $datev_csv->warnings };
551 somethin_with($datev_csv->lines);
554 my $lines = $datev_csv->lines;
555 die if @{ $datev_csv->warnings };
556 somethin_with($lines);