X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FDATEV.pm;h=af188f70a4e911f433464217e59deaac3c146fef;hb=cf96c5880f1ba0694298d7541fedce47c8c99051;hp=cc19a12b60775d2cc8af6faceb607993dd41b6c7;hpb=2cca9fb0c1b4ec6bee39db8c50e49ab58a92dad1;p=kivitendo-erp.git diff --git a/SL/DATEV.pm b/SL/DATEV.pm index cc19a12b6..af188f70a 100644 --- a/SL/DATEV.pm +++ b/SL/DATEV.pm @@ -35,7 +35,9 @@ use SL::DATEV::KNEFile; 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; @@ -215,6 +217,26 @@ sub trans_id { return $self->{trans_id}; } +sub warnings { + my $self = shift; + + if (@_) { + $self->{warnings} = [@_]; + } else { + return $self->{warnings}; + } +} + +sub use_pk { + my $self = shift; + + if (@_) { + $self->{use_pk} = $_[0]; + } + + return $self->{use_pk}; +} + sub accnofrom { my $self = shift; @@ -352,7 +374,54 @@ sub csv_export { 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'; @@ -365,7 +434,7 @@ sub csv_export { die 'unrecognized exporttype'; } -return $result; + return $result; } sub obe_export { @@ -384,6 +453,15 @@ sub _sign { $_[0] <=> 0; } +sub locked { + my $self = shift; + + if (@_) { + $self->{locked} = $_[0]; + } + return $self->{locked}; +} + sub generate_datev_data { $main::lxdebug->enter_sub(); @@ -430,11 +508,18 @@ sub generate_datev_data { 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, @@ -463,7 +548,7 @@ sub generate_datev_data { 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, @@ -490,7 +575,7 @@ sub generate_datev_data { 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, @@ -946,6 +1031,7 @@ sub generate_datev_lines { 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 "") { @@ -960,13 +1046,27 @@ sub generate_datev_lines { $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? @@ -986,8 +1086,10 @@ sub generate_datev_lines { # $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: @@ -1078,7 +1180,7 @@ sub kne_buchungsexport { $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"); }; @@ -1342,94 +1444,40 @@ sub csv_export_for_tax_accountant { 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 ]; - - 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 lenght 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 - die "Not a valid value: '$row->{ $column->{kivi_datev_name} }'" . - " for '$column->{kivi_datev_name}' with amount '$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; - } - 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) = @_; - # EXTF_Buchungsstapel.csv: ISO-8859 text, with very long lines, with CRLF line terminators - 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(iso-8859-1)') 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(); } @@ -1601,7 +1649,7 @@ Forces a garbage collection on previous exports which will delete all exports th =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 @@ -1628,33 +1676,26 @@ Example: # }; -=item csv_buchungsexport - -Generates the CSV-Format data for the CSV DATEV export and returns -an 2-dimensional array as an array_ref. +=item check_vcnumbers_are_valid_pk_numbers -Requires $self->fromto for a valid DATEV header. +Returns 1 if all vcnumbers are suitable for the DATEV export, 0 if not. -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. +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". -To add or alter the structure of the data take a look at SL::DATEV::CSV.pm +All vcnumbers are checked, obsolete customers or vendors aren't exempt. -=item _csv_buchungsexport_to_file +There is also no check for the typical customer range 10000-69999 and the +typical vendor range 70000-99999. -Generates one downloadable csv file wrapped in a zip archive. -Basically this method is just a thin wrapper for TEXT::CSV_XS.pm - -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 @@ -1704,6 +1745,11 @@ correctly. 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