[Enhance] Multiselect for Cost Center and Project (#15208)
* add filter for Cost Center, make CC & Project multiselect field
* update queries according to project/cost_center multiselect
* add multiselect filter for cost_center and project
* update query according to multiselect
diff --git a/erpnext/accounts/report/financial_statements.py b/erpnext/accounts/report/financial_statements.py
index 3ac2eee..3a97f44 100644
--- a/erpnext/accounts/report/financial_statements.py
+++ b/erpnext/accounts/report/financial_statements.py
@@ -375,8 +375,15 @@
if filters:
if filters.get("project"):
+ if not isinstance(filters.get("project"), list):
+ projects = str(filters.get("project")).strip()
+ filters.project = [d.strip() for d in projects.split(',') if d]
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")))
company_finance_book = erpnext.get_default_finance_book(filters.get("company"))
@@ -392,9 +399,12 @@
def get_cost_center_cond(cost_center):
- lft, rgt = frappe.db.get_value("Cost Center", cost_center, ["lft", "rgt"])
- return """ cost_center in (select name from `tabCost Center` where lft >=%s and rgt <=%s)""" % (lft, rgt)
+ cost_centers = frappe.db.get_all("Cost Center", {"name": ["in", cost_center]},
+ ["name", "lft", "rgt"])
+ 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)
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 5ba0bde..602e671 100644
--- a/erpnext/accounts/report/general_ledger/general_ledger.js
+++ b/erpnext/accounts/report/general_ledger/general_ledger.js
@@ -57,10 +57,65 @@
}
},
{
+ "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": "Link",
- "options": "Project"
+ "fieldtype": "MultiSelect",
+ get_data: function() {
+ var projects = frappe.query_report.get_filter_value("project") || "";
+
+ const values = projects.split(/\s*,\s*/).filter(d => d);
+ const txt = projects.match(/[^,\s*]*$/)[0] || '';
+ let data = [];
+
+ frappe.call({
+ type: "GET",
+ method:'frappe.desk.search.search_link',
+ async: false,
+ no_spinner: true,
+ args: {
+ doctype: "Project",
+ txt: txt,
+ filters: {
+ "name": ["not in", values]
+ }
+ },
+ callback: function(r) {
+ data = r.results;
+ }
+ });
+ return data;
+ }
},
{
"fieldtype": "Break",
diff --git a/erpnext/accounts/report/general_ledger/general_ledger.py b/erpnext/accounts/report/general_ledger/general_ledger.py
index 26dfd0d..2d174ff 100644
--- a/erpnext/accounts/report/general_ledger/general_ledger.py
+++ b/erpnext/accounts/report/general_ledger/general_ledger.py
@@ -58,6 +58,14 @@
if filters.from_date > filters.to_date:
frappe.throw(_("From Date must be before To Date"))
+ if filters.get('project'):
+ projects = str(filters.get("project")).strip()
+ filters.project = [d.strip() for d in projects.split(',') if d]
+
+ if filters.get('cost_center'):
+ cost_centers = str(filters.get("cost_center")).strip()
+ filters.cost_center = [d.strip() for d in cost_centers.split(',') if d]
+
def validate_party(filters):
party_type, party = filters.get("party_type"), filters.get("party")
@@ -164,7 +172,10 @@
conditions.append("posting_date <=%(to_date)s")
if filters.get("project"):
- conditions.append("project=%(project)s")
+ 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):
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 a02c592..1804733 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
@@ -10,14 +10,63 @@
{
"fieldname":"cost_center",
"label": __("Cost Center"),
- "fieldtype": "Link",
- "options": "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": "Link",
- "options": "Project"
+ "fieldtype": "MultiSelect",
+ get_data: function() {
+ var projects = frappe.query_report.get_filter_value("project") || "";
+
+ const values = projects.split(/\s*,\s*/).filter(d => d);
+ const txt = projects.match(/[^,\s*]*$/)[0] || '';
+ let data = [];
+
+ frappe.call({
+ type: "GET",
+ method:'frappe.desk.search.search_link',
+ async: false,
+ no_spinner: true,
+ args: {
+ doctype: "Project",
+ txt: txt,
+ filters: {
+ "name": ["not in", values]
+ }
+ },
+ callback: function(r) {
+ data = r.results;
+ }
+ });
+ return data;
+ }
},
{
"fieldname": "accumulated_values",