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 Z43 Z45 Z53 Z62 Z65 Z67
+ 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
+ 52 73 84 81 89
);
$form->{decimalplaces} *= 1;
&get_accounts_ustva($dbh, $last_period, $form->{fromdate}, $form->{todate},
$form, $category);
+
+ # 16%/19% Umstelung
+ # Umordnen der Kennziffern und abfangen von Fehlern
+
+# $form->header;
+# print $form->{81};
+
+ 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
+ # 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->{"861"} + $form->{"36"} + $form->{"80"} +
- $form->{"971"} + $form->{"931"} + $form->{"96"} + $form->{"98"};
+
+ $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->{"Z43"};
- $form->{"Z62"} =
- $form->{"Z43"} - $form->{"66"} - $form->{"61"} - $form->{"62"} -
- $form->{"63"} - $form->{"64"} - $form->{"59"};
- $form->{"Z65"} = $form->{"Z62"} - $form->{"69"};
- $form->{"83"} = $form->{"Z65"} - $form->{"39"};
- # Hier fehlen moeglicherweise noch einige Berechnungen!
+
+ $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;
(
SELECT SUM(acc.amount)
FROM acc_trans acc
- INNER JOIN chart c ON (acc.chart_id = c.id AND c.link like '%AR_paid%')
+ INNER JOIN chart c ON (acc.chart_id = c.id
+ AND c.link like '%AR_paid%')
WHERE
1=1
$ARwhere
select amount from ar where id = ac.trans_id
)
) AS amount,
- c.pos_ustva
+ tk.pos_ustva
FROM acc_trans ac
- JOIN chart c ON (c.id = ac.chart_id)
- --JOIN ar ON (ar.id = ac.trans_id)
- where
+ 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.deliverydate, 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 c.pos_ustva
+ GROUP BY tk.pos_ustva
UNION -- alle Ausgaben AP erfassen
SELECT
- sum(ac.amount) AS amount, pos_ustva
+ 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 AND pos_ustva NOT LIKE '')
+ 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 pos_ustva
+ GROUP BY tk.pos_ustva
UNION -- alle Ausgaben und Einnahmen direkter gl Buchungen erfassen
SELECT sum
- (
- CASE WHEN c.link LIKE '%AR%' THEN ac.amount * -1
- WHEN c.link LIKE '%AP%' THEN ac.amount * 1
- END
- ) AS amount, c.$category
+ ( - 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 startdate <= COALESCE(ac.transdate)
+ ORDER BY startdate DESC LIMIT 1
+ )
+ )
+
$dpt_join
WHERE $where
$dpt_from
- AND NOT (c.link = 'AR' OR c.link = 'AP')
$project
- GROUP BY c.$category
+ GROUP BY tk.pos_ustva
|;
} else {