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))
response = frappe.db.sql(
"""select `tabWarehouse`.name,
CONCAT_WS(" : ", "Actual Qty", ifnull( ({sub_query}), 0) ) as actual_qty
from `tabWarehouse`
where
`tabWarehouse`.`{key}` like %(txt)s
{fcond} {mcond}
order by
`tabWarehouse`.name desc
limit
%(start)s, %(page_len)s
""".format(sub_query=sub_query,
key=frappe.db.escape(searchfield),
fcond=get_filters_cond(doctype, filter_dict.get("Warehouse"),
conditions),
mcond=get_match_cond(doctype)), {
"txt": "%%%s%%" % frappe.db.escape(txt),
"start": start,
"page_len": page_len
})
return response
def get_users_for_project(doctype, txt, searchfield, start, page_len, filters):
conditions = []
return frappe.db.sql(
"""select name, concat_ws(' ', first_name, middle_name, last_name)
from `tabUser`
where enabled=1
and name not in ("Guest", "Administrator")
and ({key} like %(txt)s
or full_name like %(txt)s)
{fcond} {mcond}
order by
if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
if(locate(%(_txt)s, full_name), locate(%(_txt)s, full_name), 99999),
idx desc,
name, full_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 se_get_allowed_warehouses(doctype, txt, searchfield, start, page_len,
filters):
conditions = []
wh_map_names = frappe.get_all("SPN User Warehouse Map",
{"name": frappe.session.user})
warehouse_clause = ""
if len(wh_map_names) > 0:
wh_map = frappe.get_doc("SPN User Warehouse Map", wh_map_names[0])
if wh_map and len(wh_map.warehouses) > 0:
warehouse_clause = "and name in (" + ",".join(
[("'" + wh.warehouse + "'") for wh in wh_map.warehouses]) + ")"
return frappe.db.sql(
"""select name, warehouse_name from `tabWarehouse`
where ({key} like %(txt)s or name like %(txt)s) {fcond} {mcond} {whcond}
order by
if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
idx desc, name
limit %(start)s, %(page_len)s""".format(
**{
'key': searchfield,
'fcond': get_filters_cond(doctype, filters, conditions),
'mcond': get_match_cond(doctype),
'whcond': warehouse_clause
}), {
'txt': "%%%s%%" % txt,
'_txt': txt.replace("%", ""),
'start': start,
'page_len': page_len
})
def item_query(doctype, txt, searchfield, start, page_len, filters):
conditions = []
return frappe.db.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,
CASE tabItem.item_group
when "Plumbing" then
concat("<b>DI-IN:</b> ",ifnull(tabItem.diameter_in_inches, 0), " <b>DI-MM:</b> ", ifnull(tabItem.diameter_in_millimeter,0)," <b>TH:</b> ", ifnull(tabItem.thickness,0)," <b>SI:</b> ", ifnull(tabItem.size,0))
when "Pump" then
concat("<b>DI-IN:</b> ",ifnull(tabItem.diameter_in_inches, 0), " <b>DI-MM:</b> ", ifnull(tabItem.diameter_in_millimeter,0)," <b>O-IN:</b> ", ifnull(tabItem.outlet_in_inches,0)," <b>O-MM:</b> ", ifnull(tabItem.outlet_in_millimeter,0)," <b>HP:</b> ", ifnull(tabItem.hp,0)," <b>PH:</b> ", ifnull(tabItem.phase,0)," <b>ST:</b> ", ifnull(tabItem.stage,0))
when "Electrical" or "Unit Component" or "Auto Pump System" then
concat("<b>RR:</b> ",ifnull(tabItem.relay_range, 0), " <b>SI:</b>",ifnull(tabItem.size,0))
else
concat(" ")
END as phase
from tabItem
where tabItem.docstatus < 2
and ifnull(tabItem.has_variants, 0)=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_name LIKE %(txt)s
or tabItem.description LIKE %(txt)s
or tabItem.variant_of_item LIKE %(txt)s
or tabItem.diameter_in_inches LIKE %(txt)s
or tabItem.outlet_in_inches LIKE %(txt)s
or tabItem.stage LIKE %(txt)s
or tabItem.thickness LIKE %(txt)s
or tabItem.size LIKE %(txt)s
or tabItem.diameter_in_millimeter LIKE %(txt)s
or tabItem.outlet_in_millimeter LIKE %(txt)s
or tabItem.phase LIKE %(txt)s
or tabItem.hp LIKE %(txt)s
or tabItem.relay_range 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)),
{
"today": nowdate(),
"txt": "%%%s%%" % txt,
"_txt": txt.replace("%", ""),
"start": start,
"page_len": page_len
})
def kp_sinv_item_query(doctype, txt, searchfield, start, page_len, filters=None):
kp_filters = filters
filters = {}
conditions = []
x = None
try:
x = frappe.db.sql("""select tabItem.name,tabItem.item_group,
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 INNER JOIN `tabItem Customer Detail` AS B ON tabItem.name = B.parent
where
tabItem.excise_chapter = '{exc}' AND B.customer_name = '{cn}'
and 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_group LIKE %(txt)s
or tabItem.item_name LIKE %(txt)s
or tabItem.description LIKE %(txt)s)
{fcond} {mcond}
order by
if(locate(%(_txt)s, tabItem.name), locate(%(_txt)s, tabItem.name), 99999),
if(locate(%(_txt)s, tabItem.item_name), locate(%(_txt)s, tabItem.item_name), 99999),
tabItem.idx desc,
tabItem.name, tabItem.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('%', '%%'),
exc=kp_filters.get("excise_chapter"), cn=kp_filters.get("cust_name")), {
"today": nowdate(),
"txt": "%%%s%%" % txt,
"_txt": txt.replace("%", ""),
"start": start,
"page_len": page_len
})
except Exception, e:
frappe.msgprint(e)
def get_students():
conditions = []
return frappe.db.sql(
"""select first_name,middle_name)
from `tabStudent`
where enabled=1
{fcond} {mcond}
order by
name, full_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_users_for_project(doctype, txt, searchfield, start, page_len, filters):
conditions = []
return frappe.db.sql("""select name, concat_ws(' ', first_name, middle_name, last_name)
from `tabUser`
where enabled=1
and name not in ("Guest", "Administrator")
and ({key} like %(txt)s
or full_name like %(txt)s)
{fcond} {mcond}
order by
if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
if(locate(%(_txt)s, full_name), locate(%(_txt)s, full_name), 99999),
idx desc,
name, full_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
})
