use SL::DATEV::CSV;
use SL::DB;
use SL::HTML::Util ();
+use SL::Iconv;
use SL::Locale::String qw(t8);
+use SL::VATIDNr;
use Data::Dumper;
use DateTime;
}
}
+sub use_pk {
+ my $self = shift;
+
+ if (@_) {
+ $self->{use_pk} = $_[0];
+ }
+
+ return $self->{use_pk};
+}
+
sub accnofrom {
my $self = shift;
die 'no exporttype set!' unless $self->has_exporttype;
if ($self->exporttype == DATEV_ET_BUCHUNGEN) {
- _csv_buchungsexport_to_file($self, data => $self->csv_buchungsexport);
+
+ $self->generate_datev_data(from_to => $self->fromto);
+ return if $self->errors;
+
+ my $datev_csv = SL::DATEV::CSV->new(
+ datev_lines => $self->generate_datev_lines,
+ from => $self->from,
+ to => $self->to,
+ locked => $self->locked,
+ );
+
+
+ my $filename = "EXTF_DATEV_kivitendo" . $self->from->ymd() . '-' . $self->to->ymd() . ".csv";
+
+ my $csv = Text::CSV_XS->new({
+ binary => 1,
+ sep_char => ";",
+ always_quote => 1,
+ eol => "\r\n",
+ }) or die "Cannot use CSV: ".Text::CSV_XS->error_diag();
+
+ # get encoding from defaults - use cp1252 if DATEV strict export is used
+ my $enc = ($::instance_conf->get_datev_export_format eq 'cp1252') ? 'cp1252' : 'utf-8';
+ my $csv_file = IO::File->new($self->export_path . '/' . $filename, ">:encoding($enc)") 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;
+
+ # convert utf-8 to cp1252//translit if set
+ if ($::instance_conf->get_datev_export_format eq 'cp1252-translit') {
+
+ my $filename_translit = "EXTF_DATEV_kivitendo_translit" . $self->from->ymd() . '-' . $self->to->ymd() . ".csv";
+ open my $fh_in, '<:encoding(UTF-8)', $self->export_path . '/' . $filename or die "could not open $filename for reading: $!";
+ open my $fh_out, '>', $self->export_path . '/' . $filename_translit or die "could not open $filename_translit for writing: $!";
+
+ my $converter = SL::Iconv->new("utf-8", "cp1252//translit");
+
+ print $fh_out $converter->convert($_) while <$fh_in>;
+ close $fh_in;
+ close $fh_out;
+
+ unlink $self->export_path . '/' . $filename or warn "Could not unlink $filename: $!";
+ $filename = $filename_translit;
+ }
+
+ return { download_token => $self->download_token, filenames => $filename };
} elsif ($self->exporttype == DATEV_ET_STAMM) {
die 'will never be implemented';
die 'unrecognized exporttype';
}
-return $result;
+ return $result;
}
sub obe_export {
$_[0] <=> 0;
}
+sub locked {
+ my $self = shift;
+
+ if (@_) {
+ $self->{locked} = $_[0];
+ }
+ return $self->{locked};
+}
+
sub generate_datev_data {
$main::lxdebug->enter_sub();
my %all_taxchart_ids = selectall_as_map($form, $self->dbh, qq|SELECT DISTINCT chart_id, TRUE AS is_set FROM tax|, 'chart_id', 'is_set');
+ my $ar_accno = "c.accno";
+ my $ap_accno = "c.accno";
+ if ( $self->use_pk ) {
+ $ar_accno = "CASE WHEN ac.chart_link = 'AR' THEN ct.customernumber ELSE c.accno END as accno";
+ $ap_accno = "CASE WHEN ac.chart_link = 'AP' THEN ct.vendornumber ELSE c.accno END as accno";
+ }
+
my $query =
qq|SELECT ac.acc_trans_id, ac.transdate, ac.gldate, ac.trans_id,ar.id, ac.amount, ac.taxkey, ac.memo,
ar.invnumber, ar.duedate, ar.amount as umsatz, ar.deliverydate, ar.itime::date,
ct.name, ct.ustid, ct.customernumber AS vcnumber, ct.id AS customer_id, NULL AS vendor_id,
- c.accno, c.description AS accname, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
+ $ar_accno, c.description AS accname, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
ar.invoice,
t.rate AS taxrate, t.taxdescription,
'ar' as table,
SELECT ac.acc_trans_id, ac.transdate, ac.gldate, ac.trans_id,ap.id, ac.amount, ac.taxkey, ac.memo,
ap.invnumber, ap.duedate, ap.amount as umsatz, ap.deliverydate, ap.itime::date,
ct.name, ct.ustid, ct.vendornumber AS vcnumber, NULL AS customer_id, ct.id AS vendor_id,
- c.accno, c.description AS accname, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
+ $ap_accno, c.description AS accname, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
ap.invoice,
t.rate AS taxrate, t.taxdescription,
'ap' as table,
UNION ALL
SELECT ac.acc_trans_id, ac.transdate, ac.gldate, ac.trans_id,gl.id, ac.amount, ac.taxkey, ac.memo,
- gl.reference AS invnumber, gl.transdate AS duedate, ac.amount as umsatz, NULL as deliverydate, gl.itime::date,
+ gl.reference AS invnumber, NULL AS duedate, ac.amount as umsatz, gl.deliverydate, gl.itime::date,
gl.description AS name, NULL as ustid, '' AS vcname, NULL AS customer_id, NULL AS vendor_id,
c.accno, c.description AS accname, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
FALSE AS invoice,
if ($trans_lines >= 2) {
+ # Personenkontenerweiterung: accno has already been replaced if use_pk was set
$datev_data{'gegenkonto'} = $transaction->[$haben]->{'accno'};
$datev_data{'konto'} = $transaction->[$soll]->{'accno'};
if ($transaction->[$haben]->{'invnumber'} ne "") {
$datev_data{buchungstext} = $transaction->[$haben]->{'name'};
}
if (($transaction->[$haben]->{'ustid'} // '') ne "") {
- $datev_data{ustid} = $transaction->[$haben]->{'ustid'};
+ $datev_data{ustid} = SL::VATIDNr->normalize($transaction->[$haben]->{'ustid'});
}
if (($transaction->[$haben]->{'duedate'} // '') ne "") {
$datev_data{belegfeld2} = $transaction->[$haben]->{'duedate'};
}
+
+ # if deliverydate exists, add it to datev export if it is
+ # * an ar/ap booking that is not a payment
+ # * a gl booking
+ if ( ($transaction->[$haben]->{'deliverydate'} // '') ne ''
+ && (
+ ( $transaction->[$haben]->{'table'} =~ /^(ar|ap)$/
+ && $transaction->[$haben]->{'link'} !~ m/_paid/
+ && $transaction->[$soll]->{'link'} !~ m/_paid/
+ )
+ || $transaction->[$haben]->{'table'} eq 'gl'
+ )
+ ) {
+ $datev_data{leistungsdatum} = $transaction->[$haben]->{'deliverydate'};
+ }
}
- $datev_data{soll_haben_kennzeichen} = (0 < $umsatz) ? 'H' : 'S';
$datev_data{umsatz} = abs($umsatz); # sales invoices without tax have a different sign???
# Dies ist die einzige Stelle die datevautomatik auswertet. Was soll gesagt werden?
# $datev_data{buchungsschluessel} = !$datevautomatik ? $taxkey : "4";
$datev_data{buchungsschluessel} = $taxkey;
}
+ # set lock for each transaction
+ $datev_data{locked} = $self->locked;
- push(@datev_lines, \%datev_data);
+ push(@datev_lines, \%datev_data) if $datev_data{umsatz};
}
# example of modifying export data:
$name =~ s/\ *$//;
$kne_file->add_block("\x1E" . $name . "\x1C");
- $kne_file->add_block("\xBA" . $kne->{'ustid'} . "\x1C") if $kne->{'ustid'};
+ $kne_file->add_block("\xBA" . SL::VATIDNr->normalize($kne->{'ustid'}) . "\x1C") if $kne->{'ustid'};
$kne_file->add_block("\xB3" . $kne->{'waehrung'} . "\x1C" . "\x79");
};
return { download_token => $self->download_token, filenames => \@filenames };
}
-sub csv_buchungsexport {
- my $self = shift;
- my %params = @_;
-
- $self->generate_datev_data(from_to => $self->fromto);
- return if $self->errors;
-
- my @datev_lines = @{ $self->generate_datev_lines };
-
- my @csv_columns = SL::DATEV::CSV->kivitendo_to_datev();
- my @csv_headers = SL::DATEV::CSV->generate_csv_header(
- from => $self->from->ymd(''),
- to => $self->to->ymd(''),
- first_day_of_fiscal_year => $self->to->year . '0101',
- locked => 0
- );
-
- my @array_of_datev;
-
- # 2 Headers
- push @array_of_datev, \@csv_headers;
- push @array_of_datev, [ map { $_->{csv_header_name} } @csv_columns ];
-
- my @warnings;
- foreach my $row ( @datev_lines ) {
- my @current_datev_row;
-
- # shorten strings
- if ($row->{belegfeld1}) {
- $row->{buchungsbes} = $row->{belegfeld1} if $row->{belegfeld1};
- $row->{belegfeld1} = substr($row->{belegfeld1}, 0, 12);
- $row->{buchungsbes} = substr($row->{buchungsbes}, 0, 60);
- }
-
- $row->{datum} = datetofour($row->{datum}, 0);
- $row->{kost1} = substr($row->{kost1}, 0, 8) if $row->{kost1};
- $row->{kost2} = substr($row->{kost2}, 0, 8) if $row->{kost2};
-
- # , as decimal point and trim for UstID
- $row->{umsatz} =~ s/\./,/;
- $row->{ustid} =~ s/\s//g if $row->{ustid}; # trim whitespace
+sub check_vcnumbers_are_valid_pk_numbers {
+ my ($self) = @_;
- foreach my $column (@csv_columns) {
- if (exists $column->{max_length} && $column->{kivi_datev_name} ne 'not yet implemented') {
- # check max length
- die "Incorrect length of field" if length($row->{ $column->{kivi_datev_name} }) > $column->{max_length};
- }
- if (exists $column->{valid_check} && $column->{kivi_datev_name} ne 'not yet implemented') {
- # more checks, listed as user warnings
- push @warnings, t8("Wrong field value '#1' for field '#2' for the transaction" .
- " with amount '#3'",$row->{ $column->{kivi_datev_name} },
- $column->{kivi_datev_name},$row->{umsatz})
- unless ($column->{valid_check}->($row->{ $column->{kivi_datev_name} }));
- }
- push @current_datev_row, $row->{ $column->{kivi_datev_name} };
- }
- push @array_of_datev, \@current_datev_row;
- }
- $self->warnings(@warnings) if @warnings;
- return \@array_of_datev;
+ # better use a class variable and set this in sub new (also needed in DATEV::CSV)
+ # calculation is also a bit more sane in sub check_valid_length_of_accounts
+ my $length_of_accounts = length(SL::DB::Manager::Chart->get_first(where => [charttype => 'A'])->accno) // 4;
+ my $pk_length = $length_of_accounts + 1;
+ my $query = <<"SQL";
+ SELECT customernumber AS vcnumber FROM customer WHERE customernumber !~ '^[[:digit:]]{$pk_length}\$'
+ UNION
+ SELECT vendornumber AS vcnumber FROM vendor WHERE vendornumber !~ '^[[:digit:]]{$pk_length}\$'
+ LIMIT 1;
+SQL
+ my ($has_non_pk_accounts) = selectrow_query($::form, SL::DB->client->dbh, $query);
+ return defined $has_non_pk_accounts ? 0 : 1;
}
-sub _csv_buchungsexport_to_file {
- my $self = shift;
- my %params = @_;
-
- # we can definitely deny shorter data structures
- croak ("Need at least 2 rows for header info") unless scalar @{ $params{data} } > 1;
- my $filename = "EXTF_DATEV_kivitendo" . $self->from->ymd() . '-' . $self->to->ymd() . ".csv";
- my @data = \$params{data};
+sub check_valid_length_of_accounts {
+ my ($self) = @_;
- my $csv = Text::CSV_XS->new({
- binary => 1,
- sep_char => ";",
- always_quote => 1,
- eol => "\r\n",
- }) or die "Cannot use CSV: ".Text::CSV_XS->error_diag();
+ my $query = <<"SQL";
+ SELECT DISTINCT char_length (accno) FROM chart WHERE charttype='A' AND id in (select chart_id from acc_trans);
+SQL
- if ($csv->version >= 1.18) {
- # get rid of stupid datev warnings in "Validity program"
- $csv->quote_empty(1);
+ my $accno_length = selectall_hashref_query($::form, SL::DB->client->dbh, $query);
+ if (1 < scalar @$accno_length) {
+ $::form->error(t8("Invalid combination of ledger account number length." .
+ " Mismatch length of #1 with length of #2. Please check your account settings. ",
+ $accno_length->[0]->{char_length}, $accno_length->[1]->{char_length}));
}
-
- my $csv_file = IO::File->new($self->export_path . '/' . $filename, '>:encoding(cp1252)') or die "Can't open: $!";
- $csv->print($csv_file, $_) for @{ $params{data} };
- $csv_file->close;
-
- return { download_token => $self->download_token, filenames => $params{filename} };
+ return 1;
}
+
sub DESTROY {
clean_temporary_directories();
}
=item errors
-Returns a list of errors that occured. If no errors occured, the export was a success.
+Returns a list of errors that occurred. If no errors occurred, the export was a success.
=item export
# };
-=item csv_buchungsexport
-
-Generates the CSV-Format data for the CSV DATEV export and returns
-an 2-dimensional array as an array_ref.
-
-Requires $self->fromto for a valid DATEV header.
+=item check_vcnumbers_are_valid_pk_numbers
-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.
+Returns 1 if all vcnumbers are suitable for the DATEV export, 0 if not.
-To add or alter the structure of the data take a look at SL::DATEV::CSV.pm
+Finds the default length of charts (e.g. 4), adds 1 for the pk chart length
+(e.g. 5), and checks the database for any customers or vendors whose customer-
+or vendornumber doesn't consist of only numbers with exactly that length. E.g.
+for a chart length of four "10001" would be ok, but not "10001b" or "1000".
-=item _csv_buchungsexport_to_file
+All vcnumbers are checked, obsolete customers or vendors aren't exempt.
-Generates one downloadable csv file wrapped in a zip archive.
-Basically this method is just a thin wrapper for TEXT::CSV_XS.pm
+There is also no check for the typical customer range 10000-69999 and the
+typical vendor range 70000-99999.
-Generates a CSV-file with the same encodings as defined in DATEV Format CSV 2015:
- $ file
- $ EXTF_Buchungsstapel.csv: ISO-8859 text, with very long lines, with CRLF line terminators
-
-Usage: _csv_buchungsexport_to_file($self, data => $self->csv_buchungsexport);
+=item check_valid_length_of_accounts
+Returns 1 if all currently booked accounts have only one common number length domain (e.g. 4 or 6).
+Will throw an error if more than one distinct size is detected.
+The error message gives a short hint with the value of the (at least)
+two mismatching number length domains.
=back
Set boundary account numbers for the export. Only useful for a stammdaten export.
+=item locked
+
+Boolean if the transactions are locked (read-only in kivitenod) or not.
+Default value is false
+
=back
=head1 CONSTANTS