- $query .= qq|
- SELECT g.id, g.reference, g.description, ac.transdate,
- $false AS invoice,
- ac.amount, 'gl' as charttype
- FROM gl g, acc_trans ac
- WHERE $where
- AND ac.chart_id = $id
- AND ac.trans_id = g.id
- UNION ALL
- SELECT a.id, a.invnumber, c.name, ac.transdate,
- a.invoice,
- ac.amount, 'ar' as charttype
- FROM ar a, acc_trans ac, customer c
- WHERE $where
- AND ac.chart_id = $id
- AND ac.trans_id = a.id
- AND a.customer_id = c.id
- UNION ALL
- SELECT a.id, a.invnumber, v.name, ac.transdate,
- a.invoice,
- ac.amount, 'ap' as charttype
- FROM ap a, acc_trans ac, vendor v
- WHERE $where
- AND ac.chart_id = $id
- AND ac.trans_id = a.id
- AND a.vendor_id = v.id
- UNION ALL|;
+ $query .= qq|$union
+ SELECT a.id, a.reference, a.description, ac.transdate,
+ $false AS invoice, ac.amount, 'gl' as module, ac.cleared,
+ '' AS till
+ FROM gl a
+ JOIN acc_trans ac ON (ac.trans_id = a.id)
+ $dpt_join
+ WHERE ac.chart_id = $id
+ $fromdate_where
+ $todate_where
+ $dpt_where
+ $project
+
+ UNION ALL
+
+ SELECT a.id, a.invnumber, c.name, ac.transdate,
+ a.invoice, ac.amount, 'ar' as module, ac.cleared,
+ a.till
+ FROM ar a
+ JOIN acc_trans ac ON (ac.trans_id = a.id)
+ JOIN customer c ON (a.customer_id = c.id)
+ $dpt_join
+ WHERE ac.chart_id = $id
+ $fromdate_where
+ $todate_where
+ $dpt_where
+ $project
+
+ UNION ALL
+
+ SELECT a.id, a.invnumber, v.name, ac.transdate,
+ a.invoice, ac.amount, 'ap' as module, ac.cleared,
+ a.till
+ FROM ap a
+ JOIN acc_trans ac ON (ac.trans_id = a.id)
+ JOIN vendor v ON (a.vendor_id = v.id)
+ $dpt_join
+ WHERE ac.chart_id = $id
+ $fromdate_where
+ $todate_where
+ $dpt_where
+ $project
+ |;
+
+ if ($project_id) {
+
+ $fromdate_where =~ s/ac\./a\./;
+ $todate_where =~ s/ac\./a\./;
+
+ $query .= qq|
+
+ UNION ALL
+
+ -- sold items
+
+ SELECT a.id, a.invnumber, c.name, a.transdate,
+ a.invoice, ac.sellprice * ac.qty, 'ar' as module, '0' AS cleared,
+ a.till
+ FROM ar a
+ JOIN invoice ac ON (ac.trans_id = a.id)
+ JOIN parts p ON (ac.parts_id = p.id)
+ JOIN customer c ON (a.customer_id = c.id)
+ $dpt_join
+ WHERE p.income_accno_id = $id
+ $fromdate_where
+ $todate_where
+ $dpt_where
+ $project
+
+ UNION ALL
+
+ -- bought services
+
+ SELECT a.id, a.invnumber, v.name, a.transdate,
+ a.invoice, ac.sellprice * ac.qty, 'ap' as module, '0' AS cleared,
+ a.till
+ FROM ap a
+ JOIN invoice ac ON (ac.trans_id = a.id)
+ JOIN parts p ON (ac.parts_id = p.id)
+ JOIN vendor v ON (a.vendor_id = v.id)
+ $dpt_join
+ WHERE p.expense_accno_id = $id
+ AND p.inventory_accno_id IS NULL
+ AND p.assembly = '0'
+ $fromdate_where
+ $todate_where
+ $dpt_where
+ $project
+
+ UNION ALL
+
+ -- cogs
+
+ SELECT a.id, a.invnumber, v.name, a.transdate,
+ a.invoice, ac.sellprice * ac.allocated * -1, 'ap' as module, '0' AS cleared,
+ a.till
+ FROM ap a
+ JOIN invoice ac ON (ac.trans_id = a.id)
+ JOIN parts p ON (ac.parts_id = p.id)
+ JOIN vendor v ON (a.vendor_id = v.id)
+ $dpt_join
+ WHERE p.expense_accno_id = $id
+ AND ac.assemblyitem = '0'
+ $fromdate_where
+ $todate_where
+ $dpt_where
+ $project
+
+ |;
+
+ $fromdate_where =~ s/a\./ac\./;
+ $todate_where =~ s/a\./ac\./;
+
+ }
+
+ $union = qq|
+ UNION ALL
+ |;