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 ($elsterland, $elsterFFFF, $steuernummer) = @_;
68 $elster_land = $elsterland;
69 $elster_FFFF = $elsterFFFF;
70 $steuernummer = '0000000000' if ($steuernummer eq '');
72 # $steuernummer formatieren (nur Zahlen) -> $stnr
73 $stnr = $steuernummer;
76 #Pattern description Elstersteuernummer
77 my %elster_STNRformat = (
78 'Mecklenburg Vorpommern' => 'FFF/BBB/UUUUP', # '/' 3
79 'Hessen' => '0FF BBB UUUUP', # ' ' 3
80 'Nordrhein Westfalen' => 'FFF/BBBB/UUUP', # '/' 3
81 'Schleswig Holstein' => 'FF BBB UUUUP', # ' ' 2
82 'Berlin' => 'FF/BBB/UUUUP', # '/' 3
83 'Thüringen' => 'FFF/BBB/UUUUP', # '/' 3
84 'Sachsen' => 'FFF/BBB/UUUUP', # '/' 3
85 'Hamburg' => 'FF/BBB/UUUUP', # '/' 3
86 'Baden Würtemberg' => 'FF/BBB/UUUUP', # '/' 2
87 'Sachsen Anhalt' => 'FFF/BBB/UUUUP', # '/' 3
88 'Saarland' => 'FFF/BBB/UUUUP', # '/' 3
89 'Bremen' => 'FF BBB UUUUP', # ' ' 3
90 'Bayern' => 'FFF/BBB/UUUUP', # '/' 3
91 'Rheinland Pfalz' => 'FF/BBB/UUUU/P', # '/' 4
92 'Niedersachsen' => 'FF/BBB/UUUUP', # '/' 3
93 'Brandenburg' => 'FFF/BBB/UUUUP', # '/' 3
97 $elster_pattern = $elster_STNRformat{$elster_land};
98 my @elster_pattern = split(' ', $elster_pattern);
99 my $delimiter = ' ';
100 my $patterncount = @elster_pattern;
101 if ($patterncount < 2) {
102 @elster_pattern = ();
103 @elster_pattern = split('/', $elster_pattern);
105 $patterncount = @elster_pattern;
108 # no we have an array of patternparts and a delimiter
109 # create the first automated and fixed part and delimiter
111 print qq|<b><font size="+1">|;
114 $elster_pattern[0] eq 'FFF' && do {
115 $part = substr($elster_FFFF, 1, 4);
119 $elster_pattern[0] eq '0FF' && do {
120 $part = '0' . substr($elster_FFFF, 2, 4);
124 $elster_pattern[0] eq 'FF' && do {
125 $part = substr($elster_FFFF, 2, 4);
135 #now the rest of the Steuernummer ...
136 print qq|</b></font>|;
138 <input type=hidden name="elster_pattern" value="$elster_pattern">
139 <input type=hidden name="patterncount" value="$patterncount">
140 <input type=hidden name="patternlength" value="$patterncount">
141 <input type=hidden name="delimiter" value="$delimiter">
142 <input type=hidden name="part" value="$part">
149 for ($h = 1; $h < $patterncount; $h++) {
150 print qq| $delimiter \n|;
151 for ($i = 1; $i <= length($elster_pattern[$h]); $i++) {
152 print qq|<select name="part_$h\_$i">\n|;
154 for ($j = 0; $j <= 9; $j++) {
155 print qq| <option value="$j"|;
156 if ($steuernummer ne '') {
157 if ($j eq substr($stnr, length($part) + $k, 1)) {
161 print qq|>$j</option>\n|;
164 print qq|</select>\n|;
167 $main::lxdebug->leave_sub();
171 $main::lxdebug->enter_sub();
175 # Referenz wird übergeben, hash of hash wird nicht
176 # in neues Hash kopiert, sondern direkt über die Referenz verändert
177 # Prototyp für diese Konstruktion
179 my ($land, $elsterFFFF, $elster_init) =
180 @_; #Referenz auf Hash von Hash übergeben
182 my $FFFF = $elsterFFFF;
185 $checked = 'checked' if ($elsterFFFF eq '' and $land eq '');
187 #if ($ENV{HTTP_USER_AGENT} =~ /(mozilla|opera|w3m)/i){
188 #$terminal='mozilla';
189 #} elsif ($ENV{HTTP_USER_AGENT} =~ /(links|lynx)/i){
193 #if ( $terminal eq 'mozilla' or $terminal eq 'js' ) {
196 <script language="Javascript">
197 function update_auswahl()
199 var elsterBLAuswahl = document.verzeichnis.elsterland_new;
200 var elsterFAAuswahl = document.verzeichnis.elsterFFFF_new;
202 elsterFAAuswahl.options.length = 0; // dropdown aufräumen
205 foreach $elster_land (sort keys %$elster_init) {
207 if (elsterBLAuswahl.options[elsterBLAuswahl.selectedIndex].
208 value == "$elster_land")
212 my %elster_land_fa = ();
214 for $FFFF (keys %{ $elster_init->{$elster_land} }) {
215 $elster_land_fa{$FFFF} = $elster_init->{$elster_land}->{$FFFF}->[0];
217 foreach $ffff (sort { $elster_land_fa{$a} cmp $elster_land_fa{$b} }
218 keys(%elster_land_fa)) {
220 elsterFAAuswahl.options[$j] = new Option("$elster_land_fa{$ffff} ($ffff)","$ffff");|;
236 <select size="1" name="elsterland_new" onchange="update_auswahl()">|;
238 print qq|<option value="Auswahl" $checked>hier auswählen...</option>\n|;
240 foreach $elster_land (sort keys %$elster_init) {
242 <option value="$elster_land"|;
243 if ($elster_land eq $land and $checked eq '') {
246 print qq|>$elster_land</option>
254 my $elster_land = '';
255 $elster_land = ($land ne '') ? $land : '';
256 %elster_land_fa = ();
257 for $FFFF (keys %{ $elster_init->{$elster_land} }) {
258 $elster_land_fa{$FFFF} = $elster_init->{$elster_land}->{$FFFF}->[0];
266 <select size="1" name="elsterFFFF_new">|;
267 if ($elsterFFFF eq '') {
268 print qq|<option value="Auswahl" $checked>hier auswählen...</option>|;
270 foreach $ffff (sort { $elster_land_fa{$a} cmp $elster_land_fa{$b} }
271 keys(%elster_land_fa)) {
274 <option value="$ffff"|;
275 if ($ffff eq $elsterFFFF and $checked eq '') {
278 print qq|>$elster_land_fa{$ffff} ($ffff)</option>|;
287 $main::lxdebug->leave_sub();
291 $main::lxdebug->enter_sub();
295 if ($ENV{HTTP_USER_AGENT}) {
298 print qq|<body><h2 class=info>Hinweis</h2>
304 <input type=button value="zurück" onClick="history.go(-1)">
312 if ($form->{error_function}) {
313 &{ $form->{error_function} }($msg);
315 die "Hinweis: $msg\n";
319 $main::lxdebug->leave_sub();
323 $main::lxdebug->enter_sub();
326 # soll mal eine Erinnerungsfunktion für USTVA Abgaben werden, die automatisch
327 # den Termin der nächsten USTVA anzeigt.
330 my ($today, $FA_dauerfrist, $FA_voranmeld) = @_;
335 $today =~ /(\d\d\d\d)(\d\d)(\d\d)/;
341 $yymmdd = "$year$month$day" * 1;
342 $mmdd = "$month$day" * 1;
346 #$ical = '...vcal format';
348 #if ($FA_voranmeld eq 'month'){
350 %liste = ("0110" => 'December',
352 "0310" => 'February',
359 "1010" => 'September',
361 "1210" => 'November');
365 $month += 1 if ($day > 10);
366 $month = sprintf("%02d", $month);
367 $stichtag = $year . $month . "10";
368 $ust_va = $month . "10";
370 foreach $date (%liste) {
371 $ust_va = $liste{$date} if ($date eq $stichtag);
374 #} elsif ($FA_voranmeld eq 'quarter'){
379 #@stichtag = ('10.04.2004', '10.05.2004');
381 #@liste = ['0110', '0210', '0310', '0410', '0510', '0610', '0710', '0810', '0910',
382 # '1010', '1110', '1210', ];
384 #foreach $key (@liste){
385 # #if ($ddmm < ('0110' * 1));
387 # $stichtag = $liste[$key - 1] if ($ddmm > $key);
391 #$stichtag =~ /([\d]\d)(\d\d)$/
392 #$stichtag = "$1.$2.$yy"
394 $main::lxdebug->leave_sub();
395 return ($stichtag, $description, $tage_bis, $ical);
398 sub query_finanzamt {
399 $main::lxdebug->enter_sub();
401 my ($myconfig, $form) = @_;
402 my $dbh = $form->dbconnect($myconfig) or $self->error(DBI->errstr);
404 #Test, if table finanzamt exist
405 my $table = 'finanzamt';
406 my $filename = "sql/$table.sql";
408 my $tst = $dbh->prepare("SELECT * FROM $table");
412 #There is no table, read the table from sql/finanzamt.sql
413 print qq|<p>Bitte warten, Tabelle $table wird einmalig in Datenbank:
414 $myconfig->{dbname} als Benutzer: $myconfig->{dbuser} hinzugefügt...</p>|;
415 process_query($form, $dbh, $filename) || $self->error(DBI->errstr);
417 #execute second last call
418 my $dbh = $form->dbconnect($myconfig) or $self->error(DBI->errstr);
435 'FA_PLZ_Grosskunden', # 9
436 'FA_PLZ_Postfach', # 10
439 'FA_Kontonummer_1', # 13
440 'FA_Bankbezeichnung_1', # 14
441 #'FA_BankIBAN_1', # 15
442 #'FA_BankBIC_1', # 16
443 #'FA_BankInhaber_BUFA_Nr_1', # 17
445 'FA_Kontonummer_2', # 19
446 'FA_Bankbezeichnung_2', # 20
447 #'FA_BankIBAN_2', # 21
448 #'FA_BankBIC_2', # 22
449 #'FA_BankInhaber_BUFA_Nr_2', # 23
450 'FA_Oeffnungszeiten', # 24
453 #'FA_zustaendige_Hauptstelle_BUFA_Nr', # 27
454 #'FA_zustaendige_vorgesetzte_Finanzbehoerde' # 28
457 my $field = join(', ', @vars);
459 my $query = "SELECT $field FROM finanzamt ORDER BY FA_Land_nr";
460 my $sth = $dbh->prepare($query) or $self->error($dbh->errstr);
461 $sth->execute || $form->dberror($query);
462 my $array_ref = $sth->fetchall_arrayref();
464 foreach my $row (@$array_ref) {
465 my $FA_finanzamt = $row;
466 $land = 'Schleswig Holstein' if (@$FA_finanzamt[0] eq '1');
467 $land = 'Hamburg' if (@$FA_finanzamt[0] eq '2');
468 $land = 'Niedersachsen' if (@$FA_finanzamt[0] eq '3');
469 $land = 'Bremen' if (@$FA_finanzamt[0] eq '4');
470 $land = 'Nordrhein Westfalen' if (@$FA_finanzamt[0] eq '5');
471 $land = 'Hessen' if (@$FA_finanzamt[0] eq '6');
472 $land = 'Rheinland Pfalz' if (@$FA_finanzamt[0] eq '7');
473 $land = 'Baden Würtemberg' if (@$FA_finanzamt[0] eq '8');
474 $land = 'Bayern' if (@$FA_finanzamt[0] eq '9');
475 $land = 'Saarland' if (@$FA_finanzamt[0] eq '10');
476 $land = 'Berlin' if (@$FA_finanzamt[0] eq '11');
477 $land = 'Brandenburg' if (@$FA_finanzamt[0] eq '12');
478 $land = 'Mecklenburg Vorpommern' if (@$FA_finanzamt[0] eq '13');
479 $land = 'Sachsen' if (@$FA_finanzamt[0] eq '14');
480 $land = 'Sachsen Anhalt' if (@$FA_finanzamt[0] eq '15');
481 $land = 'Thüringen' if (@$FA_finanzamt[0] eq '16');
483 my $ffff = @$FA_finanzamt[1];
486 $rec->{$land} = $ffff;
491 $finanzamt{$land}{$ffff} = [@$FA_finanzamt];
497 $main::lxdebug->leave_sub();
503 $main::lxdebug->enter_sub();
505 # Copyright D. Simander -> SL::Form under Gnu GPL.
506 my ($form, $dbh, $filename) = @_;
508 # return unless (-f $filename);
510 open(FH, "$filename") or $form->error("$filename : $!\n");
517 # Remove DOS and Unix style line endings.
520 # don't add comments or empty lines
521 next if /^(--.*|\s+)$/;
523 for (my $i = 0; $i < length($_); $i++) {
524 my $char = substr($_, $i, 1);
526 # Are we inside a string?
528 if ($char eq $quote_chars[-1]) {
534 if (($char eq "'") || ($char eq "\"")) {
535 push(@quote_chars, $char);
537 } elsif ($char eq ";") {
539 # Query is complete. Send it.
541 $sth = $dbh->prepare($query);
542 $sth->execute || $form->dberror($query);
556 $main::lxdebug->leave_sub();