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}$/);
253 kivi_datev_name => 'not yet implemented',
256 kivi_datev_name => 'not yet implemented',
259 kivi_datev_name => 'not yet implemented',
262 kivi_datev_name => 'not yet implemented',
265 kivi_datev_name => 'not yet implemented',
268 kivi_datev_name => 'not yet implemented',
271 kivi_datev_name => 'not yet implemented',
274 kivi_datev_name => 'not yet implemented',
277 kivi_datev_name => 'not yet implemented',
280 kivi_datev_name => 'not yet implemented',
283 kivi_datev_name => 'not yet implemented',
286 kivi_datev_name => 'not yet implemented',
289 kivi_datev_name => 'not yet implemented',
292 kivi_datev_name => 'not yet implemented',
295 kivi_datev_name => 'not yet implemented',
298 kivi_datev_name => 'not yet implemented',
301 kivi_datev_name => 'not yet implemented',
304 kivi_datev_name => 'not yet implemented',
307 kivi_datev_name => 'not yet implemented',
310 kivi_datev_name => 'not yet implemented',
313 kivi_datev_name => 'not yet implemented',
316 kivi_datev_name => 'not yet implemented',
319 kivi_datev_name => 'not yet implemented',
322 kivi_datev_name => 'not yet implemented',
325 kivi_datev_name => 'not yet implemented',
328 kivi_datev_name => 'not yet implemented',
331 kivi_datev_name => 'not yet implemented',
334 kivi_datev_name => 'not yet implemented',
337 kivi_datev_name => 'not yet implemented',
340 kivi_datev_name => 'not yet implemented',
343 kivi_datev_name => 'not yet implemented',
346 kivi_datev_name => 'not yet implemented',
349 kivi_datev_name => 'not yet implemented',
352 kivi_datev_name => 'not yet implemented',
355 kivi_datev_name => 'not yet implemented',
358 kivi_datev_name => 'not yet implemented',
361 kivi_datev_name => 'not yet implemented',
364 kivi_datev_name => 'not yet implemented',
367 kivi_datev_name => 'not yet implemented',
370 kivi_datev_name => 'not yet implemented',
373 kivi_datev_name => 'not yet implemented',
376 kivi_datev_name => 'not yet implemented',
379 kivi_datev_name => 'not yet implemented',
382 kivi_datev_name => 'not yet implemented',
385 kivi_datev_name => 'not yet implemented',
388 kivi_datev_name => 'not yet implemented',
391 kivi_datev_name => 'not yet implemented',
394 kivi_datev_name => 'not yet implemented',
397 kivi_datev_name => 'not yet implemented',
400 kivi_datev_name => 'not yet implemented',
403 kivi_datev_name => 'not yet implemented',
406 kivi_datev_name => 'not yet implemented',
409 kivi_datev_name => 'not yet implemented',
412 kivi_datev_name => 'not yet implemented',
415 kivi_datev_name => 'not yet implemented',
418 kivi_datev_name => 'not yet implemented',
421 kivi_datev_name => 'not yet implemented',
424 kivi_datev_name => 'not yet implemented',
427 kivi_datev_name => 'not yet implemented',
430 kivi_datev_name => 'not yet implemented',
433 kivi_datev_name => 'not yet implemented',
436 kivi_datev_name => 'not yet implemented',
439 kivi_datev_name => 'not yet implemented',
442 kivi_datev_name => 'not yet implemented',
445 kivi_datev_name => 'not yet implemented',
448 kivi_datev_name => 'not yet implemented',
451 kivi_datev_name => 'not yet implemented',
454 kivi_datev_name => 'not yet implemented',
457 kivi_datev_name => 'not yet implemented',
460 kivi_datev_name => 'not yet implemented',
463 kivi_datev_name => 'not yet implemented',
466 kivi_datev_name => 'not yet implemented',
469 kivi_datev_name => 'not yet implemented',
472 kivi_datev_name => 'locked',
473 csv_header_name => t8('Lock'),
477 valid_check => sub { my ($check) = @_; return ($check =~ m/^(0|1)$/) },
480 kivi_datev_name => 'not yet implemented',
483 kivi_datev_name => 'not yet implemented',
486 kivi_datev_name => 'not yet implemented',
489 kivi_datev_name => 'not yet implemented',
492 kivi_datev_name => 'not yet implemented',
495 kivi_datev_name => 'not yet implemented',
503 croak(t8('We need a valid from date')) unless (ref $data{from} eq 'DateTime');
504 croak(t8('We need a valid to date')) unless (ref $data{to} eq 'DateTime');
505 croak(t8('We need a array of datev_lines')) unless (ref $data{datev_lines} eq 'ARRAY');
507 my $obj = bless {}, $class;
508 $obj->$_($data{$_}) for keys %data;
514 return undef unless $test;
516 encode('Windows-1252', $test, Encode::FB_CROAK|Encode::LEAVE_SRC);
528 # we can safely set these defaults
529 # TODO get length_of_accounts from DATEV.pm
530 my $today = DateTime->now_local;
531 my $created_on = $today->ymd('') . $today->hms('') . '000';
532 my $length_of_accounts = length(SL::DB::Manager::Chart->get_first(where => [charttype => 'A'])->accno) // 4;
533 my $default_curr = SL::DB::Default->get_default_currency;
535 # datev metadata and the string length limits
537 my %meta_datev_to_valid_length = (
543 my $datev = SL::DB::Manager::Datev->get_first();
545 while (my ($k, $v) = each %meta_datev_to_valid_length) {
546 next unless $datev->{$k};
547 $meta_datev{$k} = substr $datev->{$k}, 0, $v;
551 "EXTF", "510", 21, "Buchungsstapel", 7, $created_on, "", "ki",
552 "kivitendo-datev", "", $meta_datev{beraternr}, $meta_datev{mandantennr},
553 $self->first_day_of_fiscal_year->ymd(''), $length_of_accounts,
554 $self->from->ymd(''), $self->to->ymd(''), "", "", 1, "", $self->locked,
555 $default_curr, "", "", "",""
557 push @header, [ @header_row_1 ];
559 # second header row, just the column names
560 push @header, [ map { $_->{csv_header_name} } @kivitendo_to_datev ];
568 my (@array_of_datev, @warnings);
570 foreach my $row (@{ $self->datev_lines }) {
571 my @current_datev_row;
573 # 1. check all datev_lines and see if we have a defined value
574 # 2. if we don't have a defined value set a default if exists
576 foreach my $column (@kivitendo_to_datev) {
577 if ($column->{kivi_datev_name} eq 'not yet implemented') {
578 push @current_datev_row, '';
581 my $data = $row->{$column->{kivi_datev_name}};
582 if (!defined $data) {
583 if (defined $column->{default}) {
584 $data = $column->{default};
586 die 'No sensible value or a sensible default found for the entry: ' . $column->{kivi_datev_name};
589 # checkpoint a: no undefined data. All strict checks now!
590 if (exists $column->{input_check} && !$column->{input_check}->($data)) {
591 die t8("Wrong field value '#1' for field '#2' for the transaction with amount '#3'",
592 $data, $column->{kivi_datev_name}, $row->{umsatz});
594 # checkpoint b: we can safely format the input
595 if ($column->{formatter}) {
596 $data = $column->{formatter}->($data);
598 # checkpoint c: all soft checks now, will pop up as a user warning
599 if (exists $column->{valid_check} && !$column->{valid_check}->($data)) {
600 push @warnings, t8("Wrong field value '#1' for field '#2' for the transaction" .
601 " with amount '#3'", $data, $column->{kivi_datev_name}, $row->{umsatz});
603 push @current_datev_row, $data;
605 push @array_of_datev, \@current_datev_row;
607 $self->warnings(\@warnings);
608 return \@array_of_datev;
614 $::form->format_amount({ numberformat => '1000,00' }, @_);
617 sub first_day_of_fiscal_year {
618 $_[0]->to->clone->truncate(to => 'year');
629 SL::DATEV::CSV - kivitendo DATEV CSV Specification
633 use SL::DATEV qw(:CONSTANTS);
636 my $startdate = DateTime->new(year => 2014, month => 9, day => 1);
637 my $enddate = DateTime->new(year => 2014, month => 9, day => 31);
638 my $datev = SL::DATEV->new(
639 exporttype => DATEV_ET_BUCHUNGEN,
640 format => DATEV_FORMAT_CSV,
644 $datev->generate_datev_data;
646 my $datev_csv = SL::DATEV::CSV->new(datev_lines => $datev->generate_datev_lines,
647 from => $datev->from,
649 locked => $datev->locked,
651 $datev_csv->header; # returns the required 2 rows of header ($aref = [ ["row1" ..], [ "row2" .. ] ]) as array of array
652 $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
653 $datev_csv->warnings; # returns warnings
656 # The above object methods can be directly chained to a CSV export function, like this:
657 my $csv_file = IO::File->new($somewhere_in_filesystem)') or die "Can't open: $!";
658 $csv->print($csv_file, $_) for @{ $datev_csv->header };
659 $csv->print($csv_file, $_) for @{ $datev_csv->lines };
661 $self->{warnings} = $datev_csv->warnings;
668 The parsing of the DATEV CSV is index based, therefore the correct
669 column must be present at the corresponding index, i.e.:
671 Field Name : Debit/Credit Label
672 Valid Values : 'S' or 'H'
675 The columns in C<@kivi_datev> are in the correct order and the
676 specific attributes are defined as a key value hash list for each entry.
678 The key names are the english translation according to the DATEV specs
679 (Leitfaden DATEV englisch).
681 The two attributes C<max_length> and C<type> are also set as specified
684 To link the structure to kivitendo data, each entry has the attribute C<kivi_datev_name>
685 which is by convention the key name as generated by DATEV->generate_datev_data.
686 A value of C<'not yet implemented'> indicates that this field has no
687 corresponding kivitendo data and will be given an empty value by DATEV->csv_buchungsexport.
692 This is an excerpt of the DATEV Format 2015 Specification for CSV-Header
697 The filename is subject to the following restrictions:
698 1. The filename must begin with the prefix DTVF_ or EXTF_.
699 2. The filename must end with .csv.
701 When exporting from or importing into DATEV applications, the filename is
702 marked with the prefix "DTVF_" (DATEV Format).
703 The prefix "DTVF_" is reserved for DATEV applications.
704 If you are using a third-party application to create a file in the DATEV format
705 that you want to import using batch processing, use the prefix "EXTF_"
708 =head2 File Structure
710 The file structure of the text file exported/imported is defined as follows
712 Line 1: Header (serves to assist in the interpretation of the following data)
714 Line 2: Headline (headline of the user data)
716 Line 3 – n: Records (user data)
718 For an valid example file take a look at doc/DATEV-2015/EXTF_Buchungsstapel.csv
721 =head2 Detailed Description
723 Line 1 must contain 11 fields.
725 Line 2 must contain 26 fields.
727 Line 3 - n: must contain 116 fields, a smaller subset is mandatory.
735 Constructor for CSV-DATEV export.
736 Checks mandantory params as described in section synopsis.
740 Helper function, returns true if a string is not empty and cp1252 encoded
741 For example some arabic utf-8 like ݐ will return false
745 Mostly all other header information are constants or metadata loaded
746 from SL::DB::Datev.pm.
748 Returns the first two entries for the header (see above: File Structure)
751 =item kivitendo_to_datev
753 Returns the data structure C<@datev_data> as an array
757 Lightweight wrapper for form->format_amount.
758 Expects a number in kivitendo database format and returns the same number
761 =item first_day_of_fiscal_year
763 Takes a look at $self->to to determine the first day of the fiscal year.
767 Generates the CSV-Format data for the CSV DATEV export and returns
768 an 2-dimensional array as an array_ref.
769 May additionally return a second array_ref with warnings.
771 Requires the same date fields as the constructor for a valid DATEV header.
773 Furthermore we assume that the first day of the fiscal year is
774 the first of January and we cannot guarantee that our data in kivitendo
775 is locked, that means a booking cannot be modified after a defined (vat tax)
777 Some validity checks (max_length and regex) will be done if the
778 data structure contains them and the field is defined.
780 To add or alter the structure of the data take a look at the C<@kivitendo_to_datev> structure.
786 One can circumevent the check of the warnings.quite easily,
787 becaus warnings are generated after the call to lines:
790 die if @{ $datev_csv->warnings };
791 somethin_with($datev_csv->lines);
794 my $lines = $datev_csv->lines;
795 die if @{ $datev_csv->warnings };
796 somethin_with($lines);