chore: Stock Ageing and Item Shortage Reports with Charts
diff --git a/erpnext/stock/report/item_shortage_report/item_shortage_report.js b/erpnext/stock/report/item_shortage_report/item_shortage_report.js
new file mode 100644
index 0000000..ca42a33
--- /dev/null
+++ b/erpnext/stock/report/item_shortage_report/item_shortage_report.js
@@ -0,0 +1,26 @@
+// Copyright (c) 2016, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+/* eslint-disable */
+
+frappe.query_reports["Item Shortage Report"] = {
+ "filters": [
+ {
+ "fieldname": "company",
+ "label": __("Company"),
+ "fieldtype": "Link",
+ "width": "80",
+ "options": "Company",
+ "reqd": 1,
+ "default": frappe.defaults.get_default("company")
+ },
+ {
+ "fieldname": "warehouse",
+ "label": __("Warehouse"),
+ "fieldtype": "MultiSelectList",
+ "width": "100",
+ get_data: function(txt) {
+ return frappe.db.get_link_options('Warehouse', txt);
+ }
+ }
+ ]
+};
diff --git a/erpnext/stock/report/item_shortage_report/item_shortage_report.json b/erpnext/stock/report/item_shortage_report/item_shortage_report.json
index 577a853..17285c0 100644
--- a/erpnext/stock/report/item_shortage_report/item_shortage_report.json
+++ b/erpnext/stock/report/item_shortage_report/item_shortage_report.json
@@ -1,29 +1,30 @@
{
- "add_total_row": 0,
- "apply_user_permissions": 1,
- "creation": "2013-08-20 13:43:30",
- "disabled": 0,
- "docstatus": 0,
- "doctype": "Report",
- "idx": 3,
- "is_standard": "Yes",
- "json": "{\"add_total_row\": 0, \"sort_by\": \"Bin.projected_qty\", \"sort_order\": \"asc\", \"sort_by_next\": \"\", \"filters\": [[\"Bin\", \"projected_qty\", \"<\", \"0\"]], \"sort_order_next\": \"desc\", \"columns\": [[\"warehouse\", \"Bin\"], [\"item_code\", \"Bin\"], [\"actual_qty\", \"Bin\"], [\"ordered_qty\", \"Bin\"], [\"planned_qty\", \"Bin\"], [\"reserved_qty\", \"Bin\"], [\"projected_qty\", \"Bin\"]]}",
- "modified": "2017-02-24 20:00:46.439935",
- "modified_by": "Administrator",
- "module": "Stock",
- "name": "Item Shortage Report",
- "owner": "Administrator",
- "query": "SELECT bin.warehouse as \"Warehouse:Link/Warehouse:150\",\n\tbin.item_code as \"Item Code:Link/Item:100\",\n\tbin.actual_qty as \"Actual Quantity:Float:120\",\n\tbin.ordered_qty as \"Ordered Quantity:Float:120\",\n\tbin.planned_qty as \"Planned Quantity:Float:120\",\n\tbin.reserved_qty as \"Reserved Quantity:Float:120\",\n\tbin.projected_qty as \"Project Quantity:Float:120\",\n\titem.item_name as \"Item Name:Data:150\",\n\titem.description as \"Description::200\"\nFROM tabBin as bin\nINNER JOIN tabItem as item\nON bin.item_code=item.name\nWHERE bin.projected_qty<0\nORDER BY bin.projected_qty;",
- "ref_doctype": "Bin",
- "report_name": "Item Shortage Report",
- "report_type": "Query Report",
+ "add_total_row": 0,
+ "creation": "2013-08-20 13:43:30",
+ "disable_prepared_report": 0,
+ "disabled": 0,
+ "docstatus": 0,
+ "doctype": "Report",
+ "idx": 3,
+ "is_standard": "Yes",
+ "json": "{\"add_total_row\": 0, \"sort_by\": \"Bin.projected_qty\", \"sort_order\": \"asc\", \"sort_by_next\": \"\", \"filters\": [[\"Bin\", \"projected_qty\", \"<\", \"0\"]], \"sort_order_next\": \"desc\", \"columns\": [[\"warehouse\", \"Bin\"], [\"item_code\", \"Bin\"], [\"actual_qty\", \"Bin\"], [\"ordered_qty\", \"Bin\"], [\"planned_qty\", \"Bin\"], [\"reserved_qty\", \"Bin\"], [\"projected_qty\", \"Bin\"]]}",
+ "modified": "2020-05-14 12:32:07.158991",
+ "modified_by": "Administrator",
+ "module": "Stock",
+ "name": "Item Shortage Report",
+ "owner": "Administrator",
+ "prepared_report": 0,
+ "query": "",
+ "ref_doctype": "Bin",
+ "report_name": "Item Shortage Report",
+ "report_type": "Script Report",
"roles": [
{
"role": "Sales User"
- },
+ },
{
"role": "Purchase User"
- },
+ },
{
"role": "Stock User"
}
diff --git a/erpnext/stock/report/item_shortage_report/item_shortage_report.py b/erpnext/stock/report/item_shortage_report/item_shortage_report.py
new file mode 100644
index 0000000..07749eb
--- /dev/null
+++ b/erpnext/stock/report/item_shortage_report/item_shortage_report.py
@@ -0,0 +1,162 @@
+# Copyright (c) 2013, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+
+from __future__ import unicode_literals
+import frappe
+from frappe import _
+
+def execute(filters=None):
+ columns = get_columns()
+ conditions = get_conditions(filters)
+ data = get_data(conditions, filters)
+
+ if not data:
+ return [], []
+
+ chart_data = get_chart_data(data)
+
+ return columns, data, None, chart_data
+
+def get_conditions(filters):
+ conditions = ""
+
+ if filters.get("warehouse"):
+ conditions += "AND warehouse in %(warehouse)s"
+ if filters.get("company"):
+ conditions += "AND company = %(company)s"
+
+ return conditions
+
+def get_data(conditions, filters):
+ data = frappe.db.sql("""
+ SELECT
+ bin.warehouse,
+ bin.item_code,
+ bin.actual_qty ,
+ bin.ordered_qty ,
+ bin.planned_qty ,
+ bin.reserved_qty ,
+ bin.reserved_qty_for_production,
+ bin.projected_qty ,
+ warehouse.company,
+ item.item_name ,
+ item.description
+ FROM
+ `tabBin` bin,
+ `tabWarehouse` warehouse,
+ `tabItem` item
+ WHERE
+ bin.projected_qty<0
+ AND warehouse.name = bin.warehouse
+ AND bin.item_code=item.name
+ {0}
+ ORDER BY bin.projected_qty;""".format(conditions), filters, as_dict=1)
+
+ return data
+
+def get_chart_data(data):
+ labels, datapoints = [], []
+
+ for row in data:
+ labels.append(row.get("item_code"))
+ datapoints.append(row.get("projected_qty"))
+
+ if len(data) > 10:
+ labels = labels[:10]
+ datapoints = datapoints[:10]
+
+ return {
+ "data": {
+ "labels": labels,
+ "datasets":[
+ {
+ "name": _("Projected Qty"),
+ "values": datapoints
+ }
+ ]
+ },
+ "type": "bar"
+ }
+
+def get_columns():
+ columns = [
+ {
+ "label": _("Warehouse"),
+ "fieldname": "warehouse",
+ "fieldtype": "Link",
+ "options": "Warehouse",
+ "width": 150
+ },
+ {
+ "label": _("Item"),
+ "fieldname": "item_code",
+ "fieldtype": "Link",
+ "options": "Item",
+ "width": 150
+ },
+ {
+ "label": _("Actual Quantity"),
+ "fieldname": "actual_qty",
+ "fieldtype": "Float",
+ "width": 120,
+ "convertible": "qty"
+ },
+ {
+ "label": _("Ordered Quantity"),
+ "fieldname": "ordered_qty",
+ "fieldtype": "Float",
+ "width": 120,
+ "convertible": "qty"
+ },
+ {
+ "label": _("Planned Quantity"),
+ "fieldname": "planned_qty",
+ "fieldtype": "Float",
+ "width": 120,
+ "convertible": "qty"
+ },
+ {
+ "label": _("Reserved Quantity"),
+ "fieldname": "reserved_qty",
+ "fieldtype": "Float",
+ "width": 120,
+ "convertible": "qty"
+ },
+ {
+ "label": _("Reserved Quantity for Production"),
+ "fieldname": "reserved_qty_for_production",
+ "fieldtype": "Float",
+ "width": 120,
+ "convertible": "qty"
+ },
+ {
+ "label": _("Projected Quantity"),
+ "fieldname": "projected_qty",
+ "fieldtype": "Float",
+ "width": 120,
+ "convertible": "qty"
+ },
+ {
+ "label": _("Company"),
+ "fieldname": "company",
+ "fieldtype": "Link",
+ "options": "Company",
+ "width": 120
+ },
+ {
+ "label": _("Item Name"),
+ "fieldname": "item_name",
+ "fieldtype": "Data",
+ "width": 100
+ },
+ {
+ "label": _("Description"),
+ "fieldname": "description",
+ "fieldtype": "Data",
+ "width": 120
+ }
+ ]
+
+ return columns
+
+
diff --git a/erpnext/stock/report/stock_ageing/stock_ageing.py b/erpnext/stock/report/stock_ageing/stock_ageing.py
index 803a5c8..4ce967d 100644
--- a/erpnext/stock/report/stock_ageing/stock_ageing.py
+++ b/erpnext/stock/report/stock_ageing/stock_ageing.py
@@ -37,7 +37,9 @@
data.append(row)
- return columns, data
+ chart_data = get_chart_data(data, filters)
+
+ return columns, data, None, chart_data
def get_average_age(fifo_queue, to_date):
batch_age = age_qty = total_qty = 0.0
@@ -230,3 +232,31 @@
where wh.lft >= {0} and rgt <= {1})""".format(lft, rgt))
return "and {}".format(" and ".join(conditions)) if conditions else ""
+
+def get_chart_data(data, filters):
+ labels, datapoints = [], []
+
+ if filters.get("show_warehouse_wise_stock"):
+ return {}
+
+ if len(data) > 10:
+ data = data[:10]
+
+ for row in data:
+ labels.append(row[0])
+ datapoints.append(row[6])
+
+ print(labels)
+ print(datapoints)
+ return {
+ "data" : {
+ "labels": labels,
+ "datasets": [
+ {
+ "name": _("Average Age"),
+ "values": datapoints
+ }
+ ]
+ },
+ "type" : "bar"
+ }
\ No newline at end of file