1 package SL::DATEV::CSV;
5 use SL::Locale::String qw(t8);
10 use Encode qw(decode);
13 my @kivitendo_to_datev = (
15 kivi_datev_name => 'umsatz',
16 csv_header_name => t8('Transaction Value'),
19 valid_check => sub { my ($check) = @_; return ($check =~ m/^\d{1,10}(\,\d{1,2})?$/) },
22 kivi_datev_name => 'soll_haben_kennzeichen',
23 csv_header_name => t8('Debit/Credit Label'),
26 valid_check => sub { my ($check) = @_; return ($check =~ m/^(S|H)$/) },
29 kivi_datev_name => 'waehrung',
30 csv_header_name => t8('Transaction Value Currency Code'),
33 valid_check => sub { my ($check) = @_; return ($check =~ m/^[A-Z]{3}$/) },
36 kivi_datev_name => 'wechselkurs',
37 csv_header_name => t8('Exchange Rate'),
40 valid_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]*\.?[0-9]*$/) },
43 kivi_datev_name => 'not yet implemented',
44 csv_header_name => t8('Base Transaction Value'),
47 kivi_datev_name => 'not yet implemented',
48 csv_header_name => t8('Base Transaction Value Currency Code'),
51 kivi_datev_name => 'konto',
52 csv_header_name => t8('Account'),
53 max_length => 9, # May contain a maximum of 8 or 9 digits -> perldoc
55 valid_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]{4,9}$/) },
58 kivi_datev_name => 'gegenkonto',
59 csv_header_name => t8('Contra Account'),
60 max_length => 9, # May contain a maximum of 8 or 9 digits -> perldoc
62 valid_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]{4,9}$/) },
65 kivi_datev_name => 'buchungsschluessel',
66 csv_header_name => t8('Posting Key'),
69 valid_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]{0,2}$/) },
72 kivi_datev_name => 'datum',
73 csv_header_name => t8('Invoice Date'),
76 valid_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]{4}$/) },
79 kivi_datev_name => 'belegfeld1',
80 csv_header_name => t8('Invoice Field 1'),
83 valid_check => sub { my ($text) = @_; check_encoding($text); },
86 kivi_datev_name => 'not yet implemented',
87 csv_header_name => t8('Invoice Field 2'),
90 valid_check => sub { my ($check) = @_; return ($check =~ m/[ -~]{1,12}/) },
93 kivi_datev_name => 'not yet implemented',
94 csv_header_name => t8('Discount'),
98 kivi_datev_name => 'buchungsbes',
99 csv_header_name => t8('Posting Text'),
102 valid_check => sub { my ($text) = @_; return 1 unless $text; check_encoding($text); },
105 kivi_datev_name => 'not yet implemented',
108 kivi_datev_name => 'not yet implemented',
111 kivi_datev_name => 'not yet implemented',
114 kivi_datev_name => 'not yet implemented',
117 kivi_datev_name => 'not yet implemented',
120 kivi_datev_name => 'not yet implemented',
121 csv_header_name => t8('Link to invoice'),
122 max_length => 210, # DMS Application shortcut and GUID
124 # "8DB85C02-4CC3-FF3E-06D7-7F87EEECCF3A".
127 kivi_datev_name => 'not yet implemented',
130 kivi_datev_name => 'not yet implemented',
133 kivi_datev_name => 'not yet implemented',
136 kivi_datev_name => 'not yet implemented',
139 kivi_datev_name => 'not yet implemented',
142 kivi_datev_name => 'not yet implemented',
145 kivi_datev_name => 'not yet implemented',
148 kivi_datev_name => 'not yet implemented',
151 kivi_datev_name => 'not yet implemented',
154 kivi_datev_name => 'not yet implemented',
157 kivi_datev_name => 'not yet implemented',
160 kivi_datev_name => 'not yet implemented',
163 kivi_datev_name => 'not yet implemented',
166 kivi_datev_name => 'not yet implemented',
169 kivi_datev_name => 'not yet implemented',
172 kivi_datev_name => 'not yet implemented',
175 kivi_datev_name => 'kost1',
176 csv_header_name => t8('Cost Center'),
179 valid_check => sub { my ($text) = @_; return 1 unless $text; check_encoding($text); },
182 kivi_datev_name => 'kost2',
183 csv_header_name => t8('Cost Center'),
186 valid_check => sub { my ($text) = @_; return 1 unless $text; check_encoding($text); },
189 kivi_datev_name => 'not yet implemented',
190 csv_header_name => t8('KOST Quantity'),
193 valid_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]{0,9}$/) },
196 kivi_datev_name => 'ustid',
197 csv_header_name => t8('EU Member State and VAT ID Number'),
202 return 1 unless defined($ustid);
203 return ($ustid =~ m/^CH|^[A-Z]{2}\w{5,13}$/);
210 return undef unless $test;
212 decode('Windows-1252', $test, Encode::FB_CROAK|Encode::LEAVE_SRC);
219 sub kivitendo_to_datev {
222 my $entries = scalar (@kivitendo_to_datev);
223 push @kivitendo_to_datev, { kivi_datev_name => 'not yet implemented' } for 1 .. (116 - $entries);
224 return @kivitendo_to_datev;
227 sub generate_csv_header {
228 my ($self, %params) = @_;
230 # we need from and to in YYYYDDMM
231 croak "Wrong format for from" unless $params{from} =~ m/^[0-9]{8}$/;
232 croak "Wrong format for to" unless $params{to} =~ m/^[0-9]{8}$/;
234 # who knows if we want locking and when our fiscal year starts
235 croak "Wrong state of locking" unless $params{locked} =~ m/(0|1)/;
236 croak "No startdate of fiscal year" unless $params{first_day_of_fiscal_year} =~ m/^[0-9]{8}$/;
239 # we can safely set these defaults
240 my $today = DateTime->now(time_zone => "local");
241 my $created_on = $today->ymd('') . $today->hms('') . '000';
242 my $length_of_accounts = length(SL::DB::Manager::Chart->get_first(where => [charttype => 'A'])->accno) // 4;
243 my $default_curr = SL::DB::Default->get_default_currency;
245 # datev metadata and the string length limits
247 my %meta_datev_to_valid_length = (
253 my $datev = SL::DB::Manager::Datev->get_first();
255 while (my ($k, $v) = each %meta_datev_to_valid_length) {
256 $meta_datev{$k} = substr $datev->{$k}, 0, $v;
260 "EXTF", "300", 21, "Buchungsstapel", 7, $created_on, "", "ki",
261 "kivitendo-datev", "", $meta_datev{beraternr}, $meta_datev{mandantennr},
262 $params{first_day_of_fiscal_year}, $length_of_accounts,
263 $params{from}, $params{to}, "", "", 1, "", $params{locked},
264 $default_curr, "", "", "",""
271 $::form->format_amount({ numberformat => '1000,00' }, @_);
282 SL::DATEV::CSV - kivitendo DATEV CSV Specification
286 The parsing of the DATEV CSV is index based, therefore the correct
287 column must be present at the corresponding index, i.e.:
289 Field Name : Debit/Credit Label
290 Valid Values : 'S' or 'H'
293 The columns in C<@kivi_datev> are in the correct order and the
294 specific attributes are defined as a key value hash list for each entry.
296 The key names are the english translation according to the DATEV specs
297 (Leitfaden DATEV englisch).
299 The two attributes C<max_length> and C<type> are also set as specified
302 To link the structure to kivitendo data, each entry has the attribute C<kivi_datev_name>
303 which is by convention the key name as generated by DATEV->generate_datev_data.
304 A value of C<'not yet implemented'> indicates that this field has no
305 corresponding kivitendo data and will be given an empty value by DATEV->csv_buchungsexport.
310 This is an excerpt of the DATEV Format 2015 Specification for CSV-Header
315 The filename is subject to the following restrictions:
316 1. The filename must begin with the prefix DTVF_ or EXTF_.
317 2. The filename must end with .csv.
319 When exporting from or importing into DATEV applications, the filename is
320 marked with the prefix "DTVF_" (DATEV Format).
321 The prefix "DTVF_" is reserved for DATEV applications.
322 If you are using a third-party application to create a file in the DATEV format
323 that you want to import using batch processing, use the prefix "EXTF_"
326 =head2 File Structure
328 The file structure of the text file exported/imported is defined as follows
330 Line 1: Header (serves to assist in the interpretation of the following data)
332 Line 2: Headline (headline of the user data)
334 Line 3 – n: Records (user data)
336 For an valid example file take a look at doc/DATEV-2015/EXTF_Buchungsstapel.csv
339 =head2 Detailed Description
341 Line 1 must contain 11 fields.
343 Line 2 must contain 26 fields.
345 Line 3 - n: must contain 116 fields, a smaller subset is mandatory.
353 Helper function, returns true if a string is not empty and cp1252 encoded
354 For example some arabic utf-8 like ݐ will return false
356 =item generate_csv_header(from => 'YYYYDDMM', to => 'YYYYDDMM', locked => 0,
357 first_day_of_fiscal_year => 'YYYYDDMM')
359 Mostly all other header information are constants or metadata loaded
360 from SL::DB::Datev.pm.
362 Returns the first two entries for the header (see above: File Structure)
365 All params are mandatory:
366 C<params{from}>, C<params{to}>
367 and C<params{first_day_of_fiscal_year}> have to be in YYYYDDMM date string
369 Furthermore C<params{locked}> needs to be a boolean in number format (0|1).
372 =item kivitendo_to_datev
374 Returns the data structure C<@datev_data> as an array
378 Lightweight wrapper for form->format_amount.
379 Expects a number in kivitendo database format and returns the same number