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