X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FUSTVA.pm;h=d7e2e428e870020f64b41a5129897aebd5d22221;hb=1a344dd59350e494024e81054bc6c752b08c668c;hp=aafb9e75dd714cb18996434be5f3dcfbd28ac20b;hpb=4dbb09950c9f5596646537c12d991c99086fe7c1;p=kivitendo-erp.git
diff --git a/SL/USTVA.pm b/SL/USTVA.pm
index aafb9e75d..d7e2e428e 100644
--- a/SL/USTVA.pm
+++ b/SL/USTVA.pm
@@ -20,7 +20,7 @@
# along with this program; if not, write to the Free Software
# Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
#======================================================================
-# Utilities for ustva
+# Utilities for ustva
#=====================================================================
package USTVA;
@@ -28,172 +28,173 @@ package USTVA;
sub create_steuernummer {
$main::lxdebug->enter_sub();
-
- $part=$form->{part};
- $patterncount = $form->{patterncount};
- $delimiter = $form->{delimiter};
+ $part = $form->{part};
+ $patterncount = $form->{patterncount};
+ $delimiter = $form->{delimiter};
$elster_pattern = $form->{elster_pattern};
-
+
# rebuild steuernummer and elstersteuernummer
# es gibt eine gespeicherte steuernummer $form->{steuernummer}
- # und die parts und delimiter
-
- my $h =0;
- my $i =0;
-
- $steuernummer_new = $part;
+ # und die parts und delimiter
+
+ my $h = 0;
+ my $i = 0;
+
+ $steuernummer_new = $part;
$elstersteuernummer_new = $elster_FFFF;
$elstersteuernummer_new .= '0';
-
- for ( $h = 1; $h < $patterncount; $h++) {
+
+ for ($h = 1; $h < $patterncount; $h++) {
$steuernummer_new .= qq|$delimiter|;
- for ( $i = 1; $i <= length($elster_pattern); $i++ ) {
- $steuernummer_new .=$form->{"part_$h\_$i"};
- $elstersteuernummer_new .=$form->{"part_$h\_$i"};
+ for ($i = 1; $i <= length($elster_pattern); $i++) {
+ $steuernummer_new .= $form->{"part_$h\_$i"};
+ $elstersteuernummer_new .= $form->{"part_$h\_$i"};
}
}
- if ($form->{steuernummer} ne $steuernummer_new){
- $form->{steuernummer} = $steuernummer_new;
+ if ($form->{steuernummer} ne $steuernummer_new) {
+ $form->{steuernummer} = $steuernummer_new;
$form->{elstersteuernummer} = $elstersteuernummer_new;
- $form->{steuernummer_new} = $steuernummer_new;
+ $form->{steuernummer_new} = $steuernummer_new;
}
$main::lxdebug->leave_sub();
return ($steuernummer_new, $elstersteuernummer_new);
}
-
sub steuernummer_input {
$main::lxdebug->enter_sub();
+ my ($self, $elsterland, $elsterFFFF, $steuernummer) = @_;
- ($elsterland, $elsterFFFF, $steuernummer ) = @_;
+ my $steuernummer_input = '';
- $elster_land=$elsterland;
- $elster_FFFF=$elsterFFFF;
- $steuernummer = '0000000000' if ( $steuernummer eq '' );
+ $elster_land = $elsterland;
+ $elster_FFFF = $elsterFFFF;
+ $steuernummer = '0000000000' if ($steuernummer eq '');
+
# $steuernummer formatieren (nur Zahlen) -> $stnr
- $stnr = $steuernummer;
+ my $stnr = $steuernummer;
$stnr =~ s/\D+//g;
-
+
#Pattern description Elstersteuernummer
- my %elster_STNRformat = (
- 'Mecklenburg Vorpommern' => 'FFF/BBB/UUUUP', # '/' 3
- 'Hessen' => '0FF BBB UUUUP', # ' ' 3
- 'Nordrhein Westfalen' => 'FFF/BBBB/UUUP', # '/' 3
- 'Schleswig Holstein' => 'FF BBB UUUUP', # ' ' 2
- 'Berlin' => 'FF/BBB/UUUUP', # '/' 3
- 'Thüringen' => 'FFF/BBB/UUUUP', # '/' 3
- 'Sachsen' => 'FFF/BBB/UUUUP', # '/' 3
- 'Hamburg' => 'FF/BBB/UUUUP', # '/' 3
- 'Baden Würtemberg' => 'FF/BBB/UUUUP', # '/' 2
- 'Sachsen Anhalt' => 'FFF/BBB/UUUUP', # '/' 3
- 'Saarland' => 'FFF/BBB/UUUUP', # '/' 3
- 'Bremen' => 'FF BBB UUUUP', # ' ' 3
- 'Bayern' => 'FFF/BBB/UUUUP', # '/' 3
- 'Rheinland Pfalz' => 'FF/BBB/UUUU/P', # '/' 4
- 'Niedersachsen' => 'FF/BBB/UUUUP', # '/' 3
- 'Brandenburg' => 'FFF/BBB/UUUUP', # '/' 3
+ my %elster_STNRformat = (
+ 'Mecklenburg Vorpommern' => 'FFF/BBB/UUUUP', # '/' 3
+ 'Hessen' => '0FF BBB UUUUP', # ' ' 3
+ 'Nordrhein Westfalen' => 'FFF/BBBB/UUUP', # '/' 3
+ 'Schleswig Holstein' => 'FF BBB UUUUP', # ' ' 2
+ 'Berlin' => 'FF/BBB/UUUUP', # '/' 3
+ 'Thüringen' => 'FFF/BBB/UUUUP', # '/' 3
+ 'Sachsen' => 'FFF/BBB/UUUUP', # '/' 3
+ 'Hamburg' => 'FF/BBB/UUUUP', # '/' 3
+ 'Baden Würtemberg' => 'FF/BBB/UUUUP', # '/' 2
+ 'Sachsen Anhalt' => 'FFF/BBB/UUUUP', # '/' 3
+ 'Saarland' => 'FFF/BBB/UUUUP', # '/' 3
+ 'Bremen' => 'FF BBB UUUUP', # ' ' 3
+ 'Bayern' => 'FFF/BBB/UUUUP', # '/' 3
+ 'Rheinland Pfalz' => 'FF/BBB/UUUU/P', # '/' 4
+ 'Niedersachsen' => 'FF/BBB/UUUUP', # '/' 3
+ 'Brandenburg' => 'FFF/BBB/UUUUP', # '/' 3
);
-
+
#split the pattern
- $elster_pattern = $elster_STNRformat{$elster_land};
+ my $elster_pattern = $elster_STNRformat{$elster_land};
my @elster_pattern = split(' ', $elster_pattern);
- my $delimiter=' ';
- my $patterncount = @elster_pattern;
+ my $delimiter = ' ';
+ my $patterncount = @elster_pattern;
if ($patterncount < 2) {
- @elster_pattern=();
- @elster_pattern = split('/', $elster_pattern);
- $delimiter='/';
- $patterncount = @elster_pattern;
- };
+ @elster_pattern = ();
+ @elster_pattern = split('/', $elster_pattern);
+ $delimiter = '/';
+ $patterncount = @elster_pattern;
+ }
# no we have an array of patternparts and a delimiter
# create the first automated and fixed part and delimiter
-
- print qq||;
- $part='';
- SWITCH: {
- $elster_pattern[0] eq 'FFF' && do {
- $part= substr($elster_FFFF,1,4);
- print qq|$part|;
- last SWITCH;
- };
- $elster_pattern[0] eq '0FF' && do {
- $part= '0'.substr($elster_FFFF,2,4);
- print qq|$part|;
- last SWITCH;
- };
- $elster_pattern[0] eq 'FF' && do {
- $part= substr($elster_FFFF,2,4);
- print qq|$part|;
- last SWITCH;
- };
- 1==1 && do {
- print qq|Fehler!|;;
- last SWITCH;
- };
+
+ $steuernummer_input .= qq||;
+ my $part = '';
+SWITCH: {
+ $elster_pattern[0] eq 'FFF' && do {
+ $part = substr($elster_FFFF, 1, 4);
+ $steuernummer_input .= qq|$part|;
+ last SWITCH;
+ };
+ $elster_pattern[0] eq '0FF' && do {
+ $part = '0' . substr($elster_FFFF, 2, 4);
+ $steuernummer_input .= qq|$part|;
+ last SWITCH;
+ };
+ $elster_pattern[0] eq 'FF' && do {
+ $part = substr($elster_FFFF, 2, 4);
+ $steuernummer_input .= qq|$part|;
+ last SWITCH;
+ };
+ 1 == 1 && do {
+ $steuernummer_input .= qq|Fehler!|;
+ last SWITCH;
+ };
}
+
#now the rest of the Steuernummer ...
- print qq||;
- print qq|\n
+ $steuernummer_input .= qq||;
+ $steuernummer_input .= qq|\n
-
+
|;
- my $h =0;
- my $i =0;
- my $j = 0;
- $k = 0;
-
- for ( $h = 1; $h < $patterncount; $h++) {
- print qq| $delimiter \n|;
- for ( $i = 1; $i <= length($elster_pattern[$h] ); $i++ ) {
- print qq|\n|;
+ $steuernummer_input .= qq|\n|;
}
}
+
$main::lxdebug->leave_sub();
+
+ return $steuernummer_input;
}
sub fa_auswahl {
$main::lxdebug->enter_sub();
-
- use SL::Form;
+# use SL::Form;
- # Referenz wird übergeben, hash of hash wird nicht
+ # Referenz wird übergeben, hash of hash wird nicht
# in neues Hash kopiert, sondern direkt über die Referenz verändert
# Prototyp für diese Konstruktion
- my ($land, $elsterFFFF, $elster_init) = @_; #Referenz auf Hash von Hash übergeben
- my $terminal ='';
- my $FFFF=$elsterFFFF;
- my $ffff='';
- my $checked='';
- $checked='checked' if ($elsterFFFF eq '' and $land eq '');
+ my ($self, $land, $elsterFFFF, $elster_init) = @_;
+
+ my $terminal = '';
+ my $FFFF = $elsterFFFF;
+ my $ffff = '';
+ my $checked = '';
+ $checked = 'checked' if ($elsterFFFF eq '' and $land eq '');
+
#if ($ENV{HTTP_USER_AGENT} =~ /(mozilla|opera|w3m)/i){
#$terminal='mozilla';
#} elsif ($ENV{HTTP_USER_AGENT} =~ /(links|lynx)/i){
#$terminal = 'lynx';
#}
-
-
+
#if ( $terminal eq 'mozilla' or $terminal eq 'js' ) {
- print qq|
-
+ my $fa_auswahl = qq|
@@ -237,58 +237,59 @@ sub fa_auswahl {
|
|;
- my $elster_land = '';
- $elster_land = ( $land ne '' ) ? $land:'';
- %elster_land_fa =();
- for $FFFF ( keys % { $elster_init->{$elster_land} } ) {
- $elster_land_fa{$FFFF} = $elster_init->{$elster_land}->{$FFFF}->[0]
- }
-
- print qq|
+ my $elster_land = '';
+ $elster_land = ($land ne '') ? $land : '';
+ %elster_land_fa = ();
+ for $FFFF (keys %{ $elster_init->{$elster_land} }) {
+ $elster_land_fa{$FFFF} = $elster_init->{$elster_land}->{$FFFF}->[0];
+ }
+
+ $fa_auswahl .= qq|
| Finanzamt
- |
+
|
-
+
|;
-
+
$main::lxdebug->leave_sub();
+ return $fa_auswahl;
}
sub info {
@@ -312,80 +313,79 @@ sub info {
exit;
} else {
-
+
if ($form->{error_function}) {
&{ $form->{error_function} }($msg);
} else {
die "Hinweis: $msg\n";
}
}
-
+
$main::lxdebug->leave_sub();
}
-
+
sub stichtag {
$main::lxdebug->enter_sub();
# noch nicht fertig
- # soll mal eine Erinnerungsfunktion für USTVA Abgaben werden, die automatisch
+ # soll mal eine Erinnerungsfunktion für USTVA Abgaben werden, die automatisch
# den Termin der nächsten USTVA anzeigt.
- #
+ #
#
my ($today, $FA_dauerfrist, $FA_voranmeld) = @_;
+
#$today zerlegen:
-
+
#$today =today * 1;
$today =~ /(\d\d\d\d)(\d\d)(\d\d)/;
- $year = $1;
- $month = $2;
- $day = $3;
- $yy = $year;
- $mm = $month;
- $yymmdd = "$year$month$day" * 1;
- $mmdd = "$month$day" * 1;
+ $year = $1;
+ $month = $2;
+ $day = $3;
+ $yy = $year;
+ $mm = $month;
+ $yymmdd = "$year$month$day" * 1;
+ $mmdd = "$month$day" * 1;
$stichtag = '';
+
#$tage_bis = '1234';
- #$ical = '...vcal format';
-
+ #$ical = '...vcal format';
+
#if ($FA_voranmeld eq 'month'){
-
- %liste = ("0110" => 'December',
- "0210" => 'January',
- "0310" => 'February',
- "0410" => 'March',
- "0510" => 'April',
- "0610" => 'May',
- "0710" => 'June',
- "0810" => 'July',
- "0910" => 'August',
- "1010" => 'September',
- "1110" => 'October',
- "1210" => 'November'
- );
+
+ %liste = ("0110" => 'December',
+ "0210" => 'January',
+ "0310" => 'February',
+ "0410" => 'March',
+ "0510" => 'April',
+ "0610" => 'May',
+ "0710" => 'June',
+ "0810" => 'July',
+ "0910" => 'August',
+ "1010" => 'September',
+ "1110" => 'October',
+ "1210" => 'November');
#$mm += $dauerfrist
#$month *= 1;
- $month += 1 if ( $day > 10 );
- $month = sprintf("%02d", $month);
- $stichtag = $year.$month."10";
- $ust_va = $month."10";
-
-
- foreach $date ( %liste ){
+ $month += 1 if ($day > 10);
+ $month = sprintf("%02d", $month);
+ $stichtag = $year . $month . "10";
+ $ust_va = $month . "10";
+
+ foreach $date (%liste) {
$ust_va = $liste{$date} if ($date eq $stichtag);
}
-
-
+
#} elsif ($FA_voranmeld eq 'quarter'){
#1;
-
+
#}
-
+
#@stichtag = ('10.04.2004', '10.05.2004');
-
+
#@liste = ['0110', '0210', '0310', '0410', '0510', '0610', '0710', '0810', '0910',
# '1010', '1110', '1210', ];
- #
+ #
#foreach $key (@liste){
# #if ($ddmm < ('0110' * 1));
# if ($ddmm ){}
@@ -403,95 +403,99 @@ sub stichtag {
sub query_finanzamt {
$main::lxdebug->enter_sub();
-
- my ($myconfig, $form) =@_;
+ my ($self, $myconfig, $form) = @_;
+
my $dbh = $form->dbconnect($myconfig) or $self->error(DBI->errstr);
+
#Test, if table finanzamt exist
- my $table= 'finanzamt';
- my $filename= "sql/$table.sql";
+ my $table = 'finanzamt';
+ my $filename = "sql/$table.sql";
my $tst = $dbh->prepare("SELECT * FROM $table");
- $tst->execute ;
- if ( $DBI::err ) {
+ $tst->execute;
+ if ($DBI::err) {
+
#There is no table, read the table from sql/finanzamt.sql
- print qq|Bitte warten, Tabelle $table wird einmalig in Datenbank:
+ print qq|
Bitte warten, Tabelle $table wird einmalig in Datenbank:
$myconfig->{dbname} als Benutzer: $myconfig->{dbuser} hinzugefügt...
|;
- process_query($form, $dbh, $filename)||$self->error(DBI->errstr);
+ process_query($form, $dbh, $filename) || $self->error(DBI->errstr);
+
#execute second last call
my $dbh = $form->dbconnect($myconfig) or $self->error(DBI->errstr);
$dbh->disconnect();
- };
+ }
$tst->finish();
+
#$dbh->disconnect();
-
+
my @vars = (
- 'FA_Land_Nr', # 0
- 'FA_BUFA_Nr', # 1
- #'FA_Verteiler', # 2
- 'FA_Name', # 3
- 'FA_Strasse', # 4
- 'FA_PLZ', # 5
- 'FA_Ort', # 6
- 'FA_Telefon', # 7
- 'FA_Fax', # 8
- 'FA_PLZ_Grosskunden', # 9
- 'FA_PLZ_Postfach', # 10
- 'FA_Postfach', # 11
- 'FA_BLZ_1', # 12
- 'FA_Kontonummer_1', # 13
- 'FA_Bankbezeichnung_1', # 14
- #'FA_BankIBAN_1', # 15
- #'FA_BankBIC_1', # 16
- #'FA_BankInhaber_BUFA_Nr_1', # 17
- 'FA_BLZ_2', # 18
- 'FA_Kontonummer_2', # 19
- 'FA_Bankbezeichnung_2', # 20
- #'FA_BankIBAN_2', # 21
- #'FA_BankBIC_2', # 22
- #'FA_BankInhaber_BUFA_Nr_2', # 23
- 'FA_Oeffnungszeiten', # 24
- 'FA_Email', # 25
- 'FA_Internet' # 26
- #'FA_zustaendige_Hauptstelle_BUFA_Nr', # 27
- #'FA_zustaendige_vorgesetzte_Finanzbehoerde' # 28
- );
+ 'FA_Land_Nr', # 0
+ 'FA_BUFA_Nr', # 1
+ #'FA_Verteiler', # 2
+ 'FA_Name', # 3
+ 'FA_Strasse', # 4
+ 'FA_PLZ', # 5
+ 'FA_Ort', # 6
+ 'FA_Telefon', # 7
+ 'FA_Fax', # 8
+ 'FA_PLZ_Grosskunden', # 9
+ 'FA_PLZ_Postfach', # 10
+ 'FA_Postfach', # 11
+ 'FA_BLZ_1', # 12
+ 'FA_Kontonummer_1', # 13
+ 'FA_Bankbezeichnung_1', # 14
+ #'FA_BankIBAN_1', # 15
+ #'FA_BankBIC_1', # 16
+ #'FA_BankInhaber_BUFA_Nr_1', # 17
+ 'FA_BLZ_2', # 18
+ 'FA_Kontonummer_2', # 19
+ 'FA_Bankbezeichnung_2', # 20
+ #'FA_BankIBAN_2', # 21
+ #'FA_BankBIC_2', # 22
+ #'FA_BankInhaber_BUFA_Nr_2', # 23
+ 'FA_Oeffnungszeiten', # 24
+ 'FA_Email', # 25
+ 'FA_Internet' # 26
+ #'FA_zustaendige_Hauptstelle_BUFA_Nr', # 27
+ #'FA_zustaendige_vorgesetzte_Finanzbehoerde' # 28
+ );
my $field = join(', ', @vars);
-
+
my $query = "SELECT $field FROM finanzamt ORDER BY FA_Land_nr";
my $sth = $dbh->prepare($query) or $self->error($dbh->errstr);
$sth->execute || $form->dberror($query);
- my $array_ref = $sth->fetchall_arrayref( );
- my $land = '';
- foreach my $row ( @$array_ref ) {
+ my $array_ref = $sth->fetchall_arrayref();
+ my $land = '';
+ foreach my $row (@$array_ref) {
my $FA_finanzamt = $row;
- $land = 'Schleswig Holstein' if ( @$FA_finanzamt[0] eq '1' );
- $land = 'Hamburg' if ( @$FA_finanzamt[0] eq '2' );
- $land = 'Niedersachsen' if ( @$FA_finanzamt[0] eq '3' );
- $land = 'Bremen' if ( @$FA_finanzamt[0] eq '4' );
- $land = 'Nordrhein Westfalen' if ( @$FA_finanzamt[0] eq '5' );
- $land = 'Hessen' if ( @$FA_finanzamt[0] eq '6' );
- $land = 'Rheinland Pfalz' if ( @$FA_finanzamt[0] eq '7' );
- $land = 'Baden Würtemberg' if ( @$FA_finanzamt[0] eq '8' );
- $land = 'Bayern' if ( @$FA_finanzamt[0] eq '9' );
- $land = 'Saarland' if ( @$FA_finanzamt[0] eq '10' );
- $land = 'Berlin' if ( @$FA_finanzamt[0] eq '11' );
- $land = 'Brandenburg' if ( @$FA_finanzamt[0] eq '12' );
- $land = 'Mecklenburg Vorpommern' if ( @$FA_finanzamt[0] eq '13' );
- $land = 'Sachsen' if ( @$FA_finanzamt[0] eq '14' );
- $land = 'Sachsen Anhalt' if ( @$FA_finanzamt[0] eq '15' );
- $land = 'Thüringen' if ( @$FA_finanzamt[0] eq '16' );
-
+ $land = 'Schleswig Holstein' if (@$FA_finanzamt[0] eq '1');
+ $land = 'Hamburg' if (@$FA_finanzamt[0] eq '2');
+ $land = 'Niedersachsen' if (@$FA_finanzamt[0] eq '3');
+ $land = 'Bremen' if (@$FA_finanzamt[0] eq '4');
+ $land = 'Nordrhein Westfalen' if (@$FA_finanzamt[0] eq '5');
+ $land = 'Hessen' if (@$FA_finanzamt[0] eq '6');
+ $land = 'Rheinland Pfalz' if (@$FA_finanzamt[0] eq '7');
+ $land = 'Baden Würtemberg' if (@$FA_finanzamt[0] eq '8');
+ $land = 'Bayern' if (@$FA_finanzamt[0] eq '9');
+ $land = 'Saarland' if (@$FA_finanzamt[0] eq '10');
+ $land = 'Berlin' if (@$FA_finanzamt[0] eq '11');
+ $land = 'Brandenburg' if (@$FA_finanzamt[0] eq '12');
+ $land = 'Mecklenburg Vorpommern' if (@$FA_finanzamt[0] eq '13');
+ $land = 'Sachsen' if (@$FA_finanzamt[0] eq '14');
+ $land = 'Sachsen Anhalt' if (@$FA_finanzamt[0] eq '15');
+ $land = 'Thüringen' if (@$FA_finanzamt[0] eq '16');
+
my $ffff = @$FA_finanzamt[1];
-
+
my $rec = {};
- $rec->{$land} = $ffff;
+ $rec->{$land} = $ffff;
shift @$row;
shift @$row;
- $finanzamt{$land}{$ffff} = [ @$FA_finanzamt ] ;
- }
+ $finanzamt{$land}{$ffff} = [@$FA_finanzamt];
+ }
$sth->finish();
$dbh->disconnect();
@@ -501,21 +505,21 @@ sub query_finanzamt {
return \%finanzamt;
}
-
sub process_query {
$main::lxdebug->enter_sub();
-# Copyright D. Simander -> SL::Form under Gnu GPL.
+ # Copyright D. Simander -> SL::Form under Gnu GPL.
my ($form, $dbh, $filename) = @_;
-
-# return unless (-f $filename);
-
+
+ # return unless (-f $filename);
+
open(FH, "$filename") or $form->error("$filename : $!\n");
my $query = "";
my $sth;
my @quote_chars;
while () {
+
# Remove DOS and Unix style line endings.
s/[\r\n]//g;
@@ -537,13 +541,14 @@ sub process_query {
push(@quote_chars, $char);
} elsif ($char eq ";") {
+
# Query is complete. Send it.
$sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
$sth->finish;
- $char = "";
+ $char = "";
$query = "";
}
@@ -557,8 +562,355 @@ sub process_query {
$main::lxdebug->leave_sub();
}
+sub ustva {
+ $main::lxdebug->enter_sub();
+ my ($self, $myconfig, $form) = @_;
-1;
+ # connect to database
+ my $dbh = $form->dbconnect($myconfig);
+
+ my $last_period = 0;
+ my $category = "pos_ustva";
+ my @category_cent = qw(
+ 511 861 36 80 971 931 98 96 53 74
+ 85 65 66 61 62 67 63 64 59 69
+ 39 83 811 891 Z43 Z45 Z53 Z62 Z65 Z67
+ );
+
+ my @category_euro = qw(
+ 41 44 49 43 48 51
+ 86 35 77 76 91 97
+ 93 95 94 42 60 45
+ 52 73 84 81 89
+ );
+
+ $form->{decimalplaces} *= 1;
+
+ foreach $item (@category_cent) {
+ $form->{"$item"} = 0;
+ }
+ foreach $item (@category_euro) {
+ $form->{"$item"} = 0;
+ }
+
+ &get_accounts_ustva($dbh, $last_period, $form->{fromdate}, $form->{todate},
+ $form, $category);
+
+
+ # 16%/19% Umstellung
+ # Umordnen der Kennziffern
+ if ( $form->{year} < 2007) {
+ $form->{35} += $form->{81};
+ $form->{36} += $form->{811};
+ $form->{95} += $form->{89};
+ $form->{98} += $form->{891};
+ map { delete $form->{$_} } qw(81 811 89 891);
+ } else {
+ $form->{35} += $form->{51};
+ $form->{36} += $form->{511};
+ $form->{95} += $form->{97};
+ $form->{98} += $form->{971};
+ map { delete $form->{$_} } qw(51 511 97 971);
+ }
+
+
+ #
+ # Berechnung der USTVA Formularfelder laut Bogen 207
+ #
+
+ $form->{"51r"} = $form->{"511"};
+ $form->{"86r"} = $form->{"861"};
+ $form->{"97r"} = $form->{"971"};
+ $form->{"93r"} = $form->{"931"};
+
+ $form->{"Z43"} = $form->{"511"} + $form->{"811"} + $form->{"861"}
+ + $form->{"36"} + $form->{"80"} + $form->{"971"}
+ + $form->{"891"} + $form->{"931"} + $form->{"96"}
+ + $form->{"98"};
+
+ $form->{"Z45"} = $form->{"Z43"};
+
+ $form->{"Z53"} = $form->{"Z45"} + $form->{"53"} + $form->{"74"}
+ + $form->{"85"} + $form->{"65"};
+
+ $form->{"Z62"} = $form->{"Z43"} - $form->{"66"} - $form->{"61"}
+ - $form->{"62"} - $form->{"67"} - $form->{"63"}
+ - $form->{"64"} - $form->{"59"};
+
+ $form->{"Z65"} = $form->{"Z62"} - $form->{"69"};
+ $form->{"83"} = $form->{"Z65"} - $form->{"39"};
+
+ $dbh->disconnect;
+
+ $main::lxdebug->leave_sub();
+}
+
+sub get_accounts_ustva {
+ $main::lxdebug->enter_sub();
+
+ my ($dbh, $last_period, $fromdate, $todate, $form, $category) = @_;
+
+ my ($null, $department_id) = split /--/, $form->{department};
+
+ my $query;
+ my $dpt_where;
+ my $dpt_join;
+ my $project;
+ my $where = "";
+ my $glwhere = "";
+ my $subwhere = "";
+ my $ARwhere = "";
+ my $APwhere = '';
+ my $arwhere = "";
+ my $item;
+ my $gltaxkey_where = "(tk.pos_ustva>=59 AND tk.pos_ustva<=66)";
+
+ if ($fromdate) {
+ if ($form->{method} eq 'cash') {
+ $subwhere .= " AND transdate >= '$fromdate'";
+ $glwhere = " AND ac.transdate >= '$fromdate'";
+ $ARwhere .= " AND acc.transdate >= '$fromdate'";
+ }
+ $APwhere .= " AND AP.transdate >= '$fromdate'";
+ $where .= " AND ac.transdate >= '$fromdate'";
+ }
+
+ if ($todate) {
+ $where .= " AND ac.transdate <= '$todate'";
+ $ARwhere .= " AND acc.transdate <= '$todate'";
+ $subwhere .= " AND transdate <= '$todate'";
+ $APwhere .= " AND AP.transdate <= '$todate'";
+ }
+
+ if ($department_id) {
+ $dpt_join = qq|
+ JOIN department t ON (a.department_id = t.id)
+ |;
+ $dpt_where = qq|
+ AND t.id = $department_id
+ |;
+ }
+
+ if ($form->{project_id}) {
+ $project = qq|
+ AND ac.project_id = $form->{project_id}
+ |;
+ }
+ ############################################
+ # Method eq 'cash' = IST Versteuerung
+ ############################################
+ # Betrifft nur die eingenommene Umsatzsteuer
+ #
+ ############################################
+
+ if ($form->{method} eq 'cash') {
+
+ $query = qq|
+ SELECT
+ -- USTVA IST-Versteuerung
+ --
+ -- Alle tatsaechlichen _Zahlungseingaenge_
+ -- im Voranmeldezeitraum erfassen
+ -- (Teilzahlungen werden prozentual auf verschiedene Steuern aufgeteilt)
+ SUM( ac.amount *
+ -- Bezahlt / Rechnungssumme
+ (
+ SELECT SUM(acc.amount)
+ FROM acc_trans acc
+ INNER JOIN chart c ON (acc.chart_id = c.id
+ AND c.link like '%AR_paid%')
+ WHERE
+ 1=1
+ $ARwhere
+ AND acc.trans_id = ac.trans_id
+ )
+ /
+ (
+ SELECT amount FROM ar WHERE id = ac.trans_id
+ )
+ ) AS amount,
+ tk.pos_ustva
+ FROM acc_trans ac
+ LEFT JOIN chart c ON (c.id = ac.chart_id)
+ LEFT JOIN ar ON (ar.id = ac.trans_id)
+ LEFT JOIN taxkeys tk ON (
+ tk.id = (
+ SELECT id FROM taxkeys
+ WHERE chart_id = ac.chart_id
+ -- AND taxkey_id = ac.taxkey
+ AND startdate <= COALESCE(ar.transdate)
+ ORDER BY startdate DESC LIMIT 1
+ )
+ )
+ WHERE
+ 1=1
+ -- Here no where, please. All Transactions ever should be
+ -- testet if they are paied in the USTVA report period.
+ GROUP BY tk.pos_ustva
+ |;
+
+ } elsif ($form->{method} eq 'accrual') {
+ #########################################
+ # Method eq 'accrual' = Soll Versteuerung
+ #########################################
+
+ if ($department_id) {
+ $dpt_join = qq|
+ JOIN dpt_trans t ON (t.trans_id = ac.trans_id)
+ |;
+ $dpt_where = qq|
+ AND t.department_id = $department_id
+ |;
+ }
+
+
+ $query = qq|
+ -- Alle Einnahmen AR und pos_ustva erfassen
+ SELECT
+ - sum(ac.amount) AS amount,
+ tk.pos_ustva
+ FROM acc_trans ac
+ JOIN chart c ON (c.id = ac.chart_id)
+ JOIN ar ON (ar.id = ac.trans_id)
+ JOIN taxkeys tk ON (
+ tk.id = (
+ SELECT id FROM taxkeys
+ WHERE chart_id = ac.chart_id
+ AND startdate <= COALESCE(ar.transdate)
+ ORDER BY startdate DESC LIMIT 1
+ )
+ )
+ $dpt_join
+ WHERE 1 = 1
+ $where
+ $dpt_where
+ $project
+ GROUP BY tk.pos_ustva
+ |;
+
+ } else {
+
+ $self->error("Unknown tax method: $form->{method}")
+
+ }
+
+ #########################################
+ # Ausgaben und Gl Buchungen sind gleich
+ # für Ist- und Soll-Versteuerung
+ #########################################
+ $query .= qq|
+ UNION -- alle Ausgaben AP erfassen
+
+ SELECT
+ sum(ac.amount) AS amount,
+ tk.pos_ustva
+ FROM acc_trans ac
+ JOIN AP ON (AP.id = ac.trans_id )
+ JOIN chart c ON (c.id = ac.chart_id)
+ LEFT JOIN taxkeys tk ON (
+ tk.id = (
+ SELECT id FROM taxkeys
+ WHERE chart_id=ac.chart_id
+ --AND taxkey_id=ac.taxkey
+ AND startdate <= COALESCE(AP.transdate)
+ ORDER BY startdate DESC LIMIT 1
+ )
+ )
+ WHERE
+ 1=1
+ $APwhere
+ $dpt_where
+ $project
+ GROUP BY tk.pos_ustva
+
+ UNION -- Einnahmen direkter gl Buchungen erfassen
+
+ SELECT sum
+ ( - ac.amount) AS amount,
+ tk.pos_ustva
+ FROM acc_trans ac
+ JOIN chart c ON (c.id = ac.chart_id)
+ JOIN gl a ON (a.id = ac.trans_id)
+ LEFT JOIN taxkeys tk ON (
+ tk.id = (
+ SELECT id FROM taxkeys
+ WHERE chart_id=ac.chart_id
+ --AND taxkey_id=ac.taxkey
+ AND NOT $gltaxkey_where
+ AND startdate <= COALESCE(ac.transdate)
+ ORDER BY startdate DESC LIMIT 1
+ )
+ )
+
+ $dpt_join
+ WHERE 1 = 1
+ $where
+ $dpt_from
+ $project
+ GROUP BY tk.pos_ustva
+
+
+ UNION -- Ausgaben direkter gl Buchungen erfassen
+
+ SELECT sum
+ (ac.amount) AS amount,
+ tk.pos_ustva
+ FROM acc_trans ac
+ JOIN chart c ON (c.id = ac.chart_id)
+ JOIN gl a ON (a.id = ac.trans_id)
+ LEFT JOIN taxkeys tk ON (
+ tk.id = (
+ SELECT id FROM taxkeys
+ WHERE chart_id=ac.chart_id
+ --AND taxkey_id=ac.taxkey
+ AND $gltaxkey_where
+ AND startdate <= COALESCE(ac.transdate)
+ ORDER BY startdate DESC LIMIT 1
+ )
+ )
+
+ $dpt_join
+ WHERE 1 = 1
+ $where
+ $dpt_from
+ $project
+ GROUP BY tk.pos_ustva
+
+ |;
+
+ my @accno;
+ my $accno;
+ my $ref;
+
+ # Show all $query in Debuglevel LXDebug::QUERY
+ $callingdetails = (caller (0))[3];
+ $main::lxdebug->message(LXDebug::QUERY, "$callingdetails \$query=\n $query");
+
+ my $sth = $dbh->prepare($query);
+
+ $sth->execute || $form->dberror($query);
+
+ while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+ # Bug 365 solved?!
+ $ref->{amount} *= -1;
+ if ($category eq "pos_bwa") {
+ if ($last_period) {
+ $form->{ $ref->{$category} }{kumm} += $ref->{amount};
+ } else {
+ $form->{ $ref->{$category} }{jetzt} += $ref->{amount};
+ }
+ } else {
+ $form->{ $ref->{$category} } += $ref->{amount};
+ }
+ }
+
+ $sth->finish;
+
+ $main::lxdebug->leave_sub();
+
+}
+
+1;