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