perf: improve item wise register reports (backport #35908) (#35911)
perf: improve item wise register reports (#35908)
(cherry picked from commit 33ee01174bb909ee46bb3d714d6f94fa0aefe48f)
Co-authored-by: Anand Baburajan <anandbaburajan@gmail.com>
diff --git a/erpnext/accounts/report/item_wise_purchase_register/item_wise_purchase_register.py b/erpnext/accounts/report/item_wise_purchase_register/item_wise_purchase_register.py
index 924c14b..6fdb2f3 100644
--- a/erpnext/accounts/report/item_wise_purchase_register/item_wise_purchase_register.py
+++ b/erpnext/accounts/report/item_wise_purchase_register/item_wise_purchase_register.py
@@ -15,7 +15,6 @@
get_group_by_conditions,
get_tax_accounts,
)
-from erpnext.selling.report.item_wise_sales_history.item_wise_sales_history import get_item_details
def execute(filters=None):
@@ -40,6 +39,16 @@
tax_doctype="Purchase Taxes and Charges",
)
+ scrubbed_tax_fields = {}
+
+ for tax in tax_columns:
+ scrubbed_tax_fields.update(
+ {
+ tax + " Rate": frappe.scrub(tax + " Rate"),
+ tax + " Amount": frappe.scrub(tax + " Amount"),
+ }
+ )
+
po_pr_map = get_purchase_receipts_against_purchase_order(item_list)
data = []
@@ -50,11 +59,7 @@
if filters.get("group_by"):
grand_total = get_grand_total(filters, "Purchase Invoice")
- item_details = get_item_details()
-
for d in item_list:
- item_record = item_details.get(d.item_code)
-
purchase_receipt = None
if d.purchase_receipt:
purchase_receipt = d.purchase_receipt
@@ -67,8 +72,8 @@
row = {
"item_code": d.item_code,
- "item_name": item_record.item_name if item_record else d.item_name,
- "item_group": item_record.item_group if item_record else d.item_group,
+ "item_name": d.pi_item_name if d.pi_item_name else d.i_item_name,
+ "item_group": d.pi_item_group if d.pi_item_group else d.i_item_group,
"description": d.description,
"invoice": d.parent,
"posting_date": d.posting_date,
@@ -101,8 +106,8 @@
item_tax = itemised_tax.get(d.name, {}).get(tax, {})
row.update(
{
- frappe.scrub(tax + " Rate"): item_tax.get("tax_rate", 0),
- frappe.scrub(tax + " Amount"): item_tax.get("tax_amount", 0),
+ scrubbed_tax_fields[tax + " Rate"]: item_tax.get("tax_rate", 0),
+ scrubbed_tax_fields[tax + " Amount"]: item_tax.get("tax_amount", 0),
}
)
total_tax += flt(item_tax.get("tax_amount"))
@@ -325,15 +330,17 @@
`tabPurchase Invoice`.supplier, `tabPurchase Invoice`.remarks, `tabPurchase Invoice`.base_net_total,
`tabPurchase Invoice`.unrealized_profit_loss_account,
`tabPurchase Invoice Item`.`item_code`, `tabPurchase Invoice Item`.description,
- `tabPurchase Invoice Item`.`item_name`, `tabPurchase Invoice Item`.`item_group`,
+ `tabPurchase Invoice Item`.`item_name` as pi_item_name, `tabPurchase Invoice Item`.`item_group` as pi_item_group,
+ `tabItem`.`item_name` as i_item_name, `tabItem`.`item_group` as i_item_group,
`tabPurchase Invoice Item`.`project`, `tabPurchase Invoice Item`.`purchase_order`,
`tabPurchase Invoice Item`.`purchase_receipt`, `tabPurchase Invoice Item`.`po_detail`,
`tabPurchase Invoice Item`.`expense_account`, `tabPurchase Invoice Item`.`stock_qty`,
`tabPurchase Invoice Item`.`stock_uom`, `tabPurchase Invoice Item`.`base_net_amount`,
`tabPurchase Invoice`.`supplier_name`, `tabPurchase Invoice`.`mode_of_payment` {0}
- from `tabPurchase Invoice`, `tabPurchase Invoice Item`
+ from `tabPurchase Invoice`, `tabPurchase Invoice Item`, `tabItem`
where `tabPurchase Invoice`.name = `tabPurchase Invoice Item`.`parent` and
- `tabPurchase Invoice`.docstatus = 1 %s
+ `tabItem`.name = `tabPurchase Invoice Item`.`item_code` and
+ `tabPurchase Invoice`.docstatus = 1 %s
""".format(
additional_query_columns
)
diff --git a/erpnext/accounts/report/item_wise_sales_register/item_wise_sales_register.py b/erpnext/accounts/report/item_wise_sales_register/item_wise_sales_register.py
index 0ebe13f..bd7d02e 100644
--- a/erpnext/accounts/report/item_wise_sales_register/item_wise_sales_register.py
+++ b/erpnext/accounts/report/item_wise_sales_register/item_wise_sales_register.py
@@ -11,7 +11,6 @@
from erpnext.accounts.report.sales_register.sales_register import get_mode_of_payments
from erpnext.selling.report.item_wise_sales_history.item_wise_sales_history import (
get_customer_details,
- get_item_details,
)
@@ -35,6 +34,16 @@
if item_list:
itemised_tax, tax_columns = get_tax_accounts(item_list, columns, company_currency)
+ scrubbed_tax_fields = {}
+
+ for tax in tax_columns:
+ scrubbed_tax_fields.update(
+ {
+ tax + " Rate": frappe.scrub(tax + " Rate"),
+ tax + " Amount": frappe.scrub(tax + " Amount"),
+ }
+ )
+
mode_of_payments = get_mode_of_payments(set(d.parent for d in item_list))
so_dn_map = get_delivery_notes_against_sales_order(item_list)
@@ -47,11 +56,9 @@
grand_total = get_grand_total(filters, "Sales Invoice")
customer_details = get_customer_details()
- item_details = get_item_details()
for d in item_list:
customer_record = customer_details.get(d.customer)
- item_record = item_details.get(d.item_code)
delivery_note = None
if d.delivery_note:
@@ -64,8 +71,8 @@
row = {
"item_code": d.item_code,
- "item_name": item_record.item_name if item_record else d.item_name,
- "item_group": item_record.item_group if item_record else d.item_group,
+ "item_name": d.si_item_name if d.si_item_name else d.i_item_name,
+ "item_group": d.si_item_group if d.si_item_group else d.i_item_group,
"description": d.description,
"invoice": d.parent,
"posting_date": d.posting_date,
@@ -107,8 +114,8 @@
item_tax = itemised_tax.get(d.name, {}).get(tax, {})
row.update(
{
- frappe.scrub(tax + " Rate"): item_tax.get("tax_rate", 0),
- frappe.scrub(tax + " Amount"): item_tax.get("tax_amount", 0),
+ scrubbed_tax_fields[tax + " Rate"]: item_tax.get("tax_rate", 0),
+ scrubbed_tax_fields[tax + " Amount"]: item_tax.get("tax_amount", 0),
}
)
if item_tax.get("is_other_charges"):
@@ -404,15 +411,18 @@
`tabSales Invoice Item`.project,
`tabSales Invoice Item`.item_code, `tabSales Invoice Item`.description,
`tabSales Invoice Item`.`item_name`, `tabSales Invoice Item`.`item_group`,
+ `tabSales Invoice Item`.`item_name` as si_item_name, `tabSales Invoice Item`.`item_group` as si_item_group,
+ `tabItem`.`item_name` as i_item_name, `tabItem`.`item_group` as i_item_group,
`tabSales Invoice Item`.sales_order, `tabSales Invoice Item`.delivery_note,
`tabSales Invoice Item`.income_account, `tabSales Invoice Item`.cost_center,
`tabSales Invoice Item`.stock_qty, `tabSales Invoice Item`.stock_uom,
`tabSales Invoice Item`.base_net_rate, `tabSales Invoice Item`.base_net_amount,
`tabSales Invoice`.customer_name, `tabSales Invoice`.customer_group, `tabSales Invoice Item`.so_detail,
`tabSales Invoice`.update_stock, `tabSales Invoice Item`.uom, `tabSales Invoice Item`.qty {0}
- from `tabSales Invoice`, `tabSales Invoice Item`
- where `tabSales Invoice`.name = `tabSales Invoice Item`.parent
- and `tabSales Invoice`.docstatus = 1 {1}
+ from `tabSales Invoice`, `tabSales Invoice Item`, `tabItem`
+ where `tabSales Invoice`.name = `tabSales Invoice Item`.parent and
+ `tabItem`.name = `tabSales Invoice Item`.`item_code` and
+ `tabSales Invoice`.docstatus = 1 {1}
""".format(
additional_query_columns or "", conditions
),