fix: purchase invoice performance issue
diff --git a/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.py b/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.py
index 0e9f976..c95dafa 100644
--- a/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.py
+++ b/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.py
@@ -5,6 +5,7 @@
import frappe
from frappe import _, throw
from frappe.model.mapper import get_mapped_doc
+from frappe.query_builder.functions import Sum
from frappe.utils import cint, cstr, flt, formatdate, get_link_to_form, getdate, nowdate
import erpnext
@@ -1463,19 +1464,16 @@
def update_billing_status_in_pr(self, update_modified=True):
updated_pr = []
po_details = []
+
+ pr_details_billed_amt = self.get_pr_details_billed_amt()
+
for d in self.get("items"):
if d.pr_detail:
- billed_amt = frappe.db.sql(
- """select sum(amount) from `tabPurchase Invoice Item`
- where pr_detail=%s and docstatus=1""",
- d.pr_detail,
- )
- billed_amt = billed_amt and billed_amt[0][0] or 0
frappe.db.set_value(
"Purchase Receipt Item",
d.pr_detail,
"billed_amt",
- billed_amt,
+ flt(pr_details_billed_amt.get(d.pr_detail)),
update_modified=update_modified,
)
updated_pr.append(d.purchase_receipt)
@@ -1491,6 +1489,24 @@
pr_doc = frappe.get_doc("Purchase Receipt", pr)
update_billing_percentage(pr_doc, update_modified=update_modified)
+ def get_pr_details_billed_amt(self):
+ # Get billed amount based on purchase receipt item reference (pr_detail) in purchase invoice
+
+ pr_details_billed_amt = {}
+ pr_details = [d.get("pr_detail") for d in self.get("items") if d.get("pr_detail")]
+ if pr_details:
+ doctype = frappe.qb.DocType("Purchase Invoice Item")
+ query = (
+ frappe.qb.from_(doctype)
+ .select(doctype.pr_detail, Sum(doctype.amount))
+ .where(doctype.pr_detail.isin(pr_details) & doctype.docstatus == 1)
+ .groupby(doctype.pr_detail)
+ )
+
+ pr_details_billed_amt = frappe._dict(query.run(as_list=1))
+
+ return pr_details_billed_amt
+
def on_recurring(self, reference_doc, auto_repeat_doc):
self.due_date = None
diff --git a/erpnext/stock/doctype/purchase_receipt/purchase_receipt.py b/erpnext/stock/doctype/purchase_receipt/purchase_receipt.py
index 3739cb8..e6025ab 100644
--- a/erpnext/stock/doctype/purchase_receipt/purchase_receipt.py
+++ b/erpnext/stock/doctype/purchase_receipt/purchase_receipt.py
@@ -887,18 +887,10 @@
# Update Billing % based on pending accepted qty
total_amount, total_billed_amount = 0, 0
- for item in pr_doc.items:
- return_data = frappe.db.get_list(
- "Purchase Receipt",
- fields=["sum(abs(`tabPurchase Receipt Item`.qty)) as qty"],
- filters=[
- ["Purchase Receipt", "docstatus", "=", 1],
- ["Purchase Receipt", "is_return", "=", 1],
- ["Purchase Receipt Item", "purchase_receipt_item", "=", item.name],
- ],
- )
+ item_wise_returned_qty = get_item_wise_returned_qty(pr_doc)
- returned_qty = return_data[0].qty if return_data else 0
+ for item in pr_doc.items:
+ returned_qty = flt(item_wise_returned_qty.get(item.name))
returned_amount = flt(returned_qty) * flt(item.rate)
pending_amount = flt(item.amount) - returned_amount
total_billable_amount = pending_amount if item.billed_amt <= pending_amount else item.billed_amt
@@ -915,6 +907,27 @@
pr_doc.notify_update()
+def get_item_wise_returned_qty(pr_doc):
+ items = [d.name for d in pr_doc.items]
+
+ return frappe._dict(
+ frappe.get_all(
+ "Purchase Receipt",
+ fields=[
+ "`tabPurchase Receipt Item`.purchase_receipt_item",
+ "sum(abs(`tabPurchase Receipt Item`.qty)) as qty",
+ ],
+ filters=[
+ ["Purchase Receipt", "docstatus", "=", 1],
+ ["Purchase Receipt", "is_return", "=", 1],
+ ["Purchase Receipt Item", "purchase_receipt_item", "in", items],
+ ],
+ group_by="`tabPurchase Receipt Item`.purchase_receipt_item",
+ as_list=1,
+ )
+ )
+
+
@frappe.whitelist()
def make_purchase_invoice(source_name, target_doc=None):
from erpnext.accounts.party import get_payment_terms_template
diff --git a/erpnext/stock/doctype/purchase_receipt_item/purchase_receipt_item.json b/erpnext/stock/doctype/purchase_receipt_item/purchase_receipt_item.json
index 557bb59..7a350b9 100644
--- a/erpnext/stock/doctype/purchase_receipt_item/purchase_receipt_item.json
+++ b/erpnext/stock/doctype/purchase_receipt_item/purchase_receipt_item.json
@@ -859,7 +859,8 @@
"label": "Purchase Receipt Item",
"no_copy": 1,
"print_hide": 1,
- "read_only": 1
+ "read_only": 1,
+ "search_index": 1
},
{
"collapsible": 1,
@@ -974,7 +975,8 @@
"label": "Purchase Invoice Item",
"no_copy": 1,
"print_hide": 1,
- "read_only": 1
+ "read_only": 1,
+ "search_index": 1
},
{
"fieldname": "product_bundle",
@@ -1010,7 +1012,7 @@
"idx": 1,
"istable": 1,
"links": [],
- "modified": "2022-11-02 12:49:28.746701",
+ "modified": "2023-01-18 15:48:58.114923",
"modified_by": "Administrator",
"module": "Stock",
"name": "Purchase Receipt Item",