Stock analytics script report (#15630)
* Stock analytics script report
* Codacy Issue Fixes
* Codacy Issue Fixes
* Removed Trailing Whitespaces
* Code cleaning and optimization
* Minor Changes
* Code cleaning and spacing
* Added link for stock analytics in stock.py
* Refactoring and code cleaning
* Codacy issue fixes
diff --git a/erpnext/config/stock.py b/erpnext/config/stock.py
index abdca0d..60eee71 100644
--- a/erpnext/config/stock.py
+++ b/erpnext/config/stock.py
@@ -218,10 +218,10 @@
"doctype": "Item Price",
},
{
- "type": "page",
- "name": "stock-analytics",
- "label": _("Stock Analytics"),
- "icon": "fa fa-bar-chart"
+ "type": "report",
+ "is_query_report": True,
+ "name": "Stock Analytics",
+ "doctype": "Stock Entry"
},
{
"type": "report",
diff --git a/erpnext/stock/report/stock_analytics/__init__.py b/erpnext/stock/report/stock_analytics/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/stock/report/stock_analytics/__init__.py
diff --git a/erpnext/stock/report/stock_analytics/stock_analytics.js b/erpnext/stock/report/stock_analytics/stock_analytics.js
new file mode 100644
index 0000000..6010ea9
--- /dev/null
+++ b/erpnext/stock/report/stock_analytics/stock_analytics.js
@@ -0,0 +1,136 @@
+// Copyright (c) 2016, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+/* eslint-disable */
+
+frappe.query_reports["Stock Analytics"] = {
+ "filters": [
+ {
+ fieldname: "item_group",
+ label: __("Item Group"),
+ fieldtype: "Link",
+ options:"Item Group",
+ default: "",
+ },
+ {
+ fieldname: "item_code",
+ label: __("Item"),
+ fieldtype: "Link",
+ options:"Item",
+ default: "",
+ },
+ {
+ fieldname: "value_quantity",
+ label: __("Value Or Qty"),
+ fieldtype: "Select",
+ options: [
+ { "value": "Value", "label": __("Value") },
+ { "value": "Quantity", "label": __("Quantity") }
+ ],
+ default: "Value",
+ reqd: 1
+ },
+ {
+ fieldname: "brand",
+ label: __("Brand"),
+ fieldtype: "Link",
+ options:"Brand",
+ default: "",
+ },
+ {
+ fieldname: "warehouse",
+ label: __("Warehouse"),
+ fieldtype: "Link",
+ options:"Warehouse",
+ default: "",
+ },
+ {
+ fieldname: "from_date",
+ label: __("From Date"),
+ fieldtype: "Date",
+ default: frappe.defaults.get_global_default("year_start_date"),
+ reqd: 1
+ },
+ {
+ fieldname:"to_date",
+ label: __("To Date"),
+ fieldtype: "Date",
+ default: frappe.defaults.get_global_default("year_end_date"),
+ reqd: 1
+ },
+ {
+ fieldname: "range",
+ label: __("Range"),
+ fieldtype: "Select",
+ options: [
+ { "value": "Weekly", "label": __("Weekly") },
+ { "value": "Monthly", "label": __("Monthly") },
+ { "value": "Quarterly", "label": __("Quarterly") },
+ { "value": "Yearly", "label": __("Yearly") }
+ ],
+ default: "Monthly",
+ reqd: 1
+ }
+ ],
+ "formatter": function(value, row, column, data) {
+ if(!value && (column.fieldname == 'brand' || column.fieldname == 'uom')){
+ value = ""
+ }
+
+ if(Number(value)){
+ value = value.toFixed(2)
+ }
+
+ return value;
+ },
+ get_datatable_options(options) {
+ return Object.assign(options, {
+ checkboxColumn: true,
+ events: {
+ onCheckRow: function(data) {
+ row_name = data[2].content;
+ row_values = data.slice(6).map(function (column) {
+ return column.content;
+ })
+
+ entry = {
+ 'name':row_name,
+ 'values':row_values
+ }
+
+ let raw_data = frappe.query_report.chart.data;
+ let new_datasets = raw_data.datasets;
+
+ var found = false;
+
+ for(var i=0; i < new_datasets.length;i++){
+ if(new_datasets[i].name == row_name){
+ found = true;
+ new_datasets.splice(i,1);
+ break;
+ }
+ }
+
+ if(!found){
+ new_datasets.push(entry);
+ }
+
+ let new_data = {
+ labels: raw_data.labels,
+ datasets: new_datasets
+ }
+
+ setTimeout(() => {
+ frappe.query_report.chart.update(new_data)
+ },200)
+
+
+ setTimeout(() => {
+ frappe.query_report.chart.draw(true);
+ }, 800)
+
+ frappe.query_report.raw_chart_data = new_data;
+ },
+ }
+ })
+ },
+}
diff --git a/erpnext/stock/report/stock_analytics/stock_analytics.json b/erpnext/stock/report/stock_analytics/stock_analytics.json
new file mode 100644
index 0000000..efd5e99
--- /dev/null
+++ b/erpnext/stock/report/stock_analytics/stock_analytics.json
@@ -0,0 +1,32 @@
+{
+ "add_total_row": 0,
+ "creation": "2018-10-08 12:11:32.133020",
+ "disabled": 0,
+ "docstatus": 0,
+ "doctype": "Report",
+ "idx": 0,
+ "is_standard": "Yes",
+ "modified": "2018-10-08 12:18:42.834270",
+ "modified_by": "Administrator",
+ "module": "Stock",
+ "name": "Stock Analytics",
+ "owner": "Administrator",
+ "prepared_report": 0,
+ "ref_doctype": "Stock Entry",
+ "report_name": "Stock Analytics",
+ "report_type": "Script Report",
+ "roles": [
+ {
+ "role": "Manufacturing Manager"
+ },
+ {
+ "role": "Stock Manager"
+ },
+ {
+ "role": "Stock User"
+ },
+ {
+ "role": "Manufacturing User"
+ }
+ ]
+}
\ No newline at end of file
diff --git a/erpnext/stock/report/stock_analytics/stock_analytics.py b/erpnext/stock/report/stock_analytics/stock_analytics.py
new file mode 100644
index 0000000..5a8a672
--- /dev/null
+++ b/erpnext/stock/report/stock_analytics/stock_analytics.py
@@ -0,0 +1,185 @@
+# 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 _, scrub
+from frappe.utils import getdate, flt
+from erpnext.stock.report.stock_balance.stock_balance import (get_items, get_stock_ledger_entries, get_item_details)
+from erpnext.accounts.utils import get_fiscal_year
+from six import iteritems
+
+def execute(filters=None):
+ filters = frappe._dict(filters or {})
+ columns = get_columns(filters)
+ data = get_data(filters)
+ chart = get_chart_data(columns)
+
+ return columns, data, None, chart
+
+def get_columns(filters):
+ columns = [
+ {
+ "label": _("Item"),
+ "options":"Item",
+ "fieldname": "name",
+ "fieldtype": "Link",
+ "width": 140
+ },
+ {
+ "label": _("Item Name"),
+ "options":"Item",
+ "fieldname": "item_name",
+ "fieldtype": "Link",
+ "width": 140
+ },
+ {
+ "label": _("Item Group"),
+ "options":"Item Group",
+ "fieldname": "item_group",
+ "fieldtype": "Link",
+ "width": 140
+ },
+ {
+ "label": _("Brand"),
+ "fieldname": "brand",
+ "fieldtype": "Data",
+ "width": 120
+ },
+ {
+ "label": _("UOM"),
+ "fieldname": "uom",
+ "fieldtype": "Data",
+ "width": 120
+ }]
+
+ ranges = get_period_date_ranges(filters)
+
+ for dummy, end_date in ranges:
+ period = get_period(end_date, filters)
+
+ columns.append({
+ "label": _(period),
+ "fieldname":scrub(period),
+ "fieldtype": "Float",
+ "width": 120
+ })
+
+ return columns
+
+def get_period_date_ranges(filters):
+ from dateutil.relativedelta import relativedelta
+ from_date, to_date = getdate(filters.from_date), getdate(filters.to_date)
+
+ increment = {
+ "Monthly": 1,
+ "Quarterly": 3,
+ "Half-Yearly": 6,
+ "Yearly": 12
+ }.get(filters.range,1)
+
+ periodic_daterange = []
+ for dummy in range(1, 53, increment):
+ if filters.range == "Weekly":
+ period_end_date = from_date + relativedelta(days=6)
+ else:
+ period_end_date = from_date + relativedelta(months=increment, days=-1)
+
+ if period_end_date > to_date:
+ period_end_date = to_date
+ periodic_daterange.append([from_date, period_end_date])
+
+ from_date = period_end_date + relativedelta(days=1)
+ if period_end_date == to_date:
+ break
+
+ return periodic_daterange
+
+def get_period(posting_date, filters):
+ months = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"]
+
+ if filters.range == 'Weekly':
+ period = "Week " + str(posting_date.isocalendar()[1])
+ elif filters.range == 'Monthly':
+ period = months[posting_date.month - 1]
+ elif filters.range == 'Quarterly':
+ period = "Quarter " + str(((posting_date.month-1)//3)+1)
+ else:
+ year = get_fiscal_year(posting_date, company=filters.company)
+ period = str(year[2])
+
+ return period
+
+
+def get_periodic_data(entry, filters):
+ periodic_data = {}
+ for d in entry:
+ period = get_period(d.posting_date, filters)
+ bal_qty = 0
+
+ if d.voucher_type == "Stock Reconciliation":
+ if periodic_data.get(d.item_code):
+ bal_qty = periodic_data[d.item_code]["balance"]
+
+ qty_diff = d.qty_after_transaction - bal_qty
+ else:
+ qty_diff = d.actual_qty
+
+ if filters["value_quantity"] == 'Quantity':
+ value = qty_diff
+ else:
+ value = d.stock_value_difference
+
+ periodic_data.setdefault(d.item_code, {}).setdefault(period, 0.0)
+ periodic_data.setdefault(d.item_code, {}).setdefault("balance", 0.0)
+
+ periodic_data[d.item_code]["balance"] += value
+ periodic_data[d.item_code][period] = periodic_data[d.item_code]["balance"]
+
+
+ return periodic_data
+
+def get_data(filters):
+ data = []
+ items = get_items(filters)
+ sle = get_stock_ledger_entries(filters, items)
+ item_details = get_item_details(items, sle, filters)
+ periodic_data = get_periodic_data(sle, filters)
+ ranges = get_period_date_ranges(filters)
+
+ for dummy, item_data in iteritems(item_details):
+ row = {
+ "name": item_data.name,
+ "item_name": item_data.item_name,
+ "item_group": item_data.item_group,
+ "uom": item_data.stock_uom,
+ "brand": item_data.brand,
+ }
+ total = 0
+ for dummy, end_date in ranges:
+ period = get_period(end_date, filters)
+ amount = flt(periodic_data.get(item_data.name, {}).get(period))
+ row[scrub(period)] = amount
+ total += amount
+ row["total"] = total
+ data.append(row)
+
+ return data
+
+def get_chart_data(columns):
+ labels = [d.get("label") for d in columns[4:]]
+ chart = {
+ "data": {
+ 'labels': labels,
+ 'datasets':[
+ { "values": ['0' for d in columns[4:]] }
+ ]
+ }
+ }
+ chart["type"] = "line"
+
+ return chart
+
+
+
+