1 #=====================================================================
6 # Email: p.reetz@linet-services.de
7 # Web: http://www.lx-office.org
10 # This program is free software; you can redistribute it and/or modify
11 # it under the terms of the GNU General Public License as published by
12 # the Free Software Foundation; either version 2 of the License, or
13 # (at your option) any later version.
15 # This program is distributed in the hope that it will be useful,
16 # but WITHOUT ANY WARRANTY; without even the implied warranty of
17 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
18 # GNU General Public License for more details.
19 # You should have received a copy of the GNU General Public License
20 # along with this program; if not, write to the Free Software
21 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
22 #======================================================================
25 #======================================================================
32 $main::lxdebug->enter_sub();
34 my ($self, $myconfig, $form) = @_;
37 my $dbh = $form->dbconnect($myconfig);
39 $query = qq|SELECT * FROM datev|;
40 $sth = $dbh->prepare($query);
41 $sth->execute || $form->dberror($query);
43 my $ref = $sth->fetchrow_hashref(NAME_lc);
45 map { $form->{$_} = $ref->{$_} } keys %$ref;
49 $main::lxdebug->leave_sub();
53 sub save_datev_stamm {
54 $main::lxdebug->enter_sub();
56 my ($self, $myconfig, $form) = @_;
59 my $dbh = $form->dbconnect_noauto($myconfig);
61 $query = qq|DELETE FROM datev|;
62 $dbh->do($query) || $form->dberror($query);
64 $query = qq|INSERT INTO datev
65 (beraternr, beratername, dfvkz, mandantennr, datentraegernr, abrechnungsnr) VALUES
66 (|.$dbh->quote($form->{beraternr}).qq|,|.$dbh->quote($form->{beratername}).qq|,|.$dbh->quote($form->{dfvkz}).qq|,
67 |.$dbh->quote($form->{mandantennr}).qq|,|.$dbh->quote($form->{datentraegernr}).qq|,|.$dbh->quote($form->{abrechnungsnr}).qq|)|;
68 $sth = $dbh->prepare($query);
69 $sth->execute || $form->dberror($query);
74 $main::lxdebug->leave_sub();
78 $main::lxdebug->enter_sub();
80 my ($self, $myconfig, $form) = @_;
83 if ($form->{exporttype}==0) {
84 $rc = &kne_buchungsexport($myconfig, $form);
86 $rc = &kne_stammdatenexport($myconfig, $form);
89 $main::lxdebug->leave_sub();
96 $main::lxdebug->enter_sub();
98 my ($self, $myconfig, $form) = @_;
100 # connect to database
101 my $dbh = $form->dbconnect_noauto($myconfig);
104 $main::lxdebug->leave_sub();
108 $main::lxdebug->enter_sub();
110 my ($zeitraum, $monat, $quartal, $transdatefrom, $transdateto) = @_;
112 $fromto="transdate >= ";
114 my @a = localtime; $a[5] += 1900;
116 if ($zeitraum eq "monat") {
118 $monat eq "1" && do {
119 $form->{fromdate}="1.1.$jahr";
120 $form->{todate}="31.1.$jahr";
123 $monat eq "2" && do {
124 $form->{fromdate}="1.2.$jahr";
125 #this works from 1901 to 2099, 1900 and 2100 fail.
126 $leap=($jahr % 4 == 0) ? "29" : "28";
127 $form->{todate}="$leap.2.$jahr";
130 $monat eq "3" && do {
131 $form->{fromdate}="1.3.$jahr";
132 $form->{todate}="31.3.$jahr";
135 $monat eq "4" && do {
136 $form->{fromdate}="1.4.$jahr";
137 $form->{todate}="30.4.$jahr";
140 $monat eq "5" && do {
141 $form->{fromdate}="1.5.$jahr";
142 $form->{todate}="31.5.$jahr";
145 $monat eq "6" && do {
146 $form->{fromdate}="1.6.$jahr";
147 $form->{todate}="30.6.$jahr";
150 $monat eq "7" && do {
151 $form->{fromdate}="1.7.$jahr";
152 $form->{todate}="31.7.$jahr";
155 $monat eq "8" && do {
156 $form->{fromdate}="1.8.$jahr";
157 $form->{todate}="31.8.$jahr";
160 $monat eq "9" && do {
161 $form->{fromdate}="1.9.$jahr";
162 $form->{todate}="30.9.$jahr";
165 $monat eq "10" && do {
166 $form->{fromdate}="1.10.$jahr";
167 $form->{todate}="31.10.$jahr";
170 $monat eq "11" && do {
171 $form->{fromdate}="1.11.$jahr";
172 $form->{todate}="30.11.$jahr";
175 $monat eq "12" && do {
176 $form->{fromdate}="1.12.$jahr";
177 $form->{todate}="31.12.$jahr";
181 $fromto .= "'".$form->{fromdate}."' and transdate <= '".$form->{todate}."'";
184 elsif ($zeitraum eq "quartal") {
186 $fromto .= "'01.01.".$jahr."' and transdate <= '31.03.".$jahr."'";
188 elsif ($quartal==2) {
189 $fromto .= "'01.04.".$jahr."' and transdate <= '30.06.".$jahr."'";
191 elsif ($quartal==3) {
192 $fromto .= "'01.07.".$jahr."' and transdate <= '30.09.".$jahr."'";
194 elsif ($quartal==4) {
195 $fromto .= "'01.10.".$jahr."' and transdate <= '31.12.".$jahr."'";
199 elsif ($zeitraum eq "zeit") {
200 $fromto .= "'".$transdatefrom."' and transdate <= '".$transdateto."'";
203 $main::lxdebug->leave_sub();
208 sub get_transactions {
209 $main::lxdebug->enter_sub();
211 my ($myconfig, $form, $fromto) = @_;
213 # connect to database
214 my $dbh = $form->dbconnect($myconfig);
216 $fromto =~ s/transdate/ac\.transdate/g;
218 $query = qq|SELECT taxkey, rate FROM tax|;
219 $sth = $dbh->prepare($query);
220 $sth->execute || $form->dberror($query);
222 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
223 $taxes{$ref->{taxkey}} = $ref->{rate};
228 $query = qq|SELECT ac.oid, ac.transdate, ac.trans_id,ar.id, ac.amount, ac.taxkey, ar.invnumber, ar.duedate, ar.amount as umsatz,
229 ct.name, c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, t.chart_id, t.rate FROM acc_trans ac,ar ar, customer ct,
230 chart c LEFT JOIN tax t ON
231 (t.chart_id=c.id)WHERE $fromto AND ac.trans_id=ar.id AND ac.trans_id=ar.id
232 AND ar.customer_id=ct.id AND ac.chart_id=c.id
234 SELECT ac.oid, ac.transdate, ac.trans_id,ap.id, ac.amount, ac.taxkey, ap.invnumber, ap.duedate, ap.amount as umsatz,
235 ct.name, c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, t.chart_id, t.rate FROM acc_trans ac, ap ap, vendor ct, chart c LEFT JOIN tax t ON
237 WHERE $fromto AND ac.trans_id=ap.id AND ap.vendor_id=ct.id AND ac.chart_id=c.id
239 SELECT ac.oid, ac.transdate, ac.trans_id,gl.id, ac.amount, ac.taxkey, gl.reference AS invnumber, gl.transdate AS duedate, ac.amount as umsatz,
240 gl.description AS name, c.accno, c.taxkey_id as charttax, c.datevautomatik, c.id, t.chart_id, t.rate FROM acc_trans ac, gl gl,
241 chart c LEFT JOIN tax t ON
242 (t.chart_id=c.id) WHERE $fromto AND ac.trans_id=gl.id AND ac.chart_id=c.id
243 ORDER BY trans_id, oid|;
245 $sth = $dbh->prepare($query);
246 $sth->execute || $form->dberror($query);
250 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
253 $count += $ref->{amount};
255 while (abs($count)> 0.01 || $firstrun) {
256 $ref2 = $sth->fetchrow_hashref(NAME_lc);
258 $count += $ref2->{amount};
263 if (scalar(@ {$i}) >2) {
264 for my $j (0 .. (scalar(@ {$i}) -1)) {
265 if (abs($i->[$j]->{'amount'}) > abs($absumsatz)) {
266 $absumsatz = $i->[$j]->{'amount'};
270 $ml = ($i->[0]->{'umsatz'} > 0) ? 1 : -1;
271 for my $j (0 .. (scalar(@ {$i}) -1)) {
272 if (($j != $notsplitindex) && ($i->[$j]->{'chart_id'} eq "") && ($i->[$j]->{'taxkey'} eq "" || $i->[$j]->{'taxkey'} eq "0" || $i->[$j]->{'taxkey'} eq "1" || $i->[$j]->{'taxkey'} eq "10" || $i->[$j]->{'taxkey'} eq "11")) {
274 map({ $blubb{$_} = $i->[$notsplitindex]->{$_}; } keys(%{ $i->[$notsplitindex] }));
275 $absumsatz += $i->[$j]->{'amount'};
276 $blubb{'amount'} = $i->[$j]->{'amount'} * (-1);
277 $blubb{'umsatz'} = abs($i->[$j]->{'amount'}) * $ml;
278 $i->[$j]->{'umsatz'} = abs($i->[$j]->{'amount'}) * $ml;
279 push @{$splits[$g]}, \%blubb; #$i->[$notsplitindex];
280 push @{$splits[$g]}, $i->[$j];
281 push @{ $form->{DATEV} }, \@{$splits[$g]};
283 } elsif (($j != $notsplitindex) && ($i->[$j]->{'chart_id'} eq "")) {
284 $absumsatz += ($i->[$j]->{'amount'} * (1 + $taxes{$i->[$j]->{'taxkey'}}));
286 map({ $blubb{$_} = $i->[$notsplitindex]->{$_}; } keys(%{ $i->[$notsplitindex] }));
287 $test = 1+ $taxes{$i->[$j]->{'taxkey'}};
288 $blubb{'amount'} = $form->round_amount(($i->[$j]->{'amount'} * $test * -1),2);
290 #print(STDERR $test, " Taxrate\n\n");
291 $blubb{'umsatz'} = abs($form->round_amount(($i->[$j]->{'amount'} * $test),2)) *$ml;
293 $i->[$j]->{'umsatz'} = abs($form->round_amount(($i->[$j]->{'amount'} * $test),2)) *$ml;
295 #print(STDERR $i->[$j]->{'umsatz'}, " Steuer Umsatz\n");
296 #print(STDERR $i->[$j]->{'amount'}, " Steuer Betrag\n");
297 #print(STDERR $blubb{'umsatz'}, " Umsatz NOTSPLIT\n");
298 push @{$splits[$g]}, \%blubb;
299 push @{$splits[$g]}, $i->[$j];
300 push @{ $form->{DATEV} }, \@{$splits[$g]};
306 if (abs($absumsatz) > 0.01) {
307 print(STDERR $absumsatz, "ABSAUMSATZ\n");
308 $form->error("Datev-Export fehlgeschlagen!");
311 push @{ $form->{DATEV} }, \@{ $i};
318 $main::lxdebug->leave_sub();
321 sub make_kne_data_header {
322 $main::lxdebug->enter_sub();
324 my ($myconfig, $form, $fromto) = @_;
326 # connect to database
327 my $dbh = $form->dbconnect($myconfig);
333 $anwendungsnr = ($fromto) ? "\x31\x31" : "\x31\x33" ;
334 while (length($form->{datentraegernr})<3) {
335 $form->{datentraegernr}= "\x30".$form->{datentraegernr};
338 $header="\x1D\x18\x31".$form->{datentraegernr}.$anwendungsnr;
340 $dfvkz=$form->{dfvkz};
341 while (length($dfvkz)<2) {
342 $dfvkz = "\x30".$dfvkz;
346 $beraternr=$form->{beraternr};
347 while (length($beraternr)<7) {
348 $beraternr = "\x30".$beraternr;
350 $header .= $beraternr;
352 $mandantennr=$form->{mandantennr};
353 while (length($mandantennr)<5) {
354 $mandantennr = "\x30".$mandantennr;
356 $header .= $mandantennr;
358 $abrechnungsnr=$form->{abrechnungsnr}.$jahr;
359 while (length($abrechnungsnr)<6) {
360 $abrechnungsnr = "\x30".$abrechnungsnr;
362 $header .= $abrechnungsnr;
364 $fromto =~ s/transdate|>=|and|\'|<=//g;
365 my ($from, $to) = split / /, $fromto;
370 my ($fday, $fmonth, $fyear) = split /\./, $from;
371 if (length($fmonth) < 2) {
372 $fmonth = "0".$fmonth;
374 if (length($fday) < 2) {
377 $from = $fday.$fmonth.substr($fyear, -2, 2);
383 my ($tday, $tmonth, $tyear) = split /\./, $to;
384 if (length($tmonth) <2) {
385 $tmonth = "0".$tmonth;
387 if (length($tday) <2) {
390 $to = $tday.$tmonth.substr($tyear, -2, 2);
394 $primanota = "\x30\x30\x31";
395 $header .= $primanota;
398 $passwort=$form->{passwort};
399 while (length($passwort)<4) {
400 $passwort = "\x30".$passwort;
402 $header .= $passwort;
404 $anwendungsinfo = "\x20" x 16;
405 $header .= $anwendungsinfo;
406 $inputinfo = "\x20" x 16;
407 $header .= $inputinfo;
412 if ($form->{exporttype}==0) {
413 $versionssatz= "\xB5"."1,";
415 $versionssatz= "\xB6"."1,";}
418 $query = qq| select accno from chart limit 1|;
419 $sth=$dbh->prepare($query);
420 $sth->execute || $form->dberror($query);
421 my $ref = $sth->fetchrow_hashref(NAME_lc);
423 $accnolength = $ref->{accno};
426 $versionssatz .= length($accnolength);
427 $versionssatz .= ",";
428 $versionssatz .= length($accnolength);
429 $versionssatz .= ",SELF"."\x1C\x79";
433 $header .= $versionssatz;
435 $main::lxdebug->leave_sub();
441 $main::lxdebug->enter_sub();
443 my ($date, $six) = @_;
445 ($day, $month, $year) = split /\./, $date;
448 $day = substr($day,1,1);
450 if (length($month) <2) {
453 if (length($year) >2) {
454 $year = substr($year, -2, 2);
458 $date= $day.$month.$year;
463 $main::lxdebug->leave_sub();
469 $main::lxdebug->enter_sub();
471 my ($umsatz, $stellen) = @_;
474 ($vorkomma, $nachkomma) = split /\./, $umsatz;
477 for ($i=$stellen; $i >= $stellen+2 - length($vorkomma); $i--) {
481 for ($i=3; $i > length($nachkomma); $i--) {
484 $umsatz = $vorkomma.substr($nachkomma,0,2);
486 $main::lxdebug->leave_sub();
491 sub make_ed_versionset {
492 $main::lxdebug->enter_sub();
494 my ($header, $filename, $blockcount, $fromto) = @_;
496 $versionset="V".substr($filename,2,5);
497 $versionset.=substr($header,6,22);
499 $versionset.="0000".substr($header,28,19);
500 } else { $datum= "\x20" x 16;
501 $versionset .= $datum."001".substr($header,28,4);
503 while (length($blockcount) <5) {
504 $blockcount= "0".$blockcount;
506 $versionset.=$blockcount;
508 $versionset.="\x20\x31";
509 $versionset.= substr($header,-12,10)." ";
510 $versionset.= "\x20" x 53;
512 $main::lxdebug->leave_sub();
518 $main::lxdebug->enter_sub();
520 my ($form, $fileno) = @_;
521 $datentraegernr=$form->{datentraegernr};
522 $beraternummer=$form->{beraternr};
523 $beratername=$form->{beratername};
524 $anzahl_dateien=$fileno;
526 while (length($datentraegernr) <3) {
527 $datentraegernr.= " ";
530 while (length($beraternummer) <7) {
534 while (length($beratername) <9) {
538 while (length($anzahl_dateien) <5) {
539 $anzahl_dateien = "0".$anzahl_dateien;
543 $ev_header=$datentraegernr."\x20\x20\x20".$beraternummer.$beratername."\x20";
544 $ev_header.=$anzahl_dateien.$anzahl_dateien;
545 $ev_header.= "\x20" x 95;
548 $main::lxdebug->leave_sub();
553 sub kne_buchungsexport {
554 $main::lxdebug->enter_sub();
556 my ($myconfig, $form) = @_;
558 my $export_path="datev/";
559 my $filename="ED00000";
564 $fromto= &get_dates($form->{zeitraum}, $form->{monat}, $form->{quartal}, $form->{transdatefrom}, $form->{transdateto});
565 &get_transactions($myconfig, $form, $fromto);
567 while (scalar(@{ $form->{DATEV}})) {
569 my $remaining_bytes=256;
572 my $buchungssatz = "";
574 my $ed_filename= $export_path.$filename;
575 open (ED, "> $ed_filename") or die "can't open outputfile: $!\n";
576 $header=&make_kne_data_header($myconfig, $form, $fromto);
577 $remaining_bytes -= length($header);
579 while (scalar(@{ $form->{DATEV}})>0) {
580 $transaction = shift @{ $form->{DATEV}};
581 $trans_lines = scalar(@ {$transaction});
593 %umlaute = ('ä' => 'ae', 'ö' => 'oe', 'ü' => 'ue', 'Ä' => 'Ae', 'Ö' => 'Oe', 'Ü' => 'Ue', 'ß' => 'sz');
595 for (my $i=0; $i <$trans_lines; $i++) {
596 if (abs($transaction->[$i]->{'umsatz'}) > abs($umsatz)) {
597 $umsatz = $transaction->[$i]->{'umsatz'};
599 if ($transaction->[$i]->{'datevautomatik'}) {
602 if ($transaction->[$i]->{'taxkey'}) {
603 $taxkey = $transaction->[$i]->{'taxkey'};
605 if ($transaction->[$i]->{'charttax'}) {
606 $charttax = $transaction->[$i]->{'charttax'};
608 if (($transaction->[$i]->{'id'} eq $transaction->[$i]->{'chart_id'}) && ($trans_lines > 2)) {
609 undef($transaction->[$i]);
610 } elsif ($transaction->[$i]->{'amount'} >0) {
617 $umsatzsumme += $umsatz;
619 # Umwandlung von Umlauten und Sonderzeichen in erlaubte Zeichen bei Textfeldern
620 foreach $umlaut (keys(%umlaute)) {
621 $transaction->[$haben]->{'invnumber'} =~ s/${umlaut}/${umlaute{$umlaut}}/g;
622 $transaction->[$haben]->{'name'} =~ s/${umlaut}/${umlaute{$umlaut}}/g;
625 $transaction->[$haben]->{'invnumber'} =~ s/[^0-9A-Za-z\$\%\&\*\+\-\/]//g;
626 $transaction->[$haben]->{'name'} =~ s/[^0-9A-Za-z\$\%\&\*\+\-\ \/]//g;
629 $transaction->[$haben]->{'invnumber'} = substr($transaction->[$haben]->{'invnumber'}, 0, 12);
630 $transaction->[$haben]->{'name'} = substr($transaction->[$haben]->{'name'}, 0, 30);
631 $transaction->[$haben]->{'invnumber'} =~ s/\ *$//;
632 $transaction->[$haben]->{'name'} =~ s/\ *$//;
635 if ($trans_lines >= 2) {
637 $gegenkonto = "a".$transaction->[$haben]->{'accno'};
638 $konto = "e".$transaction->[$soll]->{'accno'};
639 if ($transaction->[$haben]->{'invnumber'} ne "") {
640 $belegfeld1= "\xBD".$transaction->[$haben]->{'invnumber'}."\x1C";}
642 $datum .= &datetofour($transaction->[$haben]->{'transdate'},0);
643 $waehrung = "\xB3"."EUR"."\x1C";
644 if ($transaction->[$haben]->{'name'} ne "") {
645 $buchungstext = "\x1E".$transaction->[$haben]->{'name'}."\x1C";}
646 if ($transaction->[$haben]->{'duedate'} ne "") {
647 $belegfeld2 = "\xBE".&datetofour($transaction->[$haben]->{'duedate'},1)."\x1C";}
651 if (($remaining_bytes- length("+".&formatumsatz($umsatz, 0))) <=6) {
652 $fuellzeichen = ($blockcount * 256 - length($buchungssatz.$header));
653 $buchungssatz .= "\x00" x $fuellzeichen;
655 $total_bytes = ($blockcount) *256;
657 $vorzeichen = ($umsatz > 0) ? "+" : "-";
658 $buchungssatz .=$vorzeichen.&formatumsatz($umsatz, 0);
659 $remaining_bytes = $total_bytes - length($buchungssatz.$header);
661 if (($taxkey || $datevautomatik) && (!$datevautomatik || ($datevautomatik && ($charttax ne $taxkey)))) {
662 if (($remaining_bytes- length("\x6C"."11")) <=6) {
663 $fuellzeichen = ($blockcount * 256 - length($buchungssatz.$header));
664 $buchungssatz .= "\x00" x $fuellzeichen;
666 $total_bytes = ($blockcount) *256;
668 if (!$datevautomatik) {
669 $buchungssatz .="\x6C".$taxkey;
671 $buchungssatz .="\x6C"."4";
673 $remaining_bytes = $total_bytes - length($buchungssatz.$header);
676 if (($remaining_bytes- length($gegenkonto)) <=6) {
677 $fuellzeichen = ($blockcount * 256 - length($buchungssatz.$header));
678 $buchungssatz .= "\x00" x $fuellzeichen;
680 $total_bytes = ($blockcount) *256;
682 $buchungssatz .= $gegenkonto;
683 $remaining_bytes = $total_bytes - length($buchungssatz.$header);
685 if (($remaining_bytes- length($belegfeld1)) <=6) {
686 $fuellzeichen = ($blockcount * 256 - length($buchungssatz.$header));
687 $buchungssatz .= "\x00" x $fuellzeichen;
689 $total_bytes = ($blockcount) *256;
691 $buchungssatz .= $belegfeld1;
692 $remaining_bytes = $total_bytes - length($buchungssatz.$header);
694 if (($remaining_bytes- length($belegfeld2)) <=6) {
695 $fuellzeichen = ($blockcount * 256 - length($buchungssatz.$header));
696 $buchungssatz .= "\x00" x $fuellzeichen;
698 $total_bytes = ($blockcount) *256;
700 $buchungssatz .= $belegfeld2;
701 $remaining_bytes = $total_bytes - length($buchungssatz.$header);
703 if (($remaining_bytes- length($datum)) <=6) {
704 $fuellzeichen = ($blockcount * 256 - length($buchungssatz.$header));
705 $buchungssatz .= "\x00" x $fuellzeichen;
707 $total_bytes = ($blockcount) *256;
709 $buchungssatz .= $datum;
710 $remaining_bytes = $total_bytes - length($buchungssatz.$header);
712 if (($remaining_bytes- length($konto)) <=6) {
713 $fuellzeichen = ($blockcount * 256 - length($buchungssatz.$header));
714 $buchungssatz .= "\x00" x $fuellzeichen;
716 $total_bytes = ($blockcount) *256;
718 $buchungssatz .= $konto;
719 $remaining_bytes = $total_bytes - length($buchungssatz.$header);
722 if (($remaining_bytes- length($buchungstext)) <=6) {
723 $fuellzeichen = ($blockcount * 256 - length($buchungssatz.$header));
724 $buchungssatz .= "\x00" x $fuellzeichen;
726 $total_bytes = ($blockcount) *256;
728 $buchungssatz .= $buchungstext;
729 $remaining_bytes = $total_bytes - length($buchungssatz.$header);
731 if (($remaining_bytes- (length($waehrung."\x79"))) <=6) {
732 $fuellzeichen = ($blockcount * 256 - length($buchungssatz.$header));
733 $buchungssatz .= "\x00" x $fuellzeichen;
735 $total_bytes = ($blockcount) *256;
737 $buchungssatz .= $waehrung."\x79";
738 $remaining_bytes = $total_bytes - length($buchungssatz.$header);
742 $mandantenendsumme = "x".&formatumsatz($umsatzsumme,14)."\x79"."\x7a";
743 $fuellzeichen = 256 - (length($header.$buchungssatz.$mandantenendsumme) % 256);
744 $dateiende = "\x00" x $fuellzeichen;
746 print (ED $buchungssatz);
747 print (ED $mandantenendsumme);
748 print (ED $dateiende);
751 $ed_versionset[$fileno]= &make_ed_versionset($header, $filename, $blockcount, $fromto);
755 #Make EV Verwaltungsdatei
756 $ev_header= &make_ev_header($form, $fileno);
757 $ev_filename= $export_path.$evfile;
758 open (EV, "> $ev_filename") or die "can't open outputfile: EV01\n";
759 print (EV $ev_header);
761 foreach $file (@ed_versionset) {
762 print (EV $ed_versionset[$file]);
766 $main::lxdebug->leave_sub();
769 sub kne_stammdatenexport {
770 $main::lxdebug->enter_sub();
772 my ($myconfig, $form) = @_;
773 $form->{abrechnungsnr}="99";
775 my $export_path="datev/";
776 my $filename="ED00000";
782 my $remaining_bytes=256;
784 my $buchungssatz = "";
786 my $ed_filename= $export_path.$filename;
787 open (ED, "> $ed_filename") or die "can't open outputfile: $!\n";
788 $header=&make_kne_data_header($myconfig, $form, "");
789 $remaining_bytes -= length($header);
792 # connect to database
793 my $dbh = $form->dbconnect($myconfig);
796 $query = qq|SELECT c.accno, c.description FROM chart c WHERE c.accno >=|.$dbh->quote($form->{accnofrom}).qq|
797 AND c.accno <= |.$dbh->quote($form->{accnoto}).qq| ORDER BY c.accno|;
799 $sth = $dbh->prepare($query);
800 $sth->execute || $form->dberror($query);
802 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
803 if (($remaining_bytes- length("t".$ref->{'accno'})) <=6) {
804 $fuellzeichen = ($blockcount * 256 - length($buchungssatz.$header));
805 $buchungssatz .= "\x00" x $fuellzeichen;
807 $total_bytes = ($blockcount) *256;
809 $buchungssatz .= "t".$ref->{'accno'};
810 $remaining_bytes = $total_bytes - length($buchungssatz.$header);
811 $ref->{'description'} =~ s/[^0-9A-Za-z\$\%\&\*\+\-\/]//g;
812 $ref->{'description'} = substr($ref->{'description'}, 0, 40);
813 $ref->{'description'} =~ s/\ *$//;
815 if (($remaining_bytes- length("\x1E".$ref->{'description'}."\x1C\x79")) <=6) {
816 $fuellzeichen = ($blockcount * 256 - length($buchungssatz.$header));
817 $buchungssatz .= "\x00" x $fuellzeichen;
819 $total_bytes = ($blockcount) *256;
821 $buchungssatz .= "\x1E".$ref->{'description'}."\x1C\x79";
822 $remaining_bytes = $total_bytes - length($buchungssatz.$header);
827 print (ED $buchungssatz);
828 $fuellzeichen = 256 - (length($header.$buchungssatz."z") % 256);
829 $dateiende = "\x00" x $fuellzeichen;
831 print (ED $dateiende);
834 #Make EV Verwaltungsdatei
835 $ed_versionset[0]= &make_ed_versionset($header, $filename, $blockcount, $fromto);
837 $ev_header= &make_ev_header($form, $fileno);
838 $ev_filename= $export_path.$evfile;
839 open (EV, "> $ev_filename") or die "can't open outputfile: EV01\n";
840 print (EV $ev_header);
842 foreach $file (@ed_versionset) {
843 print (EV $ed_versionset[$file]);
850 $main::lxdebug->leave_sub();