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 SL::Helper::DateTime;
   8
   9 use Carp;
  10 use DateTime;
  11 use Encode qw(decode);
  12
  13
  14 my @kivitendo_to_datev = (
  15                             {
  16                               kivi_datev_name => 'umsatz',
  17                               csv_header_name => t8('Transaction Value'),
  18                               max_length      => 13,
  19                               type            => 'Value',
  20                               valid_check     => sub { my ($check) = @_; return ($check =~ m/^\d{1,10}(\,\d{1,2})?$/) },
  21                             },
  22                             {
  23                               kivi_datev_name => 'soll_haben_kennzeichen',
  24                               csv_header_name => t8('Debit/Credit Label'),
  25                               max_length      => 1,
  26                               type            => 'Text',
  27                               valid_check     => sub { my ($check) = @_; return ($check =~ m/^(S|H)$/) },
  28                             },
  29                             {
  30                               kivi_datev_name => 'waehrung',
  31                               csv_header_name => t8('Transaction Value Currency Code'),
  32                               max_length      => 3,
  33                               type            => 'Text',
  34                               valid_check     => sub { my ($check) = @_; return ($check =~ m/^[A-Z]{3}$/) },
  35                             },
  36                             {
  37                               kivi_datev_name => 'wechselkurs',
  38                               csv_header_name => t8('Exchange Rate'),
  39                               max_length      => 11,
  40                               type            => 'Number',
  41                               valid_check     => sub { my ($check) = @_; return ($check =~ m/^[0-9]*\.?[0-9]*$/) },
  42                             },
  43                             {
  44                               kivi_datev_name => 'not yet implemented',
  45                               csv_header_name => t8('Base Transaction Value'),
  46                             },
  47                             {
  48                               kivi_datev_name => 'not yet implemented',
  49                               csv_header_name => t8('Base Transaction Value Currency Code'),
  50                             },
  51                             {
  52                               kivi_datev_name => 'konto',
  53                               csv_header_name => t8('Account'),
  54                               max_length      => 9, # May contain a maximum of 8 or 9 digits -> perldoc
  55                               type            => 'Account',
  56                               valid_check     => sub { my ($check) = @_; return ($check =~ m/^[0-9]{4,9}$/) },
  57                             },
  58                             {
  59                               kivi_datev_name => 'gegenkonto',
  60                               csv_header_name => t8('Contra Account'),
  61                               max_length      => 9, # May contain a maximum of 8 or 9 digits -> perldoc
  62                               type            => 'Account',
  63                               valid_check     => sub { my ($check) = @_; return ($check =~ m/^[0-9]{4,9}$/) },
  64                             },
  65                             {
  66                               kivi_datev_name => 'buchungsschluessel',
  67                               csv_header_name => t8('Posting Key'),
  68                               max_length      => 2,
  69                               type            => 'Text',
  70                               valid_check     => sub { my ($check) = @_; return ($check =~ m/^[0-9]{0,2}$/) },
  71                             },
  72                             {
  73                               kivi_datev_name => 'datum',
  74                               csv_header_name => t8('Invoice Date'),
  75                               max_length      => 4,
  76                               type            => 'Date',
  77                               valid_check     => sub { my ($check) = @_; return ($check =~ m/^[0-9]{4}$/) },
  78                             },
  79                             {
  80                               kivi_datev_name => 'belegfeld1',
  81                               csv_header_name => t8('Invoice Field 1'),
  82                               max_length      => 12,
  83                               type            => 'Text',
  84                               valid_check     => sub { my ($text) = @_; check_encoding($text); },
  85                             },
  86                             {
  87                               kivi_datev_name => 'not yet implemented',
  88                               csv_header_name => t8('Invoice Field 2'),
  89                              max_length      => 12,
  90                               type            => 'Text',
  91                               valid_check     => sub { my ($check) = @_; return ($check =~ m/[ -~]{1,12}/) },
  92                             },
  93                             {
  94                               kivi_datev_name => 'not yet implemented',
  95                               csv_header_name => t8('Discount'),
  96                               type            => 'Value',
  97                             },
  98                             {
  99                               kivi_datev_name => 'buchungsbes',
 100                               csv_header_name => t8('Posting Text'),
 101                               max_length      => 60,
 102                               type            => 'Text',
 103                               valid_check     => sub { my ($text) = @_; return 1 unless $text; check_encoding($text);  },
 104                             },  # pos 14
 105                             {
 106                               kivi_datev_name => 'not yet implemented',
 107                             },
 108                             {
 109                               kivi_datev_name => 'not yet implemented',
 110                             },
 111                             {
 112                               kivi_datev_name => 'not yet implemented',
 113                             },
 114                             {
 115                               kivi_datev_name => 'not yet implemented',
 116                             },
 117                             {
 118                               kivi_datev_name => 'not yet implemented',
 119                             },
 120                             {
 121                               kivi_datev_name => 'not yet implemented',
 122                               csv_header_name => t8('Link to invoice'),
 123                               max_length      => 210, # DMS Application shortcut and GUID
 124                                                       # Example: "BEDI"
 125                                                       # "8DB85C02-4CC3-FF3E-06D7-7F87EEECCF3A".
 126                             }, # pos 20
 127                             {
 128                               kivi_datev_name => 'not yet implemented',
 129                             },
 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                             },
 148                             {
 149                               kivi_datev_name => 'not yet implemented',
 150                             },
 151                             {
 152                               kivi_datev_name => 'not yet implemented',
 153                             },
 154                             {
 155                               kivi_datev_name => 'not yet implemented',
 156                             },
 157                             {
 158                               kivi_datev_name => 'not yet implemented',
 159                             },
 160                             {
 161                               kivi_datev_name => 'not yet implemented',
 162                             },
 163                             {
 164                               kivi_datev_name => 'not yet implemented',
 165                             },
 166                             {
 167                               kivi_datev_name => 'not yet implemented',
 168                             },
 169                             {
 170                               kivi_datev_name => 'not yet implemented',
 171                             },
 172                             {
 173                               kivi_datev_name => 'not yet implemented',
 174                             },
 175                             {
 176                               kivi_datev_name => 'kost1',
 177                               csv_header_name => t8('Cost Center'),
 178                               max_length      => 8,
 179                               type            => 'Text',
 180                               valid_check     => sub { my ($text) = @_; return 1 unless $text; check_encoding($text);  },
 181                             }, # pos 37
 182                             {
 183                               kivi_datev_name => 'kost2',
 184                               csv_header_name => t8('Cost Center'),
 185                               max_length      => 8,
 186                               type            => 'Text',
 187                               valid_check     => sub { my ($text) = @_; return 1 unless $text; check_encoding($text);  },
 188                             }, # pos 38
 189                             {
 190                               kivi_datev_name => 'not yet implemented',
 191                               csv_header_name => t8('KOST Quantity'),
 192                               max_length      => 9,
 193                               type            => 'Number',
 194                               valid_check     => sub { my ($check) = @_; return ($check =~ m/^[0-9]{0,9}$/) },
 195                             }, # pos 39
 196                             {
 197                               kivi_datev_name => 'ustid',
 198                               csv_header_name => t8('EU Member State and VAT ID Number'),
 199                               max_length      => 15,
 200                               type            => 'Text',
 201                               valid_check     => sub {
 202                                                        my ($ustid) = @_;
 203                                                        return 1 unless defined($ustid);
 204                                                        return ($ustid =~ m/^CH|^[A-Z]{2}\w{5,13}$/);
 205                                                      },
 206                             }, # pos 40
 207   );
 208
 209 sub new {
 210   my $class = shift;
 211   my %data  = @_;
 212
 213   my $obj = bless {}, $class;
 214
 215   croak(t8('We need a valid from date'))      unless (ref $data{from} eq 'DateTime');
 216   croak(t8('We need a valid to date'))        unless (ref $data{to}   eq 'DateTime');
 217   croak(t8('We need a array of datev_lines')) unless (ref $data{datev_lines} eq 'ARRAY');
 218
 219   # TODO no params here, better class variables/values
 220   return _csv_buchungsexport(from        => $data{from},
 221                              to          => $data{to},
 222                              datev_lines => $data{datev_lines},
 223                              locked      => $data{locked},
 224                             );
 225
 226   $obj;
 227 }
 228
 229 sub check_encoding {
 230   my ($test) = @_;
 231   return undef unless $test;
 232   if (eval {
 233     decode('Windows-1252', $test, Encode::FB_CROAK|Encode::LEAVE_SRC);
 234     1
 235   }) {
 236     return 1;
 237   }
 238 }
 239
 240 sub _kivitendo_to_datev {
 241   my ($self) = @_;
 242
 243   my $entries = scalar (@kivitendo_to_datev);
 244   push @kivitendo_to_datev, { kivi_datev_name => 'not yet implemented' } for 1 .. (116 - $entries);
 245   return @kivitendo_to_datev;
 246 }
 247
 248 sub _generate_csv_header {
 249   my %params  = @_;
 250
 251   # we need from and to in YYYYDDMM
 252   croak "Wrong format for from $params{from}" unless $params{from} =~ m/^[0-9]{8}$/;
 253   croak "Wrong format for to $params{to}"   unless $params{to} =~ m/^[0-9]{8}$/;
 254
 255   # who knows if we want locking and when our fiscal year starts
 256   # croak "Wrong state of locking"      unless $params{locked} =~ m/^(0|1)$/;
 257   my $locked = defined($params{locked}) ? 1 : 0;
 258   croak "No startdate of fiscal year" unless $params{first_day_of_fiscal_year} =~ m/^[0-9]{8}$/;
 259
 260
 261   # we can safely set these defaults
 262   my $today              = DateTime->now(time_zone => "local");
 263   my $created_on         = $today->ymd('') . $today->hms('') . '000';
 264   my $length_of_accounts = length(SL::DB::Manager::Chart->get_first(where => [charttype => 'A'])->accno) // 4;
 265   my $default_curr       = SL::DB::Default->get_default_currency;
 266
 267   # datev metadata and the string length limits
 268   my %meta_datev;
 269   my %meta_datev_to_valid_length = (
 270     beraternr   =>  7,
 271     beratername => 25,
 272     mandantennr =>  5,
 273   );
 274
 275   my $datev = SL::DB::Manager::Datev->get_first();
 276
 277   while (my ($k, $v) = each %meta_datev_to_valid_length) {
 278     $meta_datev{$k} = substr $datev->{$k}, 0, $v;
 279   }
 280
 281   my @header = (
 282     "EXTF", "300", 21, "Buchungsstapel", 7, $created_on, "", "ki",
 283     "kivitendo-datev", "", $meta_datev{beraternr}, $meta_datev{mandantennr},
 284     $params{first_day_of_fiscal_year}, $length_of_accounts,
 285     $params{from}, $params{to}, "", "", 1, "", $locked,
 286     $default_curr, "", "", "",""
 287   );
 288
 289   return @header;
 290 }
 291
 292 sub _csv_buchungsexport {
 293   my %params = @_;
 294
 295   my @csv_columns = _kivitendo_to_datev();
 296   my @csv_headers = _generate_csv_header(
 297                       from                     => $params{from}->ymd(''),
 298                       to                       => $params{to}->ymd(''),
 299                       first_day_of_fiscal_year => $params{to}->year . '0101',
 300                       locked                   => $params{locked}
 301                     );
 302
 303   my @array_of_datev;
 304
 305   # 2 Headers
 306   push @array_of_datev, \@csv_headers;
 307   push @array_of_datev, [ map { $_->{csv_header_name} } @csv_columns ];
 308
 309   my @warnings;
 310   foreach my $row (@{ $params{datev_lines} }) {
 311     my @current_datev_row;
 312
 313     # shorten strings
 314     if ($row->{belegfeld1}) {
 315       $row->{buchungsbes} = $row->{belegfeld1} if $row->{belegfeld1};
 316       $row->{belegfeld1}  = substr($row->{belegfeld1}, 0, 12);
 317       $row->{buchungsbes} = substr($row->{buchungsbes}, 0, 60);
 318     }
 319
 320     $row->{datum} = DateTime->from_kivitendo($row->{datum})->strftime('%d%m');
 321
 322     $row->{kost1}       = substr($row->{kost1}, 0, 8) if $row->{kost1};
 323     $row->{kost2}       = substr($row->{kost2}, 0, 8) if $row->{kost2};
 324
 325     # , as decimal point and trim for UstID
 326     $row->{umsatz}      = _format_amount($row->{umsatz});
 327     $row->{ustid}       =~ s/\s//g if $row->{ustid}; # trim whitespace
 328
 329     foreach my $column (@csv_columns) {
 330       if (exists $column->{max_length} && $column->{kivi_datev_name} ne 'not yet implemented') {
 331         # check max length
 332         die "Incorrect length of field" if length($row->{ $column->{kivi_datev_name} }) > $column->{max_length};
 333       }
 334       if (exists $column->{valid_check} && $column->{kivi_datev_name} ne 'not yet implemented') {
 335         # more checks, listed as user warnings
 336         push @warnings, t8("Wrong field value '#1' for field '#2' for the transaction" .
 337                             " with amount '#3'",$row->{ $column->{kivi_datev_name} },
 338                             $column->{kivi_datev_name},$row->{umsatz})
 339           unless ($column->{valid_check}->($row->{ $column->{kivi_datev_name} }));
 340       }
 341       push @current_datev_row, $row->{ $column->{kivi_datev_name} };
 342     }
 343     push @array_of_datev, \@current_datev_row;
 344   }
 345   return (\@array_of_datev, \@warnings);
 346 }
 347
 348 sub _format_amount {
 349   $::form->format_amount({ numberformat => '1000,00' }, @_);
 350 }
 351
 352 1;
 353
 354 __END__
 355
 356 =encoding utf-8
 357
 358 =head1 NAME
 359
 360 SL::DATEV::CSV - kivitendo DATEV CSV Specification
 361
 362 =head1 SYNOPSIS
 363
 364   use SL::DATEV qw(:CONSTANTS);
 365   use SL::DATEV::CSV;
 366
 367   my $startdate = DateTime->new(year => 2014, month => 9, day => 1);
 368   my $enddate   = DateTime->new(year => 2014, month => 9, day => 31);
 369   my $datev = SL::DATEV->new(
 370     exporttype => DATEV_ET_BUCHUNGEN,
 371     format     => DATEV_FORMAT_CSV,
 372     from       => $startdate,
 373     to         => $enddate,
 374   );
 375   $datev->generate_datev_data;
 376
 377   my $datev_ref = SL::DATEV::CSV->new(datev_lines  => $datev->generate_datev_lines,
 378                                       from         => $datev->from,
 379                                       to           => $datev->to,
 380                                       locked       => $datev->locked,
 381                                      );
 382
 383 =head1 DESCRIPTION
 384
 385 The parsing of the DATEV CSV is index based, therefore the correct
 386 column must be present at the corresponding index, i.e.:
 387  Index 2
 388  Field Name   : Debit/Credit Label
 389  Valid Values : 'S' or 'H'
 390  Length:      : 1
 391
 392 The columns in C<@kivi_datev> are in the correct order and the
 393 specific attributes are defined as a key value hash list for each entry.
 394
 395 The key names are the english translation according to the DATEV specs
 396 (Leitfaden DATEV englisch).
 397
 398 The two attributes C<max_length> and C<type> are also set as specified
 399 by the DATEV specs.
 400
 401 To link the structure to kivitendo data, each entry has the attribute C<kivi_datev_name>
 402 which is by convention the key name as generated by DATEV->generate_datev_data.
 403 A value of C<'not yet implemented'> indicates that this field has no
 404 corresponding kivitendo data and will be given an empty value by DATEV->csv_buchungsexport.
 405
 406
 407 =head1 SPECIFICATION
 408
 409 This is an excerpt of the DATEV Format 2015 Specification for CSV-Header
 410 and CSV-Data lines.
 411
 412 =head2 FILENAME
 413
 414 The filename is subject to the following restrictions:
 415 1. The filename must begin with the prefix DTVF_ or EXTF_.
 416 2. The filename must end with .csv.
 417
 418 When exporting from or importing into DATEV applications, the filename is
 419 marked with the prefix "DTVF_" (DATEV Format).
 420 The prefix "DTVF_" is reserved for DATEV applications.
 421 If you are using a third-party application to create a file in the DATEV format
 422 that you want to import using batch processing, use the prefix "EXTF_"
 423 (External Format).
 424
 425 =head2 File Structure
 426
 427 The file structure of the text file exported/imported is defined as follows
 428
 429 Line 1: Header (serves to assist in the interpretation of the following data)
 430
 431 Line 2: Headline (headline of the user data)
 432
 433 Line 3 – n: Records (user data)
 434
 435 For an valid example file take a look at doc/DATEV-2015/EXTF_Buchungsstapel.csv
 436
 437
 438 =head2 Detailed Description
 439
 440 Line 1 must contain 11 fields.
 441
 442 Line 2 must contain 26 fields.
 443
 444 Line 3 - n:  must contain 116 fields, a smaller subset is mandatory.
 445
 446 =head1 FUNCTIONS
 447
 448 =over 4
 449
 450 =item new PARAMS
 451
 452 Constructor for CSV-DATEV export.
 453 Checks mandantory params as described in section synopsis.
 454
 455 =item check_encoding
 456
 457 Helper function, returns true if a string is not empty and cp1252 encoded
 458 For example some arabic utf-8 like  ݐ  will return false
 459
 460 =item generate_csv_header(from => 'YYYYDDMM', to => 'YYYYDDMM', locked => 0,
 461                           first_day_of_fiscal_year => 'YYYYDDMM')
 462
 463 Mostly all other header information are constants or metadata loaded
 464 from SL::DB::Datev.pm.
 465
 466 Returns the first two entries for the header (see above: File Structure)
 467 as an array.
 468
 469 All params are mandatory:
 470 C<params{from}>,  C<params{to}>
 471 and C<params{first_day_of_fiscal_year}> have to be in YYYYDDMM date string
 472 format.
 473 Furthermore C<params{locked}> is a perlish boolean.
 474
 475
 476 =item kivitendo_to_datev
 477
 478 Returns the data structure C<@datev_data> as an array
 479
 480 =item _format_amount
 481
 482 Lightweight wrapper for form->format_amount.
 483 Expects a number in kivitendo database format and returns the same number
 484 in DATEV format.
 485
 486 =item _csv_buchungsexport
 487
 488 Generates the CSV-Format data for the CSV DATEV export and returns
 489 an 2-dimensional array as an array_ref.
 490 May additionally return a second array_ref with warnings.
 491
 492 Requires the same date fields as the constructor for a valid DATEV header.
 493
 494 Furthermore we assume that the first day of the fiscal year is
 495 the first of January and we cannot guarantee that our data in kivitendo
 496 is locked, that means a booking cannot be modified after a defined (vat tax)
 497 period.
 498 Some validity checks (max_length and regex) will be done if the
 499 data structure contains them and the field is defined.
 500
 501 To add or alter the structure of the data take a look at the C<@kivitendo_to_datev> structure.
 502
 503
 504 =back