def warehouse_query(doctype, txt, searchfield, start, page_len, filters):
# Should be used when item code is passed in filters.
conditions, bin_conditions = [], []
filter_dict = get_doctype_wise_filters(filters)
sub_query = """ select round(`tabBin`.actual_qty, 2) from `tabBin`
where `tabBin`.warehouse = `tabWarehouse`.name
{bin_conditions} """.format(
bin_conditions=get_filters_cond(doctype, filter_dict.get("Bin"), bin_conditions))
response = frappe.db.sql("""select `tabWarehouse`.name,
CONCAT_WS(" : ", "Actual Qty", ifnull( ({sub_query}), 0) ) as actual_qty
from `tabWarehouse`
where
`tabWarehouse`.`{key}` like %(txt)s
{fcond} {mcond}
order by
`tabWarehouse`.name desc
limit
%(start)s, %(page_len)s
""".format(
sub_query=sub_query,
key=frappe.db.escape(searchfield),
fcond=get_filters_cond(doctype, filter_dict.get("Warehouse"), conditions),
mcond=get_match_cond(doctype)
),
{
"txt": "%%%s%%" % frappe.db.escape(txt),
"start": start,
"page_len": page_len
})
return response
def warehouse_query(doctype, txt, searchfield, start, page_len, filters):
# Should be used when item code is passed in filters.
conditions, bin_conditions = [], []
filter_dict = get_doctype_wise_filters(filters)
sub_query = """ select round(`tabBin`.actual_qty, 2) from `tabBin`
where `tabBin`.warehouse = `tabWarehouse`.name
{bin_conditions} """.format(
bin_conditions=get_filters_cond(doctype, filter_dict.get("Bin"),
bin_conditions, ignore_permissions=True))
query = """select `tabWarehouse`.name,
CONCAT_WS(" : ", "Actual Qty", ifnull( ({sub_query}), 0) ) as actual_qty
from `tabWarehouse`
where
`tabWarehouse`.`{key}` like '{txt}'
{fcond} {mcond}
order by
`tabWarehouse`.name desc
limit
{start}, {page_len}
""".format(
sub_query=sub_query,
key=frappe.db.escape(searchfield),
fcond=get_filters_cond(doctype, filter_dict.get("Warehouse"), conditions),
mcond=get_match_cond(doctype),
start=start,
page_len=page_len,
txt=frappe.db.escape('%{0}%'.format(txt))
)
return frappe.db.sql(query)
def warehouse_query(doctype, txt, searchfield, start, page_len, filters):
# Should be used when item code is passed in filters.
conditions, bin_conditions = [], []
filter_dict = get_doctype_wise_filters(filters)
sub_query = """ select round(`tabBin`.actual_qty, 2) from `tabBin`
where `tabBin`.warehouse = `tabWarehouse`.name
{bin_conditions} """.format(
bin_conditions=get_filters_cond(doctype,
filter_dict.get("Bin"),
bin_conditions,
ignore_permissions=True))
query = """select `tabWarehouse`.name,
CONCAT_WS(" : ", "Actual Qty", ifnull( ({sub_query}), 0) ) as actual_qty
from `tabWarehouse`
where
`tabWarehouse`.`{key}` like {txt}
{fcond} {mcond}
order by
`tabWarehouse`.name desc
limit
{start}, {page_len}
""".format(sub_query=sub_query,
key=searchfield,
fcond=get_filters_cond(doctype, filter_dict.get("Warehouse"),
conditions),
mcond=get_match_cond(doctype),
start=start,
page_len=page_len,
txt=frappe.db.escape('%{0}%'.format(txt)))
return frappe.db.sql(query)
def warehouse_query(doctype, txt, searchfield, start, page_len, filters):
# Should be used when item code is passed in filters.
conditions, bin_conditions = [], []
filter_dict = get_doctype_wise_filters(filters)
sub_query = """ select round(`tabBin`.actual_qty, 2) from `tabBin`
where `tabBin`.warehouse = `tabWarehouse`.name
{bin_conditions} """.format(bin_conditions=get_filters_cond(
doctype, filter_dict.get("Bin"), bin_conditions))
response = frappe.db.sql(
"""select `tabWarehouse`.name,
CONCAT_WS(" : ", "Actual Qty", ifnull( ({sub_query}), 0) ) as actual_qty
from `tabWarehouse`
where
`tabWarehouse`.`{key}` like %(txt)s
{fcond} {mcond}
order by
`tabWarehouse`.name desc
limit
%(start)s, %(page_len)s
""".format(sub_query=sub_query,
key=frappe.db.escape(searchfield),
fcond=get_filters_cond(doctype, filter_dict.get("Warehouse"),
conditions),
mcond=get_match_cond(doctype)), {
"txt": "%%%s%%" % frappe.db.escape(txt),
"start": start,
"page_len": page_len
})
return response
def item_query(doctype, txt, searchfield, start, page_len, filters, as_dict=False):
conditions = []
return frappe.db.sql("""select tabItem.name, tabItem.item_group, tabItem.image,
if(length(tabItem.item_name) > 40,
concat(substr(tabItem.item_name, 1, 40), "..."), item_name) as item_name,
if(length(tabItem.description) > 40, \
concat(substr(tabItem.description, 1, 40), "..."), description) as decription
from tabItem
where tabItem.docstatus < 2
and tabItem.has_variants=0
and tabItem.disabled=0
and (tabItem.end_of_life > %(today)s or ifnull(tabItem.end_of_life, '0000-00-00')='0000-00-00')
and (tabItem.`{key}` LIKE %(txt)s
or tabItem.item_group LIKE %(txt)s
or tabItem.item_name LIKE %(txt)s
or tabItem.description LIKE %(txt)s)
{fcond} {mcond}
order by
if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
if(locate(%(_txt)s, item_name), locate(%(_txt)s, item_name), 99999),
idx desc,
name, item_name
limit %(start)s, %(page_len)s """.format(key=searchfield,
fcond=get_filters_cond(doctype, filters, conditions).replace('%', '%%'),
mcond=get_match_cond(doctype).replace('%', '%%')),
{
"today": nowdate(),
"txt": "%%%s%%" % txt,
"_txt": txt.replace("%", ""),
"start": start,
"page_len": page_len
}, as_dict=as_dict)
def get_sales_person(doctype, txt, searchfield, start, page_len, filters):
conditions = []
sales_persons = frappe.db.sql(
""" SELECT SP.name as name from `tabSales Person` AS SP
INNER JOIN `tabSales Team` AS ST ON ST.sales_person = SP.name
INNER JOIN `tabSales Invoice` AS SI ON SI.name = ST.parent and SI.docstatus = 1 and SI.agent_commision_record = 0
WHERE SP.enabled = 1 """,
as_dict=1)
sales_persons_has_si = []
for i in sales_persons:
if i.name not in sales_persons_has_si:
sales_persons_has_si.append(i.name)
return frappe.db.sql(
"""select SP.name from `tabSales Person` AS SP
where SP.enabled = 1
and SP.name like %(txt)s
and SP.name in {names}
{fcond} {mcond}
order by
if(locate(%(_txt)s, SP.name), locate(%(_txt)s, SP.name), 99999),
SP.idx desc,
SP.name
limit %(start)s, %(page_len)s""".format(
**{
'fcond': get_filters_cond(doctype, filters, conditions),
'names': tuple(sales_persons_has_si),
'mcond': get_match_cond(doctype)
}), {
'txt': "%%%s%%" % txt,
'_txt': txt.replace("%", ""),
'start': start,
'page_len': page_len
})
def get_school_events(start, end, user=None, filters=None):
if not user:
user = frappe.session.user
if isinstance(filters, string_types):
filters = json.loads(filters)
filter_condition = get_filters_cond('School Event', filters, [])
tables = ["`tabSchool Event`"]
if "`tabSchool Event Participants`" in filter_condition:
tables.append("`tabSchool Event Participants`")
events = frappe.db.sql(
""" SELECT `tabSchool Event`.name, `tabSchool Event`.subject,
`tabSchool Event`.color, `tabSchool Event`.starts_on, `tabSchool Event`.ends_on,
`tabSchool Event`.owner, `tabSchool Event`.all_day, `tabSchool Event`.event_category,
`tabSchool Event`.school, `tabSchool Event`.room
FROM {tables}
WHERE ( (date(`tabSchool Event`.starts_on) BETWEEN date(%(start)s) AND date(%(end)s))
OR (date(`tabSchool Event`.ends_on) BETWEEN date(%(start)s) AND date(%(end)s))
)
{filter_condition}
ORDER BY `tabSchool Event`.starts_on""".format(
tables=", ".join(tables), filter_condition=filter_condition), {
"start": start,
"end": end,
"user": user
},
as_dict=1)
allowed_events = []
for event in events:
if frappe.get_doc("School Event", event.name).has_permission():
allowed_events.append(event)
return allowed_events
def get_interviewers(doctype, txt, searchfield, start, page_len, filters):
from frappe.desk.reportview import get_match_cond, get_filters_cond
conditions = []
return frappe.db.sql(
"""SELECT emp.name,emp.employee_name
FROM `tabHas Role` as role
INNER JOIN `tabEmployee` as emp
WHERE role.parent = emp.user_id
and role.role ='Interviewer' and role.`parenttype` = 'user' and role.parent <> 'Administrator'
and role.docstatus < 2
and ({key} like %(txt)s)
{fcond} {mcond}
order by
if(locate(%(_txt)s, role.parent), locate(%(_txt)s, role.parent), 99999) desc
limit %(start)s, %(page_len)s""".format(
**{
'key': 'emp.employee_name',
'fcond': get_filters_cond(doctype, filters, conditions),
'mcond': get_match_cond(doctype)
}), {
'txt': "%%%s%%" % txt,
'_txt': txt.replace("%", ""),
'start': start,
'page_len': page_len
})
def get_events(start, end, filters=None):
events = []
employee = frappe.db.get_value("Employee",
{"user_id": frappe.session.user},
["name", "company"],
as_dict=True)
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 get_filters_cond
conditions = get_filters_cond("Leave Application", filters, [])
# show department leaves for employee
if "Employee" in frappe.get_roles():
add_department_leaves(events, start, end, employee, company)
add_leaves(events, start, end, conditions)
add_block_dates(events, start, end, employee, company)
add_holidays(events, start, end, employee, company)
return events
def employee_query(doctype, txt, searchfield, start, page_len, filters):
conditions = []
fields = get_fields("Employee", ["name", "employee_name"])
return frappe.db.sql(
"""select {fields} from `tabEmployee`
where status in ('Active', 'Suspended')
and docstatus < 2
and ({key} like %(txt)s
or employee_name like %(txt)s)
{fcond} {mcond}
order by
if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
if(locate(%(_txt)s, employee_name), locate(%(_txt)s, employee_name), 99999),
idx desc,
name, employee_name
limit %(start)s, %(page_len)s""".format(
**{
"fields": ", ".join(fields),
"key": searchfield,
"fcond": get_filters_cond(doctype, filters, conditions),
"mcond": get_match_cond(doctype),
}),
{
"txt": "%%%s%%" % txt,
"_txt": txt.replace("%", ""),
"start": start,
"page_len": page_len
},
)
def customer_query(doctype, txt, searchfield, start, page_len, filters):
conditions = []
cust_master_name = frappe.defaults.get_user_default("cust_master_name")
if cust_master_name == "Customer Name":
fields = ["name", "customer_group", "territory"]
else:
fields = ["name", "customer_name", "customer_group", "territory"]
fields = get_fields("Customer", fields)
searchfields = frappe.get_meta("Customer").get_search_fields()
searchfields = " or ".join([field + " like %(txt)s" for field in searchfields])
return frappe.db.sql("""select {fields} from `tabCustomer`
where docstatus < 2
and ({scond}) and disabled=0
{fcond} {mcond}
order by
if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
if(locate(%(_txt)s, customer_name), locate(%(_txt)s, customer_name), 99999),
idx desc,
name, customer_name
limit %(start)s, %(page_len)s""".format(**{
"fields": ", ".join(fields),
"scond": searchfields,
"mcond": get_match_cond(doctype),
"fcond": get_filters_cond(doctype, filters, conditions).replace('%', '%%'),
}), {
'txt': "%%%s%%" % txt,
'_txt': txt.replace("%", ""),
'start': start,
'page_len': page_len
})
def get_events(start, end, filters=None):
events = []
employee = frappe.db.get_value("Employee", {"user_id": frappe.session.user}, ["name", "company"],
as_dict=True)
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 get_filters_cond
conditions = get_filters_cond("Leave Application", filters, [])
user_role = get_user_role()
subordinates = get_auth_team_ids()
if (conditions):
conditions += " and `tabLeave Application`.employee IN (%(subordinates)s)" % {"subordinates": subordinates}
else:
conditions = " and `tabLeave Application`.employee IN (%(subordinates)s)" % {"subordinates": subordinates}
add_leaves(events, start, end, conditions)
add_block_dates(events, start, end, employee, company)
add_holidays(events, start, end, employee, company)
return events
def user_query(doctype, txt, searchfield, start, page_len, filters):
from frappe.desk.reportview import get_match_cond, get_filters_cond
conditions = []
user_type_condition = "and user_type != 'Website User'"
if filters and filters.get('ignore_user_type'):
user_type_condition = ''
filters.pop('ignore_user_type')
txt = "%{}%".format(txt)
return frappe.db.sql(
"""SELECT `name`, CONCAT_WS(' ', first_name, middle_name, last_name)
FROM `tabUser`
WHERE `enabled`=1
{user_type_condition}
AND `docstatus` < 2
AND `name` NOT IN ({standard_users})
AND ({key} LIKE %(txt)s
OR CONCAT_WS(' ', first_name, middle_name, last_name) LIKE %(txt)s)
{fcond} {mcond}
ORDER BY
CASE WHEN `name` LIKE %(txt)s THEN 0 ELSE 1 END,
CASE WHEN concat_ws(' ', first_name, middle_name, last_name) LIKE %(txt)s
THEN 0 ELSE 1 END,
NAME asc
LIMIT %(page_len)s OFFSET %(start)s
""".format(user_type_condition=user_type_condition,
standard_users=", ".join(
[frappe.db.escape(u) for u in STANDARD_USERS]),
key=searchfield,
fcond=get_filters_cond(doctype, filters, conditions),
mcond=get_match_cond(doctype)),
dict(start=start, page_len=page_len, txt=txt))
def get_event_conditions(doctype, filters=None):
"""Returns SQL conditions with user permissions and filters for event queries"""
from frappe.desk.reportview import get_filters_cond
if not frappe.has_permission(doctype):
frappe.throw(_("Not Permitted"), frappe.PermissionError)
return get_filters_cond(doctype, filters, [], with_match_conditions = True)
def bom(doctype, txt, searchfield, start, page_len, filters):
conditions = []
fields = get_fields("BOM", ["name", "item"])
return frappe.db.sql(
"""select {fields}
from tabBOM
where tabBOM.docstatus=1
and tabBOM.is_active=1
and tabBOM.`{key}` like %(txt)s
{fcond} {mcond}
order by
if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
idx desc, name
limit %(start)s, %(page_len)s """.format(
fields=", ".join(fields),
fcond=get_filters_cond(doctype, filters,
conditions).replace('%', '%%'),
mcond=get_match_cond(doctype).replace('%', '%%'),
key=searchfield), {
'txt': '%' + txt + '%',
'_txt': txt.replace("%", ""),
'start': start or 0,
'page_len': page_len or 20
})
def address_query(doctype, txt, searchfield, start, page_len, filters):
conditions = []
fields = ['name', 'address_line1', 'city', 'country']
fields = get_fields("Address", fields)
searchfields = frappe.get_meta("Address").get_search_fields()
searchfields = " or ".join(
[field + " like %(txt)s" for field in searchfields])
return frappe.db.sql(
"""select {fields} from `tabAddress`
where docstatus < 2
and ({scond}) and disabled=0
{fcond} {mcond}
order by
if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
idx desc,
address_title
limit %(start)s, %(page_len)s""".format(
**{
"fields":
", ".join(fields),
"scond":
searchfields,
"mcond":
get_match_cond(doctype),
"fcond":
get_filters_cond(doctype, filters, conditions).replace(
'%', '%%'),
}), {
'txt': "%%%s%%" % txt,
'_txt': txt.replace("%", ""),
'start': start,
'page_len': page_len
})
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_delivery_notes_to_be_billed(doctype, txt, searchfield, start, page_len,
filters, as_dict):
return frappe.db.sql("""
select `tabDelivery Note`.name, `tabDelivery Note`.customer, `tabDelivery Note`.posting_date
from `tabDelivery Note`
where `tabDelivery Note`.`%(key)s` like %(txt)s and
`tabDelivery Note`.docstatus = 1
and status not in ("Stopped", "Closed") %(fcond)s
and (
(`tabDelivery Note`.is_return = 0 and `tabDelivery Note`.per_billed < 100)
or `tabDelivery Note`.grand_total = 0
or (
`tabDelivery Note`.is_return = 1
and return_against in (select name from `tabDelivery Note` where per_billed < 100)
)
)
%(mcond)s order by `tabDelivery Note`.`%(key)s` asc limit %(start)s, %(page_len)s
""" % {
"key": searchfield,
"fcond": get_filters_cond(doctype, filters, []),
"mcond": get_match_cond(doctype),
"start": start,
"page_len": page_len,
"txt": "%(txt)s"
}, {"txt": ("%%%s%%" % txt)},
as_dict=as_dict)
def employee_query(doctype, txt, searchfield, start, page_len, filters):
conditions = []
fields = get_fields("Employee", ["name", "employee_name"])
return frappe.db.sql(
"""select {fields} from `tabEmployee`
where status = 'Active'
and docstatus < 2
and ({key} like %(txt)s
or employee_name like %(txt)s)
{fcond} {mcond}
order by
if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
if(locate(%(_txt)s, employee_name), locate(%(_txt)s, employee_name), 99999),
idx desc,
name, employee_name
limit %(start)s, %(page_len)s""".format(
**{
'fields': ", ".join(fields),
'key': searchfield,
'fcond': get_filters_cond(doctype, filters, conditions),
'mcond': get_match_cond(doctype)
}), {
'txt': "%%%s%%" % txt,
'_txt': txt.replace("%", ""),
'start': start,
'page_len': page_len
})
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_event_conditions(doctype, filters=None):
"""Returns SQL conditions with user permissions and filters for event queries"""
from frappe.desk.reportview import get_filters_cond
if not frappe.has_permission(doctype):
frappe.throw(_("Not Permitted"), frappe.PermissionError)
return get_filters_cond(doctype, filters, [], with_match_conditions=True)
def item_query(doctype, txt, searchfield, start, page_len, filters, as_dict=False):
conditions = []
return frappe.db.sql("""select tabItem.name, tabItem.item_group, tabItem.image,
if(length(tabItem.item_name) > 40,
concat(substr(tabItem.item_name, 1, 40), "..."), item_name) as item_name,
if(length(tabItem.description) > 40, \
concat(substr(tabItem.description, 1, 40), "..."), description) as decription
from tabItem
where tabItem.docstatus < 2
and tabItem.has_variants=0
and tabItem.disabled=0
and (tabItem.end_of_life > %(today)s or ifnull(tabItem.end_of_life, '0000-00-00')='0000-00-00')
and (tabItem.`{key}` LIKE %(txt)s
or tabItem.item_group LIKE %(txt)s
or tabItem.item_name LIKE %(txt)s
or tabItem.barcode LIKE %(txt)s
or tabItem.description LIKE %(txt)s)
{fcond} {mcond}
order by
if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
if(locate(%(_txt)s, item_name), locate(%(_txt)s, item_name), 99999),
idx desc,
name, item_name
limit %(start)s, %(page_len)s """.format(
key=searchfield,
fcond=get_filters_cond(doctype, filters, conditions).replace('%', '%%'),
mcond=get_match_cond(doctype).replace('%', '%%')),
{
"today": nowdate(),
"txt": "%%%s%%" % txt,
"_txt": txt.replace("%", ""),
"start": start,
"page_len": page_len
}, as_dict=as_dict)
def item_query(doctype, txt, searchfield, start, page_len, filters, as_dict=False):
conditions = []
#Get searchfields from meta and use in Item Link field query
meta = frappe.get_meta("Item", cached=True)
searchfields = meta.get_search_fields()
if "description" in searchfields:
searchfields.remove("description")
columns = ''
extra_searchfields = [field for field in searchfields
if field not in ["name", "item_group", "description"]]
if extra_searchfields:
columns = ", " + ", ".join(extra_searchfields)
searchfields = searchfields + [field for field in[searchfield or "name", "item_code", "item_group", "item_name"]
if not field in searchfields]
searchfields = " or ".join([field + " like %(txt)s" for field in searchfields])
description_cond = ''
if frappe.db.count('Item', cache=True) < 50000:
# scan description only if items are less than 50000
description_cond = 'or tabItem.description LIKE %(txt)s'
return frappe.db.sql("""select tabItem.name,
if(length(tabItem.item_name) > 40,
concat(substr(tabItem.item_name, 1, 40), "..."), item_name) as item_name,
tabItem.item_group,
if(length(tabItem.description) > 40, \
concat(substr(tabItem.description, 1, 40), "..."), description) as description
{columns}
from tabItem
where tabItem.docstatus < 2
and tabItem.has_variants=0
and tabItem.disabled=0
and (tabItem.end_of_life > %(today)s or ifnull(tabItem.end_of_life, '0000-00-00')='0000-00-00')
and ({scond} or tabItem.item_code IN (select parent from `tabItem Barcode` where barcode LIKE %(txt)s)
{description_cond})
{fcond} {mcond}
order by
if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
if(locate(%(_txt)s, item_name), locate(%(_txt)s, item_name), 99999),
idx desc,
name, item_name
limit %(start)s, %(page_len)s """.format(
key=searchfield,
columns=columns,
scond=searchfields,
fcond=get_filters_cond(doctype, filters, conditions).replace('%', '%%'),
mcond=get_match_cond(doctype).replace('%', '%%'),
description_cond = description_cond),
{
"today": nowdate(),
"txt": "%%%s%%" % txt,
"_txt": txt.replace("%", ""),
"start": start,
"page_len": page_len
}, as_dict=as_dict)
def get_events(start, end, filters=None):
events = []
employee = frappe.db.get_value("Employee",
{"user_id": frappe.session.user})
if not employee:
return events
from frappe.desk.reportview import get_filters_cond
conditions = get_filters_cond("Attendance", filters, [])
user_role = get_user_role()
if (user_role == LINE_MANAGER or user_role == EMPLOYEE):
subordinates = get_auth_team_ids()
if (conditions):
conditions += " and `tabAttendance`.employee IN (%(subordinates)s)" % {
"subordinates": subordinates
}
else:
conditions = " and `tabAttendance`.employee IN (%(subordinates)s)" % {
"subordinates": subordinates
}
add_attendance(events, start, end, conditions=conditions)
return events
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_unused_position(doctype, txt, searchfield, start, page_len, filters):
from frappe.desk.reportview import get_match_cond, get_filters_cond
conditions = []
if not page_len:
return frappe.db.sql(
"""
select `name`
from tabPositions
where `status`='Active'
and name not in (select position from tabEmployee where position is not NULL)
and ({key} like %(txt)s)
{fcond} {mcond}
order by
if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999) desc """
.format(
**{
'key': searchfield,
'fcond': get_filters_cond(doctype, filters, conditions),
'mcond': get_match_cond(doctype)
}), {
'txt': "%%%s%%" % txt,
'_txt': txt.replace("%", ""),
})
else:
return frappe.db.sql(
"""
select `name`
from tabPositions
where `status`='Active'
and name not in (select position from tabEmployee where position is not NULL)
and ({key} like %(txt)s)
{fcond} {mcond}
order by
if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999) desc
limit %(start)s, %(page_len)s""".format(
**{
'key': searchfield,
'fcond': get_filters_cond(doctype, filters, conditions),
'mcond': get_match_cond(doctype)
}), {
'txt': "%%%s%%" % txt,
'_txt': txt.replace("%", ""),
'start': start,
'page_len': page_len
})
def get_no_of_samples(doctype, filters=None):
conditions = []
samples = frappe.db.sql("""SELECT count(*) FROM `tabSamples`
WHERE docstatus < 2 {fcond} {mcond} """.format(
**{
'fcond': get_filters_cond(doctype, filters, conditions),
'mcond': get_match_cond(doctype)
}))
return samples[0][0]
def test_ignore_permissions_for_get_filters_cond(self):
frappe.set_user('*****@*****.**')
self.assertRaises(frappe.PermissionError, get_filters_cond, 'DocType',
dict(istable=1), [])
self.assertTrue(
get_filters_cond('DocType',
dict(istable=1), [],
ignore_permissions=True))
frappe.set_user('Administrator')
def employee_query(doctype, txt, searchfield, start, page_len, filters):
filters = frappe._dict(filters)
conditions = []
include_employees = []
emp_cond = ""
if not filters.payroll_frequency:
frappe.throw(_("Select Payroll Frequency."))
if filters.start_date and filters.end_date:
employee_list = get_employee_list(filters)
emp = filters.get("employees") or []
include_employees = [
employee.employee for employee in employee_list if employee.employee not in emp
]
filters.pop("start_date")
filters.pop("end_date")
filters.pop("salary_slip_based_on_timesheet")
filters.pop("payroll_frequency")
filters.pop("payroll_payable_account")
filters.pop("currency")
if filters.employees is not None:
filters.pop("employees")
if include_employees:
emp_cond += "and employee in %(include_employees)s"
return frappe.db.sql(
"""select name, employee_name from `tabEmployee`
where status = 'Active'
and docstatus < 2
and ({key} like %(txt)s
or employee_name like %(txt)s)
{emp_cond}
{fcond} {mcond}
order by
if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
if(locate(%(_txt)s, employee_name), locate(%(_txt)s, employee_name), 99999),
idx desc,
name, employee_name
limit %(start)s, %(page_len)s""".format(
**{
"key": searchfield,
"fcond": get_filters_cond(doctype, filters, conditions),
"mcond": get_match_cond(doctype),
"emp_cond": emp_cond,
}
),
{
"txt": "%%%s%%" % txt,
"_txt": txt.replace("%", ""),
"start": start,
"page_len": page_len,
"include_employees": include_employees,
},
)
def employee_query(doctype, txt, searchfield, start, page_len, filters):
filters = frappe._dict(filters)
conditions = []
include_employees = []
emp_cond = ''
if not filters.payroll_frequency:
frappe.throw(_('Select Payroll Frequency.'))
if filters.start_date and filters.end_date:
employee_list = get_employee_list(filters)
emp = filters.get('employees') or []
include_employees = [
employee.employee for employee in employee_list
if employee.employee not in emp
]
filters.pop('start_date')
filters.pop('end_date')
filters.pop('salary_slip_based_on_timesheet')
filters.pop('payroll_frequency')
filters.pop('payroll_payable_account')
filters.pop('currency')
if filters.employees is not None:
filters.pop('employees')
if include_employees:
emp_cond += 'and employee in %(include_employees)s'
return frappe.db.sql(
"""select name, employee_name from `tabEmployee`
where status = 'Active'
and docstatus < 2
and ({key} like %(txt)s
or employee_name like %(txt)s)
{emp_cond}
{fcond} {mcond}
order by
if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
if(locate(%(_txt)s, employee_name), locate(%(_txt)s, employee_name), 99999),
idx desc,
name, employee_name
limit %(start)s, %(page_len)s""".format(
**{
'key': searchfield,
'fcond': get_filters_cond(doctype, filters, conditions),
'mcond': get_match_cond(doctype),
'emp_cond': emp_cond
}), {
'txt': "%%%s%%" % txt,
'_txt': txt.replace("%", ""),
'start': start,
'page_len': page_len,
'include_employees': include_employees
})
def get_events(start, end, filters=None):
events = []
employee = frappe.db.get_value("Employee", {"user_id": frappe.session.user})
if not employee:
return events
from frappe.desk.reportview import get_filters_cond
conditions = get_filters_cond("Attendance", filters, [])
add_attendance(events, start, end, conditions=conditions)
return events
def get_events(start, end, filters=None):
events = []
employee = frappe.db.get_value("Employee", {"user_id": frappe.session.user})
if not employee:
return events
from frappe.desk.reportview import get_filters_cond
conditions = get_filters_cond("Attendance", filters, [])
add_attendance(events, start, end, conditions=conditions)
return events
def get_delivery_notes_to_be_billed(doctype, txt, searchfield, start, page_len, filters):
return frappe.db.sql("""select `tabDelivery Note`.name, `tabDelivery Note`.customer_name
from `tabDelivery Note`
where `tabDelivery Note`.`%(key)s` like %(txt)s and
`tabDelivery Note`.docstatus = 1 and status not in ("Stopped", "Closed") %(fcond)s
and (`tabDelivery Note`.per_billed < 100 or `tabDelivery Note`.grand_total = 0)
%(mcond)s order by `tabDelivery Note`.`%(key)s` asc
limit %(start)s, %(page_len)s""" % {
"key": searchfield,
"fcond": get_filters_cond(doctype, filters, []),
"mcond": get_match_cond(doctype),
"start": "%(start)s", "page_len": "%(page_len)s", "txt": "%(txt)s"
}, { "start": start, "page_len": page_len, "txt": ("%%%s%%" % txt) })
def item_query(doctype,
txt,
searchfield,
start,
page_len,
filters,
as_dict=False):
conditions = []
frappe.errprint("testetst")
description_cond = ''
if frappe.db.count('Item', cache=True) < 50000:
# scan description only if items are less than 50000
description_cond = 'or tabItem.description LIKE %(txt)s'
return frappe.db.sql("""select tabItem.name,
if(length(tabItem.item_name) > 40,
concat(substr(tabItem.item_name, 1, 40), "..."), item_name) as item_name,
tabItem.item_group,
if(length(tabItem.description) > 40, \
concat(substr(tabItem.description, 1, 40), "..."), description) as decription
from tabItem
where tabItem.docstatus < 2
and tabItem.disabled=0
and (tabItem.end_of_life > %(today)s or ifnull(tabItem.end_of_life, '0000-00-00')='0000-00-00')
and (tabItem.`{key}` LIKE %(txt)s
or tabItem.item_code LIKE %(txt)s
or tabItem.item_group LIKE %(txt)s
or tabItem.item_name LIKE %(txt)s
or tabItem.item_code IN (select parent from `tabItem Barcode` where barcode LIKE %(txt)s
{description_cond}))
{fcond} {mcond}
order by
if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
if(locate(%(_txt)s, item_name), locate(%(_txt)s, item_name), 99999),
idx desc,
name, item_name
limit %(start)s, %(page_len)s """.format(
key=searchfield,
fcond=get_filters_cond(doctype, filters,
conditions).replace('%', '%%'),
mcond=get_match_cond(doctype).replace('%', '%%'),
description_cond=description_cond), {
"today": nowdate(),
"txt": "%%%s%%" % txt.replace(" ", "%%"),
"_txt": txt.replace("%", ""),
"start": start,
"page_len": page_len
},
as_dict=as_dict)
def get_events(start, end, filters=None):
events = []
employee = frappe.db.get_value("Employee", {"user_id": frappe.session.user}, ["name", "company"],
as_dict=True)
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 get_filters_cond
conditions = get_filters_cond("Shift Assignment", filters, [])
add_assignments(events, start, end, conditions=conditions)
return events
def custom_item_query(doctype,
txt,
searchfield,
start,
page_len,
filters,
as_dict=False):
namesearch = '('
for strparam in txt.split("%"):
namesearch += " tabItem.item_name like '%" + strparam + "%' and"
namesearch = namesearch.rsplit(' ', 1)[0]
namesearch += ' )'
conditions = []
return frappe.db.sql("""
select tabItem.name, tabItem.item_name as item_name, tabItem.item_group
from tabItem
left join tabBin on tabBin.item_code = tabItem.name
where tabItem.docstatus < 2
and tabItem.has_variants=0
and tabItem.disabled=0
and (tabItem.end_of_life > %(today)s or ifnull(tabItem.end_of_life, '0000-00-00')='0000-00-00')
and (tabItem.`{key}` LIKE %(txt)s
or tabItem.item_group LIKE %(txt)s
or {namesearch}
)
{fcond} {mcond}
group by tabItem.name
order by
sum(ifnull(tabBin.actual_qty - tabBin.reserved_qty, 0)) desc,
if(locate(%(_txt)s, tabItem.name), locate(%(_txt)s, tabItem.name), 99999),
if(locate(%(_txt)s, tabItem.item_name), locate(%(_txt)s, tabItem.item_name), 99999),
tabItem.idx desc,
tabItem.name, tabItem.item_name
limit %(start)s, %(page_len)s """.format(
key=searchfield,
fcond=get_filters_cond(doctype, filters,
conditions).replace('%', '%%'),
mcond=get_match_cond(doctype).replace('%', '%%'),
namesearch=namesearch.replace('%', '%%')), {
"today": nowdate(),
"txt": "%%%s%%" % txt,
"_txt": txt.replace("%", ""),
"start": start,
"page_len": page_len
},
as_dict=as_dict)
def get_job_details(start, end, filters=None):
events = []
event_color = {
"Completed": "#cdf5a6",
"Material Transferred": "#ffdd9e",
"Work In Progress": "#D3D3D3",
}
from frappe.desk.reportview import get_filters_cond
conditions = get_filters_cond("Job Card", filters, [])
job_cards = frappe.db.sql(
""" SELECT `tabJob Card`.name, `tabJob Card`.work_order,
`tabJob Card`.status, ifnull(`tabJob Card`.remarks, ''),
min(`tabJob Card Time Log`.from_time) as from_time,
max(`tabJob Card Time Log`.to_time) as to_time
FROM `tabJob Card` , `tabJob Card Time Log`
WHERE
`tabJob Card`.name = `tabJob Card Time Log`.parent {0}
group by `tabJob Card`.name""".format(
conditions
),
as_dict=1,
)
for d in job_cards:
subject_data = []
for field in ["name", "work_order", "remarks"]:
if not d.get(field):
continue
subject_data.append(d.get(field))
color = event_color.get(d.status)
job_card_data = {
"from_time": d.from_time,
"to_time": d.to_time,
"name": d.name,
"subject": "\n".join(subject_data),
"color": color if color else "#89bcde",
}
events.append(job_card_data)
return events
def get_delivery_notes_to_be_billed(doctype, txt, searchfield, start, page_len, filters, as_dict):
return frappe.db.sql("""
select `tabDelivery Note`.name, `tabDelivery Note`.customer, `tabDelivery Note`.posting_date
from `tabDelivery Note`
where `tabDelivery Note`.`%(key)s` like %(txt)s and
`tabDelivery Note`.docstatus = 1
and status not in ("Stopped", "Closed") %(fcond)s
and (
(`tabDelivery Note`.is_return = 0 and `tabDelivery Note`.per_billed < 100)
or `tabDelivery Note`.grand_total = 0
or (
`tabDelivery Note`.is_return = 1
and return_against in (select name from `tabDelivery Note` where per_billed < 100)
)
)
%(mcond)s order by `tabDelivery Note`.`%(key)s` asc
""" % {
"key": searchfield,
"fcond": get_filters_cond(doctype, filters, []),
"mcond": get_match_cond(doctype),
"txt": "%(txt)s"
}, {"txt": ("%%%s%%" % txt)}, as_dict=as_dict)
def bom(doctype, txt, searchfield, start, page_len, filters):
conditions = []
return frappe.db.sql("""select tabBOM.name, tabBOM.item
from tabBOM
where tabBOM.docstatus=1
and tabBOM.is_active=1
and tabBOM.`{key}` like %(txt)s
{fcond} {mcond}
order by
if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
idx desc, name
limit %(start)s, %(page_len)s """.format(
fcond=get_filters_cond(doctype, filters, conditions),
mcond=get_match_cond(doctype),
key=frappe.db.escape(searchfield)),
{
'txt': "%%%s%%" % frappe.db.escape(txt),
'_txt': txt.replace("%", ""),
'start': start or 0,
'page_len': page_len or 20
})
def get_events(start, end, filters=None):
events = []
employee = frappe.db.get_value("Employee", {"user_id": frappe.session.user}, ["name", "company"],
as_dict=True)
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 get_filters_cond
conditions = get_filters_cond("Leave Application", filters, [])
# show department leaves for employee
if "Employee" in frappe.get_roles():
add_department_leaves(events, start, end, employee, company)
add_leaves(events, start, end, conditions)
add_block_dates(events, start, end, employee, company)
add_holidays(events, start, end, employee, company)
return events
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 customer_query(doctype, txt, searchfield, start, page_len, filters):
conditions = []
cust_master_name = frappe.defaults.get_user_default("cust_master_name")
if cust_master_name == "Customer Name":
fields = ["name", "customer_group", "territory"]
else:
fields = ["name", "customer_name", "customer_group", "territory"]
meta = frappe.get_meta("Customer")
searchfields = meta.get_search_fields()
searchfields = searchfields + [f for f in [searchfield or "name", "customer_name"] \
if not f in searchfields]
fields = fields + [f for f in searchfields if not f in fields]
fields = ", ".join(fields)
searchfields = " or ".join([field + " like %(txt)s" for field in searchfields])
return frappe.db.sql("""select {fields} from `tabCustomer`
where docstatus < 2
and ({scond}) and disabled=0
{fcond} {mcond}
order by
if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
if(locate(%(_txt)s, customer_name), locate(%(_txt)s, customer_name), 99999),
idx desc,
name, customer_name
limit %(start)s, %(page_len)s""".format(**{
"fields": fields,
"scond": searchfields,
"mcond": get_match_cond(doctype),
"fcond": get_filters_cond(doctype, filters, conditions).replace('%', '%%'),
}), {
'txt': "%%%s%%" % txt,
'_txt': txt.replace("%", ""),
'start': start,
'page_len': page_len
})
def employee_query(doctype, txt, searchfield, start, page_len, filters):
conditions = []
return frappe.db.sql("""select name, employee_name from `tabEmployee`
where status = 'Active'
and docstatus < 2
and ({key} like %(txt)s
or employee_name like %(txt)s)
{fcond} {mcond}
order by
if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
if(locate(%(_txt)s, employee_name), locate(%(_txt)s, employee_name), 99999),
idx desc,
name, employee_name
limit %(start)s, %(page_len)s""".format(**{
'key': searchfield,
'fcond': get_filters_cond(doctype, filters, conditions),
'mcond': get_match_cond(doctype)
}), {
'txt': "%%%s%%" % txt,
'_txt': txt.replace("%", ""),
'start': start,
'page_len': page_len
})
def get_events(start, end, user=None, for_reminder=False, filters=None):
if not user:
user = frappe.session.user
if isinstance(filters, string_types):
filters = json.loads(filters)
roles = frappe.get_roles(user)
events = frappe.db.sql("""select name, subject, description, color,
starts_on, ends_on, owner, all_day, event_type, repeat_this_event, repeat_on,repeat_till,
monday, tuesday, wednesday, thursday, friday, saturday, sunday
from tabEvent where ((
(date(starts_on) between date(%(start)s) and date(%(end)s))
or (date(ends_on) between date(%(start)s) and date(%(end)s))
or (date(starts_on) <= date(%(start)s) and date(ends_on) >= date(%(end)s))
) or (
date(starts_on) <= date(%(start)s) and repeat_this_event=1 and
ifnull(repeat_till, "3000-01-01") > date(%(start)s)
))
{reminder_condition}
{filter_condition}
and (event_type='Public' or owner=%(user)s
or exists(select name from `tabDocShare` where
tabDocShare.share_doctype="Event" and `tabDocShare`.share_name=tabEvent.name
and tabDocShare.user=%(user)s))
order by starts_on""".format(
filter_condition=get_filters_cond('Event', filters, []),
reminder_condition="and ifnull(send_reminder,0)=1" if for_reminder else "",
roles=", ".join('"{}"'.format(frappe.db.escape(r)) for r in roles)
), {
"start": start,
"end": end,
"user": user,
}, as_dict=1)
# process recurring events
start = start.split(" ")[0]
end = end.split(" ")[0]
add_events = []
remove_events = []
def add_event(e, date):
new_event = e.copy()
enddate = add_days(date,int(date_diff(e.ends_on.split(" ")[0], e.starts_on.split(" ")[0]))) \
if (e.starts_on and e.ends_on) else date
new_event.starts_on = date + " " + e.starts_on.split(" ")[1]
if e.ends_on:
new_event.ends_on = enddate + " " + e.ends_on.split(" ")[1]
add_events.append(new_event)
for e in events:
if e.repeat_this_event:
e.starts_on = get_datetime_str(e.starts_on)
if e.ends_on:
e.ends_on = get_datetime_str(e.ends_on)
event_start, time_str = get_datetime_str(e.starts_on).split(" ")
if cstr(e.repeat_till) == "":
repeat = "3000-01-01"
else:
repeat = e.repeat_till
if e.repeat_on=="Every Year":
start_year = cint(start.split("-")[0])
end_year = cint(end.split("-")[0])
event_start = "-".join(event_start.split("-")[1:])
# repeat for all years in period
for year in range(start_year, end_year+1):
date = str(year) + "-" + event_start
if getdate(date) >= getdate(start) and getdate(date) <= getdate(end) and getdate(date) <= getdate(repeat):
add_event(e, date)
remove_events.append(e)
if e.repeat_on=="Every Month":
date = start.split("-")[0] + "-" + start.split("-")[1] + "-" + event_start.split("-")[2]
# last day of month issue, start from prev month!
try:
getdate(date)
except ValueError:
date = date.split("-")
date = date[0] + "-" + str(cint(date[1]) - 1) + "-" + date[2]
start_from = date
for i in range(int(date_diff(end, start) / 30) + 3):
if getdate(date) >= getdate(start) and getdate(date) <= getdate(end) \
and getdate(date) <= getdate(repeat) and getdate(date) >= getdate(event_start):
add_event(e, date)
date = add_months(start_from, i+1)
remove_events.append(e)
if e.repeat_on=="Every Week":
weekday = getdate(event_start).weekday()
# monday is 0
start_weekday = getdate(start).weekday()
# start from nearest weeday after last monday
date = add_days(start, weekday - start_weekday)
for cnt in range(int(date_diff(end, start) / 7) + 3):
if getdate(date) >= getdate(start) and getdate(date) <= getdate(end) \
and getdate(date) <= getdate(repeat) and getdate(date) >= getdate(event_start):
add_event(e, date)
date = add_days(date, 7)
remove_events.append(e)
if e.repeat_on=="Every Day":
for cnt in range(date_diff(end, start) + 1):
date = add_days(start, cnt)
if getdate(date) >= getdate(event_start) and getdate(date) <= getdate(end) \
and getdate(date) <= getdate(repeat) and e[weekdays[getdate(date).weekday()]]:
add_event(e, date)
remove_events.append(e)
for e in remove_events:
events.remove(e)
events = events + add_events
for e in events:
# remove weekday properties (to reduce message size)
for w in weekdays:
del e[w]
return events
def test_ignore_permissions_for_get_filters_cond(self):
frappe.set_user('*****@*****.**')
self.assertRaises(frappe.PermissionError, get_filters_cond, 'DocType', dict(istable=1), [])
self.assertTrue(get_filters_cond('DocType', dict(istable=1), [], ignore_permissions=True))
frappe.set_user('Administrator')
