Fixes saving of Sales Order and fetching Prices frappe/frappe#478
diff --git a/erpnext/controllers/queries.py b/erpnext/controllers/queries.py
index e8cdc80..408f434 100644
--- a/erpnext/controllers/queries.py
+++ b/erpnext/controllers/queries.py
@@ -16,47 +16,47 @@
flt.append([doctype, f[0], '!=', f[1][1:]])
else:
flt.append([doctype, f[0], '=', f[1]])
-
+
query = DatabaseQuery(doctype)
query.filters = flt
query.conditions = conditions
query.build_filter_conditions()
-
- cond = ' and ' + ' and '.join(query.conditions)
+
+ cond = ' and ' + ' and '.join(query.conditions)
else:
cond = ''
return cond
# searches for active employees
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)s like "%(txt)s"
- or employee_name like "%(txt)s")
+ return frappe.db.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
- 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,
+ 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,
'mcond':get_match_cond(doctype), 'start': start, 'page_len': page_len})
# searches for leads which are not converted
-def lead_query(doctype, txt, searchfield, start, page_len, filters):
+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)s like "%(txt)s"
- or lead_name like "%(txt)s"
- or company_name like "%(txt)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,
+ 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,
'mcond':get_match_cond(doctype), 'start': start, 'page_len': page_len})
# searches for customer
@@ -68,82 +68,82 @@
else:
fields = ["name", "customer_name", "customer_group", "territory"]
- fields = ", ".join(fields)
+ fields = ", ".join(fields)
- return frappe.db.sql("""select %(field)s from `tabCustomer`
- where docstatus < 2
- and (%(key)s like "%(txt)s"
- or customer_name like "%(txt)s")
+ return frappe.db.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,
- 'txt': "%%%s%%" % txt, 'mcond':get_match_cond(doctype),
+ 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,
+ 'txt': "%%%s%%" % txt, 'mcond':get_match_cond(doctype),
'start': start, 'page_len': page_len})
# searches for supplier
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":
+ if supp_master_name == "Supplier Name":
fields = ["name", "supplier_type"]
- else:
+ else:
fields = ["name", "supplier_name", "supplier_type"]
- fields = ", ".join(fields)
+ fields = ", ".join(fields)
- return frappe.db.sql("""select %(field)s from `tabSupplier`
- where docstatus < 2
- and (%(key)s like "%(txt)s"
- or supplier_name like "%(txt)s")
+ return frappe.db.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,
- 'txt': "%%%s%%" % txt, 'mcond':get_match_cond(doctype), 'start': start,
+ 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,
+ 'txt': "%%%s%%" % txt, 'mcond':get_match_cond(doctype), 'start': start,
'page_len': page_len})
-
+
def tax_account_query(doctype, txt, searchfield, start, page_len, filters):
- tax_accounts = frappe.db.sql("""select name, parent_account from tabAccount
- where tabAccount.docstatus!=2
+ tax_accounts = frappe.db.sql("""select name, parent_account from tabAccount
+ where tabAccount.docstatus!=2
and account_type in (%s)
and group_or_ledger = 'Ledger'
and company = %s
and `%s` LIKE %s
- limit %s, %s""" %
- (", ".join(['%s']*len(filters.get("account_type"))), "%s", searchfield, "%s", "%s", "%s"),
- tuple(filters.get("account_type") + [filters.get("company"), "%%%s%%" % txt,
+ limit %s, %s""" %
+ (", ".join(['%s']*len(filters.get("account_type"))), "%s", searchfield, "%s", "%s", "%s"),
+ tuple(filters.get("account_type") + [filters.get("company"), "%%%s%%" % txt,
start, page_len]))
if not tax_accounts:
- tax_accounts = frappe.db.sql("""select name, parent_account from tabAccount
- where tabAccount.docstatus!=2 and group_or_ledger = 'Ledger'
- and company = %s and `%s` LIKE %s limit %s, %s"""
- % ("%s", searchfield, "%s", "%s", "%s"),
+ tax_accounts = frappe.db.sql("""select name, parent_account from tabAccount
+ where tabAccount.docstatus!=2 and group_or_ledger = 'Ledger'
+ and company = %s and `%s` LIKE %s limit %s, %s"""
+ % ("%s", searchfield, "%s", "%s", "%s"),
(filters.get("company"), "%%%s%%" % txt, start, page_len))
-
+
return tax_accounts
def item_query(doctype, txt, searchfield, start, page_len, filters):
from frappe.utils import nowdate
-
+
conditions = []
- 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,
+ 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,
if(length(tabItem.description) > 40, \
concat(substr(tabItem.description, 1, 40), "..."), description) as decription
- from tabItem
+ from tabItem
where tabItem.docstatus < 2
and (ifnull(tabItem.end_of_life, '') = '' or tabItem.end_of_life > %(today)s)
and (tabItem.`{key}` LIKE %(txt)s
- or tabItem.item_name LIKE %(txt)s)
+ or tabItem.item_name LIKE %(txt)s)
{fcond} {mcond}
limit %(start)s, %(page_len)s """.format(key=searchfield,
fcond=get_filters_cond(doctype, filters, conditions),
- mcond=get_match_cond(doctype)),
+ mcond=get_match_cond(doctype)),
{
"today": nowdate(),
"txt": "%%%s%%" % txt,
@@ -152,38 +152,38 @@
})
def bom(doctype, txt, searchfield, start, page_len, filters):
- conditions = []
+ 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%%" % txt,
- 'fcond': get_filters_cond(doctype, 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%%" % txt,
+ 'fcond': get_filters_cond(doctype, filters, conditions),
'mcond':get_match_cond(doctype), '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 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,
+
+ 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 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
+ 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
+ (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
+ 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
@@ -198,30 +198,30 @@
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)
+ 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),
+ 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'
+ 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,
+ 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})