+
+ # Fixme: Wird auch noch für Oesterreich gebraucht,
+ # weil kein eigenes Ausgabeformular
+ # sollte aber aus der allgemeinen Steuerberechnung verschwinden
+ #
+ # 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->{"47"} + $form->{"53"} + $form->{"74"}
+ + $form->{"85"} + $form->{"65"};
+
+ $form->{"Z62"} = $form->{"Z53"} - $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"};
+
+ $main::lxdebug->leave_sub();
+}
+
+sub get_accounts_ustva {
+ $main::lxdebug->enter_sub();
+
+ my ($dbh, $last_period, $fromdate, $todate, $form, $category) = @_;
+ our ($dpt_join);
+
+ my $query;
+ my $where = "";
+ my $glwhere = "";
+ my $subwhere = "";
+ my $ARwhere = "";
+ my $APwhere = '';
+ my $arwhere = "";
+ my $item;
+
+ my $gltaxkey_where = "((tk.pos_ustva = 46) OR (tk.pos_ustva>=59 AND tk.pos_ustva<=67) or (tk.pos_ustva>=89 AND tk.pos_ustva<=93))";
+
+ if ($fromdate) {
+ if ($form->{accounting_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'";
+ }
+
+ my $acc_trans_where = '1=1';
+ if ($fromdate || $todate) {
+ $acc_trans_where = "ac.trans_id IN (SELECT DISTINCT trans_id FROM acc_trans WHERE ";
+
+ if ($fromdate) {
+ $acc_trans_where .= "transdate >= '$fromdate'";
+ }
+ if ($todate) {
+ $acc_trans_where .= " AND " if ($fromdate);
+ $acc_trans_where .= "transdate <= '$todate'";
+ }
+
+ $acc_trans_where .= ")";
+ }
+
+ ############################################
+ # Method eq 'cash' = IST Versteuerung
+ ############################################
+ # Betrifft nur die eingenommene Umsatzsteuer
+ #
+ ############################################
+
+ if ($form->{accounting_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, t.rate, c.accno
+ 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 tax t ON (t.id = ac.tax_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.deliverydate,ar.transdate)
+ ORDER BY startdate DESC LIMIT 1
+ )
+ )
+ WHERE
+ $acc_trans_where
+ GROUP BY tk.pos_ustva, t.rate, c.accno
+ |;
+
+ } elsif ($form->{accounting_method} eq 'accrual') {
+ #########################################
+ # Method eq 'accrual' = Soll Versteuerung
+ #########################################
+
+ $query = qq|
+ -- Alle Einnahmen AR und pos_ustva erfassen
+ SELECT
+ - sum(ac.amount) AS amount,
+ tk.pos_ustva, t.rate, c.accno
+ FROM acc_trans ac
+ JOIN chart c ON (c.id = ac.chart_id)
+ JOIN ar ON (ar.id = ac.trans_id)
+ JOIN tax t ON (t.id = ac.tax_id)
+ JOIN taxkeys tk ON (
+ tk.id = (
+ SELECT id FROM taxkeys
+ WHERE chart_id = ac.chart_id
+ AND startdate <= COALESCE(ar.deliverydate,ar.transdate)
+ ORDER BY startdate DESC LIMIT 1
+ )
+ )
+ $dpt_join
+ WHERE 1 = 1
+ $where
+ GROUP BY tk.pos_ustva, t.rate, c.accno
+ |;
+
+ } else {
+
+ $form->error("Unknown tax method: $form->{accounting_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, t.rate, c.accno
+ FROM acc_trans ac
+ JOIN ap ON (ap.id = ac.trans_id )
+ JOIN chart c ON (c.id = ac.chart_id)
+ JOIN tax t ON (t.id = ac.tax_id)
+ LEFT JOIN taxkeys tk ON (
+ tk.id = (
+ SELECT id FROM taxkeys
+ WHERE 1=1
+ AND chart_id=ac.chart_id
+ --AND taxkey_id = ac.taxkey
+ AND startdate <= COALESCE(AP.transdate)
+ ORDER BY startdate DESC LIMIT 1
+ )
+ )
+ WHERE
+ 1=1
+ $where
+ GROUP BY tk.pos_ustva, t.rate, c.accno
+
+ UNION -- Einnahmen direkter gl Buchungen erfassen
+
+ SELECT sum
+ ( - ac.amount) AS amount,
+ tk.pos_ustva, t.rate, c.accno
+ FROM acc_trans ac
+ JOIN chart c ON (c.id = ac.chart_id)
+ JOIN gl a ON (a.id = ac.trans_id)
+ JOIN tax t ON (t.id = ac.tax_id)
+ LEFT JOIN taxkeys tk ON (
+ tk.id = (
+ SELECT id FROM taxkeys
+ WHERE chart_id=ac.chart_id
+ AND NOT $gltaxkey_where
+ AND startdate <= COALESCE(ac.transdate)
+ ORDER BY startdate DESC LIMIT 1
+ )
+ )
+
+ $dpt_join
+ WHERE 1 = 1
+ $where
+ GROUP BY tk.pos_ustva, t.rate, c.accno
+
+
+ UNION -- Ausgaben direkter gl Buchungen erfassen
+
+ SELECT sum
+ (ac.amount) AS amount,
+ tk.pos_ustva, t.rate, c.accno
+ FROM acc_trans ac
+ JOIN chart c ON (c.id = ac.chart_id)
+ JOIN gl a ON (a.id = ac.trans_id)
+ JOIN tax t ON (t.id = ac.tax_id)
+ LEFT JOIN taxkeys tk ON (
+ tk.id = (
+ SELECT id FROM taxkeys
+ WHERE chart_id=ac.chart_id
+ AND $gltaxkey_where
+ AND startdate <= COALESCE(ac.transdate)
+ ORDER BY startdate DESC LIMIT 1
+ )
+ )
+
+ $dpt_join
+ WHERE 1 = 1
+ $where
+ GROUP BY tk.pos_ustva, t.rate, c.accno
+
+ |;
+
+ # Show all $query in Debuglevel LXDebug::QUERY
+ my $callingdetails = (caller (0))[3];
+ $main::lxdebug->message(LXDebug->QUERY(), "$callingdetails \$query=\n $query");
+
+ my $sth = $dbh->prepare($query);
+
+ $sth->execute || $form->dberror($query);
+ # ugly, but we need to use static accnos
+ my ($accno_five, $accno_sixteen, $corr);
+
+ if ($form->{coa} eq 'Germany-DATEV-SKR03EU') {
+ $accno_five = 1773;
+ $accno_sixteen = 1775;
+ } elsif (($form->{coa} eq 'Germany-DATEV-SKR04EU')) {
+ $accno_five = 3803; # SKR04
+ $accno_sixteen = 3805; # SKR04
+ } else {die "wrong call"; }
+
+ while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
+ next unless $ref->{$category};
+ $corr = 0;
+ $ref->{amount} *= -1;
+ # USTVA Pos 35
+ if ($ref->{pos_ustva} eq '35') {
+ if ($ref->{rate} == 0.16) {
+ $form->{"pos_ustva_81b_kivi"} += $ref->{amount};
+ } elsif ($ref->{rate} == 0.05) {
+ $form->{"pos_ustva_86b_kivi"} += $ref->{amount};
+ } elsif ($ref->{rate} == 0.19) {
+ # pos_ustva says 16, but rate says 19
+ # (pos_ustva should be tax dependent and not taxkeys dependent)
+ # correction hotfix for this case:
+ # bookings exists with 19% ->
+ # move 19% bookings to the 19% position
+ # Dont rely on dates of taxkeys
+ $corr = 1;
+ $form->{"81"} += $ref->{amount};
+ } elsif ($ref->{rate} == 0.07) {
+ # pos_ustva says 5, but rate says 7
+ # see comment above:
+ # Dont rely on dates of taxkeys
+ $corr = 1;
+ $form->{"86"} += $ref->{amount};
+ } else {die ("No valid tax rate for pos 35" . Dumper($ref)); }
+ }
+ # USTVA Pos 36 (Steuerkonten)
+ if ($ref->{pos_ustva} eq '36') {
+ if ($ref->{accno} =~ /^$accno_sixteen/) {
+ $form->{"pos_ustva_811b_kivi"} += $ref->{amount};
+ } elsif ($ref->{accno} =~ /^$accno_five/) {
+ $form->{"pos_ustva_861b_kivi"} += $ref->{amount};
+ } else { die ("No valid accno for pos 36" . Dumper($ref)); }
+ }
+ $form->{ $ref->{$category} } += $ref->{amount} unless $corr;
+ }
+
+ $sth->finish;
+
+ $main::lxdebug->leave_sub();
+
+}
+
+sub set_FromTo {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $form) = @_;
+
+ # init some form vars
+ my @anmeldungszeitraum =
+ qw('0401' '0402' '0403'
+ '0404' '0405' '0406'
+ '0407' '0408' '0409'
+ '0410' '0411' '0412'
+ '0441' '0442' '0443' '0444');
+
+ foreach my $item (@anmeldungszeitraum) {
+ $form->{$item} = "";
+ }
+
+ #forgotten the year --> thisyear
+ if ($form->{year} !~ m/^\d\d\d\d$/) {
+ $form->{year} = substr(
+ $form->datetonum(
+ $form->current_date(\%::myconfig), \%::myconfig
+ ),
+ 0, 4);
+ $::lxdebug->message(LXDebug->DEBUG1,
+ qq|Actual year from Database: $form->{year}\n|);
+ }
+
+ #
+ # using dates in ISO-8601 format: yyyymmmdd for Postgres...
+ #
+
+ #yearly report
+ if ($form->{period} eq "13") {
+ $form->{fromdate} = "$form->{year}0101";
+ $form->{todate} = "$form->{year}1231";
+ }
+
+ #quarter reports
+ if ($form->{period} eq "41") {
+ $form->{fromdate} = "$form->{year}0101";
+ $form->{todate} = "$form->{year}0331";
+ $form->{'0441'} = "X";
+ }
+ if ($form->{period} eq "42") {
+ $form->{fromdate} = "$form->{year}0401";
+ $form->{todate} = "$form->{year}0630";
+ $form->{'0442'} = "X";
+ }
+ if ($form->{period} eq "43") {
+ $form->{fromdate} = "$form->{year}0701";
+ $form->{todate} = "$form->{year}0930";
+ $form->{'0443'} = "X";
+ }
+ if ($form->{period} eq "44") {
+ $form->{fromdate} = "$form->{year}1001";
+ $form->{todate} = "$form->{year}1231";
+ $form->{'0444'} = "X";
+ }
+
+ #Monthly reports
+ SWITCH: {
+ $form->{period} eq "01" && do {
+ $form->{fromdate} = "$form->{year}0101";
+ $form->{todate} = "$form->{year}0131";
+ $form->{'0401'} = "X";
+ last SWITCH;
+ };
+ $form->{period} eq "02" && do {
+ $form->{fromdate} = "$form->{year}0201";
+
+ #this works from 1901 to 2099, 1900 and 2100 fail.
+ my $leap = ($form->{year} % 4 == 0) ? "29" : "28";
+ $form->{todate} = "$form->{year}02$leap";
+ $form->{"0402"} = "X";
+ last SWITCH;
+ };
+ $form->{period} eq "03" && do {
+ $form->{fromdate} = "$form->{year}0301";
+ $form->{todate} = "$form->{year}0331";
+ $form->{"0403"} = "X";
+ last SWITCH;
+ };
+ $form->{period} eq "04" && do {
+ $form->{fromdate} = "$form->{year}0401";
+ $form->{todate} = "$form->{year}0430";
+ $form->{"0404"} = "X";
+ last SWITCH;
+ };
+ $form->{period} eq "05" && do {
+ $form->{fromdate} = "$form->{year}0501";
+ $form->{todate} = "$form->{year}0531";
+ $form->{"0405"} = "X";
+ last SWITCH;
+ };
+ $form->{period} eq "06" && do {
+ $form->{fromdate} = "$form->{year}0601";
+ $form->{todate} = "$form->{year}0630";
+ $form->{"0406"} = "X";
+ last SWITCH;
+ };
+ $form->{period} eq "07" && do {
+ $form->{fromdate} = "$form->{year}0701";
+ $form->{todate} = "$form->{year}0731";
+ $form->{"0407"} = "X";
+ last SWITCH;
+ };
+ $form->{period} eq "08" && do {
+ $form->{fromdate} = "$form->{year}0801";
+ $form->{todate} = "$form->{year}0831";
+ $form->{"0408"} = "X";
+ last SWITCH;
+ };
+ $form->{period} eq "09" && do {
+ $form->{fromdate} = "$form->{year}0901";
+ $form->{todate} = "$form->{year}0930";
+ $form->{"0409"} = "X";
+ last SWITCH;
+ };
+ $form->{period} eq "10" && do {
+ $form->{fromdate} = "$form->{year}1001";
+ $form->{todate} = "$form->{year}1031";
+ $form->{"0410"} = "X";
+ last SWITCH;
+ };
+ $form->{period} eq "11" && do {
+ $form->{fromdate} = "$form->{year}1101";
+ $form->{todate} = "$form->{year}1130";
+ $form->{"0411"} = "X";
+ last SWITCH;
+ };
+ $form->{period} eq "12" && do {
+ $form->{fromdate} = "$form->{year}1201";
+ $form->{todate} = "$form->{year}1231";
+ $form->{"0412"} = "X";
+ last SWITCH;
+ };
+ }
+
+ # Kontrollvariablen für die Templates
+ $form->{"year$_"} = ($form->{year} >= $_ ) ? "1":"0" for 2007..2107;
+
+ $main::lxdebug->leave_sub();
+}
+
+sub get_fiamt_vars {
+ return @fiamt_finanzamt;
+}
+
+sub get_oldconfig {
+ $main::lxdebug->enter_sub();
+
+ my $ret = 0;
+ my %oldkeys = (
+ 'steuernummer' => 'taxnumber',
+ 'elsterFFFF' => 'fa_bufa_nr',
+ 'FA_dauerfrist' => 'fa_dauerfrist',
+ 'FA_steuerberater_city' => 'fa_steuerberater_city',
+ 'FA_steuerberater_name' => 'fa_steuerberater_name',
+ 'FA_steuerberater_street' => 'fa_steuerberater_street',
+ 'FA_steuerberater_tel' => 'fa_steuerberater_tel',
+ 'FA_voranmeld' => 'fa_voranmeld',
+ );
+
+ my $filename = $::lx_office_conf{paths}{userspath}."/finanzamt.ini";
+ my $FACONF;
+ return unless (open( $FACONF, "<", $filename));
+
+ while (<$FACONF>) {
+ last if (/^\[/);
+ next if (/^(\#|\s)/);
+
+ # remove comments
+ s/\s#.*//g;
+
+ # remove any trailing whitespace
+ s/^\s*(.*?)\s*$/$1/;
+ my ($key, $value) = split(/=/, $_, 2);
+
+ $main::lxdebug->message(LXDebug->DEBUG2(), "oldkey: ".$key." val=".$value." newkey=".
+ $oldkeys{$key}." oval=".$::form->{$oldkeys{$key}});
+ if ( $oldkeys{$key} && $::form->{$oldkeys{$key}} eq '' ) {
+ $::form->{$oldkeys{$key}} = $::locale->{iconv_utf8}->convert($value);
+ $main::lxdebug->message(LXDebug->DEBUG2(), "set ".$oldkeys{$key}."=".$::form->{$oldkeys{$key}});
+ $ret = 1;
+ }
+ }
+ $main::lxdebug->leave_sub();
+ return $ret;
+}
+
+sub get_config {
+ $main::lxdebug->enter_sub();
+ my $defaults = SL::DB::Default->get;
+ my @rd_config = @fiamt_config;
+ push @rd_config ,qw(accounting_method coa company address co_ustid duns);
+ $::form->{$_} = $defaults->$_ for @rd_config;
+
+ if ( $::form->{taxnumber} eq '' || $::form->{fa_bufa_nr} eq '') {
+ #alte finanzamt.ini lesen, ggf abspeichern
+ if ( get_oldconfig() ) {
+ get_finanzamt();
+ save_config();
+ }
+ }
+
+ my $coa = $::form->{coa};
+ $::form->{"COA_$coa"} = '1';
+ $::form->{COA_Germany} = '1' if ($coa =~ m/^germany/i);
+ $main::lxdebug->leave_sub();
+}
+
+sub get_finanzamt {
+ $main::lxdebug->enter_sub();
+ if ( $::form->{fa_bufa_nr} && $::form->{fa_bufa_nr} ne '' ) {
+ my $fiamt = SL::DB::Finanzamt->_get_manager_class->get_first(
+ query => [ fa_bufa_nr => $::form->{fa_bufa_nr} ]);
+ $::form->{$_} = $fiamt->$_ for @fiamt_finanzamt;
+ }
+ $main::lxdebug->leave_sub();
+}
+
+sub save_config {
+ $main::lxdebug->enter_sub();
+ my $defaults = SL::DB::Default->get;
+ $defaults->$_($::form->{$_}) for @fiamt_config;
+ $defaults->save;
+ if ( $defaults->fa_bufa_nr ) {
+ my $fiamt = SL::DB::Finanzamt->_get_manager_class->get_first(
+ query => [ fa_bufa_nr => $defaults->fa_bufa_nr ]);
+ $fiamt->$_($::form->{$_}) for @fiamt_finanzamt;
+ $fiamt->save;
+ }
+ $main::lxdebug->leave_sub();
+}
+
+1;