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 { my ($text) = @_; check_encoding($text); },
107 formatter => sub { my ($input) = @_; return substr($input, 0, 12) },
110 kivi_datev_name => 'belegfeld2',
111 csv_header_name => t8('Invoice Field 2'),
115 input_check => sub { my ($check) = @_; return 1 unless $check; return (ref (DateTime->from_kivitendo($check)) eq 'DateTime') },
116 formatter => sub { my ($input) = @_; return '' unless $input; return trim(DateTime->from_kivitendo($input)->strftime('%e%m%y')) },
117 valid_check => sub { my ($check) = @_; return 1 unless $check; return ($check =~ m/^[0-9]{5,6}$/) },
120 kivi_datev_name => 'not yet implemented',
121 csv_header_name => t8('Discount'),
125 kivi_datev_name => 'buchungstext',
126 csv_header_name => t8('Posting Text'),
130 input_check => sub { my ($text) = @_; return 1 unless $text; check_encoding($text); },
131 formatter => sub { my ($input) = @_; return substr($input, 0, 60) },
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',
149 kivi_datev_name => 'not yet implemented',
150 csv_header_name => t8('Link to invoice'),
151 max_length => 210, # DMS Application shortcut and GUID
153 # "8DB85C02-4CC3-FF3E-06D7-7F87EEECCF3A".
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 => 'not yet implemented',
204 kivi_datev_name => 'kost1',
205 csv_header_name => t8('Cost Center'),
209 input_check => sub { my ($text) = @_; return 1 unless $text; check_encoding($text); },
210 formatter => sub { my ($input) = @_; return substr($input, 0, 8) },
213 kivi_datev_name => 'kost2',
214 csv_header_name => t8('Cost Center'),
218 input_check => sub { my ($text) = @_; return 1 unless $text; check_encoding($text); },
219 formatter => sub { my ($input) = @_; return substr($input, 0, 8) },
222 kivi_datev_name => 'not yet implemented',
223 csv_header_name => t8('KOST Quantity'),
226 valid_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]{0,9}$/) },
229 kivi_datev_name => 'ustid',
230 csv_header_name => t8('EU Member State and VAT ID Number'),
236 return 1 if ('' eq $ustid);
238 return ($ustid =~ m/^CH|^[A-Z]{2}\w{5,13}$/);
240 formatter => sub { my ($input) = @_; $input =~ s/\s//g; return $input },
243 return 1 if ('' eq $ustid);
244 return ($ustid =~ m/^CH|^[A-Z]{2}\w{5,13}$/);
253 croak(t8('We need a valid from date')) unless (ref $data{from} eq 'DateTime');
254 croak(t8('We need a valid to date')) unless (ref $data{to} eq 'DateTime');
255 croak(t8('We need a array of datev_lines')) unless (ref $data{datev_lines} eq 'ARRAY');
257 my $obj = bless {}, $class;
258 $obj->$_($data{$_}) for keys %data;
264 return undef unless $test;
266 encode('Windows-1252', $test, Encode::FB_CROAK|Encode::LEAVE_SRC);
273 sub _kivitendo_to_datev {
274 @kivitendo_to_datev, ({ kivi_datev_name => 'not yet implemented' }) x (116 - @kivitendo_to_datev);
282 # we can safely set these defaults
283 # TODO get length_of_accounts from DATEV.pm
284 my $today = DateTime->now_local;
285 my $created_on = $today->ymd('') . $today->hms('') . '000';
286 my $length_of_accounts = length(SL::DB::Manager::Chart->get_first(where => [charttype => 'A'])->accno) // 4;
287 my $default_curr = SL::DB::Default->get_default_currency;
289 # datev metadata and the string length limits
291 my %meta_datev_to_valid_length = (
297 my $datev = SL::DB::Manager::Datev->get_first();
299 while (my ($k, $v) = each %meta_datev_to_valid_length) {
300 next unless $datev->{$k};
301 $meta_datev{$k} = substr $datev->{$k}, 0, $v;
305 "EXTF", "510", 21, "Buchungsstapel", 7, $created_on, "", "ki",
306 "kivitendo-datev", "", $meta_datev{beraternr}, $meta_datev{mandantennr},
307 $self->first_day_of_fiscal_year->ymd(''), $length_of_accounts,
308 $self->from->ymd(''), $self->to->ymd(''), "", "", 1, "", $self->locked,
309 $default_curr, "", "", "",""
311 push @header, [ @header_row_1 ];
313 # second header row, just the column names
314 push @header, [ map { $_->{csv_header_name} } _kivitendo_to_datev() ];
322 my (@array_of_datev, @warnings);
323 my @csv_columns = _kivitendo_to_datev();
325 foreach my $row (@{ $self->datev_lines }) {
326 my @current_datev_row;
328 # 1. check all datev_lines and see if we have a defined value
329 # 2. if we don't have a defined value set a default if exists
331 foreach my $column (@csv_columns) {
332 if ($column->{kivi_datev_name} eq 'not yet implemented') {
333 push @current_datev_row, '';
336 my $data = $row->{$column->{kivi_datev_name}};
337 if (!defined $data) {
338 if (defined $column->{default}) {
339 $data = $column->{default};
341 die 'No sensible value or a sensible default found for the entry: ' . $column->{kivi_datev_name};
344 # checkpoint a: no undefined data. All strict checks now!
345 if (exists $column->{input_check} && !$column->{input_check}->($data)) {
346 die t8("Wrong field value '#1' for field '#2' for the transaction with amount '#3'",
347 $data, $column->{kivi_datev_name}, $row->{umsatz});
349 # checkpoint b: we can safely format the input
350 if ($column->{formatter}) {
351 $data = $column->{formatter}->($data);
353 # checkpoint c: all soft checks now, will pop up as a user warning
354 if (exists $column->{valid_check} && !$column->{valid_check}->($data)) {
355 push @warnings, t8("Wrong field value '#1' for field '#2' for the transaction" .
356 " with amount '#3'", $data, $column->{kivi_datev_name}, $row->{umsatz});
358 push @current_datev_row, $data;
360 push @array_of_datev, \@current_datev_row;
362 $self->warnings(\@warnings);
363 return \@array_of_datev;
369 $::form->format_amount({ numberformat => '1000,00' }, @_);
372 sub first_day_of_fiscal_year {
373 $_[0]->to->clone->truncate(to => 'year');
384 SL::DATEV::CSV - kivitendo DATEV CSV Specification
388 use SL::DATEV qw(:CONSTANTS);
391 my $startdate = DateTime->new(year => 2014, month => 9, day => 1);
392 my $enddate = DateTime->new(year => 2014, month => 9, day => 31);
393 my $datev = SL::DATEV->new(
394 exporttype => DATEV_ET_BUCHUNGEN,
395 format => DATEV_FORMAT_CSV,
399 $datev->generate_datev_data;
401 my $datev_csv = SL::DATEV::CSV->new(datev_lines => $datev->generate_datev_lines,
402 from => $datev->from,
404 locked => $datev->locked,
406 $datev_csv->header; # returns the required 2 rows of header ($aref = [ ["row1" ..], [ "row2" .. ] ]) as array of array
407 $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
408 $datev_csv->warnings; # returns warnings
411 # The above object methods can be directly chained to a CSV export function, like this:
412 my $csv_file = IO::File->new($somewhere_in_filesystem)') or die "Can't open: $!";
413 $csv->print($csv_file, $_) for @{ $datev_csv->header };
414 $csv->print($csv_file, $_) for @{ $datev_csv->lines };
416 $self->{warnings} = $datev_csv->warnings;
423 The parsing of the DATEV CSV is index based, therefore the correct
424 column must be present at the corresponding index, i.e.:
426 Field Name : Debit/Credit Label
427 Valid Values : 'S' or 'H'
430 The columns in C<@kivi_datev> are in the correct order and the
431 specific attributes are defined as a key value hash list for each entry.
433 The key names are the english translation according to the DATEV specs
434 (Leitfaden DATEV englisch).
436 The two attributes C<max_length> and C<type> are also set as specified
439 To link the structure to kivitendo data, each entry has the attribute C<kivi_datev_name>
440 which is by convention the key name as generated by DATEV->generate_datev_data.
441 A value of C<'not yet implemented'> indicates that this field has no
442 corresponding kivitendo data and will be given an empty value by DATEV->csv_buchungsexport.
447 This is an excerpt of the DATEV Format 2015 Specification for CSV-Header
452 The filename is subject to the following restrictions:
453 1. The filename must begin with the prefix DTVF_ or EXTF_.
454 2. The filename must end with .csv.
456 When exporting from or importing into DATEV applications, the filename is
457 marked with the prefix "DTVF_" (DATEV Format).
458 The prefix "DTVF_" is reserved for DATEV applications.
459 If you are using a third-party application to create a file in the DATEV format
460 that you want to import using batch processing, use the prefix "EXTF_"
463 =head2 File Structure
465 The file structure of the text file exported/imported is defined as follows
467 Line 1: Header (serves to assist in the interpretation of the following data)
469 Line 2: Headline (headline of the user data)
471 Line 3 – n: Records (user data)
473 For an valid example file take a look at doc/DATEV-2015/EXTF_Buchungsstapel.csv
476 =head2 Detailed Description
478 Line 1 must contain 11 fields.
480 Line 2 must contain 26 fields.
482 Line 3 - n: must contain 116 fields, a smaller subset is mandatory.
490 Constructor for CSV-DATEV export.
491 Checks mandantory params as described in section synopsis.
495 Helper function, returns true if a string is not empty and cp1252 encoded
496 For example some arabic utf-8 like ݐ will return false
500 Mostly all other header information are constants or metadata loaded
501 from SL::DB::Datev.pm.
503 Returns the first two entries for the header (see above: File Structure)
506 =item kivitendo_to_datev
508 Returns the data structure C<@datev_data> as an array
512 Lightweight wrapper for form->format_amount.
513 Expects a number in kivitendo database format and returns the same number
516 =item first_day_of_fiscal_year
518 Takes a look at $self->to to determine the first day of the fiscal year.
522 Generates the CSV-Format data for the CSV DATEV export and returns
523 an 2-dimensional array as an array_ref.
524 May additionally return a second array_ref with warnings.
526 Requires the same date fields as the constructor for a valid DATEV header.
528 Furthermore we assume that the first day of the fiscal year is
529 the first of January and we cannot guarantee that our data in kivitendo
530 is locked, that means a booking cannot be modified after a defined (vat tax)
532 Some validity checks (max_length and regex) will be done if the
533 data structure contains them and the field is defined.
535 To add or alter the structure of the data take a look at the C<@kivitendo_to_datev> structure.
541 One can circumevent the check of the warnings.quite easily,
542 becaus warnings are generated after the call to lines:
545 die if @{ $datev_csv->warnings };
546 somethin_with($datev_csv->lines);
549 my $lines = $datev_csv->lines;
550 die if @{ $datev_csv->warnings };
551 somethin_with($lines);