def get_events(start, end): events = [] employee = frappe.db.get_value("Employee", {"user_id": frappe.session.user}, ["name", "company"], as_dict=True) if not employee: return events employee, company = employee.name, employee.company from frappe.widgets.reportview import build_match_conditions match_conditions = build_match_conditions("Leave Application") # show department leaves for employee if "Employee" in frappe.get_roles(): add_department_leaves(events, start, end, employee, company) add_leaves(events, start, end, match_conditions) add_block_dates(events, start, end, employee, company) add_holidays(events, start, end, employee, company) return events
def get_events(start, end, filters=None): from frappe.widgets.reportview import build_match_conditions if not frappe.has_permission("Task"): frappe.msgprint(_("No Permission"), raise_exception=1) conditions = build_match_conditions("Task") conditions and (" and " + conditions) or "" if filters: filters = json.loads(filters) for key in filters: if filters[key]: conditions += " and " + key + ' = "' + filters[key].replace('"', '\"') + '"' data = frappe.db.sql("""select name, exp_start_date, exp_end_date, subject, status, project from `tabTask` where ((exp_start_date between '%(start)s' and '%(end)s') \ or (exp_end_date between '%(start)s' and '%(end)s')) %(conditions)s""" % { "start": start, "end": end, "conditions": conditions }, as_dict=True, update={"allDay": 0}) return data
def get_events(start, end, filters=None): from frappe.widgets.reportview import build_match_conditions if not frappe.has_permission("Task"): frappe.msgprint(_("No Permission"), raise_exception=1) conditions = build_match_conditions("Task") conditions and (" and " + conditions) or "" if filters: filters = json.loads(filters) for key in filters: if filters[key]: conditions += " and " + key + ' = "' + filters[key].replace( '"', '\"') + '"' data = frappe.db.sql("""select name, exp_start_date, exp_end_date, subject, status, project from `tabTask` where ((exp_start_date between '%(start)s' and '%(end)s') \ or (exp_end_date between '%(start)s' and '%(end)s')) %(conditions)s""" % { "start": start, "end": end, "conditions": conditions }, as_dict=True, update={"allDay": 0}) return data
def get_events(start, end): from frappe.widgets.reportview import build_match_conditions if not frappe.has_permission("Time Log"): frappe.msgprint(_("No Permission"), raise_exception=1) match = build_match_conditions("Time Log") data = frappe.db.sql("""select name, from_time, to_time, activity_type, task, project from `tabTime Log` where from_time between '%(start)s' and '%(end)s' or to_time between '%(start)s' and '%(end)s' %(match)s""" % { "start": start, "end": end, "match": match and (" and " + match) or "" }, as_dict=True, update={"allDay": 0}) for d in data: d.title = d.name + ": " + (d.activity_type or "[Activity Type not set]") if d.task: d.title += " for Task: " + d.task if d.project: d.title += " for Project: " + d.project return data
def query_for_permitted_employees(doctype, txt, searchfield, start, page_len, filters): txt = "%" + cstr(txt) + "%" if "Leave Approver" in frappe.user.get_roles(): user = frappe.session.user.replace('"', '\"') condition = """and (exists(select ela.name from `tabEmployee Leave Approver` ela where ela.parent=`tabEmployee`.name and ela.leave_approver= "%s") or not exists(select ela.name from `tabEmployee Leave Approver` ela where ela.parent=`tabEmployee`.name) or user_id = "%s")""" % (user, user) else: from frappe.widgets.reportview import build_match_conditions condition = build_match_conditions("Employee") condition = ("and " + condition) if condition else "" return frappe.db.sql( """select name, employee_name from `tabEmployee` where status = 'Active' and docstatus < 2 and (`%s` like %s or employee_name like %s) %s order by case when name like %s then 0 else 1 end, case when employee_name like %s then 0 else 1 end, name limit %s, %s""" % tuple([searchfield] + ["%s"] * 2 + [condition] + ["%s"] * 4), (txt, txt, txt, txt, start, page_len))
def build_conditions(filters): conditions = "" if filters.get("from_date"): conditions += " and from_time >= timestamp(%(from_date)s, %(from_time)s)" if filters.get("to_date"): conditions += " and to_time <= timestamp(%(to_date)s, %(to_time)s)" from frappe.widgets.reportview import build_match_conditions match_conditions = build_match_conditions("Time Log") if match_conditions: conditions += " and %s" % match_conditions return conditions
def get_events(start, end): events = [] employee = frappe.db.get_default("employee", frappe.session.user) company = frappe.db.get_default("company", frappe.session.user) from frappe.widgets.reportview import build_match_conditions match_conditions = build_match_conditions("Leave Application") # show department leaves for employee if "Employee" in frappe.get_roles(): add_department_leaves(events, start, end, employee, company) add_leaves(events, start, end, employee, company, match_conditions) add_block_dates(events, start, end, employee, company) add_holidays(events, start, end, employee, company) return events
def get_conditions(filters): conditions = [] if filters.get("account"): lft, rgt = frappe.db.get_value("Account", filters["account"], ["lft", "rgt"]) conditions.append("""account in (select name from tabAccount where lft>=%s and rgt<=%s and docstatus<2)""" % (lft, rgt)) else: conditions.append("posting_date between %(from_date)s and %(to_date)s") if filters.get("voucher_no"): conditions.append("voucher_no=%(voucher_no)s") from frappe.widgets.reportview import build_match_conditions match_conditions = build_match_conditions("GL Entry") if match_conditions: conditions.append(match_conditions) return "and {}".format(" and ".join(conditions)) if conditions else ""
def query_task(doctype, txt, searchfield, start, page_len, filters): from frappe.widgets.reportview import build_match_conditions search_string = "%%%s%%" % txt order_by_string = "%s%%" % txt match_conditions = build_match_conditions("Task") match_conditions = ("and" + match_conditions) if match_conditions else "" return frappe.db.sql("""select name, subject from `tabTask` where (`%s` like %s or `subject` like %s) %s order by case when `subject` like %s then 0 else 1 end, case when `%s` like %s then 0 else 1 end, `%s`, subject limit %s, %s""" % (searchfield, "%s", "%s", match_conditions, "%s", searchfield, "%s", searchfield, "%s", "%s"), (search_string, search_string, order_by_string, order_by_string, start, page_len))
def query_task(doctype, txt, searchfield, start, page_len, filters): from frappe.widgets.reportview import build_match_conditions search_string = "%%%s%%" % txt order_by_string = "%s%%" % txt match_conditions = build_match_conditions("Task") match_conditions = ("and" + match_conditions) if match_conditions else "" return frappe.db.sql( """select name, subject from `tabTask` where (`%s` like %s or `subject` like %s) %s order by case when `subject` like %s then 0 else 1 end, case when `%s` like %s then 0 else 1 end, `%s`, subject limit %s, %s""" % (searchfield, "%s", "%s", match_conditions, "%s", searchfield, "%s", searchfield, "%s", "%s"), (search_string, search_string, order_by_string, order_by_string, start, page_len))
def add_match_conditions(q, tl): from frappe.widgets.reportview import build_match_conditions sl = [] for dt in tl: s = build_match_conditions(dt) if s: sl.append(s) # insert the conditions if sl: condition_st = q.find('WHERE')!=-1 and ' AND ' or ' WHERE ' condition_end = q.find('ORDER BY')!=-1 and 'ORDER BY' or 'LIMIT' condition_end = q.find('GROUP BY')!=-1 and 'GROUP BY' or condition_end if q.find('ORDER BY')!=-1 or q.find('LIMIT')!=-1 or q.find('GROUP BY')!=-1: # if query continues beyond conditions q = q.split(condition_end) q = q[0] + condition_st + '(' + ' OR '.join(sl) + ') ' + condition_end + q[1] else: q = q + condition_st + '(' + ' OR '.join(sl) + ')' return q
def add_match_conditions(q, tl): from frappe.widgets.reportview import build_match_conditions sl = [] for dt in tl: s = build_match_conditions(dt) if s: sl.append(s) # insert the conditions if sl: condition_st = q.find('WHERE') != -1 and ' AND ' or ' WHERE ' condition_end = q.find('ORDER BY') != -1 and 'ORDER BY' or 'LIMIT' condition_end = q.find( 'GROUP BY') != -1 and 'GROUP BY' or condition_end if q.find('ORDER BY') != -1 or q.find('LIMIT') != -1 or q.find( 'GROUP BY') != -1: # if query continues beyond conditions q = q.split(condition_end) q = q[0] + condition_st + '(' + ' OR '.join( sl) + ') ' + condition_end + q[1] else: q = q + condition_st + '(' + ' OR '.join(sl) + ')' return q