X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FDATEV.pm;h=bf96fa4471a52c3eb912245658372f35dcec3296;hb=74bb985acc52a4244cad68df32cb1c8a735c64da;hp=8756c729535cce56c9ea435e0051cad123102a0b;hpb=3c147670068cc4731d0c86c81f8f1bf186da796c;p=kivitendo-erp.git diff --git a/SL/DATEV.pm b/SL/DATEV.pm index 8756c7295..bf96fa447 100644 --- a/SL/DATEV.pm +++ b/SL/DATEV.pm @@ -56,10 +56,11 @@ use Time::HiRes qw(gettimeofday); DATEV_FORMAT_KNE => $i++, DATEV_FORMAT_OBE => $i++, + DATEV_FORMAT_CSV => $i++, }; } -my @export_constants = qw(DATEV_ET_BUCHUNGEN DATEV_ET_STAMM DATEV_ET_CSV 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 ]); @@ -311,7 +312,9 @@ sub export { die 'no format set!' unless $self->has_format; - if ($self->format == DATEV_FORMAT_KNE) { + if ($self->format == DATEV_FORMAT_CSV) { + $result = $self->csv_export; + } elsif ($self->format == DATEV_FORMAT_KNE) { $result = $self->kne_export; } elsif ($self->format == DATEV_FORMAT_OBE) { $result = $self->obe_export; @@ -341,6 +344,10 @@ sub kne_export { return $result; } +sub csv_export { + die 'not yet implemented'; +} + sub obe_export { die 'not yet implemented'; } @@ -357,16 +364,24 @@ sub _sign { $_[0] <=> 0; } -sub _get_transactions { +sub generate_datev_data { $main::lxdebug->enter_sub(); my ($self, %params) = @_; - my $fromto = $params{from_to}; + my $fromto = $params{from_to} // ''; my $progress_callback = $params{progress_callback} || sub {}; my $form = $main::form; 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 = ? "; + } if ( $self->{trans_id} ) { # ignore dates when trans_id is passed so that the entire transaction is @@ -393,6 +408,7 @@ sub _get_transactions { t.rate AS taxrate, t.taxdescription, 'ar' as table, tc.accno AS tax_accno, tc.description AS tax_accname, + ar.department_id, ar.notes FROM acc_trans ac LEFT JOIN ar ON (ac.trans_id = ar.id) @@ -403,6 +419,7 @@ sub _get_transactions { WHERE (ar.id IS NOT NULL) AND $fromto $trans_id_filter + $ar_department_id_filter $filter UNION ALL @@ -415,6 +432,7 @@ sub _get_transactions { t.rate AS taxrate, t.taxdescription, 'ap' as table, tc.accno AS tax_accno, tc.description AS tax_accname, + ap.department_id, ap.notes FROM acc_trans ac LEFT JOIN ap ON (ac.trans_id = ap.id) @@ -425,6 +443,7 @@ sub _get_transactions { WHERE (ap.id IS NOT NULL) AND $fromto $trans_id_filter + $ap_department_id_filter $filter UNION ALL @@ -437,6 +456,7 @@ sub _get_transactions { t.rate AS taxrate, t.taxdescription, 'gl' as table, tc.accno AS tax_accno, tc.description AS tax_accname, + gl.department_id, gl.notes FROM acc_trans ac LEFT JOIN gl ON (ac.trans_id = gl.id) @@ -446,11 +466,17 @@ sub _get_transactions { WHERE (gl.id IS NOT NULL) AND $fromto $trans_id_filter + $gl_department_id_filter $filter ORDER BY trans_id, acc_trans_id|; - my $sth = prepare_execute_query($form, $self->dbh, $query); + my @query_args; + if ( $form->{department_id} ) { + push(@query_args, ($form->{department_id}) x 3); + } + + my $sth = prepare_execute_query($form, $self->dbh, $query, @query_args); $self->{DATEV} = []; my $counter = 0; @@ -814,201 +840,209 @@ sub make_ev_header { 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(from_to => $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); - - # transform $self->{DATEV} into an array of hashrefs containing all the - # necessary information for the actual DATEV export, storing it in @kne_lines. - my @kne_lines = (); - while (scalar(@{ $self->{DATEV} }) > 0) { - my %kne_data = (); - 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 $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'}; - } - } 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'}; } } + if ($transaction->[$i]->{'datevautomatik'}) { + $datevautomatik = 1; + } + if ($transaction->[$i]->{'taxkey'}) { + $taxkey = $transaction->[$i]->{'taxkey'}; + } + if ($transaction->[$i]->{'charttax'}) { + $charttax = $transaction->[$i]->{'charttax'}; + } + if ($transaction->[$i]->{'amount'} > 0) { + $haben = $i; + } else { + $soll = $i; + } + } - if ($trans_lines >= 2) { - - $kne_data{'gegenkonto'} = $transaction->[$haben]->{'accno'}; - $kne_data{'konto'} = $transaction->[$soll]->{'accno'}; - if ($transaction->[$haben]->{'invnumber'} ne "") { - $kne_data{belegfeld1} = $transaction->[$haben]->{'invnumber'}; - } - $kne_data{datum} = $transaction->[$haben]->{'transdate'}; - $kne_data{waehrung} = 'EUR'; + if ($trans_lines >= 2) { - if ($transaction->[$haben]->{'name'} ne "") { - $kne_data{buchungstext} = $transaction->[$haben]->{'name'}; - } - if (($transaction->[$haben]->{'ustid'} // '') ne "") { - $kne_data{ustid} = $transaction->[$haben]->{'ustid'}; - } - if (($transaction->[$haben]->{'duedate'} // '') ne "") { - $kne_data{belegfeld2} = $transaction->[$haben]->{'duedate'}; - } + $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'; - $kne_data{umsatz} = abs($umsatz); # sales invoices without tax have a different sign??? - $umsatzsumme += $kne_data{umsatz}; #umsatz; # add the abs amount - - # 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)))) { - # $kne_data{buchungsschluessel} = !$datevautomatik ? $taxkey : "4"; - $kne_data{buchungsschluessel} = $taxkey; + if ($transaction->[$haben]->{'name'} ne "") { + $datev_data{buchungstext} = $transaction->[$haben]->{'name'}; + } + if (($transaction->[$haben]->{'ustid'} // '') ne "") { + $datev_data{ustid} = $transaction->[$haben]->{'ustid'}; } + if (($transaction->[$haben]->{'duedate'} // '') ne "") { + $datev_data{belegfeld2} = $transaction->[$haben]->{'duedate'}; + } + } - push(@kne_lines, \%kne_data); + $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; } - # the data in @kne_lines is now ready to be transformed to a kne file, or even to csv + push(@datev_lines, \%datev_data); + } - 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'); + # example of modifying export data: + # foreach my $datev_line ( @datev_lines ) { + # if ( $datev_line{"konto"} eq '1234' ) { + # $datev_line{"konto"} = '9999'; + # } + # } + # - my $header = $self->make_kne_data_header($form); + return \@datev_lines; +} - my $kne_file = SL::DATEV::KNEFile->new(); - $kne_file->add_block($header); - # add the data from @kne_lines to the kne_file, formatting as needed - foreach my $kne (@kne_lines) { - $kne_file->add_block("+" . $kne_file->format_amount(abs($kne->{umsatz}), 0)); +sub kne_buchungsexport { + $main::lxdebug->enter_sub(); - # only add buchungsschluessel if it was previously defined - $kne_file->add_block("\x6C" . $kne->{buchungsschluessel}) if defined $kne->{buchungsschluessel}; + my ($self) = @_; - # ($kne->{gegenkonto}) = $kne->{gegenkonto} =~ /^(\d+)/; - $kne_file->add_block("a" . trim_leading_zeroes($kne->{gegenkonto})); + my $form = $::form; - if ( $kne->{belegfeld1} ) { - my $invnumber = $kne->{belegfeld1}; - foreach my $umlaut (keys(%umlaute)) { - $invnumber =~ s/${umlaut}/${umlaute{$umlaut}}/g; - } - $invnumber =~ s/[^0-9A-Za-z\$\%\&\*\+\-\/]//g; - $invnumber = substr($invnumber, 0, 12); - $invnumber =~ s/\ *$//; - $kne_file->add_block("\xBD" . $invnumber . "\x1C"); - } + my @filenames; + + my $filename = "ED00001"; + my $evfile = "EV01"; + my @ed_versionset; + my $fileno = 1; + my $ed_filename = $self->export_path . $filename; + + my $fromto = $self->fromto; + + $self->generate_datev_data(from_to => $self->fromto); # fetches data from db, transforms data and fills $self->{DATEV} + return if $self->errors; - $kne_file->add_block("\xBE" . &datetofour($kne->{belegfeld2},1) . "\x1C"); + my @datev_lines = @{ $self->generate_datev_lines }; - $kne_file->add_block("d" . &datetofour($kne->{datum},0)); - # ($kne->{konto}) = $kne->{konto} =~ /^(\d+)/; - $kne_file->add_block("e" . trim_leading_zeroes($kne->{konto})); + my $umsatzsumme = sum map { $_->{umsatz} } @datev_lines; - my $name = $kne->{buchungstext}; + # prepare kne file, everything gets stored in ED00001 + my $header = $self->make_kne_data_header($form); + my $kne_file = SL::DATEV::KNEFile->new(); + $kne_file->add_block($header); + + 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'); + + # add the data from @datev_lines to the kne_file, formatting as needed + foreach my $kne ( @datev_lines ) { + $kne_file->add_block("+" . $kne_file->format_amount(abs($kne->{umsatz}), 0)); + + # only add buchungsschluessel if it was previously defined + $kne_file->add_block("\x6C" . $kne->{buchungsschluessel}) if defined $kne->{buchungsschluessel}; + + # ($kne->{gegenkonto}) = $kne->{gegenkonto} =~ /^(\d+)/; + $kne_file->add_block("a" . trim_leading_zeroes($kne->{gegenkonto})); + + if ( $kne->{belegfeld1} ) { + my $invnumber = $kne->{belegfeld1}; foreach my $umlaut (keys(%umlaute)) { - $name =~ s/${umlaut}/${umlaute{$umlaut}}/g; + $invnumber =~ s/${umlaut}/${umlaute{$umlaut}}/g; } - $name =~ s/[^0-9A-Za-z\$\%\&\*\+\-\ \/]//g; - $name = substr($name, 0, 30); - $name =~ s/\ *$//; - $kne_file->add_block("\x1E" . $name . "\x1C"); + $invnumber =~ s/[^0-9A-Za-z\$\%\&\*\+\-\/]//g; + $invnumber = substr($invnumber, 0, 12); + $invnumber =~ s/\ *$//; + $kne_file->add_block("\xBD" . $invnumber . "\x1C"); + } - $kne_file->add_block("\xBA" . $kne->{'ustid'} . "\x1C") if $kne->{'ustid'}; + $kne_file->add_block("\xBE" . &datetofour($kne->{belegfeld2},1) . "\x1C"); - $kne_file->add_block("\xB3" . $kne->{'waehrung'} . "\x1C" . "\x79"); - }; + $kne_file->add_block("d" . &datetofour($kne->{datum},0)); - $umsatzsumme = $kne_file->format_amount(abs($umsatzsumme), 0); - my $mandantenendsumme = "x" . $kne_file->format_amount($umsatzsumme / 100.0, 14) . "\x79\x7a"; + # ($kne->{konto}) = $kne->{konto} =~ /^(\d+)/; + $kne_file->add_block("e" . trim_leading_zeroes($kne->{konto})); - $kne_file->add_block($mandantenendsumme); - $kne_file->flush(); + my $name = $kne->{buchungstext}; + foreach my $umlaut (keys(%umlaute)) { + $name =~ s/${umlaut}/${umlaute{$umlaut}}/g; + } + $name =~ s/[^0-9A-Za-z\$\%\&\*\+\-\ \/]//g; + $name = substr($name, 0, 30); + $name =~ s/\ *$//; + $kne_file->add_block("\x1E" . $name . "\x1C"); - open(ED, ">", $ed_filename) or die "can't open outputfile: $!\n"; - print(ED $kne_file->get_data()); - close(ED); + $kne_file->add_block("\xBA" . $kne->{'ustid'} . "\x1C") if $kne->{'ustid'}; - $ed_versionset[$fileno] = $self->make_ed_versionset($header, $filename, $kne_file->get_block_count()); - $fileno++; - } + $kne_file->add_block("\xB3" . $kne->{'waehrung'} . "\x1C" . "\x79"); + }; + + $umsatzsumme = $kne_file->format_amount(abs($umsatzsumme), 0); + my $mandantenendsumme = "x" . $kne_file->format_amount($umsatzsumme / 100.0, 14) . "\x79\x7a"; + + $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); + + $ed_versionset[$fileno] = $self->make_ed_versionset($header, $filename, $kne_file->get_block_count()); #Make EV Verwaltungsdatei - my $ev_header = $self->make_ev_header($form, $fileno); + 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"; @@ -1140,7 +1174,7 @@ sub _format_accno { sub csv_export_for_tax_accountant { my ($self) = @_; - $self->_get_transactions(from_to => $self->fromto); + $self->generate_datev_data(from_to => $self->fromto); foreach my $transaction (@{ $self->{DATEV} }) { foreach my $entry (@{ $transaction }) { @@ -1323,6 +1357,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. @@ -1335,6 +1379,30 @@ This module implements the DATEV export standard. For usage see above. 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 Loads DATEV Stammdaten and returns as hashref. @@ -1398,6 +1466,25 @@ Returns a list of errors that occured. If no errors occured, the export was a su 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' + # ] + # }; + =back =head1 ATTRIBUTES