fix: dimension filter query
diff --git a/erpnext/accounts/doctype/accounting_dimension/accounting_dimension.py b/erpnext/accounts/doctype/accounting_dimension/accounting_dimension.py
index f888d9e..b9d4da2 100644
--- a/erpnext/accounts/doctype/accounting_dimension/accounting_dimension.py
+++ b/erpnext/accounts/doctype/accounting_dimension/accounting_dimension.py
@@ -203,7 +203,7 @@
return all_dimensions
@frappe.whitelist()
-def get_dimension_filters():
+def get_dimension_filters(with_costcenter_and_project=False):
dimension_filters = frappe.db.sql("""
SELECT label, fieldname, document_type
FROM `tabAccounting Dimension`
@@ -214,6 +214,18 @@
FROM `tabAccounting Dimension Detail` c, `tabAccounting Dimension` p
WHERE c.parent = p.name""", as_dict=1)
+ if with_costcenter_and_project:
+ dimension_filters.extend([
+ {
+ 'fieldname': 'cost_center',
+ 'document_type': 'Cost Center'
+ },
+ {
+ 'fieldname': 'project',
+ 'document_type': 'Project'
+ }
+ ])
+
default_dimensions_map = {}
for dimension in default_dimensions:
default_dimensions_map.setdefault(dimension.company, {})
diff --git a/erpnext/controllers/queries.py b/erpnext/controllers/queries.py
index 8fe3816..015807d 100644
--- a/erpnext/controllers/queries.py
+++ b/erpnext/controllers/queries.py
@@ -493,6 +493,53 @@
'company': filters.get("company", "")
})
+@frappe.whitelist()
+@frappe.validate_and_sanitize_search_inputs
+def get_filtered_dimensions(doctype, txt, searchfield, start, page_len, filters):
+ from erpnext.accounts.doctype.accounting_dimension_filter.accounting_dimension_filter import get_dimension_filter_map
+ dimension_filters = get_dimension_filter_map()
+ dimension_filters = dimension_filters.get((filters.get('dimension'),filters.get('account')))
+ group_condition = ''
+ company_condition = ''
+
+ meta = frappe.get_meta(doctype)
+
+ if meta.is_tree:
+ group_condition = 'and is_group = 0 '
+
+ if meta.has_field('company'):
+ company_condition = 'and company = %s ' % (frappe.db.escape(filters.get('company')))
+
+ if dimension_filters:
+ if dimension_filters['allow_or_restrict'] == 'Allow':
+ query_selector = 'in'
+ else:
+ query_selector = 'not in'
+
+ if len(dimension_filters['allowed_dimensions']) == 1:
+ dimensions = tuple(dimension_filters['allowed_dimensions'] * 2)
+ else:
+ dimensions = tuple(dimension_filters['allowed_dimensions'])
+
+ result = frappe.db.sql("""SELECT name from `tab{doctype}` where
+ name {query_selector} {restricted}
+ {group_condition} {company_condition}
+ and {key} LIKE %(txt)s""".format(
+ doctype=doctype, query_selector=query_selector, restricted=dimensions,
+ group_condition = group_condition,
+ company_condition = company_condition,
+ key=searchfield), {
+ 'txt': '%' + txt + '%'
+ })
+
+ return result
+ else:
+ return frappe.db.sql("""
+ SELECT name from `tab{doctype}` where
+ {key} LIKE %(txt)s {group_condition} {company_condition}"""
+ .format(doctype=doctype, key=searchfield,
+ group_condition=group_condition, company_condition=company_condition),
+ { 'txt': '%' + txt + '%'})
@frappe.whitelist()
@frappe.validate_and_sanitize_search_inputs
diff --git a/erpnext/public/js/utils/dimension_tree_filter.js b/erpnext/public/js/utils/dimension_tree_filter.js
index b6720c0..34b5635 100644
--- a/erpnext/public/js/utils/dimension_tree_filter.js
+++ b/erpnext/public/js/utils/dimension_tree_filter.js
@@ -5,14 +5,18 @@
let doctypes_with_dimensions = ["GL Entry", "Sales Invoice", "Purchase Invoice", "Payment Entry", "Asset",
"Expense Claim", "Stock Entry", "Budget", "Payroll Entry", "Delivery Note", "Shipping Rule", "Loyalty Program",
"Fee Schedule", "Fee Structure", "Stock Reconciliation", "Travel Request", "Fees", "POS Profile", "Opening Invoice Creation Tool",
- "Subscription", "Purchase Order", "Journal Entry", "Material Request", "Purchase Receipt", "Landed Cost Item", "Asset"];
+ "Subscription", "Purchase Order", "Journal Entry", "Material Request", "Purchase Receipt", "Asset", "Asset Value Adjustment"];
let child_docs = ["Sales Invoice Item", "Purchase Invoice Item", "Purchase Order Item", "Journal Entry Account",
"Material Request Item", "Delivery Note Item", "Purchase Receipt Item", "Stock Entry Detail", "Payment Entry Deduction",
- "Landed Cost Item", "Asset Value Adjustment", "Opening Invoice Creation Tool Item", "Subscription Plan"];
+ "Landed Cost Item", "Asset Value Adjustment", "Opening Invoice Creation Tool Item", "Subscription Plan",
+ "Sales Taxes and Charges", "Purchase Taxes and Charges"];
frappe.call({
method: "erpnext.accounts.doctype.accounting_dimension.accounting_dimension.get_dimension_filters",
+ args: {
+ 'with_costcenter_and_project': true
+ },
callback: function(r) {
erpnext.dimension_filters = r.message[0];
default_dimensions = r.message[1];
@@ -24,11 +28,16 @@
onload: function(frm) {
erpnext.dimension_filters.forEach((dimension) => {
frappe.model.with_doctype(dimension['document_type'], () => {
- if(frappe.meta.has_field(dimension['document_type'], 'is_group')) {
- frm.set_query(dimension['fieldname'], {
- "is_group": 0
- });
- }
+ let parent_fields = [];
+ frappe.meta.get_docfields(doctype).forEach((df) => {
+ if (df.fieldtype === 'Link' && df.options === 'Account') {
+ parent_fields.push(df.fieldname);
+ } else if (df.fieldtype === 'Table') {
+ setup_child_filters(frm, df.options, df.fieldname, dimension['fieldname']);
+ };
+
+ setup_account_filters(frm, dimension['fieldname'], parent_fields);
+ });
});
});
},
@@ -67,17 +76,41 @@
child_docs.forEach((doctype) => {
frappe.ui.form.on(doctype, {
items_add: function(frm, cdt, cdn) {
- erpnext.dimension_filters.forEach((dimension) => {
- var row = frappe.get_doc(cdt, cdn);
- frm.script_manager.copy_from_first_row("items", row, [dimension['fieldname']]);
- });
+ copy_dimension(frm, cdt, cdn, "items");
},
accounts_add: function(frm, cdt, cdn) {
- erpnext.dimension_filters.forEach((dimension) => {
- var row = frappe.get_doc(cdt, cdn);
- frm.script_manager.copy_from_first_row("accounts", row, [dimension['fieldname']]);
- });
+ copy_dimension(frm, cdt, cdn, "accounts");
}
});
-});
\ No newline at end of file
+});
+
+let copy_dimension = function(frm, cdt, cdn, fieldname) {
+ erpnext.dimension_filters.forEach((dimension) => {
+ let row = frappe.get_doc(cdt, cdn);
+ frm.script_manager.copy_from_first_row(fieldname, row, [dimension['fieldname']]);
+ });
+}
+
+let setup_child_filters = function(frm, doctype, parentfield, dimension) {
+ let fields = [];
+
+ frappe.model.with_doctype(doctype, () => {
+ frappe.meta.get_docfields(doctype).forEach((df) => {
+ if (df.fieldtype === 'Link' && df.options === 'Account') {
+ fields.push(df.fieldname);
+ }
+ });
+
+ frm.set_query(dimension, parentfield, function(doc, cdt, cdn) {
+ let row = locals[cdt][cdn];
+ return erpnext.queries.get_filtered_dimensions(row, fields, dimension, doc.company);
+ });
+ });
+}
+
+let setup_account_filters = function(frm, dimension, fields) {
+ frm.set_query(dimension, function(doc) {
+ return erpnext.queries.get_filtered_dimensions(doc, fields, dimension, doc.company);
+ });
+}
\ No newline at end of file