Merge pull request #21724 from rohitwaghchaure/production-forecasting
feat: production forecasting using exponential smoothing method
diff --git a/erpnext/accounts/report/financial_statements.py b/erpnext/accounts/report/financial_statements.py
index 7fb598b..4a35a66 100644
--- a/erpnext/accounts/report/financial_statements.py
+++ b/erpnext/accounts/report/financial_statements.py
@@ -19,7 +19,7 @@
from erpnext.accounts.doctype.accounting_dimension.accounting_dimension import get_accounting_dimensions, get_dimension_with_children
def get_period_list(from_fiscal_year, to_fiscal_year, period_start_date, period_end_date, filter_based_on, periodicity, accumulated_values=False,
- company=None, reset_period_on_fy_change=True):
+ company=None, reset_period_on_fy_change=True, ignore_fiscal_year=False):
"""Get a list of dict {"from_date": from_date, "to_date": to_date, "key": key, "label": label}
Periodicity can be (Yearly, Quarterly, Monthly)"""
@@ -67,8 +67,9 @@
# if a fiscal year ends before a 12 month period
period.to_date = year_end_date
- period.to_date_fiscal_year = get_fiscal_year(period.to_date, company=company)[0]
- period.from_date_fiscal_year_start_date = get_fiscal_year(period.from_date, company=company)[1]
+ if not ignore_fiscal_year:
+ period.to_date_fiscal_year = get_fiscal_year(period.to_date, company=company)[0]
+ period.from_date_fiscal_year_start_date = get_fiscal_year(period.from_date, company=company)[1]
period_list.append(period)
diff --git a/erpnext/manufacturing/desk_page/manufacturing/manufacturing.json b/erpnext/manufacturing/desk_page/manufacturing/manufacturing.json
index ecd2dc9..f2e07bf 100644
--- a/erpnext/manufacturing/desk_page/manufacturing/manufacturing.json
+++ b/erpnext/manufacturing/desk_page/manufacturing/manufacturing.json
@@ -43,10 +43,11 @@
"docstatus": 0,
"doctype": "Desk Page",
"extends_another_page": 0,
+ "hide_custom": 0,
"idx": 0,
"is_standard": 1,
"label": "Manufacturing",
- "modified": "2020-05-19 14:05:59.100891",
+ "modified": "2020-05-20 11:50:20.029056",
"modified_by": "Administrator",
"module": "Manufacturing",
"name": "Manufacturing",
@@ -89,16 +90,31 @@
"type": "DocType"
},
{
+ "label": "Dashboard",
+ "link_to": "Manufacturing",
+ "restrict_to_domain": "Manufacturing",
+ "type": "Dashboard"
+ },
+ {
+ "label": "Forecasting",
+ "link_to": "Exponential Smoothing Forecasting",
+ "type": "Report"
+ },
+ {
"label": "Work Order Summary",
"link_to": "Work Order Summary",
"restrict_to_domain": "Manufacturing",
"type": "Report"
},
{
- "label": "Dashboard",
- "link_to": "Manufacturing",
- "restrict_to_domain": "Manufacturing",
- "type": "Dashboard"
+ "label": "BOM Stock Report",
+ "link_to": "BOM Stock Report",
+ "type": "Report"
+ },
+ {
+ "label": "Production Planning Report",
+ "link_to": "Production Planning Report",
+ "type": "Report"
}
]
}
\ No newline at end of file
diff --git a/erpnext/manufacturing/report/exponential_smoothing_forecasting/__init__.py b/erpnext/manufacturing/report/exponential_smoothing_forecasting/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/manufacturing/report/exponential_smoothing_forecasting/__init__.py
diff --git a/erpnext/manufacturing/report/exponential_smoothing_forecasting/exponential_smoothing_forecasting.js b/erpnext/manufacturing/report/exponential_smoothing_forecasting/exponential_smoothing_forecasting.js
new file mode 100644
index 0000000..123a82a
--- /dev/null
+++ b/erpnext/manufacturing/report/exponential_smoothing_forecasting/exponential_smoothing_forecasting.js
@@ -0,0 +1,97 @@
+// Copyright (c) 2016, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+/* eslint-disable */
+
+frappe.query_reports["Exponential Smoothing Forecasting"] = {
+ "filters": [
+ {
+ "fieldname":"company",
+ "label": __("Company"),
+ "fieldtype": "Link",
+ "options": "Company",
+ "reqd": 1,
+ "default": frappe.defaults.get_user_default("Company")
+ },
+ {
+ "fieldname":"from_date",
+ "label": __("From Date"),
+ "fieldtype": "Date",
+ "default": frappe.datetime.get_today(),
+ "reqd": 1
+ },
+ {
+ "fieldname":"to_date",
+ "label": __("To Date"),
+ "fieldtype": "Date",
+ "default": frappe.datetime.add_months(frappe.datetime.get_today(), 12),
+ "reqd": 1
+ },
+ {
+ "fieldname":"based_on_document",
+ "label": __("Based On Document"),
+ "fieldtype": "Select",
+ "options": ["Sales Order", "Delivery Note", "Quotation"],
+ "default": "Sales Order",
+ "reqd": 1
+ },
+ {
+ "fieldname":"based_on_field",
+ "label": __("Based On"),
+ "fieldtype": "Select",
+ "options": ["Qty", "Amount"],
+ "default": "Qty",
+ "reqd": 1
+ },
+ {
+ "fieldname":"no_of_years",
+ "label": __("Based On Data ( in years )"),
+ "fieldtype": "Select",
+ "options": [3, 6, 9],
+ "default": 3,
+ "reqd": 1
+ },
+ {
+ "fieldname": "periodicity",
+ "label": __("Periodicity"),
+ "fieldtype": "Select",
+ "options": [
+ { "value": "Monthly", "label": __("Monthly") },
+ { "value": "Quarterly", "label": __("Quarterly") },
+ { "value": "Half-Yearly", "label": __("Half-Yearly") },
+ { "value": "Yearly", "label": __("Yearly") }
+ ],
+ "default": "Yearly",
+ "reqd": 1
+ },
+ {
+ "fieldname":"smoothing_constant",
+ "label": __("Smoothing Constant"),
+ "fieldtype": "Select",
+ "options": [0.0, 0.1, 0.2, 0.3, 0.4, 0.5, 0.6, 0.7, 0.8, 0.9, 1.0],
+ "reqd": 1,
+ "default": 0.3
+ },
+ {
+ "fieldname":"item_code",
+ "label": __("Item Code"),
+ "fieldtype": "Link",
+ "options": "Item"
+ },
+ {
+ "fieldname":"warehouse",
+ "label": __("Warehouse"),
+ "fieldtype": "Link",
+ "options": "Warehouse",
+ get_query: () => {
+ var company = frappe.query_report.get_filter_value('company');
+ if (company) {
+ return {
+ filters: {
+ 'company': company
+ }
+ };
+ }
+ }
+ }
+ ]
+};
diff --git a/erpnext/manufacturing/report/exponential_smoothing_forecasting/exponential_smoothing_forecasting.json b/erpnext/manufacturing/report/exponential_smoothing_forecasting/exponential_smoothing_forecasting.json
new file mode 100644
index 0000000..5092ef4
--- /dev/null
+++ b/erpnext/manufacturing/report/exponential_smoothing_forecasting/exponential_smoothing_forecasting.json
@@ -0,0 +1,40 @@
+{
+ "add_total_row": 0,
+ "creation": "2020-05-15 05:18:55.838030",
+ "disable_prepared_report": 0,
+ "disabled": 0,
+ "docstatus": 0,
+ "doctype": "Report",
+ "idx": 0,
+ "is_standard": "Yes",
+ "letter_head": "",
+ "modified": "2020-05-15 05:18:55.838030",
+ "modified_by": "Administrator",
+ "module": "Manufacturing",
+ "name": "Exponential Smoothing Forecasting",
+ "owner": "Administrator",
+ "prepared_report": 0,
+ "ref_doctype": "Sales Order",
+ "report_name": "Exponential Smoothing Forecasting",
+ "report_type": "Script Report",
+ "roles": [
+ {
+ "role": "Manufacturing User"
+ },
+ {
+ "role": "Stock User"
+ },
+ {
+ "role": "Manufacturing Manager"
+ },
+ {
+ "role": "Stock Manager"
+ },
+ {
+ "role": "Sales Manager"
+ },
+ {
+ "role": "Sales User"
+ }
+ ]
+}
\ No newline at end of file
diff --git a/erpnext/manufacturing/report/exponential_smoothing_forecasting/exponential_smoothing_forecasting.py b/erpnext/manufacturing/report/exponential_smoothing_forecasting/exponential_smoothing_forecasting.py
new file mode 100644
index 0000000..b5127f1
--- /dev/null
+++ b/erpnext/manufacturing/report/exponential_smoothing_forecasting/exponential_smoothing_forecasting.py
@@ -0,0 +1,222 @@
+# Copyright (c) 2020, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+
+from __future__ import unicode_literals
+import frappe, erpnext
+from frappe import _
+from frappe.utils import flt, nowdate, add_years, cint, getdate
+from erpnext.accounts.report.financial_statements import get_period_list
+from erpnext.stock.doctype.warehouse.warehouse import get_child_warehouses
+
+def execute(filters=None):
+ return ForecastingReport(filters).execute_report()
+
+class ExponentialSmoothingForecast(object):
+ def forecast_future_data(self):
+ for key, value in self.period_wise_data.items():
+ forecast_data = []
+ for period in self.period_list:
+ forecast_key = "forecast_" + period.key
+
+ if value.get(period.key) and not forecast_data:
+ value[forecast_key] = flt(value.get("avg", 0)) or flt(value.get(period.key))
+
+ # will be use to forecaset next period
+ forecast_data.append([value.get(period.key), value.get(forecast_key)])
+ elif forecast_data:
+ previous_period_data = forecast_data[-1]
+ value[forecast_key] = (previous_period_data[1] +
+ flt(self.filters.smoothing_constant) * (
+ flt(previous_period_data[0]) - flt(previous_period_data[1])
+ )
+ )
+
+class ForecastingReport(ExponentialSmoothingForecast):
+ def __init__(self, filters=None):
+ self.filters = frappe._dict(filters or {})
+ self.data = []
+ self.doctype = self.filters.based_on_document
+ self.child_doctype = self.doctype + " Item"
+ self.based_on_field = ("qty"
+ if self.filters.based_on_field == "Qty" else "amount")
+ self.fieldtype = "Float" if self.based_on_field == "qty" else "Currency"
+ self.company_currency = erpnext.get_company_currency(self.filters.company)
+
+ def execute_report(self):
+ self.prepare_periodical_data()
+ self.forecast_future_data()
+ self.data = self.period_wise_data.values()
+ self.add_total()
+
+ columns = self.get_columns()
+ charts = self.get_chart_data()
+ summary_data = self.get_summary_data()
+
+ return columns, self.data, None, charts, summary_data
+
+ def prepare_periodical_data(self):
+ self.period_wise_data = {}
+
+ from_date = add_years(self.filters.from_date, cint(self.filters.no_of_years) * -1)
+ self.period_list = get_period_list(from_date, self.filters.to_date,
+ from_date, self.filters.to_date, None, self.filters.periodicity, ignore_fiscal_year=True)
+
+ order_data = self.get_data_for_forecast() or []
+
+ for entry in order_data:
+ key = (entry.item_code, entry.warehouse)
+ if key not in self.period_wise_data:
+ self.period_wise_data[key] = entry
+
+ period_data = self.period_wise_data[key]
+ for period in self.period_list:
+ # check if posting date is within the period
+ if (entry.posting_date >= period.from_date and entry.posting_date <= period.to_date):
+ period_data[period.key] = period_data.get(period.key, 0.0) + flt(entry.get(self.based_on_field))
+
+ for key, value in self.period_wise_data.items():
+ list_of_period_value = [value.get(p.key, 0) for p in self.period_list]
+
+ if list_of_period_value:
+ value["avg"] = sum(list_of_period_value) / len(list_of_period_value)
+
+ def get_data_for_forecast(self):
+ cond = ""
+ if self.filters.item_code:
+ cond = " AND soi.item_code = %s" %(frappe.db.escape(self.filters.item_code))
+
+ warehouses = []
+ if self.filters.warehouse:
+ warehouses = get_child_warehouses(self.filters.warehouse)
+ cond += " AND soi.warehouse in ({})".format(','.join(['%s'] * len(warehouses)))
+
+ input_data = [self.filters.from_date, self.filters.company]
+ if warehouses:
+ input_data.extend(warehouses)
+
+ date_field = "posting_date" if self.doctype == "Delivery Note" else "transaction_date"
+
+ return frappe.db.sql("""
+ SELECT
+ so.{date_field} as posting_date, soi.item_code, soi.warehouse,
+ soi.item_name, soi.stock_qty as qty, soi.base_amount as amount
+ FROM
+ `tab{doc}` so, `tab{child_doc}` soi
+ WHERE
+ so.docstatus = 1 AND so.name = soi.parent AND
+ so.{date_field} < %s AND so.company = %s {cond}
+ """.format(doc=self.doctype, child_doc=self.child_doctype, date_field=date_field, cond=cond),
+ tuple(input_data), as_dict=1)
+
+ def add_total(self):
+ total_row = {
+ "item_code": _(frappe.bold("Total Quantity"))
+ }
+
+ for value in self.data:
+ for period in self.period_list:
+ forecast_key = "forecast_" + period.key
+ if forecast_key not in total_row:
+ total_row.setdefault(forecast_key, 0.0)
+
+ if period.key not in total_row:
+ total_row.setdefault(period.key, 0.0)
+
+ total_row[forecast_key] += value.get(forecast_key, 0.0)
+ total_row[period.key] += value.get(period.key, 0.0)
+
+ self.data.append(total_row)
+
+ def get_columns(self):
+ columns = [{
+ "label": _("Item Code"),
+ "options": "Item",
+ "fieldname": "item_code",
+ "fieldtype": "Link",
+ "width": 130
+ }, {
+ "label": _("Warehouse"),
+ "options": "Warehouse",
+ "fieldname": "warehouse",
+ "fieldtype": "Link",
+ "width": 130
+ }]
+
+ width = 150 if self.filters.periodicity in ['Yearly', "Half-Yearly", "Quarterly"] else 100
+ for period in self.period_list:
+ if (self.filters.periodicity in ['Yearly', "Half-Yearly", "Quarterly"]
+ or period.from_date >= getdate(self.filters.from_date)):
+
+ forecast_key = 'forecast_' + period.key
+
+ columns.append({
+ "label": _(period.label),
+ "fieldname": forecast_key,
+ "fieldtype": self.fieldtype,
+ "width": width,
+ "default": 0.0
+ })
+
+ return columns
+
+ def get_chart_data(self):
+ if not self.data: return
+
+ labels = []
+ self.total_demand = []
+ self.total_forecast = []
+ self.total_history_forecast = []
+ self.total_future_forecast = []
+
+ for period in self.period_list:
+ forecast_key = "forecast_" + period.key
+
+ labels.append(_(period.label))
+
+ if period.from_date < getdate(self.filters.from_date):
+ self.total_demand.append(self.data[-1].get(period.key, 0))
+ self.total_history_forecast.append(self.data[-1].get(forecast_key, 0))
+ else:
+ self.total_future_forecast.append(self.data[-1].get(forecast_key, 0))
+
+ self.total_forecast.append(self.data[-1].get(forecast_key, 0))
+
+ return {
+ "data": {
+ "labels": labels,
+ "datasets": [
+ {
+ "name": "Demand",
+ "values": self.total_demand
+ },
+ {
+ "name": "Forecast",
+ "values": self.total_forecast
+ }
+ ]
+ },
+ "type": "line"
+ }
+
+ def get_summary_data(self):
+ return [
+ {
+ "value": sum(self.total_demand),
+ "label": _("Total Demand (Past Data)"),
+ "currency": self.company_currency,
+ "datatype": self.fieldtype
+ },
+ {
+ "value": sum(self.total_history_forecast),
+ "label": _("Total Forecast (Past Data)"),
+ "currency": self.company_currency,
+ "datatype": self.fieldtype
+ },
+ {
+ "value": sum(self.total_future_forecast),
+ "indicator": "Green",
+ "label": _("Total Forecast (Future Data)"),
+ "currency": self.company_currency,
+ "datatype": self.fieldtype
+ }
+ ]
\ No newline at end of file