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=dataent.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=dataent.db.escape('%{0}%'.format(txt)))
return dataent.db.sql(query)
def get_event_conditions(doctype, filters=None):
"""Returns SQL conditions with user permissions and filters for event queries"""
from dataent.desk.reportview import get_filters_cond
if not dataent.has_permission(doctype):
dataent.throw(_("Not Permitted"), dataent.PermissionError)
return get_filters_cond(doctype, filters, [], with_match_conditions=True)
def get_events(start, end, filters=None):
events = []
employee = dataent.db.get_value("Employee",
{"user_id": dataent.session.user},
["name", "company"],
as_dict=True)
if employee:
employee, company = employee.name, employee.company
else:
employee = ''
company = dataent.db.get_value("Global Defaults", None,
"default_company")
from dataent.desk.reportview import get_filters_cond
conditions = get_filters_cond("Leave Application", filters, [])
# show department leaves for employee
if "Employee" in dataent.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 = []
return dataent.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_delivery_notes_to_be_billed(doctype, txt, searchfield, start, page_len,
filters, as_dict):
return dataent.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 get_customer_list(doctype,
txt,
searchfield,
start,
page_len,
filters=None):
if dataent.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 dataent.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 test_ignore_permissions_for_get_filters_cond(self):
dataent.set_user('*****@*****.**')
self.assertRaises(dataent.PermissionError, get_filters_cond, 'DocType',
dict(istable=1), [])
self.assertTrue(
get_filters_cond('DocType',
dict(istable=1), [],
ignore_permissions=True))
dataent.set_user('Administrator')
def get_events(start, end, filters=None):
events = []
employee = dataent.db.get_value("Employee", {"user_id": dataent.session.user})
if not employee:
return events
from dataent.desk.reportview import get_filters_cond
conditions = get_filters_cond("Attendance", filters, [])
add_attendance(events, start, end, conditions=conditions)
return events
def item_query(doctype,
txt,
searchfield,
start,
page_len,
filters,
as_dict=False):
conditions = []
description_cond = ''
if dataent.db.count('Item', cache=True) < 50000:
# scan description only if items are less than 50000
description_cond = 'or tabItem.description LIKE %(txt)s'
return dataent.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.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_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,
"_txt": txt.replace("%", ""),
"start": start,
"page_len": page_len
},
as_dict=as_dict)
def customer_query(doctype, txt, searchfield, start, page_len, filters):
conditions = []
cust_master_name = dataent.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 = dataent.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 dataent.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 get_events(start, end, filters=None):
events = []
employee = dataent.db.get_value("Employee",
{"user_id": dataent.session.user},
["name", "company"],
as_dict=True)
if employee:
employee, company = employee.name, employee.company
else:
employee = ''
company = dataent.db.get_value("Global Defaults", None,
"default_company")
from dataent.desk.reportview import get_filters_cond
conditions = get_filters_cond("Shift Assignment", filters, [])
add_assignments(events, start, end, conditions=conditions)
return events
def bom(doctype, txt, searchfield, start, page_len, filters):
conditions = []
return dataent.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=dataent.db.escape(searchfield)),
{
'txt': "%%%s%%" % dataent.db.escape(txt),
'_txt': txt.replace("%", ""),
'start': start or 0,
'page_len': page_len or 20
})