Merge pull request #432 from akhileshdarjee/master
Batch-Wise Balance History
diff --git a/stock/report/batch_wise_balance_history/__init__.py b/stock/report/batch_wise_balance_history/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/stock/report/batch_wise_balance_history/__init__.py
diff --git a/stock/report/batch_wise_balance_history/batch_wise_balance_history.js b/stock/report/batch_wise_balance_history/batch_wise_balance_history.js
new file mode 100644
index 0000000..0ba1938
--- /dev/null
+++ b/stock/report/batch_wise_balance_history/batch_wise_balance_history.js
@@ -0,0 +1,39 @@
+wn.query_reports["Batch-Wise Balance History"] = {
+ "filters": [
+ {
+ "fieldname":"item_code",
+ "label": "Item",
+ "fieldtype": "Link",
+ "options": "Item",
+ "width": "80"
+ },
+ {
+ "fieldname":"warehouse",
+ "label": "Warehouse",
+ "fieldtype": "Link",
+ "options": "Warehouse",
+ "width": "80"
+ },
+ {
+ "fieldname":"batch_no",
+ "label": "Batch",
+ "fieldtype": "Link",
+ "options": "Batch",
+ "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/batch_wise_balance_history/batch_wise_balance_history.py b/stock/report/batch_wise_balance_history/batch_wise_balance_history.py
new file mode 100644
index 0000000..ca3e775
--- /dev/null
+++ b/stock/report/batch_wise_balance_history/batch_wise_balance_history.py
@@ -0,0 +1,109 @@
+# 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_batch_map(filters)
+
+ data = []
+ for item in sorted(iwb_map):
+ for wh in sorted(iwb_map[item]):
+ for batch in sorted(iwb_map[item][wh]):
+ qty_dict = iwb_map[item][wh][batch]
+ data.append([item, item_map[item]["item_name"],
+ item_map[item]["description"], wh, batch,
+ 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"] + ["Batch:Link/Batch: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 filters.get("batch_no"):
+ conditions += " and batch_no='%s'" % filters["batch_no"]
+
+ 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, batch_no, 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_batch_map(filters):
+ sle = get_stock_ledger_entries(filters)
+ iwb_map = {}
+
+ for d in sle:
+ iwb_map.setdefault(d.item_code, {}).setdefault(d.warehouse, {})\
+ .setdefault(d.batch_no, 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][d.batch_no]
+ 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/batch_wise_balance_history/batch_wise_balance_history.txt b/stock/report/batch_wise_balance_history/batch_wise_balance_history.txt
new file mode 100644
index 0000000..9e795b9
--- /dev/null
+++ b/stock/report/batch_wise_balance_history/batch_wise_balance_history.txt
@@ -0,0 +1,21 @@
+[
+ {
+ "creation": "2013-06-04 11:03:47",
+ "docstatus": 0,
+ "modified": "2013-06-04 19:32:27",
+ "modified_by": "Administrator",
+ "owner": "Administrator"
+ },
+ {
+ "doctype": "Report",
+ "is_standard": "Yes",
+ "name": "__common__",
+ "ref_doctype": "Stock Ledger Entry",
+ "report_name": "Batch-Wise Balance History",
+ "report_type": "Script Report"
+ },
+ {
+ "doctype": "Report",
+ "name": "Batch-Wise Balance History"
+ }
+]
\ No newline at end of file