get_query continued
diff --git a/accounts/doctype/account/account.py b/accounts/doctype/account/account.py
index ac8339c..9efcded 100644
--- a/accounts/doctype/account/account.py
+++ b/accounts/doctype/account/account.py
@@ -31,13 +31,12 @@
self.nsm_parent_field = 'parent_account'
def autoname(self):
- company_abbr = sql("select abbr from tabCompany where name=%s", self.doc.company)[0][0]
- self.doc.name = self.doc.account_name.strip() + ' - ' + company_abbr
+ self.doc.name = self.doc.account_name.strip() + ' - ' + \
+ webnotes.conn.get_value("Company", self.doc.company, "abbr")
- def get_address(self):
- add=sql("Select address from `tab%s` where name='%s'" %
- (self.doc.master_type, self.doc.master_name))
- return {'address': add[0][0]}
+ def get_address(self):
+ address = webnotes.conn.get_value(self.doc.master_type, self.doc.master_name, "address")
+ return {'address': address}
def validate_master_name(self):
if (self.doc.master_type == 'Customer' or self.doc.master_type == 'Supplier') \
@@ -48,7 +47,7 @@
"""Fetch Parent Details and validation for account not to be created under ledger"""
if self.doc.parent_account:
par = sql("""select name, group_or_ledger, is_pl_account, debit_or_credit
- from tabAccount where name =%s""",self.doc.parent_account)
+ from tabAccount where name =%s""", self.doc.parent_account)
if not par:
msgprint("Parent account does not exists", raise_exception=1)
elif par and par[0][0] == self.doc.name:
@@ -108,8 +107,8 @@
# Check if any previous balance exists
def check_gle_exists(self):
- exists = sql("""select name from `tabGL Entry` where account = '%s'
- and ifnull(is_cancelled, 'No') = 'No'""" % (self.doc.name))
+ exists = sql("""select name from `tabGL Entry` where account = %s
+ and ifnull(is_cancelled, 'No') = 'No'""", self.doc.name)
return exists and exists[0][0] or ''
def check_if_child_exists(self):
@@ -152,7 +151,7 @@
credit_limit_from = 'Customer'
cr_limit = sql("""select t1.credit_limit from tabCustomer t1, `tabAccount` t2
- where t2.name='%s' and t1.name = t2.master_name""" % account)
+ where t2.name=%s and t1.name = t2.master_name""", account)
credit_limit = cr_limit and flt(cr_limit[0][0]) or 0
if not credit_limit:
credit_limit = webnotes.conn.get_value('Company', company, 'credit_limit')
@@ -195,16 +194,19 @@
# rename account name
account_name = " - ".join(parts[:-1])
- sql("update `tabAccount` set account_name = '%s' where name = '%s'" % \
- (account_name, old))
+ sql("update `tabAccount` set account_name = %s where name = %s", (account_name, old))
return " - ".join(parts)
def get_master_name(doctype, txt, searchfield, start, page_len, filters):
- return webnotes.conn.sql("""select name from `tab%s` where name like '%%%s%%'""" %
- (filters["master_type"], txt), as_list=1)
+ return webnotes.conn.sql("""select name from `tab%s` where %s like %s
+ order by name limit %s, %s""" %
+ (filters["master_type"], searchfield, "%s", "%s", "%s"),
+ ("%%%s%%" % txt, start, page_len), as_list=1)
def get_parent_account(doctype, txt, searchfield, start, page_len, filters):
return webnotes.conn.sql("""select name from tabAccount
- where group_or_ledger = 'Group' and docstatus != 2 and company = '%s'
- and name like '%%%s%%'""" % (filters["company"], txt))
+ where group_or_ledger = 'Group' and docstatus != 2 and company = %s
+ and %s like %s order by name limit %s, %s""" %
+ ("%s", searchfield, "%s", "%s", "%s"),
+ (filters["company"], "%%%s%%" % txt, start, page_len), as_list=1)
diff --git a/accounts/doctype/c_form/c_form.js b/accounts/doctype/c_form/c_form.js
index adb989d..29c05f7 100644
--- a/accounts/doctype/c_form/c_form.js
+++ b/accounts/doctype/c_form/c_form.js
@@ -17,15 +17,16 @@
//c-form js file
// -----------------------------
cur_frm.fields_dict.invoice_details.grid.get_field("invoice_no").get_query = function(doc) {
- cond = ""
- if (doc.customer) cond += ' AND `tabSales Invoice`.`customer` = "' + cstr(doc.customer) + '"';
- if (doc.company) cond += ' AND `tabSales Invoice`.`company` = "' + cstr(doc.company) + '"';
- return 'SELECT `tabSales Invoice`.`name` FROM `tabSales Invoice` WHERE `tabSales Invoice`.`docstatus` = 1 and `tabSales Invoice`.`c_form_applicable` = "Yes" and ifnull(`tabSales Invoice`.c_form_no, "") = ""'+cond+' AND `tabSales Invoice`.%(key)s LIKE "%s" ORDER BY `tabSales Invoice`.`name` ASC LIMIT 50';
+ return {
+ query: "accounts.doctype.c_form.c_form.get_invoice_nos",
+ filters: {
+ customer: doc.customer,
+ company: doc.company
+ }
+ }
}
cur_frm.cscript.invoice_no = function(doc, cdt, cdn) {
var d = locals[cdt][cdn];
get_server_fields('get_invoice_details', d.invoice_no, 'invoice_details', doc, cdt, cdn, 1);
-}
-
-cur_frm.fields_dict.customer.get_query = erpnext.utils.customer_query;
\ No newline at end of file
+}
\ No newline at end of file
diff --git a/accounts/doctype/c_form/c_form.py b/accounts/doctype/c_form/c_form.py
index 4575653..5bca063 100644
--- a/accounts/doctype/c_form/c_form.py
+++ b/accounts/doctype/c_form/c_form.py
@@ -32,22 +32,23 @@
and no other c-form is received for that"""
for d in getlist(self.doclist, 'invoice_details'):
- inv = webnotes.conn.sql("""select c_form_applicable, c_form_no from
- `tabSales Invoice` where name = %s""", d.invoice_no)
+ if d.invoice_no:
+ inv = webnotes.conn.sql("""select c_form_applicable, c_form_no from
+ `tabSales Invoice` where name = %s""", d.invoice_no)
- if not inv:
- webnotes.msgprint("Invoice: %s is not exists in the system, please check." %
- d.invoice_no, raise_exception=1)
+ if not inv:
+ webnotes.msgprint("Invoice: %s is not exists in the system, please check." %
+ d.invoice_no, raise_exception=1)
- elif inv[0][0] != 'Yes':
- webnotes.msgprint("C-form is not applicable for Invoice: %s" %
- d.invoice_no, raise_exception=1)
+ elif inv[0][0] != 'Yes':
+ webnotes.msgprint("C-form is not applicable for Invoice: %s" %
+ d.invoice_no, raise_exception=1)
- elif inv[0][1] and inv[0][1] != self.doc.name:
- webnotes.msgprint("""Invoice %s is tagged in another C-form: %s.
- If you want to change C-form no for this invoice,
- please remove invoice no from the previous c-form and then try again""" %
- (d.invoice_no, inv[0][1]), raise_exception=1)
+ elif inv[0][1] and inv[0][1] != self.doc.name:
+ webnotes.msgprint("""Invoice %s is tagged in another C-form: %s.
+ If you want to change C-form no for this invoice,
+ please remove invoice no from the previous c-form and then try again""" %
+ (d.invoice_no, inv[0][1]), raise_exception=1)
def on_update(self):
""" Update C-Form No on invoices"""
@@ -80,4 +81,14 @@
'territory' : inv and inv[0][1] or '',
'net_total' : inv and flt(inv[0][2]) or '',
'grand_total' : inv and flt(inv[0][3]) or ''
- }
\ No newline at end of file
+ }
+
+def get_invoice_nos(doctype, txt, searchfield, start, page_len, filters):
+ from utilities import build_filter_conditions
+ conditions, filter_values = build_filter_conditions(filters)
+
+ return webnotes.conn.sql("""select name from `tabSales Invoice` where docstatus = 1
+ and c_form_applicable = 'Yes' and ifnull(c_form_no, '') = '' %s
+ and %s like %s order by name limit %s, %s""" %
+ (conditions, searchfield, "%s", "%s", "%s"),
+ tuple(filter_values + ["%%%s%%" % txt, start, page_len]))
\ No newline at end of file
diff --git a/accounts/doctype/journal_voucher/journal_voucher.js b/accounts/doctype/journal_voucher/journal_voucher.js
index 444763a..9f82828 100644
--- a/accounts/doctype/journal_voucher/journal_voucher.js
+++ b/accounts/doctype/journal_voucher/journal_voucher.js
@@ -49,44 +49,6 @@
if (doc.is_opening == 'Yes') unhide_field('aging_date');
}
-cur_frm.fields_dict['entries'].grid.get_field('account').get_query = function(doc) {
- return "SELECT `tabAccount`.name, `tabAccount`.parent_account FROM `tabAccount` WHERE `tabAccount`.company='"+doc.company+"' AND tabAccount.group_or_ledger = 'Ledger' AND tabAccount.docstatus != 2 AND `tabAccount`.%(key)s LIKE '%s' ORDER BY `tabAccount`.name DESC LIMIT 50";
-}
-
-cur_frm.fields_dict["entries"].grid.get_field("cost_center").get_query = function(doc, cdt, cdn) {
- return 'SELECT `tabCost Center`.`name`, `tabCost Center`.parent_cost_center FROM `tabCost Center` WHERE `tabCost Center`.`company_name` = "' +doc.company+'" AND `tabCost Center`.%(key)s LIKE "%s" AND `tabCost Center`.`group_or_ledger` = "Ledger" AND `tabCost Center`.docstatus != 2 ORDER BY `tabCost Center`.`name` ASC LIMIT 50';
-}
-
-// Restrict Voucher based on Account
-// ---------------------------------
-cur_frm.fields_dict['entries'].grid.get_field('against_voucher').get_query = function(doc) {
- var d = locals[this.doctype][this.docname];
- return "SELECT `tabPurchase Invoice`.name, `tabPurchase Invoice`.credit_to, `tabPurchase Invoice`.outstanding_amount,`tabPurchase Invoice`.bill_no, `tabPurchase Invoice`.bill_date FROM `tabPurchase Invoice` WHERE `tabPurchase Invoice`.credit_to='"+d.account+"' AND `tabPurchase Invoice`.outstanding_amount > 0 AND `tabPurchase Invoice`.docstatus = 1 AND `tabPurchase Invoice`.%(key)s LIKE '%s' ORDER BY `tabPurchase Invoice`.name DESC LIMIT 200";
-}
-
-cur_frm.fields_dict['entries'].grid.get_field('against_invoice').get_query = function(doc) {
- var d = locals[this.doctype][this.docname];
- return "SELECT `tabSales Invoice`.name, `tabSales Invoice`.debit_to, `tabSales Invoice`.outstanding_amount FROM `tabSales Invoice` WHERE `tabSales Invoice`.debit_to='"+d.account+"' AND `tabSales Invoice`.outstanding_amount > 0 AND `tabSales Invoice`.docstatus = 1 AND `tabSales Invoice`.%(key)s LIKE '%s' ORDER BY `tabSales Invoice`.name DESC LIMIT 200";
-}
-
-cur_frm.fields_dict['entries'].grid.get_field('against_jv').get_query = function(doc) {
- var d = locals[this.doctype][this.docname];
-
- if(!d.account) {
- msgprint("Please select Account first!")
- throw "account not selected"
- }
-
- return "SELECT `tabJournal Voucher`.name, `tabJournal Voucher`.posting_date,\
- `tabJournal Voucher`.user_remark\
- from `tabJournal Voucher`, `tabJournal Voucher Detail`\
- where `tabJournal Voucher Detail`.account = '"+ esc_quotes(d.account) + "'\
- and `tabJournal Voucher`.name like '%s'\
- and `tabJournal Voucher`.docstatus=1\
- and `tabJournal Voucher`.voucher_type='Journal Entry'\
- and `tabJournal Voucher Detail`.parent = `tabJournal Voucher`.name";
-}
-
//Set debit and credit to zero on adding new row
//----------------------------------------------
cur_frm.fields_dict['entries'].grid.onrowadd = function(doc, cdt, cdn){
@@ -116,9 +78,8 @@
}
}
-
// Update Totals
-// ---------------
+
cur_frm.cscript.update_totals = function(doc) {
var td=0.0; var tc =0.0;
var el = getchildren('Journal Voucher Detail', doc.name, 'entries');
@@ -161,12 +122,6 @@
cur_frm.cscript.update_totals(doc);
}
-cur_frm.fields_dict['select_print_heading'].get_query = function(doc, cdt, cdn) {
- return 'SELECT `tabPrint Heading`.name FROM `tabPrint Heading` WHERE `tabPrint Heading`.docstatus !=2 AND `tabPrint Heading`.name LIKE "%s" ORDER BY `tabPrint Heading`.name ASC LIMIT 50';
-}
-
-
-
cur_frm.cscript.select_print_heading = function(doc,cdt,cdn){
if(doc.select_print_heading){
// print heading
@@ -201,3 +156,49 @@
cur_frm.set_df_property("cheque_date", "reqd", false);
}
}
+
+// get_query
+
+cur_frm.fields_dict['entries'].grid.get_field('account').get_query = function(doc) {
+ return {
+ query: "accounts.utils.get_account_list",
+ filters: { company: doc.company }
+ }
+}
+
+cur_frm.fields_dict["entries"].grid.get_field("cost_center").get_query = function(doc) {
+ return {
+ query: "accounts.utils.get_cost_center_list",
+ filters: { company_name: doc.company}
+ }
+}
+
+cur_frm.fields_dict['entries'].grid.get_field('against_voucher').get_query = function(doc) {
+ var d = locals[this.doctype][this.docname];
+ return {
+ query: "accounts.doctype.journal_voucher.journal_voucher.get_against_purchase_invoice",
+ filters: { account: d.account }
+ }
+}
+
+cur_frm.fields_dict['entries'].grid.get_field('against_invoice').get_query = function(doc) {
+ var d = locals[this.doctype][this.docname];
+ return {
+ query: "accounts.doctype.journal_voucher.journal_voucher.get_against_sales_invoice",
+ filters: { account: d.account }
+ }
+}
+
+cur_frm.fields_dict['entries'].grid.get_field('against_jv').get_query = function(doc) {
+ var d = locals[this.doctype][this.docname];
+
+ if(!d.account) {
+ msgprint("Please select Account first!")
+ throw "account not selected"
+ }
+
+ return {
+ query: "accounts.doctype.journal_voucher.journal_voucher.get_against_jv",
+ filters: { account: d.account }
+ }
+}
\ No newline at end of file
diff --git a/accounts/doctype/journal_voucher/journal_voucher.py b/accounts/doctype/journal_voucher/journal_voucher.py
index eafd7ee..4408068 100644
--- a/accounts/doctype/journal_voucher/journal_voucher.py
+++ b/accounts/doctype/journal_voucher/journal_voucher.py
@@ -339,4 +339,27 @@
elif self.doc.write_off_based_on == 'Accounts Payable':
return webnotes.conn.sql("""select name, credit_to, outstanding_amount
from `tabPurchase Invoice` where docstatus = 1 and company = %s
- and outstanding_amount > 0 %s""" % ('%s', cond), self.doc.company)
\ No newline at end of file
+ and outstanding_amount > 0 %s""" % ('%s', cond), self.doc.company)
+
+
+def get_against_purchase_invoice(doctype, txt, searchfield, start, page_len, filters):
+ return webnotes.conn.sql("""select name, credit_to, outstanding_amount, bill_no, bill_date
+ from `tabPurchase Invoice` where credit_to = %s and docstatus = 1
+ and outstanding_amount > 0 and %s like %s order by name desc limit %s, %s""" %
+ ("%s", searchfield, "%s", "%s", "%s"),
+ (filters["account"], "%%%s%%" % txt, start, page_len))
+
+def get_against_sales_invoice(doctype, txt, searchfield, start, page_len, filters):
+ return webnotes.conn.sql("""select name, debit_to, outstanding_amount
+ from `tabSales Invoice` where debit_to = %s and docstatus = 1
+ and outstanding_amount > 0 and `%s` like %s order by name desc limit %s, %s""" %
+ ("%s", searchfield, "%s", "%s", "%s"),
+ (filters["account"], "%%%s%%" % txt, start, page_len))
+
+def get_against_jv(doctype, txt, searchfield, start, page_len, filters):
+ return webnotes.conn.sql("""select name, posting_date, user_remark
+ from `tabJournal Voucher` jv, `tabJournal Voucher Detail` jv_detail
+ where jv_detail.parent = jv.name and jv_detail.account = %s and docstatus = 1
+ and jv.%s like %s order by jv.name desc limit %s, %s""" %
+ ("%s", searchfield, "%s", "%s", "%s"),
+ (filters["account"], "%%%s%%" % txt, start, page_len))
\ No newline at end of file
diff --git a/accounts/utils.py b/accounts/utils.py
index 9596cd4..6eb2ddb 100644
--- a/accounts/utils.py
+++ b/accounts/utils.py
@@ -21,6 +21,8 @@
from webnotes.model.doc import addchild
from webnotes import msgprint, _
from webnotes.utils import formatdate
+from utilities import build_filter_conditions
+
class FiscalYearError(webnotes.ValidationError): pass
@@ -209,4 +211,26 @@
ch.against_account = cstr(jvd[0][2])
ch.is_advance = cstr(jvd[0][3])
ch.docstatus = 1
- ch.save(1)
\ No newline at end of file
+ ch.save(1)
+
+def get_account_list(doctype, txt, searchfield, start, page_len, filters):
+ if not filters.get("group_or_ledger"):
+ filters["group_or_ledger"] = "Ledger"
+
+ conditions, filter_values = build_filter_conditions(filters)
+
+ return webnotes.conn.sql("""select name, parent_account from `tabAccount`
+ where docstatus < 2 %s and %s like %s order by name limit %s, %s""" %
+ (conditions, searchfield, "%s", "%s", "%s"),
+ tuple(filter_values + ["%%%s%%" % txt, start, page_len]))
+
+def get_cost_center_list(doctype, txt, searchfield, start, page_len, filters):
+ if not filters.get("group_or_ledger"):
+ filters["group_or_ledger"] = "Ledger"
+
+ conditions, filter_values = build_filter_conditions(filters)
+
+ return webnotes.conn.sql("""select name, parent_cost_center from `tabCost Center`
+ where docstatus < 2 %s and %s like %s order by name limit %s, %s""" %
+ (conditions, searchfield, "%s", "%s", "%s"),
+ tuple(filter_values + ["%%%s%%" % txt, start, page_len]))
\ No newline at end of file
diff --git a/hr/doctype/appraisal/appraisal.py b/hr/doctype/appraisal/appraisal.py
index 849e48a..935c47c 100644
--- a/hr/doctype/appraisal/appraisal.py
+++ b/hr/doctype/appraisal/appraisal.py
@@ -76,11 +76,11 @@
if int(total_w) != 100:
msgprint("Total weightage assigned should be 100%. It is :" + str(total_w) + "%",
raise_exception=1)
-
- if webnotes.conn.get_default("employee", webnotes.session.user) != self.doc.employee:
- if total==0:
- msgprint("Total can't be zero. You must atleast give some points!", raise_exception=1)
+ webnotes.errprint([webnotes.conn.get_value("Employee", self.doc.employee, "user_id")])
+ if webnotes.conn.get_value("Employee", self.doc.employee, "user_id") != \
+ webnotes.session.user and total == 0:
+ msgprint("Total can't be zero. You must atleast give some points!", raise_exception=1)
self.doc.total_score = total
diff --git a/selling/utils.py b/selling/utils.py
new file mode 100644
index 0000000..21e94f7
--- /dev/null
+++ b/selling/utils.py
@@ -0,0 +1,32 @@
+# ERPNext - web based ERP (http://erpnext.com)
+# Copyright (C) 2012 Web Notes Technologies Pvt Ltd
+#
+# This program is free software: you can redistribute it and/or modify
+# it under the terms of the GNU General Public License as published by
+# the Free Software Foundation, either version 3 of the License, or
+# (at your option) any later version.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License
+# along with this program. If not, see <http://www.gnu.org/licenses/>.
+
+from __future__ import unicode_literals
+import webnotes
+
+def get_customer_list(doctype, txt, searchfield, start, page_len, filters):
+ if webnotes.conn.get_default("cust_master_name") == "Customer Name":
+ fields = ["name", "customer_group", "territory"]
+ else:
+ fields = ["name", "customer_name", "customer_group", "territory"]
+
+ return webnotes.conn.sql("""select %s from `tabCustomer` where docstatus < 2
+ and (%s like %s or customer_name like %s) order by
+ case when name like %s then 0 else 1 end,
+ case when customer_name like %s then 0 else 1 end,
+ name, customer_name limit %s, %s""" %
+ (", ".join(fields), searchfield, "%s", "%s", "%s", "%s", "%s", "%s"),
+ ("%%%s%%" % txt, "%%%s%%" % txt, "%%%s%%" % txt, "%%%s%%" % txt, start, page_len))
\ No newline at end of file
diff --git a/startup/query_handlers.py b/startup/query_handlers.py
index 5e28608..52a2e9e 100644
--- a/startup/query_handlers.py
+++ b/startup/query_handlers.py
@@ -1,5 +1,7 @@
from __future__ import unicode_literals
standard_queries = {
- "Warehouse": "stock.utils.get_warehouse_list"
+ "Warehouse": "stock.utils.get_warehouse_list",
+ "Customer": "selling.utils.get_customer_list",
+
}
\ No newline at end of file
diff --git a/stock/page/stock_ledger/stock_ledger.js b/stock/page/stock_ledger/stock_ledger.js
index 82230e4..6d945bb 100644
--- a/stock/page/stock_ledger/stock_ledger.js
+++ b/stock/page/stock_ledger/stock_ledger.js
@@ -114,8 +114,7 @@
},
toggle_enable_brand: function() {
- if(this.filter_inputs.item_code.val() ==
- this.filter_inputs.item_code.get(0).opts.default_value) {
+ if(!this.filter_inputs.item_code.val()) {
this.filter_inputs.brand.removeAttr("disabled");
} else {
this.filter_inputs.brand
diff --git a/stock/page/stock_level/stock_level.js b/stock/page/stock_level/stock_level.js
index df63d9b..3455e2b 100644
--- a/stock/page/stock_level/stock_level.js
+++ b/stock/page/stock_level/stock_level.js
@@ -127,8 +127,7 @@
},
toggle_enable_brand: function() {
- if(this.filter_inputs.item_code.val() ==
- this.filter_inputs.item_code.get(0).opts.default_value) {
+ if(!this.filter_inputs.item_code.val()) {
this.filter_inputs.brand.removeAttr("disabled");
} else {
this.filter_inputs.brand
diff --git a/utilities/__init__.py b/utilities/__init__.py
index 7c44ec3..079b03a 100644
--- a/utilities/__init__.py
+++ b/utilities/__init__.py
@@ -58,3 +58,12 @@
def validate_status(status, options):
if status not in options:
msgprint(_("Status must be one of ") + comma_or(options), raise_exception=True)
+
+def build_filter_conditions(filters):
+ conditions, filter_values = [], []
+ for key in filters:
+ conditions.append('`' + key + '` = %s')
+ filter_values.append(filters[key])
+
+ conditions = conditions and " and " + " and ".join(conditions) or ""
+ return conditions, filter_values
\ No newline at end of file