1 #=====================================================================
3 # Copyright (c) 2004 by Udo Spallek, Aachen
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 #======================================================================
24 #=====================================================================
28 sub create_steuernummer {
29 $main::lxdebug->enter_sub();
31 $part = $form->{part};
32 $patterncount = $form->{patterncount};
33 $delimiter = $form->{delimiter};
34 $elster_pattern = $form->{elster_pattern};
36 # rebuild steuernummer and elstersteuernummer
37 # es gibt eine gespeicherte steuernummer $form->{steuernummer}
38 # und die parts und delimiter
43 $steuernummer_new = $part;
44 $elstersteuernummer_new = $elster_FFFF;
45 $elstersteuernummer_new .= '0';
47 for ($h = 1; $h < $patterncount; $h++) {
48 $steuernummer_new .= qq|$delimiter|;
49 for ($i = 1; $i <= length($elster_pattern); $i++) {
50 $steuernummer_new .= $form->{"part_$h\_$i"};
51 $elstersteuernummer_new .= $form->{"part_$h\_$i"};
54 if ($form->{steuernummer} ne $steuernummer_new) {
55 $form->{steuernummer} = $steuernummer_new;
56 $form->{elstersteuernummer} = $elstersteuernummer_new;
57 $form->{steuernummer_new} = $steuernummer_new;
59 $main::lxdebug->leave_sub();
60 return ($steuernummer_new, $elstersteuernummer_new);
63 sub steuernummer_input {
64 $main::lxdebug->enter_sub();
66 my ($self, $elsterland, $elsterFFFF, $steuernummer) = @_;
68 my $steuernummer_input = '';
70 $elster_land = $elsterland;
71 $elster_FFFF = $elsterFFFF;
72 $steuernummer = '0000000000' if ($steuernummer eq '');
74 # $steuernummer formatieren (nur Zahlen) -> $stnr
75 my $stnr = $steuernummer;
78 #Pattern description Elstersteuernummer
79 my %elster_STNRformat = (
80 'Mecklenburg Vorpommern' => 'FFF/BBB/UUUUP', # '/' 3
81 'Hessen' => '0FF BBB UUUUP', # ' ' 3
82 'Nordrhein Westfalen' => 'FFF/BBBB/UUUP', # '/' 3
83 'Schleswig Holstein' => 'FF BBB UUUUP', # ' ' 2
84 'Berlin' => 'FF/BBB/UUUUP', # '/' 3
85 'Thüringen' => 'FFF/BBB/UUUUP', # '/' 3
86 'Sachsen' => 'FFF/BBB/UUUUP', # '/' 3
87 'Hamburg' => 'FF/BBB/UUUUP', # '/' 3
88 'Baden Würtemberg' => 'FF/BBB/UUUUP', # '/' 2
89 'Sachsen Anhalt' => 'FFF/BBB/UUUUP', # '/' 3
90 'Saarland' => 'FFF/BBB/UUUUP', # '/' 3
91 'Bremen' => 'FF BBB UUUUP', # ' ' 3
92 'Bayern' => 'FFF/BBB/UUUUP', # '/' 3
93 'Rheinland Pfalz' => 'FF/BBB/UUUU/P', # '/' 4
94 'Niedersachsen' => 'FF/BBB/UUUUP', # '/' 3
95 'Brandenburg' => 'FFF/BBB/UUUUP', # '/' 3
99 my $elster_pattern = $elster_STNRformat{$elster_land};
100 my @elster_pattern = split(' ', $elster_pattern);
101 my $delimiter = ' ';
102 my $patterncount = @elster_pattern;
103 if ($patterncount < 2) {
104 @elster_pattern = ();
105 @elster_pattern = split('/', $elster_pattern);
107 $patterncount = @elster_pattern;
110 # no we have an array of patternparts and a delimiter
111 # create the first automated and fixed part and delimiter
113 $steuernummer_input .= qq|<b><font size="+1">|;
116 $elster_pattern[0] eq 'FFF' && do {
117 $part = substr($elster_FFFF, 1, 4);
118 $steuernummer_input .= qq|$part|;
121 $elster_pattern[0] eq '0FF' && do {
122 $part = '0' . substr($elster_FFFF, 2, 4);
123 $steuernummer_input .= qq|$part|;
126 $elster_pattern[0] eq 'FF' && do {
127 $part = substr($elster_FFFF, 2, 4);
128 $steuernummer_input .= qq|$part|;
132 $steuernummer_input .= qq|Fehler!|;
137 #now the rest of the Steuernummer ...
138 $steuernummer_input .= qq|</b></font>|;
139 $steuernummer_input .= qq|\n
140 <input type=hidden name="elster_pattern" value="$elster_pattern">
141 <input type=hidden name="patterncount" value="$patterncount">
142 <input type=hidden name="patternlength" value="$patterncount">
143 <input type=hidden name="delimiter" value="$delimiter">
144 <input type=hidden name="part" value="$part">
149 for (my $h = 1; $h < $patterncount; $h++) {
150 $steuernummer_input .= qq| $delimiter \n|;
151 for (my $i = 1; $i <= length($elster_pattern[$h]); $i++) {
152 $steuernummer_input .= qq|<select name="part_$h\_$i">\n|;
154 for (my $j = 0; $j <= 9; $j++) {
155 $steuernummer_input .= qq| <option value="$j"|;
156 if ($steuernummer ne '') {
157 if ($j eq substr($stnr, length($part) + $k, 1)) {
158 $steuernummer_input .= qq| selected|;
161 $steuernummer_input .= qq|>$j</option>\n|;
164 $steuernummer_input .= qq|</select>\n|;
168 $main::lxdebug->leave_sub();
170 return $steuernummer_input;
174 $main::lxdebug->enter_sub();
178 # Referenz wird übergeben, hash of hash wird nicht
179 # in neues Hash kopiert, sondern direkt über die Referenz verändert
180 # Prototyp für diese Konstruktion
182 my ($self, $land, $elsterFFFF, $elster_init) = @_;
185 my $FFFF = $elsterFFFF;
188 $checked = 'checked' if ($elsterFFFF eq '' and $land eq '');
190 #if ($ENV{HTTP_USER_AGENT} =~ /(mozilla|opera|w3m)/i){
191 #$terminal='mozilla';
192 #} elsif ($ENV{HTTP_USER_AGENT} =~ /(links|lynx)/i){
196 #if ( $terminal eq 'mozilla' or $terminal eq 'js' ) {
198 <script language="Javascript">
199 function update_auswahl()
201 var elsterBLAuswahl = document.verzeichnis.elsterland_new;
202 var elsterFAAuswahl = document.verzeichnis.elsterFFFF_new;
204 elsterFAAuswahl.options.length = 0; // dropdown aufräumen
207 foreach $elster_land (sort keys %$elster_init) {
209 if (elsterBLAuswahl.options[elsterBLAuswahl.selectedIndex].
210 value == "$elster_land")
214 my %elster_land_fa = ();
216 for $FFFF (keys %{ $elster_init->{$elster_land} }) {
217 $elster_land_fa{$FFFF} = $elster_init->{$elster_land}->{$FFFF}->[0];
219 foreach $ffff (sort { $elster_land_fa{$a} cmp $elster_land_fa{$b} }
220 keys(%elster_land_fa)
223 elsterFAAuswahl.options[$j] = new Option("$elster_land_fa{$ffff} ($ffff)","$ffff");|;
239 <select size="1" name="elsterland_new" onchange="update_auswahl()">|;
241 $fa_auswahl .= qq|<option value="Auswahl" $checked>hier auswählen...</option>\n|;
243 foreach $elster_land (sort keys %$elster_init) {
245 <option value="$elster_land"|;
246 if ($elster_land eq $land and $checked eq '') {
247 $fa_auswahl .= qq| selected|;
249 $fa_auswahl .= qq|>$elster_land</option>
257 my $elster_land = '';
258 $elster_land = ($land ne '') ? $land : '';
259 %elster_land_fa = ();
260 for $FFFF (keys %{ $elster_init->{$elster_land} }) {
261 $elster_land_fa{$FFFF} = $elster_init->{$elster_land}->{$FFFF}->[0];
269 <select size="1" name="elsterFFFF_new">|;
270 if ($elsterFFFF eq '') {
271 $fa_auswahl .= qq|<option value="Auswahl" $checked>hier auswählen...</option>|;
273 foreach $ffff (sort { $elster_land_fa{$a} cmp $elster_land_fa{$b} }
274 keys(%elster_land_fa)
278 <option value="$ffff"|;
279 if ($ffff eq $elsterFFFF and $checked eq '') {
280 $fa_auswahl .= qq| selected|;
282 $fa_auswahl .= qq|>$elster_land_fa{$ffff} ($ffff)</option>|;
291 $main::lxdebug->leave_sub();
296 $main::lxdebug->enter_sub();
300 if ($ENV{HTTP_USER_AGENT}) {
303 print qq|<body><h2 class=info>Hinweis</h2>
309 <input type=button value="zurück" onClick="history.go(-1)">
317 if ($form->{error_function}) {
318 &{ $form->{error_function} }($msg);
320 die "Hinweis: $msg\n";
324 $main::lxdebug->leave_sub();
328 $main::lxdebug->enter_sub();
331 # soll mal eine Erinnerungsfunktion für USTVA Abgaben werden, die automatisch
332 # den Termin der nächsten USTVA anzeigt.
335 my ($today, $FA_dauerfrist, $FA_voranmeld) = @_;
340 $today =~ /(\d\d\d\d)(\d\d)(\d\d)/;
346 $yymmdd = "$year$month$day" * 1;
347 $mmdd = "$month$day" * 1;
351 #$ical = '...vcal format';
353 #if ($FA_voranmeld eq 'month'){
355 %liste = ("0110" => 'December',
357 "0310" => 'February',
364 "1010" => 'September',
366 "1210" => 'November');
370 $month += 1 if ($day > 10);
371 $month = sprintf("%02d", $month);
372 $stichtag = $year . $month . "10";
373 $ust_va = $month . "10";
375 foreach $date (%liste) {
376 $ust_va = $liste{$date} if ($date eq $stichtag);
379 #} elsif ($FA_voranmeld eq 'quarter'){
384 #@stichtag = ('10.04.2004', '10.05.2004');
386 #@liste = ['0110', '0210', '0310', '0410', '0510', '0610', '0710', '0810', '0910',
387 # '1010', '1110', '1210', ];
389 #foreach $key (@liste){
390 # #if ($ddmm < ('0110' * 1));
392 # $stichtag = $liste[$key - 1] if ($ddmm > $key);
396 #$stichtag =~ /([\d]\d)(\d\d)$/
397 #$stichtag = "$1.$2.$yy"
399 $main::lxdebug->leave_sub();
400 return ($stichtag, $description, $tage_bis, $ical);
403 sub query_finanzamt {
404 $main::lxdebug->enter_sub();
406 my ($self, $myconfig, $form) = @_;
408 my $dbh = $form->dbconnect($myconfig) or $self->error(DBI->errstr);
410 #Test, if table finanzamt exist
411 my $table = 'finanzamt';
412 my $filename = "sql/$table.sql";
414 my $tst = $dbh->prepare("SELECT * FROM $table");
418 #There is no table, read the table from sql/finanzamt.sql
419 print qq|<p>Bitte warten, Tabelle $table wird einmalig in Datenbank:
420 $myconfig->{dbname} als Benutzer: $myconfig->{dbuser} hinzugefügt...</p>|;
421 process_query($form, $dbh, $filename) || $self->error(DBI->errstr);
423 #execute second last call
424 my $dbh = $form->dbconnect($myconfig) or $self->error(DBI->errstr);
441 'FA_PLZ_Grosskunden', # 9
442 'FA_PLZ_Postfach', # 10
445 'FA_Kontonummer_1', # 13
446 'FA_Bankbezeichnung_1', # 14
447 #'FA_BankIBAN_1', # 15
448 #'FA_BankBIC_1', # 16
449 #'FA_BankInhaber_BUFA_Nr_1', # 17
451 'FA_Kontonummer_2', # 19
452 'FA_Bankbezeichnung_2', # 20
453 #'FA_BankIBAN_2', # 21
454 #'FA_BankBIC_2', # 22
455 #'FA_BankInhaber_BUFA_Nr_2', # 23
456 'FA_Oeffnungszeiten', # 24
459 #'FA_zustaendige_Hauptstelle_BUFA_Nr', # 27
460 #'FA_zustaendige_vorgesetzte_Finanzbehoerde' # 28
463 my $field = join(', ', @vars);
465 my $query = "SELECT $field FROM finanzamt ORDER BY FA_Land_nr";
466 my $sth = $dbh->prepare($query) or $self->error($dbh->errstr);
467 $sth->execute || $form->dberror($query);
468 my $array_ref = $sth->fetchall_arrayref();
470 foreach my $row (@$array_ref) {
471 my $FA_finanzamt = $row;
472 $land = 'Schleswig Holstein' if (@$FA_finanzamt[0] eq '1');
473 $land = 'Hamburg' if (@$FA_finanzamt[0] eq '2');
474 $land = 'Niedersachsen' if (@$FA_finanzamt[0] eq '3');
475 $land = 'Bremen' if (@$FA_finanzamt[0] eq '4');
476 $land = 'Nordrhein Westfalen' if (@$FA_finanzamt[0] eq '5');
477 $land = 'Hessen' if (@$FA_finanzamt[0] eq '6');
478 $land = 'Rheinland Pfalz' if (@$FA_finanzamt[0] eq '7');
479 $land = 'Baden Würtemberg' if (@$FA_finanzamt[0] eq '8');
480 $land = 'Bayern' if (@$FA_finanzamt[0] eq '9');
481 $land = 'Saarland' if (@$FA_finanzamt[0] eq '10');
482 $land = 'Berlin' if (@$FA_finanzamt[0] eq '11');
483 $land = 'Brandenburg' if (@$FA_finanzamt[0] eq '12');
484 $land = 'Mecklenburg Vorpommern' if (@$FA_finanzamt[0] eq '13');
485 $land = 'Sachsen' if (@$FA_finanzamt[0] eq '14');
486 $land = 'Sachsen Anhalt' if (@$FA_finanzamt[0] eq '15');
487 $land = 'Thüringen' if (@$FA_finanzamt[0] eq '16');
489 my $ffff = @$FA_finanzamt[1];
492 $rec->{$land} = $ffff;
497 $finanzamt{$land}{$ffff} = [@$FA_finanzamt];
503 $main::lxdebug->leave_sub();
509 $main::lxdebug->enter_sub();
511 # Copyright D. Simander -> SL::Form under Gnu GPL.
512 my ($form, $dbh, $filename) = @_;
514 # return unless (-f $filename);
516 open(FH, "$filename") or $form->error("$filename : $!\n");
523 # Remove DOS and Unix style line endings.
526 # don't add comments or empty lines
527 next if /^(--.*|\s+)$/;
529 for (my $i = 0; $i < length($_); $i++) {
530 my $char = substr($_, $i, 1);
532 # Are we inside a string?
534 if ($char eq $quote_chars[-1]) {
540 if (($char eq "'") || ($char eq "\"")) {
541 push(@quote_chars, $char);
543 } elsif ($char eq ";") {
545 # Query is complete. Send it.
547 $sth = $dbh->prepare($query);
548 $sth->execute || $form->dberror($query);
562 $main::lxdebug->leave_sub();
566 $main::lxdebug->enter_sub();
568 my ($self, $myconfig, $form) = @_;
570 # connect to database
571 my $dbh = $form->dbconnect($myconfig);
574 my $category = "pos_ustva";
575 my @category_cent = qw(
576 511 861 36 80 971 931 98 96 53 74
577 85 65 66 61 62 67 63 64 59 69
578 39 83 811 891 Z43 Z45 Z53 Z62 Z65 Z67
581 my @category_euro = qw(
588 $form->{decimalplaces} *= 1;
590 foreach $item (@category_cent) {
591 $form->{"$item"} = 0;
593 foreach $item (@category_euro) {
594 $form->{"$item"} = 0;
597 &get_accounts_ustva($dbh, $last_period, $form->{fromdate}, $form->{todate},
602 # Umordnen der Kennziffern
603 if ( $form->{year} < 2007) {
604 $form->{35} += $form->{81};
605 $form->{36} += $form->{811};
606 $form->{95} += $form->{89};
607 $form->{98} += $form->{891};
608 map { delete $form->{$_} } qw(81 811 89 891);
610 $form->{35} += $form->{51};
611 $form->{36} += $form->{511};
612 $form->{95} += $form->{97};
613 $form->{98} += $form->{971};
614 map { delete $form->{$_} } qw(51 511 97 971);
619 # Berechnung der USTVA Formularfelder laut Bogen 207
622 $form->{"51r"} = $form->{"511"};
623 $form->{"86r"} = $form->{"861"};
624 $form->{"97r"} = $form->{"971"};
625 $form->{"93r"} = $form->{"931"};
627 $form->{"Z43"} = $form->{"511"} + $form->{"811"} + $form->{"861"}
628 + $form->{"36"} + $form->{"80"} + $form->{"971"}
629 + $form->{"891"} + $form->{"931"} + $form->{"96"}
632 $form->{"Z45"} = $form->{"Z43"};
634 $form->{"Z53"} = $form->{"Z45"} + $form->{"53"} + $form->{"74"}
635 + $form->{"85"} + $form->{"65"};
637 $form->{"Z62"} = $form->{"Z43"} - $form->{"66"} - $form->{"61"}
638 - $form->{"62"} - $form->{"67"} - $form->{"63"}
639 - $form->{"64"} - $form->{"59"};
641 $form->{"Z65"} = $form->{"Z62"} - $form->{"69"};
642 $form->{"83"} = $form->{"Z65"} - $form->{"39"};
646 $main::lxdebug->leave_sub();
649 sub get_accounts_ustva {
650 $main::lxdebug->enter_sub();
652 my ($dbh, $last_period, $fromdate, $todate, $form, $category) = @_;
654 my ($null, $department_id) = split /--/, $form->{department};
667 my $gltaxkey_where = "(tk.pos_ustva>=59 AND tk.pos_ustva<=66)";
670 if ($form->{method} eq 'cash') {
671 $subwhere .= " AND transdate >= '$fromdate'";
672 $glwhere = " AND ac.transdate >= '$fromdate'";
673 $ARwhere .= " AND acc.transdate >= '$fromdate'";
675 $APwhere .= " AND AP.transdate >= '$fromdate'";
676 $where .= " AND ac.transdate >= '$fromdate'";
680 $where .= " AND ac.transdate <= '$todate'";
681 $ARwhere .= " AND acc.transdate <= '$todate'";
682 $subwhere .= " AND transdate <= '$todate'";
683 $APwhere .= " AND AP.transdate <= '$todate'";
686 if ($department_id) {
688 JOIN department t ON (a.department_id = t.id)
691 AND t.id = $department_id
695 if ($form->{project_id}) {
697 AND ac.project_id = $form->{project_id}
700 ############################################
701 # Method eq 'cash' = IST Versteuerung
702 ############################################
703 # Betrifft nur die eingenommene Umsatzsteuer
705 ############################################
707 if ($form->{method} eq 'cash') {
711 -- USTVA IST-Versteuerung
713 -- Alle tatsaechlichen _Zahlungseingaenge_
714 -- im Voranmeldezeitraum erfassen
715 -- (Teilzahlungen werden prozentual auf verschiedene Steuern aufgeteilt)
717 -- Bezahlt / Rechnungssumme
719 SELECT SUM(acc.amount)
721 INNER JOIN chart c ON (acc.chart_id = c.id
722 AND c.link like '%AR_paid%')
726 AND acc.trans_id = ac.trans_id
730 SELECT amount FROM ar WHERE id = ac.trans_id
735 LEFT JOIN chart c ON (c.id = ac.chart_id)
736 LEFT JOIN ar ON (ar.id = ac.trans_id)
737 LEFT JOIN taxkeys tk ON (
739 SELECT id FROM taxkeys
740 WHERE chart_id = ac.chart_id
741 -- AND taxkey_id = ac.taxkey
742 AND startdate <= COALESCE(ar.deliverydate,ar.transdate)
743 ORDER BY startdate DESC LIMIT 1
748 -- Here no where, please. All Transactions ever should be
749 -- testet if they are paied in the USTVA report period.
750 GROUP BY tk.pos_ustva
753 } elsif ($form->{method} eq 'accrual') {
754 #########################################
755 # Method eq 'accrual' = Soll Versteuerung
756 #########################################
758 if ($department_id) {
760 JOIN dpt_trans t ON (t.trans_id = ac.trans_id)
763 AND t.department_id = $department_id
769 -- Alle Einnahmen AR und pos_ustva erfassen
771 - sum(ac.amount) AS amount,
774 JOIN chart c ON (c.id = ac.chart_id)
775 JOIN ar ON (ar.id = ac.trans_id)
778 SELECT id FROM taxkeys
779 WHERE chart_id = ac.chart_id
780 AND startdate <= COALESCE(ar.deliverydate,ar.transdate)
781 ORDER BY startdate DESC LIMIT 1
789 GROUP BY tk.pos_ustva
794 $self->error("Unknown tax method: $form->{method}")
798 #########################################
799 # Ausgaben und Gl Buchungen sind gleich
800 # für Ist- und Soll-Versteuerung
801 #########################################
803 UNION -- alle Ausgaben AP erfassen
806 sum(ac.amount) AS amount,
809 JOIN AP ON (AP.id = ac.trans_id )
810 JOIN chart c ON (c.id = ac.chart_id)
811 LEFT JOIN taxkeys tk ON (
813 SELECT id FROM taxkeys
814 WHERE chart_id=ac.chart_id
815 --AND taxkey_id=ac.taxkey
816 AND startdate <= COALESCE(AP.transdate)
817 ORDER BY startdate DESC LIMIT 1
825 GROUP BY tk.pos_ustva
827 UNION -- Einnahmen direkter gl Buchungen erfassen
830 ( - ac.amount) AS amount,
833 JOIN chart c ON (c.id = ac.chart_id)
834 JOIN gl a ON (a.id = ac.trans_id)
835 LEFT JOIN taxkeys tk ON (
837 SELECT id FROM taxkeys
838 WHERE chart_id=ac.chart_id
839 --AND taxkey_id=ac.taxkey
840 AND NOT $gltaxkey_where
841 AND startdate <= COALESCE(ac.transdate)
842 ORDER BY startdate DESC LIMIT 1
851 GROUP BY tk.pos_ustva
854 UNION -- Ausgaben direkter gl Buchungen erfassen
857 (ac.amount) AS amount,
860 JOIN chart c ON (c.id = ac.chart_id)
861 JOIN gl a ON (a.id = ac.trans_id)
862 LEFT JOIN taxkeys tk ON (
864 SELECT id FROM taxkeys
865 WHERE chart_id=ac.chart_id
866 --AND taxkey_id=ac.taxkey
868 AND startdate <= COALESCE(ac.transdate)
869 ORDER BY startdate DESC LIMIT 1
878 GROUP BY tk.pos_ustva
886 # Show all $query in Debuglevel LXDebug::QUERY
887 $callingdetails = (caller (0))[3];
888 $main::lxdebug->message(LXDebug::QUERY, "$callingdetails \$query=\n $query");
890 my $sth = $dbh->prepare($query);
892 $sth->execute || $form->dberror($query);
894 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
896 $ref->{amount} *= -1;
897 if ($category eq "pos_bwa") {
899 $form->{ $ref->{$category} }{kumm} += $ref->{amount};
901 $form->{ $ref->{$category} }{jetzt} += $ref->{amount};
904 $form->{ $ref->{$category} } += $ref->{amount};
910 $main::lxdebug->leave_sub();