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
   8 use Carp;
   9 use DateTime;
  10
  11
  12 my @kivitendo_to_datev = (
  13                             {
  14                               kivi_datev_name => 'umsatz',
  15                               csv_header_name => t8('Transaction Value'),
  16                               max_length      => 13,
  17                               type            => 'Value',
  18                               valid_check     => sub { return (shift =~ m/^\d{1,10}(\,\d{1,2})?$/) },
  19                             },
  20                             {
  21                               kivi_datev_name => 'soll_haben_kennzeichen',
  22                               csv_header_name => t8('Debit/Credit Label'),
  23                               max_length      => 1,
  24                               type            => 'Text',
  25                               valid_check     => sub { return (shift =~ m/^(S|H)$/) },
  26                             },
  27                             {
  28                               kivi_datev_name => 'waehrung',
  29                               csv_header_name => t8('Transaction Value Currency Code'),
  30                               max_length      => 3,
  31                               type            => 'Text',
  32                               valid_check     => sub { return (shift =~ m/^[A-Z]{3}$/) },
  33                             },
  34                             {
  35                               kivi_datev_name => 'wechselkurs',
  36                               csv_header_name => t8('Exchange Rate'),
  37                               max_length      => 11,
  38                               type            => 'Number',
  39                               valid_check     => sub { return (shift =~ m/^[0-9]*\.?[0-9]*$/) },
  40                             },
  41                             {
  42                               kivi_datev_name => 'not yet implemented',
  43                               csv_header_name => t8('Base Transaction Value'),
  44                             },
  45                             {
  46                               kivi_datev_name => 'not yet implemented',
  47                               csv_header_name => t8('Base Transaction Value Currency Code'),
  48                             },
  49                             {
  50                               kivi_datev_name => 'konto',
  51                               csv_header_name => t8('Account'),
  52                               max_length      => 9, # May contain a maximum of 8 or 9 digits -> perldoc
  53                               type            => 'Account',
  54                               valid_check     => sub { return (shift =~ m/^[0-9]{4,9}$/) },
  55                             },
  56                             {
  57                               kivi_datev_name => 'gegenkonto',
  58                               csv_header_name => t8('Contra Account'),
  59                               max_length      => 9, # May contain a maximum of 8 or 9 digits -> perldoc
  60                               type            => 'Account',
  61                               valid_check     => sub { return (shift =~ m/^[0-9]{4,9}$/) },
  62                             },
  63                             {
  64                               kivi_datev_name => 'buchungsschluessel',
  65                               csv_header_name => t8('Posting Key'),
  66                               max_length      => 2,
  67                               type            => 'Text',
  68                               valid_check     => sub { return (shift =~ m/^[0-9]{0,2}$/) },
  69                             },
  70                             {
  71                               kivi_datev_name => 'datum',
  72                               csv_header_name => t8('Invoice Date'),
  73                               max_length      => 4,
  74                               type            => 'Date',
  75                               valid_check     => sub { return (shift =~ m/^[0-9]{4}$/) },
  76                             },
  77                             {
  78                               kivi_datev_name => 'belegfeld1',
  79                               csv_header_name => t8('Invoice Field 1'),
  80                               max_length      => 12,
  81                               type            => 'Text',
  82                               valid_check     => sub { my $text = shift; check_encoding($text); },
  83                             },
  84                             {
  85                               kivi_datev_name => 'not yet implemented',
  86                               csv_header_name => t8('Invoice Field 2'),
  87                              max_length      => 12,
  88                               type            => 'Text',
  89                               valid_check     => sub { return (shift =~ m/[ -~]{1,12}/) },
  90                             },
  91                             {
  92                               kivi_datev_name => 'not yet implemented',
  93                               csv_header_name => t8('Discount'),
  94                               type            => 'Value',
  95                             },
  96                             {
  97                               kivi_datev_name => 'buchungsbes',
  98                               csv_header_name => t8('Posting Text'),
  99                               max_length      => 60,
 100                               type            => 'Text',
 101                               valid_check     => sub { my $text = shift; return 1 unless $text; check_encoding($text);  },
 102                             },  # pos 14
 103                             {
 104                               kivi_datev_name => 'not yet implemented',
 105                             },
 106                             {
 107                               kivi_datev_name => 'not yet implemented',
 108                             },
 109                             {
 110                               kivi_datev_name => 'not yet implemented',
 111                             },
 112                             {
 113                               kivi_datev_name => 'not yet implemented',
 114                             },
 115                             {
 116                               kivi_datev_name => 'not yet implemented',
 117                             },
 118                             {
 119                               kivi_datev_name => 'not yet implemented',
 120                               csv_header_name => t8('Link to invoice'),
 121                               max_length      => 210, # DMS Application shortcut and GUID
 122                                                       # Example: "BEDI"
 123                                                       # "8DB85C02-4CC3-FF3E-06D7-7F87EEECCF3A".
 124                             }, # pos 20
 125                             {
 126                               kivi_datev_name => 'not yet implemented',
 127                             },
 128                             {
 129                               kivi_datev_name => 'not yet implemented',
 130                             },
 131                             {
 132                               kivi_datev_name => 'not yet implemented',
 133                             },
 134                             {
 135                               kivi_datev_name => 'not yet implemented',
 136                             },
 137                             {
 138                               kivi_datev_name => 'not yet implemented',
 139                             },
 140                             {
 141                               kivi_datev_name => 'not yet implemented',
 142                             },
 143                             {
 144                               kivi_datev_name => 'not yet implemented',
 145                             },
 146                             {
 147                               kivi_datev_name => 'not yet implemented',
 148                             },
 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 => 'kost1',
 175                               csv_header_name => t8('Cost Center'),
 176                               max_length      => 8,
 177                               type            => 'Text',
 178                               valid_check     => sub { my $text = shift; return 1 unless $text; check_encoding($text);  },
 179                             }, # pos 37
 180                             {
 181                               kivi_datev_name => 'kost2',
 182                               csv_header_name => t8('Cost Center'),
 183                               max_length      => 8,
 184                               type            => 'Text',
 185                               valid_check     => sub { my $text = shift; return 1 unless $text; check_encoding($text);  },
 186                             }, # pos 38
 187                             {
 188                               kivi_datev_name => 'not yet implemented',
 189                               csv_header_name => t8('KOST Quantity'),
 190                               max_length      => 9,
 191                               type            => 'Number',
 192                               valid_check     => sub { return (shift =~ m/^[0-9]{0,9}$/) },
 193                             }, # pos 39
 194                             {
 195                               kivi_datev_name => 'ustid',
 196                               csv_header_name => t8('EU Member State and VAT ID Number'),
 197                               max_length      => 15,
 198                               type            => 'Text',
 199                               valid_check     => sub {
 200                                                        my $ustid = shift;
 201                                                        return 1 unless defined($ustid);
 202                                                        return ($ustid =~ m/^CH|^[A-Z]{2}\w{5,13}$/);
 203                                                      },
 204                             }, # pos 40
 205   );
 206
 207 sub check_encoding {
 208   use Encode qw( decode );
 209   # counter test: arabic doesnt work: ݐ
 210   my $test = shift;
 211   return undef unless $test;
 212   if (eval {
 213     decode('Windows-1252', $test, Encode::FB_CROAK|Encode::LEAVE_SRC);
 214     1
 215   }) {
 216     return 1;
 217   }
 218 }
 219
 220 sub kivitendo_to_datev {
 221   my $self = shift;
 222
 223   my $entries = scalar (@kivitendo_to_datev);
 224   push @kivitendo_to_datev, { kivi_datev_name => 'not yet implemented' } for 1 .. (116 - $entries);
 225   return @kivitendo_to_datev;
 226 }
 227
 228 sub generate_csv_header {
 229   my ($self, %params)   = @_;
 230
 231   # we need from and to in YYYYDDMM
 232   croak "Wrong format for from" unless $params{from} =~ m/^[0-9]{8}$/;
 233   croak "Wrong format for to"   unless $params{to} =~ m/^[0-9]{8}$/;
 234
 235   # who knows if we want locking and when our fiscal year starts
 236   croak "Wrong state of locking"      unless $params{locked} =~ m/(0|1)/;
 237   croak "No startdate of fiscal year" unless $params{first_day_of_fiscal_year} =~ m/^[0-9]{8}$/;
 238
 239
 240   # we can safely set these defaults
 241   my $today              = DateTime->now(time_zone => "local");
 242   my $created_on         = $today->ymd('') . $today->hms('') . '000';
 243   my $length_of_accounts = length(SL::DB::Manager::Chart->get_first(where => [charttype => 'A'])->accno) // 4;
 244   my $default_curr       = SL::DB::Default->get_default_currency;
 245
 246   # datev metadata and the string lenght limits
 247   my %meta_datev;
 248   my %meta_datev_to_valid_length = (
 249     beraternr   =>  7,
 250     beratername => 25,
 251     mandantennr =>  5,
 252   );
 253
 254   my $datev = SL::DB::Manager::Datev->get_first();
 255
 256   while (my ($k, $v) = each %meta_datev_to_valid_length) {
 257     $meta_datev{$k} = substr $datev->{$k}, 0, $v;
 258   }
 259
 260   my @header = (
 261     "EXTF", "300", 21, "Buchungsstapel", 7, $created_on, "", "ki",
 262     "kivitendo-datev", "", $meta_datev{beraternr}, $meta_datev{mandantennr},
 263     $params{first_day_of_fiscal_year}, $length_of_accounts,
 264     $params{from}, $params{to}, "", "", 1, "", $params{locked},
 265     $default_curr, "", "", "",""
 266   );
 267
 268   return @header;
 269 }
 270 1;
 271
 272 __END__
 273
 274 =encoding utf-8
 275
 276 =head1 NAME
 277
 278 SL::DATEV::CSV - kivitendo DATEV CSV Specification
 279
 280 =head1 SYNOPSIS
 281
 282 The parsing of the DATEV CSV is index based, therefore the correct
 283 column must be present at the corresponding index, i.e.:
 284  Index 2
 285  Field Name   : Debit/Credit Label
 286  Valid Values : 'S' or 'H'
 287  Length:      : 1
 288
 289 The columns in C<@kivi_datev> are in the correct order and the
 290 specific attributes are defined as a key value hash list for each entry.
 291
 292 The key names are the english translation according to the DATEV specs
 293 (Leitfaden DATEV englisch).
 294
 295 The two attributes C<max_length> and C<type> are also set as specified
 296 by the DATEV specs.
 297
 298 To link the structure to kivitendo data, each entry has the attribute C<kivi_datev_name>
 299 which is by convention the key name as generated by DATEV->generate_datev_data.
 300 A value of C<'not yet implemented'> indicates that this field has no
 301 corresponding kivitendo data and will be given an empty value by DATEV->csv_buchungsexport.
 302
 303
 304 =head1 SPECIFICATION
 305
 306 This is an excerpt of the DATEV Format 2015 Specification for CSV-Header
 307 and CSV-Data lines.
 308
 309 =head2 FILENAME
 310
 311 The filename is subject to the following restrictions:
 312 1. The filename must begin with the prefix DTVF_ or EXTF_.
 313 2. The filename must end with .csv.
 314
 315 When exporting from or importing into DATEV applications, the filename is
 316 marked with the prefix "DTVF_" (DATEV Format).
 317 The prefix "DTVF_" is reserved for DATEV applications.
 318 If you are using a third-party application to create a file in the DATEV format
 319 that you want to import using batch processing, use the prefix "EXTF_"
 320 (External Format).
 321
 322 =head2 File Structure
 323
 324 The file structure of the text file exported/imported is defined as follows
 325
 326 Line 1: Header (serves to assist in the interpretation of the following data)
 327
 328 Line 2: Headline (headline of the user data)
 329
 330 Line 3 – n: Records (user data)
 331
 332 For an valid example file take a look at doc/DATEV-2015/EXTF_Buchungsstapel.csv
 333
 334
 335 =head2 Detailed Description
 336
 337 Line 1 must contain 11 fields.
 338
 339 Line 2 must contain 26 fields.
 340
 341 Line 3 - n:  must contain 116 fields, a smaller subset is mandatory.
 342
 343 =head1 FUNCTIONS
 344
 345 =over 4
 346
 347 =item check_encoding
 348
 349 Helper function, returns true if a string is not empty and cp1252 encoded
 350
 351 =item generate_csv_header(from => 'YYYYDDMM', to => 'YYYYDDMM', locked => 0,
 352                           first_day_of_fiscal_year => 'YYYYDDMM')
 353
 354 Mostly all other header information are constants or metadata loaded
 355 from SL::DB::Datev.pm.
 356
 357 Returns the first two entries for the header (see above: File Structure)
 358 as an array.
 359
 360 All params are mandatory:
 361 C<params{from}>,  C<params{to}>
 362 and C<params{first_day_of_fiscal_year}> have to be in YYYYDDMM date string
 363 format.
 364 Furthermore C<params{locked}> needs to be a boolean in number format (0|1).
 365
 366
 367 =item kivitendo_to_datev
 368
 369 Returns the data structure C<@datev_data> as an array
 370
 371 =back