SL/DATEV/CSV.pm

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