def get_batch_no(doctype, txt, searchfield, start, page_len, filters):
from erpnext.controllers.queries import get_match_cond
if filters.has_key('warehouse'):
return frappe.db.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),
'start': start, 'page_len': page_len})
else:
return frappe.db.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),'start': start,
'page_len': page_len})
def get_batch_no(doctype, txt, searchfield, start, page_len, filters):
cond = ""
if filters.get("posting_date"):
cond = "and (ifnull(batch.expiry_date, '')='' or batch.expiry_date >= %(posting_date)s)"
batch_nos = None
args = {
"item_code": filters.get("item_code"),
"warehouse": filters.get("warehouse"),
"posting_date": filters.get("posting_date"),
"txt": "%{0}%".format(txt),
"start": start,
"page_len": page_len,
}
if args.get("warehouse"):
batch_nos = frappe.db.sql(
"""select sle.batch_no, round(sum(sle.actual_qty),2), sle.stock_uom, batch.expiry_date
from `tabStock Ledger Entry` sle
INNER JOIN `tabBatch` batch on sle.batch_no = batch.name
where
sle.item_code = %(item_code)s
and sle.warehouse = %(warehouse)s
and sle.batch_no like %(txt)s
and batch.docstatus < 2
{0}
{match_conditions}
group by batch_no having sum(sle.actual_qty) > 0
order by batch.expiry_date, sle.batch_no desc
limit %(start)s, %(page_len)s""".format(
cond, match_conditions=get_match_cond(doctype)
),
args,
)
if batch_nos:
return batch_nos
else:
return frappe.db.sql(
"""select name, expiry_date from `tabBatch` batch
where item = %(item_code)s
and name like %(txt)s
and docstatus < 2
{0}
{match_conditions}
order by expiry_date, name desc
limit %(start)s, %(page_len)s""".format(
cond, match_conditions=get_match_cond(doctype)
),
args,
)
def supplier_query(doctype, txt, searchfield, start, page_len, filters):
supp_master_name = frappe.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 frappe.db.sql("""select {field} from `tabSupplier`
where docstatus < 2
and ({key} like %(txt)s
or supplier_name like %(txt)s) and disabled=0
{mcond}
order by
if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
if(locate(%(_txt)s, supplier_name), locate(%(_txt)s, supplier_name), 99999),
idx desc,
name, supplier_name
limit %(start)s, %(page_len)s """.format(**{
'field': fields,
'key': searchfield,
'mcond':get_match_cond(doctype)
}), {
'txt': "%%%s%%" % txt,
'_txt': txt.replace("%", ""),
'start': start,
'page_len': page_len
})
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 item_query(doctype, txt, searchfield, start, page_len, filters, as_dict=False):
conditions = []
return frappe.db.sql("""select tabItem.name, tabItem.item_group, tabItem.image,
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.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, name), locate(%(_txt)s, name), 99999),
if(locate(%(_txt)s, item_name), locate(%(_txt)s, item_name), 99999),
idx desc,
name, 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('%', '%%')),
{
"today": nowdate(),
"txt": "%%%s%%" % txt,
"_txt": txt.replace("%", ""),
"start": start,
"page_len": page_len
}, as_dict=as_dict)
def item_query(doctype, txt, searchfield, start, page_len, filters):
if filters.get("from"):
from frappe.desk.reportview import get_match_cond
mcond = get_match_cond(filters["from"])
cond, qi_condition = "", "and (quality_inspection is null or quality_inspection = '')"
if filters.get('from') in ['Purchase Invoice Item', 'Purchase Receipt Item']:
cond = """and item_code in (select name from `tabItem` where
inspection_required_before_purchase = 1)"""
elif filters.get('from') in ['Sales Invoice Item', 'Delivery Note Item']:
cond = """and item_code in (select name from `tabItem` where
inspection_required_before_delivery = 1)"""
elif filters.get('from') == 'Stock Entry Detail':
cond = """and s_warehouse is null"""
if filters.get('from') in ['Supplier Quotation Item']:
qi_condition = ""
return frappe.db.sql(""" select item_code from `tab{doc}`
where parent=%(parent)s and docstatus < 2 and item_code like %(txt)s
{qi_condition} {cond} {mcond}
order by item_code limit {start}, {page_len}""".format(doc=filters.get('from'),
parent=filters.get('parent'), cond = cond, mcond = mcond, start = start,
page_len = page_len, qi_condition = qi_condition),
{'parent': filters.get('parent'), 'txt': "%%%s%%" % txt})
def customer_query(doctype, txt, searchfield, start, page_len, filters):
cust_master_name = frappe.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"]
meta = frappe.get_meta("Customer")
fields = fields + [f for f in meta.get_search_fields() if not f in fields]
fields = ", ".join(fields)
return frappe.db.sql("""select {fields} from `tabCustomer`
where docstatus < 2
and ({key} like %(txt)s
or customer_name like %(txt)s) and disabled=0
{mcond}
order by
if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
if(locate(%(_txt)s, customer_name), locate(%(_txt)s, customer_name), 99999),
idx desc,
name, customer_name
limit %(start)s, %(page_len)s""".format(**{
"fields": fields,
"key": searchfield,
"mcond": get_match_cond(doctype)
}), {
'txt': "%%%s%%" % txt,
'_txt': txt.replace("%", ""),
'start': start,
'page_len': page_len
})
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, ignore_permissions=True))
query = """select `tabWarehouse`.name,
CONCAT_WS(" : ", "Actual Qty", ifnull( ({sub_query}), 0) ) as actual_qty
from `tabWarehouse`
where
`tabWarehouse`.`{key}` like '{txt}'
{fcond} {mcond}
order by
`tabWarehouse`.name desc
limit
{start}, {page_len}
""".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),
start=start,
page_len=page_len,
txt=frappe.db.escape('%{0}%'.format(txt))
)
return frappe.db.sql(query)
def get_income_account(doctype, txt, searchfield, start, page_len, filters):
from erpnext.controllers.queries import get_match_cond
# income account can be any Credit account,
# but can also be a Asset account with account_type='Income Account' in special circumstances.
# Hence the first condition is an "OR"
if not filters:
filters = {}
condition = ""
if filters.get("company"):
condition += "and tabAccount.company = %(company)s"
return frappe.db.sql(
"""select tabAccount.name from `tabAccount`
where (tabAccount.report_type = "Profit and Loss"
or tabAccount.account_type in ("Income Account", "Temporary"))
and tabAccount.is_group=0
and tabAccount.`{key}` LIKE %(txt)s
{condition} {match_condition}
order by idx desc, name""".format(
condition=condition, match_condition=get_match_cond(doctype), key=searchfield
),
{"txt": "%%%s%%" % frappe.db.escape(txt), "company": filters.get("company", "")},
)
def user_query(doctype, txt, searchfield, start, page_len, filters):
from frappe.desk.reportview import get_match_cond
user_type_condition = "and user_type = 'System User'"
if filters and filters.get('ignore_user_type'):
user_type_condition = ''
txt = "%{}%".format(txt)
return frappe.db.sql("""select name, concat_ws(' ', first_name, middle_name, last_name)
from `tabUser`
where enabled=1
{user_type_condition}
and docstatus < 2
and name not in ({standard_users})
and ({key} like %(txt)s
or concat_ws(' ', first_name, middle_name, last_name) like %(txt)s)
{mcond}
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""".format(
user_type_condition = user_type_condition,
standard_users=", ".join(["'{0}'".format(frappe.db.escape(u)) for u in STANDARD_USERS]),
key=searchfield, mcond=get_match_cond(doctype)),
dict(start=start, page_len=page_len, txt=txt))
def customer_query(doctype, txt, searchfield, start, page_len, filters):
cust_master_name = frappe.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 frappe.db.sql(
"""select {fields} from `tabCustomer`
where docstatus < 2
and ({key} like %(txt)s
or customer_name like %(txt)s)
{mcond}
order by
if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
if(locate(%(_txt)s, customer_name), locate(%(_txt)s, customer_name), 99999),
name, customer_name
limit %(start)s, %(page_len)s""".format(
**{"fields": fields, "key": searchfield, "mcond": get_match_cond(doctype)}
),
{"txt": "%%%s%%" % txt, "_txt": txt.replace("%", ""), "start": start, "page_len": page_len},
)
def get_pending_prd(doctype, txt, searchfield, start, page_len, filters):
return frappe.db.sql("""SELECT DISTINCT(`tabWork Order`.name), `tabWork Order`.sales_order, `tabWork Order`.production_order_date,
`tabWork Order`.item_description
FROM `tabWork Order`, `tabSales Order` so, `tabSales Order Item` soi
WHERE
`tabWork Order`.docstatus = 1
AND so.docstatus = 1
AND soi.parent = so.name
AND so.status != "Closed"
AND soi.qty > soi.delivered_qty
AND `tabWork Order`.sales_order = so.name
AND (`tabWork Order`.name like %(txt)s
or `tabWork Order`.sales_order like %(txt)s)
{mcond}
order by
if(locate(%(_txt)s, `tabWork Order`.name), locate(%(_txt)s, `tabWork Order`.name), 1)
limit %(start)s, %(page_len)s""".format(**{
'key': searchfield,
'mcond': get_match_cond(doctype)
}), {
'txt': "%%%s%%" % txt,
'_txt': txt.replace("%", ""),
'start': start,
'page_len': page_len,
})
def get_mob_no(doctype, txt, searchfield, start, page_len, filters):
# company = frappe.get_value("User",frappe.session.user,"company")
return frappe.db.sql("""select c.name, co.mobile_no from `tabCustomer` c LEFT JOIN `tabContact` co on
c.name = co.customer where (c.name like %(txt)s or co.mobile_no like %(txt)s) """.format(**{
'mcond': get_match_cond(doctype)
}), {
'txt': "%%%s%%" % txt
})
def get_batch_no(doctype, txt, searchfield, start, page_len, filters):
cond = ""
if filters.get("posting_date"):
cond = "and (batch.expiry_date is null or batch.expiry_date >= %(posting_date)s)"
batch_nos = None
args = {
'item_code': filters.get("item_code"),
'warehouse': filters.get("warehouse"),
'posting_date': filters.get('posting_date'),
'txt': "%{0}%".format(txt),
"start": start,
"page_len": page_len
}
if args.get('warehouse'):
batch_nos = frappe.db.sql("""select sle.batch_no, round(sum(sle.actual_qty),2), sle.stock_uom, concat('MFG-',batch.manufacturing_date), concat('EXP-',batch.expiry_date)
from `tabStock Ledger Entry` sle
INNER JOIN `tabBatch` batch on sle.batch_no = batch.name
where
batch.disabled = 0
and sle.item_code = %(item_code)s
and sle.warehouse = %(warehouse)s
and (sle.batch_no like %(txt)s
or batch.manufacturing_date like %(txt)s)
and batch.docstatus < 2
{0}
{match_conditions}
group by batch_no having sum(sle.actual_qty) > 0
order by batch.expiry_date, sle.batch_no desc
limit %(start)s, %(page_len)s""".format(cond, match_conditions=get_match_cond(doctype)), args)
if batch_nos:
return batch_nos
else:
return frappe.db.sql("""select name, concat('MFG-', manufacturing_date), concat('EXP-',expiry_date) from `tabBatch` batch
where batch.disabled = 0
and item = %(item_code)s
and (name like %(txt)s
or manufacturing_date like %(txt)s)
and docstatus < 2
{0}
{match_conditions}
order by expiry_date, name desc
limit %(start)s, %(page_len)s""".format(cond, match_conditions=get_match_cond(doctype)), args)
def item_query(doctype, txt, searchfield, start, page_len, filters):
if filters.get("from"):
from frappe.desk.reportview import get_match_cond
filters.update({"txt": txt, "mcond": get_match_cond(filters["from"]), "start": start, "page_len": page_len})
return frappe.db.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 get_project_name(doctype, txt, searchfield, start, page_len, filters):
cond = ''
if filters.get('customer'):
cond = '(`tabProject`.customer = "' + filters['customer'] + '" or ifnull(`tabProject`.customer,"")="") and'
return frappe.db.sql("""select `tabProject`.name from `tabProject`
where `tabProject`.status not in ("Completed", "Cancelled")
and {cond} `tabProject`.name like %s {match_cond}
order by `tabProject`.name asc
limit {start}, {page_len}""".format(cond=cond, match_cond=get_match_cond(doctype),
start=start, page_len=page_len), "%{0}%".format(txt))
def get_project_name(doctype, txt, searchfield, start, page_len, filters):
cond = ''
if filters.get('customer'):
cond = '(`tabProject`.customer = "' + filters['customer'] + '" or ifnull(`tabProject`.customer,"")="") and'
return frappe.db.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%%" % frappe.db.escape(txt),
'mcond':get_match_cond(doctype),'start': start, 'page_len': page_len})
def bom(doctype, txt, searchfield, start, page_len, filters):
conditions = []
return frappe.db.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%%" % frappe.db.escape(txt),
'fcond': get_filters_cond(doctype, filters, conditions),
'mcond':get_match_cond(doctype), 'start': start, 'page_len': page_len})
def get_delivery_notes_to_be_billed(doctype, txt, searchfield, start, page_len, filters):
return frappe.db.sql("""select `tabDelivery Note`.name, `tabDelivery Note`.customer_name
from `tabDelivery Note`
where `tabDelivery Note`.`%(key)s` like %(txt)s and
%(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 get_program_courses(doctype, txt, searchfield, start, page_len, filters):
if filters.get('program'):
return frappe.db.sql("""select course, course_name from `tabProgram Course`
where parent = %(program)s and course like %(txt)s {match_cond}
order by
if(locate(%(_txt)s, course), locate(%(_txt)s, course), 99999),
idx desc,
`tabProgram Course`.course asc
limit {start}, {page_len}""".format(
match_cond=get_match_cond(doctype),
start=start,
page_len=page_len), {
"txt": "%{0}%".format(txt),
"_txt": txt.replace('%', ''),
"program": filters['program']
})
def employee_query(doctype, txt, searchfield, start, page_len, filters):
return frappe.db.sql(
"""select name, employee_name from `tabEmployee`
where status = 'Active'
and docstatus < 2
and ({key} like %(txt)s
or employee_name like %(txt)s)
{mcond}
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""".format(
**{"key": searchfield, "mcond": get_match_cond(doctype)}
),
{"txt": "%%%s%%" % txt, "_txt": txt.replace("%", ""), "start": start, "page_len": page_len},
)
def employee_query(doctype, txt, searchfield, start, page_len, filters):
return frappe.db.sql(
"""select name, employee_name from `tabEmployee`
where docstatus < 2
and ({key} like %(txt)s
or employee_name like %(txt)s)
{mcond}
order by name, employee_name
limit %(start)s, %(page_len)s """.format(**{
'key': searchfield,
'mcond': get_match_cond(doctype)
}), {
'txt': "%%%s%%" % txt,
'_txt': txt.replace("%", ""),
'start': start,
'page_len': page_len
})
def hasqty_item_query(doctype, txt, searchfield, start, page_len, filters, as_dict=False):
"""
Query for has_qty field
"""
conditions = []
warehouse = filters["warehouse"]
# deleting to prevent get_filters_cond & get_match_cond
del filters["warehouse"]
return frappe.db.sql("""select tabItem.name, tabItem.item_group, tabItem.image,
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
JOIN tabBin
ON tabBin.item_code = tabItem.name
where
(tabBin.actual_qty - tabBin.reserved_qty - tabBin.reserved_qty_for_production) > 0
and tabBin.warehouse = %(warehouse)s
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('%', '%%')),
{
"today": nowdate(),
"txt": "%%%s%%" % txt,
"_txt": txt.replace("%", ""),
"start": start,
"page_len": page_len,
"warehouse" : warehouse
}, as_dict=as_dict)
def get_outward_sample(doctype, txt, searchfield, start, page_len, filters,
as_dict):
return frappe.db.sql("""
SELECT
`tabOutward Sample`.name, `tabOutward Sample`.date as transaction_date
FROM
`tabOutward Sample`
WHERE
`tabOutward Sample`.docstatus = 1
%(fcond)s
%(mcond)s
""" % {
"fcond": get_filters_cond(doctype, filters, []),
"mcond": get_match_cond(doctype),
"txt": "%(txt)s"
}, {"txt": ("%%%s%%" % txt)},
as_dict=as_dict)
def get_list_purchase_order(doctype, txt, searchfield, start, page_len, filters):
return frappe.db.sql("""select distinct(parent), concat("Qty PO: ",cast(qty as int)) from `tabPurchase Order Item`
where docstatus = '1'
and qty > received_qty
and parent like %(txt)s
and item_code = %(item_code)s
{mcond}
order by item_code asc limit %(start)s, %(page_len)s""".format(**{
'key': searchfield,
'mcond':get_match_cond(doctype)
}), {
'txt': "%%%s%%" % txt,
'_txt': txt.replace("%", ""),
'start': start,
'page_len': page_len,
'item_code': filters.get("item_code")
})
def get_delivery_notes_to_be_billed(doctype, txt, searchfield, start, page_len,
filters, as_dict):
return frappe.db.sql("""
select `tabDelivery Note`.name, `tabDelivery Note`.customer, `tabDelivery Note`.posting_date
from `tabDelivery Note`
where `tabDelivery Note`.`%(key)s` like %(txt)s and
`tabDelivery Note`.docstatus = 1 and `tabDelivery Note`.is_return = 0
and status not in ("Stopped", "Closed") %(fcond)s
and (`tabDelivery Note`.per_billed < 100 or `tabDelivery Note`.grand_total = 0)
%(mcond)s order by `tabDelivery Note`.`%(key)s` asc
""" % {
"key": searchfield,
"fcond": get_filters_cond(doctype, filters, []),
"mcond": get_match_cond(doctype),
"txt": "%(txt)s"
}, {"txt": ("%%%s%%" % txt)},
as_dict=as_dict)
def get_delivery_notes_to_be_billed(doctype, txt, searchfield, start, page_len, filters):
return frappe.db.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 employee_query(doctype, txt, searchfield, start, page_len, filters):
if filters.get('status'):
cond = "status = '{status}' and ".format(**filters)
sql = """select name,employee_name from `tabEmployee` where (1=1) and ( {cond} {key} like %(txt)s) {mcond}
order by if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999), name limit %(start)s, %(page_len)s""" \
.format(**{
"cond": cond,
'key': searchfield,
'mcond': get_match_cond(doctype),
}), {
'txt': "%%%s%%" % txt,
'_txt': txt.replace("%", ""),
'start': start,
'page_len': page_len
}
return frappe.db.sql(*sql)
def get_delivery_notes_to_be_billed(doctype, txt, searchfield, start, page_len, filters):
return frappe.db.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 and status not in ("Stopped", "Closed") %(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 customer_query(doctype, txt, searchfield, start, page_len, filters):
conditions = []
cust_master_name = frappe.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"]
meta = frappe.get_meta("Customer")
searchfields = meta.get_search_fields()
searchfields = searchfields + [f for f in [searchfield or "name", "customer_name"] \
if not f in searchfields]
fields = fields + [f for f in searchfields if not f in fields]
fields = ", ".join(fields)
searchfields = " or ".join(
[field + " like %(txt)s" for field in searchfields])
return frappe.db.sql(
"""select {fields} from `tabCustomer`
where docstatus < 2
and ({scond}) and disabled=0
{fcond} {mcond}
order by
if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
if(locate(%(_txt)s, customer_name), locate(%(_txt)s, customer_name), 99999),
idx desc,
name, customer_name
limit %(start)s, %(page_len)s""".format(
**{
"fields":
fields,
"scond":
searchfields,
"mcond":
get_match_cond(doctype),
"fcond":
get_filters_cond(doctype, filters, conditions).replace(
'%', '%%'),
}), {
'txt': "%%%s%%" % txt,
'_txt': txt.replace("%", ""),
'start': start,
'page_len': page_len
})
def new_item_query1(doctype,
txt,
searchfield,
start,
page_len,
filters,
as_dict=False):
conditions = []
return db.sql("""
select tabItem.name, tabItem.item_customer_code, tabItem.item_group, tabItem.item_other_name,
if(length(tabItem.item_name) > 40, concat(substr(tabItem.item_name, 1, 40), "..."), item_name) as item_name, if(round(sum(bin.actual_qty),2) > 0,CONCAT_WS(':',w.company,round(sum(bin.actual_qty),2)),0)
from tabItem
LEFT JOIN `tabBin` as bin ON bin.item_code = tabItem.item_code
LEFT JOIN `tabWarehouse` as w ON w.name = bin.warehouse
where
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_name LIKE %(txt)s
or tabItem.item_group LIKE %(txt)s
or tabItem.item_customer_code LIKE %(txt)s
or tabItem.item_other_name LIKE %(txt)s)
{fcond} {mcond}
group by
w.company,bin.item_code
order by
if(locate(%(_txt)s, tabItem.name), locate(%(_txt)s, tabItem.name), 99999),
if(locate(%(_txt)s, item_name), locate(%(_txt)s, item_name), 99999),
sum(bin.actual_qty) desc
limit %(start)s, %(page_len)s """.format(
key=searchfield,
fcond=get_filters_cond(doctype, filters,
conditions).replace('%', '%%'),
mcond=get_match_cond(doctype).replace('%', '%%')), {
"today": nowdate(),
"txt": "%s%%" % txt,
"_txt": txt.replace("%", ""),
"start": start,
"page_len": page_len
},
as_dict=as_dict)
def contact_query(doctype, txt, searchfield, start, page_len, filters):
return frappe.db.sql(
"""select `name`, handphone, email_id from `tabExpedition Detail`
where docstatus != '2'
and contact_name like %(txt)s
and parent = %(cond)s
{mcond}
limit %(start)s, %(page_len)s""".format(
**{
'key': searchfield,
'mcond': get_match_cond(doctype)
}), {
'txt': "%%%s%%" % txt,
'_txt': txt.replace("%", ""),
'start': start,
'page_len': page_len,
'cond': filters.get("link_name")
})
def batch_query(doctype, txt, searchfield, start, page_len, filters):
if filters.get("from"):
from frappe.desk.reportview import get_match_cond
mcond = get_match_cond(filters["from"])
# cond, qi_condition = " ", "and (quality_inspection is null or quality_inspection = '')"
return frappe.db.sql(
""" select batch_no from `tab{doc}`
where parent=%(parent)s and docstatus = 1 and item_code = %(item_code)s
{mcond}
order by item_code limit {start}, {page_len}""".format(
doc=filters.get('from'),
parent=filters.get('parent'),
mcond=mcond,
start=start,
page_len=page_len), {
'parent': filters.get('parent'),
'item_code': filters.get('item_code')
})
def uom_query(doctype, txt, searchfield, start, page_len, filters):
return frappe.db.sql(
"""select uom from `tabUOM Conversion Detail`
where docstatus = '0' and parent = %(cond)s
and uom like %(txt)s
order by idx asc
{mcond}
limit %(start)s, %(page_len)s""".format(
**{
'key': searchfield,
'mcond': get_match_cond(doctype)
}), {
'txt': "%%%s%%" % txt,
'_txt': txt.replace("%", ""),
'start': start,
'page_len': page_len,
'cond': filters.get("item_code")
})
def get_mobile_no(doctype, txt, searchfield, start, page_len, filters):
return frappe.db.sql("""select mobile_no, customer from `tabContact` where customer is not null
and ({key} like %(txt)s
or mobile_no like %(txt)s)
{mcond}
order by
if(locate(%(_txt)s, mobile_no), locate(%(_txt)s, mobile_no), 99999),
if(locate(%(_txt)s, customer), locate(%(_txt)s, customer), 99999),
mobile_no, customer
limit %(start)s, %(page_len)s""".format(**{
'key': searchfield,
'mcond': get_match_cond(doctype)
}), {
'txt': "%%%s%%" % txt,
'_txt': txt.replace("%", ""),
'start': start,
'page_len': page_len
})
def packing_query(doctype, txt, searchfield, start, page_len, filters):
return frappe.db.sql(
"""select `name`, concat('Customer: ', customer), concat('<br>Sales Order: ', sales_order) from `tabPacking`
where docstatus = '1' and delivery_order is null
and (`name` like %(txt)s or customer like %(txt)s)
and `name` not in (%(cond)s)
{mcond}
limit %(start)s, %(page_len)s""".format(
**{
'key': searchfield,
'mcond': get_match_cond(doctype)
}), {
'txt': "%%%s%%" % txt,
'_txt': txt.replace("%", ""),
'start': start,
'page_len': page_len,
'cond': filters.get("not_in")
})
def get_project_name(doctype, txt, searchfield, start, page_len, filters):
cond = ""
if filters.get("customer"):
cond = '(`tabProject`.customer = "' + filters["customer"] + '" or ifnull(`tabProject`.customer,"")="") and'
return frappe.db.sql(
"""select `tabProject`.name from `tabProject`
where `tabProject`.status not in ("Completed", "Cancelled")
and {cond} `tabProject`.name like %(txt)s {match_cond}
order by
if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
idx desc,
`tabProject`.name asc
limit {start}, {page_len}""".format(
cond=cond, match_cond=get_match_cond(doctype), start=start, page_len=page_len
),
{"txt": "%{0}%".format(txt), "_txt": txt.replace("%", "")},
)
def bom(doctype, txt, searchfield, start, page_len, filters):
conditions = []
return frappe.db.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%%" % frappe.db.escape(txt),
'fcond': get_filters_cond(doctype, filters, conditions),
'mcond': get_match_cond(doctype),
'start': start,
'page_len': page_len
})
def item_query(doctype, txt, searchfield, start, page_len, filters):
return frappe.db.sql("""select distinct(a.`name`), a.item_group from `tabItem` a
inner join `tabPurchase Invoice Item` b on a.`name` = b.item_code
inner join `tabPurchase Invoice` c on b.parent = c.`name`
where c.docstatus = '1'
and (a.`name` like %(txt)s or a.item_code like %(txt)s)
and c.supplier = %(supplier)s
{mcond}
limit %(start)s, %(page_len)s""".format(**{
'key': searchfield,
'mcond':get_match_cond(doctype)
}), {
'txt': "%%%s%%" % txt,
'_txt': txt.replace("%", ""),
'start': start,
'page_len': page_len,
'supplier': filters.get("supplier")
})
def get_leave_from(doctype, txt, searchfield, start, page_len, filters):
docname = filters['docname']
query = '''select io.service_unit
from `tabInpatient Occupancy` io, `tabInpatient Record` ir
where io.parent = '{docname}' and io.parentfield = 'inpatient_occupancies'
and io.left!=1 and io.parent = ir.name'''
return frappe.db.sql(query.format(**{
"docname": docname,
"searchfield": searchfield,
"mcond": get_match_cond(doctype)
}), {
'txt': "%%%s%%" % txt,
'_txt': txt.replace("%", ""),
'start': start,
'page_len': page_len
})
def get_project_name(doctype, txt, searchfield, start, page_len, filters):
cond = ''
if filters.get('customer'):
cond = '(`tabProject`.customer = "' + filters[
'customer'] + '" or ifnull(`tabProject`.customer,"")="") and'
return frappe.db.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),
'start': start,
'page_len': page_len
})
def item_query(doctype, txt, searchfield, start, page_len, filters):
if filters.get("from"):
from frappe.desk.reportview import get_match_cond
mcond = get_match_cond(filters["from"])
cond, qi_condition = "", "and (quality_inspection is null or quality_inspection = '')"
if filters.get("parent"):
if filters.get('from') in ['Purchase Invoice Item', 'Purchase Receipt Item']\
and filters.get("inspection_type") != "In Process":
cond = """and item_code in (select name from `tabItem` where
inspection_required_before_purchase = 1)"""
elif filters.get('from') in ['Sales Invoice Item', 'Delivery Note Item']\
and filters.get("inspection_type") != "In Process":
cond = """and item_code in (select name from `tabItem` where
inspection_required_before_delivery = 1)"""
elif filters.get('from') == 'Stock Entry Detail':
cond = """and s_warehouse is null"""
if filters.get('from') in ['Supplier Quotation Item']:
qi_condition = ""
return frappe.db.sql("""
SELECT item_code
FROM `tab{doc}`
WHERE parent=%(parent)s and docstatus < 2 and item_code like %(txt)s
{qi_condition} {cond} {mcond}
ORDER BY item_code limit {start}, {page_len}
""".format(doc=filters.get('from'),
cond = cond, mcond = mcond, start = start,
page_len = page_len, qi_condition = qi_condition),
{'parent': filters.get('parent'), 'txt': "%%%s%%" % txt})
elif filters.get("reference_name"):
return frappe.db.sql("""
SELECT production_item
FROM `tab{doc}`
WHERE name = %(reference_name)s and docstatus < 2 and production_item like %(txt)s
{qi_condition} {cond} {mcond}
ORDER BY production_item
LIMIT {start}, {page_len}
""".format(doc=filters.get("from"),
cond = cond, mcond = mcond, start = start,
page_len = page_len, qi_condition = qi_condition),
{'reference_name': filters.get('reference_name'), 'txt': "%%%s%%" % txt})
def get_mobile_no(doctype, txt, searchfield, start, page_len, filters):
return frappe.db.sql(
"""select mobile_no, customer from `tabContact` where customer is not null
and ({key} like %(txt)s
or mobile_no like %(txt)s)
{mcond}
order by
if(locate(%(_txt)s, mobile_no), locate(%(_txt)s, mobile_no), 99999),
if(locate(%(_txt)s, customer), locate(%(_txt)s, customer), 99999),
mobile_no, customer
limit %(start)s, %(page_len)s""".format(**{
'key': searchfield,
'mcond': get_match_cond(doctype)
}), {
'txt': "%%%s%%" % txt,
'_txt': txt.replace("%", ""),
'start': start,
'page_len': page_len
})
def attribute_tt_query(doctype, txt, searchfield, start, page_len, filters):
return frappe.db.sql("""select attribute_value, parent from `tabItem Attribute Value`
where parent = "Tool Type"
AND ({key} like %(txt)s
or attribute_value like %(txt)s)
{mcond}
order by
if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
if(locate(%(_txt)s, attribute_value), locate(%(_txt)s, attribute_value), 99999),
attribute_value
limit %(start)s, %(page_len)s""".format(**{
'key': searchfield,
'mcond': get_match_cond(doctype)
}), {
'txt': "%%%s%%" % txt,
'_txt': txt.replace("%", ""),
'start': start,
'page_len': page_len,
})
def item_query(doctype,
txt,
searchfield,
start,
page_len,
filters,
as_dict=False):
conditions = []
txt = txt.replace(" ", "%")
return frappe.db.sql(
"""select tabItem.name, tabItem.item_group, tabItem.image,
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.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, name), locate(%(_txt)s, name), 99999),
if(locate(%(_txt)s, item_name), locate(%(_txt)s, item_name), 99999),
idx desc,
name, 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('%', '%%')), {
"today": nowdate(),
"txt": "%%%s%%" % txt,
"_txt": txt.replace("%", ""),
"start": start,
"page_len": 50
},
as_dict=as_dict)
def loadftv(doctype, txt, searchfield, start, page_len, filters):
#frappe.errprint(get_match_cond(doctype))
return frappe.db.sql("""select name,ftv_name from `tabFirst Timer` where (ftv_owner is null or ftv_owner='') and name NOT IN (select ifnull(ftv_id_no,'') from tabMember)
and ({key} like %(txt)s
or ftv_name like %(txt)s)
{mcond}
order by
if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
if(locate(%(_txt)s, ftv_name), locate(%(_txt)s, ftv_name), 99999),
name, ftv_name
limit %(start)s, %(page_len)s""".format(**{
'key': searchfield,
'mcond': get_match_cond(doctype)
}), {
'txt': "%%%s%%" % txt,
'_txt': txt.replace("%", ""),
'start': start,
'page_len': page_len
})
def item_query(doctype, txt, searchfield, start, page_len, filters, as_dict=False):
conditions = []
description_cond = ''
if frappe.db.count('Item', cache=True) < 50000:
# scan description only if items are less than 50000
description_cond = 'or tabItem.description LIKE %(txt)s'
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,
tabItem.item_group,
if(length(tabItem.description) > 40, \
concat(substr(tabItem.description, 1, 40), "..."), description) as decription
from tabItem
where 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_code LIKE %(txt)s
or tabItem.item_group LIKE %(txt)s
or tabItem.item_name LIKE %(txt)s
or tabItem.item_code IN (select parent from `tabItem Barcode` where barcode LIKE %(txt)s)
{description_cond})
{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),
idx desc,
name, 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('%', '%%'),
description_cond = description_cond),
{
"today": nowdate(),
"txt": "%%%s%%" % txt,
"_txt": txt.replace("%", ""),
"start": start,
"page_len": page_len
}, as_dict=as_dict)
def attribute_mtm_query(doctype, txt, searchfield, start, page_len, filters):
return frappe.db.sql("""select attribute_value, parent from `tabItem Attribute Value`
where parent = "Material to Machine"
AND ({key} like %(txt)s
or attribute_value like %(txt)s)
{mcond}
order by
if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
if(locate(%(_txt)s, attribute_value), locate(%(_txt)s, attribute_value), 99999),
attribute_value
limit %(start)s, %(page_len)s""".format(**{
'key': searchfield,
'mcond': get_match_cond(doctype)
}), {
'txt': "%%%s%%" % txt,
'_txt': txt.replace("%", ""),
'start': start,
'page_len': page_len,
})
def get_program_courses(doctype, txt, searchfield, start, page_len, filters):
#if not filters.get('program'):
# frappe.msgprint(_("Please select a Program first."))
# return []
return frappe.db.sql(
"""select course, course_name from `tabProgram Course`
where parent = %(program)s and course like %(txt)s {match_cond}
order by
if(locate(%(_txt)s, course), locate(%(_txt)s, course), 99999),
idx desc,
`tabProgram Course`.course asc
limit {start}, {page_len}""".format(match_cond=get_match_cond(doctype),
start=start,
page_len=page_len), {
"txt": "%{0}%".format(txt),
"_txt": txt.replace('%', ''),
"program": filters['program']
})
def lead_query(doctype, txt, searchfield, start, page_len, filters):
return frappe.db.sql(
"""select name, lead_name, company_name from `tabLead`
where docstatus < 2
and ifnull(status, '') != 'Converted'
and ({key} like %(txt)s
or lead_name like %(txt)s
or company_name like %(txt)s)
{mcond}
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""".format(
**{"key": searchfield, "mcond": get_match_cond(doctype)}
),
{"txt": "%%%s%%" % txt, "_txt": txt.replace("%", ""), "start": start, "page_len": page_len},
)
def user_query(doctype, txt, searchfield, start, page_len, filters):
from frappe.desk.reportview import get_match_cond
txt = "%{}%".format(txt)
return frappe.db.sql("""select name, concat_ws(' ', first_name, middle_name, last_name)
from `tabUser`
where enabled=1
and docstatus < 2
and name not in ({standard_users})
and ({key} like %s
or concat_ws(' ', first_name, middle_name, last_name) like %s)
{mcond}
order by
case when name like %s then 0 else 1 end,
case when concat_ws(' ', first_name, middle_name, last_name) like %s
then 0 else 1 end,
name asc
limit %s, %s""".format(standard_users=", ".join(["%s"]*len(STANDARD_USERS)),
key=searchfield, mcond=get_match_cond(doctype)),
tuple(list(STANDARD_USERS) + [txt, txt, txt, txt, start, page_len]))
def get_delivery_notes_to_be_billed(doctype, txt, searchfield, start, page_len, filters):
return frappe.db.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 and status not in ("Stopped", "Closed") %(fcond)s
and (`tabDelivery Note`.per_billed < 100 or `tabDelivery Note`.grand_total = 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 employee_query(doctype, txt, searchfield, start, page_len, filters):
return frappe.db.sql(
"""select e.`name`, e.employee_name from `tabEmployee` e
inner join `tabEmployee Settings Detail` a on e.`name` = a.employee
where a.docstatus != '2'
and (e.`name` like %(txt)s or a.employee_name like %(txt)s)
and a.parentfield = %(cond)s
{mcond}
limit %(start)s, %(page_len)s""".format(
**{
'key': searchfield,
'mcond': get_match_cond(doctype)
}), {
'txt': "%%%s%%" % txt,
'_txt': txt.replace("%", ""),
'start': start,
'page_len': page_len,
'cond': filters.get("department")
})
def pi_query(doctype, txt, searchfield, start, page_len, filters):
return frappe.db.sql(
"""select distinct(b.`name`), concat('Date: ', b.posting_date), concat('<br>Price: ', format((a.rate / a.conversion_factor),0)), concat('<br>Qty: ', format((select sum(c.qty - c.return_qty) from `tabPurchase Invoice Item` c where c.item_code = %(item_code)s and c.parent = b.`name`), 0)) from `tabPurchase Invoice Item` a inner join `tabPurchase Invoice` b on a.parent = b.`name`
where b.docstatus = '1'
and b.`name` like %(txt)s
and b.supplier = %(supplier)s
and a.item_code = %(item_code)s
{mcond}
limit %(start)s, %(page_len)s""".format(
**{
'key': searchfield,
'mcond': get_match_cond(doctype)
}), {
'txt': "%%%s%%" % txt,
'_txt': txt.replace("%", ""),
'start': start,
'page_len': page_len,
'supplier': filters.get("supplier"),
'item_code': filters.get("item_code")
})
def loadftv(doctype, txt, searchfield, start, page_len, filters):
#frappe.errprint(get_match_cond(doctype))
return frappe.db.sql(
"""select name,ftv_name from `tabFirst Timer` where (ftv_owner is null or ftv_owner='') and name NOT IN (select ifnull(ftv_id_no,'') from tabMember)
and ({key} like %(txt)s
or ftv_name like %(txt)s)
{mcond}
order by
if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
if(locate(%(_txt)s, ftv_name), locate(%(_txt)s, ftv_name), 99999),
name, ftv_name
limit %(start)s, %(page_len)s""".format(**{
'key': searchfield,
'mcond': get_match_cond(doctype)
}), {
'txt': "%%%s%%" % txt,
'_txt': txt.replace("%", ""),
'start': start,
'page_len': page_len
})
def get_project_name(doctype, txt, searchfield, start, page_len, filters):
cond = ''
if filters.get('customer'):
cond = '(`tabProject`.customer = "' + filters['customer'] + '" or ifnull(`tabProject`.customer,"")="") and'
return frappe.db.sql("""select `tabProject`.name from `tabProject`
where `tabProject`.status not in ("Completed", "Cancelled")
and {cond} `tabProject`.name like %(txt)s {match_cond}
order by
if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
idx desc,
`tabProject`.name asc
limit {start}, {page_len}""".format(
cond=cond,
match_cond=get_match_cond(doctype),
start=start,
page_len=page_len), {
"txt": "%{0}%".format(txt),
"_txt": txt.replace('%', '')
})
def get_delivery_notes_to_be_billed(doctype, txt, searchfield, start, page_len,
filters):
return frappe.db.sql(
"""select `tabDelivery Note`.name, `tabDelivery Note`.customer_name
from `tabDelivery Note`
where `tabDelivery Note`.`%(key)s` like %(txt)s and
%(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 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 pi_item_query(doctype, txt, searchfield, start, page_len, filters):
return frappe.db.sql(
"""select roi.item_code, roi.item_name, concat('<br/>RO: ', ro.`name`), concat('<br/>Qty: ', cast(roi.qty as int), ' ', roi.uom) as ro_item from `tabReceive Order` ro
inner join `tabReceive Order Item` roi on ro.`name` = roi.parent
where ro.docstatus = '1' and ro.`status` in ('Submitted', 'Partial Bill') and roi.purchase_invoice is null
and (roi.item_code like %(txt)s or roi.item_name like %(txt)s)
and roi.supplier = %(cond)s
order by roi.item_code asc
{mcond}
limit %(start)s, %(page_len)s""".format(
**{
'key': searchfield,
'mcond': get_match_cond(doctype)
}), {
'txt': "%%%s%%" % txt,
'_txt': txt.replace("%", ""),
'start': start,
'page_len': page_len,
'cond': filters.get("supplier")
})
