feat: delayed order reports
diff --git a/erpnext/stock/report/delayed_item_report/__init__.py b/erpnext/stock/report/delayed_item_report/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/stock/report/delayed_item_report/__init__.py
diff --git a/erpnext/stock/report/delayed_item_report/delayed_item_report.js b/erpnext/stock/report/delayed_item_report/delayed_item_report.js
new file mode 100644
index 0000000..5d160b1
--- /dev/null
+++ b/erpnext/stock/report/delayed_item_report/delayed_item_report.js
@@ -0,0 +1,62 @@
+// Copyright (c) 2016, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+/* eslint-disable */
+
+frappe.query_reports["Delayed Item Report"] = {
+ "filters": [
+ {
+ fieldname: "company",
+ label: __("Company"),
+ fieldtype: "Link",
+ options: "Company",
+ default: frappe.defaults.get_default("company"),
+ reqd: 1
+ },
+ {
+ fieldname:"from_date",
+ label: __("From Date"),
+ fieldtype: "Date",
+ default: frappe.datetime.month_start(),
+ reqd: 1
+ },
+ {
+ fieldname:"to_date",
+ label: __("To Date"),
+ fieldtype: "Date",
+ default: frappe.datetime.now_date(),
+ reqd: 1
+ },
+ {
+ fieldname:"sales_order",
+ label: __("Sales Order"),
+ fieldtype: "Link",
+ options: "Sales Order",
+ },
+ {
+ fieldname:"customer",
+ label: __("Customer"),
+ fieldtype: "Link",
+ options: "Customer",
+ },
+ {
+ fieldname:"customer_group",
+ label: __("Customer Group"),
+ fieldtype: "Link",
+ options: "Customer Group",
+ },
+ {
+ fieldname:"item_group",
+ label: __("Item Group"),
+ fieldtype: "Link",
+ options: "Item Group",
+ },
+ {
+ fieldname:"based_on",
+ label: __("Based On"),
+ fieldtype: "Select",
+ options: ["Delivery Note", "Sales Invoice"],
+ default: "Delivery Note",
+ reqd: 1
+ },
+ ]
+}
\ No newline at end of file
diff --git a/erpnext/stock/report/delayed_item_report/delayed_item_report.json b/erpnext/stock/report/delayed_item_report/delayed_item_report.json
new file mode 100644
index 0000000..f336cec
--- /dev/null
+++ b/erpnext/stock/report/delayed_item_report/delayed_item_report.json
@@ -0,0 +1,37 @@
+{
+ "add_total_row": 0,
+ "creation": "2019-06-17 12:45:07.324014",
+ "disable_prepared_report": 0,
+ "disabled": 0,
+ "docstatus": 0,
+ "doctype": "Report",
+ "idx": 0,
+ "is_standard": "Yes",
+ "letter_head": "Gadgets International",
+ "modified": "2019-06-17 12:45:07.324014",
+ "modified_by": "Administrator",
+ "module": "Stock",
+ "name": "Delayed Item Report",
+ "owner": "Administrator",
+ "prepared_report": 0,
+ "ref_doctype": "Delivery Note",
+ "report_name": "Delayed Item Report",
+ "report_type": "Script Report",
+ "roles": [
+ {
+ "role": "Accounts User"
+ },
+ {
+ "role": "Sales User"
+ },
+ {
+ "role": "Stock Manager"
+ },
+ {
+ "role": "Stock User"
+ },
+ {
+ "role": "Maintenance User"
+ }
+ ]
+}
\ No newline at end of file
diff --git a/erpnext/stock/report/delayed_item_report/delayed_item_report.py b/erpnext/stock/report/delayed_item_report/delayed_item_report.py
new file mode 100644
index 0000000..7b968b8
--- /dev/null
+++ b/erpnext/stock/report/delayed_item_report/delayed_item_report.py
@@ -0,0 +1,168 @@
+# 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 _
+from frappe.utils import date_diff
+
+def execute(filters=None, consolidated = False):
+ data, columns = DelayedItemReport(filters).run()
+
+ return data, columns
+
+class DelayedItemReport(object):
+ def __init__(self, filters=None):
+ self.filters = frappe._dict(filters or {})
+
+ def run(self):
+ return self.get_columns(), self.get_data() or []
+
+ def get_data(self, consolidated=False):
+ conditions = ""
+
+ doctype = self.filters.get("based_on")
+ child_doc= "%s Item" % doctype
+
+ if doctype == "Sales Invoice":
+ conditions = " and `tabSales Invoice`.update_stock = 1 and `tabSales Invoice`.is_pos = 0"
+
+ if self.filters.get("item_group"):
+ conditions += " and `tab%s`.item_group = %s" % (child_doc,
+ frappe.db.escape(self.filters.get("item_group")))
+
+ for field in ["customer", "customer_group", "company"]:
+ if self.filters.get(field):
+ conditions += " and `tab%s`.%s = %s" % (doctype,
+ field, frappe.db.escape(self.filters.get(field)))
+
+ sales_order_field = "against_sales_order"
+ if doctype == "Sales Invoice":
+ sales_order_field = "sales_order"
+
+ if self.filters.get("sales_order"):
+ conditions = " and `tab%s`.%s = '%s'" %(child_doc, sales_order_field, self.filters.get("sales_order"))
+
+ self.transactions = frappe.db.sql(""" SELECT `tab{child_doc}`.item_code, `tab{child_doc}`.item_name,
+ `tab{child_doc}`.item_group, `tab{child_doc}`.qty, `tab{child_doc}`.rate, `tab{child_doc}`.amount,
+ `tab{child_doc}`.so_detail, `tab{child_doc}`.{so_field} as sales_order,
+ `tab{doctype}`.customer, `tab{doctype}`.posting_date, `tab{doctype}`.name, `tab{doctype}`.grand_total
+ FROM `tab{child_doc}`, `tab{doctype}`
+ WHERE
+ `tab{child_doc}`.parent = `tab{doctype}`.name and `tab{doctype}`.docstatus = 1 and
+ `tab{doctype}`.posting_date between %(from_date)s and %(to_date)s and
+ `tab{child_doc}`.{so_field} is not null and `tab{child_doc}`.{so_field} != '' {cond}
+ """.format(cond=conditions, doctype=doctype, child_doc=child_doc, so_field=sales_order_field), {
+ 'from_date': self.filters.get('from_date'),
+ 'to_date': self.filters.get('to_date')
+ }, as_dict=1)
+
+ if self.transactions:
+ self.filter_transactions_data(consolidated)
+
+ return self.transactions
+
+ def filter_transactions_data(self, consolidated=False):
+ sales_orders = [d.sales_order for d in self.transactions]
+ doctype = "Sales Order"
+ filters = {'name': ('in', sales_orders)}
+
+ if not consolidated:
+ sales_order_items = [d.so_detail for d in self.transactions]
+ doctype = "Sales Order Item"
+ filters = {'parent': ('in', sales_orders), 'name': ('in', sales_order_items)}
+
+ so_data = {}
+ for d in frappe.get_all(doctype, filters = filters,
+ fields = ["delivery_date", "parent", "name"]):
+ key = d.name if consolidated else (d.parent, d.name)
+ if key not in so_data:
+ so_data.setdefault(key, d.delivery_date)
+
+ for row in self.transactions:
+ key = row.sales_order if consolidated else (row.sales_order, row.so_detail)
+ row.update({
+ 'delivery_date': so_data.get(key),
+ 'delayed_days': date_diff(row.posting_date, so_data.get(key))
+ })
+
+ return self.transactions
+
+ def get_columns(self):
+ based_on = self.filters.get("based_on")
+
+ return [{
+ "label": _(based_on),
+ "fieldname": "name",
+ "fieldtype": "Link",
+ "options": based_on,
+ "width": 100
+ },{
+ "label": _("Customer"),
+ "fieldname": "customer",
+ "fieldtype": "Link",
+ "options": "Customer",
+ "width": 100
+ },
+ {
+ "label": _("Expected Delivery Date"),
+ "fieldname": "delivery_date",
+ "fieldtype": "Date",
+ "width": 100
+ },
+ {
+ "label": _("Actual Delivery Date"),
+ "fieldname": "posting_date",
+ "fieldtype": "Date",
+ "width": 100
+ },
+ {
+ "label": _("Item Code"),
+ "fieldname": "item_code",
+ "fieldtype": "Link",
+ "options": "Item",
+ "width": 100
+ },
+ {
+ "label": _("Item Name"),
+ "fieldname": "item_name",
+ "fieldtype": "Data",
+ "width": 100
+ },
+ {
+ "label": _("Quantity"),
+ "fieldname": "qty",
+ "fieldtype": "Float",
+ "width": 100
+ },
+ {
+ "label": _("Rate"),
+ "fieldname": "rate",
+ "fieldtype": "Currency",
+ "width": 100
+ },
+ {
+ "label": _("Amount"),
+ "fieldname": "amount",
+ "fieldtype": "Currency",
+ "width": 100
+ },
+ {
+ "label": _("Delayed Days"),
+ "fieldname": "delayed_days",
+ "fieldtype": "Int",
+ "width": 100
+ },
+ {
+ "label": _("Sales Order"),
+ "fieldname": "sales_order",
+ "fieldtype": "Link",
+ "options": "Sales Order",
+ "width": 100
+ },
+ {
+ "label": _("Customer PO"),
+ "fieldname": "po_no",
+ "fieldtype": "Data",
+ "width": 100
+ }]
\ No newline at end of file
diff --git a/erpnext/stock/report/delayed_order_report/__init__.py b/erpnext/stock/report/delayed_order_report/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/stock/report/delayed_order_report/__init__.py
diff --git a/erpnext/stock/report/delayed_order_report/delayed_order_report.js b/erpnext/stock/report/delayed_order_report/delayed_order_report.js
new file mode 100644
index 0000000..11752ae
--- /dev/null
+++ b/erpnext/stock/report/delayed_order_report/delayed_order_report.js
@@ -0,0 +1,62 @@
+// Copyright (c) 2016, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+/* eslint-disable */
+
+frappe.query_reports["Delayed Order Report"] = {
+ "filters": [
+ {
+ fieldname: "company",
+ label: __("Company"),
+ fieldtype: "Link",
+ options: "Company",
+ default: frappe.defaults.get_default("company"),
+ reqd: 1
+ },
+ {
+ fieldname:"from_date",
+ label: __("From Date"),
+ fieldtype: "Date",
+ default: frappe.datetime.month_start(),
+ reqd: 1
+ },
+ {
+ fieldname:"to_date",
+ label: __("To Date"),
+ fieldtype: "Date",
+ default: frappe.datetime.now_date(),
+ reqd: 1
+ },
+ {
+ fieldname:"sales_order",
+ label: __("Sales Order"),
+ fieldtype: "Link",
+ options: "Sales Order",
+ },
+ {
+ fieldname:"customer",
+ label: __("Customer"),
+ fieldtype: "Link",
+ options: "Customer",
+ },
+ {
+ fieldname:"customer_group",
+ label: __("Customer Group"),
+ fieldtype: "Link",
+ options: "Customer Group",
+ },
+ {
+ fieldname:"item_group",
+ label: __("Item Group"),
+ fieldtype: "Link",
+ options: "Item Group",
+ },
+ {
+ fieldname:"based_on",
+ label: __("Based On"),
+ fieldtype: "Select",
+ options: ["Delivery Note", "Sales Invoice"],
+ default: "Delivery Note",
+ reqd: 1
+ },
+ ]
+}
\ No newline at end of file
diff --git a/erpnext/stock/report/delayed_order_report/delayed_order_report.json b/erpnext/stock/report/delayed_order_report/delayed_order_report.json
new file mode 100644
index 0000000..29c27cb
--- /dev/null
+++ b/erpnext/stock/report/delayed_order_report/delayed_order_report.json
@@ -0,0 +1,37 @@
+{
+ "add_total_row": 0,
+ "creation": "2019-06-17 12:45:56.359322",
+ "disable_prepared_report": 0,
+ "disabled": 0,
+ "docstatus": 0,
+ "doctype": "Report",
+ "idx": 0,
+ "is_standard": "Yes",
+ "letter_head": "Gadgets International",
+ "modified": "2019-06-17 12:45:56.359322",
+ "modified_by": "Administrator",
+ "module": "Stock",
+ "name": "Delayed Order Report",
+ "owner": "Administrator",
+ "prepared_report": 0,
+ "ref_doctype": "Delivery Note",
+ "report_name": "Delayed Order Report",
+ "report_type": "Script Report",
+ "roles": [
+ {
+ "role": "Accounts User"
+ },
+ {
+ "role": "Sales User"
+ },
+ {
+ "role": "Stock Manager"
+ },
+ {
+ "role": "Stock User"
+ },
+ {
+ "role": "Maintenance User"
+ }
+ ]
+}
\ No newline at end of file
diff --git a/erpnext/stock/report/delayed_order_report/delayed_order_report.py b/erpnext/stock/report/delayed_order_report/delayed_order_report.py
new file mode 100644
index 0000000..d2a1a30
--- /dev/null
+++ b/erpnext/stock/report/delayed_order_report/delayed_order_report.py
@@ -0,0 +1,83 @@
+# Copyright (c) 2013, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+
+from __future__ import unicode_literals
+from frappe import _
+from erpnext.stock.report.delayed_item_report.delayed_item_report import DelayedItemReport
+
+def execute(filters=None):
+ columns, data = [], []
+
+ columns, data = DelayedOrderReport(filters).run()
+
+ return columns, data
+
+class DelayedOrderReport(DelayedItemReport):
+ def run(self):
+ return self.get_columns(), self.get_data(consolidated=True) or []
+
+ def get_data(self, consolidated=False):
+ data = super(DelayedOrderReport, self).get_data(consolidated) or []
+
+ so_list = []
+ result = []
+ for d in data:
+ if d.sales_order not in so_list:
+ so_list.append(d.sales_order)
+ result.append(d)
+
+ return result
+
+ def get_columns(self):
+ based_on = self.filters.get("based_on")
+
+ return [{
+ "label": _(based_on),
+ "fieldname": "name",
+ "fieldtype": "Link",
+ "options": based_on,
+ "width": 100
+ },{
+ "label": _("Customer"),
+ "fieldname": "customer",
+ "fieldtype": "Link",
+ "options": "Customer",
+ "width": 100
+ },
+ {
+ "label": _("Expected Delivery Date"),
+ "fieldname": "delivery_date",
+ "fieldtype": "Date",
+ "width": 100
+ },
+ {
+ "label": _("Actual Delivery Date"),
+ "fieldname": "posting_date",
+ "fieldtype": "Date",
+ "width": 100
+ },
+ {
+ "label": _("Amount"),
+ "fieldname": "grand_total",
+ "fieldtype": "Currency",
+ "width": 100
+ },
+ {
+ "label": _("Delayed Days"),
+ "fieldname": "delayed_days",
+ "fieldtype": "Int",
+ "width": 100
+ },
+ {
+ "label": _("Sales Order"),
+ "fieldname": "sales_order",
+ "fieldtype": "Link",
+ "options": "Sales Order",
+ "width": 100
+ },
+ {
+ "label": _("Customer PO"),
+ "fieldname": "po_no",
+ "fieldtype": "Data",
+ "width": 100
+ }]
\ No newline at end of file