Hello,
I have a doctype called Lot. In every purchase order I created a lot with that purchase. Then I sell item through that lot. Now I want to create a lot based profit-loss report. I already create a sql query for that reports. I created two reports. Lot wise profit loss report summery and Lot wise profit loss report details. Here are my sql queries.
Lot wise profit loss report
SELECT t1.lot_name as 'Lot No.' , t2.item_code as Item,
ROUND(t2.amount, 2) as 'Purchase Amount',
ROUND(sum(t3.amount),2) as 'Sales Amount',
CASE WHEN (sum(t3.amount) - t2.amount) <= 0
THEN '' ELSE
ROUND((sum(t3.amount) - t2.amount),2) END as 'Profit',
CASE WHEN (t2.amount - sum(t3.amount)) <= 0
THEN '' ELSE
ROUND((t2.amount - sum(t3.amount)),2) END as 'Loss'
FROM `tabPurchase Order` as t1
JOIN`tabPurchase Order Item` as t2 ON t1.name = t2.parent
JOIN `tabSales Order Item` as t3 ON t1.lot_name = t3.lot_no
WHERE t3.docstatus = 1 & t1.docstatus = 1 GROUP BY t1.lot_name, t2.item_code
Lot wise profit loss report details
SELECT t1.transaction_date as 'Date', t1.lot_name as 'Lot No.',
t2.item_code, t2.qty as 'Pur_Qty',
t2.rate as 'Pur Rate',
ROUND(t2.amount, 2) as 'Purchase Amount',
t3.qty as 'Sales_Qty', t3.rate as 'Sales Rate',
ROUND(t3.amount, 2) as 'Sales Amount', `tabLot Item`.qty as 'Remaining Qty',
CASE WHEN (t3.qty < t2.qty)
THEN
ROUND((((t3.qty * t2.kg_per_drum)/t2.conversion)*
t2.rate + t2.utility),2) END as 'Pur amnt on sales qty',
CASE WHEN (t3.qty < t2.qty) AND (t3.amount - (((t3.qty *
t2.kg_per_drum)/t2.conversion)*
t2.rate + t2.utility)) <= 0 THEN '' ELSE ROUND((t3.amount -
(((t3.qty * t2.kg_per_drum)/t2.conversion)*
t2.rate + t2.utility)),2) END as 'Profit',
CASE WHEN ((((t3.qty * t2.kg_per_drum)/t2.conversion)*
t2.rate + t2.utility) - t3.amount) <= 0 THEN '' ELSE ROUND
(((((t3.qty * t2.kg_per_drum)/t2.conversion)*
t2.rate + t2.utility) - t3.amount), 2) END as 'Loss'
FROM `tabPurchase Order` as t1
JOIN `tabPurchase Order Item` as t2 ON t1.name = t2.parent
JOIN `tabSales Order Item` as t3 ON t1.lot_name = t3.lot_no
JOIN `tabLot Item` ON t1.lot_name = `tabLot Item`.parent
WHERE t3.docstatus = 1 & t1.docstatus = 1
Its quite large sql query. I used it as query report. But I need to ad filter to my Lot wise profit loss report. I need to add link to the lot names which is go to Lot wise profit loss report details on click and filter it as lot name.
Now, matter is I don’t know how to do it with script report. Is any one here understand the matter and help me? It’ll be very helpful.
Thanks