Fixes related to finding prev sle
diff --git a/erpnext/patches/patch.py b/erpnext/patches/patch.py
index 9a82945..7e8a083 100644
--- a/erpnext/patches/patch.py
+++ b/erpnext/patches/patch.py
@@ -344,8 +344,7 @@
bin = sql("select name from tabBin")
for b in bin:
bobj = get_obj('Bin',b[0])
- prev_sle = bobj.get_prev_sle(posting_date = '2011-09-01', posting_time = '01:00')
- bobj.update_entries_after(posting_date = '2011-09-01', posting_time = '01:00', prev_sle = prev_sle)
+ bobj.update_entries_after(posting_date = '2011-09-01', posting_time = '01:00')
elif patch_no == 368:
from webnotes.utils import nestedset
t = [
diff --git a/erpnext/production/doctype/bill_of_materials/bill_of_materials.py b/erpnext/production/doctype/bill_of_materials/bill_of_materials.py
index 9fcfb81..85db9f1 100644
--- a/erpnext/production/doctype/bill_of_materials/bill_of_materials.py
+++ b/erpnext/production/doctype/bill_of_materials/bill_of_materials.py
@@ -411,8 +411,8 @@
if val_method == 'FIFO':
if warehouse:
bin_obj = get_obj('Warehouse',warehouse).get_bin(item_code)
- prev_sle = bin_obj.get_prev_sle('',nowdate(), (now().split(' ')[1])[:-3])
- fcfs_stack = prev_sle and (prev_sle[0][3] and eval(prev_sle[0][3]) or []) or []
+ prev_sle = bin_obj.get_prev_sle(nowdate(), (now().split(' ')[1])[:-3])
+ fcfs_stack = prev_sle and prev_sle['fcfs_stack'] and eval(prev_sle['fcfs_stack']) or []
else:
prev_sle = sql("select fcfs_stack from `tabStock Ledger Entry` where item_code = '%s' and posting_date <= '%s' order by posting_date DESC, posting_time DESC, name DESC limit 1" % (item_code, nowdate()))
fcfs_stack = prev_sle and (prev_sle[0][0] and eval(prev_sle[0][0]) or []) or []
diff --git a/erpnext/sandbox/testdata/stock_entry.py b/erpnext/sandbox/testdata/stock_entry.py
index 8f2a30c..bd1a109 100644
--- a/erpnext/sandbox/testdata/stock_entry.py
+++ b/erpnext/sandbox/testdata/stock_entry.py
@@ -43,7 +43,7 @@
'doctype': 'Stock Entry',
'posting_date': '2011-09-01',
'transfer_date': '2011-09-01',
- 'posting_time': '13:00',
+ 'posting_time': '12:00',
'company': 'comp',
'fiscal_year' : '2011-2012',
'purpose': 'Material Transfer',
diff --git a/erpnext/stock/doctype/bin/bin.py b/erpnext/stock/doctype/bin/bin.py
index cadab1e..e3af0a2 100644
--- a/erpnext/stock/doctype/bin/bin.py
+++ b/erpnext/stock/doctype/bin/bin.py
@@ -76,8 +76,29 @@
""", (self.doc.item_code, self.doc.warehouse), as_dict=1)
return sle and sle[0] or None
- def get_prev_sle(self, posting_date = '0000-00-00', posting_time = '00:00'):
- """get previous stock ledger entry"""
+ def get_prev_sle(self, posting_date = '1900-01-01', posting_time = '12:00', sle_id = ''):
+ """
+ get the last sle on or before the current time-bucket,
+ to get actual qty before transaction, this function
+ is called from various transaction like stock entry, reco etc
+ """
+
+ sle = sql("""
+ select * from `tabStock Ledger Entry`
+ where item_code = %s
+ and warehouse = %s
+ and ifnull(is_cancelled, 'No') = 'No'
+ and name != %s
+ and timestamp(posting_date, posting_time) <= timestamp(%s, %s)
+ order by timestamp(posting_date, posting_time) desc, name desc
+ limit 1
+ """, (self.doc.item_code, self.doc.warehouse, sle_id, posting_date, posting_time), as_dict=1)
+
+ return sle and sle[0] or {}
+
+
+ def get_sle_prev_timebucket(self, posting_date = '1900-01-01', posting_time = '12:00'):
+ """get previous stock ledger entry before current time-bucket"""
# get the last sle before the current time-bucket, so that all values
# are reposted from the current time-bucket onwards.
# this is necessary because at the time of cancellation, there may be
@@ -96,11 +117,7 @@
return sle and sle[0] or {}
-
-
- # --------------------------------------------------------------------------------------------------------------------------------------
- #
- # --------------------------------------------------------------------------------------------------------------------------------------
+ #-------------------------------------------------------------
def validate_negative_stock(self, cqty, s):
"""
validate negative stock for entries current datetime onwards
@@ -117,10 +134,12 @@
s['posting_date'], s['posting_time'], s['voucher_type'], s['voucher_no']), \
raise_exception=1)
- # ------------------------------------
- # get serialized inventory values
+
# ------------------------------------
def get_serialized_inventory_values(self, val_rate, in_rate, opening_qty, actual_qty, is_cancelled, serial_nos):
+ """
+ get serialized inventory values
+ """
if flt(in_rate) < 0: # wrong incoming rate
in_rate = val_rate
elif flt(in_rate) == 0: # In case of delivery/stock issue, get average purchase rate of serial nos of current entry
@@ -219,7 +238,7 @@
"""
# Get prev sle
- prev_sle = self.get_prev_sle(posting_date, posting_time)
+ prev_sle = self.get_sle_prev_timebucket(posting_date, posting_time)
# if no prev sle, start from the first one (for repost)
if not prev_sle:
@@ -246,7 +265,7 @@
and timestamp(posting_date, posting_time) > timestamp(%s, %s)
order by timestamp(posting_date, posting_time) asc, name asc""", \
(self.doc.item_code, self.doc.warehouse, \
- prev_sle.get('posting_date','0000-00-00'), prev_sle.get('posting_time', '00:00')), as_dict = 1)
+ prev_sle.get('posting_date','1900-01-01'), prev_sle.get('posting_time', '12:00')), as_dict = 1)
for sle in sll:
# block if stock level goes negative on any date
diff --git a/erpnext/stock/doctype/stock_entry/stock_entry.py b/erpnext/stock/doctype/stock_entry/stock_entry.py
index 2c36d0f..ab1538a 100644
--- a/erpnext/stock/doctype/stock_entry/stock_entry.py
+++ b/erpnext/stock/doctype/stock_entry/stock_entry.py
@@ -122,8 +122,6 @@
def get_raw_materials(self,pro_obj):
# get all items from flat bom except, child items of sub-contracted and sub assembly items and sub assembly items itself.
-# flat_bom_items = sql("select item_code, ifnull(sum(qty_consumed_per_unit), 0) * '%s', description, stock_uom from `tabFlat BOM Detail` where parent = '%s' and parent_bom = '%s' and is_pro_applicable = 'No' and docstatus < 2 group by item_code" % ((self.doc.process == 'Backflush') and flt(self.doc.fg_completed_qty) or flt(pro_obj.doc.qty), cstr(pro_obj.doc.bom_no), cstr(pro_obj.doc.bom_no)))
-# self.make_items_dict(flat_bom_items)
if pro_obj.doc.consider_sa_items == 'Yes':
# get all Sub Assembly items only from flat bom
@@ -132,7 +130,6 @@
if pro_obj.doc.consider_sa_items == 'No':
# get all sub assembly childs only from flat bom
- #select item_code,ifnull(sum(qty_consumed_per_unit),0)*'%s' as qty,description,stock_uom from ( select distinct fb.name,fb.description,fb.item_code,fb.qty_consumed_per_unit,fb.stock_uom from `tabFlat BOM Detail` fb,`tabBOM Material` bm where bm.parent=fb.parent_bom and bm.docstatus<2 and fb.is_pro_applicable='Yes' and fb.docstatus<2 and fb.parent='%s' and bm.bom_no is null)a group by item_code,stock_uom
fl_bom_sa_child_item = sql("select item_code,ifnull(sum(qty_consumed_per_unit),0)*'%s' as qty,description,stock_uom from ( select distinct fb.name,fb.description,fb.item_code,fb.qty_consumed_per_unit,fb.stock_uom from `tabFlat BOM Detail` fb,`tabBOM Material` bm where bm.parent=fb.parent_bom and bm.docstatus<2 and fb.is_pro_applicable='Yes' and fb.docstatus<2 and fb.parent='%s' and bm.bom_no is null)a group by item_code,stock_uom" % ((self.doc.process == 'Backflush') and flt(self.doc.fg_completed_qty) or flt(pro_obj.doc.qty), cstr(pro_obj.doc.bom_no)))
self.make_items_dict(fl_bom_sa_child_item)
@@ -179,11 +176,10 @@
if flt(d.transfer_qty) <= 0:
msgprint("Transfer Quantity can not be less than or equal to zero at Row No " + cstr(d.idx))
raise Exception
- if d.s_warehouse:
- if flt(d.transfer_qty) > flt(d.actual_qty):
- msgprint("Transfer Quantity is more than Available Qty at Row No " + cstr(d.idx))
- raise Exception
-
+ if d.s_warehouse and flt(d.transfer_qty) > flt(d.actual_qty):
+ msgprint("Transfer Quantity is more than Available Qty at Row No " + cstr(d.idx))
+ raise Exception
+
def calc_amount(self):
total_amount = 0
for d in getlist(self.doclist, 'mtn_details'):