[fix] [stock] batch no query in stock entry
diff --git a/stock/doctype/stock_entry/stock_entry.js b/stock/doctype/stock_entry/stock_entry.js
index 3fc03cb..123c9fb 100644
--- a/stock/doctype/stock_entry/stock_entry.js
+++ b/stock/doctype/stock_entry/stock_entry.js
@@ -313,23 +313,14 @@
cur_frm.fields_dict['mtn_details'].grid.get_field('batch_no').get_query = function(doc, cdt, cdn) {
var d = locals[cdt][cdn];
if(d.item_code) {
- if (d.s_warehouse) {
- return{
- query: "stock.doctype.stock_entry.stock_entry.get_batch_no",
- filters:{
- 'item_code': d.item_code,
- 's_warehouse': d.s_warehouse,
- 'posting_date': doc.posting_date
- }
- }
- } else {
- return{
- filters:[
- ['Batch', 'item', '=', d.item_code],
- ['Batch', 'expiry_date', '>=', doc.posting_date]
- ]
+ return{
+ query: "stock.doctype.stock_entry.stock_entry.get_batch_no",
+ filters:{
+ 'item_code': d.item_code,
+ 's_warehouse': d.s_warehouse,
+ 'posting_date': doc.posting_date
}
- }
+ }
} else {
msgprint("Please enter Item Code to get batch no");
}
diff --git a/stock/doctype/stock_entry/stock_entry.py b/stock/doctype/stock_entry/stock_entry.py
index d985432..9f88774 100644
--- a/stock/doctype/stock_entry/stock_entry.py
+++ b/stock/doctype/stock_entry/stock_entry.py
@@ -18,7 +18,7 @@
import webnotes
import webnotes.defaults
-from webnotes.utils import cstr, cint, flt, comma_or
+from webnotes.utils import cstr, cint, flt, comma_or, nowdate
from webnotes.model.doc import Document, addchild
from webnotes.model.bean import getlist
from webnotes.model.code import get_obj
@@ -710,7 +710,6 @@
return result and result[0] or {}
def query_sales_return_doc(doctype, txt, searchfield, start, page_len, filters):
- from controllers.queries import get_match_cond
conditions = ""
if doctype == "Sales Invoice":
conditions = "and update_stock=1"
@@ -726,7 +725,6 @@
as_list=True)
def query_purchase_return_doc(doctype, txt, searchfield, start, page_len, filters):
- 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
@@ -761,24 +759,50 @@
return result[start:start+page_len]
def get_batch_no(doctype, txt, searchfield, start, page_len, filters):
- from controllers.queries import get_match_cond
-
- return webnotes.conn.sql("""select batch_no from `tabStock Ledger Entry` sle
+ if not filters.get("posting_date"):
+ filters["posting_date"] = nowdate()
+
+ batch_nos = None
+ args = {
+ '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
+ }
+
+ if filters.get("s_warehouse"):
+ batch_nos = 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)
+ where name = sle.batch_no
+ and (ifnull(expiry_date, '2099-12-31') >= %(posting_date)s
+ or expiry_date = '')
+ 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})
+ limit %(start)s, %(page_len)s """
+ % args)
+
+ if batch_nos:
+ return batch_nos
+ else:
+ return webnotes.conn.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_stock_items_for_return(ref_doclist, parentfields):
"""return item codes filtered from doclist, which are stock items"""