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