Warehouse wise Item Balance, Age and Value (#12879)
* Create warehouse_wise_item_balanace_age_and_value.py
* Create warehouse_wise_item_balance_age_and_value.js
* Create warehouse_wise_item_balance_age_and_value.json
* Update warehouse_wise_item_balance_age_and_value.json
* Update warehouse_wise_item_balance_age_and_value.json
* Update warehouse_wise_item_balance_age_and_value.json
* Update warehouse_wise_item_balance_age_and_value.js
* Rename warehouse_wise_item_balanace_age_and_value.py to warehouse_wise_item_balance_age_and_value.py
* Update warehouse_wise_item_balance_age_and_value.py
diff --git a/erpnext/stock/report/warehouse_wise_item_balance_age_and_value/warehouse_wise_item_balance_age_and_value.js b/erpnext/stock/report/warehouse_wise_item_balance_age_and_value/warehouse_wise_item_balance_age_and_value.js
new file mode 100644
index 0000000..21884d8
--- /dev/null
+++ b/erpnext/stock/report/warehouse_wise_item_balance_age_and_value/warehouse_wise_item_balance_age_and_value.js
@@ -0,0 +1,51 @@
+// Copyright (c) 2016, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+/* eslint-disable */
+
+frappe.query_reports["Warehouse wise Item Balance Age and Value"] = {
+ "filters": [
+{
+ "fieldname":"from_date",
+ "label": __("From Date"),
+ "fieldtype": "Date",
+ "width": "80",
+ "reqd": 1,
+ "default": frappe.sys_defaults.year_start_date,
+ },
+ {
+ "fieldname":"to_date",
+ "label": __("To Date"),
+ "fieldtype": "Date",
+ "width": "80",
+ "reqd": 1,
+ "default": frappe.datetime.get_today()
+ },
+ {
+ "fieldname": "item_group",
+ "label": __("Item Group"),
+ "fieldtype": "Link",
+ "width": "80",
+ "options": "Item Group"
+ },
+ {
+ "fieldname": "item_code",
+ "label": __("Item"),
+ "fieldtype": "Link",
+ "width": "80",
+ "options": "Item"
+ },
+ {
+ "fieldname": "warehouse",
+ "label": __("Warehouse"),
+ "fieldtype": "Link",
+ "width": "80",
+ "options": "Warehouse"
+ },
+ {
+ "fieldname": "filter_total_zero_qty",
+ "label": __("Filter Total Zero Qty"),
+ "fieldtype": "Check",
+ "default": 1
+ },
+ ]
+}
diff --git a/erpnext/stock/report/warehouse_wise_item_balance_age_and_value/warehouse_wise_item_balance_age_and_value.json b/erpnext/stock/report/warehouse_wise_item_balance_age_and_value/warehouse_wise_item_balance_age_and_value.json
new file mode 100644
index 0000000..44d0f09
--- /dev/null
+++ b/erpnext/stock/report/warehouse_wise_item_balance_age_and_value/warehouse_wise_item_balance_age_and_value.json
@@ -0,0 +1,32 @@
+{
+ "add_total_row": 1,
+ "apply_user_permissions": 1,
+ "creation": "2018-02-12 17:39:57.362767",
+ "disabled": 0,
+ "docstatus": 0,
+ "doctype": "Report",
+ "idx": 0,
+ "is_standard": "Yes",
+ "modified": "2018-02-12 17:45:04.627654",
+ "modified_by": "Administrator",
+ "module": "Stock",
+ "name": "Warehouse wise Item Balance Age and Value",
+ "owner": "Administrator",
+ "ref_doctype": "Stock Ledger Entry",
+ "report_name": "Warehouse wise Item Balance Age and Value",
+ "report_type": "Script Report",
+ "roles": [
+ {
+ "role": "Accounts Manager"
+ },
+ {
+ "role": "Stock User"
+ },
+ {
+ "role": "Stock Manager"
+ },
+ {
+ "role": "Sales User"
+ }
+ ]
+}
diff --git a/erpnext/stock/report/warehouse_wise_item_balance_age_and_value/warehouse_wise_item_balance_age_and_value.py b/erpnext/stock/report/warehouse_wise_item_balance_age_and_value/warehouse_wise_item_balance_age_and_value.py
new file mode 100644
index 0000000..217a6a0
--- /dev/null
+++ b/erpnext/stock/report/warehouse_wise_item_balance_age_and_value/warehouse_wise_item_balance_age_and_value.py
@@ -0,0 +1,107 @@
+# Copyright (c) 2013, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+
+# Copyright (c) 2013, Tristar Enterprises and contributors
+# For license information, please see license.txt
+
+from __future__ import unicode_literals
+import frappe
+from frappe import _
+from frappe.utils import flt, cint, getdate
+from erpnext.stock.report.stock_balance.stock_balance import get_item_details, get_item_reorder_details, get_item_warehouse_map
+from erpnext.stock.report.stock_ageing.stock_ageing import get_fifo_queue, get_average_age
+from dlt.utils import get_user_permissions_for
+
+def execute(filters=None):
+ if not filters: filters = {}
+
+ validate_filters(filters)
+
+ columns = get_columns(filters)
+ item_map = get_item_details(filters)
+ iwb_map = get_item_warehouse_map(filters)
+ warehouse_list = get_warehouse_list(filters)
+ item_ageing = get_fifo_queue(filters)
+ data = []
+ item_balance = {}
+ item_value = {}
+
+ for (company, item, warehouse) in sorted(iwb_map):
+ row = []
+ qty_dict = iwb_map[(company, item, warehouse)]
+ item_balance.setdefault((item, item_map[item]["item_group"]), [])
+ total_stock_value = 0.00
+ for wh in warehouse_list:
+ row += [qty_dict.bal_qty] if wh.name in warehouse else [0.00]
+ total_stock_value += qty_dict.bal_val if wh.name in warehouse else 0.00
+
+ item_balance[(item, item_map[item]["item_group"])].append(row)
+ item_value.setdefault((item, item_map[item]["item_group"]),[])
+ item_value[(item, item_map[item]["item_group"])].append(total_stock_value)
+
+
+ # sum bal_qty by item
+ for (item, item_group), wh_balance in item_balance.items():
+ total_stock_value = sum(item_value[(item, item_group)])
+ row = [item, item_group, total_stock_value]
+
+ fifo_queue = item_ageing[item]["fifo_queue"]
+ average_age = 0.00
+ if fifo_queue:
+ average_age = get_average_age(fifo_queue, filters["to_date"])
+
+ row += [average_age]
+
+ bal_qty = [sum(bal_qty) for bal_qty in zip(*wh_balance)]
+ total_qty = sum(bal_qty)
+ if len(warehouse_list) > 1:
+ row += [total_qty]
+ row += bal_qty
+
+ if total_qty > 0:
+ data.append(row)
+ elif not filters.get("filter_total_zero_qty"):
+ data.append(row)
+ add_warehouse_column(columns, warehouse_list)
+ return columns, data
+
+def get_columns(filters):
+ """return columns"""
+
+ columns = [
+ _("Item")+":Link/Item:180",
+ _("Item Group")+"::100",
+ _("Value")+":Currency:100",
+ _("Age")+":Float:60",
+ ]
+ return columns
+
+def validate_filters(filters):
+ if not (filters.get("item_code") or filters.get("warehouse")):
+ sle_count = flt(frappe.db.sql("""select count(name) from `tabStock Ledger Entry`""")[0][0])
+ if sle_count > 500000:
+ frappe.throw(_("Please set filter based on Item or Warehouse"))
+ if not filters.get("company"):
+ filters["company"] = frappe.defaults.get_user_default("Company")
+
+def get_warehouse_list(filters):
+ condition = ''
+ user_permitted_warehouse = get_user_permissions_for("Warehouse")
+ value = ()
+ if user_permitted_warehouse:
+ condition = "and name in %s"
+ value = set(user_permitted_warehouse)
+ elif not user_permitted_warehouse and filters.get("warehouse"):
+ condition = "and name = %s"
+ value = filters.get("warehouse")
+
+ return frappe.db.sql("""select name
+ from `tabWarehouse` where is_group = 0
+ {condition}""".format(condition=condition), value, as_dict=1)
+
+def add_warehouse_column(columns, warehouse_list):
+ if len(warehouse_list) > 1:
+ columns += [_("Total Qty")+":Int:50"]
+
+ for wh in warehouse_list:
+ columns += [_(wh.name)+":Int:54"]