Detailed Project Income and Expense Statement Report.sql

SELECT
    'Income' AS `Type`,
    gl.voucher_type AS `Document Type`,
    gl.voucher_no AS `Document Number`,
    gl.account AS `Account`,
    SUM(gl.credit - gl.debit) AS `Amount`, -- Adjusted to show credit as positive
    gl.remarks AS `Remarks`,
    1 AS `Order`
FROM
    `tabGL Entry` AS gl
WHERE
    gl.account LIKE '4%%' AND
    gl.project = %(project)s AND
    gl.docstatus = 1 -- Excludes canceled documents
GROUP BY
    gl.voucher_type, gl.voucher_no, gl.account

UNION ALL

SELECT
    'Total Income' AS `Type`,
    '' AS `Document Type`,
    '' AS `Document Number`,
    '' AS `Account`,
    SUM(gl.credit - gl.debit) AS `Amount`,
    '' AS `Remarks`,
    2 AS `Order`
FROM
    `tabGL Entry` AS gl
WHERE
    gl.account LIKE '4%%' AND
    gl.project = %(project)s AND
    gl.docstatus = 1 -- Excludes canceled documents

UNION ALL

SELECT
    'Expense' AS `Type`,
    gl.voucher_type AS `Document Type`,
    gl.voucher_no AS `Document Number`,
    gl.account AS `Account`,
    SUM(gl.debit - gl.credit) AS `Amount`,
    gl.remarks AS `Remarks`,
    3 AS `Order`
FROM
    `tabGL Entry` AS gl
WHERE
    (gl.account LIKE '5%%' OR gl.account LIKE '6%%') AND
    gl.project = %(project)s AND
    gl.docstatus = 1 -- Excludes canceled documents
GROUP BY
    gl.voucher_type, gl.voucher_no, gl.account

UNION ALL

SELECT
    'Total Expenses' AS `Type`,
    '' AS `Document Type`,
    '' AS `Document Number`,
    '' AS `Account`,
    SUM(gl.debit - gl.credit) AS `Amount`,
    '' AS `Remarks`,
    4 AS `Order`
FROM
    `tabGL Entry` AS gl
WHERE
    (gl.account LIKE '5%%' OR gl.account LIKE '6%%') AND
    gl.project = %(project)s AND
    gl.docstatus = 1 -- Excludes canceled documents

UNION ALL

SELECT
    'Result' AS `Type`,
    '' AS `Document Type`,
    '' AS `Document Number`,
    '' AS `Account`,
    SUM(IF(gl.account LIKE '4%%', gl.credit - gl.debit, 0) - IF(gl.account LIKE '5%%' OR gl.account LIKE '6%%', gl.debit - gl.credit, 0)) AS `Amount`,
    '' AS `Remarks`,
    5 AS `Order`
FROM
    `tabGL Entry` AS gl
WHERE
    gl.project = %(project)s AND
    gl.docstatus = 1 -- Excludes canceled documents