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); },
137 formatter => sub { my ($input) = @_; return substr($input, 0, 60) },
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',
152 kivi_datev_name => 'not yet implemented',
155 kivi_datev_name => 'not yet implemented',
156 csv_header_name => t8('Link to invoice'),
157 max_length => 210, # DMS Application shortcut and GUID
159 # "8DB85C02-4CC3-FF3E-06D7-7F87EEECCF3A".
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 => 'not yet implemented',
207 kivi_datev_name => 'not yet implemented',
210 kivi_datev_name => 'kost1',
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 => 'kost2',
220 csv_header_name => t8('Cost Center'),
224 input_check => sub { my ($text) = @_; return 1 unless $text; check_encoding($text); },
225 formatter => sub { my ($input) = @_; return substr($input, 0, 8) },
228 kivi_datev_name => 'not yet implemented',
229 csv_header_name => t8('KOST Quantity'),
232 valid_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]{0,9}$/) },
235 kivi_datev_name => 'ustid',
236 csv_header_name => t8('EU Member State and VAT ID Number'),
242 return 1 if ('' eq $ustid);
244 return ($ustid =~ m/^CH|^[A-Z]{2}\w{5,13}$/);
246 formatter => sub { my ($input) = @_; $input =~ s/\s//g; return $input },
249 return 1 if ('' eq $ustid);
250 return ($ustid =~ m/^CH|^[A-Z]{2}\w{5,13}$/);
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);