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
719 AND c.link like '%AR_paid%')
723 AND acc.trans_id = ac.trans_id
727 select amount from ar where id = ac.trans_id
732 LEFT JOIN chart c ON (c.id = ac.chart_id)
733 LEFT JOIN ar ON (ar.id = ac.trans_id)
734 LEFT JOIN taxkeys tk ON (
736 SELECT id FROM taxkeys
737 WHERE chart_id = ac.chart_id
738 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,
755 JOIN AP ON (AP.id = ac.trans_id )
756 JOIN chart c ON (c.id = ac.chart_id)
757 LEFT JOIN taxkeys tk ON (
759 SELECT id FROM taxkeys
760 WHERE chart_id=ac.chart_id
761 AND taxkey_id=ac.taxkey
762 AND startdate <= COALESCE(AP.transdate)
763 ORDER BY startdate DESC LIMIT 1
771 GROUP BY tk.pos_ustva
773 UNION -- alle Ausgaben und Einnahmen direkter gl Buchungen erfassen
776 ( - ac.amount) AS amount,
779 JOIN chart c ON (c.id = ac.chart_id)
780 JOIN gl a ON (a.id = ac.trans_id)
781 LEFT JOIN taxkeys tk ON (
783 SELECT id FROM taxkeys
784 WHERE chart_id=ac.chart_id
785 AND taxkey_id=ac.taxkey
787 AND startdate <= COALESCE(ac.transdate)
788 ORDER BY startdate DESC LIMIT 1
796 GROUP BY tk.pos_ustva
800 #########################################
801 # Method eq 'accrual' = Soll Versteuerung
802 #########################################
804 if ($department_id) {
806 JOIN dpt_trans t ON (t.trans_id = ac.trans_id)
809 AND t.department_id = $department_id
816 CASE WHEN c.link LIKE '%AR%' THEN ac.amount * -1
817 WHEN c.link LIKE '%AP%' THEN ac.amount * 1
819 ) AS amount, c.$category
821 JOIN chart c ON (c.id = ac.chart_id)
834 # Show all $query in Debuglevel LXDebug::QUERY
835 $callingdetails = (caller (0))[3];
836 $main::lxdebug->message(LXDebug::QUERY, "$callingdetails \$query=\n $query");
838 my $sth = $dbh->prepare($query);
839 $sth->execute || $form->dberror($query);
841 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
843 $ref->{amount} *= -1;
844 if ($category eq "pos_bwa") {
846 $form->{ $ref->{$category} }{kumm} += $ref->{amount};
848 $form->{ $ref->{$category} }{jetzt} += $ref->{amount};
851 $form->{ $ref->{$category} } += $ref->{amount};
856 $main::lxdebug->leave_sub();