rewritten stock reconciliation
diff --git a/erpnext/sandbox/test_stock_reco.py b/erpnext/sandbox/test_stock_reco.py
new file mode 100644
index 0000000..bcde49e
--- /dev/null
+++ b/erpnext/sandbox/test_stock_reco.py
@@ -0,0 +1,94 @@
+import unittest
+
+import webnotes
+import webnotes.profile
+webnotes.user = webnotes.profile.Profile()
+
+
+from webnotes.model.doc import Document
+from webnotes.model.code import get_obj
+from webnotes.utils import cstr, flt
+from webnotes.model.doclist import getlist
+sql = webnotes.conn.sql
+
+from sandbox.testdata.masters import *
+from sandbox.testdata.sle_data import sle, bin
+from sandbox.testdata.stock_reco import *
+#----------------------------------------------------------
+
+
+class TestStockEntry(unittest.TestCase):
+ def assertDoc(self, lst):
+ """assert all values"""
+ for d in lst:
+ cl, vl = [], []
+ for k in d.keys():
+ if k!='doctype':
+ cl.append('%s=%s' % (k, '%s'))
+ vl.append(d[k])
+
+ self.assertTrue(sql("select name from `tab%s` where %s limit 1" % (d['doctype'], ' and '.join(cl)), vl))
+
+ #===========================================================================
+ def setUp(self):
+ print "====================================="
+ webnotes.conn.begin()
+ create_master_records()
+ print 'Master Data Created'
+
+ for d in sle:
+ d.save(1)
+ print "Existing SLE created"
+
+ bin.save(1)
+
+ sreco.save(1)
+ print "Stock Reco saved"
+
+ #===========================================================================
+ def test_diff_in_both(self):
+ reco = get_obj('Stock Reconciliation', sreco.name)
+ reco.doc.docstatus = 1
+ reco.doc.save()
+ reco.validate()
+ reco.on_submit()
+ print "Stock Reco submitted"
+
+ print "Checking stock ledger entry........."
+ self.assertDoc(self.get_expected_sle('diff_in_both'))
+
+ #===========================================================================
+ def tearDown(self):
+ webnotes.conn.rollback()
+
+ # Expected Result Set
+ #===================================================================================================
+ def get_expected_sle(self, action):
+ expected_sle = {
+ 'diff_in_both': [{
+ 'doctype': 'Stock Ledger Entry',
+ 'item_code':'it',
+ 'warehouse':'wh1',
+ 'voucher_type': 'Stock Reconciliation',
+ 'voucher_no': sreco.name,
+ 'actual_qty': 15,
+ 'bin_aqat': 20,
+ 'valuation_rate': 150,
+ #'stock_value': 3000,
+ 'is_cancelled': 'No'
+ },{
+ 'doctype': 'Stock Ledger Entry',
+ 'posting_date': '2011-09-10',
+ 'posting_time': '15:00',
+ 'item_code': 'it',
+ 'warehouse': 'wh1',
+ 'actual_qty': 20,
+ 'incoming_rate': 200,
+ 'bin_aqat': 40,
+ 'valuation_rate': 175,
+ #'stock_value': 4500,
+ 'is_cancelled': 'No'
+ }
+ ]
+ }
+ return expected_sle[action]
diff --git a/erpnext/sandbox/testdata/sle_data.py b/erpnext/sandbox/testdata/sle_data.py
new file mode 100644
index 0000000..eab0376
--- /dev/null
+++ b/erpnext/sandbox/testdata/sle_data.py
@@ -0,0 +1,85 @@
+from webnotes.model.doc import Document
+
+# Existing SLE data
+#---------------------------
+
+sle = [
+ Document(
+ fielddata = {
+ 'doctype': 'Stock Ledger Entry',
+ 'name': 'sle1',
+ 'posting_date': '2011-09-01',
+ 'posting_time': '12:00',
+ 'item_code': 'it',
+ 'warehouse': 'wh1',
+ 'actual_qty': 10,
+ 'incoming_rate': 100,
+ 'bin_aqat': 10,
+ 'valuation_rate': 100,
+ 'fcfs_stack': '',
+ 'stock_value': 1000,
+ 'is_cancelled': 'No'
+ }
+ ),
+ Document(
+ fielddata = {
+ 'doctype': 'Stock Ledger Entry',
+ 'name': 'sle2',
+ 'posting_date': '2011-09-01',
+ 'posting_time': '12:00',
+ 'item_code': 'it',
+ 'warehouse': 'wh1',
+ 'actual_qty': -5,
+ 'incoming_rate': 100,
+ 'bin_aqat': 5,
+ 'valuation_rate': 100,
+ 'fcfs_stack': '',
+ 'stock_value': 500,
+ 'is_cancelled': 'No'
+ }
+ ),
+ Document(
+ fielddata = {
+ 'doctype': 'Stock Ledger Entry',
+ 'name': 'sle3',
+ 'posting_date': '2011-09-10',
+ 'posting_time': '15:00',
+ 'item_code': 'it',
+ 'warehouse': 'wh1',
+ 'actual_qty': 20,
+ 'incoming_rate': 200,
+ 'bin_aqat': 25,
+ 'valuation_rate': 180,
+ 'fcfs_stack': '',
+ 'stock_value': 4500,
+ 'is_cancelled': 'No'
+ }
+ ),
+ Document(
+ fielddata = {
+ 'doctype': 'Stock Ledger Entry',
+ 'name': 'sle4',
+ 'posting_date': '2011-09-15',
+ 'posting_time': '09:30',
+ 'item_code': 'it',
+ 'warehouse': 'wh1',
+ 'actual_qty': -5,
+ 'incoming_rate': 180,
+ 'bin_aqat': 20,
+ 'valuation_rate': 180,
+ 'fcfs_stack': '',
+ 'stock_value': 3600,
+ 'is_cancelled': 'No'
+ }
+ )
+]
+
+bin = Document(
+ fielddata = {
+ 'doctype': 'Bin',
+ 'name': 'bin01',
+ 'item_code': 'it',
+ 'warehouse': 'wh1',
+ 'actual_qty': 20,
+ }
+)
diff --git a/erpnext/sandbox/testdata/stock_reco.py b/erpnext/sandbox/testdata/stock_reco.py
new file mode 100644
index 0000000..efcbbc5
--- /dev/null
+++ b/erpnext/sandbox/testdata/stock_reco.py
@@ -0,0 +1,43 @@
+from webnotes.model.doc import Document
+
+# Stock Reconciliation
+#---------------------------
+
+sreco = Document(
+ fielddata = {
+ 'doctype': 'Stock Reconciliation',
+ 'name': 'sreco',
+ 'reconciliation_date': '2011-09-08',
+ 'reconciliation_time': '20:00',
+ }
+ )
+
+# diff in both
+csv_data1 = [
+ ['Item', 'Warehouse', 'Quantity', 'Rate'],
+ ['it', 'wh1', 20, 150]
+]
+
+# diff in qty, no rate
+csv_data2 = [
+ ['Item', 'Warehouse', 'Quantity'],
+ ['it', 'wh1', 20]
+]
+
+# diff in rate, no qty
+csv_data3 = [
+ ['Item', 'Warehouse', 'Rate'],
+ ['it', 'wh1', 200]
+]
+
+# diff in rate, same qty
+csv_data4 = [
+ ['Item', 'Warehouse', 'Quantity', 'Rate'],
+ ['it', 'wh1', 5, 200]
+]
+
+# no diff
+csv_data1 = [
+ ['Item', 'Warehouse', 'Quantity', 'Rate'],
+ ['it', 'wh1', 5, 100]
+]
diff --git a/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.js b/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.js
index de89d80..2e428df 100644
--- a/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.js
+++ b/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.js
@@ -1,37 +1,7 @@
-cur_frm.cscript.onload = function(doc, cdt, cdn) {
- cfn_set_fields(doc, cdt, cdn);
+cur_frm.cscript.refresh = function(doc) {
+ if (doc.docstatus) hide_field('Steps');
}
-cur_frm.cscript.refresh = function(doc, cdt, cdn) {
- cfn_set_fields(doc, cdt, cdn);
+cur_frm.cscript['Download Template'] = function(doc, cdt, cdn) {
+ $c_obj_csv(make_doclist(cdt, cdn), 'get_template', '');
}
-
-var cfn_set_fields = function(doc, cdt, cdn) {
- refresh_field('remark');
- refresh_field('next_step');
- if (doc.docstatus == 0 && doc.next_step == 'Upload File and Save Document')
- doc.next_step = 'Validate Data';
-
- if (! doc.file_list)
- doc.next_step = 'Upload File and Save Document'
-
- if (doc.next_step == 'Upload File and Save Document') {
- //alert("Upload File and Save Document");
- cur_frm.clear_tip();
- cur_frm.set_tip("Please Enter Reconciliation Date and Attach CSV File with Columns in Following Sequence:-");
- cur_frm.append_tip("Item Code , Warehouse , Qty , MAR");
- hide_field("Validate Data");
- }
- if (doc.next_step == 'Validate Data') {
- //alert("Validate Data");
- cur_frm.clear_tip();
- cur_frm.set_tip("Please Check Remarks");
- unhide_field("Validate Data");
- }
- if (doc.next_step == 'Submit Document') {
- //alert('Submit Document');
- cur_frm.clear_tip();
- cur_frm.set_tip("Please Submit the document.");
- hide_field("Validate Data");
- }
-}
\ No newline at end of file
diff --git a/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py b/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py
index e133d6f..7b9d294 100644
--- a/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py
+++ b/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py
@@ -1,270 +1,23 @@
-# Please edit this list and import only required elements
import webnotes
-
-from webnotes.utils import add_days, add_months, add_years, cint, cstr, date_diff, default_fields, flt, fmt_money, formatdate, generate_hash, getTraceback, get_defaults, get_first_day, get_last_day, getdate, has_common, month_name, now, nowdate, replace_newlines, sendmail, set_default, str_esc_quote, user_format, validate_email_add
-from webnotes.model import db_exists
-from webnotes.model.doc import Document, addchild, removechild, getchildren, make_autoname, SuperDocType
-from webnotes.model.doclist import getlist, copy_doclist
-from webnotes.model.code import get_obj, get_server_obj, run_server_obj, updatedb, check_syntax
-from webnotes import session, form, is_testing, msgprint, errprint
-
-set = webnotes.conn.set
+from webnotes.utils import cstr, flt, get_defaults, nowdate
+from webnotes import msgprint
+from webnotes.model.code import get_obj
sql = webnotes.conn.sql
-get_value = webnotes.conn.get_value
-in_transaction = webnotes.conn.in_transaction
-convert_to_lists = webnotes.conn.convert_to_lists
# -----------------------------------------------------------------------------------------
-
class DocType:
def __init__(self, doc, doclist=[]):
self.doc = doc
self.doclist = doclist
- self.label = { 'item_code': 0 , 'warehouse': 1 , 'qty': 2, 'mar': 3,'stock_uom':4, 'actual_qty':5, 'diff': 6} # with mar
+ self.validated = 1
+ self.data = []
- # autoname
- #-----------------
- def autoname(self):
- self.doc.name = make_autoname('SR/' + self.doc.fiscal_year + '/.######')
+ def get_template(self):
+ return [['Item Code', 'Warehouse', 'Quantity', 'Valuation Rate']]
- # -----------------
- # update next step
- # -----------------
- def update_next_step(self,next_step):
- sql("update `tabStock Reconciliation` set next_step = '%s' where name = '%s'" % (next_step,self.doc.name))
-
-
- # -----------
- # add remark
- # -----------
- def add_remark(self, text, next_step, first_time = 0):
- if first_time:
- sql("update `tabStock Reconciliation` set remark = '' where name = '%s'" % self.doc.name)
- else:
- sql("update `tabStock Reconciliation` set remark = concat(remark, '%s'), modified = '%s' where name = '%s'" % (text + "<br>", nowdate(), self.doc.name))
- self.update_next_step(next_step)
-
-
- # --------------
- # validate item
- # --------------
- def validate_item(self, item, count, check_item = 1):
- det = sql("select item_code, has_serial_no from `tabItem` where name = '%s'"% cstr(item), as_dict = 1)
- if not det:
- text = "Item: " + cstr(item) + " mentioned at Row No. " + cstr(count) + "does not exist in the system"
- msgprint(text)
- self.add_remark(text, 'Validate Data', 0)
- check_item = 0
- elif det and det[0]['has_serial_no'] == 'Yes':
- text = "You cannot make Stock Reconciliation of items having serial no. You can directly upload serial no to update their inventory. Please remove Item Code : %s at Row No. %s" %(cstr(item), cstr(count))
- msgprint(text)
- self.add_remark(text, 'Validate Data', 0)
- check_item = 0
- return check_item
-
-
- # -------------------
- # validate warehouse
- # -------------------
- def validate_warehouse(self,wh,count, check_warehouse = 1):
- if not sql("select name from `tabWarehouse` where name = '%s'" % cstr(wh)):
- text = "Warehouse: " + cstr(wh) + " mentioned at Row No. " + cstr(count) + "does not exist in the system"
- self.add_remark(text,'Validate Data',0)
- check_warehouse = 0
- return check_warehouse
-
-
- # ---------------------------
- # validate data of .csv file
- # ---------------------------
- def validate_data(self,stock):
- self.add_remark('','Validate Data',1)
-
- # check whether file uploaded
- if not self.doc.file_list:
- set(self.doc,'next_step','Upload File and Save Document')
- msgprint("Please Attach File", raise_exception=1)
-
- # validate item and warehouse
- check_item,check_warehouse,count = 1, 1, 1
- for s in stock:
- count +=1
- check_item = self.validate_item(s[self.label['item_code']],count) or 0
- check_warehouse = self.validate_warehouse(s[self.label['warehouse']],count) or 0
-
- if check_item and check_warehouse:
- text = "Validation Completed Successfully..."
- self.add_remark(text,'Submit Document',0)
- return check_item and check_warehouse
-
-
- # ------------------------------
- # convert lines in .csv to list
- # ------------------------------
- def convert_into_list(self, stock, submit):
- count, st_list = 1, []
- for s in stock:
- if submit and len(s) != 4:
- msgprint("Data entered at Row No " + cstr(count) + " in Attachment File is not in correct format.", raise_exception=1)
-
- l = [s[0].encode("ascii"), s[1].encode("ascii"), s[2].encode("ascii"), s[3].encode("ascii")]
- st_list.append(l)
- count += 1
- return st_list
-
- # ------------------
- # get current stock
- # ------------------
- def get_current_stock(self, item_code, warehouse):
- bin = sql("select name from `tabBin` where item_code = '%s' and warehouse = '%s'" % (item_code, warehouse))
- prev_sle = bin and get_obj('Bin', bin[0][0]).get_prev_sle(self.doc.reconciliation_date,self.doc.reconciliation_time) or {}
- stock_uom = sql("select stock_uom from `tabItem` where name = %s",item_code)
- return {'actual_qty': prev_sle.get('bin_aqat', 0), 'stock_uom': stock_uom[0][0]}
-
-
- # -----------
- # update mar
- # -----------
- def update_mar(self, d, qty_diff):
- """
- update item valuation in previous date and also on post date if no qty diff
- """
-
- self.update_entries_pre_date(d)
-
- if not flt(d[self.label['qty']]) and not flt(d[self.label['actual_qty']]):
- # seems like a special condition when there is no actual quanitity but there is a rate, may be only for setting a rate!
- self.make_sl_entry(1,d,1)
- self.make_sl_entry(1,d,-1)
- elif not qty_diff:
- self.update_entries_post_date(d)
-
- # update valuation rate as csv file in all sle before reconciliation date
- # ------------------------------------------------------------------------
- def update_entries_pre_date(self, d):
- mar = flt(d[self.label['mar']])
-
- # previous sle
- prev_sle = sql("""
- select name, fcfs_stack
- from `tabStock Ledger Entry`
- where item_code = %s
- and warehouse = %s
- and ifnull(is_cancelled, 'No') = 'No'
- and timestamp(posting_date, posting_time) <= timestamp(%s, %s)
- """, (d[self.label['item_code']], d[self.label['warehouse']], self.doc.reconciliation_date, self.doc.reconciliation_time))
-
- for each in prev_sle:
- # updated fifo stack
- fstack = each[1] and [[i[0], mar] for i in eval(each[1])] or ''
-
- # update incoming rate, valuation rate, stock value and fifo stack
- sql("""update `tabStock Ledger Entry`
- set incoming_rate = %s, valuation_rate = %s, stock_value = bin_aqat*%s, fcfs_stack = %s
- where name = %s
- """, (mar, mar, mar, cstr(fstack), each[0]))
-
-
- # Update item valuation in all sle after the reconcliation date
- # ---------------------------------------------------------
- def update_entries_post_date(self, d):
- bin = sql("select name from `tabBin` where item_code = '%s' and warehouse = '%s'" % (d[self.label['item_code']], d[self.label['warehouse']]))
- bin_obj = get_obj('Bin', bin[0][0])
-
- # update valuation in sle posted after reconciliation datetime
- bin_obj.update_entries_after(posting_date = self.doc.reconciliation_date, posting_time = self.doc.reconciliation_time)
-
- # --------------
- # make sl entry
- # --------------
- def make_sl_entry(self, update_stock, stock, diff):
- values = []
- values.append({
- 'item_code' : stock[self.label['item_code']],
- 'warehouse' : stock[self.label['warehouse']],
- 'transaction_date' : now(),
- 'posting_date' : self.doc.reconciliation_date,
- 'posting_time' : self.doc.reconciliation_time,
- 'voucher_type' : self.doc.doctype,
- 'voucher_no' : self.doc.name,
- 'voucher_detail_no' : self.doc.name,
- 'actual_qty' : flt(update_stock) * flt(diff),
- 'stock_uom' : stock[self.label['stock_uom']],
- 'incoming_rate' : stock[self.label['mar']] or 0,
- 'company' : self.doc.company,
- 'fiscal_year' : self.doc.fiscal_year,
- 'is_cancelled' : (update_stock==1) and 'No' or 'Yes',
- 'batch_no' : '',
- 'serial_no' : ''
- })
-
- get_obj('Stock Ledger', 'Stock Ledger').update_stock(values)
-
-
- # -----------------------
- # get stock reco details
- # -----------------------
- def get_reconciliation_stock_details(self,submit = 0):
- import csv
- stock = csv.reader(self.get_csv_file_data().splitlines())
- stock = self.convert_into_list(stock, submit)
- if stock[0][0] and stock[0][0].strip()=='Item Code':
- stock.pop(0) # remove the labels
- check = self.validate_data(stock)
- if not check:
- return 0
- return stock
-
- # validate date and time
- # ------------------------
- def validate_datetime(self):
- if not self.doc.reconciliation_date:
- msgprint("Please Enter Reconciliation Date.", raise_exception=1)
- if not self.doc.reconciliation_time:
- msgprint("Please Enter Reconciliation Time.", raise_exception=1)
-
-
-
- # ----------------------
- # stock reconciliations
- # ----------------------
- def stock_reconciliations(self, submit = 0):
- self.validate_datetime()
-
- # get reco data
- rec_stock_detail = self.get_reconciliation_stock_details(submit) or []
- if not rec_stock_detail:
- msgprint("Please Check Remarks", raise_exception=1)
-
- count = 1
- for stock in rec_stock_detail:
- count += 1
-
- # Get qty as per system
- cur_stock_detail = self.get_current_stock(stock[self.label['item_code']],stock[self.label['warehouse']])
- stock.append(cur_stock_detail['stock_uom'])
- stock.append(cur_stock_detail['actual_qty'])
-
- # Qty Diff between file and system
- diff = flt(stock[self.label['qty']]) - flt(cur_stock_detail['actual_qty'])
-
- # Update MAR
- if not stock[self.label['mar']] == '~':
- self.update_mar(stock, diff)
-
- # Make sl entry if qty differ
- if diff:
- self.make_sl_entry(submit, stock, diff)
-
- if rec_stock_detail:
- text = "Stock Reconciliation Completed Successfully..."
- self.add_data_in_CSV(rec_stock_detail)
- self.add_remark(text,'Completed', 0)
-
- # Get csv data
- #--------------------------
def get_csv_file_data(self):
+ """Get csv data"""
filename = self.doc.file_list.split(',')
if not filename:
msgprint("Please Attach File. ", raise_exception=1)
@@ -274,37 +27,160 @@
if not type(content) == str:
content = content.tostring()
+
return content
+ def convert_into_list(self, data):
+ """Convert csv data into list"""
+ count = 1
+ for s in data:
+ if s[0].strip() != 'Item Code': # remove the labels
+ # validate
+ if len(s) != 4:
+ msgprint("Data entered at Row No " + cstr(count) + " in Attachment File is not in correct format.", raise_exception=1)
+ self.validated = 0
+ self.validate_item(s[0], count)
+ self.validate_warehouse(s[1], count)
+
+ # encode as ascii
+ self.data.append([d.encode("ascii") for d in s])
+ count += 1
+
+ if not self.validated:
+ raise Exception
- def getCSVelement(self,v):
- v = cstr(v)
- if not v: return ''
- if (',' in v) or ('' in v) or ('"' in v):
- if '"' in v: v = v.replace('"', '""')
- return '"'+v+'"'
- else: return v or ''
- # Add qty diff column in attached file
- #----------------------------------------
- def add_data_in_CSV(self,data):
- filename = self.doc.file_list.split(',')
- head = []
- for h in ['Item Code','Warehouse','Qty','Actual','Difference','MAR']:
- head.append(self.getCSVelement(h))
- dset = (','.join(head) + "\n")
- for d in data:
- l = [d[self.label['item_code']],d[self.label['warehouse']],d[self.label['qty']],d[self.label['actual_qty']],flt(d[self.label['qty']])-flt(d[self.label['actual_qty']]),d[self.label['mar']]]
- s =[]
- for i in l:
- s.append(self.getCSVelement(i))
- dset +=(','.join(s)+"\n")
+ def get_reconciliation_data(self,submit = 0):
+ """Read and validate csv data"""
+ import csv
+ data = csv.reader(self.get_csv_file_data().splitlines())
+ self.convert_into_list(data)
- from webnotes.utils import file_manager
- file_manager.write_file(filename[1], dset)
- # ----------
- # on submit
- # ----------
+ def validate_item(self, item, count):
+ """ Validate item exists and non-serialized"""
+ det = sql("select item_code, has_serial_no from `tabItem` where name = '%s'"% cstr(item), as_dict = 1)
+ if not det:
+ msgprint("Item: " + cstr(item) + " mentioned at Row No. " + cstr(count) + "does not exist in the system")
+ self.validated = 0
+ elif det and det[0]['has_serial_no'] == 'Yes':
+ msgprint("""You cannot make Stock Reconciliation of items having serial no. \n
+ You can directly upload serial no to update their inventory. \n
+ Please remove Item Code : %s at Row No. %s""" %(cstr(item), cstr(count)))
+ self.validated = 0
+
+
+ def validate_warehouse(self, wh, count,):
+ """Validate warehouse exists"""
+ if not sql("select name from `tabWarehouse` where name = '%s'" % cstr(wh)):
+ msgprint("Warehouse: " + cstr(wh) + " mentioned at Row No. " + cstr(count) + " does not exist in the system")
+ self.validated = 0
+
+
+
+ def validate(self):
+ """Validate attachment data"""
+ #self.data = [['it', 'wh1', 20, 150]]
+ if self.doc.file_list:
+ self.get_reconciliation_data()
+
+
+
+ def get_system_stock(self, it, wh):
+ """get actual qty on reconciliation date and time as per system"""
+ bin = sql("select name from tabBin where item_code=%s and warehouse=%s", (it, wh))
+ prev_sle = bin and get_obj('Bin', bin[0][0]).get_sle_prev_timebucket(self.doc.reconciliation_date, self.doc.reconciliation_time) or {}
+ return {
+ 'actual_qty': prev_sle.get('bin_aqat', 0),
+ 'stock_uom' : sql("select stock_uom from tabItem where name = %s", it)[0][0],
+ 'val_rate' : prev_sle.get('valuation_rate', 0)
+ }
+
+
+ def make_sl_entry(self, is_submit, row, qty_diff, sys_stock):
+ """Make stock ledger entry"""
+ in_rate = self.get_incoming_rate(row, qty_diff, sys_stock)
+ values = [{
+ 'item_code' : row[0],
+ 'warehouse' : row[1],
+ 'transaction_date' : nowdate(),
+ 'posting_date' : self.doc.reconciliation_date,
+ 'posting_time' : self.doc.reconciliation_time,
+ 'voucher_type' : self.doc.doctype,
+ 'voucher_no' : self.doc.name,
+ 'voucher_detail_no' : self.doc.name,
+ 'actual_qty' : flt(is_submit) * flt(qty_diff),
+ 'stock_uom' : sys_stock['stock_uom'],
+ 'incoming_rate' : in_rate,
+ 'company' : get_defaults()['company'],
+ 'fiscal_year' : get_defaults()['fiscal_year'],
+ 'is_cancelled' : (is_submit==1) and 'No' or 'Yes',
+ 'batch_no' : '',
+ 'serial_no' : ''
+ }]
+ get_obj('Stock Ledger', 'Stock Ledger').update_stock(values)
+
+
+ def get_incoming_rate(self, row, qty_diff, sys_stock):
+ """Calculate incoming rate to maintain valuation rate"""
+ in_rate = flt(row[3]) + (flt(sys_stock['actual_qty'])*(flt(row[3]) - flt(sys_stock['val_rate'])))/ flt(qty_diff)
+ return in_rate
+
+
+ def do_stock_reco(self, is_submit = 1):
+ """
+ Make stock entry of qty diff, calculate incoming rate to maintain valuation rate.
+ If no qty diff, but diff in valuation rate, make (+1,-1) entry to update valuation
+ """
+ for row in self.data:
+ # Get qty as per system
+ sys_stock = self.get_system_stock(row[0],row[1])
+
+ # Diff between file and system
+ qty_diff = row[2] != '~' and flt(row[2]) - flt(sys_stock['actual_qty']) or 0
+ rate_diff = row[3] != '~' and flt(row[3]) - flt(sys_stock['val_rate']) or 0
+
+ # Make sl entry
+ if qty_diff:
+ self.make_sl_entry(is_submit, row, qty_diff, sys_stock)
+ elif rate_diff:
+ self.make_sl_entry(is_submit, row, 1, sys_stock)
+ sys_stock['val_rate'] = row[3]
+ sys_stock['actual_qty'] += 1
+ self.make_sl_entry(is_submit, row, -1, sys_stock)
+
+ if is_submit == 1:
+ self.add_data_in_CSV(qty_diff, rate_diff)
+
+ msgprint("Stock Reconciliation Completed Successfully...")
+
+
+ def add_data_in_CSV(self, qty_diff, rate_diff):
+ """Add diffs column in attached file"""
+
+ # add header
+ out = "'Item Code', 'Warehouse', 'Qty', 'Valuation Rate', 'Qty Diff', 'Val Rate Diff'"
+
+ # add data
+ for d in self.data:
+ s = [cstr(i) for i in d] + [cstr(qty_diff), cstr(rate_diff)]
+ out += "\n" + ','.join(s)
+
+ # write to file
+ fname = self.doc.file_list.split(',')
+ from webnotes.utils import file_manager
+ file_manager.write_file(fname[1], out)
+
+
+
def on_submit(self):
- self.stock_reconciliations(submit = 1)
+ if not self.doc.file_list:
+ msgprint("Please attach file before submitting.", raise_exception=1)
+ else:
+ self.do_stock_reco(is_submit = 1)
+
+
+
+ def on_cancel(self):
+ self.validate()
+ self.do_stock_reco(is_submit = -1)
diff --git a/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.txt b/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.txt
index 771068d..27b1749 100644
--- a/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.txt
+++ b/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.txt
@@ -5,16 +5,18 @@
{
'creation': '2010-08-08 17:09:26',
'docstatus': 0,
- 'modified': '2011-04-25 11:46:21',
+ 'modified': '2011-11-18 17:32:21',
'modified_by': 'Administrator',
'owner': 'Administrator'
},
# These values are common for all DocType
{
- '_last_update': '1309508840',
+ '_last_update': '1321617633',
'allow_attach': 1,
+ 'autoname': 'SR/.######',
'colour': 'White:FFF',
+ 'default_print_format': 'Standard',
'doctype': 'DocType',
'max_attachments': 1,
'module': 'Stock',
@@ -23,7 +25,8 @@
'section_style': 'Tray',
'server_code_error': ' ',
'show_in_menu': 0,
- 'version': 85
+ 'subject': 'Date: %(reconciliation_date)s, Time: %(reconciliation_time)s',
+ 'version': 105
},
# These values are common for all DocField
@@ -37,6 +40,7 @@
# These values are common for all DocPerm
{
+ 'amend': 0,
'doctype': 'DocPerm',
'name': '__common__',
'parent': 'Stock Reconciliation',
@@ -53,11 +57,9 @@
# DocPerm
{
- 'amend': 1,
'cancel': 1,
'create': 1,
'doctype': 'DocPerm',
- 'idx': 1,
'permlevel': 0,
'role': 'Material Manager',
'submit': 1,
@@ -66,11 +68,9 @@
# DocPerm
{
- 'amend': 0,
'cancel': 0,
'create': 0,
'doctype': 'DocPerm',
- 'idx': 2,
'permlevel': 1,
'role': 'Material Manager',
'submit': 0,
@@ -79,44 +79,21 @@
# DocPerm
{
- 'amend': 1,
'cancel': 1,
'create': 1,
'doctype': 'DocPerm',
- 'idx': 3,
'permlevel': 0,
'role': 'System Manager',
'submit': 1,
'write': 1
},
- # DocPerm
- {
- 'doctype': 'DocPerm',
- 'idx': 4,
- 'permlevel': 1,
- 'role': 'System Manager'
- },
-
# DocField
{
'doctype': 'DocField',
- 'fieldtype': 'Button',
- 'hidden': 1,
- 'idx': 1,
- 'label': 'Validate Data',
- 'oldfieldtype': 'Button',
- 'options': 'get_reconciliation_stock_details',
- 'permlevel': 0
- },
-
- # DocField
- {
- 'doctype': 'DocField',
- 'fieldtype': 'Section Break',
- 'idx': 2,
- 'label': 'Summary',
- 'oldfieldtype': 'Section Break',
+ 'fieldtype': 'HTML',
+ 'label': 'Steps',
+ 'options': '<div class="field_description"><b>Steps:</b><br>1. Enter Reconciliation Date and Time<br>2. Save the document<br>3. Attach csv file as per template.<br>4. Submit the document</div>',
'permlevel': 0
},
@@ -125,14 +102,12 @@
'doctype': 'DocField',
'fieldname': 'reconciliation_date',
'fieldtype': 'Date',
- 'idx': 3,
'in_filter': 0,
'label': 'Reconciliation Date',
'oldfieldname': 'reconciliation_date',
'oldfieldtype': 'Date',
'permlevel': 0,
- 'reqd': 1,
- 'search_index': 1
+ 'reqd': 1
},
# DocField
@@ -140,7 +115,6 @@
'doctype': 'DocField',
'fieldname': 'reconciliation_time',
'fieldtype': 'Time',
- 'idx': 4,
'in_filter': 0,
'label': 'Reconciliation Time',
'oldfieldname': 'reconciliation_time',
@@ -152,97 +126,20 @@
# DocField
{
'doctype': 'DocField',
- 'fieldname': 'next_step',
- 'fieldtype': 'Select',
- 'idx': 5,
- 'label': 'Next Steps',
- 'oldfieldname': 'next_step',
- 'oldfieldtype': 'Select',
- 'options': 'Upload File and Save Document\nValidate Data\nSubmit Document\nCompleted',
- 'permlevel': 1
- },
-
- # DocField
- {
- 'doctype': 'DocField',
'fieldname': 'remark',
'fieldtype': 'Text',
- 'idx': 6,
'label': 'Remark',
'oldfieldname': 'remark',
'oldfieldtype': 'Text',
- 'permlevel': 1
- },
-
- # DocField
- {
- 'doctype': 'DocField',
- 'fieldname': 'company',
- 'fieldtype': 'Link',
- 'idx': 7,
- 'in_filter': 1,
- 'label': 'Company',
- 'oldfieldname': 'company',
- 'oldfieldtype': 'Link',
- 'options': 'Company',
- 'permlevel': 0,
- 'search_index': 0
- },
-
- # DocField
- {
- 'doctype': 'DocField',
- 'fieldname': 'fiscal_year',
- 'fieldtype': 'Select',
- 'idx': 8,
- 'in_filter': 1,
- 'label': 'Fiscal Year',
- 'oldfieldname': 'fiscal_year',
- 'oldfieldtype': 'Select',
- 'options': 'link:Fiscal Year',
- 'permlevel': 0,
- 'search_index': 0
- },
-
- # DocField
- {
- 'doctype': 'DocField',
- 'fieldname': 'amended_from',
- 'fieldtype': 'Data',
- 'idx': 9,
- 'label': 'Amended From',
- 'permlevel': 1
- },
-
- # DocField
- {
- 'doctype': 'DocField',
- 'fieldname': 'amendment_date',
- 'fieldtype': 'Date',
- 'idx': 10,
- 'label': 'Amendment Date',
- 'permlevel': 1
- },
-
- # DocField
- {
- 'doctype': 'DocField',
- 'fieldtype': 'Section Break',
- 'idx': 11,
- 'label': 'Attachment',
- 'oldfieldtype': 'Section Break',
'permlevel': 0
},
# DocField
{
'doctype': 'DocField',
- 'fieldtype': 'HTML',
- 'idx': 12,
- 'label': 'Attachment HTML',
- 'oldfieldtype': 'HTML',
- 'options': 'The attachment must be a CSV(Comma Seperated Value) file',
- 'permlevel': 1
+ 'fieldtype': 'Button',
+ 'label': 'Download Template',
+ 'permlevel': 0
},
# DocField
@@ -251,8 +148,8 @@
'fieldname': 'file_list',
'fieldtype': 'Text',
'hidden': 1,
- 'idx': 13,
'label': 'File List',
+ 'no_copy': 1,
'oldfieldname': 'file_list',
'oldfieldtype': 'Text',
'permlevel': 1,