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