enhancement: Item-wise sales history report (#19500)
* refactor(item-wise-sales-history): convert query report to script report
* refactor: add columns, fetch data
* refactor: shift company set func to utils
* fix: add filters
* fix: minor changes
* fix: fetch all the descendants
diff --git a/erpnext/hr/doctype/staffing_plan/staffing_plan.py b/erpnext/hr/doctype/staffing_plan/staffing_plan.py
index e6afbcc..595bcaa 100644
--- a/erpnext/hr/doctype/staffing_plan/staffing_plan.py
+++ b/erpnext/hr/doctype/staffing_plan/staffing_plan.py
@@ -7,6 +7,7 @@
from frappe.model.document import Document
from frappe import _
from frappe.utils import getdate, nowdate, cint, flt
+from frappe.utils.nestedset import get_descendants_of
class SubsidiaryCompanyError(frappe.ValidationError): pass
class ParentCompanyError(frappe.ValidationError): pass
@@ -131,7 +132,8 @@
return False
employee_counts = {}
- company_set = get_company_set(company)
+ company_set = get_descendants_of('Company', company)
+ company_set.append(company)
employee_counts["employee_count"] = frappe.db.get_value("Employee",
filters={
@@ -167,14 +169,4 @@
designation, from_date, to_date)
# Only a single staffing plan can be active for a designation on given date
- return staffing_plan if staffing_plan else None
-
-def get_company_set(company):
- return frappe.db.sql_list("""
- SELECT
- name
- FROM `tabCompany`
- WHERE
- parent_company=%(company)s
- OR name=%(company)s
- """, (dict(company=company)))
\ No newline at end of file
+ return staffing_plan if staffing_plan else None
\ No newline at end of file
diff --git a/erpnext/selling/report/item_wise_sales_history/item_wise_sales_history.js b/erpnext/selling/report/item_wise_sales_history/item_wise_sales_history.js
new file mode 100644
index 0000000..ee806a7
--- /dev/null
+++ b/erpnext/selling/report/item_wise_sales_history/item_wise_sales_history.js
@@ -0,0 +1,33 @@
+// Copyright (c) 2019, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+/* eslint-disable */
+
+frappe.query_reports["Item-wise Sales History"] = {
+ "filters": [
+ {
+ fieldname:"company",
+ label: __("Company"),
+ fieldtype: "Link",
+ options: "Company",
+ default: frappe.defaults.get_user_default("Company"),
+ reqd: 1
+ },
+ {
+ fieldname:"item_group",
+ label: __("Item Group"),
+ fieldtype: "Link",
+ options: "Item Group"
+ },
+ {
+ fieldname:"from_date",
+ label: __("From Date"),
+ fieldtype: "Date",
+ },
+ {
+ fieldname:"to_date",
+ label: __("To Date"),
+ fieldtype: "Date",
+ },
+
+ ]
+};
\ No newline at end of file
diff --git a/erpnext/selling/report/item_wise_sales_history/item_wise_sales_history.json b/erpnext/selling/report/item_wise_sales_history/item_wise_sales_history.json
index 88e6f27..a6dda28 100644
--- a/erpnext/selling/report/item_wise_sales_history/item_wise_sales_history.json
+++ b/erpnext/selling/report/item_wise_sales_history/item_wise_sales_history.json
@@ -1,34 +1,34 @@
{
- "add_total_row": 1,
- "creation": "2013-05-23 17:42:24",
- "disabled": 0,
- "docstatus": 0,
- "doctype": "Report",
- "idx": 3,
- "is_standard": "Yes",
- "modified": "2019-01-03 22:52:41.519890",
- "modified_by": "Administrator",
- "module": "Selling",
- "name": "Item-wise Sales History",
- "owner": "Administrator",
- "prepared_report": 0,
- "query": "select\n so_item.item_code as \"Item Code:Link/Item:120\",\n\tso_item.item_name as \"Item Name::120\",\n so_item.item_group as \"Item Group:Link/Item Group:120\",\n\tso_item.description as \"Description::150\",\n\tso_item.qty as \"Qty:Data:100\",\n\tso_item.uom as \"UOM:Link/UOM:80\",\n\tso_item.base_rate as \"Rate:Currency:120\",\n\tso_item.base_amount as \"Amount:Currency:120\",\n\tso.name as \"Sales Order:Link/Sales Order:120\",\n\tso.transaction_date as \"Transaction Date:Date:140\",\n\tso.customer as \"Customer:Link/Customer:130\",\n cu.customer_name as \"Customer Name::150\",\n\tcu.customer_group as \"Customer Group:Link/Customer Group:130\",\n\tso.territory as \"Territory:Link/Territory:130\",\n \tso.project as \"Project:Link/Project:130\",\n\tifnull(so_item.delivered_qty, 0) as \"Delivered Qty:Float:120\",\n\tifnull(so_item.billed_amt, 0) as \"Billed Amount:Currency:120\",\n\tso.company as \"Company:Link/Company:\"\nfrom\n\t`tabSales Order` so, `tabSales Order Item` so_item, `tabCustomer` cu\nwhere\n\tso.name = so_item.parent and so.customer=cu.name\n\tand so.docstatus = 1\norder by so.name desc",
- "ref_doctype": "Sales Order",
- "report_name": "Item-wise Sales History",
- "report_type": "Query Report",
+ "add_total_row": 1,
+ "creation": "2013-05-23 17:42:24",
+ "disable_prepared_report": 0,
+ "disabled": 0,
+ "docstatus": 0,
+ "doctype": "Report",
+ "idx": 3,
+ "is_standard": "Yes",
+ "modified": "2019-11-04 16:28:14.608904",
+ "modified_by": "Administrator",
+ "module": "Selling",
+ "name": "Item-wise Sales History",
+ "owner": "Administrator",
+ "prepared_report": 0,
+ "ref_doctype": "Sales Order",
+ "report_name": "Item-wise Sales History",
+ "report_type": "Script Report",
"roles": [
{
"role": "Sales User"
- },
+ },
{
"role": "Sales Manager"
- },
+ },
{
"role": "Maintenance User"
- },
+ },
{
"role": "Accounts User"
- },
+ },
{
"role": "Stock User"
}
diff --git a/erpnext/selling/report/item_wise_sales_history/item_wise_sales_history.py b/erpnext/selling/report/item_wise_sales_history/item_wise_sales_history.py
new file mode 100644
index 0000000..226c34f
--- /dev/null
+++ b/erpnext/selling/report/item_wise_sales_history/item_wise_sales_history.py
@@ -0,0 +1,214 @@
+# Copyright (c) 2019, 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 flt
+from frappe.utils.nestedset import get_descendants_of
+
+def execute(filters=None):
+ filters = frappe._dict(filters or {})
+ columns = get_columns(filters)
+ data = get_data(filters)
+ return columns, data
+
+def get_columns(filters):
+ return [
+ {
+ "label": _("Item Code"),
+ "fieldtype": "Link",
+ "fieldname": "item_code",
+ "options": "Item",
+ "width": 120
+ },
+ {
+ "label": _("Item Name"),
+ "fieldtype": "Data",
+ "fieldname": "item_name",
+ "width": 140
+ },
+ {
+ "label": _("Item Group"),
+ "fieldtype": "Link",
+ "fieldname": "item_group",
+ "options": "Item Group",
+ "width": 120
+ },
+ {
+ "label": _("Description"),
+ "fieldtype": "Data",
+ "fieldname": "description",
+ "width": 150
+ },
+ {
+ "label": _("Quantity"),
+ "fieldtype": "Float",
+ "fieldname": "quantity",
+ "width": 150
+ },
+ {
+ "label": _("UOM"),
+ "fieldtype": "Link",
+ "fieldname": "uom",
+ "options": "UOM",
+ "width": 100
+ },
+ {
+ "label": _("Rate"),
+ "fieldname": "rate",
+ "options": "Currency",
+ "width": 120
+ },
+ {
+ "label": _("Amount"),
+ "fieldname": "amount",
+ "options": "Currency",
+ "width": 120
+ },
+ {
+ "label": _("Sales Order"),
+ "fieldtype": "Link",
+ "fieldname": "sales_order",
+ "options": "Sales Order",
+ "width": 100
+ },
+ {
+ "label": _("Transaction Date"),
+ "fieldtype": "Date",
+ "fieldname": "transaction_date",
+ "width": 90
+ },
+ {
+ "label": _("Customer"),
+ "fieldtype": "Link",
+ "fieldname": "customer",
+ "options": "Customer",
+ "width": 100
+ },
+ {
+ "label": _("Customer Name"),
+ "fieldtype": "Data",
+ "fieldname": "customer_name",
+ "width": 140
+ },
+ {
+ "label": _("Customer Group"),
+ "fieldtype": "Link",
+ "fieldname": "customer_group",
+ "options": "customer Group",
+ "width": 120
+ },
+ {
+ "label": _("Territory"),
+ "fieldtype": "Link",
+ "fieldname": "territory",
+ "options": "Territory",
+ "width": 100
+ },
+ {
+ "label": _("Project"),
+ "fieldtype": "Link",
+ "fieldname": "project",
+ "options": "Project",
+ "width": 100
+ },
+ {
+ "label": _("Delivered Quantity"),
+ "fieldtype": "Float",
+ "fieldname": "delivered_quantity",
+ "width": 150
+ },
+ {
+ "label": _("Billed Amount"),
+ "fieldname": "rate",
+ "options": "billed_amount",
+ "width": 120
+ },
+ {
+ "label": _("Company"),
+ "fieldtype": "Link",
+ "fieldname": "company",
+ "options": "Company",
+ "width": 100
+ }
+ ]
+
+def get_data(filters):
+
+ data = []
+
+ company_list = get_descendants_of("Company", filters.get("company"))
+ company_list.append(filters.get("company"))
+
+ customer_details = get_customer_details()
+ sales_order_records = get_sales_order_details(company_list, filters)
+
+ for record in sales_order_records:
+ customer_record = customer_details.get(record.customer)
+ row = {
+ "item_code": record.item_code,
+ "item_name": record.item_name,
+ "item_group": record.item_group,
+ "description": record.description,
+ "quantity": record.qty,
+ "uom": record.uom,
+ "rate": record.base_rate,
+ "amount": record.base_amount,
+ "sales_order": record.name,
+ "transaction_date": record.transaction_date,
+ "customer": record.customer,
+ "customer_name": customer_record.customer_name,
+ "customer_group": customer_record.customer_group,
+ "territory": record.territory,
+ "project": record.project,
+ "delivered_quantity": flt(record.delivered_qty),
+ "billed_amount": flt(record.billed_amt),
+ "company": record.company
+ }
+ data.append(row)
+
+ return data
+
+def get_conditions(filters):
+ conditions = ''
+ if filters.get('item_group'):
+ conditions += "AND so_item.item_group = %s" %frappe.db.escape(filters.item_group)
+
+ if filters.get('from_date'):
+ conditions += "AND so.transaction_date >= '%s'" %filters.from_date
+
+ if filters.get('to_date'):
+ conditions += "AND so.transaction_date <= '%s'" %filters.to_date
+
+ return conditions
+
+def get_customer_details():
+ details = frappe.get_all('Customer',
+ fields=['name', 'customer_name', "customer_group"])
+ customer_details = {}
+ for d in details:
+ customer_details.setdefault(d.name, frappe._dict({
+ "customer_name": d.customer_name,
+ "customer_group": d.customer_group
+ }))
+ return customer_details
+
+def get_sales_order_details(company_list, filters):
+ conditions = get_conditions(filters)
+ return frappe.db.sql("""
+ SELECT
+ so_item.item_code, so_item.item_name, so_item.item_group,
+ so_item.description, so_item.qty, so_item.uom,
+ so_item.base_rate, so_item.base_amount, so.name,
+ so.transaction_date, so.customer, so.territory,
+ so.project, so_item.delivered_qty,
+ so_item.billed_amt, so.company
+ FROM
+ `tabSales Order` so, `tabSales Order Item` so_item
+ WHERE
+ so.name = so_item.parent
+ AND so.company in (%s)
+ AND so.docstatus = 1
+ {0}
+ """.format(conditions), company_list, as_dict=1) #nosec