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()