[stock reco] added items table
diff --git a/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.js b/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.js
index 27ed872..c2cee33 100644
--- a/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.js
+++ b/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.js
@@ -5,6 +5,26 @@
frappe.require("assets/erpnext/js/utils.js");
frappe.provide("erpnext.stock");
+frappe.ui.form.on("Stock Reconciliation", "get_items", function(frm) {
+ frappe.prompt({label:"Warehouse", fieldtype:"Link", options:"Warehouse", reqd: 1},
+ function(data) {
+ frappe.call({
+ method:"erpnext.stock.doctype.stock_reconciliation.stock_reconciliation.get_items",
+ args: {warehouse: data.warehouse},
+ callback: function(r) {
+ var items = [];
+ frm.clear_table("items");
+ for(var i=0; i< r.message.length; i++) {
+ var d = frm.add_child("items");
+ $.extend(d, r.message[i]);
+ }
+ frm.refresh_field("items");
+ }
+ });
+ }
+ );
+});
+
erpnext.stock.StockReconciliation = erpnext.stock.StockController.extend({
onload: function() {
this.set_default_expense_account();
@@ -31,6 +51,8 @@
setup: function() {
var me = this;
+ this.frm.get_field("items").grid.allow_build_edit();
+
if (sys_defaults.auto_accounting_for_stock) {
this.frm.add_fetch("company", "stock_adjustment_account", "expense_account");
this.frm.add_fetch("company", "cost_center", "cost_center");
@@ -55,108 +77,29 @@
},
refresh: function() {
- 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 appropriate data and attach the modified file."));
- }
- } else if(this.frm.doc.docstatus == 1) {
- this.frm.set_intro(__("Cancelling this Stock Reconciliation will nullify its effect."));
- this.show_stock_ledger();
- this.show_general_ledger();
- } else {
- this.frm.set_intro("");
- }
- 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");
- frappe.tools.downloadify([[__("Stock Reconciliation")],
- ["----"],
- [__("Stock Reconciliation can be used to update the stock on a particular date, usually as per physical inventory.")],
- [__("When submitted, the system creates difference entries to set the given stock and valuation on this date.")],
- [__("It can also be used to create opening stock entries and to fix stock value.")],
- ["----"],
- [__("Notes:")],
- [__("Item Code and Warehouse should already exist.")],
- [__("You can update either Quantity or Valuation Rate or both.")],
- [__("If no change in either Quantity or Valuation Rate, leave the cell blank.")],
- ["----"],
- ["Item Code", "Warehouse", "Quantity", "Valuation Rate"]], null, this);
- return false;
- }, "icon-download");
- },
+ // show_download_template: function() {
+ // var me = this;
+ // this.frm.add_custom_button(__("Download Template"), function() {
+ // this.title = __("Stock Reconcilation Template");
+ // frappe.tools.downloadify([[__("Stock Reconciliation")],
+ // ["----"],
+ // [__("Stock Reconciliation can be used to update the stock on a particular date, usually as per physical inventory.")],
+ // [__("When submitted, the system creates difference entries to set the given stock and valuation on this date.")],
+ // [__("It can also be used to create opening stock entries and to fix stock value.")],
+ // ["----"],
+ // [__("Notes:")],
+ // [__("Item Code and Warehouse should already exist.")],
+ // [__("You can update either Quantity or Valuation Rate or both.")],
+ // [__("If no change in either Quantity or Valuation Rate, leave the cell blank.")],
+ // ["----"],
+ // ["Item Code", "Warehouse", "Quantity", "Valuation Rate"]], null, this);
+ // return false;
+ // }, "icon-download");
+ // },
- show_upload: function() {
- var me = this;
- var $wrapper = $(cur_frm.fields_dict.upload_html.wrapper).empty();
-
- // upload
- frappe.upload.make({
- parent: $wrapper,
- args: {
- method: 'erpnext.stock.doctype.stock_reconciliation.stock_reconciliation.upload'
- },
- sample_url: "e.g. http://example.com/somefile.csv",
- callback: function(attachment, r) {
- me.frm.set_value("reconciliation_json", JSON.stringify(r.message));
- me.show_reconciliation_data();
- me.frm.save();
- }
- });
-
- // rename button
- $wrapper.find('form input[type="submit"]')
- .attr('value', 'Upload')
-
- },
-
- show_download_reconciliation_data: function() {
- var me = this;
- if(this.frm.doc.reconciliation_json) {
- this.frm.add_custom_button(__("Download Reconcilation Data"), function() {
- this.title = __("Stock Reconcilation Data");
- frappe.tools.downloadify(JSON.parse(me.frm.doc.reconciliation_json), null, this);
- return false;
- }, "icon-download", "btn-default");
- }
- },
-
- show_reconciliation_data: function() {
- var $wrapper = $(cur_frm.fields_dict.reconciliation_html.wrapper).empty();
- if(this.frm.doc.reconciliation_json) {
- var reconciliation_data = JSON.parse(this.frm.doc.reconciliation_json);
-
- var _make = function(data, header) {
- var result = "";
-
- var _render = header
- ? function(col) { return "<th>" + col + "</th>"; }
- : function(col) { return "<td>" + col + "</td>"; };
-
- $.each(data, function(i, row) {
- result += "<tr>"
- + $.map(row, _render).join("")
- + "</tr>";
- });
- return result;
- };
-
- var $reconciliation_table = $("<div style='overflow-x: auto;'>\
- <table class='table table-striped table-bordered'>\
- <thead>" + _make([reconciliation_data[0]], true) + "</thead>\
- <tbody>" + _make(reconciliation_data.splice(1)) + "</tbody>\
- </table>\
- </div>").appendTo($wrapper);
- }
- },
});
cur_frm.cscript = new erpnext.stock.StockReconciliation({frm: cur_frm});
@@ -167,4 +110,4 @@
cur_frm.cscript.posting_date = function(doc, cdt, cdn){
erpnext.get_fiscal_year(doc.company, doc.posting_date);
-}
\ No newline at end of file
+}
diff --git a/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.json b/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.json
index a6c09ee..6ca212d 100644
--- a/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.json
+++ b/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.json
@@ -1,5 +1,5 @@
{
- "allow_copy": 1,
+ "allow_copy": 0,
"autoname": "SR/.######",
"creation": "2013-03-28 10:35:31",
"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.",
@@ -32,6 +32,14 @@
"reqd": 1
},
{
+ "depends_on": "eval:cint(sys_defaults.auto_accounting_for_stock)",
+ "fieldname": "expense_account",
+ "fieldtype": "Link",
+ "label": "Difference Account",
+ "options": "Account",
+ "permlevel": 0
+ },
+ {
"fieldname": "amended_from",
"fieldtype": "Link",
"ignore_user_permissions": 1,
@@ -43,6 +51,11 @@
"read_only": 1
},
{
+ "fieldname": "col1",
+ "fieldtype": "Column Break",
+ "permlevel": 0
+ },
+ {
"fieldname": "company",
"fieldtype": "Link",
"label": "Company",
@@ -61,14 +74,6 @@
},
{
"depends_on": "eval:cint(sys_defaults.auto_accounting_for_stock)",
- "fieldname": "expense_account",
- "fieldtype": "Link",
- "label": "Difference Account",
- "options": "Account",
- "permlevel": 0
- },
- {
- "depends_on": "eval:cint(sys_defaults.auto_accounting_for_stock)",
"fieldname": "cost_center",
"fieldtype": "Link",
"label": "Cost Center",
@@ -76,9 +81,31 @@
"permlevel": 0
},
{
- "fieldname": "col1",
- "fieldtype": "Column Break",
- "permlevel": 0
+ "fieldname": "sb9",
+ "fieldtype": "Section Break",
+ "permlevel": 0,
+ "precision": ""
+ },
+ {
+ "fieldname": "items",
+ "fieldtype": "Table",
+ "label": "Items",
+ "options": "Stock Reconciliation Item",
+ "permlevel": 0,
+ "precision": ""
+ },
+ {
+ "fieldname": "get_items",
+ "fieldtype": "Button",
+ "label": "Get Items",
+ "permlevel": 0,
+ "precision": ""
+ },
+ {
+ "fieldname": "section_break_9",
+ "fieldtype": "Section Break",
+ "permlevel": 0,
+ "precision": ""
},
{
"fieldname": "upload_html",
@@ -119,7 +146,7 @@
"idx": 1,
"is_submittable": 1,
"max_attachments": 1,
- "modified": "2015-02-05 05:11:47.153367",
+ "modified": "2015-02-17 02:09:17.483016",
"modified_by": "Administrator",
"module": "Stock",
"name": "Stock Reconciliation",
diff --git a/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py b/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py
index 9c85277..1bb3f56 100644
--- a/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py
+++ b/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py
@@ -4,7 +4,6 @@
from __future__ import unicode_literals
import frappe
import frappe.defaults
-import json
from frappe import msgprint, _
from frappe.utils import cstr, flt, cint
from erpnext.stock.stock_ledger import update_entries_after
@@ -28,61 +27,41 @@
self.make_gl_entries_on_cancel()
def validate_data(self):
- if not self.reconciliation_json:
- return
-
- data = json.loads(self.reconciliation_json)
-
- # strip out extra columns (if any)
- data = [row[:4] for row in data]
-
- if self.head_row not in data:
- msgprint(_("""Wrong Template: Unable to find head row."""),
- raise_exception=1)
-
- # remove the help part and save the json
- head_row_no = 0
- if data.index(self.head_row) != 0:
- head_row_no = data.index(self.head_row)
- data = data[head_row_no:]
- self.reconciliation_json = json.dumps(data)
-
def _get_msg(row_num, msg):
- return _("Row # {0}: ").format(row_num+head_row_no+2) + msg
+ return _("Row # {0}: ").format(row_num+1) + msg
self.validation_messages = []
item_warehouse_combinations = []
# validate no of rows
- rows = data[1:]
+ rows = self.items
if len(rows) > 100:
- msgprint(_("""Sorry! We can only allow upto 100 rows for Stock Reconciliation."""),
- raise_exception=True)
+ frappe.throw(_("""Max 100 rows for Stock Reconciliation."""))
for row_num, row in enumerate(rows):
# find duplicates
- if [row[0], row[1]] in item_warehouse_combinations:
+ if [row.item_code, row.warehouse] in item_warehouse_combinations:
self.validation_messages.append(_get_msg(row_num, _("Duplicate entry")))
else:
- item_warehouse_combinations.append([row[0], row[1]])
+ item_warehouse_combinations.append([row.item_code, row.warehouse])
- self.validate_item(row[0], row_num+head_row_no+2)
+ self.validate_item(row.item_code, row_num+1)
# validate warehouse
- if not frappe.db.get_value("Warehouse", row[1]):
+ if not frappe.db.get_value("Warehouse", row.warehouse):
self.validation_messages.append(_get_msg(row_num, _("Warehouse not found in the system")))
# if both not specified
- if row[2] in ["", None] and row[3] in ["", None]:
+ if row.qty in ["", None] and row.valuation_rate in ["", None]:
self.validation_messages.append(_get_msg(row_num,
_("Please specify either Quantity or Valuation Rate or both")))
# do not allow negative quantity
- if flt(row[2]) < 0:
+ if flt(row.qty) < 0:
self.validation_messages.append(_get_msg(row_num,
_("Negative Quantity is not allowed")))
# do not allow negative valuation
- if flt(row[3]) < 0:
+ if flt(row.valuation_rate) < 0:
self.validation_messages.append(_get_msg(row_num,
_("Negative Valuation Rate is not allowed")))
@@ -129,16 +108,7 @@
and create stock ledger entries based on the difference"""
from erpnext.stock.stock_ledger import get_previous_sle
- row_template = ["item_code", "warehouse", "qty", "valuation_rate"]
-
- if not self.reconciliation_json:
- msgprint(_("""Stock Reconciliation file not uploaded"""), raise_exception=1)
-
- data = json.loads(self.reconciliation_json)
- for row_num, row in enumerate(data[data.index(self.head_row)+1:]):
- row = frappe._dict(zip(row_template, row))
- row["row_num"] = row_num
-
+ for row in self.items:
if row.qty in ("", None) or row.valuation_rate in ("", None):
previous_sle = get_previous_sle({
"item_code": row.item_code,
@@ -216,7 +186,14 @@
frappe.throw(_("Difference Account must be a 'Liability' type account, since this Stock Reconciliation is an Opening Entry"))
@frappe.whitelist()
-def upload():
- from frappe.utils.csvutils import read_csv_content_from_uploaded_file
- csv_content = read_csv_content_from_uploaded_file()
- return filter(lambda x: x and any(x), csv_content)
+def get_items(warehouse):
+ from erpnext.stock.utils import get_stock_balance
+ items = frappe.get_list("Item", fields=["name"], filters=
+ {"is_stock_item": "Yes", "has_serial_no": "No", "has_batch_no": "No"})
+ for item in items:
+ item.item_code = item.name
+ item.warehouse = warehouse
+ del item["name"]
+ item.qty, item.valuation_rate = get_stock_balance(item.name, warehouse, with_valuation_rate=True)
+
+ return items
diff --git a/erpnext/stock/doctype/stock_reconciliation_item/__init__.py b/erpnext/stock/doctype/stock_reconciliation_item/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/stock/doctype/stock_reconciliation_item/__init__.py
diff --git a/erpnext/stock/doctype/stock_reconciliation_item/stock_reconciliation_item.json b/erpnext/stock/doctype/stock_reconciliation_item/stock_reconciliation_item.json
new file mode 100644
index 0000000..aa55031
--- /dev/null
+++ b/erpnext/stock/doctype/stock_reconciliation_item/stock_reconciliation_item.json
@@ -0,0 +1,110 @@
+{
+ "allow_copy": 0,
+ "allow_import": 0,
+ "allow_rename": 0,
+ "creation": "2015-02-17 01:06:05.072764",
+ "custom": 0,
+ "docstatus": 0,
+ "doctype": "DocType",
+ "document_type": "Other",
+ "fields": [
+ {
+ "allow_on_submit": 0,
+ "fieldname": "item_code",
+ "fieldtype": "Link",
+ "hidden": 0,
+ "ignore_user_permissions": 0,
+ "in_filter": 0,
+ "in_list_view": 1,
+ "label": "Item Code",
+ "no_copy": 0,
+ "options": "Item",
+ "permlevel": 0,
+ "precision": "",
+ "print_hide": 0,
+ "read_only": 0,
+ "report_hide": 0,
+ "reqd": 1,
+ "search_index": 0,
+ "set_only_once": 0
+ },
+ {
+ "allow_on_submit": 0,
+ "fieldname": "warehouse",
+ "fieldtype": "Link",
+ "hidden": 0,
+ "ignore_user_permissions": 0,
+ "in_filter": 0,
+ "in_list_view": 1,
+ "label": "Warehouse",
+ "no_copy": 0,
+ "options": "Warehouse",
+ "permlevel": 0,
+ "precision": "",
+ "print_hide": 0,
+ "read_only": 0,
+ "report_hide": 0,
+ "reqd": 1,
+ "search_index": 0,
+ "set_only_once": 0
+ },
+ {
+ "allow_on_submit": 0,
+ "description": "Leave blank if no change",
+ "fieldname": "qty",
+ "fieldtype": "Float",
+ "hidden": 0,
+ "ignore_user_permissions": 0,
+ "in_filter": 0,
+ "in_list_view": 1,
+ "label": "Quantity",
+ "no_copy": 0,
+ "permlevel": 0,
+ "precision": "",
+ "print_hide": 0,
+ "read_only": 0,
+ "report_hide": 0,
+ "reqd": 0,
+ "search_index": 0,
+ "set_only_once": 0
+ },
+ {
+ "allow_on_submit": 0,
+ "description": "Leave blank if no change",
+ "fieldname": "valuation_rate",
+ "fieldtype": "Currency",
+ "hidden": 0,
+ "ignore_user_permissions": 0,
+ "in_filter": 0,
+ "in_list_view": 1,
+ "label": "Valuation Rate",
+ "no_copy": 0,
+ "permlevel": 0,
+ "precision": "",
+ "print_hide": 0,
+ "read_only": 0,
+ "report_hide": 0,
+ "reqd": 0,
+ "search_index": 0,
+ "set_only_once": 0
+ }
+ ],
+ "hide_heading": 0,
+ "hide_toolbar": 0,
+ "in_create": 0,
+ "in_dialog": 0,
+ "is_submittable": 0,
+ "issingle": 0,
+ "istable": 1,
+ "modified": "2015-02-17 01:07:50.200649",
+ "modified_by": "Administrator",
+ "module": "Stock",
+ "name": "Stock Reconciliation Item",
+ "name_case": "",
+ "owner": "Administrator",
+ "permissions": [],
+ "read_only": 0,
+ "read_only_onload": 0,
+ "sort_field": "modified",
+ "sort_order": "DESC"
+}
\ No newline at end of file
diff --git a/erpnext/stock/doctype/stock_reconciliation_item/stock_reconciliation_item.py b/erpnext/stock/doctype/stock_reconciliation_item/stock_reconciliation_item.py
new file mode 100644
index 0000000..6597efd
--- /dev/null
+++ b/erpnext/stock/doctype/stock_reconciliation_item/stock_reconciliation_item.py
@@ -0,0 +1,9 @@
+# Copyright (c) 2013, Web Notes Technologies Pvt. Ltd. and Contributors and contributors
+# For license information, please see license.txt
+
+from __future__ import unicode_literals
+import frappe
+from frappe.model.document import Document
+
+class StockReconciliationItem(Document):
+ pass
diff --git a/erpnext/stock/utils.py b/erpnext/stock/utils.py
index ceb0ebf..9a33735 100644
--- a/erpnext/stock/utils.py
+++ b/erpnext/stock/utils.py
@@ -34,19 +34,22 @@
return sum(sle_map.values())
-def get_stock_balance(item_code, warehouse, posting_date=None, posting_time=None):
+def get_stock_balance(item_code, warehouse, posting_date=None, posting_time=None, with_valuation_rate=False):
+ """Returns stock balance quantity at given warehouse on given posting date or current date.
+
+ If `with_valuation_rate` is True, will return tuple (qty, rate)"""
if not posting_date: posting_date = nowdate()
if not posting_time: posting_time = nowtime()
- last_entry = frappe.db.sql("""select qty_after_transaction from `tabStock Ledger Entry`
+ last_entry = frappe.db.sql("""select qty_after_transaction, valuation_rate from `tabStock Ledger Entry`
where item_code=%s and warehouse=%s
and timestamp(posting_date, posting_time) < timestamp(%s, %s)
order by timestamp(posting_date, posting_time) limit 1""",
(item_code, warehouse, posting_date, posting_time))
- if last_entry:
- return last_entry[0][0]
+ if with_valuation_rate:
+ return (last_entry[0][0], last_entry[0][1]) if last_entry else (0.0, 0.0)
else:
- return 0.0
+ return last_entry[0][0] if last_entry else 0.0
def get_latest_stock_balance():
bin_map = {}