get_query to servet side
diff --git a/accounts/doctype/payment_to_invoice_matching_tool/payment_to_invoice_matching_tool.py b/accounts/doctype/payment_to_invoice_matching_tool/payment_to_invoice_matching_tool.py
index 633bdd1..b1bec06 100644
--- a/accounts/doctype/payment_to_invoice_matching_tool/payment_to_invoice_matching_tool.py
+++ b/accounts/doctype/payment_to_invoice_matching_tool/payment_to_invoice_matching_tool.py
@@ -141,17 +141,27 @@
msgprint("No amount allocated.", raise_exception=1)
def gl_entry_details(doctype, txt, searchfield, start, page_len, filters):
- ("""select gle.voucher_no, gle.posting_date, gle.%(account_type)s
- from `tabGL Entry` gle
- where gle.account = '%(acc)s' and gle.voucher_type = '%(dt)s'
- and gle.voucher_no like '%s' and ifnull(gle.is_cancelled, 'No') = 'No'
- and (ifnull(gle.against_voucher, '') = '' or ifnull(gle.against_voucher, '') = gle.voucher_no )
- and ifnull(gle.%(account_type)s, 0) > 0
- and (select ifnull(abs(sum(ifnull(debit, 0)) - sum(ifnull(credit, 0))), 0)
- from `tabGL Entry`
- where against_voucher_type = '%(dt)s' and against_voucher = gle.voucher_no
- and voucher_no != gle.voucher_no
- and ifnull(is_cancelled, 'No') = 'No') != abs(ifnull(gle.debit, 0) - ifnull(gle.credit, 0))
- ORDER BY gle.posting_date DESC, gle.voucher_no DESC limit %(start)s, %(page_len)s"""%
- {dt:filters["dt"], acc:filters["acc"], account_type: filters['account_type'], "start": start,
- "page_len": page_len})
\ No newline at end of file
+ from controllers.queries import get_match_cond
+
+ return webnotes.conn.sql("""select gle.voucher_no, gle.posting_date,
+ gle.%(account_type)s from `tabGL Entry` gle
+ where gle.account = '%(acc)s'
+ and gle.voucher_type = '%(dt)s'
+ and gle.voucher_no like '%(txt)s'
+ and ifnull(gle.is_cancelled, 'No') = 'No'
+ and (ifnull(gle.against_voucher, '') = ''
+ or ifnull(gle.against_voucher, '') = gle.voucher_no )
+ and ifnull(gle.%(account_type)s, 0) > 0
+ and (select ifnull(abs(sum(ifnull(debit, 0))
+ - sum(ifnull(credit, 0))), 0)
+ from `tabGL Entry`
+ where against_voucher_type = '%(dt)s'
+ and against_voucher = gle.voucher_no
+ and voucher_no != gle.voucher_no
+ and ifnull(is_cancelled, 'No') = 'No')
+ != abs(ifnull(gle.debit, 0) - ifnull(gle.credit, 0))
+ %(mcond)s
+ ORDER BY gle.posting_date desc, gle.voucher_no desc
+ limit %(start)s, %(page_len)s""" % {dt:filters["dt"], acc:filters["acc"],
+ account_type: filters['account_type'], 'mcond':get_match_cond(doctype, searchfield)
+ 'txt': "%%%s%%" % txt,"start": start, "page_len": page_len})
\ No newline at end of file
diff --git a/controllers/queries.py b/controllers/queries.py
index b3556e5..e397420 100644
--- a/controllers/queries.py
+++ b/controllers/queries.py
@@ -16,6 +16,7 @@
from __future__ import unicode_literals
import webnotes
+from webnotes.utils import cstr
def get_filters_cond(doctype, filters, conditions):
if filters:
@@ -52,37 +53,51 @@
# searches for enabled profiles
def profile_query(doctype, txt, searchfield, start, page_len, filters):
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 (%(key)s like "%(txt)s" or
- concat_ws(' ', first_name, middle_name, last_name) like "%(txt)s") %(mcond)s
+ from `tabProfile`
+ where ifnull(enabled, 0)=1
+ and docstatus < 2
+ and name not in ('Administrator', 'Guest')
+ 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,
+ 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})
# searches for active employees
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
+ 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,
+ name
+ limit %(start)s, %(page_len)s""" % {'key': searchfield, 'txt': "%%%s%%" % txt,
'mcond':get_match_cond(doctype, searchfield), 'start': start, 'page_len': page_len})
# searches for leads which are not converted
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
+ 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,
+ 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})
# searches for customer
@@ -96,12 +111,16 @@
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
+ 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,
+ 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})
@@ -114,12 +133,16 @@
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
+ 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,
+ 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})
@@ -131,9 +154,11 @@
concat(substr(tabItem.description, 1, 40), "..."), description) as decription
FROM tabItem
WHERE tabItem.docstatus!=2
- AND tabItem.%(key)s LIKE "%(txt)s" %(mcond)s limit %(start)s, %(page_len)s """ %
- {'key': searchfield, 'txt': "%%%s%%" % txt, 'mcond':get_match_cond(doctype, searchfield),
- 'start': start, 'page_len': page_len})
+ and tabItem.%(key)s LIKE "%(txt)s"
+ %(mcond)s
+ limit %(start)s, %(page_len)s """ % {'key': searchfield, 'txt': "%%%s%%" % txt,
+ 'mcond':get_match_cond(doctype, searchfield), 'start': start,
+ 'page_len': page_len})
def account_query(doctype, txt, searchfield, start, page_len, filters):
conditions = []
@@ -144,8 +169,10 @@
return webnotes.conn.sql("""select tabAccount.name, tabAccount.parent_account,
tabAccount.debit_or_credit from tabAccount
- where tabAccount.docstatus!=2 and tabAccount.%(key)s LIKE "%(txt)s"
- %(fcond)s %(mcond)s limit %(start)s, %(page_len)s""" % {'key': searchfield,
+ where tabAccount.docstatus!=2
+ and tabAccount.%(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})
@@ -157,12 +184,14 @@
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`,"") in ("", "0000-00-00")
- OR `tabItem`.`end_of_life` > NOW())
- 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,
+ from tabItem
+ where tabItem.docstatus!=2
+ and (ifnull(`tabItem`.`end_of_life`,"") in ("", "0000-00-00")
+ or `tabItem`.`end_of_life` > NOW())
+ 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})
@@ -171,17 +200,22 @@
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 "%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})
+ where tabBOM.docstatus=1
+ and tabBOM.is_active=1
+ and tabBOM.%(key)s like "%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_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})
\ No newline at end of file
+ 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})
\ No newline at end of file