X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FDATEV.pm;h=86cb729bc6abf72719f92414ff4680b6a5223bfb;hb=f05bd96b031d7d4ffaf4804704684ae929a0890a;hp=406f09d76be5cc9ae04b8227be49104e8530782d;hpb=fc44ab06e84d2546c7eb9c7c5f0586db17f01242;p=kivitendo-erp.git diff --git a/SL/DATEV.pm b/SL/DATEV.pm index 406f09d76..86cb729bc 100644 --- a/SL/DATEV.pm +++ b/SL/DATEV.pm @@ -18,7 +18,8 @@ # GNU General Public License for more details. # You should have received a copy of the GNU General Public License # along with this program; if not, write to the Free Software -# Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA. +# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, +# MA 02110-1335, USA. #====================================================================== # # Datev export module @@ -30,14 +31,22 @@ use utf8; use strict; use SL::DBUtils; -use SL::DATEV::KNEFile; -use SL::Taxkeys; +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; use Exporter qw(import); use File::Path; -use List::Util qw(max sum); +use IO::File; +use List::MoreUtils qw(any); +use List::Util qw(min max sum); +use List::UtilsBy qw(partition_by sort_by); +use Text::CSV_XS; use Time::HiRes qw(gettimeofday); { @@ -45,13 +54,15 @@ use Time::HiRes qw(gettimeofday); use constant { DATEV_ET_BUCHUNGEN => $i++, DATEV_ET_STAMM => $i++, + DATEV_ET_CSV => $i++, DATEV_FORMAT_KNE => $i++, DATEV_FORMAT_OBE => $i++, + DATEV_FORMAT_CSV => $i++, }; } -my @export_constants = qw(DATEV_ET_BUCHUNGEN DATEV_ET_STAMM DATEV_FORMAT_KNE DATEV_FORMAT_OBE); +my @export_constants = qw(DATEV_ET_BUCHUNGEN DATEV_ET_STAMM DATEV_ET_CSV DATEV_FORMAT_KNE DATEV_FORMAT_OBE DATEV_FORMAT_CSV); our @EXPORT_OK = (@export_constants); our %EXPORT_TAGS = (CONSTANTS => [ @export_constants ]); @@ -193,6 +204,38 @@ sub to { return $self->{to}; } +sub trans_id { + my $self = shift; + + if (@_) { + $self->{trans_id} = $_[0]; + } + + die "illegal trans_id passed for DATEV export: " . $self->{trans_id} . "\n" unless $self->{trans_id} =~ m/^\d+$/; + + 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; @@ -222,7 +265,7 @@ sub dbh { $self->{provided_dbh} = 1; } - $self->{dbh} ||= $::form->get_standard_dbh; + $self->{dbh} ||= SL::DB->client->dbh; } sub provided_dbh { @@ -244,29 +287,6 @@ sub clean_temporary_directories { $::lxdebug->leave_sub; } -sub _fill { - $main::lxdebug->enter_sub(); - - my $text = shift; - my $field_len = shift; - my $fill_char = shift; - my $alignment = shift || 'right'; - - my $text_len = length $text; - - if ($field_len < $text_len) { - $text = substr $text, 0, $field_len; - - } elsif ($field_len > $text_len) { - my $filler = ($fill_char) x ($field_len - $text_len); - $text = $alignment eq 'right' ? $filler . $text : $text . $filler; - } - - $main::lxdebug->leave_sub(); - - return $text; -} - sub get_datev_stamm { return $_[0]{stamm} ||= selectfirst_hashref_query($::form, $_[0]->dbh, 'SELECT * FROM datev'); } @@ -274,43 +294,79 @@ sub get_datev_stamm { sub save_datev_stamm { my ($self, $data) = @_; - do_query($::form, $self->dbh, 'DELETE FROM datev'); - - my @columns = qw(beraternr beratername dfvkz mandantennr datentraegernr abrechnungsnr); + SL::DB->client->with_transaction(sub { + do_query($::form, $self->dbh, 'DELETE FROM datev'); - my $query = "INSERT INTO datev (" . join(', ', @columns) . ") VALUES (" . join(', ', ('?') x @columns) . ")"; - do_query($::form, $self->dbh, $query, map { $data->{$_} } @columns); + my @columns = qw(beraternr beratername dfvkz mandantennr datentraegernr abrechnungsnr); - $self->dbh->commit unless $self->provided_dbh; + my $query = "INSERT INTO datev (" . join(', ', @columns) . ") VALUES (" . join(', ', ('?') x @columns) . ")"; + do_query($::form, $self->dbh, $query, map { $data->{$_} } @columns); + 1; + }) or do { die SL::DB->client->error }; } sub export { my ($self) = @_; - my $result; - - die 'no format set!' unless $self->has_format; - if ($self->format == DATEV_FORMAT_KNE) { - $result = $self->kne_export; - } elsif ($self->format == DATEV_FORMAT_OBE) { - $result = $self->obe_export; - } else { - die 'unrecognized export format'; - } - - return $result; + return $self->csv_export; } -sub kne_export { +sub csv_export { my ($self) = @_; my $result; die 'no exporttype set!' unless $self->has_exporttype; if ($self->exporttype == DATEV_ET_BUCHUNGEN) { - $result = $self->kne_buchungsexport; - } elsif ($self->exporttype == DATEV_ET_STAMM) { - $result = $self->kne_stammdatenexport; + + $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 }; + } else { die 'unrecognized exporttype'; } @@ -318,10 +374,6 @@ sub kne_export { return $result; } -sub obe_export { - die 'not yet implemented'; -} - sub fromto { my ($self) = @_; @@ -334,73 +386,191 @@ sub _sign { $_[0] <=> 0; } -sub _get_transactions { +sub locked { + my $self = shift; + + if (@_) { + $self->{locked} = $_[0]; + } + return $self->{locked}; +} +sub imported { + my $self = shift; + + if (@_) { + $self->{imported} = $_[0]; + } + return $self->{imported}; +} + +sub generate_datev_data { $main::lxdebug->enter_sub(); - my $self = shift; - my $fromto = shift; - my $progress_callback = shift || sub {}; + + my ($self, %params) = @_; + my $fromto = $params{from_to} // ''; + my $progress_callback = $params{progress_callback} || sub {}; my $form = $main::form; - my ($notsplitindex); + my $trans_id_filter = ''; + my $ar_department_id_filter = ''; + my $ap_department_id_filter = ''; + my $gl_department_id_filter = ''; + if ( $form->{department_id} ) { + $ar_department_id_filter = " AND ar.department_id = ? "; + $ap_department_id_filter = " AND ap.department_id = ? "; + $gl_department_id_filter = " AND gl.department_id = ? "; + } - $fromto =~ s/transdate/ac\.transdate/g; + 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 + # (the transdates will likely differ) + $fromto = ''; + $trans_id_filter = 'ac.trans_id = ' . $self->trans_id; + } else { + $fromto =~ s/transdate/ac\.transdate/g; + }; + + my ($notsplitindex); - my $taxkeys = Taxkeys->new(); my $filter = ''; # Useful for debugging purposes 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 $gl_imported; + if ( !$self->imported ) { + $gl_imported = " AND NOT imported"; + } + my $query = - qq|SELECT ac.acc_trans_id, ac.transdate, ac.trans_id,ar.id, ac.amount, ac.taxkey, - ar.invnumber, ar.duedate, ar.amount as umsatz, ar.deliverydate, - ct.name, - c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link, - ar.invoice + 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, COALESCE(ar.tax_point, ar.deliverydate) AS deliverydate, ar.itime::date, + ct.name, ct.ustid, ct.customernumber AS vcnumber, ct.id AS customer_id, NULL AS vendor_id, + $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, + tc.accno AS tax_accno, tc.description AS tax_accname, + ar.department_id, + 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 UNION ALL - SELECT ac.acc_trans_id, ac.transdate, ac.trans_id,ap.id, ac.amount, ac.taxkey, - ap.invnumber, ap.duedate, ap.amount as umsatz, ap.deliverydate, - ct.name, - c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link, - ap.invoice + 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, COALESCE(ap.tax_point, ap.deliverydate) AS deliverydate, ap.itime::date, + ct.name, ct.ustid, ct.vendornumber AS vcnumber, NULL AS customer_id, ct.id AS vendor_id, + $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, + tc.accno AS tax_accno, tc.description AS tax_accname, + ap.department_id, + 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 UNION ALL - SELECT ac.acc_trans_id, ac.transdate, ac.trans_id,gl.id, ac.amount, ac.taxkey, - gl.reference AS invnumber, gl.transdate AS duedate, ac.amount as umsatz, NULL as deliverydate, - gl.description AS name, - c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link, - FALSE AS invoice + SELECT ac.acc_trans_id, ac.transdate, ac.gldate, ac.trans_id,gl.id, ac.amount, ac.taxkey, ac.memo, + gl.reference AS invnumber, NULL AS duedate, ac.amount as umsatz, COALESCE(gl.tax_point, gl.deliverydate) AS 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, + t.rate AS taxrate, t.taxdescription, + 'gl' as table, + tc.accno AS tax_accno, tc.description AS tax_accname, + gl.department_id, + 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 + $gl_imported + AND NOT EXISTS (SELECT gl_id from ap_gl where gl_id = gl.id) $filter ORDER BY trans_id, acc_trans_id|; - my $sth = prepare_execute_query($form, $self->dbh, $query); + my @query_args; + 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); $self->{DATEV} = []; my $counter = 0; - while (my $ref = $sth->fetchrow_hashref("NAME_lc")) { + my $continue = 1; # + my $name; + while ( $continue && (my $ref = $sth->fetchrow_hashref("NAME_lc")) ) { + last unless $ref; # for single transactions $counter++; if (($counter % 500) == 0) { $progress_callback->($counter); @@ -424,13 +594,24 @@ sub _get_transactions { # keep fetching new acc_trans lines until the end of a balanced group is reached while (abs($count) > 0.01 || $firstrun || ($subcent && abs($count) > 0.005)) { my $ref2 = $sth->fetchrow_hashref("NAME_lc"); - last unless ($ref2); + unless ( $ref2 ) { + $continue = 0; + last; + }; # check if trans_id of current acc_trans line is still the same as the - # trans_id of the first line in group + # trans_id of the first line in group, i.e. we haven't finished a 0-group + # before moving on to the next trans_id, error will likely be in the old + # trans_id. if ($ref2->{trans_id} != $trans->[0]->{trans_id}) { - $self->add_error("Unbalanced ledger! old trans_id " . $trans->[0]->{trans_id} . " new trans_id " . $ref2->{trans_id} . " count $count"); + require SL::DB::Manager::AccTransaction; + if ( $trans->[0]->{trans_id} ) { + my $acc_trans_obj = SL::DB::Manager::AccTransaction->get_first(where => [ trans_id => $trans->[0]->{trans_id} ]); + $self->add_error(t8("Export error in transaction #1: Unbalanced ledger before next transaction (#2)", + $acc_trans_obj->transaction_name, $ref2->{trans_id}) + ); + }; return; } @@ -458,7 +639,6 @@ sub _get_transactions { } } - my %taxid_taxkeys = (); my $absumsatz = 0; if (scalar(@{$trans}) <= 2) { push @{ $self->{DATEV} }, $trans; @@ -494,7 +674,9 @@ sub _get_transactions { # Problem: we can't distinguish between AR and AP and normal invoices via boolean "invoice" # for AR and AP transaction exit the loop as soon as an AR or AP account is found # there must be only one AR or AP chart in the booking - if ( $trans->[$j]->{'link'} eq 'AR' or $trans->[$j]->{'link'} eq 'AP') { + # since it is possible to do this kind of things with GL too, make sure those don't get aborted in case someone + # manually pays an invoice in GL. + if ($trans->[$j]->{table} ne 'gl' and ($trans->[$j]->{'link'} eq 'AR' or $trans->[$j]->{'link'} eq 'AP')) { $notsplitindex = $j; # position in booking with highest amount $absumsatz = $trans->[$j]->{'amount'}; last; @@ -528,13 +710,11 @@ sub _get_transactions { push @{ $self->{DATEV} }, [ \%new_trans, $trans->[$j] ]; } elsif (($j != $notsplitindex) && !$trans->[$j]->{is_tax}) { - my %tax_info = $taxkeys->get_full_tax_info('transdate' => $trans->[$j]->{transdate}, - 'deliverydate' => $trans->[$j]->{deliverydate}); my %new_trans = (); map { $new_trans{$_} = $trans->[$notsplitindex]->{$_}; } keys %{ $trans->[$notsplitindex] }; - my $tax_rate = $tax_info{taxkeys}->{ $trans->[$j]->{'taxkey'} }->{taxrate}; + my $tax_rate = $trans->[$j]->{'taxrate'}; $new_trans{'net_amount'} = $trans->[$j]->{'amount'} * -1; $new_trans{'tax_rate'} = 1 + $tax_rate; @@ -600,8 +780,11 @@ sub _get_transactions { $absumsatz = $form->round_amount($absumsatz, 2); if (abs($absumsatz) >= (0.01 * (1 + scalar @taxed))) { - $self->add_error("Datev-Export fehlgeschlagen! Bei Transaktion $trans->[0]->{trans_id} ($absumsatz)"); - + require SL::DB::Manager::AccTransaction; + my $acc_trans_obj = SL::DB::Manager::AccTransaction->get_first(where => [ trans_id => $trans->[0]->{trans_id} ]); + $self->add_error(t8("Export error in transaction #1: Rounding error too large #2", + $acc_trans_obj->transaction_name, $absumsatz) + ); } elsif (abs($absumsatz) >= 0.01) { $self->add_net_gross_differences($absumsatz); } @@ -612,418 +795,165 @@ sub _get_transactions { $::lxdebug->leave_sub; } -sub make_kne_data_header { - $main::lxdebug->enter_sub(); - - my ($self, $form) = @_; - my ($primanota); - - my $stamm = $self->get_datev_stamm; - - my $jahr = $self->from ? $self->from->year : DateTime->today->year; - - #Header - my $header = "\x1D\x181"; - $header .= _fill($stamm->{datentraegernr}, 3, ' ', 'left'); - $header .= ($self->fromto) ? "11" : "13"; # Anwendungsnummer - $header .= _fill($stamm->{dfvkz}, 2, '0'); - $header .= _fill($stamm->{beraternr}, 7, '0'); - $header .= _fill($stamm->{mandantennr}, 5, '0'); - $header .= _fill($stamm->{abrechnungsnr} . $jahr, 6, '0'); - - $header .= $self->from ? $self->from->strftime('%d%m%y') : ''; - $header .= $self->to ? $self->to->strftime('%d%m%y') : ''; - - if ($self->fromto) { - $primanota = "001"; - $header .= $primanota; - } - - $header .= _fill($stamm->{passwort}, 4, '0'); - $header .= " " x 16; # Anwendungsinfo - $header .= " " x 16; # Inputinfo - $header .= "\x79"; - - #Versionssatz - my $versionssatz = $self->exporttype == DATEV_ET_BUCHUNGEN ? "\xB5" . "1," : "\xB6" . "1,"; - - my $query = qq|SELECT accno FROM chart LIMIT 1|; - my $ref = selectfirst_hashref_query($form, $self->dbh, $query); - - $versionssatz .= length $ref->{accno}; - $versionssatz .= ","; - $versionssatz .= length $ref->{accno}; - $versionssatz .= ",SELF" . "\x1C\x79"; - - $header .= $versionssatz; - - $main::lxdebug->leave_sub(); - - return $header; -} - -sub datetofour { - $main::lxdebug->enter_sub(); - - my ($date, $six) = @_; - - my ($day, $month, $year) = split(/\./, $date); - - if ($day =~ /^0/) { - $day = substr($day, 1, 1); - } - if (length($month) < 2) { - $month = "0" . $month; - } - if (length($year) > 2) { - $year = substr($year, -2, 2); - } - - if ($six) { - $date = $day . $month . $year; - } else { - $date = $day . $month; - } - - $main::lxdebug->leave_sub(); - - return $date; -} - -sub trim_leading_zeroes { - my $str = shift; - - $str =~ s/^0+//g; - - return $str; -} - -sub make_ed_versionset { - $main::lxdebug->enter_sub(); - - my ($self, $header, $filename, $blockcount) = @_; - - my $versionset = "V" . substr($filename, 2, 5); - $versionset .= substr($header, 6, 22); - - if ($self->fromto) { - $versionset .= "0000" . substr($header, 28, 19); - } else { - my $datum = " " x 16; - $versionset .= $datum . "001" . substr($header, 28, 4); - } - - $versionset .= _fill($blockcount, 5, '0'); - $versionset .= "001"; - $versionset .= " 1"; - $versionset .= substr($header, -12, 10) . " "; - $versionset .= " " x 53; - - $main::lxdebug->leave_sub(); - - return $versionset; -} - -sub make_ev_header { - $main::lxdebug->enter_sub(); - - my ($self, $form, $fileno) = @_; - - my $stamm = $self->get_datev_stamm; - - my $ev_header = _fill($stamm->{datentraegernr}, 3, ' ', 'left'); - $ev_header .= " "; - $ev_header .= _fill($stamm->{beraternr}, 7, ' ', 'left'); - $ev_header .= _fill($stamm->{beratername}, 9, ' ', 'left'); - $ev_header .= " "; - $ev_header .= (_fill($fileno, 5, '0')) x 2; - $ev_header .= " " x 95; - - $main::lxdebug->leave_sub(); - - return $ev_header; -} - -sub kne_buchungsexport { - $main::lxdebug->enter_sub(); - +sub generate_datev_lines { my ($self) = @_; - my $form = $::form; - - my @filenames; - - my $filename = "ED00000"; - my $evfile = "EV01"; - my @ed_versionset; - my $fileno = 0; - - my $fromto = $self->fromto; - - $self->_get_transactions($fromto); - - return if $self->errors; - - my $counter = 0; - - while (scalar(@{ $self->{DATEV} || [] })) { - my $umsatzsumme = 0; - $filename++; - my $ed_filename = $self->export_path . $filename; - push(@filenames, $filename); - my $header = $self->make_kne_data_header($form); - - my $kne_file = SL::DATEV::KNEFile->new(); - $kne_file->add_block($header); - - while (scalar(@{ $self->{DATEV} }) > 0) { - my $transaction = shift @{ $self->{DATEV} }; - my $trans_lines = scalar(@{$transaction}); - $counter++; - - my $umsatz = 0; - my $gegenkonto = ""; - my $konto = ""; - my $belegfeld1 = ""; - my $datum = ""; - my $waehrung = ""; - my $buchungstext = ""; - my $belegfeld2 = ""; - my $datevautomatik = 0; - my $taxkey = 0; - my $charttax = 0; - my ($haben, $soll); - my $iconv = $::locale->{iconv_utf8}; - my %umlaute = ($iconv->convert('ä') => 'ae', - $iconv->convert('ö') => 'oe', - $iconv->convert('ü') => 'ue', - $iconv->convert('Ä') => 'Ae', - $iconv->convert('Ö') => 'Oe', - $iconv->convert('Ü') => 'Ue', - $iconv->convert('ß') => 'sz'); - for (my $i = 0; $i < $trans_lines; $i++) { - if ($trans_lines == 2) { - if (abs($transaction->[$i]->{'amount'}) > abs($umsatz)) { - $umsatz = $transaction->[$i]->{'amount'}; - } - } else { - if (abs($transaction->[$i]->{'umsatz'}) > abs($umsatz)) { - $umsatz = $transaction->[$i]->{'umsatz'}; - } - } - if ($transaction->[$i]->{'datevautomatik'}) { - $datevautomatik = 1; - } - if ($transaction->[$i]->{'taxkey'}) { - $taxkey = $transaction->[$i]->{'taxkey'}; - } - if ($transaction->[$i]->{'charttax'}) { - $charttax = $transaction->[$i]->{'charttax'}; + my @datev_lines = (); + + foreach my $transaction ( @{ $self->{DATEV} } ) { + + # each $transaction entry contains data from several acc_trans entries + # belonging to the same trans_id + + my %datev_data = (); # data for one transaction + my $trans_lines = scalar(@{$transaction}); + + my $umsatz = 0; + my $gegenkonto = ""; + my $konto = ""; + my $belegfeld1 = ""; + my $datum = ""; + my $waehrung = ""; + my $buchungstext = ""; + my $belegfeld2 = ""; + my $datevautomatik = 0; + my $taxkey = 0; + my $charttax = 0; + my $ustid =""; + my ($haben, $soll); + for (my $i = 0; $i < $trans_lines; $i++) { + if ($trans_lines == 2) { + if (abs($transaction->[$i]->{'amount'}) > abs($umsatz)) { + $umsatz = $transaction->[$i]->{'amount'}; } - if ($transaction->[$i]->{'amount'} > 0) { - $haben = $i; - } else { - $soll = $i; + } else { + if (abs($transaction->[$i]->{'umsatz'}) > abs($umsatz)) { + $umsatz = $transaction->[$i]->{'umsatz'}; } } - - # Umwandlung von Umlauten und Sonderzeichen in erlaubte Zeichen bei Textfeldern - foreach my $umlaut (keys(%umlaute)) { - $transaction->[$haben]->{'invnumber'} =~ s/${umlaut}/${umlaute{$umlaut}}/g; - $transaction->[$haben]->{'name'} =~ s/${umlaut}/${umlaute{$umlaut}}/g; + if ($transaction->[$i]->{'datevautomatik'}) { + $datevautomatik = 1; } - - $transaction->[$haben]->{'invnumber'} =~ s/[^0-9A-Za-z\$\%\&\*\+\-\/]//g; - $transaction->[$haben]->{'name'} =~ s/[^0-9A-Za-z\$\%\&\*\+\-\ \/]//g; - - $transaction->[$haben]->{'invnumber'} = substr($transaction->[$haben]->{'invnumber'}, 0, 12); - $transaction->[$haben]->{'name'} = substr($transaction->[$haben]->{'name'}, 0, 30); - $transaction->[$haben]->{'invnumber'} =~ s/\ *$//; - $transaction->[$haben]->{'name'} =~ s/\ *$//; - - if ($trans_lines >= 2) { - - $gegenkonto = "a" . trim_leading_zeroes($transaction->[$haben]->{'accno'}); - $konto = "e" . trim_leading_zeroes($transaction->[$soll]->{'accno'}); - if ($transaction->[$haben]->{'invnumber'} ne "") { - $belegfeld1 = "\xBD" . $transaction->[$haben]->{'invnumber'} . "\x1C"; - } - $datum = "d"; - $datum .= &datetofour($transaction->[$haben]->{'transdate'}, 0); - $waehrung = "\xB3" . "EUR" . "\x1C"; - if ($transaction->[$haben]->{'name'} ne "") { - $buchungstext = "\x1E" . $transaction->[$haben]->{'name'} . "\x1C"; - } - if ($transaction->[$haben]->{'duedate'} ne "") { - $belegfeld2 = "\xBE" . &datetofour($transaction->[$haben]->{'duedate'}, 1) . "\x1C"; - } + if ($transaction->[$i]->{'taxkey'}) { + $taxkey = $transaction->[$i]->{'taxkey'}; + # $taxkey = 0 if $taxkey == 94; # taxbookings are in gl } - - $umsatz = $kne_file->format_amount(abs($umsatz), 0); - $umsatzsumme += $umsatz; - $kne_file->add_block("+" . $umsatz); - - # Dies ist die einzige Stelle die datevautomatik auswertet. Was soll gesagt werden? - # Im Prinzip hat jeder acc_trans Eintrag einen Steuerschlüssel, außer, bei gewissen Fällen - # wie: Kreditorenbuchung mit negativen Vorzeichen, SEPA-Export oder Rechnungen die per - # Skript angelegt werden. - # Also falls ein Steuerschlüssel da ist und NICHT datevautomatik diesen Block hinzufügen. - # Oder aber datevautomatik ist WAHR, aber der Steuerschlüssel in der acc_trans weicht - # von dem in der Chart ab: Also wahrscheinlich Programmfehler (NULL übergeben, statt - # DATEV-Steuerschlüssel) oder der Steuerschlüssel des Kontos weicht WIRKLICH von dem Eintrag in der - # acc_trans ab. Gibt es für diesen Fall eine plausiblen Grund? - # - if ( ( $datevautomatik || $taxkey) - && (!$datevautomatik || ($datevautomatik && ($charttax ne $taxkey)))) { -# $kne_file->add_block("\x6C" . (!$datevautomatik ? $taxkey : "4")); - $kne_file->add_block("\x6C${taxkey}"); + if ($transaction->[$i]->{'charttax'}) { + $charttax = $transaction->[$i]->{'charttax'}; + } + if ($transaction->[$i]->{'amount'} > 0) { + $haben = $i; + } else { + $soll = $i; } - - $kne_file->add_block($gegenkonto); - $kne_file->add_block($belegfeld1); - $kne_file->add_block($belegfeld2); - $kne_file->add_block($datum); - $kne_file->add_block($konto); - $kne_file->add_block($buchungstext); - $kne_file->add_block($waehrung . "\x79"); } - my $mandantenendsumme = "x" . $kne_file->format_amount($umsatzsumme / 100.0, 14) . "\x79\x7a"; + if ($trans_lines >= 2) { - $kne_file->add_block($mandantenendsumme); - $kne_file->flush(); - - open(ED, ">", $ed_filename) or die "can't open outputfile: $!\n"; - print(ED $kne_file->get_data()); - close(ED); + # 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{belegfeld1} = $transaction->[$haben]->{'invnumber'}; + } + $datev_data{datum} = $transaction->[$haben]->{'transdate'}; + $datev_data{waehrung} = 'EUR'; + $datev_data{kost1} = $transaction->[$haben]->{'departmentdescription'}; + $datev_data{kost2} = $transaction->[$haben]->{'projectdescription'}; - $ed_versionset[$fileno] = $self->make_ed_versionset($header, $filename, $kne_file->get_block_count()); - $fileno++; - } + if ($transaction->[$haben]->{'name'} ne "") { + $datev_data{buchungstext} = $transaction->[$haben]->{'name'}; + } + if (($transaction->[$haben]->{'ustid'} // '') ne "") { + $datev_data{ustid} = SL::VATIDNr->normalize($transaction->[$haben]->{'ustid'}); + } + if (($transaction->[$haben]->{'duedate'} // '') ne "") { + $datev_data{belegfeld2} = $transaction->[$haben]->{'duedate'}; + } - #Make EV Verwaltungsdatei - my $ev_header = $self->make_ev_header($form, $fileno); - my $ev_filename = $self->export_path . $evfile; - push(@filenames, $evfile); - open(EV, ">", $ev_filename) or die "can't open outputfile: EV01\n"; - print(EV $ev_header); + # 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{umsatz} = abs($umsatz); # sales invoices without tax have a different sign??? + + # Dies ist die einzige Stelle die datevautomatik auswertet. Was soll gesagt werden? + # Im Prinzip hat jeder acc_trans Eintrag einen Steuerschlüssel, außer, bei gewissen Fällen + # wie: Kreditorenbuchung mit negativen Vorzeichen, SEPA-Export oder Rechnungen die per + # Skript angelegt werden. + # Also falls ein Steuerschlüssel da ist und NICHT datevautomatik diesen Block hinzufügen. + # Oder aber datevautomatik ist WAHR, aber der Steuerschlüssel in der acc_trans weicht + # von dem in der Chart ab: Also wahrscheinlich Programmfehler (NULL übergeben, statt + # DATEV-Steuerschlüssel) oder der Steuerschlüssel des Kontos weicht WIRKLICH von dem Eintrag in der + # acc_trans ab. Gibt es für diesen Fall eine plausiblen Grund? + # + + # only set buchungsschluessel if the following conditions are met: + if ( ( $datevautomatik || $taxkey) + && (!$datevautomatik || ($datevautomatik && ($charttax ne $taxkey)))) { + # $datev_data{buchungsschluessel} = !$datevautomatik ? $taxkey : "4"; + $datev_data{buchungsschluessel} = $taxkey; + } + # set lock for each transaction + $datev_data{locked} = $self->locked; - foreach my $file (@ed_versionset) { - print(EV $ed_versionset[$file]); + push(@datev_lines, \%datev_data) if $datev_data{umsatz}; } - close(EV); - ### - - $self->add_filenames(@filenames); - $main::lxdebug->leave_sub(); + # example of modifying export data: + # foreach my $datev_line ( @datev_lines ) { + # if ( $datev_line{"konto"} eq '1234' ) { + # $datev_line{"konto"} = '9999'; + # } + # } + # - return { 'download_token' => $self->download_token, 'filenames' => \@filenames }; + return \@datev_lines; } -sub kne_stammdatenexport { - $main::lxdebug->enter_sub(); - +sub check_vcnumbers_are_valid_pk_numbers { my ($self) = @_; - my $form = $::form; - - $self->get_datev_stamm->{abrechnungsnr} = "99"; - - my @filenames; - - my $filename = "ED00000"; - my $evfile = "EV01"; - my @ed_versionset; - my $fileno = 1; - my $i = 0; - my $blockcount = 1; - my $remaining_bytes = 256; - my $total_bytes = 256; - my $buchungssatz = ""; - $filename++; - my $ed_filename = $self->export_path . $filename; - push(@filenames, $filename); - open(ED, ">", $ed_filename) or die "can't open outputfile: $!\n"; - my $header = $self->make_kne_data_header($form); - $remaining_bytes -= length($header); - - my $fuellzeichen; - - my (@where, @values) = ((), ()); - if ($self->accnofrom) { - push @where, 'c.accno >= ?'; - push @values, $self->accnofrom; - } - if ($self->accnoto) { - push @where, 'c.accno <= ?'; - push @values, $self->accnoto; - } - my $where_str = @where ? ' WHERE ' . join(' AND ', map { "($_)" } @where) : ''; + # 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; +} - my $query = qq|SELECT c.accno, c.description - FROM chart c - $where_str - ORDER BY c.accno|; - my $sth = $self->dbh->prepare($query); - $sth->execute(@values) || $form->dberror($query); +sub check_valid_length_of_accounts { + my ($self) = @_; - while (my $ref = $sth->fetchrow_hashref("NAME_lc")) { - if (($remaining_bytes - length("t" . $ref->{'accno'})) <= 6) { - $fuellzeichen = ($blockcount * 256 - length($buchungssatz . $header)); - $buchungssatz .= "\x00" x $fuellzeichen; - $blockcount++; - $total_bytes = ($blockcount) * 256; - } - $buchungssatz .= "t" . $ref->{'accno'}; - $remaining_bytes = $total_bytes - length($buchungssatz . $header); - $ref->{'description'} =~ s/[^0-9A-Za-z\$\%\&\*\+\-\/]//g; - $ref->{'description'} = substr($ref->{'description'}, 0, 40); - $ref->{'description'} =~ s/\ *$//; - - if ( - ($remaining_bytes - length("\x1E" . $ref->{'description'} . "\x1C\x79") - ) <= 6 - ) { - $fuellzeichen = ($blockcount * 256 - length($buchungssatz . $header)); - $buchungssatz .= "\x00" x $fuellzeichen; - $blockcount++; - $total_bytes = ($blockcount) * 256; - } - $buchungssatz .= "\x1E" . $ref->{'description'} . "\x1C\x79"; - $remaining_bytes = $total_bytes - length($buchungssatz . $header); - } + my $query = <<"SQL"; + SELECT DISTINCT char_length (accno) FROM chart WHERE charttype='A' AND id in (select chart_id from acc_trans); +SQL - $sth->finish; - print(ED $header); - print(ED $buchungssatz); - $fuellzeichen = 256 - (length($header . $buchungssatz . "z") % 256); - my $dateiende = "\x00" x $fuellzeichen; - print(ED "z"); - print(ED $dateiende); - close(ED); - - #Make EV Verwaltungsdatei - $ed_versionset[0] = - $self->make_ed_versionset($header, $filename, $blockcount); - - my $ev_header = $self->make_ev_header($form, $fileno); - my $ev_filename = $self->export_path . $evfile; - push(@filenames, $evfile); - open(EV, ">", $ev_filename) or die "can't open outputfile: EV01\n"; - print(EV $ev_header); - - foreach my $file (@ed_versionset) { - print(EV $ed_versionset[$file]); + 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})); } - close(EV); - - $self->add_filenames(@filenames); - - $main::lxdebug->leave_sub(); - - return { 'download_token' => $self->download_token, 'filenames' => \@filenames }; + return 1; } sub DESTROY { @@ -1044,6 +974,8 @@ SL::DATEV - kivitendo DATEV Export module use SL::DATEV qw(:CONSTANTS); + 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_KNE, @@ -1051,6 +983,14 @@ SL::DATEV - kivitendo DATEV Export module to => $enddate, ); + # To only export transactions from a specific trans_id: (from and to are ignored) + my $invoice = SL::DB::Manager::Invoice->find_by( invnumber => '216' ); + my $datev = SL::DATEV->new( + exporttype => DATEV_ET_BUCHUNGEN, + format => DATEV_FORMAT_KNE, + trans_id => $invoice->trans_id, + ); + my $datev = SL::DATEV->new( exporttype => DATEV_ET_STAMM, format => DATEV_FORMAT_KNE, @@ -1062,7 +1002,7 @@ SL::DATEV - kivitendo DATEV Export module my $hashref = $datev->get_datev_stamm; $datev->save_datev_stamm($hashref); - # manually clean up temporary directories + # manually clean up temporary directories older than 8 hours $datev->clean_temporary_directories; # export @@ -1085,6 +1025,16 @@ SL::DATEV - kivitendo DATEV Export module my $path = $datev->export_path; my @files = glob("$path/*"); + # Only test the datev data of a specific trans_id, without generating an + # export file, but filling $datev->errors if errors exist + + my $datev = SL::DATEV->new( + trans_id => $invoice->trans_id, + ); + $datev->generate_datev_data; + # if ($datev->errors) { ... + + =head1 DESCRIPTION This module implements the DATEV export standard. For usage see above. @@ -1095,7 +1045,31 @@ This module implements the DATEV export standard. For usage see above. =item new PARAMS -Generic constructor. See section attributes for information about hat to pass. +Generic constructor. See section attributes for information about what to pass. + +=item generate_datev_data + +Fetches all transactions from the database (via a trans_id or a date range), +and does an initial transformation (e.g. filters out tax, determines +the brutto amount, checks split transactions ...) and stores this data in +$self->{DATEV}. + +If any errors are found these are collected in $self->errors. + +This function is needed for all the exports, but can be also called +independently in order to check transactions for DATEV compatibility. + +=item generate_datev_lines + +Parse the data in $self->{DATEV} and transform it into a format that can be +used by DATEV, e.g. determines Konto and Gegenkonto, the taxkey, ... + +The transformed data is returned as an arrayref, which is ready to be converted +to a DATEV data format, e.g. KNE, OBE, CSV, ... + +At this stage the "DATEV rule" has already been applied to the taxkeys, i.e. +entries with datevautomatik have an empty taxkey, as the taxkey is already +determined by the chart. =item get_datev_stamm @@ -1137,7 +1111,7 @@ Note: If either a download_token or export_path were set at the creation these a =item filenames -Returns a list of filenames generated by this DATEV object. This only works if th files were generated during it's lifetime, not if the object was created from a download_token. +Returns a list of filenames generated by this DATEV object. This only works if the files were generated during its lifetime, not if the object was created from a download_token. =item net_gross_differences @@ -1153,13 +1127,54 @@ 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 Exports data. You have to have set L and L or an error will occur. OBE exports are currently not implemented. +=item csv_export_for_tax_accountant + +Generates up to four downloadable csv files containing data about sales and +purchase invoices, and their respective payments: + +Example: + my $startdate = DateTime->new(year => 2012, month => 1, day => 1); + my $enddate = DateTime->new(year => 2012, month => 12, day => 31); + SL::DATEV->new(from => $startdate, to => $enddate)->csv_export_for_tax_accountant; + # { + # 'download_token' => '1488551625-815654-22430', + # 'filenames' => [ + # 'Zahlungen Kreditorenbuchungen 2012-01-01 - 2012-12-31.csv', + # 'Kreditorenbuchungen 2012-01-01 - 2012-12-31.csv', + # 'Zahlungen Debitorenbuchungen 2012-01-01 - 2012-12-31.csv', + # 'Debitorenbuchungen 2012-01-01 - 2012-12-31.csv' + # ] + # }; + + +=item check_vcnumbers_are_valid_pk_numbers + +Returns 1 if all vcnumbers are suitable for the DATEV export, 0 if not. + +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". + +All vcnumbers are checked, obsolete customers or vendors aren't exempt. + +There is also no check for the typical customer range 10000-69999 and the +typical vendor range 70000-99999. + +=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 =head1 ATTRIBUTES @@ -1173,6 +1188,10 @@ This is a list of attributes set in either the C or a method of the same na Set a database handle to use in the process. This allows for an export to be done on a transaction in progress without committing first. +Note: If you don't want this code to commit, simply providing a dbh is not +enough enymore. You'll have to wrap the call into a transaction yourself, so +that the internal transaction does not commit. + =item exporttype See L for possible values. This MUST be set before export is called. @@ -1189,7 +1208,14 @@ Can be set on creation to retrieve a prior export for download. =item to -Set boundary dates for the export. Currently thse MUST be set for the export to work. +Set boundary dates for the export. Unless a trans_id is passed these MUST be +set for the export to work. + +=item trans_id + +To check only one gl/ar/ap transaction, pass the trans_id. The attributes +L and L are currently still needed for the query to be assembled +correctly. =item accnofrom @@ -1197,6 +1223,11 @@ Set boundary dates for the export. Currently thse MUST be set for the export to 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 @@ -1233,7 +1264,7 @@ No or unrecognized exporttype or format was provided for an export =item * -OBE rxport was called, which is not yet implemented. +OBE export was called, which is not yet implemented. =item * @@ -1252,7 +1283,7 @@ C. Exactly that, your ledger is unbalanced. Should never occ =item * C This error occurs if a -transaction could not be reliably sorted out, or had rounding errors over the acceptable threshold. +transaction could not be reliably sorted out, or had rounding errors above the acceptable threshold. =back @@ -1262,7 +1293,7 @@ transaction could not be reliably sorted out, or had rounding errors over the ac =item * -Handling of Vollvorlauf is currently not fully implemented. You must provide both from and to to get a working export. +Handling of Vollvorlauf is currently not fully implemented. You must provide both from and to in order to get a working export. =item * @@ -1277,6 +1308,7 @@ OBE export is currently not implemented. =head1 SEE ALSO L +L =head1 AUTHORS