SL/DATEV/CSV.pm

   1 package SL::DATEV::CSV;
   2
   3 use strict;
   4 use Carp;
   5 use DateTime;
   6 use Encode qw(decode);
   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                               sv_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 => 'buchungsbes',
 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 { my ($check) = @_; return ($check eq '' || $check =~ m/[A-Z]{2}\w{5,13}/) },
 234                               formatter       => sub { my ($input) = @_; $input =~ s/\s//g; return $input },
 235                               valid_check     => sub {
 236                                                        my ($ustid) = @_;
 237                                                        return 1 if ('' eq $ustid);
 238                                                        return ($ustid =~ m/^CH|^[A-Z]{2}\w{5,13}$/);
 239                                                      },
 240                             }, # pos 40
 241   );
 242
 243 sub new {
 244   my $class = shift;
 245   my %data  = @_;
 246
 247   croak(t8('We need a valid from date'))      unless (ref $data{from} eq 'DateTime');
 248   croak(t8('We need a valid to date'))        unless (ref $data{to}   eq 'DateTime');
 249   croak(t8('We need a array of datev_lines')) unless (ref $data{datev_lines} eq 'ARRAY');
 250
 251   my $obj = bless {}, $class;
 252   $obj->$_($data{$_}) for keys %data;
 253   $obj;
 254 }
 255
 256 sub check_encoding {
 257   my ($test) = @_;
 258   return undef unless $test;
 259   if (eval {
 260     decode('Windows-1252', $test, Encode::FB_CROAK|Encode::LEAVE_SRC);
 261     1
 262   }) {
 263     return 1;
 264   }
 265 }
 266
 267 sub _kivitendo_to_datev {
 268   @kivitendo_to_datev, ({ kivi_datev_name => 'not yet implemented' }) x (116 - @kivitendo_to_datev);
 269 }
 270
 271 sub header {
 272   my ($self) = @_;
 273
 274   my @header;
 275
 276   # we can safely set these defaults
 277   # TODO get length_of_accounts from DATEV.pm
 278   my $today              = DateTime->now_local;
 279   my $created_on         = $today->ymd('') . $today->hms('') . '000';
 280   my $length_of_accounts = length(SL::DB::Manager::Chart->get_first(where => [charttype => 'A'])->accno) // 4;
 281   my $default_curr       = SL::DB::Default->get_default_currency;
 282
 283   # datev metadata and the string length limits
 284   my %meta_datev;
 285   my %meta_datev_to_valid_length = (
 286     beraternr   =>  7,
 287     beratername => 25,
 288     mandantennr =>  5,
 289   );
 290
 291   my $datev = SL::DB::Manager::Datev->get_first();
 292
 293   while (my ($k, $v) = each %meta_datev_to_valid_length) {
 294     next unless $datev->{$k};
 295     $meta_datev{$k} = substr $datev->{$k}, 0, $v;
 296   }
 297
 298   my @header_row_1 = (
 299     "EXTF", "300", 21, "Buchungsstapel", 7, $created_on, "", "ki",
 300     "kivitendo-datev", "", $meta_datev{beraternr}, $meta_datev{mandantennr},
 301     $self->first_day_of_fiscal_year->ymd(''), $length_of_accounts,
 302     $self->from->ymd(''), $self->to->ymd(''), "", "", 1, "", $self->locked,
 303     $default_curr, "", "", "",""
 304   );
 305   push @header, [ @header_row_1 ];
 306
 307   # second header row, just the column names
 308   push @header, [ map { $_->{csv_header_name} } _kivitendo_to_datev() ];
 309
 310   return \@header;
 311 }
 312
 313 sub lines {
 314   my ($self) = @_;
 315
 316   my (@array_of_datev, @warnings);
 317   my @csv_columns = _kivitendo_to_datev();
 318
 319   foreach my $row (@{ $self->datev_lines }) {
 320     my @current_datev_row;
 321
 322     # 1. check all datev_lines and see if we have a defined value
 323     # 2. if we don't have a defined value set a default if exists
 324     # 3. otherwise die
 325     foreach my $column (@csv_columns) {
 326       if ($column->{kivi_datev_name} eq 'not yet implemented') {
 327         push @current_datev_row, '';
 328         next;
 329       }
 330       my $data = $row->{$column->{kivi_datev_name}};
 331       if (!defined $data) {
 332         if (defined $column->{default}) {
 333           $data = $column->{default};
 334         } else {
 335           die 'No sensible value or a sensible default found for the entry: ' . $column->{kivi_datev_name};
 336         }
 337       }
 338       # checkpoint a: no undefined data. All strict checks now!
 339       if (exists $column->{input_check} && !$column->{input_check}->($data)) {
 340         die t8("Wrong field value '#1' for field '#2' for the transaction with amount '#3'",
 341                 $data, $column->{kivi_datev_name}, $row->{umsatz});
 342       }
 343       # checkpoint b: we can safely format the input
 344       if ($column->{formatter}) {
 345         $data = $column->{formatter}->($data);
 346       }
 347       # checkpoint c: all soft checks now, will pop up as a user warning
 348       if (exists $column->{valid_check} && !$column->{valid_check}->($data)) {
 349         push @warnings, t8("Wrong field value '#1' for field '#2' for the transaction" .
 350                            " with amount '#3'", $data, $column->{kivi_datev_name}, $row->{umsatz});
 351       }
 352       push @current_datev_row, $data;
 353     }
 354     push @array_of_datev, \@current_datev_row;
 355   }
 356   $self->warnings(\@warnings);
 357   return \@array_of_datev;
 358 }
 359
 360 # helper
 361
 362 sub _format_amount {
 363   $::form->format_amount({ numberformat => '1000,00' }, @_);
 364 }
 365
 366 sub first_day_of_fiscal_year {
 367   $_[0]->to->clone->truncate(to => 'year');
 368 }
 369
 370 1;
 371
 372 __END__
 373
 374 =encoding utf-8
 375
 376 =head1 NAME
 377
 378 SL::DATEV::CSV - kivitendo DATEV CSV Specification
 379
 380 =head1 SYNOPSIS
 381
 382   use SL::DATEV qw(:CONSTANTS);
 383   use SL::DATEV::CSV;
 384
 385   my $startdate = DateTime->new(year => 2014, month => 9, day => 1);
 386   my $enddate   = DateTime->new(year => 2014, month => 9, day => 31);
 387   my $datev = SL::DATEV->new(
 388     exporttype => DATEV_ET_BUCHUNGEN,
 389     format     => DATEV_FORMAT_CSV,
 390     from       => $startdate,
 391     to         => $enddate,
 392   );
 393   $datev->generate_datev_data;
 394
 395   my $datev_csv = SL::DATEV::CSV->new(datev_lines  => $datev->generate_datev_lines,
 396                                       from         => $datev->from,
 397                                       to           => $datev->to,
 398                                       locked       => $datev->locked,
 399                                      );
 400   $datev_csv->header;   # returns the required 2 rows of header ($aref = [ ["row1" ..], [ "row2" .. ] ]) as array of array
 401   $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
 402   $datev_csv->warnings; # returns warnings
 403
 404
 405   # The above object methods can be directly chained to a CSV export function, like this:
 406   my $csv_file = IO::File->new($somewhere_in_filesystem)') or die "Can't open: $!";
 407   $csv->print($csv_file, $_) for @{ $datev_csv->header };
 408   $csv->print($csv_file, $_) for @{ $datev_csv->lines  };
 409   $csv_file->close;
 410   $self->{warnings} = $datev_csv->warnings;
 411
 412
 413
 414
 415 =head1 DESCRIPTION
 416
 417 The parsing of the DATEV CSV is index based, therefore the correct
 418 column must be present at the corresponding index, i.e.:
 419  Index 2
 420  Field Name   : Debit/Credit Label
 421  Valid Values : 'S' or 'H'
 422  Length:      : 1
 423
 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.
 426
 427 The key names are the english translation according to the DATEV specs
 428 (Leitfaden DATEV englisch).
 429
 430 The two attributes C<max_length> and C<type> are also set as specified
 431 by the DATEV specs.
 432
 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.
 437
 438
 439 =head1 SPECIFICATION
 440
 441 This is an excerpt of the DATEV Format 2015 Specification for CSV-Header
 442 and CSV-Data lines.
 443
 444 =head2 FILENAME
 445
 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.
 449
 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_"
 455 (External Format).
 456
 457 =head2 File Structure
 458
 459 The file structure of the text file exported/imported is defined as follows
 460
 461 Line 1: Header (serves to assist in the interpretation of the following data)
 462
 463 Line 2: Headline (headline of the user data)
 464
 465 Line 3 – n: Records (user data)
 466
 467 For an valid example file take a look at doc/DATEV-2015/EXTF_Buchungsstapel.csv
 468
 469
 470 =head2 Detailed Description
 471
 472 Line 1 must contain 11 fields.
 473
 474 Line 2 must contain 26 fields.
 475
 476 Line 3 - n:  must contain 116 fields, a smaller subset is mandatory.
 477
 478 =head1 FUNCTIONS
 479
 480 =over 4
 481
 482 =item new PARAMS
 483
 484 Constructor for CSV-DATEV export.
 485 Checks mandantory params as described in section synopsis.
 486
 487 =item check_encoding
 488
 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
 491
 492 =item header
 493
 494 Mostly all other header information are constants or metadata loaded
 495 from SL::DB::Datev.pm.
 496
 497 Returns the first two entries for the header (see above: File Structure)
 498 as an array.
 499
 500 =item kivitendo_to_datev
 501
 502 Returns the data structure C<@datev_data> as an array
 503
 504 =item _format_amount
 505
 506 Lightweight wrapper for form->format_amount.
 507 Expects a number in kivitendo database format and returns the same number
 508 in DATEV format.
 509
 510 =item first_day_of_fiscal_year
 511
 512 Takes a look at $self->to to  determine the first day of the fiscal year.
 513
 514 =item lines
 515
 516 Generates the CSV-Format data for the CSV DATEV export and returns
 517 an 2-dimensional array as an array_ref.
 518 May additionally return a second array_ref with warnings.
 519
 520 Requires the same date fields as the constructor for a valid DATEV header.
 521
 522 Furthermore we assume that the first day of the fiscal year is
 523 the first of January and we cannot guarantee that our data in kivitendo
 524 is locked, that means a booking cannot be modified after a defined (vat tax)
 525 period.
 526 Some validity checks (max_length and regex) will be done if the
 527 data structure contains them and the field is defined.
 528
 529 To add or alter the structure of the data take a look at the C<@kivitendo_to_datev> structure.
 530
 531 =back
 532
 533 =head1 TODO CAVEAT
 534
 535 One can circumevent the check of the warnings.quite easily,
 536 becaus warnings are generated after the call to lines:
 537
 538   # WRONG usage
 539   die if @{ $datev_csv->warnings };
 540   somethin_with($datev_csv->lines);
 541
 542   # safe usage
 543   my $lines = $datev_csv->lines;
 544   die if @{ $datev_csv->warnings };
 545   somethin_with($lines);
 546