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 und abfangen von Fehlern
607 if ( $form->{year} < 2007) {
608 $form->{35} += $form->{81};
609 $form->{36} += $form->{811};
610 $form->{95} += $form->{89};
611 $form->{98} += $form->{891};
612 map { delete $form->{$_} } qw(81 811 89 891);
614 $form->{35} += $form->{51};
615 $form->{36} += $form->{511};
616 $form->{95} += $form->{97};
617 $form->{98} += $form->{971};
618 map { delete $form->{$_} } qw(51 511 97 971);
622 # Berechnung der USTVA Formularfelder laut Bogen 207
625 $form->{"51r"} = $form->{"511"};
626 $form->{"86r"} = $form->{"861"};
627 $form->{"97r"} = $form->{"971"};
628 $form->{"93r"} = $form->{"931"};
630 $form->{"Z43"} = $form->{"511"} + $form->{"811"} + $form->{"861"}
631 + $form->{"36"} + $form->{"80"} + $form->{"971"}
632 + $form->{"891"} + $form->{"931"} + $form->{"96"}
635 $form->{"Z45"} = $form->{"Z43"};
637 $form->{"Z53"} = $form->{"Z45"} + $form->{"53"} + $form->{"74"}
638 + $form->{"85"} + $form->{"65"};
640 $form->{"Z62"} = $form->{"Z43"} - $form->{"66"} - $form->{"61"}
641 - $form->{"62"} - $form->{"67"} - $form->{"63"}
642 - $form->{"64"} - $form->{"59"};
644 $form->{"Z65"} = $form->{"Z62"} - $form->{"69"};
645 $form->{"83"} = $form->{"Z65"} - $form->{"39"};
649 $main::lxdebug->leave_sub();
652 sub get_accounts_ustva {
653 $main::lxdebug->enter_sub();
655 my ($dbh, $last_period, $fromdate, $todate, $form, $category) = @_;
657 my ($null, $department_id) = split /--/, $form->{department};
672 if ($form->{method} eq 'cash') {
673 $subwhere .= " AND transdate >= '$fromdate'";
674 $glwhere = " AND ac.transdate >= '$fromdate'";
675 $ARwhere .= " AND acc.transdate >= '$fromdate'";
676 $APwhere .= " AND AP.transdate >= '$fromdate'";
678 $where .= " AND ac.transdate >= '$fromdate'";
682 $where .= " AND ac.transdate <= '$todate'";
683 $ARwhere .= " AND acc.transdate <= '$todate'";
684 $subwhere .= " AND transdate <= '$todate'";
685 $APwhere .= " AND AP.transdate <= '$todate'";
688 if ($department_id) {
690 JOIN department t ON (a.department_id = t.id)
693 AND t.id = $department_id
697 if ($form->{project_id}) {
699 AND ac.project_id = $form->{project_id}
702 #########################################
703 # Method eq 'cash' = IST Versteuerung
704 #########################################
706 if ($form->{method} eq 'cash') {
710 -- Alle tatsaechlichen Zahlungseingaenge
711 -- im Voranmeldezeitraum erfassen
712 -- (Teilzahlungen werden prozentual auf verschiedene Steuern aufgeteilt)
714 -- Bezahlt / Rechnungssumme
716 SELECT SUM(acc.amount)
718 INNER JOIN chart c ON (acc.chart_id = c.id AND c.link like '%AR_paid%')
722 AND acc.trans_id = ac.trans_id
726 select amount from ar where id = ac.trans_id
731 LEFT JOIN chart c ON (c.id = ac.chart_id)
732 LEFT JOIN ar ON (ar.id = ac.trans_id)
733 LEFT JOIN taxkeys tk ON (
735 SELECT id FROM taxkeys
736 WHERE chart_id=ac.chart_id
737 AND taxkey_id=ac.taxkey
739 AND startdate <= COALESCE(ar.deliverydate, ar.transdate)
740 ORDER BY startdate DESC LIMIT 1
745 -- Here no where, please. All Transactions ever should be
746 -- testet if they are paied in the USTVA report period.
747 GROUP BY tk.pos_ustva
749 UNION -- alle Ausgaben AP erfassen
752 sum(ac.amount) AS amount, pos_ustva
754 JOIN AP ON (AP.id = ac.trans_id )
755 JOIN chart c ON (c.id = ac.chart_id AND pos_ustva NOT LIKE '')
763 UNION -- alle Ausgaben und Einnahmen direkter gl Buchungen erfassen
767 CASE WHEN c.link LIKE '%AR%' THEN ac.amount * -1
768 WHEN c.link LIKE '%AP%' THEN ac.amount * 1
770 ) AS amount, c.$category
772 JOIN chart c ON (c.id = ac.chart_id)
773 JOIN gl a ON (a.id = ac.trans_id)
777 AND NOT (c.link = 'AR' OR c.link = 'AP')
783 #########################################
784 # Method eq 'accrual' = Soll Versteuerung
785 #########################################
787 if ($department_id) {
789 JOIN dpt_trans t ON (t.trans_id = ac.trans_id)
792 AND t.department_id = $department_id
799 CASE WHEN c.link LIKE '%AR%' THEN ac.amount * -1
800 WHEN c.link LIKE '%AP%' THEN ac.amount * 1
802 ) AS amount, c.$category
804 JOIN chart c ON (c.id = ac.chart_id)
817 # Show all $query in Debuglevel LXDebug::QUERY
818 $callingdetails = (caller (0))[3];
819 $main::lxdebug->message(LXDebug::QUERY, "$callingdetails \$query=\n $query");
821 my $sth = $dbh->prepare($query);
822 $sth->execute || $form->dberror($query);
824 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
826 $ref->{amount} *= -1;
827 if ($category eq "pos_bwa") {
829 $form->{ $ref->{$category} }{kumm} += $ref->{amount};
831 $form->{ $ref->{$category} }{jetzt} += $ref->{amount};
834 $form->{ $ref->{$category} } += $ref->{amount};
839 $main::lxdebug->leave_sub();