+ 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 (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 generate_datev_lines {
+ my ($self) = @_;
+
+ 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'};
+ }
+ } 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) {
+
+ # 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'};
+
+ 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'};
+ }
+
+ # 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;
+
+ push(@datev_lines, \%datev_data) if $datev_data{umsatz};
+ }
+
+ # example of modifying export data:
+ # foreach my $datev_line ( @datev_lines ) {
+ # if ( $datev_line{"konto"} eq '1234' ) {
+ # $datev_line{"konto"} = '9999';
+ # }
+ # }
+ #
+
+ return \@datev_lines;
+}
+
+
+sub kne_buchungsexport {
+ $main::lxdebug->enter_sub();
+
+ my ($self) = @_;
+
+ my $form = $::form;
+
+ 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;
+
+ my @datev_lines = @{ $self->generate_datev_lines };
+
+
+ my $umsatzsumme = sum map { $_->{umsatz} } @datev_lines;
+
+ # 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)) {
+ $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");
+ }
+
+ $kne_file->add_block("\xBE" . &datetofour($kne->{belegfeld2},1) . "\x1C");
+
+ $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 $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");
+
+ $kne_file->add_block("\xBA" . SL::VATIDNr->normalize($kne->{'ustid'}) . "\x1C") if $kne->{'ustid'};
+
+ $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_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 $file);
+ }
+ close(EV);
+ ###
+
+ $self->add_filenames(@filenames);
+
+ $main::lxdebug->leave_sub();
+
+ return { 'download_token' => $self->download_token, 'filenames' => \@filenames };
+}
+
+sub kne_stammdatenexport {
+ $main::lxdebug->enter_sub();
+
+ 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 = "";