- if ($form->{accounttype} eq 'gifi') {
-
- if ($form->{method} eq 'cash') {
-
- $query = qq|
-
- SELECT g.accno, sum(ac.amount) AS amount,
- g.description, c.category
- FROM acc_trans ac
- JOIN chart c ON (c.id = ac.chart_id)
- JOIN ar a ON (a.id = ac.trans_id)
- JOIN gifi g ON (g.accno = c.gifi_accno)
- $dpt_join
- WHERE $where
- $dpt_where
- $category
- 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 g.accno, g.description, c.category
-
- UNION ALL
-
- SELECT '' AS accno, SUM(ac.amount) AS amount,
- '' AS description, c.category
- FROM acc_trans ac
- JOIN chart c ON (c.id = ac.chart_id)
- JOIN ar a ON (a.id = ac.trans_id)
- $dpt_join
- WHERE $where
- $dpt_where
- $category
- AND c.gifi_accno = ''
- 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 ALL
-
- SELECT g.accno, sum(ac.amount) AS amount,
- g.description, c.category
- FROM acc_trans ac
- JOIN chart c ON (c.id = ac.chart_id)
- JOIN ap a ON (a.id = ac.trans_id)
- JOIN gifi g ON (g.accno = c.gifi_accno)
- $dpt_join
- WHERE $where
- $dpt_where
- $category
- 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 g.accno, g.description, c.category
-
- UNION ALL
-
- SELECT '' AS accno, SUM(ac.amount) AS amount,
- '' AS description, c.category
- FROM acc_trans ac
- JOIN chart c ON (c.id = ac.chart_id)
- JOIN ap a ON (a.id = ac.trans_id)
- $dpt_join
- WHERE $where
- $dpt_where
- $category
- AND c.gifi_accno = ''
- 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
-
- UNION ALL
-
--- add gl
-
- SELECT g.accno, sum(ac.amount) AS amount,
- g.description, c.category
- FROM acc_trans ac
- JOIN chart c ON (c.id = ac.chart_id)
- JOIN gifi g ON (g.accno = c.gifi_accno)
- JOIN gl a ON (a.id = ac.trans_id)
- $dpt_join
- WHERE $where
- $glwhere
- $dpt_where
- $category
- AND NOT (c.link = 'AR' OR c.link = 'AP')
- $project
- GROUP BY g.accno, g.description, c.category
-
- UNION ALL
-
- SELECT '' AS accno, SUM(ac.amount) AS amount,
- '' AS description, 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
- $glwhere
- $dpt_where
- $category
- AND c.gifi_accno = ''
- AND NOT (c.link = 'AR' OR c.link = 'AP')
- $project
- GROUP BY c.category
- |;
-
- if ($form->{project_id}) {
-
- $query .= qq|
-
- UNION ALL
-
- SELECT g.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount,
- g.description AS description, 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)
- JOIN gifi g ON (g.accno = c.gifi_accno)
- $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 g.accno, g.description, c.category
-
- UNION ALL
-
- SELECT g.accno AS accno, SUM(ac.sellprice * ac.qty) * -1 AS amount,
- g.description AS description, 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)
- JOIN gifi g ON (g.accno = c.gifi_accno)
- $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 g.accno, g.description, c.category
- |;
- }
-
- } else {
-
- if ($department_id) {
- $dpt_join = qq|
- JOIN dpt_trans t ON (t.trans_id = ac.trans_id)
- |;
- $dpt_where = qq|
- AND t.department_id = $department_id
- |;
-
- }
-
- $query = qq|
-
- SELECT g.accno, SUM(ac.amount) AS amount,
- g.description, c.category
- FROM acc_trans ac
- JOIN chart c ON (c.id = ac.chart_id)
- JOIN gifi g ON (c.gifi_accno = g.accno)
- $dpt_join
- WHERE $where
- $dpt_from
- $category
- $project
- GROUP BY g.accno, g.description, c.category
-
- UNION ALL
-
- SELECT '' AS accno, SUM(ac.amount) AS amount,
- '' AS description, c.category
- FROM acc_trans ac
- JOIN chart c ON (c.id = ac.chart_id)
- $dpt_join
- WHERE $where
- $dpt_from
- $category
- AND c.gifi_accno = ''
- $project
- GROUP BY c.category
- |;
-
- if ($form->{project_id}) {
-
- $query .= qq|
-
- UNION ALL
-
- SELECT g.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount,
- g.description AS description, 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)
- JOIN gifi g ON (c.gifi_accno = g.accno)
- $dpt_join
- -- use transdate from subwhere
- WHERE 1 = 1 $subwhere
- AND c.category = 'I'
- $dpt_where
- $project
- GROUP BY g.accno, g.description, c.category
-
- UNION ALL
-
- SELECT g.accno AS accno, SUM(ac.sellprice * ac.qty) * -1 AS amount,
- g.description AS description, 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)
- JOIN gifi g ON (c.gifi_accno = g.accno)
- $dpt_join
- WHERE 1 = 1 $subwhere
- AND c.category = 'E'
- $dpt_where
- $project
- GROUP BY g.accno, g.description, c.category
- |;
- }
+ if ($form->{method} eq 'cash') {
+ $query =
+ qq|SELECT c.accno, sum(ac.amount) AS amount, c.description, c.category
+ FROM acc_trans ac
+ JOIN chart c ON (c.id = ac.chart_id)
+ JOIN ar a ON (a.id = ac.trans_id)
+ $dpt_join
+ WHERE $where
+ $dpt_where
+ $category
+ 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.accno, c.description, c.category
+
+ UNION ALL
+
+ SELECT c.accno, sum(ac.amount) AS amount, c.description, c.category
+ FROM acc_trans ac
+ JOIN chart c ON (c.id = ac.chart_id)
+ JOIN ap a ON (a.id = ac.trans_id)
+ $dpt_join
+ WHERE $where
+ $dpt_where
+ $category
+ 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.accno, c.description, c.category
+
+ UNION ALL
+
+ SELECT c.accno, sum(ac.amount) AS amount, c.description, 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
+ $glwhere
+ $dpt_where
+ $category
+ AND NOT ((c.link = 'AR') OR (c.link = 'AP'))
+ $project
+ GROUP BY c.accno, c.description, c.category |;