DATEV::CSV safer order of libs
[kivitendo-erp.git] / SL / DATEV / CSV.pm
1 package SL::DATEV::CSV;
2
3 use strict;
4
5 use SL::Locale::String qw(t8);
6 use SL::DB::Datev;
7 use DateTime; # safer before the Helper
8 use SL::Helper::DateTime;
9
10 use Carp;
11 use Encode qw(decode);
12 use Scalar::Util qw(looks_like_number);
13
14
15 my @kivitendo_to_datev = (
16                             {
17                               kivi_datev_name => 'umsatz',
18                               csv_header_name => t8('Transaction Value'),
19                               max_length      => 13,
20                               type            => 'Value',
21                               required        => 1,
22                               input_check     => sub { my ($input) = @_; return (looks_like_number($input) && length($input) <= 13) },
23                               formatter       => \&_format_amount,
24                               valid_check     => sub { my ($check) = @_; return ($check =~ m/^\d{1,10}(\,\d{1,2})?$/) },
25                             },
26                             {
27                               kivi_datev_name => 'soll_haben_kennzeichen',
28                               csv_header_name => t8('Debit/Credit Label'),
29                               max_length      => 1,
30                               type            => 'Text',
31                               required        => 1,
32                               default         => 'S',
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)$/) },
36                             },
37                             {
38                               kivi_datev_name => 'waehrung',
39                               csv_header_name => t8('Transaction Value Currency Code'),
40                               max_length      => 3,
41                               type            => 'Text',
42                               default         => '',
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}$/) },
45                             },
46                             {
47                               kivi_datev_name => 'wechselkurs',
48                               csv_header_name => t8('Exchange Rate'),
49                               max_length      => 11,
50                               type            => 'Number',
51                               default         => '',
52                               valid_check     => sub { my ($check) = @_; return ($check =~ m/^[0-9]*\.?[0-9]*$/) },
53                             },
54                             {
55                               kivi_datev_name => 'not yet implemented',
56                               sv_header_name => t8('Base Transaction Value'),
57                             },
58                             {
59                               kivi_datev_name => 'not yet implemented',
60                               csv_header_name => t8('Base Transaction Value Currency Code'),
61                             },
62                             {
63                               kivi_datev_name => 'konto',
64                               csv_header_name => t8('Account'),
65                               max_length      => 9,
66                               type            => 'Account',
67                               required        => 1,
68                               input_check     => sub { my ($check) = @_; return ($check =~ m/^[0-9]{4,9}$/) },
69                             },
70                             {
71                               kivi_datev_name => 'gegenkonto',
72                               csv_header_name => t8('Contra Account'),
73                               max_length      => 9,
74                               type            => 'Account',
75                               required        => 1,
76                               input_check     => sub { my ($check) = @_; return ($check =~ m/^[0-9]{4,9}$/) },
77                             },
78                             {
79                               kivi_datev_name => 'buchungsschluessel',
80                               csv_header_name => t8('Posting Key'),
81                               max_length      => 2,
82                               type            => 'Text',
83                               default         => '',
84                               input_check     => sub { my ($check) = @_; return ($check =~ m/^[0-9]{0,2}$/) },
85                             },
86                             {
87                               kivi_datev_name => 'datum',
88                               csv_header_name => t8('Invoice Date'),
89                               max_length      => 4,
90                               type            => 'Date',
91                               required        => 1,
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}$/) },
95                             },
96                             {
97                               kivi_datev_name => 'belegfeld1',
98                               csv_header_name => t8('Invoice Field 1'),
99                               max_length      => 12,
100                               type            => 'Text',
101                               default         => '',
102                               input_check     => sub { my ($text) = @_; check_encoding($text); },
103                               formatter       => sub { my ($input) = @_; return substr($input, 0, 12) },
104                             },
105                             {
106                               kivi_datev_name => 'not yet implemented',
107                               csv_header_name => t8('Invoice Field 2'),
108                               max_length      => 12,
109                               type            => 'Text',
110                               default         => '',
111                               valid_check     => sub { my ($check) = @_; return ($check =~ m/[ -~]{1,12}/) },
112                             },
113                             {
114                               kivi_datev_name => 'not yet implemented',
115                               csv_header_name => t8('Discount'),
116                               type            => 'Value',
117                             },
118                             {
119                               kivi_datev_name => 'buchungsbes',
120                               csv_header_name => t8('Posting Text'),
121                               max_length      => 60,
122                               type            => 'Text',
123                               default         => '',
124                               input_check     => sub { my ($text) = @_; return 1 unless $text; check_encoding($text);  },
125                               formatter       => sub { my ($input) = @_; return substr($input, 0, 60) },
126                             },  # pos 14
127                             {
128                               kivi_datev_name => 'not yet implemented',
129                             },
130                             {
131                               kivi_datev_name => 'not yet implemented',
132                             },
133                             {
134                               kivi_datev_name => 'not yet implemented',
135                             },
136                             {
137                               kivi_datev_name => 'not yet implemented',
138                             },
139                             {
140                               kivi_datev_name => 'not yet implemented',
141                             },
142                             {
143                               kivi_datev_name => 'not yet implemented',
144                               csv_header_name => t8('Link to invoice'),
145                               max_length      => 210, # DMS Application shortcut and GUID
146                                                       # Example: "BEDI"
147                                                       # "8DB85C02-4CC3-FF3E-06D7-7F87EEECCF3A".
148                             }, # pos 20
149                             {
150                               kivi_datev_name => 'not yet implemented',
151                             },
152                             {
153                               kivi_datev_name => 'not yet implemented',
154                             },
155                             {
156                               kivi_datev_name => 'not yet implemented',
157                             },
158                             {
159                               kivi_datev_name => 'not yet implemented',
160                             },
161                             {
162                               kivi_datev_name => 'not yet implemented',
163                             },
164                             {
165                               kivi_datev_name => 'not yet implemented',
166                             },
167                             {
168                               kivi_datev_name => 'not yet implemented',
169                             },
170                             {
171                               kivi_datev_name => 'not yet implemented',
172                             },
173                             {
174                               kivi_datev_name => 'not yet implemented',
175                             },
176                             {
177                               kivi_datev_name => 'not yet implemented',
178                             },
179                             {
180                               kivi_datev_name => 'not yet implemented',
181                             },
182                             {
183                               kivi_datev_name => 'not yet implemented',
184                             },
185                             {
186                               kivi_datev_name => 'not yet implemented',
187                             },
188                             {
189                               kivi_datev_name => 'not yet implemented',
190                             },
191                             {
192                               kivi_datev_name => 'not yet implemented',
193                             },
194                             {
195                               kivi_datev_name => 'not yet implemented',
196                             },
197                             {
198                               kivi_datev_name => 'kost1',
199                               csv_header_name => t8('Cost Center'),
200                               max_length      => 8,
201                               type            => 'Text',
202                               default         => '',
203                               input_check     => sub { my ($text) = @_; return 1 unless $text; check_encoding($text);  },
204                               formatter       => sub { my ($input) = @_; return substr($input, 0, 8) },
205                             }, # pos 37
206                             {
207                               kivi_datev_name => 'kost2',
208                               csv_header_name => t8('Cost Center'),
209                               max_length      => 8,
210                               type            => 'Text',
211                               default         => '',
212                               input_check     => sub { my ($text) = @_; return 1 unless $text; check_encoding($text);  },
213                               formatter       => sub { my ($input) = @_; return substr($input, 0, 8) },
214                             }, # pos 38
215                             {
216                               kivi_datev_name => 'not yet implemented',
217                               csv_header_name => t8('KOST Quantity'),
218                               max_length      => 9,
219                               type            => 'Number',
220                               valid_check     => sub { my ($check) = @_; return ($check =~ m/^[0-9]{0,9}$/) },
221                             }, # pos 39
222                             {
223                               kivi_datev_name => 'ustid',
224                               csv_header_name => t8('EU Member State and VAT ID Number'),
225                               max_length      => 15,
226                               type            => 'Text',
227                               default         => '',
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) },
230                               valid_check     => sub {
231                                                        my ($ustid) = @_;
232                                                        return 1 if ('' eq $ustid);
233                                                        return ($ustid =~ m/^CH|^[A-Z]{2}\w{5,13}$/);
234                                                      },
235                             }, # pos 40
236   );
237
238 sub new {
239   my $class = shift;
240   my %data  = @_;
241
242   my $obj = bless {}, $class;
243
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');
247
248   # TODO no params here, better class variables/values
249   return _csv_buchungsexport(from        => $data{from},
250                              to          => $data{to},
251                              datev_lines => $data{datev_lines},
252                              locked      => $data{locked},
253                             );
254
255   $obj;
256 }
257
258 sub check_encoding {
259   my ($test) = @_;
260   return undef unless $test;
261   if (eval {
262     decode('Windows-1252', $test, Encode::FB_CROAK|Encode::LEAVE_SRC);
263     1
264   }) {
265     return 1;
266   }
267 }
268
269 sub _kivitendo_to_datev {
270   my ($self) = @_;
271
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;
275 }
276
277 sub _generate_csv_header {
278   my %params  = @_;
279
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}$/;
283
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}$/;
288
289
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;
295
296   # datev metadata and the string length limits
297   my %meta_datev;
298   my %meta_datev_to_valid_length = (
299     beraternr   =>  7,
300     beratername => 25,
301     mandantennr =>  5,
302   );
303
304   my $datev = SL::DB::Manager::Datev->get_first();
305
306   while (my ($k, $v) = each %meta_datev_to_valid_length) {
307     next unless $datev->{$k};
308     $meta_datev{$k} = substr $datev->{$k}, 0, $v;
309   }
310
311   my @header = (
312     "EXTF", "300", 21, "Buchungsstapel", 7, $created_on, "", "ki",
313     "kivitendo-datev", "", $meta_datev{beraternr}, $meta_datev{mandantennr},
314     $params{first_day_of_fiscal_year}, $length_of_accounts,
315     $params{from}, $params{to}, "", "", 1, "", $locked,
316     $default_curr, "", "", "",""
317   );
318
319   return @header;
320 }
321
322 sub _csv_buchungsexport {
323   my %params = @_;
324
325   my @csv_columns = _kivitendo_to_datev();
326   my @csv_headers = _generate_csv_header(
327                       from                     => $params{from}->ymd(''),
328                       to                       => $params{to}->ymd(''),
329                       first_day_of_fiscal_year => $params{to}->year . '0101',
330                       locked                   => $params{locked}
331                     );
332
333   my @array_of_datev;
334
335   # 2 Headers
336   push @array_of_datev, \@csv_headers;
337   push @array_of_datev, [ map { $_->{csv_header_name} } @csv_columns ];
338
339   my @warnings;
340   foreach my $row (@{ $params{datev_lines} }) {
341     my @current_datev_row;
342
343     # 1. check all datev_lines and see if we have a defined value
344     # 2. if we don't have a defined value set a default if exists
345     # 3. otherwise die
346     foreach my $column (@csv_columns) {
347       if ($column->{kivi_datev_name} eq 'not yet implemented') {
348         push @current_datev_row, '';
349         next;
350       }
351       my $data = $row->{$column->{kivi_datev_name}};
352       if (!defined $data) {
353         if (defined $column->{default}) {
354           $data = $column->{default};
355         } else {
356            die 'No sensible value or a sensible default found for the entry: ' . $column->{kivi_datev_name};
357         }
358       }
359       # checkpoint a: no undefined data. All strict checks now!
360       if (exists $column->{input_check}) {
361         die t8("Wrong field value '#1' for field '#2' for the transaction with amount '#3'",
362                 $data, $column->{kivi_datev_name}, $row->{umsatz})
363           unless  $column->{input_check}->($data);
364       }
365       # checkpoint b: we can safely format the input
366       if ($column->{formatter}) {
367         $data = $column->{formatter}->($data);
368       }
369       # checkpoint c: all soft checks now, will pop up as a user warning
370       if (exists $column->{valid_check} && !$column->{valid_check}->($data)) {
371         push @warnings, t8("Wrong field value '#1' for field '#2' for the transaction" .
372                            " with amount '#3'", $data, $column->{kivi_datev_name}, $row->{umsatz});
373       }
374       push @current_datev_row, $data;
375     }
376     push @array_of_datev, \@current_datev_row;
377   }
378   return (\@array_of_datev, \@warnings);
379 }
380
381 sub _format_amount {
382   $::form->format_amount({ numberformat => '1000,00' }, @_);
383 }
384
385 1;
386
387 __END__
388
389 =encoding utf-8
390
391 =head1 NAME
392
393 SL::DATEV::CSV - kivitendo DATEV CSV Specification
394
395 =head1 SYNOPSIS
396
397   use SL::DATEV qw(:CONSTANTS);
398   use SL::DATEV::CSV;
399
400   my $startdate = DateTime->new(year => 2014, month => 9, day => 1);
401   my $enddate   = DateTime->new(year => 2014, month => 9, day => 31);
402   my $datev = SL::DATEV->new(
403     exporttype => DATEV_ET_BUCHUNGEN,
404     format     => DATEV_FORMAT_CSV,
405     from       => $startdate,
406     to         => $enddate,
407   );
408   $datev->generate_datev_data;
409
410   my $datev_ref = SL::DATEV::CSV->new(datev_lines  => $datev->generate_datev_lines,
411                                       from         => $datev->from,
412                                       to           => $datev->to,
413                                       locked       => $datev->locked,
414                                      );
415
416 =head1 DESCRIPTION
417
418 The parsing of the DATEV CSV is index based, therefore the correct
419 column must be present at the corresponding index, i.e.:
420  Index 2
421  Field Name   : Debit/Credit Label
422  Valid Values : 'S' or 'H'
423  Length:      : 1
424
425 The columns in C<@kivi_datev> are in the correct order and the
426 specific attributes are defined as a key value hash list for each entry.
427
428 The key names are the english translation according to the DATEV specs
429 (Leitfaden DATEV englisch).
430
431 The two attributes C<max_length> and C<type> are also set as specified
432 by the DATEV specs.
433
434 To link the structure to kivitendo data, each entry has the attribute C<kivi_datev_name>
435 which is by convention the key name as generated by DATEV->generate_datev_data.
436 A value of C<'not yet implemented'> indicates that this field has no
437 corresponding kivitendo data and will be given an empty value by DATEV->csv_buchungsexport.
438
439
440 =head1 SPECIFICATION
441
442 This is an excerpt of the DATEV Format 2015 Specification for CSV-Header
443 and CSV-Data lines.
444
445 =head2 FILENAME
446
447 The filename is subject to the following restrictions:
448 1. The filename must begin with the prefix DTVF_ or EXTF_.
449 2. The filename must end with .csv.
450
451 When exporting from or importing into DATEV applications, the filename is
452 marked with the prefix "DTVF_" (DATEV Format).
453 The prefix "DTVF_" is reserved for DATEV applications.
454 If you are using a third-party application to create a file in the DATEV format
455 that you want to import using batch processing, use the prefix "EXTF_"
456 (External Format).
457
458 =head2 File Structure
459
460 The file structure of the text file exported/imported is defined as follows
461
462 Line 1: Header (serves to assist in the interpretation of the following data)
463
464 Line 2: Headline (headline of the user data)
465
466 Line 3 – n: Records (user data)
467
468 For an valid example file take a look at doc/DATEV-2015/EXTF_Buchungsstapel.csv
469
470
471 =head2 Detailed Description
472
473 Line 1 must contain 11 fields.
474
475 Line 2 must contain 26 fields.
476
477 Line 3 - n:  must contain 116 fields, a smaller subset is mandatory.
478
479 =head1 FUNCTIONS
480
481 =over 4
482
483 =item new PARAMS
484
485 Constructor for CSV-DATEV export.
486 Checks mandantory params as described in section synopsis.
487
488 =item check_encoding
489
490 Helper function, returns true if a string is not empty and cp1252 encoded
491 For example some arabic utf-8 like  ݐ  will return false
492
493 =item generate_csv_header(from => 'YYYYDDMM', to => 'YYYYDDMM', locked => 0,
494                           first_day_of_fiscal_year => 'YYYYDDMM')
495
496 Mostly all other header information are constants or metadata loaded
497 from SL::DB::Datev.pm.
498
499 Returns the first two entries for the header (see above: File Structure)
500 as an array.
501
502 All params are mandatory:
503 C<params{from}>,  C<params{to}>
504 and C<params{first_day_of_fiscal_year}> have to be in YYYYDDMM date string
505 format.
506 Furthermore C<params{locked}> is a perlish boolean.
507
508
509 =item kivitendo_to_datev
510
511 Returns the data structure C<@datev_data> as an array
512
513 =item _format_amount
514
515 Lightweight wrapper for form->format_amount.
516 Expects a number in kivitendo database format and returns the same number
517 in DATEV format.
518
519 =item _csv_buchungsexport
520
521 Generates the CSV-Format data for the CSV DATEV export and returns
522 an 2-dimensional array as an array_ref.
523 May additionally return a second array_ref with warnings.
524
525 Requires the same date fields as the constructor for a valid DATEV header.
526
527 Furthermore we assume that the first day of the fiscal year is
528 the first of January and we cannot guarantee that our data in kivitendo
529 is locked, that means a booking cannot be modified after a defined (vat tax)
530 period.
531 Some validity checks (max_length and regex) will be done if the
532 data structure contains them and the field is defined.
533
534 To add or alter the structure of the data take a look at the C<@kivitendo_to_datev> structure.
535
536
537 =back