Completed Warehouse-Wise Stock Balance
diff --git a/stock/page/stock_home/stock_home.js b/stock/page/stock_home/stock_home.js
index 75e4ee1..bc71a4f 100644
--- a/stock/page/stock_home/stock_home.js
+++ b/stock/page/stock_home/stock_home.js
@@ -205,6 +205,14 @@
"label":wn._("Requested Items To Be Transferred"),
route: "query-report/Requested Items To Be Transferred",
},
+ {
+ "label":wn._("Batch-Wise Balance History"),
+ route: "query-report/Batch-Wise Balance History",
+ },
+ {
+ "label":wn._("Warehouse-Wise Stock Balance"),
+ route: "query-report/Warehouse-Wise Stock Balance",
+ },
]
}
]
diff --git a/stock/report/warehouse_wise_stock_balance/__init__.py b/stock/report/warehouse_wise_stock_balance/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/stock/report/warehouse_wise_stock_balance/__init__.py
diff --git a/stock/report/warehouse_wise_stock_balance/warehouse_wise_stock_balance.js b/stock/report/warehouse_wise_stock_balance/warehouse_wise_stock_balance.js
new file mode 100644
index 0000000..5e1eb3a
--- /dev/null
+++ b/stock/report/warehouse_wise_stock_balance/warehouse_wise_stock_balance.js
@@ -0,0 +1,32 @@
+wn.query_reports["Warehouse-Wise Stock Balance"] = {
+ "filters": [
+ {
+ "fieldname":"item_code",
+ "label": "Item",
+ "fieldtype": "Link",
+ "options": "Item",
+ "width": "80"
+ },
+ {
+ "fieldname":"warehouse",
+ "label": "Warehouse",
+ "fieldtype": "Link",
+ "options": "Warehouse",
+ "width": "80"
+ },
+ {
+ "fieldname":"from_date",
+ "label": "From Date",
+ "fieldtype": "Date",
+ "width": "80",
+ "default": sys_defaults.year_start_date,
+ },
+ {
+ "fieldname":"to_date",
+ "label": "To Date",
+ "fieldtype": "Date",
+ "width": "80",
+ "default": wn.datetime.get_today()
+ }
+ ]
+}
\ No newline at end of file
diff --git a/stock/report/warehouse_wise_stock_balance/warehouse_wise_stock_balance.py b/stock/report/warehouse_wise_stock_balance/warehouse_wise_stock_balance.py
new file mode 100644
index 0000000..324bbe3
--- /dev/null
+++ b/stock/report/warehouse_wise_stock_balance/warehouse_wise_stock_balance.py
@@ -0,0 +1,104 @@
+# 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 webnotes
+from webnotes.utils import flt
+
+def execute(filters=None):
+ if not filters: filters = {}
+
+ columns = get_columns(filters)
+ item_map = get_item_details(filters)
+ iwb_map = get_item_warehouse_map(filters)
+
+ data = []
+ for item in sorted(iwb_map):
+ for wh in sorted(iwb_map[item]):
+ qty_dict = iwb_map[item][wh]
+ data.append([item, item_map[item]["item_name"],
+ item_map[item]["description"], wh,
+ qty_dict.opening_qty, qty_dict.in_qty,
+ qty_dict.out_qty, qty_dict.bal_qty
+ ])
+
+ return columns, data
+
+def get_columns(filters):
+ """return columns based on filters"""
+
+ columns = ["Item:Link/Item:100"] + ["Item Name::150"] + ["Description::150"] + \
+ ["Warehouse:Link/Warehouse:100"] + ["Opening Qty::90"] + \
+ ["In Qty::80"] + ["Out Qty::80"] + ["Balance Qty::90"]
+
+ return columns
+
+def get_conditions(filters):
+ conditions = ""
+ if filters.get("item_code"):
+ conditions += " and item_code='%s'" % filters["item_code"]
+
+ if filters.get("warehouse"):
+ conditions += " and warehouse='%s'" % filters["warehouse"]
+
+ if not filters.get("from_date"):
+ webnotes.msgprint("Please enter From Date", raise_exception=1)
+
+ if filters.get("to_date"):
+ conditions += " and posting_date <= '%s'" % filters["to_date"]
+ else:
+ webnotes.msgprint("Please enter To Date", raise_exception=1)
+
+ return conditions
+
+#get all details
+def get_stock_ledger_entries(filters):
+ conditions = get_conditions(filters)
+ return webnotes.conn.sql("""select item_code, warehouse,
+ posting_date, actual_qty
+ from `tabStock Ledger Entry`
+ where ifnull(is_cancelled, 'No') = 'No' %s order by item_code, warehouse""" %
+ conditions, as_dict=1)
+
+def get_item_warehouse_map(filters):
+ sle = get_stock_ledger_entries(filters)
+ iwb_map = {}
+
+ for d in sle:
+ iwb_map.setdefault(d.item_code, {}).setdefault(d.warehouse, webnotes._dict({\
+ "opening_qty": 0.0, "in_qty": 0.0, "out_qty": 0.0, "bal_qty": 0.0
+ }))
+ qty_dict = iwb_map[d.item_code][d.warehouse]
+ if d.posting_date < filters["from_date"]:
+ qty_dict.opening_qty += flt(d.actual_qty)
+ elif d.posting_date >= filters["from_date"] and d.posting_date <= filters["to_date"]:
+ if flt(d.actual_qty) > 0:
+ qty_dict.in_qty += flt(d.actual_qty)
+ else:
+ qty_dict.out_qty += abs(flt(d.actual_qty))
+
+ qty_dict.bal_qty += flt(d.actual_qty)
+
+ return iwb_map
+
+def get_item_details(filters):
+ if filters.get("item_code"):
+ conditions = " and name = '%s'" % filters["item_code"]
+ item_map = {}
+ for d in webnotes.conn.sql("select name, item_name, description from tabItem", as_dict=1):
+ item_map.setdefault(d.name, d)
+
+ return item_map
\ No newline at end of file
diff --git a/stock/report/warehouse_wise_stock_balance/warehouse_wise_stock_balance.txt b/stock/report/warehouse_wise_stock_balance/warehouse_wise_stock_balance.txt
new file mode 100644
index 0000000..2513587
--- /dev/null
+++ b/stock/report/warehouse_wise_stock_balance/warehouse_wise_stock_balance.txt
@@ -0,0 +1,21 @@
+[
+ {
+ "creation": "2013-06-05 11:00:31",
+ "docstatus": 0,
+ "modified": "2013-06-05 11:00:31",
+ "modified_by": "Administrator",
+ "owner": "Administrator"
+ },
+ {
+ "doctype": "Report",
+ "is_standard": "Yes",
+ "name": "__common__",
+ "ref_doctype": "Stock Ledger Entry",
+ "report_name": "Warehouse-Wise Stock Balance",
+ "report_type": "Script Report"
+ },
+ {
+ "doctype": "Report",
+ "name": "Warehouse-Wise Stock Balance"
+ }
+]
\ No newline at end of file