Projekt

Allgemein

Profil

Herunterladen (35,3 KB) Statistiken
| Zweig: | Markierung: | Revision:
package SL::DATEV::CSV;

use strict;
use Carp;
use DateTime;
use Encode qw(encode);
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 SL::Util qw(trim);
use SL::VATIDNr;

use Rose::Object::MakeMethods::Generic (
scalar => [ qw(datev_lines from to locked warnings) ],
);

my @kivitendo_to_datev = (
{
kivi_datev_name => 'umsatz',
csv_header_name => t8('Transaction Value'),
max_length => 13,
type => 'Value',
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',
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',
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',
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'),
},
{
kivi_datev_name => 'not yet implemented',
csv_header_name => t8('Base Transaction Value Currency Code'),
},
{
kivi_datev_name => 'konto',
csv_header_name => t8('Account'),
max_length => 9,
type => 'Account',
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,
type => 'Account',
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',
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',
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',
default => '',
input_check => sub { return 1 unless $::instance_conf->get_datev_export_format eq 'cp1252';
my ($text) = @_; check_encoding($text); },
valid_check => sub { return 1 if $::instance_conf->get_datev_export_format eq 'cp1252';
my ($text) = @_; check_encoding($text); },
formatter => sub { my ($input) = @_; return substr($input, 0, 12) },
},
{
kivi_datev_name => 'belegfeld2',
csv_header_name => t8('Invoice Field 2'),
max_length => 12,
type => 'Text',
default => '',
input_check => sub { my ($check) = @_; return 1 unless $check; return (ref (DateTime->from_kivitendo($check)) eq 'DateTime') },
formatter => sub { my ($input) = @_; return '' unless $input; return trim(DateTime->from_kivitendo($input)->strftime('%e%m%y')) },
valid_check => sub { my ($check) = @_; return 1 unless $check; return ($check =~ m/^[0-9]{5,6}$/) },
},
{
kivi_datev_name => 'not yet implemented',
csv_header_name => t8('Discount'),
type => 'Value',
},
{
kivi_datev_name => 'buchungstext',
csv_header_name => t8('Posting Text'),
max_length => 60,
type => 'Text',
default => '',
input_check => sub { return 1 unless $::instance_conf->get_datev_export_format eq 'cp1252';
my ($text) = @_; check_encoding($text); },
valid_check => sub { return 1 if $::instance_conf->get_datev_export_format eq 'cp1252';
my ($text) = @_; check_encoding($text); },
formatter => sub { my ($input) = @_; return substr($input, 0, 60) },
}, # pos 14
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'document_guid',
csv_header_name => t8('Link to invoice'),
max_length => 210, # DMS Application shortcut and GUID
# Example: "BEDI"
# "8DB85C02-4CC3-FF3E-06D7-7F87EEECCF3A".
type => 'Text',
default => '',
input_check => sub { my ($check) = @_; return 1 unless $check; return ($check =~ m/^[0-9a-fA-F]{8}\b-[0-9a-fA-F]{4}\b-[0-9a-fA-F]{4}\b-[0-9a-fA-F]{4}\b-[0-9a-fA-F]{12}$/) },
formatter => sub { my ($input) = @_; return '' unless $input; return 'BEDI "' . $input . '"' },

}, # pos 20
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'kost1',
csv_header_name => t8('Cost Center'),
max_length => 8,
type => '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',
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 { 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 ($ustid) = @_;
return 1 if ('' eq $ustid);
return SL::VATIDNr->validate($ustid);
},
formatter => sub { my ($input) = @_; $input =~ s/\s//g; return $input },
valid_check => sub {
my ($ustid) = @_;
return 1 if ('' eq $ustid);
return SL::VATIDNr->validate($ustid);
},
}, # pos 40
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
}, # pos 50
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
}, # pos 60
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
}, # pos 70
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
}, # pos 80
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
}, # pos 90
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
}, # pos 100
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
}, # pos 110
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'not yet implemented',
},
{
kivi_datev_name => 'locked',
csv_header_name => t8('Lock'),
max_length => 1,
type => 'Number',
default => 1,
valid_check => sub { my ($check) = @_; return ($check =~ m/^(0|1)$/) },
}, # pos 114
{
kivi_datev_name => 'leistungsdatum',
csv_header_name => t8('Payment Date'),
max_length => 8,
type => 'Date',
default => '',
input_check => sub { my ($check) = @_; return 1 if ('' eq $check); return (ref (DateTime->from_kivitendo($check)) eq 'DateTime') },
formatter => sub { my ($input) = @_; return '' if ('' eq $input); return DateTime->from_kivitendo($input)->strftime('%d%m%Y') },
valid_check => sub { my ($check) = @_; return 1 if ('' eq $check); return ($check =~ m/^[0-9]{8}$/) },
}, # pos 115
{
kivi_datev_name => 'not yet implemented',
},
# DATEV Prüfprogramm says: Only 116 fields are allowed
#{
# kivi_datev_name => 'not yet implemented',
#},
#{
# kivi_datev_name => 'not yet implemented',
#},
#{
# kivi_datev_name => 'not yet implemented',
#},
#{
# kivi_datev_name => 'not yet implemented',
#}, # pos 120
);

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 {
my ($test) = @_;
return undef unless $test;
if (eval {
encode('Windows-1252', $test, Encode::FB_CROAK|Encode::LEAVE_SRC);
1
}) {
return 1;
}
}

sub header {
my ($self) = @_;

my @header;

# we can safely set these defaults
# 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;

# datev metadata and the string length limits
my %meta_datev;
my %meta_datev_to_valid_length = (
beraternr => 7,
beratername => 25,
mandantennr => 5,
);

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 $coa = $::instance_conf->get_coa eq 'Germany-DATEV-SKR03EU' ? '03'
: $::instance_conf->get_coa eq 'Germany-DATEV-SKR04EU' ? '04'
: '';

my @header_row_1 = (
"EXTF", "510", 21, "Buchungsstapel", 7, $created_on, "", "ki",
"kivitendo-datev", "", $meta_datev{beraternr}, $meta_datev{mandantennr},
$self->first_day_of_fiscal_year->ymd(''), $length_of_accounts,
$self->from->ymd(''), $self->to->ymd(''), "", "", 1, "", $self->locked,
$default_curr, "", "", "","", $coa, "", "", "", ""
);
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);

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 (@kivitendo_to_datev) {
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;
}

# helper

sub _format_amount {
$::form->format_amount({ numberformat => '1000,00' }, @_);
}

sub first_day_of_fiscal_year {
$_[0]