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