Esempio n. 1
0
def user_query(doctype, txt, searchfield, start, page_len, filters):
	from helpdesk.py.todo import get_highest_role, get_role_priority

	highest_role = get_highest_role(frappe.session.user)
	query = ""
	roles = []
	dept = ""
	if highest_role == "Administrator":
		roles = ["Department Head"]
		if isinstance(filters.get("issue"), list):
			dept = validate_multiple_issue_name(filters.get("issue"))
		else:
			dept = frappe.db.get_value("Issue",filters.get("issue"),"department")
		dept = "AND usr.department='{dept}'".format(dept=dept)
	else:
		priority = get_role_priority(highest_role).get("priority")
		roles = frappe.db.sql("select role from `tabRole Priority` where priority < %s"%(priority), as_list=True)
		roles = [role[0] for role in roles]

	query = """	SELECT DISTINCT
				    usr.name,
				    concat_ws(' ', usr.first_name, usr.middle_name, usr.last_name),
				    department
				FROM
				    `tabUser` AS usr
				JOIN
				    `tabUserRole` AS r
				JOIN
				    `tabRole Priority` AS rp
				ON
				    r.role=rp.role
				AND rp.role IN ({roles})
				AND usr.name=r.parent
				AND ifnull(enabled, 0)=1
				AND usr.docstatus < 2
				AND usr.name NOT IN ({standard_users})
				AND usr.user_type != 'Website User' {dept}""".format(
					roles=",".join(["'%s'"%(role) for role in roles]),
					standard_users=", ".join(["'%s'"%(role) for role in STANDARD_USERS]),
					dept=dept)
	return frappe.db.sql(query)
Esempio n. 2
0
def user_query(doctype, txt, searchfield, start, page_len, filters):
	from helpdesk.py.todo import get_highest_role, get_role_priority

	highest_role = get_highest_role(frappe.session.user)
	query = ""
	roles_in = []
	roles_not_in = ["Guest"]
	dept = ""
	if highest_role == "Administrator":
		roles_in = ["Department Head"]
		if isinstance(filters.get("issue"), list):
			dept = validate_multiple_issue_name(filters.get("issue"))
		else:
			dept = frappe.db.get_value("Issue",filters.get("issue"),"department")
		dept = "AND usr.department='{dept}'".format(dept=dept) if dept else ""
	else:
		priority = get_role_priority(highest_role).get("priority")
		query = "select role, priority from `tabRole Priority`"
		roles = frappe.db.sql(query, as_dict=True)
		[roles_in.append(result.get("role")) if result.get("priority") < priority else roles_not_in.append(result.get("role")) for result in roles]

	txt = "%{}%".format(txt)
	if not roles_in: roles_in.append("")

	query = """	SELECT DISTINCT
				    usr.name,
				    concat_ws(' ', usr.first_name, usr.middle_name, usr.last_name),
				    usr.department
				FROM
				    `tabUser` AS usr
				JOIN
				    `tabUserRole` AS r
				ON
				    usr.name=r.parent
				JOIN
				    `tabRole Priority` AS rp
				ON
				    r.role=rp.role
				AND rp.role IN ({roles_in})
				AND usr.name NOT IN
				                     (
				                     SELECT DISTINCT
				                         parent
				                     FROM
				                         tabUserRole
				                     WHERE
				                         role IN ({roles_not_in}))
				AND ifnull(enabled, 0)=1
				AND usr.docstatus < 2
				AND (
				        usr.{key} LIKE %s
				    AND usr.user_type != 'Website User'
				    OR  concat_ws(' ', first_name, middle_name, last_name) LIKE %s
				    ) 
				{dept} ORDER BY usr.department ASC limit %s, %s""".format(
					roles_in=",".join(["'%s'"%(role) for role in roles_in]),
					roles_not_in=",".join(["'%s'"%(role) for role in roles_not_in]),
					standard_users=", ".join(["'%s'"%(role) for role in STANDARD_USERS]),
					dept=dept,
					key=searchfield,
				)
	return frappe.db.sql(query, tuple([txt, txt, start, page_len]))
Esempio n. 3
0
def user_query(doctype, txt, searchfield, start, page_len, filters):
    from helpdesk.py.todo import get_highest_role, get_role_priority

    highest_role = get_highest_role(frappe.session.user)
    query = ""
    roles_in = []
    roles_not_in = ["Guest"]
    dept = ""
    if highest_role == "Administrator":
        roles_in = ["Department Head"]
        if isinstance(filters.get("issue"), list):
            dept = validate_multiple_issue_name(filters.get("issue"))
        else:
            dept = frappe.db.get_value("Issue", filters.get("issue"),
                                       "department")
        dept = "AND usr.department='{dept}'".format(dept=dept) if dept else ""
    else:
        priority = get_role_priority(highest_role).get("priority")
        query = "select role, priority from `tabRole Priority`"
        roles = frappe.db.sql(query, as_dict=True)
        [
            roles_in.append(result.get("role"))
            if result.get("priority") < priority else roles_not_in.append(
                result.get("role")) for result in roles
        ]

    txt = "%{}%".format(txt)
    if not roles_in: roles_in.append("")

    query = """	SELECT DISTINCT
				    usr.name,
				    concat_ws(' ', usr.first_name, usr.middle_name, usr.last_name),
				    usr.department
				FROM
				    `tabUser` AS usr
				JOIN
				    `tabUserRole` AS r
				ON
				    usr.name=r.parent
				JOIN
				    `tabRole Priority` AS rp
				ON
				    r.role=rp.role
				AND rp.role IN ({roles_in})
				AND usr.name NOT IN
				                     (
				                     SELECT DISTINCT
				                         parent
				                     FROM
				                         tabUserRole
				                     WHERE
				                         role IN ({roles_not_in}))
				AND ifnull(enabled, 0)=1
				AND usr.docstatus < 2
				AND (
				        usr.{key} LIKE %s
				    AND usr.user_type != 'Website User'
				    OR  concat_ws(' ', first_name, middle_name, last_name) LIKE %s
				    ) 
				{dept} ORDER BY usr.department ASC limit %s, %s""".format(
        roles_in=",".join(["'%s'" % (role) for role in roles_in]),
        roles_not_in=",".join(["'%s'" % (role) for role in roles_not_in]),
        standard_users=", ".join(["'%s'" % (role) for role in STANDARD_USERS]),
        dept=dept,
        key=searchfield,
    )
    return frappe.db.sql(query, tuple([txt, txt, start, page_len]))