X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FDATEV.pm;h=9418c483970ab13993bffb6b9a633766ed17c059;hb=b2bedb6b5eff41a17ddb194df1c75213b841f3bb;hp=bf96fa4471a52c3eb912245658372f35dcec3296;hpb=975304c772762b6a029c21aab00f3f449ed5cdf5;p=kivitendo-erp.git diff --git a/SL/DATEV.pm b/SL/DATEV.pm index bf96fa447..9418c4839 100644 --- a/SL/DATEV.pm +++ b/SL/DATEV.pm @@ -32,6 +32,7 @@ use strict; use SL::DBUtils; use SL::DATEV::KNEFile; +use SL::DATEV::CSV; use SL::DB; use SL::HTML::Util (); use SL::Locale::String qw(t8); @@ -214,6 +215,16 @@ sub trans_id { return $self->{trans_id}; } +sub warnings { + my $self = shift; + + if (@_) { + $self->{warnings} = [@_]; + } else { + return $self->{warnings}; + } +} + sub accnofrom { my $self = shift; @@ -345,7 +356,26 @@ sub kne_export { } sub csv_export { - die 'not yet implemented'; + my ($self) = @_; + my $result; + + die 'no exporttype set!' unless $self->has_exporttype; + + if ($self->exporttype == DATEV_ET_BUCHUNGEN) { + _csv_buchungsexport_to_file($self, data => $self->csv_buchungsexport); + + } elsif ($self->exporttype == DATEV_ET_STAMM) { + die 'will never be implemented'; + # 'Background: Export should only contain non + # DATEV-Charts and DATEV import will only + # import new Charts.' + } elsif ($self->exporttype == DATEV_ET_CSV) { + $result = $self->csv_export_for_tax_accountant; + } else { + die 'unrecognized exporttype'; + } + +return $result; } sub obe_export { @@ -383,6 +413,17 @@ sub generate_datev_data { $gl_department_id_filter = " AND gl.department_id = ? "; } + my ($gl_itime_filter, $ar_itime_filter, $ap_itime_filter); + if ( $form->{gldatefrom} ) { + $gl_itime_filter = " AND gl.itime >= ? "; + $ar_itime_filter = " AND ar.itime >= ? "; + $ap_itime_filter = " AND ap.itime >= ? "; + } else { + $gl_itime_filter = ""; + $ar_itime_filter = ""; + $ap_itime_filter = ""; + } + if ( $self->{trans_id} ) { # ignore dates when trans_id is passed so that the entire transaction is # checked, not just either the initial bookings or the subsequent payments @@ -409,16 +450,21 @@ sub generate_datev_data { 'ar' as table, tc.accno AS tax_accno, tc.description AS tax_accname, ar.department_id, - ar.notes + ar.notes, + project.projectnumber as projectnumber, project.description as projectdescription, + department.description as departmentdescription FROM acc_trans ac LEFT JOIN ar ON (ac.trans_id = ar.id) LEFT JOIN customer ct ON (ar.customer_id = ct.id) LEFT JOIN chart c ON (ac.chart_id = c.id) LEFT JOIN tax t ON (ac.tax_id = t.id) LEFT JOIN chart tc ON (t.chart_id = tc.id) + LEFT JOIN department ON (department.id = ar.department_id) + LEFT JOIN project ON (project.id = ar.globalproject_id) WHERE (ar.id IS NOT NULL) AND $fromto $trans_id_filter + $ar_itime_filter $ar_department_id_filter $filter @@ -433,16 +479,21 @@ sub generate_datev_data { 'ap' as table, tc.accno AS tax_accno, tc.description AS tax_accname, ap.department_id, - ap.notes + ap.notes, + project.projectnumber as projectnumber, project.description as projectdescription, + department.description as departmentdescription FROM acc_trans ac LEFT JOIN ap ON (ac.trans_id = ap.id) LEFT JOIN vendor ct ON (ap.vendor_id = ct.id) LEFT JOIN chart c ON (ac.chart_id = c.id) LEFT JOIN tax t ON (ac.tax_id = t.id) LEFT JOIN chart tc ON (t.chart_id = tc.id) + LEFT JOIN department ON (department.id = ap.department_id) + LEFT JOIN project ON (project.id = ap.globalproject_id) WHERE (ap.id IS NOT NULL) AND $fromto $trans_id_filter + $ap_itime_filter $ap_department_id_filter $filter @@ -457,23 +508,37 @@ sub generate_datev_data { 'gl' as table, tc.accno AS tax_accno, tc.description AS tax_accname, gl.department_id, - gl.notes + gl.notes, + '' as projectnumber, '' as projectdescription, + department.description as departmentdescription FROM acc_trans ac LEFT JOIN gl ON (ac.trans_id = gl.id) LEFT JOIN chart c ON (ac.chart_id = c.id) LEFT JOIN tax t ON (ac.tax_id = t.id) LEFT JOIN chart tc ON (t.chart_id = tc.id) + LEFT JOIN department ON (department.id = gl.department_id) WHERE (gl.id IS NOT NULL) AND $fromto $trans_id_filter + $gl_itime_filter $gl_department_id_filter $filter ORDER BY trans_id, acc_trans_id|; my @query_args; - if ( $form->{department_id} ) { - push(@query_args, ($form->{department_id}) x 3); + if ( $form->{gldatefrom} or $form->{department_id} ) { + + for ( 1 .. 3 ) { + if ( $form->{gldatefrom} ) { + my $glfromdate = $::locale->parse_date_to_object($form->{gldatefrom}); + die "illegal data" unless ref($glfromdate) eq 'DateTime'; + push(@query_args, $glfromdate); + } + if ( $form->{department_id} ) { + push(@query_args, $form->{department_id}); + } + } } my $sth = prepare_execute_query($form, $self->dbh, $query, @query_args); @@ -765,9 +830,6 @@ sub datetofour { my ($day, $month, $year) = split(/\./, $date); - if ($day =~ /^0/) { - $day = substr($day, 1, 1); - } if (length($month) < 2) { $month = "0" . $month; } @@ -901,6 +963,8 @@ sub generate_datev_lines { } $datev_data{datum} = $transaction->[$haben]->{'transdate'}; $datev_data{waehrung} = 'EUR'; + $datev_data{kost1} = $transaction->[$haben]->{'departmentdescription'}; + $datev_data{kost2} = $transaction->[$haben]->{'projectdescription'}; if ($transaction->[$haben]->{'name'} ne "") { $datev_data{buchungstext} = $transaction->[$haben]->{'name'}; @@ -912,7 +976,7 @@ sub generate_datev_lines { $datev_data{belegfeld2} = $transaction->[$haben]->{'duedate'}; } } - + $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? @@ -1202,7 +1266,7 @@ sub csv_export_for_tax_accountant { taxkey => { 'text' => $::locale->text('Taxkey'), }, tax_accname => { 'text' => $::locale->text('Tax Account Name'), }, tax_accno => { 'text' => $::locale->text('Tax Account'), }, - transdate => { 'text' => $::locale->text('Invoice Date'), }, + transdate => { 'text' => $::locale->text('Transdate'), }, vcnumber => { 'text' => $::locale->text('Customer/Vendor Number'), }, ); @@ -1288,6 +1352,96 @@ 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 ]; + + 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 + + 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, 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; +} + +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}; + + 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(); + + if ($csv->version >= 1.18) { + # get rid of stupid datev warnings in "Validity program" + $csv->quote_empty(1); + } + + 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} }; +} sub DESTROY { clean_temporary_directories(); } @@ -1485,6 +1639,35 @@ Example: # ] # }; + +=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. + +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 SL::DATEV::CSV.pm + +=item _csv_buchungsexport_to_file + +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); + + =back =head1 ATTRIBUTES @@ -1613,6 +1796,7 @@ OBE export is currently not implemented. =head1 SEE ALSO L +L =head1 AUTHORS