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_events(start, end, filters=None):
from frappe.desk.reportview import build_match_conditions
if not frappe.has_permission("Production Order"):
frappe.msgprint(_("No Permission"), raise_exception=1)
conditions = build_match_conditions("Production Order")
conditions = 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 = frappe.db.sql(
"""select name, production_item, planned_start_date, planned_end_date
from `tabProduction Order`
where ((ifnull(planned_start_date, '0000-00-00')!= '0000-00-00') \
and (planned_start_date between %(start)s and %(end)s) \
or ((ifnull(planned_start_date, '0000-00-00')!= '0000-00-00') \
and planned_end_date between %(start)s and %(end)s)) {conditions}
""".format(conditions=conditions), {
"start": start,
"end": end
},
as_dict=True,
update={"allDay": 0})
return data
def add_leaves(events, start, end, filter_conditions=None):
from frappe.desk.reportview import build_match_conditions
conditions = []
if not cint(
frappe.db.get_value(
"HR Settings", None,
"show_leaves_of_all_department_members_in_calendar")):
match_conditions = build_match_conditions("Leave Application")
if match_conditions:
conditions.append(match_conditions)
query = """SELECT
docstatus,
name,
employee,
employee_name,
leave_type,
from_date,
to_date,
half_day,
status,
color
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 frappe.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,
"all_day":
int(not d.half_day),
"title":
cstr(d.employee_name) + f" ({cstr(d.leave_type)})" +
(" " + _("(Half Day)") if d.half_day else ""),
}
if e not in events:
events.append(e)
def get_conditions(filters):
conditions = []
if filters.get("account"):
lft, rgt = frappe.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("voucher_no"):
conditions.append("voucher_no=%(voucher_no)s")
if filters.get("num_jour"):
conditions.append("(select code_jour from `tabSeries` where name = substring(voucher_no, 1, length(name)) limit 1) = %(num_jour)s")
if filters.get("party_type"):
conditions.append("party_type=%(party_type)s")
if filters.get("party"):
conditions.append("party=%(party)s")
if not (filters.get("account") or filters.get("party") or filters.get("group_by_account")):
conditions.append("posting_date >=%(from_date)s")
conditions.append("posting_date <=%(to_date)s")
if filters.get("project"):
conditions.append("project=%(project)s")
from frappe.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 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 like workstation, project etc.
"""
from frappe.desk.reportview import build_match_conditions
if not frappe.has_permission("Time Log"):
frappe.msgprint(_("No Permission"), raise_exception=1)
conditions = build_match_conditions("Time Log")
conditions = 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 = frappe.db.sql("""select name, from_time, to_time,
activity_type, task, project, production_order, workstation from `tabTime Log`
where docstatus < 2 and ( from_time between %(start)s and %(end)s or to_time between %(start)s and %(end)s )
{conditions}""".format(conditions=conditions), {
"start": start,
"end": end
}, as_dict=True, update={"allDay": 0})
for d in data:
d.title = d.name + ": " + (d.activity_type or d.production_order or "")
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, filters=None):
"""Returns events for Gantt / Calendar view rendering.
:param start: Start date-time.
:param end: End date-time.
:param filters: Filters like workstation, project etc.
"""
from frappe.desk.reportview import build_match_conditions
if not frappe.has_permission("Time Log"):
frappe.msgprint(_("No Permission"), raise_exception=1)
conditions = build_match_conditions("Time Log")
conditions = 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 = frappe.db.sql("""select name, from_time, to_time,
activity_type, task, project, production_order, workstation from `tabTime Log`
where docstatus < 2 and ( from_time between %(start)s and %(end)s or to_time between %(start)s and %(end)s )
{conditions}""".format(conditions=conditions), {
"start": start,
"end": end
}, as_dict=True, update={"allDay": 0})
for d in data:
d.title = d.name + ": " + (d.activity_type or d.production_order or "")
if d.task:
d.title += " for Task: " + d.task
if d.project:
d.title += " for Project: " + d.project
return data
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 = frappe.db.sql("""select email_id as value,
concat(first_name, ifnull(concat(' ',last_name), '' )) as description
from tabContact
where name like %(txt)s
%(condition)s
limit %(page_length)s
""", {'txt': "%%%s%%" % frappe.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_events(start, end):
events = []
employee = frappe.db.get_value("Employee", {"user_id": frappe.session.user}, ["name", "company"],
as_dict=True)
#if not employee:
# return events
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 build_match_conditions
match_conditions = build_match_conditions("Leave Application")
# show department leaves for employee
if "Employee" in frappe.get_roles():
add_department_leaves(events, start, end, employee, company)
add_leaves(events, start, end, 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 = frappe.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 or is_opening = 'Yes')")
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, '') OR finance_book IS NULL)")
else:
conditions.append("finance_book in (%(finance_book)s)")
if not filters.get("show_cancelled_entries"):
conditions.append("is_cancelled = 0")
from frappe.desk.reportview import build_match_conditions
match_conditions = build_match_conditions("GL Entry")
if match_conditions:
conditions.append(match_conditions)
accounting_dimensions = get_accounting_dimensions(as_list=False)
if accounting_dimensions:
for dimension in accounting_dimensions:
if filters.get(dimension.fieldname):
if frappe.get_cached_value('DocType', dimension.document_type, 'is_tree'):
filters[dimension.fieldname] = get_dimension_with_children(dimension.document_type,
filters.get(dimension.fieldname))
conditions.append("{0} in %({0})s".format(dimension.fieldname))
else:
conditions.append("{0} in (%({0})s)".format(dimension.fieldname))
return "and {}".format(" and ".join(conditions)) if conditions else ""
def get_conditions(filters):
conditions = []
if filters.get("account"):
lft, rgt = frappe.db.get_value("Account", filters["account"],
["lft", "rgt"])
conditions.append("""gl.account in (select name from tabAccount
where lft>=%s and rgt<=%s and docstatus<2)""" % (lft, rgt))
if filters.get("voucher_no"):
conditions.append("gl.voucher_no=%(voucher_no)s")
if filters.get("party_type"):
conditions.append("gl.party_type=%(party_type)s")
if filters.get("party"):
conditions.append("gl.party=%(party)s")
if not (filters.get("account") or filters.get("party")
or filters.get("group_by_account")):
conditions.append("gl.posting_date >=%(from_date)s")
if filters.get("project"):
conditions.append("gl.project=%(project)s")
from frappe.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 get_conditions(filters):
conditions = []
if filters.get("account"):
lft, rgt = frappe.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("voucher_no"):
conditions.append("voucher_no=%(voucher_no)s")
if filters.get("party_type"):
conditions.append("party_type=%(party_type)s")
if filters.get("party"):
conditions.append("party=%(party)s")
if not (filters.get("account") or filters.get("party") or filters.get("group_by_account")):
conditions.append("posting_date >=%(from_date)s")
if filters.get("project"):
conditions.append("project=%(project)s")
from frappe.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 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_events(start, end, filters=None):
from frappe.desk.reportview import build_match_conditions
if not frappe.has_permission("Task"):
frappe.msgprint(_("No Permission"), raise_exception=1)
conditions = build_match_conditions("Task")
conditions = 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 = frappe.db.sql("""select name, exp_start_date, exp_end_date,
subject, status, project from `tabTask`
where ((ifnull(exp_start_date, '0000-00-00')!= '0000-00-00') \
and (exp_start_date between %(start)s and %(end)s) \
or ((ifnull(exp_start_date, '0000-00-00')!= '0000-00-00') \
and exp_end_date between %(start)s and %(end)s))
{conditions}""".format(conditions=conditions), {
"start": start,
"end": end
}, as_dict=True, update={"allDay": 0})
return data
def get_events(start, end):
events = []
employee = frappe.db.get_value("Employee",
{"user_id": frappe.session.user},
["name", "company"],
as_dict=True)
if not employee:
return events
employee, company = employee.name, employee.company
from frappe.desk.reportview import build_match_conditions
match_conditions = build_match_conditions("Leave Application")
# show department leaves for employee
if "Employee" in frappe.get_roles():
add_department_leaves(events, start, end, employee, company)
add_leaves(events, start, end, 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 = frappe.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(
"group_by") == "Group by Party" and not filters.get("party_type"):
conditions.append("party_type in ('Customer', 'Supplier')")
conditions.append("party_type='Customer'")
if filters.get("party"):
conditions.append("party in %(party)s")
if not (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 or is_opening = 'Yes')")
from frappe.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 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 = frappe.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': '%' + 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_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_conditions(filters):
conditions = []
if filters.get("account"):
lft, rgt = frappe.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("customer_group"):
conditions.append(
"party in (select name from `tabCustomer` where customer_group ='%s')"
% filters.get("customer_group"))
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")
company_finance_book = erpnext.get_default_finance_book(
filters.get("company"))
if not filters.get("finance_book") or (filters.get("finance_book")
== company_finance_book):
filters['finance_book'] = company_finance_book
conditions.append("ifnull(finance_book, '') in (%(finance_book)s, '')")
elif filters.get("finance_book"):
conditions.append("ifnull(finance_book, '') = %(finance_book)s")
from frappe.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 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 = "`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 build_conditions(filters):
conditions = ""
if filters.get("from_date"):
conditions += " and from_time >= timestamp(%(from_date)s, %(from_time)s)"
if filters.get("to_date"):
conditions += " and to_time <= timestamp(%(to_date)s, %(to_time)s)"
from frappe.desk.reportview import build_match_conditions
match_conditions = build_match_conditions("Time Log")
if match_conditions:
conditions += " and %s" % match_conditions
return conditions
def build_conditions(filters):
conditions = ""
if filters.get("from_date"):
conditions += " and from_time >= timestamp(%(from_date)s, %(from_time)s)"
if filters.get("to_date"):
conditions += " and to_time <= timestamp(%(to_date)s, %(to_time)s)"
from frappe.desk.reportview import build_match_conditions
match_conditions = build_match_conditions("Time Log")
if match_conditions:
conditions += " and %s" % match_conditions
return conditions
def get_plant_list(doctype, txt, searchfield, start, page_len, filters):
fields = ["name", "strain"]
match_conditions = build_match_conditions("Plant")
match_conditions = "and {}".format(match_conditions) if match_conditions else ""
return frappe.db.sql("""select %s from `tabPlant` where docstatus < 2
and (%s like %s or strain like %s)
{match_conditions}
order by
case when name like %s then 0 else 1 end,
case when strain like %s then 0 else 1 end,
name, strain 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 build_match_conditions
if not frappe.has_permission(doctype):
frappe.throw(_("Not Permitted"), frappe.PermissionError)
conditions = build_match_conditions(doctype)
conditions = conditions and (" and " + conditions) or ""
if filters:
filters = json.loads(filters)
for key in filters:
if filters[key]:
conditions += 'and `{0}` = "{1}"'.format(frappe.db.escape(key), frappe.db.escape(filters[key]))
return conditions
def get_conditions(filters):
conditions = []
if filters.get("party_type"):
conditions.append("party_type=%(party_type)s")
else:
conditions.append("party_type in ('Customer', 'Supplier')")
if filters.get("party"):
conditions.append("party=%(party)s")
from frappe.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 get_event_conditions(doctype, filters=None):
"""Returns SQL conditions with user permissions and filters for event queries"""
from frappe.desk.reportview import build_match_conditions
if not frappe.has_permission(doctype):
frappe.throw(_("Not Permitted"), frappe.PermissionError)
conditions = build_match_conditions(doctype)
conditions = conditions and (" and " + conditions) or ""
if filters:
filters = json.loads(filters)
for key in filters:
if filters[key]:
conditions += " and " + key + ' = "' + filters[key].replace('"', '\"') + '"'
return conditions
def get_conditions(filters):
# conditions = "`tabAssign and contribution contract`.status = 'Active'"
conditions = "`tabVehicle Details`.vehicle_status = ('%s')" % filters.get("status")
if filters.get("start_date"):
conditions += " and `tabAssign and contribution contract`.start_date >= date(%(start_date)s)"
if filters.get("end_date"):
conditions += (
" and `tabAssign and contribution contract`.end_date <= date(%(end_date)s)"
)
# if filters.get("status"):
# conditions += "and `tabVehicle Details`.vehicle_status = ('%s')" % filters.get("status")
match_conditions = build_match_conditions("Assign and contribution contract")
if match_conditions:
conditions += " and %s" % match_conditions
return conditions
def get_event_conditions(doctype, filters=None):
"""Returns SQL conditions with user permissions and filters for event queries"""
from frappe.desk.reportview import build_match_conditions
if not frappe.has_permission(doctype):
frappe.throw(_("Not Permitted"), frappe.PermissionError)
conditions = build_match_conditions(doctype)
conditions = conditions and (" and " + conditions) or ""
if filters:
filters = json.loads(filters)
for key in filters:
if filters[key]:
conditions += " and " + key + ' = "' + filters[key].replace(
'"', '\"') + '"'
return conditions
def add_leaves(events, start, end, filter_conditions=None):
conditions = []
if not cint(
frappe.db.get_value(
"HR Settings", None,
"show_leaves_of_all_department_members_in_calendar")):
from frappe.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 frappe.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) +
(' ' + _('(Half Day)') if d.half_day else ''),
}
if e not in events:
events.append(e)
def get_conditions(filters):
conditions = []
if filters.get("account"):
lft, rgt = frappe.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")
company_finance_book = erpnext.get_default_finance_book(filters.get("company"))
if not filters.get("finance_book") or (filters.get("finance_book") == company_finance_book):
filters['finance_book'] = company_finance_book
conditions.append("ifnull(finance_book, '') in (%(finance_book)s, '')")
elif filters.get("finance_book"):
conditions.append("ifnull(finance_book, '') = %(finance_book)s")
from frappe.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 get_customer_list(doctype, txt, searchfield, start, page_len, filters):
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 ""
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 joined_quotation_item_query(doctype, txt, searchfield, start, page_len, filters):
from frappe.desk.reportview import build_match_conditions, get_filters_cond
match_conditions = build_match_conditions("Quotation Item")
match_conditions = "and {}".format(match_conditions) if match_conditions else ""
if filters:
filter_conditions = get_filters_cond(doctype, filters, [])
match_conditions += "{}".format(filter_conditions)
#frappe.msgprint(match_conditions)
txt = "%{}%".format(txt)
return frappe.db.sql("""select name, item_code, master_item
from `tabQuotation Item`
where {key} like %(txt)s
{match_conditions}
limit %(start)s, %(page_len)s""".format(key=searchfield, match_conditions=match_conditions),
dict(start=start, page_len=page_len, txt=txt))
def query_task(doctype, txt, searchfield, start, page_len, filters):
from frappe.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 frappe.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""" %
(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 query_task(doctype, txt, searchfield, start, page_len, filters):
from frappe.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 frappe.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""" %
(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 get_conditions(filters):
conditions = []
# all Customer and Supplier GLs
conditions.append("gl.party_type in ('Customer', 'Supplier')")
if filters.get("consolidation_code"):
conditions.append(
"gl.party in (select party from `tabConsolidation Item` where parent = %(consolidation_code)s)"
)
else:
conditions.append(
"gl.party in (select party from `tabConsolidation Item`)")
from frappe.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 get_conditions(filters):
conditions = []
if filters.get("account"):
lft, rgt = frappe.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("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")):
conditions.append("posting_date >=%(from_date)s")
conditions.append("(posting_date <=%(to_date)s or is_opening = 'Yes')")
from frappe.desk.reportview import build_match_conditions
match_conditions = build_match_conditions("GL Entry")
if match_conditions:
conditions.append(match_conditions)
accounting_dimensions = get_accounting_dimensions(as_list=False)
if accounting_dimensions:
for dimension in accounting_dimensions:
if filters.get(dimension.fieldname):
if frappe.get_cached_value('DocType', dimension.document_type,
'is_tree'):
filters[dimension.fieldname] = get_dimension_with_children(
dimension.document_type,
filters.get(dimension.fieldname))
conditions.append("{0} in %({0})s".format(
dimension.fieldname))
else:
conditions.append("{0} in (%({0})s)".format(
dimension.fieldname))
return "and {}".format(" and ".join(conditions)) if conditions else ""
def get_conditions(filters):
conditions = []
if filters.get("account"):
if not frappe.db.exists("Account", filters["account"]):
frappe.throw(_("Account {0} is not valid").format(filters["account"]))
lft, rgt = frappe.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))
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 frappe.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 get_conditions(filters):
conditions = []
if filters.get("account"):
if not frappe.db.exists("Account", filters["account"]):
frappe.throw(
_("Account {0} is not valid").format(filters["account"]))
lft, rgt = frappe.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))
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 frappe.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 timesheet_conditions(filters):
conds = []
conds.append("`tabTimesheet`.docstatus > 0")
if filters.get("start_date"):
conds.append(
"`tabTimesheet`.start_date >= timestamp(%(start_date)s, %(start_time)s)"
)
if filters.get("end_date"):
conds.append(
"`tabTimesheet`.end_date <= timestamp(%(end_date)s, %(end_time)s)")
if filters.get("project"):
conds.append("`tabTimesheet`.project = %(project)s")
if filters.get("task"):
conds.append(" `tabTimesheet`.task = %(task)s")
match_conditions = build_match_conditions("Timesheet")
if match_conditions:
conds.append("%s" % match_conditions)
if len(conds):
return "WHERE " + " AND ".join(conds)
else:
return ""
def add_match_conditions(q, tl):
from frappe.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
def add_match_conditions(q, tl):
from frappe.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
def get_conditions(filters):
conditions = []
if filters.get("account"):
lft, rgt = frappe.db.get_value("Accounts", filters["account"], ["lft", "rgt"])
conditions.append("""account in (select name from tabAccounts
where lft>=%s and rgt<=%s and docstatus<2)""" % (lft, rgt))
if filters.get("voucher_no"):
conditions.append("voucher_no=%(voucher_no)s")
if filters.get("party"):
conditions.append("party = %(party)s")
conditions.append("posting_date>=%(from_date)s")
conditions.append("posting_date<=%(to_date)s")
from frappe.desk.reportview import build_match_conditions
match_conditions = build_match_conditions("GL Entry")
if match_conditions:
conditions.append(match_conditions)
return "where {}".format(" and ".join(conditions)) if conditions else ""
def add_leaves(events, start, end, filter_conditions=None):
conditions = []
if not cint(frappe.db.get_value("HR Settings", None, "show_leaves_of_all_department_members_in_calendar")):
from frappe.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 frappe.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 build_conditions(filters):
simplified_time_log = cint(frappe.db.get_value("Project Settings", None, "simplified_time_log"))
conditions = ""
if filters.get("from_date"):
if simplified_time_log:
conditions += " and date_worked >= timestamp(%(from_date)s, %(from_time)s)"
else:
conditions += " and from_time >= timestamp(%(from_date)s, %(from_time)s)"
if filters.get("to_date"):
if simplified_time_log:
conditions += " and date_worked <= timestamp(%(to_date)s, %(to_time)s)"
else:
conditions += " and to_time <= timestamp(%(to_date)s, %(to_time)s)"
if filters.get("employee"):
conditions += " and employee = %(employee)s"
from frappe.desk.reportview import build_match_conditions
match_conditions = build_match_conditions("Time Log")
if match_conditions:
conditions += " and %s" % match_conditions
return conditions
def get_conditions(filters):
conditions = []
if filters.get("account"):
lft, rgt = frappe.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"):
voucher_filter_method = filters.get("voucher_filter_method")
if voucher_filter_method == "Posted Against Voucher":
conditions.append("against_voucher=%(voucher_no)s")
elif voucher_filter_method == "Posted By and Against Voucher":
conditions.append(
"voucher_no=%(voucher_no)s or against_voucher=%(voucher_no)s")
else:
conditions.append("voucher_no=%(voucher_no)s")
if filters.get("against_voucher"):
conditions.append("against_voucher=%(against_voucher)s")
if filters.get("reference_no"):
conditions.append("reference_no=%(reference_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 filters.get("account") or filters.get("party") \
or filters.get("group_by") in [_("Group by Account"), _("Group by Party")]:
conditions.append(
"(posting_date <= %(to_date)s or is_opening = 'Yes')")
else:
conditions.append("posting_date between %(from_date)s and %(to_date)s")
if not filters.get('show_opening_entries'):
conditions.append("is_opening != 'Yes'")
if filters.get("project"):
conditions.append("project in %(project)s")
if filters.get("sales_person"):
lft, rgt = frappe.db.get_value("Sales Person",
filters.get("sales_person"),
["lft", "rgt"])
conditions.append(
"""exists(select name from `tabSales Team` steam where
steam.sales_person in (select name from `tabSales Person` where lft >= {0} and rgt <= {1})
and steam.parent = party and steam.parenttype = party_type)""".format(
lft, rgt))
if filters.get("finance_book"):
conditions.append("ifnull(finance_book, '') in (%(finance_book)s, '')")
from frappe.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 get_conditions(filters, party_type=None):
conditions = []
if filters.get("account"):
lft, rgt = frappe.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"):
if filters["party_type"] == 'Customer Group' and filters.get(
"party") and party_type == None:
lft, rgt = frappe.db.get_value("Customer Group",
filters["party"][0], ['lft', 'rgt'])
get_parent_customer_groups = frappe.db.sql(
"""select name from `tabCustomer Group` where lft >= %s and rgt <= %s""",
(lft, rgt),
as_dict=1)
customer_groups = [
"%s" % (frappe.db.escape(d.name))
for d in get_parent_customer_groups
]
if customer_groups:
cond = "and 1=1"
customer_group_condition = ",".join(
['%s'] * len(customer_groups)) % (tuple(customer_groups))
condition = "{0} in ({1})".format(' and customer_group',
customer_group_condition)
cond += condition
customer_list = frappe.db.sql(
""" select name from `tabCustomer` where docstatus < 2 {cond} """
.format(cond=cond),
as_list=1)
if len(customer_list) > 0:
filters["party"] = customer_list
else:
filters["party"] = ['1']
filters["party_type"] = 'Customer'
conditions.append("party_type=%(party_type)s")
else:
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 or is_opening = 'Yes')")
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, '') OR finance_book IS NULL)"
)
else:
conditions.append("finance_book in (%(finance_book)s)")
from frappe.desk.reportview import build_match_conditions
match_conditions = build_match_conditions("GL Entry")
if match_conditions:
conditions.append(match_conditions)
accounting_dimensions = get_accounting_dimensions()
if accounting_dimensions:
for dimension in accounting_dimensions:
if filters.get(dimension):
conditions.append("{0} in (%({0})s)".format(dimension))
return "and {}".format(" and ".join(conditions)) if conditions else ""
