queries to server side
diff --git a/accounts/doctype/mode_of_payment/mode_of_payment.js b/accounts/doctype/mode_of_payment/mode_of_payment.js
index 85b2ab1..e3625e6 100644
--- a/accounts/doctype/mode_of_payment/mode_of_payment.js
+++ b/accounts/doctype/mode_of_payment/mode_of_payment.js
@@ -15,8 +15,11 @@
// along with this program. If not, see <http://www.gnu.org/licenses/>.
cur_frm.set_query("default_account", function(doc) {
- return erpnext.queries.account({
- account_type: "Bank or Cash",
- company: doc.company
- });
+ return{
+ query: "controllers.queries.account_query",
+ filters: {
+ 'account_type': "Bank or Cash",
+ 'company': doc.company
+ }
+ }
});
\ No newline at end of file
diff --git a/accounts/doctype/sales_invoice/sales_invoice.js b/accounts/doctype/sales_invoice/sales_invoice.js
index 1487ec9..421630b 100644
--- a/accounts/doctype/sales_invoice/sales_invoice.js
+++ b/accounts/doctype/sales_invoice/sales_invoice.js
@@ -313,14 +313,16 @@
// Income Account in Details Table
// --------------------------------
cur_frm.set_query("income_account", "entries", function(doc) {
- return '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="'+doc.company+'" AND tabAccount.%(key)s LIKE "%s"';
-})
+ return{
+ query: "accounts.doctype.sales_invoice.sales_invoice.get_income_account",
+ filters: {'company': doc.company}
+ }
+});
// expense account
if (sys_defaults.auto_inventory_accounting) {
cur_frm.fields_dict['entries'].grid.get_field('expense_account').get_query = function(doc) {
return {
- // "query": "accounts.utils.get_account_list",
filters: {
'is_pl_account': 'Yes',
'debit_or_credit': 'Debit',
diff --git a/accounts/doctype/sales_invoice/sales_invoice.py b/accounts/doctype/sales_invoice/sales_invoice.py
index e0147b1..d7ab91a 100644
--- a/accounts/doctype/sales_invoice/sales_invoice.py
+++ b/accounts/doctype/sales_invoice/sales_invoice.py
@@ -984,3 +984,14 @@
return {
"cash_bank_account": val
}
+
+def get_income_account(doctype, txt, searchfield, start, page_len, filters):
+ from controllers.queries import get_match_cond
+
+ 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,
+ 'txt': "%%%s%%" % txt, 'mcond':get_match_cond(doctype, searchfield)})
\ No newline at end of file
diff --git a/buying/doctype/purchase_common/purchase_common.js b/buying/doctype/purchase_common/purchase_common.js
index 1b7dcf0..7d3d600 100644
--- a/buying/doctype/purchase_common/purchase_common.js
+++ b/buying/doctype/purchase_common/purchase_common.js
@@ -28,27 +28,37 @@
if(this.frm.fields_dict.price_list_name) {
this.frm.set_query("price_list_name", function() {
- return repl("select name, currency from `tabPrice List` \
- where buying_or_selling = 'Buying' and name like \"%s%%\"");
+ return{
+ filters: { 'buying_or_selling': "Buying" }
+ }
});
this.frm.set_query("price_list_currency", function() {
- return repl("select distinct ref_currency from `tabItem Price` \
- where price_list_name=\"%(price_list_name)s\" and buying_or_selling = 'Buying' \
- and ref_currency like \"%s%%\"",
- {price_list_name: me.frm.doc.price_list_name});
+ return{
+ filters: {
+ 'price_list_name': me.frm.doc.price_list_name,
+ 'buying_or_selling': "Buying"
+ }
+ }
});
}
if(this.frm.fields_dict.supplier) {
- this.frm.set_query("supplier", erpnext.utils.supplier_query);
+ this.frm.set_query("supplier", function() {
+ return{ query:"controllers.queries.supplier_query" }});
}
this.frm.set_query("item_code", this.frm.cscript.fname, function() {
if(me.frm.doc.is_subcontracted == "Yes") {
- return erpnext.queries.item({'ifnull(tabItem.is_sub_contracted_item, "No")': "Yes"});
+ return{
+ query:"controllers.queries.item_query",
+ filters:{ 'is_sub_contracted_item': 'Yes' }
+ }
} else {
- return erpnext.queries.item({'ifnull(tabItem.is_purchase_item, "No")': "Yes"});
+ return{
+ query:"controllers.queries.item_query",
+ filters:{ 'is_purchase_item': 'Yes' }
+ }
}
});
},
diff --git a/controllers/queries.py b/controllers/queries.py
index ac8ab25..67d5318 100644
--- a/controllers/queries.py
+++ b/controllers/queries.py
@@ -20,7 +20,13 @@
def get_filters_cond(doctype, filters, conditions):
if filters:
if isinstance(filters, dict):
- filters = map(lambda f: [doctype, f[0], "=", f[1]], filters.items())
+ filters = filters.items()
+ flt = []
+ for f in filters:
+ if f[1][0] == '!':
+ flt.append([doctype, f[0], '!=', f[1][1:]])
+ else:
+ flt.append([doctype, f[0], '=', f[1]])
from webnotes.widgets.reportview import build_filter_conditions
build_filter_conditions(filters, conditions)
@@ -36,6 +42,7 @@
from webnotes.widgets.reportview import build_match_conditions
cond = build_match_conditions(doctype, fields)
+
if cond:
cond = ' and ' + cond
else:
@@ -81,10 +88,12 @@
# searches for customer
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
@@ -142,6 +151,7 @@
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,
@@ -158,6 +168,7 @@
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
@@ -166,10 +177,6 @@
'fcond': get_filters_cond(doctype, filters, conditions), 'mcond':get_match_cond(doctype, searchfield),
'start': start, 'page_len': page_len})
-# erpnext.queries.task = function() {
-# return { query: "projects.utils.query_task" };
-# };
-
def get_project_name(doctype, txt, searchfield, start, page_len, filters):
cond = ''
if filters['customer']:
diff --git a/manufacturing/doctype/bom_replace_tool/bom_replace_tool.js b/manufacturing/doctype/bom_replace_tool/bom_replace_tool.js
index c98603c..bad03ac 100644
--- a/manufacturing/doctype/bom_replace_tool/bom_replace_tool.js
+++ b/manufacturing/doctype/bom_replace_tool/bom_replace_tool.js
@@ -20,10 +20,16 @@
}
cur_frm.set_query("current_bom", function(doc) {
- return erpnext.queries.bom({name: "!" + doc.new_bom});
+ return{
+ query:"controllers.queries.bom",
+ filters: {name: "!" + doc.new_bom}
+ }
});
cur_frm.set_query("new_bom", function(doc) {
- return erpnext.queries.bom({name: "!" + doc.current_bom});
+ return{
+ query:"controllers.queries.bom",
+ filters: {name: "!" + doc.current_bom}
+ }
});
\ No newline at end of file
diff --git a/manufacturing/doctype/production_order/production_order.js b/manufacturing/doctype/production_order/production_order.js
index 7dc905a..c3a45e2 100644
--- a/manufacturing/doctype/production_order/production_order.js
+++ b/manufacturing/doctype/production_order/production_order.js
@@ -117,6 +117,9 @@
cur_frm.set_query("bom_no", function(doc) {
if (doc.production_item) {
- return erpnext.queries.bom({item: cstr(doc.production_item)});
+ return{
+ query:"controllers.queries.bom",
+ filters: {item: cstr(doc.production_item)}
+ }
} else msgprint(" Please enter Production Item first");
});
\ No newline at end of file
diff --git a/projects/doctype/time_log/time_log.js b/projects/doctype/time_log/time_log.js
index 22f9610..69ec304 100644
--- a/projects/doctype/time_log/time_log.js
+++ b/projects/doctype/time_log/time_log.js
@@ -2,8 +2,8 @@
erpnext.projects.TimeLog = wn.ui.form.Controller.extend({
setup: function() {
- this.frm.set_query("task", erpnext.queries.task);
- }
-});
+ this.frm.set_query("task", function() {
+ return { query: "projects.utils.query_task" }
+ });
cur_frm.cscript = new erpnext.projects.TimeLog({frm: cur_frm});
\ No newline at end of file
diff --git a/selling/doctype/sales_common/sales_common.js b/selling/doctype/sales_common/sales_common.js
index 02db55a..8d86950 100644
--- a/selling/doctype/sales_common/sales_common.js
+++ b/selling/doctype/sales_common/sales_common.js
@@ -36,33 +36,34 @@
}
this.frm.set_query("customer_address", function() {
- return 'SELECT name, address_line1, city FROM tabAddress \
- WHERE customer = "'+ me.frm.doc.customer +'" AND docstatus != 2 AND \
- %(key)s LIKE "%s" ORDER BY name ASC LIMIT 50';
+ return {
+ filters: {'customer': me.frm.doc.customer }
+ }
});
this.frm.set_query("contact_person", function() {
- return 'SELECT name, CONCAT(first_name," ",ifnull(last_name,"")) As FullName, \
- department, designation FROM tabContact WHERE customer = "'+ me.frm.doc.customer +
- '" AND docstatus != 2 AND %(key)s LIKE "%s" ORDER BY name ASC LIMIT 50';
+ return {
+ filters: {'customer': me.frm.doc.customer }
+ }
});
if(this.frm.fields_dict.charge) {
this.frm.set_query("charge", function() {
- return 'SELECT DISTINCT `tabSales Taxes and Charges Master`.name FROM \
- `tabSales Taxes and Charges Master` \
- WHERE `tabSales Taxes and Charges Master`.company = "' + me.frm.doc.company +
- '" AND `tabSales Taxes and Charges Master`.company is not NULL \
- AND `tabSales Taxes and Charges Master`.docstatus != 2 \
- AND `tabSales Taxes and Charges Master`.%(key)s LIKE "%s" \
- ORDER BY `tabSales Taxes and Charges Master`.name LIMIT 50';
+ return {
+ filters: [
+ ['Sales Taxes and Charges Master', 'company', '=', me.frm.doc.company],
+ ['Sales Taxes and Charges Master', 'company', 'is not', 'NULL'],
+ ['Sales Taxes and Charges Master', 'docstatus', '!=', 2]
+ ]
+ }
});
}
this.frm.fields_dict.customer.get_query = function(doc,cdt,cdn) {
return{ query:"controllers.queries.customer_query" } }
- this.frm.fields_dict.lead && this.frm.set_query("lead", erpnext.utils.lead_query);
+ this.frm.fields_dict.lead && this.frm.set_query("lead", function(doc,cdt,cdn) {
+ return{ query:"controllers.queries.lead_query" } });
if(!this.fname) {
return;
@@ -71,8 +72,10 @@
if(this.frm.fields_dict[this.fname].grid.get_field('item_code')) {
this.frm.set_query("item_code", this.fname, function() {
return me.frm.doc.order_type === "Maintenance" ?
- erpnext.queries.item({'ifnull(tabItem.is_service_item, "No")': "Yes"}) :
- erpnext.queries.item({'ifnull(tabItem.is_sales_item, "No")': "Yes"});
+ { query:"controllers.queries.item_query",
+ filters:{'is_service_item': 'Yes'}} :
+ { query:"controllers.queries.item_query",
+ filters:{'is_sales_item': 'Yes' }} ;
});
}
@@ -83,18 +86,22 @@
wn.throw("Please enter Item Code to get batch no");
} else {
if(item.warehouse) {
- return "select batch_no from `tabStock Ledger Entry` sle \
- where item_code = '" + item.item_code +
- "' and warehouse = '" + item.warehouse +
- "' and ifnull(is_cancelled, 'No') = 'No' and batch_no like '%s' \
- and exists(select * from `tabBatch` where \
- name = sle.batch_no and expiry_date >= '" + me.frm.doc.posting_date +
- "' and docstatus != 2) group by batch_no having sum(actual_qty) > 0 \
- order by batch_no desc limit 50";
+ return {
+ query : "selling.doctype.sales_common.sales_common.get_batch_no",
+ filters: {
+ 'item_code': item.item_code,
+ 'warehouse': item.warehouse,
+ 'posting_date': me.frm.doc.posting_date
+ }
+ }
} else {
- return "SELECT name FROM tabBatch WHERE docstatus != 2 AND item = '" +
- item.item_code + "' and expiry_date >= '" + me.frm.doc.posting_date +
- "' AND name like '%s' ORDER BY name DESC LIMIT 50";
+ return{
+ query : "selling.doctype.sales_common.sales_common.get_batch_no",
+ filters: {
+ 'item': item.item_code,
+ 'posting_date': me.frm.doc.posting_date
+ }
+ }
}
}
});
diff --git a/selling/doctype/sales_common/sales_common.py b/selling/doctype/sales_common/sales_common.py
index f34ebf9..dfbfb48 100644
--- a/selling/doctype/sales_common/sales_common.py
+++ b/selling/doctype/sales_common/sales_common.py
@@ -365,3 +365,25 @@
dt = webnotes.conn.sql("select transaction_date from `tab%s` where name = '%s'" % (d.prevdoc_doctype, d.prevdoc_docname))
d.prevdoc_date = (dt and dt[0][0]) and dt[0][0].strftime('%Y-%m-%d') or ''
+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 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})
+ 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