package SL::DATEV::CSV;
use strict;
-
-use SL::Locale::String qw(t8);
-use SL::DB::Datev;
-
use Carp;
use DateTime;
+use Encode qw(decode);
+use Scalar::Util qw(looks_like_number);
+
+use SL::DB::Datev;
+use SL::DB::Chart;
+use SL::Helper::DateTime;
+use SL::Locale::String qw(t8);
+use Rose::Object::MakeMethods::Generic (
+ scalar => [ qw(datev_lines from to locked warnings) ],
+);
my @kivitendo_to_datev = (
{
csv_header_name => t8('Transaction Value'),
max_length => 13,
type => 'Value',
- valid_check => sub { return (shift =~ m/^\d{1,10}(\,\d{1,2})?$/) },
+ required => 1,
+ input_check => sub { my ($input) = @_; return (looks_like_number($input) && length($input) <= 13 && $input > 0) },
+ formatter => \&_format_amount,
+ valid_check => sub { my ($check) = @_; return ($check =~ m/^\d{1,10}(\,\d{1,2})?$/) },
},
{
kivi_datev_name => 'soll_haben_kennzeichen',
csv_header_name => t8('Debit/Credit Label'),
max_length => 1,
type => 'Text',
- valid_check => sub { return (shift =~ m/^(S|H)$/) },
+ required => 1,
+ default => 'S',
+ input_check => sub { my ($check) = @_; return ($check =~ m/^(S|H)$/) },
+ formatter => sub { my ($input) = @_; return $input eq 'H' ? 'H' : 'S' },
+ valid_check => sub { my ($check) = @_; return ($check =~ m/^(S|H)$/) },
},
{
kivi_datev_name => 'waehrung',
csv_header_name => t8('Transaction Value Currency Code'),
max_length => 3,
type => 'Text',
- valid_check => sub { return (shift =~ m/^[A-Z]{3}$/) },
+ default => '',
+ input_check => sub { my ($check) = @_; return ($check eq '' || $check =~ m/^[A-Z]{3}$/) },
+ valid_check => sub { my ($check) = @_; return ($check =~ m/^[A-Z]{3}$/) },
},
{
kivi_datev_name => 'wechselkurs',
csv_header_name => t8('Exchange Rate'),
max_length => 11,
type => 'Number',
- valid_check => sub { return (shift =~ m/^[0-9]*\.?[0-9]*$/) },
+ default => '',
+ valid_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]*\.?[0-9]*$/) },
},
{
kivi_datev_name => 'not yet implemented',
- csv_header_name => t8('Base Transaction Value'),
+ sv_header_name => t8('Base Transaction Value'),
},
{
kivi_datev_name => 'not yet implemented',
{
kivi_datev_name => 'konto',
csv_header_name => t8('Account'),
- max_length => 9, # May contain a maximum of 8 or 9 digits -> perldoc
+ max_length => 9,
type => 'Account',
- valid_check => sub { return (shift =~ m/^[0-9]{4,9}$/) },
+ required => 1,
+ input_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]{4,9}$/) },
},
{
kivi_datev_name => 'gegenkonto',
csv_header_name => t8('Contra Account'),
- max_length => 9, # May contain a maximum of 8 or 9 digits -> perldoc
+ max_length => 9,
type => 'Account',
- valid_check => sub { return (shift =~ m/^[0-9]{4,9}$/) },
+ required => 1,
+ input_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]{4,9}$/) },
},
{
kivi_datev_name => 'buchungsschluessel',
csv_header_name => t8('Posting Key'),
max_length => 2,
type => 'Text',
- valid_check => sub { return (shift =~ m/^[0-9]{0,2}$/) },
+ default => '',
+ input_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]{0,2}$/) },
},
{
kivi_datev_name => 'datum',
csv_header_name => t8('Invoice Date'),
max_length => 4,
type => 'Date',
- valid_check => sub { return (shift =~ m/^[0-9]{4}$/) },
+ required => 1,
+ input_check => sub { my ($check) = @_; return (ref (DateTime->from_kivitendo($check)) eq 'DateTime') },
+ formatter => sub { my ($input) = @_; return DateTime->from_kivitendo($input)->strftime('%d%m') },
+ valid_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]{4}$/) },
},
{
kivi_datev_name => 'belegfeld1',
csv_header_name => t8('Invoice Field 1'),
max_length => 12,
type => 'Text',
- valid_check => sub { my $text = shift; check_encoding($text); },
+ default => '',
+ input_check => sub { my ($text) = @_; check_encoding($text); },
+ formatter => sub { my ($input) = @_; return substr($input, 0, 12) },
},
{
kivi_datev_name => 'not yet implemented',
csv_header_name => t8('Invoice Field 2'),
- max_length => 12,
+ max_length => 12,
type => 'Text',
- valid_check => sub { return (shift =~ m/[ -~]{1,12}/) },
+ default => '',
+ valid_check => sub { my ($check) = @_; return ($check =~ m/[ -~]{1,12}/) },
},
{
kivi_datev_name => 'not yet implemented',
csv_header_name => t8('Posting Text'),
max_length => 60,
type => 'Text',
- valid_check => sub { my $text = shift; return 1 unless $text; check_encoding($text); },
+ default => '',
+ input_check => sub { my ($text) = @_; return 1 unless $text; check_encoding($text); },
+ formatter => sub { my ($input) = @_; return substr($input, 0, 60) },
}, # pos 14
{
kivi_datev_name => 'not yet implemented',
csv_header_name => t8('Cost Center'),
max_length => 8,
type => 'Text',
- valid_check => sub { my $text = shift; return 1 unless $text; check_encoding($text); },
+ default => '',
+ input_check => sub { my ($text) = @_; return 1 unless $text; check_encoding($text); },
+ formatter => sub { my ($input) = @_; return substr($input, 0, 8) },
}, # pos 37
{
kivi_datev_name => 'kost2',
csv_header_name => t8('Cost Center'),
max_length => 8,
type => 'Text',
- valid_check => sub { my $text = shift; return 1 unless $text; check_encoding($text); },
+ default => '',
+ input_check => sub { my ($text) = @_; return 1 unless $text; check_encoding($text); },
+ formatter => sub { my ($input) = @_; return substr($input, 0, 8) },
}, # pos 38
{
kivi_datev_name => 'not yet implemented',
csv_header_name => t8('KOST Quantity'),
max_length => 9,
type => 'Number',
- valid_check => sub { return (shift =~ m/^[0-9]{0,9}$/) },
+ valid_check => sub { my ($check) = @_; return ($check =~ m/^[0-9]{0,9}$/) },
}, # pos 39
{
kivi_datev_name => 'ustid',
csv_header_name => t8('EU Member State and VAT ID Number'),
max_length => 15,
type => 'Text',
+ default => '',
+ input_check => sub { my ($check) = @_; return ($check eq '' || $check =~ m/[A-Z]{2}\w{5,13}/) },
+ formatter => sub { my ($input) = @_; $input =~ s/\s//g; return $input },
valid_check => sub {
- my $ustid = shift;
- return 1 unless defined($ustid);
+ my ($ustid) = @_;
+ return 1 if ('' eq $ustid);
return ($ustid =~ m/^CH|^[A-Z]{2}\w{5,13}$/);
},
}, # pos 40
);
+sub new {
+ my $class = shift;
+ my %data = @_;
+
+ croak(t8('We need a valid from date')) unless (ref $data{from} eq 'DateTime');
+ croak(t8('We need a valid to date')) unless (ref $data{to} eq 'DateTime');
+ croak(t8('We need a array of datev_lines')) unless (ref $data{datev_lines} eq 'ARRAY');
+
+ my $obj = bless {}, $class;
+ $obj->$_($data{$_}) for keys %data;
+ $obj;
+}
+
sub check_encoding {
- use Encode qw( decode );
- # counter test: arabic doesnt work: ݐ
- my $test = shift;
+ my ($test) = @_;
return undef unless $test;
if (eval {
decode('Windows-1252', $test, Encode::FB_CROAK|Encode::LEAVE_SRC);
}
}
-sub kivitendo_to_datev {
- my $self = shift;
-
- my $entries = scalar (@kivitendo_to_datev);
- push @kivitendo_to_datev, { kivi_datev_name => 'not yet implemented' } for 1 .. (116 - $entries);
- return @kivitendo_to_datev;
+sub _kivitendo_to_datev {
+ @kivitendo_to_datev, ({ kivi_datev_name => 'not yet implemented' }) x (116 - @kivitendo_to_datev);
}
-sub generate_csv_header {
- my ($self, %params) = @_;
-
- # we need from and to in YYYYDDMM
- croak "Wrong format for from" unless $params{from} =~ m/^[0-9]{8}$/;
- croak "Wrong format for to" unless $params{to} =~ m/^[0-9]{8}$/;
-
- # who knows if we want locking and when our fiscal year starts
- croak "Wrong state of locking" unless $params{locked} =~ m/(0|1)/;
- croak "No startdate of fiscal year" unless $params{first_day_of_fiscal_year} =~ m/^[0-9]{8}$/;
+sub header {
+ my ($self) = @_;
+ my @header;
# we can safely set these defaults
- my $today = DateTime->now(time_zone => "local");
+ # TODO get length_of_accounts from DATEV.pm
+ my $today = DateTime->now_local;
my $created_on = $today->ymd('') . $today->hms('') . '000';
my $length_of_accounts = length(SL::DB::Manager::Chart->get_first(where => [charttype => 'A'])->accno) // 4;
my $default_curr = SL::DB::Default->get_default_currency;
my $datev = SL::DB::Manager::Datev->get_first();
while (my ($k, $v) = each %meta_datev_to_valid_length) {
+ next unless $datev->{$k};
$meta_datev{$k} = substr $datev->{$k}, 0, $v;
}
- my @header = (
- "EXTF", "300", 21, "Buchungsstapel", 7, $created_on, "", "ki",
+ my @header_row_1 = (
+ "EXTF", "510", 21, "Buchungsstapel", 7, $created_on, "", "ki",
"kivitendo-datev", "", $meta_datev{beraternr}, $meta_datev{mandantennr},
- $params{first_day_of_fiscal_year}, $length_of_accounts,
- $params{from}, $params{to}, "", "", 1, "", $params{locked},
+ $self->first_day_of_fiscal_year->ymd(''), $length_of_accounts,
+ $self->from->ymd(''), $self->to->ymd(''), "", "", 1, "", $self->locked,
$default_curr, "", "", "",""
);
+ push @header, [ @header_row_1 ];
+
+ # second header row, just the column names
+ push @header, [ map { $_->{csv_header_name} } _kivitendo_to_datev() ];
+
+ return \@header;
+}
+
+sub lines {
+ my ($self) = @_;
+
+ my (@array_of_datev, @warnings);
+ my @csv_columns = _kivitendo_to_datev();
+
+ foreach my $row (@{ $self->datev_lines }) {
+ my @current_datev_row;
+
+ # 1. check all datev_lines and see if we have a defined value
+ # 2. if we don't have a defined value set a default if exists
+ # 3. otherwise die
+ foreach my $column (@csv_columns) {
+ if ($column->{kivi_datev_name} eq 'not yet implemented') {
+ push @current_datev_row, '';
+ next;
+ }
+ my $data = $row->{$column->{kivi_datev_name}};
+ if (!defined $data) {
+ if (defined $column->{default}) {
+ $data = $column->{default};
+ } else {
+ die 'No sensible value or a sensible default found for the entry: ' . $column->{kivi_datev_name};
+ }
+ }
+ # checkpoint a: no undefined data. All strict checks now!
+ if (exists $column->{input_check} && !$column->{input_check}->($data)) {
+ die t8("Wrong field value '#1' for field '#2' for the transaction with amount '#3'",
+ $data, $column->{kivi_datev_name}, $row->{umsatz});
+ }
+ # checkpoint b: we can safely format the input
+ if ($column->{formatter}) {
+ $data = $column->{formatter}->($data);
+ }
+ # checkpoint c: all soft checks now, will pop up as a user warning
+ if (exists $column->{valid_check} && !$column->{valid_check}->($data)) {
+ push @warnings, t8("Wrong field value '#1' for field '#2' for the transaction" .
+ " with amount '#3'", $data, $column->{kivi_datev_name}, $row->{umsatz});
+ }
+ push @current_datev_row, $data;
+ }
+ push @array_of_datev, \@current_datev_row;
+ }
+ $self->warnings(\@warnings);
+ return \@array_of_datev;
+}
- return @header;
+# helper
+
+sub _format_amount {
+ $::form->format_amount({ numberformat => '1000,00' }, @_);
}
+
+sub first_day_of_fiscal_year {
+ $_[0]->to->clone->truncate(to => 'year');
+}
+
1;
__END__
=head1 SYNOPSIS
+ use SL::DATEV qw(:CONSTANTS);
+ use SL::DATEV::CSV;
+
+ my $startdate = DateTime->new(year => 2014, month => 9, day => 1);
+ my $enddate = DateTime->new(year => 2014, month => 9, day => 31);
+ my $datev = SL::DATEV->new(
+ exporttype => DATEV_ET_BUCHUNGEN,
+ format => DATEV_FORMAT_CSV,
+ from => $startdate,
+ to => $enddate,
+ );
+ $datev->generate_datev_data;
+
+ my $datev_csv = SL::DATEV::CSV->new(datev_lines => $datev->generate_datev_lines,
+ from => $datev->from,
+ to => $datev->to,
+ locked => $datev->locked,
+ );
+ $datev_csv->header; # returns the required 2 rows of header ($aref = [ ["row1" ..], [ "row2" .. ] ]) as array of array
+ $datev_csv->lines; # returns an array_ref of rows of array_refs soll uns die ein Arrayref von Zeilen zurückgeben, die jeweils Arrayrefs sind
+ $datev_csv->warnings; # returns warnings
+
+
+ # The above object methods can be directly chained to a CSV export function, like this:
+ my $csv_file = IO::File->new($somewhere_in_filesystem)') or die "Can't open: $!";
+ $csv->print($csv_file, $_) for @{ $datev_csv->header };
+ $csv->print($csv_file, $_) for @{ $datev_csv->lines };
+ $csv_file->close;
+ $self->{warnings} = $datev_csv->warnings;
+
+
+
+
+=head1 DESCRIPTION
+
The parsing of the DATEV CSV is index based, therefore the correct
column must be present at the corresponding index, i.e.:
Index 2
=over 4
+=item new PARAMS
+
+Constructor for CSV-DATEV export.
+Checks mandantory params as described in section synopsis.
+
=item check_encoding
Helper function, returns true if a string is not empty and cp1252 encoded
+For example some arabic utf-8 like ݐ will return false
-=item generate_csv_header(from => 'YYYYDDMM', to => 'YYYYDDMM', locked => 0,
- first_day_of_fiscal_year => 'YYYYDDMM')
+=item header
Mostly all other header information are constants or metadata loaded
from SL::DB::Datev.pm.
Returns the first two entries for the header (see above: File Structure)
as an array.
-All params are mandatory:
-C<params{from}>, C<params{to}>
-and C<params{first_day_of_fiscal_year}> have to be in YYYYDDMM date string
-format.
-Furthermore C<params{locked}> needs to be a boolean in number format (0|1).
-
-
=item kivitendo_to_datev
Returns the data structure C<@datev_data> as an array
+=item _format_amount
+
+Lightweight wrapper for form->format_amount.
+Expects a number in kivitendo database format and returns the same number
+in DATEV format.
+
+=item first_day_of_fiscal_year
+
+Takes a look at $self->to to determine the first day of the fiscal year.
+
+=item lines
+
+Generates the CSV-Format data for the CSV DATEV export and returns
+an 2-dimensional array as an array_ref.
+May additionally return a second array_ref with warnings.
+
+Requires the same date fields as the constructor for a valid DATEV header.
+
+Furthermore we assume that the first day of the fiscal year is
+the first of January and we cannot guarantee that our data in kivitendo
+is locked, that means a booking cannot be modified after a defined (vat tax)
+period.
+Some validity checks (max_length and regex) will be done if the
+data structure contains them and the field is defined.
+
+To add or alter the structure of the data take a look at the C<@kivitendo_to_datev> structure.
+
=back
+
+=head1 TODO CAVEAT
+
+One can circumevent the check of the warnings.quite easily,
+becaus warnings are generated after the call to lines:
+
+ # WRONG usage
+ die if @{ $datev_csv->warnings };
+ somethin_with($datev_csv->lines);
+
+ # safe usage
+ my $lines = $datev_csv->lines;
+ die if @{ $datev_csv->warnings };
+ somethin_with($lines);
+
projects / kivitendo-erp.git / blobdiff