Merge pull request #39384 from deepeshgarg007/income_account_filter
feat: Income account filter in item-wise reports
diff --git a/.github/workflows/release.yml b/.github/workflows/release.yml
index 37bb37e..93b1732 100644
--- a/.github/workflows/release.yml
+++ b/.github/workflows/release.yml
@@ -16,7 +16,7 @@
- name: Setup Node.js
uses: actions/setup-node@v2
with:
- node-version: 18
+ node-version: 20
- name: Setup dependencies
run: |
npm install @semantic-release/git @semantic-release/exec --no-save
diff --git a/erpnext/accounts/doctype/bank_reconciliation_tool/test_bank_reconciliation_tool.py b/erpnext/accounts/doctype/bank_reconciliation_tool/test_bank_reconciliation_tool.py
index 5a6bb69..adf5925 100644
--- a/erpnext/accounts/doctype/bank_reconciliation_tool/test_bank_reconciliation_tool.py
+++ b/erpnext/accounts/doctype/bank_reconciliation_tool/test_bank_reconciliation_tool.py
@@ -76,6 +76,7 @@
"deposit": 100,
"bank_account": self.bank_account,
"reference_number": "123",
+ "currency": "INR",
}
)
.save()
diff --git a/erpnext/accounts/doctype/bank_transaction/bank_transaction.py b/erpnext/accounts/doctype/bank_transaction/bank_transaction.py
index c38d273..fef3b56 100644
--- a/erpnext/accounts/doctype/bank_transaction/bank_transaction.py
+++ b/erpnext/accounts/doctype/bank_transaction/bank_transaction.py
@@ -49,6 +49,24 @@
def validate(self):
self.validate_duplicate_references()
+ self.validate_currency()
+
+ def validate_currency(self):
+ """
+ Bank Transaction should be on the same currency as the Bank Account.
+ """
+ if self.currency and self.bank_account:
+ account = frappe.get_cached_value("Bank Account", self.bank_account, "account")
+ account_currency = frappe.get_cached_value("Account", account, "account_currency")
+
+ if self.currency != account_currency:
+ frappe.throw(
+ _(
+ "Transaction currency: {0} cannot be different from Bank Account({1}) currency: {2}"
+ ).format(
+ frappe.bold(self.currency), frappe.bold(self.bank_account), frappe.bold(account_currency)
+ )
+ )
def set_status(self):
if self.docstatus == 2:
diff --git a/erpnext/accounts/doctype/purchase_taxes_and_charges/purchase_taxes_and_charges.json b/erpnext/accounts/doctype/purchase_taxes_and_charges/purchase_taxes_and_charges.json
index 347cae0..adab54b 100644
--- a/erpnext/accounts/doctype/purchase_taxes_and_charges/purchase_taxes_and_charges.json
+++ b/erpnext/accounts/doctype/purchase_taxes_and_charges/purchase_taxes_and_charges.json
@@ -126,7 +126,7 @@
"fieldname": "rate",
"fieldtype": "Float",
"in_list_view": 1,
- "label": "Rate",
+ "label": "Tax Rate",
"oldfieldname": "rate",
"oldfieldtype": "Currency"
},
@@ -230,7 +230,7 @@
"idx": 1,
"istable": 1,
"links": [],
- "modified": "2021-08-05 20:04:36.618240",
+ "modified": "2024-01-14 10:04:36.618240",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Purchase Taxes and Charges",
@@ -239,4 +239,4 @@
"sort_field": "modified",
"sort_order": "DESC",
"track_changes": 1
-}
\ No newline at end of file
+}
diff --git a/erpnext/accounts/doctype/subscription/subscription.py b/erpnext/accounts/doctype/subscription/subscription.py
index 72e574c..ce56a7b 100644
--- a/erpnext/accounts/doctype/subscription/subscription.py
+++ b/erpnext/accounts/doctype/subscription/subscription.py
@@ -16,6 +16,7 @@
date_diff,
flt,
get_last_day,
+ get_link_to_form,
getdate,
nowdate,
)
@@ -317,6 +318,37 @@
if self.is_new():
self.set_subscription_status()
+ self.validate_party_billing_currency()
+
+ def validate_party_billing_currency(self):
+ """
+ Subscription should be of the same currency as the Party's default billing currency or company default.
+ """
+ if self.party:
+ party_billing_currency = frappe.get_cached_value(
+ self.party_type, self.party, "default_currency"
+ ) or frappe.get_cached_value("Company", self.company, "default_currency")
+
+ plans = [x.plan for x in self.plans]
+ subscription_plan_currencies = frappe.db.get_all(
+ "Subscription Plan", filters={"name": ("in", plans)}, fields=["name", "currency"]
+ )
+ unsupported_plans = []
+ for x in subscription_plan_currencies:
+ if x.currency != party_billing_currency:
+ unsupported_plans.append("{0}".format(get_link_to_form("Subscription Plan", x.name)))
+
+ if unsupported_plans:
+ unsupported_plans = [
+ _(
+ "Below Subscription Plans are of different currency to the party default billing currency/Company currency: {0}"
+ ).format(frappe.bold(party_billing_currency))
+ ] + unsupported_plans
+
+ frappe.throw(
+ unsupported_plans, frappe.ValidationError, "Unsupported Subscription Plans", as_list=True
+ )
+
def validate_trial_period(self) -> None:
"""
Runs sanity checks on trial period dates for the `Subscription`
diff --git a/erpnext/accounts/doctype/subscription/test_subscription.py b/erpnext/accounts/doctype/subscription/test_subscription.py
index 785fd04..37326fd 100644
--- a/erpnext/accounts/doctype/subscription/test_subscription.py
+++ b/erpnext/accounts/doctype/subscription/test_subscription.py
@@ -463,7 +463,7 @@
subscription = create_subscription(
start_date="2018-01-01",
generate_invoice_at="Beginning of the current subscription period",
- plans=[{"plan": "_Test Plan Multicurrency", "qty": 1}],
+ plans=[{"plan": "_Test Plan Multicurrency", "qty": 1, "currency": "USD"}],
party="_Test Subscription Customer",
)
@@ -528,13 +528,21 @@
def make_plans():
- create_plan(plan_name="_Test Plan Name", cost=900)
- create_plan(plan_name="_Test Plan Name 2", cost=1999)
+ create_plan(plan_name="_Test Plan Name", cost=900, currency="INR")
+ create_plan(plan_name="_Test Plan Name 2", cost=1999, currency="INR")
create_plan(
- plan_name="_Test Plan Name 3", cost=1999, billing_interval="Day", billing_interval_count=14
+ plan_name="_Test Plan Name 3",
+ cost=1999,
+ billing_interval="Day",
+ billing_interval_count=14,
+ currency="INR",
)
create_plan(
- plan_name="_Test Plan Name 4", cost=20000, billing_interval="Month", billing_interval_count=3
+ plan_name="_Test Plan Name 4",
+ cost=20000,
+ billing_interval="Month",
+ billing_interval_count=3,
+ currency="INR",
)
create_plan(
plan_name="_Test Plan Multicurrency", cost=50, billing_interval="Month", currency="USD"
diff --git a/erpnext/accounts/doctype/subscription_plan/subscription_plan.json b/erpnext/accounts/doctype/subscription_plan/subscription_plan.json
index 563df79..bc1f579 100644
--- a/erpnext/accounts/doctype/subscription_plan/subscription_plan.json
+++ b/erpnext/accounts/doctype/subscription_plan/subscription_plan.json
@@ -41,7 +41,8 @@
"fieldname": "currency",
"fieldtype": "Link",
"label": "Currency",
- "options": "Currency"
+ "options": "Currency",
+ "reqd": 1
},
{
"fieldname": "column_break_3",
@@ -148,10 +149,11 @@
}
],
"links": [],
- "modified": "2021-12-10 15:24:15.794477",
+ "modified": "2024-01-14 17:59:34.687977",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Subscription Plan",
+ "naming_rule": "By fieldname",
"owner": "Administrator",
"permissions": [
{
@@ -193,5 +195,6 @@
],
"sort_field": "modified",
"sort_order": "DESC",
+ "states": [],
"track_changes": 1
}
\ No newline at end of file
diff --git a/erpnext/accounts/doctype/subscription_plan/subscription_plan.py b/erpnext/accounts/doctype/subscription_plan/subscription_plan.py
index 118d254..cdfa3e5 100644
--- a/erpnext/accounts/doctype/subscription_plan/subscription_plan.py
+++ b/erpnext/accounts/doctype/subscription_plan/subscription_plan.py
@@ -24,7 +24,7 @@
billing_interval_count: DF.Int
cost: DF.Currency
cost_center: DF.Link | None
- currency: DF.Link | None
+ currency: DF.Link
item: DF.Link
payment_gateway: DF.Link | None
plan_name: DF.Data
diff --git a/erpnext/accounts/report/customer_ledger_summary/customer_ledger_summary.py b/erpnext/accounts/report/customer_ledger_summary/customer_ledger_summary.py
index a2c0f86..f4a0175 100644
--- a/erpnext/accounts/report/customer_ledger_summary/customer_ledger_summary.py
+++ b/erpnext/accounts/report/customer_ledger_summary/customer_ledger_summary.py
@@ -376,6 +376,10 @@
if not income_or_expense_accounts:
# prevent empty 'in' condition
income_or_expense_accounts.append("")
+ else:
+ # escape '%' in account name
+ # ignoring frappe.db.escape as it replaces single quotes with double quotes
+ income_or_expense_accounts = [x.replace("%", "%%") for x in income_or_expense_accounts]
accounts_query = (
qb.from_(gl)
diff --git a/erpnext/controllers/queries.py b/erpnext/controllers/queries.py
index 2650753..8ebdcc5 100644
--- a/erpnext/controllers/queries.py
+++ b/erpnext/controllers/queries.py
@@ -421,23 +421,14 @@
meta = frappe.get_meta(doctype, cached=True)
searchfields = meta.get_search_fields()
- query = get_batches_from_stock_ledger_entries(searchfields, txt, filters)
- bundle_query = get_batches_from_serial_and_batch_bundle(searchfields, txt, filters)
-
- data = (
- frappe.qb.from_((query) + (bundle_query))
- .select("batch_no", "qty", "manufacturing_date", "expiry_date")
- .offset(start)
- .limit(page_len)
+ batches = get_batches_from_stock_ledger_entries(searchfields, txt, filters, start, page_len)
+ batches.extend(
+ get_batches_from_serial_and_batch_bundle(searchfields, txt, filters, start, page_len)
)
- for field in searchfields:
- data = data.select(field)
+ filtered_batches = get_filterd_batches(batches)
- data = data.run()
- data = get_filterd_batches(data)
-
- return data
+ return filtered_batches
def get_filterd_batches(data):
@@ -457,7 +448,7 @@
return filterd_batch
-def get_batches_from_stock_ledger_entries(searchfields, txt, filters):
+def get_batches_from_stock_ledger_entries(searchfields, txt, filters, start=0, page_len=100):
stock_ledger_entry = frappe.qb.DocType("Stock Ledger Entry")
batch_table = frappe.qb.DocType("Batch")
@@ -479,6 +470,8 @@
& (stock_ledger_entry.batch_no.isnotnull())
)
.groupby(stock_ledger_entry.batch_no, stock_ledger_entry.warehouse)
+ .offset(start)
+ .limit(page_len)
)
query = query.select(
@@ -493,16 +486,16 @@
query = query.select(batch_table[field])
if txt:
- txt_condition = batch_table.name.like(txt)
+ txt_condition = batch_table.name.like("%{0}%".format(txt))
for field in searchfields + ["name"]:
- txt_condition |= batch_table[field].like(txt)
+ txt_condition |= batch_table[field].like("%{0}%".format(txt))
query = query.where(txt_condition)
- return query
+ return query.run(as_list=1) or []
-def get_batches_from_serial_and_batch_bundle(searchfields, txt, filters):
+def get_batches_from_serial_and_batch_bundle(searchfields, txt, filters, start=0, page_len=100):
bundle = frappe.qb.DocType("Serial and Batch Entry")
stock_ledger_entry = frappe.qb.DocType("Stock Ledger Entry")
batch_table = frappe.qb.DocType("Batch")
@@ -527,6 +520,8 @@
& (stock_ledger_entry.serial_and_batch_bundle.isnotnull())
)
.groupby(bundle.batch_no, bundle.warehouse)
+ .offset(start)
+ .limit(page_len)
)
bundle_query = bundle_query.select(
@@ -541,13 +536,13 @@
bundle_query = bundle_query.select(batch_table[field])
if txt:
- txt_condition = batch_table.name.like(txt)
+ txt_condition = batch_table.name.like("%{0}%".format(txt))
for field in searchfields + ["name"]:
- txt_condition |= batch_table[field].like(txt)
+ txt_condition |= batch_table[field].like("%{0}%".format(txt))
bundle_query = bundle_query.where(txt_condition)
- return bundle_query
+ return bundle_query.run(as_list=1)
@frappe.whitelist()
diff --git a/erpnext/stock/report/stock_ageing/stock_ageing.py b/erpnext/stock/report/stock_ageing/stock_ageing.py
index d0929a0..f055c6c 100644
--- a/erpnext/stock/report/stock_ageing/stock_ageing.py
+++ b/erpnext/stock/report/stock_ageing/stock_ageing.py
@@ -3,7 +3,7 @@
from operator import itemgetter
-from typing import Dict, List, Tuple, Union
+from typing import Dict, Iterator, List, Tuple, Union
import frappe
from frappe import _
@@ -231,10 +231,12 @@
consumed/updated and maintained via FIFO. **
}
"""
- if self.sle is None:
- self.sle = self.__get_stock_ledger_entries()
- for d in self.sle:
+ stock_ledger_entries = self.sle
+ if stock_ledger_entries is None:
+ stock_ledger_entries = self.__get_stock_ledger_entries()
+
+ for d in stock_ledger_entries:
key, fifo_queue, transferred_item_key = self.__init_key_stores(d)
if d.voucher_type == "Stock Reconciliation":
@@ -251,6 +253,9 @@
self.__update_balances(d, key)
+ # Note that stock_ledger_entries is an iterator, you can not reuse it like a list
+ del stock_ledger_entries
+
if not self.filters.get("show_warehouse_wise_stock"):
# (Item 1, WH 1), (Item 1, WH 2) => (Item 1)
self.item_details = self.__aggregate_details_by_item(self.item_details)
@@ -381,7 +386,7 @@
return item_aggregated_data
- def __get_stock_ledger_entries(self) -> List[Dict]:
+ def __get_stock_ledger_entries(self) -> Iterator[Dict]:
sle = frappe.qb.DocType("Stock Ledger Entry")
item = self.__get_item_query() # used as derived table in sle query
@@ -418,7 +423,7 @@
sle_query = sle_query.orderby(sle.posting_date, sle.posting_time, sle.creation, sle.actual_qty)
- return sle_query.run(as_dict=True)
+ return sle_query.run(as_dict=True, as_iterator=True)
def __get_item_query(self) -> str:
item_table = frappe.qb.DocType("Item")