[optimize] customer outstanding query
diff --git a/erpnext/selling/doctype/customer/customer.py b/erpnext/selling/doctype/customer/customer.py
index b060fba..dcff06d 100644
--- a/erpnext/selling/doctype/customer/customer.py
+++ b/erpnext/selling/doctype/customer/customer.py
@@ -197,27 +197,26 @@
outstanding_based_on_so = flt(outstanding_based_on_so[0][0]) if outstanding_based_on_so else 0.0
# Outstanding based on Delivery Note
- outstanding_based_on_dn = frappe.db.sql("""
- select
- sum(
- (
- (ifnull(dn_item.amount, 0) - ifnull((select sum(ifnull(amount, 0))
- from `tabSales Invoice Item`
- where ifnull(dn_detail, '') = dn_item.name and docstatus = 1), 0)
- )/dn.base_net_total
- )*dn.base_grand_total
- )
+ unmarked_delivery_note_items = frappe.db.sql("""select
+ dn_item.name, dn_item.amount, dn.base_net_total, dn.base_grand_total
from `tabDelivery Note` dn, `tabDelivery Note Item` dn_item
where
- dn.name = dn_item.parent and dn.customer=%s and dn.company=%s
+ dn.name = dn_item.parent
+ and dn.customer=%s and dn.company=%s
and dn.docstatus = 1 and dn.status != 'Stopped'
and ifnull(dn_item.against_sales_order, '') = ''
- and ifnull(dn_item.against_sales_invoice, '') = ''
- and ifnull(dn_item.amount, 0) > ifnull((select sum(ifnull(amount, 0))
- from `tabSales Invoice Item`
- where ifnull(dn_detail, '') = dn_item.name and docstatus = 1), 0)""", (customer, company))
+ and ifnull(dn_item.against_sales_invoice, '') = ''""", (customer, company), as_dict=True)
- outstanding_based_on_dn = flt(outstanding_based_on_dn[0][0]) if outstanding_based_on_dn else 0.0
+ outstanding_based_on_dn = 0.0
+
+ for dn_item in unmarked_delivery_note_items:
+ si_amount = frappe.db.sql("""select sum(ifnull(amount, 0))
+ from `tabSales Invoice Item`
+ where dn_detail = %s and docstatus = 1""", dn_item.name)[0][0]
+
+ if flt(dn_item.amount) > flt(si_amount):
+ outstanding_based_on_dn += ((flt(dn_item.amount) - flt(si_amount)) \
+ / dn_item.base_net_total) * dn_item.base_grand_total
return outstanding_based_on_gle + outstanding_based_on_so + outstanding_based_on_dn
diff --git a/erpnext/setup/page/setup_wizard/setup_wizard.py b/erpnext/setup/page/setup_wizard/setup_wizard.py
index a137033..764dd78 100644
--- a/erpnext/setup/page/setup_wizard/setup_wizard.py
+++ b/erpnext/setup/page/setup_wizard/setup_wizard.py
@@ -92,8 +92,10 @@
if args.get("add_sample_data"):
try:
make_sample_data()
+ frappe.clear_cache()
except FiscalYearError:
pass
+
except:
if args:
traceback = frappe.get_traceback()