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 = []
diff --git a/erpnext/selling/sales_common.js b/erpnext/selling/sales_common.js
index e905de1..c0f3507 100644
--- a/erpnext/selling/sales_common.js
+++ b/erpnext/selling/sales_common.js
@@ -500,20 +500,23 @@
})
}
},
-
+
batch_no: function(doc, cdt, cdn) {
var me = this;
var item = frappe.get_doc(cdt, cdn);
- return this.frm.call({
- method: "erpnext.stock.get_item_details.get_batch_qty",
- child: item,
- args: {
- "batch_no": item.batch_no,
- "warehouse": item.warehouse,
- "item_code": item.item_code
- },
- "fieldname": "actual_batch_qty"
- });
+
+ if(doc.warehouse && doc.item_code && doc.batch_no) {
+ return this.frm.call({
+ method: "erpnext.stock.get_item_details.get_batch_qty",
+ child: item,
+ args: {
+ "batch_no": item.batch_no,
+ "warehouse": item.warehouse,
+ "item_code": item.item_code
+ },
+ "fieldname": "actual_batch_qty"
+ });
+ }
},
set_dynamic_labels: function() {
diff --git a/erpnext/stock/doctype/stock_entry/stock_entry.js b/erpnext/stock/doctype/stock_entry/stock_entry.js
index 61cacce..99e6af4 100644
--- a/erpnext/stock/doctype/stock_entry/stock_entry.js
+++ b/erpnext/stock/doctype/stock_entry/stock_entry.js
@@ -378,18 +378,20 @@
// Overloaded query for link batch_no
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) {
- return{
- query: "erpnext.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
- }
- }
+ var item = locals[cdt][cdn];
+ if(!item.item_code) {
+ frappe.throw(__("Please enter Item Code to get batch nos"));
} else {
- msgprint(__("Please enter Item Code to get batch no"));
+ filters = {
+ 'item_code': item.item_code,
+ 'posting_date': me.frm.doc.posting_date,
+ }
+ if(item.s_warehouse) filters["warehouse"] = item.s_warehouse
+
+ return {
+ query : "erpnext.controllers.queries.get_batch_no",
+ filters: filters
+ }
}
}
diff --git a/erpnext/stock/doctype/stock_entry/stock_entry.py b/erpnext/stock/doctype/stock_entry/stock_entry.py
index 4f45bf8..a9b7fe5 100644
--- a/erpnext/stock/doctype/stock_entry/stock_entry.py
+++ b/erpnext/stock/doctype/stock_entry/stock_entry.py
@@ -5,7 +5,7 @@
import frappe
import frappe.defaults
-from frappe.utils import cstr, cint, flt, comma_or, nowdate
+from frappe.utils import cstr, cint, flt, comma_or
from frappe import _
from erpnext.stock.utils import get_incoming_rate
@@ -680,50 +680,6 @@
return result[start:start+page_len]
-def get_batch_no(doctype, txt, searchfield, start, page_len, filters):
- if not filters.get("posting_date"):
- filters["posting_date"] = nowdate()
-
- batch_nos = None
- args = {
- 'item_code': filters.get("item_code"),
- 's_warehouse': filters.get('s_warehouse'),
- 'posting_date': filters.get('posting_date'),
- 'txt': "%%%s%%" % txt,
- 'mcond':get_match_cond(doctype),
- "start": start,
- "page_len": page_len
- }
-
- if filters.get("s_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 = '%(s_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 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_doc, parentfields):
"""return item codes filtered from doc, which are stock items"""
if isinstance(parentfields, basestring):
diff --git a/erpnext/stock/get_item_details.py b/erpnext/stock/get_item_details.py
index 6d01c33..cbf5f34 100644
--- a/erpnext/stock/get_item_details.py
+++ b/erpnext/stock/get_item_details.py
@@ -279,13 +279,13 @@
}))
def get_actual_batch_qty(batch_no,warehouse,item_code):
- actual_batch_qty = 0
- if batch_no:
- actual_batch_qty = flt(frappe.db.sql("""select sum(actual_qty)
- from `tabStock Ledger Entry`
- where warehouse=%s and item_code=%s and batch_no=%s""",
- (warehouse, item_code, batch_no))[0][0])
- return actual_batch_qty
+ actual_batch_qty = 0
+ if batch_no:
+ actual_batch_qty = flt(frappe.db.sql("""select sum(actual_qty)
+ from `tabStock Ledger Entry`
+ where warehouse=%s and item_code=%s and batch_no=%s""",
+ (warehouse, item_code, batch_no))[0][0])
+ return actual_batch_qty
@frappe.whitelist()
def get_conversion_factor(item_code, uom):