if ($form->{method} eq 'cash') {
$query = qq|
-
+
SELECT g.accno, sum(ac.amount) AS amount,
g.description, c.category
FROM acc_trans ac
)
$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
)
$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
UNION ALL
-- add gl
-
+
SELECT g.accno, sum(ac.amount) AS amount,
g.description, c.category
FROM acc_trans ac
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
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
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
}
$query = qq|
-
+
SELECT g.accno, SUM(ac.amount) AS amount,
g.description, c.category
FROM acc_trans ac
$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
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
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
if ($form->{method} eq 'cash') {
$query = qq|
-
+
SELECT c.accno, sum(ac.amount) AS amount,
c.description, c.category
FROM acc_trans ac
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
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,
if ($form->{project_id}) {
$query .= qq|
-
+
UNION ALL
-
+
SELECT c.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount,
c.description AS description, c.category
FROM invoice ac
GROUP BY c.accno, c.description, c.category
UNION ALL
-
+
SELECT c.accno AS accno, SUM(ac.sellprice) AS amount,
c.description AS description, c.category
FROM invoice ac
}
$query = qq|
-
+
SELECT c.accno, sum(ac.amount) AS amount,
c.description, c.category
FROM acc_trans ac
if ($form->{project_id}) {
$query .= qq|
-
+
UNION ALL
-
+
SELECT c.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount,
c.description AS description, c.category
FROM invoice ac
GROUP BY c.accno, c.description, c.category
UNION ALL
-
+
SELECT c.accno AS accno, SUM(ac.sellprice * ac.qty) * -1 AS amount,
c.description AS description, c.category
FROM invoice ac
if ($form->{method} eq 'cash') {
$query = qq|
-
+
SELECT sum(ac.amount) AS amount,
c.$category
FROM acc_trans ac
WHERE link LIKE '%AR_paid%'
$subwhere
)
-
+
$project
GROUP BY c.$category
-
+
UNION
-
+
SELECT sum(ac.amount) AS amount,
c.$category
FROM acc_trans ac
WHERE link LIKE '%AP_paid%'
$subwhere
)
-
+
$project
GROUP BY c.$category
-
+
UNION
SELECT sum(ac.amount) AS amount,
if ($form->{project_id}) {
$query .= qq|
-
+
UNION
-
+
SELECT SUM(ac.sellprice * ac.qty) AS amount,
c.$category
FROM invoice ac
GROUP BY c.$category
UNION
-
+
SELECT SUM(ac.sellprice) AS amount,
c.$category
FROM invoice ac
}
$query = qq|
-
+
SELECT sum(ac.amount) AS amount,
c.$category
FROM acc_trans ac
if ($form->{project_id}) {
$query .= qq|
-
+
UNION
-
+
SELECT SUM(ac.sellprice * ac.qty) AS amount,
c.$category
FROM invoice ac
GROUP BY c.$category
UNION
-
+
SELECT SUM(ac.sellprice * ac.qty) * -1 AS amount,
c.$category
FROM invoice ac
$query .= qq|
-- add project transactions from invoice
-
+
UNION ALL
-
+
SELECT g.accno, g.description, c.category,
SUM(ac.sellprice * ac.qty) AS amount
FROM invoice ac
GROUP BY g.accno, g.description, c.category
UNION ALL
-
+
SELECT g.accno, g.description, c.category,
SUM(ac.sellprice * ac.qty) * -1 AS amount
FROM invoice ac
$query .= qq|
-- add project transactions from invoice
-
+
UNION ALL
-
+
SELECT c.accno, c.description, c.category,
SUM(ac.sellprice * ac.qty) AS amount
FROM invoice ac
GROUP BY c.accno, c.description, c.category
UNION ALL
-
+
SELECT c.accno, c.description, c.category,
SUM(ac.sellprice * ac.qty) * -1 AS amount
FROM invoice ac
$project
AND ac.amount < 0
AND c.accno = ?) AS debit,
-
+
(SELECT SUM(ac.amount)
FROM acc_trans ac
JOIN chart c ON (c.id = ac.chart_id)
$project
AND ac.amount < 0
AND c.gifi_accno = ?) AS debit,
-
+
(SELECT SUM(ac.amount)
FROM acc_trans ac
JOIN chart c ON (c.id = ac.chart_id)
$dpt_where
$project
AND c.accno = ?) AS debit,
-
+
(SELECT SUM(ac.sellprice * ac.qty)
FROM invoice ac
JOIN parts p ON (ac.parts_id = p.id)
(SELECT $buysell FROM exchangerate
WHERE $form->{arap}.curr = exchangerate.curr
AND exchangerate.transdate = $form->{arap}.transdate) AS exchangerate
- FROM $form->{arap}, $form->{ct}
+ FROM $form->{arap}, $form->{ct}
WHERE paid != amount
AND $form->{arap}.$form->{ct}_id = $form->{ct}.id
AND $form->{ct}.id = $id
AND (
- transdate <= (date '$form->{todate}' - interval '0 days')
+ transdate <= (date '$form->{todate}' - interval '0 days')
AND transdate >= (date '$form->{todate}' - interval '30 days')
)
-
+
UNION
-- between 31-60 days
SELECT $form->{ct}.id AS ctid, $form->{ct}.name,
street, zipcode, city, country, contact, email,
phone as customerphone, fax as customerfax, $form->{ct}number,
- "invnumber", "transdate",
+ "invnumber", "transdate",
0.00 as "c0", (amount - paid) as "c30", 0.00 as "c60", 0.00 as "c90",
"duedate", invoice, $form->{arap}.id,
(SELECT $buysell FROM exchangerate
WHERE $form->{arap}.curr = exchangerate.curr
AND exchangerate.transdate = $form->{arap}.transdate) AS exchangerate
FROM $form->{arap}, $form->{ct}
- WHERE paid != amount
- AND $form->{arap}.$form->{ct}_id = $form->{ct}.id
+ WHERE paid != amount
+ AND $form->{arap}.$form->{ct}_id = $form->{ct}.id
AND $form->{ct}.id = $id
AND (
- transdate < (date '$form->{todate}' - interval '30 days')
+ transdate < (date '$form->{todate}' - interval '30 days')
AND transdate >= (date '$form->{todate}' - interval '60 days')
)
UNION
-
+
-- between 61-90 days
SELECT $form->{ct}.id AS ctid, $form->{ct}.name,
street, zipcode, city, country, contact, email,
phone as customerphone, fax as customerfax, $form->{ct}number,
- "invnumber", "transdate",
+ "invnumber", "transdate",
0.00 as "c0", 0.00 as "c30", (amount - paid) as "c60", 0.00 as "c90",
"duedate", invoice, $form->{arap}.id,
(SELECT $buysell FROM exchangerate
WHERE $form->{arap}.curr = exchangerate.curr
AND exchangerate.transdate = $form->{arap}.transdate) AS exchangerate
- FROM $form->{arap}, $form->{ct}
+ FROM $form->{arap}, $form->{ct}
WHERE paid != amount
- AND $form->{arap}.$form->{ct}_id = $form->{ct}.id
+ AND $form->{arap}.$form->{ct}_id = $form->{ct}.id
AND $form->{ct}.id = $id
AND (
- transdate < (date '$form->{todate}' - interval '60 days')
+ transdate < (date '$form->{todate}' - interval '60 days')
AND transdate >= (date '$form->{todate}' - interval '90 days')
)
UNION
-
+
-- over 90 days
SELECT $form->{ct}.id AS ctid, $form->{ct}.name,
street, zipcode, city, country, contact, email,
phone as customerphone, fax as customerfax, $form->{ct}number,
- "invnumber", "transdate",
+ "invnumber", "transdate",
0.00 as "c0", 0.00 as "c30", 0.00 as "c60", (amount - paid) as "c90",
"duedate", invoice, $form->{arap}.id,
(SELECT $buysell FROM exchangerate
WHERE $form->{arap}.curr = exchangerate.curr
AND exchangerate.transdate = $form->{arap}.transdate) AS exchangerate
- FROM $form->{arap}, $form->{ct}
+ FROM $form->{arap}, $form->{ct}
WHERE paid != amount
- AND $form->{arap}.$form->{ct}_id = $form->{ct}.id
+ AND $form->{arap}.$form->{ct}_id = $form->{ct}.id
AND $form->{ct}.id = $id
- AND transdate < (date '$form->{todate}' - interval '90 days')
+ AND transdate < (date '$form->{todate}' - interval '90 days')
ORDER BY
-
+
ctid, transdate, invnumber
-
+
|;
my $sth = $dbh->prepare($query);
WHERE ac.chart_id = $ref->{id}
$where
$invnumber
-
+
UNION
SELECT g.description, g.reference, NULL AS ordnumber,
ac.transdate, ac.amount * $ml AS paid, ac.source,
$main::lxdebug->leave_sub();
}
1;
-
projects / kivitendo-erp.git / blobdiff