def get_practitioner_list(doctype, txt, searchfield, start, page_len, filters=None): fields = ["name", "first_name", "mobile_phone"] match_conditions = build_match_conditions("Healthcare Practitioner") match_conditions = "and {}".format(match_conditions) if match_conditions else "" if filters: filter_conditions = get_filters_cond(doctype, filters, []) match_conditions += "{}".format(filter_conditions) return frappe.db.sql("""select %s from `tabHealthcare Practitioner` where docstatus < 2 and (%s like %s or first_name like %s) and active = 1 {match_conditions} order by case when name like %s then 0 else 1 end, case when first_name like %s then 0 else 1 end, name, first_name limit %s, %s""".format( match_conditions=match_conditions) % ( ", ".join(fields), frappe.db.escape(searchfield), "%s", "%s", "%s", "%s", "%s", "%s" ), ( "%%%s%%" % frappe.db.escape(txt), "%%%s%%" % frappe.db.escape(txt), "%%%s%%" % frappe.db.escape(txt), "%%%s%%" % frappe.db.escape(txt), start, page_len ) )
def get_events(start, end, filters=None): from frappe.desk.reportview import build_match_conditions if not frappe.has_permission("Production Order"): frappe.msgprint(_("No Permission"), raise_exception=1) conditions = build_match_conditions("Production Order") conditions = 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, production_item, planned_start_date, planned_end_date from `tabProduction Order` where ((ifnull(planned_start_date, '0000-00-00')!= '0000-00-00') \ and (planned_start_date between %(start)s and %(end)s) \ or ((ifnull(planned_start_date, '0000-00-00')!= '0000-00-00') \ and planned_end_date between %(start)s and %(end)s)) {conditions} """.format(conditions=conditions), { "start": start, "end": end }, as_dict=True, update={"allDay": 0}) return data
def add_leaves(events, start, end, filter_conditions=None): from frappe.desk.reportview import build_match_conditions conditions = [] if not cint( frappe.db.get_value( "HR Settings", None, "show_leaves_of_all_department_members_in_calendar")): match_conditions = build_match_conditions("Leave Application") if match_conditions: conditions.append(match_conditions) query = """SELECT docstatus, name, employee, employee_name, leave_type, from_date, to_date, half_day, status, color FROM `tabLeave Application` WHERE from_date <= %(end)s AND to_date >= %(start)s <= to_date AND docstatus < 2 AND status != 'Rejected' """ if conditions: query += " AND " + " AND ".join(conditions) if filter_conditions: query += filter_conditions for d in frappe.db.sql(query, {"start": start, "end": end}, as_dict=True): e = { "name": d.name, "doctype": "Leave Application", "from_date": d.from_date, "to_date": d.to_date, "docstatus": d.docstatus, "color": d.color, "all_day": int(not d.half_day), "title": cstr(d.employee_name) + f" ({cstr(d.leave_type)})" + (" " + _("(Half Day)") if d.half_day else ""), } if e not in events: events.append(e)
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)) if filters.get("voucher_no"): conditions.append("voucher_no=%(voucher_no)s") if filters.get("num_jour"): conditions.append("(select code_jour from `tabSeries` where name = substring(voucher_no, 1, length(name)) limit 1) = %(num_jour)s") if filters.get("party_type"): conditions.append("party_type=%(party_type)s") if filters.get("party"): conditions.append("party=%(party)s") if not (filters.get("account") or filters.get("party") or filters.get("group_by_account")): conditions.append("posting_date >=%(from_date)s") conditions.append("posting_date <=%(to_date)s") if filters.get("project"): conditions.append("project=%(project)s") from frappe.desk.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 get_events(start, end, filters=None): """Returns events for Gantt / Calendar view rendering. :param start: Start date-time. :param end: End date-time. :param filters: Filters like workstation, project etc. """ from frappe.desk.reportview import build_match_conditions if not frappe.has_permission("Time Log"): frappe.msgprint(_("No Permission"), raise_exception=1) conditions = build_match_conditions("Time Log") conditions = 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, from_time, to_time, activity_type, task, project, production_order, workstation from `tabTime Log` where docstatus < 2 and ( from_time between %(start)s and %(end)s or to_time between %(start)s and %(end)s ) {conditions}""".format(conditions=conditions), { "start": start, "end": end }, as_dict=True, update={"allDay": 0}) for d in data: d.title = d.name + ": " + (d.activity_type or d.production_order or "") if d.task: d.title += " for Task: " + d.task if d.project: d.title += " for Project: " + d.project return data
def get_contact_list(txt, page_length=20): """Returns contacts (from autosuggest)""" cached_contacts = get_cached_contacts(txt) if cached_contacts: return cached_contacts[:page_length] try: match_conditions = build_match_conditions('Contact') match_conditions = "and {0}".format(match_conditions) if match_conditions else "" out = frappe.db.sql("""select email_id as value, concat(first_name, ifnull(concat(' ',last_name), '' )) as description from tabContact where name like %(txt)s %(condition)s limit %(page_length)s """, {'txt': "%%%s%%" % frappe.db.escape(txt), 'condition': match_conditions, 'page_length': page_length}, as_dict=True) out = filter(None, out) except: raise update_contact_cache(out) return out
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 if employee: employee, company = employee.name, employee.company else: employee='' company=frappe.db.get_value("Global Defaults", None, "default_company") from frappe.desk.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_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)) if filters.get("cost_center"): filters.cost_center = get_cost_centers_with_children(filters.cost_center) conditions.append("cost_center in %(cost_center)s") if filters.get("voucher_no"): conditions.append("voucher_no=%(voucher_no)s") if filters.get("group_by") == "Group by Party" and not filters.get("party_type"): conditions.append("party_type in ('Customer', 'Supplier')") if filters.get("party_type"): conditions.append("party_type=%(party_type)s") if filters.get("party"): conditions.append("party in %(party)s") if not (filters.get("account") or filters.get("party") or filters.get("group_by") in ["Group by Account", "Group by Party"]): conditions.append("posting_date >=%(from_date)s") conditions.append("(posting_date <=%(to_date)s or is_opening = 'Yes')") if filters.get("project"): conditions.append("project in %(project)s") if filters.get("finance_book"): if filters.get("include_default_book_entries"): conditions.append("(finance_book in (%(finance_book)s, %(company_fb)s, '') OR finance_book IS NULL)") else: conditions.append("finance_book in (%(finance_book)s)") if not filters.get("show_cancelled_entries"): conditions.append("is_cancelled = 0") from frappe.desk.reportview import build_match_conditions match_conditions = build_match_conditions("GL Entry") if match_conditions: conditions.append(match_conditions) accounting_dimensions = get_accounting_dimensions(as_list=False) if accounting_dimensions: for dimension in accounting_dimensions: if filters.get(dimension.fieldname): if frappe.get_cached_value('DocType', dimension.document_type, 'is_tree'): filters[dimension.fieldname] = get_dimension_with_children(dimension.document_type, filters.get(dimension.fieldname)) conditions.append("{0} in %({0})s".format(dimension.fieldname)) else: conditions.append("{0} in (%({0})s)".format(dimension.fieldname)) return "and {}".format(" and ".join(conditions)) if conditions else ""
def get_conditions(filters): conditions = [] if filters.get("account"): lft, rgt = frappe.db.get_value("Account", filters["account"], ["lft", "rgt"]) conditions.append("""gl.account in (select name from tabAccount where lft>=%s and rgt<=%s and docstatus<2)""" % (lft, rgt)) if filters.get("voucher_no"): conditions.append("gl.voucher_no=%(voucher_no)s") if filters.get("party_type"): conditions.append("gl.party_type=%(party_type)s") if filters.get("party"): conditions.append("gl.party=%(party)s") if not (filters.get("account") or filters.get("party") or filters.get("group_by_account")): conditions.append("gl.posting_date >=%(from_date)s") if filters.get("project"): conditions.append("gl.project=%(project)s") from frappe.desk.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 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)) if filters.get("voucher_no"): conditions.append("voucher_no=%(voucher_no)s") if filters.get("party_type"): conditions.append("party_type=%(party_type)s") if filters.get("party"): conditions.append("party=%(party)s") if not (filters.get("account") or filters.get("party") or filters.get("group_by_account")): conditions.append("posting_date >=%(from_date)s") if filters.get("project"): conditions.append("project=%(project)s") from frappe.desk.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 get_customer_list(doctype, txt, searchfield, start, page_len, filters=None): if frappe.db.get_default("cust_master_name") == "Customer Name": fields = ["name", "customer_group", "territory"] else: fields = ["name", "customer_name", "customer_group", "territory"] match_conditions = build_match_conditions("Customer") match_conditions = "and {}".format( match_conditions) if match_conditions else "" if filters: filter_conditions = get_filters_cond(doctype, filters, []) match_conditions += "{}".format(filter_conditions) return frappe.db.sql( """select %s from `tabCustomer` where docstatus < 2 and (%s like %s or customer_name like %s) {match_conditions} order by case when name like %s then 0 else 1 end, case when customer_name like %s then 0 else 1 end, name, customer_name limit %s, %s""".format(match_conditions=match_conditions) % (", ".join(fields), searchfield, "%s", "%s", "%s", "%s", "%s", "%s"), ("%%%s%%" % txt, "%%%s%%" % txt, "%%%s%%" % txt, "%%%s%%" % txt, start, page_len))
def get_events(start, end, filters=None): from frappe.desk.reportview import build_match_conditions if not frappe.has_permission("Task"): frappe.msgprint(_("No Permission"), raise_exception=1) conditions = build_match_conditions("Task") conditions = 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 ((ifnull(exp_start_date, '0000-00-00')!= '0000-00-00') \ and (exp_start_date between %(start)s and %(end)s) \ or ((ifnull(exp_start_date, '0000-00-00')!= '0000-00-00') \ and exp_end_date between %(start)s and %(end)s)) {conditions}""".format(conditions=conditions), { "start": start, "end": end }, as_dict=True, update={"allDay": 0}) return data
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.desk.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_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)) if filters.get( "group_by") == "Group by Party" and not filters.get("party_type"): conditions.append("party_type in ('Customer', 'Supplier')") conditions.append("party_type='Customer'") if filters.get("party"): conditions.append("party in %(party)s") if not (filters.get("party") or filters.get("group_by") in ["Group by Account", "Group by Party"]): conditions.append("posting_date >=%(from_date)s") conditions.append("(posting_date <=%(to_date)s or is_opening = 'Yes')") from frappe.desk.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 get_contact_list(txt, page_length=20): """Returns contacts (from autosuggest)""" cached_contacts = get_cached_contacts(txt) if cached_contacts: return cached_contacts[:page_length] try: match_conditions = build_match_conditions('Contact') match_conditions = "and {0}".format( match_conditions) if match_conditions else "" out = frappe.db.sql("""select email_id as value, concat(first_name, ifnull(concat(' ',last_name), '' )) as description from tabContact where name like %(txt)s or email_id like %(txt)s %(condition)s limit %(page_length)s""", { 'txt': '%' + txt + '%', 'condition': match_conditions, 'page_length': page_length }, as_dict=True) out = filter(None, out) except: raise update_contact_cache(out) return out
def get_practitioner_list(doctype, txt, searchfield, start, page_len, filters=None): fields = ["name", "first_name", "mobile_phone"] match_conditions = build_match_conditions("Healthcare Practitioner") match_conditions = "and {}".format( match_conditions) if match_conditions else "" if filters: filter_conditions = get_filters_cond(doctype, filters, []) match_conditions += "{}".format(filter_conditions) return frappe.db.sql( f"""select %s from `tabHealthcare Practitioner` where docstatus < 2 and (%s like %s or first_name like %s) and active = 1 {match_conditions} order by case when name like %s then 0 else 1 end, case when first_name like %s then 0 else 1 end, name, first_name limit %s, %s""" % (", ".join(fields), frappe.db.escape(searchfield), "%s", "%s", "%s", "%s", "%s", "%s"), ("%%%s%%" % frappe.db.escape(txt), "%%%s%%" % frappe.db.escape(txt), "%%%s%%" % frappe.db.escape(txt), "%%%s%%" % frappe.db.escape(txt), start, page_len))
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)) if filters.get("cost_center"): filters.cost_center = get_cost_centers_with_children( filters.cost_center) conditions.append("cost_center in %(cost_center)s") if filters.get("voucher_no"): conditions.append("voucher_no=%(voucher_no)s") if filters.get( "group_by") == "Group by Party" and not filters.get("party_type"): conditions.append("party_type in ('Customer', 'Supplier')") if filters.get("party_type"): conditions.append("party_type=%(party_type)s") if filters.get("customer_group"): conditions.append( "party in (select name from `tabCustomer` where customer_group ='%s')" % filters.get("customer_group")) if filters.get("party"): conditions.append("party in %(party)s") if not (filters.get("account") or filters.get("party") or filters.get("group_by") in ["Group by Account", "Group by Party"]): conditions.append("posting_date >=%(from_date)s") conditions.append("posting_date <=%(to_date)s") if filters.get("project"): conditions.append("project in %(project)s") company_finance_book = erpnext.get_default_finance_book( filters.get("company")) if not filters.get("finance_book") or (filters.get("finance_book") == company_finance_book): filters['finance_book'] = company_finance_book conditions.append("ifnull(finance_book, '') in (%(finance_book)s, '')") elif filters.get("finance_book"): conditions.append("ifnull(finance_book, '') = %(finance_book)s") from frappe.desk.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 get_conditions(filters): conditions = "`tabTimesheet`.docstatus = 1" if filters.get("from_date"): conditions += " and `tabTimesheet Detail`.from_time >= timestamp(%(from_date)s, %(from_time)s)" if filters.get("to_date"): conditions += " and `tabTimesheet Detail`.to_time <= timestamp(%(to_date)s, %(to_time)s)" match_conditions = build_match_conditions("Timesheet") if match_conditions: conditions += " and %s" % match_conditions return conditions
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.desk.reportview import build_match_conditions match_conditions = build_match_conditions("Time Log") if match_conditions: conditions += " and %s" % match_conditions return conditions
def get_plant_list(doctype, txt, searchfield, start, page_len, filters): fields = ["name", "strain"] match_conditions = build_match_conditions("Plant") match_conditions = "and {}".format(match_conditions) if match_conditions else "" return frappe.db.sql("""select %s from `tabPlant` where docstatus < 2 and (%s like %s or strain like %s) {match_conditions} order by case when name like %s then 0 else 1 end, case when strain like %s then 0 else 1 end, name, strain limit %s, %s""".format(match_conditions=match_conditions) % (", ".join(fields), searchfield, "%s", "%s", "%s", "%s", "%s", "%s"), ("%%%s%%" % txt, "%%%s%%" % txt, "%%%s%%" % txt, "%%%s%%" % txt, start, page_len))
def get_event_conditions(doctype, filters=None): """Returns SQL conditions with user permissions and filters for event queries""" from frappe.desk.reportview import build_match_conditions if not frappe.has_permission(doctype): frappe.throw(_("Not Permitted"), frappe.PermissionError) conditions = build_match_conditions(doctype) conditions = conditions and (" and " + conditions) or "" if filters: filters = json.loads(filters) for key in filters: if filters[key]: conditions += 'and `{0}` = "{1}"'.format(frappe.db.escape(key), frappe.db.escape(filters[key])) return conditions
def get_conditions(filters): conditions = [] if filters.get("party_type"): conditions.append("party_type=%(party_type)s") else: conditions.append("party_type in ('Customer', 'Supplier')") if filters.get("party"): conditions.append("party=%(party)s") from frappe.desk.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 get_event_conditions(doctype, filters=None): """Returns SQL conditions with user permissions and filters for event queries""" from frappe.desk.reportview import build_match_conditions if not frappe.has_permission(doctype): frappe.throw(_("Not Permitted"), frappe.PermissionError) conditions = build_match_conditions(doctype) conditions = conditions and (" and " + conditions) or "" if filters: filters = json.loads(filters) for key in filters: if filters[key]: conditions += " and " + key + ' = "' + filters[key].replace('"', '\"') + '"' return conditions
def get_conditions(filters): # conditions = "`tabAssign and contribution contract`.status = 'Active'" conditions = "`tabVehicle Details`.vehicle_status = ('%s')" % filters.get("status") if filters.get("start_date"): conditions += " and `tabAssign and contribution contract`.start_date >= date(%(start_date)s)" if filters.get("end_date"): conditions += ( " and `tabAssign and contribution contract`.end_date <= date(%(end_date)s)" ) # if filters.get("status"): # conditions += "and `tabVehicle Details`.vehicle_status = ('%s')" % filters.get("status") match_conditions = build_match_conditions("Assign and contribution contract") if match_conditions: conditions += " and %s" % match_conditions return conditions
def get_event_conditions(doctype, filters=None): """Returns SQL conditions with user permissions and filters for event queries""" from frappe.desk.reportview import build_match_conditions if not frappe.has_permission(doctype): frappe.throw(_("Not Permitted"), frappe.PermissionError) conditions = build_match_conditions(doctype) conditions = conditions and (" and " + conditions) or "" if filters: filters = json.loads(filters) for key in filters: if filters[key]: conditions += " and " + key + ' = "' + filters[key].replace( '"', '\"') + '"' return conditions
def add_leaves(events, start, end, filter_conditions=None): conditions = [] if not cint( frappe.db.get_value( "HR Settings", None, "show_leaves_of_all_department_members_in_calendar")): from frappe.desk.reportview import build_match_conditions match_conditions = build_match_conditions("Leave Application") if match_conditions: conditions.append(match_conditions) query = """select name, from_date, to_date, employee_name, half_day, status, employee, docstatus from `tabLeave Application` where from_date <= %(end)s and to_date >= %(start)s <= to_date and docstatus < 2 and status!='Rejected' """ if conditions: query += ' and ' + ' and '.join(conditions) if filter_conditions: query += filter_conditions for d in frappe.db.sql(query, {"start": start, "end": end}, as_dict=True): e = { "name": d.name, "doctype": "Leave Application", "from_date": d.from_date, "to_date": d.to_date, "docstatus": d.docstatus, "color": d.color, "title": cstr(d.employee_name) + (' ' + _('(Half Day)') if d.half_day else ''), } if e not in events: events.append(e)
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)) if filters.get("cost_center"): filters.cost_center = get_cost_centers_with_children(filters.cost_center) conditions.append("cost_center in %(cost_center)s") if filters.get("voucher_no"): conditions.append("voucher_no=%(voucher_no)s") if filters.get("group_by") == "Group by Party" and not filters.get("party_type"): conditions.append("party_type in ('Customer', 'Supplier')") if filters.get("party_type"): conditions.append("party_type=%(party_type)s") if filters.get("party"): conditions.append("party in %(party)s") if not (filters.get("account") or filters.get("party") or filters.get("group_by") in ["Group by Account", "Group by Party"]): conditions.append("posting_date >=%(from_date)s") conditions.append("posting_date <=%(to_date)s") if filters.get("project"): conditions.append("project in %(project)s") company_finance_book = erpnext.get_default_finance_book(filters.get("company")) if not filters.get("finance_book") or (filters.get("finance_book") == company_finance_book): filters['finance_book'] = company_finance_book conditions.append("ifnull(finance_book, '') in (%(finance_book)s, '')") elif filters.get("finance_book"): conditions.append("ifnull(finance_book, '') = %(finance_book)s") from frappe.desk.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 get_customer_list(doctype, txt, searchfield, start, page_len, filters): if frappe.db.get_default("cust_master_name") == "Customer Name": fields = ["name", "customer_group", "territory"] else: fields = ["name", "customer_name", "customer_group", "territory"] match_conditions = build_match_conditions("Customer") match_conditions = "and {}".format(match_conditions) if match_conditions else "" return frappe.db.sql("""select %s from `tabCustomer` where docstatus < 2 and (%s like %s or customer_name like %s) {match_conditions} order by case when name like %s then 0 else 1 end, case when customer_name like %s then 0 else 1 end, name, customer_name limit %s, %s""".format(match_conditions=match_conditions) % (", ".join(fields), searchfield, "%s", "%s", "%s", "%s", "%s", "%s"), ("%%%s%%" % txt, "%%%s%%" % txt, "%%%s%%" % txt, "%%%s%%" % txt, start, page_len))
def joined_quotation_item_query(doctype, txt, searchfield, start, page_len, filters): from frappe.desk.reportview import build_match_conditions, get_filters_cond match_conditions = build_match_conditions("Quotation Item") match_conditions = "and {}".format(match_conditions) if match_conditions else "" if filters: filter_conditions = get_filters_cond(doctype, filters, []) match_conditions += "{}".format(filter_conditions) #frappe.msgprint(match_conditions) txt = "%{}%".format(txt) return frappe.db.sql("""select name, item_code, master_item from `tabQuotation Item` where {key} like %(txt)s {match_conditions} limit %(start)s, %(page_len)s""".format(key=searchfield, match_conditions=match_conditions), dict(start=start, page_len=page_len, txt=txt))
def query_task(doctype, txt, searchfield, start, page_len, filters): from frappe.desk.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 get_conditions(filters): conditions = [] # all Customer and Supplier GLs conditions.append("gl.party_type in ('Customer', 'Supplier')") if filters.get("consolidation_code"): conditions.append( "gl.party in (select party from `tabConsolidation Item` where parent = %(consolidation_code)s)" ) else: conditions.append( "gl.party in (select party from `tabConsolidation Item`)") from frappe.desk.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 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)) if filters.get("party_type"): conditions.append("party_type=%(party_type)s") if filters.get("party"): conditions.append("party in %(party)s") if not (filters.get("account") or filters.get("party")): conditions.append("posting_date >=%(from_date)s") conditions.append("(posting_date <=%(to_date)s or is_opening = 'Yes')") from frappe.desk.reportview import build_match_conditions match_conditions = build_match_conditions("GL Entry") if match_conditions: conditions.append(match_conditions) accounting_dimensions = get_accounting_dimensions(as_list=False) if accounting_dimensions: for dimension in accounting_dimensions: if filters.get(dimension.fieldname): if frappe.get_cached_value('DocType', dimension.document_type, 'is_tree'): filters[dimension.fieldname] = get_dimension_with_children( dimension.document_type, filters.get(dimension.fieldname)) conditions.append("{0} in %({0})s".format( dimension.fieldname)) else: conditions.append("{0} in (%({0})s)".format( dimension.fieldname)) return "and {}".format(" and ".join(conditions)) if conditions else ""
def get_conditions(filters): conditions = [] if filters.get("account"): if not frappe.db.exists("Account", filters["account"]): frappe.throw(_("Account {0} is not valid").format(filters["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.desk.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 get_conditions(filters): conditions = [] if filters.get("account"): if not frappe.db.exists("Account", filters["account"]): frappe.throw( _("Account {0} is not valid").format(filters["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.desk.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 timesheet_conditions(filters): conds = [] conds.append("`tabTimesheet`.docstatus > 0") if filters.get("start_date"): conds.append( "`tabTimesheet`.start_date >= timestamp(%(start_date)s, %(start_time)s)" ) if filters.get("end_date"): conds.append( "`tabTimesheet`.end_date <= timestamp(%(end_date)s, %(end_time)s)") if filters.get("project"): conds.append("`tabTimesheet`.project = %(project)s") if filters.get("task"): conds.append(" `tabTimesheet`.task = %(task)s") match_conditions = build_match_conditions("Timesheet") if match_conditions: conds.append("%s" % match_conditions) if len(conds): return "WHERE " + " AND ".join(conds) else: return ""
def add_match_conditions(q, tl): from frappe.desk.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 get_conditions(filters): conditions = [] if filters.get("account"): lft, rgt = frappe.db.get_value("Accounts", filters["account"], ["lft", "rgt"]) conditions.append("""account in (select name from tabAccounts where lft>=%s and rgt<=%s and docstatus<2)""" % (lft, rgt)) if filters.get("voucher_no"): conditions.append("voucher_no=%(voucher_no)s") if filters.get("party"): conditions.append("party = %(party)s") conditions.append("posting_date>=%(from_date)s") conditions.append("posting_date<=%(to_date)s") from frappe.desk.reportview import build_match_conditions match_conditions = build_match_conditions("GL Entry") if match_conditions: conditions.append(match_conditions) return "where {}".format(" and ".join(conditions)) if conditions else ""
def add_leaves(events, start, end, filter_conditions=None): conditions = [] if not cint(frappe.db.get_value("HR Settings", None, "show_leaves_of_all_department_members_in_calendar")): from frappe.desk.reportview import build_match_conditions match_conditions = build_match_conditions("Leave Application") if match_conditions: conditions.append(match_conditions) query = """select name, from_date, to_date, employee_name, half_day, status, employee, docstatus from `tabLeave Application` where from_date <= %(end)s and to_date >= %(start)s <= to_date and docstatus < 2 and status!='Rejected' """ if conditions: query += ' and ' + ' and '.join(conditions) if filter_conditions: query += filter_conditions for d in frappe.db.sql(query, {"start":start, "end": end}, as_dict=True): e = { "name": d.name, "doctype": "Leave Application", "from_date": d.from_date, "to_date": d.to_date, "docstatus": d.docstatus, "color": d.color, "title": cstr(d.employee_name) + \ (d.half_day and _(" (Half Day)") or ""), } if e not in events: events.append(e)
def build_conditions(filters): simplified_time_log = cint(frappe.db.get_value("Project Settings", None, "simplified_time_log")) conditions = "" if filters.get("from_date"): if simplified_time_log: conditions += " and date_worked >= timestamp(%(from_date)s, %(from_time)s)" else: conditions += " and from_time >= timestamp(%(from_date)s, %(from_time)s)" if filters.get("to_date"): if simplified_time_log: conditions += " and date_worked <= timestamp(%(to_date)s, %(to_time)s)" else: conditions += " and to_time <= timestamp(%(to_date)s, %(to_time)s)" if filters.get("employee"): conditions += " and employee = %(employee)s" from frappe.desk.reportview import build_match_conditions match_conditions = build_match_conditions("Time Log") if match_conditions: conditions += " and %s" % match_conditions return conditions
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)) if filters.get("cost_center"): filters.cost_center = get_cost_centers_with_children( filters.cost_center) conditions.append("cost_center in %(cost_center)s") if filters.get("voucher_no"): voucher_filter_method = filters.get("voucher_filter_method") if voucher_filter_method == "Posted Against Voucher": conditions.append("against_voucher=%(voucher_no)s") elif voucher_filter_method == "Posted By and Against Voucher": conditions.append( "voucher_no=%(voucher_no)s or against_voucher=%(voucher_no)s") else: conditions.append("voucher_no=%(voucher_no)s") if filters.get("against_voucher"): conditions.append("against_voucher=%(against_voucher)s") if filters.get("reference_no"): conditions.append("reference_no=%(reference_no)s") if filters.get("group_by") == _( "Group by Party") and not filters.get("party_type"): conditions.append("party_type in ('Customer', 'Supplier')") if filters.get("party_type"): conditions.append("party_type=%(party_type)s") if filters.get("party"): conditions.append("party in %(party)s") if filters.get("account") or filters.get("party") \ or filters.get("group_by") in [_("Group by Account"), _("Group by Party")]: conditions.append( "(posting_date <= %(to_date)s or is_opening = 'Yes')") else: conditions.append("posting_date between %(from_date)s and %(to_date)s") if not filters.get('show_opening_entries'): conditions.append("is_opening != 'Yes'") if filters.get("project"): conditions.append("project in %(project)s") if filters.get("sales_person"): lft, rgt = frappe.db.get_value("Sales Person", filters.get("sales_person"), ["lft", "rgt"]) conditions.append( """exists(select name from `tabSales Team` steam where steam.sales_person in (select name from `tabSales Person` where lft >= {0} and rgt <= {1}) and steam.parent = party and steam.parenttype = party_type)""".format( lft, rgt)) if filters.get("finance_book"): conditions.append("ifnull(finance_book, '') in (%(finance_book)s, '')") from frappe.desk.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 get_conditions(filters, party_type=None): 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)) if filters.get("cost_center"): filters.cost_center = get_cost_centers_with_children( filters.cost_center) conditions.append("cost_center in %(cost_center)s") if filters.get("voucher_no"): conditions.append("voucher_no=%(voucher_no)s") if filters.get( "group_by") == "Group by Party" and not filters.get("party_type"): conditions.append("party_type in ('Customer', 'Supplier')") if filters.get("party_type"): if filters["party_type"] == 'Customer Group' and filters.get( "party") and party_type == None: lft, rgt = frappe.db.get_value("Customer Group", filters["party"][0], ['lft', 'rgt']) get_parent_customer_groups = frappe.db.sql( """select name from `tabCustomer Group` where lft >= %s and rgt <= %s""", (lft, rgt), as_dict=1) customer_groups = [ "%s" % (frappe.db.escape(d.name)) for d in get_parent_customer_groups ] if customer_groups: cond = "and 1=1" customer_group_condition = ",".join( ['%s'] * len(customer_groups)) % (tuple(customer_groups)) condition = "{0} in ({1})".format(' and customer_group', customer_group_condition) cond += condition customer_list = frappe.db.sql( """ select name from `tabCustomer` where docstatus < 2 {cond} """ .format(cond=cond), as_list=1) if len(customer_list) > 0: filters["party"] = customer_list else: filters["party"] = ['1'] filters["party_type"] = 'Customer' conditions.append("party_type=%(party_type)s") else: conditions.append("party_type=%(party_type)s") if filters.get("party"): conditions.append("party in %(party)s") if not (filters.get("account") or filters.get("party") or filters.get("group_by") in ["Group by Account", "Group by Party"]): conditions.append("posting_date >=%(from_date)s") conditions.append("(posting_date <=%(to_date)s or is_opening = 'Yes')") if filters.get("project"): conditions.append("project in %(project)s") if filters.get("finance_book"): if filters.get("include_default_book_entries"): conditions.append( "(finance_book in (%(finance_book)s, %(company_fb)s, '') OR finance_book IS NULL)" ) else: conditions.append("finance_book in (%(finance_book)s)") from frappe.desk.reportview import build_match_conditions match_conditions = build_match_conditions("GL Entry") if match_conditions: conditions.append(match_conditions) accounting_dimensions = get_accounting_dimensions() if accounting_dimensions: for dimension in accounting_dimensions: if filters.get(dimension): conditions.append("{0} in (%({0})s)".format(dimension)) return "and {}".format(" and ".join(conditions)) if conditions else ""