Undo replace of frappe.db.sql with frappe.get_list (#14074)
diff --git a/erpnext/accounts/utils.py b/erpnext/accounts/utils.py
index a33f867..f53f924 100644
--- a/erpnext/accounts/utils.py
+++ b/erpnext/accounts/utils.py
@@ -681,23 +681,29 @@
def get_children(doctype, parent, company, is_root=False):
from erpnext.accounts.report.financial_statements import sort_accounts
- parent_fieldname = 'parent_' + doctype.lower().replace(' ', '_')
- fields = [
- 'name as value',
- 'is_group as expandable'
- ]
- filters = [['docstatus', '<', 2]]
+ fieldname = frappe.db.escape(doctype.lower().replace(' ','_'))
+ doctype = frappe.db.escape(doctype)
+
+ # root
if is_root:
- fields += ['root_type', 'report_type', 'account_currency'] if doctype == 'Account' else []
- filters.append([parent_fieldname, '=', ''])
- filters.append(['company', '=', company])
-
+ fields = ", root_type, report_type, account_currency" if doctype=="Account" else ""
+ acc = frappe.db.sql(""" select
+ name as value, is_group as expandable {fields}
+ from `tab{doctype}`
+ where ifnull(`parent_{fieldname}`,'') = ''
+ and `company` = %s and docstatus<2
+ order by name""".format(fields=fields, fieldname = fieldname, doctype=doctype),
+ company, as_dict=1)
else:
- fields += ['account_currency'] if doctype == 'Account' else []
- fields += [parent_fieldname + ' as parent']
-
-
- acc = frappe.get_list(doctype, fields=fields, filters=filters)
+ # other
+ fields = ", account_currency" if doctype=="Account" else ""
+ acc = frappe.db.sql("""select
+ name as value, is_group as expandable, parent_{fieldname} as parent {fields}
+ from `tab{doctype}`
+ where ifnull(`parent_{fieldname}`,'') = %s
+ and docstatus<2
+ order by name""".format(fields=fields, fieldname=fieldname, doctype=doctype),
+ parent, as_dict=1)
if doctype == 'Account':
sort_accounts(acc, is_root, key="value")
diff --git a/erpnext/agriculture/doctype/land_unit/land_unit.py b/erpnext/agriculture/doctype/land_unit/land_unit.py
index 35bda1c..f577eca 100644
--- a/erpnext/agriculture/doctype/land_unit/land_unit.py
+++ b/erpnext/agriculture/doctype/land_unit/land_unit.py
@@ -169,10 +169,11 @@
if is_root:
parent = ''
- land_units = frappe.get_list(doctype,
- fields = ['name as value', 'is_group as expandable'],
- filters= [['parent_land_unit', '=', parent]],
- order_by='name')
+ land_units = frappe.db.sql("""select name as value,
+ is_group as expandable
+ from `tabLand Unit`
+ where ifnull(`parent_land_unit`,'') = %s
+ order by name""", (parent), as_dict=1)
# return nodes
return land_units
diff --git a/erpnext/hr/doctype/employee/employee.py b/erpnext/hr/doctype/employee/employee.py
index 93f6532..824ddf5 100755
--- a/erpnext/hr/doctype/employee/employee.py
+++ b/erpnext/hr/doctype/employee/employee.py
@@ -318,26 +318,26 @@
@frappe.whitelist()
def get_children(doctype, parent=None, company=None, is_root=False, is_tree=False):
- filters = [['company', '=', company]]
- fields = ['name as value', 'employee_name as title']
+ condition = ''
if is_root:
- parent = ''
+ parent = ""
if parent and company and parent!=company:
- filters.append(['reports_to', '=', parent])
+ condition = ' and reports_to = "{0}"'.format(frappe.db.escape(parent))
else:
- filters.append(['reports_to', '=', ''])
+ condition = ' and ifnull(reports_to, "")=""'
- employees = frappe.get_list(doctype, fields=fields,
- filters=filters, order_by='name')
+ employee = frappe.db.sql("""
+ select
+ name as value, employee_name as title,
+ exists(select name from `tabEmployee` where reports_to=emp.name) as expandable
+ from
+ `tabEmployee` emp
+ where company='{company}' {condition} order by name"""
+ .format(company=company, condition=condition), as_dict=1)
- for employee in employees:
- is_expandable = frappe.get_all(doctype, filters=[
- ['reports_to', '=', employee.get('value')]
- ])
- employee.expandable = 1 if is_expandable else 0
-
- return employees
+ # return employee
+ return employee
def on_doctype_update():
diff --git a/erpnext/manufacturing/doctype/bom/bom.py b/erpnext/manufacturing/doctype/bom/bom.py
index 559bbdf..1fbc806 100644
--- a/erpnext/manufacturing/doctype/bom/bom.py
+++ b/erpnext/manufacturing/doctype/bom/bom.py
@@ -624,28 +624,18 @@
return
if frappe.form_dict.parent:
- bom_items = frappe.get_list('BOM Item',
- fields=['item_code', 'bom_no as value', 'stock_qty'],
- filters=[['parent', '=', frappe.form_dict.parent]],
- order_by='idx')
-
- item_names = tuple(d.get('item_code') for d in bom_items)
-
- items = frappe.get_list('Item',
- fields=['image', 'description', 'name'],
- filters=[['name', 'in', item_names]]) # to get only required item dicts
-
- for bom_item in bom_items:
- # extend bom_item dict with respective item dict
- bom_item.update(
- # returns an item dict from items list which matches with item_code
- (item for item in items if item.get('name')
- == bom_item.get('item_code')).next()
- )
- bom_item.expandable = 0 if bom_item.value in ('', None) else 1
-
- return bom_items
-
+ return frappe.db.sql("""select
+ bom_item.item_code,
+ bom_item.bom_no as value,
+ bom_item.stock_qty,
+ if(ifnull(bom_item.bom_no, "")!="", 1, 0) as expandable,
+ item.image,
+ item.description
+ from `tabBOM Item` bom_item, tabItem item
+ where bom_item.parent=%s
+ and bom_item.item_code = item.name
+ order by bom_item.idx
+ """, frappe.form_dict.parent, as_dict=True)
def get_boms_in_bottom_up_order(bom_no=None):
def _get_parent(bom_no):
diff --git a/erpnext/projects/doctype/task/task.py b/erpnext/projects/doctype/task/task.py
index c86e699..fcaa344 100644
--- a/erpnext/projects/doctype/task/task.py
+++ b/erpnext/projects/doctype/task/task.py
@@ -186,25 +186,27 @@
@frappe.whitelist()
def get_children(doctype, parent, task=None, project=None, is_root=False):
-
- filters = [['docstatus', '<', '2']]
+ conditions = ''
if task:
- filters.append(['parent_task', '=', task])
+ # via filters
+ conditions += ' and parent_task = "{0}"'.format(frappe.db.escape(task))
elif parent and not is_root:
# via expand child
- filters.append(['parent_task', '=', parent])
+ conditions += ' and parent_task = "{0}"'.format(frappe.db.escape(parent))
else:
- filters.append(['parent_task', '=', ''])
+ conditions += ' and ifnull(parent_task, "")=""'
if project:
- filters.append(['project', '=', project])
+ conditions += ' and project = "{0}"'.format(frappe.db.escape(project))
- tasks = frappe.get_list(doctype, fields=[
- 'name as value',
- 'subject as title',
- 'is_group as expandable'
- ], filters=filters, order_by='name')
+ tasks = frappe.db.sql("""select name as value,
+ subject as title,
+ is_group as expandable
+ from `tabTask`
+ where docstatus < 2
+ {conditions}
+ order by name""".format(conditions=conditions), as_dict=1)
# return tasks
return tasks
diff --git a/erpnext/stock/doctype/warehouse/warehouse.py b/erpnext/stock/doctype/warehouse/warehouse.py
index b90ee32..9c47f98 100644
--- a/erpnext/stock/doctype/warehouse/warehouse.py
+++ b/erpnext/stock/doctype/warehouse/warehouse.py
@@ -144,19 +144,17 @@
if is_root:
parent = ""
- fields = ['name as value', 'is_group as expandable']
- filters = [
- ['docstatus', '<', '2'],
- ['parent_warehouse', '=', parent],
- ['company', 'in', (company, None,'')]
- ]
-
- warehouses = frappe.get_list(doctype, fields=fields, filters=filters, order_by='name')
+ warehouses = frappe.db.sql("""select name as value,
+ is_group as expandable
+ from `tabWarehouse`
+ where docstatus < 2
+ and ifnull(`parent_warehouse`,'') = %s
+ and (`company` = %s or company is null or company = '')
+ order by name""", (parent, company), as_dict=1)
# return warehouses
for wh in warehouses:
wh["balance"] = get_stock_value_on(warehouse=wh.value)
-
return warehouses
@frappe.whitelist()