def get_new_item_code(doctype, txt, searchfield, start, page_len, filters): from erpbee.controllers.queries import get_match_cond return frappe.db.sql( """select name, item_name, description from tabItem where is_stock_item=0 and name not in (select name from `tabProduct Bundle`) and %s like %s %s limit %s, %s""" % (searchfield, "%s", get_match_cond(doctype), "%s", "%s"), ("%%%s%%" % txt, start, page_len))
def item_details(doctype, txt, searchfield, start, page_len, filters): from erpbee.controllers.queries import get_match_cond return frappe.db.sql( """select name, item_name, description from `tabItem` where name in ( select item_code FROM `tabDelivery Note Item` where parent= %s) and %s like "%s" %s limit %s, %s """ % ("%s", searchfield, "%s", get_match_cond(doctype), "%s", "%s"), ((filters or {}).get("delivery_note"), "%%%s%%" % txt, start, page_len))
def load_invoice_items(self): conditions = "" if self.filters.company: conditions += " and company = %(company)s" if self.filters.from_date: conditions += " and posting_date >= %(from_date)s" if self.filters.to_date: conditions += " and posting_date <= %(to_date)s" if self.filters.group_by == "Sales Person": sales_person_cols = ", sales.sales_person, sales.allocated_amount, sales.incentives" sales_team_table = "left join `tabSales Team` sales on sales.parent = `tabSales Invoice`.name" else: sales_person_cols = "" sales_team_table = "" if self.filters.get("sales_invoice"): conditions += " and `tabSales Invoice`.name = %(sales_invoice)s" if self.filters.get("item_code"): conditions += " and `tabSales Invoice Item`.item_code = %(item_code)s" self.si_list = frappe.db.sql( """ select `tabSales Invoice Item`.parenttype, `tabSales Invoice Item`.parent, `tabSales Invoice`.posting_date, `tabSales Invoice`.posting_time, `tabSales Invoice`.project, `tabSales Invoice`.update_stock, `tabSales Invoice`.customer, `tabSales Invoice`.customer_group, `tabSales Invoice`.territory, `tabSales Invoice Item`.item_code, `tabSales Invoice Item`.item_name, `tabSales Invoice Item`.description, `tabSales Invoice Item`.warehouse, `tabSales Invoice Item`.item_group, `tabSales Invoice Item`.brand, `tabSales Invoice Item`.dn_detail, `tabSales Invoice Item`.delivery_note, `tabSales Invoice Item`.stock_qty as qty, `tabSales Invoice Item`.base_net_rate, `tabSales Invoice Item`.base_net_amount, `tabSales Invoice Item`.name as "item_row", `tabSales Invoice`.is_return, `tabSales Invoice Item`.cost_center {sales_person_cols} from `tabSales Invoice` inner join `tabSales Invoice Item` on `tabSales Invoice Item`.parent = `tabSales Invoice`.name {sales_team_table} where `tabSales Invoice`.docstatus=1 and `tabSales Invoice`.is_opening!='Yes' {conditions} {match_cond} order by `tabSales Invoice`.posting_date desc, `tabSales Invoice`.posting_time desc""" .format(conditions=conditions, sales_person_cols=sales_person_cols, sales_team_table=sales_team_table, match_cond=get_match_cond('Sales Invoice')), self.filters, as_dict=1)
def get_project(doctype, txt, searchfield, start, page_len, filters): from erpbee.controllers.queries import get_match_cond return frappe.db.sql(""" select name from `tabProject` where %(key)s like %(txt)s %(mcond)s order by name limit %(start)s, %(page_len)s""" % { 'key': searchfield, 'txt': frappe.db.escape('%' + txt + '%'), 'mcond':get_match_cond(doctype), 'start': start, 'page_len': page_len })
def item_query(doctype, txt, searchfield, start, page_len, filters): meta = frappe.get_meta("Item", cached=True) searchfields = meta.get_search_fields() order_by = "idx desc, name, item_name" fields = ["name", "item_group", "item_name", "description"] fields.extend([field for field in searchfields if not field in ["name", "item_group", "description"]]) searchfields = searchfields + [field for field in [searchfield or "name", "item_code", "item_group", "item_name"] if not field in searchfields] query_filters = { "disabled": 0, "ifnull(end_of_life, '5050-50-50')": (">", today()) } or_cond_filters = {} if txt: for s_field in searchfields: or_cond_filters[s_field] = ("like", "%{0}%".format(txt)) barcodes = frappe.get_all("Item Barcode", fields=["distinct parent as item_code"], filters = {"barcode": ("like", "%{0}%".format(txt))}) barcodes = [d.item_code for d in barcodes] if barcodes: or_cond_filters["name"] = ("in", barcodes) for cond in get_match_cond(doctype, as_condition=False): for key, value in cond.items(): if key == doctype: key = "name" query_filters[key] = ("in", value) if filters and filters.get("item_code"): has_variants = frappe.get_cached_value("Item", filters.get("item_code"), "has_variants") if not has_variants: query_filters["has_variants"] = 0 return frappe.get_all("Item", fields = fields, filters=query_filters, or_filters = or_cond_filters, order_by=order_by, limit_start=start, limit_page_length=page_len, as_list=1)
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 (JSON). """ filters = json.loads(filters) from frappe.desk.calendar import get_event_conditions conditions = get_event_conditions("Timesheet", filters) return frappe.db.sql("""select `tabTimesheet Detail`.name as name, `tabTimesheet Detail`.docstatus as status, `tabTimesheet Detail`.parent as parent, from_time as start_date, hours, activity_type, `tabTimesheet Detail`.project, to_time as end_date, CONCAT(`tabTimesheet Detail`.parent, ' (', ROUND(hours,2),' hrs)') as title from `tabTimesheet Detail`, `tabTimesheet` where `tabTimesheet Detail`.parent = `tabTimesheet`.name and `tabTimesheet`.docstatus < 2 and (from_time <= %(end)s and to_time >= %(start)s) {conditions} {match_cond} """.format(conditions=conditions, match_cond = get_match_cond('Timesheet')), { "start": start, "end": end }, as_dict=True, update={"allDay": 0})