| 27 |
27 |
package DATEV;
|
| 28 |
28 |
|
| 29 |
29 |
use SL::DBUtils;
|
|
30 |
use SL::DATEV::KNEFile;
|
| 30 |
31 |
|
| 31 |
32 |
use Data::Dumper;
|
| 32 |
33 |
|
| ... | ... | |
| 500 |
501 |
return $date;
|
| 501 |
502 |
}
|
| 502 |
503 |
|
| 503 |
|
sub formatumsatz {
|
| 504 |
|
$main::lxdebug->enter_sub();
|
| 505 |
|
|
| 506 |
|
my ($umsatz, $stellen) = @_;
|
| 507 |
|
|
| 508 |
|
$umsatz =~ s/-//;
|
| 509 |
|
($vorkomma, $nachkomma) = split(/\./, $umsatz);
|
| 510 |
|
$umsatz = "";
|
| 511 |
|
if ($stellen > 0) {
|
| 512 |
|
for ($i = $stellen; $i >= $stellen + 2 - length($vorkomma); $i--) {
|
| 513 |
|
$umsatz .= "0";
|
| 514 |
|
}
|
| 515 |
|
}
|
| 516 |
|
for ($i = 3; $i > length($nachkomma); $i--) {
|
| 517 |
|
$nachkomma .= "0";
|
| 518 |
|
}
|
| 519 |
|
$umsatz = $vorkomma . substr($nachkomma, 0, 2);
|
|
504 |
sub trim_leading_zeroes {
|
|
505 |
my $str = shift;
|
| 520 |
506 |
|
| 521 |
|
$main::lxdebug->leave_sub();
|
|
507 |
$str =~ s/^0+//g;
|
| 522 |
508 |
|
| 523 |
|
return $umsatz;
|
|
509 |
return $str;
|
| 524 |
510 |
}
|
| 525 |
511 |
|
| 526 |
512 |
sub make_ed_versionset {
|
| ... | ... | |
| 595 |
581 |
my $counter = 0;
|
| 596 |
582 |
print qq|<br>2. Durchlauf:|;
|
| 597 |
583 |
while (scalar(@{ $form->{DATEV} })) {
|
| 598 |
|
my $blockcount = 1;
|
| 599 |
|
my $remaining_bytes = 256;
|
| 600 |
|
my $total_bytes = 256;
|
| 601 |
|
my $umsatzsumme = 0;
|
| 602 |
|
my $buchungssatz = "";
|
|
584 |
my $umsatzsumme = 0;
|
| 603 |
585 |
$filename++;
|
| 604 |
586 |
my $ed_filename = $export_path . $filename;
|
| 605 |
587 |
push(@filenames, $filename);
|
| 606 |
|
open(ED, "> $ed_filename") or die "can't open outputfile: $!\n";
|
| 607 |
588 |
$header = &make_kne_data_header($myconfig, $form, $fromto, $start_jahr);
|
| 608 |
|
$remaining_bytes -= length($header);
|
|
589 |
|
|
590 |
my $kne_file = SL::DATEV::KNEFile->new();
|
|
591 |
$kne_file->add_block($header);
|
| 609 |
592 |
|
| 610 |
593 |
while (scalar(@{ $form->{DATEV} }) > 0) {
|
| 611 |
594 |
$transaction = shift @{ $form->{DATEV} };
|
| ... | ... | |
| 615 |
598 |
print("$counter ");
|
| 616 |
599 |
}
|
| 617 |
600 |
|
| 618 |
|
$umsatz = 0;
|
| 619 |
|
$gegenkonto = "";
|
| 620 |
|
$konto = "";
|
| 621 |
|
$belegfeld1 = "";
|
| 622 |
|
$datum = "";
|
| 623 |
|
$waehrung = "";
|
| 624 |
|
$buchungstext = "";
|
| 625 |
|
$belegfeld2 = "";
|
| 626 |
|
$datevautomatik = 0;
|
| 627 |
|
$taxkey = 0;
|
| 628 |
|
$charttax = 0;
|
| 629 |
|
%umlaute = ('?' => 'ae',
|
| 630 |
|
'?' => 'oe',
|
| 631 |
|
'?' => 'ue',
|
| 632 |
|
'?' => 'Ae',
|
| 633 |
|
'?' => 'Oe',
|
| 634 |
|
'?' => 'Ue',
|
| 635 |
|
'?' => 'sz');
|
| 636 |
|
|
|
601 |
my $umsatz = 0;
|
|
602 |
my $gegenkonto = "";
|
|
603 |
my $konto = "";
|
|
604 |
my $belegfeld1 = "";
|
|
605 |
my $datum = "";
|
|
606 |
my $waehrung = "";
|
|
607 |
my $buchungstext = "";
|
|
608 |
my $belegfeld2 = "";
|
|
609 |
my $datevautomatik = 0;
|
|
610 |
my $taxkey = 0;
|
|
611 |
my $charttax = 0;
|
|
612 |
my %umlaute = ('?' => 'ae',
|
|
613 |
'?' => 'oe',
|
|
614 |
'?' => 'ue',
|
|
615 |
'?' => 'Ae',
|
|
616 |
'?' => 'Oe',
|
|
617 |
'?' => 'Ue',
|
|
618 |
'?' => 'sz');
|
| 637 |
619 |
for (my $i = 0; $i < $trans_lines; $i++) {
|
| 638 |
620 |
if ($trans_lines == 2) {
|
| 639 |
621 |
if (abs($transaction->[$i]->{'amount'}) > abs($umsatz)) {
|
| ... | ... | |
| 656 |
638 |
if ( ($transaction->[$i]->{'id'} eq $transaction->[$i]->{'chart_id'})
|
| 657 |
639 |
&& ($trans_lines > 2)) {
|
| 658 |
640 |
undef($transaction->[$i]);
|
| 659 |
|
} elsif ($transaction->[$i]->{'amount'} > 0) {
|
|
641 |
} elsif ($transaction->[$i]->{'amount'} > 0) {
|
| 660 |
642 |
$haben = $i;
|
| 661 |
|
} else {
|
|
643 |
} else {
|
| 662 |
644 |
$soll = $i;
|
| 663 |
645 |
}
|
| 664 |
646 |
}
|
| 665 |
647 |
|
| 666 |
|
$umsatzsumme += abs($umsatz);
|
| 667 |
|
|
| 668 |
648 |
# Umwandlung von Umlauten und Sonderzeichen in erlaubte Zeichen bei Textfeldern
|
| 669 |
649 |
foreach $umlaut (keys(%umlaute)) {
|
| 670 |
|
$transaction->[$haben]->{'invnumber'} =~
|
| 671 |
|
s/${umlaut}/${umlaute{$umlaut}}/g;
|
| 672 |
|
$transaction->[$haben]->{'name'} =~ s/${umlaut}/${umlaute{$umlaut}}/g;
|
|
650 |
$transaction->[$haben]->{'invnumber'} =~ s/${umlaut}/${umlaute{$umlaut}}/g;
|
|
651 |
$transaction->[$haben]->{'name'} =~ s/${umlaut}/${umlaute{$umlaut}}/g;
|
| 673 |
652 |
}
|
| 674 |
653 |
|
| 675 |
654 |
$transaction->[$haben]->{'invnumber'} =~ s/[^0-9A-Za-z\$\%\&\*\+\-\/]//g;
|
| 676 |
|
$transaction->[$haben]->{'name'} =~ s/[^0-9A-Za-z\$\%\&\*\+\-\ \/]//g;
|
|
655 |
$transaction->[$haben]->{'name'} =~ s/[^0-9A-Za-z\$\%\&\*\+\-\ \/]//g;
|
| 677 |
656 |
|
| 678 |
|
$transaction->[$haben]->{'invnumber'} =
|
| 679 |
|
substr($transaction->[$haben]->{'invnumber'}, 0, 12);
|
| 680 |
|
$transaction->[$haben]->{'name'} =
|
| 681 |
|
substr($transaction->[$haben]->{'name'}, 0, 30);
|
|
657 |
$transaction->[$haben]->{'invnumber'} = substr($transaction->[$haben]->{'invnumber'}, 0, 12);
|
|
658 |
$transaction->[$haben]->{'name'} = substr($transaction->[$haben]->{'name'}, 0, 30);
|
| 682 |
659 |
$transaction->[$haben]->{'invnumber'} =~ s/\ *$//;
|
| 683 |
660 |
$transaction->[$haben]->{'name'} =~ s/\ *$//;
|
| 684 |
661 |
|
| 685 |
662 |
if ($trans_lines >= 2) {
|
| 686 |
663 |
|
| 687 |
|
$gegenkonto = "a" . $transaction->[$haben]->{'accno'};
|
| 688 |
|
$konto = "e" . $transaction->[$soll]->{'accno'};
|
|
664 |
$gegenkonto = "a" . trim_leading_zeroes($transaction->[$haben]->{'accno'});
|
|
665 |
$konto = "e" . trim_leading_zeroes($transaction->[$soll]->{'accno'});
|
| 689 |
666 |
if ($transaction->[$haben]->{'invnumber'} ne "") {
|
| 690 |
|
$belegfeld1 =
|
| 691 |
|
"\xBD" . $transaction->[$haben]->{'invnumber'} . "\x1C";
|
|
667 |
$belegfeld1 = "\xBD" . $transaction->[$haben]->{'invnumber'} . "\x1C";
|
| 692 |
668 |
}
|
| 693 |
669 |
$datum = "d";
|
| 694 |
670 |
$datum .= &datetofour($transaction->[$haben]->{'transdate'}, 0);
|
| ... | ... | |
| 697 |
673 |
$buchungstext = "\x1E" . $transaction->[$haben]->{'name'} . "\x1C";
|
| 698 |
674 |
}
|
| 699 |
675 |
if ($transaction->[$haben]->{'duedate'} ne "") {
|
| 700 |
|
$belegfeld2 = "\xBE"
|
| 701 |
|
. &datetofour($transaction->[$haben]->{'duedate'}, 1) . "\x1C";
|
|
676 |
$belegfeld2 = "\xBE" . &datetofour($transaction->[$haben]->{'duedate'}, 1) . "\x1C";
|
| 702 |
677 |
}
|
| 703 |
678 |
}
|
| 704 |
679 |
|
| 705 |
|
if (($remaining_bytes - length("+" . &formatumsatz($umsatz, 0))) <= 6) {
|
| 706 |
|
$fuellzeichen = ($blockcount * 256 - length($buchungssatz . $header));
|
| 707 |
|
$buchungssatz .= "\x00" x $fuellzeichen;
|
| 708 |
|
$blockcount++;
|
| 709 |
|
$total_bytes = ($blockcount) * 256;
|
| 710 |
|
}
|
| 711 |
|
$umsatz = abs($umsatz);
|
| 712 |
|
$vorzeichen = ($umsatz > 0) ? "+" : "-";
|
| 713 |
|
$buchungssatz .= $vorzeichen . &formatumsatz($umsatz, 0);
|
| 714 |
|
$remaining_bytes = $total_bytes - length($buchungssatz . $header);
|
| 715 |
|
|
| 716 |
|
if ( ($taxkey || $datevautomatik)
|
| 717 |
|
&& (!$datevautomatik || ($datevautomatik && ($charttax ne $taxkey)))) {
|
| 718 |
|
if (($remaining_bytes - length("\x6C" . "11")) <= 6) {
|
| 719 |
|
$fuellzeichen =
|
| 720 |
|
($blockcount * 256 - length($buchungssatz . $header));
|
| 721 |
|
$buchungssatz .= "\x00" x $fuellzeichen;
|
| 722 |
|
$blockcount++;
|
| 723 |
|
$total_bytes = ($blockcount) * 256;
|
| 724 |
|
}
|
| 725 |
|
if (!$datevautomatik) {
|
| 726 |
|
$buchungssatz .= "\x6C" . $taxkey;
|
| 727 |
|
} else {
|
| 728 |
|
$buchungssatz .= "\x6C" . "4";
|
| 729 |
|
}
|
| 730 |
|
$remaining_bytes = $total_bytes - length($buchungssatz . $header);
|
| 731 |
|
}
|
|
680 |
$umsatz = $kne_file->format_amount(abs($umsatz), 0);
|
|
681 |
$umsatzsumme += $umsatz;
|
|
682 |
$kne_file->add_block("+" . $umsatz);
|
| 732 |
683 |
|
| 733 |
|
if (($remaining_bytes - length($gegenkonto)) <= 6) {
|
| 734 |
|
$fuellzeichen = ($blockcount * 256 - length($buchungssatz . $header));
|
| 735 |
|
$buchungssatz .= "\x00" x $fuellzeichen;
|
| 736 |
|
$blockcount++;
|
| 737 |
|
$total_bytes = ($blockcount) * 256;
|
| 738 |
|
}
|
| 739 |
|
$buchungssatz .= $gegenkonto;
|
| 740 |
|
$remaining_bytes = $total_bytes - length($buchungssatz . $header);
|
| 741 |
|
|
| 742 |
|
if (($remaining_bytes - length($belegfeld1)) <= 6) {
|
| 743 |
|
$fuellzeichen = ($blockcount * 256 - length($buchungssatz . $header));
|
| 744 |
|
$buchungssatz .= "\x00" x $fuellzeichen;
|
| 745 |
|
$blockcount++;
|
| 746 |
|
$total_bytes = ($blockcount) * 256;
|
| 747 |
|
}
|
| 748 |
|
$buchungssatz .= $belegfeld1;
|
| 749 |
|
$remaining_bytes = $total_bytes - length($buchungssatz . $header);
|
| 750 |
|
|
| 751 |
|
if (($remaining_bytes - length($belegfeld2)) <= 6) {
|
| 752 |
|
$fuellzeichen = ($blockcount * 256 - length($buchungssatz . $header));
|
| 753 |
|
$buchungssatz .= "\x00" x $fuellzeichen;
|
| 754 |
|
$blockcount++;
|
| 755 |
|
$total_bytes = ($blockcount) * 256;
|
| 756 |
|
}
|
| 757 |
|
$buchungssatz .= $belegfeld2;
|
| 758 |
|
$remaining_bytes = $total_bytes - length($buchungssatz . $header);
|
| 759 |
|
|
| 760 |
|
if (($remaining_bytes - length($datum)) <= 6) {
|
| 761 |
|
$fuellzeichen = ($blockcount * 256 - length($buchungssatz . $header));
|
| 762 |
|
$buchungssatz .= "\x00" x $fuellzeichen;
|
| 763 |
|
$blockcount++;
|
| 764 |
|
$total_bytes = ($blockcount) * 256;
|
|
684 |
if ( ( $datevautomatik || $taxkey)
|
|
685 |
&& (!$datevautomatik || ($datevautomatik && ($charttax ne $taxkey)))) {
|
|
686 |
$kne_file->add_block("\x6C" . (!$datevautomatik ? $taxkey : "4"));
|
| 765 |
687 |
}
|
| 766 |
|
$buchungssatz .= $datum;
|
| 767 |
|
$remaining_bytes = $total_bytes - length($buchungssatz . $header);
|
| 768 |
|
|
| 769 |
|
if (($remaining_bytes - length($konto)) <= 6) {
|
| 770 |
|
$fuellzeichen = ($blockcount * 256 - length($buchungssatz . $header));
|
| 771 |
|
$buchungssatz .= "\x00" x $fuellzeichen;
|
| 772 |
|
$blockcount++;
|
| 773 |
|
$total_bytes = ($blockcount) * 256;
|
| 774 |
|
}
|
| 775 |
|
$buchungssatz .= $konto;
|
| 776 |
|
$remaining_bytes = $total_bytes - length($buchungssatz . $header);
|
| 777 |
|
|
| 778 |
|
if (($remaining_bytes - length($buchungstext)) <= 6) {
|
| 779 |
|
$fuellzeichen = ($blockcount * 256 - length($buchungssatz . $header));
|
| 780 |
|
$buchungssatz .= "\x00" x $fuellzeichen;
|
| 781 |
|
$blockcount++;
|
| 782 |
|
$total_bytes = ($blockcount) * 256;
|
| 783 |
|
}
|
| 784 |
|
$buchungssatz .= $buchungstext;
|
| 785 |
|
$remaining_bytes = $total_bytes - length($buchungssatz . $header);
|
| 786 |
|
|
| 787 |
|
if (($remaining_bytes - (length($waehrung . "\x79"))) <= 6) {
|
| 788 |
|
$fuellzeichen = ($blockcount * 256 - length($buchungssatz . $header));
|
| 789 |
|
$buchungssatz .= "\x00" x $fuellzeichen;
|
| 790 |
|
$blockcount++;
|
| 791 |
|
$total_bytes = ($blockcount) * 256;
|
| 792 |
|
}
|
| 793 |
|
$buchungssatz .= $waehrung . "\x79";
|
| 794 |
|
$remaining_bytes = $total_bytes - length($buchungssatz . $header);
|
| 795 |
688 |
|
|
689 |
$kne_file->add_block($gegenkonto);
|
|
690 |
$kne_file->add_block($belegfeld1);
|
|
691 |
$kne_file->add_block($belegfeld2);
|
|
692 |
$kne_file->add_block($datum);
|
|
693 |
$kne_file->add_block($konto);
|
|
694 |
$kne_file->add_block($buchungstext);
|
|
695 |
$kne_file->add_block($waehrung . "\x79");
|
| 796 |
696 |
}
|
| 797 |
697 |
|
| 798 |
|
$mandantenendsumme =
|
| 799 |
|
"x" . &formatumsatz($umsatzsumme, 14) . "\x79" . "\x7a";
|
| 800 |
|
$fuellzeichen =
|
| 801 |
|
256 - (length($header . $buchungssatz . $mandantenendsumme) % 256);
|
| 802 |
|
$dateiende = "\x00" x $fuellzeichen;
|
| 803 |
|
print(ED $header);
|
| 804 |
|
print(ED $buchungssatz);
|
| 805 |
|
print(ED $mandantenendsumme);
|
| 806 |
|
print(ED $dateiende);
|
|
698 |
my $mandantenendsumme = "x" . $kne_file->format_amount($umsatzsumme / 100.0, 14) . "\x79\x7a";
|
|
699 |
|
|
700 |
$kne_file->add_block($mandantenendsumme);
|
|
701 |
$kne_file->flush();
|
|
702 |
|
|
703 |
open(ED, "> $ed_filename") or die "can't open outputfile: $!\n";
|
|
704 |
print(ED $kne_file->get_data());
|
| 807 |
705 |
close(ED);
|
| 808 |
706 |
|
| 809 |
|
$ed_versionset[$fileno] =
|
| 810 |
|
&make_ed_versionset($header, $filename, $blockcount, $fromto);
|
|
707 |
$ed_versionset[$fileno] = &make_ed_versionset($header, $filename, $kne_file->get_block_count(), $fromto);
|
| 811 |
708 |
$fileno++;
|
| 812 |
709 |
}
|
| 813 |
710 |
|
| ... | ... | |
| 864 |
761 |
# connect to database
|
| 865 |
762 |
my $dbh = $form->dbconnect($myconfig);
|
| 866 |
763 |
|
| 867 |
|
$query =
|
| 868 |
|
qq|SELECT c.accno, c.description FROM chart c WHERE c.accno >=|
|
| 869 |
|
. $dbh->quote($form->{accnofrom}) . qq|
|
| 870 |
|
AND c.accno <= |
|
| 871 |
|
. $dbh->quote($form->{accnoto}) . qq| ORDER BY c.accno|;
|
|
764 |
my (@where, @values) = ((), ());
|
|
765 |
if ($form->{accnofrom}) {
|
|
766 |
push @where, 'c.accno >= ?';
|
|
767 |
push @values, $form->{accnofrom};
|
|
768 |
}
|
|
769 |
if ($form->{accnoto}) {
|
|
770 |
push @where, 'c.accno <= ?';
|
|
771 |
push @values, $form->{accnoto};
|
|
772 |
}
|
|
773 |
|
|
774 |
my $where_str = ' WHERE ' . join(' AND ', map { "($_)" } @where) if (scalar @where);
|
| 872 |
775 |
|
| 873 |
|
$sth = $dbh->prepare($query);
|
| 874 |
|
$sth->execute || $form->dberror($query);
|
|
776 |
my $query = qq|SELECT c.accno, c.description
|
|
777 |
FROM chart c
|
|
778 |
$where_str
|
|
779 |
ORDER BY c.accno|;
|
|
780 |
|
|
781 |
my $sth = $dbh->prepare($query);
|
|
782 |
$sth->execute(@values) || $form->dberror($query);
|
| 875 |
783 |
|
| 876 |
784 |
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
|
| 877 |
785 |
if (($remaining_bytes - length("t" . $ref->{'accno'})) <= 6) {
|
DATEV-Schnittstelle: Auslagerung von KNE-Schreibfunktionen in eigenes Modul. Kosmetik. Einrückung. Coderefactoring für bessere Lesbarkeit.