[Enhancement] Pending SO Items for Purchase Request Report (#16188)
* deleted old purchase request query report
* [Enhancement] pending so items for purchase request report refactor
* comments and format fix
* test and minor fixes
* test fixes
diff --git a/erpnext/selling/report/pending_so_items_for_purchase_request/pending_so_items_for_purchase_request.js b/erpnext/selling/report/pending_so_items_for_purchase_request/pending_so_items_for_purchase_request.js
new file mode 100644
index 0000000..37634ef
--- /dev/null
+++ b/erpnext/selling/report/pending_so_items_for_purchase_request/pending_so_items_for_purchase_request.js
@@ -0,0 +1,6 @@
+// Copyright (c) 2016, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+/* eslint-disable */
+
+frappe.query_reports["Pending SO Items For Purchase Request"] = {
+}
diff --git a/erpnext/selling/report/pending_so_items_for_purchase_request/pending_so_items_for_purchase_request.json b/erpnext/selling/report/pending_so_items_for_purchase_request/pending_so_items_for_purchase_request.json
index 128544b..3cf3235 100644
--- a/erpnext/selling/report/pending_so_items_for_purchase_request/pending_so_items_for_purchase_request.json
+++ b/erpnext/selling/report/pending_so_items_for_purchase_request/pending_so_items_for_purchase_request.json
@@ -1,36 +1,35 @@
{
- "add_total_row": 0,
- "apply_user_permissions": 1,
- "creation": "2013-06-21 16:46:45",
- "disabled": 0,
- "docstatus": 0,
- "doctype": "Report",
- "idx": 3,
- "is_standard": "Yes",
- "modified": "2017-02-24 20:08:11.744036",
- "modified_by": "Administrator",
- "module": "Selling",
- "name": "Pending SO Items For Purchase Request",
- "owner": "Administrator",
- "query": "select so_item.item_code as \"Item Code:Link/Item:120\",\n so_item.item_name as \"Item Name::120\",\n so_item.description as \"Description::120\",\n so.`name` as \"S.O. No.:Link/Sales Order:120\",\n so.`transaction_date` as \"Date:Date:120\",\n mr.name as \"Material Request:Link/Material Request:120\",\n so.customer as \"Customer:Link/Customer:120\",\n so.territory as \"Terretory:Link/Territory:120\",\n sum(so_item.qty) as \"SO Qty:Float:100 \",\n sum(mr_item.qty) as \"Requested Qty:Float:100\",\n sum(so_item.qty) - sum(mr_item.qty) as \"Pending Qty:Float:100 \", \n so.company as \"Company:Link/Company:\"\nfrom\n `tabSales Order` so, `tabSales Order Item` so_item, \n `tabMaterial Request` mr, `tabMaterial Request Item` mr_item\nwhere \n so_item.`parent` = so.`name` \n and mr_item.parent = mr.name\n and mr_item.sales_order = so.name\n and mr_item.item_code = so_item.item_code\n and so.docstatus = 1 and so.status != \"Closed\" \n and mr.docstatus = 1 and mr.status != \"Stopped\"\ngroup by so.name, so_item.item_code\nhaving sum(so_item.qty) > sum(mr_item.qty)\norder by so.name desc, so_item.item_code asc",
- "ref_doctype": "Sales Order",
- "report_name": "Pending SO Items For Purchase Request",
- "report_type": "Query Report",
+ "add_total_row": 0,
+ "creation": "2018-11-12 14:08:27.241332",
+ "disabled": 0,
+ "docstatus": 0,
+ "doctype": "Report",
+ "idx": 0,
+ "is_standard": "Yes",
+ "modified": "2018-11-12 14:08:27.241332",
+ "modified_by": "Administrator",
+ "module": "Selling",
+ "name": "Pending SO Items For Purchase Request",
+ "owner": "Administrator",
+ "prepared_report": 0,
+ "ref_doctype": "Sales Order",
+ "report_name": "Pending SO Items For Purchase Request",
+ "report_type": "Script Report",
"roles": [
{
- "role": "Sales User"
- },
+ "role": "Stock User"
+ },
{
"role": "Sales Manager"
- },
+ },
{
"role": "Maintenance User"
- },
+ },
{
"role": "Accounts User"
- },
+ },
{
- "role": "Stock User"
+ "role": "Sales User"
}
]
}
\ No newline at end of file
diff --git a/erpnext/selling/report/pending_so_items_for_purchase_request/pending_so_items_for_purchase_request.py b/erpnext/selling/report/pending_so_items_for_purchase_request/pending_so_items_for_purchase_request.py
new file mode 100644
index 0000000..8721651
--- /dev/null
+++ b/erpnext/selling/report/pending_so_items_for_purchase_request/pending_so_items_for_purchase_request.py
@@ -0,0 +1,148 @@
+# 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 cint,cstr
+
+def execute(filters=None):
+ columns = get_columns()
+ data = get_data()
+ return columns, data
+
+def get_columns():
+ columns = [
+ {
+ "label": _("Item Code"),
+ "options": "Item",
+ "fieldname": "item_code",
+ "fieldtype": "Link",
+ "width": 200
+ },
+ {
+ "label": _("Item Name"),
+ "fieldname": "item_name",
+ "fieldtype": "Data",
+ "width": 200
+ },
+ {
+ "label": _("Description"),
+ "fieldname": "description",
+ "fieldtype": "Data",
+ "width": 140
+ },
+ {
+ "label": _("S.O. No."),
+ "options": "Sales Order",
+ "fieldname": "sales_order_no",
+ "fieldtype": "Link",
+ "width": 140
+ },
+ {
+ "label": _("Date"),
+ "fieldname": "date",
+ "fieldtype": "Date",
+ "width": 140
+ },
+ {
+ "label": _("Material Request"),
+ "options": "Material Request",
+ "fieldname": "material_request",
+ "fieldtype": "Link",
+ "width": 140
+ },
+ {
+ "label": _("Customer"),
+ "fieldname": "customer",
+ "fieldtype": "Data",
+ "width": 140
+ },
+ {
+ "label": _("Territory"),
+ "fieldname": "territory",
+ "fieldtype": "Data",
+ "width": 140
+ },
+ {
+ "label": _("SO Qty"),
+ "fieldname": "so_qty",
+ "fieldtype": "Float",
+ "width": 140
+ },
+ {
+ "label": _("Requested Qty"),
+ "fieldname": "requested_qty",
+ "fieldtype": "Float",
+ "width": 140
+ },
+ {
+ "label": _("Pending Qty"),
+ "fieldname": "pending_qty",
+ "fieldtype": "Float",
+ "width": 140
+ },
+ {
+ "label": _("Company"),
+ "fieldname": "company",
+ "fieldtype": "Data",
+ "width": 140
+ }
+ ]
+ return columns
+
+def get_data():
+ sales_order_entry = frappe.db.sql("""
+ SELECT
+ so_item.item_code,
+ so_item.item_name,
+ so_item.description,
+ so.name,
+ so.transaction_date,
+ so.customer,
+ so.territory,
+ sum(so_item.qty) as net_qty,
+ so.company
+ FROM `tabSales Order` so, `tabSales Order Item` so_item
+ WHERE
+ so.docstatus = 1
+ and so.name = so_item.parent
+ and so.status not in ("Closed","Completed","Cancelled")
+ GROUP BY
+ so.name,so_item.item_code
+ """, as_dict = 1)
+
+ mr_records = frappe.get_all("Material Request Item",
+ {"sales_order_item": ("!=",""), "docstatus": 1},
+ ["parent", "qty", "sales_order", "item_code"])
+
+ grouped_records = {}
+
+ for record in mr_records:
+ grouped_records.setdefault(record.sales_order, []).append(record)
+
+ pending_so=[]
+ for so in sales_order_entry:
+ # fetch all the material request records for a sales order item
+ mr_list = grouped_records.get(so.name) or [{}]
+ mr_item_record = ([mr for mr in mr_list if mr.get('item_code') == so.item_code] or [{}])
+
+ for mr in mr_item_record:
+ # check for pending sales order
+ if cint(so.net_qty) > cint(mr.get('qty')):
+ so_record = {
+ "item_code": so.item_code,
+ "item_name": so.item_name,
+ "description": so.description,
+ "sales_order_no": so.name,
+ "date": so.transaction_date,
+ "material_request": cstr(mr.get('parent')),
+ "customer": so.customer,
+ "territory": so.territory,
+ "so_qty": so.net_qty,
+ "requested_qty": cint(mr.get('qty')),
+ "pending_qty": so.net_qty - cint(mr.get('qty')),
+ "company": so.company
+ }
+ pending_so.append(so_record)
+ return pending_so
\ No newline at end of file
diff --git a/erpnext/selling/report/pending_so_items_for_purchase_request/test_pending_so_items_for_purchase_request.py b/erpnext/selling/report/pending_so_items_for_purchase_request/test_pending_so_items_for_purchase_request.py
new file mode 100644
index 0000000..f2b7701
--- /dev/null
+++ b/erpnext/selling/report/pending_so_items_for_purchase_request/test_pending_so_items_for_purchase_request.py
@@ -0,0 +1,27 @@
+# Copyright (c) 2013, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+
+import unittest
+from frappe.utils import nowdate, add_months
+from erpnext.selling.report.pending_so_items_for_purchase_request.pending_so_items_for_purchase_request\
+ import execute
+from erpnext.selling.doctype.sales_order.sales_order import make_material_request
+from erpnext.selling.doctype.sales_order.test_sales_order import make_sales_order
+
+
+class TestPendingSOItemsForPurchaseRequest(unittest.TestCase):
+ def test_result_for_partial_material_request(self):
+ so = make_sales_order()
+ mr=make_material_request(so.name)
+ mr.items[0].qty = 4
+ mr.schedule_date = add_months(nowdate(),1)
+ mr.submit()
+ report = execute()
+ l = len(report[1])
+ self.assertEqual((so.items[0].qty - mr.items[0].qty), report[1][l-1]['pending_qty'])
+
+ def test_result_for_so_item(self):
+ so = make_sales_order()
+ report = execute()
+ l = len(report[1])
+ self.assertEqual(so.items[0].qty, report[1][l-1]['pending_qty'])