Batch related cleanup
diff --git a/erpnext/controllers/queries.py b/erpnext/controllers/queries.py
index d555532..31727ab 100644
--- a/erpnext/controllers/queries.py
+++ b/erpnext/controllers/queries.py
@@ -5,6 +5,7 @@
import frappe
from frappe.widgets.reportview import get_match_cond
from frappe.model.db_query import DatabaseQuery
+from frappe.utils import nowdate
def get_filters_cond(doctype, filters, conditions):
if filters:
@@ -154,8 +155,6 @@
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,
@@ -229,36 +228,48 @@
}, { "start": start, "page_len": page_len, "txt": ("%%%s%%" % txt) })
def get_batch_no(doctype, txt, searchfield, start, page_len, filters):
- from erpnext.controllers.queries import get_match_cond
+ if not filters.get("posting_date"):
+ filters["posting_date"] = nowdate()
- 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)
- %(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),
- 'start': start, 'page_len': page_len})
+ batch_nos = None
+ args = {
+ 'item_code': filters.get("item_code"),
+ 'warehouse': filters.get("warehouse"),
+ 'posting_date': filters.get('posting_date'),
+ 'txt': "%%%s%%" % txt,
+ 'mcond':get_match_cond(doctype),
+ "start": start,
+ "page_len": page_len
+ }
+
+ if args.get("warehouse"):
+ batch_nos = frappe.db.sql("""select sle.batch_no
+ from `tabStock Ledger Entry` sle, `tabBatch`
+ where sle.batch_no = `tabBatch`.name
+ and sle.item_code = '%(item_code)s'
+ and sle.warehouse = '%(warehouse)s'
+ and sle.batch_no like '%(txt)s'
+ and (ifnull(`tabBatch`.expiry_date, '2099-12-31') >= %(posting_date)s
+ or `tabBatch`.expiry_date = '')
+ and `tabBatch`.docstatus != 2
+ %(mcond)s
+ group by batch_no having sum(actual_qty) > 0
+ order by batch_no desc
+ limit %(start)s, %(page_len)s """
+ % args)
+
+ if batch_nos:
+ return batch_nos
else:
- 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,
- 'page_len': page_len})
+ return frappe.db.sql("""select name from `tabBatch`
+ where item = '%(item_code)s'
+ and docstatus < 2
+ and (ifnull(expiry_date, '2099-12-31') >= %(posting_date)s
+ or expiry_date = '' or expiry_date = "0000-00-00")
+ %(mcond)s
+ order by name desc
+ limit %(start)s, %(page_len)s
+ """ % args)
def get_account_list(doctype, txt, searchfield, start, page_len, filters):
filter_list = []