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);
16 use Rose::Object::MakeMethods::Generic (
17 scalar => [ qw(datev_lines from to locked warnings) ],
20 my @kivitendo_to_datev = (
22 kivi_datev_name => 'umsatz',
23 csv_header_name => t8('Transaction Value'),
27 input_check => sub { my ($input) = @_; return (looks_like_number($input) && length($input) <= 13 && $input > 0) },
28 formatter => \&_format_amount,
29 valid_check => sub { my ($check) = @_; return ($check =~ m/^\d{1,10}(\,\d{1,2})?$/) },
32 kivi_datev_name => 'soll_haben_kennzeichen',
33 csv_header_name => t8('Debit/Credit Label'),
38 input_check => sub { my ($check) = @_; return ($check =~ m/^(S|H)$/) },
39 formatter => sub { my ($input) = @_; return $input eq 'H' ? 'H' : 'S' },
40 valid_check => sub { my ($check) = @_; return ($check =~ m/^(S|H)$/) },
43 kivi_datev_name => 'waehrung',
44 csv_header_name => t8('Transaction Value Currency Code'),
48 input_check => sub { my ($check) = @_; return ($check eq '' || $check =~ m/^[A-Z]{3}$/) },
49 valid_check => sub { my ($check) = @_; return ($check =~ m/^[A-Z]{3}$/) },
52 kivi_datev_name => 'wechselkurs',
53 csv_header_name => t8('Exchange Rate'),
57 valid_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]*\.?[0-9]*$/) },
60 kivi_datev_name => 'not yet implemented',
61 csv_header_name => t8('Base Transaction Value'),
64 kivi_datev_name => 'not yet implemented',
65 csv_header_name => t8('Base Transaction Value Currency Code'),
68 kivi_datev_name => 'konto',
69 csv_header_name => t8('Account'),
73 input_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]{4,9}$/) },
76 kivi_datev_name => 'gegenkonto',
77 csv_header_name => t8('Contra Account'),
81 input_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]{4,9}$/) },
84 kivi_datev_name => 'buchungsschluessel',
85 csv_header_name => t8('Posting Key'),
89 input_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]{0,2}$/) },
92 kivi_datev_name => 'datum',
93 csv_header_name => t8('Invoice Date'),
97 input_check => sub { my ($check) = @_; return (ref (DateTime->from_kivitendo($check)) eq 'DateTime') },
98 formatter => sub { my ($input) = @_; return DateTime->from_kivitendo($input)->strftime('%d%m') },
99 valid_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]{4}$/) },
102 kivi_datev_name => 'belegfeld1',
103 csv_header_name => t8('Invoice Field 1'),
107 input_check => sub { return 1 unless $::instance_conf->get_datev_export_format eq 'cp1252';
108 my ($text) = @_; check_encoding($text); },
109 valid_check => sub { return 1 if $::instance_conf->get_datev_export_format eq 'cp1252';
110 my ($text) = @_; check_encoding($text); },
111 formatter => sub { my ($input) = @_; return substr($input, 0, 12) },
114 kivi_datev_name => 'belegfeld2',
115 csv_header_name => t8('Invoice Field 2'),
119 input_check => sub { my ($check) = @_; return 1 unless $check; return (ref (DateTime->from_kivitendo($check)) eq 'DateTime') },
120 formatter => sub { my ($input) = @_; return '' unless $input; return trim(DateTime->from_kivitendo($input)->strftime('%e%m%y')) },
121 valid_check => sub { my ($check) = @_; return 1 unless $check; return ($check =~ m/^[0-9]{5,6}$/) },
124 kivi_datev_name => 'not yet implemented',
125 csv_header_name => t8('Discount'),
129 kivi_datev_name => 'buchungstext',
130 csv_header_name => t8('Posting Text'),
134 input_check => sub { return 1 unless $::instance_conf->get_datev_export_format eq 'cp1252';
135 my ($text) = @_; check_encoding($text); },
136 valid_check => sub { return 1 if $::instance_conf->get_datev_export_format eq 'cp1252';
137 my ($text) = @_; check_encoding($text); },
138 formatter => sub { my ($input) = @_; return substr($input, 0, 60) },
141 kivi_datev_name => 'not yet implemented',
144 kivi_datev_name => 'not yet implemented',
147 kivi_datev_name => 'not yet implemented',
150 kivi_datev_name => 'not yet implemented',
153 kivi_datev_name => 'not yet implemented',
156 kivi_datev_name => 'not yet implemented',
157 csv_header_name => t8('Link to invoice'),
158 max_length => 210, # DMS Application shortcut and GUID
160 # "8DB85C02-4CC3-FF3E-06D7-7F87EEECCF3A".
163 kivi_datev_name => 'not yet implemented',
166 kivi_datev_name => 'not yet implemented',
169 kivi_datev_name => 'not yet implemented',
172 kivi_datev_name => 'not yet implemented',
175 kivi_datev_name => 'not yet implemented',
178 kivi_datev_name => 'not yet implemented',
181 kivi_datev_name => 'not yet implemented',
184 kivi_datev_name => 'not yet implemented',
187 kivi_datev_name => 'not yet implemented',
190 kivi_datev_name => 'not yet implemented',
193 kivi_datev_name => 'not yet implemented',
196 kivi_datev_name => 'not yet implemented',
199 kivi_datev_name => 'not yet implemented',
202 kivi_datev_name => 'not yet implemented',
205 kivi_datev_name => 'not yet implemented',
208 kivi_datev_name => 'not yet implemented',
211 kivi_datev_name => 'kost1',
212 csv_header_name => t8('Cost Center'),
216 input_check => sub { my ($text) = @_; return 1 unless $text; check_encoding($text); },
217 formatter => sub { my ($input) = @_; return substr($input, 0, 8) },
220 kivi_datev_name => 'kost2',
221 csv_header_name => t8('Cost Center'),
225 input_check => sub { my ($text) = @_; return 1 unless $text; check_encoding($text); },
226 formatter => sub { my ($input) = @_; return substr($input, 0, 8) },
229 kivi_datev_name => 'not yet implemented',
230 csv_header_name => t8('KOST Quantity'),
233 valid_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]{0,9}$/) },
236 kivi_datev_name => 'ustid',
237 csv_header_name => t8('EU Member State and VAT ID Number'),
243 return 1 if ('' eq $ustid);
244 return SL::VATIDNr->validate($ustid);
246 formatter => sub { my ($input) = @_; $input =~ s/\s//g; return $input },
249 return 1 if ('' eq $ustid);
250 return SL::VATIDNr->validate($ustid);
254 kivi_datev_name => 'not yet implemented',
257 kivi_datev_name => 'not yet implemented',
260 kivi_datev_name => 'not yet implemented',
263 kivi_datev_name => 'not yet implemented',
266 kivi_datev_name => 'not yet implemented',
269 kivi_datev_name => 'not yet implemented',
272 kivi_datev_name => 'not yet implemented',
275 kivi_datev_name => 'not yet implemented',
278 kivi_datev_name => 'not yet implemented',
281 kivi_datev_name => 'not yet implemented',
284 kivi_datev_name => 'not yet implemented',
287 kivi_datev_name => 'not yet implemented',
290 kivi_datev_name => 'not yet implemented',
293 kivi_datev_name => 'not yet implemented',
296 kivi_datev_name => 'not yet implemented',
299 kivi_datev_name => 'not yet implemented',
302 kivi_datev_name => 'not yet implemented',
305 kivi_datev_name => 'not yet implemented',
308 kivi_datev_name => 'not yet implemented',
311 kivi_datev_name => 'not yet implemented',
314 kivi_datev_name => 'not yet implemented',
317 kivi_datev_name => 'not yet implemented',
320 kivi_datev_name => 'not yet implemented',
323 kivi_datev_name => 'not yet implemented',
326 kivi_datev_name => 'not yet implemented',
329 kivi_datev_name => 'not yet implemented',
332 kivi_datev_name => 'not yet implemented',
335 kivi_datev_name => 'not yet implemented',
338 kivi_datev_name => 'not yet implemented',
341 kivi_datev_name => 'not yet implemented',
344 kivi_datev_name => 'not yet implemented',
347 kivi_datev_name => 'not yet implemented',
350 kivi_datev_name => 'not yet implemented',
353 kivi_datev_name => 'not yet implemented',
356 kivi_datev_name => 'not yet implemented',
359 kivi_datev_name => 'not yet implemented',
362 kivi_datev_name => 'not yet implemented',
365 kivi_datev_name => 'not yet implemented',
368 kivi_datev_name => 'not yet implemented',
371 kivi_datev_name => 'not yet implemented',
374 kivi_datev_name => 'not yet implemented',
377 kivi_datev_name => 'not yet implemented',
380 kivi_datev_name => 'not yet implemented',
383 kivi_datev_name => 'not yet implemented',
386 kivi_datev_name => 'not yet implemented',
389 kivi_datev_name => 'not yet implemented',
392 kivi_datev_name => 'not yet implemented',
395 kivi_datev_name => 'not yet implemented',
398 kivi_datev_name => 'not yet implemented',
401 kivi_datev_name => 'not yet implemented',
404 kivi_datev_name => 'not yet implemented',
407 kivi_datev_name => 'not yet implemented',
410 kivi_datev_name => 'not yet implemented',
413 kivi_datev_name => 'not yet implemented',
416 kivi_datev_name => 'not yet implemented',
419 kivi_datev_name => 'not yet implemented',
422 kivi_datev_name => 'not yet implemented',
425 kivi_datev_name => 'not yet implemented',
428 kivi_datev_name => 'not yet implemented',
431 kivi_datev_name => 'not yet implemented',
434 kivi_datev_name => 'not yet implemented',
437 kivi_datev_name => 'not yet implemented',
440 kivi_datev_name => 'not yet implemented',
443 kivi_datev_name => 'not yet implemented',
446 kivi_datev_name => 'not yet implemented',
449 kivi_datev_name => 'not yet implemented',
452 kivi_datev_name => 'not yet implemented',
455 kivi_datev_name => 'not yet implemented',
458 kivi_datev_name => 'not yet implemented',
461 kivi_datev_name => 'not yet implemented',
464 kivi_datev_name => 'not yet implemented',
467 kivi_datev_name => 'not yet implemented',
470 kivi_datev_name => 'not yet implemented',
473 kivi_datev_name => 'locked',
474 csv_header_name => t8('Lock'),
478 valid_check => sub { my ($check) = @_; return ($check =~ m/^(0|1)$/) },
481 kivi_datev_name => 'not yet implemented',
484 kivi_datev_name => 'not yet implemented',
487 kivi_datev_name => 'not yet implemented',
490 kivi_datev_name => 'not yet implemented',
493 kivi_datev_name => 'not yet implemented',
496 kivi_datev_name => 'not yet implemented',
504 croak(t8('We need a valid from date')) unless (ref $data{from} eq 'DateTime');
505 croak(t8('We need a valid to date')) unless (ref $data{to} eq 'DateTime');
506 croak(t8('We need a array of datev_lines')) unless (ref $data{datev_lines} eq 'ARRAY');
508 my $obj = bless {}, $class;
509 $obj->$_($data{$_}) for keys %data;
515 return undef unless $test;
517 encode('Windows-1252', $test, Encode::FB_CROAK|Encode::LEAVE_SRC);
529 # we can safely set these defaults
530 # TODO get length_of_accounts from DATEV.pm
531 my $today = DateTime->now_local;
532 my $created_on = $today->ymd('') . $today->hms('') . '000';
533 my $length_of_accounts = length(SL::DB::Manager::Chart->get_first(where => [charttype => 'A'])->accno) // 4;
534 my $default_curr = SL::DB::Default->get_default_currency;
536 # datev metadata and the string length limits
538 my %meta_datev_to_valid_length = (
544 my $datev = SL::DB::Manager::Datev->get_first();
546 while (my ($k, $v) = each %meta_datev_to_valid_length) {
547 next unless $datev->{$k};
548 $meta_datev{$k} = substr $datev->{$k}, 0, $v;
552 "EXTF", "510", 21, "Buchungsstapel", 7, $created_on, "", "ki",
553 "kivitendo-datev", "", $meta_datev{beraternr}, $meta_datev{mandantennr},
554 $self->first_day_of_fiscal_year->ymd(''), $length_of_accounts,
555 $self->from->ymd(''), $self->to->ymd(''), "", "", 1, "", $self->locked,
556 $default_curr, "", "", "",""
558 push @header, [ @header_row_1 ];
560 # second header row, just the column names
561 push @header, [ map { $_->{csv_header_name} } @kivitendo_to_datev ];
569 my (@array_of_datev, @warnings);
571 foreach my $row (@{ $self->datev_lines }) {
572 my @current_datev_row;
574 # 1. check all datev_lines and see if we have a defined value
575 # 2. if we don't have a defined value set a default if exists
577 foreach my $column (@kivitendo_to_datev) {
578 if ($column->{kivi_datev_name} eq 'not yet implemented') {
579 push @current_datev_row, '';
582 my $data = $row->{$column->{kivi_datev_name}};
583 if (!defined $data) {
584 if (defined $column->{default}) {
585 $data = $column->{default};
587 die 'No sensible value or a sensible default found for the entry: ' . $column->{kivi_datev_name};
590 # checkpoint a: no undefined data. All strict checks now!
591 if (exists $column->{input_check} && !$column->{input_check}->($data)) {
592 die t8("Wrong field value '#1' for field '#2' for the transaction with amount '#3'",
593 $data, $column->{kivi_datev_name}, $row->{umsatz});
595 # checkpoint b: we can safely format the input
596 if ($column->{formatter}) {
597 $data = $column->{formatter}->($data);
599 # checkpoint c: all soft checks now, will pop up as a user warning
600 if (exists $column->{valid_check} && !$column->{valid_check}->($data)) {
601 push @warnings, t8("Wrong field value '#1' for field '#2' for the transaction" .
602 " with amount '#3'", $data, $column->{kivi_datev_name}, $row->{umsatz});
604 push @current_datev_row, $data;
606 push @array_of_datev, \@current_datev_row;
608 $self->warnings(\@warnings);
609 return \@array_of_datev;
615 $::form->format_amount({ numberformat => '1000,00' }, @_);
618 sub first_day_of_fiscal_year {
619 $_[0]->to->clone->truncate(to => 'year');
630 SL::DATEV::CSV - kivitendo DATEV CSV Specification
634 use SL::DATEV qw(:CONSTANTS);
637 my $startdate = DateTime->new(year => 2014, month => 9, day => 1);
638 my $enddate = DateTime->new(year => 2014, month => 9, day => 31);
639 my $datev = SL::DATEV->new(
640 exporttype => DATEV_ET_BUCHUNGEN,
641 format => DATEV_FORMAT_CSV,
645 $datev->generate_datev_data;
647 my $datev_csv = SL::DATEV::CSV->new(datev_lines => $datev->generate_datev_lines,
648 from => $datev->from,
650 locked => $datev->locked,
652 $datev_csv->header; # returns the required 2 rows of header ($aref = [ ["row1" ..], [ "row2" .. ] ]) as array of array
653 $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
654 $datev_csv->warnings; # returns warnings
657 # The above object methods can be directly chained to a CSV export function, like this:
658 my $csv_file = IO::File->new($somewhere_in_filesystem)') or die "Can't open: $!";
659 $csv->print($csv_file, $_) for @{ $datev_csv->header };
660 $csv->print($csv_file, $_) for @{ $datev_csv->lines };
662 $self->{warnings} = $datev_csv->warnings;
669 The parsing of the DATEV CSV is index based, therefore the correct
670 column must be present at the corresponding index, i.e.:
672 Field Name : Debit/Credit Label
673 Valid Values : 'S' or 'H'
676 The columns in C<@kivi_datev> are in the correct order and the
677 specific attributes are defined as a key value hash list for each entry.
679 The key names are the english translation according to the DATEV specs
680 (Leitfaden DATEV englisch).
682 The two attributes C<max_length> and C<type> are also set as specified
685 To link the structure to kivitendo data, each entry has the attribute C<kivi_datev_name>
686 which is by convention the key name as generated by DATEV->generate_datev_data.
687 A value of C<'not yet implemented'> indicates that this field has no
688 corresponding kivitendo data and will be given an empty value by DATEV->csv_buchungsexport.
693 This is an excerpt of the DATEV Format 2015 Specification for CSV-Header
698 The filename is subject to the following restrictions:
699 1. The filename must begin with the prefix DTVF_ or EXTF_.
700 2. The filename must end with .csv.
702 When exporting from or importing into DATEV applications, the filename is
703 marked with the prefix "DTVF_" (DATEV Format).
704 The prefix "DTVF_" is reserved for DATEV applications.
705 If you are using a third-party application to create a file in the DATEV format
706 that you want to import using batch processing, use the prefix "EXTF_"
709 =head2 File Structure
711 The file structure of the text file exported/imported is defined as follows
713 Line 1: Header (serves to assist in the interpretation of the following data)
715 Line 2: Headline (headline of the user data)
717 Line 3 – n: Records (user data)
719 For an valid example file take a look at doc/DATEV-2015/EXTF_Buchungsstapel.csv
722 =head2 Detailed Description
724 Line 1 must contain 11 fields.
726 Line 2 must contain 26 fields.
728 Line 3 - n: must contain 116 fields, a smaller subset is mandatory.
736 Constructor for CSV-DATEV export.
737 Checks mandantory params as described in section synopsis.
741 Helper function, returns true if a string is not empty and cp1252 encoded
742 For example some arabic utf-8 like ݐ will return false
746 Mostly all other header information are constants or metadata loaded
747 from SL::DB::Datev.pm.
749 Returns the first two entries for the header (see above: File Structure)
752 =item kivitendo_to_datev
754 Returns the data structure C<@datev_data> as an array
758 Lightweight wrapper for form->format_amount.
759 Expects a number in kivitendo database format and returns the same number
762 =item first_day_of_fiscal_year
764 Takes a look at $self->to to determine the first day of the fiscal year.
768 Generates the CSV-Format data for the CSV DATEV export and returns
769 an 2-dimensional array as an array_ref.
770 May additionally return a second array_ref with warnings.
772 Requires the same date fields as the constructor for a valid DATEV header.
774 Furthermore we assume that the first day of the fiscal year is
775 the first of January and we cannot guarantee that our data in kivitendo
776 is locked, that means a booking cannot be modified after a defined (vat tax)
778 Some validity checks (max_length and regex) will be done if the
779 data structure contains them and the field is defined.
781 To add or alter the structure of the data take a look at the C<@kivitendo_to_datev> structure.
787 One can circumevent the check of the warnings.quite easily,
788 becaus warnings are generated after the call to lines:
791 die if @{ $datev_csv->warnings };
792 somethin_with($datev_csv->lines);
795 my $lines = $datev_csv->lines;
796 die if @{ $datev_csv->warnings };
797 somethin_with($lines);