- $query .= qq|$union
- SELECT g.id, g.reference, g.description, ac.transdate,
- $false AS invoice, ac.amount, 'gl' as module
- FROM gl g, acc_trans ac $dpt_from
- WHERE $where
- $glwhere
- $dpt_join
- $project
- AND ac.chart_id = $id
- AND ac.trans_id = g.id
- UNION
- SELECT a.id, a.invnumber, c.name, ac.transdate,
- a.invoice, ac.amount, 'ar' as module
- FROM ar a, acc_trans ac, customer c $dpt_from
- WHERE $where
- $dpt_join
- $project
- AND ac.chart_id = $id
- AND ac.trans_id = a.id
- $AR_PAID
- AND a.customer_id = c.id
- UNION
- SELECT a.id, a.invnumber, v.name, ac.transdate,
- a.invoice, ac.amount, 'ap' as module
- FROM ap a, acc_trans ac, vendor v $dpt_from
- WHERE $where
- $dpt_join
- $project
- AND ac.chart_id = $id
- AND ac.trans_id = a.id
- $AP_PAID
- AND a.vendor_id = v.id
- |;
- $union = qq|
- UNION ALL
- |;
+ $query =
+ qq|SELECT a.id, a.reference, a.description, ac.transdate, ac.chart_id, | .
+ qq| $false AS invoice, ac.amount, 'gl' as module, | .
+ qq§(SELECT accno||'--'||rate FROM tax LEFT JOIN chart ON (tax.chart_id=chart.id) WHERE tax.id = (SELECT tax_id FROM taxkeys WHERE taxkey_id = ac.taxkey AND taxkeys.startdate <= ac.transdate ORDER BY taxkeys.startdate DESC LIMIT 1)) AS taxinfo, ac.source || ' ' || ac.memo AS memo § .
+ qq|FROM acc_trans ac, gl a | .
+ $dpt_join .
+ qq|WHERE | . $where . $dpt_where . $project .
+ qq| AND ac.chart_id = ? | .
+ qq| AND ac.trans_id = a.id | .
+ qq| AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) | .
+
+ qq|UNION ALL | .
+
+ qq|SELECT a.id, a.invnumber, c.name, ac.transdate, ac.chart_id, | .
+ qq| a.invoice, ac.amount, 'ar' as module, | .
+ qq§(SELECT accno||'--'||rate FROM tax LEFT JOIN chart ON (tax.chart_id=chart.id) WHERE tax.id = (SELECT tax_id FROM taxkeys WHERE taxkey_id = ac.taxkey AND taxkeys.startdate <= ac.transdate ORDER BY taxkeys.startdate DESC LIMIT 1)) AS taxinfo, ac.source || ' ' || ac.memo AS memo § .
+ qq|FROM acc_trans ac, customer c, ar a | .
+ $dpt_join .
+ qq|WHERE | . $where . $dpt_where . $project .
+ qq| AND ac.chart_id = ? | .
+ qq| AND ac.trans_id = a.id | .
+ qq| AND a.customer_id = c.id | .
+ qq| AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) $ar_ap_cash_where| .
+
+ qq|UNION ALL | .
+
+ qq|SELECT a.id, a.invnumber, v.name, ac.transdate, ac.chart_id, | .
+ qq| a.invoice, ac.amount, 'ap' as module, | .
+ qq§(SELECT accno||'--'||rate FROM tax LEFT JOIN chart ON (tax.chart_id=chart.id) WHERE tax.id = (SELECT tax_id FROM taxkeys WHERE taxkey_id = ac.taxkey AND taxkeys.startdate <= ac.transdate ORDER BY taxkeys.startdate DESC LIMIT 1)) AS taxinfo, ac.source || ' ' || ac.memo AS memo § .
+ qq|FROM acc_trans ac, vendor v, ap a | .
+ $dpt_join .
+ qq|WHERE | . $where . $dpt_where . $project .
+ qq| AND ac.chart_id = ? | .
+ qq| AND ac.trans_id = a.id | .
+ qq| AND a.vendor_id = v.id |;
+ qq| AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) $ar_ap_cash_where| .
+
+ push(@values,
+ @where_values, @department_values, @project_values, $id,
+ @where_values, @department_values, @project_values, $id,
+ @where_values, @department_values, @project_values, $id);
+
+ $union = qq|UNION ALL |;