- SELECT
- -- 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.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 tk.pos_ustva
-
- 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
+ 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.deliverydate, ar.transdate)
+ ORDER BY startdate DESC LIMIT 1
+ )