perf: various minor perf fixes for ledger postings (#26775)
* perf: only validate if voucher is journal entry
* perf: optimize merge GLE
- Order fields such that comparison will fail faster
- Break out of loops if not matched
* perf: don't try to match SLE if count mismatch
* refactor: simplify initialize_previous_data
* perf: use cache for fetching valuation_method
These are set only once fields
* refactor: simplify get_future_stock_vouchers
* refactor: simplify get_voucherwise_gl_entries
* perf: fetch only required fields for GL comparison
`select *` fetches all fields, output of this function is only used for
comparing.
* perf: reorder conditions in PL cost center check
* perf: reduce query while validating new gle
* perf: use cache for validating warehouse props
These properties don't change often, no need to query everytime.
* perf: use cached stock settings to validate SLE
* docs: update misleading docstring
Co-authored-by: Marica <maricadsouza221197@gmail.com>
diff --git a/erpnext/accounts/doctype/gl_entry/gl_entry.py b/erpnext/accounts/doctype/gl_entry/gl_entry.py
index 11465b7..0844995 100644
--- a/erpnext/accounts/doctype/gl_entry/gl_entry.py
+++ b/erpnext/accounts/doctype/gl_entry/gl_entry.py
@@ -58,8 +58,8 @@
if not self.get(k):
frappe.throw(_("{0} is required").format(_(self.meta.get_label(k))))
- account_type = frappe.get_cached_value("Account", self.account, "account_type")
if not (self.party_type and self.party):
+ account_type = frappe.get_cached_value("Account", self.account, "account_type")
if account_type == "Receivable":
frappe.throw(_("{0} {1}: Customer is required against Receivable account {2}")
.format(self.voucher_type, self.voucher_no, self.account))
@@ -73,15 +73,19 @@
.format(self.voucher_type, self.voucher_no, self.account))
def pl_must_have_cost_center(self):
- if frappe.get_cached_value("Account", self.account, "report_type") == "Profit and Loss":
- if not self.cost_center and self.voucher_type != 'Period Closing Voucher':
- msg = _("{0} {1}: Cost Center is required for 'Profit and Loss' account {2}.").format(
- self.voucher_type, self.voucher_no, self.account)
- msg += " "
- msg += _("Please set the cost center field in {0} or setup a default Cost Center for the Company.").format(
- self.voucher_type)
+ """Validate that profit and loss type account GL entries have a cost center."""
- frappe.throw(msg, title=_("Missing Cost Center"))
+ if self.cost_center or self.voucher_type == 'Period Closing Voucher':
+ return
+
+ if frappe.get_cached_value("Account", self.account, "report_type") == "Profit and Loss":
+ msg = _("{0} {1}: Cost Center is required for 'Profit and Loss' account {2}.").format(
+ self.voucher_type, self.voucher_no, self.account)
+ msg += " "
+ msg += _("Please set the cost center field in {0} or setup a default Cost Center for the Company.").format(
+ self.voucher_type)
+
+ frappe.throw(msg, title=_("Missing Cost Center"))
def validate_dimensions_for_pl_and_bs(self):
account_type = frappe.db.get_value("Account", self.account, "report_type")
diff --git a/erpnext/accounts/general_ledger.py b/erpnext/accounts/general_ledger.py
index 25d2cf1..4c7c567 100644
--- a/erpnext/accounts/general_ledger.py
+++ b/erpnext/accounts/general_ledger.py
@@ -100,8 +100,8 @@
return merged_gl_map
def check_if_in_list(gle, gl_map, dimensions=None):
- account_head_fieldnames = ['party_type', 'party', 'against_voucher', 'against_voucher_type',
- 'cost_center', 'project', 'voucher_detail_no']
+ account_head_fieldnames = ['voucher_detail_no', 'party', 'against_voucher',
+ 'cost_center', 'against_voucher_type', 'party_type', 'project']
if dimensions:
account_head_fieldnames = account_head_fieldnames + dimensions
@@ -110,10 +110,12 @@
same_head = True
if e.account != gle.account:
same_head = False
+ continue
for fieldname in account_head_fieldnames:
if cstr(e.get(fieldname)) != cstr(gle.get(fieldname)):
same_head = False
+ break
if same_head:
return e
@@ -143,16 +145,19 @@
validate_expense_against_budget(args)
def validate_cwip_accounts(gl_map):
- cwip_enabled = any(cint(ac.enable_cwip_accounting) for ac in frappe.db.get_all("Asset Category","enable_cwip_accounting"))
+ """Validate that CWIP account are not used in Journal Entry"""
+ if gl_map and gl_map[0].voucher_type != "Journal Entry":
+ return
- if cwip_enabled and gl_map[0].voucher_type == "Journal Entry":
- cwip_accounts = [d[0] for d in frappe.db.sql("""select name from tabAccount
- where account_type = 'Capital Work in Progress' and is_group=0""")]
+ cwip_enabled = any(cint(ac.enable_cwip_accounting) for ac in frappe.db.get_all("Asset Category", "enable_cwip_accounting"))
+ if cwip_enabled:
+ cwip_accounts = [d[0] for d in frappe.db.sql("""select name from tabAccount
+ where account_type = 'Capital Work in Progress' and is_group=0""")]
- for entry in gl_map:
- if entry.account in cwip_accounts:
- frappe.throw(
- _("Account: <b>{0}</b> is capital Work in progress and can not be updated by Journal Entry").format(entry.account))
+ for entry in gl_map:
+ if entry.account in cwip_accounts:
+ frappe.throw(
+ _("Account: <b>{0}</b> is capital Work in progress and can not be updated by Journal Entry").format(entry.account))
def round_off_debit_credit(gl_map):
precision = get_field_precision(frappe.get_meta("GL Entry").get_field("debit"),
diff --git a/erpnext/accounts/utils.py b/erpnext/accounts/utils.py
index 9272bc4..5b58e87 100644
--- a/erpnext/accounts/utils.py
+++ b/erpnext/accounts/utils.py
@@ -920,7 +920,6 @@
_delete_gl_entries(voucher_type, voucher_no)
def get_future_stock_vouchers(posting_date, posting_time, for_warehouses=None, for_items=None, company=None):
- future_stock_vouchers = []
values = []
condition = ""
@@ -936,30 +935,46 @@
condition += " and company = %s"
values.append(company)
- for d in frappe.db.sql("""select distinct sle.voucher_type, sle.voucher_no
+ future_stock_vouchers = frappe.db.sql("""select distinct sle.voucher_type, sle.voucher_no
from `tabStock Ledger Entry` sle
where
timestamp(sle.posting_date, sle.posting_time) >= timestamp(%s, %s)
and is_cancelled = 0
{condition}
order by timestamp(sle.posting_date, sle.posting_time) asc, creation asc for update""".format(condition=condition),
- tuple([posting_date, posting_time] + values), as_dict=True):
- future_stock_vouchers.append([d.voucher_type, d.voucher_no])
+ tuple([posting_date, posting_time] + values), as_dict=True)
- return future_stock_vouchers
+ return [(d.voucher_type, d.voucher_no) for d in future_stock_vouchers]
def get_voucherwise_gl_entries(future_stock_vouchers, posting_date):
+ """ Get voucherwise list of GL entries.
+
+ Only fetches GLE fields required for comparing with new GLE.
+ Check compare_existing_and_expected_gle function below.
+ """
gl_entries = {}
- if future_stock_vouchers:
- for d in frappe.db.sql("""select * from `tabGL Entry`
- where posting_date >= %s and voucher_no in (%s)""" %
- ('%s', ', '.join(['%s']*len(future_stock_vouchers))),
- tuple([posting_date] + [d[1] for d in future_stock_vouchers]), as_dict=1):
- gl_entries.setdefault((d.voucher_type, d.voucher_no), []).append(d)
+ if not future_stock_vouchers:
+ return gl_entries
+
+ voucher_nos = [d[1] for d in future_stock_vouchers]
+
+ gles = frappe.db.sql("""
+ select name, account, credit, debit, cost_center, project
+ from `tabGL Entry`
+ where
+ posting_date >= %s and voucher_no in (%s)""" %
+ ('%s', ', '.join(['%s'] * len(voucher_nos))),
+ tuple([posting_date] + voucher_nos), as_dict=1)
+
+ for d in gles:
+ gl_entries.setdefault((d.voucher_type, d.voucher_no), []).append(d)
return gl_entries
def compare_existing_and_expected_gle(existing_gle, expected_gle, precision):
+ if len(existing_gle) != len(expected_gle):
+ return False
+
matched = True
for entry in expected_gle:
account_existed = False
diff --git a/erpnext/stock/doctype/stock_ledger_entry/stock_ledger_entry.py b/erpnext/stock/doctype/stock_ledger_entry/stock_ledger_entry.py
index b4f4583..be1f00e 100644
--- a/erpnext/stock/doctype/stock_ledger_entry/stock_ledger_entry.py
+++ b/erpnext/stock/doctype/stock_ledger_entry/stock_ledger_entry.py
@@ -55,8 +55,8 @@
"sum(actual_qty)") or 0
frappe.db.set_value("Batch", self.batch_no, "batch_qty", batch_qty)
- #check for item quantity available in stock
def actual_amt_check(self):
+ """Validate that qty at warehouse for selected batch is >=0"""
if self.batch_no and not self.get("allow_negative_stock"):
batch_bal_after_transaction = flt(frappe.db.sql("""select sum(actual_qty)
from `tabStock Ledger Entry`
@@ -107,7 +107,7 @@
self.stock_uom = item_det.stock_uom
def check_stock_frozen_date(self):
- stock_settings = frappe.get_doc('Stock Settings', 'Stock Settings')
+ stock_settings = frappe.get_cached_doc('Stock Settings')
if stock_settings.stock_frozen_upto:
if (getdate(self.posting_date) <= getdate(stock_settings.stock_frozen_upto)
diff --git a/erpnext/stock/stock_ledger.py b/erpnext/stock/stock_ledger.py
index f990ce0..eddd048 100644
--- a/erpnext/stock/stock_ledger.py
+++ b/erpnext/stock/stock_ledger.py
@@ -279,15 +279,13 @@
}
"""
- self.data.setdefault(args.warehouse, frappe._dict())
- warehouse_dict = self.data[args.warehouse]
previous_sle = get_previous_sle_of_current_voucher(args)
- warehouse_dict.previous_sle = previous_sle
- for key in ("qty_after_transaction", "valuation_rate", "stock_value"):
- setattr(warehouse_dict, key, flt(previous_sle.get(key)))
-
- warehouse_dict.update({
+ self.data[args.warehouse] = frappe._dict({
+ "previous_sle": previous_sle,
+ "qty_after_transaction": flt(previous_sle.qty_after_transaction),
+ "valuation_rate": flt(previous_sle.valuation_rate),
+ "stock_value": flt(previous_sle.stock_value),
"prev_stock_value": previous_sle.stock_value or 0.0,
"stock_queue": json.loads(previous_sle.stock_queue or "[]"),
"stock_value_difference": 0.0
diff --git a/erpnext/stock/utils.py b/erpnext/stock/utils.py
index b57b2aa..9f6d0a8 100644
--- a/erpnext/stock/utils.py
+++ b/erpnext/stock/utils.py
@@ -224,7 +224,7 @@
def get_valuation_method(item_code):
"""get valuation method from item or default"""
- val_method = frappe.db.get_value('Item', item_code, 'valuation_method')
+ val_method = frappe.db.get_value('Item', item_code, 'valuation_method', cache=True)
if not val_method:
val_method = frappe.db.get_value("Stock Settings", None, "valuation_method") or "FIFO"
return val_method
@@ -275,17 +275,17 @@
return valid_serial_nos
def validate_warehouse_company(warehouse, company):
- warehouse_company = frappe.db.get_value("Warehouse", warehouse, "company")
+ warehouse_company = frappe.db.get_value("Warehouse", warehouse, "company", cache=True)
if warehouse_company and warehouse_company != company:
frappe.throw(_("Warehouse {0} does not belong to company {1}").format(warehouse, company),
InvalidWarehouseCompany)
def is_group_warehouse(warehouse):
- if frappe.db.get_value("Warehouse", warehouse, "is_group"):
+ if frappe.db.get_value("Warehouse", warehouse, "is_group", cache=True):
frappe.throw(_("Group node warehouse is not allowed to select for transactions"))
def validate_disabled_warehouse(warehouse):
- if frappe.db.get_value("Warehouse", warehouse, "disabled"):
+ if frappe.db.get_value("Warehouse", warehouse, "disabled", cache=True):
frappe.throw(_("Disabled Warehouse {0} cannot be used for this transaction.").format(get_link_to_form('Warehouse', warehouse)))
def update_included_uom_in_report(columns, result, include_uom, conversion_factors):