Merge pull request #33736 from rtdany10/gp-report-fix
fix(gp): fetch buying amount from dn related to so
diff --git a/erpnext/accounts/report/gross_profit/gross_profit.py b/erpnext/accounts/report/gross_profit/gross_profit.py
index 130b715..25e7891 100644
--- a/erpnext/accounts/report/gross_profit/gross_profit.py
+++ b/erpnext/accounts/report/gross_profit/gross_profit.py
@@ -655,10 +655,35 @@
return self.calculate_buying_amount_from_sle(
row, my_sle, parenttype, parent, item_row, item_code
)
+ elif row.sales_order and row.so_detail:
+ incoming_amount = self.get_buying_amount_from_so_dn(row.sales_order, row.so_detail, item_code)
+ if incoming_amount:
+ return incoming_amount
else:
return flt(row.qty) * self.get_average_buying_rate(row, item_code)
- return 0.0
+ return flt(row.qty) * self.get_average_buying_rate(row, item_code)
+
+ def get_buying_amount_from_so_dn(self, sales_order, so_detail, item_code):
+ from frappe.query_builder.functions import Sum
+
+ delivery_note = frappe.qb.DocType("Delivery Note")
+ delivery_note_item = frappe.qb.DocType("Delivery Note Item")
+
+ query = (
+ frappe.qb.from_(delivery_note)
+ .inner_join(delivery_note_item)
+ .on(delivery_note.name == delivery_note_item.parent)
+ .select(Sum(delivery_note_item.incoming_rate * delivery_note_item.stock_qty))
+ .where(delivery_note.docstatus == 1)
+ .where(delivery_note_item.item_code == item_code)
+ .where(delivery_note_item.against_sales_order == sales_order)
+ .where(delivery_note_item.so_detail == so_detail)
+ .groupby(delivery_note_item.item_code)
+ )
+
+ incoming_amount = query.run()
+ return flt(incoming_amount[0][0]) if incoming_amount else 0
def get_average_buying_rate(self, row, item_code):
args = row
@@ -760,7 +785,8 @@
`tabSales Invoice`.territory, `tabSales Invoice Item`.item_code,
`tabSales Invoice Item`.item_name, `tabSales Invoice Item`.description,
`tabSales Invoice Item`.warehouse, `tabSales Invoice Item`.item_group,
- `tabSales Invoice Item`.brand, `tabSales Invoice Item`.dn_detail,
+ `tabSales Invoice Item`.brand, `tabSales Invoice Item`.so_detail,
+ `tabSales Invoice Item`.sales_order, `tabSales Invoice Item`.dn_detail,
`tabSales Invoice Item`.delivery_note, `tabSales Invoice Item`.stock_qty as qty,
`tabSales Invoice Item`.base_net_rate, `tabSales Invoice Item`.base_net_amount,
`tabSales Invoice Item`.name as "item_row", `tabSales Invoice`.is_return,
diff --git a/erpnext/accounts/report/gross_profit/test_gross_profit.py b/erpnext/accounts/report/gross_profit/test_gross_profit.py
index fa11a41..21681be 100644
--- a/erpnext/accounts/report/gross_profit/test_gross_profit.py
+++ b/erpnext/accounts/report/gross_profit/test_gross_profit.py
@@ -302,3 +302,82 @@
columns, data = execute(filters=filters)
self.assertGreater(len(data), 0)
+
+ def test_order_connected_dn_and_inv(self):
+ from erpnext.selling.doctype.sales_order.test_sales_order import make_sales_order
+
+ """
+ Test gp calculation when invoice and delivery note aren't directly connected.
+ SO -- INV
+ |
+ DN
+ """
+ se = make_stock_entry(
+ company=self.company,
+ item_code=self.item,
+ target=self.warehouse,
+ qty=3,
+ basic_rate=100,
+ do_not_submit=True,
+ )
+ item = se.items[0]
+ se.append(
+ "items",
+ {
+ "item_code": item.item_code,
+ "s_warehouse": item.s_warehouse,
+ "t_warehouse": item.t_warehouse,
+ "qty": 10,
+ "basic_rate": 200,
+ "conversion_factor": item.conversion_factor or 1.0,
+ "transfer_qty": flt(item.qty) * (flt(item.conversion_factor) or 1.0),
+ "serial_no": item.serial_no,
+ "batch_no": item.batch_no,
+ "cost_center": item.cost_center,
+ "expense_account": item.expense_account,
+ },
+ )
+ se = se.save().submit()
+
+ so = make_sales_order(
+ customer=self.customer,
+ company=self.company,
+ warehouse=self.warehouse,
+ item=self.item,
+ qty=4,
+ do_not_save=False,
+ do_not_submit=False,
+ )
+
+ from erpnext.selling.doctype.sales_order.sales_order import (
+ make_delivery_note,
+ make_sales_invoice,
+ )
+
+ make_delivery_note(so.name).submit()
+ sinv = make_sales_invoice(so.name).submit()
+
+ filters = frappe._dict(
+ company=self.company, from_date=nowdate(), to_date=nowdate(), group_by="Invoice"
+ )
+
+ columns, data = execute(filters=filters)
+ expected_entry = {
+ "parent_invoice": sinv.name,
+ "currency": "INR",
+ "sales_invoice": self.item,
+ "customer": self.customer,
+ "posting_date": frappe.utils.datetime.date.fromisoformat(nowdate()),
+ "item_code": self.item,
+ "item_name": self.item,
+ "warehouse": "Stores - _GP",
+ "qty": 4.0,
+ "avg._selling_rate": 100.0,
+ "valuation_rate": 125.0,
+ "selling_amount": 400.0,
+ "buying_amount": 500.0,
+ "gross_profit": -100.0,
+ "gross_profit_%": -25.0,
+ }
+ gp_entry = [x for x in data if x.parent_invoice == sinv.name]
+ self.assertDictContainsSubset(expected_entry, gp_entry[0])