def query_for_permitted_employees(doctype, txt, searchfield, start, page_len, filters):
txt = "%" + cstr(txt) + "%"
if "Leave Approver" in webnotes.user.get_roles():
condition = """and (exists(select ela.name from `tabEmployee Leave Approver` ela
where ela.parent=`tabEmployee`.name and ela.leave_approver= "%s") or
not exists(select ela.name from `tabEmployee Leave Approver` ela
where ela.parent=`tabEmployee`.name)
or user_id = "%s")""" % (
webnotes.session.user,
webnotes.session.user,
)
else:
from webnotes.widgets.reportview import build_match_conditions
condition = build_match_conditions("Employee")
condition = ("and " + condition) if condition else ""
return webnotes.conn.sql(
"""select name, employee_name from `tabEmployee`
where status = 'Active' and docstatus < 2 and
(`%s` like %s or employee_name like %s) %s
order by
case when name like %s then 0 else 1 end,
case when employee_name like %s then 0 else 1 end,
name limit %s, %s"""
% tuple([searchfield] + ["%s"] * 2 + [condition] + ["%s"] * 4),
(txt, txt, txt, txt, start, page_len),
)
def get_events(start, end, filters=None):
from webnotes.widgets.reportview import build_match_conditions
if not webnotes.has_permission("Task"):
webnotes.msgprint(_("No Permission"), raise_exception=1)
conditions = build_match_conditions("Task")
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 = webnotes.conn.sql("""select name, exp_start_date, exp_end_date,
subject, status, project from `tabTask`
where ((exp_start_date between '%(start)s' and '%(end)s') \
or (exp_end_date between '%(start)s' and '%(end)s'))
%(conditions)s""" % {
"start": start,
"end": end,
"conditions": conditions
},
as_dict=True,
update={"allDay": 0})
return data
def get_events(start, end, doctype,op,filters=None):
webnotes.errprint(['hello',doctype, op])
cnd =''
if op:
cnd = "and encounter = '%(pros)s'"%{"pros":op}
from webnotes.widgets.reportview import build_match_conditions
#if not webnotes.has_permission("Task"):
# webnotes.msgprint(_("No Permission"), raise_exception=1)
conditions = build_match_conditions("Task")
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 = webnotes.conn.sql("""select name, start_time, end_time,
study, status,encounter from `tabPatient Encounter Entry`
where ((start_time between '%(start)s' and '%(end)s') \
or (end_time between '%(start)s' and '%(end)s')) %(cnd)s
%(conditions)s""" % {
"start": start,
"end": end,
"conditions": conditions,
"cnd":cnd
}, as_dict=True, update={"allDay": 0},debug=1)
return data
def query_for_permitted_employees(doctype, txt, searchfield, start, page_len,
filters):
txt = "%" + cstr(txt) + "%"
if "Leave Approver" in webnotes.user.get_roles():
condition = """and (exists(select ela.name from `tabEmployee Leave Approver` ela
where ela.parent=`tabEmployee`.name and ela.leave_approver= "%s") or
not exists(select ela.name from `tabEmployee Leave Approver` ela
where ela.parent=`tabEmployee`.name)
or user_id = "%s")""" % (webnotes.session.user, webnotes.session.user)
else:
from webnotes.widgets.reportview import build_match_conditions
condition = build_match_conditions("Employee")
condition = ("and " + condition) if condition else ""
return webnotes.conn.sql(
"""select name, employee_name from `tabEmployee`
where status = 'Active' and docstatus < 2 and
(`%s` like %s or employee_name like %s) %s
order by
case when name like %s then 0 else 1 end,
case when employee_name like %s then 0 else 1 end,
name limit %s, %s""" %
tuple([searchfield] + ["%s"] * 2 + [condition] + ["%s"] * 4),
(txt, txt, txt, txt, start, page_len))
def get_events(start, end, filters=None):
from webnotes.widgets.reportview import build_match_conditions
if not webnotes.has_permission("Task"):
webnotes.msgprint(_("No Permission"), raise_exception=1)
conditions = build_match_conditions("Task")
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 = webnotes.conn.sql("""select name, exp_start_date, exp_end_date,
subject, status, project from `tabTask`
where ((exp_start_date between '%(start)s' and '%(end)s') \
or (exp_end_date between '%(start)s' and '%(end)s'))
%(conditions)s""" % {
"start": start,
"end": end,
"conditions": conditions
}, as_dict=True, update={"allDay": 0})
return data
def get_events(start, end):
from webnotes.widgets.reportview import build_match_conditions
if not webnotes.has_permission("Time Log"):
webnotes.msgprint(_("No Permission"), raise_exception=1)
match = build_match_conditions("Time Log")
data = webnotes.conn.sql("""select name, from_time, to_time,
activity_type, task, project from `tabTime Log`
where from_time between '%(start)s' and '%(end)s' or to_time between '%(start)s' and '%(end)s'
%(match)s""" % {
"start": start,
"end": end,
"match": match and (" and " + match) or ""
},
as_dict=True,
update={"allDay": 0})
for d in data:
d.title = d.name + ": " + (d.activity_type
or "[Activity Type not set]")
if d.task:
d.title += " for Task: " + d.task
if d.project:
d.title += " for Project: " + d.project
return data
def get_match_cond(doctype, searchfield = 'name'): from webnotes.widgets.reportview import build_match_conditions cond = build_match_conditions(doctype) if cond: cond = ' and ' + cond else: cond = '' return cond
def get_match_cond(doctype, searchfield = 'name'): from webnotes.widgets.reportview import build_match_conditions cond = build_match_conditions(doctype) if cond: cond = ' and ' + cond else: cond = '' return cond
def build_conditions(filters):
conditions = ""
if filters.get("from_date"):
conditions += " and from_time >= %(from_date)s"
if filters.get("to_date"):
conditions += " and to_time <= %(to_date)s"
from webnotes.widgets.reportview import build_match_conditions
match_conditions = build_match_conditions("Time Log")
if match_conditions:
conditions += " and %s" % match_conditions
return conditions
def get_match_cond(doctype, searchfield = 'name'): meta = webnotes.get_doctype(doctype) from webnotes.widgets.search import get_std_fields_list fields = get_std_fields_list(meta, searchfield) from webnotes.widgets.reportview import build_match_conditions cond = build_match_conditions(doctype, fields) if cond: cond = ' and ' + cond else: cond = '' return cond
def build_conditions(filters):
conditions = ""
if filters.get("from_date"):
conditions += " and from_time >= %(from_date)s"
if filters.get("to_date"):
conditions += " and to_time <= %(to_date)s"
from webnotes.widgets.reportview import build_match_conditions
match_conditions = build_match_conditions("Time Log")
if match_conditions:
conditions += " and %s" % match_conditions
return conditions
def get_events(start, end):
events = []
employee = webnotes.conn.get_default("employee", webnotes.session.user)
company = webnotes.conn.get_default("company", webnotes.session.user)
from webnotes.widgets.reportview import build_match_conditions
match_conditions = build_match_conditions("Leave Application")
# show department leaves for employee
if "Employee" in webnotes.get_roles():
add_department_leaves(events, start, end, employee, company)
add_leaves(events, start, end, employee, company, 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 = webnotes.conn.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 webnotes.widgets.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 = webnotes.conn.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 webnotes.widgets.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):
events = []
employee = webnotes.conn.get_default("employee", webnotes.session.user)
company = webnotes.conn.get_default("company", webnotes.session.user)
from webnotes.widgets.reportview import build_match_conditions
match_conditions = build_match_conditions("Leave Application")
# show department leaves for employee
if "Employee" in webnotes.get_roles():
add_department_leaves(events, start, end, employee, company)
add_leaves(events, start, end, employee, company, match_conditions)
add_block_dates(events, start, end, employee, company)
add_holidays(events, start, end, employee, company)
return events
def get_events(start, end):
match = build_match_conditions("Time Log")
data = webnotes.conn.sql("""select name, from_time, to_time,
activity_type, task, project from `tabTime Log`
where from_time between '%(start)s' and '%(end)s' or to_time between '%(start)s' and '%(end)s'
%(match)s""" % {
"start": start,
"end": end,
"match": match and (" and " + match) or ""
}, as_dict=True, update={"allDay": 0})
for d in data:
d.title = d.name + ": " + (d.activity_type or "[Activity Type not set]")
if d.task:
d.title += " for Task: " + d.task
if d.project:
d.title += " for Project: " + d.project
return data
def query_task(doctype, txt, searchfield, start, page_len, filters):
from webnotes.widgets.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 webnotes.conn.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 add_match_conditions(q, tl):
from webnotes.widgets.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_events(start, end):
events = []
employee = webnotes.conn.get_default("employee", webnotes.session.user)
company = webnotes.conn.get_default("company", webnotes.session.user)
from webnotes.widgets.reportview import build_match_conditions
match_conditions = build_match_conditions("Leave Application")
# show department leaves for employee
show_department_leaves = match_conditions and \
len(match_conditions.split("or"))==1 and "employee" in match_conditions
if show_department_leaves:
add_department_leaves(events, start, end, employee, company)
else:
add_leaves(events, start, end, employee, company, match_conditions)
add_block_dates(events, start, end, employee, company)
add_holidays(events, start, end, employee, company)
return events
def get_events(start, end):
match = build_match_conditions("Time Log")
data = webnotes.conn.sql("""select name, from_time, to_time,
activity_type, task, project from `tabTime Log`
where from_time between '%(start)s' and '%(end)s' or to_time between '%(start)s' and '%(end)s'
%(match)s""" % {
"start": start,
"end": end,
"match": match and (" and " + match) or ""
},
as_dict=True,
update={"allDay": 0})
for d in data:
d.title = d.name + ": " + (d.activity_type
or "[Activity Type not set]")
if d.task:
d.title += " for Task: " + d.task
if d.project:
d.title += " for Project: " + d.project
return data
def get_events(start, end):
from webnotes.widgets.reportview import build_match_conditions
if not webnotes.has_permission("Time Log"):
webnotes.msgprint(_("No Permission"), raise_exception=1)
match = build_match_conditions("Time Log")
data = webnotes.conn.sql("""select name, from_time, to_time,
activity_type, task, project from `tabTime Log`
where from_time between '%(start)s' and '%(end)s' or to_time between '%(start)s' and '%(end)s'
%(match)s""" % {
"start": start,
"end": end,
"match": match and (" and " + match) or ""
}, as_dict=True, update={"allDay": 0})
for d in data:
d.title = d.name + ": " + (d.activity_type or "[Activity Type not set]")
if d.task:
d.title += " for Task: " + d.task
if d.project:
d.title += " for Project: " + d.project
return data
def add_match_conditions(q, tl):
from webnotes.widgets.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
