def export_query():
"""export from report builder"""
form_params = get_form_params()
form_params["limit_page_length"] = None
form_params["as_list"] = True
doctype = form_params.doctype
del form_params["doctype"]
frappe.permissions.can_export(doctype, raise_exception=True)
db_query = DatabaseQuery(doctype)
ret = db_query.execute(**form_params)
data = [['Sr'] + get_labels(db_query.fields, doctype)]
for i, row in enumerate(ret):
data.append([i+1] + list(row))
# convert to csv
from cStringIO import StringIO
import csv
f = StringIO()
writer = csv.writer(f)
for r in data:
# encode only unicode type strings and not int, floats etc.
writer.writerow(map(lambda v: isinstance(v, unicode) and v.encode('utf-8') or v, r))
f.seek(0)
frappe.response['result'] = unicode(f.read(), 'utf-8')
frappe.response['type'] = 'csv'
frappe.response['doctype'] = doctype
def export_query():
"""export from report builder"""
form_params = get_form_params()
form_params["limit_page_length"] = None
form_params["as_list"] = True
doctype = form_params.doctype
add_totals_row = None
file_format_type = form_params["file_format_type"]
del form_params["doctype"]
del form_params["file_format_type"]
if 'add_totals_row' in form_params and form_params['add_totals_row']=='1':
add_totals_row = 1
del form_params["add_totals_row"]
frappe.permissions.can_export(doctype, raise_exception=True)
if 'selected_items' in form_params:
si = json.loads(frappe.form_dict.get('selected_items'))
form_params["filters"] = {"name": ("in", si)}
del form_params["selected_items"]
db_query = DatabaseQuery(doctype)
ret = db_query.execute(**form_params)
if add_totals_row:
ret = append_totals_row(ret)
data = [['Sr'] + get_labels(db_query.fields, doctype)]
for i, row in enumerate(ret):
data.append([i+1] + list(row))
if file_format_type == "CSV":
# convert to csv
import csv
from frappe.utils.xlsxutils import handle_html
f = StringIO()
writer = csv.writer(f)
for r in data:
# encode only unicode type strings and not int, floats etc.
writer.writerow([handle_html(frappe.as_unicode(v)).encode('utf-8') \
if isinstance(v, string_types) else v for v in r])
f.seek(0)
frappe.response['result'] = text_type(f.read(), 'utf-8')
frappe.response['type'] = 'csv'
frappe.response['doctype'] = doctype
elif file_format_type == "Excel":
from frappe.utils.xlsxutils import make_xlsx
xlsx_file = make_xlsx(data, doctype)
frappe.response['filename'] = doctype + '.xlsx'
frappe.response['filecontent'] = xlsx_file.getvalue()
frappe.response['type'] = 'binary'
def export_query():
"""export from report builder"""
form_params = get_form_params()
form_params["limit_page_length"] = None
form_params["as_list"] = True
doctype = form_params.doctype
add_totals_row = None
file_format_type = form_params["file_format_type"]
del form_params["doctype"]
del form_params["file_format_type"]
if 'add_totals_row' in form_params and form_params['add_totals_row']=='1':
add_totals_row = 1
del form_params["add_totals_row"]
frappe.permissions.can_export(doctype, raise_exception=True)
if 'selected_items' in form_params:
si = json.loads(frappe.form_dict.get('selected_items'))
form_params["filters"] = {"name": ("in", si)}
del form_params["selected_items"]
db_query = DatabaseQuery(doctype)
ret = db_query.execute(**form_params)
if add_totals_row:
ret = append_totals_row(ret)
data = [['Sr'] + get_labels(db_query.fields, doctype)]
for i, row in enumerate(ret):
data.append([i+1] + list(row))
if file_format_type == "CSV":
# convert to csv
import csv
from six import StringIO
f = StringIO()
writer = csv.writer(f)
for r in data:
# encode only unicode type strings and not int, floats etc.
writer.writerow(map(lambda v: isinstance(v, text_type) and v.encode('utf-8') or v, r))
f.seek(0)
frappe.response['result'] = text_type(f.read(), 'utf-8')
frappe.response['type'] = 'csv'
frappe.response['doctype'] = doctype
elif file_format_type == "Excel":
from frappe.utils.xlsxutils import make_xlsx
xlsx_file = make_xlsx(data, doctype)
frappe.response['filename'] = doctype + '.xlsx'
frappe.response['filecontent'] = xlsx_file.getvalue()
frappe.response['type'] = 'binary'
def test_is_set_is_not_set(self):
res = DatabaseQuery('DocType').execute(filters={'autoname': ['is', 'not set']})
self.assertTrue({'name': 'Integration Request'} in res)
self.assertTrue({'name': 'User'} in res)
self.assertFalse({'name': 'Blogger'} in res)
res = DatabaseQuery('DocType').execute(filters={'autoname': ['is', 'set']})
self.assertTrue({'name': 'DocField'} in res)
self.assertTrue({'name': 'Prepared Report'} in res)
self.assertFalse({'name': 'Property Setter'} in res)
def test_is_set_is_not_set(self):
res = DatabaseQuery("DocType").execute(
filters={"autoname": ["is", "not set"]})
self.assertTrue({'name': 'Integration Request'} in res)
self.assertTrue({'name': 'User'} in res)
self.assertFalse({'name': 'Blogger'} in res)
res = DatabaseQuery("DocType").execute(
filters={"autoname": ["is", "set"]})
self.assertTrue({'name': 'DocField'} in res)
self.assertTrue({'name': 'Prepared Report'} in res)
self.assertFalse({'name': 'Property Setter'} in res)
def test_in_not_in_filters(self):
self.assertFalse(DatabaseQuery("DocType").execute(filters={"name": ["in", None]}))
self.assertTrue({"name":"DocType"} \
in DatabaseQuery("DocType").execute(filters={"name": ["not in", None]}))
for result in [{"name":"DocType"}, {"name":"DocField"}]:
self.assertTrue(result
in DatabaseQuery("DocType").execute(filters={"name": ["in", 'DocType,DocField']}))
for result in [{"name":"DocType"}, {"name":"DocField"}]:
self.assertFalse(result
in DatabaseQuery("DocType").execute(filters={"name": ["not in", 'DocType,DocField']}))
def test_is_set_is_not_set(self):
res = DatabaseQuery("DocType").execute(
filters={"autoname": ["is", "not set"]})
self.assertTrue({"name": "Integration Request"} in res)
self.assertTrue({"name": "User"} in res)
self.assertFalse({"name": "Blogger"} in res)
res = DatabaseQuery("DocType").execute(
filters={"autoname": ["is", "set"]})
self.assertTrue({"name": "DocField"} in res)
self.assertTrue({"name": "Prepared Report"} in res)
self.assertFalse({"name": "Property Setter"} in res)
def test_extract_tables(self):
db_query = DatabaseQuery("DocType")
add_custom_field("DocType", 'test_tab_field', 'Data')
db_query.fields = [
"tabNote.creation", "test_tab_field", "tabDocType.test_tab_field"
]
db_query.extract_tables()
self.assertIn("`tabNote`", db_query.tables)
self.assertIn("`tabDocType`", db_query.tables)
self.assertNotIn("test_tab_field", db_query.tables)
clear_custom_fields("DocType")
def test_todo_list_access(self):
create_new_todo("Test1", "*****@*****.**")
frappe.set_user("*****@*****.**")
create_new_todo("Test2", "*****@*****.**")
test_user_data = DatabaseQuery("ToDo").execute()
frappe.set_user("*****@*****.**")
system_manager_data = DatabaseQuery("ToDo").execute()
self.assertNotEqual(test_user_data, system_manager_data)
frappe.set_user("Administrator")
frappe.db.rollback()
def test_todo_list_access(self):
create_new_todo('Test1', '*****@*****.**')
frappe.set_user('*****@*****.**')
create_new_todo('Test2', '*****@*****.**')
test_user_data = DatabaseQuery('ToDo').execute()
frappe.set_user('*****@*****.**')
system_manager_data = DatabaseQuery('ToDo').execute()
self.assertNotEqual(test_user_data, system_manager_data)
frappe.set_user('Administrator')
frappe.db.rollback()
def get_filters_cond(doctype, filters, conditions, ignore_permissions=None, with_match_conditions=False): if isinstance(filters, basestring): filters = json.loads(filters) if filters: flt = filters if isinstance(filters, dict): filters = filters.items() flt = [] for f in filters: if isinstance(f[1], basestring) and f[1][0] == '!': flt.append([doctype, f[0], '!=', f[1][1:]]) else: flt.append([doctype, f[0], '=', f[1]]) query = DatabaseQuery(doctype) query.filters = flt query.conditions = conditions if with_match_conditions: query.build_match_conditions() query.build_filter_conditions(flt, conditions, ignore_permissions) cond = ' and ' + ' and '.join(query.conditions) else: cond = '' return cond
def get_filters_cond(doctype, filters, conditions, ignore_permissions=None, with_match_conditions=False):
if isinstance(filters, string_types):
filters = json.loads(filters)
if filters:
flt = filters
if isinstance(filters, dict):
filters = filters.items()
flt = []
for f in filters:
if isinstance(f[1], string_types) and f[1][0] == '!':
flt.append([doctype, f[0], '!=', f[1][1:]])
elif isinstance(f[1], (list, tuple)) and \
f[1][0] in (">", "<", ">=", "<=", "!=", "like", "not like", "in", "not in", "between"):
flt.append([doctype, f[0], f[1][0], f[1][1]])
else:
flt.append([doctype, f[0], '=', f[1]])
query = DatabaseQuery(doctype)
query.filters = flt
query.conditions = conditions
if with_match_conditions:
query.build_match_conditions()
query.build_filter_conditions(flt, conditions, ignore_permissions)
cond = ' and ' + ' and '.join(query.conditions)
else:
cond = ''
return cond
def test_nested_permission(self):
frappe.set_user('Administrator')
create_nested_doctype()
create_nested_doctype_records()
clear_user_permissions_for_doctype('Nested DocType')
# user permission for only one root folder
add_user_permission('Nested DocType', 'Level 1 A', '*****@*****.**')
from frappe.core.page.permission_manager.permission_manager import update
# to avoid if_owner filter
update('Nested DocType', 'All', 0, 'if_owner', 0)
frappe.set_user('*****@*****.**')
data = DatabaseQuery('Nested DocType').execute()
# children of root folder (for which we added user permission) should be accessible
self.assertTrue({'name': 'Level 2 A'} in data)
self.assertTrue({'name': 'Level 2 A'} in data)
# other folders should not be accessible
self.assertFalse({'name': 'Level 1 B'} in data)
self.assertFalse({'name': 'Level 2 B'} in data)
update('Nested DocType', 'All', 0, 'if_owner', 1)
frappe.set_user('Administrator')
def test_build_match_conditions(self):
clear_user_permissions_for_doctype('Blog Post', '*****@*****.**')
test2user = frappe.get_doc('User', '*****@*****.**')
test2user.add_roles('Blogger')
frappe.set_user('*****@*****.**')
# this will get match conditions for Blog Post
build_match_conditions = DatabaseQuery(
'Blog Post').build_match_conditions
# Before any user permission is applied
# get as filters
self.assertEqual(build_match_conditions(as_condition=False), [])
# get as conditions
self.assertEqual(build_match_conditions(as_condition=True), "")
add_user_permission('Blog Post', '-test-blog-post',
'*****@*****.**', True)
add_user_permission('Blog Post', '-test-blog-post-1',
'*****@*****.**', True)
# After applying user permission
# get as filters
self.assertTrue(
{'Blog Post': ['-test-blog-post-1', '-test-blog-post']
} in build_match_conditions(as_condition=False))
# get as conditions
self.assertEqual(
build_match_conditions(as_condition=True),
"""(((ifnull(`tabBlog Post`.`name`, '')='' or `tabBlog Post`.`name` in ('-test-blog-post-1', '-test-blog-post'))))"""
)
frappe.set_user('Administrator')
def test_nested_permission(self):
frappe.set_user("Administrator")
create_nested_doctype()
create_nested_doctype_records()
clear_user_permissions_for_doctype("Nested DocType")
# user permission for only one root folder
add_user_permission("Nested DocType", "Level 1 A", "*****@*****.**")
from frappe.core.page.permission_manager.permission_manager import update
# to avoid if_owner filter
update("Nested DocType", "All", 0, "if_owner", 0)
frappe.set_user("*****@*****.**")
data = DatabaseQuery("Nested DocType").execute()
# children of root folder (for which we added user permission) should be accessible
self.assertTrue({"name": "Level 2 A"} in data)
self.assertTrue({"name": "Level 2 A"} in data)
# other folders should not be accessible
self.assertFalse({"name": "Level 1 B"} in data)
self.assertFalse({"name": "Level 2 B"} in data)
update("Nested DocType", "All", 0, "if_owner", 1)
frappe.set_user("Administrator")
def test_nested_permission(self):
clear_user_permissions_for_doctype("File")
delete_test_file_hierarchy() # delete already existing folders
from frappe.core.doctype.file.file import create_new_folder
frappe.set_user('Administrator')
create_new_folder('level1-A', 'Home')
create_new_folder('level2-A', 'Home/level1-A')
create_new_folder('level2-B', 'Home/level1-A')
create_new_folder('level3-A', 'Home/level1-A/level2-A')
create_new_folder('level1-B', 'Home')
create_new_folder('level2-A', 'Home/level1-B')
# user permission for only one root folder
add_user_permission('File', 'Home/level1-A', '*****@*****.**')
from frappe.core.page.permission_manager.permission_manager import update
update('File', 'All', 0, 'if_owner', 0) # to avoid if_owner filter
frappe.set_user('*****@*****.**')
data = DatabaseQuery("File").execute()
# children of root folder (for which we added user permission) should be accessible
self.assertTrue({"name": "Home/level1-A/level2-A"} in data)
self.assertTrue({"name": "Home/level1-A/level2-B"} in data)
self.assertTrue({"name": "Home/level1-A/level2-A/level3-A"} in data)
# other folders should not be accessible
self.assertFalse({"name": "Home/level1-B"} in data)
self.assertFalse({"name": "Home/level1-B/level2-B"} in data)
update('File', 'All', 0, 'if_owner', 1)
frappe.set_user('Administrator')
def get_match_cond(doctype, as_condition=True):
cond = DatabaseQuery(doctype).build_match_conditions(
as_condition=as_condition)
if not as_condition:
return cond
return ((" and " + cond) if cond else "").replace("%", "%%")
def test_or_filters(self):
data = DatabaseQuery("DocField").execute(
filters={"parent": "DocType"}, fields=["fieldname", "fieldtype"],
or_filters=[{"fieldtype":"Table"}, {"fieldtype":"Select"}])
self.assertTrue({"fieldtype":"Table", "fieldname":"fields"} in data)
self.assertTrue({"fieldtype":"Select", "fieldname":"document_type"} in data)
self.assertFalse({"fieldtype":"Check", "fieldname":"issingle"} in data)
def test_fieldname_starting_with_int(self):
from frappe.core.doctype.doctype.test_doctype import new_doctype
dt = new_doctype(
"dt_with_int_named_fieldname",
fields=[{
"label": "1field",
"fieldname": "1field",
"fieldtype": "Data"
}],
).insert(ignore_permissions=True)
frappe.get_doc({
"doctype": "dt_with_int_named_fieldname",
"1field": "10"
}).insert(ignore_permissions=True)
query = DatabaseQuery("dt_with_int_named_fieldname")
self.assertTrue(query.execute(filters={"1field": "10"}))
self.assertTrue(query.execute(filters={"1field": ["like", "1%"]}))
self.assertTrue(query.execute(filters={"1field": ["in", "1,2,10"]}))
self.assertTrue(query.execute(filters={"1field": ["is", "set"]}))
self.assertFalse(query.execute(filters={"1field": ["not like", "1%"]}))
dt.delete()
def get_filters_cond(doctype, filters, conditions, ignore_permissions=None, with_match_conditions=False):
if isinstance(filters, string_types):
filters = json.loads(filters)
if filters:
flt = filters
if isinstance(filters, dict):
filters = filters.items()
flt = []
for f in filters:
if isinstance(f[1], string_types) and f[1][0] == '!':
flt.append([doctype, f[0], '!=', f[1][1:]])
elif isinstance(f[1], (list, tuple)) and \
f[1][0] in (">", "<", ">=", "<=", "like", "not like", "in", "not in", "between"):
flt.append([doctype, f[0], f[1][0], f[1][1]])
else:
flt.append([doctype, f[0], '=', f[1]])
query = DatabaseQuery(doctype)
query.filters = flt
query.conditions = conditions
if with_match_conditions:
query.build_match_conditions()
query.build_filter_conditions(flt, conditions, ignore_permissions)
cond = ' and ' + ' and '.join(query.conditions)
else:
cond = ''
return cond
def get_warehouse_filter_based_on_permissions(filters):
try:
# check if user has any restrictions based on user permissions on warehouse
if DatabaseQuery('Warehouse', user=frappe.session.user).build_match_conditions():
filters.append(['warehouse', 'in', [w.name for w in frappe.get_list('Warehouse')]])
return False, filters
except frappe.PermissionError:
# user does not have access on warehouse
return True, []
def export_query():
"""export from report builder"""
form_params = get_form_params()
form_params["limit_page_length"] = None
form_params["as_list"] = True
doctype = form_params.doctype
add_totals_row = None
del form_params["doctype"]
if 'add_totals_row' in form_params and form_params['add_totals_row'] == '1':
add_totals_row = 1
del form_params["add_totals_row"]
frappe.permissions.can_export(doctype, raise_exception=True)
db_query = DatabaseQuery(doctype)
ret = db_query.execute(**form_params)
if add_totals_row:
ret = append_totals_row(ret)
data = [['Sr'] + get_labels(db_query.fields, doctype)]
for i, row in enumerate(ret):
data.append([i + 1] + list(row))
# convert to csv
from cStringIO import StringIO
import csv
f = StringIO()
writer = csv.writer(f)
for r in data:
# encode only unicode type strings and not int, floats etc.
writer.writerow(
map(lambda v: isinstance(v, unicode) and v.encode('utf-8') or v,
r))
f.seek(0)
frappe.response['result'] = unicode(f.read(), 'utf-8')
frappe.response['type'] = 'csv'
frappe.response['doctype'] = doctype
def get_filters_cond(doctype, filters, conditions): if filters: if isinstance(filters, dict): filters = filters.items() flt = [] for f in filters: if isinstance(f[1], basestring) and f[1][0] == '!': flt.append([doctype, f[0], '!=', f[1][1:]]) else: flt.append([doctype, f[0], '=', f[1]]) query = DatabaseQuery(doctype) query.filters = flt query.conditions = conditions query.build_filter_conditions(flt, conditions) cond = ' and ' + ' and '.join(query.conditions) else: cond = '' return cond
def get_conditions(filter_list, and_or='and'):
from frappe.model.db_query import DatabaseQuery
if not filter_list:
return ''
conditions = []
DatabaseQuery('Item').build_filter_conditions(filter_list, conditions, ignore_permissions=True)
join_by = ' {0} '.format(and_or)
return '(' + join_by.join(conditions) + ')'
def get_filters_cond(doctype, filters, conditions):
if filters:
flt = filters
if isinstance(filters, dict):
filters = filters.items()
flt = []
for f in filters:
if isinstance(f[1], basestring) and f[1][0] == "!":
flt.append([doctype, f[0], "!=", f[1][1:]])
else:
flt.append([doctype, f[0], "=", f[1]])
query = DatabaseQuery(doctype)
query.filters = flt
query.conditions = conditions
query.build_filter_conditions(flt, conditions)
cond = " and " + " and ".join(query.conditions)
else:
cond = ""
return cond
def get_data(project=None, project_center=None, status=None, start=0, sort_by='creation', sort_order='desc'):
filters = list()
if project:
filters.append(['project', '=', project])
if status:
if status == "Not Completed":
filters.append(['status', '!=', "Completed"])
else:
filters.append(['status', '=', status])
if project_center:
projects = frappe.get_all("Projects", {
"parent": project_center,
"parentfield": "projects",
"parenttype": "Project Center",
}, "project", as_list=True)
filters.append(['project', 'in', [d for d, in projects]])
try:
doctype = 'Department'
user = frappe.session.user
database_query = DatabaseQuery(doctype, user=user)
if database_query.build_match_conditions():
departments = [d.name for d in frappe.get_list('Department')]
filters.append(['department', 'in', departments])
except frappe.PermissionError:
return []
items = frappe.get_list("Task", filters=filters,
fields="*", limit_start=start, limit_page_length="21")
for item in items:
item.translated_status = translate(item.status)
set_project_center(item)
return items
def get_data(item_code=None,
warehouse=None,
item_group=None,
start=0,
sort_by='actual_qty',
sort_order='desc'):
'''Return data to render the item dashboard'''
filters = []
if item_code:
filters.append(['item_code', '=', item_code])
if warehouse:
filters.append(['warehouse', '=', warehouse])
if item_group:
lft, rgt = frappe.db.get_value("Item Group", item_group,
["lft", "rgt"])
items = frappe.db.sql_list(
"""
select i.name from `tabItem` i
where exists(select name from `tabItem Group`
where name=i.item_group and lft >=%s and rgt<=%s)
""", (lft, rgt))
filters.append(['item_code', 'in', items])
try:
# check if user has any restrictions based on user permissions on warehouse
if DatabaseQuery('Warehouse',
user=frappe.session.user).build_match_conditions():
filters.append([
'warehouse', 'in',
[w.name for w in frappe.get_list('Warehouse')]
])
except frappe.PermissionError:
# user does not have access on warehouse
return []
return frappe.db.get_all('Bin',
fields=[
'item_code', 'warehouse', 'projected_qty',
'reserved_qty', 'reserved_qty_for_production',
'reserved_qty_for_sub_contract', 'actual_qty',
'valuation_rate'
],
or_filters={
'projected_qty': ['!=', 0],
'reserved_qty': ['!=', 0],
'reserved_qty_for_production': ['!=', 0],
'reserved_qty_for_sub_contract': ['!=', 0],
'actual_qty': ['!=', 0],
},
filters=filters,
order_by=sort_by + ' ' + sort_order,
limit_start=start,
limit_page_length='21')
def test_between_filters(self):
""" test case to check between filter for date fields """
frappe.db.sql("delete from tabEvent")
# create events to test the between operator filter
todays_event = create_event()
event1 = create_event(starts_on="2016-07-05 23:59:59")
event2 = create_event(starts_on="2016-07-06 00:00:00")
event3 = create_event(starts_on="2016-07-07 23:59:59")
event4 = create_event(starts_on="2016-07-08 00:00:01")
# if the values are not passed in filters then event should be filter as current datetime
data = DatabaseQuery("Event").execute(
filters={"starts_on": ["between", None]}, fields=["name"])
self.assertTrue({"name": event1.name} not in data)
# if both from and to_date values are passed
data = DatabaseQuery("Event").execute(
filters={"starts_on": ["between", ["2016-07-06", "2016-07-07"]]},
fields=["name"])
self.assertTrue({"name": event2.name} in data)
self.assertTrue({"name": event3.name} in data)
self.assertTrue({"name": event1.name} not in data)
self.assertTrue({"name": event4.name} not in data)
# if only one value is passed in the filter
data = DatabaseQuery("Event").execute(
filters={"starts_on": ["between", ["2016-07-07"]]},
fields=["name"])
self.assertTrue({"name": event3.name} in data)
self.assertTrue({"name": event4.name} in data)
self.assertTrue({"name": todays_event.name} in data)
self.assertTrue({"name": event1.name} not in data)
self.assertTrue({"name": event2.name} not in data)
def execute(doctype,
query=None,
filters=None,
fields=None,
docstatus=None,
group_by=None,
order_by=None,
limit_start=0,
limit_page_length=20,
as_list=False,
with_childnames=False,
debug=False):
return DatabaseQuery(doctype).execute(query, filters, fields, docstatus,
group_by, order_by, limit_start,
limit_page_length, as_list,
with_childnames, debug)
def test_filter_sanitizer(self):
self.assertRaises(frappe.DataError, DatabaseQuery("DocType").execute,
fields=["name"], filters={'istable,': 1}, limit_start=0, limit_page_length=1)
self.assertRaises(frappe.DataError, DatabaseQuery("DocType").execute,
fields=["name"], filters={'editable_grid,': 1}, or_filters={'istable,': 1},
limit_start=0, limit_page_length=1)
self.assertRaises(frappe.DataError, DatabaseQuery("DocType").execute,
fields=["name"], filters={'editable_grid,': 1},
or_filters=[['DocType', 'istable,', '=', 1]],
limit_start=0, limit_page_length=1)
self.assertRaises(frappe.DataError, DatabaseQuery("DocType").execute,
fields=["name"], filters={'editable_grid,': 1},
or_filters=[['DocType', 'istable', '=', 1], ['DocType', 'beta and 1=1', '=', 0]],
limit_start=0, limit_page_length=1)
out = DatabaseQuery("DocType").execute(fields=["name"],
filters={'editable_grid': 1, 'module': 'Core'},
or_filters=[['DocType', 'istable', '=', 1]], order_by='creation')
self.assertTrue('DocField' in [d['name'] for d in out])
out = DatabaseQuery("DocType").execute(fields=["name"],
filters={'issingle': 1}, or_filters=[['DocType', 'module', '=', 'Core']],
order_by='creation')
self.assertTrue('User Permission for Page and Report' in [d['name'] for d in out])
out = DatabaseQuery("DocType").execute(fields=["name"],
filters={'track_changes': 1, 'module': 'Core'},
order_by='creation')
self.assertTrue('File' in [d['name'] for d in out])
out = DatabaseQuery("DocType").execute(fields=["name"],
filters=[
['DocType', 'ifnull(track_changes, 0)', '=', 0],
['DocType', 'module', '=', 'Core']
], order_by='creation')
self.assertTrue('DefaultValue' in [d['name'] for d in out])
def test_permission_query_condition(self):
from frappe.desk.doctype.dashboard_settings.dashboard_settings import create_dashboard_settings
self.doctype = "Dashboard Settings"
self.user = "******"
permission_query_conditions = DatabaseQuery.get_permission_query_conditions(
self)
create_dashboard_settings(self.user)
dashboard_settings = frappe.db.sql(
"""
SELECT name
FROM `tabDashboard Settings`
WHERE {condition}
""".format(condition=permission_query_conditions),
as_dict=1,
)[0]
self.assertTrue(dashboard_settings)
def get_filters_cond(doctype, filters, conditions):
if filters:
if isinstance(filters, dict):
filters = filters.items()
flt = []
for f in filters:
if isinstance(f[1], basestring) and f[1][0] == '!':
flt.append([doctype, f[0], '!=', f[1][1:]])
else:
flt.append([doctype, f[0], '=', f[1]])
query = DatabaseQuery(doctype)
query.filters = flt
query.conditions = conditions
query.build_filter_conditions(flt, conditions)
cond = ' and ' + ' and '.join(query.conditions)
else:
cond = ''
return cond
def get_filters_cond(doctype, filters, conditions, ignore_permissions=None):
if filters:
flt = filters
if isinstance(filters, dict):
filters = filters.items()
flt = []
for f in filters:
if isinstance(f[1], basestring) and f[1][0] == '!':
flt.append([doctype, f[0], '!=', f[1][1:]])
else:
value = frappe.db.escape(f[1]) if isinstance(
f[1], basestring) else f[1]
flt.append([doctype, f[0], '=', value])
query = DatabaseQuery(doctype)
query.filters = flt
query.conditions = conditions
query.build_filter_conditions(flt, conditions, ignore_permissions)
cond = ' and ' + ' and '.join(query.conditions)
else:
cond = ''
return cond
def get_match_cond(doctype):
cond = DatabaseQuery(doctype).build_match_conditions()
return ((' and ' + cond) if cond else "").replace("%", "%%")
def execute(doctype, *args, **kwargs):
return DatabaseQuery(doctype).execute(*args, **kwargs)
def build_match_conditions(doctype, as_condition=True):
match_conditions = DatabaseQuery(doctype).build_match_conditions(as_condition=as_condition)
if as_condition:
return match_conditions.replace("%", "%%")
else:
return match_conditions