SL/DATEV/CSV.pm

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