fix(report): Cost center filters in financial statements and general ledger
diff --git a/erpnext/accounts/report/balance_sheet/balance_sheet.json b/erpnext/accounts/report/balance_sheet/balance_sheet.json
index 4e75344..f67a34b 100644
--- a/erpnext/accounts/report/balance_sheet/balance_sheet.json
+++ b/erpnext/accounts/report/balance_sheet/balance_sheet.json
@@ -1,17 +1,17 @@
{
"add_total_row": 0,
- "apply_user_permissions": 1,
"creation": "2014-07-14 05:24:20.385279",
"disabled": 0,
"docstatus": 0,
"doctype": "Report",
"idx": 2,
"is_standard": "Yes",
- "modified": "2017-02-24 20:12:47.161127",
+ "modified": "2018-09-07 12:18:21.850851",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Balance Sheet",
"owner": "Administrator",
+ "prepared_report": 0,
"ref_doctype": "GL Entry",
"report_name": "Balance Sheet",
"report_type": "Script Report",
diff --git a/erpnext/accounts/report/financial_statements.py b/erpnext/accounts/report/financial_statements.py
index 3a97f44..5b14b4b 100644
--- a/erpnext/accounts/report/financial_statements.py
+++ b/erpnext/accounts/report/financial_statements.py
@@ -351,7 +351,8 @@
"from_date": from_date,
"to_date": to_date,
"lft": root_lft,
- "rgt": root_rgt
+ "rgt": root_rgt,
+ "cost_center": filters.cost_center
},
as_dict=True)
@@ -381,10 +382,8 @@
additional_conditions.append("project = '%s'" % (frappe.db.escape(filters.get("project"))))
if filters.get("cost_center"):
- if not isinstance(filters.get("cost_center"), list):
- cost_centers = str(filters.get("cost_center")).strip()
- filters.cost_center = [d.strip() for d in cost_centers.split(',') if d]
- additional_conditions.append(get_cost_center_cond(filters.get("cost_center")))
+ filters.cost_center = get_cost_centers_with_children(filters.cost_center)
+ additional_conditions.append("cost_center in %(cost_center)s")
company_finance_book = erpnext.get_default_finance_book(filters.get("company"))
@@ -397,14 +396,17 @@
return " and {}".format(" and ".join(additional_conditions)) if additional_conditions else ""
+def get_cost_centers_with_children(cost_centers):
+ if not isinstance(cost_centers, list):
+ cost_centers = [d.strip() for d in str(cost_centers).strip().split(',') if d]
-def get_cost_center_cond(cost_center):
- cost_centers = frappe.db.get_all("Cost Center", {"name": ["in", cost_center]},
- ["name", "lft", "rgt"])
+ all_cost_centers = []
+ for d in cost_centers:
+ lft, rgt = frappe.db.get_value("Cost Center", d, ["lft", "rgt"])
+ children = frappe.get_all("Cost Center", filters={"lft": [">=", lft], "rgt": ["<=", rgt]})
+ all_cost_centers += [c.name for c in children]
- lft_rgt = " or ".join(["(lft >=%s and rgt <=%s)" % (d.lft, d.rgt) for d in cost_centers])
-
- return """ cost_center in (select name from `tabCost Center` where %s)""" % (lft_rgt)
+ return list(set(all_cost_centers))
def get_columns(periodicity, period_list, accumulated_values=1, company=None):
columns = [{
diff --git a/erpnext/accounts/report/general_ledger/general_ledger.js b/erpnext/accounts/report/general_ledger/general_ledger.js
index df69b51..602e671 100644
--- a/erpnext/accounts/report/general_ledger/general_ledger.js
+++ b/erpnext/accounts/report/general_ledger/general_ledger.js
@@ -18,21 +18,6 @@
"options": "Finance Book"
},
{
- "fieldname":"cost_center",
- "label": __("Cost Center"),
- "fieldtype": "Link",
- "options": "Cost Center",
- "get_query": function() {
- var company = frappe.query_report.get_filter_value('company');
- return {
- "doctype": "Cost Center",
- "filters": {
- "company": company,
- }
- }
- }
- },
- {
"fieldname":"from_date",
"label": __("From Date"),
"fieldtype": "Date",
diff --git a/erpnext/accounts/report/general_ledger/general_ledger.py b/erpnext/accounts/report/general_ledger/general_ledger.py
index 4524367..56663d3 100644
--- a/erpnext/accounts/report/general_ledger/general_ledger.py
+++ b/erpnext/accounts/report/general_ledger/general_ledger.py
@@ -8,7 +8,7 @@
from frappe.utils import getdate, cstr, flt, fmt_money
from frappe import _, _dict
from erpnext.accounts.utils import get_account_currency
-
+from erpnext.accounts.report.financial_statements import get_cost_centers_with_children
from six import iteritems
def execute(filters=None):
@@ -155,9 +155,8 @@
where lft>=%s and rgt<=%s and docstatus<2)""" % (lft, rgt))
if filters.get("cost_center"):
- lft, rgt = frappe.db.get_value("Cost Center", filters["cost_center"], ["lft", "rgt"])
- conditions.append("""cost_center in (select name from `tabCost Center`
- where lft>=%s and rgt<=%s and docstatus<2)""" % (lft, rgt))
+ filters.cost_center = get_cost_centers_with_children(filters.cost_center)
+ conditions.append("cost_center in %(cost_center)s")
if filters.get("voucher_no"):
conditions.append("voucher_no=%(voucher_no)s")
@@ -179,9 +178,6 @@
if filters.get("project"):
conditions.append("project in %(project)s")
- if filters.get("cost_center"):
- conditions.append("cost_center in %(cost_center)s")
-
company_finance_book = erpnext.get_default_finance_book(filters.get("company"))
if not filters.get("finance_book") or (filters.get("finance_book") == company_finance_book):
filters['finance_book'] = company_finance_book
diff --git a/erpnext/accounts/report/profit_and_loss_statement/profit_and_loss_statement.js b/erpnext/accounts/report/profit_and_loss_statement/profit_and_loss_statement.js
index 1804733..250e516 100644
--- a/erpnext/accounts/report/profit_and_loss_statement/profit_and_loss_statement.js
+++ b/erpnext/accounts/report/profit_and_loss_statement/profit_and_loss_statement.js
@@ -8,37 +8,6 @@
frappe.query_reports["Profit and Loss Statement"]["filters"].push(
{
- "fieldname":"cost_center",
- "label": __("Cost Center"),
- "fieldtype": "MultiSelect",
- get_data: function() {
- var cost_centers = frappe.query_report.get_filter_value("cost_center") || "";
-
- const values = cost_centers.split(/\s*,\s*/).filter(d => d);
- const txt = cost_centers.match(/[^,\s*]*$/)[0] || '';
- let data = [];
-
- frappe.call({
- type: "GET",
- method:'frappe.desk.search.search_link',
- async: false,
- no_spinner: true,
- args: {
- doctype: "Cost Center",
- txt: txt,
- filters: {
- "company": frappe.query_report.get_filter_value("company"),
- "name": ["not in", values]
- }
- },
- callback: function(r) {
- data = r.results;
- }
- });
- return data;
- }
- },
- {
"fieldname":"project",
"label": __("Project"),
"fieldtype": "MultiSelect",
diff --git a/erpnext/public/js/financial_statements.js b/erpnext/public/js/financial_statements.js
index f313ed1..36746cd 100644
--- a/erpnext/public/js/financial_statements.js
+++ b/erpnext/public/js/financial_statements.js
@@ -81,16 +81,32 @@
{
"fieldname":"cost_center",
"label": __("Cost Center"),
- "fieldtype": "Link",
- "options": "Cost Center",
- "get_query": function() {
- var company = frappe.query_report.get_filter_value('company');
- return {
- "doctype": "Cost Center",
- "filters": {
- "company": company,
+ "fieldtype": "MultiSelect",
+ get_data: function() {
+ var cost_centers = frappe.query_report.get_filter_value("cost_center") || "";
+
+ const values = cost_centers.split(/\s*,\s*/).filter(d => d);
+ const txt = cost_centers.match(/[^,\s*]*$/)[0] || '';
+ let data = [];
+
+ frappe.call({
+ type: "GET",
+ method:'frappe.desk.search.search_link',
+ async: false,
+ no_spinner: true,
+ args: {
+ doctype: "Cost Center",
+ txt: txt,
+ filters: {
+ "company": frappe.query_report.get_filter_value("company"),
+ "name": ["not in", values]
+ }
+ },
+ callback: function(r) {
+ data = r.results;
}
- };
+ });
+ return data;
}
},
{
diff --git a/erpnext/stock/get_item_details.py b/erpnext/stock/get_item_details.py
index a04d81d..c3cb437 100644
--- a/erpnext/stock/get_item_details.py
+++ b/erpnext/stock/get_item_details.py
@@ -392,15 +392,14 @@
price_list_rate = (args.rate / args.get('conversion_factor')
if args.get("conversion_factor") else args.rate)
- name = frappe.db.get_value('Item Price',
- {'item_code': args.item_code, 'price_list': args.price_list, 'currency': args.currency}, 'name')
-
- if name:
- item_price = frappe.get_doc('Item Price', name)
- item_price.price_list_rate = price_list_rate
- item_price.save()
- frappe.msgprint(_("Item Price updated for {0} in Price List {1}").format(args.item_code,
- args.price_list))
+ item_price = frappe.db.get_value('Item Price',
+ {'item_code': args.item_code, 'price_list': args.price_list, 'currency': args.currency},
+ ['name', 'price_list_rate'], as_dict=1)
+ if item_price and item_price.name:
+ if item_price.price_list_rate != price_list_rate:
+ frappe.db.set_value('Item Price', item_price.name, "price_list_rate", price_list_rate)
+ frappe.msgprint(_("Item Price updated for {0} in Price List {1}").format(args.item_code,
+ args.price_list), alert=True)
else:
item_price = frappe.get_doc({
"doctype": "Item Price",