get_query to server 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 b1bec06..07a97d8 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
@@ -153,13 +153,14 @@
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)
+ - 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))
+ != 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"],
diff --git a/accounts/doctype/pos_setting/pos_setting.js b/accounts/doctype/pos_setting/pos_setting.js
index aa9cf11..991c8da 100755
--- a/accounts/doctype/pos_setting/pos_setting.js
+++ b/accounts/doctype/pos_setting/pos_setting.js
@@ -55,7 +55,7 @@
return{
filters:{
'company_name': doc.company,
- 'group_or_ledger': "Ledger",
+ 'group_or_ledger': "Ledger"
}
}
}
diff --git a/accounts/doctype/sales_invoice/sales_invoice.py b/accounts/doctype/sales_invoice/sales_invoice.py
index d7ab91a..e42cb73 100644
--- a/accounts/doctype/sales_invoice/sales_invoice.py
+++ b/accounts/doctype/sales_invoice/sales_invoice.py
@@ -990,8 +990,10 @@
return webnotes.conn.sql("""select tabAccount.name from `tabAccount`
where (tabAccount.debit_or_credit="Credit"
- or tabAccount.account_type = "Income Account")
- and tabAccount.group_or_ledger="Ledger" and tabAccount.docstatus!=2
- and tabAccount.company = '%(company)s' and tabAccount.%(key)s LIKE '%(txt)s'
- %(mcond)s""" % {'company': filters['company'], 'key': searchfield,
+ or tabAccount.account_type = "Income Account")
+ and tabAccount.group_or_ledger="Ledger"
+ and tabAccount.docstatus!=2
+ and tabAccount.company = '%(company)s'
+ and tabAccount.%(key)s LIKE '%(txt)s'
+ %(mcond)s""" % {'company': filters['company'], 'key': searchfield,
'txt': "%%%s%%" % txt, 'mcond':get_match_cond(doctype, searchfield)})
\ No newline at end of file
diff --git a/buying/doctype/quality_inspection/quality_inspection.js b/buying/doctype/quality_inspection/quality_inspection.js
index 72eaecb..d40d81a 100644
--- a/buying/doctype/quality_inspection/quality_inspection.js
+++ b/buying/doctype/quality_inspection/quality_inspection.js
@@ -43,15 +43,11 @@
// item code based on GRN/DN
cur_frm.fields_dict['item_code'].get_query = function(doc, cdt, cdn) {
- filter = {};
- if (doc.purchase_receipt_no)
- filter:{
- 'parent': doc.purchase_receipt_no
- }
- else if (doc.delivery_note_no)
- filter:{
- 'parent': doc.delivery_note_no
- }
+ var filter = {};
+ if (doc.purchase_receipt_no) filter['parent'] = doc.purchase_receipt_no;
+
+ else if (doc.delivery_note_no) filter['parent'] = doc.delivery_note_no;
+
return{
filters: filter
}
@@ -59,15 +55,14 @@
// Serial No based on item_code
cur_frm.fields_dict['item_serial_no'].get_query = function(doc, cdt, cdn) {
- filter = {};
+ var filter = {};
if (doc.item_code)
filter:{
'item_code': doc.item_code,
'status': "In Store"
}
- // return 'SELECT name, item_code, warehouse FROM `tabSerial No` WHERE docstatus != 2 AND item_code = "' + doc.item_code +'" AND status = "In Store" AND %(key)s LIKE "%s" ORDER BY name ASC LIMIT 50';
else
- filter:{ 'status': "In Store" }
- // return 'SELECT name, item_code, warehouse FROM `tabSerial No` WHERE docstatus != 2 AND status = "In Store" AND %(key)s LIKE "%s" ORDER BY name ASC LIMIT 50';
- return{ filters: filter }
+ filter: { 'status': "In Store" }
+
+ return { filters: filter }
}
\ No newline at end of file
diff --git a/buying/doctype/supplier_quotation/supplier_quotation.js b/buying/doctype/supplier_quotation/supplier_quotation.js
index 54ef722..722ac00 100644
--- a/buying/doctype/supplier_quotation/supplier_quotation.js
+++ b/buying/doctype/supplier_quotation/supplier_quotation.js
@@ -67,7 +67,7 @@
function(doc, cdt, cdn) {
return{
filters:[
- ['Project', 'status', 'not in', 'Completed, Cancelled'],
+ ['Project', 'status', 'not in', 'Completed, Cancelled']
]
}
}
diff --git a/projects/doctype/task/task.py b/projects/doctype/task/task.py
index 0072b1f..0ea9eea 100644
--- a/projects/doctype/task/task.py
+++ b/projects/doctype/task/task.py
@@ -96,8 +96,9 @@
def get_project(doctype, txt, searchfield, start, page_len, filters):
from controllers.queries import get_match_cond
return webnotes.conn.sql(""" select name from `tabProject`
- where %(key)s like "%(txt)s" %(mcond)s
- order by name limit %(start)s, %(page_len)s
- """ % {'key': searchfield, 'txt': "%%%s%%" % txt,
- 'mcond':get_match_cond(doctype, searchfield),
- 'start': start, 'page_len': page_len})
\ No newline at end of file
+ where %(key)s like "%(txt)s"
+ %(mcond)s
+ order by name
+ limit %(start)s, %(page_len)s """ % {'key': searchfield,
+ 'txt': "%%%s%%" % txt, 'mcond':get_match_cond(doctype, searchfield),
+ 'start': start, 'page_len': page_len})
\ No newline at end of file
diff --git a/selling/doctype/opportunity/opportunity.py b/selling/doctype/opportunity/opportunity.py
index 6217821..92d391b 100644
--- a/selling/doctype/opportunity/opportunity.py
+++ b/selling/doctype/opportunity/opportunity.py
@@ -207,8 +207,9 @@
def get_lead(doctype, txt, searchfield, start, page_len, filters):
from controllers.queries import get_match_cond
return webnotes.conn.sql ("""select `tabLead`.name, `tabLead`.lead_name FROM `tabLead`
- where `tabLead`.%(key)s like "%(txt)s" %(mcond)s
- order by `tabLead`.`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})
\ No newline at end of file
+ where `tabLead`.%(key)s like "%(txt)s"
+ %(mcond)s
+ order by `tabLead`.`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})
\ No newline at end of file
diff --git a/selling/doctype/quotation/quotation.js b/selling/doctype/quotation/quotation.js
index cfaf611..0649f54 100644
--- a/selling/doctype/quotation/quotation.js
+++ b/selling/doctype/quotation/quotation.js
@@ -199,7 +199,7 @@
return {
query: 'selling.doctype.quotation.quotation.quotation_details',
filters:{
- cond: cond,
+ cond: cond
}
}
}
diff --git a/selling/doctype/quotation/quotation.py b/selling/doctype/quotation/quotation.py
index 152e909..5bf8510 100644
--- a/selling/doctype/quotation/quotation.py
+++ b/selling/doctype/quotation/quotation.py
@@ -266,26 +266,35 @@
if filters.has_key('cust') and filters.has_key('precision'):
return webnotes.conn.sql("""select item.name,
- (select concat('Last Quote @ ', q.currency, ' ', format(q_item.export_rate, %(precision)s))
+ (select concat('Last Quote @ ', q.currency, ' ',
+ format(q_item.export_rate, %(precision)s))
from `tabQuotation` q, `tabQuotation Item` q_item
- where q.name = q_item.parent and q_item.item_code = item.name
- and q.docstatus = 1 and q.customer = "%(cust)s"
+ where q.name = q_item.parent
+ and q_item.item_code = item.name
+ and q.docstatus = 1
+ and q.customer = "%(cust)s"
order by q.transaction_date desc
limit 1) as quote_rate,
- (select concat('Last Sale @ ', si.currency, ' ', format(si_item.basic_rate, %(precision)s))
+ (select concat('Last Sale @ ', si.currency, ' ',
+ format(si_item.basic_rate, %(precision)s))
from `tabSales Invoice` si, `tabSales Invoice Item` si_item
- where si.name = si_item.parent and si_item.item_code = item.name
- and si.docstatus = 1 and si.customer ="%(cust)s"
+ where si.name = si_item.parent
+ and si_item.item_code = item.name
+ and si.docstatus = 1
+ and si.customer ="%(cust)s"
order by si.posting_date desc
limit 1) as sales_rate,
item.item_name, item.description
from `tabItem` item
- where %(cond)s %(mcond)s and item.%(searchfield)s like '%(txt)s' order by item.name desc limit %(start)s,
- %(page_len)s """ % {'precision': filters["precision"], 'cust': filters['cust'],
- 'cond': filters['cond'], 'searchfield': searchfield, 'txt': "%%%s%%" % txt,
- 'mcond': get_match_cond(doctype, searchfield), 'start': start, 'page_len': page_len})
+ where %(cond)s %(mcond)s
+ and item.%(searchfield)s like '%(txt)s'
+ order by item.name desc limit %(start)s, %(page_len)s """ % {'precision': filters["precision"],
+ 'cust': filters['cust'], 'cond': filters['cond'], 'searchfield': searchfield,
+ 'txt': "%%%s%%" % txt, 'mcond': get_match_cond(doctype, searchfield),
+ 'start': start, 'page_len': page_len})
else:
- webnotes.conn.sql(""" select name, item_name, description from `tabItem` item
- where %s %s and %s like %s order by name desc limit %s, %s""" %
- ("%s", get_match_cond(doctype, searchfield), searchfield, "%s", "%s", "%s"), (filters["cond"], "%%%s%%" % txt, start, page_len))
\ No newline at end of file
+ return webnotes.conn.sql(""" select name, item_name, description from `tabItem` item
+ where %s %s and %s like %s order by name desc limit %s, %s""" %
+ ("%s", get_match_cond(doctype, searchfield), searchfield, "%s", "%s", "%s"),
+ (filters["cond"], "%%%s%%" % txt, start, page_len))
\ No newline at end of file
diff --git a/selling/doctype/sales_common/sales_common.py b/selling/doctype/sales_common/sales_common.py
index dfbfb48..00825bb 100644
--- a/selling/doctype/sales_common/sales_common.py
+++ b/selling/doctype/sales_common/sales_common.py
@@ -370,20 +370,30 @@
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 ifnull(is_cancelled, 'No') = 'No' and batch_no like '%(txt)s'
- and exists(select * from `tabBatch` where
- name = sle.batch_no and expiry_date >= '%(posting_date)s'
- and docstatus != 2) %(mcond)s
+ where item_code = '%(item_code)s'
+ and warehouse = '%(warehouse)s'
+ and ifnull(is_cancelled, 'No') = 'No'
+ and batch_no like '%(txt)s'
+ and exists(select * from `tabBatch`
+ where name = sle.batch_no
+ and 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': 'txt': "%%%s%%" % txt,
- 'mcond':get_match_cond(doctype, searchfield),'start': start, 'page_len': page_len})
+ 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 expiry_date >= '%(posting_date)s'
- and name like '%(txt)s' %(mcond)s ORDER BY name DESC LIMIT 50""" %
- {'item_code': filters['item_code'], 'posting_date': filters['posting_date'],
- 'txt': 'txt': "%%%s%%" % txt, 'mcond':get_match_cond(doctype, searchfield),
- 'start': start, 'page_len': page_len})
\ No newline at end of file
+ return webnotes.conn.sql("""select name from tabBatch
+ where docstatus != 2
+ and item = '%(item_code)s'
+ and 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})
\ No newline at end of file
diff --git a/stock/doctype/packing_slip/packing_slip.py b/stock/doctype/packing_slip/packing_slip.py
index a46d77c..63aa6a9 100644
--- a/stock/doctype/packing_slip/packing_slip.py
+++ b/stock/doctype/packing_slip/packing_slip.py
@@ -177,7 +177,9 @@
from controllers.queries import get_match_cond
return webnotes.conn.sql("""select name, item_name, description from `tabItem`
where name in ( select item_code FROM `tabDelivery Note Item`
- where parent= %s and ifnull(qty, 0) > ifnull(packed_qty, 0))
- and %s like "%s" %s limit %s, %s """ % ("%s", searchfield, "%s",
+ where parent= %s
+ and ifnull(qty, 0) > ifnull(packed_qty, 0))
+ and %s like "%s" %s
+ limit %s, %s """ % ("%s", searchfield, "%s",
get_match_cond(doctype, searchfield), "%s", "%s"),
(filters["delivery_note"], "%%%s%%" % txt, start, page_len))
\ No newline at end of file
diff --git a/stock/doctype/stock_entry/stock_entry.py b/stock/doctype/stock_entry/stock_entry.py
index fc4fadc..d985432 100644
--- a/stock/doctype/stock_entry/stock_entry.py
+++ b/stock/doctype/stock_entry/stock_entry.py
@@ -717,7 +717,8 @@
return webnotes.conn.sql("""select name, customer, customer_name
from `tab%s` where docstatus = 1
- and (`%s` like %%(txt)s or `customer` like %%(txt)s) %s %s
+ and (`%s` like %%(txt)s
+ or `customer` like %%(txt)s) %s %s
order by name, customer, customer_name
limit %s""" % (doctype, searchfield, conditions,
get_match_cond(doctype, searchfield), "%(start)s, %(page_len)s"),
@@ -728,7 +729,8 @@
from controllers.queries import get_match_cond
return webnotes.conn.sql("""select name, supplier, supplier_name
from `tab%s` where docstatus = 1
- and (`%s` like %%(txt)s or `supplier` like %%(txt)s) %s
+ and (`%s` like %%(txt)s
+ or `supplier` like %%(txt)s) %s
order by name, supplier, supplier_name
limit %s""" % (doctype, searchfield, get_match_cond(doctype, searchfield),
"%(start)s, %(page_len)s"), {"txt": "%%%s%%" % txt, "start":
@@ -762,15 +764,21 @@
from controllers.queries import get_match_cond
return webnotes.conn.sql("""select batch_no from `tabStock Ledger Entry` sle
- where item_code = '%(item_code)s' and warehouse = '%(s_warehouse)s'
- and ifnull(is_cancelled, 'No') = 'No' and batch_no like '%(txt)s'
- and exists(select * from `tabBatch` where name = sle.batch_no
- and 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'], 's_warehouse': filters['s_warehouse'],
- 'posting_date': filters['posting_date'], 'txt': "%%%s%%" % txt,
- 'mcond':get_match_cond(doctype, searchfield),"start": start, "page_len": page_len})
+ where item_code = '%(item_code)s'
+ and warehouse = '%(s_warehouse)s'
+ and ifnull(is_cancelled, 'No') = 'No'
+ and batch_no like '%(txt)s'
+ and exists(select * from `tabBatch`
+ where name = sle.batch_no
+ and 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'],
+ 's_warehouse': filters['s_warehouse'], 'posting_date': filters['posting_date'],
+ 'txt': "%%%s%%" % txt, 'mcond':get_match_cond(doctype, searchfield),
+ "start": start, "page_len": page_len})
def get_stock_items_for_return(ref_doclist, parentfields):
"""return item codes filtered from doclist, which are stock items"""
diff --git a/support/doctype/maintenance_schedule/maintenance_schedule.py b/support/doctype/maintenance_schedule/maintenance_schedule.py
index 6260acd..56dceaa 100644
--- a/support/doctype/maintenance_schedule/maintenance_schedule.py
+++ b/support/doctype/maintenance_schedule/maintenance_schedule.py
@@ -335,12 +335,16 @@
def get_sales_order_no(doctype, txt, searchfield, start, page_len, filters):
from controllers.queries import get_match_cond
- return webnotes.conn.sql(""" select distinct `tabSales Order`.name from `tabSales Order`,
- `tabSales Order Item`, `tabItem`
- where `tabSales Order`.company = "%(company)s" and `tabSales Order`.docstatus = 1
- and `tabSales Order Item`.parent = `tabSales Order`.name
- and `tabSales Order Item`.item_code = `tabItem`.name
- and `tabItem`.is_service_item = "Yes" and %(cond)s `tabSales Order`.name LIKE "%(txt)s" %(mcond)s
- ORDER BY `tabSales Order`.name desc LIMIT %(start)s, %(page_len)s"""
- % 'company': filters["company"], 'cond': filters['cond'], 'txt': "%%%s%%" % txt,
- 'mcond':get_match_cond(doctype, searchfield), "start": start, "page_len": page_len})
\ No newline at end of file
+ return webnotes.conn.sql(""" select distinct `tabSales Order`.name
+ from `tabSales Order`, `tabSales Order Item`, `tabItem`
+ where `tabSales Order`.company = "%(company)s"
+ and `tabSales Order`.docstatus = 1
+ and `tabSales Order Item`.parent = `tabSales Order`.name
+ and `tabSales Order Item`.item_code = `tabItem`.name
+ and `tabItem`.is_service_item = "Yes"
+ and %(cond)s `tabSales Order`.name LIKE "%(txt)s"
+ %(mcond)s
+ order by `tabSales Order`.name desc
+ limit %(start)s, %(page_len)s """ % {'company': filters["company"],
+ 'cond': filters['cond'], 'txt': "%%%s%%" % txt, 'mcond':get_match_cond(doctype, searchfield),
+ 'start': start, 'page_len': page_len})
\ No newline at end of file