Projects in Process Report 251023
250901-01
(SO) Earliest SO DATE, replaced by (PROJ) Estimated Start Date
SELECT
p.name AS "PROJECT ID:Link/Project:140",
p.department AS "Department:Link/Department:150",
p.customer AS "Customer:Link/Customer:200",
e.so_name AS "Earliest SO:Link/Sales Order:160",
p.expected_start_date AS "Estimated Start Date:Date:110",
COALESCE(si.si_items_total_base, 0) AS "Total SI Items (Base, by Project):Currency:180",
COALESCE(pt.pi_total, 0) AS "Total Purchase Invoices (Grand Total):Currency:170",
COALESCE(mr.mr_amount, 0) AS "Project MR (from Stores):Currency:160",
COALESCE(mrs.mrs_amount, 0) AS "Project MRS (to Stores):Currency:160",
COALESCE(ts.ts_costing_amount, 0) AS "Timesheets Costing Amount (by Project):Currency:180",
(
COALESCE(si.si_items_total_base, 0)
- (COALESCE(pt.pi_total, 0) + COALESCE(mr.mr_amount, 0) + COALESCE(ts.ts_costing_amount, 0))
) AS "Remainder (SI - PI - MR - TS):Currency:160",
(
SELECT IFNULL(GROUP_CONCAT(IFNULL(soi.item_name, soi.item_code) ORDER BY soi.idx SEPARATOR ', '), '')
FROM `tabSales Order Item` soi
WHERE soi.parent = e.so_name
) AS "Sales Order Items (List):Data:300"
FROM `tabProject` p
LEFT JOIN (
SELECT
p2.name AS project,
(
SELECT so2.name
FROM `tabSales Order` so2
WHERE so2.company = p2.company
AND (
so2.project = p2.name
OR EXISTS (
SELECT 1 FROM `tabSales Order Item` soi2
WHERE soi2.parent = so2.name AND soi2.project = p2.name
)
)
ORDER BY so2.transaction_date ASC, so2.name ASC
LIMIT 1
) AS so_name,
(
SELECT MIN(so3.transaction_date)
FROM `tabSales Order` so3
WHERE so3.company = p2.company
AND (
so3.project = p2.name
OR EXISTS (
SELECT 1 FROM `tabSales Order Item` soi3
WHERE soi3.parent = so3.name AND soi3.project = p2.name
)
)
) AS so_date
FROM `tabProject` p2
WHERE p2.company = %(company)s
AND ( %(project_like)s = '' OR p2.name LIKE %(project_like)s )
) e ON e.project = p.name
LEFT JOIN (
SELECT t.project, SUM(t.grand_total) AS pi_total
FROM (
SELECT DISTINCT pi.name, pii.project, pi.grand_total
FROM `tabPurchase Invoice` pi
JOIN `tabPurchase Invoice Item` pii ON pi.name = pii.parent
WHERE pi.docstatus = 1
AND pi.company = %(company)s
AND pii.project IS NOT NULL AND pii.project != ''
AND ( %(project_like)s = '' OR pii.project LIKE %(project_like)s )
) t
GROUP BY t.project
) pt ON pt.project = p.name
LEFT JOIN (
SELECT
COALESCE(NULLIF(sii.project, ''), '<<NO PROJECT>>') AS project,
SUM(sii.base_net_amount) AS si_items_total_base,
SUM(sii.net_amount) AS si_items_total_txn
FROM `tabSales Invoice` si
JOIN `tabSales Invoice Item` sii ON sii.parent = si.name
WHERE si.docstatus = 1
AND si.company = %(company)s
AND ( %(project_like)s = '' OR sii.project LIKE %(project_like)s )
GROUP BY COALESCE(NULLIF(sii.project, ''), '<<NO PROJECT>>')
) si ON si.project = p.name
LEFT JOIN (
SELECT
COALESCE(NULLIF(tsd.project, ''), '<<NO PROJECT>>') AS project,
SUM(tsd.costing_amount) AS ts_costing_amount
FROM `tabTimesheet Detail` tsd
JOIN `tabTimesheet` ts ON ts.name = tsd.parent
WHERE ts.docstatus = 1
AND ts.company = %(company)s
AND ( %(project_like)s = '' OR tsd.project LIKE %(project_like)s )
GROUP BY COALESCE(NULLIF(tsd.project, ''), '<<NO PROJECT>>')
) ts ON ts.project = p.name
LEFT JOIN (
SELECT
COALESCE(NULLIF(COALESCE(sed.project, se.project), ''), '<<NO PROJECT>>') AS project,
SUM(IFNULL(sed.basic_amount, 0)) AS mr_amount
FROM `tabStock Entry` se
JOIN `tabStock Entry Detail` sed ON sed.parent = se.name
WHERE se.docstatus = 1
AND se.company = %(company)s
AND ( %(project_like)s = '' OR COALESCE(sed.project, se.project) LIKE %(project_like)s )
AND IFNULL(sed.s_warehouse, '') LIKE '%%Stores%%'
GROUP BY COALESCE(NULLIF(COALESCE(sed.project, se.project), ''), '<<NO PROJECT>>')
) mr ON mr.project = p.name
LEFT JOIN (
SELECT
COALESCE(NULLIF(COALESCE(sed.project, se.project), ''), '<<NO PROJECT>>') AS project,
SUM(IFNULL(sed.basic_amount, 0)) AS mrs_amount
FROM `tabStock Entry` se
JOIN `tabStock Entry Detail` sed ON sed.parent = se.name
WHERE se.docstatus = 1
AND se.company = %(company)s
AND ( %(project_like)s = '' OR COALESCE(sed.project, se.project) LIKE %(project_like)s )
AND IFNULL(sed.t_warehouse, '') LIKE '%%Stores%%'
GROUP BY COALESCE(NULLIF(COALESCE(sed.project, se.project), ''), '<<NO PROJECT>>')
) mrs ON mrs.project = p.name
WHERE p.company = %(company)s
AND ( %(project_like)s = '' OR p.name LIKE %(project_like)s )
ORDER BY p.name
250901
SELECT
p.name AS "PROJECT ID:Link/Project:140",
p.department AS "Department:Link/Department:150",
p.customer AS "Customer:Link/Customer:200",
e.so_name AS "Earliest SO:Link/Sales Order:160",
e.so_date AS "Earliest SO Date:Date:110",
COALESCE(si.si_items_total_base, 0) AS "Total SI Items (Base, by Project):Currency:180",
COALESCE(pt.pi_total, 0) AS "Total Purchase Invoices (Grand Total):Currency:170",
COALESCE(mr.mr_amount, 0) AS "Project MR (from Stores):Currency:160",
COALESCE(mrs.mrs_amount, 0) AS "Project MRS (to Stores):Currency:160",
COALESCE(ts.ts_costing_amount, 0) AS "Timesheets Costing Amount (by Project):Currency:180",
(
COALESCE(si.si_items_total_base, 0)
- (COALESCE(pt.pi_total, 0) + COALESCE(mr.mr_amount, 0) + COALESCE(ts.ts_costing_amount, 0))
) AS "Remainder (SI - PI - MR - TS):Currency:160",
(
SELECT IFNULL(GROUP_CONCAT(IFNULL(soi.item_name, soi.item_code) ORDER BY soi.idx SEPARATOR ', '), '')
FROM `tabSales Order Item` soi
WHERE soi.parent = e.so_name
) AS "Sales Order Items (List):Data:300"
FROM `tabProject` p
LEFT JOIN (
SELECT
p2.name AS project,
(
SELECT so2.name
FROM `tabSales Order` so2
WHERE so2.company = p2.company
AND (
so2.project = p2.name
OR EXISTS (
SELECT 1 FROM `tabSales Order Item` soi2
WHERE soi2.parent = so2.name AND soi2.project = p2.name
)
)
ORDER BY so2.transaction_date ASC, so2.name ASC
LIMIT 1
) AS so_name,
(
SELECT MIN(so3.transaction_date)
FROM `tabSales Order` so3
WHERE so3.company = p2.company
AND (
so3.project = p2.name
OR EXISTS (
SELECT 1 FROM `tabSales Order Item` soi3
WHERE soi3.parent = so3.name AND soi3.project = p2.name
)
)
) AS so_date
FROM `tabProject` p2
WHERE p2.company = %(company)s
AND ( %(project_like)s = '' OR p2.name LIKE %(project_like)s )
) e ON e.project = p.name
LEFT JOIN (
SELECT t.project, SUM(t.grand_total) AS pi_total
FROM (
SELECT DISTINCT pi.name, pii.project, pi.grand_total
FROM `tabPurchase Invoice` pi
JOIN `tabPurchase Invoice Item` pii ON pi.name = pii.parent
WHERE pi.docstatus = 1
AND pi.company = %(company)s
AND pii.project IS NOT NULL AND pii.project != ''
AND ( %(project_like)s = '' OR pii.project LIKE %(project_like)s )
) t
GROUP BY t.project
) pt ON pt.project = p.name
LEFT JOIN (
SELECT
COALESCE(NULLIF(sii.project, ''), '<<NO PROJECT>>') AS project,
SUM(sii.base_net_amount) AS si_items_total_base,
SUM(sii.net_amount) AS si_items_total_txn
FROM `tabSales Invoice` si
JOIN `tabSales Invoice Item` sii ON sii.parent = si.name
WHERE si.docstatus = 1
AND si.company = %(company)s
AND ( %(project_like)s = '' OR sii.project LIKE %(project_like)s )
GROUP BY COALESCE(NULLIF(sii.project, ''), '<<NO PROJECT>>')
) si ON si.project = p.name
LEFT JOIN (
SELECT
COALESCE(NULLIF(tsd.project, ''), '<<NO PROJECT>>') AS project,
SUM(tsd.costing_amount) AS ts_costing_amount
FROM `tabTimesheet Detail` tsd
JOIN `tabTimesheet` ts ON ts.name = tsd.parent
WHERE ts.docstatus = 1
AND ts.company = %(company)s
AND ( %(project_like)s = '' OR tsd.project LIKE %(project_like)s )
GROUP BY COALESCE(NULLIF(tsd.project, ''), '<<NO PROJECT>>')
) ts ON ts.project = p.name
LEFT JOIN (
SELECT
COALESCE(NULLIF(COALESCE(sed.project, se.project), ''), '<<NO PROJECT>>') AS project,
SUM(IFNULL(sed.basic_amount, 0)) AS mr_amount
FROM `tabStock Entry` se
JOIN `tabStock Entry Detail` sed ON sed.parent = se.name
WHERE se.docstatus = 1
AND se.company = %(company)s
AND ( %(project_like)s = '' OR COALESCE(sed.project, se.project) LIKE %(project_like)s )
AND IFNULL(sed.s_warehouse, '') LIKE '%%Stores%%'
GROUP BY COALESCE(NULLIF(COALESCE(sed.project, se.project), ''), '<<NO PROJECT>>')
) mr ON mr.project = p.name
LEFT JOIN (
SELECT
COALESCE(NULLIF(COALESCE(sed.project, se.project), ''), '<<NO PROJECT>>') AS project,
SUM(IFNULL(sed.basic_amount, 0)) AS mrs_amount
FROM `tabStock Entry` se
JOIN `tabStock Entry Detail` sed ON sed.parent = se.name
WHERE se.docstatus = 1
AND se.company = %(company)s
AND ( %(project_like)s = '' OR COALESCE(sed.project, se.project) LIKE %(project_like)s )
AND IFNULL(sed.t_warehouse, '') LIKE '%%Stores%%'
GROUP BY COALESCE(NULLIF(COALESCE(sed.project, se.project), ''), '<<NO PROJECT>>')
) mrs ON mrs.project = p.name
WHERE p.company = %(company)s
AND ( %(project_like)s = '' OR p.name LIKE %(project_like)s )
ORDER BY p.name
SELECT
p.name AS "PROJECT ID:Link/Project:140",
p.department AS "Department:Link/Department:150",
p.customer AS "Customer:Link/Customer:200",
e.so_name AS "Earliest SO:Link/Sales Order:160",
e.so_date AS "Earliest SO Date:Date:110",
COALESCE(si.si_items_total_base, 0) AS "Total SI Items (Base, by Project):Currency:180",
COALESCE(pt.pi_total, 0) AS "Total Purchase Invoices (Grand Total):Currency:170",
COALESCE(mr.mr_amount, 0) AS "Project MR (from Stores):Currency:160",
COALESCE(mrs.mrs_amount, 0) AS "Project MRS (to Stores):Currency:160",
COALESCE(ts.ts_costing_amount, 0) AS "Timesheets Costing Amount (by Project):Currency:180",
(
COALESCE(si.si_items_total_base, 0)
- (COALESCE(pt.pi_total, 0) + COALESCE(mr.mr_amount, 0) + COALESCE(ts.ts_costing_amount, 0))
) AS "Remainder (SI - PI - MR - TS):Currency:160",
(
SELECT IFNULL(GROUP_CONCAT(IFNULL(soi.item_name, soi.item_code) ORDER BY soi.idx SEPARATOR ', '), '')
FROM `tabSales Order Item` soi
WHERE soi.parent = e.so_name
) AS "Sales Order Items (List):Data:300"
FROM `tabProject` p
LEFT JOIN (
SELECT
p2.name AS project,
(
SELECT so2.name
FROM `tabSales Order` so2
WHERE so2.company = p2.company
AND (
so2.project = p2.name
OR EXISTS (
SELECT 1 FROM `tabSales Order Item` soi2
WHERE soi2.parent = so2.name AND soi2.project = p2.name
)
)
ORDER BY so2.transaction_date ASC, so2.name ASC
LIMIT 1
) AS so_name,
(
SELECT MIN(so3.transaction_date)
FROM `tabSales Order` so3
WHERE so3.company = p2.company
AND (
so3.project = p2.name
OR EXISTS (
SELECT 1 FROM `tabSales Order Item` soi3
WHERE soi3.parent = so3.name AND soi3.project = p2.name
)
)
) AS so_date
FROM `tabProject` p2
WHERE p2.company = %(company)s
AND ( %(project_like)s = '' OR p2.name LIKE %(project_like)s )
) e ON e.project = p.name
LEFT JOIN (
SELECT t.project, SUM(t.grand_total) AS pi_total
FROM (
SELECT DISTINCT pi.name, pii.project, pi.grand_total
FROM `tabPurchase Invoice` pi
JOIN `tabPurchase Invoice Item` pii ON pi.name = pii.parent
WHERE pi.docstatus = 1
AND pi.company = %(company)s
AND pii.project IS NOT NULL AND pii.project != ''
AND ( %(project_like)s = '' OR pii.project LIKE %(project_like)s )
) t
GROUP BY t.project
) pt ON pt.project = p.name
LEFT JOIN (
SELECT
COALESCE(NULLIF(sii.project, ''), '<<NO PROJECT>>') AS project,
SUM(sii.base_net_amount) AS si_items_total_base,
SUM(sii.net_amount) AS si_items_total_txn
FROM `tabSales Invoice` si
JOIN `tabSales Invoice Item` sii ON sii.parent = si.name
WHERE si.docstatus = 1
AND si.company = %(company)s
AND ( %(project_like)s = '' OR sii.project LIKE %(project_like)s )
GROUP BY COALESCE(NULLIF(sii.project, ''), '<<NO PROJECT>>')
) si ON si.project = p.name
LEFT JOIN (
SELECT
COALESCE(NULLIF(tsd.project, ''), '<<NO PROJECT>>') AS project,
SUM(tsd.costing_amount) AS ts_costing_amount
FROM `tabTimesheet Detail` tsd
JOIN `tabTimesheet` ts ON ts.name = tsd.parent
WHERE ts.docstatus = 1
AND ts.company = %(company)s
AND ( %(project_like)s = '' OR tsd.project LIKE %(project_like)s )
GROUP BY COALESCE(NULLIF(tsd.project, ''), '<<NO PROJECT>>')
) ts ON ts.project = p.name
LEFT JOIN (
SELECT
COALESCE(NULLIF(COALESCE(sed.project, se.project), ''), '<<NO PROJECT>>') AS project,
SUM(IFNULL(sed.basic_amount, 0)) AS mr_amount
FROM `tabStock Entry` se
JOIN `tabStock Entry Detail` sed ON sed.parent = se.name
WHERE se.docstatus = 1
AND se.company = %(company)s
AND ( %(project_like)s = '' OR COALESCE(sed.project, se.project) LIKE %(project_like)s )
AND IFNULL(sed.s_warehouse, '') LIKE '%%Stores%%'
GROUP BY COALESCE(NULLIF(COALESCE(sed.project, se.project), ''), '<<NO PROJECT>>')
) mr ON mr.project = p.name
LEFT JOIN (
SELECT
COALESCE(NULLIF(COALESCE(sed.project, se.project), ''), '<<NO PROJECT>>') AS project,
SUM(IFNULL(sed.basic_amount, 0)) AS mrs_amount
FROM `tabStock Entry` se
JOIN `tabStock Entry Detail` sed ON sed.parent = se.name
WHERE se.docstatus = 1
AND se.company = %(company)s
AND ( %(project_like)s = '' OR COALESCE(sed.project, se.project) LIKE %(project_like)s )
AND IFNULL(sed.t_warehouse, '') LIKE '%%Stores%%'
GROUP BY COALESCE(NULLIF(COALESCE(sed.project, se.project), ''), '<<NO PROJECT>>')
) mrs ON mrs.project = p.name
WHERE p.company = %(company)s
AND ( %(project_like)s = '' OR p.name LIKE %(project_like)s )
ORDER BY p.name
MRS colum is supposed to be the following Take the Stock entries with the same Project. filter by Project. MRS is Target Warehouse should be Stores sum all these MRS/Returns. Next Add another Column called Remainder It should be SI Items less Total Purchase Invoice Col, Project MR Col Timesheet Costing
SELECT
p.name AS "PROJECT ID:Link/Project:140",
p.department AS "Department:Link/Department:150",
p.customer AS "Customer:Link/Customer:200",
e.so_name AS "Earliest SO:Link/Sales Order:160",
e.so_date AS "Earliest SO Date:Date:110",
COALESCE(si.si_items_total_base, 0) AS "Total SI Items (Base, by Project):Currency:180",
COALESCE(pt.pi_total, 0) AS "Total Purchase Invoices (Grand Total):Currency:170",
COALESCE(mr.mr_amount, 0) AS "Project MR (from Stores):Currency:160",
COALESCE(mrs.mrs_amount, 0) AS "Project MRS (to Any Target WH):Currency:160",
COALESCE(ts.ts_costing_amount, 0) AS "Timesheets Costing Amount (by Project):Currency:180",
(
SELECT IFNULL(GROUP_CONCAT(IFNULL(soi.item_name, soi.item_code) ORDER BY soi.idx SEPARATOR ', '), '')
FROM `tabSales Order Item` soi
WHERE soi.parent = e.so_name
) AS "Sales Order Items (List):Data:300"
FROM `tabProject` p
LEFT JOIN (
SELECT
p2.name AS project,
(
SELECT so2.name
FROM `tabSales Order` so2
WHERE so2.company = p2.company
AND (
so2.project = p2.name
OR EXISTS (
SELECT 1 FROM `tabSales Order Item` soi2
WHERE soi2.parent = so2.name AND soi2.project = p2.name
)
)
ORDER BY so2.transaction_date ASC, so2.name ASC
LIMIT 1
) AS so_name,
(
SELECT MIN(so3.transaction_date)
FROM `tabSales Order` so3
WHERE so3.company = p2.company
AND (
so3.project = p2.name
OR EXISTS (
SELECT 1 FROM `tabSales Order Item` soi3
WHERE soi3.parent = so3.name AND soi3.project = p2.name
)
)
) AS so_date
FROM `tabProject` p2
WHERE p2.company = %(company)s
AND ( %(project_like)s = '' OR p2.name LIKE %(project_like)s )
) e ON e.project = p.name
LEFT JOIN (
SELECT t.project, SUM(t.grand_total) AS pi_total
FROM (
SELECT DISTINCT pi.name, pii.project, pi.grand_total
FROM `tabPurchase Invoice` pi
JOIN `tabPurchase Invoice Item` pii ON pi.name = pii.parent
WHERE pi.docstatus = 1
AND pi.company = %(company)s
AND pii.project IS NOT NULL AND pii.project != ''
AND ( %(project_like)s = '' OR pii.project LIKE %(project_like)s )
) t
GROUP BY t.project
) pt ON pt.project = p.name
LEFT JOIN (
SELECT
COALESCE(NULLIF(sii.project, ''), '<<NO PROJECT>>') AS project,
SUM(sii.base_net_amount) AS si_items_total_base,
SUM(sii.net_amount) AS si_items_total_txn
FROM `tabSales Invoice` si
JOIN `tabSales Invoice Item` sii ON sii.parent = si.name
WHERE si.docstatus = 1
AND si.company = %(company)s
AND ( %(project_like)s = '' OR sii.project LIKE %(project_like)s )
GROUP BY COALESCE(NULLIF(sii.project, ''), '<<NO PROJECT>>')
) si ON si.project = p.name
LEFT JOIN (
SELECT
COALESCE(NULLIF(tsd.project, ''), '<<NO PROJECT>>') AS project,
SUM(tsd.costing_amount) AS ts_costing_amount
FROM `tabTimesheet Detail` tsd
JOIN `tabTimesheet` ts ON ts.name = tsd.parent
WHERE ts.docstatus = 1
AND ts.company = %(company)s
AND ( %(project_like)s = '' OR tsd.project LIKE %(project_like)s )
GROUP BY COALESCE(NULLIF(tsd.project, ''), '<<NO PROJECT>>')
) ts ON ts.project = p.name
LEFT JOIN (
SELECT
COALESCE(NULLIF(COALESCE(sed.project, se.project), ''), '<<NO PROJECT>>') AS project,
SUM(IFNULL(sed.basic_amount, 0)) AS mr_amount
FROM `tabStock Entry` se
JOIN `tabStock Entry Detail` sed ON sed.parent = se.name
WHERE se.docstatus = 1
AND se.company = %(company)s
AND ( %(project_like)s = '' OR COALESCE(sed.project, se.project) LIKE %(project_like)s )
AND IFNULL(sed.s_warehouse, '') LIKE '%%Stores%%'
GROUP BY COALESCE(NULLIF(COALESCE(sed.project, se.project), ''), '<<NO PROJECT>>')
) mr ON mr.project = p.name
LEFT JOIN (
SELECT
COALESCE(NULLIF(COALESCE(sed.project, se.project), ''), '<<NO PROJECT>>') AS project,
SUM(IFNULL(sed.basic_amount, 0)) AS mrs_amount
FROM `tabStock Entry` se
JOIN `tabStock Entry Detail` sed ON sed.parent = se.name
WHERE se.docstatus = 1
AND se.company = %(company)s
AND ( %(project_like)s = '' OR COALESCE(sed.project, se.project) LIKE %(project_like)s )
AND IFNULL(sed.t_warehouse, '') != ''
GROUP BY COALESCE(NULLIF(COALESCE(sed.project, se.project), ''), '<<NO PROJECT>>')
) mrs ON mrs.project = p.name
WHERE p.company = %(company)s
AND ( %(project_like)s = '' OR p.name LIKE %(project_like)s )
ORDER BY p.name
TImesheet Amount is now enabled.
Next is to do the following -
remove the Project Stock Value
Replace it with Project MR - this is all the Amount Stock Entries that has the same Accounting Dimensions: Project, Source Warehouse (Stock Entry Detail) %Stores%. This tracks all the withdrawals from stock on hand for the project's costs.
Next is have MRS - this the sum of the Amount of all the Stock Entries of the Project that have Target Warehouse (Stock Entry Detail)
SELECT
p.name AS "PROJECT ID:Link/Project:140",
p.department AS "Department:Link/Department:150",
p.customer AS "Customer:Link/Customer:200",
e.so_name AS "Earliest SO:Link/Sales Order:160",
e.so_date AS "Earliest SO Date:Date:110",
COALESCE(si.si_items_total_base, 0) AS "Total SI Items (Base, by Project):Currency:180",
COALESCE(pt.pi_total, 0) AS "Total Purchase Invoices (Grand Total):Currency:170",
COALESCE(av.val_asof, 0) AS "Project Stock Value (As of Last Movement):Currency:180",
COALESCE(ts.ts_costing_amount, 0) AS "Timesheets Costing Amount (by Project):Currency:180",
(
SELECT IFNULL(GROUP_CONCAT(IFNULL(soi.item_name, soi.item_code) ORDER BY soi.idx SEPARATOR ', '), '')
FROM `tabSales Order Item` soi
WHERE soi.parent = e.so_name
) AS "Sales Order Items (List):Data:300"
FROM `tabProject` p
LEFT JOIN (
SELECT
p2.name AS project,
(
SELECT so2.name
FROM `tabSales Order` so2
WHERE so2.company = p2.company
AND (
so2.project = p2.name
OR EXISTS (
SELECT 1 FROM `tabSales Order Item` soi2
WHERE soi2.parent = so2.name AND soi2.project = p2.name
)
)
ORDER BY so2.transaction_date ASC, so2.name ASC
LIMIT 1
) AS so_name,
(
SELECT MIN(so3.transaction_date)
FROM `tabSales Order` so3
WHERE so3.company = p2.company
AND (
so3.project = p2.name
OR EXISTS (
SELECT 1 FROM `tabSales Order Item` soi3
WHERE soi3.parent = so3.name AND soi3.project = p2.name
)
)
) AS so_date
FROM `tabProject` p2
WHERE p2.company = %(company)s
AND ( %(project_like)s = '' OR p2.name LIKE %(project_like)s )
) e ON e.project = p.name
LEFT JOIN (
SELECT t.project, SUM(t.grand_total) AS pi_total
FROM (
SELECT DISTINCT pi.name, pii.project, pi.grand_total
FROM `tabPurchase Invoice` pi
JOIN `tabPurchase Invoice Item` pii ON pi.name = pii.parent
WHERE pi.docstatus = 1
AND pi.company = %(company)s
AND pii.project IS NOT NULL AND pii.project != ''
AND ( %(project_like)s = '' OR pii.project LIKE %(project_like)s )
) t
GROUP BY t.project
) pt ON pt.project = p.name
LEFT JOIN (
SELECT pw.project, SUM(sle.stock_value) AS val_asof
FROM (
SELECT p3.name AS project, w.name AS wh
FROM `tabProject` p3
JOIN `tabWarehouse` w
ON w.company = p3.company
AND w.`custom_project` = p3.name
WHERE p3.company = %(company)s
AND ( %(project_like)s = '' OR p3.name LIKE %(project_like)s )
) pw
JOIN (
SELECT x.item_code, x.warehouse, IFNULL(x.batch_no, '') AS batch_no,
MAX(CONCAT(x.posting_date, ' ', x.posting_time, ' ', x.name)) AS maxkey
FROM `tabStock Ledger Entry` x
WHERE x.is_cancelled = 0
GROUP BY x.item_code, x.warehouse, IFNULL(x.batch_no, '')
) last ON last.warehouse = pw.wh
JOIN `tabStock Ledger Entry` sle
ON sle.item_code = last.item_code
AND sle.warehouse = last.warehouse
AND IFNULL(sle.batch_no, '') = last.batch_no
AND CONCAT(sle.posting_date, ' ', sle.posting_time, ' ', sle.name) = last.maxkey
GROUP BY pw.project
) av ON av.project = p.name
LEFT JOIN (
SELECT
COALESCE(NULLIF(sii.project, ''), '<<NO PROJECT>>') AS project,
SUM(sii.base_net_amount) AS si_items_total_base,
SUM(sii.net_amount) AS si_items_total_txn
FROM `tabSales Invoice` si
JOIN `tabSales Invoice Item` sii ON sii.parent = si.name
WHERE si.docstatus = 1
AND si.company = %(company)s
AND ( %(project_like)s = '' OR sii.project LIKE %(project_like)s )
GROUP BY COALESCE(NULLIF(sii.project, ''), '<<NO PROJECT>>')
) si ON si.project = p.name
LEFT JOIN (
SELECT
COALESCE(NULLIF(tsd.project, ''), '<<NO PROJECT>>') AS project,
SUM(tsd.costing_amount) AS ts_costing_amount
FROM `tabTimesheet Detail` tsd
JOIN `tabTimesheet` ts ON ts.name = tsd.parent
WHERE ts.docstatus = 1
AND ts.company = %(company)s
AND ( %(project_like)s = '' OR tsd.project LIKE %(project_like)s )
GROUP BY COALESCE(NULLIF(tsd.project, ''), '<<NO PROJECT>>')
) ts ON ts.project = p.name
WHERE p.company = %(company)s
AND ( %(project_like)s = '' OR p.name LIKE %(project_like)s )
ORDER BY p.name
240829 it works. Changed the filters to use Projects Wildcards. This is because we made a mistake with the dates. It uses PROJ-24% wild cards
SELECT
p.name AS "PROJECT ID:Link/Project:140",
p.department AS "Department:Link/Department:150",
p.customer AS "Customer:Link/Customer:200",
e.so_name AS "Earliest SO:Link/Sales Order:160",
e.so_date AS "Earliest SO Date:Date:110",
COALESCE(si.si_items_total_base, 0) AS "Total SI Items (Base, by Project):Currency:180",
COALESCE(pt.pi_total, 0) AS "Total Purchase Invoices (Grand Total):Currency:170",
COALESCE(av.val_asof, 0) AS "Project Stock Value (As of Last Movement):Currency:180",
(
SELECT IFNULL(GROUP_CONCAT(IFNULL(soi.item_name, soi.item_code) ORDER BY soi.idx SEPARATOR ', '), '')
FROM `tabSales Order Item` soi
WHERE soi.parent = e.so_name
) AS "Sales Order Items (List):Data:300"
FROM `tabProject` p
LEFT JOIN (
SELECT
p2.name AS project,
(
SELECT so2.name
FROM `tabSales Order` so2
WHERE so2.company = p2.company
AND (
so2.project = p2.name
OR EXISTS (
SELECT 1 FROM `tabSales Order Item` soi2
WHERE soi2.parent = so2.name AND soi2.project = p2.name
)
)
ORDER BY so2.transaction_date ASC, so2.name ASC
LIMIT 1
) AS so_name,
(
SELECT MIN(so3.transaction_date)
FROM `tabSales Order` so3
WHERE so3.company = p2.company
AND (
so3.project = p2.name
OR EXISTS (
SELECT 1 FROM `tabSales Order Item` soi3
WHERE soi3.parent = so3.name AND soi3.project = p2.name
)
)
) AS so_date
FROM `tabProject` p2
WHERE p2.company = %(company)s
AND ( %(project_like)s = '' OR p2.name LIKE %(project_like)s )
) e ON e.project = p.name
LEFT JOIN (
SELECT t.project, SUM(t.grand_total) AS pi_total
FROM (
SELECT DISTINCT pi.name, pii.project, pi.grand_total
FROM `tabPurchase Invoice` pi
JOIN `tabPurchase Invoice Item` pii ON pi.name = pii.parent
WHERE pi.docstatus = 1
AND pi.company = %(company)s
AND pii.project IS NOT NULL AND pii.project != ''
AND ( %(project_like)s = '' OR pii.project LIKE %(project_like)s )
) t
GROUP BY t.project
) pt ON pt.project = p.name
LEFT JOIN (
SELECT pw.project, SUM(sle.stock_value) AS val_asof
FROM (
SELECT p3.name AS project, w.name AS wh
FROM `tabProject` p3
JOIN `tabWarehouse` w
ON w.company = p3.company
AND w.`custom_project` = p3.name
WHERE p3.company = %(company)s
AND ( %(project_like)s = '' OR p3.name LIKE %(project_like)s )
) pw
JOIN (
SELECT x.item_code, x.warehouse, IFNULL(x.batch_no, '') AS batch_no,
MAX(CONCAT(x.posting_date, ' ', x.posting_time, ' ', x.name)) AS maxkey
FROM `tabStock Ledger Entry` x
WHERE x.is_cancelled = 0
GROUP BY x.item_code, x.warehouse, IFNULL(x.batch_no, '')
) last ON last.warehouse = pw.wh
JOIN `tabStock Ledger Entry` sle
ON sle.item_code = last.item_code
AND sle.warehouse = last.warehouse
AND IFNULL(sle.batch_no, '') = last.batch_no
AND CONCAT(sle.posting_date, ' ', sle.posting_time, ' ', sle.name) = last.maxkey
GROUP BY pw.project
) av ON av.project = p.name
LEFT JOIN (
SELECT
COALESCE(NULLIF(sii.project, ''), '<<NO PROJECT>>') AS project,
SUM(sii.base_net_amount) AS si_items_total_base,
SUM(sii.net_amount) AS si_items_total_txn
FROM `tabSales Invoice` si
JOIN `tabSales Invoice Item` sii ON sii.parent = si.name
WHERE si.docstatus = 1
AND si.company = %(company)s
AND ( %(project_like)s = '' OR sii.project LIKE %(project_like)s )
GROUP BY COALESCE(NULLIF(sii.project, ''), '<<NO PROJECT>>')
) si ON si.project = p.name
WHERE p.company = %(company)s
AND ( %(project_like)s = '' OR p.name LIKE %(project_like)s )
ORDER BY p.name
250828 works already
but the fitler doesn't work. I need to redo all the Sales Invoices that have 1900 dates.
NO SO projects -
SELECT
p.name AS "PROJECT ID:Link/Project:140",
p.department AS "Department:Link/Department:150",
p.customer AS "Customer:Link/Customer:200",
e.so_name AS "Earliest SO:Link/Sales Order:160",
e.so_date AS "Earliest SO Date:Date:110",
COALESCE(si.si_items_total_base, 0) AS "Total SI Items (Base, by Project):Currency:180",
COALESCE(pt.pi_total, 0) AS "Total Purchase Invoices (Grand Total):Currency:170",
COALESCE(av.val_asof, 0) AS "Project Stock Value (As of To Date):Currency:180",
(
SELECT IFNULL(GROUP_CONCAT(IFNULL(soi.item_name, soi.item_code) ORDER BY soi.idx SEPARATOR ', '), '')
FROM `tabSales Order Item` soi
WHERE soi.parent = e.so_name
) AS "Sales Order Items (List):Data:300"
FROM `tabProject` p
LEFT JOIN (
SELECT
p2.name AS project,
(
SELECT so2.name
FROM `tabSales Order` so2
WHERE so2.company = p2.company
AND (
so2.project = p2.name
OR EXISTS (
SELECT 1 FROM `tabSales Order Item` soi2
WHERE soi2.parent = so2.name AND soi2.project = p2.name
)
)
ORDER BY so2.transaction_date ASC, so2.name ASC
LIMIT 1
) AS so_name,
(
SELECT MIN(so3.transaction_date)
FROM `tabSales Order` so3
WHERE so3.company = p2.company
AND (
so3.project = p2.name
OR EXISTS (
SELECT 1 FROM `tabSales Order Item` soi3
WHERE soi3.parent = so3.name AND soi3.project = p2.name
)
)
) AS so_date
FROM `tabProject` p2
WHERE p2.company = %(company)s
) e ON e.project = p.name
LEFT JOIN (
SELECT t.project, SUM(t.grand_total) AS pi_total
FROM (
SELECT DISTINCT pi.name, pii.project, pi.grand_total
FROM `tabPurchase Invoice` pi
JOIN `tabPurchase Invoice Item` pii ON pi.name = pii.parent
WHERE pi.docstatus = 1
AND pi.company = %(company)s
AND pi.posting_date BETWEEN %(from_date)s AND %(to_date)s
AND pii.project IS NOT NULL AND pii.project != ''
) t
GROUP BY t.project
) pt ON pt.project = p.name
LEFT JOIN (
SELECT pw.project, SUM(sle.stock_value) AS val_asof
FROM (
SELECT p3.name AS project, w.name AS wh
FROM `tabProject` p3
JOIN `tabWarehouse` w
ON w.company = p3.company
AND w.`custom_project` = p3.name
WHERE p3.company = %(company)s
) pw
JOIN (
SELECT x.item_code, x.warehouse, IFNULL(x.batch_no, '') AS batch_no,
MAX(CONCAT(x.posting_date, ' ', x.posting_time, ' ', x.name)) AS maxkey
FROM `tabStock Ledger Entry` x
WHERE x.is_cancelled = 0 AND x.posting_date <= %(to_date)s
GROUP BY x.item_code, x.warehouse, IFNULL(x.batch_no, '')
) last ON last.warehouse = pw.wh
JOIN `tabStock Ledger Entry` sle
ON sle.item_code = last.item_code
AND sle.warehouse = last.warehouse
AND IFNULL(sle.batch_no, '') = last.batch_no
AND CONCAT(sle.posting_date, ' ', sle.posting_time, ' ', sle.name) = last.maxkey
GROUP BY pw.project
) av ON av.project = p.name
LEFT JOIN (
SELECT
COALESCE(NULLIF(sii.project, ''), NULLIF(si.project, ''), '<<NO PROJECT>>') AS project,
SUM(sii.base_net_amount) AS si_items_total_base,
SUM(sii.net_amount) AS si_items_total_txn
FROM `tabSales Invoice` si
JOIN `tabSales Invoice Item` sii ON sii.parent = si.name
WHERE si.docstatus = 1
AND si.company = %(company)s
AND si.posting_date BETWEEN %(from_date)s AND %(to_date)s
GROUP BY COALESCE(NULLIF(sii.project, ''), NULLIF(si.project, ''), '<<NO PROJECT>>')
) si ON si.project = p.name
WHERE p.company = %(company)s
ORDER BY p.name
250828
WITH
-- Earliest SO per project (header or item-linked)
earliest_so AS (
SELECT
p.name AS project,
(
SELECT so2.name
FROM `tabSales Order` so2
WHERE so2.company = p.company
AND (
so2.project = p.name
OR EXISTS (
SELECT 1 FROM `tabSales Order Item` soi2
WHERE soi2.parent = so2.name AND soi2.project = p.name
)
)
ORDER BY so2.transaction_date ASC, so2.name ASC
LIMIT 1
) AS so_name,
(
SELECT MIN(so3.transaction_date)
FROM `tabSales Order` so3
WHERE so3.company = p.company
AND (
so3.project = p.name
OR EXISTS (
SELECT 1 FROM `tabSales Order Item` soi3
WHERE soi3.parent = so3.name AND soi3.project = p.name
)
)
) AS so_date
FROM `tabProject` p
WHERE p.company = %(company)s
),
-- PI totals per project (submitted only)
pi_totals AS (
SELECT pii.project AS project, SUM(DISTINCT pi.grand_total) AS pi_total
FROM `tabPurchase Invoice` pi
JOIN `tabPurchase Invoice Item` pii ON pi.name = pii.parent
WHERE pi.docstatus = 1
GROUP BY pii.project
),
-- Warehouses tied to each project via Warehouse.custom_project (custom Link field)
proj_wh AS (
SELECT p.name AS project, p.company, w.name AS wh
FROM `tabProject` p
JOIN `tabWarehouse` w
ON w.company = p.company
AND w.`custom_project` = p.name
WHERE p.company = %(company)s
),
-- As-of value using last SLE per (item, warehouse, batch) on or before to_date
asof_val AS (
SELECT pw.project, SUM(sle.stock_value) AS val_asof
FROM proj_wh pw
JOIN (
SELECT x.item_code, x.warehouse, IFNULL(x.batch_no, '') AS batch_no,
MAX(CONCAT(x.posting_date, ' ', x.posting_time, ' ', x.name)) AS maxkey
FROM `tabStock Ledger Entry` x
WHERE x.is_cancelled = 0 AND x.posting_date <= %(to_date)s
GROUP BY x.item_code, x.warehouse, IFNULL(x.batch_no, '')
) last ON last.warehouse = pw.wh
JOIN `tabStock Ledger Entry` sle
ON sle.item_code = last.item_code
AND sle.warehouse = last.warehouse
AND IFNULL(sle.batch_no, '') = last.batch_no
AND CONCAT(sle.posting_date, ' ', sle.posting_time, ' ', sle.name) = last.maxkey
GROUP BY pw.project
),
-- Sum of Sales Invoices per Sales Order (submitted only), avoiding double-count across items
si_total_by_so AS (
SELECT x.sales_order AS so_name, SUM(x.grand_total) AS si_total
FROM (
SELECT DISTINCT sii.sales_order, si.name, si.grand_total
FROM `tabSales Invoice` si
JOIN `tabSales Invoice Item` sii ON sii.parent = si.name
WHERE si.docstatus = 1
AND sii.sales_order IS NOT NULL
) x
GROUP BY x.sales_order
)
SELECT
p.name AS "PROJECT ID:Link/Project:120",
p.department AS "Department:Link/Department:150",
p.customer AS "Customer:Link/Customer:200",
e.so_name AS "Sales Order:Link/Sales Order:160",
e.so_date AS "Sales Order Date:Date:110",
COALESCE(st.so_name, e.so_name) AS "SO (for SI Sum):Link/Sales Order:160",
COALESCE(st.si_total, 0) AS "Total Sales Invoices for SO (Grand Total):Currency:170",
(
SELECT IFNULL(GROUP_CONCAT(IFNULL(soi.item_name, soi.item_code) ORDER BY soi.idx SEPARATOR ', '), '')
FROM `tabSales Order Item` soi
WHERE soi.parent = e.so_name
) AS "Sales Order Items (List):Data:300",
COALESCE(pt.pi_total, 0) AS "Total Purchase Invoices (Grand Total):Currency:160",
COALESCE(av.val_asof, 0) AS "Project Stock Value (As of To Date):Currency:170"
FROM `tabProject` p
LEFT JOIN earliest_so e ON e.project = p.name
LEFT JOIN pi_totals pt ON pt.project = p.name
LEFT JOIN asof_val av ON av.project = p.name
LEFT JOIN si_total_by_so st ON st.so_name = e.so_name
WHERE p.company = %(company)s
ORDER BY p.name
Proj ID, Dept, Customer, Sales order, Sales Order Date, Sales order items (arrayed; ITEM NAMES) - no brackets and qutations marks. + TOtal Purchase Invoices - but has two Warehouse Balance as of Date and
WITH
-- Earliest SO per project (header or item-linked)
earliest_so AS (
SELECT
p.name AS project,
(
SELECT so2.name
FROM `tabSales Order` so2
WHERE so2.company = p.company
AND (
so2.project = p.name
OR EXISTS (
SELECT 1 FROM `tabSales Order Item` soi2
WHERE soi2.parent = so2.name AND soi2.project = p.name
)
)
ORDER BY so2.transaction_date ASC, so2.name ASC
LIMIT 1
) AS so_name,
(
SELECT MIN(so3.transaction_date)
FROM `tabSales Order` so3
WHERE so3.company = p.company
AND (
so3.project = p.name
OR EXISTS (
SELECT 1 FROM `tabSales Order Item` soi3
WHERE soi3.parent = so3.name AND soi3.project = p.name
)
)
) AS so_date
FROM `tabProject` p
WHERE p.company = %(company)s
),
-- PI totals per project (submitted only)
pi_totals AS (
SELECT pii.project AS project, SUM(DISTINCT pi.grand_total) AS pi_total
FROM `tabPurchase Invoice` pi
JOIN `tabPurchase Invoice Item` pii ON pi.name = pii.parent
WHERE pi.docstatus = 1
GROUP BY pii.project
),
-- Warehouses tied to each project via Warehouse.custom_project (custom Link field)
proj_wh AS (
SELECT p.name AS project, p.company, w.name AS wh
FROM `tabProject` p
JOIN `tabWarehouse` w
ON w.company = p.company
AND w.`custom_project` = p.name
WHERE p.company = %(company)s
),
-- Current value from Bin (now)
bin_val AS (
SELECT pw.project, SUM(b.actual_qty * IFNULL(b.valuation_rate, 0)) AS val_now
FROM proj_wh pw
JOIN `tabBin` b ON b.warehouse = pw.wh
GROUP BY pw.project
),
-- As-of value using last SLE per (item, warehouse, batch) on or before to_date
asof_val AS (
SELECT pw.project, SUM(sle.stock_value) AS val_asof
FROM proj_wh pw
JOIN (
SELECT x.item_code, x.warehouse, IFNULL(x.batch_no, '') AS batch_no,
MAX(CONCAT(x.posting_date, ' ', x.posting_time, ' ', x.name)) AS maxkey
FROM `tabStock Ledger Entry` x
WHERE x.is_cancelled = 0 AND x.posting_date <= %(to_date)s
GROUP BY x.item_code, x.warehouse, IFNULL(x.batch_no, '')
) last ON last.warehouse = pw.wh
JOIN `tabStock Ledger Entry` sle
ON sle.item_code = last.item_code
AND sle.warehouse = last.warehouse
AND IFNULL(sle.batch_no, '') = last.batch_no
AND CONCAT(sle.posting_date, ' ', sle.posting_time, ' ', sle.name) = last.maxkey
GROUP BY pw.project
)
SELECT
p.name AS "PROJECT ID:Link/Project:120",
p.department AS "Department:Link/Department:150",
p.customer AS "Customer:Link/Customer:200",
e.so_name AS "Sales Order:Link/Sales Order:160",
e.so_date AS "Sales Order Date:Date:110",
(
SELECT IFNULL(GROUP_CONCAT(IFNULL(soi.item_name, soi.item_code) ORDER BY soi.idx SEPARATOR ', '), '')
FROM `tabSales Order Item` soi
WHERE soi.parent = e.so_name
) AS "Sales Order Items (List):Data:300",
COALESCE(pt.pi_total, 0) AS "Total Purchase Invoices (Grand Total):Currency:160",
COALESCE(bv.val_now, 0) AS "Project Stock Value (Current):Currency:160",
COALESCE(av.val_asof, 0) AS "Project Stock Value as of To Date:Currency:170"
FROM `tabProject` p
LEFT JOIN earliest_so e ON e.project = p.name
LEFT JOIN pi_totals pt ON pt.project = p.name
LEFT JOIN bin_val bv ON bv.project = p.name
LEFT JOIN asof_val av ON av.project = p.name
WHERE p.company = %(company)s
ORDER BY p.name
Proj ID, Dept, Customer, Sales order, Sales Order Date, Sales order items (arrayed; ITEM NAMES) - no brackets and qutations marks. + TOtal Purchase Invoices
SELECT
proj.name AS "PROJECT ID:Link/Project:120", /* OK to comment here: report column header */
/* GUESS: earliest Sales Order date for this project — works if Project is on SO header OR items */
(
SELECT MIN(so.transaction_date) /* CONFIRMED field on `tabSales Order` */
FROM `tabSales Order` so
WHERE so.company = proj.company
AND (
so.project = proj.name /* GUESS: header link; remove if you never use it */
OR EXISTS (
SELECT 1 FROM `tabSales Order Item` soi
WHERE soi.parent = so.name AND soi.project = proj.name /* CONFIRMED alt link on items */
)
)
) AS "Sales Order Date:Date:120",
/* GUESS: Project Department lives on `tabProject`. If you use a custom field, swap `department` for your fieldname. */
proj.department AS "Department:Link/Department:150",
/* GUESS: Customer Link on Project. If custom, change to your fieldname. */
proj.customer AS "Customer:Link/Customer:200"
FROM `tabProject` proj
WHERE proj.company = %(company)s /* REQUIRED report filter named `company` */
ORDER BY proj.name /* Safe to comment anywhere after the initial SELECT */
Proj ID, Dept, Customer, Sales order, Sales Order Date, Sales order items (arrayed; ITEM NAMES) - no brackets and qutations marks.
SELECT
proj.name AS "PROJECT ID:Link/Project:120",
proj.department AS "Department:Link/Department:150",
proj.customer AS "Customer:Link/Customer:200",
-- Earliest Sales Order (by date) linked to this Project (header OR items)
(
SELECT so.name
FROM `tabSales Order` so
WHERE so.company = proj.company
AND (
so.project = proj.name
OR EXISTS (
SELECT 1 FROM `tabSales Order Item` soi
WHERE soi.parent = so.name AND soi.project = proj.name
)
)
ORDER BY so.transaction_date ASC, so.name ASC
LIMIT 1
) AS "Sales Order:Link/Sales Order:180",
-- Corresponding Sales Order Date (same match rule)
(
SELECT MIN(so.transaction_date)
FROM `tabSales Order` so
WHERE so.company = proj.company
AND (
so.project = proj.name
OR EXISTS (
SELECT 1 FROM `tabSales Order Item` soi
WHERE soi.parent = so.name AND soi.project = proj.name
)
)
) AS "Sales Order Date:Date:120",
-- Plain list (no brackets/quotes): ALL item names on that earliest SO, duplicates preserved, original row order
(
SELECT IFNULL(
GROUP_CONCAT(IFNULL(soi.item_name, soi.item_code) ORDER BY soi.idx SEPARATOR ', '),
''
)
FROM `tabSales Order Item` soi
WHERE soi.parent = (
SELECT so.name
FROM `tabSales Order` so
WHERE so.company = proj.company
AND (
so.project = proj.name
OR EXISTS (
SELECT 1 FROM `tabSales Order Item` soi2
WHERE soi2.parent = so.name AND soi2.project = proj.name
)
)
ORDER BY so.transaction_date ASC, so.name ASC
LIMIT 1
)
) AS "Sales Order Items (List):Data:360"
FROM `tabProject` proj
WHERE proj.company = %(company)s
ORDER BY proj.name
Proj ID, Dept, Customer, Sales order, Sales Order Date, Sales order items (arrayed; ITEM NAMES)
SELECT
proj.name AS "PROJECT ID:Link/Project:120",
proj.department AS "Department:Link/Department:150",
proj.customer AS "Customer:Link/Customer:200",
-- Earliest Sales Order (by date) linked to this Project (header OR items)
(
SELECT so.name
FROM `tabSales Order` so
WHERE so.company = proj.company
AND (
so.project = proj.name
OR EXISTS (
SELECT 1 FROM `tabSales Order Item` soi
WHERE soi.parent = so.name AND soi.project = proj.name
)
)
ORDER BY so.transaction_date ASC, so.name ASC
LIMIT 1
) AS "Sales Order:Link/Sales Order:180",
-- Corresponding Sales Order Date (same match rule)
(
SELECT MIN(so.transaction_date)
FROM `tabSales Order` so
WHERE so.company = proj.company
AND (
so.project = proj.name
OR EXISTS (
SELECT 1 FROM `tabSales Order Item` soi
WHERE soi.parent = so.name AND soi.project = proj.name
)
)
) AS "Sales Order Date:Date:120",
-- Array of ALL item names on that earliest Sales Order (duplicates preserved, row order)
(
SELECT IFNULL(
CONCAT('[',
GROUP_CONCAT(
JSON_QUOTE(IFNULL(soi.item_name, soi.item_code))
ORDER BY soi.idx SEPARATOR ', '
),
']'
),
'[]'
)
FROM `tabSales Order Item` soi
WHERE soi.parent = (
SELECT so.name
FROM `tabSales Order` so
WHERE so.company = proj.company
AND (
so.project = proj.name
OR EXISTS (
SELECT 1 FROM `tabSales Order Item` soi2
WHERE soi2.parent = so.name AND soi2.project = proj.name
)
)
ORDER BY so.transaction_date ASC, so.name ASC
LIMIT 1
)
) AS "Sales Order Items (Array):Data:360"
FROM `tabProject` proj
WHERE proj.company = %(company)s
ORDER BY proj.name
Proj ID, Dept, Customer, Sales order, Sales Order Date, Sales order items (arrayed)
SELECT
proj.name AS "PROJECT ID:Link/Project:120",
proj.department AS "Department:Link/Department:150",
proj.customer AS "Customer:Link/Customer:200",
-- Earliest Sales Order (by date) linked to this Project (either on SO header or on SO Items)
(
SELECT so.name
FROM `tabSales Order` so
WHERE so.company = proj.company
AND (
so.project = proj.name
OR EXISTS (
SELECT 1 FROM `tabSales Order Item` soi
WHERE soi.parent = so.name AND soi.project = proj.name
)
)
ORDER BY so.transaction_date ASC, so.name ASC
LIMIT 1
) AS "Sales Order:Link/Sales Order:180",
-- Corresponding Sales Order Date (same matching rule)
(
SELECT MIN(so.transaction_date)
FROM `tabSales Order` so
WHERE so.company = proj.company
AND (
so.project = proj.name
OR EXISTS (
SELECT 1 FROM `tabSales Order Item` soi
WHERE soi.parent = so.name AND soi.project = proj.name
)
)
) AS "Sales Order Date:Date:120",
-- Array of ALL item codes on that earliest Sales Order (duplicates preserved, ordered by row index)
(
SELECT IFNULL(
CONCAT('[', GROUP_CONCAT(CONCAT('"', soi.item_code, '"') ORDER BY soi.idx SEPARATOR ', '), ']'),
'[]'
)
FROM `tabSales Order Item` soi
WHERE soi.parent = (
SELECT so.name
FROM `tabSales Order` so
WHERE so.company = proj.company
AND (
so.project = proj.name
OR EXISTS (
SELECT 1 FROM `tabSales Order Item` soi2
WHERE soi2.parent = so.name AND soi2.project = proj.name
)
)
ORDER BY so.transaction_date ASC, so.name ASC
LIMIT 1
)
) AS "Sales Order Items (Array):Data:320"
FROM `tabProject` proj
WHERE proj.company = %(company)s
ORDER BY proj.name
Project ID, Dept., Customer, Sales order Date
SELECT
proj.name AS "PROJECT ID:Link/Project:120",
proj.department AS "Department:Link/Department:150",
proj.customer AS "Customer:Link/Customer:200",
(
SELECT MIN(so.transaction_date)
FROM `tabSales Order` so
WHERE so.company = proj.company
AND (
so.project = proj.name
OR EXISTS (
SELECT 1 FROM `tabSales Order Item` soi
WHERE soi.parent = so.name AND soi.project = proj.name
)
)
) AS "Sales Order Date:Date:120"
FROM `tabProject` proj
WHERE proj.company = %(company)s
ORDER BY proj.name
Only SO Date and Project ID 10:28pm
SELECT
proj.name AS "PROJECT ID:Link/Project:120",
(
SELECT MIN(so.transaction_date)
FROM `tabSales Order` so
WHERE so.company = proj.company
AND (
so.project = proj.name
OR EXISTS (
SELECT 1 FROM `tabSales Order Item` soi
WHERE soi.parent = so.name AND soi.project = proj.name
)
)
) AS "Sales Order Date:Date:120"
FROM `tabProject` proj
WHERE proj.company = %(company)s
ORDER BY proj.name
Works 10:08pm
SELECT
proj.name AS "PROJECT ID:Link/Project:120",
proj.department AS "Department:Link/Department:150",
proj.customer AS "Customer:Link/Customer:200",
(
SELECT MIN(so.transaction_date)
FROM `tabSales Order` so
JOIN `tabSales Order Item` soi ON so.name = soi.parent
WHERE soi.project = proj.name
) AS "Sales Order Posting Date:Date:100",
(
SELECT GROUP_CONCAT(DISTINCT soi.item_code SEPARATOR ', ')
FROM `tabSales Order Item` soi
WHERE soi.project = proj.name
) AS "Sales Order Items Aggregated:Data:250",
(
SELECT GROUP_CONCAT(DISTINCT soi.description SEPARATOR '\n')
FROM `tabSales Order Item` soi
WHERE soi.project = proj.name AND IFNULL(soi.description, '') <> ''
) AS "Notes:Small Text:250",
(
SELECT SUM(pi.grand_total)
FROM `tabPurchase Invoice` pi
JOIN `tabPurchase Invoice Item` pii ON pi.name = pii.parent
WHERE pii.project = proj.name
AND pi.docstatus = 1
AND pi.posting_date BETWEEN %(from_date)s AND %(to_date)s
) AS "Total Purchase Cost (via Purchase Invoice):Currency:150",
(
SELECT COUNT(DISTINCT pi.name)
FROM `tabPurchase Invoice` pi
JOIN `tabPurchase Invoice Item` pii ON pi.name = pii.parent
WHERE pii.project = proj.name
AND pi.docstatus = 1
AND pi.posting_date BETWEEN %(from_date)s AND %(to_date)s
) AS "Purchase Invoice Count:Int:100",
(
SELECT SUM(tsd.costing_amount)
FROM `tabTimesheet Detail` tsd
JOIN `tabTimesheet` ts ON ts.name = tsd.parent
WHERE tsd.project = proj.name
AND ts.docstatus = 1
AND ts.start_date BETWEEN %(from_date)s AND %(to_date)s
) AS "Total Costing Amount (via Timesheet):Currency:150",
(
SELECT SUM(si.grand_total)
FROM `tabSales Invoice` si
WHERE si.docstatus = 1
AND si.posting_date BETWEEN %(from_date)s AND %(to_date)s
AND EXISTS (
SELECT 1 FROM `tabSales Invoice Item` sii
WHERE sii.parent = si.name AND sii.project = proj.name
)
) AS "Total Billed Amount (via Sales Invoice):Currency:150",
(
IFNULL((
SELECT SUM(si.grand_total)
FROM `tabSales Invoice` si
WHERE si.docstatus = 1
AND si.posting_date BETWEEN %(from_date)s AND %(to_date)s
AND EXISTS (
SELECT 1 FROM `tabSales Invoice Item` sii
WHERE sii.parent = si.name AND sii.project = proj.name
)
), 0)
- IFNULL((
SELECT SUM(pi.grand_total)
FROM `tabPurchase Invoice` pi
JOIN `tabPurchase Invoice Item` pii ON pi.name = pii.parent
WHERE pii.project = proj.name
AND pi.docstatus = 1
AND pi.posting_date BETWEEN %(from_date)s AND %(to_date)s
), 0)
- IFNULL((
SELECT SUM(tsd.costing_amount)
FROM `tabTimesheet Detail` tsd
JOIN `tabTimesheet` ts ON ts.name = tsd.parent
WHERE tsd.project = proj.name
AND ts.docstatus = 1
AND ts.start_date BETWEEN %(from_date)s AND %(to_date)s
), 0)
) AS "Gross Margin:Currency:150",
IFNULL((
SELECT SUM(b.actual_qty * IFNULL(b.valuation_rate, 0))
FROM `tabBin` b
WHERE b.warehouse IN (
SELECT w.name FROM `tabWarehouse` w
WHERE w.warehouse_name LIKE CONCAT(proj.`project_name`, CHAR(37))
AND w.company = proj.company
)
), 0) AS "Project Warehouse Stock Value:Currency:150"
FROM `tabProject` proj
WHERE proj.company = %(company)s
ORDER BY proj.name
problems -
it should be Sales Order Date not Posting date - it pulls the date of the Sales Order linked to this Project.
Sales Order Items Aggregted - make an array out of the list of the all the Sales Order Items