1 package SL::DATEV::CSV;
5 use SL::Locale::String qw(t8);
7 use SL::Helper::DateTime;
11 use Encode qw(decode);
12 use Scalar::Util qw(looks_like_number);
15 my @kivitendo_to_datev = (
17 kivi_datev_name => 'umsatz',
18 csv_header_name => t8('Transaction Value'),
22 input_check => sub { my ($input) = @_; return (looks_like_number($input) && length($input) <= 13) },
23 formatter => sub { my ($input) = @_; return _format_amount($input) },
24 valid_check => sub { my ($check) = @_; return ($check =~ m/^\d{1,10}(\,\d{1,2})?$/) },
27 kivi_datev_name => 'soll_haben_kennzeichen',
28 csv_header_name => t8('Debit/Credit Label'),
33 input_check => sub { my ($check) = @_; return ($check =~ m/^(S|H)$/) },
34 formatter => sub { my ($input) = @_; return $input eq 'H' ? 'H' : 'S' },
35 valid_check => sub { my ($check) = @_; return ($check =~ m/^(S|H)$/) },
38 kivi_datev_name => 'waehrung',
39 csv_header_name => t8('Transaction Value Currency Code'),
43 input_check => sub { my ($check) = @_; return ($check eq '' || $check =~ m/^[A-Z]{3}$/) },
44 valid_check => sub { my ($check) = @_; return ($check =~ m/^[A-Z]{3}$/) },
47 kivi_datev_name => 'wechselkurs',
48 csv_header_name => t8('Exchange Rate'),
52 valid_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]*\.?[0-9]*$/) },
55 kivi_datev_name => 'not yet implemented',
56 sv_header_name => t8('Base Transaction Value'),
59 kivi_datev_name => 'not yet implemented',
60 csv_header_name => t8('Base Transaction Value Currency Code'),
63 kivi_datev_name => 'konto',
64 csv_header_name => t8('Account'),
68 input_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]{4,9}$/) },
71 kivi_datev_name => 'gegenkonto',
72 csv_header_name => t8('Contra Account'),
76 input_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]{4,9}$/) },
79 kivi_datev_name => 'buchungsschluessel',
80 csv_header_name => t8('Posting Key'),
84 input_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]{0,2}$/) },
87 kivi_datev_name => 'datum',
88 csv_header_name => t8('Invoice Date'),
92 input_check => sub { my ($check) = @_; return (ref (DateTime->from_kivitendo($check)) eq 'DateTime') },
93 formatter => sub { my ($input) = @_; return DateTime->from_kivitendo($input)->strftime('%d%m') },
94 valid_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]{4}$/) },
97 kivi_datev_name => 'belegfeld1',
98 csv_header_name => t8('Invoice Field 1'),
102 input_check => sub { my ($text) = @_; check_encoding($text); },
103 formatter => sub { my ($input) = @_; return substr($input, 0, 12) },
106 kivi_datev_name => 'not yet implemented',
107 csv_header_name => t8('Invoice Field 2'),
111 valid_check => sub { my ($check) = @_; return ($check =~ m/[ -~]{1,12}/) },
114 kivi_datev_name => 'not yet implemented',
115 csv_header_name => t8('Discount'),
119 kivi_datev_name => 'buchungsbes',
120 csv_header_name => t8('Posting Text'),
124 input_check => sub { my ($text) = @_; return 1 unless $text; check_encoding($text); },
125 formatter => sub { my ($input) = @_; return substr($input, 0, 60) },
128 kivi_datev_name => 'not yet implemented',
131 kivi_datev_name => 'not yet implemented',
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',
144 csv_header_name => t8('Link to invoice'),
145 max_length => 210, # DMS Application shortcut and GUID
147 # "8DB85C02-4CC3-FF3E-06D7-7F87EEECCF3A".
150 kivi_datev_name => 'not yet implemented',
153 kivi_datev_name => 'not yet implemented',
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 => 'kost1',
199 csv_header_name => t8('Cost Center'),
203 input_check => sub { my ($text) = @_; return 1 unless $text; check_encoding($text); },
204 formatter => sub { my ($input) = @_; return substr($input, 0, 8) },
207 kivi_datev_name => 'kost2',
208 csv_header_name => t8('Cost Center'),
212 input_check => sub { my ($text) = @_; return 1 unless $text; check_encoding($text); },
213 formatter => sub { my ($input) = @_; return substr($input, 0, 8) },
216 kivi_datev_name => 'not yet implemented',
217 csv_header_name => t8('KOST Quantity'),
220 valid_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]{0,9}$/) },
223 kivi_datev_name => 'ustid',
224 csv_header_name => t8('EU Member State and VAT ID Number'),
228 input_check => sub { my ($check) = @_; return ($check eq '' || $check =~ m/[A-Z]{2}\w{5,13}/) },
229 formatter => sub { my ($input) = @_; return ($input =~ s/\s//g) },
232 return 1 if ('' eq $ustid);
233 return ($ustid =~ m/^CH|^[A-Z]{2}\w{5,13}$/);
242 my $obj = bless {}, $class;
244 croak(t8('We need a valid from date')) unless (ref $data{from} eq 'DateTime');
245 croak(t8('We need a valid to date')) unless (ref $data{to} eq 'DateTime');
246 croak(t8('We need a array of datev_lines')) unless (ref $data{datev_lines} eq 'ARRAY');
248 # TODO no params here, better class variables/values
249 return _csv_buchungsexport(from => $data{from},
251 datev_lines => $data{datev_lines},
252 locked => $data{locked},
260 return undef unless $test;
262 decode('Windows-1252', $test, Encode::FB_CROAK|Encode::LEAVE_SRC);
269 sub _kivitendo_to_datev {
272 my $entries = scalar (@kivitendo_to_datev);
273 push @kivitendo_to_datev, { kivi_datev_name => 'not yet implemented' } for 1 .. (116 - $entries);
274 return @kivitendo_to_datev;
277 sub _generate_csv_header {
280 # we need from and to in YYYYDDMM
281 croak "Wrong format for from $params{from}" unless $params{from} =~ m/^[0-9]{8}$/;
282 croak "Wrong format for to $params{to}" unless $params{to} =~ m/^[0-9]{8}$/;
284 # who knows if we want locking and when our fiscal year starts
285 # croak "Wrong state of locking" unless $params{locked} =~ m/^(0|1)$/;
286 my $locked = defined($params{locked}) ? 1 : 0;
287 croak "No startdate of fiscal year" unless $params{first_day_of_fiscal_year} =~ m/^[0-9]{8}$/;
290 # we can safely set these defaults
291 my $today = DateTime->now(time_zone => "local");
292 my $created_on = $today->ymd('') . $today->hms('') . '000';
293 my $length_of_accounts = length(SL::DB::Manager::Chart->get_first(where => [charttype => 'A'])->accno) // 4;
294 my $default_curr = SL::DB::Default->get_default_currency;
296 # datev metadata and the string length limits
298 my %meta_datev_to_valid_length = (
304 my $datev = SL::DB::Manager::Datev->get_first();
306 while (my ($k, $v) = each %meta_datev_to_valid_length) {
307 $meta_datev{$k} = substr $datev->{$k}, 0, $v;
311 "EXTF", "300", 21, "Buchungsstapel", 7, $created_on, "", "ki",
312 "kivitendo-datev", "", $meta_datev{beraternr}, $meta_datev{mandantennr},
313 $params{first_day_of_fiscal_year}, $length_of_accounts,
314 $params{from}, $params{to}, "", "", 1, "", $locked,
315 $default_curr, "", "", "",""
321 sub _csv_buchungsexport {
324 my @csv_columns = _kivitendo_to_datev();
325 my @csv_headers = _generate_csv_header(
326 from => $params{from}->ymd(''),
327 to => $params{to}->ymd(''),
328 first_day_of_fiscal_year => $params{to}->year . '0101',
329 locked => $params{locked}
335 push @array_of_datev, \@csv_headers;
336 push @array_of_datev, [ map { $_->{csv_header_name} } @csv_columns ];
339 foreach my $row (@{ $params{datev_lines} }) {
340 my @current_datev_row;
342 # 1. check all datev_lines and see if we have a defined value
343 # 2. if we don't have a defined value set a default if exists
345 foreach my $column (@csv_columns) {
346 if ($column->{kivi_datev_name} eq 'not yet implemented') {
347 push @current_datev_row, '';
350 my $data = $row->{$column->{kivi_datev_name}};
351 if (!defined $data) {
352 if (defined $column->{default}) {
353 $data = $column->{default};
355 die 'No sensible value or a sensible default found for the entry: ' . $column->{kivi_datev_name};
358 # checkpoint a: no undefined data. All strict checks now!
359 if (exists $column->{input_check}) {
360 die t8("Wrong field value '#1' for field '#2' for the transaction with amount '#3'",
361 $data, $column->{kivi_datev_name}, $row->{umsatz})
362 unless $column->{input_check}->($data);
364 # checkpoint b: we can safely format the input
365 if ($column->{formatter}) {
366 $data = $column->{formatter}->($data);
368 # checkpoint c: all soft checks now, will pop up as a user warning
369 if (exists $column->{valid_check} && !$column->{valid_check}->($data)) {
370 push @warnings, t8("Wrong field value '#1' for field '#2' for the transaction" .
371 " with amount '#3'", $data, $column->{kivi_datev_name}, $row->{umsatz});
373 push @current_datev_row, $data;
375 push @array_of_datev, \@current_datev_row;
377 return (\@array_of_datev, \@warnings);
381 $::form->format_amount({ numberformat => '1000,00' }, @_);
392 SL::DATEV::CSV - kivitendo DATEV CSV Specification
396 use SL::DATEV qw(:CONSTANTS);
399 my $startdate = DateTime->new(year => 2014, month => 9, day => 1);
400 my $enddate = DateTime->new(year => 2014, month => 9, day => 31);
401 my $datev = SL::DATEV->new(
402 exporttype => DATEV_ET_BUCHUNGEN,
403 format => DATEV_FORMAT_CSV,
407 $datev->generate_datev_data;
409 my $datev_ref = SL::DATEV::CSV->new(datev_lines => $datev->generate_datev_lines,
410 from => $datev->from,
412 locked => $datev->locked,
417 The parsing of the DATEV CSV is index based, therefore the correct
418 column must be present at the corresponding index, i.e.:
420 Field Name : Debit/Credit Label
421 Valid Values : 'S' or 'H'
424 The columns in C<@kivi_datev> are in the correct order and the
425 specific attributes are defined as a key value hash list for each entry.
427 The key names are the english translation according to the DATEV specs
428 (Leitfaden DATEV englisch).
430 The two attributes C<max_length> and C<type> are also set as specified
433 To link the structure to kivitendo data, each entry has the attribute C<kivi_datev_name>
434 which is by convention the key name as generated by DATEV->generate_datev_data.
435 A value of C<'not yet implemented'> indicates that this field has no
436 corresponding kivitendo data and will be given an empty value by DATEV->csv_buchungsexport.
441 This is an excerpt of the DATEV Format 2015 Specification for CSV-Header
446 The filename is subject to the following restrictions:
447 1. The filename must begin with the prefix DTVF_ or EXTF_.
448 2. The filename must end with .csv.
450 When exporting from or importing into DATEV applications, the filename is
451 marked with the prefix "DTVF_" (DATEV Format).
452 The prefix "DTVF_" is reserved for DATEV applications.
453 If you are using a third-party application to create a file in the DATEV format
454 that you want to import using batch processing, use the prefix "EXTF_"
457 =head2 File Structure
459 The file structure of the text file exported/imported is defined as follows
461 Line 1: Header (serves to assist in the interpretation of the following data)
463 Line 2: Headline (headline of the user data)
465 Line 3 – n: Records (user data)
467 For an valid example file take a look at doc/DATEV-2015/EXTF_Buchungsstapel.csv
470 =head2 Detailed Description
472 Line 1 must contain 11 fields.
474 Line 2 must contain 26 fields.
476 Line 3 - n: must contain 116 fields, a smaller subset is mandatory.
484 Constructor for CSV-DATEV export.
485 Checks mandantory params as described in section synopsis.
489 Helper function, returns true if a string is not empty and cp1252 encoded
490 For example some arabic utf-8 like ݐ will return false
492 =item generate_csv_header(from => 'YYYYDDMM', to => 'YYYYDDMM', locked => 0,
493 first_day_of_fiscal_year => 'YYYYDDMM')
495 Mostly all other header information are constants or metadata loaded
496 from SL::DB::Datev.pm.
498 Returns the first two entries for the header (see above: File Structure)
501 All params are mandatory:
502 C<params{from}>, C<params{to}>
503 and C<params{first_day_of_fiscal_year}> have to be in YYYYDDMM date string
505 Furthermore C<params{locked}> is a perlish boolean.
508 =item kivitendo_to_datev
510 Returns the data structure C<@datev_data> as an array
514 Lightweight wrapper for form->format_amount.
515 Expects a number in kivitendo database format and returns the same number
518 =item _csv_buchungsexport
520 Generates the CSV-Format data for the CSV DATEV export and returns
521 an 2-dimensional array as an array_ref.
522 May additionally return a second array_ref with warnings.
524 Requires the same date fields as the constructor for a valid DATEV header.
526 Furthermore we assume that the first day of the fiscal year is
527 the first of January and we cannot guarantee that our data in kivitendo
528 is locked, that means a booking cannot be modified after a defined (vat tax)
530 Some validity checks (max_length and regex) will be done if the
531 data structure contains them and the field is defined.
533 To add or alter the structure of the data take a look at the C<@kivitendo_to_datev> structure.