def get_batch_no(doctype, txt, searchfield, start, page_len, filters):
from controllers.queries import get_match_cond
if filters.has_key('warehouse'):
return webnotes.conn.sql("""select batch_no from `tabStock Ledger Entry` sle
where item_code = '%(item_code)s'
and warehouse = '%(warehouse)s'
and batch_no like '%(txt)s'
and exists(select * from `tabBatch`
where name = sle.batch_no
and (ifnull(expiry_date, '')='' or expiry_date >= '%(posting_date)s')
and docstatus != 2)
%(mcond)s
group by batch_no having sum(actual_qty) > 0
order by batch_no desc
limit %(start)s, %(page_len)s """ % {'item_code': filters['item_code'],
'warehouse': filters['warehouse'], 'posting_date': filters['posting_date'],
'txt': "%%%s%%" % txt, 'mcond':get_match_cond(doctype, searchfield),
'start': start, 'page_len': page_len})
else:
return webnotes.conn.sql("""select name from tabBatch
where docstatus != 2
and item = '%(item_code)s'
and (ifnull(expiry_date, '')='' or expiry_date >= '%(posting_date)s')
and name like '%(txt)s'
%(mcond)s
order by name desc
limit %(start)s, %(page_len)s""" % {'item_code': filters['item_code'],
'posting_date': filters['posting_date'], 'txt': "%%%s%%" % txt,
'mcond':get_match_cond(doctype, searchfield),'start': start,
'page_len': page_len})
def customer_query(doctype, txt, searchfield, start, page_len, filters):
cust_master_name = webnotes.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 = ", ".join(fields)
return webnotes.conn.sql(
"""select %(field)s from `tabCustomer`
where docstatus < 2
and (%(key)s like "%(txt)s"
or customer_name like "%(txt)s")
%(mcond)s
order by
if(locate("%(_txt)s", name), locate("%(_txt)s", name), 99999),
if(locate("%(_txt)s", customer_name), locate("%(_txt)s", name), 99999),
name, customer_name
limit %(start)s, %(page_len)s""" % {
'field': fields,
'key': searchfield,
'txt': "%%%s%%" % txt,
'_txt': txt.replace("%", ""),
'mcond': get_match_cond(doctype, searchfield),
'start': start,
'page_len': page_len
})
def customer_query(doctype, txt, searchfield, start, page_len, filters):
cust_master_name = webnotes.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 = ", ".join(fields)
return webnotes.conn.sql(
"""select %(field)s from `tabCustomer`
where docstatus < 2
and (%(key)s like "%(txt)s"
or customer_name like "%(txt)s")
%(mcond)s
order by
if(locate("%(_txt)s", name), locate("%(_txt)s", name), 99999),
if(locate("%(_txt)s", customer_name), locate("%(_txt)s", name), 99999),
name, customer_name
limit %(start)s, %(page_len)s"""
% {
"field": fields,
"key": searchfield,
"txt": "%%%s%%" % txt,
"_txt": txt.replace("%", ""),
"mcond": get_match_cond(doctype, searchfield),
"start": start,
"page_len": page_len,
}
)
def item_query(doctype, txt, searchfield, start, page_len, filters):
from webnotes.utils import nowdate
conditions = []
return webnotes.conn.sql(
"""select tabItem.name,
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 (ifnull(tabItem.end_of_life, '') = '' or tabItem.end_of_life > %(today)s)
and (tabItem.`{key}` LIKE %(txt)s
or tabItem.item_name 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),
name, item_name
limit %(start)s, %(page_len)s """.format(
key=searchfield,
fcond=get_filters_cond(doctype, filters, conditions),
mcond=get_match_cond(doctype, searchfield)), {
"today": nowdate(),
"txt": "%%%s%%" % txt,
"_txt": txt.replace("%", ""),
"start": start,
"page_len": page_len
})
def item_query(doctype, txt, searchfield, start, page_len, filters):
from webnotes.utils import nowdate
conditions = []
return webnotes.conn.sql(
"""select tabItem.name,
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 (ifnull(tabItem.end_of_life, '') = '' or tabItem.end_of_life > %(today)s)
and (tabItem.`{key}` LIKE %(txt)s
or tabItem.item_name 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),
name, item_name
limit %(start)s, %(page_len)s """.format(
key=searchfield,
fcond=get_filters_cond(doctype, filters, conditions),
mcond=get_match_cond(doctype, searchfield),
),
{"today": nowdate(), "txt": "%%%s%%" % txt, "_txt": txt.replace("%", ""), "start": start, "page_len": page_len},
)
def profile_query(doctype, txt, searchfield, start, page_len, filters):
from webnotes.widgets.reportview import get_match_cond
return webnotes.conn.sql(
"""select name, concat_ws(' ', first_name, middle_name, last_name)
from `tabProfile`
where ifnull(enabled, 0)=1
and docstatus < 2
and name not in ('Administrator', 'Guest')
and user_type != 'Website User'
and (%(key)s like "%(txt)s"
or concat_ws(' ', first_name, middle_name, last_name) like "%(txt)s")
%(mcond)s
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 %(start)s, %(page_len)s"""
% {
"key": searchfield,
"txt": "%%%s%%" % txt,
"mcond": get_match_cond(doctype, searchfield),
"start": start,
"page_len": page_len,
}
)
def get_delivery_notes_to_be_billed(doctype, txt, searchfield, start, page_len,
filters):
return webnotes.conn.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 %(fcond)s and
(ifnull((select sum(qty) from `tabDelivery Note Item` where
`tabDelivery Note Item`.parent=`tabDelivery Note`.name), 0) >
ifnull((select sum(qty) from `tabSales Invoice Item` where
`tabSales Invoice Item`.docstatus = 1 and
`tabSales Invoice Item`.delivery_note=`tabDelivery Note`.name), 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 supplier_query(doctype, txt, searchfield, start, page_len, filters):
supp_master_name = webnotes.defaults.get_user_default("supp_master_name")
if supp_master_name == "Supplier Name":
fields = ["name", "supplier_type"]
else:
fields = ["name", "supplier_name", "supplier_type"]
fields = ", ".join(fields)
return webnotes.conn.sql(
"""select %(field)s from `tabSupplier`
where docstatus < 2
and (%(key)s like "%(txt)s"
or supplier_name like "%(txt)s")
%(mcond)s
order by
case when name like "%(txt)s" then 0 else 1 end,
case when supplier_name like "%(txt)s" then 0 else 1 end,
name, supplier_name
limit %(start)s, %(page_len)s """ % {
'field': fields,
'key': searchfield,
'txt': "%%%s%%" % txt,
'mcond': get_match_cond(doctype, searchfield),
'start': start,
'page_len': page_len
})
def supplier_query(doctype, txt, searchfield, start, page_len, filters):
supp_master_name = webnotes.defaults.get_user_default("supp_master_name")
if supp_master_name == "Supplier Name":
fields = ["name", "supplier_type"]
else:
fields = ["name", "supplier_name", "supplier_type"]
fields = ", ".join(fields)
return webnotes.conn.sql(
"""select %(field)s from `tabSupplier`
where docstatus < 2
and (%(key)s like "%(txt)s"
or supplier_name like "%(txt)s")
%(mcond)s
order by
case when name like "%(txt)s" then 0 else 1 end,
case when supplier_name like "%(txt)s" then 0 else 1 end,
name, supplier_name
limit %(start)s, %(page_len)s """
% {
"field": fields,
"key": searchfield,
"txt": "%%%s%%" % txt,
"mcond": get_match_cond(doctype, searchfield),
"start": start,
"page_len": page_len,
}
)
def employee_query(doctype, txt, searchfield, start, page_len, filters):
conditions = []
return webnotes.conn.sql(
"""select name, employee_name from `tabEmployee`
where status = 'Active'
and docstatus < 2
and (%(key)s like "%(txt)s"
or employee_name like "%(txt)s")
%(fcond)s %(mcond)s
order by
case when name like "%(txt)s" then 0 else 1 end,
case when employee_name like "%(txt)s" then 0 else 1 end,
name
limit %(start)s, %(page_len)s"""
% {
"key": searchfield,
"txt": "%%%s%%" % txt,
"fcond": get_filters_cond(doctype, filters, conditions),
"mcond": get_match_cond(doctype, searchfield),
"start": start,
"page_len": page_len,
}
)
def get_project_name(doctype, txt, searchfield, start, page_len, filters):
cond = ''
if filters['customer']:
cond = '(`tabProject`.customer = "' + filters['customer'] + '" or ifnull(`tabProject`.customer,"")="") and'
return webnotes.conn.sql("""select `tabProject`.name from `tabProject`
where `tabProject`.status not in ("Completed", "Cancelled")
and %(cond)s `tabProject`.name like "%(txt)s" %(mcond)s
order by `tabProject`.name asc
limit %(start)s, %(page_len)s """ % {'cond': cond,'txt': "%%%s%%" % txt,
'mcond':get_match_cond(doctype, searchfield),'start': start, 'page_len': page_len})
def bom(doctype, txt, searchfield, start, page_len, filters):
conditions = []
return webnotes.conn.sql("""select tabBOM.name, tabBOM.item
from tabBOM
where tabBOM.docstatus=1
and tabBOM.is_active=1
and tabBOM.%(key)s like "%(txt)s"
%(fcond)s %(mcond)s
limit %(start)s, %(page_len)s """ % {'key': searchfield, 'txt': "%%%s%%" % txt,
'fcond': get_filters_cond(doctype, filters, conditions),
'mcond':get_match_cond(doctype, searchfield), 'start': start, 'page_len': page_len})
def item_query(doctype, txt, searchfield, start, page_len, filters):
if filters.get("from"):
from webnotes.widgets.reportview import get_match_cond
filters.update({
"txt": txt,
"mcond": get_match_cond(filters["from"], searchfield),
"start": start,
"page_len": page_len
})
return webnotes.conn.sql("""select item_code from `tab%(from)s`
where parent='%(parent)s' and docstatus < 2 and item_code like '%%%(txt)s%%' %(mcond)s
order by item_code limit %(start)s, %(page_len)s""" % filters)
def employee_query(doctype, txt, searchfield, start, page_len, filters):
return webnotes.conn.sql("""select name, employee_name from `tabEmployee`
where status = 'Active'
and docstatus < 2
and (%(key)s like "%(txt)s"
or employee_name like "%(txt)s")
%(mcond)s
order by
case when name like "%(txt)s" then 0 else 1 end,
case when employee_name like "%(txt)s" then 0 else 1 end,
name
limit %(start)s, %(page_len)s""" % {'key': searchfield, 'txt': "%%%s%%" % txt,
'mcond':get_match_cond(doctype, searchfield), 'start': start, 'page_len': page_len})
def employee_query(doctype, txt, searchfield, start, page_len, filters):
return webnotes.conn.sql("""select name, employee_name from `tabEmployee`
where status = 'Active'
and docstatus < 2
and (%(key)s like "%(txt)s"
or employee_name like "%(txt)s")
%(mcond)s
order by
case when name like "%(txt)s" then 0 else 1 end,
case when employee_name like "%(txt)s" then 0 else 1 end,
name
limit %(start)s, %(page_len)s""" % {'key': searchfield, 'txt': "%%%s%%" % txt,
'mcond':get_match_cond(doctype, searchfield), 'start': start, 'page_len': page_len})
def employee_query(doctype, txt, searchfield, start, page_len, filters):
return webnotes.conn.sql("""select name, employee_name from `tabEmployee`
where status = 'Active'
and docstatus < 2
and (%(key)s like "%(txt)s"
or employee_name like "%(txt)s")
%(mcond)s
order by
if(locate("%(_txt)s", name), locate("%(_txt)s", name), 99999),
if(locate("%(_txt)s", employee_name), locate("%(_txt)s", employee_name), 99999),
name, employee_name
limit %(start)s, %(page_len)s""" % {'key': searchfield, 'txt': "%%%s%%" % txt,
'_txt': txt.replace("%", ""),
'mcond':get_match_cond(doctype, searchfield), 'start': start, 'page_len': page_len})
def lead_query(doctype, txt, searchfield, start, page_len, filters):
return webnotes.conn.sql("""select name, lead_name, company_name from `tabLead`
where docstatus < 2
and ifnull(status, '') != 'Converted'
and (%(key)s like "%(txt)s"
or lead_name like "%(txt)s"
or company_name like "%(txt)s")
%(mcond)s
order by
case when name like "%(txt)s" then 0 else 1 end,
case when lead_name like "%(txt)s" then 0 else 1 end,
case when company_name like "%(txt)s" then 0 else 1 end,
lead_name asc
limit %(start)s, %(page_len)s""" % {'key': searchfield, 'txt': "%%%s%%" % txt,
'mcond':get_match_cond(doctype, searchfield), 'start': start, 'page_len': page_len})
def lead_query(doctype, txt, searchfield, start, page_len, filters):
return webnotes.conn.sql("""select name, lead_name, company_name from `tabLead`
where docstatus < 2
and ifnull(status, '') != 'Converted'
and (%(key)s like "%(txt)s"
or lead_name like "%(txt)s"
or company_name like "%(txt)s")
%(mcond)s
order by
if(locate("%(_txt)s", name), locate("%(_txt)s", name), 99999),
if(locate("%(_txt)s", lead_name), locate("%(_txt)s", lead_name), 99999),
if(locate("%(_txt)s", company_name), locate("%(_txt)s", company_name), 99999),
name, lead_name
limit %(start)s, %(page_len)s""" % {'key': searchfield, 'txt': "%%%s%%" % txt,
'_txt': txt.replace("%", ""),
'mcond':get_match_cond(doctype, searchfield), 'start': start, 'page_len': page_len})
def item_query(doctype, txt, searchfield, start, page_len, filters):
conditions = []
return webnotes.conn.sql("""select tabItem.name,
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.%(key)s LIKE "%(txt)s"
or tabItem.item_name LIKE "%(txt)s")
%(fcond)s %(mcond)s
limit %(start)s,%(page_len)s """ % {'key': searchfield, 'txt': "%%%s%%" % txt,
'fcond': get_filters_cond(doctype, filters, conditions),
'mcond': get_match_cond(doctype, searchfield), 'start': start, 'page_len': page_len})
def item_query(doctype, txt, searchfield, start, page_len, filters):
conditions = []
return webnotes.conn.sql("""select tabItem.name,
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.%(key)s LIKE "%(txt)s"
or tabItem.item_name LIKE "%(txt)s")
%(fcond)s %(mcond)s
limit %(start)s,%(page_len)s """ % {'key': searchfield, 'txt': "%%%s%%" % txt,
'fcond': get_filters_cond(doctype, filters, conditions),
'mcond': get_match_cond(doctype, searchfield), 'start': start, 'page_len': page_len})
def get_delivery_notes_to_be_billed(doctype, txt, searchfield, start, page_len, filters):
return webnotes.conn.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 %(fcond)s and
(ifnull((select sum(qty) from `tabDelivery Note Item` where
`tabDelivery Note Item`.parent=`tabDelivery Note`.name), 0) >
ifnull((select sum(qty) from `tabSales Invoice Item` where
`tabSales Invoice Item`.docstatus = 1 and
`tabSales Invoice Item`.delivery_note=`tabDelivery Note`.name), 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 profile_query(doctype, txt, searchfield, start, page_len, filters):
from webnotes.widgets.reportview import get_match_cond
return webnotes.conn.sql("""select name, concat_ws(' ', first_name, middle_name, last_name)
from `tabProfile`
where ifnull(enabled, 0)=1
and docstatus < 2
and name not in ('Administrator', 'Guest')
and user_type != 'Website User'
and (%(key)s like "%(txt)s"
or concat_ws(' ', first_name, middle_name, last_name) like "%(txt)s")
%(mcond)s
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 %(start)s, %(page_len)s""" % {'key': searchfield, 'txt': "%%%s%%" % txt,
'mcond':get_match_cond(doctype, searchfield), 'start': start, 'page_len': page_len})
def get_project_name(doctype, txt, searchfield, start, page_len, filters):
cond = ""
if filters["customer"]:
cond = '(`tabProject`.customer = "' + filters["customer"] + '" or ifnull(`tabProject`.customer,"")="") and'
return webnotes.conn.sql(
"""select `tabProject`.name from `tabProject`
where `tabProject`.status not in ("Completed", "Cancelled")
and %(cond)s `tabProject`.name like "%(txt)s" %(mcond)s
order by `tabProject`.name asc
limit %(start)s, %(page_len)s """
% {
"cond": cond,
"txt": "%%%s%%" % txt,
"mcond": get_match_cond(doctype, searchfield),
"start": start,
"page_len": page_len,
}
)
def employee_query(doctype, txt, searchfield, start, page_len, filters):
return webnotes.conn.sql(
"""select name, employee_name from `tabEmployee`
where status = 'Active'
and docstatus < 2
and (%(key)s like "%(txt)s"
or employee_name like "%(txt)s")
%(mcond)s
order by
if(locate("%(_txt)s", name), locate("%(_txt)s", name), 99999),
if(locate("%(_txt)s", employee_name), locate("%(_txt)s", item_name), 99999),
name, employee_name
limit %(start)s, %(page_len)s""" % {
'key': searchfield,
'txt': "%%%s%%" % txt,
'_txt': txt.replace("%", ""),
'mcond': get_match_cond(doctype, searchfield),
'start': start,
'page_len': page_len
})
def bom(doctype, txt, searchfield, start, page_len, filters):
conditions = []
return webnotes.conn.sql(
"""select tabBOM.name, tabBOM.item
from tabBOM
where tabBOM.docstatus=1
and tabBOM.is_active=1
and tabBOM.%(key)s like "%(txt)s"
%(fcond)s %(mcond)s
limit %(start)s, %(page_len)s """
% {
"key": searchfield,
"txt": "%%%s%%" % txt,
"fcond": get_filters_cond(doctype, filters, conditions),
"mcond": get_match_cond(doctype, searchfield),
"start": start,
"page_len": page_len,
}
)
def supplier_query(doctype, txt, searchfield, start, page_len, filters):
supp_master_name = webnotes.defaults.get_user_default("supp_master_name")
if supp_master_name == "Supplier Name":
fields = ["name", "supplier_type"]
else:
fields = ["name", "supplier_name", "supplier_type"]
fields = ", ".join(fields)
return webnotes.conn.sql("""select %(field)s from `tabSupplier`
where docstatus < 2
and (%(key)s like "%(txt)s"
or supplier_name like "%(txt)s")
%(mcond)s
order by
if(locate("%(_txt)s", name), locate("%(_txt)s", name), 99999),
if(locate("%(_txt)s", supplier_name), locate("%(_txt)s", supplier_name), 99999),
name, supplier_name
limit %(start)s, %(page_len)s """ % {'field': fields,'key': searchfield,
'txt': "%%%s%%" % txt, '_txt': txt.replace("%", ""),
'mcond':get_match_cond(doctype, searchfield), 'start': start,
'page_len': page_len})
def customer_query(doctype, txt, searchfield, start, page_len, filters):
cust_master_name = webnotes.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 = ", ".join(fields)
return webnotes.conn.sql("""select %(field)s from `tabCustomer`
where docstatus < 2
and (%(key)s like "%(txt)s"
or customer_name like "%(txt)s")
%(mcond)s
order by
case when name like "%(txt)s" then 0 else 1 end,
case when customer_name like "%(txt)s" then 0 else 1 end,
name, customer_name
limit %(start)s, %(page_len)s""" % {'field': fields,'key': searchfield,
'txt': "%%%s%%" % txt, 'mcond':get_match_cond(doctype, searchfield),
'start': start, 'page_len': page_len})
def lead_query(doctype, txt, searchfield, start, page_len, filters):
return webnotes.conn.sql(
"""select name, lead_name, company_name from `tabLead`
where docstatus < 2
and ifnull(status, '') != 'Converted'
and (%(key)s like "%(txt)s"
or lead_name like "%(txt)s"
or company_name like "%(txt)s")
%(mcond)s
order by
if(locate("%(_txt)s", name), locate("%(_txt)s", name), 99999),
if(locate("%(_txt)s", lead_name), locate("%(_txt)s", name), 99999),
if(locate("%(_txt)s", company_name), locate("%(_txt)s", name), 99999),
name, lead_name
limit %(start)s, %(page_len)s""" % {
'key': searchfield,
'txt': "%%%s%%" % txt,
'_txt': txt.replace("%", ""),
'mcond': get_match_cond(doctype, searchfield),
'start': start,
'page_len': page_len
})
