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 report_variables {
29 # Get all positions for taxreport out of the database
30 # Needs Databaseupdate Pg-upgrade2/USTVA_abstraction.pl
32 return unless defined wantarray;
37 my $myconfig = $arg_ref->{myconfig};
38 my $form = $arg_ref->{form};
39 my $type = $arg_ref->{type}; # 'paied' || 'received' || ''
40 my $attribute = $arg_ref->{attribute}; #
41 my $dec_places = (defined $arg_ref->{dec_places}) ? $arg_ref->{dec_places}:undef;
43 my $where_type = "AND tax.report_headings.type = '$type'" if ( $type );
44 my $where_dcp = "AND tax.report_variables.dec_places = '$dec_places'" if ( defined $dec_places );
48 FROM tax.report_variables
49 LEFT JOIN tax.report_headings
50 ON (tax.report_variables.heading_id = tax.report_headings.id)
56 $main::lxdebug->message(LXDebug::QUERY, "\$query= \n $query\n");
58 my $dbh = $form->dbconnect($myconfig);
59 my $sth = $dbh->prepare($query);
61 $sth->execute() || $form->dberror($query);
65 while ( my $row_ref = $sth->fetchrow_arrayref() ) {
66 push @positions, @$row_ref; # Copy the array contents
77 sub create_steuernummer {
78 $main::lxdebug->enter_sub();
80 $part = $form->{part};
81 $patterncount = $form->{patterncount};
82 $delimiter = $form->{delimiter};
83 $elster_pattern = $form->{elster_pattern};
85 # rebuild steuernummer and elstersteuernummer
86 # es gibt eine gespeicherte steuernummer $form->{steuernummer}
87 # und die parts und delimiter
92 $steuernummer_new = $part;
93 $elstersteuernummer_new = $elster_FFFF;
94 $elstersteuernummer_new .= '0';
96 for ($h = 1; $h < $patterncount; $h++) {
97 $steuernummer_new .= qq|$delimiter|;
98 for ($i = 1; $i <= length($elster_pattern); $i++) {
99 $steuernummer_new .= $form->{"part_$h\_$i"};
100 $elstersteuernummer_new .= $form->{"part_$h\_$i"};
103 if ($form->{steuernummer} ne $steuernummer_new) {
104 $form->{steuernummer} = $steuernummer_new;
105 $form->{elstersteuernummer} = $elstersteuernummer_new;
106 $form->{steuernummer_new} = $steuernummer_new;
108 $main::lxdebug->leave_sub();
109 return ($steuernummer_new, $elstersteuernummer_new);
112 sub steuernummer_input {
113 $main::lxdebug->enter_sub();
115 my ($self, $elsterland, $elsterFFFF, $steuernummer) = @_;
117 my $steuernummer_input = '';
119 $elster_land = $elsterland;
120 $elster_FFFF = $elsterFFFF;
121 $steuernummer = '0000000000' if ($steuernummer eq '');
123 # $steuernummer formatieren (nur Zahlen) -> $stnr
124 my $stnr = $steuernummer;
127 #Pattern description Elstersteuernummer
128 my %elster_STNRformat = (
129 'Mecklenburg Vorpommern' => 'FFF/BBB/UUUUP', # '/' 3
130 'Hessen' => '0FF BBB UUUUP', # ' ' 3
131 'Nordrhein Westfalen' => 'FFF/BBBB/UUUP', # '/' 3
132 'Schleswig Holstein' => 'FF BBB UUUUP', # ' ' 2
133 'Berlin' => 'FF/BBB/UUUUP', # '/' 3
134 'Thüringen' => 'FFF/BBB/UUUUP', # '/' 3
135 'Sachsen' => 'FFF/BBB/UUUUP', # '/' 3
136 'Hamburg' => 'FF/BBB/UUUUP', # '/' 3
137 'Baden Würtemberg' => 'FF/BBB/UUUUP', # '/' 2
138 'Sachsen Anhalt' => 'FFF/BBB/UUUUP', # '/' 3
139 'Saarland' => 'FFF/BBB/UUUUP', # '/' 3
140 'Bremen' => 'FF BBB UUUUP', # ' ' 3
141 'Bayern' => 'FFF/BBB/UUUUP', # '/' 3
142 'Rheinland Pfalz' => 'FF/BBB/UUUU/P', # '/' 4
143 'Niedersachsen' => 'FF/BBB/UUUUP', # '/' 3
144 'Brandenburg' => 'FFF/BBB/UUUUP', # '/' 3
148 my $elster_pattern = $elster_STNRformat{$elster_land};
149 my @elster_pattern = split(' ', $elster_pattern);
150 my $delimiter = ' ';
151 my $patterncount = @elster_pattern;
152 if ($patterncount < 2) {
153 @elster_pattern = ();
154 @elster_pattern = split('/', $elster_pattern);
156 $patterncount = @elster_pattern;
159 # no we have an array of patternparts and a delimiter
160 # create the first automated and fixed part and delimiter
162 $steuernummer_input .= qq|<b><font size="+1">|;
165 $elster_pattern[0] eq 'FFF' && do {
166 $part = substr($elster_FFFF, 1, 4);
167 $steuernummer_input .= qq|$part|;
170 $elster_pattern[0] eq '0FF' && do {
171 $part = '0' . substr($elster_FFFF, 2, 4);
172 $steuernummer_input .= qq|$part|;
175 $elster_pattern[0] eq 'FF' && do {
176 $part = substr($elster_FFFF, 2, 4);
177 $steuernummer_input .= qq|$part|;
181 $steuernummer_input .= qq|Fehler!|;
186 #now the rest of the Steuernummer ...
187 $steuernummer_input .= qq|</b></font>|;
188 $steuernummer_input .= qq|\n
189 <input type=hidden name="elster_pattern" value="$elster_pattern">
190 <input type=hidden name="patterncount" value="$patterncount">
191 <input type=hidden name="patternlength" value="$patterncount">
192 <input type=hidden name="delimiter" value="$delimiter">
193 <input type=hidden name="part" value="$part">
198 for (my $h = 1; $h < $patterncount; $h++) {
199 $steuernummer_input .= qq| $delimiter \n|;
200 for (my $i = 1; $i <= length($elster_pattern[$h]); $i++) {
201 $steuernummer_input .= qq|<select name="part_$h\_$i">\n|;
203 for (my $j = 0; $j <= 9; $j++) {
204 $steuernummer_input .= qq| <option value="$j"|;
205 if ($steuernummer ne '') {
206 if ($j eq substr($stnr, length($part) + $k, 1)) {
207 $steuernummer_input .= qq| selected|;
210 $steuernummer_input .= qq|>$j</option>\n|;
213 $steuernummer_input .= qq|</select>\n|;
217 $main::lxdebug->leave_sub();
219 return $steuernummer_input;
223 $main::lxdebug->enter_sub();
227 # Referenz wird übergeben, hash of hash wird nicht
228 # in neues Hash kopiert, sondern direkt über die Referenz verändert
229 # Prototyp für diese Konstruktion
231 my ($self, $land, $elsterFFFF, $elster_init) = @_;
234 my $FFFF = $elsterFFFF;
237 $checked = 'checked' if ($elsterFFFF eq '' and $land eq '');
239 #if ($ENV{HTTP_USER_AGENT} =~ /(mozilla|opera|w3m)/i){
240 #$terminal='mozilla';
241 #} elsif ($ENV{HTTP_USER_AGENT} =~ /(links|lynx)/i){
245 #if ( $terminal eq 'mozilla' or $terminal eq 'js' ) {
247 <script language="Javascript">
248 function update_auswahl()
250 var elsterBLAuswahl = document.verzeichnis.elsterland_new;
251 var elsterFAAuswahl = document.verzeichnis.elsterFFFF_new;
253 elsterFAAuswahl.options.length = 0; // dropdown aufräumen
256 foreach $elster_land (sort keys %$elster_init) {
258 if (elsterBLAuswahl.options[elsterBLAuswahl.selectedIndex].
259 value == "$elster_land")
263 my %elster_land_fa = ();
265 for $FFFF (keys %{ $elster_init->{$elster_land} }) {
266 $elster_land_fa{$FFFF} = $elster_init->{$elster_land}->{$FFFF}->[0];
268 foreach $ffff (sort { $elster_land_fa{$a} cmp $elster_land_fa{$b} }
269 keys(%elster_land_fa)
272 elsterFAAuswahl.options[$j] = new Option("$elster_land_fa{$ffff} ($ffff)","$ffff");|;
288 <select size="1" name="elsterland_new" onchange="update_auswahl()">|;
290 $fa_auswahl .= qq|<option value="Auswahl" $checked>hier auswählen...</option>\n|;
292 foreach $elster_land (sort keys %$elster_init) {
294 <option value="$elster_land"|;
295 if ($elster_land eq $land and $checked eq '') {
296 $fa_auswahl .= qq| selected|;
298 $fa_auswahl .= qq|>$elster_land</option>
306 my $elster_land = '';
307 $elster_land = ($land ne '') ? $land : '';
308 %elster_land_fa = ();
309 for $FFFF (keys %{ $elster_init->{$elster_land} }) {
310 $elster_land_fa{$FFFF} = $elster_init->{$elster_land}->{$FFFF}->[0];
318 <select size="1" name="elsterFFFF_new">|;
319 if ($elsterFFFF eq '') {
320 $fa_auswahl .= qq|<option value="Auswahl" $checked>hier auswählen...</option>|;
322 foreach $ffff (sort { $elster_land_fa{$a} cmp $elster_land_fa{$b} }
323 keys(%elster_land_fa)
327 <option value="$ffff"|;
328 if ($ffff eq $elsterFFFF and $checked eq '') {
329 $fa_auswahl .= qq| selected|;
331 $fa_auswahl .= qq|>$elster_land_fa{$ffff} ($ffff)</option>|;
340 $main::lxdebug->leave_sub();
345 $main::lxdebug->enter_sub();
349 if ($ENV{HTTP_USER_AGENT}) {
352 print qq|<body><h2 class=info>Hinweis</h2>
358 <input type=button value="zurück" onClick="history.go(-1)">
366 if ($form->{error_function}) {
367 &{ $form->{error_function} }($msg);
369 die "Hinweis: $msg\n";
373 $main::lxdebug->leave_sub();
377 $main::lxdebug->enter_sub();
380 # soll mal eine Erinnerungsfunktion für USTVA Abgaben werden, die automatisch
381 # den Termin der nächsten USTVA anzeigt.
384 my ($today, $FA_dauerfrist, $FA_voranmeld) = @_;
389 $today =~ /(\d\d\d\d)(\d\d)(\d\d)/;
395 $yymmdd = "$year$month$day" * 1;
396 $mmdd = "$month$day" * 1;
400 #$ical = '...vcal format';
402 #if ($FA_voranmeld eq 'month'){
404 %liste = ("0110" => 'December',
406 "0310" => 'February',
413 "1010" => 'September',
415 "1210" => 'November');
419 $month += 1 if ($day > 10);
420 $month = sprintf("%02d", $month);
421 $stichtag = $year . $month . "10";
422 $ust_va = $month . "10";
424 foreach $date (%liste) {
425 $ust_va = $liste{$date} if ($date eq $stichtag);
428 #} elsif ($FA_voranmeld eq 'quarter'){
433 #@stichtag = ('10.04.2004', '10.05.2004');
435 #@liste = ['0110', '0210', '0310', '0410', '0510', '0610', '0710', '0810', '0910',
436 # '1010', '1110', '1210', ];
438 #foreach $key (@liste){
439 # #if ($ddmm < ('0110' * 1));
441 # $stichtag = $liste[$key - 1] if ($ddmm > $key);
445 #$stichtag =~ /([\d]\d)(\d\d)$/
446 #$stichtag = "$1.$2.$yy"
448 $main::lxdebug->leave_sub();
449 return ($stichtag, $description, $tage_bis, $ical);
452 sub query_finanzamt {
453 $main::lxdebug->enter_sub();
455 my ($self, $myconfig, $form) = @_;
457 my $dbh = $form->dbconnect($myconfig) or $self->error(DBI->errstr);
459 #Test, if table finanzamt exist
460 my $table = 'finanzamt';
461 my $filename = "sql/$table.sql";
463 my $tst = $dbh->prepare("SELECT * FROM $table");
467 #There is no table, read the table from sql/finanzamt.sql
468 print qq|<p>Bitte warten, Tabelle $table wird einmalig in Datenbank:
469 $myconfig->{dbname} als Benutzer: $myconfig->{dbuser} hinzugefügt...</p>|;
470 process_query($form, $dbh, $filename) || $self->error(DBI->errstr);
472 #execute second last call
473 my $dbh = $form->dbconnect($myconfig) or $self->error(DBI->errstr);
490 'FA_PLZ_Grosskunden', # 9
491 'FA_PLZ_Postfach', # 10
494 'FA_Kontonummer_1', # 13
495 'FA_Bankbezeichnung_1', # 14
496 #'FA_BankIBAN_1', # 15
497 #'FA_BankBIC_1', # 16
498 #'FA_BankInhaber_BUFA_Nr_1', # 17
500 'FA_Kontonummer_2', # 19
501 'FA_Bankbezeichnung_2', # 20
502 #'FA_BankIBAN_2', # 21
503 #'FA_BankBIC_2', # 22
504 #'FA_BankInhaber_BUFA_Nr_2', # 23
505 'FA_Oeffnungszeiten', # 24
508 #'FA_zustaendige_Hauptstelle_BUFA_Nr', # 27
509 #'FA_zustaendige_vorgesetzte_Finanzbehoerde' # 28
512 my $field = join(', ', @vars);
514 my $query = "SELECT $field FROM finanzamt ORDER BY FA_Land_nr";
515 my $sth = $dbh->prepare($query) or $self->error($dbh->errstr);
516 $sth->execute || $form->dberror($query);
517 my $array_ref = $sth->fetchall_arrayref();
519 foreach my $row (@$array_ref) {
520 my $FA_finanzamt = $row;
521 $land = 'Schleswig Holstein' if (@$FA_finanzamt[0] eq '1');
522 $land = 'Hamburg' if (@$FA_finanzamt[0] eq '2');
523 $land = 'Niedersachsen' if (@$FA_finanzamt[0] eq '3');
524 $land = 'Bremen' if (@$FA_finanzamt[0] eq '4');
525 $land = 'Nordrhein Westfalen' if (@$FA_finanzamt[0] eq '5');
526 $land = 'Hessen' if (@$FA_finanzamt[0] eq '6');
527 $land = 'Rheinland Pfalz' if (@$FA_finanzamt[0] eq '7');
528 $land = 'Baden Würtemberg' if (@$FA_finanzamt[0] eq '8');
529 $land = 'Bayern' if (@$FA_finanzamt[0] eq '9');
530 $land = 'Saarland' if (@$FA_finanzamt[0] eq '10');
531 $land = 'Berlin' if (@$FA_finanzamt[0] eq '11');
532 $land = 'Brandenburg' if (@$FA_finanzamt[0] eq '12');
533 $land = 'Mecklenburg Vorpommern' if (@$FA_finanzamt[0] eq '13');
534 $land = 'Sachsen' if (@$FA_finanzamt[0] eq '14');
535 $land = 'Sachsen Anhalt' if (@$FA_finanzamt[0] eq '15');
536 $land = 'Thüringen' if (@$FA_finanzamt[0] eq '16');
538 my $ffff = @$FA_finanzamt[1];
541 $rec->{$land} = $ffff;
546 $finanzamt{$land}{$ffff} = [@$FA_finanzamt];
552 $main::lxdebug->leave_sub();
558 $main::lxdebug->enter_sub();
560 # Copyright D. Simander -> SL::Form under Gnu GPL.
561 my ($form, $dbh, $filename) = @_;
563 # return unless (-f $filename);
567 open(FH, "$filename") or $form->error("$filename : $!\n");
574 # Remove DOS and Unix style line endings.
577 # don't add comments or empty lines
578 next if /^(--.*|\s+)$/;
580 for (my $i = 0; $i < length($_); $i++) {
581 my $char = substr($_, $i, 1);
583 # Are we inside a string?
585 if ($char eq $quote_chars[-1]) {
591 if (($char eq "'") || ($char eq "\"")) {
592 push(@quote_chars, $char);
594 } elsif ($char eq ";") {
596 # Query is complete. Send it.
598 $sth = $dbh->prepare($query);
599 $sth->execute || $form->dberror($query);
613 $main::lxdebug->leave_sub();
617 $main::lxdebug->enter_sub();
619 my ($self, $myconfig, $form) = @_;
621 # connect to database
622 my $dbh = $form->dbconnect($myconfig);
625 my $category = "pos_ustva";
627 my @category_cent = USTVA->report_variables({
628 myconfig => $myconfig,
631 attribute => 'position',
635 push @category_cent, qw(83 Z43 Z45 Z53 Z62 Z65 Z67);
637 my @category_euro = USTVA->report_variables({
638 myconfig => $myconfig,
641 attribute => 'position',
645 push @category_euro, USTVA->report_variables({
646 myconfig => $myconfig,
649 attribute => 'position',
653 $form->{decimalplaces} *= 1;
655 foreach $item (@category_cent) {
656 $form->{"$item"} = 0;
658 foreach $item (@category_euro) {
659 $form->{"$item"} = 0;
661 my $coa_name = coa_get($dbh);
662 $form->{coa} = $coa_name;
664 # Controlvariable for templates
665 $form->{"$coa_name"} = '1';
667 $main::lxdebug->message(LXDebug::DEBUG2, "COA: '$form->{coa}', \$form->{$coa_name} = 1");
669 &get_accounts_ustva($dbh, $last_period, $form->{fromdate}, $form->{todate},
672 ###########################################
674 # Nationspecific Modfications
676 ###########################################
680 if ( $form->{coa} eq 'Germany-DATEV-SKR03EU' or $form->{coa} eq 'Germany-DATEV-SKR04EU'){
683 # Umordnen der Kennziffern
684 if ( $form->{year} < 2007) {
685 $form->{35} += $form->{81};
686 $form->{36} += $form->{811};
687 $form->{95} += $form->{89};
688 $form->{98} += $form->{891};
689 map { delete $form->{$_} } qw(81 811 89 891);
691 $form->{35} += $form->{51};
692 $form->{36} += $form->{511};
693 $form->{95} += $form->{97};
694 $form->{98} += $form->{971};
695 map { delete $form->{$_} } qw(51 511 97 971);
701 # Fixme: Wird auch noch für Oesterreich gebraucht,
702 # weil kein eigenes Ausgabeformular
703 # sotte aber aus der allgeméinen Steuerberechnung verschwinden
705 # Berechnung der USTVA Formularfelder laut Bogen 207
708 $form->{"51r"} = $form->{"511"};
709 $form->{"86r"} = $form->{"861"};
710 $form->{"97r"} = $form->{"971"};
711 $form->{"93r"} = $form->{"931"};
713 $form->{"Z43"} = $form->{"511"} + $form->{"811"} + $form->{"861"}
714 + $form->{"36"} + $form->{"80"} + $form->{"971"}
715 + $form->{"891"} + $form->{"931"} + $form->{"96"}
718 $form->{"Z45"} = $form->{"Z43"};
720 $form->{"Z53"} = $form->{"Z45"} + $form->{"53"} + $form->{"74"}
721 + $form->{"85"} + $form->{"65"};
723 $form->{"Z62"} = $form->{"Z43"} - $form->{"66"} - $form->{"61"}
724 - $form->{"62"} - $form->{"67"} - $form->{"63"}
725 - $form->{"64"} - $form->{"59"};
727 $form->{"Z65"} = $form->{"Z62"} - $form->{"69"};
728 $form->{"83"} = $form->{"Z65"} - $form->{"39"};
732 $main::lxdebug->leave_sub();
739 my $query= qq|SELECT coa FROM defaults|;
741 my $sth = $dbh->prepare($query);
743 $sth->execute || $form->dberror($query);
745 ($ref) = $sth->fetchrow_array;
751 sub get_accounts_ustva {
752 $main::lxdebug->enter_sub();
754 my ($dbh, $last_period, $fromdate, $todate, $form, $category) = @_;
756 my ($null, $department_id) = split /--/, $form->{department};
770 my $gltaxkey_where = "(tk.pos_ustva>=59 AND tk.pos_ustva<=66)";
773 if ($form->{method} eq 'cash') {
774 $subwhere .= " AND transdate >= '$fromdate'";
775 $glwhere = " AND ac.transdate >= '$fromdate'";
776 $ARwhere .= " AND acc.transdate >= '$fromdate'";
778 $APwhere .= " AND AP.transdate >= '$fromdate'";
779 $where .= " AND ac.transdate >= '$fromdate'";
783 $where .= " AND ac.transdate <= '$todate'";
784 $ARwhere .= " AND acc.transdate <= '$todate'";
787 if ($department_id) {
789 JOIN department t ON (a.department_id = t.id)
792 AND t.id = $department_id
796 if ($form->{project_id}) {
798 AND ac.project_id = $form->{project_id}
801 ############################################
802 # Method eq 'cash' = IST Versteuerung
803 ############################################
804 # Betrifft nur die eingenommene Umsatzsteuer
806 ############################################
808 if ($form->{method} eq 'cash') {
812 -- USTVA IST-Versteuerung
814 -- Alle tatsaechlichen _Zahlungseingaenge_
815 -- im Voranmeldezeitraum erfassen
816 -- (Teilzahlungen werden prozentual auf verschiedene Steuern aufgeteilt)
818 -- Bezahlt / Rechnungssumme
820 SELECT SUM(acc.amount)
822 INNER JOIN chart c ON (acc.chart_id = c.id
823 AND c.link like '%AR_paid%')
827 AND acc.trans_id = ac.trans_id
831 SELECT amount FROM ar WHERE id = ac.trans_id
836 LEFT JOIN chart c ON (c.id = ac.chart_id)
837 LEFT JOIN ar ON (ar.id = ac.trans_id)
838 LEFT JOIN taxkeys tk ON (
840 SELECT id FROM taxkeys
841 WHERE chart_id = ac.chart_id
842 -- AND taxkey_id = ac.taxkey
843 AND startdate <= COALESCE(ar.deliverydate,ar.transdate)
844 ORDER BY startdate DESC LIMIT 1
849 -- Here no where, please. All Transactions ever should be
850 -- testet if they are paied in the USTVA report period.
851 GROUP BY tk.pos_ustva
854 } elsif ($form->{method} eq 'accrual') {
855 #########################################
856 # Method eq 'accrual' = Soll Versteuerung
857 #########################################
859 if ($department_id) {
861 JOIN dpt_trans t ON (t.trans_id = ac.trans_id)
864 AND t.department_id = $department_id
870 -- Alle Einnahmen AR und pos_ustva erfassen
872 - sum(ac.amount) AS amount,
875 JOIN chart c ON (c.id = ac.chart_id)
876 JOIN ar ON (ar.id = ac.trans_id)
879 SELECT id FROM taxkeys
880 WHERE chart_id = ac.chart_id
881 AND startdate <= COALESCE(ar.deliverydate,ar.transdate)
882 ORDER BY startdate DESC LIMIT 1
890 GROUP BY tk.pos_ustva
895 $self->error("Unknown tax method: $form->{method}")
899 #########################################
900 # Ausgaben und Gl Buchungen sind gleich
901 # für Ist- und Soll-Versteuerung
902 #########################################
904 UNION -- alle Ausgaben AP erfassen
907 sum(ac.amount) AS amount,
910 JOIN ap ON (ap.id = ac.trans_id )
911 JOIN chart c ON (c.id = ac.chart_id)
912 LEFT JOIN taxkeys tk ON (
914 SELECT id FROM taxkeys
916 AND chart_id=ac.chart_id
917 --AND taxkey_id = ac.taxkey
918 AND startdate <= COALESCE(AP.transdate)
919 ORDER BY startdate DESC LIMIT 1
927 GROUP BY tk.pos_ustva
929 UNION -- Einnahmen direkter gl Buchungen erfassen
932 ( - ac.amount) AS amount,
935 JOIN chart c ON (c.id = ac.chart_id)
936 JOIN gl a ON (a.id = ac.trans_id)
937 LEFT JOIN taxkeys tk ON (
939 SELECT id FROM taxkeys
940 WHERE chart_id=ac.chart_id
941 AND NOT $gltaxkey_where
942 AND startdate <= COALESCE(ac.transdate)
943 ORDER BY startdate DESC LIMIT 1
952 GROUP BY tk.pos_ustva
955 UNION -- Ausgaben direkter gl Buchungen erfassen
958 (ac.amount) AS amount,
961 JOIN chart c ON (c.id = ac.chart_id)
962 JOIN gl a ON (a.id = ac.trans_id)
963 LEFT JOIN taxkeys tk ON (
965 SELECT id FROM taxkeys
966 WHERE chart_id=ac.chart_id
968 AND startdate <= COALESCE(ac.transdate)
969 ORDER BY startdate DESC LIMIT 1
978 GROUP BY tk.pos_ustva
986 # Show all $query in Debuglevel LXDebug::QUERY
987 $callingdetails = (caller (0))[3];
988 $main::lxdebug->message(LXDebug::QUERY, "$callingdetails \$query=\n $query");
990 my $sth = $dbh->prepare($query);
992 $sth->execute || $form->dberror($query);
994 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
996 $ref->{amount} *= -1;
997 if ($category eq "pos_bwa") {
999 $form->{ $ref->{$category} }{kumm} += $ref->{amount};
1001 $form->{ $ref->{$category} }{jetzt} += $ref->{amount};
1004 $form->{ $ref->{$category} } += $ref->{amount};
1010 $main::lxdebug->leave_sub();
1015 $main::lxdebug->enter_sub();
1017 my ($self, $userpath, $filename) = @_;
1019 local (*FACONF, *FANEW);
1021 my $form = $main::form;
1023 $filename = "$form->{login}_$filename";
1024 $filename =~ s|.*/||;
1025 $filename = "$userspath/$filename";
1027 if (!open(FACONF, "<", $filename)) {
1028 open(FANEW, ">", $filename) || $form->error("$filename : $!");
1030 open(FACONF, "<", $filename) || $form->error("$filename : $!");
1035 next if (/^(\#|\s)/);
1040 # remove any trailing whitespace
1041 s/^\s*(.*?)\s*$/$1/;
1042 my ($key, $value) = split(/=/, $_, 2);
1044 $form->{$key} = "$value";
1050 $main::lxdebug->leave_sub();