fixes in stock reco, test case for serial no stock entry
diff --git a/patches/january_2013/stock_reconciliation.py b/patches/january_2013/stock_reconciliation.py
deleted file mode 100644
index e14044f..0000000
--- a/patches/january_2013/stock_reconciliation.py
+++ /dev/null
@@ -1,14 +0,0 @@
-import webnotes
-
-def execute():
- rename_fields()
-
-def rename_fields():
- webnotes.reload_doc("stock", "doctype", "stock_ledger_entry")
-
- args = [["Stock Ledger Entry", "bin_aqat", "qty_after_transaction"],
- ["Stock Ledger Entry", "fcfs_stack", "stock_queue"]]
- for doctype, old_fieldname, new_fieldname in args:
- webnotes.conn.sql("""update `tab%s` set `%s`=`%s`""" %
- (doctype, new_fieldname, old_fieldname))
-
\ No newline at end of file
diff --git a/patches/january_2013/stock_reconciliation_patch.py b/patches/january_2013/stock_reconciliation_patch.py
new file mode 100644
index 0000000..75dab76
--- /dev/null
+++ b/patches/january_2013/stock_reconciliation_patch.py
@@ -0,0 +1,35 @@
+import webnotes
+
+def execute():
+ webnotes.reload_doc("stock", "doctype", "stock_ledger_entry")
+
+ rename_fields()
+ store_stock_reco_json()
+
+def rename_fields():
+ args = [["Stock Ledger Entry", "bin_aqat", "qty_after_transaction"],
+ ["Stock Ledger Entry", "fcfs_stack", "stock_queue"]]
+ for doctype, old_fieldname, new_fieldname in args:
+ webnotes.conn.sql("""update `tab%s` set `%s`=`%s`""" %
+ (doctype, new_fieldname, old_fieldname))
+
+def store_stock_reco_json():
+ import os
+ import conf
+ import json
+ from webnotes.utils.datautils import read_csv_content
+ base_path = os.path.dirname(os.path.abspath(conf.__file__))
+
+ for reco, file_list in webnotes.conn.sql("""select name, file_list
+ from `tabStock Reconciliation`"""):
+ if file_list:
+ file_list = file_list.split("\n")
+ stock_reco_file = file_list[0].split(",")[1]
+ stock_reco_file = os.path.join(base_path, "public", "files", stock_reco_file)
+ if os.path.exists(stock_reco_file):
+ with open(stock_reco_file, "r") as open_reco_file:
+ content = open_reco_file.read()
+ content = read_csv_content(content)
+ webnotes.conn.set_value("Stock Reconciliation", reco, "reconciliation_json",
+ json.dumps(content, separators=(',', ': ')))
+
\ No newline at end of file
diff --git a/stock/doctype/serial_no/serial_no.py b/stock/doctype/serial_no/serial_no.py
index 190b92b..00f2de7 100644
--- a/stock/doctype/serial_no/serial_no.py
+++ b/stock/doctype/serial_no/serial_no.py
@@ -82,7 +82,6 @@
self.make_stock_ledger_entry(1)
webnotes.conn.set(self.doc, 'sle_exists', 1)
-
def make_stock_ledger_entry(self, qty):
from webnotes.model.code import get_obj
values = [{
@@ -103,7 +102,7 @@
'batch_no' : '',
'serial_no' : self.doc.name
}]
- get_obj('Stock Ledger', 'Stock Ledger').update_stock(values)
+ get_obj('Stock Ledger').update_stock(values)
# ---------
diff --git a/stock/doctype/serial_no/test_serial_no.py b/stock/doctype/serial_no/test_serial_no.py
new file mode 100644
index 0000000..1398e68
--- /dev/null
+++ b/stock/doctype/serial_no/test_serial_no.py
@@ -0,0 +1,93 @@
+# ERPNext - web based ERP (http://erpnext.com)
+# Copyright (C) 2012 Web Notes Technologies Pvt Ltd
+#
+# This program is free software: you can redistribute it and/or modify
+# it under the terms of the GNU General Public License as published by
+# the Free Software Foundation, either version 3 of the License, or
+# (at your option) any later version.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License
+# along with this program. If not, see <http://www.gnu.org/licenses/>.
+
+
+from __future__ import unicode_literals
+import unittest
+import webnotes
+from webnotes.tests import insert_test_data
+
+company = webnotes.conn.get_default("company")
+
+class TestSerialNo(unittest.TestCase):
+ def setUp(self):
+ webnotes.conn.begin()
+ self.insert_test_data()
+
+ def tearDown(self):
+ # print "Message Log:", "\n--\n".join(webnotes.message_log)
+ # print "Debug Log:", "\n--\n".join(webnotes.debug_log)
+ webnotes.conn.rollback()
+
+ def test_serialized_stock_entry(self):
+ data = [["2012-01-01", "01:00", "10001", 400, 400],
+ ["2012-01-01", "03:00", "10002", 500, 700],
+ ["2012-01-01", "04:00", "10003", 700, 700],
+ ["2012-01-01", "05:00", "10004", 1200, 800],
+ ["2012-01-01", "05:00", "10005", 800, 800],
+ ["2012-01-01", "02:00", "10006", 1200, 800],
+ ["2012-01-01", "06:00", "10007", 1500, 900]]
+ for d in data:
+ webnotes.model_wrapper([{
+ "doctype": "Serial No",
+ "item_code": "Nebula 8",
+ "warehouse": "Default Warehouse",
+ "status": "In Store",
+ "sle_exists": 0,
+ "purchase_date": d[0],
+ "purchase_time": d[1],
+ "serial_no": d[2],
+ "purchase_rate": d[3],
+ "company": company,
+ }]).insert()
+
+ for d in data:
+ res = webnotes.conn.sql("""select valuation_rate from `tabStock Ledger Entry`
+ where posting_date=%s and posting_time=%s and actual_qty=1 and serial_no=%s""",
+ (d[0], d[1], d[2]))
+ self.assertEquals(res[0][0], d[4])
+
+ print "deleted"
+ webnotes.delete_doc("Serial No", "10002")
+
+ test_data = [["10001", 400, 400],
+ ["10003", 700, 766.666667],
+ ["10004", 1200, 875],
+ ["10005", 800, 860],
+ ["10006", 1200, 800],
+ ["10007", 1500, 966.666667]]
+
+ for d in test_data:
+ res = webnotes.conn.sql("""select valuation_rate from `tabStock Ledger Entry`
+ where actual_qty=1 and serial_no=%s""", (d[0],))
+ self.assertEquals(res[0][0], d[2])
+
+ def insert_test_data(self):
+ # create default warehouse
+ if not webnotes.conn.exists("Warehouse", "Default Warehouse"):
+ webnotes.insert({"doctype": "Warehouse",
+ "warehouse_name": "Default Warehouse",
+ "warehouse_type": "Stores"})
+
+ # create UOM: Nos.
+ if not webnotes.conn.exists("UOM", "Nos"):
+ webnotes.insert({"doctype": "UOM", "uom_name": "Nos"})
+
+ # create item groups and items
+ insert_test_data("Item Group",
+ sort_fn=lambda ig: (ig[0].get('parent_item_group'), ig[0].get('name')))
+
+ insert_test_data("Item")
\ No newline at end of file
diff --git a/stock/doctype/stock_entry/stock_entry.py b/stock/doctype/stock_entry/stock_entry.py
index 975e12e..eaf7966 100644
--- a/stock/doctype/stock_entry/stock_entry.py
+++ b/stock/doctype/stock_entry/stock_entry.py
@@ -23,7 +23,8 @@
from webnotes.model.wrapper import getlist, copy_doclist
from webnotes.model.code import get_obj
from webnotes import msgprint, _
-from stock.utils import get_previous_sle, get_incoming_rate
+from stock.utils import get_incoming_rate
+from stock.stock_ledger import get_previous_sle
sql = webnotes.conn.sql
diff --git a/stock/doctype/stock_ledger/stock_ledger.py b/stock/doctype/stock_ledger/stock_ledger.py
index 3231850..10a905f 100644
--- a/stock/doctype/stock_ledger/stock_ledger.py
+++ b/stock/doctype/stock_ledger/stock_ledger.py
@@ -17,10 +17,9 @@
from __future__ import unicode_literals
import webnotes
-from webnotes.utils import add_days, cstr, flt, now, nowdate
-from webnotes.model import db_exists
+from webnotes.utils import add_days, cstr, flt, nowdate
from webnotes.model.doc import Document
-from webnotes.model.wrapper import getlist, copy_doclist
+from webnotes.model.wrapper import getlist
from webnotes.model.code import get_obj
from webnotes import session, msgprint
from stock.utils import get_valid_serial_nos
@@ -196,7 +195,7 @@
# get serial nos
if v.get("serial_no"):
serial_nos = get_valid_serial_nos(v["serial_no"], v['actual_qty'], v['item_code'])
-
+
# reverse quantities for cancel
if v.get('is_cancelled') == 'Yes':
v['actual_qty'] = -flt(v['actual_qty'])
@@ -216,19 +215,13 @@
def make_entry(self, args):
- sle = Document(doctype = 'Stock Ledger Entry')
- for k in args.keys():
- # adds warehouse_type
- if k == 'warehouse':
- sle.fields['warehouse_type'] = webnotes.conn.get_value('Warehouse' , args[k], 'warehouse_type')
- sle.fields[k] = args[k]
- sle_obj = get_obj(doc=sle)
-
- # validate
- sle_obj.validate()
- sle.save(new = 1)
- return sle.name
-
+ args.update({"doctype": "Stock Ledger Entry"})
+ if args.get("warehouse"):
+ args["warehouse_type"] = webnotes.conn.get_value('Warehouse' , args["warehouse"],
+ 'warehouse_type')
+ sle = webnotes.model_wrapper([args]).insert()
+ return sle.doc.name
+
def repost(self):
"""
Repost everything!
diff --git a/stock/doctype/stock_ledger_entry/stock_ledger_entry.py b/stock/doctype/stock_ledger_entry/stock_ledger_entry.py
index ea9ac12..9b73c6b 100644
--- a/stock/doctype/stock_ledger_entry/stock_ledger_entry.py
+++ b/stock/doctype/stock_ledger_entry/stock_ledger_entry.py
@@ -17,7 +17,7 @@
from __future__ import unicode_literals
import webnotes
-from webnotes.utils import cstr, cint, flt, cstr, getdate
+from webnotes.utils import cint, flt, getdate
sql = webnotes.conn.sql
msgprint = webnotes.msgprint
diff --git a/stock/doctype/stock_reconciliation/stock_reconciliation.js b/stock/doctype/stock_reconciliation/stock_reconciliation.js
index 3758c59..1e64965 100644
--- a/stock/doctype/stock_reconciliation/stock_reconciliation.js
+++ b/stock/doctype/stock_reconciliation/stock_reconciliation.js
@@ -20,15 +20,25 @@
if(this.frm.doc.docstatus===0) {
this.show_download_template();
this.show_upload();
+ if(this.frm.doc.reconciliation_json) {
+ this.frm.set_intro("You can submit this Stock Reconciliation.");
+ } else {
+ this.frm.set_intro("Download the template, fill in data and \
+ upload it.");
+ }
}
- if(this.frm.doc.reconciliation_json) this.show_reconciliation_data();
+ if(this.frm.doc.reconciliation_json) {
+ this.show_reconciliation_data();
+ this.show_download_reconciliation_data();
+ }
},
show_download_template: function() {
var me = this;
this.frm.add_custom_button("Download Template", function() {
this.title = "Stock Reconcilation Template";
- wn.downloadify([["Item Code", "Warehouse", "Quantity", "Valuation Rate"]], null, this);
+ wn.tools.downloadify([["Item Code", "Warehouse", "Quantity", "Valuation Rate"]], null,
+ this);
return false;
}, "icon-download");
},
@@ -42,7 +52,7 @@
wn.upload.make({
parent: $('#dit-upload-area'),
args: {
- method: 'stock.doctype.stock_reconciliation.stock_reconciliation.upload',
+ method: 'stock.doctype.stock_reconciliation.stock_reconciliation.upload'
},
sample_url: "e.g. http://example.com/somefile.csv",
callback: function(r) {
@@ -53,6 +63,15 @@
});
},
+ show_download_reconciliation_data: function() {
+ var me = this;
+ this.frm.add_custom_button("Download Reconcilation Data", function() {
+ this.title = "Stock Reconcilation Data";
+ wn.tools.downloadify(JSON.parse(me.frm.doc.reconciliation_json), null, this);
+ return false;
+ }, "icon-download");
+ },
+
show_reconciliation_data: function() {
if(this.frm.doc.reconciliation_json) {
var $wrapper = $(cur_frm.fields_dict.reconciliation_html.wrapper).empty();
@@ -62,8 +81,8 @@
var result = "";
var _render = header
- ? function(col) { return "<th>" + col + "</th>" }
- : function(col) { return "<td>" + col + "</td>" };
+ ? function(col) { return "<th>" + col + "</th>"; }
+ : function(col) { return "<td>" + col + "</td>"; };
$.each(data, function(i, row) {
result += "<tr>"
@@ -71,7 +90,7 @@
+ "</tr>";
});
return result;
- }
+ };
var $reconciliation_table = $("<div style='overflow-x: scroll;'>\
<table class='table table-striped table-bordered'>\
@@ -80,7 +99,7 @@
</table>\
</div>").appendTo($wrapper);
}
- },
+ }
});
cur_frm.cscript = new erpnext.stock.StockReconciliation({frm: cur_frm});
\ No newline at end of file
diff --git a/stock/doctype/stock_reconciliation/stock_reconciliation.py b/stock/doctype/stock_reconciliation/stock_reconciliation.py
index 3e3ad20..725bb5f 100644
--- a/stock/doctype/stock_reconciliation/stock_reconciliation.py
+++ b/stock/doctype/stock_reconciliation/stock_reconciliation.py
@@ -27,7 +27,6 @@
self.validate_data()
def on_submit(self):
- print "in stock reco"
self.insert_stock_ledger_entries()
def on_cancel(self):
@@ -94,7 +93,8 @@
if item.has_serial_no == "Yes":
raise webnotes.ValidationError, (_("Serialized Item: '") + item_code +
_("""' can not be managed using Stock Reconciliation.\
- You can add/delete Serial No directly, to modify stock of this item."""))
+ You can add/delete Serial No directly, \
+ to modify stock of this item."""))
# docstatus should be < 2
validate_cancelled_item(item_code, item.docstatus, verbose=0)
@@ -105,7 +105,8 @@
def insert_stock_ledger_entries(self):
""" find difference between current and expected entries
and create stock ledger entries based on the difference"""
- from stock.utils import get_previous_sle, get_valuation_method
+ from stock.utils import get_valuation_method
+ from stock.stock_ledger import get_previous_sle
row_template = ["item_code", "warehouse", "qty", "valuation_rate"]
@@ -119,9 +120,8 @@
"posting_time": self.doc.posting_time
})
-
change_in_qty = row.qty != "" and \
- (flt(row.qty) != flt(previous_sle.get("qty_after_transaction")))
+ (flt(row.qty) - flt(previous_sle.get("qty_after_transaction")))
change_in_rate = row.valuation_rate != "" and \
(flt(row.valuation_rate) != flt(previous_sle.get("valuation_rate")))
@@ -134,26 +134,33 @@
def sle_for_moving_avg(self, row, previous_sle, change_in_qty, change_in_rate):
"""Insert Stock Ledger Entries for Moving Average valuation"""
- def _get_incoming_rate(qty, valuation_rate, previous_qty, previous_valuation_rate):
+ def _get_incoming_rate(qty, valuation_rate, previous_qty,
+ previous_valuation_rate):
if previous_valuation_rate == 0:
- return valuation_rate
+ return flt(valuation_rate)
else:
+ if valuation_rate == "":
+ valuation_rate = previous_valuation_rate
+
return (qty * valuation_rate - previous_qty * previous_valuation_rate) \
/ flt(qty - previous_qty)
-
+
if change_in_qty:
+ # if change in qty, irrespective of change in rate
incoming_rate = _get_incoming_rate(flt(row.qty), flt(row.valuation_rate),
flt(previous_sle.qty_after_transaction),
flt(previous_sle.valuation_rate))
- self.insert_entries({"actual_qty": qty_diff, "incoming_rate": incoming_rate}, row)
+ self.insert_entries({"actual_qty": change_in_qty,
+ "incoming_rate": incoming_rate}, row)
elif change_in_rate and previous_sle.qty_after_transaction >= 0:
-
- incoming_rate = _get_incoming_rate(flt(previous_sle.qty_after_transaction) + 1,
+ # if no change in qty, but change in rate
+ # and positive actual stock before this reconciliation
+ incoming_rate = _get_incoming_rate(flt(previous_sle.qty_after_transaction)+1,
flt(row.valuation_rate), flt(previous_sle.qty_after_transaction),
flt(previous_sle.valuation_rate))
-
+
# +1 entry
self.insert_entries({"actual_qty": 1, "incoming_rate": incoming_rate}, row)
@@ -163,19 +170,37 @@
def sle_for_fifo(self, row, previous_sle, change_in_qty, change_in_rate):
"""Insert Stock Ledger Entries for FIFO valuation"""
previous_stock_queue = json.loads(previous_sle.stock_queue or "[]")
-
- if change_in_qty:
+ previous_stock_qty = sum((batch[0] for batch in previous_stock_queue))
+ previous_stock_value = sum((batch[0] * batch[1] for batch in \
+ previous_stock_queue))
+
+ def _insert_entries():
if previous_stock_queue != [[row.qty, row.valuation_rate]]:
# make entry as per attachment
- self.insert_entries({"actual_qty": row.qty, "incoming_rate": row.valuation_rate}, row)
-
+ self.insert_entries({"actual_qty": row.qty,
+ "incoming_rate": flt(row.valuation_rate)}, row)
+
# Make reverse entry
- qty = sum((flt(fifo_item[0]) for fifo_item in previous_stock_queue))
- self.insert_entries({"actual_qty": -1 * qty,
- "incoming_rate": qty < 0 and row.valuation_rate or 0}, row)
+ self.insert_entries({"actual_qty": -1 * previous_stock_qty,
+ "incoming_rate": previous_stock_qty < 0 and \
+ flt(row.valuation_rate) or 0}, row)
- elif change_in_rate:
- pass
+ if change_in_qty:
+ if row.valuation_rate == "":
+ # dont want change in valuation
+ if previous_stock_qty > 0:
+ # set valuation_rate as previous valuation_rate
+ row.valuation_rate = \
+ previous_stock_value / flt(previous_stock_qty)
+
+ _insert_entries()
+
+ elif change_in_rate and previous_stock_qty > 0:
+ # if no change in qty, but change in rate
+ # and positive actual stock before this reconciliation
+
+ row.qty = previous_stock_qty
+ _insert_entries()
def insert_entries(self, opts, row):
"""Insert Stock Ledger Entries"""
@@ -191,7 +216,7 @@
"is_cancelled": "No"
}
args.update(opts)
- print args
+
sle_wrapper = webnotes.model_wrapper([args]).insert()
update_entries_after(args)
diff --git a/stock/doctype/stock_reconciliation/stock_reconciliation.txt b/stock/doctype/stock_reconciliation/stock_reconciliation.txt
index f5cf623..272bf99 100644
--- a/stock/doctype/stock_reconciliation/stock_reconciliation.txt
+++ b/stock/doctype/stock_reconciliation/stock_reconciliation.txt
@@ -2,20 +2,21 @@
{
"owner": "Administrator",
"docstatus": 0,
- "creation": "2013-01-04 13:57:25",
+ "creation": "2013-01-09 11:24:35",
"modified_by": "Administrator",
- "modified": "2013-01-04 13:58:54"
+ "modified": "2013-01-10 19:26:28"
},
{
- "is_submittable": 1,
"allow_attach": 0,
+ "is_submittable": 1,
"allow_print": 1,
"search_fields": "reconciliation_date",
"module": "Stock",
- "allow_email": 1,
- "autoname": "SR/.######",
- "name": "__common__",
"doctype": "DocType",
+ "autoname": "SR/.######",
+ "description": "This tool helps you to update or fix the quantity and valuation of stock in the system. It is typically used to synchronise the system values and what actually exists in your warehouses.",
+ "allow_email": 1,
+ "name": "__common__",
"max_attachments": 1,
"allow_copy": 1
},
@@ -33,7 +34,6 @@
"read": 1,
"doctype": "DocPerm",
"parenttype": "DocType",
- "role": "Material Manager",
"parentfield": "permissions"
},
{
@@ -127,12 +127,13 @@
"hidden": 1
},
{
- "amend": 1,
+ "amend": 0,
"create": 1,
"doctype": "DocPerm",
"submit": 1,
"write": 1,
"cancel": 1,
+ "role": "Material Manager",
"permlevel": 0
},
{
@@ -142,6 +143,16 @@
"submit": 0,
"write": 0,
"cancel": 0,
+ "role": "Material Manager",
"permlevel": 1
+ },
+ {
+ "create": 1,
+ "doctype": "DocPerm",
+ "submit": 1,
+ "write": 1,
+ "cancel": 1,
+ "role": "System Manager",
+ "permlevel": 0
}
]
\ No newline at end of file
diff --git a/stock/doctype/stock_reconciliation/test_stock_reconciliation.py b/stock/doctype/stock_reconciliation/test_stock_reconciliation.py
index 48f0019..fadc3b4 100644
--- a/stock/doctype/stock_reconciliation/test_stock_reconciliation.py
+++ b/stock/doctype/stock_reconciliation/test_stock_reconciliation.py
@@ -19,8 +19,8 @@
import unittest
import webnotes
from webnotes.tests import insert_test_data
+from webnotes.utils import flt
import json
-from accounts.utils import get_fiscal_year
from pprint import pprint
company = webnotes.conn.get_default("company")
@@ -35,39 +35,67 @@
# print "Debug Log:", "\n--\n".join(webnotes.debug_log)
webnotes.conn.rollback()
- def test_reco_for_fifo(self):
+ def test_reco_for_fifo(self):
# [[qty, valuation_rate, posting_date, posting_time]]
input_data = [
- # [50, 1000, "2012-12-26", "12:00", 50000],
- # [5, 1000, "2012-12-26", "12:00", 5000],
- # [15, 1000, "2012-12-26", "12:00", 15000],
- # [25, 900, "2012-12-26", "12:00", 22500],
- # [20, 500, "2012-12-26", "12:00", 10000],
- # [50, 1000, "2013-01-01", "12:00", 50000],
- # [5, 1000, "2013-01-01", "12:00", 5000],
- ["", 800, "2012-12-26", "12:05", 12000],
- # [20, "", "2012-12-26", "12:05", 16000]
+ [50, 1000, "2012-12-26", "12:00", 50000],
+ [5, 1000, "2012-12-26", "12:00", 5000],
+ [15, 1000, "2012-12-26", "12:00", 15000],
+ [25, 900, "2012-12-26", "12:00", 22500],
+ [20, 500, "2012-12-26", "12:00", 10000],
+ [50, 1000, "2013-01-01", "12:00", 50000],
+ [5, 1000, "2013-01-01", "12:00", 5000],
+ ["", 1000, "2012-12-26", "12:05", 15000],
+ [20, "", "2012-12-26", "12:05", 16000],
+ [10, 2000, "2012-12-26", "12:10", 20000]
]
for d in input_data:
self.insert_existing_sle("FIFO")
- reco = self.submit_stock_reconciliation(d[0], d[1], d[2], d[3])
+ self.submit_stock_reconciliation(d[0], d[1], d[2], d[3])
- res = webnotes.conn.sql("""select stock_queue from `tabStock Ledger Entry`
+ res = webnotes.conn.sql("""select stock_value from `tabStock Ledger Entry`
where item_code = 'Android Jack D' and warehouse = 'Default Warehouse'
and posting_date = %s and posting_time = %s order by name desc limit 1""",
(d[2], d[3]))
- stock_value = sum([v[0]*v[1] for v in json.loads(res[0][0] or "[]")])
- self.assertEqual(stock_value, d[4])
+ # stock_value = sum([v[0]*v[1] for v in json.loads(res and res[0][0] or "[]")])
+ self.assertEqual(res and flt(res[0][0]) or 0, d[4])
self.tearDown()
self.setUp()
- def atest_reco_for_moving_average(self):
- webnotes.conn.set_value("Item", "Android Jack D", "valuation_method", "Moving Average")
+ def test_reco_for_moving_average(self):
+ # [[qty, valuation_rate, posting_date, posting_time]]
+ input_data = [
+ [50, 1000, "2012-12-26", "12:00", 50000],
+ [5, 1000, "2012-12-26", "12:00", 5000],
+ [15, 1000, "2012-12-26", "12:00", 15000],
+ [25, 900, "2012-12-26", "12:00", 22500],
+ [20, 500, "2012-12-26", "12:00", 10000],
+ [50, 1000, "2013-01-01", "12:00", 50000],
+ [5, 1000, "2013-01-01", "12:00", 5000],
+ ["", 1000, "2012-12-26", "12:05", 15000],
+ [20, "", "2012-12-26", "12:05", 18000],
+ [10, 2000, "2012-12-26", "12:10", 20000]
+ ]
+
+ for d in input_data:
+ self.insert_existing_sle("Moving Average")
+
+ self.submit_stock_reconciliation(d[0], d[1], d[2], d[3])
+
+ res = webnotes.conn.sql("""select stock_value from `tabStock Ledger Entry`
+ where item_code = 'Android Jack D' and warehouse = 'Default Warehouse'
+ and posting_date = %s and posting_time = %s order by name desc limit 1""",
+ (d[2], d[3]))
+
+ self.assertEqual(res and flt(res[0][0], 4) or 0, d[4])
+
+ self.tearDown()
+ self.setUp()
def submit_stock_reconciliation(self, qty, rate, posting_date, posting_time):
return webnotes.model_wrapper([{
diff --git a/stock/stock_ledger.py b/stock/stock_ledger.py
index 95c74d7..d9629d0 100644
--- a/stock/stock_ledger.py
+++ b/stock/stock_ledger.py
@@ -15,9 +15,9 @@
# along with this program. If not, see <http://www.gnu.org/licenses/>.
import webnotes
-from webnotes import msgprint, _
+from webnotes import msgprint
from webnotes.utils import cint, flt, cstr
-from stock.utils import _msgprint, get_valuation_method
+from stock.utils import get_valuation_method
import json
# future reposting
@@ -48,14 +48,14 @@
valuation_method = get_valuation_method(args["item_code"])
for sle in entries_to_fix:
- if sle.serial_nos or not cint(webnotes.conn.get_default("allow_negative_stock")):
+ if sle.serial_no or not cint(webnotes.conn.get_default("allow_negative_stock")):
# validate negative stock for serialized items, fifo valuation
# or when negative stock is not allowed for moving average
if not validate_negative_stock(qty_after_transaction, sle):
qty_after_transaction += flt(sle.actual_qty)
continue
- if sle.serial_nos:
+ if sle.serial_no:
valuation_rate, incoming_rate = get_serialized_values(qty_after_transaction, sle,
valuation_rate)
elif valuation_method == "Moving Average":
@@ -64,11 +64,11 @@
else:
valuation_rate, incoming_rate = get_fifo_values(qty_after_transaction, sle,
stock_queue)
-
+
qty_after_transaction += flt(sle.actual_qty)
# get stock value
- if sle.serial_nos:
+ if sle.serial_no:
stock_value = qty_after_transaction * valuation_rate
elif valuation_method == "Moving Average":
stock_value = (qty_after_transaction > 0) and \
@@ -76,17 +76,21 @@
else:
stock_value = sum((flt(batch[0]) * flt(batch[1]) for batch in stock_queue))
+ # print sle.posting_date, qty_after_transaction, incoming_rate, valuation_rate
+
# update current sle
webnotes.conn.sql("""update `tabStock Ledger Entry`
- set qty_after_transaction=%s, valuation_rate=%s, stock_queue=%s, stock_value=%s,
- incoming_rate = %s where name=%s""", (qty_after_transaction, valuation_rate,
+ set qty_after_transaction=%s, valuation_rate=%s, stock_queue=%s,
+ stock_value=%s, incoming_rate = %s where name=%s""",
+ (qty_after_transaction, valuation_rate,
json.dumps(stock_queue), stock_value, incoming_rate, sle.name))
-
+
if _exceptions:
_raise_exceptions(args, verbose)
# update bin
- webnotes.conn.sql("""update `tabBin` set valuation_rate=%s, actual_qty=%s, stock_value=%s,
+ webnotes.conn.sql("""update `tabBin` set valuation_rate=%s, actual_qty=%s,
+ stock_value=%s,
projected_qty = (actual_qty + indented_qty + ordered_qty + planned_qty - reserved_qty)
where item_code=%s and warehouse=%s""", (valuation_rate, qty_after_transaction,
stock_value, args["item_code"], args["warehouse"]))
@@ -151,7 +155,7 @@
def get_serialized_values(qty_after_transaction, sle, valuation_rate):
incoming_rate = flt(sle.incoming_rate)
actual_qty = flt(sle.actual_qty)
- serial_nos = cstr(sle.serial_nos).split("\n")
+ serial_no = cstr(sle.serial_no).split("\n")
if incoming_rate < 0:
# wrong incoming rate
@@ -160,8 +164,8 @@
# In case of delivery/stock issue, get average purchase rate
# of serial nos of current entry
incoming_rate = flt(webnotes.conn.sql("""select avg(ifnull(purchase_rate, 0))
- from `tabSerial No` where name in (%s)""" % (", ".join(["%s"]*len(serial_nos))),
- tuple(serial_nos))[0][0])
+ from `tabSerial No` where name in (%s)""" % (", ".join(["%s"]*len(serial_no))),
+ tuple(serial_no))[0][0])
if incoming_rate and not valuation_rate:
valuation_rate = incoming_rate
@@ -173,29 +177,31 @@
# calculate new valuation rate only if stock value is positive
# else it remains the same as that of previous entry
valuation_rate = new_stock_value / new_stock_qty
-
+
return valuation_rate, incoming_rate
def get_moving_average_values(qty_after_transaction, sle, valuation_rate):
incoming_rate = flt(sle.incoming_rate)
actual_qty = flt(sle.actual_qty)
- if not incoming_rate or actual_qty < 0:
+ if not incoming_rate:
# In case of delivery/stock issue in_rate = 0 or wrong incoming rate
incoming_rate = valuation_rate
- # val_rate is same as previous entry if :
- # 1. actual qty is negative(delivery note / stock entry)
- # 2. cancelled entry
- # 3. val_rate is negative
- # Otherwise it will be calculated as per moving average
+ elif qty_after_transaction < 0:
+ # if negative stock, take current valuation rate as incoming rate
+ valuation_rate = incoming_rate
+
new_stock_qty = qty_after_transaction + actual_qty
new_stock_value = qty_after_transaction * valuation_rate + actual_qty * incoming_rate
- if actual_qty > 0 and new_stock_qty > 0 and new_stock_value > 0:
+
+ if new_stock_qty > 0 and new_stock_value > 0:
valuation_rate = new_stock_value / flt(new_stock_qty)
elif new_stock_qty <= 0:
valuation_rate = 0.0
-
+
+ # NOTE: val_rate is same as previous entry if new stock value is negative
+
return valuation_rate, incoming_rate
def get_fifo_values(qty_after_transaction, sle, stock_queue):
@@ -255,4 +261,25 @@
if verbose:
msgprint(msg, raise_exception=1)
else:
- raise webnotes.ValidationError, msg
\ No newline at end of file
+ raise webnotes.ValidationError, msg
+
+def get_previous_sle(args):
+ """
+ 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
+
+ args = {
+ "item_code": "ABC",
+ "warehouse": "XYZ",
+ "posting_date": "2012-12-12",
+ "posting_time": "12:00",
+ "sle": "name of reference Stock Ledger Entry"
+ }
+ """
+ if not args.get("sle"): args["sle"] = ""
+
+ sle = get_stock_ledger_entries(args, ["name != %(sle)s",
+ "timestamp(posting_date, posting_time) <= timestamp(%(posting_date)s, %(posting_time)s)"],
+ "desc", "limit 1")
+ return sle and sle[0] or {}
\ No newline at end of file
diff --git a/stock/utils.py b/stock/utils.py
index 2c0eaef..a65406b 100644
--- a/stock/utils.py
+++ b/stock/utils.py
@@ -17,7 +17,7 @@
import webnotes
from webnotes import msgprint, _
import json
-from webnotes.utils import flt
+from webnotes.utils import flt, cstr
def validate_end_of_life(item_code, end_of_life=None, verbose=1):
if not end_of_life:
@@ -63,39 +63,9 @@
else:
raise webnotes.ValidationError, msg
-def get_previous_sle(args):
- """
- 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
-
- args = {
- "item_code": "ABC",
- "warehouse": "XYZ",
- "posting_date": "2012-12-12",
- "posting_time": "12:00",
- "sle": "name of reference Stock Ledger Entry"
- }
- """
- if not args.get("posting_date"): args["posting_date"] = "1900-01-01"
- if not args.get("posting_time"): args["posting_time"] = "12:00"
- if not args.get("sle"): args["sle"] = ""
-
- sle = webnotes.conn.sql("""
- select * from `tabStock Ledger Entry`
- where item_code = %(item_code)s
- and warehouse = %(warehouse)s
- and ifnull(is_cancelled, 'No') = 'No'
- and name != %(sle)s
- and timestamp(posting_date, posting_time) <= timestamp(%(posting_date)s, %(posting_time)s)
- order by timestamp(posting_date, posting_time) desc, name desc
- limit 1
- """, args, as_dict=1)
-
- return sle and sle[0] or {}
-
def get_incoming_rate(args):
"""Get Incoming Rate based on valuation method"""
+ from stock.stock_ledger import get_previous_sle
in_rate = 0
if args.get("serial_no"):
diff --git a/tests/data/item/nebula_8.txt b/tests/data/item/nebula_8.txt
new file mode 100644
index 0000000..a666379
--- /dev/null
+++ b/tests/data/item/nebula_8.txt
@@ -0,0 +1,31 @@
+[
+ {
+ "owner": "Administrator",
+ "docstatus": 0,
+ "creation": "2012-08-26 11:32:02",
+ "modified_by": "Administrator",
+ "modified": "2012-08-26 11:32:02"
+ },
+ {
+ "is_service_item": "No",
+ "description": "Nebula 8",
+ "item_code": "Nebula 8",
+ "is_stock_item": "Yes",
+ "inspection_required": "No",
+ "is_purchase_item": "No",
+ "name": "__common__",
+ "item_name": "Nebula 8",
+ "item_group": "Small Tablets",
+ "doctype": "Item",
+ "is_sales_item": "Yes",
+ "is_sub_contracted_item": "Yes",
+ "stock_uom": "Nos",
+ "has_batch_no": "No",
+ "has_serial_no": "Yes",
+ "default_warehouse": "Default Warehouse"
+ },
+ {
+ "name": "Nebula 8",
+ "doctype": "Item"
+ }
+]
\ No newline at end of file