SL/DATEV/CSV.pm

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