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 = dataent.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': "%%%s%%" % dataent.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_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 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 get_conditions(filters):
conditions = []
if filters.get("account"):
lft, rgt = dataent.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")
if filters.get("finance_book"):
if filters.get("include_default_book_entries"):
conditions.append(
"finance_book in (%(finance_book)s, %(company_fb)s)")
else:
conditions.append("finance_book in (%(finance_book)s)")
from dataent.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 query_task(doctype, txt, searchfield, start, page_len, filters):
from dataent.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 dataent.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""" % (dataent.db.escape(searchfield), "%s", "%s",
match_conditions, "%s", dataent.db.escape(searchfield),
"%s", dataent.db.escape(searchfield), "%s", "%s"),
(search_string, search_string, order_by_string, order_by_string, start,
page_len))
def add_leaves(events, start, end, filter_conditions=None):
conditions = []
if not cint(
dataent.db.get_value(
"HR Settings", None,
"show_leaves_of_all_department_members_in_calendar")):
from dataent.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 dataent.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 add_match_conditions(q, tl):
from dataent.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