Merge pull request #31194 from ruthra-kumar/bug_sales_order_analysis_report
fix: incorrect billed_qty in sales order analysis report when multiple delivery notes for item
diff --git a/erpnext/selling/report/sales_order_analysis/sales_order_analysis.py b/erpnext/selling/report/sales_order_analysis/sales_order_analysis.py
index dcfb10a..cc61594 100644
--- a/erpnext/selling/report/sales_order_analysis/sales_order_analysis.py
+++ b/erpnext/selling/report/sales_order_analysis/sales_order_analysis.py
@@ -1,11 +1,13 @@
# Copyright (c) 2013, Frappe Technologies Pvt. Ltd. and contributors
# For license information, please see license.txt
-
import copy
+from collections import OrderedDict
import frappe
-from frappe import _
+from frappe import _, qb
+from frappe.query_builder import CustomFunction
+from frappe.query_builder.functions import Max
from frappe.utils import date_diff, flt, getdate
@@ -18,11 +20,12 @@
columns = get_columns(filters)
conditions = get_conditions(filters)
data = get_data(conditions, filters)
+ so_elapsed_time = get_so_elapsed_time(data)
if not data:
return [], [], None, []
- data, chart_data = prepare_data(data, filters)
+ data, chart_data = prepare_data(data, so_elapsed_time, filters)
return columns, data, None, chart_data
@@ -65,7 +68,6 @@
IF(so.status in ('Completed','To Bill'), 0, (SELECT delay_days)) as delay,
soi.qty, soi.delivered_qty,
(soi.qty - soi.delivered_qty) AS pending_qty,
- IF((SELECT pending_qty) = 0, (TO_SECONDS(Max(dn.posting_date))-TO_SECONDS(so.transaction_date)), 0) as time_taken_to_deliver,
IFNULL(SUM(sii.qty), 0) as billed_qty,
soi.base_amount as amount,
(soi.delivered_qty * soi.base_rate) as delivered_qty_amount,
@@ -76,13 +78,9 @@
soi.description as description
FROM
`tabSales Order` so,
- (`tabSales Order Item` soi
+ `tabSales Order Item` soi
LEFT JOIN `tabSales Invoice Item` sii
- ON sii.so_detail = soi.name and sii.docstatus = 1)
- LEFT JOIN `tabDelivery Note Item` dni
- on dni.so_detail = soi.name
- LEFT JOIN `tabDelivery Note` dn
- on dni.parent = dn.name and dn.docstatus = 1
+ ON sii.so_detail = soi.name and sii.docstatus = 1
WHERE
soi.parent = so.name
and so.status not in ('Stopped', 'Closed', 'On Hold')
@@ -100,7 +98,48 @@
return data
-def prepare_data(data, filters):
+def get_so_elapsed_time(data):
+ """
+ query SO's elapsed time till latest delivery note
+ """
+ so_elapsed_time = OrderedDict()
+ if data:
+ sales_orders = [x.sales_order for x in data]
+
+ so = qb.DocType("Sales Order")
+ soi = qb.DocType("Sales Order Item")
+ dn = qb.DocType("Delivery Note")
+ dni = qb.DocType("Delivery Note Item")
+
+ to_seconds = CustomFunction("TO_SECONDS", ["date"])
+
+ query = (
+ qb.from_(so)
+ .inner_join(soi)
+ .on(soi.parent == so.name)
+ .left_join(dni)
+ .on(dni.so_detail == soi.name)
+ .left_join(dn)
+ .on(dni.parent == dn.name)
+ .select(
+ so.name.as_("sales_order"),
+ soi.item_code.as_("so_item_code"),
+ (to_seconds(Max(dn.posting_date)) - to_seconds(so.transaction_date)).as_("elapsed_seconds"),
+ )
+ .where((so.name.isin(sales_orders)) & (dn.docstatus == 1))
+ .orderby(so.name, soi.name)
+ .groupby(soi.name)
+ )
+ dn_elapsed_time = query.run(as_dict=True)
+
+ for e in dn_elapsed_time:
+ key = (e.sales_order, e.so_item_code)
+ so_elapsed_time[key] = e.elapsed_seconds
+
+ return so_elapsed_time
+
+
+def prepare_data(data, so_elapsed_time, filters):
completed, pending = 0, 0
if filters.get("group_by_so"):
@@ -115,6 +154,13 @@
row["qty_to_bill"] = flt(row["qty"]) - flt(row["billed_qty"])
row["delay"] = 0 if row["delay"] and row["delay"] < 0 else row["delay"]
+
+ row["time_taken_to_deliver"] = (
+ so_elapsed_time.get((row.sales_order, row.item_code))
+ if row["status"] in ("To Bill", "Completed")
+ else 0
+ )
+
if filters.get("group_by_so"):
so_name = row["sales_order"]
diff --git a/erpnext/selling/report/sales_order_analysis/test_sales_order_analysis.py b/erpnext/selling/report/sales_order_analysis/test_sales_order_analysis.py
index 25cbb73..241f435 100644
--- a/erpnext/selling/report/sales_order_analysis/test_sales_order_analysis.py
+++ b/erpnext/selling/report/sales_order_analysis/test_sales_order_analysis.py
@@ -11,7 +11,7 @@
class TestSalesOrderAnalysis(FrappeTestCase):
- def create_sales_order(self, transaction_date):
+ def create_sales_order(self, transaction_date, do_not_save=False, do_not_submit=False):
item = create_item(item_code="_Test Excavator", is_stock_item=0)
so = make_sales_order(
transaction_date=transaction_date,
@@ -24,25 +24,31 @@
so.taxes_and_charges = ""
so.taxes = ""
so.items[0].delivery_date = add_days(transaction_date, 15)
- so.save()
- so.submit()
+ if not do_not_save:
+ so.save()
+ if not do_not_submit:
+ so.submit()
return item, so
- def create_sales_invoice(self, so):
+ def create_sales_invoice(self, so, do_not_save=False, do_not_submit=False):
sinv = make_sales_invoice(so.name)
sinv.posting_date = so.transaction_date
sinv.taxes_and_charges = ""
sinv.taxes = ""
- sinv.insert()
- sinv.submit()
+ if not do_not_save:
+ sinv.save()
+ if not do_not_submit:
+ sinv.submit()
return sinv
- def create_delivery_note(self, so):
+ def create_delivery_note(self, so, do_not_save=False, do_not_submit=False):
dn = make_delivery_note(so.name)
dn.set_posting_time = True
dn.posting_date = add_days(so.transaction_date, 1)
- dn.save()
- dn.submit()
+ if not do_not_save:
+ dn.save()
+ if not do_not_submit:
+ dn.submit()
return dn
def test_01_so_to_deliver_and_bill(self):
@@ -164,3 +170,85 @@
)
# SO's from first 4 test cases should be in output
self.assertEqual(len(data), 4)
+
+ def test_06_so_pending_delivery_with_multiple_delivery_notes(self):
+ transaction_date = "2021-06-01"
+ item, so = self.create_sales_order(transaction_date)
+
+ # bill 2 items
+ sinv1 = self.create_sales_invoice(so, do_not_save=True)
+ sinv1.items[0].qty = 2
+ sinv1 = sinv1.save().submit()
+ # deliver 2 items
+ dn1 = self.create_delivery_note(so, do_not_save=True)
+ dn1.items[0].qty = 2
+ dn1 = dn1.save().submit()
+
+ # bill 2 items
+ sinv2 = self.create_sales_invoice(so, do_not_save=True)
+ sinv2.items[0].qty = 2
+ sinv2 = sinv2.save().submit()
+ # deliver 1 item
+ dn2 = self.create_delivery_note(so, do_not_save=True)
+ dn2.items[0].qty = 1
+ dn2 = dn2.save().submit()
+
+ columns, data, message, chart = execute(
+ {
+ "company": "_Test Company",
+ "from_date": "2021-06-01",
+ "to_date": "2021-06-30",
+ "sales_order": [so.name],
+ }
+ )
+ expected_value = {
+ "status": "To Deliver and Bill",
+ "sales_order": so.name,
+ "delay_days": frappe.utils.date_diff(frappe.utils.datetime.date.today(), so.delivery_date),
+ "qty": 10,
+ "delivered_qty": 3,
+ "pending_qty": 7,
+ "qty_to_bill": 6,
+ "billed_qty": 4,
+ "time_taken_to_deliver": 0,
+ }
+ self.assertEqual(len(data), 1)
+ for key, val in expected_value.items():
+ with self.subTest(key=key, val=val):
+ self.assertEqual(data[0][key], val)
+
+ def test_07_so_delivered_with_multiple_delivery_notes(self):
+ transaction_date = "2021-06-01"
+ item, so = self.create_sales_order(transaction_date)
+
+ dn1 = self.create_delivery_note(so, do_not_save=True)
+ dn1.items[0].qty = 5
+ dn1 = dn1.save().submit()
+
+ dn2 = self.create_delivery_note(so, do_not_save=True)
+ dn2.items[0].qty = 5
+ dn2 = dn2.save().submit()
+
+ columns, data, message, chart = execute(
+ {
+ "company": "_Test Company",
+ "from_date": "2021-06-01",
+ "to_date": "2021-06-30",
+ "sales_order": [so.name],
+ }
+ )
+ expected_value = {
+ "status": "To Bill",
+ "sales_order": so.name,
+ "delay_days": frappe.utils.date_diff(frappe.utils.datetime.date.today(), so.delivery_date),
+ "qty": 10,
+ "delivered_qty": 10,
+ "pending_qty": 0,
+ "qty_to_bill": 10,
+ "billed_qty": 0,
+ "time_taken_to_deliver": 86400,
+ }
+ self.assertEqual(len(data), 1)
+ for key, val in expected_value.items():
+ with self.subTest(key=key, val=val):
+ self.assertEqual(data[0][key], val)