- |;
-
- if ($form->{project_id}) {
-
- $query .= qq|
-
- UNION
-
- SELECT SUM(ac.sellprice * ac.qty) AS amount,
- c.$category
- FROM invoice ac
- JOIN ar a ON (a.id = ac.trans_id)
- JOIN parts p ON (ac.parts_id = p.id)
- JOIN chart c on (p.income_accno_id = c.id)
- $dpt_join
- -- use transdate from subwhere
- WHERE 1 = 1 $subwhere
- AND c.category = 'I'
- $dpt_where
- AND ac.trans_id IN
- (
- SELECT trans_id
- FROM acc_trans
- JOIN chart ON (chart_id = id)
- WHERE link LIKE '%AR_paid%'
- $subwhere
- )
-
- $project
- GROUP BY c.$category
-
- UNION
-
- SELECT SUM(ac.sellprice) AS amount,
- c.$category
- FROM invoice ac
- JOIN ap a ON (a.id = ac.trans_id)
- JOIN parts p ON (ac.parts_id = p.id)
- JOIN chart c on (p.expense_accno_id = c.id)
- $dpt_join
- WHERE 1 = 1 $subwhere
- AND c.category = 'E'
- $dpt_where
- AND ac.trans_id IN
- (
- SELECT trans_id
- FROM acc_trans
- JOIN chart ON (chart_id = id)
- WHERE link LIKE '%AP_paid%'
- $subwhere
- )
-
- $project
- GROUP BY c.$category
- |;
- }
-
- } else {
+ UNION -- alle Ausgaben AP erfassen
+
+ SELECT
+ sum(ac.amount) AS amount, 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 '')
+ WHERE
+ 1=1
+ $APwhere
+ $dpt_where
+ $project
+ GROUP BY 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
+ FROM acc_trans ac
+ JOIN chart c ON (c.id = ac.chart_id)
+ JOIN gl a ON (a.id = ac.trans_id)
+ $dpt_join
+ WHERE $where
+ $dpt_from
+ AND NOT (c.link = 'AR' OR c.link = 'AP')
+ $project
+ GROUP BY c.$category
+ |;
+
+ } else {
+#########################################
+# Method eq 'accrual' = Soll Versteuerung
+#########################################